<?php
header('Content-Type: application/json; charset=utf-8');
$r = '../../';
require_once($r."inc/funciones.php");

$mysqli = connectDB($r);

$columns = [
    0 => 'p.id',
    1 => 'p.producto',
    2 => 'i.cantidad',
    3 => 'i.precio'
];

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

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

$orderColumn = $columns[$orderColumnIndex] ?? 'p.id';

$searchValue = $mysqli->real_escape_string($_POST['search']['value'] ?? '');

$where = "";

if(!empty($searchValue)){
    $where = "WHERE p.producto COLLATE utf8mb4_unicode_ci LIKE '%$searchValue%'";
}

/* TOTAL REGISTROS */
$totalQuery = $mysqli->query("
    SELECT COUNT(*) as total
    FROM z_productos p
");

if(!$totalQuery){
    die(json_encode(["error"=>$mysqli->error]));
}

$totalData = $totalQuery->fetch_assoc()['total'];

/* TOTAL FILTRADOS */
if(!empty($where)){
    $filterQuery = $mysqli->query("
        SELECT COUNT(*) as total
        FROM z_productos p
        $where
    ");
    $totalFiltered = $filterQuery->fetch_assoc()['total'];
}else{
    $totalFiltered = $totalData;
}

/* DATOS */
$dataQuery = $mysqli->query("
    SELECT 
        p.id,
        p.producto,
        IFNULL(i.cantidad,0) as cantidad,
        IFNULL(i.precio,0) as precio
    FROM z_productos p
    LEFT JOIN z_inventario i 
        ON i.idproducto = p.id
    $where
    ORDER BY $orderColumn $orderDir
    LIMIT $start, $length
");

if(!$dataQuery){
    die(json_encode(["error"=>$mysqli->error]));
}

$data = [];

while($row = $dataQuery->fetch_assoc()){

    $nestedData = [];
    $nestedData[] = $row['id'];
    $nestedData[] = $row['producto'];
    $nestedData[] = number_format($row['cantidad'], 3, ',', '.');
    $nestedData[] = number_format($row['precio'], 2, ',', '.');

    $data[] = $nestedData;
}

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

exit;