<?php
include 'includes/auth.php';
include 'db.php';
require_once('tcpdf-main/tcpdf.php'); // adjust path if needed

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

// Create PDF
$pdf = new TCPDF();
$pdf->SetCreator('GiantPlus');
$pdf->SetTitle("Sales Reconciliation: $from_date to $to_date");
$pdf->AddPage();
$pdf->SetFont('helvetica', '', 10);

$html = '<h3>🍹 Restaurant & Bar Sales Reconciliation</h3>';
$html .= "<p>From: $from_date &nbsp;&nbsp; To: $to_date</p>";

$html .= '<table border="1" cellpadding="4">
<thead>
<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>';

// 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()){
    $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) ? '⚠️' : '✔️';

    $html .= "<tr>
        <td>$i</td>
        <td>{$s['guest_name']}</td>
        <td>{$s['sales_date']}</td>
        <td>".number_format($sale_total,2)."</td>
        <td>".number_format($paid,2)."</td>
        <td>".number_format($balance,2)."</td>
        <td>".implode("<br>", $item_list)."</td>
        <td>{$s['created_by_name']}</td>
        <td>$discrepancy</td>
    </tr>";
    $i++;
}

$html .= '</tbody></table>';

// Output PDF
$pdf->writeHTML($html,true,false,true,false,'');
$pdf->Output("sales_reconciliation_{$from_date}_to_{$to_date}.pdf",'D');
exit;
?>