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

$date_from     = $_GET['date_from'] ?? date('Y-m-d');
$date_to       = $_GET['date_to'] ?? date('Y-m-d');
$payment_mode  = $_GET['payment_mode'] ?? '';
$created_by    = $_GET['created_by'] ?? '';
$time_from     = $_GET['time_from'] ?? '';
$time_to       = $_GET['time_to'] ?? '';

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=shift_report.xls");

$filters = [];

if (!empty($payment_mode)) {
    $filters[] = "s.payment_method = '".$conn->real_escape_string($payment_mode)."'";
}

if (!empty($created_by)) {
    $filters[] = "s.created_by = '".$conn->real_escape_string($created_by)."'";
}

if (!empty($date_from) && !empty($date_to)) {
    $filters[] = "DATE(ro.created_at) BETWEEN '$date_from' AND '$date_to'";
}

if (!empty($time_from) && !empty($time_to)) {
    $filters[] = "TIME(ro.created_at) BETWEEN '$time_from' AND '$time_to'";
}

$filter_sql = count($filters) ? "WHERE ".implode(" AND ", $filters) : "";

$report = $conn->query("
    SELECT 
        ro.created_at,
        COALESCE(c.name,'Guest') AS customer,
        rm.name AS meal,
        roi.qty,
        roi.price,
        (roi.qty * roi.price) AS total,
        COALESCE(s.payment_method,'-') AS payment_mode,
        COALESCE(s.created_by,'-') AS created_by
    FROM restaurant_orders ro
    JOIN restaurant_order_items roi ON roi.order_id = ro.id
    JOIN restaurant_menu rm ON rm.id = roi.menu_id
    LEFT JOIN guests c ON c.id = ro.customer_id
    LEFT JOIN sales s 
        ON s.total = ro.grand_total
        AND DATE(s.sales_date)=DATE(ro.created_at)
    $filter_sql
    ORDER BY ro.created_at DESC
");
?>

<table border="1">
<tr>
    <th>Date</th>
    <th>Customer</th>
    <th>Meal</th>
    <th>Qty</th>
    <th>Price</th>
    <th>Total</th>
    <th>Payment</th>
    <th>Created By</th>
</tr>

<?php while($r = $report->fetch_assoc()): ?>
<tr>
    <td><?= $r['created_at'] ?></td>
    <td><?= $r['customer'] ?></td>
    <td><?= $r['meal'] ?></td>
    <td><?= $r['qty'] ?></td>
    <td><?= number_format($r['price'],2) ?></td>
    <td><?= number_format($r['total'],2) ?></td>
    <td><?= $r['payment_mode'] ?></td>
    <td><?= $r['created_by'] ?></td>
</tr>
<?php endwhile; ?>
</table>