RelationalDesign: dml.sql

-- =============================================================================
-- DML — synthetic dataset, generate_series only (no PL/pgSQL loops)
-- Approx. row counts:
--   language                       3
--   account                  500,000
--   "user"                   450,000
--   expert                    50,000
--   course                     2,000
--   course_version             6,000
--   course_translate           6,000
--   course_content            36,000
--   course_content_translate 108,000
--   course_lecture           288,000
--   course_lecture_translate 864,000
--   enrollment               800,000
--   payment                  800,000
--   review                  ~160,000
--   user_course_progress    ~960,000
--   verification_token       150,000
--   tag                          500
--   tag_translate              1,500
--   user_tag                 900,000
--   course_tag                 6,000
--   meeting_email_reminder   250,000
--   user_favorite_course     900,000
--   -------------------------------------
--   TOTAL                   ~7,200,000 rows
--
-- All IDs are explicit and deterministic so cross-table references can be
-- computed arithmetically; identity sequences are re-synced at the end.
-- Run after 01_ddl_v2.sql.
-- =============================================================================

SET synchronous_commit = off;

TRUNCATE TABLE
    user_favorite_course, meeting_email_reminder, course_tag, user_tag,
    tag_translate, tag, verification_token, user_course_progress, review,
    payment, enrollment, course_lecture_translate, course_lecture,
    course_content_translate, course_content, course_translate, course_version,
    course, expert, "user", account, language
    RESTART IDENTITY CASCADE;

-- -----------------------------------------------------------------------------
-- 22. language — 3 rows
-- -----------------------------------------------------------------------------
INSERT INTO language (id, value) VALUES
                                     (1, 'mk'),
                                     (2, 'en'),
                                     (3, 'sq');

-- -----------------------------------------------------------------------------
-- 1. account — 500,000 rows
--    1        .. 450,000 -> "user"
--    450,001  .. 500,000 -> expert
-- -----------------------------------------------------------------------------
INSERT INTO account (id, email, password_hash, name)
SELECT g,
       'account' || g || '@shifter.mk',
       md5('pwd::' || g),
       'Account Holder ' || g
FROM generate_series(1, 500000) AS g;

-- -----------------------------------------------------------------------------
-- 2. "user" — 450,000 rows
-- -----------------------------------------------------------------------------
INSERT INTO "user" (user_id, name, email, password_hash, login_provider,
                    is_verified, is_profile_complete, has_used_free_consultation,
                    work_position, company_size, points, account_id)
SELECT g,
       'Account Holder ' || g,
       'account' || g || '@shifter.mk',
       md5('pwd::' || g),
       (ARRAY['local','google']::login_provider[])[1 + (g % 2)],
       (g % 10) <> 0,
       (g % 4)  <> 0,
       (g % 7)  =  0,
       (ARRAY['CEO','CTO','Product Manager','Team Lead','Software Engineer',
           'Marketing Specialist','HR Manager','Accountant'])[1 + (g % 8)],
       (ARRAY['freelance','micro','small','medium','mid_market','enterprise','other']
           ::company_size[])[1 + (g % 7)],
       (g * 37) % 5000,
       g
FROM generate_series(1, 450000) AS g;

-- -----------------------------------------------------------------------------
-- 3. expert — 50,000 rows
-- -----------------------------------------------------------------------------
INSERT INTO expert (expert_id, account_id)
SELECT g, 450000 + g
FROM generate_series(1, 50000) AS g;

-- -----------------------------------------------------------------------------
-- 8. course — 2,000 rows
-- -----------------------------------------------------------------------------
INSERT INTO course (course_id, color, difficulty, duration_minutes, image_url, price)
SELECT g,
       '#' || lpad(to_hex((g * 7919) % 16777216), 6, '0'),
       (ARRAY['beginner','intermediate','advanced','expert']::difficulty[])[1 + (g % 4)],
       60 + (g % 24) * 15,
       'https://cdn.shifter.mk/courses/' || g || '/cover.webp',
       (20 + (g % 40) * 5)::numeric(12,2)
FROM generate_series(1, 2000) AS g;

-- -----------------------------------------------------------------------------
-- 7. course_version — 6,000 rows (3 per course; version 3 is the active one)
--    course_version_id = (course_id - 1) * 3 + version_number
-- -----------------------------------------------------------------------------
INSERT INTO course_version (course_version_id, version_number, created_at, is_active, course_id)
SELECT (c - 1) * 3 + v,
       v,
       DATE '2022-01-01' + ((c % 700) + v * 30),
       (v = 3),
       c
FROM generate_series(1, 2000) AS c
         CROSS JOIN generate_series(1, 3) AS v;

-- -----------------------------------------------------------------------------
-- 9. course_translate — 6,000 rows (2,000 courses x 3 languages)
-- -----------------------------------------------------------------------------
INSERT INTO course_translate (course_translate_id, description_short, description,
                              description_long, title_short, title,
                              what_will_be_learned, course_id, language_id)
SELECT (c - 1) * 3 + l,
       'Short description for course ' || c || ' (lang ' || l || ')',
       'Description for course ' || c || ' (lang ' || l || '). '
           || repeat('Content paragraph. ', 5),
       'Long description for course ' || c || ' (lang ' || l || '). '
           || repeat('Extended content paragraph. ', 15),
       'Course ' || c,
       'Course ' || c || ' — Full Title (lang ' || l || ')',
       ARRAY['Outcome A of course ' || c,
           'Outcome B of course ' || c,
           'Outcome C of course ' || c],
       c,
       l
FROM generate_series(1, 2000) AS c
         CROSS JOIN generate_series(1, 3) AS l;

-- -----------------------------------------------------------------------------
-- 10. course_content — 36,000 rows (6 modules per version)
--     course_content_id = (course_version_id - 1) * 6 + position
-- -----------------------------------------------------------------------------
INSERT INTO course_content (course_content_id, position, course_version_id)
SELECT (cv - 1) * 6 + p, p, cv
FROM generate_series(1, 6000) AS cv
         CROSS JOIN generate_series(1, 6) AS p;

-- -----------------------------------------------------------------------------
-- 11. course_content_translate — 108,000 rows
-- -----------------------------------------------------------------------------
INSERT INTO course_content_translate (course_content_translate_id, title,
                                      course_content_id, language_id)
SELECT (cc - 1) * 3 + l,
       'Module ' || (((cc - 1) % 6) + 1) || ' (lang ' || l || ')',
       cc,
       l
FROM generate_series(1, 36000) AS cc
         CROSS JOIN generate_series(1, 3) AS l;

-- -----------------------------------------------------------------------------
-- 12. course_lecture — 288,000 rows (8 lectures per module)
--     course_lecture_id = (course_content_id - 1) * 8 + position
-- -----------------------------------------------------------------------------
INSERT INTO course_lecture (course_lecture_id, position, duration_minutes,
                            content_type, course_content_id)
SELECT (cc - 1) * 8 + p,
       p,
       5 + ((cc + p) % 12) * 5,
       (ARRAY['text','file','video','quiz']::content_type[])[1 + ((cc + p) % 4)],
       cc
FROM generate_series(1, 36000) AS cc
         CROSS JOIN generate_series(1, 8) AS p;

-- -----------------------------------------------------------------------------
-- 13. course_lecture_translate — 864,000 rows
-- -----------------------------------------------------------------------------
INSERT INTO course_lecture_translate (course_lecture_translate_id, content_file_name,
                                      content_text, description, title,
                                      course_lecture_id, language_id)
SELECT (cl.course_lecture_id - 1) * 3 + l,
       CASE WHEN cl.content_type IN ('file','video')
                THEN 'lecture_' || cl.course_lecture_id || '_' || l
               || CASE WHEN cl.content_type = 'video' THEN '.mp4' ELSE '.pdf' END
           END,
       CASE WHEN cl.content_type IN ('text','quiz')
                THEN 'Body text for lecture ' || cl.course_lecture_id
                         || ' (lang ' || l || '). ' || repeat('Lorem ipsum dolor sit amet. ', 8)
           END,
       'Description of lecture ' || cl.course_lecture_id || ' (lang ' || l || ')',
       'Lecture ' || cl.position || ' (lang ' || l || ')',
       cl.course_lecture_id,
       l
FROM course_lecture cl
         CROSS JOIN generate_series(1, 3) AS l;

-- -----------------------------------------------------------------------------
-- 4. enrollment — 800,000 rows
--    user_id           = ((e - 1) % 450000) + 1
--    k                 = (e - 1) / 450000          (0 or 1 -> 1st / 2nd enrollment)
--    course_version_id = ((user_slot * 7919 + k * 3001) % 6000) + 1
--    The +3001 offset guarantees the (user_id, course_version_id) pair is unique.
-- -----------------------------------------------------------------------------
INSERT INTO enrollment (enrollment_id, enrollment_status, activation_date,
                        completion_date, purchase_date, user_id, course_version_id)
SELECT e,
       CASE WHEN e % 10 < 2 THEN 'pending'
            WHEN e % 10 < 5 THEN 'active'
            ELSE 'completed' END::enrollment_status,
       CASE WHEN e % 10 >= 2
                THEN DATE '2023-01-01' + (e % 900) + (e % 5) END,
       CASE WHEN e % 10 >= 5
                THEN DATE '2023-01-01' + (e % 900) + (e % 5) + 14 + (e % 60) END,
       DATE '2023-01-01' + (e % 900),
       ((e - 1) % 450000) + 1,
       (((((e - 1) % 450000)::bigint * 7919) + ((e - 1) / 450000) * 3001) % 6000) + 1
FROM generate_series(1, 800000) AS e;

-- -----------------------------------------------------------------------------
-- 5. payment — 800,000 rows (payment_id mirrors enrollment_id)
-- -----------------------------------------------------------------------------
INSERT INTO payment (payment_id, amount, payment_date, payment_method,
                     payment_status, enrollment_id)
SELECT e.enrollment_id,
       c.price,
       e.purchase_date,
       (ARRAY['card','paypal','casys']::payment_method[])[1 + (e.enrollment_id % 3)],
       CASE WHEN e.enrollment_status = 'pending' THEN 'pending'
            WHEN e.enrollment_id % 50 = 0        THEN 'failed'
            ELSE 'completed' END::payment_status,
       e.enrollment_id
FROM enrollment e
         JOIN course_version cv ON cv.course_version_id = e.course_version_id
         JOIN course c          ON c.course_id = cv.course_id;

-- -----------------------------------------------------------------------------
-- 6. review — ~160,000 rows (completed enrollments with an even id)
-- -----------------------------------------------------------------------------
INSERT INTO review (review_id, rating, comment, review_date, enrollment_id)
SELECT e.enrollment_id,
       1 + (e.enrollment_id % 5),
       'Review text for enrollment ' || e.enrollment_id || '. '
           || (ARRAY['Very useful.','Solid material.','Could be deeper.',
           'Great instructor.','Too basic for me.'])[1 + (e.enrollment_id % 5)],
       e.completion_date + 3,
       e.enrollment_id
FROM enrollment e
WHERE e.enrollment_status = 'completed'
  AND e.enrollment_id % 2 = 0;

-- -----------------------------------------------------------------------------
-- 14. user_course_progress — ~960,000 rows
--     First 3 lectures of each non-pending enrollment among ids 1..400,000.
--     Lecture id of module 1 of a version: (course_version_id - 1) * 48 + p
-- -----------------------------------------------------------------------------
INSERT INTO user_course_progress (user_course_progress_id, is_completed,
                                  completed_at, course_lecture_id, enrollment_id)
SELECT (e.enrollment_id - 1) * 3 + p,
       (p < 3),
       CASE WHEN p < 3
                THEN (e.activation_date + p)::timestamp + interval '9 hours' END,
       (e.course_version_id - 1) * 48 + p,
       e.enrollment_id
FROM enrollment e
         CROSS JOIN generate_series(1, 3) AS p
WHERE e.enrollment_id <= 400000
  AND e.enrollment_status <> 'pending';

-- -----------------------------------------------------------------------------
-- 15. verification_token — 150,000 rows
-- -----------------------------------------------------------------------------
INSERT INTO verification_token (verification_token_uuid, expires_at, created_at, user_id)
SELECT gen_random_uuid(),
       TIMESTAMP '2024-01-01 00:00:00' + (g % 500) * interval '1 day' + interval '24 hours',
       TIMESTAMP '2024-01-01 00:00:00' + (g % 500) * interval '1 day',
       g
FROM generate_series(1, 450000) AS g
WHERE g % 3 = 0;

-- -----------------------------------------------------------------------------
-- 16. tag — 500 rows
-- -----------------------------------------------------------------------------
INSERT INTO tag (tag_id, tag_type)
SELECT g, (ARRAY['skill','topic']::tag_type[])[1 + (g % 2)]
FROM generate_series(1, 500) AS g;

-- -----------------------------------------------------------------------------
-- 17. tag_translate — 1,500 rows
-- -----------------------------------------------------------------------------
INSERT INTO tag_translate (tag_translate_id, value, tag_id, language_id)
SELECT (t - 1) * 3 + l,
       'tag_' || t || '_lang_' || l,
       t,
       l
FROM generate_series(1, 500) AS t
         CROSS JOIN generate_series(1, 3) AS l;

-- -----------------------------------------------------------------------------
-- 18. user_tag — 900,000 rows (2 distinct tags per user)
-- -----------------------------------------------------------------------------
INSERT INTO user_tag (tag_id, user_id)
SELECT ((u * 7 + k * 13) % 500) + 1, u
FROM generate_series(1, 450000) AS u
         CROSS JOIN generate_series(1, 2) AS k;

-- -----------------------------------------------------------------------------
-- 19. course_tag — 6,000 rows (3 distinct tags per course)
-- -----------------------------------------------------------------------------
INSERT INTO course_tag (tag_id, course_id)
SELECT ((c * 11 + k * 17) % 500) + 1, c
FROM generate_series(1, 2000) AS c
         CROSS JOIN generate_series(1, 3) AS k;

-- -----------------------------------------------------------------------------
-- 20. meeting_email_reminder — 250,000 rows
-- -----------------------------------------------------------------------------
INSERT INTO meeting_email_reminder (id, meeting_at, scheduled_at, sent, meeting_link, user_id)
SELECT g,
       TIMESTAMP '2025-01-01 08:00:00' + (g % 400) * interval '1 day' + (g % 9) * interval '1 hour',
       TIMESTAMP '2025-01-01 08:00:00' + (g % 400) * interval '1 day' + (g % 9) * interval '1 hour'
           - interval '1 day',
       (g % 3) <> 0,
       'https://meet.shifter.mk/' || md5('meeting::' || g),
       ((g * 7) % 450000) + 1
FROM generate_series(1, 250000) AS g;

-- -----------------------------------------------------------------------------
-- 21. user_favorite_course — 900,000 rows (2 distinct courses per user)
-- -----------------------------------------------------------------------------
INSERT INTO user_favorite_course (user_id, course_id)
SELECT u, ((u * 13 + k * 331) % 2000) + 1
FROM generate_series(1, 450000) AS u
         CROSS JOIN generate_series(1, 2) AS k;

-- -----------------------------------------------------------------------------
-- Re-sync identity sequences after explicit-ID inserts
-- -----------------------------------------------------------------------------
SELECT setval(pg_get_serial_sequence('language',                 'id'),                          (SELECT MAX(id)                          FROM language));
SELECT setval(pg_get_serial_sequence('account',                  'id'),                          (SELECT MAX(id)                          FROM account));
SELECT setval(pg_get_serial_sequence('"user"',                   'user_id'),                     (SELECT MAX(user_id)                     FROM "user"));
SELECT setval(pg_get_serial_sequence('expert',                   'expert_id'),                   (SELECT MAX(expert_id)                   FROM expert));
SELECT setval(pg_get_serial_sequence('course',                   'course_id'),                   (SELECT MAX(course_id)                   FROM course));
SELECT setval(pg_get_serial_sequence('course_version',           'course_version_id'),           (SELECT MAX(course_version_id)           FROM course_version));
SELECT setval(pg_get_serial_sequence('course_translate',         'course_translate_id'),         (SELECT MAX(course_translate_id)         FROM course_translate));
SELECT setval(pg_get_serial_sequence('course_content',           'course_content_id'),           (SELECT MAX(course_content_id)           FROM course_content));
SELECT setval(pg_get_serial_sequence('course_content_translate', 'course_content_translate_id'), (SELECT MAX(course_content_translate_id) FROM course_content_translate));
SELECT setval(pg_get_serial_sequence('course_lecture',           'course_lecture_id'),           (SELECT MAX(course_lecture_id)           FROM course_lecture));
SELECT setval(pg_get_serial_sequence('course_lecture_translate', 'course_lecture_translate_id'), (SELECT MAX(course_lecture_translate_id) FROM course_lecture_translate));
SELECT setval(pg_get_serial_sequence('enrollment',               'enrollment_id'),               (SELECT MAX(enrollment_id)               FROM enrollment));
SELECT setval(pg_get_serial_sequence('payment',                  'payment_id'),                  (SELECT MAX(payment_id)                  FROM payment));
SELECT setval(pg_get_serial_sequence('review',                   'review_id'),                   (SELECT MAX(review_id)                   FROM review));
SELECT setval(pg_get_serial_sequence('user_course_progress',     'user_course_progress_id'),     (SELECT MAX(user_course_progress_id)     FROM user_course_progress));
SELECT setval(pg_get_serial_sequence('tag',                      'tag_id'),                      (SELECT MAX(tag_id)                      FROM tag));
SELECT setval(pg_get_serial_sequence('tag_translate',            'tag_translate_id'),            (SELECT MAX(tag_translate_id)            FROM tag_translate));
SELECT setval(pg_get_serial_sequence('meeting_email_reminder',   'id'),                          (SELECT MAX(id)                          FROM meeting_email_reminder));

VACUUM ANALYZE;