<?php
// api/novedades_tarja.php — Novedades por chasis (robusto: fecha dinámica, pivote opcional, severidad por REGEXP)
declare(strict_types=1);

header('Content-Type: application/json; charset=utf-8');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: POST, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type, Authorization');

if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') { http_response_code(204); exit; }

$pathConex = realpath(__DIR__ . '/../conex.php') ?: realpath(__DIR__ . '/../../conex.php') ?: realpath(__DIR__ . '/conex.php');
if (!$pathConex) { http_response_code(500); echo json_encode(['success'=>false,'message'=>'No se encontró conex.php']); exit; }
require_once $pathConex;

$db = $conn ?? ($conex ?? null);
if (!$db || !($db instanceof mysqli)) { http_response_code(500); echo json_encode(['success'=>false,'message'=>'Conexión BD no disponible']); exit; }
mysqli_set_charset($db, 'utf8mb4');
@mysqli_query($db, "SET time_zone = '-05:00'");

$in = json_decode(file_get_contents('php://input'), true) ?: [];
$chasis = trim((string)($in['chasis'] ?? ''));
if ($chasis === '') { http_response_code(422); echo json_encode(['success'=>false,'message'=>'chasis requerido']); exit; }

/* === Detectar columna de fecha disponible en tb_incidente === */
$fechaCandidates = ['fecha_incidente','fecha','created_at','fechareg','fechacrea'];
$fechaCol = null;
try {
  $placeholders = implode(',', array_fill(0, count($fechaCandidates), '?'));
  $sqlCols = "SELECT column_name 
              FROM information_schema.columns 
              WHERE table_schema = DATABASE() 
                AND table_name = 'tb_incidente'
                AND column_name IN ($placeholders)
              ORDER BY FIELD(column_name, $placeholders) LIMIT 1";
  $types = str_repeat('s', count($fechaCandidates)*2);
  $params = array_merge($fechaCandidates, $fechaCandidates);
  if ($stmt = $db->prepare($sqlCols)) {
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $stmt->bind_result($colFound);
    if ($stmt->fetch()) { $fechaCol = $colFound; }
    $stmt->close();
  }
} catch (Throwable $e) { /* ignorar */ }

$fechaSelect = $fechaCol
  ? "DATE_FORMAT(i.`{$fechaCol}`, '%Y-%m-%d %H:%i:%s') AS fecha_incidente"
  : "'' AS fecha_incidente";
$fechaOrder = $fechaCol ? "i.`{$fechaCol}` DESC" : "i.idincidente DESC";

/* === Pivote opcional === */
$tblPivot = 'tb_incidente_tipo'; // cambia si tu pivote tiene otro nombre
$hasPivot = false;
try {
  if ($chk = $db->prepare("SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?")) {
    $chk->bind_param('s', $tblPivot);
    $chk->execute();
    $chk->store_result();
    $hasPivot = ($chk->num_rows > 0);
    $chk->close();
  }
} catch (Throwable $e) { /* ignorar */ }

$joinPivot   = $hasPivot ? "LEFT JOIN {$tblPivot} itp ON itp.incident_id = i.idincidente" : "";
$selectPivot = $hasPivot ? "GROUP_CONCAT(itp.id_tipo) AS tipos_reparacion_ids_csv" : "NULL AS tipos_reparacion_ids_csv";

/* === Consulta principal (severidad por REGEXP en detallenovedad; seccion no existe -> NULL) === */
$sql = "
SELECT
  i.idincidente                                AS incident_id,
  {$fechaSelect},
  z.detallezona,
  NULL                                         AS seccion,
  p.detalleparte,
  n.detallenovedad,
  i.observacion,
  i.foto,
  CASE
    WHEN n.detallenovedad REGEXP '(golpe|estructura|quebrad|rotur|chasis|vidrio roto|airbag|fuga)' THEN 'CRÍTICA'
    WHEN n.detallenovedad REGEXP '(abolladur|pintur|rayon|raspon|faro|luz|parachoque|espejo|parabrisa)' THEN 'MEDIA'
    ELSE 'LEVE'
  END                                          AS severidad,
  m.medida                                     AS nombre_medida_catalogo,
  i.medida                                     AS medida_tomada,
  {$selectPivot}
FROM tb_incidente i
LEFT JOIN tb_datadai d   ON d.iddatadai = i.id_datadai
LEFT JOIN tb_zona    z   ON z.idzona    = i.id_zona
LEFT JOIN tb_parte   p   ON p.idparte   = i.id_parte
LEFT JOIN tb_novedad n   ON n.idnovedad = i.id_novedad
LEFT JOIN tb_medida  m   ON m.id = i.id_medida
{$joinPivot}
WHERE d.chasis = ?
  AND i.condicion = 1
GROUP BY i.idincidente
ORDER BY {$fechaOrder}";

try {
  $stmt = $db->prepare($sql);
  if (!$stmt) { throw new RuntimeException($db->error); }
  $stmt->bind_param('s', $chasis);
  $stmt->execute();
  $res = $stmt->get_result();

  $data = [];
  while ($row = $res->fetch_assoc()) {
    $row['incident_id'] = (int)$row['incident_id'];
    $row['tipos_reparacion_ids'] =
      (!empty($row['tipos_reparacion_ids_csv']))
        ? array_map('intval', explode(',', $row['tipos_reparacion_ids_csv']))
        : [];
    unset($row['tipos_reparacion_ids_csv']);
    $data[] = $row;
  }
  $stmt->close();

  echo json_encode(['success'=>true, 'data'=>$data]);
} catch (Throwable $e) {
  http_response_code(500);
  echo json_encode(['success'=>false,'message'=>'Error al obtener novedades','sql_error'=>$e->getMessage()]);
}
