source: scripts/ddl.sql@ 0519e8d

drop schema if exists project cascade;

create schema project;

SET search_path TO project;

DROP TABLE IF EXISTS ARTIST_CONTRIBUTIONS;
DROP TABLE IF EXISTS PLAYLIST_SONGS;
DROP TABLE IF EXISTS SAVED_PLAYLISTS;
DROP TABLE IF EXISTS REVIEWS;
DROP TABLE IF EXISTS LISTENS;
DROP TABLE IF EXISTS LIKES;
DROP TABLE IF EXISTS PERFORMS_AT;
DROP TABLE IF EXISTS FOLLOWS;
DROP TABLE IF EXISTS PLAYLISTS;
DROP TABLE IF EXISTS EVENTS;
DROP TABLE IF EXISTS SONGS;
DROP TABLE IF EXISTS ALBUMS;
DROP TABLE IF EXISTS MUSICAL_ENTITIES;
DROP TABLE IF EXISTS ARTISTS;
DROP TABLE IF EXISTS LISTENERS;
DROP TABLE IF EXISTS ADMINS;
DROP TABLE IF EXISTS NON_ADMIN_USERS;
DROP TABLE IF EXISTS USERS;


CREATE TABLE USERS (
    user_id BIGINT PRIMARY KEY,
    full_name TEXT NOT NULL, 
    email TEXT NOT NULL,
    password_hash TEXT NOT NULL,
    username TEXT NOT NULL, 
    profile_photo TEXT
);

CREATE TABLE NON_ADMIN_USERS (
    user_id BIGINT PRIMARY KEY REFERENCES USERS(user_id) ON DELETE CASCADE
);

CREATE TABLE ADMINS (
    user_id BIGINT PRIMARY KEY REFERENCES USERS(user_id) ON DELETE CASCADE
);

CREATE TABLE LISTENERS (
    user_id BIGINT PRIMARY KEY REFERENCES NON_ADMIN_USERS(user_id) ON DELETE CASCADE
);

CREATE TABLE ARTISTS (
    user_id BIGINT PRIMARY KEY REFERENCES NON_ADMIN_USERS(user_id) ON DELETE CASCADE
);

CREATE TABLE MUSICAL_ENTITIES (
    id BIGINT PRIMARY KEY,
    title TEXT NOT NULL,
    genre TEXT NOT NULL,
    release_date  DATE NOT NULL,
    released_by BIGINT REFERENCES ARTISTS(user_id) ON DELETE CASCADE
);


CREATE TABLE ALBUMS (
    id BIGINT PRIMARY KEY REFERENCES MUSICAL_ENTITIES(id) ON DELETE CASCADE
);

CREATE TABLE SONGS (
    id BIGINT PRIMARY KEY REFERENCES MUSICAL_ENTITIES(id) ON DELETE CASCADE,
    album_id BIGINT REFERENCES ALBUMS(id) ON DELETE CASCADE,
    link TEXT NOT NULL
);


CREATE TABLE EVENTS (
    event_id BIGINT PRIMARY KEY,
    name TEXT NOT NULL,
    location TEXT NOT NULL,
    venue  TEXT NOT NULL,
    date DATE NOT NULL,
    time TIME NOT NULL,
    creator_id BIGINT REFERENCES USERS(user_id) ON DELETE CASCADE
);

CREATE TABLE PLAYLISTS (
    playlist_id  BIGINT PRIMARY KEY,
    name   TEXT NOT NULL,
    created_by BIGINT REFERENCES LISTENERS(user_id) ON DELETE CASCADE
);

CREATE TABLE CONTRIBUTIONS (
    musical_entity_id BIGINT NOT NULL REFERENCES MUSICAL_ENTITIES(id) ON DELETE CASCADE,
    artist_id BIGINT NOT NULL REFERENCES ARTISTS(user_id) ON DELETE CASCADE,
    contribution_order INT NOT NULL,
 
    CONSTRAINT contributions_pk
        PRIMARY KEY (musical_entity_id, artist_id),
 
    CONSTRAINT contributions_order_positive
        CHECK (contribution_order > 0)
);

CREATE TABLE ROLES (
    role_id BIGINT PRIMARY KEY,
    name TEXT NOT NULL,
 
    CONSTRAINT roles_name_unique UNIQUE (name)
);

-- RELACII


CREATE TABLE FOLLOWS (
    follower BIGINT REFERENCES NON_ADMIN_USERS(user_id) ON DELETE CASCADE,
    followee BIGINT REFERENCES NON_ADMIN_USERS(user_id) ON DELETE CASCADE,

    CONSTRAINT follows_no_self_follow
        CHECK (follower <> followee),

    CONSTRAINT follows_pk
        PRIMARY KEY (follower, followee)
);

CREATE TABLE PERFORMS_AT (
    event_id BIGINT REFERENCES EVENTS(event_id) ON DELETE CASCADE,
    artist_id BIGINT REFERENCES ARTISTS(user_id) ON DELETE CASCADE,

    CONSTRAINT performs_at_pk
        PRIMARY KEY (event_id, artist_id)
);

CREATE TABLE LIKES (
    listener_id BIGINT REFERENCES LISTENERS(user_id) ON DELETE CASCADE,
    musical_entity_id BIGINT REFERENCES MUSICAL_ENTITIES(id) ON DELETE CASCADE,

    CONSTRAINT likes_pk
        PRIMARY KEY (listener_id, musical_entity_id)
);

CREATE TABLE LISTENS (
    listener_id BIGINT REFERENCES LISTENERS(user_id) ON DELETE CASCADE,
    song_id BIGINT REFERENCES SONGS(id) ON DELETE CASCADE,
    timestamp TIMESTAMP NOT NULL,

    CONSTRAINT listens_to_pk
        PRIMARY KEY (listener_id, song_id, timestamp)
);

CREATE TABLE REVIEWS (
    listener_id BIGINT REFERENCES LISTENERS(user_id) ON DELETE CASCADE,
    musical_entity_id BIGINT REFERENCES MUSICAL_ENTITIES(id) ON DELETE CASCADE,
    grade INT CHECK ( grade between 1 and 5) NOT NULL,
    comment TEXT,

    CONSTRAINT reviews_pk
        PRIMARY KEY (listener_id, musical_entity_id)
);

CREATE TABLE SAVED_PLAYLISTS (
    listener_id BIGINT REFERENCES LISTENERS(user_id) ON DELETE CASCADE,
    playlist_id BIGINT REFERENCES PLAYLISTS(playlist_id) ON DELETE CASCADE,

    CONSTRAINT saved_playlists_id
        PRIMARY KEY (listener_id, playlist_id)
);

CREATE TABLE PLAYLIST_SONGS (
    song_id BIGINT REFERENCES SONGS(id) ON DELETE CASCADE,
    playlist_id BIGINT REFERENCES PLAYLISTS(playlist_id) ON DELETE CASCADE,

    CONSTRAINT playlist_songs_id
        PRIMARY KEY (song_id, playlist_id)
);

CREATE TABLE CONTRIBUTION_ROLES (
    musical_entity_id BIGINT NOT NULL,
    artist_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL REFERENCES ROLES(role_id),
 
    CONSTRAINT contribution_roles_pk
        PRIMARY KEY (musical_entity_id, artist_id, role_id),
 
    CONSTRAINT contribution_roles_contribution_fk
        FOREIGN KEY (musical_entity_id, artist_id)
        REFERENCES CONTRIBUTIONS(musical_entity_id, artist_id)
        ON DELETE CASCADE
);