<?php
// POST /api/stock/list.php
require_once __DIR__ . '/../config/db.php';
require_once __DIR__ . '/../config/auth.php';

$user = requireAuth();
$db   = getDB();
$p    = getPagination();

$search     = post('search', '');
$categoryId = (int)post('category_id', 0);
$lowStock   = post('low_stock', '');
$where      = ['si.is_active = 1'];
$params     = [];

if ($search)     { $where[] = '(si.name LIKE ? OR si.sku LIKE ?)'; $params[] = "%$search%"; $params[] = "%$search%"; }
if ($categoryId) { $where[] = 'si.category_id = ?'; $params[] = $categoryId; }

$whereStr = implode(' AND ', $where);

$countStmt = $db->prepare("SELECT COUNT(*) FROM stock_items si WHERE $whereStr");
$countStmt->execute($params);
$total = (int)$countStmt->fetchColumn();

$having = $lowStock ? 'HAVING total_qty <= si.min_qty' : '';

$stmt = $db->prepare("
    SELECT si.*,
           sc.name AS category_name,
           COALESCE(SUM(inv.quantity), 0) AS total_qty
    FROM stock_items si
    LEFT JOIN stock_categories sc ON sc.id = si.category_id
    LEFT JOIN stock_inventory inv ON inv.stock_item_id = si.id
    WHERE $whereStr
    GROUP BY si.id
    $having
    ORDER BY si.name ASC
    LIMIT {$p['limit']} OFFSET {$p['offset']}
");
$stmt->execute($params);

$items = $stmt->fetchAll();
foreach ($items as &$item) {
    // Get per-location breakdown
    $locStmt = $db->prepare("
        SELECT sl.name AS location_name, inv.quantity
        FROM stock_inventory inv
        JOIN stock_locations sl ON sl.id = inv.location_id
        WHERE inv.stock_item_id = ?
    ");
    $locStmt->execute([$item['id']]);
    $item['locations'] = $locStmt->fetchAll();
}

apiSuccess([
    'items'      => $items,
    'pagination' => ['total' => $total, 'page' => $p['page'], 'limit' => $p['limit'], 'pages' => ceil($total / $p['limit'])]
]);