RelationalDesign: ddl-v3.sql

DROP SCHEMA IF EXISTS project CASCADE;

CREATE SCHEMA project;

SET search_path TO project;

DROP TABLE IF EXISTS MODIFICATION_PRODUCTS;
DROP TABLE IF EXISTS SINGLE_FEATURES;
DROP TABLE IF EXISTS SONG_ARTISTS;
DROP TABLE IF EXISTS RELEASE_ARTISTS;
DROP TABLE IF EXISTS ALBUM_SONGS;
DROP TABLE IF EXISTS WISHLIST_ITEMS;
DROP TABLE IF EXISTS ORDER_ITEMS;
DROP TABLE IF EXISTS MODIFICATIONS;
DROP TABLE IF EXISTS WISHLISTS;
DROP TABLE IF EXISTS ORDERS;
DROP TABLE IF EXISTS SONGS;
DROP TABLE IF EXISTS SINGLE_RELEASES;
DROP TABLE IF EXISTS ALBUMS;
DROP TABLE IF EXISTS PRODUCTS;
DROP TABLE IF EXISTS RELEASES;
DROP TABLE IF EXISTS ARTISTS;
DROP TABLE IF EXISTS CONSUMERS;
DROP TABLE IF EXISTS ADMINS;
DROP TABLE IF EXISTS USERS;

CREATE TYPE admin_type AS ENUM (
    'SUPER_ADMIN',
    'PRODUCT_MANAGER',
    'CONTENT_MANAGER'
);

CREATE TYPE product_format AS ENUM (
    'VINYL',
    'CD',
    'CASSETTE'
);

CREATE TYPE payment_method_type AS ENUM (
    'CARD',
    'PAYPAL',
    'CASH'
);

CREATE TYPE order_status_type AS ENUM (
    'PENDING',
    'PAID',
    'SHIPPED',
    'DELIVERED',
    'CANCELLED'
);

CREATE TYPE modification_type AS ENUM (
    'CREATE',
    'UPDATE',
    'DELETE',
    'DISCOUNT'
);

CREATE TYPE artist_release_type AS ENUM (
    'MAIN',
    'FEATURE'
);

-- USERS
CREATE TABLE USERS (
    user_id BIGINT PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    date_created DATE NOT NULL,
    shipping_address TEXT,
    telephone_number TEXT
);

-- ADMINS
CREATE TABLE ADMINS (
    user_id BIGINT PRIMARY KEY
        REFERENCES USERS(user_id) ON DELETE CASCADE,
    type admin_type NOT NULL,
    discount_percentage NUMERIC NOT NULL
);

-- CONSUMERS
CREATE TABLE CONSUMERS (
    user_id BIGINT PRIMARY KEY
        REFERENCES USERS(user_id) ON DELETE CASCADE,
    points_collected BIGINT NOT NULL
);

-- ARTISTS
CREATE TABLE ARTISTS (
    artist_id BIGINT PRIMARY KEY,
    artist_name TEXT NOT NULL,
    artist_description TEXT,
    artist_photo TEXT
);

-- RELEASES
CREATE TABLE RELEASES (
    release_id BIGINT PRIMARY KEY,
    title TEXT NOT NULL,
    record_label TEXT,
    genre TEXT NOT NULL,
    release_date DATE NOT NULL,
    cover_photo TEXT NOT NULL
);

-- ALBUMS
CREATE TABLE ALBUMS (
    release_id BIGINT PRIMARY KEY
        REFERENCES RELEASES(release_id) ON DELETE CASCADE
);

-- SINGLE RELEASES
CREATE TABLE SINGLE_RELEASES (
    release_id BIGINT PRIMARY KEY
        REFERENCES RELEASES(release_id) ON DELETE CASCADE,
    duration TEXT NOT NULL
);

-- SONGS
CREATE TABLE SONGS (
    song_id BIGINT PRIMARY KEY,
    song_name TEXT NOT NULL,
    song_duration TEXT NOT NULL
);

-- PRODUCTS
CREATE TABLE PRODUCTS (
    product_id BIGINT PRIMARY KEY,
    release_id BIGINT NOT NULL
        REFERENCES RELEASES(release_id) ON DELETE CASCADE,
    format product_format NOT NULL,
    price NUMERIC NOT NULL,
    product_description TEXT NOT NULL,
    stock BIGINT NOT NULL
);

-- ORDERS
CREATE TABLE ORDERS (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL
        REFERENCES USERS(user_id) ON DELETE CASCADE,
    payment_method payment_method_type NOT NULL,
    purchase_date DATE NOT NULL,
    points_earned BIGINT NOT NULL,
    points_used BIGINT,
    status order_status_type NOT NULL
);

-- MODIFICATIONS
CREATE TABLE MODIFICATIONS (
    modification_id BIGINT PRIMARY KEY,
    admin_id BIGINT NOT NULL
        REFERENCES ADMINS(user_id) ON DELETE CASCADE,
    date_modified DATE NOT NULL,
    type_of_modification modification_type NOT NULL,
    discount NUMERIC
);

-- WISHLISTS
CREATE TABLE WISHLISTS (
    wishlist_id BIGINT PRIMARY KEY,
    user_id BIGINT UNIQUE NOT NULL
        REFERENCES USERS(user_id) ON DELETE CASCADE
);


-- ORDER ITEMS
CREATE TABLE ORDER_ITEMS (
    order_id BIGINT NOT NULL
        REFERENCES ORDERS(order_id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL
        REFERENCES PRODUCTS(product_id) ON DELETE CASCADE,
    price_at_purchase NUMERIC NOT NULL,
    quantity BIGINT NOT NULL,

    CONSTRAINT order_items_pk
        PRIMARY KEY (order_id, product_id)
);

-- WISHLIST ITEMS
CREATE TABLE WISHLIST_ITEMS (
    wishlist_id BIGINT NOT NULL
        REFERENCES WISHLISTS(wishlist_id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL
        REFERENCES PRODUCTS(product_id) ON DELETE CASCADE,
    added_at DATE NOT NULL,

    CONSTRAINT wishlist_items_pk
        PRIMARY KEY (wishlist_id, product_id)
);

-- ALBUM SONGS
CREATE TABLE ALBUM_SONGS (
    album_id BIGINT NOT NULL
        REFERENCES ALBUMS(release_id) ON DELETE CASCADE,
    song_id BIGINT NOT NULL
        REFERENCES SONGS(song_id) ON DELETE CASCADE,

    CONSTRAINT album_songs_pk
        PRIMARY KEY (album_id, song_id)
);

-- RELEASE ARTISTS
CREATE TABLE RELEASE_ARTISTS (
    release_id BIGINT
        REFERENCES RELEASES(release_id) ON DELETE CASCADE,
    artist_id BIGINT
        REFERENCES ARTISTS(artist_id) ON DELETE CASCADE,
    release_ordinal BIGINT NOT NULL,
    type artist_release_type NOT NULL,

    CONSTRAINT release_artists_pk
        PRIMARY KEY (release_id, artist_id)
); 

-- SONG ARTISTS
CREATE TABLE SONG_ARTISTS (
    song_id BIGINT
        REFERENCES SONGS(song_id) ON DELETE CASCADE,
    artist_id BIGINT
        REFERENCES ARTISTS(artist_id) ON DELETE CASCADE,
    song_feature_ordinal BIGINT NOT NULL,

    CONSTRAINT song_artists_pk
        PRIMARY KEY (song_id, artist_id)
);

-- MODIFICATION PRODUCTS
CREATE TABLE MODIFICATION_PRODUCTS (
    modification_id BIGINT NOT NULL
        REFERENCES MODIFICATIONS(modification_id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL
        REFERENCES PRODUCTS(product_id) ON DELETE CASCADE,

    CONSTRAINT modification_products_pk
        PRIMARY KEY (modification_id, product_id)
);