<?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.brand",
    "p.category",
    "p.name", // batch dummy
    "vendor_name",
    "p.name",
    "mrp",
    "purchase_price",
    "selling_price",
    "total_qty",
    "available_qty",
    "gst_percent",
    "expiry_date"
];

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

$userRole = $_SESSION['user_role'] ?? '';
$filterCo = ($userRole === 'admin' && isset($_POST['co'])) ? (int)$_POST['co'] : $cid;
$coWhere  = $filterCo > 0 ? "AND po.company_id = $filterCo AND p.company_id = $filterCo" : "";
$where = "WHERE 1=1 $coWhere";
$params = [];
$types = "";

if (!empty($searchValue)) {
    $where .= " AND (
        p.name LIKE ? OR
        p.brand LIKE ? OR
        p.category LIKE ?
    )";
    $like = "%".$searchValue."%";
    $params = [$like, $like, $like];
    $types = "sss";
}

/* TOTAL COUNT */
$countSql = "
    SELECT COUNT(DISTINCT p.id) AS total
    FROM purchase_items pi
    JOIN products p ON p.id = pi.product_id
    JOIN purchase_orders po ON po.id = pi.purchase_order_id
    $where
";

$countStmt = $conn->prepare($countSql);
if (!empty($params)) $countStmt->bind_param($types, ...$params);
$countStmt->execute();
$totalRecords = $countStmt->get_result()->fetch_assoc()['total'] ?? 0;


/* MAIN GROUP QUERY */
$sql = "
    SELECT
        p.id AS product_id,
        p.name AS product_name,
        p.brand,
        p.category,

        '' AS batch_no,

        (
            SELECT v.name
            FROM purchase_orders po
            JOIN vendors v ON v.id = po.vendor_id
            JOIN purchase_items pi2 ON pi2.purchase_order_id = po.id
            WHERE pi2.product_id = p.id
            ORDER BY po.purchase_date DESC
            LIMIT 1
        ) AS vendor_name,

        '' AS purchase_date,

        MAX(pi.mrp) AS mrp,
        MAX(pi.purchase_price) AS purchase_price,
        MAX(pi.selling_price) AS selling_price,

        SUM(pi.quantity) AS total_qty,
        SUM(pi.available_qty) AS available_qty,

        MAX(pi.gst_percent) AS gst_percent,
        MAX(pi.expiry_date) AS expiry_date

    FROM purchase_items pi
    JOIN products p ON p.id = pi.product_id
    JOIN purchase_orders po ON po.id = pi.purchase_order_id
    $where
    GROUP BY p.id
    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['quantity'] = number_format((float)$row['total_qty'], 0);
    $row['available_qty'] = number_format((float)$row['available_qty'], 0);

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

    $row['expiry_date'] = (!empty($row['expiry_date']))
        ? date('d-m-Y', strtotime($row['expiry_date']))
        : '-';

    if (empty($row['vendor_name'])) $row['vendor_name'] = "-";

    $data[] = $row;
}

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