<?php
// ─── GET /api/stock/booked-list.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();

$orders = $db->rows(
    "SELECT bs.*, t.name AS team_name
     FROM   book_stock bs
     LEFT JOIN teams t ON t.record_id = bs.team_assigned_id
     ORDER BY bs.record_id DESC
     LIMIT 300"
);

foreach ($orders as &$order) {
    $order['items'] = $db->rows(
        "SELECT bsl.*,
                s.item_price,
                COALESCE((SELECT SUM(quantity) FROM stock_trans
                  WHERE stock_no = bsl.stock_no AND status = 'RETURNED'
                  AND order_no = bsl.order_no), 0) AS returned_qty,
                COALESCE((SELECT SUM(quantity) FROM stock_trans
                  WHERE stock_no = bsl.stock_no AND status = 'USED'
                  AND order_no = bsl.order_no), 0) AS used_qty
         FROM   book_stock_list bsl
         LEFT JOIN stock s ON s.stock_no = bsl.stock_no
         WHERE  bsl.order_no = ?",
        [$order['order_no']]
    );
    $used_value = 0;
    foreach ($order['items'] as &$item) {
        $item['outstanding'] = max(0, (int)$item['amount'] - (int)$item['returned_qty'] - (int)$item['used_qty']);
        $used_value += (float)($item['item_price'] ?? 0) * (int)$item['used_qty'];
    }
    unset($item);
    $order['used_value']     = round($used_value, 2);
    $order['fully_returned'] = array_sum(array_column($order['items'], 'outstanding')) === 0;
}
unset($order, $item);

Response::ok($orders);