<?php
require_once __DIR__ . '/../config/db.php';
require_once __DIR__ . '/../config/auth.php';
requireAuth();
$db = getDB();

$page   = max(1, (int)post('page', 1));
$limit  = 30;
$offset = ($page - 1) * $limit;

$search  = post('search', '');
$type    = post('type', '');
$itemId  = (int)post('item_id', 0);
$dateFrom = post('date_from', '');
$dateTo   = post('date_to', '');

$where  = ['1=1'];
$params = [];

if ($search) {
    $where[] = '(si.name LIKE ? OR st.reference_no LIKE ? OR st.supplier LIKE ? OR u.full_name LIKE ?)';
    $params  = array_merge($params, ["%$search%", "%$search%", "%$search%", "%$search%"]);
}
if ($type)     { $where[] = 'st.transaction_type = ?'; $params[] = $type; }
if ($itemId)   { $where[] = 'st.stock_item_id = ?';    $params[] = $itemId; }
if ($dateFrom) { $where[] = 'DATE(st.transaction_date) >= ?'; $params[] = $dateFrom; }
if ($dateTo)   { $where[] = 'DATE(st.transaction_date) <= ?'; $params[] = $dateTo; }

$whereStr = implode(' AND ', $where);

$total = (int)$db->prepare("SELECT COUNT(*) FROM stock_transactions st JOIN stock_items si ON si.id=st.stock_item_id JOIN users u ON u.id=st.user_id WHERE $whereStr")->execute($params) ? $db->prepare("SELECT COUNT(*) FROM stock_transactions st JOIN stock_items si ON si.id=st.stock_item_id JOIN users u ON u.id=st.user_id WHERE $whereStr")->execute($params) : 0;

$cStmt = $db->prepare("SELECT COUNT(*) FROM stock_transactions st JOIN stock_items si ON si.id=st.stock_item_id JOIN users u ON u.id=st.user_id WHERE $whereStr");
$cStmt->execute($params);
$total = (int)$cStmt->fetchColumn();

$stmt = $db->prepare("
    SELECT st.*,
           si.name AS item_name, si.sku, si.unit,
           u.full_name AS recorded_by,
           jc.job_number, jc.title AS jc_title,
           p.name AS project_name
    FROM stock_transactions st
    JOIN stock_items si ON si.id = st.stock_item_id
    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 $whereStr
    ORDER BY st.transaction_date DESC
    LIMIT $limit OFFSET $offset
");
$stmt->execute($params);

apiSuccess([
    'transactions' => $stmt->fetchAll(),
    'pagination'   => ['total'=>$total,'page'=>$page,'limit'=>$limit,'pages'=>(int)ceil($total/$limit)]
]);