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

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

$pid = (int)($_GET['pid'] ?? 0);
if ($pid <= 0) { echo json_encode(['error' => 'Invalid product']); exit; }

$prod = $conn->query("SELECT id, name, category, brand, hsn_code, unit FROM products WHERE id = $pid LIMIT 1")->fetch_assoc();
if (!$prod) { echo json_encode(['error' => 'Product not found']); exit; }

/* Summary — totals */
$sumRow = $conn->query("
    SELECT
        COALESCE(SUM(pi.quantity), 0)      AS total_purchased,
        COALESCE(SUM(pi.available_qty), 0) AS available
    FROM purchase_items pi
    JOIN purchase_orders po ON po.id = pi.purchase_order_id
    WHERE pi.product_id = $pid AND po.status = 'final'
")->fetch_assoc();

$soldRow = $conn->query("
    SELECT COALESCE(SUM(sit.sold_qty), 0) AS total_sold
    FROM sales_items sit
    JOIN sales_invoices si ON si.id = sit.sales_invoice_id
    WHERE sit.product_id = $pid AND si.status = 'final'
")->fetch_assoc();

/* Last purchase price incl. GST */
$lastPurRow = $conn->query("
    SELECT ROUND(pi.purchase_price * (1 + pi.gst_percent / 100), 2) AS last_purchase_price_incl
    FROM purchase_items pi
    JOIN purchase_orders po ON po.id = pi.purchase_order_id
    WHERE pi.product_id = $pid AND po.status = 'final'
    ORDER BY po.purchase_date DESC, pi.id DESC
    LIMIT 1
")->fetch_assoc();

/* Last selling price incl. GST */
$lastSaleRow = $conn->query("
    SELECT sit.selling_price_incl AS last_selling_price_incl
    FROM sales_items sit
    JOIN sales_invoices si ON si.id = sit.sales_invoice_id
    WHERE sit.product_id = $pid AND si.status = 'final'
    ORDER BY si.invoice_date DESC, si.id DESC
    LIMIT 1
")->fetch_assoc();

$summary = [
    'total_purchased'         => (int)$sumRow['total_purchased'],
    'total_sold'              => (int)$soldRow['total_sold'],
    'available'               => (int)$sumRow['available'],
    'last_purchase_price_incl'=> $lastPurRow  ? (float)$lastPurRow['last_purchase_price_incl']   : null,
    'last_selling_price_incl' => $lastSaleRow ? (float)$lastSaleRow['last_selling_price_incl']    : null,
];

/* All batches (including sold-out), purchase price incl. GST */
$batchRes = $conn->query("
    SELECT
        po.id AS purchase_order_id,
        pi.batch_no,
        COALESCE(v.name, '—') AS vendor_name,
        DATE_FORMAT(po.purchase_date, '%d-%m-%Y') AS purchase_date,
        pi.quantity,
        pi.available_qty,
        pi.gst_percent,
        ROUND(pi.purchase_price * (1 + pi.gst_percent / 100), 2) AS purchase_price_incl,
        ROUND(pi.mrp, 2) AS mrp,
        pi.expiry_date
    FROM purchase_items pi
    JOIN purchase_orders po ON po.id = pi.purchase_order_id
    LEFT JOIN vendors v ON v.id = po.vendor_id
    WHERE pi.product_id = $pid AND po.status = 'final'
    ORDER BY po.purchase_date DESC, pi.id DESC
");
$batches = [];
while ($b = $batchRes->fetch_assoc()) {
    if ($b['expiry_date']) {
        $b['expiry_date'] = date('d-m-Y', strtotime($b['expiry_date']));
    }
    $batches[] = $b;
}

/* Recent sales (last 50), tax-inclusive prices */
$salesRes = $conn->query("
    SELECT
        si.id AS sales_invoice_id,
        DATE_FORMAT(si.invoice_date, '%d-%m-%Y') AS invoice_date,
        si.invoice_no,
        COALESCE(cl.name, '—') AS customer_name,
        sit.sold_qty,
        sit.selling_price_incl,
        ROUND(sit.sold_qty * sit.selling_price_incl, 2) AS line_total
    FROM sales_items sit
    JOIN sales_invoices si ON si.id = sit.sales_invoice_id
    LEFT JOIN clients cl ON cl.id = si.client_id
    WHERE sit.product_id = $pid AND si.status = 'final'
    ORDER BY si.invoice_date DESC, si.id DESC
    LIMIT 50
");
$sales = [];
while ($s = $salesRes->fetch_assoc()) $sales[] = $s;

echo json_encode([
    'product' => $prod,
    'summary' => $summary,
    'batches' => $batches,
    'sales'   => $sales,
]);
