<?php
// ─── GET /api/stock/stats.php ────────────────────────────────────────────
define('ROOT', dirname(__DIR__, 2));
require_once ROOT . '/core/DB.php';
require_once ROOT . '/core/Response.php';
require_once ROOT . '/core/Auth.php';

Auth::require();
$db = DB::get();

// All stock items with live balance and price
$items = $db->rows(
    "SELECT s.record_id, s.stock_no, s.item_name, s.item_price,
            s.unit_of_measure, s.stock_type_id, s.date_time,
            st.name AS type_name,
            COALESCE((
              SELECT SUM(CASE
                WHEN t.status IN ('BOOKED OUT','USED') THEN -t.quantity
                ELSE t.quantity
              END)
              FROM stock_trans t WHERE t.stock_no = s.stock_no
            ), 0) AS balance
     FROM   stock s
     LEFT JOIN stock_types st ON st.record_id = s.stock_type_id
     ORDER BY st.name, s.item_name"
);

$total_items   = count($items);
$out_of_stock  = 0;
$low_stock     = 0;
$total_value   = 0.0;

foreach ($items as $item) {
    $bal = (float) $item['balance'];
    if ($bal <= 0)  $out_of_stock++;
    if ($bal > 0 && $bal <= 2) $low_stock++;
    if ($item['item_price'] && $bal > 0) {
        $total_value += (float) $item['item_price'] * $bal;
    }
}

Response::ok([
    'total_items'   => $total_items,
    'out_of_stock'  => $out_of_stock,
    'low_stock'     => $low_stock,
    'total_value'   => round($total_value, 2),
]);