-- =========================================================
-- UNIEDU v1 - Esquema de Base de Datos
-- Base de datos destino en cPanel: alfredoc_GIACOMO
-- Usuario destino: alfredoc_giacomo
-- Importar este archivo desde phpMyAdmin (pestaña "Importar")
-- =========================================================

SET NAMES utf8mb4;
SET time_zone = '-05:00';

-- ---------------------------------------------------------
-- Tabla: usuarios (profesores y estudiantes)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS usuarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tipo ENUM('profesor','estudiante') NOT NULL,
    usuario VARCHAR(60) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    dni VARCHAR(20) NOT NULL,
    nombres VARCHAR(120) NOT NULL,
    apellidos VARCHAR(120) NOT NULL,
    email VARCHAR(150) NULL,
    especialidad VARCHAR(150) NULL,
    estado ENUM('activo','inactivo') NOT NULL DEFAULT 'activo',
    es_master TINYINT(1) NOT NULL DEFAULT 0,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_dni (dni)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- Tabla: cursos
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS cursos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(150) NOT NULL,
    seccion VARCHAR(50) NULL,
    ciclo VARCHAR(50) NULL,
    profesor_id INT NOT NULL,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (profesor_id) REFERENCES usuarios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- Tabla: matriculas (relación estudiante-curso)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS matriculas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    estudiante_id INT NOT NULL,
    curso_id INT NOT NULL,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_matricula (estudiante_id, curso_id),
    FOREIGN KEY (estudiante_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (curso_id) REFERENCES cursos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- Tabla: notas (4 notas + promedio por matrícula)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS notas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    matricula_id INT NOT NULL UNIQUE,
    nota1 DECIMAL(4,2) NULL,
    nota2 DECIMAL(4,2) NULL,
    nota3 DECIMAL(4,2) NULL,
    nota4 DECIMAL(4,2) NULL,
    promedio DECIMAL(4,2) NULL,
    actualizado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (matricula_id) REFERENCES matriculas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- Tabla: sesiones_asistencia (una sesión = un QR)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS sesiones_asistencia (
    id INT AUTO_INCREMENT PRIMARY KEY,
    curso_id INT NOT NULL,
    profesor_id INT NOT NULL,
    titulo VARCHAR(150) NULL,
    fecha DATE NOT NULL,
    token VARCHAR(64) NOT NULL UNIQUE,
    activa TINYINT(1) NOT NULL DEFAULT 1,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (curso_id) REFERENCES cursos(id) ON DELETE CASCADE,
    FOREIGN KEY (profesor_id) REFERENCES usuarios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- Tabla: sesion_estudiantes (lista esperada, cargada por Excel)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS sesion_estudiantes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sesion_id INT NOT NULL,
    dni VARCHAR(20) NOT NULL,
    nombres VARCHAR(120) NOT NULL,
    apellidos VARCHAR(120) NOT NULL,
    FOREIGN KEY (sesion_id) REFERENCES sesiones_asistencia(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- Tabla: registros_asistencia (marcado real al escanear QR)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS registros_asistencia (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sesion_id INT NOT NULL,
    dni VARCHAR(20) NOT NULL,
    nombres VARCHAR(120) NOT NULL,
    apellidos VARCHAR(120) NOT NULL,
    fecha_hora TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_sesion_dni (sesion_id, dni),
    FOREIGN KEY (sesion_id) REFERENCES sesiones_asistencia(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- Tabla: examenes
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS examenes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    profesor_id INT NOT NULL,
    curso_id INT NULL,
    titulo VARCHAR(150) NOT NULL,
    archivo_enc VARCHAR(255) NOT NULL,
    clave VARCHAR(255) NOT NULL,
    duracion_min INT NOT NULL DEFAULT 30,
    intentos_max INT NOT NULL DEFAULT 3,
    estado ENUM('borrador','publicado','cerrado') NOT NULL DEFAULT 'borrador',
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (profesor_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (curso_id) REFERENCES cursos(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- Tabla: examen_preguntas (parseadas del formato Aiken)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS examen_preguntas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    examen_id INT NOT NULL,
    orden INT NOT NULL,
    enunciado TEXT NOT NULL,
    opcion_a VARCHAR(500) NULL,
    opcion_b VARCHAR(500) NULL,
    opcion_c VARCHAR(500) NULL,
    opcion_d VARCHAR(500) NULL,
    opcion_e VARCHAR(500) NULL,
    correcta CHAR(1) NOT NULL,
    puntaje DECIMAL(5,2) NOT NULL DEFAULT 1.00,
    FOREIGN KEY (examen_id) REFERENCES examenes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- Tabla: examen_intentos (control de 3 oportunidades / bloqueo)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS examen_intentos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    examen_id INT NOT NULL,
    estudiante_id INT NOT NULL,
    intentos_restantes INT NOT NULL DEFAULT 3,
    bloqueado TINYINT(1) NOT NULL DEFAULT 0,
    finalizado TINYINT(1) NOT NULL DEFAULT 0,
    iniciado_en TIMESTAMP NULL,
    finalizado_en TIMESTAMP NULL,
    nota DECIMAL(5,2) NULL,
    respuestas TEXT NULL,
    UNIQUE KEY uq_intento (examen_id, estudiante_id),
    FOREIGN KEY (examen_id) REFERENCES examenes(id) ON DELETE CASCADE,
    FOREIGN KEY (estudiante_id) REFERENCES usuarios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- Tabla: examen_eventos (bitácora antitrampa)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS examen_eventos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    intento_id INT NOT NULL,
    tipo ENUM('inicio','cambio_pestana','bloqueo','desbloqueo','fin') NOT NULL,
    detalle VARCHAR(255) NULL,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (intento_id) REFERENCES examen_intentos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================================
-- DATOS DE MUESTRA (login de demostración)
-- Profesor MASTER: GIACOMO MOTTA
--   usuario:  admin1234
--   password: 12345678
-- Estudiante demo:
--   usuario (DNI): 75634521
--   password: Estudiante#2026
-- Las contraseñas se guardan con password_hash (bcrypt) desde PHP,
-- por eso el INSERT real de estos dos usuarios se ejecuta con
-- seed.php (incluido) en vez de en este .sql, para no pegar un
-- hash desactualizado. Ejecuta seed.php UNA sola vez tras importar
-- este schema. El curso de muestra y los dos usuarios de muestra
-- se crean todos dentro de seed.php.
-- =========================================================