<?php
// api/bateria.php — Listado server-side para DataTables (con filtros y últimos registros de batería)
// dias: desde ultima_fecha si existe; si NO existe, desde d.fecha_ingreso.
// Incluye: lb.observacion (tb_bateria.observacion del último registro) y flag tiene_medicion.
declare(strict_types=1);

session_start();
header('Content-Type: application/json; charset=utf-8');

require_once __DIR__ . '/../conex.php';

if (!isset($conn) || !$conn instanceof mysqli) {
  echo json_encode(['draw'=>0,'recordsTotal'=>0,'recordsFiltered'=>0,'data'=>[],'error'=>'Sin conexión BD']); exit;
}

mysqli_set_charset($conn, 'utf8mb4');
@mysqli_query($conn, "SET time_zone = '-05:00'");

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

// Índices según TU tabla (checkbox, marca, modelo, chasis, color, ult.fecha, días, estado, observación, botón)
$order = isset($_POST['order']) ? $_POST['order'] : (isset($_GET['order']) ? $_GET['order'] : []);
$colMap = array(
  1 => 'd.marca',
  2 => 'd.modelo',
  3 => 'd.chasis',
  4 => 'd.color',
  5 => 'lb.ultima_fecha',
  6 => 'dias',
  7 => 'lb.estado_bateria',
  8 => 'lb.observacion'
);

/* =======================
   Filtros personalizados
   ======================= */
$desde  = isset($_POST['desde']) ? trim((string)$_POST['desde']) : (isset($_GET['desde']) ? trim((string)$_GET['desde']) : '');
$hasta  = isset($_POST['hasta']) ? trim((string)$_POST['hasta']) : (isset($_GET['hasta']) ? trim((string)$_GET['hasta']) : '');
$rango  = isset($_POST['rango']) ? trim((string)$_POST['rango']) : (isset($_GET['rango']) ? trim((string)$_GET['rango']) : '');
$chasisRaw = isset($_POST['chasis']) ? (string)$_POST['chasis'] : (isset($_GET['chasis']) ? (string)$_GET['chasis'] : '');
$chasisTmp = preg_split('/[\s,;|\n\r]+/', $chasisRaw);
$chasisArr = array();
if (is_array($chasisTmp)) {
  foreach ($chasisTmp as $s) {
    $s = trim($s);
    if ($s !== '') $chasisArr[] = $s;
  }
}
if (count($chasisArr) > 200) $chasisArr = array_slice($chasisArr, 0, 200);

// Búsqueda global (opcional)
$searchValue = '';
if (isset($_POST['search']) && isset($_POST['search']['value'])) {
  $searchValue = trim((string)$_POST['search']['value']);
} elseif (isset($_GET['search']) && isset($_GET['search']['value'])) {
  $searchValue = trim((string)$_GET['search']['value']);
}

/* =======================
   Subconsulta última medición
   ======================= */
$subUltima = "
  SELECT 
    b.id_datadai,
    b.idbateria,
    b.fecha  AS ultima_fecha,
    b.hora   AS ultima_hora,
    b.estado AS estado_bateria,
    b.valor,
    m.unidad,
    b.observacion
  FROM tb_bateria b
  INNER JOIN (
      SELECT id_datadai, MAX(CONCAT(fecha, ' ', hora, ' ', LPAD(idbateria,10,'0'))) AS mk
      FROM tb_bateria
      WHERE condicion = 1
      GROUP BY id_datadai
  ) t ON t.id_datadai = b.id_datadai 
     AND CONCAT(b.fecha, ' ', b.hora, ' ', LPAD(b.idbateria,10,'0')) = t.mk
  LEFT JOIN tb_unidad_medida m ON m.id = b.id_unidad
  WHERE b.condicion = 1
";

/* =======================
   SELECT base
   ======================= */
$select = "
  SELECT
    d.iddatadai                                            AS id_datadai,
    COALESCE(d.marca,'')                                   AS marca,
    COALESCE(d.modelo,'')                                  AS modelo,
    COALESCE(d.chasis,'')                                  AS chasis,
    COALESCE(d.color,'')                                   AS color,
    lb.estado_bateria,
    lb.valor,
    lb.unidad,
    lb.ultima_fecha,
    lb.ultima_hora,
    lb.observacion,

    CASE WHEN lb.ultima_fecha IS NULL THEN 0 ELSE 1 END    AS tiene_medicion,

    CASE 
      WHEN lb.ultima_fecha IS NULL 
        THEN DATEDIFF(CURDATE(), COALESCE(d.fecha_ingreso, CURDATE()))
      ELSE DATEDIFF(CURDATE(), lb.ultima_fecha)
    END AS dias

  FROM tb_datadai d
  LEFT JOIN ($subUltima) lb ON lb.id_datadai = d.iddatadai
  WHERE 1=1 
    AND d.id_estado > 2
";

/* =======================
   WHERE dinámico
   ======================= */
$where  = array();
$params = array();
$types  = '';

if ($desde !== '') {
  $where[] = "COALESCE(lb.ultima_fecha, d.fecha_ingreso) >= ?";
  $params[] = $desde;
  $types   .= 's';
}
if ($hasta !== '') {
  $where[] = "COALESCE(lb.ultima_fecha, d.fecha_ingreso) <= ?";
  $params[] = $hasta;
  $types   .= 's';
}

/* Rango de días (para vehículos con medición) */
if ($rango === 'sin') {
  $where[] = "lb.ultima_fecha IS NULL";
} elseif ($rango === 'lt7') {       // Menor a 15
  $where[] = "(lb.ultima_fecha IS NOT NULL AND DATEDIFF(CURDATE(), lb.ultima_fecha) < 15)";
} elseif ($rango === 'b7_14') {     // Igual a 15
  $where[] = "(lb.ultima_fecha IS NOT NULL AND DATEDIFF(CURDATE(), lb.ultima_fecha) = 15)";
} elseif ($rango === 'ge15') {      // Mayor a 15
  $where[] = "(lb.ultima_fecha IS NOT NULL AND DATEDIFF(CURDATE(), lb.ultima_fecha) > 15)";
}

/* Filtro por chasis exactos */
if (!empty($chasisArr)) {
  $in = implode(',', array_fill(0, count($chasisArr), '?'));
  $where[] = "d.chasis IN ($in)";
  foreach ($chasisArr as $ch) { $params[] = $ch; $types .= 's'; }
}

/* Búsqueda global */
if ($searchValue !== '') {
  $where[] = "(d.marca  LIKE CONCAT('%', ?, '%')
            OR d.modelo LIKE CONCAT('%', ?, '%')
            OR d.chasis LIKE CONCAT('%', ?, '%')
            OR d.color  LIKE CONCAT('%', ?, '%'))";
  for ($i=0; $i<4; $i++) { $params[] = $searchValue; $types .= 's'; }
}

/* Aplica WHERE */
if (!empty($where)) {
  $select .= " AND " . implode(" AND ", $where);
}

/* =======================
   Ordenamiento
   ======================= */
$orderBy = array();
if (is_array($order)) {
  foreach ($order as $od) {
    $idx = isset($od['column']) ? (int)$od['column'] : -1;
    $dir = (isset($od['dir']) && strtolower($od['dir']) === 'desc') ? 'DESC' : 'ASC';
    if (isset($colMap[$idx])) {
      $orderBy[] = $colMap[$idx] . ' ' . $dir;
    }
  }
}
if (empty($orderBy)) {
  $orderBy = array('dias DESC', 'lb.ultima_fecha DESC', 'lb.ultima_hora DESC');
}
$select .= " ORDER BY " . implode(", ", $orderBy);

/* =======================
   Conteos
   ======================= */
$totalSql = "SELECT COUNT(*) AS c FROM tb_datadai d WHERE d.id_estado > 2";
$totalRes = $conn->query($totalSql);
$recordsTotal = (int)($totalRes->fetch_assoc()['c'] ?? 0);
$totalRes->close();

$countSql = "
  SELECT COUNT(*) AS c FROM (
    " . preg_replace('/ORDER BY .+$/i', '', $select) . "
  ) x
";
$countStmt = $conn->prepare($countSql);
if ($countStmt === false) {
  echo json_encode(['draw'=>$draw,'recordsTotal'=>$recordsTotal,'recordsFiltered'=>0,'data'=>[],'error'=>'No se pudo preparar COUNT']); exit;
}
if ($types !== '') { $countStmt->bind_param($types, ...$params); }
$countStmt->execute();
$countRes = $countStmt->get_result();
$recordsFiltered = (int)($countRes->fetch_assoc()['c'] ?? 0);
$countStmt->close();

/* =======================
   LIMIT y ejecución final
   ======================= */
$params2 = $params;
$types2  = $types;
if ($length !== -1) {
  $select .= " LIMIT ?, ?";
  $params2[] = $start;
  $params2[] = $length;
  $types2   .= 'ii';
}

$stmt = $conn->prepare($select);
if ($stmt === false) {
  echo json_encode(['draw'=>$draw,'recordsTotal'=>$recordsTotal,'recordsFiltered'=>$recordsFiltered,'data'=>[],'error'=>'No se pudo preparar SELECT']); exit;
}
if ($types2 !== '') { $stmt->bind_param($types2, ...$params2); }
$stmt->execute();
$res = $stmt->get_result();

$data = array();
while ($r = $res->fetch_assoc()) {
  $data[] = array(
    'check'           => '',
    'id_datadai'      => (int)$r['id_datadai'],
    'marca'           => $r['marca'],
    'modelo'          => $r['modelo'],
    'chasis'          => $r['chasis'],
    'color'           => $r['color'],
    'estado_bateria'  => isset($r['estado_bateria']) ? $r['estado_bateria'] : null,
    'valor'           => isset($r['valor']) ? $r['valor'] : null,
    'unidad'          => isset($r['unidad']) ? $r['unidad'] : null,
    'ultima_fecha'    => isset($r['ultima_fecha']) ? $r['ultima_fecha'] : null,
    'ultima_hora'     => isset($r['ultima_hora']) ? $r['ultima_hora'] : null,
    'observacion'     => isset($r['observacion']) ? $r['observacion'] : null,
    'tiene_medicion'  => (int)$r['tiene_medicion'],
    'dias'            => (int)$r['dias']
  );
}
$stmt->close();

echo json_encode(array(
  'draw' => $draw,
  'recordsTotal' => $recordsTotal,
  'recordsFiltered' => $recordsFiltered,
  'data' => $data
), JSON_UNESCAPED_UNICODE);
