Index: scripts/ddl.sql
===================================================================
--- scripts/ddl.sql	(revision c807e22a23a43e4044ff83aa0fe6a9c8c59a7cb4)
+++ scripts/ddl.sql	(revision c807e22a23a43e4044ff83aa0fe6a9c8c59a7cb4)
@@ -0,0 +1,161 @@
+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,
+    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,
+    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)
+);