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

// ================= GET FILTERS =================
$search = $_GET['search'] ?? '';
$category = $_GET['category'] ?? '';
$lowstock = $_GET['lowstock'] ?? '';

// ================= BUILD QUERY =================
$where = "1";

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

if($category){
    $category_safe = $conn->real_escape_string($category);
    $where .= " AND c.name = '$category_safe'";
}

if($lowstock){
    if($lowstock=='Low'){
        $where .= " AND COALESCE(SUM(ps.quantity),0) <= p.min_level";
    } else {
        $where .= " AND COALESCE(SUM(ps.quantity),0) > p.min_level";
    }
}

// ================= FETCH DATA =================
$sql = "
SELECT p.sku, p.name, c.name AS category_name, COALESCE(SUM(ps.quantity),0) AS total_qty,
       p.min_level, p.buying_price, p.selling_price, (p.selling_price - p.buying_price) AS profit,
       (p.selling_price * COALESCE(SUM(ps.quantity),0)) AS total_value
FROM products p
LEFT JOIN categories c ON p.category_id=c.id
LEFT JOIN product_stock ps ON p.id = ps.product_id
WHERE $where
GROUP BY p.id
ORDER BY p.name ASC
";

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

// ================= SEND HEADERS =================
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=inventory_".date('Ymd_His').".xls");
header("Pragma: no-cache");
header("Expires: 0");

// ================= PRINT CSV =================
echo "SKU\tName\tCategory\tQty\tMin Level\tCP\tSP\tProfit\tTotal Value\n";

while($row = $result->fetch_assoc()){
    echo "{$row['sku']}\t{$row['name']}\t{$row['category_name']}\t{$row['total_qty']}\t{$row['min_level']}\t{$row['buying_price']}\t{$row['selling_price']}\t{$row['profit']}\t{$row['total_value']}\n";
}
exit;