-- ****************************************************************
-- ** DROP THE LOT
-- ****************************************************************

DROP TABLE "purchaseorder_line" CASCADE;
DROP SEQUENCE "purchaseorder_line_seq" CASCADE;
DROP TABLE "purchaseorder_line_status" CASCADE;
DROP TABLE "purchaseorder" CASCADE;
DROP SEQUENCE "purchaseorder_seq" CASCADE;
DROP TABLE "purchaseorder_status" CASCADE;

DROP FUNCTION po_stat(varchar) CASCADE;
DROP FUNCTION pol_stat(varchar) CASCADE;

DROP FUNCTION spawn_pol(int, int);
DROP TYPE spawned_pol_type;

DROP FUNCTION tr_purchaseorder_def() CASCADE;

DROP LANGUAGE 'plpgsql' CASCADE;
DROP FUNCTION plpgsql_call_handler() CASCADE;

CREATE FUNCTION plpgsql_call_handler() RETURNS LANGUAGE_HANDLER AS
	'$libdir/plpgsql' LANGUAGE C;

-- ****************************************************************
-- ** CREATE THE LOT
-- ****************************************************************

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
	HANDLER plpgsql_call_handler;

CREATE TABLE "purchaseorder_status" (
	"id" int primary key check(id > 0),
	"abbreviation" varchar(20) not null unique,
	"descr" varchar(30) not null
);
COPY "purchaseorder_status" FROM stdin;
1	PO_ONE	one
2	PO_TWO	two
3	PO_THREE	three
4	PO_FOUR	four
\.

CREATE SEQUENCE purchaseorder_seq start 3;

CREATE TABLE "purchaseorder" (
	"id" int primary key check(id > 0),
	"status_id" int not null references purchaseorder_status(id),
	"price_total" numeric not null check(price_total >= 0)
);
COPY "purchaseorder" FROM stdin;
1	1	0
2	1	0
\.

CREATE TABLE "purchaseorder_line_status" (
	"id" int primary key check(id > 0),
	"abbreviation" varchar(20) not null unique,
	"descr" varchar(30) not null
);
COPY "purchaseorder_line_status" FROM stdin;
1	POL_ONE	one
2	POL_TWO	two
3	POL_THREE	three
4	POL_FOUR	four
\.

CREATE SEQUENCE purchaseorder_line_seq start 6;

CREATE TABLE "purchaseorder_line" (
	"id" int primary key check(id > 0),
	"purchaseorder_id" int not null references purchaseorder(id),
	"status_id" int not null references purchaseorder_line_status(id),
	"amount" int not null check(amount > 0),
	"price" numeric check(price > 0)
);
COPY "purchaseorder_line" FROM stdin;
1	1	1	99	9.99
2	1	3	99	9.99
3	2	1	99	9.99
4	2	2	99	9.99
5	2	4	99	9.99
\.

CREATE OR REPLACE FUNCTION po_stat(varchar)
	RETURNS int
	LANGUAGE 'sql'
	IMMUTABLE
	STRICT
	SECURITY INVOKER
	AS '	SELECT id FROM purchaseorder_status WHERE abbreviation = $1';

CREATE OR REPLACE FUNCTION pol_stat(varchar)
	RETURNS int
	LANGUAGE 'sql'
	IMMUTABLE
	STRICT
	SECURITY INVOKER
	AS '	SELECT id FROM purchaseorder_line_status WHERE abbreviation = $1';

CREATE TYPE spawned_pol_type AS (pol_id_orig int, pol_id_new int);
CREATE OR REPLACE FUNCTION spawn_pol(integer, integer)
	RETURNS SETOF spawned_pol_type
	LANGUAGE 'plpgsql'
	VOLATILE
	STRICT
	SECURITY INVOKER
	AS '	DECLARE
			my_id ALIAS FOR $1;
			spawned_amount ALIAS FOR $2;
			result_size INTEGER;
			pol RECORD;
			result spawned_pol_type%ROWTYPE;
		BEGIN
			-- select the orderline to spawn
			SELECT into pol * FROM purchaseorder_line WHERE id = my_id FOR UPDATE;
			result.pol_id_orig = my_id;

			-- our amount must be high enough to be able to spawn
			IF spawned_amount >= pol.amount THEN
				RAISE EXCEPTION ''The original amount % in line % is not high enough to be able to spawn amount %'', pol.amount, spawned_amount, my_id;
				RETURN;
			END IF;

			-- we have enough stock, insert the new orderline and update the original one
			INSERT INTO purchaseorder_line SELECT
					nextval(''purchaseorder_line_seq''),		-- id
					purchaseorder_id,
					status_id,
					spawned_amount,					-- amount,
					price
				FROM purchaseorder_line WHERE id = my_id;
			result.pol_id_new = currval(''purchaseorder_line_seq'');
			UPDATE purchaseorder_line SET amount = amount - spawned_amount WHERE id = my_id;

			-- finish
			RETURN NEXT result;
			RETURN;
		END;';

CREATE or replace FUNCTION tr_purchaseorder_def()
	RETURNS trigger
	LANGUAGE 'plpgsql'
	VOLATILE
	STRICT
	SECURITY INVOKER
	AS '	DECLARE
			my_purchaseorder_id INTEGER := 0;
		BEGIN
			-- how are we called?
			IF TG_OP = ''INSERT'' THEN
				my_purchaseorder_id = NEW.purchaseorder_id;
			ELSIF TG_OP = ''UPDATE'' THEN
				my_purchaseorder_id = NEW.purchaseorder_id;
			ELSE -- TG_OP = ''DELETE''
				my_purchaseorder_id = OLD.purchaseorder_id;
			END IF;

			-- why are we processing?
			RAISE NOTICE ''tr_purchaseorder_def() was triggered by the % on % for id %'', TG_OP, TG_RELNAME, my_purchaseorder_id;

			-- update the purchaseorder
			UPDATE purchaseorder SET
				price_total = total,
				status_id = CASE
					WHEN count_lines = 0 OR count_pol_one > 0 THEN po_stat(''PO_ONE'')
					WHEN count_pol_two > 0 THEN po_stat(''PO_TWO'')
					WHEN count_pol_three > 0 THEN po_stat(''PO_THREE'')
					WHEN count_pol_four = count_lines THEN po_stat(''PO_FOUR'')
					ELSE po_stat(''PO_FOUR'') END
			FROM (
				SELECT
					purchaseorder_id,
					coalesce(SUM(price * amount), 0) AS total,
					COUNT(purchaseorder_line.id) AS count_lines,
					COUNT(CASE WHEN status_id = pol_stat(''POL_ONE'') THEN 1 ELSE null END) AS count_pol_one,
					COUNT(CASE WHEN status_id = pol_stat(''POL_TWO'') THEN 1 ELSE null END) AS count_pol_two,
					COUNT(CASE WHEN status_id = pol_stat(''POL_THREE'') THEN 1 ELSE null END) AS count_pol_three,
					COUNT(CASE WHEN status_id = pol_stat(''POL_FOUR'') THEN 1 ELSE null END) AS count_pol_four
				FROM purchaseorder_line
				WHERE purchaseorder_id = my_purchaseorder_id
				GROUP BY purchaseorder_id
			) AS foo
			WHERE id = purchaseorder_id;

			RETURN NULL;
		END;';

CREATE CONSTRAINT TRIGGER purchaseorder_def AFTER INSERT OR UPDATE OR DELETE ON purchaseorder_line DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE tr_purchaseorder_def();

-- ****************************************************************
-- ** DEFY THE LOT
-- ****************************************************************

-- calling spawn_pol() at least once will avoid the error
--select 1 from spawn_pol(1, 9);

-- this will fail when the line above is commented out
begin;
select 1 from spawn_pol(2, 9);
update purchaseorder_line set amount = amount where id = currval('purchaseorder_line_seq');
commit;