<?php

require_once __DIR__ . '/../../core/database.php';
require_once __DIR__ . '/BillingHelpers.php';
require_once __DIR__ . '/BillingRuntimeGate.php';

class BillingIssuedDocumentsService
{
    private $pdo;

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

    public function listDocuments(array $filters = array())
    {
        $environment = $this->normalizeEnvironment($filters['environment'] ?? 'sandbox');
        billing_assert_runtime_operation('read', $environment, 'Consulta de documentos tributarios');
        $search = trim((string)($filters['search'] ?? ''));
        $family = trim((string)($filters['family'] ?? 'all'));
        $origin = strtolower(trim((string)($filters['origin'] ?? 'all')));
        if (!in_array($origin, array('all', 'issued', 'received'), true)) {
            $origin = 'all';
        }
        $from = $this->normalizeDate($filters['from'] ?? null);
        $to = $this->normalizeDate($filters['to'] ?? null);

        $where = array('fd.environment = ?');
        $params = array($environment);
        if ($search !== '') {
            $where[] = '(fd.document_number LIKE ? OR fd.receiver_legal_name LIKE ? OR fd.receiver_tax_id_code LIKE ? OR q.quote_number LIKE ?)';
            $needle = '%' . $search . '%';
            array_push($params, $needle, $needle, $needle, $needle);
        }
        $familyMap = array(
            'invoice' => '(2,32)',
            'credit_note' => '(16,48)',
            'debit_note' => '(17,47)',
            'dispatch_guide' => '(54)',
            'export' => '(46)',
            'receipt' => '(37,41)'
        );
        if (isset($familyMap[$family])) {
            $where[] = 'CAST(fd.document_type_id AS UNSIGNED) IN ' . $familyMap[$family];
        } elseif ($family === 'other') {
            $where[] = "COALESCE(bdt.document_family, 'other') = 'other'";
        }
        if ($from) { $where[] = 'fd.issue_date >= ?'; $params[] = $from; }
        if ($to) { $where[] = 'fd.issue_date <= ?'; $params[] = $to; }

        $sql = "
            SELECT fd.*,
                   CASE WHEN CAST(fd.document_type_id AS UNSIGNED)=54 THEN 'Guía de despacho electrónica'
                        ELSE COALESCE(bdt.document_label, CONCAT('DTE ', COALESCE(fd.document_type_taxbureau,fd.document_type_id))) END AS document_label,
                   CASE WHEN CAST(fd.document_type_id AS UNSIGNED)=54 THEN 'dispatch_guide'
                        ELSE COALESCE(bdt.document_family,'other') END AS document_family,
                   CASE WHEN CAST(fd.document_type_id AS UNSIGNED)=54 THEN 'dispatch_guide_electronic'
                        ELSE bdt.document_key END AS document_key,
                   c.customer_name, c.legal_name AS customer_legal_name, q.quote_number,
                   bc.id AS collection_id, bc.total_amount AS collection_total, bc.paid_amount, bc.balance_amount,
                   bc.collection_status, bc.currency_code AS collection_currency_code, bc.exchange_rate_value AS collection_exchange_rate_value,
                   bc.exchange_rate_date AS collection_exchange_rate_date, bc.exchange_rate_source AS collection_exchange_rate_source, bc.exchange_rate_status AS collection_exchange_rate_status,
                   bc.foreign_total_amount, bc.foreign_paid_amount, bc.foreign_balance_amount,
                   (SELECT COUNT(*) FROM billing_document_references br
                    WHERE BINARY br.environment=BINARY fd.environment
                      AND br.reference_facto_document_id=fd.document_id) AS adjustment_count
            FROM facto_documents fd
            LEFT JOIN billing_document_types bdt
              ON bdt.direction='issued' AND bdt.facto_document_type_id=CAST(fd.document_type_id AS UNSIGNED)
            LEFT JOIN crm_customers c ON c.id=fd.crm_customer_id
            LEFT JOIN crm_quotes q ON q.id=fd.crm_quote_id
            LEFT JOIN billing_collections bc ON CAST(fd.document_type_id AS UNSIGNED)<>54 AND bc.id=(
                SELECT MAX(bc2.id) FROM billing_collections bc2
                WHERE BINARY bc2.environment=BINARY fd.environment
                  AND bc2.direction='receivable' AND bc2.facto_document_id=fd.document_id
            )
            WHERE " . implode(' AND ', $where) . "
            ORDER BY fd.issue_date DESC, fd.id DESC LIMIT 1000
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $origin === 'received' ? array() : $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach ($rows as &$row) {
            $row['document_origin'] = 'issued';
            $row['counterparty_name'] = $row['customer_legal_name'] ?: ($row['customer_name'] ?: $row['receiver_legal_name']);
            $row['counterparty_tax_id'] = $row['receiver_tax_id_code'];
            $row['is_tax_exempt'] = in_array((int)$row['document_type_id'], array(32, 41), true)
                || in_array((string)$row['document_type_taxbureau'], array('34', '41', '111'), true) ? 1 : 0;
            $row['is_financial_document'] = in_array((int)$row['document_type_id'], array(2, 32, 46), true) ? 1 : 0;
        }
        unset($row);

        if ($origin !== 'issued') {
            $receivedFamilySql = "CASE
                WHEN bdt.document_family IS NOT NULL THEN bdt.document_family
                WHEN CAST(COALESCE(d.document_type_taxbureau,'0') AS UNSIGNED) IN (33,34) THEN 'invoice'
                WHEN CAST(COALESCE(d.document_type_taxbureau,'0') AS UNSIGNED)=61 THEN 'credit_note'
                WHEN CAST(COALESCE(d.document_type_taxbureau,'0') AS UNSIGNED)=56 THEN 'debit_note'
                WHEN CAST(COALESCE(d.document_type_taxbureau,'0') AS UNSIGNED)=52 THEN 'dispatch_guide'
                WHEN CAST(COALESCE(d.document_type_taxbureau,'0') AS UNSIGNED) IN (39,41) THEN 'receipt'
                WHEN CAST(COALESCE(d.document_type_taxbureau,'0') AS UNSIGNED) IN (110,111,112) THEN 'export'
                ELSE 'other' END";
            $receivedWhere = array('d.environment = ?');
            $receivedParams = array($environment);
            if ($search !== '') {
                $receivedWhere[] = '(d.document_number LIKE ? OR d.issuer_name LIKE ? OR d.issuer_tax_id_code LIKE ? OR d.purchase_order LIKE ?)';
                $needle = '%' . $search . '%';
                array_push($receivedParams, $needle, $needle, $needle, $needle);
            }
            if ($family !== '' && $family !== 'all') {
                $receivedWhere[] = '(' . $receivedFamilySql . ') = ?';
                $receivedParams[] = $family;
            }
            if ($from) { $receivedWhere[] = 'd.document_date >= ?'; $receivedParams[] = $from; }
            if ($to) { $receivedWhere[] = 'd.document_date <= ?'; $receivedParams[] = $to; }

            $receivedSql = "
                SELECT d.*,
                       'received' AS document_origin,
                       d.document_date AS issue_date,
                       COALESCE(d.document_id,d.inbox_document_id) AS source_document_id,
                       COALESCE(s.customer_name,d.issuer_name) AS counterparty_name,
                       COALESCE(s.customer_tax_id,d.issuer_tax_id_code) AS counterparty_tax_id,
                       COALESCE(
                           bdt.document_label,
                           CASE CAST(COALESCE(d.document_type_taxbureau,'0') AS UNSIGNED)
                               WHEN 33 THEN 'Factura electrónica recibida'
                               WHEN 34 THEN 'Factura no afecta o exenta electrónica recibida'
                               WHEN 61 THEN 'Nota de crédito electrónica recibida'
                               WHEN 56 THEN 'Nota de débito electrónica recibida'
                               WHEN 52 THEN 'Guía de despacho electrónica recibida'
                               ELSE CONCAT('DTE recibido ',COALESCE(d.document_type_taxbureau,d.document_type_id))
                           END
                       ) AS document_label,
                       " . $receivedFamilySql . " AS document_family,
                       bdt.document_key,
                       CASE WHEN CAST(COALESCE(d.document_type_id,0) AS UNSIGNED)=33
                                  OR CAST(COALESCE(d.document_type_taxbureau,'0') AS UNSIGNED) IN (34,41,111)
                            THEN 1 ELSE 0 END AS is_tax_exempt,
                       CASE WHEN CAST(COALESCE(d.document_type_id,0) AS UNSIGNED) IN (9,33)
                                  OR CAST(COALESCE(d.document_type_taxbureau,'0') AS UNSIGNED) IN (33,34)
                            THEN 1 ELSE 0 END AS is_financial_document,
                       s.customer_name AS supplier_name, s.customer_tax_id AS supplier_tax_id,
                       bc.id AS collection_id, bc.total_amount AS collection_total,
                       COALESCE(bc.paid_amount,0) AS paid_amount,
                       CASE WHEN bc.id IS NOT NULL THEN bc.balance_amount ELSE 0 END AS balance_amount,
                       bc.collection_status, bc.due_date,
                       NULL AS adjustment_count
                FROM facto_inbox_documents d
                LEFT JOIN billing_document_types bdt
                  ON bdt.direction='received' AND bdt.facto_document_type_id=CAST(d.document_type_id AS UNSIGNED)
                LEFT JOIN crm_customers s ON s.id=d.supplier_customer_id
                LEFT JOIN billing_collections bc
                  ON bc.environment=d.environment AND bc.direction='payable'
                 AND bc.inbox_document_id=d.inbox_document_id
                WHERE " . implode(' AND ', $receivedWhere) . "
                ORDER BY d.document_date DESC, d.id DESC LIMIT 1000
            ";
            $receivedStmt = $this->pdo->prepare($receivedSql);
            $receivedStmt->execute($receivedParams);
            $rows = array_merge($rows, $receivedStmt->fetchAll(PDO::FETCH_ASSOC));
        }

        usort($rows, function ($a, $b) {
            $dateComparison = strcmp((string)($b['issue_date'] ?? ''), (string)($a['issue_date'] ?? ''));
            if ($dateComparison !== 0) return $dateComparison;
            return ((int)($b['id'] ?? 0)) <=> ((int)($a['id'] ?? 0));
        });

        return array(
            'environment' => $environment,
            'origin' => $origin,
            'rows' => $rows,
            'kpis' => $this->getKpis($environment)
        );
    }

    public function getDocument($localId, $environment = 'sandbox')
    {
        $environment = $this->normalizeEnvironment($environment);
        billing_assert_runtime_operation('read', $environment, 'Detalle de documento emitido');
        $stmt = $this->pdo->prepare("
            SELECT fd.*,
                   CASE WHEN CAST(fd.document_type_id AS UNSIGNED)=54 THEN 'Guía de despacho electrónica'
                        ELSE COALESCE(bdt.document_label,CONCAT('DTE ',COALESCE(fd.document_type_taxbureau,fd.document_type_id))) END AS document_label,
                   CASE WHEN CAST(fd.document_type_id AS UNSIGNED)=54 THEN 'dispatch_guide'
                        ELSE COALESCE(bdt.document_family,'other') END AS document_family,
                   CASE WHEN CAST(fd.document_type_id AS UNSIGNED)=54 THEN 'dispatch_guide_electronic'
                        ELSE bdt.document_key END AS document_key,
                   c.customer_name,c.legal_name AS customer_legal_name,c.email,c.email_dte,c.address,c.district,c.city,c.business_activity,
                   q.quote_number,bc.id AS collection_id,bc.total_amount AS collection_total,bc.paid_amount,bc.balance_amount,
                   bc.collection_status,bc.due_date,bc.currency_code AS collection_currency_code,bc.exchange_rate_value AS collection_exchange_rate_value,
                   bc.exchange_rate_date AS collection_exchange_rate_date,bc.exchange_rate_source AS collection_exchange_rate_source,bc.exchange_rate_status AS collection_exchange_rate_status,
                   bc.foreign_total_amount,bc.foreign_paid_amount,bc.foreign_balance_amount
            FROM facto_documents fd
            LEFT JOIN billing_document_types bdt ON bdt.direction='issued' AND bdt.facto_document_type_id=CAST(fd.document_type_id AS UNSIGNED)
            LEFT JOIN crm_customers c ON c.id=fd.crm_customer_id
            LEFT JOIN crm_quotes q ON q.id=fd.crm_quote_id
            LEFT JOIN billing_collections bc ON CAST(fd.document_type_id AS UNSIGNED)<>54 AND bc.id=(
                SELECT MAX(bc2.id) FROM billing_collections bc2
                WHERE BINARY bc2.environment=BINARY fd.environment AND bc2.direction='receivable' AND bc2.facto_document_id=fd.document_id
            )
            WHERE fd.id=? AND fd.environment=? LIMIT 1
        ");
        $stmt->execute(array((int)$localId, $environment));
        $doc = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$doc) throw new RuntimeException('Documento emitido no encontrado.');

        $lineStmt = $this->pdo->prepare("SELECT * FROM billing_document_lines WHERE environment=? AND facto_document_id=? ORDER BY id");
        $lineStmt->execute(array($environment, $doc['document_id']));
        $doc['lines'] = $lineStmt->fetchAll(PDO::FETCH_ASSOC);

        $refStmt = $this->pdo->prepare("
            SELECT br.*,fd.document_number AS referenced_document_number,fd.document_type_taxbureau AS referenced_document_type
            FROM billing_document_references br
            LEFT JOIN facto_documents fd ON BINARY fd.environment=BINARY br.environment AND fd.document_id=br.reference_facto_document_id
            WHERE br.environment=? AND (br.source_facto_document_id=? OR br.reference_facto_document_id=?)
            ORDER BY br.id DESC
        ");
        $refStmt->execute(array($environment, $doc['document_id'], $doc['document_id']));
        $doc['references'] = $refStmt->fetchAll(PDO::FETCH_ASSOC);

        $opStmt = $this->pdo->prepare("
            SELECT id,operation_type,status,reason,total_amount,facto_document_id,document_number,error_message,created_at,issued_at
            FROM billing_tax_operations WHERE environment=? AND source_document_local_id=? ORDER BY id DESC
        ");
        $opStmt->execute(array($environment, (int)$localId));
        $doc['operations'] = $opStmt->fetchAll(PDO::FETCH_ASSOC);

        $guideStmt = $this->pdo->prepare("
            SELECT id,source_type,source_document_local_id,source_document_number,quote_id,customer_id,
                   facto_document_id,document_number,related_invoice_facto_document_id,issue_date,guide_type,transfer_type,
                   dispatch_address,dispatch_district,dispatch_city,observations,status,pdf_file_path,xml_file_path,transfer_copy_pdf_file_path,
                   error_message,issued_at,created_at
            FROM billing_dispatch_guides
            WHERE environment=? AND (facto_document_id=? OR (source_type='invoice' AND source_document_local_id=?)
                  OR (? IS NOT NULL AND quote_id=?))
            ORDER BY id DESC
        " );
        $guideStmt->execute(array($environment, $doc['document_id'], (int)$localId, $doc['crm_quote_id'], $doc['crm_quote_id']));
        $doc['dispatch_guides'] = $guideStmt->fetchAll(PDO::FETCH_ASSOC);
        $doc['dispatch_guide'] = null;
        foreach ($doc['dispatch_guides'] as $guide) {
            if ((string)$guide['facto_document_id'] === (string)$doc['document_id']) {
                $doc['dispatch_guide'] = $guide;
                break;
            }
        }

        $doc['can_adjust'] = in_array((int)$doc['document_type_id'], array(2,32,46), true) && (int)($doc['document_status'] ?? 1) !== 0;
        $doc['can_create_guide'] = in_array((int)$doc['document_type_id'], array(2,32,46), true) && (int)($doc['document_status'] ?? 1) !== 0;
        return $doc;
    }

    private function getKpis($environment)
    {
        $stmt = $this->pdo->prepare("
            SELECT COUNT(*) AS documents,
                   SUM(CASE WHEN CAST(document_type_id AS UNSIGNED) IN (2,32,46) THEN 1 ELSE 0 END) AS invoices,
                   SUM(CASE WHEN CAST(document_type_id AS UNSIGNED) IN (16,48) THEN 1 ELSE 0 END) AS credit_notes,
                   SUM(CASE WHEN CAST(document_type_id AS UNSIGNED) IN (17,47) THEN 1 ELSE 0 END) AS debit_notes,
                   SUM(CASE WHEN CAST(document_type_id AS UNSIGNED)=54 THEN 1 ELSE 0 END) AS dispatch_guides,
                   COALESCE(SUM(CASE
                       WHEN CAST(document_type_id AS UNSIGNED) IN (2,32,17,46,47) THEN COALESCE(net_amount_clp, CASE WHEN UPPER(COALESCE(NULLIF(currency_code,''),'CLP'))='CLP' THEN net_amount WHEN COALESCE(exchange_rate_value,0)>0 THEN ROUND(net_amount*exchange_rate_value,2) ELSE 0 END)
                       WHEN CAST(document_type_id AS UNSIGNED) IN (16,48) THEN -COALESCE(net_amount_clp, CASE WHEN UPPER(COALESCE(NULLIF(currency_code,''),'CLP'))='CLP' THEN net_amount WHEN COALESCE(exchange_rate_value,0)>0 THEN ROUND(net_amount*exchange_rate_value,2) ELSE 0 END)
                       ELSE 0 END),0) AS net_sales
            FROM facto_documents WHERE environment=?
        ");
        $stmt->execute(array($environment));
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: array();
    }

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

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