Video streaming

CREATE TYPE title_kind AS ENUM ('movie', 'series');

CREATE TABLE titles (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    kind title_kind NOT NULL,
    name text NOT NULL,
    synopsis text,
    released_year int,
    maturity_rating text
);

CREATE TABLE seasons (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    title_id uuid NOT NULL,
    number int NOT NULL,
    UNIQUE (title_id, number)
);

CREATE TABLE episodes (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    season_id uuid NOT NULL,
    number int NOT NULL,
    name text NOT NULL,
    duration_seconds int NOT NULL,
    UNIQUE (season_id, number)
);

CREATE TABLE people (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE credits (
    title_id uuid NOT NULL,
    person_id uuid NOT NULL,
    role text NOT NULL,
    PRIMARY KEY (title_id, person_id, role)
);
CREATE INDEX credits_person_id_idx ON credits (person_id);

CREATE TABLE accounts (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    email text NOT NULL UNIQUE
);

CREATE TABLE profiles (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    account_id uuid NOT NULL,
    name text NOT NULL,
    kids boolean DEFAULT false NOT NULL
);
CREATE INDEX profiles_account_id_idx ON profiles (account_id);

CREATE TABLE watchlist (
    profile_id uuid NOT NULL,
    title_id uuid NOT NULL,
    added_at timestamptz DEFAULT now() NOT NULL,
    PRIMARY KEY (profile_id, title_id)
);
CREATE INDEX watchlist_title_id_idx ON watchlist (title_id);

CREATE TABLE watch_history (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    profile_id uuid NOT NULL,
    episode_id uuid,
    title_id uuid,
    position_seconds int DEFAULT 0 NOT NULL,
    watched_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX watch_history_profile_idx ON watch_history (profile_id, watched_at);
CREATE INDEX watch_history_episode_id_idx ON watch_history (episode_id);
CREATE INDEX watch_history_title_id_idx ON watch_history (title_id);

ALTER TABLE seasons ADD FOREIGN KEY (title_id) REFERENCES titles (id) ON DELETE CASCADE;

ALTER TABLE episodes ADD FOREIGN KEY (season_id) REFERENCES seasons (id) ON DELETE CASCADE;

ALTER TABLE credits ADD FOREIGN KEY (title_id) REFERENCES titles (id) ON DELETE CASCADE;

ALTER TABLE credits ADD FOREIGN KEY (person_id) REFERENCES people (id) ON DELETE CASCADE;

ALTER TABLE profiles ADD FOREIGN KEY (account_id) REFERENCES accounts (id) ON DELETE CASCADE;

ALTER TABLE watchlist ADD FOREIGN KEY (profile_id) REFERENCES profiles (id) ON DELETE CASCADE;

ALTER TABLE watchlist ADD FOREIGN KEY (title_id) REFERENCES titles (id) ON DELETE CASCADE;

ALTER TABLE watch_history ADD FOREIGN KEY (profile_id) REFERENCES profiles (id) ON DELETE CASCADE;

ALTER TABLE watch_history ADD FOREIGN KEY (episode_id) REFERENCES episodes (id) ON DELETE CASCADE;

ALTER TABLE watch_history ADD FOREIGN KEY (title_id) REFERENCES titles (id) ON DELETE CASCADE;