<?php
require_once __DIR__ . '/../config.php';
requireLogin();

$category = $_GET['category'] ?? null;
$itemCode = $_GET['item_code'] ?? null;
$warehouse = $_GET['warehouse'] ?? null;
$lowStockOnly = $_GET['low_stock'] ?? null;

$pdo = getDBConnection();
$sql = "SELECT i.ItemCode, i.ItemName, i.Category, i.UnitOfMeasure, i.ReorderLevel, i.UnitCost,
              COALESCE(SUM(t.quantity_in), 0) - COALESCE(SUM(t.quantity_out), 0) as StockOnHand,
              (COALESCE(SUM(t.quantity_in), 0) - COALESCE(SUM(t.quantity_out), 0)) * i.UnitCost as StockValue,
              COALESCE(AVG(t.unit_cost), i.UnitCost) as AvgCost
       FROM items_master i
       LEFT JOIN inventory_transaction t ON i.ItemCode = t.item_code
       WHERE 1=1";
$params = [];

if ($category) {
    $sql .= " AND i.Category = ?";
    $params[] = $category;
}
if ($itemCode) {
    $sql .= " AND i.ItemCode = ?";
    $params[] = $itemCode;
}
if ($warehouse) {
    $sql .= " AND t.warehouse = ?";
    $params[] = $warehouse;
}

$sql .= " GROUP BY i.ItemCode";

if ($lowStockOnly) {
    $sql .= " HAVING StockOnHand < i.ReorderLevel OR (StockOnHand < 0 AND i.ReorderLevel > 0)";
}

$sql .= " ORDER BY i.Category, i.ItemName";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$data = $stmt->fetchAll();

$summary = [
    'total_items' => count($data),
    'total_value' => array_sum(array_column($data, 'StockValue')),
    'total_soh' => array_sum(array_column($data, 'StockOnHand')),
    'low_stock_count' => count(array_filter($data, fn($i) => $i['StockOnHand'] < $i['ReorderLevel']))
];

jsonResponse(['data' => $data, 'summary' => $summary]);
?>