DatabaseCreation-AdvDb: shifter_dml.sql

-- ============================================================================
-- SHIFTER COMPLETE DEMO DATA GENERATION
-- ============================================================================
-- Generates realistic demo data for ALL 49 tables with:
-- - 1-5 million users
-- - 50,000 courses (for index testing)
-- - Multiple course versions per course
-- - Enrollments (UNIQUE per user+course_version, some users with 100k-1M enrollments)
-- - All related entities automatically scaled
-- - Multi-language support (mk, en, es, it, de, sr, hr)
-- ============================================================================

SET client_min_messages = WARNING;

-- Configuration
DO $$
BEGIN
    RAISE NOTICE '============================================';
    RAISE NOTICE 'SHIFTER DEMO DATA GENERATION';
    RAISE NOTICE '============================================';
    RAISE NOTICE 'Target Configuration:';
    RAISE NOTICE '  - Users: 3,000,000';
    RAISE NOTICE '  - Courses: 50,000';
    RAISE NOTICE '  - Topics: 100';
    RAISE NOTICE '  - Skills: 500';
    RAISE NOTICE '  - Languages: 7';
    RAISE NOTICE '  - Power Users: 50 (100k-1M enrollments each)';
    RAISE NOTICE '  - Normal Users: 100k (1-50 enrollments each)';
    RAISE NOTICE '============================================';
END $$;

-- ============================================================================
-- 1. LANGUAGE (7 languages)
-- ============================================================================

INSERT INTO language (language) VALUES
    ('mk'), ('en'), ('es'), ('it'), ('de'), ('sr'), ('hr')
ON CONFLICT (language) DO NOTHING;

RAISE NOTICE 'Languages inserted: 7';

-- ============================================================================
-- 2. ADMIN (10 admins)
-- ============================================================================

INSERT INTO admin (email, password_hash)
SELECT 
    'admin' || i || '@shifter.mk',
    '$2a$10$' || md5(random()::text || i::text)
FROM generate_series(1, 10) i;

DO $$ BEGIN RAISE NOTICE 'Admins inserted: %', (SELECT COUNT(*) FROM admin); END $$;

-- ============================================================================
-- 3. EXPERT (2000 experts for course creation)
-- ============================================================================

INSERT INTO expert (name, email, password_hash, login_provider)
SELECT 
    'Expert ' || i,
    'expert' || i || '@shifter.mk',
    CASE WHEN random() < 0.3 THEN NULL ELSE '$2a$10$' || md5(random()::text || i::text) END,
    CASE WHEN random() < 0.3 THEN 'GOOGLE' ELSE 'EMAIL' END
FROM generate_series(1, 2000) i;

DO $$ BEGIN RAISE NOTICE 'Experts inserted: %', (SELECT COUNT(*) FROM expert); END $$;

-- ============================================================================
-- 4. USERS (3 MILLION - Batched for performance)
-- ============================================================================

DO $$
DECLARE
    batch_size INTEGER := 100000;
    total_users INTEGER := 3000000;
    num_batches INTEGER := total_users / batch_size;
    current_batch INTEGER;
    start_id INTEGER;
    end_id INTEGER;
BEGIN
    FOR current_batch IN 1..num_batches LOOP
        start_id := (current_batch - 1) * batch_size + 1;
        end_id := current_batch * batch_size;
        
        INSERT INTO "user" (
            name, email, password_hash, login_provider, verified, profile_completed, 
            deleted, used_free_consultation, company_size, work_position, points, created_at
        )
        SELECT 
            'User ' || i,
            'user' || i || '@demo.mk',
            CASE WHEN random() < 0.3 THEN NULL ELSE '$2a$10$' || md5(random()::text || i::text) END,
            CASE WHEN random() < 0.35 THEN 'GOOGLE' ELSE 'EMAIL' END,
            random() < 0.72,
            random() < 0.65,
            random() < 0.03,
            random() < 0.18,
            CASE 
                WHEN random() < 0.25 THEN 'FREELANCE'
                WHEN random() < 0.45 THEN 'SMALL'
                WHEN random() < 0.70 THEN 'MEDIUM'
                WHEN random() < 0.90 THEN 'LARGE'
                ELSE 'ENTERPRISE'
            END,
            (ARRAY['Software Engineer', 'Product Manager', 'Designer', 'Marketing Specialist', 
                   'Business Analyst', 'Data Scientist', 'DevOps Engineer', 'Sales Manager',
                   'HR Manager', 'Financial Analyst', 'Content Creator', 'Operations Manager'])[1 + floor(random() * 12)::INTEGER],
            (random() * 15000)::INTEGER,
            CURRENT_TIMESTAMP - (random() * INTERVAL '3 years')
        FROM generate_series(start_id, end_id) i;
        
        IF current_batch % 5 = 0 THEN
            RAISE NOTICE 'User batch % of % completed (% users)', current_batch, num_batches, end_id;
        END IF;
    END LOOP;
END $$;

DO $$ BEGIN RAISE NOTICE 'Users inserted: %', (SELECT COUNT(*) FROM "user"); END $$;

-- ============================================================================
-- 5. VERIFICATION_TOKEN (For unverified users)
-- ============================================================================

INSERT INTO verification_token (user_id, created_at, expires_at)
SELECT 
    id,
    created_at,
    created_at + INTERVAL '24 hours'
FROM "user"
WHERE verified = FALSE
LIMIT 500000; -- First 500k unverified users

DO $$ BEGIN RAISE NOTICE 'Verification tokens inserted: %', (SELECT COUNT(*) FROM verification_token); END $$;

-- ============================================================================
-- 6. MEETING_EMAIL_REMINDER (For consultation meetings)
-- ============================================================================

INSERT INTO meeting_email_reminder (user_id, meeting_at, scheduled_at, sent, meeting_link)
SELECT 
    id,
    CURRENT_TIMESTAMP + (random() * INTERVAL '30 days'),
    CURRENT_TIMESTAMP - (random() * INTERVAL '7 days'),
    random() < 0.4,
    CASE WHEN random() < 0.8 THEN 'https://meet.shifter.mk/' || md5(random()::text) ELSE NULL END
FROM "user"
WHERE used_free_consultation = TRUE OR random() < 0.15
LIMIT 200000;

DO $$ BEGIN RAISE NOTICE 'Meeting reminders inserted: %', (SELECT COUNT(*) FROM meeting_email_reminder); END $$;

-- ============================================================================
-- 7. TOPIC (100 topics)
-- ============================================================================

INSERT INTO topic (slug)
SELECT 
    CASE i
        WHEN 1 THEN 'programming'
        WHEN 2 THEN 'web-development'
        WHEN 3 THEN 'data-science'
        WHEN 4 THEN 'machine-learning'
        WHEN 5 THEN 'artificial-intelligence'
        WHEN 6 THEN 'digital-marketing'
        WHEN 7 THEN 'business-analytics'
        WHEN 8 THEN 'project-management'
        WHEN 9 THEN 'leadership'
        WHEN 10 THEN 'finance'
        WHEN 11 THEN 'design'
        WHEN 12 THEN 'ux-ui'
        WHEN 13 THEN 'mobile-development'
        WHEN 14 THEN 'cloud-computing'
        WHEN 15 THEN 'devops'
        WHEN 16 THEN 'cybersecurity'
        WHEN 17 THEN 'blockchain'
        WHEN 18 THEN 'sales'
        WHEN 19 THEN 'customer-success'
        WHEN 20 THEN 'human-resources'
        ELSE 'topic-' || i
    END
FROM generate_series(1, 100) i;

-- Topic translations
INSERT INTO topic_translate (topic_id, language_id, title, description)
SELECT 
    t.id,
    l.id,
    CASE l.language
        WHEN 'mk' THEN 'Тема ' || t.slug
        WHEN 'en' THEN 'Topic ' || t.slug
        WHEN 'es' THEN 'Tema ' || t.slug
        WHEN 'it' THEN 'Argomento ' || t.slug
        WHEN 'de' THEN 'Thema ' || t.slug
        WHEN 'sr' THEN 'Тема ' || t.slug
        WHEN 'hr' THEN 'Tema ' || t.slug
    END,
    CASE l.language
        WHEN 'mk' THEN 'Опис за тема ' || t.slug
        WHEN 'en' THEN 'Description for topic ' || t.slug
        WHEN 'es' THEN 'Descripción del tema ' || t.slug
        WHEN 'it' THEN 'Descrizione argomento ' || t.slug
        WHEN 'de' THEN 'Themenbeschreibung ' || t.slug
        WHEN 'sr' THEN 'Опис теме ' || t.slug
        WHEN 'hr' THEN 'Opis teme ' || t.slug
    END
FROM topic t
CROSS JOIN language l;

DO $$ BEGIN RAISE NOTICE 'Topics inserted: %, Translations: %', 
    (SELECT COUNT(*) FROM topic), (SELECT COUNT(*) FROM topic_translate); END $$;

-- ============================================================================
-- 8. SKILL (500 skills)
-- ============================================================================

INSERT INTO skill (created_at)
SELECT CURRENT_TIMESTAMP - (random() * INTERVAL '2 years')
FROM generate_series(1, 500) i;

-- Skill translations
INSERT INTO skill_translate (skill_id, language_id, title, description)
SELECT 
    s.id,
    l.id,
    CASE l.language
        WHEN 'mk' THEN (ARRAY['Python', 'JavaScript', 'Java', 'SQL', 'React', 'Лидерство', 'Комуникација', 'Анализа'])[1 + (s.id % 8)] || ' ' || s.id
        WHEN 'en' THEN (ARRAY['Python', 'JavaScript', 'Java', 'SQL', 'React', 'Leadership', 'Communication', 'Analysis'])[1 + (s.id % 8)] || ' ' || s.id
        WHEN 'es' THEN (ARRAY['Python', 'JavaScript', 'Java', 'SQL', 'React', 'Liderazgo', 'Comunicación', 'Análisis'])[1 + (s.id % 8)] || ' ' || s.id
        WHEN 'it' THEN (ARRAY['Python', 'JavaScript', 'Java', 'SQL', 'React', 'Leadership', 'Comunicazione', 'Analisi'])[1 + (s.id % 8)] || ' ' || s.id
        WHEN 'de' THEN (ARRAY['Python', 'JavaScript', 'Java', 'SQL', 'React', 'Führung', 'Kommunikation', 'Analyse'])[1 + (s.id % 8)] || ' ' || s.id
        WHEN 'sr' THEN (ARRAY['Python', 'JavaScript', 'Java', 'SQL', 'React', 'Лидерство', 'Комуникација', 'Анализа'])[1 + (s.id % 8)] || ' ' || s.id
        WHEN 'hr' THEN (ARRAY['Python', 'JavaScript', 'Java', 'SQL', 'React', 'Vodstvo', 'Komunikacija', 'Analiza'])[1 + (s.id % 8)] || ' ' || s.id
    END,
    CASE l.language
        WHEN 'mk' THEN 'Опис за вештина ' || s.id
        WHEN 'en' THEN 'Skill description ' || s.id
        WHEN 'es' THEN 'Descripción de habilidad ' || s.id
        WHEN 'it' THEN 'Descrizione abilità ' || s.id
        WHEN 'de' THEN 'Fähigkeitsbeschreibung ' || s.id
        WHEN 'sr' THEN 'Опис вештине ' || s.id
        WHEN 'hr' THEN 'Opis vještine ' || s.id
    END
FROM skill s
CROSS JOIN language l;

DO $$ BEGIN RAISE NOTICE 'Skills inserted: %, Translations: %', 
    (SELECT COUNT(*) FROM skill), (SELECT COUNT(*) FROM skill_translate); END $$;

-- ============================================================================
-- 9. COURSE (50,000 courses for index testing)
-- ============================================================================

DO $$
DECLARE
    batch_size INTEGER := 5000;
    total_courses INTEGER := 50000;
    num_batches INTEGER := total_courses / batch_size;
    current_batch INTEGER;
    start_id INTEGER;
    end_id INTEGER;
BEGIN
    FOR current_batch IN 1..num_batches LOOP
        start_id := (current_batch - 1) * batch_size + 1;
        end_id := current_batch * batch_size;
        
        INSERT INTO course (
            expert_id, difficulty, estimated_duration, is_published, 
            is_featured, created_at, updated_at
        )
        SELECT 
            (SELECT id FROM expert ORDER BY random() LIMIT 1),
            (ARRAY['BEGINNER', 'INTERMEDIATE', 'ADVANCED', 'EXPERT']::course_difficulty_enum[])[1 + floor(random() * 4)::INTEGER],
            (1800 + random() * 43200)::INTEGER, -- 30min to 13 hours
            random() < 0.92,
            random() < 0.08,
            CURRENT_TIMESTAMP - (random() * INTERVAL '3 years'),
            CURRENT_TIMESTAMP - (random() * INTERVAL '6 months')
        FROM generate_series(start_id, end_id) i;
        
        IF current_batch % 2 = 0 THEN
            RAISE NOTICE 'Course batch % of % completed (% courses)', current_batch, num_batches, end_id;
        END IF;
    END LOOP;
END $$;

DO $$ BEGIN RAISE NOTICE 'Courses inserted: %', (SELECT COUNT(*) FROM course); END $$;

-- ============================================================================
-- 10. COURSE_TRANSLATE (7 languages × 50k courses = 350k rows)
-- ============================================================================

DO $$
DECLARE
    batch_size INTEGER := 50000;
    total_courses INTEGER := 50000;
    num_batches INTEGER := CEIL(total_courses::NUMERIC / batch_size);
    current_batch INTEGER;
    start_id BIGINT;
    end_id BIGINT;
BEGIN
    FOR current_batch IN 1..num_batches LOOP
        SELECT MIN(id), MAX(id) INTO start_id, end_id
        FROM (
            SELECT id FROM course ORDER BY id LIMIT batch_size OFFSET (current_batch - 1) * batch_size
        ) sub;
        
        INSERT INTO course_translate (course_id, language_id, title, description, short_description)
        SELECT 
            c.id,
            l.id,
            CASE l.language
                WHEN 'mk' THEN (ARRAY['Програмирање', 'Дигитален Маркетинг', 'Дизајн', 'Бизнис Аналитика', 
                                       'Управување со Проекти', 'Финансии', 'Лидерство', 'Податочна Наука'])[1 + (c.id % 8)] || ' - Курс ' || c.id
                WHEN 'en' THEN (ARRAY['Programming', 'Digital Marketing', 'Design', 'Business Analytics', 
                                       'Project Management', 'Finance', 'Leadership', 'Data Science'])[1 + (c.id % 8)] || ' - Course ' || c.id
                WHEN 'es' THEN 'Curso ' || c.id || ': Programación'
                WHEN 'it' THEN 'Corso ' || c.id || ': Programmazione'
                WHEN 'de' THEN 'Kurs ' || c.id || ': Programmierung'
                WHEN 'sr' THEN 'Курс ' || c.id || ': Програмирање'
                WHEN 'hr' THEN 'Tečaj ' || c.id || ': Programiranje'
            END,
            CASE l.language
                WHEN 'mk' THEN 'Детален опис за курс ' || c.id || '. Научете практични вештини.'
                WHEN 'en' THEN 'Detailed description for course ' || c.id || '. Learn practical skills.'
                WHEN 'es' THEN 'Descripción detallada del curso ' || c.id
                WHEN 'it' THEN 'Descrizione dettagliata del corso ' || c.id
                WHEN 'de' THEN 'Detaillierte Kursbeschreibung ' || c.id
                WHEN 'sr' THEN 'Детаљан опис курса ' || c.id
                WHEN 'hr' THEN 'Detaljan opis tečaja ' || c.id
            END,
            CASE l.language
                WHEN 'mk' THEN 'Краток опис'
                WHEN 'en' THEN 'Short description'
                ELSE 'Short desc'
            END
        FROM course c
        CROSS JOIN language l
        WHERE c.id BETWEEN start_id AND end_id;
        
        RAISE NOTICE 'Course translations batch % of % completed', current_batch, num_batches;
    END LOOP;
END $$;

DO $$ BEGIN RAISE NOTICE 'Course translations inserted: %', (SELECT COUNT(*) FROM course_translate); END $$;

-- ============================================================================
-- 11. COURSE_TOPIC (Each course has 1-3 topics)
-- ============================================================================

INSERT INTO course_topic (course_id, topic_id)
SELECT DISTINCT ON (c.id, t.id)
    c.id,
    t.id
FROM course c
CROSS JOIN LATERAL (
    SELECT id FROM topic ORDER BY random() LIMIT (1 + floor(random() * 2)::INTEGER)
) t;

DO $$ BEGIN RAISE NOTICE 'Course topics inserted: %', (SELECT COUNT(*) FROM course_topic); END $$;

-- ============================================================================
-- 12. COURSE_VERSION (1-4 versions per course)
-- ============================================================================

INSERT INTO course_version (
    course_id, version_number, is_current, published_at, created_at
)
SELECT 
    c.id,
    ver,
    ver = max_ver,
    CURRENT_TIMESTAMP - (random() * INTERVAL '2 years'),
    CURRENT_TIMESTAMP - (random() * INTERVAL '2 years')
FROM course c
CROSS JOIN LATERAL (
    SELECT 
        generate_series(1, (1 + floor(random() * 3))::INTEGER) as ver,
        (1 + floor(random() * 3))::INTEGER as max_ver
) v;

DO $$ BEGIN RAISE NOTICE 'Course versions inserted: %', (SELECT COUNT(*) FROM course_version); END $$;

-- ============================================================================
-- 13. COURSE_CONTENT (3-10 modules per version)
-- ============================================================================

INSERT INTO course_content (
    course_version_id, parent_id, content_type, 
    order_index, duration, is_preview, created_at
)
SELECT 
    cv.id,
    NULL,
    (ARRAY['VIDEO', 'ARTICLE', 'INTERACTIVE', 'EXERCISE']::content_type_enum[])[1 + floor(random() * 4)::INTEGER],
    ROW_NUMBER() OVER (PARTITION BY cv.id ORDER BY random()),
    (300 + random() * 3300)::INTEGER,
    random() < 0.18,
    cv.created_at
FROM course_version cv
CROSS JOIN generate_series(1, (3 + floor(random() * 7))::INTEGER) m;

DO $$ BEGIN RAISE NOTICE 'Course content modules inserted: %', (SELECT COUNT(*) FROM course_content); END $$;

-- Course content translations
INSERT INTO course_content_translate (course_content_id, language_id, title, description)
SELECT 
    cc.id,
    l.id,
    CASE l.language
        WHEN 'mk' THEN 'Модул ' || cc.order_index
        WHEN 'en' THEN 'Module ' || cc.order_index
        WHEN 'es' THEN 'Módulo ' || cc.order_index
        WHEN 'it' THEN 'Modulo ' || cc.order_index
        WHEN 'de' THEN 'Modul ' || cc.order_index
        WHEN 'sr' THEN 'Модул ' || cc.order_index
        WHEN 'hr' THEN 'Modul ' || cc.order_index
    END,
    'Description for module ' || cc.id
FROM course_content cc
CROSS JOIN language l;

DO $$ BEGIN RAISE NOTICE 'Course content translations inserted: %', (SELECT COUNT(*) FROM course_content_translate); END $$;

-- ============================================================================
-- 14. COURSE_LECTURE (3-12 lectures per module)
-- ============================================================================

INSERT INTO course_lecture (
    course_content_id, order_index, duration, 
    video_url, is_preview, created_at
)
SELECT 
    cc.id,
    ROW_NUMBER() OVER (PARTITION BY cc.id ORDER BY random()),
    (120 + random() * 1680)::INTEGER,
    'https://cdn.shifter.mk/videos/' || md5(random()::text || cc.id::text) || '.mp4',
    random() < 0.12,
    cc.created_at
FROM course_content cc
CROSS JOIN generate_series(1, (3 + floor(random() * 9))::INTEGER) lec;

DO $$ BEGIN RAISE NOTICE 'Course lectures inserted: %', (SELECT COUNT(*) FROM course_lecture); END $$;

-- Lecture translations
INSERT INTO course_lecture_translate (course_lecture_id, language_id, title, description, transcript)
SELECT 
    cl.id,
    l.id,
    CASE l.language
        WHEN 'mk' THEN 'Лекција ' || cl.order_index
        WHEN 'en' THEN 'Lecture ' || cl.order_index
        WHEN 'es' THEN 'Lección ' || cl.order_index
        WHEN 'it' THEN 'Lezione ' || cl.order_index
        WHEN 'de' THEN 'Vorlesung ' || cl.order_index
        WHEN 'sr' THEN 'Лекција ' || cl.order_index
        WHEN 'hr' THEN 'Predavanje ' || cl.order_index
    END,
    'Lecture description ' || cl.id,
    'Transcript for lecture ' || cl.id
FROM course_lecture cl
CROSS JOIN language l;

DO $$ BEGIN RAISE NOTICE 'Course lecture translations inserted: %', (SELECT COUNT(*) FROM course_lecture_translate); END $$;

-- ============================================================================
-- 15. COURSE_PRICE (Each course has 1-2 pricing tiers)
-- ============================================================================

INSERT INTO course_price (
    course_id, price, currency, valid_from, valid_until, is_active
)
SELECT 
    c.id,
    CASE 
        WHEN random() < 0.18 THEN 0
        ELSE (9.99 + random() * 390)::NUMERIC(10, 2)
    END,
    (ARRAY['USD', 'EUR', 'MKD'])[1 + floor(random() * 3)::INTEGER],
    CURRENT_TIMESTAMP - INTERVAL '1 year',
    CASE WHEN random() < 0.75 THEN NULL ELSE CURRENT_TIMESTAMP + INTERVAL '1 year' END,
    TRUE
FROM course c;

DO $$ BEGIN RAISE NOTICE 'Course prices inserted: %', (SELECT COUNT(*) FROM course_price); END $$;

-- ============================================================================
-- 16. BUNDLE (1000 bundles)
-- ============================================================================

INSERT INTO bundle (
    bundle_type, discount_percent, is_active, valid_from, valid_until, created_at
)
SELECT 
    (ARRAY['MANUAL', 'PERSONALIZED']::bundle_type_enum[])[1 + floor(random() * 2)::INTEGER],
    (10 + random() * 45)::INTEGER,
    random() < 0.88,
    CURRENT_TIMESTAMP - INTERVAL '6 months',
    CASE WHEN random() < 0.65 THEN NULL ELSE CURRENT_TIMESTAMP + INTERVAL '1 year' END,
    CURRENT_TIMESTAMP - (random() * INTERVAL '1 year')
FROM generate_series(1, 1000) i;

-- Manual bundles
INSERT INTO manual_bundle (bundle_id)
SELECT id FROM bundle WHERE bundle_type = 'MANUAL';

-- Personalized bundles
INSERT INTO personalized_bundle (bundle_id, user_id, personalized_type)
SELECT 
    b.id,
    (SELECT id FROM "user" WHERE NOT deleted ORDER BY random() LIMIT 1),
    (ARRAY['SKILL_GAP', 'INTEREST_BASED', 'CAREER_PATH', 'TRENDING']::personalized_type_enum[])[1 + floor(random() * 4)::INTEGER]
FROM bundle b
WHERE b.bundle_type = 'PERSONALIZED';

-- Bundle translations
INSERT INTO bundle_translate (bundle_id, language_id, title, description)
SELECT 
    b.id,
    l.id,
    CASE l.language
        WHEN 'mk' THEN 'Пакет ' || b.id
        WHEN 'en' THEN 'Bundle ' || b.id
        WHEN 'es' THEN 'Paquete ' || b.id
        WHEN 'it' THEN 'Pacchetto ' || b.id
        WHEN 'de' THEN 'Paket ' || b.id
        WHEN 'sr' THEN 'Пакет ' || b.id
        WHEN 'hr' THEN 'Paket ' || b.id
    END,
    'Bundle description ' || b.id
FROM bundle b
CROSS JOIN language l;

DO $$ BEGIN RAISE NOTICE 'Bundles inserted: %, Manual: %, Personalized: %, Translations: %', 
    (SELECT COUNT(*) FROM bundle),
    (SELECT COUNT(*) FROM manual_bundle),
    (SELECT COUNT(*) FROM personalized_bundle),
    (SELECT COUNT(*) FROM bundle_translate); END $$;

-- ============================================================================
-- 17. LEARNING_PATH (500 learning paths)
-- ============================================================================

INSERT INTO learning_path (
    learning_path_type, estimated_duration, is_active, created_at
)
SELECT 
    (ARRAY['MANUAL', 'PERSONALIZED']::bundle_type_enum[])[1 + floor(random() * 2)::INTEGER],
    (7200 + random() * 100800)::INTEGER,
    random() < 0.82,
    CURRENT_TIMESTAMP - (random() * INTERVAL '18 months')
FROM generate_series(1, 500) i;

-- Manual learning paths
INSERT INTO manual_learning_path (learning_path_id)
SELECT id FROM learning_path WHERE learning_path_type = 'MANUAL';

-- Personalized learning paths
INSERT INTO personalized_learning_path (learning_path_id, user_id, personalized_type)
SELECT 
    lp.id,
    (SELECT id FROM "user" WHERE NOT deleted ORDER BY random() LIMIT 1),
    (ARRAY['SKILL_GAP', 'INTEREST_BASED', 'CAREER_PATH', 'TRENDING']::personalized_type_enum[])[1 + floor(random() * 4)::INTEGER]
FROM learning_path lp
WHERE lp.learning_path_type = 'PERSONALIZED';

-- Learning path translations
INSERT INTO learning_path_translate (learning_path_id, language_id, title, description)
SELECT 
    lp.id,
    l.id,
    CASE l.language
        WHEN 'mk' THEN 'Патека за учење ' || lp.id
        WHEN 'en' THEN 'Learning Path ' || lp.id
        WHEN 'es' THEN 'Ruta de Aprendizaje ' || lp.id
        WHEN 'it' THEN 'Percorso ' || lp.id
        WHEN 'de' THEN 'Lernpfad ' || lp.id
        WHEN 'sr' THEN 'Путања ' || lp.id
        WHEN 'hr' THEN 'Put Učenja ' || lp.id
    END,
    'Learning path description ' || lp.id
FROM learning_path lp
CROSS JOIN language l;

DO $$ BEGIN RAISE NOTICE 'Learning paths inserted: %, Translations: %', 
    (SELECT COUNT(*) FROM learning_path), (SELECT COUNT(*) FROM learning_path_translate); END $$;

-- ============================================================================
-- 18. LEARNING_PATH_COURSE (2-8 courses per path)
-- ============================================================================

INSERT INTO learning_path_course (learning_path_id, course_id, order_index)
SELECT DISTINCT ON (lp.id, c.id)
    lp.id,
    c.id,
    ROW_NUMBER() OVER (PARTITION BY lp.id ORDER BY random())
FROM learning_path lp
CROSS JOIN LATERAL (
    SELECT id FROM course WHERE is_published = TRUE ORDER BY random() LIMIT (2 + floor(random() * 6)::INTEGER)
) c;

DO $$ BEGIN RAISE NOTICE 'Learning path courses inserted: %', (SELECT COUNT(*) FROM learning_path_course); END $$;

-- ============================================================================
-- 19. QUIZ (1-3 quizzes per course)
-- ============================================================================

INSERT INTO quiz (
    course_id, quiz_type, passing_score, max_attempts, 
    time_limit, points_value, created_at
)
SELECT 
    c.id,
    (ARRAY['PRE_DIAGNOSTIC', 'MODULE_CHECKPOINT', 'FINAL_CERTIFICATION', 'PRACTICE']::quiz_type_enum[])[1 + floor(random() * 4)::INTEGER],
    (60 + random() * 30)::INTEGER,
    CASE WHEN random() < 0.25 THEN NULL ELSE (2 + floor(random() * 4))::INTEGER END,
    CASE WHEN random() < 0.35 THEN NULL ELSE (1800 + random() * 5400)::INTEGER END,
    (50 + random() * 450)::INTEGER,
    c.created_at + (random() * INTERVAL '30 days')
FROM course c
CROSS JOIN generate_series(1, (1 + floor(random() * 2))::INTEGER) q;

DO $$ BEGIN RAISE NOTICE 'Quizzes inserted: %', (SELECT COUNT(*) FROM quiz); END $$;

-- Quiz translations
INSERT INTO quiz_translate (quiz_id, language_id, title, description, instructions)
SELECT 
    q.id,
    l.id,
    CASE l.language
        WHEN 'mk' THEN 'Квиз ' || q.id
        WHEN 'en' THEN 'Quiz ' || q.id
        WHEN 'es' THEN 'Cuestionario ' || q.id
        WHEN 'it' THEN 'Quiz ' || q.id
        WHEN 'de' THEN 'Quiz ' || q.id
        WHEN 'sr' THEN 'Квиз ' || q.id
        WHEN 'hr' THEN 'Kviz ' || q.id
    END,
    'Quiz description ' || q.id,
    'Quiz instructions ' || q.id
FROM quiz q
CROSS JOIN language l;

DO $$ BEGIN RAISE NOTICE 'Quiz translations inserted: %', (SELECT COUNT(*) FROM quiz_translate); END $$;

-- ============================================================================
-- 20. QUESTION (5-25 per quiz)
-- ============================================================================

INSERT INTO question (
    quiz_id, question_type, difficulty, points, 
    order_index, created_at
)
SELECT 
    qz.id,
    (ARRAY['MULTIPLE_CHOICE', 'TRUE_FALSE', 'SHORT_ANSWER', 'MULTI_SELECT']::question_type_enum[])[1 + floor(random() * 4)::INTEGER],
    (ARRAY['BEGINNER', 'INTERMEDIATE', 'ADVANCED', 'EXPERT']::course_difficulty_enum[])[1 + floor(random() * 4)::INTEGER],
    (5 + random() * 25)::INTEGER,
    ROW_NUMBER() OVER (PARTITION BY qz.id ORDER BY random()),
    qz.created_at
FROM quiz qz
CROSS JOIN generate_series(1, (5 + floor(random() * 20))::INTEGER) ques;

DO $$ BEGIN RAISE NOTICE 'Questions inserted: %', (SELECT COUNT(*) FROM question); END $$;

-- Question translations
INSERT INTO question_translate (question_id, language_id, question_text, explanation)
SELECT 
    ques.id,
    l.id,
    CASE l.language
        WHEN 'mk' THEN 'Прашање ' || ques.id || ': Која е вистинската опција?'
        WHEN 'en' THEN 'Question ' || ques.id || ': What is the correct option?'
        WHEN 'es' THEN 'Pregunta ' || ques.id || ': ¿Cuál es correcta?'
        WHEN 'it' THEN 'Domanda ' || ques.id || ': Quale è corretta?'
        WHEN 'de' THEN 'Frage ' || ques.id || ': Welche ist richtig?'
        WHEN 'sr' THEN 'Питање ' || ques.id || ': Која је тачна?'
        WHEN 'hr' THEN 'Pitanje ' || ques.id || ': Koja je točna?'
    END,
    'Explanation for question ' || ques.id
FROM question ques
CROSS JOIN language l;

DO $$ BEGIN RAISE NOTICE 'Question translations inserted: %', (SELECT COUNT(*) FROM question_translate); END $$;

-- ============================================================================
-- 21. ANSWER_OPTION (2-6 per question)
-- ============================================================================

INSERT INTO answer_option (
    question_id, is_correct, order_index, created_at
)
SELECT 
    q.id,
    ROW_NUMBER() OVER (PARTITION BY q.id ORDER BY random()) = 1,
    ROW_NUMBER() OVER (PARTITION BY q.id ORDER BY random()),
    q.created_at
FROM question q
CROSS JOIN generate_series(1, (2 + floor(random() * 4))::INTEGER) opt;

DO $$ BEGIN RAISE NOTICE 'Answer options inserted: %', (SELECT COUNT(*) FROM answer_option); END $$;

-- Answer option translations
INSERT INTO answer_option_translate (answer_option_id, language_id, option_text)
SELECT 
    ao.id,
    l.id,
    CASE l.language
        WHEN 'mk' THEN 'Опција ' || ao.order_index || ' за прашање ' || ao.question_id
        WHEN 'en' THEN 'Option ' || ao.order_index || ' for question ' || ao.question_id
        WHEN 'es' THEN 'Opción ' || ao.order_index
        WHEN 'it' THEN 'Opzione ' || ao.order_index
        WHEN 'de' THEN 'Option ' || ao.order_index
        WHEN 'sr' THEN 'Опција ' || ao.order_index
        WHEN 'hr' THEN 'Opcija ' || ao.order_index
    END
FROM answer_option ao
CROSS JOIN language l;

DO $$ BEGIN RAISE NOTICE 'Answer option translations inserted: %', (SELECT COUNT(*) FROM answer_option_translate); END $$;

-- ============================================================================
-- 22. QUESTION_SKILL (Each question maps to 1-4 skills)
-- ============================================================================

INSERT INTO question_skill (question_id, skill_id, weight)
SELECT DISTINCT ON (q.id, s.id)
    q.id,
    s.id,
    (0.5 + random() * 2.5)::NUMERIC(5, 2)
FROM question q
CROSS JOIN LATERAL (
    SELECT id FROM skill ORDER BY random() LIMIT (1 + floor(random() * 3)::INTEGER)
) s;

DO $$ BEGIN RAISE NOTICE 'Question skills inserted: %', (SELECT COUNT(*) FROM question_skill); END $$;

-- ============================================================================
-- 23. ENROLLMENTS - POWER USERS (50 users with 100k-1M enrollments each)
-- ============================================================================
-- UNIQUE constraint: (user_id, course_version_id) implied by business logic

DO $$
DECLARE
    v_power_user_id BIGINT;
    v_enrollment_count INTEGER;
    v_batch_size INTEGER := 10000;
    v_total_batches INTEGER;
    v_batch INTEGER;
    v_course_versions BIGINT[];
    v_total_versions BIGINT;
BEGIN
    -- Get all available course versions
    SELECT ARRAY_AGG(id) INTO v_course_versions FROM course_version;
    v_total_versions := array_length(v_course_versions, 1);
    
    RAISE NOTICE 'Starting power user enrollment generation...';
    RAISE NOTICE 'Total course versions available: %', v_total_versions;
    
    FOR v_power_user_id IN 
        SELECT id FROM "user" WHERE NOT deleted ORDER BY id LIMIT 50
    LOOP
        -- Random enrollment count between 100k and min(1M, total_versions)
        v_enrollment_count := LEAST(100000 + (random() * 900000)::INTEGER, v_total_versions);
        v_total_batches := CEIL(v_enrollment_count::NUMERIC / v_batch_size);
        
        RAISE NOTICE 'User %: Creating % enrollments in % batches', 
            v_power_user_id, v_enrollment_count, v_total_batches;
        
        FOR v_batch IN 1..v_total_batches LOOP
            INSERT INTO enrollment (
                user_id, course_id, course_version_id, enrollment_type, enrollment_status,
                enrolled_at, expires_at, completed_at, progress_percent
            )
            SELECT DISTINCT ON (cv.id)
                v_power_user_id,
                cv.course_id,
                cv.id,
                (ARRAY['FREE', 'PAID', 'GIFTED', 'ADMIN_GRANTED']::enrollment_type_enum[])[1 + floor(random() * 4)::INTEGER],
                (ARRAY['ACTIVE', 'COMPLETED', 'EXPIRED', 'SUSPENDED']::enrollment_status_enum[])[1 + floor(random() * 4)::INTEGER],
                CURRENT_TIMESTAMP - (random() * INTERVAL '2 years'),
                CASE WHEN random() < 0.3 THEN NULL 
                     ELSE CURRENT_TIMESTAMP + (random() * INTERVAL '1 year') END,
                CASE WHEN random() < 0.25 THEN CURRENT_TIMESTAMP - (random() * INTERVAL '6 months')
                     ELSE NULL END,
                (random() * 100)::INTEGER
            FROM (
                SELECT * FROM course_version 
                WHERE id = ANY(v_course_versions)
                ORDER BY random() 
                LIMIT LEAST(v_batch_size, v_enrollment_count - (v_batch - 1) * v_batch_size)
            ) cv;
            
            IF v_batch % 5 = 0 THEN
                RAISE NOTICE '  User %: Batch % of % completed', v_power_user_id, v_batch, v_total_batches;
            END IF;
        END LOOP;
    END LOOP;
    
    RAISE NOTICE 'Power user enrollments completed';
END $$;

DO $$ BEGIN RAISE NOTICE 'Total enrollments after power users: %', (SELECT COUNT(*) FROM enrollment); END $$;

-- ============================================================================
-- 24. ENROLLMENTS - NORMAL USERS (100k users with 1-50 enrollments each)
-- ============================================================================

DO $$
DECLARE
    v_normal_user_id BIGINT;
    v_enrollment_count INTEGER;
    v_user_count INTEGER := 0;
    v_total_normal_users INTEGER := 100000;
BEGIN
    RAISE NOTICE 'Starting normal user enrollment generation (% users)...', v_total_normal_users;
    
    FOR v_normal_user_id IN 
        SELECT id FROM "user" 
        WHERE NOT deleted 
        AND id NOT IN (SELECT id FROM "user" ORDER BY id LIMIT 50)
        ORDER BY random()
        LIMIT v_total_normal_users
    LOOP
        v_enrollment_count := (1 + floor(random() * 49))::INTEGER;
        v_user_count := v_user_count + 1;
        
        INSERT INTO enrollment (
            user_id, course_id, course_version_id, enrollment_type, enrollment_status,
            enrolled_at, expires_at, completed_at, progress_percent
        )
        SELECT DISTINCT ON (cv.id)
            v_normal_user_id,
            cv.course_id,
            cv.id,
            (ARRAY['FREE', 'PAID', 'GIFTED', 'ADMIN_GRANTED']::enrollment_type_enum[])[1 + floor(random() * 4)::INTEGER],
            (ARRAY['ACTIVE', 'COMPLETED', 'EXPIRED']::enrollment_status_enum[])[1 + floor(random() * 3)::INTEGER],
            CURRENT_TIMESTAMP - (random() * INTERVAL '2 years'),
            CASE WHEN random() < 0.4 THEN NULL 
                 ELSE CURRENT_TIMESTAMP + (random() * INTERVAL '1 year') END,
            CASE WHEN random() < 0.2 THEN CURRENT_TIMESTAMP - (random() * INTERVAL '6 months')
                 ELSE NULL END,
            (random() * 100)::INTEGER
        FROM (
            SELECT * FROM course_version ORDER BY random() LIMIT v_enrollment_count
        ) cv;
        
        IF v_user_count % 5000 = 0 THEN
            RAISE NOTICE 'Normal users processed: %/%', v_user_count, v_total_normal_users;
        END IF;
    END LOOP;
    
    RAISE NOTICE 'Normal user enrollments completed';
END $$;

DO $$ BEGIN RAISE NOTICE 'Total enrollments: %', (SELECT COUNT(*) FROM enrollment); END $$;

-- ============================================================================
-- 25. USER_LEARNING_PATH (Users in learning paths)
-- ============================================================================

INSERT INTO user_learning_path (user_id, learning_path_id, status, started_at, completed_at)
SELECT DISTINCT ON (u.id, lp.id)
    u.id,
    lp.id,
    (ARRAY['NOT_STARTED', 'IN_PROGRESS', 'COMPLETED', 'ABANDONED']::learning_path_status_enum[])[1 + floor(random() * 4)::INTEGER],
    CURRENT_TIMESTAMP - (random() * INTERVAL '1 year'),
    CASE WHEN random() < 0.2 THEN CURRENT_TIMESTAMP - (random() * INTERVAL '6 months') ELSE NULL END
FROM (SELECT id FROM "user" WHERE NOT deleted ORDER BY random() LIMIT 50000) u
CROSS JOIN LATERAL (
    SELECT id FROM learning_path WHERE is_active = TRUE ORDER BY random() LIMIT (1 + floor(random() * 2)::INTEGER)
) lp;

DO $$ BEGIN RAISE NOTICE 'User learning paths inserted: %', (SELECT COUNT(*) FROM user_learning_path); END $$;

-- ============================================================================
-- 26. ORDER & PAYMENT (For paid enrollments)
-- ============================================================================

-- Create orders for paid enrollments (sample)
INSERT INTO "order" (
    user_id, total_amount, currency, order_status, created_at
)
SELECT 
    e.user_id,
    (cp.price * (1 - COALESCE(b.discount_percent, 0) / 100.0))::NUMERIC(10, 2),
    cp.currency,
    (ARRAY['PENDING', 'PROCESSING', 'COMPLETED', 'CANCELLED']::order_status_enum[])[1 + floor(random() * 4)::INTEGER],
    e.enrolled_at
FROM (
    SELECT DISTINCT ON (user_id, course_id) * 
    FROM enrollment 
    WHERE enrollment_type = 'PAID' 
    ORDER BY user_id, course_id, id
    LIMIT 1000000
) e
JOIN course c ON e.course_id = c.id
LEFT JOIN course_price cp ON c.id = cp.course_id AND cp.is_active = TRUE
LEFT JOIN bundle b ON random() < 0.15;

DO $$ BEGIN RAISE NOTICE 'Orders inserted: %', (SELECT COUNT(*) FROM "order"); END $$;

-- Order details
INSERT INTO order_details (order_id, course_id, bundle_id, price, quantity)
SELECT 
    o.id,
    e.course_id,
    CASE WHEN random() < 0.15 THEN (SELECT id FROM bundle WHERE is_active = TRUE ORDER BY random() LIMIT 1) ELSE NULL END,
    o.total_amount,
    1
FROM "order" o
JOIN enrollment e ON e.user_id = o.user_id
WHERE e.enrollment_type = 'PAID'
LIMIT 1000000;

DO $$ BEGIN RAISE NOTICE 'Order details inserted: %', (SELECT COUNT(*) FROM order_details); END $$;

-- Payments
INSERT INTO payment (
    order_id, amount, currency, payment_method, payment_status, 
    transaction_id, processed_at
)
SELECT 
    o.id,
    o.total_amount,
    o.currency,
    (ARRAY['CREDIT_CARD', 'PAYPAL', 'BANK_TRANSFER', 'STRIPE']::payment_method_enum[])[1 + floor(random() * 4)::INTEGER],
    CASE 
        WHEN o.order_status = 'COMPLETED' THEN 'COMPLETED'::payment_status_enum
        WHEN o.order_status = 'CANCELLED' THEN 'CANCELLED'::payment_status_enum
        ELSE 'PENDING'::payment_status_enum
    END,
    'TXN-' || md5(random()::text || o.id::text),
    CASE WHEN o.order_status = 'COMPLETED' THEN o.created_at + INTERVAL '5 minutes' ELSE NULL END
FROM "order" o;

DO $$ BEGIN RAISE NOTICE 'Payments inserted: %', (SELECT COUNT(*) FROM payment); END $$;

-- ============================================================================
-- 27. REVIEW (15% of completed enrollments)
-- ============================================================================

INSERT INTO review (
    enrollment_id, rating, created_at, updated_at
)
SELECT 
    e.id,
    (3 + floor(random() * 3))::INTEGER,
    e.completed_at + (random() * INTERVAL '30 days'),
    e.completed_at + (random() * INTERVAL '30 days')
FROM enrollment e
WHERE e.enrollment_status = 'COMPLETED' 
  AND e.completed_at IS NOT NULL
  AND random() < 0.15
LIMIT 2000000;

DO $$ BEGIN RAISE NOTICE 'Reviews inserted: %', (SELECT COUNT(*) FROM review); END $$;

-- ============================================================================
-- 28. CERTIFICATE (50% of completed enrollments)
-- ============================================================================

INSERT INTO certificate (
    enrollment_id, certificate_number, issued_at, verification_code
)
SELECT 
    e.id,
    'CERT-' || LPAD(e.id::TEXT, 12, '0'),
    e.completed_at,
    md5(random()::text || e.id::text)
FROM enrollment e
WHERE e.enrollment_status = 'COMPLETED'
  AND e.completed_at IS NOT NULL
  AND random() < 0.5
LIMIT 5000000;

DO $$ BEGIN RAISE NOTICE 'Certificates inserted: %', (SELECT COUNT(*) FROM certificate); END $$;

-- ============================================================================
-- 29. USER_COURSE_INTERACTION (10M interactions)
-- ============================================================================

DO $$
DECLARE
    batch_size INTEGER := 1000000;
    total_interactions INTEGER := 10000000;
    num_batches INTEGER := total_interactions / batch_size;
    current_batch INTEGER;
BEGIN
    FOR current_batch IN 1..num_batches LOOP
        INSERT INTO user_course_interaction (
            user_id, course_id, interaction_type, interaction_source, created_at
        )
        SELECT 
            (SELECT id FROM "user" WHERE NOT deleted ORDER BY random() LIMIT 1),
            (SELECT id FROM course WHERE is_published = TRUE ORDER BY random() LIMIT 1),
            (ARRAY['VIEW', 'CLICK', 'SEARCH', 'PREVIEW', 'BOOKMARK', 'SHARE']::interaction_type_enum[])[1 + floor(random() * 6)::INTEGER],
            (ARRAY['CATALOG', 'SEARCH', 'RECOMMENDATION', 'EMAIL', 'SOCIAL', 'DIRECT']::interaction_source_enum[])[1 + floor(random() * 6)::INTEGER],
            CURRENT_TIMESTAMP - (random() * INTERVAL '1 year')
        FROM generate_series(1, batch_size) i;
        
        RAISE NOTICE 'User interactions batch % of % completed', current_batch, num_batches;
    END LOOP;
END $$;

DO $$ BEGIN RAISE NOTICE 'User course interactions inserted: %', (SELECT COUNT(*) FROM user_course_interaction); END $$;

-- ============================================================================
-- 30. COURSE_SIMILARITY (200k similarity pairs)
-- ============================================================================

INSERT INTO course_similarity (
    course_id_a, course_id_b, similarity_type, similarity_score, created_at
)
SELECT DISTINCT ON (c1.id, c2.id)
    c1.id,
    c2.id,
    (ARRAY['CONTENT', 'COLLABORATIVE', 'HYBRID']::similarity_type_enum[])[1 + floor(random() * 3)::INTEGER],
    (0.3 + random() * 0.7)::NUMERIC(5, 4),
    CURRENT_TIMESTAMP - (random() * INTERVAL '6 months')
FROM (SELECT id FROM course ORDER BY random() LIMIT 10000) c1
CROSS JOIN LATERAL (
    SELECT id FROM course WHERE id != c1.id ORDER BY random() LIMIT 20
) c2;

DO $$ BEGIN RAISE NOTICE 'Course similarities inserted: %', (SELECT COUNT(*) FROM course_similarity); END $$;

-- ============================================================================
-- 31. LECTURE_PROGRESS (Sample from active enrollments)
-- ============================================================================

INSERT INTO lecture_progress (
    enrollment_id, course_lecture_id, completed, last_position, 
    completed_at, created_at, updated_at
)
SELECT 
    e.id,
    cl.id,
    random() < 0.35,
    (random() * cl.duration)::INTEGER,
    CASE WHEN random() < 0.35 THEN CURRENT_TIMESTAMP - (random() * INTERVAL '30 days') ELSE NULL END,
    e.enrolled_at,
    CURRENT_TIMESTAMP - (random() * INTERVAL '7 days')
FROM (
    SELECT * FROM enrollment 
    WHERE enrollment_status IN ('ACTIVE', 'COMPLETED') 
    ORDER BY random() 
    LIMIT 2000000
) e
CROSS JOIN LATERAL (
    SELECT cl.id, cl.duration
    FROM course_lecture cl
    JOIN course_content cc ON cl.course_content_id = cc.id
    WHERE cc.course_version_id = e.course_version_id
    ORDER BY random()
    LIMIT (1 + floor(random() * 9))::INTEGER
) cl;

DO $$ BEGIN RAISE NOTICE 'Lecture progress inserted: %', (SELECT COUNT(*) FROM lecture_progress); END $$;

-- ============================================================================
-- 32. QUIZ_ATTEMPT (25% of enrollments attempt quizzes)
-- ============================================================================

INSERT INTO quiz_attempt (
    enrollment_id, quiz_id, attempt_number, started_at, submited_at,
    status, score, total_points, earned_points, passed
)
SELECT 
    e.id,
    q.id,
    (1 + floor(random() * 3))::INTEGER,
    e.enrolled_at + (random() * INTERVAL '60 days'),
    CASE WHEN random() < 0.75 
         THEN e.enrolled_at + (random() * INTERVAL '60 days') + INTERVAL '45 minutes'
         ELSE NULL END,
    (ARRAY['IN_PROGRESS', 'SUBMITTED', 'GRADED', 'ABANDONED']::quiz_attempt_status_enum[])[1 + floor(random() * 4)::INTEGER],
    CASE WHEN random() < 0.75 THEN (40 + floor(random() * 60))::INTEGER ELSE NULL END,
    q.points_value,
    CASE WHEN random() < 0.75 THEN (q.points_value * random())::INTEGER ELSE NULL END,
    random() < 0.62
FROM (
    SELECT * FROM enrollment ORDER BY random() LIMIT 5000000
) e
JOIN quiz q ON q.course_id = e.course_id
WHERE random() < 0.25;

DO $$ BEGIN RAISE NOTICE 'Quiz attempts inserted: %', (SELECT COUNT(*) FROM quiz_attempt); END $$;

-- ============================================================================
-- 33. QUIZ_ANSWER
-- ============================================================================

INSERT INTO quiz_answer (
    quiz_attempt_id, question_id, answer_option_id, 
    confidence_value, is_correct
)
SELECT 
    qa.id,
    ques.id,
    (SELECT ao.id FROM answer_option ao WHERE ao.question_id = ques.id ORDER BY random() LIMIT 1),
    (50 + random() * 50)::NUMERIC(5, 2),
    random() < 0.62
FROM (
    SELECT * FROM quiz_attempt WHERE status IN ('SUBMITTED', 'GRADED') ORDER BY random() LIMIT 3000000
) qa
JOIN quiz q ON qa.quiz_id = q.id
CROSS JOIN LATERAL (
    SELECT id FROM question WHERE quiz_id = q.id ORDER BY random() LIMIT (5 + floor(random() * 10)::INTEGER)
) ques;

DO $$ BEGIN RAISE NOTICE 'Quiz answers inserted: %', (SELECT COUNT(*) FROM quiz_answer); END $$;

-- ============================================================================
-- 34. USER_SKILL (From quiz attempts)
-- ============================================================================

INSERT INTO user_skill (
    user_id, skill_id, verified, proficiency, score_percent, created_at
)
SELECT DISTINCT ON (e.user_id, qs.skill_id)
    e.user_id,
    qs.skill_id,
    random() < 0.28,
    CASE 
        WHEN AVG(qans.is_correct::INTEGER) > 0.8 THEN 'ADVANCED'::proficiency_level_enum
        WHEN AVG(qans.is_correct::INTEGER) > 0.6 THEN 'INTERMEDIATE'::proficiency_level_enum
        ELSE 'BEGINNER'::proficiency_level_enum
    END,
    (AVG(qans.is_correct::INTEGER) * 100)::NUMERIC(5, 2),
    MIN(e.enrolled_at)
FROM enrollment e
JOIN quiz_attempt qat ON qat.enrollment_id = e.id
JOIN quiz_answer qans ON qans.quiz_attempt_id = qat.id
JOIN question ques ON qans.question_id = ques.id
JOIN question_skill qs ON qs.question_id = ques.id
WHERE qat.status = 'GRADED'
GROUP BY e.user_id, qs.skill_id
LIMIT 10000000;

DO $$ BEGIN RAISE NOTICE 'User skills inserted: %', (SELECT COUNT(*) FROM user_skill); END $$;

-- ============================================================================
-- 35. USER_SKILL_SNAPSHOT (From quiz attempts)
-- ============================================================================

INSERT INTO user_skill_snapshot (
    user_id, skill_id, quiz_attempt_id, weight_percent, 
    confidence_avg, snapshot_type
)
SELECT 
    e.user_id,
    qs.skill_id,
    qat.id,
    AVG(qs.weight)::NUMERIC(5, 2),
    AVG(qans.confidence_value)::NUMERIC(5, 2),
    CASE q.quiz_type
        WHEN 'PRE_DIAGNOSTIC' THEN 'PRE_DIAGNOSTIC'::snapshot_type_enum
        WHEN 'MODULE_CHECKPOINT' THEN 'CHECKPOINT'::snapshot_type_enum
        WHEN 'FINAL_CERTIFICATION' THEN 'FINAL'::snapshot_type_enum
        ELSE 'CHECKPOINT'::snapshot_type_enum
    END
FROM (SELECT * FROM quiz_attempt WHERE status = 'GRADED' ORDER BY random() LIMIT 2000000) qat
JOIN enrollment e ON qat.enrollment_id = e.id
JOIN quiz q ON qat.quiz_id = q.id
JOIN quiz_answer qans ON qans.quiz_attempt_id = qat.id
JOIN question ques ON qans.question_id = ques.id
JOIN question_skill qs ON qs.question_id = ques.id
GROUP BY e.user_id, qs.skill_id, qat.id, q.quiz_type;

DO $$ BEGIN RAISE NOTICE 'User skill snapshots inserted: %', (SELECT COUNT(*) FROM user_skill_snapshot); END $$;

-- ============================================================================
-- FINAL SUMMARY & ANALYSIS
-- ============================================================================

DO $$
DECLARE
    rec RECORD;
BEGIN
    RAISE NOTICE '============================================';
    RAISE NOTICE 'DATA GENERATION COMPLETE - FINAL SUMMARY';
    RAISE NOTICE '============================================';
    
    FOR rec IN 
        SELECT 
            'language' as table_name, COUNT(*) as row_count FROM language
        UNION ALL SELECT 'admin', COUNT(*) FROM admin
        UNION ALL SELECT 'expert', COUNT(*) FROM expert
        UNION ALL SELECT 'user', COUNT(*) FROM "user"
        UNION ALL SELECT 'verification_token', COUNT(*) FROM verification_token
        UNION ALL SELECT 'meeting_email_reminder', COUNT(*) FROM meeting_email_reminder
        UNION ALL SELECT 'topic', COUNT(*) FROM topic
        UNION ALL SELECT 'topic_translate', COUNT(*) FROM topic_translate
        UNION ALL SELECT 'skill', COUNT(*) FROM skill
        UNION ALL SELECT 'skill_translate', COUNT(*) FROM skill_translate
        UNION ALL SELECT 'course', COUNT(*) FROM course
        UNION ALL SELECT 'course_translate', COUNT(*) FROM course_translate
        UNION ALL SELECT 'course_topic', COUNT(*) FROM course_topic
        UNION ALL SELECT 'course_version', COUNT(*) FROM course_version
        UNION ALL SELECT 'course_content', COUNT(*) FROM course_content
        UNION ALL SELECT 'course_content_translate', COUNT(*) FROM course_content_translate
        UNION ALL SELECT 'course_lecture', COUNT(*) FROM course_lecture
        UNION ALL SELECT 'course_lecture_translate', COUNT(*) FROM course_lecture_translate
        UNION ALL SELECT 'course_price', COUNT(*) FROM course_price
        UNION ALL SELECT 'bundle', COUNT(*) FROM bundle
        UNION ALL SELECT 'manual_bundle', COUNT(*) FROM manual_bundle
        UNION ALL SELECT 'personalized_bundle', COUNT(*) FROM personalized_bundle
        UNION ALL SELECT 'bundle_translate', COUNT(*) FROM bundle_translate
        UNION ALL SELECT 'learning_path', COUNT(*) FROM learning_path
        UNION ALL SELECT 'manual_learning_path', COUNT(*) FROM manual_learning_path
        UNION ALL SELECT 'personalized_learning_path', COUNT(*) FROM personalized_learning_path
        UNION ALL SELECT 'learning_path_translate', COUNT(*) FROM learning_path_translate
        UNION ALL SELECT 'learning_path_course', COUNT(*) FROM learning_path_course
        UNION ALL SELECT 'user_learning_path', COUNT(*) FROM user_learning_path
        UNION ALL SELECT 'quiz', COUNT(*) FROM quiz
        UNION ALL SELECT 'quiz_translate', COUNT(*) FROM quiz_translate
        UNION ALL SELECT 'question', COUNT(*) FROM question
        UNION ALL SELECT 'question_translate', COUNT(*) FROM question_translate
        UNION ALL SELECT 'answer_option', COUNT(*) FROM answer_option
        UNION ALL SELECT 'answer_option_translate', COUNT(*) FROM answer_option_translate
        UNION ALL SELECT 'question_skill', COUNT(*) FROM question_skill
        UNION ALL SELECT 'enrollment', COUNT(*) FROM enrollment
        UNION ALL SELECT 'order', COUNT(*) FROM "order"
        UNION ALL SELECT 'order_details', COUNT(*) FROM order_details
        UNION ALL SELECT 'payment', COUNT(*) FROM payment
        UNION ALL SELECT 'review', COUNT(*) FROM review
        UNION ALL SELECT 'certificate', COUNT(*) FROM certificate
        UNION ALL SELECT 'user_course_interaction', COUNT(*) FROM user_course_interaction
        UNION ALL SELECT 'course_similarity', COUNT(*) FROM course_similarity
        UNION ALL SELECT 'lecture_progress', COUNT(*) FROM lecture_progress
        UNION ALL SELECT 'quiz_attempt', COUNT(*) FROM quiz_attempt
        UNION ALL SELECT 'quiz_answer', COUNT(*) FROM quiz_answer
        UNION ALL SELECT 'user_skill', COUNT(*) FROM user_skill
        UNION ALL SELECT 'user_skill_snapshot', COUNT(*) FROM user_skill_snapshot
        ORDER BY table_name
    LOOP
        RAISE NOTICE '% : %', RPAD(rec.table_name, 30), rec.row_count;
    END LOOP;
    
    RAISE NOTICE '============================================';
    RAISE NOTICE 'Creating indexes for performance...';
END $$;

-- ============================================================================
-- INDEX CREATION (In addition to DDL indexes)
-- ============================================================================

CREATE INDEX IF NOT EXISTS idx_enrollment_user_id ON enrollment(user_id);
CREATE INDEX IF NOT EXISTS idx_enrollment_course_id ON enrollment(course_id);
CREATE INDEX IF NOT EXISTS idx_enrollment_course_version_id ON enrollment(course_version_id);
CREATE INDEX IF NOT EXISTS idx_enrollment_status ON enrollment(enrollment_status);
CREATE INDEX IF NOT EXISTS idx_enrollment_type ON enrollment(enrollment_type);
CREATE INDEX IF NOT EXISTS idx_enrollment_enrolled_at ON enrollment(enrolled_at);

CREATE INDEX IF NOT EXISTS idx_quiz_attempt_enrollment_id ON quiz_attempt(enrollment_id);
CREATE INDEX IF NOT EXISTS idx_quiz_attempt_quiz_id ON quiz_attempt(quiz_id);
CREATE INDEX IF NOT EXISTS idx_quiz_attempt_status ON quiz_attempt(status);

CREATE INDEX IF NOT EXISTS idx_quiz_answer_attempt_id ON quiz_answer(quiz_attempt_id);
CREATE INDEX IF NOT EXISTS idx_quiz_answer_question_id ON quiz_answer(question_id);

CREATE INDEX IF NOT EXISTS idx_user_skill_user_id ON user_skill(user_id);
CREATE INDEX IF NOT EXISTS idx_user_skill_skill_id ON user_skill(skill_id);

CREATE INDEX IF NOT EXISTS idx_lecture_progress_enrollment ON lecture_progress(enrollment_id);
CREATE INDEX IF NOT EXISTS idx_lecture_progress_lecture ON lecture_progress(course_lecture_id);

CREATE INDEX IF NOT EXISTS idx_user_course_interaction_user ON user_course_interaction(user_id);
CREATE INDEX IF NOT EXISTS idx_user_course_interaction_course ON user_course_interaction(course_id);
CREATE INDEX IF NOT EXISTS idx_user_course_interaction_type ON user_course_interaction(interaction_type);

CREATE INDEX IF NOT EXISTS idx_course_similarity_a ON course_similarity(course_id_a);
CREATE INDEX IF NOT EXISTS idx_course_similarity_b ON course_similarity(course_id_b);

-- Composite indexes for common queries
CREATE INDEX IF NOT EXISTS idx_enrollment_user_status ON enrollment(user_id, enrollment_status);
CREATE INDEX IF NOT EXISTS idx_enrollment_course_status ON enrollment(course_id, enrollment_status);
CREATE INDEX IF NOT EXISTS idx_course_published_difficulty ON course(is_published, difficulty);
CREATE INDEX IF NOT EXISTS idx_course_featured ON course(is_featured) WHERE is_featured = TRUE;

DO $$ BEGIN RAISE NOTICE 'Indexes created successfully'; END $$;

-- ============================================================================
-- ANALYZE TABLES FOR QUERY OPTIMIZATION
-- ============================================================================

ANALYZE language;
ANALYZE admin;
ANALYZE expert;
ANALYZE "user";
ANALYZE verification_token;
ANALYZE meeting_email_reminder;
ANALYZE topic;
ANALYZE topic_translate;
ANALYZE skill;
ANALYZE skill_translate;
ANALYZE course;
ANALYZE course_translate;
ANALYZE course_topic;
ANALYZE course_version;
ANALYZE course_content;
ANALYZE course_content_translate;
ANALYZE course_lecture;
ANALYZE course_lecture_translate;
ANALYZE course_price;
ANALYZE bundle;
ANALYZE manual_bundle;
ANALYZE personalized_bundle;
ANALYZE bundle_translate;
ANALYZE learning_path;
ANALYZE manual_learning_path;
ANALYZE personalized_learning_path;
ANALYZE learning_path_translate;
ANALYZE learning_path_course;
ANALYZE user_learning_path;
ANALYZE quiz;
ANALYZE quiz_translate;
ANALYZE question;
ANALYZE question_translate;
ANALYZE answer_option;
ANALYZE answer_option_translate;
ANALYZE question_skill;
ANALYZE enrollment;
ANALYZE "order";
ANALYZE order_details;
ANALYZE payment;
ANALYZE review;
ANALYZE certificate;
ANALYZE user_course_interaction;
ANALYZE course_similarity;
ANALYZE lecture_progress;
ANALYZE quiz_attempt;
ANALYZE quiz_answer;
ANALYZE user_skill;
ANALYZE user_skill_snapshot;

DO $$ BEGIN 
    RAISE NOTICE '============================================';
    RAISE NOTICE 'ALL TABLES ANALYZED';
    RAISE NOTICE 'Demo data generation completed successfully!';
    RAISE NOTICE '============================================';
END $$;