<?php

require_once __DIR__ . '/../../core/database.php';
require_once __DIR__ . '/FactoClient.php';
require_once __DIR__ . '/FactoFileStorageService.php';
require_once __DIR__ . '/../billing/BillingRuntimeGate.php';
require_once __DIR__ . '/../billing/BillingReceivablesService.php';
require_once __DIR__ . '/../finance/ExchangeRateService.php';
require_once __DIR__ . '/../crm/QuoteInvoiceReconciliationService.php';

class FactoDocumentsSyncService
{
    private $pdo;
    private $client;
    private $storage;
    private $environment;
    private $quoteInvoiceReconciliation;

    public function __construct($environment = null)
    {
        $this->pdo = db();
        $this->client = new FactoClient($environment);
        $this->storage = new FactoFileStorageService();
        $this->environment = $this->client->getEnvironment();
        $this->quoteInvoiceReconciliation = new QuoteInvoiceReconciliationService($this->pdo);
    }

    public function syncIssuedDocuments($fromDate, $toDate, $perPage = 100, $fetchFiles = false)
    {
        // FACTO GET /documents: received_issued_flag 1 = issued, 0 = received.
        return $this->syncDocumentsByFlag(1, $fromDate, $toDate, $perPage, $fetchFiles);
    }

    public function syncReceivedDocuments($fromDate, $toDate, $perPage = 100, $fetchFiles = false)
    {
        // FACTO GET /documents: received_issued_flag 1 = issued, 0 = received.
        return $this->syncDocumentsByFlag(0, $fromDate, $toDate, $perPage, $fetchFiles);
    }

    public function syncDocumentsByFlag($receivedIssuedFlag, $fromDate, $toDate, $perPage = 100, $fetchFiles = false)
    {
        $page = 1;
        $totalSaved = 0;
        $perPage = max(1, min(100, (int) $perPage));
        $receivedIssuedFlag = (int) $receivedIssuedFlag;

        do {
            $response = $this->client->getDocuments(array(
                'page' => $page,
                'per_page' => $perPage,
                'issue_date_from' => $fromDate,
                'issue_date_to' => $toDate,
                'received_issued_flag' => $receivedIssuedFlag,
                'order_by' => 'desc'
            ));

            $documents = $this->extractEmbeddedList($response, 'documents');

            foreach ($documents as $document) {
                $saved = $this->saveDocument($document);
                if ($saved) {
                    $totalSaved++;

                    if ($fetchFiles) {
                        $documentId = $this->pick($document, 'document_id');
                        if ($documentId) {
                            $this->refreshDocument($documentId, true);
                        }
                    }
                }
            }

            $pageCount = isset($response['page_count']) ? (int) $response['page_count'] : 1;
            if ($pageCount < 1) {
                $pageCount = 1;
            }
            $page++;
        } while ($page <= $pageCount);

        $kind = $receivedIssuedFlag === 1 ? 'emitidos' : 'recibidos';
        $this->log('success', 'Documentos FACTO ' . $kind . ' sincronizados ambiente ' . $this->environment . ': ' . $totalSaved . ' desde ' . $fromDate . ' hasta ' . $toDate);

        return $totalSaved;
    }

    public function refreshDocument($documentId, $saveFiles = true)
    {
        $document = $this->client->getDocument($documentId, true);
        $savedPaths = array();

        if ($saveFiles) {
            $electronicDocument = $this->extractElectronicDocument($document);
            if (!empty($electronicDocument)) {
                $savedPaths = $this->storage->saveElectronicDocumentFiles((string) $documentId, $electronicDocument, $this->environment);
                $document['_icylab_saved_files'] = $savedPaths;
            }
        }

        $this->saveDocument($document, $savedPaths);
        return $document;
    }

    public function saveDocument($d, $savedPaths = array())
    {
        $documentId = $this->pick($d, 'document_id');

        if (!$documentId) {
            return false;
        }

        if ($this->isExcludedProductionTestDocument($d)) {
            return false;
        }

        $pdfFilePath = isset($savedPaths['pdf_file_path']) ? $savedPaths['pdf_file_path'] : $this->pickNested($d, array('_icylab_saved_files', 'pdf_file_path'));
        $xmlFilePath = isset($savedPaths['xml_file_path']) ? $savedPaths['xml_file_path'] : $this->pickNested($d, array('_icylab_saved_files', 'xml_file_path'));
        $currencyId = $this->pick($d, 'currency_id');
        $currencyCode = $this->pick($d, 'currency_code');
        if (!$currencyCode && $currencyId !== null) {
            $currencyCode = $this->currencyCodeFromFactoId($currencyId);
        }

        $rawPayload = json_encode($d, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        $payloadHash = hash('sha256', $rawPayload ?: (string) $documentId);

        $stmt = $this->pdo->prepare("\n            INSERT INTO facto_documents\n            (\n                environment, document_id, account_id, issue_date, document_number, document_type_id,\n                issuer_tax_id_code, receiver_legal_name, receiver_tax_id_code, receiver_country_id,\n                receiver_tax_id_type, customer_supplier_id, document_status, currency_id, currency_code,\n                exchange_rate_value, received_issued_flag, net_amount, taxes_amount, total_amount,\n                document_type_taxbureau, taxbureau_sending_status, taxbureau_validation_status,\n                document_sequence_code, pdf_file_path, xml_file_path, raw_payload, payload_hash,\n                last_synced_at, sale_modality, sale_clause, source_mode\n            )\n            VALUES\n            (\n                ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?, ?\n            )\n            ON DUPLICATE KEY UPDATE\n                environment = VALUES(environment),\n                account_id = VALUES(account_id),\n                issue_date = VALUES(issue_date),\n                document_number = VALUES(document_number),\n                document_type_id = VALUES(document_type_id),\n                issuer_tax_id_code = VALUES(issuer_tax_id_code),\n                receiver_legal_name = VALUES(receiver_legal_name),\n                receiver_tax_id_code = VALUES(receiver_tax_id_code),\n                receiver_country_id = COALESCE(VALUES(receiver_country_id), receiver_country_id),\n                receiver_tax_id_type = COALESCE(NULLIF(VALUES(receiver_tax_id_type), ''), receiver_tax_id_type),\n                customer_supplier_id = VALUES(customer_supplier_id),\n                document_status = VALUES(document_status),\n                currency_id = VALUES(currency_id),\n                currency_code = COALESCE(NULLIF(VALUES(currency_code), ''), currency_code),\n                exchange_rate_value = COALESCE(VALUES(exchange_rate_value), exchange_rate_value),\n                received_issued_flag = VALUES(received_issued_flag),\n                net_amount = VALUES(net_amount),\n                taxes_amount = VALUES(taxes_amount),\n                total_amount = VALUES(total_amount),\n                document_type_taxbureau = VALUES(document_type_taxbureau),\n                taxbureau_sending_status = VALUES(taxbureau_sending_status),\n                taxbureau_validation_status = VALUES(taxbureau_validation_status),\n                document_sequence_code = VALUES(document_sequence_code),\n                pdf_file_path = COALESCE(VALUES(pdf_file_path), pdf_file_path),\n                xml_file_path = COALESCE(VALUES(xml_file_path), xml_file_path),\n                raw_payload = VALUES(raw_payload),\n                payload_hash = VALUES(payload_hash),\n                sale_modality = COALESCE(VALUES(sale_modality), sale_modality),\n                sale_clause = COALESCE(VALUES(sale_clause), sale_clause),\n                source_mode = COALESCE(NULLIF(source_mode, ''), VALUES(source_mode)),\n                last_synced_at = NOW(),\n                updated_at = CURRENT_TIMESTAMP\n        ");

        $stmt->execute(array(
            $this->environment,
            $documentId,
            $this->pick($d, 'account_id'),
            $this->normalizeDate($this->pick($d, 'issue_date')),
            $this->pick($d, 'document_number'),
            $this->pick($d, 'document_type_id'),
            $this->pick($d, 'issuer_tax_id_code'),
            $this->pick($d, 'receiver_legal_name'),
            $this->pick($d, 'receiver_tax_id_code'),
            $this->pick($d, 'receiver_country_id'),
            $this->pick($d, 'receiver_tax_id_type'),
            $this->pick($d, 'customer_supplier_id'),
            $this->toInt($this->pick($d, 'document_status')),
            $currencyId,
            $currencyCode,
            $this->pick($d, 'exchange_rate_value'),
            $this->toInt($this->pick($d, 'received_issued_flag')),
            $this->toDecimal($this->pick($d, 'net_amount')),
            $this->toDecimal($this->pick($d, 'taxes_amount')),
            $this->toDecimal($this->pick($d, 'total_amount')),
            $this->pick($d, 'document_type_taxbureau'),
            $this->pick($d, 'taxbureau_sending_status'),
            $this->pick($d, 'taxbureau_validation_status'),
            $this->pick($d, 'document_sequence_code'),
            $pdfFilePath,
            $xmlFilePath,
            $rawPayload,
            $payloadHash,
            $this->pick($d, 'sale_modality'),
            $this->pick($d, 'sale_clause'),
            'facto_sync'
        ));

        // El vínculo canónico se guarda en crm_customer_id. Para exportaciones se
        // prioriza el identificador FACTO y luego los identificadores tributarios
        // extranjeros normalizados; nunca se depende del formato visual del RUT/ID.
        $this->resolveCustomerLink((string)$documentId);

        // Primero fija una asociación inequívoca para que la eventual CxC nazca
        // con quote_id/opportunity_id. El estado se calcula después de materializarla.
        try {
            $this->quoteInvoiceReconciliation->reconcileDocument($this->environment, (string)$documentId, false);
        } catch (Throwable $quoteLinkError) {
            $this->log('warning', 'Conciliación de cotización ' . $documentId . ': ' . $quoteLinkError->getMessage());
        }
        $this->materializeIssuedInvoiceReceivable($documentId, $d);

        try {
            $this->quoteInvoiceReconciliation->reconcileDocument($this->environment, (string)$documentId, true);
        } catch (Throwable $quoteStatusError) {
            $this->log('warning', 'Estado de cotización ' . $documentId . ': ' . $quoteStatusError->getMessage());
        }

        // Toda moneda queda normalizada a CLP para analítica y cobranza. Si FACTO
        // no trae la tasa, USD/EUR se resuelven por fecha de emisión vía Findic.
        try {
            (new ExchangeRateService($this->pdo))->applyToFactoDocumentByDocumentId($this->environment, (string)$documentId);
        } catch (Throwable $fxError) {
            error_log('FACTO FX ' . $documentId . ': ' . $fxError->getMessage());
        }

        return true;
    }

    private function resolveCustomerLink($documentId)
    {
        $baseWhere = "d.environment = ? AND BINARY d.document_id = BINARY ? AND d.crm_customer_id IS NULL";
        $baseParams = array($this->environment, (string)$documentId);

        // 1. Relación explícita desde cotización emitida.
        $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 = q.customer_id,\n                d.link_status = 'auto_linked',\n                d.link_confidence = 100,\n                d.linked_at = COALESCE(d.linked_at, NOW())\n            WHERE " . $baseWhere . "\n        ");
        $stmt->execute($baseParams);

        // 2. customer_supplier_id FACTO: llave más estable para clientes extranjeros.
        $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            WHERE " . $baseWhere . "\n              AND d.customer_supplier_id IS NOT NULL\n              AND d.customer_supplier_id <> ''\n        ");
        $stmt->execute($baseParams);

        // 3. Identificador de exportación normalizado.
        $normalizedDocTax = "REPLACE(REPLACE(REPLACE(UPPER(d.receiver_tax_id_code),'.',''),'-',''),' ','')";
        $normalizedExportTax = "REPLACE(REPLACE(REPLACE(UPPER(export_tax_id_code),'.',''),'-',''),' ','')";
        $stmt = $this->pdo->prepare("\n            UPDATE facto_documents d\n            INNER JOIN (\n                SELECT " . $normalizedExportTax . " 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 " . $normalizedExportTax . "\n                HAVING COUNT(*) = 1\n            ) c ON c.normalized_tax = " . $normalizedDocTax . "\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            WHERE " . $baseWhere . "\n              AND d.receiver_tax_id_code IS NOT NULL\n              AND d.receiver_tax_id_code <> ''\n        ");
        $stmt->execute($baseParams);

        // 4. Compatibilidad histórica: ID extranjero almacenado en customer_tax_id.
        $normalizedCustomerTax = "REPLACE(REPLACE(REPLACE(UPPER(customer_tax_id),'.',''),'-',''),' ','')";
        $stmt = $this->pdo->prepare("\n            UPDATE facto_documents d\n            INNER JOIN (\n                SELECT " . $normalizedCustomerTax . " 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 " . $normalizedCustomerTax . "\n                HAVING COUNT(*) = 1\n            ) c ON c.normalized_tax = " . $normalizedDocTax . "\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            WHERE " . $baseWhere . "\n              AND d.receiver_tax_id_code IS NOT NULL\n              AND d.receiver_tax_id_code <> ''\n        ");
        $stmt->execute($baseParams);

        // Completa metadatos de exportación y propaga el vínculo a la CxC.
        $stmt = $this->pdo->prepare("\n            UPDATE facto_documents d\n            INNER JOIN crm_customers c ON c.id = d.crm_customer_id\n            SET d.receiver_country_id = COALESCE(d.receiver_country_id, c.export_country_id, c.country_id),\n                d.receiver_tax_id_type = COALESCE(NULLIF(d.receiver_tax_id_type, ''), NULLIF(c.export_tax_id_type, ''), NULLIF(c.tax_id_type, '')),\n                d.finance_center_id = COALESCE(d.finance_center_id, c.default_income_center_id)\n            WHERE d.environment = ?\n              AND BINARY d.document_id = BINARY ?\n        ");
        $stmt->execute(array($this->environment, (string)$documentId));

        $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.updated_at = CURRENT_TIMESTAMP\n            WHERE d.environment = ?\n              AND BINARY d.document_id = BINARY ?\n              AND d.crm_customer_id IS NOT NULL\n        ");
        $stmt->execute(array($this->environment, (string)$documentId));
    }

    private function currencyCodeFromFactoId($currencyId)
    {
        $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 !== false ? (string)$value : null;
    }

    private function materializeIssuedInvoiceReceivable($documentId, array $payload)
    {
        $direction = $this->toInt($this->pick($payload, 'received_issued_flag'));
        $typeId = (int)$this->pick($payload, 'document_type_id');
        $total = $this->toDecimal($this->pick($payload, 'total_amount'));
        if ($direction !== 1 || !in_array($typeId, array(2, 32, 46), true) || $total <= 0) {
            return;
        }
        if (!billing_runtime_gate_allows('finance', $this->environment)) {
            $this->log('warning', 'Factura emitida ' . $documentId . ' sincronizada sin crear CxC porque FINANCE está cerrado.');
            return;
        }
        try {
            $service = new BillingReceivablesService($this->pdo);
            $service->ensureReceivableCollection((string)$documentId, $this->environment, array(
                'notes' => 'Cuenta por cobrar creada automáticamente al sincronizar la factura emitida.'
            ));
        } catch (Throwable $e) {
            $this->log('error', 'Factura emitida ' . $documentId . ' sincronizada, pero no fue posible crear su CxC: ' . $e->getMessage());
        }
    }

    /**
     * Excluye únicamente el documento confirmado como prueba de sandbox.
     * El documento 858 de Koywe Billing es real y no se excluye.
     */
    private function isExcludedProductionTestDocument($document)
    {
        if ($this->environment !== 'production') {
            return false;
        }

        $documentId = (string) $this->pick($document, 'document_id');
        $directionFlag = $this->toInt($this->pick($document, 'received_issued_flag'));

        return $directionFlag === 0 && $documentId === '862';
    }

    private function extractEmbeddedList($response, $key)
    {
        if (isset($response['_embedded'][$key]) && is_array($response['_embedded'][$key])) {
            return $response['_embedded'][$key];
        }

        if (isset($response[$key]) && is_array($response[$key])) {
            return $response[$key];
        }

        return array();
    }

    private function extractElectronicDocument($document)
    {
        if (isset($document['electronic_document']) && is_array($document['electronic_document'])) {
            return $document['electronic_document'];
        }

        return array();
    }

    private function pick($array, $key)
    {
        return isset($array[$key]) && $array[$key] !== '' ? $array[$key] : null;
    }

    private function pickNested($array, $path)
    {
        $cursor = $array;
        foreach ($path as $key) {
            if (!is_array($cursor) || !array_key_exists($key, $cursor)) {
                return null;
            }
            $cursor = $cursor[$key];
        }

        return $cursor !== '' ? $cursor : null;
    }

    private function normalizeDate($value)
    {
        if (!$value) {
            return null;
        }

        $timestamp = strtotime($value);
        return $timestamp ? date('Y-m-d', $timestamp) : null;
    }

    private function toInt($value)
    {
        if ($value === null || $value === '') {
            return null;
        }

        return (int) $value;
    }

    private function toDecimal($value)
    {
        if ($value === null || $value === '') {
            return 0;
        }

        return (float) str_replace(',', '.', (string) $value);
    }

    private function log($status, $message)
    {
        $stmt = $this->pdo->prepare("\n            INSERT INTO facto_sync_logs (status, message)\n            VALUES (?, ?)\n        ");

        $stmt->execute(array($status, $message));
    }
}
