<?php
require_once __DIR__ . '/../config/db.php';
require_once __DIR__ . '/../config/auth.php';
$user = requireAuth();
// Allow if Admin or has stock.reports permission
if ((int)$user['role_id'] !== 1) {
    try {
        $ps = getDB();
        $chk = $ps->prepare("SELECT allowed FROM role_permissions WHERE role_id=? AND module='stock' AND action='reports' LIMIT 1");
        $chk->execute([$user['role_id']]);
        $row = $chk->fetch();
        if (!$row || !(int)$row['allowed']) requireRole([]); // force 403
    } catch (Exception $e) {
        requireRole([1,2,5]); // fallback
    }
}
$db   = getDB();
$type = post('type', 'summary');

try {
    if ($type === 'summary') {
        $stmt = $db->query("
            SELECT si.id, si.name, si.sku, si.unit, si.unit_cost, si.min_qty,
                   sc.name AS category_name, si.item_type,
                   COALESCE(inv.total_qty, 0) AS qty_on_hand,
                   ROUND(COALESCE(inv.total_qty, 0) * COALESCE(si.unit_cost, 0), 2) AS stock_value,
                   CASE WHEN COALESCE(inv.total_qty, 0) <= si.min_qty THEN 1 ELSE 0 END AS is_low
            FROM stock_items si
            LEFT JOIN stock_categories sc ON sc.id = si.category_id
            LEFT JOIN (
                SELECT stock_item_id, SUM(quantity) AS total_qty
                FROM stock_inventory GROUP BY stock_item_id
            ) inv ON inv.stock_item_id = si.id
            WHERE si.is_active = 1
            ORDER BY sc.name, si.name
        ");
        $items      = $stmt->fetchAll();
        $totalValue = array_sum(array_column($items, 'stock_value'));
        $totalItems = count($items);
        $lowCount   = count(array_filter($items, fn($i) => $i['is_low']));
        apiSuccess(['items' => $items, 'total_value' => round($totalValue, 2), 'total_items' => $totalItems, 'low_stock_count' => $lowCount]);
    }

    if ($type === 'low_stock') {
        $stmt = $db->query("
            SELECT si.id, si.name, si.sku, si.unit, si.unit_cost, si.min_qty, sc.name AS category_name,
                   COALESCE(inv.total_qty, 0) AS qty_on_hand
            FROM stock_items si
            LEFT JOIN stock_categories sc ON sc.id = si.category_id
            LEFT JOIN (SELECT stock_item_id, SUM(quantity) AS total_qty FROM stock_inventory GROUP BY stock_item_id) inv ON inv.stock_item_id = si.id
            WHERE si.is_active = 1 AND COALESCE(inv.total_qty, 0) <= si.min_qty
            ORDER BY qty_on_hand ASC
        ");
        apiSuccess(['items' => $stmt->fetchAll()]);
    }

    if ($type === 'movements') {
        $p      = getPagination();
        $itemId = post('item_id') ? (int)post('item_id') : null;
        $where  = $itemId ? 'WHERE st.stock_item_id = ?' : 'WHERE 1=1';
        $params = $itemId ? [$itemId] : [];
        $stmt   = $db->prepare("
            SELECT st.*, si.name AS item_name, si.unit, u.full_name AS user_name,
                   jc.job_number, p.name AS project_name
            FROM stock_transactions st
            JOIN stock_items si ON si.id = st.stock_item_id
            LEFT JOIN users u ON u.id = st.user_id
            LEFT JOIN job_cards jc ON jc.id = st.job_card_id
            LEFT JOIN projects p ON p.id = st.project_id
            $where ORDER BY st.transaction_date DESC
            LIMIT {$p['limit']} OFFSET {$p['offset']}
        ");
        $stmt->execute($params);
        apiSuccess(['movements' => $stmt->fetchAll()]);
    }

    apiError('Unknown report type.', 400);
} catch (PDOException $e) {
    apiError('Database error: ' . $e->getMessage(), 500);
}