<?php
include 'db.php';

$q = $_GET['q'] ?? '';
$search = '%' . $q . '%';

// Enhanced query to get more detailed item information
$stmt = $conn->prepare("
  SELECT 
    i.id, 
    i.name,
    i.item_code,
    i.item_type,
    i.cost_price,
    i.retail_price,
    COALESCE(tr.rate, 0) AS tax_rate,
    COALESCE(
      (SELECT SUM(CASE WHEN im.movement_type = 'in' THEN im.quantity ELSE 0 END) -
              SUM(CASE WHEN im.movement_type = 'out' THEN im.quantity ELSE 0 END) +
              SUM(CASE WHEN im.movement_type = 'adjustment' THEN im.quantity ELSE 0 END)
       FROM inventory_movements im 
       WHERE im.item_id = i.id
      ), 0
    ) as current_stock
  FROM items i
  LEFT JOIN tax_rates tr ON i.tax_rate_id = tr.id
  WHERE i.name LIKE ? OR i.item_code LIKE ? OR i.sku_number LIKE ?
  ORDER BY i.name
  LIMIT 10
");

$stmt->bind_param("sss", $search, $search, $search);
$stmt->execute();
$result = $stmt->get_result();

// Return results as JSON array
$items = [];
while ($row = $result->fetch_assoc()) {
    $items[] = [
        'id' => $row['id'],
        'name' => $row['name'],
        'item_code' => $row['item_code'],
        'item_type' => $row['item_type'],
        'cost_price' => $row['cost_price'],
        'retail_price' => $row['retail_price'],
        'tax_rate' => $row['tax_rate'],
        'current_stock' => $row['current_stock']
    ];
}

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