-- ==============================================================================
-- SISTEMA: TUTOR INTERACTIVO DE IDIOMAS Y CORRECTOR DE CÓDIGO CON IA
-- ARCHIVO: schema.sql
-- ESTÁNDAR: MySQL 8.0+ / PostgreSQL (ANSI SQL Compliant con tipos compatibles)
-- AUTOR: Arquitectura de Software & AI Engineering
-- DESCRIPCIÓN: Esquema relacional con integridad referencial, índices de alta
--              concurrencia y trazabilidad total 100% Database-Driven UI.
-- ==============================================================================

-- Crear base de datos si no existe (Sintaxis MySQL / MariaDB)
CREATE DATABASE IF NOT EXISTS tutor_ai_db 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

USE tutor_ai_db;

-- ------------------------------------------------------------------------------
-- 1. TABLA: usuarios
-- Almacena la identidad del estudiante, su nivel general, racha y estadísticas.
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS usuarios (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    puntaje_acumulado INT NOT NULL DEFAULT 0,
    promedio_calificaciones DECIMAL(5, 2) NOT NULL DEFAULT 0.00,
    total_evaluaciones INT NOT NULL DEFAULT 0,
    racha_dias INT NOT NULL DEFAULT 0,
    ultimo_acceso TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    activo BOOLEAN NOT NULL DEFAULT TRUE,
    
    INDEX idx_usuarios_email (email),
    INDEX idx_usuarios_puntaje (puntaje_acumulado DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------------------
-- 2. TABLA: sesiones_usuario
-- Registra cada inicio de sesión activa y metadata técnica para auditoría.
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sesiones_usuario (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    usuario_id BIGINT NOT NULL,
    token_sesion VARCHAR(255) NOT NULL UNIQUE,
    ip_origen VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    fecha_inicio TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_expiracion TIMESTAMP NULL,
    activa BOOLEAN NOT NULL DEFAULT TRUE,
    
    CONSTRAINT fk_sesiones_usuario 
        FOREIGN KEY (usuario_id) REFERENCES usuarios (id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX idx_sesiones_usuario_activa (usuario_id, activa)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------------------
-- 3. TABLA: retos_practica
-- Banco de ejercicios por categoría (IDIOMA / PROGRAMACION), lenguaje y nivel.
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS retos_practica (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    categoria ENUM('IDIOMAS', 'PROGRAMACION') NOT NULL,
    subcategoria VARCHAR(50) NOT NULL, -- ej: 'INGLES', 'ESPANOL', 'JAVA', 'JAVASCRIPT', 'PYTHON', 'HTML'
    nivel_dificultad ENUM('PRINCIPIANTE', 'INTERMEDIO', 'AVANZADO') NOT NULL,
    titulo VARCHAR(200) NOT NULL,
    descripcion TEXT NOT NULL,
    codigo_o_frase_base TEXT NULL,
    instrucciones TEXT NOT NULL,
    criterios_evaluacion JSON NULL, -- Reglas y expectativas en formato JSON
    activo BOOLEAN NOT NULL DEFAULT TRUE,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_retos_filtro (categoria, subcategoria, nivel_dificultad, activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------------------
-- 4. TABLA: peticiones_evaluacion
-- Registro de cada intento enviado por el usuario. Registrado ANTES de llamar
-- a OpenAI (estado 'PROCESANDO') y actualizado DESPUÉS con la evaluación final.
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS peticiones_evaluacion (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    usuario_id BIGINT NOT NULL,
    reto_id BIGINT NOT NULL,
    modo_practica VARCHAR(50) NOT NULL,        -- 'IDIOMAS' o 'PROGRAMACION'
    lenguaje_o_idioma VARCHAR(50) NOT NULL,    -- 'INGLES', 'JAVA', etc.
    nivel_dificultad VARCHAR(20) NOT NULL,    -- 'PRINCIPIANTE', 'INTERMEDIO', 'AVANZADO'
    codigo_o_texto_enviado LONGTEXT NOT NULL, -- Entrada enviada por el usuario
    
    -- Trazabilidad de la respuesta IA
    estado ENUM('PENDIENTE', 'PROCESANDO', 'COMPLETADO', 'FALLIDO') NOT NULL DEFAULT 'PROCESANDO',
    puntaje INT NULL,                         -- Calificación de 0 a 100
    aprobado BOOLEAN NULL,
    explicacion_ia LONGTEXT NULL,             -- Explicación gramatical o técnica
    version_corregida LONGTEXT NULL,          -- Solución idónea generada por la IA
    consejo_proactivo TEXT NULL,              -- Tip adicional de buenas prácticas
    tiempo_respuesta_ms BIGINT NULL,          -- Latencia de la inferencia del LLM
    tokens_utilizados INT NULL,               -- Consumo de tokens OpenAI
    modelo_ia VARCHAR(50) DEFAULT 'gpt-4o-mini',
    
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_peticiones_usuario 
        FOREIGN KEY (usuario_id) REFERENCES usuarios (id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_peticiones_reto 
        FOREIGN KEY (reto_id) REFERENCES retos_practica (id) 
        ON DELETE RESTRICT ON UPDATE CASCADE,
        
    INDEX idx_peticiones_usuario_fecha (usuario_id, creado_en DESC),
    INDEX idx_peticiones_reto (reto_id),
    INDEX idx_peticiones_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------------------
-- 5. TABLA: detalle_errores_ia
-- Detalle 1:N de los errores específicos detectados por el modelo gpt-4o-mini.
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS detalle_errores_ia (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    peticion_id BIGINT NOT NULL,
    tipo_error VARCHAR(80) NOT NULL,          -- 'SINTAXIS', 'GRAMATICA', 'CONCORDANCIA', 'LOGICA', 'ESTILO'
    linea_o_segmento VARCHAR(255) NULL,       -- Número de línea o frase textual donde radica el error
    descripcion TEXT NOT NULL,                -- Explicación puntual del fallo
    sugerencia TEXT NOT NULL,                 -- Cómo solucionarlo
    severidad ENUM('BAJA', 'MEDIA', 'ALTA') NOT NULL DEFAULT 'MEDIA',
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_errores_peticion 
        FOREIGN KEY (peticion_id) REFERENCES peticiones_evaluacion (id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX idx_errores_peticion (peticion_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ==============================================================================
-- DATOS SEMILLA (SEED DATA) PARA AMBIENTE DE DESARROLLO Y TESTING
-- ==============================================================================

-- Usuario de prueba inicial
INSERT INTO usuarios (id, nombre, email, password_hash, puntaje_acumulado, promedio_calificaciones, total_evaluaciones, racha_dias, activo)
VALUES (1, 'Alex Dev & Linguist', 'alex.developer@example.com', '$2a$12$e8YkZ7G8J4bT7zY/9w6e1uX4FwP9C1Z0G6sW9T8F0Y.Q3e9K1N3k.', 280, 85.50, 4, 3, TRUE)
ON DUPLICATE KEY UPDATE nombre=VALUES(nombre);

-- Reto 1: Programación - Java (Principiante)
INSERT INTO retos_practica (id, categoria, subcategoria, nivel_dificultad, titulo, descripcion, codigo_o_frase_base, instrucciones, criterios_evaluacion)
VALUES (
    1, 
    'PROGRAMACION', 
    'JAVA', 
    'PRINCIPIANTE', 
    'Cálculo de Factorial Seguro', 
    'Implementa un método estático que calcule el factorial de un número entero positivo de manera recursiva o iterativa.',
    'public class Calculadora {\n    public static long factorial(int n) {\n        // Escribe tu solución aquí\n        return 0;\n    }\n}', 
    'Validar que números negativos arrojen IllegalArgumentException. Optimizar el tipo de retorno a long.',
    '{"requiere_validacion_negativos": true, "manejo_cero": true, "eficiencia": "O(N)"}'
) ON DUPLICATE KEY UPDATE titulo=VALUES(titulo);

-- Reto 2: Programación - JavaScript (Intermedio)
INSERT INTO retos_practica (id, categoria, subcategoria, nivel_dificultad, titulo, descripcion, codigo_o_frase_base, instrucciones, criterios_evaluacion)
VALUES (
    2, 
    'PROGRAMACION', 
    'JAVASCRIPT', 
    'INTERMEDIO', 
    'Desduplicador y Normalizador de Objetos', 
    'Escribe una función pura que reciba una lista de usuarios con IDs y elimine duplicados conservando la versión con timestamp más reciente.',
    'function desduplicarUsuarios(lista) {\n    // Tu código ES6+ aquí\n}', 
    'Utiliza Map, reduce o Set para evitar complejidad cuadrática O(N^2). Código limpio e inmutable.',
    '{"complejidad_maxima": "O(N)", "inmutabilidad": true}'
) ON DUPLICATE KEY UPDATE titulo=VALUES(titulo);

-- Reto 3: Idiomas - Inglés (Intermedio)
INSERT INTO retos_practica (id, categoria, subcategoria, nivel_dificultad, titulo, descripcion, codigo_o_frase_base, instrucciones, criterios_evaluacion)
VALUES (
    3, 
    'IDIOMAS', 
    'INGLES', 
    'INTERMEDIO', 
    'Condicionales Mixtos en Entorno Laboral', 
    'Describe una situación hipotética en el pasado que tiene una consecuencia en el presente dentro de una reunión técnica.',
    'If we _____ (deploy) the hotfix yesterday, the database _____ (be) stable today.', 
    'Completa y redacta una oración coherente utilizando el Mixed Conditional (Third + Second Conditional). Añade una breve justificación en inglés.',
    '{"tiempo_verbal": "Mixed Conditional", "coherencia_contextual": true}'
) ON DUPLICATE KEY UPDATE titulo=VALUES(titulo);

-- Reto 4: Idiomas - Español (Avanzado)
INSERT INTO retos_practica (id, categoria, subcategoria, nivel_dificultad, titulo, descripcion, codigo_o_frase_base, instrucciones, criterios_evaluacion)
VALUES (
    4, 
    'IDIOMAS', 
    'ESPANOL', 
    'AVANZADO', 
    'Uso del Subjuntivo en Argumentación Formal', 
    'Redacta un párrafo argumentativo defendiendo la migración a microservicios utilizando al menos tres formas del modo subjuntivo.',
    'Es imperativo que el equipo considere...', 
    'Asegurar concordancia de tiempos verbales, conectores lógicos de causa-consecuencia y ortografía impecable.',
    '{"subordinacion_adverbial": true, "formas_subjuntivo_minimas": 3}'
) ON DUPLICATE KEY UPDATE titulo=VALUES(titulo);