 <?php
// include "config.php";

// $offset = isset($_GET['offset']) ? (int)$_GET['offset'] : 0;
// $limit = isset($_GET['limit']) ? (int)$_GET['limit'] : 20;

// $productQuery = "SELECT p.*, c.category_name FROM tbl_stock_items p 
//                  LEFT JOIN tbl_categories c ON p.category = c.id 
//                  ORDER BY p.id DESC 
//                  LIMIT ?, ?";
// $stmt = $con->prepare($productQuery);
// $stmt->bind_param("ii", $offset, $limit);
// $stmt->execute();
// $result = $stmt->get_result();

// $products = [];
// while ($row = $result->fetch_assoc()) {
//     $products[] = $row;
// }

// $stmt->close();
// echo json_encode($products);
 ?>

<?php
include "config.php";

// ini_set('display_errors', 1);
// ini_set('display_startup_errors', 1);
// error_reporting(E_ALL);

$offset = isset($_GET['offset']) ? (int)$_GET['offset'] : 0;
$limit = isset($_GET['limit']) ? (int)$_GET['limit'] : 20;
$category = isset($_GET['category']) ? $_GET['category'] : '';
$search = isset($_GET['search']) ? $_GET['search'] : '';

$whereClauses = [];
$params = [];
$types = '';

if ($category !== '') {
    $whereClauses[] = "p.category = ?";
    $params[] = $category;
    $types .= 'i';
}

if ($search !== '') {
    $whereClauses[] = "(p.item_name LIKE ? OR c.category_name LIKE ? OR p.item_code LIKE ? OR p.p_code LIKE ?)";
    $searchParam = "%$search%";
    $params[] = $searchParam;
    $params[] = $searchParam;
    $params[] = $searchParam;
    $params[] = $searchParam;
    $types .= 'ssss';
}

$whereSQL = '';
if (count($whereClauses) > 0) {
    $whereSQL = 'WHERE ' . implode(' AND ', $whereClauses);
}

$sql = "SELECT p.*, c.category_name FROM tbl_stock_items p 
        LEFT JOIN tbl_categories c ON p.category = c.id
        $whereSQL
        ORDER BY p.item_name ASC
        LIMIT ?, ?";

$stmt = $con->prepare($sql);
if (!$stmt) {
    die("Prepare failed: (" . $con->errno . ") " . $con->error);
}

// Add offset and limit params
$types .= 'ii';
$params[] = $offset;
$params[] = $limit;

// bind_param requires variables, so use call_user_func_array
// Prepare references for bind_param
$bind_names[] = $types;
for ($i=0; $i<count($params); $i++) {
    $bind_name = 'bind' . $i;
    $$bind_name = $params[$i];
    $bind_names[] = &$$bind_name;
}

call_user_func_array([$stmt, 'bind_param'], $bind_names);

$stmt->execute();
$result = $stmt->get_result();

$products = [];
while ($row = $result->fetch_assoc()) {
    $products[] = $row;
}

header('Content-Type: application/json');
echo json_encode($products);
?>

