<?php
include 'db.php';
require 'vendor/autoload.php';
use Dompdf\Dompdf;

$department_id = 1; // Bar

// =================== Filters ===================
$search = $_GET['search'] ?? '';
$status_filter = $_GET['status'] ?? '';

$where_inventory = "ps.department_id = $department_id";

if ($search) {
    $search_safe = $conn->real_escape_string($search);
    $where_inventory .= " AND (p.name LIKE '%$search_safe%' OR p.sku LIKE '%$search_safe%')";
}

if ($status_filter == 'low') 
    $where_inventory .= " AND ps.quantity <= IFNULL(ps.reorder_level,5) AND ps.quantity > 0";
elseif ($status_filter == 'out') 
    $where_inventory .= " AND ps.quantity = 0";

// ==================== Inventory Query ====================
$sql_inventory = "
SELECT p.id, p.name, p.sku, p.selling_price, 
       IFNULL(ps.quantity,0) as quantity,
       IFNULL(ps.reorder_level,5) as reorder_level
FROM products p
LEFT JOIN product_stock ps 
  ON p.id=ps.product_id 
  AND ps.department_id=$department_id
WHERE $where_inventory
ORDER BY p.name ASC
";

$result = $conn->query($sql_inventory);

// Build HTML for PDF
$html = '<h2>Bar Inventory Report</h2>';
$html .= '<table border="1" cellpadding="5" cellspacing="0" width="100%">';
$html .= '<thead><tr style="background:#ccc;">
<th>ID</th><th>SKU</th><th>Product</th><th>Price</th><th>Qty</th><th>Reorder Level</th><th>Status</th>
</tr></thead><tbody>';

while($row = $result->fetch_assoc()){
    $qty = intval($row['quantity']);
    $reorder_level = intval($row['reorder_level']);
    $status = ($qty==0) ? 'Out' : (($qty<=$reorder_level)?'Reorder':'OK');

    $html .= '<tr>
        <td>'.$row['id'].'</td>
        <td>'.$row['sku'].'</td>
        <td>'.$row['name'].'</td>
        <td>'.number_format($row['selling_price'],2).'</td>
        <td>'.$qty.'</td>
        <td>'.$reorder_level.'</td>
        <td>'.$status.'</td>
    </tr>';
}

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

// Render PDF
$dompdf = new Dompdf();
$dompdf->loadHtml($html);
$dompdf->setPaper('A4','landscape');
$dompdf->render();
$dompdf->stream("bar_inventory.pdf", ["Attachment" => false]); // false = open in browser
exit;