<?php
/**
 * payment_support.php - shared payment posting & folio resolution for RoomZ.
 *
 * These functions used to live only inside payments.php, which meant anything
 * else needing to take a payment (the Booking Control Centre, a future POS
 * bridge, a mobile front desk) either could not post one or had to
 * re-implement the rules and drift out of step.
 *
 * That is exactly what happened to rate resolution: fetch_rate_plan() ended up
 * defined THREE times - in functions.php, rate_support.php and
 * reservation_form.php - each guarded by function_exists(), so whichever file
 * loaded first silently won. The copies had drifted to different signatures and
 * different defaults, and bookings priced differently depending on include
 * order. Payment maths is money moving in and out of a folio; the same drift
 * here would be worse.
 *
 * Every file that needs to take a payment includes THIS file. There is one
 * definition of:
 *   - which folio a payment posts to (master / room / individual guest)
 *   - the ROE conversion (entered currency -> USD base)
 *   - the 3% POS/Card surcharge, on principal only
 *   - the optional gratuity, via inventory item 2133, never surcharged
 *   - how a folio's totals are recalculated, including voids and reversals
 *
 * All functions are guarded with function_exists(), so including this file
 * alongside a page that already defines them is safe.
 *
 * ACCOUNTING CONTRACT (do not change without checking every caller):
 *   - payment.amount is stored in USD BASE, and is the effective amount
 *     actually collected. For POS/Card that INCLUDES the 3% surcharge.
 *   - Optional gratuity is included in the collected amount but is posted as a
 *     separate gratuity charge, and is never surcharged.
 *   - The original currency and ROE are retained in payment.currency /
 *     payment.roe_atm.
 *   - ROE means: selected currency per 1 USD.  Effective USD = entered / ROE.
 */

if (!defined('CC_SURCHARGE_SERVICE_ID')) {
    define('CC_SURCHARGE_SERVICE_ID', 11);
}
if (!defined('CC_SURCHARGE_RATE')) {
    define('CC_SURCHARGE_RATE', 0.03);
}
if (!defined('OPTIONAL_GRATUITY_INVENTORY_ITEM_ID')) {
    define('OPTIONAL_GRATUITY_INVENTORY_ITEM_ID', 2133);
}

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);
    }
}

if (!function_exists('currency_rate_direction_note')) {
    function currency_rate_direction_note(): string
    {
        return 'ROE is locked as Currency per 1 USD. Example: ZAR 18.50, EUR 1.02, GBP 0.85.';
    }
}

if (!function_exists('currency_code_from_row')) {
    function currency_code_from_row(array $row): string
    {
        $code = strtoupper(trim((string)($row['CurrencyCode'] ?? '')));
        if ($code === '') {
            $code = strtoupper(trim((string)($row['Currency'] ?? '')));
        }
        return $code;
    }
}

if (!function_exists('folio_tx_badge')) {
    function folio_tx_badge(string $type): string
    {
        $type = strtolower(trim($type));

        switch ($type) {
            case 'room_charge':
                return '<span class="badge text-bg-primary">Room Charge</span>';
            case 'payment':
                return '<span class="badge text-bg-success">Payment</span>';
            case 'extra':
                return '<span class="badge text-bg-warning text-dark">Extra</span>';
            case 'cc_charge':
            case 'card_surcharge':
                return '<span class="badge text-bg-info text-dark">CC Charge</span>';
            case 'adjustment':
                return '<span class="badge text-bg-secondary">Adjustment</span>';
            case 'restaurant':
            case 'minibar':
            case 'laundry':
            case 'transfer':
            case 'park_fee':
                return '<span class="badge text-bg-warning text-dark">' . htmlspecialchars(ucwords(str_replace('_', ' ', $type)), ENT_QUOTES, 'UTF-8') . '</span>';
            case 'payment_void':
            case 'void_payment':
            case 'payment_reversal':
            case 'payment_refund':
            case 'refund':
            case 'reversal':
                return '<span class="badge text-bg-danger">Payment Reversal</span>';
            default:
                return '<span class="badge text-bg-light text-dark border">' . htmlspecialchars(ucwords(str_replace('_', ' ', $type)), ENT_QUOTES, 'UTF-8') . '</span>';
        }
    }
}

if (!function_exists('season_badge_class')) {
    function season_badge_class(?string $color): string
    {
        $allowed = ['primary', 'secondary', 'success', 'danger', 'warning', 'info', 'dark'];
        $color = strtolower(trim((string)$color));
        return in_array($color, $allowed, true) ? $color : 'secondary';
    }
}

if (!function_exists('pay_table_exists')) {
    function pay_table_exists(PDO $pdo, string $tableName, bool $refresh = false): bool
    {
        static $cache = [];
        /*
           $refresh exists because this cache is per-request and a table can be
           CREATED mid-request - ensure_payment_audit_log_table() does exactly
           that. Without a way to invalidate, a 'false' cached before the create
           silently persisted, and the reversal audit row was dropped on the
           floor even though the table now existed.
        */
        if (!$refresh && array_key_exists($tableName, $cache)) {
            return $cache[$tableName];
        }
        try {
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table_name");
            $stmt->execute([':table_name' => $tableName]);
            $cache[$tableName] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) {
            $cache[$tableName] = false;
        }
        return $cache[$tableName];
    }
}

if (!function_exists('pay_column_exists')) {
    function pay_column_exists(PDO $pdo, string $tableName, string $columnName): bool
    {
        static $cache = [];
        $key = $tableName . '.' . $columnName;
        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 = :table_name AND COLUMN_NAME = :column_name");
            $stmt->execute([':table_name' => $tableName, ':column_name' => $columnName]);
            $cache[$key] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) {
            $cache[$key] = false;
        }
        return $cache[$key];
    }
}

if (!function_exists('pay_column_max_length')) {
    function pay_column_max_length(PDO $pdo, string $tableName, string $columnName, int $fallback = 100): int
    {
        static $cache = [];
        $key = $tableName . '.' . $columnName;
        if (array_key_exists($key, $cache)) {
            return $cache[$key];
        }
        try {
            $stmt = $pdo->prepare("SELECT CHARACTER_MAXIMUM_LENGTH, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table_name AND COLUMN_NAME = :column_name LIMIT 1");
            $stmt->execute([':table_name' => $tableName, ':column_name' => $columnName]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            $len = $row ? (int)($row['CHARACTER_MAXIMUM_LENGTH'] ?? 0) : 0;
            $dataType = $row ? strtolower((string)($row['DATA_TYPE'] ?? '')) : '';
            if ($len <= 0 && in_array($dataType, ['text', 'mediumtext', 'longtext'], true)) {
                $len = 65000; // effectively unbounded for our purposes
            }
            $cache[$key] = $len > 0 ? $len : $fallback;
        } catch (Throwable $e) {
            $cache[$key] = $fallback;
        }
        return $cache[$key];
    }
}

if (!function_exists('current_user_id_for_audit')) {
    function current_user_id_for_audit(): ?int
    {
        foreach (['user_id', 'userid', 'id', 'staffid'] as $key) {
            if (!empty($_SESSION[$key]) && (int)$_SESSION[$key] > 0) {
                return (int)$_SESSION[$key];
            }
        }
        return null;
    }
}

if (!function_exists('next_folio_no')) {
    function next_folio_no(PDO $pdo, int $propertyId = 0): string
    {
        do {
            $prefix = 'FOL';
            if ($propertyId > 0) {
                $stmt = $pdo->prepare("SELECT code FROM properties WHERE id = :id LIMIT 1");
                $stmt->execute([':id' => $propertyId]);
                $code = trim((string)($stmt->fetchColumn() ?: ''));
                if ($code !== '') {
                    $prefix = 'FOL-' . strtoupper($code);
                }
            }

            $folioNo = $prefix . '-' . date('YmdHis') . '-' . random_int(100, 999);

            $stmt = $pdo->prepare("
                SELECT COUNT(*)
                FROM folios
                WHERE folio_no = :folio_no
            ");
            $stmt->execute([':folio_no' => $folioNo]);
            $exists = (int)$stmt->fetchColumn() > 0;
        } while ($exists);

        return $folioNo;
    }
}

if (!function_exists('build_room_mix_map')) {
    function build_room_mix_map(PDO $pdo, int $orderId): array
    {
        $stmt = $pdo->prepare("
            SELECT
                rmg.room_type_id,
                rt.title AS room_type_title,
                COUNT(*) AS qty_rooms
            FROM order_rooming rmg
            LEFT JOIN room_types rt ON rt.id = rmg.room_type_id
            WHERE rmg.order_id = :order_id
            GROUP BY rmg.room_type_id, rt.title
            ORDER BY rt.title ASC, rmg.room_type_id ASC
        ");
        $stmt->execute([':order_id' => $orderId]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $parts = [];
        $map = [];
        foreach ($rows as $row) {
            $roomTypeId = (int)($row['room_type_id'] ?? 0);
            $title = trim((string)($row['room_type_title'] ?? ''));
            if ($title === '') {
                $title = 'Room Type #' . $roomTypeId;
            }
            $qty = (int)($row['qty_rooms'] ?? 0);
            $label = $title . ' x ' . $qty;
            $parts[] = $label;
            if ($roomTypeId > 0) {
                $map[$roomTypeId] = $title;
            }
        }

        return [
            'mix' => implode(', ', $parts),
            'map' => $map,
        ];
    }
}

if (!function_exists('get_or_create_master_folio')) {
    function get_or_create_master_folio(PDO $pdo, int $orderId): int
    {
        $stmt = $pdo->prepare("
            SELECT id
            FROM folios
            WHERE order_id = ? AND folio_type = 'master'
            LIMIT 1
        ");
        $stmt->execute([$orderId]);
        $id = (int)($stmt->fetchColumn() ?: 0);
        if ($id > 0) {
            return $id;
        }

        $stmt = $pdo->prepare("
            SELECT property_id, order_no
            FROM orders
            WHERE id = ?
            LIMIT 1
        ");
        $stmt->execute([$orderId]);
        $o = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$o) {
            throw new RuntimeException('Booking not found while creating master folio.');
        }

        $pdo->prepare("
            INSERT INTO folios
            (
                property_id,
                order_id,
                folio_no,
                folio_name,
                folio_type,
                status,
                total_charges,
                total_payments,
                balance
            )
            VALUES
            (
                ?, ?, ?, ?, 'master', 1, 0, 0, 0
            )
        ")->execute([
            (int)($o['property_id'] ?? 0),
            $orderId,
            'FOL-M-' . $orderId,
            'Master Folio - ' . ($o['order_no'] ?? ('Booking #' . $orderId))
        ]);

        return (int)$pdo->lastInsertId();
    }
}

if (!function_exists('get_room_folios')) {
    function get_room_folios(PDO $pdo, int $orderId): array
    {
        $rows = $pdo->prepare("
            SELECT 
                orm.id AS rooming_id,
                orm.room_id,
                orm.room_type_id,
                rt.title AS room_type_title,
                r.room_no,
                CONCAT(COALESCE(g.firstname,''),' ',COALESCE(g.lastname,'')) AS guest
            FROM order_rooming orm
            LEFT JOIN rooms r ON r.id = orm.room_id
            LEFT JOIN room_types rt ON rt.id = orm.room_type_id
            LEFT JOIN orders o ON o.id = orm.order_id
            LEFT JOIN guests g ON g.id = o.guest_id
            WHERE orm.order_id = ?
            ORDER BY r.room_no ASC, orm.room_no ASC, orm.id ASC
        ");
        $rows->execute([$orderId]);
        return $rows->fetchAll(PDO::FETCH_ASSOC);
    }
}

if (!function_exists('get_guest_folios')) {
    function get_guest_folios(PDO $pdo, int $orderId): array
    {
        $stmt = $pdo->prepare("
            SELECT
                f.id AS folio_id,
                f.order_id,
                f.room_id,
                f.rooming_id,
                f.guest_id,
                f.occupant_id,
                f.occupant_name,
                f.folio_no,
                f.folio_name,
                f.balance,
                r.room_no,
                rt.title AS room_type_title
            FROM folios f
            LEFT JOIN rooms r ON r.id = f.room_id
            LEFT JOIN order_rooming orm ON orm.id = f.rooming_id
            LEFT JOIN room_types rt ON rt.id = orm.room_type_id
            WHERE f.order_id = :order_id
              AND f.folio_type = 'guest'
              AND COALESCE(f.status,1) = 1
            ORDER BY COALESCE(r.room_no, ''), f.occupant_name ASC, f.id ASC
        ");
        $stmt->execute([':order_id' => $orderId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

if (!function_exists('get_payment_target_folio')) {
    function get_payment_target_folio(PDO $pdo, int $orderId, string $postTo, int $masterFolioId): int
    {
        if ($postTo === '' || $postTo === 'master') {
            return $masterFolioId;
        }

        $target = json_decode($postTo, true);
        if (!is_array($target)) {
            throw new RuntimeException('Invalid payment folio selection.');
        }

        $targetType = strtolower(trim((string)($target['target_type'] ?? $target['folio_type'] ?? 'room')));

        if ($targetType === 'guest') {
            $folioId = (int)($target['folio_id'] ?? 0);
            if ($folioId <= 0) {
                throw new RuntimeException('Invalid guest folio selected.');
            }

            $stmt = $pdo->prepare("
                SELECT id
                FROM folios
                WHERE id = :folio_id
                  AND order_id = :order_id
                  AND folio_type = 'guest'
                  AND COALESCE(status,1) = 1
                LIMIT 1
            ");
            $stmt->execute([
                ':folio_id' => $folioId,
                ':order_id' => $orderId,
            ]);

            $validFolioId = (int)($stmt->fetchColumn() ?: 0);
            if ($validFolioId <= 0) {
                throw new RuntimeException('Selected guest folio does not belong to this booking or is inactive.');
            }

            return $validFolioId;
        }

        return get_or_create_room_folio($pdo, $orderId, $target);
    }
}

if (!function_exists('ensure_folio_exists')) {
    function ensure_folio_exists(PDO $pdo, int $orderId): int
    {
        $stmt = $pdo->prepare("
            SELECT id
            FROM folios
            WHERE order_id = :order_id
            LIMIT 1
        ");
        $stmt->execute([':order_id' => $orderId]);
        $folioId = (int)($stmt->fetchColumn() ?: 0);

        if ($folioId > 0) {
            return $folioId;
        }

        $stmt = $pdo->prepare("
            SELECT
                o.id,
                o.property_id,
                o.order_no,
                g.firstname,
                g.lastname
            FROM orders o
            LEFT JOIN guests g ON g.id = o.guest_id
            WHERE o.id = :id
            LIMIT 1
        ");
        $stmt->execute([':id' => $orderId]);
        $order = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$order) {
            throw new RuntimeException('Booking not found for folio creation.');
        }

        $guestName = trim(($order['firstname'] ?? '') . ' ' . ($order['lastname'] ?? ''));
        if ($guestName === '') {
            $guestName = 'Guest';
        }

        $propertyId = (int)($order['property_id'] ?? 0);
        $folioNo = next_folio_no($pdo, $propertyId);
        $folioName = ($order['order_no'] ?: ('Booking #' . $orderId)) . ' - ' . $guestName;

        $stmt = $pdo->prepare("
            INSERT INTO folios
            (
                property_id,
                order_id,
                folio_no,
                folio_name,
                status,
                total_charges,
                total_payments,
                balance
            )
            VALUES
            (
                :property_id,
                :order_id,
                :folio_no,
                :folio_name,
                1,
                0,
                0,
                0
            )
        ");
        $stmt->execute([
            ':property_id' => $propertyId,
            ':order_id'    => $orderId,
            ':folio_no'    => $folioNo,
            ':folio_name'  => $folioName,
        ]);

        return (int)$pdo->lastInsertId();
    }
}

if (!function_exists('get_or_create_room_folio')) {
    function get_or_create_room_folio(PDO $pdo, int $orderId, array $rooming): int
    {
        $roomingId = (int)($rooming['rooming_id'] ?? 0);
        if ($roomingId <= 0) {
            throw new RuntimeException('Invalid room folio target.');
        }

        $stmt = $pdo->prepare("
            SELECT id
            FROM folios
            WHERE order_id = ? AND rooming_id = ? AND folio_type = 'room'
            LIMIT 1
        ");
        $stmt->execute([$orderId, $roomingId]);
        $id = (int)($stmt->fetchColumn() ?: 0);
        if ($id > 0) {
            return $id;
        }

        $stmt = $pdo->prepare("
            SELECT property_id, order_no
            FROM orders
            WHERE id = ?
            LIMIT 1
        ");
        $stmt->execute([$orderId]);
        $order = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$order) {
            throw new RuntimeException('Booking not found while creating room folio.');
        }

        $roomNo = trim((string)($rooming['room_no'] ?? ''));
        $guest  = trim((string)($rooming['guest'] ?? ''));
        $roomTypeTitle = trim((string)($rooming['room_type_title'] ?? ''));
        if ($guest === '') {
            $guest = 'Guest';
        }

        $folioNo = 'FOL-R-' . ($roomNo !== '' ? $roomNo : $roomingId) . '-' . $orderId;
        $folioName = 'Room ' . ($roomNo !== '' ? $roomNo : 'N/A') . ' - ' . $guest;
        if ($roomTypeTitle !== '') {
            $folioName .= ' • ' . $roomTypeTitle;
        }

        $pdo->prepare("
            INSERT INTO folios
            (
                order_id,
                property_id,
                folio_no,
                folio_name,
                folio_type,
                room_id,
                rooming_id,
                status,
                total_charges,
                total_payments,
                balance
            )
            VALUES
            (
                ?, ?, ?, ?, 'room', ?, ?, 1, 0, 0, 0
            )
        ")->execute([
            $orderId,
            (int)($order['property_id'] ?? 0),
            $folioNo,
            $folioName,
            (int)($rooming['room_id'] ?? 0),
            $roomingId
        ]);

        return (int)$pdo->lastInsertId();
    }
}

if (!function_exists('recalculate_folio_totals')) {
    function recalculate_folio_totals(PDO $pdo, int $folioId): void
    {
        /*
           IMPORTANT ACCOUNTING RULE
           -------------------------
           A payment void/reversal must NOT reduce booking/accommodation value.

           Correct behaviour:
             - charges = real revenue/charge debits only (room_charge, extra, etc.)
             - payments = net valid payment credits less payment void/reversal debits
             - balance = charges - net payments

           This supports both common void styles:
             1) original payment row/folio transaction marked is_voided = 1
             2) separate reversal row posted with debit > 0 or negative credit
        */
        $stmt = $pdo->prepare("
            SELECT
                COALESCE(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'
                             )
                             AND LOWER(COALESCE(source_table, '')) NOT IN (
                                'payment', 'payments', 'receipt', 'receipts'
                             )
                        THEN COALESCE(debit, 0)
                        ELSE 0
                    END
                ), 0) AS total_charges,

                GREATEST(
                    0,
                    COALESCE(SUM(
                        CASE
                            WHEN COALESCE(is_voided, 0) = 0
                                 AND (
                                    LOWER(COALESCE(tx_type, '')) IN (
                                        'payment', 'receipt', 'deposit', 'payment_received',
                                        'folio_payment', 'guest_payment'
                                    )
                                    OR LOWER(COALESCE(source_table, '')) IN (
                                        'payment', 'payments', 'receipt', 'receipts'
                                    )
                                 )
                                 AND COALESCE(credit, 0) > 0
                            THEN COALESCE(credit, 0)

                            WHEN COALESCE(is_voided, 0) = 0
                                 AND (
                                    LOWER(COALESCE(tx_type, '')) IN (
                                        'payment_void', 'void_payment', 'payment_reversal',
                                        'reversal', 'refund', 'payment_refund'
                                    )
                                    OR LOWER(COALESCE(description, '')) LIKE '%void%'
                                    OR LOWER(COALESCE(description, '')) LIKE '%reversal%'
                                    OR LOWER(COALESCE(description, '')) LIKE '%refund%'
                                 )
                                 AND COALESCE(debit, 0) > 0
                            THEN -1 * COALESCE(debit, 0)

                            WHEN COALESCE(is_voided, 0) = 0
                                 AND (
                                    LOWER(COALESCE(tx_type, '')) IN (
                                        'payment_void', 'void_payment', 'payment_reversal',
                                        'reversal', 'refund', 'payment_refund'
                                    )
                                    OR LOWER(COALESCE(description, '')) LIKE '%void%'
                                    OR LOWER(COALESCE(description, '')) LIKE '%reversal%'
                                    OR LOWER(COALESCE(description, '')) LIKE '%refund%'
                                 )
                                 AND COALESCE(credit, 0) < 0
                            THEN COALESCE(credit, 0)

                            ELSE 0
                        END
                    ), 0)
                ) AS total_payments
            FROM folio_transactions
            WHERE folio_id = :folio_id
        ");
        $stmt->execute([':folio_id' => $folioId]);
        $totals = $stmt->fetch(PDO::FETCH_ASSOC);

        $charges  = (float)($totals['total_charges'] ?? 0);
        $payments = (float)($totals['total_payments'] ?? 0);
        $balance  = $charges - $payments;

        $stmt = $pdo->prepare("
            UPDATE folios
            SET total_charges = :total_charges,
                total_payments = :total_payments,
                balance = :balance,
                updated_at = NOW()
            WHERE id = :id
        ");
        $stmt->execute([
            ':total_charges'  => round($charges, 2),
            ':total_payments' => round($payments, 2),
            ':balance'        => round($balance, 2),
            ':id'             => $folioId,
        ]);
    }
}

if (!function_exists('sync_order_payment_status')) {
    function sync_order_payment_status(PDO $pdo, int $orderId): void
    {
        $stmt = $pdo->prepare("
            SELECT
                COALESCE(SUM(total_charges), 0) AS total_charges,
                COALESCE(SUM(total_payments), 0) AS total_payments,
                COALESCE(SUM(balance), 0) AS total_balance
            FROM folios
            WHERE order_id = :order_id
        ");
        $stmt->execute([':order_id' => $orderId]);
        $folio = $stmt->fetch(PDO::FETCH_ASSOC);

        $charges  = (float)($folio['total_charges'] ?? 0);
        $payments = (float)($folio['total_payments'] ?? 0);
        $balance  = (float)($folio['total_balance'] ?? 0);

        if ($charges <= 0.00001 || $payments <= 0.00001) {
            $status = 0;
        } elseif ($balance <= 0.00001) {
            $status = 2;
        } else {
            $status = 1;
        }

        $stmt = $pdo->prepare("
            UPDATE orders
            SET payment_status = :payment_status
            WHERE id = :id
        ");
        $stmt->execute([
            ':payment_status' => $status,
            ':id'             => $orderId,
        ]);
    }
}

if (!function_exists('folio_move_label')) {
    function folio_move_label(array $folio): string
    {
        $type = strtolower(trim((string)($folio['folio_type'] ?? '')));
        $name = trim((string)($folio['folio_name'] ?? ''));
        $room = trim((string)($folio['room_no'] ?? ''));
        $occupant = trim((string)($folio['occupant_name'] ?? ''));

        if ($type === 'master') {
            return 'Master Folio' . ($name !== '' ? ' - ' . $name : '');
        }
        if ($type === 'room') {
            return 'Room Folio' . ($room !== '' ? ' - Room ' . $room : '') . ($name !== '' ? ' - ' . $name : '');
        }
        if ($type === 'guest') {
            return 'Guest Folio' . ($occupant !== '' ? ' - ' . $occupant : '') . ($name !== '' ? ' - ' . $name : '');
        }
        return ucwords(str_replace('_', ' ', $type ?: 'Folio')) . ($name !== '' ? ' - ' . $name : '');
    }
}

if (!function_exists('get_booking_folios_for_move')) {
    function get_booking_folios_for_move(PDO $pdo, int $orderId): array
    {
        $stmt = $pdo->prepare("\n            SELECT\n                f.id,\n                f.order_id,\n                f.folio_no,\n                f.folio_name,\n                f.folio_type,\n                f.room_id,\n                f.rooming_id,\n                f.occupant_name,\n                f.balance,\n                r.room_no\n            FROM folios f\n            LEFT JOIN rooms r ON r.id = f.room_id\n            WHERE f.order_id = :order_id\n              AND COALESCE(f.status,1) = 1\n            ORDER BY\n                CASE f.folio_type\n                    WHEN 'master' THEN 1\n                    WHEN 'room' THEN 2\n                    WHEN 'guest' THEN 3\n                    ELSE 9\n                END,\n                r.room_no ASC, f.id ASC\n        ");
        $stmt->execute([':order_id' => $orderId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

if (!function_exists('write_payment_audit_log_safe')) {
    function write_payment_audit_log_safe(PDO $pdo, int $orderId, string $actionType, string $reason, array $payload): void
    {
        if (!pay_table_exists($pdo, 'payment_audit_log')) {
            return;
        }

        $cols = ['order_id', 'action_type', 'action_reason', 'payload_json'];
        $vals = [':order_id', ':action_type', ':action_reason', ':payload_json'];
        $params = [
            ':order_id' => $orderId,
            ':action_type' => $actionType,
            ':action_reason' => $reason,
            ':payload_json' => json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES),
        ];

        if (pay_column_exists($pdo, 'payment_audit_log', 'action_by')) {
            $cols[] = 'action_by';
            $vals[] = ':action_by';
            $params[':action_by'] = current_user_id_for_audit();
        }
        if (pay_column_exists($pdo, 'payment_audit_log', 'action_at')) {
            $cols[] = 'action_at';
            $vals[] = 'NOW()';
        }

        $sql = 'INSERT INTO payment_audit_log (' . implode(',', $cols) . ') VALUES (' . implode(',', $vals) . ')';
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
    }
}

if (!function_exists('sync_source_folio_reference_if_present')) {
    function sync_source_folio_reference_if_present(PDO $pdo, array $tx, int $newFolioId): void
    {
        $sourceTable = trim((string)($tx['source_table'] ?? ''));
        $sourceId = (int)($tx['source_id'] ?? 0);
        if ($sourceTable === '' || $sourceId <= 0) {
            return;
        }

        // Whitelist real application source tables only. Do not build SQL from arbitrary values.
        $allowed = ['payment', 'payments', 'extras', 'folio_extras', 'order_extras', 'tblbookingdetail', 'pos_orders', 'pos_order_items'];
        if (!in_array($sourceTable, $allowed, true)) {
            return;
        }
        if (!pay_table_exists($pdo, $sourceTable) || !pay_column_exists($pdo, $sourceTable, 'folio_id')) {
            return;
        }

        $stmt = $pdo->prepare("UPDATE `$sourceTable` SET folio_id = :folio_id WHERE id = :id");
        $stmt->execute([':folio_id' => $newFolioId, ':id' => $sourceId]);
    }
}

if (!function_exists('move_folio_transaction')) {
    function move_folio_transaction(PDO $pdo, int $orderId, int $transactionId, int $targetFolioId, string $reason): void
    {
        if ($transactionId <= 0) {
            throw new RuntimeException('Invalid transaction selected.');
        }
        if ($targetFolioId <= 0) {
            throw new RuntimeException('Please select the destination folio.');
        }
        if (trim($reason) === '') {
            throw new RuntimeException('Please enter a reason for moving this transaction.');
        }

        $stmt = $pdo->prepare("\n            SELECT\n                ft.*,\n                f.folio_name AS old_folio_name,\n                f.folio_type AS old_folio_type,\n                f.order_id AS old_order_id\n            FROM folio_transactions ft\n            INNER JOIN folios f ON f.id = ft.folio_id\n            WHERE ft.id = :id\n              AND ft.order_id = :order_id\n            LIMIT 1\n            FOR UPDATE\n        ");
        $stmt->execute([':id' => $transactionId, ':order_id' => $orderId]);
        $tx = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$tx) {
            throw new RuntimeException('Selected folio transaction was not found for this booking.');
        }

        $oldFolioId = (int)$tx['folio_id'];
        if ($oldFolioId === $targetFolioId) {
            throw new RuntimeException('The selected transaction is already on that folio.');
        }

        if (!empty($tx['is_voided'])) {
            throw new RuntimeException('Voided transactions cannot be moved.');
        }

        $txType = strtolower(trim((string)($tx['tx_type'] ?? '')));
        if ($txType === 'room_charge') {
            throw new RuntimeException('Room charges cannot be moved here. Rebuild or correct room allocations instead.');
        }

        $stmt = $pdo->prepare("\n            SELECT *\n            FROM folios\n            WHERE id = :folio_id\n              AND order_id = :order_id\n              AND COALESCE(status,1) = 1\n            LIMIT 1\n            FOR UPDATE\n        ");
        $stmt->execute([':folio_id' => $targetFolioId, ':order_id' => $orderId]);
        $targetFolio = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$targetFolio) {
            throw new RuntimeException('Destination folio does not belong to this booking or is inactive.');
        }

        $stmt = $pdo->prepare("\n            UPDATE folio_transactions\n            SET folio_id = :new_folio_id\n            WHERE id = :id\n              AND order_id = :order_id\n        ");
        $stmt->execute([
            ':new_folio_id' => $targetFolioId,
            ':id' => $transactionId,
            ':order_id' => $orderId,
        ]);

        sync_source_folio_reference_if_present($pdo, $tx, $targetFolioId);

        recalculate_folio_totals($pdo, $oldFolioId);
        recalculate_folio_totals($pdo, $targetFolioId);
        sync_order_payment_status($pdo, $orderId);

        write_payment_audit_log_safe($pdo, $orderId, 'move_folio_transaction', $reason, [
            'transaction_id' => $transactionId,
            'tx_type' => $tx['tx_type'] ?? '',
            'description' => $tx['description'] ?? '',
            'debit' => $tx['debit'] ?? 0,
            'credit' => $tx['credit'] ?? 0,
            'source_table' => $tx['source_table'] ?? '',
            'source_id' => $tx['source_id'] ?? '',
            'from_folio_id' => $oldFolioId,
            'from_folio_name' => $tx['old_folio_name'] ?? '',
            'to_folio_id' => $targetFolioId,
            'to_folio_name' => $targetFolio['folio_name'] ?? '',
        ]);
    }
}

if (!function_exists('calculate_saved_room_charge_total')) {
    function calculate_saved_room_charge_total(
        int $adults,
        int $children,
        float $sharingRate,
        float $singleRate,
        float $singleSupplement,
        float $childRatePercent
    ): array {
        $adults = max(0, $adults);
        $children = max(0, $children);
        $childRatePercent = max(0, $childRatePercent);

        if ($adults <= 0) {
            return [
                'adult_total'  => 0.00,
                'child_total'  => 0.00,
                'room_total'   => 0.00,
                'rate_type'    => 'Empty',
            ];
        }

        if ($adults === 1) {
            $adultTotal = $singleRate > 0
                ? $singleRate
                : ($sharingRate + $singleSupplement);

            $rateType = $singleRate > 0 ? 'Single' : 'Sharing + Supplement';
        } else {
            $adultTotal = $adults * $sharingRate;
            $rateType = 'Sharing';
        }

        $childRate  = $sharingRate * ($childRatePercent / 100);
        $childTotal = $children * $childRate;
        $roomTotal  = $adultTotal + $childTotal;

        return [
            'adult_total'  => round($adultTotal, 2),
            'child_total'  => round($childTotal, 2),
            'room_total'   => round($roomTotal, 2),
            'rate_type'    => $rateType,
        ];
    }
}

if (!function_exists('ensure_room_charges_posted_to_folio')) {
    function ensure_room_charges_posted_to_folio(PDO $pdo, int $orderId, int $folioId): void
    {
        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM folio_transactions
            WHERE folio_id = :folio_id
              AND tx_type = 'room_charge'
        ");
        $stmt->execute([':folio_id' => $folioId]);
        $exists = (int)$stmt->fetchColumn();

        if ($exists > 0) {
            return;
        }

        $stmt = $pdo->prepare("
            SELECT
                property_id,
                date,
                sharing_rate,
                single_rate,
                single_supplement,
                child_rate_percent,
                rate_plan_id,
                rate_plan_title,
                rate_plan_currency,
                rate_plan_season_label,
                rate_plan_season_color
            FROM rel_orders_prices
            WHERE order_id = :order_id
            ORDER BY date ASC
        ");
        $stmt->execute([':order_id' => $orderId]);
        $nightRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (!$nightRows) {
            return;
        }

        $stmt = $pdo->prepare("
            SELECT
                property_id,
                room_no,
                room_type_id,
                adults,
                children
            FROM order_rooming
            WHERE order_id = :order_id
            ORDER BY room_no ASC
        ");
        $stmt->execute([':order_id' => $orderId]);
        $roomingRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (!$roomingRows) {
            return;
        }

        $roomTypeMap = build_room_mix_map($pdo, $orderId)['map'];

        $propertyId = 0;
        if (!empty($nightRows[0]['property_id'])) {
            $propertyId = (int)$nightRows[0]['property_id'];
        } elseif (!empty($roomingRows[0]['property_id'])) {
            $propertyId = (int)$roomingRows[0]['property_id'];
        }

        $insert = $pdo->prepare("
            INSERT INTO folio_transactions
            (
                property_id,
                folio_id,
                order_id,
                tx_date,
                tx_type,
                description,
                debit,
                credit,
                source_table,
                source_id
            )
            VALUES
            (
                :property_id,
                :folio_id,
                :order_id,
                :tx_date,
                :tx_type,
                :description,
                :debit,
                :credit,
                :source_table,
                :source_id
            )
        ");

        $seq = 1;

        foreach ($nightRows as $night) {
            $stayDate = (string)$night['date'];
            $sharingRate = (float)$night['sharing_rate'];
            $singleRate = (float)$night['single_rate'];
            $singleSupplement = (float)$night['single_supplement'];
            $childRatePercent = (float)$night['child_rate_percent'];

            $ratePlanTitle = trim((string)($night['rate_plan_title'] ?? ''));
            $ratePlanCurrency = trim((string)($night['rate_plan_currency'] ?? ''));
            $ratePlanSeason = trim((string)($night['rate_plan_season_label'] ?? ''));
            $ratePlanId = (int)($night['rate_plan_id'] ?? 0);

            foreach ($roomingRows as $slot) {
                $roomNo = (int)$slot['room_no'];
                $adults = (int)$slot['adults'];
                $children = (int)$slot['children'];
                $roomTypeTitle = trim((string)($roomTypeMap[(int)($slot['room_type_id'] ?? 0)] ?? ''));

                $calc = calculate_saved_room_charge_total(
                    $adults,
                    $children,
                    $sharingRate,
                    $singleRate,
                    $singleSupplement,
                    $childRatePercent
                );

                $roomCharge = (float)$calc['room_total'];

                if ($roomCharge <= 0) {
                    continue;
                }

                $descParts = [];
                $slotLabel = 'Room Slot ' . $roomNo;
                if ($roomTypeTitle !== '') {
                    $slotLabel .= ' (' . $roomTypeTitle . ')';
                }
                $descParts[] = $slotLabel . ' charge for ' . $stayDate;

                if ($ratePlanTitle !== '') {
                    $planText = $ratePlanTitle;
                    if ($ratePlanCurrency !== '') {
                        $planText .= ' (' . $ratePlanCurrency . ')';
                    }
                    if ($ratePlanSeason !== '') {
                        $planText .= ' - ' . $ratePlanSeason;
                    }
                    if ($ratePlanId > 0) {
                        $planText .= ' [Plan #' . $ratePlanId . ']';
                    }
                    $descParts[] = $planText;
                }

                $descParts[] = $calc['rate_type'];

                if ($adults > 0 || $children > 0) {
                    $descParts[] = $adults . 'A' . ($children > 0 ? '/' . $children . 'C' : '');
                }

                $desc = implode(' | ', $descParts);

                $insert->execute([
                    ':property_id'  => $propertyId,
                    ':folio_id'     => $folioId,
                    ':order_id'     => $orderId,
                    ':tx_date'      => $stayDate . ' 00:00:00',
                    ':tx_type'      => 'room_charge',
                    ':description'  => $desc,
                    ':debit'        => $roomCharge,
                    ':credit'       => 0,
                    ':source_table' => 'rel_orders_prices',
                    ':source_id'    => $seq,
                ]);

                $seq++;
            }
        }
    }
}

if (!function_exists('rebuild_folio_room_charges')) {
    function rebuild_folio_room_charges(PDO $pdo, int $orderId, int $folioId): void
    {
        $stmt = $pdo->prepare("
            DELETE FROM folio_transactions
            WHERE folio_id = :folio_id
              AND tx_type = 'room_charge'
        ");
        $stmt->execute([':folio_id' => $folioId]);

        ensure_room_charges_posted_to_folio($pdo, $orderId, $folioId);
        recalculate_folio_totals($pdo, $folioId);
        sync_order_payment_status($pdo, $orderId);
    }
}

if (!function_exists('is_card_surcharge_method')) {
    function is_card_surcharge_method(string $method): bool
    {
        $normalized = strtolower(trim($method));
        return in_array($normalized, ['pos', 'card'], true);
    }
}

if (!function_exists('is_bank_payment_method')) {
    function is_bank_payment_method(string $method): bool
    {
        $normalized = strtolower(trim($method));
        return strpos($normalized, 'bank') !== false || strpos($normalized, 'transfer') !== false;
    }
}

if (!function_exists('post_cc_surcharge_to_folio')) {
    function post_cc_surcharge_to_folio(PDO $pdo, int $orderId, int $propertyId, int $folioId, string $paymentDate, float $surchargeUsd, float $surchargeEntered, float $roeValue, string $paymentMethod): void
    {
        if ($surchargeUsd <= 0) {
            return;
        }

        $serviceId = CC_SURCHARGE_SERVICE_ID;
        $serviceName = 'CC Charge';
        $categoryLabel = 'Extra';

        try {
            $stmtService = $pdo->prepare("\n                SELECT\n                    s.service,\n                    s.price_per_person,\n                    c.category AS category_name\n                FROM tblservices s\n                LEFT JOIN tblservicecategory c ON c.id = s.category\n                WHERE s.id = :id\n                LIMIT 1\n            ");
            $stmtService->execute([':id' => $serviceId]);
            $svc = $stmtService->fetch(PDO::FETCH_ASSOC);
            if ($svc) {
                $serviceName = trim((string)($svc['service'] ?? '')) ?: $serviceName;
                $categoryLabel = trim((string)($svc['category_name'] ?? '')) ?: $categoryLabel;
            }
        } catch (Throwable $e) {
            // Do not block payment if the service label cannot be loaded; source_id still references service 11.
        }

        $txType = strtolower(preg_replace('/[^a-zA-Z0-9]+/', '_', $categoryLabel));
        $txType = trim($txType, '_');
        if ($txType === '') {
            $txType = 'cc_charge';
        }

        $description = $serviceName
            . ' - 3% surcharge for ' . $paymentMethod
            . ' | Charged ' . number_format($surchargeEntered, 2, '.', '')
            . ' / ROE ' . number_format($roeValue, 5, '.', '')
            . ' = USD ' . number_format($surchargeUsd, 2, '.', '');

        $stmt = $pdo->prepare("\n            INSERT INTO folio_transactions\n            (\n                property_id,\n                folio_id,\n                order_id,\n                tx_date,\n                tx_type,\n                description,\n                debit,\n                credit,\n                source_table,\n                source_id\n            )\n            VALUES\n            (\n                :property_id,\n                :folio_id,\n                :order_id,\n                :tx_date,\n                :tx_type,\n                :description,\n                :debit,\n                0,\n                'tblservices',\n                :source_id\n            )\n        ");
        $stmt->execute([
            ':property_id' => $propertyId,
            ':folio_id'    => $folioId,
            ':order_id'    => $orderId,
            ':tx_date'     => $paymentDate . ' 00:00:00',
            ':tx_type'     => $txType,
            ':description' => $description,
            ':debit'       => round($surchargeUsd, 2),
            ':source_id'   => $serviceId,
        ]);
    }
}

if (!function_exists('post_optional_gratuity_to_folio')) {
    function post_optional_gratuity_to_folio(PDO $pdo, int $orderId, int $propertyId, int $folioId, string $paymentDate, float $gratuityUsd, float $gratuityEntered, float $roeValue): void
    {
        if ($gratuityUsd <= 0) {
            return;
        }

        $itemName = 'Optional Gratuity';
        try {
            if (pay_table_exists($pdo, 'inventory_items')) {
                $nameColumns = ['item_name', 'name', 'description', 'item_description', 'product_name'];
                $nameColumn = '';
                foreach ($nameColumns as $col) {
                    if (pay_column_exists($pdo, 'inventory_items', $col)) {
                        $nameColumn = $col;
                        break;
                    }
                }

                if ($nameColumn !== '') {
                    $stmtItem = $pdo->prepare("SELECT `{$nameColumn}` FROM inventory_items WHERE id = :id LIMIT 1");
                    $stmtItem->execute([':id' => OPTIONAL_GRATUITY_INVENTORY_ITEM_ID]);
                    $loadedName = trim((string)$stmtItem->fetchColumn());
                    if ($loadedName !== '') {
                        $itemName = $loadedName;
                    }
                }
            }
        } catch (Throwable $e) {
            $itemName = 'Optional Gratuity';
        }

        $description = $itemName
            . ' | Optional guest gratuity'
            . ' | Entered ' . number_format($gratuityEntered, 2, '.', '')
            . ' / ROE ' . number_format($roeValue, 5, '.', '')
            . ' = USD ' . number_format($gratuityUsd, 2, '.', '')
            . ' | No POS/Card surcharge applied';

        $stmt = $pdo->prepare("\n            INSERT INTO folio_transactions\n            (\n                property_id,\n                folio_id,\n                order_id,\n                tx_date,\n                tx_type,\n                description,\n                debit,\n                credit,\n                source_table,\n                source_id\n            )\n            VALUES\n            (\n                :property_id,\n                :folio_id,\n                :order_id,\n                :tx_date,\n                'gratuity',\n                :description,\n                :debit,\n                0,\n                'inventory_items',\n                :source_id\n            )\n        ");
        $stmt->execute([
            ':property_id' => $propertyId,
            ':folio_id'    => $folioId,
            ':order_id'    => $orderId,
            ':tx_date'     => $paymentDate . ' 00:00:00',
            ':description' => $description,
            ':debit'       => round($gratuityUsd, 2),
            ':source_id'   => OPTIONAL_GRATUITY_INVENTORY_ITEM_ID,
        ]);
    }
}

if (!function_exists('post_payment_to_folio')) {
    function post_payment_to_folio(PDO $pdo, int $paymentId, int $folioId): void
    {
        $stmt = $pdo->prepare("
            SELECT
                p.*,
                b.Bank AS linked_bank_name,
                b.AccountName AS linked_bank_account_name,
                b.AccountNo AS linked_bank_account_no,
                pm.machine_name AS linked_pos_machine_name,
                pm.terminal_id AS linked_pos_terminal_id
            FROM payment p
            LEFT JOIN tblbankdetails b ON b.ID = p.bank_account_id
            LEFT JOIN pos_machines pm ON pm.id = p.pos_machine_id
            WHERE p.id = :id
            LIMIT 1
        ");
        $stmt->execute([':id' => $paymentId]);
        $payment = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$payment) {
            throw new RuntimeException('Payment not found.');
        }

        $check = $pdo->prepare("
            SELECT COUNT(*)
            FROM folio_transactions
            WHERE source_table = 'payment'
              AND source_id = :source_id
              AND folio_id = :folio_id
        ");
        $check->execute([
            ':source_id' => $paymentId,
            ':folio_id'  => $folioId,
        ]);

        if ((int)$check->fetchColumn() > 0) {
            return;
        }

        $method = trim((string)($payment['payment_method'] ?? 'Payment'));
        $methodExtra = '';
        if (!empty($payment['linked_bank_name'])) {
            $methodExtra .= ' | Bank: ' . trim((string)$payment['linked_bank_name']);
            if (!empty($payment['linked_bank_account_name'])) {
                $methodExtra .= ' - ' . trim((string)$payment['linked_bank_account_name']);
            }
            if (!empty($payment['linked_bank_account_no'])) {
                $methodExtra .= ' (' . trim((string)$payment['linked_bank_account_no']) . ')';
            }
        }
        if (!empty($payment['linked_pos_machine_name'])) {
            $methodExtra .= ' | POS Machine: ' . trim((string)$payment['linked_pos_machine_name']);
            if (!empty($payment['linked_pos_terminal_id'])) {
                $methodExtra .= ' / Terminal ' . trim((string)$payment['linked_pos_terminal_id']);
            }
        }
        $roeText = '';
        if (!empty($payment['roe_atm']) && (float)$payment['roe_atm'] > 0) {
            $roeText = ' | USD base = Amount / ROE ' . number_format((float)$payment['roe_atm'], 5, '.', '');
        }

        $txDate = !empty($payment['date_time'])
            ? (string)$payment['date_time']
            : (!empty($payment['added_date']) ? $payment['added_date'] . ' 00:00:00' : date('Y-m-d H:i:s'));

        $stmt = $pdo->prepare("
            INSERT INTO folio_transactions
            (
                property_id,
                folio_id,
                order_id,
                tx_date,
                tx_type,
                description,
                debit,
                credit,
                source_table,
                source_id
            )
            VALUES
            (
                :property_id,
                :folio_id,
                :order_id,
                :tx_date,
                :tx_type,
                :description,
                :debit,
                :credit,
                :source_table,
                :source_id
            )
        ");
        $stmt->execute([
            ':property_id'  => (int)($payment['property_id'] ?? 0),
            ':folio_id'     => $folioId,
            ':order_id'     => (int)$payment['order_id'],
            ':tx_date'      => $txDate,
            ':tx_type'      => 'payment',
            ':description'  => 'Payment received - ' . $method . $methodExtra . $roeText,
            ':debit'        => 0,
            ':credit'       => round((float)$payment['amount'], 2),
            ':source_table' => 'payment',
            ':source_id'    => $paymentId,
        ]);
    }
}

/* =====================================================================
   PAYMENT HISTORY + REVERSAL
   ---------------------------------------------------------------------
   These are new: payments.php could take a payment but had no way to
   list or reverse one from a booking's own context. recalculate_folio_totals()
   already understood both void styles (is_voided flag, or a separate
   reversal row), so reversal below posts in the style the totals maths
   already expects rather than inventing a third.
   ===================================================================== */

if (!function_exists('get_booking_payments')) {
    /**
     * Every payment on a booking, with its folio and void state.
     *
     * Defensive about is_voided: the column may not exist on older installs,
     * in which case nothing is ever reported as voided rather than the query
     * failing outright.
     */
    function get_booking_payments(PDO $pdo, int $orderId): array
    {
        if ($orderId <= 0 || !pay_table_exists($pdo, 'payment')) {
            return [];
        }

        $hasVoided = pay_column_exists($pdo, 'payment', 'is_voided');
        $voidedSel = $hasVoided ? 'COALESCE(p.is_voided, 0) AS is_voided' : '0 AS is_voided';

        $hasBank = pay_table_exists($pdo, 'tblbankdetails');
        $hasPos  = pay_table_exists($pdo, 'pos_machines');

        $bankSel = $hasBank ? 'b.Bank AS bank_name' : 'NULL AS bank_name';
        $posSel  = $hasPos  ? 'pm.machine_name AS pos_machine_name' : 'NULL AS pos_machine_name';
        $bankJoin = $hasBank ? 'LEFT JOIN tblbankdetails b ON b.ID = p.bank_account_id' : '';
        $posJoin  = $hasPos  ? 'LEFT JOIN pos_machines pm ON pm.id = p.pos_machine_id' : '';

        try {
            $sql = "
                SELECT
                    p.id,
                    p.order_id,
                    p.date_time,
                    p.amount,
                    p.payment_method,
                    p.id_upload,
                    p.currency,
                    p.roe_atm,
                    {$voidedSel},
                    {$bankSel},
                    {$posSel},
                    ft.id AS folio_tx_id,
                    ft.folio_id,
                    f.folio_no,
                    f.folio_name
                FROM payment p
                {$bankJoin}
                {$posJoin}
                LEFT JOIN folio_transactions ft
                    ON ft.source_table = 'payment' AND ft.source_id = p.id
                LEFT JOIN folios f ON f.id = ft.folio_id
                WHERE p.order_id = :order_id
                ORDER BY p.date_time DESC, p.id DESC
            ";
            $stmt = $pdo->prepare($sql);
            $stmt->execute([':order_id' => $orderId]);
            return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (Throwable $e) {
            return [];
        }
    }
}

if (!function_exists('payment_is_reversed')) {
    /**
     * Has this payment already been reversed?
     *
     * Checks BOTH void styles, because recalculate_folio_totals() honours both
     * and a payment reversed in either style must not be reversible twice.
     */
    function payment_is_reversed(PDO $pdo, int $paymentId): bool
    {
        if ($paymentId <= 0) return false;

        // Style 1: the payment row itself flagged.
        if (pay_column_exists($pdo, 'payment', 'is_voided')) {
            try {
                $stmt = $pdo->prepare("SELECT COALESCE(is_voided, 0) FROM payment WHERE id = :id LIMIT 1");
                $stmt->execute([':id' => $paymentId]);
                if ((int)$stmt->fetchColumn() === 1) return true;
            } catch (Throwable $e) { /* fall through */ }
        }

        // Style 2: a separate reversal transaction posted against it.
        try {
            $stmt = $pdo->prepare("
                SELECT COUNT(*)
                FROM folio_transactions
                WHERE source_table = 'payment_reversal'
                  AND source_id = :id
            ");
            $stmt->execute([':id' => $paymentId]);
            return (int)$stmt->fetchColumn() > 0;
        } catch (Throwable $e) {
            return false;
        }
    }
}

if (!function_exists('reverse_payment')) {
    /**
     * Reverse a payment.
     *
     * Posts a REVERSAL ROW rather than deleting anything. A folio is a legal
     * record: the original payment, and the fact it was reversed, both have to
     * remain visible. recalculate_folio_totals() already nets reversal rows off
     * the payment credits, so the folio balance corrects itself.
     *
     * Authorisation is the CALLER'S job - see payment_reversal_authorised().
     * This function does not check permissions, so that the same posting logic
     * can serve the front desk, a manager override, and any future automated
     * correction without three copies of the accounting.
     *
     * CALLERS MUST call ensure_payment_audit_log_table() BEFORE opening their
     * transaction. It issues DDL, and DDL implicitly commits in MySQL - doing
     * it inside the transaction ends it early and the commit that follows
     * throws "There is no active transaction" on a reversal that in fact
     * succeeded.
     *
     * @throws RuntimeException when the payment is missing, already reversed,
     *         or has no folio transaction to reverse against.
     */
    function reverse_payment(PDO $pdo, int $orderId, int $paymentId, string $reason, ?int $userId = null): array
    {
        $reason = trim($reason);

        /*
           Who did this. Resolve from the session rather than trusting the
           caller's argument: there is no PIN step on a reversal, so the session
           user IS the accountability record. If a caller passes a user id we
           honour it (an automated correction may have no session), otherwise we
           take it from the session.
        */
        if ($userId === null) {
            $userId = current_user_id_for_audit();
        }
        $userName = trim((string)($_SESSION['ss_username'] ?? ''));
        if ($paymentId <= 0) {
            throw new RuntimeException('Invalid payment selected.');
        }
        if ($reason === '') {
            throw new RuntimeException('A reason is required to reverse a payment.');
        }
        if (payment_is_reversed($pdo, $paymentId)) {
            throw new RuntimeException('This payment has already been reversed.');
        }

        $stmt = $pdo->prepare("SELECT * FROM payment WHERE id = :id AND order_id = :order_id LIMIT 1");
        $stmt->execute([':id' => $paymentId, ':order_id' => $orderId]);
        $payment = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$payment) {
            throw new RuntimeException('Payment not found on this booking.');
        }

        // The folio the original payment landed on. Reverse it there, not on
        // whatever folio happens to be selected now.
        $txStmt = $pdo->prepare("
            SELECT id, folio_id, property_id, credit, debit
            FROM folio_transactions
            WHERE source_table = 'payment' AND source_id = :id
            ORDER BY id ASC
            LIMIT 1
        ");
        $txStmt->execute([':id' => $paymentId]);
        $tx = $txStmt->fetch(PDO::FETCH_ASSOC);
        if (!$tx) {
            throw new RuntimeException('This payment has no folio transaction to reverse.');
        }

        $folioId = (int)$tx['folio_id'];
        $amount = round((float)($tx['credit'] ?? 0), 2);
        if ($amount <= 0) {
            $amount = round((float)($payment['amount'] ?? 0), 2);
        }
        if ($amount <= 0) {
            throw new RuntimeException('Payment amount could not be determined.');
        }

        $method = trim((string)($payment['payment_method'] ?? 'Payment'));

        /*
           The user goes in the DESCRIPTION, not just the audit table. A folio
           line is what a manager actually reads when a balance is queried;
           making them cross-reference payment_audit_log to find out who
           reversed a payment means in practice nobody checks.
        */
        $desc = 'REVERSAL of payment #' . $paymentId . ' (' . $method . ') - ' . $reason;
        if ($userName !== '') {
            $desc .= ' [by ' . $userName . ']';
        } elseif ($userId !== null && $userId > 0) {
            $desc .= ' [by user #' . $userId . ']';
        }

        $cols = ['folio_id', 'order_id', 'tx_date', 'tx_type', 'description', 'debit', 'credit', 'source_table', 'source_id'];
        $vals = [':folio_id', ':order_id', ':tx_date', "'payment_reversal'", ':description', ':debit', '0', "'payment_reversal'", ':source_id'];
        $params = [
            ':folio_id'    => $folioId,
            ':order_id'    => $orderId,
            ':tx_date'     => date('Y-m-d'),
            ':description' => $desc,
            // A reversal DEBITS the folio, cancelling the original credit.
            ':debit'       => $amount,
            ':source_id'   => $paymentId,
        ];
        if (pay_column_exists($pdo, 'folio_transactions', 'property_id')) {
            $cols[] = 'property_id';
            $vals[] = ':property_id';
            $params[':property_id'] = (int)($tx['property_id'] ?? 0);
        }
        if (pay_column_exists($pdo, 'folio_transactions', 'created_by') && $userId !== null) {
            $cols[] = 'created_by';
            $vals[] = ':created_by';
            $params[':created_by'] = $userId;
        }

        $pdo->prepare(
            "INSERT INTO folio_transactions (" . implode(', ', $cols) . ") VALUES (" . implode(', ', $vals) . ")"
        )->execute($params);

        $reversalTxId = (int)$pdo->lastInsertId();

        /*
           Also flag the ORIGINAL rows where the schema allows it. Belt and
           braces: recalculate_folio_totals() nets the reversal row off on its
           own, but a flagged original means any report that only understands
           the is_voided style still gets the right answer.
        */
        if (pay_column_exists($pdo, 'payment', 'is_voided')) {
            try {
                $pdo->prepare("UPDATE payment SET is_voided = 1 WHERE id = :id")
                    ->execute([':id' => $paymentId]);
            } catch (Throwable $e) { /* non-fatal */ }
        }
        if (pay_column_exists($pdo, 'folio_transactions', 'is_voided')) {
            try {
                $pdo->prepare("UPDATE folio_transactions SET is_voided = 1 WHERE id = :id")
                    ->execute([':id' => (int)$tx['id']]);
            } catch (Throwable $e) { /* non-fatal */ }
        }

        recalculate_folio_totals($pdo, $folioId);
        sync_order_payment_status($pdo, $orderId);

        /*
           NOTE: ensure_payment_audit_log_table() is deliberately NOT called
           here. It runs CREATE TABLE, and DDL causes an IMPLICIT COMMIT in
           MySQL - which silently ended the caller's transaction and made a
           successful reversal report "There is no active transaction" while
           having actually gone through. Callers create the table BEFORE
           opening their transaction instead.
        */
        write_payment_audit_log_safe($pdo, $orderId, 'payment_reversal', $reason, [
            'payment_id'        => $paymentId,
            'folio_id'          => $folioId,
            'amount_usd'        => $amount,
            'method'            => $method,
            'reversal_tx'       => $reversalTxId,
            'reversed_by_id'    => $userId,
            'reversed_by_name'  => $userName !== '' ? $userName : null,
            'reversed_by_role'  => strtolower(trim((string)($_SESSION['ss_role_id'] ?? ''))) ?: null,
            'reversed_at'       => date('Y-m-d H:i:s'),
        ]);

        return [
            'payment_id'  => $paymentId,
            'folio_id'    => $folioId,
            'amount'      => $amount,
            'reversal_tx' => $reversalTxId,
        ];
    }
}

if (!function_exists('ensure_payment_audit_log_table')) {
    /**
     * Create payment_audit_log if it is missing.
     *
     * write_payment_audit_log_safe() returns silently when the table is absent,
     * which is right for a nice-to-have log but wrong for a reversal: the whole
     * point of removing the PIN step was that the USER RECORD is the
     * accountability, so losing it is not acceptable. Called before any reversal
     * is written.
     *
     * Non-fatal: if the table cannot be created (permissions, say) the reversal
     * still proceeds and the folio description still names the user.
     */
    function ensure_payment_audit_log_table(PDO $pdo): bool
    {
        static $done = null;
        if ($done !== null) return $done;

        if (pay_table_exists($pdo, 'payment_audit_log', true)) {
            $done = true;
            return $done;
        }
        try {
            $pdo->exec("
                CREATE TABLE IF NOT EXISTS `payment_audit_log` (
                    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
                    `order_id`      INT UNSIGNED NOT NULL,
                    `action_type`   VARCHAR(60) NOT NULL,
                    `action_reason` TEXT NULL,
                    `payload_json`  TEXT NULL,
                    `action_by`     INT UNSIGNED NULL,
                    `action_at`     DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (`id`),
                    KEY `idx_pal_order` (`order_id`),
                    KEY `idx_pal_action` (`action_type`, `action_at`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            ");
            // Refresh: the cache holds a stale 'false' from before the CREATE.
            $done = pay_table_exists($pdo, 'payment_audit_log', true);
        } catch (Throwable $e) {
            $done = false;
        }
        return $done;
    }
}

if (!function_exists('payment_reversal_roles')) {
    /**
     * Roles permitted to reverse a payment.
     *
     * Mirrors the $canVoidPayments gate that already existed at the top of
     * payments.php, kept here so every caller enforces the same list instead
     * of each page inventing its own.
     */
    function payment_reversal_roles(): array
    {
        return ['master', 'manager', 'accounts', '2'];
    }
}

if (!function_exists('payment_reversal_authorised')) {
    /**
     * May the current user reverse a payment?
     *
     * Role only. This mirrors the $canVoidPayments gate that already existed at
     * the top of payments.php, kept here so every caller enforces the same rule
     * rather than each page inventing its own.
     *
     * There is deliberately no PIN step: a PIN is only meaningful if it proves
     * WHO authorised the reversal, and a shared manager PIN does not - it proves
     * only that someone in the room knew it. The audit row written by
     * reverse_payment() records the actual session user, which is the thing you
     * need when a folio is queried weeks later.
     *
     * @return array{ok: bool, error: string}
     */
    function payment_reversal_authorised(PDO $pdo): array
    {
        $role = strtolower(trim((string)($_SESSION['ss_role_id'] ?? '')));
        if (!in_array($role, payment_reversal_roles(), true)) {
            return ['ok' => false, 'error' => 'Your role is not permitted to reverse payments.'];
        }
        return ['ok' => true, 'error' => ''];
    }
}
