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

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

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

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

// 1. Buscar las tiendas del sector
$sql = "SELECT Tiendas FROM sectoresdPDVs WHERE Sector = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $sector);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows === 0) {
  echo json_encode($response);
  exit;
}

$row = $result->fetch_assoc();
$tiendas = array_map('trim', explode(',', $row['Tiendas']));

// 2. Para cada tienda, buscar totales por tipo y ocupación
foreach ($tiendas as $pdv) {
  $sqlPDV = "
    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 = ?
  ";

  $stmt2 = $conn->prepare($sqlPDV);
  $stmt2->bind_param("s", $pdv);
  $stmt2->execute();
  $datos = $stmt2->get_result()->fetch_assoc();

  $response['tiendas'][] = [
    'nombre' => $pdv,
    'total_completo' => (int)($datos['total_completo'] ?? 0),
    'total_medio' => (int)($datos['total_medio'] ?? 0),
    'vacante_completo' => (int)($datos['vacante_completo'] ?? 0),
    'vacante_medio' => (int)($datos['vacante_medio'] ?? 0)
  ];
}

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