schema.sql

-- Create a database called catalog_app
CREATE DATABASE catalog_app;

\c catalog_app;

-- create items table (parent class)
CREATE TABLE items (
  id SERIAL PRIMARY KEY,
  genre_id int REFERENCES genre(id),
  author_id int REFERENCES authors(id),
  label_id int REFERENCES label(id),
  publish_date DATE,
  archived BOOLEAN
);

-- create the games table (child class of Items)
CREATE TABLE games (
  id INT PRIMARY KEY,
  multiplayer BOOLEAN,
  last_played_at DATE REFERENCES item(publish_date),
  item_id int REFERENCES items(id)
);

-- create authors table
CREATE TABLE authors (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(255),
  last_name VARCHAR(255)
);

-- Add music_album table (child class of Items)
CREATE TABLE music_album(
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  on_spotify BOOLEAN,
  archived BOOLEAN,
  item_id int REFERENCES item(id)    
);

-- Add genre table
CREATE table genre (
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(255),
);

-- Add label table
CREATE TABLE label(
  id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  title varchar(100),
  color varchar(100)
);

-- Add book table
CREATE TABLE book(
  id int PRIMARY KEY REFERENCES item(id),
  publisher varchar(100),
  cover_state varchar(64),
  item_id int REFERENCES item(id)
);