co3103-lms-db

db.sql

-- This script was generated by the ERD tool in pgAdmin 4.
-- Please log an issue at https://github.com/pgadmin-org/pgadmin4/issues/new/choose if you find any bugs, including reproduction steps.
BEGIN;


CREATE TABLE IF NOT EXISTS public.app_session
(
    id serial NOT NULL,
    user_id integer NOT NULL,
    token character varying(255) COLLATE pg_catalog."default" NOT NULL,
    created_at timestamp without time zone NOT NULL DEFAULT now(),
    expires_at timestamp without time zone NOT NULL,
    CONSTRAINT app_session_pkey PRIMARY KEY (id),
    CONSTRAINT app_session_token_unique UNIQUE (token)
);

CREATE TABLE IF NOT EXISTS public.app_user
(
    id serial NOT NULL,
    email character varying(255) COLLATE pg_catalog."default" NOT NULL,
    password_hash character varying(255) COLLATE pg_catalog."default" NOT NULL,
    name character varying(255) COLLATE pg_catalog."default" NOT NULL,
    bio text COLLATE pg_catalog."default",
    image_url character varying(255) COLLATE pg_catalog."default",
    created_at timestamp without time zone NOT NULL DEFAULT now(),
    CONSTRAINT app_user_pkey PRIMARY KEY (id),
    CONSTRAINT app_user_email_unique UNIQUE (email)
);

CREATE TABLE IF NOT EXISTS public.course
(
    id serial NOT NULL,
    instructor_id integer NOT NULL,
    title character varying(255) COLLATE pg_catalog."default" NOT NULL,
    description text COLLATE pg_catalog."default" NOT NULL,
    course_category_id integer NOT NULL,
    price integer NOT NULL,
    currency_code character varying(3) COLLATE pg_catalog."default" NOT NULL,
    created_at timestamp without time zone NOT NULL DEFAULT now(),
    language_code character varying(2) COLLATE pg_catalog."default" NOT NULL,
    image_url character varying(255) COLLATE pg_catalog."default",
    learning_outcomes character varying[] COLLATE pg_catalog."default",
    requirements character varying[] COLLATE pg_catalog."default",
    learning_audience character varying[] COLLATE pg_catalog."default",
    CONSTRAINT course_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.course_assignment
(
    id serial NOT NULL,
    course_item_id integer NOT NULL,
    title character varying(255) COLLATE pg_catalog."default" NOT NULL,
    description text COLLATE pg_catalog."default" NOT NULL,
    max_score integer NOT NULL,
    CONSTRAINT course_assignment_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.course_assignment_attempt
(
    id serial NOT NULL,
    course_assignment_id integer NOT NULL,
    user_id integer NOT NULL,
    instructor_feedback text COLLATE pg_catalog."default",
    score integer,
    created_at timestamp without time zone NOT NULL DEFAULT now(),
    CONSTRAINT course_assignment_attempt_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.course_assignment_attempt_answer
(
    id serial NOT NULL,
    course_assignment_attempt_id integer NOT NULL,
    url character varying(255) COLLATE pg_catalog."default" NOT NULL,
    mime_type character varying(255) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT course_assignment_attempt_answer_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.course_category
(
    id serial NOT NULL,
    title character varying(255) COLLATE pg_catalog."default" NOT NULL,
    description text COLLATE pg_catalog."default",
    CONSTRAINT course_category_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.course_enrollment
(
    id serial NOT NULL,
    course_id integer NOT NULL,
    user_id integer NOT NULL,
    enrolled_at timestamp without time zone NOT NULL DEFAULT now(),
    completed_at timestamp without time zone,
    CONSTRAINT course_enrollment_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.course_item
(
    id serial NOT NULL,
    course_section_id integer NOT NULL,
    index integer NOT NULL,
    CONSTRAINT course_item_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.course_item_comment
(
    id serial NOT NULL,
    course_item_id integer NOT NULL,
    user_id integer NOT NULL,
    comment text COLLATE pg_catalog."default" NOT NULL,
    reply_to_comment_id integer,
    created_at timestamp without time zone NOT NULL DEFAULT now(),
    CONSTRAINT course_item_comment_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.course_material
(
    id serial NOT NULL,
    course_item_id integer NOT NULL,
    title character varying(255) COLLATE pg_catalog."default" NOT NULL,
    description text COLLATE pg_catalog."default" NOT NULL,
    url character varying(255) COLLATE pg_catalog."default" NOT NULL,
    mime_type character varying(255) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT course_material_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.course_quiz
(
    id serial NOT NULL,
    course_item_id integer NOT NULL,
    title character varying(255) COLLATE pg_catalog."default" NOT NULL,
    description text COLLATE pg_catalog."default" NOT NULL,
    time_limit_seconds integer,
    max_score integer NOT NULL,
    CONSTRAINT course_quiz_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.course_quiz_attempt
(
    id serial NOT NULL,
    course_quiz_id integer NOT NULL,
    user_id integer NOT NULL,
    score integer NOT NULL,
    instructor_feedback text COLLATE pg_catalog."default",
    created_at timestamp without time zone NOT NULL DEFAULT now(),
    CONSTRAINT course_quiz_attempt_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.course_quiz_attempt_answer
(
    id serial NOT NULL,
    course_quiz_attempt_id integer NOT NULL,
    course_quiz_question_id integer NOT NULL,
    course_quiz_question_option_id integer NOT NULL,
    CONSTRAINT course_quiz_attempt_answer_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.course_quiz_question
(
    id serial NOT NULL,
    course_quiz_id integer NOT NULL,
    question text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT course_quiz_question_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.course_quiz_question_option
(
    id serial NOT NULL,
    course_quiz_question_id integer NOT NULL,
    option text COLLATE pg_catalog."default" NOT NULL,
    is_correct integer NOT NULL,
    CONSTRAINT course_quiz_question_option_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.course_section
(
    id serial NOT NULL,
    course_id integer NOT NULL,
    title character varying(255) COLLATE pg_catalog."default" NOT NULL,
    description text COLLATE pg_catalog."default" NOT NULL,
    index integer NOT NULL,
    CONSTRAINT course_section_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.user_payment
(
    id serial NOT NULL,
    user_id integer NOT NULL,
    user_payment_method_id integer NOT NULL,
    course_id integer NOT NULL,
    amount integer NOT NULL,
    currency_code character varying(3) COLLATE pg_catalog."default" NOT NULL,
    created_at timestamp without time zone NOT NULL DEFAULT now(),
    CONSTRAINT user_payment_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.user_payment_method
(
    id serial NOT NULL,
    user_id integer NOT NULL,
    credit_card_number character varying(255) COLLATE pg_catalog."default" NOT NULL,
    credit_card_expiration_month integer NOT NULL,
    credit_card_expiration_year integer NOT NULL,
    credit_card_cvv character varying(255) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT user_payment_method_pkey PRIMARY KEY (id)
);

ALTER TABLE IF EXISTS public.app_session
    ADD CONSTRAINT app_session_user_id_app_user_id_fk FOREIGN KEY (user_id)
    REFERENCES public.app_user (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.course
    ADD CONSTRAINT course_course_category_id_course_category_id_fk FOREIGN KEY (course_category_id)
    REFERENCES public.course_category (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.course
    ADD CONSTRAINT course_instructor_id_app_user_id_fk FOREIGN KEY (instructor_id)
    REFERENCES public.app_user (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.course_assignment
    ADD CONSTRAINT course_assignment_course_item_id_course_item_id_fk FOREIGN KEY (course_item_id)
    REFERENCES public.course_item (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.course_assignment_attempt
    ADD CONSTRAINT course_assignment_attempt_course_assignment_id_course_assignmen FOREIGN KEY (course_assignment_id)
    REFERENCES public.course_assignment (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.course_assignment_attempt
    ADD CONSTRAINT course_assignment_attempt_user_id_app_user_id_fk FOREIGN KEY (user_id)
    REFERENCES public.app_user (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.course_assignment_attempt_answer
    ADD CONSTRAINT course_assignment_attempt_answer_course_assignment_attempt_id_c FOREIGN KEY (course_assignment_attempt_id)
    REFERENCES public.course_assignment_attempt (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.course_enrollment
    ADD CONSTRAINT course_enrollment_course_id_course_id_fk FOREIGN KEY (course_id)
    REFERENCES public.course (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.course_enrollment
    ADD CONSTRAINT course_enrollment_user_id_app_user_id_fk FOREIGN KEY (user_id)
    REFERENCES public.app_user (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.course_item
    ADD CONSTRAINT course_item_course_section_id_course_section_id_fk FOREIGN KEY (course_section_id)
    REFERENCES public.course_section (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.course_item_comment
    ADD CONSTRAINT course_item_comment_course_item_id_course_item_id_fk FOREIGN KEY (course_item_id)
    REFERENCES public.course_item (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.course_item_comment
    ADD CONSTRAINT course_item_comment_reply_to_comment_id_course_item_comment_id_ FOREIGN KEY (reply_to_comment_id)
    REFERENCES public.course_item_comment (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.course_item_comment
    ADD CONSTRAINT course_item_comment_user_id_app_user_id_fk FOREIGN KEY (user_id)
    REFERENCES public.app_user (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.course_material
    ADD CONSTRAINT course_material_course_item_id_course_item_id_fk FOREIGN KEY (course_item_id)
    REFERENCES public.course_item (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.course_quiz
    ADD CONSTRAINT course_quiz_course_item_id_course_item_id_fk FOREIGN KEY (course_item_id)
    REFERENCES public.course_item (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.course_quiz_attempt
    ADD CONSTRAINT course_quiz_attempt_course_quiz_id_course_quiz_id_fk FOREIGN KEY (course_quiz_id)
    REFERENCES public.course_quiz (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.course_quiz_attempt
    ADD CONSTRAINT course_quiz_attempt_user_id_app_user_id_fk FOREIGN KEY (user_id)
    REFERENCES public.app_user (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.course_quiz_attempt_answer
    ADD CONSTRAINT course_quiz_attempt_answer_course_quiz_attempt_id_course_quiz_a FOREIGN KEY (course_quiz_attempt_id)
    REFERENCES public.course_quiz_attempt (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.course_quiz_attempt_answer
    ADD CONSTRAINT course_quiz_attempt_answer_course_quiz_question_id_course_quiz_ FOREIGN KEY (course_quiz_question_id)
    REFERENCES public.course_quiz_attempt (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.course_quiz_attempt_answer
    ADD CONSTRAINT course_quiz_attempt_answer_course_quiz_question_option_id_cours FOREIGN KEY (course_quiz_question_option_id)
    REFERENCES public.course_quiz_attempt (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.course_quiz_question
    ADD CONSTRAINT course_quiz_question_course_quiz_id_course_quiz_id_fk FOREIGN KEY (course_quiz_id)
    REFERENCES public.course_quiz (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.course_quiz_question_option
    ADD CONSTRAINT course_quiz_question_option_course_quiz_question_id_course_quiz FOREIGN KEY (course_quiz_question_id)
    REFERENCES public.course_quiz_question (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.course_section
    ADD CONSTRAINT course_section_course_id_course_id_fk FOREIGN KEY (course_id)
    REFERENCES public.course (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE CASCADE;


ALTER TABLE IF EXISTS public.user_payment
    ADD CONSTRAINT user_payment_course_id_course_id_fk FOREIGN KEY (course_id)
    REFERENCES public.course (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.user_payment
    ADD CONSTRAINT user_payment_user_id_app_user_id_fk FOREIGN KEY (user_id)
    REFERENCES public.app_user (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.user_payment
    ADD CONSTRAINT user_payment_user_payment_method_id_user_payment_method_id_fk FOREIGN KEY (user_payment_method_id)
    REFERENCES public.user_payment_method (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.user_payment_method
    ADD CONSTRAINT user_payment_method_user_id_app_user_id_fk FOREIGN KEY (user_id)
    REFERENCES public.app_user (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;

END;