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

$today = date('Y-m-d');

// ===================== FILTERS =====================
$from = $_GET['from'] ?? $today;
$to   = $_GET['to'] ?? $today;
$payment_method = $_GET['payment_method'] ?? '';
$store_filter = $_GET['store'] ?? ''; // Restaurant, Bar, Conference

// Build WHERE clauses safely
$where_sales = "1=1";
$where_payments = "1=1";

if(!empty($from) && !empty($to)){
    $where_sales .= " AND DATE(s.sale_date) BETWEEN '$from' AND '$to'";
    $where_payments .= " AND DATE(p.payment_date) BETWEEN '$from' AND '$to'";
}

if(!empty($payment_method)){
    $where_sales .= " AND s.payment_method='$payment_method'";
    $where_payments .= " AND p.method='$payment_method'";
}

if(!empty($store_filter)){
    $where_sales .= " AND 
        CASE 
            WHEN s.store_id='1' THEN 'Bar'
            WHEN s.store_id='2' THEN 'Conference'
            WHEN s.store_id='3' THEN 'Restaurant'
            ELSE 'Other'
        END = '$store_filter'";
    $where_payments .= " AND p.type='$store_filter'";
}

// ===================== FETCH COMBINED SALES & PAYMENTS =====================
$sql = "
SELECT s.sale_date AS date, 
       CASE 
         WHEN s.store_id='1' THEN 'Bar'
         WHEN s.store_id='3' THEN 'Restaurant'
         ELSE 'Other'
       END AS store_name,
       c.name AS customer_name,
       s.payment_method AS payment_method,
       s.total AS amount,
       'Sale' AS source
FROM sales s
LEFT JOIN guests c ON s.customer_id=c.id
WHERE $where_sales

UNION ALL

SELECT p.payment_date AS date,
       p.type AS store_name,
       c.name AS customer_name,
       p.method AS payment_method,
       p.amount AS amount,
       'Payment' AS source
FROM payments p
LEFT JOIN customers c ON p.guest_id=c.id
WHERE $where_payments

";

$result = $conn->query($sql);
if(!$result){
    die("SQL Error: " . $conn->error);
}

// ===================== TOTALS PER STORE =====================
$totals = ['Restaurant'=>0,'Bar'=>0,'Conference'=>0,'Other'=>0];
$total_overall = 0;

$all_rows = [];
while($row = $result->fetch_assoc()){
    $amount = $row['amount'];
    $total_overall += $amount;

    switch($row['store_name']){
        case 'Restaurant': $totals['Restaurant'] += $amount; break;
        case 'Bar': $totals['Bar'] += $amount; break;
        case 'Conference': $totals['Conference'] += $amount; break;
        default: $totals['Other'] += $amount;
    }

    $all_rows[] = $row;
}
?>

<div class="container mt-4">
     <div class="welcome-msg">
        <i class="fa fa-user-circle"></i> Welcome, <strong><?= htmlspecialchars($_SESSION['username'] ?? 'Guest') ?></strong>!
    </div>

    <button class="btn btn-secondary btn-custom" onclick="history.back()">
        <i class="fa fa-arrow-left"></i> Back
    </button>

    <h3 class="text-center">💰 All Sales & Payments</h3>
    <hr>

    <!-- ================= FILTER FORM ================= -->
    <form method="GET" class="row g-3 mb-3">
        <div class="col-md-3">
            <label>From</label>
            <input type="date" name="from" value="<?= $from ?>" class="form-control">
        </div>
        <div class="col-md-3">
            <label>To</label>
            <input type="date" name="to" value="<?= $to ?>" class="form-control">
        </div>
        <div class="col-md-3">
            <label>Payment Method</label>
            <select name="payment_method" class="form-control">
                <option value="">-- All --</option>
                <option <?= $payment_method=='Cash'?'selected':'' ?>>Cash</option>
                <option <?= $payment_method=='Mobile Money'?'selected':'' ?>>Mobile Money</option>
                <option <?= $payment_method=='Card'?'selected':'' ?>>Card</option>
                <option <?= $payment_method=='Bank'?'selected':'' ?>>Bank</option>
            </select>
        </div>
        <div class="col-md-3">
            <label>Store/Type</label>
            <select name="store" class="form-control">
                <option value="">-- All --</option>
                <option <?= $store_filter=='Restaurant'?'selected':'' ?>>Restaurant</option>
                <option <?= $store_filter=='Bar'?'selected':'' ?>>Bar</option>
                <option <?= $store_filter=='Conference'?'selected':'' ?>>Conference</option>
                <option <?= $store_filter=='Other'?'selected':'' ?>>Other</option>
            </select>
        </div>
        <div class="col-md-12 mt-2">
            <button class="btn btn-primary w-100">Filter</button>
        </div>
    </form>

    <!-- ================= TOTALS ================= -->
    <div class="alert alert-success">
        <strong>Total Overall:</strong> MK <?= number_format($total_overall,2) ?><br>
        <small>
            Restaurant: <?= number_format($totals['Restaurant'],2) ?> | 
            Bar: <?= number_format($totals['Bar'],2) ?> | 
            Conference: <?= number_format($totals['Conference'],2) ?> | 
            Other: <?= number_format($totals['Other'],2) ?>
        </small>
    </div>

    <!-- ================= TABLE ================= -->
    <table class="table table-bordered table-striped">
        <thead class="bg-dark text-white">
            <tr>
                <th>#</th>
                <th>Date</th>
                <th>Store/Type</th>
                <th>Customer</th>
                <th>Payment Method</th>
                <th>Amount (MK)</th>
                <th>Source</th>
            </tr>
        </thead>
        <tbody>
            <?php 
            $i=1;
            foreach($all_rows as $row): 
            ?>
            <tr>
                <td><?= $i++ ?></td>
                <td><?= date('d-M-Y H:i', strtotime($row['date'])) ?></td>
                <td><?= $row['store_name'] ?></td>
                <td><?= $row['customer_name'] ?? 'Walk-in' ?></td>
                <td><?= $row['payment_method'] ?></td>
                <td><strong><?= number_format($row['amount'],2) ?></strong></td>
                <td><?= $row['source'] ?></td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>

</div>

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