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

// ===================== FILTER INPUTS =====================
$from_date = $_GET['from_date'] ?? '';
$to_date   = $_GET['to_date'] ?? '';
$category  = $_GET['category'] ?? '';
$movement  = $_GET['movement'] ?? '';

// ===================== BUILD WHERE =====================
$where = [];

if($category){
    $where[] = "p.category_id = ".intval($category);
}

// movement date filter
$movement_where = '';
if($movement == 'added'){
    $movement_where = "AND ps.created_at BETWEEN '".($from_date ?: '1970-01-01')."' 
                       AND '".($to_date ?: date('Y-m-d'))."'";
} elseif($movement == 'dispatched'){
    $movement_where = "AND ps.dispatched_at BETWEEN '".($from_date ?: '1970-01-01')."' 
                       AND '".($to_date ?: date('Y-m-d'))."'";
}

// ===================== QUERY =====================
$sql = "
    SELECT 
        p.sku,
        p.name,
        c.name AS category_name,
        COALESCE(SUM(ps.quantity),0) AS total_qty,
        p.min_level,
        p.selling_price,
        (p.selling_price * COALESCE(SUM(ps.quantity),0)) AS total_value,
        MAX(ps.created_at) AS last_date
    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 1
    ".(!empty($where) ? " AND ".implode(" AND ", $where) : "")."
    $movement_where
    GROUP BY p.id
    ORDER BY p.name ASC
";

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

// ===================== CSV HEADERS =====================
$filename = "inventory_report_" . date('Ymd_His') . ".csv";

header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="'.$filename.'"');

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

// CSV column headers
fputcsv($output, [
    'SKU',
    'Product Name',
    'Category',
    'Quantity',
    'Min Level',
    'Selling Price',
    'Total Value',
    'Low Stock',
    'Last Date'
]);

// ===================== DATA =====================
while($row = $result->fetch_assoc()){

    $low_stock = ($row['total_qty'] <= $row['min_level']) ? 'Yes' : 'No';

    fputcsv($output, [
        $row['sku'],
        $row['name'],
        $row['category_name'],
        $row['total_qty'],
        $row['min_level'],
        number_format($row['selling_price'],2),
        number_format($row['total_value'],2),
        $low_stock,
        $row['last_date']
    ]);
}

fclose($output);
exit;
?>