<?php

require_once __DIR__ . '/../../core/database.php';
require_once __DIR__ . '/BillingPaymentOptionsService.php';
require_once __DIR__ . '/BillingPaymentTermsService.php';
require_once __DIR__ . '/BillingRuntimeGate.php';
require_once __DIR__ . '/../finance/ExchangeRateService.php';

class BillingReceivablesService
{
    private $pdo;
    private $exchangeRates;

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

    /**
     * Las cuentas por cobrar nacen exclusivamente desde facturas.
     * Guías y notas complementarias se trazan en sus propios módulos y nunca
     * deben inicializar una colección financiera independiente.
     */
    private function isReceivableDocumentType($documentTypeId)
    {
        return in_array((int)$documentTypeId, array(2, 32, 46), true);
    }

    /**
     * Expresiones de cobranza neta.
     *
     * billing_collections conserva importes y pagos brutos porque representan
     * movimientos de caja reales. Los indicadores de cobranza se expresan en
     * neto aplicando al neto del DTE la misma proporción que permanece abierta
     * sobre el total bruto. Esto mantiene correctos los pagos parciales.
     */
    private function receivableNetSqlExpressions()
    {
        $grossTotal = "GREATEST(COALESCE(c.total_amount, NULLIF(d.total_amount_clp, 0), CASE WHEN UPPER(COALESCE(d.currency_code, 'CLP')) <> 'CLP' THEN d.total_amount * NULLIF(d.exchange_rate_value, 0) ELSE d.total_amount END, d.total_amount, 0), 0)";
        $grossBalance = "GREATEST(COALESCE(c.balance_amount, " . $grossTotal . "), 0)";
        $netTotal = "GREATEST(COALESCE(NULLIF(d.net_amount_clp, 0), CASE WHEN UPPER(COALESCE(d.currency_code, 'CLP')) <> 'CLP' THEN d.net_amount * NULLIF(d.exchange_rate_value, 0) ELSE d.net_amount END, d.net_amount, 0), 0)";
        $openRatio = "(CASE WHEN " . $grossTotal . " > 0 THEN LEAST(1, GREATEST(" . $grossBalance . " / NULLIF(" . $grossTotal . ", 0), 0)) ELSE 0 END)";
        $netBalance = "ROUND(" . $netTotal . " * " . $openRatio . ", 2)";
        $netPaid = "ROUND(GREATEST(" . $netTotal . " - (" . $netBalance . "), 0), 2)";
        $foreignNetTotal = "GREATEST(COALESCE(d.net_amount, 0), 0)";
        $foreignNetBalance = "ROUND(" . $foreignNetTotal . " * " . $openRatio . ", 4)";
        $foreignNetPaid = "ROUND(GREATEST(" . $foreignNetTotal . " - (" . $foreignNetBalance . "), 0), 4)";

        return array(
            'gross_total' => $grossTotal,
            'gross_balance' => $grossBalance,
            'net_total' => $netTotal,
            'net_paid' => $netPaid,
            'net_balance' => $netBalance,
            'foreign_net_total' => $foreignNetTotal,
            'foreign_net_paid' => $foreignNetPaid,
            'foreign_net_balance' => $foreignNetBalance,
        );
    }

    public function listReceivables(array $filters = array())
    {
        $environment = $this->normalizeEnvironment(isset($filters['environment']) ? $filters['environment'] : (defined('FACTO_DEFAULT_ENVIRONMENT') ? FACTO_DEFAULT_ENVIRONMENT : 'production'));
        billing_assert_runtime_operation('read', $environment, 'Listado de cuentas por cobrar');

        $status = isset($filters['status']) ? trim((string)$filters['status']) : 'open';
        $search = isset($filters['search']) ? trim((string)$filters['search']) : '';
        $dateFrom = $this->normalizeDate(isset($filters['date_from']) ? $filters['date_from'] : null);
        $dateTo = $this->normalizeDate(isset($filters['date_to']) ? $filters['date_to'] : null);
        $limit = isset($filters['limit']) ? max(10, min(5000, (int)$filters['limit'])) : 500;
        $financialTypeSql = "CAST(COALESCE(d.document_type_id, 0) AS UNSIGNED) IN (2,32,46)";
        $accountDisplayTypeSql = "CAST(COALESCE(d.document_type_id, 0) AS UNSIGNED) IN (2,32)";
        $effectiveStatusSql = "COALESCE(c.collection_status, d.payment_status, 'por_cobrar')";
        $netSql = $this->receivableNetSqlExpressions();

        // CxC muestra únicamente facturas que originan cuenta. Las facturas
        // afectas y exentas se presentan en bloques separados.
        $where = array('d.environment = ?', 'd.total_amount > 0', $accountDisplayTypeSql);
        $params = array($environment);

        if ($status === 'open') {
            $where[] = "(" . $effectiveStatusSql . " IN ('por_cobrar','atrasado','parcial'))";
        } elseif ($status !== '' && $status !== 'all') {
            $where[] = "(" . $financialTypeSql . " AND " . $effectiveStatusSql . " = ?)";
            $params[] = $status;
        }

        if ($search !== '') {
            $where[] = "(COALESCE(customer.customer_name, d.receiver_legal_name) LIKE ? OR COALESCE(customer.customer_tax_id, d.receiver_tax_id_code) LIKE ? OR d.document_number LIKE ? OR q.quote_number LIKE ?)";
            $like = '%' . $search . '%';
            array_push($params, $like, $like, $like, $like);
        }
        if ($dateFrom) { $where[] = 'd.issue_date >= ?'; $params[] = $dateFrom; }
        if ($dateTo) { $where[] = 'd.issue_date <= ?'; $params[] = $dateTo; }

        $sql = "
            SELECT
                d.id, d.environment, d.document_id, d.issue_date, d.document_number,
                d.document_type_id, d.document_type_taxbureau, d.receiver_legal_name,
                d.receiver_tax_id_code, d.crm_customer_id, d.crm_quote_id,
                d.crm_opportunity_id, d.finance_center_id, d.net_amount,
                d.taxes_amount, d.total_amount, d.net_amount_clp, d.taxes_amount_clp, d.total_amount_clp,
                d.currency_code, d.currency_id, d.exchange_rate_value, d.exchange_rate_date,
                d.exchange_rate_source, d.exchange_rate_status, d.payment_status,
                d.payment_due_date, d.payment_paid_date, d.pdf_file_path, d.xml_file_path,
                COALESCE(bdt.document_label, CONCAT('DTE ', COALESCE(d.document_type_taxbureau, d.document_type_id))) AS document_label,
                COALESCE(bdt.document_family, 'other') AS document_family,
                bdt.document_key,
                CASE WHEN " . $financialTypeSql . " THEN 1 ELSE 0 END AS is_financial_document,
                CASE WHEN CAST(COALESCE(d.document_type_id,0) AS UNSIGNED)=32 THEN 1 ELSE 0 END AS is_tax_exempt,
                CASE WHEN CAST(COALESCE(d.document_type_id,0) AS UNSIGNED)=32 THEN 'invoice_exempt' ELSE 'invoice_affected' END AS account_document_group,
                customer.customer_name, customer.customer_tax_id,
                customer.payment_terms AS customer_payment_terms, customer.facto_payment_term,
                customer.default_income_center_id, q.quote_number, q.status AS quote_status,
                q.payment_due_date AS quote_payment_due_date, q.payment_terms_selected,
                o.title AS opportunity_title,
                COALESCE(d.finance_center_id, customer.default_income_center_id) AS effective_finance_center_id,
                COALESCE(fc_doc.center_name, fc_customer.center_name) AS finance_center_name,
                c.id AS collection_id, c.collection_status, c.due_date,
                c.total_amount AS collection_total, COALESCE(c.paid_amount, 0) AS paid_amount,
                CASE WHEN " . $financialTypeSql . " THEN COALESCE(c.balance_amount, d.total_amount_clp, d.total_amount) ELSE 0 END AS balance_amount,
                CASE WHEN " . $financialTypeSql . " THEN " . $netSql['net_total'] . " ELSE 0 END AS collection_net_total,
                CASE WHEN " . $financialTypeSql . " THEN " . $netSql['net_paid'] . " ELSE 0 END AS paid_net_amount,
                CASE WHEN " . $financialTypeSql . " THEN " . $netSql['net_balance'] . " ELSE 0 END AS net_balance_amount,
                CASE WHEN " . $financialTypeSql . " THEN " . $netSql['foreign_net_total'] . " ELSE 0 END AS foreign_net_total,
                CASE WHEN " . $financialTypeSql . " THEN " . $netSql['foreign_net_paid'] . " ELSE 0 END AS foreign_paid_net_amount,
                CASE WHEN " . $financialTypeSql . " THEN " . $netSql['foreign_net_balance'] . " ELSE 0 END AS foreign_net_balance_amount,
                c.currency_code AS collection_currency_code, c.currency_id AS collection_currency_id,
                c.exchange_rate_value AS collection_exchange_rate_value, c.exchange_rate_date AS collection_exchange_rate_date,
                c.exchange_rate_source AS collection_exchange_rate_source, c.exchange_rate_status AS collection_exchange_rate_status,
                c.foreign_total_amount, c.foreign_paid_amount, c.foreign_balance_amount,
                c.last_payment_date, c.notes AS collection_notes
            FROM facto_documents d
            LEFT JOIN billing_document_types bdt
              ON bdt.direction = 'issued' AND bdt.facto_document_type_id = CAST(d.document_type_id AS UNSIGNED)
            LEFT JOIN crm_customers customer ON customer.id = d.crm_customer_id
            LEFT JOIN crm_quotes q ON q.id = d.crm_quote_id
            LEFT JOIN crm_opportunities o ON o.id = d.crm_opportunity_id
            LEFT JOIN finance_centers fc_doc ON fc_doc.id = d.finance_center_id
            LEFT JOIN finance_centers fc_customer ON fc_customer.id = customer.default_income_center_id
            LEFT JOIN billing_collections c ON c.environment = d.environment
                AND c.direction = 'receivable' AND c.facto_document_id = d.document_id
            WHERE " . implode(' AND ', $where) . "
            ORDER BY FIELD(COALESCE(bdt.document_family,'other'),'invoice','export','credit_note','debit_note','dispatch_guide','receipt','other'),
                     COALESCE(c.due_date, d.payment_due_date, d.issue_date) ASC, d.id DESC
            LIMIT " . (int)$limit;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return array('environment' => $environment, 'rows' => $stmt->fetchAll(PDO::FETCH_ASSOC), 'kpis' => $this->getKpis($environment));
    }

    public function getDocument($idOrDocumentId, $environment = 'sandbox')
    {
        $environment = $this->normalizeEnvironment($environment);
        billing_assert_runtime_operation('read', $environment, 'Detalle de cuenta por cobrar');

        // Las vistas envian facto_documents.id. Se evita mezclarlo con document_id para no cruzar folios.
        $row = $this->fetchDocumentByCondition('d.id = ?', array((string)$idOrDocumentId), $environment);
        if (!$row) {
            $row = $this->fetchDocumentByCondition('d.document_id = ?', array((string)$idOrDocumentId), $environment);
        }
        if (!$row) {
            throw new Exception('Documento emitido no encontrado.');
        }
        // v6.12: abrir el detalle no crea una CxC ni modifica relaciones.
        // Las escrituras quedan reservadas para acciones financieras explicitas
        // como actualizar el documento o registrar un pago.
        return $row;
    }

    public function getPaymentHistory($idOrDocumentId, $environment = 'sandbox')
    {
        $document = $this->getDocument($idOrDocumentId, $environment);
        if (empty($document['collection_id'])) {
            return array();
        }

        $fxSelect = billing_table_has_column($this->pdo, 'billing_payments', 'currency_code')
            ? "p.currency_code, p.exchange_rate_value, p.exchange_rate_date, p.exchange_rate_source, p.exchange_rate_status, p.foreign_amount,"
            : "NULL AS currency_code, NULL AS exchange_rate_value, NULL AS exchange_rate_date, NULL AS exchange_rate_source, NULL AS exchange_rate_status, NULL AS foreign_amount,";
        $voidSelect = billing_table_has_column($this->pdo, 'billing_payments', 'is_voided')
            ? "p.is_voided, p.voided_at, p.voided_by, p.void_reason,"
            : "0 AS is_voided, NULL AS voided_at, NULL AS voided_by, NULL AS void_reason,";

        $stmt = $this->pdo->prepare("
            SELECT p.id,
                   p.payment_date,
                   p.amount,
                   " . $fxSelect . "
                   " . $voidSelect . "
                   p.payment_method,
                   p.payment_method_id,
                   p.cash_account_id,
                   p.external_reference,
                   p.notes,
                   p.created_at,
                   pm.method_name,
                   ca.account_name,
                   b.id AS batch_id,
                   b.amount_total AS batch_total,
                   b.external_reference AS batch_reference
            FROM billing_payments p
            LEFT JOIN billing_payment_methods pm ON pm.id = p.payment_method_id
            LEFT JOIN billing_cash_accounts ca ON ca.id = p.cash_account_id
            LEFT JOIN billing_payment_batches b ON b.id = p.payment_batch_id
            WHERE p.collection_id = ? AND p.direction = 'received'
            ORDER BY p.payment_date DESC, p.id DESC
        ");
        $stmt->execute(array((int)$document['collection_id']));
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getOptions($environment = 'sandbox')
    {
        $environment = $this->normalizeEnvironment($environment);
        billing_assert_runtime_operation('read', $environment, 'Opciones de cuentas por cobrar');
        $paymentOptions = new BillingPaymentOptionsService($this->pdo);
        return array(
            'environment' => $environment,
            'finance_centers' => $this->listIncomeCenters(),
            'payment_methods' => $paymentOptions->listPaymentMethods(),
            'cash_accounts' => $paymentOptions->listCashAccounts(),
            'statuses' => array(
                array('value' => 'open', 'label' => 'Impagos / parciales'),
                array('value' => 'por_cobrar', 'label' => 'Por cobrar'),
                array('value' => 'atrasado', 'label' => 'Atrasado'),
                array('value' => 'parcial', 'label' => 'Parcial'),
                array('value' => 'pagado', 'label' => 'Pagado'),
                array('value' => 'incobrable', 'label' => 'Incobrable'),
                array('value' => 'anulado', 'label' => 'Anulado'),
            ),
        );
    }

    /**
     * Anula el efecto financiero de un pago sin eliminar su evidencia.
     * El saldo y los estados comerciales se recalculan desde pagos activos.
     */
    public function voidPayment($paymentId, array $data, $userId = null)
    {
        $environment = $this->normalizeEnvironment(isset($data['environment']) ? $data['environment'] : 'sandbox');
        billing_assert_runtime_operation('finance', $environment, 'Anulación de pago recibido');
        if (!billing_table_has_column($this->pdo, 'billing_payments', 'is_voided')) {
            throw new RuntimeException('La reversa auditada de pagos aún no está instalada. Ejecuta primero la migración v9.0.4.');
        }

        $paymentId = (int)$paymentId;
        $reason = trim((string)($data['reason'] ?? ''));
        if ($paymentId <= 0) throw new InvalidArgumentException('Pago inválido.');
        if (mb_strlen($reason) < 8) throw new InvalidArgumentException('Describe el motivo de la anulación con al menos 8 caracteres.');
        if (mb_strlen($reason) > 500) throw new InvalidArgumentException('El motivo de anulación no puede superar 500 caracteres.');

        $this->pdo->beginTransaction();
        try {
            $stmt = $this->pdo->prepare("SELECT * FROM billing_payments WHERE id = ? AND environment = ? AND direction = 'received' LIMIT 1 FOR UPDATE");
            $stmt->execute(array($paymentId, $environment));
            $payment = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$payment) throw new RuntimeException('Pago recibido no encontrado.');
            if (!empty($payment['is_voided'])) throw new RuntimeException('Este pago ya está anulado.');
            if (empty($payment['collection_id'])) throw new RuntimeException('El pago no tiene una cuenta por cobrar vinculada.');

            $collection = $this->getCollectionById((int)$payment['collection_id']);
            if (!$collection || $collection['environment'] !== $environment || $collection['direction'] !== 'receivable') {
                throw new RuntimeException('La cuenta por cobrar vinculada al pago no es válida.');
            }

            $tableStmt = $this->pdo->prepare("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'treasury_statement_rows'");
            $tableStmt->execute();
            if ((int)$tableStmt->fetchColumn() === 1) {
                $matchStmt = $this->pdo->prepare("SELECT id FROM treasury_statement_rows WHERE matched_payment_id = ? AND reconciliation_status = 'matched' LIMIT 1");
                $matchStmt->execute(array($paymentId));
                if ($matchStmt->fetchColumn()) {
                    throw new RuntimeException('El pago está conciliado con una cartola bancaria. Desconcílialo antes de anularlo.');
                }
            }

            $before = $payment;
            $stmt = $this->pdo->prepare('UPDATE billing_payments SET is_voided = 1, voided_at = NOW(), voided_by = ?, void_reason = ? WHERE id = ? AND is_voided = 0');
            $stmt->execute(array($this->nullableInt($userId), $reason, $paymentId));
            if ($stmt->rowCount() !== 1) throw new RuntimeException('El pago cambió mientras se procesaba la anulación. Actualiza la pantalla.');

            if (!empty($payment['payment_batch_id'])) {
                $batchStmt = $this->pdo->prepare("UPDATE billing_payment_batches b SET amount_allocated = (SELECT COALESCE(SUM(p.amount),0) FROM billing_payments p WHERE p.payment_batch_id = b.id AND p.is_voided = 0) WHERE b.id = ?");
                $batchStmt->execute(array((int)$payment['payment_batch_id']));
            }

            $updatedCollection = $this->recalculateCollection((int)$collection['id']);
            $this->syncRelatedCommercialStatus((int)$collection['id'], 'Pago anulado: ' . $reason, $userId, false);

            if (billing_table_has_column($this->pdo, 'billing_payment_audit_logs', 'payment_id')) {
                $afterStmt = $this->pdo->prepare('SELECT * FROM billing_payments WHERE id = ? LIMIT 1');
                $afterStmt->execute(array($paymentId));
                $after = $afterStmt->fetch(PDO::FETCH_ASSOC);
                $auditStmt = $this->pdo->prepare('INSERT INTO billing_payment_audit_logs (payment_id, action_key, before_json, after_json, actor_user_id) VALUES (?, ?, ?, ?, ?)');
                $auditStmt->execute(array(
                    $paymentId,
                    'void',
                    json_encode($before, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                    json_encode($after, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                    $this->nullableInt($userId),
                ));
            }

            if (!empty($updatedCollection['customer_id'])) {
                $title = 'Pago anulado - factura ' . ($updatedCollection['document_number'] ?: $updatedCollection['facto_document_id']);
                $description = 'Pago anulado: $' . number_format((float)$payment['amount'], 0, ',', '.') . "\nMotivo: " . $reason . "\nNuevo saldo: $" . number_format((float)$updatedCollection['balance_amount'], 0, ',', '.');
                $activity = $this->pdo->prepare("INSERT INTO crm_activities (opportunity_id, customer_id, customer_name, activity_type, title, description, activity_date, owner_name) SELECT ?, c.id, c.customer_name, 'factura', ?, ?, NOW(), ? FROM crm_customers c WHERE c.id = ? LIMIT 1");
                $activity->execute(array($updatedCollection['opportunity_id'] ?: null, $title, $description, $this->userName($userId), (int)$updatedCollection['customer_id']));
            }

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

        $doc = $this->getDocument($collection['facto_document_id'], $environment);
        return array(
            'document' => $doc,
            'payments' => $this->getPaymentHistory($doc['id'], $environment),
            'collection' => $this->getCollectionById((int)$collection['id']),
        );
    }

    public function reopenAsReceivable($idOrDocumentId, array $data, $userId = null)
    {
        $environment = $this->normalizeEnvironment(isset($data['environment']) ? $data['environment'] : 'sandbox');
        billing_assert_runtime_operation('finance', $environment, 'Reapertura de cuenta por cobrar');
        if (!billing_table_has_column($this->pdo, 'billing_payments', 'is_voided')) {
            throw new RuntimeException('La reapertura segura aún no está instalada. Ejecuta primero la migración v9.0.4.');
        }
        $reason = trim((string)($data['reason'] ?? ''));
        if (mb_strlen($reason) < 8) throw new InvalidArgumentException('Describe el motivo de la reapertura con al menos 8 caracteres.');

        $doc = $this->getDocument($idOrDocumentId, $environment);
        $collection = $this->getCollectionByDocumentId((string)$doc['document_id'], $environment);
        if (!$collection) throw new RuntimeException('La factura no tiene una cuenta por cobrar materializada.');

        $this->pdo->beginTransaction();
        try {
            $locked = $this->pdo->prepare("SELECT * FROM billing_collections WHERE id = ? AND environment = ? AND direction = 'receivable' LIMIT 1 FOR UPDATE");
            $locked->execute(array((int)$collection['id'], $environment));
            $collection = $locked->fetch(PDO::FETCH_ASSOC);
            if (!$collection) throw new RuntimeException('Cuenta por cobrar no encontrada.');

            $payments = $this->pdo->prepare("SELECT COUNT(*) FROM billing_payments WHERE collection_id = ? AND direction = 'received' AND COALESCE(is_voided,0)=0");
            $payments->execute(array((int)$collection['id']));
            if ((int)$payments->fetchColumn() > 0) {
                throw new RuntimeException('La cuenta tiene pagos activos. Anula primero los pagos incorrectos; el estado se recalculará automáticamente.');
            }

            $stmt = $this->pdo->prepare("UPDATE billing_collections SET paid_amount=0, balance_amount=total_amount, collection_status='por_cobrar', last_payment_date=NULL, foreign_paid_amount=CASE WHEN UPPER(COALESCE(currency_code,'CLP'))<>'CLP' THEN 0 ELSE foreign_paid_amount END, foreign_balance_amount=CASE WHEN UPPER(COALESCE(currency_code,'CLP'))<>'CLP' THEN foreign_total_amount ELSE foreign_balance_amount END, notes=CONCAT(COALESCE(notes,''), CASE WHEN COALESCE(notes,'')='' THEN '' ELSE '\n' END, '[REAPERTURA] ', ?), updated_at=NOW() WHERE id=?");
            $stmt->execute(array($reason, (int)$collection['id']));
            $collection = $this->recalculateCollection((int)$collection['id']);
            $this->syncRelatedCommercialStatus((int)$collection['id'], 'Cuenta reabierta: ' . $reason, $userId, false);
            $this->pdo->commit();
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            throw $e;
        }

        $doc = $this->getDocument($doc['id'], $environment);
        return array('document'=>$doc, 'payments'=>$this->getPaymentHistory($doc['id'], $environment), 'collection'=>$collection);
    }

    public function listIncomeCenters()
    {
        $stmt = $this->pdo->query("
            SELECT id, center_code, center_name, center_type
            FROM finance_centers
            WHERE is_active = 1 AND center_type IN ('income','both')
            ORDER BY is_default DESC, center_name ASC
        ");
        return $stmt ? $stmt->fetchAll(PDO::FETCH_ASSOC) : array();
    }

    public function updateDocument($idOrDocumentId, array $data, $userId = null)
    {
        $environment = $this->normalizeEnvironment(isset($data['environment']) ? $data['environment'] : 'sandbox');
        billing_assert_runtime_operation('finance', $environment, 'Actualización de cuenta por cobrar');
        $doc = $this->getDocument($idOrDocumentId, $environment);
        $financeCenterId = $this->nullableInt(isset($data['finance_center_id']) ? $data['finance_center_id'] : null);
        $notes = isset($data['notes']) ? trim((string)$data['notes']) : null;
        $materializeCollection = !empty($data['materialize_collection']);
        $existingCollection = $this->getCollectionByDocumentId((string)$doc['document_id'], $environment);

        $effectiveCenterId = $financeCenterId !== null
            ? $financeCenterId
            : $this->nullableInt(isset($doc['default_income_center_id']) ? $doc['default_income_center_id'] : null);

        if ($materializeCollection && empty($existingCollection)) {
            if (empty($doc['crm_customer_id'])) {
                throw new Exception('Vincula un cliente antes de crear la cuenta por cobrar.');
            }
            if ($effectiveCenterId === null) {
                throw new Exception('Selecciona un centro de ingreso antes de crear la cuenta por cobrar.');
            }
        }

        $stmt = $this->pdo->prepare('UPDATE facto_documents SET finance_center_id = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?');
        $stmt->execute(array($financeCenterId, (int)$doc['id']));

        $collection = null;
        if ($existingCollection || $materializeCollection) {
            $collection = $this->ensureReceivableCollection($doc['document_id'], $environment, array(
                'finance_center_id' => $effectiveCenterId,
                'notes' => $notes,
            ));
        }

        return array(
            'document' => $this->getDocument($doc['id'], $environment),
            'collection' => $collection,
            'materialized' => $collection !== null,
        );
    }
    public function registerPayment($idOrDocumentId, array $data, $userId = null)
    {
        $environment = $this->normalizeEnvironment(isset($data['environment']) ? $data['environment'] : 'sandbox');
        billing_assert_runtime_operation('finance', $environment, 'Registro de pago recibido');
        $doc = $this->getDocument($idOrDocumentId, $environment);
        $collection = $this->getCollectionByDocumentId((string)$doc['document_id'], $environment);
        if (!$collection) {
            throw new Exception('Crea primero la cuenta por cobrar antes de registrar un cobro.');
        }

        $expectedCollectionId = $this->nullableInt(isset($data['expected_collection_id']) ? $data['expected_collection_id'] : null);
        $expectedDocumentId = isset($data['expected_document_id']) ? trim((string)$data['expected_document_id']) : '';
        $expectedBalance = $this->normalizeExpectedBalance(isset($data['expected_balance']) ? $data['expected_balance'] : null, $environment);
        $idempotencyKey = $this->normalizePaymentIdempotencyKey(isset($data['idempotency_key']) ? $data['idempotency_key'] : null, $environment);

        if ($environment === 'production' && ($expectedCollectionId === null || $expectedDocumentId === '')) {
            throw new Exception('Faltan los identificadores esperados de la cuenta para el cobro productivo. Actualiza la pantalla.');
        }
        if ($expectedCollectionId !== null && $expectedCollectionId !== (int)$collection['id']) {
            throw new Exception('La cuenta por cobrar cambió desde que se abrió el documento. Actualiza y vuelve a intentar.');
        }
        if ($expectedDocumentId !== '' && $expectedDocumentId !== (string)$doc['document_id']) {
            throw new Exception('La identidad del documento no coincide. Actualiza y vuelve a abrir la factura.');
        }

        $amount = round((float)(isset($data['amount']) ? $data['amount'] : 0), 2);
        $paymentDate = $this->normalizeDate(isset($data['payment_date']) ? $data['payment_date'] : date('Y-m-d'));
        $paymentMethodId = $this->requiredPositiveInt(isset($data['payment_method_id']) ? $data['payment_method_id'] : null, 'Selecciona el método de pago.');
        $cashAccountId = $this->requiredPositiveInt(isset($data['cash_account_id']) ? $data['cash_account_id'] : null, 'Selecciona la caja o cuenta de ingreso.');
        $reference = isset($data['external_reference']) ? trim((string)$data['external_reference']) : '';
        $notes = isset($data['notes']) ? trim((string)$data['notes']) : null;

        if (!$paymentDate) {
            throw new Exception('Fecha de pago inválida.');
        }
        if ($environment === 'production' && $paymentDate > date('Y-m-d')) {
            throw new Exception('La fecha de un cobro productivo no puede estar en el futuro.');
        }
        if ($amount <= 0) {
            throw new Exception('El monto recibido debe ser mayor a cero.');
        }
        $this->assertProductionPaymentAuditFields($environment, $reference, $idempotencyKey);

        $options = new BillingPaymentOptionsService($this->pdo);
        $method = $options->getPaymentMethodById($paymentMethodId);
        $account = $options->getCashAccountById($cashAccountId);
        if (!$method || !$account) {
            throw new Exception('Método de pago o caja/cuenta no disponible.');
        }

        $replay = $this->findPaymentReplay(
            $environment,
            'incoming',
            $idempotencyKey,
            (int)$collection['id'],
            'received',
            (string)$doc['document_id'],
            null,
            $paymentDate,
            $amount,
            $paymentMethodId,
            $cashAccountId,
            $reference
        );
        if ($replay) {
            return array(
                'document' => $this->getDocument($doc['id'], $environment),
                'payments' => $this->getPaymentHistory($doc['id'], $environment),
                'idempotent_replay' => true,
                'payment_batch_id' => (int)$replay['batch_id'],
                'payment_id' => (int)$replay['payment_id'],
            );
        }

        $this->assertPaymentReferenceAvailable($environment, 'incoming', $cashAccountId, $reference, $idempotencyKey);

        $this->pdo->beginTransaction();
        try {
            if ($environment === 'production') {
                $this->lockPaymentAccount($cashAccountId);
                $this->assertPaymentReferenceAvailable($environment, 'incoming', $cashAccountId, $reference, $idempotencyKey);
            }
            $collection = $this->lockAndRefreshCollection((int)$collection['id']);
            if ($collection['environment'] !== $environment || $collection['direction'] !== 'receivable' || (string)$collection['facto_document_id'] !== (string)$doc['document_id']) {
                throw new Exception('La identidad financiera de la cuenta por cobrar cambió. Actualiza y vuelve a intentar.');
            }
            $balance = round((float)$collection['balance_amount'], 2);
            if ($expectedBalance !== null && abs($balance - $expectedBalance) > 0.01) {
                throw new Exception('El saldo cambió desde que se abrió el documento. Saldo vigente: $' . number_format($balance, 0, ',', '.') . '.');
            }
            if ($balance <= 0.01) {
                throw new Exception('El documento ya no tiene saldo pendiente.');
            }
            if ($amount > $balance + 0.01) {
                throw new Exception(
                    'El monto recibido ($' . number_format($amount, 0, ',', '.') .
                    ') supera el saldo vigente del documento ($' . number_format($balance, 0, ',', '.') . ').'
                );
            }

            $batchId = $this->insertPaymentBatch($environment, $paymentDate, $paymentMethodId, $cashAccountId, $amount, $reference, $notes, $userId, $idempotencyKey);
            $paymentId = $this->insertPayment($doc, $collection, $batchId, $paymentDate, $amount, $method, $cashAccountId, $reference, $notes, $userId);
            $this->insertAllocation($batchId, $paymentId, (int)$collection['id'], 'facto_document', $doc['document_id'], $amount);
            $stmtBatch = $this->pdo->prepare('UPDATE billing_payment_batches SET amount_allocated = ? WHERE id = ?');
            $stmtBatch->execute(array($amount, $batchId));
            $this->recalculateCollection((int)$collection['id']);
            $this->syncRelatedCommercialStatus((int)$collection['id'], $notes, $userId);
            $this->pdo->commit();
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            $replay = $this->findPaymentReplay(
                $environment,
                'incoming',
                $idempotencyKey,
                (int)$collection['id'],
                'received',
                (string)$doc['document_id'],
                null,
                $paymentDate,
                $amount,
                $paymentMethodId,
                $cashAccountId,
                $reference
            );
            if ($replay) {
                return array(
                    'document' => $this->getDocument($doc['id'], $environment),
                    'payments' => $this->getPaymentHistory($doc['id'], $environment),
                    'idempotent_replay' => true,
                    'payment_batch_id' => (int)$replay['batch_id'],
                    'payment_id' => (int)$replay['payment_id'],
                );
            }
            throw $e;
        }

        return array(
            'document' => $this->getDocument($doc['id'], $environment),
            'payments' => $this->getPaymentHistory($doc['id'], $environment),
            'idempotent_replay' => false,
            'payment_batch_id' => $batchId,
            'payment_id' => $paymentId,
        );
    }

    public function registerBulkPayment(array $ids, array $data, $userId = null)
    {
        $environment = $this->normalizeEnvironment(isset($data['environment']) ? $data['environment'] : 'sandbox');
        billing_assert_runtime_operation('finance', $environment, 'Registro masivo de pagos recibidos');

        $ids = array_values(array_unique(array_filter(array_map('intval', $ids), function ($id) { return $id > 0; })));
        if (!$ids) {
            throw new Exception('Selecciona al menos un documento.');
        }
        if (count($ids) > 100) {
            throw new Exception('El cobro masivo permite un máximo de 100 documentos por lote.');
        }

        $paymentDate = $this->normalizeDate(isset($data['payment_date']) ? $data['payment_date'] : date('Y-m-d'));
        $paymentMethodId = $this->requiredPositiveInt(isset($data['payment_method_id']) ? $data['payment_method_id'] : null, 'Selecciona el método de pago.');
        $cashAccountId = $this->requiredPositiveInt(isset($data['cash_account_id']) ? $data['cash_account_id'] : null, 'Selecciona la caja o cuenta de ingreso.');
        $reference = isset($data['external_reference']) ? trim((string)$data['external_reference']) : '';
        $notes = isset($data['notes']) ? trim((string)$data['notes']) : null;
        $fullPayment = !empty($data['full_payment']);
        $amountInformed = round((float)(isset($data['amount']) ? $data['amount'] : 0), 2);
        $idempotencyKey = $this->normalizePaymentIdempotencyKey(isset($data['idempotency_key']) ? $data['idempotency_key'] : null, $environment);
        $snapshot = $this->normalizeReceivableBulkSnapshot(isset($data['selection_snapshot']) ? $data['selection_snapshot'] : null, $environment);

        if (!$paymentDate) {
            throw new Exception('Fecha de cobro inválida.');
        }
        if ($environment === 'production' && $paymentDate > date('Y-m-d')) {
            throw new Exception('La fecha de un cobro masivo productivo no puede estar en el futuro.');
        }
        $this->assertProductionPaymentAuditFields($environment, $reference, $idempotencyKey);

        if (!$snapshot) {
            foreach ($ids as $id) {
                $doc = $this->getDocument($id, $environment);
                $collection = $this->getCollectionByDocumentId((string)$doc['document_id'], $environment);
                if (!$collection) {
                    throw new Exception('Todos los documentos del cobro masivo deben tener una cuenta por cobrar creada previamente.');
                }
                $snapshot[] = array(
                    'id' => (int)$doc['id'],
                    'collection_id' => (int)$collection['id'],
                    'document_id' => (string)$doc['document_id'],
                    'expected_balance' => round((float)$collection['balance_amount'], 2),
                    'due_date' => $collection['due_date'] ?: ($doc['issue_date'] ?: '9999-12-31'),
                    'folio' => (string)$doc['document_number'],
                );
            }
        }

        $snapshotIds = array_map(function ($row) { return (int)$row['id']; }, $snapshot);
        sort($snapshotIds);
        $sortedIds = $ids;
        sort($sortedIds);
        if ($snapshotIds !== $sortedIds) {
            throw new Exception('La selección cambió desde que se abrió el cobro masivo. Cierra el modal y vuelve a seleccionar los documentos.');
        }

        $entries = array();
        foreach ($snapshot as $expected) {
            $doc = $this->getDocument((int)$expected['id'], $environment);
            $collection = $this->getCollectionByDocumentId((string)$doc['document_id'], $environment);
            if (!$collection) {
                throw new Exception('La factura folio ' . ($doc['document_number'] ?: '-') . ' no tiene una cuenta por cobrar materializada.');
            }
            if ((int)$collection['id'] !== (int)$expected['collection_id'] || (string)$doc['document_id'] !== (string)$expected['document_id']) {
                throw new Exception('La identidad de una cuenta por cobrar cambió. Actualiza la lista antes de registrar el lote.');
            }
            $entries[] = array(
                'document' => $doc,
                'collection' => $collection,
                'expected_balance' => round((float)$expected['expected_balance'], 2),
                'expected_due_date' => (string)$expected['due_date'],
                'balance' => round((float)$expected['expected_balance'], 2),
                'sort_date' => (string)$expected['due_date'],
                'local_id' => (int)$doc['id'],
            );
        }

        $selectedBalance = round(array_sum(array_map(function ($entry) { return (float)$entry['expected_balance']; }, $entries)), 2);
        $amountTotal = $fullPayment ? $selectedBalance : $amountInformed;
        if ($amountTotal <= 0) {
            throw new Exception('El monto recibido debe ser mayor a cero.');
        }
        if ($amountTotal > $selectedBalance + 0.01) {
            throw new Exception('El monto supera el saldo total seleccionado.');
        }

        $options = new BillingPaymentOptionsService($this->pdo);
        $method = $options->getPaymentMethodById($paymentMethodId);
        $account = $options->getCashAccountById($cashAccountId);
        if (!$method || !$account) {
            throw new Exception('Método de pago o caja/cuenta no disponible.');
        }

        $expectedPlan = $this->buildReceivableBulkAllocationPlan($entries, $amountTotal);
        $replay = $this->findReceivableBulkReplay($environment, $idempotencyKey, $paymentDate, $paymentMethodId, $cashAccountId, $amountTotal, $reference, $expectedPlan);
        if ($replay) {
            return $replay;
        }
        $this->assertPaymentReferenceAvailable($environment, 'incoming', $cashAccountId, $reference, $idempotencyKey);

        $this->pdo->beginTransaction();
        try {
            if ($environment === 'production') {
                $this->lockPaymentAccount($cashAccountId);
                $this->assertPaymentReferenceAvailable($environment, 'incoming', $cashAccountId, $reference, $idempotencyKey);
            }
            $lockOrder = $entries;
            usort($lockOrder, function ($a, $b) { return (int)$a['collection']['id'] <=> (int)$b['collection']['id']; });
            $lockedByCollection = array();
            foreach ($lockOrder as $entry) {
                $locked = $this->lockAndRefreshCollection((int)$entry['collection']['id']);
                if ($locked['environment'] !== $environment || $locked['direction'] !== 'receivable' || (string)$locked['facto_document_id'] !== (string)$entry['document']['document_id']) {
                    throw new Exception('La identidad financiera de una cuenta por cobrar cambió. Actualiza la lista.');
                }
                $balance = round((float)$locked['balance_amount'], 2);
                if (abs($balance - (float)$entry['expected_balance']) > 0.01) {
                    throw new Exception('El saldo del folio ' . ($entry['document']['document_number'] ?: '-') . ' cambió. Saldo vigente: $' . number_format($balance, 0, ',', '.') . '.');
                }
                $currentDueDate = (string)($locked['due_date'] ?: ($entry['document']['issue_date'] ?: '9999-12-31'));
                if ($currentDueDate !== (string)$entry['expected_due_date']) {
                    throw new Exception('La fecha de vencimiento del folio ' . ($entry['document']['document_number'] ?: '-') . ' cambió. Actualiza la lista.');
                }
                if ($balance <= 0.01 || in_array($locked['collection_status'], array('pagado','anulado','incobrable'), true)) {
                    throw new Exception('El folio ' . ($entry['document']['document_number'] ?: '-') . ' ya no tiene saldo cobrable.');
                }
                $lockedByCollection[(int)$locked['id']] = $locked;
            }

            foreach ($entries as &$entry) {
                $entry['collection'] = $lockedByCollection[(int)$entry['collection']['id']];
                $entry['balance'] = round((float)$entry['collection']['balance_amount'], 2);
                $entry['sort_date'] = (string)($entry['collection']['due_date'] ?: ($entry['document']['issue_date'] ?: '9999-12-31'));
            }
            unset($entry);

            $currentPlan = $this->buildReceivableBulkAllocationPlan($entries, $amountTotal);
            if ($this->bulkPlanSignature($currentPlan) !== $this->bulkPlanSignature($expectedPlan)) {
                throw new Exception('La distribución del cobro cambió. Actualiza la lista antes de continuar.');
            }

            $batchId = $this->insertPaymentBatch($environment, $paymentDate, $paymentMethodId, $cashAccountId, $amountTotal, $reference, $notes, $userId, $idempotencyKey);
            $processed = array();
            foreach ($currentPlan as $planRow) {
                $entry = $planRow['entry'];
                $apply = (float)$planRow['amount'];
                $paymentId = $this->insertPayment($entry['document'], $entry['collection'], $batchId, $paymentDate, $apply, $method, $cashAccountId, $reference, $notes, $userId);
                $this->insertAllocation($batchId, $paymentId, (int)$entry['collection']['id'], 'facto_document', $entry['document']['document_id'], $apply);
                $updated = $this->recalculateCollection((int)$entry['collection']['id']);
                $this->syncRelatedCommercialStatus((int)$entry['collection']['id'], $notes, $userId);
                $processed[] = array(
                    'collection_id' => (int)$entry['collection']['id'],
                    'customer' => $entry['document']['customer_name'] ?: $entry['document']['receiver_legal_name'],
                    'folio' => $entry['document']['document_number'],
                    'amount_applied' => $apply,
                    'balance_after' => (float)$updated['balance_amount'],
                    'status_after' => $updated['collection_status'],
                );
            }

            $stmt = $this->pdo->prepare('UPDATE billing_payment_batches SET amount_allocated = ? WHERE id = ?');
            $stmt->execute(array($amountTotal, $batchId));
            $this->pdo->commit();
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            $replay = $this->findReceivableBulkReplay($environment, $idempotencyKey, $paymentDate, $paymentMethodId, $cashAccountId, $amountTotal, $reference, $expectedPlan);
            if ($replay) {
                return $replay;
            }
            throw $e;
        }

        return array(
            'batch_id' => $batchId,
            'amount_total' => $amountTotal,
            'amount_allocated' => $amountTotal,
            'unallocated' => 0.0,
            'processed' => $processed,
            'idempotent_replay' => false,
        );
    }



    private function normalizeReceivableBulkSnapshot($raw, $environment)
    {
        if (is_string($raw)) {
            $decoded = json_decode($raw, true);
        } elseif (is_array($raw)) {
            $decoded = $raw;
        } else {
            $decoded = array();
        }
        if (!$decoded) {
            if ($environment === 'production') {
                throw new Exception('Falta el snapshot de la selección masiva. Actualiza la pantalla.');
            }
            return array();
        }
        $normalized = array();
        $seenIds = array();
        $seenCollections = array();
        foreach ($decoded as $row) {
            if (!is_array($row)) {
                throw new Exception('Snapshot de selección inválido.');
            }
            $id = isset($row['id']) ? (int)$row['id'] : 0;
            $collectionId = isset($row['collection_id']) ? (int)$row['collection_id'] : 0;
            $documentId = isset($row['document_id']) ? trim((string)$row['document_id']) : '';
            $balance = isset($row['expected_balance']) && is_numeric($row['expected_balance']) ? round((float)$row['expected_balance'], 2) : -1;
            $dueDate = isset($row['due_date']) && trim((string)$row['due_date']) !== '' ? trim((string)$row['due_date']) : '9999-12-31';
            if ($id <= 0 || $collectionId <= 0 || $documentId === '' || $balance <= 0.01 || !$this->normalizeDate($dueDate)) {
                throw new Exception('El snapshot de una cuenta por cobrar está incompleto. Actualiza la pantalla.');
            }
            if (isset($seenIds[$id]) || isset($seenCollections[$collectionId])) {
                throw new Exception('La selección masiva contiene documentos duplicados.');
            }
            $seenIds[$id] = true;
            $seenCollections[$collectionId] = true;
            $normalized[] = array(
                'id' => $id,
                'collection_id' => $collectionId,
                'document_id' => $documentId,
                'expected_balance' => $balance,
                'due_date' => $dueDate,
                'folio' => isset($row['folio']) ? trim((string)$row['folio']) : '',
            );
        }
        return $normalized;
    }

    private function buildReceivableBulkAllocationPlan(array $entries, $amountTotal)
    {
        usort($entries, function ($a, $b) {
            if ((string)$a['sort_date'] === (string)$b['sort_date']) {
                return (int)$a['local_id'] <=> (int)$b['local_id'];
            }
            return strcmp((string)$a['sort_date'], (string)$b['sort_date']);
        });
        $remaining = round((float)$amountTotal, 2);
        $plan = array();
        foreach ($entries as $entry) {
            if ($remaining <= 0.01) break;
            $apply = round(min($remaining, (float)$entry['balance']), 2);
            if ($apply <= 0.01) continue;
            $plan[] = array('entry' => $entry, 'amount' => $apply);
            $remaining = round($remaining - $apply, 2);
        }
        if ($remaining > 0.01) {
            throw new Exception('No fue posible distribuir íntegramente el cobro masivo entre los documentos seleccionados.');
        }
        return $plan;
    }

    private function bulkPlanSignature(array $plan)
    {
        $rows = array();
        foreach ($plan as $row) {
            $rows[] = (int)$row['entry']['collection']['id'] . ':' . number_format((float)$row['amount'], 2, '.', '');
        }
        return implode('|', $rows);
    }

    private function findReceivableBulkReplay($environment, $idempotencyKey, $paymentDate, $methodId, $cashAccountId, $amountTotal, $reference, array $plan)
    {
        if ($idempotencyKey === null || $idempotencyKey === '') return null;
        $stmt = $this->pdo->prepare("SELECT * FROM billing_payment_batches WHERE BINARY environment=BINARY ? AND BINARY direction=BINARY 'incoming' AND BINARY idempotency_key=BINARY ? LIMIT 1");
        $stmt->execute(array($environment, $idempotencyKey));
        $batch = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$batch) return null;
        if ((string)$batch['payment_date'] !== (string)$paymentDate
            || (int)$batch['payment_method_id'] !== (int)$methodId
            || (int)$batch['cash_account_id'] !== (int)$cashAccountId
            || abs((float)$batch['amount_total'] - (float)$amountTotal) > 0.01
            || trim((string)$batch['external_reference']) !== trim((string)$reference)) {
            throw new Exception('La clave de idempotencia ya fue utilizada por otra operación masiva. Actualiza la pantalla.');
        }
        $stmt = $this->pdo->prepare("SELECT id,collection_id,facto_document_id,amount FROM billing_payments WHERE payment_batch_id=? AND BINARY direction=BINARY 'received' ORDER BY collection_id");
        $stmt->execute(array((int)$batch['id']));
        $payments = $stmt->fetchAll(PDO::FETCH_ASSOC);
        if (count($payments) !== count($plan)) {
            throw new Exception('El lote idempotente existente está incompleto y requiere revisión.');
        }
        $paymentByCollection = array();
        foreach ($payments as $payment) $paymentByCollection[(int)$payment['collection_id']] = $payment;
        $stmt = $this->pdo->prepare("SELECT collection_id,source_type,source_id,amount_applied FROM billing_payment_allocations WHERE payment_batch_id=? ORDER BY collection_id");
        $stmt->execute(array((int)$batch['id']));
        $allocations = $stmt->fetchAll(PDO::FETCH_ASSOC);
        if (count($allocations) !== count($plan)) {
            throw new Exception('Las asignaciones del lote idempotente existente están incompletas.');
        }
        $allocationByCollection = array();
        foreach ($allocations as $allocation) $allocationByCollection[(int)$allocation['collection_id']] = $allocation;
        $processed = array();
        foreach ($plan as $planRow) {
            $entry = $planRow['entry'];
            $collectionId = (int)$entry['collection']['id'];
            if (!isset($paymentByCollection[$collectionId], $allocationByCollection[$collectionId])) {
                throw new Exception('El lote idempotente existente no coincide con la selección original.');
            }
            $payment = $paymentByCollection[$collectionId];
            $allocation = $allocationByCollection[$collectionId];
            if ((string)$payment['facto_document_id'] !== (string)$entry['document']['document_id']
                || abs((float)$payment['amount'] - (float)$planRow['amount']) > 0.01
                || (string)$allocation['source_type'] !== 'facto_document'
                || (string)$allocation['source_id'] !== (string)$entry['document']['document_id']
                || abs((float)$allocation['amount_applied'] - (float)$planRow['amount']) > 0.01) {
                throw new Exception('El lote idempotente existente contiene documentos o montos diferentes.');
            }
            $current = $this->getCollectionById($collectionId);
            $processed[] = array(
                'collection_id' => $collectionId,
                'customer' => $entry['document']['customer_name'] ?: $entry['document']['receiver_legal_name'],
                'folio' => $entry['document']['document_number'],
                'amount_applied' => (float)$planRow['amount'],
                'balance_after' => $current ? (float)$current['balance_amount'] : 0.0,
                'status_after' => $current ? $current['collection_status'] : 'pagado',
            );
        }
        return array(
            'batch_id' => (int)$batch['id'],
            'amount_total' => (float)$batch['amount_total'],
            'amount_allocated' => (float)$batch['amount_allocated'],
            'unallocated' => max(round((float)$batch['amount_total'] - (float)$batch['amount_allocated'], 2), 0),
            'processed' => $processed,
            'idempotent_replay' => true,
        );
    }

    public function refreshCalculatedReceivableStatuses($environment = 'sandbox', $documentLocalId = null)
    {
        $environment = $this->normalizeEnvironment($environment);
        billing_assert_runtime_operation('finance', $environment, 'Recalculo financiero de cuentas por cobrar');
        $this->cleanupGuideReceivables($environment, $documentLocalId);
        $this->resolveCustomers($environment, $documentLocalId);
        $this->ensureCollectionsForEnvironment($environment, $documentLocalId);

        $documentId = null;
        if ($documentLocalId !== null) {
            $doc = $this->fetchDocumentByCondition('d.id = ?', array((int)$documentLocalId), $environment);
            $documentId = $doc ? (string)$doc['document_id'] : null;
        }

        $filter = $documentId !== null ? ' AND c.facto_document_id = ?' : '';
        $params = $documentId !== null ? array($environment, $documentId) : array($environment);

        // Recalculo masivo: evita una consulta por documento en cargas con historial amplio.
        $sql = "
            UPDATE billing_collections c
            LEFT JOIN (
                SELECT collection_id, COALESCE(SUM(amount), 0) AS paid_amount, MAX(payment_date) AS last_payment_date
                FROM billing_payments
                WHERE direction = 'received' AND COALESCE(is_voided, 0) = 0
                GROUP BY collection_id
            ) p ON p.collection_id = c.id
            SET c.paid_amount = COALESCE(p.paid_amount, 0),
                c.balance_amount = GREATEST(c.total_amount - COALESCE(p.paid_amount, 0), 0),
                c.last_payment_date = p.last_payment_date,
                c.foreign_total_amount = CASE
                    WHEN UPPER(COALESCE(c.currency_code,'CLP')) <> 'CLP'
                    THEN COALESCE(c.foreign_total_amount, c.total_amount / NULLIF(c.exchange_rate_value,0))
                    ELSE c.foreign_total_amount END,
                c.foreign_paid_amount = CASE
                    WHEN UPPER(COALESCE(c.currency_code,'CLP')) <> 'CLP'
                    THEN COALESCE(p.paid_amount,0) / NULLIF(c.exchange_rate_value,0)
                    ELSE c.foreign_paid_amount END,
                c.foreign_balance_amount = CASE
                    WHEN UPPER(COALESCE(c.currency_code,'CLP')) <> 'CLP'
                    THEN GREATEST(c.total_amount - COALESCE(p.paid_amount,0),0) / NULLIF(c.exchange_rate_value,0)
                    ELSE c.foreign_balance_amount END,
                c.collection_status = CASE
                    WHEN c.collection_status IN ('anulado','incobrable') THEN c.collection_status
                    WHEN GREATEST(c.total_amount - COALESCE(p.paid_amount, 0), 0) <= 0.5 THEN 'pagado'
                    WHEN c.due_date IS NULL OR c.due_date < CURRENT_DATE THEN 'atrasado'
                    WHEN COALESCE(p.paid_amount, 0) > 0 THEN 'parcial'
                    ELSE 'por_cobrar'
                END,
                c.updated_at = CURRENT_TIMESTAMP
            WHERE c.environment = ? AND c.direction = 'receivable'" . $filter;
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);

        $sqlDoc = "
            UPDATE facto_documents d
            INNER JOIN billing_collections c ON c.environment = d.environment
                AND c.direction = 'receivable'
                AND c.facto_document_id = d.document_id
            SET d.payment_status = c.collection_status,
                d.payment_due_date = c.due_date,
                d.payment_paid_date = CASE WHEN c.collection_status = 'pagado' THEN c.last_payment_date ELSE NULL END,
                d.finance_center_id = COALESCE(d.finance_center_id, c.finance_center_id),
                d.updated_at = CURRENT_TIMESTAMP
            WHERE d.environment = ?" . ($documentId !== null ? ' AND d.document_id = ?' : '');
        $stmtDoc = $this->pdo->prepare($sqlDoc);
        $stmtDoc->execute($params);

        // Sincroniza estados comerciales vinculados sin generar actividades.
        $sqlQuote = "
            UPDATE crm_quotes q
            INNER JOIN billing_collections c ON c.quote_id = q.id
            SET q.status = CASE
                    WHEN c.collection_status = 'pagado' THEN 'Pagada'
                    WHEN c.collection_status IN ('por_cobrar','parcial','atrasado') THEN 'Esperando pago'
                    ELSE q.status END,
                q.payment_status = CASE WHEN c.collection_status = 'parcial' THEN 'por_cobrar' ELSE c.collection_status END,
                q.payment_due_date = c.due_date,
                q.payment_paid_date = CASE WHEN c.collection_status = 'pagado' THEN c.last_payment_date ELSE NULL END,
                q.payment_date = CASE WHEN c.collection_status = 'pagado' THEN c.last_payment_date ELSE q.payment_date END,
                q.payment_amount = c.paid_amount
            WHERE c.environment = ? AND c.direction = 'receivable'" . ($documentId !== null ? ' AND c.facto_document_id = ?' : '');
        $stmtQuote = $this->pdo->prepare($sqlQuote);
        $stmtQuote->execute($params);

        $sqlOpp = "
            UPDATE crm_opportunities o
            INNER JOIN billing_collections c ON c.opportunity_id = o.id
            SET o.payment_status = CASE WHEN c.collection_status = 'parcial' THEN 'por_cobrar' ELSE c.collection_status END,
                o.payment_due_date = c.due_date,
                o.payment_paid_date = CASE WHEN c.collection_status = 'pagado' THEN c.last_payment_date ELSE NULL END,
                o.updated_at = CURRENT_TIMESTAMP
            WHERE c.environment = ? AND c.direction = 'receivable'" . ($documentId !== null ? ' AND c.facto_document_id = ?' : '');
        $stmtOpp = $this->pdo->prepare($sqlOpp);
        $stmtOpp->execute($params);
    }

    public function ensureReceivableCollection($factoDocumentId, $environment = 'sandbox', array $overrides = array())
    {
        $environment = $this->normalizeEnvironment($environment);
        billing_assert_runtime_operation('finance', $environment, 'Inicialización de cuenta por cobrar');
        $doc = $this->fetchDocumentByCondition('d.document_id = ?', array((string)$factoDocumentId), $environment);
        if (!$doc) {
            throw new Exception('Documento FACTO emitido no encontrado.');
        }
        if (!$this->isReceivableDocumentType($doc['document_type_id'])) {
            throw new Exception('El documento FACTO no es una factura y no puede generar una cuenta por cobrar.');
        }

        // v6.18.1: una sincronización productiva puede detectar una factura antes de
        // que su contraparte haya sido vinculada localmente. Primero intenta resolver
        // cliente y centro por RUT; luego exige ambos datos antes de crear la CxC.
        if (empty($doc['crm_customer_id']) || $this->nullableInt(isset($doc['effective_finance_center_id']) ? $doc['effective_finance_center_id'] : null) === null) {
            $this->resolveCustomers($environment, (int)$doc['id']);
            $doc = $this->fetchDocumentByCondition('d.document_id = ?', array((string)$factoDocumentId), $environment);
            if (!$doc) {
                throw new Exception('Documento FACTO emitido no encontrado después de resolver su contraparte.');
            }
        }

        if ($environment === 'production') {
            if (empty($doc['crm_customer_id'])) {
                throw new Exception('La factura emitida no tiene un cliente CRM vinculado; no se creó la cuenta por cobrar.');
            }
            if ($this->nullableInt(isset($doc['effective_finance_center_id']) ? $doc['effective_finance_center_id'] : null) === null) {
                throw new Exception('La factura emitida no tiene un centro de ingreso; no se creó la cuenta por cobrar.');
            }
        }

        $currencyCode = strtoupper(trim((string)($doc['currency_code'] ?? 'CLP')));
        if ($currencyCode !== 'CLP' && (empty($doc['total_amount_clp']) || (float)($doc['exchange_rate_value'] ?? 0) <= 0)) {
            $this->exchangeRates->applyToFactoDocument($doc, false);
            $doc = $this->fetchDocumentByCondition('d.document_id = ?', array((string)$factoDocumentId), $environment);
            if (!$doc || empty($doc['total_amount_clp'])) {
                throw new Exception('No fue posible resolver el tipo de cambio histórico del documento antes de crear la cuenta por cobrar.');
            }
            $currencyCode = strtoupper(trim((string)($doc['currency_code'] ?? 'CLP')));
        }

        $dueDate = $this->resolveDueDate($doc);
        $financeCenterId = array_key_exists('finance_center_id', $overrides)
            ? $this->nullableInt($overrides['finance_center_id'])
            : $this->nullableInt(isset($doc['effective_finance_center_id']) ? $doc['effective_finance_center_id'] : null);
        $notes = array_key_exists('notes', $overrides) ? $overrides['notes'] : (isset($doc['collection_notes']) ? $doc['collection_notes'] : null);
        $total = max((float)($doc['total_amount_clp'] ?? $doc['total_amount']), 0.0);
        // La importación de un estado remoto "pagado" no debe fabricar un
        // movimiento de caja. Sólo una migración histórica explícita puede
        // solicitar ese comportamiento.
        if (!empty($overrides['migrate_legacy_paid'])) {
            $this->ensureLegacyPaidPayment($doc, $environment, $total);
        }

        // Los pagos y saldos se administran siempre en CLP. El monto original
        // y la tasa histórica quedan congelados como metadatos de la CxC.
        $existingCollection = $this->getCollectionByDocumentId((string)$doc['document_id'], $environment);
        $paid = $existingCollection ? (float)$existingCollection['paid_amount'] : 0.0;
        $balance = max($total - $paid, 0.0);
        $exchangeRate = max((float)($doc['exchange_rate_value'] ?? 1), 0.000001);
        $foreignTotal = $currencyCode === 'CLP' ? null : max((float)$doc['total_amount'], 0.0);
        $foreignPaid = $currencyCode === 'CLP' ? null : round($paid / $exchangeRate, 4);
        $foreignBalance = $currencyCode === 'CLP' ? null : max(0, round($foreignTotal - $foreignPaid, 4));
        $status = $this->calculateStatus($total, $paid, $dueDate, isset($doc['payment_status']) ? $doc['payment_status'] : null);

        $stmt = $this->pdo->prepare("
            INSERT INTO billing_collections
            (environment, direction, source_table, facto_document_id, quote_id, opportunity_id, customer_id,
             document_number, document_type_id, issue_date, due_date, total_amount, paid_amount, balance_amount,
             collection_status, payment_terms, last_payment_date, finance_center_id, notes,
             currency_code, currency_id, exchange_rate_value, exchange_rate_date, exchange_rate_source, exchange_rate_status,
             foreign_total_amount, foreign_paid_amount, foreign_balance_amount)
            VALUES (?, 'receivable', 'facto_documents', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE
                quote_id = VALUES(quote_id),
                opportunity_id = VALUES(opportunity_id),
                customer_id = VALUES(customer_id),
                document_number = VALUES(document_number),
                document_type_id = VALUES(document_type_id),
                issue_date = VALUES(issue_date),
                due_date = VALUES(due_date),
                total_amount = VALUES(total_amount),
                paid_amount = VALUES(paid_amount),
                balance_amount = VALUES(balance_amount),
                collection_status = VALUES(collection_status),
                payment_terms = VALUES(payment_terms),
                last_payment_date = VALUES(last_payment_date),
                finance_center_id = VALUES(finance_center_id),
                notes = COALESCE(VALUES(notes), notes),
                currency_code = VALUES(currency_code),
                currency_id = VALUES(currency_id),
                exchange_rate_value = VALUES(exchange_rate_value),
                exchange_rate_date = VALUES(exchange_rate_date),
                exchange_rate_source = VALUES(exchange_rate_source),
                exchange_rate_status = VALUES(exchange_rate_status),
                foreign_total_amount = VALUES(foreign_total_amount),
                foreign_paid_amount = VALUES(foreign_paid_amount),
                foreign_balance_amount = VALUES(foreign_balance_amount),
                updated_at = CURRENT_TIMESTAMP
        ");
        $stmt->execute(array(
            $environment,
            $doc['document_id'],
            $this->nullableInt($doc['crm_quote_id']),
            $this->nullableInt($doc['crm_opportunity_id']),
            $this->nullableInt($doc['crm_customer_id']),
            $doc['document_number'],
            $this->nullableInt($doc['document_type_id']),
            $doc['issue_date'],
            $dueDate,
            $total,
            $paid,
            $balance,
            $status,
            $this->customerPaymentTermsLabel($doc),
            ($existingCollection && !empty($existingCollection['last_payment_date'])) ? $existingCollection['last_payment_date'] : null,
            $financeCenterId,
            $notes,
            $currencyCode,
            $this->nullableInt($doc['currency_id'] ?? null),
            $exchangeRate,
            $doc['exchange_rate_date'] ?? $doc['issue_date'],
            $doc['exchange_rate_source'] ?? ($currencyCode === 'CLP' ? 'CLP' : null),
            $doc['exchange_rate_status'] ?? 'validated',
            $foreignTotal,
            $foreignPaid,
            $foreignBalance,
        ));

        $collection = $this->getCollectionByDocumentId((string)$doc['document_id'], $environment);
        if (!$collection) {
            throw new Exception('No fue posible inicializar la cuenta por cobrar del documento.');
        }

        $this->attachOrphanPaymentsToCollection($collection);
        $collection = $this->recalculateCollection((int)$collection['id']);
        $this->updateFactoPaymentState($doc, $collection);
        return $collection;
    }

    private function ensureCollectionsForEnvironment($environment, $documentLocalId = null)
    {
        billing_assert_runtime_operation('finance', $environment, 'Materialización de cuentas por cobrar');
        $where = array(
            'd.environment = ?',
            'd.total_amount > 0',
            'CAST(COALESCE(d.document_type_id, 0) AS UNSIGNED) IN (2,32,46)',
            "COALESCE(d.payment_status, 'por_cobrar') NOT IN ('anulado','no_facturado')"
        );
        $params = array($environment);
        if ($documentLocalId !== null) {
            $where[] = 'd.id = ?';
            $params[] = (int)$documentLocalId;
        }
        $sql = "SELECT d.id, d.document_id
                FROM facto_documents d
                LEFT JOIN billing_collections c ON c.environment = d.environment
                    AND c.direction = 'receivable'
                    AND c.facto_document_id = d.document_id
                WHERE " . implode(' AND ', $where) . " AND c.id IS NULL
                ORDER BY d.id DESC LIMIT 5000";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $this->ensureReceivableCollection($row['document_id'], $environment);
        }
    }

    private function ensureDocumentCustomerAndCollection($localId, $environment)
    {
        billing_assert_runtime_operation('finance', $environment, 'Vinculación automática de cliente y CxC');
        $this->resolveCustomers($environment, $localId);
        $doc = $this->fetchDocumentByCondition('d.id = ?', array((int)$localId), $environment);
        if ($doc && (float)$doc['total_amount'] > 0 && $this->isReceivableDocumentType($doc['document_type_id'])) {
            $this->ensureReceivableCollection($doc['document_id'], $environment);
        }
    }

    /**
     * Repara guías que hayan quedado accidentalmente en cobranza. Si existen
     * pagos, la colección se conserva y se marca para revisión para no alterar
     * movimientos financieros ya registrados.
     */
    private function cleanupGuideReceivables($environment, $documentLocalId = null)
    {
        $environment = $this->normalizeEnvironment($environment);
        $extra = $documentLocalId !== null ? ' AND d.id = ?' : '';
        $params = $documentLocalId !== null
            ? array($environment, (int)$documentLocalId)
            : array($environment);

        $stmtDoc = $this->pdo->prepare("
            UPDATE facto_documents d
            SET d.payment_status = 'no_facturado',
                d.payment_due_date = NULL,
                d.payment_paid_date = NULL,
                d.document_type_taxbureau = '52',
                d.updated_at = CURRENT_TIMESTAMP
            WHERE d.environment = ?
              AND CAST(COALESCE(d.document_type_id, 0) AS UNSIGNED) = 54" . $extra);
        $stmtDoc->execute($params);

        $stmtReview = $this->pdo->prepare("
            UPDATE billing_collections c
            INNER JOIN facto_documents d
                ON d.environment = c.environment
               AND BINARY d.document_id = BINARY c.facto_document_id
            SET c.notes = CASE
                    WHEN COALESCE(c.notes, '') LIKE '%[REVISAR GUIA MANUAL]%' THEN c.notes
                    ELSE CONCAT(COALESCE(c.notes, ''), CASE WHEN COALESCE(c.notes, '') = '' THEN '' ELSE '\n' END, '[REVISAR GUIA MANUAL] La guía posee pagos asociados y no fue eliminada automáticamente.')
                END,
                c.updated_at = CURRENT_TIMESTAMP
            WHERE c.environment = ?
              AND c.direction = 'receivable'
              AND CAST(COALESCE(d.document_type_id, 0) AS UNSIGNED) = 54
              AND EXISTS (SELECT 1 FROM billing_payments p WHERE COALESCE(p.is_voided,0)=0 AND (p.collection_id = c.id OR (p.environment = c.environment AND BINARY p.facto_document_id = BINARY c.facto_document_id)))" . $extra);
        $stmtReview->execute($params);

        $stmtDelete = $this->pdo->prepare("
            DELETE c
            FROM billing_collections c
            INNER JOIN facto_documents d
                ON d.environment = c.environment
               AND BINARY d.document_id = BINARY c.facto_document_id
            WHERE c.environment = ?
              AND c.direction = 'receivable'
              AND CAST(COALESCE(d.document_type_id, 0) AS UNSIGNED) = 54
              AND NOT EXISTS (SELECT 1 FROM billing_payments p WHERE COALESCE(p.is_voided,0)=0 AND (p.collection_id = c.id OR (p.environment = c.environment AND BINARY p.facto_document_id = BINARY c.facto_document_id)))" . $extra);
        $stmtDelete->execute($params);
    }

    private function resolveCustomers($environment, $documentLocalId = null)
    {
        $whereExtra = $documentLocalId !== null ? ' AND d.id = ?' : '';
        $params = $documentLocalId !== null
            ? array($environment, (int)$documentLocalId)
            : array($environment);

        // Relación explícita desde la cotización. Tiene prioridad sobre cualquier
        // coincidencia textual y conserva oportunidad/centro financiero.
        $sql = "
            UPDATE facto_documents d
            INNER JOIN crm_quotes q ON BINARY q.facto_document_id = BINARY d.document_id
            SET d.crm_quote_id = COALESCE(d.crm_quote_id, q.id),
                d.crm_opportunity_id = COALESCE(d.crm_opportunity_id, q.opportunity_id),
                d.crm_customer_id = COALESCE(d.crm_customer_id, q.customer_id),
                d.finance_center_id = COALESCE(d.finance_center_id, q.finance_center_id),
                d.link_status = CASE WHEN d.link_status = 'unlinked' THEN 'auto_linked' ELSE d.link_status END,
                d.link_confidence = COALESCE(d.link_confidence, 100),
                d.linked_at = COALESCE(d.linked_at, NOW())
            WHERE d.environment = ?" . $whereExtra;
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);

        // Para clientes extranjeros, customer_supplier_id es la llave estable
        // entregada por FACTO y debe preceder al matching por identificador fiscal.
        $sql = "
            UPDATE facto_documents d
            INNER JOIN (
                SELECT facto_customer_supplier_id, MIN(id) AS customer_id
                FROM crm_customers
                WHERE deleted_at IS NULL
                  AND facto_customer_supplier_id IS NOT NULL
                  AND facto_customer_supplier_id <> ''
                GROUP BY facto_customer_supplier_id
                HAVING COUNT(*) = 1
            ) c ON BINARY c.facto_customer_supplier_id = BINARY d.customer_supplier_id
            SET d.crm_customer_id = c.customer_id,
                d.finance_center_id = COALESCE(d.finance_center_id, (SELECT default_income_center_id FROM crm_customers WHERE id=c.customer_id)),
                d.link_status = CASE WHEN d.link_status = 'unlinked' THEN 'auto_linked' ELSE d.link_status END,
                d.link_confidence = COALESCE(d.link_confidence, 100),
                d.linked_at = COALESCE(d.linked_at, NOW())
            WHERE d.environment = ?
              AND d.crm_customer_id IS NULL
              AND d.customer_supplier_id IS NOT NULL
              AND d.customer_supplier_id <> ''" . $whereExtra;
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);

        $docTax = "REPLACE(REPLACE(REPLACE(UPPER(d.receiver_tax_id_code), '.', ''), '-', ''), ' ', '')";

        // Identificador extranjero canónico.
        $exportTax = "REPLACE(REPLACE(REPLACE(UPPER(export_tax_id_code), '.', ''), '-', ''), ' ', '')";
        $sql = "
            UPDATE facto_documents d
            INNER JOIN (
                SELECT " . $exportTax . " AS normalized_tax, MIN(id) AS customer_id
                FROM crm_customers
                WHERE deleted_at IS NULL
                  AND export_tax_id_code IS NOT NULL
                  AND export_tax_id_code <> ''
                GROUP BY " . $exportTax . "
                HAVING COUNT(*) = 1
            ) x ON x.normalized_tax = " . $docTax . "
            INNER JOIN crm_customers c ON c.id = x.customer_id
            SET d.crm_customer_id = c.id,
                d.finance_center_id = COALESCE(d.finance_center_id, c.default_income_center_id),
                d.link_status = CASE WHEN d.link_status = 'unlinked' THEN 'auto_linked' ELSE d.link_status END,
                d.link_confidence = COALESCE(d.link_confidence, 98),
                d.linked_at = COALESCE(d.linked_at, NOW())
            WHERE d.environment = ?
              AND d.crm_customer_id IS NULL
              AND d.receiver_tax_id_code IS NOT NULL
              AND d.receiver_tax_id_code <> ''" . $whereExtra;
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);

        // Compatibilidad histórica: varios clientes de exportación fueron cargados
        // antes de existir export_tax_id_code y el ID quedó en customer_tax_id.
        $customerTax = "REPLACE(REPLACE(REPLACE(UPPER(customer_tax_id), '.', ''), '-', ''), ' ', '')";
        $sql = "
            UPDATE facto_documents d
            INNER JOIN (
                SELECT " . $customerTax . " AS normalized_tax, MIN(id) AS customer_id
                FROM crm_customers
                WHERE deleted_at IS NULL
                  AND customer_tax_id IS NOT NULL
                  AND customer_tax_id <> ''
                GROUP BY " . $customerTax . "
                HAVING COUNT(*) = 1
            ) x ON x.normalized_tax = " . $docTax . "
            INNER JOIN crm_customers c ON c.id = x.customer_id
            SET d.crm_customer_id = c.id,
                d.finance_center_id = COALESCE(d.finance_center_id, c.default_income_center_id),
                d.link_status = CASE WHEN d.link_status = 'unlinked' THEN 'auto_linked' ELSE d.link_status END,
                d.link_confidence = COALESCE(d.link_confidence, 95),
                d.linked_at = COALESCE(d.linked_at, NOW())
            WHERE d.environment = ?
              AND d.crm_customer_id IS NULL
              AND d.receiver_tax_id_code IS NOT NULL
              AND d.receiver_tax_id_code <> ''" . $whereExtra;
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);

        // Completa metadatos del receptor y propaga la relación a la CxC.
        $sql = "
            UPDATE facto_documents d
            INNER JOIN crm_customers c ON c.id = d.crm_customer_id
            SET d.receiver_country_id = COALESCE(d.receiver_country_id, c.export_country_id, c.country_id),
                d.receiver_tax_id_type = COALESCE(NULLIF(d.receiver_tax_id_type, ''), NULLIF(c.export_tax_id_type, ''), NULLIF(c.tax_id_type, '')),
                d.finance_center_id = COALESCE(d.finance_center_id, c.default_income_center_id)
            WHERE d.environment = ?" . $whereExtra;
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);

        $collectionParams = array($environment);
        $collectionExtra = '';
        if ($documentLocalId !== null) {
            $collectionExtra = ' AND d.id = ?';
            $collectionParams[] = (int)$documentLocalId;
        }
        $sql = "
            UPDATE billing_collections bc
            INNER JOIN facto_documents d
              ON d.environment = bc.environment
             AND BINARY d.document_id = BINARY bc.facto_document_id
            SET bc.customer_id = COALESCE(bc.customer_id, d.crm_customer_id),
                bc.finance_center_id = COALESCE(bc.finance_center_id, d.finance_center_id),
                bc.updated_at = CURRENT_TIMESTAMP
            WHERE d.environment = ?
              AND d.crm_customer_id IS NOT NULL" . $collectionExtra;
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($collectionParams);
    }

    private function fetchDocumentByCondition($condition, array $params, $environment)
    {
        $financialTypeSql = "CAST(COALESCE(d.document_type_id, 0) AS UNSIGNED) IN (2,32,46)";
        $netSql = $this->receivableNetSqlExpressions();
        $sql = "
            SELECT d.*,
                   COALESCE(bdt.document_label, CONCAT('DTE ', COALESCE(d.document_type_taxbureau, d.document_type_id))) AS document_label,
                   COALESCE(bdt.document_family, 'other') AS document_family,
                   bdt.document_key,
                   CASE WHEN " . $financialTypeSql . " THEN 1 ELSE 0 END AS is_financial_document,
                   customer.customer_name, customer.customer_tax_id,
                   customer.payment_terms AS customer_payment_terms, customer.facto_payment_term,
                   customer.default_income_center_id, q.quote_number, q.status AS quote_status,
                   q.payment_due_date AS quote_payment_due_date, q.payment_terms_selected,
                   o.title AS opportunity_title,
                   COALESCE(d.finance_center_id, customer.default_income_center_id) AS effective_finance_center_id,
                   COALESCE(fc_doc.center_name, fc_customer.center_name) AS finance_center_name,
                   c.id AS collection_id, c.collection_status, c.due_date,
                   c.total_amount AS collection_total, COALESCE(c.paid_amount, 0) AS paid_amount,
                   CASE WHEN " . $financialTypeSql . " THEN COALESCE(c.balance_amount, d.total_amount) ELSE 0 END AS balance_amount,
                   CASE WHEN " . $financialTypeSql . " THEN " . $netSql['net_total'] . " ELSE 0 END AS collection_net_total,
                   CASE WHEN " . $financialTypeSql . " THEN " . $netSql['net_paid'] . " ELSE 0 END AS paid_net_amount,
                   CASE WHEN " . $financialTypeSql . " THEN " . $netSql['net_balance'] . " ELSE 0 END AS net_balance_amount,
                   CASE WHEN " . $financialTypeSql . " THEN " . $netSql['foreign_net_total'] . " ELSE 0 END AS foreign_net_total,
                   CASE WHEN " . $financialTypeSql . " THEN " . $netSql['foreign_net_paid'] . " ELSE 0 END AS foreign_paid_net_amount,
                   CASE WHEN " . $financialTypeSql . " THEN " . $netSql['foreign_net_balance'] . " ELSE 0 END AS foreign_net_balance_amount,
                   c.currency_code AS collection_currency_code, c.currency_id AS collection_currency_id,
                   c.exchange_rate_value AS collection_exchange_rate_value, c.exchange_rate_date AS collection_exchange_rate_date,
                   c.exchange_rate_source AS collection_exchange_rate_source, c.exchange_rate_status AS collection_exchange_rate_status,
                   c.foreign_total_amount, c.foreign_paid_amount, c.foreign_balance_amount,
                   c.last_payment_date, c.notes AS collection_notes
            FROM facto_documents d
            LEFT JOIN billing_document_types bdt
              ON bdt.direction='issued' AND bdt.facto_document_type_id=CAST(d.document_type_id AS UNSIGNED)
            LEFT JOIN crm_customers customer ON customer.id = d.crm_customer_id
            LEFT JOIN crm_quotes q ON q.id = d.crm_quote_id
            LEFT JOIN crm_opportunities o ON o.id = d.crm_opportunity_id
            LEFT JOIN finance_centers fc_doc ON fc_doc.id = d.finance_center_id
            LEFT JOIN finance_centers fc_customer ON fc_customer.id = customer.default_income_center_id
            LEFT JOIN billing_collections c ON c.environment = d.environment
                AND c.direction = 'receivable' AND c.facto_document_id = d.document_id
            WHERE d.environment = ? AND " . $condition . " LIMIT 1";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array_merge(array($environment), $params));
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    private function getKpis($environment)
    {
        $netSql = $this->receivableNetSqlExpressions();
        $stmt = $this->pdo->prepare("
            SELECT
                COUNT(*) AS documents_count,
                COALESCE(SUM(" . $netSql['net_total'] . "), 0) AS total_billed,
                COALESCE(SUM(CASE WHEN c.collection_status IN ('por_cobrar','atrasado','parcial') THEN " . $netSql['net_balance'] . " ELSE 0 END), 0) AS open_balance,
                COALESCE(SUM(CASE WHEN c.collection_status = 'atrasado' THEN " . $netSql['net_balance'] . " ELSE 0 END), 0) AS overdue_balance,
                COALESCE(SUM(CASE WHEN c.collection_status = 'parcial' THEN 1 ELSE 0 END), 0) AS partial_documents,
                COALESCE(SUM(CASE WHEN c.customer_id IS NULL THEN 1 ELSE 0 END), 0) AS unlinked_customers
            FROM billing_collections c
            INNER JOIN facto_documents d
              ON d.environment = c.environment
             AND BINARY d.document_id = BINARY c.facto_document_id
            WHERE c.environment = ? AND c.direction = 'receivable'
        ");
        $stmt->execute(array($environment));
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: array();
    }

    private function resolveDueDate(array $doc)
    {
        // Conserva el vencimiento fijado al emitir o en la cotizacion. Solo calcula cuando no existe.
        $documentDueDate = $this->normalizeDate(isset($doc['payment_due_date']) ? $doc['payment_due_date'] : null);
        if ($documentDueDate) {
            return $documentDueDate;
        }
        $quoteDueDate = $this->normalizeDate(isset($doc['quote_payment_due_date']) ? $doc['quote_payment_due_date'] : null);
        if ($quoteDueDate) {
            return $quoteDueDate;
        }
        $issueDate = $this->normalizeDate(isset($doc['issue_date']) ? $doc['issue_date'] : null);
        if (!$issueDate) {
            return date('Y-m-d', strtotime('-1 day'));
        }
        $days = $this->customerPaymentTermDays($doc);
        return date('Y-m-d', strtotime('+' . $days . ' days', strtotime($issueDate)));
    }

    private function customerPaymentTermDays(array $doc)
    {
        $quoteTerms = isset($doc['payment_terms_selected']) ? trim((string)$doc['payment_terms_selected']) : '';
        if ($quoteTerms !== '') {
            if ($quoteTerms === '0' || stripos($quoteTerms, 'contado') !== false || stripos($quoteTerms, 'prepago') !== false) {
                return 0;
            }
            if (preg_match('/(\d{1,3})/', $quoteTerms, $match)) {
                return max(0, min(365, (int)$match[1]));
            }
        }
        $facto = isset($doc['facto_payment_term']) ? trim((string)$doc['facto_payment_term']) : '';
        if ($facto !== '') {
            $service = new BillingPaymentTermsService();
            return max(0, min(365, $service->daysFromFacto($facto)));
        }
        $label = isset($doc['customer_payment_terms']) ? strtolower(trim((string)$doc['customer_payment_terms'])) : '';
        if ($label === '' || strpos($label, 'contado') !== false || strpos($label, 'prepago') !== false) {
            return 0;
        }
        if (preg_match('/(\d{1,3})/', $label, $match)) {
            return max(0, min(365, (int)$match[1]));
        }
        return 0;
    }

    private function customerPaymentTermsLabel(array $doc)
    {
        $quoteTerms = isset($doc['payment_terms_selected']) ? trim((string)$doc['payment_terms_selected']) : '';
        if ($quoteTerms !== '') {
            if ($quoteTerms === '0' || stripos($quoteTerms, 'contado') !== false || stripos($quoteTerms, 'prepago') !== false) {
                return 'Contado';
            }
            if (preg_match('/(\d{1,3})/', $quoteTerms, $match)) {
                return 'Credito ' . (int)$match[1] . ' dias';
            }
            return $quoteTerms;
        }
        $label = isset($doc['customer_payment_terms']) ? trim((string)$doc['customer_payment_terms']) : '';
        if ($label !== '') return $label;
        $days = $this->customerPaymentTermDays($doc);
        return $days > 0 ? ('Credito ' . $days . ' dias') : 'Contado';
    }

    private function calculateStatus($total, $paid, $dueDate, $existingStatus = null)
    {
        if (in_array($existingStatus, array('anulado','incobrable'), true)) return $existingStatus;
        $balance = max((float)$total - (float)$paid, 0.0);
        if ($balance <= 0.5) return 'pagado';
        if ($dueDate && $dueDate < date('Y-m-d')) return 'atrasado';
        if ($paid > 0.005) return 'parcial';
        return 'por_cobrar';
    }

    private function attachOrphanPaymentsToCollection(array $collection)
    {
        $stmt = $this->pdo->prepare(
            "UPDATE billing_payments
             SET collection_id = ?,
                 quote_id = COALESCE(quote_id, ?),
                 opportunity_id = COALESCE(opportunity_id, ?),
                 customer_id = COALESCE(customer_id, ?),
                 finance_center_id = COALESCE(finance_center_id, ?)
             WHERE environment = ?
               AND direction = 'received'
               AND facto_document_id = ?
               AND collection_id IS NULL"
        );
        $stmt->execute(array(
            (int)$collection['id'],
            $collection['quote_id'],
            $collection['opportunity_id'],
            $collection['customer_id'],
            $collection['finance_center_id'],
            $collection['environment'],
            (string)$collection['facto_document_id'],
        ));
    }

    private function lockAndRefreshCollection($collectionId)
    {
        $stmt = $this->pdo->prepare('SELECT * FROM billing_collections WHERE id = ? FOR UPDATE');
        $stmt->execute(array((int)$collectionId));
        $collection = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$collection || $collection['direction'] !== 'receivable') {
            throw new Exception('Cuenta por cobrar no encontrada.');
        }

        $this->attachOrphanPaymentsToCollection($collection);
        return $this->recalculateCollection((int)$collectionId);
    }

    private function recalculateCollection($collectionId)
    {
        $collection = $this->getCollectionById($collectionId);
        if (!$collection) throw new Exception('Cuenta por cobrar no encontrada.');
        $stmt = $this->pdo->prepare("SELECT COALESCE(SUM(amount), 0), MAX(payment_date) FROM billing_payments WHERE collection_id = ? AND direction = 'received' AND COALESCE(is_voided,0)=0");
        $stmt->execute(array($collectionId));
        $totals = $stmt->fetch(PDO::FETCH_NUM);
        $paid = round((float)$totals[0], 2);
        $lastDate = $totals[1] ?: null;
        $total = round((float)$collection['total_amount'], 2);
        $balance = max(round($total - $paid, 2), 0.0);
        $status = $this->calculateStatus($total, $paid, $collection['due_date'], $collection['collection_status']);
        $currencyCode = strtoupper(trim((string)($collection['currency_code'] ?? 'CLP')));
        $exchangeRate = (float)($collection['exchange_rate_value'] ?? 0);
        $foreignTotal = $collection['foreign_total_amount'];
        $foreignPaid = $collection['foreign_paid_amount'];
        $foreignBalance = $collection['foreign_balance_amount'];
        if ($currencyCode !== 'CLP' && $exchangeRate > 0) {
            $foreignTotal = $foreignTotal !== null ? round((float)$foreignTotal, 2) : round($total / $exchangeRate, 2);
            $foreignPaid = round($paid / $exchangeRate, 2);
            $foreignBalance = round($balance / $exchangeRate, 2);
        }

        $stmt = $this->pdo->prepare('UPDATE billing_collections SET paid_amount = ?, balance_amount = ?, collection_status = ?, last_payment_date = ?, foreign_total_amount = ?, foreign_paid_amount = ?, foreign_balance_amount = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?');
        $stmt->execute(array($paid, $balance, $status, $lastDate, $foreignTotal, $foreignPaid, $foreignBalance, $collectionId));
        $updated = $this->getCollectionById($collectionId);

        $docStmt = $this->pdo->prepare("UPDATE facto_documents SET payment_status = ?, payment_due_date = ?, payment_paid_date = ?, updated_at = CURRENT_TIMESTAMP WHERE environment = ? AND document_id = ?");
        $docStmt->execute(array($status, $updated['due_date'], $status === 'pagado' ? $lastDate : null, $updated['environment'], $updated['facto_document_id']));
        return $updated;
    }

    private function syncRelatedCommercialStatus($collectionId, $notes = null, $userId = null, $createActivity = true)
    {
        $collection = $this->getCollectionById($collectionId);
        if (!$collection) return;
        $status = $collection['collection_status'];
        $paidDate = $status === 'pagado' ? $collection['last_payment_date'] : null;

        if (!empty($collection['quote_id'])) {
            $quoteStatus = $status === 'parcial' ? 'por_cobrar' : $status;
            $commercialStatus = $status === 'pagado'
                ? 'Pagada'
                : (in_array($status, array('por_cobrar', 'parcial', 'atrasado'), true) ? 'Esperando pago' : null);
            $stmt = $this->pdo->prepare("UPDATE crm_quotes SET status = COALESCE(?, status), payment_status = ?, payment_due_date = ?, payment_paid_date = ?, payment_date = ?, payment_amount = ?, payment_notes = COALESCE(?, payment_notes) WHERE id = ?");
            $stmt->execute(array($commercialStatus, $quoteStatus, $collection['due_date'], $paidDate, $paidDate, $collection['paid_amount'], $notes, (int)$collection['quote_id']));
        }
        if (!empty($collection['opportunity_id'])) {
            $opportunityStatus = $status === 'parcial' ? 'por_cobrar' : $status;
            if (!in_array($opportunityStatus, array('por_cobrar','pagado','atrasado','incobrable','anulado'), true)) {
                $opportunityStatus = 'por_cobrar';
            }
            $stmt = $this->pdo->prepare("UPDATE crm_opportunities SET payment_status = ?, payment_due_date = ?, payment_paid_date = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?");
            $stmt->execute(array($opportunityStatus, $collection['due_date'], $paidDate, (int)$collection['opportunity_id']));
        }

        if ($createActivity && !empty($collection['customer_id'])) {
            $ownerName = $this->userName($userId);
            $title = $status === 'pagado' ? 'Pago recibido - factura ' . $collection['document_number'] : 'Pago parcial - factura ' . $collection['document_number'];
            $description = 'Pago acumulado: $' . number_format((float)$collection['paid_amount'], 0, ',', '.') . "\nSaldo pendiente: $" . number_format((float)$collection['balance_amount'], 0, ',', '.') . ($notes ? "\nNota: " . $notes : '');
            $stmt = $this->pdo->prepare("INSERT INTO crm_activities (opportunity_id, customer_id, customer_name, activity_type, title, description, activity_date, owner_name) SELECT ?, c.id, c.customer_name, 'factura', ?, ?, NOW(), ? FROM crm_customers c WHERE c.id = ? LIMIT 1");
            $stmt->execute(array($collection['opportunity_id'] ?: null, $title, $description, $ownerName, (int)$collection['customer_id']));
        }
    }

    private function updateFactoPaymentState(array $doc, array $collection)
    {
        $stmt = $this->pdo->prepare('UPDATE facto_documents SET crm_customer_id = ?, finance_center_id = ?, payment_status = ?, payment_due_date = ?, payment_paid_date = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?');
        $stmt->execute(array(
            $collection['customer_id'],
            $collection['finance_center_id'],
            $collection['collection_status'],
            $collection['due_date'],
            $collection['collection_status'] === 'pagado' ? $collection['last_payment_date'] : null,
            (int)$doc['id'],
        ));
    }

    private function insertPaymentBatch($environment, $date, $methodId, $accountId, $amount, $reference, $notes, $userId, $idempotencyKey = null)
    {
        $stmt = $this->pdo->prepare("INSERT INTO billing_payment_batches (environment, direction, payment_date, payment_method_id, cash_account_id, amount_total, amount_allocated, external_reference, notes, idempotency_key, created_by) VALUES (?, 'incoming', ?, ?, ?, ?, 0, ?, ?, ?, ?)");
        $stmt->execute(array($environment, $date, $methodId, $accountId, $amount, $reference, $notes, $idempotencyKey, $this->nullableInt($userId)));
        return (int)$this->pdo->lastInsertId();
    }

    private function insertPayment(array $doc, array $collection, $batchId, $date, $amount, array $method, $accountId, $reference, $notes, $userId)
    {
        $currencyCode = strtoupper(trim((string)($collection['currency_code'] ?? ($doc['currency_code'] ?? 'CLP'))));
        if ($currencyCode === '') $currencyCode = 'CLP';
        $exchangeRate = (float)($collection['exchange_rate_value'] ?? ($doc['exchange_rate_value'] ?? 1));
        if ($currencyCode === 'CLP') $exchangeRate = 1.0;
        $foreignAmount = ($currencyCode !== 'CLP' && $exchangeRate > 0) ? round(((float)$amount) / $exchangeRate, 4) : null;

        $cols = array(
            'payment_batch_id','collection_id','environment','direction','facto_document_id','quote_id','opportunity_id','customer_id',
            'payment_date','amount','payment_method','payment_method_id','cash_account_id','external_reference','finance_center_id','notes','created_by'
        );
        $vals = array(
            $batchId, (int)$collection['id'], $doc['environment'], 'received', $doc['document_id'],
            $this->nullableInt($doc['crm_quote_id']), $this->nullableInt($doc['crm_opportunity_id']), $this->nullableInt($doc['crm_customer_id']),
            $date, $amount, $method['method_name'], (int)$method['id'], $accountId, $reference,
            $this->nullableInt($collection['finance_center_id']), $notes, $this->nullableInt($userId)
        );

        if (billing_table_has_column($this->pdo, 'billing_payments', 'currency_code')) {
            $cols = array_merge($cols, array('currency_code','exchange_rate_value','exchange_rate_date','exchange_rate_source','exchange_rate_status','foreign_amount'));
            $vals = array_merge($vals, array(
                $currencyCode,
                $exchangeRate > 0 ? $exchangeRate : null,
                $collection['exchange_rate_date'] ?? ($doc['exchange_rate_date'] ?? null),
                $collection['exchange_rate_source'] ?? ($doc['exchange_rate_source'] ?? null),
                $collection['exchange_rate_status'] ?? ($doc['exchange_rate_status'] ?? null),
                $foreignAmount
            ));
        }

        $placeholders = implode(',', array_fill(0, count($cols), '?'));
        $sql = 'INSERT INTO billing_payments (' . implode(',', $cols) . ') VALUES (' . $placeholders . ')';
        $this->pdo->prepare($sql)->execute($vals);
        return (int)$this->pdo->lastInsertId();
    }

    private function insertAllocation($batchId, $paymentId, $collectionId, $sourceType, $sourceId, $amount)
    {
        $stmt = $this->pdo->prepare('INSERT INTO billing_payment_allocations (payment_batch_id, payment_id, collection_id, source_type, source_id, amount_applied) VALUES (?, ?, ?, ?, ?, ?)');
        $stmt->execute(array($batchId, $paymentId, $collectionId, $sourceType, $sourceId, $amount));
    }


    private function lockPaymentAccount($cashAccountId)
    {
        $stmt = $this->pdo->prepare('SELECT id FROM billing_cash_accounts WHERE id = ? AND is_active = 1 FOR UPDATE');
        $stmt->execute(array((int)$cashAccountId));
        if (!$stmt->fetchColumn()) {
            throw new Exception('La caja o cuenta seleccionada ya no está disponible.');
        }
    }

    private function normalizePaymentIdempotencyKey($value, $environment)
    {
        $key = trim((string)$value);
        if ($key === '') {
            if ($environment === 'production') {
                throw new Exception('La operación no incluye una clave de idempotencia válida. Actualiza la pantalla y vuelve a intentar.');
            }
            return null;
        }
        if (strlen($key) < 16 || strlen($key) > 64 || !preg_match('/^[A-Za-z0-9._:-]+$/', $key)) {
            throw new Exception('Clave de idempotencia inválida. Actualiza la pantalla y vuelve a intentar.');
        }
        return $key;
    }

    private function normalizeExpectedBalance($value, $environment)
    {
        if ($value === null || $value === '') {
            if ($environment === 'production') {
                throw new Exception('Falta el saldo esperado de la cuenta. Actualiza la pantalla y vuelve a intentar.');
            }
            return null;
        }
        if (!is_numeric($value)) {
            throw new Exception('Saldo esperado inválido.');
        }
        return round((float)$value, 2);
    }

    private function assertProductionPaymentAuditFields($environment, $reference, $idempotencyKey)
    {
        if ($environment !== 'production') {
            return;
        }
        if ($idempotencyKey === null) {
            throw new Exception('Clave de idempotencia requerida para cobros productivos.');
        }
        $reference = trim((string)$reference);
        if ($reference === '') {
            throw new Exception('Ingresa una referencia verificable para el cobro productivo.');
        }
        if (strlen($reference) > 120) {
            throw new Exception('La referencia del pago no puede superar 120 caracteres.');
        }
        $normalizedReference = strtolower(preg_replace('/\s+/', ' ', $reference));
        $weakReferences = array('na', 'n/a', 'n.a.', 'no aplica', 'sin referencia', 'sin comprobante', 's/r', 'sr', 'none', 'null', '-', '--');
        if (strlen($reference) < 6 || in_array($normalizedReference, $weakReferences, true)) {
            throw new Exception('La referencia productiva debe identificar el movimiento. Usa el número de operación o una referencia interna única de al menos 6 caracteres.');
        }
    }

    private function assertPaymentReferenceAvailable($environment, $batchDirection, $cashAccountId, $reference, $idempotencyKey)
    {
        if ($environment !== 'production') {
            return;
        }
        $stmt = $this->pdo->prepare("SELECT id, idempotency_key FROM billing_payment_batches WHERE BINARY environment = BINARY ? AND BINARY direction = BINARY ? AND cash_account_id = ? AND BINARY external_reference = BINARY ? LIMIT 1");
        $stmt->execute(array($environment, $batchDirection, (int)$cashAccountId, trim((string)$reference)));
        $existing = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($existing && (string)$existing['idempotency_key'] !== (string)$idempotencyKey) {
            throw new Exception('La referencia ya fue utilizada en otra operación de la misma caja o cuenta. Verifica el movimiento antes de continuar.');
        }
    }

    private function findPaymentReplay($environment, $batchDirection, $idempotencyKey, $collectionId, $paymentDirection, $factoDocumentId, $inboxDocumentId, $paymentDate, $amount, $methodId, $cashAccountId, $reference)
    {
        if ($idempotencyKey === null || $idempotencyKey === '') {
            return null;
        }
        $stmt = $this->pdo->prepare("SELECT * FROM billing_payment_batches WHERE BINARY environment = BINARY ? AND BINARY direction = BINARY ? AND BINARY idempotency_key = BINARY ? LIMIT 1");
        $stmt->execute(array($environment, $batchDirection, $idempotencyKey));
        $batch = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$batch) {
            return null;
        }
        $batchMatches = (string)$batch['payment_date'] === (string)$paymentDate
            && (int)$batch['payment_method_id'] === (int)$methodId
            && (int)$batch['cash_account_id'] === (int)$cashAccountId
            && abs((float)$batch['amount_total'] - (float)$amount) <= 0.01
            && trim((string)$batch['external_reference']) === trim((string)$reference);
        if (!$batchMatches) {
            throw new Exception('La clave de idempotencia ya fue utilizada por otra operación. Actualiza la pantalla.');
        }
        $stmt = $this->pdo->prepare("SELECT id, collection_id, facto_document_id, inbox_document_id, amount FROM billing_payments WHERE payment_batch_id = ? AND BINARY direction = BINARY ? ORDER BY id");
        $stmt->execute(array((int)$batch['id'], $paymentDirection));
        $payments = $stmt->fetchAll(PDO::FETCH_ASSOC);
        if (count($payments) !== 1) {
            throw new Exception('La operación idempotente existente está incompleta o no corresponde a un pago individual. Requiere revisión.');
        }
        $payment = $payments[0];
        $paymentMatches = (int)$payment['collection_id'] === (int)$collectionId
            && abs((float)$payment['amount'] - (float)$amount) <= 0.01
            && ($factoDocumentId === null || (string)$payment['facto_document_id'] === (string)$factoDocumentId)
            && ($inboxDocumentId === null || (string)$payment['inbox_document_id'] === (string)$inboxDocumentId);
        if (!$paymentMatches) {
            throw new Exception('La clave de idempotencia ya fue utilizada para otro documento o monto. Actualiza la pantalla.');
        }
        return array('batch_id' => (int)$batch['id'], 'payment_id' => (int)$payment['id']);
    }

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

    private function getCollectionById($id)
    {
        $stmt = $this->pdo->prepare('SELECT * FROM billing_collections WHERE id = ? LIMIT 1');
        $stmt->execute(array($id));
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }


    private function ensureLegacyPaidPayment(array $doc, $environment, $total)
    {
        if (($doc['payment_status'] ?? '') !== 'pagado' || $total <= 0) {
            return;
        }
        $stmt = $this->pdo->prepare("SELECT COUNT(*) FROM billing_payments WHERE environment = ? AND direction = 'received' AND facto_document_id = ?");
        $stmt->execute(array($environment, (string)$doc['document_id']));
        if ((int)$stmt->fetchColumn() > 0) {
            return;
        }

        $paymentDate = $this->normalizeDate($doc['payment_paid_date'] ?? null) ?: $this->normalizeDate($doc['issue_date'] ?? null) ?: date('Y-m-d');
        $currencyCode = strtoupper(trim((string)($doc['currency_code'] ?? 'CLP')));
        if ($currencyCode === '') $currencyCode = 'CLP';
        $exchangeRate = (float)($doc['exchange_rate_value'] ?? 1);
        if ($currencyCode === 'CLP') $exchangeRate = 1.0;
        $foreignAmount = ($currencyCode !== 'CLP' && $exchangeRate > 0)
            ? (isset($doc['total_amount']) ? round((float)$doc['total_amount'], 4) : round(((float)$total) / $exchangeRate, 4))
            : null;

        $cols = array('environment','direction','facto_document_id','quote_id','opportunity_id','customer_id','payment_date','amount','payment_method','finance_center_id','notes');
        $vals = array(
            $environment, 'received', (string)$doc['document_id'],
            $this->nullableInt($doc['crm_quote_id'] ?? null),
            $this->nullableInt($doc['crm_opportunity_id'] ?? null),
            $this->nullableInt($doc['crm_customer_id'] ?? null),
            $paymentDate, $total, 'Pago historico / no especificado',
            $this->nullableInt($doc['effective_finance_center_id'] ?? null),
            'Saldo pagado migrado desde el estado previo del documento.'
        );
        if (billing_table_has_column($this->pdo, 'billing_payments', 'currency_code')) {
            $cols = array_merge($cols, array('currency_code','exchange_rate_value','exchange_rate_date','exchange_rate_source','exchange_rate_status','foreign_amount'));
            $vals = array_merge($vals, array(
                $currencyCode, $exchangeRate > 0 ? $exchangeRate : null,
                $doc['exchange_rate_date'] ?? ($doc['issue_date'] ?? null),
                $doc['exchange_rate_source'] ?? ($currencyCode === 'CLP' ? 'CLP' : null),
                $doc['exchange_rate_status'] ?? ($currencyCode === 'CLP' ? 'validated' : null),
                $foreignAmount
            ));
        }
        $sql = 'INSERT INTO billing_payments (' . implode(',', $cols) . ') VALUES (' . implode(',', array_fill(0, count($cols), '?')) . ')';
        $this->pdo->prepare($sql)->execute($vals);
    }

    private function sumPaymentsForDocument($environment, $documentId)
    {
        $stmt = $this->pdo->prepare("SELECT COALESCE(SUM(amount), 0) FROM billing_payments WHERE environment = ? AND direction = 'received' AND facto_document_id = ? AND COALESCE(is_voided,0)=0");
        $stmt->execute(array($environment, $documentId));
        return round((float)$stmt->fetchColumn(), 2);
    }

    private function lastPaymentDateForDocument($environment, $documentId)
    {
        $stmt = $this->pdo->prepare("SELECT MAX(payment_date) FROM billing_payments WHERE environment = ? AND direction = 'received' AND facto_document_id = ? AND COALESCE(is_voided,0)=0");
        $stmt->execute(array($environment, $documentId));
        return $stmt->fetchColumn() ?: null;
    }

    private function userName($userId)
    {
        if (!$userId) return 'Sistema';
        $stmt = $this->pdo->prepare('SELECT name FROM users WHERE id = ? LIMIT 1');
        $stmt->execute(array((int)$userId));
        return $stmt->fetchColumn() ?: 'Sistema';
    }

    private function normalizeEnvironment($environment)
    {
        return strtolower(trim((string)$environment)) === 'production' ? 'production' : 'sandbox';
    }

    private function normalizeDate($date)
    {
        if (!$date) return null;
        $ts = strtotime((string)$date);
        return $ts ? date('Y-m-d', $ts) : null;
    }

    private function nullableInt($value)
    {
        $value = (int)$value;
        return $value > 0 ? $value : null;
    }

    private function requiredPositiveInt($value, $message)
    {
        $value = (int)$value;
        if ($value <= 0) throw new Exception($message);
        return $value;
    }
}
