<?php
// api/solicitud_list.php — DataTables (detecta sp.estado opcional)
declare(strict_types=1);
header('Content-Type: application/json; charset=utf-8');

// ——— Debug (apaga en prod) ———
$DEBUG = true;
if ($DEBUG) { ini_set('display_errors','1'); error_reporting(E_ALL); }

// Helpers
function respond($arr){ echo json_encode($arr, JSON_UNESCAPED_UNICODE); exit; }
function fail($msg,$extra=[]){ http_response_code(500); respond(array_merge(['success'=>false,'data'=>[], 'message'=>$msg], $extra)); }

// Sesión
session_start();
if (!isset($_SESSION['ingreso']) || $_SESSION['ingreso'] !== 'YES') {
  fail('No autorizado o sesión expirada');
}

// Conexión
$paths=[__DIR__.'/../conex.php', __DIR__.'/../../conex.php', dirname(__DIR__,3).'/conex.php', __DIR__.'/conex.php'];
$pathConex=null; foreach($paths as $p){ if (is_file($p)) { $pathConex=$p; break; } }
if(!$pathConex) fail('No se encontró conex.php', ['paths_intentados'=>$paths]);
require_once $pathConex;

$db = $conn ?? ($conex ?? null);
if (!$db instanceof mysqli) fail('Sin conexión BD (se esperaba mysqli en $conn o $conex)');
mysqli_set_charset($db,'utf8mb4');

// Parámetros DT
$draw   = (int)($_GET['draw'] ?? $_POST['draw'] ?? 1);
$start  = max(0, (int)($_GET['start'] ?? $_POST['start'] ?? 0));
$length = (int)($_GET['length'] ?? $_POST['length'] ?? 10);
if($length<=0) $length=10; if($length>2000) $length=2000;
$searchValue = trim((string)($_GET['search']['value'] ?? $_POST['search']['value'] ?? ''));

// Columnas (alineadas con tu DT)
$columns = [
  'sp.idsolicitudprevia',
  "CONCAT(sp.codigo,'-',sp.anio,'-',sp.numero)",
  'sp.pedido',
  'sp.tributo',
  'sp.cod_embarque',
  'e.ruc',
  'e.razon_social',
  'cat.detalle',
  'sp.idsolicitudprevia'
];

// Orden
$orderSql=' ORDER BY sp.idsolicitudprevia DESC';
if(isset($_GET['order'][0]['column'])){
  $ci=(int)$_GET['order'][0]['column'];
  $dir=strtolower($_GET['order'][0]['dir'] ?? 'desc')==='asc'?'ASC':'DESC';
  if(isset($columns[$ci])) $orderSql=' ORDER BY '.$columns[$ci].' '.$dir.', sp.idsolicitudprevia DESC';
}

// FROM + JOIN
$fromJoin = "
  FROM tb_solicitudprevia sp
  INNER JOIN tb_categoria   cat ON cat.idcategoria = sp.id_categoria
  INNER JOIN tb_empresa     e   ON e.idempresa    = sp.id_empresa
  INNER JOIN tb_localidad   l   ON l.idlocalidad  = sp.id_localidad
  LEFT  JOIN tb_daientrada  ed  ON ed.id_solicitudprevia = sp.idsolicitudprevia
";

// ¿Existe sp.estado?
$hasSpEstado = false;
$chk = $db->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tb_solicitudprevia' AND COLUMN_NAME = 'estado'");
if($chk && $chk->execute()){ $chk->bind_result($cnt); $chk->fetch(); $hasSpEstado = ($cnt>0); $chk->close(); }

// WHERE base
$where = " WHERE sp.condicion = 1 ";
if ($hasSpEstado) $where .= " AND sp.estado = 1 ";

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

// Búsqueda global
if ($searchValue!==''){
  $where.=" AND (
    sp.codigo LIKE ? OR sp.anio LIKE ? OR sp.numero LIKE ?
    OR sp.pedido LIKE ? OR sp.tributo LIKE ?
    OR sp.cod_embarque LIKE ? OR sp.naviera LIKE ?
    OR e.razon_social LIKE ? OR e.ruc LIKE ?
    OR ed.numerodai LIKE ? OR cat.detalle LIKE ?
  )";
  $like='%'.$searchValue.'%';
  $params = array_merge($params, array_fill(0,11,$like));
  $types  .= str_repeat('s',11);
}

// Totales
$qTotal = "SELECT COUNT(*) $fromJoin " . (" WHERE sp.condicion = 1 " . ($hasSpEstado ? " AND sp.estado = 1 " : ""));
$s1=$db->prepare($qTotal) ?: fail('Error total (prepare)',['mysqli_error'=>$db->error,'sql'=>$qTotal]);
$s1->execute(); $s1->bind_result($recordsTotal); $s1->fetch(); $s1->close();

// Filtrado
$qFiltered = "SELECT COUNT(*) $fromJoin $where";
$s2=$db->prepare($qFiltered) ?: fail('Error filtrado (prepare)',['mysqli_error'=>$db->error,'sql'=>$qFiltered]);
if($types!=='') $s2->bind_param($types, ...$params);
$s2->execute(); $s2->bind_result($recordsFiltered); $s2->fetch(); $s2->close();

// Datos
$qData = "
  SELECT
    sp.idsolicitudprevia,
    CONCAT(sp.codigo,'-',sp.anio,'-',sp.numero) AS numerosp,
    sp.pedido,
    sp.tributo,
    sp.cod_embarque AS embarque,
    COALESCE(sp.naviera,'')   AS naviera,
    COALESCE(e.ruc,'')        AS ruc,
    e.razon_social,
    COALESCE(ed.numerodai,'') AS numerodai,
    COALESCE(cat.detalle,'')  AS categoria
  $fromJoin
  $where
  $orderSql
  LIMIT ?, ?
";
$s3=$db->prepare($qData) ?: fail('Error datos (prepare)',['mysqli_error'=>$db->error,'sql'=>$qData]);
if($types!==''){ $typesLimit=$types.'ii'; $par = array_merge($params, [$start,$length]); $s3->bind_param($typesLimit, ...$par); }
else { $s3->bind_param('ii', $start, $length); }
$s3->execute(); $res=$s3->get_result();

$data=[];
while($row=$res->fetch_assoc()){
  $data[]=[
    'idsolicitudprevia'=>(int)$row['idsolicitudprevia'],
    'numerosp'=>$row['numerosp'],
    'pedido'=>$row['pedido'],
    'tributo'=>$row['tributo'],
    'embarque'=>$row['embarque'],
    'naviera'=>$row['naviera'],
    'ruc'=>$row['ruc'],
    'razon_social'=>$row['razon_social'],
    'numerodai'=>$row['numerodai'],
    'categoria'=>$row['categoria'],
  ];
}
$s3->close();

respond([
  'success'=>true,
  'draw'=>$draw,
  'recordsTotal'=>(int)$recordsTotal,
  'recordsFiltered'=>(int)$recordsFiltered,
  'data'=>$data,
]);
