RelationalDesign: OPLMS--DML.sql

INSERT INTO user_entity (id, first_name, last_name, email, password, role) VALUES
(1, 'Ana',   'Adminova',      'ana.admin@oplms.com',       'pass123', 'ADMIN'),
(2, 'Ivan',  'Instruktorov',  'ivan.instructor@oplms.com', 'pass123', 'INSTRUCTOR'),
(3, 'Elena', 'Instruktorka',  'elena.instructor@oplms.com','pass123', 'INSTRUCTOR'),
(4, 'Marko', 'Userov',        'marko.user@oplms.com',      'pass123', 'USER'),
(5, 'Sara',  'Userova',       'sara.user@oplms.com',       'pass123', 'USER'),
(6, 'Dalia', 'Userova',       'dalia.user@oplms.com',      'pass123', 'USER');

INSERT INTO administrators (id) VALUES (1);

INSERT INTO instructors (id) VALUES
(2),
(3);

INSERT INTO users (id) VALUES
(4),
(5),
(6);

INSERT INTO subscription_plan (plan_id, name, price, duration_months, description, access_type) VALUES
(1, 'Basic',      9.99,   1,  'Access to basic courses',       'BASIC'),
(2, 'Pro',        19.99,  1,  'Access to all courses',         'FULL'),
(3, 'Annual Pro', 199.99, 12, 'Full access for 12 months',     'FULL');

INSERT INTO user_subscription (subscription_id, user_id, plan_id, start_date, end_date, status) VALUES
(1, 4, 2, DATE '2025-12-01', DATE '2025-12-31', 'ACTIVE'),
(2, 5, 1, DATE '2025-12-05', DATE '2026-01-05', 'ACTIVE');

INSERT INTO payment (payment_id, user_id, subscription_id, amount) VALUES
(1, 4, 1, 19.99),
(2, 5, 2,  9.99);


INSERT INTO support_ticket (ticket_id, user_id, admin_id, subject, description, status) VALUES
(1, 4, 1, 'Payment issue', 'My payment shows pending.', 'OPEN'),
(2, 5, 1, 'Course access', 'I cannot access one course.', 'IN_PROGRESS');


INSERT INTO category (category_id, name, description) VALUES
(1, 'Programming', 'Programming related courses'),
(2, 'Databases',   'SQL, relational design and normalization'),
(3, 'Networks',    'Networking fundamentals and security');


INSERT INTO course (course_id, name, price, status, instructor_id) VALUES
(1, 'SQL Fundamentals',0.00,  'ACTIVE', 2),
(2, 'Database Design (ER -> Relational)',29.99,'ACTIVE', 2),
(3, 'Network Security Basics',19.99, 'ACTIVE', 3);


INSERT INTO course_category (course_id, category_id) VALUES
(1, 2),
(2, 2),
(3, 3);


INSERT INTO module (module_id, course_id, title, description) VALUES
(1, 1, 'Intro to SQL', 'Basics of SELECT, WHERE, ORDER BY'),
(2, 1, 'Joins', 'INNER/LEFT JOIN and practice'),
(3, 2, 'ER Modeling', 'Entities, relationships, cardinalities'),
(4, 2, 'Relational Mapping', 'Transform ER to tables, PK/FK'),
(5, 3, 'Security Concepts', 'CIA, threats, authentication');


INSERT INTO lesson (lesson_id, module_id, title, material) VALUES
(1, 1, 'SQL SELECT basics',         'Intro material for SELECT'),
(2, 2, 'JOIN examples',             'Practice joins with examples'),
(3, 3, 'ER entities and attributes','How to identify entities'),
(4, 4, 'Mapping rules',             'Rules for PK/FK and relations'),
(5, 5, 'Security overview',         'Basic security principles');


INSERT INTO quiz (quiz_id, total_points, passing_score, lesson_id) VALUES
(1, 100, 60, 1),
(2,  50, 30, 3),
(3,  80, 50, 5);


INSERT INTO enrollment (enrollment_id, user_id, course_id, enroll_date, completion_status, progress_percentage) VALUES
(1, 4, 2, DATE '2025-12-10', 'IN_PROGRESS', 70),
(2, 5, 1, DATE '2025-12-12', 'IN_PROGRESS', 40);


INSERT INTO quiz_attempt (attempt_id, score, attempt_date, user_id, quiz_id) VALUES
(1, 40, DATE '2025-12-15', 4, 2),
(2, 45, DATE '2025-12-16', 5, 1);


UPDATE enrollment
SET completion_status = 'COMPLETED',
    progress_percentage = 100
WHERE enrollment_id = 1;

UPDATE enrollment
SET completion_status = 'IN_PROGRESS',
    progress_percentage = 40
WHERE enrollment_id = 2;


INSERT INTO certificate (certificate_id, enrollment_id, issue_date, certificate_code, status) VALUES
(1, 1, DATE '2025-12-20', 'CERT-DB-2025-0001', 'ISSUED');