-- Drop existing tables (in reverse dependency order) DROP TABLE IF EXISTS quiz_answer CASCADE; DROP TABLE IF EXISTS quiz_attempt CASCADE; DROP TABLE IF EXISTS answer_option_translate CASCADE; DROP TABLE IF EXISTS answer_option CASCADE; DROP TABLE IF EXISTS question_translate CASCADE; DROP TABLE IF EXISTS question_skill CASCADE; DROP TABLE IF EXISTS question CASCADE; DROP TABLE IF EXISTS quiz_translate CASCADE; DROP TABLE IF EXISTS quiz CASCADE; DROP TABLE IF EXISTS user_skill CASCADE; DROP TABLE IF EXISTS user_skill_snapshot CASCADE; DROP TABLE IF EXISTS skill_translate CASCADE; DROP TABLE IF EXISTS skill CASCADE; DROP TABLE IF EXISTS lecture_progress CASCADE; DROP TABLE IF EXISTS user_course_interaction CASCADE; DROP TABLE IF EXISTS course_similarity CASCADE; DROP TABLE IF EXISTS certificate CASCADE; DROP TABLE IF EXISTS review CASCADE; DROP TABLE IF EXISTS enrollment CASCADE; DROP TABLE IF EXISTS order_details CASCADE; DROP TABLE IF EXISTS "order" CASCADE; DROP TABLE IF EXISTS payment CASCADE; DROP TABLE IF EXISTS course_price CASCADE; DROP TABLE IF EXISTS learning_path_course CASCADE; DROP TABLE IF EXISTS user_learning_path CASCADE; DROP TABLE IF EXISTS personalized_learning_path CASCADE; DROP TABLE IF EXISTS manual_learning_path CASCADE; DROP TABLE IF EXISTS learning_path_translate CASCADE; DROP TABLE IF EXISTS learning_path CASCADE; DROP TABLE IF EXISTS personalized_bundle CASCADE; DROP TABLE IF EXISTS manual_bundle CASCADE; DROP TABLE IF EXISTS bundle_translate CASCADE; DROP TABLE IF EXISTS bundle CASCADE; DROP TABLE IF EXISTS course_lecture_translate CASCADE; DROP TABLE IF EXISTS course_lecture CASCADE; DROP TABLE IF EXISTS course_content_translate CASCADE; DROP TABLE IF EXISTS course_content CASCADE; DROP TABLE IF EXISTS course_version CASCADE; DROP TABLE IF EXISTS course_translate CASCADE; DROP TABLE IF EXISTS course_topic CASCADE; DROP TABLE IF EXISTS course CASCADE; DROP TABLE IF EXISTS topic_translate CASCADE; DROP TABLE IF EXISTS topic CASCADE; DROP TABLE IF EXISTS meeting_email_reminder CASCADE; DROP TABLE IF EXISTS verification_token CASCADE; DROP TABLE IF EXISTS expert CASCADE; DROP TABLE IF EXISTS "user" CASCADE; DROP TABLE IF EXISTS admin CASCADE; DROP TABLE IF EXISTS language CASCADE; -- Drop custom types DROP TYPE IF EXISTS enrollment_status_enum CASCADE; DROP TYPE IF EXISTS enrollment_type_enum CASCADE; DROP TYPE IF EXISTS payment_method_enum CASCADE; DROP TYPE IF EXISTS payment_status_enum CASCADE; DROP TYPE IF EXISTS course_difficulty_enum CASCADE; DROP TYPE IF EXISTS content_type_enum CASCADE; DROP TYPE IF EXISTS bundle_type_enum CASCADE; DROP TYPE IF EXISTS interaction_type_enum CASCADE; DROP TYPE IF EXISTS interaction_source_enum CASCADE; DROP TYPE IF EXISTS similarity_type_enum CASCADE; DROP TYPE IF EXISTS quiz_type_enum CASCADE; DROP TYPE IF EXISTS question_type_enum CASCADE; DROP TYPE IF EXISTS quiz_attempt_status_enum CASCADE; DROP TYPE IF EXISTS learning_path_status_enum CASCADE; DROP TYPE IF EXISTS order_status_enum CASCADE; DROP TYPE IF EXISTS proficiency_level_enum CASCADE; DROP TYPE IF EXISTS snapshot_type_enum CASCADE; DROP TYPE IF EXISTS personalized_type_enum CASCADE; DROP TYPE IF EXISTS login_provider_enum CASCADE; DROP TYPE IF EXISTS company_size_enum CASCADE; -- ============================================================================ -- ENUM TYPES -- ============================================================================ CREATE TYPE login_provider_enum AS ENUM ('EMAIL', 'GOOGLE'); CREATE TYPE company_size_enum AS ENUM ('FREELANCE', 'SMALL', 'MEDIUM', 'LARGE', 'ENTERPRISE'); CREATE TYPE enrollment_status_enum AS ENUM ('ACTIVE', 'COMPLETED', 'EXPIRED', 'SUSPENDED'); CREATE TYPE enrollment_type_enum AS ENUM ('PAID', 'FREE', 'GIFTED', 'ADMIN_GRANTED'); CREATE TYPE payment_method_enum AS ENUM ('CREDIT_CARD', 'PAYPAL', 'BANK_TRANSFER', 'STRIPE', 'ADMIN_MANUAL'); CREATE TYPE payment_status_enum AS ENUM ('PENDING', 'COMPLETED', 'FAILED', 'REFUNDED', 'CANCELLED'); CREATE TYPE course_difficulty_enum AS ENUM ('BEGINNER', 'INTERMEDIATE', 'ADVANCED', 'EXPERT'); CREATE TYPE content_type_enum AS ENUM ('VIDEO', 'ARTICLE', 'QUIZ', 'INTERACTIVE', 'EXERCISE', 'RESOURCE'); CREATE TYPE bundle_type_enum AS ENUM ('MANUAL', 'PERSONALIZED'); CREATE TYPE interaction_type_enum AS ENUM ('VIEW', 'CLICK', 'SEARCH', 'PREVIEW', 'BOOKMARK', 'SHARE'); CREATE TYPE interaction_source_enum AS ENUM ('CATALOG', 'SEARCH', 'RECOMMENDATION', 'EMAIL', 'SOCIAL', 'DIRECT'); CREATE TYPE similarity_type_enum AS ENUM ('CONTENT', 'COLLABORATIVE', 'HYBRID'); CREATE TYPE quiz_type_enum AS ENUM ('PRE_DIAGNOSTIC', 'MODULE_CHECKPOINT', 'FINAL_CERTIFICATION', 'PRACTICE'); CREATE TYPE question_type_enum AS ENUM ('MULTIPLE_CHOICE', 'TRUE_FALSE', 'SHORT_ANSWER', 'ESSAY', 'MULTI_SELECT'); CREATE TYPE quiz_attempt_status_enum AS ENUM ('IN_PROGRESS', 'SUBMITTED', 'GRADED', 'ABANDONED'); CREATE TYPE learning_path_status_enum AS ENUM ('NOT_STARTED', 'IN_PROGRESS', 'COMPLETED', 'ABANDONED'); CREATE TYPE order_status_enum AS ENUM ('PENDING', 'PROCESSING', 'COMPLETED', 'CANCELLED', 'REFUNDED'); CREATE TYPE proficiency_level_enum AS ENUM ('BEGINNER', 'INTERMEDIATE', 'ADVANCED'); CREATE TYPE snapshot_type_enum AS ENUM ('PRE_DIAGNOSTIC', 'POST_DIAGNOSTIC', 'CHECKPOINT', 'FINAL'); CREATE TYPE personalized_type_enum AS ENUM ('SKILL_GAP', 'INTEREST_BASED', 'CAREER_PATH', 'TRENDING'); -- ============================================================================ -- CORE TABLES -- ============================================================================ -- Language table (referenced by many translation tables) CREATE TABLE language ( id BIGSERIAL PRIMARY KEY, language VARCHAR(10) NOT NULL UNIQUE, CONSTRAINT chk_language_code CHECK (language ~ '^[a-z]{2}(-[A-Z]{2})?$') ); CREATE INDEX idx_language_code ON language(language); -- User table CREATE TABLE "user" ( id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, password_hash VARCHAR(255), login_provider login_provider_enum NOT NULL DEFAULT 'EMAIL', verified BOOLEAN NOT NULL DEFAULT FALSE, profile_completed BOOLEAN NOT NULL DEFAULT FALSE, deleted BOOLEAN NOT NULL DEFAULT FALSE, used_free_consultation BOOLEAN NOT NULL DEFAULT FALSE, company_size company_size_enum, work_position VARCHAR(255), points INTEGER NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT chk_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$'), CONSTRAINT chk_points_non_negative CHECK (points >= 0) ); CREATE UNIQUE INDEX idx_user_email_where_user_not_deleted ON "user"(email) WHERE deleted = FALSE; CREATE INDEX idx_user_verified ON "user"(verified); -- Expert table CREATE TABLE expert ( id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, password_hash VARCHAR(255), login_provider login_provider_enum NOT NULL DEFAULT 'EMAIL', CONSTRAINT chk_expert_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$') ); CREATE INDEX idx_expert_email ON expert(email); -- Admin table CREATE TABLE admin ( id BIGSERIAL PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, CONSTRAINT chk_admin_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$') ); CREATE INDEX idx_admin_email ON admin(email); -- Verification Token CREATE TABLE verification_token ( uuid UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMP NOT NULL, CONSTRAINT chk_token_expiry CHECK (expires_at > created_at) ); CREATE INDEX idx_verification_token_user ON verification_token(user_id); CREATE INDEX idx_verification_token_expires ON verification_token(expires_at); -- Meeting Email Reminder CREATE TABLE meeting_email_reminder ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, meeting_at TIMESTAMP NOT NULL, scheduled_at TIMESTAMP NOT NULL, sent BOOLEAN NOT NULL DEFAULT FALSE, meeting_link TEXT, CONSTRAINT chk_meeting_future CHECK (meeting_at > scheduled_at) ); CREATE INDEX idx_meeting_reminder_user ON meeting_email_reminder(user_id); CREATE INDEX idx_meeting_reminder_sent ON meeting_email_reminder(sent, meeting_at); -- ============================================================================ -- COURSE STRUCTURE TABLES -- ============================================================================ -- Topic (Category) CREATE TABLE topic ( id BIGSERIAL PRIMARY KEY, slug VARCHAR(100) NOT NULL UNIQUE, CONSTRAINT chk_topic_slug CHECK (slug ~ '^[a-z0-9]+(?:-[a-z0-9]+)*$') ); CREATE INDEX idx_topic_slug ON topic(slug); -- Topic Translation CREATE TABLE topic_translate ( id BIGSERIAL PRIMARY KEY, topic_id BIGINT NOT NULL REFERENCES topic(id) ON DELETE CASCADE, language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT, name VARCHAR(255) NOT NULL, CONSTRAINT uq_topic_translate_lang UNIQUE (topic_id, language_id) ); CREATE INDEX idx_topic_translate_topic ON topic_translate(topic_id); CREATE INDEX idx_topic_translate_language ON topic_translate(language_id); -- Course CREATE TABLE course ( id BIGSERIAL PRIMARY KEY, image_url TEXT, color VARCHAR(7), difficulty course_difficulty_enum NOT NULL DEFAULT 'BEGINNER', duration_minutes INTEGER NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT chk_course_color CHECK (color ~ '^#[0-9A-Fa-f]{6}$'), CONSTRAINT chk_course_duration CHECK (duration_minutes >= 0) ); CREATE INDEX idx_course_difficulty ON course(difficulty); CREATE INDEX idx_course_created_at ON course(created_at); CREATE TABLE course_topic ( course_id BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE, topic_id BIGINT NOT NULL REFERENCES topic(id) ON DELETE CASCADE, PRIMARY KEY (course_id, topic_id) ); -- Course Translation CREATE TABLE course_translate ( id BIGSERIAL PRIMARY KEY, course_id BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE, language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT, title_short VARCHAR(100) NOT NULL, title VARCHAR(255) NOT NULL, description_short TEXT NOT NULL, description TEXT NOT NULL, description_long TEXT, what_will_be_learned TEXT, CONSTRAINT uq_course_translate_lang UNIQUE (course_id, language_id) ); CREATE INDEX idx_course_translate_course ON course_translate(course_id); CREATE INDEX idx_course_translate_language ON course_translate(language_id); -- Course Version CREATE TABLE course_version ( id BIGSERIAL PRIMARY KEY, course_id BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE, version_number INTEGER NOT NULL, creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, active BOOLEAN NOT NULL DEFAULT TRUE, CONSTRAINT uq_course_version_number UNIQUE (course_id, version_number), CONSTRAINT chk_version_positive CHECK (version_number > 0) ); CREATE INDEX idx_course_version_course ON course_version(course_id); CREATE INDEX idx_course_version_active ON course_version(course_id, active); -- Course Content (Module/Section) CREATE TABLE course_content ( id BIGSERIAL PRIMARY KEY, course_version_id BIGINT NOT NULL REFERENCES course_version(id) ON DELETE CASCADE, position INTEGER NOT NULL, CONSTRAINT uq_content_position UNIQUE (course_version_id, position), CONSTRAINT chk_content_position CHECK (position > 0) ); CREATE INDEX idx_course_content_version ON course_content(course_version_id); CREATE INDEX idx_course_content_position ON course_content(course_version_id, position); -- Course Content Translation CREATE TABLE course_content_translate ( id BIGSERIAL PRIMARY KEY, course_content_id BIGINT NOT NULL REFERENCES course_content(id) ON DELETE CASCADE, language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT, title VARCHAR(255) NOT NULL, CONSTRAINT uq_content_translate_lang UNIQUE (course_content_id, language_id) ); CREATE INDEX idx_content_translate_content ON course_content_translate(course_content_id); CREATE INDEX idx_content_translate_language ON course_content_translate(language_id); -- Course Lecture CREATE TABLE course_lecture ( id BIGSERIAL PRIMARY KEY, course_content_id BIGINT NOT NULL REFERENCES course_content(id) ON DELETE CASCADE, position INTEGER NOT NULL, duration_minutes INTEGER NOT NULL DEFAULT 0, content_type content_type_enum NOT NULL DEFAULT 'VIDEO', CONSTRAINT uq_lecture_position UNIQUE (course_content_id, position), CONSTRAINT chk_lecture_position CHECK (position > 0), CONSTRAINT chk_lecture_duration CHECK (duration_minutes >= 0) ); CREATE INDEX idx_course_lecture_content ON course_lecture(course_content_id); CREATE INDEX idx_course_lecture_position ON course_lecture(course_content_id, position); CREATE INDEX idx_course_lecture_type ON course_lecture(content_type); -- Course Lecture Translation CREATE TABLE course_lecture_translate ( id BIGSERIAL PRIMARY KEY, course_lecture_id BIGINT NOT NULL REFERENCES course_lecture(id) ON DELETE CASCADE, language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT, title VARCHAR(255) NOT NULL, description TEXT, content_file_name VARCHAR(255), content_text TEXT, CONSTRAINT uq_lecture_translate_lang UNIQUE (course_lecture_id, language_id) ); CREATE INDEX idx_lecture_translate_lecture ON course_lecture_translate(course_lecture_id); CREATE INDEX idx_lecture_translate_language ON course_lecture_translate(language_id); -- ============================================================================ -- PRICING & BUNDLE TABLES -- ============================================================================ -- Course Price CREATE TABLE course_price ( id BIGSERIAL PRIMARY KEY, course_id BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE, amount DECIMAL(10, 2) NOT NULL, discount DECIMAL(5, 2) NOT NULL DEFAULT 0.00, has_discount BOOLEAN NOT NULL DEFAULT FALSE, active BOOLEAN NOT NULL DEFAULT TRUE, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, valid_until TIMESTAMP, CONSTRAINT chk_price_amount CHECK (amount >= 0), CONSTRAINT chk_price_discount CHECK (discount >= 0 AND discount <= 100), CONSTRAINT chk_valid_until CHECK (valid_until IS NULL OR valid_until > created_at), CONSTRAINT chk_price_discount_consistency CHECK ( (has_discount = TRUE AND discount > 0) OR (has_discount = FALSE AND discount = 0) ) ); CREATE INDEX idx_course_price_course_active ON course_price(course_id, active); -- Bundle (supertype - Class Table Inheritance) CREATE TABLE bundle ( id BIGSERIAL PRIMARY KEY, type bundle_type_enum NOT NULL, base_price DECIMAL(10, 2) NOT NULL, discount_pct DECIMAL(5, 2) NOT NULL DEFAULT 0.00, final_price DECIMAL(10, 2) NOT NULL, active BOOLEAN NOT NULL DEFAULT TRUE, image_url TEXT, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, deactivated_at TIMESTAMP, CONSTRAINT chk_bundle_base_price CHECK (base_price >= 0), CONSTRAINT chk_bundle_discount CHECK (discount_pct >= 0 AND discount_pct <= 100), CONSTRAINT chk_bundle_final_price CHECK (final_price >= 0), CONSTRAINT chk_bundle_deactivated CHECK ( (active = FALSE AND deactivated_at IS NOT NULL) OR (active = TRUE AND deactivated_at IS NULL) ) ); CREATE INDEX idx_bundle_type ON bundle(type); CREATE INDEX idx_bundle_active ON bundle(active); CREATE INDEX idx_bundle_created_at ON bundle(created_at); -- Manual Bundle (subtype) CREATE TABLE manual_bundle ( id BIGINT PRIMARY KEY REFERENCES bundle(id) ON DELETE CASCADE -- CONSTRAINT chk_manual_bundle_type CHECK ((SELECT type FROM bundle WHERE id = manual_bundle.id) = 'MANUAL') ); -- Personalized Bundle (subtype) CREATE TABLE personalized_bundle ( id BIGINT PRIMARY KEY REFERENCES bundle(id) ON DELETE CASCADE, user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, type personalized_type_enum NOT NULL, generated_reason TEXT NOT NULL, reason_display_text VARCHAR(500), added_discount DECIMAL(5, 2) NOT NULL DEFAULT 0.00, active BOOLEAN NOT NULL DEFAULT TRUE, expires_at TIMESTAMP, -- CONSTRAINT chk_personalized_bundle_type CHECK ((SELECT type FROM bundle WHERE id = personalized_bundle.id) = 'PERSONALIZED'), CONSTRAINT chk_personalized_discount CHECK (added_discount >= 0 AND added_discount <= 100) ); CREATE INDEX idx_personalized_bundle_user ON personalized_bundle(user_id); CREATE INDEX idx_personalized_bundle_active ON personalized_bundle(active, expires_at); -- Bundle Translation CREATE TABLE bundle_translate ( id BIGSERIAL PRIMARY KEY, bundle_id BIGINT NOT NULL REFERENCES bundle(id) ON DELETE CASCADE, language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT, title VARCHAR(255) NOT NULL, description TEXT, CONSTRAINT uq_bundle_translate_lang UNIQUE (bundle_id, language_id) ); CREATE INDEX idx_bundle_translate_bundle ON bundle_translate(bundle_id); CREATE INDEX idx_bundle_translate_language ON bundle_translate(language_id); -- ============================================================================ -- LEARNING PATH TABLES -- ============================================================================ -- Learning Path (supertype) CREATE TABLE learning_path ( id BIGSERIAL PRIMARY KEY, discount DECIMAL(5, 2) NOT NULL DEFAULT 0.00, estimated_duration_wks INTEGER NOT NULL DEFAULT 0, difficulty course_difficulty_enum NOT NULL DEFAULT 'BEGINNER', active BOOLEAN NOT NULL DEFAULT TRUE, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, deactivated_at TIMESTAMP, CONSTRAINT chk_lp_discount CHECK (discount >= 0 AND discount <= 100), CONSTRAINT chk_lp_duration CHECK (estimated_duration_wks >= 0), CONSTRAINT chk_lp_deactivated CHECK ( (active = FALSE AND deactivated_at IS NOT NULL) OR (active = TRUE AND deactivated_at IS NULL) ) ); CREATE INDEX idx_learning_path_active ON learning_path(active); CREATE INDEX idx_learning_path_difficulty ON learning_path(difficulty); -- Manual Learning Path (subtype) CREATE TABLE manual_learning_path ( id BIGINT PRIMARY KEY REFERENCES learning_path(id) ON DELETE CASCADE ); -- Personalized Learning Path (subtype) CREATE TABLE personalized_learning_path ( id BIGINT PRIMARY KEY REFERENCES learning_path(id) ON DELETE CASCADE, user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, type personalized_type_enum NOT NULL, generated_reason TEXT NOT NULL, reason_display_text VARCHAR(500), added_discount DECIMAL(5, 2) NOT NULL DEFAULT 0.00, active BOOLEAN NOT NULL DEFAULT TRUE, expires_at TIMESTAMP, CONSTRAINT chk_plp_discount CHECK (added_discount >= 0 AND added_discount <= 100) ); CREATE INDEX idx_personalized_lp_user ON personalized_learning_path(user_id); CREATE INDEX idx_personalized_lp_active ON personalized_learning_path(active, expires_at); -- Learning Path Translation CREATE TABLE learning_path_translate ( id BIGSERIAL PRIMARY KEY, learning_path_id BIGINT NOT NULL REFERENCES learning_path(id) ON DELETE CASCADE, language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT, name VARCHAR(255) NOT NULL, description TEXT, learning_outcomes TEXT, CONSTRAINT uq_lp_translate_lang UNIQUE (learning_path_id, language_id) ); CREATE INDEX idx_lp_translate_lp ON learning_path_translate(learning_path_id); CREATE INDEX idx_lp_translate_language ON learning_path_translate(language_id); -- Learning Path Course (junction table) CREATE TABLE learning_path_course ( id BIGSERIAL PRIMARY KEY, learning_path_id BIGINT NOT NULL REFERENCES learning_path(id) ON DELETE CASCADE, course_id BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE, sequence_order INTEGER NOT NULL, CONSTRAINT uq_lp_course UNIQUE (learning_path_id, course_id), CONSTRAINT uq_lp_sequence UNIQUE (learning_path_id, sequence_order), CONSTRAINT chk_lp_sequence CHECK (sequence_order > 0) ); CREATE INDEX idx_lp_course_lp ON learning_path_course(learning_path_id); CREATE INDEX idx_lp_course_course ON learning_path_course(course_id); CREATE INDEX idx_lp_course_sequence ON learning_path_course(learning_path_id, sequence_order); -- ============================================================================ -- ENROLLMENT & PAYMENT TABLES -- ============================================================================ -- Payment CREATE TABLE payment ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, amount DECIMAL(10, 2) NOT NULL, date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, method payment_method_enum NOT NULL, status payment_status_enum NOT NULL DEFAULT 'PENDING', CONSTRAINT chk_payment_amount CHECK (amount >= 0) ); CREATE INDEX idx_payment_user ON payment(user_id); CREATE INDEX idx_payment_status ON payment(status); CREATE INDEX idx_payment_date ON payment(date); -- Enrollment CREATE TABLE enrollment ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, course_id BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE, payment_id BIGINT REFERENCES payment(id) ON DELETE SET NULL, enrollment_status enrollment_status_enum NOT NULL DEFAULT 'ACTIVE', enrollment_type enrollment_type_enum NOT NULL DEFAULT 'PAID', enrollment_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, purchase_date TIMESTAMP, activation_date TIMESTAMP, completion_date TIMESTAMP, CONSTRAINT uq_enrollment_user_course UNIQUE (user_id, course_id), CONSTRAINT chk_enrollment_dates CHECK ( purchase_date IS NULL OR purchase_date <= enrollment_date ), CONSTRAINT chk_enrollment_completion CHECK ( completion_date IS NULL OR completion_date >= enrollment_date ) ); CREATE INDEX idx_enrollment_user ON enrollment(user_id); CREATE INDEX idx_enrollment_course ON enrollment(course_id); CREATE INDEX idx_enrollment_status ON enrollment(enrollment_status); CREATE INDEX idx_enrollment_payment ON enrollment(payment_id); -- User Learning Path CREATE TABLE user_learning_path ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, learning_path_id BIGINT NOT NULL REFERENCES learning_path(id) ON DELETE CASCADE, enrolled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, completed_at TIMESTAMP, status learning_path_status_enum NOT NULL DEFAULT 'NOT_STARTED', progress DECIMAL(5, 2) NOT NULL DEFAULT 0.00, CONSTRAINT uq_user_lp UNIQUE (user_id, learning_path_id), CONSTRAINT chk_ulp_progress CHECK (progress >= 0 AND progress <= 100), CONSTRAINT chk_ulp_completion CHECK ( (status = 'COMPLETED' AND completed_at IS NOT NULL AND progress = 100) OR (status != 'COMPLETED') ) ); CREATE INDEX idx_user_lp_user ON user_learning_path(user_id); CREATE INDEX idx_user_lp_path ON user_learning_path(learning_path_id); CREATE INDEX idx_user_lp_status ON user_learning_path(status); -- Order CREATE TABLE "order" ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, status order_status_enum NOT NULL DEFAULT 'PENDING' ); CREATE INDEX idx_order_user ON "order"(user_id); CREATE INDEX idx_order_status ON "order"(status); CREATE INDEX idx_order_created_at ON "order"(created_at); -- Order Details CREATE TABLE order_details ( id BIGSERIAL PRIMARY KEY, order_id BIGINT NOT NULL REFERENCES "order"(id) ON DELETE CASCADE, course_id BIGINT REFERENCES course(id) ON DELETE SET NULL, bundle_id BIGINT REFERENCES bundle(id) ON DELETE SET NULL, price DECIMAL(10, 2) NOT NULL, discount DECIMAL(5, 2) NOT NULL DEFAULT 0.00, image_url TEXT, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT chk_order_item_type CHECK ( (course_id IS NOT NULL AND bundle_id IS NULL) OR (course_id IS NULL AND bundle_id IS NOT NULL) ), CONSTRAINT chk_order_price CHECK (price >= 0), CONSTRAINT chk_order_discount CHECK (discount >= 0 AND discount <= 100) ); CREATE INDEX idx_order_details_order ON order_details(order_id); CREATE INDEX idx_order_details_course ON order_details(course_id); CREATE INDEX idx_order_details_bundle ON order_details(bundle_id); -- ============================================================================ -- USER INTERACTION & ANALYTICS TABLES -- ============================================================================ -- User Course Interaction CREATE TABLE user_course_interaction ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, course_id BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE, type interaction_type_enum NOT NULL, date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, source interaction_source_enum NOT NULL, duration_seconds INTEGER, CONSTRAINT chk_interaction_duration CHECK (duration_seconds IS NULL OR duration_seconds >= 0) ); CREATE INDEX idx_user_interaction_user ON user_course_interaction(user_id); CREATE INDEX idx_user_interaction_course ON user_course_interaction(course_id); CREATE INDEX idx_user_interaction_type ON user_course_interaction(type); CREATE INDEX idx_user_interaction_date ON user_course_interaction(date); -- Course Similarity CREATE TABLE course_similarity ( id BIGSERIAL PRIMARY KEY, course_id_1 BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE, course_id_2 BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE, score DECIMAL(5, 4) NOT NULL, type similarity_type_enum NOT NULL, calculated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT uq_course_similarity UNIQUE (course_id_1, course_id_2, type), CONSTRAINT chk_similarity_different_courses CHECK (course_id_1 < course_id_2), CONSTRAINT chk_similarity_score CHECK (score >= 0 AND score <= 1) ); CREATE INDEX idx_similarity_course1 ON course_similarity(course_id_1); CREATE INDEX idx_similarity_course2 ON course_similarity(course_id_2); CREATE INDEX idx_similarity_score ON course_similarity(score DESC); -- Lecture Progress CREATE TABLE lecture_progress ( id BIGSERIAL PRIMARY KEY, enrollment_id BIGINT NOT NULL REFERENCES enrollment(id) ON DELETE CASCADE, course_lecture_id BIGINT NOT NULL REFERENCES course_lecture(id) ON DELETE CASCADE, completed BOOLEAN NOT NULL DEFAULT FALSE, started_ad TIMESTAMP, completed_ad TIMESTAMP, last_accessed_at TIMESTAMP, CONSTRAINT uq_lecture_progress UNIQUE (enrollment_id, course_lecture_id), CONSTRAINT chk_lecture_progress_completion CHECK ( (completed = FALSE) OR (completed = TRUE AND completed_ad IS NOT NULL) ) ); CREATE INDEX idx_lecture_progress_enrollment ON lecture_progress(enrollment_id); CREATE INDEX idx_lecture_progress_lecture ON lecture_progress(course_lecture_id); CREATE INDEX idx_lecture_progress_completed ON lecture_progress(completed); -- Review CREATE TABLE review ( id BIGSERIAL PRIMARY KEY, enrollment_id BIGINT NOT NULL REFERENCES enrollment(id) ON DELETE CASCADE, rating INTEGER NOT NULL, comment TEXT, date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT uq_review_enrollment UNIQUE (enrollment_id), CONSTRAINT chk_review_rating CHECK (rating >= 1 AND rating <= 5) ); CREATE INDEX idx_review_enrollment ON review(enrollment_id); CREATE INDEX idx_review_rating ON review(rating); CREATE INDEX idx_review_date ON review(date); -- Certificate CREATE TABLE certificate ( id BIGSERIAL PRIMARY KEY, enrollment_id BIGINT NOT NULL REFERENCES enrollment(id) ON DELETE CASCADE, course_id BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE, issue_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, pdf_url TEXT NOT NULL, certificate_number VARCHAR(50) NOT NULL UNIQUE, duration_seconds INTEGER NOT NULL, CONSTRAINT uq_certificate_enrollment UNIQUE (enrollment_id), CONSTRAINT chk_certificate_duration CHECK (duration_seconds > 0) ); CREATE INDEX idx_certificate_enrollment ON certificate(enrollment_id); CREATE INDEX idx_certificate_course ON certificate(course_id); CREATE INDEX idx_certificate_number ON certificate(certificate_number); -- ============================================================================ -- QUIZ & ASSESSMENT TABLES -- ============================================================================ -- Skill CREATE TABLE skill ( id BIGSERIAL PRIMARY KEY, slug VARCHAR(100) NOT NULL UNIQUE, show_in_radar BOOLEAN NOT NULL DEFAULT TRUE, CONSTRAINT chk_skill_slug CHECK (slug ~ '^[a-z0-9]+(?:-[a-z0-9]+)*$') ); CREATE INDEX idx_skill_slug ON skill(slug); CREATE INDEX idx_skill_radar ON skill(show_in_radar); -- Skill Translation CREATE TABLE skill_translate ( id BIGSERIAL PRIMARY KEY, skill_id BIGINT NOT NULL REFERENCES skill(id) ON DELETE CASCADE, language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT, name VARCHAR(255) NOT NULL, description TEXT, CONSTRAINT uq_skill_translate_lang UNIQUE (skill_id, language_id) ); CREATE INDEX idx_skill_translate_skill ON skill_translate(skill_id); CREATE INDEX idx_skill_translate_language ON skill_translate(language_id); -- Quiz CREATE TABLE quiz ( id BIGSERIAL PRIMARY KEY, course_id BIGINT REFERENCES course(id) ON DELETE CASCADE, course_content_id BIGINT REFERENCES course_content(id) ON DELETE CASCADE, type quiz_type_enum NOT NULL, passing_score INTEGER NOT NULL, randomize BOOLEAN NOT NULL DEFAULT FALSE, is_active BOOLEAN NOT NULL DEFAULT TRUE, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT chk_quiz_parent CHECK ( (course_id IS NOT NULL AND course_content_id IS NULL) OR (course_id IS NULL AND course_content_id IS NOT NULL) ), CONSTRAINT chk_quiz_passing_score CHECK (passing_score >= 0 AND passing_score <= 100) ); CREATE INDEX idx_quiz_course ON quiz(course_id); CREATE INDEX idx_quiz_content ON quiz(course_content_id); CREATE INDEX idx_quiz_type ON quiz(type); CREATE INDEX idx_quiz_active ON quiz(is_active); -- Quiz Translation CREATE TABLE quiz_translate ( id BIGSERIAL PRIMARY KEY, quiz_id BIGINT NOT NULL REFERENCES quiz(id) ON DELETE CASCADE, language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT, title VARCHAR(255) NOT NULL, description TEXT, CONSTRAINT uq_quiz_translate_lang UNIQUE (quiz_id, language_id) ); CREATE INDEX idx_quiz_translate_quiz ON quiz_translate(quiz_id); CREATE INDEX idx_quiz_translate_language ON quiz_translate(language_id); -- Question CREATE TABLE question ( id BIGSERIAL PRIMARY KEY, quiz_id BIGINT NOT NULL REFERENCES quiz(id) ON DELETE CASCADE, type question_type_enum NOT NULL, points INTEGER NOT NULL DEFAULT 1, position INTEGER NOT NULL, CONSTRAINT uq_question_position UNIQUE (quiz_id, position), CONSTRAINT chk_question_points CHECK (points > 0), CONSTRAINT chk_question_position CHECK (position > 0) ); CREATE INDEX idx_question_quiz ON question(quiz_id); CREATE INDEX idx_question_type ON question(type); CREATE INDEX idx_question_position ON question(quiz_id, position); -- Question Translation CREATE TABLE question_translate ( id BIGSERIAL PRIMARY KEY, question_id BIGINT NOT NULL REFERENCES question(id) ON DELETE CASCADE, language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT, question_text TEXT NOT NULL, scenario_text TEXT, CONSTRAINT uq_question_translate_lang UNIQUE (question_id, language_id) ); CREATE INDEX idx_question_translate_question ON question_translate(question_id); CREATE INDEX idx_question_translate_language ON question_translate(language_id); -- Answer Option CREATE TABLE answer_option ( id BIGSERIAL PRIMARY KEY, question_id BIGINT NOT NULL REFERENCES question(id) ON DELETE CASCADE, is_correct BOOLEAN NOT NULL DEFAULT FALSE ); CREATE INDEX idx_answer_option_question ON answer_option(question_id); -- Answer Option Translation CREATE TABLE answer_option_translate ( id BIGSERIAL PRIMARY KEY, answer_option_id BIGINT NOT NULL REFERENCES answer_option(id) ON DELETE CASCADE, language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT, answer_text TEXT NOT NULL, explanation TEXT, CONSTRAINT uq_answer_translate_lang UNIQUE (answer_option_id, language_id) ); CREATE INDEX idx_answer_translate_option ON answer_option_translate(answer_option_id); CREATE INDEX idx_answer_translate_language ON answer_option_translate(language_id); -- Question Skill (many-to-many) CREATE TABLE question_skill ( id BIGSERIAL PRIMARY KEY, question_id BIGINT NOT NULL REFERENCES question(id) ON DELETE CASCADE, skill_id BIGINT NOT NULL REFERENCES skill(id) ON DELETE CASCADE, weight DECIMAL(5, 2) NOT NULL DEFAULT 1.00, CONSTRAINT uq_question_skill UNIQUE (question_id, skill_id), CONSTRAINT chk_question_skill_weight CHECK (weight > 0 AND weight <= 100) ); CREATE INDEX idx_question_skill_question ON question_skill(question_id); CREATE INDEX idx_question_skill_skill ON question_skill(skill_id); -- Quiz Attempt CREATE TABLE quiz_attempt ( id BIGSERIAL PRIMARY KEY, enrollment_id BIGINT NOT NULL REFERENCES enrollment(id) ON DELETE CASCADE, quiz_id BIGINT NOT NULL REFERENCES quiz(id) ON DELETE CASCADE, attempt_number INTEGER NOT NULL DEFAULT 1, started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, submited_at TIMESTAMP, status quiz_attempt_status_enum NOT NULL DEFAULT 'IN_PROGRESS', score INTEGER, total_points INTEGER NOT NULL, earned_points INTEGER, passed BOOLEAN, CONSTRAINT uq_quiz_attempt_number UNIQUE (enrollment_id, quiz_id, attempt_number), CONSTRAINT chk_quiz_attempt_number CHECK (attempt_number > 0), CONSTRAINT chk_quiz_attempt_submit CHECK ( (status = 'IN_PROGRESS' AND submited_at IS NULL) OR (status != 'IN_PROGRESS' AND submited_at IS NOT NULL) ), CONSTRAINT chk_quiz_attempt_score CHECK (score IS NULL OR (score >= 0 AND score <= 100)), CONSTRAINT chk_quiz_attempt_points CHECK ( total_points >= 0 AND (earned_points IS NULL OR earned_points >= 0) AND (earned_points IS NULL OR earned_points <= total_points) ) ); CREATE INDEX idx_quiz_attempt_enrollment ON quiz_attempt(enrollment_id); CREATE INDEX idx_quiz_attempt_quiz ON quiz_attempt(quiz_id); CREATE INDEX idx_quiz_attempt_status ON quiz_attempt(status); -- Quiz Answer CREATE TABLE quiz_answer ( id BIGSERIAL PRIMARY KEY, quiz_attempt_id BIGINT NOT NULL REFERENCES quiz_attempt(id) ON DELETE CASCADE, question_id BIGINT NOT NULL REFERENCES question(id) ON DELETE CASCADE, answer_option_id BIGINT REFERENCES answer_option(id) ON DELETE CASCADE, confidence_value DECIMAL(5, 2), is_correct BOOLEAN NOT NULL DEFAULT FALSE, CONSTRAINT uq_quiz_answer UNIQUE (quiz_attempt_id, question_id), CONSTRAINT chk_quiz_answer_confidence CHECK (confidence_value IS NULL OR (confidence_value >= 0 AND confidence_value <= 100)) ); CREATE INDEX idx_quiz_answer_attempt ON quiz_answer(quiz_attempt_id); CREATE INDEX idx_quiz_answer_question ON quiz_answer(question_id); CREATE INDEX idx_quiz_answer_option ON quiz_answer(answer_option_id); -- ============================================================================ -- SKILL TRACKING TABLES -- ============================================================================ -- User Skill CREATE TABLE user_skill ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, skill_id BIGINT NOT NULL REFERENCES skill(id) ON DELETE CASCADE, verified BOOLEAN NOT NULL DEFAULT FALSE, proficiency proficiency_level_enum NOT NULL DEFAULT 'BEGINNER', score_percent DECIMAL(5, 2) NOT NULL DEFAULT 0.00, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT uq_user_skill UNIQUE (user_id, skill_id), CONSTRAINT chk_user_skill_score CHECK (score_percent >= 0 AND score_percent <= 100) ); CREATE INDEX idx_user_skill_user ON user_skill(user_id); CREATE INDEX idx_user_skill_skill ON user_skill(skill_id); CREATE INDEX idx_user_skill_proficiency ON user_skill(proficiency); -- User Skill Snapshot CREATE TABLE user_skill_snapshot ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, skill_id BIGINT NOT NULL REFERENCES skill(id) ON DELETE CASCADE, quiz_attempt_id BIGINT NOT NULL REFERENCES quiz_attempt(id) ON DELETE CASCADE, weight_percent DECIMAL(5, 2) NOT NULL, confidence_avg DECIMAL(5, 2), snapshot_type snapshot_type_enum NOT NULL, CONSTRAINT chk_snapshot_weight CHECK (weight_percent >= 0 AND weight_percent <= 100), CONSTRAINT chk_snapshot_confidence CHECK (confidence_avg IS NULL OR (confidence_avg >= 0 AND confidence_avg <= 100)) ); CREATE INDEX idx_skill_snapshot_user ON user_skill_snapshot(user_id); CREATE INDEX idx_skill_snapshot_skill ON user_skill_snapshot(skill_id); CREATE INDEX idx_skill_snapshot_attempt ON user_skill_snapshot(quiz_attempt_id); CREATE INDEX idx_skill_snapshot_type ON user_skill_snapshot(snapshot_type); -- ============================================================================ -- TRIGGERS FOR AUTOMATED TIMESTAMP UPDATES -- ============================================================================ CREATE OR REPLACE FUNCTION update_updated_at_column() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = CURRENT_TIMESTAMP; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER update_user_updated_at BEFORE UPDATE ON "user" FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); CREATE TRIGGER update_course_updated_at BEFORE UPDATE ON course FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- ============================================================================ -- INITIAL DATA SEEDS -- ============================================================================ -- Insert default languages INSERT INTO language (language) VALUES ('en'), ('mk'), ('sq'), ('sr') ON CONFLICT (language) DO NOTHING; -- ============================================================================ -- COMMENTS FOR DOCUMENTATION -- ============================================================================ COMMENT ON TABLE "user" IS 'Main user accounts for students and learners'; COMMENT ON TABLE expert IS 'Expert/mentor accounts for consultations and meetings'; COMMENT ON TABLE admin IS 'Administrative accounts with system access'; COMMENT ON TABLE course IS 'Core course catalog'; COMMENT ON TABLE enrollment IS 'User course enrollments tracking progress and completion'; COMMENT ON TABLE bundle IS 'Course bundles - supertype for manual and personalized bundles'; COMMENT ON TABLE manual_bundle IS 'Manually created course bundles (subtype of bundle)'; COMMENT ON TABLE personalized_bundle IS 'AI-generated personalized bundles for specific users (subtype of bundle)'; COMMENT ON TABLE learning_path IS 'Curated learning paths - supertype for manual and personalized paths'; COMMENT ON TABLE quiz IS 'Assessments for skill tracking and certification'; COMMENT ON TABLE user_skill IS 'User skill proficiency tracking with tiered levels'; COMMENT ON TABLE user_skill_snapshot IS 'Point-in-time skill assessment snapshots from quiz attempts'; -- ============================================================================ -- END OF SCHEMA -- ============================================================================