<?php
/**
 * accounting.php - Accounting: Bulk Payments, Allocations & Fiscalization
 *
 * WHAT THIS MODULE DOES
 * 1. BOOKINGS BROWSER: search bookings by agent, folio no, order no, guest/pax
 *    name and period (arrival / departure / stay overlap), with live master
 *    folio charges / payments / balance per booking, and multi-select.
 * 2. BULK PAYMENT ALLOCATION: select multiple bookings, see each balance,
 *    enter the amount allocated to each, and post. Each allocation:
 *      - creates the master folio if the booking has none yet
 *      - inserts a row in the legacy `payment` table (so all existing
 *        reports/repair scripts keep working)
 *      - posts a folio_transactions payment (source_table='payment',
 *        source_id=payment.id - the SAME linkage the folio repair script
 *        uses, so re-running that script never double-posts these)
 *      - recalculates folio totals and syncs orders.payment_status
 *    The whole batch is recorded under one bulk payment header for audit,
 *    and can be voided (reverses the folio postings, recalcs, resyncs).
 * 3. BULK FISCALIZATION: multi-select bookings in a period and fiscalize in
 *    bulk. If a fiscalization function from your existing ZIMRA/FDMS
 *    integration is loaded (see acc_fiscalize_order_hook), each booking is
 *    submitted through it; otherwise bookings are QUEUED in
 *    accounting_fiscalization_log for the fiscal service to pick up, and the
 *    fiscal status shows on the browser.
 *
 * Conventions: PHP 7.2-safe, PDO named params (no reuse), INFORMATION_SCHEMA
 * guards before assuming columns, ONLY_FULL_GROUP_BY-safe aggregation, and
 * the same tx_type classification lists as reservation_list.php so balances
 * here always agree with the reservation screens.
 */

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

$pageTitle = 'Accounting - Bulk Payments & Fiscalization';

/* =========================================================================
   Guarded helpers
   ========================================================================= */
if (!function_exists('h')) {
    function h($value): string { return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8'); }
}
if (!function_exists('money_fmt')) {
    function money_fmt($amount): string { return number_format((float)$amount, 2); }
}

/*
   Split a VAT+ZTA-inclusive gross figure into its ZTA levy and VAT components,
   using the IDENTICAL method as fiscal_tax_invoice.php so the at-a-glance
   columns here reconcile to the cent with the actual fiscal tax invoices:
     - ZTA Levy 2%  is removed FIRST:  ZTA = round( (2 / 117.5) * gross , 2 )
     - VAT 15.5% is then derived from the ZTA-removed remainder:
                     amt = gross - ZTA
                     VAT = round( (15.5 / 115.5) * amt , 2 )
     - net = amt - VAT   (so net + ZTA + VAT = gross)
   Returns ['zta' => x, 'vat' => y, 'net' => z].
*/
if (!function_exists('acc_tax_split')) {
    function acc_tax_split($grossInclusive): array {
        $gross = (float)$grossInclusive;
        $zta = round((2.0 / 117.5) * $gross, 2);
        $amt = round($gross - $zta, 2);
        $vat = round((15.5 / 115.5) * $amt, 2);
        $net = round($amt - $vat, 2);
        return ['zta' => $zta, 'vat' => $vat, 'net' => $net];
    }
}
if (!function_exists('acc_table_exists')) {
    function acc_table_exists(PDO $pdo, string $table): bool {
        static $cache = [];
        if (array_key_exists($table, $cache)) return $cache[$table];
        try {
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t");
            $stmt->execute([':t' => $table]);
            $cache[$table] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) { $cache[$table] = false; }
        return $cache[$table];
    }
}
if (!function_exists('acc_column_exists')) {
    function acc_column_exists(PDO $pdo, string $table, string $column): bool {
        static $cache = [];
        $key = $table . '.' . $column;
        if (array_key_exists($key, $cache)) return $cache[$key];
        try {
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t AND COLUMN_NAME = :c");
            $stmt->execute([':t' => $table, ':c' => $column]);
            $cache[$key] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) { $cache[$key] = false; }
        return $cache[$key];
    }
}
if (!function_exists('acc_current_user_id')) {
    function acc_current_user_id(): int {
        if (function_exists('current_user_id')) return (int)current_user_id();
        return (int)($_SESSION['user_id'] ?? $_SESSION['id'] ?? 0);
    }
}
if (!function_exists('acc_current_user_name')) {
    function acc_current_user_name(): string {
        foreach (['user_name', 'username', 'full_name', 'name'] as $k) {
            if (!empty($_SESSION[$k])) return (string)$_SESSION[$k];
        }
        return 'User #' . acc_current_user_id();
    }
}

/* =========================================================================
   Schema
   ========================================================================= */
function acc_ensure_schema(PDO $pdo): void {
    $pdo->exec("CREATE TABLE IF NOT EXISTS accounting_bulk_payments (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        property_id INT UNSIGNED NULL,
        payment_date DATE NOT NULL,
        payer_name VARCHAR(190) NULL,
        agent_id INT UNSIGNED NULL,
        payment_method VARCHAR(60) NULL,
        reference VARCHAR(190) NULL,
        total_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
        notes TEXT NULL,
        status ENUM('posted','voided') NOT NULL DEFAULT 'posted',
        created_by INT UNSIGNED NULL,
        created_by_name VARCHAR(180) NULL,
        created_at DATETIME NULL,
        voided_at DATETIME NULL,
        voided_by_name VARCHAR(180) NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS accounting_payment_allocations (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        bulk_payment_id BIGINT UNSIGNED NOT NULL,
        order_id BIGINT UNSIGNED NOT NULL,
        folio_id BIGINT UNSIGNED NULL,
        payment_row_id BIGINT UNSIGNED NULL,
        folio_tx_id BIGINT UNSIGNED NULL,
        amount DECIMAL(14,2) NOT NULL DEFAULT 0,
        balance_before DECIMAL(14,2) NULL,
        created_at DATETIME NULL,
        INDEX idx_acc_alloc_bulk (bulk_payment_id),
        INDEX idx_acc_alloc_order (order_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS accounting_fiscalization_log (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        order_id BIGINT UNSIGNED NOT NULL,
        folio_id BIGINT UNSIGNED NULL,
        fiscal_ref VARCHAR(190) NULL,
        status ENUM('queued','done','failed') NOT NULL DEFAULT 'queued',
        message TEXT NULL,
        requested_by_name VARCHAR(180) NULL,
        requested_at DATETIME NULL,
        fiscalized_at DATETIME NULL,
        INDEX idx_acc_fisc_order (order_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
}

/* =========================================================================
   Folio helpers - IDENTICAL classification to the folio repair script and
   rl_live_order_balance, so this module never disagrees with them.
   ========================================================================= */
function acc_ensure_master_folio(PDO $pdo, int $orderId): int {
    $stmt = $pdo->prepare("SELECT id FROM folios WHERE order_id = :oid AND folio_type = 'master' LIMIT 1");
    $stmt->execute([':oid' => $orderId]);
    $fid = (int)($stmt->fetchColumn() ?: 0);
    if ($fid > 0) return $fid;

    $o = $pdo->prepare("SELECT id, property_id, order_no FROM orders WHERE id = :oid LIMIT 1");
    $o->execute([':oid' => $orderId]);
    $order = $o->fetch(PDO::FETCH_ASSOC);
    if (!$order) throw new RuntimeException('Booking #' . $orderId . ' not found.');

    $pdo->prepare("INSERT INTO folios (property_id, order_id, folio_no, folio_name, folio_type, status, total_charges, total_payments, balance)
                   VALUES (:pid, :oid, :fno, :fname, 'master', 1, 0, 0, 0)")
        ->execute([
            ':pid' => (int)($order['property_id'] ?? 0),
            ':oid' => $orderId,
            ':fno' => 'FOL-M-' . $orderId,
            ':fname' => 'Master Folio - ' . (string)(($order['order_no'] ?? '') !== '' ? $order['order_no'] : ('Booking #' . $orderId)),
        ]);
    return (int)$pdo->lastInsertId();
}

function acc_recalc_folio(PDO $pdo, int $folioId): void {
    $pdo->prepare("
        UPDATE folios f
        LEFT JOIN (
            SELECT folio_id,
                SUM(CASE WHEN COALESCE(is_voided,0)=0
                     AND LOWER(COALESCE(tx_type,'')) NOT IN
                        ('payment','receipt','deposit','payment_received','folio_payment','guest_payment',
                         'payment_void','void_payment','payment_reversal','reversal','refund','payment_refund')
                    THEN COALESCE(debit,0) ELSE 0 END) AS charges,
                SUM(CASE WHEN COALESCE(is_voided,0)=0
                     AND LOWER(COALESCE(tx_type,'')) IN
                        ('payment','receipt','deposit','payment_received','folio_payment','guest_payment')
                    THEN COALESCE(credit,0) ELSE 0 END) AS payments
            FROM folio_transactions
            WHERE folio_id = :fid_inner
            GROUP BY folio_id
        ) x ON x.folio_id = f.id
        SET f.total_charges = COALESCE(x.charges,0),
            f.total_payments = COALESCE(x.payments,0),
            f.balance = COALESCE(x.charges,0) - COALESCE(x.payments,0),
            f.updated_at = NOW()
        WHERE f.id = :fid_outer
    ")->execute([':fid_inner' => $folioId, ':fid_outer' => $folioId]);
}

function acc_sync_order_payment_status(PDO $pdo, int $orderId): void {
    $pdo->prepare("
        UPDATE orders o
        JOIN (
            SELECT order_id,
                   SUM(total_charges) AS charges,
                   SUM(total_payments) AS payments,
                   SUM(balance) AS balance
            FROM folios
            WHERE order_id = :oid_inner
            GROUP BY order_id
        ) x ON x.order_id = o.id
        SET o.payment_status = CASE
            WHEN COALESCE(x.charges,0) <= 0 OR COALESCE(x.payments,0) <= 0 THEN 0
            WHEN COALESCE(x.balance,0) <= 0.00001 THEN 2
            ELSE 1
        END
        WHERE o.id = :oid_outer
    ")->execute([':oid_inner' => $orderId, ':oid_outer' => $orderId]);
}

if (!function_exists('acc_required_payment_columns')) {
    /**
     * Columns on `payment` that are NOT NULL with no default and no
     * auto_increment - i.e. the ones MySQL will reject an INSERT for if they
     * are omitted, under the strict mode this server runs.
     *
     * Read from information_schema rather than hardcoded, because the point is
     * to catch the columns this code does not already know about. `invoice` was
     * the one that surfaced:
     *
     *     SQLSTATE[HY000]: General error: 1364 Field 'invoice' doesn't have a
     *     default value
     *
     * payments.php always passed :invoice explicitly, so the bulk allocation
     * path here was the only one that omitted it.
     *
     * @return array column_name => data_type
     */
    function acc_required_payment_columns(PDO $pdo): array
    {
        static $cache = null;
        if ($cache !== null) return $cache;
        $cache = [];
        try {
            $stmt = $pdo->query("
                SELECT COLUMN_NAME, DATA_TYPE
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = 'payment'
                  AND IS_NULLABLE = 'NO'
                  AND COLUMN_DEFAULT IS NULL
                  AND EXTRA NOT LIKE '%auto_increment%'
            ");
            foreach (($stmt ? $stmt->fetchAll(PDO::FETCH_ASSOC) : []) as $r) {
                $cache[$r['COLUMN_NAME']] = strtolower((string)$r['DATA_TYPE']);
            }
        } catch (Throwable $e) {
            $cache = [];
        }
        return $cache;
    }
}

if (!function_exists('acc_default_for_column')) {
    /**
     * A safe zero-ish value for a required column we have nothing better for.
     * Never guesses at meaning - just satisfies NOT NULL with the empty value
     * for that type.
     */
    function acc_default_for_column(string $dataType)
    {
        if (in_array($dataType, ['int', 'bigint', 'smallint', 'mediumint', 'tinyint'], true)) {
            return 0;
        }
        if (in_array($dataType, ['decimal', 'float', 'double', 'numeric'], true)) {
            return 0;
        }
        if (in_array($dataType, ['date'], true))     return date('Y-m-d');
        if (in_array($dataType, ['datetime', 'timestamp'], true)) return date('Y-m-d H:i:s');
        if (in_array($dataType, ['time'], true))     return date('H:i:s');
        return '';
    }
}

/* Insert into the legacy `payment` table with column detection, so existing
   reports and the folio repair script keep seeing these payments. */
function acc_insert_legacy_payment(PDO $pdo, int $orderId, int $propertyId, float $amount, string $method, string $reference, string $paymentDate): int {
    $cols = ['order_id' => $orderId, 'amount' => $amount];
    if (acc_column_exists($pdo, 'payment', 'property_id')) $cols['property_id'] = $propertyId;
    if (acc_column_exists($pdo, 'payment', 'payment_method')) $cols['payment_method'] = $method;
    if (acc_column_exists($pdo, 'payment', 'date_time')) $cols['date_time'] = $paymentDate . ' ' . date('H:i:s');
    if (acc_column_exists($pdo, 'payment', 'added_date')) $cols['added_date'] = $paymentDate;
    if (acc_column_exists($pdo, 'payment', 'id_upload')) $cols['id_upload'] = $reference;
    if (acc_column_exists($pdo, 'payment', 'notes')) $cols['notes'] = 'Bulk payment allocation' . ($reference !== '' ? ' | ' . $reference : '');

    /*
       Columns payments.php sets explicitly and this path must match, or these
       rows read back differently from a payment taken through the normal
       screen. invoice = 0 means "not tied to an invoice", which is what the
       payments screen sends for an ordinary payment. is_main_amount = 1 marks
       it as the principal rather than a surcharge or gratuity line. ROE is 1
       because $amount is already USD base by the time it reaches here.
    */
    if (acc_column_exists($pdo, 'payment', 'invoice') && !array_key_exists('invoice', $cols)) {
        $cols['invoice'] = 0;
    }
    if (acc_column_exists($pdo, 'payment', 'is_main_amount') && !array_key_exists('is_main_amount', $cols)) {
        $cols['is_main_amount'] = 1;
    }
    if (acc_column_exists($pdo, 'payment', 'roe_atm') && !array_key_exists('roe_atm', $cols)) {
        $cols['roe_atm'] = 1.00000;
    }

    /*
       Anything else the schema demands. Without this, the next NOT NULL column
       added to `payment` breaks bulk allocation exactly the way `invoice` did,
       and only in production - the error is invisible until a row is actually
       inserted.
    */
    foreach (acc_required_payment_columns($pdo) as $col => $type) {
        if (!array_key_exists($col, $cols)) {
            $cols[$col] = acc_default_for_column($type);
        }
    }

    $names = [];
    $phs = [];
    $params = [];
    $n = 0;
    foreach ($cols as $c => $v) {
        $n++;
        $ph = ':p' . $n;
        $names[] = '`' . $c . '`';
        $phs[] = $ph;
        $params[$ph] = $v;
    }
    $pdo->prepare("INSERT INTO payment (" . implode(',', $names) . ") VALUES (" . implode(',', $phs) . ")")->execute($params);
    return (int)$pdo->lastInsertId();
}

/*
   FISCALIZATION HOOK: wire this to the existing ZIMRA / FDMS integration.
   If one of the candidate functions is loaded by bootstrap, it is called
   with the order id and must return array('ok'=>bool,'ref'=>string,'message'=>string).
   When none is available the booking is queued so the fiscal service (or a
   cron) can process accounting_fiscalization_log rows with status='queued'.
*/
function acc_fiscalize_order_hook(PDO $pdo, int $orderId): array {
    foreach (['fiscalize_order', 'zimra_fiscalize_order', 'fdms_fiscalize_order', 'fiscal_submit_invoice'] as $fn) {
        if (function_exists($fn)) {
            try {
                $res = call_user_func($fn, $pdo, $orderId);
                if (is_array($res)) {
                    return [
                        'status' => !empty($res['ok']) ? 'done' : 'failed',
                        'ref' => (string)($res['ref'] ?? ''),
                        'message' => (string)($res['message'] ?? ''),
                    ];
                }
                return ['status' => 'done', 'ref' => '', 'message' => 'Fiscalized via ' . $fn];
            } catch (Throwable $e) {
                return ['status' => 'failed', 'ref' => '', 'message' => $e->getMessage()];
            }
        }
    }
    return ['status' => 'queued', 'ref' => '', 'message' => 'No fiscal integration function loaded - queued for the fiscal service.'];
}

/* =========================================================================
   Flash
   ========================================================================= */
if (session_status() !== PHP_SESSION_ACTIVE) { @session_start(); }
function acc_flash(string $type, string $msg): void { $_SESSION['acc_flash'] = ['type' => $type, 'msg' => $msg]; }
$accFlash = $_SESSION['acc_flash'] ?? null;
unset($_SESSION['acc_flash']);

/* =========================================================================
   POST actions
   ========================================================================= */
acc_ensure_schema($pdo);

/* orders.invoiced drives the Invoiced filter and is set when a tax invoice
   is finalized from fiscal_tax_invoice.php. Auto-created once. */
if (!acc_column_exists($pdo, 'orders', 'invoiced')) {
    try { $pdo->exec("ALTER TABLE orders ADD COLUMN invoiced TINYINT(1) NOT NULL DEFAULT 0"); } catch (Throwable $e) {}
}
if (!acc_column_exists($pdo, 'orders', 'invoiced_at')) {
    try { $pdo->exec("ALTER TABLE orders ADD COLUMN invoiced_at DATETIME NULL"); } catch (Throwable $e) {}
}

/* Load the FDMS/FiscalPro adapter if deployed alongside this file, so
   acc_fiscalize_order_hook() finds fdms_fiscalize_order() and submits for
   real instead of queueing. Guarded: without it, bookings simply queue. */
if (is_file(__DIR__ . '/fiscal_fdms.php')) {
    require_once __DIR__ . '/fiscal_fdms.php';
}
/* Credit note engine (full credit, and the ZIMRA-compliant
   full-credit-then-reinvoice partial correction chain). */
if (is_file(__DIR__ . '/fiscal_credit_note.php')) {
    require_once __DIR__ . '/fiscal_credit_note.php';
    try { fdms_ensure_credit_note_schema($pdo); } catch (Throwable $e) {}
}

$errors = [];
$allocationOrders = [];   // populated by prepare_allocation

$hasAgents = acc_table_exists($pdo, 'agents') && acc_column_exists($pdo, 'orders', 'agent_id');
$hasOriginatingAgent = acc_column_exists($pdo, 'orders', 'originating_agent');

/* Name expressions built from columns that actually exist on THIS database.
   Assuming firstname/lastname is what broke the pax search with
   "Unknown column 'rg.firstname'" - never assume a name column again. */
if (!function_exists('acc_name_expr')) {
    function acc_name_expr(PDO $pdo, string $table, string $alias): string {
        foreach ([['firstname', 'lastname'], ['first_name', 'last_name']] as $pair) {
            if (acc_column_exists($pdo, $table, $pair[0]) && acc_column_exists($pdo, $table, $pair[1])) {
                return "TRIM(CONCAT(COALESCE({$alias}.`{$pair[0]}`,''),' ',COALESCE({$alias}.`{$pair[1]}`,'')))";
            }
        }
        foreach (['guest_name', 'full_name', 'name', 'agent', 'company_name', 'company'] as $single) {
            if (acc_column_exists($pdo, $table, $single)) {
                return "TRIM(COALESCE({$alias}.`{$single}`,''))";
            }
        }
        return "''";
    }
}
$guestNameExpr = acc_table_exists($pdo, 'guests') ? acc_name_expr($pdo, 'guests', 'g') : "''";
$agentNameExpr = $hasAgents ? acc_name_expr($pdo, 'agents', 'a') : "''";

try {
    $action = (string)($_POST['action'] ?? '');

    if ($action === 'prepare_allocation' || $action === 'bulk_fiscalize') {
        $ids = array_values(array_unique(array_filter(array_map('intval', (array)($_POST['order_ids'] ?? [])))));
        if (!$ids) throw new RuntimeException('Select at least one booking first.');
        if (count($ids) > 200) throw new RuntimeException('Please work with 200 bookings or fewer at a time.');

        if ($action === 'bulk_fiscalize') {
            $done = 0; $queued = 0; $failed = 0; $skipped = 0;
            foreach ($ids as $oid) {
                /* Never resubmit a booking that already has a successful
                   fiscal invoice - the rows are colour-coded and their
                   checkboxes disabled in the browser, but a stale page or a
                   crafted POST must not double-fiscalize either. */
                $already = false;
                if (acc_table_exists($pdo, 'fdms_fiscal_invoices')) {
                    try {
                        $chk = $pdo->prepare("SELECT COUNT(*) FROM fdms_fiscal_invoices WHERE order_id = :oid AND success = 1");
                        $chk->execute([':oid' => $oid]);
                        $already = ((int)$chk->fetchColumn() > 0);
                    } catch (Throwable $e) {}
                }
                if (!$already) {
                    try {
                        $chk2 = $pdo->prepare("SELECT COUNT(*) FROM accounting_fiscalization_log WHERE order_id = :oid AND status = 'done'");
                        $chk2->execute([':oid' => $oid]);
                        $already = ((int)$chk2->fetchColumn() > 0);
                    } catch (Throwable $e) {}
                }
                if ($already) { $skipped++; continue; }

                $folioStmt = $pdo->prepare("SELECT id FROM folios WHERE order_id = :oid AND folio_type = 'master' LIMIT 1");
                $folioStmt->execute([':oid' => $oid]);
                $fid = (int)($folioStmt->fetchColumn() ?: 0);

                $res = acc_fiscalize_order_hook($pdo, $oid);
                $pdo->prepare("INSERT INTO accounting_fiscalization_log
                        (order_id, folio_id, fiscal_ref, status, message, requested_by_name, requested_at, fiscalized_at)
                        VALUES (:oid, :fid, :ref, :st, :msg, :by, NOW(), :fat)")
                    ->execute([
                        ':oid' => $oid,
                        ':fid' => $fid ?: null,
                        ':ref' => $res['ref'] !== '' ? $res['ref'] : null,
                        ':st'  => $res['status'],
                        ':msg' => $res['message'],
                        ':by'  => acc_current_user_name(),
                        ':fat' => $res['status'] === 'done' ? date('Y-m-d H:i:s') : null,
                    ]);
                if ($res['status'] === 'done') $done++;
                elseif ($res['status'] === 'failed') $failed++;
                else $queued++;
            }
            $parts = [];
            if ($done) $parts[] = $done . ' fiscalized';
            if ($queued) $parts[] = $queued . ' queued for the fiscal service';
            if ($skipped) $parts[] = $skipped . ' skipped (already fiscalized)';
            if ($failed) $parts[] = $failed . ' FAILED (see fiscal status)';
            acc_flash($failed ? 'warning' : 'success', 'Bulk fiscalization: ' . implode(', ', $parts) . '.');
            header('Location: accounting.php'); exit;
        }

        // prepare_allocation: load the selected bookings with live balances
        $phs = [];
        $params = [];
        $n = 0;
        foreach ($ids as $oid) { $n++; $phs[] = ':o' . $n; $params[':o' . $n] = $oid; }
        $sql = "
            SELECT o.id, o.order_no, o.property_id, o.check_in, o.check_out, o.totalamount,
                   " . ($hasOriginatingAgent ? "o.originating_agent" : "''") . " AS originating_agent,
                   {$agentNameExpr} AS agent_name,
                   {$guestNameExpr} AS guest_name,
                   f.id AS folio_id, f.folio_no,
                   COALESCE(f.total_charges, o.totalamount, 0) AS charges,
                   COALESCE(f.total_payments, 0) AS payments,
                   COALESCE(f.balance, o.totalamount, 0) AS balance
            FROM orders o
            LEFT JOIN guests g ON g.id = o.guest_id
            " . ($hasAgents ? "LEFT JOIN agents a ON a.id = o.agent_id" : "") . "
            LEFT JOIN folios f ON f.order_id = o.id AND f.folio_type = 'master'
            WHERE o.id IN (" . implode(',', $phs) . ")
            ORDER BY o.check_in ASC, o.id ASC";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $allocationOrders = $stmt->fetchAll(PDO::FETCH_ASSOC);
        if (!$allocationOrders) throw new RuntimeException('None of the selected bookings could be loaded.');
    }

    if ($action === 'post_bulk_payment') {
        $payerName = trim((string)($_POST['payer_name'] ?? ''));
        $method    = trim((string)($_POST['payment_method'] ?? 'Bank Transfer'));
        $reference = trim((string)($_POST['reference'] ?? ''));
        $payDate   = trim((string)($_POST['payment_date'] ?? date('Y-m-d')));
        $notes     = trim((string)($_POST['notes'] ?? ''));
        $agentId   = (int)($_POST['agent_id'] ?? 0);
        $allocOrderIds = (array)($_POST['alloc_order_id'] ?? []);
        $allocAmounts  = (array)($_POST['alloc_amount'] ?? []);

        if ($payerName === '') throw new RuntimeException('Enter the payer name (agent / company / guest making this payment).');
        $ts = strtotime($payDate);
        if ($ts === false) throw new RuntimeException('Invalid payment date.');
        $payDate = date('Y-m-d', $ts);

        // Collect valid allocations
        $allocs = [];
        foreach ($allocOrderIds as $idx => $oidRaw) {
            $oid = (int)$oidRaw;
            $amtRaw = trim(str_replace(',', '', (string)($allocAmounts[$idx] ?? '')));
            if ($oid <= 0 || $amtRaw === '' || !is_numeric($amtRaw)) continue;
            $amt = round((float)$amtRaw, 2);
            if ($amt < 0) throw new RuntimeException('Allocation amounts cannot be negative (booking #' . $oid . ').');
            if ($amt == 0.0) continue; // zero allocations are simply skipped
            if (isset($allocs[$oid])) throw new RuntimeException('Booking #' . $oid . ' appears twice in the allocation.');
            $allocs[$oid] = $amt;
        }
        if (!$allocs) throw new RuntimeException('Enter an allocation amount for at least one booking.');
        $totalAmount = round(array_sum($allocs), 2);

        $pdo->beginTransaction();
        try {
            $pdo->prepare("INSERT INTO accounting_bulk_payments
                    (property_id, payment_date, payer_name, agent_id, payment_method, reference, total_amount, notes, status, created_by, created_by_name, created_at)
                    VALUES (NULL, :pdate, :payer, :agid, :method, :ref, :total, :notes, 'posted', :uid, :uname, NOW())")
                ->execute([
                    ':pdate' => $payDate,
                    ':payer' => $payerName,
                    ':agid'  => $agentId ?: null,
                    ':method'=> $method,
                    ':ref'   => $reference,
                    ':total' => $totalAmount,
                    ':notes' => $notes,
                    ':uid'   => acc_current_user_id(),
                    ':uname' => acc_current_user_name(),
                ]);
            $bulkId = (int)$pdo->lastInsertId();

            foreach ($allocs as $oid => $amt) {
                $oStmt = $pdo->prepare("SELECT id, property_id FROM orders WHERE id = :oid LIMIT 1");
                $oStmt->execute([':oid' => $oid]);
                $orderRow = $oStmt->fetch(PDO::FETCH_ASSOC);
                if (!$orderRow) throw new RuntimeException('Booking #' . $oid . ' not found - nothing was posted.');
                $propId = (int)($orderRow['property_id'] ?? 0);

                $folioId = acc_ensure_master_folio($pdo, $oid);

                $balStmt = $pdo->prepare("SELECT balance FROM folios WHERE id = :fid LIMIT 1");
                $balStmt->execute([':fid' => $folioId]);
                $balanceBefore = (float)($balStmt->fetchColumn() ?: 0);

                $paymentRowId = acc_insert_legacy_payment($pdo, $oid, $propId, $amt, $method, $reference, $payDate);

                $desc = 'Bulk payment - ' . $payerName
                    . ($reference !== '' ? ' | ' . $reference : '')
                    . ' | Batch #' . $bulkId;
                $pdo->prepare("INSERT INTO folio_transactions
                        (property_id, folio_id, order_id, tx_date, tx_type, description, debit, credit, source_table, source_id)
                        VALUES (:pid, :fid, :oid, :txd, 'payment', :descr, 0, :amt, 'payment', :src)")
                    ->execute([
                        ':pid' => $propId,
                        ':fid' => $folioId,
                        ':oid' => $oid,
                        ':txd' => $payDate . ' ' . date('H:i:s'),
                        ':descr' => $desc,
                        ':amt' => $amt,
                        ':src' => $paymentRowId,
                    ]);
                $txId = (int)$pdo->lastInsertId();

                $pdo->prepare("INSERT INTO accounting_payment_allocations
                        (bulk_payment_id, order_id, folio_id, payment_row_id, folio_tx_id, amount, balance_before, created_at)
                        VALUES (:bid, :oid, :fid, :prid, :txid, :amt, :bb, NOW())")
                    ->execute([
                        ':bid' => $bulkId,
                        ':oid' => $oid,
                        ':fid' => $folioId,
                        ':prid'=> $paymentRowId,
                        ':txid'=> $txId,
                        ':amt' => $amt,
                        ':bb'  => $balanceBefore,
                    ]);

                acc_recalc_folio($pdo, $folioId);
                acc_sync_order_payment_status($pdo, $oid);
            }

            $pdo->commit();
        } catch (Throwable $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            throw $e;
        }

        acc_flash('success', 'Bulk payment #' . $bulkId . ' posted: ' . money_fmt($totalAmount) . ' allocated across ' . count($allocs) . ' booking(s). Master folios and payment statuses updated.');
        header('Location: accounting.php?bulk_id=' . $bulkId); exit;
    }

    if ($action === 'issue_credit_note') {
        if (!function_exists('fdms_issue_credit_note')) throw new RuntimeException('Credit note engine not deployed (fiscal_credit_note.php).');

        $invoiceId  = (int)($_POST['invoice_id'] ?? 0);
        $creditMode = (string)($_POST['credit_mode'] ?? 'full');
        $reasonCode = (int)($_POST['reason_code'] ?? 4);
        $reasonText = trim((string)($_POST['reason_text'] ?? ''));
        $creditAmt  = trim(str_replace(',', '', (string)($_POST['credit_amount'] ?? '')));

        if ($invoiceId <= 0) throw new RuntimeException('Select the fiscal invoice to credit.');
        if ($reasonText === '') throw new RuntimeException('Enter a reason for the credit note (it is submitted to ZIMRA as the comment).');

        if ($creditMode === 'correction') {
            if ($creditAmt === '' || !is_numeric($creditAmt)) throw new RuntimeException('Enter the amount to credit the customer.');
            /* ZIMRA rule: the credit note is ALWAYS for the full invoice; the
               amount entered only determines the replacement invoice. */
            $res = fdms_partial_correction($pdo, $invoiceId, (float)$creditAmt, $reasonCode, $reasonText);
        } else {
            $res = fdms_issue_credit_note($pdo, $invoiceId, $reasonCode, $reasonText, 'full', null);
        }

        acc_flash(!empty($res['ok']) ? 'success' : 'warning', (string)($res['message'] ?? 'Credit note processed.'));
        header('Location: accounting.php'); exit;
    }

    if ($action === 'retry_replacement_invoice') {
        if (!function_exists('fdms_retry_replacement_invoice')) throw new RuntimeException('Credit note engine not deployed.');
        $cnId = (int)($_POST['credit_note_id'] ?? 0);
        if ($cnId <= 0) throw new RuntimeException('Invalid credit note.');
        $res = fdms_retry_replacement_invoice($pdo, $cnId);
        acc_flash(!empty($res['ok']) ? 'success' : 'warning', (string)($res['message'] ?? ''));
        header('Location: accounting.php'); exit;
    }

    if ($action === 'void_bulk_payment') {
        $bulkId = (int)($_POST['bulk_payment_id'] ?? 0);
        if ($bulkId <= 0) throw new RuntimeException('Invalid bulk payment.');

        $bStmt = $pdo->prepare("SELECT id, status, total_amount FROM accounting_bulk_payments WHERE id = :bid LIMIT 1");
        $bStmt->execute([':bid' => $bulkId]);
        $bulk = $bStmt->fetch(PDO::FETCH_ASSOC);
        if (!$bulk) throw new RuntimeException('Bulk payment not found.');
        if ((string)$bulk['status'] === 'voided') throw new RuntimeException('This bulk payment is already voided.');

        $aStmt = $pdo->prepare("SELECT id, order_id, folio_id, folio_tx_id FROM accounting_payment_allocations WHERE bulk_payment_id = :bid");
        $aStmt->execute([':bid' => $bulkId]);
        $allocRows = $aStmt->fetchAll(PDO::FETCH_ASSOC);

        $pdo->beginTransaction();
        try {
            $hasVoided = acc_column_exists($pdo, 'folio_transactions', 'is_voided');
            foreach ($allocRows as $al) {
                $txId = (int)($al['folio_tx_id'] ?? 0);
                if ($txId > 0 && $hasVoided) {
                    $pdo->prepare("UPDATE folio_transactions SET is_voided = 1 WHERE id = :tid")->execute([':tid' => $txId]);
                }
                if ((int)$al['folio_id'] > 0) acc_recalc_folio($pdo, (int)$al['folio_id']);
                acc_sync_order_payment_status($pdo, (int)$al['order_id']);
            }
            $pdo->prepare("UPDATE accounting_bulk_payments SET status='voided', voided_at=NOW(), voided_by_name=:by WHERE id=:bid")
                ->execute([':by' => acc_current_user_name(), ':bid' => $bulkId]);
            $pdo->commit();
        } catch (Throwable $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            throw $e;
        }
        acc_flash('success', 'Bulk payment #' . $bulkId . ' voided - folio postings reversed and statuses resynced. (The legacy payment rows remain for audit; the folio no longer counts them.)');
        header('Location: accounting.php'); exit;
    }
} catch (Throwable $e) {
    $errors[] = $e->getMessage();
}

/* =========================================================================
   Bookings browser (GET filters)
   ========================================================================= */
/* Persist browser filters in the session until the user explicitly resets them.
   On first visit, default to arrivals from today back through the previous
   31 days, with the latest check-ins shown first. */
$filterSessionKey = 'accounting_booking_filters';
if (isset($_GET['reset_filters'])) {
    unset($_SESSION[$filterSessionKey]);
    header('Location: accounting.php');
    exit;
}

$filterFieldsPresent = isset($_GET['apply_filters']) || isset($_GET['q']) || isset($_GET['agent_id'])
    || isset($_GET['period_type']) || isset($_GET['date_from']) || isset($_GET['date_to'])
    || isset($_GET['balance_only']) || isset($_GET['invoiced']) || isset($_GET['fiscal_status']);

if ($filterFieldsPresent) {
    $requestedStatuses = array_values(array_intersect(
        ['fiscalized','queued','failed','invoiced_unfiscalized','unprocessed'],
        array_map('strval', (array)($_GET['fiscal_status'] ?? []))
    ));
    $_SESSION[$filterSessionKey] = [
        'q' => trim((string)($_GET['q'] ?? '')),
        'agent_id' => (int)($_GET['agent_id'] ?? 0),
        'period_type' => in_array((string)($_GET['period_type'] ?? 'arrival'), ['arrival','departure','overlap'], true)
            ? (string)$_GET['period_type'] : 'arrival',
        'date_from' => trim((string)($_GET['date_from'] ?? '')),
        'date_to' => trim((string)($_GET['date_to'] ?? '')),
        'balance_only' => !empty($_GET['balance_only']) ? 1 : 0,
        'invoiced' => isset($_GET['invoiced']) && $_GET['invoiced'] !== '' ? (int)$_GET['invoiced'] : -1,
        'fiscal_status' => $requestedStatuses,
    ];
}

$defaultFilters = [
    'q' => '',
    'agent_id' => 0,
    'period_type' => 'arrival',
    'date_from' => date('Y-m-d', strtotime('-31 days')),
    'date_to' => date('Y-m-d'),
    'balance_only' => 0,
    'invoiced' => -1,
    'fiscal_status' => [],
];
$savedFilters = array_merge($defaultFilters, (array)($_SESSION[$filterSessionKey] ?? []));

$q              = trim((string)$savedFilters['q']);
$filterAgent    = (int)$savedFilters['agent_id'];
$periodType     = in_array((string)$savedFilters['period_type'], ['arrival','departure','overlap'], true) ? (string)$savedFilters['period_type'] : 'arrival';
$dateFrom       = trim((string)$savedFilters['date_from']);
$dateTo         = trim((string)$savedFilters['date_to']);
$balanceOnly    = !empty($savedFilters['balance_only']) ? 1 : 0;
$invoicedFilter = (int)$savedFilters['invoiced'];
$fiscalStatusFilters = array_values(array_intersect(
    ['fiscalized','queued','failed','invoiced_unfiscalized','unprocessed'],
    array_map('strval', (array)$savedFilters['fiscal_status'])
));
$exportExcel = (($_GET['export'] ?? '') === 'excel');

$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) { $agentsList = []; }
}

$rows = [];
try {
    $where = ["COALESCE(o.status, 1) NOT IN (0, 5)"];
    $params = [];

    if ($q !== '') {
        $like = '%' . $q . '%';
        $searchParts = [
            "o.order_no LIKE :q_orderno",
            "CAST(o.id AS CHAR) LIKE :q_oid",
            "f.folio_no LIKE :q_folio",
        ];
        $params[':q_orderno'] = $like;
        $params[':q_oid'] = $like;
        $params[':q_folio'] = $like;

        if ($guestNameExpr !== "''") {
            $searchParts[] = "{$guestNameExpr} LIKE :q_guest";
            $params[':q_guest'] = $like;
        }
        if ($hasOriginatingAgent) {
            $searchParts[] = "o.originating_agent LIKE :q_origagent";
            $params[':q_origagent'] = $like;
        }
        if ($hasAgents && $agentNameExpr !== "''") {
            $searchParts[] = "{$agentNameExpr} LIKE :q_agent";
            $params[':q_agent'] = $like;
        }
        /* Pax search: any preloaded rooming guest name on the booking.
           The name columns vary by install (firstname/lastname, guest_name,
           full_name, first_name/last_name...), so build the expression from
           whichever columns actually exist rather than assuming - an
           unguarded rg.firstname threw "Unknown column" on this database. */
        if (acc_table_exists($pdo, 'order_rooming_guests') && acc_column_exists($pdo, 'order_rooming_guests', 'order_id')) {
            $paxParts = [];
            $pairs = [['firstname', 'lastname'], ['first_name', 'last_name']];
            foreach ($pairs as $pair) {
                if (acc_column_exists($pdo, 'order_rooming_guests', $pair[0]) && acc_column_exists($pdo, 'order_rooming_guests', $pair[1])) {
                    $paxParts[] = "CONCAT(COALESCE(rg.`{$pair[0]}`,''),' ',COALESCE(rg.`{$pair[1]}`,''))";
                    break;
                }
            }
            foreach (['guest_name', 'full_name', 'name', 'pax_name'] as $single) {
                if (acc_column_exists($pdo, 'order_rooming_guests', $single)) {
                    $paxParts[] = "COALESCE(rg.`{$single}`,'')";
                    break;
                }
            }
            if ($paxParts) {
                $paxExpr = count($paxParts) > 1 ? "CONCAT_WS(' ', " . implode(', ', $paxParts) . ")" : $paxParts[0];
                $searchParts[] = "EXISTS (SELECT 1 FROM order_rooming_guests rg WHERE rg.order_id = o.id AND {$paxExpr} LIKE :q_pax)";
                $params[':q_pax'] = $like;
            }
        }
        $where[] = '(' . implode(' OR ', $searchParts) . ')';
    }

    if ($hasAgents && $filterAgent > 0) {
        $where[] = "o.agent_id = :agent_id";
        $params[':agent_id'] = $filterAgent;
    }

    if ($dateFrom !== '' && $dateTo !== '') {
        if ($periodType === 'arrival') {
            $where[] = "DATE(o.check_in) BETWEEN :d_from AND :d_to";
        } elseif ($periodType === 'departure') {
            $where[] = "DATE(o.check_out) BETWEEN :d_from AND :d_to";
        } else { // overlap
            $where[] = "DATE(o.check_in) <= :d_to AND DATE(o.check_out) >= :d_from";
        }
        $params[':d_from'] = $dateFrom;
        $params[':d_to'] = $dateTo;
    }

    if ($balanceOnly) {
        $where[] = "COALESCE(f.balance, o.totalamount, 0) > 0.005";
    }

    if ($invoicedFilter === 1) {
        $where[] = "COALESCE(o.invoiced, 0) = 1";
    } elseif ($invoicedFilter === 0) {
        $where[] = "COALESCE(o.invoiced, 0) = 0";
    }

    $hasFdmsTable = acc_table_exists($pdo, 'fdms_fiscal_invoices');
    $hasCreditedCol = $hasFdmsTable && acc_column_exists($pdo, 'fdms_fiscal_invoices', 'credited');
    $fdmsSelect = $hasFdmsTable
        ? "fdi.zimra_invoice_no AS fdms_zimra_no, fdi.invoice_id AS fdms_invoice_id, fdi.amount_including_tax AS fdms_amount, fdi.invoice_no AS fdms_invoice_no, fdi.credited AS fdms_credited"
        : "NULL AS fdms_zimra_no, NULL AS fdms_invoice_id, NULL AS fdms_amount, NULL AS fdms_invoice_no, NULL AS fdms_credited";
    $creditedExpr = $hasCreditedCol ? "i1.credited" : "0";
    $fdmsJoin = $hasFdmsTable ? "
        LEFT JOIN (
            SELECT i1.order_id, i1.zimra_invoice_no, i1.id AS invoice_id,
                   i1.amount_including_tax, i1.invoice_no, {$creditedExpr} AS credited
            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
        ) fdi ON fdi.order_id = o.id" : "";

    /* Fiscal-state tick boxes are OR filters. No boxes selected means show all. */
    if ($fiscalStatusFilters) {
        $statusParts = [];
        foreach ($fiscalStatusFilters as $statusFilter) {
            if ($statusFilter === 'fiscalized') {
                $statusParts[] = "(" . ($hasFdmsTable ? "COALESCE(fdi.zimra_invoice_no,'') <> '' OR " : "") . "COALESCE(fisc.fstatus,'') = 'done')";
            } elseif ($statusFilter === 'queued') {
                $statusParts[] = "(" . ($hasFdmsTable ? "COALESCE(fdi.zimra_invoice_no,'') = '' AND " : "") . "COALESCE(fisc.fstatus,'') = 'queued')";
            } elseif ($statusFilter === 'failed') {
                $statusParts[] = "(" . ($hasFdmsTable ? "COALESCE(fdi.zimra_invoice_no,'') = '' AND " : "") . "COALESCE(fisc.fstatus,'') = 'failed')";
            } elseif ($statusFilter === 'invoiced_unfiscalized') {
                $statusParts[] = "(COALESCE(o.invoiced,0) = 1 AND " . ($hasFdmsTable ? "COALESCE(fdi.zimra_invoice_no,'') = '' AND " : "") . "COALESCE(fisc.fstatus,'') <> 'done')";
            } elseif ($statusFilter === 'unprocessed') {
                $statusParts[] = "(" . ($hasFdmsTable ? "COALESCE(fdi.zimra_invoice_no,'') = '' AND " : "") . "COALESCE(fisc.fstatus,'') NOT IN ('done','queued','failed') AND COALESCE(o.invoiced,0) = 0)";
            }
        }
        if ($statusParts) $where[] = '(' . implode(' OR ', $statusParts) . ')';
    }

    $sql = "
        SELECT o.id, o.order_no, o.property_id, o.check_in, o.check_out, o.payment_status, o.totalamount,
               COALESCE(o.invoiced, 0) AS invoiced,
               " . ($hasOriginatingAgent ? "o.originating_agent" : "''") . " AS originating_agent,
               {$agentNameExpr} AS agent_name,
               {$guestNameExpr} AS guest_name,
               f.id AS folio_id, f.folio_no,
               COALESCE(f.total_charges, o.totalamount, 0) AS charges,
               COALESCE(f.total_payments, 0) AS payments,
               COALESCE(f.balance, o.totalamount, 0) AS balance,
               fisc.fstatus AS fiscal_status, fisc.fref AS fiscal_ref,
               {$fdmsSelect}
        FROM orders o
        LEFT JOIN guests g ON g.id = o.guest_id
        " . ($hasAgents ? "LEFT JOIN agents a ON a.id = o.agent_id" : "") . "
        LEFT JOIN folios f ON f.order_id = o.id AND f.folio_type = 'master'
        LEFT JOIN (
            SELECT l1.order_id, l1.status AS fstatus, l1.fiscal_ref AS fref
            FROM accounting_fiscalization_log l1
            INNER JOIN (
                SELECT order_id, MAX(id) AS max_id
                FROM accounting_fiscalization_log
                GROUP BY order_id
            ) l2 ON l2.order_id = l1.order_id AND l2.max_id = l1.id
        ) fisc ON fisc.order_id = o.id
        {$fdmsJoin}
        WHERE " . implode(' AND ', $where) . "
        ORDER BY o.check_in DESC, o.id DESC
        LIMIT " . ($exportExcel ? 5000 : 300);
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (Throwable $e) {
    $errors[] = 'Bookings search failed: ' . $e->getMessage();
}

/* =========================================================================
   EXCEL EXPORT of the current search results.
   Emitted as an XML Spreadsheet 2003 workbook - opens natively in Excel and
   LibreOffice, needs no library, and (unlike CSV) keeps numbers as numbers
   and dates as dates, so totals and pivots work straight away.
   Must run BEFORE any HTML output.
   ========================================================================= */
if ($exportExcel && !$errors) {
    $filename = 'bookings_accounting_' . date('Ymd_His') . '.xls';
    header('Content-Type: application/vnd.ms-excel; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Pragma: no-cache');
    header('Expires: 0');

    $xmlEsc = function ($v) { return htmlspecialchars((string)$v, ENT_QUOTES | ENT_XML1, 'UTF-8'); };

    $filterBits = [];
    if ($q !== '') $filterBits[] = 'Search: ' . $q;
    if ($filterAgent > 0) $filterBits[] = 'Agent ID: ' . $filterAgent;
    if ($dateFrom !== '' && $dateTo !== '') $filterBits[] = ucfirst($periodType) . ': ' . $dateFrom . ' to ' . $dateTo;
    if ($balanceOnly) $filterBits[] = 'Owing only';
    if ($invoicedFilter === 1) $filterBits[] = 'Invoiced only';
    if ($invoicedFilter === 0) $filterBits[] = 'Not invoiced only';
    $filterLine = $filterBits ? implode('  |  ', $filterBits) : 'No filters (all bookings)';

    $totCharges = 0.0; $totPayments = 0.0; $totBalance = 0.0;
    $totZta = 0.0; $totVat = 0.0;
    foreach ($rows as $r) {
        $totCharges += (float)$r['charges'];
        $totPayments += (float)$r['payments'];
        $totBalance += (float)$r['balance'];
        $rowSplit = acc_tax_split($r['charges']);
        $totZta += $rowSplit['zta'];
        $totVat += $rowSplit['vat'];
    }
    $totZta = round($totZta, 2);
    $totVat = round($totVat, 2);

    echo '<?xml version="1.0" encoding="UTF-8"?>' . "\n";
    echo '<?mso-application progid="Excel.Sheet"?>' . "\n";
    echo '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">' . "\n";

    echo '<Styles>
 <Style ss:ID="sTitle"><Font ss:Bold="1" ss:Size="14"/></Style>
 <Style ss:ID="sMeta"><Font ss:Italic="1" ss:Size="9" ss:Color="#555555"/></Style>
 <Style ss:ID="sHead"><Font ss:Bold="1" ss:Color="#FFFFFF"/><Interior ss:Color="#1F4E5F" ss:Pattern="Solid"/><Alignment ss:Vertical="Center"/><Borders><Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/></Borders></Style>
 <Style ss:ID="sText"><Alignment ss:Vertical="Top"/></Style>
 <Style ss:ID="sDate"><NumberFormat ss:Format="yyyy\-mm\-dd"/></Style>
 <Style ss:ID="sMoney"><NumberFormat ss:Format="#,##0.00"/></Style>
 <Style ss:ID="sMoneyBold"><Font ss:Bold="1"/><NumberFormat ss:Format="#,##0.00"/></Style>
 <Style ss:ID="sTotal"><Font ss:Bold="1"/><Interior ss:Color="#E8E8E8" ss:Pattern="Solid"/></Style>
</Styles>' . "\n";

    echo '<Worksheet ss:Name="Bookings"><Table>' . "\n";
    // Column widths
    foreach ([80, 150, 130, 80, 80, 100, 80, 80, 80, 80, 80, 90, 90, 110, 70] as $w) {
        echo '<Column ss:AutoFitWidth="0" ss:Width="' . $w . '"/>' . "\n";
    }

    echo '<Row ss:Height="20"><Cell ss:StyleID="sTitle"><Data ss:Type="String">Accounting - Bookings Export</Data></Cell></Row>' . "\n";
    echo '<Row><Cell ss:StyleID="sMeta"><Data ss:Type="String">' . $xmlEsc($filterLine) . '</Data></Cell></Row>' . "\n";
    echo '<Row><Cell ss:StyleID="sMeta"><Data ss:Type="String">Generated ' . $xmlEsc(date('Y-m-d H:i')) . ' by ' . $xmlEsc(acc_current_user_name()) . ' - ' . count($rows) . ' booking(s)</Data></Cell></Row>' . "\n";
    echo '<Row></Row>' . "\n";

    $headers = ['Booking No', 'Guest', 'Agent', 'Arrival', 'Departure', 'Folio No', 'Charges', 'ZTA', 'VAT', 'Paid', 'Balance', 'Pay Status', 'Fiscal Status', 'ZIMRA / Fiscal Ref', 'Invoiced'];
    echo '<Row>';
    foreach ($headers as $hd) echo '<Cell ss:StyleID="sHead"><Data ss:Type="String">' . $xmlEsc($hd) . '</Data></Cell>';
    echo '</Row>' . "\n";

    foreach ($rows as $r) {
        $ps = (int)($r['payment_status'] ?? 0);
        $psText = $ps === 2 ? 'Paid' : ($ps === 1 ? 'Part' : 'Unpaid');
        $zimraNo = trim((string)($r['fdms_zimra_no'] ?? ''));
        $fsRaw = (string)($r['fiscal_status'] ?? '');
        $isCredited = ((int)($r['fdms_credited'] ?? 0) === 1);
        $fsText = $isCredited ? 'Credited'
                : (($zimraNo !== '' || $fsRaw === 'done') ? 'Fiscalized'
                : ($fsRaw === 'queued' ? 'Queued' : ($fsRaw === 'failed' ? 'Failed' : '')));
        $ref = $zimraNo !== '' ? $zimraNo : (string)($r['fiscal_ref'] ?? '');
        $agentName = ($r['agent_name'] ?? '') !== '' ? $r['agent_name'] : ($r['originating_agent'] ?? '');
        $xlSplit = acc_tax_split($r['charges']);

        echo '<Row>';
        echo '<Cell ss:StyleID="sText"><Data ss:Type="String">' . $xmlEsc(($r['order_no'] ?? '') !== '' ? $r['order_no'] : ('#' . $r['id'])) . '</Data></Cell>';
        echo '<Cell ss:StyleID="sText"><Data ss:Type="String">' . $xmlEsc($r['guest_name'] ?? '') . '</Data></Cell>';
        echo '<Cell ss:StyleID="sText"><Data ss:Type="String">' . $xmlEsc($agentName) . '</Data></Cell>';
        echo '<Cell ss:StyleID="sDate"><Data ss:Type="DateTime">' . $xmlEsc(substr((string)$r['check_in'], 0, 10)) . 'T00:00:00.000</Data></Cell>';
        echo '<Cell ss:StyleID="sDate"><Data ss:Type="DateTime">' . $xmlEsc(substr((string)$r['check_out'], 0, 10)) . 'T00:00:00.000</Data></Cell>';
        echo '<Cell ss:StyleID="sText"><Data ss:Type="String">' . $xmlEsc($r['folio_no'] ?? '') . '</Data></Cell>';
        echo '<Cell ss:StyleID="sMoney"><Data ss:Type="Number">' . number_format((float)$r['charges'], 2, '.', '') . '</Data></Cell>';
        echo '<Cell ss:StyleID="sMoney"><Data ss:Type="Number">' . number_format((float)$xlSplit['zta'], 2, '.', '') . '</Data></Cell>';
        echo '<Cell ss:StyleID="sMoney"><Data ss:Type="Number">' . number_format((float)$xlSplit['vat'], 2, '.', '') . '</Data></Cell>';
        echo '<Cell ss:StyleID="sMoney"><Data ss:Type="Number">' . number_format((float)$r['payments'], 2, '.', '') . '</Data></Cell>';
        echo '<Cell ss:StyleID="sMoney"><Data ss:Type="Number">' . number_format((float)$r['balance'], 2, '.', '') . '</Data></Cell>';
        echo '<Cell ss:StyleID="sText"><Data ss:Type="String">' . $xmlEsc($psText) . '</Data></Cell>';
        echo '<Cell ss:StyleID="sText"><Data ss:Type="String">' . $xmlEsc($fsText) . '</Data></Cell>';
        echo '<Cell ss:StyleID="sText"><Data ss:Type="String">' . $xmlEsc($ref) . '</Data></Cell>';
        echo '<Cell ss:StyleID="sText"><Data ss:Type="Number">' . ((int)($r['invoiced'] ?? 0)) . '</Data></Cell>';
        echo '</Row>' . "\n";
    }

    // Totals row
    echo '<Row>';
    echo '<Cell ss:StyleID="sTotal"><Data ss:Type="String">TOTAL</Data></Cell>';
    for ($i = 0; $i < 5; $i++) echo '<Cell ss:StyleID="sTotal"/>';
    echo '<Cell ss:StyleID="sMoneyBold"><Data ss:Type="Number">' . number_format($totCharges, 2, '.', '') . '</Data></Cell>';
    echo '<Cell ss:StyleID="sMoneyBold"><Data ss:Type="Number">' . number_format($totZta, 2, '.', '') . '</Data></Cell>';
    echo '<Cell ss:StyleID="sMoneyBold"><Data ss:Type="Number">' . number_format($totVat, 2, '.', '') . '</Data></Cell>';
    echo '<Cell ss:StyleID="sMoneyBold"><Data ss:Type="Number">' . number_format($totPayments, 2, '.', '') . '</Data></Cell>';
    echo '<Cell ss:StyleID="sMoneyBold"><Data ss:Type="Number">' . number_format($totBalance, 2, '.', '') . '</Data></Cell>';
    for ($i = 0; $i < 4; $i++) echo '<Cell ss:StyleID="sTotal"/>';
    echo '</Row>' . "\n";

    echo '</Table>
 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
  <FreezePanes/><FrozenNoSplit/><SplitHorizontal>5</SplitHorizontal><TopRowBottomPane>5</TopRowBottomPane><ActivePane>2</ActivePane>
 </WorksheetOptions>
</Worksheet>' . "\n";
    echo '</Workbook>';
    exit;
}

/* Recent bulk payments (with allocations for the one being viewed) */
$recentBulk = [];
$viewBulkAllocs = [];
$viewBulkId = (int)($_GET['bulk_id'] ?? 0);
try {
    $recentBulk = $pdo->query("SELECT * FROM accounting_bulk_payments ORDER BY id DESC LIMIT 15")->fetchAll(PDO::FETCH_ASSOC);
    if ($viewBulkId > 0) {
        $va = $pdo->prepare("
            SELECT al.*, o.order_no,
                   {$guestNameExpr} AS guest_name
            FROM accounting_payment_allocations al
            LEFT JOIN orders o ON o.id = al.order_id
            LEFT JOIN guests g ON g.id = o.guest_id
            WHERE al.bulk_payment_id = :bid
            ORDER BY al.id ASC");
        $va->execute([':bid' => $viewBulkId]);
        $viewBulkAllocs = $va->fetchAll(PDO::FETCH_ASSOC);
    }
} catch (Throwable $e) {}

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-cash-coin"></i> Accounting</div>
            <div class="hero-title mt-2">Bulk Payments, Allocations &amp; Fiscalization</div>
            <div class="hero-subtitle mt-2">Record one payment, allocate it across many bookings, post to each master folio - and fiscalize bookings in bulk.</div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <a href="agent_statement.php" class="btn btn-light me-2"><i class="bi bi-file-earmark-spreadsheet me-2"></i>Statements</a>
            <a href="reservation_list.php" class="btn btn-light"><i class="bi bi-list-ul me-2"></i>Reservations</a>
        </div>
    </div>
</div>

<?php if ($accFlash): ?>
    <div class="alert alert-<?php echo h($accFlash['type'] === 'warning' ? 'warning' : ($accFlash['type'] === 'success' ? 'success' : 'info')); ?>"><?php echo h($accFlash['msg']); ?></div>
<?php endif; ?>
<?php foreach ($errors as $err): ?>
    <div class="alert alert-danger"><?php echo h($err); ?></div>
<?php endforeach; ?>

<?php if ($allocationOrders): ?>
<!-- =====================================================================
     ALLOCATION FORM (shown after selecting bookings and pressing Allocate)
     ===================================================================== -->
<div class="card panel-card mb-4 border-primary">
    <div class="card-header bg-primary text-white"><i class="bi bi-diagram-3 me-2"></i>Allocate Bulk Payment - <?php echo count($allocationOrders); ?> booking(s) selected</div>
    <div class="card-body">
        <form method="post" id="allocForm">
            <?php if (function_exists('csrf_field')) echo csrf_field(); ?>
            <input type="hidden" name="action" value="post_bulk_payment">
            <div class="row g-2 mb-3">
                <div class="col-md-3">
                    <label class="form-label small">Payer (agent / company) *</label>
                    <input type="text" name="payer_name" class="form-control form-control-sm" required placeholder="e.g. Tourvest Destinations">
                </div>
                <?php if ($agentsList): ?>
                <div class="col-md-2">
                    <label class="form-label small">Link to agent</label>
                    <select name="agent_id" class="form-select form-select-sm">
                        <option value="0">- none -</option>
                        <?php foreach ($agentsList as $ag): ?>
                            <option value="<?php echo (int)$ag['id']; ?>"><?php echo h($ag['agent_name']); ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <?php endif; ?>
                <div class="col-md-2">
                    <label class="form-label small">Method</label>
                    <select name="payment_method" class="form-select form-select-sm">
                        <option>Bank Transfer</option><option>Cash</option><option>Card</option><option>EcoCash</option><option>Cheque</option><option>Other</option>
                    </select>
                </div>
                <div class="col-md-2">
                    <label class="form-label small">Reference</label>
                    <input type="text" name="reference" class="form-control form-control-sm" placeholder="e.g. TT-4471 / remittance no">
                </div>
                <div class="col-md-2">
                    <label class="form-label small">Payment date</label>
                    <input type="date" name="payment_date" class="form-control form-control-sm" value="<?php echo h(date('Y-m-d')); ?>">
                </div>
                <div class="col-md-3">
                    <label class="form-label small">Notes</label>
                    <input type="text" name="notes" class="form-control form-control-sm" placeholder="optional">
                </div>
            </div>

            <div class="table-responsive">
                <table class="table table-sm align-middle" id="allocTable">
                    <thead><tr>
                        <th>Booking</th><th>Guest</th><th>Agent</th><th>Stay</th><th>Folio</th>
                        <th class="text-end">Charges</th><th class="text-end">Paid</th><th class="text-end">Balance</th>
                        <th class="text-end" style="min-width:140px;">Allocate</th>
                    </tr></thead>
                    <tbody>
                    <?php foreach ($allocationOrders as $ao): ?>
                        <?php $bal = max(0, (float)$ao['balance']); ?>
                        <tr>
                            <td><strong><?php echo h(($ao['order_no'] ?? '') !== '' ? $ao['order_no'] : ('#' . $ao['id'])); ?></strong></td>
                            <td><?php echo h($ao['guest_name'] ?? ''); ?></td>
                            <td class="small text-muted"><?php echo h(($ao['agent_name'] ?? '') !== '' ? $ao['agent_name'] : ($ao['originating_agent'] ?? '')); ?></td>
                            <td class="small"><?php echo h(substr((string)$ao['check_in'],0,10)); ?> &rarr; <?php echo h(substr((string)$ao['check_out'],0,10)); ?></td>
                            <td class="small"><?php echo h($ao['folio_no'] ?? '(folio will be created)'); ?></td>
                            <td class="text-end"><?php echo money_fmt($ao['charges']); ?></td>
                            <td class="text-end"><?php echo money_fmt($ao['payments']); ?></td>
                            <td class="text-end fw-bold <?php echo $bal > 0.005 ? 'text-danger' : 'text-success'; ?>"><?php echo money_fmt($ao['balance']); ?></td>
                            <td class="text-end">
                                <input type="hidden" name="alloc_order_id[]" value="<?php echo (int)$ao['id']; ?>">
                                <input type="number" step="0.01" min="0" name="alloc_amount[]"
                                       class="form-control form-control-sm text-end alloc-amount"
                                       data-balance="<?php echo h(number_format($bal, 2, '.', '')); ?>"
                                       value="<?php echo h(number_format($bal, 2, '.', '')); ?>">
                            </td>
                        </tr>
                    <?php endforeach; ?>
                    </tbody>
                    <tfoot>
                        <tr class="table-dark">
                            <td colspan="8" class="text-end fw-bold">TOTAL PAYMENT (sum of allocations)</td>
                            <td class="text-end fw-bold" id="allocTotal">0.00</td>
                        </tr>
                    </tfoot>
                </table>
            </div>
            <div class="d-flex flex-wrap gap-2 align-items-center">
                <button type="button" class="btn btn-sm btn-outline-secondary" id="fillBalancesBtn">Fill all with balance</button>
                <button type="button" class="btn btn-sm btn-outline-secondary" id="clearAllocBtn">Clear all</button>
                <div class="ms-auto d-flex gap-2">
                    <a href="accounting.php" class="btn btn-outline-secondary">Cancel</a>
                    <button class="btn btn-success" onclick="return confirm('Post this bulk payment and allocate to the selected bookings? Each allocation is posted to the booking\'s master folio.');"><i class="bi bi-check2-circle me-1"></i>Post Bulk Payment</button>
                </div>
            </div>
            <div class="small text-muted mt-2">Zero allocations are skipped. Amounts default to each booking's balance - adjust freely (partial payments and overpayments are allowed; overpayment leaves a credit balance on the folio).</div>
        </form>
    </div>
</div>
<?php endif; ?>

<!-- =====================================================================
     BOOKINGS BROWSER
     ===================================================================== -->
<div class="card panel-card mb-3">
    <div class="card-body">
        <form method="get" class="row g-2 align-items-end" id="accountingFilters">
            <input type="hidden" name="apply_filters" value="1">
            <div class="col-md-3">
                <label class="form-label small mb-1">Search (booking no / folio / guest / pax / agent)</label>
                <input type="text" name="q" value="<?php echo h($q); ?>" class="form-control form-control-sm" placeholder="e.g. FOL-M-107220, Moyo, Tourvest">
            </div>
            <?php if ($agentsList): ?>
            <div class="col-md-2">
                <label class="form-label small mb-1">Agent</label>
                <select name="agent_id" class="form-select form-select-sm">
                    <option value="0">All agents</option>
                    <?php foreach ($agentsList as $ag): ?>
                        <option value="<?php echo (int)$ag['id']; ?>" <?php echo $filterAgent === (int)$ag['id'] ? 'selected' : ''; ?>><?php echo h($ag['agent_name']); ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <?php endif; ?>
            <div class="col-md-2">
                <label class="form-label small mb-1">Period type</label>
                <select name="period_type" class="form-select form-select-sm">
                    <option value="arrival" <?php echo $periodType === 'arrival' ? 'selected' : ''; ?>>Arrival in period</option>
                    <option value="departure" <?php echo $periodType === 'departure' ? 'selected' : ''; ?>>Departure in period</option>
                    <option value="overlap" <?php echo $periodType === 'overlap' ? 'selected' : ''; ?>>Stay overlaps period</option>
                </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-2">
                <label class="form-label small mb-1">Invoiced</label>
                <select name="invoiced" class="form-select form-select-sm">
                    <option value="" <?php echo $invoicedFilter === -1 ? 'selected' : ''; ?>>All</option>
                    <option value="0" <?php echo $invoicedFilter === 0 ? 'selected' : ''; ?>>0 - Not yet invoiced</option>
                    <option value="1" <?php echo $invoicedFilter === 1 ? 'selected' : ''; ?>>1 - Invoiced</option>
                </select>
            </div>
            <div class="col-md-1 d-flex flex-column gap-1">
                <div class="form-check">
                    <input class="form-check-input" type="checkbox" name="balance_only" value="1" id="accBalOnly" <?php echo $balanceOnly ? 'checked' : ''; ?>>
                    <label class="form-check-label small" for="accBalOnly">Owing</label>
                </div>
            </div>
            <div class="col-12">
                <div class="acc-status-filters d-flex flex-wrap align-items-center gap-3">
                    <span class="small fw-semibold text-muted me-1">Fiscal status:</span>
                    <?php
                    $statusOptions = [
                        'fiscalized' => ['Fiscalized', 'status-fiscalized'],
                        'queued' => ['Queued', 'status-queued'],
                        'failed' => ['Failed', 'status-failed'],
                        'invoiced_unfiscalized' => ['Invoiced, not fiscalized', 'status-invoiced'],
                        'unprocessed' => ['Unprocessed', 'status-unprocessed'],
                    ];
                    foreach ($statusOptions as $statusValue => $statusMeta):
                    ?>
                        <div class="form-check form-check-inline m-0 acc-status-option <?php echo h($statusMeta[1]); ?>">
                            <input class="form-check-input" type="checkbox" name="fiscal_status[]"
                                   id="accStatus_<?php echo h($statusValue); ?>" value="<?php echo h($statusValue); ?>"
                                   <?php echo in_array($statusValue, $fiscalStatusFilters, true) ? 'checked' : ''; ?>>
                            <label class="form-check-label small" for="accStatus_<?php echo h($statusValue); ?>"><?php echo h($statusMeta[0]); ?></label>
                        </div>
                    <?php endforeach; ?>
                    <div class="ms-auto d-flex gap-2">
                        <button class="btn btn-sm btn-primary" type="submit"><i class="bi bi-funnel me-1"></i>Apply Filter</button>
                        <a class="btn btn-sm btn-outline-secondary" href="accounting.php?reset_filters=1"><i class="bi bi-arrow-counterclockwise me-1"></i>Reset</a>
                    </div>
                </div>
                <div class="small text-muted mt-1">Filters stay active until you press Reset. The default view is arrivals from today back 31 days, newest check-in first.</div>
            </div>
        </form>
    </div>
</div>

<form method="post" id="selectionForm">
    <?php if (function_exists('csrf_field')) echo csrf_field(); ?>
    <input type="hidden" name="action" value="prepare_allocation" id="selectionAction">
    <div class="card panel-card mb-4">
        <div class="card-header d-flex flex-wrap justify-content-between align-items-center gap-2">
            <span><i class="bi bi-journal-check me-2"></i>Bookings (<?php echo count($rows); ?> shown, cancelled excluded, max 300)</span>
            <span class="d-flex gap-2">
                <a class="btn btn-sm btn-outline-success"
                   href="?<?php echo h(http_build_query(['q'=>$q,'agent_id'=>$filterAgent,'period_type'=>$periodType,'date_from'=>$dateFrom,'date_to'=>$dateTo,'balance_only'=>$balanceOnly,'invoiced'=>($invoicedFilter === -1 ? '' : $invoicedFilter),'fiscal_status'=>$fiscalStatusFilters,'apply_filters'=>1,'export'=>'excel'])); ?>"
                   title="Export these search results to Excel (honours the current filters)">
                    <i class="bi bi-file-earmark-excel me-1"></i>Export to Excel
                </a>
                <button type="submit" class="btn btn-sm btn-primary" onclick="return accRequireSelection('prepare_allocation');"><i class="bi bi-cash-coin me-1"></i>Allocate Payment to Selected</button>
                <button type="submit" class="btn btn-sm btn-outline-dark" onclick="return accRequireSelection('bulk_fiscalize') && confirm('Fiscalize the selected bookings now?');"><i class="bi bi-receipt me-1"></i>Fiscalize Selected</button>
            </span>
        </div>
        <div class="card-body p-0">
            <div class="acc-legend px-3 pt-2" aria-label="Row colour legend">
                <span><i style="background:#e8f6ec;border:1px solid #198754;"></i>Fiscalized (locked)</span>
                <span><i style="background:#e7f4f9;border:1px solid #0dcaf0;"></i>Queued</span>
                <span><i style="background:#fdeaea;border:1px solid #dc3545;"></i>Fiscalization failed</span>
                <span><i style="background:#f4f1fb;border:1px solid #6f42c1;"></i>Invoiced, not fiscalized</span>
                <span class="text-muted">Row colours are a legend; use the real Fiscal status tick boxes above to filter.</span>
            </div>
            <?php if (!$rows): ?>
                <div class="p-3 text-muted small">No bookings match the current filters.</div>
            <?php else: ?>
            <div class="table-responsive" style="max-height:60vh;overflow:auto;">
                <table class="table table-sm table-hover align-middle mb-0">
                    <thead style="position:sticky;top:0;background:#fff;z-index:2;">
                        <tr>
                            <th><input type="checkbox" id="accSelectAll" title="Select all selectable"></th>
                            <th>Booking</th><th>Guest</th><th>Agent</th><th>Arrival</th><th>Departure</th><th>Folio</th>
                            <th class="text-end">Charges</th>
                            <th class="text-end" title="ZTA Levy 2% portion of Charges">ZTA</th>
                            <th class="text-end" title="VAT 15.5% portion of Charges">VAT</th>
                            <th class="text-end">Paid</th><th class="text-end">Balance</th>
                            <th>Pay Status</th><th>Fiscal</th><th>Invoiced</th><th></th>
                        </tr>
                    </thead>
                    <tbody>
                    <?php foreach ($rows as $r): ?>
                        <?php
                            $bal = (float)$r['balance'];
                            $ps = (int)($r['payment_status'] ?? 0);
                            $psLabel = $ps === 2 ? '<span class="badge text-bg-success">Paid</span>' : ($ps === 1 ? '<span class="badge text-bg-warning">Part</span>' : '<span class="badge text-bg-secondary">Unpaid</span>');

                            /* A booking is treated as FISCALIZED when either the
                               FDMS invoice table has a successful record for it, or
                               the fiscalization log's latest entry says 'done'. Such
                               rows are green and cannot be ticked again. */
                            $fs = (string)($r['fiscal_status'] ?? '');
                            $zimraNo = trim((string)($r['fdms_zimra_no'] ?? ''));
                            $isFiscalized = ($zimraNo !== '' || $fs === 'done');
                            $isQueued = (!$isFiscalized && $fs === 'queued');
                            $isFailed = (!$isFiscalized && $fs === 'failed');
                            $isInvoiced = ((int)($r['invoiced'] ?? 0) === 1);
                            $fdmsInvoiceId = (int)($r['fdms_invoice_id'] ?? 0);
                            $isCredited = ((int)($r['fdms_credited'] ?? 0) === 1);

                            $fsLabel = $isCredited ? '<span class="badge text-bg-primary">Credited</span>'
                                : ($isFiscalized ? '<span class="badge text-bg-success">Fiscalized</span>'
                                : ($isQueued ? '<span class="badge text-bg-info">Queued</span>'
                                : ($isFailed ? '<span class="badge text-bg-danger">Failed</span>' : '<span class="text-muted small">-</span>')));

                            $rowClass = $isCredited ? 'acc-row-credited'
                                : ($isFiscalized ? 'acc-row-fiscalized'
                                : ($isFailed ? 'acc-row-failed'
                                : ($isQueued ? 'acc-row-queued'
                                : ($isInvoiced ? 'acc-row-invoiced' : ''))));
                            $fiscalRef = $zimraNo !== '' ? $zimraNo : (string)($r['fiscal_ref'] ?? '');
                            $taxSplit = acc_tax_split($r['charges']);
                        ?>
                        <tr class="<?php echo h($rowClass); ?>">
                            <td>
                                <?php if ($isFiscalized): ?>
                                    <input type="checkbox" class="acc-row-check acc-fiscalized-check" name="order_ids[]" value="<?php echo (int)$r['id']; ?>"
                                           title="Already fiscalized - it can still receive a payment allocation, but it will be skipped by Fiscalize Selected.">
                                <?php else: ?>
                                    <input type="checkbox" class="acc-row-check" name="order_ids[]" value="<?php echo (int)$r['id']; ?>">
                                <?php endif; ?>
                            </td>
                            <td><strong><?php echo h(($r['order_no'] ?? '') !== '' ? $r['order_no'] : ('#' . $r['id'])); ?></strong></td>
                            <td><?php echo h($r['guest_name'] ?? ''); ?></td>
                            <td class="small text-muted"><?php echo h(($r['agent_name'] ?? '') !== '' ? $r['agent_name'] : ($r['originating_agent'] ?? '')); ?></td>
                            <td class="small"><?php echo h(substr((string)$r['check_in'],0,10)); ?></td>
                            <td class="small"><?php echo h(substr((string)$r['check_out'],0,10)); ?></td>
                            <td class="small"><?php echo h($r['folio_no'] ?? '-'); ?></td>
                            <td class="text-end"><?php echo money_fmt($r['charges']); ?></td>
                            <td class="text-end small text-muted"><?php echo money_fmt($taxSplit['zta']); ?></td>
                            <td class="text-end small text-muted"><?php echo money_fmt($taxSplit['vat']); ?></td>
                            <td class="text-end"><?php echo money_fmt($r['payments']); ?></td>
                            <td class="text-end fw-bold <?php echo $bal > 0.005 ? 'text-danger' : 'text-success'; ?>"><?php echo money_fmt($bal); ?></td>
                            <td><?php echo $psLabel; ?></td>
                            <td><?php echo $fsLabel; ?><?php if ($fiscalRef !== ''): ?><div class="small text-muted"><?php echo h($fiscalRef); ?></div><?php endif; ?></td>
                            <td><?php echo $isInvoiced ? '<span class="badge text-bg-dark">1</span>' : '<span class="badge text-bg-light text-muted">0</span>'; ?></td>
                            <td class="text-end text-nowrap">
                                <a class="btn btn-sm <?php echo $isFiscalized ? 'btn-success' : 'btn-outline-secondary'; ?>"
                                   href="fiscal_tax_invoice.php?order_id=<?php echo (int)$r['id']; ?>&finalize=1"
                                   target="_blank"
                                   title="<?php echo $isFiscalized ? 'Print the fiscal tax invoice (includes ZIMRA QR code)' : 'Print the tax invoice (marked NOT YET FISCALIZED until fiscalized)'; ?>">
                                    <i class="bi bi-printer"></i>
                                </a>
                                <?php if ($isFiscalized && $fdmsInvoiceId > 0 && !$isCredited): ?>
                                    <button type="button" class="btn btn-sm btn-outline-primary acc-credit-btn"
                                            data-invoice-id="<?php echo $fdmsInvoiceId; ?>"
                                            data-invoice-no="<?php echo h($r['fdms_invoice_no'] ?? ''); ?>"
                                            data-amount="<?php echo h(number_format((float)($r['fdms_amount'] ?? 0), 2, '.', '')); ?>"
                                            data-booking="<?php echo h(($r['order_no'] ?? '') !== '' ? $r['order_no'] : ('#' . $r['id'])); ?>"
                                            title="Issue a fiscal credit note against this invoice">
                                        <i class="bi bi-arrow-counterclockwise"></i> Credit
                                    </button>
                                <?php elseif ($isCredited): ?>
                                    <span class="badge text-bg-light text-muted" title="This invoice has already been credited">Credited</span>
                                <?php endif; ?>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
            <?php endif; ?>
        </div>
    </div>
</form>

<!-- =====================================================================
     RECENT BULK PAYMENTS
     ===================================================================== -->
<div class="card panel-card mb-4">
    <div class="card-header"><i class="bi bi-clock-history me-2"></i>Recent Bulk Payments</div>
    <div class="card-body p-0">
        <?php if (!$recentBulk): ?>
            <div class="p-3 text-muted small">No bulk payments recorded yet.</div>
        <?php else: ?>
        <div class="table-responsive">
            <table class="table table-sm align-middle mb-0">
                <thead><tr><th>#</th><th>Date</th><th>Payer</th><th>Method</th><th>Reference</th><th class="text-end">Total</th><th>Status</th><th>By</th><th></th></tr></thead>
                <tbody>
                <?php foreach ($recentBulk as $bp): ?>
                    <tr class="<?php echo (string)$bp['status'] === 'voided' ? 'table-secondary' : ''; ?>">
                        <td><?php echo (int)$bp['id']; ?></td>
                        <td class="small"><?php echo h($bp['payment_date']); ?></td>
                        <td><?php echo h($bp['payer_name']); ?></td>
                        <td class="small"><?php echo h($bp['payment_method']); ?></td>
                        <td class="small"><?php echo h($bp['reference']); ?></td>
                        <td class="text-end fw-bold"><?php echo money_fmt($bp['total_amount']); ?></td>
                        <td><?php echo (string)$bp['status'] === 'voided' ? '<span class="badge text-bg-danger">Voided</span>' : '<span class="badge text-bg-success">Posted</span>'; ?></td>
                        <td class="small text-muted"><?php echo h($bp['created_by_name']); ?></td>
                        <td class="text-end">
                            <a class="btn btn-sm btn-outline-primary" href="accounting.php?bulk_id=<?php echo (int)$bp['id']; ?>">Allocations</a>
                            <?php if ((string)$bp['status'] !== 'voided'): ?>
                            <form method="post" class="d-inline" onsubmit="return confirm('Void bulk payment #<?php echo (int)$bp['id']; ?>? All its folio postings will be reversed and booking payment statuses resynced.');">
                                <?php if (function_exists('csrf_field')) echo csrf_field(); ?>
                                <input type="hidden" name="action" value="void_bulk_payment">
                                <input type="hidden" name="bulk_payment_id" value="<?php echo (int)$bp['id']; ?>">
                                <button class="btn btn-sm btn-outline-danger">Void</button>
                            </form>
                            <?php endif; ?>
                        </td>
                    </tr>
                    <?php if ($viewBulkId === (int)$bp['id'] && $viewBulkAllocs): ?>
                    <tr><td colspan="9" class="bg-light">
                        <div class="small fw-bold mb-1">Allocations for bulk payment #<?php echo (int)$bp['id']; ?>:</div>
                        <table class="table table-sm mb-0">
                            <thead><tr><th>Booking</th><th>Guest</th><th class="text-end">Balance before</th><th class="text-end">Allocated</th><th class="small">Posted</th></tr></thead>
                            <tbody>
                            <?php foreach ($viewBulkAllocs as $va): ?>
                                <tr>
                                    <td><?php echo h(($va['order_no'] ?? '') !== '' ? $va['order_no'] : ('#' . $va['order_id'])); ?></td>
                                    <td><?php echo h($va['guest_name'] ?? ''); ?></td>
                                    <td class="text-end"><?php echo money_fmt($va['balance_before']); ?></td>
                                    <td class="text-end fw-bold"><?php echo money_fmt($va['amount']); ?></td>
                                    <td class="small text-muted"><?php echo h($va['created_at']); ?></td>
                                </tr>
                            <?php endforeach; ?>
                            </tbody>
                        </table>
                    </td></tr>
                    <?php endif; ?>
                <?php endforeach; ?>
                </tbody>
            </table>
        </div>
        <?php endif; ?>
    </div>
</div>

<!-- =====================================================================
     CREDIT NOTE MODAL
     ===================================================================== -->
<div class="modal fade" id="creditNoteModal" tabindex="-1">
  <div class="modal-dialog modal-lg">
    <div class="modal-content">
      <form method="post">
        <?php if (function_exists('csrf_field')) echo csrf_field(); ?>
        <input type="hidden" name="action" value="issue_credit_note">
        <input type="hidden" name="invoice_id" id="cnInvoiceId">
        <div class="modal-header">
          <h5 class="modal-title"><i class="bi bi-arrow-counterclockwise me-2"></i>Issue Fiscal Credit Note</h5>
          <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
        </div>
        <div class="modal-body">
          <div class="alert alert-secondary small mb-3">
            Crediting invoice <strong id="cnInvoiceNo"></strong> for booking <strong id="cnBooking"></strong>,
            original value <strong id="cnOriginalAmount"></strong>.
          </div>

          <div class="mb-3">
            <div class="form-check">
              <input class="form-check-input" type="radio" name="credit_mode" value="full" id="cnModeFull" checked>
              <label class="form-check-label" for="cnModeFull">
                <strong>Full credit note</strong> - credit the entire invoice. Nothing is reinvoiced.
              </label>
            </div>
            <div class="form-check mt-2">
              <input class="form-check-input" type="radio" name="credit_mode" value="correction" id="cnModeCorrection">
              <label class="form-check-label" for="cnModeCorrection">
                <strong>Partial correction</strong> - credit the customer part of the invoice.
              </label>
            </div>
          </div>

          <div id="cnCorrectionBox" class="d-none">
            <div class="alert alert-info small">
              <i class="bi bi-info-circle me-1"></i>
              ZIMRA does not permit a partial credit note. The invoice is credited <strong>in full</strong>,
              then a <strong>replacement invoice</strong> is raised for the remainder. Both are fiscalized automatically.
            </div>
            <div class="row g-2 align-items-end">
              <div class="col-md-4">
                <label class="form-label small">Amount to credit the customer</label>
                <input type="number" step="0.01" min="0.01" name="credit_amount" id="cnCreditAmount" class="form-control form-control-sm text-end" placeholder="e.g. 25.00">
              </div>
              <div class="col-md-8">
                <div class="small text-muted">
                  Credit note will be for <strong id="cnFullPreview">0.00</strong> (the full invoice)<br>
                  Replacement invoice will be raised for <strong id="cnReplacementPreview" class="text-primary">0.00</strong>
                </div>
              </div>
            </div>
          </div>

          <div class="row g-2 mt-2">
            <div class="col-md-5">
              <label class="form-label small">Reason (ZIMRA code)</label>
              <select name="reason_code" class="form-select form-select-sm">
                <option value="1">1 - Cancellation of the transaction</option>
                <option value="2">2 - Return of goods / services</option>
                <option value="3" selected>3 - Discount or price adjustment</option>
                <option value="4">4 - Other correction</option>
              </select>
            </div>
            <div class="col-md-7">
              <label class="form-label small">Reason detail * <span class="text-muted">(sent to ZIMRA as the comment)</span></label>
              <input type="text" name="reason_text" class="form-control form-control-sm" required placeholder="e.g. Late checkout charge waived by manager">
            </div>
          </div>
        </div>
        <div class="modal-footer">
          <button type="button" class="btn btn-outline-secondary" data-bs-dismiss="modal">Cancel</button>
          <button class="btn btn-primary" onclick="return confirm('Submit this credit note to ZIMRA?\n\nA fiscalized credit note CANNOT be reversed.');">
            <i class="bi bi-send me-1"></i>Fiscalize Credit Note
          </button>
        </div>
      </form>
    </div>
  </div>
</div>

<?php
/* Recent credit notes */
$recentCreditNotes = [];
try {
    if (acc_table_exists($pdo, 'fdms_credit_notes')) {
        $recentCreditNotes = $pdo->query("
            SELECT cn.*, o.order_no, ri.invoice_no AS replacement_no, ri.amount_including_tax AS replacement_total
            FROM fdms_credit_notes cn
            LEFT JOIN orders o ON o.id = cn.order_id
            LEFT JOIN fdms_fiscal_invoices ri ON ri.id = cn.replacement_invoice_id
            ORDER BY cn.id DESC LIMIT 15")->fetchAll(PDO::FETCH_ASSOC);
    }
} catch (Throwable $e) { $recentCreditNotes = []; }
?>
<?php if ($recentCreditNotes): ?>
<div class="card panel-card mb-4">
    <div class="card-header"><i class="bi bi-arrow-counterclockwise me-2"></i>Recent Credit Notes</div>
    <div class="card-body p-0">
        <div class="table-responsive">
            <table class="table table-sm align-middle mb-0">
                <thead><tr><th>CN</th><th>Booking</th><th>Credits Invoice</th><th>Type</th><th class="text-end">Credited</th><th>Replacement</th><th>ZIMRA</th><th>Status</th><th></th></tr></thead>
                <tbody>
                <?php foreach ($recentCreditNotes as $cnRow): ?>
                    <?php
                        $cnOk = ((int)$cnRow['success'] === 1);
                        $isCorr = ((string)$cnRow['credit_type'] === 'correction');
                        $needsRetry = ($cnOk && $isCorr && (int)($cnRow['replacement_invoice_id'] ?? 0) === 0);
                    ?>
                    <tr class="<?php echo $needsRetry ? 'table-warning' : ''; ?>">
                        <td><strong>CN-<?php echo (int)$cnRow['id']; ?></strong></td>
                        <td class="small"><?php echo h(($cnRow['order_no'] ?? '') !== '' ? $cnRow['order_no'] : ('#' . $cnRow['order_id'])); ?></td>
                        <td class="small"><?php echo h($cnRow['original_invoice_no']); ?></td>
                        <td><?php echo $isCorr ? '<span class="badge text-bg-info">Correction</span>' : '<span class="badge text-bg-secondary">Full</span>'; ?></td>
                        <td class="text-end fw-bold text-danger">-<?php echo money_fmt($cnRow['amount_including_tax']); ?></td>
                        <td class="small">
                            <?php if (!empty($cnRow['replacement_no'])): ?>
                                <?php echo h($cnRow['replacement_no']); ?> (<?php echo money_fmt($cnRow['replacement_total']); ?>)
                            <?php elseif ($needsRetry): ?>
                                <span class="text-danger fw-bold">PENDING - <?php echo money_fmt($cnRow['replacement_amount']); ?></span>
                            <?php else: ?><span class="text-muted">-</span><?php endif; ?>
                        </td>
                        <td class="small"><?php echo h($cnRow['zimra_credit_no'] ?? ''); ?></td>
                        <td><?php echo $cnOk ? '<span class="badge text-bg-success">Fiscalized</span>' : '<span class="badge text-bg-danger">Failed</span>'; ?></td>
                        <td class="text-end text-nowrap">
                            <a class="btn btn-sm btn-outline-primary" href="credit_note_pdf.php?credit_note_id=<?php echo (int)$cnRow['id']; ?>" target="_blank"><i class="bi bi-printer"></i></a>
                            <?php if ($needsRetry): ?>
                            <form method="post" class="d-inline" onsubmit="return confirm('Retry raising the replacement invoice? The credit note is already filed - this only raises the missing replacement.');">
                                <?php if (function_exists('csrf_field')) echo csrf_field(); ?>
                                <input type="hidden" name="action" value="retry_replacement_invoice">
                                <input type="hidden" name="credit_note_id" value="<?php echo (int)$cnRow['id']; ?>">
                                <button class="btn btn-sm btn-warning">Retry replacement</button>
                            </form>
                            <?php endif; ?>
                        </td>
                    </tr>
                <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>
<?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}

/* Row colour coding - fiscalized rows are unmistakable so nobody ticks one
   for fiscalization again. Left border carries the meaning even for anyone
   with colour-vision difficulty, and the Fiscal badge states it in words. */
tr.acc-row-fiscalized{background:#e8f6ec!important;border-left:4px solid #198754;}
tr.acc-row-fiscalized:hover{background:#dcf0e3!important;}
tr.acc-row-fiscalized td{color:#14532d;}
tr.acc-row-queued{background:#e7f4f9!important;border-left:4px solid #0dcaf0;}
tr.acc-row-failed{background:#fdeaea!important;border-left:4px solid #dc3545;}
tr.acc-row-invoiced{background:#f4f1fb!important;border-left:4px solid #6f42c1;}
tr.acc-row-credited{background:#eef2fb!important;border-left:4px solid #0d6efd;}
tr.acc-row-credited td{color:#0a3a7a;}
.acc-legend span{display:inline-flex;align-items:center;gap:4px;margin-right:14px;font-size:.72rem;color:#475569;}
.acc-legend i{width:12px;height:12px;border-radius:3px;display:inline-block;}
.acc-status-filters{padding:9px 11px;border:1px solid #dbe3ea;border-radius:9px;background:#f8fafc;}
.acc-status-option{padding:4px 8px 4px 30px;border-radius:7px;border:1px solid transparent;}
.acc-status-option.status-fiscalized{background:#e8f6ec;border-color:#b7dfc2;}
.acc-status-option.status-queued{background:#e7f4f9;border-color:#b7e1ed;}
.acc-status-option.status-failed{background:#fdeaea;border-color:#f3bcbc;}
.acc-status-option.status-invoiced{background:#f4f1fb;border-color:#d8ccef;}
.acc-status-option.status-unprocessed{background:#fff;border-color:#dbe3ea;}
.acc-status-option .form-check-input{cursor:pointer;}
.acc-status-option .form-check-label{cursor:pointer;font-weight:600;}
</style>

<script>
document.addEventListener('DOMContentLoaded', function(){
    /* Select-all in the bookings browser */
    var selAll = document.getElementById('accSelectAll');
    if (selAll) {
        selAll.addEventListener('change', function(){
            document.querySelectorAll('.acc-row-check').forEach(function(cb){ cb.checked = selAll.checked; });
        });
    }

    /* Allocation total live sum + fill/clear helpers */
    function sumAllocs(){
        var total = 0;
        document.querySelectorAll('#allocTable .alloc-amount').forEach(function(el){
            total += parseFloat(el.value || '0') || 0;
        });
        var lbl = document.getElementById('allocTotal');
        if (lbl) lbl.textContent = total.toFixed(2);
    }
    var allocTable = document.getElementById('allocTable');
    if (allocTable) {
        allocTable.addEventListener('input', sumAllocs);
        sumAllocs();
    }
    var fillBtn = document.getElementById('fillBalancesBtn');
    if (fillBtn) fillBtn.addEventListener('click', function(){
        document.querySelectorAll('#allocTable .alloc-amount').forEach(function(el){
            el.value = el.getAttribute('data-balance') || '0.00';
        });
        sumAllocs();
    });
    var clearBtn = document.getElementById('clearAllocBtn');
    if (clearBtn) clearBtn.addEventListener('click', function(){
        document.querySelectorAll('#allocTable .alloc-amount').forEach(function(el){ el.value = '0.00'; });
        sumAllocs();
    });

    /* ---------------------------------------------------------------
       Credit note modal: populate from the clicked row, toggle the
       correction box, and preview the ZIMRA chain live -
       credit note = FULL invoice; replacement = invoice - credit.
       --------------------------------------------------------------- */
    var cnModalEl = document.getElementById('creditNoteModal');
    var cnOriginal = 0;
    document.querySelectorAll('.acc-credit-btn').forEach(function(btn){
        btn.addEventListener('click', function(){
            cnOriginal = parseFloat(btn.getAttribute('data-amount') || '0') || 0;
            document.getElementById('cnInvoiceId').value = btn.getAttribute('data-invoice-id') || '';
            document.getElementById('cnInvoiceNo').textContent = btn.getAttribute('data-invoice-no') || '';
            document.getElementById('cnBooking').textContent = btn.getAttribute('data-booking') || '';
            document.getElementById('cnOriginalAmount').textContent = cnOriginal.toFixed(2);
            document.getElementById('cnFullPreview').textContent = cnOriginal.toFixed(2);
            var amtEl = document.getElementById('cnCreditAmount');
            if (amtEl) { amtEl.value = ''; amtEl.setAttribute('max', cnOriginal.toFixed(2)); }
            document.getElementById('cnReplacementPreview').textContent = '0.00';
            document.getElementById('cnModeFull').checked = true;
            document.getElementById('cnCorrectionBox').classList.add('d-none');
            if (cnModalEl && window.bootstrap) new bootstrap.Modal(cnModalEl).show();
        });
    });

    function cnToggleMode(){
        var isCorrection = document.getElementById('cnModeCorrection').checked;
        var box = document.getElementById('cnCorrectionBox');
        var amtEl = document.getElementById('cnCreditAmount');
        if (isCorrection) { box.classList.remove('d-none'); if (amtEl) amtEl.required = true; }
        else { box.classList.add('d-none'); if (amtEl) { amtEl.required = false; amtEl.value = ''; } }
    }
    ['cnModeFull','cnModeCorrection'].forEach(function(id){
        var el = document.getElementById(id);
        if (el) el.addEventListener('change', cnToggleMode);
    });

    var cnAmt = document.getElementById('cnCreditAmount');
    if (cnAmt) cnAmt.addEventListener('input', function(){
        var credit = parseFloat(cnAmt.value || '0') || 0;
        var replacement = cnOriginal - credit;
        if (replacement < 0) replacement = 0;
        document.getElementById('cnReplacementPreview').textContent = replacement.toFixed(2);
    });
});

/*
   Selection routing. Fiscalized rows stay tickable - a fiscalized booking
   can still receive a payment allocation - but they are green, labelled, and
   automatically SKIPPED by Fiscalize Selected (client-side here, and
   enforced again server-side so a stale page can never double-fiscalize).
*/
function accRequireSelection(action){
    var checked = [];
    var fiscalizedChecked = 0;
    document.querySelectorAll('.acc-row-check').forEach(function(cb){
        if (!cb.checked) return;
        checked.push(cb);
        if (cb.classList.contains('acc-fiscalized-check')) fiscalizedChecked++;
    });

    if (!checked.length) { alert('Select at least one booking first.'); return false; }

    if (action === 'bulk_fiscalize') {
        var toFiscalize = checked.length - fiscalizedChecked;
        if (toFiscalize <= 0) {
            alert('Every booking you selected has already been fiscalized.\n\nGreen rows cannot be fiscalized again - select bookings that are not yet fiscalized.');
            return false;
        }
        if (fiscalizedChecked > 0 && !confirm(fiscalizedChecked + ' of the selected booking(s) are already fiscalized and will be SKIPPED.\n\nFiscalize the remaining ' + toFiscalize + '?')) {
            return false;
        }
    }

    var actionInput = document.getElementById('selectionAction');
    if (actionInput) actionInput.value = action;
    return true;
}
</script>

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