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

$estado = $_GET['estado'] ?? '';
$estado = trim($estado);

$response = ['success' => false, 'sectores' => []];

if ($estado === '') {
  echo json_encode($response);
  exit;
}

// 1. Obtener sectores del estado
$sql = "SELECT Sector, Tiendas FROM sectoresdPDVs WHERE Estado = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $estado);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
  $sector = $row['Sector'];
  $tiendas = array_map('trim', explode(',', $row['Tiendas']));
  $totalTiendas = count($tiendas);

  // 2. Consulta extendida: contar por tipoPosic y si está ocupado o no
  $sqlPos = "
    SELECT 
      SUM(CASE WHEN pos.tipoPosic = 'ÚNICO' THEN 1 ELSE 0 END) AS total_completo,
      SUM(CASE WHEN pos.tipoPosic = 'COMPUESTO' THEN 1 ELSE 0 END) AS total_medio,
      SUM(CASE WHEN pos.tipoPosic = 'ÚNICO' AND (pl.CURP IS NULL OR pl.CURP = '') THEN 1 ELSE 0 END) AS vacante_completo,
      SUM(CASE WHEN pos.tipoPosic = 'COMPUESTO' AND (pl.CURP IS NULL OR pl.CURP = '') THEN 1 ELSE 0 END) AS vacante_medio
    FROM Posiciones pos
    INNER JOIN Tiendas t ON pos.ORGPDV = t.ORGanizacionSOC
    LEFT JOIN Plantilla pl ON pos.codigo = pl.posicionCode
    WHERE t.PDV IN (" . implode(',', array_fill(0, count($tiendas), '?')) . ")
  ";

  $tipos = str_repeat('s', count($tiendas));
  $stmt2 = $conn->prepare($sqlPos);
  $stmt2->bind_param($tipos, ...$tiendas);
  $stmt2->execute();
  $res2 = $stmt2->get_result()->fetch_assoc();

  $response['sectores'][] = [
    'nombre' => $sector,
    'total_pdvs' => $totalTiendas,
    'total_completo' => (int)$res2['total_completo'],
    'total_medio' => (int)$res2['total_medio'],
    'vacante_completo' => (int)$res2['vacante_completo'],
    'vacante_medio' => (int)$res2['vacante_medio']
  ];
}

$response['success'] = true;
echo json_encode($response);
