CREATE TABLE CUSTOMERS (
	ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	FIRST_NAME VARCHAR(20),
	LAST_NAME VARCHAR(30),
        ADDRESS VARCHAR(30),
	CITY VARCHAR(30),
	STATE_ VARCHAR(30),
	ZIP VARCHAR(15),
	COUNTRY_ID INTEGER,
	PHONE VARCHAR(15),
	EMAIL_ADDRESS VARCHAR(50) 
)ENGINE=INNODB;


CREATE TABLE ORDERS (
    ID INTEGER NOT NULL AUTO_INCREMENT,
    CUSTOMER_ID INTEGER NOT NULL,
    ORDER_DATE DATE,
    PRODUCT_ID INTEGER NOT NULL,
    QUANTITY INTEGER NOT NULL,
    PRIMARY KEY(ID),
    FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(ID) ON DELETE CASCADE
)ENGINE=INNODB;
  
CREATE TABLE COUNTRIES (
    COUNTRY_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    COUNTRY VARCHAR(30) 
)ENGINE=INNODB;

INSERT INTO COUNTRIES (COUNTRY) VALUES ('Bulgaria'), ('Cameroon'), ('Peru');

ALTER TABLE CUSTOMERS    
ADD CONSTRAINT COUNTRIES_FK Foreign Key (COUNTRY_ID)
   REFERENCES COUNTRIES (COUNTRY_ID) ON DELETE SET NULL;
   
CREATE TABLE PRODUCTS (
    PRODUCT_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    PROD_TYPE VARCHAR(20),
    BRAND VARCHAR(20),
    MODEL VARCHAR(20),
    PRICE DECIMAL
)ENGINE=INNODB;

ALTER TABLE ORDERS    
ADD CONSTRAINT ORDERS_FK Foreign Key (PRODUCT_ID)
   REFERENCES PRODUCTS (PRODUCT_ID);
   
INSERT INTO PRODUCTS (PROD_TYPE, BRAND, MODEL, PRICE) VALUES ('insect repellent', 'Shriek', 'Bug Zapper 3000', '7.99'), ('party favors', 'Scream', 'Sparkly FunPak', '12.99');