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

$status    = trim($_GET['status']    ?? 'all');   // all | open | closed
$team_id   = trim($_GET['team_id']   ?? '');
$date_from = trim($_GET['date_from'] ?? '');
$date_to   = trim($_GET['date_to']   ?? '');

$where  = ['1=1'];
$params = [];

if ($status === 'open')   { $where[] = "(bs.status IS NULL OR bs.status = '0')"; }
if ($status === 'closed') { $where[] = "bs.status = 'returned'"; }
if ($team_id)             { $where[] = 'bs.team_assigned_id = ?'; $params[] = (int) $team_id; }
if ($date_from)           { $where[] = 'DATE(bs.booking_date) >= ?'; $params[] = $date_from; }
if ($date_to)             { $where[] = 'DATE(bs.booking_date) <= ?'; $params[] = $date_to; }

$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
     WHERE  " . implode(' AND ', $where) . "
     ORDER BY bs.record_id DESC",
    $params
);

$total_booked_value = 0;
$total_used_value   = 0;
$total_orders       = count($orders);

foreach ($orders as &$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']]
    );

    $order_used_value   = 0;
    $order_booked_value = 0;

    foreach ($items as &$item) {
        $item['outstanding']  = max(0, (int)$item['amount'] - (int)$item['returned_qty'] - (int)$item['used_qty']);
        $price = (float)($item['item_price'] ?? 0);
        $item['used_value']   = $price * (int)$item['used_qty'];
        $item['booked_value'] = $price * (int)$item['amount'];
        $order_used_value   += $item['used_value'];
        $order_booked_value += $item['booked_value'];
    }
    unset($item);

    $order['items']              = $items;
    $order['used_value']         = round($order_used_value, 2);
    $order['booked_value']       = round($order_booked_value, 2);
    $order['fully_returned']     = array_sum(array_column($items, 'outstanding')) === 0;
    $total_used_value           += $order_used_value;
    $total_booked_value         += $order_booked_value;
}
unset($order);

Response::ok([
    'orders'             => $orders,
    'total_orders'       => $total_orders,
    'total_used_value'   => round($total_used_value, 2),
    'total_booked_value' => round($total_booked_value, 2),
    'generated'          => date('Y-m-d H:i:s'),
]);