<?php

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// CORS
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: Content-Type, Authorization");
header("Access-Control-Allow-Methods: GET, OPTIONS");

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

require_once(__DIR__ . '/../config.php');
require_once __DIR__ . "/../vendor/autoload.php";

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

// ================== PARAMETRO ==================
$idEstudio = isset($_GET['id_estudio']) ? intval($_GET['id_estudio']) : 0;
if ($idEstudio <= 0) {
  http_response_code(400);
  echo "ID_ESTUDIO inválido";
  exit;
}





function findFirstRowAfterAmongTexts($sheet, $afterRow, $titles) {
  $afterRow = (int)$afterRow;
  $best = 0;

  foreach ((array)$titles as $t) {
    $r = findRowByText($sheet, $t);
    if ($r > $afterRow) {
      if ($best === 0 || $r < $best) $best = $r;
    }
  }
  return $best; // 0 si no encuentra
}



function isMeaningfulText($v) {
  $s = trim((string)$v);
  if ($s === '') return false;

  $u = _normLabel($s); // usa tu normalizador (quita tildes y pone mayus)

  // textos que NO deben considerarse "info"
  $bad = [
    'NO', 'N/A', 'NA', 'SIN', 'NINGUNO', 'NINGUNA',
    'SIN REGISTRO', 'SIN INFORMACION', 'SIN INFORMACIÓN',
    'NO APLICA', 'NO REGISTRA', 'NO REGISTRAN', 'NO PRESENTA'
  ];

  return !in_array($u, $bad, true);
}

function hechosHasAnyMeaningful($hechos) {
  if (!is_array($hechos) || count($hechos) === 0) return false;

  foreach ($hechos as $h) {
    if (
      isMeaningfulText($h['TIPO_NOTICIA'] ?? '') ||
      isMeaningfulText($h['DELITO'] ?? '') ||
      isMeaningfulText($h['CALIDAD'] ?? '') ||
      isMeaningfulText($h['FECHA'] ?? '')
    ) {
      return true;
    }
  }
  return false;
}


// ================== ✅ MOSTRAR SOLO SI D22 TIENE TEXTO DESPUÉS DE ":" ==================
function hideHistorialHechosIfD22EmptyAfterColon($sheet) {

  $raw = trim((string)$sheet->getCell('D22')->getValue());

  // Si está totalmente vacío -> ocultar
  if ($raw === '') {
    return _removeHistorialHechosSection($sheet);
  }

  // Debe existir ":" para evaluar lo que hay después
  $posColon = mb_strpos($raw, ':');
  if ($posColon === false) {
    // Si por alguna razón no hay ":", lo tratamos como vacío -> ocultar
    return _removeHistorialHechosSection($sheet);
  }

  // Texto después de los ":" (puede tener espacios)
  $after = trim(mb_substr($raw, $posColon + 1));

  // Si NO hay nada después de ":" -> ocultar
  if ($after === '') {
    return _removeHistorialHechosSection($sheet);
  }

  // Si SÍ hay algo después de ":" -> dejar visible
  return false;
}

// Helper interno: elimina el bloque HISTORIAL DE HECHOS completo
function _removeHistorialHechosSection($sheet) {

  // Inicio del bloque (título)
  $posStart = findCellContainsText($sheet, 'HISTORIAL DE HECHOS');
  if (!$posStart) return false;
  $startRow = (int)$posStart['row'];

  // Fin del bloque: antes de la siguiente sección grande
  $nextRow = findNextSectionRowAfter($sheet, $startRow, [
    'CONSULTA HISTORIAL FINANCIERO',
    '1. HISTORIA LABORAL',
    'HISTORIA LABORAL',
    'CONSULTA ACADEMICA',
    'CONSULTA ACADÉMICA',
    'OBSERVACION PROCURADURIA',
    'OBSERVACIÓN PROCURADURIA'
  ]);

  $endRow = ($nextRow > 0) ? ($nextRow - 1) : ($startRow + 10);

  if ($endRow < $startRow) return false;

  // ✅ Eliminar filas (definitivo, no se “revive” en Sheets)
  $sheet->removeRow($startRow, ($endRow - $startRow + 1));

  return true;
}




// ================== ✅ OCULTAR (ELIMINAR) SECCIÓN NOVEDAD SI D21 ESTÁ VACÍA ==================
function hideNovedadIfD21Empty($sheet) {

  // ✅ Si D21 está vacío (NULL/vacío/espacios) => ocultar toda la sección NOVEDAD
  $d21 = trim((string)$sheet->getCell('D21')->getValue());

  if ($d21 !== '') {
    return false; // hay algo en D21 => se deja la sección
  }

  // ✅ Buscar inicio del bloque NOVEDAD (por contenga, merge-friendly)
  $posStart = findCellContainsText($sheet, 'NOVEDAD');
  if (!$posStart) return false;

  $startRow = (int)$posStart['row'];

  // ✅ Buscar fin del bloque: antes de "HISTORIAL DE HECHOS"
  $posEnd = findCellContainsText($sheet, 'HISTORIAL DE HECHOS');
  $endRow = $posEnd ? ((int)$posEnd['row'] - 1) : ($startRow + 6); // fallback

  if ($endRow < $startRow) return false;

  // ✅ ELIMINAR filas (Google Sheets no revive esto)
  $sheet->removeRow($startRow, ($endRow - $startRow + 1));

  return true;
}





// ================== ✅ OCULTAR NOVEDAD + HISTORIAL (si NO hay info real aunque existan filas) ==================
function hideNovedadIfEmptyStrict($sheet, $hechos, $consultas) {

  // ✅ Hechos: solo cuenta si hay al menos un campo con texto/fecha real
  $hasHechos = false;
  if (is_array($hechos) && count($hechos) > 0) {
    foreach ($hechos as $h) {
      $tipo = trim((string)($h['TIPO_NOTICIA'] ?? ''));
      $del  = trim((string)($h['DELITO'] ?? ''));
      $cal  = trim((string)($h['CALIDAD'] ?? ''));
      $fec  = trim((string)($h['FECHA'] ?? ''));
      $est  = trim((string)($h['ESTADO'] ?? ''));

      if ($tipo !== '' || $del !== '' || $cal !== '' || $fec !== '' || $est !== '') {
        $hasHechos = true;
        break;
      }
    }
  }

  // ✅ Noticia criminal: solo cuenta si no es NULL/vacío
  $hasNoticia = false;
  if (is_array($consultas) && !empty($consultas)) {
    $hasNoticia =
      (isset($consultas['NOTICIA_CRIMINAL']) && trim((string)$consultas['NOTICIA_CRIMINAL']) !== '') ||
      (isset($consultas['NOTICIA_CRIMINAL_OBSERVACIONES']) && trim((string)$consultas['NOTICIA_CRIMINAL_OBSERVACIONES']) !== '');
  }

  // Si hay algo, NO ocultar
  if ($hasHechos || $hasNoticia) return false;

  // ✅ Buscar "NOVEDAD" por contenga (merge friendly)
  $pos = findCellContainsText($sheet, 'NOVEDAD');
  if (!$pos) return false;

  $startRow = (int)$pos['row'];

  // ✅ Buscar siguiente sección por "contenga"
  $nextRow = findNextSectionRowAfter($sheet, $startRow, [
    'CONSULTA HISTORIAL FINANCIERO',
    'HISTORIA LABORAL',
    'CONSULTA ACADEMICA',
    'CONSULTA ACADÉMICA',
    'OBSERVACION PROCURADURIA',
    'OBSERVACIÓN PROCURADURIA'
  ]);

  $endRow = ($nextRow > 0) ? ($nextRow - 1) : ($startRow + 25);

  // ✅ ELIMINAR filas (Sheets no revive esto)
  $sheet->removeRow($startRow, ($endRow - $startRow + 1));

  return true;
}





// ================== ✅ OCULTAR SECCIÓN NOVEDAD COMPLETA SI NO HAY INFORMACIÓN ==================
function hideNovedadIfEmpty($sheet, $hechos, $consultas) {

  // 1. ¿Hay hechos con información real?
  $hasHechos = hechosHasAnyMeaningful($hechos);

  // 2. ¿Hay noticia criminal u observaciones?
  $hasNoticia =
    isMeaningfulText($consultas['NOTICIA_CRIMINAL'] ?? '') ||
    isMeaningfulText($consultas['NOTICIA_CRIMINAL_OBSERVACIONES'] ?? '');

  // 👉 Si hay algo, NO ocultar
  if ($hasHechos || $hasNoticia) {
    return false;
  }

  // 3. Buscar encabezado NOVEDAD
  $rowNovedad = findRowByText($sheet, 'NOVEDAD');
  if ($rowNovedad <= 0) return false;

  // 4. Buscar siguiente sección principal
  $nextRow = findFirstRowAfterAmongTexts($sheet, $rowNovedad, [
    'CONSULTA HISTORIAL FINANCIERO',
    '1. HISTORIA LABORAL',
    'HISTORIA LABORAL',
    'CONSULTA ACADÉMICA',
    'CONSULTA ACADEMICA',
    'OBSERVACIÓN PROCURADURIA',
    'OBSERVACION PROCURADURIA'
  ]);

  // 5. Definir rango a ocultar
  $endRow = ($nextRow > 0)
    ? ($nextRow - 1)
    : ($rowNovedad + 20); // fallback seguro

  // 6. Ocultar filas
  hideRowsRange($sheet, $rowNovedad, $endRow);

  return true;
}



// ================== ✅ OCULTAR HISTORIA LABORAL SI NO HAY DATOS ==================
function hideHistoriaLaboralIfEmpty($sheet, $refsLab) {

  $hasData = is_array($refsLab) && count($refsLab) > 0;

  // Si hay información → NO ocultar
  if ($hasData) return false;

  // Buscar encabezado de la sección (tienes dos variantes en tu código)
  $labRow = findRowByText($sheet, '1. HISTORIA LABORAL');
  if ($labRow <= 0) $labRow = findRowByText($sheet, 'HISTORIA LABORAL');

  if ($labRow <= 0) return false;

  // Buscar la siguiente sección después de HISTORIA LABORAL
  $nextRow = findFirstRowAfterAmongTexts($sheet, $labRow, [
    'CONSULTA ACADÉMICA',
    'CONSULTA ACADEMICA',
    'OBSERVACIÓN PROCURADURIA',
    'OBSERVACION PROCURADURIA',
    'OBSERVACIÓN PROCURADURÍA',
    'OBSERVACION PROCURADURÍA',
    'OBSERVACIÓN PROCURADURIA GENERAL DE LA NACIÓN',
    'OBSERVACIÓN POLICÍA',
    'OBSERVACION POLICIA',
    'OBSERVACIÓN POLICÍA NACIONAL',
    'OBSERVACION POLICIA NACIONAL'
  ]);

  // Rango a ocultar (si no encuentra la siguiente sección, usa fallback)
  $endRow = ($nextRow > 0) ? ($nextRow - 1) : ($labRow + 25);

  hideRowsRange($sheet, $labRow, $endRow);

  return true;
}






// ================== ✅ OCULTAR CONSULTA ACADÉMICA SI NO HAY DATOS ==================
function hideConsultaAcademicaIfEmpty($sheet, $refsEdu) {

  $hasData = is_array($refsEdu) && count($refsEdu) > 0;

  // Si hay información → NO ocultar
  if ($hasData) return false;

  // Buscar encabezado de la sección
  $acadRow = findRowByText($sheet, 'CONSULTA ACADÉMICA');
  if ($acadRow <= 0) {
    $acadRow = findRowByText($sheet, 'CONSULTA ACADEMICA');
  }

  if ($acadRow <= 0) return false;

  // Buscar la siguiente sección después de Consulta Académica
  $nextRow = findFirstRowAfterAmongTexts($sheet, $acadRow, [
    'OBSERVACIÓN PROCURADURIA',
    'OBSERVACION PROCURADURIA',
    'OBSERVACIÓN PROCURADURÍA',
    'OBSERVACION PROCURADURÍA',
    'OBSERVACIÓN PROCURADURIA GENERAL DE LA NACIÓN',
    'OBSERVACIÓN POLICÍA',
    'OBSERVACION POLICIA',
    'OBSERVACIÓN POLICÍA NACIONAL',
    'OBSERVACION POLICIA NACIONAL'
  ]);

  // Rango a ocultar
  $endRow = ($nextRow > 0) ? ($nextRow - 1) : ($acadRow + 20);

  hideRowsRange($sheet, $acadRow, $endRow);

  return true;
}






// ================== ✅ OCULTAR NOVEDAD + HISTORIAL DE HECHOS ==================
function hideNovedadAndHistorialHechosIfEmpty($sheet, $hechos, $consultas) {

$hasHechos = hechosHasAnyMeaningful($hechos);

$hasNoticiaCriminal =
  isMeaningfulText($consultas['NOTICIA_CRIMINAL'] ?? '') ||
  isMeaningfulText($consultas['NOTICIA_CRIMINAL_OBSERVACIONES'] ?? '');
  // Si hay algo, NO ocultar
  if ($hasHechos || $hasNoticiaCriminal) return false;

  // ✅ fila exacta de NOVEDAD (como en tu imagen)
  $rowNovedad = findRowByText($sheet, 'NOVEDAD');
  if ($rowNovedad <= 0) return false;

  // ocultar desde NOVEDAD hasta antes de la siguiente sección principal
  $nextRow = findFirstRowAfterAmongTexts($sheet, $rowNovedad, [
    'CONSULTA HISTORIAL FINANCIERO',
    '1. HISTORIA LABORAL',
    'HISTORIA LABORAL',
    'CONSULTA ACADEMICA',
    'CONSULTA ACADÉMICA'
  ]);

  $endRow = ($nextRow > 0) ? ($nextRow - 1) : ($rowNovedad + 18);

  hideRowsRange($sheet, $rowNovedad, $endRow);
  return true;
}






// ================== ✅ HELPERS EXTRA: BUSCAR POR "CONTENGA" + OCULTAR BLOQUE HASTA LA SIGUIENTE SECCIÓN ==================

function findCellContainsText($sheet, $needlePart) {
  $needle = _normLabel($needlePart);

  $highestRow = $sheet->getHighestRow();
  $highestColIndex = Coordinate::columnIndexFromString($sheet->getHighestColumn());

  for ($r = 1; $r <= $highestRow; $r++) {
    for ($c = 1; $c <= $highestColIndex; $c++) {
      $addr = cellAddr($c, $r);
      $val = _normLabel($sheet->getCell($addr)->getValue());
      if ($val !== '' && mb_strpos($val, $needle) !== false) {
        return ['row' => $r, 'col' => $c];
      }
    }
  }
  return null;
}

function findNextSectionRowAfter($sheet, $startRow, $sectionTitles) {
  $startRow = (int)$startRow;
  $best = 0;

  foreach ($sectionTitles as $t) {
    $pos = findCellContainsText($sheet, $t);
    if ($pos && (int)$pos['row'] > $startRow) {
      $r = (int)$pos['row'];
      if ($best === 0 || $r < $best) $best = $r;
    }
  }
  return $best; // 0 si no encuentra nada
}

function hideBlockFromContainsUntilNext($sheet, $startContains, $fallbackRows = 18) {
  $pos = findCellContainsText($sheet, $startContains);
  if (!$pos) return false;

  $startRow = (int)$pos['row'];

  // Próximas secciones posibles (en tu plantilla)
  $nextRow = findNextSectionRowAfter($sheet, $startRow, [
    'NOVEDAD',
    'CONSULTA HISTORIAL FINANCIERO',
    '1. HISTORIA LABORAL',
    'HISTORIA LABORAL',
    'CONSULTA ACADEMICA',
    'CONSULTA ACADÉMICA'
  ]);

  $endRow = ($nextRow > 0) ? ($nextRow - 1) : ($startRow + (int)$fallbackRows);

  return hideRowsRange($sheet, $startRow, $endRow);
}




// ================== ✅ HELPERS: DETECTAR JSON VACÍO + OCULTAR BLOQUES POR ANCHOR ==================

function detailHasAnyValue($arr) {
  if (!is_array($arr) || empty($arr)) return false;

  foreach ($arr as $k => $v) {
    if (is_array($v)) {
      if (detailHasAnyValue($v)) return true;
      continue;
    }
    if (is_object($v)) {
      if (detailHasAnyValue((array)$v)) return true;
      continue;
    }

    $s = trim((string)$v);
    if ($s !== '') return true;
  }
  return false;
}

function hideRowsRange($sheet, $rowStart, $rowEnd, $clearColsStart = 1, $clearColsEnd = 12) {
  $rowStart = (int)$rowStart;
  $rowEnd   = (int)$rowEnd;

  if ($rowStart <= 0 || $rowEnd <= 0 || $rowEnd < $rowStart) return false;

  for ($r = $rowStart; $r <= $rowEnd; $r++) {
    // limpiar valores (recomendado)
    for ($c = (int)$clearColsStart; $c <= (int)$clearColsEnd; $c++) {
      $sheet->setCellValue(cellAddr($c, $r), '');
    }

    // ✅ FORZAR ocultamiento (visible=false + altura 0)
    $rd = $sheet->getRowDimension($r);
    $rd->setVisible(false);
    $rd->setRowHeight(0);
    $rd->setCollapsed(true);
    $rd->setOutlineLevel(1);
  }

  return true;
}


/**
 * Oculta el bloque desde startLabel hasta (endLabel - 1).
 * Si endLabel no existe, oculta startLabel + fallbackRows.
 */
function hideBlockBetweenLabels($sheet, $startLabel, $endLabel = null, $fallbackRows = 12) {
  $startPos = findCellByText($sheet, $startLabel);
  if (!$startPos) return false;

  $startRow = (int)$startPos['row'];
  $endRow = 0;

  if ($endLabel) {
    $endPos = findCellByText($sheet, $endLabel);
    if ($endPos) $endRow = (int)$endPos['row'] - 1;
  }

  if ($endRow <= 0) {
    $endRow = $startRow + (int)$fallbackRows;
  }

  return hideRowsRange($sheet, $startRow, $endRow);
}




// ================== HELPERS ==================
function v($arr, $key, $default = '') {
  return isset($arr[$key]) && $arr[$key] !== null ? $arr[$key] : $default;
}
function upper($s) {
  return mb_strtoupper(trim((string)$s), 'UTF-8');
}
function safeJsonArray($val) {
  if (!isset($val) || $val === null) return null;
  if (is_array($val)) return $val;
  if (is_object($val)) return (array)$val;

  $str = trim((string)$val);
  if ($str === '') return null;

  $decoded = json_decode($str, true);
  if (json_last_error() === JSON_ERROR_NONE && is_array($decoded)) return $decoded;
  return null;
}
function jsonPick($arr, $key, $default = '') {
  if (!is_array($arr)) return $default;
  return isset($arr[$key]) && $arr[$key] !== null ? $arr[$key] : $default;
}

// ✅ helper SI/NO robusto
function isYes($v) {
  if ($v === null) return false;
  $s = mb_strtoupper(trim((string)$v), 'UTF-8');
  if ($s === '') return false;
  return in_array($s, ['SI','S','1','TRUE','VERDADERO','YES','Y'], true);
}

// ================== HELPERS DINÁMICOS (CONSULTA ACADÉMICA / HISTORIAL / LABORAL / NOVEDAD) ==================
function cellAddr($colIndex, $row) {
  return Coordinate::stringFromColumnIndex((int)$colIndex) . (int)$row;
}

function findRowByText($sheet, $textNeedle) {
  $needle = mb_strtoupper(trim($textNeedle), 'UTF-8');

  $highestRow = $sheet->getHighestRow();
  $highestColIndex = Coordinate::columnIndexFromString($sheet->getHighestColumn());

  for ($r = 1; $r <= $highestRow; $r++) {
    for ($c = 1; $c <= $highestColIndex; $c++) {
      $addr = cellAddr($c, $r);
      $val = $sheet->getCell($addr)->getValue();
      $val = mb_strtoupper(trim((string)$val), 'UTF-8');
      if ($val === $needle) return $r;
    }
  }
  return 0;
}

function copyBlockWithStylesAndMerges($sheet, $srcStartRow, $srcEndRow, $dstStartRow) {
  $blockRows = $srcEndRow - $srcStartRow + 1;

  // Insertar filas antes del destino
  $sheet->insertNewRowBefore($dstStartRow, $blockRows);

  $highestColIndex = Coordinate::columnIndexFromString($sheet->getHighestColumn());

  // Copiar alturas + valores + estilos
  for ($i = 0; $i < $blockRows; $i++) {
    $srcRow = $srcStartRow + $i;
    $dstRow = $dstStartRow + $i;

    $sheet->getRowDimension($dstRow)->setRowHeight($sheet->getRowDimension($srcRow)->getRowHeight());

    for ($c = 1; $c <= $highestColIndex; $c++) {
      $srcAddr = cellAddr($c, $srcRow);
      $dstAddr = cellAddr($c, $dstRow);

      $srcCell = $sheet->getCell($srcAddr);
      $sheet->setCellValue($dstAddr, $srcCell->getValue());

      // duplicar estilo
      $sheet->duplicateStyle($sheet->getStyle($srcAddr), $dstAddr);
    }
  }

  // Copiar merges que estén dentro del bloque fuente
  $merges = $sheet->getMergeCells();
  foreach ($merges as $range) {
    [$start, $end] = explode(':', $range);
    [$sc, $sr] = Coordinate::coordinateFromString($start);
    [$ec, $er] = Coordinate::coordinateFromString($end);

    $sr = (int)$sr; $er = (int)$er;

    if ($sr >= $srcStartRow && $er <= $srcEndRow) {
      $offset = $dstStartRow - $srcStartRow;
      $newRange = $sc . ($sr + $offset) . ':' . $ec . ($er + $offset);
      $sheet->mergeCells($newRange);
    }
  }
}

/**
 * ================== ✅ NORMALIZADOR + DETECTOR DE MERGES + FILL INTELIGENTE ==================
 */
function _normLabel($s) {
  $s = trim((string)$s);
  $s = mb_strtoupper($s, 'UTF-8');

  $s = str_replace(['Á','É','Í','Ó','Ú','Ü','Ñ'], ['A','E','I','O','U','U','N'], $s);
  $s = preg_replace('/\s+/', ' ', $s);
  $s = rtrim($s, ':');

  return $s;
}

function _mergedRangeForCell($sheet, $colIndex, $row) {
  foreach ($sheet->getMergeCells() as $range) {
    [$start, $end] = explode(':', $range);
    [$sc, $sr] = Coordinate::coordinateFromString($start);
    [$ec, $er] = Coordinate::coordinateFromString($end);

    $sr = (int)$sr; $er = (int)$er;
    $sci = Coordinate::columnIndexFromString($sc);
    $eci = Coordinate::columnIndexFromString($ec);

    if ($row >= $sr && $row <= $er && $colIndex >= $sci && $colIndex <= $eci) {
      return ['startCol' => $sci, 'endCol' => $eci, 'startRow' => $sr, 'endRow' => $er];
    }
  }
  return null;
}

function fillSectionByLabels($sheet, $startRow, $endRow, $labelToValue) {
  $highestColIndex = Coordinate::columnIndexFromString($sheet->getHighestColumn());

  $map = [];
  foreach ($labelToValue as $label => $value) {
    $map[_normLabel($label)] = $value;
  }

  for ($r = $startRow; $r <= $endRow; $r++) {
    for ($c = 1; $c <= $highestColIndex; $c++) {
      $addr = cellAddr($c, $r);
      $raw = $sheet->getCell($addr)->getValue();
      $txt = _normLabel($raw);

      if ($txt === '') continue;

      if (isset($map[$txt])) {
        $value = (string)$map[$txt];

        // Institución Educativa siempre va en columna E
        if ($txt === 'INSTITUCION EDUCATIVA' || $txt === 'INSTITUCION') {
          $targetAddr = cellAddr(5, $r); // Columna E
          $sheet->setCellValue($targetAddr, $value);
          $sheet->getStyle($targetAddr)->getAlignment()->setWrapText(true);
          continue;
        }

        $merge = _mergedRangeForCell($sheet, $c, $r);
        $startWriteCol = $merge ? ($merge['endCol'] + 1) : ($c + 1);

        if ($startWriteCol > $highestColIndex) continue;

        $m2 = _mergedRangeForCell($sheet, $startWriteCol, $r);
        if ($m2) {
          $startWriteCol = $m2['startCol'];
        }

        $targetAddr = cellAddr($startWriteCol, $r);
        $sheet->setCellValue($targetAddr, $value);
        $sheet->getStyle($targetAddr)->getAlignment()->setWrapText(true);
      }
    }
  }
}

// ================== ✅ NUEVOS HELPERS: BUSCAR CELDA POR TEXTO + ESCRIBIR A LA DERECHA (SIN Celdas fijas) ==================
function findCellByText($sheet, $textNeedle) {
  $needle = _normLabel($textNeedle);

  $highestRow = $sheet->getHighestRow();
  $highestColIndex = Coordinate::columnIndexFromString($sheet->getHighestColumn());

  for ($r = 1; $r <= $highestRow; $r++) {
    for ($c = 1; $c <= $highestColIndex; $c++) {
      $addr = cellAddr($c, $r);
      $val = _normLabel($sheet->getCell($addr)->getValue());
      if ($val === $needle) return ['row' => $r, 'col' => $c];
    }
  }
  return null;
}



// ✅ Busca una celda por texto PERO empezando desde una fila (evita la tabla ENTIDAD/OBSERVACIONES)
function findCellByTextAfterRow($sheet, $textNeedle, $afterRow = 0) {
  $needle = _normLabel($textNeedle);

  $highestRow = $sheet->getHighestRow();
  $highestColIndex = Coordinate::columnIndexFromString($sheet->getHighestColumn());

  $startRow = max(1, (int)$afterRow + 1);

  for ($r = $startRow; $r <= $highestRow; $r++) {
    for ($c = 1; $c <= $highestColIndex; $c++) {
      $addr = cellAddr($c, $r);
      $val = _normLabel($sheet->getCell($addr)->getValue());
      if ($val === $needle) return ['row' => $r, 'col' => $c];
    }
  }
  return null;
}

// ✅ Igual que setObservationBelowBlock, pero anclando el label DESPUÉS de cierta fila (ej. 18)
function setObservationBelowBlockAfterRow($sheet, $anchorLabel, $text, $afterRow = 18, $maxRows = 15, $colStart = 2, $colEnd = 9) {
  $text = trim((string)$text);
  if ($text === '') return false;

  $pos = findCellByTextAfterRow($sheet, $anchorLabel, $afterRow);
  if (!$pos) return false;

  $startRow = (int)$pos['row'] + 1;
  $endRow   = $startRow + (int)$maxRows;

  $highestRow = $sheet->getHighestRow();
  if ($endRow > $highestRow) $endRow = $highestRow;

  for ($r = $startRow; $r <= $endRow; $r++) {
    if (isRowEmptyInCols($sheet, $r, $colStart, $colEnd)) {

      $addr = cellAddr($colStart, $r);

      // merge B:I si no existe
      $merge = _mergedRangeForCell($sheet, $colStart, $r);
      if (!$merge) {
        $sheet->mergeCells(cellAddr($colStart, $r) . ':' . cellAddr($colEnd, $r));
      }

      // limpiar J por si antes quedó escrito ahí
      $sheet->setCellValue(cellAddr($colEnd + 1, $r), '');

      $sheet->setCellValue($addr, $text);
      $sheet->getStyle($addr)->getAlignment()->setWrapText(true);
      $sheet->getStyle($addr)->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);

      return true;
    }
  }

  return false;
}

function findCellByTextInRange($sheet, $textNeedle, $rowStart, $rowEnd) {
  $needle = _normLabel($textNeedle);

  $highestRow = $sheet->getHighestRow();
  $rowStart = max(1, (int)$rowStart);
  $rowEnd   = min((int)$rowEnd, (int)$highestRow);

  $highestColIndex = Coordinate::columnIndexFromString($sheet->getHighestColumn());

  for ($r = $rowStart; $r <= $rowEnd; $r++) {
    for ($c = 1; $c <= $highestColIndex; $c++) {
      $addr = cellAddr($c, $r);
      $val = _normLabel($sheet->getCell($addr)->getValue());
      if ($val === $needle) return ['row' => $r, 'col' => $c];
    }
  }
  return null;
}

function setValueRightOfPos($sheet, $row, $col, $value) {
  $r = (int)$row;
  $c = (int)$col;

  $highestColIndex = Coordinate::columnIndexFromString($sheet->getHighestColumn());

  $merge = _mergedRangeForCell($sheet, $c, $r);
  $writeCol = $merge ? ($merge['endCol'] + 1) : ($c + 1);

  if ($writeCol > $highestColIndex) return false;

  $m2 = _mergedRangeForCell($sheet, $writeCol, $r);
  if ($m2) $writeCol = $m2['startCol'];

  $targetAddr = cellAddr($writeCol, $r);
  $sheet->setCellValue($targetAddr, (string)$value);
  $sheet->getStyle($targetAddr)->getAlignment()->setWrapText(true);
  return true;
}

function setValueRightOfLabel($sheet, $label, $value) {
  $pos = findCellByText($sheet, $label);
  if (!$pos) return false;
  return setValueRightOfPos($sheet, $pos['row'], $pos['col'], $value);
}

// ✅ Para evitar conflictos con labels repetidos (ej. "Fecha"), se usa un anchor y un rango cercano
function setValueRightOfLabelNear($sheet, $anchorLabel, $label, $value, $maxRows = 8) {
  $anchorPos = findCellByText($sheet, $anchorLabel);
  if (!$anchorPos) return false;

  $rowStart = (int)$anchorPos['row'];
  $rowEnd   = $rowStart + (int)$maxRows;

  $pos = findCellByTextInRange($sheet, $label, $rowStart, $rowEnd);
  if (!$pos) return false;

  return setValueRightOfPos($sheet, $pos['row'], $pos['col'], $value);
}

/**
 * ================== ✅ FIX: OBSERVACIONES DE CABECERA DEBEN IR DEBAJO (NO A LA DERECHA)
 * En tu plantilla, "Observación ..." está mergeado (B:I), por eso escribir "a la derecha" termina en J.
 * Estas funciones escriben el texto en la primera fila vacía dentro del bloque (B:I), debajo de las líneas.
 */
function isRowEmptyInCols($sheet, $row, $colStart, $colEnd) {
  for ($c = $colStart; $c <= $colEnd; $c++) {
    $v = trim((string)$sheet->getCell(cellAddr($c, $row))->getValue());
    if ($v !== '') return false;
  }
  return true;
}

/**
 * Escribe un texto en la PRIMERA fila vacía debajo de un anchor (label),
 * dentro del rango B:I (por defecto).
 * Si la fila no está mergeada, la mergea para que quede bonito.
 */
function setObservationBelowBlock($sheet, $anchorLabel, $text, $maxRows = 15, $colStart = 2, $colEnd = 9) {
  $text = trim((string)$text);
  if ($text === '') return false;

  $pos = findCellByText($sheet, $anchorLabel);
  if (!$pos) return false;

  $startRow = (int)$pos['row'] + 1;
  $endRow   = $startRow + (int)$maxRows;

  $highestRow = $sheet->getHighestRow();
  if ($endRow > $highestRow) $endRow = $highestRow;

  for ($r = $startRow; $r <= $endRow; $r++) {
    // fila vacía en B:I (donde van las líneas)
    if (isRowEmptyInCols($sheet, $r, $colStart, $colEnd)) {

      $addr = cellAddr($colStart, $r);

      // merge B:I si no existe
      $merge = _mergedRangeForCell($sheet, $colStart, $r);
      if (!$merge) {
        $sheet->mergeCells(cellAddr($colStart, $r) . ':' . cellAddr($colEnd, $r));
      }

      // limpiar J por si antes quedó escrito ahí
      $sheet->setCellValue(cellAddr($colEnd + 1, $r), '');

      $sheet->setCellValue($addr, $text);
      $sheet->getStyle($addr)->getAlignment()->setWrapText(true);
      $sheet->getStyle($addr)->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);

      return true;
    }
  }

  return false;
}

// ================== ESTUDIO + CANDIDATO (+ EMPRESA + TIPO DOC) ==================
$sql = "
SELECT
  es.*,
  UPPER(c.NOMBRE_CANDIDATO)  AS NOMBRE_CANDIDATO,
  UPPER(c.APELLIDO_CANDIDATO) AS APELLIDO_CANDIDATO,
  c.NUMERO_DOCUMENTO_CANDIDATO AS NUMERO_DOCUMENTO_CANDIDATO_CAND,
  c.EMPRESA_CANDIDATO,
  td.NOMBRE_DOCUMENTO,
  ec.NOMBRE_EMPRESA
FROM ESTUDIO_SEGURIDAD es
INNER JOIN CANDIDATO_EMPRESA c
  ON es.ID_CANDIDATO_EMPRESA = c.ID_EMPRESA
INNER JOIN EMPRESA_CLIENTE ec
  ON ec.ID_EMPRESA = c.EMPRESA_CANDIDATO
INNER JOIN TIPO_DOCUMENTOS td
  ON td.ID_DOCUMENTO = c.TIPO_DOCUMENTO_CANDIDATO
WHERE es.ID_ESTUDIO = ?
LIMIT 1
";

$stmt = $mysqli->prepare($sql);
if (!$stmt) {
  http_response_code(500);
  echo "Error preparando consulta: " . $mysqli->error;
  exit;
}
$stmt->bind_param("i", $idEstudio);
$stmt->execute();
$estudio = $stmt->get_result()->fetch_assoc();
$stmt->close();

if (!$estudio) {
  http_response_code(404);
  echo "Estudio no encontrado";
  exit;
}

$idCandidato = (int)$estudio['ID_CANDIDATO_EMPRESA'];

// ================== CONSULTAS ENTIDADES ==================
$sql = "
SELECT 
  ID_CONSULTA_ENTIDAD, ID_ESTUDIO, ID_CANDIDATO_EMPRESA,
  UPPER(PROCURADURIA) AS PROCURADURIA, PROCURADURIA_DETALLE_JSON,
  UPPER(POLICIA_NACIONAL) AS POLICIA_NACIONAL, POLICIA_NACIONAL_DETALLE_JSON,
  UPPER(NOTICIA_CRIMINAL) AS NOTICIA_CRIMINAL, UPPER(NOTICIA_CRIMINAL_OBSERVACIONES) AS NOTICIA_CRIMINAL_OBSERVACIONES,
  UPPER(RUNT_CE) AS RUNT_CE, RUNT_CE_DETALLE_JSON,
  UPPER(RAMA_JUDICIAL_PP_CE) AS RAMA_JUDICIAL_PP_CE, RAMA_JUDICIAL_PP_CE_DETALLE_JSON,
  USUARIO_CREA, FECHA_CREACION, ESTADO_REGISTRO
FROM CONSULTAS_ENTIDADES
WHERE ID_CANDIDATO_EMPRESA = ?
  AND (ID_ESTUDIO = ? OR ID_ESTUDIO IS NULL)
  AND ESTADO_REGISTRO = 1
ORDER BY ID_CONSULTA_ENTIDAD DESC
LIMIT 1
";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("ii", $idCandidato, $idEstudio);
$stmt->execute();
$consultas = $stmt->get_result()->fetch_assoc();
$stmt->close();

if (!$consultas) $consultas = [];

// ================== HECHOS NOVEDADES ==================
$hechos = [];
$sql = "
SELECT 
  ID_HECHO_NOVEDAD, ID_CANDIDATO_EMPRESA, ID_ESTUDIO,
  UPPER(TIPO_NOTICIA) AS TIPO_NOTICIA,
  UPPER(DELITO) AS DELITO,
  UPPER(CALIDAD) AS CALIDAD,
  FECHA
FROM HECHOS_NOVEDADES
WHERE ID_CANDIDATO_EMPRESA = ?
  AND (ID_ESTUDIO = ? OR ID_ESTUDIO IS NULL)
  AND ESTADO_REGISTRO = 1
ORDER BY ID_HECHO_NOVEDAD DESC
";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("ii", $idCandidato, $idEstudio);
$stmt->execute();
$res = $stmt->get_result();
while ($row = $res->fetch_assoc()) $hechos[] = $row;
$stmt->close();

// ================== HISTORIAL FINANCIERO ==================
$historial = [];
$sql = "
SELECT 
  ID_HISTORIAL_FINANCIERO, ID_ESTUDIO, ID_CANDIDATO_EMPRESA,
  CANTIDAD_OBLIGACIONES,
  UPPER(EMPRESAS_CON_REPORTE) AS EMPRESAS_CON_REPORTE,
  REPORTADO_NEGATIVAMENTE,
  CARTERA_CASTIGADA,
  UPPER(OBSERVACIONES) AS OBSERVACIONES, SECTOR
FROM HISTORIAL_FINANCIERO
WHERE ID_CANDIDATO_EMPRESA = ?
  AND (ID_ESTUDIO = ? OR ID_ESTUDIO IS NULL)
  AND (ESTADO_REGISTRO = 1 OR ESTADO_REGISTRO IS NULL)
ORDER BY ID_HISTORIAL_FINANCIERO DESC
";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("ii", $idCandidato, $idEstudio);
$stmt->execute();
$res = $stmt->get_result();
while ($row = $res->fetch_assoc()) $historial[] = $row;
$stmt->close();

// ================== REFERENCIAS LABORALES ==================
$refsLab = [];
$sql = "
SELECT 
  ID_REFERENCIA_LABORAL, ID_ESTUDIO, ID_CANDIDATO_EMPRESA,
  UPPER(EMPRESA) AS EMPRESA,
  UPPER(CARGO) AS CARGO,
  UPPER(MOTIVO_RETIRO) AS MOTIVO_RETIRO,
  FECHA_INICIO,
  FECHA_RETIRO,
  DESEMPENO,
  TELEFONO,
  UPPER(CIUDAD) AS CIUDAD,
  UPPER(REFERENCIA_NOMBRE) AS REFERENCIA_NOMBRE,
  UPPER(REFERENCIA_CARGO) AS REFERENCIA_CARGO
FROM REFERENCIAS_LABORALES
WHERE ID_CANDIDATO_EMPRESA = ?
  AND (ID_ESTUDIO = ? OR ID_ESTUDIO IS NULL)
  AND ESTADO_REGISTRO = 1
ORDER BY ID_REFERENCIA_LABORAL DESC
";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("ii", $idCandidato, $idEstudio);
$stmt->execute();
$res = $stmt->get_result();
while ($row = $res->fetch_assoc()) $refsLab[] = $row;
$stmt->close();

// ================== REFERENCIAS EDUCATIVAS ==================
$refsEdu = [];
$sql = "
SELECT 
  ID_REFERENCIA_EDUCATIVA, ID_ESTUDIO, ID_CANDIDATO_EMPRESA,
  UPPER(INSTITUCION) AS INSTITUCION,
  UPPER(TITULO_OBTENIDO) AS TITULO_OBTENIDO,
  FECHA_FIN,
  UPPER(CIUDAD) AS CIUDAD,
  UPPER(NUMERO_ACTA) AS NUMERO_ACTA,
  UPPER(FOLIO) AS FOLIO,
  UPPER(LIBRO) AS LIBRO,
  UPPER(CONTACTO_NOMBRE) AS CONTACTO_NOMBRE,
  UPPER(CONTACTO_CARGO) AS CONTACTO_CARGO,
  UPPER(OBSERVACIONES) AS OBSERVACIONES
FROM REFERENCIAS_EDUCATIVAS
WHERE ID_CANDIDATO_EMPRESA = ?
  AND ID_ESTUDIO = ?
  AND ESTADO_REGISTRO = 1
ORDER BY ID_REFERENCIA_EDUCATIVA DESC
";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("ii", $idCandidato, $idEstudio);
$stmt->execute();
$res = $stmt->get_result();
while ($row = $res->fetch_assoc()) $refsEdu[] = $row;
$stmt->close();

// ================== CARGAR PLANTILLA NUEVA ==================
$templatePath = __DIR__ . "/Plantilla Estudio Estandar 2024.xlsx";
if (!file_exists($templatePath)) {
  http_response_code(500);
  echo "No se encontró la plantilla: " . $templatePath;
  exit;
}

$spreadsheet = IOFactory::load($templatePath);
$sheet = $spreadsheet->getSheetByName('ESTUDIO NORMAL');
if (!$sheet) $sheet = $spreadsheet->getActiveSheet();

// ================== ✅ OCULTAR CONSULTA ACADÉMICA SI NO HAY DATOS ==================
hideHistoriaLaboralIfEmpty($sheet, $refsLab);
hideConsultaAcademicaIfEmpty($sheet, $refsEdu);
// hideNovedadIfEmptyStrict($sheet, $hechos, $consultas);



// ================== IDENTIFICACIÓN ==================
$nombreCompleto  = trim(v($estudio,'NOMBRE_CANDIDATO').' '.v($estudio,'APELLIDO_CANDIDATO'));
$estadoTxt       = ((int)v($estudio,'ESTADO_ESTUDIO',1) === 1) ? 'ACTIVO' : 'INACTIVO';
$tipoDocumento   = upper(v($estudio,'NOMBRE_DOCUMENTO',''));
$numeroDocumento = v($estudio,'NUMERO_DOCUMENTO_CANDIDATO_CAND','');

$sheet->setCellValue('D7', $nombreCompleto);
$sheet->setCellValue('C7', $estadoTxt);

// Limpieza doc
$sheet->setCellValue('D8',  '');
$sheet->setCellValue('D9',  '');
$sheet->setCellValue('D10', '');
$sheet->setCellValue('H10', '');

if ($tipoDocumento === 'CEDULA DE CIUDADANIA') {
  $sheet->setCellValue('D10', $numeroDocumento);
} elseif ($tipoDocumento === 'CEDULA DE EXTRANJERIA') {
  $sheet->setCellValue('D8', $numeroDocumento);
}

$sheet->setCellValue('D11', v($estudio,'NOMBRE_EMPRESA',''));
$sheet->setCellValue('H11', substr((string)v($estudio,'FECHA_CREACION',''), 0, 10));
$sheet->setCellValue('D12', v($estudio,'CARGO_ASPIRA',''));


// ================== ✅ NOVEDAD DINÁMICA (HECHOS_NOVEDADES) ==================
$novedadHeaderRow = findRowByText($sheet, 'NOVEDAD');



if ($novedadHeaderRow > 0 && !empty($hechos)) {

  // Según tu imagen: NOVEDAD (fila header) + 2 filas de campos
  $sectionHeight = 3;

  $templateStart = $novedadHeaderRow;
  $templateEnd   = $novedadHeaderRow + ($sectionHeight - 1);

  // Cursor para insertar sin desfasar
  $insertAt = $templateEnd + 1;
  for ($i = 1; $i < count($hechos); $i++) {
    copyBlockWithStylesAndMerges($sheet, $templateStart, $templateEnd, $insertAt);
    $insertAt += $sectionHeight;
  }

  // Llenar cada bloque
  for ($i = 0; $i < count($hechos); $i++) {
    $secStart = $templateStart + ($sectionHeight * $i);
    $secEnd   = $secStart + ($sectionHeight - 1);

    $h = $hechos[$i];

    $estadoVal = '';
    if (isset($h['ESTADO']) && trim((string)$h['ESTADO']) !== '') {
      $estadoVal = v($h, 'ESTADO', '');
    } else {
      $estadoVal = '';
    }

    $labelToValue = [
      'Tipo de Noticia' => v($h, 'TIPO_NOTICIA', ''),
      'Delito'          => v($h, 'DELITO', ''),
      'Calidad'         => v($h, 'CALIDAD', ''),
      'Estado'          => $estadoVal,
      'Fecha'           => v($h, 'FECHA', ''),
    ];

    fillSectionByLabels($sheet, $secStart, $secEnd, $labelToValue);

    $sheet->getRowDimension($secStart)->setRowHeight($sheet->getRowDimension($templateStart)->getRowHeight());
    $sheet->getRowDimension($secStart + 1)->setRowHeight($sheet->getRowDimension($templateStart + 1)->getRowHeight());
    $sheet->getRowDimension($secStart + 2)->setRowHeight($sheet->getRowDimension($templateStart + 2)->getRowHeight());
  }
}

hideNovedadIfD21Empty($sheet);
hideHistorialHechosIfD22EmptyAfterColon($sheet);



// ================== ✅ CONSULTA HISTORIAL FINANCIERO (SUMA / CONCAT / SI-NO) ==================
$hfHeaderRow = findRowByText($sheet, 'CONSULTA HISTORIAL FINANCIERO');
if ($hfHeaderRow <= 0) $hfHeaderRow = findRowByText($sheet, 'CONSULTA HISTORIAL FINANCIERO ');

if ($hfHeaderRow > 0 && !empty($historial)) {
  $secStart = $hfHeaderRow;
  $secEnd   = $hfHeaderRow + 6;

  $sumOblig = 0;
  $sectores = [];
  $empresas = [];
  $repNeg   = false;
  $cartera  = false;
  $obsParts = [];

  foreach ($historial as $h) {
    $cantRaw = v($h, 'CANTIDAD_OBLIGACIONES', 0);
    $cantNum = (int)preg_replace('/[^0-9\-]/', '', (string)$cantRaw);
    $sumOblig += $cantNum;

    $sectorVal = trim((string)v($h, 'SECTOR', ''));
    if ($sectorVal !== '') {
      foreach (explode(',', $sectorVal) as $p) {
        $p = trim($p);
        if ($p !== '') $sectores[$p] = true;
      }
    }

    $emp = trim((string)v($h, 'EMPRESAS_CON_REPORTE', ''));
    if ($emp !== '') {
      foreach (explode(',', $emp) as $p) {
        $p = trim($p);
        if ($p !== '') $empresas[$p] = true;
      }
    }

    $repNeg = $repNeg || isYes(v($h, 'REPORTADO_NEGATIVAMENTE', ''));
    $cartera = $cartera || isYes(v($h, 'CARTERA_CASTIGADA', ''));

    $ob = trim((string)v($h, 'OBSERVACIONES', ''));
    if ($ob !== '') $obsParts[] = $ob;
  }

  $sectorTxt = implode(', ', array_keys($sectores));
  $empTxt    = implode(', ', array_keys($empresas));
  $obsTxt    = implode("\n", $obsParts);

  $labelToValue = [
    'Cantidad de obligaciones activas' => (string)$sumOblig,
    'Sector al que pertenecen' => $sectorTxt,
    'Esta reportado negativamente' => $repNeg ? 'SI' : 'NO',
    'Si esta reportado que empresas son' => $empTxt,
    'Presenta cartera castigada' => $cartera ? 'SI' : 'NO',
    'Observaciones' => $obsTxt,
    'Observaciones:' => $obsTxt,
  ];

  fillSectionByLabels($sheet, $secStart, $secEnd, $labelToValue);
  $sheet->getRowDimension($hfHeaderRow + 6)->setRowHeight(45);
}

// ================== ✅ HISTORIA LABORAL DINÁMICA (SECCIÓN POR LÍNEA) ==================
$labHeaderRow = findRowByText($sheet, '1. HISTORIA LABORAL');
if ($labHeaderRow <= 0) $labHeaderRow = findRowByText($sheet, 'HISTORIA LABORAL');

if ($labHeaderRow > 0 && !empty($refsLab)) {

  $sectionHeight = 6;

  $templateStart = $labHeaderRow;
  $templateEnd   = $labHeaderRow + ($sectionHeight - 1);

  $insertAt = $templateEnd + 1;
  for ($i = 1; $i < count($refsLab); $i++) {
    copyBlockWithStylesAndMerges($sheet, $templateStart, $templateEnd, $insertAt);
    $insertAt += $sectionHeight;
  }

  for ($i = 0; $i < count($refsLab); $i++) {
    $secStart = $templateStart + ($sectionHeight * $i);
    $secEnd   = $secStart + ($sectionHeight - 1);

    $r = $refsLab[$i];

    $labelToValue = [
      'Empresa' => v($r, 'EMPRESA', ''),
      'Cargo'   => v($r, 'CARGO', ''),

      'Motivo de Retiro' => v($r, 'MOTIVO_RETIRO', ''),

      'Inicio' => v($r, 'FECHA_INICIO', ''),
      'Retiro' => v($r, 'FECHA_RETIRO', ''),

      'Desempeño' => v($r, 'DESEMPENO', ''),
      'Desempeno' => v($r, 'DESEMPENO', ''),

      'Teléfono' => v($r, 'TELEFONO', ''),
      'Telefono' => v($r, 'TELEFONO', ''),

      'Ciudad' => v($r, 'CIUDAD', ''),

      'Referencia'  => v($r, 'REFERENCIA_NOMBRE', ''),
      'Referencia:' => v($r, 'REFERENCIA_NOMBRE', ''),

      // Observación no se usa aquí (la plantilla la trae, pero tú no la llenas)
      'Observación'  => '',
      'Observacion'  => '',
      'Observación:' => '',
      'Observacion:' => '',
    ];

    fillSectionByLabels($sheet, $secStart, $secEnd, $labelToValue);

    // ✅ Refuerzo: llenar "Cargo" (referencia) dentro de este bloque (segundo "Cargo")
    $highestColIndex = Coordinate::columnIndexFromString($sheet->getHighestColumn());
    $foundFirstCargo = false;
    for ($rr = $secStart; $rr <= $secEnd; $rr++) {
      for ($cc = 1; $cc <= $highestColIndex; $cc++) {
        $addr = cellAddr($cc, $rr);
        if (_normLabel($sheet->getCell($addr)->getValue()) === 'CARGO') {
          if (!$foundFirstCargo) {
            $foundFirstCargo = true;
          } else {
            setValueRightOfPos($sheet, $rr, $cc, v($r, 'REFERENCIA_CARGO', ''));
            break 2;
          }
        }
      }
    }

    $sheet->getRowDimension($secEnd)->setRowHeight(35);
  }
}

// ================== CONSULTA ACADÉMICA DINÁMICA ==================
$acadHeaderRow = findRowByText($sheet, 'CONSULTA ACADÉMICA');
if ($acadHeaderRow <= 0) $acadHeaderRow = findRowByText($sheet, 'CONSULTA ACADEMICA');

if ($acadHeaderRow > 0 && !empty($refsEdu)) {

  $sectionHeight = 6;

  $templateStart = $acadHeaderRow;
  $templateEnd   = $acadHeaderRow + ($sectionHeight - 1);

  $insertAt = $templateEnd + 1;
  for ($i = 1; $i < count($refsEdu); $i++) {
    copyBlockWithStylesAndMerges($sheet, $templateStart, $templateEnd, $insertAt);
    $insertAt += $sectionHeight;
  }

  for ($i = 0; $i < count($refsEdu); $i++) {
    $secStart = $templateStart + ($sectionHeight * $i);
    $secEnd   = $secStart + ($sectionHeight - 1);

    $ref = $refsEdu[$i];

    $labelToValue = [
      'Institución Educativa' => v($ref, 'INSTITUCION', ''),
      'Institucion Educativa' => v($ref, 'INSTITUCION', ''),

      'Número de Acta' => v($ref, 'NUMERO_ACTA', ''),
      'Numero de Acta' => v($ref, 'NUMERO_ACTA', ''),

      'Folio' => v($ref, 'FOLIO', ''),
      'Libro' => v($ref, 'LIBRO', ''),

      'Título o Nombre del Curso' => v($ref, 'TITULO_OBTENIDO', ''),
      'Titulo o Nombre del Curso' => v($ref, 'TITULO_OBTENIDO', ''),

      'Ciudad' => v($ref, 'CIUDAD', ''),
      'Fecha'  => v($ref, 'FECHA_FIN', ''),

      'Referencia'  => v($ref, 'CONTACTO_NOMBRE', ''),
      'Referencia:' => v($ref, 'CONTACTO_NOMBRE', ''),

      'Cargo' => v($ref, 'CONTACTO_CARGO', ''),

      'Observación'  => v($ref, 'OBSERVACIONES', ''),
      'Observación:' => v($ref, 'OBSERVACIONES', ''),
      'Observacion'  => v($ref, 'OBSERVACIONES', ''),
      'Observacion:' => v($ref, 'OBSERVACIONES', ''),
    ];

    fillSectionByLabels($sheet, $secStart, $secEnd, $labelToValue);
  }
}

// ================== ✅ LLENADO POR LABEL: PROCURADURÍA / POLICÍA / RUNT / RAMA JUDICIAL ==================

// ---------- PROCURADURÍA ----------
$procDetalle = safeJsonArray(v($consultas, 'PROCURADURIA_DETALLE_JSON', null));

$procObs1 = trim((string) jsonPick($procDetalle, 'observacion', ''));
$procObs2 = trim((string) jsonPick($procDetalle, 'observaciones', ''));
$procObsFinal = trim($procObs1 . ($procObs2 !== '' ? "\n" . $procObs2 : ''));

$procDelito = trim((string) jsonPick($procDetalle, 'delito', ''));
$procFecha  = trim((string) jsonPick($procDetalle, 'fechaProvidencia', ''));
$procPrin   = trim((string) jsonPick($procDetalle, 'sancionPrincipal', ''));
$procAcc    = trim((string) jsonPick($procDetalle, 'sancionAccesoria', ''));
$procInhab  = trim((string) jsonPick($procDetalle, 'inhabilidades', ''));
$procTerm   = trim((string) jsonPick($procDetalle, 'termino', ''));

// ---------- PROCURADURÍA ----------
$procDetalle = safeJsonArray(v($consultas, 'PROCURADURIA_DETALLE_JSON', null));

if (!detailHasAnyValue($procDetalle)) {
  // Oculta desde Procuraduria hasta antes de Policia
  hideBlockBetweenLabels(
    $sheet,
    'Observación Procuraduria General de la Nación',
    'Observación Policía Nacional',
    14
  );
  hideBlockBetweenLabels(
    $sheet,
    'Observacion Procuraduria General de la Nacion',
    'Observación Policía Nacional',
    14
  );
} else {
  $procObs1 = trim((string) jsonPick($procDetalle, 'observacion', ''));
  $procObs2 = trim((string) jsonPick($procDetalle, 'observaciones', ''));
  $procObsFinal = trim($procObs1 . ($procObs2 !== '' ? "\n" . $procObs2 : ''));

  $procDelito = trim((string) jsonPick($procDetalle, 'delito', ''));
  $procFecha  = trim((string) jsonPick($procDetalle, 'fechaProvidencia', ''));
  $procPrin   = trim((string) jsonPick($procDetalle, 'sancionPrincipal', ''));
  $procAcc    = trim((string) jsonPick($procDetalle, 'sancionAccesoria', ''));
  $procInhab  = trim((string) jsonPick($procDetalle, 'inhabilidades', ''));
  $procTerm   = trim((string) jsonPick($procDetalle, 'termino', ''));

  // ✅ OBSERVACIÓN: DEBAJO DEL BLOQUE (B:I)
  setObservationBelowBlock($sheet, 'Observación Procuraduria General de la Nación', $procObsFinal, 20);
  setObservationBelowBlock($sheet, 'Observacion Procuraduria General de la Nacion', $procObsFinal, 20);

  // Campos cercanos (a la derecha dentro del bloque)
  setValueRightOfLabelNear($sheet, 'Observación Procuraduria General de la Nación', 'Delito', $procDelito, 6);
  setValueRightOfLabelNear($sheet, 'Observación Procuraduria General de la Nación', 'Fecha Providencia', $procFecha, 6);

  setValueRightOfLabelNear($sheet, 'Observación Procuraduria General de la Nación', 'Sanción Principal', $procPrin, 6);
  setValueRightOfLabelNear($sheet, 'Observación Procuraduria General de la Nación', 'Sancion Principal', $procPrin, 6);

  setValueRightOfLabelNear($sheet, 'Observación Procuraduria General de la Nación', 'Sanción Accesoria', $procAcc, 6);
  setValueRightOfLabelNear($sheet, 'Observación Procuraduria General de la Nación', 'Sancion Accesoria', $procAcc, 6);

  setValueRightOfLabelNear($sheet, 'Observación Procuraduria General de la Nación', 'Inhabilidades', $procInhab, 6);
  setValueRightOfLabelNear($sheet, 'Observación Procuraduria General de la Nación', 'Término', $procTerm, 6);
  setValueRightOfLabelNear($sheet, 'Observación Procuraduria General de la Nación', 'Termino', $procTerm, 6);
}

// ---------- POLICÍA ----------
$polDetalle = safeJsonArray(v($consultas, 'POLICIA_NACIONAL_DETALLE_JSON', null));

if (!detailHasAnyValue($polDetalle)) {
  // Oculta desde Policia hasta antes de RUNT
  hideBlockBetweenLabels(
    $sheet,
    'Observación Policía Nacional',
    'Observación Registro Único Nacional de Tránsito',
    14
  );
  hideBlockBetweenLabels(
    $sheet,
    'Observación Policia Nacional',
    'Observación Registro Único Nacional de Tránsito',
    14
  );
  hideBlockBetweenLabels(
    $sheet,
    'Observacion Policia Nacional',
    'Observación Registro Único Nacional de Tránsito',
    14
  );
} else {
  $polObs1 = trim((string) jsonPick($polDetalle, 'observacion', ''));
  $polObs2 = trim((string) jsonPick($polDetalle, 'observaciones', ''));
  $polObsFinal = trim($polObs1 . ($polObs2 !== '' ? "\n" . $polObs2 : ''));

  // ✅ OBSERVACIÓN: DEBAJO DEL BLOQUE (B:I)
  setObservationBelowBlock($sheet, 'Observación Policia Nacional', $polObsFinal, 20);
  setObservationBelowBlock($sheet, 'Observación Policía Nacional', $polObsFinal, 20);
  setObservationBelowBlock($sheet, 'Observacion Policia Nacional', $polObsFinal, 20);
}


// ---------- RUNT ----------
$runtDetalle = safeJsonArray(v($consultas, 'RUNT_CE_DETALLE_JSON', null));
$anchorRunt = 'Observación Registro Único Nacional de Tránsito';

if (!detailHasAnyValue($runtDetalle)) {
  // Oculta desde RUNT hasta antes de Rama Judicial
  hideBlockBetweenLabels(
    $sheet,
    $anchorRunt,
    'Observación Rama Judicial',
    16
  );
  hideBlockBetweenLabels(
    $sheet,
    'Observacion Registro Unico Nacional de Transito',
    'Observación Rama Judicial',
    16
  );
} else {
  $runtObs      = trim((string) jsonPick($runtDetalle, 'observacion', ''));
  $runtFecha    = trim((string) jsonPick($runtDetalle, 'fecha', ''));
  $runtVehiculo = trim((string) jsonPick($runtDetalle, 'vehiculoTipo', ''));
  $runtValorPte = trim((string) jsonPick($runtDetalle, 'valorPte', ''));
  $runtCodigo   = trim((string) jsonPick($runtDetalle, 'codigo', ''));
  $runtDesc     = trim((string) jsonPick($runtDetalle, 'descripcion', ''));

  $descFinal = trim($runtObs . ($runtDesc !== '' ? "\n" . $runtDesc : ''));

  // ✅ OBSERVACIÓN: DEBAJO DEL BLOQUE (B:I)
  setObservationBelowBlock($sheet, $anchorRunt, $runtObs, 20);
  setObservationBelowBlock($sheet, 'Observacion Registro Unico Nacional de Transito', $runtObs, 20);

  // Campos cercanos
  setValueRightOfLabelNear($sheet, $anchorRunt, 'Fecha', $runtFecha, 10);
  setValueRightOfLabelNear($sheet, $anchorRunt, 'Vehiculo Tipo', $runtVehiculo, 10);
  setValueRightOfLabelNear($sheet, $anchorRunt, 'Vehículo Tipo', $runtVehiculo, 10);
  setValueRightOfLabelNear($sheet, $anchorRunt, 'Valor Pte', $runtValorPte, 10);
  setValueRightOfLabelNear($sheet, $anchorRunt, 'Codigo', $runtCodigo, 10);
  setValueRightOfLabelNear($sheet, $anchorRunt, 'Código', $runtCodigo, 10);
  setValueRightOfLabelNear($sheet, $anchorRunt, 'Descripción', $descFinal, 12);
  setValueRightOfLabelNear($sheet, $anchorRunt, 'Descripcion', $descFinal, 12);
}




// ---------- RAMA JUDICIAL ----------
$rjDetalle = safeJsonArray(v($consultas, 'RAMA_JUDICIAL_PP_CE_DETALLE_JSON', null));

// ✅ buscamos por "CONTENGA" para que funcione aunque el label venga diferente
if (!detailHasAnyValue($rjDetalle)) {
 
  hideBlockFromContainsUntilNext($sheet, 'OBSERVACION RAMA JUDICIAL', 22);
} else {
  $rjObs          = trim((string) jsonPick($rjDetalle, 'observacion', ''));
  $rjObsExtra     = trim((string) jsonPick($rjDetalle, 'observaciones', ''));
  $rjObsFinal     = trim($rjObs . ($rjObsExtra !== '' ? "\n" . $rjObsExtra : ''));

  $rjClaseProceso = trim((string) jsonPick($rjDetalle, 'claseProceso', ''));
  $rjFechaRad     = trim((string) jsonPick($rjDetalle, 'fechaRadicacion', ''));
  $rjTipoProceso  = trim((string) jsonPick($rjDetalle, 'tipoProceso', ''));
  $rjDespacho     = trim((string) jsonPick($rjDetalle, 'despacho', ''));
  $rjNumeroRad    = trim((string) jsonPick($rjDetalle, 'numeroRadicacion', ''));


  $anchorRj = 'Observación Rama Judicial';


  setObservationBelowBlock($sheet, $anchorRj, $rjObsFinal, 20);
  setObservationBelowBlock($sheet, 'Observacion Rama Judicial', $rjObsFinal, 20);

  // Campos cercanos
  setValueRightOfLabelNear($sheet, $anchorRj, 'Clase de Proceso', $rjClaseProceso, 10);
  setValueRightOfLabelNear($sheet, $anchorRj, 'Fecha de Radicación', $rjFechaRad, 10);
  setValueRightOfLabelNear($sheet, $anchorRj, 'Fecha de Radicacion', $rjFechaRad, 10);
  setValueRightOfLabelNear($sheet, $anchorRj, 'Tipo de Proceso', $rjTipoProceso, 10);
  setValueRightOfLabelNear($sheet, $anchorRj, 'Despacho', $rjDespacho, 10);
  setValueRightOfLabelNear($sheet, $anchorRj, 'Número de Radicación del Proceso', $rjNumeroRad, 12);
  setValueRightOfLabelNear($sheet, $anchorRj, 'Numero de Radicacion del Proceso', $rjNumeroRad, 12);
}


// =========================================================
// ✅ ENTIDAD / OBSERVACIONES (CABECERA) — COLOCAR AQUÍ
// =========================================================
$sheet->setCellValue('E15', v($consultas,'PROCURADURIA',''));
$sheet->setCellValue('E16', v($consultas,'POLICIA_NACIONAL',''));
$sheet->setCellValue('E17', v($consultas,'RUNT_CE',''));
$sheet->setCellValue('E18', v($consultas,'RAMA_JUDICIAL_PP_CE',''));




$filename = "Estudio_Seguridad_{$idEstudio}.xlsx";

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="'.$filename.'"');
header('Cache-Control: max-age=0');

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
exit;