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

$username = $_SESSION['username'] ?? 'Guest';
$current_user = $_SESSION['username'] ?? '';
$current_role = strtolower($_SESSION['role'] ?? 'guest');
// ================= FILTERS ==================
$filters = [];
$date_from = $_GET['date_from'] ?? '';
$date_to   = $_GET['date_to'] ?? '';
$meal_name = $_GET['meal_name'] ?? '';

// Filter by meal
if(!empty($meal_name)){
    $filters[] = "rm.name LIKE '%".$conn->real_escape_string($meal_name)."%'";
}

// Filter by date range
if(!empty($date_from) && !empty($date_to)){
    $filters[] = "DATE(ro.created_at) BETWEEN '".$conn->real_escape_string($date_from)."' AND '".$conn->real_escape_string($date_to)."'";
}

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

// ================= FETCH REPORT DATA ==================
$report = $conn->query("
    SELECT ro.id, ro.created_at, 
           COALESCE(c.name, 'Guest') AS customer, 
           rm.name AS meal, roi.qty, roi.price, (roi.qty*roi.price) AS total
    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
    $filter_sql
    ORDER BY ro.created_at DESC
");

// ================= CALCULATE TOTAL ==================
$total_revenue = $conn->query("
    SELECT IFNULL(SUM(roi.qty*roi.price),0) AS total
    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
    $filter_sql
")->fetch_assoc()['total'] ?? 0;

// ================= FETCH MEALS FOR DROPDOWN ==================
$mealOptions = $conn->query("SELECT id, name FROM restaurant_menu ORDER BY name ASC");
?>

<div class="container mt-4">
    <button class="btn btn-secondary btn-custom" onclick="history.back()">
    <i class="fa fa-arrow-left"></i> Back
</button>
    <h3 class="text-center mb-3"><i class="fa fa-cutlery text-success"></i> Restaurant Reports</h3>
    <hr>

    <!-- ================= FILTER FORM ================= -->
    <form method="GET" class="row g-3 mb-4 justify-content-center">
        <div class="col-md-3">
            <label>Meal</label>
            <select name="meal_name" class="form-control">
                <option value="">-- All Meals --</option>
                <?php while($m = $mealOptions->fetch_assoc()): ?>
                    <option value="<?= htmlspecialchars($m['name']) ?>" <?= ($meal_name==$m['name'])?'selected':'' ?>>
                        <?= htmlspecialchars($m['name']) ?>
                    </option>
                <?php endwhile; ?>
            </select>
        </div>
        <div class="col-md-3">
            <label>Date From</label>
            <input type="date" name="date_from" value="<?= htmlspecialchars($date_from) ?>" class="form-control">
        </div>
        <div class="col-md-3">
            <label>Date To</label>
            <input type="date" name="date_to" value="<?= htmlspecialchars($date_to) ?>" class="form-control">
        </div>
        <div class="col-md-2 d-flex align-items-end">
            <button class="btn btn-primary w-100"><i class="fa fa-filter"></i> Filter</button>
        </div>
    </form>

    <!-- ================= REPORT TABLE ================= -->
    <div class="table-responsive">
        <table class="table table-bordered table-striped">
            <thead class="bg-dark text-white text-center">
                <tr>
                    <th>#</th>
                    <th>Order Date</th>
                    <th>Customer</th>
                    <th>Meal</th>
                    <th>Qty</th>
                    <th>Price (MK)</th>
                    <th>Total (MK)</th>
                </tr>
            </thead>
            <tbody>
                <?php
                $i = 1;
                while($row = $report->fetch_assoc()):
                ?>
                <tr class="text-center">
                    <td><?= $i++ ?></td>
                    <td><?= date("d-M-Y H:i", strtotime($row['created_at'])) ?></td>
                    <td><?= htmlspecialchars($row['customer']) ?></td>
                    <td><?= htmlspecialchars($row['meal']) ?></td>
                    <td><?= $row['qty'] ?></td>
                    <td><?= number_format($row['price'],2) ?></td>
                    <td><?= number_format($row['total'],2) ?></td>
                </tr>
                <?php endwhile; ?>
            </tbody>
            <tfoot class="text-center fw-bold bg-light">
                <tr>
                    <td colspan="6">Total Revenue</td>
                    <td><?= number_format($total_revenue,2) ?></td>
                </tr>
            </tfoot>
        </table>
    </div>

    <!-- ================= PRINT BUTTON ================= -->
    <div class="text-center mt-3">
        <button class="btn btn-info" onclick="window.print();"><i class="fa fa-print"></i> Print Report</button>
    </div>
</div>

<?php include 'includes/footer.php'; ?>
