CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY,
    user_name VARCHAR(255) NOT NULL,
    user_mail VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS albums (
    id UUID PRIMARY KEY,
    musicbrainz_id UUID UNIQUE,
    name VARCHAR(255) NOT NULL,
    extra_info VARCHAR(255),
    artist_id VARCHAR(255) NOT NULL,
    release_date DATE,
    album_art_url VARCHAR(255)
    FOREIGN KEY (artist_id) REFERENCES artists(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS artists (
    id UUID PRIMARY KEY,
    musicbrainz_id UUID UNIQUE,
    name VARCHAR(255) NOT NULL,

);

CREATE TABLE IF NOT EXISTS user_albums (
    user_id UUID PRIMARY KEY,
    album_id UUID,
    date_added TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    album_owned BOOLEAN NOT NULL DEFAULT FALSE,
    album_want BOOLEAN NOT NULL DEFAULT FALSE,
    album_condition VARCHAR(50) CHECK (album_condition IN ('Mint', 'Near Mint', 'Very Good Plus', 'Very Good', 'Good Plus', 'Good', 'Fair', 'Poor')),
    is_vinyl BOOLEAN NOT NULL DEFAULT FALSE,
    is_cd BOOLEAN NOT NULL DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE CASCADE,
    FOREIGN KEY (album_id) REFERENCES Albums(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS tags (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS artist_tags (
    artist_id UUID PRIMARY KEY,
    tag_id UUID,
    FOREIGN KEY (artist_id) REFERENCES Artists(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES Tags(id) ON DELETE CASCADE
);