<?php
// search_transactions.php — paginated, filtered transaction search

require 'auth_check.php';
require_once 'db.php';

header('Content-Type: application/json');

$item       = '%' . trim($_GET['item']       ?? '') . '%';
$department = '%' . trim($_GET['department'] ?? '') . '%';
$vendor     = '%' . trim($_GET['vendor']     ?? '') . '%';
$date_from  = $_GET['date_from'] ?? '';
$date_to    = $_GET['date_to']   ?? '';
$page       = max(1, (int)($_GET['page'] ?? 1));
$per_page   = 50;
$offset     = ($page - 1) * $per_page;

// Whitelist sort columns and directions to prevent SQL injection
$allowed_sorts = ['inv_date', 'item', 'department', 'vendor', 'price'];
$allowed_dirs  = ['asc', 'desc'];
$sort = in_array($_GET['sort'] ?? '', $allowed_sorts) ? $_GET['sort'] : 'inv_date';
$dir  = in_array(strtolower($_GET['dir'] ?? ''), $allowed_dirs) ? strtolower($_GET['dir']) : 'desc';

// Build dynamic WHERE clauses
$where  = "WHERE item LIKE :item AND department LIKE :department AND vendor LIKE :vendor";
$params = [':item' => $item, ':department' => $department, ':vendor' => $vendor];

if ($date_from !== '') {
    $where .= ' AND inv_date >= :date_from';
    $params[':date_from'] = $date_from;
}
if ($date_to !== '') {
    $where .= ' AND inv_date <= :date_to';
    $params[':date_to'] = $date_to;
}

// Total count (for pagination controls)
$countStmt = $pdo->prepare("SELECT COUNT(*) FROM transactions $where");
$countStmt->execute($params);
$total = (int)$countStmt->fetchColumn();

// Data page
$dataStmt = $pdo->prepare("
    SELECT id, inv_date, item, department, vendor, unit, price, terms
    FROM transactions
    $where
    ORDER BY `$sort` $dir
    LIMIT :limit OFFSET :offset
");
// PDO needs integer binding for LIMIT/OFFSET
foreach ($params as $k => $v) $dataStmt->bindValue($k, $v);
$dataStmt->bindValue(':limit',  $per_page, PDO::PARAM_INT);
$dataStmt->bindValue(':offset', $offset,   PDO::PARAM_INT);
$dataStmt->execute();
$rows = $dataStmt->fetchAll();

echo json_encode([
    'rows'       => $rows,
    'total'      => $total,
    'page'       => $page,
    'per_page'   => $per_page,
    'total_pages' => (int)ceil($total / $per_page),
]);
