DROP table orders cascade;
DROP table inventory cascade;
DROP table catalog cascade;
CREATE TABLE catalog (
	item_name	text PRIMARY KEY,
	item_description	text
);

CREATE TABLE inventory (
	item_name	text references catalog (item_name),
	in_stock	integer,
	price	numeric,
	st	timestamp default current_timestamp,
	et	timestamp,
	UNIQUE (item_name, st, et)
);
CREATE UNIQUE INDEX inventory_ridx ON inventory (item_name) WHERE et IS NULL;

CREATE VIEW current_inventory AS
   SELECT item_name, in_stock, price FROM inventory
   WHERE et IS NULL;

CREATE OR REPLACE FUNCTION inventory_at_time(timestamp with time zone) 
RETURNS SETOF current_inventory AS 
$$
   SELECT item_name, in_stock, price from inventory
      WHERE (SELECT CASE WHEN et IS NULL
            THEN (st <= $1)
            ELSE (st <= $1 AND et > $1)
            END) ;
$$ LANGUAGE 'SQL';

CREATE RULE inv_del AS ON DELETE TO inventory
   DO INSTEAD
	UPDATE inventory SET et=current_timestamp
   WHERE item_name = OLD.item_name AND et IS NULL;

CREATE OR REPLACE FUNCTION upd_inventory()
RETURNS TRIGGER AS
$$
 	BEGIN
   	-- RAISE NOTICE 'UPD_INV TRIGGER';
 	   IF OLD.et IS NOT NULL THEN -- NEW.et can be non-null
 	      RETURN NULL; -- return quietly to avoid notice on update et
 	   END IF;
 	   IF NEW.et IS NULL THEN
 	      INSERT INTO inventory VALUES 
				(OLD.item_name, OLD.in_stock, OLD.price, OLD.st, current_timestamp);
 	      NEW.st = current_timestamp;
 	   END IF;
 	   RETURN NEW;
 	END;
$$ LANGUAGE 'plpgsql';
 	
CREATE TRIGGER upd_inventory BEFORE UPDATE ON inventory
FOR EACH ROW EXECUTE PROCEDURE upd_inventory();

CREATE TABLE orders (
	order_id	SERIAL,
	order_date	date default current_date,
	vendor	text,
	item_name	text	references catalog (item_name),
	amount	integer,
	st	timestamp  default current_timestamp,
	et	timestamp,
	UNIQUE (order_id, st, et)
);
CREATE UNIQUE INDEX orders_ridx ON orders (order_id) WHERE et IS NULL;

CREATE VIEW current_orders AS
   SELECT order_id, order_date, vendor, amount, item_name
   FROM orders
   WHERE et IS NULL; 

CREATE OR REPLACE FUNCTION orders_at_time(timestamp without time zone) 
RETURNS SETOF current_orders AS 
$$
   SELECT order_id, order_date, vendor, amount, item_name
   FROM orders
   WHERE (SELECT CASE WHEN et IS NULL
         THEN (st <= $1)
         ELSE (st <= $1 AND et > $1)
         END) ;
$$
LANGUAGE 'SQL';


CREATE RULE ord_del AS ON DELETE TO orders
   DO INSTEAD
   UPDATE orders set et=current_timestamp
   WHERE order_id = OLD.order_id AND et IS NULL;

CREATE OR REPLACE FUNCTION upd_orders()
RETURNS TRIGGER AS
$$
   BEGIN
   	-- RAISE NOTICE 'UPD_ORDERS TRIGGER';
      IF OLD.et IS NOT NULL THEN -- NEW.et can be non-null
 	      RETURN NULL; -- return quietly to avoid notice on update et
      END IF;
      IF NEW.et IS NULL THEN
   		-- RAISE NOTICE 'UPD_ORDERS INSERT OLD CASE';
         INSERT INTO orders VALUES 
            (OLD.order_id, OLD.order_date, OLD.vendor, 
				 OLD.item_name, OLD.amount, OLD.st, current_timestamp);
         NEW.st = current_timestamp;
      END IF;
   	-- RAISE NOTICE 'UPD_ORDERS DEL CASE';
   	-- RAISE NOTICE 'NEW: order %  item % st % et % ', NEW.order_id, NEW.item_name, NEW.st, NEW.et;
      RETURN NEW;
   END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER upd_orders BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE PROCEDURE upd_orders();

---------------- application functions ------------------------------------

CREATE OR REPLACE FUNCTION receive_order(r_order_id integer) 
RETURNS integer AS 
$$
DECLARE
rowcount integer;
orec RECORD;
BEGIN
	-- preselect valid order record or raise error
	SELECT into orec order_id, item_name, amount from current_orders o
	WHERE o.order_id = r_order_id;
	IF NOT FOUND THEN
		RAISE EXCEPTION 'Cannot Receive Order % -- has order already been received?',
						 r_order_id;
	END IF;
-- insert or update inventory record
   LOOP
      UPDATE inventory SET in_stock = in_stock + orec.amount
      WHERE inventory.item_name = orec.item_name;
      IF FOUND THEN
      	DELETE FROM orders WHERE order_id = r_order_id;
         RETURN r_order_id;
      ELSE
         BEGIN
            INSERT INTO inventory VALUES
            	(orec.item_name, orec.amount, NULL);
         EXCEPTION WHEN unique_violation THEN
            -- do nothing: loop around
         END;
      END IF;
   END LOOP;
   RETURN -1;
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION sale(sale_item_name text, sale_amount integer) 
RETURNS void AS 
$$
update inventory set in_stock = in_stock - $2
where item_name = $1;
$$ LANGUAGE 'SQL';



---------------- data initialization ------------------------------------
-- initialize catalog
insert into catalog values ('widgets');
insert into catalog values ('thingies');
insert into catalog values ('whatchamacallits');
insert into catalog values ('thatstuff');
insert into catalog values ('thisstuff');

-- initialize inventory (optional)
insert into inventory values ('widgets', 30);
insert into inventory values ('thingies', 25);
insert into inventory values ('whatchamacallits', 50);
insert into inventory values ('thatstuff', 40);
insert into inventory values ('thisstuff', 60);

-- on create order(vendor, item_name, amount):
insert into orders values (default, default, 'WidgetsRUS', 'widgets', 100);
insert into orders values (default, default, 'Things4all', 'thingies', 200);
insert into orders values (default, default, 'WhatsGalore', 'whatchamacallits', 25);
insert into orders values (default, default, 'StuffInc', 'thatstuff', 50);
insert into orders values (default, default, 'StuffInc', 'thisstuff', 75);