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

require_once BASE_PATH . '/vendor/autoload.php';

check_role('administrador');

$page_title = 'Cargar Usuarios desde Excel';

// Get user type from GET parameter, default to 'students'
$user_type = sanitize_get('type', 'string', ['max_length' => 20]) ?: 'students';

$success_rows = [];
$error_rows = [];

/**
 * Detects the delimiter of a CSV file.
 * @param string $csv_file Path to the CSV file.
 * @return string The detected delimiter (',' or ';').
 */
function detect_csv_delimiter($csv_file) {
    $delimiters = [",", ";"];
    $handle = fopen($csv_file, 'r');
    $first_line = fgets($handle);
    fclose($handle);
    foreach ($delimiters as $delimiter) {
        if (strpos($first_line, $delimiter) !== false) {
            return $delimiter;
        }
    }
    return ','; // Default to comma
}

/**
 * Process a single user row from CSV or Excel based on user type
 */
function process_user_row($row, $row_number, $pdo, $user_type, &$success_rows, &$error_rows) {
    // Expected format varies by user type
    $username = trim($row[0] ?? '');
    $email = trim($row[1] ?? '');
    $password = trim($row[2] ?? '');
    $first_name = trim($row[3] ?? '');
    $last_name = trim($row[4] ?? '');

    // Common validation
    if (empty($username) || empty($email) || empty($password) || empty($first_name) || empty($last_name)) {
        $error_rows[] = "Fila $row_number: Faltan datos obligatorios (username, email, password, nombres, apellidos).";
        return;
    }

    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        $error_rows[] = "Fila $row_number: El email '$email' no es válido.";
        return;
    }

    // Check if username or email already exists
    $stmt = $pdo->prepare("SELECT id FROM users WHERE username = ? OR email = ?");
    $stmt->execute([$username, $email]);
    if ($stmt->fetch()) {
        $error_rows[] = "Fila $row_number: El usuario '$username' o email '$email' ya existe.";
        return;
    }

    try {
        $pdo->beginTransaction();

        // Get role_id based on user type
        $role_name = match($user_type) {
            'students' => 'alumno',
            'graduates' => 'egresado',
            'teachers' => 'profesor',
            default => 'alumno'
        };

        $role_stmt = $pdo->prepare("SELECT id FROM roles WHERE name = ?");
        $role_stmt->execute([$role_name]);
        $role_id = $role_stmt->fetchColumn();

        if (!$role_id) {
            throw new Exception("Rol '$role_name' no encontrado.");
        }

        // Insert into users table
        $hashed_password = password_hash($password, PASSWORD_DEFAULT);
        $user_stmt = $pdo->prepare("INSERT INTO users (username, email, password, first_name, last_name, role_id, created_at) VALUES (?, ?, ?, ?, ?, ?, NOW())");
        $user_stmt->execute([$username, $email, $hashed_password, $first_name, $last_name, $role_id]);
        $user_id = $pdo->lastInsertId();

        // Insert into specific table based on user type
        switch ($user_type) {
            case 'students':
                $career_name = trim($row[5] ?? '');
                $dni = trim($row[6] ?? '');
                $phone = trim($row[7] ?? '');

                // Get careers to map name to ID
                $careers_stmt = $pdo->query("SELECT name, id FROM careers");
                $careers = $careers_stmt->fetchAll(PDO::FETCH_KEY_PAIR);
                $careers_lower = array_change_key_case($careers, CASE_LOWER);

                $career_id = $careers_lower[strtolower($career_name)] ?? null;
                if (!$career_id) {
                    throw new Exception("La carrera '$career_name' no fue encontrada.");
                }

                $student_stmt = $pdo->prepare("INSERT INTO students (user_id, career_id, dni, phone, created_at) VALUES (?, ?, ?, ?, NOW())");
                $student_stmt->execute([$user_id, $career_id, $dni, $phone]);
                $success_rows[] = "Fila $row_number: Alumno '$first_name $last_name' ($username) importado exitosamente.";
                break;

            case 'graduates':
                $career_name = trim($row[5] ?? '');
                $dni = trim($row[6] ?? '');
                $phone = trim($row[7] ?? '');
                $graduation_year = trim($row[8] ?? '');

                // Get careers to map name to ID
                $careers_stmt = $pdo->query("SELECT name, id FROM careers");
                $careers = $careers_stmt->fetchAll(PDO::FETCH_KEY_PAIR);
                $careers_lower = array_change_key_case($careers, CASE_LOWER);

                $career_id = $careers_lower[strtolower($career_name)] ?? null;
                if (!$career_id) {
                    throw new Exception("La carrera '$career_name' no fue encontrada.");
                }

                $graduate_stmt = $pdo->prepare("INSERT INTO graduates (user_id, career_id, graduation_year, dni, phone, status, created_at) VALUES (?, ?, ?, ?, ?, ?, NOW())");
                $graduate_stmt->execute([$user_id, $career_id, $graduation_year, $dni, $phone, 'active']);
                $success_rows[] = "Fila $row_number: Egresado '$first_name $last_name' ($username) importado exitosamente.";
                break;

            case 'teachers':
                $career_name = trim($row[5] ?? '');
                $department = trim($row[6] ?? '');
                $dni = trim($row[7] ?? '');
                $phone = trim($row[8] ?? '');

                // Get careers to map name to ID
                $careers_stmt = $pdo->query("SELECT name, id FROM careers");
                $careers = $careers_stmt->fetchAll(PDO::FETCH_KEY_PAIR);
                $careers_lower = array_change_key_case($careers, CASE_LOWER);

                $career_id = $careers_lower[strtolower($career_name)] ?? null;

                $teacher_stmt = $pdo->prepare("INSERT INTO teachers (user_id, career_id, department, dni, phone, created_at) VALUES (?, ?, ?, ?, ?, NOW())");
                $teacher_stmt->execute([$user_id, $career_id, $department, $dni, $phone]);
                $success_rows[] = "Fila $row_number: Profesor '$first_name $last_name' ($username) importado exitosamente.";
                break;
        }

        $pdo->commit();

    } catch (Exception $e) {
        $pdo->rollBack();
        $error_rows[] = "Fila $row_number: Error en la base de datos al importar a '$username'. " . $e->getMessage();
    }
}

if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_FILES['student_file'])) {
    if ($_FILES['student_file']['error'] == UPLOAD_ERR_OK) {
        $file_tmp_path = $_FILES['student_file']['tmp_name'];
        $file_name = $_FILES['student_file']['name'];
        $file_size = $_FILES['student_file']['size'];
        $file_type = $_FILES['student_file']['type'];
        $file_extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));

        $allowed_extensions = ['csv', 'xlsx', 'xls'];
        if (in_array($file_extension, $allowed_extensions)) {

            // Get careers only if needed for the user type
            $careers_lower = [];
            if (in_array($user_type, ['students', 'graduates', 'teachers'])) {
                $careers_stmt = $pdo->query("SELECT name, id FROM careers");
                $careers = $careers_stmt->fetchAll(PDO::FETCH_KEY_PAIR);
                $careers_lower = array_change_key_case($careers, CASE_LOWER);
            }

            if ($file_extension === 'csv') {
                // Process CSV file
                // Detect delimiter
                $delimiter = detect_csv_delimiter($file_tmp_path);

                $file_handle = fopen($file_tmp_path, 'r');

                // Skip header row
                fgetcsv($file_handle, 1000, $delimiter, '"', '\\');

                $row_number = 1;
                while (($row = fgetcsv($file_handle, 1000, $delimiter, '"', '\\')) !== FALSE) {
                    $row_number++;
                    process_user_row($row, $row_number, $pdo, $user_type, $success_rows, $error_rows);
                }

                fclose($file_handle);
            } elseif (in_array($file_extension, ['xlsx', 'xls'])) {
                // Process Excel file
                // Check if PhpSpreadsheet is available
                if (!class_exists('PhpOffice\PhpSpreadsheet\IOFactory')) {
                    $error_rows[] = 'Error: La biblioteca PhpSpreadsheet no está instalada. Para archivos Excel, instale PhpSpreadsheet con: composer require phpoffice/phpspreadsheet';
                } else {
                    try {
                        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file_tmp_path);
                        $worksheet = $spreadsheet->getActiveSheet();
                        $rows = $worksheet->toArray();

                        // Skip header row
                        array_shift($rows);

                        $row_number = 1;
                        foreach ($rows as $row) {
                            $row_number++;
                            process_user_row($row, $row_number, $pdo, $user_type, $success_rows, $error_rows);
                        }
                    } catch (Exception $e) {
                        $error_rows[] = 'Error al procesar el archivo Excel: ' . $e->getMessage();
                    }
                }
            }

        } else {
            $error_rows[] = 'Error: Por favor, suba un archivo CSV o Excel válido (.csv, .xlsx, .xls).';
        }
    } else {
        $error_rows[] = 'Error al subir el archivo. Código: ' . $_FILES['student_file']['error'];
    }
}

$content = '
<div class="row">
    <div class="col-md-8">
        <div class="card card-primary">
            <div class="card-header">
                <h3 class="card-title">Cargar Usuarios desde Archivo Excel</h3>
                <div class="card-tools">
                    <select name="user_type" id="user_type" class="form-select form-select-sm" onchange="updateForm(this.value)">
                        <option value="students" ' . ($user_type == 'students' ? 'selected' : '') . '>Alumnos</option>
                        <option value="graduates" ' . ($user_type == 'graduates' ? 'selected' : '') . '>Egresados</option>
                        <option value="teachers" ' . ($user_type == 'teachers' ? 'selected' : '') . '>Profesores</option>
                    </select>
                </div>
            </div>
            <form method="post" enctype="multipart/form-data">
                <div class="card-body">
                    <div class="form-group">
                        <label for="student_file">Archivo EXCEL o CSV </label>
                        <div class="input-group">
                            <div class="custom-file">
                                <input type="file" class="custom-file-input" id="student_file" name="student_file" accept=".csv,.xlsx,.xls" required>
                                <label class="custom-file-label" for="student_file">Elegir archivo</label>
                            </div>
                        </div>
                        <small class="form-text text-muted">
                            El archivo debe ser un CSV o Excel con las columnas en el siguiente orden:
                            <code>username</code>, <code>email</code>, <code>password</code>, <code>first_name</code>, <code>last_name</code>, <code>career_name</code>, <code>dni</code>, <code>phone</code>.
                            La primera fila debe ser la cabecera y será ignorada.
                        </small>
                    </div>
                </div>
                <div class="card-footer">
                    <button type="submit" class="btn btn-primary"><i class="fas fa-upload"></i> Cargar Usuarios</button>
                    <a href="download-template.php?type=' . $user_type . '" class="btn btn-info"><i class="fas fa-download"></i> Descargar Plantilla</a>
                    <a href="' . ($user_type == 'students' ? 'students.php' : ($user_type == 'graduates' ? 'graduates.php' : 'teachers.php')) . '" class="btn btn-secondary">Volver</a>
                </div>
            </form>
        </div>
    </div>
    <div class="col-md-4">
        <div class="card">
            <div class="card-header">
                <h3 class="card-title">Instrucciones</h3>
            </div>
            <div class="card-body">
                <div id="instructions-students" class="user-instructions" style="display: ' . ($user_type == 'students' ? 'block' : 'none') . ';">
                    <p>Para cargar alumnos de forma masiva, siga estos pasos:</p>
                    <ol>
                        <li>Cree una hoja de cálculo en Excel o Google Sheets.</li>
                        <li>Asegúrese de que las columnas estén en el orden correcto y tengan los siguientes encabezados:
                            <br><code>username, email, password, first_name, last_name, career_name, dni, phone</code>
                        </li>
                        <li>El campo <code>career_name</code> debe coincidir <strong>exactamente</strong> con el nombre de un programa de estudio existente en el sistema.</li>
                        <li>Guarde el archivo en formato <strong>Excel (.xlsx)</strong>.</li>
                        <li>Seleccione el archivo usando el formulario y haga clic en "Cargar Usuarios".</li>
                    </ol>
                </div>

                <div id="instructions-graduates" class="user-instructions" style="display: ' . ($user_type == 'graduates' ? 'block' : 'none') . ';">
                    <p>Para cargar egresados de forma masiva, siga estos pasos:</p>
                    <ol>
                        <li>Cree una hoja de cálculo en Excel o Google Sheets.</li>
                        <li>Asegúrese de que las columnas estén en el orden correcto y tengan los siguientes encabezados:
                            <br><code>username, email, password, first_name, last_name, career_name, dni, phone, graduation_year</code>
                        </li>
                        <li>El campo <code>career_name</code> debe coincidir <strong>exactamente</strong> con el nombre de un programa de estudio existente en el sistema.</li>
                        <li>Guarde el archivo en formato <strong>Excel (.xlsx)</strong>.</li>
                        <li>Seleccione el archivo usando el formulario y haga clic en "Cargar Usuarios".</li>
                    </ol>
                </div>

                <div id="instructions-teachers" class="user-instructions" style="display: ' . ($user_type == 'teachers' ? 'block' : 'none') . ';">
                    <p>Para cargar profesores de forma masiva, siga estos pasos:</p>
                    <ol>
                        <li>Cree una hoja de cálculo en Excel o Google Sheets.</li>
                        <li>Asegúrese de que las columnas estén en el orden correcto y tengan los siguientes encabezados:
                            <br><code>username, email, password, first_name, last_name, career_name, department, dni, phone</code>
                        </li>
                        <li>El campo <code>career_name</code> es opcional para profesores.</li>
                        <li>Guarde el archivo en formato <strong>Excel (.xlsx)</strong>.</li>
                        <li>Seleccione el archivo usando el formulario y haga clic en "Cargar Usuarios".</li>
                    </ol>
                </div>

                <p><strong>Nota:</strong> Asegúrese de que la biblioteca PhpSpreadsheet esté instalada en el servidor.</p>
            </div>
        </div>
    </div>
</div>';

if (!empty($success_rows) || !empty($error_rows)) {
    $total_processed = count($success_rows) + count($error_rows);
    $success_percentage = $total_processed > 0 ? round((count($success_rows) / $total_processed) * 100, 1) : 0;

    $content .= '
    <div class="row mt-4">
        <div class="col-12">
            <div class="card">
                <div class="card-header">
                    <h3 class="card-title">Resultados de la Importación</h3>
                    <div class="card-tools">
                        <span class="badge badge-info text-black">Total procesado: ' . $total_processed . '</span>
                        <span class="badge badge-success">Éxito: ' . $success_percentage . '%</span>
                    </div>
                </div>
                <div class="card-body">
                    <div class="row mb-3">
                        <div class="col-md-4">
                            <div class="info-box bg-light">
                                <div class="info-box-content">
                                    <span class="info-box-text">Total de filas</span>
                                    <span class="info-box-number">' . $total_processed . '</span>
                                </div>
                            </div>
                        </div>
                        <div class="col-md-4">
                            <div class="info-box bg-success">
                                <div class="info-box-content">
                                    <span class="info-box-text text-white">Importados</span>
                                    <span class="info-box-number text-white">' . count($success_rows) . '</span>
                                </div>
                            </div>
                        </div>
                        <div class="col-md-4">
                            <div class="info-box bg-danger">
                                <div class="info-box-content">
                                    <span class="info-box-text text-white">Errores</span>
                                    <span class="info-box-number text-white">' . count($error_rows) . '</span>
                                </div>
                            </div>
                        </div>
                    </div>
    ';

    if (!empty($success_rows)) {
        $content .= '<h5><i class="fas fa-check-circle text-success"></i> Alumnos Importados Correctamente (' . count($success_rows) . ')</h5>';
        $content .= '<div class="alert alert-success" style="max-height: 200px; overflow-y: auto;"><ul>';
        foreach ($success_rows as $msg) {
            $content .= '<li>' . htmlspecialchars($msg) . '</li>';
        }
        $content .= '</ul></div>';
    }

    if (!empty($error_rows)) {
        $content .= '<h5 class="mt-3"><i class="fas fa-exclamation-triangle text-danger"></i> Errores Encontrados (' . count($error_rows) . ')</h5>';
        $content .= '<div class="alert alert-danger" style="max-height: 200px; overflow-y: auto;"><ul>';
        foreach ($error_rows as $msg) {
            $content .= '<li>' . htmlspecialchars($msg) . '</li>';
        }
        $content .= '</ul></div>';
    }

    $content .= '
                </div>
            </div>
        </div>
    </div>';
}

include BASE_PATH . '/includes/layout.php';
?>
<script>
// Script para mostrar el nombre del archivo en el input de tipo file
document.addEventListener('DOMContentLoaded', function () {
    var fileInput = document.querySelector('.custom-file-input');
    if (fileInput) {
        fileInput.addEventListener('change', function(e){
            var fileName = e.target.files[0].name;
            var nextSibling = e.target.nextElementSibling;
            nextSibling.innerText = fileName;
        });
    }
});

// Function to update form when user type changes
function updateForm(userType) {
    // Update URL with new type parameter
    const url = new URL(window.location);
    url.searchParams.set('type', userType);
    window.location.href = url.toString();
}
</script>