<?php
declare(strict_types=1);

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

$columns = [
    'vd.fecha', 'v.cod_regional', 'v.nombre', 'c.ciudad', 'c.ruta', 'c.barrio',
    'p.categoria', 'p.subcategoria', 'p.marca', 'vd.cantidad', 'vd.valor_neto',
    'c.nombre', 'c.tipologia', 'c.nombre_negocio', 'p.titulo', 'vd.valor_iva',
    'vd.factura', 'estadofactura',
];
$p = dtRequestParams($columns);
$conn = db();

[$whereSql, $params, $types] = construirFiltrosUtilidadVentas($p['search']);

$joinSql = "FROM ventas_detalle vd
            LEFT JOIN clientes c ON c.codigo_cliente = vd.codigo_cliente
            LEFT JOIN productos p ON p.codigo = vd.codigo_producto
            LEFT JOIN vendedores v ON v.cod_vendedor = vd.cod_vendedor
            LEFT JOIN cartera car ON car.factura = vd.factura";

$total = (int) $conn->query('SELECT COUNT(*) AS n FROM ventas_detalle')->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();

$orderCol = $p['orderCol'] === 'estadofactura'
    ? "(CASE WHEN car.factura IS NULL OR car.saldo <= 0 THEN 'CANCELADA' ELSE 'PENDIENTE' END)"
    : $p['orderCol'];

$sql = "SELECT vd.fecha, v.cod_regional, v.nombre AS vendedor, c.ciudad, c.ruta, c.barrio,
               p.categoria, p.subcategoria, p.marca, vd.cantidad, vd.valor_neto,
               c.nombre AS cliente, c.tipologia, c.nombre_negocio AS negocio, p.titulo AS nombre,
               vd.valor_iva, vd.factura,
               CASE WHEN car.factura IS NULL OR car.saldo <= 0 THEN 'CANCELADA' ELSE 'PENDIENTE' END AS estadofactura
        $joinSql
        $whereSql
        ORDER BY $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['fecha']),
        h($r['cod_regional']),
        h($r['vendedor']),
        h($r['ciudad']),
        h($r['ruta']),
        h($r['barrio']),
        h($r['categoria']),
        h($r['subcategoria']),
        h($r['marca']),
        number_format((float) $r['cantidad'], 2),
        money((float) $r['valor_neto']),
        h($r['cliente']),
        h($r['tipologia']),
        h($r['negocio']),
        h($r['nombre']),
        money((float) $r['valor_iva']),
        h($r['factura']),
        $r['estadofactura'] === 'CANCELADA'
            ? '<span class="badge bg-success">CANCELADA</span>'
            : '<span class="badge bg-warning text-dark">PENDIENTE</span>',
    ];
}
$stmt->close();

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