<?php
// ─── GET /api/stock/report.php ───────────────────────────────────────────
// Returns stock data filtered for report generation.
// Filters: type_id, status (all|in_stock|out_of_stock|low_stock),
//          date_from, date_to (based on date_time created)
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();

$type_id   = trim($_GET['type_id']   ?? '');
$status    = trim($_GET['status']    ?? 'all');
$date_from = trim($_GET['date_from'] ?? '');
$date_to   = trim($_GET['date_to']   ?? '');

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

if ($type_id)   { $where[] = 's.stock_type_id = ?'; $params[] = (int) $type_id; }
if ($date_from) { $where[] = 'DATE(s.date_time) >= ?'; $params[] = $date_from; }
if ($date_to)   { $where[] = 'DATE(s.date_time) <= ?'; $params[] = $date_to; }

$rows = $db->rows(
    "SELECT s.*,
            st.name  AS type_name,
            sp.name  AS supplier_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
     LEFT JOIN suppliers   sp ON sp.record_id = s.supplier_id
     WHERE  " . implode(' AND ', $where) . "
     ORDER BY st.name, s.item_name",
    $params
);

// Apply balance-based status filter
$rows = array_values(array_filter($rows, function($r) use ($status) {
    $bal = (float) $r['balance'];
    return match($status) {
        'in_stock'    => $bal > 0,
        'out_of_stock'=> $bal <= 0,
        'low_stock'   => $bal > 0 && $bal <= 2,
        default       => true,
    };
}));

// Totals
$total_value = 0;
$total_qty   = 0;
foreach ($rows as $r) {
    $bal = (float) $r['balance'];
    $total_qty   += $bal;
    if ($r['item_price'] && $bal > 0) {
        $total_value += (float) $r['item_price'] * $bal;
    }
}

Response::ok([
    'rows'        => $rows,
    'count'       => count($rows),
    'total_qty'   => $total_qty,
    'total_value' => round($total_value, 2),
    'generated'   => date('Y-m-d H:i:s'),
]);