-- =========================================================
-- AULA VIRTUAL EMPRENDER EDUCATIVO
-- Base de datos: empr3_aula
-- =========================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =========================================================
-- 1. ROLES
-- =========================================================

CREATE TABLE roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL UNIQUE,
    descripcion VARCHAR(255) NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 2. USUARIOS
-- =========================================================

CREATE TABLE usuarios (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    rol_id INT UNSIGNED NOT NULL,
    nombres VARCHAR(100) NOT NULL,
    apellidos VARCHAR(100) NOT NULL,
    tipo_documento VARCHAR(30) NULL,
    numero_documento VARCHAR(50) NULL,
    correo VARCHAR(150) NOT NULL UNIQUE,
    telefono VARCHAR(30) NULL,
    whatsapp VARCHAR(30) NULL,
    password_hash VARCHAR(255) NOT NULL,
    foto VARCHAR(255) NULL,
    estado ENUM('activo','inactivo','bloqueado','pendiente') NOT NULL DEFAULT 'activo',
    ultimo_acceso DATETIME NULL,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_usuarios_roles
        FOREIGN KEY (rol_id) REFERENCES roles(id),

    UNIQUE KEY uk_documento (tipo_documento, numero_documento),
    INDEX idx_usuario_rol (rol_id),
    INDEX idx_usuario_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 3. PERFIL DE ESTUDIANTES
-- =========================================================

CREATE TABLE estudiantes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id BIGINT UNSIGNED NOT NULL UNIQUE,
    codigo_estudiante VARCHAR(50) NOT NULL UNIQUE,
    fecha_nacimiento DATE NULL,
    genero VARCHAR(30) NULL,
    ciudad VARCHAR(100) NULL,
    departamento VARCHAR(100) NULL,
    direccion VARCHAR(255) NULL,
    contacto_emergencia VARCHAR(150) NULL,
    telefono_emergencia VARCHAR(30) NULL,
    observaciones TEXT NULL,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_estudiantes_usuario
        FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 4. PERFIL DE DOCENTES
-- =========================================================

CREATE TABLE docentes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id BIGINT UNSIGNED NOT NULL UNIQUE,
    codigo_docente VARCHAR(50) NOT NULL UNIQUE,
    perfil_profesional TEXT NULL,
    especialidad VARCHAR(150) NULL,
    experiencia TEXT NULL,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_docentes_usuario
        FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 5. PROGRAMAS ACADÉMICOS
-- =========================================================

CREATE TABLE programas (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL,
    tipo ENUM(
        'curso',
        'diplomado',
        'programa_tecnico',
        'educacion_media',
        'otro'
    ) NOT NULL,
    descripcion TEXT NULL,
    objetivo TEXT NULL,
    requisitos TEXT NULL,
    modalidad VARCHAR(100) NULL,
    duracion VARCHAR(100) NULL,
    imagen VARCHAR(255) NULL,
    estado ENUM('borrador','publicado','inactivo') NOT NULL DEFAULT 'borrador',
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_programa_tipo (tipo),
    INDEX idx_programa_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 6. CURSOS
-- =========================================================

CREATE TABLE cursos (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    programa_id BIGINT UNSIGNED NULL,
    nombre VARCHAR(200) NOT NULL,
    codigo VARCHAR(50) NOT NULL UNIQUE,
    descripcion TEXT NULL,
    imagen VARCHAR(255) NULL,
    estado ENUM('borrador','publicado','cerrado','inactivo') NOT NULL DEFAULT 'borrador',
    fecha_inicio DATE NULL,
    fecha_fin DATE NULL,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_cursos_programa
        FOREIGN KEY (programa_id) REFERENCES programas(id)
        ON DELETE SET NULL,

    INDEX idx_curso_programa (programa_id),
    INDEX idx_curso_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 7. DOCENTES ASIGNADOS A CURSOS
-- =========================================================

CREATE TABLE curso_docentes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    curso_id BIGINT UNSIGNED NOT NULL,
    docente_id BIGINT UNSIGNED NOT NULL,
    rol ENUM('docente','tutor','coordinador') NOT NULL DEFAULT 'docente',
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_curso_docente_curso
        FOREIGN KEY (curso_id) REFERENCES cursos(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_curso_docente_docente
        FOREIGN KEY (docente_id) REFERENCES docentes(id)
        ON DELETE CASCADE,

    UNIQUE KEY uk_curso_docente (curso_id, docente_id, rol)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 8. MÓDULOS
-- =========================================================

CREATE TABLE modulos (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    curso_id BIGINT UNSIGNED NOT NULL,
    nombre VARCHAR(200) NOT NULL,
    descripcion TEXT NULL,
    orden INT UNSIGNED NOT NULL DEFAULT 1,
    estado ENUM('borrador','publicado','inactivo') NOT NULL DEFAULT 'borrador',
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_modulos_curso
        FOREIGN KEY (curso_id) REFERENCES cursos(id)
        ON DELETE CASCADE,

    INDEX idx_modulo_curso_orden (curso_id, orden)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 9. LECCIONES
-- =========================================================

CREATE TABLE lecciones (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    modulo_id BIGINT UNSIGNED NOT NULL,
    titulo VARCHAR(200) NOT NULL,
    descripcion TEXT NULL,
    contenido LONGTEXT NULL,
    tipo ENUM(
        'texto',
        'video',
        'pdf',
        'audio',
        'mixto'
    ) NOT NULL DEFAULT 'texto',
    video_url VARCHAR(500) NULL,
    archivo VARCHAR(255) NULL,
    orden INT UNSIGNED NOT NULL DEFAULT 1,
    duracion_minutos INT UNSIGNED NULL,
    estado ENUM('borrador','publicada','inactiva') NOT NULL DEFAULT 'borrador',
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_lecciones_modulo
        FOREIGN KEY (modulo_id) REFERENCES modulos(id)
        ON DELETE CASCADE,

    INDEX idx_leccion_modulo_orden (modulo_id, orden)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 10. MATERIALES EDUCATIVOS
-- =========================================================

CREATE TABLE materiales (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    leccion_id BIGINT UNSIGNED NULL,
    curso_id BIGINT UNSIGNED NULL,
    titulo VARCHAR(200) NOT NULL,
    descripcion TEXT NULL,
    tipo ENUM('pdf','documento','presentacion','imagen','audio','video','otro') NOT NULL,
    archivo VARCHAR(500) NOT NULL,
    publico TINYINT(1) NOT NULL DEFAULT 0,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_material_leccion
        FOREIGN KEY (leccion_id) REFERENCES lecciones(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_material_curso
        FOREIGN KEY (curso_id) REFERENCES cursos(id)
        ON DELETE CASCADE,

    INDEX idx_material_leccion (leccion_id),
    INDEX idx_material_curso (curso_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 11. MATRÍCULAS
-- =========================================================

CREATE TABLE matriculas (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    estudiante_id BIGINT UNSIGNED NOT NULL,
    curso_id BIGINT UNSIGNED NOT NULL,
    fecha_matricula DATE NOT NULL,
    estado ENUM(
        'preinscrito',
        'activo',
        'retirado',
        'finalizado',
        'cancelado'
    ) NOT NULL DEFAULT 'preinscrito',
    observaciones TEXT NULL,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_matricula_estudiante
        FOREIGN KEY (estudiante_id) REFERENCES estudiantes(id),

    CONSTRAINT fk_matricula_curso
        FOREIGN KEY (curso_id) REFERENCES cursos(id),

    UNIQUE KEY uk_estudiante_curso (estudiante_id, curso_id),
    INDEX idx_matricula_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 12. PROGRESO
-- =========================================================

CREATE TABLE progreso (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    matricula_id BIGINT UNSIGNED NOT NULL,
    leccion_id BIGINT UNSIGNED NOT NULL,
    estado ENUM('pendiente','en_progreso','completada') NOT NULL DEFAULT 'pendiente',
    porcentaje DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    iniciado_en DATETIME NULL,
    completado_en DATETIME NULL,
    actualizado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_progreso_matricula
        FOREIGN KEY (matricula_id) REFERENCES matriculas(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_progreso_leccion
        FOREIGN KEY (leccion_id) REFERENCES lecciones(id)
        ON DELETE CASCADE,

    UNIQUE KEY uk_progreso (matricula_id, leccion_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 13. ACTIVIDADES
-- =========================================================

CREATE TABLE actividades (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    curso_id BIGINT UNSIGNED NOT NULL,
    modulo_id BIGINT UNSIGNED NULL,
    titulo VARCHAR(200) NOT NULL,
    descripcion TEXT NULL,
    fecha_apertura DATETIME NULL,
    fecha_cierre DATETIME NULL,
    porcentaje DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    estado ENUM('borrador','publicada','cerrada') NOT NULL DEFAULT 'borrador',
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_actividad_curso
        FOREIGN KEY (curso_id) REFERENCES cursos(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_actividad_modulo
        FOREIGN KEY (modulo_id) REFERENCES modulos(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 14. ENTREGAS DE ACTIVIDADES
-- =========================================================

CREATE TABLE entregas_actividades (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    actividad_id BIGINT UNSIGNED NOT NULL,
    estudiante_id BIGINT UNSIGNED NOT NULL,
    archivo VARCHAR(500) NULL,
    respuesta TEXT NULL,
    fecha_entrega DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    calificacion DECIMAL(5,2) NULL,
    retroalimentacion TEXT NULL,
    estado ENUM('entregada','calificada','devuelta') NOT NULL DEFAULT 'entregada',

    CONSTRAINT fk_entrega_actividad
        FOREIGN KEY (actividad_id) REFERENCES actividades(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_entrega_estudiante
        FOREIGN KEY (estudiante_id) REFERENCES estudiantes(id)
        ON DELETE CASCADE,

    INDEX idx_entrega_actividad (actividad_id),
    INDEX idx_entrega_estudiante (estudiante_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 15. EVALUACIONES
-- =========================================================

CREATE TABLE evaluaciones (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    curso_id BIGINT UNSIGNED NOT NULL,
    modulo_id BIGINT UNSIGNED NULL,
    titulo VARCHAR(200) NOT NULL,
    descripcion TEXT NULL,
    tiempo_minutos INT UNSIGNED NULL,
    intentos_permitidos INT UNSIGNED NOT NULL DEFAULT 1,
    nota_minima DECIMAL(5,2) NOT NULL DEFAULT 60.00,
    estado ENUM('borrador','publicada','cerrada') NOT NULL DEFAULT 'borrador',
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_evaluacion_curso
        FOREIGN KEY (curso_id) REFERENCES cursos(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_evaluacion_modulo
        FOREIGN KEY (modulo_id) REFERENCES modulos(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 16. PREGUNTAS
-- =========================================================

CREATE TABLE preguntas (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    evaluacion_id BIGINT UNSIGNED NOT NULL,
    pregunta TEXT NOT NULL,
    tipo ENUM(
        'opcion_multiple',
        'verdadero_falso',
        'respuesta_corta',
        'respuesta_abierta'
    ) NOT NULL,
    puntaje DECIMAL(6,2) NOT NULL DEFAULT 1.00,
    orden INT UNSIGNED NOT NULL DEFAULT 1,

    CONSTRAINT fk_pregunta_evaluacion
        FOREIGN KEY (evaluacion_id) REFERENCES evaluaciones(id)
        ON DELETE CASCADE,

    INDEX idx_pregunta_evaluacion (evaluacion_id, orden)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 17. OPCIONES DE PREGUNTAS
-- =========================================================

CREATE TABLE opciones_pregunta (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pregunta_id BIGINT UNSIGNED NOT NULL,
    opcion TEXT NOT NULL,
    es_correcta TINYINT(1) NOT NULL DEFAULT 0,
    orden INT UNSIGNED NOT NULL DEFAULT 1,

    CONSTRAINT fk_opcion_pregunta
        FOREIGN KEY (pregunta_id) REFERENCES preguntas(id)
        ON DELETE CASCADE,

    INDEX idx_opcion_pregunta (pregunta_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 18. INTENTOS DE EVALUACIÓN
-- =========================================================

CREATE TABLE intentos_evaluacion (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    evaluacion_id BIGINT UNSIGNED NOT NULL,
    estudiante_id BIGINT UNSIGNED NOT NULL,
    numero_intento INT UNSIGNED NOT NULL,
    iniciado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    finalizado_en DATETIME NULL,
    calificacion DECIMAL(5,2) NULL,
    aprobado TINYINT(1) NULL,

    CONSTRAINT fk_intento_evaluacion
        FOREIGN KEY (evaluacion_id) REFERENCES evaluaciones(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_intento_estudiante
        FOREIGN KEY (estudiante_id) REFERENCES estudiantes(id)
        ON DELETE CASCADE,

    UNIQUE KEY uk_intento (evaluacion_id, estudiante_id, numero_intento)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 19. RESPUESTAS DE EVALUACIÓN
-- =========================================================

CREATE TABLE respuestas_evaluacion (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    intento_id BIGINT UNSIGNED NOT NULL,
    pregunta_id BIGINT UNSIGNED NOT NULL,
    opcion_id BIGINT UNSIGNED NULL,
    respuesta_texto TEXT NULL,
    es_correcta TINYINT(1) NULL,
    puntaje_obtenido DECIMAL(6,2) NULL,

    CONSTRAINT fk_respuesta_intento
        FOREIGN KEY (intento_id) REFERENCES intentos_evaluacion(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_respuesta_pregunta
        FOREIGN KEY (pregunta_id) REFERENCES preguntas(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_respuesta_opcion
        FOREIGN KEY (opcion_id) REFERENCES opciones_pregunta(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 20. CUENTAS FINANCIERAS DEL ESTUDIANTE
-- =========================================================

CREATE TABLE cuentas_financieras (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    estudiante_id BIGINT UNSIGNED NOT NULL UNIQUE,
    saldo_a_favor DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    saldo_pendiente DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    estado ENUM('al_dia','pendiente','vencido','bloqueado') NOT NULL DEFAULT 'al_dia',
    actualizado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_cuenta_financiera_estudiante
        FOREIGN KEY (estudiante_id) REFERENCES estudiantes(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 21. OBLIGACIONES FINANCIERAS
-- =========================================================

CREATE TABLE obligaciones_financieras (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    estudiante_id BIGINT UNSIGNED NOT NULL,
    matricula_id BIGINT UNSIGNED NULL,
    concepto VARCHAR(200) NOT NULL,
    valor DECIMAL(15,2) NOT NULL,
    fecha_generacion DATE NOT NULL,
    fecha_vencimiento DATE NULL,
    estado ENUM('pendiente','pagada','vencida','anulada') NOT NULL DEFAULT 'pendiente',
    observaciones TEXT NULL,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_obligacion_estudiante
        FOREIGN KEY (estudiante_id) REFERENCES estudiantes(id),

    CONSTRAINT fk_obligacion_matricula
        FOREIGN KEY (matricula_id) REFERENCES matriculas(id)
        ON DELETE SET NULL,

    INDEX idx_obligacion_estudiante (estudiante_id),
    INDEX idx_obligacion_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 22. PAGOS
-- =========================================================

CREATE TABLE pagos (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    estudiante_id BIGINT UNSIGNED NOT NULL,
    obligacion_id BIGINT UNSIGNED NULL,
    referencia VARCHAR(100) NULL UNIQUE,
    valor DECIMAL(15,2) NOT NULL,
    metodo VARCHAR(50) NULL,
    fecha_pago DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    estado ENUM('pendiente','aprobado','rechazado','anulado') NOT NULL DEFAULT 'pendiente',
    comprobante VARCHAR(500) NULL,
    observaciones TEXT NULL,

    CONSTRAINT fk_pago_estudiante
        FOREIGN KEY (estudiante_id) REFERENCES estudiantes(id),

    CONSTRAINT fk_pago_obligacion
        FOREIGN KEY (obligacion_id) REFERENCES obligaciones_financieras(id)
        ON DELETE SET NULL,

    INDEX idx_pago_estudiante (estudiante_id),
    INDEX idx_pago_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 23. MOVIMIENTOS FINANCIEROS
-- =========================================================

CREATE TABLE movimientos_financieros (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    estudiante_id BIGINT UNSIGNED NOT NULL,
    tipo ENUM('cargo','abono','ajuste','saldo_a_favor') NOT NULL,
    concepto VARCHAR(200) NOT NULL,
    valor DECIMAL(15,2) NOT NULL,
    referencia VARCHAR(100) NULL,
    fecha_movimiento DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    observaciones TEXT NULL,

    CONSTRAINT fk_movimiento_estudiante
        FOREIGN KEY (estudiante_id) REFERENCES estudiantes(id),

    INDEX idx_movimiento_estudiante (estudiante_id),
    INDEX idx_movimiento_fecha (fecha_movimiento)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 24. CERTIFICADOS
-- =========================================================

CREATE TABLE certificados (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    estudiante_id BIGINT UNSIGNED NOT NULL,
    curso_id BIGINT UNSIGNED NOT NULL,
    codigo_verificacion VARCHAR(100) NOT NULL UNIQUE,
    fecha_expedicion DATE NOT NULL,
    horas_formacion INT UNSIGNED NULL,
    archivo VARCHAR(500) NULL,
    estado ENUM('emitido','anulado') NOT NULL DEFAULT 'emitido',
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_certificado_estudiante
        FOREIGN KEY (estudiante_id) REFERENCES estudiantes(id),

    CONSTRAINT fk_certificado_curso
        FOREIGN KEY (curso_id) REFERENCES cursos(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 25. NOTIFICACIONES
-- =========================================================

CREATE TABLE notificaciones (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id BIGINT UNSIGNED NOT NULL,
    titulo VARCHAR(200) NOT NULL,
    mensaje TEXT NOT NULL,
    tipo VARCHAR(50) NULL,
    leida TINYINT(1) NOT NULL DEFAULT 0,
    fecha_lectura DATETIME NULL,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_notificacion_usuario
        FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
        ON DELETE CASCADE,

    INDEX idx_notificacion_usuario (usuario_id, leida)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- 26. MENSAJES
-- =========================================================

CREATE TABLE mensajes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    remitente_id BIGINT UNSIGNED NOT NULL,
    destinatario_id BIGINT UNSIGNED NOT NULL,
    asunto VARCHAR(200) NULL,
    mensaje TEXT NOT NULL,
    leido TINYINT(1) NOT NULL DEFAULT 0,
    fecha_lectura DATETIME NULL,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_mensaje_remitente
        FOREIGN KEY (remitente_id) REFERENCES usuarios(id),

    CONSTRAINT fk_mensaje_destinatario
        FOREIGN KEY (destinatario_id) REFERENCES usuarios(id),

    INDEX idx_mensaje_destinatario (destinatario_id, leido),
    INDEX idx_mensaje_remitente (remitente_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =========================================================
-- ROLES INICIALES
-- =========================================================

INSERT INTO roles (nombre, descripcion) VALUES
('superadministrador', 'Control total del Aula Virtual'),
('administrador', 'Administración académica y administrativa'),
('docente', 'Gestión de contenidos y actividades académicas'),
('tutor', 'Acompañamiento académico de estudiantes'),
('estudiante', 'Acceso al proceso de formación');


SET FOREIGN_KEY_CHECKS = 1;