<?php
// views/pages/raspadita/api/raspadita_robo_list.php
// Lista robos/bloqueo desde tbr_raspadita con filtros:
// POST JSON: { fecha_ini?, fecha_fin?, id_coordinador?, id_punto?, id_canillita? }
// Devuelve: {success:true,total:int,data:[...]}

declare(strict_types=1);
session_start();
date_default_timezone_set('America/Guayaquil');

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');
header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
header('Pragma: no-cache');
header('Expires: 0');

if (($_SERVER['REQUEST_METHOD'] ?? '') === 'OPTIONS') { http_response_code(204); exit; }
if (($_SERVER['REQUEST_METHOD'] ?? '') !== 'POST') {
  http_response_code(405);
  echo json_encode(['success'=>false,'message'=>'Método no permitido. Use POST'], JSON_UNESCAPED_UNICODE);
  exit;
}

require_once __DIR__ . '/../../conex.php';
$db = $conn ?? null;

if (!$db instanceof mysqli) {
  http_response_code(500);
  echo json_encode(['success'=>false,'message'=>'Sin conexión BD'], JSON_UNESCAPED_UNICODE);
  exit;
}

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

/* ===================== HELPERS ===================== */
function jexit(array $p, int $code=200): void {
  http_response_code($code);
  echo json_encode($p, JSON_UNESCAPED_UNICODE);
  exit;
}
function read_json(): array {
  $raw = file_get_contents('php://input');
  if (!$raw) return [];
  $j = json_decode($raw, true);
  return is_array($j) ? $j : [];
}
function stmt_bind_dynamic(mysqli_stmt $stmt, string $types, array &$params): void {
  $bind = [];
  $bind[] = $types;
  foreach ($params as $k => &$v) { $bind[] = &$v; }
  call_user_func_array([$stmt, 'bind_param'], $bind);
}

/* ===================== INPUT ===================== */
$in = read_json();

$fecha_ini = trim((string)($in['fecha_ini'] ?? ''));        // YYYY-MM-DD
$fecha_fin = trim((string)($in['fecha_fin'] ?? ''));        // YYYY-MM-DD
$id_coor   = (int)($in['id_coordinador'] ?? 0);             // r.id_coordinador
$id_punto  = (int)($in['id_punto'] ?? 0);                   // r.id_usuario
$id_can    = (int)($in['id_canillita'] ?? 0);               // r.id_canillita

// ✅ NO mostrar devueltos (todos deben ser 0 o NULL)
$where = "
  r.estado <= 3
  AND r.anulado = 0
  AND COALESCE(r.devuelto_punto,0) = 0
  AND COALESCE(r.devuelto_canillita,0) = 0
  AND COALESCE(r.devuelto_coordinador,0) = 0
";

$params = [];
$types  = "";

/* ===================== FILTROS ===================== */
// fechas por DATE(r.fecha_hora)
if ($fecha_ini !== '' && $fecha_fin !== '') {
  $where .= " AND DATE(r.fecha_hora) BETWEEN ? AND ?";
  $types .= "ss";
  $params[] = $fecha_ini;
  $params[] = $fecha_fin;
} elseif ($fecha_ini !== '') {
  $where .= " AND DATE(r.fecha_hora) >= ?";
  $types .= "s";
  $params[] = $fecha_ini;
} elseif ($fecha_fin !== '') {
  $where .= " AND DATE(r.fecha_hora) <= ?";
  $types .= "s";
  $params[] = $fecha_fin;
}

// coordinador
if ($id_coor > 0) {
  $where .= " AND r.id_coordinador = ?";
  $types .= "i";
  $params[] = $id_coor;
}

// punto
if ($id_punto > 0) {
  $where .= " AND r.id_usuario = ?";
  $types .= "i";
  $params[] = $id_punto;
}

// canillita
if ($id_can > 0) {
  $where .= " AND r.id_canillita = ?";
  $types .= "i";
  $params[] = $id_can;
}

/* ===== 1) TOTAL ===== */
$sqlTotal = "
  SELECT COUNT(*) AS total
  FROM tbr_raspadita r
  LEFT JOIN tbr_usuario u ON u.idusuario = r.id_usuario
  LEFT JOIN tbr_canillita c ON c.id = r.id_canillita
  LEFT JOIN tbr_coordinador co ON co.id = r.id_coordinador
  WHERE $where
";

$stmt = mysqli_prepare($db, $sqlTotal);
if (!$stmt) jexit(['success'=>false,'message'=>'Error preparando SQL total'], 500);

if ($types !== "") stmt_bind_dynamic($stmt, $types, $params);
mysqli_stmt_execute($stmt);
$res = mysqli_stmt_get_result($stmt);
$row = mysqli_fetch_assoc($res) ?: ['total'=>0];
mysqli_stmt_close($stmt);

$total = (int)($row['total'] ?? 0);

/* ===== 2) DATA ===== */
$sqlData = "
  SELECT
    r.id,
    r.fecha_hora,
    r.codigo,
    CASE
      WHEN r.id_canillita IS NOT NULL AND r.id_canillita <> 0
           AND c.canillita IS NOT NULL AND c.canillita <> '' 
        THEN c.canillita

      WHEN r.id_usuario IS NOT NULL AND r.id_usuario <> 0
           AND u.usuario IS NOT NULL AND u.usuario <> ''
        THEN u.usuario

      WHEN r.id_coordinador IS NOT NULL AND r.id_coordinador <> 0
           AND co.nombre IS NOT NULL AND co.nombre <> ''
        THEN co.nombre

      ELSE '—'
    END AS responsable
  FROM tbr_raspadita r
  LEFT JOIN tbr_usuario u ON u.idusuario = r.id_usuario
  LEFT JOIN tbr_canillita c ON c.id = r.id_canillita
  LEFT JOIN tbr_coordinador co ON co.id = r.id_coordinador
  WHERE $where
  ORDER BY r.fecha_hora DESC
  LIMIT 2000
";

$stmt = mysqli_prepare($db, $sqlData);
if (!$stmt) jexit(['success'=>false,'message'=>'Error preparando SQL data'], 500);

if ($types !== "") stmt_bind_dynamic($stmt, $types, $params);
mysqli_stmt_execute($stmt);
$res = mysqli_stmt_get_result($stmt);

$data = [];
while ($r = mysqli_fetch_assoc($res)) {
  $data[] = [
    'id'          => $r['id'],
    'fecha_hora'  => $r['fecha_hora'],
    'codigo'      => $r['codigo'],
    'responsable' => $r['responsable'],
  ];
}
mysqli_stmt_close($stmt);

jexit([
  'success' => true,
  'total'   => $total,
  'data'    => $data
]);
