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

$pageTitle = 'Occupancy 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('occ_table_exists')) {
    function occ_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('occ_column_exists')) {
    function occ_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('build_date_range')) {
    function build_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');
$monthStart = date('Y-m-01');

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

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

if (strtotime($dateFrom) === false || strtotime($dateTo) === false) {
    $errors[] = 'Invalid 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 (!occ_table_exists($pdo, $table)) {
        $errors[] = 'Required table missing: ' . $table;
    }
}

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

$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 = [];
if (!$errors) {
    $dateList = build_date_range($dateFrom, $dateTo);
}

$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 = [
    'available_room_nights' => 0,
    'occupied_room_nights' => 0,
    'vacant_room_nights' => 0,
    'occupancy_percent' => 0,
    'rooms_count' => 0,
    'bookings_count' => 0,
    'adults' => 0,
    'children' => 0,
    'report_available_rooms' => 0,
    'report_occupied_rooms' => 0,
    'report_stayover_rooms' => 0,
    'report_in_house_pax' => 0,
    'previous_lunch_covers' => 0,
    'previous_dinner_covers' => 0,
    'previous_total_covers' => 0,
    'room_revenue' => 0.0,
    'restaurant_revenue' => 0.0,
    'other_revenue' => 0.0,
    'total_revenue' => 0.0,
    'adr' => 0.0,
    'revpar' => 0.0,
];

$dailyRows = [];
$roomTypeRows = [];
$propertyRows = [];
$detailRows = [];
$roomStatusRows = [];
$roomStatusDate = $dateTo;
$previousCoversDate = date('Y-m-d', strtotime('-1 day', strtotime($roomStatusDate)));
$lunchCoverWindow = '11:00-16:59';
$dinnerCoverWindow = '17:00-23:59';

/*
   Restaurant covers from the day before the selected report end date.
   This helps front office see expected breakfast/room pressure today using
   yesterday's lunch and dinner trade. Uses restaurant_orders.covers where
   present; otherwise falls back to counting checks.
*/
if (!$errors && occ_table_exists($pdo, 'restaurant_orders')) {
    try {
        $hasRestCovers = occ_column_exists($pdo, 'restaurant_orders', 'covers');
        $hasRestOpenedAt = occ_column_exists($pdo, 'restaurant_orders', 'opened_at');
        $hasRestCreatedAt = occ_column_exists($pdo, 'restaurant_orders', 'created_at');
        $hasRestStatus = occ_column_exists($pdo, 'restaurant_orders', 'status');
        $hasRestProperty = occ_column_exists($pdo, 'restaurant_orders', 'property_id');

        $dateColumn = $hasRestOpenedAt ? 'opened_at' : ($hasRestCreatedAt ? 'created_at' : 'NULL');

        if ($dateColumn !== 'NULL') {
            $coverExpr = $hasRestCovers ? 'COALESCE(covers, 1)' : '1';
            $where = "DATE({$dateColumn}) = :covers_date";
            $params = [':covers_date' => $previousCoversDate];

            if ($hasRestStatus && !$includeCancelled) {
                $where .= " AND LOWER(COALESCE(status, '')) NOT IN ('void','cancelled','canceled','deleted')";
            }

            if ($hasRestProperty && $selectedPropertyId > 0) {
                $where .= " AND property_id = :covers_property_id";
                $params[':covers_property_id'] = $selectedPropertyId;
            }

            $stmt = $pdo->prepare("
                SELECT
                    COALESCE(SUM(CASE
                        WHEN TIME({$dateColumn}) >= '11:00:00' AND TIME({$dateColumn}) < '17:00:00'
                        THEN {$coverExpr} ELSE 0 END), 0) AS lunch_covers,
                    COALESCE(SUM(CASE
                        WHEN TIME({$dateColumn}) >= '17:00:00' AND TIME({$dateColumn}) <= '23:59:59'
                        THEN {$coverExpr} ELSE 0 END), 0) AS dinner_covers
                FROM restaurant_orders
                WHERE {$where}
            ");
            $stmt->execute($params);
            $coverRow = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

            $summary['previous_lunch_covers'] = (int)($coverRow['lunch_covers'] ?? 0);
            $summary['previous_dinner_covers'] = (int)($coverRow['dinner_covers'] ?? 0);
            $summary['previous_total_covers'] = $summary['previous_lunch_covers'] + $summary['previous_dinner_covers'];
        }
    } catch (Throwable $e) {
        /* Covers are helpful but should not break the room occupancy report. */
        $summary['previous_lunch_covers'] = 0;
        $summary['previous_dinner_covers'] = 0;
        $summary['previous_total_covers'] = 0;
    }
}


$revenueByDate = [];
foreach ($dateList as $d) {
    $revenueByDate[$d] = [
        'room_revenue' => 0.0,
        'restaurant_revenue' => 0.0,
        'other_revenue' => 0.0,
        'total_revenue' => 0.0,
    ];
}

/*
   Revenue analysis for the occupancy report.
   - Accommodation revenue is read from rel_orders_prices by stay date.
   - Restaurant revenue is read from restaurant_orders by opened/created date.
   - Other revenue is read from folio_transactions charges that are not payments,
     accommodation room charges, or restaurant charges.
   Missing tables/columns are ignored so the report remains usable on older installs.
*/
if (!$errors && $dateList) {
    try {
        if (occ_table_exists($pdo, 'rel_orders_prices') && occ_column_exists($pdo, 'rel_orders_prices', 'date') && occ_column_exists($pdo, 'rel_orders_prices', 'total')) {
            $roomRevenueWhere = "rp.date BETWEEN :rev_from AND :rev_to";
            $roomRevenueParams = [':rev_from' => $dateFrom, ':rev_to' => $dateTo];

            if (occ_table_exists($pdo, 'orders')) {
                if ($selectedPropertyId > 0 && occ_column_exists($pdo, 'orders', 'property_id')) {
                    $roomRevenueWhere .= " AND o.property_id = :rev_property_id";
                    $roomRevenueParams[':rev_property_id'] = $selectedPropertyId;
                }
                if ($selectedRoomTypeId > 0) {
                    if (occ_table_exists($pdo, 'order_rooming') && occ_column_exists($pdo, 'order_rooming', 'room_type_id')) {
                        $roomRevenueWhere .= " AND (o.room_type_id = :rev_room_type_id OR EXISTS (SELECT 1 FROM order_rooming rr WHERE rr.order_id = o.id AND rr.room_type_id = :rev_room_type_id_exists))";
                        $roomRevenueParams[':rev_room_type_id'] = $selectedRoomTypeId;
                        $roomRevenueParams[':rev_room_type_id_exists'] = $selectedRoomTypeId;
                    } elseif (occ_column_exists($pdo, 'orders', 'room_type_id')) {
                        $roomRevenueWhere .= " AND o.room_type_id = :rev_room_type_id";
                        $roomRevenueParams[':rev_room_type_id'] = $selectedRoomTypeId;
                    }
                }
                if (!$includeCancelled) {
                    if ($hasOrderCancelled) {
                        $roomRevenueWhere .= " AND COALESCE(o.cancelled, 0) = 0";
                    }
                    if ($hasOrderStatus) {
                        $roomRevenueWhere .= " AND LOWER(COALESCE(o.status, '')) NOT IN ('cancelled','canceled','void','deleted')";
                    }
                }
            }

            $stmtRev = $pdo->prepare("
                SELECT rp.date AS revenue_date, COALESCE(SUM(COALESCE(rp.total,0)),0) AS revenue_amount
                FROM rel_orders_prices rp
                LEFT JOIN orders o ON o.id = rp.order_id
                WHERE {$roomRevenueWhere}
                GROUP BY rp.date
            ");
            $stmtRev->execute($roomRevenueParams);
            foreach ($stmtRev->fetchAll(PDO::FETCH_ASSOC) as $rr) {
                $d = (string)($rr['revenue_date'] ?? '');
                if (isset($revenueByDate[$d])) {
                    $revenueByDate[$d]['room_revenue'] = (float)($rr['revenue_amount'] ?? 0);
                }
            }
        }
    } catch (Throwable $e) {
        /* Accommodation revenue is optional for this report. */
    }

    try {
        if (occ_table_exists($pdo, 'restaurant_orders') && occ_column_exists($pdo, 'restaurant_orders', 'total_amount')) {
            $hasRestOpenedAt = occ_column_exists($pdo, 'restaurant_orders', 'opened_at');
            $hasRestCreatedAt = occ_column_exists($pdo, 'restaurant_orders', 'created_at');
            $hasRestStatus = occ_column_exists($pdo, 'restaurant_orders', 'status');
            $hasRestProperty = occ_column_exists($pdo, 'restaurant_orders', 'property_id');
            $restDateColumn = $hasRestOpenedAt ? 'opened_at' : ($hasRestCreatedAt ? 'created_at' : 'NULL');

            if ($restDateColumn !== 'NULL') {
                $restWhere = "DATE({$restDateColumn}) BETWEEN :rest_from AND :rest_to";
                $restParams = [':rest_from' => $dateFrom, ':rest_to' => $dateTo];

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

                $stmtRev = $pdo->prepare("
                    SELECT DATE({$restDateColumn}) AS revenue_date, COALESCE(SUM(COALESCE(total_amount,0)),0) AS revenue_amount
                    FROM restaurant_orders
                    WHERE {$restWhere}
                    GROUP BY DATE({$restDateColumn})
                ");
                $stmtRev->execute($restParams);
                foreach ($stmtRev->fetchAll(PDO::FETCH_ASSOC) as $rr) {
                    $d = (string)($rr['revenue_date'] ?? '');
                    if (isset($revenueByDate[$d])) {
                        $revenueByDate[$d]['restaurant_revenue'] = (float)($rr['revenue_amount'] ?? 0);
                    }
                }
            }
        }
    } catch (Throwable $e) {
        /* Restaurant revenue is optional for this report. */
    }

    try {
        if (occ_table_exists($pdo, 'folio_transactions') && occ_column_exists($pdo, 'folio_transactions', 'debit')) {
            $folioDateColumn = occ_column_exists($pdo, 'folio_transactions', 'tx_date') ? 'tx_date' : (occ_column_exists($pdo, 'folio_transactions', 'transaction_date') ? 'transaction_date' : (occ_column_exists($pdo, 'folio_transactions', 'created_at') ? 'created_at' : 'NULL'));
            if ($folioDateColumn !== 'NULL') {
                $otherWhere = "DATE(ft.{$folioDateColumn}) BETWEEN :other_from AND :other_to AND COALESCE(ft.debit,0) > 0";
                $otherParams = [':other_from' => $dateFrom, ':other_to' => $dateTo];

                if (occ_column_exists($pdo, 'folio_transactions', 'is_voided')) {
                    $otherWhere .= " AND COALESCE(ft.is_voided,0) = 0";
                }
                if (occ_column_exists($pdo, 'folio_transactions', 'tx_type')) {
                    $otherWhere .= " AND LOWER(COALESCE(ft.tx_type,'')) NOT IN ('payment','receipt','deposit','payment_received','folio_payment','guest_payment','room_charge','accommodation','accommodation_charge','restaurant','restaurant_split','restaurant_bill_splits')";
                }
                if ($selectedPropertyId > 0) {
                    if (occ_column_exists($pdo, 'folio_transactions', 'property_id')) {
                        $otherWhere .= " AND ft.property_id = :other_property_id";
                        $otherParams[':other_property_id'] = $selectedPropertyId;
                    } elseif (occ_table_exists($pdo, 'folios') && occ_table_exists($pdo, 'orders')) {
                        $otherWhere .= " AND o.property_id = :other_property_id";
                        $otherParams[':other_property_id'] = $selectedPropertyId;
                    }
                }

                $stmtRev = $pdo->prepare("
                    SELECT DATE(ft.{$folioDateColumn}) AS revenue_date, COALESCE(SUM(COALESCE(ft.debit,0)),0) AS revenue_amount
                    FROM folio_transactions ft
                    LEFT JOIN folios f ON f.id = ft.folio_id
                    LEFT JOIN orders o ON o.id = f.order_id
                    WHERE {$otherWhere}
                    GROUP BY DATE(ft.{$folioDateColumn})
                ");
                $stmtRev->execute($otherParams);
                foreach ($stmtRev->fetchAll(PDO::FETCH_ASSOC) as $rr) {
                    $d = (string)($rr['revenue_date'] ?? '');
                    if (isset($revenueByDate[$d])) {
                        $revenueByDate[$d]['other_revenue'] = (float)($rr['revenue_amount'] ?? 0);
                    }
                }
            }
        }
    } catch (Throwable $e) {
        /* Other revenue is optional for this report. */
    }

    foreach ($revenueByDate as $d => $rev) {
        $revenueByDate[$d]['total_revenue'] = (float)$rev['room_revenue'] + (float)$rev['restaurant_revenue'] + (float)$rev['other_revenue'];
    }
}

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);
        $roomsCount = (int)($stmt->fetchColumn() ?: 0);

        foreach ($dateList as $day) {
            $daySql = $pdo->quote($day);

            $bookingWhere = "
                orm.check_in <= $daySql
                AND orm.check_out > $daySql
            ";

            $params = [];

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

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

            if (!$includeCancelled) {
                if ($hasOrderCancelled) {
                    $bookingWhere .= " AND COALESCE(o.cancelled, 0) = 0 ";
                }

                if ($hasRoomingCancelled) {
                    $bookingWhere .= " AND COALESCE(orm.cancelled, 0) = 0 ";
                }

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

            $stmt = $pdo->prepare("
                SELECT
                    COUNT(DISTINCT orm.id) AS occupied_rooms,
                    COUNT(DISTINCT o.id) AS bookings_count,
                    COALESCE(SUM(orm.adults), 0) AS adults,
                    COALESCE(SUM(orm.children), 0) AS children
                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 $bookingWhere
            ");
            $stmt->execute($params);
            $occ = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

            $available = $roomsCount;
            $occupied = (int)($occ['occupied_rooms'] ?? 0);

            if ($occupied > $available && $available > 0) {
                $occupied = $available;
            }

            $vacant = max(0, $available - $occupied);
            $occPct = $available > 0 ? ($occupied / $available) * 100 : 0;

            $dailyRows[] = [
                'occ_date' => $day,
                'available_rooms' => $available,
                'occupied_rooms' => $occupied,
                'vacant_rooms' => $vacant,
                'occupancy_percent' => $occPct,
                'bookings_count' => (int)($occ['bookings_count'] ?? 0),
                'adults' => (int)($occ['adults'] ?? 0),
                'children' => (int)($occ['children'] ?? 0),
                'room_revenue' => round((float)($revenueByDate[$day]['room_revenue'] ?? 0), 2),
                'restaurant_revenue' => round((float)($revenueByDate[$day]['restaurant_revenue'] ?? 0), 2),
                'other_revenue' => round((float)($revenueByDate[$day]['other_revenue'] ?? 0), 2),
                'total_revenue' => round((float)($revenueByDate[$day]['total_revenue'] ?? 0), 2),
                'adr' => $occupied > 0 ? round(((float)($revenueByDate[$day]['room_revenue'] ?? 0) / $occupied), 2) : 0,
                'revpar' => $available > 0 ? round(((float)($revenueByDate[$day]['room_revenue'] ?? 0) / $available), 2) : 0,
            ];

            $summary['room_revenue'] += (float)($revenueByDate[$day]['room_revenue'] ?? 0);
            $summary['restaurant_revenue'] += (float)($revenueByDate[$day]['restaurant_revenue'] ?? 0);
            $summary['other_revenue'] += (float)($revenueByDate[$day]['other_revenue'] ?? 0);
            $summary['total_revenue'] += (float)($revenueByDate[$day]['total_revenue'] ?? 0);
            $summary['available_room_nights'] += $available;
            $summary['occupied_room_nights'] += $occupied;
            $summary['vacant_room_nights'] += $vacant;
            $summary['bookings_count'] += (int)($occ['bookings_count'] ?? 0);
            $summary['adults'] += (int)($occ['adults'] ?? 0);
            $summary['children'] += (int)($occ['children'] ?? 0);
        }

        $summary['rooms_count'] = $roomsCount;
        $summary['occupancy_percent'] = $summary['available_room_nights'] > 0
            ? ($summary['occupied_room_nights'] / $summary['available_room_nights']) * 100
            : 0;
        $summary['adr'] = $summary['occupied_room_nights'] > 0
            ? ($summary['room_revenue'] / $summary['occupied_room_nights'])
            : 0;
        $summary['revpar'] = $summary['available_room_nights'] > 0
            ? ($summary['room_revenue'] / $summary['available_room_nights'])
            : 0;
    } catch (Throwable $e) {
        $errors[] = 'Could not load daily occupancy: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $roomTypeFilter = " 1 = 1 ";
        $params = [];

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

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

        if ($hasRoomActive) {
            $roomTypeFilter .= " AND COALESCE(r.is_active, 1) = 1 ";
        }

        $stmt = $pdo->prepare("
            SELECT
                rt.id AS room_type_id,
                rt.title AS room_type,
                COALESCE(p.name, 'No Property') AS property_name,
                COUNT(r.id) AS rooms_count
            FROM room_types rt
            LEFT JOIN rooms r ON r.room_type_id = rt.id
            LEFT JOIN properties p ON p.id = COALESCE(r.property_id, rt.property_id)
            WHERE $roomTypeFilter
            GROUP BY rt.id, rt.title, property_name
            HAVING rooms_count > 0
            ORDER BY property_name ASC, rt.title ASC
        ");
        $stmt->execute($params);
        $types = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($types as $type) {
            $available = (int)$type['rooms_count'] * count($dateList);

            $fromSql = $pdo->quote($dateFrom);
            $toSql = $pdo->quote($dateTo);

            $paramsOcc = [
                ':rtid' => (int)$type['room_type_id'],
            ];

            $where = "
                orm.room_type_id = :rtid
                AND orm.check_in <= $toSql
                AND orm.check_out > $fromSql
            ";

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

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

            $stmtOcc = $pdo->prepare("
                SELECT
                    COALESCE(SUM(
                        DATEDIFF(
                            LEAST(orm.check_out, DATE_ADD($toSql, INTERVAL 1 DAY)),
                            GREATEST(orm.check_in, $fromSql)
                        )
                    ), 0) AS occupied_nights,
                    COUNT(DISTINCT o.id) AS bookings_count,
                    COALESCE(SUM(orm.adults), 0) AS adults,
                    COALESCE(SUM(orm.children), 0) AS children
                FROM order_rooming orm
                LEFT JOIN orders o ON o.id = orm.order_id
                WHERE $where
            ");
            $stmtOcc->execute($paramsOcc);
            $occ = $stmtOcc->fetch(PDO::FETCH_ASSOC) ?: [];

            $occupied = (int)($occ['occupied_nights'] ?? 0);
            if ($occupied > $available && $available > 0) {
                $occupied = $available;
            }

            $roomTypeRows[] = [
                'property_name' => $type['property_name'],
                'room_type' => $type['room_type'],
                'rooms_count' => (int)$type['rooms_count'],
                'available_room_nights' => $available,
                'occupied_room_nights' => $occupied,
                'vacant_room_nights' => max(0, $available - $occupied),
                'occupancy_percent' => $available > 0 ? ($occupied / $available) * 100 : 0,
                'bookings_count' => (int)($occ['bookings_count'] ?? 0),
                'adults' => (int)($occ['adults'] ?? 0),
                'children' => (int)($occ['children'] ?? 0),
                'room_revenue' => round(((float)$summary['adr'] * $occupied), 2),
                'adr' => $occupied > 0 ? round(((float)$summary['adr']), 2) : 0,
                'revpar' => $available > 0 ? round((((float)$summary['adr'] * $occupied) / $available), 2) : 0,
            ];
        }
    } catch (Throwable $e) {
        $errors[] = 'Could not load room type occupancy: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $propFilter = " 1 = 1 ";
        $params = [];

        if ($selectedPropertyId > 0) {
            $propFilter .= " AND p.id = :property_id ";
            $params[':property_id'] = $selectedPropertyId;
        }

        $stmt = $pdo->prepare("
            SELECT
                p.id AS property_id,
                p.name AS property_name,
                p.code AS property_code,
                COUNT(r.id) AS rooms_count
            FROM properties p
            LEFT JOIN rooms r ON r.property_id = p.id
            WHERE $propFilter
            GROUP BY p.id, p.name, p.code
            HAVING rooms_count > 0
            ORDER BY p.name ASC
        ");
        $stmt->execute($params);
        $props = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($props as $prop) {
            $available = (int)$prop['rooms_count'] * count($dateList);
            $fromSql = $pdo->quote($dateFrom);
            $toSql = $pdo->quote($dateTo);

            $paramsOcc = [
                ':prop' => (int)$prop['property_id'],
            ];

            if ($hasRoomingProperty) {
                $where = "COALESCE(orm.property_id, o.property_id, r.property_id) = :prop";
            } else {
                $where = "COALESCE(o.property_id, r.property_id) = :prop";
            }

            $where .= "
                AND orm.check_in <= $toSql
                AND orm.check_out > $fromSql
            ";

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

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

            $stmtOcc = $pdo->prepare("
                SELECT
                    COALESCE(SUM(
                        DATEDIFF(
                            LEAST(orm.check_out, DATE_ADD($toSql, INTERVAL 1 DAY)),
                            GREATEST(orm.check_in, $fromSql)
                        )
                    ), 0) AS occupied_nights,
                    COUNT(DISTINCT o.id) AS bookings_count
                FROM order_rooming orm
                LEFT JOIN orders o ON o.id = orm.order_id
                LEFT JOIN rooms r ON r.id = orm.room_id
                WHERE $where
            ");
            $stmtOcc->execute($paramsOcc);
            $occ = $stmtOcc->fetch(PDO::FETCH_ASSOC) ?: [];

            $occupied = (int)($occ['occupied_nights'] ?? 0);
            if ($occupied > $available && $available > 0) {
                $occupied = $available;
            }

            $propertyRows[] = [
                'property_name' => $prop['property_name'],
                'property_code' => $prop['property_code'],
                'rooms_count' => (int)$prop['rooms_count'],
                'available_room_nights' => $available,
                'occupied_room_nights' => $occupied,
                'vacant_room_nights' => max(0, $available - $occupied),
                'occupancy_percent' => $available > 0 ? ($occupied / $available) * 100 : 0,
                'bookings_count' => (int)($occ['bookings_count'] ?? 0),
                'room_revenue' => round(((float)$summary['adr'] * $occupied), 2),
                'adr' => $occupied > 0 ? round(((float)$summary['adr']), 2) : 0,
                'revpar' => $available > 0 ? round((((float)$summary['adr'] * $occupied) / $available), 2) : 0,
            ];
        }
    } catch (Throwable $e) {
        $propertyRows = [];
    }
}

if (!$errors) {
    try {
        $fromSql = $pdo->quote($dateFrom);
        $toSql = $pdo->quote($dateTo);

        $detailWhere = "
            orm.check_in <= $toSql
            AND orm.check_out > $fromSql
        ";

        $params = [];

        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 ";
            }
            $params[':property_id'] = $selectedPropertyId;
        }

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

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

        $stmt = $pdo->prepare("
            SELECT
                orm.id AS rooming_id,
                orm.order_id,
                o.order_no,
                o.check_in AS booking_check_in,
                o.check_out AS booking_check_out,
                orm.check_in,
                orm.check_out,
                orm.adults,
                orm.children,
                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 = " . ($hasRoomingProperty ? "COALESCE(orm.property_id, o.property_id, r.property_id, rt.property_id)" : "COALESCE(o.property_id, r.property_id, rt.property_id)") . "
            WHERE $detailWhere
            ORDER BY orm.check_in ASC, p.name ASC, rt.title ASC, r.room_no ASC
            LIMIT 1500
        ");
        $stmt->execute($params);
        $detailRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $errors[] = 'Could not load occupancy detail: ' . $e->getMessage();
    }
}

/*
   Room status snapshot for the selected report end date.
   This shows every physical room as Available, Occupied, or Stayover.
   A room checked in today must count as Occupied immediately, not remain only under Arrivals.
*/
if (!$errors) {
    try {
        $snapshotDateSql = $pdo->quote($roomStatusDate);

        $snapshotRoomWhere = " 1 = 1 ";
        $snapshotRoomParams = [];

        if ($selectedPropertyId > 0) {
            $snapshotRoomWhere .= " AND r.property_id = :snapshot_property_id ";
            $snapshotRoomParams[':snapshot_property_id'] = $selectedPropertyId;
        }

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

        if ($hasRoomActive) {
            $snapshotRoomWhere .= " AND COALESCE(r.is_active, 1) = 1 ";
        }

        $stmt = $pdo->prepare("
            SELECT
                r.id,
                r.room_no,
                r.property_id,
                r.room_type_id,
                r.status AS room_status,
                r.max_adults,
                r.max_children,
                r.max_occupancy,
                r.allows_extra_bed,
                r.max_extra_beds,
                r.allows_baby_cot,
                r.max_cots,
                r.wheelchair_accessible,
                r.interleading_group,
                r.balcony,
                r.bath,
                r.shower,
                r.view_type,
                r.room_configuration_notes,
                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 $snapshotRoomWhere
            ORDER BY p.name ASC, r.room_no ASC, r.id ASC
        ");
        $stmt->execute($snapshotRoomParams);
        $snapshotRooms = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($snapshotRooms as $room) {
            $assignmentParams = [
                ':room_id' => (int)$room['id'],
            ];

            $assignmentWhere = "
                orm.room_id = :room_id
                AND orm.check_in <= $snapshotDateSql
                AND orm.check_out > $snapshotDateSql
            ";

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

            $stmtOcc = $pdo->prepare("
                SELECT
                    orm.id AS rooming_id,
                    orm.order_id,
                    orm.check_in,
                    orm.check_out,
                    orm.adults,
                    orm.children,
                    COALESCE(orm.checked_in_status,0) AS checked_in_status,
                    COALESCE(orm.checked_out_status,0) AS checked_out_status,
                    o.order_no,
                    TRIM(CONCAT(COALESCE(g.firstname,''), ' ', COALESCE(g.lastname,''))) AS guest_name,
                    TRIM(CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,''))) AS agent_name
                FROM order_rooming orm
                LEFT JOIN orders o ON o.id = orm.order_id
                LEFT JOIN guests g ON g.id = o.guest_id
                LEFT JOIN agents a ON a.id = o.agent_id
                WHERE $assignmentWhere
                ORDER BY COALESCE(orm.checked_in_status,0) DESC, orm.check_in ASC, orm.id ASC
                LIMIT 1
            ");
            $stmtOcc->execute($assignmentParams);
            $assignment = $stmtOcc->fetch(PDO::FETCH_ASSOC) ?: null;

            $snapshotStatus = 'Available';
            $snapshotClass = 'success';
            $isInHouse = false;

            if ($assignment) {
                $checkedIn = (int)($assignment['checked_in_status'] ?? 0) === 1;
                $checkedOut = (int)($assignment['checked_out_status'] ?? 0) === 1;
                $checkInDay = substr((string)($assignment['check_in'] ?? ''), 0, 10);
                $checkOutDay = substr((string)($assignment['check_out'] ?? ''), 0, 10);

                if ($checkedIn && !$checkedOut) {
                    $isInHouse = true;
                    if ($checkInDay < $roomStatusDate && $checkOutDay > $roomStatusDate) {
                        $snapshotStatus = 'Stayover';
                        $snapshotClass = 'primary';
                    } else {
                        $snapshotStatus = 'Occupied';
                        $snapshotClass = 'danger';
                    }
                } else {
                    /*
                       Reserved but not checked in for the selected date.
                       It is not in-house yet, but it still blocks inventory.
                    */
                    $snapshotStatus = 'Reserved';
                    $snapshotClass = 'warning';
                }
            }

            if ($snapshotStatus === 'Available') {
                $summary['report_available_rooms']++;
            } elseif ($snapshotStatus === 'Stayover') {
                $summary['report_stayover_rooms']++;
                $summary['report_occupied_rooms']++;
            } elseif ($snapshotStatus === 'Occupied') {
                $summary['report_occupied_rooms']++;
            }

            if ($isInHouse && $assignment) {
                $summary['report_in_house_pax'] += (int)($assignment['adults'] ?? 0) + (int)($assignment['children'] ?? 0);
            }

            $roomStatusRows[] = [
                'room' => $room,
                'assignment' => $assignment,
                'snapshot_status' => $snapshotStatus,
                'snapshot_class' => $snapshotClass,
                'is_in_house' => $isInHouse,
            ];
        }
    } catch (Throwable $e) {
        $errors[] = 'Could not load room status snapshot: ' . $e->getMessage();
    }
}

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

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

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

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

    echo '<table style="width:100%;"><tr><td class="title" colspan="10">';
    echo '<h1>Occupancy Report</h1>';
    echo '<div class="subtitle">Period: ' . $xlsText($dateFrom) . ' to ' . $xlsText($dateTo) . ' | Snapshot: ' . $xlsText($roomStatusDate) . ' | Exported: ' . date('Y-m-d H:i:s') . '</div>';
    echo '</td></tr></table>';

    echo '<h2>Executive Summary</h2>';
    echo '<table class="metric"><tr>';
    echo '<th>Rooms</th><th>Available Nights</th><th>Occupied Nights</th><th>Vacant Nights</th><th>Occupancy %</th><th>Pax</th><th>Accommodation Revenue</th><th>Restaurant Revenue</th><th>Other Revenue</th><th>Total Revenue</th><th>ADR</th><th>RevPAR</th><th>Available Rooms</th><th>Occupied Rooms</th><th>Stayovers</th><th>In-House Pax</th>';
    echo '</tr><tr>';
    echo '<td class="num">' . (int)$summary['rooms_count'] . '</td>';
    echo '<td class="num">' . (int)$summary['available_room_nights'] . '</td>';
    echo '<td class="num">' . (int)$summary['occupied_room_nights'] . '</td>';
    echo '<td class="num">' . (int)$summary['vacant_room_nights'] . '</td>';
    echo '<td class="num">' . $xlsNum($summary['occupancy_percent'], 2) . '%</td>';
    echo '<td class="num">' . (int)$summary['adults'] . 'A / ' . (int)$summary['children'] . 'C</td>';
    echo '<td class="num">' . $xlsNum($summary['room_revenue'], 2) . '</td>';
    echo '<td class="num">' . $xlsNum($summary['restaurant_revenue'], 2) . '</td>';
    echo '<td class="num">' . $xlsNum($summary['other_revenue'], 2) . '</td>';
    echo '<td class="num">' . $xlsNum($summary['total_revenue'], 2) . '</td>';
    echo '<td class="num">' . $xlsNum($summary['adr'], 2) . '</td>';
    echo '<td class="num">' . $xlsNum($summary['revpar'], 2) . '</td>';
    echo '<td class="num">' . (int)$summary['report_available_rooms'] . '</td>';
    echo '<td class="num">' . (int)$summary['report_occupied_rooms'] . '</td>';
    echo '<td class="num">' . (int)$summary['report_stayover_rooms'] . '</td>';
    echo '<td class="num">' . (int)$summary['report_in_house_pax'] . '</td>';
    echo '</tr></table>';

    echo '<h2>Previous Day Restaurant Covers</h2>';
    echo '<table><tr><th>Covers Date</th><th>Lunch Covers (' . $xlsText($lunchCoverWindow) . ')</th><th>Dinner Covers (' . $xlsText($dinnerCoverWindow) . ')</th><th>Total Covers</th></tr>';
    echo '<tr><td>' . $xlsText($previousCoversDate) . '</td><td class="num">' . (int)$summary['previous_lunch_covers'] . '</td><td class="num">' . (int)$summary['previous_dinner_covers'] . '</td><td class="num">' . (int)$summary['previous_total_covers'] . '</td></tr></table>';

    echo '<h2>Occupancy Trend Chart Data</h2>';
    echo '<table><tr><th>Date</th><th>Occupancy %</th><th>Visual Bar</th><th>Occupied</th><th>Vacant</th><th>Available</th><th>Room Revenue</th><th>Total Revenue</th><th>ADR</th><th>RevPAR</th></tr>';
    foreach ($dailyRows as $row) {
        $pct = max(0, min(100, (float)$row['occupancy_percent']));
        echo '<tr><td>' . $xlsText($row['occ_date']) . '</td><td class="num">' . $xlsNum($pct, 2) . '%</td><td><div class="barwrap"><div class="bar" style="width:' . $xlsNum($pct, 2) . '%"></div></div></td><td class="num">' . (int)$row['occupied_rooms'] . '</td><td class="num">' . (int)$row['vacant_rooms'] . '</td><td class="num">' . (int)$row['available_rooms'] . '</td><td class="num">' . $xlsNum($row['room_revenue'] ?? 0, 2) . '</td><td class="num">' . $xlsNum($row['total_revenue'] ?? 0, 2) . '</td><td class="num">' . $xlsNum($row['adr'] ?? 0, 2) . '</td><td class="num">' . $xlsNum($row['revpar'] ?? 0, 2) . '</td></tr>';
    }
    echo '</table>';

    echo '<h2>Daily Occupancy</h2>';
    echo '<table><tr><th>Date</th><th>Available Rooms</th><th>Occupied Rooms</th><th>Vacant Rooms</th><th>Occupancy %</th><th>Bookings</th><th>Adults</th><th>Children</th><th>Accommodation Revenue</th><th>Restaurant Revenue</th><th>Other Revenue</th><th>Total Revenue</th><th>ADR</th><th>RevPAR</th></tr>';
    foreach ($dailyRows as $row) {
        echo '<tr><td>' . $xlsText($row['occ_date']) . '</td><td class="num">' . (int)$row['available_rooms'] . '</td><td class="num">' . (int)$row['occupied_rooms'] . '</td><td class="num">' . (int)$row['vacant_rooms'] . '</td><td class="num">' . $xlsNum($row['occupancy_percent'], 2) . '%</td><td class="num">' . (int)$row['bookings_count'] . '</td><td class="num">' . (int)$row['adults'] . '</td><td class="num">' . (int)$row['children'] . '</td><td class="num">' . $xlsNum($row['room_revenue'] ?? 0, 2) . '</td><td class="num">' . $xlsNum($row['restaurant_revenue'] ?? 0, 2) . '</td><td class="num">' . $xlsNum($row['other_revenue'] ?? 0, 2) . '</td><td class="num">' . $xlsNum($row['total_revenue'] ?? 0, 2) . '</td><td class="num">' . $xlsNum($row['adr'] ?? 0, 2) . '</td><td class="num">' . $xlsNum($row['revpar'] ?? 0, 2) . '</td></tr>';
    }
    echo '</table>';

    echo '<h2>Property Occupancy</h2>';
    echo '<table><tr><th>Property</th><th>Code</th><th>Rooms</th><th>Available Room Nights</th><th>Occupied Room Nights</th><th>Vacant Room Nights</th><th>Occupancy %</th><th>Bookings</th><th>Room Revenue</th><th>ADR</th><th>RevPAR</th></tr>';
    foreach ($propertyRows as $row) {
        echo '<tr><td>' . $xlsText($row['property_name']) . '</td><td>' . $xlsText($row['property_code']) . '</td><td class="num">' . (int)$row['rooms_count'] . '</td><td class="num">' . (int)$row['available_room_nights'] . '</td><td class="num">' . (int)$row['occupied_room_nights'] . '</td><td class="num">' . (int)$row['vacant_room_nights'] . '</td><td class="num">' . $xlsNum($row['occupancy_percent'], 2) . '%</td><td class="num">' . (int)$row['bookings_count'] . '</td><td class="num">' . $xlsNum($row['room_revenue'] ?? 0, 2) . '</td><td class="num">' . $xlsNum($row['adr'] ?? 0, 2) . '</td><td class="num">' . $xlsNum($row['revpar'] ?? 0, 2) . '</td></tr>';
    }
    echo '</table>';

    echo '<h2>Room Type Performance</h2>';
    echo '<table><tr><th>Property</th><th>Room Type</th><th>Rooms</th><th>Available Room Nights</th><th>Occupied Room Nights</th><th>Vacant Room Nights</th><th>Occupancy %</th><th>Bookings</th><th>Adults</th><th>Children</th><th>Room Revenue</th><th>ADR</th><th>RevPAR</th></tr>';
    foreach ($roomTypeRows as $row) {
        echo '<tr><td>' . $xlsText($row['property_name']) . '</td><td>' . $xlsText($row['room_type']) . '</td><td class="num">' . (int)$row['rooms_count'] . '</td><td class="num">' . (int)$row['available_room_nights'] . '</td><td class="num">' . (int)$row['occupied_room_nights'] . '</td><td class="num">' . (int)$row['vacant_room_nights'] . '</td><td class="num">' . $xlsNum($row['occupancy_percent'], 2) . '%</td><td class="num">' . (int)$row['bookings_count'] . '</td><td class="num">' . (int)$row['adults'] . '</td><td class="num">' . (int)$row['children'] . '</td><td class="num">' . $xlsNum($row['room_revenue'] ?? 0, 2) . '</td><td class="num">' . $xlsNum($row['adr'] ?? 0, 2) . '</td><td class="num">' . $xlsNum($row['revpar'] ?? 0, 2) . '</td></tr>';
    }
    echo '</table>';

    echo '<h2>Room Status Snapshot as at ' . $xlsText($roomStatusDate) . '</h2>';
    echo '<table><tr><th>Property</th><th>Room</th><th>Room Type</th><th>Status</th><th>Guest</th><th>Booking</th><th>Stay</th><th>Pax</th><th>Room Setup</th></tr>';
    foreach ($roomStatusRows as $row) {
        $room = $row['room'];
        $assignment = $row['assignment'];
        $propertyLabel = trim((string)($room['property_name'] ?? ''));
        if (!empty($room['property_code'])) {
            $propertyLabel .= ($propertyLabel !== '' ? ' (' . $room['property_code'] . ')' : $room['property_code']);
        }
        $setupBits = [];
        if (!empty($room['allows_extra_bed'])) $setupBits[] = 'Extra bed x ' . (int)($room['max_extra_beds'] ?? 0);
        if (!empty($room['allows_baby_cot'])) $setupBits[] = 'Cot x ' . (int)($room['max_cots'] ?? 0);
        if (!empty($room['wheelchair_accessible'])) $setupBits[] = 'Wheelchair';
        if (!empty($room['interleading_group'])) $setupBits[] = 'Interleading: ' . $room['interleading_group'];
        if (!empty($room['view_type'])) $setupBits[] = $room['view_type'];
        if (!empty($room['room_configuration_notes'])) $setupBits[] = $room['room_configuration_notes'];
        $statusClass = $row['snapshot_status'] === 'Available' ? 'good' : ($row['snapshot_status'] === 'Reserved' ? 'warn' : 'bad');
        echo '<tr><td>' . $xlsText($propertyLabel ?: 'Not set') . '</td><td>' . $xlsText($room['room_no'] ?? '') . '</td><td>' . $xlsText($room['room_type'] ?? 'Not set') . '</td><td class="' . $statusClass . '">' . $xlsText($row['snapshot_status']) . '</td><td>' . $xlsText($assignment['guest_name'] ?? '') . '</td><td>' . $xlsText($assignment['order_no'] ?? '') . '</td><td>' . ($assignment ? $xlsText(($assignment['check_in'] ?? '') . ' to ' . ($assignment['check_out'] ?? '')) : '') . '</td><td>' . ($assignment ? ((int)($assignment['adults'] ?? 0) . 'A / ' . (int)($assignment['children'] ?? 0) . 'C') : '') . '</td><td>' . $xlsText(implode(' • ', $setupBits)) . '</td></tr>';
    }
    echo '</table>';

    echo '<h2>Occupied Room Detail</h2>';
    echo '<table><tr><th>Property</th><th>Booking</th><th>Guest</th><th>Agent</th><th>Room</th><th>Room Type</th><th>Stay</th><th>Nights in Range</th><th>Pax</th></tr>';
    foreach ($detailRows as $row) {
        $propertyLabel = trim((string)($row['property_name'] ?? ''));
        if (!empty($row['property_code'])) $propertyLabel .= ' (' . $row['property_code'] . ')';
        echo '<tr><td>' . $xlsText($propertyLabel ?: 'Not set') . '</td><td>' . $xlsText($row['order_no'] ?: ('#' . $row['order_id'])) . '</td><td>' . $xlsText(trim((string)($row['guest_name'] ?? '')) ?: 'Guest') . '</td><td>' . $xlsText(trim((string)($row['agent_name'] ?? '')) ?: 'Direct') . '</td><td>' . $xlsText($row['room_no'] ?: 'Unassigned') . '</td><td>' . $xlsText($row['room_type'] ?: 'Not set') . '</td><td>' . $xlsText(($row['check_in'] ?? '') . ' to ' . ($row['check_out'] ?? '')) . '</td><td class="num">' . max(0, (int)$row['nights_in_range']) . '</td><td>' . (int)$row['adults'] . 'A / ' . (int)$row['children'] . 'C</td></tr>';
    }
    echo '</table>';

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

if (isset($_GET['export']) && $_GET['export'] === 'csv' && !$errors) {
    $filename = 'occupancy_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, ['Restaurant Covers from Previous Day']);
    fputcsv($out, ['Covers Date', 'Lunch Covers (' . $lunchCoverWindow . ')', 'Dinner Covers (' . $dinnerCoverWindow . ')', 'Total Covers']);
    fputcsv($out, [
        $previousCoversDate,
        (int)$summary['previous_lunch_covers'],
        (int)$summary['previous_dinner_covers'],
        (int)$summary['previous_total_covers'],
    ]);

    fputcsv($out, []);
    fputcsv($out, ['Daily Occupancy']);
    fputcsv($out, [
        'Date',
        'Available Rooms',
        'Occupied Rooms',
        'Vacant Rooms',
        'Occupancy %',
        'Bookings',
        'Adults',
        'Children',
        'Accommodation Revenue',
        'Restaurant Revenue',
        'Other Revenue',
        'Total Revenue',
        'ADR',
        'RevPAR'
    ]);

    foreach ($dailyRows as $row) {
        fputcsv($out, [
            $row['occ_date'],
            $row['available_rooms'],
            $row['occupied_rooms'],
            $row['vacant_rooms'],
            number_format((float)$row['occupancy_percent'], 2, '.', ''),
            $row['bookings_count'],
            $row['adults'],
            $row['children'],
            number_format((float)($row['room_revenue'] ?? 0), 2, '.', ''),
            number_format((float)($row['restaurant_revenue'] ?? 0), 2, '.', ''),
            number_format((float)($row['other_revenue'] ?? 0), 2, '.', ''),
            number_format((float)($row['total_revenue'] ?? 0), 2, '.', ''),
            number_format((float)($row['adr'] ?? 0), 2, '.', ''),
            number_format((float)($row['revpar'] ?? 0), 2, '.', ''),
        ]);
    }

    fputcsv($out, []);
    fputcsv($out, ['Room Type Breakdown']);
    fputcsv($out, ['Property', 'Room Type', 'Rooms', 'Available Room Nights', 'Occupied Room Nights', 'Vacant Room Nights', 'Occupancy %', 'Bookings', 'Adults', 'Children', 'Room Revenue', 'ADR', 'RevPAR']);

    foreach ($roomTypeRows as $row) {
        fputcsv($out, [
            $row['property_name'],
            $row['room_type'],
            $row['rooms_count'],
            $row['available_room_nights'],
            $row['occupied_room_nights'],
            $row['vacant_room_nights'],
            number_format((float)$row['occupancy_percent'], 2, '.', ''),
            $row['bookings_count'],
            $row['adults'],
            $row['children'],
            number_format((float)($row['room_revenue'] ?? 0), 2, '.', ''),
            number_format((float)($row['adr'] ?? 0), 2, '.', ''),
            number_format((float)($row['revpar'] ?? 0), 2, '.', ''),
        ]);
    }

    fputcsv($out, []);
    fputcsv($out, ['Room Status Snapshot as at ' . $roomStatusDate]);
    fputcsv($out, ['Property', 'Room', 'Room Type', 'Status', 'Guest', 'Booking', 'Stay', 'Pax']);

    foreach ($roomStatusRows as $row) {
        $room = $row['room'];
        $assignment = $row['assignment'];
        $propertyLabel = trim((string)($room['property_name'] ?? ''));
        if (!empty($room['property_code'])) {
            $propertyLabel .= ($propertyLabel !== '' ? ' (' . $room['property_code'] . ')' : $room['property_code']);
        }

        fputcsv($out, [
            $propertyLabel,
            $room['room_no'] ?? '',
            $room['room_type'] ?? '',
            $row['snapshot_status'],
            $assignment['guest_name'] ?? '',
            $assignment['order_no'] ?? '',
            $assignment ? (($assignment['check_in'] ?? '') . ' to ' . ($assignment['check_out'] ?? '')) : '',
            $assignment ? ((int)($assignment['adults'] ?? 0) . 'A / ' . (int)($assignment['children'] ?? 0) . 'C') : '',
        ]);
    }

    fclose($out);
    exit;
}

require_once __DIR__ . '/../../includes/header.php';
?>
<style>
    .hero-band.occ-compact-hero {
        padding: 14px 18px !important;
        margin-bottom: 14px !important;
        border-radius: 16px !important;
    }
    .occ-compact-hero .row { row-gap: .5rem !important; }
    .occ-compact-hero .hero-title {
        font-size: 1.35rem !important;
        line-height: 1.15 !important;
        margin-top: .15rem !important;
    }
    .occ-compact-hero .hero-subtitle {
        font-size: .86rem !important;
        margin-top: .25rem !important;
        max-width: 980px;
    }
    .occ-compact-hero .page-eyebrow {
        font-size: .72rem !important;
        letter-spacing: .08em;
        text-transform: uppercase;
    }
    .occ-compact-hero .btn { padding: .42rem .75rem; }
    .room-status-scroll {
        max-height: 560px;
        overflow: auto;
    }
    .room-status-table thead th {
        position: sticky;
        top: 0;
        z-index: 5;
        background: #f8fafc;
        box-shadow: inset 0 -1px 0 rgba(15, 23, 42, .12), 0 2px 4px rgba(15, 23, 42, .06);
    }
</style>


<div class="hero-band occ-compact-hero">
    <div class="row g-4 align-items-center">
        <div class="col-lg-8">
            <div class="page-eyebrow text-white-50">
                <i class="bi bi-calendar3"></i> Rooms Performance
            </div>
            <div class="hero-title mt-2">Occupancy Report</div>
            <div class="hero-subtitle mt-2">
                Track occupied room nights, available room nights, restaurant covers from the previous day, room-type performance, property utilisation and daily occupancy.
            </div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <div class="d-flex flex-wrap gap-2 justify-content-lg-end">
                <a href="occupancy_report.php?<?php echo h(http_build_query(array_merge($_GET, ['export' => 'xls']))); ?>" class="btn btn-light">
                    <i class="bi bi-file-earmark-excel me-2"></i>Export Full Excel
                </a>
                <a href="occupancy_report.php?<?php echo h(http_build_query(array_merge($_GET, ['export' => 'csv']))); ?>" class="btn btn-outline-light">
                    <i class="bi bi-download me-2"></i>CSV
                </a>
            </div>
        </div>
    </div>
</div>

<?php if ($errors): ?>
    <div class="alert alert-danger">
        <div class="fw-bold mb-2">Please fix the following:</div>
        <ul class="mb-0">
            <?php foreach ($errors as $err): ?>
                <li><?php echo h($err); ?></li>
            <?php endforeach; ?>
        </ul>
    </div>
<?php endif; ?>

<div class="card panel-card mb-4">
    <div class="card-body">
        <form method="get" class="row g-3 align-items-end">
            <div class="col-md-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-info-subtle text-info"><i class="bi bi-calendar-check"></i></div>
            <div>
                <div class="metric-label">Available Nights</div>
                <div class="metric-value"><?php echo (int)$summary['available_room_nights']; ?></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">Occupied Nights</div>
                <div class="metric-value"><?php echo (int)$summary['occupied_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-house-dash"></i></div>
            <div>
                <div class="metric-label">Vacant Nights</div>
                <div class="metric-value"><?php echo (int)$summary['vacant_room_nights']; ?></div>
            </div>
        </div>
    </div>

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

    <div class="col-md-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-secondary-subtle text-secondary"><i class="bi bi-people"></i></div>
            <div>
                <div class="metric-label">Pax</div>
                <div class="metric-value"><?php echo (int)$summary['adults']; ?>A / <?php echo (int)$summary['children']; ?>C</div>
            </div>
        </div>
    </div>
</div>

<div class="row g-4 mb-4">
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-success-subtle text-success"><i class="bi bi-check-circle"></i></div>
            <div>
                <div class="metric-label">Available Rooms</div>
                <div class="metric-value"><?php echo (int)$summary['report_available_rooms']; ?></div>
                <div class="small text-muted">As at <?php echo h($roomStatusDate); ?></div>
            </div>
        </div>
    </div>

    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-danger-subtle text-danger"><i class="bi bi-door-closed"></i></div>
            <div>
                <div class="metric-label">Occupied Rooms</div>
                <div class="metric-value"><?php echo (int)$summary['report_occupied_rooms']; ?></div>
                <div class="small text-muted">Checked in, not checked out</div>
            </div>
        </div>
    </div>

    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-primary-subtle text-primary"><i class="bi bi-moon-stars"></i></div>
            <div>
                <div class="metric-label">Stayovers</div>
                <div class="metric-value"><?php echo (int)$summary['report_stayover_rooms']; ?></div>
                <div class="small text-muted">Checked in before this date</div>
            </div>
        </div>
    </div>

    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-secondary-subtle text-secondary"><i class="bi bi-people-fill"></i></div>
            <div>
                <div class="metric-label">In-House Pax</div>
                <div class="metric-value"><?php echo (int)$summary['report_in_house_pax']; ?></div>
                <div class="small text-muted">Adults + children in house</div>
            </div>
        </div>
    </div>
</div>

<div class="row g-4 mb-4">
    <div class="col-md-6 col-xl-4">
        <div class="metric-card">
            <div class="metric-icon bg-warning-subtle text-warning"><i class="bi bi-sun"></i></div>
            <div>
                <div class="metric-label">Lunch Covers</div>
                <div class="metric-value"><?php echo (int)$summary['previous_lunch_covers']; ?></div>
                <div class="small text-muted"><?php echo h($previousCoversDate); ?> • <?php echo h($lunchCoverWindow); ?></div>
            </div>
        </div>
    </div>

    <div class="col-md-6 col-xl-4">
        <div class="metric-card">
            <div class="metric-icon bg-dark-subtle text-dark"><i class="bi bi-moon"></i></div>
            <div>
                <div class="metric-label">Dinner Covers</div>
                <div class="metric-value"><?php echo (int)$summary['previous_dinner_covers']; ?></div>
                <div class="small text-muted"><?php echo h($previousCoversDate); ?> • <?php echo h($dinnerCoverWindow); ?></div>
            </div>
        </div>
    </div>

    <div class="col-md-6 col-xl-4">
        <div class="metric-card">
            <div class="metric-icon bg-info-subtle text-info"><i class="bi bi-cup-hot"></i></div>
            <div>
                <div class="metric-label">Previous Day Covers</div>
                <div class="metric-value"><?php echo (int)$summary['previous_total_covers']; ?></div>
                <div class="small text-muted">Lunch + dinner covers from previous day</div>
            </div>
        </div>
    </div>
</div>

<div class="row g-4 mb-4">
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-success-subtle text-success"><i class="bi bi-cash-stack"></i></div>
            <div>
                <div class="metric-label">Accommodation Revenue</div>
                <div class="metric-value"><?php echo money_fmt($summary['room_revenue']); ?></div>
                <div class="small text-muted">Room/stay revenue</div>
            </div>
        </div>
    </div>
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-warning-subtle text-warning"><i class="bi bi-cup-straw"></i></div>
            <div>
                <div class="metric-label">Restaurant Revenue</div>
                <div class="metric-value"><?php echo money_fmt($summary['restaurant_revenue']); ?></div>
                <div class="small text-muted">Restaurant orders</div>
            </div>
        </div>
    </div>
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-info-subtle text-info"><i class="bi bi-receipt"></i></div>
            <div>
                <div class="metric-label">Total Revenue</div>
                <div class="metric-value"><?php echo money_fmt($summary['total_revenue']); ?></div>
                <div class="small text-muted">Accommodation + restaurant + other</div>
            </div>
        </div>
    </div>
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-primary-subtle text-primary"><i class="bi bi-graph-up-arrow"></i></div>
            <div>
                <div class="metric-label">ADR / RevPAR</div>
                <div class="metric-value"><?php echo money_fmt($summary['adr']); ?> / <?php echo money_fmt($summary['revpar']); ?></div>
                <div class="small text-muted">Room revenue based</div>
            </div>
        </div>
    </div>
</div>

<div class="row g-4 mb-4">
    <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">Revenue & Occupancy Analysis</h5>
                <span class="text-muted small">Daily trend across selected period</span>
            </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 class="text-end">Occ %</th>
                                <th class="text-end">Room Revenue</th>
                                <th class="text-end">Restaurant Revenue</th>
                                <th class="text-end">Other Revenue</th>
                                <th class="text-end">Total Revenue</th>
                                <th class="text-end">ADR</th>
                                <th class="text-end">RevPAR</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($dailyRows as $row): ?>
                                <tr>
                                    <td><strong><?php echo h($row['occ_date']); ?></strong></td>
                                    <td class="text-end"><?php echo money_fmt($row['occupancy_percent']); ?>%</td>
                                    <td class="text-end"><?php echo money_fmt($row['room_revenue'] ?? 0); ?></td>
                                    <td class="text-end"><?php echo money_fmt($row['restaurant_revenue'] ?? 0); ?></td>
                                    <td class="text-end"><?php echo money_fmt($row['other_revenue'] ?? 0); ?></td>
                                    <td class="text-end fw-bold"><?php echo money_fmt($row['total_revenue'] ?? 0); ?></td>
                                    <td class="text-end"><?php echo money_fmt($row['adr'] ?? 0); ?></td>
                                    <td class="text-end"><?php echo money_fmt($row['revpar'] ?? 0); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<div class="row g-4">
    <div class="col-xl-7">
        <div class="card panel-card h-100">
            <div class="card-header">
                <h5 class="mb-0">Daily Occupancy</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 class="text-end">Available</th>
                                <th class="text-end">Occupied</th>
                                <th class="text-end">Vacant</th>
                                <th class="text-end">Occupancy %</th>
                                <th class="text-end">Bookings</th>
                                <th class="text-end">Pax</th>
                                <th class="text-end">Total Rev</th>
                                <th class="text-end">ADR</th>
                                <th class="text-end">RevPAR</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$dailyRows): ?>
                                <tr><td colspan="7" class="text-center text-muted py-4">No occupancy data found.</td></tr>
                            <?php else: ?>
                                <?php foreach ($dailyRows as $row): ?>
                                    <tr>
                                        <td><strong><?php echo h($row['occ_date']); ?></strong></td>
                                        <td class="text-end"><?php echo (int)$row['available_rooms']; ?></td>
                                        <td class="text-end fw-bold"><?php echo (int)$row['occupied_rooms']; ?></td>
                                        <td class="text-end"><?php echo (int)$row['vacant_rooms']; ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['occupancy_percent']); ?>%</td>
                                        <td class="text-end"><?php echo (int)$row['bookings_count']; ?></td>
                                        <td class="text-end"><?php echo (int)$row['adults']; ?>A / <?php echo (int)$row['children']; ?>C</td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['total_revenue'] ?? 0); ?></td>
                                        <td class="text-end"><?php echo money_fmt($row['adr'] ?? 0); ?></td>
                                        <td class="text-end"><?php echo money_fmt($row['revpar'] ?? 0); ?></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">Property Occupancy</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 class="text-end">Rooms</th>
                                <th class="text-end">Occ Nights</th>
                                <th class="text-end">Occ %</th>
                                <th class="text-end">Room Revenue</th>
                                <th class="text-end">ADR</th>
                                <th class="text-end">RevPAR</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$propertyRows): ?>
                                <tr><td colspan="7" class="text-center text-muted py-4">No property data found.</td></tr>
                            <?php else: ?>
                                <?php foreach ($propertyRows as $row): ?>
                                    <tr>
                                        <td>
                                            <strong><?php echo h($row['property_name']); ?></strong>
                                            <?php if (!empty($row['property_code'])): ?>
                                                <div class="small text-muted"><?php echo h($row['property_code']); ?></div>
                                            <?php endif; ?>
                                        </td>
                                        <td class="text-end"><?php echo (int)$row['rooms_count']; ?></td>
                                        <td class="text-end fw-bold"><?php echo (int)$row['occupied_room_nights']; ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['occupancy_percent']); ?>%</td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['room_revenue'] ?? 0); ?></td>
                                        <td class="text-end"><?php echo money_fmt($row['adr'] ?? 0); ?></td>
                                        <td class="text-end"><?php echo money_fmt($row['revpar'] ?? 0); ?></td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <div class="col-12">
        <div class="card panel-card">
            <div class="card-header">
                <h5 class="mb-0">Room Type Performance</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">Available Nights</th>
                                <th class="text-end">Occupied Nights</th>
                                <th class="text-end">Vacant Nights</th>
                                <th class="text-end">Occupancy %</th>
                                <th class="text-end">Bookings</th>
                                <th class="text-end">Pax</th>
                                <th class="text-end">Room Revenue</th>
                                <th class="text-end">ADR</th>
                                <th class="text-end">RevPAR</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$roomTypeRows): ?>
                                <tr><td colspan="12" class="text-center text-muted py-4">No room type data found.</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"><?php echo (int)$row['available_room_nights']; ?></td>
                                        <td class="text-end fw-bold"><?php echo (int)$row['occupied_room_nights']; ?></td>
                                        <td class="text-end"><?php echo (int)$row['vacant_room_nights']; ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['occupancy_percent']); ?>%</td>
                                        <td class="text-end"><?php echo (int)$row['bookings_count']; ?></td>
                                        <td class="text-end"><?php echo (int)$row['adults']; ?>A / <?php echo (int)$row['children']; ?>C</td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['room_revenue'] ?? 0); ?></td>
                                        <td class="text-end"><?php echo money_fmt($row['adr'] ?? 0); ?></td>
                                        <td class="text-end"><?php echo money_fmt($row['revpar'] ?? 0); ?></td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </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">Room Status Snapshot</h5>
                <span class="text-muted small">All rooms as at <?php echo h($roomStatusDate); ?></span>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive room-status-scroll">
                    <table class="table table-modern align-middle mb-0 room-status-table">
                        <thead>
                            <tr>
                                <th>Property</th>
                                <th>Room</th>
                                <th>Room Type</th>
                                <th>Status</th>
                                <th>Guest / Booking</th>
                                <th>Stay</th>
                                <th class="text-end">Pax</th>
                                <th>Room Setup</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$roomStatusRows): ?>
                                <tr><td colspan="8" class="text-center text-muted py-4">No rooms found.</td></tr>
                            <?php else: ?>
                                <?php foreach ($roomStatusRows as $row): ?>
                                    <?php
                                    $room = $row['room'];
                                    $assignment = $row['assignment'];
                                    $propertyLabel = trim((string)($room['property_name'] ?? ''));
                                    if (!empty($room['property_code'])) {
                                        $propertyLabel .= ' (' . $room['property_code'] . ')';
                                    }

                                    $setupBits = [];
                                    if (!empty($room['allows_extra_bed'])) {
                                        $setupBits[] = 'Extra bed x ' . (int)($room['max_extra_beds'] ?? 0);
                                    }
                                    if (!empty($room['allows_baby_cot'])) {
                                        $setupBits[] = 'Cot x ' . (int)($room['max_cots'] ?? 0);
                                    }
                                    if (!empty($room['wheelchair_accessible'])) {
                                        $setupBits[] = 'Wheelchair';
                                    }
                                    if (!empty($room['interleading_group'])) {
                                        $setupBits[] = 'Interleading: ' . $room['interleading_group'];
                                    }
                                    if (!empty($room['view_type'])) {
                                        $setupBits[] = $room['view_type'];
                                    }
                                    ?>
                                    <tr>
                                        <td><?php echo h($propertyLabel ?: 'Not set'); ?></td>
                                        <td><strong>Room <?php echo h($room['room_no'] ?? ''); ?></strong></td>
                                        <td><?php echo h($room['room_type'] ?? 'Not set'); ?></td>
                                        <td>
                                            <span class="badge text-bg-<?php echo h($row['snapshot_class']); ?>">
                                                <?php echo h($row['snapshot_status']); ?>
                                            </span>
                                            <?php if (!empty($room['room_status'])): ?>
                                                <div class="small text-muted"><?php echo h($room['room_status']); ?></div>
                                            <?php endif; ?>
                                        </td>
                                        <td>
                                            <?php if ($assignment): ?>
                                                <div class="data-stack">
                                                    <strong><?php echo h(trim((string)($assignment['guest_name'] ?? '')) ?: 'Guest'); ?></strong>
                                                    <small><?php echo h($assignment['order_no'] ?: ('#' . (int)$assignment['order_id'])); ?></small>
                                                </div>
                                            <?php else: ?>
                                                <span class="text-muted">No in-house guest</span>
                                            <?php endif; ?>
                                        </td>
                                        <td>
                                            <?php if ($assignment): ?>
                                                <div class="data-stack">
                                                    <strong><?php echo h($assignment['check_in']); ?></strong>
                                                    <small>to <?php echo h($assignment['check_out']); ?></small>
                                                </div>
                                            <?php else: ?>
                                                <span class="text-muted">Available</span>
                                            <?php endif; ?>
                                        </td>
                                        <td class="text-end">
                                            <?php if ($assignment): ?>
                                                <?php echo (int)$assignment['adults']; ?>A / <?php echo (int)$assignment['children']; ?>C
                                            <?php else: ?>
                                                —
                                            <?php endif; ?>
                                        </td>
                                        <td>
                                            <?php if ($setupBits): ?>
                                                <div class="small"><?php echo h(implode(' • ', $setupBits)); ?></div>
                                            <?php else: ?>
                                                <span class="text-muted small">Standard</span>
                                            <?php endif; ?>
                                            <?php if (!empty($room['room_configuration_notes'])): ?>
                                                <div class="small text-muted"><?php echo h($room['room_configuration_notes']); ?></div>
                                            <?php endif; ?>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </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">Occupied Room Detail</h5>
                <span class="text-muted small">Showing latest 1,500 rooming records</span>
            </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>Booking</th>
                                <th>Guest</th>
                                <th>Agent</th>
                                <th>Room</th>
                                <th>Room Type</th>
                                <th>Stay</th>
                                <th class="text-end">Nights in Range</th>
                                <th class="text-end">Pax</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$detailRows): ?>
                                <tr><td colspan="9" class="text-center text-muted py-4">No occupied rooming records found.</td></tr>
                            <?php else: ?>
                                <?php foreach ($detailRows as $row): ?>
                                    <?php
                                    $propertyLabel = trim((string)($row['property_name'] ?? ''));
                                    if (!empty($row['property_code'])) {
                                        $propertyLabel .= ' (' . $row['property_code'] . ')';
                                    }
                                    $guestName = trim((string)($row['guest_name'] ?? ''));
                                    $agentName = trim((string)($row['agent_name'] ?? ''));
                                    ?>
                                    <tr>
                                        <td><?php echo h($propertyLabel ?: 'Not set'); ?></td>
                                        <td>
                                            <a href="/rooms/modules/reservations/reservation_view.php?order_id=<?php echo (int)$row['order_id']; ?>" class="booking-ref">
                                                <?php echo h($row['order_no'] ?: ('#' . $row['order_id'])); ?>
                                            </a>
                                        </td>
                                        <td><?php echo h($guestName ?: 'Guest'); ?></td>
                                        <td><?php echo h($agentName ?: 'Direct'); ?></td>
                                        <td><?php echo h($row['room_no'] ?: 'Unassigned'); ?></td>
                                        <td><strong><?php echo h($row['room_type'] ?: 'Not set'); ?></strong></td>
                                        <td>
                                            <div class="data-stack">
                                                <strong><?php echo h($row['check_in']); ?></strong>
                                                <small>to <?php echo h($row['check_out']); ?></small>
                                            </div>
                                        </td>
                                        <td class="text-end fw-bold"><?php echo max(0, (int)$row['nights_in_range']); ?></td>
                                        <td class="text-end"><?php echo (int)$row['adults']; ?>A / <?php echo (int)$row['children']; ?>C</td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

</div>

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