-- Base de Datos: Lourdes Evalúa
-- Creación de tablas según expediente técnico


-- 1. Tabla: usuarios
CREATE TABLE IF NOT EXISTS usuarios (
    id_usuario INT AUTO_INCREMENT PRIMARY KEY,
    nombres VARCHAR(100) NOT NULL,
    apellidos VARCHAR(100) NOT NULL,
    usuario VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    rol ENUM('administrador', 'directivo', 'docente', 'estudiante') NOT NULL,
    estado TINYINT DEFAULT 1 COMMENT '1: Activo, 0: Inactivo',
    fecha_registro DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 2. Tabla: grados
CREATE TABLE IF NOT EXISTS grados (
    id_grado INT AUTO_INCREMENT PRIMARY KEY,
    nombre_grado VARCHAR(50) NOT NULL,
    nivel VARCHAR(50) DEFAULT 'Primaria',
    estado TINYINT DEFAULT 1
);

-- 3. Tabla: secciones
CREATE TABLE IF NOT EXISTS secciones (
    id_seccion INT AUTO_INCREMENT PRIMARY KEY,
    id_grado INT NOT NULL,
    nombre_seccion VARCHAR(10) NOT NULL,
    estado TINYINT DEFAULT 1,
    FOREIGN KEY (id_grado) REFERENCES grados(id_grado) ON DELETE CASCADE
);

-- 4. Tabla: estudiantes
CREATE TABLE IF NOT EXISTS estudiantes (
    id_estudiante INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT NOT NULL,
    dni VARCHAR(15) NOT NULL UNIQUE,
    nombres VARCHAR(100) NOT NULL,
    apellidos VARCHAR(100) NOT NULL,
    id_grado INT NOT NULL,
    id_seccion INT NOT NULL,
    estado TINYINT DEFAULT 1,
    FOREIGN KEY (id_usuario) REFERENCES usuarios(id_usuario) ON DELETE CASCADE,
    FOREIGN KEY (id_grado) REFERENCES grados(id_grado),
    FOREIGN KEY (id_seccion) REFERENCES secciones(id_seccion)
);

-- 5. Tabla: docentes
CREATE TABLE IF NOT EXISTS docentes (
    id_docente INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT NOT NULL,
    dni VARCHAR(15) NOT NULL UNIQUE,
    nombres VARCHAR(100) NOT NULL,
    apellidos VARCHAR(100) NOT NULL,
    especialidad VARCHAR(100),
    estado TINYINT DEFAULT 1,
    FOREIGN KEY (id_usuario) REFERENCES usuarios(id_usuario) ON DELETE CASCADE
);

-- 6. Tabla: areas
CREATE TABLE IF NOT EXISTS areas (
    id_area INT AUTO_INCREMENT PRIMARY KEY,
    nombre_area VARCHAR(100) NOT NULL,
    estado TINYINT DEFAULT 1
);

-- 7. Tabla: competencias
CREATE TABLE IF NOT EXISTS competencias (
    id_competencia INT AUTO_INCREMENT PRIMARY KEY,
    id_area INT NOT NULL,
    nombre_competencia TEXT NOT NULL,
    estado TINYINT DEFAULT 1,
    FOREIGN KEY (id_area) REFERENCES areas(id_area) ON DELETE CASCADE
);

-- 8. Tabla: criterios
CREATE TABLE IF NOT EXISTS criterios (
    id_criterio INT AUTO_INCREMENT PRIMARY KEY,
    id_competencia INT NOT NULL,
    descripcion TEXT NOT NULL,
    grado VARCHAR(50),
    estado TINYINT DEFAULT 1,
    FOREIGN KEY (id_competencia) REFERENCES competencias(id_competencia) ON DELETE CASCADE
);

-- 9. Tabla: evaluaciones
CREATE TABLE IF NOT EXISTS evaluaciones (
    id_evaluacion INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(200) NOT NULL,
    id_area INT NOT NULL,
    id_docente INT NOT NULL,
    id_grado INT NOT NULL,
    id_seccion INT NOT NULL,
    fecha_inicio DATETIME NOT NULL,
    fecha_fin DATETIME NOT NULL,
    duracion_minutos INT DEFAULT 60,
    puntaje_total DECIMAL(5,2) DEFAULT 0,
    estado ENUM('borrador', 'activa', 'cerrada') DEFAULT 'borrador',
    FOREIGN KEY (id_area) REFERENCES areas(id_area),
    FOREIGN KEY (id_docente) REFERENCES docentes(id_docente),
    FOREIGN KEY (id_grado) REFERENCES grados(id_grado),
    FOREIGN KEY (id_seccion) REFERENCES secciones(id_seccion)
);

-- 10. Tabla: preguntas
CREATE TABLE IF NOT EXISTS preguntas (
    id_pregunta INT AUTO_INCREMENT PRIMARY KEY,
    id_area INT NOT NULL,
    id_competencia INT,
    id_criterio INT,
    enunciado TEXT NOT NULL,
    tipo_pregunta VARCHAR(50) DEFAULT 'opcion_multiple',
    dificultad VARCHAR(20) DEFAULT 'media',
    puntaje DECIMAL(5,2) NOT NULL,
    imagen VARCHAR(255) NULL,
    estado TINYINT DEFAULT 1,
    FOREIGN KEY (id_area) REFERENCES areas(id_area),
    FOREIGN KEY (id_competencia) REFERENCES competencias(id_competencia),
    FOREIGN KEY (id_criterio) REFERENCES criterios(id_criterio)
);

-- 11. Tabla: alternativas
CREATE TABLE IF NOT EXISTS alternativas (
    id_alternativa INT AUTO_INCREMENT PRIMARY KEY,
    id_pregunta INT NOT NULL,
    texto_alternativa TEXT NOT NULL,
    es_correcta TINYINT NOT NULL DEFAULT 0,
    FOREIGN KEY (id_pregunta) REFERENCES preguntas(id_pregunta) ON DELETE CASCADE
);

-- 12. Tabla: evaluacion_preguntas
CREATE TABLE IF NOT EXISTS evaluacion_preguntas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_evaluacion INT NOT NULL,
    id_pregunta INT NOT NULL,
    orden INT DEFAULT 1,
    FOREIGN KEY (id_evaluacion) REFERENCES evaluaciones(id_evaluacion) ON DELETE CASCADE,
    FOREIGN KEY (id_pregunta) REFERENCES preguntas(id_pregunta) ON DELETE CASCADE
);

-- 13. Tabla: intentos
CREATE TABLE IF NOT EXISTS intentos (
    id_intento INT AUTO_INCREMENT PRIMARY KEY,
    id_evaluacion INT NOT NULL,
    id_estudiante INT NOT NULL,
    fecha_inicio DATETIME NOT NULL,
    fecha_envio DATETIME NULL,
    puntaje_obtenido DECIMAL(5,2) DEFAULT 0,
    porcentaje DECIMAL(5,2) DEFAULT 0,
    estado VARCHAR(20) DEFAULT 'iniciado', -- iniciado, enviado, revisado
    FOREIGN KEY (id_evaluacion) REFERENCES evaluaciones(id_evaluacion),
    FOREIGN KEY (id_estudiante) REFERENCES estudiantes(id_estudiante)
);

-- 14. Tabla: respuestas
CREATE TABLE IF NOT EXISTS respuestas (
    id_respuesta INT AUTO_INCREMENT PRIMARY KEY,
    id_intento INT NOT NULL,
    id_pregunta INT NOT NULL,
    id_alternativa INT NULL,
    respuesta_texto TEXT NULL,
    es_correcta TINYINT DEFAULT 0,
    puntaje_obtenido DECIMAL(5,2) DEFAULT 0,
    retroalimentacion TEXT NULL,
    FOREIGN KEY (id_intento) REFERENCES intentos(id_intento) ON DELETE CASCADE,
    FOREIGN KEY (id_pregunta) REFERENCES preguntas(id_pregunta),
    FOREIGN KEY (id_alternativa) REFERENCES alternativas(id_alternativa)
);

-- Inserción de usuario administrador por defecto
INSERT INTO usuarios (nombres, apellidos, usuario, password, rol) 
VALUES ('Administrador', 'Lourdes', 'admin', '$2y$10$TKh8H1.PfQx37YgCzwiKb.KjNyWgaHb9cbcoQgdIVFlYg7B77UdFm', 'administrador');
-- La contraseña por defecto es "secret"