<?php
declare(strict_types=1);

require_once __DIR__ . '/../includes/db.php';
require_once __DIR__ . '/../includes/functions.php';

$columns = ['p.codigo', 'p.titulo', 'p.categoria', 'p.subcategoria', 'p.marca', 'p.unidad', 'p.iva', 'p.gramaje'];
$p = dtRequestParams($columns);
$conn = db();

$where = [];
$params = [];
$types = '';

if ($p['search'] !== '') {
    $where[] = '(p.codigo LIKE ? OR p.titulo LIKE ? OR p.nombre_comercial LIKE ? OR p.marca LIKE ?)';
    $like = '%' . $p['search'] . '%';
    array_push($params, $like, $like, $like, $like);
    $types .= 'ssss';
}
if (!empty($_GET['categoria'])) {
    $where[] = 'p.categoria = ?';
    $params[] = $_GET['categoria'];
    $types .= 's';
}
if (!empty($_GET['marca'])) {
    $where[] = 'p.marca = ?';
    $params[] = $_GET['marca'];
    $types .= 's';
}

$whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';

$total = (int) $conn->query('SELECT COUNT(*) AS n FROM productos')->fetch_assoc()['n'];

$stmt = $conn->prepare("SELECT COUNT(*) AS n FROM productos p $whereSql");
if ($types !== '') { $stmt->bind_param($types, ...$params); }
$stmt->execute();
$filtered = (int) $stmt->get_result()->fetch_assoc()['n'];
$stmt->close();

$sql = "SELECT p.codigo, p.titulo, p.categoria, p.subcategoria, p.marca, p.unidad, p.iva, p.gramaje
        FROM productos p $whereSql
        ORDER BY {$p['orderCol']} {$p['orderDir']}
        LIMIT ?, ?";
$allParams = $params; $allTypes = $types . 'ii';
$allParams[] = $p['start']; $allParams[] = $p['length'];

$stmt = $conn->prepare($sql);
$stmt->bind_param($allTypes, ...$allParams);
$stmt->execute();
$result = $stmt->get_result();

$rows = [];
while ($r = $result->fetch_assoc()) {
    $rows[] = [
        h($r['codigo']), h($r['titulo']), h($r['categoria']), h($r['subcategoria']),
        h($r['marca']), h($r['unidad']), number_format((float) $r['iva'], 1) . '%',
        $r['gramaje'] !== null ? number_format((float) $r['gramaje'], 2) : '-',
    ];
}
$stmt->close();

dtRespond($p['draw'], $total, $filtered, $rows);
