
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
);