<?php

require_once __DIR__ . '/../../core/database.php';
require_once __DIR__ . '/../facto/FactoClient.php';
require_once __DIR__ . '/../facto/FactoInboxDocumentsSyncService.php';

class BillingInboxReceptionService
{
    private $pdo;

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

    public function listDocuments(array $filters = array())
    {
        $environment = $this->normalizeEnvironment($filters['environment'] ?? 'sandbox');
        $tab = trim((string)($filters['tab'] ?? 'pending'));
        $status = trim((string)($filters['status'] ?? ''));
        $type = trim((string)($filters['document_type_id'] ?? ''));
        $rut = trim((string)($filters['rut'] ?? ''));
        $folio = trim((string)($filters['folio'] ?? ''));
        $po = trim((string)($filters['purchase_order'] ?? ''));
        $limit = isset($filters['limit']) ? max(1, min(100, (int)$filters['limit'])) : 80;

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

        if ($status !== '' && $status !== 'all') {
            $where[] = "COALESCE(d.icylab_reception_status, 'pending') = ?";
            $params[] = $status;
        } elseif ($tab === 'pending') {
            $where[] = "COALESCE(d.icylab_reception_status, 'pending') IN ('pending','error')";
        } elseif ($tab === 'soon') {
            $where[] = "COALESCE(d.icylab_reception_status, 'pending') = 'pending'";
            $where[] = 'DATEDIFF(DATE_ADD(COALESCE(d.receive_date, d.document_date), INTERVAL 8 DAY), CURDATE()) BETWEEN 0 AND 2';
        } elseif ($tab === 'without_po') {
            $where[] = "COALESCE(d.icylab_reception_status, 'pending') = 'pending'";
            $where[] = "(d.purchase_order IS NULL OR d.purchase_order = '')";
        } elseif ($tab === 'approved') {
            $where[] = "COALESCE(d.icylab_reception_status, 'pending') IN ('approved','approved_with_observations','merchandise_received')";
        } elseif ($tab === 'rejected') {
            $where[] = "COALESCE(d.icylab_reception_status, 'pending') = 'rejected'";
        }

        if ($type !== '' && $type !== 'all') {
            $where[] = 'd.document_type_id = ?';
            $params[] = (int)$type;
        }
        if ($rut !== '') {
            $where[] = 'd.issuer_tax_id_code LIKE ?';
            $params[] = '%' . $rut . '%';
        }
        if ($folio !== '') {
            $where[] = 'd.document_number LIKE ?';
            $params[] = '%' . $folio . '%';
        }
        if ($po !== '') {
            $where[] = 'd.purchase_order LIKE ?';
            $params[] = '%' . $po . '%';
        }

        $sql = "
            SELECT d.id, d.environment, d.inbox_document_id, d.document_id, d.document_date, d.receive_date,
                   d.inbox_document_status, d.issuer_name, d.issuer_tax_id_code, d.document_type_id,
                   d.document_type_taxbureau, d.document_number, d.purchase_order, d.total_amount,
                   d.net_amount, d.taxes_amount, d.pdf_file_path, d.xml_file_path,
                   d.reception_confirmation_status, d.document_approval_status, d.product_receipt_status,
                   d.payment_due_date, d.icylab_reception_status, d.icylab_reception_decision_id,
                   fc.center_name AS finance_center_name,
                   s.customer_name AS supplier_name
            FROM facto_inbox_documents d
            LEFT JOIN finance_centers fc ON fc.id = d.finance_center_id
            LEFT JOIN crm_customers s ON s.id = d.supplier_customer_id
            WHERE " . implode(' AND ', $where) . "
            ORDER BY COALESCE(d.receive_date, d.document_date) DESC, d.id DESC
            LIMIT " . (int)$limit;
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);

        return array(
            'tabs' => $this->getTabs($environment),
            'rows' => $stmt->fetchAll(PDO::FETCH_ASSOC),
            'options' => $this->getOptions($environment)
        );
    }

    public function getTabs($environment = 'sandbox')
    {
        $environment = $this->normalizeEnvironment($environment);
        $stmt = $this->pdo->prepare("
            SELECT
              SUM(CASE WHEN COALESCE(icylab_reception_status, 'pending') IN ('pending','error') THEN 1 ELSE 0 END) AS pending_count,
              SUM(CASE WHEN COALESCE(icylab_reception_status, 'pending') = 'pending'
                        AND DATEDIFF(DATE_ADD(COALESCE(receive_date, document_date), INTERVAL 8 DAY), CURDATE()) BETWEEN 0 AND 2 THEN 1 ELSE 0 END) AS soon_count,
              SUM(CASE WHEN COALESCE(icylab_reception_status, 'pending') = 'pending'
                        AND (purchase_order IS NULL OR purchase_order = '') THEN 1 ELSE 0 END) AS without_po_count,
              SUM(CASE WHEN COALESCE(icylab_reception_status, 'pending') IN ('approved','approved_with_observations','merchandise_received') THEN 1 ELSE 0 END) AS approved_count,
              SUM(CASE WHEN COALESCE(icylab_reception_status, 'pending') = 'rejected' THEN 1 ELSE 0 END) AS rejected_count
            FROM facto_inbox_documents
            WHERE environment = ?
        ");
        $stmt->execute(array($environment));
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: array();
        foreach (array('pending_count','soon_count','without_po_count','approved_count','rejected_count') as $k) {
            $row[$k] = (int)($row[$k] ?? 0);
        }
        return $row;
    }

    public function getDocument($idOrInboxId, $environment = 'sandbox')
    {
        $environment = $this->normalizeEnvironment($environment);
        $stmt = $this->pdo->prepare("
            SELECT d.*, fc.center_name AS finance_center_name, s.customer_name AS supplier_name, s.customer_tax_id AS supplier_tax_id
            FROM facto_inbox_documents d
            LEFT JOIN finance_centers fc ON fc.id = d.finance_center_id
            LEFT JOIN crm_customers s ON s.id = d.supplier_customer_id
            WHERE d.environment = ? AND (d.id = ? OR d.inbox_document_id = ?)
            LIMIT 1
        ");
        $stmt->execute(array($environment, (string)$idOrInboxId, (string)$idOrInboxId));
        $doc = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$doc) {
            throw new Exception('Documento recibido no encontrado.');
        }

        $doc['lines'] = $this->extractLines($doc);
        $doc['latest_decision'] = $this->getLatestDecision($doc['inbox_document_id'], $environment);
        return $doc;
    }

    public function getOptions($environment = 'sandbox')
    {
        return array(
            'environment' => $this->normalizeEnvironment($environment),
            'finance_centers' => $this->listFinanceCenters(),
            'products' => $this->listProducts(),
            'document_types' => $this->listDocumentTypes($environment),
            'statuses' => array(
                array('value' => 'pending', 'label' => 'Pendiente'),
                array('value' => 'approved', 'label' => 'Aprobado'),
                array('value' => 'approved_with_observations', 'label' => 'Aprobado con reparo'),
                array('value' => 'rejected', 'label' => 'Rechazado'),
                array('value' => 'merchandise_received', 'label' => 'Mercaderia/servicio recibido'),
                array('value' => 'error', 'label' => 'Error')
            )
        );
    }


    public function bulkDecide(array $ids, array $data, $userId = null)
    {
        $environment = $this->normalizeEnvironment($data['environment'] ?? 'sandbox');
        $decisionType = trim((string)($data['decision_type'] ?? ''));
        if (!in_array($decisionType, array('approved','approved_with_observations','rejected','merchandise_received'), true)) {
            throw new Exception('Decision DTE masiva no valida.');
        }

        $ids = array_values(array_unique(array_filter(array_map(function ($id) {
            return trim((string)$id);
        }, $ids), function ($id) {
            return $id !== '';
        })));

        if (empty($ids)) {
            throw new Exception('Debes seleccionar al menos un documento.');
        }
        if (count($ids) > 50) {
            throw new Exception('La recepcion masiva permite hasta 50 documentos por operacion.');
        }

        $receptionConfirmed = !empty($data['reception_confirmed']) ? 1 : 0;
        $receptionPlace = trim((string)($data['reception_place'] ?? ''));
        $contactEmail = trim((string)($data['contact_email'] ?? ''));
        $notes = trim((string)($data['notes'] ?? ''));

        if ($decisionType !== 'rejected' && !$receptionConfirmed) {
            throw new Exception('Para aprobar en forma masiva debes confirmar recepcion de mercaderia o servicio.');
        }
        if ($receptionConfirmed && $receptionPlace === '') {
            throw new Exception('Debes indicar lugar de recepcion.');
        }
        if ($decisionType === 'rejected' && $notes === '') {
            throw new Exception('Para rechazar masivamente debes registrar motivo u observacion.');
        }

        $batchId = null;
        try {
            $stmt = $this->pdo->prepare("\n                INSERT INTO billing_inbox_bulk_decision_batches\n                (environment, decision_type, reception_confirmed, reception_place, contact_email, notes,\n                 selected_count, processed_count, success_count, error_count, ids_json, created_by, created_at)\n                VALUES (?, ?, ?, ?, ?, ?, ?, 0, 0, 0, ?, ?, NOW())\n            ");
            $stmt->execute(array(
                $environment,
                $decisionType,
                $receptionConfirmed,
                $receptionPlace !== '' ? $receptionPlace : null,
                $contactEmail !== '' ? $contactEmail : null,
                $notes !== '' ? $notes : null,
                count($ids),
                json_encode($ids, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                $userId
            ));
            $batchId = (int)$this->pdo->lastInsertId();
        } catch (Throwable $e) {
            $batchId = null;
        }

        $results = array();
        $success = 0;
        $error = 0;

        foreach ($ids as $id) {
            $rowResult = array('id' => $id, 'success' => false, 'message' => 'No procesado.');
            try {
                $doc = $this->getDocument($id, $environment);
                $rowResult['issuer_name'] = $doc['issuer_name'] ?? null;
                $rowResult['document_number'] = $doc['document_number'] ?? null;
                $rowResult['inbox_document_id'] = $doc['inbox_document_id'] ?? null;

                $currentStatus = $doc['icylab_reception_status'] ?? 'pending';
                if (!in_array($currentStatus, array('', 'pending', 'error'), true)) {
                    throw new Exception('Documento no pendiente; estado actual: ' . $currentStatus . '.');
                }

                if ($this->requiresFactoPendingStatus($decisionType)) {
                    $doc = $this->refreshBeforeExternalDecision($doc, $environment);
                    if (!$this->isFactoPending($doc)) {
                        throw new Exception('Omitido: FACTO ya no tiene este DTE pendiente. Estado actual: ' . $this->describeFactoInboxStatus($doc['inbox_document_status'] ?? null) . '.');
                    }
                }

                $singleNotes = $notes;
                if ($batchId) {
                    $singleNotes = trim($singleNotes . ($singleNotes !== '' ? "\n" : '') . 'Recepcion masiva ICYLAB batch #' . $batchId);
                }

                $result = $this->decide($doc['id'], array(
                    'environment' => $environment,
                    'decision_type' => $decisionType,
                    'reception_confirmed' => $receptionConfirmed,
                    'reception_place' => $receptionPlace,
                    'contact_email' => $contactEmail,
                    'notes' => $singleNotes,
                    'lines' => array()
                ), $userId);

                if (!empty($result['success'])) {
                    $success++;
                    $rowResult['success'] = true;
                    $rowResult['decision_id'] = $result['decision_id'] ?? null;
                    $rowResult['message'] = $result['message'] ?? 'Procesado correctamente.';
                } else {
                    $error++;
                    $rowResult['message'] = $result['message'] ?? 'FACTO no confirmo el procesamiento.';
                    $rowResult['decision_id'] = $result['decision_id'] ?? null;
                }
            } catch (Throwable $e) {
                $error++;
                $rowResult['message'] = $e->getMessage();
            }
            $results[] = $rowResult;
        }

        $processed = count($results);
        if ($batchId) {
            try {
                $status = $error === 0 ? 'success' : ($success > 0 ? 'partial' : 'error');
                $stmt = $this->pdo->prepare("\n                    UPDATE billing_inbox_bulk_decision_batches\n                    SET processed_count = ?, success_count = ?, error_count = ?, batch_status = ?, result_json = ?, updated_at = CURRENT_TIMESTAMP\n                    WHERE id = ?\n                ");
                $stmt->execute(array(
                    $processed,
                    $success,
                    $error,
                    $status,
                    json_encode($results, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                    $batchId
                ));
            } catch (Throwable $e) {
                // La recepcion individual ya quedo registrada; no se aborta por error de auditoria batch.
            }
        }

        return array(
            'success' => true,
            'completed_without_errors' => $error === 0,
            'batch_id' => $batchId,
            'message' => $error === 0 ? 'Recepcion masiva completada correctamente.' : 'Recepcion masiva finalizada con observaciones.',
            'summary' => array(
                'batch_id' => $batchId,
                'environment' => $environment,
                'decision_type' => $decisionType,
                'selected' => count($ids),
                'processed' => $processed,
                'success' => $success,
                'error' => $error
            ),
            'results' => $results
        );
    }

    public function decide($idOrInboxId, array $data, $userId = null)
    {
        $environment = $this->normalizeEnvironment($data['environment'] ?? 'sandbox');
        $doc = $this->getDocument($idOrInboxId, $environment);
        $decisionType = trim((string)($data['decision_type'] ?? ''));
        if (!in_array($decisionType, array('approved','approved_with_observations','rejected','merchandise_received'), true)) {
            throw new Exception('Decision DTE no valida.');
        }

        $receptionConfirmed = !empty($data['reception_confirmed']) ? 1 : 0;
        $receptionPlace = trim((string)($data['reception_place'] ?? ''));
        $contactEmail = trim((string)($data['contact_email'] ?? ''));
        $notes = trim((string)($data['notes'] ?? ''));
        $lines = isset($data['lines']) && is_array($data['lines']) ? $data['lines'] : array();

        if ($decisionType !== 'rejected' && !$receptionConfirmed) {
            throw new Exception('Para aprobar el DTE debes confirmar recepcion de mercaderia o servicio.');
        }
        if ($receptionConfirmed && $receptionPlace === '') {
            throw new Exception('Debes indicar lugar de recepcion.');
        }
        if ($decisionType === 'rejected' && $notes === '') {
            throw new Exception('Para rechazar debes registrar el motivo u observacion.');
        }

        // FACTO solo permite aprobar, aprobar con reparo o rechazar cuando el documento
        // aun esta en estado 1 (pendiente). Si el estado externo ya cambio, no se debe
        // intentar PATCH porque FACTO responde HTTP 400 y el usuario termina en una
        // pantalla de error. Refrescamos el documento antes de decidir para evitar usar
        // datos locales obsoletos.
        if ($this->requiresFactoPendingStatus($decisionType)) {
            $doc = $this->refreshBeforeExternalDecision($doc, $environment);
            if (!$this->isFactoPending($doc)) {
                return array(
                    'success' => false,
                    'blocked' => true,
                    'status_conflict' => true,
                    'message' => 'FACTO no permite aprobar o rechazar este DTE porque ya no esta pendiente. Estado FACTO actual: ' . $this->describeFactoInboxStatus($doc['inbox_document_status'] ?? null) . '. No se envio una nueva decision. Actualiza el listado o revisa el documento en FACTO.',
                    'document' => $doc
                );
            }
        }

        $this->pdo->beginTransaction();
        $decisionId = null;
        try {
            $stmt = $this->pdo->prepare("
                INSERT INTO billing_inbox_decisions
                (environment, inbox_document_id, facto_inbox_row_id, decision_type, reception_confirmed,
                 reception_place, contact_email, notes, facto_status_before, decided_by, decided_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
            ");
            $stmt->execute(array(
                $environment,
                $doc['inbox_document_id'],
                $doc['id'],
                $decisionType,
                $receptionConfirmed,
                $receptionPlace !== '' ? $receptionPlace : null,
                $contactEmail !== '' ? $contactEmail : null,
                $notes !== '' ? $notes : null,
                $doc['inbox_document_status'] ?? null,
                $userId
            ));
            $decisionId = (int)$this->pdo->lastInsertId();

            $this->saveDecisionLines($decisionId, $doc, $lines);
            $this->pdo->commit();
        } catch (Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }

        $factoResponse = null;
        $factoError = null;
        $statusAfter = null;
        try {
            $actionCode = $this->mapDecisionToFactoStatus($decisionType, $receptionConfirmed);
            if ($actionCode !== null) {
                $client = new FactoClient($environment);
                $payload = array('inbox_document_status' => $actionCode);
                if ($notes !== '') {
                    $payload['observation'] = $notes;
                    $payload['comments'] = $notes;
                }
                if ($receptionConfirmed) {
                    $payload['product_receipt_location'] = $receptionPlace;
                    $payload['product_receipt_email'] = $contactEmail;
                }
                $factoResponse = $client->patchInboxDocument($doc['inbox_document_id'], $payload);

                $sync = new FactoInboxDocumentsSyncService($environment);
                $sync->refreshInboxDocument($doc['inbox_document_id'], true);
                $fresh = $this->getDocument($doc['inbox_document_id'], $environment);
                $statusAfter = $fresh['inbox_document_status'] ?? null;
            }
        } catch (Throwable $e) {
            $factoError = $e->getMessage();
            if ($this->isFactoStatusConflictError($factoError)) {
                $doc = $this->refreshBeforeExternalDecision($doc, $environment);
                $factoError = 'FACTO no permite aprobar o rechazar este DTE porque ya no esta pendiente. Estado FACTO actual: ' . $this->describeFactoInboxStatus($doc['inbox_document_status'] ?? null) . '. No se envio una nueva decision.';
            }
        }

        $internalStatus = $decisionType;
        if ($factoError !== null) {
            $internalStatus = 'error';
        }

        $this->pdo->beginTransaction();
        try {
            $stmt = $this->pdo->prepare("
                UPDATE billing_inbox_decisions
                SET facto_status_after = ?,
                    sii_reported = ?,
                    provider_reported = ?,
                    purchase_book_registered = ?,
                    facto_response_json = ?,
                    error_message = ?
                WHERE id = ?
            ");
            $stmt->execute(array(
                $statusAfter,
                $factoError === null && $decisionType !== 'merchandise_received' ? 1 : 0,
                $factoError === null && $decisionType !== 'merchandise_received' ? 1 : 0,
                $factoError === null && $decisionType !== 'rejected' ? 1 : 0,
                $factoResponse ? json_encode($factoResponse, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null,
                $factoError,
                $decisionId
            ));

            $stmt = $this->pdo->prepare("
                UPDATE facto_inbox_documents
                SET icylab_reception_status = ?,
                    icylab_reception_decision_id = ?,
                    icylab_reception_notes = ?,
                    product_receipt_status = CASE WHEN ? = 1 THEN COALESCE(product_receipt_status, 'received') ELSE product_receipt_status END,
                    product_receipt_location = CASE WHEN ? = 1 THEN ? ELSE product_receipt_location END,
                    product_receipt_date = CASE WHEN ? = 1 THEN COALESCE(product_receipt_date, NOW()) ELSE product_receipt_date END,
                    payment_status = CASE WHEN ? = 'rejected' THEN 'rechazado' ELSE payment_status END,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
            ");
            $stmt->execute(array(
                $internalStatus,
                $decisionId,
                $notes !== '' ? $notes : null,
                $receptionConfirmed,
                $receptionConfirmed,
                $receptionPlace !== '' ? $receptionPlace : null,
                $receptionConfirmed,
                $decisionType,
                $doc['id']
            ));

            if ($factoError === null && $decisionType !== 'rejected') {
                $this->ensurePayableCollection($doc);
            }
            $this->pdo->commit();
        } catch (Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }

        if ($factoError !== null) {
            return array(
                'success' => false,
                'decision_id' => $decisionId,
                'message' => $factoError,
                'document' => $this->getDocument($doc['id'], $environment)
            );
        }

        return array(
            'success' => true,
            'decision_id' => $decisionId,
            'message' => 'Documento procesado correctamente.',
            'facto_response' => $factoResponse,
            'document' => $this->getDocument($doc['id'], $environment)
        );
    }

    private function saveDecisionLines($decisionId, array $doc, array $lines)
    {
        if (empty($lines)) {
            $lines = $doc['lines'] ?? array();
        }
        $stmt = $this->pdo->prepare("
            INSERT INTO billing_inbox_received_lines
            (decision_id, environment, inbox_document_id, line_index, description, quantity_invoiced,
             quantity_received, unit, warehouse_destination, matched_product_id, matched_sku, free_sku, notes)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        $i = 0;
        foreach ($lines as $line) {
            $qtyInvoiced = $this->toDecimal($line['quantity_invoiced'] ?? $line['quantity'] ?? 0);
            $qtyReceived = $this->toDecimal($line['quantity_received'] ?? $qtyInvoiced);
            $productId = isset($line['matched_product_id']) && $line['matched_product_id'] !== '' ? (int)$line['matched_product_id'] : null;
            $stmt->execute(array(
                $decisionId,
                $doc['environment'],
                $doc['inbox_document_id'],
                isset($line['line_index']) ? (int)$line['line_index'] : $i,
                trim((string)($line['description'] ?? $line['name'] ?? '')),
                $qtyInvoiced,
                $qtyReceived,
                trim((string)($line['unit'] ?? '')) ?: null,
                trim((string)($line['warehouse_destination'] ?? '')) ?: null,
                $productId,
                trim((string)($line['matched_sku'] ?? '')) ?: null,
                trim((string)($line['free_sku'] ?? '')) ?: null,
                trim((string)($line['notes'] ?? '')) ?: null
            ));
            $i++;
        }
    }

    private function requiresFactoPendingStatus($decisionType)
    {
        return in_array($decisionType, array('approved', 'approved_with_observations', 'rejected'), true);
    }

    private function isFactoPending(array $doc)
    {
        return trim((string)($doc['inbox_document_status'] ?? '')) === '1';
    }

    private function refreshBeforeExternalDecision(array $doc, $environment)
    {
        if (empty($doc['inbox_document_id'])) {
            return $doc;
        }
        try {
            $sync = new FactoInboxDocumentsSyncService($environment);
            $sync->refreshInboxDocument($doc['inbox_document_id'], false);
            return $this->getDocument($doc['inbox_document_id'], $environment);
        } catch (Throwable $e) {
            // Si FACTO no permite refrescar en ese momento, mantenemos el registro local.
            // La validacion local o el PATCH posterior resolveran el estado.
            return $doc;
        }
    }

    private function isFactoStatusConflictError($message)
    {
        $message = strtolower((string)$message);
        return strpos($message, 'current status must be 1') !== false
            || strpos($message, 'inbox_document_status 2, 3 or 4') !== false
            || strpos($message, 'inbox_document_status 2') !== false
            || strpos($message, 'inbox_document_status 3') !== false
            || strpos($message, 'inbox_document_status 4') !== false;
    }

    private function describeFactoInboxStatus($status)
    {
        $status = trim((string)$status);
        $map = array(
            '' => 'no informado',
            '1' => '1 - pendiente',
            '2' => '2 - aprobado',
            '3' => '3 - aprobado con reparo',
            '4' => '4 - rechazado',
            '5' => '5 - recepcion de mercaderia/servicio registrada'
        );
        return $map[$status] ?? ($status . ' - estado no mapeado');
    }

    private function mapDecisionToFactoStatus($decisionType, $receptionConfirmed)
    {
        if ($decisionType === 'approved') return '2';
        if ($decisionType === 'approved_with_observations') return '3';
        if ($decisionType === 'rejected') return '4';
        if ($decisionType === 'merchandise_received' || $receptionConfirmed) return '5';
        return null;
    }

    private function ensurePayableCollection(array $doc)
    {
        $stmt = $this->pdo->prepare("SELECT id FROM billing_collections WHERE direction = 'payable' AND environment = ? AND inbox_document_id = ? LIMIT 1");
        $stmt->execute(array($doc['environment'], $doc['inbox_document_id']));
        $existingId = $stmt->fetchColumn();
        $dueDate = $doc['payment_due_date'] ?: $this->defaultDueDate($doc['document_date'] ?? null);
        if ($existingId) {
            $upd = $this->pdo->prepare("
                UPDATE billing_collections
                SET source_table = 'facto_inbox_documents',
                    customer_id = ?, document_number = ?, document_type_id = ?, issue_date = ?, due_date = ?,
                    total_amount = ?, balance_amount = GREATEST(total_amount - paid_amount, 0),
                    collection_status = CASE WHEN collection_status IN ('pagado','parcial') THEN collection_status ELSE 'por_pagar' END,
                    finance_center_id = ?, updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
            ");
            $upd->execute(array(
                $doc['supplier_customer_id'] ?: null,
                $doc['document_number'] ?: null,
                $doc['document_type_id'] ?: null,
                $doc['document_date'] ?: null,
                $dueDate,
                $this->toDecimal($doc['total_amount'] ?? 0),
                $doc['finance_center_id'] ?: null,
                $existingId
            ));
            return;
        }
        $ins = $this->pdo->prepare("
            INSERT INTO billing_collections
            (environment, direction, source_table, inbox_document_id, customer_id, document_number, document_type_id,
             issue_date, due_date, total_amount, paid_amount, balance_amount, collection_status, finance_center_id, notes)
            VALUES (?, 'payable', 'facto_inbox_documents', ?, ?, ?, ?, ?, ?, ?, 0, ?, 'por_pagar', ?, ?)
        ");
        $total = $this->toDecimal($doc['total_amount'] ?? 0);
        $ins->execute(array(
            $doc['environment'],
            $doc['inbox_document_id'],
            $doc['supplier_customer_id'] ?: null,
            $doc['document_number'] ?: null,
            $doc['document_type_id'] ?: null,
            $doc['document_date'] ?: null,
            $dueDate,
            $total,
            $total,
            $doc['finance_center_id'] ?: null,
            'Cuenta por pagar creada desde recepcion DTE.'
        ));
    }

    private function getLatestDecision($inboxId, $environment)
    {
        $stmt = $this->pdo->prepare("SELECT * FROM billing_inbox_decisions WHERE environment = ? AND inbox_document_id = ? ORDER BY id DESC LIMIT 1");
        $stmt->execute(array($environment, $inboxId));
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    private function extractLines(array $doc)
    {
        $payload = json_decode($doc['raw_payload'] ?? '', true);
        if (!is_array($payload)) return array();
        $candidates = array(
            $payload['details'] ?? null,
            $payload['detail'] ?? null,
            $payload['items'] ?? null,
            $payload['lines'] ?? null,
            $payload['document_details'] ?? null,
            $payload['electronic_document']['details'] ?? null,
            $payload['electronic_document']['items'] ?? null
        );
        foreach ($candidates as $candidate) {
            if (is_array($candidate) && !empty($candidate)) {
                $out = array();
                $i = 0;
                foreach ($candidate as $line) {
                    if (!is_array($line)) continue;
                    $desc = $line['description'] ?? $line['name'] ?? $line['product_name'] ?? $line['glosa'] ?? $line['item_description'] ?? '';
                    $qty = $line['quantity'] ?? $line['qty'] ?? $line['amount'] ?? $line['count'] ?? 0;
                    $unit = $line['unit'] ?? $line['measure'] ?? $line['unit_name'] ?? '';
                    $out[] = array(
                        'line_index' => $i,
                        'description' => $desc,
                        'quantity_invoiced' => $this->toDecimal($qty),
                        'quantity_received' => $this->toDecimal($qty),
                        'unit' => $unit,
                        'warehouse_destination' => '',
                        'matched_product_id' => null,
                        'matched_sku' => '',
                        'free_sku' => '',
                        'notes' => ''
                    );
                    $i++;
                }
                if (!empty($out)) return $out;
            }
        }
        return array(array(
            'line_index' => 0,
            'description' => 'Detalle no disponible en payload sincronizado',
            'quantity_invoiced' => 1,
            'quantity_received' => 1,
            'unit' => 'unidad',
            'warehouse_destination' => '',
            'matched_product_id' => null,
            'matched_sku' => '',
            'free_sku' => '',
            'notes' => ''
        ));
    }

    private function listFinanceCenters()
    {
        try {
            $stmt = $this->pdo->query("SELECT id, center_name, center_type FROM finance_centers ORDER BY center_type, center_name");
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Throwable $e) { return array(); }
    }

    private function listProducts()
    {
        try {
            $stmt = $this->pdo->query("SELECT id, sku, name FROM products WHERE COALESCE(is_active,1) = 1 ORDER BY name LIMIT 300");
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Throwable $e) {
            try {
                $stmt = $this->pdo->query("SELECT id, sku, name FROM products ORDER BY name LIMIT 300");
                return $stmt->fetchAll(PDO::FETCH_ASSOC);
            } catch (Throwable $e2) { return array(); }
        }
    }

    private function listDocumentTypes($environment)
    {
        $stmt = $this->pdo->prepare("SELECT DISTINCT document_type_id, document_type_taxbureau FROM facto_inbox_documents WHERE environment = ? ORDER BY document_type_taxbureau, document_type_id");
        $stmt->execute(array($environment));
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function defaultDueDate($date)
    {
        $ts = $date ? strtotime($date . ' +30 days') : strtotime('+30 days');
        return date('Y-m-d', $ts ?: time());
    }

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

    private function toDecimal($value)
    {
        if ($value === null || $value === '') return 0.0;
        if (is_string($value)) {
            $value = str_replace(array('$', '.', ','), array('', '', '.'), $value);
        }
        return (float)$value;
    }
}
