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

$pageTitle = 'Executive Dashboard';

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('exec_table_exists')) {
    function exec_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('exec_column_exists')) {
    function exec_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');
$yesterday = date('Y-m-d', strtotime('-1 day'));
$monthStart = date('Y-m-01');
$monthEnd = date('Y-m-t');
$last14 = date('Y-m-d', strtotime('-13 days'));
$selectedPropertyId = (int)($_GET['property_id'] ?? 0);

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

$hasOrderStatus = !$errors && exec_column_exists($pdo, 'orders', 'status');
$hasOrderCancelled = !$errors && exec_column_exists($pdo, 'orders', 'cancelled');
$hasRoomingCancelled = !$errors && exec_column_exists($pdo, 'order_rooming', 'cancelled');
$hasRoomingProperty = !$errors && exec_column_exists($pdo, 'order_rooming', 'property_id');
$hasRoomActive = !$errors && exec_column_exists($pdo, 'rooms', 'is_active');
$hasRoomStatus = !$errors && exec_column_exists($pdo, 'rooms', 'status');
$hasPaymentVoided = !$errors && exec_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();
    }
}

$todaySql = $pdo->quote($today);
$yesterdaySql = $pdo->quote($yesterday);
$monthStartSql = $pdo->quote($monthStart);
$monthEndSql = $pdo->quote($monthEnd);
$last14Sql = $pdo->quote($last14);

$propertyFt = $propertyPmt = $propertyOrders = $propertyRoom = '';
$paramsFt = $paramsPmt = $paramsOrders = $paramsRoom = [];

if ($selectedPropertyId > 0) {
    $propertyFt = " AND ft.property_id = :property_id ";
    $propertyPmt = " AND pmt.property_id = :property_id ";
    $propertyOrders = " AND o.property_id = :property_id ";
    $propertyRoom = " AND COALESCE(r.property_id, rt.property_id) = :property_id ";
    $paramsFt[':property_id'] = $selectedPropertyId;
    $paramsPmt[':property_id'] = $selectedPropertyId;
    $paramsOrders[':property_id'] = $selectedPropertyId;
    $paramsRoom[':property_id'] = $selectedPropertyId;
}

$orderActive = '';
if ($hasOrderCancelled) $orderActive .= " AND COALESCE(o.cancelled, 0) = 0 ";
if ($hasOrderStatus) $orderActive .= " AND LOWER(COALESCE(o.status,'')) NOT IN ('cancelled','canceled','void','deleted') ";
$roomingActive = $hasRoomingCancelled ? " AND COALESCE(orm.cancelled, 0) = 0 " : "";
$voidExpr = $hasPaymentVoided ? "COALESCE(pmt.is_voided,0)" : "0";

$kpi = [
    'today_revenue' => 0, 'yesterday_revenue' => 0, 'month_revenue' => 0,
    'today_payments' => 0, 'month_payments' => 0, 'outstanding' => 0,
    'today_arrivals' => 0, 'today_departures' => 0, 'today_occupancy' => 0,
    'available_rooms' => 0, 'occupied_rooms' => 0, 'month_room_revenue' => 0,
    'month_room_nights' => 0, 'adr' => 0, 'revpar' => 0
];

$revenueMix = $paymentsByMethod = $dailyRevenue = $occupancyTrend = $topAgents = $topExtras = $arrivals = $departures = $outstandingAlerts = [];

if (!$errors) {
    try {
        $stmt = $pdo->prepare("SELECT COALESCE(SUM(ft.debit),0) FROM folio_transactions ft WHERE DATE(ft.tx_date) = $todaySql $propertyFt");
        $stmt->execute($paramsFt);
        $kpi['today_revenue'] = (float)$stmt->fetchColumn();

        $stmt = $pdo->prepare("SELECT COALESCE(SUM(ft.debit),0) FROM folio_transactions ft WHERE DATE(ft.tx_date) = $yesterdaySql $propertyFt");
        $stmt->execute($paramsFt);
        $kpi['yesterday_revenue'] = (float)$stmt->fetchColumn();

        $stmt = $pdo->prepare("
            SELECT
                COALESCE(SUM(ft.debit),0) AS month_revenue,
                COALESCE(SUM(CASE WHEN ft.tx_type='room_charge' THEN ft.debit ELSE 0 END),0) AS room_revenue
            FROM folio_transactions ft
            WHERE DATE(ft.tx_date) BETWEEN $monthStartSql AND $monthEndSql
            $propertyFt
        ");
        $stmt->execute($paramsFt);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
        $kpi['month_revenue'] = (float)($row['month_revenue'] ?? 0);
        $kpi['month_room_revenue'] = (float)($row['room_revenue'] ?? 0);

        $stmt = $pdo->prepare("
            SELECT COALESCE(SUM(CASE WHEN $voidExpr = 0 THEN pmt.amount ELSE 0 END),0)
            FROM payment pmt
            WHERE DATE(COALESCE(pmt.date_time, CONCAT(pmt.added_date,' 00:00:00'))) = $todaySql
            $propertyPmt
        ");
        $stmt->execute($paramsPmt);
        $kpi['today_payments'] = (float)$stmt->fetchColumn();

        $stmt = $pdo->prepare("
            SELECT COALESCE(SUM(CASE WHEN $voidExpr = 0 THEN pmt.amount ELSE 0 END),0)
            FROM payment pmt
            WHERE DATE(COALESCE(pmt.date_time, CONCAT(pmt.added_date,' 00:00:00'))) BETWEEN $monthStartSql AND $monthEndSql
            $propertyPmt
        ");
        $stmt->execute($paramsPmt);
        $kpi['month_payments'] = (float)$stmt->fetchColumn();

        $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
            $propertyOrders
        ");
        $stmt->execute($paramsOrders);
        $kpi['outstanding'] = (float)$stmt->fetchColumn();
    } catch (Throwable $e) {
        $errors[] = 'Could not load financial KPIs: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $stmt = $pdo->prepare("SELECT COUNT(DISTINCT o.id) FROM orders o WHERE DATE(o.check_in) = $todaySql $propertyOrders $orderActive");
        $stmt->execute($paramsOrders);
        $kpi['today_arrivals'] = (int)$stmt->fetchColumn();

        $stmt = $pdo->prepare("SELECT COUNT(DISTINCT o.id) FROM orders o WHERE DATE(o.check_out) = $todaySql $propertyOrders $orderActive");
        $stmt->execute($paramsOrders);
        $kpi['today_departures'] = (int)$stmt->fetchColumn();

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

        $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);
        $kpi['available_rooms'] = (int)$stmt->fetchColumn();

        $occWhere = "orm.check_in <= $todaySql AND orm.check_out > $todaySql $roomingActive $orderActive";
        $occParams = [];
        if ($selectedPropertyId > 0) {
            $occWhere .= $hasRoomingProperty
                ? " AND COALESCE(orm.property_id, o.property_id, r.property_id, rt.property_id) = :property_id "
                : " 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);
        $kpi['occupied_rooms'] = (int)$stmt->fetchColumn();

        if ($kpi['available_rooms'] > 0 && $kpi['occupied_rooms'] > $kpi['available_rooms']) {
            $kpi['occupied_rooms'] = $kpi['available_rooms'];
        }

        $kpi['today_occupancy'] = $kpi['available_rooms'] > 0 ? ($kpi['occupied_rooms'] / $kpi['available_rooms']) * 100 : 0;

        $rnWhere = "orm.check_in <= $monthEndSql AND orm.check_out > $monthStartSql $roomingActive $orderActive";
        $rnParams = [];
        if ($selectedPropertyId > 0) {
            $rnWhere .= $hasRoomingProperty
                ? " AND COALESCE(orm.property_id, o.property_id, r.property_id, rt.property_id) = :property_id "
                : " AND COALESCE(o.property_id, r.property_id, rt.property_id) = :property_id ";
            $rnParams[':property_id'] = $selectedPropertyId;
        }

        $stmt = $pdo->prepare("
            SELECT COALESCE(SUM(
                DATEDIFF(
                    LEAST(orm.check_out, DATE_ADD($monthEndSql, INTERVAL 1 DAY)),
                    GREATEST(orm.check_in, $monthStartSql)
                )
            ),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 $rnWhere
        ");
        $stmt->execute($rnParams);
        $kpi['month_room_nights'] = (int)$stmt->fetchColumn();

        $monthAvailableNights = $kpi['available_rooms'] * (int)date('t');
        $kpi['adr'] = $kpi['month_room_nights'] > 0 ? ($kpi['month_room_revenue'] / $kpi['month_room_nights']) : 0;
        $kpi['revpar'] = $monthAvailableNights > 0 ? ($kpi['month_room_revenue'] / $monthAvailableNights) : 0;
    } catch (Throwable $e) {
        $errors[] = 'Could not load occupancy KPIs: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT
                DATE(ft.tx_date) AS tx_date,
                COALESCE(SUM(ft.debit),0) AS revenue
            FROM folio_transactions ft
            WHERE DATE(ft.tx_date) BETWEEN $last14Sql AND $todaySql
            $propertyFt
            GROUP BY DATE(ft.tx_date)
            ORDER BY tx_date ASC
        ");
        $stmt->execute($paramsFt);
        $dailyRevenue = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $stmt = $pdo->prepare("
            SELECT
                CASE
                    WHEN ft.tx_type = 'room_charge' THEN 'Room Revenue'
                    WHEN ft.credit > 0 THEN 'Payments'
                    ELSE 'Extras / Other'
                END AS label,
                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) BETWEEN $monthStartSql AND $monthEndSql
            $propertyFt
            GROUP BY label
            ORDER BY debit_total DESC, credit_total DESC
        ");
        $stmt->execute($paramsFt);
        $revenueMix = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $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 amount
            FROM payment pmt
            WHERE DATE(COALESCE(pmt.date_time, CONCAT(pmt.added_date,' 00:00:00'))) BETWEEN $monthStartSql AND $monthEndSql
            $propertyPmt
            GROUP BY payment_method
            ORDER BY amount DESC
            LIMIT 8
        ");
        $stmt->execute($paramsPmt);
        $paymentsByMethod = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $stmt = $pdo->prepare("
            SELECT
                COALESCE(NULLIF(TRIM(CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,''))), ''), 'Direct / No Agent') AS agent_name,
                COUNT(DISTINCT o.id) AS bookings,
                COALESCE(SUM(CASE WHEN ft.debit > 0 THEN ft.debit ELSE 0 END),0) AS revenue
            FROM orders o
            LEFT JOIN agents a ON a.id = o.agent_id
            LEFT JOIN folio_transactions ft ON ft.order_id = o.id
                AND DATE(ft.tx_date) BETWEEN $monthStartSql AND $monthEndSql
            WHERE o.check_in <= $monthEndSql
              AND o.check_out > $monthStartSql
              $propertyOrders
              $orderActive
            GROUP BY agent_name
            ORDER BY revenue DESC
            LIMIT 8
        ");
        $stmt->execute($paramsOrders);
        $topAgents = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $stmt = $pdo->prepare("
            SELECT
                COALESCE(ft.description, ft.tx_type) AS item,
                COUNT(*) AS qty,
                COALESCE(SUM(ft.debit),0) AS revenue
            FROM folio_transactions ft
            WHERE DATE(ft.tx_date) BETWEEN $monthStartSql AND $monthEndSql
              AND ft.debit > 0
              AND ft.tx_type <> 'room_charge'
              $propertyFt
            GROUP BY item
            ORDER BY revenue DESC
            LIMIT 8
        ");
        $stmt->execute($paramsFt);
        $topExtras = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $errors[] = 'Could not load chart data: ' . $e->getMessage();
    }
}

/* Occupancy trend */
if (!$errors) {
    try {
        $start = new DateTime($last14);
        $end = new DateTime($today);
        while ($start <= $end) {
            $d = $start->format('Y-m-d');
            $dSql = $pdo->quote($d);
            $where = "orm.check_in <= $dSql AND orm.check_out > $dSql $roomingActive $orderActive";
            $params = [];
            if ($selectedPropertyId > 0) {
                $where .= $hasRoomingProperty
                    ? " AND COALESCE(orm.property_id, o.property_id, r.property_id, rt.property_id) = :property_id "
                    : " AND COALESCE(o.property_id, r.property_id, rt.property_id) = :property_id ";
                $params[':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 $where
            ");
            $stmt->execute($params);
            $occ = (int)$stmt->fetchColumn();

            $pct = $kpi['available_rooms'] > 0 ? ($occ / $kpi['available_rooms']) * 100 : 0;
            $occupancyTrend[] = ['date' => $d, 'occupancy' => round($pct, 2)];
            $start->modify('+1 day');
        }
    } catch (Throwable $e) {
        $occupancyTrend = [];
    }
}

if (!$errors) {
    try {
        $baseList = "
            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 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
            $propertyOrders
            $orderActive
        ";

        $stmt = $pdo->prepare($baseList . "
            AND DATE(o.check_in) = $todaySql
            GROUP BY o.id, o.order_no, o.check_in, o.check_out, property_name, guest_name, agent_name
            ORDER BY o.check_in ASC
            LIMIT 10
        ");
        $stmt->execute($paramsOrders);
        $arrivals = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $stmt = $pdo->prepare($baseList . "
            AND DATE(o.check_out) = $todaySql
            GROUP BY o.id, o.order_no, o.check_in, o.check_out, property_name, guest_name, agent_name
            ORDER BY o.check_out ASC
            LIMIT 10
        ");
        $stmt->execute($paramsOrders);
        $departures = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $stmt = $pdo->prepare("
            SELECT
                o.id,
                o.order_no,
                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
            $propertyOrders
            GROUP BY o.id, o.order_no, property_name, guest_name, agent_name
            ORDER BY balance DESC
            LIMIT 10
        ");
        $stmt->execute($paramsOrders);
        $outstandingAlerts = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $errors[] = 'Could not load operational lists: ' . $e->getMessage();
    }
}

$revenueDelta = $kpi['yesterday_revenue'] > 0 ? (($kpi['today_revenue'] - $kpi['yesterday_revenue']) / $kpi['yesterday_revenue']) * 100 : 0;

$chartRevenueLabels = array_map(fn($r) => date('d M', strtotime($r['tx_date'])), $dailyRevenue);
$chartRevenueData = array_map(fn($r) => round((float)$r['revenue'], 2), $dailyRevenue);
$chartOccLabels = array_map(fn($r) => date('d M', strtotime($r['date'])), $occupancyTrend);
$chartOccData = array_map(fn($r) => round((float)$r['occupancy'], 2), $occupancyTrend);
$chartMixLabels = array_map(fn($r) => (string)$r['label'], $revenueMix);
$chartMixData = array_map(fn($r) => round((float)$r['debit_total'], 2), $revenueMix);
$chartPaymentLabels = array_map(fn($r) => (string)$r['payment_method'], $paymentsByMethod);
$chartPaymentData = array_map(fn($r) => round((float)$r['amount'], 2), $paymentsByMethod);
$chartAgentLabels = array_map(fn($r) => (string)$r['agent_name'], $topAgents);
$chartAgentData = array_map(fn($r) => round((float)$r['revenue'], 2), $topAgents);

$chartRevenueDates = array_map(fn($r) => (string)$r['tx_date'], $dailyRevenue);
$chartOccDates = array_map(fn($r) => (string)$r['date'], $occupancyTrend);
$chartAgentNames = array_map(fn($r) => (string)$r['agent_name'], $topAgents);
$chartPaymentMethods = array_map(fn($r) => (string)$r['payment_method'], $paymentsByMethod);
$chartMixTypes = array_map(fn($r) => (string)$r['label'], $revenueMix);
$propertyQuerySuffix = $selectedPropertyId > 0 ? '&property_id=' . (int)$selectedPropertyId : '';

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-stars"></i> Executive Intelligence</div>
            <div class="hero-title mt-2">Executive Dashboard</div>
            <div class="hero-subtitle mt-2">Live PMS snapshot with visual revenue, occupancy, payment and agent performance charts.</div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <a href="daily_manager_report.php?report_date=<?php echo h($today); ?><?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="alerts.php?date=<?php echo h($today); ?><?php echo $selectedPropertyId > 0 ? '&property_id=' . (int)$selectedPropertyId : ''; ?>" class="btn btn-outline-light">
                <i class="bi bi-shield-exclamation me-2"></i>Alerts
            </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-8">
                <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 Dashboard</button>
            </div>
        </form>
    </div>
</div>

<div class="row g-4 mb-4">
    <div class="col-md-6 col-xl-3"><div class="exec-kpi"><div class="kpi-top"><span>Today Revenue</span><i class="bi bi-cash-coin text-success"></i></div><div class="kpi-value"><?php echo money_fmt($kpi['today_revenue']); ?></div><div class="kpi-sub <?php echo $revenueDelta >= 0 ? 'text-success' : 'text-danger'; ?>"><?php echo $revenueDelta >= 0 ? '+' : ''; ?><?php echo money_fmt($revenueDelta); ?>% vs yesterday</div></div></div>
    <div class="col-md-6 col-xl-3"><div class="exec-kpi"><div class="kpi-top"><span>Occupancy Today</span><i class="bi bi-percent text-warning"></i></div><div class="kpi-value"><?php echo money_fmt($kpi['today_occupancy']); ?>%</div><div class="kpi-sub"><?php echo (int)$kpi['occupied_rooms']; ?> occupied / <?php echo (int)$kpi['available_rooms']; ?> rooms</div></div></div>
    <div class="col-md-6 col-xl-3"><div class="exec-kpi"><div class="kpi-top"><span>Month Revenue</span><i class="bi bi-graph-up-arrow text-primary"></i></div><div class="kpi-value"><?php echo money_fmt($kpi['month_revenue']); ?></div><div class="kpi-sub">Payments: <?php echo money_fmt($kpi['month_payments']); ?></div></div></div>
    <div class="col-md-6 col-xl-3"><div class="exec-kpi"><div class="kpi-top"><span>Outstanding</span><i class="bi bi-exclamation-circle text-danger"></i></div><div class="kpi-value"><?php echo money_fmt($kpi['outstanding']); ?></div><div class="kpi-sub">Top debtors shown below</div></div></div>
    <div class="col-md-6 col-xl-3"><div class="exec-kpi"><div class="kpi-top"><span>ADR</span><i class="bi bi-speedometer2 text-info"></i></div><div class="kpi-value"><?php echo money_fmt($kpi['adr']); ?></div><div class="kpi-sub">Month-to-date</div></div></div>
    <div class="col-md-6 col-xl-3"><div class="exec-kpi"><div class="kpi-top"><span>RevPAR</span><i class="bi bi-bar-chart text-secondary"></i></div><div class="kpi-value"><?php echo money_fmt($kpi['revpar']); ?></div><div class="kpi-sub">Month-to-date</div></div></div>
    <div class="col-md-6 col-xl-3"><div class="exec-kpi"><div class="kpi-top"><span>Arrivals Today</span><i class="bi bi-box-arrow-in-right text-warning"></i></div><div class="kpi-value"><?php echo (int)$kpi['today_arrivals']; ?></div><div class="kpi-sub">Check-in workload</div></div></div>
    <div class="col-md-6 col-xl-3"><div class="exec-kpi"><div class="kpi-top"><span>Departures Today</span><i class="bi bi-box-arrow-right text-info"></i></div><div class="kpi-value"><?php echo (int)$kpi['today_departures']; ?></div><div class="kpi-sub">Checkout workload</div></div></div>
</div>

<div class="row g-4">
    <div class="col-xl-8">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Revenue Trend - Last 14 Days</h5></div>
            <div class="card-body chart-box"><canvas id="revenueTrendChart"></canvas></div>
        </div>
    </div>

    <div class="col-xl-4">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Revenue Mix - Month</h5></div>
            <div class="card-body chart-box"><canvas id="revenueMixChart"></canvas></div>
        </div>
    </div>

    <div class="col-xl-8">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Occupancy Trend - Last 14 Days</h5></div>
            <div class="card-body chart-box"><canvas id="occupancyTrendChart"></canvas></div>
        </div>
    </div>

    <div class="col-xl-4">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Payments by Method - Month</h5></div>
            <div class="card-body chart-box"><canvas id="paymentMethodChart"></canvas></div>
        </div>
    </div>

    <div class="col-xl-7">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Top Agents - Month</h5></div>
            <div class="card-body chart-box chart-box-tall"><canvas id="topAgentsChart"></canvas></div>
        </div>
    </div>

    <div class="col-xl-5">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Top Extras - Month</h5></div>
            <div class="card-body p-0">
                <table class="table table-modern align-middle mb-0">
                    <thead><tr><th>Extra / Service</th><th class="text-end">Qty</th><th class="text-end">Revenue</th></tr></thead>
                    <tbody>
                    <?php if (!$topExtras): ?>
                        <tr><td colspan="3" class="text-center text-muted py-4">No extras data.</td></tr>
                    <?php else: ?>
                        <?php foreach ($topExtras as $row): ?>
                            <tr>
                                <td><strong><?php echo h($row['item']); ?></strong></td>
                                <td class="text-end"><?php echo (int)$row['qty']; ?></td>
                                <td class="text-end fw-bold"><?php echo money_fmt($row['revenue']); ?></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <div class="col-xl-6">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Arrivals Today</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 class="text-end">Balance</th></tr></thead>
                    <tbody>
                    <?php if (!$arrivals): ?>
                        <tr><td colspan="3" class="text-center text-muted py-4">No arrivals today.</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 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 h-100">
            <div class="card-header"><h5 class="mb-0">Departures Today</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 class="text-end">Balance</th></tr></thead>
                    <tbody>
                    <?php if (!$departures): ?>
                        <tr><td colspan="3" class="text-center text-muted py-4">No departures today.</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 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-12">
        <div class="card panel-card">
            <div class="card-header"><h5 class="mb-0">Top Outstanding Balances</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 class="text-end">Balance</th></tr></thead>
                    <tbody>
                    <?php if (!$outstandingAlerts): ?>
                        <tr><td colspan="5" class="text-center text-muted py-4">No outstanding balances.</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 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>

<style>
.exec-kpi{background:#fff;border:1px solid rgba(15,23,42,.08);border-radius:24px;padding:20px;box-shadow:0 14px 35px rgba(15,23,42,.06);height:100%}
.kpi-top{display:flex;justify-content:space-between;align-items:center;color:#64748b;font-weight:800;font-size:13px;text-transform:uppercase;letter-spacing:.04em}
.kpi-top i{font-size:24px}
.kpi-value{margin-top:12px;font-size:30px;font-weight:900;color:#111827;letter-spacing:-.04em}
.kpi-sub{margin-top:6px;color:#64748b;font-size:13px;font-weight:700}
.chart-box{position:relative;min-height:315px}
.chart-box canvas{width:100%!important;height:285px!important}
.chart-box-tall canvas{height:330px!important}
</style>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
document.addEventListener('DOMContentLoaded', function () {
    if (typeof Chart === 'undefined') {
        return;
    }

    const money = value => Number(value || 0).toLocaleString(undefined, {minimumFractionDigits: 2, maximumFractionDigits: 2});

    const revenueLabels = <?php echo json_encode($chartRevenueLabels); ?>;
    const revenueDates = <?php echo json_encode($chartRevenueDates); ?>;

    const occupancyLabels = <?php echo json_encode($chartOccLabels); ?>;
    const occupancyDates = <?php echo json_encode($chartOccDates); ?>;

    const paymentMethods = <?php echo json_encode($chartPaymentMethods); ?>;
    const revenueMixTypes = <?php echo json_encode($chartMixTypes); ?>;
    const agentNames = <?php echo json_encode($chartAgentNames); ?>;

    const propertySuffix = <?php echo json_encode($propertyQuerySuffix); ?>;

    const baseOptions = {
        responsive: true,
        maintainAspectRatio: false,
        onHover: function(evt, elements) {
            evt.native.target.style.cursor = elements.length ? 'pointer' : 'default';
        },
        plugins: {
            legend: { labels: { boxWidth: 12, usePointStyle: true } },
            tooltip: { callbacks: { label: ctx => ctx.dataset.label + ': ' + money(ctx.raw) } }
        }
    };

    new Chart(document.getElementById('revenueTrendChart'), {
        type: 'line',
        data: {
            labels: revenueLabels,
            datasets: [{
                label: 'Revenue',
                data: <?php echo json_encode($chartRevenueData); ?>,
                tension: .35,
                fill: true,
                borderWidth: 3,
                pointRadius: 4
            }]
        },
        options: {
            ...baseOptions,
            onClick: function(evt, elements) {
                if (!elements.length) return;
                const date = revenueDates[elements[0].index];
                window.location.href = 'revenue_report.php?date_from=' + encodeURIComponent(date) + '&date_to=' + encodeURIComponent(date) + propertySuffix;
            },
            scales: { y: { beginAtZero: true, ticks: { callback: value => money(value) } } }
        }
    });

    new Chart(document.getElementById('occupancyTrendChart'), {
        type: 'line',
        data: {
            labels: occupancyLabels,
            datasets: [{
                label: 'Occupancy %',
                data: <?php echo json_encode($chartOccData); ?>,
                tension: .35,
                fill: true,
                borderWidth: 3,
                pointRadius: 4
            }]
        },
        options: {
            ...baseOptions,
            onClick: function(evt, elements) {
                if (!elements.length) return;
                const date = occupancyDates[elements[0].index];
                window.location.href = 'occupancy_report.php?date_from=' + encodeURIComponent(date) + '&date_to=' + encodeURIComponent(date) + propertySuffix;
            },
            plugins: {
                ...baseOptions.plugins,
                tooltip: {
                    callbacks: {
                        label: ctx => 'Occupancy: ' + Number(ctx.raw || 0).toFixed(2) + '%'
                    }
                }
            },
            scales: { y: { beginAtZero: true, max: 100, ticks: { callback: value => value + '%' } } }
        }
    });

    new Chart(document.getElementById('revenueMixChart'), {
        type: 'doughnut',
        data: {
            labels: <?php echo json_encode($chartMixLabels); ?>,
            datasets: [{
                label: 'Revenue Mix',
                data: <?php echo json_encode($chartMixData); ?>,
                borderWidth: 2
            }]
        },
        options: {
            ...baseOptions,
            onClick: function(evt, elements) {
                if (!elements.length) return;
                const label = revenueMixTypes[elements[0].index] || '';
                let url = 'revenue_report.php?date_from=<?php echo h($monthStart); ?>&date_to=<?php echo h($today); ?>' + propertySuffix;

                if (label === 'Room Revenue') {
                    url += '&tx_type=room_charge';
                } else if (label === 'Extras / Other') {
                    url = 'extras_report.php?date_from=<?php echo h($monthStart); ?>&date_to=<?php echo h($today); ?>' + propertySuffix;
                } else if (label === 'Payments') {
                    url = 'payments_report.php?date_from=<?php echo h($monthStart); ?>&date_to=<?php echo h($today); ?>' + propertySuffix;
                }

                window.location.href = url;
            }
        }
    });

    new Chart(document.getElementById('paymentMethodChart'), {
        type: 'doughnut',
        data: {
            labels: <?php echo json_encode($chartPaymentLabels); ?>,
            datasets: [{
                label: 'Payments',
                data: <?php echo json_encode($chartPaymentData); ?>,
                borderWidth: 2
            }]
        },
        options: {
            ...baseOptions,
            onClick: function(evt, elements) {
                if (!elements.length) return;
                const method = paymentMethods[elements[0].index] || '';
                window.location.href = 'payments_report.php?date_from=<?php echo h($monthStart); ?>&date_to=<?php echo h($today); ?>&payment_method=' + encodeURIComponent(method) + propertySuffix;
            }
        }
    });

    new Chart(document.getElementById('topAgentsChart'), {
        type: 'bar',
        data: {
            labels: <?php echo json_encode($chartAgentLabels); ?>,
            datasets: [{
                label: 'Agent Revenue',
                data: <?php echo json_encode($chartAgentData); ?>,
                borderWidth: 1,
                borderRadius: 10
            }]
        },
        options: {
            ...baseOptions,
            indexAxis: 'y',
            onClick: function(evt, elements) {
                if (!elements.length) return;
                const agent = agentNames[elements[0].index] || '';
                window.location.href = 'agent_production_report.php?date_from=<?php echo h($monthStart); ?>&date_to=<?php echo h($today); ?>&agent_name=' + encodeURIComponent(agent) + propertySuffix;
            },
            scales: { x: { beginAtZero: true, ticks: { callback: value => money(value) } } }
        }
    });
});
</script>

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