
CREATE TABLE tree (
	id 	serial PRIMARY KEY,
	parent 	int REFERENCES tree,
	l 	int DEFAULT NULL,
	r 	int DEFAULT NULL, 
);

-- Are these a good idea?
CREATE INDEX tree_l_idx ON tree (l);
CREATE INDEX tree_r_idx ON tree (r);

CREATE OR REPLACE FUNCTION handle_tree_insert() RETURNS opaque AS '
	DECLARE
		parentR INTEGER;
	BEGIN 
		IF NEW.l ISNULL THEN
			parentR := ( SELECT r FROM tree WHERE id = NEW.parent );
			UPDATE tree SET l=l+2 WHERE l >= parentR;
			UPDATE tree SET r=r+2 WHERE r >= parentR;
			NEW.l := parentR;
			NEW.r := parentR + 1;
		END IF;
		RETURN NEW;
	END;
' LANGUAGE PLPGSQL;	

CREATE TRIGGER on_tree_insert BEFORE INSERT
	ON tree 
	FOR EACH ROW
	EXECUTE PROCEDURE handle_tree_insert();
    

CREATE OR REPLACE FUNCTION handle_tree_delete() RETURNS opaque AS '
	DECLARE
		decr INTEGER;
	BEGIN 
		-- RAISE NOTICE ''Gets here, ID %'', OLD.id;
		IF NOT OLD.l ISNULL THEN
			decr := (((OLD.r - OLD.l - 1) / 2 ) + 1) * 2;
			-- RAISE NOTICE ''...and executes stuff (decrementing with %)'', decr;

			UPDATE tree SET parent = NULL, l = NULL, r = NULL WHERE l > OLD.l AND r < OLD.r;
			DELETE FROM tree WHERE parent ISNULL AND l ISNULL AND r ISNULL;

			UPDATE tree SET l = l - decr WHERE l > OLD.l;
			UPDATE tree SET r = r - decr WHERE r > OLD.r;
		END IF;
		RETURN OLD;
	END;
' LANGUAGE PLPGSQL;	

CREATE TRIGGER on_tree_delete AFTER DELETE
	ON tree 
	FOR EACH ROW
	EXECUTE PROCEDURE handle_tree_delete();

CREATE OR REPLACE FUNCTION handle_tree_update() RETURNS opaque AS '
	DECLARE
		parentR INTEGER;
		span INTEGER;
		dist INTEGER;
	BEGIN 
		IF (NOT OLD.parent ISNULL) AND (NOT NEW.parent ISNULL) AND (OLD.parent <> NEW.parent) THEN
			span := OLD.r - OLD.l + 1;

			-- Get the R value of the new parent
			parentR := ( SELECT r FROM tree WHERE id = NEW.parent );

			-- Determine the distance
			dist = parentR - OLD.l;

			-- Make the gab
			UPDATE tree SET l = l + span WHERE l >= parentR;
			UPDATE tree SET r = r + span WHERE r >= parentR;
	
			-- Move our branch to the newly created gab
			-- Decrement nodes after the old position to close the old gab
			-- If dist is < 0 then our own branch was moved <span> steps - this must be taken into consideration
			IF (dist > 0) 
				THEN UPDATE tree SET l = l + (dist), r = r + (dist) WHERE l >= OLD.l AND r <= OLD.r;
				UPDATE tree SET l = l - span WHERE l >= OLD.l; 
				UPDATE tree SET r = r - span WHERE r >= OLD.r;
			ELSE 
				UPDATE tree SET l = l + (dist - span), r = r + (dist - span) WHERE l >= (OLD.l + span) AND r <= (OLD.r + span);
				UPDATE tree SET l = l - span WHERE l >= (OLD.l + span); 
				UPDATE tree SET r = r - span WHERE r >= (OLD.r + span);
			END IF;

		END IF;
		RETURN NULL;
	END;
' LANGUAGE PLPGSQL;	

CREATE TRIGGER on_tree_update AFTER UPDATE
	ON tree 
	FOR EACH ROW
	EXECUTE PROCEDURE handle_tree_update();