DROP DATABASE IF EXISTS assignment;
CREATE DATABASE assignment;

# -- select the database
USE assignment;

# -- create products tables
CREATE TABLE products
(
    PRODUCT_ID INT PRIMARY KEY,
    DESCRIPTION VARCHAR(255),
    PRODUCT_NAME VARCHAR(255) NOT NULL,
    SUPPLIER_ID INT,
    FOREIGN KEY(SUPPLIER_ID)
    REFERENCES suppliers (SUPPLIER_ID)
);

CREATE TABLE product_types
(
    PRODUCT_ID INT PRIMARY KEY, #this will cause a problem
    DESCRIPTION VARCHAR(255),
    FOREIGN KEY (PRODUCT_ID)
    REFERENCES products (PRODUCT_ID)
);

CREATE TABLE invoices
(
    INVOICE_ID INT PRIMARY KEY,
    PRODUCT_ID INT,
    DESCRIPTION VARCHAR(255),
    CUSTOMER_ID INT,
    FOREIGN KEY (CUSTOMER_ID)
    REFERENCES customers (CUSTOMER_ID),

    FOREIGN KEY (PRODUCT_ID)
    REFERENCES products (PRODUCT_ID)
);

CREATE TABLE invoice_item
(
    PRODUCT_ID INT,
    INVOICE_ID INT,
    FOREIGN KEY(PRODUCT_ID)
    REFERENCES products (PRODUCT_ID),

    FOREIGN KEY (INVOICE_ID)
    REFERENCES invoices (INVOICE_ID)
);

CREATE TABLE customer
(
    CUSTOMER_ID INT PRIMARY KEY,
    CONTACT_ID INT,
    FOREIGN KEY (CONTACT_ID)
    REFERENCES contact (CONTACT_ID)
);

CREATE TABLE employees
(
    EMPLOYEE_ID INT PRIMARY KEY,
    SIN VARCHAR(7),
    CONTACT_ID INT,
    FOREIGN KEY(CONTACT_ID)
    REFERENCES contact (CONTACT_ID)
);

CREATE TABLE contact
(
    CONTACT_ID INT PRIMARY KEY,
    PHONE_NUMBER VARCHAR(10),
    FIRST_NAME VARCHAR(255) NOT NULL,
    LAST_NAME VARCHAR(255) NOT NULL,
    ADDRESS VARCHAR(255),
    POSTAL_CODE VARCHAR(6),
    CITY VARCHAR(255)
);

CREATE TABLE suppliers
(
    SUPLIER_ID INT PRIMARY KEY,
    CONTACT_ID INT,
    FOREIGN KEY(CONTACT_ID)
    REFERENCES contact (CONTACT_ID)
);

CREATE TABLE catering_event
(
    EVENT_ID INT PRIMARY KEY,
    EVENT_DATE DATE,
    INVOICE_ID INT,
    FOREIGN KEY (INVOICE_ID)
    REFERENCES invoices (INVOICE_ID)
);

CREATE TABLE delivery
(
    DELIVERY_ID INT PRIMARY KEY,
    DELIVERY_DATE DATE,
    SALE_ID INT,
    FOREIGN KEY (SALE_ID)
    REFERENCES sale (SALE_ID)
);

CREATE TABLE sale
(
    SALE_ID INT PRIMARY KEY,
    SALE_DATE DATE,
    INVOICE_ID INT,
    FOREIGN KEY (INVOICE_ID)
    REFERENCES invoices (INVOICE_ID)
);

CREATE TABLE stock
(
    PRODUCT_ID INT,
    SALE_ID INT,
    FOREIGN KEY(PRODUCT_ID)
    REFERENCES products (PRODUCT_ID),

    FOREIGN KEY(SALE_ID)
    REFERENCES sale (SALE_ID)
);