up.sql

create table if not exists genres
(
	id uuid not null
		constraint genres_pkey
			primary key,
	tmdb_id integer,
	"name" text not null,
	created_at timestamp with time zone not null,
	updated_at timestamp with time zone not null
);

create table if not exists people
(
	id uuid not null
		constraint people_pkey
			primary key,
	imdb_id text,
	"name" text not null,
	created_at timestamp with time zone not null,
	updated_at timestamp with time zone not null
);

create table if not exists titles
(
	id uuid not null
		constraint titles_pkey
			primary key,
	imdb_id text,
	tmdb_id integer,
	tvdb_id integer,
	original_language text,
	runtime integer,
	year_start integer,
	year_end integer,
	created_at timestamp with time zone not null,
	updated_at timestamp with time zone not null,
    parent_id uuid
);

create table if not exists title_casts
(
	id uuid not null
		constraint title_casts_pkey
			primary key,
	category text,
	characters text [] not null,
	credit text,
	created_at timestamp with time zone not null,
	updated_at timestamp with time zone not null,
	title_id uuid not null
		constraint title_casts_title_id_fkey
			references titles
			    on delete set null
				on update cascade,
	person_id uuid not null
		constraint title_casts_person_id_fkey
			references people
			    on delete set null
				on update cascade
);

create table if not exists title_descriptions
(
	id uuid not null
		constraint title_descriptions_pkey
			primary key,
	region text,
	languages text [] not null,
	kind text not null,
	overview text not null,
	tagline text,
	created_at timestamp with time zone not null,
	updated_at timestamp with time zone not null,
	title_id uuid not null
		constraint title_descriptions_title_id_fkey
			references titles
			    on delete set null
				on update cascade
);

create table if not exists title_episodes
(
	id uuid not null
		constraint title_episodes_pkey
			primary key,
	season_number text,
	episode_number text,
	air_date date,
	created_at timestamp with time zone not null,
	updated_at timestamp with time zone not null,
	parent_id uuid not null
		constraint title_episodes_parent_id_fkey
			references titles
			    on delete set null
				on update cascade
);

alter table titles add constraint title_parent_id_fkey foreign key (parent_id)
    references title_episodes
    on delete set null
    on update cascade;

create index if not exists title_episodes_show_id_season_number_episode_number
	on title_episodes (parent_id, season_number, episode_number);

create index if not exists title_episodes_show_id_air_date
	on title_episodes (parent_id, air_date);

create table if not exists title_genres
(
	id uuid not null
		constraint title_genres_pkey
			primary key,
	title_id uuid not null
		constraint title_genres_title_id_fkey
			references titles
			    on delete set null
				on update cascade,
	genre_id uuid not null
		constraint title_genres_genre_id_fkey
			references genres
			    on delete set null
				on update cascade,
	created_at timestamp with time zone not null,
	updated_at timestamp with time zone not null
);

create table if not exists title_images
(
	id uuid not null
		constraint title_images_pkey
			primary key,
	kind text not null,
	language text not null,
	mime text not null,
	src text not null,
	created_at timestamp with time zone not null,
	updated_at timestamp with time zone not null,
	title_id uuid not null
		constraint title_images_title_id_fkey
			references titles
			    on delete set null
				on update cascade
);

create table if not exists title_media
(
	id uuid not null
		constraint title_media_pkey
			primary key,
	mime text not null,
	codecs text [] not null,
	languages text [] not null,
	src text not null,
	created_at timestamp with time zone not null,
	updated_at timestamp with time zone not null,
	title_id uuid not null
		constraint title_media_title_id_fkey
			references titles
			    on delete set null
				on update cascade
);

create table if not exists title_names
(
	id uuid not null
		constraint title_names_pkey
			primary key,
	region text,
	languages text [] not null,
	kind text not null,
	"name" text not null,
	created_at timestamp with time zone not null,
	updated_at timestamp with time zone not null,
	title_id uuid not null
		constraint title_names_title_id_fkey
			references titles
			    on delete set null
				on update cascade
);

create table if not exists title_ratings
(
	id uuid not null
		constraint title_ratings_pkey
			primary key,
	region text,
	certification text not null,
	created_at timestamp with time zone not null,
	updated_at timestamp with time zone not null,
	title_id uuid not null
		constraint title_ratings_title_id_fkey
			references titles
			    on delete set null
				on update cascade
);

create table if not exists title_subtitles
(
	id uuid not null
		constraint title_subtitles_pkey
			primary key,
	format text not null,
	language text,
	region text,
	specifier text,
	src text not null,
	created_at timestamp with time zone not null,
	updated_at timestamp with time zone not null,
	title_id uuid not null
		constraint title_subtitles_title_id_fkey
			references titles
			    on delete set null
				on update cascade
);