RelationalDesign: ddl-v2.sql

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 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,
--     modified_by_admin_id BIGINT REFERENCES ADMIN(user_id) ON DELETE SET NULL,
--     removed_by_admin_id BIGINT REFERENCES ADMIN(user_id) ON DELETE SET NULL,
    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_artist_id BIGINT REFERENCES ARTISTS(user_id) ON DELETE CASCADE,
    creator_admin_id BIGINT REFERENCES ADMINS (user_id) ON DELETE CASCADE,

    CONSTRAINT has_exactly_one_creator
        CHECK ((creator_artist_id is not null and creator_admin_id is null) OR
                (creator_artist_id is null and creator_admin_id is not null))
);

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

-- 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 ARTIST_CONTRIBUTIONS (
    artist_id BIGINT REFERENCES ARTISTS(user_id) ON DELETE CASCADE,
    musical_entity_id BIGINT REFERENCES MUSICAL_ENTITIES(id) ON DELETE CASCADE,
    role TEXT NOT NULL,

    CONSTRAINT artist_contribution_id
        PRIMARY KEY (artist_id, musical_entity_id)
);