Learning management (LMS)

CREATE TYPE enrollment_status AS ENUM ('active', 'completed', 'dropped');

CREATE TABLE users (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    email text NOT NULL UNIQUE,
    name text NOT NULL
);

CREATE TABLE courses (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    instructor_id uuid NOT NULL,
    title text NOT NULL,
    summary text,
    published boolean DEFAULT false NOT NULL
);
CREATE INDEX courses_instructor_id_idx ON courses (instructor_id);

CREATE TABLE modules (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    course_id uuid NOT NULL,
    title text NOT NULL,
    position int NOT NULL,
    UNIQUE (course_id, position)
);

CREATE TABLE lessons (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    module_id uuid NOT NULL,
    title text NOT NULL,
    content text,
    video_url text,
    position int NOT NULL,
    UNIQUE (module_id, position)
);

CREATE TABLE enrollments (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id uuid NOT NULL,
    course_id uuid NOT NULL,
    status enrollment_status DEFAULT 'active' NOT NULL,
    enrolled_at timestamptz DEFAULT now() NOT NULL,
    UNIQUE (user_id, course_id)
);
CREATE INDEX enrollments_course_id_idx ON enrollments (course_id);

CREATE TABLE lesson_progress (
    enrollment_id uuid NOT NULL,
    lesson_id uuid NOT NULL,
    completed_at timestamptz DEFAULT now() NOT NULL,
    PRIMARY KEY (enrollment_id, lesson_id)
);
CREATE INDEX lesson_progress_lesson_id_idx ON lesson_progress (lesson_id);

CREATE TABLE quizzes (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    module_id uuid NOT NULL,
    title text NOT NULL,
    pass_mark int DEFAULT 70 NOT NULL
);
CREATE INDEX quizzes_module_id_idx ON quizzes (module_id);

CREATE TABLE quiz_attempts (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    quiz_id uuid NOT NULL,
    enrollment_id uuid NOT NULL,
    score int NOT NULL,
    attempted_at timestamptz DEFAULT now() NOT NULL,
    CHECK (score between 0 and 100)
);
CREATE INDEX quiz_attempts_quiz_id_idx ON quiz_attempts (quiz_id);
CREATE INDEX quiz_attempts_enrollment_id_idx ON quiz_attempts (enrollment_id);

ALTER TABLE courses ADD FOREIGN KEY (instructor_id) REFERENCES users (id);

ALTER TABLE modules ADD FOREIGN KEY (course_id) REFERENCES courses (id) ON DELETE CASCADE;

ALTER TABLE lessons ADD FOREIGN KEY (module_id) REFERENCES modules (id) ON DELETE CASCADE;

ALTER TABLE enrollments ADD FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

ALTER TABLE enrollments ADD FOREIGN KEY (course_id) REFERENCES courses (id) ON DELETE CASCADE;

ALTER TABLE lesson_progress ADD FOREIGN KEY (enrollment_id) REFERENCES enrollments (id) ON DELETE CASCADE;

ALTER TABLE lesson_progress ADD FOREIGN KEY (lesson_id) REFERENCES lessons (id) ON DELETE CASCADE;

ALTER TABLE quizzes ADD FOREIGN KEY (module_id) REFERENCES modules (id) ON DELETE CASCADE;

ALTER TABLE quiz_attempts ADD FOREIGN KEY (quiz_id) REFERENCES quizzes (id) ON DELETE CASCADE;

ALTER TABLE quiz_attempts ADD FOREIGN KEY (enrollment_id) REFERENCES enrollments (id) ON DELETE CASCADE;