DROP TABLE IF EXISTS user_favorite_course CASCADE; DROP TABLE IF EXISTS meeting_email_reminder CASCADE; DROP TABLE IF EXISTS course_tag CASCADE; DROP TABLE IF EXISTS user_tag CASCADE; DROP TABLE IF EXISTS tag_translate CASCADE; DROP TABLE IF EXISTS tag CASCADE; DROP TABLE IF EXISTS verification_token CASCADE; DROP TABLE IF EXISTS user_course_progress CASCADE; DROP TABLE IF EXISTS review CASCADE; DROP TABLE IF EXISTS payment CASCADE; DROP TABLE IF EXISTS enrollment 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_translate CASCADE; DROP TABLE IF EXISTS course_version CASCADE; DROP TABLE IF EXISTS course CASCADE; DROP TABLE IF EXISTS expert CASCADE; DROP TABLE IF EXISTS "user" CASCADE; DROP TABLE IF EXISTS account CASCADE; DROP TABLE IF EXISTS language CASCADE; DROP TYPE IF EXISTS login_provider CASCADE; DROP TYPE IF EXISTS company_size CASCADE; DROP TYPE IF EXISTS enrollment_status CASCADE; DROP TYPE IF EXISTS payment_method CASCADE; DROP TYPE IF EXISTS payment_status CASCADE; DROP TYPE IF EXISTS difficulty CASCADE; DROP TYPE IF EXISTS content_type CASCADE; DROP TYPE IF EXISTS tag_type CASCADE; -- ----------------------------------------------------------------------------- -- Enum types -- ----------------------------------------------------------------------------- CREATE TYPE login_provider AS ENUM ('local', 'google'); CREATE TYPE company_size AS ENUM ('freelance', 'micro', 'small', 'medium', 'mid_market', 'enterprise', 'other'); CREATE TYPE enrollment_status AS ENUM ('pending', 'active', 'completed'); CREATE TYPE payment_method AS ENUM ('card', 'paypal', 'casys'); CREATE TYPE payment_status AS ENUM ('pending', 'completed', 'failed'); CREATE TYPE difficulty AS ENUM ('beginner', 'intermediate', 'advanced', 'expert'); CREATE TYPE content_type AS ENUM ('text', 'file', 'video', 'quiz'); CREATE TYPE tag_type AS ENUM ('skill', 'topic'); -- ----------------------------------------------------------------------------- -- 22. language -- ----------------------------------------------------------------------------- CREATE TABLE language ( id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, value varchar(64) NOT NULL, CONSTRAINT uq_language_value UNIQUE (value) ); -- ----------------------------------------------------------------------------- -- 1. account -- ----------------------------------------------------------------------------- CREATE TABLE account ( id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, email varchar(320) NOT NULL, password_hash varchar(255) NOT NULL, name varchar(255) NOT NULL, CONSTRAINT uq_account_email UNIQUE (email) ); -- ----------------------------------------------------------------------------- -- 2. "user" -- ----------------------------------------------------------------------------- CREATE TABLE "user" ( user_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name varchar(255) NOT NULL, email varchar(320) NOT NULL, password_hash varchar(255) NOT NULL, login_provider login_provider NOT NULL, is_verified boolean NOT NULL DEFAULT false, is_profile_complete boolean NOT NULL DEFAULT false, has_used_free_consultation boolean NOT NULL DEFAULT false, work_position varchar(255) NOT NULL, company_size company_size NOT NULL, points integer NOT NULL DEFAULT 0, account_id bigint NOT NULL, CONSTRAINT uq_user_email UNIQUE (email), CONSTRAINT uq_user_account_id UNIQUE (account_id), CONSTRAINT fk_user_account FOREIGN KEY (account_id) REFERENCES account (id) ON DELETE CASCADE, CONSTRAINT ck_user_points_non_negative CHECK (points >= 0) ); -- ----------------------------------------------------------------------------- -- 3. expert -- ----------------------------------------------------------------------------- CREATE TABLE expert ( expert_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, account_id bigint NOT NULL, CONSTRAINT uq_expert_account_id UNIQUE (account_id), CONSTRAINT fk_expert_account FOREIGN KEY (account_id) REFERENCES account (id) ON DELETE CASCADE ); -- ----------------------------------------------------------------------------- -- 8. course -- ----------------------------------------------------------------------------- CREATE TABLE course ( course_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, color varchar(32) NOT NULL, difficulty difficulty NOT NULL, duration_minutes integer NOT NULL, image_url varchar(1024) NOT NULL, price numeric(12,2) NOT NULL, CONSTRAINT ck_course_duration_positive CHECK (duration_minutes > 0), CONSTRAINT ck_course_price_non_negative CHECK (price >= 0) ); -- ----------------------------------------------------------------------------- -- 7. course_version -- ----------------------------------------------------------------------------- CREATE TABLE course_version ( course_version_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, version_number integer NOT NULL, created_at date NOT NULL, is_active boolean NOT NULL DEFAULT false, course_id bigint NOT NULL, CONSTRAINT uq_course_version_number UNIQUE (course_id, version_number), CONSTRAINT fk_course_version_course FOREIGN KEY (course_id) REFERENCES course (course_id) ON DELETE CASCADE, CONSTRAINT ck_course_version_number_positive CHECK (version_number > 0) ); -- Only one active version per course. CREATE UNIQUE INDEX uq_course_version_one_active ON course_version (course_id) WHERE is_active; -- ----------------------------------------------------------------------------- -- 9. course_translate -- ----------------------------------------------------------------------------- CREATE TABLE course_translate ( course_translate_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, description_short text NOT NULL, description text NOT NULL, description_long text NOT NULL, title_short varchar(255) NOT NULL, title varchar(512) NOT NULL, what_will_be_learned text[] NOT NULL, course_id bigint NOT NULL, language_id bigint NOT NULL, CONSTRAINT uq_course_translate UNIQUE (course_id, language_id), CONSTRAINT fk_course_translate_course FOREIGN KEY (course_id) REFERENCES course (course_id) ON DELETE CASCADE, CONSTRAINT fk_course_translate_language FOREIGN KEY (language_id) REFERENCES language (id) ON DELETE RESTRICT ); -- ----------------------------------------------------------------------------- -- 10. course_content -- ----------------------------------------------------------------------------- CREATE TABLE course_content ( course_content_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, position integer NOT NULL, course_version_id bigint NOT NULL, CONSTRAINT uq_course_content_position UNIQUE (course_version_id, position), CONSTRAINT fk_course_content_version FOREIGN KEY (course_version_id) REFERENCES course_version (course_version_id) ON DELETE CASCADE, CONSTRAINT ck_course_content_position_positive CHECK (position > 0) ); -- ----------------------------------------------------------------------------- -- 11. course_content_translate -- ----------------------------------------------------------------------------- CREATE TABLE course_content_translate ( course_content_translate_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, title varchar(512) NOT NULL, course_content_id bigint NOT NULL, language_id bigint NOT NULL, CONSTRAINT uq_course_content_translate UNIQUE (course_content_id, language_id), CONSTRAINT fk_cct_content FOREIGN KEY (course_content_id) REFERENCES course_content (course_content_id) ON DELETE CASCADE, CONSTRAINT fk_cct_language FOREIGN KEY (language_id) REFERENCES language (id) ON DELETE RESTRICT ); -- ----------------------------------------------------------------------------- -- 12. course_lecture -- ----------------------------------------------------------------------------- CREATE TABLE course_lecture ( course_lecture_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, position integer NOT NULL, duration_minutes integer NOT NULL, content_type content_type NOT NULL, course_content_id bigint NOT NULL, CONSTRAINT uq_course_lecture_position UNIQUE (course_content_id, position), CONSTRAINT fk_course_lecture_content FOREIGN KEY (course_content_id) REFERENCES course_content (course_content_id) ON DELETE CASCADE, CONSTRAINT ck_course_lecture_position_positive CHECK (position > 0), CONSTRAINT ck_course_lecture_duration_positive CHECK (duration_minutes > 0) ); -- ----------------------------------------------------------------------------- -- 13. course_lecture_translate -- ----------------------------------------------------------------------------- CREATE TABLE course_lecture_translate ( course_lecture_translate_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, content_file_name varchar(512), content_text text, description text NOT NULL, title varchar(512) NOT NULL, course_lecture_id bigint NOT NULL, language_id bigint NOT NULL, CONSTRAINT uq_course_lecture_translate UNIQUE (course_lecture_id, language_id), CONSTRAINT fk_clt_lecture FOREIGN KEY (course_lecture_id) REFERENCES course_lecture (course_lecture_id) ON DELETE CASCADE, CONSTRAINT fk_clt_language FOREIGN KEY (language_id) REFERENCES language (id) ON DELETE RESTRICT ); -- ----------------------------------------------------------------------------- -- 4. enrollment -- ----------------------------------------------------------------------------- CREATE TABLE enrollment ( enrollment_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, enrollment_status enrollment_status NOT NULL, activation_date date, completion_date date, purchase_date date NOT NULL, user_id bigint NOT NULL, course_version_id bigint NOT NULL, CONSTRAINT uq_enrollment_user_version UNIQUE (user_id, course_version_id), CONSTRAINT fk_enrollment_user FOREIGN KEY (user_id) REFERENCES "user" (user_id) ON DELETE CASCADE, CONSTRAINT fk_enrollment_course_version FOREIGN KEY (course_version_id) REFERENCES course_version (course_version_id) ON DELETE RESTRICT, CONSTRAINT ck_enrollment_status_dates CHECK ( (enrollment_status = 'pending' AND activation_date IS NULL AND completion_date IS NULL) OR (enrollment_status = 'active' AND activation_date IS NOT NULL AND completion_date IS NULL) OR (enrollment_status = 'completed' AND activation_date IS NOT NULL AND completion_date IS NOT NULL) ) ); -- ----------------------------------------------------------------------------- -- 5. payment -- ----------------------------------------------------------------------------- CREATE TABLE payment ( payment_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, amount numeric(12,2) NOT NULL, payment_date date NOT NULL, payment_method payment_method NOT NULL, payment_status payment_status NOT NULL, enrollment_id bigint NOT NULL, CONSTRAINT uq_payment_enrollment UNIQUE (enrollment_id), CONSTRAINT fk_payment_enrollment FOREIGN KEY (enrollment_id) REFERENCES enrollment (enrollment_id) ON DELETE RESTRICT, CONSTRAINT ck_payment_amount_non_negative CHECK (amount >= 0) ); -- ----------------------------------------------------------------------------- -- 6. review -- ----------------------------------------------------------------------------- CREATE TABLE review ( review_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, rating integer NOT NULL, comment text NOT NULL, review_date date NOT NULL, enrollment_id bigint NOT NULL, CONSTRAINT uq_review_enrollment UNIQUE (enrollment_id), CONSTRAINT fk_review_enrollment FOREIGN KEY (enrollment_id) REFERENCES enrollment (enrollment_id) ON DELETE CASCADE, CONSTRAINT ck_review_rating_range CHECK (rating BETWEEN 1 AND 5) ); -- ----------------------------------------------------------------------------- -- 14. user_course_progress -- ----------------------------------------------------------------------------- CREATE TABLE user_course_progress ( user_course_progress_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, is_completed boolean NOT NULL DEFAULT false, completed_at timestamp, course_lecture_id bigint NOT NULL, enrollment_id bigint NOT NULL, CONSTRAINT uq_ucp UNIQUE (enrollment_id, course_lecture_id), CONSTRAINT fk_ucp_lecture FOREIGN KEY (course_lecture_id) REFERENCES course_lecture (course_lecture_id) ON DELETE CASCADE, CONSTRAINT fk_ucp_enrollment FOREIGN KEY (enrollment_id) REFERENCES enrollment (enrollment_id) ON DELETE CASCADE, CONSTRAINT ck_ucp_completed CHECK ( (is_completed AND completed_at IS NOT NULL) OR (NOT is_completed AND completed_at IS NULL) ) ); -- ----------------------------------------------------------------------------- -- 15. verification_token -- ----------------------------------------------------------------------------- CREATE TABLE verification_token ( verification_token_uuid uuid PRIMARY KEY DEFAULT gen_random_uuid(), expires_at timestamp NOT NULL, created_at timestamp NOT NULL DEFAULT now(), user_id bigint NOT NULL, CONSTRAINT fk_verification_token_user FOREIGN KEY (user_id) REFERENCES "user" (user_id) ON DELETE CASCADE, CONSTRAINT ck_verification_token_expiry CHECK (expires_at > created_at) ); -- ----------------------------------------------------------------------------- -- 16. tag -- ----------------------------------------------------------------------------- CREATE TABLE tag ( tag_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, tag_type tag_type NOT NULL ); -- ----------------------------------------------------------------------------- -- 17. tag_translate -- ----------------------------------------------------------------------------- CREATE TABLE tag_translate ( tag_translate_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, value varchar(255) NOT NULL, tag_id bigint NOT NULL, language_id bigint NOT NULL, CONSTRAINT uq_tag_translate UNIQUE (tag_id, language_id), CONSTRAINT fk_tag_translate_tag FOREIGN KEY (tag_id) REFERENCES tag (tag_id) ON DELETE CASCADE, CONSTRAINT fk_tag_translate_language FOREIGN KEY (language_id) REFERENCES language (id) ON DELETE RESTRICT ); -- ----------------------------------------------------------------------------- -- 18. user_tag -- ----------------------------------------------------------------------------- CREATE TABLE user_tag ( tag_id bigint NOT NULL, user_id bigint NOT NULL, CONSTRAINT pk_user_tag PRIMARY KEY (user_id, tag_id), CONSTRAINT fk_user_tag_tag FOREIGN KEY (tag_id) REFERENCES tag (tag_id) ON DELETE CASCADE, CONSTRAINT fk_user_tag_user FOREIGN KEY (user_id) REFERENCES "user" (user_id) ON DELETE CASCADE ); -- ----------------------------------------------------------------------------- -- 19. course_tag -- ----------------------------------------------------------------------------- CREATE TABLE course_tag ( tag_id bigint NOT NULL, course_id bigint NOT NULL, CONSTRAINT pk_course_tag PRIMARY KEY (course_id, tag_id), CONSTRAINT fk_course_tag_tag FOREIGN KEY (tag_id) REFERENCES tag (tag_id) ON DELETE CASCADE, CONSTRAINT fk_course_tag_course FOREIGN KEY (course_id) REFERENCES course (course_id) ON DELETE CASCADE ); -- ----------------------------------------------------------------------------- -- 20. meeting_email_reminder -- ----------------------------------------------------------------------------- CREATE TABLE meeting_email_reminder ( id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, meeting_at timestamp NOT NULL, scheduled_at timestamp NOT NULL, sent boolean NOT NULL DEFAULT false, meeting_link varchar(1024) NOT NULL, user_id bigint NOT NULL, CONSTRAINT uq_mer_link UNIQUE (meeting_link), CONSTRAINT fk_mer_user FOREIGN KEY (user_id) REFERENCES "user" (user_id) ON DELETE CASCADE ); -- ----------------------------------------------------------------------------- -- 21. user_favorite_course -- ----------------------------------------------------------------------------- CREATE TABLE user_favorite_course ( user_id bigint NOT NULL, course_id bigint NOT NULL, CONSTRAINT pk_user_favorite_course PRIMARY KEY (user_id, course_id), CONSTRAINT fk_ufc_user FOREIGN KEY (user_id) REFERENCES "user" (user_id) ON DELETE CASCADE, CONSTRAINT fk_ufc_course FOREIGN KEY (course_id) REFERENCES course (course_id) ON DELETE CASCADE );