<?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\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

check_role('administrador');

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

// Base query
$query = "SELECT j.*, c.company_name, car.name as career_name, COUNT(a.id) as app_count 
          FROM jobs j 
          JOIN companies c ON j.company_id = c.id 
          LEFT JOIN careers car ON j.career_id = car.id 
          LEFT JOIN applications a ON j.id = a.job_id";

$params = [];
if ($search) {
    $query .= " WHERE (j.title LIKE ? OR j.description LIKE ? OR c.company_name LIKE ? OR car.name LIKE ? OR j.location LIKE ?)";
    $params = ["%$search%", "%$search%", "%$search%", "%$search%", "%$search%"];
}

$query .= " GROUP BY j.id ORDER BY j.posted_date DESC";

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

if ($format == 'excel') {
    // --- EXCEL EXPORT ---
    $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', 'REPORTE DE OFERTAS LABORALES');
    $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:K1');
    $sheet->mergeCells('B2:K2');
    $sheet->mergeCells('B3:K3');

    // Header row (starting from row 5)
    $headerRow = 5;
    $sheet->setCellValue('A' . $headerRow, 'Nro');
    $sheet->setCellValue('B' . $headerRow, 'Título');
    $sheet->setCellValue('C' . $headerRow, 'Empresa');
    $sheet->setCellValue('D' . $headerRow, 'Programa de Estudio');
    $sheet->setCellValue('E' . $headerRow, 'Lugar');
    $sheet->setCellValue('F' . $headerRow, 'Sueldo');
    $sheet->setCellValue('G' . $headerRow, 'Tipo de Trabajo');
    $sheet->setCellValue('H' . $headerRow, 'Estado');
    $sheet->setCellValue('I' . $headerRow, 'Postulantes');
    $sheet->setCellValue('J' . $headerRow, 'Fecha de Publicación');
    $sheet->setCellValue('K' . $headerRow, 'Fecha de Cierre');

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

    // Data rows (starting from row 6)
    $row = 6;
    $counter = 1;
    foreach ($jobs as $job) {
        $sheet->setCellValue('A' . $row, $counter);
        $sheet->setCellValue('B' . $row, $job['title']);
        $sheet->setCellValue('C' . $row, $job['company_name']);
        $sheet->setCellValue('D' . $row, $job['career_name'] ?? 'General');
        $sheet->setCellValue('E' . $row, $job['location']);
        $sheet->setCellValue('F' . $row, $job['salary'] ? 'S/. ' . number_format($job['salary'], 2) : 'No especificado');
        $sheet->setCellValue('G' . $row, ['full-time' => 'Tiempo Completo', 'part-time' => 'Medio Tiempo', 'contract' => 'Contratado', 'internship' => 'Pasantia'][$job['job_type']] ?? ucfirst($job['job_type']));
        $sheet->setCellValue('H' . $row, ['active' => 'Activo', 'inactive' => 'Inactivo', 'filled' => 'Completado'][$job['status']] ?? ucfirst($job['status']));
        $sheet->setCellValue('I' . $row, $job['app_count']);
        $sheet->setCellValue('J' . $row, $job['posted_date']);
        $sheet->setCellValue('K' . $row, $job['expiration_date'] ?? 'N/A');
        $row++;
        $counter++;
    }

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

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

    $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 Ofertas Laborales', 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 = [10, 50, 40, 40, 30, 25, 30, 25, 25]; // 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[1], htmlspecialchars($row['title'])), $this->getStringHeight($w[3], 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['title']), 'LR', 'L', $fill, 0, '', '', true, 0, false, true, $row_height, 'M');
                $this->MultiCell($w[2], $row_height, htmlspecialchars($row['company_name']), 'LR', 'L', $fill, 0, '', '', true, 0, false, true, $row_height, 'M');
                $this->MultiCell($w[3], $row_height, htmlspecialchars($row['career_name'] ?? 'General'), 'LR', 'L', $fill, 0, '', '', true, 0, false, true, $row_height, 'M');
                $this->MultiCell($w[4], $row_height, htmlspecialchars($row['location']), 'LR', 'L', $fill, 0, '', '', true, 0, false, true, $row_height, 'M');
                $this->MultiCell($w[5], $row_height, ($row['salary'] ? 'S/. ' . number_format($row['salary'], 2) : 'N/A'), 'LR', 'R', $fill, 0, '', '', true, 0, false, true, $row_height, 'M');
                $this->MultiCell($w[6], $row_height, (['full-time' => 'T. Completo', 'part-time' => 'M. Tiempo', 'contract' => 'Contrato', 'internship' => 'Pasantía'][$row['job_type']] ?? ucfirst($row['job_type'])), 'LR', 'C', $fill, 0, '', '', true, 0, false, true, $row_height, 'M');
                $this->MultiCell($w[7], $row_height, (['active' => 'Activo', 'inactive' => 'Inactivo', 'filled' => 'Completado'][$row['status']] ?? ucfirst($row['status'])), 'LR', 'C', $fill, 0, '', '', true, 0, false, true, $row_height, 'M');
                $this->MultiCell($w[8], $row_height, $row['app_count'], 'LR', 'C', $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°', 'Título', 'Empresa', 'Programa', 'Lugar', 'Sueldo', 'Tipo', 'Estado', 'Postulantes'];
    $pdf->drawTable($header, $jobs);

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

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