design: schema_creation.sql

drop table if exists _user;

create table _user (
    id BIGSERIAL primary key,
    name VARCHAR(255),
    email VARCHAR(255) not null unique,
    login_provider VARCHAR(50),
    password_hash VARCHAR(255) not null,
    verified BOOLEAN not null,
    profile_complete BOOLEAN not null,
    used_free_consultation BOOLEAN not null,
    company_size VARCHAR(50),
    work_position VARCHAR(255),
    points INTEGER
);

drop table if exists meeting_email_reminder;

create table meeting_email_reminder (
        id BIGSERIAL primary key,
        meeting_at TIMESTAMP not null,
        scheduled_at TIMESTAMP not null,
        sent BOOLEAN not null,
        meeting_link TEXT not null,
        user_id BIGINT not null,
        foreign key (user_id) references _user(id) on
delete
        cascade
);

drop table if exists user_favorite_course_ids;

create table user_favorite_course_ids (
    user_id BIGINT not null,
    favorite_course_ids BIGINT not null,
    primary key (user_id,
favorite_course_ids),
    foreign key (user_id) references _user(id) on
delete
        cascade
);

drop table if exists expert;

create table expert (
    id BIGSERIAL primary key,
    name VARCHAR(255),
    email VARCHAR(255) not null unique,
    login_provider VARCHAR(50),
    password_hash VARCHAR(255) not null
);

drop table if exists course;

create table course (
        id BIGSERIAL primary key,
        image_url TEXT not null,
        color VARCHAR(255),
        difficulty VARCHAR(255) not null,
        duration_minutes INT not null,
        price DOUBLE precision not null
);

drop table if exists expert_course;

create table expert_course (
        course_id BIGINT not null,
        expert_id BIGINT not null,
        primary key (expert_id,
course_id),
        foreign key (expert_id) references expert(id) on
delete
        cascade,
        foreign key (course_id) references course(id) on
        delete
                cascade
);

drop table if exists course_version;

create table course_version (
        id BIGSERIAL primary key,
        version_number INT not null,
        creation_date DATE default CURRENT_DATE not null,
        active BOOLEAN not null,
        course_id BIGINT not null,
        foreign key (course_id) references course(id) on
delete
        cascade
);

drop table if exists enrollment;

create table enrollment (
        id BIGSERIAL primary key,
        enrollment_status VARCHAR(255),
        purchase_date DATE,
        activation_date DATE default CURRENT_DATE,
        completion_date DATE,
        course_version_id BIGINT not null,
        user_id BIGINT not null,
        foreign key (user_id) references _user(id),
        foreign key (course_version_id) references course_version(id)
);

drop table if exists payment;

create table payment (
        id BIGSERIAL primary key,
        amount DOUBLE precision not null,
        payment_date DATE default CURRENT_DATE not null,
        payment_method VARCHAR(255) not null,
        payment_status VARCHAR(255) not null,
        enrollment_id BIGINT not null unique,
        foreign key (enrollment_id) references enrollment(id) on delete cascade
);

drop table if exists review;

create table review (
        id BIGSERIAL primary key,
        rating INT not null,
        comment VARCHAR(255),
        review_date DATE default CURRENT_DATE not null,
        enrollment_id BIGINT not null unique,
        foreign key (enrollment_id) references enrollment(id) on delete cascade
);

drop table if exists course_translate;

create table course_translate (
        id BIGSERIAL primary key,
        language VARCHAR(255) not null,
        title_short VARCHAR(255) not null,
        title VARCHAR(255) not null,
        description_short VARCHAR(255) not null,
        description VARCHAR(255) not null,
        description_long VARCHAR(255) not null,
        course_id BIGINT not null,
        foreign key (course_id) references course(id) on
delete
        cascade
);

drop table if exists course_translate_what_will_be_learned;

create table course_translate_what_will_be_learned (
        course_translate_id BIGINT not null,
        what_will_be_learned TEXT not null,
        primary key (course_translate_id,
what_will_be_learned),
        foreign key (course_translate_id) references course_translate(id) on
delete
        cascade
);

drop table if exists course_content;

create table course_content (
        id BIGSERIAL primary key,
        position INT not null,
        course_version_id BIGINT not null,
        foreign key (course_version_id) references course_version(id) on
delete
        cascade
);

drop table if exists course_content_translate;

create table course_content_translate (
        id BIGSERIAL primary key,
        title VARCHAR(255) not null,
        language VARCHAR(255) not null,
        course_content_id BIGINT not null,
        foreign key (course_content_id) references course_content(id) on
delete
        cascade
);

drop table if exists course_lecture;

create table course_lecture (
        id BIGSERIAL primary key,
        duration_minutes INT not null,
        position INT not null,
        content_type VARCHAR(255) not null,
        course_content_id BIGINT not null,
        foreign key (course_content_id) references course_content(id) on
delete
        cascade
);

drop table if exists course_lecture_translate;

create table course_lecture_translate (
        id BIGSERIAL primary key,
        title VARCHAR(255) not null,
        language VARCHAR(255) not null,
        content_file_name TEXT,
        description TEXT not null,
        content_text TEXT,
        course_lecture_id BIGINT not null,
        foreign key (course_lecture_id) references course_lecture(id) on
delete
        cascade
);

drop table if exists user_course_progress;

create table user_course_progress (
        id BIGSERIAL primary key,
        completed BOOLEAN,
        completed_at TIMESTAMP,
        enrollment_id BIGINT not null,
        course_lecture_id BIGINT not null,
        foreign key (enrollment_id) references enrollment(id) on
delete
        cascade,
        foreign key (course_lecture_id) references course_lecture(id) on
        delete
                cascade
);

drop table if exists verification_token;

create table verification_token (
        uuid BIGSERIAL primary key,
        created_at TIMESTAMP default CURRENT_TIMESTAMP,
        expired_at TIMESTAMP,
        user_id BIGINT not null,
        foreign key (user_id) references _user(id) on
delete
        cascade
);

drop table if exists tag;

create table tag (
        id BIGSERIAL primary key,
        type VARCHAR(255) not null
);

drop table if exists tag_translate;

create table tag_translate (
        id BIGSERIAL primary key,
        language VARCHAR(255) not null,
        value VARCHAR(255) not null,
        tag_id BIGINT not null,
        foreign key (tag_id) references tag(id) on
delete
        cascade
);

drop table if exists user_tag;

create table user_tag (
        tag_id BIGINT not null,
        user_id BIGINT not null,
        primary key (tag_id,
user_id),
        foreign key (tag_id) references tag(id) on
delete
        cascade,
        foreign key (user_id) references _user(id) on
        delete
                cascade
);

drop table if exists course_tag;

create table course_tag (
        tag_id BIGINT not null,
        course_id BIGINT not null,
        primary key (tag_id,
course_id),
        foreign key (tag_id) references tag(id) on
delete
        cascade,
        foreign key (course_id) references course(id) on
        delete
                cascade
);