<?php
if (session_status() === PHP_SESSION_NONE) {
  session_start();
}   
error_reporting(0);
ini_set('display_errors', 0);

header('Content-Type: application/json');
require_once(__DIR__ . '/../db/conexion.php');

$fin = $_GET['fin'] ?? null;
$inicio = $_GET['inicio'] ?? null;
$attuid = $_SESSION['attuid'] ?? null;
$IncluirAnteriores = isset($_GET['incAnt']) ? (bool)$_GET['incAnt'] : false;

$condEstatus = '';
$condEstAcum = '';
$secCondEstatus = '';
$condEstBanco = '';
$paramsEstatus = [];
$tiposEstatus = '';

if ($fin) {

  if(!$IncluirAnteriores){
    $condEstatus = "WHERE DATE(fecha) <= ?";
    $secCondEstatus = "AND DATE(a.fecha_registro) <= ?";
    $condEstAcum = "AND DATE(a.fecha_registro) >= ?";
    $condEstBanco = "AND DATE(fecha_ingreso) <= ?";
    $paramsEstatus = [$fin, $fin, $inicio, $attuid, $fin];
    $tiposEstatus = 'sssss';
  }else {
  $condEstatus = "WHERE DATE(fecha) <= ?";
  $secCondEstatus = "AND DATE(a.fecha_registro) <= ?";
  $condEstBanco = "AND DATE(fecha_ingreso) <= ?";
  $paramsEstatus = [$fin, $fin, $attuid, $fin];
  $tiposEstatus = 'ssss';
  }
} else {
  $paramsEstatus = [ $attuid ];
  $tiposEstatus = 's';
}

$sqlEstatus = "
SELECT 
    COALESCE(s.estatus, 'asignado') as estatus,
    COUNT(*) AS total 
  FROM aspirantes a
  LEFT JOIN (
      SELECT s1.aspirante_id, s1.estatus, s1.fecha
      FROM seguimiento_estatus s1
      INNER JOIN (
          SELECT MAX(id) as max_id
          FROM seguimiento_estatus
          $condEstatus
          GROUP BY aspirante_id
      ) s2 ON s1.id = s2.max_id
  ) s ON a.id = s.aspirante_id
    WHERE COALESCE(s.estatus, 'asignado') NOT IN (
      'asignado', 'rechazado', 'contratado', 'en_pre_alta', 'en_plantilla', 'proceso_concluido'
    )
    $secCondEstatus
    $condEstAcum
    AND a.attuid = ?
    AND a.id NOT IN (
        SELECT aspirante_id FROM banco_registros WHERE estatus = 'pendiente'
        $condEstBanco
    )
  GROUP BY COALESCE(s.estatus, 'asignado')
  ORDER BY total DESC";

$stmt = $conn->prepare($sqlEstatus);
if (!empty($paramsEstatus)) {
    $stmt->bind_param($tiposEstatus, ...$paramsEstatus);
}
$stmt->execute();
$result = $stmt->get_result();

$data = [];
while ($row = $result->fetch_assoc()) {
  $estatus = strtoupper($row['estatus']);
  $estatus = strtr($estatus, "ÁÉÍÓÚÑÜáéíóúñü", "AEIOUNUAEIOUNU"); // quitar acentos
  $data[] = [
    'estatus' => $estatus,
    'total' => (int)$row['total']
  ];
}   
echo json_encode(['success' => true, 'data' => $data]);
?>