<?php
/**
 * agent_statement.php - Customer (Agent) Statement
 *
 * A standard open-item statement of account:
 *   - Opening balance brought forward (everything before the period)
 *   - Transaction ledger in date order with a RUNNING BALANCE
 *   - Aged analysis (Current / 30 / 60 / 90 / 120+ days)
 *   - Closing balance and total due
 *
 * DOCUMENTS INCLUDED (industry standard - only issued documents appear):
 *   INVOICES      debit   only bookings where orders.invoiced = 1
 *   CREDIT NOTES  credit  fdms_credit_notes (successfully fiscalized)
 *   PAYMENTS      credit  accounting_payment_allocations from posted bulk
 *                         payments, allocated to that agent's bookings
 *
 * DELIBERATELY EXCLUDED:
 *   - Uninvoiced bookings. A booking that has not been invoiced is not yet a
 *     receivable and must never appear on a statement of account - it would
 *     overstate the debt and the customer cannot reconcile it.
 *   - Voided bulk payments and failed credit notes (they never happened).
 *
 * Usage:
 *   agent_statement.php                          -> agent picker
 *   agent_statement.php?agent_id=5               -> on-screen statement
 *   agent_statement.php?agent_id=5&format=pdf    -> PDF
 *   &date_from=YYYY-MM-DD&date_to=YYYY-MM-DD     -> statement period
 *   &open_only=1                                 -> outstanding items only
 */

require_once __DIR__ . '/../../includes/bootstrap.php';
require_login();

$pageTitle = 'Customer Statement';

if (!function_exists('h')) {
    function h($v): string { return htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8'); }
}
if (!function_exists('money_fmt')) {
    function money_fmt($a): string { return number_format((float)$a, 2); }
}
if (!function_exists('stmt_table_exists')) {
    function stmt_table_exists(PDO $pdo, string $t): bool {
        static $c = [];
        if (array_key_exists($t, $c)) return $c[$t];
        try {
            $s = $pdo->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t");
            $s->execute([':t' => $t]);
            $c[$t] = ((int)$s->fetchColumn() > 0);
        } catch (Throwable $e) { $c[$t] = false; }
        return $c[$t];
    }
}
if (!function_exists('stmt_column_exists')) {
    function stmt_column_exists(PDO $pdo, string $t, string $col): bool {
        static $c = [];
        $k = $t . '.' . $col;
        if (array_key_exists($k, $c)) return $c[$k];
        try {
            $s = $pdo->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t AND COLUMN_NAME = :c");
            $s->execute([':t' => $t, ':c' => $col]);
            $c[$k] = ((int)$s->fetchColumn() > 0);
        } catch (Throwable $e) { $c[$k] = false; }
        return $c[$k];
    }
}
if (!function_exists('stmt_first_nonempty')) {
    function stmt_first_nonempty(array $row, array $cands): string {
        foreach ($cands as $c) {
            if (isset($row[$c]) && trim((string)$row[$c]) !== '') return trim((string)$row[$c]);
        }
        return '';
    }
}

$agentId  = (int)($_GET['agent_id'] ?? 0);
$dateFrom = trim((string)($_GET['date_from'] ?? ''));
$dateTo   = trim((string)($_GET['date_to'] ?? date('Y-m-d')));
$format   = (string)($_GET['format'] ?? 'html');
$openOnly = !empty($_GET['open_only']) ? 1 : 0;

if ($dateFrom === '') $dateFrom = date('Y-m-d', strtotime('-12 months'));

$hasAgents   = stmt_table_exists($pdo, 'agents') && stmt_column_exists($pdo, 'orders', 'agent_id');
$hasInvoiced = stmt_column_exists($pdo, 'orders', 'invoiced');
$hasCredits  = stmt_table_exists($pdo, 'fdms_credit_notes');
$hasFdms     = stmt_table_exists($pdo, 'fdms_fiscal_invoices');
$hasBulk     = stmt_table_exists($pdo, 'accounting_bulk_payments') && stmt_table_exists($pdo, 'accounting_payment_allocations');

$errors = [];
$agentsList = [];
if ($hasAgents) {
    try {
        $agentsList = $pdo->query("SELECT id, TRIM(CONCAT(COALESCE(firstname,''),' ',COALESCE(lastname,''))) AS agent_name FROM agents ORDER BY firstname ASC LIMIT 1000")->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {}
}

/* =========================================================================
   Build the ledger
   ========================================================================= */
$agent = null;
$txns = [];           // every transaction, both periods
$openingBalance = 0.0;
$periodTxns = [];
$totals = ['invoices' => 0.0, 'credits' => 0.0, 'payments' => 0.0];
$aged = ['current' => 0.0, 'd30' => 0.0, 'd60' => 0.0, 'd90' => 0.0, 'd120' => 0.0];
$currency = 'USD';

if ($agentId > 0 && $hasAgents) {
    try {
        $a = $pdo->prepare("SELECT * FROM agents WHERE id = :id LIMIT 1");
        $a->execute([':id' => $agentId]);
        $agent = $a->fetch(PDO::FETCH_ASSOC) ?: null;
        if (!$agent) throw new RuntimeException('Agent not found.');

        if (!$hasInvoiced) {
            throw new RuntimeException('The orders.invoiced column does not exist yet. Open the Accounting page once - it creates the column - then print an invoice for at least one booking.');
        }

        /* -----------------------------------------------------------------
           1. INVOICES (debits). ONLY invoiced bookings.
              The invoice date is orders.invoiced_at where available (the day
              the document was raised), falling back to check_out - never the
              booking date, since the receivable arises when invoiced.
              The invoice VALUE is the fiscal invoice amount when a fiscal
              invoice exists (that is the document the customer holds),
              otherwise the master folio's charges.
           ----------------------------------------------------------------- */
        $invoicedAtExpr = stmt_column_exists($pdo, 'orders', 'invoiced_at')
            ? "COALESCE(DATE(o.invoiced_at), DATE(o.check_out))"
            : "DATE(o.check_out)";

        $fiscalAmountJoin = '';
        $fiscalAmountExpr = 'NULL';
        $fiscalNoExpr = 'NULL';
        $replacesExpr = '0';
        if ($hasFdms) {
            $hasReplacesCol = stmt_column_exists($pdo, 'fdms_fiscal_invoices', 'replaces_credit_note_id');
            $replacesSel = $hasReplacesCol ? "i1.replaces_credit_note_id" : "NULL";
            $fiscalAmountJoin = "
                LEFT JOIN (
                    SELECT i1.order_id, i1.amount_including_tax, i1.invoice_no, i1.currency,
                           {$replacesSel} AS replaces_credit_note_id
                    FROM fdms_fiscal_invoices i1
                    INNER JOIN (
                        SELECT order_id, MAX(id) AS max_id FROM fdms_fiscal_invoices WHERE success = 1 GROUP BY order_id
                    ) i2 ON i2.order_id = i1.order_id AND i2.max_id = i1.id
                ) fi ON fi.order_id = o.id";
            $fiscalAmountExpr = 'fi.amount_including_tax';
            $fiscalNoExpr = 'fi.invoice_no';
            $replacesExpr = 'COALESCE(fi.replaces_credit_note_id, 0)';
        }

        $sql = "
            SELECT o.id AS order_id, o.order_no, o.check_in, o.check_out,
                   {$invoicedAtExpr} AS doc_date,
                   COALESCE({$fiscalAmountExpr}, f.total_charges, o.totalamount, 0) AS amount,
                   COALESCE({$fiscalNoExpr}, o.order_no, CONCAT('RZ-', o.id)) AS doc_no,
                   {$replacesExpr} AS replaces_credit_note_id,
                   TRIM(CONCAT(COALESCE(g.firstname,''),' ',COALESCE(g.lastname,''))) AS guest_name,
                   o.currency
            FROM orders o
            LEFT JOIN guests g ON g.id = o.guest_id
            LEFT JOIN folios f
                ON f.order_id = o.id
               AND LOWER(COALESCE(f.folio_type, '')) = 'master'
            {$fiscalAmountJoin}
            WHERE o.agent_id = :agent_id
              AND (
                    DATE(o.check_in) < :legacy_cutoff
                    OR COALESCE(o.invoiced, 0) = 1
                  )
              AND COALESCE(o.status, 1) NOT IN (0, 5)
            ORDER BY doc_date ASC, o.id ASC";
        $st = $pdo->prepare($sql);
        $st->execute([
            ':agent_id' => $agentId,
            ':legacy_cutoff' => '2026-07-01',
        ]);
        foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $r) {
            $amt = round((float)$r['amount'], 0);
            if ($amt == 0.0) continue;
            if (trim((string)($r['currency'] ?? '')) !== '') $currency = strtoupper(trim((string)$r['currency']));
            $isReplacement = ((int)($r['replaces_credit_note_id'] ?? 0) > 0);
            $txns[] = [
                'date' => (string)$r['doc_date'],
                'type' => 'Invoice',
                'doc_no' => (string)$r['doc_no'],
                'ref' => ($isReplacement ? 'Replacement - ' : '') . 'Booking ' . (string)(($r['order_no'] ?? '') !== '' ? $r['order_no'] : ('#' . $r['order_id'])),
                'detail' => trim((string)($r['guest_name'] ?? '')) . '  ' . substr((string)$r['check_in'],0,10) . ' - ' . substr((string)$r['check_out'],0,10),
                'debit' => $amt,
                'credit' => 0.0,
                'order_id' => (int)$r['order_id'],
                // 1 = ordinary invoice, 3 = replacement (must follow its credit note)
                'sort' => $isReplacement ? 3 : 1,
            ];
        }

        /* -----------------------------------------------------------------
           2. CREDIT NOTES (credits). Successfully fiscalized only, and only
              against this agent's invoiced bookings.
              A partial correction's REPLACEMENT invoice is already picked up
              above as the booking's current fiscal invoice, so the ledger
              reads: original invoice, credit note, replacement invoice.
           ----------------------------------------------------------------- */
        if ($hasCredits) {
            $cn = $pdo->prepare("
                SELECT cn.id, cn.created_at, cn.original_invoice_no, cn.amount_including_tax,
                       cn.credit_type, cn.reason_text, cn.zimra_credit_no, cn.order_id,
                       o.order_no
                FROM fdms_credit_notes cn
                INNER JOIN orders o ON o.id = cn.order_id
                WHERE o.agent_id = :agent_id
                  AND cn.success = 1
                  AND COALESCE(o.invoiced, 0) = 1
                ORDER BY cn.id ASC");
            $cn->execute([':agent_id' => $agentId]);
            foreach ($cn->fetchAll(PDO::FETCH_ASSOC) as $r) {
                $amt = round((float)$r['amount_including_tax'], 2);
                if ($amt <= 0) continue;
                $txns[] = [
                    'date' => substr((string)$r['created_at'], 0, 10),
                    'type' => 'Credit Note',
                    'doc_no' => 'CN-' . (int)$r['id'] . ((string)($r['zimra_credit_no'] ?? '') !== '' ? ' / ' . $r['zimra_credit_no'] : ''),
                    'ref' => 'Credits ' . (string)$r['original_invoice_no'],
                    'detail' => ((string)$r['credit_type'] === 'correction' ? 'Correction - replacement invoice raised. ' : '') . (string)($r['reason_text'] ?? ''),
                    'debit' => 0.0,
                    'credit' => $amt,
                    'order_id' => (int)$r['order_id'],
                    'sort' => 2,
                ];
            }
        }

        /* -----------------------------------------------------------------
           3. PAYMENTS (credits).

           Read directly from non-voided MASTER FOLIO transactions so the
           statement reflects every payment shown against the booking, including:
             - ordinary/manual booking payments
             - imported Semper payments
             - deposits and receipts
             - bulk-payment allocations
             - other valid master-folio payment postings

           Room and Guest folios remain excluded.

           Using folio_transactions as the single payment source also avoids
           relying on accounting_payment_allocations, which only exists for
           payments posted through the Accounting bulk-payment workflow.
           ----------------------------------------------------------------- */
        if (stmt_table_exists($pdo, 'folio_transactions') && stmt_table_exists($pdo, 'folios')) {
            $hasIsVoided = stmt_column_exists($pdo, 'folio_transactions', 'is_voided');

            $paymentVoidCondition = $hasIsVoided
                ? "AND COALESCE(ft.is_voided, 0) = 0"
                : "";

            $pm = $pdo->prepare("
                SELECT
                    ft.id,
                    ft.order_id,
                    ft.tx_date,
                    ft.tx_type,
                    ft.description,
                    ft.credit,
                    ft.source_table,
                    ft.source_id,
                    o.order_no
                FROM folio_transactions ft
                INNER JOIN folios pf
                    ON pf.id = ft.folio_id
                   AND pf.order_id = ft.order_id
                   AND LOWER(COALESCE(pf.folio_type, '')) = 'master'
                INNER JOIN orders o
                    ON o.id = ft.order_id
                WHERE o.agent_id = :agent_id
                  AND (
                        DATE(o.check_in) < :legacy_cutoff
                        OR COALESCE(o.invoiced, 0) = 1
                      )
                  {$paymentVoidCondition}
                  AND COALESCE(ft.credit, 0) > 0
                  AND LOWER(COALESCE(ft.tx_type, '')) IN (
                        'payment',
                        'receipt',
                        'deposit',
                        'payment_received',
                        'folio_payment',
                        'guest_payment'
                      )
                  AND LOWER(COALESCE(ft.tx_type, '')) NOT IN (
                        'payment_void',
                        'void_payment',
                        'payment_reversal',
                        'reversal',
                        'refund',
                        'extra',
                        'payment_refund'
                      )
                  AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%void%'
                  AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%reversal%'
                  AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%refund%'
                 AND LOWER(COALESCE(ft.source_table, ''))NOT LIKE '%semper_import%'
                  /*
                   * Statement payment policy:
                   * - Include only Bank Transfer payments.
                   * - Exclude imported Semper payments.
                   * - Exclude Room Balance transfers.
                   * - Exclude FCB Swipe payments.
                   */
                  AND (
                        LOWER(COALESCE(ft.description, '')) LIKE '%bank transfer%'
                        OR LOWER(COALESCE(ft.tx_type, '')) = 'bank_transfer'
                        OR LOWER(COALESCE(ft.source_table, '')) = 'bank_transfer'
                      )
                /*  AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%semper payment%'
                  AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%room balance%'
                  AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%fcb swipe%'*/
                ORDER BY ft.tx_date ASC, ft.id ASC
            ");

            $pm->execute([
                ':agent_id' => $agentId,
                ':legacy_cutoff' => '2026-07-01',
            ]);

            foreach ($pm->fetchAll(PDO::FETCH_ASSOC) as $r) {
                $amt = round((float)$r['credit'], 2);
                if ($amt <= 0) continue;

                $sourceTable = trim((string)($r['source_table'] ?? ''));
                $sourceId = (int)($r['source_id'] ?? 0);
                $description = trim((string)($r['description'] ?? ''));

                $documentNo = 'PMT-' . (int)$r['id'];
                if ($sourceTable !== '' && $sourceId > 0) {
                    $documentNo = strtoupper(substr($sourceTable, 0, 8)) . '-' . $sourceId;
                }

                $txns[] = [
                    'date' => substr((string)$r['tx_date'], 0, 10),
                    'type' => 'Payment',
                    'doc_no' => $documentNo,
                    'ref' => 'Allocated to ' . (string)(
                        ($r['order_no'] ?? '') !== ''
                            ? $r['order_no']
                            : ('#' . $r['order_id'])
                    ),
                    'detail' => $description !== ''
                        ? $description
                        : ucwords(str_replace('_', ' ', (string)$r['tx_type'])),
                    'debit' => 0.0,
                    'credit' => $amt,
                    'order_id' => (int)$r['order_id'],
                    'sort' => 4,
                ];
            }
        }

        /* -----------------------------------------------------------------
           Order the ledger, split opening vs period, run the balance.

           Same-day ordering (sort key):
             1 = invoice, 2 = credit note, 3 = replacement invoice, 4 = payment
           A REPLACEMENT invoice must print AFTER the credit note that spawned
           it, so a correction reads in the order it happened:
               original invoice -> credit note -> replacement invoice
           Printing the replacement first would read as "charged twice, then
           credited", which is backwards and unreconcilable for the customer.
           Payments come last so a payment never appears to settle an invoice
           raised later that same day.
           ----------------------------------------------------------------- */
        usort($txns, function ($x, $y) {
            if ($x['date'] !== $y['date']) return strcmp($x['date'], $y['date']);
            if ($x['sort'] !== $y['sort']) return $x['sort'] - $y['sort'];
            return 0;
        });

        foreach ($txns as $t) {
            if ($t['date'] < $dateFrom) {
                $openingBalance += $t['debit'] - $t['credit'];
            }
        }
        $openingBalance = round($openingBalance, 2);

        $running = $openingBalance;
        foreach ($txns as $t) {
            if ($t['date'] < $dateFrom || $t['date'] > $dateTo) continue;
            $running = round($running + $t['debit'] - $t['credit'], 2);
            $t['balance'] = $running;
            $periodTxns[] = $t;
            $totals['invoices'] += $t['debit'];
            if ($t['type'] === 'Credit Note') $totals['credits'] += $t['credit'];
            if ($t['type'] === 'Payment') $totals['payments'] += $t['credit'];
        }
        $closingBalance = $running;

        /* -----------------------------------------------------------------
           AGED ANALYSIS - standard practice: age the OUTSTANDING debt by
           invoice date, settling credits against the OLDEST invoices first
           (FIFO), which is how open-item statements and most agents' own
           ledgers reconcile.
           ----------------------------------------------------------------- */
        $openInvoices = [];
        $creditPool = 0.0;
        foreach ($txns as $t) {
            if ($t['date'] > $dateTo) continue;
            if ($t['debit'] > 0) $openInvoices[] = ['date' => $t['date'], 'amount' => $t['debit']];
            else $creditPool += $t['credit'];
        }
        // FIFO: oldest invoices absorbed first
        foreach ($openInvoices as &$inv) {
            if ($creditPool <= 0) break;
            $applied = min($creditPool, $inv['amount']);
            $inv['amount'] = round($inv['amount'] - $applied, 2);
            $creditPool = round($creditPool - $applied, 2);
        }
        unset($inv);

        $asOf = strtotime($dateTo);
        foreach ($openInvoices as $inv) {
            if ($inv['amount'] <= 0.005) continue;
            $ageDays = (int)floor(($asOf - strtotime($inv['date'])) / 86400);
            if ($ageDays < 30)       $aged['current'] += $inv['amount'];
            elseif ($ageDays < 60)   $aged['d30'] += $inv['amount'];
            elseif ($ageDays < 90)   $aged['d60'] += $inv['amount'];
            elseif ($ageDays < 120)  $aged['d90'] += $inv['amount'];
            else                     $aged['d120'] += $inv['amount'];
        }
        foreach ($aged as $k => $v) $aged[$k] = round($v, 2);

        // Unapplied credit sits against the customer (a negative balance)
        $unappliedCredit = round($creditPool, 2);

    } catch (Throwable $e) {
        $errors[] = $e->getMessage();
        $agent = null;
    }
}

$agentName = $agent ? (trim(stmt_first_nonempty($agent, ['firstname']) . ' ' . stmt_first_nonempty($agent, ['lastname'])) ?: stmt_first_nonempty($agent, ['agent','name','company','company_name'])) : '';
$agentCompany = $agent ? stmt_first_nonempty($agent, ['company','company_name','registered_name','trade_name']) : '';
$agentAddress = $agent ? stmt_first_nonempty($agent, ['address','address1','physical_address']) : '';
$agentEmail   = $agent ? stmt_first_nonempty($agent, ['email']) : '';
$agentVat     = $agent ? stmt_first_nonempty($agent, ['vatno','vat_no','vat_number','vatref','vat_ref']) : '';
$agentTin     = $agent ? stmt_first_nonempty($agent, ['tinno','tin','tin_number','bp_no','bpno']) : '';

$property = null;
try { $property = $pdo->query("SELECT * FROM properties ORDER BY id ASC LIMIT 1")->fetch(PDO::FETCH_ASSOC) ?: null; } catch (Throwable $e) {}
$propName    = $property ? stmt_first_nonempty($property, ['property_name','name','title']) : 'RoomZ Property';
$propAddress = $property ? stmt_first_nonempty($property, ['address','address1','physical_address']) : '';
$propPhone   = $property ? stmt_first_nonempty($property, ['phone','tel','contact_no']) : '';
$propEmail   = $property ? stmt_first_nonempty($property, ['email']) : '';
$propVat     = $property ? stmt_first_nonempty($property, ['vat_no','vat_number','vatref']) : '';
$propTin     = $property ? stmt_first_nonempty($property, ['tin','tin_number','bp_no','bpno']) : '';

$closingBalance = $closingBalance ?? 0.0;
$unappliedCredit = $unappliedCredit ?? 0.0;

/* =========================================================================
   PDF OUTPUT
   ========================================================================= */
if ($format === 'pdf' && $agent && !$errors) {
    foreach ([
        __DIR__ . '/../../includes/fpdf/fpdf.php',
        __DIR__ . '/../../includes/fpdf.php',
        __DIR__ . '/../../vendor/setasign/fpdf/fpdf.php',
        __DIR__ . '/../../libs/fpdf/fpdf.php',
    ] as $cand) {
        if (!class_exists('FPDF') && is_file($cand)) { require_once $cand; break; }
    }
    if (!class_exists('FPDF')) { http_response_code(500); exit('FPDF library not found.'); }

    if (!function_exists('stmt_pdf_text')) {
        function stmt_pdf_text($value): string {
            $value = (string)$value;
            $converted = @iconv('UTF-8', 'ISO-8859-1//TRANSLIT', $value);
            return $converted !== false ? $converted : $value;
        }
    }

    class StatementPDF extends FPDF
    {
        public $propName = '';
        public $propAddress = '';
        public $propPhone = '';
        public $propEmail = '';
        public $propVat = '';
        public $propTin = '';

        function Header()
        {
            $this->SetFillColor(15, 23, 42);
            $this->Rect(0, 0, 297, 31, 'F');

            $this->SetXY(12, 7);
            $this->SetTextColor(255, 255, 255);
            $this->SetFont('Arial', 'B', 16);
            $this->Cell(175, 7, stmt_pdf_text($this->propName), 0, 1, 'L');

            // Property address on its own line.
            if ($this->propAddress !== '') {
                $this->SetX(12);
                $this->SetFont('Arial', '', 7.5);
                $this->Cell(175, 4, stmt_pdf_text($this->propAddress), 0, 1, 'L');
            }

            // Telephone and email on the next line.
            $contact = [];
            if ($this->propPhone !== '') $contact[] = 'Tel: ' . $this->propPhone;
            if ($this->propEmail !== '') $contact[] = 'Email: ' . $this->propEmail;
            if ($contact) {
                $this->SetX(12);
                $this->SetFont('Arial', '', 7.5);
                $this->Cell(175, 4, stmt_pdf_text(implode('  |  ', $contact)), 0, 1, 'L');
            }

            // Lodge tax references on their own line below the contact line.
            $tax = [];
            if ($this->propTin !== '') $tax[] = 'TIN/BP: ' . $this->propTin;
            if ($this->propVat !== '') $tax[] = 'VAT No: ' . $this->propVat;
            if ($tax) {
                $this->SetX(12);
                $this->SetFont('Arial', 'B', 7.5);
                $this->Cell(175, 4, stmt_pdf_text(implode('  |  ', $tax)), 0, 1, 'L');
            }

            $this->SetXY(194, 8);
            $this->SetFont('Arial', 'B', 17);
            $this->Cell(91, 8, 'STATEMENT OF ACCOUNT', 0, 1, 'R');

            $this->SetXY(194, 19);
            $this->SetFont('Arial', '', 8);
            $this->SetTextColor(210, 220, 235);
            $this->Cell(91, 4, 'Customer ledger, allocations and aged analysis', 0, 1, 'R');

            $this->SetTextColor(0, 0, 0);
            $this->SetY(36);
        }

        function Footer()
        {
            $this->SetY(-11);
            $this->SetDrawColor(203, 213, 225);
            $this->Line(10, $this->GetY(), 287, $this->GetY());
            $this->SetY(-9);
            $this->SetFont('Arial', 'I', 7);
            $this->SetTextColor(100, 116, 139);
            $this->Cell(
                0,
                4,
                'Page ' . $this->PageNo() . '/{nb}  |  Generated ' . date('d/m/Y H:i') . '  |  E&OE',
                0,
                0,
                'C'
            );
            $this->SetTextColor(0, 0, 0);
        }

        function LedgerHead()
        {
            $this->SetFont('Arial', 'B', 7.5);
            $this->SetFillColor(30, 41, 59);
            $this->SetTextColor(255, 255, 255);
            $this->Cell(22, 7, 'Date', 1, 0, 'C', true);
            $this->Cell(23, 7, 'Type', 1, 0, 'C', true);
            $this->Cell(40, 7, 'Document', 1, 0, 'L', true);
            $this->Cell(62, 7, 'Reference', 1, 0, 'L', true);
            $this->Cell(64, 7, 'Detail', 1, 0, 'L', true);
            $this->Cell(22, 7, 'Debit', 1, 0, 'R', true);
            $this->Cell(22, 7, 'Credit', 1, 0, 'R', true);
            $this->Cell(22, 7, 'Balance', 1, 1, 'R', true);
            $this->SetTextColor(0, 0, 0);
        }
    }

    $pdf = new StatementPDF('L', 'mm', 'A4');
    $pdf->propName = $propName;
    $pdf->propAddress = $propAddress;
    $pdf->propPhone = $propPhone;
    $pdf->propEmail = $propEmail;
    $pdf->propVat = $propVat;
    $pdf->propTin = $propTin;
    $pdf->AliasNbPages();
    $pdf->SetMargins(10, 10, 10);
    $pdf->SetAutoPageBreak(true, 18);
    $pdf->AddPage();

    /* Agent and statement details */
    $boxY = $pdf->GetY();
    $leftW = 175;
    $rightW = 99;

    $pdf->SetFillColor(241, 245, 249);
    $pdf->SetFont('Arial', 'B', 8);
    $pdf->Cell($leftW, 6, ' STATEMENT TO', 1, 0, 'L', true);
    $pdf->Cell(3, 6, '', 0, 0);
    $pdf->Cell($rightW, 6, ' STATEMENT DETAILS', 1, 1, 'L', true);

    $contentY = $pdf->GetY();
    $pdf->Rect(10, $contentY, $leftW, 28);
    $pdf->Rect(188, $contentY, $rightW, 28);

    $pdf->SetXY(13, $contentY + 3);
    $pdf->SetFont('Arial', 'B', 11);
    $pdf->Cell(168, 5, stmt_pdf_text($agentCompany !== '' ? $agentCompany : $agentName), 0, 1, 'L');

    $pdf->SetFont('Arial', '', 8);
    if ($agentCompany !== '' && $agentName !== '' && $agentName !== $agentCompany) {
        $pdf->SetX(13);
        $pdf->Cell(168, 4, stmt_pdf_text('Attn: ' . $agentName), 0, 1, 'L');
    }
    if ($agentAddress !== '') {
        $pdf->SetX(13);
        $pdf->Cell(168, 4, stmt_pdf_text($agentAddress), 0, 1, 'L');
    }
    if ($agentEmail !== '') {
        $pdf->SetX(13);
        $pdf->Cell(168, 4, stmt_pdf_text($agentEmail), 0, 1, 'L');
    }

    $agentTax = [];
    if ($agentVat !== '') $agentTax[] = 'VAT No: ' . $agentVat;
    if ($agentTin !== '') $agentTax[] = 'TIN/BP No: ' . $agentTin;
    if ($agentTax) {
        $pdf->SetX(13);
        $pdf->SetFont('Arial', 'B', 8);
        $pdf->Cell(168, 4.5, stmt_pdf_text(implode('  |  ', $agentTax)), 0, 1, 'L');
    }

    $detailY = $contentY + 2.5;
    foreach ([
        ['Statement Date', date('d/m/Y')],
        ['Period', date('d/m/Y', strtotime($dateFrom)) . ' - ' . date('d/m/Y', strtotime($dateTo))],
        ['Currency', $currency],
        ['Account', 'AG-' . $agentId],
    ] as $detailRow) {
        $pdf->SetXY(191, $detailY);
        $pdf->SetFont('Arial', 'B', 7.5);
        $pdf->Cell(27, 5, $detailRow[0] . ':', 0, 0, 'L');
        $pdf->SetFont('Arial', '', 8);
        $pdf->Cell(65, 5, stmt_pdf_text($detailRow[1]), 0, 1, 'L');
        $detailY += 5.5;
    }

    $pdf->SetY($contentY + 32);

    /* Summary cards */
    $cardW = 66.5;
    $cardGap = 3.5;
    $summaryCards = [
        ['Opening Balance', $openingBalance],
        ['Invoiced This Period', $totals['invoices']],
        ['Payments & Credits', -($totals['payments'] + $totals['credits'])],
        ['Total Due (' . $currency . ')', $closingBalance],
    ];

    foreach ($summaryCards as $idx => $card) {
        $x = 10 + ($idx * ($cardW + $cardGap));
        $pdf->SetXY($x, $pdf->GetY());
        $pdf->SetFillColor($idx === 3 ? 15 : 248, $idx === 3 ? 23 : 250, $idx === 3 ? 42 : 252);
        $pdf->SetTextColor($idx === 3 ? 255 : 71, $idx === 3 ? 255 : 85, $idx === 3 ? 255 : 105);
        $pdf->SetFont('Arial', 'B', 7.5);
        $pdf->Cell($cardW, 5.5, stmt_pdf_text($card[0]), 1, 2, 'L', true);
        $pdf->SetFont('Arial', 'B', 11);
        $pdf->Cell($cardW, 8, money_fmt($card[1]), 1, 0, 'R', true);
    }
    $pdf->SetTextColor(0, 0, 0);
    $pdf->Ln(12);

    /* Ledger */
    $pdf->LedgerHead();

    $pdf->SetFillColor(226, 232, 240);
    $pdf->SetFont('Arial', 'B', 7.5);
    $pdf->Cell(211, 6, 'Balance brought forward', 1, 0, 'L', true);
    $pdf->Cell(22, 6, '', 1, 0, 'R', true);
    $pdf->Cell(22, 6, '', 1, 0, 'R', true);
    $pdf->Cell(22, 6, money_fmt($openingBalance), 1, 1, 'R', true);

    $fill = false;
    $pdf->SetFont('Arial', '', 7.1);

    foreach ($periodTxns as $t) {
        if ($pdf->GetY() > 178) {
            $pdf->AddPage();
            $pdf->LedgerHead();
            $pdf->SetFont('Arial', '', 7.1);
        }

        $pdf->SetFillColor(248, 250, 252);
        $doc = (string)$t['doc_no'];
        $ref = (string)$t['ref'];
        $detail = (string)$t['detail'];

        if (strlen($doc) > 27) $doc = substr($doc, 0, 24) . '...';
        if (strlen($ref) > 43) $ref = substr($ref, 0, 40) . '...';
        if (strlen($detail) > 47) $detail = substr($detail, 0, 44) . '...';

        $pdf->Cell(22, 5.6, date('d/m/Y', strtotime($t['date'])), 1, 0, 'C', $fill);
        $pdf->Cell(23, 5.6, stmt_pdf_text($t['type']), 1, 0, 'C', $fill);
        $pdf->Cell(40, 5.6, stmt_pdf_text($doc), 1, 0, 'L', $fill);
        $pdf->Cell(62, 5.6, stmt_pdf_text($ref), 1, 0, 'L', $fill);
        $pdf->Cell(64, 5.6, stmt_pdf_text($detail), 1, 0, 'L', $fill);
        $pdf->Cell(22, 5.6, $t['debit'] > 0 ? money_fmt($t['debit']) : '', 1, 0, 'R', $fill);
        $pdf->Cell(22, 5.6, $t['credit'] > 0 ? money_fmt($t['credit']) : '', 1, 0, 'R', $fill);
        $pdf->Cell(22, 5.6, money_fmt($t['balance']), 1, 1, 'R', $fill);
        $fill = !$fill;
    }

    $pdf->SetFillColor(15, 23, 42);
    $pdf->SetTextColor(255, 255, 255);
    $pdf->SetFont('Arial', 'B', 9);
    $pdf->Cell(255, 7, 'TOTAL DUE (' . $currency . ')', 1, 0, 'R', true);
    $pdf->Cell(22, 7, money_fmt($closingBalance), 1, 1, 'R', true);
    $pdf->SetTextColor(0, 0, 0);

    if ($pdf->GetY() > 164) $pdf->AddPage();

    /* Aged analysis */
    $pdf->Ln(4);
    $pdf->SetFont('Arial', 'B', 8.5);
    $pdf->SetFillColor(241, 245, 249);
    $pdf->Cell(277, 6, 'AGED ANALYSIS AS AT ' . date('d/m/Y', strtotime($dateTo)), 1, 1, 'L', true);

    $agedWidth = 55.4;
    $pdf->SetFont('Arial', 'B', 8);
    foreach (['Current', '30 Days', '60 Days', '90 Days', '120+ Days'] as $label) {
        $pdf->Cell($agedWidth, 6, $label, 1, 0, 'C', true);
    }
    $pdf->Ln();
    $pdf->SetFont('Arial', 'B', 9);
    foreach (['current', 'd30', 'd60', 'd90', 'd120'] as $key) {
        $pdf->Cell($agedWidth, 7, money_fmt($aged[$key]), 1, 0, 'C');
    }
    $pdf->Ln(10);

    if ($unappliedCredit > 0.005) {
        $pdf->SetFillColor(236, 253, 245);
        $pdf->SetTextColor(20, 83, 45);
        $pdf->SetFont('Arial', 'B', 8);
        $pdf->Cell(
            277,
            6,
            stmt_pdf_text('Unapplied credit available: ' . $currency . ' ' . money_fmt($unappliedCredit)),
            1,
            1,
            'L',
            true
        );
        $pdf->SetTextColor(0, 0, 0);
        $pdf->Ln(2);
    }

    $pdf->SetFont('Arial', '', 7.5);
    $pdf->SetTextColor(71, 85, 105);
    $pdf->MultiCell(
        277,
        4,
        stmt_pdf_text(
            'Statement policy: bookings with check-in dates before 01/07/2026 are included when not cancelled, '
          . 'to support legacy Semper imports that did not populate orders.invoiced. Bookings from 01/07/2026 onward '
          . 'must have orders.invoiced = 1. Only Master Folio charges and qualifying Bank Transfer payments are included. Semper Payment, Room Balance, FCB Swipe, Room Folio and Guest Folio payments are excluded. Successfully fiscalized credit notes are included.'
        ),
        0,
        'L'
    );
    $pdf->SetTextColor(0, 0, 0);

    $safeAgent = preg_replace(
        '/[^A-Za-z0-9_\-]/',
        '_',
        ($agentCompany !== '' ? $agentCompany : $agentName)
    );
    $pdf->Output('I', 'Statement_' . $safeAgent . '_' . date('Ymd') . '.pdf');
    exit;
}

require_once __DIR__ . '/../../includes/header.php';
?>

<div class="hero-band">
    <div class="row g-4 align-items-center">
        <div class="col-lg-8">
            <div class="page-eyebrow text-white-50"><i class="bi bi-file-earmark-spreadsheet"></i> Accounting</div>
            <div class="hero-title mt-2">Customer Statement</div>
            <div class="hero-subtitle mt-2">Open-item statement of account: invoices, credit notes and payments, with aged analysis.</div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <a href="accounting.php" class="btn btn-light"><i class="bi bi-cash-coin me-2"></i>Accounting</a>
        </div>
    </div>
</div>

<?php foreach ($errors as $e): ?><div class="alert alert-danger"><?php echo h($e); ?></div><?php endforeach; ?>

<?php if (!$hasAgents): ?>
    <div class="alert alert-warning">No <code>agents</code> table (or <code>orders.agent_id</code>) found - statements require agent-linked bookings.</div>
<?php else: ?>

<div class="card panel-card mb-3">
    <div class="card-body">
        <form method="get" class="row g-2 align-items-end">
            <div class="col-md-4">
                <label class="form-label small mb-1">Agent *</label>
                <select name="agent_id" class="form-select form-select-sm" required>
                    <option value="">- select agent -</option>
                    <?php foreach ($agentsList as $ag): ?>
                        <option value="<?php echo (int)$ag['id']; ?>" <?php echo $agentId === (int)$ag['id'] ? 'selected' : ''; ?>><?php echo h($ag['agent_name']); ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-2">
                <label class="form-label small mb-1">From</label>
                <input type="date" name="date_from" value="<?php echo h($dateFrom); ?>" class="form-control form-control-sm">
            </div>
            <div class="col-md-2">
                <label class="form-label small mb-1">To</label>
                <input type="date" name="date_to" value="<?php echo h($dateTo); ?>" class="form-control form-control-sm">
            </div>
            <div class="col-md-4 d-flex gap-2">
                <button class="btn btn-sm btn-primary"><i class="bi bi-search me-1"></i>View Statement</button>
                <?php if ($agentId > 0): ?>
                    <a class="btn btn-sm btn-danger" target="_blank"
                       href="?<?php echo h(http_build_query(['agent_id'=>$agentId,'date_from'=>$dateFrom,'date_to'=>$dateTo,'format'=>'pdf'])); ?>">
                        <i class="bi bi-file-earmark-pdf me-1"></i>PDF
                    </a>
                <?php endif; ?>
            </div>
        </form>
    </div>
</div>

<?php if ($agent): ?>

<div class="row g-3 mb-3">
    <div class="col-md-3"><div class="card panel-card h-100"><div class="card-body py-2 px-3">
        <div class="small text-muted">Opening Balance</div>
        <div class="fw-bold fs-5"><?php echo money_fmt($openingBalance); ?></div>
    </div></div></div>
    <div class="col-md-3"><div class="card panel-card h-100"><div class="card-body py-2 px-3">
        <div class="small text-muted">Invoiced this period</div>
        <div class="fw-bold fs-5"><?php echo money_fmt($totals['invoices']); ?></div>
    </div></div></div>
    <div class="col-md-3"><div class="card panel-card h-100"><div class="card-body py-2 px-3">
        <div class="small text-muted">Payments &amp; Credits</div>
        <div class="fw-bold fs-5 text-success">-<?php echo money_fmt($totals['payments'] + $totals['credits']); ?></div>
    </div></div></div>
    <div class="col-md-3"><div class="card panel-card h-100 border-dark"><div class="card-body py-2 px-3">
        <div class="small text-muted">Total Due (<?php echo h($currency); ?>)</div>
        <div class="fw-bold fs-4 <?php echo $closingBalance > 0.005 ? 'text-danger' : 'text-success'; ?>"><?php echo money_fmt($closingBalance); ?></div>
    </div></div></div>
</div>

<div class="card panel-card mb-3">
    <div class="card-header d-flex justify-content-between align-items-center flex-wrap gap-2">
        <div>
            <span><i class="bi bi-list-columns me-2"></i>Statement of Account &mdash; <?php echo h($agentCompany !== '' ? $agentCompany : $agentName); ?></span>
            <?php if ($agentVat !== '' || $agentTin !== ''): ?>
                <div class="small text-muted mt-1">
                    <?php if ($agentVat !== ''): ?><strong>VAT No:</strong> <?php echo h($agentVat); ?><?php endif; ?>
                    <?php if ($agentVat !== '' && $agentTin !== ''): ?> &nbsp;|&nbsp; <?php endif; ?>
                    <?php if ($agentTin !== ''): ?><strong>TIN/BP No:</strong> <?php echo h($agentTin); ?><?php endif; ?>
                </div>
            <?php endif; ?>
        </div>
        <span class="small text-muted"><?php echo h(date('d/m/Y', strtotime($dateFrom))); ?> to <?php echo h(date('d/m/Y', strtotime($dateTo))); ?></span>
    </div>
    <div class="card-body p-0">
        <div class="table-responsive">
            <table class="table table-sm align-middle mb-0">
                <thead class="table-dark">
                    <tr><th>Date</th><th>Type</th><th>Document</th><th>Reference</th><th>Detail</th>
                        <th class="text-end">Debit</th><th class="text-end">Credit</th><th class="text-end">Balance</th></tr>
                </thead>
                <tbody>
                    <tr class="table-secondary">
                        <td colspan="7" class="fw-bold">Balance brought forward</td>
                        <td class="text-end fw-bold"><?php echo money_fmt($openingBalance); ?></td>
                    </tr>
                    <?php if (!$periodTxns): ?>
                        <tr><td colspan="8" class="text-muted small p-3">No invoiced transactions in this period.</td></tr>
                    <?php else: foreach ($periodTxns as $t): ?>
                        <tr class="<?php echo $t['type'] === 'Payment' ? 'table-success' : ($t['type'] === 'Credit Note' ? 'table-info' : ''); ?>">
                            <td class="small text-nowrap"><?php echo h(date('d/m/Y', strtotime($t['date']))); ?></td>
                            <td><span class="badge <?php echo $t['type'] === 'Invoice' ? 'text-bg-dark' : ($t['type'] === 'Payment' ? 'text-bg-success' : 'text-bg-info'); ?>"><?php echo h($t['type']); ?></span></td>
                            <td class="small"><?php echo h($t['doc_no']); ?></td>
                            <td class="small"><?php echo h($t['ref']); ?></td>
                            <td class="small text-muted"><?php echo h($t['detail']); ?></td>
                            <td class="text-end"><?php echo $t['debit'] > 0 ? money_fmt($t['debit']) : ''; ?></td>
                            <td class="text-end text-success"><?php echo $t['credit'] > 0 ? money_fmt($t['credit']) : ''; ?></td>
                            <td class="text-end fw-bold"><?php echo money_fmt($t['balance']); ?></td>
                        </tr>
                    <?php endforeach; endif; ?>
                </tbody>
                <tfoot>
                    <tr class="table-dark">
                        <td colspan="7" class="text-end fw-bold">TOTAL DUE (<?php echo h($currency); ?>)</td>
                        <td class="text-end fw-bold"><?php echo money_fmt($closingBalance); ?></td>
                    </tr>
                </tfoot>
            </table>
        </div>
    </div>
</div>

<div class="card panel-card mb-4">
    <div class="card-header"><i class="bi bi-hourglass-split me-2"></i>Aged Analysis (as at <?php echo h(date('d/m/Y', strtotime($dateTo))); ?>)</div>
    <div class="card-body p-0">
        <table class="table table-sm text-center mb-0">
            <thead><tr><th>Current</th><th>30 Days</th><th>60 Days</th><th>90 Days</th><th>120+ Days</th></tr></thead>
            <tbody><tr>
                <td><?php echo money_fmt($aged['current']); ?></td>
                <td><?php echo money_fmt($aged['d30']); ?></td>
                <td class="<?php echo $aged['d60'] > 0.005 ? 'text-warning fw-bold' : ''; ?>"><?php echo money_fmt($aged['d60']); ?></td>
                <td class="<?php echo $aged['d90'] > 0.005 ? 'text-danger fw-bold' : ''; ?>"><?php echo money_fmt($aged['d90']); ?></td>
                <td class="<?php echo $aged['d120'] > 0.005 ? 'text-danger fw-bold' : ''; ?>"><?php echo money_fmt($aged['d120']); ?></td>
            </tr></tbody>
        </table>
        <?php if ($unappliedCredit > 0.005): ?>
            <div class="p-2 small text-success border-top">
                <i class="bi bi-info-circle me-1"></i>Includes unapplied credit of <strong><?php echo money_fmt($unappliedCredit); ?></strong> available against future invoices.
            </div>
        <?php endif; ?>
    </div>
</div>

<div class="alert alert-light border small">
    <i class="bi bi-shield-check me-1"></i>
    This statement includes all non-cancelled legacy bookings with check-in dates before <strong>01/07/2026</strong>,
    even where the Semper import did not populate <code>orders.invoiced</code>. Bookings from 01/07/2026 onward must have
    <code>orders.invoiced = 1</code>. Only Master Folio charges and qualifying Bank Transfer payments are included. Semper Payment, Room Balance, FCB Swipe, Room Folio and Guest Folio payments are excluded. Successfully fiscalized credit notes are also included.
</div>

<?php endif; ?>
<?php endif; ?>

<style>
.hero-band{padding:10px 14px!important;margin-bottom:8px!important}
.hero-band .btn{padding:5px 9px!important;font-size:.78rem!important;border-radius:8px!important}
.panel-card .card-header{padding:9px 12px!important}
</style>

<?php require_once __DIR__ . '/../../includes/footer.php'; ?>
