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