-- DROP TYPE project."offer_status";

CREATE TYPE project."offer_status" AS ENUM (
	'IN PROGRESS',
	'WAITING FOR PAYMENT',
	'COMPLETED',
	'DECLINED');

-- DROP TYPE project."user_type";

CREATE TYPE project."user_type" AS ENUM (
	'ORGANIZER',
	'ARTIST',
	'MANAGER');

-- project.artist_types definition

-- Drop table

-- DROP TABLE project.artist_types;

CREATE TABLE project.artist_types (
	id serial4 NOT NULL,
	"name" varchar NOT NULL,
	CONSTRAINT artist_types_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX artist_types_id_uindex ON project.artist_types USING btree (id);


-- project.event_types definition

-- Drop table

-- DROP TABLE project.event_types;

CREATE TABLE project.event_types (
	id serial4 NOT NULL,
	"name" varchar NOT NULL,
	CONSTRAINT event_types_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX event_types_id_uindex ON project.event_types USING btree (id);


-- project.genres definition

-- Drop table

-- DROP TABLE project.genres;

CREATE TABLE project.genres (
	id serial4 NOT NULL,
	"name" varchar NOT NULL,
	CONSTRAINT genres_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX genres_id_uindex ON project.genres USING btree (id);


-- project.password_resets definition

-- Drop table

-- DROP TABLE project.password_resets;

CREATE TABLE project.password_resets (
	email varchar(255) NOT NULL,
	"token" varchar(255) NOT NULL,
	created_at timestamp(0) NULL
);
CREATE INDEX password_resets_email_index ON project.password_resets USING btree (email);


-- project.users definition

-- Drop table

-- DROP TABLE project.users;

CREATE TABLE project.users (
	id serial4 NOT NULL,
	email varchar NOT NULL,
	username varchar(100) NOT NULL,
	"password" varchar NOT NULL,
	"name" varchar(80) NOT NULL,
	created_at timestamp NOT NULL,
	updated_at timestamp NULL,
	deleted_at timestamp NULL,
	email_verified_at timestamp NULL,
	profile_picture varchar NOT NULL,
	last_login_at timestamp NULL,
	last_login_ip varchar(45) NULL,
	user_agent varchar NULL,
	"type" project."user_type" NOT NULL,
	remember_token varchar(100) NULL,
	stripe_id varchar NULL,
	pm_type varchar NULL,
	pm_last_four bpchar(4) NULL,
	trial_ends_at timestamp NULL,
	CONSTRAINT users_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX users_email_uindex ON project.users USING btree (email);
CREATE UNIQUE INDEX users_id_uindex ON project.users USING btree (id);
CREATE UNIQUE INDEX users_username_uindex ON project.users USING btree (username);


-- project.managers definition

-- Drop table

-- DROP TABLE project.managers;

CREATE TABLE project.managers (
	user_id int4 NOT NULL,
	CONSTRAINT managers_pk PRIMARY KEY (user_id),
	CONSTRAINT managers_fk FOREIGN KEY (user_id) REFERENCES project.users(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);


-- project.organizers definition

-- Drop table

-- DROP TABLE project.organizers;

CREATE TABLE project.organizers (
	user_id int4 NOT NULL,
	city varchar(85) NULL,
	country varchar(56) NULL,
	CONSTRAINT organizers_pk PRIMARY KEY (user_id),
	CONSTRAINT organizers_fk FOREIGN KEY (user_id) REFERENCES project.users(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE UNIQUE INDEX organizers_user_id_uindex ON project.organizers USING btree (user_id);


-- project.artists definition

-- Drop table

-- DROP TABLE project.artists;

CREATE TABLE project.artists (
	user_id int4 NOT NULL,
	birth_date date NULL,
	city varchar(85) NULL,
	country varchar(56) NULL,
	short_description text NULL,
	price_per_hour float4 NULL,
	instagram_link varchar NULL,
	soundcloud_link varchar NULL,
	apple_music_link varchar NULL,
	youtube_link varchar NULL,
	spotify_link varchar NULL,
	manager_id int4 NULL,
	artist_type_id int4 NULL,
	admin_verified_at timestamp NULL,
	CONSTRAINT artists_pk PRIMARY KEY (user_id),
	CONSTRAINT artists_fk FOREIGN KEY (user_id) REFERENCES project.users(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
	CONSTRAINT artists_fk_manager FOREIGN KEY (manager_id) REFERENCES project.managers(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT artists_fk_type FOREIGN KEY (artist_type_id) REFERENCES project.artist_types(id) ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE UNIQUE INDEX artists_user_id_uindex ON project.artists USING btree (user_id);


-- project.events definition

-- Drop table

-- DROP TABLE project.events;

CREATE TABLE project.events (
	id serial4 NOT NULL,
	title varchar(100) NOT NULL,
	slug varchar(150) NOT NULL,
	start_time time NOT NULL,
	end_time time NOT NULL,
	event_date date NOT NULL,
	city varchar(85) NOT NULL,
	country varchar(56) NOT NULL,
	description text NULL,
	event_type_id int4 NOT NULL,
	updated_at timestamp NULL,
	created_at timestamp NOT NULL,
	organizer_id int4 NOT NULL,
	CONSTRAINT events_pk PRIMARY KEY (id),
	CONSTRAINT events_fk_event_type FOREIGN KEY (event_type_id) REFERENCES project.event_types(id) ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT events_fk_organizer FOREIGN KEY (organizer_id) REFERENCES project.organizers(user_id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE UNIQUE INDEX events_id_uindex ON project.events USING btree (id);
CREATE UNIQUE INDEX events_slug_uindex ON project.events USING btree (slug);


-- project.manager_invites definition

-- Drop table

-- DROP TABLE project.manager_invites;

CREATE TABLE project.manager_invites (
	id serial4 NOT NULL,
	email varchar NOT NULL,
	invitation_token varchar NOT NULL,
	created_at timestamp NOT NULL,
	updated_at timestamp NULL,
	registered_at timestamp NULL,
	artist_id int4 NOT NULL,
	CONSTRAINT manager_invites_pk PRIMARY KEY (id),
	CONSTRAINT manager_invites_fk_artist FOREIGN KEY (artist_id) REFERENCES project.artists(user_id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE UNIQUE INDEX manager_invites_id_uindex ON project.manager_invites USING btree (id);


-- project.offers definition

-- Drop table

-- DROP TABLE project.offers;

CREATE TABLE project.offers (
	id serial4 NOT NULL,
	slug varchar(150) NOT NULL,
	payment_type int4 NOT NULL,
	created_at timestamp NOT NULL,
	updated_at timestamp NULL,
	completed_at timestamp NULL,
	artist_id int4 NOT NULL,
	event_id int4 NOT NULL,
	price float4 NULL,
	status project."offer_status" NOT NULL,
	CONSTRAINT offers_pk PRIMARY KEY (id),
	CONSTRAINT offers_fk_artist FOREIGN KEY (artist_id) REFERENCES project.artists(user_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
	CONSTRAINT offers_fk_event FOREIGN KEY (event_id) REFERENCES project.events(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE UNIQUE INDEX offers_id_uindex ON project.offers USING btree (id);
CREATE UNIQUE INDEX offers_slug_uindex ON project.offers USING btree (slug);


-- project.reviews definition

-- Drop table

-- DROP TABLE project.reviews;

CREATE TABLE project.reviews (
	id serial4 NOT NULL,
	rating int4 NOT NULL,
	"content" text NOT NULL,
	created_at timestamp NOT NULL DEFAULT now(),
	organizer_id int4 NOT NULL,
	artist_id int4 NOT NULL,
	CONSTRAINT reviews_pk PRIMARY KEY (id),
	CONSTRAINT reviews_fk_artists FOREIGN KEY (artist_id) REFERENCES project.artists(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT reviews_fk_organizers FOREIGN KEY (organizer_id) REFERENCES project.organizers(user_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE UNIQUE INDEX reviews_id_uindex ON project.reviews USING btree (id);


-- project.transactions definition

-- Drop table

-- DROP TABLE project.transactions;

CREATE TABLE project.transactions (
	id serial4 NOT NULL,
	"name" varchar NOT NULL,
	stripe_id varchar NOT NULL,
	stripe_price float4 NULL,
	created_at timestamp NOT NULL,
	updated_at timestamp NULL,
	offer_id int4 NOT NULL,
	invoice_id varchar NULL,
	CONSTRAINT transactions_pk PRIMARY KEY (id),
	CONSTRAINT transactions_fk_offer FOREIGN KEY (offer_id) REFERENCES project.offers(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE UNIQUE INDEX transactions_id_uindex ON project.transactions USING btree (id);
CREATE UNIQUE INDEX transactions_name_uindex ON project.transactions USING btree (name);


-- project.artist_images definition

-- Drop table

-- DROP TABLE project.artist_images;

CREATE TABLE project.artist_images (
	id serial4 NOT NULL,
	"path" varchar NOT NULL,
	created_at timestamp NOT NULL,
	updated_at timestamp NULL,
	artist_id int4 NOT NULL,
	CONSTRAINT images_pk PRIMARY KEY (id),
	CONSTRAINT artist_images_fk_artist FOREIGN KEY (artist_id) REFERENCES project.artists(user_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE UNIQUE INDEX images_id_uindex ON project.artist_images USING btree (id);


-- project.artist_sings_genres definition

-- Drop table

-- DROP TABLE project.artist_sings_genres;

CREATE TABLE project.artist_sings_genres (
	artist_id int4 NOT NULL,
	genre_id int4 NOT NULL,
	CONSTRAINT artist_sings_genres_fk_artist FOREIGN KEY (artist_id) REFERENCES project.artists(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT artist_sings_genres_fk_genre FOREIGN KEY (genre_id) REFERENCES project.genres(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);


-- project.offer_comments definition

-- Drop table

-- DROP TABLE project.offer_comments;

CREATE TABLE project.offer_comments (
	id serial4 NOT NULL,
	"content" text NOT NULL,
	created_at timestamp NOT NULL,
	updated_at timestamp NULL,
	author_id int4 NOT NULL,
	offer_id int4 NOT NULL,
	CONSTRAINT offer_comments_pk PRIMARY KEY (id),
	CONSTRAINT offer_comments_fk_author FOREIGN KEY (author_id) REFERENCES project.users(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
	CONSTRAINT offer_comments_fk_offer FOREIGN KEY (offer_id) REFERENCES project.offers(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
CREATE UNIQUE INDEX offer_comments_id_uindex ON project.offer_comments USING btree (id);