
-- 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
-- ============================================================================