<?php

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

/**
 * Repairs historical export-document links and applies historical FX rates.
 *
 * This service works only with documents already stored locally. It does not
 * call FACTO and therefore can safely run after a normal sync or from cron.
 */
class ExportDocumentReconciliationService
{
    private $pdo;
    private $environment;

    public function __construct(?PDO $pdo = null, $environment = 'production')
    {
        $this->pdo = $pdo ?: db();
        $this->environment = in_array((string)$environment, array('production', 'sandbox'), true)
            ? (string)$environment
            : 'production';
    }

    public function reconcileHistoricalExports($limit = 5000)
    {
        $limit = max(1, min(10000, (int)$limit));
        $result = array(
            'documents_total' => 0,
            'documents_linked_before' => 0,
            'documents_linked_after' => 0,
            'documents_unlinked' => 0,
            'customers_normalized' => 0,
            'collections_updated' => 0,
            'fx_evaluated' => 0,
            'fx_resolved' => 0,
            'fx_missing' => 0,
            'fx_errors' => 0,
            'fx_messages' => array(),
        );

        $where = "environment = ? AND received_issued_flag = 1 AND CAST(COALESCE(document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112)";
        $stmt = $this->pdo->prepare("SELECT COUNT(*) AS total, SUM(crm_customer_id IS NOT NULL) AS linked FROM facto_documents WHERE $where");
        $stmt->execute(array($this->environment));
        $counts = $stmt->fetch(PDO::FETCH_ASSOC) ?: array();
        $result['documents_total'] = (int)($counts['total'] ?? 0);
        $result['documents_linked_before'] = (int)($counts['linked'] ?? 0);

        $this->pdo->beginTransaction();
        try {
            // 1. Explicit quote relationship.
            $stmt = $this->pdo->prepare("\n                UPDATE facto_documents d\n                INNER JOIN crm_quotes q ON BINARY q.facto_document_id = BINARY d.document_id\n                SET d.crm_quote_id = COALESCE(d.crm_quote_id, q.id),\n                    d.crm_opportunity_id = COALESCE(d.crm_opportunity_id, q.opportunity_id),\n                    d.crm_customer_id = COALESCE(d.crm_customer_id, q.customer_id),\n                    d.finance_center_id = COALESCE(d.finance_center_id, q.finance_center_id),\n                    d.link_status = 'auto_linked',\n                    d.link_confidence = 100,\n                    d.linked_at = COALESCE(d.linked_at, NOW()),\n                    d.updated_at = CURRENT_TIMESTAMP\n                WHERE d.environment = ?\n                  AND d.received_issued_flag = 1\n                  AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112)\n            ");
            $stmt->execute(array($this->environment));

            // 2. Unique FACTO customer/supplier id.
            $stmt = $this->pdo->prepare("\n                UPDATE facto_documents d\n                INNER JOIN (\n                    SELECT facto_customer_supplier_id, MIN(id) AS customer_id\n                    FROM crm_customers\n                    WHERE deleted_at IS NULL\n                      AND facto_customer_supplier_id IS NOT NULL\n                      AND facto_customer_supplier_id <> ''\n                    GROUP BY facto_customer_supplier_id\n                    HAVING COUNT(*) = 1\n                ) c ON BINARY c.facto_customer_supplier_id = BINARY d.customer_supplier_id\n                SET d.crm_customer_id = c.customer_id,\n                    d.link_status = 'auto_linked',\n                    d.link_confidence = 100,\n                    d.linked_at = COALESCE(d.linked_at, NOW()),\n                    d.updated_at = CURRENT_TIMESTAMP\n                WHERE d.environment = ?\n                  AND d.received_issued_flag = 1\n                  AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112)\n                  AND d.crm_customer_id IS NULL\n            ");
            $stmt->execute(array($this->environment));

            // 3. Canonical foreign tax id.
            $docTax = "REPLACE(REPLACE(REPLACE(UPPER(d.receiver_tax_id_code),'.',''),'-',''),' ','')";
            $exportTax = "REPLACE(REPLACE(REPLACE(UPPER(export_tax_id_code),'.',''),'-',''),' ','')";
            $stmt = $this->pdo->prepare("\n                UPDATE facto_documents d\n                INNER JOIN (\n                    SELECT $exportTax AS normalized_tax, MIN(id) AS customer_id\n                    FROM crm_customers\n                    WHERE deleted_at IS NULL\n                      AND export_tax_id_code IS NOT NULL\n                      AND export_tax_id_code <> ''\n                    GROUP BY $exportTax\n                    HAVING COUNT(*) = 1\n                ) c ON c.normalized_tax = $docTax\n                SET d.crm_customer_id = c.customer_id,\n                    d.link_status = 'auto_linked',\n                    d.link_confidence = 98,\n                    d.linked_at = COALESCE(d.linked_at, NOW()),\n                    d.updated_at = CURRENT_TIMESTAMP\n                WHERE d.environment = ?\n                  AND d.received_issued_flag = 1\n                  AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112)\n                  AND d.crm_customer_id IS NULL\n                  AND d.receiver_tax_id_code IS NOT NULL\n                  AND d.receiver_tax_id_code <> ''\n            ");
            $stmt->execute(array($this->environment));

            // 4. Historical compatibility: foreign id stored in customer_tax_id.
            $customerTax = "REPLACE(REPLACE(REPLACE(UPPER(customer_tax_id),'.',''),'-',''),' ','')";
            $stmt = $this->pdo->prepare("\n                UPDATE facto_documents d\n                INNER JOIN (\n                    SELECT $customerTax AS normalized_tax, MIN(id) AS customer_id\n                    FROM crm_customers\n                    WHERE deleted_at IS NULL\n                      AND customer_tax_id IS NOT NULL\n                      AND customer_tax_id <> ''\n                    GROUP BY $customerTax\n                    HAVING COUNT(*) = 1\n                ) c ON c.normalized_tax = $docTax\n                SET d.crm_customer_id = c.customer_id,\n                    d.link_status = 'auto_linked',\n                    d.link_confidence = 95,\n                    d.linked_at = COALESCE(d.linked_at, NOW()),\n                    d.updated_at = CURRENT_TIMESTAMP\n                WHERE d.environment = ?\n                  AND d.received_issued_flag = 1\n                  AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112)\n                  AND d.crm_customer_id IS NULL\n                  AND d.receiver_tax_id_code IS NOT NULL\n                  AND d.receiver_tax_id_code <> ''\n            ");
            $stmt->execute(array($this->environment));

            // Normalize ISO currency before FX processing.
            $stmt = $this->pdo->prepare("\n                UPDATE facto_documents d\n                LEFT JOIN billing_currencies cur ON cur.facto_currency_id = CAST(d.currency_id AS UNSIGNED)\n                SET d.currency_code = COALESCE(NULLIF(d.currency_code, ''), NULLIF(cur.currency_code, '')),\n                    d.source_mode = COALESCE(NULLIF(d.source_mode, ''), 'facto_sync'),\n                    d.updated_at = CURRENT_TIMESTAMP\n                WHERE d.environment = ?\n                  AND d.received_issued_flag = 1\n                  AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112)\n            ");
            $stmt->execute(array($this->environment));

            // Complete canonical export fields in CRM customers.
            $stmt = $this->pdo->prepare("\n                UPDATE crm_customers c\n                INNER JOIN facto_documents d ON d.crm_customer_id = c.id\n                SET c.export_tax_id_code = COALESCE(NULLIF(c.export_tax_id_code, ''), NULLIF(d.receiver_tax_id_code, '')),\n                    c.export_tax_id_type = COALESCE(NULLIF(c.export_tax_id_type, ''), NULLIF(d.receiver_tax_id_type, ''), 'IDEXTRANJERO'),\n                    c.export_country_id = COALESCE(c.export_country_id, d.receiver_country_id, CASE WHEN c.country_id <> 253 THEN c.country_id ELSE NULL END),\n                    c.updated_at = CURRENT_TIMESTAMP\n                WHERE d.environment = ?\n                  AND d.received_issued_flag = 1\n                  AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112)\n                  AND c.deleted_at IS NULL\n            ");
            $stmt->execute(array($this->environment));
            $result['customers_normalized'] = (int)$stmt->rowCount();

            // Propagate canonical link and currency to receivables.
            $stmt = $this->pdo->prepare("\n                UPDATE billing_collections bc\n                INNER JOIN facto_documents d\n                  ON d.environment = bc.environment\n                 AND BINARY d.document_id = BINARY bc.facto_document_id\n                SET bc.customer_id = COALESCE(bc.customer_id, d.crm_customer_id),\n                    bc.finance_center_id = COALESCE(bc.finance_center_id, d.finance_center_id),\n                    bc.currency_code = COALESCE(NULLIF(bc.currency_code, ''), NULLIF(d.currency_code, '')),\n                    bc.currency_id = COALESCE(bc.currency_id, d.currency_id),\n                    bc.exchange_rate_value = COALESCE(bc.exchange_rate_value, d.exchange_rate_value),\n                    bc.updated_at = CURRENT_TIMESTAMP\n                WHERE d.environment = ?\n                  AND d.received_issued_flag = 1\n                  AND CAST(COALESCE(d.document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112)\n                  AND d.crm_customer_id IS NOT NULL\n            ");
            $stmt->execute(array($this->environment));
            $result['collections_updated'] = (int)$stmt->rowCount();

            $this->pdo->commit();
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            throw $e;
        }

        // Apply FX after the relational transaction. Network failures must not
        // roll back valid customer links.
        $stmt = $this->pdo->prepare("\n            SELECT *\n            FROM facto_documents\n            WHERE environment = ?\n              AND received_issued_flag = 1\n              AND document_status = 1\n              AND CAST(COALESCE(document_type_taxbureau, 0) AS UNSIGNED) IN (110,111,112)\n            ORDER BY issue_date DESC, id DESC\n            LIMIT $limit\n        ");
        $stmt->execute(array($this->environment));
        $fx = new ExchangeRateService($this->pdo);
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $document) {
            $result['fx_evaluated']++;
            try {
                $applied = $fx->applyToFactoDocument($document, false);
                if (!empty($applied['ok'])) {
                    $result['fx_resolved']++;
                } else {
                    $result['fx_missing']++;
                    $result['fx_messages'][] = array(
                        'document_id' => (string)($document['document_id'] ?? ''),
                        'document_number' => (string)($document['document_number'] ?? ''),
                        'issue_date' => (string)($document['issue_date'] ?? ''),
                        'currency_code' => (string)($document['currency_code'] ?? ''),
                        'message' => (string)($applied['message'] ?? 'Tipo de cambio no resuelto.'),
                    );
                }
            } catch (Throwable $e) {
                $result['fx_errors']++;
                $result['fx_messages'][] = array(
                    'document_id' => (string)($document['document_id'] ?? ''),
                    'document_number' => (string)($document['document_number'] ?? ''),
                    'issue_date' => (string)($document['issue_date'] ?? ''),
                    'currency_code' => (string)($document['currency_code'] ?? ''),
                    'message' => $e->getMessage(),
                );
                error_log('Export reconciliation FX ' . ($document['document_id'] ?? '') . ': ' . $e->getMessage());
            }
        }

        $stmt = $this->pdo->prepare("SELECT COUNT(*) AS total, SUM(crm_customer_id IS NOT NULL) AS linked FROM facto_documents WHERE $where");
        $stmt->execute(array($this->environment));
        $counts = $stmt->fetch(PDO::FETCH_ASSOC) ?: array();
        $result['documents_linked_after'] = (int)($counts['linked'] ?? 0);
        $result['documents_unlinked'] = max(0, (int)($counts['total'] ?? 0) - $result['documents_linked_after']);

        return $result;
    }
}
