RelationalDesign: ddl-v1.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 WISHLIST_ITEMS;
DROP TABLE IF EXISTS ORDER_ITEMS;
DROP TABLE IF EXISTS SONG_ARTISTS;
DROP TABLE IF EXISTS RELEASE_ARTISTS;
DROP TABLE IF EXISTS ALBUM_SONGS;
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'
);

-- 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,
    description TEXT,
    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,
    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,
    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
);
-- RELATIONS
-- ORDER ITEMS

CREATE TABLE ORDER_ITEMS (
    order_id BIGINT
        REFERENCES ORDERS(order_id) ON DELETE CASCADE,

    product_id BIGINT
        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
        REFERENCES WISHLISTS(wishlist_id) ON DELETE CASCADE,

    product_id BIGINT
        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
        REFERENCES ALBUMS(release_id) ON DELETE CASCADE,

    song_id BIGINT
        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,

    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,

    CONSTRAINT song_artists_pk
        PRIMARY KEY (song_id, artist_id)
);

-- MODIFICATION PRODUCTS

CREATE TABLE MODIFICATION_PRODUCTS (
    modification_id BIGINT
        REFERENCES MODIFICATIONS(modification_id) ON DELETE CASCADE,

    product_id BIGINT
        REFERENCES PRODUCTS(product_id) ON DELETE CASCADE,

    CONSTRAINT modification_products_pk
        PRIMARY KEY (modification_id, product_id)
);