<?php

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

class BillingPayablesService
{
    private $pdo;

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

    public function listPayables(array $filters = array())
    {
        $environment = $this->normalizeEnvironment(isset($filters['environment']) ? $filters['environment'] : 'sandbox');
        $this->resolveSupplierDefaults($environment, 200);
        $this->refreshCalculatedPayableStatuses($environment);
        $status = isset($filters['status']) ? trim((string)$filters['status']) : 'open';
        $search = isset($filters['search']) ? trim((string)$filters['search']) : '';
        $dateFrom = isset($filters['date_from']) ? trim((string)$filters['date_from']) : '';
        $dateTo = isset($filters['date_to']) ? trim((string)$filters['date_to']) : '';
        $limit = isset($filters['limit']) ? max(1, min(200, (int)$filters['limit'])) : 80;

        $this->refreshOverdue($environment);

        $where = array('d.environment = ?');
        $params = array($environment);

        if ($status !== '' && $status !== 'all') {
            if (in_array($status, array('open','impago','pendiente'), true)) {
                $where[] = "COALESCE(c.collection_status, d.payment_status, 'por_pagar') IN ('por_pagar','vencido','parcial')";
            } else {
                $where[] = "COALESCE(c.collection_status, d.payment_status, 'por_pagar') = ?";
                $params[] = $status;
            }
        }

        if ($dateFrom !== '') {
            $where[] = 'd.document_date >= ?';
            $params[] = $dateFrom;
        }

        if ($dateTo !== '') {
            $where[] = 'd.document_date <= ?';
            $params[] = $dateTo;
        }

        if ($search !== '') {
            $where[] = '(d.issuer_name LIKE ? OR d.issuer_tax_id_code LIKE ? OR d.document_number LIKE ?)';
            $like = '%' . $search . '%';
            $params[] = $like;
            $params[] = $like;
            $params[] = $like;
        }

        $sql = "
            SELECT
                d.id,
                d.environment,
                d.inbox_document_id,
                d.document_id,
                d.document_date,
                d.receive_date,
                d.issuer_name,
                d.issuer_tax_id_code,
                d.document_type_id,
                d.document_type_taxbureau,
                d.document_number,
                d.net_amount,
                d.taxes_amount,
                d.total_amount,
                d.supplier_customer_id,
                d.finance_center_id,
                COALESCE(d.finance_center_id, s.supplier_default_cost_center_id) AS effective_finance_center_id,
                CASE WHEN d.finance_center_id IS NOT NULL THEN 'document_override' WHEN s.supplier_default_cost_center_id IS NOT NULL THEN 'supplier_default' ELSE 'none' END AS finance_center_source,
                COALESCE(c.collection_status, d.payment_status, 'por_pagar') AS payment_status,
                d.payment_due_date,
                d.payment_paid_date,
                d.payment_terms_id,
                d.supplier_resolution_status,
                d.supplier_resolution_notes,
                d.supplier_resolved_at,
                d.document_approval_status,
                d.reception_confirmation_status,
                d.product_receipt_status,
                d.pdf_file_path,
                d.xml_file_path,
                s.customer_name AS supplier_name,
                s.customer_tax_id AS supplier_tax_id,
                s.is_supplier,
                s.supplier_default_cost_center_id,
                s.supplier_payment_terms_id,
                pt.term_label AS supplier_payment_term_label,
                pt.days AS supplier_payment_term_days,
                COALESCE(fc_doc.center_name, fc_supplier.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 c.id IS NULL THEN d.total_amount ELSE c.balance_amount END AS balance_amount,
                c.last_payment_date
            FROM facto_inbox_documents d
            LEFT JOIN crm_customers s ON s.id = d.supplier_customer_id
            LEFT JOIN finance_centers fc_doc ON fc_doc.id = d.finance_center_id
            LEFT JOIN finance_centers fc_supplier ON fc_supplier.id = s.supplier_default_cost_center_id
            LEFT JOIN billing_payment_terms pt ON pt.id = COALESCE(d.payment_terms_id, s.supplier_payment_terms_id)
            LEFT JOIN billing_collections c ON c.direction = 'payable'
                AND c.environment = d.environment
                AND c.inbox_document_id = d.inbox_document_id
            WHERE " . implode(' AND ', $where) . "
            ORDER BY COALESCE(c.due_date, d.payment_due_date, d.document_date) DESC, d.id DESC
            LIMIT " . (int)$limit;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return array(
            'kpis' => $this->getKpis($environment),
            'rows' => $rows,
            'filters' => array(
                'environment' => $environment,
                'status' => $status,
                'search' => $search,
                'date_from' => $dateFrom,
                'date_to' => $dateTo,
                'limit' => $limit
            )
        );
    }

    public function getKpis($environment = 'sandbox')
    {
        $environment = $this->normalizeEnvironment($environment);
        $this->resolveSupplierDefaults($environment, 200);
        $this->refreshCalculatedPayableStatuses($environment);
        $this->refreshOverdue($environment);

        $sql = "
            SELECT
                COUNT(*) AS documents_count,
                COALESCE(SUM(d.total_amount), 0) AS total_received,
                COALESCE(SUM(CASE WHEN COALESCE(c.collection_status, d.payment_status, 'por_pagar') IN ('por_pagar','vencido','parcial') THEN CASE WHEN c.id IS NULL THEN d.total_amount ELSE c.balance_amount END ELSE 0 END), 0) AS open_balance,
                COALESCE(SUM(CASE WHEN COALESCE(c.collection_status, d.payment_status) = 'vencido' THEN CASE WHEN c.id IS NULL THEN d.total_amount ELSE c.balance_amount END ELSE 0 END), 0) AS overdue_balance,
                COALESCE(SUM(CASE WHEN d.supplier_customer_id IS NULL THEN 1 ELSE 0 END), 0) AS unlinked_suppliers,
                COALESCE(SUM(CASE WHEN COALESCE(d.finance_center_id, s.supplier_default_cost_center_id) IS NULL THEN 1 ELSE 0 END), 0) AS without_center
            FROM facto_inbox_documents d
            LEFT JOIN crm_customers s ON s.id = d.supplier_customer_id
            LEFT JOIN billing_collections c ON c.direction = 'payable'
                AND c.environment = d.environment
                AND c.inbox_document_id = d.inbox_document_id
            WHERE d.environment = ?
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array($environment));
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: array();
    }

    public function getDocument($idOrInboxId, $environment = 'sandbox')
    {
        $environment = $this->normalizeEnvironment($environment);

        // IMPORTANTE: las vistas de Cuentas por pagar envian d.id (PK local).
        // El inbox_document_id de FACTO puede coincidir numericamente con el id local de otro DTE.
        // Por eso se busca primero por PK local y solo si no existe se usa inbox_document_id.
        $row = $this->fetchDocumentByCondition('d.id = ?', array((string)$idOrInboxId), $environment);
        if (!$row) {
            $row = $this->fetchDocumentByCondition('d.inbox_document_id = ?', array((string)$idOrInboxId), $environment);
        }

        if (!$row) {
            throw new Exception('Documento recibido no encontrado.');
        }

        if (empty($row['supplier_customer_id']) || empty($row['payment_due_date']) || empty($row['payment_terms_id'])) {
            try {
                $resolver = new BillingSupplierResolverService($this->pdo);
                $resolver->resolveDocumentByLocalId((int)$row['id'], $environment, true);
                $row = $this->fetchDocumentByCondition('d.id = ?', array((string)$row['id']), $environment);
            } catch (Throwable $e) {
                // No bloquear el detalle por fallas de resolucion de proveedor.
            }
        }

        $this->refreshCalculatedPayableStatuses($environment, (int)$row['id']);
        $row = $this->fetchDocumentByCondition('d.id = ?', array((string)$row['id']), $environment) ?: $row;

        return $row;
    }

    private function getDocumentByInboxId($inboxDocumentId, $environment = 'sandbox')
    {
        $environment = $this->normalizeEnvironment($environment);
        $row = $this->fetchDocumentByCondition('d.inbox_document_id = ?', array((string)$inboxDocumentId), $environment);
        if (!$row) {
            throw new Exception('Documento recibido no encontrado por inbox_document_id.');
        }
        return $row;
    }

    private function fetchDocumentByCondition($condition, array $params, $environment)
    {
        $sql = " 
            SELECT d.*, 
                   s.customer_name AS supplier_name,
                   s.customer_tax_id AS supplier_tax_id,
                   s.is_supplier,
                   s.supplier_default_cost_center_id,
                   s.supplier_payment_terms_id,
                   COALESCE(d.finance_center_id, s.supplier_default_cost_center_id) AS effective_finance_center_id,
                   CASE WHEN d.finance_center_id IS NOT NULL THEN 'document_override' WHEN s.supplier_default_cost_center_id IS NOT NULL THEN 'supplier_default' ELSE 'none' END AS finance_center_source,
                   pt.term_label AS supplier_payment_term_label,
                   pt.days AS supplier_payment_term_days,
                   COALESCE(fc_doc.center_name, fc_supplier.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 c.id IS NULL THEN d.total_amount ELSE c.balance_amount END AS balance_amount,
                   c.last_payment_date,
                   c.notes AS collection_notes
            FROM facto_inbox_documents d
            LEFT JOIN crm_customers s ON s.id = d.supplier_customer_id
            LEFT JOIN finance_centers fc_doc ON fc_doc.id = d.finance_center_id
            LEFT JOIN finance_centers fc_supplier ON fc_supplier.id = s.supplier_default_cost_center_id
            LEFT JOIN billing_payment_terms pt ON pt.id = COALESCE(d.payment_terms_id, s.supplier_payment_terms_id)
            LEFT JOIN billing_collections c ON c.direction = 'payable'
                AND c.environment = d.environment
                AND c.inbox_document_id = d.inbox_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);
    }

    public function getPaymentHistory($idOrInboxId, $environment = 'sandbox')
    {
        $environment = $this->normalizeEnvironment($environment);
        $doc = $this->getDocument($idOrInboxId, $environment);
        if (!$doc['collection_id']) {
            return array();
        }

        $stmt = $this->pdo->prepare(" 
            SELECT p.id,
                   p.payment_date,
                   p.amount,
                   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 = 'paid'
            ORDER BY p.payment_date DESC, p.id DESC
        ");
        $stmt->execute(array((int)$doc['collection_id']));
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getOptions($environment = 'sandbox')
    {
        $environment = $this->normalizeEnvironment($environment);
        $paymentOptions = new BillingPaymentOptionsService($this->pdo);

        return array(
            'environment' => $environment,
            'finance_centers' => $this->listFinanceCenters(),
            'payment_methods' => $paymentOptions->listPaymentMethods(),
            'cash_accounts' => $paymentOptions->listCashAccounts(),
            'payment_terms' => (new BillingSupplierResolverService($this->pdo))->listPaymentTerms(),
            'statuses' => array(
                array('value' => 'open', 'label' => 'Impagos / parciales'),
                array('value' => 'por_pagar', 'label' => 'Por pagar'),
                array('value' => 'vencido', 'label' => 'Vencido'),
                array('value' => 'parcial', 'label' => 'Parcial'),
                array('value' => 'pagado', 'label' => 'Pagado'),
                array('value' => 'rechazado', 'label' => 'Rechazado'),
                array('value' => 'anulado', 'label' => 'Anulado')
            )
        );
    }

    public function updateDocument($idOrInboxId, array $data, $userId = null)
    {
        $environment = $this->normalizeEnvironment(isset($data['environment']) ? $data['environment'] : 'sandbox');
        $doc = $this->getDocument($idOrInboxId, $environment);
        $financeCenterId = $this->nullableInt(isset($data['finance_center_id']) ? $data['finance_center_id'] : null);
        $dueDate = $this->resolveCalculatedDueDate($doc);
        $status = $this->calculateStatusFromAmounts($doc, $dueDate);
        $notes = isset($data['notes']) ? trim((string)$data['notes']) : null;

        // La fecha de vencimiento no se edita desde cuentas por pagar.
        // Se calcula por condicion de pago del proveedor. Si no hay datos confiables, queda vencida.
        $stmt = $this->pdo->prepare(" 
            UPDATE facto_inbox_documents
            SET finance_center_id = ?, payment_due_date = ?, payment_status = ?, updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
        ");
        $stmt->execute(array($financeCenterId, $dueDate, $status, $doc['id']));

        $collection = $this->ensurePayableCollection($doc['inbox_document_id'], $environment, array(
            'finance_center_id' => $financeCenterId !== null ? $financeCenterId : (isset($doc['supplier_default_cost_center_id']) ? $doc['supplier_default_cost_center_id'] : null),
            'due_date' => $dueDate,
            'collection_status' => $status,
            'notes' => $notes
        ));

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

    public function autoLinkSupplier($idOrInboxId, $environment = 'sandbox')
    {
        $environment = $this->normalizeEnvironment($environment);
        $doc = $this->getDocument($idOrInboxId, $environment);
        $rut = trim((string)$doc['issuer_tax_id_code']);
        if ($rut === '') {
            throw new Exception('El documento recibido no tiene RUT de emisor para homologar proveedor.');
        }

        $stmt = $this->pdo->prepare(" 
            SELECT id, customer_name, customer_tax_id
            FROM crm_customers
            WHERE deleted_at IS NULL
              AND UPPER(REPLACE(customer_tax_id, '.', '')) = UPPER(REPLACE(?, '.', ''))
            LIMIT 1
        ");
        $stmt->execute(array($rut));
        $supplier = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$supplier) {
            throw new Exception('No existe proveedor/cliente homologable con RUT ' . $rut . '. Crear o sincronizar proveedor antes de vincular.');
        }

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

        $this->ensurePayableCollection($doc['inbox_document_id'], $environment, array(
            'customer_id' => (int)$supplier['id']
        ));

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

    public function registerPayment($idOrInboxId, array $data, $userId = null)
    {
        $environment = $this->normalizeEnvironment(isset($data['environment']) ? $data['environment'] : 'sandbox');
        $doc = $this->getDocument($idOrInboxId, $environment);
        $paymentDate = $this->normalizeDate(isset($data['payment_date']) ? $data['payment_date'] : date('Y-m-d'));
        $amount = isset($data['amount']) ? (float)$data['amount'] : 0;
        $methodId = $this->nullableInt(isset($data['payment_method_id']) ? $data['payment_method_id'] : null);
        $cashAccountId = $this->nullableInt(isset($data['cash_account_id']) ? $data['cash_account_id'] : null);
        $reference = isset($data['external_reference']) ? trim((string)$data['external_reference']) : null;
        $notes = isset($data['notes']) ? trim((string)$data['notes']) : null;

        $this->validatePaymentHeader($paymentDate, $amount, $methodId, $cashAccountId);
        $options = new BillingPaymentOptionsService($this->pdo);
        $method = $options->getPaymentMethodById($methodId);
        $account = $options->getCashAccountById($cashAccountId);
        if (!$method) {
            throw new Exception('Metodo de pago no valido.');
        }
        if (!$account) {
            throw new Exception('Caja/cuenta no valida.');
        }

        $this->pdo->beginTransaction();
        try {
            $collection = $this->ensurePayableCollection($doc['inbox_document_id'], $environment);
            $collection = $this->getCollectionById((int)$collection['id']);
            $balance = (float)$collection['balance_amount'];
            if ($balance <= 0.5) {
                throw new Exception('El documento ya no tiene saldo pendiente.');
            }
            if ($amount > $balance + 0.5) {
                throw new Exception('El monto a pagar supera el saldo pendiente del documento. Saldo: ' . $this->formatMoney($balance));
            }

            $batchId = $this->createPaymentBatch($environment, $paymentDate, $methodId, $cashAccountId, $amount, $reference, $notes, $userId);
            $paymentId = $this->insertPaymentForCollection($collection, $doc, $environment, $paymentDate, $amount, $method, $methodId, $cashAccountId, $reference, $notes, $userId, $batchId);
            $this->insertAllocation($batchId, $paymentId, (int)$collection['id'], $doc['inbox_document_id'], $amount);
            $this->updateBatchAllocated($batchId);
            $this->recalculateCollection((int)$collection['id']);
            $this->pdo->commit();
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }

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

    public function registerBulkPayment(array $ids, array $data, $userId = null)
    {
        $environment = $this->normalizeEnvironment(isset($data['environment']) ? $data['environment'] : 'sandbox');
        $paymentDate = $this->normalizeDate(isset($data['payment_date']) ? $data['payment_date'] : date('Y-m-d'));
        $amount = isset($data['amount']) ? (float)$data['amount'] : 0;
        $methodId = $this->nullableInt(isset($data['payment_method_id']) ? $data['payment_method_id'] : null);
        $cashAccountId = $this->nullableInt(isset($data['cash_account_id']) ? $data['cash_account_id'] : null);
        $reference = isset($data['external_reference']) ? trim((string)$data['external_reference']) : null;
        $notes = isset($data['notes']) ? trim((string)$data['notes']) : null;
        $fullPayment = !empty($data['full_payment']);

        $ids = array_values(array_unique(array_filter(array_map('trim', $ids), function($v) { return $v !== ''; })));
        if (count($ids) < 1) {
            throw new Exception('Debes seleccionar al menos un documento.');
        }
        if (count($ids) > 100) {
            throw new Exception('El pago masivo permite maximo 100 documentos por operacion.');
        }
        $this->validatePaymentHeader($paymentDate, $amount > 0 ? $amount : 1, $methodId, $cashAccountId);

        $options = new BillingPaymentOptionsService($this->pdo);
        $method = $options->getPaymentMethodById($methodId);
        $account = $options->getCashAccountById($cashAccountId);
        if (!$method) {
            throw new Exception('Metodo de pago no valido.');
        }
        if (!$account) {
            throw new Exception('Caja/cuenta no valida.');
        }

        $docs = array();
        $totalBalance = 0.0;
        foreach ($ids as $id) {
            $doc = $this->getDocument($id, $environment);
            $collection = $this->ensurePayableCollection($doc['inbox_document_id'], $environment);
            $collection = $this->getCollectionById((int)$collection['id']);
            $balance = (float)$collection['balance_amount'];
            if ($balance <= 0.5) {
                continue;
            }
            $docs[] = array('document' => $doc, 'collection' => $collection, 'balance' => $balance);
            $totalBalance += $balance;
        }

        if (!$docs) {
            throw new Exception('Los documentos seleccionados no tienen saldo pendiente.');
        }

        if ($fullPayment || $amount <= 0) {
            $amount = $totalBalance;
        }
        if ($amount > $totalBalance + 0.5) {
            throw new Exception('El monto total supera el saldo pendiente seleccionado. Saldo seleccionado: ' . $this->formatMoney($totalBalance));
        }

        $this->pdo->beginTransaction();
        $processed = array();
        $remaining = $amount;
        try {
            $batchId = $this->createPaymentBatch($environment, $paymentDate, $methodId, $cashAccountId, $amount, $reference, $notes, $userId);
            foreach ($docs as $entry) {
                if ($remaining <= 0.5) {
                    break;
                }
                $apply = min($entry['balance'], $remaining);
                if ($apply <= 0) {
                    continue;
                }
                $paymentId = $this->insertPaymentForCollection(
                    $entry['collection'],
                    $entry['document'],
                    $environment,
                    $paymentDate,
                    $apply,
                    $method,
                    $methodId,
                    $cashAccountId,
                    $reference,
                    $notes,
                    $userId,
                    $batchId
                );
                $this->insertAllocation($batchId, $paymentId, (int)$entry['collection']['id'], $entry['document']['inbox_document_id'], $apply);
                $this->recalculateCollection((int)$entry['collection']['id']);
                $updated = $this->getDocument($entry['document']['id'], $environment);
                $processed[] = array(
                    'id' => $entry['document']['id'],
                    'folio' => $entry['document']['document_number'],
                    'supplier' => $entry['document']['supplier_name'] ?: $entry['document']['issuer_name'],
                    'amount_applied' => $apply,
                    'balance_after' => (float)$updated['balance_amount'],
                    'status_after' => $updated['collection_status'] ?: $updated['payment_status']
                );
                $remaining -= $apply;
            }
            $this->updateBatchAllocated($batchId);
            $this->pdo->commit();
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }

        return array(
            'batch_id' => $batchId,
            'amount_total' => $amount,
            'amount_allocated' => $amount - max($remaining, 0),
            'unallocated' => max($remaining, 0),
            'processed' => $processed,
            'selected_balance' => $totalBalance
        );
    }

    public function ensurePayableCollection($inboxDocumentId, $environment = 'sandbox', array $overrides = array())
    {
        $environment = $this->normalizeEnvironment($environment);
        $doc = $this->getDocumentByInboxId($inboxDocumentId, $environment);
        if (empty($doc['supplier_customer_id']) || empty($doc['payment_due_date']) || empty($doc['payment_terms_id'])) {
            try {
                $resolver = new BillingSupplierResolverService($this->pdo);
                $resolver->resolveDocumentByLocalId((int)$doc['id'], $environment, true);
                $doc = $this->getDocumentByInboxId($inboxDocumentId, $environment);
            } catch (Throwable $e) {
                // Continuar con datos disponibles.
            }
        }

        $stmt = $this->pdo->prepare(" 
            SELECT * FROM billing_collections
            WHERE environment = ? AND direction = 'payable' AND inbox_document_id = ?
            LIMIT 1
        ");
        $stmt->execute(array($environment, $doc['inbox_document_id']));
        $existing = $stmt->fetch(PDO::FETCH_ASSOC);

        $dueDate = array_key_exists('due_date', $overrides) ? $overrides['due_date'] : $this->resolveCalculatedDueDate($doc);
        $financeCenterId = array_key_exists('finance_center_id', $overrides) ? $overrides['finance_center_id'] : ($doc['finance_center_id'] ?: (isset($doc['supplier_default_cost_center_id']) ? $doc['supplier_default_cost_center_id'] : null));
        $status = array_key_exists('collection_status', $overrides) ? $overrides['collection_status'] : ($doc['payment_status'] ?: 'por_pagar');
        $customerId = array_key_exists('customer_id', $overrides) ? $overrides['customer_id'] : ($doc['supplier_customer_id'] ?: null);
        $notes = array_key_exists('notes', $overrides) ? $overrides['notes'] : (isset($doc['collection_notes']) ? $doc['collection_notes'] : null);
        $paymentTerms = isset($doc['supplier_payment_term_label']) && $doc['supplier_payment_term_label'] ? $doc['supplier_payment_term_label'] : null;

        if ($status === 'por_pagar' && $dueDate && strtotime($dueDate) < strtotime(date('Y-m-d'))) {
            $status = 'vencido';
        }

        if ($existing) {
            $stmt = $this->pdo->prepare(" 
                UPDATE billing_collections
                SET facto_document_id = ?,
                    customer_id = COALESCE(?, customer_id),
                    document_number = ?,
                    document_type_id = ?,
                    issue_date = ?,
                    due_date = ?,
                    total_amount = ?,
                    balance_amount = GREATEST(? - paid_amount, 0),
                    collection_status = CASE
                        WHEN paid_amount >= ? THEN 'pagado'
                        WHEN paid_amount > 0 AND ? > paid_amount THEN 'parcial'
                        ELSE ?
                    END,
                    finance_center_id = ?,
                    payment_terms = COALESCE(?, payment_terms),
                    notes = COALESCE(?, notes),
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
            ");
            $stmt->execute(array(
                $doc['document_id'],
                $customerId,
                $doc['document_number'],
                $doc['document_type_id'],
                $doc['document_date'],
                $dueDate,
                $doc['total_amount'],
                $doc['total_amount'],
                $doc['total_amount'],
                $doc['total_amount'],
                $status,
                $financeCenterId,
                $paymentTerms,
                $notes,
                $existing['id']
            ));
            $this->recalculateCollection((int)$existing['id']);
            return $this->getCollectionById((int)$existing['id']);
        }

        $stmt = $this->pdo->prepare(" 
            INSERT INTO billing_collections
            (environment, direction, source_table, facto_document_id, inbox_document_id, customer_id, document_number, document_type_id, issue_date, due_date, total_amount, paid_amount, balance_amount, collection_status, payment_terms, finance_center_id, notes)
            VALUES (?, 'payable', 'facto_inbox_documents', ?, ?, ?, ?, ?, ?, ?, ?, 0, ?, ?, ?, ?, ?)
        ");
        $stmt->execute(array(
            $environment,
            $doc['document_id'],
            $doc['inbox_document_id'],
            $customerId,
            $doc['document_number'],
            $doc['document_type_id'],
            $doc['document_date'],
            $dueDate,
            $doc['total_amount'],
            $doc['total_amount'],
            $status,
            $paymentTerms,
            $financeCenterId,
            $notes
        ));

        $id = (int)$this->pdo->lastInsertId();
        return $this->getCollectionById($id);
    }

    private function refreshCalculatedPayableStatuses($environment = 'sandbox', $localDocumentId = null)
    {
        $environment = $this->normalizeEnvironment($environment);
        $today = date('Y-m-d');

        $where = "d.environment = ?";
        $params = array($environment);
        if ($localDocumentId !== null) {
            $where .= " AND d.id = ?";
            $params[] = (int)$localDocumentId;
        }

        $sql = "
            SELECT
                d.id,
                d.inbox_document_id,
                d.document_date,
                d.receive_date,
                d.total_amount,
                d.payment_status,
                c.id AS collection_id,
                c.paid_amount,
                c.collection_status,
                c.last_payment_date,
                pt.days AS term_days
            FROM facto_inbox_documents d
            LEFT JOIN crm_customers s ON s.id = d.supplier_customer_id
            LEFT JOIN billing_payment_terms pt ON pt.id = COALESCE(d.payment_terms_id, s.supplier_payment_terms_id)
            LEFT JOIN billing_collections c ON c.environment = d.environment
                AND c.direction = 'payable'
                AND c.inbox_document_id = d.inbox_document_id
            WHERE " . $where . "
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($rows as $row) {
            $currentStatus = $row['collection_status'] ?: $row['payment_status'];
            if (in_array($currentStatus, array('rechazado','anulado'), true)) {
                continue;
            }

            $dueDate = $this->calculateDueDateFromTermsRow($row);
            $paid = isset($row['paid_amount']) ? (float)$row['paid_amount'] : 0.0;
            $total = isset($row['total_amount']) ? (float)$row['total_amount'] : 0.0;
            $balance = max($total - $paid, 0);
            $status = $this->statusFromPaidBalanceDue($paid, $balance, $dueDate, $today);

            $stmtDoc = $this->pdo->prepare("
                UPDATE facto_inbox_documents
                SET payment_due_date = ?,
                    payment_status = ?,
                    payment_paid_date = CASE WHEN ? = 'pagado' THEN COALESCE(payment_paid_date, ?) ELSE payment_paid_date END,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
            ");
            $stmtDoc->execute(array($dueDate, $status, $status, $row['last_payment_date'], (int)$row['id']));

            if (!empty($row['collection_id'])) {
                $stmtCol = $this->pdo->prepare("
                    UPDATE billing_collections
                    SET due_date = ?,
                        balance_amount = ?,
                        collection_status = ?,
                        updated_at = CURRENT_TIMESTAMP
                    WHERE id = ?
                ");
                $stmtCol->execute(array($dueDate, $balance, $status, (int)$row['collection_id']));
            }
        }
    }

    private function calculateDueDateFromTermsRow(array $row)
    {
        $baseDate = $this->normalizeDate(isset($row['document_date']) ? $row['document_date'] : null);
        if (!$baseDate) {
            $baseDate = $this->normalizeDate(isset($row['receive_date']) ? $row['receive_date'] : null);
        }
        if (!$baseDate) {
            return date('Y-m-d', strtotime('-1 day'));
        }

        $days = 0;
        if (isset($row['term_days']) && $row['term_days'] !== null && $row['term_days'] !== '') {
            $days = (int)$row['term_days'];
        }
        $ts = strtotime($baseDate . ' +' . max(0, $days) . ' days');
        return $ts ? date('Y-m-d', $ts) : $baseDate;
    }

    private function statusFromPaidBalanceDue($paid, $balance, $dueDate, $today = null)
    {
        $today = $today ?: date('Y-m-d');
        $isOverdue = $dueDate && strtotime($dueDate) < strtotime($today);
        if ($paid > 0 && $balance <= 0.5) {
            return 'pagado';
        }
        if ($isOverdue) {
            return 'vencido';
        }
        if ($paid > 0) {
            return 'parcial';
        }
        return 'por_pagar';
    }

    public function refreshOverdue($environment = 'sandbox')
    {
        $environment = $this->normalizeEnvironment($environment);
        $today = date('Y-m-d');
        $stmt = $this->pdo->prepare(" 
            UPDATE billing_collections
            SET collection_status = 'vencido', updated_at = CURRENT_TIMESTAMP
            WHERE environment = ?
              AND direction = 'payable'
              AND collection_status IN ('por_pagar','parcial')
              AND due_date IS NOT NULL
              AND due_date < ?
              AND balance_amount > 0
        ");
        $stmt->execute(array($environment, $today));

        $stmt = $this->pdo->prepare(" 
            UPDATE facto_inbox_documents d
            JOIN billing_collections c ON c.environment = d.environment
                AND c.direction = 'payable'
                AND c.inbox_document_id = d.inbox_document_id
            SET d.payment_status = c.collection_status,
                d.payment_due_date = c.due_date,
                d.payment_paid_date = c.last_payment_date,
                d.updated_at = CURRENT_TIMESTAMP
            WHERE d.environment = ?
        ");
        $stmt->execute(array($environment));
    }

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

    public function filePathForDocument($idOrInboxId, $type, $environment = 'sandbox')
    {
        $doc = $this->getDocument($idOrInboxId, $environment);
        $path = $type === 'xml' ? $doc['xml_file_path'] : $doc['pdf_file_path'];
        if (!$path) {
            throw new Exception('Archivo no disponible para este documento.');
        }
        $absolute = dirname(__DIR__, 2) . '/' . ltrim($path, '/');
        $storageRoot = realpath(dirname(__DIR__, 2) . '/storage/billing');
        $real = realpath($absolute);
        if (!$real || !$storageRoot || strpos($real, $storageRoot) !== 0 || !is_file($real)) {
            throw new Exception('Ruta de archivo no autorizada o inexistente.');
        }
        return $real;
    }

    private function validatePaymentHeader($paymentDate, $amount, $methodId, $cashAccountId)
    {
        if (!$paymentDate) {
            throw new Exception('Fecha de pago requerida.');
        }
        if ((float)$amount <= 0) {
            throw new Exception('Monto de pago requerido.');
        }
        if (!$methodId) {
            throw new Exception('Metodo de pago requerido.');
        }
        if (!$cashAccountId) {
            throw new Exception('Caja/cuenta requerida.');
        }
    }

    private function createPaymentBatch($environment, $paymentDate, $methodId, $cashAccountId, $amount, $reference, $notes, $userId)
    {
        $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, created_by)
            VALUES (?, 'outgoing', ?, ?, ?, ?, 0, ?, ?, ?)
        ");
        $stmt->execute(array($environment, $paymentDate, $methodId, $cashAccountId, $amount, $reference, $notes, $userId));
        return (int)$this->pdo->lastInsertId();
    }

    private function insertPaymentForCollection(array $collection, array $doc, $environment, $paymentDate, $amount, array $method, $methodId, $cashAccountId, $reference, $notes, $userId, $batchId)
    {
        $stmt = $this->pdo->prepare(" 
            INSERT INTO billing_payments
            (payment_batch_id, collection_id, environment, direction, facto_document_id, inbox_document_id, customer_id, payment_date, amount, payment_method, payment_method_id, cash_account_id, external_reference, finance_center_id, notes, created_by)
            VALUES (?, ?, ?, 'paid', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        $stmt->execute(array(
            $batchId,
            $collection['id'],
            $environment,
            $doc['document_id'],
            $doc['inbox_document_id'],
            $doc['supplier_customer_id'],
            $paymentDate,
            $amount,
            $method['method_name'],
            $methodId,
            $cashAccountId,
            $reference,
            $doc['finance_center_id'] ?: (isset($doc['supplier_default_cost_center_id']) ? $doc['supplier_default_cost_center_id'] : null),
            $notes,
            $userId
        ));
        return (int)$this->pdo->lastInsertId();
    }

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

    private function updateBatchAllocated($batchId)
    {
        $stmt = $this->pdo->prepare(" 
            UPDATE billing_payment_batches b
            SET amount_allocated = (
                SELECT COALESCE(SUM(amount_applied), 0)
                FROM billing_payment_allocations a
                WHERE a.payment_batch_id = b.id
            )
            WHERE b.id = ?
        ");
        $stmt->execute(array($batchId));
    }

    private function recalculateCollection($collectionId)
    {
        $collection = $this->getCollectionById($collectionId);
        $stmt = $this->pdo->prepare(" 
            SELECT COALESCE(SUM(amount), 0) AS paid_amount, MAX(payment_date) AS last_payment_date
            FROM billing_payments
            WHERE collection_id = ? AND direction = 'paid'
        ");
        $stmt->execute(array($collectionId));
        $p = $stmt->fetch(PDO::FETCH_ASSOC) ?: array('paid_amount' => 0, 'last_payment_date' => null);
        $paid = (float)$p['paid_amount'];
        $total = (float)$collection['total_amount'];
        $balance = max($total - $paid, 0);
        $status = $collection['collection_status'];
        if ($paid <= 0) {
            $status = $collection['due_date'] && strtotime($collection['due_date']) < strtotime(date('Y-m-d')) ? 'vencido' : 'por_pagar';
        } elseif ($balance <= 0.5) {
            $status = 'pagado';
        } else {
            $status = $collection['due_date'] && strtotime($collection['due_date']) < strtotime(date('Y-m-d')) ? 'vencido' : 'parcial';
        }

        $stmt = $this->pdo->prepare(" 
            UPDATE billing_collections
            SET paid_amount = ?, balance_amount = ?, collection_status = ?, last_payment_date = ?, updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
        ");
        $stmt->execute(array($paid, $balance, $status, $p['last_payment_date'], $collectionId));

        $stmt = $this->pdo->prepare(" 
            UPDATE facto_inbox_documents
            SET payment_status = ?, payment_paid_date = ?, updated_at = CURRENT_TIMESTAMP
            WHERE environment = ? AND inbox_document_id = ?
        ");
        $stmt->execute(array($status, $p['last_payment_date'], $collection['environment'], $collection['inbox_document_id']));
    }

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


    private function resolveSupplierDefaults($environment, $limit = 200)
    {
        try {
            $resolver = new BillingSupplierResolverService($this->pdo);
            $resolver->resolveEnvironment($environment, $limit);
        } catch (Throwable $e) {
            // La bandeja de cuentas por pagar no debe fallar por una resolucion automatica de proveedor.
        }
    }

    private function resolveCalculatedDueDate(array $doc)
    {
        // La fecha de vencimiento se recalcula desde la condicion de pago del proveedor.
        // No se usa un valor manual previo como fuente principal para evitar estados obsoletos.
        $baseDate = $this->normalizeDate(isset($doc['document_date']) ? $doc['document_date'] : null);
        if (!$baseDate) {
            $baseDate = $this->normalizeDate(isset($doc['receive_date']) ? $doc['receive_date'] : null);
        }
        if (!$baseDate) {
            return date('Y-m-d', strtotime('-1 day'));
        }

        $days = 0;
        if (isset($doc['supplier_payment_term_days']) && $doc['supplier_payment_term_days'] !== null && $doc['supplier_payment_term_days'] !== '') {
            $days = (int)$doc['supplier_payment_term_days'];
        }

        $ts = strtotime($baseDate . ' +' . max(0, $days) . ' days');
        return $ts ? date('Y-m-d', $ts) : $baseDate;
    }

    private function calculateStatusFromAmounts(array $doc, $dueDate = null)
    {
        $paid = isset($doc['paid_amount']) ? (float)$doc['paid_amount'] : 0.0;
        $total = isset($doc['total_amount']) ? (float)$doc['total_amount'] : 0.0;
        $balance = max($total - $paid, 0);
        return $this->statusFromPaidBalanceDue($paid, $balance, $dueDate);
    }

    private function getDefaultCostCenterId()
    {
        if (!$this->tableExists('finance_centers')) {
            return null;
        }
        $stmt = $this->pdo->prepare("SELECT id FROM finance_centers WHERE center_code = 'NO_COST_CENTER' LIMIT 1");
        $stmt->execute();
        $id = $stmt->fetchColumn();
        return $id ? (int)$id : null;
    }

    private function tableExists($table)
    {
        $stmt = $this->pdo->prepare('SHOW TABLES LIKE ?');
        $stmt->execute(array($table));
        return (bool)$stmt->fetchColumn();
    }

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

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

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

    private function formatMoney($amount)
    {
        return '$' . number_format((float)$amount, 0, ',', '.');
    }
}
