<?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 SALES & ITEMS =================
// Fetch sales in the date range
$sales = $conn->query("
 SELECT s.*, g.name AS guest_name, s.created_by
    FROM sales s
    LEFT JOIN guests g ON s.customer_id=g.id
    WHERE s.sales_date BETWEEN '{$conn->real_escape_string($from_date)}' 
                          AND '{$conn->real_escape_string($to_date)}'
      AND rest=0
    ORDER BY s.sales_date DESC
");

// Analytics initialization
$analytics = [
    'total_sales' => 0,
    'total_amount' => 0,
    'total_paid' => 0,
    'total_balance' => 0,
    'stock_discrepancies' => 0,
    'payment_discrepancies' => 0
];

$sale_data = [];
$daily_stats = [];

while($s = $sales->fetch_assoc()){
    // Fetch sale items for this sale
    $items = $conn->query("
        SELECT si.*, p.name AS product_name
        FROM sale_items si
        LEFT JOIN products p ON si.product_id = p.id
        WHERE si.sale_id = {$s['id']}
    ");

    $item_list = [];
    $sale_total = 0;

    while($item = $items->fetch_assoc()){
        $sale_total += $item['price'] * $item['qty'];

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

        $item_list[] = [
            'id' => $item['id'],
            'product_id' => $item['product_id'],
            'product_name' => $item['product_name'],
            'menu_id' => $item['menu_id'],
            'qty' => $item['qty'],
            'price' => $item['price'],
            'available' => $available,
            'discrepancy' => $discrepancy,
            'created_by' => $item['created_by']
        ];

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

    // Payments for this sale
    $paid = floatval($conn->query("
        SELECT COALESCE(SUM(amount),0) AS total_paid 
        FROM payments 
        WHERE id={$s['id']} AND status='Paid'
    ")->fetch_assoc()['total_paid']);

    $balance = $sale_total - $paid;
    if($balance != 0) $analytics['payment_discrepancies']++;

    // Update analytics
    $analytics['total_sales']++;
    $analytics['total_amount'] += $sale_total;
    $analytics['total_paid'] += $paid;
    $analytics['total_balance'] += $balance;

    $sale_data[] = array_merge($s, ['paid'=>$paid,'balance'=>$balance,'items'=>$item_list]);

    // Daily sales
    $day = date('Y-m-d', strtotime($s['sales_date']));
    if(!isset($daily_stats[$day])) $daily_stats[$day]=['sales'=>0];
    $daily_stats[$day]['sales'] += $sale_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">
    <button class="btn btn-secondary btn-custom" onclick="history.back()">
        <i class="fa fa-arrow-left"></i> Back
    </button>
<h3 class="text-center">🍹 Bar 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>
    <div class="col-md-2">
        <a href="reconcile_sales.php?from=<?= $from_date ?>&to=<?= $to_date ?>" class="btn btn-success w-100">Reconcile</a>
    </div>
</form>

<!-- Summary Cards -->
<div class="row mb-4">
    <div class="col-md-2"><div class="alert alert-success text-center"><strong>Total Sales:</strong><br><?= $analytics['total_sales'] ?></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>Discrepancies (Stock / Payment):</strong><br><?= $analytics['stock_discrepancies'].' / '.$analytics['payment_discrepancies'] ?></div></div>
</div>

<div class="col-md-2">
    <a href="export_bar_sales_analytics_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_bar_sales_analytics_pdf.php?from=<?= $from_date ?>&to=<?= $to_date ?>" class="btn btn-danger w-100">
        <i class="fa fa-file-pdf"></i> PDF
    </a>
</div>
<!-- Detailed Sales -->
<div class="table-responsive">
<table class="table table-bordered table-striped table-sm">
<thead class="bg-dark text-white">
<tr>
<th>#</th>
<th>Guest</th>
<th>Sale Date</th>
<th>Total</th>
<th>Paid</th>
<th>Balance</th>
<th>Items / Stock</th>
<th>Created By</th>
<th>Discrepancy</th>
</tr>
</thead>
<tbody>
<?php $i=1; foreach($sale_data as $s): ?>
<tr class="<?= $s['balance']>0?'table-warning':'table-success' ?>">
    <td><?= $i ?></td>
    <td><?= $s['guest_name'] ?></td>
    <td><?= date('Y-m-d H:i', strtotime($s['sales_date'])) ?></td>
    <td><?= number_format(array_sum(array_map(fn($it)=>$it['price']*$it['qty'],$s['items'])),2) ?></td>
    <td><?= number_format($s['paid'],2) ?></td>
    <td><?= number_format($s['balance'],2) ?></td>
    <td>
        <ul class="mb-0">
        <?php foreach($s['items'] as $it): ?>
            <li>
                <?= $it['product_name'] ?? ('Menu '.$it['menu_id']) ?> x <?= $it['qty'] ?> 
                (Stock: <?= $it['available'] ?>)
            </li>
        <?php endforeach; ?>
        </ul>
    </td>
    <td><?= $s['created_by'] ?></td>
    <td><?= ($s['balance']>0 || array_sum(array_column($s['items'],'discrepancy'))>0)?'⚠️':'✔️' ?></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'; ?>