<?php
include 'db.php';

$department_id = 1; // Bar

// =================== Filters ===================
$search = $_GET['search'] ?? '';
$status_filter = $_GET['status'] ?? '';
$from_date = $_GET['from_date'] ?? date('Y-m-d'); 
$to_date = $_GET['to_date'] ?? date('Y-m-d');

$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);

// Output Excel headers
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=bar_inventory.xls");
header("Pragma: no-cache");
header("Expires: 0");

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

// Add column headers
fputcsv($output, ['ID','SKU','Product','Price','Remaining Qty','Reorder Level','Status'], "\t");

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

    // Use tab-delimited values for Excel
    fputcsv($output, [
        $row['id'],
        $row['sku'],
        $row['name'],
        $row['selling_price'],
        $qty,
        $reorder_level,
        $status
    ], "\t");
}

fclose($output);
exit;