<?php
// api/nacionalizados_list.php — DataTables server-side para "Nacionalizados"
declare(strict_types=1);
session_start();

header('Content-Type: application/json; charset=utf-8');
header('Access-Control-Allow-Origin: *');

if (!isset($_SESSION['ingreso']) || $_SESSION['ingreso'] !== 'YES') {
  echo json_encode(['draw'=>0,'recordsTotal'=>0,'recordsFiltered'=>0,'data'=>[]]);
  exit;
}

require_once __DIR__ . '/../conex.php';
$db = $conn ?? ($conex ?? null);
if (!$db) {
  echo json_encode(['draw'=>0,'recordsTotal'=>0,'recordsFiltered'=>0,'data'=>[],'error'=>'Sin conexión DB']);
  exit;
}
mysqli_set_charset($db, 'utf8mb4');
@mysqli_query($db, "SET time_zone = '-05:00'");

/* ===== Parámetros DataTables ===== */
$draw   = isset($_POST['draw'])   ? (int)$_POST['draw']   : 1;
$start  = isset($_POST['start'])  ? (int)$_POST['start']  : 0;
$length = isset($_POST['length']) ? (int)$_POST['length'] : 10;
$length = ($length <= 0 || $length > 1000) ? 10 : $length;

$searchValue = trim((string)($_POST['search']['value'] ?? ''));

/* Ordenamiento (según columnas de tu DataTable)
   0:Nro 1:Fecha 2:Cliente 3:Pedido 4:Embarque 5:SP 6:DAI 7:Acciones */
$orderColumnIdx = isset($_POST['order'][0]['column']) ? (int)$_POST['order'][0]['column'] : 0;
$orderDir = strtoupper($_POST['order'][0]['dir'] ?? 'DESC');
$orderDir = ($orderDir === 'ASC') ? 'ASC' : 'DESC';

$orderableMap = [
  0 => 'fi.numero',
  1 => 'fi.fecha',
  2 => 'cliente',     // <— ahora ordena por el alias calculado
  3 => 'pedido',      // alias agregado
  4 => 'embarque',    // alias correcto
  5 => 'sp_nums',     // alias agregado
  6 => 'dai_nums',    // alias agregado
  7 => 'fi.idfacturainfo'
];
$orderBy = $orderableMap[$orderColumnIdx] ?? 'fi.idfacturainfo';

/* ===== Base Query (agregada por factura) ===== */
$baseFrom = "
  FROM tb_facturainfo AS fi
  LEFT JOIN tb_empresa AS e ON e.idempresa = fi.id_cliente
  LEFT JOIN tb_detallefactura AS df ON df.id_facturainfo = fi.idfacturainfo AND df.condicion = 1
  LEFT JOIN tb_datadai AS d ON d.iddatadai = df.id_datadai
  LEFT JOIN tb_solicitudprevia AS sp ON sp.idsolicitudprevia = d.id_solicitudprevia
  LEFT JOIN tb_daientrada AS de ON de.iddaientrada = d.id_daientrada
  WHERE fi.condicion = 1
    AND fi.id_estado > 5
";

/* ===== Conteo total (sin búsqueda) ===== */
$sqlTotal = "SELECT COUNT(DISTINCT fi.idfacturainfo) AS total " . $baseFrom;
$resTotal = mysqli_query($db, $sqlTotal);
$recordsTotal = 0;
if ($resTotal && ($r = mysqli_fetch_assoc($resTotal))) {
  $recordsTotal = (int)$r['total'];
}

/* ===== Filtro de búsqueda global ===== */
$whereSearch = '';
$params = [];
$types  = '';

if ($searchValue !== '') {
  // Incluimos fi.nombre para cuando exonerado = 1
  $whereSearch = " AND (
    fi.numero LIKE CONCAT('%', ?, '%')
    OR DATE_FORMAT(fi.fecha, '%Y-%m-%d') LIKE CONCAT('%', ?, '%')
    OR e.razon_social LIKE CONCAT('%', ?, '%')
    OR fi.nombre LIKE CONCAT('%', ?, '%')
    OR sp.pedido LIKE CONCAT('%', ?, '%')
    OR COALESCE(sp.cod_embarque, '') LIKE CONCAT('%', ?, '%')
    OR sp.numerosp LIKE CONCAT('%', ?, '%')
    OR de.numerodai LIKE CONCAT('%', ?, '%')
  )";
  $params = array_fill(0, 8, $searchValue); // 8 placeholders
  $types  = str_repeat('s', 8);
}

/* ===== Conteo filtrado ===== */
$sqlFiltered = "SELECT COUNT(DISTINCT fi.idfacturainfo) AS total " . $baseFrom . $whereSearch;
$recordsFiltered = $recordsTotal;

if ($stmtF = mysqli_prepare($db, $sqlFiltered)) {
  if ($searchValue !== '') {
    mysqli_stmt_bind_param($stmtF, $types, ...$params);
  }
  mysqli_stmt_execute($stmtF);
  $resF = mysqli_stmt_get_result($stmtF);
  if ($resF && ($rf = mysqli_fetch_assoc($resF))) {
    $recordsFiltered = (int)$rf['total'];
  }
  mysqli_stmt_close($stmtF);
}

/* ===== Query de datos (agregada por factura) ===== */
$sqlData = "
SELECT
  fi.idfacturainfo AS id,
  fi.numero,
  DATE_FORMAT(fi.fecha, '%Y-%m-%d') AS fecha,
  -- Muestra fi.nombre si exonerado=1 y tiene valor; si no, la razón social
  CASE
    WHEN COALESCE(fi.exonerado,0)=1 AND COALESCE(fi.nombre,'')<>'' THEN fi.nombre
    ELSE e.razon_social
  END AS cliente,
  TRIM(BOTH ', ' FROM GROUP_CONCAT(DISTINCT sp.pedido ORDER BY sp.pedido SEPARATOR ', ')) AS pedido,
  TRIM(BOTH ', ' FROM GROUP_CONCAT(DISTINCT COALESCE(sp.cod_embarque,'') ORDER BY sp.cod_embarque SEPARATOR ', ')) AS embarque,
  TRIM(BOTH ', ' FROM GROUP_CONCAT(DISTINCT sp.numerosp ORDER BY sp.numerosp SEPARATOR ', ')) AS sp_nums,
  TRIM(BOTH ', ' FROM GROUP_CONCAT(DISTINCT de.numerodai ORDER BY de.numerodai SEPARATOR ', ')) AS dai_nums,
  COALESCE(fi.exonerado,0) AS exonerado,
  fi.nombre AS nombre_factura
" . $baseFrom . $whereSearch . "
GROUP BY fi.idfacturainfo
ORDER BY $orderBy $orderDir
LIMIT ?, ?
";

$data = [];
if ($stmt = mysqli_prepare($db, $sqlData)) {
  if ($searchValue !== '') {
    // 8 's' + 2 'i'
    $types2 = $types . 'ii';
    $params2 = array_merge($params, [$start, $length]);
    mysqli_stmt_bind_param($stmt, $types2, ...$params2);
  } else {
    mysqli_stmt_bind_param($stmt, 'ii', $start, $length);
  }

  mysqli_stmt_execute($stmt);
  $res = mysqli_stmt_get_result($stmt);

  while ($row = $res->fetch_assoc()) {
    $data[] = [
      'id'         => (int)$row['id'],
      'numero'     => (string)$row['numero'],
      'fecha'      => (string)$row['fecha'],
      'cliente'    => (string)$row['cliente'], // ya viene calculado
      'pedido'     => (string)($row['pedido'] ?? ''),
      'embarque'   => (string)($row['embarque'] ?? ''),
      'sp'         => (string)($row['sp_nums'] ?? ''),
      'dai'        => (string)($row['dai_nums'] ?? ''),
      // opcional por si quieres usarlo en el front
      'exonerado'  => (int)$row['exonerado'],
      'nombre'     => (string)($row['nombre_factura'] ?? '')
    ];
  }
  mysqli_stmt_close($stmt);
}

/* ===== Respuesta ===== */
echo json_encode([
  'draw'            => $draw,
  'recordsTotal'    => $recordsTotal,
  'recordsFiltered' => $recordsFiltered,
  'data'            => $data
], JSON_UNESCAPED_UNICODE);
