<?php

require_once __DIR__ . '/QuoteLifecycleService.php';
require_once __DIR__ . '/../billing/BillingQuoteCalculator.php';

/**
 * Vincula facturas emitidas sincronizadas desde FACTO con cotizaciones CRM.
 *
 * Nunca selecciona una cotización de forma arbitraria: una coincidencia
 * inferida sólo se acepta cuando existe exactamente una candidata elegible.
 */
class QuoteInvoiceReconciliationService
{
    private $pdo;
    private $columnCache = array();

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    public function reconcileDocument($environment, $documentId, $synchronizeStatus = true, $explicitQuote = null, array $verificationDocument = array()): array
    {
        $environment = strtolower(trim((string)$environment));
        if (!in_array($environment, array('sandbox', 'production'), true)) {
            throw new InvalidArgumentException('Ambiente de facturación inválido.');
        }

        $documentId = trim((string)$documentId);
        if ($documentId === '') {
            throw new InvalidArgumentException('Documento FACTO inválido.');
        }

        $document = $this->loadDocument($environment, $documentId, false);
        if (!$document) {
            return array('matched' => false, 'reason' => 'Documento FACTO no encontrado.');
        }
        if (!$this->isEligibleInvoice($document)) {
            return array('matched' => false, 'reason' => 'El documento no es una factura emitida vigente.');
        }

        if (!empty($verificationDocument)) {
            $this->assertRemoteDocumentIdentity($document, $verificationDocument);
        }

        $opportunityId = $this->resolveOpportunityId($document);
        if ($opportunityId > 0 && empty($document['crm_opportunity_id'])) {
            // Conserva la pista en memoria para resolver la cotizacion. La
            // escritura se realiza recien dentro de la transaccion verificada.
            $document['crm_opportunity_id'] = $opportunityId;
        }

        $match = $explicitQuote !== null && trim((string)$explicitQuote) !== ''
            ? $this->resolveExplicitQuoteMatch($explicitQuote)
            : $this->resolveQuoteMatch($document);
        if (empty($match['quote_id'])) {
            return array(
                'matched' => false,
                'ambiguous' => !empty($match['ambiguous']),
                'reason' => $match['reason'] ?? 'No existe una cotización asociable sin ambigüedad.'
            );
        }

        $quoteId = (int)$match['quote_id'];
        $manageTransaction = !$this->pdo->inTransaction();
        if ($manageTransaction) {
            $this->pdo->beginTransaction();
        }

        try {
            $lockedDocument = $this->loadDocument($environment, $documentId, true);
            if (!$lockedDocument || !$this->isEligibleInvoice($lockedDocument)) {
                throw new RuntimeException('El documento dejo de ser una factura emitida elegible. No se modifico ningun vinculo.');
            }
            $document = $lockedDocument;
            $quote = $this->loadQuote($quoteId, true);
            if (!$quote || !empty($quote['deleted_at'])) {
                throw new RuntimeException('La cotización asociada no está disponible.');
            }

            $existingFactoId = trim((string)($quote['facto_document_id'] ?? ''));
            if ($existingFactoId !== '' && $existingFactoId !== $documentId) {
                throw new RuntimeException('La cotización ya está vinculada a otra factura FACTO.');
            }
            if (!empty($document['crm_quote_id']) && (int)$document['crm_quote_id'] !== $quoteId) {
                throw new RuntimeException('La factura ya está vinculada a otra cotización.');
            }

            $quoteOpportunityId = (int)($quote['opportunity_id'] ?? 0);
            if ($quoteOpportunityId > 0) {
                $opportunityStmt = $this->pdo->prepare('SELECT facto_document_id FROM crm_opportunities WHERE id=? LIMIT 1 FOR UPDATE');
                $opportunityStmt->execute(array($quoteOpportunityId));
                $opportunityFactoId = trim((string)$opportunityStmt->fetchColumn());
                if ($opportunityFactoId !== '' && $opportunityFactoId !== $documentId) {
                    throw new RuntimeException('La oportunidad ya esta vinculada a otra factura FACTO. No se modifico ningun vinculo.');
                }
            }

            $collectionConflict = $this->pdo->prepare("SELECT quote_id,opportunity_id FROM billing_collections WHERE environment=? AND direction='receivable' AND BINARY facto_document_id=BINARY ? LIMIT 1 FOR UPDATE");
            $collectionConflict->execute(array($environment, $documentId));
            $collectionLink = $collectionConflict->fetch(PDO::FETCH_ASSOC);
            if ($collectionLink && !empty($collectionLink['quote_id']) && (int)$collectionLink['quote_id'] !== $quoteId) {
                throw new RuntimeException('La cuenta por cobrar ya esta vinculada a otra cotizacion. No se modifico ningun vinculo.');
            }
            if ($collectionLink && !empty($collectionLink['opportunity_id']) && $quoteOpportunityId > 0 && (int)$collectionLink['opportunity_id'] !== $quoteOpportunityId) {
                throw new RuntimeException('La cuenta por cobrar ya esta vinculada a otra oportunidad. No se modifico ningun vinculo.');
            }

            if ($this->tableExists('crm_documents')) {
                $crmDocumentStmt = $this->pdo->prepare('SELECT quote_id,opportunity_id FROM crm_documents WHERE BINARY facto_document_id=BINARY ? LIMIT 1 FOR UPDATE');
                $crmDocumentStmt->execute(array($documentId));
                $crmDocumentLink = $crmDocumentStmt->fetch(PDO::FETCH_ASSOC);
                if ($crmDocumentLink && !empty($crmDocumentLink['quote_id']) && (int)$crmDocumentLink['quote_id'] !== $quoteId) {
                    throw new RuntimeException('El documento CRM ya esta vinculado a otra cotizacion. No se modifico ningun vinculo.');
                }
                if ($crmDocumentLink && !empty($crmDocumentLink['opportunity_id']) && $quoteOpportunityId > 0 && (int)$crmDocumentLink['opportunity_id'] !== $quoteOpportunityId) {
                    throw new RuntimeException('El documento CRM ya esta vinculado a otra oportunidad. No se modifico ningun vinculo.');
                }
            }

            if (!empty($match['requires_identity_validation'])) {
                $this->assertQuoteDocumentIdentity($document, $quote);
            }

            $before = array(
                'document_quote_id' => !empty($document['crm_quote_id']) ? (int)$document['crm_quote_id'] : null,
                'document_opportunity_id' => !empty($document['crm_opportunity_id']) ? (int)$document['crm_opportunity_id'] : null,
                'quote_facto_document_id' => $existingFactoId !== '' ? $existingFactoId : null,
                'quote_status' => $quote['status'] ?? null,
            );

            $this->linkDocument($document, $quote, (string)($match['strategy'] ?? 'reconciliation'));
            $collection = $this->loadCollection($environment, $documentId);
            $this->updateQuoteInvoiceMetadata($quoteId, $document, $collection);

            $targetStatus = null;
            $lifecycleResult = null;
            if ($synchronizeStatus) {
                $targetStatus = $this->targetQuoteStatus($document, $collection);
                $lifecycleResult = (new QuoteLifecycleService($this->pdo))->changeStatus(
                    $quoteId,
                    $targetStatus,
                    array(
                        'allow_system_status' => true,
                        'invoice_issue_date' => $document['issue_date'] ?? null,
                        'payment_date' => $this->paymentDate($document, $collection),
                        'owner_name' => 'Sincronización FACTO',
                        'record_activity' => true,
                        'description_prefix' => 'Estado conciliado desde una factura emitida y vinculada.'
                    )
                );
            }

            $this->updateOpportunityInvoiceMetadata($quote, $document, $collection);
            $this->ensureCrmInvoiceDocument($quote, $document);
            $this->auditLink($document, $quote, (string)($match['strategy'] ?? 'reconciliation'), $before);

            if ($manageTransaction) {
                $this->pdo->commit();
            }

            return array(
                'matched' => true,
                'quote_id' => $quoteId,
                'opportunity_id' => !empty($quote['opportunity_id']) ? (int)$quote['opportunity_id'] : null,
                'strategy' => $match['strategy'],
                'verified' => !empty($match['requires_identity_validation']) || !empty($verificationDocument),
                'target_status' => $targetStatus,
                'status_changed' => is_array($lifecycleResult) ? !empty($lifecycleResult['status_changed']) : false
            );
        } catch (Throwable $e) {
            if ($manageTransaction && $this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }
    }

    private function loadDocument($environment, $documentId, $lock): ?array
    {
        $sql = "SELECT * FROM facto_documents WHERE environment=? AND BINARY document_id=BINARY ? LIMIT 1";
        if ($lock) {
            $sql .= ' FOR UPDATE';
        }
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array($environment, $documentId));
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    private function loadQuote($quoteId, $lock): ?array
    {
        $sql = 'SELECT q.*, c.customer_tax_id AS reconciliation_customer_tax_id '
             . 'FROM crm_quotes q LEFT JOIN crm_customers c ON c.id=q.customer_id '
             . 'WHERE q.id=? LIMIT 1' . ($lock ? ' FOR UPDATE' : '');
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array((int)$quoteId));
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    private function isEligibleInvoice(array $document): bool
    {
        $typeId = (int)($document['document_type_id'] ?? 0);
        $taxType = trim((string)($document['document_type_taxbureau'] ?? ''));
        return (int)($document['received_issued_flag'] ?? -1) === 1
            && in_array((int)($document['document_status'] ?? 0), array(1, 2, 3), true)
            && in_array($typeId, array(2, 32, 46), true)
            && !in_array($taxType, array('61', '801'), true)
            && strtolower(trim((string)($document['payment_status'] ?? ''))) !== 'anulado';
    }

    private function resolveOpportunityId(array $document): int
    {
        if (!empty($document['crm_opportunity_id'])) {
            return (int)$document['crm_opportunity_id'];
        }

        $documentId = (string)$document['document_id'];
        $stmt = $this->pdo->prepare("SELECT id FROM crm_opportunities WHERE BINARY facto_document_id=BINARY ? AND deleted_at IS NULL LIMIT 2");
        $stmt->execute(array($documentId));
        $ids = array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN));
        if (count($ids) === 1) {
            return $ids[0];
        }

        $stmt = $this->pdo->prepare("SELECT DISTINCT opportunity_id FROM crm_documents WHERE BINARY facto_document_id=BINARY ? AND opportunity_id IS NOT NULL LIMIT 2");
        $stmt->execute(array($documentId));
        $ids = array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN));
        return count($ids) === 1 ? $ids[0] : 0;
    }

    private function resolveQuoteMatch(array $document): array
    {
        if (!empty($document['crm_quote_id'])) {
            return array('quote_id' => (int)$document['crm_quote_id'], 'strategy' => 'facto_document_explicit');
        }

        if ($this->columnExists('crm_documents', 'quote_id')) {
            $crmDocumentMatch = $this->uniqueQuote(
                "SELECT DISTINCT quote_id FROM crm_documents WHERE BINARY facto_document_id=BINARY ? AND quote_id IS NOT NULL LIMIT 2",
                array((string)$document['document_id'])
            );
            if (!empty($crmDocumentMatch['quote_id']) || !empty($crmDocumentMatch['ambiguous'])) {
                $crmDocumentMatch['strategy'] = 'crm_document_quote_id';
                return $crmDocumentMatch;
            }
        }

        $direct = $this->uniqueQuote(
            "SELECT id FROM crm_quotes WHERE BINARY facto_document_id=BINARY ?" . $this->activeQuoteFilter() . " LIMIT 2",
            array((string)$document['document_id'])
        );
        if (!empty($direct['quote_id']) || !empty($direct['ambiguous'])) {
            $direct['strategy'] = 'quote_facto_document_id';
            return $direct;
        }

        $payloadQuoteNumber = $this->extractQuoteNumber($document['raw_payload'] ?? null);
        if ($payloadQuoteNumber !== '') {
            $byPayload = $this->uniqueQuote(
                "SELECT id FROM crm_quotes WHERE UPPER(quote_number)=?" . $this->activeQuoteFilter() . ' LIMIT 2',
                array($payloadQuoteNumber)
            );
            if (!empty($byPayload['quote_id']) || !empty($byPayload['ambiguous'])) {
                $byPayload['strategy'] = 'document_observation_quote_number';
                $byPayload['requires_identity_validation'] = true;
                return $byPayload;
            }
        }

        $documentNumber = trim((string)($document['document_number'] ?? ''));
        if (
            $documentNumber !== ''
            && (!empty($document['crm_customer_id']) || !empty($document['crm_opportunity_id']))
            && $this->columnExists('crm_quotes', 'invoice_number')
        ) {
            $sql = "SELECT id FROM crm_quotes WHERE BINARY invoice_number=BINARY ?" . $this->activeQuoteFilter();
            $params = array($documentNumber);
            if (!empty($document['crm_customer_id'])) {
                $sql .= ' AND customer_id=?';
                $params[] = (int)$document['crm_customer_id'];
            }
            if (!empty($document['crm_opportunity_id'])) {
                $sql .= ' AND opportunity_id=?';
                $params[] = (int)$document['crm_opportunity_id'];
            }
            $byFolio = $this->uniqueQuote($sql . ' LIMIT 2', $params);
            if (!empty($byFolio['quote_id']) || !empty($byFolio['ambiguous'])) {
                $byFolio['strategy'] = 'invoice_number';
                return $byFolio;
            }
        }

        $byIdentity = $this->resolveExactIdentityMatch($document);
        if (!empty($byIdentity['quote_id']) || !empty($byIdentity['ambiguous'])) {
            return $byIdentity;
        }

        if (!empty($document['crm_opportunity_id'])) {
            $sql = "SELECT id FROM crm_quotes WHERE opportunity_id=?"
                . $this->activeQuoteFilter()
                . " AND status IN ('Aceptada','Facturada','Esperando pago','Pagada') LIMIT 2";
            $byOpportunity = $this->uniqueQuote($sql, array((int)$document['crm_opportunity_id']));
            $byOpportunity['strategy'] = 'single_eligible_quote_in_opportunity';
            $byOpportunity['requires_identity_validation'] = true;
            return $byOpportunity;
        }

        return array('quote_id' => 0, 'ambiguous' => false, 'reason' => 'La factura no tiene oportunidad ni cotización vinculada.');
    }

    private function resolveExplicitQuoteMatch($identifier): array
    {
        $identifier = trim((string)$identifier);
        if ($identifier === '') {
            return array('quote_id' => 0, 'ambiguous' => false, 'reason' => 'Debes indicar la cotizacion de origen.');
        }

        if (ctype_digit($identifier)) {
            $match = $this->uniqueQuote(
                'SELECT id FROM crm_quotes WHERE id=?' . $this->activeQuoteFilter() . ' LIMIT 2',
                array((int)$identifier)
            );
        } else {
            $match = $this->uniqueQuote(
                'SELECT id FROM crm_quotes WHERE UPPER(quote_number)=?' . $this->activeQuoteFilter() . ' LIMIT 2',
                array(strtoupper($identifier))
            );
        }
        $match['strategy'] = 'explicit_verified_quote';
        $match['requires_identity_validation'] = true;
        if (empty($match['quote_id']) && empty($match['ambiguous'])) {
            $match['reason'] = 'La cotizacion indicada no existe o fue eliminada.';
        }
        return $match;
    }

    private function resolveExactIdentityMatch(array $document): array
    {
        $customerId = (int)($document['crm_customer_id'] ?? 0);
        $issueDate = trim((string)($document['issue_date'] ?? ''));
        $currency = strtoupper(trim((string)($document['currency_code'] ?? 'CLP')));
        if ($customerId <= 0 || $issueDate === '' || $currency !== 'CLP') {
            return array('quote_id' => 0, 'ambiguous' => false);
        }

        $net = $this->documentAmount($document, 'net');
        $tax = $this->documentAmount($document, 'taxes');
        $total = $this->documentAmount($document, 'total');
        $documentId = (string)$document['document_id'];
        $sql = "SELECT id FROM crm_quotes
                WHERE customer_id=?" . $this->activeQuoteFilter() . "
                  AND status IN ('Aceptada','Facturada','Esperando pago','Pagada')
                  AND (facto_document_id IS NULL OR facto_document_id='' OR BINARY facto_document_id=BINARY ?)
                  AND ABS((subtotal-COALESCE(discount_amount,0))-?)<=1
                  AND ABS(COALESCE(iva,0)-?)<=1
                  AND ABS(total-?)<=1
                  AND DATE(created_at)<=?
                ORDER BY id DESC LIMIT 2";
        $match = $this->uniqueQuote($sql, array($customerId, $documentId, $net, $tax, $total, $issueDate));
        $match['strategy'] = 'unique_customer_date_amount_identity';
        $match['requires_identity_validation'] = true;
        return $match;
    }

    private function uniqueQuote($sql, array $params): array
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $ids = array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN));
        if (count($ids) === 1) {
            return array('quote_id' => $ids[0], 'ambiguous' => false);
        }
        if (count($ids) > 1) {
            return array('quote_id' => 0, 'ambiguous' => true, 'reason' => 'Múltiples cotizaciones coinciden con la factura; se requiere vinculación manual.');
        }
        return array('quote_id' => 0, 'ambiguous' => false);
    }

    private function activeQuoteFilter(): string
    {
        return $this->columnExists('crm_quotes', 'deleted_at') ? ' AND deleted_at IS NULL' : '';
    }

    private function linkDocument(array $document, array $quote, $strategy): void
    {
        $isManual = $strategy === 'explicit_verified_quote';
        $actorId = function_exists('current_user_id') ? (int)current_user_id() : 0;
        $stmt = $this->pdo->prepare("
            UPDATE facto_documents
            SET crm_quote_id=?,
                crm_opportunity_id=?,
                crm_customer_id=?,
                link_status=?,
                link_confidence=100,
                linked_by_user_id=COALESCE(?,linked_by_user_id),
                linked_at=NOW(),
                updated_at=NOW()
            WHERE id=?
        ");
        $stmt->execute(array(
            (int)$quote['id'],
            !empty($quote['opportunity_id']) ? (int)$quote['opportunity_id'] : null,
            !empty($quote['customer_id']) ? (int)$quote['customer_id'] : null,
            $isManual ? 'manual_linked' : 'auto_linked',
            $isManual && $actorId > 0 ? $actorId : null,
            (int)$document['id']
        ));

        $stmt = $this->pdo->prepare("
            UPDATE billing_collections
            SET quote_id=?,
                opportunity_id=?,
                customer_id=?,
                updated_at=NOW()
            WHERE environment=? AND BINARY facto_document_id=BINARY ?
        ");
        $stmt->execute(array(
            (int)$quote['id'],
            !empty($quote['opportunity_id']) ? (int)$quote['opportunity_id'] : null,
            !empty($quote['customer_id']) ? (int)$quote['customer_id'] : null,
            (string)$document['environment'],
            (string)$document['document_id']
        ));

        if ($this->tableExists('billing_document_lines') && $this->columnExists('billing_document_lines', 'quote_id')) {
            $sets = array('quote_id=?');
            $params = array((int)$quote['id']);
            if ($this->columnExists('billing_document_lines', 'finance_center_id') && !empty($document['finance_center_id'])) {
                $sets[] = 'finance_center_id=COALESCE(finance_center_id,?)';
                $params[] = (int)$document['finance_center_id'];
            }
            $params[] = (string)$document['environment'];
            $params[] = (string)$document['document_id'];
            $this->pdo->prepare('UPDATE billing_document_lines SET ' . implode(',', $sets) . ' WHERE environment=? AND BINARY facto_document_id=BINARY ?')
                ->execute($params);
            $this->ensureBillingDocumentLines($document, $quote);
        }
    }

    private function updateQuoteInvoiceMetadata($quoteId, array $document, ?array $collection): void
    {
        $sets = array();
        $params = array();
        $this->addColumnValue($sets, $params, 'crm_quotes', 'facto_document_id', (string)$document['document_id']);
        $this->addColumnValue($sets, $params, 'crm_quotes', 'invoice_number', $document['document_number'] ?? null);
        $this->addColumnValue($sets, $params, 'crm_quotes', 'invoice_issue_date', $document['issue_date'] ?? null);
        $this->addColumnValue($sets, $params, 'crm_quotes', 'billing_environment', $document['environment']);
        $this->addColumnValue($sets, $params, 'crm_quotes', 'billing_document_type_id', $document['document_type_id'] ?? null);
        $this->addColumnValue($sets, $params, 'crm_quotes', 'billing_document_type', $this->documentTypeKey((int)($document['document_type_id'] ?? 0)));
        $this->addColumnValue($sets, $params, 'crm_quotes', 'billing_currency_code', $document['currency_code'] ?? null);
        $this->addColumnValue($sets, $params, 'crm_quotes', 'billing_exchange_rate_value', $document['exchange_rate_value'] ?? null);
        $this->addColumnValue($sets, $params, 'crm_quotes', 'billing_exchange_rate_date', $document['exchange_rate_date'] ?? null);
        $this->addColumnValue($sets, $params, 'crm_quotes', 'billing_exchange_rate_source', $document['exchange_rate_source'] ?? null);
        $this->addColumnValue($sets, $params, 'crm_quotes', 'billing_exchange_rate_status', $document['exchange_rate_status'] ?? null);
        $this->addColumnValue($sets, $params, 'crm_quotes', 'billing_receiver_country_id', $document['receiver_country_id'] ?? null);
        $this->addColumnValue($sets, $params, 'crm_quotes', 'billing_sale_modality', $document['sale_modality'] ?? null);
        $this->addColumnValue($sets, $params, 'crm_quotes', 'billing_sale_clause', $document['sale_clause'] ?? null);
        $this->addColumnValue($sets, $params, 'crm_quotes', 'finance_center_id', $document['finance_center_id'] ?? null);
        $this->addColumnValue($sets, $params, 'crm_quotes', 'billing_match_status', 'matched');

        $financialStatus = $collection['collection_status'] ?? ($document['payment_status'] ?? null);
        if ($financialStatus === 'parcial') {
            $financialStatus = 'por_cobrar';
        }
        $this->addColumnValue($sets, $params, 'crm_quotes', 'payment_status', $financialStatus);
        $this->addColumnValue($sets, $params, 'crm_quotes', 'payment_due_date', $collection['due_date'] ?? ($document['payment_due_date'] ?? null));
        $this->addColumnValue($sets, $params, 'crm_quotes', 'payment_paid_date', $this->paymentDate($document, $collection));
        $this->addColumnValue($sets, $params, 'crm_quotes', 'payment_terms_selected', $collection['payment_terms'] ?? null);
        $this->addColumnValue($sets, $params, 'crm_quotes', 'payment_amount', $financialStatus === 'pagado' ? ($collection['paid_amount'] ?? $document['total_amount']) : null);

        if ($this->columnExists('crm_quotes', 'facto_linked_at')) {
            $sets[] = 'facto_linked_at=COALESCE(facto_linked_at,NOW())';
        }
        if ($this->columnExists('crm_quotes', 'facto_linked_by') && function_exists('current_user_id') && (int)current_user_id() > 0) {
            $sets[] = 'facto_linked_by=COALESCE(facto_linked_by,?)';
            $params[] = (int)current_user_id();
        }
        if ($this->columnExists('crm_quotes', 'emitted_at')) {
            $sets[] = 'emitted_at=COALESCE(emitted_at,?)';
            $params[] = !empty($document['issue_date']) ? $document['issue_date'] . ' 00:00:00' : date('Y-m-d H:i:s');
        }
        if (empty($sets)) {
            return;
        }
        $params[] = (int)$quoteId;
        $stmt = $this->pdo->prepare('UPDATE crm_quotes SET ' . implode(', ', $sets) . ' WHERE id=?');
        $stmt->execute($params);
    }

    private function addColumnValue(array &$sets, array &$params, $table, $column, $value): void
    {
        if (!$this->columnExists($table, $column)) {
            return;
        }
        $sets[] = '`' . $column . '`=?';
        $params[] = $value;
    }

    private function loadCollection($environment, $documentId): ?array
    {
        $stmt = $this->pdo->prepare("
            SELECT * FROM billing_collections
            WHERE environment=? AND direction='receivable' AND BINARY facto_document_id=BINARY ?
            LIMIT 1
        ");
        $stmt->execute(array($environment, $documentId));
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    private function targetQuoteStatus(array $document, ?array $collection): string
    {
        $paymentStatus = strtolower(trim((string)($collection['collection_status'] ?? ($document['payment_status'] ?? ''))));
        if ($paymentStatus === 'pagado') {
            return 'Pagada';
        }
        if (in_array($paymentStatus, array('por_cobrar', 'parcial', 'atrasado'), true)) {
            return 'Esperando pago';
        }
        return 'Facturada';
    }

    private function paymentDate(array $document, ?array $collection): ?string
    {
        $date = $collection['last_payment_date'] ?? ($document['payment_paid_date'] ?? null);
        $date = trim((string)$date);
        return $date !== '' ? $date : null;
    }

    private function assertQuoteDocumentIdentity(array $document, array $quote): void
    {
        $errors = array();
        $status = trim((string)($quote['status'] ?? ''));
        if (!in_array($status, array('Aceptada', 'Facturada', 'Esperando pago', 'Pagada'), true)) {
            $errors[] = 'estado de cotizacion no facturable';
        }

        $documentCustomerId = (int)($document['crm_customer_id'] ?? 0);
        $quoteCustomerId = (int)($quote['customer_id'] ?? 0);
        if ($documentCustomerId > 0 && $quoteCustomerId > 0 && $documentCustomerId !== $quoteCustomerId) {
            $errors[] = 'cliente distinto';
        }

        $documentTaxId = $this->normalizeTaxId($document['receiver_tax_id_code'] ?? '');
        $quoteTaxId = $this->normalizeTaxId($quote['reconciliation_customer_tax_id'] ?? '');
        if ($documentTaxId === '' || $quoteTaxId === '' || !hash_equals($documentTaxId, $quoteTaxId)) {
            $errors[] = 'RUT receptor distinto';
        }

        $issueDate = trim((string)($document['issue_date'] ?? ''));
        $quoteDate = !empty($quote['created_at']) ? substr((string)$quote['created_at'], 0, 10) : '';
        if ($issueDate === '' || ($quoteDate !== '' && $issueDate < $quoteDate)) {
            $errors[] = 'fecha de emision anterior a la cotizacion';
        }

        $typeId = (int)($document['document_type_id'] ?? 0);
        $configuredTypeId = (int)($quote['billing_document_type_id'] ?? 0);
        if ($configuredTypeId > 0 && $configuredTypeId !== $typeId) {
            $errors[] = 'tipo de documento distinto';
        } elseif ($typeId === 2 && abs((float)($quote['iva'] ?? 0)) < 0.01) {
            $errors[] = 'factura afecta sin IVA en la cotizacion';
        } elseif ($typeId === 32 && abs((float)($quote['iva'] ?? 0)) > 1) {
            $errors[] = 'factura exenta con IVA en la cotizacion';
        }

        $currency = strtoupper(trim((string)($document['currency_code'] ?? 'CLP')));
        if ($currency === '' || $currency === 'CLP') {
            $quoteNet = (float)($quote['subtotal'] ?? 0) - (float)($quote['discount_amount'] ?? 0);
            if (!$this->sameAmount($quoteNet, $this->documentAmount($document, 'net'))) $errors[] = 'monto neto distinto';
            if (!$this->sameAmount((float)($quote['iva'] ?? 0), $this->documentAmount($document, 'taxes'))) $errors[] = 'IVA distinto';
            if (!$this->sameAmount((float)($quote['total'] ?? 0), $this->documentAmount($document, 'total'))) $errors[] = 'total distinto';
        }

        if (!empty($errors)) {
            throw new RuntimeException('La factura y la cotizacion no superan la validacion de identidad (' . implode(', ', $errors) . '). No se modifico ningun vinculo.');
        }
    }

    private function assertRemoteDocumentIdentity(array $document, array $remote): void
    {
        if (isset($remote['document']) && is_array($remote['document'])) {
            $remote = $remote['document'];
        }
        $errors = array();
        $remoteId = trim((string)$this->payloadValue($remote, 'document_id'));
        if ($remoteId === '' || !hash_equals((string)$document['document_id'], $remoteId)) $errors[] = 'document_id';
        $remoteStatus = (int)$this->payloadValue($remote, 'document_status');
        if (!in_array($remoteStatus, array(1, 2, 3), true)) $errors[] = 'estado emitido';
        if ((int)$this->payloadValue($remote, 'received_issued_flag') !== 1) $errors[] = 'direccion emitida';
        if ((int)$this->payloadValue($remote, 'document_type_id') !== (int)$document['document_type_id']) $errors[] = 'tipo';
        if ($this->normalizeTaxId($this->payloadValue($remote, 'receiver_tax_id_code')) !== $this->normalizeTaxId($document['receiver_tax_id_code'] ?? '')) $errors[] = 'RUT';
        if (substr((string)$this->payloadValue($remote, 'issue_date'), 0, 10) !== (string)$document['issue_date']) $errors[] = 'fecha';
        foreach (array('net_amount' => 'net', 'taxes_amount' => 'taxes', 'total_amount' => 'total') as $remoteKey => $localKey) {
            if (!$this->sameAmount((float)$this->payloadValue($remote, $remoteKey), $this->documentAmount($document, $localKey, false))) {
                $errors[] = $remoteKey;
            }
        }
        if (!empty($errors)) {
            throw new RuntimeException('La respuesta remota no coincide con el documento local (' . implode(', ', $errors) . '). No se modifico ningun vinculo.');
        }
    }

    private function updateOpportunityInvoiceMetadata(array $quote, array $document, ?array $collection): void
    {
        $opportunityId = (int)($quote['opportunity_id'] ?? 0);
        if ($opportunityId <= 0) return;
        $stmt = $this->pdo->prepare("UPDATE crm_opportunities SET
            facto_document_id=CASE WHEN facto_document_id IS NULL OR facto_document_id='' OR BINARY facto_document_id=BINARY ? THEN ? ELSE facto_document_id END,
            payment_due_date=?, payment_paid_date=?, finance_center_id=COALESCE(finance_center_id,?), updated_at=NOW()
            WHERE id=?");
        $stmt->execute(array(
            (string)$document['document_id'],
            (string)$document['document_id'],
            $collection['due_date'] ?? ($document['payment_due_date'] ?? null),
            $this->paymentDate($document, $collection),
            $document['finance_center_id'] ?? null,
            $opportunityId,
        ));
    }

    private function ensureCrmInvoiceDocument(array $quote, array $document): void
    {
        if (!$this->tableExists('crm_documents')) return;
        $stmt = $this->pdo->prepare('SELECT id FROM crm_documents WHERE BINARY facto_document_id=BINARY ? LIMIT 1 FOR UPDATE');
        $stmt->execute(array((string)$document['document_id']));
        $existingId = (int)$stmt->fetchColumn();
        $path = $document['pdf_file_path'] ?? null;
        if ($existingId > 0) {
            $this->pdo->prepare("UPDATE crm_documents SET opportunity_id=COALESCE(opportunity_id,?), customer_id=COALESCE(customer_id,?), quote_id=COALESCE(quote_id,?), document_number=COALESCE(document_number,?), file_path=COALESCE(file_path,?), link_status='auto', finance_center_id=COALESCE(finance_center_id,?) WHERE id=?")
                ->execute(array($quote['opportunity_id'] ?? null, $quote['customer_id'] ?? null, $quote['id'], $document['document_number'] ?? null, $path, $document['finance_center_id'] ?? null, $existingId));
            return;
        }
        $this->pdo->prepare("INSERT INTO crm_documents (opportunity_id,customer_id,quote_id,document_type,document_name,document_number,document_date,net_amount,total_amount,file_path,facto_document_id,link_status,finance_center_id,notes) VALUES (?,?,?,'factura',?,?,?,?,?,?,?,'auto',?,?)")
            ->execute(array(
                $quote['opportunity_id'] ?? null,
                $quote['customer_id'] ?? null,
                $quote['id'],
                'Factura FACTO ' . (($document['document_number'] ?? '') ?: $document['document_id']),
                $document['document_number'] ?? null,
                $document['issue_date'] ?? null,
                $this->documentAmount($document, 'net'),
                $this->documentAmount($document, 'total'),
                $path,
                (string)$document['document_id'],
                $document['finance_center_id'] ?? null,
                'Documento tributario conciliado con ' . ($quote['quote_number'] ?? ('cotizacion #' . $quote['id'])) . '.',
            ));
    }

    private function ensureBillingDocumentLines(array $document, array $quote): void
    {
        $count = $this->pdo->prepare('SELECT COUNT(*) FROM billing_document_lines WHERE environment=? AND BINARY facto_document_id=BINARY ?');
        $count->execute(array((string)$document['environment'], (string)$document['document_id']));
        if ((int)$count->fetchColumn() > 0) return;

        $stmt = $this->pdo->prepare("SELECT qi.*, p.sku AS product_sku
            FROM crm_quote_items qi
            LEFT JOIN products p ON p.id=qi.product_id
            WHERE qi.quote_id=? ORDER BY qi.id ASC");
        $stmt->execute(array((int)$quote['id']));
        $items = $stmt->fetchAll(PDO::FETCH_ASSOC);
        if (empty($items)) return;

        $typeId = (int)($document['document_type_id'] ?? 0);
        $isExport = $typeId === 46;
        $calculation = (new BillingQuoteCalculator($this->pdo))->calculate(
            $quote,
            $items,
            array(
                'is_export' => $isExport ? 1 : 0,
                'is_tax_exempt' => in_array($typeId, array(32, 46), true) ? 1 : 0,
                'document_family' => 'invoice',
            ),
            array(
                'currency_code' => $document['currency_code'] ?? 'CLP',
                'export_pricing_mode' => 'convert_from_clp',
                'exchange_rate_value' => $document['exchange_rate_value'] ?? 0,
            )
        );
        if (!empty($calculation['errors'])) return;

        $preferClp = !$isExport || strtoupper((string)($document['currency_code'] ?? 'CLP')) === 'CLP';
        if (!$this->sameAmount($calculation['net_amount'] ?? 0, $this->documentAmount($document, 'net', $preferClp))
            || !$this->sameAmount($calculation['taxes_amount'] ?? 0, $this->documentAmount($document, 'taxes', $preferClp))
            || !$this->sameAmount($calculation['total_amount'] ?? 0, $this->documentAmount($document, 'total', $preferClp))) {
            return;
        }

        $insert = $this->pdo->prepare("INSERT INTO billing_document_lines
            (environment,facto_document_id,quote_id,source_line_id,product_id,sku,description,quantity,unit_measure,unit_price,discount_percent,discount_amount,net_amount,tax_amount,total_amount,tax_treatment,finance_center_id,raw_payload)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        foreach (($calculation['lines'] ?? array()) as $line) {
            $insert->execute(array(
                (string)$document['environment'],
                (string)$document['document_id'],
                (int)$quote['id'],
                $line['source_item_id'] ?? null,
                $line['product_id'] ?? null,
                $line['sku'] ?? null,
                $line['name'] ?? ($line['description'] ?? ''),
                (float)($line['quantity'] ?? 0),
                $line['unit_measure'] ?? 'UN',
                (float)($line['unit_price'] ?? 0),
                (float)($line['line_discount_percent'] ?? 0),
                (float)($line['line_discount_amount'] ?? 0) + (float)($line['global_discount_allocated'] ?? 0),
                (float)($line['net_amount'] ?? 0),
                (float)($line['tax_amount'] ?? 0),
                (float)($line['gross_amount'] ?? 0),
                $line['tax_treatment'] ?? 'afecto',
                $line['finance_center_id'] ?? ($document['finance_center_id'] ?? null),
                json_encode($line, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
            ));
        }
    }

    private function auditLink(array $document, array $quote, $strategy, array $before): void
    {
        if (!$this->tableExists('billing_link_audit_logs')) return;
        $alreadyLinked = !empty($before['document_quote_id']) && (int)$before['document_quote_id'] === (int)$quote['id'];
        if ($alreadyLinked) return;
        $actorId = function_exists('current_user_id') ? (int)current_user_id() : 0;
        $after = array(
            'document_id' => (string)$document['document_id'],
            'quote_id' => (int)$quote['id'],
            'opportunity_id' => !empty($quote['opportunity_id']) ? (int)$quote['opportunity_id'] : null,
            'strategy' => $strategy,
        );
        $this->pdo->prepare('INSERT INTO billing_link_audit_logs (environment,facto_document_id,quote_id,opportunity_id,action_key,strategy,before_json,after_json,actor_user_id) VALUES (?,?,?,?,?,?,?,?,?)')
            ->execute(array(
                (string)$document['environment'],
                (string)$document['document_id'],
                (int)$quote['id'],
                !empty($quote['opportunity_id']) ? (int)$quote['opportunity_id'] : null,
                'link_verified',
                $strategy,
                json_encode($before, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                json_encode($after, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                $actorId ?: null,
            ));
    }

    private function extractQuoteNumber($value, $depth = 0): string
    {
        if ($depth > 8) return '';
        if (is_string($value)) {
            if (preg_match('/\\bCOT-\\d{4}-\\d+\\b/i', $value, $matches)) return strtoupper($matches[0]);
            $decoded = json_decode($value, true);
            return is_array($decoded) ? $this->extractQuoteNumber($decoded, $depth + 1) : '';
        }
        if (!is_array($value)) return '';
        foreach ($value as $child) {
            $found = $this->extractQuoteNumber($child, $depth + 1);
            if ($found !== '') return $found;
        }
        return '';
    }

    private function payloadValue(array $payload, $key)
    {
        if (array_key_exists($key, $payload)) return $payload[$key];
        foreach (array('header', 'totals') as $section) {
            if (isset($payload[$section]) && is_array($payload[$section]) && array_key_exists($key, $payload[$section])) return $payload[$section][$key];
        }
        return null;
    }

    private function normalizeTaxId($value): string
    {
        return strtoupper((string)preg_replace('/[^0-9K]/i', '', (string)$value));
    }

    private function sameAmount($left, $right): bool
    {
        return abs((float)$left - (float)$right) <= 1.0;
    }

    private function documentAmount(array $document, $kind, $preferClp = true): float
    {
        $base = $kind . '_amount';
        $clp = $kind . '_amount_clp';
        if ($preferClp && isset($document[$clp]) && $document[$clp] !== null && $document[$clp] !== '') return (float)$document[$clp];
        return (float)($document[$base] ?? 0);
    }

    private function documentTypeKey($typeId): ?string
    {
        $map = array(2 => 'invoice_electronic', 32 => 'invoice_exempt_electronic', 46 => 'invoice_export_electronic');
        return $map[(int)$typeId] ?? null;
    }

    private function tableExists($table): bool
    {
        $key = 'table.' . $table;
        if (array_key_exists($key, $this->columnCache)) return $this->columnCache[$key];
        $stmt = $this->pdo->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name=?');
        $stmt->execute(array($table));
        $this->columnCache[$key] = (int)$stmt->fetchColumn() > 0;
        return $this->columnCache[$key];
    }

    private function columnExists($table, $column): bool
    {
        $key = $table . '.' . $column;
        if (array_key_exists($key, $this->columnCache)) {
            return $this->columnCache[$key];
        }
        $stmt = $this->pdo->prepare('SHOW COLUMNS FROM `' . str_replace('`', '', $table) . '` LIKE ?');
        $stmt->execute(array($column));
        $this->columnCache[$key] = (bool)$stmt->fetch(PDO::FETCH_ASSOC);
        return $this->columnCache[$key];
    }
}
