<?php
/**
 * Enhanced Detailed Report Export for Scientific Abstracts Dashboard
 * 
 * This file provides enhanced Excel export functionality for detailed reports
 * grouped by competition type with proper formatting and styling.
 */

// Function to generate detailed report with proper Excel formatting
function generateDetailedReport($conn) {
    // Check if PhpSpreadsheet is available
    if (!class_exists('PhpOffice\PhpSpreadsheet\Spreadsheet')) {
        // Try to load autoloader
        if (file_exists('vendor/autoload.php')) {
            require_once 'vendor/autoload.php';
        } else {
            // Fallback to original ZIP-based CSV export
            return false;
        }
    }
    
    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
    use PhpOffice\PhpSpreadsheet\Style\Alignment;
    use PhpOffice\PhpSpreadsheet\Style\Border;
    use PhpOffice\PhpSpreadsheet\Style\Color;
    use PhpOffice\PhpSpreadsheet\Style\Fill;
    use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
    use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
    
    // Get competition types
    $sql_tipos_competicion = "SELECT id, codigo, descripcion_esp FROM ttipo_competicion";
    $result_tipos = $conn->query($sql_tipos_competicion);
    
    $tipos_competicion = [];
    if ($result_tipos->num_rows > 0) {
        while($row = $result_tipos->fetch_assoc()) {
            $tipos_competicion[$row['codigo']] = [
                'id' => $row['id'],
                'nombre' => $row['descripcion_esp']
            ];
        }
    }
    
    // Get presentation types
    $sql_tipos_presentacion = "SELECT id, codigo, descripcion_esp FROM ttipo_presentacion";
    $result_presentacion = $conn->query($sql_tipos_presentacion);
    
    $tipos_presentacion = [];
    if ($result_presentacion->num_rows > 0) {
        while($row = $result_presentacion->fetch_assoc()) {
            $tipos_presentacion[$row['codigo']] = [
                'id' => $row['id'],
                'nombre' => $row['descripcion_esp']
            ];
        }
    }
    
    // Get evaluators information
    $sql_evaluadores = "SELECT id, nombre FROM tusuario WHERE tipousuario IN ('E', 'evaluador')";
    $result_evaluadores = $conn->query($sql_evaluadores);
    
    $evaluadores = [];
    if ($result_evaluadores && $result_evaluadores->num_rows > 0) {
        while($row = $result_evaluadores->fetch_assoc()) {
            $evaluadores[$row['id']] = $row['nombre'];
        }
    }
    
    // Create a new spreadsheet for the combined report
    $masterSpreadsheet = new Spreadsheet();
    $masterSpreadsheet->getProperties()
        ->setCreator('Sistema de Gestión de Trabajos Científicos')
        ->setLastModifiedBy('Sistema de Gestión de Trabajos Científicos')
        ->setTitle('Reporte Detallado por Competición')
        ->setSubject('Resumen Detallado de Trabajos Científicos')
        ->setDescription('Información detallada de trabajos científicos agrupados por tipo de competición.');
    
    // Set up master sheet
    $masterSheet = $masterSpreadsheet->getActiveSheet();
    $masterSheet->setTitle('Resumen');
    
    // Add title to master sheet
    $masterSheet->setCellValue('A1', 'REPORTE DETALLADO DE TRABAJOS CIENTÍFICOS');
    $masterSheet->setCellValue('A2', 'Generado el ' . date('d/m/Y H:i:s'));
    $masterSheet->mergeCells('A1:F1');
    $masterSheet->mergeCells('A2:F2');
    
    // Style title
    $masterSheet->getStyle('A1')->applyFromArray([
        'font' => [
            'bold' => true,
            'size' => 16,
            'color' => ['rgb' => '377970'],
        ],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
        ],
    ]);
    
    // Style date
    $masterSheet->getStyle('A2')->applyFromArray([
        'font' => [
            'italic' => true,
            'size' => 10,
        ],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
        ],
    ]);
    
    // Add summary information
    $masterSheet->setCellValue('A4', 'RESUMEN DE TIPOS DE COMPETICIÓN');
    $masterSheet->mergeCells('A4:F4');
    $masterSheet->getStyle('A4')->applyFromArray([
        'font' => [
            'bold' => true,
            'size' => 12,
            'color' => ['rgb' => 'FFFFFF'],
        ],
        'fill' => [
            'fillType' => Fill::FILL_SOLID,
            'color' => ['rgb' => '16222A'],
        ],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
        ],
    ]);
    
    // Headers for competition types summary
    $masterSheet->setCellValue('A5', 'CÓDIGO');
    $masterSheet->setCellValue('B5', 'TIPO DE COMPETICIÓN');
    $masterSheet->setCellValue('C5', 'TOTAL TRABAJOS');
    $masterSheet->setCellValue('D5', 'CALIFICACIÓN PROMEDIO');
    $masterSheet->setCellValue('E5', 'PAÍSES PARTICIPANTES');
    $masterSheet->setCellValue('F5', 'SHEET');
    
    $masterSheet->getStyle('A5:F5')->applyFromArray([
        'font' => [
            'bold' => true,
            'color' => ['rgb' => 'FFFFFF'],
        ],
        'fill' => [
            'fillType' => Fill::FILL_SOLID,
            'color' => ['rgb' => '377970'],
        ],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical' => Alignment::VERTICAL_CENTER,
        ],
        'borders' => [
            'allBorders' => [
                'borderStyle' => Border::BORDER_THIN,
                'color' => ['rgb' => '000000'],
            ],
        ],
    ]);
    
    // Set column widths
    $masterSheet->getColumnDimension('A')->setWidth(15);
    $masterSheet->getColumnDimension('B')->setWidth(40);
    $masterSheet->getColumnDimension('C')->setWidth(15);
    $masterSheet->getColumnDimension('D')->setWidth(20);
    $masterSheet->getColumnDimension('E')->setWidth(20);
    $masterSheet->getColumnDimension('F')->setWidth(20);
    
    // Track competition stats
    $competitionStats = [];
    $totalWorks = 0;
    $allCountries = [];
    
    // Define a color array for different competition types
    $competitionColors = [
        'E8F6F3', 'FCF3CF', 'F9EBEA', 'EAF2F8', 'F5EEF8', 
        'EAFAF1', 'FEF9E7', 'EBF7FA', 'F4F6F6', 'F6DDCC'
    ];
    $colorIndex = 0;
    
    // Initialize data arrays for each competition type
    $allCompetitionData = [];
    
    // Process each competition type
    foreach ($tipos_competicion as $codigo => $tipo) {
        // Get works for this competition type
        $sql = "SELECT 
            t.id,
            t.codigo,
            t.cod_tipo_competicion_propuesto,
            t.cod_tipo_presentacion_propuesto,
            t.cod_tipo_presentacion_aprobado,
            t.titulo,
            t.introduccion,
            t.materiales,
            t.resultados,
            t.conclusiones,
            t.pais,
            t.ciudad,
            t.nro_autores,
            t.email,
            t.apellido_corresponsal,
            t.nombre_corresponsal,
            t.area_tematica,
            t.evaluador1,
            t.evaluador2,
            t.evaluador3,
            (t.calificacion1_1 + t.calificacion1_2 + t.calificacion1_3 + t.calificacion1_4 + t.calificacion1_5 + t.calificacion1_6) AS calificacion_evaluador1,
            (t.calificacion2_1 + t.calificacion2_2 + t.calificacion2_3 + t.calificacion2_4 + t.calificacion2_5 + t.calificacion2_6) AS calificacion_evaluador2,
            (t.calificacion3_1 + t.calificacion3_2 + t.calificacion3_3 + t.calificacion3_4 + t.calificacion3_5 + t.calificacion3_6) AS calificacion_evaluador3,
            ROUND((
                (t.calificacion1_1 + t.calificacion1_2 + t.calificacion1_3 + t.calificacion1_4 + t.calificacion1_5 + t.calificacion1_6) +
                (t.calificacion2_1 + t.calificacion2_2 + t.calificacion2_3 + t.calificacion2_4 + t.calificacion2_5 + t.calificacion2_6) +
                (t.calificacion3_1 + t.calificacion3_2 + t.calificacion3_3 + t.calificacion3_4 + t.calificacion3_5 + t.calificacion3_6)
            ) / 3, 2) AS promedio_calificacion,
            t.autores,
            t.inst_autor1,
            CONCAT(t.titulo_autor1, ' ', t.autor1, ' ', t.apellido_autor1) AS autor1_completo,
            CONCAT(t.titulo_autor2, ' ', t.autor2, ' ', t.apellido_autor2) AS autor2_completo,
            CONCAT(t.titulo_autor3, ' ', t.autor3, ' ', t.apellido_autor3) AS autor3_completo,
            CONCAT(t.titulo_autor4, ' ', t.autor4, ' ', t.apellido_autor4) AS autor4_completo,
            CONCAT(t.titulo_autor5, ' ', t.autor5, ' ', t.apellido_autor5) AS autor5_completo,
            CONCAT(t.titulo_autor6, ' ', t.autor6, ' ', t.apellido_autor6) AS autor6_completo,
            CONCAT(t.titulo_autor7, ' ', t.autor7, ' ', t.apellido_autor7) AS autor7_completo,
            CONCAT(t.titulo_autor8, ' ', t.autor8, ' ', t.apellido_autor8) AS autor8_completo,
            CONCAT(t.titulo_autor9, ' ', t.autor9, ' ', t.apellido_autor9) AS autor9_completo,
            CONCAT(t.titulo_autor10, ' ', t.autor10, ' ', t.apellido_autor10) AS autor10_completo
        FROM 
            ttrabajo t
        WHERE 
            t.cod_tipo_competicion_propuesto = ? 
            AND t.calificacion1_fecha IS NOT NULL 
            AND t.calificacion2_fecha IS NOT NULL 
            AND t.calificacion3_fecha IS NOT NULL
        ORDER BY 
            t.pais ASC, promedio_calificacion DESC";
        
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("s", $codigo);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $trabajos = [];
        $countriesForCompetition = [];
        $totalScoreForCompetition = 0;
        
        if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
                // Prepare presentation type
                $tipo_presentacion = "No definido";
                if (!empty($row['cod_tipo_presentacion_aprobado']) && isset($tipos_presentacion[$row['cod_tipo_presentacion_aprobado']])) {
                    $tipo_presentacion = $tipos_presentacion[$row['cod_tipo_presentacion_aprobado']]['nombre'];
                } else if (!empty($row['cod_tipo_presentacion_propuesto']) && isset($tipos_presentacion[$row['cod_tipo_presentacion_propuesto']])) {
                    $tipo_presentacion = $tipos_presentacion[$row['cod_tipo_presentacion_propuesto']]['nombre'];
                }
                
                // Get evaluator names
                $evaluador1 = isset($evaluadores[$row['evaluador1']]) ? $evaluadores[$row['evaluador1']] : "No asignado";
                $evaluador2 = isset($evaluadores[$row['evaluador2']]) ? $evaluadores[$row['evaluador2']] : "No asignado";
                $evaluador3 = isset($evaluadores[$row['evaluador3']]) ? $evaluadores[$row['evaluador3']] : "No asignado";
                
                // Calculate scores
                $calificacion_eval1 = $row['calificacion_evaluador1'];
                $calificacion_eval2 = $row['calificacion_evaluador2'];
                $calificacion_eval3 = $row['calificacion_evaluador3'];
                $promedio_calificacion = $row['promedio_calificacion'];
                
                $totalScoreForCompetition += $promedio_calificacion;
                
                // Get complete authors list
                $todos_autores = [];
                for ($i = 1; $i <= 10; $i++) {
                    if (!empty($row["autor{$i}_completo"]) && $row["autor{$i}_completo"] != '  ') {
                        $todos_autores[] = trim($row["autor{$i}_completo"]);
                    }
                }
                $lista_autores = implode("; ", $todos_autores);
                
                // Store country for statistics
                $countriesForCompetition[$row['pais']] = true;
                $allCountries[$row['pais']] = true;
                
                // Add work to array
                $trabajos[] = [
                    'codigo' => $row['codigo'],
                    'categoria' => $codigo,
                    'tipo_presentacion' => $tipo_presentacion,
                    'titulo' => $row['titulo'],
                    'introduccion' => $row['introduccion'],
                    'materiales' => $row['materiales'],
                    'resultados' => $row['resultados'],
                    'conclusiones' => $row['conclusiones'],
                    'pais' => $row['pais'],
                    'institucion' => $row['inst_autor1'],
                    'corresponsal' => trim($row['nombre_corresponsal'] . ' ' . $row['apellido_corresponsal']),
                    'email' => $row['email'],
                    'nro_autores' => $row['nro_autores'],
                    'todos_autores' => $lista_autores,
                    'evaluador1' => $evaluador1,
                    'evaluador2' => $evaluador2,
                    'evaluador3' => $evaluador3,
                    'calificacion_eval1' => $calificacion_eval1,
                    'calificacion_eval2' => $calificacion_eval2,
                    'calificacion_eval3' => $calificacion_eval3,
                    'calificacion_promedio' => $promedio_calificacion
                ];
            }
        }
        
        $stmt->close();
        
        // Store statistics for this competition type
        $competitionStats[$codigo] = [
            'codigo' => $codigo,
            'nombre' => $tipo['nombre'],
            'total_trabajos' => count($trabajos),
            'promedio_calificacion' => count($trabajos) > 0 ? $totalScoreForCompetition / count($trabajos) : 0,
            'paises' => count($countriesForCompetition),
            'color' => $competitionColors[$colorIndex % count($competitionColors)]
        ];
        
        $colorIndex++;
        $totalWorks += count($trabajos);
        
        // Store all data for this competition type
        $allCompetitionData[$codigo] = $trabajos;
    }
    
    // Add competition types summary to master sheet
    $row = 6;
    foreach ($competitionStats as $codigo => $stats) {
        // Skip if no works
        if ($stats['total_trabajos'] == 0) continue;
        
        // Sanitize sheet name
        $sheetName = 'C_' . substr(preg_replace('/[^a-zA-Z0-9]/', '', $codigo), 0, 25);
        
        $masterSheet->setCellValue('A' . $row, $codigo);
        $masterSheet->setCellValue('B' . $row, $stats['nombre']);
        $masterSheet->setCellValue('C' . $row, $stats['total_trabajos']);
        $masterSheet->setCellValue('D' . $row, $stats['promedio_calificacion']);
        $masterSheet->setCellValue('E' . $row, $stats['paises']);
        
        // Add link to corresponding sheet
        $masterSheet->setCellValue('F' . $row, 'Ver detalles');
        
        // Style the link
        $masterSheet->getCell('F' . $row)->getHyperlink()->setUrl("sheet://{$sheetName}");
        $masterSheet->getStyle('F' . $row)->getFont()->setColor(new Color(Color::COLOR_BLUE));
        $masterSheet->getStyle('F' . $row)->getFont()->setUnderline(true);
        
        // Style row with the competition color
        $masterSheet->getStyle('A' . $row . ':F' . $row)->applyFromArray([
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'color' => ['rgb' => $stats['color']],
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['rgb' => 'CCCCCC'],
                ],
            ],
        ]);
        
        // Center some cells
        $masterSheet->getStyle('C' . $row . ':F' . $row)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        
        // Format numbers
        $masterSheet->getStyle('D' . $row)->getNumberFormat()->setFormatCode('0.00');
        
        $row++;
    }
    
    // Add total row
    $masterSheet->setCellValue('A' . $row, 'TOTAL');
    $masterSheet->setCellValue('C' . $row, $totalWorks);
    $masterSheet->setCellValue('E' . $row, count($allCountries));
    
    $masterSheet->mergeCells('A' . $row . ':B' . $row);
    $masterSheet->getStyle('A' . $row . ':F' . $row)->applyFromArray([
        'font' => [
            'bold' => true,
        ],
        'fill' => [
            'fillType' => Fill::FILL_SOLID,
            'color' => ['rgb' => 'D5D8DC'],
        ],
        'borders' => [
            'outline' => [
                'borderStyle' => Border::BORDER_MEDIUM,
                'color' => ['rgb' => '000000'],
            ],
        ],
    ]);
    
    // Create a worksheet for each competition type
    foreach ($competitionStats as $codigo => $stats) {
        // Skip if no works
        if ($stats['total_trabajos'] == 0 || !isset($allCompetitionData[$codigo])) continue;
        
        // Get works for this competition
        $trabajos = $allCompetitionData[$codigo];
        
        // Sanitize sheet name
        $sheetName = 'C_' . substr(preg_replace('/[^a-zA-Z0-9]/', '', $codigo), 0, 25);
        
        // Create new sheet
        $sheet = $masterSpreadsheet->createSheet();
        $sheet->setTitle($sheetName);
        
        // Set sheet color
        $sheet->getTabColor()->setRGB($stats['color']);
        
        // Add title
        $sheet->setCellValue('A1', 'TIPO DE COMPETICIÓN: ' . strtoupper($stats['nombre']) . ' (' . $codigo . ')');
        $sheet->mergeCells('A1:T1');
        $sheet->getStyle('A1')->applyFromArray([
            'font' => [
                'bold' => true,
                'size' => 14,
                'color' => ['rgb' => 'FFFFFF'],
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'color' => ['rgb' => '16222A'],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
        ]);
        
        // Add headers
        $headers = [
            'A' => 'CÓDIGO',
            'B' => 'PAÍS',
            'C' => 'TIPO PRESENTACIÓN',
            'D' => 'TÍTULO',
            'E' => 'INSTITUCIÓN',
            'F' => 'PRESENTADOR',
            'G' => 'EMAIL',
            'H' => 'AUTORES',
            'I' => 'EVALUADOR 1',
            'J' => 'CALIFICACIÓN 1',
            'K' => 'EVALUADOR 2',
            'L' => 'CALIFICACIÓN 2',
            'M' => 'EVALUADOR 3',
            'N' => 'CALIFICACIÓN 3',
            'O' => 'CALIFICACIÓN PROMEDIO',
            'P' => 'INTRODUCCIÓN',
            'Q' => 'MATERIALES',
            'R' => 'RESULTADOS',
            'S' => 'CONCLUSIONES',
            'T' => 'RANGO'
        ];
        
        foreach ($headers as $col => $header) {
            $sheet->setCellValue($col . '2', $header);
        }
        
        // Style headers
        $sheet->getStyle('A2:T2')->applyFromArray([
            'font' => [
                'bold' => true,
                'color' => ['rgb' => 'FFFFFF'],
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'color' => ['rgb' => '377970'],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['rgb' => '000000'],
                ],
            ],
        ]);
        
        // Set column widths
        $columnWidths = [
            'A' => 12, 'B' => 15, 'C' => 20, 'D' => 40, 'E' => 25,
            'F' => 25, 'G' => 25, 'H' => 40, 'I' => 20, 'J' => 12,
            'K' => 20, 'L' => 12, 'M' => 20, 'N' => 12, 'O' => 15,
            'P' => 30, 'Q' => 30, 'R' => 30, 'S' => 30, 'T' => 12
        ];
        
        foreach ($columnWidths as $col => $width) {
            $sheet->getColumnDimension($col)->setWidth($width);
        }
        
        // Track unique elements for color coding
        $tiposPresentacionColors = [];
        $evaluadoresColors = [];
        $paisesColors = [];
        $presentationColorIndex = 0;
        $evaluatorColorIndex = 0;
        $paisColorIndex = 0;
        $presentationColors = ['E8F6F3', 'FCF3CF', 'F9EBEA', 'EAF2F8', 'F5EEF8', 'EAFAF1', 'FEF9E7'];
        $evaluatorColors = ['E8F8F5', 'FEF9E7', 'F9EBEA', 'F5EEF8', 'EAF2F8', 'EAFAF1', 'F2F3F4'];
        $countryColors = ['F2F6FC', 'EBF5FB', 'FDEBD0', 'FADBD8', 'F5EEF8', 'D6EAF8', 'D1F2EB'];
        
        // Populate data rows
        $row = 3;
        foreach ($trabajos as $trabajo) {
            // Determine colors for consistent styling
            if (!isset($tiposPresentacionColors[$trabajo['tipo_presentacion']])) {
                $tiposPresentacionColors[$trabajo['tipo_presentacion']] = $presentationColors[$presentationColorIndex % count($presentationColors)];
                $presentationColorIndex++;
            }
            
            if (!isset($evaluadoresColors[$trabajo['evaluador1']])) {
                $evaluadoresColors[$trabajo['evaluador1']] = $evaluatorColors[$evaluatorColorIndex % count($evaluatorColors)];
                $evaluatorColorIndex++;
            }
            
            if (!isset($evaluadoresColors[$trabajo['evaluador2']])) {
                $evaluadoresColors[$trabajo['evaluador2']] = $evaluatorColors[$evaluatorColorIndex % count($evaluatorColors)];
                $evaluatorColorIndex++;
            }
            
            if (!isset($evaluadoresColors[$trabajo['evaluador3']])) {
                $evaluadoresColors[$trabajo['evaluador3']] = $evaluatorColors[$evaluatorColorIndex % count($evaluatorColors)];
                $evaluatorColorIndex++;
            }
            
            if (!isset($paisesColors[$trabajo['pais']])) {
                $paisesColors[$trabajo['pais']] = $countryColors[$paisColorIndex % count($countryColors)];
                $paisColorIndex++;
            }
            
            // Set cell values
            $sheet->setCellValue('A' . $row, $trabajo['codigo']);
            $sheet->setCellValue('B' . $row, $trabajo['pais']);
            $sheet->setCellValue('C' . $row, $trabajo['tipo_presentacion']);
            $sheet->setCellValue('D' . $row, html_entity_decode(strip_tags($trabajo['titulo']), ENT_QUOTES, 'UTF-8'));
            $sheet->setCellValue('E' . $row, $trabajo['institucion']);
            $sheet->setCellValue('F' . $row, $trabajo['corresponsal']);
            $sheet->setCellValue('G' . $row, $trabajo['email']);
            $sheet->setCellValue('H' . $row, $trabajo['todos_autores']);
            $sheet->setCellValue('I' . $row, $trabajo['evaluador1']);
            $sheet->setCellValue('J' . $row, $trabajo['calificacion_eval1']);
            $sheet->setCellValue('K' . $row, $trabajo['evaluador2']);
            $sheet->setCellValue('L' . $row, $trabajo['calificacion_eval2']);
            $sheet->setCellValue('M' . $row, $trabajo['evaluador3']);
            $sheet->setCellValue('N' . $row, $trabajo['calificacion_eval3']);
            $sheet->setCellValue('O' . $row, $trabajo['calificacion_promedio']);
            $sheet->setCellValue('P' . $row, html_entity_decode(strip_tags($trabajo['introduccion']), ENT_QUOTES, 'UTF-8'));
            $sheet->setCellValue('Q' . $row, html_entity_decode(strip_tags($trabajo['materiales']), ENT_QUOTES, 'UTF-8'));
            $sheet->setCellValue('R' . $row, html_entity_decode(strip_tags($trabajo['resultados']), ENT_QUOTES, 'UTF-8'));
            $sheet->setCellValue('S' . $row, html_entity_decode(strip_tags($trabajo['conclusiones']), ENT_QUOTES, 'UTF-8'));
            
            // Determine rating range
            $calificacion = floatval($trabajo['calificacion_promedio']);
            $rango = '';
            $rangoColor = '';
            
            if ($calificacion >= 90) {
                $rango = 'Excelente';
                $rangoColor = '2ECC71'; // Green
            } elseif ($calificacion >= 75) {
                $rango = 'Bueno';
                $rangoColor = '3498DB'; // Blue
            } elseif ($calificacion >= 60) {
                $rango = 'Regular';
                $rangoColor = 'F39C12'; // Orange
            } else {
                $rango = 'Deficiente';
                $rangoColor = 'E74C3C'; // Red
            }
            
            $sheet->setCellValue('T' . $row, $rango);
            
            // Apply color formatting for specific cells
            // Country styling
            $sheet->getStyle('B' . $row)->applyFromArray([
                'fill' => [
                    'fillType' => Fill::FILL_SOLID,
                    'color' => ['rgb' => $paisesColors[$trabajo['pais']]],
                ],
            ]);
            
            // Presentation type styling
            $sheet->getStyle('C' . $row)->applyFromArray([
                'fill' => [
                    'fillType' => Fill::FILL_SOLID,
                    'color' => ['rgb' => $tiposPresentacionColors[$trabajo['tipo_presentacion']]],
                ],
            ]);
            
            // Evaluator styling
            $sheet->getStyle('I' . $row)->applyFromArray([
                'fill' => [
                    'fillType' => Fill::FILL_SOLID,
                    'color' => ['rgb' => $evaluadoresColors[$trabajo['evaluador1']]],
                ],
            ]);
            
            $sheet->getStyle('K' . $row)->applyFromArray([
                'fill' => [
                    'fillType' => Fill::FILL_SOLID,
                    'color' => ['rgb' => $evaluadoresColors[$trabajo['evaluador2']]],
                ],
            ]);
            
            $sheet->getStyle('M' . $row)->applyFromArray([
                'fill' => [
                    'fillType' => Fill::FILL_SOLID,
                    'color' => ['rgb' => $evaluadoresColors[$trabajo['evaluador3']]],
                ],
            ]);
            
            // Qualification styling
            $calStyle = [
                'font' => ['color' => ['rgb' => 'FFFFFF']],
                'fill' => ['fillType' => Fill::FILL_SOLID, 'color' => ['rgb' => $rangoColor]],
                'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
            ];
            
            $sheet->getStyle('J' . $row)->applyFromArray($calStyle);
            $sheet->getStyle('L' . $row)->applyFromArray($calStyle);
            $sheet->getStyle('N' . $row)->applyFromArray($calStyle);
            $sheet->getStyle('O' . $row)->applyFromArray($calStyle);
            $sheet->getStyle('T' . $row)->applyFromArray($calStyle);
            
            // Format numbers
            $sheet->getStyle('J' . $row)->getNumberFormat()->setFormatCode('0.00');
            $sheet->getStyle('L' . $row)->getNumberFormat()->setFormatCode('0.00');
            $sheet->getStyle('N' . $row)->getNumberFormat()->setFormatCode('0.00');
            $sheet->getStyle('O' . $row)->getNumberFormat()->setFormatCode('0.00');
            
            // Add borders
            $sheet->getStyle('A' . $row . ':T' . $row)->applyFromArray([
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => Border::BORDER_THIN,
                        'color' => ['rgb' => 'CCCCCC'],
                    ],
                ],
            ]);
            
            // Apply text wrapping for content cells
            $sheet->getStyle('D' . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle('H' . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle('P' . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle('Q' . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle('R' . $row)->getAlignment()->setWrapText(true);
            $sheet->getStyle('S' . $row)->getAlignment()->setWrapText(true);
            
            $row++;
        }
        
        // Add summary at the bottom
        $row += 2;
        $sheet->setCellValue('A' . $row, 'RESUMEN DE ' . strtoupper($stats['nombre']));
        $sheet->mergeCells('A' . $row . ':T' . $row);
        $sheet->getStyle('A' . $row)->applyFromArray([
            'font' => [
                'bold' => true,
                'size' => 12,
                'color' => ['rgb' => 'FFFFFF'],
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'color' => ['rgb' => '16222A'],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
        ]);
        
        $row++;
        $sheet->setCellValue('A' . $row, 'Total de trabajos:');
        $sheet->setCellValue('B' . $row, $stats['total_trabajos']);
        
        $row++;
        $sheet->setCellValue('A' . $row, 'Promedio calificación:');
        $sheet->setCellValue('B' . $row, $stats['promedio_calificacion']);
        $sheet->getStyle('B' . $row)->getNumberFormat()->setFormatCode('0.00');
        
        $row++;
        $sheet->setCellValue('A' . $row, 'Países participantes:');
        $sheet->setCellValue('B' . $row, $stats['paises']);
        
        // Add a legend for colors
        $row += 2;
        $sheet->setCellValue('A' . $row, 'LEYENDA DE COLORES');
        $sheet->mergeCells('A' . $row . ':T' . $row);
        $sheet->getStyle('A' . $row)->applyFromArray([
            'font' => [
                'bold' => true,
                'size' => 11,
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
        ]);
        
        // Types of presentation legend
        $row++;
        $sheet->setCellValue('A' . $row, 'TIPOS DE PRESENTACIÓN:');
        $sheet->mergeCells('A' . $row . ':C' . $row);
        $sheet->getStyle('A' . $row)->getFont()->setBold(true);
        
        $row++;
        $legendCol = 'A';
        foreach ($tiposPresentacionColors as $tipo => $color) {
            $sheet->setCellValue($legendCol . $row, $tipo);
            $sheet->getStyle($legendCol . $row)->applyFromArray([
                'fill' => [
                    'fillType' => Fill::FILL_SOLID,
                    'color' => ['rgb' => $color],
                ],
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => Border::BORDER_THIN,
                        'color' => ['rgb' => 'CCCCCC'],
                    ],
                ],
            ]);
            
            $legendCol++;
            if ($legendCol > 'F') {
                $legendCol = 'A';
                $row++;
            }
        }
        
        // Evaluator legend
        $row += 2;
        $sheet->setCellValue('A' . $row, 'EVALUADORES:');
        $sheet->mergeCells('A' . $row . ':C' . $row);
        $sheet->getStyle('A' . $row)->getFont()->setBold(true);
        
        $row++;
        $legendCol = 'A';
        foreach ($evaluadoresColors as $evaluador => $color) {
            $sheet->setCellValue($legendCol . $row, $evaluador);
            $sheet->getStyle($legendCol . $row)->applyFromArray([
                'fill' => [
                    'fillType' => Fill::FILL_SOLID,
                    'color' => ['rgb' => $color],
                ],
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => Border::BORDER_THIN,
                        'color' => ['rgb' => 'CCCCCC'],
                    ],
                ],
            ]);
            
            $legendCol++;
            if ($legendCol > 'F') {
                $legendCol = 'A';
                $row++;
            }
        }
        
        // Freeze panes for better navigation
        $sheet->freezePane('A3');
    }
    
    // Set active sheet to the first one
    $masterSpreadsheet->setActiveSheetIndex(0);
    
    // Create Excel writer
    $writer = new Xlsx($masterSpreadsheet);
    
    // Set headers for file download
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="reporte_detallado_' . date('Y-m-d') . '.xlsx"');
    header('Cache-Control: max-age=0');
    
    // Save to PHP output
    $writer->save('php://output');
    
    return true;
}

// Updated function to handle detailed report export
function handleDetailedReportExport($conn) {
    // Try to use PhpSpreadsheet for enhanced Excel export
    $result = generateDetailedReport($conn);
    
    // Fallback to original ZIP-based CSV export if PhpSpreadsheet is not available
    if (!$result) {
        $csv_content = [];
        
        // Get competition types
        $sql_tipos_competicion = "SELECT id, codigo, descripcion_esp FROM ttipo_competicion";
        $result_tipos = $conn->query($sql_tipos_competicion);
        
        $tipos_competicion = [];
        if ($result_tipos->num_rows > 0) {
            while($row = $result_tipos->fetch_assoc()) {
                $tipos_competicion[$row['codigo']] = [
                    'id' => $row['id'],
                    'nombre' => $row['descripcion_esp']
                ];
            }
        }
        
        // Get presentation types
        $sql_tipos_presentacion = "SELECT id, codigo, descripcion_esp FROM ttipo_presentacion";
        $result_presentacion = $conn->query($sql_tipos_presentacion);
        
        $tipos_presentacion = [];
        if ($result_presentacion->num_rows > 0) {
            while($row = $result_presentacion->fetch_assoc()) {
                $tipos_presentacion[$row['codigo']] = [
                    'id' => $row['id'],
                    'nombre' => $row['descripcion_esp']
                ];
            }
        }
        
        // Get evaluators information
        $sql_evaluadores = "SELECT id, nombre FROM tusuario WHERE tipousuario IN ('E', 'evaluador')";
        $result_evaluadores = $conn->query($sql_evaluadores);
        
        $evaluadores = [];
        if ($result_evaluadores && $result_evaluadores->num_rows > 0) {
            while($row = $result_evaluadores->fetch_assoc()) {
                $evaluadores[$row['id']] = $row['nombre'];
            }
        }
        
        // Function to get works by competition type
        function obtenerTrabajosPorCompeticion($conn, $codCompeticion, $tipos_presentacion, $evaluadores) {
            $sql = "SELECT 
                t.id,
                t.codigo,
                t.cod_tipo_competicion_propuesto,
                t.cod_tipo_presentacion_propuesto,
                t.cod_tipo_presentacion_aprobado,
                t.titulo,
                t.introduccion,
                t.materiales,
                t.resultados,
                t.conclusiones,
                t.pais,
                t.ciudad,
                t.nro_autores,
                t.email,
                t.apellido_corresponsal,
                t.nombre_corresponsal,
                t.area_tematica,
                t.evaluador1,
                t.evaluador2,
                t.evaluador3,
                (t.calificacion1_1 + t.calificacion1_2 + t.calificacion1_3 + t.calificacion1_4 + t.calificacion1_5 + t.calificacion1_6) AS calificacion_evaluador1,
                (t.calificacion2_1 + t.calificacion2_2 + t.calificacion2_3 + t.calificacion2_4 + t.calificacion2_5 + t.calificacion2_6) AS calificacion_evaluador2,
                (t.calificacion3_1 + t.calificacion3_2 + t.calificacion3_3 + t.calificacion3_4 + t.calificacion3_5 + t.calificacion3_6) AS calificacion_evaluador3,
                ROUND((
                    (t.calificacion1_1 + t.calificacion1_2 + t.calificacion1_3 + t.calificacion1_4 + t.calificacion1_5 + t.calificacion1_6) +
                    (t.calificacion2_1 + t.calificacion2_2 + t.calificacion2_3 + t.calificacion2_4 + t.calificacion2_5 + t.calificacion2_6) +
                    (t.calificacion3_1 + t.calificacion3_2 + t.calificacion3_3 + t.calificacion3_4 + t.calificacion3_5 + t.calificacion3_6)
                ) / 3, 2) AS promedio_calificacion,
                t.autores,
                t.inst_autor1,
                CONCAT(t.titulo_autor1, ' ', t.autor1, ' ', t.apellido_autor1) AS autor1_completo,
                CONCAT(t.titulo_autor2, ' ', t.autor2, ' ', t.apellido_autor2) AS autor2_completo,
                CONCAT(t.titulo_autor3, ' ', t.autor3, ' ', t.apellido_autor3) AS autor3_completo,
                CONCAT(t.titulo_autor4, ' ', t.autor4, ' ', t.apellido_autor4) AS autor4_completo,
                CONCAT(t.titulo_autor5, ' ', t.autor5, ' ', t.apellido_autor5) AS autor5_completo,
                CONCAT(t.titulo_autor6, ' ', t.autor6, ' ', t.apellido_autor6) AS autor6_completo,
                CONCAT(t.titulo_autor7, ' ', t.autor7, ' ', t.apellido_autor7) AS autor7_completo,
                CONCAT(t.titulo_autor8, ' ', t.autor8, ' ', t.apellido_autor8) AS autor8_completo,
                CONCAT(t.titulo_autor9, ' ', t.autor9, ' ', t.apellido_autor9) AS autor9_completo,
                CONCAT(t.titulo_autor10, ' ', t.autor10, ' ', t.apellido_autor10) AS autor10_completo
            FROM 
                ttrabajo t
            WHERE 
                t.cod_tipo_competicion_propuesto = ? 
                AND t.calificacion1_fecha IS NOT NULL 
                AND t.calificacion2_fecha IS NOT NULL 
                AND t.calificacion3_fecha IS NOT NULL
            ORDER BY 
                t.pais ASC, promedio_calificacion DESC";
            
            $stmt = $conn->prepare($sql);
            $stmt->bind_param("s", $codCompeticion);
            $stmt->execute();
            $result = $stmt->get_result();
            
            $trabajos = [];
            if ($result->num_rows > 0) {
                while($row = $result->fetch_assoc()) {
                    // Prepare presentation type
                    $tipo_presentacion = "No definido";
                    if (!empty($row['cod_tipo_presentacion_aprobado']) && isset($tipos_presentacion[$row['cod_tipo_presentacion_aprobado']])) {
                        $tipo_presentacion = $tipos_presentacion[$row['cod_tipo_presentacion_aprobado']]['nombre'];
                    } else if (!empty($row['cod_tipo_presentacion_propuesto']) && isset($tipos_presentacion[$row['cod_tipo_presentacion_propuesto']])) {
                        $tipo_presentacion = $tipos_presentacion[$row['cod_tipo_presentacion_propuesto']]['nombre'];
                    }
                    
                    // Get evaluator names
                    $evaluador1 = isset($evaluadores[$row['evaluador1']]) ? $evaluadores[$row['evaluador1']] : "No asignado";
                    $evaluador2 = isset($evaluadores[$row['evaluador2']]) ? $evaluadores[$row['evaluador2']] : "No asignado";
                    $evaluador3 = isset($evaluadores[$row['evaluador3']]) ? $evaluadores[$row['evaluador3']] : "No asignado";
                    
                    // Get scores
                    $calificacion_eval1 = $row['calificacion_evaluador1'];
                    $calificacion_eval2 = $row['calificacion_evaluador2'];
                    $calificacion_eval3 = $row['calificacion_evaluador3'];
                    $promedio_calificacion = $row['promedio_calificacion'];
                    
                    // Complete authors list
                    $todos_autores = [];
                    for ($i = 1; $i <= 10; $i++) {
                        if (!empty($row["autor{$i}_completo"]) && $row["autor{$i}_completo"] != '  ') {
                            $todos_autores[] = trim($row["autor{$i}_completo"]);
                        }
                    }
                    $lista_autores = implode("; ", $todos_autores);
                    
                    // Add work to array
                    $trabajos[] = [
                        'codigo' => $row['codigo'],
                        'categoria' => $codCompeticion,
                        'tipo_presentacion' => $tipo_presentacion,
                        'titulo' => $row['titulo'],
                        'introduccion' => $row['introduccion'],
                        'materiales' => $row['materiales'],
                        'resultados' => $row['resultados'],
                        'conclusiones' => $row['conclusiones'],
                        'pais' => $row['pais'],
                        'institucion' => $row['inst_autor1'],
                        'corresponsal' => trim($row['nombre_corresponsal'] . ' ' . $row['apellido_corresponsal']),
                        'email' => $row['email'],
                        'nro_autores' => $row['nro_autores'],
                        'todos_autores' => $lista_autores,
                        'evaluador1' => $evaluador1,
                        'evaluador2' => $evaluador2,
                        'evaluador3' => $evaluador3,
                        'calificacion_eval1' => $calificacion_eval1,
                        'calificacion_eval2' => $calificacion_eval2,
                        'calificacion_eval3' => $calificacion_eval3,
                        'calificacion_promedio' => $promedio_calificacion
                    ];
                }
            }
            
            $stmt->close();
            return $trabajos;
        }
        
        // Create CSV files for each competition type
        foreach ($tipos_competicion as $codigo => $tipo) {
            // Get works for this competition type
            $trabajos = obtenerTrabajosPorCompeticion($conn, $codigo, $tipos_presentacion, $evaluadores);
            
            if (count($trabajos) > 0) {
                // File name
                $filename = 'resumen_' . sanitizeFileName($tipo['nombre']) . '_' . date('Y-m-d') . '.csv';
                
                // Create a temporary file for this competition type
                $output = fopen('php://temp', 'r+');
                
                // Set the correct field delimiter for Excel
                fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF)); // Write BOM for UTF-8
                
                // Write headers
                fputcsv($output, [
                    'Código', 
                    'Categoría', 
                    'Tipo de Presentación', 
                    'Título',
                    'Introducción', 
                    'Materiales', 
                    'Resultados', 
                    'Conclusiones', 
                    'País', 
                    'Institución', 
                    'Presentador', 
                    'Email', 
                    'Número de Autores', 
                    'Todos los Autores', 
                    'Evaluador 1', 
                    'Evaluador 2', 
                    'Evaluador 3', 
                    'Calificación Evaluador 1', 
                    'Calificación Evaluador 2', 
                    'Calificación Evaluador 3', 
                    'Calificación Promedio'
                ]);
                
                // Write data
                foreach ($trabajos as $trabajo) {
                    // To avoid issues with special characters and HTML tags
                    $fila = [
                        $trabajo['codigo'],
                        $tipo['nombre'],
                        $trabajo['tipo_presentacion'],
                        html_entity_decode(strip_tags($trabajo['titulo']), ENT_QUOTES, 'UTF-8'),
                        html_entity_decode(strip_tags($trabajo['introduccion']), ENT_QUOTES, 'UTF-8'),
                        html_entity_decode(strip_tags($trabajo['materiales']), ENT_QUOTES, 'UTF-8'),
                        html_entity_decode(strip_tags($trabajo['resultados']), ENT_QUOTES, 'UTF-8'),
                        html_entity_decode(strip_tags($trabajo['conclusiones']), ENT_QUOTES, 'UTF-8'),
                        $trabajo['pais'],
                        $trabajo['institucion'],
                        $trabajo['corresponsal'],
                        $trabajo['email'],
                        $trabajo['nro_autores'],
                        $trabajo['todos_autores'],
                        $trabajo['evaluador1'],
                        $trabajo['evaluador2'],
                        $trabajo['evaluador3'],
                        $trabajo['calificacion_eval1'],
                        $trabajo['calificacion_eval2'],
                        $trabajo['calificacion_eval3'],
                        $trabajo['calificacion_promedio']
                    ];
                    fputcsv($output, $fila);
                }
                
                // Save content in array
                rewind($output);
                $csv_content[$codigo] = stream_get_contents($output);
                fclose($output);
            }
        }
        
        // Create a ZIP file to contain all CSVs
        $zip_file = 'reporte_detallado_' . date('Y-m-d') . '.zip';
        $zip = new ZipArchive();
        
        // Create temporary ZIP file
        $temp_file = tempnam(sys_get_temp_dir(), 'zip');
        
        if ($zip->open($temp_file, ZipArchive::CREATE) === TRUE) {
            // Add each CSV to the ZIP file
            foreach ($csv_content as $codigo => $content) {
                $filename = 'resumen_' . sanitizeFileName($tipos_competicion[$codigo]['nombre']) . '.csv';
                $zip->addFromString($filename, $content);
            }
            
            // Close the ZIP
            $zip->close();
            
            // Read the content of the ZIP file
            $zip_content = file_get_contents($temp_file);
            
            // Delete temporary file
            unlink($temp_file);
            
            // Set headers for download
            header('Content-Type: application/zip');
            header('Content-Disposition: attachment; filename="' . $zip_file . '"');
            header('Content-Length: ' . strlen($zip_content));
            header('Cache-Control: max-age=0');
            
            // Send ZIP content
            echo $zip_content;
            exit;
        } else {
            // If ZIP creation fails, send a single CSV with all data
            header('Content-Type: text/csv; charset=utf-8');
            header('Content-Disposition: attachment; filename="reporte_detallado_' . date('Y-m-d') . '.csv"');
            
            $output = fopen('php://output', 'w');
            
            // Write BOM for UTF-8
            fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
            
            // Write headers
            fputcsv($output, [
                'Código', 
                'Categoría', 
                'Tipo de Presentación', 
                'Título',
                'Introducción', 
                'Materiales', 
                'Resultados', 
                'Conclusiones', 
                'País', 
                'Institución', 
                'Presentador', 
                'Email', 
                'Número de Autores', 
                'Todos los Autores', 
                'Evaluador 1', 
                'Evaluador 2', 
                'Evaluador 3', 
                'Calificación Evaluador 1', 
                'Calificación Evaluador 2', 
                'Calificación Evaluador 3', 
                'Calificación Promedio'
            ]);
            
            // Write all data
            foreach ($tipos_competicion as $codigo => $tipo) {
                $trabajos = obtenerTrabajosPorCompeticion($conn, $codigo, $tipos_presentacion, $evaluadores);
                
                foreach ($trabajos as $trabajo) {
                    $fila = [
                        $trabajo['codigo'],
                        $tipo['nombre'],
                        $trabajo['tipo_presentacion'],
                        html_entity_decode(strip_tags($trabajo['titulo']), ENT_QUOTES, 'UTF-8'),
                        html_entity_decode(strip_tags($trabajo['introduccion']), ENT_QUOTES, 'UTF-8'),
                        html_entity_decode(strip_tags($trabajo['materiales']), ENT_QUOTES, 'UTF-8'),
                        html_entity_decode(strip_tags($trabajo['resultados']), ENT_QUOTES, 'UTF-8'),
                        html_entity_decode(strip_tags($trabajo['conclusiones']), ENT_QUOTES, 'UTF-8'),
                        $trabajo['pais'],
                        $trabajo['institucion'],
                        $trabajo['corresponsal'],
                        $trabajo['email'],
                        $trabajo['nro_autores'],
                        $trabajo['todos_autores'],
                        $trabajo['evaluador1'],
                        $trabajo['evaluador2'],
                        $trabajo['evaluador3'],
                        $trabajo['calificacion_eval1'],
                        $trabajo['calificacion_eval2'],
                        $trabajo['calificacion_eval3'],
                        $trabajo['calificacion_promedio']
                    ];
                    fputcsv($output, $fila);
                }
            }
            
            fclose($output);
            exit;
        }
    }
    
    exit;
}