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

$pageTitle = 'Detailed Restaurant Sales Report';

if (!function_exists('h')) {
    function h($value): string { return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8'); }
}
if (!function_exists('money_fmt')) {
    function money_fmt($amount): string { return number_format((float)$amount, 2); }
}
if (!function_exists('rr_table_exists')) {
    function rr_table_exists(PDO $pdo, string $tableName): bool {
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = :table");
        $stmt->execute([':table' => $tableName]);
        return (int)$stmt->fetchColumn() > 0;
    }
}
if (!function_exists('rr_column_exists')) {
    function rr_column_exists(PDO $pdo, string $tableName, string $columnName): bool {
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = :table AND column_name = :column");
        $stmt->execute([':table' => $tableName, ':column' => $columnName]);
        return (int)$stmt->fetchColumn() > 0;
    }
}
if (!function_exists('rr_pct')) {
    function rr_pct($part, $total): float {
        $total = (float)$total;
        return $total > 0 ? (((float)$part / $total) * 100) : 0.0;
    }
}
if (!function_exists('rr_label')) {
    function rr_label(string $value): string {
        $value = trim($value);
        if ($value === '') return 'Unspecified';
        return ucwords(str_replace('_', ' ', $value));
    }
}

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

$dateFrom = trim((string)($_GET['date_from'] ?? $monthStart));
$dateTo = trim((string)($_GET['date_to'] ?? $today));
$selectedPropertyId = (int)($_GET['property_id'] ?? 0);
$selectedCategoryId = (int)($_GET['category_id'] ?? 0);
$selectedOrderType = trim((string)($_GET['order_type'] ?? ''));
$includeOpen = (int)($_GET['include_open'] ?? 0);

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

$requiredTables = ['restaurant_orders', 'restaurant_order_items', 'restaurant_payments', 'restaurant_menu_items', 'restaurant_menu_categories'];
foreach ($requiredTables as $table) {
    if (!rr_table_exists($pdo, $table)) {
        $errors[] = 'Required restaurant table missing: ' . $table;
    }
}

$hasProperties = rr_table_exists($pdo, 'properties');
$hasSplitSettlements = rr_table_exists($pdo, 'restaurant_split_settlements');
$hasOrderProperty = !$errors && rr_column_exists($pdo, 'restaurant_orders', 'property_id');
$hasOrderClosedAt = !$errors && rr_column_exists($pdo, 'restaurant_orders', 'closed_at');
$hasOrderOpenedAt = !$errors && rr_column_exists($pdo, 'restaurant_orders', 'opened_at');
$hasOrderCreatedAt = !$errors && rr_column_exists($pdo, 'restaurant_orders', 'created_at');
$hasOrderStatus = !$errors && rr_column_exists($pdo, 'restaurant_orders', 'status');
$hasOrderType = !$errors && rr_column_exists($pdo, 'restaurant_orders', 'order_type');
$hasCovers = !$errors && rr_column_exists($pdo, 'restaurant_orders', 'covers');
$hasCashierName = !$errors && rr_column_exists($pdo, 'restaurant_orders', 'cashier_name');
$hasMenuCategory = !$errors && rr_column_exists($pdo, 'restaurant_menu_items', 'category_id');
$hasMenuIsFood = !$errors && rr_column_exists($pdo, 'restaurant_menu_items', 'is_food');
$hasMenuIsBar = !$errors && rr_column_exists($pdo, 'restaurant_menu_items', 'is_bar');
$hasPaymentStatus = !$errors && rr_column_exists($pdo, 'restaurant_payments', 'status');

$orderDateExpr = 'ro.id';
if ($hasOrderClosedAt && $hasOrderOpenedAt && $hasOrderCreatedAt) {
    $orderDateExpr = 'COALESCE(ro.closed_at, ro.opened_at, ro.created_at)';
} elseif ($hasOrderClosedAt && $hasOrderOpenedAt) {
    $orderDateExpr = 'COALESCE(ro.closed_at, ro.opened_at)';
} elseif ($hasOrderOpenedAt) {
    $orderDateExpr = 'ro.opened_at';
} elseif ($hasOrderCreatedAt) {
    $orderDateExpr = 'ro.created_at';
}

$paymentDateExpr = rr_column_exists($pdo, 'restaurant_payments', 'payment_date') ? 'rp.payment_date' : 'rp.created_at';

$properties = [];
$categories = [];
$orderTypes = [];

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

    try {
        $categories = $pdo->query("SELECT id, category_name, parent_id FROM restaurant_menu_categories WHERE COALESCE(is_active,1)=1 ORDER BY category_name ASC")->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) { $categories = []; }

    try {
        if ($hasOrderType) {
            $orderTypes = $pdo->query("SELECT DISTINCT order_type FROM restaurant_orders WHERE order_type IS NOT NULL AND order_type <> '' ORDER BY order_type ASC")->fetchAll(PDO::FETCH_COLUMN);
        }
    } catch (Throwable $e) { $orderTypes = []; }
}

$whereOrders = " DATE($orderDateExpr) BETWEEN :date_from AND :date_to ";
$paramsOrders = [':date_from' => $dateFrom, ':date_to' => $dateTo];

if ($hasOrderStatus && !$includeOpen) {
    $whereOrders .= " AND LOWER(COALESCE(ro.status,'')) NOT IN ('void','cancelled','canceled','deleted','open','sent','preparing','served','bill_requested') ";
} elseif ($hasOrderStatus) {
    $whereOrders .= " AND LOWER(COALESCE(ro.status,'')) NOT IN ('void','cancelled','canceled','deleted') ";
}

if ($selectedPropertyId > 0 && $hasOrderProperty) {
    $whereOrders .= " AND ro.property_id = :property_id ";
    $paramsOrders[':property_id'] = $selectedPropertyId;
}
if ($selectedOrderType !== '' && $hasOrderType) {
    $whereOrders .= " AND ro.order_type = :order_type ";
    $paramsOrders[':order_type'] = $selectedOrderType;
}
if ($selectedCategoryId > 0 && $hasMenuCategory) {
    $whereOrders .= " AND mi.category_id = :category_id ";
    $paramsOrders[':category_id'] = $selectedCategoryId;
}

$wherePayments = " DATE($paymentDateExpr) BETWEEN :date_from AND :date_to ";
$paramsPayments = [':date_from' => $dateFrom, ':date_to' => $dateTo];
if ($hasPaymentStatus) {
    $wherePayments .= " AND LOWER(COALESCE(rp.status,'posted')) = 'posted' ";
}
if ($selectedPropertyId > 0 && $hasOrderProperty) {
    $wherePayments .= " AND ro.property_id = :property_id ";
    $paramsPayments[':property_id'] = $selectedPropertyId;
}
if ($selectedOrderType !== '' && $hasOrderType) {
    $wherePayments .= " AND ro.order_type = :order_type ";
    $paramsPayments[':order_type'] = $selectedOrderType;
}

$summary = [
    'gross_sales' => 0.0,
    'net_sales' => 0.0,
    'tax_total' => 0.0,
    'payments_total' => 0.0,
    'folio_settlements_total' => 0.0,
    'total_settled' => 0.0,
    'balance' => 0.0,
    'orders_count' => 0,
    'items_qty' => 0.0,
    'covers' => 0,
    'average_check' => 0.0,
    'spend_per_cover' => 0.0,
    'food_sales' => 0.0,
    'bar_sales' => 0.0,
    'curio_sales' => 0.0,
    'spa_sales' => 0.0,
    'other_sales' => 0.0,
];

$categoryRows = [];
$itemRows = [];
$paymentRows = [];
$orderTypeRows = [];
$hourlyRows = [];
$cashierRows = [];
$dailyRows = [];
$detailRows = [];
$settlementRows = [];

if (!$errors) {
    try {
        $foodExpr = $hasMenuIsFood ? 'COALESCE(mi.is_food,0)' : '0';
        $barExpr = $hasMenuIsBar ? 'COALESCE(mi.is_bar,0)' : '0';
        $catNameExpr = "COALESCE(mc.category_name, 'Uncategorised')";
        $coversExpr = $hasCovers ? 'COALESCE(ro.covers,0)' : '0';

        $stmt = $pdo->prepare("\n            SELECT\n                COALESCE(SUM(roi.line_total + roi.tax_amount), 0) AS gross_sales,\n                COALESCE(SUM(roi.line_total), 0) AS net_sales,\n                COALESCE(SUM(roi.tax_amount), 0) AS tax_total,\n                COUNT(DISTINCT ro.id) AS orders_count,\n                COALESCE(SUM(roi.qty), 0) AS items_qty,\n                COALESCE(SUM(DISTINCT CASE WHEN ro.id IS NOT NULL THEN {$coversExpr} ELSE 0 END), 0) AS covers,\n                COALESCE(SUM(CASE WHEN {$foodExpr}=1 THEN roi.line_total + roi.tax_amount ELSE 0 END), 0) AS food_sales,\n                COALESCE(SUM(CASE WHEN {$barExpr}=1 THEN roi.line_total + roi.tax_amount ELSE 0 END), 0) AS bar_sales,\n                COALESCE(SUM(CASE WHEN LOWER({$catNameExpr}) LIKE '%curio%' THEN roi.line_total + roi.tax_amount ELSE 0 END), 0) AS curio_sales,\n                COALESCE(SUM(CASE WHEN LOWER({$catNameExpr}) LIKE '%spa%' THEN roi.line_total + roi.tax_amount ELSE 0 END), 0) AS spa_sales\n            FROM restaurant_order_items roi\n            INNER JOIN restaurant_orders ro ON ro.id = roi.restaurant_order_id\n            LEFT JOIN restaurant_menu_items mi ON mi.id = roi.menu_item_id\n            LEFT JOIN restaurant_menu_categories mc ON mc.id = mi.category_id\n            WHERE {$whereOrders}\n              AND LOWER(COALESCE(roi.status,'active')) = 'active'\n        ");
        $stmt->execute($paramsOrders);
        $summary = array_merge($summary, $stmt->fetch(PDO::FETCH_ASSOC) ?: []);
        $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']);

        $stmt = $pdo->prepare("\n            SELECT\n                COALESCE(SUM(rp.amount),0) AS payments_total\n            FROM restaurant_payments rp\n            INNER JOIN restaurant_orders ro ON ro.id = rp.restaurant_order_id\n            WHERE {$wherePayments}\n        ");
        $stmt->execute($paramsPayments);
        $summary['payments_total'] = (float)($stmt->fetchColumn() ?: 0);

        if ($hasSplitSettlements) {
            $whereSett = " DATE(COALESCE(rs.created_at, {$orderDateExpr})) BETWEEN :date_from AND :date_to ";
            $paramsSett = [':date_from' => $dateFrom, ':date_to' => $dateTo];
            if ($selectedPropertyId > 0 && $hasOrderProperty) { $whereSett .= " AND ro.property_id = :property_id "; $paramsSett[':property_id'] = $selectedPropertyId; }
            if ($selectedOrderType !== '' && $hasOrderType) { $whereSett .= " AND ro.order_type = :order_type "; $paramsSett[':order_type'] = $selectedOrderType; }

            $stmt = $pdo->prepare("\n                SELECT COALESCE(SUM(rs.amount),0)\n                FROM restaurant_split_settlements rs\n                INNER JOIN restaurant_orders ro ON ro.id = rs.restaurant_order_id\n                WHERE {$whereSett}\n                  AND COALESCE(rs.status,'posted') = 'posted'\n                  AND rs.destination_type IN ('room_folio','guest_folio','master_folio','group_master','company_account','folio')\n            ");
            $stmt->execute($paramsSett);
            $summary['folio_settlements_total'] = (float)($stmt->fetchColumn() ?: 0);
        }

        $summary['total_settled'] = (float)$summary['payments_total'] + (float)$summary['folio_settlements_total'];
        $summary['balance'] = (float)$summary['gross_sales'] - (float)$summary['total_settled'];
        $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;
    } catch (Throwable $e) {
        $errors[] = 'Could not load restaurant summary: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $stmt = $pdo->prepare("\n            SELECT\n                COALESCE(mc.category_name, 'Uncategorised') AS category_name,\n                COUNT(DISTINCT ro.id) AS orders_count,\n                COALESCE(SUM(roi.qty),0) AS qty_sold,\n                COALESCE(SUM(roi.line_total),0) AS net_sales,\n                COALESCE(SUM(roi.tax_amount),0) AS tax_total,\n                COALESCE(SUM(roi.line_total + roi.tax_amount),0) AS gross_sales\n            FROM restaurant_order_items roi\n            INNER JOIN restaurant_orders ro ON ro.id = roi.restaurant_order_id\n            LEFT JOIN restaurant_menu_items mi ON mi.id = roi.menu_item_id\n            LEFT JOIN restaurant_menu_categories mc ON mc.id = mi.category_id\n            WHERE {$whereOrders}\n              AND LOWER(COALESCE(roi.status,'active')) = 'active'\n            GROUP BY category_name\n            ORDER BY gross_sales DESC\n        ");
        $stmt->execute($paramsOrders);
        $categoryRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $stmt = $pdo->prepare("\n            SELECT\n                COALESCE(mc.category_name, 'Uncategorised') AS category_name,\n                COALESCE(mi.item_code, '') AS item_code,\n                COALESCE(mi.item_name, roi.item_name) AS item_name,\n                COUNT(DISTINCT ro.id) AS orders_count,\n                COALESCE(SUM(roi.qty),0) AS qty_sold,\n                COALESCE(AVG(roi.unit_price),0) AS avg_unit_price,\n                COALESCE(SUM(roi.line_total),0) AS net_sales,\n                COALESCE(SUM(roi.tax_amount),0) AS tax_total,\n                COALESCE(SUM(roi.line_total + roi.tax_amount),0) AS gross_sales\n            FROM restaurant_order_items roi\n            INNER JOIN restaurant_orders ro ON ro.id = roi.restaurant_order_id\n            LEFT JOIN restaurant_menu_items mi ON mi.id = roi.menu_item_id\n            LEFT JOIN restaurant_menu_categories mc ON mc.id = mi.category_id\n            WHERE {$whereOrders}\n              AND LOWER(COALESCE(roi.status,'active')) = 'active'\n            GROUP BY category_name, item_code, item_name\n            ORDER BY gross_sales DESC, qty_sold DESC\n            LIMIT 1500\n        ");
        $stmt->execute($paramsOrders);
        $itemRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $stmt = $pdo->prepare("\n            SELECT\n                COALESCE(NULLIF(rp.payment_method,''), 'Unspecified') AS payment_method,\n                COUNT(*) AS payment_count,\n                COALESCE(SUM(rp.amount),0) AS amount\n            FROM restaurant_payments rp\n            INNER JOIN restaurant_orders ro ON ro.id = rp.restaurant_order_id\n            WHERE {$wherePayments}\n            GROUP BY payment_method\n            ORDER BY amount DESC\n        ");
        $stmt->execute($paramsPayments);
        $paymentRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if ($hasSplitSettlements) {
            $whereSett = " DATE(COALESCE(rs.created_at, {$orderDateExpr})) BETWEEN :date_from AND :date_to ";
            $paramsSett = [':date_from' => $dateFrom, ':date_to' => $dateTo];
            if ($selectedPropertyId > 0 && $hasOrderProperty) { $whereSett .= " AND ro.property_id = :property_id "; $paramsSett[':property_id'] = $selectedPropertyId; }
            if ($selectedOrderType !== '' && $hasOrderType) { $whereSett .= " AND ro.order_type = :order_type "; $paramsSett[':order_type'] = $selectedOrderType; }
            $stmt = $pdo->prepare("\n                SELECT\n                    COALESCE(NULLIF(rs.destination_type,''),'folio') AS settlement_type,\n                    COUNT(*) AS settlement_count,\n                    COALESCE(SUM(rs.amount),0) AS amount\n                FROM restaurant_split_settlements rs\n                INNER JOIN restaurant_orders ro ON ro.id = rs.restaurant_order_id\n                WHERE {$whereSett}\n                  AND COALESCE(rs.status,'posted') = 'posted'\n                GROUP BY settlement_type\n                ORDER BY amount DESC\n            ");
            $stmt->execute($paramsSett);
            $settlementRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        }

        $orderTypeSelect = $hasOrderType ? "COALESCE(NULLIF(ro.order_type,''),'unknown')" : "'restaurant'";
        $stmt = $pdo->prepare("\n            SELECT\n                {$orderTypeSelect} AS order_type,\n                COUNT(DISTINCT ro.id) AS orders_count,\n                COALESCE(SUM(roi.qty),0) AS qty_sold,\n                COALESCE(SUM(roi.line_total + roi.tax_amount),0) AS gross_sales\n            FROM restaurant_order_items roi\n            INNER JOIN restaurant_orders ro ON ro.id = roi.restaurant_order_id\n            LEFT JOIN restaurant_menu_items mi ON mi.id = roi.menu_item_id\n            WHERE {$whereOrders}\n              AND LOWER(COALESCE(roi.status,'active')) = 'active'\n            GROUP BY order_type\n            ORDER BY gross_sales DESC\n        ");
        $stmt->execute($paramsOrders);
        $orderTypeRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $cashierSelect = $hasCashierName ? "COALESCE(NULLIF(ro.cashier_name,''),'Unspecified')" : "'Unspecified'";
        $stmt = $pdo->prepare("\n            SELECT\n                {$cashierSelect} AS cashier_name,\n                COUNT(DISTINCT ro.id) AS orders_count,\n                COALESCE(SUM(roi.qty),0) AS qty_sold,\n                COALESCE(SUM(roi.line_total + roi.tax_amount),0) AS gross_sales\n            FROM restaurant_order_items roi\n            INNER JOIN restaurant_orders ro ON ro.id = roi.restaurant_order_id\n            LEFT JOIN restaurant_menu_items mi ON mi.id = roi.menu_item_id\n            WHERE {$whereOrders}\n              AND LOWER(COALESCE(roi.status,'active')) = 'active'\n            GROUP BY cashier_name\n            ORDER BY gross_sales DESC\n        ");
        $stmt->execute($paramsOrders);
        $cashierRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $stmt = $pdo->prepare("\n            SELECT\n                HOUR({$orderDateExpr}) AS sale_hour,\n                COUNT(DISTINCT ro.id) AS orders_count,\n                COALESCE(SUM(roi.qty),0) AS qty_sold,\n                COALESCE(SUM(roi.line_total + roi.tax_amount),0) AS gross_sales\n            FROM restaurant_order_items roi\n            INNER JOIN restaurant_orders ro ON ro.id = roi.restaurant_order_id\n            LEFT JOIN restaurant_menu_items mi ON mi.id = roi.menu_item_id\n            WHERE {$whereOrders}\n              AND LOWER(COALESCE(roi.status,'active')) = 'active'\n            GROUP BY sale_hour\n            ORDER BY sale_hour ASC\n        ");
        $stmt->execute($paramsOrders);
        $hourlyRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $stmt = $pdo->prepare("\n            SELECT\n                DATE({$orderDateExpr}) AS sale_date,\n                COUNT(DISTINCT ro.id) AS orders_count,\n                COALESCE(SUM(roi.qty),0) AS qty_sold,\n                COALESCE(SUM(roi.line_total),0) AS net_sales,\n                COALESCE(SUM(roi.tax_amount),0) AS tax_total,\n                COALESCE(SUM(roi.line_total + roi.tax_amount),0) AS gross_sales\n            FROM restaurant_order_items roi\n            INNER JOIN restaurant_orders ro ON ro.id = roi.restaurant_order_id\n            LEFT JOIN restaurant_menu_items mi ON mi.id = roi.menu_item_id\n            WHERE {$whereOrders}\n              AND LOWER(COALESCE(roi.status,'active')) = 'active'\n            GROUP BY sale_date\n            ORDER BY sale_date ASC\n        ");
        $stmt->execute($paramsOrders);
        $dailyRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $propertySelect = ($hasProperties && $hasOrderProperty) ? "COALESCE(p.name,'No Property')" : "'Restaurant'";
        $propertyJoin = ($hasProperties && $hasOrderProperty) ? "LEFT JOIN properties p ON p.id = ro.property_id" : "";
        $stmt = $pdo->prepare("\n            SELECT\n                ro.id AS restaurant_order_id,\n                ro.order_no,\n                {$orderDateExpr} AS sale_datetime,\n                {$propertySelect} AS property_name,\n                COALESCE(mc.category_name, 'Uncategorised') AS category_name,\n                COALESCE(mi.item_code, '') AS item_code,\n                roi.item_name,\n                roi.qty,\n                roi.unit_price,\n                roi.tax_rate,\n                roi.tax_amount,\n                roi.line_total,\n                (roi.line_total + roi.tax_amount) AS gross_line_total,\n                COALESCE(ro.guest_name,'') AS guest_name,\n                COALESCE(ro.room_no,'') AS room_no,\n                {$orderTypeSelect} AS order_type,\n                {$cashierSelect} AS cashier_name\n            FROM restaurant_order_items roi\n            INNER JOIN restaurant_orders ro ON ro.id = roi.restaurant_order_id\n            LEFT JOIN restaurant_menu_items mi ON mi.id = roi.menu_item_id\n            LEFT JOIN restaurant_menu_categories mc ON mc.id = mi.category_id\n            {$propertyJoin}\n            WHERE {$whereOrders}\n              AND LOWER(COALESCE(roi.status,'active')) = 'active'\n            ORDER BY sale_datetime DESC, ro.id DESC, roi.id ASC\n            LIMIT 3000\n        ");
        $stmt->execute($paramsOrders);
        $detailRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $errors[] = 'Could not load restaurant report sections: ' . $e->getMessage();
    }
}

$export = (string)($_GET['export'] ?? '');

if (in_array($export, ['xls', 'excel'], true) && !$errors) {
    $filename = 'restaurant_sales_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 = static fn($v): string => htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8');
    $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;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}.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}</style></head><body>";
    echo "<table><tr><td class='title' colspan='10'><h1>Detailed Restaurant Sales 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><th>Gross Sales</th><th>Net Sales</th><th>Tax</th><th>Cash/POS Payments</th><th>Folio Settlements</th><th>Total Settled</th><th>Balance</th><th>Orders</th><th>Items Qty</th><th>Covers</th><th>Average Check</th><th>Spend / Cover</th></tr><tr>";
    echo "<td class='num'>{$n($summary['gross_sales'])}</td><td class='num'>{$n($summary['net_sales'])}</td><td class='num'>{$n($summary['tax_total'])}</td><td class='num'>{$n($summary['payments_total'])}</td><td class='num'>{$n($summary['folio_settlements_total'])}</td><td class='num'>{$n($summary['total_settled'])}</td><td class='num'>{$n($summary['balance'])}</td><td class='num'>{$x($summary['orders_count'])}</td><td class='num'>{$n($summary['items_qty'], 2)}</td><td class='num'>{$x($summary['covers'])}</td><td class='num'>{$n($summary['average_check'])}</td><td class='num'>{$n($summary['spend_per_cover'])}</td></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=rr_pct($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 Sales' => [$dailyRows, ['sale_date','orders_count','qty_sold','net_sales','tax_total','gross_sales']],
        'Category Sales' => [$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']],
        'Payment Methods' => [$paymentRows, ['payment_method','payment_count','amount']],
        'Folio / Other Settlements' => [$settlementRows, ['settlement_type','settlement_count','amount']],
        'Order Types' => [$orderTypeRows, ['order_type','orders_count','qty_sold','gross_sales']],
        'Cashiers / Servers' => [$cashierRows, ['cashier_name','orders_count','qty_sold','gross_sales']],
        'Hourly Sales' => [$hourlyRows, ['sale_hour','orders_count','qty_sold','gross_sales']],
        'Sales Detail' => [$detailRows, ['sale_datetime','property_name','order_no','category_name','item_code','item_name','qty','unit_price','tax_rate','tax_amount','line_total','gross_line_total','guest_name','room_no','order_type','cashier_name']],
    ];
    foreach ($sections as $title => [$rows, $cols]) {
        echo '<h2>' . $x($title) . '</h2><table><tr>';
        foreach ($cols as $col) echo '<th>' . $x(rr_label($col)) . '</th>';
        echo '</tr>';
        foreach ($rows as $row) { echo '<tr>'; foreach ($cols as $col) { $val=$row[$col] ?? ''; $class=is_numeric($val)?' class="num"':''; echo '<td'.$class.'>'.$x(is_numeric($val)?$n($val, (strpos($col,'count')!==false||strpos($col,'hour')!==false?0:2)):$val).'</td>'; } echo '</tr>'; }
        echo '</table>';
    }
    echo '</body></html>';
    exit;
}

if ($export === 'csv' && !$errors) {
    $filename = 'restaurant_sales_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, ['Detailed Restaurant Sales Report', $dateFrom . ' to ' . $dateTo]);
    fputcsv($out, []);
    fputcsv($out, ['Summary']);
    foreach ($summary as $k => $v) fputcsv($out, [rr_label($k), $v]);
    fputcsv($out, []);
    fputcsv($out, ['Items Sold']);
    fputcsv($out, ['Category','Item Code','Item','Orders','Qty Sold','Avg Unit Price','Net Sales','Tax','Gross Sales']);
    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 by Method']);
    fputcsv($out, ['Method','Count','Amount']);
    foreach ($paymentRows as $r) fputcsv($out, [$r['payment_method'],$r['payment_count'],$r['amount']]);
    fputcsv($out, []);
    fputcsv($out, ['Sales Detail']);
    fputcsv($out, ['Date','Property','Order','Category','Item Code','Item','Qty','Unit Price','Tax Rate','Tax','Net Line','Gross Line','Guest','Room','Order Type','Cashier']);
    foreach ($detailRows as $r) fputcsv($out, [$r['sale_datetime'],$r['property_name'],$r['order_no'],$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['order_type'],$r['cashier_name']]);
    fclose($out);
    exit;
}

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

$grossSales = (float)$summary['gross_sales'];
$barMax = max(1, $grossSales);
?>
<style>
    .rr-compact-hero{padding:14px 18px!important;margin-bottom:14px!important;border-radius:16px!important}.rr-compact-hero .hero-title{font-size:1.35rem!important;line-height:1.15!important;margin-top:.15rem!important}.rr-compact-hero .hero-subtitle{font-size:.86rem!important;margin-top:.25rem!important;max-width:980px}.rr-compact-hero .page-eyebrow{font-size:.72rem!important;letter-spacing:.08em;text-transform:uppercase}.rr-compact-hero .btn{padding:.42rem .75rem}.rr-bar{height:12px;background:#e5e7eb;border-radius:999px;overflow:hidden;min-width:80px}.rr-bar span{display:block;height:100%;background:linear-gradient(90deg,#0ea5e9,#2563eb);border-radius:999px}.rr-mix-card{border:1px solid rgba(15,23,42,.08);border-radius:16px;padding:14px;background:#fff}.rr-chart-row{display:flex;align-items:center;gap:10px;margin-bottom:10px}.rr-chart-label{width:115px;font-size:.82rem;color:#475569}.rr-chart-val{width:92px;text-align:right;font-weight:700}.rr-scroll{max-height:620px;overflow:auto}.rr-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)}
</style>

<div class="hero-band rr-compact-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-cup-hot"></i> Restaurant / Curio / Spa Sales</div>
            <div class="hero-title">Detailed Restaurant Sales Report</div>
            <div class="hero-subtitle">Items sold, category performance, payment methods, folio settlements, covers, curio/spa sales, hourly movement and transaction detail.</div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <div class="d-flex flex-wrap gap-2 justify-content-lg-end">
                <a href="restaurant_sales_report.php?<?php echo h(http_build_query(array_merge($_GET, ['export'=>'xls']))); ?>" class="btn btn-light"><i class="bi bi-file-earmark-excel me-2"></i>Export Excel</a>
                <a href="restaurant_sales_report.php?<?php echo h(http_build_query(array_merge($_GET, ['export'=>'csv']))); ?>" class="btn btn-outline-light"><i class="bi bi-download me-2"></i>CSV</a>
            </div>
        </div>
    </div>
</div>

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

<div class="card panel-card mb-4">
    <div class="card-body">
        <form method="get" class="row g-3 align-items-end">
            <div class="col-md-2"><label class="form-label">Date From</label><input type="date" name="date_from" class="form-control" value="<?php echo h($dateFrom); ?>"></div>
            <div class="col-md-2"><label class="form-label">Date To</label><input type="date" name="date_to" class="form-control" value="<?php echo h($dateTo); ?>"></div>
            <div class="col-md-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 h($p['name']); ?> <?php echo !empty($p['code'])?' - '.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 h($c['category_name']); ?></option><?php endforeach; ?></select></div>
            <div class="col-md-2"><label class="form-label">Order Type</label><select name="order_type" class="form-select"><option value="">All types</option><?php foreach ($orderTypes as $t): ?><option value="<?php echo h($t); ?>" <?php echo $selectedOrderType===(string)$t?'selected':''; ?>><?php echo h(rr_label((string)$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>
        </form>
    </div>
</div>

<div class="row g-4 mb-4">
    <?php
    $cards = [
        ['Gross Sales','gross_sales','bi-cash-coin','success'], ['Net Sales','net_sales','bi-receipt','primary'], ['Tax','tax_total','bi-percent','warning'], ['Payments','total_settled','bi-credit-card','info'], ['Orders','orders_count','bi-journal-check','secondary',true], ['Covers','covers','bi-people','danger',true],
    ];
    foreach ($cards as $card): ?>
        <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-icon bg-<?php echo h($card[3]); ?>-subtle text-<?php echo h($card[3]); ?>"><i class="bi <?php echo h($card[2]); ?>"></i></div><div><div class="metric-label"><?php echo h($card[0]); ?></div><div class="metric-value"><?php echo !empty($card[4]) ? (int)$summary[$card[1]] : money_fmt($summary[$card[1]]); ?></div></div></div></div>
    <?php endforeach; ?>
</div>

<div class="row g-4 mb-4">
    <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=rr_pct($seg[1],$grossSales); ?>
            <div class="rr-chart-row"><div class="rr-chart-label"><?php echo h($seg[0]); ?></div><div class="rr-bar flex-grow-1"><span style="width:<?php echo min(100,$pct); ?>%"></span></div><div class="rr-chart-val"><?php echo money_fmt($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">Payment Methods</h5></div><div class="card-body">
        <?php if (!$paymentRows): ?><div class="text-muted">No payments found.</div><?php else: foreach (array_slice($paymentRows,0,8) as $r): $pct=rr_pct($r['amount'],$summary['payments_total']); ?><div class="rr-chart-row"><div class="rr-chart-label"><?php echo h(rr_label($r['payment_method'])); ?></div><div class="rr-bar flex-grow-1"><span style="width:<?php echo min(100,$pct); ?>%"></span></div><div class="rr-chart-val"><?php echo money_fmt($r['amount']); ?></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">Manager Indicators</h5></div><div class="card-body">
        <div class="mini-stat mb-3"><span>Average Check</span><strong><?php echo money_fmt($summary['average_check']); ?></strong></div>
        <div class="mini-stat mb-3"><span>Spend Per Cover</span><strong><?php echo money_fmt($summary['spend_per_cover']); ?></strong></div>
        <div class="mini-stat mb-3"><span>Items Sold</span><strong><?php echo money_fmt($summary['items_qty']); ?></strong></div>
        <div class="mini-stat mb-3"><span>Folio / Room Charges</span><strong><?php echo money_fmt($summary['folio_settlements_total']); ?></strong></div>
        <div class="mini-stat mb-0"><span>Unsettled Difference</span><strong class="<?php echo abs((float)$summary['balance']) > 0.01 ? 'text-danger' : 'text-success'; ?>"><?php echo money_fmt($summary['balance']); ?></strong></div>
    </div></div></div>
</div>

<div class="row g-4">
    <div class="col-xl-6"><div class="card panel-card h-100"><div class="card-header"><h5 class="mb-0">Category Sales</h5></div><div class="card-body p-0"><div class="table-responsive"><table class="table table-modern align-middle mb-0"><thead><tr><th>Category</th><th class="text-end">Qty</th><th class="text-end">Net</th><th class="text-end">Tax</th><th class="text-end">Gross</th></tr></thead><tbody><?php if(!$categoryRows): ?><tr><td colspan="5" class="text-center text-muted py-4">No category sales found.</td></tr><?php else: foreach($categoryRows as $r): ?><tr><td><strong><?php echo h($r['category_name']); ?></strong></td><td class="text-end"><?php echo money_fmt($r['qty_sold']); ?></td><td class="text-end"><?php echo money_fmt($r['net_sales']); ?></td><td class="text-end"><?php echo money_fmt($r['tax_total']); ?></td><td class="text-end fw-bold"><?php echo money_fmt($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">Daily Sales Movement</h5></div><div class="card-body p-0"><div class="table-responsive"><table class="table table-modern align-middle mb-0"><thead><tr><th>Date</th><th class="text-end">Orders</th><th class="text-end">Qty</th><th class="text-end">Net</th><th class="text-end">Tax</th><th class="text-end">Gross</th></tr></thead><tbody><?php if(!$dailyRows): ?><tr><td colspan="6" class="text-center text-muted py-4">No daily sales found.</td></tr><?php else: foreach($dailyRows as $r): ?><tr><td><strong><?php echo h($r['sale_date']); ?></strong></td><td class="text-end"><?php echo (int)$r['orders_count']; ?></td><td class="text-end"><?php echo money_fmt($r['qty_sold']); ?></td><td class="text-end"><?php echo money_fmt($r['net_sales']); ?></td><td class="text-end"><?php echo money_fmt($r['tax_total']); ?></td><td class="text-end fw-bold"><?php echo money_fmt($r['gross_sales']); ?></td></tr><?php endforeach; endif; ?></tbody></table></div></div></div></div>

    <div class="col-12"><div class="card panel-card"><div class="card-header d-flex justify-content-between align-items-center"><h5 class="mb-0">All Items Sold</h5><span class="text-muted small">Showing up to 1,500 item summaries</span></div><div class="card-body p-0"><div class="table-responsive rr-scroll"><table class="table table-modern align-middle mb-0 rr-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">Tax</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 h($r['category_name']); ?></td><td><?php echo h($r['item_code']); ?></td><td><strong><?php echo h($r['item_name']); ?></strong></td><td class="text-end"><?php echo (int)$r['orders_count']; ?></td><td class="text-end"><?php echo money_fmt($r['qty_sold']); ?></td><td class="text-end"><?php echo money_fmt($r['avg_unit_price']); ?></td><td class="text-end"><?php echo money_fmt($r['net_sales']); ?></td><td class="text-end"><?php echo money_fmt($r['tax_total']); ?></td><td class="text-end fw-bold"><?php echo money_fmt($r['gross_sales']); ?></td></tr><?php endforeach; endif; ?></tbody></table></div></div></div></div>

    <div class="col-xl-6"><div class="card panel-card"><div class="card-header"><h5 class="mb-0">Hourly Sales</h5></div><div class="card-body p-0"><div class="table-responsive"><table class="table table-modern align-middle mb-0"><thead><tr><th>Hour</th><th class="text-end">Orders</th><th class="text-end">Qty</th><th class="text-end">Sales</th></tr></thead><tbody><?php if(!$hourlyRows): ?><tr><td colspan="4" class="text-center text-muted py-4">No hourly movement found.</td></tr><?php else: foreach($hourlyRows as $r): ?><tr><td><strong><?php echo str_pad((string)(int)$r['sale_hour'],2,'0',STR_PAD_LEFT); ?>:00</strong></td><td class="text-end"><?php echo (int)$r['orders_count']; ?></td><td class="text-end"><?php echo money_fmt($r['qty_sold']); ?></td><td class="text-end fw-bold"><?php echo money_fmt($r['gross_sales']); ?></td></tr><?php endforeach; endif; ?></tbody></table></div></div></div></div>
    <div class="col-xl-6"><div class="card panel-card"><div class="card-header"><h5 class="mb-0">Cashiers / Servers</h5></div><div class="card-body p-0"><div class="table-responsive"><table class="table table-modern align-middle mb-0"><thead><tr><th>Cashier</th><th class="text-end">Orders</th><th class="text-end">Qty</th><th class="text-end">Sales</th></tr></thead><tbody><?php if(!$cashierRows): ?><tr><td colspan="4" class="text-center text-muted py-4">No cashier data found.</td></tr><?php else: foreach($cashierRows as $r): ?><tr><td><strong><?php echo h($r['cashier_name']); ?></strong></td><td class="text-end"><?php echo (int)$r['orders_count']; ?></td><td class="text-end"><?php echo money_fmt($r['qty_sold']); ?></td><td class="text-end fw-bold"><?php echo money_fmt($r['gross_sales']); ?></td></tr><?php endforeach; endif; ?></tbody></table></div></div></div></div>
</div>

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