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

$product_id = (int)($_GET['product_id'] ?? 0);

// Total available qty from finalized purchase batches (FIFO: oldest first)
$batchInfo = $conn->query("
    SELECT pi.id AS purchase_item_id, pi.batch_no, pi.gst_percent, pi.mrp
    FROM purchase_items pi
    JOIN purchase_orders po ON po.id = pi.purchase_order_id
    WHERE pi.product_id = $product_id
      AND pi.available_qty > 0
      AND po.status = 'final'
    ORDER BY pi.created_at ASC
    LIMIT 1
")->fetch_assoc();

$totalAvail = (float)$conn->query("
    SELECT COALESCE(SUM(pi.available_qty), 0) AS total
    FROM purchase_items pi
    JOIN purchase_orders po ON po.id = pi.purchase_order_id
    WHERE pi.product_id = $product_id
      AND pi.available_qty > 0
      AND po.status = 'final'
")->fetch_assoc()['total'];

// Selling price: max of product master vs last actual sale
$productRow = $conn->query("
    SELECT selling_price FROM products WHERE id = $product_id LIMIT 1
")->fetch_assoc();
$lastRow = $conn->query("
    SELECT selling_price_incl FROM sales_items WHERE product_id = $product_id ORDER BY id DESC LIMIT 1
")->fetch_assoc();

$productSP = (float)($productRow['selling_price'] ?? 0);
$lastSP    = (float)($lastRow['selling_price_incl'] ?? 0);
$bestPrice = max($productSP, $lastSP);

header('Content-Type: application/json');
echo json_encode([
    'total_avail'       => $totalAvail,
    'mrp'               => (float)($batchInfo['mrp'] ?? 0),
    'gst_percent'       => (float)($batchInfo['gst_percent'] ?? 0),
    'last_price'        => $bestPrice,
    'purchase_item_id'  => (int)($batchInfo['purchase_item_id'] ?? 0),
    'batch_no'          => $batchInfo['batch_no'] ?? '',
]);
