<?php
// api_poligrafia/historial_financiero/generar_excel_financiero.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 ==================

/**
 * Busca una celda que contenga (como substring) el texto indicado.
 */
function findCellByText(Worksheet $sheet, string $searchText)
{
    $search = mb_strtolower(trim($searchText), 'UTF-8');

    $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 = mb_strtolower(trim((string)$value), 'UTF-8');
            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)
{
    $cell = findCellByText($sheet, $labelText);
    if (!$cell) {
        // No se encontró el rótulo
        return;
    }

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

    // Buscar si la celda está dentro de un rango combinado
    $mergedCells = $sheet->getMergeCells();
    $rightMostCol = $colIndex;

    foreach ($mergedCells as $range) {
        // Ej: "B13:I13"
        [$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) {
            // El rótulo está dentro de este rango combinado
            $rightMostCol = $endCol;
            break;
        }
    }

    // La celda de valor es la columna inmediatamente a la derecha
    $targetColIndex  = $rightMostCol + 1;
    $targetColLetter = Coordinate::stringFromColumnIndex($targetColIndex);
    $targetAddress   = $targetColLetter . $row;

    $sheet->setCellValue($targetAddress, $value);
}

/**
 * Escribe un valor dentro de la misma celda del rótulo (para Observaciones).
 */
function setValueInSameLabelCell(Worksheet $sheet, string $labelText, $value)
{
    $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 DE SEGURIDAD ==================

$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 HISTORIAL FINANCIERO ==================

$sqlHistorial = "
    SELECT 
        TIPO_SOLICITUD,
        CANTIDAD_OBLIGACIONES,
        EMPRESAS_CON_REPORTE,
        SECTOR,
        REPORTADO_NEGATIVAMENTE,
        CARTERA_CASTIGADA,
        OBSERVACIONES
    FROM HISTORIAL_FINANCIERO
    WHERE ID_CANDIDATO_EMPRESA = ? AND ESTADO_REGISTRO = 1
    ORDER BY FECHA_CREACION DESC
";

$stmtHist = $mysqli->prepare($sqlHistorial);
if (!$stmtHist) {
    http_response_code(500);
    echo "Error al preparar consulta de historial financiero: " . $mysqli->error;
    exit;
}
$stmtHist->bind_param("i", $idCandidato);
$stmtHist->execute();
$resultHist = $stmtHist->get_result();

// Pasar a array
$historiales = [];
while ($row = $resultHist->fetch_assoc()) {
    $historiales[] = $row;
}

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

// Si no hay historial, igual generamos con texto por defecto
if (empty($historiales)) {
    $totalObligaciones   = 0;
    $sectoresString      = '';
    $empresasString      = '';
    $estaReportadoTexto  = '';
    $carteraCastigadaTxt = '';
    $observacionesTxt    = 'No se registran obligaciones financieras a la fecha.';
} else {
    $totalObligaciones = 0;
    $sectores = [];
    $empresas = [];
    $hayReporteNegativo = false;
    $hayCarteraCastigada = false;
    $observacionesParts = [];

    foreach ($historiales as $h) {
        $totalObligaciones += (int)($h['CANTIDAD_OBLIGACIONES'] ?? 0);

        if (!empty($h['SECTOR'])) {
            $sectores[] = trim($h['SECTOR']);
        }
        if (!empty($h['EMPRESAS_CON_REPORTE'])) {
            $empresas[] = trim($h['EMPRESAS_CON_REPORTE']);
        }
        if (strcasecmp($h['REPORTADO_NEGATIVAMENTE'] ?? '', 'Si') === 0) {
            $hayReporteNegativo = true;
        }
        if (strcasecmp($h['CARTERA_CASTIGADA'] ?? '', 'Si') === 0) {
            $hayCarteraCastigada = true;
        }
        if (!empty($h['OBSERVACIONES'])) {
            $observacionesParts[] = trim($h['OBSERVACIONES']);
        }
    }

    // Unificar sectores y empresas (sin duplicados)
    $sectores = array_unique(array_filter($sectores));
    $empresas = array_unique(array_filter($empresas));

    $sectoresString = '';
    if (!empty($sectores)) {
        $sectoresString = '(' . implode(', ', $sectores) . ').';
    }

    $empresasString = '';
    if (!empty($empresas)) {
        $empresasString = implode(', ', $empresas);
    }

    $estaReportadoTexto  = $hayReporteNegativo ? 'SI' : 'NO';
    $carteraCastigadaTxt = $hayCarteraCastigada ? 'SI' : 'NO';

    if (!empty($observacionesParts)) {
        $observacionesTxt = 'Observaciones: ' . implode('. ', $observacionesParts) . '.';
    } else {
        $observacionesTxt = 'Observaciones: No se registran observaciones adicionales en el historial financiero consultado.';
    }
}

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

$templatePath = __DIR__ . "/PLANTILLA INFORME CONSULTA FINANCIERA.xls";
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->getSheetByName('ESTUDIO NORMAL');
if (!$sheet) {
    $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'] ?? '';

// Usando los mismos helpers que con Hechos
setValueRightOfLabel($sheet, 'Nombres y Apellidos', $nombreCompleto);
setValueRightOfLabel($sheet, 'Cédula', $cedula);
setValueRightOfLabel($sheet, 'Cliente', $empresa);
setValueRightOfLabel($sheet, 'Cargo', $cargoAspira);

// Fecha de realización -> justo a la derecha del rótulo "Fecha de Realización"
setValueRightOfLabel($sheet, 'Fecha de Realización', date('Y-m-d'));

// ---- Reporte financiero ----

// 1) Cantidad de obligaciones activas
setValueRightOfLabel($sheet, 'Cantidad de obligaciones activas', $totalObligaciones);

// 2) Sector al que pertenecen (texto tipo "(Sector Financiero, Telcos, ...).")
setValueRightOfLabel($sheet, 'Sector al que pertenecen', $sectoresString);

// 3) Está reportado negativamente => "SI" o "NO"
setValueRightOfLabel($sheet, 'Esta reportado negativamente', $estaReportadoTexto);

// 4) Si está reportado, qué empresas son => "Claro, Davivienda"
setValueRightOfLabel($sheet, 'Si esta repirtado que empresas son', $empresasString);

// 5) Presenta cartera castigada => "SI" o "NO"
setValueRightOfLabel($sheet, 'Presenta cartera castigada', $carteraCastigadaTxt);

// 6) Observaciones: todo en el bloque grande de abajo
setValueInSameLabelCell($sheet, 'Observaciones', $observacionesTxt);

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

$filename = "Informe_Historial_Financiero_{$idCandidato}.xls";

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

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