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

$pageTitle = 'Operations Alerts';

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('alerts_table_exists')) {
    function alerts_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('alerts_column_exists')) {
    function alerts_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 = [];

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

if ($selectedDate === '') {
    $selectedDate = $today;
}

if (strtotime($selectedDate) === false) {
    $errors[] = 'Invalid alert date selected.';
}

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

$hasOrderStatus = !$errors && alerts_column_exists($pdo, 'orders', 'status');
$hasOrderCancelled = !$errors && alerts_column_exists($pdo, 'orders', 'cancelled');
$hasRoomingCancelled = !$errors && alerts_column_exists($pdo, 'order_rooming', 'cancelled');
$hasRoomingProperty = !$errors && alerts_column_exists($pdo, 'order_rooming', 'property_id');
$hasRoomActive = !$errors && alerts_column_exists($pdo, 'rooms', 'is_active');
$hasRoomStatus = !$errors && alerts_column_exists($pdo, 'rooms', 'status');

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

$dateSql = $pdo->quote($selectedDate);
$tomorrowSql = $pdo->quote(date('Y-m-d', strtotime($selectedDate . ' +1 day')));
$weekAheadSql = $pdo->quote(date('Y-m-d', strtotime($selectedDate . ' +7 days')));

$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 && $hasRoomingCancelled) {
    $roomingActiveSql = " AND COALESCE(orm.cancelled, 0) = 0 ";
}

$propertyFilterOrders = '';
$orderParams = [];

if ($selectedPropertyId > 0) {
    $propertyFilterOrders = " AND o.property_id = :property_id ";
    $orderParams[':property_id'] = $selectedPropertyId;
}

$propertyFilterRooming = '';
$roomingParams = [];

if ($selectedPropertyId > 0) {
    if ($hasRoomingProperty) {
        $propertyFilterRooming = " AND COALESCE(orm.property_id, o.property_id, r.property_id, rt.property_id) = :property_id ";
    } else {
        $propertyFilterRooming = " AND COALESCE(o.property_id, r.property_id, rt.property_id) = :property_id ";
    }
    $roomingParams[':property_id'] = $selectedPropertyId;
}

$unpaidDepartures = [];
$arrivalsToday = [];
$inHouseBalances = [];
$unassignedRooms = [];
$overbookingRisks = [];
$agentOutstanding = [];
$longStayNoPayment = [];
$roomConflicts = [];
$forecastAlertPack = ['critical' => [], 'warning' => [], 'info' => [], 'counts' => ['critical' => 0, 'warning' => 0, 'info' => 0, 'total' => 0]];
$forecastCriticalAlerts = [];
$forecastWarningAlerts = [];
$forecastAllAlerts = [];

$counts = [
    'critical' => 0,
    'warning' => 0,
    'info' => 0,
    'total' => 0,
];

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            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 DATE(o.check_out) = $dateSql
              $propertyFilterOrders
              $orderActiveSql
            GROUP BY o.id, o.order_no, o.check_in, o.check_out, property_name, property_code, guest_name, agent_name
            HAVING balance > 0.00001
            ORDER BY balance DESC, o.check_out ASC
            LIMIT 50
        ");
        $stmt->execute($orderParams);
        $unpaidDepartures = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $counts['critical'] += count($unpaidDepartures);
    } catch (Throwable $e) {
        $errors[] = 'Could not load unpaid departures: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            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 DATE(o.check_in) = $dateSql
              $propertyFilterOrders
              $orderActiveSql
            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
            LIMIT 80
        ");
        $stmt->execute($orderParams);
        $arrivalsToday = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $counts['info'] += count($arrivalsToday);
    } catch (Throwable $e) {
        $errors[] = 'Could not load arrivals: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            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 o.check_in <= $dateSql
              AND o.check_out > $dateSql
              $propertyFilterOrders
              $orderActiveSql
            GROUP BY o.id, o.order_no, o.check_in, o.check_out, property_name, property_code, guest_name, agent_name
            HAVING balance > 0.00001
            ORDER BY balance DESC
            LIMIT 80
        ");
        $stmt->execute($orderParams);
        $inHouseBalances = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $counts['warning'] += count($inHouseBalances);
    } catch (Throwable $e) {
        $errors[] = 'Could not load in-house balances: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT
                orm.id AS rooming_id,
                orm.order_id,
                orm.check_in,
                orm.check_out,
                rt.title AS room_type,
                p.name AS property_name,
                p.code AS property_code,
                o.order_no,
                TRIM(CONCAT(COALESCE(g.firstname,''), ' ', COALESCE(g.lastname,''))) AS guest_name,
                TRIM(CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,''))) AS agent_name
            FROM order_rooming orm
            LEFT JOIN orders o ON o.id = orm.order_id
            LEFT JOIN guests g ON g.id = o.guest_id
            LEFT JOIN agents a ON a.id = o.agent_id
            LEFT JOIN room_types rt ON rt.id = orm.room_type_id
            LEFT JOIN rooms r ON r.id = orm.room_id
            LEFT JOIN properties p ON p.id = " . ($hasRoomingProperty ? "COALESCE(orm.property_id, o.property_id, r.property_id, rt.property_id)" : "COALESCE(o.property_id, r.property_id, rt.property_id)") . "
            WHERE orm.check_in <= $weekAheadSql
              AND orm.check_out > $dateSql
              AND (orm.room_id IS NULL OR orm.room_id = 0)
              $roomingActiveSql
              $propertyFilterRooming
              $orderActiveSql
            ORDER BY orm.check_in ASC, p.name ASC, rt.title ASC
            LIMIT 100
        ");
        $stmt->execute($roomingParams);
        $unassignedRooms = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $counts['warning'] += count($unassignedRooms);
    } catch (Throwable $e) {
        $errors[] = 'Could not load unassigned rooms: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $roomFilter = '';
        $roomParams = [];
        if ($selectedPropertyId > 0) {
            $roomFilter = " AND COALESCE(r.property_id, rt.property_id) = :property_id ";
            $roomParams[':property_id'] = $selectedPropertyId;
        }

        $activeRoomSql = '';
        if ($hasRoomActive) {
            $activeRoomSql .= " AND COALESCE(r.is_active, 1) = 1 ";
        }
        if ($hasRoomStatus) {
            $activeRoomSql .= " AND (r.status IS NULL OR r.status NOT IN ('inactive','out_of_order','blocked')) ";
        }

        $stmtTypes = $pdo->prepare("
            SELECT
                rt.id AS room_type_id,
                rt.title AS room_type,
                COALESCE(p.name, 'No Property') AS property_name,
                COALESCE(p.id, 0) AS property_id,
                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 1=1
              $roomFilter
              $activeRoomSql
            GROUP BY rt.id, rt.title, property_name, property_id
            HAVING rooms_count > 0
            ORDER BY property_name ASC, rt.title ASC
        ");
        $stmtTypes->execute($roomParams);
        $roomTypeCapacity = $stmtTypes->fetchAll(PDO::FETCH_ASSOC);

        foreach ($roomTypeCapacity as $type) {
            $params = [
                ':room_type_id' => (int)$type['room_type_id'],
            ];

            $where = "
                orm.room_type_id = :room_type_id
                AND orm.check_in <= $dateSql
                AND orm.check_out > $dateSql
                $roomingActiveSql
                $orderActiveSql
            ";

            if ((int)$type['property_id'] > 0) {
                if ($hasRoomingProperty) {
                    $where .= " AND COALESCE(orm.property_id, o.property_id) = :property_id ";
                } else {
                    $where .= " AND o.property_id = :property_id ";
                }
                $params[':property_id'] = (int)$type['property_id'];
            }

            $stmtOcc = $pdo->prepare("
                SELECT COUNT(DISTINCT orm.id)
                FROM order_rooming orm
                LEFT JOIN orders o ON o.id = orm.order_id
                WHERE $where
            ");
            $stmtOcc->execute($params);
            $occupied = (int)($stmtOcc->fetchColumn() ?: 0);
            $capacity = (int)$type['rooms_count'];

            if ($occupied > $capacity) {
                $overbookingRisks[] = [
                    'property_name' => $type['property_name'],
                    'room_type' => $type['room_type'],
                    'capacity' => $capacity,
                    'occupied' => $occupied,
                    'over_by' => $occupied - $capacity,
                ];
            }
        }

        $counts['critical'] += count($overbookingRisks);
    } catch (Throwable $e) {
        $overbookingRisks = [];
    }
}

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT
                COALESCE(a.id, 0) AS agent_id,
                COALESCE(NULLIF(TRIM(CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,''))), ''), 'Direct / No Agent') AS agent_name,
                COUNT(DISTINCT o.id) AS booking_count,
                COALESCE(SUM(f.balance), 0) AS balance
            FROM folios f
            INNER JOIN orders o ON o.id = f.order_id
            LEFT JOIN agents a ON a.id = o.agent_id
            WHERE f.balance > 0.00001
              $propertyFilterOrders
            GROUP BY agent_id, agent_name
            HAVING balance > 0.00001
            ORDER BY balance DESC
            LIMIT 10
        ");
        $stmt->execute($orderParams);
        $agentOutstanding = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $counts['warning'] += count($agentOutstanding);
    } catch (Throwable $e) {
        $agentOutstanding = [];
    }
}

if (!$errors && alerts_table_exists($pdo, 'payment')) {
    try {
        $stmt = $pdo->prepare("
            SELECT
                o.id,
                o.order_no,
                o.check_in,
                o.check_out,
                DATEDIFF($dateSql, o.check_in) AS days_in_house,
                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
            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
            WHERE o.check_in <= $dateSql
              AND o.check_out > $dateSql
              AND DATEDIFF($dateSql, o.check_in) >= 3
              $propertyFilterOrders
              $orderActiveSql
              AND NOT EXISTS (
                  SELECT 1
                  FROM payment pmt
                  WHERE pmt.order_id = o.id
                    AND COALESCE(pmt.amount,0) > 0
              )
            ORDER BY days_in_house DESC
            LIMIT 50
        ");
        $stmt->execute($orderParams);
        $longStayNoPayment = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $counts['warning'] += count($longStayNoPayment);
    } catch (Throwable $e) {
        $longStayNoPayment = [];
    }
}


if (!$errors && function_exists('get_forecast_alerts')) {
    try {
        $forecastAlertPack = get_forecast_alerts($pdo, $selectedPropertyId, 14);
        $forecastCriticalAlerts = $forecastAlertPack['critical'] ?? [];
        $forecastWarningAlerts = $forecastAlertPack['warning'] ?? [];
        $forecastAllAlerts = array_merge($forecastCriticalAlerts, $forecastWarningAlerts);

        $counts['critical'] += count($forecastCriticalAlerts);
        $counts['warning'] += count($forecastWarningAlerts);
    } catch (Throwable $e) {
        $errors[] = 'Could not load forecast alerts: ' . $e->getMessage();
    }
}

$counts['total'] = $counts['critical'] + $counts['warning'] + $counts['info'];

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-shield-exclamation"></i> Operations Command Center
            </div>
            <div class="hero-title mt-2">Operations Alerts</div>
            <div class="hero-subtitle mt-2">
                Daily operational exceptions: unpaid departures, arrivals, in-house balances, unassigned rooms, overbooking risk, debtors and forecast inventory risks.
            </div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <a href="daily_manager_report.php?report_date=<?php echo h($selectedDate); ?><?php echo $selectedPropertyId > 0 ? '&property_id=' . (int)$selectedPropertyId : ''; ?>" class="btn btn-light me-2">
                <i class="bi bi-moon-stars me-2"></i>Daily Report
            </a>
            <a href="forecast_report.php" class="btn btn-outline-light me-2">
                <i class="bi bi-calendar-range me-2"></i>Forecast
            </a>
            <a href="index.php" class="btn btn-outline-light">
                <i class="bi bi-grid me-2"></i>Reports
            </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="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">Alert Date</label>
                <input type="date" name="date" class="form-control" value="<?php echo h($selectedDate); ?>">
            </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>Refresh Alerts
                </button>
            </div>
        </form>
    </div>
</div>

<div class="row g-4 mb-4">
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-danger-subtle text-danger"><i class="bi bi-exclamation-octagon"></i></div>
            <div>
                <div class="metric-label">Critical</div>
                <div class="metric-value"><?php echo (int)$counts['critical']; ?></div>
            </div>
        </div>
    </div>
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-warning-subtle text-warning"><i class="bi bi-exclamation-triangle"></i></div>
            <div>
                <div class="metric-label">Warnings</div>
                <div class="metric-value"><?php echo (int)$counts['warning']; ?></div>
            </div>
        </div>
    </div>
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-info-subtle text-info"><i class="bi bi-info-circle"></i></div>
            <div>
                <div class="metric-label">Information</div>
                <div class="metric-value"><?php echo (int)$counts['info']; ?></div>
            </div>
        </div>
    </div>
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-primary-subtle text-primary"><i class="bi bi-list-check"></i></div>
            <div>
                <div class="metric-label">Total Alerts</div>
                <div class="metric-value"><?php echo (int)$counts['total']; ?></div>
            </div>
        </div>
    </div>
</div>

<div class="row g-4">

    <div class="col-12">
        <div class="card panel-card alert-panel alert-critical">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h5 class="mb-0"><i class="bi bi-calendar-range me-2"></i>Forecast Alerts - Next 14 Days</h5>
                <span>
                    <span class="badge text-bg-danger me-1"><?php echo count($forecastCriticalAlerts); ?> Critical</span>
                    <span class="badge text-bg-warning"><?php echo count($forecastWarningAlerts); ?> Warning</span>
                </span>
            </div>
            <div class="card-body p-0">
                <table class="table table-modern align-middle mb-0">
                    <thead>
                    <tr>
                        <th>Date</th>
                        <th>Day</th>
                        <th>Alert</th>
                        <th class="text-end">Booked</th>
                        <th class="text-end">Rooms Left</th>
                        <th class="text-end">Occupancy</th>
                        <th class="text-end">Action</th>
                    </tr>
                    </thead>
                    <tbody>
                    <?php if (!$forecastAllAlerts): ?>
                        <tr><td colspan="7" class="text-center text-muted py-4">No forecast alerts for the next 14 days.</td></tr>
                    <?php else: ?>
                        <?php foreach ($forecastAllAlerts as $alert): ?>
                            <tr class="<?php echo ($alert['severity'] ?? '') === 'critical' ? 'table-danger' : 'table-warning'; ?>">
                                <td><strong><?php echo h($alert['date'] ?? ''); ?></strong></td>
                                <td><?php echo h($alert['day_name'] ?? ''); ?></td>
                                <td>
                                    <strong><?php echo h($alert['title'] ?? 'Forecast Alert'); ?></strong>
                                    <div class="small text-muted"><?php echo h($alert['message'] ?? ''); ?></div>
                                </td>
                                <td class="text-end"><?php echo (int)($alert['booked_rooms'] ?? 0); ?> / <?php echo (int)($alert['available_rooms'] ?? 0); ?></td>
                                <td class="text-end fw-bold <?php echo (int)($alert['rooms_left'] ?? 0) < 5 ? 'text-danger' : ''; ?>">
                                    <?php echo (int)($alert['rooms_left'] ?? 0); ?>
                                </td>
                                <td class="text-end"><?php echo number_format((float)($alert['occupancy_percent'] ?? 0), 2); ?>%</td>
                                <td class="text-end">
                                    <a href="<?php echo h($alert['url'] ?? 'forecast_report.php'); ?>" class="btn btn-sm <?php echo ($alert['severity'] ?? '') === 'critical' ? 'btn-outline-danger' : 'btn-outline-warning'; ?>">
                                        View Forecast
                                    </a>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>


    <div class="col-xl-6">
        <div class="card panel-card alert-panel alert-critical h-100">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h5 class="mb-0"><i class="bi bi-exclamation-octagon me-2"></i>Unpaid Departures</h5>
                <span class="badge text-bg-danger"><?php echo count($unpaidDepartures); ?></span>
            </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 (!$unpaidDepartures): ?>
                        <tr><td colspan="4" class="text-center text-muted py-4">No unpaid departures.</td></tr>
                    <?php else: ?>
                        <?php foreach ($unpaidDepartures 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 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 class="col-xl-6">
        <div class="card panel-card alert-panel alert-critical h-100">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h5 class="mb-0"><i class="bi bi-layers me-2"></i>Overbooking Risk</h5>
                <span class="badge text-bg-danger"><?php echo count($overbookingRisks); ?></span>
            </div>
            <div class="card-body p-0">
                <table class="table table-modern align-middle mb-0">
                    <thead>
                    <tr>
                        <th>Property</th>
                        <th>Room Type</th>
                        <th class="text-end">Capacity</th>
                        <th class="text-end">Booked</th>
                        <th class="text-end">Over</th>
                    </tr>
                    </thead>
                    <tbody>
                    <?php if (!$overbookingRisks): ?>
                        <tr><td colspan="5" class="text-center text-muted py-4">No overbooking risk detected.</td></tr>
                    <?php else: ?>
                        <?php foreach ($overbookingRisks 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['capacity']; ?></td>
                                <td class="text-end fw-bold"><?php echo (int)$row['occupied']; ?></td>
                                <td class="text-end fw-bold text-danger"><?php echo (int)$row['over_by']; ?></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <div class="col-xl-6">
        <div class="card panel-card alert-panel alert-warning h-100">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h5 class="mb-0"><i class="bi bi-door-closed me-2"></i>Unassigned Rooms</h5>
                <span class="badge text-bg-warning"><?php echo count($unassignedRooms); ?></span>
            </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>Room Type</th>
                        <th>Stay</th>
                    </tr>
                    </thead>
                    <tbody>
                    <?php if (!$unassignedRooms): ?>
                        <tr><td colspan="4" class="text-center text-muted py-4">No unassigned rooms in next 7 days.</td></tr>
                    <?php else: ?>
                        <?php foreach ($unassignedRooms as $row): ?>
                            <tr>
                                <td><a href="/rooms/modules/calendar/availability_planner.php?order_id=<?php echo (int)$row['order_id']; ?>"><?php echo h($row['order_no'] ?: '#' . $row['order_id']); ?></a></td>
                                <td><?php echo h(trim((string)$row['guest_name']) ?: 'Guest'); ?></td>
                                <td><strong><?php echo h($row['room_type'] ?: 'Not set'); ?></strong></td>
                                <td><?php echo h($row['check_in']); ?> to <?php echo h($row['check_out']); ?></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <div class="col-xl-6">
        <div class="card panel-card alert-panel alert-warning h-100">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h5 class="mb-0"><i class="bi bi-house-exclamation me-2"></i>In-house Balances</h5>
                <span class="badge text-bg-warning"><?php echo count($inHouseBalances); ?></span>
            </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 (!$inHouseBalances): ?>
                        <tr><td colspan="4" class="text-center text-muted py-4">No in-house balances.</td></tr>
                    <?php else: ?>
                        <?php foreach ($inHouseBalances 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 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 alert-panel alert-info h-100">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h5 class="mb-0"><i class="bi bi-box-arrow-in-right me-2"></i>Arrivals Today</h5>
                <span class="badge text-bg-info"><?php echo count($arrivalsToday); ?></span>
            </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 (!$arrivalsToday): ?>
                        <tr><td colspan="4" class="text-center text-muted py-4">No arrivals today.</td></tr>
                    <?php else: ?>
                        <?php foreach ($arrivalsToday 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 <?php echo (float)$row['balance'] > 0 ? 'fw-bold text-danger' : ''; ?>"><?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 alert-panel alert-warning h-100">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h5 class="mb-0"><i class="bi bi-clock-history me-2"></i>Long Stay Without Payment</h5>
                <span class="badge text-bg-warning"><?php echo count($longStayNoPayment); ?></span>
            </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">Days</th>
                    </tr>
                    </thead>
                    <tbody>
                    <?php if (!$longStayNoPayment): ?>
                        <tr><td colspan="4" class="text-center text-muted py-4">No long stay payment alerts.</td></tr>
                    <?php else: ?>
                        <?php foreach ($longStayNoPayment 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 class="text-end fw-bold"><?php echo (int)$row['days_in_house']; ?></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <div class="col-12">
        <div class="card panel-card alert-panel alert-warning top-agent-outstanding-card">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h5 class="mb-0"><i class="bi bi-people me-2"></i>Top Agent Outstanding</h5>
                <span class="badge text-bg-warning"><?php echo count($agentOutstanding); ?></span>
            </div>
            <div class="card-body p-0 top-agent-table-wrap">
                <table class="table table-modern align-middle mb-0 top-agent-table">
                    <thead>
                    <tr>
                        <th>Agent</th>
                        <th class="text-end">Bookings</th>
                        <th class="text-end">Outstanding</th>
                        <th class="text-end">Action</th>
                    </tr>
                    </thead>
                    <tbody>
                    <?php if (!$agentOutstanding): ?>
                        <tr><td colspan="4" class="text-center text-muted py-4">No agent outstanding alerts.</td></tr>
                    <?php else: ?>
                        <?php foreach ($agentOutstanding as $row): ?>
                            <tr>
                                <td><strong><?php echo h($row['agent_name']); ?></strong></td>
                                <td class="text-end"><?php echo (int)$row['booking_count']; ?></td>
                                <td class="text-end fw-bold text-danger"><?php echo money_fmt($row['balance']); ?></td>
                                <td class="text-end">
                                    <a href="outstanding_report.php?agent_id=<?php echo (int)$row['agent_id']; ?>&as_at=<?php echo h($selectedDate); ?>" class="btn btn-sm btn-outline-danger">
                                        View Debtors
                                    </a>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

</div>

<style>

/* Compact Operations Alerts layout */
.hero-band{
    padding:10px 14px !important;
    margin-bottom:8px !important;
    border-radius:14px !important;
}
.hero-band .row.g-4{
    --bs-gutter-x:.75rem !important;
    --bs-gutter-y:.45rem !important;
}
.hero-title{
    font-size:1.22rem !important;
    line-height:1.08 !important;
    margin-top:2px !important;
}
.hero-subtitle{
    font-size:.78rem !important;
    line-height:1.22 !important;
    margin-top:3px !important;
    max-width:980px;
}
.page-eyebrow{
    font-size:.68rem !important;
    letter-spacing:.06em !important;
}
.hero-band .btn{
    padding:5px 9px !important;
    font-size:.78rem !important;
    border-radius:8px !important;
}
.card.panel-card.mb-4{
    margin-bottom:8px !important;
}
.panel-card .card-header{
    padding:8px 11px !important;
}
.panel-card .card-body{
    padding:10px !important;
}
.form-label{
    font-size:.7rem !important;
    margin-bottom:2px !important;
    font-weight:700 !important;
}
.form-control,.form-select{
    min-height:31px !important;
    height:31px !important;
    font-size:.8rem !important;
    padding:3px 7px !important;
    border-radius:8px !important;
}
.row.g-4.mb-4{
    --bs-gutter-x:.65rem !important;
    --bs-gutter-y:.65rem !important;
    margin-bottom:8px !important;
}
.metric-card{
    padding:8px 10px !important;
    min-height:52px !important;
    gap:7px !important;
}
.metric-icon{
    width:30px !important;
    height:30px !important;
    border-radius:8px !important;
    font-size:13px !important;
}
.metric-label{
    font-size:.68rem !important;
    margin-bottom:1px !important;
}
.metric-value{
    font-size:.98rem !important;
    line-height:1.05 !important;
}

/* Top outstanding debtors / agents: freeze table header */
.top-agent-table-wrap{
    max-height:360px;
    overflow:auto;
}
.top-agent-table thead th{
    position:sticky;
    top:0;
    z-index:12;
    background:#f8fafc !important;
    box-shadow:0 2px 0 rgba(15,23,42,.08);
}
.top-agent-table th,
.top-agent-table td{
    white-space:nowrap;
}

.alert-panel {
    overflow: hidden;
}
.alert-critical {
    border-color: rgba(220, 38, 38, .22) !important;
}
.alert-warning {
    border-color: rgba(217, 119, 6, .22) !important;
}
.alert-info {
    border-color: rgba(14, 165, 233, .22) !important;
}
</style>

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