<?php

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

/**
 * Resolves and freezes historical exchange rates to CLP.
 *
 * Source policy:
 *  - CLP = 1.0.
 *  - A valid exchange rate already supplied by FACTO is preserved.
 *  - Otherwise USD/EUR are retrieved from Findic, which republishes BCCh data.
 *  - If Findic has no record for a weekend/holiday, the last available value
 *    from the previous seven calendar days is used and its actual date stored.
 */
class ExchangeRateService
{
    const PROVIDER = 'FINDIC_BCCH';
    const BASE_URL = 'https://findic.cl/api';
    const HISTORY_BASE_URL = 'https://findic.cl/bjf';

    private $pdo;
    private $yearPayloadCache = array();
    private $historyPayloadCache = array();
    private $indicatorMap = array(
        'USD' => 'dolar',
        'EUR' => 'euro',
    );

    public function __construct(?PDO $pdo = null)
    {
        $this->pdo = $pdo ?: db();
    }

    public function resolve($currencyCode, $requestedDate, $allowRemote = true, $maxLookbackDays = 7)
    {
        $currencyCode = strtoupper(trim((string)$currencyCode));
        $requestedDate = $this->normalizeDate($requestedDate);
        $maxLookbackDays = max(0, min(31, (int)$maxLookbackDays));

        if ($currencyCode === '' || $currencyCode === 'CLP') {
            return array(
                'ok' => true,
                'currency_code' => 'CLP',
                'requested_date' => $requestedDate,
                'rate_date' => $requestedDate,
                'rate_to_clp' => 1.0,
                'source' => 'CLP',
                'provider' => 'internal',
                'quality_status' => 'validated',
                'is_fallback_date' => false,
            );
        }

        if (!isset($this->indicatorMap[$currencyCode])) {
            return array(
                'ok' => false,
                'currency_code' => $currencyCode,
                'requested_date' => $requestedDate,
                'rate_date' => null,
                'rate_to_clp' => null,
                'source' => null,
                'provider' => null,
                'quality_status' => 'unsupported',
                'message' => 'No existe proveedor automático configurado para ' . $currencyCode . '.',
            );
        }

        // Prioriza siempre el valor exacto de la fecha solicitada. No se debe
        // reutilizar prematuramente una tasa anterior si Findic ya publicó el
        // indicador del día requerido.
        $exactCached = $this->findCachedExact($currencyCode, $requestedDate);
        if ($exactCached) {
            return $this->publicResult($exactCached, $requestedDate);
        }

        if (!$allowRemote) {
            $fallbackCached = $this->findCached($currencyCode, $requestedDate, $maxLookbackDays);
            if ($fallbackCached) {
                return $this->publicResult($fallbackCached, $requestedDate);
            }
            return array(
                'ok' => false,
                'currency_code' => $currencyCode,
                'requested_date' => $requestedDate,
                'rate_date' => null,
                'rate_to_clp' => null,
                'source' => self::PROVIDER,
                'provider' => 'findic.cl',
                'quality_status' => 'missing',
                'message' => 'No existe un tipo de cambio almacenado para la fecha solicitada.',
            );
        }

        $requested = new DateTimeImmutable($requestedDate);
        $errors = array();
        for ($offset = 0; $offset <= $maxLookbackDays; $offset++) {
            $candidate = $requested->modify('-' . $offset . ' days');
            $candidateDate = $candidate->format('Y-m-d');

            if ($offset > 0) {
                $candidateCached = $this->findCachedExact($currencyCode, $candidateDate);
                if ($candidateCached) {
                    return $this->publicResult($candidateCached, $requestedDate);
                }
            }

            try {
                $remote = $this->fetchFindic($currencyCode, $candidateDate);
                if ($remote) {
                    $this->store($remote, $requestedDate);
                    return $this->publicResult($remote, $requestedDate);
                }
            } catch (Throwable $e) {
                $errors[] = $e->getMessage();
            }
        }

        return array(
            'ok' => false,
            'currency_code' => $currencyCode,
            'requested_date' => $requestedDate,
            'rate_date' => null,
            'rate_to_clp' => null,
            'source' => self::PROVIDER,
            'provider' => 'findic.cl',
            'quality_status' => 'unavailable',
            'message' => $errors ? end($errors) : 'Findic no devolvió una serie válida para la fecha ni los siete días anteriores.',
        );
    }

    public function probeRemote($currencyCode, $requestedDate)
    {
        $currencyCode = strtoupper(trim((string)$currencyCode));
        $requestedDate = $this->normalizeDate($requestedDate);
        if (!isset($this->indicatorMap[$currencyCode])) {
            return array('ok' => false, 'message' => 'Moneda no soportada por Findic: ' . $currencyCode);
        }
        try {
            $rate = $this->fetchFindic($currencyCode, $requestedDate);
            if (!$rate) {
                return array('ok' => false, 'message' => 'Findic respondió, pero no existe serie para la fecha solicitada.');
            }
            return array(
                'ok' => true,
                'currency_code' => $currencyCode,
                'requested_date' => $requestedDate,
                'rate_date' => $rate['rate_date'],
                'rate_to_clp' => $rate['rate_to_clp'],
                'source_url' => $rate['source_url'],
            );
        } catch (Throwable $e) {
            return array('ok' => false, 'message' => $e->getMessage());
        }
    }

    public function resolveWithManualFallback($currencyCode, $requestedDate, $manualRate = null)
    {
        $resolved = $this->resolve($currencyCode, $requestedDate, true, 7);
        if (!empty($resolved['ok'])) {
            return $resolved;
        }

        $manualRate = (float)$manualRate;
        if ($manualRate > 0) {
            return array(
                'ok' => true,
                'currency_code' => strtoupper(trim((string)$currencyCode)),
                'requested_date' => $this->normalizeDate($requestedDate),
                'rate_date' => $this->normalizeDate($requestedDate),
                'rate_to_clp' => $manualRate,
                'source' => 'MANUAL_AUTHORIZED',
                'provider' => 'manual',
                'quality_status' => 'manual',
                'is_fallback_date' => false,
                'warning' => 'Findic no estaba disponible; se utilizó el tipo de cambio manual informado.',
            );
        }
        return $resolved;
    }

    public function applyToFactoDocumentByDocumentId($environment, $documentId, $force = false)
    {
        $stmt = $this->pdo->prepare("SELECT * FROM facto_documents WHERE environment=? AND BINARY document_id=BINARY ? LIMIT 1");
        $stmt->execute(array((string)$environment, (string)$documentId));
        $document = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$document) {
            return array('ok' => false, 'message' => 'Documento FACTO local no encontrado.');
        }
        return $this->applyToFactoDocument($document, $force);
    }

    public function applyToFactoDocument(array $document, $force = false)
    {
        $currency = strtoupper(trim((string)($document['currency_code'] ?? '')));
        if ($currency === '') {
            $currency = $this->currencyCodeFromFactoId($document['currency_id'] ?? null) ?: 'CLP';
        }
        $issueDate = $this->normalizeDate($document['issue_date'] ?? date('Y-m-d'));

        $existingRate = (float)($document['exchange_rate_value'] ?? 0);
        $existingSource = strtoupper(trim((string)($document['exchange_rate_source'] ?? '')));
        $existingDate = !empty($document['exchange_rate_date']) ? $this->normalizeDate($document['exchange_rate_date']) : $issueDate;

        if ($currency === 'CLP') {
            $rate = array(
                'ok' => true,
                'currency_code' => 'CLP',
                'requested_date' => $issueDate,
                'rate_date' => $issueDate,
                'rate_to_clp' => 1.0,
                'source' => 'CLP',
                'provider' => 'internal',
                'quality_status' => 'validated',
                'is_fallback_date' => false,
            );
        } elseif (!$force && $existingRate > 0 && in_array($existingSource, array('FACTO','FACTO_DTE','FINDIC_BCCH','MANUAL_AUTHORIZED'), true)) {
            $rate = array(
                'ok' => true,
                'currency_code' => $currency,
                'requested_date' => $issueDate,
                'rate_date' => $existingDate,
                'rate_to_clp' => $existingRate,
                'source' => $existingSource,
                'provider' => strtolower($existingSource),
                'quality_status' => $existingSource === 'MANUAL_AUTHORIZED' ? 'manual' : 'validated',
                'is_fallback_date' => $existingDate !== $issueDate,
            );
        } elseif (!$force && $existingRate > 0 && $existingSource === '') {
            // FACTO can include exchange_rate_value in the DTE response. Preserve it.
            $rate = array(
                'ok' => true,
                'currency_code' => $currency,
                'requested_date' => $issueDate,
                'rate_date' => $existingDate,
                'rate_to_clp' => $existingRate,
                'source' => 'FACTO_DTE',
                'provider' => 'facto',
                'quality_status' => 'validated',
                'is_fallback_date' => $existingDate !== $issueDate,
            );
        } else {
            $rate = $this->resolve($currency, $issueDate, true, 7);
        }

        if (empty($rate['ok'])) {
            $stmt = $this->pdo->prepare("UPDATE facto_documents SET exchange_rate_status='missing', updated_at=CURRENT_TIMESTAMP WHERE id=?");
            $stmt->execute(array((int)$document['id']));
            return $rate;
        }

        $value = (float)$rate['rate_to_clp'];
        $netClp = round((float)($document['net_amount'] ?? 0) * $value, 2);
        $taxesClp = round((float)($document['taxes_amount'] ?? 0) * $value, 2);
        $totalClp = round((float)($document['total_amount'] ?? 0) * $value, 2);

        $stmt = $this->pdo->prepare("UPDATE facto_documents
            SET currency_code=?, exchange_rate_value=?, exchange_rate_date=?, exchange_rate_source=?, exchange_rate_status=?,
                net_amount_clp=?, taxes_amount_clp=?, total_amount_clp=?, updated_at=CURRENT_TIMESTAMP
            WHERE id=?");
        $stmt->execute(array(
            $currency,
            $value,
            $rate['rate_date'],
            $rate['source'],
            $rate['quality_status'],
            $netClp,
            $taxesClp,
            $totalClp,
            (int)$document['id'],
        ));

        $this->propagateToCollections($document, $currency, $rate, $totalClp);
        $this->propagateToQuote($document, $currency, $rate);

        $rate['net_amount_clp'] = $netClp;
        $rate['taxes_amount_clp'] = $taxesClp;
        $rate['total_amount_clp'] = $totalClp;
        return $rate;
    }

    public function backfillDocuments($limit = 500, $force = false)
    {
        $limit = max(1, min(5000, (int)$limit));
        $sql = "SELECT * FROM facto_documents
                WHERE issue_date IS NOT NULL
                  AND received_issued_flag=1
                  AND document_status=1
                  AND (currency_code IS NULL OR currency_code=''
                       OR exchange_rate_value IS NULL OR exchange_rate_value<=0
                       OR total_amount_clp IS NULL
                       OR exchange_rate_status IN ('missing','unavailable'))
                ORDER BY issue_date DESC,id DESC LIMIT " . $limit;
        $rows = $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        $result = array('evaluated' => 0, 'resolved' => 0, 'missing' => 0, 'errors' => 0, 'details' => array());
        foreach ($rows as $row) {
            $result['evaluated']++;
            try {
                $applied = $this->applyToFactoDocument($row, $force);
                if (!empty($applied['ok'])) {
                    $result['resolved']++;
                    if (count($result['details']) < 100) $result['details'][] = array('document_id' => $row['document_id'] ?? null, 'document_number' => $row['document_number'] ?? null, 'currency' => $row['currency_code'] ?? null, 'issue_date' => $row['issue_date'] ?? null, 'status' => 'resolved', 'rate_date' => $applied['rate_date'] ?? null, 'rate_to_clp' => $applied['rate_to_clp'] ?? null, 'source' => $applied['source'] ?? null);
                } else {
                    $result['missing']++;
                    if (count($result['details']) < 100) $result['details'][] = array('document_id' => $row['document_id'] ?? null, 'document_number' => $row['document_number'] ?? null, 'currency' => $row['currency_code'] ?? null, 'issue_date' => $row['issue_date'] ?? null, 'status' => 'missing', 'message' => $applied['message'] ?? 'Sin tasa disponible.');
                }
            } catch (Throwable $e) {
                $result['errors']++;
                if (count($result['details']) < 100) $result['details'][] = array('document_id' => $row['document_id'] ?? null, 'document_number' => $row['document_number'] ?? null, 'currency' => $row['currency_code'] ?? null, 'issue_date' => $row['issue_date'] ?? null, 'status' => 'error', 'message' => $e->getMessage());
                error_log('Exchange rate document ' . ($row['document_id'] ?? '') . ': ' . $e->getMessage());
            }
        }
        return $result;
    }

    /**
     * Compatibility alias used by diagnostics/reconciliation scripts.
     */
    public function applyPendingDocuments($limit = 500, $force = false)
    {
        return $this->backfillDocuments($limit, $force);
    }

    public function syncDailyRates($date = null)
    {
        $date = $this->normalizeDate($date ?: date('Y-m-d'));
        $result = array('date' => $date, 'resolved' => 0, 'missing' => 0, 'items' => array());
        foreach (array_keys($this->indicatorMap) as $currency) {
            $rate = $this->resolve($currency, $date, true, 7);
            $result['items'][$currency] = $rate;
            if (!empty($rate['ok'])) $result['resolved']++;
            else $result['missing']++;
        }
        return $result;
    }

    private function findCachedExact($currencyCode, $rateDate)
    {
        $stmt = $this->pdo->prepare("SELECT * FROM finance_exchange_rates
            WHERE currency_code=?
              AND rate_date=?
              AND provider IN ('FINDIC_BCCH','BCCH_SII_SEED','FACTO_DTE','MANUAL_AUTHORIZED')
              AND quality_status IN ('validated','provisional','manual')
            ORDER BY FIELD(provider,'FINDIC_BCCH','BCCH_SII_SEED','FACTO_DTE','MANUAL_AUTHORIZED'), id DESC
            LIMIT 1");
        $stmt->execute(array($currencyCode, $rateDate));
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    private function findCached($currencyCode, $requestedDate, $maxLookbackDays)
    {
        $minDate = (new DateTimeImmutable($requestedDate))->modify('-' . $maxLookbackDays . ' days')->format('Y-m-d');
        $stmt = $this->pdo->prepare("SELECT * FROM finance_exchange_rates
            WHERE currency_code=?
              AND rate_date BETWEEN ? AND ?
              AND provider IN ('FINDIC_BCCH','BCCH_SII_SEED','FACTO_DTE','MANUAL_AUTHORIZED')
              AND quality_status IN ('validated','provisional','manual')
            ORDER BY rate_date DESC,
                     FIELD(provider,'FINDIC_BCCH','BCCH_SII_SEED','FACTO_DTE','MANUAL_AUTHORIZED'),
                     id DESC
            LIMIT 1");
        $stmt->execute(array($currencyCode, $minDate, $requestedDate));
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    private function fetchFindic($currencyCode, $date)
    {
        $indicator = $this->indicatorMap[$currencyCode];
        $dateObj = new DateTimeImmutable($date);
        $errors = array();

        // 1) Ruta oficial por fecha. Findic exige dd-mm-yyyy en la URL y
        // responde la fecha normalizada dentro de serie[].
        $exactUrl = self::BASE_URL . '/' . rawurlencode($indicator) . '/' . $dateObj->format('d-m-Y');
        try {
            $json = $this->httpGet($exactUrl);
            $row = $this->extractFindicRow($json, $date, $indicator);
            if ($row) return $this->buildRemoteRate($currencyCode, $indicator, $date, $row, $exactUrl, $json);
        } catch (Throwable $e) {
            $errors[] = 'fecha exacta: ' . $e->getMessage();
        }

        // 2) Serie anual documentada. Reduce llamadas para backfills de varias
        // facturas del mismo ano y sirve de fallback ante proxies compartidos.
        $year = $dateObj->format('Y');
        $yearUrl = self::BASE_URL . '/' . rawurlencode($indicator) . '/' . $year;
        try {
            if (!isset($this->yearPayloadCache[$indicator])) $this->yearPayloadCache[$indicator] = array();
            if (!array_key_exists($year, $this->yearPayloadCache[$indicator])) {
                $this->yearPayloadCache[$indicator][$year] = $this->httpGet($yearUrl);
            }
            $yearJson = $this->yearPayloadCache[$indicator][$year];
            $row = $this->extractFindicRow($yearJson, $date, $indicator);
            if ($row) return $this->buildRemoteRate($currencyCode, $indicator, $date, $row, $yearUrl, $yearJson);
        } catch (Throwable $e) {
            $errors[] = 'serie anual: ' . $e->getMessage();
        }

        // 3) Historico JSON estatico publicado por Findic y regenerado a
        // diario. Es especialmente util cuando el hosting bloquea rutas API
        // parametrizadas, pero permite descargas HTTPS salientes normales.
        $historyUrl = self::HISTORY_BASE_URL . '/' . rawurlencode($indicator) . '.json';
        try {
            if (!array_key_exists($indicator, $this->historyPayloadCache)) {
                $this->historyPayloadCache[$indicator] = $this->httpGet($historyUrl, 45);
            }
            $historyJson = $this->historyPayloadCache[$indicator];
            $row = $this->extractFindicRow($historyJson, $date, $indicator);
            if ($row) return $this->buildRemoteRate($currencyCode, $indicator, $date, $row, $historyUrl, $historyJson);
        } catch (Throwable $e) {
            $errors[] = 'historico JSON: ' . $e->getMessage();
        }

        if ($errors) {
            throw new RuntimeException('Findic no pudo resolverse (' . implode(' | ', $errors) . ').');
        }
        return null;
    }

    public function probeProviderPaths($currencyCode, $requestedDate)
    {
        $currencyCode = strtoupper(trim((string)$currencyCode));
        $requestedDate = $this->normalizeDate($requestedDate);
        if (!isset($this->indicatorMap[$currencyCode])) {
            return array('ok' => false, 'message' => 'Moneda no soportada: ' . $currencyCode, 'paths' => array());
        }
        $indicator = $this->indicatorMap[$currencyCode];
        $dateObj = new DateTimeImmutable($requestedDate);
        $paths = array(
            'exact' => self::BASE_URL . '/' . rawurlencode($indicator) . '/' . $dateObj->format('d-m-Y'),
            'year' => self::BASE_URL . '/' . rawurlencode($indicator) . '/' . $dateObj->format('Y'),
            'history' => self::HISTORY_BASE_URL . '/' . rawurlencode($indicator) . '.json',
        );
        $out = array('ok' => false, 'currency_code' => $currencyCode, 'requested_date' => $requestedDate, 'paths' => array());
        foreach ($paths as $name => $url) {
            try {
                $json = $this->httpGet($url, $name === 'history' ? 45 : 20);
                $row = $this->extractFindicRow($json, $requestedDate, $indicator);
                $out['paths'][$name] = array(
                    'ok' => (bool)$row,
                    'url' => $url,
                    'bytes' => strlen($json),
                    'rate_date' => $row['fecha'] ?? null,
                    'rate_to_clp' => $row['valor'] ?? null,
                    'message' => $row ? 'Registro encontrado.' : 'Respuesta valida, pero sin registro para la fecha.',
                );
                if ($row) $out['ok'] = true;
            } catch (Throwable $e) {
                $out['paths'][$name] = array('ok' => false, 'url' => $url, 'bytes' => 0, 'rate_date' => null, 'rate_to_clp' => null, 'message' => $e->getMessage());
            }
        }
        return $out;
    }

    private function extractFindicRow($json, $expectedDate, $indicator = null)
    {
        $payload = json_decode((string)$json, true);
        if (!is_array($payload)) throw new RuntimeException('Findic devolvio JSON invalido.');

        $series = array();
        if (isset($payload['serie']) && is_array($payload['serie'])) {
            $series = $payload['serie'];
        } elseif (isset($payload['series']) && is_array($payload['series'])) {
            $series = $payload['series'];
        } elseif (isset($payload['data']) && is_array($payload['data'])) {
            $series = $payload['data'];
        } elseif ($indicator && isset($payload[$indicator]) && is_array($payload[$indicator])) {
            $node = $payload[$indicator];
            if (isset($node['serie']) && is_array($node['serie'])) $series = $node['serie'];
            elseif (isset($node['series']) && is_array($node['series'])) $series = $node['series'];
            elseif (isset($node['data']) && is_array($node['data'])) $series = $node['data'];
            else $series = $node;
        } elseif ($this->isListArray($payload)) {
            $series = $payload;
        }

        foreach ($series as $row) {
            if (!is_array($row)) continue;
            $rawDate = $row['fecha'] ?? $row['date'] ?? $row['Fecha'] ?? null;
            $rawValue = $row['valor'] ?? $row['value'] ?? $row['Valor'] ?? null;
            if ($rawDate === null || $rawValue === null) continue;
            $rowDate = $this->normalizeProviderDate($rawDate);
            $value = $this->parseProviderNumber($rawValue);
            if ($rowDate === $expectedDate && $value > 0) {
                return array('fecha' => $rowDate, 'valor' => $value);
            }
        }
        return null;
    }

    private function normalizeProviderDate($value)
    {
        $raw = trim((string)$value);
        if ($raw === '') return null;
        $raw10 = substr($raw, 0, 10);
        foreach (array('Y-m-d', 'd-m-Y', 'd/m/Y', 'Y/m/d') as $format) {
            $date = DateTimeImmutable::createFromFormat('!' . $format, $raw10);
            if ($date && $date->format($format) === $raw10) return $date->format('Y-m-d');
        }
        try { return (new DateTimeImmutable($raw))->format('Y-m-d'); } catch (Throwable $e) { return null; }
    }

    private function parseProviderNumber($value)
    {
        if (is_int($value) || is_float($value)) return (float)$value;
        $raw = trim((string)$value);
        if ($raw === '') return 0.0;
        $raw = str_replace(array("\xc2\xa0", ' '), '', $raw);
        if (strpos($raw, ',') !== false && strpos($raw, '.') !== false) {
            if (strrpos($raw, ',') > strrpos($raw, '.')) $raw = str_replace(array('.', ','), array('', '.'), $raw);
            else $raw = str_replace(',', '', $raw);
        } elseif (strpos($raw, ',') !== false) {
            $raw = str_replace(',', '.', $raw);
        }
        $raw = preg_replace('/[^0-9.\-]/', '', $raw);
        return is_numeric($raw) ? (float)$raw : 0.0;
    }

    private function isListArray(array $value)
    {
        if ($value === array()) return true;
        return array_keys($value) === range(0, count($value) - 1);
    }

    private function buildRemoteRate($currencyCode, $indicator, $requestedDate, array $row, $sourceUrl, $json)
    {
        return array(
            'currency_code' => $currencyCode,
            'indicator_code' => $indicator,
            'requested_date' => $requestedDate,
            'rate_date' => $row['fecha'],
            'rate_to_clp' => (float)$row['valor'],
            'source' => self::PROVIDER,
            'provider' => self::PROVIDER,
            'source_url' => $sourceUrl,
            'quality_status' => 'validated',
            // Store only the selected record. Annual/static historical payloads can be
            // hundreds of KB and must not be duplicated for every invoice.
            'raw_payload' => json_encode(array(
                'provider' => 'findic.cl',
                'indicator' => $indicator,
                'requested_date' => $requestedDate,
                'selected' => array('fecha' => $row['fecha'], 'valor' => (float)$row['valor']),
                'source_url' => $sourceUrl,
            ), JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
            'response_hash' => hash('sha256', (string)$json),
        );
    }

    private function store(array $rate, $requestedDate)
    {
        $stmt = $this->pdo->prepare("INSERT INTO finance_exchange_rates
            (currency_code,indicator_code,requested_date,rate_date,rate_to_clp,provider,source_name,source_url,quality_status,response_hash,raw_payload,retrieved_at)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,NOW())
            ON DUPLICATE KEY UPDATE requested_date=VALUES(requested_date),rate_to_clp=VALUES(rate_to_clp),source_url=VALUES(source_url),
                quality_status=VALUES(quality_status),response_hash=VALUES(response_hash),raw_payload=VALUES(raw_payload),retrieved_at=NOW(),updated_at=CURRENT_TIMESTAMP");
        $stmt->execute(array(
            $rate['currency_code'],
            $rate['indicator_code'],
            $requestedDate,
            $rate['rate_date'],
            $rate['rate_to_clp'],
            self::PROVIDER,
            'Banco Central de Chile vía Findic',
            $rate['source_url'],
            $rate['quality_status'],
            $rate['response_hash'],
            $rate['raw_payload'],
        ));
    }

    private function publicResult(array $row, $requestedDate)
    {
        $rateDate = $this->normalizeDate($row['rate_date']);
        return array(
            'ok' => true,
            'currency_code' => strtoupper((string)$row['currency_code']),
            'requested_date' => $requestedDate,
            'rate_date' => $rateDate,
            'rate_to_clp' => (float)$row['rate_to_clp'],
            'source' => (string)($row['source'] ?? $row['provider'] ?? self::PROVIDER),
            'provider' => (string)($row['provider'] ?? self::PROVIDER),
            'quality_status' => (string)($row['quality_status'] ?? 'validated'),
            'is_fallback_date' => $rateDate !== $requestedDate,
        );
    }

    private function propagateToCollections(array $document, $currency, array $rate, $totalClp)
    {
        $stmt = $this->pdo->prepare("SELECT * FROM billing_collections
            WHERE environment=? AND direction='receivable' AND BINARY facto_document_id=BINARY ?");
        $stmt->execute(array($document['environment'], $document['document_id']));
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $collection) {
            $paidClp = (float)($collection['paid_amount'] ?? 0);
            $balanceClp = max(0, $totalClp - $paidClp);
            $foreignTotal = $currency === 'CLP' ? null : (float)($document['total_amount'] ?? 0);
            $foreignPaid = $currency === 'CLP' ? null : round($paidClp / max((float)$rate['rate_to_clp'], 0.000001), 4);
            $foreignBalance = $currency === 'CLP' ? null : max(0, round($foreignTotal - $foreignPaid, 4));
            $existingStatus = (string)($collection['collection_status'] ?? 'por_cobrar');
            if (in_array($existingStatus, array('anulado','incobrable'), true)) {
                $status = $existingStatus;
            } elseif ($balanceClp <= 0.5) {
                $status = 'pagado';
            } elseif (!empty($collection['due_date']) && $collection['due_date'] < date('Y-m-d')) {
                $status = 'atrasado';
            } elseif ($paidClp > 0.005) {
                $status = 'parcial';
            } else {
                $status = 'por_cobrar';
            }
            $update = $this->pdo->prepare("UPDATE billing_collections SET
                currency_code=?,exchange_rate_value=?,exchange_rate_date=?,exchange_rate_source=?,exchange_rate_status=?,
                total_amount=?,balance_amount=?,collection_status=?,foreign_total_amount=?,foreign_paid_amount=?,foreign_balance_amount=?,updated_at=CURRENT_TIMESTAMP
                WHERE id=?");
            $update->execute(array(
                $currency,
                $rate['rate_to_clp'],
                $rate['rate_date'],
                $rate['source'],
                $rate['quality_status'],
                $totalClp,
                $balanceClp,
                $status,
                $foreignTotal,
                $foreignPaid,
                $foreignBalance,
                (int)$collection['id'],
            ));
        }
    }

    private function propagateToQuote(array $document, $currency, array $rate)
    {
        $quoteId = (int)($document['crm_quote_id'] ?? 0);
        if ($quoteId <= 0) return;
        $stmt = $this->pdo->prepare("UPDATE crm_quotes SET billing_currency_code=?,billing_exchange_rate_value=?,
            billing_exchange_rate_date=?,billing_exchange_rate_source=?,billing_exchange_rate_status=? WHERE id=?");
        $stmt->execute(array($currency,$rate['rate_to_clp'],$rate['rate_date'],$rate['source'],$rate['quality_status'],$quoteId));
    }

    private function currencyCodeFromFactoId($currencyId)
    {
        if ($currencyId === null || $currencyId === '') return null;
        $stmt = $this->pdo->prepare("SELECT currency_code FROM billing_currencies WHERE facto_currency_id=? AND is_active=1 LIMIT 1");
        $stmt->execute(array((int)$currencyId));
        $value = $stmt->fetchColumn();
        return $value ? strtoupper((string)$value) : null;
    }

    private function httpGet($url, $timeoutSeconds = 20)
    {
        $timeoutSeconds = max(5, min(60, (int)$timeoutSeconds));
        if (function_exists('curl_init')) {
            $ch = curl_init($url);
            $curlOptions = array(
                CURLOPT_RETURNTRANSFER => true,
                CURLOPT_FOLLOWLOCATION => true,
                CURLOPT_CONNECTTIMEOUT => 8,
                CURLOPT_TIMEOUT => $timeoutSeconds,
                CURLOPT_HTTPHEADER => array('Accept: application/json'),
                CURLOPT_USERAGENT => 'ICYLAB-OS/8.6.3 exchange-rate-service',
                CURLOPT_SSL_VERIFYPEER => true,
                CURLOPT_SSL_VERIFYHOST => 2,
            );
            if (defined('CURLOPT_IPRESOLVE') && defined('CURL_IPRESOLVE_V4')) {
                $curlOptions[CURLOPT_IPRESOLVE] = CURL_IPRESOLVE_V4;
            }
            if (defined('CURLOPT_ENCODING')) {
                $curlOptions[CURLOPT_ENCODING] = '';
            }
            curl_setopt_array($ch, $curlOptions);
            $body = curl_exec($ch);
            $status = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
            $error = curl_error($ch);
            curl_close($ch);
            if ($body === false || $status < 200 || $status >= 300) {
                throw new RuntimeException('Error consultando Findic (' . $status . ')' . ($error ? ': ' . $error : '.'));
            }
            return (string)$body;
        }

        $context = stream_context_create(array('http' => array(
            'method' => 'GET',
            'timeout' => $timeoutSeconds,
            'header' => "Accept: application/json\r\nUser-Agent: ICYLAB-OS/8.6.3 exchange-rate-service\r\n",
        )));
        $body = @file_get_contents($url, false, $context);
        if ($body === false) throw new RuntimeException('No fue posible consultar Findic desde el servidor.');
        return (string)$body;
    }

    private function normalizeDate($value)
    {
        $raw = substr(trim((string)$value), 0, 10);
        $date = DateTimeImmutable::createFromFormat('Y-m-d', $raw);
        if (!$date || $date->format('Y-m-d') !== $raw) {
            throw new InvalidArgumentException('Fecha inválida para tipo de cambio.');
        }
        return $raw;
    }
}
