trading-platform-database/schemas/02_education_schema.sql

-- ============================================================================
-- OrbiQuant IA - Esquema EDUCATION
-- ============================================================================
-- Archivo: 02_education_schema.sql
-- Descripción: Cursos, lecciones, inscripciones y contenido educativo
-- Fecha: 2025-12-05
-- ============================================================================

SET search_path TO education;

-- ============================================================================
-- TIPOS ENUMERADOS
-- ============================================================================

CREATE TYPE course_level_enum AS ENUM ('beginner', 'intermediate', 'advanced', 'expert');
CREATE TYPE course_status_enum AS ENUM ('draft', 'published', 'archived');
CREATE TYPE content_type_enum AS ENUM ('video', 'text', 'quiz', 'exercise', 'resource');
CREATE TYPE enrollment_status_enum AS ENUM ('active', 'completed', 'expired', 'cancelled');

-- ============================================================================
-- TABLA: categories
-- Descripción: Categorías de cursos
-- ============================================================================
CREATE TABLE IF NOT EXISTS categories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    icon VARCHAR(50),
    parent_id UUID REFERENCES categories(id),
    sort_order INT DEFAULT 0,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_categories_parent ON categories(parent_id);
CREATE INDEX idx_categories_slug ON categories(slug);

-- ============================================================================
-- TABLA: courses
-- Descripción: Cursos de trading
-- ============================================================================
CREATE TABLE IF NOT EXISTS courses (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Información básica
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    short_description VARCHAR(500),
    thumbnail_url TEXT,
    preview_video_url TEXT,

    -- Categorización
    category_id UUID REFERENCES categories(id),
    level course_level_enum DEFAULT 'beginner',
    tags TEXT[],

    -- Pricing
    is_free BOOLEAN DEFAULT FALSE,
    price DECIMAL(10,2) DEFAULT 0,
    currency CHAR(3) DEFAULT 'USD',

    -- Acceso
    requires_subscription BOOLEAN DEFAULT FALSE,
    min_subscription_tier VARCHAR(20), -- 'basic', 'pro', 'elite'

    -- Metadata
    duration_minutes INT,
    lessons_count INT DEFAULT 0,
    enrolled_count INT DEFAULT 0,

    -- Rating
    average_rating DECIMAL(3,2) DEFAULT 0,
    ratings_count INT DEFAULT 0,

    -- Estado
    status course_status_enum DEFAULT 'draft',
    published_at TIMESTAMPTZ,

    -- Autor
    instructor_id UUID REFERENCES public.users(id),

    -- AI Generation
    ai_generated BOOLEAN DEFAULT FALSE,
    ai_generation_prompt TEXT,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_courses_slug ON courses(slug);
CREATE INDEX idx_courses_category ON courses(category_id);
CREATE INDEX idx_courses_status ON courses(status);
CREATE INDEX idx_courses_level ON courses(level);
CREATE INDEX idx_courses_instructor ON courses(instructor_id);

-- ============================================================================
-- TABLA: modules
-- Descripción: Módulos/secciones de un curso
-- ============================================================================
CREATE TABLE IF NOT EXISTS modules (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,

    title VARCHAR(255) NOT NULL,
    description TEXT,
    sort_order INT DEFAULT 0,

    -- Progreso requerido del módulo anterior para desbloquear
    unlock_after_module_id UUID REFERENCES modules(id),

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_modules_course ON modules(course_id);

-- ============================================================================
-- TABLA: lessons
-- Descripción: Lecciones individuales
-- ============================================================================
CREATE TABLE IF NOT EXISTS lessons (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    module_id UUID NOT NULL REFERENCES modules(id) ON DELETE CASCADE,
    course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,

    -- Contenido
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    content_type content_type_enum DEFAULT 'video',

    -- Video
    video_url TEXT,
    video_duration_seconds INT,
    video_provider VARCHAR(50), -- 'youtube', 'vimeo', 'internal'

    -- Texto
    content_markdown TEXT,
    content_html TEXT,

    -- Recursos adicionales
    resources JSONB DEFAULT '[]', -- [{name, url, type}]

    -- Orden
    sort_order INT DEFAULT 0,

    -- Acceso
    is_preview BOOLEAN DEFAULT FALSE, -- Disponible sin inscripción

    -- AI
    ai_generated BOOLEAN DEFAULT FALSE,
    ai_summary TEXT,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(course_id, slug)
);

CREATE INDEX idx_lessons_module ON lessons(module_id);
CREATE INDEX idx_lessons_course ON lessons(course_id);

-- ============================================================================
-- TABLA: quizzes
-- Descripción: Cuestionarios y evaluaciones
-- ============================================================================
CREATE TABLE IF NOT EXISTS quizzes (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    lesson_id UUID REFERENCES lessons(id) ON DELETE CASCADE,
    course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,

    title VARCHAR(255) NOT NULL,
    description TEXT,

    -- Configuración
    passing_score INT DEFAULT 70, -- Porcentaje mínimo
    max_attempts INT, -- NULL = ilimitado
    time_limit_minutes INT,
    shuffle_questions BOOLEAN DEFAULT FALSE,
    show_correct_answers BOOLEAN DEFAULT TRUE,

    -- AI
    ai_generated BOOLEAN DEFAULT FALSE,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_quizzes_lesson ON quizzes(lesson_id);
CREATE INDEX idx_quizzes_course ON quizzes(course_id);

-- ============================================================================
-- TABLA: quiz_questions
-- Descripción: Preguntas de cuestionarios
-- ============================================================================
CREATE TYPE question_type_enum AS ENUM ('multiple_choice', 'true_false', 'multiple_answer', 'short_answer');

CREATE TABLE IF NOT EXISTS quiz_questions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    quiz_id UUID NOT NULL REFERENCES quizzes(id) ON DELETE CASCADE,

    question_type question_type_enum DEFAULT 'multiple_choice',
    question_text TEXT NOT NULL,
    explanation TEXT, -- Explicación mostrada después de responder

    -- Opciones (para multiple_choice y multiple_answer)
    options JSONB, -- [{id, text, is_correct}]

    -- Para short_answer
    correct_answers TEXT[], -- Respuestas aceptadas

    points INT DEFAULT 1,
    sort_order INT DEFAULT 0,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_quiz_questions_quiz ON quiz_questions(quiz_id);

-- ============================================================================
-- TABLA: enrollments
-- Descripción: Inscripciones de usuarios a cursos
-- ============================================================================
CREATE TABLE IF NOT EXISTS enrollments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
    course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,

    -- Estado
    status enrollment_status_enum DEFAULT 'active',

    -- Progreso
    progress_percentage DECIMAL(5,2) DEFAULT 0,
    lessons_completed INT DEFAULT 0,

    -- Acceso
    enrolled_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMPTZ, -- NULL = acceso permanente
    completed_at TIMESTAMPTZ,

    -- Pago (si aplica)
    payment_id UUID, -- Referencia a financial.payments

    -- Certificado
    certificate_issued BOOLEAN DEFAULT FALSE,
    certificate_url TEXT,
    certificate_issued_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(user_id, course_id)
);

CREATE INDEX idx_enrollments_user ON enrollments(user_id);
CREATE INDEX idx_enrollments_course ON enrollments(course_id);
CREATE INDEX idx_enrollments_status ON enrollments(status);

-- ============================================================================
-- TABLA: lesson_progress
-- Descripción: Progreso por lección
-- ============================================================================
CREATE TABLE IF NOT EXISTS lesson_progress (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
    lesson_id UUID NOT NULL REFERENCES lessons(id) ON DELETE CASCADE,
    enrollment_id UUID NOT NULL REFERENCES enrollments(id) ON DELETE CASCADE,

    -- Progreso de video
    video_watched_seconds INT DEFAULT 0,
    video_completed BOOLEAN DEFAULT FALSE,

    -- Estado
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,

    -- Notas del usuario
    user_notes TEXT,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(user_id, lesson_id)
);

CREATE INDEX idx_lesson_progress_user ON lesson_progress(user_id);
CREATE INDEX idx_lesson_progress_lesson ON lesson_progress(lesson_id);
CREATE INDEX idx_lesson_progress_enrollment ON lesson_progress(enrollment_id);

-- ============================================================================
-- TABLA: quiz_attempts
-- Descripción: Intentos de cuestionarios
-- ============================================================================
CREATE TABLE IF NOT EXISTS quiz_attempts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
    quiz_id UUID NOT NULL REFERENCES quizzes(id) ON DELETE CASCADE,
    enrollment_id UUID REFERENCES enrollments(id) ON DELETE SET NULL,

    -- Resultado
    score DECIMAL(5,2),
    passed BOOLEAN,

    -- Respuestas
    answers JSONB, -- [{question_id, answer, is_correct}]

    -- Tiempo
    started_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    submitted_at TIMESTAMPTZ,
    time_spent_seconds INT,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_quiz_attempts_user ON quiz_attempts(user_id);
CREATE INDEX idx_quiz_attempts_quiz ON quiz_attempts(quiz_id);

-- ============================================================================
-- TABLA: course_reviews
-- Descripción: Reseñas de cursos
-- ============================================================================
CREATE TABLE IF NOT EXISTS course_reviews (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
    course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,

    rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    review_text TEXT,

    -- Moderación
    is_approved BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(user_id, course_id)
);

CREATE INDEX idx_course_reviews_course ON course_reviews(course_id);

-- ============================================================================
-- TABLA: ai_content_generations
-- Descripción: Registro de contenido generado por IA
-- ============================================================================
CREATE TABLE IF NOT EXISTS ai_content_generations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Referencia
    entity_type VARCHAR(50) NOT NULL, -- 'course', 'lesson', 'quiz'
    entity_id UUID NOT NULL,

    -- Generación
    prompt TEXT NOT NULL,
    model_used VARCHAR(100),
    generated_content TEXT NOT NULL,
    tokens_used INT,

    -- Estado
    approved BOOLEAN DEFAULT FALSE,
    approved_by UUID REFERENCES public.users(id),
    approved_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_ai_generations_entity ON ai_content_generations(entity_type, entity_id);

-- ============================================================================
-- TRIGGERS
-- ============================================================================

CREATE TRIGGER update_courses_updated_at
    BEFORE UPDATE ON courses
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_modules_updated_at
    BEFORE UPDATE ON modules
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_lessons_updated_at
    BEFORE UPDATE ON lessons
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_enrollments_updated_at
    BEFORE UPDATE ON enrollments
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_lesson_progress_updated_at
    BEFORE UPDATE ON lesson_progress
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at_column();