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

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('pct_fmt')) {
    function pct_fmt($value): string {
        return number_format((float)$value, 1) . '%';
    }
}

$today = date('Y-m-d');
$propertyId = (int)($_GET['property_id'] ?? 0);
$dateFrom = trim((string)($_GET['date_from'] ?? $today));
$dateTo = trim((string)($_GET['date_to'] ?? date('Y-m-d', strtotime($today . ' +13 days'))));

$paramsBase = [];
$orderPropertySql = '';
$roomPropertySql  = '';
$ratePropertySql  = '';

$propertyLabel = 'All Properties';
if ($propertyId > 0) {
    $paramsBase[':property_id'] = $propertyId;
    $orderPropertySql = " AND o.property_id = :property_id ";
    $roomPropertySql  = " AND r.property_id = :property_id ";
    $ratePropertySql  = " AND rp.property_id = :property_id ";

    $stmt = $pdo->prepare("SELECT name, code FROM properties WHERE id = :id LIMIT 1");
    $stmt->execute([':id' => $propertyId]);
    $prop = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($prop) {
        $propertyLabel = trim((string)($prop['name'] ?? ''));
        if (!empty($prop['code'])) {
            $propertyLabel .= ' (' . $prop['code'] . ')';
        }
    }
}

$summary = [
    'room_stock' => 0,
    'occupied_rooms_today' => 0,
    'vacant_rooms_today' => 0,
    'arrivals_today' => 0,
    'departures_today' => 0,
    'in_house_today' => 0,
    'dirty_rooms' => 0,
    'ooo_rooms' => 0,
    'open_balance_bookings' => 0,
    'today_revenue' => 0,
    'today_room_nights' => 0,
    'today_adr' => 0,
    'today_occ_pct' => 0,
    'forecast_revenue' => 0,
    'forecast_room_nights' => 0,
    'forecast_avg_adr' => 0,
    'forecast_avg_occ' => 0,
];

$arrivalsToday = [];
$departuresToday = [];
$openBalanceBookings = [];
$dirtyRooms = [];
$outOfOrderRooms = [];
$forecastRows = [];

/* Room stock */
$sql = "SELECT COUNT(*) FROM rooms r WHERE 1=1 $roomPropertySql";
$stmt = $pdo->prepare($sql);
$stmt->execute($paramsBase);
$summary['room_stock'] = (int)$stmt->fetchColumn();

/* Occupied rooms today */
$sql = "
    SELECT COUNT(DISTINCT rmg.room_id)
    FROM order_rooming rmg
    INNER JOIN orders o ON o.id = rmg.order_id
    INNER JOIN rooms r ON r.id = rmg.room_id
    WHERE DATE(o.check_in) <= :today
      AND DATE(o.check_out) >= :today
      AND rmg.room_id IS NOT NULL
      $roomPropertySql
";
$params = $paramsBase;
$params[':today'] = $today;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$summary['occupied_rooms_today'] = (int)$stmt->fetchColumn();
$summary['vacant_rooms_today'] = max(0, $summary['room_stock'] - $summary['occupied_rooms_today']);
$summary['today_occ_pct'] = $summary['room_stock'] > 0 ? ($summary['occupied_rooms_today'] / $summary['room_stock']) * 100 : 0;

/* Arrivals today */
$sql = "SELECT COUNT(*) FROM orders o WHERE DATE(o.check_in) = :today $orderPropertySql";
$params = $paramsBase;
$params[':today'] = $today;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$summary['arrivals_today'] = (int)$stmt->fetchColumn();

/* Departures today */
$sql = "SELECT COUNT(*) FROM orders o WHERE DATE(o.check_out) = :today $orderPropertySql";
$params = $paramsBase;
$params[':today'] = $today;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$summary['departures_today'] = (int)$stmt->fetchColumn();

/* In house */
$sql = "SELECT COUNT(*) FROM orders o WHERE DATE(o.check_in) <= :today AND DATE(o.check_out) >= :today $orderPropertySql";
$params = $paramsBase;
$params[':today'] = $today;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$summary['in_house_today'] = (int)$stmt->fetchColumn();

/* Dirty rooms */
$sql = "SELECT COUNT(*) FROM rooms r WHERE COALESCE(LOWER(r.housekeeping_status), LOWER(r.status), '') = 'dirty' $roomPropertySql";
$stmt = $pdo->prepare($sql);
$stmt->execute($paramsBase);
$summary['dirty_rooms'] = (int)$stmt->fetchColumn();

/* OOO rooms */
$sql = "SELECT COUNT(*) FROM rooms r WHERE COALESCE(LOWER(r.housekeeping_status), LOWER(r.status), '') IN ('out_of_order','ooo','maintenance','blocked') $roomPropertySql";
$stmt = $pdo->prepare($sql);
$stmt->execute($paramsBase);
$summary['ooo_rooms'] = (int)$stmt->fetchColumn();

/* Arrivals list */
$sql = "
    SELECT o.*, p.name AS property_name, p.code AS property_code, g.firstname, g.lastname
    FROM orders o
    LEFT JOIN properties p ON p.id = o.property_id
    LEFT JOIN guests g ON g.id = o.guest_id
    WHERE DATE(o.check_in) = :today
    $orderPropertySql
    ORDER BY p.name ASC, o.id DESC
";
$params = $paramsBase;
$params[':today'] = $today;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$arrivalsToday = $stmt->fetchAll(PDO::FETCH_ASSOC);

/* Departures list */
$sql = "
    SELECT o.*, p.name AS property_name, p.code AS property_code, g.firstname, g.lastname,
           (SELECT COALESCE(SUM(f.balance),0) FROM folios f WHERE f.order_id = o.id) AS booking_balance
    FROM orders o
    LEFT JOIN properties p ON p.id = o.property_id
    LEFT JOIN guests g ON g.id = o.guest_id
    WHERE DATE(o.check_out) = :today
    $orderPropertySql
    ORDER BY p.name ASC, o.id DESC
";
$params = $paramsBase;
$params[':today'] = $today;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$departuresToday = $stmt->fetchAll(PDO::FETCH_ASSOC);

/* Open balances */
$sql = "
    SELECT o.*, p.name AS property_name, p.code AS property_code, g.firstname, g.lastname,
           (SELECT COALESCE(SUM(f.total_charges),0) FROM folios f WHERE f.order_id = o.id) AS booking_charges,
           (SELECT COALESCE(SUM(f.total_payments),0) FROM folios f WHERE f.order_id = o.id) AS booking_payments,
           (SELECT COALESCE(SUM(f.balance),0) FROM folios f WHERE f.order_id = o.id) AS booking_balance
    FROM orders o
    LEFT JOIN properties p ON p.id = o.property_id
    LEFT JOIN guests g ON g.id = o.guest_id
    WHERE DATE(o.check_in) <= :today
      AND DATE(o.check_out) >= :today
      $orderPropertySql
    ORDER BY booking_balance DESC, o.check_out ASC
";
$params = $paramsBase;
$params[':today'] = $today;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
    if (abs((float)($row['booking_balance'] ?? 0)) > 0.00001) {
        $openBalanceBookings[] = $row;
    }
}
$summary['open_balance_bookings'] = count($openBalanceBookings);

/* Dirty rooms list */
$sql = "
    SELECT r.*, p.name AS property_name, p.code AS property_code, rt.title AS room_type
    FROM rooms r
    LEFT JOIN properties p ON p.id = r.property_id
    LEFT JOIN room_types rt ON rt.id = r.room_type_id
    WHERE COALESCE(LOWER(r.housekeeping_status), LOWER(r.status), '') = 'dirty'
    $roomPropertySql
    ORDER BY p.name ASC, r.room_no ASC
";
$stmt = $pdo->prepare($sql);
$stmt->execute($paramsBase);
$dirtyRooms = $stmt->fetchAll(PDO::FETCH_ASSOC);

/* OOO rooms list */
$sql = "
    SELECT r.*, p.name AS property_name, p.code AS property_code, rt.title AS room_type
    FROM rooms r
    LEFT JOIN properties p ON p.id = r.property_id
    LEFT JOIN room_types rt ON rt.id = r.room_type_id
    WHERE COALESCE(LOWER(r.housekeeping_status), LOWER(r.status), '') IN ('out_of_order','ooo','maintenance','blocked')
    $roomPropertySql
    ORDER BY p.name ASC, r.room_no ASC
";
$stmt = $pdo->prepare($sql);
$stmt->execute($paramsBase);
$outOfOrderRooms = $stmt->fetchAll(PDO::FETCH_ASSOC);

/* Today revenue */
$sql = "
    SELECT
        COALESCE(SUM(
            CASE
                WHEN COALESCE(rmg.adults,0) <= 0 THEN 0
                WHEN COALESCE(rmg.adults,0) = 1 THEN
                    CASE
                        WHEN COALESCE(rp.single_rate,0) > 0 THEN COALESCE(rp.single_rate,0)
                        ELSE COALESCE(rp.sharing_rate,0) + COALESCE(rp.single_supplement,0)
                    END
                ELSE
                    (COALESCE(rmg.adults,0) * COALESCE(rp.sharing_rate,0))
            END
            +
            (COALESCE(rmg.children,0) * (COALESCE(rp.sharing_rate,0) * (COALESCE(rp.child_rate_percent,0) / 100)))
        ),0) AS projected_revenue,
        COUNT(*) AS room_nights
    FROM rel_orders_prices rp
    INNER JOIN order_rooming rmg ON rmg.order_id = rp.order_id
    WHERE rp.date = :today
    $ratePropertySql
";
$params = $paramsBase;
$params[':today'] = $today;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rev = $stmt->fetch(PDO::FETCH_ASSOC);
$summary['today_revenue'] = (float)($rev['projected_revenue'] ?? 0);
$summary['today_room_nights'] = (int)($rev['room_nights'] ?? 0);
$summary['today_adr'] = $summary['today_room_nights'] > 0 ? ($summary['today_revenue'] / $summary['today_room_nights']) : 0;

/* Forecast */
$start = new DateTime($dateFrom);
$end = new DateTime($dateTo);
$endInclusive = clone $end;
$endInclusive->modify('+1 day');
$period = new DatePeriod($start, new DateInterval('P1D'), $endInclusive);

$occSum = 0;
$occCount = 0;

foreach ($period as $dt) {
    $day = $dt->format('Y-m-d');

    $sql = "
        SELECT COUNT(DISTINCT rmg.room_id)
        FROM order_rooming rmg
        INNER JOIN orders o ON o.id = rmg.order_id
        INNER JOIN rooms r ON r.id = rmg.room_id
        WHERE DATE(o.check_in) <= :day
          AND DATE(o.check_out) >= :day
          AND rmg.room_id IS NOT NULL
          $roomPropertySql
    ";
    $params = $paramsBase;
    $params[':day'] = $day;
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $occupied = (int)$stmt->fetchColumn();
    $occPct = $summary['room_stock'] > 0 ? ($occupied / $summary['room_stock']) * 100 : 0;

    $sql = "
        SELECT
            COALESCE(SUM(
                CASE
                    WHEN COALESCE(rmg.adults,0) <= 0 THEN 0
                    WHEN COALESCE(rmg.adults,0) = 1 THEN
                        CASE
                            WHEN COALESCE(rp.single_rate,0) > 0 THEN COALESCE(rp.single_rate,0)
                            ELSE COALESCE(rp.sharing_rate,0) + COALESCE(rp.single_supplement,0)
                        END
                    ELSE
                        (COALESCE(rmg.adults,0) * COALESCE(rp.sharing_rate,0))
                END
                +
                (COALESCE(rmg.children,0) * (COALESCE(rp.sharing_rate,0) * (COALESCE(rp.child_rate_percent,0) / 100)))
            ),0) AS projected_revenue,
            COUNT(*) AS room_nights
        FROM rel_orders_prices rp
        INNER JOIN order_rooming rmg ON rmg.order_id = rp.order_id
        WHERE rp.date = :day
        $ratePropertySql
    ";
    $params = $paramsBase;
    $params[':day'] = $day;
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $rev = $stmt->fetch(PDO::FETCH_ASSOC);

    $revenue = (float)($rev['projected_revenue'] ?? 0);
    $roomNights = (int)($rev['room_nights'] ?? 0);
    $adr = $roomNights > 0 ? ($revenue / $roomNights) : 0;

    $forecastRows[] = [
        'day' => $day,
        'occupied' => $occupied,
        'vacant' => max(0, $summary['room_stock'] - $occupied),
        'occ_pct' => $occPct,
        'room_nights' => $roomNights,
        'adr' => $adr,
        'revenue' => $revenue,
    ];

    $summary['forecast_revenue'] += $revenue;
    $summary['forecast_room_nights'] += $roomNights;
    $occSum += $occPct;
    $occCount++;
}

$summary['forecast_avg_adr'] = $summary['forecast_room_nights'] > 0 ? ($summary['forecast_revenue'] / $summary['forecast_room_nights']) : 0;
$summary['forecast_avg_occ'] = $occCount > 0 ? ($occSum / $occCount) : 0;
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Management Dashboard PDF</title>
<style>
    body{font-family:Arial,Helvetica,sans-serif;color:#222;font-size:12px;margin:18px}
    h1,h2,h3{margin:0 0 8px}
    .top{display:flex;justify-content:space-between;align-items:flex-start;margin-bottom:16px}
    .muted{color:#666}
    .grid{display:grid;grid-template-columns:repeat(4,1fr);gap:10px;margin-bottom:16px}
    .card{border:1px solid #ddd;border-radius:8px;padding:10px}
    .metric-label{font-size:11px;color:#666}
    .metric-value{font-size:20px;font-weight:700}
    .section{margin-top:16px}
    table{width:100%;border-collapse:collapse;margin-top:8px}
    th,td{border:1px solid #ddd;padding:6px 7px;font-size:11px;vertical-align:top}
    th{background:#f5f5f5;text-align:left}
    .text-right{text-align:right}
    .two-col{display:grid;grid-template-columns:1fr 1fr;gap:14px}
    .nowrap{white-space:nowrap}
    @media print{
        .no-print{display:none}
        body{margin:10mm}
    }
</style>
</head>
<body>
<div class="top">
    <div>
        <h1>Management Dashboard</h1>
        <div class="muted">Property: <?php echo h($propertyLabel); ?></div>
        <div class="muted">Forecast: <?php echo h($dateFrom); ?> to <?php echo h($dateTo); ?></div>
    </div>
    <div class="text-right">
        <div><strong>Date Printed:</strong> <?php echo h(date('Y-m-d H:i')); ?></div>
        <button class="no-print" onclick="window.print()">Print / Save PDF</button>
    </div>
</div>

<div class="grid">
    <div class="card"><div class="metric-label">Room Stock</div><div class="metric-value"><?php echo number_format($summary['room_stock']); ?></div></div>
    <div class="card"><div class="metric-label">Occupied Today</div><div class="metric-value"><?php echo number_format($summary['occupied_rooms_today']); ?></div></div>
    <div class="card"><div class="metric-label">Occ. Today</div><div class="metric-value"><?php echo pct_fmt($summary['today_occ_pct']); ?></div></div>
    <div class="card"><div class="metric-label">Revenue Today</div><div class="metric-value"><?php echo money_fmt($summary['today_revenue']); ?></div></div>

    <div class="card"><div class="metric-label">Arrivals Today</div><div class="metric-value"><?php echo number_format($summary['arrivals_today']); ?></div></div>
    <div class="card"><div class="metric-label">Departures Today</div><div class="metric-value"><?php echo number_format($summary['departures_today']); ?></div></div>
    <div class="card"><div class="metric-label">Open Balances</div><div class="metric-value"><?php echo number_format($summary['open_balance_bookings']); ?></div></div>
    <div class="card"><div class="metric-label">In House</div><div class="metric-value"><?php echo number_format($summary['in_house_today']); ?></div></div>

    <div class="card"><div class="metric-label">Dirty Rooms</div><div class="metric-value"><?php echo number_format($summary['dirty_rooms']); ?></div></div>
    <div class="card"><div class="metric-label">Out of Order</div><div class="metric-value"><?php echo number_format($summary['ooo_rooms']); ?></div></div>
    <div class="card"><div class="metric-label">Forecast Avg Occ.</div><div class="metric-value"><?php echo pct_fmt($summary['forecast_avg_occ']); ?></div></div>
    <div class="card"><div class="metric-label">Forecast Revenue</div><div class="metric-value"><?php echo money_fmt($summary['forecast_revenue']); ?></div></div>
</div>

<div class="two-col">
    <div class="section">
        <h3>Arrivals Today</h3>
        <table>
            <thead><tr><th>Booking</th><th>Guest</th><th>Property</th></tr></thead>
            <tbody>
            <?php if (!$arrivalsToday): ?>
                <tr><td colspan="3">No arrivals today.</td></tr>
            <?php else: foreach (array_slice($arrivalsToday, 0, 12) as $row):
                $guestName = trim(($row['firstname'] ?? '') . ' ' . ($row['lastname'] ?? ''));
                $prop = trim((string)($row['property_name'] ?? ''));
                if (!empty($row['property_code'])) $prop .= ($prop !== '' ? ' (' . $row['property_code'] . ')' : $row['property_code']);
            ?>
                <tr>
                    <td><?php echo h($row['order_no'] ?: ('#' . $row['id'])); ?></td>
                    <td><?php echo h($guestName !== '' ? $guestName : 'Guest'); ?></td>
                    <td><?php echo h($prop); ?></td>
                </tr>
            <?php endforeach; endif; ?>
            </tbody>
        </table>
    </div>

    <div class="section">
        <h3>Departures Today</h3>
        <table>
            <thead><tr><th>Booking</th><th>Guest</th><th class="text-right">Balance</th></tr></thead>
            <tbody>
            <?php if (!$departuresToday): ?>
                <tr><td colspan="3">No departures today.</td></tr>
            <?php else: foreach (array_slice($departuresToday, 0, 12) as $row):
                $guestName = trim(($row['firstname'] ?? '') . ' ' . ($row['lastname'] ?? ''));
            ?>
                <tr>
                    <td><?php echo h($row['order_no'] ?: ('#' . $row['id'])); ?></td>
                    <td><?php echo h($guestName !== '' ? $guestName : 'Guest'); ?></td>
                    <td class="text-right"><?php echo money_fmt($row['booking_balance'] ?? 0); ?></td>
                </tr>
            <?php endforeach; endif; ?>
            </tbody>
        </table>
    </div>
</div>

<div class="two-col">
    <div class="section">
        <h3>Open Folio Balances</h3>
        <table>
            <thead><tr><th>Booking</th><th>Guest</th><th class="text-right">Balance</th></tr></thead>
            <tbody>
            <?php if (!$openBalanceBookings): ?>
                <tr><td colspan="3">No open balances.</td></tr>
            <?php else: foreach (array_slice($openBalanceBookings, 0, 12) as $row):
                $guestName = trim(($row['firstname'] ?? '') . ' ' . ($row['lastname'] ?? ''));
            ?>
                <tr>
                    <td><?php echo h($row['order_no'] ?: ('#' . $row['id'])); ?></td>
                    <td><?php echo h($guestName !== '' ? $guestName : 'Guest'); ?></td>
                    <td class="text-right"><?php echo money_fmt($row['booking_balance'] ?? 0); ?></td>
                </tr>
            <?php endforeach; endif; ?>
            </tbody>
        </table>
    </div>

    <div class="section">
        <h3>Forecast Snapshot</h3>
        <table>
            <thead><tr><th>Date</th><th class="text-right">Occ.%</th><th class="text-right">ADR</th><th class="text-right">Revenue</th></tr></thead>
            <tbody>
            <?php if (!$forecastRows): ?>
                <tr><td colspan="4">No forecast rows.</td></tr>
            <?php else: foreach (array_slice($forecastRows, 0, 12) as $row): ?>
                <tr>
                    <td class="nowrap"><?php echo h($row['day']); ?></td>
                    <td class="text-right"><?php echo pct_fmt($row['occ_pct']); ?></td>
                    <td class="text-right"><?php echo money_fmt($row['adr']); ?></td>
                    <td class="text-right"><?php echo money_fmt($row['revenue']); ?></td>
                </tr>
            <?php endforeach; endif; ?>
            </tbody>
        </table>
    </div>
</div>

<div class="two-col">
    <div class="section">
        <h3>Dirty Rooms</h3>
        <table>
            <thead><tr><th>Room</th><th>Property</th><th>Type</th></tr></thead>
            <tbody>
            <?php if (!$dirtyRooms): ?>
                <tr><td colspan="3">No dirty rooms.</td></tr>
            <?php else: foreach (array_slice($dirtyRooms, 0, 12) as $row):
                $prop = trim((string)($row['property_name'] ?? ''));
                if (!empty($row['property_code'])) $prop .= ($prop !== '' ? ' (' . $row['property_code'] . ')' : $row['property_code']);
            ?>
                <tr>
                    <td><?php echo h($row['room_no'] ?? ''); ?></td>
                    <td><?php echo h($prop); ?></td>
                    <td><?php echo h($row['room_type'] ?? ''); ?></td>
                </tr>
            <?php endforeach; endif; ?>
            </tbody>
        </table>
    </div>

    <div class="section">
        <h3>Out of Order Rooms</h3>
        <table>
            <thead><tr><th>Room</th><th>Property</th><th>Type</th></tr></thead>
            <tbody>
            <?php if (!$outOfOrderRooms): ?>
                <tr><td colspan="3">No out of order rooms.</td></tr>
            <?php else: foreach (array_slice($outOfOrderRooms, 0, 12) as $row):
                $prop = trim((string)($row['property_name'] ?? ''));
                if (!empty($row['property_code'])) $prop .= ($prop !== '' ? ' (' . $row['property_code'] . ')' : $row['property_code']);
            ?>
                <tr>
                    <td><?php echo h($row['room_no'] ?? ''); ?></td>
                    <td><?php echo h($prop); ?></td>
                    <td><?php echo h($row['room_type'] ?? ''); ?></td>
                </tr>
            <?php endforeach; endif; ?>
            </tbody>
        </table>
    </div>
</div>
</body>
</html>