#CONTACT TABLE=======================================================================
# 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)
# );

# INSERT INTO contact(PHONE_NUMBER, FIRST_NAME, LAST_NAME, ADDRESS, POSTAL_CODE, CITY)
# VALUES
#   ('613-555-0177', 'Leah', 'Tate', 'Street one', '000001', 'North Bay'),
#   ('613-555-0158', 'Harry', 'Parks', 'Street two', '000002', 'North Bay'),
#   ('613-555-0165', 'Jean', 'Barber', 'Street three', '000003', 'North Bay')
#   ;
#=====================================================================================

#EMPLOYEES TABLE======================================================================
# 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)
# );

# INSERT INTO employees(SIN, CONTACT_ID)
# VALUES
#   ('1234567', 1),
#   ('7654321', 2),
#   ('0000007', 3)
# ;
#======================================================================================

#CUSTOMER TABLE========================================================================
# 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)
# );

# INSERT INTO customer(CONTACT_ID)
# VALUES
#   (1),
#   (2),
#   (3)
# ;
#======================================================================================

#SUPPLIERS TABLE=======================================================================
# 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)
# );

# INSERT INTO suppliers(CONTACT_ID)
# VALUES
#   (1),
#   (2),
#   (3)
# ;
#=======================================================================================

#PRODUCTS TABLE ========================================================================
# 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)
# );

# INSERT INTO products(DESCRIPTION, PRODUCT_NAME, SUPPLIER_ID, PRICE)
# VALUES
#   ('FIRST PRODUCT', 'FIRST ONE', 1, 1.1),
#   ('SECOND PRODUCT', 'SECOND ONE', 2, 2.18),
#   ('THIRD PRODUCT', 'THIRD ONE', 3, 3.11)
# ;
#=======================================================================================

#INVOICES TABLE=========================================================================
# 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)
# );

# INSERT INTO invoices (DESCRIPTION, CUSTOMER_ID)
# VALUES
#   ('ONE PRODUCT WAS BOUGHT', 2),
#   ('THREE PRODUCTS WERE BOUGHT', 1),
#   ('TWO PRODUCTS WERE BOUGHT', 3)
# ;
#=======================================================================================

#INVOICE_ITEM TABLE ====================================================================
# 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)
# );

# INSERT INTO invoice_item (PRODUCT_ID, INVOICE_ID)
# VALUES
#   (1, 2),
#   (2, 2),
#   (3, 2)
# ;
#=======================================================================================

#CATERING EVENT TABLE ==================================================================
# 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)
# );

# INSERT INTO catering_event (EVENT_DATE, INVOICE_ID)
# VALUES
#   ("2017-06-15", 2),
#   ("2019-07-01", 1),
#   ("2016-03-16", 3)
# ;
#=======================================================================================

#SALE TABLE ============================================================================
# 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)
# );

# INSERT INTO sale (SALE_DATE, INVOICE_ID, EMPLOYEE_ID)
# VALUES
#   ("2017-06-15", 2, 1),
#   ("2019-07-01", 1, 2),
#   ("2016-03-16", 3, 3)
# ;
#=======================================================================================

# DELIVERY TABLE========================================================================
# 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)
# );

# INSERT INTO delivery (DELIVERY_DATE, SALE_ID)
# VALUES
#   ("2017-06-15", 2),
#   ("2019-07-01", 1),
#   ("2016-03-16", 3)
# ;
#========================================================================================

# STOCK TABLE ===========================================================================
# 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)
# );

# INSERT INTO stock (PRODUCT_ID, DELIVERY_ID, EXPIRATION)
# VALUES
#   (1, 2, "2025-08-25"),
#   (3, 2, "2031-04-05"),
#   (3, 3, "2027-03-15")
# ;
#=========================================================================================