<?php
// api_poligrafia/consultas_entidades/generar_excel_consultas.php

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;

// ================== FUNCIONES AUXILIARES ==================

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

    // Quitar tildes / diacríticos comunes
    $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);

    // Normalizar espacios
    $text = preg_replace('/\s+/', ' ', $text);

    return $text;
}

/**
 * Busca una celda que contenga (como substring) el texto indicado,
 * ignorando tildes y mayúsculas/minúsculas.
 */
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++) {
            $cellAddress = Coordinate::stringFromColumnIndex($col) . $row;
            $value = $sheet->getCell($cellAddress)->getValue();

            if ($value === null || $value === '') {
                continue;
            }

            $cellText = normalizeText($value);
            if ($cellText === '') {
                continue;
            }

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

    return null;
}

/**
 * Dado un rótulo, escribe el valor en la celda que está a la derecha
 * del rango COMBINADO donde se encuentre el rótulo.
 */
function setValueRightOfLabel(Worksheet $sheet, string $labelText, $value)
{
    if ($value === null) return;

    // Si es string vacío, también lo saltamos
    if (is_string($value) && trim($value) === '') return;

    $cell = findCellByText($sheet, $labelText);
    if (!$cell) {
        return;
    }

    $row      = $cell->getRow();
    $colIndex = Coordinate::columnIndexFromString($cell->getColumn());

    $mergedCells   = $sheet->getMergeCells();
    $rightMostCol  = $colIndex;

    foreach ($mergedCells as $range) {
        [$start, $end] = explode(':', $range);

        $startCol = Coordinate::columnIndexFromString(preg_replace('/\d+/', '', $start));
        $startRow = intval(preg_replace('/\D+/', '', $start));
        $endCol   = Coordinate::columnIndexFromString(preg_replace('/\d+/', '', $end));
        $endRow   = intval(preg_replace('/\D+/', '', $end));

        if ($row >= $startRow && $row <= $endRow &&
            $colIndex >= $startCol && $colIndex <= $endCol) {
            $rightMostCol = $endCol;
            break;
        }
    }

    $targetColIndex  = $rightMostCol + 1;
    $targetColLetter = Coordinate::stringFromColumnIndex($targetColIndex);
    $targetAddress   = $targetColLetter . $row;

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

/**
 * Escribe un valor dentro de la misma celda del rótulo (útil para campos largos).
 */
function setValueInSameLabelCell(Worksheet $sheet, string $labelText, $value)
{
    if ($value === null) return;
    if (is_string($value) && trim($value) === '') return;

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

    $cell->setValue($value);
    $sheet->getStyle($cell->getCoordinate())->getAlignment()->setWrapText(true);
}

// ================== OBTENER ID CANDIDATO ==================

$idCandidato = isset($_GET['id_candidato']) ? intval($_GET['id_candidato']) : 0;
if ($idCandidato <= 0) {
    http_response_code(400);
    echo "ID de candidato inválido";
    exit;
}

// ================== CARGAR DATOS DEL CANDIDATO ==================

$sqlCandidato = "
    SELECT 
        c.NOMBRE_CANDIDATO,
        c.APELLIDO_CANDIDATO,
        c.NUMERO_DOCUMENTO_CANDIDATO,
        c.EMPRESA_CANDIDATO,
        ec.NOMBRE_EMPRESA
    FROM CANDIDATO_EMPRESA c
    LEFT JOIN EMPRESA_CLIENTE ec
        ON c.EMPRESA_CANDIDATO = ec.ID_EMPRESA
    WHERE c.ID_EMPRESA = ?
";

$stmtCandidato = $mysqli->prepare($sqlCandidato);
if (!$stmtCandidato) {
    http_response_code(500);
    echo "Error al preparar consulta de candidato: " . $mysqli->error;
    exit;
}
$stmtCandidato->bind_param("i", $idCandidato);
$stmtCandidato->execute();
$resultCandidato = $stmtCandidato->get_result();
$candidato = $resultCandidato->fetch_assoc();

if (!$candidato) {
    http_response_code(404);
    echo "No se encontró el candidato";
    exit;
}

// ================== CARGAR ÚLTIMO ESTUDIO (OPCIONAL) ==================

$sqlEstudio = "
    SELECT *
    FROM ESTUDIO_SEGURIDAD
    WHERE ID_CANDIDATO_EMPRESA = ? AND ESTADO_ESTUDIO = 1
    ORDER BY FECHA_CREACION DESC
    LIMIT 1
";

$stmtEstudio = $mysqli->prepare($sqlEstudio);
if (!$stmtEstudio) {
    http_response_code(500);
    echo "Error al preparar consulta de estudio: " . $mysqli->error;
    exit;
}
$stmtEstudio->bind_param("i", $idCandidato);
$stmtEstudio->execute();
$resultEstudio = $stmtEstudio->get_result();
$estudio = $resultEstudio->fetch_assoc() ?: [];

// ================== CARGAR CONSULTAS DE ENTIDADES ==================

$sqlConsultas = "
    SELECT 
        PROCURADURIA,
        POLICIA_NACIONAL,
        FISCALIA,
        CONTRALORIA,
        DIJIN
    FROM CONSULTAS_ENTIDADES
    WHERE ID_CANDIDATO_EMPRESA = ? AND ESTADO_REGISTRO = 1
    ORDER BY FECHA_CREACION DESC
    LIMIT 1
";

$stmtCons = $mysqli->prepare($sqlConsultas);
if (!$stmtCons) {
    http_response_code(500);
    echo "Error al preparar consulta de entidades: " . $mysqli->error;
    exit;
}
$stmtCons->bind_param("i", $idCandidato);
$stmtCons->execute();
$resultCons = $stmtCons->get_result();
$consulta = $resultCons->fetch_assoc();

if (!$consulta) {
    // Si no hay consulta, dejamos observaciones por defecto
    $consulta = [
        'PROCURADURIA'     => 'Sin registros adversos reportados a la fecha.',
        'POLICIA_NACIONAL' => 'Sin registros adversos reportados a la fecha.',
        'FISCALIA'         => 'Sin registros adversos reportados a la fecha.',
        'CONTRALORIA'      => 'Sin registros adversos reportados a la fecha.',
        'DIJIN'            => 'Sin registros adversos reportados a la fecha.'
    ];
}

$stmtCandidato->close();
$stmtEstudio->close();
$stmtCons->close();

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

$templatePath = __DIR__ . "/PLANTILLA CONSULTA DE ANTECEDENTES - ADVERSOS.xlsx";

if (!file_exists($templatePath)) {
    http_response_code(500);
    echo "No se encontró la plantilla de Excel en: " . $templatePath;
    exit;
}

$spreadsheet = IOFactory::load($templatePath);
$sheet = $spreadsheet->getActiveSheet();

// ================== MAPEAR DATOS A LA PLANTILLA ==================

// ---- Identificación ----
$nombreCompleto = trim(($candidato['NOMBRE_CANDIDATO'] ?? '') . ' ' . ($candidato['APELLIDO_CANDIDATO'] ?? ''));
$cedula         = $candidato['NUMERO_DOCUMENTO_CANDIDATO'] ?? '';
$empresa        = $candidato['NOMBRE_EMPRESA'] ?? '';
$cargoAspira    = $estudio['CARGO_ASPIRA'] ?? '';

setValueRightOfLabel($sheet, 'Nombres y Apellidos', $nombreCompleto);
setValueRightOfLabel($sheet, 'Cedula', $cedula);
setValueRightOfLabel($sheet, 'Cliente', $empresa);
setValueRightOfLabel($sheet, 'Cargo', $cargoAspira);
setValueRightOfLabel($sheet, 'Fecha de Realizacion', date('Y-m-d'));

// ---- Consultas por entidad (IMPORTANTÍSIMO: escribir en OBSERVACIONES a la derecha) ----
// Usa textos que existan en tu plantilla. Con normalizeText, ya no importa si hay tildes.
setValueRightOfLabel($sheet, 'Procuraduria', $consulta['PROCURADURIA'] ?? '');
setValueRightOfLabel($sheet, 'Policia Nacional', $consulta['POLICIA_NACIONAL'] ?? '');
setValueRightOfLabel($sheet, 'Fiscalia', $consulta['FISCALIA'] ?? '');
setValueRightOfLabel($sheet, 'Contraloria', $consulta['CONTRALORIA'] ?? '');
setValueRightOfLabel($sheet, 'DIJIN', $consulta['DIJIN'] ?? '');

// ================== ENVIAR ARCHIVO AL NAVEGADOR ==================

$filename = "Informe_Consultas_Entidades_{$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;
