<?php
require_once '../includes/auth.php';
require_once '../db.php';

if (!hasPermission('master_sales')) { echo json_encode([]); exit; }

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

$q   = trim($_GET['q']   ?? '');
$pid = intval($_GET['pid'] ?? 0);

// Must have either a search query or a product_id
if ($pid <= 0 && strlen($q) < 1) { echo json_encode([]); exit; }

if ($pid > 0) {
    // Specific product — return ALL batches (used when changing batch on a row)
    $productCond = "AND p.id = $pid";
    $fifoFilter  = "";
    $orderBy     = "ORDER BY po.purchase_date ASC, pi.id ASC";
    $limit       = "LIMIT 60";
} else {
    // Name search — return only the FIFO batch (oldest purchase) per product
    $like        = $conn->real_escape_string($q);
    $productCond = "AND p.name LIKE '%$like%'";
    $fifoFilter  = "AND pi.id = (
        SELECT pi2.id
        FROM purchase_items pi2
        JOIN purchase_orders po2 ON po2.id = pi2.purchase_order_id
        WHERE pi2.product_id = p.id AND pi2.available_qty > 0
        ORDER BY po2.purchase_date ASC, pi2.id ASC
        LIMIT 1
    )";
    $orderBy     = "ORDER BY p.name";
    $limit       = "LIMIT 30";
}

$sql = "
    SELECT
        pi.id          AS purchase_item_id,
        pi.batch_no,
        pi.available_qty,
        pi.mrp,
        pi.selling_price,
        pi.gst_percent,
        pi.purchase_price,
        po.company_id,
        cm.company_name,
        cm.invoice_prefix,
        p.id           AS product_id,
        p.name         AS product_name,
        p.unit
    FROM purchase_items pi
    JOIN purchase_orders po ON po.id = pi.purchase_order_id
    JOIN company_master   cm ON cm.id = po.company_id
    JOIN products         p  ON p.id  = pi.product_id
    WHERE pi.available_qty > 0
      AND p.status = 1
      $productCond
      $fifoFilter
    $orderBy
    $limit
";

$res = $conn->query($sql);
$rows = [];
$productIds = [];
while ($row = $res->fetch_assoc()) {
    $rows[] = $row;
    $productIds[] = (int)$row['product_id'];
}

// Fetch last selling price from sales_items for each product (single batched query)
$lastPrices = [];
if (!empty($productIds)) {
    $inList = implode(',', array_unique($productIds));
    $lpRes = $conn->query("
        SELECT si.product_id, si.selling_price_incl
        FROM sales_items si
        INNER JOIN (
            SELECT product_id, MAX(id) AS max_id
            FROM sales_items
            WHERE product_id IN ($inList)
            GROUP BY product_id
        ) latest ON latest.product_id = si.product_id AND latest.max_id = si.id
    ");
    while ($lp = $lpRes->fetch_assoc()) {
        $lastPrices[(int)$lp['product_id']] = (float)$lp['selling_price_incl'];
    }
}

$data = [];
foreach ($rows as $row) {
    $pid = (int)$row['product_id'];
    $purchase_price_incl = round(
        floatval($row['selling_price']) * (1 + floatval($row['gst_percent']) / 100), 2
    );
    $last_sale_price = $lastPrices[$pid] ?? 0;
    $selling_price_incl = max($purchase_price_incl, $last_sale_price);

    $data[] = [
        'purchase_item_id'  => (int)$row['purchase_item_id'],
        'product_id'        => $pid,
        'product_name'      => $row['product_name'],
        'unit'              => $row['unit'],
        'batch_no'          => $row['batch_no'],
        'available_qty'     => (float)$row['available_qty'],
        'mrp'               => (float)$row['mrp'],
        'selling_price'     => (float)$row['selling_price'],
        'selling_price_incl'=> $selling_price_incl,
        'gst_percent'       => (float)$row['gst_percent'],
        'purchase_price'    => (float)$row['purchase_price'],
        'company_id'        => (int)$row['company_id'],
        'company_name'      => $row['company_name'],
        'company_prefix'    => $row['invoice_prefix'] ?: strtoupper(substr($row['company_name'], 0, 3)),
    ];
}

echo json_encode($data);
