<?php

// services/CandleService.php

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

if (file_exists(__DIR__ . '/SystemConfigService.php')) {
    require_once __DIR__ . '/SystemConfigService.php';
}

if (file_exists(__DIR__ . '/MarketDataService.php')) {
    require_once __DIR__ . '/MarketDataService.php';
}

class CandleService
{
    public static function ensureTable(): void
    {
        db()->exec("CREATE TABLE IF NOT EXISTS candles (
            id INT AUTO_INCREMENT PRIMARY KEY,
            symbol VARCHAR(30) NOT NULL,
            timeframe VARCHAR(20) NOT NULL,
            open_time DATETIME NOT NULL,
            open_price DECIMAL(20,8) NOT NULL,
            high_price DECIMAL(20,8) NOT NULL,
            low_price DECIMAL(20,8) NOT NULL,
            close_price DECIMAL(20,8) NOT NULL,
            volume DECIMAL(20,8) DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_symbol_tf_time (symbol, timeframe, open_time),
            INDEX idx_symbol_tf (symbol, timeframe),
            INDEX idx_open_time (open_time)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    public static function insertCandle($symbol, $timeframe, $openTime, $open, $high, $low, $close, $volume = null)
    {
        self::ensureTable();

        $normalized = self::normalizeCandle([
            'symbol' => $symbol,
            'timeframe' => $timeframe,
            'open_time' => $openTime,
            'open' => $open,
            'high' => $high,
            'low' => $low,
            'close' => $close,
            'volume' => $volume,
        ], $symbol, $timeframe);

        if (!$normalized['ok']) {
            return false;
        }

        return self::upsertNormalizedCandle($normalized['candle'])['ok'];
    }

    public static function upsertCandle($symbol, $timeframe, array $row): array
    {
        self::ensureTable();

        $normalized = self::normalizeCandle($row, $symbol, $timeframe);

        if (!$normalized['ok']) {
            return [
                'ok' => false,
                'message' => $normalized['message'],
                'row' => $row,
            ];
        }

        return self::upsertNormalizedCandle($normalized['candle']);
    }

    public static function bulkUpsertCandles($symbol, $timeframe, array $candles): array
    {
        self::ensureTable();

        $symbol = self::normalizeSymbol($symbol);
        $timeframe = self::normalizeTimeframe($timeframe);

        $result = [
            'ok' => true,
            'symbol' => $symbol,
            'timeframe' => $timeframe,
            'fetched' => count($candles),
            'saved' => 0,
            'inserted' => 0,
            'updated' => 0,
            'unchanged' => 0,
            'skipped' => 0,
            'errors' => [],
            'first_open_time' => null,
            'last_open_time' => null,
            'sample_times' => [],
        ];

        if (!$candles) {
            $result['ok'] = false;
            $result['message'] = 'No candles were provided for saving.';
            return $result;
        }

        try {
            db()->beginTransaction();

            foreach ($candles as $row) {
                $normalized = self::normalizeCandle($row, $symbol, $timeframe);

                if (!$normalized['ok']) {
                    $result['skipped']++;
                    if (count($result['errors']) < 10) {
                        $result['errors'][] = $normalized['message'];
                    }
                    continue;
                }

                $saved = self::upsertNormalizedCandle($normalized['candle']);

                if (!$saved['ok']) {
                    $result['skipped']++;
                    if (count($result['errors']) < 10) {
                        $result['errors'][] = $saved['message'];
                    }
                    continue;
                }

                $result['saved']++;

                if ($saved['status'] === 'inserted') {
                    $result['inserted']++;
                } elseif ($saved['status'] === 'updated') {
                    $result['updated']++;
                } else {
                    $result['unchanged']++;
                }

                $openTime = $normalized['candle']['open_time'];

                if ($result['first_open_time'] === null || $openTime < $result['first_open_time']) {
                    $result['first_open_time'] = $openTime;
                }

                if ($result['last_open_time'] === null || $openTime > $result['last_open_time']) {
                    $result['last_open_time'] = $openTime;
                }

                if (count($result['sample_times']) < 5) {
                    $result['sample_times'][] = $openTime;
                }
            }

            db()->commit();
        } catch (Throwable $e) {
            if (db()->inTransaction()) {
                db()->rollBack();
            }

            return [
                'ok' => false,
                'message' => 'Bulk candle save failed: ' . $e->getMessage(),
                'fetched' => count($candles),
                'saved' => $result['saved'],
                'skipped' => $result['skipped'],
                'errors' => $result['errors'],
            ];
        }

        if ($result['saved'] <= 0) {
            $result['ok'] = false;
            $result['message'] = 'No candles were saved. Check skipped row errors.';
        } else {
            $result['message'] = "Saved/updated {$result['saved']} candle(s). Inserted: {$result['inserted']}. Updated: {$result['updated']}. Unchanged: {$result['unchanged']}. Skipped: {$result['skipped']}.";
        }

        return $result;
    }

    public static function syncFromApi($symbol, $timeframe, $limit = 300, $provider = 'twelvedata'): array
    {
        self::ensureTable();

        $symbol = self::normalizeSymbol($symbol);
        $timeframe = self::normalizeTimeframe($timeframe);
        $limit = max(10, min(5000, (int)$limit));
        $apiInterval = self::apiInterval($timeframe);
        $provider = strtolower(trim((string)$provider));
        $provider = $provider ?: 'twelvedata';
        $providerSymbol = self::providerSymbol($symbol, $provider);

        if (!class_exists('MarketDataService') || !method_exists('MarketDataService', 'fetchCandles')) {
            return [
                'ok' => false,
                'message' => 'MarketDataService::fetchCandles() is not available.',
                'symbol' => $symbol,
                'timeframe' => $timeframe,
                'api_interval' => $apiInterval,
                'provider_symbol' => $providerSymbol,
            ];
        }

        ob_start();
        $candles = MarketDataService::fetchCandles($symbol, $apiInterval, $limit, $provider);
        $debug = trim(ob_get_clean());

        if (!is_array($candles)) {
            return [
                'ok' => false,
                'message' => 'MarketDataService returned an invalid response.',
                'debug' => $debug,
                'symbol' => $symbol,
                'timeframe' => $timeframe,
                'api_interval' => $apiInterval,
                'provider_symbol' => $providerSymbol,
            ];
        }

        $save = self::bulkUpsertCandles($symbol, $timeframe, $candles);
        $save['api_interval'] = $apiInterval;
        $save['provider'] = $provider;
        $save['provider_symbol'] = $providerSymbol;
        $save['requested_limit'] = $limit;
        $save['api_debug'] = $debug;
        $save['market_service_returned'] = count($candles);
        $save['db_schema_compatibility'] = 'No updated_at column required.';

        if (count($candles) <= 2) {
            $save['warning'] = 'Only ' . count($candles) . ' candle(s) were returned by the API/provider. The saving loop is not capped; check provider symbol, interval, API plan/rate limit, or provider response.';
        }

        return $save;
    }

    public static function getCandles($symbol, $timeframe, $limit = 100)
    {
        self::ensureTable();

        $limit = max(1, min(5000, (int)$limit));

        $sql = "SELECT * FROM candles
                WHERE symbol = ?
                AND timeframe = ?
                ORDER BY open_time DESC
                LIMIT {$limit}";

        $stmt = db()->prepare($sql);
        $stmt->execute([self::normalizeSymbol($symbol), self::normalizeTimeframe($timeframe)]);

        return array_reverse($stmt->fetchAll(PDO::FETCH_ASSOC) ?: []);
    }

    public static function getStats($symbol, $timeframe): array
    {
        self::ensureTable();

        $stmt = db()->prepare("
            SELECT COUNT(*) total, MIN(open_time) first_candle, MAX(open_time) last_candle
            FROM candles
            WHERE symbol = ? AND timeframe = ?
        ");
        $stmt->execute([self::normalizeSymbol($symbol), self::normalizeTimeframe($timeframe)]);

        return $stmt->fetch(PDO::FETCH_ASSOC) ?: ['total' => 0, 'first_candle' => null, 'last_candle' => null];
    }

    public static function normalizeCandle(array $row, $symbol, $timeframe): array
    {
        $symbol = self::normalizeSymbol($row['symbol'] ?? $symbol);
        $timeframe = self::normalizeTimeframe($row['timeframe'] ?? $timeframe);

        $openTime = self::normalizeTime($row['open_time'] ?? $row['datetime'] ?? $row['time'] ?? $row['date'] ?? $row['timestamp'] ?? null);
        $open = self::num($row['open'] ?? $row['open_price'] ?? null);
        $high = self::num($row['high'] ?? $row['high_price'] ?? null);
        $low = self::num($row['low'] ?? $row['low_price'] ?? null);
        $close = self::num($row['close'] ?? $row['close_price'] ?? null);
        $volume = self::num($row['volume'] ?? $row['vol'] ?? 0) ?? 0;

        if ($symbol === '' || $timeframe === '' || !$openTime) {
            return ['ok' => false, 'message' => 'Missing symbol, timeframe or candle open time.'];
        }

        if ($open === null || $high === null || $low === null || $close === null) {
            return ['ok' => false, 'message' => 'Missing OHLC values for candle ' . ($openTime ?: 'unknown time') . '.'];
        }

        if ($high < max($open, $close) || $low > min($open, $close) || $high < $low) {
            return ['ok' => false, 'message' => 'Invalid OHLC at ' . $openTime . '. High/Low does not match candle body.'];
        }

        return [
            'ok' => true,
            'candle' => [
                'symbol' => $symbol,
                'timeframe' => $timeframe,
                'open_time' => $openTime,
                'open_price' => $open,
                'high_price' => $high,
                'low_price' => $low,
                'close_price' => $close,
                'volume' => $volume,
            ],
        ];
    }

    private static function upsertNormalizedCandle(array $candle): array
    {
        try {
            $stmt = db()->prepare("
                INSERT INTO candles
                    (symbol, timeframe, open_time, open_price, high_price, low_price, close_price, volume)
                VALUES
                    (?, ?, ?, ?, ?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE
                    open_price = VALUES(open_price),
                    high_price = VALUES(high_price),
                    low_price = VALUES(low_price),
                    close_price = VALUES(close_price),
                    volume = VALUES(volume)
            ");

            $stmt->execute([
                $candle['symbol'],
                $candle['timeframe'],
                $candle['open_time'],
                $candle['open_price'],
                $candle['high_price'],
                $candle['low_price'],
                $candle['close_price'],
                $candle['volume'],
            ]);

            $rowCount = (int)$stmt->rowCount();

            return [
                'ok' => true,
                'status' => $rowCount === 1 ? 'inserted' : ($rowCount === 2 ? 'updated' : 'unchanged'),
                'row_count' => $rowCount,
            ];
        } catch (Throwable $e) {
            return [
                'ok' => false,
                'message' => 'DB insert failed for ' . ($candle['open_time'] ?? 'unknown time') . ': ' . $e->getMessage(),
            ];
        }
    }

    public static function normalizeSymbol($symbol): string
    {
        return strtoupper(str_replace(['/', '-', '_', ' '], '', trim((string)$symbol)));
    }

    public static function normalizeTimeframe($timeframe): string
    {
        $tf = strtoupper(trim((string)$timeframe));

        $map = [
            'M1' => '1M',
            'M3' => '3M',
            'M5' => '5M',
            'M15' => '15M',
            'M30' => '30M',
            'H1' => '1H',
            'H2' => '2H',
            'H4' => '4H',
            'D1' => '1D',
            'W1' => '1W',
        ];

        return $map[$tf] ?? $tf;
    }

    public static function apiInterval($timeframe): string
    {
        $tf = self::normalizeTimeframe($timeframe);

        try {
            if (class_exists('SystemConfigService') && method_exists('SystemConfigService', 'getApiInterval')) {
                $apiInterval = trim((string)SystemConfigService::getApiInterval($tf));
                if ($apiInterval !== '') {
                    return self::normalizeApiInterval($apiInterval);
                }
            }
        } catch (Throwable $e) {
        }

        return self::normalizeApiInterval($tf);
    }

    public static function normalizeApiInterval($interval): string
    {
        $raw = strtolower(trim((string)$interval));
        $raw = str_replace([' ', '_'], '', $raw);

        $map = [
            '1m' => '1min',
            'm1' => '1min',
            '1min' => '1min',
            '3m' => '3min',
            'm3' => '3min',
            '3min' => '3min',
            '5m' => '5min',
            'm5' => '5min',
            '5min' => '5min',
            '15m' => '15min',
            'm15' => '15min',
            '15min' => '15min',
            '30m' => '30min',
            'm30' => '30min',
            '30min' => '30min',
            '1h' => '1h',
            'h1' => '1h',
            '2h' => '2h',
            'h2' => '2h',
            '4h' => '4h',
            'h4' => '4h',
            '1d' => '1day',
            'd1' => '1day',
            '1day' => '1day',
            '1w' => '1week',
            'w1' => '1week',
            '1week' => '1week',
        ];

        return $map[$raw] ?? $raw;
    }

    public static function providerSymbol($symbol, string $provider = 'twelvedata'): string
    {
        $symbol = self::normalizeSymbol($symbol);
        $provider = strtolower(trim($provider));

        if ($provider === 'binance') {
            if (substr($symbol, -4) === 'USDT') {
                return $symbol;
            }

            if (substr($symbol, -3) === 'USD') {
                return substr($symbol, 0, -3) . 'USDT';
            }

            return $symbol;
        }

        try {
            if (class_exists('SystemConfigService') && method_exists('SystemConfigService', 'getProviderSymbol') && $provider !== 'fmp') {
                $providerSymbol = trim((string)SystemConfigService::getProviderSymbol($symbol, 'twelvedata'));
                if ($providerSymbol !== '') {
                    return $providerSymbol;
                }
            }
        } catch (Throwable $e) {
        }

        if ($provider === 'fmp') {
            $known = [
                'XAUUSD' => 'GCUSD',
                'XAGUSD' => 'SIUSD',
                'XPTUSD' => 'PLUSD',
                'XPDUSD' => 'PAUSD',
            ];

            if (isset($known[$symbol])) {
                return $known[$symbol];
            }

            return $symbol;
        }

        if (preg_match('/^[A-Z]{6}$/', $symbol)) {
            return substr($symbol, 0, 3) . '/' . substr($symbol, 3, 3);
        }

        return $symbol;
    }


    public static function normalizeTime($value): ?string
    {
        if ($value === null || $value === '') {
            return null;
        }

        $value = trim((string)$value);

        if ($value === '') {
            return null;
        }

        if (is_numeric($value)) {
            $num = (int)$value;

            if ($num > 20000000000) {
                $num = (int)floor($num / 1000);
            }

            return gmdate('Y-m-d H:i:s', $num);
        }

        /*
         * IMPORTANT:
         * Binance and most provider candle APIs return UTC candle open time.
         * If a value already looks like "YYYY-MM-DD HH:MM:SS" with no timezone,
         * keep it exactly as UTC text instead of passing it through strtotime().
         *
         * Passing it through strtotime() on a Lagos server converts it as local time
         * and shifts the candle one hour backward.
         */
        if (preg_match('/^\d{4}-\d{2}-\d{2}[ T]\d{2}:\d{2}:\d{2}$/', $value)) {
            return str_replace('T', ' ', $value);
        }

        if (preg_match('/^\d{4}-\d{2}-\d{2}[ T]\d{2}:\d{2}$/', $value)) {
            return str_replace('T', ' ', $value) . ':00';
        }

        try {
            $dt = new DateTimeImmutable($value);
            return $dt->setTimezone(new DateTimeZone('UTC'))->format('Y-m-d H:i:s');
        } catch (Throwable $e) {
            $ts = strtotime($value);

            if (!$ts) {
                return null;
            }

            return gmdate('Y-m-d H:i:s', $ts);
        }
    }


    public static function num($value): ?float
    {
        if ($value === null || $value === '') {
            return null;
        }

        $value = trim((string)$value);
        $value = str_replace(',', '', $value);

        return is_numeric($value) ? (float)$value : null;
    }
}
