<?php

declare(strict_types=1);

namespace ApiModel\Service;

use PDO;

final class FinancialReportService
{
    public function __construct(private readonly PDO $pdo) {}

    /**
     * Balanza de Comprobación (Trial Balance)
     * 
     * @return list<array<string, mixed>>
     */
    public function getTrialBalance(string $companyId, ?string $branchId, string $startDate, string $endDate): array
    {
        // 1. Obtener todas las cuentas activas
        $stmtAcc = $this->pdo->prepare(
            'SELECT id, code, name, account_type, parent_id, is_header 
             FROM chart_accounts 
             WHERE company_id = ? AND is_active = TRUE 
             ORDER BY code ASC'
        );
        $stmtAcc->execute([$companyId]);
        $rawAccounts = $stmtAcc->fetchAll();

        // Indexar por ID
        $accounts = [];
        $childrenMap = [];
        foreach ($rawAccounts as $acc) {
            $id = (string) $acc['id'];
            $accounts[$id] = [
                'id' => $id,
                'code' => (string) $acc['code'],
                'name' => (string) $acc['name'],
                'account_type' => (string) $acc['account_type'],
                'parent_id' => $acc['parent_id'] !== null ? (string) $acc['parent_id'] : null,
                'is_header' => (bool) $acc['is_header'],
                'initial_debit' => 0.0,
                'initial_credit' => 0.0,
                'debit' => 0.0,
                'credit' => 0.0,
                'initial_balance' => 0.0,
                'final_balance' => 0.0,
            ];

            if ($acc['parent_id'] !== null) {
                $parentId = (string) $acc['parent_id'];
                $childrenMap[$parentId][] = $id;
            }
        }

        // 2. Obtener saldos iniciales (movimientos antes de startDate)
        $sqlInit = 'SELECT jl.chart_account_id, 
                           COALESCE(SUM(jl.debit_functional), 0) AS debits, 
                           COALESCE(SUM(jl.credit_functional), 0) AS credits 
                    FROM journal_lines jl 
                    JOIN journal_entries je ON jl.journal_entry_id = je.id 
                    WHERE je.company_id = ? AND je.status = \'posted\' AND je.entry_date < ?';
        $paramsInit = [$companyId, $startDate];
        if ($branchId !== null && $branchId !== '') {
            $sqlInit .= ' AND je.branch_id = ?';
            $paramsInit[] = $branchId;
        }
        $sqlInit .= ' GROUP BY jl.chart_account_id';

        $stmtInit = $this->pdo->prepare($sqlInit);
        $stmtInit->execute($paramsInit);
        foreach ($stmtInit->fetchAll() as $row) {
            $accId = (string) $row['chart_account_id'];
            if (isset($accounts[$accId])) {
                $accounts[$accId]['initial_debit'] = (float) $row['debits'];
                $accounts[$accId]['initial_credit'] = (float) $row['credits'];
            }
        }

        // 3. Obtener movimientos en el rango [startDate, endDate]
        $sqlRange = 'SELECT jl.chart_account_id, 
                            COALESCE(SUM(jl.debit_functional), 0) AS debits, 
                            COALESCE(SUM(jl.credit_functional), 0) AS credits 
                     FROM journal_lines jl 
                     JOIN journal_entries je ON jl.journal_entry_id = je.id 
                     WHERE je.company_id = ? AND je.status = \'posted\' AND je.entry_date BETWEEN ? AND ?';
        $paramsRange = [$companyId, $startDate, $endDate];
        if ($branchId !== null && $branchId !== '') {
            $sqlRange .= ' AND je.branch_id = ?';
            $paramsRange[] = $branchId;
        }
        $sqlRange .= ' GROUP BY jl.chart_account_id';

        $stmtRange = $this->pdo->prepare($sqlRange);
        $stmtRange->execute($paramsRange);
        foreach ($stmtRange->fetchAll() as $row) {
            $accId = (string) $row['chart_account_id'];
            if (isset($accounts[$accId])) {
                $accounts[$accId]['debit'] = (float) $row['debits'];
                $accounts[$accId]['credit'] = (float) $row['credits'];
            }
        }

        // 4. Agregar jerárquicamente
        $memo = [];
        foreach ($accounts as $id => $acc) {
            if ($acc['parent_id'] === null) {
                $this->aggregateTrialBalance($id, $accounts, $childrenMap, $memo);
            }
        }

        // 5. Calcular saldos netos finales e iniciales según naturaleza
        foreach ($accounts as $id => &$acc) {
            $isDebitNature = in_array($acc['account_type'], ['asset', 'expense'], true);
            if ($isDebitNature) {
                $acc['initial_balance'] = $acc['initial_debit'] - $acc['initial_credit'];
                $acc['final_balance'] = ($acc['initial_debit'] + $acc['debit']) - ($acc['initial_credit'] + $acc['credit']);
            } else {
                $acc['initial_balance'] = $acc['initial_credit'] - $acc['initial_debit'];
                $acc['final_balance'] = ($acc['initial_credit'] + $acc['credit']) - ($acc['initial_debit'] + $acc['debit']);
            }
        }
        unset($acc); // Quitar referencia

        return array_values($accounts);
    }

    /**
     * Balance General (Balance Sheet)
     * 
     * @return array<string, mixed>
     */
    public function getBalanceSheet(string $companyId, ?string $branchId, string $endDate): array
    {
        // 1. Obtener todas las cuentas activas de Balance
        $stmtAcc = $this->pdo->prepare(
            'SELECT id, code, name, account_type, parent_id, is_header 
             FROM chart_accounts 
             WHERE company_id = ? AND is_active = TRUE AND account_type IN (\'asset\', \'liability\', \'equity\') 
             ORDER BY code ASC'
        );
        $stmtAcc->execute([$companyId]);
        $rawAccounts = $stmtAcc->fetchAll();

        // Indexar
        $accounts = [];
        $childrenMap = [];
        foreach ($rawAccounts as $acc) {
            $id = (string) $acc['id'];
            $accounts[$id] = [
                'id' => $id,
                'code' => (string) $acc['code'],
                'name' => (string) $acc['name'],
                'account_type' => (string) $acc['account_type'],
                'parent_id' => $acc['parent_id'] !== null ? (string) $acc['parent_id'] : null,
                'is_header' => (bool) $acc['is_header'],
                'debit' => 0.0,
                'credit' => 0.0,
                'balance' => 0.0,
            ];

            if ($acc['parent_id'] !== null) {
                $parentId = (string) $acc['parent_id'];
                $childrenMap[$parentId][] = $id;
            }
        }

        // 2. Obtener movimientos acumulados hasta endDate para estas cuentas
        $sqlRange = 'SELECT jl.chart_account_id, 
                            COALESCE(SUM(jl.debit_functional), 0) AS debits, 
                            COALESCE(SUM(jl.credit_functional), 0) AS credits 
                     FROM journal_lines jl 
                     JOIN journal_entries je ON jl.journal_entry_id = je.id 
                     WHERE je.company_id = ? AND je.status = \'posted\' AND je.entry_date <= ?';
        $paramsRange = [$companyId, $endDate];
        if ($branchId !== null && $branchId !== '') {
            $sqlRange .= ' AND je.branch_id = ?';
            $paramsRange[] = $branchId;
        }
        $sqlRange .= ' GROUP BY jl.chart_account_id';

        $stmtRange = $this->pdo->prepare($sqlRange);
        $stmtRange->execute($paramsRange);
        foreach ($stmtRange->fetchAll() as $row) {
            $accId = (string) $row['chart_account_id'];
            if (isset($accounts[$accId])) {
                $accounts[$accId]['debit'] = (float) $row['debits'];
                $accounts[$accId]['credit'] = (float) $row['credits'];
            }
        }

        // 3. Calcular la Utilidad / Pérdida del Ejercicio (Ingresos - Gastos hasta endDate)
        $sqlPl = 'SELECT ca.account_type, 
                         COALESCE(SUM(jl.debit_functional), 0) AS debits, 
                         COALESCE(SUM(jl.credit_functional), 0) AS credits 
                  FROM journal_lines jl 
                  JOIN journal_entries je ON jl.journal_entry_id = je.id 
                  JOIN chart_accounts ca ON jl.chart_account_id = ca.id 
                  WHERE je.company_id = ? AND je.status = \'posted\' AND je.entry_date <= ? 
                    AND ca.account_type IN (\'income\', \'expense\')';
        $paramsPl = [$companyId, $endDate];
        if ($branchId !== null && $branchId !== '') {
            $sqlPl .= ' AND je.branch_id = ?';
            $paramsPl[] = $branchId;
        }
        $sqlPl .= ' GROUP BY ca.account_type';

        $stmtPl = $this->pdo->prepare($sqlPl);
        $stmtPl->execute($paramsPl);
        
        $totalIncome = 0.0;
        $totalExpense = 0.0;
        foreach ($stmtPl->fetchAll() as $row) {
            if ($row['account_type'] === 'income') {
                $totalIncome = (float) $row['credits'] - (float) $row['debits'];
            } elseif ($row['account_type'] === 'expense') {
                $totalExpense = (float) $row['debits'] - (float) $row['credits'];
            }
        }
        $netIncome = $totalIncome - $totalExpense;

        // Buscar un padre adecuado para inyectar la utilidad del ejercicio
        $equityParentId = null;
        foreach ($accounts as $acc) {
            if ($acc['account_type'] === 'equity' && $acc['is_header'] && str_starts_with($acc['code'], '3')) {
                $equityParentId = $acc['id'];
                break;
            }
        }

        // Inyectar cuenta virtual de Utilidad del Ejercicio
        $virtualId = 'virtual-net-income-id';
        $accounts[$virtualId] = [
            'id' => $virtualId,
            'code' => '3605',
            'name' => 'Utilidad / Pérdida del Ejercicio',
            'account_type' => 'equity',
            'parent_id' => $equityParentId,
            'is_header' => false,
            'debit' => 0.0,
            'credit' => $netIncome,
            'balance' => $netIncome, // Naturaleza crédito
        ];
        if ($equityParentId !== null) {
            $childrenMap[$equityParentId][] = $virtualId;
        }

        // 4. Calcular balance preliminar neto para cada cuenta individual
        foreach ($accounts as $id => &$acc) {
            if ($id === $virtualId) {
                continue;
            }
            if ($acc['account_type'] === 'asset') {
                $acc['balance'] = $acc['debit'] - $acc['credit'];
            } else {
                $acc['balance'] = $acc['credit'] - $acc['debit'];
            }
        }
        unset($acc);

        // 5. Agregar jerárquicamente los saldos (acumular hijos en padres)
        $memo = [];
        foreach ($accounts as $id => $acc) {
            if ($acc['parent_id'] === null) {
                $this->aggregateBalance($id, $accounts, $childrenMap, $memo);
            }
        }

        // 6. Separar en bloques para el reporte
        $assets = [];
        $liabilities = [];
        $equity = [];

        foreach ($accounts as $acc) {
            if ($acc['account_type'] === 'asset') {
                $assets[] = $acc;
            } elseif ($acc['account_type'] === 'liability') {
                $liabilities[] = $acc;
            } elseif ($acc['account_type'] === 'equity') {
                $equity[] = $acc;
            }
        }

        // Totales de primer nivel
        $totalAssets = 0.0;
        foreach ($assets as $acc) {
            if ($acc['parent_id'] === null) {
                $totalAssets += $acc['balance'];
            }
        }

        $totalLiabilities = 0.0;
        foreach ($liabilities as $acc) {
            if ($acc['parent_id'] === null) {
                $totalLiabilities += $acc['balance'];
            }
        }

        $totalEquity = 0.0;
        foreach ($equity as $acc) {
            if ($acc['parent_id'] === null) {
                $totalEquity += $acc['balance'];
            }
        }

        return [
            'assets' => $assets,
            'liabilities' => $liabilities,
            'equity' => $equity,
            'totals' => [
                'total_assets' => $totalAssets,
                'total_liabilities' => $totalLiabilities,
                'total_equity' => $totalEquity,
                'net_income' => $netIncome,
            ]
        ];
    }

    /**
     * Estado de Resultados (Profit and Loss)
     * 
     * @return array<string, mixed>
     */
    public function getProfitAndLoss(string $companyId, ?string $branchId, string $startDate, string $endDate): array
    {
        // 1. Obtener todas las cuentas activas de Ingreso y Gasto
        $stmtAcc = $this->pdo->prepare(
            'SELECT id, code, name, account_type, parent_id, is_header 
             FROM chart_accounts 
             WHERE company_id = ? AND is_active = TRUE AND account_type IN (\'income\', \'expense\') 
             ORDER BY code ASC'
        );
        $stmtAcc->execute([$companyId]);
        $rawAccounts = $stmtAcc->fetchAll();

        // Indexar
        $accounts = [];
        $childrenMap = [];
        foreach ($rawAccounts as $acc) {
            $id = (string) $acc['id'];
            $accounts[$id] = [
                'id' => $id,
                'code' => (string) $acc['code'],
                'name' => (string) $acc['name'],
                'account_type' => (string) $acc['account_type'],
                'parent_id' => $acc['parent_id'] !== null ? (string) $acc['parent_id'] : null,
                'is_header' => (bool) $acc['is_header'],
                'debit' => 0.0,
                'credit' => 0.0,
                'balance' => 0.0,
            ];

            if ($acc['parent_id'] !== null) {
                $parentId = (string) $acc['parent_id'];
                $childrenMap[$parentId][] = $id;
            }
        }

        // 2. Obtener movimientos en rango
        $sqlRange = 'SELECT jl.chart_account_id, 
                            COALESCE(SUM(jl.debit_functional), 0) AS debits, 
                            COALESCE(SUM(jl.credit_functional), 0) AS credits 
                     FROM journal_lines jl 
                     JOIN journal_entries je ON jl.journal_entry_id = je.id 
                     WHERE je.company_id = ? AND je.status = \'posted\' AND je.entry_date BETWEEN ? AND ?';
        $paramsRange = [$companyId, $startDate, $endDate];
        if ($branchId !== null && $branchId !== '') {
            $sqlRange .= ' AND je.branch_id = ?';
            $paramsRange[] = $branchId;
        }
        $sqlRange .= ' GROUP BY jl.chart_account_id';

        $stmtRange = $this->pdo->prepare($sqlRange);
        $stmtRange->execute($paramsRange);
        foreach ($stmtRange->fetchAll() as $row) {
            $accId = (string) $row['chart_account_id'];
            if (isset($accounts[$accId])) {
                $accounts[$accId]['debit'] = (float) $row['debits'];
                $accounts[$accId]['credit'] = (float) $row['credits'];
            }
        }

        // 3. Calcular balance por cuenta
        foreach ($accounts as &$acc) {
            if ($acc['account_type'] === 'income') {
                $acc['balance'] = $acc['credit'] - $acc['debit'];
            } else {
                $acc['balance'] = $acc['debit'] - $acc['credit'];
            }
        }
        unset($acc);

        // 4. Agregar jerárquicamente los saldos
        $memo = [];
        foreach ($accounts as $id => $acc) {
            if ($acc['parent_id'] === null) {
                $this->aggregateBalance($id, $accounts, $childrenMap, $memo);
            }
        }

        // 5. Separar y calcular totales
        $income = [];
        $expenses = [];

        foreach ($accounts as $acc) {
            if ($acc['account_type'] === 'income') {
                $income[] = $acc;
            } else {
                $expenses[] = $acc;
            }
        }

        $totalIncome = 0.0;
        foreach ($income as $acc) {
            if ($acc['parent_id'] === null) {
                $totalIncome += $acc['balance'];
            }
        }

        $totalExpense = 0.0;
        foreach ($expenses as $acc) {
            if ($acc['parent_id'] === null) {
                $totalExpense += $acc['balance'];
            }
        }

        return [
            'income' => $income,
            'expenses' => $expenses,
            'totals' => [
                'total_income' => $totalIncome,
                'total_expense' => $totalExpense,
                'net_income' => $totalIncome - $totalExpense,
            ]
        ];
    }

    /**
     * Antigüedad de Cuentas por Cobrar (AR Aging)
     * 
     * @return list<array<string, mixed>>
     */
    public function getArAgingReport(string $companyId, ?string $branchId, string $referenceDate): array
    {
        $sql = 'SELECT si.id, si.invoice_number, si.invoice_date, si.due_date, si.total, si.balance, 
                       si.customer_id, c.name AS customer_name 
                FROM sales_invoices si 
                JOIN contacts c ON si.customer_id = c.id 
                WHERE si.company_id = ? AND si.status NOT IN (\'void\', \'paid\') AND si.balance > 0';
        
        $params = [$companyId];
        if ($branchId !== null && $branchId !== '') {
            $sql .= ' AND si.branch_id = ?';
            $params[] = $branchId;
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $invoices = $stmt->fetchAll();

        return $this->processAging($invoices, $referenceDate, 'customer_id', 'customer_name');
    }

    /**
     * Antigüedad de Cuentas por Pagar (AP Aging)
     * 
     * @return list<array<string, mixed>>
     */
    public function getApAgingReport(string $companyId, ?string $branchId, string $referenceDate): array
    {
        $sql = 'SELECT pb.id, pb.bill_number, pb.bill_date, pb.due_date, pb.total, pb.balance, 
                       pb.vendor_id, c.name AS vendor_name 
                FROM purchases_bills pb 
                JOIN contacts c ON pb.vendor_id = c.id 
                WHERE pb.company_id = ? AND pb.status NOT IN (\'void\', \'paid\') AND pb.balance > 0';
        
        $params = [$companyId];
        if ($branchId !== null && $branchId !== '') {
            $sql .= ' AND pb.branch_id = ?';
            $params[] = $branchId;
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $bills = $stmt->fetchAll();

        return $this->processAging($bills, $referenceDate, 'vendor_id', 'vendor_name');
    }

    /**
     * Helper para procesar brackets de antigüedad
     * 
     * @param list<array<string, mixed>> $documents
     * @return list<array<string, mixed>>
     */
    private function processAging(array $documents, string $referenceDate, string $contactIdKey, string $contactNameKey): array
    {
        $refTime = strtotime($referenceDate);
        $grouped = [];

        foreach ($documents as $doc) {
            $cId = (string) $doc[$contactIdKey];
            $cName = (string) $doc[$contactNameKey];
            $balance = (float) $doc['balance'];
            $dueTime = strtotime((string) $doc['due_date']);

            // Días de retraso (vencimiento - referencia)
            // Si dueTime >= refTime, está al día (días <= 0)
            $diffSeconds = $refTime - $dueTime;
            $daysOverdue = (int) ceil($diffSeconds / 86400);

            if (!isset($grouped[$cId])) {
                $grouped[$cId] = [
                    'contact_id' => $cId,
                    'contact_name' => $cName,
                    'total_balance' => 0.0,
                    'current' => 0.0,
                    'bracket_1_30' => 0.0,
                    'bracket_31_60' => 0.0,
                    'bracket_61_90' => 0.0,
                    'bracket_over_90' => 0.0,
                ];
            }

            $grouped[$cId]['total_balance'] += $balance;

            if ($daysOverdue <= 0) {
                $grouped[$cId]['current'] += $balance;
            } elseif ($daysOverdue <= 30) {
                $grouped[$cId]['bracket_1_30'] += $balance;
            } elseif ($daysOverdue <= 60) {
                $grouped[$cId]['bracket_31_60'] += $balance;
            } elseif ($daysOverdue <= 90) {
                $grouped[$cId]['bracket_61_90'] += $balance;
            } else {
                $grouped[$cId]['bracket_over_90'] += $balance;
            }
        }

        // Ordenar por nombre del contacto
        $result = array_values($grouped);
        usort($result, fn (array $a, array $b): int => strcmp((string) $a['contact_name'], (string) $b['contact_name']));

        return $result;
    }

    /**
     * Acumulación recursiva de Balanza de Comprobación
     * 
     * @param array<string, array<string, mixed>> $accounts
     * @param array<string, list<string>> $childrenMap
     * @param array<string, array<string, float>> $memo
     * @return array<string, float>
     */
    private function aggregateTrialBalance(string $id, array &$accounts, array $childrenMap, array &$memo): array
    {
        if (isset($memo[$id])) {
            return $memo[$id];
        }

        $acc = $accounts[$id];
        $totals = [
            'initial_debit' => (float) $acc['initial_debit'],
            'initial_credit' => (float) $acc['initial_credit'],
            'debit' => (float) $acc['debit'],
            'credit' => (float) $acc['credit']
        ];

        if (isset($childrenMap[$id])) {
            foreach ($childrenMap[$id] as $childId) {
                $childTotals = $this->aggregateTrialBalance($childId, $accounts, $childrenMap, $memo);
                $totals['initial_debit'] += $childTotals['initial_debit'];
                $totals['initial_credit'] += $childTotals['initial_credit'];
                $totals['debit'] += $childTotals['debit'];
                $totals['credit'] += $childTotals['credit'];
            }
        }

        $accounts[$id]['initial_debit'] = $totals['initial_debit'];
        $accounts[$id]['initial_credit'] = $totals['initial_credit'];
        $accounts[$id]['debit'] = $totals['debit'];
        $accounts[$id]['credit'] = $totals['credit'];

        $memo[$id] = $totals;
        return $totals;
    }

    /**
     * Acumulación recursiva de Saldos Netos (Balance Sheet y P&L)
     * 
     * @param array<string, array<string, mixed>> $accounts
     * @param array<string, list<string>> $childrenMap
     * @param array<string, float> $memo
     */
    private function aggregateBalance(string $id, array &$accounts, array $childrenMap, array &$memo): float
    {
        if (isset($memo[$id])) {
            return $memo[$id];
        }

        $acc = $accounts[$id];
        $balance = (float) $acc['balance'];

        if (isset($childrenMap[$id])) {
            foreach ($childrenMap[$id] as $childId) {
                $balance += $this->aggregateBalance($childId, $accounts, $childrenMap, $memo);
            }
        }

        $accounts[$id]['balance'] = $balance;
        $memo[$id] = $balance;
        return $balance;
    }

    /**
     * Libro de Ventas
     * 
     * @return list<array<string, mixed>>
     */
    public function getSalesBook(string $companyId, ?string $branchId, string $startDate, string $endDate): array
    {
        $sql = "SELECT * FROM (
                    SELECT si.id, 
                           si.invoice_number, 
                           si.invoice_date, 
                           si.due_date, 
                           si.subtotal, 
                           si.discount_amount, 
                           si.tax_amount, 
                           si.total, 
                           si.balance, 
                           si.status, 
                           si.currency_code, 
                           si.exchange_rate,
                           si.company_id,
                           si.branch_id,
                           c.name AS customer_name, 
                           c.tax_id AS customer_tax_id
                    FROM sales_invoices si
                    JOIN contacts c ON si.customer_id = c.id
                    WHERE si.status != 'void'

                    UNION ALL

                    SELECT sa.id,
                           CASE WHEN sa.adjustment_type = 'credit_note' THEN 'NC-' || sa.document_number ELSE 'ND-' || sa.document_number END AS invoice_number,
                           sa.document_date AS invoice_date,
                           sa.document_date AS due_date,
                           CASE WHEN sa.adjustment_type = 'credit_note' THEN -sa.subtotal ELSE sa.subtotal END AS subtotal,
                           sa.discount_amount,
                           CASE WHEN sa.adjustment_type = 'credit_note' THEN -sa.tax_amount ELSE sa.tax_amount END AS tax_amount,
                           CASE WHEN sa.adjustment_type = 'credit_note' THEN -sa.total ELSE sa.total END AS total,
                           0.0 AS balance,
                           sa.status,
                           'USD' AS currency_code,
                           1.0 AS exchange_rate,
                           sa.company_id,
                           sa.branch_id,
                           c.name AS customer_name,
                           c.tax_id AS customer_tax_id
                    FROM sales_adjustments sa
                    JOIN contacts c ON sa.customer_id = c.id
                    WHERE sa.status != 'void'
                ) t
                WHERE t.company_id = ? AND t.invoice_date BETWEEN ? AND ?";
        
        $params = [$companyId, $startDate, $endDate];
        if ($branchId !== null && $branchId !== '') {
            $sql .= ' AND t.branch_id = ?';
            $params[] = $branchId;
        }
        $sql .= ' ORDER BY t.invoice_date ASC, t.invoice_number ASC';

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

        return array_map(function (array $r): array {
            return [
                'id' => $r['id'],
                'invoice_number' => $r['invoice_number'],
                'invoice_date' => $r['invoice_date'],
                'due_date' => $r['due_date'],
                'currency_code' => $r['currency_code'],
                'exchange_rate' => (float) $r['exchange_rate'],
                'subtotal' => (float) $r['subtotal'],
                'discount_amount' => (float) $r['discount_amount'],
                'tax_amount' => (float) $r['tax_amount'],
                'total' => (float) $r['total'],
                'balance' => (float) $r['balance'],
                'status' => $r['status'],
                'customer_name' => $r['customer_name'],
                'customer_tax_id' => $r['customer_tax_id'] ?? '',
            ];
        }, $rows);
    }

    /**
     * Libro de Compras
     * 
     * @return list<array<string, mixed>>
     */
    public function getPurchasesBook(string $companyId, ?string $branchId, string $startDate, string $endDate): array
    {
        $sql = 'SELECT pb.id, pb.bill_number, pb.bill_date, pb.due_date, pb.total, pb.balance,
                       pb.subtotal, pb.discount_amount, pb.tax_amount, pb.currency_code, pb.exchange_rate,
                       pb.status, c.name AS vendor_name, c.tax_id AS vendor_tax_id
                FROM purchases_bills pb
                JOIN contacts c ON pb.vendor_id = c.id
                WHERE pb.company_id = ? AND pb.status != \'void\' AND pb.bill_date BETWEEN ? AND ?';
        
        $params = [$companyId, $startDate, $endDate];
        if ($branchId !== null && $branchId !== '') {
            $sql .= ' AND pb.branch_id = ?';
            $params[] = $branchId;
        }
        $sql .= ' ORDER BY pb.bill_date ASC, pb.bill_number ASC';

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

        return array_map(function (array $r): array {
            return [
                'id' => $r['id'],
                'bill_number' => $r['bill_number'],
                'bill_date' => $r['bill_date'],
                'due_date' => $r['due_date'],
                'currency_code' => $r['currency_code'],
                'exchange_rate' => (float) $r['exchange_rate'],
                'subtotal' => (float) $r['subtotal'],
                'discount_amount' => (float) $r['discount_amount'],
                'tax_amount' => (float) $r['tax_amount'],
                'total' => (float) $r['total'],
                'balance' => (float) $r['balance'],
                'status' => $r['status'],
                'vendor_name' => $r['vendor_name'],
                'vendor_tax_id' => $r['vendor_tax_id'] ?? '',
            ];
        }, $rows);
    }

    /**
     * Libro Diario
     */
    public function getJournalBook(string $companyId, ?string $branchId, string $startDate, string $endDate): array
    {
        $sql = 'SELECT je.entry_date, je.entry_number, je.description as entry_desc,
                       jl.line_order, jl.description as line_desc, jl.debit_functional, jl.credit_functional,
                       ca.code as account_code, ca.name as account_name
                FROM journal_lines jl
                JOIN journal_entries je ON jl.journal_entry_id = je.id
                JOIN chart_accounts ca ON jl.chart_account_id = ca.id
                WHERE je.company_id = ? AND je.status = \'posted\' AND je.entry_date BETWEEN ? AND ?';
        
        $params = [$companyId, $startDate, $endDate];
        if ($branchId !== null && $branchId !== '') {
            $sql .= ' AND je.branch_id = ?';
            $params[] = $branchId;
        }
        $sql .= ' ORDER BY je.entry_date ASC, je.entry_number ASC, jl.line_order ASC';

        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        return $st->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Libro Mayor
     */
    public function getGeneralLedger(string $companyId, ?string $branchId, string $startDate, string $endDate, ?string $accountId = null): array
    {
        $sqlAcc = 'SELECT id, code, name, account_type, parent_id, is_header 
                   FROM chart_accounts 
                   WHERE company_id = ? AND is_active = TRUE';
        $paramsAcc = [$companyId];
        if ($accountId) {
            $sqlAcc .= ' AND id = ?';
            $paramsAcc[] = $accountId;
        }
        $sqlAcc .= ' ORDER BY code ASC';
        
        $stmtAcc = $this->pdo->prepare($sqlAcc);
        $stmtAcc->execute($paramsAcc);
        $rawAccounts = $stmtAcc->fetchAll(PDO::FETCH_ASSOC);

        $result = [];

        foreach ($rawAccounts as $acc) {
            $accId = (string) $acc['id'];
            $isDebitNature = in_array($acc['account_type'], ['asset', 'expense'], true);

            // A. Saldo Inicial (movimientos antes de startDate)
            $sqlInit = 'SELECT COALESCE(SUM(jl.debit_functional), 0) AS debits, 
                               COALESCE(SUM(jl.credit_functional), 0) AS credits 
                        FROM journal_lines jl 
                        JOIN journal_entries je ON jl.journal_entry_id = je.id 
                        WHERE je.company_id = ? AND je.status = \'posted\' AND jl.chart_account_id = ? AND je.entry_date < ?';
            $paramsInit = [$companyId, $accId, $startDate];
            if ($branchId !== null && $branchId !== '') {
                $sqlInit .= ' AND je.branch_id = ?';
                $paramsInit[] = $branchId;
            }
            $stmtInit = $this->pdo->prepare($sqlInit);
            $stmtInit->execute($paramsInit);
            $initRow = $stmtInit->fetch(PDO::FETCH_ASSOC);
            $initDeb = (float) $initRow['debits'];
            $initCred = (float) $initRow['credits'];
            $initialBalance = $isDebitNature ? ($initDeb - $initCred) : ($initCred - $initDeb);

            // B. Movimientos en el rango
            $sqlRange = 'SELECT je.entry_date, je.entry_number, jl.description, 
                                jl.debit_functional as debit, jl.credit_functional as credit
                         FROM journal_lines jl 
                         JOIN journal_entries je ON jl.journal_entry_id = je.id 
                         WHERE je.company_id = ? AND je.status = \'posted\' AND jl.chart_account_id = ? AND je.entry_date BETWEEN ? AND ?';
            $paramsRange = [$companyId, $accId, $startDate, $endDate];
            if ($branchId !== null && $branchId !== '') {
                $sqlRange .= ' AND je.branch_id = ?';
                $paramsRange[] = $branchId;
            }
            $sqlRange .= ' ORDER BY je.entry_date ASC, je.entry_number ASC, jl.line_order ASC';
            
            $stmtRange = $this->pdo->prepare($sqlRange);
            $stmtRange->execute($paramsRange);
            $movements = $stmtRange->fetchAll(PDO::FETCH_ASSOC);

            // Calcular saldos acumulativos
            $runningBalance = $initialBalance;
            $formattedMovements = [];
            foreach ($movements as $m) {
                $deb = (float) $m['debit'];
                $cred = (float) $m['credit'];
                $runningBalance += $isDebitNature ? ($deb - $cred) : ($cred - $deb);
                
                $formattedMovements[] = [
                    'entry_date' => $m['entry_date'],
                    'entry_number' => $m['entry_number'],
                    'description' => $m['description'],
                    'debit' => $deb,
                    'credit' => $cred,
                    'balance' => $runningBalance
                ];
            }

            $result[] = [
                'account_id' => $accId,
                'code' => $acc['code'],
                'name' => $acc['name'],
                'account_type' => $acc['account_type'],
                'is_header' => (bool)$acc['is_header'],
                'is_debit_nature' => $isDebitNature,
                'initial_debit' => $initDeb,
                'initial_credit' => $initCred,
                'initial_balance' => $initialBalance,
                'movements' => $formattedMovements,
                'final_balance' => $runningBalance
            ];
        }

        return $result;
    }

    /**
     * Resumen Fiscal de Impuestos y Retenciones
     */
    public function getTaxSummary(string $companyId, ?string $branchId, string $startDate, string $endDate): array
    {
        // 1. IVA Débito (Ventas)
        $sqlVatDebit = "
            SELECT COALESCE(SUM(t.tax_amount * t.exchange_rate), 0) AS total_tax, 
                   COALESCE(SUM(t.subtotal * t.exchange_rate), 0) AS total_base
            FROM (
                SELECT si.tax_amount, si.subtotal, si.exchange_rate, si.branch_id 
                FROM sales_invoices si 
                WHERE si.company_id = ? AND si.status = 'posted' AND si.invoice_date BETWEEN ? AND ?
                UNION ALL
                SELECT CASE WHEN sa.adjustment_type = 'credit_note' THEN -sa.tax_amount ELSE sa.tax_amount END as tax_amount,
                       CASE WHEN sa.adjustment_type = 'credit_note' THEN -sa.subtotal ELSE sa.subtotal END as subtotal,
                       1.0 as exchange_rate, sa.branch_id
                FROM sales_adjustments sa 
                WHERE sa.company_id = ? AND sa.status = 'posted' AND sa.document_date BETWEEN ? AND ?
            ) t
            WHERE 1=1";
        $paramsVatDebit = [$companyId, $startDate, $endDate, $companyId, $startDate, $endDate];
        if ($branchId !== null && $branchId !== '') {
            $sqlVatDebit .= ' AND t.branch_id = ?';
            $paramsVatDebit[] = $branchId;
        }
        $stmtVatDebit = $this->pdo->prepare($sqlVatDebit);
        $stmtVatDebit->execute($paramsVatDebit);
        $vatDebit = $stmtVatDebit->fetch(PDO::FETCH_ASSOC);

        // 2. IVA Crédito (Compras)
        $sqlVatCredit = "
            SELECT COALESCE(SUM(t.tax_amount * t.exchange_rate), 0) AS total_tax,
                   COALESCE(SUM(t.subtotal * t.exchange_rate), 0) AS total_base
            FROM (
                SELECT pb.tax_amount, pb.subtotal, pb.exchange_rate, pb.branch_id 
                FROM purchases_bills pb 
                WHERE pb.company_id = ? AND pb.status = 'posted' AND pb.bill_date BETWEEN ? AND ?
                UNION ALL
                SELECT pel.tax_amount, (pel.price * pel.quantity) as subtotal, pe.exchange_rate, pe.branch_id
                FROM purchases_expense_lines pel
                JOIN purchases_expenses pe ON pel.expense_id = pe.id
                WHERE pe.company_id = ? AND pe.payment_date BETWEEN ? AND ?
            ) t
            WHERE 1=1";
        $paramsVatCredit = [$companyId, $startDate, $endDate, $companyId, $startDate, $endDate];
        if ($branchId !== null && $branchId !== '') {
            $sqlVatCredit .= ' AND t.branch_id = ?';
            $paramsVatCredit[] = $branchId;
        }
        $stmtVatCredit = $this->pdo->prepare($sqlVatCredit);
        $stmtVatCredit->execute($paramsVatCredit);
        $vatCredit = $stmtVatCredit->fetch(PDO::FETCH_ASSOC);

        // 3. Retenciones Recibidas (en cobros de clientes - pagos)
        $sqlWithSales = "
            SELECT ca.code as account_code, ca.name as account_name, COALESCE(SUM(p.withholding_functional), 0) as amount
            FROM (
                SELECT withholding_amount * exchange_rate as withholding_functional, withholding_account_id, branch_id
                FROM payment_receives
                WHERE company_id = ? AND withholding_amount > 0 AND payment_date BETWEEN ? AND ?
            ) p
            JOIN chart_accounts ca ON p.withholding_account_id = ca.id
            WHERE 1=1";
        $paramsWithSales = [$companyId, $startDate, $endDate];
        if ($branchId !== null && $branchId !== '') {
            $sqlWithSales .= ' AND p.branch_id = ?';
            $paramsWithSales[] = $branchId;
        }
        $sqlWithSales .= ' GROUP BY ca.code, ca.name ORDER BY ca.code';
        $stmtWithSales = $this->pdo->prepare($sqlWithSales);
        $stmtWithSales->execute($paramsWithSales);
        $withholdingsSales = $stmtWithSales->fetchAll(PDO::FETCH_ASSOC);

        // 4. Retenciones Aplicadas (en pagos a proveedores)
        $sqlWithPurchases = "
            SELECT ca.code as account_code, ca.name as account_name, COALESCE(SUM(p.withholding_functional), 0) as amount
            FROM (
                SELECT withholding_amount * exchange_rate as withholding_functional, withholding_account_id, branch_id
                FROM purchases_payments
                WHERE company_id = ? AND withholding_amount > 0 AND payment_date BETWEEN ? AND ?
            ) p
            JOIN chart_accounts ca ON p.withholding_account_id = ca.id
            WHERE 1=1";
        $paramsWithPurchases = [$companyId, $startDate, $endDate];
        if ($branchId !== null && $branchId !== '') {
            $sqlWithPurchases .= ' AND p.branch_id = ?';
            $paramsWithPurchases[] = $branchId;
        }
        $sqlWithPurchases .= ' GROUP BY ca.code, ca.name ORDER BY ca.code';
        $stmtWithPurchases = $this->pdo->prepare($sqlWithPurchases);
        $stmtWithPurchases->execute($paramsWithPurchases);
        $withholdingsPurchases = $stmtWithPurchases->fetchAll(PDO::FETCH_ASSOC);

        // Calcular neto IVA
        $salesTax = (float) $vatDebit['total_tax'];
        $purchasesTax = (float) $vatCredit['total_tax'];
        $netVat = $salesTax - $purchasesTax;

        return [
            'vat_sales' => [
                'base' => (float) $vatDebit['total_base'],
                'tax' => $salesTax
            ],
            'vat_purchases' => [
                'base' => (float) $vatCredit['total_base'],
                'tax' => $purchasesTax
            ],
            'net_vat' => [
                'amount' => abs($netVat),
                'type' => $netVat >= 0 ? 'debit' : 'credit' // debit: a pagar, credit: a favor
            ],
            'withholdings_sales' => $withholdingsSales,
            'withholdings_purchases' => $withholdingsPurchases
        ];
    }
}
