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

use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

check_role('administrador');

$format = $_GET['format'] ?? 'excel';
$search = isset($_GET['search']) ? trim($_GET['search']) : '';

// Base query
$query = "SELECT u.*, s.career_id, s.graduation_year, s.resume, s.dni, s.birth_date, s.address, s.phone, c.name as career_name FROM users u JOIN students s ON u.id = s.user_id LEFT JOIN careers c ON s.career_id = c.id";

$params = [];
if ($search) {
    $query .= " WHERE (u.first_name LIKE ? OR u.last_name LIKE ? OR u.email LIKE ? OR u.username LIKE ? OR c.name LIKE ? OR s.dni LIKE ? OR s.phone LIKE ? OR s.address LIKE ?)";
    $params = ["%$search%", "%$search%", "%$search%", "%$search%", "%$search%", "%$search%", "%$search%", "%$search%"];
}

$query .= " ORDER BY u.created_at DESC";

$stmt = $pdo->prepare($query);
$stmt->execute($params);
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);

if ($format == 'excel') {
    // --- EXCEL EXPORT ---
    $spreadsheet = new \PhpOffice\PhpSpreadsheet\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', 'REPORTE 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:J1');
    $sheet->mergeCells('B2:J2');
    $sheet->mergeCells('B3:J3');

    // Header row (starting from row 5)
    $headerRow = 5;
    $sheet->setCellValue('A' . $headerRow, 'Nro');
    $sheet->setCellValue('B' . $headerRow, 'Nombres');
    $sheet->setCellValue('C' . $headerRow, 'Apellidos');
    $sheet->setCellValue('D' . $headerRow, 'Email');
    $sheet->setCellValue('E' . $headerRow, 'Usuario');
    $sheet->setCellValue('F' . $headerRow, 'Programa de Estudio');
    $sheet->setCellValue('G' . $headerRow, 'DNI');
    $sheet->setCellValue('H' . $headerRow, 'Fecha de Nacimiento');
    $sheet->setCellValue('I' . $headerRow, 'Dirección');
    $sheet->setCellValue('J' . $headerRow, 'Teléfono');

    // Style header
    $headerStyle = [
        'font' => ['bold' => true],
        'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['rgb' => 'CCCCCC']]
    ];
    $sheet->getStyle('A' . $headerRow . ':J' . $headerRow)->applyFromArray($headerStyle);

    // Data rows (starting from row 6)
    $row = 6;
    $counter = 1;
    foreach ($students as $student) {
        $sheet->setCellValue('A' . $row, $counter);
        $sheet->setCellValue('B' . $row, $student['first_name'] ?? '');
        $sheet->setCellValue('C' . $row, $student['last_name'] ?? '');
        $sheet->setCellValue('D' . $row, $student['email'] ?? '');
        $sheet->setCellValue('E' . $row, $student['username'] ?? '');
        $sheet->setCellValue('F' . $row, $student['career_name'] ?? 'General');
        $sheet->setCellValue('G' . $row, $student['dni'] ?? '');
        $sheet->setCellValue('H' . $row, $student['birth_date'] ?? '');
        $sheet->setCellValue('I' . $row, $student['address'] ?? '');
        $sheet->setCellValue('J' . $row, $student['phone'] ?? '');
        $row++;
        $counter++;
    }

    // Auto-size columns
    foreach (range('A', 'J') as $column) {
        $sheet->getColumnDimension($column)->setAutoSize(true);
    }

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

    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save('php://output');
    exit;

} elseif ($format == 'pdf') {
    // --- PDF EXPORT ---

    class MYPDF extends TCPDF {
        protected $header_color = [40, 84, 128]; // Un azul corporativo
        protected $header_text_color = [255, 255, 255];
        protected $line_color = [200, 200, 200];

        public function Header() {
            $logoPath = dirname(__DIR__) . '/assets/img/logo.png';
            if (file_exists($logoPath)) {
                $this->Image($logoPath, 15, 10, 20, 0, 'PNG', '', 'T', false, 300, '', false, false, 0, false, false, false);
            }

            $this->SetFont('helvetica', 'B', 18);
            $this->SetTextColorArray($this->header_color);
            $this->SetXY(40, 12);
            $this->Cell(0, 10, 'BOLSA DE TRABAJO', 0, false, 'L');

            $this->SetFont('helvetica', 'B', 12);
            $this->SetXY(40, 20);
            $this->Cell(0, 10, 'Reporte de Alumnos', 0, false, 'L');

            $this->SetFont('helvetica', '', 9);
            $this->SetTextColor(100, 100, 100);
            $this->SetXY(0, 12);
            $this->Cell(0, 10, 'Generado: ' . date('d/m/Y H:i'), 0, false, 'R', 0, '', 0, false, 'T', 'M');

            // Línea divisoria
            $this->Line(15, 32, $this->getPageWidth() - 15, 32);
            $this->Ln(20);
        }

        public function Footer() {
            $this->SetY(-15);
            $this->SetFont('helvetica', 'I', 8);
            $this->SetTextColor(128);
            $this->Cell(0, 10, 'BOLSA DE TRABAJO | Página '.$this->getAliasNumPage().'/'.$this->getAliasNbPages(), 0, false, 'C', 0, '', 0, false, 'T', 'M');
        }

        public function drawTable($header, $data) {
            // Colores y fuentes
            $this->SetFillColor($this->header_color[0], $this->header_color[1], $this->header_color[2]);
            $this->SetTextColorArray($this->header_text_color);
            $this->SetFont('helvetica', 'B', 16);
            $this->SetFont('helvetica', 'B', 9);
            $w = [15, 55, 55, 60, 60, 30]; // Anchos de las columnas
            $num_headers = count($header);
            for($i = 0; $i < $num_headers; ++$i) {
                $this->Cell($w[$i], 7, $header[$i], 1, 0, 'C', 1);
            }
            $this->Ln();

            // Restaurar colores y fuentes
            $this->SetFillColor(245, 245, 245);
            $this->SetTextColor(0);
            $this->SetFont('');

            // Datos
            $fill = 0;
            $counter = 1;
            $row_height = 6;
            foreach($data as $row) {
                // Calculate row height
                $row_height = max($this->getStringHeight($w[4], htmlspecialchars($row['career_name'] ?? 'General')), 6);

                $this->MultiCell($w[0], $row_height, $counter++, 'LR', 'C', $fill, 0, '', '', true, 0, false, true, $row_height, 'M');
                $this->MultiCell($w[1], $row_height, htmlspecialchars($row['first_name'] ?? ''), 'LR', 'L', $fill, 0, '', '', true, 0, false, true, $row_height, 'M');
                $this->MultiCell($w[2], $row_height, htmlspecialchars($row['last_name'] ?? ''), 'LR', 'L', $fill, 0, '', '', true, 0, false, true, $row_height, 'M');
                $this->MultiCell($w[3], $row_height, htmlspecialchars($row['email'] ?? ''), 'LR', 'L', $fill, 0, '', '', true, 0, false, true, $row_height, 'M');
                $this->MultiCell($w[4], $row_height, htmlspecialchars($row['career_name'] ?? 'General'), 'LR', 'L', $fill, 0, '', '', true, 0, false, true, $row_height, 'M');
                $this->MultiCell($w[5], $row_height, htmlspecialchars($row['dni'] ?? ''), 'LR', 'L', $fill, 1, '', '', true, 0, false, true, $row_height, 'M');

                $this->Ln();
                $fill=!$fill;
            }
            $this->Cell(array_sum($w), 0, '', 'T');
        }
    }

    $pdf = new MYPDF('L', PDF_UNIT, 'A4', true, 'UTF-8', false);

    $pdf->SetMargins(15, 35, 15);
    $pdf->SetHeaderMargin(10);
    $pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
    $pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);

    $pdf->AddPage();
    $pdf->SetFont('helvetica', '', 8);

    // Build HTML table
    $header = ['N°', 'Nombres', 'Apellidos', 'Email', 'Programa de Estudio', 'DNI'];
    $pdf->drawTable($header, $students);

    $pdf->Output('alumnos.pdf', 'D');
    exit;
}

header('Location: students.php');
exit;
?>
