# *************************************************************
# MYSQL ASSIGNMENT
# NAMES:
# Brown, Shayne
# Logan, Justin
# Goncalves, Caio Victor
# *************************************************************

DROP DATABASE IF EXISTS assignment;
CREATE DATABASE assignment;

# select the database
USE assignment;

# create tables

CREATE TABLE contact
(
    CONTACT_ID      INT             NOT NULL    AUTO_INCREMENT  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 employees
(
    EMPLOYEE_ID     INT             NOT NULL    AUTO_INCREMENT PRIMARY KEY,
    SIN             VARCHAR(7),
    CONTACT_ID      INT,
    CONSTRAINT employees_fk_contact
        FOREIGN KEY(CONTACT_ID)
            REFERENCES contact (CONTACT_ID)
);

CREATE TABLE customer
(
    CUSTOMER_ID     INT             NOT NULL    AUTO_INCREMENT PRIMARY KEY,
    CONTACT_ID      INT,
    CONSTRAINT customer_fk_contact
        FOREIGN KEY (CONTACT_ID)
            REFERENCES contact (CONTACT_ID)
);

CREATE TABLE suppliers
(
    SUPPLIER_ID     INT             NOT NULL    AUTO_INCREMENT PRIMARY KEY,
    CONTACT_ID      INT,
    CONSTRAINT suppliers_fk_contact
        FOREIGN KEY(CONTACT_ID)
            REFERENCES contact (CONTACT_ID)
);

CREATE TABLE products
(
    PRODUCT_ID      INT             NOT NULL    AUTO_INCREMENT PRIMARY KEY,
    DESCRIPTION     VARCHAR(255),
    PRODUCT_NAME    VARCHAR(255)    NOT NULL,
    SUPPLIER_ID     INT,
    PRICE           DOUBLE,
    CONSTRAINT products_fk_suppliers
        FOREIGN KEY(SUPPLIER_ID)
            REFERENCES suppliers (SUPPLIER_ID)
);

CREATE TABLE invoices
(
    INVOICE_ID      INT             NOT NULL    AUTO_INCREMENT PRIMARY KEY,
    DESCRIPTION     VARCHAR(255),
    CUSTOMER_ID     INT             NOT NULL,
    CONSTRAINT invoices_fk_customer
        FOREIGN KEY (CUSTOMER_ID)
            REFERENCES customer (CUSTOMER_ID)
);

CREATE TABLE invoice_item
(
    PRODUCT_ID      INT             NOT NULL,
    INVOICE_ID      INT             NOT NULL,
    QUANTITY        INT,
    CONSTRAINT invoice_item_fk_products
        FOREIGN KEY(PRODUCT_ID)
            REFERENCES products (PRODUCT_ID),

    CONSTRAINT invoice_item_fk_invoices
        FOREIGN KEY (INVOICE_ID)
            REFERENCES invoices (INVOICE_ID),
    PRIMARY KEY(PRODUCT_ID, INVOICE_ID)
);

CREATE TABLE product_types
(
    PRODUCT_ID      INT             NOT NULL,
    DESCRIPTION     VARCHAR(255),
    CONSTRAINT product_types_fk_products
        FOREIGN KEY (PRODUCT_ID)
            REFERENCES products (PRODUCT_ID)
);

CREATE TABLE catering_event
(
    EVENT_ID        INT             NOT NULL    AUTO_INCREMENT PRIMARY KEY,
    EVENT_DATE      DATE,
    INVOICE_ID      INT             NOT NULL,
    CONSTRAINT catering_event_fk_invoices
        FOREIGN KEY (INVOICE_ID)
            REFERENCES invoices (INVOICE_ID)
);

CREATE TABLE sale
(
    SALE_ID         INT             NOT NULL    AUTO_INCREMENT PRIMARY KEY,
    SALE_DATE       DATE,
    INVOICE_ID      INT             NOT NULL,
    CONSTRAINT sale_fk_invoices
        FOREIGN KEY (INVOICE_ID)
            REFERENCES invoices (INVOICE_ID),
    EMPLOYEE_ID     INT             NOT NULL,
    CONSTRAINT sale_fk_employees
        FOREIGN KEY (EMPLOYEE_ID)
            REFERENCES employees (EMPLOYEE_ID)
);

CREATE TABLE delivery
(
    DELIVERY_ID     INT             NOT NULL    AUTO_INCREMENT PRIMARY KEY,
    DELIVERY_DATE   DATE,
    SALE_ID         INT             NOT NULL,
    CONSTRAINT delivery_fk_sale
        FOREIGN KEY (SALE_ID)
            REFERENCES sale (SALE_ID)
);

CREATE TABLE stock
(
    PRODUCT_ID      INT             NOT NULL,
    DELIVERY_ID     INT             NOT NULL,
    EXPIRATION      DATE,
    CONSTRAINT stock_fk_products
        FOREIGN KEY(PRODUCT_ID)
            REFERENCES products (PRODUCT_ID),

    CONSTRAINT stock_fk_delivery
        FOREIGN KEY(DELIVERY_ID)
            REFERENCES delivery(DELIVERY_ID),
    PRIMARY KEY(PRODUCT_ID, DELIVERY_ID)
);