CREATE TABLE APP.ADDRESS (
     	ADDRESS_ID INTEGER NOT NULL AUTO_INCREMENT,
     	LINE1 VARCHAR(255),
     	LINE2 VARCHAR(255),
     	CITY VARCHAR(255),
     	STATE_PROV_CD VARCHAR(2),
     	POSTAL_CODE VARCHAR(9),
     	COUNTRY VARCHAR(255),
     	PRIMARY KEY (ADDRESS_ID)
     );
     
     CREATE TABLE APP.CUSTOMER (
     	CUSTOMER_ID INTEGER NOT NULL AUTO_INCREMENT,
     	FIRST_NAME VARCHAR(255),
     	LAST_NAME VARCHAR(255),
     	ADDRESS_ID INTEGER,
     	PRIMARY KEY (CUSTOMER_ID)
     );
     
     CREATE TABLE APP.DEALER (
     	DEALER_ID INTEGER NOT NULL AUTO_INCREMENT,
     	NAME VARCHAR(255),
     	ADDRESS_ID INTEGER,
     	REGION_ID INTEGER,
     	PRIMARY KEY (DEALER_ID)
     );
     
     CREATE TABLE APP.DISCOUNT_RATE (
     	DISCOUNT_RATE CHAR(1) NOT NULL,
     	RATE DOUBLE,
     	PRIMARY KEY (DISCOUNT_RATE)
     );
     
     CREATE TABLE APP.EMPLOYEE (
     	EMPLOYEE_ID INTEGER NOT NULL AUTO_INCREMENT,
     	FIRST_NAME VARCHAR(255),
     	LAST_NAME VARCHAR(255),
     	ROLE_CODE CHAR(1),
     	ADDRESS_ID INTEGER,
     	PRIMARY KEY (EMPLOYEE_ID)
     );
     
     CREATE TABLE APP.ENGINE (
     	ENGINE_ID INTEGER NOT NULL,
     	LITRE DOUBLE ,
     	CYLINDERS SMALLINT,
     	SUPERCHARGE SMALLINT,
     	TURBOCHARGE SMALLINT,
     	HYBRID SMALLINT,
     	INLINE SMALLINT,
     	VEE SMALLINT,
     	PRIMARY KEY (ENGINE_ID)
     );
     
     CREATE TABLE APP.PRODUCT (
     	PRODUCT_ID INTEGER NOT NULL,
     	NAME VARCHAR(255),
     	BODY_STYLE INTEGER,
     	ENGINE_ID INTEGER,
     	TRANSMISSION_ID INTEGER,
     	LENGTH DOUBLE ,
     	WIDTH DOUBLE ,
     	HEIGHT DOUBLE ,
     	COST DOUBLE,
     	PRICE DOUBLE,
     	MODEL_YEAR INTEGER,
     	PRODUCT_TYPE_ID INTEGER,
     	PRIMARY KEY (PRODUCT_ID)
     );
     
     CREATE TABLE APP.PRODUCT_TYPE (
     	PRODUCT_TYPE_ID INTEGER NOT NULL,
     	CLASS VARCHAR(255),
     	SUBCLASS VARCHAR(255),
     	PRIMARY KEY (PRODUCT_TYPE_ID)
     );
     
     CREATE TABLE APP.PROJECTED_SALES (
     	PROJECTED_SALES_ID INTEGER NOT NULL AUTO_INCREMENT,
     	SALES_YEAR SMALLINT,
     	QUARTER SMALLINT,
     	EMPLOYEE_ID INTEGER,
     	REGION_ID INTEGER,
     	DEALER_ID INTEGER,
     	PROJECTED_SALES DOUBLE,
     	PRIMARY KEY (PROJECTED_SALES_ID)
     );
     
     CREATE TABLE APP.REGION (
     	REGION_ID INTEGER NOT NULL,
     	NAME VARCHAR(255),
     	INTERNATIONAL SMALLINT NOT NULL,
     	START_ZONE INTEGER NOT NULL,
     	END_ZONE INTEGER NOT NULL,
     	PRIMARY KEY (REGION_ID)
     );
     
     CREATE TABLE APP.ROLE (
     	ROLE_CODE CHAR(1) NOT NULL,
     	DESCRIPTION VARCHAR(255),
     	PRIMARY KEY (ROLE_CODE)
     );
     
     CREATE TABLE APP.SALES_ORDER (
     	ORDER_ID INTEGER NOT NULL AUTO_INCREMENT,
     	DATE TIMESTAMP,
     	CUSTOMER_ID INTEGER,
     	REGION_ID INTEGER,
     	DEALER_ID INTEGER,
     	EMPLOYEE_ID INTEGER,
     	CHANNEL VARCHAR(255),
     	PRIMARY KEY (ORDER_ID)
     );
     
     CREATE TABLE APP.SALES_ORDER_LINE (
     	ORDER_LINE_ID INTEGER NOT NULL AUTO_INCREMENT,
     	ORDER_ID INTEGER,
     	PRODUCT_ID INTEGER,
     	DISCOUNT_RATE CHAR(1),
     	QUANTITY INTEGER,
     	PRIMARY KEY (ORDER_LINE_ID)
     );
     
     CREATE TABLE APP.TRANSMISSION (
     	TRANSMISSION_ID INTEGER NOT NULL,
     	TYPE CHAR(4) DEFAULT 'AUTO' NOT NULL,
     	GEARS SMALLINT DEFAULT 6,
     	PRIMARY KEY (TRANSMISSION_ID)
     );
     
     CREATE TABLE APP.ZIP_CITY_INFO (
     	ZIP INTEGER NOT NULL,
     	CITY VARCHAR(64),
     	STATE CHAR(2),
     	LONGITUDE DOUBLE,
     	LATITUDE DOUBLE,
     	TIMEZONE SMALLINT,
     	PRIMARY KEY (ZIP)
     );
     
     ALTER TABLE APP.CUSTOMER
     	ADD FOREIGN KEY (ADDRESS_ID) 
     	REFERENCES APP.ADDRESS (ADDRESS_ID);
     
     
     
     ALTER TABLE APP.DEALER
     	ADD FOREIGN KEY (ADDRESS_ID) 
     	REFERENCES APP.ADDRESS (ADDRESS_ID);
     
     ALTER TABLE APP.DEALER
     	ADD FOREIGN KEY (REGION_ID) 
     	REFERENCES APP.REGION (REGION_ID);
     
     
     
     ALTER TABLE APP.EMPLOYEE
     	ADD FOREIGN KEY (ADDRESS_ID) 
     	REFERENCES APP.ADDRESS (ADDRESS_ID);
     
     ALTER TABLE APP.EMPLOYEE
     	ADD FOREIGN KEY (ROLE_CODE) 
     	REFERENCES APP.ROLE (ROLE_CODE);
     
     
     
     ALTER TABLE APP.PRODUCT
     	ADD FOREIGN KEY (ENGINE_ID) 
     	REFERENCES APP.ENGINE (ENGINE_ID);
     
     ALTER TABLE APP.PRODUCT
     	ADD FOREIGN KEY (PRODUCT_TYPE_ID) 
     	REFERENCES APP.PRODUCT_TYPE (PRODUCT_TYPE_ID);
     
     ALTER TABLE APP.PRODUCT
     	ADD FOREIGN KEY (TRANSMISSION_ID) 
     	REFERENCES APP.TRANSMISSION (TRANSMISSION_ID);
     
     
     
     ALTER TABLE APP.PROJECTED_SALES
     	ADD FOREIGN KEY (DEALER_ID) 
     	REFERENCES APP.DEALER (DEALER_ID);
     
     ALTER TABLE APP.PROJECTED_SALES
     	ADD FOREIGN KEY (EMPLOYEE_ID) 
     	REFERENCES APP.EMPLOYEE (EMPLOYEE_ID);
     
     ALTER TABLE APP.PROJECTED_SALES
     	ADD FOREIGN KEY (REGION_ID) 
     	REFERENCES APP.REGION (REGION_ID);
     
     
     
     ALTER TABLE APP.SALES_ORDER
     	ADD FOREIGN KEY (CUSTOMER_ID) 
     	REFERENCES APP.CUSTOMER (CUSTOMER_ID);
     
     
     ALTER TABLE APP.SALES_ORDER
     	ADD FOREIGN KEY (DEALER_ID) 
     	REFERENCES APP.DEALER (DEALER_ID);
     
     
     ALTER TABLE APP.SALES_ORDER
     	ADD FOREIGN KEY (EMPLOYEE_ID) 
     	REFERENCES APP.EMPLOYEE (EMPLOYEE_ID);
     
     
     ALTER TABLE APP.SALES_ORDER
     	ADD FOREIGN KEY (REGION_ID) 
     	REFERENCES APP.REGION (REGION_ID);
     
     
     
     ALTER TABLE APP.SALES_ORDER_LINE
     	ADD FOREIGN KEY (DISCOUNT_RATE) 
     	REFERENCES APP.DISCOUNT_RATE (DISCOUNT_RATE);
     
     ALTER TABLE APP.SALES_ORDER_LINE
     	ADD FOREIGN KEY (PRODUCT_ID) 
     	REFERENCES APP.PRODUCT (PRODUCT_ID);
     
     ALTER TABLE APP.SALES_ORDER_LINE
     	ADD FOREIGN KEY (ORDER_ID) 
     	REFERENCES APP.SALES_ORDER (ORDER_ID);
     
     
     
     CREATE VIEW APP.FULL_PRODUCT_LISTING AS SELECT
     	P.PRODUCT_ID,
     	P.NAME,
     	PT.CLASS,
     	PT.SUBCLASS, 
     	P.LENGTH,
     	P.WIDTH,
     	P.HEIGHT,
     	P.COST,
     	P.PRICE,
     	P.MODEL_YEAR,
     	E.CYLINDERS,
     	E.LITRE,
     	E.INLINE,
     	E.VEE,
     	E.HYBRID,
     	E.SUPERCHARGE,
     	E.TURBOCHARGE,
     	T.TYPE,
     	T.GEARS
     FROM 
     	APP.PRODUCT AS P
     INNER JOIN
     	APP.ENGINE E on E.ENGINE_ID = P.ENGINE_ID
     INNER JOIN
     	APP.TRANSMISSION T on T.TRANSMISSION_ID = P.TRANSMISSION_ID
     INNER JOIN
     	APP.PRODUCT_TYPE PT on P.PRODUCT_TYPE_ID = PT.PRODUCT_TYPE_ID
     ORDER BY
     PRODUCT_ID;
     
     CREATE VIEW APP.LIVE_SALES_LIST AS SELECT 
     	SOL.ORDER_LINE_ID,
     	P.NAME AS PRODUCT,
     	PT.CLASS AS TYPE,
     	PT.SUBCLASS AS SUBTYPE,
     	P.PRICE AS PRICE,
     	R.NAME AS REGION,
     	SO.CHANNEL AS CHANNEL,	
     	SOL.QUANTITY AS QUANTITY,
     	D.NAME AS DEALER,
     	E.LAST_NAME AS SALESMAN,
     	A.CITY AS CITY,
     	A.STATE_PROV_CD AS STATE,
     	Z.LATITUDE,
     	Z.LONGITUDE,
     	SO.DATE,
             P.PRODUCT_TYPE_ID
      FROM APP.SALES_ORDER_LINE SOL
     	 LEFT OUTER JOIN APP.SALES_ORDER SO ON SO.ORDER_ID = SOL.ORDER_ID
     	 LEFT OUTER JOIN APP.REGION R ON R.REGION_ID = SO.REGION_ID
     	 LEFT OUTER JOIN APP.DEALER D ON SO.DEALER_ID = D.DEALER_ID
     	 LEFT OUTER JOIN APP.PRODUCT P ON P.PRODUCT_ID = SOL.PRODUCT_ID
     	 LEFT OUTER JOIN APP.PRODUCT_TYPE PT ON PT.PRODUCT_TYPE_ID = P.PRODUCT_TYPE_ID
     	 LEFT OUTER JOIN APP.EMPLOYEE E ON E.EMPLOYEE_ID = SO.EMPLOYEE_ID
     	 LEFT OUTER JOIN APP.CUSTOMER C ON C.CUSTOMER_ID = SO.CUSTOMER_ID
     	 LEFT OUTER JOIN APP.ADDRESS A ON A.ADDRESS_ID = C.ADDRESS_ID
     	 LEFT OUTER JOIN APP.ZIP_CITY_INFO Z ON Z.ZIP = CAST(A.POSTAL_CODE AS SIGNED INTEGER)
     WHERE R.INTERNATIONAL = 0
     ORDER BY DATE DESC;
     
     CREATE INDEX ZIPPREFIXSTARTIDX ON APP.REGION(START_ZONE);
     CREATE INDEX ZIPPREFIXENDIDX ON APP.REGION(END_ZONE);
     
     CREATE TABLE APP.DAILY_SALES (
         DAILY_SALES_ID INTEGER NOT NULL AUTO_INCREMENT,
         REGION_ID INTEGER,
         PRODUCT_ID INTEGER,
         STATE_PROV_CD VARCHAR(2),
         QUANTITY INTEGER,
         DATE DATE,
         PRIMARY KEY(DAILY_SALES_ID)
     );
     
     CREATE INDEX DAILYSALESSTATEIDX ON APP.DAILY_SALES(STATE_PROV_CD);
     CREATE INDEX DAILYDATEIDX ON APP.DAILY_SALES(DATE);
     
     ALTER TABLE APP.DAILY_SALES
     	ADD FOREIGN KEY (PRODUCT_ID) 
     	REFERENCES APP.PRODUCT (PRODUCT_ID);
     
     ALTER TABLE APP.DAILY_SALES
     	ADD FOREIGN KEY (REGION_ID) 
     	REFERENCES APP.REGION (REGION_ID);