<?php
// api_poligrafia/visita_domiciliaria/generar_excel_visita.php
// ? Ajuste HOJA 2 (P�GINA 2): marca con "X" EN LA FILA DE ABAJO (A13, B13...)
// ? Seg�n tu plantilla (como en la imagen)

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

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\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

// ================== HELPERS ==================



function toNumber($n) {
    if ($n === null) return null;
    if (is_string($n)) {
        $n = trim($n);
        if ($n === '') return null;
        // deja solo dígitos y signo/punto si existiera
        $n = preg_replace('/[^\d\-\.]/', '', $n);
    }
    return is_numeric($n) ? (float)$n : null;
}





function normalizeText($text): string
{
    $text = trim((string)$text);
    $text = mb_strtolower($text, 'UTF-8');

    $replacements = [
        '�'=>'a','�'=>'e','�'=>'i','�'=>'o','�'=>'u',
        '�'=>'a','�'=>'e','�'=>'i','�'=>'o','�'=>'u',
        '�'=>'a','�'=>'e','�'=>'i','�'=>'o','�'=>'u',
        '�'=>'a','�'=>'e','�'=>'i','�'=>'o','�'=>'u',
        '�'=>'n'
    ];
    $text = strtr($text, $replacements);

    $text = preg_replace('/[^a-z0-9\s]/u', ' ', $text);
    $text = preg_replace('/\s+/', ' ', $text);

    return trim($text);
}

function fmtDate($dateStr): string
{
    $dateStr = trim((string)$dateStr);
    if ($dateStr === '') return '';
    $ts = strtotime($dateStr);
    if (!$ts) return $dateStr;
    return date('d/m/Y', $ts);
}

function fmtMoney($n): string
{
    if ($n === null || $n === '') return '';
    if (is_string($n)) $n = str_replace([' ', ','], ['', ''], $n);
    if (!is_numeric($n)) return (string)$n;
    return number_format((float)$n, 0, ',', '.');
}

function safeJson($json)
{
    if ($json === null) return null;
    if (!is_string($json)) return null;

    $json = trim($json);
    if ($json === '') return null;

    // ✅ Limpiezas típicas cuando viene "sucio" desde BD
    $json = preg_replace('/\xEF\xBB\xBF/', '', $json);          // BOM
    $json = preg_replace('/,\s*,+/', ',', $json);               // ",," -> ","
    $json = preg_replace('/,\s*([\]\}])/', '$1', $json);        // ", ]" o ", }" -> "]" / "}"
    $json = trim($json);

    $decoded = json_decode($json, true);
    return (json_last_error() === JSON_ERROR_NONE) ? $decoded : null;
}


function findCellByText(Worksheet $sheet, string $searchText)
{
    $search = normalizeText($searchText);

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

    for ($row = 1; $row <= $highestRow; $row++) {
        for ($col = 1; $col <= $highestCol; $col++) {
            $addr = Coordinate::stringFromColumnIndex($col) . $row;
            $val = $sheet->getCell($addr)->getValue();
            if ($val === null || $val === '') continue;

            $cellText = normalizeText($val);
            if ($cellText === '') continue;

            if (mb_strpos($cellText, $search, 0, 'UTF-8') !== false) {
                return $sheet->getCell($addr);
            }
        }
    }
    return null;
}

function getMergeRangeForCell(Worksheet $sheet, int $row, int $colIndex): ?string
{
    foreach ($sheet->getMergeCells() as $range) {
        [$start, $end] = explode(':', $range);

        $startCol = Coordinate::columnIndexFromString(preg_replace('/\d+/', '', $start));
        $startRow = (int)preg_replace('/\D+/', '', $start);

        $endCol   = Coordinate::columnIndexFromString(preg_replace('/\d+/', '', $end));
        $endRow   = (int)preg_replace('/\D+/', '', $end);

        if ($row >= $startRow && $row <= $endRow && $colIndex >= $startCol && $colIndex <= $endCol) {
            return $range;
        }
    }
    return null;
}

function parseMergeRange(string $range): array
{
    [$start, $end] = explode(':', $range);

    $startCol = Coordinate::columnIndexFromString(preg_replace('/\d+/', '', $start));
    $startRow = (int)preg_replace('/\D+/', '', $start);

    $endCol   = Coordinate::columnIndexFromString(preg_replace('/\d+/', '', $end));
    $endRow   = (int)preg_replace('/\D+/', '', $end);

    return [$startRow, $startCol, $endRow, $endCol];
}

function setValueInFieldRightOfLabel(Worksheet $sheet, string $labelText, $value, int $maxScanCols = 30): bool
{
    if ($value === null) return false;
    if (is_string($value) && trim($value) === '') return false;

    $labelCell = findCellByText($sheet, $labelText);
    if (!$labelCell) return false;

    $row = $labelCell->getRow();
    $labelCol = Coordinate::columnIndexFromString($labelCell->getColumn());

    $labelMerge = getMergeRangeForCell($sheet, $row, $labelCol);
    $startCol = $labelCol + 1;

    if ($labelMerge) {
        [$sr, $sc, $er, $ec] = parseMergeRange($labelMerge);
        $startCol = $ec + 1;
    }

    $highestCol = Coordinate::columnIndexFromString($sheet->getHighestColumn());
    $limitCol = min($highestCol, $startCol + $maxScanCols);

    for ($c = $startCol; $c <= $limitCol; $c++) {
        $merge = getMergeRangeForCell($sheet, $row, $c);

        if ($merge) {
            if ($labelMerge && $merge === $labelMerge) continue;

            [$sr, $sc, $er, $ec] = parseMergeRange($merge);
            $topLeftAddr = Coordinate::stringFromColumnIndex($sc) . $sr;

            $sheet->setCellValue($topLeftAddr, $value);
            $sheet->getStyle($topLeftAddr)->getAlignment()->setWrapText(true);
            return true;
        }

        $addr = Coordinate::stringFromColumnIndex($c) . $row;
        $cell = $sheet->getCell($addr);

        $currentVal = $cell->getValue();
        $norm = normalizeText((string)$currentVal);
        $looksLikeLabel = ($currentVal !== null && $currentVal !== '' && mb_strlen($norm, 'UTF-8') > 2);

        if (!$looksLikeLabel) {
            $sheet->setCellValue($addr, $value);
            $sheet->getStyle($addr)->getAlignment()->setWrapText(true);
            return true;
        }
    }

    return false;
}

function setDateInFieldRightOfLabel(Worksheet $sheet, string $labelText, $dateStr): bool
{
    $v = fmtDate($dateStr);
    if ($v === '') return false;
    return setValueInFieldRightOfLabel($sheet, $labelText, $v);
}

function setValueInFieldRightOfAnyLabel(Worksheet $sheet, array $labels, $value): bool
{
    foreach ($labels as $lbl) {
        if (setValueInFieldRightOfLabel($sheet, $lbl, $value)) return true;
    }
    return false;
}

function setDateInFieldRightOfAnyLabel(Worksheet $sheet, array $labels, $dateStr): bool
{
    foreach ($labels as $lbl) {
        if (setDateInFieldRightOfLabel($sheet, $lbl, $dateStr)) return true;
    }
    return false;
}

function findColIndexInRowByText(Worksheet $sheet, int $row, string $text): ?int
{
    $highestCol = Coordinate::columnIndexFromString($sheet->getHighestColumn());
    $search = normalizeText($text);

    for ($col = 1; $col <= $highestCol; $col++) {
        $addr = Coordinate::stringFromColumnIndex($col) . $row;
        $val = $sheet->getCell($addr)->getValue();
        if ($val === null || $val === '') continue;

        if (mb_strpos(normalizeText($val), $search, 0, 'UTF-8') !== false) {
            return $col;
        }
    }
    return null;
}


function findColIndexInRowByTextAfter(Worksheet $sheet, int $row, string $text, int $afterCol): ?int
{
    $highestCol = Coordinate::columnIndexFromString($sheet->getHighestColumn());
    $search = normalizeText($text);

    for ($col = max(1, $afterCol + 1); $col <= $highestCol; $col++) {
        $addr = Coordinate::stringFromColumnIndex($col) . $row;
        $val = $sheet->getCell($addr)->getValue();
        if ($val === null || $val === '') continue;

        if (mb_strpos(normalizeText((string)$val), $search, 0, 'UTF-8') !== false) {
            return $col;
        }
    }
    return null;
}


/**
 * ? Marca una celda EXACTA (por coordenada) con "X".
 * Si es un rango combinado, escribe en la esquina superior izquierda.
 */
function putX(Worksheet $sheet, string $cellAddr, string $mark = 'X'): void
{
    // Si la celda pertenece a un merge, escribir en el top-left del merge
    $colLetters = preg_replace('/\d+/', '', $cellAddr);
    $rowNum = (int)preg_replace('/\D+/', '', $cellAddr);
    $colIdx = Coordinate::columnIndexFromString($colLetters);

    $merge = getMergeRangeForCell($sheet, $rowNum, $colIdx);
    if ($merge) {
        [$sr, $sc, $er, $ec] = parseMergeRange($merge);
        $cellAddr = Coordinate::stringFromColumnIndex($sc) . $sr;
    }

    $sheet->setCellValue($cellAddr, $mark);
    $sheet->getStyle($cellAddr)->getAlignment()->setWrapText(true);
}

/**
 * ? Marca una opci�n de vivienda/comodidad SEG�N TU MAPEO DE CELDAS (X DEBAJO).
 * - $map = ['habitacion' => 'A13', 'casa' => 'B13', ...]
 * - si es "otro": escribe en $otherField (ej: H13) como "X\n<texto>"
 */
function markByFixedMap(Worksheet $sheet, array $map, string $selected, ?string $otherText, string $otherField): void
{
    $k = normalizeText($selected);

    if ($k === '' ) return;

    if ($k === 'otro') {
        $t = trim((string)$otherText);
        $val = $t !== '' ? "X\n".$t : "X";
        putX($sheet, $otherField, $val);
        return;
    }

    // normaliza keys del map tambi�n
    foreach ($map as $key => $addr) {
        if (normalizeText($key) === $k) {
            putX($sheet, $addr, 'X');
            return;
        }
    }
}



/**
 * Busca una opción en una fila (por texto) y marca con X en la fila de abajo (misma columna).
 * - $optionsRow: fila donde están los textos (ej: 16)
 * - $markRow: fila donde debe ir la X (ej: 17)
 * - $colStart/$colEnd: rango de columnas a revisar
 */
function markXUnderOptionInRow(
    Worksheet $sheet,
    int $optionsRow,
    int $markRow,
    string $optionText,
    string $colStart = 'A',
    string $colEnd   = 'H'
): bool {
    $search = normalizeText($optionText);
    if ($search === '') return false;

    $startCol = Coordinate::columnIndexFromString($colStart);
    $endCol   = Coordinate::columnIndexFromString($colEnd);

    for ($c = $startCol; $c <= $endCol; $c++) {
        $addrTitle = Coordinate::stringFromColumnIndex($c) . $optionsRow;
        $val = $sheet->getCell($addrTitle)->getValue();
        if ($val === null || $val === '') continue;

        if (normalizeText((string)$val) === $search) {
            $addrMark = Coordinate::stringFromColumnIndex($c) . $markRow;
            putX($sheet, $addrMark, 'X');
            return true;
        }
    }
    return false;
}

/**
 * Marca múltiples opciones (lista JSON) contra una fila de opciones y pone X debajo.
 */
function markJsonListUnderRow(
    Worksheet $sheet,
    int $optionsRow,
    int $markRow,
    array $list,
    string $colStart = 'A',
    string $colEnd   = 'H'
): void {
    foreach ($list as $item) {
        if (!is_string($item)) continue;
        markXUnderOptionInRow($sheet, $optionsRow, $markRow, $item, $colStart, $colEnd);
    }
}





/**
 * Busca una celda que contenga $text dentro de un rango (filas/columnas).
 * Retorna [row, colIndex] o null.
 */
function findCellInRangeByText(
    Worksheet $sheet,
    string $text,
    int $rowMin,
    int $rowMax,
    string $colMin = 'A',
    string $colMax = 'H'
): ?array {
    $search = normalizeText($text);
    if ($search === '') return null;

    $cMin = Coordinate::columnIndexFromString($colMin);
    $cMax = Coordinate::columnIndexFromString($colMax);

    for ($r = $rowMin; $r <= $rowMax; $r++) {
        for ($c = $cMin; $c <= $cMax; $c++) {
            $addr = Coordinate::stringFromColumnIndex($c) . $r;
            $val  = $sheet->getCell($addr)->getValue();
            if ($val === null || $val === '') continue;

            $cellText = normalizeText((string)$val);
            if ($cellText === '') continue;

            // contiene (por si la celda trae "Baños" con salto, o "Otro\nCual:")
            if (mb_strpos($cellText, $search, 0, 'UTF-8') !== false) {
                return [$r, $c];
            }
        }
    }
    return null;
}

/**
 * Marca "X" en la celda de abajo (misma columna) de la opción encontrada.
 */
function markXBelowOptionInRange(
    Worksheet $sheet,
    string $optionText,
    int $rowMin,
    int $rowMax,
    string $colMin = 'A',
    string $colMax = 'H'
): void {
    $found = findCellInRangeByText($sheet, $optionText, $rowMin, $rowMax, $colMin, $colMax);
    if (!$found) return;

    [$r, $c] = $found;
    $markAddr = Coordinate::stringFromColumnIndex($c) . ($r + 1);
    putX($sheet, $markAddr, 'X');
}



/**
 * ? Marca comodidades (JSON) por celdas fijas.
 * Soporta:
 *  - lista: ["Ventilada","Aseada",...]
 *  - objeto: { ventilada:true, gas_natural:true, otros:"..." }
 */
function markComodidadesFixed(Worksheet $sheet, $comod): void
{
    if (!is_array($comod)) return;

    // RANGO donde está "3. DESCRIPCIÓN DE LA VIVIENDA" en tu imagen
    // (A:H y filas 12–23). Ajusta si tu plantilla cambia, pero normalmente queda ahí.
    $ROW_MIN = 12;
    $ROW_MAX = 23;
    $COL_MIN = 'A';
    $COL_MAX = 'H';

    // Caso lista: ["Aseada","organizada",...]
    $isList = array_keys($comod) === range(0, count($comod) - 1);
    if ($isList) {
        foreach ($comod as $item) {
            if (!is_string($item)) continue;
            $k = trim($item);
            if ($k === '') continue;

            // Si llega "Otro", en tu plantilla el "Otro" está en col H con "Cual:"
            // pero como no mandas el texto del "cual", solo marcamos la X en el "otro" encontrado
            if (normalizeText($k) === 'otro') {
                markXBelowOptionInRange($sheet, 'Otro', $ROW_MIN, $ROW_MAX, $COL_MIN, $COL_MAX);
                continue;
            }

            // Normal: busca la opción (Aseada, Organizada, Comedor, Agua, Nevera, etc.)
            // y marca X en la celda de abajo
            markXBelowOptionInRange($sheet, $k, $ROW_MIN, $ROW_MAX, $COL_MIN, $COL_MAX);
        }
        return;
    }

    // Caso objeto: { "aseada": true, "organizada": true, "otros": "..." }
    foreach ($comod as $k => $v) {
        $kk = trim((string)$k);
        if ($kk === '') continue;

        $vv = is_string($v) ? normalizeText($v) : $v;
        $isTrue = ($vv === true || $vv === 1 || $vv === 'si' || $vv === 'yes');

        if (normalizeText($kk) === 'otros' || normalizeText($kk) === 'otro') {
            if (is_string($v) && trim($v) !== '') {
                // Si quieres escribir el "cual" debajo del "Otro" (col H en tu imagen)
                // ponlo en H17 o en el "Otro" que tengas: aquí lo dejamos en H17
                putX($sheet, 'H17', "X\n" . trim($v));
            } else {
                markXBelowOptionInRange($sheet, 'Otro', $ROW_MIN, $ROW_MAX, $COL_MIN, $COL_MAX);
            }
            continue;
        }

        if (!$isTrue) continue;

        // Marca usando la clave como texto a buscar (aseada, organizada, etc.)
        markXBelowOptionInRange($sheet, $kk, $ROW_MIN, $ROW_MAX, $COL_MIN, $COL_MAX);
    }
}

// ================== INPUT ==================

$idEstudio   = isset($_GET['id_estudio']) ? (int)$_GET['id_estudio'] : 0;
$idCandidato = isset($_GET['id_candidato']) ? (int)$_GET['id_candidato'] : 0;

if ($idEstudio <= 0 && $idCandidato <= 0) {
    http_response_code(400);
    echo "Debe enviar id_estudio o id_candidato";
    exit;
}

// ================== RESOLVER ESTUDIO ==================

if ($idEstudio <= 0) {
    $sqlLast = "
        SELECT ID_ESTUDIO
        FROM ESTUDIO_SEGURIDAD
        WHERE ID_CANDIDATO_EMPRESA = ? AND ESTADO_ESTUDIO = 1
        ORDER BY FECHA_CREACION DESC
        LIMIT 1
    ";
    $st = $mysqli->prepare($sqlLast);
    if (!$st) { http_response_code(500); echo "Error prepare last estudio: " . $mysqli->error; exit; }
    $st->bind_param("i", $idCandidato);
    $st->execute();
    $rs = $st->get_result();
    $row = $rs->fetch_assoc();
    $st->close();

    if (!$row) { http_response_code(404); echo "No se encontr� estudio activo para el candidato"; exit; }
    $idEstudio = (int)$row['ID_ESTUDIO'];
}

// ================== DATA: CORE ==================

$sqlCore = "
    SELECT
        es.ID_ESTUDIO,
        es.ID_CANDIDATO_EMPRESA,
        es.CARGO_ASPIRA,
        es.FECHA_CREACION AS FECHA_ESTUDIO,

        c.NOMBRE_CANDIDATO,
        c.APELLIDO_CANDIDATO,
        c.NUMERO_DOCUMENTO_CANDIDATO,
        c.EMPRESA_CANDIDATO,

        ec.NOMBRE_EMPRESA

    FROM ESTUDIO_SEGURIDAD es
    INNER JOIN CANDIDATO_EMPRESA c
        ON c.ID_EMPRESA = es.ID_CANDIDATO_EMPRESA
    LEFT JOIN EMPRESA_CLIENTE ec
        ON ec.ID_EMPRESA = c.EMPRESA_CANDIDATO
    WHERE es.ID_ESTUDIO = ?
    LIMIT 1
";
$stmtCore = $mysqli->prepare($sqlCore);
if (!$stmtCore) { http_response_code(500); echo "Error prepare core: " . $mysqli->error; exit; }
$stmtCore->bind_param("i", $idEstudio);
$stmtCore->execute();
$core = $stmtCore->get_result()->fetch_assoc();
$stmtCore->close();

if (!$core) {
    http_response_code(404);
    echo "No se encontr� el estudio";
    exit;
}

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

// ================== DATA: DATOS PERSONALES (P�G 1) ==================

$sqlPersonal = "
    SELECT *
    FROM ESTUDIO_DATOS_PERSONALES
    WHERE ID_ESTUDIO = ? AND ID_CANDIDATO_EMPRESA = ? AND ESTADO_REGISTRO = 1
    LIMIT 1
";
$stmtP = $mysqli->prepare($sqlPersonal);
if (!$stmtP) { http_response_code(500); echo "Error prepare personal: " . $mysqli->error; exit; }
$stmtP->bind_param("ii", $idEstudio, $idCandidato);
$stmtP->execute();
$personal = $stmtP->get_result()->fetch_assoc() ?: [];
$stmtP->close();

// ================== DATA: CONSULTAS ENTIDADES ==================

$sqlCons = "
    SELECT PROCURADURIA
    FROM CONSULTAS_ENTIDADES
    WHERE ID_CANDIDATO_EMPRESA = ? AND ESTADO_REGISTRO = 1
    ORDER BY FECHA_CREACION DESC
    LIMIT 1
";
$stmtC = $mysqli->prepare($sqlCons);
if (!$stmtC) { http_response_code(500); echo "Error prepare consultas: " . $mysqli->error; exit; }
$stmtC->bind_param("i", $idCandidato);
$stmtC->execute();
$cons = $stmtC->get_result()->fetch_assoc() ?: [];
$stmtC->close();

// ================== DATA: FAMILIARES ==================

$sqlFam = "
    SELECT
        NOMBRE_COMPLETO,
        RESIDE_CON_PERSONA,
        PARENTESCO,
        EDAD,
        ESTADO_CIVIL,
        ACTIVIDAD_LABORAL,
        TELEFONO
    FROM ESTUDIO_FAMILIARES
    WHERE ID_ESTUDIO = ? AND ID_CANDIDATO_EMPRESA = ? AND ESTADO_REGISTRO = 1
    ORDER BY ID_FAMILIAR ASC
";
$stmtF = $mysqli->prepare($sqlFam);
if (!$stmtF) { http_response_code(500); echo "Error prepare familiares: " . $mysqli->error; exit; }
$stmtF->bind_param("ii", $idEstudio, $idCandidato);
$stmtF->execute();
$familiares = $stmtF->get_result()->fetch_all(MYSQLI_ASSOC) ?: [];
$stmtF->close();

// ================== DATA: CONCEPTO 1 (P�G 2) ==================

$sqlC1 = "
    SELECT *
    FROM ESTUDIO_CONCEPTO_1
    WHERE ID_ESTUDIO = ? AND ID_CANDIDATO_EMPRESA = ? AND ESTADO_REGISTRO = 1
    LIMIT 1
";
$stmtC1 = $mysqli->prepare($sqlC1);
if (!$stmtC1) { http_response_code(500); echo "Error prepare concepto_1: " . $mysqli->error; exit; }
$stmtC1->bind_param("ii", $idEstudio, $idCandidato);
$stmtC1->execute();
$concepto1 = $stmtC1->get_result()->fetch_assoc() ?: [];
$stmtC1->close();

// ================== CARGAR PLANTILLA ==================

$templatePath = __DIR__ . "/Plantilla.xlsx";
if (!file_exists($templatePath)) {
    http_response_code(500);
    echo "No se encontr� la plantilla XLSX en: " . $templatePath;
    exit;
}

$spreadsheet = IOFactory::load($templatePath);

// ================== HOJA 1 (P�GINA 1) ==================

$sheet1 = $spreadsheet->getSheet(0);

$nombreCompleto = trim(($core['NOMBRE_CANDIDATO'] ?? '') . ' ' . ($core['APELLIDO_CANDIDATO'] ?? ''));
$empresaNombre  = $core['NOMBRE_EMPRESA'] ?? '';
$cargoAspira    = $core['CARGO_ASPIRA'] ?? '';
$ciudad         = $personal['CIUDAD'] ?? '';
$fechaHoy       = date('d/m/Y');

// Encabezado
setValueInFieldRightOfAnyLabel($sheet1, ['Empresa', 'Empresa:'], $empresaNombre);
setValueInFieldRightOfAnyLabel($sheet1, ['Persona que atiende la visita', 'Persona que atiende la visita:'], $personal['PERSONA_ATIENDE'] ?? '');
setValueInFieldRightOfAnyLabel($sheet1, ['Cargo', 'Cargo:'], $cargoAspira);
setValueInFieldRightOfAnyLabel($sheet1, ['Ciudad y Fecha', 'Ciudad y Fecha:'], trim($ciudad . ' - ' . $fechaHoy));

// 1. DATOS PERSONALES
setValueInFieldRightOfAnyLabel($sheet1, ['Nombres y Apellidos', 'Nombres y Apellidos:'], $nombreCompleto);
setValueInFieldRightOfAnyLabel($sheet1, ['C�dula', 'Cedula', 'C�dula:', 'Cedula:'], $personal['CEDULA'] ?? ($core['NUMERO_DOCUMENTO_CANDIDATO'] ?? ''));
setValueInFieldRightOfAnyLabel($sheet1, ['Estado Civil', 'Estado Civil:'], $personal['ESTADO_CIVIL'] ?? '');

setValueInFieldRightOfAnyLabel($sheet1, ['Lugar de Expedici�n', 'Lugar de Expedicion', 'Lugar de Expedici�n:', 'Lugar de Expedicion:'], $personal['LUGAR_EXPEDICION'] ?? '');
setDateInFieldRightOfAnyLabel($sheet1, ['Fecha de Expedici�n', 'Fecha de Expedicion', 'Fecha de Expedici�n:', 'Fecha de Expedicion:'], $personal['FECHA_EXPEDICION'] ?? '');

setDateInFieldRightOfAnyLabel($sheet1, ['Fecha de Nacimiento', 'Fecha de Nacimiento:'], $personal['FECHA_NACIMIENTO'] ?? '');
setValueInFieldRightOfAnyLabel($sheet1, ['Lugar de Nacimiento', 'Lugar de Nacimiento:'], $personal['LUGAR_NACIMIENTO'] ?? '');

setValueInFieldRightOfAnyLabel($sheet1, ['Libreta Militar Numero', 'Libreta Militar N�mero', 'Libreta Militar Numero:', 'Libreta Militar N�mero:'], $personal['NR_LIBRETA_MILITAR'] ?? '');
setValueInFieldRightOfAnyLabel($sheet1, ['Clase', 'Clase:'], $personal['CLASE_LIBRETA_MILITAR'] ?? '');
setValueInFieldRightOfAnyLabel($sheet1, ['C�dula Militar', 'Cedula Militar', 'C�dula Militar:', 'Cedula Militar:'], $personal['CEDULA_MILITAR'] ?? '');

setValueInFieldRightOfAnyLabel($sheet1, ['Porte de Arma N�mero', 'Porte de Arma Numero', 'Porte de Arma N�mero:', 'Porte de Arma Numero:'], $personal['PORTE_ARMA_NUMERO'] ?? '');
setValueInFieldRightOfAnyLabel($sheet1, ['Clase de Arma', 'Clase de Arma:'], $personal['CLASE_ARMA'] ?? '');

setValueInFieldRightOfAnyLabel($sheet1, ['Direcci�n Residencia', 'Direccion Residencia', 'Direcci�n Residencia:', 'Direccion Residencia:'], $personal['DIRECCION_RESIDENCIA'] ?? '');
setValueInFieldRightOfAnyLabel($sheet1, ['Barrio', 'Barrio:'], $personal['BARRIO'] ?? '');
setValueInFieldRightOfAnyLabel($sheet1, ['Estrato', 'Estrato:'], $personal['ESTRATO'] ?? '');
setValueInFieldRightOfAnyLabel($sheet1, ['Zona', 'Zona:'], $personal['ZONA'] ?? '');
setValueInFieldRightOfAnyLabel($sheet1, ['Tel�fonos', 'Telefonos', 'Tel�fonos:', 'Telefonos:'], $personal['TELEFONO_CONTACTO'] ?? '');

$antecProc = $personal['ANTECEDENTES_PROCURADURIA'] ?? '';
if (trim((string)$antecProc) === '' && !empty($cons['PROCURADURIA'])) $antecProc = $cons['PROCURADURIA'];
setValueInFieldRightOfAnyLabel($sheet1, ['Antec. Procuradur�a', 'Antec Procuraduria', 'Antec. Procuradur�a:', 'Antec Procuraduria:'], $antecProc);

setValueInFieldRightOfAnyLabel($sheet1, ['Consulta de Antecedentes', 'Consulta de Antecedentes:'], $personal['CONSULTA_ANTECEDENTES'] ?? '');
setDateInFieldRightOfAnyLabel($sheet1, ['Fecha de Expedici�n', 'Fecha de Expedicion', 'Fecha de Expedici�n:', 'Fecha de Expedicion:'], $personal['FECHA_EXPEDICION_CONSULTA'] ?? '');

setValueInFieldRightOfAnyLabel($sheet1, ['Pasaporte N�mero', 'Pasaporte Numero', 'Pasaporte N�mero:', 'Pasaporte Numero:'], $personal['NUMERO_PASAPORTE'] ?? '');
setValueInFieldRightOfAnyLabel($sheet1, ['Visa N�mero', 'Visa Numero', 'Visa N�mero:', 'Visa Numero:'], $personal['VISA_NUMERO'] ?? '');

setValueInFieldRightOfAnyLabel($sheet1, ['Licencia de Conducci�n', 'Licencia de Conduccion', 'Licencia de Conducci�n:', 'Licencia de Conduccion:'], $personal['LICENCIA_CONDUCCION'] ?? '');
setValueInFieldRightOfAnyLabel($sheet1, ['Veh�culo', 'Vehiculo', 'Veh�culo:', 'Vehiculo:'], $personal['TIPO_VEHICULO'] ?? '');

setValueInFieldRightOfAnyLabel($sheet1, ['Fondo de Pensiones', 'Fondo de Pension es', 'Fondo de Pensiones:', 'Fondo de Pension es:'], $personal['FONDO_PENSIONES'] ?? '');
setValueInFieldRightOfAnyLabel($sheet1, ['EPS', 'EPS:'], $personal['EPS'] ?? '');
setValueInFieldRightOfAnyLabel($sheet1, ['Tipo de Sangre', 'Tipo de Sangre:'], $personal['TIPO_SANGRE'] ?? '');
setValueInFieldRightOfAnyLabel($sheet1, ['RH', 'RH:'], $personal['RH'] ?? '');

// Tabla familiares
$headerCell = findCellByText($sheet1, '2.FAMILIARES O AMIGOS CERCANOS');
if ($headerCell) {
    $sectionRow = $headerCell->getRow();
    $headerRow  = $sectionRow + 1;

    $namesHeader = findCellByText($sheet1, 'Personas con las que reside');
    if ($namesHeader) $headerRow = $namesHeader->getRow();

    $colNombre   = findColIndexInRowByText($sheet1, $headerRow, 'Nombres y Apellidos');
    $colReside   = findColIndexInRowByText($sheet1, $headerRow, 'Personas con las que reside');
    $colPar      = findColIndexInRowByText($sheet1, $headerRow, 'Parentesco');
    $colEdad     = findColIndexInRowByText($sheet1, $headerRow, 'Edad');
    $colEC       = findColIndexInRowByText($sheet1, $headerRow, 'Estado Civil');
    $colAct      = findColIndexInRowByText($sheet1, $headerRow, 'Actividad');
    $colTel      = findColIndexInRowByText($sheet1, $headerRow, 'Tel�fono');
    if (!$colTel) $colTel = findColIndexInRowByText($sheet1, $headerRow, 'Telefono');

    $startRow = $headerRow + 1;

    $max = min(count($familiares), 10);
    for ($i = 0; $i < $max; $i++) {
        $r = $startRow + $i;
        $f = $familiares[$i];

        if ($colNombre) $sheet1->setCellValue(Coordinate::stringFromColumnIndex($colNombre) . $r, $f['NOMBRE_COMPLETO'] ?? '');
        if ($colReside) $sheet1->setCellValue(Coordinate::stringFromColumnIndex($colReside) . $r, $f['RESIDE_CON_PERSONA'] ?? '');
        if ($colPar)    $sheet1->setCellValue(Coordinate::stringFromColumnIndex($colPar) . $r, $f['PARENTESCO'] ?? '');
        if ($colEdad)   $sheet1->setCellValue(Coordinate::stringFromColumnIndex($colEdad) . $r, $f['EDAD'] ?? '');
        if ($colEC)     $sheet1->setCellValue(Coordinate::stringFromColumnIndex($colEC) . $r, $f['ESTADO_CIVIL'] ?? '');
        if ($colAct)    $sheet1->setCellValue(Coordinate::stringFromColumnIndex($colAct) . $r, $f['ACTIVIDAD_LABORAL'] ?? '');
        if ($colTel)    $sheet1->setCellValue(Coordinate::stringFromColumnIndex($colTel) . $r, $f['TELEFONO'] ?? '');
    }
}

// ================== HOJA 2 (P�GINA 2) ==================

$sheet2 = $spreadsheet->getSheet(1);

// Campos superiores (texto normal)
setValueInFieldRightOfAnyLabel($sheet2, ['Ciudad de origen Familiar', 'Ciudad de origen Familiar:'], $concepto1['CIUDAD_ORIGEN_FAMILIAR'] ?? '');
setValueInFieldRightOfAnyLabel($sheet2, ['Valores Morales', 'Valores Morales:'], $concepto1['VALORES_MORALES'] ?? '');
setValueInFieldRightOfAnyLabel($sheet2, ['Proyectos a Mediano y largo Plazo', 'Proyectos a Mediano y largo Plazo:'], $concepto1['PROYECTOS'] ?? '');
setValueInFieldRightOfAnyLabel($sheet2, ['Actividades en el Tiempo Libre', 'Actividades en el Tiempo Libre:'], $concepto1['ACTIVIDADES_TIEMPO_LIBRE'] ?? '');
setValueInFieldRightOfAnyLabel($sheet2, ['Situaciones Dif�ciles en la Familia', 'Situaciones Dificiles en la Familia', 'Situaciones Dif�ciles en la Familia:'], $concepto1['SITUACIONES_DIFICILES'] ?? '');
setValueInFieldRightOfAnyLabel($sheet2, ['Enfermedades en la Familia', 'Enfermedades en la Familia:'], $concepto1['ENFERMEDADES_FAMILIA'] ?? '');
setValueInFieldRightOfAnyLabel($sheet2, ['Socio de alg�n Club o Agremiaci�n', 'Socio de algun Club o Agremiacion', 'Socio de alg�n Club o Agremiaci�n:'], $concepto1['SOCIO_CLUB'] ?? '');

// ? 3.DESCRIPCI�N DE LA VIVIENDA (X DEBAJO seg�n tu imagen)

// 1) Tipo de vivienda: Habitaci�n/Casa/... (X en A13..F13). "Otro": en H13
$mapTipoVivienda = [
    'Habitación'  => 'A13',
    'Habitacion'  => 'A13',
    'Casa'        => 'B13',
    'Inquilinato' => 'C13',
    'Casalote'    => 'D13',
    'Apartamento' => 'E13',
    'Finca'       => 'F13',
];
markByFixedMap(
    $sheet2,
    $mapTipoVivienda,
    (string)($concepto1['LA_VIVIENDA_ES'] ?? ''),
    (string)($concepto1['OTRO_TIPO_VIVIENDA'] ?? ''),
    'H13'
);


// 2) La vivienda es: Familiar/Propia/... (X en A15..F15). "Otro": en H15
$mapLaViviendaEs = [
    'Familiar'  => 'A15',
    'Propia'    => 'B15',
    'Cedida'    => 'C15',
    'Invasión'  => 'D15',
    'Invasion'  => 'D15',
    'Arrendada' => 'E15',
    'Sucesión'  => 'F15',
    'Sucesion'  => 'F15',
];
markByFixedMap(
    $sheet2,
    $mapLaViviendaEs,
    (string)($concepto1['TIPO_VIVIENDA'] ?? ''),
    (string)($concepto1['OTRO_LA_VIVIENDA_ES'] ?? ''),
    'H15'
);


// 3) Comodidades JSON (marca en A17..F17, A19..F19, A21..F21 y A23..G23)
$comod = safeJson($concepto1['COMODIDADES_JSON'] ?? null);
markComodidadesFixed($sheet2, $comod);

// ================== INGRESOS / EGRESOS (igual que antes por encabezados) ==================
// ================== INGRESOS / EGRESOS (APORTES_JSON / EGRESOS_JSON) ==================

// ================== INGRESOS / EGRESOS (APORTES_JSON / EGRESOS_JSON) ==================

$aportes = safeJson($concepto1['APORTES_JSON'] ?? null);
$egresos = safeJson($concepto1['EGRESOS_JSON'] ?? null);

$ingCell = findCellByText($sheet2, 'INGRESOS');
if ($ingCell) {

    // ✅ En tu plantilla (según imagen): los datos empiezan en la fila 28
    // Si en tu Excel empieza en otra fila, cambia SOLO este número.
    $startRow = 28;

    // ================== INGRESOS ==================
    // A = Nombre de Quien Aporta
    // B = Ing. Personal
    // C = Valor Aporte
    $totalAportesCalc = 0;

    if (is_array($aportes)) {
        $max = min(count($aportes), 5);
        for ($i = 0; $i < $max; $i++) {
            $r = $startRow + $i;
            $a = $aportes[$i];
            if (!is_array($a)) continue;

            $nombre = trim((string)($a['nombre'] ?? ''));
            $ing    = $a['ingreso'] ?? '';
            $valor  = $a['valorAporte'] ?? '';

            // Nombre
            $sheet2->setCellValue("A{$r}", $nombre);

            // Ingreso (numérico)
            $numIng = toNumber($ing);
            $sheet2->setCellValue("B{$r}", ($numIng === null ? '' : $numIng));
            $sheet2->getStyle("B{$r}")->getNumberFormat()->setFormatCode('#,##0');

            // Valor aporte (numérico)
            $numVal = toNumber($valor);
            $sheet2->setCellValue("C{$r}", ($numVal === null ? '' : $numVal));
            $sheet2->getStyle("C{$r}")->getNumberFormat()->setFormatCode('#,##0');

            if ($numVal !== null) $totalAportesCalc += $numVal;
        }
    }

    // ================== EGRESOS ==================

$totalEgresosCalc = 0;

if (is_array($egresos)) {
    $maxE = min(count($egresos), 5);
    for ($i = 0; $i < $maxE; $i++) {
        $r = $startRow + $i;
        $e = $egresos[$i];
        if (!is_array($e)) continue;

        $motivo = trim((string)($e['motivo'] ?? ''));
        $valor  = $e['valorEgreso'] ?? '';
        $num    = toNumber($valor);

        // ✅ 1er par: D (Motivo) | E (Valor)
        $sheet2->setCellValue("D{$r}", $motivo);

        $sheet2->setCellValue("E{$r}", ($num === null ? '' : $num));
        $sheet2->getStyle("E{$r}")->getNumberFormat()->setFormatCode('#,##0');

        // ✅ 2do par: F (Motivo) | G (Valor) -> vacío (por ahora)
        $sheet2->setCellValue("F{$r}", '');
        $sheet2->setCellValue("G{$r}", '');

        if ($num !== null) $totalEgresosCalc += $num;
    }
}


    // ================== TOTALES ==================
    $totalAportado = $concepto1['TOTAL_VALOR_APORTADO'] ?? '';
    $totalEgresos  = $concepto1['TOTAL_EGRESOS'] ?? '';

    if (trim((string)$totalAportado) === '' && $totalAportesCalc > 0) $totalAportado = $totalAportesCalc;
    if (trim((string)$totalEgresos) === '' && $totalEgresosCalc > 0)  $totalEgresos  = $totalEgresosCalc;

    setValueInFieldRightOfAnyLabel($sheet2, ['Total Valor Aportado', 'Total Valor Aportado:'], fmtMoney($totalAportado));
    setValueInFieldRightOfAnyLabel($sheet2, ['Egresos Total', 'Egresos Total:'], fmtMoney($totalEgresos));
}








// Veh�culos / Inmuebles / Concepto
setValueInFieldRightOfAnyLabel($sheet2, ['Veh�culos', 'Vehiculos', 'Veh�culos:', 'Vehiculos:'], $concepto1['VEHICULOS'] ?? '');
setValueInFieldRightOfAnyLabel($sheet2, ['Inmuebles', 'Inmuebles:'], $concepto1['INMUEBLES'] ?? '');
setValueInFieldRightOfAnyLabel($sheet2, ['Concepto', 'Concepto:'], $concepto1['CONCEPTO'] ?? '');

// ================== DESCARGAR ==================

$filename = "Visita_Domiciliaria_{$idEstudio}_Candidato_{$idCandidato}.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;
