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

$draw   = intval($_POST['draw'] ?? 1);
$start  = intval($_POST['start'] ?? 0);
$length = intval($_POST['length'] ?? 25);

$searchValue = $_POST['search']['value'] ?? '';

$orderColumnIndex = $_POST['order'][0]['column'] ?? 0;
$orderDir = $_POST['order'][0]['dir'] ?? 'asc';

$columns = [
    "p.name",
    "p.category",
    "p.brand",
    "p.hsn_code",
    "p.unit",
    "latest_pi.mrp",
    "latest_pi.purchase_price",
    "latest_pi.selling_price",
    "vendor_name",
    "total_purchased",
    "total_sold",
    "available_stock",
    "latest_pi.gst_percent"
];

$orderColumn = $columns[$orderColumnIndex] ?? "p.name";

// SEARCH FILTER
$where = "WHERE p.company_id = $cid";
$params = [];
$types = "";

if (!empty($searchValue)) {
    $where .= " AND (
        p.name LIKE ? OR
        p.category LIKE ? OR
        p.brand LIKE ? OR
        p.hsn_code LIKE ?
    )";

    $like = "%".$searchValue."%";
    $params = [$like, $like, $like, $like];
    $types = "ssss";
}

// TOTAL COUNT
$countSql = "SELECT COUNT(*) as total FROM products p $where";
$countStmt = $conn->prepare($countSql);

if (!empty($params)) {
    $countStmt->bind_param($types, ...$params);
}

$countStmt->execute();
$totalRecords = $countStmt->get_result()->fetch_assoc()['total'] ?? 0;
$totalFiltered = $totalRecords;


// MAIN QUERY
$sql = "
SELECT 
    p.id,
    p.name AS product_name,
    p.category,
    p.brand,
    p.hsn_code,
    p.unit,

    COALESCE(latest_pi.mrp,0) AS mrp,
    COALESCE(latest_pi.purchase_price,0) AS purchase_price,
    COALESCE(latest_pi.selling_price,0) AS selling_price,
    COALESCE(latest_pi.gst_percent,0) AS gst_percent,

    COALESCE(v.name,'-') AS vendor_name,

    COALESCE(pur.total_purchased,0) AS total_purchased,
    COALESCE(sal.total_sold,0) AS total_sold,
    COALESCE(stock.available_stock,0) AS available_stock

FROM products p

LEFT JOIN (
    SELECT pi.product_id, SUM(pi.quantity) AS total_purchased
    FROM purchase_items pi
    JOIN purchase_orders po ON po.id = pi.purchase_order_id AND po.company_id = $cid
    GROUP BY pi.product_id
) pur ON pur.product_id = p.id

LEFT JOIN (
    SELECT si.product_id, SUM(si.sold_qty) AS total_sold
    FROM sales_items si
    JOIN sales_invoices inv ON inv.id = si.sales_invoice_id AND inv.company_id = $cid
    WHERE inv.status = 'final'
    GROUP BY si.product_id
) sal ON sal.product_id = p.id

LEFT JOIN (
    SELECT pi.product_id, SUM(pi.available_qty) AS available_stock
    FROM purchase_items pi
    JOIN purchase_orders po ON po.id = pi.purchase_order_id AND po.company_id = $cid
    GROUP BY pi.product_id
) stock ON stock.product_id = p.id

LEFT JOIN (
    SELECT pi1.*
    FROM purchase_items pi1
    JOIN purchase_orders po1 ON po1.id = pi1.purchase_order_id AND po1.company_id = $cid
    INNER JOIN (
        SELECT pi_inner.product_id, MAX(pi_inner.id) AS max_id
        FROM purchase_items pi_inner
        JOIN purchase_orders po_inner ON po_inner.id = pi_inner.purchase_order_id AND po_inner.company_id = $cid
        GROUP BY pi_inner.product_id
    ) pi2 ON pi2.product_id = pi1.product_id AND pi2.max_id = pi1.id
) latest_pi ON latest_pi.product_id = p.id

LEFT JOIN purchase_orders po ON po.id = latest_pi.purchase_order_id
LEFT JOIN vendors v ON v.id = po.vendor_id

$where
ORDER BY $orderColumn $orderDir
LIMIT ?, ?
";

$stmt = $conn->prepare($sql);

if (!empty($params)) {
    $types2 = $types . "ii";
    $params[] = $start;
    $params[] = $length;
    $stmt->bind_param($types2, ...$params);
} else {
    $stmt->bind_param("ii", $start, $length);
}

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

$data = [];
while ($row = $res->fetch_assoc()) {

    $row['mrp'] = number_format((float)$row['mrp'], 2);
    $row['purchase_price'] = number_format((float)$row['purchase_price'], 2);
    $row['selling_price'] = number_format((float)$row['selling_price'], 2);

    $row['total_purchased'] = number_format((float)$row['total_purchased'], 0);
    $row['total_sold'] = number_format((float)$row['total_sold'], 0);
    $row['available_stock'] = number_format((float)$row['available_stock'], 0);

    $row['gst_percent'] = number_format((float)$row['gst_percent'], 2);

    $data[] = $row;
}

echo json_encode([
    "draw" => $draw,
    "recordsTotal" => $totalRecords,
    "recordsFiltered" => $totalFiltered,
    "data" => $data
]);
exit;