<?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__) . '/vendor/autoload.php';

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

check_role('administrador');

// 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');
$sheet->setCellValue('B2', 'PLANTILLA PARA CARGA DE ALUMNOS');
$sheet->setCellValue('B3', 'Generado el: ' . date('d/m/Y H:i'));

// 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:I1');
$sheet->mergeCells('B2:I2');
$sheet->mergeCells('B3:I3');

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

$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.',
    'Guarde el archivo en formato Excel (.xlsx) antes de subirlo.'
];

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

// Header row (starting after instructions)
$headerRow = $row + 1;
$sheet->setCellValue('A' . $headerRow, 'username *');
$sheet->setCellValue('B' . $headerRow, 'email *');
$sheet->setCellValue('C' . $headerRow, 'password *');
$sheet->setCellValue('D' . $headerRow, 'first_name *');
$sheet->setCellValue('E' . $headerRow, 'last_name *');
$sheet->setCellValue('F' . $headerRow, 'career_name *');
$sheet->setCellValue('G' . $headerRow, 'dni');
$sheet->setCellValue('H' . $headerRow, 'phone');

// 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 . ':H' . $headerRow)->applyFromArray($headerStyle);

// Sample data row
$sampleRow = $headerRow + 1;
$sheet->setCellValue('A' . $sampleRow, 'juan_perez');
$sheet->setCellValue('B' . $sampleRow, 'juan.perez@email.com');
$sheet->setCellValue('C' . $sampleRow, 'password123');
$sheet->setCellValue('D' . $sampleRow, 'Juan');
$sheet->setCellValue('E' . $sampleRow, 'Pérez');
$sheet->setCellValue('F' . $sampleRow, 'Ingeniería de Sistemas');
$sheet->setCellValue('G' . $sampleRow, '12345678');
$sheet->setCellValue('H' . $sampleRow, '987654321');

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

// Get available careers for reference
$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', 'H') as $column) {
    $sheet->getColumnDimension($column)->setAutoSize(true);
}

// Set minimum column widths
$sheet->getColumnDimension('A')->setWidth(max($sheet->getColumnDimension('A')->getWidth(), 15));
$sheet->getColumnDimension('B')->setWidth(max($sheet->getColumnDimension('B')->getWidth(), 25));
$sheet->getColumnDimension('C')->setWidth(max($sheet->getColumnDimension('C')->getWidth(), 15));
$sheet->getColumnDimension('D')->setWidth(max($sheet->getColumnDimension('D')->getWidth(), 15));
$sheet->getColumnDimension('E')->setWidth(max($sheet->getColumnDimension('E')->getWidth(), 15));
$sheet->getColumnDimension('F')->setWidth(max($sheet->getColumnDimension('F')->getWidth(), 20));
$sheet->getColumnDimension('G')->setWidth(max($sheet->getColumnDimension('G')->getWidth(), 12));
$sheet->getColumnDimension('H')->setWidth(max($sheet->getColumnDimension('H')->getWidth(), 15));

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

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