Tul xxx Tul

Viewing: ReportesController.php

<?php

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class ReportesController
{
    public function deuda(): void
    {
        $filters = $this->collectDebtFilters();
        $rows = $this->fetchDebtRows($filters);
        $totalGeneral = $this->calculateDebtTotal($rows);

        if (isset($_GET['export']) && $_GET['export'] === 'csv') {
            $this->exportDebtCsv($rows, $totalGeneral);
            return;
        }

        if (isset($_GET['print']) && $_GET['print'] === '1') {
            $this->renderDebtPrint($rows, $totalGeneral, $filters);
            return;
        }

        $filtersLabel = $this->describeDebtFilters($filters);
        $q = $filters['q'] ?? '';

        require __DIR__ . '/../views/reportes/deuda.php';
    }

    public function deudaExportExcel(): void
    {
        $filters = $this->collectDebtFilters();
        $rows = $this->fetchDebtRows($filters);
        $totalGeneral = $this->calculateDebtTotal($rows);
        $this->exportDebtExcel($rows, $totalGeneral, $filters);
    }

    public function deudaExportPdf(): void
    {
        $filters = $this->collectDebtFilters();
        $rows = $this->fetchDebtRows($filters);
        $totalGeneral = $this->calculateDebtTotal($rows);

        $autoload = __DIR__ . '/../vendor/autoload.php';
        if (is_file($autoload)) { @require_once $autoload; }

        $html = $this->renderDebtPdfHtml($rows, $totalGeneral, $filters);

        if (class_exists('Dompdf\\Dompdf')) {
            $dompdf = new Dompdf\Dompdf();
            $dompdf->loadHtml($html);
            $dompdf->setPaper('A4', 'portrait');
            $dompdf->render();
            $filename = 'Deuda_por_cliente_' . date('Ymd_His') . '.pdf';
            $dompdf->stream($filename, ['Attachment' => true]);
            return;
        }

        header('Content-Type: text/html; charset=UTF-8');
        echo $html;
        echo '<script>window.addEventListener("load",()=>setTimeout(()=>window.print(),200));</script>';
    }

    private function collectDebtFilters(): array
    {
        return [
            'q' => trim((string)($_GET['q'] ?? '')),
        ];
    }

    private function fetchDebtRows(array $filters): array
    {
        $pdo = (new Database())->getConnection();
        $params = [];
        $filterSql = '';
        if ($filters['q'] !== '') {
            $filterSql = " AND (c.name LIKE :q1 OR c.customer_code LIKE :q4 OR c.document_number LIKE :q2 OR c.address LIKE :q3)";
            $params[':q1'] = '%' . $filters['q'] . '%';
            $params[':q2'] = '%' . $filters['q'] . '%';
            $params[':q3'] = '%' . $filters['q'] . '%';
            $params[':q4'] = '%' . $filters['q'] . '%';
        }

        $sql = "SELECT 
                    c.id,
                    c.name,
                    c.document_number,
                    c.address,
                    SUM(GREATEST(i.total - COALESCE(p.paid,0), 0)) AS debt_total,
                    SUM(CASE WHEN (i.total - COALESCE(p.paid,0)) > 0 THEN 1 ELSE 0 END) AS pending_count
                FROM invoices i
                JOIN customers c ON c.id = i.customer_id
                LEFT JOIN (
                    SELECT invoice_id, SUM(amount) AS paid
                    FROM payments
                    WHERE invoice_id IS NOT NULL
                    GROUP BY invoice_id
                ) p ON p.invoice_id = i.id
                WHERE i.status IN ('Pendiente','Parcial','Vencida')
                  $filterSql
                GROUP BY c.id, c.name, c.document_number, c.address
                HAVING debt_total > 0
                ORDER BY c.name ASC";

        $stmt = $pdo->prepare($sql);
        foreach ($params as $k => $v) { $stmt->bindValue($k, $v); }
        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        foreach ($rows as &$row) {
            $row['debt_total'] = (float)($row['debt_total'] ?? 0);
            $row['pending_count'] = (int)($row['pending_count'] ?? 0);
        }
        unset($row);

        return $rows;
    }

    private function calculateDebtTotal(array $rows): float
    {
        $total = 0.0;
        foreach ($rows as $row) {
            $total += (float)($row['debt_total'] ?? 0);
        }
        return $total;
    }

    private function describeDebtFilters(array $filters): string
    {
        $parts = [];
        if ($filters['q'] !== '') {
            $parts[] = 'Búsqueda: "' . $filters['q'] . '"';
        }
        return $parts ? implode(' | ', $parts) : 'Sin filtros';
    }

    private function exportDebtCsv(array $rows, float $total): void
    {
        $filename = 'reporte_deuda_' . date('Ymd') . '.csv';
        header('Content-Type: text/csv; charset=UTF-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        echo "\xEF\xBB\xBF";
        $out = fopen('php://output', 'w');
        fputcsv($out, ['Cliente', 'Nº Documento', 'Dirección', 'Nº facturas pendientes', 'Deuda total acumulada']);
        foreach ($rows as $row) {
            fputcsv($out, [
                (string)($row['name'] ?? ''),
                (string)($row['document_number'] ?? ''),
                (string)($row['address'] ?? ''),
                (int)($row['pending_count'] ?? 0),
                number_format((float)($row['debt_total'] ?? 0), 2, '.', ''),
            ]);
        }
        fputcsv($out, ['TOTAL GENERAL', '', '', '', number_format($total, 2, '.', '')]);
        fclose($out);
    }

    private function renderDebtPrint(array $rows, float $total, array $filters): void
    {
        $filtersLabel = $this->describeDebtFilters($filters);
        ob_start();
        ?>
        <!doctype html>
        <html lang="es">
        <head>
            <meta charset="utf-8">
            <title>Reporte de Deuda por Cliente</title>
            <meta name="viewport" content="width=device-width, initial-scale=1">
            <style>
                body { font-family: -apple-system, Segoe UI, Roboto, Helvetica, Arial, sans-serif; margin: 24px; }
                h1 { margin: 0 0 8px 0; font-size: 22px; }
                .muted { color: #6c757d; font-size: 12px; margin-bottom: 12px; }
                table { width: 100%; border-collapse: collapse; }
                th, td { border: 1px solid #dee2e6; padding: 8px 10px; font-size: 13px; }
                thead th { background: #f8f9fa; text-align: left; }
                tfoot td { font-weight: 700; }
                .text-end { text-align: right; }
            </style>
        </head>
        <body onload="window.print()">
            <h1>Reporte de Deuda por Cliente</h1>
            <div class="muted">Generado el <?= htmlspecialchars(date('d/m/Y H:i')) ?> · <?= htmlspecialchars($filtersLabel) ?></div>
            <table>
                <thead>
                    <tr>
                        <th>Cliente</th>
                        <th>Nº Documento</th>
                        <th>Dirección</th>
                        <th>Nº facturas pendientes</th>
                        <th class="text-end">Deuda total acumulada</th>
                    </tr>
                </thead>
                <tbody>
                <?php if (!empty($rows)): foreach ($rows as $row): ?>
                    <tr>
                        <td><?= htmlspecialchars((string)($row['name'] ?? '')) ?></td>
                        <td><?= htmlspecialchars((string)($row['document_number'] ?? '')) ?></td>
                        <td><?= htmlspecialchars((string)($row['address'] ?? '')) ?></td>
                        <td><?= (int)($row['pending_count'] ?? 0) ?></td>
                        <td class="text-end"><?= format_currency($row['debt_total'] ?? 0) ?></td>
                    </tr>
                <?php endforeach; else: ?>
                    <tr><td colspan="5" style="text-align:center;color:#6c757d;padding:16px">No existen clientes con deuda.</td></tr>
                <?php endif; ?>
                </tbody>
                <tfoot>
                    <tr>
                        <td colspan="4" class="text-end">TOTAL GENERAL</td>
                        <td class="text-end"><?= format_currency($total) ?></td>
                    </tr>
                </tfoot>
            </table>
        </body>
        </html>
        <?php
        $html = ob_get_clean();
        header('Content-Type: text/html; charset=UTF-8');
        echo $html;
    }

    private function renderMorososPdfHtml(array $rows, float $totalOverdue, int $countMorosos, array $filters): string
    {
        $generatedAt = date('d/m/Y H:i');
        $filtersDesc = $this->describeMorososFilters($filters);

        $systemData = SystemData::get();
        $committeeName = trim((string)($systemData['committee_name'] ?? ''));
        if ($committeeName === '') {
            $committeeName = 'Comité de Agua Potable y Saneamiento';
        }
        $providerReg = trim((string)($systemData['provider_registration_number'] ?? ''));
        $ruc = trim((string)($systemData['ruc_number'] ?? ''));
        $municipality = trim((string)($systemData['municipality'] ?? ''));
        $department = trim((string)($systemData['department'] ?? ''));
        $physicalAddress = trim((string)($systemData['physical_address'] ?? ''));
        $phone = trim((string)($systemData['phone'] ?? ''));

        if ($physicalAddress !== '') {
            $maxAddr = 110;
            if (mb_strlen($physicalAddress) > $maxAddr) {
                $physicalAddress = rtrim(mb_substr($physicalAddress, 0, $maxAddr)) . '...';
            }
        }

        $locationLine = trim(implode(' - ', array_filter([$municipality, $department])));
        $regRucLine = trim(implode(' | ', array_filter([
            $providerReg !== '' ? ('Reg. prestador: ' . $providerReg) : '',
            $ruc !== '' ? ('RUC: ' . $ruc) : '',
        ])));
        $contactLine = trim(implode(' | ', array_filter([
            $physicalAddress !== '' ? ('Dir.: ' . $physicalAddress) : '',
            $phone !== '' ? ('Tel.: ' . $phone) : '',
        ])));

        $logoDataUri = null;
        $logoRel = trim((string)($systemData['logo_path'] ?? ''));
        if ($logoRel !== '') {
            $logoAbs = dirname(__DIR__) . '/public/' . ltrim($logoRel, '/');
            if (is_file($logoAbs)) {
                $ext = strtolower(pathinfo($logoAbs, PATHINFO_EXTENSION));
                $mimeMap = [
                    'png' => 'image/png',
                    'jpg' => 'image/jpeg',
                    'jpeg' => 'image/jpeg',
                    'webp' => 'image/webp',
                    'gif' => 'image/gif',
                ];
                $mime = $mimeMap[$ext] ?? 'application/octet-stream';
                $bin = @file_get_contents($logoAbs);
                if ($bin !== false) {
                    $logoDataUri = 'data:' . $mime . ';base64,' . base64_encode($bin);
                }
            }
        }

        ob_start();
        ?>
        <!doctype html>
        <html lang="es">
        <head>
            <meta charset="utf-8">
            <title>Reporte de Morosos</title>
            <style>
                * { box-sizing: border-box; }
                body { font-family: Arial, Helvetica, sans-serif; font-size: 10px; color:#222; margin: 12px; }
                .membrete { border-bottom: 1px solid #ddd; padding-bottom: 6px; margin-bottom: 8px; }
                .membrete-table { width: 100%; border-collapse: collapse; table-layout: auto; }
                .membrete-logo { width: 64px; vertical-align: top; padding-right: 8px; }
                .membrete-logo img { width: 64px; height: 64px; object-fit: contain; }
                .membrete-info { vertical-align: top; }
                .membrete-right { width: 280px; vertical-align: top; text-align: right; }
                .membrete-title { font-size: 12px; font-weight: bold; margin: 0; line-height: 1.2; }
                .membrete-line { font-size: 9px; margin: 0; line-height: 1.2; color: #333; }
                .membrete-meta { font-size: 9px; color: #555; word-break: break-word; }
                table.report { width:100%; border-collapse: collapse; table-layout: fixed; }
                table.report th, table.report td { border: 1px solid #dee2e6; padding: 6px 8px; text-align:left; font-size: 9px; vertical-align: top; word-wrap: break-word; }
                table.report thead th { background:#f8f9fa; }
                .text-end { text-align: right; }
                .text-center { text-align: center; }
            </style>
        </head>
        <body>
            <div class="membrete">
                <table class="membrete-table">
                    <tr>
                        <td class="membrete-logo">
                            <?php if (!empty($logoDataUri)): ?>
                                <img src="<?= htmlspecialchars($logoDataUri) ?>" alt="Logo">
                            <?php endif; ?>
                        </td>
                        <td class="membrete-info">
                            <p class="membrete-title"><?= htmlspecialchars($committeeName) ?></p>
                            <?php if ($regRucLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($regRucLine) ?></p>
                            <?php endif; ?>
                            <?php if ($locationLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($locationLine) ?></p>
                            <?php endif; ?>
                            <?php if ($contactLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($contactLine) ?></p>
                            <?php endif; ?>
                        </td>
                        <td class="membrete-right">
                            <p class="membrete-title">Morosos</p>
                            <div class="membrete-meta"><strong>Total morosos:</strong> <?= (int)$countMorosos ?></div>
                            <div class="membrete-meta"><strong>Total deuda vencida:</strong> <?= format_currency($totalOverdue) ?></div>
                            <div class="membrete-meta"><strong>Generado:</strong> <?= htmlspecialchars($generatedAt) ?></div>
                            <div class="membrete-meta"><strong>Filtro:</strong> <?= htmlspecialchars($filtersDesc) ?></div>
                        </td>
                    </tr>
                </table>
            </div>

            <table class="report">
                <thead>
                    <tr>
                        <th>Cliente</th>
                        <th style="width:18%">Nº Documento</th>
                        <th>Dirección</th>
                        <th class="text-center" style="width:14%">Facturas vencidas</th>
                        <th class="text-center" style="width:10%">Días mora</th>
                        <th class="text-end" style="width:16%">Deuda vencida</th>
                    </tr>
                </thead>
                <tbody>
                <?php if (!empty($rows)): foreach ($rows as $row): ?>
                    <tr>
                        <td><?= htmlspecialchars((string)($row['name'] ?? '')) ?></td>
                        <td><?= htmlspecialchars((string)($row['document_number'] ?? '')) ?></td>
                        <td><?= htmlspecialchars((string)($row['address'] ?? '')) ?></td>
                        <td class="text-center"><?= (int)($row['overdue_count'] ?? 0) ?></td>
                        <td class="text-center"><?= (int)($row['days_overdue'] ?? 0) ?></td>
                        <td class="text-end"><?= format_currency($row['overdue_total'] ?? 0) ?></td>
                    </tr>
                <?php endforeach; else: ?>
                    <tr><td colspan="6" class="text-center" style="color:#6c757d; padding: 12px;">No existen clientes morosos actualmente.</td></tr>
                <?php endif; ?>
                </tbody>
                <tfoot>
                    <tr>
                        <th colspan="5" class="text-end">TOTAL DEUDA VENCIDA</th>
                        <th class="text-end"><?= format_currency($totalOverdue) ?></th>
                    </tr>
                </tfoot>
            </table>
        </body>
        </html>
        <?php
        return (string)ob_get_clean();
    }

    private function renderDebtPdfHtml(array $rows, float $total, array $filters): string
    {
        $generatedAt = date('d/m/Y H:i');
        $filtersDesc = $this->describeDebtFilters($filters);

        $systemData = SystemData::get();
        $committeeName = trim((string)($systemData['committee_name'] ?? ''));
        if ($committeeName === '') {
            $committeeName = 'Comité de Agua Potable y Saneamiento';
        }
        $providerReg = trim((string)($systemData['provider_registration_number'] ?? ''));
        $ruc = trim((string)($systemData['ruc_number'] ?? ''));
        $municipality = trim((string)($systemData['municipality'] ?? ''));
        $department = trim((string)($systemData['department'] ?? ''));
        $physicalAddress = trim((string)($systemData['physical_address'] ?? ''));
        $phone = trim((string)($systemData['phone'] ?? ''));

        if ($physicalAddress !== '') {
            $maxAddr = 110;
            if (mb_strlen($physicalAddress) > $maxAddr) {
                $physicalAddress = rtrim(mb_substr($physicalAddress, 0, $maxAddr)) . '...';
            }
        }

        $locationLine = trim(implode(' - ', array_filter([$municipality, $department])));
        $regRucLine = trim(implode(' | ', array_filter([
            $providerReg !== '' ? ('Reg. prestador: ' . $providerReg) : '',
            $ruc !== '' ? ('RUC: ' . $ruc) : '',
        ])));
        $contactLine = trim(implode(' | ', array_filter([
            $physicalAddress !== '' ? ('Dir.: ' . $physicalAddress) : '',
            $phone !== '' ? ('Tel.: ' . $phone) : '',
        ])));

        $logoDataUri = null;
        $logoRel = trim((string)($systemData['logo_path'] ?? ''));
        if ($logoRel !== '') {
            $logoAbs = dirname(__DIR__) . '/public/' . ltrim($logoRel, '/');
            if (is_file($logoAbs)) {
                $ext = strtolower(pathinfo($logoAbs, PATHINFO_EXTENSION));
                $mimeMap = [
                    'png' => 'image/png',
                    'jpg' => 'image/jpeg',
                    'jpeg' => 'image/jpeg',
                    'webp' => 'image/webp',
                    'gif' => 'image/gif',
                ];
                $mime = $mimeMap[$ext] ?? 'application/octet-stream';
                $bin = @file_get_contents($logoAbs);
                if ($bin !== false) {
                    $logoDataUri = 'data:' . $mime . ';base64,' . base64_encode($bin);
                }
            }
        }

        ob_start();
        ?>
        <!doctype html>
        <html lang="es">
        <head>
            <meta charset="utf-8">
            <title>Reporte de Deuda por Cliente</title>
            <style>
                * { box-sizing: border-box; }
                body { font-family: Arial, Helvetica, sans-serif; font-size: 10px; color:#222; margin: 12px; }
                .membrete { border-bottom: 1px solid #ddd; padding-bottom: 6px; margin-bottom: 8px; }
                .membrete-table { width: 100%; border-collapse: collapse; table-layout: auto; }
                .membrete-logo { width: 64px; vertical-align: top; padding-right: 8px; }
                .membrete-logo img { width: 64px; height: 64px; object-fit: contain; }
                .membrete-info { vertical-align: top; }
                .membrete-right { width: 280px; vertical-align: top; text-align: right; }
                .membrete-title { font-size: 12px; font-weight: bold; margin: 0; line-height: 1.2; }
                .membrete-line { font-size: 9px; margin: 0; line-height: 1.2; color: #333; }
                .membrete-meta { font-size: 9px; color: #555; word-break: break-word; }
                table.report { width:100%; border-collapse: collapse; table-layout: fixed; }
                table.report th, table.report td { border: 1px solid #dee2e6; padding: 6px 8px; text-align:left; font-size: 9px; vertical-align: top; word-wrap: break-word; }
                table.report thead th { background:#f8f9fa; }
                .text-end { text-align: right; }
                .text-center { text-align: center; }
            </style>
        </head>
        <body>
            <div class="membrete">
                <table class="membrete-table">
                    <tr>
                        <td class="membrete-logo">
                            <?php if (!empty($logoDataUri)): ?>
                                <img src="<?= htmlspecialchars($logoDataUri) ?>" alt="Logo">
                            <?php endif; ?>
                        </td>
                        <td class="membrete-info">
                            <p class="membrete-title"><?= htmlspecialchars($committeeName) ?></p>
                            <?php if ($regRucLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($regRucLine) ?></p>
                            <?php endif; ?>
                            <?php if ($locationLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($locationLine) ?></p>
                            <?php endif; ?>
                            <?php if ($contactLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($contactLine) ?></p>
                            <?php endif; ?>
                        </td>
                        <td class="membrete-right">
                            <p class="membrete-title">Deuda por cliente</p>
                            <div class="membrete-meta"><strong>Total clientes:</strong> <?= count($rows) ?></div>
                            <div class="membrete-meta"><strong>Total deuda:</strong> <?= format_currency($total) ?></div>
                            <div class="membrete-meta"><strong>Generado:</strong> <?= htmlspecialchars($generatedAt) ?></div>
                            <div class="membrete-meta"><strong>Filtro:</strong> <?= htmlspecialchars($filtersDesc) ?></div>
                        </td>
                    </tr>
                </table>
            </div>

            <table class="report">
                <thead>
                    <tr>
                        <th>Cliente</th>
                        <th style="width:18%">Nº Documento</th>
                        <th>Dirección</th>
                        <th class="text-center" style="width:16%">Nº facturas pendientes</th>
                        <th class="text-end" style="width:16%">Deuda total acumulada</th>
                    </tr>
                </thead>
                <tbody>
                <?php if (!empty($rows)): foreach ($rows as $row): ?>
                    <tr>
                        <td><?= htmlspecialchars((string)($row['name'] ?? '')) ?></td>
                        <td><?= htmlspecialchars((string)($row['document_number'] ?? '')) ?></td>
                        <td><?= htmlspecialchars((string)($row['address'] ?? '')) ?></td>
                        <td class="text-center"><?= (int)($row['pending_count'] ?? 0) ?></td>
                        <td class="text-end"><?= format_currency($row['debt_total'] ?? 0) ?></td>
                    </tr>
                <?php endforeach; else: ?>
                    <tr><td colspan="5" class="text-center" style="color:#6c757d; padding: 12px;">No existen clientes con deuda.</td></tr>
                <?php endif; ?>
                </tbody>
                <tfoot>
                    <tr>
                        <th colspan="4" class="text-end">TOTAL GENERAL</th>
                        <th class="text-end"><?= format_currency($total) ?></th>
                    </tr>
                </tfoot>
            </table>
        </body>
        </html>
        <?php
        return (string)ob_get_clean();
    }

    private function exportDebtExcel(array $rows, float $total, array $filters): void
    {
        $autoload = __DIR__ . '/../vendor/autoload.php';
        if (is_file($autoload)) { @require_once $autoload; }
        if (!class_exists(Spreadsheet::class)) {
            http_response_code(500);
            echo 'PhpSpreadsheet no está disponible.';
            return;
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Deuda por cliente');

        $headerColor = '173e62';
        $row = 1;
        $sheet->mergeCells("A{$row}:E{$row}");
        $sheet->setCellValue("A{$row}", 'SISCAPS - Reporte de Deuda por Cliente');
        $sheet->getStyle("A{$row}")->getFont()->setBold(true)->setSize(15);
        $sheet->getStyle("A{$row}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $row++;
        $sheet->mergeCells("A{$row}:E{$row}");
        $sheet->setCellValue("A{$row}", 'Generado: ' . date('d/m/Y H:i'));

        $row++;
        $sheet->mergeCells("A{$row}:E{$row}");
        $sheet->setCellValue("A{$row}", 'Filtros: ' . $this->describeDebtFilters($filters));

        $row++;
        $sheet->mergeCells("A{$row}:E{$row}");
        $sheet->setCellValue("A{$row}", 'Total general: ' . format_currency($total));

        $row += 2;
        $headerRow = $row;
        $headers = [
            'Cliente',
            'Nº Documento',
            'Dirección',
            'Nº facturas pendientes',
            'Deuda total acumulada',
        ];
        $sheet->fromArray($headers, null, "A{$headerRow}");
        $sheet->getStyle("A{$headerRow}:E{$headerRow}")->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => strtoupper($headerColor)],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
            'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
        ]);

        $row = $headerRow + 1;
        foreach ($rows as $data) {
            $sheet->fromArray([
                $data['name'] ?? '',
                $data['document_number'] ?? '',
                $data['address'] ?? '',
                (int)($data['pending_count'] ?? 0),
                (float)($data['debt_total'] ?? 0),
            ], null, "A{$row}");
            $row++;
        }

        $dataEndRow = $row - 1;
        if ($dataEndRow >= $headerRow + 1) {
            $sheet->getStyle("A" . ($headerRow + 1) . ":E{$dataEndRow}")->applyFromArray([
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_HAIR]],
                'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
            ]);
            $sheet->getStyle("D" . ($headerRow + 1) . ":D{$dataEndRow}")
                ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER);
            $sheet->getStyle("E" . ($headerRow + 1) . ":E{$dataEndRow}")
                ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
            $sheet->setAutoFilter("A{$headerRow}:E{$dataEndRow}");
        }

        $sheet->mergeCells("A{$row}:C{$row}");
        $sheet->setCellValue("A{$row}", 'Total general');
        $sheet->setCellValue("E{$row}", $total);
        $sheet->getStyle("A{$row}:E{$row}")->applyFromArray([
            'font' => ['bold' => true],
            'borders' => ['top' => ['borderStyle' => Border::BORDER_THIN]],
        ]);
        $sheet->getStyle("E{$row}")
            ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $row++;

        foreach (range('A', 'E') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }
        $sheet->freezePane('A' . ($headerRow + 1));

        $filename = 'Reporte_Deuda_' . date('Ymd_His') . '.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }
    public function recaudacion()
    {
        $filters = $this->collectRecaudacionFilters();
        $fromDate = $filters['date_from'];
        $toDate = $filters['date_to'];
        $group = $filters['group'];

        $rows = $this->fetchRecaudacionRows($filters);

        if (isset($_GET['export']) && (int)$_GET['export'] === 1) {
            $this->exportRecaudacionCsv($rows, $fromDate, $toDate);
            return;
        }

        [$summaryTotal, $count, $byMethod, $byDay, $byMonth] = $this->summarizeRecaudacion($rows);

        if ($group === 'month') {
            ksort($byMonth);
            $seriesLabels = array_keys($byMonth);
            $seriesValues = array_values($byMonth);
        } else {
            ksort($byDay);
            $seriesLabels = array_keys($byDay);
            $seriesValues = array_values($byDay);
        }

        arsort($byMethod);

        require __DIR__ . '/../views/reportes/recaudacion.php';
    }

    public function recaudacionExportExcel(): void
    {
        $filters = $this->collectRecaudacionFilters();
        $rows = $this->fetchRecaudacionRows($filters);
        [$summaryTotal, $count, $byMethod] = $this->summarizeRecaudacion($rows);

        $autoload = __DIR__ . '/../vendor/autoload.php';
        if (is_file($autoload)) { @require_once $autoload; }
        if (!class_exists(Spreadsheet::class)) {
            http_response_code(500);
            echo 'PhpSpreadsheet no está disponible.';
            return;
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Recaudación');

        $headerColor = '173e62';
        $row = 1;
        $sheet->mergeCells("A{$row}:H{$row}");
        $sheet->setCellValue("A{$row}", 'SISCAPS - Reporte de Recaudación');
        $sheet->getStyle("A{$row}")->getFont()->setBold(true)->setSize(15);
        $sheet->getStyle("A{$row}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $row++;
        $sheet->mergeCells("A{$row}:H{$row}");
        $sheet->setCellValue("A{$row}", 'Generado: ' . date('d/m/Y H:i'));

        $row++;
        $sheet->mergeCells("A{$row}:H{$row}");
        $sheet->setCellValue("A{$row}", 'Filtros: ' . $this->describeRecaudacionFilters($filters));

        $row++;
        $sheet->mergeCells("A{$row}:H{$row}");
        $sheet->setCellValue("A{$row}", sprintf(
            'Total recaudado: %s | Nº de cobros: %d | Promedio: %s',
            format_currency($summaryTotal),
            $count,
            format_currency($count > 0 ? $summaryTotal / max(1, $count) : 0)
        ));

        $row += 2;
        $headerRow = $row;
        $headers = ['Fecha', 'Recibo', 'Factura', 'Cliente', 'Método', 'Monto', 'Cajero', 'Notas'];
        $sheet->fromArray($headers, null, "A{$headerRow}");
        $sheet->getStyle("A{$headerRow}:H{$headerRow}")->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => strtoupper($headerColor)],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
            'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
        ]);

        $row = $headerRow + 1;
        foreach ($rows as $payment) {
            $sheet->fromArray([
                format_datetime($payment['payment_date'] ?? ''),
                $payment['receipt_number'] ?? ('#' . ($payment['id'] ?? '')),
                $payment['invoice_number'] ?? '-',
                $payment['customer_name'] ?? '',
                $payment['method'] ?? '',
                (float)($payment['amount'] ?? 0),
                $payment['cashier_name'] ?? '',
                $payment['note'] ?? '',
            ], null, "A{$row}");
            $row++;
        }

        $dataEndRow = $row - 1;
        if ($dataEndRow >= $headerRow + 1) {
            $sheet->getStyle("A" . ($headerRow + 1) . ":H{$dataEndRow}")->applyFromArray([
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_HAIR]],
                'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
            ]);
            $sheet->getStyle("F" . ($headerRow + 1) . ":F{$dataEndRow}")
                ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
            $sheet->setAutoFilter("A{$headerRow}:H{$dataEndRow}");
        }

        $sheet->mergeCells("A{$row}:E{$row}");
        $sheet->setCellValue("A{$row}", 'Total recaudado');
        $sheet->setCellValue("F{$row}", $summaryTotal);
        $sheet->getStyle("A{$row}:H{$row}")->applyFromArray([
            'font' => ['bold' => true],
            'borders' => ['top' => ['borderStyle' => Border::BORDER_THIN]],
        ]);
        $sheet->getStyle("F{$row}")
            ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $row += 2;

        if ($byMethod) {
            $sheet->setCellValue("A{$row}", 'Resumen por método');
            $sheet->getStyle("A{$row}")->getFont()->setBold(true);
            $row++;
            $sheet->fromArray(['Método', 'Monto', 'Participación'], null, "A{$row}");
            $sheet->getStyle("A{$row}:C{$row}")->applyFromArray([
                'font' => ['bold' => true],
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
                'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
            ]);
            $row++;
            foreach ($byMethod as $method => $amount) {
                $sheet->fromArray([
                    $method,
                    (float)$amount,
                    $summaryTotal > 0 ? round(($amount * 100) / $summaryTotal, 1) . '%' : '0%',
                ], null, "A{$row}");
                $row++;
            }
            $sheet->getStyle("B" . ($row - count($byMethod)) . ":B" . ($row - 1))
                ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
            $sheet->getStyle("A" . ($row - count($byMethod)) . ":C" . ($row - 1))->applyFromArray([
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_HAIR]],
            ]);
        }

        foreach (range('A', 'H') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }
        $sheet->freezePane('A' . ($headerRow + 1));

        $filename = 'Recaudacion_' . date('Ymd_His') . '.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        IOFactory::createWriter($spreadsheet, 'Xlsx')->save('php://output');
        exit;
    }

    private function collectRecaudacionFilters(): array
    {
        $today = date('Y-m-d');
        $firstOfMonth = date('Y-m-01');
        $grpParam = $_GET['group'] ?? 'day';
        return [
            'date_from' => $_GET['date_from'] ?? $firstOfMonth,
            'date_to' => $_GET['date_to'] ?? $today,
            'group' => in_array($grpParam, ['day','month'], true) ? $grpParam : 'day',
        ];
    }

    private function fetchRecaudacionRows(array $filters): array
    {
        return Payment::getPaymentsByDateRange($filters['date_from'], $filters['date_to']);
    }

    /**
     * @return array{0:float,1:int,2:array,3:array,4:array}
     */
    private function summarizeRecaudacion(array $rows): array
    {
        $summaryTotal = 0.0; $count = 0;
        $byMethod = []; $byDay = []; $byMonth = [];
        foreach ($rows as $r) {
            $amt = (float)($r['amount'] ?? 0);
            $summaryTotal += $amt; $count++;
            $m = $r['method'] ?? 'Otro';
            $byMethod[$m] = ($byMethod[$m] ?? 0) + $amt;
            $d = substr((string)$r['payment_date'], 0, 10);
            $byDay[$d] = ($byDay[$d] ?? 0) + $amt;
            $ym = substr($d, 0, 7);
            $byMonth[$ym] = ($byMonth[$ym] ?? 0) + $amt;
        }
        return [$summaryTotal, $count, $byMethod, $byDay, $byMonth];
    }

    private function describeRecaudacionFilters(array $filters): string
    {
        return sprintf(
            'Rango: %s a %s | Agrupación: %s',
            $filters['date_from'] ?? '',
            $filters['date_to'] ?? '',
            $filters['group'] === 'month' ? 'Mensual' : 'Diaria'
        );
    }

    private function collectFlujoCajaFilters(): array
    {
        $today = date('Y-m-d');
        $firstOfMonth = date('Y-m-01');
        $grpParam = $_GET['group'] ?? 'day';
        return [
            'date_from' => $_GET['date_from'] ?? $firstOfMonth,
            'date_to' => $_GET['date_to'] ?? $today,
            'group' => in_array($grpParam, ['day','month'], true) ? $grpParam : 'day',
        ];
    }

    private function buildFlujoCajaContext(array $filters): array
    {
        $fromDate = $filters['date_from'];
        $toDate = $filters['date_to'];
        $group = $filters['group'];

        $rowsIn = Payment::getPaymentsByDateRange($fromDate, $toDate);
        $rowsOut = Expense::getExpensesByDateRange($fromDate, $toDate);

        $initialBalance = 0.0;
        try {
            $pdo = (new Database())->getConnection();
            $cut = $fromDate . ' 00:00:00';
            $qIn = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM payments WHERE payment_date < :cut");
            $qIn->execute([':cut' => $cut]);
            $sumInBefore = (float)$qIn->fetchColumn();
            $qOut = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM expenses WHERE expense_date < :cut");
            $qOut->execute([':cut' => $cut]);
            $sumOutBefore = (float)$qOut->fetchColumn();
            $initialBalance = $sumInBefore - $sumOutBefore;
        } catch (Throwable $e) {
            $initialBalance = 0.0;
        }

        $accountsReceivable = 0.0;
        $debtorsCount = 0;
        $openInvoicesCount = 0;
        try {
            $pdo = $pdo ?? (new Database())->getConnection();
            $sqlCxC = "SELECT
                          SUM(GREATEST(i.total - COALESCE(p.paid,0), 0)) AS receivable,
                          COUNT(DISTINCT CASE WHEN (i.total - COALESCE(p.paid,0)) > 0 THEN i.customer_id END) AS debtors,
                          SUM(CASE WHEN (i.total - COALESCE(p.paid,0)) > 0 THEN 1 ELSE 0 END) AS open_invoices
                        FROM invoices i
                        LEFT JOIN (
                          SELECT invoice_id, SUM(amount) AS paid
                          FROM payments
                          WHERE invoice_id IS NOT NULL
                          GROUP BY invoice_id
                        ) p ON p.invoice_id = i.id
                        WHERE i.status IN ('Pendiente','Parcial','Vencida')";
            $r = $pdo->query($sqlCxC)->fetch(PDO::FETCH_ASSOC) ?: [];
            $accountsReceivable = (float)($r['receivable'] ?? 0);
            $debtorsCount = (int)($r['debtors'] ?? 0);
            $openInvoicesCount = (int)($r['open_invoices'] ?? 0);
        } catch (Throwable $e) {
            $accountsReceivable = 0.0;
            $debtorsCount = 0;
            $openInvoicesCount = 0;
        }

        $today = date('Y-m-d');
        $next7 = date('Y-m-d', strtotime('+7 days'));
        $endMonth = date('Y-m-t');
        $payablesNext7 = 0.0;
        $payablesMonth = 0.0;
        try {
            $pdo = $pdo ?? (new Database())->getConnection();
            $stmt7 = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM expenses WHERE expense_date BETWEEN :a AND :b");
            $stmt7->execute([':a' => $today . ' 00:00:00', ':b' => $next7 . ' 23:59:59']);
            $payablesNext7 = (float)$stmt7->fetchColumn();

            $stmtM = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM expenses WHERE expense_date BETWEEN :a AND :b");
            $stmtM->execute([':a' => $today . ' 00:00:00', ':b' => $endMonth . ' 23:59:59']);
            $payablesMonth = (float)$stmtM->fetchColumn();
        } catch (Throwable $e) {
            $payablesNext7 = 0.0;
            $payablesMonth = 0.0;
        }

        $totalIn = 0.0;
        $totalOut = 0.0;
        $countIn = 0;
        $countOut = 0;
        $byDayIn = [];
        $byDayOut = [];
        $byMonthIn = [];
        $byMonthOut = [];
        foreach ($rowsIn as $r) {
            $amt = (float)($r['amount'] ?? 0);
            $totalIn += $amt;
            $countIn++;
            $d = substr((string)$r['payment_date'], 0, 10);
            $ym = substr($d, 0, 7);
            $byDayIn[$d] = ($byDayIn[$d] ?? 0) + $amt;
            $byMonthIn[$ym] = ($byMonthIn[$ym] ?? 0) + $amt;
        }
        foreach ($rowsOut as $r) {
            $amt = (float)($r['amount'] ?? 0);
            $totalOut += $amt;
            $countOut++;
            $d = substr((string)$r['expense_date'], 0, 10);
            $ym = substr($d, 0, 7);
            $byDayOut[$d] = ($byDayOut[$d] ?? 0) + $amt;
            $byMonthOut[$ym] = ($byMonthOut[$ym] ?? 0) + $amt;
        }

        if ($group === 'month') {
            $keys = array_unique(array_merge(array_keys($byMonthIn), array_keys($byMonthOut)));
            sort($keys);
            $seriesLabels = $keys;
            $seriesIncome = array_map(fn($k) => (float)($byMonthIn[$k] ?? 0), $keys);
            $seriesExpense = array_map(fn($k) => (float)($byMonthOut[$k] ?? 0), $keys);
        } else {
            $keys = array_unique(array_merge(array_keys($byDayIn), array_keys($byDayOut)));
            sort($keys);
            $seriesLabels = $keys;
            $seriesIncome = array_map(fn($k) => (float)($byDayIn[$k] ?? 0), $keys);
            $seriesExpense = array_map(fn($k) => (float)($byDayOut[$k] ?? 0), $keys);
        }

        $ledger = [];
        foreach ($rowsIn as $r) {
            $ledger[] = [
                'date' => (string)($r['payment_date'] ?? ''),
                'type' => 'Ingreso',
                'amount' => (float)($r['amount'] ?? 0),
                'meta' => (string)($r['method'] ?? ''),
                'detail' => 'Recibo ' . ($r['receipt_number'] ?? ('#' . ($r['id'] ?? ''))) . ' — ' . ($r['customer_name'] ?? ''),
                'sign' => 1,
            ];
        }
        foreach ($rowsOut as $r) {
            $ledger[] = [
                'date' => (string)($r['expense_date'] ?? ''),
                'type' => 'Egreso',
                'amount' => (float)($r['amount'] ?? 0),
                'meta' => (string)($r['category_name'] ?? ''),
                'detail' => trim(($r['subcategory_name'] ?? '') . (($r['vendor'] ?? '') !== '' ? (' — ' . $r['vendor']) : '')),
                'sign' => -1,
            ];
        }
        usort($ledger, function ($a, $b) {
            $ta = strtotime($a['date'] ?? '') ?: 0;
            $tb = strtotime($b['date'] ?? '') ?: 0;
            return $ta <=> $tb;
        });

        $net = $totalIn - $totalOut;

        return [
            'rowsIn' => $rowsIn,
            'rowsOut' => $rowsOut,
            'initialBalance' => $initialBalance,
            'accountsReceivable' => $accountsReceivable,
            'debtorsCount' => $debtorsCount,
            'openInvoicesCount' => $openInvoicesCount,
            'payablesNext7' => $payablesNext7,
            'payablesMonth' => $payablesMonth,
            'totalIn' => $totalIn,
            'totalOut' => $totalOut,
            'countIn' => $countIn,
            'countOut' => $countOut,
            'seriesLabels' => $seriesLabels,
            'seriesIncome' => $seriesIncome,
            'seriesExpense' => $seriesExpense,
            'ledger' => $ledger,
            'net' => $net,
        ];
    }

    private function describeFlujoCajaFilters(array $filters): string
    {
        return sprintf(
            'Rango: %s a %s | Agrupación: %s',
            $filters['date_from'] ?? '',
            $filters['date_to'] ?? '',
            $filters['group'] === 'month' ? 'Mensual' : 'Diaria'
        );
    }

    public function flujocaja()
    {
        $filters = $this->collectFlujoCajaFilters();
        $context = $this->buildFlujoCajaContext($filters);

        if (isset($_GET['export']) && $_GET['export'] == '1') {
            $this->exportFlujoCajaCsv($context['rowsIn'], $context['rowsOut'], $filters['date_from'], $filters['date_to']);
            return;
        }

        $fromDate = $filters['date_from'];
        $toDate = $filters['date_to'];
        $group = $filters['group'];

        $rowsIn = $context['rowsIn'];
        $rowsOut = $context['rowsOut'];
        $initialBalance = $context['initialBalance'];
        $accountsReceivable = $context['accountsReceivable'];
        $debtorsCount = $context['debtorsCount'];
        $openInvoicesCount = $context['openInvoicesCount'];
        $payablesNext7 = $context['payablesNext7'];
        $payablesMonth = $context['payablesMonth'];
        $totalIn = $context['totalIn'];
        $totalOut = $context['totalOut'];
        $countIn = $context['countIn'];
        $countOut = $context['countOut'];
        $seriesLabels = $context['seriesLabels'];
        $seriesIncome = $context['seriesIncome'];
        $seriesExpense = $context['seriesExpense'];
        $ledger = $context['ledger'];
        $net = $context['net'];

        require __DIR__ . '/../views/reportes/flujocaja.php';
    }

    public function flujocajaExportExcel(): void
    {
        $filters = $this->collectFlujoCajaFilters();
        $context = $this->buildFlujoCajaContext($filters);
        $this->exportFlujoCajaExcel($filters, $context);
    }

    private function exportFlujoCajaExcel(array $filters, array $context): void
    {
        $autoload = __DIR__ . '/../vendor/autoload.php';
        if (is_file($autoload)) { @require_once $autoload; }
        if (!class_exists(Spreadsheet::class)) {
            http_response_code(500);
            echo 'PhpSpreadsheet no está disponible.';
            return;
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Flujo de Caja');

        $row = 1;
        $sheet->mergeCells("A{$row}:H{$row}");
        $sheet->setCellValue("A{$row}", 'SISCAPS - Reporte de Flujo de Caja');
        $sheet->getStyle("A{$row}")->getFont()->setBold(true)->setSize(15);
        $sheet->getStyle("A{$row}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $row++;
        $sheet->mergeCells("A{$row}:H{$row}");
        $sheet->setCellValue("A{$row}", 'Generado: ' . date('d/m/Y H:i'));

        $row++;
        $sheet->mergeCells("A{$row}:H{$row}");
        $sheet->setCellValue("A{$row}", 'Filtros: ' . $this->describeFlujoCajaFilters($filters));

        $row++;
        $sheet->mergeCells("A{$row}:H{$row}");
        $sheet->setCellValue("A{$row}", sprintf(
            'Ingresos: %s | Egresos: %s | Neto: %s',
            format_currency($context['totalIn']),
            format_currency($context['totalOut']),
            format_currency($context['net'])
        ));

        $row += 2;
        $metrics = [
            ['Saldo inicial', $context['initialBalance']],
            ['Ctas. por cobrar', $context['accountsReceivable']],
            ['Deudores', (int)$context['debtorsCount']],
            ['Facturas abiertas', (int)$context['openInvoicesCount']],
            ['Ctas. por pagar (mes)', $context['payablesMonth']],
            ['Ctas. por pagar (próx. 7 días)', $context['payablesNext7']],
            ['Movimientos ingresos', (int)$context['countIn']],
            ['Movimientos egresos', (int)$context['countOut']],
        ];
        $sheet->fromArray(['Indicador', 'Valor'], null, "A{$row}");
        $sheet->getStyle("A{$row}:B{$row}")->getFont()->setBold(true);
        $row++;
        foreach ($metrics as $metric) {
            $sheet->setCellValue("A{$row}", $metric[0]);
            $sheet->setCellValue("B{$row}", $metric[1]);
            if (is_numeric($metric[1])) {
                $sheet->getStyle("B{$row}")
                    ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
            }
            $row++;
        }

        $row += 1;
        $sheet->setCellValue("A{$row}", 'Libro de Caja');
        $sheet->getStyle("A{$row}")->getFont()->setBold(true)->setSize(13);
        $row++;
        $headerRow = $row;
        $headers = ['Fecha', 'Tipo', 'Detalle', 'Método/Categoría', 'Ingreso', 'Egreso', 'Saldo'];
        $sheet->fromArray($headers, null, "A{$headerRow}");
        $sheet->getStyle("A{$headerRow}:G{$headerRow}")->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => '173E62'],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
            'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
        ]);

        $row = $headerRow + 1;
        $running = (float)$context['initialBalance'];
        $sheet->fromArray([
            format_datetime(($filters['date_from'] ?? '') . ' 00:00:00'),
            'Saldo inicial',
            'Saldo acumulado antes del período',
            '',
            null,
            null,
            $running,
        ], null, "A{$row}");
        $sheet->getStyle("G{$row}")
            ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $row++;

        foreach ($context['ledger'] as $entry) {
            $amount = (float)($entry['amount'] ?? 0);
            $sign = (int)($entry['sign'] ?? 1);
            $running += ($sign >= 0 ? $amount : -$amount);
            $sheet->fromArray([
                format_datetime($entry['date'] ?? ''),
                $entry['type'] ?? '',
                $entry['detail'] ?? '',
                $entry['meta'] ?? '',
                $sign >= 0 ? $amount : null,
                $sign < 0 ? $amount : null,
                $running,
            ], null, "A{$row}");
            $row++;
        }

        $dataEndRow = $row - 1;
        if ($dataEndRow >= $headerRow + 1) {
            $sheet->getStyle("A" . ($headerRow + 1) . ":G{$dataEndRow}")->applyFromArray([
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_HAIR]],
                'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
            ]);
            $sheet->getStyle("E" . ($headerRow + 1) . ":G{$dataEndRow}")
                ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        }

        foreach (range('A', 'G') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        $filename = 'FlujoCaja_' . date('Ymd_His') . '.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        IOFactory::createWriter($spreadsheet, 'Xlsx')->save('php://output');
        exit;
    }

    public function morosos()
    {
        $filters = $this->collectMorososFilters();
        $rows = $this->fetchMorososRows($filters);
        [$totalOverdue, $countMorosos] = $this->summarizeMorosos($rows);

        if (isset($_GET['export']) && $_GET['export'] === 'csv') {
            $this->exportMorososCsv($rows, $totalOverdue, $countMorosos);
            return;
        }

        if (isset($_GET['print']) && $_GET['print'] === '1') {
            $this->renderMorososPrint($rows, $totalOverdue, $countMorosos, $filters);
            return;
        }

        $filtersLabel = $this->describeMorososFilters($filters);
        $q = $filters['q'];

        require __DIR__ . '/../views/reportes/morosos.php';
    }

    public function morososExportExcel(): void
    {
        $filters = $this->collectMorososFilters();
        $rows = $this->fetchMorososRows($filters);
        [$totalOverdue, $countMorosos] = $this->summarizeMorosos($rows);
        $this->exportMorososExcel($rows, $totalOverdue, $countMorosos, $filters);
    }

    public function morososExportPdf(): void
    {
        $filters = $this->collectMorososFilters();
        $rows = $this->fetchMorososRows($filters);
        [$totalOverdue, $countMorosos] = $this->summarizeMorosos($rows);

        $autoload = __DIR__ . '/../vendor/autoload.php';
        if (is_file($autoload)) { @require_once $autoload; }

        $html = $this->renderMorososPdfHtml($rows, $totalOverdue, $countMorosos, $filters);

        if (class_exists('Dompdf\\Dompdf')) {
            $dompdf = new Dompdf\Dompdf();
            $dompdf->loadHtml($html);
            $dompdf->setPaper('A4', 'portrait');
            $dompdf->render();
            $filename = 'Morosos_' . date('Ymd_His') . '.pdf';
            $dompdf->stream($filename, ['Attachment' => true]);
            return;
        }

        header('Content-Type: text/html; charset=UTF-8');
        echo $html;
        echo '<script>window.addEventListener("load",()=>setTimeout(()=>window.print(),200));</script>';
    }

    private function collectMorososFilters(): array
    {
        return [
            'q' => trim((string)($_GET['q'] ?? '')),
        ];
    }

    private function fetchMorososRows(array $filters): array
    {
        $pdo = (new Database())->getConnection();
        $params = [];
        $filterSql = '';
        if ($filters['q'] !== '') {
            $filterSql = " AND (c.name LIKE :q1 OR c.customer_code LIKE :q4 OR c.document_number LIKE :q2 OR c.address LIKE :q3)";
            $params[':q1'] = '%' . $filters['q'] . '%';
            $params[':q2'] = '%' . $filters['q'] . '%';
            $params[':q3'] = '%' . $filters['q'] . '%';
            $params[':q4'] = '%' . $filters['q'] . '%';
        }

        $sql = "SELECT
                    c.id,
                    c.name,
                    c.document_number,
                    c.address,
                    SUM(GREATEST(i.total - (
                        SELECT COALESCE(SUM(p.amount), 0)
                        FROM payments p
                        WHERE p.invoice_id = i.id
                    ), 0)) AS overdue_total,
                    COUNT(CASE WHEN (i.total - (
                        SELECT COALESCE(SUM(p.amount), 0)
                        FROM payments p
                        WHERE p.invoice_id = i.id
                    )) > 0 THEN 1 END) AS overdue_count,
                    MIN(i.due_date) AS min_due_date,
                    DATEDIFF(CURDATE(), MIN(i.due_date)) AS days_overdue
                FROM invoices i
                JOIN customers c ON c.id = i.customer_id
                WHERE i.status IN ('Pendiente','Parcial','Vencida')
                  AND i.due_date < CURDATE()
                  $filterSql
                GROUP BY c.id, c.name, c.document_number, c.address
                HAVING overdue_total > 0
                ORDER BY days_overdue DESC, c.name ASC";

        $stmt = $pdo->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        foreach ($rows as &$row) {
            $row['overdue_total'] = (float)($row['overdue_total'] ?? 0);
            $row['overdue_count'] = (int)($row['overdue_count'] ?? 0);
            $row['days_overdue'] = (int)($row['days_overdue'] ?? 0);
        }
        unset($row);

        return $rows;
    }

    /**
     * @return array{0:float,1:int}
     */
    private function summarizeMorosos(array $rows): array
    {
        $totalOverdue = 0.0;
        $countMorosos = 0;
        foreach ($rows as $row) {
            $totalOverdue += (float)($row['overdue_total'] ?? 0);
            $countMorosos++;
        }
        return [$totalOverdue, $countMorosos];
    }

    private function describeMorososFilters(array $filters): string
    {
        if ($filters['q'] === '') {
            return 'Sin filtros';
        }
        return 'Búsqueda: "' . $filters['q'] . '"';
    }

    private function exportMorososCsv(array $rows, float $totalOverdue, int $countMorosos): void
    {
        $filename = 'reporte_morosos_' . date('Y-m-d') . '.csv';
        header('Content-Type: text/csv; charset=UTF-8');
        header('Content-Disposition: attachment; filename=' . $filename);
        echo "\xEF\xBB\xBF";
        $out = fopen('php://output', 'w');
        fputcsv($out, ['Cliente', 'Nº Documento', 'Dirección', 'Nº facturas vencidas', 'Días de mora', 'Deuda vencida']);
        foreach ($rows as $row) {
            fputcsv($out, [
                (string)($row['name'] ?? ''),
                (string)($row['document_number'] ?? ''),
                (string)($row['address'] ?? ''),
                (int)($row['overdue_count'] ?? 0),
                (int)($row['days_overdue'] ?? 0),
                number_format((float)($row['overdue_total'] ?? 0), 2, '.', ''),
            ]);
        }
        fputcsv($out, ['TOTAL', '', '', $countMorosos, '', number_format($totalOverdue, 2, '.', '')]);
        fclose($out);
    }

    private function renderMorososPrint(array $rows, float $totalOverdue, int $countMorosos, array $filters): void
    {
        $filtersLabel = $this->describeMorososFilters($filters);
        ob_start();
        ?>
        <!doctype html>
        <html lang="es">
        <head>
            <meta charset="utf-8">
            <title>Reporte de Morosos</title>
            <meta name="viewport" content="width=device-width, initial-scale=1">
            <style>
                body { font-family: -apple-system, Segoe UI, Roboto, Helvetica, Arial, sans-serif; margin: 24px; }
                h1 { margin: 0 0 8px 0; font-size: 22px; }
                .muted { color: #6c757d; font-size: 12px; margin-bottom: 12px; }
                table { width: 100%; border-collapse: collapse; }
                th, td { border: 1px solid #dee2e6; padding: 8px 10px; font-size: 13px; }
                thead th { background: #f8f9fa; text-align: left; }
                tfoot td { font-weight: 700; }
                .text-end { text-align: right; }
                .row-warn { background: #fff3cd; }
                .row-danger { background: #f8d7da; }
            </style>
        </head>
        <body onload="window.print()">
            <h1>Reporte de Morosos</h1>
            <div class="muted">Generado el <?= htmlspecialchars(date('d/m/Y H:i')) ?> · <?= htmlspecialchars($filtersLabel) ?></div>
            <table>
                <thead>
                    <tr>
                        <th>Cliente</th>
                        <th>Nº Documento</th>
                        <th>Dirección</th>
                        <th>Nº facturas vencidas</th>
                        <th>Días de mora</th>
                        <th class="text-end">Deuda vencida</th>
                    </tr>
                </thead>
                <tbody>
                <?php if (!empty($rows)): foreach ($rows as $row):
                    $days = (int)($row['days_overdue'] ?? 0);
                    $rowClass = ($days > 60) ? 'row-danger' : (($days >= 30) ? 'row-warn' : '');
                ?>
                    <tr class="<?= $rowClass ?>">
                        <td><?= htmlspecialchars((string)($row['name'] ?? '')) ?></td>
                        <td><?= htmlspecialchars((string)($row['document_number'] ?? '')) ?></td>
                        <td><?= htmlspecialchars((string)($row['address'] ?? '')) ?></td>
                        <td><?= (int)($row['overdue_count'] ?? 0) ?></td>
                        <td><?= $days ?></td>
                        <td class="text-end"><?= format_currency($row['overdue_total'] ?? 0) ?></td>
                    </tr>
                <?php endforeach; else: ?>
                    <tr><td colspan="6" style="text-align:center;color:#6c757d;padding:16px">No existen clientes morosos actualmente.</td></tr>
                <?php endif; ?>
                </tbody>
                <tfoot>
                    <tr>
                        <td colspan="3">TOTAL MOROSOS: <?= (int)$countMorosos ?></td>
                        <td colspan="2" class="text-end">TOTAL DEUDA VENCIDA</td>
                        <td class="text-end"><?= format_currency($totalOverdue) ?></td>
                    </tr>
                </tfoot>
            </table>
        </body>
        </html>
        <?php
        $html = ob_get_clean();
        header('Content-Type: text/html; charset=UTF-8');
        echo $html;
    }

    private function exportMorososExcel(array $rows, float $totalOverdue, int $countMorosos, array $filters): void
    {
        $autoload = __DIR__ . '/../vendor/autoload.php';
        if (is_file($autoload)) { @require_once $autoload; }
        if (!class_exists(Spreadsheet::class)) {
            http_response_code(500);
            echo 'PhpSpreadsheet no está disponible.';
            return;
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Morosos');

        $row = 1;
        $sheet->mergeCells("A{$row}:F{$row}");
        $sheet->setCellValue("A{$row}", 'SISCAPS - Reporte de Morosos');
        $sheet->getStyle("A{$row}")->getFont()->setBold(true)->setSize(15);
        $sheet->getStyle("A{$row}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $row++;
        $sheet->mergeCells("A{$row}:F{$row}");
        $sheet->setCellValue("A{$row}", 'Generado: ' . date('d/m/Y H:i'));

        $row++;
        $sheet->mergeCells("A{$row}:F{$row}");
        $sheet->setCellValue("A{$row}", 'Filtros: ' . $this->describeMorososFilters($filters));

        $row++;
        $sheet->mergeCells("A{$row}:F{$row}");
        $sheet->setCellValue("A{$row}", sprintf(
            'Total morosos: %d | Deuda vencida: %s',
            $countMorosos,
            format_currency($totalOverdue)
        ));

        $row += 2;
        $headerRow = $row;
        $headers = ['Cliente', 'Nº Documento', 'Dirección', 'Nº facturas vencidas', 'Días de mora', 'Deuda vencida'];
        $sheet->fromArray($headers, null, "A{$headerRow}");
        $sheet->getStyle("A{$headerRow}:F{$headerRow}")->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => '173E62'],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
            'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
        ]);

        $row = $headerRow + 1;
        foreach ($rows as $r) {
            $sheet->fromArray([
                (string)($r['name'] ?? ''),
                (string)($r['document_number'] ?? ''),
                (string)($r['address'] ?? ''),
                (int)($r['overdue_count'] ?? 0),
                (int)($r['days_overdue'] ?? 0),
                (float)($r['overdue_total'] ?? 0),
            ], null, "A{$row}");
            $row++;
        }

        $dataEndRow = $row - 1;
        if ($dataEndRow >= $headerRow + 1) {
            $sheet->getStyle("A" . ($headerRow + 1) . ":F{$dataEndRow}")->applyFromArray([
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_HAIR]],
            ]);
            $sheet->getStyle("F" . ($headerRow + 1) . ":F{$dataEndRow}")
                ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        }

        foreach (range('A', 'F') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        $filename = 'Morosos_' . date('Ymd_His') . '.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        IOFactory::createWriter($spreadsheet, 'Xlsx')->save('php://output');
        exit;
    }

    public function poblacion(): void
    {
        $data = $this->collectPopulationData();
        $summary = $data['summary'];
        $bySector = $data['bySector'];
        $byStatus = $data['byStatus'];
        $topHouseholds = $data['topHouseholds'];
        $generatedAt = $data['generatedAt'];
        require __DIR__ . '/../views/reportes/poblacion.php';
    }

    public function poblacionExportExcel(): void
    {
        $data = $this->collectPopulationData();

        $autoload = __DIR__ . '/../vendor/autoload.php';
        if (is_file($autoload)) { @require_once $autoload; }
        if (!class_exists(Spreadsheet::class)) {
            http_response_code(500);
            echo 'PhpSpreadsheet no está disponible.';
            return;
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Población');

        $summary = $data['summary'] ?? [];
        $bySector = $data['bySector'] ?? [];
        $byStatus = $data['byStatus'] ?? [];
        $topHouseholds = $data['topHouseholds'] ?? [];

        $row = 1;
        $sheet->mergeCells("A{$row}:E{$row}");
        $sheet->setCellValue("A{$row}", 'SISCAPS - Población Total');
        $sheet->getStyle("A{$row}")->getFont()->setBold(true)->setSize(15);
        $sheet->getStyle("A{$row}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $row++;
        $sheet->mergeCells("A{$row}:E{$row}");
        $sheet->setCellValue("A{$row}", 'Generado: ' . date('d/m/Y H:i'));

        $row += 2;
        $sheet->setCellValue("A{$row}", 'Resumen General');
        $sheet->getStyle("A{$row}")->getFont()->setBold(true)->setSize(13);
        $row++;
        $sheet->fromArray(
            ['Indicador', 'Valor', 'Indicador', 'Valor'],
            null,
            "A{$row}"
        );
        $sheet->getStyle("A{$row}:D{$row}")->getFont()->setBold(true);
        $row++;
        $summaryPairs = [
            ['Total de hogares', format_num($summary['households'] ?? 0, 0)],
            ['Total de integrantes', format_num($summary['totalMembers'] ?? 0, 0)],
            ['Hogares con datos', sprintf('%s (%0.1f%%)', format_num($summary['withMembers'] ?? 0, 0), (float)($summary['withMembersPct'] ?? 0))],
            ['Hogares sin datos', sprintf('%s (%0.1f%%)', format_num($summary['withoutMembers'] ?? 0, 0), (float)($summary['withoutMembersPct'] ?? 0))],
            ['Promedio integrantes (hogares con datos)', number_format((float)($summary['avgMembers'] ?? 0), 1)],
            ['Promedio integrantes (general)', number_format((float)($summary['avgMembersAll'] ?? 0), 1)],
            ['Máximo integrantes en un hogar', format_num($summary['maxMembers'] ?? 0, 0)],
            ['Mínimo integrantes en un hogar', format_num($summary['minMembers'] ?? 0, 0)],
        ];
        foreach ($summaryPairs as $idx => $pair) {
            $sheet->setCellValue("A" . ($row + $idx), $pair[0]);
            $sheet->setCellValue("B" . ($row + $idx), $pair[1]);
            $sheet->setCellValue("C" . ($row + $idx), $summaryPairs[++$idx][0] ?? '');
            $sheet->setCellValue("D" . ($row + $idx), $summaryPairs[$idx][1] ?? '');
        }
        $row += count($summaryPairs) + 1;

        $sections = [
            ['Distribución por Sector', ['Sector', 'Hogares', 'Integrantes', 'Hogares con datos'], $bySector, function ($item) {
                return [
                    (string)($item['label'] ?? ''),
                    (int)($item['households'] ?? 0),
                    (int)($item['total_members'] ?? 0),
                    (int)($item['with_members'] ?? 0),
                ];
            }],
            ['Distribución por Estado', ['Estado', 'Hogares', 'Integrantes'], $byStatus, function ($item) {
                return [
                    (string)($item['label'] ?? ''),
                    (int)($item['households'] ?? 0),
                    (int)($item['total_members'] ?? 0),
                ];
            }],
            ['Hogares con más integrantes', ['ID', 'Código', 'Nombre', 'Sector', 'Integrantes'], $topHouseholds, function ($item) {
                return [
                    (int)($item['id'] ?? 0),
                    (string)($item['customer_code'] ?? ''),
                    (string)($item['name'] ?? ''),
                    (string)($item['sector'] ?? ''),
                    (int)($item['members_count'] ?? 0),
                ];
            }],
        ];

        foreach ($sections as [$title, $headers, $items, $mapper]) {
            $sheet->setCellValue("A{$row}", $title);
            $sheet->getStyle("A{$row}")->getFont()->setBold(true)->setSize(13);
            $row++;
            $sheet->fromArray($headers, null, "A{$row}");
            $lastColumn = chr(ord('A') + count($headers) - 1);
            $sheet->getStyle("A{$row}:{$lastColumn}{$row}")->applyFromArray([
                'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
                'fill' => [
                    'fillType' => Fill::FILL_SOLID,
                    'startColor' => ['rgb' => '173E62'],
                ],
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_CENTER,
                    'vertical' => Alignment::VERTICAL_CENTER,
                ],
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
            ]);
            $row++;
            if (!empty($items)) {
                foreach ($items as $item) {
                    $sheet->fromArray($mapper($item), null, "A{$row}");
                    $row++;
                }
                $dataEndRow = $row - 1;
                $sheet->getStyle("A" . ($dataEndRow - count($items) + 1) . ":{$lastColumn}{$dataEndRow}")->applyFromArray([
                    'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_HAIR]],
                ]);
            } else {
                $sheet->mergeCells("A{$row}:{$lastColumn}{$row}");
                $sheet->setCellValue("A{$row}", 'Sin datos disponibles.');
                $sheet->getStyle("A{$row}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $row++;
            }
            $row++;
        }

        foreach (range('A', 'E') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        $filename = 'Poblacion_Total_' . date('Ymd_His') . '.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        IOFactory::createWriter($spreadsheet, 'Xlsx')->save('php://output');
        exit;
    }

    public function poblacionPdf(): void
    {
        $data = $this->collectPopulationData();
        $autoload = __DIR__ . '/../vendor/autoload.php';
        if (is_file($autoload)) { @require_once $autoload; }
        $html = $this->renderPoblacionPdfHtml($data);
        if (class_exists('Dompdf\\Dompdf')) {
            $dompdf = new Dompdf\Dompdf();
            $dompdf->loadHtml($html);
            $dompdf->setPaper('A4', 'portrait');
            $dompdf->render();
            $dompdf->stream('Poblacion_Total_' . date('Ymd_His') . '.pdf', ['Attachment' => true]);
            return;
        }
        header('Content-Type: text/html; charset=UTF-8');
        echo $html;
        echo '<script>window.addEventListener("load",()=>setTimeout(()=>window.print(),200));</script>';
    }

    public function adminfin(): void
    {
        // Conexión
        $pdo = (new Database())->getConnection();

        // Rango histórico completo
        $today = date('Y-m-d');
        $firstDataDate = $this->resolveFinancialDataStartDate($pdo);

        $rawFrom = $_GET['date_from'] ?? '';
        $rawTo = $_GET['date_to'] ?? '';

        $filterFrom = $this->normalizeDate($rawFrom, $firstDataDate);
        $filterTo = $this->normalizeDate($rawTo, $today);
        if ($filterFrom > $filterTo) {
            [$filterFrom, $filterTo] = [$filterTo, $filterFrom];
        }
        if ($filterFrom < $firstDataDate) {
            $filterFrom = $firstDataDate;
        }
        if ($filterTo > $today) {
            $filterTo = $today;
        }

        $filters = [
            'global_from' => $firstDataDate,
            'global_to' => $today,
            'date_from' => $rawFrom !== '' ? $rawFrom : $filterFrom,
            'date_to' => $rawTo !== '' ? $rawTo : $filterTo,
            'filter_from_computed' => $filterFrom,
            'filter_to_computed' => $filterTo,
            'filter_active' => ($rawFrom !== '' || $rawTo !== ''),
        ];

        $globalData = $this->computeFinancialAggregates($pdo, $firstDataDate . ' 00:00:00', $today . ' 23:59:59');
        $filteredData = $this->computeFinancialAggregates($pdo, $filterFrom . ' 00:00:00', $filterTo . ' 23:59:59');

        // Ingresos/Costos globales para tarjetas y KPIs
        $ingresosGlobal = $globalData['ingresos'];
        $costosGlobal = $globalData['costos'];
        $totalIngresos = array_sum($ingresosGlobal);
        $totalCostos = array_sum($costosGlobal);
        $ahorro = $totalIngresos - $totalCostos;

        // Datos filtrados para tablas y gráficos
        $ingresosFiltrados = $filteredData['ingresos'];
        $costsByType = $filteredData['costsByType'];

        $ingServicio = $ingresosFiltrados['servicio'] ?? 0.0;
        $ingMora = $this->computeMoraIncome($pdo, $filterFrom . ' 00:00:00', $filterTo . ' 23:59:59');
        $ingInscripcion = $ingresosFiltrados['inscripcion'] ?? 0.0;
        $ingReconexion = $ingresosFiltrados['reconexion'] ?? 0.0;
        $ingMulta = $ingresosFiltrados['multa'] ?? 0.0;
        $ingVentas = $ingresosFiltrados['ventas'] ?? 0.0;
        $ingOtros = $ingresosFiltrados['otros'] ?? 0.0;

        // Informe administrativo (no depende del rango: estado actual)
        $totalActive = 0; $debtors = 0; $onTime = 0; $arrearsPct = 0.0;
        try {
            $q1 = $pdo->query("SELECT COUNT(*)
                               FROM customers c
                               WHERE c.status='Activo'
                                 AND EXISTS (SELECT 1 FROM contracts ct WHERE ct.customer_id=c.id)");
            $totalActive = (int)$q1->fetchColumn();

            $q2 = $pdo->query("SELECT COUNT(DISTINCT i.customer_id)
                               FROM invoices i
                               JOIN customers c ON c.id = i.customer_id
                               WHERE c.status='Activo'
                                 AND EXISTS (SELECT 1 FROM contracts ct WHERE ct.customer_id=c.id)
                                 AND i.status IN ('Pendiente','Parcial','Vencida')
                                 AND (i.total - (
                                       SELECT COALESCE(SUM(p.amount),0)
                                       FROM payments p WHERE p.invoice_id = i.id
                                     )) > 0");
            $debtors = (int)$q2->fetchColumn();
            $onTime = max(0, $totalActive - $debtors);
            $arrearsPct = $totalActive > 0 ? ($debtors * 100.0 / $totalActive) : 0.0;
        } catch (Throwable $e) {
            // Mantener valores en 0 si algo falla silenciosamente
        }

        require __DIR__ . '/../views/reportes/adminfin.php';
    }

    public function adminfinExportExcel(): void
    {
        $pdo = (new Database())->getConnection();

        $today = date('Y-m-d');
        $firstDataDate = $this->resolveFinancialDataStartDate($pdo);

        $rawFrom = $_GET['date_from'] ?? '';
        $rawTo = $_GET['date_to'] ?? '';

        $filterFrom = $this->normalizeDate($rawFrom, $firstDataDate);
        $filterTo = $this->normalizeDate($rawTo, $today);
        if ($filterFrom > $filterTo) {
            [$filterFrom, $filterTo] = [$filterTo, $filterFrom];
        }
        if ($filterFrom < $firstDataDate) {
            $filterFrom = $firstDataDate;
        }
        if ($filterTo > $today) {
            $filterTo = $today;
        }

        $filters = [
            'global_from' => $firstDataDate,
            'global_to' => $today,
            'date_from' => $rawFrom !== '' ? $rawFrom : $filterFrom,
            'date_to' => $rawTo !== '' ? $rawTo : $filterTo,
            'filter_from_computed' => $filterFrom,
            'filter_to_computed' => $filterTo,
            'filter_active' => ($rawFrom !== '' || $rawTo !== ''),
        ];

        $globalData = $this->computeFinancialAggregates($pdo, $firstDataDate . ' 00:00:00', $today . ' 23:59:59');
        $filteredData = $this->computeFinancialAggregates($pdo, $filterFrom . ' 00:00:00', $filterTo . ' 23:59:59');

        $ingresosGlobal = $globalData['ingresos'];
        $costosGlobal = $globalData['costos'];
        $totalIngresos = array_sum($ingresosGlobal);
        $totalCostos = array_sum($costosGlobal);
        $ahorro = $totalIngresos - $totalCostos;

        $ingresosFiltrados = $filteredData['ingresos'];
        $costsByType = $filteredData['costsByType'];

        $ingServicio = $ingresosFiltrados['servicio'] ?? 0.0;
        $ingMora = $this->computeMoraIncome($pdo, $filterFrom . ' 00:00:00', $filterTo . ' 23:59:59');
        $ingInscripcion = $ingresosFiltrados['inscripcion'] ?? 0.0;
        $ingReconexion = $ingresosFiltrados['reconexion'] ?? 0.0;
        $ingMulta = $ingresosFiltrados['multa'] ?? 0.0;
        $ingVentas = $ingresosFiltrados['ventas'] ?? 0.0;
        $ingOtros = $ingresosFiltrados['otros'] ?? 0.0;

        $totalIngresosFiltrado = $ingServicio + $ingInscripcion + $ingReconexion + $ingMulta + $ingVentas + $ingOtros;
        $totalCostosFiltrado = array_sum(array_map(fn($info) => (float)($info['total'] ?? 0), $costsByType));

        $totalActive = 0; $debtors = 0; $onTime = 0; $arrearsPct = 0.0;
        try {
            $q1 = $pdo->query("SELECT COUNT(*)
                               FROM customers c
                               WHERE c.status='Activo'
                                 AND EXISTS (SELECT 1 FROM contracts ct WHERE ct.customer_id=c.id)");
            $totalActive = (int)$q1->fetchColumn();

            $q2 = $pdo->query("SELECT COUNT(DISTINCT i.customer_id)
                               FROM invoices i
                               JOIN customers c ON c.id = i.customer_id
                               WHERE c.status='Activo'
                                 AND EXISTS (SELECT 1 FROM contracts ct WHERE ct.customer_id=c.id)
                                 AND i.status IN ('Pendiente','Parcial','Vencida')
                                 AND (i.total - (
                                       SELECT COALESCE(SUM(p.amount),0)
                                       FROM payments p WHERE p.invoice_id = i.id
                                     )) > 0");
            $debtors = (int)$q2->fetchColumn();
            $onTime = max(0, $totalActive - $debtors);
            $arrearsPct = $totalActive > 0 ? ($debtors * 100.0 / $totalActive) : 0.0;
        } catch (Throwable $e) { }

        $autoload = __DIR__ . '/../vendor/autoload.php';
        if (is_file($autoload)) { @require_once $autoload; }
        if (!class_exists(Spreadsheet::class)) {
            http_response_code(500);
            echo 'PhpSpreadsheet no está disponible.';
            return;
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Administrativo-Financiero');

        $row = 1;
        $sheet->mergeCells("A{$row}:H{$row}");
        $sheet->setCellValue("A{$row}", 'SISCAPS - Informe Administrativo-Financiero');
        $sheet->getStyle("A{$row}")->getFont()->setBold(true)->setSize(15);
        $sheet->getStyle("A{$row}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $row++;
        $sheet->mergeCells("A{$row}:H{$row}");
        $sheet->setCellValue("A{$row}", 'Generado: ' . date('d/m/Y H:i'));

        $row++;
        $sheet->mergeCells("A{$row}:H{$row}");
        $sheet->setCellValue("A{$row}", sprintf(
            'Período global: %s a %s | Período filtrado: %s a %s',
            format_date($filters['global_from'] ?? ''),
            format_date($filters['global_to'] ?? ''),
            format_date($filters['filter_from_computed'] ?? ''),
            format_date($filters['filter_to_computed'] ?? '')
        ));

        $row += 2;
        $sheet->setCellValue("A{$row}", 'Resumen Administrativo');
        $sheet->getStyle("A{$row}")->getFont()->setBold(true)->setSize(13);
        $row++;
        $sheet->fromArray([
            ['Indicador', 'Valor'],
            ['Usuarios actuales', (int)($totalActive ?? 0)],
            ['Usuarios al día con sus pagos', (int)($onTime ?? 0)],
            ['Usuarios morosos', (int)($debtors ?? 0)],
            ['Porcentaje de morosidad', number_format((float)($arrearsPct ?? 0), 1) . '%'],
        ], null, "A{$row}");
        $sheet->getStyle("A{$row}:B{$row}")->getFont()->setBold(true);
        $row += 5;

        $sheet->setCellValue("A{$row}", 'Resumen Financiero (Global)');
        $sheet->getStyle("A{$row}")->getFont()->setBold(true)->setSize(13);
        $row++;
        $sheet->fromArray([
            ['Total Ingresos', format_currency($totalIngresos)],
            ['Total Costos', format_currency($totalCostos)],
            ['Ahorro del período', format_currency($ahorro)],
        ], null, "A{$row}");
        $row += 4;

        $sheet->setCellValue("A{$row}", 'Ingresos por categoría (Filtrado)');
        $sheet->getStyle("A{$row}")->getFont()->setBold(true)->setSize(13);
        $row++;
        $sheet->fromArray([
            ['Concepto', 'Monto'],
            ['Cobro de servicio de agua', $ingServicio],
            ['Ingreso Por Mora', $ingMora],
            ['Nuevos servicios / Inscripciones', $ingInscripcion],
            ['Reconexiones', $ingReconexion],
            ['Multas', $ingMulta],
            ['Ventas', $ingVentas],
            ['Otros ingresos', $ingOtros],
            ['TOTAL INGRESOS FILTRADO', $totalIngresosFiltrado],
        ], null, "A{$row}");
        $sheet->getStyle("A{$row}:B{$row}")->getFont()->setBold(true);
        $sheet->getStyle("B" . ($row + 1) . ":B" . ($row + 7))->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $sheet->getStyle("A" . ($row + 8) . ":B" . ($row + 8))->getFont()->setBold(true);
        $row += 9;

        $sheet->setCellValue("A{$row}", 'Costos detallados (Filtrado)');
        $sheet->getStyle("A{$row}")->getFont()->setBold(true)->setSize(13);
        $row++;
        $sheet->fromArray(['Tipo', 'Categoría', 'Subcategoría', 'Monto'], null, "A{$row}");
        $sheet->getStyle("A{$row}:D{$row}")->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => '173E62'],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
            'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
        ]);
        $row++;

        if (!empty($costsByType)) {
            foreach ($costsByType as $type => $info) {
                $info = $info ?? [];
                $cats = $info['categories'] ?? [];
                foreach ($cats as $catName => $catData) {
                    $catData = $catData ?? [];
                    $subs = $catData['subcategories'] ?? [];
                    $sheet->fromArray([
                        (string)($type ?? 'Otro'),
                        (string)($catName ?? 'Sin categoría'),
                        'TOTAL CATEGORÍA',
                        (float)($catData['total'] ?? 0),
                    ], null, "A{$row}");
                    $sheet->getStyle("A{$row}:D{$row}")->getFont()->setBold(true);
                    $row++;
                    foreach ($subs as $subName => $amt) {
                        $sheet->fromArray([
                            '',
                            '',
                            (string)($subName ?? 'Sin subcategoría'),
                            (float)$amt,
                        ], null, "A{$row}");
                        $row++;
                    }
                }
            }
        } else {
            $sheet->mergeCells("A{$row}:D{$row}");
            $sheet->setCellValue("A{$row}", 'Sin costos registrados en el período filtrado.');
            $sheet->getStyle("A{$row}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $row++;
        }

        $sheet->getStyle("D" . ($row - 1))->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);

        foreach (range('A', 'H') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        $filename = 'Administrativo_Financiero_' . date('Ymd_His') . '.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        IOFactory::createWriter($spreadsheet, 'Xlsx')->save('php://output');
        exit;
    }

    public function adminfinPdf(): void
    {
        $pdo = (new Database())->getConnection();

        // Rango histórico completo para exportaciones
        $all = isset($_GET['all']) && (string)$_GET['all'] === '1';
        $rawFrom = $_GET['date_from'] ?? '';
        $rawTo = $_GET['date_to'] ?? '';
        $today = date('Y-m-d');
        $firstDataDate = $this->resolveFinancialDataStartDate($pdo);
        if ($all) {
            $fromDate = $firstDataDate;
            $toDate = $today;
        } else {
            // Si no se proporcionan fechas, usar rango histórico completo
            if ($rawFrom === '' && $rawTo === '') {
                $fromDate = $firstDataDate;
                $toDate = $today;
            } else {
                $fromDate = $this->normalizeDate($rawFrom, $firstDataDate);
                $toDate = $this->normalizeDate($rawTo, $today);
                if ($fromDate > $toDate) {
                    [$fromDate, $toDate] = [$toDate, $fromDate];
                }
                if ($fromDate < $firstDataDate) {
                    $fromDate = $firstDataDate;
                }
                if ($toDate > $today) {
                    $toDate = $today;
                }
            }
        }

        $displayFrom = $rawFrom !== '' ? $rawFrom : $fromDate;
        $displayTo = $rawTo !== '' ? $rawTo : $toDate;

        // Administrativo (snapshot)
        $totalActive = 0; $debtors = 0; $onTime = 0; $arrearsPct = 0.0;
        try {
            $q1 = $pdo->query("SELECT COUNT(*)
                               FROM customers c
                               WHERE c.status='Activo'
                                 AND EXISTS (SELECT 1 FROM contracts ct WHERE ct.customer_id=c.id)");
            $totalActive = (int)$q1->fetchColumn();

            $q2 = $pdo->query("SELECT COUNT(DISTINCT i.customer_id)
                               FROM invoices i
                               JOIN customers c ON c.id = i.customer_id
                               WHERE c.status='Activo'
                                 AND EXISTS (SELECT 1 FROM contracts ct WHERE ct.customer_id=c.id)
                                 AND i.status IN ('Pendiente','Parcial','Vencida')
                                 AND (i.total - (
                                       SELECT COALESCE(SUM(p.amount),0)
                                       FROM payments p WHERE p.invoice_id = i.id
                                     )) > 0");
            $debtors = (int)$q2->fetchColumn();
            $onTime = max(0, $totalActive - $debtors);
            $arrearsPct = $totalActive > 0 ? ($debtors * 100.0 / $totalActive) : 0.0;
        } catch (Throwable $e) { }

        // Financiero (con rango)
        $from = $fromDate . ' 00:00:00';
        $to = $toDate . ' 23:59:59';
        $ingServicio = 0.0; $ingInscripcion = 0.0; $ingReconexion = 0.0; $ingMulta = 0.0; $ingVentas = 0.0; $ingOtros = 0.0;
        $ingMora = $this->computeMoraIncome($pdo, $from, $to);
        $costoOperativo = 0.0; $costoMantenimiento = 0.0; $costoAdministrativo = 0.0; $costoOtros = 0.0;
        try {
            $stmtPay = $pdo->prepare("SELECT category, COALESCE(SUM(amount),0) AS total
                                       FROM payments
                                       WHERE payment_date BETWEEN :a AND :b
                                       GROUP BY category");
            $stmtPay->execute([':a' => $from, ':b' => $to]);
            foreach ($stmtPay->fetchAll(PDO::FETCH_ASSOC) as $r) {
                $cat = (string)($r['category'] ?? '');
                $sum = (float)($r['total'] ?? 0);
                if ($cat === 'Servicio') $ingServicio += $sum;
                elseif ($cat === 'Inscripcion') $ingInscripcion += $sum;
                elseif ($cat === 'Reconexion') $ingReconexion += $sum;
                elseif ($cat === 'Multa') $ingMulta += $sum;
            }
            $stmtAcc = $pdo->prepare("SELECT
                                          SUM(CASE WHEN LOWER(concept) LIKE '%venta%' THEN amount ELSE 0 END) AS ventas,
                                          SUM(CASE WHEN LOWER(concept) LIKE '%venta%' THEN 0 ELSE amount END) AS otros
                                       FROM account_transactions
                                       WHERE direction IN ('Ingreso','Transferencia entrada')
                                         AND payment_id IS NULL
                                         AND transacted_at BETWEEN :a AND :b");
            $stmtAcc->execute([':a' => $from, ':b' => $to]);
            $rowAcc = $stmtAcc->fetch(PDO::FETCH_ASSOC) ?: [];
            $ingVentas = (float)($rowAcc['ventas'] ?? 0);
            $ingOtros = (float)($rowAcc['otros'] ?? 0);

            $stmtCost = $pdo->prepare("SELECT ec.type, COALESCE(SUM(e.amount),0) AS total
                                        FROM expenses e
                                        LEFT JOIN expense_categories ec ON ec.id = e.category_id
                                        WHERE e.expense_date BETWEEN :a AND :b
                                        GROUP BY ec.type");
            $stmtCost->execute([':a' => $from, ':b' => $to]);
            foreach ($stmtCost->fetchAll(PDO::FETCH_ASSOC) as $r) {
                $type = (string)($r['type'] ?? 'Otro');
                $sum = (float)($r['total'] ?? 0);
                if ($type === 'Operativo') $costoOperativo += $sum;
                elseif ($type === 'Mantenimiento') $costoMantenimiento += $sum;
                elseif ($type === 'Administrativo') $costoAdministrativo += $sum;
                else $costoOtros += $sum;
            }
            $stmtCostDetail = $pdo->prepare("SELECT 
                                                COALESCE(ec.type,'Otro') AS type,
                                                COALESCE(ec.name,'Sin categoría') AS category_name,
                                                COALESCE(s.name,'Sin subcategoría') AS subcategory_name,
                                                COALESCE(SUM(e.amount),0) AS total
                                            FROM expenses e
                                            LEFT JOIN expense_categories ec ON ec.id = e.category_id
                                            LEFT JOIN expense_subcategories s ON s.id = e.subcategory_id
                                            WHERE e.expense_date BETWEEN :a AND :b
                                            GROUP BY ec.type, ec.name, s.name
                                            ORDER BY ec.type, ec.name, s.name");
            $stmtCostDetail->execute([':a' => $from, ':b' => $to]);
            foreach ($stmtCostDetail->fetchAll(PDO::FETCH_ASSOC) as $r) {
                $t = (string)($r['type'] ?? 'Otro');
                $cat = (string)($r['category_name'] ?? 'Sin categoría');
                $sub = (string)($r['subcategory_name'] ?? 'Sin subcategoría');
                $amt = (float)($r['total'] ?? 0);
                if (!isset($costsByType[$t])) {
                    $costsByType[$t] = ['total' => 0.0, 'categories' => []];
                }
                $costsByType[$t]['total'] += $amt;
                if (!isset($costsByType[$t]['categories'][$cat])) {
                    $costsByType[$t]['categories'][$cat] = ['total' => 0.0, 'subcategories' => []];
                }
                $costsByType[$t]['categories'][$cat]['total'] += $amt;
                $costsByType[$t]['categories'][$cat]['subcategories'][$sub] = ($costsByType[$t]['categories'][$cat]['subcategories'][$sub] ?? 0) + $amt;
            }
        } catch (Throwable $e) { }

        $totalIngresos = $ingServicio + $ingInscripcion + $ingReconexion + $ingMulta + $ingVentas + $ingOtros;
        $totalCostos = $costoOperativo + $costoMantenimiento + $costoAdministrativo + $costoOtros;
        $ahorro = $totalIngresos - $totalCostos;

        // Dompdf
        $autoload = __DIR__ . '/../vendor/autoload.php';
        if (is_file($autoload)) { @require_once $autoload; }

        $html = $this->renderAdminFinPdfHtml([
            'fromDate' => $fromDate,
            'toDate' => $toDate,
            'displayFrom' => $displayFrom,
            'displayTo' => $displayTo,
            'totalActive' => $totalActive,
            'onTime' => $onTime,
            'debtors' => $debtors,
            'arrearsPct' => $arrearsPct,
            'ingServicio' => $ingServicio,
            'ingMora' => $ingMora,
            'ingInscripcion' => $ingInscripcion,
            'ingReconexion' => $ingReconexion,
            'ingMulta' => $ingMulta,
            'ingVentas' => $ingVentas,
            'ingOtros' => $ingOtros,
            'costoOperativo' => $costoOperativo,
            'costoMantenimiento' => $costoMantenimiento,
            'costoAdministrativo' => $costoAdministrativo,
            'totalIngresos' => $totalIngresos,
            'totalCostos' => $totalCostos,
            'ahorro' => $ahorro,
            'costsByType' => $costsByType,
        ]);

        if (class_exists('Dompdf\\Dompdf')) {
            $dompdf = new Dompdf\Dompdf();
            $dompdf->loadHtml($html);
            $dompdf->setPaper('A4', 'portrait');
            $dompdf->render();
            $dompdf->stream('Administrativo_Financiero_' . $fromDate . '_a_' . $toDate . '.pdf', ['Attachment' => true]);
            return;
        }

        header('Content-Type: text/html; charset=UTF-8');
        echo $html;
        echo '<script>window.addEventListener("load",()=>setTimeout(()=>window.print(),200));</script>';
    }

    private function collectPopulationData(): array
    {
        $pdo = (new Database())->getConnection();
        $summary = [
            'households' => 0,
            'withMembers' => 0,
            'withoutMembers' => 0,
            'totalMembers' => 0,
            'avgMembers' => 0.0,
            'avgMembersAll' => 0.0,
            'withMembersPct' => 0.0,
            'withoutMembersPct' => 0.0,
            'maxMembers' => 0,
            'minMembers' => 0,
        ];
        $bySector = [];
        $byStatus = [];
        $topHouseholds = [];
        try {
            $stmt = $pdo->query("SELECT
                    COUNT(*) AS households,
                    SUM(CASE WHEN members_count IS NOT NULL AND members_count > 0 THEN 1 ELSE 0 END) AS with_members,
                    SUM(CASE WHEN members_count IS NULL OR members_count = 0 THEN 1 ELSE 0 END) AS without_members,
                    SUM(COALESCE(members_count,0)) AS total_members,
                    MAX(CASE WHEN members_count IS NOT NULL THEN members_count ELSE NULL END) AS max_members,
                    MIN(CASE WHEN members_count IS NOT NULL THEN members_count ELSE NULL END) AS min_members
                FROM customers");
            $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
            $summary['households'] = (int)($row['households'] ?? 0);
            $summary['withMembers'] = (int)($row['with_members'] ?? 0);
            $summary['withoutMembers'] = (int)($row['without_members'] ?? 0);
            $summary['totalMembers'] = (int)($row['total_members'] ?? 0);
            $summary['maxMembers'] = (int)($row['max_members'] ?? 0);
            $summary['minMembers'] = isset($row['min_members']) ? (int)$row['min_members'] : 0;
            if ($summary['minMembers'] === 0 && $summary['withMembers'] === 0) {
                $summary['minMembers'] = 0;
            }
            if ($summary['withMembers'] > 0) {
                $summary['avgMembers'] = $summary['totalMembers'] / max(1, $summary['withMembers']);
            }
            if ($summary['households'] > 0) {
                $summary['avgMembersAll'] = $summary['totalMembers'] / max(1, $summary['households']);
                $summary['withMembersPct'] = ($summary['withMembers'] * 100) / $summary['households'];
                $summary['withoutMembersPct'] = ($summary['withoutMembers'] * 100) / $summary['households'];
            }
        } catch (Throwable $e) {
        }
        try {
            $stmt = $pdo->query("SELECT
                    CASE WHEN TRIM(COALESCE(sector,'')) = '' THEN 'Sin sector' ELSE sector END AS label,
                    COUNT(*) AS households,
                    SUM(COALESCE(members_count,0)) AS total_members,
                    SUM(CASE WHEN members_count IS NOT NULL AND members_count > 0 THEN 1 ELSE 0 END) AS with_members
                FROM customers
                GROUP BY label
                ORDER BY total_members DESC, label ASC");
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
            foreach ($rows as $r) {
                $bySector[] = [
                    'label' => (string)($r['label'] ?? ''),
                    'households' => (int)($r['households'] ?? 0),
                    'total_members' => (int)($r['total_members'] ?? 0),
                    'with_members' => (int)($r['with_members'] ?? 0),
                ];
            }
        } catch (Throwable $e) {
        }
        try {
            $stmt = $pdo->query("SELECT
                    CASE WHEN TRIM(COALESCE(status,'')) = '' THEN 'Sin estado' ELSE status END AS label,
                    COUNT(*) AS households,
                    SUM(COALESCE(members_count,0)) AS total_members
                FROM customers
                GROUP BY label
                ORDER BY households DESC, label ASC");
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
            foreach ($rows as $r) {
                $byStatus[] = [
                    'label' => (string)($r['label'] ?? ''),
                    'households' => (int)($r['households'] ?? 0),
                    'total_members' => (int)($r['total_members'] ?? 0),
                ];
            }
        } catch (Throwable $e) {
        }
        try {
            $stmt = $pdo->query("SELECT id, customer_code, name, sector, members_count
                FROM customers
                WHERE members_count IS NOT NULL AND members_count > 0
                ORDER BY members_count DESC, name ASC
                LIMIT 10");
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
            foreach ($rows as $r) {
                $topHouseholds[] = [
                    'id' => (int)($r['id'] ?? 0),
                    'customer_code' => (string)($r['customer_code'] ?? ''),
                    'name' => (string)($r['name'] ?? ''),
                    'sector' => (string)($r['sector'] ?? ''),
                    'members_count' => (int)($r['members_count'] ?? 0),
                ];
            }
        } catch (Throwable $e) {
        }
        return [
            'summary' => $summary,
            'bySector' => $bySector,
            'byStatus' => $byStatus,
            'topHouseholds' => $topHouseholds,
            'generatedAt' => date('Y-m-d H:i:s'),
        ];
    }

    private function renderPoblacionPdfHtml(array $data): string
    {
        $summary = $data['summary'] ?? [];
        $byStatus = $data['byStatus'] ?? [];
        $generatedAt = $data['generatedAt'] ?? date('Y-m-d H:i:s');
        $generatedAtFmt = date('d/m/Y H:i', strtotime((string)$generatedAt));

        $systemData = SystemData::get();
        $committeeName = trim((string)($systemData['committee_name'] ?? ''));
        if ($committeeName === '') {
            $committeeName = 'Comité de Agua Potable y Saneamiento';
        }
        $providerReg = trim((string)($systemData['provider_registration_number'] ?? ''));
        $ruc = trim((string)($systemData['ruc_number'] ?? ''));
        $municipality = trim((string)($systemData['municipality'] ?? ''));
        $department = trim((string)($systemData['department'] ?? ''));
        $physicalAddress = trim((string)($systemData['physical_address'] ?? ''));
        $phone = trim((string)($systemData['phone'] ?? ''));

        if ($physicalAddress !== '') {
            $maxAddr = 110;
            if (mb_strlen($physicalAddress) > $maxAddr) {
                $physicalAddress = rtrim(mb_substr($physicalAddress, 0, $maxAddr)) . '...';
            }
        }

        $locationLine = trim(implode(' - ', array_filter([$municipality, $department])));
        $regRucLine = trim(implode(' | ', array_filter([
            $providerReg !== '' ? ('Reg. prestador: ' . $providerReg) : '',
            $ruc !== '' ? ('RUC: ' . $ruc) : '',
        ])));
        $contactLine = trim(implode(' | ', array_filter([
            $physicalAddress !== '' ? ('Dir.: ' . $physicalAddress) : '',
            $phone !== '' ? ('Tel.: ' . $phone) : '',
        ])));

        $logoDataUri = null;
        $logoRel = trim((string)($systemData['logo_path'] ?? ''));
        if ($logoRel !== '') {
            $logoAbs = dirname(__DIR__) . '/public/' . ltrim($logoRel, '/');
            if (is_file($logoAbs)) {
                $ext = strtolower(pathinfo($logoAbs, PATHINFO_EXTENSION));
                $mimeMap = [
                    'png' => 'image/png',
                    'jpg' => 'image/jpeg',
                    'jpeg' => 'image/jpeg',
                    'webp' => 'image/webp',
                    'gif' => 'image/gif',
                ];
                $mime = $mimeMap[$ext] ?? 'application/octet-stream';
                $bin = @file_get_contents($logoAbs);
                if ($bin !== false) {
                    $logoDataUri = 'data:' . $mime . ';base64,' . base64_encode($bin);
                }
            }
        }
        ob_start();
        ?>
        <!doctype html>
        <html lang="es">
        <head>
            <meta charset="utf-8">
            <title>Reporte de Población Total</title>
            <style>
                * { box-sizing: border-box; }
                body { font-family: Arial, Helvetica, sans-serif; font-size: 10px; color:#222; margin: 12px; }
                .membrete { border-bottom: 1px solid #ddd; padding-bottom: 6px; margin-bottom: 8px; }
                .membrete-table { width: 100%; border-collapse: collapse; table-layout: auto; }
                .membrete-logo { width: 64px; vertical-align: top; padding-right: 8px; }
                .membrete-logo img { width: 64px; height: 64px; object-fit: contain; }
                .membrete-info { vertical-align: top; }
                .membrete-right { width: 280px; vertical-align: top; text-align: right; }
                .membrete-title { font-size: 12px; font-weight: bold; margin: 0; line-height: 1.2; }
                .membrete-line { font-size: 9px; margin: 0; line-height: 1.2; color: #333; }
                .membrete-meta { font-size: 9px; color: #555; word-break: break-word; }
                .muted { color:#666; font-size: 11px; }
                .section { margin-top:14px; }
                table { width:100%; border-collapse: collapse; }
                th, td { padding:6px 8px; border-bottom:1px solid #eee; text-align:left; }
                thead th { background:#f8f9fa; border-bottom:1px solid #ddd; }
                .right { text-align:right; }
                .highlight { font-weight:bold; }
                .grid { display:grid; grid-template-columns: 1fr 1fr; gap:12px; }
                .box { border:1px solid #ddd; border-radius:6px; padding:10px; }
            </style>
        </head>
        <body>
            <div class="membrete">
                <table class="membrete-table">
                    <tr>
                        <td class="membrete-logo">
                            <?php if (!empty($logoDataUri)): ?>
                                <img src="<?= htmlspecialchars($logoDataUri) ?>" alt="Logo">
                            <?php endif; ?>
                        </td>
                        <td class="membrete-info">
                            <p class="membrete-title"><?= htmlspecialchars($committeeName) ?></p>
                            <?php if ($regRucLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($regRucLine) ?></p>
                            <?php endif; ?>
                            <?php if ($locationLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($locationLine) ?></p>
                            <?php endif; ?>
                            <?php if ($contactLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($contactLine) ?></p>
                            <?php endif; ?>
                        </td>
                        <td class="membrete-right">
                            <p class="membrete-title">Población total</p>
                            <div class="membrete-meta"><strong>Total integrantes:</strong> <?= format_num($summary['totalMembers'] ?? 0, 0) ?></div>
                            <div class="membrete-meta"><strong>Total hogares:</strong> <?= format_num($summary['households'] ?? 0, 0) ?></div>
                            <div class="membrete-meta"><strong>Generado:</strong> <?= htmlspecialchars($generatedAtFmt) ?></div>
                        </td>
                    </tr>
                </table>
            </div>
            <div class="section box">
                <table>
                    <tbody>
                        <tr><td>Total de integrantes registrados</td><td class="right highlight"><?= format_num($summary['totalMembers'] ?? 0, 0) ?></td></tr>
                        <tr><td>Total de hogares registrados</td><td class="right"><?= format_num($summary['households'] ?? 0, 0) ?></td></tr>
                        <tr><td>Hogares con datos de integrantes</td><td class="right"><?= format_num($summary['withMembers'] ?? 0, 0) ?> (<?= number_format((float)($summary['withMembersPct'] ?? 0), 1) ?>%)</td></tr>
                        <tr><td>Hogares sin datos de integrantes</td><td class="right"><?= format_num($summary['withoutMembers'] ?? 0, 0) ?> (<?= number_format((float)($summary['withoutMembersPct'] ?? 0), 1) ?>%)</td></tr>
                        <tr><td>Mayor número de integrantes en un hogar</td><td class="right"><?= format_num($summary['maxMembers'] ?? 0, 0) ?></td></tr>
                        <tr><td>Menor número de integrantes en un hogar</td><td class="right"><?= format_num($summary['minMembers'] ?? 0, 0) ?></td></tr>
                    </tbody>
                </table>
            </div>
            <div class="section box">
                <strong>Distribución por estado</strong>
                <table>
                    <thead>
                        <tr><th>Estado</th><th class="right">Hogares</th><th class="right">Integrantes</th></tr>
                    </thead>
                    <tbody>
                    <?php if (!empty($byStatus)): foreach ($byStatus as $item): ?>
                        <tr>
                            <td><?= htmlspecialchars((string)($item['label'] ?? '')) ?></td>
                            <td class="right"><?= format_num($item['households'] ?? 0, 0) ?></td>
                            <td class="right"><?= format_num($item['total_members'] ?? 0, 0) ?></td>
                        </tr>
                    <?php endforeach; else: ?>
                        <tr><td colspan="3" class="muted">Sin datos disponibles.</td></tr>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </body>
        </html>
        <?php
        return (string)ob_get_clean();
    }

    private function renderAdminFinPdfHtml(array $d): string
    {
        $ORG_NAME = defined('ORG_NAME') ? constant('ORG_NAME') : 'Comité de Agua Potable y Saneamiento (CAPS)';
        $ORG_SUB = defined('ORG_SUBNAME') ? constant('ORG_SUBNAME') : 'Teodoro Mendoza- Laurel Galán';

        $systemData = SystemData::get();
        $committeeName = trim((string)($systemData['committee_name'] ?? ''));
        if ($committeeName === '') {
            $committeeName = (string)$ORG_NAME;
        }
        $providerReg = trim((string)($systemData['provider_registration_number'] ?? ''));
        $ruc = trim((string)($systemData['ruc_number'] ?? ''));
        $municipality = trim((string)($systemData['municipality'] ?? ''));
        $department = trim((string)($systemData['department'] ?? ''));
        $physicalAddress = trim((string)($systemData['physical_address'] ?? ''));
        $phone = trim((string)($systemData['phone'] ?? ''));

        if ($physicalAddress !== '') {
            $maxAddr = 110;
            if (mb_strlen($physicalAddress) > $maxAddr) {
                $physicalAddress = rtrim(mb_substr($physicalAddress, 0, $maxAddr)) . '...';
            }
        }

        $locationLine = trim(implode(' - ', array_filter([$municipality, $department])));
        $regRucLine = trim(implode(' | ', array_filter([
            $providerReg !== '' ? ('Reg. prestador: ' . $providerReg) : '',
            $ruc !== '' ? ('RUC: ' . $ruc) : '',
        ])));
        $contactLine = trim(implode(' | ', array_filter([
            $physicalAddress !== '' ? ('Dir.: ' . $physicalAddress) : '',
            $phone !== '' ? ('Tel.: ' . $phone) : '',
        ])));

        $logoDataUri = null;
        $logoRel = trim((string)($systemData['logo_path'] ?? ''));
        if ($logoRel !== '') {
            $logoAbs = dirname(__DIR__) . '/public/' . ltrim($logoRel, '/');
            if (is_file($logoAbs)) {
                $ext = strtolower(pathinfo($logoAbs, PATHINFO_EXTENSION));
                $mimeMap = [
                    'png' => 'image/png',
                    'jpg' => 'image/jpeg',
                    'jpeg' => 'image/jpeg',
                    'webp' => 'image/webp',
                    'gif' => 'image/gif',
                ];
                $mime = $mimeMap[$ext] ?? 'application/octet-stream';
                $bin = @file_get_contents($logoAbs);
                if ($bin !== false) {
                    $logoDataUri = 'data:' . $mime . ';base64,' . base64_encode($bin);
                }
            }
        }
        ob_start();
        ?>
        <!doctype html>
        <html lang="es">
        <head>
            <meta charset="utf-8">
            <title>Informe Administrativo-Financiero</title>
            <style>
                @page { margin: 18mm 14mm; }
                * { box-sizing: border-box; }
                body { font-family: Arial, Helvetica, sans-serif; font-size: 12px; color:#222; }
                .membrete { border-bottom: 1px solid #ddd; padding-bottom: 6px; margin-bottom: 10px; }
                .membrete-table { width: 100%; border-collapse: collapse; table-layout: auto; }
                .membrete-logo { width: 64px; vertical-align: top; padding-right: 8px; }
                .membrete-logo img { width: 64px; height: 64px; object-fit: contain; }
                .membrete-info { vertical-align: top; }
                .membrete-right { width: 280px; vertical-align: top; text-align: right; }
                .membrete-title { font-size: 12px; font-weight: bold; margin: 0; line-height: 1.2; }
                .membrete-line { font-size: 9px; margin: 0; line-height: 1.2; color: #333; }
                .membrete-meta { font-size: 9px; color: #555; word-break: break-word; }
                h1 { font-size: 18px; text-align:center; margin: 6px 0 10px; }
                .center { text-align:center; }
                .muted { color:#666; font-size: 11px; }
                .section-title { font-weight:bold; margin: 10px 0 6px; }
                .box { border:1px solid #ddd; border-radius:6px; padding:10px; margin:8px 0; }
                table { width:100%; border-collapse: collapse; }
                th, td { padding:6px 8px; border-bottom:1px solid #f0f0f0; text-align:left; }
                thead th { background:#f8f9fa; border-bottom:1px solid #ddd; }
                .right { text-align:right; }
                .totals { font-weight:bold; }
                .grid { display:grid; grid-template-columns: 1fr 1fr; gap:12px; }
                .avoid-break { page-break-inside: avoid; }
            </style>
        </head>
        <body>
            <div class="membrete">
                <table class="membrete-table">
                    <tr>
                        <td class="membrete-logo">
                            <?php if (!empty($logoDataUri)): ?>
                                <img src="<?= htmlspecialchars($logoDataUri) ?>" alt="Logo">
                            <?php endif; ?>
                        </td>
                        <td class="membrete-info">
                            <p class="membrete-title"><?= htmlspecialchars($committeeName) ?></p>
                            <?php if ($regRucLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($regRucLine) ?></p>
                            <?php endif; ?>
                            <?php if ($locationLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($locationLine) ?></p>
                            <?php endif; ?>
                            <?php if ($contactLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($contactLine) ?></p>
                            <?php endif; ?>
                        </td>
                        <td class="membrete-right">
                            <p class="membrete-title">Administrativo-financiero</p>
                            <div class="membrete-meta"><strong>Generado:</strong> <?= htmlspecialchars(date('d/m/Y')) ?></div>
                        </td>
                    </tr>
                </table>
            </div>

            <h1>INFORME ADMINISTRATIVO-FINANCIERO</h1>

            <div class="section-title">I- INFORME ADMINISTRATIVO</div>
            <div class="box avoid-break">
                <table>
                    <tbody>
                        <tr><td>Número de usuarios actuales</td><td class="right"><?= (int)($d['totalActive'] ?? 0) ?></td></tr>
                        <tr><td>Número de usuarios al día con sus pagos</td><td class="right"><?= (int)($d['onTime'] ?? 0) ?></td></tr>
                        <tr><td>Número de usuarios morosos</td><td class="right"><?= (int)($d['debtors'] ?? 0) ?></td></tr>
                        <tr><td>Porcentaje de morosidad</td><td class="right"><?= number_format((float)($d['arrearsPct'] ?? 0), 1) ?>%</td></tr>
                    </tbody>
                </table>
            </div>

            <div class="section-title">II- INFORME FINANCIERO</div>
            <div class="grid">
                <div class="box avoid-break">
                    <strong>INGRESOS</strong>
                    <table>
                        <tbody>
                            <tr><td>Ingresos por cobro de servicio de agua</td><td class="right"><?= format_currency($d['ingServicio'] ?? 0) ?></td></tr>
                            <tr><td>Ingreso Por Mora</td><td class="right"><?= format_currency($d['ingMora'] ?? 0) ?></td></tr>
                            <tr><td>Ingresos por nuevos servicios</td><td class="right"><?= format_currency($d['ingInscripcion'] ?? 0) ?></td></tr>
                            <tr><td>Ingresos por reconexiones</td><td class="right"><?= format_currency($d['ingReconexion'] ?? 0) ?></td></tr>
                            <tr><td>Ingresos por Multas</td><td class="right"><?= format_currency($d['ingMulta'] ?? 0) ?></td></tr>
                            <tr><td>Ingresos por ventas</td><td class="right"><?= format_currency($d['ingVentas'] ?? 0) ?></td></tr>
                            <tr><td>Otros ingresos</td><td class="right"><?= format_currency($d['ingOtros'] ?? 0) ?></td></tr>
                        </tbody>
                    </table>
                </div>
                <div class="box avoid-break">
                    <strong>COSTOS</strong>
                    <?php $cbt = $d['costsByType'] ?? []; if (!empty($cbt)): ?>
                        <?php foreach ($cbt as $type => $info): $info = $info ?? []; $cats = $info['categories'] ?? []; ?>
                            <div style="margin-top:6px;">
                                <div style="font-weight:bold;"><?= htmlspecialchars((string)$type) ?> (<?= format_currency($info['total'] ?? 0) ?>)</div>
                                <table>
                                    <thead>
                                        <tr>
                                            <th style="width:40%">Categoría</th>
                                            <th style="width:40%">Subcategoría</th>
                                            <th class="right" style="width:20%">Monto</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php foreach ($cats as $catName => $catData): $catData = $catData ?? []; $subs = $catData['subcategories'] ?? []; ?>
                                            <tr>
                                                <td><strong><?= htmlspecialchars((string)$catName) ?></strong></td>
                                                <td class="muted">Total categoría</td>
                                                <td class="right"><strong><?= format_currency($catData['total'] ?? 0) ?></strong></td>
                                            </tr>
                                            <?php foreach ($subs as $subName => $amt): ?>
                                                <tr>
                                                    <td></td>
                                                    <td><?= htmlspecialchars((string)$subName) ?></td>
                                                    <td class="right"><?= format_currency((float)$amt) ?></td>
                                                </tr>
                                            <?php endforeach; ?>
                                        <?php endforeach; ?>
                                    </tbody>
                                </table>
                            </div>
                        <?php endforeach; ?>
                    <?php else: ?>
                        <p class="muted">Sin gastos registrados en el período seleccionado.</p>
                    <?php endif; ?>
                </div>
            </div>

            <div class="box avoid-break">
                <table>
                    <tbody>
                        <tr>
                            <td class="totals">TOTAL INGRESOS</td>
                            <td class="right totals"><?= format_currency($d['totalIngresos'] ?? 0) ?></td>
                            <td class="totals" style="padding-left:16px;">TOTAL COSTOS</td>
                            <td class="right totals"><?= format_currency($d['totalCostos'] ?? 0) ?></td>
                        </tr>
                    </tbody>
                </table>
                <div class="right" style="margin-top:6px;"><strong>AHORRO DEL PERIODO: </strong><?= format_currency($d['ahorro'] ?? 0) ?></div>
            </div>

        </body>
        </html>
        <?php
        return (string)ob_get_clean();
    }

    private function resolveFinancialDataStartDate(PDO $pdo): string
    {
        $fallback = '1970-01-01';
        try {
            $sql = "SELECT MIN(src_date) AS start_date
                    FROM (
                        SELECT MIN(payment_date) AS src_date FROM payments
                        UNION ALL
                        SELECT MIN(transacted_at) FROM account_transactions
                        UNION ALL
                        SELECT MIN(expense_date) FROM expenses
                    ) dates
                    WHERE src_date IS NOT NULL";
            $row = $pdo->query($sql)->fetch(PDO::FETCH_ASSOC) ?: [];
            if (!empty($row['start_date'])) {
                $date = substr((string)$row['start_date'], 0, 10);
                if ($date !== '') {
                    return $date;
                }
            }
        } catch (Throwable $e) {
            // Usar fallback
        }
        return $fallback;
    }

    private function normalizeDate(string $value, string $fallback): string
    {
        if ($value === '') {
            return $fallback;
        }
        $dt = DateTime::createFromFormat('Y-m-d', $value);
        if ($dt === false) {
            return $fallback;
        }
        return $dt->format('Y-m-d');
    }

    private function computeFinancialAggregates(PDO $pdo, string $fromDateTime, string $toDateTime): array
    {
        $data = [
            'ingresos' => [
                'servicio' => 0.0,
                'inscripcion' => 0.0,
                'reconexion' => 0.0,
                'multa' => 0.0,
                'ventas' => 0.0,
                'otros' => 0.0,
            ],
            'costos' => [
                'Operativo' => 0.0,
                'Mantenimiento' => 0.0,
                'Administrativo' => 0.0,
                'Otros' => 0.0,
            ],
            'costsByType' => [],
        ];

        try {
            $stmtPay = $pdo->prepare("SELECT category, COALESCE(SUM(amount),0) AS total
                                      FROM payments
                                      WHERE payment_date BETWEEN :a AND :b
                                      GROUP BY category");
            $stmtPay->execute([':a' => $fromDateTime, ':b' => $toDateTime]);
            foreach ($stmtPay->fetchAll(PDO::FETCH_ASSOC) as $r) {
                $cat = (string)($r['category'] ?? '');
                $sum = (float)($r['total'] ?? 0);
                if ($cat === 'Servicio') {
                    $data['ingresos']['servicio'] += $sum;
                } elseif ($cat === 'Inscripcion') {
                    $data['ingresos']['inscripcion'] += $sum;
                } elseif ($cat === 'Reconexion') {
                    $data['ingresos']['reconexion'] += $sum;
                } elseif ($cat === 'Multa') {
                    $data['ingresos']['multa'] += $sum;
                }
            }
        } catch (Throwable $e) {
        }

        try {
            $stmtAcc = $pdo->prepare("SELECT
                                         SUM(CASE WHEN LOWER(concept) LIKE '%venta%' THEN amount ELSE 0 END) AS ventas,
                                         SUM(CASE WHEN LOWER(concept) LIKE '%venta%' THEN 0 ELSE amount END) AS otros
                                      FROM account_transactions
                                      WHERE direction IN ('Ingreso','Transferencia entrada')
                                        AND payment_id IS NULL
                                        AND transacted_at BETWEEN :a AND :b");
            $stmtAcc->execute([':a' => $fromDateTime, ':b' => $toDateTime]);
            $rowAcc = $stmtAcc->fetch(PDO::FETCH_ASSOC) ?: [];
            $data['ingresos']['ventas'] += (float)($rowAcc['ventas'] ?? 0);
            $data['ingresos']['otros'] += (float)($rowAcc['otros'] ?? 0);
        } catch (Throwable $e) {
        }

        try {
            $stmtCost = $pdo->prepare("SELECT ec.type, COALESCE(SUM(e.amount),0) AS total
                                       FROM expenses e
                                       LEFT JOIN expense_categories ec ON ec.id = e.category_id
                                       WHERE e.expense_date BETWEEN :a AND :b
                                       GROUP BY ec.type");
            $stmtCost->execute([':a' => $fromDateTime, ':b' => $toDateTime]);
            foreach ($stmtCost->fetchAll(PDO::FETCH_ASSOC) as $r) {
                $type = (string)($r['type'] ?? 'Otros');
                $sum = (float)($r['total'] ?? 0);
                if (!isset($data['costos'][$type])) {
                    $data['costos'][$type] = 0.0;
                }
                $data['costos'][$type] += $sum;
            }
        } catch (Throwable $e) {
        }

        try {
            $stmtCostDetail = $pdo->prepare("SELECT 
                                                COALESCE(ec.type,'Otros') AS type,
                                                COALESCE(ec.name,'Sin categoría') AS category_name,
                                                COALESCE(s.name,'Sin subcategoría') AS subcategory_name,
                                                COALESCE(SUM(e.amount),0) AS total
                                            FROM expenses e
                                            LEFT JOIN expense_categories ec ON ec.id = e.category_id
                                            LEFT JOIN expense_subcategories s ON s.id = e.subcategory_id
                                            WHERE e.expense_date BETWEEN :a AND :b
                                            GROUP BY ec.type, ec.name, s.name
                                            ORDER BY ec.type, ec.name, s.name");
            $stmtCostDetail->execute([':a' => $fromDateTime, ':b' => $toDateTime]);
            foreach ($stmtCostDetail->fetchAll(PDO::FETCH_ASSOC) as $r) {
                $type = (string)($r['type'] ?? 'Otros');
                $cat = (string)($r['category_name'] ?? 'Sin categoría');
                $sub = (string)($r['subcategory_name'] ?? 'Sin subcategoría');
                $amt = (float)($r['total'] ?? 0);
                if (!isset($data['costsByType'][$type])) {
                    $data['costsByType'][$type] = ['total' => 0.0, 'categories' => []];
                }
                $data['costsByType'][$type]['total'] += $amt;
                if (!isset($data['costsByType'][$type]['categories'][$cat])) {
                    $data['costsByType'][$type]['categories'][$cat] = ['total' => 0.0, 'subcategories' => []];
                }
                $data['costsByType'][$type]['categories'][$cat]['total'] += $amt;
                $data['costsByType'][$type]['categories'][$cat]['subcategories'][$sub] = ($data['costsByType'][$type]['categories'][$cat]['subcategories'][$sub] ?? 0) + $amt;
            }
        } catch (Throwable $e) {
        }

        return $data;
    }

    private function computeMoraIncome(PDO $pdo, string $fromDateTime, string $toDateTime): float
    {
        try {
            $sql = "SELECT COALESCE(SUM(i.late_fee_amount),0)
                    FROM invoices i
                    JOIN (
                        SELECT invoice_id, MAX(payment_date) AS last_payment_date
                        FROM payments
                        WHERE invoice_id IS NOT NULL
                        GROUP BY invoice_id
                    ) p ON p.invoice_id = i.id
                    WHERE i.status = 'Pagado'
                      AND i.late_fee_amount > 0
                      AND p.last_payment_date BETWEEN :a AND :b";
            $stmt = $pdo->prepare($sql);
            $stmt->execute([':a' => $fromDateTime, ':b' => $toDateTime]);
            return (float)$stmt->fetchColumn();
        } catch (Throwable $e) {
            return 0.0;
        }
    }

    public function recaudacionPdf(): void
    {
        // Filtros idénticos a recaudacion()
        $today = date('Y-m-d');
        $firstOfMonth = date('Y-m-01');
        $grpParam = $_GET['group'] ?? 'day';
        $grp = in_array($grpParam, ['day','month'], true) ? $grpParam : 'day';
        $fromDate = $_GET['date_from'] ?? $firstOfMonth;
        $toDate = $_GET['date_to'] ?? $today;

        $rows = Payment::getPaymentsByDateRange($fromDate, $toDate);

        $summaryTotal = 0.0; $count = 0; $byMethod = [];
        foreach ($rows as $r) {
            $amt = (float)($r['amount'] ?? 0);
            $summaryTotal += $amt; $count++;
            $m = $r['method'] ?? 'Otro';
            $byMethod[$m] = ($byMethod[$m] ?? 0) + $amt;
        }
        arsort($byMethod);

        // Dompdf
        $autoload = __DIR__ . '/../vendor/autoload.php';
        if (is_file($autoload)) { @require_once $autoload; }

        $html = $this->renderRecaudacionPdfHtml($rows, $fromDate, $toDate, $grp, $summaryTotal, $count, $byMethod);

        if (class_exists('Dompdf\\Dompdf')) {
            $dompdf = new Dompdf\Dompdf();
            $dompdf->loadHtml($html);
            $dompdf->setPaper('A4', 'portrait');
            $dompdf->render();
            $dompdf->stream('Recaudacion_' . $fromDate . '_a_' . $toDate . '.pdf', ['Attachment' => true]);
            return;
        }

        // Fallback imprimible
        header('Content-Type: text/html; charset=UTF-8');
        echo $html;
        echo '<script>window.addEventListener("load",()=>setTimeout(()=>window.print(),200));</script>';
    }

    public function flujocajaPdf(): void
    {
        $today = date('Y-m-d');
        $firstOfMonth = date('Y-m-01');
        $grpParam = $_GET['group'] ?? 'day';
        $group = in_array($grpParam, ['day','month'], true) ? $grpParam : 'day';
        $fromDate = $_GET['date_from'] ?? $firstOfMonth;
        $toDate = $_GET['date_to'] ?? $today;

        $rowsIn = Payment::getPaymentsByDateRange($fromDate, $toDate);
        $rowsOut = Expense::getExpensesByDateRange($fromDate, $toDate);

        // Saldo inicial
        $initialBalance = 0.0;
        try {
            $pdo = (new Database())->getConnection();
            $cut = $fromDate . ' 00:00:00';
            $qIn = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM payments WHERE payment_date < :cut");
            $qIn->execute([':cut' => $cut]);
            $sumInBefore = (float)$qIn->fetchColumn();
            $qOut = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM expenses WHERE expense_date < :cut");
            $qOut->execute([':cut' => $cut]);
            $sumOutBefore = (float)$qOut->fetchColumn();
            $initialBalance = $sumInBefore - $sumOutBefore;
        } catch (Throwable $e) {
            $initialBalance = 0.0;
        }

        // CxC
        $accountsReceivable = 0.0; $debtorsCount = 0; $openInvoicesCount = 0;
        try {
            $pdo = $pdo ?? (new Database())->getConnection();
            $sqlCxC = "SELECT
                          SUM(GREATEST(i.total - COALESCE(p.paid,0), 0)) AS receivable,
                          COUNT(DISTINCT CASE WHEN (i.total - COALESCE(p.paid,0)) > 0 THEN i.customer_id END) AS debtors,
                          SUM(CASE WHEN (i.total - COALESCE(p.paid,0)) > 0 THEN 1 ELSE 0 END) AS open_invoices
                        FROM invoices i
                        LEFT JOIN (
                          SELECT invoice_id, SUM(amount) AS paid
                          FROM payments
                          WHERE invoice_id IS NOT NULL
                          GROUP BY invoice_id
                        ) p ON p.invoice_id = i.id
                        WHERE i.status IN ('Pendiente','Parcial','Vencida')";
            $r = $pdo->query($sqlCxC)->fetch(PDO::FETCH_ASSOC) ?: [];
            $accountsReceivable = (float)($r['receivable'] ?? 0);
            $debtorsCount = (int)($r['debtors'] ?? 0);
            $openInvoicesCount = (int)($r['open_invoices'] ?? 0);
        } catch (Throwable $e) {
            $accountsReceivable = 0.0; $debtorsCount = 0; $openInvoicesCount = 0;
        }

        // Próximos egresos
        $today2 = date('Y-m-d');
        $next7 = date('Y-m-d', strtotime('+7 days'));
        $endMonth = date('Y-m-t');
        $payablesNext7 = 0.0; $payablesMonth = 0.0;
        try {
            $pdo = $pdo ?? (new Database())->getConnection();
            $stmt7 = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM expenses WHERE expense_date BETWEEN :a AND :b");
            $stmt7->execute([':a' => $today2 . ' 00:00:00', ':b' => $next7 . ' 23:59:59']);
            $payablesNext7 = (float)$stmt7->fetchColumn();
            $stmtM = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM expenses WHERE expense_date BETWEEN :a AND :b");
            $stmtM->execute([':a' => $today2 . ' 00:00:00', ':b' => $endMonth . ' 23:59:59']);
            $payablesMonth = (float)$stmtM->fetchColumn();
        } catch (Throwable $e) {
            $payablesNext7 = 0.0; $payablesMonth = 0.0;
        }

        // Totales y ledger
        $totalIn = 0.0; $totalOut = 0.0; $countIn = 0; $countOut = 0;
        foreach ($rowsIn as $r) { $totalIn += (float)($r['amount'] ?? 0); $countIn++; }
        foreach ($rowsOut as $r) { $totalOut += (float)($r['amount'] ?? 0); $countOut++; }
        $ledger = [];
        foreach ($rowsIn as $r) {
            $ledger[] = [
                'date' => (string)($r['payment_date'] ?? ''),
                'type' => 'Ingreso',
                'amount' => (float)($r['amount'] ?? 0),
                'detail' => 'Recibo ' . ($r['receipt_number'] ?? ('#' . ($r['id'] ?? ''))) . ' — ' . ($r['customer_name'] ?? ''),
                'sign' => 1,
            ];
        }
        foreach ($rowsOut as $r) {
            $ledger[] = [
                'date' => (string)($r['expense_date'] ?? ''),
                'type' => 'Egreso',
                'amount' => (float)($r['amount'] ?? 0),
                'detail' => trim(($r['subcategory_name'] ?? '') . (($r['vendor'] ?? '') !== '' ? (' — ' . $r['vendor']) : '')),
                'sign' => -1,
            ];
        }
        usort($ledger, function($a, $b){
            $ta = strtotime($a['date'] ?? '') ?: 0; $tb = strtotime($b['date'] ?? '') ?: 0; return $ta <=> $tb;
        });
        $net = $totalIn - $totalOut;

        // Dompdf
        $autoload = __DIR__ . '/../vendor/autoload.php';
        if (is_file($autoload)) { @require_once $autoload; }

        $html = $this->renderFlujoCajaPdfHtml([
            'fromDate' => $fromDate,
            'toDate' => $toDate,
            'group' => $group,
            'initialBalance' => $initialBalance,
            'accountsReceivable' => $accountsReceivable,
            'debtorsCount' => $debtorsCount,
            'openInvoicesCount' => $openInvoicesCount,
            'payablesNext7' => $payablesNext7,
            'payablesMonth' => $payablesMonth,
            'totalIn' => $totalIn,
            'totalOut' => $totalOut,
            'net' => $net,
            'countIn' => $countIn,
            'countOut' => $countOut,
        ], $ledger);

        if (class_exists('Dompdf\\Dompdf')) {
            $dompdf = new Dompdf\Dompdf();
            $dompdf->loadHtml($html);
            $dompdf->setPaper('A4', 'portrait');
            $dompdf->render();
            $dompdf->stream('FlujoCaja_' . $fromDate . '_a_' . $toDate . '.pdf', ['Attachment' => true]);
            return;
        }

        header('Content-Type: text/html; charset=UTF-8');
        echo $html;
        echo '<script>window.addEventListener("load",()=>setTimeout(()=>window.print(),200));</script>';
    }

    private function renderRecaudacionPdfHtml(array $rows, string $fromDate, string $toDate, string $group, float $total, int $count, array $byMethod): string
    {
        $generatedAt = date('d/m/Y H:i');
        $periodLine = format_period($fromDate, $toDate);
        $groupLabel = $group === 'month' ? 'Mes' : 'Día';

        $systemData = SystemData::get();
        $committeeName = trim((string)($systemData['committee_name'] ?? ''));
        if ($committeeName === '') {
            $committeeName = 'Comité de Agua Potable y Saneamiento';
        }
        $providerReg = trim((string)($systemData['provider_registration_number'] ?? ''));
        $ruc = trim((string)($systemData['ruc_number'] ?? ''));
        $municipality = trim((string)($systemData['municipality'] ?? ''));
        $department = trim((string)($systemData['department'] ?? ''));
        $physicalAddress = trim((string)($systemData['physical_address'] ?? ''));
        $phone = trim((string)($systemData['phone'] ?? ''));

        if ($physicalAddress !== '') {
            $maxAddr = 110;
            if (mb_strlen($physicalAddress) > $maxAddr) {
                $physicalAddress = rtrim(mb_substr($physicalAddress, 0, $maxAddr)) . '...';
            }
        }

        $locationLine = trim(implode(' - ', array_filter([$municipality, $department])));
        $regRucLine = trim(implode(' | ', array_filter([
            $providerReg !== '' ? ('Reg. prestador: ' . $providerReg) : '',
            $ruc !== '' ? ('RUC: ' . $ruc) : '',
        ])));
        $contactLine = trim(implode(' | ', array_filter([
            $physicalAddress !== '' ? ('Dir.: ' . $physicalAddress) : '',
            $phone !== '' ? ('Tel.: ' . $phone) : '',
        ])));

        $logoDataUri = null;
        $logoRel = trim((string)($systemData['logo_path'] ?? ''));
        if ($logoRel !== '') {
            $logoAbs = dirname(__DIR__) . '/public/' . ltrim($logoRel, '/');
            if (is_file($logoAbs)) {
                $ext = strtolower(pathinfo($logoAbs, PATHINFO_EXTENSION));
                $mimeMap = [
                    'png' => 'image/png',
                    'jpg' => 'image/jpeg',
                    'jpeg' => 'image/jpeg',
                    'webp' => 'image/webp',
                    'gif' => 'image/gif',
                ];
                $mime = $mimeMap[$ext] ?? 'application/octet-stream';
                $bin = @file_get_contents($logoAbs);
                if ($bin !== false) {
                    $logoDataUri = 'data:' . $mime . ';base64,' . base64_encode($bin);
                }
            }
        }

        ob_start();
        ?>
        <!doctype html>
        <html lang="es">
        <head>
            <meta charset="utf-8">
            <title>Informe de Recaudación</title>
            <style>
                * { box-sizing: border-box; }
                body { font-family: Arial, Helvetica, sans-serif; font-size: 12px; color: #222; margin: 12px; }
                .membrete { border-bottom: 1px solid #ddd; padding-bottom: 6px; margin-bottom: 10px; }
                .membrete-table { width: 100%; border-collapse: collapse; table-layout: auto; }
                .membrete-logo { width: 64px; vertical-align: top; padding-right: 8px; }
                .membrete-logo img { width: 64px; height: 64px; object-fit: contain; }
                .membrete-info { vertical-align: top; }
                .membrete-right { width: 280px; vertical-align: top; text-align: right; }
                .membrete-title { font-size: 12px; font-weight: bold; margin: 0; line-height: 1.2; }
                .membrete-line { font-size: 9px; margin: 0; line-height: 1.2; color: #333; }
                .membrete-meta { font-size: 9px; color: #555; word-break: break-word; }
                .muted { color:#666; font-size: 11px; }
                .box { border:1px solid #ddd; border-radius:6px; padding:10px; margin:10px 0; }
                table { width:100%; border-collapse: collapse; }
                th, td { padding:6px 8px; border-bottom:1px solid #eee; text-align:left; }
                thead th { background:#f8f9fa; border-bottom:1px solid #ddd; }
                .right { text-align:right; }
                .grid { display:grid; grid-template-columns: 1fr 1fr; gap:10px; }
            </style>
        </head>
        <body>
            <div class="membrete">
                <table class="membrete-table">
                    <tr>
                        <td class="membrete-logo">
                            <?php if (!empty($logoDataUri)): ?>
                                <img src="<?= htmlspecialchars($logoDataUri) ?>" alt="Logo">
                            <?php endif; ?>
                        </td>
                        <td class="membrete-info">
                            <p class="membrete-title"><?= htmlspecialchars($committeeName) ?></p>
                            <?php if ($regRucLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($regRucLine) ?></p>
                            <?php endif; ?>
                            <?php if ($locationLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($locationLine) ?></p>
                            <?php endif; ?>
                            <?php if ($contactLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($contactLine) ?></p>
                            <?php endif; ?>
                        </td>
                        <td class="membrete-right">
                            <p class="membrete-title">Recaudación</p>
                            <div class="membrete-meta"><strong>Total recaudado:</strong> <?= format_currency($total) ?></div>
                            <div class="membrete-meta"><strong>Nº cobros:</strong> <?= (int)$count ?></div>
                            <div class="membrete-meta"><strong>Promedio:</strong> <?= format_currency($count > 0 ? ($total / max(1, $count)) : 0) ?></div>
                            <div class="membrete-meta"><strong>Generado:</strong> <?= htmlspecialchars($generatedAt) ?></div>
                            <div class="membrete-meta"><strong>Período:</strong> <?= htmlspecialchars($periodLine) ?> (<?= htmlspecialchars($groupLabel) ?>)</div>
                        </td>
                    </tr>
                </table>
            </div>

            <div class="grid">
                <div class="box">
                    <strong>Por Método</strong>
                    <table>
                        <thead><tr><th>Método</th><th class="right">Monto</th><th class="right">%</th></tr></thead>
                        <tbody>
                        <?php $tot = (float)$total; if ($byMethod): foreach ($byMethod as $m=>$amt): $pct = $tot>0?($amt*100/$tot):0; ?>
                            <tr><td><?= htmlspecialchars($m) ?></td><td class="right"><?= format_currency($amt) ?></td><td class="right"><?= number_format($pct,1) ?>%</td></tr>
                        <?php endforeach; else: ?>
                            <tr><td colspan="3" class="muted">Sin datos</td></tr>
                        <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>

            <div class="box">
                <strong>Cobros en el período (<?= htmlspecialchars(format_period($fromDate, $toDate)) ?>)</strong>
                <table>
                    <thead>
                        <tr>
                            <th style="width:16%">Fecha</th>
                            <th style="width:16%">Recibo</th>
                            <th style="width:16%">Factura</th>
                            <th>Cliente</th>
                            <th style="width:12%">Método</th>
                            <th class="right" style="width:14%">Monto</th>
                            <th style="width:16%">Cajero</th>
                        </tr>
                    </thead>
                    <tbody>
                    <?php if ($rows): foreach ($rows as $r): ?>
                        <tr>
                            <td><?= htmlspecialchars(format_datetime($r['payment_date'] ?? '')) ?></td>
                            <td><?= htmlspecialchars($r['receipt_number'] ?? ('#'.($r['id'] ?? ''))) ?></td>
                            <td><?= htmlspecialchars($r['invoice_number'] ?? '-') ?></td>
                            <td><?= htmlspecialchars($r['customer_name'] ?? '') ?></td>
                            <td><?= htmlspecialchars($r['method'] ?? '') ?></td>
                            <td class="right"><?= format_currency($r['amount'] ?? 0) ?></td>
                            <td><?= htmlspecialchars($r['cashier_name'] ?? '') ?></td>
                        </tr>
                    <?php endforeach; else: ?>
                        <tr><td colspan="7" class="muted">Sin registros</td></tr>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </body>
        </html>
        <?php
        return (string)ob_get_clean();
    }

    private function renderFlujoCajaPdfHtml(array $data, array $ledger): string
    {
        $fromDate = $data['fromDate'] ?? '';
        $toDate = $data['toDate'] ?? '';
        $initial = (float)($data['initialBalance'] ?? 0);

        $generatedAt = date('d/m/Y H:i');
        $periodLine = format_period($fromDate, $toDate);

        $systemData = SystemData::get();
        $committeeName = trim((string)($systemData['committee_name'] ?? ''));
        if ($committeeName === '') {
            $committeeName = 'Comité de Agua Potable y Saneamiento';
        }
        $providerReg = trim((string)($systemData['provider_registration_number'] ?? ''));
        $ruc = trim((string)($systemData['ruc_number'] ?? ''));
        $municipality = trim((string)($systemData['municipality'] ?? ''));
        $department = trim((string)($systemData['department'] ?? ''));
        $physicalAddress = trim((string)($systemData['physical_address'] ?? ''));
        $phone = trim((string)($systemData['phone'] ?? ''));

        if ($physicalAddress !== '') {
            $maxAddr = 110;
            if (mb_strlen($physicalAddress) > $maxAddr) {
                $physicalAddress = rtrim(mb_substr($physicalAddress, 0, $maxAddr)) . '...';
            }
        }

        $locationLine = trim(implode(' - ', array_filter([$municipality, $department])));
        $regRucLine = trim(implode(' | ', array_filter([
            $providerReg !== '' ? ('Reg. prestador: ' . $providerReg) : '',
            $ruc !== '' ? ('RUC: ' . $ruc) : '',
        ])));
        $contactLine = trim(implode(' | ', array_filter([
            $physicalAddress !== '' ? ('Dir.: ' . $physicalAddress) : '',
            $phone !== '' ? ('Tel.: ' . $phone) : '',
        ])));

        $logoDataUri = null;
        $logoRel = trim((string)($systemData['logo_path'] ?? ''));
        if ($logoRel !== '') {
            $logoAbs = dirname(__DIR__) . '/public/' . ltrim($logoRel, '/');
            if (is_file($logoAbs)) {
                $ext = strtolower(pathinfo($logoAbs, PATHINFO_EXTENSION));
                $mimeMap = [
                    'png' => 'image/png',
                    'jpg' => 'image/jpeg',
                    'jpeg' => 'image/jpeg',
                    'webp' => 'image/webp',
                    'gif' => 'image/gif',
                ];
                $mime = $mimeMap[$ext] ?? 'application/octet-stream';
                $bin = @file_get_contents($logoAbs);
                if ($bin !== false) {
                    $logoDataUri = 'data:' . $mime . ';base64,' . base64_encode($bin);
                }
            }
        }
        ob_start();
        ?>
        <!doctype html>
        <html lang="es">
        <head>
            <meta charset="utf-8">
            <title>Informe de Flujo de Caja</title>
            <style>
                * { box-sizing: border-box; }
                body { font-family: Arial, Helvetica, sans-serif; font-size: 12px; color:#222; margin: 12px; }
                .membrete { border-bottom: 1px solid #ddd; padding-bottom: 6px; margin-bottom: 10px; }
                .membrete-table { width: 100%; border-collapse: collapse; table-layout: auto; }
                .membrete-logo { width: 64px; vertical-align: top; padding-right: 8px; }
                .membrete-logo img { width: 64px; height: 64px; object-fit: contain; }
                .membrete-info { vertical-align: top; }
                .membrete-right { width: 280px; vertical-align: top; text-align: right; }
                .membrete-title { font-size: 12px; font-weight: bold; margin: 0; line-height: 1.2; }
                .membrete-line { font-size: 9px; margin: 0; line-height: 1.2; color: #333; }
                .membrete-meta { font-size: 9px; color: #555; word-break: break-word; }
                .muted { color:#666; font-size: 11px; }
                .box { border:1px solid #ddd; border-radius:6px; padding:10px; margin:10px 0; }
                table { width:100%; border-collapse: collapse; }
                th, td { padding:6px 8px; border-bottom:1px solid #eee; text-align:left; }
                thead th { background:#f8f9fa; border-bottom:1px solid #ddd; }
                .right { text-align:right; }
                .success { color:#198754; }
                .danger { color:#dc3545; }
            </style>
        </head>
        <body>
            <div class="membrete">
                <table class="membrete-table">
                    <tr>
                        <td class="membrete-logo">
                            <?php if (!empty($logoDataUri)): ?>
                                <img src="<?= htmlspecialchars($logoDataUri) ?>" alt="Logo">
                            <?php endif; ?>
                        </td>
                        <td class="membrete-info">
                            <p class="membrete-title"><?= htmlspecialchars($committeeName) ?></p>
                            <?php if ($regRucLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($regRucLine) ?></p>
                            <?php endif; ?>
                            <?php if ($locationLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($locationLine) ?></p>
                            <?php endif; ?>
                            <?php if ($contactLine !== ''): ?>
                                <p class="membrete-line"><?= htmlspecialchars($contactLine) ?></p>
                            <?php endif; ?>
                        </td>
                        <td class="membrete-right">
                            <p class="membrete-title">Flujo de caja</p>
                            <div class="membrete-meta"><strong>Total ingresos:</strong> <?= format_currency($data['totalIn'] ?? 0) ?></div>
                            <div class="membrete-meta"><strong>Total egresos:</strong> <?= format_currency($data['totalOut'] ?? 0) ?></div>
                            <?php $net = (float)($data['net'] ?? 0); $cls = $net>=0?'success':'danger'; ?>
                            <div class="membrete-meta"><strong>Neto:</strong> <span class="<?= $cls ?>"><?= format_currency($net) ?></span></div>
                            <div class="membrete-meta"><strong>Generado:</strong> <?= htmlspecialchars($generatedAt) ?></div>
                            <div class="membrete-meta"><strong>Período:</strong> <?= htmlspecialchars($periodLine) ?></div>
                        </td>
                    </tr>
                </table>
            </div>

            <div class="box">
                <table>
                    <tbody>
                        <tr>
                            <td><strong>Saldo inicial</strong></td>
                            <td class="right"><?= format_currency($data['initialBalance'] ?? 0) ?></td>
                            <td><strong>Movimientos</strong></td>
                            <td class="right"><?= (int)(($data['countIn'] ?? 0) + ($data['countOut'] ?? 0)) ?></td>
                        </tr>
                        <tr>
                            <td><strong>Ctas. por cobrar</strong></td>
                            <td class="right"><?= format_currency($data['accountsReceivable'] ?? 0) ?> (<?= (int)($data['debtorsCount'] ?? 0) ?> deud.)</td>
                            <td><strong>Egresos próximos (mes)</strong></td>
                            <td class="right"><?= format_currency($data['payablesMonth'] ?? 0) ?> (Próx.7d: <?= format_currency($data['payablesNext7'] ?? 0) ?>)</td>
                        </tr>
                    </tbody>
                </table>
            </div>

            <div class="box">
                <strong>Libro de Caja</strong>
                <table>
                    <thead>
                        <tr>
                            <th style="width:16%">Fecha</th>
                            <th style="width:14%">Tipo</th>
                            <th>Detalle</th>
                            <th class="right" style="width:16%">Ingreso</th>
                            <th class="right" style="width:16%">Egreso</th>
                            <th class="right" style="width:16%">Saldo</th>
                        </tr>
                    </thead>
                    <tbody>
                        <tr>
                            <td><?= htmlspecialchars(format_datetime(($fromDate ?? '') . ' 00:00:00')) ?></td>
                            <td>Saldo inicial</td>
                            <td class="muted">Saldo acumulado antes de este período</td>
                            <td class="right"></td>
                            <td class="right"></td>
                            <td class="right"><strong><?= format_currency($initial) ?></strong></td>
                        </tr>
                        <?php $running = $initial; if ($ledger): foreach ($ledger as $row): $amt=(float)($row['amount']??0); $sign=(int)($row['sign']??1); $running += ($sign>=0?$amt:-$amt); ?>
                        <tr>
                            <td><?= htmlspecialchars(format_datetime($row['date'] ?? '')) ?></td>
                            <td><?= htmlspecialchars($row['type'] ?? '') ?></td>
                            <td><?= htmlspecialchars($row['detail'] ?? '') ?></td>
                            <td class="right"><?= $sign>=0 ? format_currency($amt) : '' ?></td>
                            <td class="right"><?= $sign<0 ? format_currency($amt) : '' ?></td>
                            <td class="right"><strong><?= format_currency($running) ?></strong></td>
                        </tr>
                        <?php endforeach; else: ?>
                        <tr><td colspan="6" class="muted">Sin movimientos en el período seleccionado.</td></tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </body>
        </html>
        <?php
        return (string)ob_get_clean();
    }

    private function exportRecaudacionCsv(array $rows, string $fromDate, string $toDate): void
    {
        $filename = 'recaudacion_' . $fromDate . '_a_' . $toDate . '.csv';
        header('Content-Type: text/csv; charset=UTF-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        // BOM para Excel en Windows
        echo "\xEF\xBB\xBF";
        $out = fopen('php://output', 'w');
        fputcsv($out, ['Fecha', 'Recibo', 'Factura', 'Cliente', 'Método', 'Monto', 'Cajero']);
        foreach ($rows as $r) {
            fputcsv($out, [
                $r['payment_date'] ?? '',
                $r['receipt_number'] ?? '',
                $r['invoice_number'] ?? '',
                $r['customer_name'] ?? '',
                $r['method'] ?? '',
                number_format((float)($r['amount'] ?? 0), 2, '.', ''),
                $r['cashier_name'] ?? '',
            ]);
        }
        fclose($out);
    }

    private function exportFlujoCajaCsv(array $rowsIn, array $rowsOut, string $fromDate, string $toDate): void
    {
        $filename = 'flujocaja_' . $fromDate . '_a_' . $toDate . '.csv';
        header('Content-Type: text/csv; charset=UTF-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        echo "\xEF\xBB\xBF"; // BOM
        $out = fopen('php://output', 'w');
        fputcsv($out, ['Fecha', 'Tipo', 'Detalle', 'Método/Categoría', 'Monto']);
        // Unificar y ordenar
        $ledger = [];
        foreach ($rowsIn as $r) {
            $ledger[] = [
                'date' => (string)($r['payment_date'] ?? ''),
                'type' => 'Ingreso',
                'detail' => 'Recibo ' . ($r['receipt_number'] ?? ('#' . ($r['id'] ?? ''))) . ' — ' . ($r['customer_name'] ?? ''),
                'meta' => (string)($r['method'] ?? ''),
                'amount' => (float)($r['amount'] ?? 0),
            ];
        }
        foreach ($rowsOut as $r) {
            $ledger[] = [
                'date' => (string)($r['expense_date'] ?? ''),
                'type' => 'Egreso',
                'detail' => trim(($r['subcategory_name'] ?? '') . (($r['vendor'] ?? '') !== '' ? (' — ' . $r['vendor']) : '')),
                'meta' => (string)($r['category_name'] ?? ''),
                'amount' => (float)($r['amount'] ?? 0),
            ];
        }
        usort($ledger, function($a, $b){
            $ta = strtotime($a['date'] ?? '') ?: 0;
            $tb = strtotime($b['date'] ?? '') ?: 0;
            if ($ta === $tb) return 0; return $ta <=> $tb;
        });
        foreach ($ledger as $l) {
            fputcsv($out, [
                $l['date'], $l['type'], $l['detail'], $l['meta'], number_format((float)$l['amount'], 2, '.', ''),
            ]);
        }
        fclose($out);
    }
}
User / IP	: 216.73.217.33
Host / Server	: 45.84.207.204 / aircan.me
System	: Linux lt-bnk-web1726.main-hosting.eu 5.14.0-611.36.1.el9_7.x86_64 #1 SMP PREEMPT_DYNAMIC Tue Mar 3 11:23:52 EST 2026 x86_64