<?php
if (session_status() == PHP_SESSION_NONE) {
    session_start();
}
require_once dirname(__DIR__) . '/includes/db.php';
require_once dirname(__DIR__) . '/includes/auth.php';
require_once dirname(__DIR__) . '/includes/helpers.php';
require_once dirname(__DIR__) . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

check_role('administrador');

// Get template type from GET parameter
$template_type = sanitize_get('type', 'string', ['max_length' => 20]) ?: 'students';

// Create new spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Add institution logo and title
$logoPath = dirname(__DIR__) . '/assets/img/logo.png';
if (file_exists($logoPath)) {
    $drawing = new Drawing();
    $drawing->setName('Institution Logo');
    $drawing->setDescription('Institution Logo');
    $drawing->setPath($logoPath);
    $drawing->setHeight(80);
    $drawing->setCoordinates('A1');
    $drawing->setWorksheet($sheet);
}

// Institution title
$sheet->setCellValue('B1', 'BOLSA DE TRABAJO');

// Template-specific title and instructions
switch ($template_type) {
    case 'students':
        $sheet->setCellValue('B2', 'PLANTILLA PARA CARGA DE ALUMNOS');
        $sheet->setCellValue('B3', 'Generado el: ' . date('d/m/Y H:i'));
        $instructions = [
            'Complete las columnas con la información de los alumnos.',
            'Los campos marcados con * son obligatorios.',
            'El nombre de la carrera debe coincidir exactamente con uno existente en el sistema.',
            'La contraseña debe tener al menos 8 caracteres.',
            'No modifique los encabezados de las columnas.'
        ];
        $headers = ['username *', 'email *', 'password *', 'first_name *', 'last_name *', 'career_name *', 'dni', 'phone'];
        $sample = ['juan_perez', 'juan.perez@email.com', 'password123', 'Juan', 'Pérez', 'Ingeniería de Sistemas', '12345678', '987654321'];
        break;

    case 'graduates':
        $sheet->setCellValue('B2', 'PLANTILLA PARA CARGA DE EGRESADOS');
        $sheet->setCellValue('B3', 'Generado el: ' . date('d/m/Y H:i'));
        $instructions = [
            'Complete las columnas con la información de los egresados.',
            'Los campos marcados con * son obligatorios.',
            'El nombre de la carrera debe coincidir exactamente con uno existente en el sistema.',
            'La contraseña debe tener al menos 8 caracteres.',
            'El año de graduación es opcional.',
            'No modifique los encabezados de las columnas.'
        ];
        $headers = ['username *', 'email *', 'password *', 'first_name *', 'last_name *', 'career_name *', 'dni', 'phone', 'graduation_year'];
        $sample = ['maria_gomez', 'maria.gomez@email.com', 'password123', 'María', 'Gómez', 'Ingeniería de Sistemas', '87654321', '987654322', '2023'];
        break;

    case 'teachers':
        $sheet->setCellValue('B2', 'PLANTILLA PARA CARGA DE PROFESORES');
        $sheet->setCellValue('B3', 'Generado el: ' . date('d/m/Y H:i'));
        $instructions = [
            'Complete las columnas con la información de los profesores.',
            'Los campos marcados con * son obligatorios.',
            'El nombre de la carrera es opcional para profesores.',
            'La contraseña debe tener al menos 8 caracteres.',
            'No modifique los encabezados de las columnas.'
        ];
        $headers = ['username *', 'email *', 'password *', 'first_name *', 'last_name *', 'career_name', 'department', 'dni', 'phone'];
        $sample = ['carlos_lopez', 'carlos.lopez@email.com', 'password123', 'Carlos', 'López', 'Ingeniería de Sistemas', 'Departamento de Informática', '11223344', '987654323'];
        break;

    default:
        $template_type = 'students';
        $sheet->setCellValue('B2', 'PLANTILLA PARA CARGA DE ALUMNOS');
        $sheet->setCellValue('B3', 'Generado el: ' . date('d/m/Y H:i'));
        $instructions = [
            'Complete las columnas con la información de los alumnos.',
            'Los campos marcados con * son obligatorios.',
            'El nombre de la carrera debe coincidir exactamente con uno existente en el sistema.',
            'La contraseña debe tener al menos 8 caracteres.',
            'No modifique los encabezados de las columnas.'
        ];
        $headers = ['username *', 'email *', 'password *', 'first_name *', 'last_name *', 'career_name *', 'dni', 'phone'];
        $sample = ['juan_perez', 'juan.perez@email.com', 'password123', 'Juan', 'Pérez', 'Ingeniería de Sistemas', '12345678', '987654321'];
        break;
}

// Style title
$titleStyle = [
    'font' => ['bold' => true, 'size' => 16],
    'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER]
];
$subtitleStyle = [
    'font' => ['bold' => true, 'size' => 12],
    'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER]
];
$dateStyle = [
    'font' => ['size' => 10],
    'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER]
];

$sheet->getStyle('B1')->applyFromArray($titleStyle);
$sheet->getStyle('B2')->applyFromArray($subtitleStyle);
$sheet->getStyle('B3')->applyFromArray($dateStyle);

// Merge cells for title
$sheet->mergeCells('B1:' . chr(65 + count($headers) - 1) . '1');
$sheet->mergeCells('B2:' . chr(65 + count($headers) - 1) . '2');
$sheet->mergeCells('B3:' . chr(65 + count($headers) - 1) . '3');

// Instructions section
$sheet->setCellValue('A5', 'INSTRUCCIONES:');
$sheet->getStyle('A5')->getFont()->setBold(true);

$row = 6;
foreach ($instructions as $instruction) {
    $sheet->setCellValue('A' . $row, '• ' . $instruction);
    $row++;
}

// Header row (starting after instructions)
$headerRow = $row + 1;
foreach ($headers as $col => $header) {
    $sheet->setCellValue(chr(65 + $col) . $headerRow, $header);
}

// Style header
$headerStyle = [
    'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
    'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['rgb' => '007BFF']],
    'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER]
];
$sheet->getStyle('A' . $headerRow . ':' . chr(65 + count($headers) - 1) . $headerRow)->applyFromArray($headerStyle);

// Sample data row
$sampleRow = $headerRow + 1;
foreach ($sample as $col => $value) {
    $sheet->setCellValue(chr(65 + $col) . $sampleRow, $value);
}

// Style sample row
$sampleStyle = [
    'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['rgb' => 'F8F9FA']]
];
$sheet->getStyle('A' . $sampleRow . ':' . chr(65 + count($headers) - 1) . $sampleRow)->applyFromArray($sampleStyle);

// Get available careers for reference (only for students and graduates)
if (in_array($template_type, ['students', 'graduates'])) {
    $careers_stmt = $pdo->query("SELECT name FROM careers ORDER BY name");
    $careers = $careers_stmt->fetchAll(PDO::FETCH_COLUMN);

    // Add careers reference in a separate section
    $careersRow = $sampleRow + 3;
    $sheet->setCellValue('A' . $careersRow, 'CARRERAS DISPONIBLES:');
    $sheet->getStyle('A' . $careersRow)->getFont()->setBold(true);

    $careersRow++;
    $sheet->setCellValue('A' . $careersRow, 'Lista de carreras válidas para el campo career_name:');
    $sheet->getStyle('A' . $careersRow)->getFont()->setItalic(true);

    $careersRow++;
    foreach ($careers as $career) {
        $sheet->setCellValue('A' . $careersRow, '• ' . $career);
        $careersRow++;
    }
}

// Auto-size columns
foreach (range('A', chr(65 + count($headers) - 1)) as $column) {
    $sheet->getColumnDimension($column)->setAutoSize(true);
}

// Set minimum column widths
foreach ($headers as $col => $header) {
    $column = chr(65 + $col);
    $minWidth = match($column) {
        'A' => 15, // username
        'B' => 25, // email
        'C' => 15, // password
        'D' => 15, // first_name
        'E' => 15, // last_name
        'F' => 20, // career_name
        'G' => in_array($template_type, ['students', 'graduates']) ? 12 : 20, // dni or department
        'H' => 15, // phone or dni
        'I' => 15, // phone or graduation_year
        default => 15
    };
    $sheet->getColumnDimension($column)->setWidth(max($sheet->getColumnDimension($column)->getWidth(), $minWidth));
}

// Output Excel file
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename=plantilla_' . $template_type . '.xlsx');
header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;
?>