/* 2007 Assignment 1 example solution, schema creation */
/* Note heavy use of views for UML derived attributes */

/* modified 17:34, 14 Aug 08, GregO:
   fix packing time for large envelope
   remove unnecessary grouping in orderPackage view
*/

CREATE TABLE product (
	grams integer,
	stock integer,
	productID varchar,
	PRIMARY KEY (productID)
);


/* plural is poor form, but order is an SQL keyword */
CREATE TABLE orders (
	orderID integer PRIMARY KEY
);

CREATE TABLE productOrder (
	quantity integer,
	productID varchar,
	orderID integer,
	PRIMARY KEY (productID, orderID),
	FOREIGN KEY (productID) REFERENCES product(productID),
	FOREIGN KEY (orderID) REFERENCES orders(orderID)
);

CREATE VIEW orderWeight
AS 	SELECT orderID, sum(quantity*grams) AS weight
	FROM productOrder NATURAL JOIN product
	GROUP BY orderID;

CREATE VIEW productOrderPickTime
AS	SELECT orderID, productID, 
		CASE WHEN quantity > 10 THEN interval '30 sec' 
		ELSE interval '15 sec' END AS picktime
	FROM productOrder;

CREATE VIEW orderPackage
AS	SELECT orderID, CASE 
		WHEN weight < 100 THEN 'small envelope'
		WHEN weight < 500 THEN 'large envelope'
		ELSE 'boxes' END AS package
	FROM orderWeight;

CREATE VIEW orderDerived
AS 	SELECT orderID, package, sum(picktime) AS picktime, CASE
		WHEN package = 'small envelope' THEN interval '30 sec'
		WHEN package = 'large envelope' THEN interval '30 sec'
		ELSE interval '2 min' END AS packtime
	FROM orderWeight NATURAL JOIN orderPackage NATURAL JOIN productOrderPickTime
	GROUP BY orderID, package;
	
	
CREATE VIEW productStockOrdered
AS	SELECT productID, stock, sum(quantity) as ordered
	FROM product NATURAL JOIN productOrder
	GROUP BY productID, stock;