<?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';

check_role('administrador');

$format = $_POST['format'] ?? 'excel';
$start_date = !empty($_POST['start_date']) ? $_POST['start_date'] : null;
$end_date = !empty($_POST['end_date']) ? $_POST['end_date'] : null;
$user_type = $_POST['user_type'] ?? 'both';
$gender = !empty($_POST['gender']) ? $_POST['gender'] : null;
$careers = isset($_POST['careers']) && is_array($_POST['careers']) ? $_POST['careers'] : [];

$params = [];
$where_clauses = [];

// Date filter
if ($start_date) {
    $where_clauses[] = "u.created_at >= ?";
    $params[] = $start_date . ' 00:00:00';
}
if ($end_date) {
    $where_clauses[] = "u.created_at <= ?";
    $params[] = $end_date . ' 23:59:59';
}

// Gender filter - only apply if gender is specified
if ($gender) {
    // For student query
    $student_where_clauses = $where_clauses;
    $student_where_clauses[] = "s.gender = ?";
    $student_params = array_merge($params, [$gender]);

    // For graduate query
    $graduate_where_clauses = $where_clauses;
    $graduate_where_clauses[] = "g.gender = ?";
    $graduate_params = array_merge($params, [$gender]);
} else {
    $student_where_clauses = $where_clauses;
    $graduate_where_clauses = $where_clauses;
    $student_params = $params;
    $graduate_params = $params;
}

// Career filter
if (!empty($careers)) {
    $placeholders = str_repeat('?,', count($careers) - 1) . '?';
    $where_clauses[] = "c.id IN ($placeholders)";
    $params = array_merge($params, $careers);
}

// Initialize query-specific variables
$student_where_clauses = $where_clauses;
$graduate_where_clauses = $where_clauses;
$student_params = $params;
$graduate_params = $params;

// Gender filter - only apply if gender is specified
if ($gender) {
    // For student query
    $student_where_clauses[] = "s.gender = ?";
    $student_params = array_merge($student_params, [$gender]);

    // For graduate query
    $graduate_where_clauses[] = "g.gender = ?";
    $graduate_params = array_merge($graduate_params, [$gender]);
}

$where_sql = '';
if (!empty($where_clauses)) {
    $where_sql = "WHERE " . implode(' AND ', $where_clauses);
}

$student_where_sql = '';
if (!empty($student_where_clauses)) {
    $student_where_sql = "WHERE " . implode(' AND ', $student_where_clauses);
}

$graduate_where_sql = '';
if (!empty($graduate_where_clauses)) {
    $graduate_where_sql = "WHERE " . implode(' AND ', $graduate_where_clauses);
}

$student_query = "
    SELECT u.first_name, u.last_name, u.email, s.dni, c.name as career_name, 'Alumno' as user_type, s.gender, u.created_at
    FROM users u
    JOIN students s ON u.id = s.user_id
    JOIN careers c ON s.career_id = c.id
    " . $student_where_sql;

$graduate_query = "
    SELECT u.first_name, u.last_name, u.email, g.dni, c.name as career_name, 'Egresado' as user_type, g.gender, u.created_at
    FROM users u
    JOIN graduates g ON u.id = g.user_id
    JOIN careers c ON g.career_id = c.id
    " . $graduate_where_sql;

$final_query = "";
$final_params = [];

if ($user_type == 'student') {
    $final_query = $student_query;
    $final_params = $student_params;
} elseif ($user_type == 'graduate') {
    $final_query = $graduate_query;
    $final_params = $graduate_params;
} else { // both
    $final_query = "$student_query UNION ALL $graduate_query";
    // For UNION, parameters need to be combined from both queries
    $final_params = array_merge($student_params, $graduate_params);
}

$final_query .= " ORDER BY created_at DESC";

$stmt = $pdo->prepare($final_query);
$stmt->execute($final_params);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

if ($format == 'excel') {
    // --- EXCEL (CSV) EXPORT ---
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=reporte_personalizado.csv');

    $output = fopen('php://output', 'w');
    fwrite($output, "\xEF\xBB\xBF"); // BOM for UTF-8

    fputcsv($output, [
        'Nro', 'Nombres', 'Apellidos', 'Email', 'DNI', 'Programa de Estudio', 'Tipo de Usuario', 'Género', 'Fecha de Registro'
    ], ';', '"', '\\');

    $counter = 1;
    foreach ($results as $row) {
        fputcsv($output, [
            $counter++,
            $row['first_name'],
            $row['last_name'],
            $row['email'],
            $row['dni'] ?? 'N/A',
            $row['career_name'],
            $row['user_type'],
            $row['gender'] ?? 'No especificado',
            date('d/m/Y', strtotime($row['created_at']))
        ], ';', '"', '\\');
    }

    fclose($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 Personalizado', 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', 9);
            $w = [15, 65, 55, 30, 45, 25, 20, 20]; // 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'])), 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'] . ' ' . $row['last_name']), 'LR', 'L', $fill, 0, '', '', true, 0, false, true, $row_height, 'M');
                $this->MultiCell($w[2], $row_height, htmlspecialchars($row['email']), 'LR', 'L', $fill, 0, '', '', true, 0, false, true, $row_height, 'M');
                $this->MultiCell($w[3], $row_height, htmlspecialchars($row['dni'] ?? 'N/A'), 'LR', 'L', $fill, 0, '', '', true, 0, false, true, $row_height, 'M');
                $this->MultiCell($w[4], $row_height, htmlspecialchars($row['career_name']), 'LR', 'L', $fill, 0, '', '', true, 0, false, true, $row_height, 'M');
                $this->MultiCell($w[5], $row_height, htmlspecialchars($row['user_type']), 'LR', 'C', $fill, 0, '', '', true, 0, false, true, $row_height, 'M');
                $this->MultiCell($w[6], $row_height, htmlspecialchars($row['gender'] ?? 'No especificado'), 'LR', 'C', $fill, 0, '', '', true, 0, false, true, $row_height, 'M');
                $this->MultiCell($w[7], $row_height, date('d/m/Y', strtotime($row['created_at'])), '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);

    $header = ['N°', 'Nombres y Apellidos', 'Email', 'DNI', 'Programa', 'Tipo', 'Género', 'Registro'];
    $pdf->drawTable($header, $results);

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

header('Location: custom-reports.php');
exit;
?>