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

$pageTitle = 'Night Audit / Daily Manager Report';

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('dm_table_exists')) {
    function dm_table_exists(PDO $pdo, string $tableName): bool {
        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM information_schema.tables
            WHERE table_schema = DATABASE()
              AND table_name = :table_name
        ");
        $stmt->execute([':table_name' => $tableName]);
        return (int)$stmt->fetchColumn() > 0;
    }
}
if (!function_exists('dm_column_exists')) {
    function dm_column_exists(PDO $pdo, string $tableName, string $columnName): bool {
        $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,
        ]);
        return (int)$stmt->fetchColumn() > 0;
    }
}

$errors = [];

$reportDate = trim((string)($_GET['report_date'] ?? date('Y-m-d')));
$selectedPropertyId = (int)($_GET['property_id'] ?? 0);

if ($reportDate === '') {
    $reportDate = date('Y-m-d');
}
if (strtotime($reportDate) === false) {
    $errors[] = 'Invalid report date selected.';
}

$requiredTables = ['orders', 'order_rooming', 'rooms', 'room_types', 'properties', 'folios', 'folio_transactions', 'payment'];
foreach ($requiredTables as $table) {
    if (!dm_table_exists($pdo, $table)) {
        $errors[] = 'Required table missing: ' . $table;
    }
}

$hasOrderStatus = !$errors && dm_column_exists($pdo, 'orders', 'status');
$hasOrderCancelled = !$errors && dm_column_exists($pdo, 'orders', 'cancelled');
$hasRoomingCancelled = !$errors && dm_column_exists($pdo, 'order_rooming', 'cancelled');
$hasRoomingProperty = !$errors && dm_column_exists($pdo, 'order_rooming', 'property_id');
$hasRoomActive = !$errors && dm_column_exists($pdo, 'rooms', 'is_active');
$hasPaymentVoided = !$errors && dm_column_exists($pdo, 'payment', 'is_voided');

$properties = [];
if (!$errors) {
    try {
        $properties = $pdo->query("
            SELECT id, name, code
            FROM properties
            WHERE is_active = 1
            ORDER BY name ASC
        ")->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $errors[] = 'Could not load properties: ' . $e->getMessage();
    }
}

$daySql = $pdo->quote($reportDate);

$propertyFilterOrders = '';
$propertyFilterFt = '';
$propertyFilterPayment = '';
$propertyFilterRoom = '';
$paramsOrders = [];
$paramsFt = [];
$paramsPayment = [];
$paramsRoom = [];

if ($selectedPropertyId > 0) {
    $propertyFilterOrders = " AND o.property_id = :property_id ";
    $propertyFilterFt = " AND ft.property_id = :property_id ";
    $propertyFilterPayment = " AND pmt.property_id = :property_id ";
    $propertyFilterRoom = " AND COALESCE(r.property_id, rt.property_id) = :property_id ";

    $paramsOrders[':property_id'] = $selectedPropertyId;
    $paramsFt[':property_id'] = $selectedPropertyId;
    $paramsPayment[':property_id'] = $selectedPropertyId;
    $paramsRoom[':property_id'] = $selectedPropertyId;
}

$orderActiveSql = '';
if (!$errors) {
    if ($hasOrderCancelled) {
        $orderActiveSql .= " AND COALESCE(o.cancelled, 0) = 0 ";
    }
    if ($hasOrderStatus) {
        $orderActiveSql .= " AND LOWER(COALESCE(o.status, '')) NOT IN ('cancelled','canceled','void','deleted') ";
    }
}

$roomingActiveSql = '';
if (!$errors) {
    if ($hasRoomingCancelled) {
        $roomingActiveSql .= " AND COALESCE(orm.cancelled, 0) = 0 ";
    }
}

$summary = [
    'room_revenue' => 0,
    'extras_revenue' => 0,
    'total_revenue' => 0,
    'payments_total' => 0,
    'voided_payments' => 0,
    'outstanding_total' => 0,
    'arrivals' => 0,
    'departures' => 0,
    'inhouse' => 0,
    'available_rooms' => 0,
    'occupied_rooms' => 0,
    'vacant_rooms' => 0,
    'occupancy_percent' => 0,
    'adr' => 0,
    'revpar' => 0,
    'arrival_balance' => 0,
    'departure_balance' => 0,
    'outstanding_count' => 0,
    'inhouse_pax' => 0,
    'restaurant_revenue' => 0,
    'restaurant_covers' => 0,
    'restaurant_checks' => 0,
    'cash_variance' => 0,
];

$paymentMethodRows = [];
$arrivals = [];
$departures = [];
$inhouse = [];
$outstandingAlerts = [];
$revenueRows = [];
$cashRows = [];
$restaurantRows = [];
$roomTypeSnapshotRows = [];
$managerChartRows = [];


if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT
                COALESCE(SUM(CASE WHEN ft.tx_type = 'room_charge' THEN ft.debit ELSE 0 END), 0) AS room_revenue,
                COALESCE(SUM(CASE WHEN ft.tx_type <> 'room_charge' AND ft.debit > 0 THEN ft.debit ELSE 0 END), 0) AS extras_revenue,
                COALESCE(SUM(ft.debit), 0) AS total_revenue
            FROM folio_transactions ft
            WHERE DATE(ft.tx_date) = $daySql
            $propertyFilterFt
        ");
        $stmt->execute($paramsFt);
        $rev = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
        $summary['room_revenue'] = (float)($rev['room_revenue'] ?? 0);
        $summary['extras_revenue'] = (float)($rev['extras_revenue'] ?? 0);
        $summary['total_revenue'] = (float)($rev['total_revenue'] ?? 0);
    } catch (Throwable $e) {
        $errors[] = 'Could not load revenue: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $voidExpr = $hasPaymentVoided ? "COALESCE(pmt.is_voided,0)" : "0";

        $stmt = $pdo->prepare("
            SELECT
                COALESCE(SUM(CASE WHEN $voidExpr = 0 THEN pmt.amount ELSE 0 END), 0) AS payments_total,
                COALESCE(SUM(CASE WHEN $voidExpr = 1 THEN pmt.amount ELSE 0 END), 0) AS voided_payments
            FROM payment pmt
            WHERE DATE(COALESCE(pmt.date_time, CONCAT(pmt.added_date, ' 00:00:00'))) = $daySql
            $propertyFilterPayment
        ");
        $stmt->execute($paramsPayment);
        $pay = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
        $summary['payments_total'] = (float)($pay['payments_total'] ?? 0);
        $summary['voided_payments'] = (float)($pay['voided_payments'] ?? 0);

        $stmt = $pdo->prepare("
            SELECT
                COALESCE(NULLIF(pmt.payment_method,''), 'Unspecified') AS payment_method,
                COUNT(*) AS payment_count,
                COALESCE(SUM(CASE WHEN $voidExpr = 0 THEN pmt.amount ELSE 0 END), 0) AS active_total,
                COALESCE(SUM(CASE WHEN $voidExpr = 1 THEN pmt.amount ELSE 0 END), 0) AS voided_total
            FROM payment pmt
            WHERE DATE(COALESCE(pmt.date_time, CONCAT(pmt.added_date, ' 00:00:00'))) = $daySql
            $propertyFilterPayment
            GROUP BY payment_method
            ORDER BY active_total DESC
        ");
        $stmt->execute($paramsPayment);
        $paymentMethodRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $errors[] = 'Could not load payments: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT COALESCE(SUM(f.balance),0)
            FROM folios f
            INNER JOIN orders o ON o.id = f.order_id
            WHERE f.balance > 0.00001
            $propertyFilterOrders
        ");
        $stmt->execute($paramsOrders);
        $summary['outstanding_total'] = (float)($stmt->fetchColumn() ?: 0);
    } catch (Throwable $e) {
        $summary['outstanding_total'] = 0;
    }
}

if (!$errors) {
    try {
        $baseSelect = "
            SELECT
                o.id,
                o.order_no,
                o.check_in,
                o.check_out,
                p.name AS property_name,
                p.code AS property_code,
                TRIM(CONCAT(COALESCE(g.firstname,''), ' ', COALESCE(g.lastname,''))) AS guest_name,
                TRIM(CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,''))) AS agent_name,
                COALESCE(SUM(f.balance),0) AS balance
            FROM orders o
            LEFT JOIN guests g ON g.id = o.guest_id
            LEFT JOIN agents a ON a.id = o.agent_id
            LEFT JOIN properties p ON p.id = o.property_id
            LEFT JOIN folios f ON f.order_id = o.id
            WHERE 1=1
            $propertyFilterOrders
            $orderActiveSql
        ";

        $stmt = $pdo->prepare($baseSelect . "
            AND DATE(o.check_in) = $daySql
            GROUP BY o.id, o.order_no, o.check_in, o.check_out, property_name, property_code, guest_name, agent_name
            ORDER BY o.check_in ASC, guest_name ASC
        ");
        $stmt->execute($paramsOrders);
        $arrivals = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $summary['arrivals'] = count($arrivals);

        $stmt = $pdo->prepare($baseSelect . "
            AND DATE(o.check_out) = $daySql
            GROUP BY o.id, o.order_no, o.check_in, o.check_out, property_name, property_code, guest_name, agent_name
            ORDER BY o.check_out ASC, guest_name ASC
        ");
        $stmt->execute($paramsOrders);
        $departures = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $summary['departures'] = count($departures);

        $stmt = $pdo->prepare($baseSelect . "
            AND o.check_in <= $daySql
            AND o.check_out > $daySql
            GROUP BY o.id, o.order_no, o.check_in, o.check_out, property_name, property_code, guest_name, agent_name
            ORDER BY property_name ASC, guest_name ASC
        ");
        $stmt->execute($paramsOrders);
        $inhouse = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $summary['inhouse'] = count($inhouse);
    } catch (Throwable $e) {
        $errors[] = 'Could not load arrivals/departures: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $roomWhere = "1=1 $propertyFilterRoom";
        if ($hasRoomActive) {
            $roomWhere .= " AND COALESCE(r.is_active,1) = 1 ";
        }

        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM rooms r
            LEFT JOIN room_types rt ON rt.id = r.room_type_id
            WHERE $roomWhere
        ");
        $stmt->execute($paramsRoom);
        $availableRooms = (int)($stmt->fetchColumn() ?: 0);

        $occWhere = "
            orm.check_in <= $daySql
            AND orm.check_out > $daySql
            $roomingActiveSql
            $orderActiveSql
        ";
        $occParams = [];
        if ($selectedPropertyId > 0) {
            if ($hasRoomingProperty) {
                $occWhere .= " AND COALESCE(orm.property_id, o.property_id, r.property_id, rt.property_id) = :property_id ";
            } else {
                $occWhere .= " AND COALESCE(o.property_id, r.property_id, rt.property_id) = :property_id ";
            }
            $occParams[':property_id'] = $selectedPropertyId;
        }

        $stmt = $pdo->prepare("
            SELECT COUNT(DISTINCT orm.id)
            FROM order_rooming orm
            LEFT JOIN orders o ON o.id = orm.order_id
            LEFT JOIN rooms r ON r.id = orm.room_id
            LEFT JOIN room_types rt ON rt.id = orm.room_type_id
            WHERE $occWhere
        ");
        $stmt->execute($occParams);
        $occupiedRooms = (int)($stmt->fetchColumn() ?: 0);
        if ($availableRooms > 0 && $occupiedRooms > $availableRooms) {
            $occupiedRooms = $availableRooms;
        }

        $summary['available_rooms'] = $availableRooms;
        $summary['occupied_rooms'] = $occupiedRooms;
        $summary['vacant_rooms'] = max(0, $availableRooms - $occupiedRooms);
        $summary['occupancy_percent'] = $availableRooms > 0 ? ($occupiedRooms / $availableRooms) * 100 : 0;
    } catch (Throwable $e) {
        $errors[] = 'Could not load occupancy: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT
                ft.tx_type,
                COUNT(*) AS tx_count,
                COALESCE(SUM(ft.debit),0) AS debit_total,
                COALESCE(SUM(ft.credit),0) AS credit_total
            FROM folio_transactions ft
            WHERE DATE(ft.tx_date) = $daySql
            $propertyFilterFt
            GROUP BY ft.tx_type
            ORDER BY debit_total DESC, credit_total DESC
        ");
        $stmt->execute($paramsFt);
        $revenueRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $stmt = $pdo->prepare("
            SELECT
                o.id,
                o.order_no,
                o.check_in,
                o.check_out,
                p.name AS property_name,
                TRIM(CONCAT(COALESCE(g.firstname,''), ' ', COALESCE(g.lastname,''))) AS guest_name,
                TRIM(CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,''))) AS agent_name,
                COALESCE(SUM(f.balance),0) AS balance
            FROM folios f
            INNER JOIN orders o ON o.id = f.order_id
            LEFT JOIN guests g ON g.id = o.guest_id
            LEFT JOIN agents a ON a.id = o.agent_id
            LEFT JOIN properties p ON p.id = o.property_id
            WHERE f.balance > 0.00001
            $propertyFilterOrders
            GROUP BY o.id, o.order_no, o.check_in, o.check_out, property_name, guest_name, agent_name
            ORDER BY balance DESC
            LIMIT 20
        ");
        $stmt->execute($paramsOrders);
        $outstandingAlerts = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $outstandingAlerts = [];
    }
}


/*
   Comprehensive manager report extensions.
   These are defensive: if optional restaurant / room detail columns are missing,
   the report still opens and the related values remain zero.
*/
if (!$errors) {
    try {
        $summary['adr'] = (int)$summary['occupied_rooms'] > 0
            ? ((float)$summary['room_revenue'] / max(1, (int)$summary['occupied_rooms']))
            : 0;
        $summary['revpar'] = (int)$summary['available_rooms'] > 0
            ? ((float)$summary['room_revenue'] / max(1, (int)$summary['available_rooms']))
            : 0;
        $summary['cash_variance'] = (float)$summary['payments_total'] - (float)$summary['total_revenue'];

        foreach ($arrivals as $row) {
            $summary['arrival_balance'] += (float)($row['balance'] ?? 0);
        }
        foreach ($departures as $row) {
            $summary['departure_balance'] += (float)($row['balance'] ?? 0);
        }
        $summary['outstanding_count'] = count($outstandingAlerts);
    } catch (Throwable $e) {
        /* Non-critical summary calculations. */
    }
}

if (!$errors) {
    try {
        $inhouseParams = [];
        $inhouseWhere = "
            orm.check_in <= $daySql
            AND orm.check_out > $daySql
            $roomingActiveSql
            $orderActiveSql
        ";
        if ($selectedPropertyId > 0) {
            if ($hasRoomingProperty) {
                $inhouseWhere .= " AND COALESCE(orm.property_id, o.property_id, r.property_id, rt.property_id) = :property_id ";
            } else {
                $inhouseWhere .= " AND COALESCE(o.property_id, r.property_id, rt.property_id) = :property_id ";
            }
            $inhouseParams[':property_id'] = $selectedPropertyId;
        }

        $stmt = $pdo->prepare("
            SELECT COALESCE(SUM(COALESCE(orm.adults,0) + COALESCE(orm.children,0)),0)
            FROM order_rooming orm
            LEFT JOIN orders o ON o.id = orm.order_id
            LEFT JOIN rooms r ON r.id = orm.room_id
            LEFT JOIN room_types rt ON rt.id = orm.room_type_id
            WHERE $inhouseWhere
        ");
        $stmt->execute($inhouseParams);
        $summary['inhouse_pax'] = (int)($stmt->fetchColumn() ?: 0);
    } catch (Throwable $e) {
        $summary['inhouse_pax'] = 0;
    }
}

if (!$errors && dm_table_exists($pdo, 'restaurant_orders')) {
    try {
        $hasRestTotal = dm_column_exists($pdo, 'restaurant_orders', 'total_amount');
        $hasRestCovers = dm_column_exists($pdo, 'restaurant_orders', 'covers');
        $hasRestOpenedAt = dm_column_exists($pdo, 'restaurant_orders', 'opened_at');
        $hasRestCreatedAt = dm_column_exists($pdo, 'restaurant_orders', 'created_at');
        $hasRestStatus = dm_column_exists($pdo, 'restaurant_orders', 'status');
        $hasRestProperty = dm_column_exists($pdo, 'restaurant_orders', 'property_id');
        $restDateColumn = $hasRestOpenedAt ? 'opened_at' : ($hasRestCreatedAt ? 'created_at' : 'created_at');
        $restTotalExpr = $hasRestTotal ? 'COALESCE(total_amount,0)' : '0';
        $restCoverExpr = $hasRestCovers ? 'COALESCE(covers,1)' : '1';
        $restWhere = "DATE($restDateColumn) = $daySql";
        $restParams = [];

        if ($hasRestStatus) {
            $restWhere .= " AND LOWER(COALESCE(status,'')) NOT IN ('void','cancelled','canceled','deleted')";
        }
        if ($selectedPropertyId > 0 && $hasRestProperty) {
            $restWhere .= " AND property_id = :property_id";
            $restParams[':property_id'] = $selectedPropertyId;
        }

        $stmt = $pdo->prepare("
            SELECT
                CASE
                    WHEN TIME($restDateColumn) >= '05:00:00' AND TIME($restDateColumn) < '11:00:00' THEN 'Breakfast'
                    WHEN TIME($restDateColumn) >= '11:00:00' AND TIME($restDateColumn) < '17:00:00' THEN 'Lunch'
                    WHEN TIME($restDateColumn) >= '17:00:00' AND TIME($restDateColumn) <= '23:59:59' THEN 'Dinner'
                    ELSE 'Other'
                END AS meal_period,
                COUNT(*) AS checks_count,
                COALESCE(SUM($restCoverExpr),0) AS covers,
                COALESCE(SUM($restTotalExpr),0) AS revenue
            FROM restaurant_orders
            WHERE $restWhere
            GROUP BY meal_period
            ORDER BY FIELD(meal_period,'Breakfast','Lunch','Dinner','Other')
        ");
        $stmt->execute($restParams);
        $restaurantRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach ($restaurantRows as $rr) {
            $summary['restaurant_revenue'] += (float)($rr['revenue'] ?? 0);
            $summary['restaurant_covers'] += (int)($rr['covers'] ?? 0);
            $summary['restaurant_checks'] += (int)($rr['checks_count'] ?? 0);
        }
    } catch (Throwable $e) {
        $restaurantRows = [];
    }
}

if (!$errors) {
    try {
        $rtParams = [];
        $rtWhereRooms = "1=1";
        if ($selectedPropertyId > 0) {
            $rtWhereRooms .= " AND COALESCE(r.property_id, rt.property_id) = :property_id";
            $rtParams[':property_id'] = $selectedPropertyId;
        }
        if ($hasRoomActive) {
            $rtWhereRooms .= " AND COALESCE(r.is_active,1) = 1";
        }
        $stmt = $pdo->prepare("
            SELECT rt.id, rt.title AS room_type, COALESCE(p.name,'No Property') AS property_name, COUNT(r.id) AS rooms_count
            FROM room_types rt
            LEFT JOIN rooms r ON r.room_type_id = rt.id
            LEFT JOIN properties p ON p.id = COALESCE(r.property_id, rt.property_id)
            WHERE $rtWhereRooms
            GROUP BY rt.id, rt.title, property_name
            HAVING rooms_count > 0
            ORDER BY property_name ASC, rt.title ASC
        ");
        $stmt->execute($rtParams);
        $types = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($types as $type) {
            $occParams = [':room_type_id' => (int)$type['id']];
            $occWhere = "
                orm.room_type_id = :room_type_id
                AND orm.check_in <= $daySql
                AND orm.check_out > $daySql
                $roomingActiveSql
                $orderActiveSql
            ";
            if ($selectedPropertyId > 0) {
                if ($hasRoomingProperty) {
                    $occWhere .= " AND COALESCE(orm.property_id, o.property_id, r.property_id, rt.property_id) = :property_id";
                } else {
                    $occWhere .= " AND COALESCE(o.property_id, r.property_id, rt.property_id) = :property_id";
                }
                $occParams[':property_id'] = $selectedPropertyId;
            }
            $stmtOcc = $pdo->prepare("
                SELECT COUNT(DISTINCT orm.id)
                FROM order_rooming orm
                LEFT JOIN orders o ON o.id = orm.order_id
                LEFT JOIN rooms r ON r.id = orm.room_id
                LEFT JOIN room_types rt ON rt.id = orm.room_type_id
                WHERE $occWhere
            ");
            $stmtOcc->execute($occParams);
            $occupied = (int)($stmtOcc->fetchColumn() ?: 0);
            $rooms = (int)$type['rooms_count'];
            if ($rooms > 0 && $occupied > $rooms) $occupied = $rooms;
            $roomTypeSnapshotRows[] = [
                'property_name' => $type['property_name'],
                'room_type' => $type['room_type'],
                'rooms_count' => $rooms,
                'occupied_rooms' => $occupied,
                'vacant_rooms' => max(0, $rooms - $occupied),
                'occupancy_percent' => $rooms > 0 ? (($occupied / $rooms) * 100) : 0,
            ];
        }
    } catch (Throwable $e) {
        $roomTypeSnapshotRows = [];
    }
}

$managerChartRows = [
    ['label' => 'Room Revenue', 'amount' => (float)$summary['room_revenue']],
    ['label' => 'Other Folio Revenue', 'amount' => (float)$summary['extras_revenue']],
    ['label' => 'Restaurant Revenue', 'amount' => (float)$summary['restaurant_revenue']],
    ['label' => 'Payments Collected', 'amount' => (float)$summary['payments_total']],
];

if (isset($_GET['export']) && in_array((string)$_GET['export'], ['xls','excel'], true) && !$errors) {
    $filename = 'daily_manager_report_full_' . $reportDate . '.xls';

    header('Content-Type: application/vnd.ms-excel; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Pragma: no-cache');
    header('Expires: 0');

    $xlsText = static function ($value): string { return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8'); };
    $xlsNum = static function ($value, int $decimals = 2): string { return number_format((float)$value, $decimals, '.', ''); };

    echo "<!DOCTYPE html><html><head><meta charset=\"utf-8\">";
    echo "<style>body{font-family:Arial,Helvetica,sans-serif;color:#111827;}h1{font-size:22px;margin:0;color:#fff;}h2{font-size:15px;margin:18px 0 7px;background:#e5e7eb;padding:7px;color:#111827;}table{border-collapse:collapse;margin-bottom:14px;}th{background:#1f2937;color:#fff;border:1px solid #9ca3af;padding:6px;}td{border:1px solid #d1d5db;padding:5px;vertical-align:top;}.title{background:#0f172a;color:#fff;padding:12px;}.sub{color:#e5e7eb;font-size:11px;}.num{text-align:right;}.good{background:#dcfce7;}.warn{background:#fef3c7;}.bad{background:#fee2e2;}.barwrap{width:190px;height:13px;background:#e5e7eb;}.bar{height:13px;background:#2563eb;}</style>";
    echo "</head><body>";

    echo '<table style="width:100%;"><tr><td class="title" colspan="12"><h1>Daily Manager Report</h1><div class="sub">Report date: ' . $xlsText($reportDate) . ' | Exported: ' . date('Y-m-d H:i:s') . '</div></td></tr></table>';

    echo '<h2>Executive Summary</h2><table><tr><th>Revenue</th><th>Room Revenue</th><th>Extras Revenue</th><th>Restaurant Revenue</th><th>Payments</th><th>Variance</th><th>Outstanding</th><th>Occupancy %</th><th>ADR</th><th>RevPAR</th><th>Arrivals</th><th>Departures</th></tr><tr>';
    echo '<td class="num">' . $xlsNum($summary['total_revenue']) . '</td><td class="num">' . $xlsNum($summary['room_revenue']) . '</td><td class="num">' . $xlsNum($summary['extras_revenue']) . '</td><td class="num">' . $xlsNum($summary['restaurant_revenue']) . '</td><td class="num">' . $xlsNum($summary['payments_total']) . '</td><td class="num">' . $xlsNum($summary['cash_variance']) . '</td><td class="num">' . $xlsNum($summary['outstanding_total']) . '</td><td class="num">' . $xlsNum($summary['occupancy_percent']) . '%</td><td class="num">' . $xlsNum($summary['adr']) . '</td><td class="num">' . $xlsNum($summary['revpar']) . '</td><td class="num">' . (int)$summary['arrivals'] . '</td><td class="num">' . (int)$summary['departures'] . '</td></tr></table>';

    echo '<h2>Graphical Analysis - Revenue Mix</h2><table><tr><th>Measure</th><th>Amount</th><th>Visual</th></tr>';
    $maxChart = 0.0; foreach ($managerChartRows as $cr) { $maxChart = max($maxChart, (float)$cr['amount']); }
    foreach ($managerChartRows as $cr) { $pct = $maxChart > 0 ? min(100, ((float)$cr['amount'] / $maxChart) * 100) : 0; echo '<tr><td>' . $xlsText($cr['label']) . '</td><td class="num">' . $xlsNum($cr['amount']) . '</td><td><div class="barwrap"><div class="bar" style="width:' . $xlsNum($pct) . '%"></div></div></td></tr>'; }
    echo '</table>';

    echo '<h2>Cash Reconciliation by Method</h2><table><tr><th>Method</th><th>Count</th><th>Active Total</th><th>Voided Total</th></tr>';
    foreach ($paymentMethodRows as $row) { echo '<tr><td>' . $xlsText($row['payment_method']) . '</td><td class="num">' . (int)$row['payment_count'] . '</td><td class="num">' . $xlsNum($row['active_total']) . '</td><td class="num">' . $xlsNum($row['voided_total']) . '</td></tr>'; }
    echo '</table>';

    echo '<h2>Transaction Types</h2><table><tr><th>Type</th><th>Count</th><th>Debit</th><th>Credit</th></tr>';
    foreach ($revenueRows as $row) { echo '<tr><td>' . $xlsText(ucwords(str_replace('_',' ',(string)$row['tx_type']))) . '</td><td class="num">' . (int)$row['tx_count'] . '</td><td class="num">' . $xlsNum($row['debit_total']) . '</td><td class="num">' . $xlsNum($row['credit_total']) . '</td></tr>'; }
    echo '</table>';

    echo '<h2>Restaurant Revenue / Covers</h2><table><tr><th>Meal Period</th><th>Checks</th><th>Covers</th><th>Revenue</th><th>Average Spend per Cover</th></tr>';
    foreach ($restaurantRows as $row) { $covers=(int)($row['covers']??0); $rev=(float)($row['revenue']??0); echo '<tr><td>' . $xlsText($row['meal_period']) . '</td><td class="num">' . (int)$row['checks_count'] . '</td><td class="num">' . $covers . '</td><td class="num">' . $xlsNum($rev) . '</td><td class="num">' . $xlsNum($covers>0?$rev/$covers:0) . '</td></tr>'; }
    echo '</table>';

    echo '<h2>Room Type Snapshot</h2><table><tr><th>Property</th><th>Room Type</th><th>Rooms</th><th>Occupied</th><th>Vacant</th><th>Occupancy %</th></tr>';
    foreach ($roomTypeSnapshotRows as $row) { echo '<tr><td>' . $xlsText($row['property_name']) . '</td><td>' . $xlsText($row['room_type']) . '</td><td class="num">' . (int)$row['rooms_count'] . '</td><td class="num">' . (int)$row['occupied_rooms'] . '</td><td class="num">' . (int)$row['vacant_rooms'] . '</td><td class="num">' . $xlsNum($row['occupancy_percent']) . '%</td></tr>'; }
    echo '</table>';

    $sections = ['Arrivals' => $arrivals, 'Departures' => $departures, 'In-House Guests' => $inhouse, 'Outstanding Alerts' => $outstandingAlerts];
    foreach ($sections as $title => $rows) {
        echo '<h2>' . $xlsText($title) . '</h2><table><tr><th>Booking</th><th>Guest</th><th>Agent</th><th>Property</th><th>Stay</th><th>Balance</th></tr>';
        foreach ($rows as $row) { echo '<tr><td>' . $xlsText($row['order_no'] ?: '#' . $row['id']) . '</td><td>' . $xlsText(trim((string)$row['guest_name']) ?: 'Guest') . '</td><td>' . $xlsText(trim((string)$row['agent_name']) ?: 'Direct') . '</td><td>' . $xlsText($row['property_name'] ?? '') . '</td><td>' . $xlsText(($row['check_in'] ?? '') . ' to ' . ($row['check_out'] ?? '')) . '</td><td class="num">' . $xlsNum($row['balance'] ?? 0) . '</td></tr>'; }
        echo '</table>';
    }

    echo '</body></html>';
    exit;
}

if (isset($_GET['export']) && $_GET['export'] === 'csv' && !$errors) {
    $filename = 'daily_manager_report_' . $reportDate . '.csv';

    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');

    $out = fopen('php://output', 'w');

    fputcsv($out, ['Daily Manager Report', $reportDate]);
    fputcsv($out, []);
    fputcsv($out, ['Metric', 'Value']);
    foreach ($summary as $key => $value) {
        fputcsv($out, [ucwords(str_replace('_', ' ', $key)), $value]);
    }

    fputcsv($out, []);
    fputcsv($out, ['Payments by Method']);
    fputcsv($out, ['Method', 'Count', 'Active Total', 'Voided Total']);
    foreach ($paymentMethodRows as $row) {
        fputcsv($out, [$row['payment_method'], $row['payment_count'], $row['active_total'], $row['voided_total']]);
    }

    fputcsv($out, []);
    fputcsv($out, ['Transaction Types']);
    fputcsv($out, ['Type', 'Count', 'Debit', 'Credit']);
    foreach ($revenueRows as $row) {
        fputcsv($out, [$row['tx_type'], $row['tx_count'], $row['debit_total'], $row['credit_total']]);
    }

    fputcsv($out, []);
    fputcsv($out, ['Restaurant Revenue / Covers']);
    fputcsv($out, ['Meal Period', 'Checks', 'Covers', 'Revenue']);
    foreach ($restaurantRows as $row) {
        fputcsv($out, [$row['meal_period'], $row['checks_count'], $row['covers'], $row['revenue']]);
    }

    fputcsv($out, []);
    fputcsv($out, ['Room Type Snapshot']);
    fputcsv($out, ['Property', 'Room Type', 'Rooms', 'Occupied', 'Vacant', 'Occupancy %']);
    foreach ($roomTypeSnapshotRows as $row) {
        fputcsv($out, [$row['property_name'], $row['room_type'], $row['rooms_count'], $row['occupied_rooms'], $row['vacant_rooms'], number_format((float)$row['occupancy_percent'],2,'.','')]);
    }

    foreach (['Arrivals' => $arrivals, 'Departures' => $departures, 'In-House Guests' => $inhouse, 'Outstanding Alerts' => $outstandingAlerts] as $section => $rows) {
        fputcsv($out, []);
        fputcsv($out, [$section]);
        fputcsv($out, ['Booking', 'Guest', 'Agent', 'Property', 'Stay', 'Balance']);
        foreach ($rows as $row) {
            fputcsv($out, [$row['order_no'] ?: '#' . $row['id'], $row['guest_name'], $row['agent_name'], $row['property_name'] ?? '', ($row['check_in'] ?? '') . ' to ' . ($row['check_out'] ?? ''), $row['balance'] ?? 0]);
        }
    }

    fclose($out);
    exit;
}

require_once __DIR__ . '/../../includes/header.php';
?>
<style>
    .hero-band.dm-compact-hero{padding:14px 18px!important;margin-bottom:14px!important;border-radius:16px!important;}
    .dm-compact-hero .hero-title{font-size:1.35rem!important;line-height:1.15!important;margin-top:.15rem!important;}
    .dm-compact-hero .hero-subtitle{font-size:.86rem!important;margin-top:.25rem!important;}
    .dm-compact-hero .page-eyebrow{font-size:.72rem!important;letter-spacing:.08em;text-transform:uppercase;}
    .dm-bars .bar-row{display:grid;grid-template-columns:160px 1fr 110px;gap:.75rem;align-items:center;margin:.45rem 0;}
    .dm-bars .bar-track{height:12px;border-radius:999px;background:#e5e7eb;overflow:hidden;}
    .dm-bars .bar-fill{height:12px;border-radius:999px;background:linear-gradient(90deg,#1d4ed8,#38bdf8);}
    .dm-scroll-table{max-height:420px;overflow:auto;}
    .dm-scroll-table thead th{position:sticky;top:0;z-index:3;background:#f8fafc;box-shadow:inset 0 -1px 0 rgba(15,23,42,.12),0 2px 4px rgba(15,23,42,.06);}
</style>

<div class="hero-band dm-compact-hero">
    <div class="row g-4 align-items-center">
        <div class="col-lg-8">
            <div class="page-eyebrow text-white-50">
                <i class="bi bi-moon-stars"></i> Night Audit & Daily Control
            </div>
            <div class="hero-title mt-2">Daily Manager Report</div>
            <div class="hero-subtitle mt-2">
                Revenue, payments, arrivals, departures, occupancy, cash reconciliation and outstanding alerts for the selected day.
            </div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <div class="d-flex flex-wrap gap-2 justify-content-lg-end">
                <a href="daily_manager_report.php?<?php echo h(http_build_query(array_merge($_GET, ['export' => 'xls']))); ?>" class="btn btn-light">
                    <i class="bi bi-file-earmark-excel me-2"></i>Full Excel
                </a>
                <a href="daily_manager_report.php?<?php echo h(http_build_query(array_merge($_GET, ['export' => 'csv']))); ?>" class="btn btn-outline-light">
                    <i class="bi bi-download me-2"></i>CSV
                </a>
                <button type="button" onclick="window.print()" class="btn btn-outline-light">
                    <i class="bi bi-printer me-2"></i>Print / PDF
                </button>
            </div>
        </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="card panel-card mb-4">
    <div class="card-body">
        <form method="get" class="row g-3 align-items-end">
            <div class="col-md-3">
                <label class="form-label">Report Date</label>
                <input type="date" name="report_date" class="form-control" value="<?php echo h($reportDate); ?>">
            </div>

            <div class="col-md-5">
                <label class="form-label">Property</label>
                <select name="property_id" class="form-select">
                    <option value="0">All properties</option>
                    <?php foreach ($properties as $property): ?>
                        <option value="<?php echo (int)$property['id']; ?>" <?php echo $selectedPropertyId === (int)$property['id'] ? 'selected' : ''; ?>>
                            <?php echo h($property['name']); ?> - <?php echo h($property['code']); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>

            <div class="col-md-4 d-grid d-md-flex justify-content-md-end">
                <button class="btn btn-primary">
                    <i class="bi bi-funnel me-2"></i>Run Report
                </button>
            </div>
        </form>
    </div>
</div>

<div class="row g-4 mb-4">
    <div class="col-md-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-success-subtle text-success"><i class="bi bi-cash-coin"></i></div>
            <div>
                <div class="metric-label">Revenue</div>
                <div class="metric-value"><?php echo money_fmt($summary['total_revenue']); ?></div>
            </div>
        </div>
    </div>
    <div class="col-md-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-primary-subtle text-primary"><i class="bi bi-cash-stack"></i></div>
            <div>
                <div class="metric-label">Payments</div>
                <div class="metric-value"><?php echo money_fmt($summary['payments_total']); ?></div>
            </div>
        </div>
    </div>
    <div class="col-md-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-warning-subtle text-warning"><i class="bi bi-box-arrow-in-right"></i></div>
            <div>
                <div class="metric-label">Arrivals</div>
                <div class="metric-value"><?php echo (int)$summary['arrivals']; ?></div>
            </div>
        </div>
    </div>
    <div class="col-md-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-info-subtle text-info"><i class="bi bi-box-arrow-right"></i></div>
            <div>
                <div class="metric-label">Departures</div>
                <div class="metric-value"><?php echo (int)$summary['departures']; ?></div>
            </div>
        </div>
    </div>
    <div class="col-md-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-danger-subtle text-danger"><i class="bi bi-percent"></i></div>
            <div>
                <div class="metric-label">Occupancy</div>
                <div class="metric-value"><?php echo money_fmt($summary['occupancy_percent']); ?>%</div>
            </div>
        </div>
    </div>
    <div class="col-md-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-secondary-subtle text-secondary"><i class="bi bi-exclamation-circle"></i></div>
            <div>
                <div class="metric-label">Outstanding</div>
                <div class="metric-value"><?php echo money_fmt($summary['outstanding_total']); ?></div>
            </div>
        </div>
    </div>
</div>

<div class="row g-4 mb-4">
    <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-icon bg-success-subtle text-success"><i class="bi bi-building"></i></div><div><div class="metric-label">ADR</div><div class="metric-value"><?php echo money_fmt($summary['adr']); ?></div></div></div></div>
    <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-icon bg-info-subtle text-info"><i class="bi bi-graph-up-arrow"></i></div><div><div class="metric-label">RevPAR</div><div class="metric-value"><?php echo money_fmt($summary['revpar']); ?></div></div></div></div>
    <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-icon bg-warning-subtle text-warning"><i class="bi bi-people-fill"></i></div><div><div class="metric-label">In-House Pax</div><div class="metric-value"><?php echo (int)$summary['inhouse_pax']; ?></div></div></div></div>
    <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-icon bg-dark-subtle text-dark"><i class="bi bi-cup-hot"></i></div><div><div class="metric-label">Restaurant</div><div class="metric-value"><?php echo money_fmt($summary['restaurant_revenue']); ?></div><div class="small text-muted"><?php echo (int)$summary['restaurant_covers']; ?> covers</div></div></div></div>
    <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-icon bg-danger-subtle text-danger"><i class="bi bi-exclamation-triangle"></i></div><div><div class="metric-label">Departure Bal.</div><div class="metric-value"><?php echo money_fmt($summary['departure_balance']); ?></div></div></div></div>
    <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-icon bg-secondary-subtle text-secondary"><i class="bi bi-arrow-left-right"></i></div><div><div class="metric-label">Cash Variance</div><div class="metric-value"><?php echo money_fmt($summary['cash_variance']); ?></div></div></div></div>
</div>

<div class="row g-4 mb-4">
    <div class="col-xl-7">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Graphical Revenue Analysis</h5></div>
            <div class="card-body dm-bars">
                <?php $maxManagerChart = 0.0; foreach ($managerChartRows as $cr) { $maxManagerChart = max($maxManagerChart, (float)$cr['amount']); } ?>
                <?php foreach ($managerChartRows as $cr): ?>
                    <?php $pct = $maxManagerChart > 0 ? min(100, ((float)$cr['amount'] / $maxManagerChart) * 100) : 0; ?>
                    <div class="bar-row">
                        <div class="small fw-bold"><?php echo h($cr['label']); ?></div>
                        <div class="bar-track"><div class="bar-fill" style="width:<?php echo h(number_format($pct, 2, '.', '')); ?>%"></div></div>
                        <div class="text-end fw-bold"><?php echo money_fmt($cr['amount']); ?></div>
                    </div>
                <?php endforeach; ?>
            </div>
        </div>
    </div>
    <div class="col-xl-5">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Restaurant Covers & Revenue</h5></div>
            <div class="card-body p-0">
                <table class="table table-modern align-middle mb-0">
                    <thead><tr><th>Period</th><th class="text-end">Checks</th><th class="text-end">Covers</th><th class="text-end">Revenue</th></tr></thead>
                    <tbody>
                    <?php if (!$restaurantRows): ?>
                        <tr><td colspan="4" class="text-center text-muted py-4">No restaurant sales found.</td></tr>
                    <?php else: foreach ($restaurantRows as $row): ?>
                        <tr><td><strong><?php echo h($row['meal_period']); ?></strong></td><td class="text-end"><?php echo (int)$row['checks_count']; ?></td><td class="text-end"><?php echo (int)$row['covers']; ?></td><td class="text-end fw-bold"><?php echo money_fmt($row['revenue']); ?></td></tr>
                    <?php endforeach; endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<div class="row g-4">
    <div class="col-xl-4">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Revenue Breakdown</h5></div>
            <div class="card-body">
                <div class="mini-stat mb-3"><span>Room Revenue</span><strong><?php echo money_fmt($summary['room_revenue']); ?></strong></div>
                <div class="mini-stat mb-3"><span>Extras Revenue</span><strong><?php echo money_fmt($summary['extras_revenue']); ?></strong></div>
                <div class="mini-stat mb-3"><span>Total Revenue</span><strong><?php echo money_fmt($summary['total_revenue']); ?></strong></div>
                <div class="mini-stat mb-3"><span>Occupied / Available Rooms</span><strong><?php echo (int)$summary['occupied_rooms']; ?> / <?php echo (int)$summary['available_rooms']; ?></strong></div>
                <div class="mini-stat mb-0"><span>Vacant Rooms</span><strong><?php echo (int)$summary['vacant_rooms']; ?></strong></div>
            </div>
        </div>
    </div>

    <div class="col-xl-4">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Cash Reconciliation</h5></div>
            <div class="card-body p-0">
                <table class="table table-modern align-middle mb-0">
                    <thead>
                    <tr>
                        <th>Method</th>
                        <th class="text-end">Count</th>
                        <th class="text-end">Active</th>
                        <th class="text-end">Voided</th>
                    </tr>
                    </thead>
                    <tbody>
                    <?php if (!$paymentMethodRows): ?>
                        <tr><td colspan="4" class="text-center text-muted py-4">No payments found.</td></tr>
                    <?php else: ?>
                        <?php foreach ($paymentMethodRows as $row): ?>
                            <tr>
                                <td><strong><?php echo h($row['payment_method']); ?></strong></td>
                                <td class="text-end"><?php echo (int)$row['payment_count']; ?></td>
                                <td class="text-end fw-bold"><?php echo money_fmt($row['active_total']); ?></td>
                                <td class="text-end"><?php echo money_fmt($row['voided_total']); ?></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                    </tbody>
                    <tfoot>
                    <tr>
                        <th colspan="2">Total</th>
                        <th class="text-end"><?php echo money_fmt($summary['payments_total']); ?></th>
                        <th class="text-end"><?php echo money_fmt($summary['voided_payments']); ?></th>
                    </tr>
                    </tfoot>
                </table>
            </div>
        </div>
    </div>

    <div class="col-xl-4">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Transaction Types</h5></div>
            <div class="card-body p-0">
                <table class="table table-modern align-middle mb-0">
                    <thead>
                    <tr>
                        <th>Type</th>
                        <th class="text-end">Count</th>
                        <th class="text-end">Debit</th>
                        <th class="text-end">Credit</th>
                    </tr>
                    </thead>
                    <tbody>
                    <?php if (!$revenueRows): ?>
                        <tr><td colspan="4" class="text-center text-muted py-4">No transactions found.</td></tr>
                    <?php else: ?>
                        <?php foreach ($revenueRows as $row): ?>
                            <tr>
                                <td><?php echo h(ucwords(str_replace('_', ' ', (string)$row['tx_type']))); ?></td>
                                <td class="text-end"><?php echo (int)$row['tx_count']; ?></td>
                                <td class="text-end fw-bold"><?php echo money_fmt($row['debit_total']); ?></td>
                                <td class="text-end"><?php echo money_fmt($row['credit_total']); ?></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>


    <div class="col-12">
        <div class="card panel-card">
            <div class="card-header"><h5 class="mb-0">Room Type Snapshot</h5></div>
            <div class="card-body p-0 dm-scroll-table">
                <table class="table table-modern align-middle mb-0">
                    <thead><tr><th>Property</th><th>Room Type</th><th class="text-end">Rooms</th><th class="text-end">Occupied</th><th class="text-end">Vacant</th><th class="text-end">Occ %</th></tr></thead>
                    <tbody>
                    <?php if (!$roomTypeSnapshotRows): ?>
                        <tr><td colspan="6" class="text-center text-muted py-4">No room type snapshot found.</td></tr>
                    <?php else: foreach ($roomTypeSnapshotRows as $row): ?>
                        <tr><td><?php echo h($row['property_name']); ?></td><td><strong><?php echo h($row['room_type']); ?></strong></td><td class="text-end"><?php echo (int)$row['rooms_count']; ?></td><td class="text-end fw-bold"><?php echo (int)$row['occupied_rooms']; ?></td><td class="text-end"><?php echo (int)$row['vacant_rooms']; ?></td><td class="text-end fw-bold"><?php echo money_fmt($row['occupancy_percent']); ?>%</td></tr>
                    <?php endforeach; endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <div class="col-xl-6">
        <div class="card panel-card">
            <div class="card-header"><h5 class="mb-0">Arrivals</h5></div>
            <div class="card-body p-0">
                <table class="table table-modern align-middle mb-0">
                    <thead>
                    <tr>
                        <th>Booking</th>
                        <th>Guest</th>
                        <th>Agent</th>
                        <th class="text-end">Balance</th>
                    </tr>
                    </thead>
                    <tbody>
                    <?php if (!$arrivals): ?>
                        <tr><td colspan="4" class="text-center text-muted py-4">No arrivals.</td></tr>
                    <?php else: ?>
                        <?php foreach ($arrivals as $row): ?>
                            <tr>
                                <td><a href="/rooms/modules/reservations/reservation_view.php?order_id=<?php echo (int)$row['id']; ?>"><?php echo h($row['order_no'] ?: '#' . $row['id']); ?></a></td>
                                <td><?php echo h(trim((string)$row['guest_name']) ?: 'Guest'); ?></td>
                                <td><?php echo h(trim((string)$row['agent_name']) ?: 'Direct'); ?></td>
                                <td class="text-end fw-bold"><?php echo money_fmt($row['balance']); ?></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <div class="col-xl-6">
        <div class="card panel-card">
            <div class="card-header"><h5 class="mb-0">Departures</h5></div>
            <div class="card-body p-0">
                <table class="table table-modern align-middle mb-0">
                    <thead>
                    <tr>
                        <th>Booking</th>
                        <th>Guest</th>
                        <th>Agent</th>
                        <th class="text-end">Balance</th>
                    </tr>
                    </thead>
                    <tbody>
                    <?php if (!$departures): ?>
                        <tr><td colspan="4" class="text-center text-muted py-4">No departures.</td></tr>
                    <?php else: ?>
                        <?php foreach ($departures as $row): ?>
                            <tr>
                                <td><a href="/rooms/modules/reservations/reservation_view.php?order_id=<?php echo (int)$row['id']; ?>"><?php echo h($row['order_no'] ?: '#' . $row['id']); ?></a></td>
                                <td><?php echo h(trim((string)$row['guest_name']) ?: 'Guest'); ?></td>
                                <td><?php echo h(trim((string)$row['agent_name']) ?: 'Direct'); ?></td>
                                <td class="text-end fw-bold <?php echo (float)$row['balance'] > 0 ? 'text-danger' : ''; ?>"><?php echo money_fmt($row['balance']); ?></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <div class="col-12">
        <div class="card panel-card">
            <div class="card-header"><h5 class="mb-0">Outstanding Alerts - Top 20</h5></div>
            <div class="card-body p-0">
                <table class="table table-modern align-middle mb-0">
                    <thead>
                    <tr>
                        <th>Booking</th>
                        <th>Guest</th>
                        <th>Agent</th>
                        <th>Property</th>
                        <th>Stay</th>
                        <th class="text-end">Balance</th>
                    </tr>
                    </thead>
                    <tbody>
                    <?php if (!$outstandingAlerts): ?>
                        <tr><td colspan="6" class="text-center text-muted py-4">No outstanding alerts.</td></tr>
                    <?php else: ?>
                        <?php foreach ($outstandingAlerts as $row): ?>
                            <tr>
                                <td><a href="/rooms/modules/payments/payments.php?order_id=<?php echo (int)$row['id']; ?>"><?php echo h($row['order_no'] ?: '#' . $row['id']); ?></a></td>
                                <td><?php echo h(trim((string)$row['guest_name']) ?: 'Guest'); ?></td>
                                <td><?php echo h(trim((string)$row['agent_name']) ?: 'Direct'); ?></td>
                                <td><?php echo h($row['property_name']); ?></td>
                                <td><?php echo h($row['check_in']); ?> to <?php echo h($row['check_out']); ?></td>
                                <td class="text-end fw-bold text-danger"><?php echo money_fmt($row['balance']); ?></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

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