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

$pageTitle = 'Folios';

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('folio_type_badge')) {
    function folio_type_badge(string $type): string
    {
        $type = strtolower(trim($type));

        switch ($type) {
            case 'master':
                return '<span class="badge text-bg-dark">Master</span>';
            case 'room':
                return '<span class="badge text-bg-primary">Room</span>';
            case 'guest':
                return '<span class="badge text-bg-info text-dark">Guest</span>';
            default:
                return '<span class="badge text-bg-secondary">' . h(ucfirst($type !== '' ? $type : 'Unknown')) . '</span>';
        }
    }
}


if (!function_exists('folio_table_exists')) {
    function folio_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 = :table_name
            ");
            $stmt->execute([':table_name' => $table]);
            $cache[$table] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) {
            $cache[$table] = false;
        }
        return $cache[$table];
    }
}

if (!function_exists('folio_money_badge')) {
    function folio_money_badge(float $amount, string $label, string $class = 'secondary'): string
    {
        if (abs($amount) <= 0.00001) return '';
        return '<span class="breakdown-chip text-bg-' . h($class) . '">' . h($label) . ' ' . money_fmt($amount) . '</span>';
    }
}

$search = trim((string)($_GET['q'] ?? ''));
$status = trim((string)($_GET['status'] ?? ''));
$folioType = trim((string)($_GET['folio_type'] ?? ''));

$errors = [];
$folios = [];
$totalFolios = 0;
$totalCharges = 0.0;
$totalPayments = 0.0;
$totalBalance = 0.0;

$hasExtraChargeItems = folio_table_exists($pdo, 'extra_charge_items');

$extraChargeItemsJoinSql = $hasExtraChargeItems
    ? " LEFT JOIN extra_charge_items eci ON eci.folio_transaction_id = ft.id "
    : "";

$extraClassificationExpr = $hasExtraChargeItems
    ? "LOWER(CONCAT_WS(' ', COALESCE(ft.tx_type,''), COALESCE(ft.source_table,''), COALESCE(ft.description,''), COALESCE(eci.category_name,''), COALESCE(eci.item_name,'')))"
    : "LOWER(CONCAT_WS(' ', COALESCE(ft.tx_type,''), COALESCE(ft.source_table,''), COALESCE(ft.description,'')))";

try {
    $sql = "
        SELECT
            f.*,
            r.room_no,
            o.order_no,
            o.check_in,
            o.check_out,
            o.totalrooms,
            o.adults,
            o.kids,
            o.payment_status,
            g.firstname,
            g.lastname,
            COALESCE(a.firstname,'') AS agent_name,
            rmix.room_mix,
            COALESCE(ftcalc.live_total_charges, 0) AS live_total_charges,
            COALESCE(ftcalc.live_total_payments, 0) AS live_total_payments,
            COALESCE(ftcalc.live_total_balance, 0) AS live_total_balance,
            COALESCE(ftcalc.accommodation_total, 0) AS accommodation_total,
            COALESCE(ftcalc.restaurant_total, 0) AS restaurant_total,
            COALESCE(ftcalc.bar_total, 0) AS bar_total,
            COALESCE(ftcalc.spa_total, 0) AS spa_total,
            COALESCE(ftcalc.curio_total, 0) AS curio_total,
            COALESCE(ftcalc.other_extra_total, 0) AS other_extra_total
        FROM folios f
        INNER JOIN orders o ON o.id = f.order_id
        LEFT JOIN rooms r ON r.id = f.room_id
        LEFT JOIN guests g ON g.id = o.guest_id
        LEFT JOIN agents a ON a.id = o.agent_id
        LEFT JOIN (
            SELECT order_id,
                   GROUP_CONCAT(room_summary ORDER BY sort_title SEPARATOR ', ') AS room_mix
            FROM (
                SELECT
                    rmg.order_id,
                    rt.title AS sort_title,
                    CONCAT(
                        rt.title,
                        ' x ',
                        COUNT(*),
                        CASE
                            WHEN SUM(
                                CASE
                                    WHEN (COALESCE(rmg.adults,0) + COALESCE(rmg.children,0)) = 3
                                         AND LOWER(COALESCE(rt.title,'')) NOT LIKE '%family%'
                                    THEN 1 ELSE 0
                                END
                            ) > 0
                            THEN CONCAT(
                                ' (',
                                SUM(
                                    CASE
                                        WHEN (COALESCE(rmg.adults,0) + COALESCE(rmg.children,0)) = 3
                                             AND LOWER(COALESCE(rt.title,'')) NOT LIKE '%family%'
                                        THEN 1 ELSE 0
                                    END
                                ),
                                ' Triple / Extra Bed)'
                            )
                            ELSE ''
                        END
                    ) AS room_summary
                FROM order_rooming rmg
                INNER JOIN room_types rt ON rt.id = rmg.room_type_id
                GROUP BY rmg.order_id, rmg.room_type_id, rt.title
            ) z
            GROUP BY order_id
        ) rmix ON rmix.order_id = o.id
        LEFT JOIN (
            SELECT
                x.folio_id,
                COALESCE(SUM(CASE WHEN x.is_charge = 1 THEN x.amount ELSE 0 END), 0) AS live_total_charges,
                GREATEST(0, COALESCE(SUM(CASE WHEN x.is_payment = 1 THEN x.amount ELSE 0 END), 0)) AS live_total_payments,
                COALESCE(SUM(CASE WHEN x.is_charge = 1 THEN x.amount ELSE 0 END), 0)
                - GREATEST(0, COALESCE(SUM(CASE WHEN x.is_payment = 1 THEN x.amount ELSE 0 END), 0)) AS live_total_balance,
                COALESCE(SUM(CASE WHEN x.is_charge = 1 AND x.category_key = 'accommodation' THEN x.amount ELSE 0 END), 0) AS accommodation_total,
                COALESCE(SUM(CASE WHEN x.is_charge = 1 AND x.category_key = 'restaurant' THEN x.amount ELSE 0 END), 0) AS restaurant_total,
                COALESCE(SUM(CASE WHEN x.is_charge = 1 AND x.category_key = 'bar' THEN x.amount ELSE 0 END), 0) AS bar_total,
                COALESCE(SUM(CASE WHEN x.is_charge = 1 AND x.category_key = 'spa' THEN x.amount ELSE 0 END), 0) AS spa_total,
                COALESCE(SUM(CASE WHEN x.is_charge = 1 AND x.category_key = 'curio' THEN x.amount ELSE 0 END), 0) AS curio_total,
                COALESCE(SUM(CASE WHEN x.is_charge = 1 AND x.category_key = 'extras' THEN x.amount ELSE 0 END), 0) AS other_extra_total
            FROM (
                SELECT
                    ft.id,
                    ft.folio_id,
                    CASE
                        WHEN COALESCE(ft.is_voided,0) = 0
                             AND COALESCE(ft.debit,0) > 0
                             AND LOWER(COALESCE(ft.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(ft.description,'')) NOT LIKE '%void%'
                             AND LOWER(COALESCE(ft.description,'')) NOT LIKE '%reversal%'
                             AND LOWER(COALESCE(ft.description,'')) NOT LIKE '%refund%'
                        THEN 1 ELSE 0
                    END AS is_charge,
                    CASE
                        WHEN COALESCE(ft.is_voided,0) = 0
                             AND (
                                LOWER(COALESCE(ft.tx_type,'')) IN ('payment','receipt','deposit','payment_received','folio_payment','guest_payment')
                                OR LOWER(COALESCE(ft.source_table,'')) IN ('payments','payment','receipts','receipt')
                             )
                             AND COALESCE(ft.credit,0) > 0
                        THEN 1 ELSE 0
                    END AS is_payment,
                    CASE
                        WHEN COALESCE(ft.is_voided,0) = 0
                             AND (
                                LOWER(COALESCE(ft.tx_type,'')) IN ('payment','receipt','deposit','payment_received','folio_payment','guest_payment')
                                OR LOWER(COALESCE(ft.source_table,'')) IN ('payments','payment','receipts','receipt')
                             )
                             AND COALESCE(ft.credit,0) > 0
                        THEN COALESCE(ft.credit,0)
                        WHEN COALESCE(ft.is_voided,0) = 0
                             AND LOWER(COALESCE(ft.tx_type,'')) IN ('payment_void','void_payment','payment_reversal','reversal','refund','payment_refund')
                             AND COALESCE(ft.debit,0) > 0
                        THEN -1 * COALESCE(ft.debit,0)
                        ELSE COALESCE(ft.debit,0) - COALESCE(ft.credit,0)
                    END AS amount,
                    CASE
                        WHEN LOWER(COALESCE(ft.tx_type,'')) IN ('room_charge','accommodation','accommodation_charge','nightly','room_night')
                             OR LOWER(COALESCE(ft.source_table,'')) = 'rel_orders_prices'
                             OR LOWER(COALESCE(ft.description,'')) LIKE '%room charge%'
                             OR LOWER(COALESCE(ft.description,'')) LIKE '%accommodation%'
                        THEN 'accommodation'
                        WHEN $extraClassificationExpr LIKE '%restaurant%' OR $extraClassificationExpr LIKE '%food%' THEN 'restaurant'
                        WHEN $extraClassificationExpr LIKE '%bar%' OR $extraClassificationExpr LIKE '%drink%' OR $extraClassificationExpr LIKE '%beverage%' THEN 'bar'
                        WHEN $extraClassificationExpr LIKE '%spa%' OR $extraClassificationExpr LIKE '%spar%' THEN 'spa'
                        WHEN $extraClassificationExpr LIKE '%curio%' OR $extraClassificationExpr LIKE '%inventory_items%' THEN 'curio'
                        ELSE 'extras'
                    END AS category_key
                FROM folio_transactions ft
                $extraChargeItemsJoinSql
            ) x
            GROUP BY x.folio_id
        ) ftcalc ON ftcalc.folio_id = f.id
        WHERE 1=1
    ";

    $params = [];

    if ($search !== '') {
        $sql .= "
            AND (
                f.folio_no LIKE :search_folio_no
                OR f.folio_name LIKE :search_folio_name
                OR f.folio_type LIKE :search_folio_type
                OR o.order_no LIKE :search_order_no
                OR g.firstname LIKE :search_guest_first
                OR g.lastname LIKE :search_guest_last
                OR COALESCE(a.firstname,'') LIKE :search_agent
                OR r.room_no LIKE :search_room_no
                OR rmix.room_mix LIKE :search_room_mix
            )
        ";

        $like = '%' . $search . '%';
        $params[':search_folio_no']    = $like;
        $params[':search_folio_name']  = $like;
        $params[':search_folio_type']  = $like;
        $params[':search_order_no']    = $like;
        $params[':search_guest_first'] = $like;
        $params[':search_guest_last']  = $like;
        $params[':search_agent']       = $like;
        $params[':search_room_no']     = $like;
        $params[':search_room_mix']    = $like;
    }

    if ($status !== '') {
        if ($status === 'open') {
            $sql .= " AND COALESCE(ftcalc.live_total_balance, f.balance, 0) > 0 ";
        } elseif ($status === 'paid') {
            $sql .= " AND COALESCE(ftcalc.live_total_balance, f.balance, 0) <= 0 ";
        }
    }

    if ($folioType !== '') {
        $sql .= " AND f.folio_type = :folio_type ";
        $params[':folio_type'] = $folioType;
    }

    $sql .= "
        ORDER BY
            o.id DESC,
            CASE f.folio_type
                WHEN 'master' THEN 1
                WHEN 'room' THEN 2
                WHEN 'guest' THEN 3
                ELSE 9
            END,
            f.id DESC
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $folios = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $totalFolios = count($folios);

    foreach ($folios as $folio) {
        $totalCharges += (float)($folio['live_total_charges'] ?? $folio['total_charges'] ?? 0);
        $totalPayments += (float)($folio['live_total_payments'] ?? $folio['total_payments'] ?? 0);
        $totalBalance += (float)($folio['live_total_balance'] ?? $folio['balance'] ?? 0);
    }
} catch (Throwable $e) {
    $errors[] = $e->getMessage();
}

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-receipt-cutoff"></i> Financial Control</div>
            <div class="hero-title mt-2">Folios</div>
            <div class="hero-subtitle mt-2">
                Track master, room, and guest folios with room mix, stay details, charges, payments, and balances across all bookings.
            </div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <a href="/rooms/modules/reservations/reservation_list.php" class="btn btn-outline-light">
                <i class="bi bi-arrow-left me-2"></i>Back to Reservations
            </a>
        </div>
    </div>
</div>

<?php if ($errors): ?>
    <div class="alert alert-danger">
        <div class="fw-bold mb-2">Please fix the following:</div>
        <ul class="mb-0">
            <?php foreach ($errors as $err): ?>
                <li><?php echo h($err); ?></li>
            <?php endforeach; ?>
        </ul>
    </div>
<?php endif; ?>

<div class="row g-4 mb-4">
    <div class="col-sm-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-primary-subtle text-primary">
                <i class="bi bi-collection"></i>
            </div>
            <div>
                <div class="metric-label">Folios</div>
                <div class="metric-value"><?php echo number_format($totalFolios); ?></div>
            </div>
        </div>
    </div>

    <div class="col-sm-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-success-subtle text-success">
                <i class="bi bi-plus-circle"></i>
            </div>
            <div>
                <div class="metric-label">Charges</div>
                <div class="metric-value"><?php echo money_fmt($totalCharges); ?></div>
            </div>
        </div>
    </div>

    <div class="col-sm-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-info-subtle text-info">
                <i class="bi bi-credit-card"></i>
            </div>
            <div>
                <div class="metric-label">Payments</div>
                <div class="metric-value"><?php echo money_fmt($totalPayments); ?></div>
            </div>
        </div>
    </div>

    <div class="col-sm-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-warning-subtle text-warning">
                <i class="bi bi-cash-stack"></i>
            </div>
            <div>
                <div class="metric-label">Balance</div>
                <div class="metric-value"><?php echo money_fmt($totalBalance); ?></div>
            </div>
        </div>
    </div>
</div>

<div class="card panel-card mb-4">
    <div class="card-header">
        <h5 class="mb-0">Search & Filters</h5>
    </div>
    <div class="card-body">
        <form method="get" class="row g-3 align-items-end">
            <div class="col-md-5">
                <label class="form-label">Search</label>
                <input type="text" name="q" class="form-control" value="<?php echo h($search); ?>" placeholder="Folio no, booking no, guest, room, agent, room mix...">
            </div>

            <div class="col-md-3">
                <label class="form-label">Status</label>
                <select name="status" class="form-select">
                    <option value="">All</option>
                    <option value="open" <?php echo $status === 'open' ? 'selected' : ''; ?>>Open Balance</option>
                    <option value="paid" <?php echo $status === 'paid' ? 'selected' : ''; ?>>Paid / Zero Balance</option>
                </select>
            </div>

            <div class="col-md-3">
                <label class="form-label">Folio Type</label>
                <select name="folio_type" class="form-select">
                    <option value="">All</option>
                    <option value="master" <?php echo $folioType === 'master' ? 'selected' : ''; ?>>Master</option>
                    <option value="room" <?php echo $folioType === 'room' ? 'selected' : ''; ?>>Room</option>
                    <option value="guest" <?php echo $folioType === 'guest' ? 'selected' : ''; ?>>Guest</option>
                </select>
            </div>

            <div class="col-md-1 d-grid">
                <button class="btn btn-primary">Go</button>
            </div>

            <div class="col-12">
                <a href="/rooms/modules/payments/folio.php" class="btn btn-light">Reset</a>
            </div>
        </form>
    </div>
</div>

<div class="card panel-card">
    <div class="card-header d-flex justify-content-between align-items-center">
        <div>
            <h5 class="mb-0">Folio List</h5>
            <small>All booking folios with room mix, charges, payments, and current balance.</small>
        </div>
    </div>

    <div class="card-body p-0">
        <div class="table-responsive">
            <table class="table table-modern align-middle mb-0">
                <thead>
                    <tr>
                        <th>Folio</th>
                        <th>Type</th>
                        <th>Booking</th>
                        <th>Guest</th>
                        <th>Agent</th>
                        <th>Stay</th>
                        <th>Room Mix / Pax</th>
                        <th class="text-end">Charges</th>
                        <th class="text-end">Payments</th>
                        <th>Breakdown</th>
                        <th class="text-end">Balance</th>
                        <th class="text-end">Actions</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (!$folios): ?>
                        <tr>
                            <td colspan="12" class="text-center text-muted py-5">
                                No folios found.
                            </td>
                        </tr>
                    <?php else: ?>
                        <?php foreach ($folios as $folio): ?>
                            <?php
                            $guestName = trim(($folio['firstname'] ?? '') . ' ' . ($folio['lastname'] ?? ''));
                            if ($guestName === '') {
                                $guestName = 'Guest';
                            }

                            $agentName = trim((string)($folio['agent_name'] ?? ''));
                            if ($agentName === '') {
                                $agentName = 'Direct';
                            }

                            $charges = (float)($folio['live_total_charges'] ?? $folio['total_charges'] ?? 0);
                            $payments = (float)($folio['live_total_payments'] ?? $folio['total_payments'] ?? 0);
                            $balance = (float)($folio['live_total_balance'] ?? $folio['balance'] ?? 0);
                            $balanceBadge = $balance > 0.00001 ? 'warning' : 'success';

                            $breakdownHtml = '';
                            $breakdownHtml .= folio_money_badge((float)($folio['accommodation_total'] ?? 0), 'Room', 'dark');
                            $breakdownHtml .= folio_money_badge((float)($folio['restaurant_total'] ?? 0), 'Restaurant', 'primary');
                            $breakdownHtml .= folio_money_badge((float)($folio['bar_total'] ?? 0), 'Bar', 'info');
                            $breakdownHtml .= folio_money_badge((float)($folio['spa_total'] ?? 0), 'Spa/Spar', 'success');
                            $breakdownHtml .= folio_money_badge((float)($folio['curio_total'] ?? 0), 'Curio', 'warning');
                            $breakdownHtml .= folio_money_badge((float)($folio['other_extra_total'] ?? 0), 'Other', 'secondary');
                            if ($breakdownHtml === '') {
                                $breakdownHtml = '<span class="text-muted small">No charges</span>';
                            }

                            $folioSubtitle = trim((string)($folio['folio_name'] ?? ''));
                            if (strtolower((string)$folio['folio_type']) === 'room' && !empty($folio['room_no'])) {
                                $folioSubtitle .= ($folioSubtitle !== '' ? ' • ' : '') . 'Room ' . $folio['room_no'];
                            }

                            $roomMix = trim((string)($folio['room_mix'] ?? ''));
                            if ($roomMix === '') {
                                $roomMix = ((int)($folio['totalrooms'] ?? 1)) . ' room(s)';
                            }
                            ?>
                            <tr>
                                <td>
                                    <div class="data-stack">
                                        <strong><?php echo h($folio['folio_no']); ?></strong>
                                        <small><?php echo h($folioSubtitle); ?></small>
                                    </div>
                                </td>

                                <td><?php echo folio_type_badge((string)($folio['folio_type'] ?? '')); ?></td>

                                <td>
                                    <div class="data-stack">
                                        <strong><?php echo h($folio['order_no'] ?: ('#' . $folio['order_id'])); ?></strong>
                                        <small>Order ID <?php echo (int)$folio['order_id']; ?></small>
                                    </div>
                                </td>

                                <td><?php echo h($guestName); ?></td>
                                <td><?php echo h($agentName); ?></td>

                                <td>
                                    <div class="data-stack">
                                        <strong><?php echo h($folio['check_in']); ?></strong>
                                        <small>to <?php echo h($folio['check_out']); ?></small>
                                    </div>
                                </td>

                                <td>
                                    <div class="data-stack">
                                        <strong><?php echo h($roomMix); ?></strong>
                                        <small>
                                            <?php echo (int)($folio['totalrooms'] ?? 1); ?> room(s) •
                                            <?php echo (int)($folio['adults'] ?? 0); ?>A /
                                            <?php echo (int)($folio['kids'] ?? 0); ?>C
                                        </small>
                                    </div>
                                </td>

                                <td class="text-end"><?php echo money_fmt($charges); ?></td>
                                <td class="text-end"><?php echo money_fmt($payments); ?></td>
                                <td><div class="breakdown-wrap"><?php echo $breakdownHtml; ?></div></td>

                                <td class="text-end">
                                    <span class="badge text-bg-<?php echo h($balanceBadge); ?> px-3 py-2">
                                        <?php echo money_fmt($balance); ?>
                                    </span>
                                </td>

                                <td class="text-end">
                                    <div class="d-inline-flex gap-2">
                                        <a href="/rooms/modules/payments/folio_view.php?folio_id=<?php echo (int)$folio['id']; ?>"
                                           class="btn btn-sm btn-primary">
                                            Open
                                        </a>
                                        <a href="/rooms/modules/payments/payments.php?order_id=<?php echo (int)$folio['order_id']; ?>"
                                           class="btn btn-sm btn-light">
                                            Payments
                                        </a>
                                    </div>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                </tbody>
                <?php if ($folios): ?>
                    <tfoot>
                        <tr>
                            <th colspan="7" class="text-end">Totals</th>
                            <th class="text-end"><?php echo money_fmt($totalCharges); ?></th>
                            <th class="text-end"><?php echo money_fmt($totalPayments); ?></th>
                            <th></th>
                            <th class="text-end"><?php echo money_fmt($totalBalance); ?></th>
                            <th></th>
                        </tr>
                    </tfoot>
                <?php endif; ?>
            </table>
        </div>
    </div>
</div>


<style>
.hero-band{padding:14px 18px!important;border-radius:18px!important;margin-bottom:12px!important}
.metric-card{padding:12px 14px!important;border-radius:16px!important}
.panel-card{border-radius:18px!important;overflow:hidden!important}
.table-modern th{font-size:.74rem!important;text-transform:uppercase;letter-spacing:.05em;white-space:nowrap}
.table-modern td{font-size:.84rem!important;vertical-align:middle!important}
.breakdown-wrap{display:flex;flex-wrap:wrap;gap:4px;min-width:190px}
.breakdown-chip{display:inline-flex;align-items:center;border-radius:999px;padding:.22rem .48rem;font-size:.68rem;font-weight:800;white-space:nowrap}
</style>

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