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

$from_date = $_GET['from'] ?? date('Y-m-01');
$to_date   = $_GET['to'] ?? date('Y-m-d');

// Set headers for download
header("Content-Type: application/csv");
header("Content-Disposition: attachment; filename=sales_reconciliation_{$from_date}_to_{$to_date}.csv");
header("Pragma: no-cache");
header("Expires: 0");

// Open output stream
$output = fopen("php://output", "w");

// Column headers
fputcsv($output, ['#', 'Guest', 'Sale Date', 'Total', 'Paid', 'Balance', 'Items / Stock', 'Created By', 'Discrepancy']);

// Fetch sales
$sales = $conn->query("
    SELECT s.*, g.name AS guest_name, u.username AS created_by_name
    FROM sales s
    LEFT JOIN guests g ON s.customer_id=g.id
    LEFT JOIN users u ON s.created_by=u.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
");

$i = 1;
while($s = $sales->fetch_assoc()){
    // Fetch sale items
    $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'];
        $item_list[] = ($item['product_name'] ?? 'Menu '.$item['menu_id']) . ' x ' . $item['qty'] . ' (Stock: ' . ($item['quantity'] ?? '-') . ')';
    }

    $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;
    $discrepancy = ($balance > 0) ? '⚠️' : '✔️';

    fputcsv($output, [
        $i++,
        $s['guest_name'],
        $s['sales_date'],
        number_format($sale_total,2),
        number_format($paid,2),
        number_format($balance,2),
        implode("; ", $item_list),
        $s['created_by_name'],
        $discrepancy
    ]);
}
fclose($output);
exit;
?>