<?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'] ?? 'desc';

$columns = [
    "p.name",
    "p.brand",
    "p.category",
    "pi.batch_no",
    "v.name",
    "po.purchase_date",
    "pi.mrp",
    "pi.purchase_price",
    "pi.selling_price",
    "pi.quantity",
    "pi.available_qty",
    "pi.gst_percent",
    "pi.expiry_date"
];

$orderColumn = $columns[$orderColumnIndex] ?? "po.purchase_date";

$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 ? OR
        pi.batch_no LIKE ? OR
        v.name LIKE ?
    )";
    $like = "%".$searchValue."%";
    $params = [$like, $like, $like, $like, $like];
    $types = "sssss";
}

/* TOTAL COUNT */
$countSql = "
    SELECT COUNT(*) 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
    JOIN vendors v ON v.id = po.vendor_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 QUERY */
$sql = "
    SELECT
        p.name AS product_name,
        p.brand,
        p.category,
        pi.batch_no,
        v.name AS vendor_name,
        po.purchase_date,
        pi.mrp,
        pi.purchase_price,
        pi.selling_price,
        pi.quantity,
        pi.available_qty,
        pi.gst_percent,
        pi.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
    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['purchase_date'] = date('d-m-Y', strtotime($row['purchase_date']));

    $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['quantity'], 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']))
        : '-';

    $data[] = $row;
}

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