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

$pageTitle = 'Forecast Report';

if (!function_exists('h')) {
    function h($value): string { return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8'); }
}
if (!function_exists('money_fmt')) {
    function money_fmt($amount): string { return number_format((float)$amount, 2); }
}
if (!function_exists('forecast_table_exists')) {
    function forecast_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('forecast_column_exists')) {
    function forecast_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;
    }
}
if (!function_exists('forecast_date_range')) {
    function forecast_date_range(string $from, string $to): array {
        $dates = [];
        $start = new DateTime($from);
        $end = new DateTime($to);
        $endExclusive = (clone $end)->modify('+1 day');

        while ($start < $endExclusive) {
            $dates[] = $start->format('Y-m-d');
            $start->modify('+1 day');
        }

        return $dates;
    }
}

$errors = [];

$today = date('Y-m-d');
$defaultTo = date('Y-m-d', strtotime('+29 days'));

$dateFrom = trim((string)($_GET['date_from'] ?? $today));
$dateTo = trim((string)($_GET['date_to'] ?? $defaultTo));
$selectedPropertyId = (int)($_GET['property_id'] ?? 0);
$selectedRoomTypeId = (int)($_GET['room_type_id'] ?? 0);
$includeCancelled = (int)($_GET['include_cancelled'] ?? 0);

if ($dateFrom === '') {
    $dateFrom = $today;
}
if ($dateTo === '') {
    $dateTo = $defaultTo;
}

if (strtotime($dateFrom) === false || strtotime($dateTo) === false) {
    $errors[] = 'Invalid forecast date range selected.';
} elseif (strtotime($dateFrom) > strtotime($dateTo)) {
    $errors[] = 'Date from cannot be after date to.';
}

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

$hasFolioTransactions = !$errors && forecast_table_exists($pdo, 'folio_transactions');
$hasRoomActive = !$errors && forecast_column_exists($pdo, 'rooms', 'is_active');
$hasRoomStatus = !$errors && forecast_column_exists($pdo, 'rooms', 'status');
$hasOrderStatus = !$errors && forecast_column_exists($pdo, 'orders', 'status');
$hasOrderCancelled = !$errors && forecast_column_exists($pdo, 'orders', 'cancelled');
$hasRoomingCancelled = !$errors && forecast_column_exists($pdo, 'order_rooming', 'cancelled');
$hasRoomingProperty = !$errors && forecast_column_exists($pdo, 'order_rooming', 'property_id');
$hasOrdersCreatedAt = !$errors && forecast_column_exists($pdo, 'orders', 'created_at');
$hasRoomingRate = !$errors && forecast_column_exists($pdo, 'order_rooming', 'rate');
$hasRoomingAmount = !$errors && forecast_column_exists($pdo, 'order_rooming', 'amount');

$properties = [];
$roomTypes = [];

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

    try {
        $roomTypes = $pdo->query("
            SELECT id, title, property_id
            FROM room_types
            ORDER BY title ASC
        ")->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $errors[] = 'Could not load room types: ' . $e->getMessage();
    }
}

$dateList = !$errors ? forecast_date_range($dateFrom, $dateTo) : [];

$fromSql = $pdo->quote($dateFrom);
$toSql = $pdo->quote($dateTo);
$todaySql = $pdo->quote($today);
$last7Sql = $pdo->quote(date('Y-m-d', strtotime('-7 days')));
$last14Sql = $pdo->quote(date('Y-m-d', strtotime('-14 days')));
$last30Sql = $pdo->quote(date('Y-m-d', strtotime('-30 days')));

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

$roomWhere = " 1 = 1 ";
$roomParams = [];

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

if ($selectedRoomTypeId > 0) {
    $roomWhere .= " AND r.room_type_id = :room_type_id ";
    $roomParams[':room_type_id'] = $selectedRoomTypeId;
}

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')) ";
}

$summary = [
    'rooms_count' => 0,
    'forecast_days' => count($dateList),
    'available_room_nights' => 0,
    'booked_room_nights' => 0,
    'rooms_left_nights' => 0,
    'avg_occupancy' => 0,
    'expected_room_revenue' => 0,
    'adr_forecast' => 0,
    'revpar_forecast' => 0,
    'red_days' => 0,
    'orange_days' => 0,
    'overbooked_days' => 0,
    'pickup_7' => 0,
    'pickup_14' => 0,
    'pickup_30' => 0,
];

$forecastRows = [];
$roomTypeRows = [];
$propertyRows = [];
$pickupRows = [];
$riskRows = [];
$bookingRows = [];

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT COUNT(*) AS rooms_count
            FROM rooms r
            LEFT JOIN room_types rt ON rt.id = r.room_type_id
            WHERE $roomWhere
        ");
        $stmt->execute($roomParams);
        $summary['rooms_count'] = (int)($stmt->fetchColumn() ?: 0);
    } catch (Throwable $e) {
        $errors[] = 'Could not load room capacity: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        foreach ($dateList as $day) {
            $daySql = $pdo->quote($day);

            $where = "
                orm.check_in <= $daySql
                AND orm.check_out > $daySql
                $roomingActiveSql
                $orderActiveSql
            ";

            $params = [];

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

            if ($selectedRoomTypeId > 0) {
                $where .= " AND orm.room_type_id = :room_type_id ";
                $params[':room_type_id'] = $selectedRoomTypeId;
            }

            $revenueSelect = "0 AS expected_revenue";
            if ($hasFolioTransactions) {
                $revenueSelect = "
                    (
                        SELECT COALESCE(SUM(ft.debit), 0)
                        FROM folio_transactions ft
                        WHERE ft.order_id = o.id
                          AND ft.tx_type = 'room_charge'
                          AND DATE(ft.tx_date) = $daySql
                    ) AS expected_revenue
                ";
            } elseif ($hasRoomingAmount) {
                $revenueSelect = "COALESCE(SUM(orm.amount / NULLIF(DATEDIFF(orm.check_out, orm.check_in), 0)), 0) AS expected_revenue";
            } elseif ($hasRoomingRate) {
                $revenueSelect = "COALESCE(SUM(orm.rate), 0) AS expected_revenue";
            }

            if ($hasFolioTransactions) {
                $stmt = $pdo->prepare("
                    SELECT
                        COUNT(DISTINCT orm.id) AS booked_rooms,
                        COUNT(DISTINCT o.id) AS bookings,
                        COALESCE(SUM(rev.expected_revenue), 0) AS expected_revenue
                    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
                    LEFT JOIN (
                        SELECT
                            ft.order_id,
                            COALESCE(SUM(ft.debit), 0) AS expected_revenue
                        FROM folio_transactions ft
                        WHERE ft.tx_type = 'room_charge'
                          AND DATE(ft.tx_date) = $daySql
                        GROUP BY ft.order_id
                    ) rev ON rev.order_id = o.id
                    WHERE $where
                ");
            } else {
                $fallbackRevenueExpr = "0";
                if ($hasRoomingAmount) {
                    $fallbackRevenueExpr = "COALESCE(orm.amount / NULLIF(DATEDIFF(orm.check_out, orm.check_in), 0), 0)";
                } elseif ($hasRoomingRate) {
                    $fallbackRevenueExpr = "COALESCE(orm.rate, 0)";
                }

                $stmt = $pdo->prepare("
                    SELECT
                        COUNT(DISTINCT orm.id) AS booked_rooms,
                        COUNT(DISTINCT o.id) AS bookings,
                        COALESCE(SUM($fallbackRevenueExpr), 0) AS expected_revenue
                    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);
            $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

            $available = (int)$summary['rooms_count'];
            $booked = (int)($row['booked_rooms'] ?? 0);
            $roomsLeft = $available - $booked;
            $occupancy = $available > 0 ? ($booked / $available) * 100 : 0;
            $expectedRevenue = (float)($row['expected_revenue'] ?? 0);

            $risk = 'normal';
            if ($roomsLeft < 0) {
                $risk = 'overbooked';
                $summary['overbooked_days']++;
            } elseif ($roomsLeft < 5) {
                $risk = 'red';
                $summary['red_days']++;
            } elseif ($roomsLeft < 10) {
                $risk = 'orange';
                $summary['orange_days']++;
            }

            $forecastRows[] = [
                'forecast_date' => $day,
                'day_name' => date('D', strtotime($day)),
                'available_rooms' => $available,
                'booked_rooms' => $booked,
                'rooms_left' => $roomsLeft,
                'occupancy_percent' => $occupancy,
                'bookings' => (int)($row['bookings'] ?? 0),
                'expected_revenue' => $expectedRevenue,
                'adr' => $booked > 0 ? ($expectedRevenue / $booked) : 0,
                'revpar' => $available > 0 ? ($expectedRevenue / $available) : 0,
                'risk' => $risk,
            ];

            if ($risk !== 'normal') {
                $riskRows[] = end($forecastRows);
            }

            $summary['available_room_nights'] += $available;
            $summary['booked_room_nights'] += $booked;
            $summary['rooms_left_nights'] += max(0, $roomsLeft);
            $summary['expected_room_revenue'] += $expectedRevenue;
        }

        $summary['avg_occupancy'] = $summary['available_room_nights'] > 0
            ? ($summary['booked_room_nights'] / $summary['available_room_nights']) * 100
            : 0;

        $summary['adr_forecast'] = $summary['booked_room_nights'] > 0
            ? ($summary['expected_room_revenue'] / $summary['booked_room_nights'])
            : 0;

        $summary['revpar_forecast'] = $summary['available_room_nights'] > 0
            ? ($summary['expected_room_revenue'] / $summary['available_room_nights'])
            : 0;

    } catch (Throwable $e) {
        $errors[] = 'Could not load daily forecast: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $createdExpr = $hasOrdersCreatedAt ? "DATE(o.created_at)" : "DATE(o.check_in)";

        $pickupWhere = "
            o.check_in BETWEEN $fromSql AND $toSql
            $orderActiveSql
        ";
        $pickupParams = [];

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

        $stmt = $pdo->prepare("
            SELECT
                COALESCE(SUM(CASE WHEN $createdExpr >= $last7Sql THEN 1 ELSE 0 END), 0) AS pickup_7,
                COALESCE(SUM(CASE WHEN $createdExpr >= $last14Sql THEN 1 ELSE 0 END), 0) AS pickup_14,
                COALESCE(SUM(CASE WHEN $createdExpr >= $last30Sql THEN 1 ELSE 0 END), 0) AS pickup_30
            FROM orders o
            WHERE $pickupWhere
        ");
        $stmt->execute($pickupParams);
        $pickup = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

        $summary['pickup_7'] = (int)($pickup['pickup_7'] ?? 0);
        $summary['pickup_14'] = (int)($pickup['pickup_14'] ?? 0);
        $summary['pickup_30'] = (int)($pickup['pickup_30'] ?? 0);

        $stmt = $pdo->prepare("
            SELECT
                $createdExpr AS pickup_date,
                COUNT(DISTINCT o.id) AS bookings
            FROM orders o
            WHERE $pickupWhere
              AND $createdExpr >= $last30Sql
            GROUP BY pickup_date
            ORDER BY pickup_date ASC
        ");
        $stmt->execute($pickupParams);
        $pickupRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $pickupRows = [];
    }
}

if (!$errors) {
    try {
        $typeFilter = " 1 = 1 ";
        $typeParams = [];

        if ($selectedPropertyId > 0) {
            $typeFilter .= " AND COALESCE(r.property_id, rt.property_id) = :property_id ";
            $typeParams[':property_id'] = $selectedPropertyId;
        }
        if ($selectedRoomTypeId > 0) {
            $typeFilter .= " AND rt.id = :room_type_id ";
            $typeParams[':room_type_id'] = $selectedRoomTypeId;
        }
        if ($hasRoomActive) {
            $typeFilter .= " AND COALESCE(r.is_active, 1) = 1 ";
        }
        if ($hasRoomStatus) {
            $typeFilter .= " AND (r.status IS NULL OR r.status NOT IN ('inactive','out_of_order','blocked')) ";
        }

        $stmt = $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 $typeFilter
            GROUP BY rt.id, rt.title, property_name, property_id
            HAVING rooms_count > 0
            ORDER BY property_name ASC, rt.title ASC
        ");
        $stmt->execute($typeParams);
        $types = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($types as $type) {
            $where = "
                orm.room_type_id = :room_type_id
                AND orm.check_in <= $toSql
                AND orm.check_out > $fromSql
                $roomingActiveSql
                $orderActiveSql
            ";
            $params = [':room_type_id' => (int)$type['room_type_id']];

            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
                    COALESCE(SUM(
                        DATEDIFF(
                            LEAST(orm.check_out, DATE_ADD($toSql, INTERVAL 1 DAY)),
                            GREATEST(orm.check_in, $fromSql)
                        )
                    ), 0) AS booked_nights,
                    COUNT(DISTINCT o.id) AS bookings
                FROM order_rooming orm
                LEFT JOIN orders o ON o.id = orm.order_id
                WHERE $where
            ");
            $stmtOcc->execute($params);
            $occ = $stmtOcc->fetch(PDO::FETCH_ASSOC) ?: [];

            $available = (int)$type['rooms_count'] * count($dateList);
            $booked = (int)($occ['booked_nights'] ?? 0);

            $roomTypeRows[] = [
                'property_name' => $type['property_name'],
                'room_type' => $type['room_type'],
                'rooms_count' => (int)$type['rooms_count'],
                'available_room_nights' => $available,
                'booked_room_nights' => $booked,
                'rooms_left_nights' => max(0, $available - $booked),
                'occupancy_percent' => $available > 0 ? ($booked / $available) * 100 : 0,
                'bookings' => (int)($occ['bookings'] ?? 0),
            ];
        }
    } catch (Throwable $e) {
        $errors[] = 'Could not load room type forecast: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $detailWhere = "
            orm.check_in <= $toSql
            AND orm.check_out > $fromSql
            $roomingActiveSql
            $orderActiveSql
        ";
        $detailParams = [];

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

        $propertyJoinExpr = $hasRoomingProperty
            ? "COALESCE(orm.property_id, o.property_id, r.property_id, rt.property_id)"
            : "COALESCE(o.property_id, r.property_id, rt.property_id)";

        $stmt = $pdo->prepare("
            SELECT
                o.id AS order_id,
                o.order_no,
                orm.check_in,
                orm.check_out,
                r.room_no,
                rt.title AS room_type,
                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,
                DATEDIFF(
                    LEAST(orm.check_out, DATE_ADD($toSql, INTERVAL 1 DAY)),
                    GREATEST(orm.check_in, $fromSql)
                ) AS nights_in_range
            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 rooms r ON r.id = orm.room_id
            LEFT JOIN room_types rt ON rt.id = orm.room_type_id
            LEFT JOIN properties p ON p.id = $propertyJoinExpr
            WHERE $detailWhere
            ORDER BY orm.check_in ASC, p.name ASC, rt.title ASC, r.room_no ASC
            LIMIT 1000
        ");
        $stmt->execute($detailParams);
        $bookingRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $bookingRows = [];
    }
}

if (isset($_GET['export']) && $_GET['export'] === 'csv' && !$errors) {
    $filename = 'forecast_report_' . date('Ymd_His') . '.csv';

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

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

    fputcsv($out, ['Forecast Date', 'Day', 'Available Rooms', 'Booked Rooms', 'Rooms Left', 'Occupancy %', 'Bookings', 'Expected Revenue', 'ADR', 'RevPAR', 'Risk']);

    foreach ($forecastRows as $row) {
        fputcsv($out, [
            $row['forecast_date'],
            $row['day_name'],
            $row['available_rooms'],
            $row['booked_rooms'],
            $row['rooms_left'],
            number_format((float)$row['occupancy_percent'], 2, '.', ''),
            $row['bookings'],
            number_format((float)$row['expected_revenue'], 2, '.', ''),
            number_format((float)$row['adr'], 2, '.', ''),
            number_format((float)$row['revpar'], 2, '.', ''),
            $row['risk'],
        ]);
    }

    fputcsv($out, []);
    fputcsv($out, ['Room Type Forecast']);
    fputcsv($out, ['Property', 'Room Type', 'Rooms', 'Available Nights', 'Booked Nights', 'Rooms Left Nights', 'Occupancy %', 'Bookings']);

    foreach ($roomTypeRows as $row) {
        fputcsv($out, [
            $row['property_name'],
            $row['room_type'],
            $row['rooms_count'],
            $row['available_room_nights'],
            $row['booked_room_nights'],
            $row['rooms_left_nights'],
            number_format((float)$row['occupancy_percent'], 2, '.', ''),
            $row['bookings'],
        ]);
    }

    fclose($out);
    exit;
}

$chartDates = array_map(fn($r) => date('d M', strtotime($r['forecast_date'])), $forecastRows);
$chartOccupancy = array_map(fn($r) => round((float)$r['occupancy_percent'], 2), $forecastRows);
$chartRevenue = array_map(fn($r) => round((float)$r['expected_revenue'], 2), $forecastRows);
$chartRoomsLeft = array_map(fn($r) => (int)$r['rooms_left'], $forecastRows);
$chartPickupLabels = array_map(fn($r) => date('d M', strtotime($r['pickup_date'])), $pickupRows);
$chartPickupData = array_map(fn($r) => (int)$r['bookings'], $pickupRows);

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-calendar-range"></i> Forward-Looking Intelligence
            </div>
            <div class="hero-title mt-2">Forecast Report</div>
            <div class="hero-subtitle mt-2">
                Predict occupancy, rooms left, expected room revenue, ADR, RevPAR and booking pickup for future dates.
            </div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <a href="forecast_report.php?<?php echo h(http_build_query(array_merge($_GET, ['export' => 'csv']))); ?>" class="btn btn-light">
                <i class="bi bi-download me-2"></i>Export CSV
            </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-2">
                <label class="form-label">Date From</label>
                <input type="date" name="date_from" class="form-control" value="<?php echo h($dateFrom); ?>">
            </div>

            <div class="col-md-2">
                <label class="form-label">Date To</label>
                <input type="date" name="date_to" class="form-control" value="<?php echo h($dateTo); ?>">
            </div>

            <div class="col-md-3">
                <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-3">
                <label class="form-label">Room Type</label>
                <select name="room_type_id" class="form-select">
                    <option value="0">All room types</option>
                    <?php foreach ($roomTypes as $rt): ?>
                        <option value="<?php echo (int)$rt['id']; ?>" <?php echo $selectedRoomTypeId === (int)$rt['id'] ? 'selected' : ''; ?>>
                            <?php echo h($rt['title']); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>

            <div class="col-md-1">
                <label class="form-label">Cancelled</label>
                <select name="include_cancelled" class="form-select">
                    <option value="0" <?php echo $includeCancelled === 0 ? 'selected' : ''; ?>>No</option>
                    <option value="1" <?php echo $includeCancelled === 1 ? 'selected' : ''; ?>>Yes</option>
                </select>
            </div>

            <div class="col-md-1 d-grid">
                <button class="btn btn-primary">
                    <i class="bi bi-funnel"></i>
                </button>
            </div>
        </form>
    </div>
</div>

<div class="row g-4 mb-4">
    <div class="col-md-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-primary-subtle text-primary"><i class="bi bi-door-open"></i></div>
            <div>
                <div class="metric-label">Rooms</div>
                <div class="metric-value"><?php echo (int)$summary['rooms_count']; ?></div>
            </div>
        </div>
    </div>

    <div class="col-md-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-success-subtle text-success"><i class="bi bi-house-check"></i></div>
            <div>
                <div class="metric-label">Booked Nights</div>
                <div class="metric-value"><?php echo (int)$summary['booked_room_nights']; ?></div>
            </div>
        </div>
    </div>

    <div class="col-md-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-warning-subtle text-warning"><i class="bi bi-percent"></i></div>
            <div>
                <div class="metric-label">Avg Occupancy</div>
                <div class="metric-value"><?php echo money_fmt($summary['avg_occupancy']); ?>%</div>
            </div>
        </div>
    </div>

    <div class="col-md-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-info-subtle text-info"><i class="bi bi-cash-coin"></i></div>
            <div>
                <div class="metric-label">Expected Revenue</div>
                <div class="metric-value"><?php echo money_fmt($summary['expected_room_revenue']); ?></div>
            </div>
        </div>
    </div>

    <div class="col-md-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-danger-subtle text-danger"><i class="bi bi-exclamation-triangle"></i></div>
            <div>
                <div class="metric-label">Risk Days</div>
                <div class="metric-value"><?php echo (int)($summary['red_days'] + $summary['overbooked_days']); ?></div>
            </div>
        </div>
    </div>

    <div class="col-md-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-secondary-subtle text-secondary"><i class="bi bi-graph-up-arrow"></i></div>
            <div>
                <div class="metric-label">Pickup 30 Days</div>
                <div class="metric-value"><?php echo (int)$summary['pickup_30']; ?></div>
            </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">Occupancy Forecast</h5></div>
            <div class="card-body chart-box">
                <canvas id="occupancyForecastChart"></canvas>
            </div>
        </div>
    </div>

    <div class="col-xl-4">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Booking Pickup</h5></div>
            <div class="card-body">
                <div class="mini-stat mb-3"><span>Last 7 Days</span><strong><?php echo (int)$summary['pickup_7']; ?></strong></div>
                <div class="mini-stat mb-3"><span>Last 14 Days</span><strong><?php echo (int)$summary['pickup_14']; ?></strong></div>
                <div class="mini-stat mb-3"><span>Last 30 Days</span><strong><?php echo (int)$summary['pickup_30']; ?></strong></div>
                <div class="small text-muted">Pickup is based on booking created date when available, otherwise check-in date is used.</div>
            </div>
        </div>
    </div>

    <div class="col-xl-8">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Expected Room Revenue Forecast</h5></div>
            <div class="card-body chart-box">
                <canvas id="revenueForecastChart"></canvas>
            </div>
        </div>
    </div>

    <div class="col-xl-4">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Rooms Left Forecast</h5></div>
            <div class="card-body chart-box">
                <canvas id="roomsLeftChart"></canvas>
            </div>
        </div>
    </div>

    <div class="col-12">
        <div class="card panel-card">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h5 class="mb-0">Risk Alerts</h5>
                <span class="text-muted small">Red: fewer than 5 rooms left. Orange: fewer than 10 rooms left.</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 class="text-end">Available</th>
                        <th class="text-end">Booked</th>
                        <th class="text-end">Rooms Left</th>
                        <th class="text-end">Occupancy</th>
                        <th class="text-end">Expected Revenue</th>
                        <th>Risk</th>
                    </tr>
                    </thead>
                    <tbody>
                    <?php if (!$riskRows): ?>
                        <tr><td colspan="8" class="text-center text-muted py-4">No high-risk forecast days.</td></tr>
                    <?php else: ?>
                        <?php foreach ($riskRows as $row): ?>
                            <tr class="<?php echo $row['risk'] === 'overbooked' || $row['risk'] === 'red' ? 'table-danger' : 'table-warning'; ?>">
                                <td><strong><?php echo h($row['forecast_date']); ?></strong></td>
                                <td><?php echo h($row['day_name']); ?></td>
                                <td class="text-end"><?php echo (int)$row['available_rooms']; ?></td>
                                <td class="text-end fw-bold"><?php echo (int)$row['booked_rooms']; ?></td>
                                <td class="text-end fw-bold"><?php echo (int)$row['rooms_left']; ?></td>
                                <td class="text-end"><?php echo money_fmt($row['occupancy_percent']); ?>%</td>
                                <td class="text-end"><?php echo money_fmt($row['expected_revenue']); ?></td>
                                <td><span class="badge <?php echo $row['risk'] === 'orange' ? 'text-bg-warning' : 'text-bg-danger'; ?>"><?php echo h(ucwords($row['risk'])); ?></span></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">Daily Forecast</h5></div>
            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-modern align-middle mb-0">
                        <thead>
                        <tr>
                            <th>Date</th>
                            <th>Day</th>
                            <th class="text-end">Available</th>
                            <th class="text-end">Booked</th>
                            <th class="text-end">Rooms Left</th>
                            <th class="text-end">Occupancy %</th>
                            <th class="text-end">Bookings</th>
                            <th class="text-end">Expected Revenue</th>
                            <th class="text-end">ADR</th>
                            <th class="text-end">RevPAR</th>
                        </tr>
                        </thead>
                        <tbody>
                        <?php if (!$forecastRows): ?>
                            <tr><td colspan="10" class="text-center text-muted py-4">No forecast data found.</td></tr>
                        <?php else: ?>
                            <?php foreach ($forecastRows as $row): ?>
                                <?php
                                $trClass = '';
                                if ($row['risk'] === 'overbooked' || $row['risk'] === 'red') {
                                    $trClass = 'table-danger';
                                } elseif ($row['risk'] === 'orange') {
                                    $trClass = 'table-warning';
                                }
                                ?>
                                <tr class="<?php echo $trClass; ?>">
                                    <td><strong><?php echo h($row['forecast_date']); ?></strong></td>
                                    <td><?php echo h($row['day_name']); ?></td>
                                    <td class="text-end"><?php echo (int)$row['available_rooms']; ?></td>
                                    <td class="text-end fw-bold"><?php echo (int)$row['booked_rooms']; ?></td>
                                    <td class="text-end fw-bold"><?php echo (int)$row['rooms_left']; ?></td>
                                    <td class="text-end"><?php echo money_fmt($row['occupancy_percent']); ?>%</td>
                                    <td class="text-end"><?php echo (int)$row['bookings']; ?></td>
                                    <td class="text-end fw-bold"><?php echo money_fmt($row['expected_revenue']); ?></td>
                                    <td class="text-end"><?php echo money_fmt($row['adr']); ?></td>
                                    <td class="text-end"><?php echo money_fmt($row['revpar']); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <div class="col-xl-7">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Room Type Forecast</h5></div>
            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-modern align-middle mb-0">
                        <thead>
                        <tr>
                            <th>Property</th>
                            <th>Room Type</th>
                            <th class="text-end">Rooms</th>
                            <th class="text-end">Booked Nights</th>
                            <th class="text-end">Occ %</th>
                        </tr>
                        </thead>
                        <tbody>
                        <?php if (!$roomTypeRows): ?>
                            <tr><td colspan="5" class="text-center text-muted py-4">No room type data.</td></tr>
                        <?php else: ?>
                            <?php foreach ($roomTypeRows as $row): ?>
                                <tr>
                                    <td><?php echo h($row['property_name']); ?></td>
                                    <td><strong><?php echo h($row['room_type']); ?></strong></td>
                                    <td class="text-end"><?php echo (int)$row['rooms_count']; ?></td>
                                    <td class="text-end fw-bold"><?php echo (int)$row['booked_room_nights']; ?></td>
                                    <td class="text-end"><?php echo money_fmt($row['occupancy_percent']); ?>%</td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <div class="col-xl-5">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Bookings in Forecast Range</h5></div>
            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-modern align-middle mb-0">
                        <thead>
                        <tr>
                            <th>Booking</th>
                            <th>Guest</th>
                            <th>Room</th>
                            <th class="text-end">Nights</th>
                        </tr>
                        </thead>
                        <tbody>
                        <?php if (!$bookingRows): ?>
                            <tr><td colspan="4" class="text-center text-muted py-4">No future booking details.</td></tr>
                        <?php else: ?>
                            <?php foreach ($bookingRows as $row): ?>
                                <tr>
                                    <td><a href="/rooms/modules/reservations/reservation_view.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><?php echo h(($row['room_no'] ?: 'Unassigned') . ' / ' . ($row['room_type'] ?: 'Room Type')); ?></td>
                                    <td class="text-end fw-bold"><?php echo max(0, (int)$row['nights_in_range']); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<style>
.chart-box{position:relative;min-height:315px}
.chart-box canvas{width:100%!important;height:285px!important}
.mini-stat{display:flex;align-items:center;justify-content:space-between;border:1px solid rgba(15,23,42,.08);border-radius:16px;padding:14px 16px;background:#fff}
.mini-stat span{font-weight:700;color:#64748b}
.mini-stat strong{font-size:24px;font-weight:900;color:#111827}
</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 baseOptions = {
        responsive: true,
        maintainAspectRatio: false,
        plugins: {
            legend: { labels: { boxWidth: 12, usePointStyle: true } }
        }
    };

    new Chart(document.getElementById('occupancyForecastChart'), {
        type: 'line',
        data: {
            labels: <?php echo json_encode($chartDates); ?>,
            datasets: [{
                label: 'Occupancy %',
                data: <?php echo json_encode($chartOccupancy); ?>,
                tension: .35,
                fill: true,
                borderWidth: 3,
                pointRadius: 3
            }]
        },
        options: {
            ...baseOptions,
            scales: {
                y: {
                    beginAtZero: true,
                    max: 100,
                    ticks: { callback: value => value + '%' }
                }
            },
            plugins: {
                ...baseOptions.plugins,
                tooltip: {
                    callbacks: {
                        label: ctx => 'Occupancy: ' + Number(ctx.raw || 0).toFixed(2) + '%'
                    }
                }
            }
        }
    });

    new Chart(document.getElementById('revenueForecastChart'), {
        type: 'bar',
        data: {
            labels: <?php echo json_encode($chartDates); ?>,
            datasets: [{
                label: 'Expected Revenue',
                data: <?php echo json_encode($chartRevenue); ?>,
                borderWidth: 1,
                borderRadius: 8
            }]
        },
        options: {
            ...baseOptions,
            scales: {
                y: {
                    beginAtZero: true,
                    ticks: { callback: value => money(value) }
                }
            },
            plugins: {
                ...baseOptions.plugins,
                tooltip: {
                    callbacks: {
                        label: ctx => 'Expected Revenue: ' + money(ctx.raw)
                    }
                }
            }
        }
    });

    new Chart(document.getElementById('roomsLeftChart'), {
        type: 'line',
        data: {
            labels: <?php echo json_encode($chartDates); ?>,
            datasets: [{
                label: 'Rooms Left',
                data: <?php echo json_encode($chartRoomsLeft); ?>,
                tension: .35,
                fill: true,
                borderWidth: 3,
                pointRadius: 3
            }]
        },
        options: {
            ...baseOptions,
            scales: {
                y: { beginAtZero: true }
            }
        }
    });
});
</script>

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