<?php
/**
 * ApiCreditGuard.php
 * ------------------------------------------------------------
 * Central API-credit protection for production.
 *
 * Multi-key rules:
 * - Duplicate provider rows are valid when they contain different API keys.
 * - Usage, success, failure and cooldown are updated by api_settings.id, not by provider name.
 * - provider=FMP/TwelveData can therefore rotate through multiple API keys safely.
 * - Crypto candles/quotes use Binance public endpoints only.
 */

if (!function_exists('db')) {
    require_once __DIR__ . '/../config/database.php';
}

class ApiCreditGuard
{
    public const BUILD = 'API_CREDIT_GUARD_LAGOS_LOGS_FINNHUB_GOLDAPI_FIX_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',
            'binance_vision' => 'binance',
            'binance_spot' => 'binance',
            'binance_futures' => 'binance',
        ];
        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', 'binance_spot', 'binance_futures', 'binance_vision'],
        ];
        return $aliases[$provider] ?? [$provider];
    }

    public 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;
        }
    }

    public 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);
    }

    public 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;
        }
    }

    public 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;
        }
    }

    private static function apiColumns(): array
    {
        static $columns = null;
        if ($columns !== null) {
            return $columns;
        }
        $columns = [];
        if (!self::tableExists('api_settings')) {
            return $columns;
        }
        try {
            foreach (db()->query("SHOW COLUMNS FROM api_settings")->fetchAll(PDO::FETCH_ASSOC) ?: [] as $row) {
                if (!empty($row['Field'])) {
                    $columns[(string)$row['Field']] = true;
                }
            }
        } catch (Throwable $e) {
        }
        return $columns;
    }

    private static function hasApiColumn(string $column): bool
    {
        $columns = self::apiColumns();
        return isset($columns[$column]);
    }

    public static function resetDailyUsageIfNeeded(): void
    {
        if (!self::tableExists('api_settings') || !self::hasApiColumn('used_today')) {
            return;
        }

        try {
            if (self::hasApiColumn('reset_date')) {
                db()->query("UPDATE api_settings SET used_today = 0, reset_date = CURDATE() WHERE reset_date IS NULL OR reset_date < CURDATE()");
            }
        } catch (Throwable $e) {
        }
    }

    public static function guessAssetType(string $symbol, array $row = []): string
    {
        $asset = strtoupper(trim((string)($row['asset_type'] ?? '')));
        if ($asset !== '') {
            if ($asset === 'METALS') return 'METAL';
            if ($asset === 'INDICES' || $asset === 'INDEX') return 'INDEX';
            return $asset;
        }

        $symbol = strtoupper(str_replace(['/', '-', '_', ' '], '', trim($symbol)));
        if (preg_match('/^(BTC|ETH|SOL|BNB|XRP|ADA|DOGE|AVAX|LINK|LTC|BCH|DOT|MATIC|TRX|ATOM|NEAR|APT|OP|ARB)/', $symbol)) {
            return 'CRYPTO';
        }
        if (in_array($symbol, ['XAUUSD', 'XAGUSD', 'XPTUSD', 'XPDUSD'], true)) {
            return 'METAL';
        }
        if (preg_match('/^[A-Z]{6}$/', $symbol)) {
            return 'FOREX';
        }
        if (preg_match('/^(NAS|US30|DJI|SPX|US100|US500|GER40|UK100|JP225)/', $symbol)) {
            return 'INDEX';
        }
        return 'OTHER';
    }

    public static function isCrypto(string $symbol, array $row = []): bool
    {
        return self::guessAssetType($symbol, $row) === 'CRYPTO';
    }

    public static function symbolProviderSymbol(string $symbol, array $row = [], string $provider = ''): string
    {
        $symbol = strtoupper(str_replace(['/', '-', '_', ' '], '', trim($symbol)));
        $provider = self::normalizeProvider($provider);

        foreach (['provider_symbol', 'api_symbol', 'data_symbol'] as $key) {
            if (!empty($row[$key])) {
                return strtoupper(str_replace(['/', '-', '_', ' '], '', trim((string)$row[$key])));
            }
        }

        if ($provider === 'binance') {
            if (preg_match('/USD$/', $symbol)) {
                return preg_replace('/USD$/', 'USDT', $symbol);
            }
            if (!str_ends_with($symbol, 'USDT')) {
                return $symbol . 'USDT';
            }
        }

        return $symbol;
    }

    public static function twelvedataSymbol(string $symbol): string
    {
        $symbol = strtoupper(str_replace(['/', '-', '_', ' '], '', trim($symbol)));
        if (in_array($symbol, ['XAUUSD', 'XAGUSD', 'XPTUSD', 'XPDUSD'], true)) {
            return substr($symbol, 0, 3) . '/' . substr($symbol, 3, 3);
        }
        if (preg_match('/^[A-Z]{6}$/', $symbol)) {
            return substr($symbol, 0, 3) . '/' . substr($symbol, 3, 3);
        }
        return $symbol;
    }

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

        $safeKey = preg_replace('/[^a-z0-9_]/', '_', $provider);
        $defaults = [
            'fmp' => 250,
            'twelvedata' => 800,
            'finnhub' => 60,
            'alphavantage' => 25,
            'goldapiio' => 100,
            'metalsapi' => 1000,
            'metalpriceapi' => 1000,
        ];
        $default = (string)($defaults[$provider] ?? 0);
        return (int)self::setting('api_guard_' . $safeKey . '_daily_limit', $default);
    }

    public static function providerRows(string $provider, string $purpose = 'quote', int $estimatedCredits = 1, bool $eligibleOnly = true): array
    {
        $provider = self::normalizeProvider($provider);
        if ($provider === 'binance') {
            return [[
                'id' => 0,
                'provider' => 'binance',
                'api_key' => '',
                'is_active' => 1,
                'daily_limit' => 0,
                'used_today' => 0,
                '__normalized_provider' => 'binance',
                '__effective_daily_limit' => 0,
            ]];
        }

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

        self::resetDailyUsageIfNeeded();

        $aliases = self::providerAliases($provider);
        $placeholders = implode(',', array_fill(0, count($aliases), '?'));
        $params = array_map('strtolower', $aliases);
        $where = ["LOWER(provider) IN ($placeholders)"];

        if (self::hasApiColumn('is_active')) {
            $where[] = "COALESCE(is_active, 1) = 1";
        }

        $capabilityColumn = $purpose === 'candles' ? 'supports_candles' : 'supports_quote';
        if (self::hasApiColumn($capabilityColumn)) {
            $where[] = "($capabilityColumn = 1 OR $capabilityColumn IS NULL)";
        }

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

        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) {
            $row['__normalized_provider'] = self::normalizeProvider((string)($row['provider'] ?? $provider));
            $row['__effective_daily_limit'] = self::effectiveDailyLimit($row['__normalized_provider'], $row);
            $row['__usage_ratio'] = 0.0;

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

            if ($purpose === 'candles' && self::hasApiColumn('supports_candles') && empty($row['supports_candles'])) {
                continue;
            }
            if ($purpose === 'quote' && self::hasApiColumn('supports_quote') && empty($row['supports_quote'])) {
                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 providerRow(string $provider, string $purpose = 'quote', int $estimatedCredits = 1): array
    {
        $rows = self::providerRows($provider, $purpose, $estimatedCredits, true);
        return $rows[0] ?? [];
    }

    public static function getApiKey(string $provider): string
    {
        $row = self::providerRow($provider, 'quote', 1);
        return self::getApiKeyFromRow($row);
    }

    public static function getApiKeyFromRow(array $row): string
    {
        return trim((string)($row['api_key'] ?? ''));
    }

    public static function providerSupports(string $provider, string $purpose): bool
    {
        $provider = self::normalizeProvider($provider);
        if ($provider === 'binance') return true;
        return (bool)self::providerRow($provider, $purpose, 1);
    }

    public static function isProviderInCooldown(string $provider): array
    {
        $provider = self::normalizeProvider($provider);
        if ($provider === 'binance') {
            return ['blocked' => false, 'reason' => 'Binance public endpoint.'];
        }

        $rows = self::providerRows($provider, 'quote', 1, false);
        foreach ($rows as $row) {
            $cooldown = trim((string)($row['cooldown_until'] ?? ''));
            if ($cooldown !== '') {
                $ts = strtotime($cooldown);
                if ($ts && $ts > time()) {
                    continue;
                }
            }
            return ['blocked' => false, 'reason' => 'At least one ' . strtoupper($provider) . ' API key is available.'];
        }

        return ['blocked' => true, 'reason' => strtoupper($provider) . ' has no available active API key row.'];
    }

    public static function usageLimitStatusForRow(array $row, int $estimatedCredits = 1): array
    {
        $provider = self::normalizeProvider((string)($row['provider'] ?? ''));
        if ($provider === 'binance') {
            return ['blocked' => false, 'reason' => 'Binance public endpoint.'];
        }

        $used = (int)($row['used_today'] ?? 0);
        $limit = self::effectiveDailyLimit($provider, $row);
        if ($limit <= 0) {
            return ['blocked' => false, 'reason' => 'No app-side daily limit set for this API key row.'];
        }

        if (($used + $estimatedCredits) > $limit) {
            return [
                'blocked' => true,
                'reason' => strtoupper($provider) . ' row #' . (int)($row['id'] ?? 0) . " credit guard: used {$used}/{$limit}, estimated {$estimatedCredits}.",
            ];
        }

        return ['blocked' => false, 'reason' => strtoupper($provider) . ' row #' . (int)($row['id'] ?? 0) . " usage ok: {$used}/{$limit}."];
    }

    public static function usageLimitStatus(string $provider, int $estimatedCredits = 1): array
    {
        $row = self::providerRow($provider, 'quote', $estimatedCredits);
        if (!$row) {
            return ['blocked' => true, 'reason' => strtoupper(self::normalizeProvider($provider)) . ' has no available API key row.'];
        }
        return self::usageLimitStatusForRow($row, $estimatedCredits);
    }

    public static function shouldUseProviderRow(array $row, string $purpose, int $estimatedCredits = 1): array
    {
        $provider = self::normalizeProvider((string)($row['provider'] ?? ''));
        if ($provider === 'binance') {
            return ['allowed' => true, 'reason' => 'Binance public endpoint.', 'row' => $row];
        }
        if (!$row) {
            return ['allowed' => false, 'reason' => 'No provider row supplied.'];
        }
        if (isset($row['is_active']) && !(int)$row['is_active']) {
            return ['allowed' => false, 'reason' => strtoupper($provider) . ' row #' . (int)($row['id'] ?? 0) . ' is inactive.', 'row' => $row];
        }
        if ($purpose === 'candles' && array_key_exists('supports_candles', $row) && !(int)$row['supports_candles']) {
            return ['allowed' => false, 'reason' => strtoupper($provider) . ' row #' . (int)($row['id'] ?? 0) . ' does not support candles.', 'row' => $row];
        }
        if ($purpose === 'quote' && array_key_exists('supports_quote', $row) && !(int)$row['supports_quote']) {
            return ['allowed' => false, 'reason' => strtoupper($provider) . ' row #' . (int)($row['id'] ?? 0) . ' does not support quotes.', 'row' => $row];
        }
        $cooldown = trim((string)($row['cooldown_until'] ?? ''));
        if ($cooldown !== '') {
            $ts = strtotime($cooldown);
            if ($ts && $ts > time()) {
                return ['allowed' => false, 'reason' => strtoupper($provider) . ' row #' . (int)($row['id'] ?? 0) . ' is in cooldown until ' . $cooldown . '.', 'row' => $row];
            }
        }
        $usage = self::usageLimitStatusForRow($row, $estimatedCredits);
        if (!empty($usage['blocked'])) {
            return ['allowed' => false, 'reason' => $usage['reason'], 'row' => $row];
        }
        return ['allowed' => true, 'reason' => $usage['reason'] ?? 'Provider row allowed.', 'row' => $row];
    }

    public static function shouldUseProvider(string $provider, string $purpose, int $estimatedCredits = 1): array
    {
        $provider = self::normalizeProvider($provider);
        if ($provider === 'binance') {
            return ['allowed' => true, 'reason' => 'Binance public endpoint.', 'row' => ['provider' => 'binance', 'id' => 0]];
        }

        $row = self::providerRow($provider, $purpose, $estimatedCredits);
        if (!$row) {
            return ['allowed' => false, 'reason' => strtoupper($provider) . " is not active, does not support {$purpose}, is in cooldown, or has reached its daily limit."];
        }

        return self::shouldUseProviderRow($row, $purpose, $estimatedCredits);
    }

    public static function shouldSyncCandles(string $symbol, string $provider, string $assetType, string $mode, bool $allowPaidCandles = false): array
    {
        $provider = self::normalizeProvider($provider);
        $assetType = strtoupper(trim($assetType));

        if ($assetType === 'CRYPTO') {
            return $provider === 'binance'
                ? ['allowed' => true, 'reason' => 'Crypto candles are routed to Binance only.']
                : ['allowed' => false, 'reason' => 'Crypto candle sync is restricted to Binance only.'];
        }

        if ($assetType === 'METAL' && $provider === 'fmp' && !self::boolSetting('candle_sync_fmp_metals_enabled', false)) {
            return ['allowed' => false, 'reason' => 'FMP metals/commodity candle sync is disabled by default. Use TwelveData/metals providers for XAUUSD/XAGUSD, or enable candle_sync_fmp_metals_enabled only if your FMP subscription supports commodities.'];
        }

        if ($provider === 'twelvedata' && !$allowPaidCandles && !self::boolSetting('api_guard_twelvedata_candles_enabled', false)) {
            return ['allowed' => false, 'reason' => 'TwelveData candle sync is disabled unless alert-aware paid candles are explicitly allowed.'];
        }

        if (in_array($provider, ['twelvedata', 'fmp'], true) && !$allowPaidCandles && !self::boolSetting('candle_sync_allow_paid_providers', false)) {
            return ['allowed' => false, 'reason' => 'Paid-provider candle sync is disabled unless explicitly enabled.'];
        }

        return self::shouldUseProvider($provider, 'candles', 1);
    }

    public static function resolveCandleProvider(string $symbol, array $symbolRow = [], string $override = '', bool $allowPaidCandles = false): string
    {
        $assetType = self::guessAssetType($symbol, $symbolRow);
        $override = self::normalizeProvider($override);

        if ($assetType === 'CRYPTO') {
            return 'binance';
        }

        if ($override !== '' && $override !== 'auto') {
            return $override;
        }

        if ($allowPaidCandles || self::boolSetting('candle_sync_allow_paid_providers', false)) {
            if ($assetType === 'METAL' && !self::boolSetting('candle_sync_fmp_metals_enabled', false)) {
                if (self::providerSupports('twelvedata', 'candles') && self::boolSetting('api_guard_twelvedata_candles_enabled', true)) {
                    return 'twelvedata';
                }
                return 'blocked';
            }

            if (self::providerSupports('fmp', 'candles')) {
                return 'fmp';
            }
            if (self::providerSupports('twelvedata', 'candles') && self::boolSetting('api_guard_twelvedata_candles_enabled', true)) {
                return 'twelvedata';
            }
        }

        return 'blocked';
    }

    public static function recordUsageForRow(array $row, int $credits = 1, string $endpoint = 'api_request', ?string $symbol = null, ?string $timeframe = null): void
    {
        $id = (int)($row['id'] ?? 0);
        $provider = self::normalizeProvider((string)($row['provider'] ?? ''));
        if ($credits <= 0) {
            return;
        }

        if ($provider !== 'binance' && $id > 0 && self::tableExists('api_settings')) {
            $sets = [];
            $params = [];
            if (self::hasApiColumn('used_today')) { $sets[] = 'used_today = COALESCE(used_today,0) + ?'; $params[] = $credits; }
            if (self::hasApiColumn('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) {
                }
            }
        }

        /*
         * Important: usage counters are still updated above, but attempt rows
         * are NOT logged by default. A single quote can try several provider
         * rows before it succeeds; logging every attempt was creating hundreds
         * or thousands of rows per hour from the background alert worker.
         */
        if (self::boolSetting('api_provider_log_attempts_enabled', false)) {
            self::logProviderEvent($row, $endpoint, $symbol, $timeframe, 'attempt', $credits, 0, '');
        }
    }

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

        if ($provider !== 'binance' && $id > 0 && self::tableExists('api_settings')) {
            $sets = [];
            if (self::hasApiColumn('fail_count')) $sets[] = 'fail_count = 0';
            if (self::hasApiColumn('cooldown_until')) $sets[] = 'cooldown_until = NULL';
            if (self::hasApiColumn('last_error')) $sets[] = 'last_error = NULL';
            if (self::hasApiColumn('last_used_at')) $sets[] = 'last_used_at = NOW()';
            if ($sets) {
                try {
                    db()->prepare('UPDATE api_settings SET ' . implode(', ', $sets) . ' WHERE id = ?')->execute([$id]);
                } catch (Throwable $e) {
                }
            }
        }

        self::logProviderEvent($row, $endpoint, $symbol, $timeframe, 'success', $creditsUsed, $responseMs, '');
    }

    public static function recordFailureForRow(array $row, string $error, string $endpoint = 'api_request', ?string $symbol = null, ?string $timeframe = null, int $responseMs = 0): void
    {
        $id = (int)($row['id'] ?? 0);
        $provider = self::normalizeProvider((string)($row['provider'] ?? ''));

        if ($provider !== 'binance' && $id > 0 && self::tableExists('api_settings')) {
            $currentFailCount = (int)($row['fail_count'] ?? 0) + 1;
            $threshold = max(1, (int)self::setting('api_guard_fail_count_cooldown_threshold', '3'));
            $cooldownMinutes = max(5, (int)self::setting('api_guard_provider_cooldown_minutes', '60'));

            $sets = [];
            $params = [];
            if (self::hasApiColumn('fail_count')) { $sets[] = 'fail_count = COALESCE(fail_count,0) + 1'; }
            if (self::hasApiColumn('last_error')) { $sets[] = 'last_error = ?'; $params[] = substr($error, 0, 500); }
            if ($currentFailCount >= $threshold && self::hasApiColumn('cooldown_until')) {
                $sets[] = 'cooldown_until = DATE_ADD(NOW(), INTERVAL ? MINUTE)';
                $params[] = $cooldownMinutes;
            }
            if (self::hasApiColumn('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) {
                }
            }
        }

        self::logProviderEvent($row, $endpoint, $symbol, $timeframe, 'failed', 0, $responseMs, $error);
    }

    public static function recordRateLimitedForRow(array $row, string $error, int $cooldownMinutes = 60, string $endpoint = 'api_request', ?string $symbol = null, ?string $timeframe = null, int $responseMs = 0): void
    {
        $id = (int)($row['id'] ?? 0);
        $provider = self::normalizeProvider((string)($row['provider'] ?? ''));
        if ($provider !== 'binance' && $id > 0 && self::tableExists('api_settings')) {
            $cooldownMinutes = max(15, min(1440, $cooldownMinutes));
            $sets = [];
            $params = [];
            if (self::hasApiColumn('fail_count')) $sets[] = 'fail_count = COALESCE(fail_count,0) + 1';
            if (self::hasApiColumn('last_error')) { $sets[] = 'last_error = ?'; $params[] = substr($error, 0, 500); }
            if (self::hasApiColumn('cooldown_until')) { $sets[] = 'cooldown_until = DATE_ADD(NOW(), INTERVAL ? MINUTE)'; $params[] = $cooldownMinutes; }
            if (self::hasApiColumn('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) {
                }
            }
        }

        self::logProviderEvent($row, $endpoint, $symbol, $timeframe, 'rate_limited', 0, $responseMs, $error);
    }

    public static function recordUsage(string $provider, int $credits = 1, string $endpoint = 'api_request', ?string $symbol = null, ?string $timeframe = null): void
    {
        $row = self::providerRow($provider, 'quote', $credits);
        if (!$row) $row = self::providerRow($provider, 'candles', $credits);
        if ($row) self::recordUsageForRow($row, $credits, $endpoint, $symbol, $timeframe);
    }

    public static function recordSuccess(string $provider, string $endpoint = 'api_request', ?string $symbol = null, ?string $timeframe = null, int $responseMs = 0): void
    {
        $row = self::providerRow($provider, 'quote', 1);
        if (!$row) $row = self::providerRow($provider, 'candles', 1);
        if ($row) self::recordSuccessForRow($row, $endpoint, $symbol, $timeframe, $responseMs);
    }

    public static function recordFailure(string $provider, string $error, string $endpoint = 'api_request', ?string $symbol = null, ?string $timeframe = null, int $responseMs = 0): void
    {
        $row = self::providerRow($provider, 'quote', 1);
        if (!$row) $row = self::providerRow($provider, 'candles', 1);
        if ($row) self::recordFailureForRow($row, $error, $endpoint, $symbol, $timeframe, $responseMs);
    }


    private static function providerLogStatusEnabled(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 providerLogThrottleSeconds(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;
    }

    private static function shouldInsertProviderLog(array $payload): bool
    {
        $status = strtolower(trim((string)($payload['status'] ?? 'info')));

        if (!self::providerLogStatusEnabled($status)) {
            return false;
        }

        $minSeconds = self::providerLogThrottleSeconds($status);
        if ($minSeconds <= 0 || !self::tableExists('api_request_logs')) {
            return true;
        }

        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)($payload['provider'] ?? ''),
                (string)($payload['status'] ?? ''),
                (string)($payload['endpoint'] ?? ''),
                (string)($payload['symbol'] ?? ''),
                (string)($payload['timeframe'] ?? ''),
                (int)($payload['api_setting_id'] ?? 0),
                substr((string)($payload['error_message'] ?? ''), 0, 255),
                $minSeconds,
            ]);

            return !$stmt->fetchColumn();
        } catch (Throwable $e) {
            return true;
        }
    }

    public static function logProviderEvent(array $row, string $endpoint, ?string $symbol, ?string $timeframe, string $status, int $creditsUsed = 0, int $responseMs = 0, string $errorMessage = ''): void
    {
        try {
            self::ensureProviderLogTables();

            $provider = self::normalizeProvider((string)($row['provider'] ?? ''));
            if ($provider === '') {
                $provider = (string)($row['provider'] ?? 'provider');
            }

            $payload = [
                'api_setting_id' => (int)($row['id'] ?? 0),
                'provider' => $provider,
                'endpoint' => substr($endpoint ?: 'api_request', 0, 120),
                'symbol' => $symbol !== null ? strtoupper(trim($symbol)) : null,
                'timeframe' => $timeframe !== null ? strtoupper(trim($timeframe)) : null,
                'status' => substr($status ?: 'info', 0, 40),
                'response_ms' => max(0, (int)$responseMs),
                'credits_used' => max(0, (int)$creditsUsed),
                'error_message' => substr($errorMessage, 0, 1000),
            ];

            if (!self::shouldInsertProviderLog($payload)) {
                return;
            }

            self::insertProviderLogRow('api_request_logs', $payload);

            /*
             * api_provider_usage_logs.php and api_monitor.php read api_request_logs.
             * Mirroring every row into api_rotation_logs doubled table growth.
             * Keep mirroring optional for backward compatibility only.
             */
            if (self::boolSetting('api_provider_mirror_rotation_logs_enabled', false)) {
                self::insertProviderLogRow('api_rotation_logs', $payload);
            }
        } catch (Throwable $e) {
        }
    }

    private static function ensureProviderLogTables(): void
    {
        try {
            db()->exec("\n                CREATE TABLE IF NOT EXISTS api_request_logs (\n                    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n                    api_setting_id BIGINT NULL,\n                    provider VARCHAR(80) NOT NULL,\n                    endpoint VARCHAR(120) NULL,\n                    symbol VARCHAR(40) NULL,\n                    timeframe VARCHAR(20) NULL,\n                    status VARCHAR(40) NOT NULL DEFAULT 'info',\n                    response_ms INT DEFAULT 0,\n                    credits_used INT DEFAULT 0,\n                    error_message TEXT NULL,\n                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n                    INDEX idx_api_req_created (created_at),\n                    INDEX idx_api_req_provider_created (provider, created_at),\n                    INDEX idx_api_req_symbol_created (symbol, created_at),\n                    INDEX idx_api_req_status_created (status, created_at),\n                    INDEX idx_api_req_setting_created (api_setting_id, created_at)\n                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4\n            ");

            db()->exec("\n                CREATE TABLE IF NOT EXISTS api_rotation_logs (\n                    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n                    api_setting_id BIGINT NULL,\n                    provider VARCHAR(80) NOT NULL,\n                    endpoint VARCHAR(120) NULL,\n                    symbol VARCHAR(40) NULL,\n                    timeframe VARCHAR(20) NULL,\n                    status VARCHAR(40) NOT NULL DEFAULT 'info',\n                    response_ms INT DEFAULT 0,\n                    credits_used INT DEFAULT 0,\n                    error_message TEXT NULL,\n                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n                    INDEX idx_api_rot_created (created_at),\n                    INDEX idx_api_rot_provider_created (provider, created_at),\n                    INDEX idx_api_rot_symbol_created (symbol, created_at),\n                    INDEX idx_api_rot_status_created (status, created_at),\n                    INDEX idx_api_rot_setting_created (api_setting_id, created_at)\n                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4\n            ");

            foreach (['api_request_logs', 'api_rotation_logs'] as $table) {
                $columns = [
                    'api_setting_id' => 'BIGINT NULL',
                    'endpoint' => 'VARCHAR(120) NULL',
                    'symbol' => 'VARCHAR(40) NULL',
                    'timeframe' => 'VARCHAR(20) NULL',
                    'response_ms' => 'INT DEFAULT 0',
                    'credits_used' => 'INT DEFAULT 0',
                    'error_message' => 'TEXT NULL',
                    'created_at' => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
                ];
                foreach ($columns as $column => $definition) {
                    if (!self::columnExists($table, $column)) {
                        try { db()->exec("ALTER TABLE `$table` ADD COLUMN `$column` $definition"); } catch (Throwable $e) {}
                    }
                }
            }
        } catch (Throwable $e) {
        }
    }

    private static function insertProviderLogRow(string $table, array $payload): void
    {
        if (!self::tableExists($table)) {
            return;
        }

        $columns = [];
        $marks = [];
        $params = [];

        foreach ($payload as $column => $value) {
            if (self::columnExists($table, $column)) {
                $columns[] = '`' . $column . '`';
                $marks[] = '?';
                $params[] = $value;
            }
        }

        if (self::columnExists($table, 'created_at')) {
            $columns[] = '`created_at`';
            $marks[] = 'NOW()';
        }

        if (!$columns) {
            return;
        }

        try {
            db()->prepare('INSERT INTO `' . $table . '` (' . implode(',', $columns) . ') VALUES (' . implode(',', $marks) . ')')->execute($params);
        } catch (Throwable $e) {
        }
    }

    public static function httpJson(string $url, int $timeout = 12): array
    {
        $context = stream_context_create([
            'http' => [
                'method' => 'GET',
                'timeout' => $timeout,
                'ignore_errors' => true,
                'header' => "User-Agent: ITA-ApiCreditGuard/2.0\r\nAccept: application/json\r\n",
            ],
            'ssl' => [
                'verify_peer' => true,
                'verify_peer_name' => true,
            ],
        ]);

        $body = @file_get_contents($url, false, $context);
        if ($body === false || trim($body) === '') {
            return ['ok' => false, 'error' => 'Empty HTTP response.'];
        }
        $json = json_decode($body, true);
        if (!is_array($json)) {
            return ['ok' => false, 'error' => 'Invalid JSON response.', 'raw' => substr($body, 0, 500)];
        }
        return ['ok' => true, 'data' => $json];
    }
}
