DROP TRIGGER pos_trig ON pos;
--DROP TRIGGER roc_trig ON roc;


--DROP FUNCTION roc_trig();
--DROP FUNCTION rob_pos_trig();
DROP FUNCTION pos_trig();



-- item_type(dbid INT): return type of an item's dbid
-- --------------------------------------------------
--   Implementation is rather ugly, but it works
--
DROP FUNCTION item_type(INT);
CREATE FUNCTION item_type(INT) RETURNS CHAR AS '
    BEGIN
	IF (SELECT COUNT(*) FROM mcm WHERE dbid=$1)>0 THEN
	    RETURN ''mcm'';
	END IF;

	IF (SELECT COUNT(*) FROM rob WHERE dbid=$1)>0 THEN
	    RETURN ''rob'';
	END IF;

	IF (SELECT COUNT(*) FROM dcs WHERE dbid=$1)>0 THEN
	    RETURN ''dcs'';
	END IF;

	IF (SELECT COUNT(*) FROM ori WHERE dbid=$1)>0 THEN
	    RETURN ''ori'';
	END IF;

	IF (SELECT COUNT(*) FROM roc WHERE dbid=$1)>0 THEN
	    RETURN ''roc'';
	END IF;

	IF (SELECT COUNT(*) FROM pdb WHERE dbid=$1)>0 THEN
	    RETURN ''pdb'';
	END IF;

	IF (SELECT COUNT(*) FROM sm WHERE dbid=$1)>0 THEN
	    RETURN ''sm'';
	END IF;


	RETURN ''unknown'';
    END;
' LANGUAGE 'plpgsql';






DROP FUNCTION valid_pos (INT,INT,INT);
CREATE FUNCTION valid_pos (INT,INT,INT) RETURNS BOOL AS '
    DECLARE
	p_dbid	ALIAS FOR $1;
	p_loc  	ALIAS FOR $2;
	p_pos   ALIAS FOR $3;

	roctype	roc.type%TYPE;
    BEGIN

	-- Check if the position is valid for the given location
	-- -----------------------------------------------------
	--   Thinking about hardcoding this...

	--PERFORM * 
	--FROM valid_positions 
	--WHERE dbid=p_dbid AND loc=p_loc AND (p_pos BETWEEN min_pos AND max_pos);

	IF NOT FOUND THEN
	    RAISE NOTICE ''Invalid location: % -> %:%'', p_dbid, p_loc, p_pos;
	    RETURN false;
	END IF;

	-- Check MCM pos on ROB
	-- --------------------

	IF item_type(p_dbid)=''mcm'' AND item_type(p_loc)=''rob'' THEN

	    IF ((SELECT type from rob WHERE dbid=p_loc)=''1A'' AND p_pos>=17) OR
	       ((SELECT type from rob WHERE dbid=p_loc)=''1B'' AND p_pos>=17) OR	
	       ((SELECT type from rob WHERE dbid=p_loc)=''2B'' AND p_pos>=17) OR	
	       ((SELECT type from rob WHERE dbid=p_loc)=''3A'' AND p_pos>=18) OR	
	       ((SELECT type from rob WHERE dbid=p_loc)=''3B'' AND p_pos>=18) OR	
	       ((SELECT type from rob WHERE dbid=p_loc)=''4A'' AND p_pos>=17) OR	
	       ((SELECT type from rob WHERE dbid=p_loc)=''4B'' AND p_pos>=17) THEN

	    RAISE NOTICE ''Invalid location: % -> %:%'', p_dbid, p_loc, p_pos;
	    RETURN false;

	END IF;


	-- C0 chambers only carry 6 ROBS
	-- -----------------------------

	IF item_type(p_dbid)=''mcm'' AND item_type(p_loc)=''rob'' THEN

	    roctype = (SELECT type FROM roc WHERE dbid=p_loc);

	    IF substring(roctype FROM 3 FOR 2)=''C0'' 
		AND p_pos>=6 THEN

		RAISE NOTICE ''Invalid location: % -> %:%'', p_dbid, p_loc, p_pos;
	    	RETURN false;
	    END IF; 
	END IF;

	-- All checks passed, location seems to be ok
	-- ------------------------------------------

	RETURN true;

    END;
' LANGUAGE 'plpgsql';


CREATE FUNCTION pos_trig () RETURNS TRIGGER AS '
    DECLARE

	rowcount INTEGER;

    BEGIN
	

	-- Check update requests
	-- ---------------------

	IF TG_OP=''UPDATE'' THEN

	    -- Only allow updates to remove_time and remove_person
	    -- ---------------------------------------------------

	    IF  (OLD.loc_dbid      != NEW.loc_dbid) OR
		(OLD.loc_pos       != NEW.loc_pos) OR
		(OLD.ctime         != NEW.ctime) OR
		(OLD.dbid          != NEW.dbid) THEN

		RAISE EXCEPTION ''Editing of position history not allowed'';

	    END IF;


	    -- Checks passed, allow update!
	    -- ----------------------------

	END IF;

	IF TG_OP=''INSERT'' THEN

	    -- Make sure there is only one current entry for the item
	    -- ------------------------------------------------------

	    --UPDATE pos   
	--	SET is_current=false
	--	WHERE dbid=NEW.dbid AND ts;

	END IF;



	RETURN NEW;

    END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER pos_trig AFTER INSERT OR UPDATE ON pos
    FOR EACH ROW EXECUTE PROCEDURE pos_trig();




-- item_insert_pos(): insert a new item into the pos table with prod time/person/loc
-- --------------------------------------------------------------------------------- 
--   When a new item is created, it should always be inserted into the pos table,
--   so that the history can be tracked back to the production.
--
--   As the inherited tables don't seem to call the trigger of the base table,
--   we have to insert the trigger in all derivatives of item

--DROP TRIGGER item_insert_pos ON item;
--DROP TRIGGER item_insert_pos ON mcm;
--DROP TRIGGER item_insert_pos ON rob;
--DROP TRIGGER item_insert_pos ON dcs;
--DROP TRIGGER item_insert_pos ON ori;
--DROP TRIGGER item_insert_pos ON roc;
--DROP TRIGGER item_insert_pos ON pdb;
--DROP TRIGGER item_insert_pos ON sm;
--DROP FUNCTION item_insert_pos();

--CREATE FUNCTION item_insert_pos() RETURNS TRIGGER AS '
--    DECLARE
--	iid item.dbid%TYPE;

--    BEGIN
--	--iid = (SELECT dbid FROM item WHERE dbid=NEW.dbid);

--	--RAISE NOTICE ''dbid: %'',iid;

--	INSERT INTO pos(dbid,insert_time,insert_person,loc_other)
--		VALUES(NEW.dbid,NEW.prod_time,NEW.prod_person,NEW.prod_loc);


--	RETURN NEW;
--    END;
--' LANGUAGE 'plpgsql';

--CREATE TRIGGER item_insert_pos AFTER INSERT ON item
--    FOR EACH ROW EXECUTE PROCEDURE item_insert_pos();

--CREATE TRIGGER item_insert_pos AFTER INSERT ON mcm
--    FOR EACH ROW EXECUTE PROCEDURE item_insert_pos();

--CREATE TRIGGER item_insert_pos AFTER INSERT ON rob
--    FOR EACH ROW EXECUTE PROCEDURE item_insert_pos();

--CREATE TRIGGER item_insert_pos AFTER INSERT ON dcs
--    FOR EACH ROW EXECUTE PROCEDURE item_insert_pos();

--CREATE TRIGGER item_insert_pos AFTER INSERT ON ori
--    FOR EACH ROW EXECUTE PROCEDURE item_insert_pos();

--CREATE TRIGGER item_insert_pos AFTER INSERT ON roc
--    FOR EACH ROW EXECUTE PROCEDURE item_insert_pos();

--CREATE TRIGGER item_insert_pos AFTER INSERT ON pdb
--    FOR EACH ROW EXECUTE PROCEDURE item_insert_pos();

--CREATE TRIGGER item_insert_pos AFTER INSERT ON sm
--    FOR EACH ROW EXECUTE PROCEDURE item_insert_pos();

