DROP TABLE request;
DROP TABLE item;
DROP TABLE source;
DROP TABLE account;

CREATE TABLE account (
	id integer UNIQUE PRIMARY KEY NOT NULL,
	username varchar(8) NOT NULL,
	balance_cents integer NOT NULL,
	balance_bytes integer NOT NULL,
	permissions integer -- LSB bit 0 for coke, 1 for door
);
COPY account (id, username, balance_cents, balance_bytes, permissions) FROM stdin;
0	root	0	0	3
11002	dagobah	3722	0	1
\.

CREATE OR REPLACE FUNCTION in_coke(integer) RETURNS boolean AS
	'SELECT (permissions & 1) <> 0 FROM account WHERE id = $1'
	LANGUAGE sql;

CREATE OR REPLACE FUNCTION in_door(integer) RETURNS boolean AS
	'SELECT (permissions & 2) <> 0 FROM account WHERE id = $1'
	LANGUAGE sql;

CREATE TABLE source (
	id integer UNIQUE PRIMARY KEY NOT NULL,
	name varchar(8),
	handler varchar(20)
);
COPY source (id, name, "handler") FROM stdin;
1	coke	coke
2	door	door
\.

CREATE TABLE item (
	id integer UNIQUE PRIMARY KEY NOT NULL,
	source_id integer REFERENCES source(id),
	name varchar(20) NOT NULL,
	cost integer NOT NULL,
	data bytea,
	stock integer
);
COPY item (id, source_id, name, cost, data, stock) FROM stdin;
1	1	ginger beer	86	0	\N
2	1	dead	82	1	\N
3	1	kole beer	79	2	\N
4	1	random	70	3	\N
5	1	dead	86	4	\N
6	1	mountain dew	90	5	\N
7	1	coke	86	6	\N
8	1	give	0	\N	\N
9	1	acct	0	\N	\N
20	2	door	0	\N	\N
\.

CREATE TABLE request (
	requestor_id integer REFERENCES account(id),
	target_id integer REFERENCES account(id),
	item_id integer REFERENCES item(id),
	params bytea
);

CREATE OR REPLACE FUNCTION request_validate() RETURNS trigger AS '
	DECLARE
		account RECORD;
		item RECORD;
	BEGIN
		-- Check balance is +ve, or requestor is in coke
		SELECT INTO account * FROM account WHERE id = NEW.target_id;
		SELECT INTO item * FROM item WHERE id = NEW.item_id;
		IF item.cost IS NULL THEN
			RAISE EXCEPTION ''There is no such item with id %'',NEW.item_id;
		END IF;
		IF (account.balance_cents < item.cost) AND 
				NOT (in_coke(NEW.requestor_id)) THEN
			RAISE EXCEPTION ''Your balance is too low, and you are not in coke.'';
		END IF;
		RETURN NEW;
	END;
	' LANGUAGE plpgsql;
CREATE TRIGGER request_validate BEFORE INSERT ON request FOR EACH ROW
	EXECUTE PROCEDURE request_validate(); 

CREATE OR REPLACE FUNCTION request_do() RETURNS trigger AS '
	DECLARE
		itemrec RECORD;
	BEGIN
		SELECT INTO itemrec item.*, source.handler as handler FROM 
				item inner join source ON source_id = source.id WHERE item.id = NEW.item_id;

		-- Call the relevant function
		IF itemrec.handler = ''coke'' THEN
			PERFORM coke_request(itemrec.data, NEW.params);
		ELSIF itemrec.handler = ''door'' THEN
			PERFORM door_request(itemrec.data, NEW.params);
		END IF;


		UPDATE account SET balance_cents = balance_cents - itemrec.cost WHERE 
				id = NEW.target_id;

		IF itemrec.stock IS NOT NULL THEN
			IF itemrec.stock > 0 THEN
				UPDATE item SET stock = stock - 1 WHERE id = NEW.item_id;
			ELSE
				RAISE EXCEPTION ''We are out of stock of %'', itemrec.name;
			END IF;
		END IF;
		RETURN NULL;
	END;
	' LANGUAGE plpgsql;
CREATE TRIGGER request_do AFTER INSERT ON request FOR EACH ROW 
	EXECUTE PROCEDURE request_do(); 


CREATE OR REPLACE FUNCTION coke_request(bytea, bytea) RETURNS integer AS '
	my $item = shift;
	system("echo Dispensing $item >> /tmp/foobar");
	1;
	' LANGUAGE plperlu;
