<?php
declare(strict_types=1);

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

$columns = ['c.codigo_cliente', 'c.nombre', 'c.nombre_negocio', 'c.nit', 'c.telefono',
            'c.ciudad', 'c.barrio', 'c.tipologia', 'v.nombre', 'c.ruta', 'c.dias_cartera'];
$p = dtRequestParams($columns);

$conn = db();

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

if ($p['search'] !== '') {
    $where[] = '(c.codigo_cliente LIKE ? OR c.nombre LIKE ? OR c.nombre_negocio LIKE ? OR c.nit LIKE ?)';
    $like = '%' . $p['search'] . '%';
    array_push($params, $like, $like, $like, $like);
    $types .= 'ssss';
}

if (!empty($_GET['cod_vendedor'])) {
    $where[] = 'c.cod_vendedor = ?';
    $params[] = $_GET['cod_vendedor'];
    $types .= 's';
}
if (!empty($_GET['ciudad'])) {
    $where[] = 'c.ciudad = ?';
    $params[] = $_GET['ciudad'];
    $types .= 's';
}
if (!empty($_GET['tipologia'])) {
    $where[] = 'c.tipologia = ?';
    $params[] = $_GET['tipologia'];
    $types .= 's';
}

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

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

$countSql = "SELECT COUNT(*) AS n FROM clientes c LEFT JOIN vendedores v ON v.cod_vendedor = c.cod_vendedor $whereSql";
$stmt = $conn->prepare($countSql);
if ($types !== '') {
    $stmt->bind_param($types, ...$params);
}
$stmt->execute();
$filtered = (int) $stmt->get_result()->fetch_assoc()['n'];
$stmt->close();

$sql = "SELECT c.codigo_cliente, c.nombre, c.nombre_negocio, c.nit, c.telefono, c.ciudad, c.barrio,
               c.tipologia, v.nombre AS vendedor, c.ruta, c.dias_cartera
        FROM clientes c
        LEFT JOIN vendedores v ON v.cod_vendedor = c.cod_vendedor
        $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_cliente']),
        h($r['nombre']),
        h($r['nombre_negocio']),
        h($r['nit']),
        h($r['telefono']),
        h($r['ciudad']),
        h($r['barrio']),
        h($r['tipologia']),
        h($r['vendedor']),
        h($r['ruta']),
        (int) $r['dias_cartera'],
    ];
}
$stmt->close();

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