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

$username = $_SESSION['username'] ?? 'Guest';

// ================= DATE RANGE FILTER =================
$default_from = date('Y-m-01');
$default_to   = date('Y-m-d');
$from_date = $_GET['from'] ?? $default_from;
$to_date   = $_GET['to'] ?? $default_to;

// ================= FETCH RESTAURANT ORDERS & ITEMS =================
$orders = $conn->query("
    SELECT o.*, o.created_by AS created_by_name, o.is_reconciled
    FROM restaurant_orders o
    WHERE DATE(o.created_at) BETWEEN '{$conn->real_escape_string($from_date)}'
                                AND '{$conn->real_escape_string($to_date)}'
    ORDER BY o.created_at DESC
");

$analytics = [
    'total_orders' => 0,
    'total_amount' => 0,
    'total_paid' => 0,
    'total_balance' => 0,
    'stock_discrepancies' => 0,
];

$order_data = [];
$daily_stats = [];

while($o = $orders->fetch_assoc()){
    $items_res = $conn->query("
        SELECT i.*, m.name AS menu_name, p.name AS product_name
        FROM restaurant_order_items i
        LEFT JOIN restaurant_menu m ON i.menu_id = m.id
        LEFT JOIN products p ON i.product_id = p.id
        WHERE i.order_id = {$o['id']}
    ");

    $item_list = [];
    $order_total = 0;

    while($it = $items_res->fetch_assoc()){
        $order_total += $it['price'] * $it['qty'];

        // Stock check for products only
        if(!empty($it['product_id'])){
            $stock_row = $conn->query("SELECT quantity FROM products WHERE id={$it['product_id']}")->fetch_assoc();
            $available = $stock_row['quantity'] ?? 0;
            $discrepancy = $available < $it['qty'] ? 1 : 0;
        } else {
            $available = '-';
            $discrepancy = 0;
        }

        if($discrepancy) $analytics['stock_discrepancies']++;

        $item_list[] = array_merge($it, ['available'=>$available, 'discrepancy'=>$discrepancy]);
    }

    $paid = ($o['status'] === 'PAID' ? floatval($o['grand_total']) : 0);
    $balance = floatval($o['grand_total'] ?? $order_total) - $paid;

    $analytics['total_orders']++;
    $analytics['total_amount'] += $order_total;
    $analytics['total_paid'] += $paid;
    $analytics['total_balance'] += $balance;

    $order_data[] = array_merge($o, ['balance'=>$balance,'paid'=>$paid,'items'=>$item_list]);

    // Daily sales
    $day = date('Y-m-d', strtotime($o['created_at']));
    if(!isset($daily_stats[$day])) $daily_stats[$day] = ['sales'=>0];
    $daily_stats[$day]['sales'] += $order_total;
}

// Prepare chart data
$chart_labels = array_keys($daily_stats);
$chart_sales = array_map(fn($d)=>$d['sales'],$daily_stats);
?>

<div class="container mt-4">

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

    <!-- Success Message -->
    <?php if(!empty($_GET['success'])): ?>
    <div class="alert alert-success"><?= htmlspecialchars($_GET['success']) ?></div>
    <?php endif; ?>

    <h3 class="text-center">🍹 Restaurant Sales Reconciliation</h3>
    <hr>

    <!-- Date Filter Form -->
    <form class="row g-2 mb-3" method="get">
        <div class="col-md-3">
            <input type="date" class="form-control" name="from" value="<?= $from_date ?>">
        </div>
        <div class="col-md-3">
            <input type="date" class="form-control" name="to" value="<?= $to_date ?>">
        </div>
        <div class="col-md-2">
            <button type="submit" class="btn btn-primary w-100">Filter</button>
        </div>
    </form>

    <!-- Summary Cards -->
    <div class="row mb-4">
        <div class="col-md-2"><div class="alert alert-success text-center"><strong>Total Orders:</strong><br><?= $analytics['total_orders'] ?></div></div>
        <div class="col-md-3"><div class="alert alert-warning text-center"><strong>Total Amount:</strong><br><?= number_format($analytics['total_amount'],2) ?> MK</div></div>
        <div class="col-md-3"><div class="alert alert-primary text-center"><strong>Total Paid:</strong><br><?= number_format($analytics['total_paid'],2) ?> MK</div></div>
        <div class="col-md-2"><div class="alert alert-danger text-center"><strong>Balance:</strong><br><?= number_format($analytics['total_balance'],2) ?> MK</div></div>
        <div class="col-md-2"><div class="alert alert-dark text-center"><strong>Stock Discrepancies:</strong><br><?= $analytics['stock_discrepancies'] ?></div></div>
    </div>
<div class="row mb-3">

<div class="col-md-2">
<a href="export_restaurant_sales_excel.php?from=<?= $from_date ?>&to=<?= $to_date ?>" 
class="btn btn-success w-100">
<i class="fa fa-file-excel"></i> Excel
</a>
</div>

<div class="col-md-2">
<a href="export_restaurant_sales_pdf.php?from=<?= $from_date ?>&to=<?= $to_date ?>" 
class="btn btn-danger w-100">
<i class="fa fa-file-pdf"></i> PDF
</a>
</div>

</div>
    <!-- Detailed Orders Table -->
    <div class="table-responsive">
    <table class="table table-bordered table-striped table-sm">
    <thead class="bg-dark text-white">
    <tr>
        <th>#</th>
        <th>Order ID</th>
        <th>Created At</th>
        <th>Total</th>
        <th>Paid</th>
        <th>Balance</th>
        <th>Items / Stock</th>
        <th>Created By</th>
        <th>Status</th>
        <th>Action</th>
    </tr>
    </thead>
    <tbody>
    <?php $i=1; foreach($order_data as $o): ?>
    <tr class="<?= $o['balance']>0?'table-warning':'table-success' ?>">
        <td><?= $i ?></td>
        <td><?= $o['id'] ?></td>
        <td><?= date('Y-m-d H:i', strtotime($o['created_at'])) ?></td>
        <td><?= number_format(array_sum(array_map(fn($it)=>$it['price']*$it['qty'],$o['items'])),2) ?></td>
        <td><?= number_format($o['paid'],2) ?></td>
        <td><?= number_format($o['balance'],2) ?></td>
        <td>
            <ul class="mb-0">
            <?php foreach($o['items'] as $it): ?>
                <li>
                 <?= $it['product_name'] ?? $it['menu_name'] ?? ('Menu '.$it['menu_id']) ?> x <?= $it['qty'] ?> 
                 (Stock: <?= $it['available'] ?>)
                </li>
            <?php endforeach; ?>
            </ul>
        </td>
        <td><?= $o['created_by_name'] ?></td>
        <td>
    <?= ($o['balance']>0 || array_sum(array_column($o['items'],'discrepancy'))>0)?'⚠️ Pending':'✔️ OK' ?>
</td>
<td>
    <?php if(!empty($o['is_reconciled'])): ?>
        <span class="badge bg-success">Reconciled</span>
    <?php endif; ?>
</td>
        <td>
            <?php if(empty($o['is_reconciled'])): ?>
                <a href="reconcile_order.php?order_id=<?= $o['id'] ?>" class="btn btn-sm btn-success">Reconcile</a>
            <?php else: ?>
                <span class="badge bg-success">Reconciled</span>
            <?php endif; ?>
        </td>
    </tr>
    <?php $i++; endforeach; ?>
    </tbody>
    </table>
    </div>

    <!-- Daily Sales Chart -->
    <h5 class="mt-4"><i class="fa fa-chart-line"></i> Daily Sales</h5>
    <canvas id="salesChart" height="150"></canvas>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script>
    new Chart(document.getElementById('salesChart'),{
        type:'line',
        data:{
            labels: <?= json_encode($chart_labels) ?>,
            datasets:[{
                label:'Sales MK',
                data: <?= json_encode($chart_sales) ?>,
                borderColor:'rgba(255,206,86,1)',
                backgroundColor:'rgba(255,206,86,0.2)',
                tension:0.3
            }]
        },
        options:{
            responsive:true,
            plugins:{legend:{position:'top'}},
            scales:{y:{beginAtZero:true}}
        }
    });
    </script>

</div>

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