<?php

require_once __DIR__ . '/../config/database.php';

class ApiProviderRotationService
{
    public const BUILD = 'API_PROVIDER_ROTATION_LOG_FLOOD_SAFE_V1_2026_06_05';

    public static function normalizeProvider(string $provider): string
    {
        $provider = strtolower(trim($provider));
        $map = [
            'financialmodelingprep' => 'fmp',
            'financial_modeling_prep' => 'fmp',
            'twelve_data' => 'twelvedata',
            'twelve-data' => 'twelvedata',
            'alpha-vantage' => 'alphavantage',
            'alpha_vantage' => 'alphavantage',
            'goldapi.io' => 'goldapiio',
            'goldapi' => 'goldapiio',
            'gold-api.com' => 'gold-api',
            'metals-api' => 'metalsapi',
            'metalprice-api' => 'metalpriceapi',
        ];
        return $map[$provider] ?? $provider;
    }

    public static function providerAliases(string $provider): array
    {
        $provider = self::normalizeProvider($provider);
        $aliases = [
            'fmp' => ['fmp', 'financialmodelingprep', 'financial_modeling_prep'],
            'twelvedata' => ['twelvedata', 'twelve_data', 'twelve-data'],
            'alphavantage' => ['alphavantage', 'alpha_vantage', 'alpha-vantage'],
            'goldapiio' => ['goldapiio', 'goldapi', 'goldapi.io'],
            'gold-api' => ['gold-api', 'gold-api.com', 'goldapi_free', 'goldapi_public'],
            'metalsapi' => ['metalsapi', 'metals-api'],
            'metalpriceapi' => ['metalpriceapi', 'metalprice-api'],
            'finnhub' => ['finnhub'],
            'binance' => ['binance'],
        ];
        return $aliases[$provider] ?? [$provider];
    }

    private static function setting(string $key, string $default = ''): string
    {
        try {
            if (!self::tableExists('system_settings')) {
                return $default;
            }
            $stmt = db()->prepare("SELECT setting_value FROM system_settings WHERE setting_key = ? ORDER BY id DESC LIMIT 1");
            $stmt->execute([$key]);
            $value = $stmt->fetchColumn();
            return $value !== false ? (string)$value : $default;
        } catch (Throwable $e) {
            return $default;
        }
    }

    private static function boolSetting(string $key, bool $default = false): bool
    {
        $value = strtolower(trim(self::setting($key, $default ? '1' : '0')));
        return in_array($value, ['1', 'true', 'yes', 'on', 'enabled'], true);
    }

    private static function logStatusEnabled(string $status): bool
    {
        $status = strtolower(trim($status));
        if ($status === 'attempt') return self::boolSetting('api_provider_log_attempts_enabled', false);
        if ($status === 'success') return self::boolSetting('api_provider_log_success_enabled', true);
        if ($status === 'failed') return self::boolSetting('api_provider_log_failures_enabled', true);
        if ($status === 'rate_limited') return self::boolSetting('api_provider_log_rate_limited_enabled', true);
        return self::boolSetting('api_provider_log_other_enabled', false);
    }

    private static function logThrottleSeconds(string $status): int
    {
        $status = strtolower(trim($status));
        if ($status === 'failed') return max(0, min(86400, (int)self::setting('api_provider_log_failure_throttle_seconds', '900')));
        if ($status === 'rate_limited') return max(0, min(86400, (int)self::setting('api_provider_log_rate_limit_throttle_seconds', '900')));
        if ($status === 'attempt') return max(0, min(86400, (int)self::setting('api_provider_log_attempt_throttle_seconds', '300')));
        if ($status === 'success') return max(0, min(86400, (int)self::setting('api_provider_log_success_throttle_seconds', '0')));
        return 0;
    }


    public static function getBestProvider(string $capability = 'candles', ?string $symbol = null, ?string $timeframe = null, ?string $preferredProvider = null): ?array
    {
        $rows = self::getAllActiveProviders($capability, $preferredProvider);
        return $rows[0] ?? null;
    }

    public static function getAllActiveProviders(string $capability = 'candles', ?string $preferredProvider = null): array
    {
        self::resetDailyUsageIfNeeded();

        if (!self::tableExists('api_settings')) {
            return [];
        }

        $capabilityColumn = $capability === 'quote' ? 'supports_quote' : 'supports_candles';
        $where = [];
        $params = [];

        $where[] = self::columnExists('api_settings', 'is_active') ? 'COALESCE(is_active, 1) = 1' : '1=1';

        if (self::columnExists('api_settings', $capabilityColumn)) {
            $where[] = "($capabilityColumn = 1 OR $capabilityColumn IS NULL)";
        }

        if (self::columnExists('api_settings', 'cooldown_until')) {
            $where[] = "(cooldown_until IS NULL OR cooldown_until = '0000-00-00 00:00:00' OR cooldown_until <= NOW())";
        }

        if ($preferredProvider !== null && trim($preferredProvider) !== '') {
            $aliases = self::providerAliases($preferredProvider);
            $where[] = 'LOWER(provider) IN (' . implode(',', array_fill(0, count($aliases), '?')) . ')';
            foreach ($aliases as $alias) {
                $params[] = strtolower($alias);
            }
        }

        try {
            $stmt = db()->prepare('SELECT * FROM api_settings WHERE ' . implode(' AND ', $where));
            $stmt->execute($params);
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (Throwable $e) {
            return [];
        }

        $filtered = [];
        foreach ($rows as $row) {
            $provider = self::normalizeProvider((string)($row['provider'] ?? ''));
            $row['__normalized_provider'] = $provider;
            $row['__effective_daily_limit'] = self::effectiveDailyLimit($provider, $row);
            $row['__usage_ratio'] = 0.0;

            $limit = (int)$row['__effective_daily_limit'];
            $used = (int)($row['used_today'] ?? 0);
            if ($limit > 0) {
                $row['__usage_ratio'] = $used / max(1, $limit);
                if ($used >= $limit) {
                    continue;
                }
            }

            $filtered[] = $row;
        }

        usort($filtered, function ($a, $b) {
            $priorityA = (int)($a['priority_order'] ?? 999999);
            $priorityB = (int)($b['priority_order'] ?? 999999);
            if ($priorityA !== $priorityB) return $priorityA <=> $priorityB;

            $failA = (int)($a['fail_count'] ?? 0);
            $failB = (int)($b['fail_count'] ?? 0);
            if ($failA !== $failB) return $failA <=> $failB;

            $ratioA = (float)($a['__usage_ratio'] ?? 0);
            $ratioB = (float)($b['__usage_ratio'] ?? 0);
            if (abs($ratioA - $ratioB) > 0.000001) return $ratioA <=> $ratioB;

            $lastA = strtotime((string)($a['last_used_at'] ?? '')) ?: 0;
            $lastB = strtotime((string)($b['last_used_at'] ?? '')) ?: 0;
            if ($lastA !== $lastB) return $lastA <=> $lastB;

            return (int)($a['id'] ?? 0) <=> (int)($b['id'] ?? 0);
        });

        return $filtered;
    }

    public static function markAttempt(array $provider, string $endpoint, ?string $symbol = null, ?string $timeframe = null, int $creditsUsed = 1): void
    {
        $id = (int)($provider['id'] ?? 0);
        if ($id <= 0 || $creditsUsed <= 0) {
            return;
        }

        $sets = [];
        $params = [];
        if (self::columnExists('api_settings', 'used_today')) { $sets[] = 'used_today = COALESCE(used_today,0) + ?'; $params[] = $creditsUsed; }
        if (self::columnExists('api_settings', 'last_used_at')) { $sets[] = 'last_used_at = NOW()'; }
        if ($sets) {
            $params[] = $id;
            try {
                db()->prepare('UPDATE api_settings SET ' . implode(', ', $sets) . ' WHERE id = ?')->execute($params);
            } catch (Throwable $e) {
            }
        }

        if (self::boolSetting('api_provider_log_attempts_enabled', false)) {
            self::logRotation($provider, $endpoint, $symbol, $timeframe, 'attempt', 0, $creditsUsed, '');
        }
    }

    public static function markSuccess(array $provider, string $endpoint, ?string $symbol = null, ?string $timeframe = null, int $creditsUsed = 0, int $responseMs = 0): void
    {
        $id = (int)($provider['id'] ?? 0);

        if ($id <= 0) {
            return;
        }

        $sets = [];
        $params = [];
        if ($creditsUsed > 0 && self::columnExists('api_settings', 'used_today')) { $sets[] = 'used_today = COALESCE(used_today,0) + ?'; $params[] = $creditsUsed; }
        if (self::columnExists('api_settings', 'last_used_at')) $sets[] = 'last_used_at = NOW()';
        if (self::columnExists('api_settings', 'fail_count')) $sets[] = 'fail_count = 0';
        if (self::columnExists('api_settings', 'cooldown_until')) $sets[] = 'cooldown_until = NULL';
        if (self::columnExists('api_settings', 'last_error')) $sets[] = 'last_error = NULL';

        if ($sets) {
            $params[] = $id;
            try {
                db()->prepare('UPDATE api_settings SET ' . implode(', ', $sets) . ' WHERE id = ?')->execute($params);
            } catch (Throwable $e) {
            }
        }

        self::logRotation($provider, $endpoint, $symbol, $timeframe, 'success', $responseMs, $creditsUsed, '');
        self::updateHealth($provider, 'connected', $responseMs, null);
    }

    public static function markFailure(array $provider, string $endpoint, string $errorMessage, ?string $symbol = null, ?string $timeframe = null, int $responseMs = 0): void
    {
        $id = (int)($provider['id'] ?? 0);

        if ($id <= 0) {
            return;
        }

        $cooldownMinutes = self::calculateCooldownMinutes($provider);
        $sets = [];
        $params = [];
        if (self::columnExists('api_settings', 'fail_count')) $sets[] = 'fail_count = COALESCE(fail_count,0) + 1';
        if (self::columnExists('api_settings', 'last_used_at')) $sets[] = 'last_used_at = NOW()';
        if (self::columnExists('api_settings', 'cooldown_until')) { $sets[] = 'cooldown_until = DATE_ADD(NOW(), INTERVAL ? MINUTE)'; $params[] = $cooldownMinutes; }
        if (self::columnExists('api_settings', 'last_error')) { $sets[] = 'last_error = ?'; $params[] = substr($errorMessage, 0, 500); }

        if ($sets) {
            $params[] = $id;
            try {
                db()->prepare('UPDATE api_settings SET ' . implode(', ', $sets) . ' WHERE id = ?')->execute($params);
            } catch (Throwable $e) {
            }
        }

        self::logRotation($provider, $endpoint, $symbol, $timeframe, 'failed', $responseMs, 0, $errorMessage);
        self::updateHealth($provider, 'failed', $responseMs, $errorMessage);
    }

    public static function markRateLimited(array $provider, string $endpoint, string $errorMessage, ?string $symbol = null, ?string $timeframe = null, int $responseMs = 0): void
    {
        $id = (int)($provider['id'] ?? 0);

        if ($id <= 0) {
            return;
        }

        $sets = [];
        $params = [];
        if (self::columnExists('api_settings', 'fail_count')) $sets[] = 'fail_count = COALESCE(fail_count,0) + 1';
        if (self::columnExists('api_settings', 'last_used_at')) $sets[] = 'last_used_at = NOW()';
        if (self::columnExists('api_settings', 'cooldown_until')) $sets[] = 'cooldown_until = DATE_ADD(NOW(), INTERVAL 60 MINUTE)';
        if (self::columnExists('api_settings', 'last_error')) { $sets[] = 'last_error = ?'; $params[] = substr($errorMessage, 0, 500); }

        if ($sets) {
            $params[] = $id;
            try {
                db()->prepare('UPDATE api_settings SET ' . implode(', ', $sets) . ' WHERE id = ?')->execute($params);
            } catch (Throwable $e) {
            }
        }

        self::logRotation($provider, $endpoint, $symbol, $timeframe, 'rate_limited', $responseMs, 0, $errorMessage);
        self::updateHealth($provider, 'rate_limited', $responseMs, $errorMessage);
    }

    public static function resetDailyUsageIfNeeded(): void
    {
        try {
            if (self::tableExists('api_settings') && self::columnExists('api_settings', 'reset_date') && self::columnExists('api_settings', 'used_today')) {
                db()->query("UPDATE api_settings SET used_today = 0, reset_date = CURDATE() WHERE reset_date IS NULL OR reset_date < CURDATE()");
            }
        } catch (Throwable $e) {
        }
    }

    private static function effectiveDailyLimit(string $provider, array $row): int
    {
        $provider = self::normalizeProvider($provider);
        $limit = (int)($row['daily_limit'] ?? 0);
        if ($limit > 0) return $limit;

        $defaults = [
            'fmp' => 250,
            'twelvedata' => 800,
            'finnhub' => 60,
            'alphavantage' => 25,
        ];
        return (int)($defaults[$provider] ?? 0);
    }

    private static function calculateCooldownMinutes(array $provider): int
    {
        $failCount = (int)($provider['fail_count'] ?? 0);

        if ($failCount >= 5) {
            return 60;
        }

        if ($failCount >= 3) {
            return 30;
        }

        return 10;
    }

    private static function ensureLogTables(): void
    {
        try {
            db()->exec("
                CREATE TABLE IF NOT EXISTS api_rotation_logs (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY,
                    api_setting_id BIGINT NULL,
                    provider VARCHAR(80) NOT NULL,
                    endpoint VARCHAR(120) NULL,
                    symbol VARCHAR(40) NULL,
                    timeframe VARCHAR(20) NULL,
                    status VARCHAR(40) NOT NULL DEFAULT 'info',
                    response_ms INT DEFAULT 0,
                    credits_used INT DEFAULT 0,
                    error_message TEXT NULL,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_api_rot_created (created_at),
                    INDEX idx_api_rot_provider_created (provider, created_at),
                    INDEX idx_api_rot_symbol_created (symbol, created_at),
                    INDEX idx_api_rot_status_created (status, created_at),
                    INDEX idx_api_rot_setting_created (api_setting_id, created_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            ");
            db()->exec("
                CREATE TABLE IF NOT EXISTS api_request_logs (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY,
                    api_setting_id BIGINT NULL,
                    provider VARCHAR(80) NOT NULL,
                    endpoint VARCHAR(120) NULL,
                    symbol VARCHAR(40) NULL,
                    timeframe VARCHAR(20) NULL,
                    status VARCHAR(40) NOT NULL DEFAULT 'info',
                    response_ms INT DEFAULT 0,
                    credits_used INT DEFAULT 0,
                    error_message TEXT NULL,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_api_req_created (created_at),
                    INDEX idx_api_req_provider_created (provider, created_at),
                    INDEX idx_api_req_symbol_created (symbol, created_at),
                    INDEX idx_api_req_status_created (status, created_at),
                    INDEX idx_api_req_setting_created (api_setting_id, created_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            ");
            foreach (['api_rotation_logs','api_request_logs'] as $table) {
                if (!self::columnExists($table, 'api_setting_id')) {
                    try { db()->exec("ALTER TABLE `$table` ADD COLUMN api_setting_id BIGINT NULL"); } catch (Throwable $e) {}
                }
            }
        } catch (Throwable $e) {
        }
    }

    private static function logRotation(array $provider, string $endpoint, ?string $symbol, ?string $timeframe, string $status, int $responseMs, int $creditsUsed, string $errorMessage): void
    {
        $status = strtolower(trim($status ?: 'info'));
        if (!self::logStatusEnabled($status)) {
            return;
        }

        self::ensureLogTables();

        $throttleSeconds = self::logThrottleSeconds($status);
        if ($throttleSeconds > 0 && self::tableExists('api_request_logs')) {
            try {
                $stmt = db()->prepare("
                    SELECT id
                    FROM api_request_logs
                    WHERE provider = ?
                      AND status = ?
                      AND COALESCE(endpoint, '') = ?
                      AND COALESCE(symbol, '') = ?
                      AND COALESCE(timeframe, '') = ?
                      AND COALESCE(api_setting_id, 0) = ?
                      AND LEFT(COALESCE(error_message, ''), 255) = ?
                      AND created_at >= DATE_SUB(NOW(), INTERVAL ? SECOND)
                    LIMIT 1
                ");
                $stmt->execute([
                    (string)($provider['provider'] ?? ''),
                    in_array($status, ['success', 'attempt'], true) ? 'success' : $status,
                    $endpoint,
                    $symbol ?? '',
                    $timeframe ?? '',
                    (int)($provider['id'] ?? 0),
                    substr((string)$errorMessage, 0, 255),
                    $throttleSeconds,
                ]);
                if ($stmt->fetchColumn()) {
                    return;
                }
            } catch (Throwable $e) {
            }
        }
        try {
            if (self::boolSetting('api_provider_mirror_rotation_logs_enabled', false) && self::tableExists('api_rotation_logs')) {
                db()->prepare("
                    INSERT INTO api_rotation_logs
                        (api_setting_id, provider, endpoint, symbol, timeframe, status, response_ms, credits_used, error_message, created_at)
                    VALUES
                        (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
                ")->execute([
                    (int)($provider['id'] ?? 0),
                    (string)($provider['provider'] ?? ''),
                    $endpoint,
                    $symbol,
                    $timeframe,
                    $status,
                    $responseMs,
                    $creditsUsed,
                    $errorMessage
                ]);
            }
        } catch (Throwable $e) {
        }

        try {
            if (self::tableExists('api_request_logs')) {
                db()->prepare("
                    INSERT INTO api_request_logs
                        (provider, endpoint, symbol, timeframe, status, response_ms, credits_used, error_message, created_at)
                    VALUES
                        (?, ?, ?, ?, ?, ?, ?, ?, NOW())
                ")->execute([
                    (string)($provider['provider'] ?? ''),
                    $endpoint,
                    $symbol,
                    $timeframe,
                    in_array($status, ['success', 'attempt'], true) ? 'success' : 'failed',
                    $responseMs,
                    $creditsUsed,
                    $errorMessage
                ]);
            }
        } catch (Throwable $e) {
        }
    }

    private static function updateHealth(array $provider, string $status, int $responseMs = 0, ?string $error = null): void
    {
        try {
            if (!self::tableExists('api_provider_health')) {
                return;
            }

            $successAt = $status === 'connected' ? 'NOW()' : 'last_success_at';
            $failedAt = $status !== 'connected' ? 'NOW()' : 'last_failed_at';

            db()->prepare("
                INSERT INTO api_provider_health
                    (api_setting_id, provider, status, response_ms, last_success_at, last_failed_at, last_error, created_at, updated_at)
                VALUES
                    (?, ?, ?, ?, " . ($status === 'connected' ? 'NOW()' : 'NULL') . ", " . ($status !== 'connected' ? 'NOW()' : 'NULL') . ", ?, NOW(), NOW())
                ON DUPLICATE KEY UPDATE
                    provider = VALUES(provider),
                    status = VALUES(status),
                    response_ms = VALUES(response_ms),
                    last_success_at = $successAt,
                    last_failed_at = $failedAt,
                    last_error = VALUES(last_error),
                    updated_at = NOW()
            ")->execute([
                (int)($provider['id'] ?? 0),
                (string)($provider['provider'] ?? ''),
                $status,
                $responseMs,
                $error
            ]);
        } catch (Throwable $e) {
        }
    }

    private static function tableExists(string $table): bool
    {
        try {
            $stmt = db()->prepare("SHOW TABLES LIKE ?");
            $stmt->execute([$table]);
            return (bool)$stmt->fetchColumn();
        } catch (Throwable $e) {
            return false;
        }
    }

    private static function columnExists(string $table, string $column): bool
    {
        try {
            $stmt = db()->prepare("SHOW COLUMNS FROM `$table` LIKE ?");
            $stmt->execute([$column]);
            return (bool)$stmt->fetchColumn();
        } catch (Throwable $e) {
            return false;
        }
    }
}
