<?php
declare(strict_types=1);

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

$columns = ['car.factura', 'c.nombre', 'v.nombre', 'car.fecha_factura', 'car.valor_documento', 'car.saldo', 'car.dias_vencidos'];
$p = dtRequestParams($columns);
$conn = db();

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

if ($p['search'] !== '') {
    $where[] = '(car.factura LIKE ? OR c.nombre LIKE ?)';
    $like = '%' . $p['search'] . '%';
    array_push($params, $like, $like);
    $types .= 'ss';
}
if (!empty($_GET['cod_vendedor'])) {
    $where[] = 'car.cod_vendedor = ?';
    $params[] = $_GET['cod_vendedor'];
    $types .= 's';
}
if (isset($_GET['dias_min']) && $_GET['dias_min'] !== '') {
    $where[] = 'car.dias_vencidos >= ?';
    $params[] = (int) $_GET['dias_min'];
    $types .= 'i';
}
if (isset($_GET['dias_max']) && $_GET['dias_max'] !== '') {
    $where[] = 'car.dias_vencidos <= ?';
    $params[] = (int) $_GET['dias_max'];
    $types .= 'i';
}

$whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';
$joinSql = "FROM cartera car
            LEFT JOIN clientes c ON c.codigo_cliente = car.codigo_cliente
            LEFT JOIN vendedores v ON v.cod_vendedor = car.cod_vendedor";

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

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

$sql = "SELECT car.factura, c.nombre AS cliente, v.nombre AS vendedor, car.fecha_factura,
               car.valor_documento, car.saldo, car.dias_vencidos
        $joinSql $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()) {
    $badge = 'bg-success';
    if ($r['dias_vencidos'] > 60) { $badge = 'bg-danger'; }
    elseif ($r['dias_vencidos'] > 30) { $badge = 'bg-warning text-dark'; }
    elseif ($r['dias_vencidos'] > 0) { $badge = 'bg-info text-dark'; }

    $rows[] = [
        h($r['factura']),
        h($r['cliente']),
        h($r['vendedor']),
        h($r['fecha_factura']),
        money((float) $r['valor_documento']),
        money((float) $r['saldo']),
        '<span class="badge ' . $badge . '">' . (int) $r['dias_vencidos'] . ' días</span>',
    ];
}
$stmt->close();

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