Music streaming catalog

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

CREATE TABLE albums (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    artist_id uuid NOT NULL,
    title text NOT NULL,
    released_on date
);
CREATE INDEX albums_artist_id_idx ON albums (artist_id);

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

CREATE TABLE tracks (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    album_id uuid NOT NULL,
    title text NOT NULL,
    track_number int NOT NULL,
    duration_seconds int NOT NULL,
    explicit boolean DEFAULT false NOT NULL,
    UNIQUE (album_id, track_number)
);

CREATE TABLE track_genres (
    track_id uuid NOT NULL,
    genre_id uuid NOT NULL,
    PRIMARY KEY (track_id, genre_id)
);
CREATE INDEX track_genres_genre_id_idx ON track_genres (genre_id);

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

CREATE TABLE playlists (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    listener_id uuid NOT NULL,
    name text NOT NULL,
    public boolean DEFAULT false NOT NULL
);
CREATE INDEX playlists_listener_id_idx ON playlists (listener_id);

CREATE TABLE playlist_tracks (
    playlist_id uuid NOT NULL,
    track_id uuid NOT NULL,
    position int NOT NULL,
    PRIMARY KEY (playlist_id, position)
);
CREATE INDEX playlist_tracks_track_id_idx ON playlist_tracks (track_id);

CREATE TABLE plays (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    listener_id uuid NOT NULL,
    track_id uuid NOT NULL,
    played_at timestamptz DEFAULT now() NOT NULL,
    seconds_played int NOT NULL
);
CREATE INDEX plays_listener_idx ON plays (listener_id, played_at);
CREATE INDEX plays_track_id_idx ON plays (track_id);

ALTER TABLE albums ADD FOREIGN KEY (artist_id) REFERENCES artists (id) ON DELETE CASCADE;

ALTER TABLE tracks ADD FOREIGN KEY (album_id) REFERENCES albums (id) ON DELETE CASCADE;

ALTER TABLE track_genres ADD FOREIGN KEY (track_id) REFERENCES tracks (id) ON DELETE CASCADE;

ALTER TABLE track_genres ADD FOREIGN KEY (genre_id) REFERENCES genres (id) ON DELETE CASCADE;

ALTER TABLE playlists ADD FOREIGN KEY (listener_id) REFERENCES listeners (id) ON DELETE CASCADE;

ALTER TABLE playlist_tracks ADD FOREIGN KEY (playlist_id) REFERENCES playlists (id) ON DELETE CASCADE;

ALTER TABLE playlist_tracks ADD FOREIGN KEY (track_id) REFERENCES tracks (id) ON DELETE CASCADE;

ALTER TABLE plays ADD FOREIGN KEY (listener_id) REFERENCES listeners (id) ON DELETE CASCADE;

ALTER TABLE plays ADD FOREIGN KEY (track_id) REFERENCES tracks (id) ON DELETE CASCADE;