<?php
/**
 * Script de Reparación Integral de Base de Datos - Bolsa de Trabajo
 * Este script verifica y crea TODAS las tablas necesarias si faltan.
 */
require_once 'includes/db.php';

error_reporting(E_ALL);
ini_set('display_errors', 1);

echo "<html><head><title>Reparación Integral de Base de Datos</title>";
echo "<style>body{font-family:sans-serif;line-height:1.6;padding:20px;max-width:900px;margin:0 auto;background:#f4f7f6;}
      h1{color:#27538C;border-bottom:2px solid #27538C;padding-bottom:10px;}
      .status{padding:10px;margin:5px 0;border-radius:4px;}
      .success{background:#d4edda;color:#155724;border:1px solid #c3e6cb;}
      .error{background:#f8d7da;color:#721c24;border:1px solid #f5c6cb;}
      .info{background:#d1ecf1;color:#0c5460;border:1px solid #bee5eb;}
      pre{background:#eee;padding:10px;overflow:auto;}
      </style></head><body>";

echo "<h1>🛠 Reparación Integral de Base de Datos</h1>";

function executeSQL($pdo, $tableName, $query) {
    try {
        $stmt = $pdo->query("SHOW TABLES LIKE '$tableName'");
        if ($stmt->rowCount() == 0) {
            echo "<div class='info'>Creando tabla <strong>$tableName</strong>...</div>";
            $pdo->exec($query);
            echo "<div class='success'>✅ Tabla <strong>$tableName</strong> creada.</div>";
        } else {
            echo "<div class='success'>✔ Tabla <strong>$tableName</strong> ya existe.</div>";
        }
    } catch (PDOException $e) {
        echo "<div class='error'>❌ Error en $tableName: " . $e->getMessage() . "</div>";
    }
}

// 1. FUNDAMENTALES
executeSQL($pdo, 'careers', "CREATE TABLE careers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE)");
executeSQL($pdo, 'roles', "CREATE TABLE roles (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE)");
executeSQL($pdo, 'users', "CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    username VARCHAR(50) NOT NULL UNIQUE, 
    password VARCHAR(255) NOT NULL, 
    email VARCHAR(100) NOT NULL UNIQUE, 
    role_id INT NOT NULL, 
    first_name VARCHAR(50), 
    last_name VARCHAR(50), 
    photo VARCHAR(255), 
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    FOREIGN KEY (role_id) REFERENCES roles(id)
)");

// 2. PERFILES Y ENTIDADES
executeSQL($pdo, 'companies', "CREATE TABLE companies (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    user_id INT NOT NULL UNIQUE, 
    company_name VARCHAR(100) NOT NULL, 
    description TEXT, 
    website VARCHAR(255), 
    address VARCHAR(255), 
    phone VARCHAR(20), 
    logo VARCHAR(255), 
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)");

executeSQL($pdo, 'students', "CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    user_id INT NOT NULL UNIQUE, 
    career_id INT, 
    dni VARCHAR(20), 
    birth_date DATE, 
    address TEXT, 
    phone VARCHAR(20), 
    graduation_year YEAR, 
    resume TEXT, 
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, 
    FOREIGN KEY (career_id) REFERENCES careers(id)
)");

executeSQL($pdo, 'teachers', "CREATE TABLE teachers (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    user_id INT NOT NULL UNIQUE, 
    career_id INT, 
    bio TEXT, 
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, 
    FOREIGN KEY (career_id) REFERENCES careers(id)
)");

executeSQL($pdo, 'graduates', "CREATE TABLE graduates (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    user_id INT NOT NULL UNIQUE, 
    career_id INT, 
    dni VARCHAR(20), 
    birth_date DATE, 
    address TEXT, 
    phone VARCHAR(20), 
    graduation_year YEAR, 
    status ENUM('active', 'inactive') DEFAULT 'active', 
    resume TEXT, 
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, 
    FOREIGN KEY (career_id) REFERENCES careers(id)
)");

// 3. OPERATIVAS
executeSQL($pdo, 'jobs', "CREATE TABLE jobs (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    company_id INT NOT NULL, 
    career_id INT, 
    title VARCHAR(255) NOT NULL, 
    description TEXT NOT NULL, 
    requirements TEXT, 
    salary DECIMAL(10,2), 
    location VARCHAR(100), 
    job_type ENUM('full-time', 'part-time', 'contract', 'internship') DEFAULT 'full-time', 
    status ENUM('active', 'inactive', 'filled') DEFAULT 'active', 
    posted_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    expiration_date DATE, 
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, 
    FOREIGN KEY (career_id) REFERENCES careers(id)
)");

executeSQL($pdo, 'applications', "CREATE TABLE applications (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    job_id INT NOT NULL, 
    student_id INT NOT NULL, 
    status ENUM('pending', 'reviewed', 'accepted', 'rejected') DEFAULT 'pending', 
    applied_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    notes TEXT, 
    FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE, 
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE, 
    UNIQUE KEY unique_application (job_id, student_id)
)");

executeSQL($pdo, 'notifications', "CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    user_id INT NOT NULL, 
    type ENUM('info', 'success', 'warning', 'danger') DEFAULT 'info', 
    title VARCHAR(255), 
    message TEXT NOT NULL, 
    is_read BOOLEAN DEFAULT FALSE, 
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)");

executeSQL($pdo, 'institution_settings', "CREATE TABLE institution_settings (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    institution_name VARCHAR(255) NOT NULL DEFAULT 'Bolsa de Trabajo', 
    institution_logo VARCHAR(255), 
    institution_description TEXT, 
    primary_color VARCHAR(7) DEFAULT '#27538C', 
    secondary_color VARCHAR(7) DEFAULT '#204473', 
    accent_color VARCHAR(7) DEFAULT '#9BABBF', 
    success_color VARCHAR(7) DEFAULT '#28A745', 
    warning_color VARCHAR(7) DEFAULT '#FFC107', 
    danger_color VARCHAR(7) DEFAULT '#DC3545', 
    info_color VARCHAR(7) DEFAULT '#17A2B8', 
    light_bg_color VARCHAR(7) DEFAULT '#F2F2F2', 
    dark_text_color VARCHAR(7) DEFAULT '#212426', 
    favicon VARCHAR(255), 
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)");

// Subtablas de detalles
executeSQL($pdo, 'student_languages', "CREATE TABLE student_languages (id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NOT NULL, language_name VARCHAR(100) NOT NULL, proficiency_level VARCHAR(50), FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE)");
executeSQL($pdo, 'student_trainings', "CREATE TABLE student_trainings (id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NOT NULL, training_name VARCHAR(255) NOT NULL, duration_hours INT, FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE)");
executeSQL($pdo, 'student_work_experience', "CREATE TABLE student_work_experience (id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NOT NULL, company_name VARCHAR(255) NOT NULL, job_title VARCHAR(255) NOT NULL, start_date DATE, end_date DATE, FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE)");

// 4. DATOS MÍNIMOS
$pdo->exec("INSERT IGNORE INTO roles (id, name) VALUES (1, 'administrador'), (2, 'profesor'), (3, 'alumno'), (4, 'empresa'), (5, 'egresado')");
$pdo->exec("INSERT IGNORE INTO institution_settings (id, institution_name) VALUES (1, 'Bolsa de Trabajo')");
$pdo->exec("INSERT IGNORE INTO careers (name) VALUES ('Enfermería Técnica'), ('Computación'), ('Contabilidad'), ('Construcción Civil'), ('Electrónica Industrial'), ('Mecánica Production')");

echo "<h2>✅ Estructura Completa Verificada.</h2>";
echo "<p><a href='admin/dashboard.php' style='padding:10px 20px;background:#27538C;color:white;text-decoration:none;border-radius:4px;'>Volver al Dashboard</a></p>";
?>
