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

$pageTitle = 'Restaurant Executive Report';

if (!function_exists('rex_h')) {
    function rex_h($value): string { return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8'); }
}
if (!function_exists('rex_money')) {
    function rex_money($amount): string { return number_format((float)$amount, 2); }
}
if (!function_exists('rex_num')) {
    function rex_num($amount, int $decimals = 0): string { return number_format((float)$amount, $decimals); }
}
if (!function_exists('rex_label')) {
    function rex_label($value): string {
        $value = trim((string)$value);
        if ($value === '') return 'Unspecified';
        return ucwords(str_replace(['_', '-'], ' ', $value));
    }
}
if (!function_exists('rex_pct')) {
    function rex_pct($part, $total, int $decimals = 1): string {
        $total = (float)$total;
        if ($total <= 0) return number_format(0, $decimals) . '%';
        return number_format(((float)$part / $total) * 100, $decimals) . '%';
    }
}
if (!function_exists('rex_pct_raw')) {
    function rex_pct_raw($part, $total): float {
        $total = (float)$total;
        return $total > 0 ? (((float)$part / $total) * 100) : 0.0;
    }
}
if (!function_exists('rex_date')) {
    function rex_date(string $value, string $fallback): string {
        $value = trim($value);
        if ($value === '') return $fallback;
        $ts = strtotime($value);
        return $ts ? date('Y-m-d', $ts) : $fallback;
    }
}
if (!function_exists('rex_table_exists')) {
    function rex_table_exists(PDO $pdo, string $table): bool {
        static $cache = [];
        if (array_key_exists($table, $cache)) return $cache[$table];
        try {
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = :table");
            $stmt->execute([':table' => $table]);
            return $cache[$table] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) {
            return $cache[$table] = false;
        }
    }
}
if (!function_exists('rex_column_exists')) {
    function rex_column_exists(PDO $pdo, string $table, string $column): bool {
        static $cache = [];
        $key = $table . '.' . $column;
        if (array_key_exists($key, $cache)) return $cache[$key];
        try {
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = :table AND column_name = :column");
            $stmt->execute([':table' => $table, ':column' => $column]);
            return $cache[$key] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) {
            return $cache[$key] = false;
        }
    }
}
if (!function_exists('rex_fetch_all')) {
    function rex_fetch_all(PDO $pdo, string $sql, array $params, array &$warnings, string $label): array {
        try {
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (Throwable $e) {
            $warnings[] = $label . ': ' . $e->getMessage();
            return [];
        }
    }
}
if (!function_exists('rex_fetch_one')) {
    function rex_fetch_one(PDO $pdo, string $sql, array $params, array &$warnings, string $label): array {
        $rows = rex_fetch_all($pdo, $sql, $params, $warnings, $label);
        return $rows[0] ?? [];
    }
}
if (!function_exists('rex_xls_cell')) {
    function rex_xls_cell($value): string { return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8'); }
}
if (!function_exists('rex_bar_width')) {
    function rex_bar_width($value, $max): string {
        $max = (float)$max;
        if ($max <= 0) return '0';
        return number_format(max(0, min(100, ((float)$value / $max) * 100)), 2, '.', '');
    }
}

$warnings = [];
$errors = [];
$today = date('Y-m-d');
$monthStart = date('Y-m-01');

/* Accept both old report parameter styles. */
$dateFrom = rex_date((string)($_GET['date_from'] ?? $_GET['from'] ?? ''), $monthStart);
$dateTo   = rex_date((string)($_GET['date_to'] ?? $_GET['to'] ?? ''), $today);
if ($dateFrom > $dateTo) { [$dateFrom, $dateTo] = [$dateTo, $dateFrom]; }

$selectedPropertyId = (int)($_GET['property_id'] ?? 0);
$selectedCategoryId = (int)($_GET['category_id'] ?? 0);
$statusFilter = trim((string)($_GET['status'] ?? 'closed'));
$orderTypeFilter = trim((string)($_GET['order_type'] ?? ''));
$closeMethodFilter = trim((string)($_GET['close_method'] ?? ''));
$mealFilter = trim((string)($_GET['meal_period'] ?? ''));
$includeOpen = (int)($_GET['include_open'] ?? 0);
$export = strtolower(trim((string)($_GET['export'] ?? '')));

$required = ['restaurant_orders', 'restaurant_order_items'];
foreach ($required as $table) {
    if (!rex_table_exists($pdo, $table)) $errors[] = 'Required table missing: ' . $table;
}

$hasProperties = rex_table_exists($pdo, 'properties');
$hasAreas = rex_table_exists($pdo, 'restaurant_areas');
$hasTables = rex_table_exists($pdo, 'restaurant_tables');
$hasPayments = rex_table_exists($pdo, 'restaurant_payments');
$hasSettlements = rex_table_exists($pdo, 'restaurant_split_settlements');
$hasMenuItems = rex_table_exists($pdo, 'restaurant_menu_items');
$hasCategories = rex_table_exists($pdo, 'restaurant_menu_categories');
$hasFolios = rex_table_exists($pdo, 'folios');
$hasPmsOrders = rex_table_exists($pdo, 'orders');
$hasGuests = rex_table_exists($pdo, 'guests');
$hasRecipeStock = rex_table_exists($pdo, 'stock_movements');

$ro = 'restaurant_orders';
$ri = 'restaurant_order_items';

$orderHasProperty = rex_column_exists($pdo, $ro, 'property_id');
$orderHasStatus = rex_column_exists($pdo, $ro, 'status');
$orderHasOrderType = rex_column_exists($pdo, $ro, 'order_type');
$orderHasCloseMethod = rex_column_exists($pdo, $ro, 'close_method');
$orderHasPaymentMethod = rex_column_exists($pdo, $ro, 'payment_method');
$orderHasMealPeriod = rex_column_exists($pdo, $ro, 'meal_period');
$orderHasCovers = rex_column_exists($pdo, $ro, 'covers');
$orderHasSurcharge = rex_column_exists($pdo, $ro, 'surcharge_amount');
$orderHasDiscount = rex_column_exists($pdo, $ro, 'discount_amount');
$orderHasCashier = rex_column_exists($pdo, $ro, 'cashier_name');
$orderHasOpenedBy = rex_column_exists($pdo, $ro, 'opened_by');
$orderHasClosedBy = rex_column_exists($pdo, $ro, 'closed_by');
$orderHasTable = rex_column_exists($pdo, $ro, 'table_id');
$orderHasLinkedOrder = rex_column_exists($pdo, $ro, 'linked_order_id');
$orderHasGuest = rex_column_exists($pdo, $ro, 'guest_name');
$orderHasRoom = rex_column_exists($pdo, $ro, 'room_no');
$orderHasGroup = rex_column_exists($pdo, $ro, 'group_name');
$orderHasTotal = rex_column_exists($pdo, $ro, 'total_amount');
$orderHasSubtotal = rex_column_exists($pdo, $ro, 'subtotal');
$orderHasTaxTotal = rex_column_exists($pdo, $ro, 'tax_total');
$orderHasCreatedAt = rex_column_exists($pdo, $ro, 'created_at');
$orderHasOpenedAt = rex_column_exists($pdo, $ro, 'opened_at');
$orderHasClosedAt = rex_column_exists($pdo, $ro, 'closed_at');
$orderHasNotes = rex_column_exists($pdo, $ro, 'notes');

$itemHasMenuItem = rex_column_exists($pdo, $ri, 'menu_item_id');
$itemHasCategory = rex_column_exists($pdo, $ri, 'category_id');
$itemHasInventory = rex_column_exists($pdo, $ri, 'inventory_item_id');
$itemHasQty = rex_column_exists($pdo, $ri, 'qty') || rex_column_exists($pdo, $ri, 'quantity');
$itemQtyCol = rex_column_exists($pdo, $ri, 'qty') ? 'qty' : (rex_column_exists($pdo, $ri, 'quantity') ? 'quantity' : '1');
$itemTotalCol = rex_column_exists($pdo, $ri, 'line_total') ? 'line_total' : (rex_column_exists($pdo, $ri, 'total') ? 'total' : '0');
$itemPriceCol = rex_column_exists($pdo, $ri, 'unit_price') ? 'unit_price' : (rex_column_exists($pdo, $ri, 'price') ? 'price' : '0');
$itemTaxCol = rex_column_exists($pdo, $ri, 'tax_amount') ? 'tax_amount' : '0';
$itemTaxRateCol = rex_column_exists($pdo, $ri, 'tax_rate') ? 'tax_rate' : '0';
$itemStatusExpr = rex_column_exists($pdo, $ri, 'status') ? "LOWER(COALESCE(ri.status,'active')) = 'active'" : '1=1';
$itemPrinterRoleExpr = rex_column_exists($pdo, $ri, 'printer_role') ? "COALESCE(ri.printer_role,'Not Set')" : "'Not Set'";
$itemNameExpr = rex_column_exists($pdo, $ri, 'item_name') ? 'ri.item_name' : "'Item'";

$menuHasCategory = $hasMenuItems && rex_column_exists($pdo, 'restaurant_menu_items', 'category_id');
$menuHasCode = $hasMenuItems && rex_column_exists($pdo, 'restaurant_menu_items', 'item_code');
$menuHasName = $hasMenuItems && rex_column_exists($pdo, 'restaurant_menu_items', 'item_name');
$menuHasFood = $hasMenuItems && rex_column_exists($pdo, 'restaurant_menu_items', 'is_food');
$menuHasBar = $hasMenuItems && rex_column_exists($pdo, 'restaurant_menu_items', 'is_bar');
$catNameCol = ($hasCategories && rex_column_exists($pdo, 'restaurant_menu_categories', 'category_name')) ? 'category_name' : (($hasCategories && rex_column_exists($pdo, 'restaurant_menu_categories', 'name')) ? 'name' : 'category_name');

$orderDateExpr = 'ro.id';
if ($orderHasClosedAt && $orderHasOpenedAt && $orderHasCreatedAt) $orderDateExpr = 'COALESCE(ro.closed_at, ro.opened_at, ro.created_at)';
elseif ($orderHasClosedAt && $orderHasOpenedAt) $orderDateExpr = 'COALESCE(ro.closed_at, ro.opened_at)';
elseif ($orderHasOpenedAt && $orderHasCreatedAt) $orderDateExpr = 'COALESCE(ro.opened_at, ro.created_at)';
elseif ($orderHasClosedAt) $orderDateExpr = 'ro.closed_at';
elseif ($orderHasOpenedAt) $orderDateExpr = 'ro.opened_at';
elseif ($orderHasCreatedAt) $orderDateExpr = 'ro.created_at';

$paymentDateExpr = 'rp.created_at';
if ($hasPayments && rex_column_exists($pdo, 'restaurant_payments', 'payment_date')) $paymentDateExpr = 'rp.payment_date';
elseif ($hasPayments && rex_column_exists($pdo, 'restaurant_payments', 'created_at')) $paymentDateExpr = 'rp.created_at';

$properties = [];
$categories = [];
$orderTypes = [];
if (!$errors) {
    if ($hasProperties) {
        $properties = rex_fetch_all($pdo, "SELECT id, name, code FROM properties WHERE COALESCE(is_active,1)=1 ORDER BY name ASC", [], $warnings, 'Properties');
    }
    if ($hasCategories) {
        $categories = rex_fetch_all($pdo, "SELECT id, {$catNameCol} AS category_name FROM restaurant_menu_categories WHERE COALESCE(is_active,1)=1 ORDER BY {$catNameCol} ASC", [], $warnings, 'Categories');
    }
    if ($orderHasOrderType) {
        $orderTypes = rex_fetch_all($pdo, "SELECT DISTINCT order_type FROM restaurant_orders WHERE order_type IS NOT NULL AND order_type <> '' ORDER BY order_type ASC", [], $warnings, 'Order types');
        $orderTypes = array_column($orderTypes, 'order_type');
    }
}

$where = " DATE({$orderDateExpr}) BETWEEN :date_from AND :date_to ";
$params = [':date_from' => $dateFrom, ':date_to' => $dateTo];

if ($orderHasStatus) {
    if (!$includeOpen && $statusFilter === '') {
        $where .= " AND LOWER(COALESCE(ro.status,'')) NOT IN ('void','cancelled','canceled','deleted','open','sent','preparing','served','bill_requested') ";
    } elseif ($statusFilter !== '') {
        $where .= " AND LOWER(COALESCE(ro.status,'')) = :status_filter ";
        $params[':status_filter'] = strtolower($statusFilter);
    } else {
        $where .= " AND LOWER(COALESCE(ro.status,'')) NOT IN ('void','cancelled','canceled','deleted') ";
    }
}
if ($selectedPropertyId > 0 && $orderHasProperty) {
    $where .= " AND ro.property_id = :property_id ";
    $params[':property_id'] = $selectedPropertyId;
}
if ($orderTypeFilter !== '' && $orderHasOrderType) {
    $where .= " AND ro.order_type = :order_type ";
    $params[':order_type'] = $orderTypeFilter;
}
if ($closeMethodFilter !== '' && $orderHasCloseMethod) {
    $where .= " AND COALESCE(ro.close_method,'') = :close_method ";
    $params[':close_method'] = $closeMethodFilter;
}
if ($mealFilter !== '' && $orderHasMealPeriod) {
    $where .= " AND COALESCE(ro.meal_period,'') = :meal_period ";
    $params[':meal_period'] = $mealFilter;
}
if ($selectedCategoryId > 0) {
    if ($menuHasCategory && $itemHasMenuItem) {
        $where .= " AND mi.category_id = :category_id ";
        $params[':category_id'] = $selectedCategoryId;
    } elseif ($itemHasCategory) {
        $where .= " AND ri.category_id = :category_id ";
        $params[':category_id'] = $selectedCategoryId;
    }
}

$baseJoin = "FROM restaurant_order_items ri INNER JOIN restaurant_orders ro ON ro.id = ri.restaurant_order_id ";
$baseJoin .= ($hasMenuItems && $itemHasMenuItem) ? " LEFT JOIN restaurant_menu_items mi ON mi.id = ri.menu_item_id " : "";
if ($hasCategories) {
    if ($menuHasCategory && $itemHasMenuItem) $baseJoin .= " LEFT JOIN restaurant_menu_categories mc ON mc.id = mi.category_id ";
    elseif ($itemHasCategory) $baseJoin .= " LEFT JOIN restaurant_menu_categories mc ON mc.id = ri.category_id ";
}
if ($hasProperties && $orderHasProperty) $baseJoin .= " LEFT JOIN properties p ON p.id = ro.property_id ";
if ($hasTables && $orderHasTable) $baseJoin .= " LEFT JOIN restaurant_tables rt ON rt.id = ro.table_id ";
if ($hasAreas && $hasTables && $orderHasTable) $baseJoin .= " LEFT JOIN restaurant_areas ra ON ra.id = rt.area_id ";

$categoryExpr = $hasCategories ? "COALESCE(mc.{$catNameCol}, 'Uncategorised')" : "'Uncategorised'";
$itemCodeExpr = ($menuHasCode && $itemHasMenuItem) ? "COALESCE(mi.item_code,'')" : "''";
$itemDisplayExpr = ($menuHasName && $itemHasMenuItem) ? "COALESCE(mi.item_name, {$itemNameExpr})" : $itemNameExpr;
$propertyExpr = ($hasProperties && $orderHasProperty) ? "COALESCE(p.name,'Restaurant')" : "'Restaurant'";
$propertyCodeExpr = ($hasProperties && $orderHasProperty) ? "COALESCE(p.code,'')" : "''";
$tableExpr = ($hasTables && $orderHasTable) ? "COALESCE(rt.table_no,'Walk-in')" : "'Walk-in'";
$areaExpr = ($hasAreas && $hasTables && $orderHasTable) ? "COALESCE(ra.area_name,'No Area / Walk-in')" : "'No Area / Walk-in'";
$orderTypeExpr = $orderHasOrderType ? "COALESCE(NULLIF(ro.order_type,''),'unknown')" : "'restaurant'";
$statusExpr = $orderHasStatus ? "COALESCE(ro.status,'')" : "''";
$closeMethodExpr = $orderHasCloseMethod ? "COALESCE(NULLIF(ro.close_method,''),'Not Set')" : "'Not Set'";
$mealExpr = $orderHasMealPeriod ? "COALESCE(NULLIF(ro.meal_period,''),'Not Set')" : $itemPrinterRoleExpr;
$cashierExpr = $orderHasCashier ? "COALESCE(NULLIF(ro.cashier_name,''),'Unspecified')" : "'Unspecified'";
$coversExpr = $orderHasCovers ? "COALESCE(ro.covers,0)" : "0";
$surchargeExpr = $orderHasSurcharge ? "COALESCE(ro.surcharge_amount,0)" : "0";
$discountExpr = $orderHasDiscount ? "COALESCE(ro.discount_amount,0)" : "0";
$orderTotalExpr = $orderHasTotal ? "COALESCE(ro.total_amount,0)" : "0";
$guestExpr = $orderHasGuest ? "COALESCE(ro.guest_name,'')" : "''";
$roomExpr = $orderHasRoom ? "COALESCE(ro.room_no,'')" : "''";
$groupExpr = $orderHasGroup ? "COALESCE(ro.group_name,'')" : "''";
$notesExpr = $orderHasNotes ? "COALESCE(ro.notes,'')" : "''";

$foodExpr = $menuHasFood ? "COALESCE(mi.is_food,0)" : "0";
$barExpr = $menuHasBar ? "COALESCE(mi.is_bar,0)" : "0";
$grossLineExpr = "(COALESCE(ri.{$itemTotalCol},0) + COALESCE(ri.{$itemTaxCol},0))";
$netLineExpr = "COALESCE(ri.{$itemTotalCol},0)";
$taxLineExpr = "COALESCE(ri.{$itemTaxCol},0)";
$qtyExpr = "COALESCE(ri.{$itemQtyCol},0)";
$unitPriceExpr = "COALESCE(ri.{$itemPriceCol},0)";

$summary = [
    'gross_sales' => 0, 'net_sales' => 0, 'tax_total' => 0, 'orders_count' => 0,
    'items_qty' => 0, 'covers' => 0, 'average_check' => 0, 'spend_per_cover' => 0,
    'food_sales' => 0, 'bar_sales' => 0, 'curio_sales' => 0, 'spa_sales' => 0, 'other_sales' => 0,
    'payments_total' => 0, 'folio_settlements_total' => 0, 'total_settled' => 0, 'balance' => 0,
    'surcharge_total' => 0, 'discount_total' => 0, 'room_charges' => 0, 'walkins' => 0,
    'open_tabs' => 0, 'open_value' => 0,
];

$dailyRows = $hourlyRows = $categoryRows = $itemRows = $paymentRows = $settlementRows = $orderTypeRows = $mealRows = $cashierRows = $tableRows = $areaRows = $openTabs = $recentOrders = $transactionRows = $stationRows = $curioRows = $spaRows = $menuEngineeringRows = [];

if (!$errors) {
    $summaryRow = rex_fetch_one($pdo, "
        SELECT
            COALESCE(SUM({$grossLineExpr}),0) AS gross_sales,
            COALESCE(SUM({$netLineExpr}),0) AS net_sales,
            COALESCE(SUM({$taxLineExpr}),0) AS tax_total,
            COUNT(DISTINCT ro.id) AS orders_count,
            COALESCE(SUM({$qtyExpr}),0) AS items_qty,
            COALESCE(SUM(CASE WHEN {$foodExpr}=1 THEN {$grossLineExpr} ELSE 0 END),0) AS food_sales,
            COALESCE(SUM(CASE WHEN {$barExpr}=1 THEN {$grossLineExpr} ELSE 0 END),0) AS bar_sales,
            COALESCE(SUM(CASE WHEN LOWER({$categoryExpr}) LIKE '%curio%' THEN {$grossLineExpr} ELSE 0 END),0) AS curio_sales,
            COALESCE(SUM(CASE WHEN LOWER({$categoryExpr}) LIKE '%spa%' THEN {$grossLineExpr} ELSE 0 END),0) AS spa_sales
        {$baseJoin}
        WHERE {$where}
          AND {$itemStatusExpr}
    ", $params, $warnings, 'Executive summary');
    $summary = array_merge($summary, $summaryRow);

    $orderAgg = rex_fetch_one($pdo, "
        SELECT
            COUNT(DISTINCT ro.id) AS orders_count,
            COALESCE(SUM(DISTINCT CASE WHEN ro.id IS NOT NULL THEN {$coversExpr} ELSE 0 END),0) AS covers,
            COALESCE(SUM(DISTINCT CASE WHEN ro.id IS NOT NULL THEN {$surchargeExpr} ELSE 0 END),0) AS surcharge_total,
            COALESCE(SUM(DISTINCT CASE WHEN ro.id IS NOT NULL THEN {$discountExpr} ELSE 0 END),0) AS discount_total,
            COALESCE(SUM(DISTINCT CASE WHEN {$orderTypeExpr}='walkin' THEN 1 ELSE 0 END),0) AS walkins,
            COALESCE(SUM(DISTINCT CASE WHEN {$closeMethodExpr} IN ('room','master','guest_folio','folio','room_folio','Room Folio','Guest Folio','Master Folio') THEN {$orderTotalExpr} ELSE 0 END),0) AS room_charges
        FROM restaurant_orders ro
        WHERE DATE({$orderDateExpr}) BETWEEN :date_from AND :date_to
        " . ($orderHasStatus && $statusFilter !== '' ? " AND LOWER(COALESCE(ro.status,'')) = :status_filter " : "") . "
        " . ($selectedPropertyId > 0 && $orderHasProperty ? " AND ro.property_id = :property_id " : "") . "
        " . ($orderTypeFilter !== '' && $orderHasOrderType ? " AND ro.order_type = :order_type " : "") . "
        " . ($closeMethodFilter !== '' && $orderHasCloseMethod ? " AND COALESCE(ro.close_method,'') = :close_method " : "") . "
        " . ($mealFilter !== '' && $orderHasMealPeriod ? " AND COALESCE(ro.meal_period,'') = :meal_period " : "") . "
    ", $params, $warnings, 'Order summary');
    $summary = array_merge($summary, $orderAgg);

    $summary['other_sales'] = max(0, (float)$summary['gross_sales'] - (float)$summary['food_sales'] - (float)$summary['bar_sales'] - (float)$summary['curio_sales'] - (float)$summary['spa_sales']);
    $summary['average_check'] = (int)$summary['orders_count'] > 0 ? ((float)$summary['gross_sales'] / (int)$summary['orders_count']) : 0;
    $summary['spend_per_cover'] = (int)$summary['covers'] > 0 ? ((float)$summary['gross_sales'] / (int)$summary['covers']) : 0;

    if ($hasPayments) {
        $paymentStatusWhere = rex_column_exists($pdo, 'restaurant_payments', 'status') ? " AND LOWER(COALESCE(rp.status,'posted')) = 'posted' " : "";
        $paymentParams = [':date_from' => $dateFrom, ':date_to' => $dateTo];
        $paymentPropSql = '';
        if ($selectedPropertyId > 0 && $orderHasProperty) { $paymentPropSql = " AND ro.property_id = :property_id "; $paymentParams[':property_id'] = $selectedPropertyId; }
        if ($orderTypeFilter !== '' && $orderHasOrderType) { $paymentPropSql .= " AND ro.order_type = :order_type "; $paymentParams[':order_type'] = $orderTypeFilter; }

        $payTotal = rex_fetch_one($pdo, "
            SELECT COALESCE(SUM(rp.amount),0) AS payments_total
            FROM restaurant_payments rp
            INNER JOIN restaurant_orders ro ON ro.id = rp.restaurant_order_id
            WHERE DATE({$paymentDateExpr}) BETWEEN :date_from AND :date_to
            {$paymentStatusWhere}
            {$paymentPropSql}
        ", $paymentParams, $warnings, 'Payment total');
        $summary['payments_total'] = (float)($payTotal['payments_total'] ?? 0);

        $paymentRows = rex_fetch_all($pdo, "
            SELECT COALESCE(NULLIF(rp.payment_method,''),'Unspecified') AS label,
                   COUNT(*) AS count_no,
                   COALESCE(SUM(rp.amount),0) AS amount
            FROM restaurant_payments rp
            INNER JOIN restaurant_orders ro ON ro.id = rp.restaurant_order_id
            WHERE DATE({$paymentDateExpr}) BETWEEN :date_from AND :date_to
            {$paymentStatusWhere}
            {$paymentPropSql}
            GROUP BY label
            ORDER BY amount DESC
        ", $paymentParams, $warnings, 'Payment methods');
    }

    if ($hasSettlements) {
        $settParams = [':date_from' => $dateFrom, ':date_to' => $dateTo];
        $settWhere = " DATE(COALESCE(rs.created_at, {$orderDateExpr})) BETWEEN :date_from AND :date_to AND COALESCE(rs.status,'posted')='posted' ";
        if ($selectedPropertyId > 0 && $orderHasProperty) { $settWhere .= " AND ro.property_id = :property_id "; $settParams[':property_id'] = $selectedPropertyId; }
        if ($orderTypeFilter !== '' && $orderHasOrderType) { $settWhere .= " AND ro.order_type = :order_type "; $settParams[':order_type'] = $orderTypeFilter; }
        $settTotal = rex_fetch_one($pdo, "
            SELECT COALESCE(SUM(CASE WHEN rs.destination_type IN ('room_folio','guest_folio','master_folio','group_master','company_account','folio') THEN rs.amount ELSE 0 END),0) AS folio_settlements_total
            FROM restaurant_split_settlements rs
            INNER JOIN restaurant_orders ro ON ro.id = rs.restaurant_order_id
            WHERE {$settWhere}
        ", $settParams, $warnings, 'Folio settlements total');
        $summary['folio_settlements_total'] = (float)($settTotal['folio_settlements_total'] ?? 0);

        $settlementRows = rex_fetch_all($pdo, "
            SELECT COALESCE(NULLIF(rs.destination_type,''),'folio') AS label,
                   COUNT(*) AS count_no,
                   COALESCE(SUM(rs.amount),0) AS amount
            FROM restaurant_split_settlements rs
            INNER JOIN restaurant_orders ro ON ro.id = rs.restaurant_order_id
            WHERE {$settWhere}
            GROUP BY label
            ORDER BY amount DESC
        ", $settParams, $warnings, 'Settlements by destination');
    }

    $summary['total_settled'] = (float)$summary['payments_total'] + (float)$summary['folio_settlements_total'];
    $summary['balance'] = (float)$summary['gross_sales'] - (float)$summary['total_settled'];

    if ($orderHasStatus) {
        $open = rex_fetch_one($pdo, "
            SELECT COUNT(*) AS open_tabs, COALESCE(SUM({$orderTotalExpr}),0) AS open_value
            FROM restaurant_orders ro
            WHERE LOWER(COALESCE(ro.status,'')) IN ('open','sent','preparing','served','bill_requested')
            " . ($selectedPropertyId > 0 && $orderHasProperty ? " AND ro.property_id = :property_id " : "") . "
        ", ($selectedPropertyId > 0 && $orderHasProperty ? [':property_id' => $selectedPropertyId] : []), $warnings, 'Open tabs');
        $summary['open_tabs'] = (int)($open['open_tabs'] ?? 0);
        $summary['open_value'] = (float)($open['open_value'] ?? 0);
    }

    $dailyRows = rex_fetch_all($pdo, "
        SELECT DATE({$orderDateExpr}) AS sale_date,
               COUNT(DISTINCT ro.id) AS orders_count,
               COALESCE(SUM({$qtyExpr}),0) AS qty_sold,
               COALESCE(SUM({$netLineExpr}),0) AS net_sales,
               COALESCE(SUM({$taxLineExpr}),0) AS tax_total,
               COALESCE(SUM({$grossLineExpr}),0) AS gross_sales
        {$baseJoin}
        WHERE {$where} AND {$itemStatusExpr}
        GROUP BY sale_date
        ORDER BY sale_date ASC
    ", $params, $warnings, 'Daily sales');

    $hourlyRows = rex_fetch_all($pdo, "
        SELECT HOUR({$orderDateExpr}) AS sale_hour,
               COUNT(DISTINCT ro.id) AS orders_count,
               COALESCE(SUM({$qtyExpr}),0) AS qty_sold,
               COALESCE(SUM({$grossLineExpr}),0) AS gross_sales
        {$baseJoin}
        WHERE {$where} AND {$itemStatusExpr}
        GROUP BY sale_hour
        ORDER BY sale_hour ASC
    ", $params, $warnings, 'Hourly sales');

    $categoryRows = rex_fetch_all($pdo, "
        SELECT {$categoryExpr} AS category_name,
               COUNT(DISTINCT ro.id) AS orders_count,
               COALESCE(SUM({$qtyExpr}),0) AS qty_sold,
               COALESCE(SUM({$netLineExpr}),0) AS net_sales,
               COALESCE(SUM({$taxLineExpr}),0) AS tax_total,
               COALESCE(SUM({$grossLineExpr}),0) AS gross_sales
        {$baseJoin}
        WHERE {$where} AND {$itemStatusExpr}
        GROUP BY category_name
        ORDER BY gross_sales DESC
    ", $params, $warnings, 'Category sales');

    $itemRows = rex_fetch_all($pdo, "
        SELECT {$categoryExpr} AS category_name,
               {$itemCodeExpr} AS item_code,
               {$itemDisplayExpr} AS item_name,
               COUNT(DISTINCT ro.id) AS orders_count,
               COALESCE(SUM({$qtyExpr}),0) AS qty_sold,
               COALESCE(AVG({$unitPriceExpr}),0) AS avg_unit_price,
               COALESCE(SUM({$netLineExpr}),0) AS net_sales,
               COALESCE(SUM({$taxLineExpr}),0) AS tax_total,
               COALESCE(SUM({$grossLineExpr}),0) AS gross_sales
        {$baseJoin}
        WHERE {$where} AND {$itemStatusExpr}
        GROUP BY category_name, item_code, item_name
        ORDER BY gross_sales DESC, qty_sold DESC
        LIMIT 2000
    ", $params, $warnings, 'Items sold');

    $orderTypeRows = rex_fetch_all($pdo, "
        SELECT {$orderTypeExpr} AS label,
               COUNT(DISTINCT ro.id) AS orders_count,
               COALESCE(SUM({$qtyExpr}),0) AS qty_sold,
               COALESCE(SUM({$grossLineExpr}),0) AS amount
        {$baseJoin}
        WHERE {$where} AND {$itemStatusExpr}
        GROUP BY label
        ORDER BY amount DESC
    ", $params, $warnings, 'Order type analysis');

    $mealRows = rex_fetch_all($pdo, "
        SELECT {$mealExpr} AS label,
               COUNT(DISTINCT ro.id) AS orders_count,
               COALESCE(SUM({$coversExpr}),0) AS covers,
               COALESCE(SUM({$qtyExpr}),0) AS qty_sold,
               COALESCE(SUM({$grossLineExpr}),0) AS amount
        {$baseJoin}
        WHERE {$where} AND {$itemStatusExpr}
        GROUP BY label
        ORDER BY amount DESC
    ", $params, $warnings, 'Meal / station analysis');

    $stationRows = rex_fetch_all($pdo, "
        SELECT {$itemPrinterRoleExpr} AS label,
               COALESCE(SUM({$qtyExpr}),0) AS qty_sold,
               COALESCE(SUM({$grossLineExpr}),0) AS amount
        {$baseJoin}
        WHERE {$where} AND {$itemStatusExpr}
        GROUP BY label
        ORDER BY amount DESC
    ", $params, $warnings, 'Kitchen / bar station analysis');

    $cashierRows = rex_fetch_all($pdo, "
        SELECT {$cashierExpr} AS cashier_name,
               COUNT(DISTINCT ro.id) AS orders_count,
               COALESCE(SUM({$coversExpr}),0) AS covers,
               COALESCE(SUM({$qtyExpr}),0) AS qty_sold,
               COALESCE(SUM({$grossLineExpr}),0) AS gross_sales
        {$baseJoin}
        WHERE {$where} AND {$itemStatusExpr}
        GROUP BY cashier_name
        ORDER BY gross_sales DESC
    ", $params, $warnings, 'Cashier / waiter analysis');

    $tableRows = rex_fetch_all($pdo, "
        SELECT {$tableExpr} AS table_no,
               COUNT(DISTINCT ro.id) AS orders_count,
               COALESCE(SUM({$coversExpr}),0) AS covers,
               COALESCE(SUM({$grossLineExpr}),0) AS gross_sales
        {$baseJoin}
        WHERE {$where} AND {$itemStatusExpr}
        GROUP BY table_no
        ORDER BY gross_sales DESC
        LIMIT 50
    ", $params, $warnings, 'Table performance');

    $areaRows = rex_fetch_all($pdo, "
        SELECT {$areaExpr} AS area_name,
               COUNT(DISTINCT ro.id) AS orders_count,
               COALESCE(SUM({$coversExpr}),0) AS covers,
               COALESCE(SUM({$grossLineExpr}),0) AS gross_sales
        {$baseJoin}
        WHERE {$where} AND {$itemStatusExpr}
        GROUP BY area_name
        ORDER BY gross_sales DESC
    ", $params, $warnings, 'Area performance');

    $curioRows = rex_fetch_all($pdo, "
        SELECT {$categoryExpr} AS category_name,
               {$itemDisplayExpr} AS item_name,
               COALESCE(SUM({$qtyExpr}),0) AS qty_sold,
               COALESCE(SUM({$grossLineExpr}),0) AS gross_sales
        {$baseJoin}
        WHERE {$where} AND {$itemStatusExpr} AND LOWER({$categoryExpr}) LIKE '%curio%'
        GROUP BY category_name, item_name
        ORDER BY gross_sales DESC
        LIMIT 100
    ", $params, $warnings, 'Curio analysis');

    $spaRows = rex_fetch_all($pdo, "
        SELECT {$categoryExpr} AS category_name,
               {$itemDisplayExpr} AS item_name,
               COALESCE(SUM({$qtyExpr}),0) AS qty_sold,
               COALESCE(SUM({$grossLineExpr}),0) AS gross_sales
        {$baseJoin}
        WHERE {$where} AND {$itemStatusExpr} AND LOWER({$categoryExpr}) LIKE '%spa%'
        GROUP BY category_name, item_name
        ORDER BY gross_sales DESC
        LIMIT 100
    ", $params, $warnings, 'Spa analysis');

    $openTabs = rex_fetch_all($pdo, "
        SELECT ro.id,
               ro.order_no,
               {$orderDateExpr} AS opened_at,
               {$statusExpr} AS status,
               {$guestExpr} AS guest_name,
               {$roomExpr} AS room_no,
               {$tableExpr} AS table_no,
               {$cashierExpr} AS cashier_name,
               {$orderTotalExpr} AS total_amount,
               TIMESTAMPDIFF(MINUTE, {$orderDateExpr}, NOW()) AS age_minutes
        FROM restaurant_orders ro
        " . ($hasTables && $orderHasTable ? "LEFT JOIN restaurant_tables rt ON rt.id = ro.table_id" : "") . "
        WHERE " . ($orderHasStatus ? "LOWER(COALESCE(ro.status,'')) IN ('open','sent','preparing','served','bill_requested')" : "1=0") . "
        " . ($selectedPropertyId > 0 && $orderHasProperty ? " AND ro.property_id = :property_id" : "") . "
        ORDER BY age_minutes DESC, ro.id ASC
        LIMIT 50
    ", ($selectedPropertyId > 0 && $orderHasProperty ? [':property_id' => $selectedPropertyId] : []), $warnings, 'Open tabs detail');

    $recentOrders = rex_fetch_all($pdo, "
        SELECT ro.id,
               ro.order_no,
               {$orderDateExpr} AS sale_datetime,
               {$statusExpr} AS status,
               {$orderTypeExpr} AS order_type,
               {$closeMethodExpr} AS close_method,
               {$guestExpr} AS guest_name,
               {$roomExpr} AS room_no,
               {$tableExpr} AS table_no,
               {$cashierExpr} AS cashier_name,
               {$orderTotalExpr} AS total_amount
        FROM restaurant_orders ro
        " . ($hasTables && $orderHasTable ? "LEFT JOIN restaurant_tables rt ON rt.id = ro.table_id" : "") . "
        WHERE DATE({$orderDateExpr}) BETWEEN :date_from AND :date_to
        " . ($orderHasStatus && $statusFilter !== '' ? " AND LOWER(COALESCE(ro.status,'')) = :status_filter" : "") . "
        " . ($selectedPropertyId > 0 && $orderHasProperty ? " AND ro.property_id = :property_id" : "") . "
        ORDER BY sale_datetime DESC, ro.id DESC
        LIMIT 50
    ", $params, $warnings, 'Recent orders');

    $transactionRows = rex_fetch_all($pdo, "
        SELECT ro.id AS restaurant_order_id,
               ro.order_no,
               {$orderDateExpr} AS sale_datetime,
               {$propertyExpr} AS property_name,
               {$propertyCodeExpr} AS property_code,
               {$tableExpr} AS table_no,
               {$areaExpr} AS area_name,
               {$categoryExpr} AS category_name,
               {$itemCodeExpr} AS item_code,
               {$itemDisplayExpr} AS item_name,
               {$qtyExpr} AS qty,
               {$unitPriceExpr} AS unit_price,
               COALESCE(ri.{$itemTaxRateCol},0) AS tax_rate,
               {$taxLineExpr} AS tax_amount,
               {$netLineExpr} AS line_total,
               {$grossLineExpr} AS gross_line_total,
               {$guestExpr} AS guest_name,
               {$roomExpr} AS room_no,
               {$groupExpr} AS group_name,
               {$orderTypeExpr} AS order_type,
               {$statusExpr} AS status,
               {$closeMethodExpr} AS close_method,
               {$mealExpr} AS meal_period,
               {$cashierExpr} AS cashier_name
        {$baseJoin}
        WHERE {$where} AND {$itemStatusExpr}
        ORDER BY sale_datetime DESC, ro.id DESC
        LIMIT 5000
    ", $params, $warnings, 'Transaction register');

    /* Menu engineering: high/low popularity vs high/low contribution. */
    $totalQty = 0.0;
    $totalAvgRev = 0.0;
    foreach ($itemRows as $r) { $totalQty += (float)$r['qty_sold']; $totalAvgRev += (float)$r['gross_sales']; }
    $avgItemQty = count($itemRows) ? ($totalQty / count($itemRows)) : 0;
    $avgItemRevenue = count($itemRows) ? ($totalAvgRev / count($itemRows)) : 0;
    foreach ($itemRows as $r) {
        $popular = (float)$r['qty_sold'] >= $avgItemQty;
        $highRevenue = (float)$r['gross_sales'] >= $avgItemRevenue;
        $class = 'Dog';
        if ($popular && $highRevenue) $class = 'Star';
        elseif ($popular && !$highRevenue) $class = 'Plow Horse';
        elseif (!$popular && $highRevenue) $class = 'Puzzle';
        $r['engineering_class'] = $class;
        $r['popularity_index'] = $avgItemQty > 0 ? ((float)$r['qty_sold'] / $avgItemQty) : 0;
        $r['revenue_index'] = $avgItemRevenue > 0 ? ((float)$r['gross_sales'] / $avgItemRevenue) : 0;
        $menuEngineeringRows[] = $r;
    }
}

/* CSV export: broad but simple. */
if ($export === 'csv' && !$errors) {
    $filename = 'restaurant_executive_report_' . date('Ymd_His') . '.csv';
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['Restaurant Executive Report', $dateFrom . ' to ' . $dateTo]);
    fputcsv($out, []);
    fputcsv($out, ['Executive Summary']);
    foreach ($summary as $key => $value) fputcsv($out, [rex_label($key), $value]);
    fputcsv($out, []);
    fputcsv($out, ['Daily Sales']);
    fputcsv($out, ['Date', 'Orders', 'Qty', 'Net', 'Tax', 'Gross']);
    foreach ($dailyRows as $r) fputcsv($out, [$r['sale_date'], $r['orders_count'], $r['qty_sold'], $r['net_sales'], $r['tax_total'], $r['gross_sales']]);
    fputcsv($out, []);
    fputcsv($out, ['Items Sold']);
    fputcsv($out, ['Category', 'Item Code', 'Item', 'Orders', 'Qty', 'Avg Price', 'Net', 'Tax', 'Gross']);
    foreach ($itemRows as $r) fputcsv($out, [$r['category_name'], $r['item_code'], $r['item_name'], $r['orders_count'], $r['qty_sold'], $r['avg_unit_price'], $r['net_sales'], $r['tax_total'], $r['gross_sales']]);
    fputcsv($out, []);
    fputcsv($out, ['Payments']);
    fputcsv($out, ['Method', 'Count', 'Amount']);
    foreach ($paymentRows as $r) fputcsv($out, [$r['label'], $r['count_no'], $r['amount']]);
    fputcsv($out, []);
    fputcsv($out, ['Transaction Register']);
    fputcsv($out, ['Date', 'Property', 'Order', 'Table', 'Area', 'Category', 'Item Code', 'Item', 'Qty', 'Unit', 'Tax Rate', 'Tax', 'Net', 'Gross', 'Guest', 'Room', 'Group', 'Type', 'Status', 'Close Method', 'Meal/Station', 'Cashier']);
    foreach ($transactionRows as $r) {
        fputcsv($out, [$r['sale_datetime'], $r['property_name'], $r['order_no'], $r['table_no'], $r['area_name'], $r['category_name'], $r['item_code'], $r['item_name'], $r['qty'], $r['unit_price'], $r['tax_rate'], $r['tax_amount'], $r['line_total'], $r['gross_line_total'], $r['guest_name'], $r['room_no'], $r['group_name'], $r['order_type'], $r['status'], $r['close_method'], $r['meal_period'], $r['cashier_name']]);
    }
    fclose($out);
    exit;
}

/* Excel export using HTML workbook-compatible output. */
if (in_array($export, ['xls', 'excel'], true) && !$errors) {
    $filename = 'restaurant_executive_report_' . date('Ymd_His') . '.xls';
    header('Content-Type: application/vnd.ms-excel; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Pragma: no-cache');
    header('Expires: 0');
    $x = 'rex_xls_cell';
    $n = static fn($v, $d = 2): string => number_format((float)$v, (int)$d, '.', '');
    echo "<!DOCTYPE html><html><head><meta charset='utf-8'><style>
        body{font-family:Arial,Helvetica,sans-serif;color:#111827} h1{font-size:22px;margin:0;color:#fff} h2{font-size:15px;background:#e5e7eb;padding:7px;margin-top:18px}
        table{border-collapse:collapse;margin-bottom:14px;width:100%} th{background:#1f2937;color:#fff;border:1px solid #9ca3af;padding:6px} td{border:1px solid #d1d5db;padding:5px;vertical-align:top}
        .title{background:#0f172a;color:#fff;padding:14px}.num{text-align:right}.barwrap{width:180px;background:#e5e7eb;height:13px}.bar{height:13px;background:#2563eb}.good{background:#dcfce7}.warn{background:#fef3c7}.bad{background:#fee2e2}
    </style></head><body>";
    echo "<table><tr><td class='title' colspan='12'><h1>Restaurant Executive Report</h1><div>Period: " . $x($dateFrom) . " to " . $x($dateTo) . " | Exported: " . date('Y-m-d H:i:s') . "</div></td></tr></table>";

    echo "<h2>Executive Summary</h2><table><tr>";
    foreach (['gross_sales','net_sales','tax_total','payments_total','folio_settlements_total','total_settled','balance','orders_count','covers','average_check','spend_per_cover','open_tabs','open_value','surcharge_total','discount_total'] as $k) echo '<th>' . $x(rex_label($k)) . '</th>';
    echo '</tr><tr>';
    foreach (['gross_sales','net_sales','tax_total','payments_total','folio_settlements_total','total_settled','balance','orders_count','covers','average_check','spend_per_cover','open_tabs','open_value','surcharge_total','discount_total'] as $k) echo '<td class="num">' . $x($n($summary[$k] ?? 0, in_array($k, ['orders_count','covers','open_tabs'], true) ? 0 : 2)) . '</td>';
    echo '</tr></table>';

    echo "<h2>Sales Mix</h2><table><tr><th>Segment</th><th>Sales</th><th>Share</th><th>Visual</th></tr>";
    foreach ([['Food',$summary['food_sales']],['Bar / Drinks',$summary['bar_sales']],['Curio',$summary['curio_sales']],['Spa',$summary['spa_sales']],['Other',$summary['other_sales']]] as $seg) {
        $pct = rex_pct_raw($seg[1], $summary['gross_sales']);
        echo '<tr><td>' . $x($seg[0]) . '</td><td class="num">' . $n($seg[1]) . '</td><td class="num">' . $n($pct) . '%</td><td><div class="barwrap"><div class="bar" style="width:' . $n($pct) . '%"></div></div></td></tr>';
    }
    echo '</table>';

    $sections = [
        'Daily Revenue' => [$dailyRows, ['sale_date','orders_count','qty_sold','net_sales','tax_total','gross_sales']],
        'Hourly Revenue' => [$hourlyRows, ['sale_hour','orders_count','qty_sold','gross_sales']],
        'Categories' => [$categoryRows, ['category_name','orders_count','qty_sold','net_sales','tax_total','gross_sales']],
        'Items Sold' => [$itemRows, ['category_name','item_code','item_name','orders_count','qty_sold','avg_unit_price','net_sales','tax_total','gross_sales']],
        'Payments' => [$paymentRows, ['label','count_no','amount']],
        'Folio Settlements' => [$settlementRows, ['label','count_no','amount']],
        'Meal / Station' => [$mealRows, ['label','orders_count','covers','qty_sold','amount']],
        'Cashiers / Waiters' => [$cashierRows, ['cashier_name','orders_count','covers','qty_sold','gross_sales']],
        'Tables' => [$tableRows, ['table_no','orders_count','covers','gross_sales']],
        'Areas' => [$areaRows, ['area_name','orders_count','covers','gross_sales']],
        'Curio' => [$curioRows, ['category_name','item_name','qty_sold','gross_sales']],
        'Spa' => [$spaRows, ['category_name','item_name','qty_sold','gross_sales']],
        'Menu Engineering' => [$menuEngineeringRows, ['category_name','item_name','qty_sold','gross_sales','popularity_index','revenue_index','engineering_class']],
        'Open Tabs' => [$openTabs, ['order_no','opened_at','status','guest_name','room_no','table_no','cashier_name','total_amount','age_minutes']],
        'Transaction Register' => [$transactionRows, ['sale_datetime','property_name','order_no','table_no','area_name','category_name','item_code','item_name','qty','unit_price','tax_rate','tax_amount','line_total','gross_line_total','guest_name','room_no','group_name','order_type','status','close_method','meal_period','cashier_name']],
    ];
    foreach ($sections as $title => [$rows, $cols]) {
        echo '<h2>' . $x($title) . '</h2><table><tr>';
        foreach ($cols as $col) echo '<th>' . $x(rex_label($col)) . '</th>';
        echo '</tr>';
        foreach ($rows as $row) {
            echo '<tr>';
            foreach ($cols as $col) {
                $val = $row[$col] ?? '';
                $isNum = is_numeric($val) && !in_array($col, ['order_no','room_no'], true);
                echo '<td' . ($isNum ? ' class="num"' : '') . '>' . $x($isNum ? $n($val, (str_contains($col,'count') || str_contains($col,'hour') || str_contains($col,'minute') ? 0 : 2)) : $val) . '</td>';
            }
            echo '</tr>';
        }
        echo '</table>';
    }
    echo '</body></html>';
    exit;
}

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

$grossSales = (float)$summary['gross_sales'];
$maxDaily = max(1, ...array_map(static fn($r) => (float)($r['gross_sales'] ?? 0), $dailyRows ?: [['gross_sales'=>1]]));
$maxHourly = max(1, ...array_map(static fn($r) => (float)($r['gross_sales'] ?? 0), $hourlyRows ?: [['gross_sales'=>1]]));
$maxCategory = max(1, ...array_map(static fn($r) => (float)($r['gross_sales'] ?? 0), $categoryRows ?: [['gross_sales'=>1]]));
$maxItem = max(1, ...array_map(static fn($r) => (float)($r['gross_sales'] ?? 0), array_slice($itemRows, 0, 20) ?: [['gross_sales'=>1]]));
?>
<style>
    .rex-hero{padding:14px 18px!important;margin-bottom:14px!important;border-radius:16px!important}.rex-hero .hero-title{font-size:1.38rem!important;line-height:1.15!important;margin-top:.15rem!important}.rex-hero .hero-subtitle{font-size:.86rem!important;margin-top:.25rem!important;max-width:1050px}.rex-hero .page-eyebrow{font-size:.72rem!important;letter-spacing:.08em;text-transform:uppercase}.rex-hero .btn{padding:.42rem .75rem}.rex-filter .card-body{padding:12px!important}.rex-mini{font-size:.78rem;color:#64748b}.rex-bar{height:12px;background:#e5e7eb;border-radius:999px;overflow:hidden;min-width:90px}.rex-bar span{display:block;height:100%;border-radius:999px;background:linear-gradient(90deg,#0ea5e9,#2563eb)}.rex-bar.green span{background:linear-gradient(90deg,#22c55e,#16a34a)}.rex-bar.orange span{background:linear-gradient(90deg,#f59e0b,#f97316)}.rex-chart-row{display:flex;align-items:center;gap:10px;margin-bottom:10px}.rex-chart-label{width:135px;font-size:.82rem;color:#475569;white-space:nowrap;overflow:hidden;text-overflow:ellipsis}.rex-chart-val{width:95px;text-align:right;font-weight:700}.rex-scroll{max-height:620px;overflow:auto}.rex-sticky thead th{position:sticky;top:0;z-index:5;background:#f8fafc;box-shadow:inset 0 -1px 0 rgba(15,23,42,.12),0 2px 4px rgba(15,23,42,.06)}.rex-pill{display:inline-flex;align-items:center;gap:4px;border:1px solid rgba(15,23,42,.1);background:#fff;border-radius:999px;padding:3px 8px;font-size:.75rem;color:#475569}.rex-mix-card{border:1px solid rgba(15,23,42,.08);border-radius:16px;background:#fff;padding:14px;height:100%}.rex-class-Star{background:#dcfce7!important}.rex-class-Plow-Horse{background:#dbeafe!important}.rex-class-Puzzle{background:#fef3c7!important}.rex-class-Dog{background:#fee2e2!important}@media print{.sidebar,.navbar,.rex-no-print,.btn,.report-filter-card{display:none!important}.card{break-inside:avoid}.rex-scroll{max-height:none;overflow:visible}.hero-band{background:#111827!important;color:#fff!important}}
</style>

<div class="hero-band rex-hero">
    <div class="row g-3 align-items-center">
        <div class="col-lg-8">
            <div class="page-eyebrow text-white-50"><i class="bi bi-bar-chart-line"></i> Restaurant Executive Intelligence</div>
            <div class="hero-title">Restaurant Executive Report</div>
            <div class="hero-subtitle">One comprehensive view of restaurant, bar, curio, spa, payment methods, covers, folios, open tabs, menu engineering, table performance and transaction detail.</div>
        </div>
        <div class="col-lg-4 text-lg-end rex-no-print">
            <div class="d-flex flex-wrap gap-2 justify-content-lg-end">
                <a href="reports.php?<?php echo rex_h(http_build_query(array_merge($_GET, ['export'=>'xls']))); ?>" class="btn btn-light"><i class="bi bi-file-earmark-excel me-2"></i>Excel</a>
                <a href="reports.php?<?php echo rex_h(http_build_query(array_merge($_GET, ['export'=>'csv']))); ?>" class="btn btn-outline-light"><i class="bi bi-download me-2"></i>CSV</a>
                <button type="button" onclick="window.print()" class="btn btn-outline-light"><i class="bi bi-printer me-2"></i>Print/PDF</button>
            </div>
        </div>
    </div>
</div>

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

<div class="card panel-card mb-3 report-filter-card rex-filter rex-no-print">
    <div class="card-body">
        <form method="get" class="row g-2 align-items-end">
            <div class="col-md-2"><label class="form-label">Date From</label><input type="date" name="date_from" class="form-control" value="<?php echo rex_h($dateFrom); ?>"></div>
            <div class="col-md-2"><label class="form-label">Date To</label><input type="date" name="date_to" class="form-control" value="<?php echo rex_h($dateTo); ?>"></div>
            <div class="col-md-2"><label class="form-label">Property</label><select name="property_id" class="form-select"><option value="0">All properties</option><?php foreach ($properties as $p): ?><option value="<?php echo (int)$p['id']; ?>" <?php echo $selectedPropertyId===(int)$p['id']?'selected':''; ?>><?php echo rex_h($p['name']); ?><?php echo !empty($p['code'])?' - '.rex_h($p['code']):''; ?></option><?php endforeach; ?></select></div>
            <div class="col-md-2"><label class="form-label">Category</label><select name="category_id" class="form-select"><option value="0">All categories</option><?php foreach ($categories as $c): ?><option value="<?php echo (int)$c['id']; ?>" <?php echo $selectedCategoryId===(int)$c['id']?'selected':''; ?>><?php echo rex_h($c['category_name']); ?></option><?php endforeach; ?></select></div>
            <div class="col-md-1"><label class="form-label">Status</label><select name="status" class="form-select"><option value="">All</option><?php foreach(['closed'=>'Closed','open'=>'Open','sent'=>'Sent','preparing'=>'Preparing','served'=>'Served','bill_requested'=>'Bill Requested','void'=>'Void'] as $k=>$v): ?><option value="<?php echo rex_h($k); ?>" <?php echo $statusFilter===$k?'selected':''; ?>><?php echo rex_h($v); ?></option><?php endforeach; ?></select></div>
            <div class="col-md-1"><label class="form-label">Type</label><select name="order_type" class="form-select"><option value="">All</option><?php foreach ($orderTypes as $t): ?><option value="<?php echo rex_h($t); ?>" <?php echo $orderTypeFilter===(string)$t?'selected':''; ?>><?php echo rex_h(rex_label($t)); ?></option><?php endforeach; ?></select></div>
            <div class="col-md-1"><label class="form-label">Open</label><select name="include_open" class="form-select"><option value="0" <?php echo $includeOpen===0?'selected':''; ?>>No</option><option value="1" <?php echo $includeOpen===1?'selected':''; ?>>Yes</option></select></div>
            <div class="col-md-1 d-grid"><button class="btn btn-primary"><i class="bi bi-funnel"></i></button></div>
            <div class="col-12 d-flex gap-2 flex-wrap pt-1">
                <a class="btn btn-sm btn-light" href="reports.php"><i class="bi bi-x-circle me-1"></i>Reset</a>
                <a class="btn btn-sm btn-outline-secondary" href="reports.php?date_from=<?php echo rex_h($today); ?>&date_to=<?php echo rex_h($today); ?>&status=closed">Today</a>
                <a class="btn btn-sm btn-outline-secondary" href="reports.php?date_from=<?php echo rex_h($monthStart); ?>&date_to=<?php echo rex_h($today); ?>&status=closed">This Month</a>
            </div>
        </form>
    </div>
</div>

<div class="row g-3 mb-3">
    <?php $cards = [
        ['Gross Sales','gross_sales','bi-cash-coin','success',false], ['Net Sales','net_sales','bi-receipt','primary',false], ['VAT','tax_total','bi-percent','warning',false], ['Settled','total_settled','bi-credit-card','info',false], ['Balance','balance','bi-exclamation-triangle','danger',false], ['Orders','orders_count','bi-journal-check','secondary',true], ['Covers','covers','bi-people','dark',true], ['Avg Check','average_check','bi-calculator','primary',false], ['Spend/Cover','spend_per_cover','bi-person-check','success',false], ['Open Tabs','open_tabs','bi-hourglass-split','warning',true], ['Curio','curio_sales','bi-bag','info',false], ['Spa','spa_sales','bi-stars','secondary',false]
    ]; foreach ($cards as $c): ?>
        <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-icon bg-<?php echo rex_h($c[3]); ?>-subtle text-<?php echo rex_h($c[3]); ?>"><i class="bi <?php echo rex_h($c[2]); ?>"></i></div><div><div class="metric-label"><?php echo rex_h($c[0]); ?></div><div class="metric-value"><?php echo $c[4] ? rex_num($summary[$c[1]] ?? 0,0) : rex_money($summary[$c[1]] ?? 0); ?></div></div></div></div>
    <?php endforeach; ?>
</div>

<div class="row g-3 mb-3">
    <div class="col-xl-4"><div class="card panel-card h-100"><div class="card-header"><h5 class="mb-0">Sales Mix</h5></div><div class="card-body">
        <?php foreach ([['Food',$summary['food_sales']],['Bar / Drinks',$summary['bar_sales']],['Curio',$summary['curio_sales']],['Spa',$summary['spa_sales']],['Other',$summary['other_sales']]] as $seg): $pct=rex_pct_raw($seg[1],$grossSales); ?>
            <div class="rex-chart-row"><div class="rex-chart-label"><?php echo rex_h($seg[0]); ?></div><div class="rex-bar flex-grow-1"><span style="width:<?php echo min(100,$pct); ?>%"></span></div><div class="rex-chart-val"><?php echo rex_money($seg[1]); ?></div></div>
        <?php endforeach; ?>
    </div></div></div>
    <div class="col-xl-4"><div class="card panel-card h-100"><div class="card-header"><h5 class="mb-0">Daily Revenue Trend</h5></div><div class="card-body">
        <?php if (!$dailyRows): ?><div class="text-center text-muted py-4">No daily sales.</div><?php else: foreach ($dailyRows as $r): ?>
            <div class="rex-chart-row"><div class="rex-chart-label"><?php echo rex_h($r['sale_date']); ?></div><div class="rex-bar green flex-grow-1"><span style="width:<?php echo rex_bar_width($r['gross_sales'],$maxDaily); ?>%"></span></div><div class="rex-chart-val"><?php echo rex_money($r['gross_sales']); ?></div></div>
        <?php endforeach; endif; ?>
    </div></div></div>
    <div class="col-xl-4"><div class="card panel-card h-100"><div class="card-header"><h5 class="mb-0">Payment Mix</h5></div><div class="card-body">
        <?php if (!$paymentRows && !$settlementRows): ?><div class="text-center text-muted py-4">No payments.</div><?php else: foreach (array_merge($paymentRows, $settlementRows) as $r): $amt=(float)($r['amount']??0); ?>
            <div class="rex-chart-row"><div class="rex-chart-label"><?php echo rex_h(rex_label($r['label'] ?? '')); ?></div><div class="rex-bar orange flex-grow-1"><span style="width:<?php echo min(100,rex_pct_raw($amt,max(1,$summary['total_settled']))); ?>%"></span></div><div class="rex-chart-val"><?php echo rex_money($amt); ?></div></div>
        <?php endforeach; endif; ?>
    </div></div></div>
</div>

<div class="row g-3 mb-3">
    <div class="col-xl-7"><div class="card panel-card h-100"><div class="card-header d-flex justify-content-between"><h5 class="mb-0">Category Performance</h5><span class="rex-mini">Revenue by menu branch</span></div><div class="card-body p-0"><div class="table-responsive"><table class="table table-modern mb-0"><thead><tr><th>Category</th><th class="text-end">Orders</th><th class="text-end">Qty</th><th class="text-end">Net</th><th class="text-end">VAT</th><th class="text-end">Gross</th><th class="text-end">Share</th></tr></thead><tbody><?php if(!$categoryRows): ?><tr><td colspan="7" class="text-center text-muted py-4">No category sales.</td></tr><?php else: foreach($categoryRows as $r): ?><tr><td class="fw-semibold"><?php echo rex_h($r['category_name']); ?></td><td class="text-end"><?php echo rex_num($r['orders_count']); ?></td><td class="text-end"><?php echo rex_num($r['qty_sold'],2); ?></td><td class="text-end"><?php echo rex_money($r['net_sales']); ?></td><td class="text-end"><?php echo rex_money($r['tax_total']); ?></td><td class="text-end fw-bold"><?php echo rex_money($r['gross_sales']); ?></td><td class="text-end"><?php echo rex_pct($r['gross_sales'],$grossSales); ?></td></tr><?php endforeach; endif; ?></tbody></table></div></div></div></div>
    <div class="col-xl-5"><div class="card panel-card h-100"><div class="card-header"><h5 class="mb-0">Top 20 Items</h5></div><div class="card-body"><?php if(!$itemRows): ?><div class="text-center text-muted py-4">No item sales.</div><?php else: foreach(array_slice($itemRows,0,20) as $r): ?><div class="rex-chart-row"><div class="rex-chart-label"><?php echo rex_h($r['item_name']); ?></div><div class="rex-bar flex-grow-1"><span style="width:<?php echo rex_bar_width($r['gross_sales'],$maxItem); ?>%"></span></div><div class="rex-chart-val"><?php echo rex_money($r['gross_sales']); ?></div></div><?php endforeach; endif; ?></div></div></div>
</div>

<div class="row g-3 mb-3">
    <div class="col-xl-4"><div class="card panel-card h-100"><div class="card-header"><h5 class="mb-0">Meal / Station Analysis</h5></div><div class="card-body p-0"><div class="table-responsive"><table class="table table-modern mb-0"><thead><tr><th>Period / Station</th><th class="text-end">Orders</th><th class="text-end">Covers</th><th class="text-end">Sales</th></tr></thead><tbody><?php if(!$mealRows): ?><tr><td colspan="4" class="text-center text-muted py-4">No data.</td></tr><?php else: foreach($mealRows as $r): ?><tr><td><?php echo rex_h(rex_label($r['label'])); ?></td><td class="text-end"><?php echo rex_num($r['orders_count']); ?></td><td class="text-end"><?php echo rex_num($r['covers']); ?></td><td class="text-end fw-bold"><?php echo rex_money($r['amount']); ?></td></tr><?php endforeach; endif; ?></tbody></table></div></div></div></div>
    <div class="col-xl-4"><div class="card panel-card h-100"><div class="card-header"><h5 class="mb-0">Order Type Mix</h5></div><div class="card-body p-0"><div class="table-responsive"><table class="table table-modern mb-0"><thead><tr><th>Type</th><th class="text-end">Orders</th><th class="text-end">Qty</th><th class="text-end">Sales</th></tr></thead><tbody><?php if(!$orderTypeRows): ?><tr><td colspan="4" class="text-center text-muted py-4">No data.</td></tr><?php else: foreach($orderTypeRows as $r): ?><tr><td><?php echo rex_h(rex_label($r['label'])); ?></td><td class="text-end"><?php echo rex_num($r['orders_count']); ?></td><td class="text-end"><?php echo rex_num($r['qty_sold'],2); ?></td><td class="text-end fw-bold"><?php echo rex_money($r['amount']); ?></td></tr><?php endforeach; endif; ?></tbody></table></div></div></div></div>
    <div class="col-xl-4"><div class="card panel-card h-100"><div class="card-header"><h5 class="mb-0">Hourly Sales</h5></div><div class="card-body"><?php if(!$hourlyRows): ?><div class="text-center text-muted py-4">No hourly sales.</div><?php else: foreach($hourlyRows as $r): ?><div class="rex-chart-row"><div class="rex-chart-label"><?php echo str_pad((string)(int)$r['sale_hour'],2,'0',STR_PAD_LEFT); ?>:00</div><div class="rex-bar flex-grow-1"><span style="width:<?php echo rex_bar_width($r['gross_sales'],$maxHourly); ?>%"></span></div><div class="rex-chart-val"><?php echo rex_money($r['gross_sales']); ?></div></div><?php endforeach; endif; ?></div></div></div>
</div>

<div class="row g-3 mb-3">
    <div class="col-xl-6"><div class="card panel-card h-100"><div class="card-header"><h5 class="mb-0">Payment Methods</h5></div><div class="card-body p-0"><div class="table-responsive"><table class="table table-modern mb-0"><thead><tr><th>Method</th><th class="text-end">Count</th><th class="text-end">Amount</th><th class="text-end">Share</th></tr></thead><tbody><?php if(!$paymentRows): ?><tr><td colspan="4" class="text-center text-muted py-4">No payments.</td></tr><?php else: foreach($paymentRows as $r): ?><tr><td><?php echo rex_h(rex_label($r['label'])); ?></td><td class="text-end"><?php echo rex_num($r['count_no']); ?></td><td class="text-end fw-bold"><?php echo rex_money($r['amount']); ?></td><td class="text-end"><?php echo rex_pct($r['amount'],$summary['total_settled']); ?></td></tr><?php endforeach; endif; ?></tbody></table></div></div></div></div>
    <div class="col-xl-6"><div class="card panel-card h-100"><div class="card-header"><h5 class="mb-0">Folio / Other Settlements</h5></div><div class="card-body p-0"><div class="table-responsive"><table class="table table-modern mb-0"><thead><tr><th>Destination</th><th class="text-end">Count</th><th class="text-end">Amount</th><th class="text-end">Share</th></tr></thead><tbody><?php if(!$settlementRows): ?><tr><td colspan="4" class="text-center text-muted py-4">No settlements.</td></tr><?php else: foreach($settlementRows as $r): ?><tr><td><?php echo rex_h(rex_label($r['label'])); ?></td><td class="text-end"><?php echo rex_num($r['count_no']); ?></td><td class="text-end fw-bold"><?php echo rex_money($r['amount']); ?></td><td class="text-end"><?php echo rex_pct($r['amount'],$summary['total_settled']); ?></td></tr><?php endforeach; endif; ?></tbody></table></div></div></div></div>
</div>

<div class="row g-3 mb-3">
    <div class="col-xl-6"><div class="card panel-card h-100"><div class="card-header"><h5 class="mb-0">Cashier / Waiter Performance</h5></div><div class="card-body p-0"><div class="table-responsive"><table class="table table-modern mb-0"><thead><tr><th>Cashier / Waiter</th><th class="text-end">Orders</th><th class="text-end">Covers</th><th class="text-end">Qty</th><th class="text-end">Sales</th></tr></thead><tbody><?php if(!$cashierRows): ?><tr><td colspan="5" class="text-center text-muted py-4">No cashier data.</td></tr><?php else: foreach($cashierRows as $r): ?><tr><td><?php echo rex_h($r['cashier_name']); ?></td><td class="text-end"><?php echo rex_num($r['orders_count']); ?></td><td class="text-end"><?php echo rex_num($r['covers']); ?></td><td class="text-end"><?php echo rex_num($r['qty_sold'],2); ?></td><td class="text-end fw-bold"><?php echo rex_money($r['gross_sales']); ?></td></tr><?php endforeach; endif; ?></tbody></table></div></div></div></div>
    <div class="col-xl-6"><div class="card panel-card h-100"><div class="card-header"><h5 class="mb-0">Area Performance</h5></div><div class="card-body p-0"><div class="table-responsive"><table class="table table-modern mb-0"><thead><tr><th>Area</th><th class="text-end">Orders</th><th class="text-end">Covers</th><th class="text-end">Sales</th></tr></thead><tbody><?php if(!$areaRows): ?><tr><td colspan="4" class="text-center text-muted py-4">No area data.</td></tr><?php else: foreach($areaRows as $r): ?><tr><td><?php echo rex_h($r['area_name']); ?></td><td class="text-end"><?php echo rex_num($r['orders_count']); ?></td><td class="text-end"><?php echo rex_num($r['covers']); ?></td><td class="text-end fw-bold"><?php echo rex_money($r['gross_sales']); ?></td></tr><?php endforeach; endif; ?></tbody></table></div></div></div></div>
</div>

<div class="row g-3 mb-3">
    <div class="col-xl-6"><div class="card panel-card h-100"><div class="card-header"><h5 class="mb-0">Curio Analysis</h5></div><div class="card-body p-0"><div class="table-responsive"><table class="table table-modern mb-0"><thead><tr><th>Category</th><th>Item</th><th class="text-end">Qty</th><th class="text-end">Sales</th></tr></thead><tbody><?php if(!$curioRows): ?><tr><td colspan="4" class="text-center text-muted py-4">No curio sales.</td></tr><?php else: foreach($curioRows as $r): ?><tr><td><?php echo rex_h($r['category_name']); ?></td><td><?php echo rex_h($r['item_name']); ?></td><td class="text-end"><?php echo rex_num($r['qty_sold'],2); ?></td><td class="text-end fw-bold"><?php echo rex_money($r['gross_sales']); ?></td></tr><?php endforeach; endif; ?></tbody></table></div></div></div></div>
    <div class="col-xl-6"><div class="card panel-card h-100"><div class="card-header"><h5 class="mb-0">Spa Analysis</h5></div><div class="card-body p-0"><div class="table-responsive"><table class="table table-modern mb-0"><thead><tr><th>Category</th><th>Treatment / Item</th><th class="text-end">Qty</th><th class="text-end">Sales</th></tr></thead><tbody><?php if(!$spaRows): ?><tr><td colspan="4" class="text-center text-muted py-4">No spa sales.</td></tr><?php else: foreach($spaRows as $r): ?><tr><td><?php echo rex_h($r['category_name']); ?></td><td><?php echo rex_h($r['item_name']); ?></td><td class="text-end"><?php echo rex_num($r['qty_sold'],2); ?></td><td class="text-end fw-bold"><?php echo rex_money($r['gross_sales']); ?></td></tr><?php endforeach; endif; ?></tbody></table></div></div></div></div>
</div>

<div class="card panel-card mb-3"><div class="card-header d-flex justify-content-between align-items-center"><h5 class="mb-0">Menu Engineering</h5><span class="rex-mini">Stars = high popularity/high revenue, Plow Horses = high popularity/low revenue, Puzzles = low popularity/high revenue, Dogs = low/low.</span></div><div class="card-body p-0"><div class="table-responsive rex-scroll"><table class="table table-modern mb-0 rex-sticky"><thead><tr><th>Class</th><th>Category</th><th>Item</th><th class="text-end">Qty</th><th class="text-end">Revenue</th><th class="text-end">Popularity Index</th><th class="text-end">Revenue Index</th></tr></thead><tbody><?php if(!$menuEngineeringRows): ?><tr><td colspan="7" class="text-center text-muted py-4">No menu engineering data.</td></tr><?php else: foreach(array_slice($menuEngineeringRows,0,200) as $r): $cls=str_replace(' ','-',$r['engineering_class']); ?><tr class="rex-class-<?php echo rex_h($cls); ?>"><td><strong><?php echo rex_h($r['engineering_class']); ?></strong></td><td><?php echo rex_h($r['category_name']); ?></td><td><?php echo rex_h($r['item_name']); ?></td><td class="text-end"><?php echo rex_num($r['qty_sold'],2); ?></td><td class="text-end fw-bold"><?php echo rex_money($r['gross_sales']); ?></td><td class="text-end"><?php echo rex_num($r['popularity_index'],2); ?></td><td class="text-end"><?php echo rex_num($r['revenue_index'],2); ?></td></tr><?php endforeach; endif; ?></tbody></table></div></div></div>

<div class="row g-3 mb-3">
    <div class="col-xl-6"><div class="card panel-card h-100"><div class="card-header"><h5 class="mb-0">Table Performance</h5></div><div class="card-body p-0"><div class="table-responsive rex-scroll"><table class="table table-modern mb-0 rex-sticky"><thead><tr><th>Table</th><th class="text-end">Orders</th><th class="text-end">Covers</th><th class="text-end">Sales</th></tr></thead><tbody><?php if(!$tableRows): ?><tr><td colspan="4" class="text-center text-muted py-4">No table data.</td></tr><?php else: foreach($tableRows as $r): ?><tr><td><strong><?php echo rex_h($r['table_no']); ?></strong></td><td class="text-end"><?php echo rex_num($r['orders_count']); ?></td><td class="text-end"><?php echo rex_num($r['covers']); ?></td><td class="text-end fw-bold"><?php echo rex_money($r['gross_sales']); ?></td></tr><?php endforeach; endif; ?></tbody></table></div></div></div></div>
    <div class="col-xl-6"><div class="card panel-card h-100"><div class="card-header"><h5 class="mb-0">Open Tabs Risk</h5></div><div class="card-body p-0"><div class="table-responsive rex-scroll"><table class="table table-modern mb-0 rex-sticky"><thead><tr><th>Order</th><th>Table</th><th>Guest</th><th>Status</th><th class="text-end">Value</th><th class="text-end">Age</th></tr></thead><tbody><?php if(!$openTabs): ?><tr><td colspan="6" class="text-center text-muted py-4">No open tabs.</td></tr><?php else: foreach($openTabs as $r): $age=(int)($r['age_minutes']??0); ?><tr class="<?php echo $age>=120?'table-danger':($age>=60?'table-warning':''); ?>"><td><?php echo rex_h($r['order_no']); ?></td><td><?php echo rex_h($r['table_no']); ?></td><td><?php echo rex_h(trim(($r['guest_name']??'').' '.(($r['room_no']??'')?'Room '.$r['room_no']:''))); ?></td><td><?php echo rex_h(rex_label($r['status'])); ?></td><td class="text-end fw-bold"><?php echo rex_money($r['total_amount']); ?></td><td class="text-end"><?php echo rex_num($age); ?> min</td></tr><?php endforeach; endif; ?></tbody></table></div></div></div></div>
</div>

<div class="card panel-card mb-3"><div class="card-header d-flex justify-content-between align-items-center"><h5 class="mb-0">Items Sold Detail</h5><span class="rex-mini">Top 2,000 item rows</span></div><div class="card-body p-0"><div class="table-responsive rex-scroll"><table class="table table-modern mb-0 rex-sticky"><thead><tr><th>Category</th><th>Code</th><th>Item</th><th class="text-end">Orders</th><th class="text-end">Qty</th><th class="text-end">Avg Price</th><th class="text-end">Net</th><th class="text-end">VAT</th><th class="text-end">Gross</th></tr></thead><tbody><?php if(!$itemRows): ?><tr><td colspan="9" class="text-center text-muted py-4">No items sold.</td></tr><?php else: foreach($itemRows as $r): ?><tr><td><?php echo rex_h($r['category_name']); ?></td><td><?php echo rex_h($r['item_code']); ?></td><td><strong><?php echo rex_h($r['item_name']); ?></strong></td><td class="text-end"><?php echo rex_num($r['orders_count']); ?></td><td class="text-end"><?php echo rex_num($r['qty_sold'],2); ?></td><td class="text-end"><?php echo rex_money($r['avg_unit_price']); ?></td><td class="text-end"><?php echo rex_money($r['net_sales']); ?></td><td class="text-end"><?php echo rex_money($r['tax_total']); ?></td><td class="text-end fw-bold"><?php echo rex_money($r['gross_sales']); ?></td></tr><?php endforeach; endif; ?></tbody></table></div></div></div>

<div class="card panel-card mb-3"><div class="card-header d-flex justify-content-between align-items-center"><h5 class="mb-0">Transaction Register</h5><span class="rex-mini">Latest 5,000 transaction lines</span></div><div class="card-body p-0"><div class="table-responsive rex-scroll"><table class="table table-modern mb-0 rex-sticky"><thead><tr><th>Date</th><th>Property</th><th>Order</th><th>Table</th><th>Area</th><th>Category</th><th>Item</th><th class="text-end">Qty</th><th class="text-end">Unit</th><th class="text-end">VAT</th><th class="text-end">Net</th><th class="text-end">Gross</th><th>Guest / Room</th><th>Type</th><th>Status</th><th>Cashier</th></tr></thead><tbody><?php if(!$transactionRows): ?><tr><td colspan="16" class="text-center text-muted py-4">No transaction lines.</td></tr><?php else: foreach($transactionRows as $r): ?><tr><td><?php echo rex_h($r['sale_datetime']); ?></td><td><?php echo rex_h($r['property_name']); ?></td><td><?php echo rex_h($r['order_no']); ?></td><td><?php echo rex_h($r['table_no']); ?></td><td><?php echo rex_h($r['area_name']); ?></td><td><?php echo rex_h($r['category_name']); ?></td><td><strong><?php echo rex_h($r['item_name']); ?></strong></td><td class="text-end"><?php echo rex_num($r['qty'],2); ?></td><td class="text-end"><?php echo rex_money($r['unit_price']); ?></td><td class="text-end"><?php echo rex_money($r['tax_amount']); ?></td><td class="text-end"><?php echo rex_money($r['line_total']); ?></td><td class="text-end fw-bold"><?php echo rex_money($r['gross_line_total']); ?></td><td><?php echo rex_h(trim(($r['guest_name']?:'') . (($r['room_no']??'') ? ' / Room '.$r['room_no'] : '') . (($r['group_name']??'') ? ' / '.$r['group_name'] : '')) ?: 'Walk-in'); ?></td><td><?php echo rex_h(rex_label($r['order_type'])); ?></td><td><?php echo rex_h(rex_label($r['status'])); ?></td><td><?php echo rex_h($r['cashier_name']); ?></td></tr><?php endforeach; endif; ?></tbody></table></div></div></div>

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