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

$pageTitle = 'Front Office Daily Reports';
/* Zimbabwe local time for PHP output, receipts and PrintNode payload text. */
date_default_timezone_set('Africa/Harare');
try {
    /* Zimbabwe is UTC+02:00 throughout the year. */
    $pdo->exec("SET time_zone = '+02:00'");
} catch (Throwable $e) {
    /* PHP date/time output still remains correctly set to Africa/Harare. */
}
if (!function_exists('h')) {
    function h($v): string { return htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8'); }
}
if (!function_exists('money_fmt')) {
    function money_fmt($v): string { return number_format((float)$v, 2); }
}

function fo_table_exists(PDO $pdo, string $table): bool {
    static $cache = [];
    if (array_key_exists($table, $cache)) return $cache[$table];
    try {
        $s = $pdo->prepare("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = :t");
        $s->execute([':t' => $table]);
        return $cache[$table] = ((int)$s->fetchColumn() > 0);
    } catch (Throwable $e) {
        return $cache[$table] = false;
    }
}

function fo_column_exists(PDO $pdo, string $table, string $column): bool {
    static $cache = [];
    $key = $table . '.' . $column;
    if (array_key_exists($key, $cache)) return $cache[$key];
    try {
        $s = $pdo->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = :t AND column_name = :c");
        $s->execute([':t' => $table, ':c' => $column]);
        return $cache[$key] = ((int)$s->fetchColumn() > 0);
    } catch (Throwable $e) {
        return $cache[$key] = false;
    }
}

function fo_column_is_textual(PDO $pdo, string $table, string $column): bool {
    try {
        $s = $pdo->prepare("SELECT DATA_TYPE FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = :t AND column_name = :c LIMIT 1");
        $s->execute([':t' => $table, ':c' => $column]);
        $type = strtolower((string)($s->fetchColumn() ?: ''));
        return in_array($type, ['varchar','char','text','tinytext','mediumtext','longtext','enum','set'], true);
    } catch (Throwable $e) {
        return false;
    }
}

function fo_display_date($v, string $format = 'd/m/Y'): string {
    if (!$v) return '';
    $ts = strtotime((string)$v);
    return $ts ? date($format, $ts) : (string)$v;
}

function fo_date_sql_value(string $date): string {
    $ts = strtotime($date);
    return $ts ? date('Y-m-d', $ts) : date('Y-m-d');
}

function fo_status_label($status): string {
    $s = strtolower(trim((string)$status));
    switch ($s) {
        case '1': case 'confirmed': case 'confirm': case 'reserved':
            return 'Confirmed';
        case '2': case 'pending': case 'provisional': case 'draft':
            return 'Provisional';
        case '3': case 'checked_in': case 'checkin': case 'inhouse': case 'in-house':
            return 'In House';
        case '4': case 'checked_out': case 'checkout': case 'completed':
            return 'Checked Out';
        case '0': case 'cancelled': case 'canceled':
            return 'Cancelled';
        default:
            return $s !== '' ? ucwords(str_replace('_', ' ', $s)) : '';
    }
}

function fo_guest_name_expr(PDO $pdo): string {
    if (!fo_table_exists($pdo, 'guests')) return "''";
    if (fo_column_exists($pdo, 'guests', 'firstname') && fo_column_exists($pdo, 'guests', 'lastname')) {
        return "TRIM(CONCAT(COALESCE(g.firstname,''), ' ', COALESCE(g.lastname,'')))";
    }
    if (fo_column_exists($pdo, 'guests', 'first_name') && fo_column_exists($pdo, 'guests', 'last_name')) {
        return "TRIM(CONCAT(COALESCE(g.first_name,''), ' ', COALESCE(g.last_name,'')))";
    }
    foreach (['guest_name','name','full_name'] as $col) {
        if (fo_column_exists($pdo, 'guests', $col)) return "COALESCE(g.`{$col}`,'')";
    }
    return "''";
}

function fo_guest_phone_expr(PDO $pdo, string $type = 'mobile'): string {
    if (!fo_table_exists($pdo, 'guests')) return "''";
    $mobileCols = ['mobile','mobile_no','cell','phone_mobile','telephone','phone'];
    $homeCols   = ['home_phone','phone_home','home','telephone_home','phone'];
    $cols = $type === 'home' ? $homeCols : $mobileCols;
    foreach ($cols as $col) {
        if (fo_column_exists($pdo, 'guests', $col)) return "COALESCE(g.`{$col}`,'')";
    }
    return "''";
}

function fo_rooming_kids_expr(PDO $pdo): string {
    foreach (['kids_0_3_no_charge','kids_0_3','children_0_3_no_charge','children_0_3','kids_no_charge','infants','toddlers','babies'] as $col) {
        if (fo_column_exists($pdo, 'order_rooming', $col)) return "COALESCE(rmg.`{$col}`,0)";
    }
    foreach (['kids_0_3_no_charge','kids_0_3','children_0_3_no_charge','children_0_3','kids_no_charge','infants','toddlers','babies'] as $col) {
        if (fo_column_exists($pdo, 'orders', $col)) return "COALESCE(o.`{$col}`,0)";
    }
    return "0";
}

function fo_fetch_properties(PDO $pdo): array {
    if (!fo_table_exists($pdo, 'properties')) return [];
    try {
        return $pdo->query("SELECT id, name, code FROM properties WHERE COALESCE(is_active,1)=1 ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        return [];
    }
}

function fo_property_label(PDO $pdo, int $propertyId): string {
    if ($propertyId <= 0 || !fo_table_exists($pdo, 'properties')) return 'All Properties';
    try {
        $s = $pdo->prepare("SELECT name, code FROM properties WHERE id = :id LIMIT 1");
        $s->execute([':id' => $propertyId]);
        $r = $s->fetch(PDO::FETCH_ASSOC) ?: [];
        return trim((string)($r['name'] ?? '') . (!empty($r['code']) ? ' (' . $r['code'] . ')' : '')) ?: 'Property #' . $propertyId;
    } catch (Throwable $e) {
        return 'Property #' . $propertyId;
    }
}

function fo_first_existing_order_expr(PDO $pdo, array $cols, string $fallback = "''"): string {
    $parts = [];
    foreach ($cols as $col) {
        if (fo_column_exists($pdo, 'orders', $col)) {
            $parts[] = "NULLIF(TRIM(COALESCE(o.`{$col}`,'')), '')";
        }
    }
    if (!$parts) return $fallback;
    $parts[] = $fallback;
    return 'COALESCE(' . implode(', ', $parts) . ')';
}

// ---------------------------------------------------------------------------
// FIXED: mirrors the dashboard's exact filter — numeric status is never
// compared to text workflow words; those live in booking_status / no_show_at.
// ---------------------------------------------------------------------------

function fo_meal_plan_expr(PDO $pdo): string {
    foreach (['meal_plan','mealplan','MealPlan','mealPlan'] as $col) {
        if (fo_column_exists($pdo, 'orders', $col)) {
            return "NULLIF(UPPER(TRIM(CAST(o.`{$col}` AS CHAR))), '')";
        }
    }
    return "''";
}

function fo_active_status_filter_sql(PDO $pdo, string $alias = 'o'): string {
    /*
       EXACT same active/not-no-show workflow filter used by frontdesk_dashboard.php.

       Important:
       - orders.status may be numeric.
       - workflow words such as no_show live in booking_status when that column exists.
       - Do not add extra exclusions here, otherwise the Daily Reports will not match
         the Front Desk Dashboard in-house list.
    */
    $bookingStatusExists = fo_column_exists($pdo, 'orders', 'booking_status');
    $statusIsText = fo_column_exists($pdo, 'orders', 'status') && fo_column_is_textual($pdo, 'orders', 'status');

    $workflowStatusExpr = $bookingStatusExists
        ? "LOWER(COALESCE({$alias}.booking_status,''))"
        : ($statusIsText ? "LOWER(COALESCE({$alias}.status,''))" : "''");

    return "CAST(COALESCE({$alias}.status,1) AS CHAR) NOT IN ('0')
            AND LOWER(COALESCE({$alias}.status,'')) NOT IN ('cancelled','canceled')
            AND {$workflowStatusExpr} NOT IN ('no_show','noshow','cancelled','canceled')";
}

// ---------------------------------------------------------------------------
// FIXED fo_fetch_rooming — In-house now uses the actual frontdesk_dashboard-style dataset (same source of truth as the
// dashboard), LEFT JOINs rooming for room detail only, and uses GROUP BY o.id
// so a booking with multiple rooming rows does not appear multiple times.
// ---------------------------------------------------------------------------


function fo_room_config_exprs(PDO $pdo, string $rmgAlias = 'rmg'): array {
    $bedCol = fo_column_exists($pdo, 'order_rooming', 'bed_configuration') ? 'bed_configuration' : null;

    if (fo_column_exists($pdo, 'order_rooming', 'special_room_request')) {
        $requestCol = 'special_room_request';
    } elseif (fo_column_exists($pdo, 'order_rooming', 'notes')) {
        $requestCol = 'notes';
    } else {
        $requestCol = null;
    }

    $bedExpr = $bedCol
        ? "NULLIF(TRIM(CAST({$rmgAlias}.`{$bedCol}` AS CHAR)), '')"
        : "NULL";

    $requestExpr = $requestCol
        ? "NULLIF(TRIM(CAST({$rmgAlias}.`{$requestCol}` AS CHAR)), '')"
        : "NULL";

    return [
        'bed' => $bedExpr,
        'request' => $requestExpr,
        'config' => "TRIM(CONCAT_WS(' | ', {$bedExpr}, {$requestExpr}))",
    ];
}

function fo_dashboard_room_mix_sql(): string
{
    return "
        LEFT JOIN (
            SELECT
                order_id,
                GROUP_CONCAT(room_summary ORDER BY sort_title SEPARATOR ', ') AS room_mix,
                SUM(triple_slots) AS triple_slots,
                SUM(family_slots) AS family_slots
            FROM (
                SELECT
                    rmg.order_id,
                    rt.title AS sort_title,
                    COUNT(*) AS total_slots,
                    SUM(
                        CASE
                            WHEN (COALESCE(rmg.adults,0) + COALESCE(rmg.children,0)) = 3
                                 AND LOWER(COALESCE(rt.title,'')) NOT LIKE '%family%'
                            THEN 1 ELSE 0
                        END
                    ) AS triple_slots,
                    SUM(
                        CASE
                            WHEN LOWER(COALESCE(rt.title,'')) LIKE '%family%'
                            THEN 1 ELSE 0
                        END
                    ) AS family_slots,
                    CONCAT(
                        COALESCE(rt.title, 'No type'),
                        ' x ',
                        COUNT(*)
                    ) AS room_summary
                FROM order_rooming rmg
                LEFT JOIN room_types rt ON rt.id = rmg.room_type_id
                GROUP BY rmg.order_id, rmg.room_type_id, rt.title
            ) mix
            GROUP BY order_id
        ) rmix ON rmix.order_id = o.id
    ";
}


function fo_fetch_dashboard_inhouse_rooming(PDO $pdo, int $propertyId, string $date): array
{
    /*
       In-House = Arrivals today + StayOvers.
       WHERE DATE(o.check_in)  <= report_date   (checked in today or earlier)
         AND DATE(o.check_out) >  report_date   (checking out tomorrow or later)

       Guests checking OUT today are NOT counted as in-house here - they belong
       to the Departures section instead. A guest arriving today with checkout
       tomorrow-or-later naturally satisfies both halves of this condition.
    */
    if (!fo_table_exists($pdo, 'orders')) return [];

    $bookingStatusExists = fo_column_exists($pdo, 'orders', 'booking_status');
    $statusIsText = fo_column_exists($pdo, 'orders', 'status') && fo_column_is_textual($pdo, 'orders', 'status');
    $workflowStatusExpr = $bookingStatusExists
        ? "LOWER(COALESCE(o.booking_status,''))"
        : ($statusIsText ? "LOWER(COALESCE(o.status,''))" : "''");

    $notNoShowWorkflowSql = " AND CAST(COALESCE(o.status,1) AS CHAR) NOT IN ('0')
        AND LOWER(COALESCE(o.status,'')) NOT IN ('cancelled','canceled')
        AND {$workflowStatusExpr} NOT IN ('no_show','noshow','cancelled','canceled') ";

    $guestNameExpr = fo_guest_name_expr($pdo);
    $orderGuestExpr = fo_first_existing_order_expr($pdo, ['guest_name','guest','customer_name','client_name','pax_name','PaxName','name']);
    $guestSelectExpr = "COALESCE(NULLIF({$guestNameExpr}, ''), {$orderGuestExpr}, '')";

    $mobileExpr  = fo_guest_phone_expr($pdo, 'mobile');
    $homeExpr    = fo_guest_phone_expr($pdo, 'home');
    $companyExpr = fo_first_existing_order_expr($pdo, ['company_name','company','CompanyName']);
    $voucherExpr = fo_first_existing_order_expr($pdo, ['voucher','voucher_no','reference','booking_ref','BookingRef']);
    $reasonExpr  = fo_first_existing_order_expr($pdo, ['reason','special_occasion','notes']);
    $visitsExpr  = fo_first_existing_order_expr($pdo, ['visits'], "'1'");
    $dietaryExpr = fo_first_existing_order_expr($pdo, ['dietary','dietary_notes','meal_notes']);
    $orderNoExpr = fo_column_exists($pdo, 'orders', 'order_no') ? 'o.order_no' : 'CAST(o.id AS CHAR)';
    $statusExpr  = fo_column_exists($pdo, 'orders', 'booking_status')
        ? "COALESCE(NULLIF(o.booking_status,''), CAST(o.status AS CHAR), '')"
        : (fo_column_exists($pdo, 'orders', 'status') ? "COALESCE(o.status, '')" : "''");
    $mealPlanExpr = fo_meal_plan_expr($pdo);

    $oAdults = fo_column_exists($pdo, 'orders', 'adults') ? 'COALESCE(o.adults,0)' : '0';
    $oChildren = fo_column_exists($pdo, 'orders', 'kids')
        ? 'COALESCE(o.kids,0)'
        : (fo_column_exists($pdo, 'orders', 'children') ? 'COALESCE(o.children,0)' : '0');
    /*
       Adults/children must be summed per physical room (order_rooming), not read
       from the order-level orders.adults/kids column. A 3-room group booking
       stores its true headcount split across 3 order_rooming rows; the
       order-level column undercounts (or reflects only one room) once a
       booking has more than one room. This mirrors the SUM(...) pattern
       fo_fetch_rooming() already uses correctly for Arrivals/Departures.
    */
    if (fo_table_exists($pdo, 'order_rooming') && fo_column_exists($pdo, 'order_rooming', 'adults')) {
        $oAdults = "COALESCE((SELECT SUM(COALESCE(rmg_sum.adults,0)) FROM order_rooming rmg_sum WHERE rmg_sum.order_id = o.id), {$oAdults})";
    }
    if (fo_table_exists($pdo, 'order_rooming') && fo_column_exists($pdo, 'order_rooming', 'children')) {
        $oChildren = "COALESCE((SELECT SUM(COALESCE(rmg_sum2.children,0)) FROM order_rooming rmg_sum2 WHERE rmg_sum2.order_id = o.id), {$oChildren})";
    }
    $kidsExpr = fo_rooming_kids_expr($pdo);

    $params = [$date, $date];

    $propertyFilter = '';
    if ($propertyId > 0 && fo_column_exists($pdo, 'orders', 'property_id')) {
        $propertyFilter = ' AND o.property_id = ? ';
        $params[] = $propertyId;
    }

    $roomSlotsTotal = fo_table_exists($pdo, 'order_rooming')
        ? "(SELECT COUNT(*) FROM order_rooming rmg WHERE rmg.order_id = o.id)"
        : "COALESCE(o.totalrooms,1)";

    $roomNoExpr = fo_table_exists($pdo, 'order_rooming')
        ? "(SELECT GROUP_CONCAT(DISTINCT COALESCE(rm.room_no, rmg.room_no) ORDER BY COALESCE(rm.room_no, rmg.room_no) SEPARATOR ', ')
             FROM order_rooming rmg
             LEFT JOIN rooms rm ON rm.id = rmg.room_id
             WHERE rmg.order_id = o.id)"
        : "''";

    $cfg = fo_room_config_exprs($pdo, 'rmg');
    $configExpr = fo_table_exists($pdo, 'order_rooming')
        ? "(SELECT GROUP_CONCAT(DISTINCT NULLIF(" . $cfg['config'] . ", '') ORDER BY rmg.id SEPARATOR '; ')
             FROM order_rooming rmg
             WHERE rmg.order_id = o.id)"
        : "''";

    $roomMixJoin = fo_table_exists($pdo, 'order_rooming') && fo_table_exists($pdo, 'room_types')
        ? fo_dashboard_room_mix_sql()
        : "";

    $roomTypeExpr = fo_table_exists($pdo, 'room_types') ? "COALESCE(rmix.room_mix, rt.title, '')" : "COALESCE(rmix.room_mix, '')";
    $roomTypeJoin = fo_table_exists($pdo, 'room_types') ? "LEFT JOIN room_types rt ON rt.id = o.room_type_id" : "";
    $propertyJoin = fo_table_exists($pdo, 'properties') ? "LEFT JOIN properties p ON p.id = o.property_id" : "";
    $guestJoin = fo_table_exists($pdo, 'guests') && fo_column_exists($pdo, 'orders', 'guest_id') ? "LEFT JOIN guests g ON g.id = o.guest_id" : "";
    $agentJoin = fo_table_exists($pdo, 'agents') && fo_column_exists($pdo, 'orders', 'agent_id') ? "LEFT JOIN agents a ON a.id = o.agent_id" : "";

    if (fo_table_exists($pdo, 'agents') && fo_column_exists($pdo, 'orders', 'agent_id')) {
        if (fo_column_exists($pdo, 'agents', 'firstname') && fo_column_exists($pdo, 'agents', 'lastname')) {
            $agentExpr = "TRIM(CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,'')))";
        } elseif (fo_column_exists($pdo, 'agents', 'name')) {
            $agentExpr = "COALESCE(a.name,'')";
        } elseif (fo_column_exists($pdo, 'agents', 'Agent')) {
            $agentExpr = "COALESCE(a.Agent,'')";
        } else {
            $agentExpr = "''";
        }
    } else {
        $agentExpr = "''";
    }

    $sql = "
        SELECT
            o.id                         AS order_id,
            {$orderNoExpr}               AS order_no,
            COALESCE({$roomNoExpr}, CASE WHEN COALESCE(o.totalrooms,1) > 1 THEN CONCAT(o.totalrooms, ' rooms') ELSE '' END) AS room_no,
            {$roomTypeExpr}              AS room_type,
            COALESCE({$configExpr}, '')  AS config,
            {$mealPlanExpr}              AS meal_plan,
            {$guestSelectExpr}           AS guest_name,
            {$mobileExpr}                AS mobile,
            {$homeExpr}                  AS home_phone,
            {$companyExpr}               AS company,
            {$voucherExpr}               AS voucher,
            {$reasonExpr}                AS reason,
            {$visitsExpr}                AS visits,
            {$dietaryExpr}               AS dietary,
            {$statusExpr}                AS status_raw,
            {$agentExpr}                 AS agent_name,
            o.check_in                   AS check_in,
            o.check_out                  AS check_out,
            DATEDIFF(o.check_out, o.check_in) AS nights,
            {$oAdults}                   AS adults,
            {$oChildren}                 AS children,
            {$kidsExpr}                  AS kids_0_3,
            GREATEST(1, COALESCE({$roomSlotsTotal}, COALESCE(o.totalrooms,1), 1)) AS room_count,
            0                            AS rooming_id
        FROM orders o
        {$propertyJoin}
        {$roomTypeJoin}
        {$guestJoin}
        {$agentJoin}
        {$roomMixJoin}
        WHERE DATE(o.check_in) <= ?
          AND DATE(o.check_out) > ?
          {$notNoShowWorkflowSql}
          {$propertyFilter}
        ORDER BY
          COALESCE(p.name, '') ASC,
          o.check_out ASC,
          o.id DESC
    ";

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    } catch (Throwable $e) {
        error_log('fo_fetch_dashboard_inhouse_rooming error: ' . $e->getMessage());
        return [];
    }
}

function fo_fetch_dashboard_stayover_rooming(PDO $pdo, int $propertyId, string $date): array
{
    /*
       StayOvers for Daily Reservations / Rooming List.

       Correct rule:
           DATE(o.check_in) < report_date
       AND DATE(o.check_out) > report_date

       This means:
       - arrived yesterday or before
       - leaving tomorrow or later
       - not arriving today
       - not departing today
    */
    if (!fo_table_exists($pdo, 'orders')) return [];

    $bookingStatusExists = fo_column_exists($pdo, 'orders', 'booking_status');
    $statusIsText = fo_column_exists($pdo, 'orders', 'status') && fo_column_is_textual($pdo, 'orders', 'status');
    $workflowStatusExpr = $bookingStatusExists
        ? "LOWER(COALESCE(o.booking_status,''))"
        : ($statusIsText ? "LOWER(COALESCE(o.status,''))" : "''");

    $notNoShowWorkflowSql = " AND CAST(COALESCE(o.status,1) AS CHAR) NOT IN ('0')
        AND LOWER(COALESCE(o.status,'')) NOT IN ('cancelled','canceled')
        AND {$workflowStatusExpr} NOT IN ('no_show','noshow','cancelled','canceled') ";

    $guestNameExpr = fo_guest_name_expr($pdo);
    $orderGuestExpr = fo_first_existing_order_expr($pdo, ['guest_name','guest','customer_name','client_name','pax_name','PaxName','name']);
    $guestSelectExpr = "COALESCE(NULLIF({$guestNameExpr}, ''), {$orderGuestExpr}, '')";

    $mobileExpr  = fo_guest_phone_expr($pdo, 'mobile');
    $homeExpr    = fo_guest_phone_expr($pdo, 'home');
    $companyExpr = fo_first_existing_order_expr($pdo, ['company_name','company','CompanyName']);
    $voucherExpr = fo_first_existing_order_expr($pdo, ['voucher','voucher_no','reference','booking_ref','BookingRef']);
    $reasonExpr  = fo_first_existing_order_expr($pdo, ['reason','special_occasion','notes']);
    $visitsExpr  = fo_first_existing_order_expr($pdo, ['visits'], "'1'");
    $dietaryExpr = fo_first_existing_order_expr($pdo, ['dietary','dietary_notes','meal_notes']);
    $orderNoExpr = fo_column_exists($pdo, 'orders', 'order_no') ? 'o.order_no' : 'CAST(o.id AS CHAR)';
    $statusExpr  = fo_column_exists($pdo, 'orders', 'booking_status')
        ? "COALESCE(NULLIF(o.booking_status,''), CAST(o.status AS CHAR), '')"
        : (fo_column_exists($pdo, 'orders', 'status') ? "COALESCE(o.status, '')" : "''");
    $mealPlanExpr = fo_meal_plan_expr($pdo);

    $oAdults = fo_column_exists($pdo, 'orders', 'adults') ? 'COALESCE(o.adults,0)' : '0';
    $oChildren = fo_column_exists($pdo, 'orders', 'kids')
        ? 'COALESCE(o.kids,0)'
        : (fo_column_exists($pdo, 'orders', 'children') ? 'COALESCE(o.children,0)' : '0');
    /*
       Adults/children must be summed per physical room (order_rooming), not read
       from the order-level orders.adults/kids column. A 3-room group booking
       stores its true headcount split across 3 order_rooming rows; the
       order-level column undercounts (or reflects only one room) once a
       booking has more than one room. This mirrors the SUM(...) pattern
       fo_fetch_rooming() already uses correctly for Arrivals/Departures.
    */
    if (fo_table_exists($pdo, 'order_rooming') && fo_column_exists($pdo, 'order_rooming', 'adults')) {
        $oAdults = "COALESCE((SELECT SUM(COALESCE(rmg_sum.adults,0)) FROM order_rooming rmg_sum WHERE rmg_sum.order_id = o.id), {$oAdults})";
    }
    if (fo_table_exists($pdo, 'order_rooming') && fo_column_exists($pdo, 'order_rooming', 'children')) {
        $oChildren = "COALESCE((SELECT SUM(COALESCE(rmg_sum2.children,0)) FROM order_rooming rmg_sum2 WHERE rmg_sum2.order_id = o.id), {$oChildren})";
    }
    $kidsExpr = fo_rooming_kids_expr($pdo);

    $params = [$date, $date];

    $propertyFilter = '';
    if ($propertyId > 0 && fo_column_exists($pdo, 'orders', 'property_id')) {
        $propertyFilter = ' AND o.property_id = ? ';
        $params[] = $propertyId;
    }

    $roomSlotsTotal = fo_table_exists($pdo, 'order_rooming')
        ? "(SELECT COUNT(*) FROM order_rooming rmg WHERE rmg.order_id = o.id)"
        : "COALESCE(o.totalrooms,1)";

    $roomNoExpr = fo_table_exists($pdo, 'order_rooming')
        ? "(SELECT GROUP_CONCAT(DISTINCT COALESCE(rm.room_no, rmg.room_no) ORDER BY COALESCE(rm.room_no, rmg.room_no) SEPARATOR ', ')
             FROM order_rooming rmg
             LEFT JOIN rooms rm ON rm.id = rmg.room_id
             WHERE rmg.order_id = o.id)"
        : "''";

    $cfg = fo_room_config_exprs($pdo, 'rmg');
    $configExpr = fo_table_exists($pdo, 'order_rooming')
        ? "(SELECT GROUP_CONCAT(DISTINCT NULLIF(" . $cfg['config'] . ", '') ORDER BY rmg.id SEPARATOR '; ')
             FROM order_rooming rmg
             WHERE rmg.order_id = o.id)"
        : "''";

    $roomMixJoin = fo_table_exists($pdo, 'order_rooming') && fo_table_exists($pdo, 'room_types')
        ? fo_dashboard_room_mix_sql()
        : "";

    $roomTypeExpr = fo_table_exists($pdo, 'room_types') ? "COALESCE(rmix.room_mix, rt.title, '')" : "COALESCE(rmix.room_mix, '')";
    $roomTypeJoin = fo_table_exists($pdo, 'room_types') ? "LEFT JOIN room_types rt ON rt.id = o.room_type_id" : "";
    $propertyJoin = fo_table_exists($pdo, 'properties') ? "LEFT JOIN properties p ON p.id = o.property_id" : "";
    $guestJoin = fo_table_exists($pdo, 'guests') && fo_column_exists($pdo, 'orders', 'guest_id') ? "LEFT JOIN guests g ON g.id = o.guest_id" : "";
    $agentJoin = fo_table_exists($pdo, 'agents') && fo_column_exists($pdo, 'orders', 'agent_id') ? "LEFT JOIN agents a ON a.id = o.agent_id" : "";

    if (fo_table_exists($pdo, 'agents') && fo_column_exists($pdo, 'orders', 'agent_id')) {
        if (fo_column_exists($pdo, 'agents', 'firstname') && fo_column_exists($pdo, 'agents', 'lastname')) {
            $agentExpr = "TRIM(CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,'')))";
        } elseif (fo_column_exists($pdo, 'agents', 'name')) {
            $agentExpr = "COALESCE(a.name,'')";
        } elseif (fo_column_exists($pdo, 'agents', 'Agent')) {
            $agentExpr = "COALESCE(a.Agent,'')";
        } else {
            $agentExpr = "''";
        }
    } else {
        $agentExpr = "''";
    }

    $sql = "
        SELECT
            o.id                         AS order_id,
            {$orderNoExpr}               AS order_no,
            COALESCE(NULLIF({$roomNoExpr}, ''), CASE WHEN COALESCE(o.totalrooms,1) > 1 THEN CONCAT(o.totalrooms, ' rooms') ELSE '' END) AS room_no,
            {$roomTypeExpr}              AS room_type,
            COALESCE({$configExpr}, '')  AS config,
            {$mealPlanExpr}              AS meal_plan,
            {$guestSelectExpr}           AS guest_name,
            {$mobileExpr}                AS mobile,
            {$homeExpr}                  AS home_phone,
            {$companyExpr}               AS company,
            {$voucherExpr}               AS voucher,
            {$reasonExpr}                AS reason,
            {$visitsExpr}                AS visits,
            {$dietaryExpr}               AS dietary,
            {$statusExpr}                AS status_raw,
            {$agentExpr}                 AS agent_name,
            o.check_in                   AS check_in,
            o.check_out                  AS check_out,
            DATEDIFF(o.check_out, o.check_in) AS nights,
            {$oAdults}                   AS adults,
            {$oChildren}                 AS children,
            {$kidsExpr}                  AS kids_0_3,
            GREATEST(1, COALESCE({$roomSlotsTotal}, COALESCE(o.totalrooms,1), 1)) AS room_count,
            0                            AS rooming_id
        FROM orders o
        {$propertyJoin}
        {$roomTypeJoin}
        {$guestJoin}
        {$agentJoin}
        {$roomMixJoin}
        WHERE DATE(o.check_in) < ?
          AND DATE(o.check_out) > ?
          {$notNoShowWorkflowSql}
          {$propertyFilter}
        ORDER BY
          COALESCE(p.name, '') ASC,
          o.check_out ASC,
          o.id DESC
    ";

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    } catch (Throwable $e) {
        error_log('fo_fetch_dashboard_stayover_rooming error: ' . $e->getMessage());
        return [];
    }
}



function fo_fetch_rooming(PDO $pdo, int $propertyId, string $date, string $section): array {
    if ($section === 'stayovers') {
        return fo_fetch_dashboard_stayover_rooming($pdo, $propertyId, $date);
    }

    if ($section === 'inhouse') {
        return fo_fetch_dashboard_inhouse_rooming($pdo, $propertyId, $date);
    }

    /*
       SOURCE OF TRUTH: orders.check_in / orders.check_out — same as the dashboard.

       Deduplication strategy:
       order_rooming is pre-aggregated into a subquery (one row per order_id) so
       a booking with 3 rooming rows does not appear 3 times.  There is NO GROUP BY
       on the outer query, which avoids the ONLY_FULL_GROUP_BY failure that made
       all reports return empty arrays.

       Bookings with no rooming rows still appear (all rmg.* columns will be NULL).
    */
    if (!fo_table_exists($pdo, 'orders')) return [];

    $hasRooming   = fo_table_exists($pdo, 'order_rooming');
    $hasRooms     = fo_table_exists($pdo, 'rooms');
    $hasRoomTypes = fo_table_exists($pdo, 'room_types');
    $hasGuests    = fo_table_exists($pdo, 'guests');
    $hasAgents    = fo_table_exists($pdo, 'agents');

    // --- Date columns on orders ---
    $ciCol = fo_column_exists($pdo, 'orders', 'check_in')
           ? 'o.check_in'
           : (fo_column_exists($pdo, 'orders', 'arrival_date') ? 'o.arrival_date' : 'NULL');
    $coCol = fo_column_exists($pdo, 'orders', 'check_out')
           ? 'o.check_out'
           : (fo_column_exists($pdo, 'orders', 'departure_date') ? 'o.departure_date' : 'NULL');

    // --- Order-level pax / room fallbacks ---
    $oAdults   = fo_column_exists($pdo, 'orders', 'adults')
               ? 'COALESCE(o.adults, 0)' : '0';
    $oChildren = fo_column_exists($pdo, 'orders', 'kids')
               ? 'COALESCE(o.kids, 0)'
               : (fo_column_exists($pdo, 'orders', 'children') ? 'COALESCE(o.children, 0)' : '0');
    $oRooms    = fo_column_exists($pdo, 'orders', 'totalrooms')
               ? 'COALESCE(o.totalrooms, 1)' : '1';
    $oRoomId   = fo_column_exists($pdo, 'orders', 'room_id')      ? 'o.room_id'      : 'NULL';
    $oRoomType = fo_column_exists($pdo, 'orders', 'room_type_id') ? 'o.room_type_id' : 'NULL';

    // --- Rooming subquery (pre-aggregate to 1 row per order) ---
    $roomingJoin   = '';
    $rmgRoomNo     = 'NULL';
    $adultsExpr    = $oAdults;
    $childrenExpr  = $oChildren;
    $effectiveRoomId     = $oRoomId;
    $effectiveRoomTypeId = $oRoomType;
    $rooming_id_expr     = '0';
    $roomCountExpr       = $oRooms;

    if ($hasRooming) {
        $rmgRoomIdCol     = fo_column_exists($pdo, 'order_rooming', 'room_id')     ? 'MIN(rr.room_id)'      : 'NULL';
        $rmgRoomTypeIdCol = fo_column_exists($pdo, 'order_rooming', 'room_type_id') ? 'MIN(rr.room_type_id)' : 'NULL';
        $rmgRoomNoCol     = fo_column_exists($pdo, 'order_rooming', 'room_no')     ? 'MIN(rr.room_no)'      : 'NULL';
        $rmgAdultsCol     = fo_column_exists($pdo, 'order_rooming', 'adults')      ? 'SUM(COALESCE(rr.adults,0))'   : 'NULL';
        $rmgChildrenCol   = fo_column_exists($pdo, 'order_rooming', 'children')    ? 'SUM(COALESCE(rr.children,0))' : 'NULL';
        $rmgBedCol        = fo_column_exists($pdo, 'order_rooming', 'bed_configuration') ? "GROUP_CONCAT(DISTINCT NULLIF(TRIM(CAST(rr.bed_configuration AS CHAR)), '') ORDER BY rr.id SEPARATOR '; ')" : "NULL";
        if (fo_column_exists($pdo, 'order_rooming', 'special_room_request')) {
            $rmgRequestCol = "GROUP_CONCAT(DISTINCT NULLIF(TRIM(CAST(rr.special_room_request AS CHAR)), '') ORDER BY rr.id SEPARATOR '; ')";
        } elseif (fo_column_exists($pdo, 'order_rooming', 'notes')) {
            $rmgRequestCol = "GROUP_CONCAT(DISTINCT NULLIF(TRIM(CAST(rr.notes AS CHAR)), '') ORDER BY rr.id SEPARATOR '; ')";
        } else {
            $rmgRequestCol = "NULL";
        }

        /*
           STAGGERED-ARRIVAL FIX (Room 49 case):
           Rooming rows carry their OWN check_in/check_out, and on group
           bookings these can differ from the order header dates - e.g. a
           7-room group where one room arrives two days early. The old
           subquery aggregated ALL rooming rows and the outer query filtered
           on HEADER dates only, so that early room never appeared in
           Arrivals (or anywhere) on its actual arrival date, and the
           arrivals count came up short.

           Fix: filter the rooming rows INSIDE the subquery by the section's
           date rule using the per-room dates (falling back to the header
           date when a rooming row has no dates of its own). The subquery
           then returns, per order, only the rooms that belong to this
           section on this date - so pax/room counts and room numbers are
           the rooms actually arriving/departing/staying that day.
        */
        $hasRmgDates = fo_column_exists($pdo, 'order_rooming', 'check_in') && fo_column_exists($pdo, 'order_rooming', 'check_out');
        $rmgSectionWhere = '';
        $rmgDateSelects = "NULL AS rmg_ci, NULL AS rmg_co,";
        if ($hasRmgDates) {
            $ciInner = ($ciCol === 'NULL') ? 'NULL' : str_replace('o.', 'oo.', $ciCol);
            $coInner = ($coCol === 'NULL') ? 'NULL' : str_replace('o.', 'oo.', $coCol);
            $effCiInner = "COALESCE(rr.check_in, {$ciInner})";
            $effCoInner = "COALESCE(rr.check_out, {$coInner})";
            switch ($section) {
                case 'arrivals':
                    $rmgSectionWhere = "WHERE DATE({$effCiInner}) = :rmg_date_1";
                    break;
                case 'departures':
                    $rmgSectionWhere = "WHERE DATE({$effCoInner}) = :rmg_date_1";
                    break;
                case 'stayovers':
                    $rmgSectionWhere = "WHERE DATE({$effCiInner}) < :rmg_date_1 AND DATE({$effCoInner}) > :rmg_date_2";
                    break;
                default: // inhouse (mirrors the outer header rule below)
                    $rmgSectionWhere = "WHERE DATE({$effCiInner}) <= :rmg_date_1 AND DATE({$effCoInner}) >= :rmg_date_2";
                    break;
            }
            $rmgDateSelects = "MIN({$effCiInner}) AS rmg_ci, MAX({$effCoInner}) AS rmg_co,";
        }

        $roomingJoin = "LEFT JOIN (
            SELECT
                rr.order_id,
                {$rmgDateSelects}
                MIN(rr.id)             AS rmg_id,
                COUNT(*)            AS rmg_room_count,
                {$rmgRoomIdCol}     AS rmg_room_id,
                {$rmgRoomTypeIdCol} AS rmg_room_type_id,
                {$rmgRoomNoCol}     AS rmg_room_no,
                {$rmgAdultsCol}     AS rmg_adults,
                {$rmgChildrenCol}   AS rmg_children,
                {$rmgBedCol}        AS rmg_bed_configuration,
                {$rmgRequestCol}    AS rmg_special_room_request
            FROM order_rooming rr
            INNER JOIN orders oo ON oo.id = rr.order_id
            {$rmgSectionWhere}
            GROUP BY rr.order_id
        ) rmg ON rmg.order_id = o.id";

        $rmgRoomNo           = "NULLIF(TRIM(CAST(rmg.rmg_room_no AS CHAR)), '')";
        $adultsExpr          = "COALESCE(rmg.rmg_adults, {$oAdults})";
        $childrenExpr        = "COALESCE(rmg.rmg_children, {$oChildren})";
        $effectiveRoomId     = "COALESCE(rmg.rmg_room_id, {$oRoomId})";
        $effectiveRoomTypeId = "COALESCE({$oRoomType}, rmg.rmg_room_type_id)";
        $rooming_id_expr     = 'COALESCE(rmg.rmg_id, 0)';
        $roomCountExpr       = "COALESCE(rmg.rmg_room_count, {$oRooms})";
    }

    $configExpr = $hasRooming
        ? "TRIM(CONCAT_WS(' | ', NULLIF(TRIM(CAST(rmg.rmg_bed_configuration AS CHAR)), ''), NULLIF(TRIM(CAST(rmg.rmg_special_room_request AS CHAR)), '')))"
        : "''";

    // --- Rooms / room-types joins ---
    $roomsJoin     = $hasRooms     ? "LEFT JOIN rooms r ON r.id = ({$effectiveRoomId})"            : '';
    $roomTypesJoin = $hasRoomTypes ? "LEFT JOIN room_types rt ON rt.id = ({$effectiveRoomTypeId})" : '';

    // Room number: rooms table > rooming row > order room-count label
    $roomNoExpr = "COALESCE(
        NULLIF(TRIM(CAST(r.room_no AS CHAR)), ''),
        {$rmgRoomNo},
        CASE WHEN {$oRooms} > 1 THEN CONCAT({$oRooms}, ' rooms') ELSE '' END
    )";

    // --- Guest join ---
    $guestJoin = ($hasGuests && fo_column_exists($pdo, 'orders', 'guest_id'))
               ? 'LEFT JOIN guests g ON g.id = CAST(o.guest_id AS UNSIGNED)'
               : '';

    // --- Agent join + expression ---
    if ($hasAgents && fo_column_exists($pdo, 'orders', 'agent_id')) {
        $agentJoin = 'LEFT JOIN agents a ON a.id = o.agent_id';
        if (fo_column_exists($pdo, 'agents', 'firstname') && fo_column_exists($pdo, 'agents', 'lastname')) {
            $agentExpr = "TRIM(CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,'')))";
        } elseif (fo_column_exists($pdo, 'agents', 'name')) {
            $agentExpr = "COALESCE(a.name, '')";
        } elseif (fo_column_exists($pdo, 'agents', 'Agent')) {
            $agentExpr = "COALESCE(a.Agent, '')";
        } else {
            $agentExpr = "''";
        }
    } else {
        $agentJoin = '';
        $agentExpr = "''";
    }

    // --- Guest name ---
    $guestExpr       = fo_guest_name_expr($pdo);
    $orderGuestExpr  = fo_first_existing_order_expr($pdo,
        ['guest_name','guest','customer_name','client_name','pax_name','PaxName','name']);
    $guestSelectExpr = "COALESCE(NULLIF({$guestExpr}, ''), {$orderGuestExpr}, '')";

    // --- Other fields ---
    $mobileExpr  = fo_guest_phone_expr($pdo, 'mobile');
    $homeExpr    = fo_guest_phone_expr($pdo, 'home');
    $companyExpr = fo_first_existing_order_expr($pdo, ['company_name','company','CompanyName']);
    $voucherExpr = fo_first_existing_order_expr($pdo, ['voucher','voucher_no','reference','booking_ref','BookingRef']);
    $reasonExpr  = fo_first_existing_order_expr($pdo, ['reason','special_occasion','notes']);
    $visitsExpr  = fo_first_existing_order_expr($pdo, ['visits'], "'1'");
    $dietaryExpr = fo_first_existing_order_expr($pdo, ['dietary','dietary_notes','meal_notes']);
    $orderNoExpr = fo_column_exists($pdo, 'orders', 'order_no') ? 'o.order_no' : 'CAST(o.id AS CHAR)';
    $statusExpr  = fo_column_exists($pdo, 'orders', 'booking_status')
        ? "COALESCE(NULLIF(o.booking_status,''), CAST(o.status AS CHAR), '')"
        : (fo_column_exists($pdo, 'orders', 'status') ? "COALESCE(o.status, '')" : "''");
    $mealPlanExpr = fo_meal_plan_expr($pdo);
    $kidsExpr = fo_rooming_kids_expr($pdo);

    // --- Active status filter (exact mirror of dashboard logic) ---
    $activeFilter = fo_active_status_filter_sql($pdo, 'o');

    // --- Date filter by section ---
    switch ($section) {
        case 'arrivals':
            $dateFilter = "DATE({$ciCol}) = :report_date";
            $params = [':report_date' => $date];
            break;
        case 'departures':
            $dateFilter = "DATE({$coCol}) = :report_date";
            $params = [':report_date' => $date];
            break;
        case 'stayovers':
            $dateFilter = "DATE({$ciCol}) < :report_date_1 AND DATE({$coCol}) > :report_date_2";
            $params = [':report_date_1' => $date, ':report_date_2' => $date];
            break;
        default: // inhouse: EXACT frontdesk_dashboard morning logic
            $dateFilter = "DATE({$ciCol}) <= :report_date_1 AND DATE({$coCol}) >= :report_date_2";
            $params = [':report_date_1' => $date, ':report_date_2' => $date];
            break;
    }

    /*
       Staggered-arrival fix (outer half): when rooming dates are available,
       the section subquery above only matched the rooms belonging to this
       section on this date - so the booking qualifies when the subquery
       found rooms (rmg.order_id IS NOT NULL). The header-date rule is kept
       ONLY for legacy bookings with no rooming rows at all, so they still
       appear exactly as before.
    */
    if ($hasRooming && !empty($hasRmgDates)) {
        $params[':rmg_date_1'] = $date;
        if (in_array($section, ['stayovers', 'inhouse'], true) || !in_array($section, ['arrivals', 'departures'], true)) {
            $params[':rmg_date_2'] = $date;
        }
        $dateFilter = "(rmg.order_id IS NOT NULL OR (NOT EXISTS (SELECT 1 FROM order_rooming z WHERE z.order_id = o.id) AND ({$dateFilter})))";
    }

    // --- Property filter ---
    $propertyFilter = '';
    if ($propertyId > 0 && fo_column_exists($pdo, 'orders', 'property_id')) {
        $propertyFilter = 'AND o.property_id = :property_id';
        $params[':property_id'] = $propertyId;
    }

    // Displayed dates: the matched rooms' own dates when available (so Room 49
    // arriving early shows ITS dates), header dates otherwise.
    $ciSelect = $ciCol;
    $coSelect = $coCol;
    if ($hasRooming && !empty($hasRmgDates)) {
        $ciSelect = "COALESCE(rmg.rmg_ci, {$ciCol})";
        $coSelect = "COALESCE(rmg.rmg_co, {$coCol})";
    }

    // No GROUP BY on the outer query — deduplication is handled by the rooming subquery above.
    $sql = "
        SELECT
            o.id                         AS order_id,
            {$orderNoExpr}               AS order_no,
            {$roomNoExpr}                AS room_no,
            COALESCE(rt.title, '')       AS room_type,
            COALESCE({$configExpr}, '')  AS config,
            {$mealPlanExpr}              AS meal_plan,
            {$guestSelectExpr}           AS guest_name,
            {$mobileExpr}                AS mobile,
            {$homeExpr}                  AS home_phone,
            {$companyExpr}               AS company,
            {$voucherExpr}               AS voucher,
            {$reasonExpr}                AS reason,
            {$visitsExpr}                AS visits,
            {$dietaryExpr}               AS dietary,
            {$statusExpr}                AS status_raw,
            {$agentExpr}                 AS agent_name,
            {$ciSelect}                  AS check_in,
            {$coSelect}                  AS check_out,
            DATEDIFF({$coSelect}, {$ciSelect}) AS nights,
            {$adultsExpr}                AS adults,
            {$childrenExpr}              AS children,
            {$kidsExpr}                  AS kids_0_3,
            {$roomCountExpr}             AS room_count,
            {$rooming_id_expr}           AS rooming_id
        FROM orders o
        {$roomingJoin}
        {$guestJoin}
        {$agentJoin}
        {$roomsJoin}
        {$roomTypesJoin}
        WHERE {$activeFilter}
          AND {$dateFilter}
          {$propertyFilter}
        ORDER BY
            CAST(COALESCE(NULLIF(TRIM(CAST(r.room_no AS CHAR)), ''), '999999') AS UNSIGNED),
            COALESCE(NULLIF(TRIM(CAST(r.room_no AS CHAR)), ''), ''),
            {$ciCol},
            o.id DESC
    ";

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        error_log('fo_fetch_rooming error [' . $section . ']: ' . $e->getMessage());
        return [];
    }
}


function fo_fetch_room_by_room_list(PDO $pdo, int $propertyId, string $date, string $section): array
{
    /*
       Room-by-room version of the Daily Reservations / Rooming List.

       fo_fetch_rooming() (above) returns ONE ROW PER BOOKING with all of a
       multi-room booking's rooms comma-joined into a single string - fine for
       Pax List totals, but not what's wanted here: a group booking with 3
       rooms must show as 3 separate rows, each with that room's own captured
       guest name(s), so the list reads room-by-room going down the page -
       the same level of detail as the Preload Guest Names screen.

       LEFT JOIN order_rooming (not INNER) so a legacy booking with no rooming
       detail rows at all still appears as a single fallback row instead of
       vanishing from the report.
    */
    if (!fo_table_exists($pdo, 'orders')) return [];

    $hasRooming   = fo_table_exists($pdo, 'order_rooming');
    $hasRooms     = fo_table_exists($pdo, 'rooms');
    $hasRoomTypes = fo_table_exists($pdo, 'room_types');
    $hasGuests    = fo_table_exists($pdo, 'guests');
    $hasAgents    = fo_table_exists($pdo, 'agents');

    $ciCol = fo_column_exists($pdo, 'orders', 'check_in')
        ? 'o.check_in'
        : (fo_column_exists($pdo, 'orders', 'arrival_date') ? 'o.arrival_date' : 'NULL');
    $coCol = fo_column_exists($pdo, 'orders', 'check_out')
        ? 'o.check_out'
        : (fo_column_exists($pdo, 'orders', 'departure_date') ? 'o.departure_date' : 'NULL');

    $oAdults = fo_column_exists($pdo, 'orders', 'adults') ? 'COALESCE(o.adults,0)' : '0';
    $oChildren = fo_column_exists($pdo, 'orders', 'kids')
        ? 'COALESCE(o.kids,0)'
        : (fo_column_exists($pdo, 'orders', 'children') ? 'COALESCE(o.children,0)' : '0');
    $oRoomsFallback = fo_column_exists($pdo, 'orders', 'totalrooms') ? 'COALESCE(o.totalrooms,1)' : '1';

    $roomingJoin = '';
    $roomNoExpr = "CASE WHEN {$oRoomsFallback} > 1 THEN CONCAT({$oRoomsFallback}, ' rooms') ELSE '' END";
    $adultsExpr = $oAdults;
    $childrenExpr = $oChildren;
    $roomIdExpr = fo_column_exists($pdo, 'orders', 'room_id') ? 'o.room_id' : 'NULL';
    $roomTypeIdExpr = fo_column_exists($pdo, 'orders', 'room_type_id') ? 'o.room_type_id' : 'NULL';
    $configExpr = "''";
    $roomGuestsExpr = "''";

    if ($hasRooming) {
        $roomingJoin = "LEFT JOIN order_rooming rmg ON rmg.order_id = o.id";
        $roomNoExpr = "NULLIF(TRIM(CAST(rmg.room_no AS CHAR)), '')";
        $roomIdExpr = fo_column_exists($pdo, 'order_rooming', 'room_id') ? 'rmg.room_id' : $roomIdExpr;
        $roomTypeIdExpr = fo_column_exists($pdo, 'order_rooming', 'room_type_id') ? 'rmg.room_type_id' : $roomTypeIdExpr;
        $adultsExpr = fo_column_exists($pdo, 'order_rooming', 'adults') ? "COALESCE(rmg.adults, {$oAdults})" : $oAdults;
        $childrenExpr = fo_column_exists($pdo, 'order_rooming', 'children') ? "COALESCE(rmg.children, {$oChildren})" : $oChildren;

        $configParts = [];
        if (fo_column_exists($pdo, 'order_rooming', 'bed_configuration')) $configParts[] = "NULLIF(TRIM(CAST(rmg.bed_configuration AS CHAR)), '')";
        if (fo_column_exists($pdo, 'order_rooming', 'special_room_request')) $configParts[] = "NULLIF(TRIM(CAST(rmg.special_room_request AS CHAR)), '')";
        elseif (fo_column_exists($pdo, 'order_rooming', 'notes')) $configParts[] = "NULLIF(TRIM(CAST(rmg.notes AS CHAR)), '')";
        $configExpr = $configParts ? ("TRIM(CONCAT_WS(' | ', " . implode(', ', $configParts) . "))") : "''";

        if (fo_table_exists($pdo, 'order_rooming_guests')) {
            $roomingGuestLinkColumn = '';
            foreach (['order_rooming_id', 'rooming_id'] as $linkCol) {
                if (fo_column_exists($pdo, 'order_rooming_guests', $linkCol)) {
                    $roomingGuestLinkColumn = $linkCol;
                    break;
                }
            }
            if ($roomingGuestLinkColumn !== '') {
                if (fo_column_exists($pdo, 'order_rooming_guests', 'first_name') && fo_column_exists($pdo, 'order_rooming_guests', 'last_name')) {
                    $nameExpr = "TRIM(CONCAT(COALESCE(org.first_name,''), ' ', COALESCE(org.last_name,'')))";
                } elseif (fo_column_exists($pdo, 'order_rooming_guests', 'firstname') && fo_column_exists($pdo, 'order_rooming_guests', 'lastname')) {
                    $nameExpr = "TRIM(CONCAT(COALESCE(org.firstname,''), ' ', COALESCE(org.lastname,'')))";
                } elseif (fo_column_exists($pdo, 'order_rooming_guests', 'guest_name')) {
                    $nameExpr = "org.guest_name";
                } elseif (fo_column_exists($pdo, 'order_rooming_guests', 'name')) {
                    $nameExpr = "org.name";
                } else {
                    $nameExpr = "''";
                }
                $roomGuestsExpr = "(SELECT GROUP_CONCAT(NULLIF(TRIM({$nameExpr}), '') ORDER BY org.id SEPARATOR ', ') FROM order_rooming_guests org WHERE org.`{$roomingGuestLinkColumn}` = rmg.id)";
            }
        }
    }

    $roomsJoin = ($hasRooms) ? "LEFT JOIN rooms r ON r.id = ({$roomIdExpr})" : '';
    $roomTypesJoin = ($hasRoomTypes) ? "LEFT JOIN room_types rt ON rt.id = ({$roomTypeIdExpr})" : '';
    $finalRoomNoExpr = "COALESCE(NULLIF(TRIM(CAST(r.room_no AS CHAR)), ''), {$roomNoExpr}, '')";

    $guestJoin = ($hasGuests && fo_column_exists($pdo, 'orders', 'guest_id'))
        ? 'LEFT JOIN guests g ON g.id = CAST(o.guest_id AS UNSIGNED)'
        : '';

    if ($hasAgents && fo_column_exists($pdo, 'orders', 'agent_id')) {
        $agentJoin = 'LEFT JOIN agents a ON a.id = o.agent_id';
        if (fo_column_exists($pdo, 'agents', 'firstname') && fo_column_exists($pdo, 'agents', 'lastname')) {
            $agentExpr = "TRIM(CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,'')))";
        } elseif (fo_column_exists($pdo, 'agents', 'name')) {
            $agentExpr = "COALESCE(a.name, '')";
        } else {
            $agentExpr = "''";
        }
    } else {
        $agentJoin = '';
        $agentExpr = "''";
    }

    $guestExpr = fo_guest_name_expr($pdo);
    $orderGuestExpr = fo_first_existing_order_expr($pdo, ['guest_name', 'guest', 'customer_name', 'client_name', 'pax_name', 'PaxName', 'name']);
    $bookingGuestSelectExpr = "COALESCE(NULLIF({$guestExpr}, ''), {$orderGuestExpr}, '')";

    $mealPlanExpr = fo_meal_plan_expr($pdo);
    $reasonExpr = fo_first_existing_order_expr($pdo, ['reason', 'special_occasion', 'notes']);
    $dietaryExpr = fo_first_existing_order_expr($pdo, ['dietary', 'dietary_notes', 'meal_notes']);
    $orderNoExpr = fo_column_exists($pdo, 'orders', 'order_no') ? 'o.order_no' : 'CAST(o.id AS CHAR)';
    $statusExpr = fo_column_exists($pdo, 'orders', 'booking_status')
        ? "COALESCE(NULLIF(o.booking_status,''), CAST(o.status AS CHAR), '')"
        : (fo_column_exists($pdo, 'orders', 'status') ? "COALESCE(o.status, '')" : "''");
    $kidsExpr = fo_rooming_kids_expr($pdo);

    $activeFilter = fo_active_status_filter_sql($pdo, 'o');

    /*
       Staggered-arrival fix: each rooming row is judged on ITS OWN dates
       (falling back to the order header when the row has none, and for
       legacy bookings with no rooming rows at all where rmg.* is NULL the
       COALESCE lands on the header dates automatically). A 7-room group
       where one room arrives two days early now lists that room on its
       actual arrival date instead of vanishing.
    */
    $effCi = $ciCol;
    $effCo = $coCol;
    if ($hasRooming && fo_column_exists($pdo, 'order_rooming', 'check_in') && fo_column_exists($pdo, 'order_rooming', 'check_out')) {
        $effCi = "COALESCE(rmg.check_in, {$ciCol})";
        $effCo = "COALESCE(rmg.check_out, {$coCol})";
    }

    switch ($section) {
        case 'arrivals':
            $dateFilter = "DATE({$effCi}) = :report_date";
            $params = [':report_date' => $date];
            break;
        case 'departures':
            $dateFilter = "DATE({$effCo}) = :report_date";
            $params = [':report_date' => $date];
            break;
        case 'stayovers':
            $dateFilter = "DATE({$effCi}) < :report_date_1 AND DATE({$effCo}) > :report_date_2";
            $params = [':report_date_1' => $date, ':report_date_2' => $date];
            break;
        default: // inhouse: Arrivals today + StayOvers (same-day departures excluded)
            $dateFilter = "DATE({$effCi}) <= :report_date_1 AND DATE({$effCo}) > :report_date_2";
            $params = [':report_date_1' => $date, ':report_date_2' => $date];
            break;
    }

    $propertyFilter = '';
    if ($propertyId > 0 && fo_column_exists($pdo, 'orders', 'property_id')) {
        $propertyFilter = 'AND o.property_id = :property_id';
        $params[':property_id'] = $propertyId;
    }

    /*
       Split-stay group flag: how many rooms the whole booking has, and the
       DISTINCT arrival/departure dates across all of its rooms. The renderer
       uses these to print "Part of N-room group — remaining rooms arrive
       DD/MM" on staggered bookings, so the front office immediately sees
       that today's single room is one wave of a larger group.
    */
    $groupTotalExpr = "0";
    $groupArrExpr   = "NULL";
    $groupDepExpr   = "NULL";
    if ($hasRooming) {
        $groupTotalExpr = "(SELECT COUNT(*) FROM order_rooming ram WHERE ram.order_id = o.id)";
        if (fo_column_exists($pdo, 'order_rooming', 'check_in') && fo_column_exists($pdo, 'order_rooming', 'check_out')) {
            $groupArrExpr = "(SELECT GROUP_CONCAT(DISTINCT DATE(COALESCE(ram2.check_in, {$ciCol})) ORDER BY DATE(COALESCE(ram2.check_in, {$ciCol})) SEPARATOR ',') FROM order_rooming ram2 WHERE ram2.order_id = o.id)";
            $groupDepExpr = "(SELECT GROUP_CONCAT(DISTINCT DATE(COALESCE(ram3.check_out, {$coCol})) ORDER BY DATE(COALESCE(ram3.check_out, {$coCol})) SEPARATOR ',') FROM order_rooming ram3 WHERE ram3.order_id = o.id)";
        }
    }

    $sql = "
        SELECT
            o.id                          AS order_id,
            {$orderNoExpr}                AS order_no,
            {$finalRoomNoExpr}             AS room_no,
            COALESCE(rt.title, '')        AS room_type,
            COALESCE({$configExpr}, '')   AS config,
            {$mealPlanExpr}               AS meal_plan,
            {$bookingGuestSelectExpr}     AS booking_guest_name,
            COALESCE({$roomGuestsExpr}, '') AS room_guests,
            {$reasonExpr}                 AS reason,
            {$dietaryExpr}                AS dietary,
            {$statusExpr}                 AS status_raw,
            {$agentExpr}                  AS agent_name,
            {$effCi}                      AS check_in,
            {$effCo}                      AS check_out,
            DATEDIFF({$effCo}, {$effCi})  AS nights,
            {$adultsExpr}                 AS adults,
            {$childrenExpr}               AS children,
            {$kidsExpr}                   AS kids_0_3,
            {$groupTotalExpr}             AS group_total_rooms,
            {$groupArrExpr}               AS group_arrival_dates,
            {$groupDepExpr}               AS group_departure_dates
        FROM orders o
        {$roomingJoin}
        {$guestJoin}
        {$agentJoin}
        {$roomsJoin}
        {$roomTypesJoin}
        WHERE {$activeFilter}
          AND {$dateFilter}
          {$propertyFilter}
        ORDER BY
            CAST(COALESCE(NULLIF(TRIM(CAST(r.room_no AS CHAR)), ''), '999999') AS UNSIGNED),
            COALESCE(NULLIF(TRIM(CAST(r.room_no AS CHAR)), ''), ''),
            {$ciCol},
            o.id ASC
    ";

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        error_log('fo_fetch_room_by_room_list error [' . $section . ']: ' . $e->getMessage());
        return [];
    }
}



function fo_count_physical_rooms_for_section(PDO $pdo, int $propertyId, string $date, string $section): int
{
    /*
       Physical room count for Pax List summary.

       Stayovers:
           check_in < report_date AND check_out > report_date
           Example: checked in yesterday or before, checking out tomorrow or beyond.

       In-house (Arrivals today + StayOvers):
           check_in <= report_date AND check_out > report_date
           Guests checking OUT today are counted under Departures, not In-House.
    */
    if (!fo_table_exists($pdo, 'orders') || !fo_table_exists($pdo, 'order_rooming')) {
        return 0;
    }

    $activeFilter = fo_active_status_filter_sql($pdo, 'o');

    /* Per-room effective dates (staggered-arrival fix): a room is a stayover /
       in-house based on ITS dates, header dates as fallback per row. The
       no-rooming-rows fallback branch below keeps pure header dates. */
    $hasRmgDates = fo_column_exists($pdo, 'order_rooming', 'check_in') && fo_column_exists($pdo, 'order_rooming', 'check_out');
    $effCi = $hasRmgDates ? "COALESCE(rmg.check_in, o.check_in)" : "o.check_in";
    $effCo = $hasRmgDates ? "COALESCE(rmg.check_out, o.check_out)" : "o.check_out";

    switch ($section) {
        case 'stayovers':
            $dateFilter       = "DATE({$effCi}) < :report_date_1 AND DATE({$effCo}) > :report_date_2";
            $headerDateFilter = "DATE(o.check_in) < :hdr_date_1 AND DATE(o.check_out) > :hdr_date_2";
            break;
        case 'inhouse':
            $dateFilter       = "DATE({$effCi}) <= :report_date_1 AND DATE({$effCo}) > :report_date_2";
            $headerDateFilter = "DATE(o.check_in) <= :hdr_date_1 AND DATE(o.check_out) > :hdr_date_2";
            break;
        default:
            return 0;
    }

    $propertySql = '';
    $propertySql2 = '';
    $params = [
        ':report_date_1' => $date,
        ':report_date_2' => $date,
    ];

    if ($propertyId > 0) {
        $propertySql = " AND o.property_id = :property_id ";
        $propertySql2 = " AND o.property_id = :property_id_2 ";
        $params[':property_id'] = $propertyId;
    }

    $roomIdCountSql = "
        SELECT COUNT(DISTINCT rmg.room_id)
        FROM order_rooming rmg
        INNER JOIN orders o ON o.id = rmg.order_id
        WHERE rmg.room_id IS NOT NULL
          AND rmg.room_id > 0
          AND {$dateFilter}
          AND {$activeFilter}
          {$propertySql}
    ";

    try {
        $stmt = $pdo->prepare($roomIdCountSql);
        $stmt->execute($params);
        $physicalRooms = (int)$stmt->fetchColumn();
        if ($physicalRooms > 0) return $physicalRooms;
    } catch (Throwable $e) {
        error_log('fo_count_physical_rooms_for_section room_id count error [' . $section . ']: ' . $e->getMessage());
    }

    /*
       Fallback for bookings not yet assigned to physical rooms:
       Count rooming slots/order totalrooms, but only if no physical rooms were found.
    */
    $fallbackSql = "
        SELECT COALESCE(SUM(x.rooms_used), 0)
        FROM (
            SELECT COUNT(*) AS rooms_used
            FROM order_rooming rmg
            INNER JOIN orders o ON o.id = rmg.order_id
            WHERE {$dateFilter}
              AND {$activeFilter}
              {$propertySql}
            UNION ALL
            SELECT COALESCE(SUM(o.totalrooms), 0) AS rooms_used
            FROM orders o
            WHERE {$headerDateFilter}
              AND {$activeFilter}
              {$propertySql2}
              AND NOT EXISTS (
                  SELECT 1
                  FROM order_rooming r2
                  WHERE r2.order_id = o.id
              )
        ) x
    ";

    try {
        $stmt = $pdo->prepare($fallbackSql);
        $fallbackParams = $params;
        $fallbackParams[':hdr_date_1'] = $date;
        $fallbackParams[':hdr_date_2'] = $date;
        if ($propertyId > 0) {
            $fallbackParams[':property_id_2'] = $propertyId;
        }
        $stmt->execute($fallbackParams);
        return (int)$stmt->fetchColumn();
    } catch (Throwable $e) {
        error_log('fo_count_physical_rooms_for_section fallback error [' . $section . ']: ' . $e->getMessage());
        return 0;
    }
}


function fo_count_inhouse_physical_rooms(PDO $pdo, int $propertyId, string $date): int
{
    return fo_count_physical_rooms_for_section($pdo, $propertyId, $date, 'inhouse');
}


/**
 * Total sellable/physical rooms for a property, used to detect a full house.
 * Counts rows in the `rooms` table (active where an is_active/status column
 * exists), scoped to the property when a property column is present. Result
 * is statically cached per property. Returns 0 when it can't be determined,
 * in which case callers should not flag "full".
 */
function fo_property_room_capacity(PDO $pdo, int $propertyId): int
{
    static $cache = [];
    if (array_key_exists($propertyId, $cache)) return $cache[$propertyId];

    if (!fo_table_exists($pdo, 'rooms')) {
        return $cache[$propertyId] = 0;
    }

    $where  = [];
    $params = [];

    if ($propertyId > 0 && fo_column_exists($pdo, 'rooms', 'property_id')) {
        $where[]        = 'property_id = :pid';
        $params[':pid'] = $propertyId;
    }

    // Exclude inactive/out-of-service rooms when such a flag exists.
    if (fo_column_exists($pdo, 'rooms', 'is_active')) {
        $where[] = 'COALESCE(is_active,1) = 1';
    } elseif (fo_column_exists($pdo, 'rooms', 'status')) {
        $where[] = "LOWER(CAST(COALESCE(status,'') AS CHAR)) NOT IN ('inactive','disabled','deleted','0','out_of_service','oos')";
    }

    $sql = 'SELECT COUNT(*) FROM rooms';
    if ($where) $sql .= ' WHERE ' . implode(' AND ', $where);

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $cache[$propertyId] = (int)$stmt->fetchColumn();
    } catch (Throwable $e) {
        error_log('fo_property_room_capacity error: ' . $e->getMessage());
        return $cache[$propertyId] = 0;
    }
}


function fo_fetch_pax_summary(PDO $pdo, int $propertyId, string $date): array {
    // Pax List fix: stayovers and in-house count DISTINCT physical rooms. In-house follows dashboard morning logic.
    $sections = [
        'arrivals'   => 'Arrivals',
        'stayovers'  => 'StayOvers',
        'departures' => 'Departures',
        'inhouse'    => 'In-House',
    ];

    $out = [];
    foreach ($sections as $key => $label) {
        $rows     = fo_fetch_rooming($pdo, $propertyId, $date, $key);
        $rooms    = 0;
        $adults   = 0;
        $children = 0;
        $kids     = 0;
        foreach ($rows as $r) {
            $rooms    += max(1, (int)($r['room_count'] ?? 1));
            $adults   += (int)($r['adults']   ?? 0);
            $children += (int)($r['children'] ?? 0);
            $kids     += (int)($r['kids_0_3'] ?? 0);
        }
        if (in_array($key, ['stayovers', 'inhouse'], true)) {
            $physicalRooms = fo_count_physical_rooms_for_section($pdo, $propertyId, $date, $key);
            if ($physicalRooms > 0) {
                $rooms = $physicalRooms;
            }
        }

        $out[$key] = [
            'label'    => $label,
            'rooms'    => $rooms,
            'adults'   => $adults,
            'children' => $children,
            'kids_0_3' => $kids,
            'guide'    => 0,
        ];
    }
    return $out;
}

/**
 * FAST set-based Pax matrix.
 *
 * The original fo_build_pax_matrix() called fo_fetch_pax_summary() once per day,
 * which itself fired 4 heavy detail queries + up to 2 physical-room counts per
 * day — ~180 queries for a 31-day range, each hydrating full guest/agent/room
 * joins just to be counted. This computes every day's counts for the whole
 * range in a handful of aggregate queries grouped by day.
 *
 * The section date rules and active-status filter mirror fo_fetch_rooming()
 * and fo_count_physical_rooms_for_section() exactly so totals match the
 * single-day summary. Returns [ 'Y-m-d' => ['arrivals'=>[...], ...], ... ]
 * or null if the schema is missing (caller falls back to the slow path).
 */
function fo_fetch_pax_matrix_fast(PDO $pdo, int $propertyId, string $startDate, string $endDate): ?array {
    if (!fo_table_exists($pdo, 'orders')) return null;

    $hasRooming  = fo_table_exists($pdo, 'order_rooming');
    $activeFilter = fo_active_status_filter_sql($pdo, 'o');

    $ciCol = fo_column_exists($pdo, 'orders', 'check_in')
           ? 'o.check_in'
           : (fo_column_exists($pdo, 'orders', 'arrival_date') ? 'o.arrival_date' : null);
    $coCol = fo_column_exists($pdo, 'orders', 'check_out')
           ? 'o.check_out'
           : (fo_column_exists($pdo, 'orders', 'departure_date') ? 'o.departure_date' : null);
    if ($ciCol === null || $coCol === null) return null;

    $oAdults   = fo_column_exists($pdo, 'orders', 'adults') ? 'COALESCE(o.adults,0)' : '0';
    $oChildren = fo_column_exists($pdo, 'orders', 'kids')
               ? 'COALESCE(o.kids,0)'
               : (fo_column_exists($pdo, 'orders', 'children') ? 'COALESCE(o.children,0)' : '0');
    // Kids 0-3 column resolution mirrors fo_rooming_kids_expr(): rooming first, order fallback.
    $kidsCandidates = ['kids_0_3_no_charge','kids_0_3','children_0_3_no_charge','children_0_3','kids_no_charge','infants','toddlers','babies'];
    $rmgKids03Col = null;
    $oKids03Col   = null;
    foreach ($kidsCandidates as $col) {
        if ($rmgKids03Col === null && fo_table_exists($pdo, 'order_rooming') && fo_column_exists($pdo, 'order_rooming', $col)) {
            $rmgKids03Col = $col;
        }
        if ($oKids03Col === null && fo_column_exists($pdo, 'orders', $col)) {
            $oKids03Col = $col;
        }
    }
    $oKids03 = $oKids03Col !== null ? "COALESCE(o.`{$oKids03Col}`,0)" : '0';
    $oRooms  = fo_column_exists($pdo, 'orders', 'totalrooms') ? 'COALESCE(o.totalrooms,1)' : '1';

    $propOrders = '';
    $baseParams = [':start' => $startDate, ':end' => $endDate];
    if ($propertyId > 0 && fo_column_exists($pdo, 'orders', 'property_id')) {
        $propOrders = ' AND o.property_id = :pid ';
        $baseParams[':pid'] = $propertyId;
    }

    // Rooming-level pax columns (fallback to order-level).
    $rmgHasDates    = $hasRooming && fo_column_exists($pdo, 'order_rooming', 'check_in') && fo_column_exists($pdo, 'order_rooming', 'check_out');
    $rmgHasAdults   = $hasRooming && fo_column_exists($pdo, 'order_rooming', 'adults');
    $rmgHasChildren = $hasRooming && fo_column_exists($pdo, 'order_rooming', 'children');
    $rmgHasKids03   = $hasRooming && ($rmgKids03Col !== null);
    $rmgHasRoomId   = $hasRooming && fo_column_exists($pdo, 'order_rooming', 'room_id');

    // Init result grid.
    $out = [];
    $ts  = strtotime($startDate);
    $endTs = strtotime($endDate);
    while ($ts <= $endTs) {
        $d = date('Y-m-d', $ts);
        $out[$d] = [
            'arrivals'   => ['rooms'=>0,'adults'=>0,'children'=>0,'kids_0_3'=>0],
            'stayovers'  => ['rooms'=>0,'adults'=>0,'children'=>0,'kids_0_3'=>0],
            'departures' => ['rooms'=>0,'adults'=>0,'children'=>0,'kids_0_3'=>0],
            'inhouse'    => ['rooms'=>0,'adults'=>0,'children'=>0,'kids_0_3'=>0],
        ];
        $ts = strtotime('+1 day', $ts);
    }

    /*
       Per-order-per-day counts.
       We aggregate at the ORDER level (one contribution per order per day) using
       order-level pax + room totals. This matches the single-day summary, whose
       room count for arrivals/departures sums room_count per matched order, and
       whose adults/children sum order/rooming pax per matched order.

       Rooming rows are collapsed to one row per order via a subquery so a
       multi-rooming booking is not double counted, mirroring fo_fetch_rooming's
       pre-aggregation. Per-room dates are used when present (staggered arrivals),
       header dates otherwise.
    */
    if ($rmgHasDates) {
        $rmgCi = 'COALESCE(rr.check_in, o.check_in)';
        $rmgCo = 'COALESCE(rr.check_out, o.check_out)';
    } else {
        $rmgCi = 'o.check_in';
        $rmgCo = 'o.check_out';
    }

    $rmgAdults   = $rmgHasAdults   ? 'SUM(COALESCE(rr.adults,0))'   : 'NULL';
    $rmgChildren = $rmgHasChildren ? 'SUM(COALESCE(rr.children,0))' : 'NULL';
    $rmgKids03   = $rmgHasKids03   ? "SUM(COALESCE(rr.`{$rmgKids03Col}`,0))" : 'NULL';

    // Effective per-order pax/rooms whether or not rooming rows exist.
    if ($hasRooming) {
        $roomingAgg = "
            LEFT JOIN (
                SELECT rr.order_id,
                       COUNT(*)          AS rmg_rooms,
                       {$rmgAdults}      AS rmg_adults,
                       {$rmgChildren}    AS rmg_children,
                       {$rmgKids03}      AS rmg_kids03,
                       MIN({$rmgCi})     AS rmg_ci,
                       MAX({$rmgCo})     AS rmg_co
                FROM order_rooming rr
                INNER JOIN orders o ON o.id = rr.order_id
                GROUP BY rr.order_id
            ) rmg ON rmg.order_id = o.id
        ";
        $adultsExpr   = "COALESCE(rmg.rmg_adults, {$oAdults})";
        $childrenExpr = "COALESCE(rmg.rmg_children, {$oChildren})";
        $kids03Expr   = "COALESCE(rmg.rmg_kids03, {$oKids03})";
        $roomsExpr    = "COALESCE(rmg.rmg_rooms, {$oRooms})";
        $effCi        = "DATE(COALESCE(rmg.rmg_ci, o.check_in))";
        $effCo        = "DATE(COALESCE(rmg.rmg_co, o.check_out))";
    } else {
        $roomingAgg   = '';
        $adultsExpr   = $oAdults;
        $childrenExpr = $oChildren;
        $kids03Expr   = $oKids03;
        $roomsExpr    = $oRooms;
        $effCi        = 'DATE(o.check_in)';
        $effCo        = 'DATE(o.check_out)';
    }

    // One row per order carrying its effective dates + pax/rooms, then bucket in PHP.
    $sql = "
        SELECT
            o.id                 AS order_id,
            {$effCi}             AS eff_ci,
            {$effCo}             AS eff_co,
            {$adultsExpr}        AS adults,
            {$childrenExpr}      AS children,
            {$kids03Expr}        AS kids_0_3,
            {$roomsExpr}         AS rooms
        FROM orders o
        {$roomingAgg}
        WHERE {$activeFilter}
          {$propOrders}
          AND DATE(o.check_out) >= :start
          AND DATE(o.check_in)  <= :end
    ";

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($baseParams);
        $orders = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        error_log('fo_fetch_pax_matrix_fast query error: ' . $e->getMessage());
        return null;
    }

    // Physical distinct-room counts per day for stayovers/inhouse (mirrors
    // fo_count_physical_rooms_for_section). Only when rooming+room_id exist.
    $physical = ['stayovers' => [], 'inhouse' => []];
    if ($hasRooming && $rmgHasRoomId) {
        $physCi = $rmgHasDates ? 'COALESCE(rr.check_in, o.check_in)' : 'o.check_in';
        $physCo = $rmgHasDates ? 'COALESCE(rr.check_out, o.check_out)' : 'o.check_out';
        $propPhys = '';
        $physParams = [':start' => $startDate, ':end' => $endDate];
        if ($propertyId > 0 && fo_column_exists($pdo, 'orders', 'property_id')) {
            $propPhys = ' AND o.property_id = :pid ';
            $physParams[':pid'] = $propertyId;
        }
        $physSql = "
            SELECT DATE({$physCi}) AS ci, DATE({$physCo}) AS co, rr.room_id AS room_id
            FROM order_rooming rr
            INNER JOIN orders o ON o.id = rr.order_id
            WHERE rr.room_id IS NOT NULL AND rr.room_id > 0
              AND {$activeFilter}
              {$propPhys}
              AND DATE({$physCo}) >= :start
              AND DATE({$physCi}) <= :end
        ";
        try {
            $ps = $pdo->prepare($physSql);
            $ps->execute($physParams);
            $physRows = $ps->fetchAll(PDO::FETCH_ASSOC);
            // Bucket distinct room_ids per day for each rule.
            $soRooms = []; $ihRooms = [];
            foreach (array_keys($out) as $d) { $soRooms[$d] = []; $ihRooms[$d] = []; }
            foreach ($physRows as $pr) {
                $ci = $pr['ci']; $co = $pr['co']; $rid = (int)$pr['room_id'];
                foreach (array_keys($out) as $d) {
                    // stayover: ci < d AND co > d ; inhouse: ci <= d AND co > d
                    if ($ci < $d && $co > $d) $soRooms[$d][$rid] = true;
                    if ($ci <= $d && $co > $d) $ihRooms[$d][$rid] = true;
                }
            }
            foreach (array_keys($out) as $d) {
                $physical['stayovers'][$d] = count($soRooms[$d]);
                $physical['inhouse'][$d]   = count($ihRooms[$d]);
            }
        } catch (Throwable $e) {
            error_log('fo_fetch_pax_matrix_fast physical error: ' . $e->getMessage());
        }
    }

    // Bucket each order into the day grid per section rules.
    foreach ($orders as $o) {
        $ci = $o['eff_ci']; $co = $o['eff_co'];
        $a  = (int)$o['adults']; $c = (int)$o['children']; $k = (int)$o['kids_0_3'];
        $rm = max(1, (int)$o['rooms']);
        foreach ($out as $d => &$day) {
            // arrivals: eff_ci == d
            if ($ci === $d) {
                $day['arrivals']['rooms']    += $rm;
                $day['arrivals']['adults']   += $a;
                $day['arrivals']['children'] += $c;
                $day['arrivals']['kids_0_3'] += $k;
            }
            // departures: eff_co == d
            if ($co === $d) {
                $day['departures']['rooms']    += $rm;
                $day['departures']['adults']   += $a;
                $day['departures']['children'] += $c;
                $day['departures']['kids_0_3'] += $k;
            }
            // stayovers: ci < d AND co > d
            if ($ci < $d && $co > $d) {
                $day['stayovers']['rooms']    += $rm;
                $day['stayovers']['adults']   += $a;
                $day['stayovers']['children'] += $c;
                $day['stayovers']['kids_0_3'] += $k;
            }
            // inhouse: ci <= d AND co > d
            if ($ci <= $d && $co > $d) {
                $day['inhouse']['rooms']    += $rm;
                $day['inhouse']['adults']   += $a;
                $day['inhouse']['children'] += $c;
                $day['inhouse']['kids_0_3'] += $k;
            }
        }
        unset($day);
    }

    // Prefer physical distinct-room counts for stayovers/inhouse where available.
    foreach ($out as $d => &$day) {
        if (isset($physical['stayovers'][$d]) && $physical['stayovers'][$d] > 0) {
            $day['stayovers']['rooms'] = $physical['stayovers'][$d];
        }
        if (isset($physical['inhouse'][$d]) && $physical['inhouse'][$d] > 0) {
            $day['inhouse']['rooms'] = $physical['inhouse'][$d];
        }
    }
    unset($day);

    return $out;
}

/**
 * Build the Pax List matrix for the report date plus the next N days.
 * Each entry is one calendar day carrying the four movement sections
 * (arrivals, stayovers, departures, in-house) with room/pax breakdowns.
 * Used by both the on-screen render and the Excel export so they match.
 */
function fo_build_pax_matrix(PDO $pdo, int $propertyId, string $startDate, int $days = 30): array {
    $startTs = strtotime($startDate);
    $endDate = date('Y-m-d', strtotime("+{$days} day", $startTs));

    // Fast path: one aggregate pass for the whole range.
    $fast = fo_fetch_pax_matrix_fast($pdo, $propertyId, $startDate, $endDate);

    $labels = ['arrivals'=>'Arrivals','stayovers'=>'StayOvers','departures'=>'Departures','inhouse'=>'In-House'];

    $rows = [];
    for ($i = 0; $i <= $days; $i++) {
        $ts   = strtotime("+{$i} day", $startTs);
        $dSql = date('Y-m-d', $ts);

        if ($fast !== null && isset($fast[$dSql])) {
            $summary = [];
            foreach ($labels as $key => $label) {
                $cell = $fast[$dSql][$key];
                $summary[$key] = [
                    'label'    => $label,
                    'rooms'    => (int)$cell['rooms'],
                    'adults'   => (int)$cell['adults'],
                    'children' => (int)$cell['children'],
                    'kids_0_3' => (int)$cell['kids_0_3'],
                    'guide'    => 0,
                ];
            }
        } else {
            // Fallback to the original per-day path if the fast query is unavailable.
            $summary = fo_fetch_pax_summary($pdo, $propertyId, $dSql);
        }

        $rows[] = [
            'date'    => $dSql,
            'label'   => date('D d/m/Y', $ts),
            'is_base' => ($i === 0),
            'summary' => $summary,
        ];
    }
    return $rows;
}

function fo_report_title(string $report): string {
    switch ($report) {
        case 'pax_list':         return 'Pax List';
        case 'inhouse_activity': return 'In-House Guest Activity Sheet';
        case 'inhouse_by_room':  return 'In-House by Room';
        case 'bulk_guest_registration': return 'Bulk Guest Registration Forms';
        case 'preload_guest_names': return 'Preload Tomorrow Guest Names';
        default:                 return 'Daily Reservations / Rooming List';
    }
}

function fo_output_xls_start(string $filename): void {
    while (ob_get_level() > 0) { @ob_end_clean(); }
    header('Content-Type: application/vnd.ms-excel; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Pragma: no-cache');
    header('Expires: 0');
    echo "\xEF\xBB\xBF";
}

function fo_render_rooming_list(PDO $pdo, int $propertyId, string $date, bool $export = false): void {
    $sections = [
        'stayovers'  => ['Stay Overs',  'st-stayovers',  'bi-moon-stars'],
        'arrivals'   => ['Arrivals',    'st-arrivals',   'bi-door-open'],
        'departures' => ['Departures',  'st-departures', 'bi-door-closed'],
        'inhouse'    => ['In-House',    'st-inhouse',    'bi-house-door'],
    ];
    foreach ($sections as $key => [$label, $cls, $icon]) {
        $rows = fo_fetch_room_by_room_list($pdo, $propertyId, $date, $key);
        $count = count($rows);
        echo '<div class="report-section">';
        echo '<div class="section-title ' . $cls . '">';
        echo '<i class="bi ' . $icon . '"></i>';
        echo h($label);
        echo '<span style="margin-left:auto;font-size:.7rem;opacity:.75;">' . $count . ' room' . ($count !== 1 ? 's' : '') . '</span>';
        echo '</div>';
        echo '<div class="table-responsive"><table class="fo-table">';
        echo '<thead><tr>';
        foreach (['Room / Type','Room Guest(s)','Booking / Agent','Meal Plan','Arrival','Departure','Nights','Adults','Children','Reason','Config','Dietary','Status'] as $col) {
            echo '<th>' . h($col) . '</th>';
        }
        echo '</tr></thead><tbody>';
        if (!$rows) {
            echo '<tr><td colspan="13" class="fo-empty" style="padding:16px;text-align:center;">No ' . h($label) . ' records for this date.</td></tr>';
        }
        foreach ($rows as $r) {
            $roomNo   = trim((string)($r['room_no']   ?? ''));
            if ($roomNo === '') $roomNo = 'Unallocated';
            $roomType = trim((string)($r['room_type'] ?? ''));
            $statusLabel = fo_status_label($r['status_raw'] ?? '');
            $statusLower = strtolower($statusLabel);
            if (in_array($statusLower, ['confirmed','reserved'], true)) {
                $badgeCls = 'fo-badge-confirmed';
            } elseif (in_array($statusLower, ['in house','in-house','checked in'], true)) {
                $badgeCls = 'fo-badge-inhouse';
            } elseif ($statusLower === 'checked out') {
                $badgeCls = 'fo-badge-checkout';
            } elseif ($statusLower === 'provisional') {
                $badgeCls = 'fo-badge-provisional';
            } else {
                $badgeCls = '';
            }

            $roomGuests = trim((string)($r['room_guests'] ?? ''));
            $bookingName = trim((string)($r['booking_guest_name'] ?? ''));
            $agentName = trim((string)($r['agent_name'] ?? ''));

            echo '<tr>';
            echo '<td>';
            if ($roomNo !== '')   echo '<div class="fo-room-no">' . h($roomNo) . '</div>';
            if ($roomType !== '') echo '<div style="font-size:.7rem;color:#64748b;">' . h($roomType) . '</div>';
            echo '</td>';
            echo '<td class="fo-guest">';
            if ($roomGuests !== '') {
                echo h($roomGuests);
            } else {
                echo '<span style="color:#94a3b8;font-style:italic;">Not yet captured</span>';
            }
            echo '</td>';
            echo '<td>' . h($bookingName ?: ('Booking #' . ($r['order_id'] ?? ''))) . ($agentName !== '' ? '<div style="font-size:.7rem;color:#64748b;">' . h($agentName) . '</div>' : '');

            /*
               Split-stay group flag: when this room belongs to a multi-room
               booking whose rooms arrive/depart on different dates, say so on
               the line - otherwise a single early room reads like a 1-room
               booking and the rest of the group surprises everyone later.
            */
            $groupTotal = (int)($r['group_total_rooms'] ?? 0);
            if ($groupTotal > 1) {
                $ownCi = substr((string)($r['check_in'] ?? ''), 0, 10);
                $ownCo = substr((string)($r['check_out'] ?? ''), 0, 10);
                $arrDates = array_values(array_filter(array_unique(explode(',', (string)($r['group_arrival_dates'] ?? '')))));
                $depDates = array_values(array_filter(array_unique(explode(',', (string)($r['group_departure_dates'] ?? '')))));
                $groupNote = '';
                if ($key === 'arrivals') {
                    $otherDates = array_values(array_diff($arrDates, [$ownCi]));
                    if ($otherDates) {
                        $past = []; $future = [];
                        foreach ($otherDates as $d) { if ($d < $date) $past[] = $d; else $future[] = $d; }
                        $parts = [];
                        if ($past)   $parts[] = 'other rooms arrived ' . implode(', ', array_map('fo_display_date', $past));
                        if ($future) $parts[] = 'remaining rooms arrive ' . implode(', ', array_map('fo_display_date', $future));
                        $groupNote = 'Part of ' . $groupTotal . '-room group — ' . implode('; ', $parts);
                    }
                } elseif ($key === 'departures') {
                    $otherDates = array_values(array_diff($depDates, [$ownCo]));
                    if ($otherDates) {
                        $past = []; $future = [];
                        foreach ($otherDates as $d) { if ($d < $date) $past[] = $d; else $future[] = $d; }
                        $parts = [];
                        if ($past)   $parts[] = 'other rooms departed ' . implode(', ', array_map('fo_display_date', $past));
                        if ($future) $parts[] = 'remaining rooms depart ' . implode(', ', array_map('fo_display_date', $future));
                        $groupNote = 'Part of ' . $groupTotal . '-room group — ' . implode('; ', $parts);
                    }
                } else { // stayovers / in-house: flag group rooms still to arrive
                    $futureArrivals = [];
                    foreach ($arrDates as $d) {
                        if ($d > $date) $futureArrivals[] = $d;
                    }
                    if ($futureArrivals) {
                        $groupNote = 'Part of ' . $groupTotal . '-room group — remaining rooms arrive ' . implode(', ', array_map('fo_display_date', $futureArrivals));
                    }
                }
                if ($groupNote !== '') {
                    echo '<div style="font-size:.68rem;color:#7c3aed;font-weight:700;margin-top:2px;"><i class="bi bi-people-fill" style="margin-right:3px;"></i>' . h($groupNote) . '</div>';
                }
            }

            echo '</td>';
            $mealPlan = strtoupper(trim((string)($r['meal_plan'] ?? '')));
            if (!in_array($mealPlan, ['HB','BB'], true)) $mealPlan = '';
            echo '<td style="text-align:center;font-weight:800;">' . h($mealPlan) . '</td>';
            echo '<td>' . h(fo_display_date($r['check_in']  ?? '')) . '</td>';
            echo '<td>' . h(fo_display_date($r['check_out'] ?? '')) . '</td>';
            echo '<td class="fo-nights" style="text-align:center;">' . (int)($r['nights'] ?? 0) . '</td>';
            echo '<td style="text-align:center;">' . (int)($r['adults']   ?? 0) . '</td>';
            echo '<td style="text-align:center;">' . (int)($r['children'] ?? 0) . '</td>';
            echo '<td>' . h($r['reason']  ?? '') . '</td>';
            $configText = trim((string)($r['config'] ?? ''));
            if ($configText === '') $configText = $roomType;
            echo '<td>' . h($configText) . '</td>';
            echo '<td>' . h($r['dietary'] ?? '') . '</td>';
            echo '<td>' . ($badgeCls ? '<span class="fo-badge ' . $badgeCls . '">' . h($statusLabel) . '</span>' : h($statusLabel)) . '</td>';
            echo '</tr>';
        }
        echo '</tbody></table></div></div>';
    }
}

function fo_render_pax_list(PDO $pdo, int $propertyId, string $date): void {
    // Build the 31-day matrix once; the base row (report date) feeds the summary cards.
    $matrix  = fo_build_pax_matrix($pdo, $propertyId, $date, 30);
    $summary = $matrix[0]['summary'] ?? fo_fetch_pax_summary($pdo, $propertyId, $date);
    $sectionClasses = ['arrivals'=>'pc-arrivals','stayovers'=>'pc-stayovers','departures'=>'pc-departures','inhouse'=>'pc-inhouse'];
    $sectionIcons   = ['arrivals'=>'bi-door-open','stayovers'=>'bi-moon-stars','departures'=>'bi-door-closed','inhouse'=>'bi-house-door'];

    // Full-house detection: flag In-House red when occupied rooms reach capacity.
    $capacity            = fo_property_room_capacity($pdo, $propertyId);
    $baseInhouseRooms    = (int)($summary['inhouse']['rooms'] ?? 0);
    $baseIsFullHouse     = ($capacity > 0 && $baseInhouseRooms >= $capacity);

    echo '<div class="pax-summary-wrap">';
    echo '<div class="section-title st-arrivals" style="margin-top:0;"><i class="bi bi-people"></i>Accommodation Summary &mdash; ' . h(fo_display_date($date)) . '</div>';

    // Cards
    echo '<div class="pax-band">';
    foreach ($summary as $key => $s) {
        $cls  = $sectionClasses[$key] ?? 'pc-inhouse';
        $icon = $sectionIcons[$key]   ?? 'bi-house-door';
        $total = (int)$s['adults'] + (int)$s['children'] + (int)$s['kids_0_3'];
        $cardFull  = ($key === 'inhouse' && $baseIsFullHouse);
        $cardStyle = $cardFull ? ' style="background:#dc2626;color:#fff;border:2px solid #991b1b;"' : '';
        echo '<div class="pax-card ' . $cls . '"' . $cardStyle . '>';
        echo '<div class="pax-card-label"><i class="bi ' . $icon . ' me-1"></i>' . h($s['label']);
        if ($cardFull) echo ' <span style="background:#fff;color:#dc2626;font-size:.6em;font-weight:900;padding:1px 6px;border-radius:8px;margin-left:4px;">FULL</span>';
        echo '</div>';
        echo '<div class="pax-total">' . (int)$s['rooms'];
        if ($key === 'inhouse' && $capacity > 0) echo ' <span style="font-size:.5em;opacity:.75;">/ ' . (int)$capacity . '</span>';
        echo ' <span style="font-size:.55em;opacity:.7;">rooms</span></div>';
        echo '<div class="pax-card-stats">';
        echo '<div class="pax-stat"><div class="pax-stat-val">' . (int)$s['adults']   . '</div><div class="pax-stat-key">Adults</div></div>';
        echo '<div class="pax-stat"><div class="pax-stat-val">' . (int)$s['children'] . '</div><div class="pax-stat-key">Ch 4-12</div></div>';
        echo '<div class="pax-stat"><div class="pax-stat-val">' . (int)$s['kids_0_3'] . '</div><div class="pax-stat-key">Ch 0-3</div></div>';
        echo '</div>';
        echo '</div>';
    }
    echo '</div>';

    // 30-day forward matrix (report date + next 30 days going down)
    $sectionOrder = array_keys($summary);
    $subCols      = ['Rooms','Adults','Ch 4-12','Ch 0-3','Guide'];

    echo '<div class="section-title st-arrivals"><i class="bi bi-calendar3"></i>30-Day Forecast &mdash; from ' . h(fo_display_date($date)) . ($capacity > 0 ? ' &nbsp;<span style="font-weight:600;opacity:.85;">(House capacity: ' . (int)$capacity . ' rooms — In-House turns red when full)</span>' : '') . '</div>';
    echo '<div class="table-responsive"><table class="fo-table fo-pax-matrix">';
    echo '<thead><tr><th rowspan="2" style="vertical-align:middle;">Date</th>';
    foreach ($summary as $s) echo '<th colspan="5" style="text-align:center;">' . h($s['label']) . '</th>';
    echo '</tr><tr>';
    foreach ($summary as $s) {
        foreach ($subCols as $col) echo '<th style="text-align:center;">' . h($col) . '</th>';
    }
    echo '</tr></thead><tbody>';

    foreach ($matrix as $row) {
        $rowCls = $row['is_base'] ? ' class="fo-pax-base"' : '';
        $dayInhouseRooms = (int)($row['summary']['inhouse']['rooms'] ?? 0);
        $dayIsFull       = ($capacity > 0 && $dayInhouseRooms >= $capacity);
        echo '<tr' . $rowCls . '>';
        echo '<td style="white-space:nowrap;"><strong>' . h($row['label']) . '</strong></td>';
        foreach ($sectionOrder as $key) {
            $s = $row['summary'][$key];
            $roomsFull  = ($key === 'inhouse' && $dayIsFull);
            $roomsStyle = $roomsFull
                ? 'text-align:center;font-weight:900;background:#dc2626;color:#fff;'
                : 'text-align:center;font-weight:800;';
            echo '<td style="' . $roomsStyle . '">' . (int)$s['rooms'] . ($roomsFull ? ' <span style="font-size:.7em;">FULL</span>' : '') . '</td>';
            echo '<td style="text-align:center;">'                  . (int)$s['adults']   . '</td>';
            echo '<td style="text-align:center;">'                  . (int)$s['children'] . '</td>';
            echo '<td style="text-align:center;">'                  . (int)$s['kids_0_3'] . '</td>';
            echo '<td style="text-align:center;color:#94a3b8;">&mdash;</td>';
        }
        echo '</tr>';
    }
    echo '</tbody></table></div>';
    echo '</div>';
}

/**
 * Nicely-formatted Excel export for the Pax List: summary cards as a small
 * table, then the 31-day matrix with banded rows and grouped section headers.
 */
function fo_render_pax_list_xls(PDO $pdo, int $propertyId, string $date): void {
    $matrix       = fo_build_pax_matrix($pdo, $propertyId, $date, 30);
    $summary      = $matrix[0]['summary'] ?? fo_fetch_pax_summary($pdo, $propertyId, $date);
    $sectionOrder = array_keys($summary);
    $subCols      = ['Rooms','Adults','Ch 4-12','Ch 0-3','Guide'];
    $sectionBg    = ['arrivals'=>'#dbeafe','stayovers'=>'#fef9c3','departures'=>'#fee2e2','inhouse'=>'#dcfce7'];

    // Full-house detection.
    $capacity        = fo_property_room_capacity($pdo, $propertyId);
    $baseInhouse     = (int)($summary['inhouse']['rooms'] ?? 0);
    $baseIsFullHouse = ($capacity > 0 && $baseInhouse >= $capacity);

    // Accommodation summary for the report date
    echo '<table style="border-collapse:collapse;margin-bottom:14px;">';
    echo '<tr><td colspan="6" style="background:#0f172a;color:#fff;font-weight:bold;padding:6px 8px;">Accommodation Summary &mdash; ' . h(fo_display_date($date)) . ($capacity > 0 ? '  (House capacity: ' . (int)$capacity . ' rooms)' : '') . '</td></tr>';
    echo '<tr>';
    foreach (['Movement','Rooms','Adults','Ch 4-12','Ch 0-3','Total Pax'] as $c) {
        echo '<th style="background:#1e293b;color:#fff;padding:5px 8px;border:1px solid #334155;text-align:center;">' . h($c) . '</th>';
    }
    echo '</tr>';
    foreach ($summary as $key => $s) {
        $rowFull = ($key === 'inhouse' && $baseIsFullHouse);
        $bg      = $rowFull ? '#dc2626' : ($sectionBg[$key] ?? '#f1f5f9');
        $fg      = $rowFull ? 'color:#fff;' : '';
        $total   = (int)$s['adults'] + (int)$s['children'] + (int)$s['kids_0_3'];
        $roomsTxt = (int)$s['rooms'] . ($rowFull ? ' FULL' : (($key === 'inhouse' && $capacity > 0) ? ' / ' . (int)$capacity : ''));
        echo '<tr>';
        echo '<td style="background:' . $bg . ';' . $fg . 'font-weight:bold;padding:4px 8px;border:1px solid #cbd5e1;">' . h($s['label']) . '</td>';
        echo '<td style="background:' . $bg . ';' . $fg . 'text-align:center;font-weight:bold;padding:4px 8px;border:1px solid #cbd5e1;">' . $roomsTxt . '</td>';
        echo '<td style="text-align:center;padding:4px 8px;border:1px solid #cbd5e1;">' . (int)$s['adults'] . '</td>';
        echo '<td style="text-align:center;padding:4px 8px;border:1px solid #cbd5e1;">' . (int)$s['children'] . '</td>';
        echo '<td style="text-align:center;padding:4px 8px;border:1px solid #cbd5e1;">' . (int)$s['kids_0_3'] . '</td>';
        echo '<td style="text-align:center;font-weight:bold;padding:4px 8px;border:1px solid #cbd5e1;">' . (int)$total . '</td>';
        echo '</tr>';
    }
    echo '</table>';

    // 31-day matrix
    echo '<table style="border-collapse:collapse;">';
    echo '<tr><td colspan="' . (1 + count($sectionOrder) * 5) . '" style="background:#0f172a;color:#fff;font-weight:bold;padding:6px 8px;">30-Day Forecast &mdash; from ' . h(fo_display_date($date)) . ($capacity > 0 ? '  (In-House cells turn red when house is full: ' . (int)$capacity . ' rooms)' : '') . '</td></tr>';

    // Grouped section header
    echo '<tr>';
    echo '<th rowspan="2" style="background:#1e293b;color:#fff;padding:5px 8px;border:1px solid #334155;vertical-align:middle;">Date</th>';
    foreach ($summary as $key => $s) {
        $bg = $sectionBg[$key] ?? '#e2e8f0';
        echo '<th colspan="5" style="background:' . $bg . ';color:#0f172a;padding:5px 8px;border:1px solid #94a3b8;text-align:center;">' . h($s['label']) . '</th>';
    }
    echo '</tr>';
    // Sub header
    echo '<tr>';
    foreach ($sectionOrder as $key) {
        $bg = $sectionBg[$key] ?? '#e2e8f0';
        foreach ($subCols as $c) {
            echo '<th style="background:' . $bg . ';color:#334155;padding:4px 6px;border:1px solid #94a3b8;text-align:center;font-size:12px;">' . h($c) . '</th>';
        }
    }
    echo '</tr>';

    // Data rows (banded)
    $i = 0;
    foreach ($matrix as $row) {
        $i++;
        if (!empty($row['is_base'])) {
            $rowBg = '#fff7ed';
            $dateStyle = 'font-weight:bold;background:#fdba74;';
        } else {
            $rowBg = ($i % 2 === 0) ? '#f8fafc' : '#ffffff';
            $dateStyle = 'font-weight:bold;background:#e2e8f0;';
        }
        $dayInhouse = (int)($row['summary']['inhouse']['rooms'] ?? 0);
        $dayIsFull  = ($capacity > 0 && $dayInhouse >= $capacity);
        echo '<tr>';
        echo '<td style="' . $dateStyle . 'white-space:nowrap;padding:4px 8px;border:1px solid #cbd5e1;">' . h($row['label']) . '</td>';
        foreach ($sectionOrder as $key) {
            $s = $row['summary'][$key];
            $roomsFull = ($key === 'inhouse' && $dayIsFull);
            $roomsBg   = $roomsFull ? '#dc2626' : $rowBg;
            $roomsFg   = $roomsFull ? 'color:#fff;' : '';
            $roomsTxt  = (int)$s['rooms'] . ($roomsFull ? ' FULL' : '');
            echo '<td style="text-align:center;font-weight:bold;background:' . $roomsBg . ';' . $roomsFg . 'padding:4px 6px;border:1px solid #cbd5e1;">' . $roomsTxt . '</td>';
            echo '<td style="text-align:center;background:' . $rowBg . ';padding:4px 6px;border:1px solid #cbd5e1;">' . (int)$s['adults'] . '</td>';
            echo '<td style="text-align:center;background:' . $rowBg . ';padding:4px 6px;border:1px solid #cbd5e1;">' . (int)$s['children'] . '</td>';
            echo '<td style="text-align:center;background:' . $rowBg . ';padding:4px 6px;border:1px solid #cbd5e1;">' . (int)$s['kids_0_3'] . '</td>';
            echo '<td style="text-align:center;color:#94a3b8;background:' . $rowBg . ';padding:4px 6px;border:1px solid #cbd5e1;">-</td>';
        }
        echo '</tr>';
    }
    echo '</table>';
}

function fo_render_inhouse_activity(PDO $pdo, int $propertyId, string $date): void {
    $rows = fo_fetch_rooming($pdo, $propertyId, $date, 'inhouse');
    $mealGroups = [
        'Morning'   => ['st-morning',   'bi-sunrise',      '#713f12'],
        'Afternoon' => ['st-afternoon', 'bi-sun',          '#7c2d12'],
        'Dinner'    => ['st-dinner',    'bi-moon-stars',   '#831843'],
    ];
    foreach ($mealGroups as $group => [$cls, $icon, $color]) {
        echo '<div class="fo-meal-section">';
        echo '<div class="section-title ' . $cls . '"><i class="bi ' . $icon . '"></i>' . h($group) . '</div>';
        echo '<div class="table-responsive"><table class="fo-table">';
        echo '<thead><tr>';
        foreach (['Date','Guest Name','Room #','Company','Activity','Time','Booked By','Voucher #','Amount'] as $col) echo '<th>' . h($col) . '</th>';
        echo '</tr></thead><tbody>';
        if (!$rows) {
            echo '<tr><td colspan="9" class="fo-empty" style="padding:14px;text-align:center;">No in-house guests for this date.</td></tr>';
        }
        foreach ($rows as $r) {
            echo '<tr>';
            echo '<td>' . h(fo_display_date($date)) . '</td>';
            echo '<td class="fo-guest">' . h($r['guest_name'] ?: ('Booking #' . ($r['order_id'] ?? ''))) . '</td>';
            echo '<td class="fo-room-no">' . h($r['room_no']    ?? '') . '</td>';
            echo '<td>' . h($r['company']    ?? '') . '</td>';
            echo '<td style="min-width:120px;"></td>';
            echo '<td style="min-width:70px;"></td>';
            echo '<td>' . h($r['agent_name'] ?? '') . '</td>';
            echo '<td>' . h($r['voucher']    ?? '') . '</td>';
            echo '<td style="min-width:80px;"></td>';
            echo '</tr>';
        }
        echo '</tbody></table></div></div>';
    }
}

function fo_render_inhouse_by_room(PDO $pdo, int $propertyId, string $date): void {
    $rows = fo_fetch_rooming($pdo, $propertyId, $date, 'inhouse');
    $days = [];
    for ($i = 0; $i < 3; $i++) {
        $ts     = strtotime("+{$i} day", strtotime($date));
        $days[] = [
            'date'   => date('Y-m-d', $ts),
            'label'  => date('D d/m/Y', $ts),
            'day_no' => $i + 1,
        ];
    }

    echo '<div class="report-section">';
    echo '<div class="section-title st-inhouse"><i class="bi bi-layout-three-columns"></i>In-House by Room</div>';
    echo '<div class="table-responsive"><table class="fo-table">';
    echo '<thead><tr>';
    echo '<th rowspan="2">Room No</th><th rowspan="2">Guest Name</th><th rowspan="2">Pax</th><th rowspan="2">Dble/Twin</th><th rowspan="2">Status</th><th rowspan="2">No of Nights</th>';
    foreach ($days as $d) {
        echo '<th colspan="3" class="text-center">Day ' . (int)$d['day_no'] . ' - ' . h($d['label']) . '</th>';
    }
    echo '</tr><tr>';
    foreach ($days as $d) {
        echo '<th>Morning</th><th>Afternoon</th><th>Dinner</th>';
    }
    echo '</tr></thead><tbody>';

    if (!$rows) {
        echo '<tr><td colspan="15" class="text-muted">No in-house guests.</td></tr>';
    }

    foreach ($rows as $r) {
        $pax = (int)($r['adults'] ?? 0) + (int)($r['children'] ?? 0) + (int)($r['kids_0_3'] ?? 0);
        echo '<tr>';
        echo '<td>' . h($r['room_no']    ?? '') . '</td>';
        echo '<td>' . h($r['guest_name'] ?: ('Booking #' . ($r['order_id'] ?? ''))) . '</td>';
        echo '<td class="text-end">' . (int)$pax . '</td>';
        echo '<td>' . h(((int)($r['adults'] ?? 0) >= 2) ? 'D' : 'S') . '</td>';
        echo '<td>' . h(fo_status_label($r['status_raw'] ?? '')) . '</td>';
        echo '<td class="text-end">' . (int)($r['nights'] ?? 0) . '</td>';
        foreach ($days as $d) {
            $marker = '';
            if (fo_date_sql_value((string)($r['check_in']  ?? '')) === $d['date']) $marker = 'CI';
            if (fo_date_sql_value((string)($r['check_out'] ?? '')) === $d['date']) $marker = 'CO';
            if ($marker === 'CI') {
                echo '<td><span class="fo-ci-marker">CI</span></td><td></td><td></td>';
            } elseif ($marker === 'CO') {
                echo '<td><span class="fo-co-marker">CO</span></td><td></td><td></td>';
            } else {
                echo '<td></td><td></td><td></td>';
            }
        }
        echo '</tr>';
    }
    echo '</tbody></table></div></div>';
}


function fo_pdf_logo_path(): string {
    $paths = [
        __DIR__ . '/../../assets/images/logo.png',
        __DIR__ . '/../../assets/img/logo.png',
        __DIR__ . '/../../assets/logo.png',
        __DIR__ . '/../../uploads/logo.png',
        __DIR__ . '/../../../assets/images/logo.png',
        __DIR__ . '/../../../assets/img/logo.png',
        __DIR__ . '/../../../uploads/logo.png',
        ($_SERVER['DOCUMENT_ROOT'] ?? '') . '/rooms/assets/images/logo.png',
        ($_SERVER['DOCUMENT_ROOT'] ?? '') . '/rooms/assets/img/logo.png',
        ($_SERVER['DOCUMENT_ROOT'] ?? '') . '/assets/images/logo.png',
        ($_SERVER['DOCUMENT_ROOT'] ?? '') . '/assets/img/logo.png',
        ($_SERVER['DOCUMENT_ROOT'] ?? '') . '/uploads/logo.png',
    ];

    foreach ($paths as $path) {
        if ($path && is_file($path) && is_readable($path)) {
            $ext = strtolower(pathinfo($path, PATHINFO_EXTENSION));
            if (in_array($ext, ['png','jpg','jpeg'], true)) return $path;
        }
    }
    return '';
}

function fo_pdf_load_fpdf(): void {
    if (class_exists('FPDF')) return;

    $paths = [
        __DIR__ . '/fpdf.php',
        __DIR__ . '/../fpdf.php',
        __DIR__ . '/../../fpdf.php',
        __DIR__ . '/../../includes/fpdf.php',
        __DIR__ . '/../../../fpdf.php',
        ($_SERVER['DOCUMENT_ROOT'] ?? '') . '/fpdf.php',
        ($_SERVER['DOCUMENT_ROOT'] ?? '') . '/rooms/fpdf.php',
        ($_SERVER['DOCUMENT_ROOT'] ?? '') . '/rooms/modules/reservations/fpdf.php',
        ($_SERVER['DOCUMENT_ROOT'] ?? '') . '/rooms/admin/fpdf.php',
        ($_SERVER['DOCUMENT_ROOT'] ?? '') . '/admin/fpdf.php',
        ($_SERVER['DOCUMENT_ROOT'] ?? '') . '/batonka/modules/reservations/fpdf.php',
        ($_SERVER['DOCUMENT_ROOT'] ?? '') . '/batonka/admin/fpdf.php',
    ];

    foreach ($paths as $path) {
        if ($path && is_file($path) && is_readable($path)) {
            require_once $path;
            if (class_exists('FPDF')) return;
        }
    }

    throw new RuntimeException('FPDF was not found. Please place fpdf.php in this module folder or in /rooms/fpdf.php.');
}

function fo_pdf_clean_text($value): string {
    $value = html_entity_decode(strip_tags((string)$value), ENT_QUOTES, 'UTF-8');
    $value = preg_replace('/\s+/u', ' ', trim($value));
    $map = [
        '–'=>'-', '—'=>'-', '•'=>'-', '’'=>"'", '‘'=>"'", '“'=>'"', '”'=>'"',
        'é'=>'e','è'=>'e','ê'=>'e','ë'=>'e','á'=>'a','à'=>'a','â'=>'a','ä'=>'a',
        'í'=>'i','ì'=>'i','î'=>'i','ï'=>'i','ó'=>'o','ò'=>'o','ô'=>'o','ö'=>'o',
        'ú'=>'u','ù'=>'u','û'=>'u','ü'=>'u','ñ'=>'n','ç'=>'c',
    ];
    $value = strtr($value, $map);
    $converted = @iconv('UTF-8', 'ISO-8859-1//TRANSLIT//IGNORE', $value);
    return $converted !== false ? $converted : preg_replace('/[^\x20-\x7E]/', '', $value);
}

function fo_rooming_pdf_collect_data(PDO $pdo, int $propertyId, string $date): array {
    $sections = [
        'stayovers'  => ['label'=>'Stay Overs',  'color'=>[219,234,254], 'text'=>[30,64,175]],
        'arrivals'   => ['label'=>'Arrivals',    'color'=>[220,252,231], 'text'=>[20,83,45]],
        'departures' => ['label'=>'Departures',  'color'=>[254,226,226], 'text'=>[127,29,29]],
        'inhouse'    => ['label'=>'In-House',    'color'=>[237,233,254], 'text'=>[76,29,149]],
    ];

    $out = [];
    $grand = ['bookings'=>0,'rooms'=>0,'adults'=>0,'children'=>0,'kids_0_3'=>0];

    foreach ($sections as $key => $meta) {
        $rows = fo_fetch_room_by_room_list($pdo, $propertyId, $date, $key);
        $tot = ['bookings'=>count($rows),'rooms'=>count($rows),'adults'=>0,'children'=>0,'kids_0_3'=>0];
        foreach ($rows as $r) {
            $tot['adults']   += (int)($r['adults'] ?? 0);
            $tot['children'] += (int)($r['children'] ?? 0);
            $tot['kids_0_3'] += (int)($r['kids_0_3'] ?? 0);
        }
        foreach ($grand as $k => $v) $grand[$k] += $tot[$k];
        $out[$key] = ['meta'=>$meta, 'rows'=>$rows, 'totals'=>$tot];
    }

    return ['sections'=>$out, 'grand'=>$grand];
}

function fo_define_rooming_list_pdf_class(): void {
    if (class_exists('FO_Rooming_List_PDF', false)) return;
    fo_pdf_load_fpdf();

class FO_Rooming_List_PDF extends FPDF {
    public string $propertyLabel = '';
    public string $reportDateLabel = '';
    public string $logoPath = '';
    public array $grandTotals = [];
    public array $sectionTotals = [];
    public string $titleText = 'Daily Reservations / Rooming List';
    public array $widths = [];
    public array $aligns = [];

    function Header() {
        $this->SetFillColor(15,23,42);
        $this->Rect(8, 8, 281, 24, 'F');
        $this->SetFillColor(255,255,255);
        $this->Rect(11, 11, 27, 18, 'F');

        if ($this->logoPath !== '') {
            $ext = strtolower(pathinfo($this->logoPath, PATHINFO_EXTENSION));
            $type = ($ext === 'jpg' || $ext === 'jpeg') ? 'JPG' : 'PNG';
            try { $this->Image($this->logoPath, 13, 12, 23, 0, $type); } catch (Throwable $e) {}
        } else {
            $this->SetFont('Arial','B',14);
            $this->SetTextColor(30,64,175);
            $this->SetXY(11,16);
            $this->Cell(27,7,'PGL',0,0,'C');
        }

        $this->SetTextColor(255,255,255);
        $this->SetFont('Arial','B',17);
        $this->SetXY(43, 12);
        $this->Cell(150, 8, fo_pdf_clean_text($this->titleText), 0, 2, 'L');
        $this->SetFont('Arial','',8);
        $this->SetTextColor(219,234,254);
        $this->Cell(150, 5, fo_pdf_clean_text($this->propertyLabel . ' - ' . $this->reportDateLabel), 0, 0, 'L');

        $this->SetXY(205, 12);
        $this->SetFont('Arial','',7.5);
        $this->SetTextColor(191,219,254);
        $this->Cell(80, 5, 'Generated: ' . date('d/m/Y H:i'), 0, 2, 'R');
        $this->Cell(80, 5, 'Front Office Daily Pack', 0, 2, 'R');
        $this->Cell(80, 5, 'Page ' . $this->PageNo(), 0, 2, 'R');

        $this->SetY(36);
        $this->DrawSummaryCards();
        $this->Ln(3);
    }

    function Footer() {
        $this->SetY(-10);
        $this->SetDrawColor(226,232,240);
        $this->Line(8, $this->GetY(), 289, $this->GetY());
        $this->SetTextColor(100,116,139);
        $this->SetFont('Arial','',7);
        $this->Cell(0,5, fo_pdf_clean_text('Daily Reservations / Rooming List - Printed from Front Office Daily Reports'), 0, 0, 'C');
    }

    function DrawSummaryCards() {
        $cards = [
            ['Bookings', (int)($this->grandTotals['bookings'] ?? 0)],
            ['Rooms / Slots', (int)($this->grandTotals['rooms'] ?? 0)],
            ['Adults', (int)($this->grandTotals['adults'] ?? 0)],
            ['Children', (int)($this->grandTotals['children'] ?? 0)],
        ];
        $x = 8; $w = 67.75;
        foreach ($cards as $i => $card) {
            $this->SetFillColor(248,250,252);
            $this->SetDrawColor(226,232,240);
            $this->RoundedRect($x + ($i * ($w + 3)), $this->GetY(), $w, 15, 2.2, 'DF');
            $this->SetXY($x + ($i * ($w + 3)) + 4, $this->GetY()+2.3);
            $this->SetTextColor(100,116,139);
            $this->SetFont('Arial','B',6.5);
            $this->Cell($w-8, 4, strtoupper(fo_pdf_clean_text($card[0])), 0, 2, 'L');
            $this->SetTextColor(15,23,42);
            $this->SetFont('Arial','B',12);
            $this->Cell($w-8, 6, (string)$card[1], 0, 0, 'L');
        }
        $this->SetY($this->GetY()+16.5);
    }

    function RoundedRect($x, $y, $w, $h, $r, $style = '') {
        $k = $this->k; $hp = $this->h;
        if($style=='F') $op='f'; elseif($style=='FD' || $style=='DF') $op='B'; else $op='S';
        $MyArc = 4/3 * (sqrt(2) - 1);
        $this->_out(sprintf('%.2F %.2F m', ($x+$r)*$k, ($hp-$y)*$k));
        $xc=$x+$w-$r; $yc=$y+$r;
        $this->_out(sprintf('%.2F %.2F l', $xc*$k, ($hp-$y)*$k));
        $this->_Arc($xc+$r*$MyArc, $yc-$r, $xc+$r, $yc-$r*$MyArc, $xc+$r, $yc);
        $xc=$x+$w-$r; $yc=$y+$h-$r;
        $this->_out(sprintf('%.2F %.2F l', ($x+$w)*$k, ($hp-$yc)*$k));
        $this->_Arc($xc+$r, $yc+$r*$MyArc, $xc+$r*$MyArc, $yc+$r, $xc, $yc+$r);
        $xc=$x+$r; $yc=$y+$h-$r;
        $this->_out(sprintf('%.2F %.2F l', $xc*$k, ($hp-($y+$h))*$k));
        $this->_Arc($xc-$r*$MyArc, $yc+$r, $xc-$r, $yc+$r*$MyArc, $xc-$r, $yc);
        $xc=$x+$r; $yc=$y+$r;
        $this->_out(sprintf('%.2F %.2F l', $x*$k, ($hp-$yc)*$k));
        $this->_Arc($xc-$r, $yc-$r*$MyArc, $xc-$r*$MyArc, $yc-$r, $xc, $yc-$r);
        $this->_out($op);
    }

    function _Arc($x1, $y1, $x2, $y2, $x3, $y3) {
        $h = $this->h;
        $this->_out(sprintf('%.2F %.2F %.2F %.2F %.2F %.2F c ',
            $x1*$this->k, ($h-$y1)*$this->k,
            $x2*$this->k, ($h-$y2)*$this->k,
            $x3*$this->k, ($h-$y3)*$this->k));
    }

    function SectionHeader(string $label, array $meta, array $totals) {
        if ($this->GetY() > 172) $this->AddPage();
        $color = $meta['color'] ?? [226,232,240];
        $text  = $meta['text'] ?? [15,23,42];
        $this->SetFillColor($color[0], $color[1], $color[2]);
        $this->SetDrawColor($color[0], $color[1], $color[2]);
        $this->RoundedRect(8, $this->GetY(), 281, 9, 2, 'DF');
        $this->SetXY(11, $this->GetY()+1.8);
        $this->SetTextColor($text[0], $text[1], $text[2]);
        $this->SetFont('Arial','B',8);
        $summary = sprintf('%s  |  Bookings: %d  Rooms: %d  Adults: %d  Children: %d',
            strtoupper($label), (int)$totals['bookings'], (int)$totals['rooms'], (int)$totals['adults'], (int)$totals['children']);
        $this->Cell(275,5.5, fo_pdf_clean_text($summary), 0, 1, 'L');
        $this->Ln(1.2);
        $this->TableHeader();
    }

    function TableHeader() {
        $headers = ['Room / Type','Guest','Meal','Arrival','Depart','Nts','Ad','Ch','Reason','Config','Dietary','Status'];
        $this->SetFillColor(15,23,42);
        $this->SetTextColor(255,255,255);
        $this->SetDrawColor(15,23,42);
        $this->SetLineWidth(.1);
        $this->SetFont('Arial','B',5.8);
        foreach ($headers as $i => $h) {
            $this->Cell($this->widths[$i], 6, fo_pdf_clean_text($h), 1, 0, 'C', true);
        }
        $this->Ln();
    }

    function NbLines($w, $txt) {
        $cw = &$this->CurrentFont['cw'];
        if($w==0) $w = $this->w-$this->rMargin-$this->x;
        $wmax = ($w-2*$this->cMargin)*1000/$this->FontSize;
        $s = str_replace("\r", '', (string)$txt);
        $nb = strlen($s);
        if($nb>0 && $s[$nb-1]=="\n") $nb--;
        $sep = -1; $i = 0; $j = 0; $l = 0; $nl = 1;
        while($i < $nb) {
            $c = $s[$i];
            if($c == "\n") { $i++; $sep = -1; $j = $i; $l = 0; $nl++; continue; }
            if($c == ' ') $sep = $i;
            $l += $cw[$c] ?? 0;
            if($l > $wmax) {
                if($sep == -1) { if($i == $j) $i++; }
                else $i = $sep + 1;
                $sep = -1; $j = $i; $l = 0; $nl++;
            } else $i++;
        }
        return $nl;
    }

    function CheckPageBreak($h) {
        if($this->GetY() + $h > $this->PageBreakTrigger) {
            $this->AddPage($this->CurOrientation);
            $this->TableHeader();
        }
    }

    function Row(array $data, array $fills = []) {
        $this->SetFont('Arial','',6.2);
        $nb = 1;
        foreach ($data as $i => $txt) {
            $nb = max($nb, $this->NbLines($this->widths[$i], fo_pdf_clean_text($txt)));
        }
        $h = max(7, min(22, 3.7 * $nb));
        $this->CheckPageBreak($h);
        $x0 = $this->GetX(); $y0 = $this->GetY();
        foreach ($data as $i => $txt) {
            $w = $this->widths[$i];
            $a = $this->aligns[$i] ?? 'L';
            [$r,$g,$b] = $fills[$i] ?? [255,255,255];
            $this->SetFillColor($r,$g,$b);
            $this->SetDrawColor(226,232,240);
            $this->Rect($x0, $y0, $w, $h, 'DF');
            $this->SetXY($x0+1, $y0+1.1);
            $this->SetTextColor(15,23,42);
            $this->MultiCell($w-2, 3.5, fo_pdf_clean_text($txt), 0, $a);
            $x0 += $w;
            $this->SetXY($x0, $y0);
        }
        $this->SetY($y0 + $h);
    }
}
}


function fo_output_rooming_list_pdf(PDO $pdo, int $propertyId, string $date, string $propertyLabel): void {
    fo_define_rooming_list_pdf_class();
    $packet = fo_rooming_pdf_collect_data($pdo, $propertyId, $date);
    $filename = 'daily_rooming_list_' . date('Ymd', strtotime($date)) . '.pdf';

    while (ob_get_level() > 0) { @ob_end_clean(); }

    $pdf = new FO_Rooming_List_PDF('L', 'mm', 'A4');
    $pdf->SetTitle('Daily Reservations / Rooming List');
    $pdf->SetAuthor(fo_pdf_clean_text($propertyLabel));
    $pdf->SetAutoPageBreak(true, 10);
    $pdf->SetMargins(8, 8, 8);
    $pdf->propertyLabel = $propertyLabel;
    $pdf->reportDateLabel = fo_display_date($date, 'l, d F Y');
    $pdf->logoPath = fo_pdf_logo_path();
    $pdf->grandTotals = $packet['grand'];
    $pdf->widths = [24, 40, 10, 17, 17, 8, 8, 8, 34, 50, 35, 25];
    $pdf->aligns = ['L','L','C','C','C','C','C','C','L','L','L','C'];
    $pdf->AddPage();

    foreach ($packet['sections'] as $key => $section) {
        $label = (string)$section['meta']['label'];
        $pdf->SectionHeader($label, $section['meta'], $section['totals']);
        $rows = $section['rows'];
        if (!$rows) {
            $pdf->SetFont('Arial','I',7);
            $pdf->SetFillColor(248,250,252);
            $pdf->SetTextColor(148,163,184);
            $pdf->Cell(array_sum($pdf->widths), 7, 'No ' . fo_pdf_clean_text($label) . ' records for this date.', 1, 1, 'C', true);
            $pdf->Ln(2);
            continue;
        }

        $stripe = false;
        foreach ($rows as $r) {
            $roomNo = trim((string)($r['room_no'] ?? ''));
            if ($roomNo === '') $roomNo = 'Unallocated';
            $roomType = trim((string)($r['room_type'] ?? ''));
            $mealPlan = strtoupper(trim((string)($r['meal_plan'] ?? '')));
            if (!in_array($mealPlan, ['HB','BB'], true)) $mealPlan = '';
            $statusLabel = fo_status_label($r['status_raw'] ?? '');
            $configText = trim((string)($r['config'] ?? ''));
            if ($configText === '') $configText = $roomType;
            $guest = trim((string)($r['room_guests'] ?? ''));
            if ($guest === '') {
                $bookingName = trim((string)($r['booking_guest_name'] ?? ''));
                $guest = ($bookingName !== '' ? $bookingName : ('Booking #' . (string)($r['order_id'] ?? ''))) . ' (not yet captured)';
            }

            $fill = $stripe ? [248,250,252] : [255,255,255];
            $mealFill = $mealPlan !== '' ? [254,243,199] : $fill;
            $rowFills = [$fill,$fill,$mealFill,$fill,$fill,$fill,$fill,$fill,$fill,$fill,$fill,$fill];
            $pdf->Row([
                $roomNo . ($roomType !== '' ? "\n" . $roomType : ''),
                $guest,
                $mealPlan,
                fo_display_date($r['check_in'] ?? ''),
                fo_display_date($r['check_out'] ?? ''),
                (string)(int)($r['nights'] ?? 0),
                (string)(int)($r['adults'] ?? 0),
                (string)(int)($r['children'] ?? 0),
                (string)($r['reason'] ?? ''),
                $configText,
                (string)($r['dietary'] ?? ''),
                $statusLabel,
            ], $rowFills);
            $stripe = !$stripe;
        }
        $pdf->Ln(2.5);
    }

    $pdf->Output('I', $filename);
    exit;
}

function fo_render_report(PDO $pdo, string $report, int $propertyId, string $date): void {
    if ($report === 'pax_list') {
        fo_render_pax_list($pdo, $propertyId, $date);
    } elseif ($report === 'inhouse_activity') {
        fo_render_inhouse_activity($pdo, $propertyId, $date);
    } elseif ($report === 'inhouse_by_room') {
        fo_render_inhouse_by_room($pdo, $propertyId, $date);
    } else {
        fo_render_rooming_list($pdo, $propertyId, $date);
    }
}

// ---------------------------------------------------------------------------
// Page bootstrap
// ---------------------------------------------------------------------------
$properties = fo_fetch_properties($pdo);
$propertyId = (int)($_GET['property_id'] ?? 0);
if ($propertyId <= 0 && count($properties) === 1) {
    $propertyId = (int)$properties[0]['id'];
}
$date           = fo_date_sql_value(trim((string)($_GET['date'] ?? date('Y-m-d'))));
$report         = trim((string)($_GET['report'] ?? 'dashboard'));
$allowedReports = ['dashboard','rooming_list','pax_list','inhouse_activity','inhouse_by_room','bulk_guest_registration','preload_guest_names'];
if (!in_array($report, $allowedReports, true)) $report = 'dashboard';

$propertyLabel = fo_property_label($pdo, $propertyId);

// ---------------------------------------------------------------------------
// Bulk Guest Registration shortcut
// ---------------------------------------------------------------------------
if ($report === 'bulk_guest_registration') {
    $bulkDate = date('Y-m-d', strtotime('+1 day'));
    header('Location: bulk_guest_registration_tomorrow.php?' . http_build_query([
        'property_id' => $propertyId,
        'date' => $bulkDate,
    ]));
    exit;
}

if ($report === 'preload_guest_names') {
    $preloadDate = date('Y-m-d', strtotime('+1 day'));
    header('Location: preload_tomorrow_guest_names.php?' . http_build_query([
        'property_id' => $propertyId,
        'date' => $preloadDate,
    ]));
    exit;
}

// ---------------------------------------------------------------------------
// Excel export
// ---------------------------------------------------------------------------
if (($_GET['export'] ?? '') === 'xls') {
    $exportReport = $report === 'dashboard' ? 'rooming_list' : $report;
    fo_output_xls_start(strtolower($exportReport) . '_' . date('Ymd', strtotime($date)) . '.xls');
    echo '<html><head><meta charset="utf-8"><style>table{border-collapse:collapse}td,th{border:1px solid #999;padding:5px}th{background:#e5e7eb}.title{font-size:18px;font-weight:bold}.sub{color:#555}.section-title{background:#0f172a;color:#fff;padding:6px}</style></head><body>';
    echo '<div class="title">' . h($propertyLabel) . '</div>';
    echo '<div class="title">' . h(fo_report_title($exportReport)) . '</div>';
    echo '<div class="sub">Report Date: ' . h(fo_display_date($date)) . ' | Generated: ' . h(date('d/m/Y H:i:s')) . '</div><br>';
    if ($exportReport === 'pax_list') {
        fo_render_pax_list_xls($pdo, $propertyId, $date);
    } else {
        fo_render_report($pdo, $exportReport, $propertyId, $date);
    }
    echo '</body></html>';
    exit;
}

if (($_GET['export'] ?? '') === 'pdf') {
    $exportReport = $report === 'dashboard' ? 'rooming_list' : $report;
    if ($exportReport === 'rooming_list') {
        fo_output_rooming_list_pdf($pdo, $propertyId, $date, $propertyLabel);
    }
}

require_once __DIR__ . '/../../includes/header.php';

$reportCards = [
    'rooming_list'   => ['Daily Reservations / Rooming List', 'Stay overs, arrivals, departures, in-house rooms and meal plan.', 'bi-door-open'],
    'pax_list'       => ['Pax List', 'Rooms, adults, children and in-house totals by movement.', 'bi-people'],
    'inhouse_activity' => ['In-House Activity Sheet', 'Front office activity sheet with Morning, Afternoon and Dinner sections.', 'bi-calendar-check'],
    'inhouse_by_room'  => ['In-House by Room', 'Room-by-room in-house list with three daily service columns.', 'bi-layout-three-columns'],
    'bulk_guest_registration' => ['Bulk Print Tomorrow Guest Registration Forms', 'Generate one PDF for tomorrow check-in guest registration forms.', 'bi-printer'],
    'preload_guest_names' => ['Preload Tomorrow Guest Names', 'Enter Guest 1-4 names before arrival so bulk forms print complete.', 'bi-people-fill'],
];

function fo_url_filter_value($v): bool { return $v !== null && $v !== ''; }
function fo_url(array $params): string {
    return 'front_office_daily_reports.php?' . http_build_query(array_filter($params, 'fo_url_filter_value'));
}
?>
<div class="fo-report-hero">
    <div class="fo-hero-left">
        <div class="fo-eyebrow"><i class="bi bi-clipboard-data me-1"></i>Front Office Daily Pack</div>
        <h2 class="fo-hero-title">Daily Front Office Reports</h2>
        <div class="fo-hero-sub">Rooming List &bull; Pax List &bull; Activity Sheet &bull; In-House by Room</div>
    </div>
    <div class="fo-hero-actions">
        <a class="btn fo-btn-ghost" href="../calendar/calendar.php?property_id=<?php echo (int)$propertyId; ?>">
            <i class="bi bi-arrow-left me-1"></i>Calendar
        </a>
        <a class="btn fo-btn-print" href="<?php echo h('bulk_guest_registration_tomorrow.php?' . http_build_query(['property_id'=>$propertyId,'date'=>date('Y-m-d', strtotime('+1 day'))])); ?>">
            <i class="bi bi-printer me-1"></i>Bulk Guest Reg
        </a>
        <a class="btn fo-btn-excel" href="<?php echo h('preload_tomorrow_guest_names.php?' . http_build_query(['property_id'=>$propertyId,'date'=>date('Y-m-d', strtotime('+1 day'))])); ?>">
            <i class="bi bi-people-fill me-1"></i>Preload Names
        </a>
        <?php if ($report !== 'dashboard'): ?>
            <a class="btn fo-btn-excel" href="<?php echo h(fo_url(['property_id'=>$propertyId,'date'=>$date,'report'=>$report,'export'=>'xls'])); ?>">
                <i class="bi bi-file-earmark-excel me-1"></i>Export Excel
            </a>
            <?php if ($report === 'rooming_list'): ?>
                <a class="btn fo-btn-print" target="_blank" href="<?php echo h(fo_url(['property_id'=>$propertyId,'date'=>$date,'report'=>'rooming_list','export'=>'pdf'])); ?>">
                    <i class="bi bi-file-earmark-pdf me-1"></i>FPDF PDF
                </a>
            <?php endif; ?>
            <button class="btn fo-btn-print" onclick="window.print()">
                <i class="bi bi-printer me-1"></i>Print
            </button>
        <?php endif; ?>
    </div>
</div>

<div class="fo-filter-bar">
    <form method="get" class="fo-filter-form">
        <div class="fo-filter-group">
            <label class="fo-filter-label"><i class="bi bi-building me-1"></i>Property</label>
            <select name="property_id" class="fo-select">
                <option value="0">All Properties</option>
                <?php foreach ($properties as $p): ?>
                    <option value="<?php echo (int)$p['id']; ?>" <?php echo $propertyId === (int)$p['id'] ? 'selected' : ''; ?>>
                        <?php echo h($p['name'] . (!empty($p['code']) ? ' (' . $p['code'] . ')' : '')); ?>
                    </option>
                <?php endforeach; ?>
            </select>
        </div>
        <div class="fo-filter-group">
            <label class="fo-filter-label"><i class="bi bi-calendar3 me-1"></i>Report Date</label>
            <input type="date" name="date" class="fo-input" value="<?php echo h($date); ?>">
        </div>
        <div class="fo-filter-group fo-filter-grow">
            <label class="fo-filter-label"><i class="bi bi-file-text me-1"></i>Report</label>
            <select name="report" class="fo-select">
                <option value="dashboard" <?php echo $report === 'dashboard' ? 'selected' : ''; ?>>Report Centre</option>
                <?php foreach ($reportCards as $key => $card): ?>
                    <option value="<?php echo h($key); ?>" <?php echo $report === $key ? 'selected' : ''; ?>><?php echo h($card[0]); ?></option>
                <?php endforeach; ?>
            </select>
        </div>
        <button type="submit" class="btn fo-btn-primary">
            <i class="bi bi-search me-1"></i>Pull Report
        </button>
    </form>
</div>

<?php if ($report === 'dashboard'): ?>
<div class="fo-dashboard-grid">
    <?php
    $tileColors = [
        'rooming_list'     => ['#1d4ed8','#dbeafe','#eff6ff'],
        'pax_list'         => ['#0f766e','#ccfbf1','#f0fdfa'],
        'inhouse_activity' => ['#7c3aed','#ede9fe','#f5f3ff'],
        'inhouse_by_room'  => ['#c2410c','#fed7aa','#fff7ed'],
        'bulk_guest_registration' => ['#be185d','#fbcfe8','#fdf2f8'],
        'preload_guest_names' => ['#0891b2','#cffafe','#ecfeff'],
    ];
    foreach ($reportCards as $key => $card):
        [$accent, $chipBg, $tileBg] = $tileColors[$key] ?? ['#334155','#e2e8f0','#f8fafc'];
    ?>
    <div class="fo-report-tile" style="--tile-accent:<?php echo $accent; ?>;--tile-chip:<?php echo $chipBg; ?>;--tile-bg:<?php echo $tileBg; ?>">
        <div class="fo-tile-icon"><i class="bi <?php echo h($card[2]); ?>"></i></div>
        <div class="fo-tile-body">
            <div class="fo-tile-title"><?php echo h($card[0]); ?></div>
            <div class="fo-tile-desc"><?php echo h($card[1]); ?></div>
        </div>
        <div class="fo-tile-actions">
            <?php if ($key === 'bulk_guest_registration'): ?>
                <a class="fo-tile-btn fo-tile-open" href="<?php echo h('bulk_guest_registration_tomorrow.php?' . http_build_query(['property_id'=>$propertyId,'date'=>date('Y-m-d', strtotime('+1 day'))])); ?>">
                    <i class="bi bi-printer me-1"></i>Open
                </a>
                <a class="fo-tile-btn fo-tile-excel" href="<?php echo h('bulk_guest_registration_tomorrow.php?' . http_build_query(['property_id'=>$propertyId,'date'=>date('Y-m-d', strtotime('+1 day')),'combined_pdf'=>1])); ?>" target="_blank">
                    <i class="bi bi-file-earmark-pdf me-1"></i>One PDF
                </a>
            <?php elseif ($key === 'preload_guest_names'): ?>
                <a class="fo-tile-btn fo-tile-open" href="<?php echo h('preload_tomorrow_guest_names.php?' . http_build_query(['property_id'=>$propertyId,'date'=>date('Y-m-d', strtotime('+1 day'))])); ?>">
                    <i class="bi bi-people-fill me-1"></i>Open
                </a>
                <a class="fo-tile-btn fo-tile-excel" href="<?php echo h('bulk_guest_registration_tomorrow.php?' . http_build_query(['property_id'=>$propertyId,'date'=>date('Y-m-d', strtotime('+1 day'))])); ?>">
                    <i class="bi bi-printer me-1"></i>Bulk Forms
                </a>
            <?php else: ?>
                <a class="fo-tile-btn fo-tile-open" href="<?php echo h(fo_url(['property_id'=>$propertyId,'date'=>$date,'report'=>$key])); ?>">
                    <i class="bi bi-eye me-1"></i>Open
                </a>
                <a class="fo-tile-btn fo-tile-excel" href="<?php echo h(fo_url(['property_id'=>$propertyId,'date'=>$date,'report'=>$key,'export'=>'xls'])); ?>">
                    <i class="bi bi-file-earmark-excel me-1"></i>Excel
                </a>
                <?php if ($key === 'rooming_list'): ?>
                    <a class="fo-tile-btn fo-tile-open" target="_blank" href="<?php echo h(fo_url(['property_id'=>$propertyId,'date'=>$date,'report'=>'rooming_list','export'=>'pdf'])); ?>">
                        <i class="bi bi-file-earmark-pdf me-1"></i>PDF
                    </a>
                <?php endif; ?>
            <?php endif; ?>
        </div>
    </div>
    <?php endforeach; ?>
</div>

<?php else: ?>
<div class="fo-report-paper">
    <div class="fo-report-masthead">
        <div class="fo-masthead-logo"><i class="bi bi-building"></i></div>
        <div>
            <div class="fo-masthead-property"><?php echo h($propertyLabel); ?></div>
            <div class="fo-masthead-title"><?php echo h(fo_report_title($report)); ?></div>
            <div class="fo-masthead-meta">
                <span><i class="bi bi-calendar3 me-1"></i>Report Date: <strong><?php echo h(fo_display_date($date)); ?></strong></span>
                <span class="fo-meta-sep">&bull;</span>
                <span><i class="bi bi-clock me-1"></i>Generated: <?php echo h(date('d/m/Y H:i:s')); ?></span>
            </div>
        </div>
    </div>
    <?php fo_render_report($pdo, $report, $propertyId, $date); ?>
</div>
<?php endif; ?>

<style>
/* =====================================================================
   FRONT OFFICE DAILY REPORTS — FULL DESIGN SYSTEM
   ===================================================================== */

/* --- Hero band -------------------------------------------------------- */
.fo-report-hero{
    display:flex;justify-content:space-between;align-items:center;gap:16px;
    background:linear-gradient(135deg,#0f172a 0%,#1e3a8a 100%);
    border-radius:20px;padding:18px 22px;margin-bottom:14px;
    box-shadow:0 16px 40px rgba(15,23,42,.18);
}
.fo-eyebrow{font-size:10px;text-transform:uppercase;letter-spacing:.14em;font-weight:900;color:#93c5fd;margin-bottom:4px;}
.fo-hero-title{font-size:1.45rem;font-weight:950;color:#fff;margin:0 0 2px;}
.fo-hero-sub{font-size:.8rem;color:#bfdbfe;font-weight:600;}
.fo-hero-actions{display:flex;gap:8px;flex-wrap:wrap;}
.fo-btn-ghost{background:rgba(255,255,255,.10);color:#fff;border:1px solid rgba(255,255,255,.22);border-radius:12px;font-size:.8rem;padding:.38rem .85rem;font-weight:700;}
.fo-btn-ghost:hover{background:rgba(255,255,255,.20);color:#fff;}
.fo-btn-excel{background:#16a34a;color:#fff;border:0;border-radius:12px;font-size:.8rem;padding:.38rem .85rem;font-weight:700;}
.fo-btn-excel:hover{background:#15803d;color:#fff;}
.fo-btn-print{background:rgba(255,255,255,.15);color:#fff;border:1px solid rgba(255,255,255,.3);border-radius:12px;font-size:.8rem;padding:.38rem .85rem;font-weight:700;}
.fo-btn-print:hover{background:rgba(255,255,255,.25);color:#fff;}

/* --- Filter bar ------------------------------------------------------- */
.fo-filter-bar{
    background:#fff;border:1px solid #e2e8f0;border-radius:18px;
    padding:14px 18px;margin-bottom:16px;
    box-shadow:0 4px 16px rgba(15,23,42,.05);
}
.fo-filter-form{display:flex;gap:12px;align-items:flex-end;flex-wrap:wrap;}
.fo-filter-group{display:flex;flex-direction:column;gap:4px;min-width:140px;}
.fo-filter-grow{flex:1;min-width:180px;}
.fo-filter-label{font-size:.72rem;font-weight:900;text-transform:uppercase;letter-spacing:.06em;color:#64748b;}
.fo-select,.fo-input{
    border:1px solid #e2e8f0;border-radius:10px;
    padding:.38rem .75rem;font-size:.84rem;color:#0f172a;
    background:#f8fafc;min-height:36px;
    transition:border-color .14s;
}
.fo-select:focus,.fo-input:focus{border-color:#2563eb;outline:0;background:#fff;}
.fo-btn-primary{
    background:linear-gradient(135deg,#2563eb,#1d4ed8);
    color:#fff;border:0;border-radius:12px;
    font-size:.84rem;font-weight:800;padding:.42rem 1.1rem;
    white-space:nowrap;min-height:36px;
}
.fo-btn-primary:hover{background:linear-gradient(135deg,#1d4ed8,#1e40af);color:#fff;}

/* --- Dashboard tiles -------------------------------------------------- */
.fo-dashboard-grid{
    display:grid;
    grid-template-columns:repeat(auto-fill,minmax(260px,1fr));
    gap:16px;
}
.fo-report-tile{
    background:var(--tile-bg,#f8fafc);
    border:1.5px solid var(--tile-chip,#e2e8f0);
    border-radius:20px;padding:20px;
    display:flex;flex-direction:column;gap:12px;
    transition:transform .14s,box-shadow .14s;
    box-shadow:0 4px 16px rgba(15,23,42,.05);
}
.fo-report-tile:hover{transform:translateY(-2px);box-shadow:0 12px 32px rgba(15,23,42,.12);}
.fo-tile-icon{
    width:48px;height:48px;border-radius:16px;
    background:var(--tile-chip,#e2e8f0);
    color:var(--tile-accent,#334155);
    display:flex;align-items:center;justify-content:center;
    font-size:22px;
}
.fo-tile-title{font-size:.95rem;font-weight:900;color:#0f172a;margin-bottom:3px;}
.fo-tile-desc{font-size:.78rem;color:#64748b;line-height:1.4;}
.fo-tile-actions{display:flex;gap:8px;margin-top:auto;}
.fo-tile-btn{
    flex:1;text-align:center;font-size:.78rem;font-weight:800;
    border-radius:10px;padding:.38rem .5rem;text-decoration:none;
    border:1.5px solid transparent;transition:.12s;
}
.fo-tile-open{
    background:var(--tile-accent,#334155);color:#fff;
}
.fo-tile-open:hover{opacity:.88;color:#fff;}
.fo-tile-excel{
    background:transparent;
    color:var(--tile-accent,#334155);
    border-color:var(--tile-accent,#334155);
}
.fo-tile-excel:hover{background:var(--tile-chip,#e2e8f0);}

/* --- Report paper ----------------------------------------------------- */
.fo-report-paper{
    background:#fff;border:1px solid #e2e8f0;border-radius:20px;
    box-shadow:0 8px 32px rgba(15,23,42,.07);overflow:hidden;
}
.fo-report-masthead{
    display:flex;align-items:flex-start;gap:16px;
    background:linear-gradient(135deg,#0f172a,#1e3a8a);
    color:#fff;padding:20px 24px 18px;
}
.fo-masthead-logo{
    width:52px;height:52px;border-radius:16px;
    background:rgba(255,255,255,.12);
    display:flex;align-items:center;justify-content:center;
    font-size:26px;flex-shrink:0;
}
.fo-masthead-property{font-size:.8rem;font-weight:900;text-transform:uppercase;letter-spacing:.1em;color:#93c5fd;margin-bottom:2px;}
.fo-masthead-title{font-size:1.3rem;font-weight:950;color:#fff;margin-bottom:6px;}
.fo-masthead-meta{display:flex;flex-wrap:wrap;gap:8px;font-size:.78rem;color:#bfdbfe;font-weight:600;}
.fo-meta-sep{opacity:.5;}

/* --- Section titles --------------------------------------------------- */
.report-section{padding:0 20px 6px;}
.section-title{
    display:flex;align-items:center;gap:10px;
    font-size:.78rem;font-weight:900;text-transform:uppercase;letter-spacing:.1em;
    padding:10px 14px;margin:18px 0 0;border-radius:12px;
}
.section-title.st-stayovers{ background:#dbeafe;color:#1e40af; }
.section-title.st-arrivals{  background:#dcfce7;color:#14532d; }
.section-title.st-departures{background:#fee2e2;color:#7f1d1d; }
.section-title.st-inhouse{   background:#ede9fe;color:#4c1d95; }
.section-title.st-morning{   background:#fef9c3;color:#713f12; }
.section-title.st-afternoon{ background:#ffedd5;color:#7c2d12; }
.section-title.st-dinner{    background:#fce7f3;color:#831843; }
.section-title i{font-size:1rem;}

/* --- Tables ----------------------------------------------------------- */
.fo-table{
    width:100%;border-collapse:collapse;font-size:12px;
    margin-bottom:0;
}
.fo-table thead tr{
    background:linear-gradient(135deg,#0f172a,#1e3a8a);
}
.fo-table thead th{
    color:#fff;font-size:.68rem;font-weight:900;
    text-transform:uppercase;letter-spacing:.07em;
    padding:9px 10px;white-space:nowrap;border:0;
}
.fo-table thead th:first-child{border-radius:0;}
.fo-table tbody tr{border-bottom:1px solid #f1f5f9;}
.fo-table tbody tr:hover{background:#f8fafc;}
.fo-table tbody td{padding:8px 10px;color:#0f172a;vertical-align:middle;border:0;}
.fo-table tbody tr:last-child{border-bottom:0;}
.fo-pax-matrix tbody tr:nth-child(even){background:#f8fafc;}
.fo-pax-matrix tbody tr.fo-pax-base{background:#fff7ed;}
.fo-pax-matrix tbody tr.fo-pax-base td{border-top:2px solid #fdba74;border-bottom:2px solid #fdba74;}
.fo-pax-matrix tbody tr.fo-pax-base td:first-child{color:#9a3412;}
.fo-pax-matrix tbody tr:hover{background:#eef2ff;}
.fo-table .fo-badge{
    display:inline-block;padding:2px 8px;border-radius:999px;
    font-size:.65rem;font-weight:900;letter-spacing:.04em;
}
.fo-badge-confirmed{background:#dbeafe;color:#1e40af;}
.fo-badge-inhouse{background:#dcfce7;color:#14532d;}
.fo-badge-checkout{background:#fee2e2;color:#7f1d1d;}
.fo-badge-provisional{background:#fef9c3;color:#713f12;}
.fo-table .fo-room-no{
    font-weight:950;font-size:1rem;color:#1e3a8a;
    font-variant-numeric:tabular-nums;
}
.fo-table .fo-guest{font-weight:700;color:#0f172a;}
.fo-table .fo-nights{font-weight:900;color:#7c3aed;font-size:.9rem;}
.fo-table .fo-pax{font-weight:700;color:#0f172a;}
.fo-table .fo-empty{color:#94a3b8;font-style:italic;font-size:.75rem;}

/* Alternating row tint */
.fo-table tbody tr:nth-child(even){background:#fafbff;}
.fo-table tbody tr:nth-child(even):hover{background:#f0f4ff;}

/* --- Pax list specific ------------------------------------------------ */
.pax-summary-wrap{padding:20px;}
.pax-band{
    display:grid;
    grid-template-columns:repeat(auto-fill,minmax(180px,1fr));
    gap:12px;margin-bottom:20px;
}
.pax-card{
    border-radius:16px;padding:14px 16px;
    border:1.5px solid transparent;
}
.pax-card.pc-arrivals{  background:#dcfce7;border-color:#86efac;color:#14532d;}
.pax-card.pc-stayovers{ background:#dbeafe;border-color:#93c5fd;color:#1e40af;}
.pax-card.pc-departures{background:#fee2e2;border-color:#fca5a5;color:#7f1d1d;}
.pax-card.pc-inhouse{   background:#ede9fe;border-color:#c4b5fd;color:#4c1d95;}
.pax-card-label{font-size:.68rem;font-weight:900;text-transform:uppercase;letter-spacing:.1em;margin-bottom:8px;opacity:.75;}
.pax-card-stats{display:grid;grid-template-columns:repeat(3,1fr);gap:4px;}
.pax-stat{text-align:center;}
.pax-stat-val{font-size:1.4rem;font-weight:950;line-height:1;}
.pax-stat-key{font-size:.6rem;font-weight:900;text-transform:uppercase;opacity:.65;margin-top:2px;}
.pax-total{font-size:1.8rem;font-weight:950;margin-bottom:2px;}

/* --- Activity sheet --------------------------------------------------- */
.fo-meal-section{padding:0 20px 16px;}

/* --- In-house by room markers ---------------------------------------- */
.fo-ci-marker{
    display:inline-block;background:#dcfce7;color:#14532d;
    border-radius:6px;padding:1px 6px;font-size:.65rem;font-weight:900;
}
.fo-co-marker{
    display:inline-block;background:#fee2e2;color:#7f1d1d;
    border-radius:6px;padding:1px 6px;font-size:.65rem;font-weight:900;
}

/* --- Print styles ----------------------------------------------------- */
@media print{
    .fo-report-hero,.fo-filter-bar,.fo-hero-actions,.fo-btn-excel,.fo-btn-print,
    .sidebar,.navbar,nav,form,.btn,header{display:none!important;}
    .fo-report-paper{box-shadow:none!important;border:0!important;border-radius:0!important;}
    .fo-report-masthead{background:#0f172a!important;-webkit-print-color-adjust:exact;print-color-adjust:exact;}
    .section-title{-webkit-print-color-adjust:exact;print-color-adjust:exact;}
    .fo-table thead tr{background:#0f172a!important;-webkit-print-color-adjust:exact;print-color-adjust:exact;}
    .fo-table{font-size:10px!important;}
    .fo-table thead th,.fo-table tbody td{padding:5px 7px!important;}
    .report-section{padding:0 8px 4px!important;}
    .pax-band{gap:6px!important;}
    .pax-card{padding:8px!important;-webkit-print-color-adjust:exact;print-color-adjust:exact;}
    .fo-dashboard-grid{display:none!important;}
    body{font-size:10pt!important;}
}

@media(max-width:768px){
    .fo-report-hero{flex-direction:column;align-items:flex-start;}
    .fo-filter-form{flex-direction:column;}
    .fo-filter-group,.fo-filter-grow{min-width:100%;}
    .fo-btn-primary{width:100%;}
    .pax-band{grid-template-columns:1fr 1fr;}
}
</style>

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