<?php
include 'includes/auth.php';
include 'db.php';

// ================= FILTERS =================
$today = date('Y-m-d');
$from_date = $_GET['from'] ?? $today;
$to_date   = $_GET['to'] ?? $today;
$waiter    = $_GET['waiter'] ?? '';
$table     = $_GET['table'] ?? '';
$status    = $_GET['status'] ?? '';
$search    = $_GET['search'] ?? '';

// ================= BUILD WHERE =================
$where = [];
$where[] = "DATE(ro.created_at) >= '".$conn->real_escape_string($from_date)."'";
$where[] = "DATE(ro.created_at) <= '".$conn->real_escape_string($to_date)."'";

if($waiter){
    $where[] = "ro.waiter_id = '".$conn->real_escape_string($waiter)."'";
}
if($table){
    $where[] = "ro.table_number LIKE '%".$conn->real_escape_string($table)."%'";
}
if($status){
    $where[] = "ro.status = '".$conn->real_escape_string($status)."'";
}
if($search){
    $s = $conn->real_escape_string($search);
    $where[] = "(ro.table_number LIKE '%$s%' OR ro.created_by LIKE '%$s%')";
}

$where_sql = "WHERE ".implode(" AND ", $where);

// ================= FETCH ORDERS =================
$query = "
SELECT ro.*, w.username AS waiter_name, 
       IFNULL(SUM(roi.price * roi.qty),0) AS total_items_amount
FROM restaurant_orders ro
LEFT JOIN restaurant_order_items roi ON roi.order_id = ro.id
LEFT JOIN users w ON w.id = ro.waiter_id
$where_sql
GROUP BY ro.id
ORDER BY ro.created_at DESC
";

$result = $conn->query($query);

// ================= OUTPUT EXCEL =================
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=restaurant_orders_".date('Ymd_His').".xls");
header("Pragma: no-cache");
header("Expires: 0");
?>

<table border="1">
    <thead>
        <tr style="background-color:#f2f2f2;">
            <th>Order ID</th>
            <th>Waiter</th>
            <th>Table Number</th>
            <th>Subtotal</th>
            <th>Discount</th>
            <th>Grand Total</th>
            <th>Payment Method</th>
            <th>Status</th>
            <th>Created At</th>
            <th>Created By</th>
        </tr>
    </thead>
    <tbody>
        <?php if($result && $result->num_rows): ?>
            <?php while($row = $result->fetch_assoc()): ?>
                <tr>
                    <td><?= $row['id'] ?></td>
                    <td><?= $row['waiter_name'] ?? $row['waiter_id'] ?></td>
                    <td><?= $row['table_number'] ?></td>
                    <td><?= number_format($row['subtotal'],2) ?></td>
                    <td><?= number_format($row['discount'],2) ?></td>
                    <td><?= number_format($row['grand_total'],2) ?></td>
                    <td><?= $row['payment_method'] ?></td>
                    <td><?= $row['status'] ?></td>
                    <td><?= $row['created_at'] ?></td>
                    <td><?= $row['created_by'] ?></td>
                </tr>
            <?php endwhile; ?>
        <?php else: ?>
            <tr><td colspan="10" style="text-align:center;">No orders found</td></tr>
        <?php endif; ?>
    </tbody>
</table>