/*
 * Table: pages
 */

CREATE TABLE pages (
	id				uuid			PRIMARY KEY REFERENCES nodes ( id )
									DEFERRABLE INITIALLY DEFERRED,
	main_id			uuid			NOT NULL REFERENCES pages ( id ) ON DELETE CASCADE
									DEFERRABLE INITIALLY DEFERRED,
	r				boolean,
	w				boolean,
	status			node_status		NOT NULL DEFAULT 'draft',
	published		timestamp(0) with time zone,
	modified		timestamp(0) with time zone
									NOT NULL DEFAULT now(),
	user_id			uuid			REFERENCES users ( id ) ON DELETE SET NULL,
	parent_id		uuid			REFERENCES pages ( id ),
	order_id		int				NOT NULL,
	lft				int,
	rgt				int,
	depth			int,
	sort_num		int[],
	key				varchar			NOT NULL DEFAULT '',
	title			varchar			NOT NULL,
	sort_name		varchar			NOT NULL,
	scan_name		varchar(1)		NOT NULL,
	abstract		text			NOT NULL DEFAULT '',
	content			text			NOT NULL DEFAULT '',
	tsv				tsvector		NOT NULL,
	memo			text			NOT NULL DEFAULT '',
	CHECK ( key <> '' OR status <> 'active' ),
	CHECK ( title <> '' ),
	CHECK ( order_id > 0 ),
	CHECK ( lft IS NULL AND rgt IS NULL AND sort_num IS NULL AND depth IS NULL
		OR lft IS NOT NULL AND lft > 0 AND rgt IS NOT NULL AND rgt > 0 AND sort_num IS NOT NULL AND sort_num >= '{1}'
		),
	CHECK ( r IS NULL AND w IS NULL OR r IS NOT NULL AND w IS NOT NULL ),
	CHECK ( NOT ( parent_id IS NULL AND r IS NULL ) ),
	CHECK ( NOT ( w AND NOT r ) ),
	CONSTRAINT pages_main_id_key UNIQUE ( main_id, id ),
	CONSTRAINT pages_valid_main_id_fkey	FOREIGN KEY ( main_id, main_id )
									REFERENCES pages ( main_id, id ) ON DELETE CASCADE
									DEFERRABLE INITIALLY DEFERRED,
	CONSTRAINT pages_valid_parent_id_fkey FOREIGN KEY ( parent_id, parent_id )
									REFERENCES pages ( main_id, id ) ON DELETE CASCADE
									DEFERRABLE INITIALLY DEFERRED
) WITH ( FILLFACTOR = 90 );

CREATE UNIQUE INDEX pages_key_key ON pages ( key, parent_id )
WHERE	id = main_id AND key <> '';

CREATE INDEX pages_sort ON pages ( sort_name );
CREATE INDEX pages_scan ON pages ( scan_name, sort_name );
CREATE INDEX pages_tree ON pages ( lft, rgt DESC );
CREATE INDEX pages_tsv ON pages USING GIN ( tsv );

CREATE INDEX pages_parent ON pages ( parent_id ) WHERE id = main_id;
CREATE INDEX pages_latest ON pages ( main_id ) WHERE status <> 'revision';
CREATE INDEX pages_modified ON pages ( modified DESC, main_id );

CREATE INDEX pages_current ON pages ( id ) WHERE id = main_id;
CREATE INDEX pages_current_tree ON pages ( lft, rgt DESC ) WHERE id = main_id;

CREATE TRIGGER __insert_page BEFORE INSERT ON pages
	FOR EACH ROW EXECUTE PROCEDURE __insert_node('page');

CREATE TRIGGER __delete_page AFTER DELETE ON pages
	FOR EACH ROW EXECUTE PROCEDURE __delete_node();


/*
 * Function: page_children(parent_id)
 */

CREATE OR REPLACE FUNCTION page_children(uuid)
	RETURNS SETOF tree_nodes
AS $$
DECLARE
	p_id	alias for $1;
	branch	tree_nodes;
	leaf	tree_nodes;
	i		int := 0;
	max_i	int;
	num		int := 0;
BEGIN
	FOR branch IN
	SELECT	main_id as id,
			parent_id,
			0 as order_id,
			0 as lft,
			0 as rgt,
			'{}' as sort_num
	FROM	pages
	WHERE	id = main_id
	AND		parent_id = p_id
	ORDER BY order_id
	LOOP
		num := num + 1;
	
		branch.sort_num[1] = num;
	
		i := i + 1;
		branch.lft := i;
	
		SELECT	rgt
		INTO	max_i
		FROM	page_children(branch.id)
		ORDER BY rgt DESC
		LIMIT 1;
	
		IF max_i IS NULL
		THEN
			branch.rgt := i + 1;
	
			RETURN NEXT branch;
		
			i := i + 1;
		ELSE
			branch.rgt := i + max_i + 1;
	
			RETURN NEXT branch;
		
			FOR leaf IN
			SELECT	id,
					parent_id,
					order_id,
					lft + i,
					rgt + i,
					array_prepend(num, sort_num) as sort_num
			FROM	page_children(branch.id)
			LOOP
				RETURN NEXT leaf;
			END LOOP;
		
			i := i + max_i + 1;
		END IF;
	END LOOP;

	RETURN;
END;
$$ LANGUAGE plpgsql STABLE STRICT;


/*
 * Function: page_tree()
 */

CREATE OR REPLACE FUNCTION page_tree()
	RETURNS SETOF tree_nodes
AS $$
DECLARE
	branch	tree_nodes;
	leaf	tree_nodes;
	o_id	int := 0;
	i		int := 0;
	max_i	int;
	num		int := 0;
BEGIN
	FOR branch IN
	SELECT	main_id as id,
			parent_id,
			0 as order_id,
			0 as lft,
			0 as rgt,
			'{}' as sort_num
	FROM	pages
	WHERE	parent_id IS NULL
	AND		id = main_id
	ORDER BY order_id
	LOOP
		num := num + 1;
		o_id := o_id + 1;
		i := i + 1;
	
		branch.sort_num[1] := num;
		branch.order_id := o_id;
		branch.lft := i;
	
		SELECT	rgt
		INTO	max_i
		FROM	page_children(branch.id)
		ORDER BY rgt DESC
		LIMIT 1;
	
		IF max_i IS NULL
		THEN
			branch.rgt := i + 1;
	
			RETURN NEXT branch;
		
			i := i + 1;
		ELSE
			branch.rgt := i + max_i + 1;
	
			RETURN NEXT branch;
	
			FOR leaf IN
			SELECT	id,
					parent_id,
					order_id,
					lft + i as lft,
					rgt + i as rgt,
					array_prepend(num, sort_num) as sort_num
			FROM	page_children(branch.id)
			LOOP
				o_id := o_id + 1;
				leaf.order_id := leaf.order_id + o_id;
		
				RETURN NEXT leaf;
			END LOOP;
	
			i := i + max_i + 1;
		END IF;
	END LOOP;

	RETURN;
END;
$$ LANGUAGE plpgsql STABLE STRICT;


/*
 * Trigger: _save_page()
 */

CREATE OR REPLACE FUNCTION _save_page()
	RETURNS trigger
AS $$
DECLARE
	do_stuff	boolean := false;
	str			varchar;
	arr			int[];
	i			integer;
	new_key		varchar;
BEGIN
	-- sanitize fields
	NEW.title := trim(NEW.title);
	
	-- sort_name, scan_name
	NEW.sort_name := lower(NEW.title);
	NEW.scan_name := upper(substr(NEW.title, 1, 1));
	
	-- key
	IF NEW.status = 'active' OR NEW.key <> ''
	THEN
		NEW.key := str2key(NEW.key);
		
		IF NEW.key = ''
		THEN
			NEW.key := str2key(NEW.title);
		END IF;
	
		-- catch key conflict
		new_key := NEW.key;
	
		IF EXISTS (
			SELECT	NULL
			FROM	pages
			WHERE	key = new_key
			AND		parent_id IS NOT DISTINCT FROM NEW.parent_id
			AND		main_id <> NEW.main_id
			)
		THEN
			i := 2;
		
			IF new_key ~ ( '^' || str2key(NEW.title) || E'_\\d+$' )
			THEN
				NEW.key := str2key(NEW.title);
			ELSE
				NEW.key := new_key;
			END IF;
		
			LOOP
				new_key := NEW.key || '_' || i;
			
				IF NOT EXISTS (
					SELECT	NULL
					FROM	pages
					WHERE	key = new_key
					AND		parent_id IS NOT DISTINCT FROM NEW.parent_id
					AND		main_id <> NEW.main_id
					)
				THEN
					NEW.key := new_key;
					EXIT;
				END IF;
			
				i := i + 1;
			END LOOP;
		END IF;
	END IF;
	
	-- tsv
	IF NEW.status <> 'revision'
	THEN
		NEW.tsv := setweight(to_tsvector(str2keywords(NEW.title)), 'A')
			|| setweight(to_tsvector(tags2keywords(NEW.id)), 'B')
			|| setweight(to_tsvector(xml2text(NEW.abstract)), 'C')
			|| setweight(to_tsvector(xml2text(NEW.content)), 'D');
	ELSE
		NEW.tsv := '';
	END IF;
	
	RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER _save_page BEFORE INSERT OR UPDATE ON pages
	FOR EACH ROW EXECUTE PROCEDURE _save_page();


/*
 * Trigger: _insert_page()
 */

CREATE OR REPLACE FUNCTION _insert_page()
	RETURNS trigger
AS $$
BEGIN
	-- main_id
	IF NEW.main_id IS NULL
	THEN
		NEW.main_id := NEW.id;
	END IF;
	
	-- active
	IF NEW.status = 'active' AND NEW.published IS NULL
	THEN
		NEW.published := now();
	ELSEIF NEW.status = 'future' AND NEW.published IS NULL
	THEN
		NEW.published := date_trunc('day', now() + interval '1 day');
	ELSEIF NEW.status = 'future' AND NEW.published <= now()
	THEN
		NEW.status = 'active';
	END IF;
	
	-- lft, rgt, sort_num, depth
	IF NEW.main_id <> NEW.id
	THEN
		SELECT	lft,
				rgt,
				sort_num,
				depth
		INTO	NEW.lft,
				NEW.rgt,
				NEW.sort_num,
				NEW.depth
		FROM	pages
		WHERE	id = NEW.main_id;
		
		IF NEW.status = 'active'
		THEN
			NEW.status := 'revision';
		END IF;
	END IF;
	
	-- order_id
	IF NEW.order_id IS NULL
	THEN
		SELECT	order_id + 1
		INTO	NEW.order_id
		FROM	pages
		WHERE	parent_id IS NOT DISTINCT FROM NEW.parent_id
		AND		order_id IS NOT NULL
		ORDER BY order_id DESC
		LIMIT 1;
		
		IF NOT FOUND
		THEN
			NEW.order_id := 1;
		END IF;
	END IF;
	
	RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER _insert_page BEFORE INSERT ON pages
	FOR EACH ROW EXECUTE PROCEDURE _insert_page();


/*
 * Trigger: insert_page()
 */

CREATE OR REPLACE FUNCTION insert_page()
	RETURNS trigger
AS $$
DECLARE
	do_perms	boolean := false;
	profile_rec	record;
	page_rec	record;
BEGIN
	IF NEW.id <> NEW.main_id
	THEN
		IF NEW.status IN ('obsolete', 'revision', 'future')
		THEN
			-- publish
			IF NEW.status <> 'future'
			THEN
				do_perms := NEW.status = 'obsolete'
				OR NEW.status = 'revision'
				AND EXISTS (
						SELECT	NULL
						FROM	pages
						WHERE	id = NEW.main_id
						AND		ROW(r, w, parent_id) IS DISTINCT FROM ROW(NEW.r, NEW.w, NEW.parent_id)
						);
				
				UPDATE	pages
				SET		status = CASE NEW.status
						WHEN 'revision'
						THEN
							'active'::node_status
						ELSE
							'obsolete'::node_status
						END,
						published = NEW.published,
						user_id = NEW.user_id,
						modified = now(),
						key = NEW.key,
						title = NEW.title,
						abstract = NEW.abstract,
						content = NEW.content,
						memo = NEW.memo,
						r = NEW.r,
						w = NEW.w
				WHERE	id = NEW.main_id;
			END IF;
		
			-- auto-clean
			DELETE FROM pages
			WHERE	main_id = NEW.main_id
			AND		id <> main_id
			AND		status IN ( 'obsolete', 'draft', 'pending' );
		ELSE
			do_perms := EXISTS (
				SELECT	NULL
				FROM	pages
				WHERE	id = NEW.main_id
				AND		ROW(r, w) IS DISTINCT FROM ROW(NEW.r, NEW.w)
				);
			
			IF do_perms
			THEN
				-- update perms
				UPDATE	pages
				SET		r = NEW.r,
						w = NEW.w
				WHERE	main_id = NEW.main_id
				AND		id <> NEW.id;
			END IF;
			
			-- auto-clean
			UPDATE	pages
			SET		status = 'draft'
			WHERE	status IN ('pending', 'future')
			AND		main_id = NEW.main_id
			AND		id <> NEW.id;
		END IF;
	END IF;
	
	-- enforce unique order_id
	IF NEW.parent_id IS NULL
	THEN
		IF EXISTS (
			SELECT	NULL
			FROM	pages
			WHERE	parent_id IS NULL
			AND		main_id <> NEW.main_id
			AND		order_id = NEW.order_id
			)
		THEN
			UPDATE	pages
			SET		order_id = order_id + 1
			WHERE	parent_id IS NULL
			AND		main_id <> NEW.main_id
			AND		order_id >= NEW.order_id;
		END IF;
	ELSE
		IF EXISTS (
			SELECT	NULL
			FROM	pages
			WHERE	parent_id = NEW.parent_id
			AND		main_id <> NEW.main_id
			AND		order_id = NEW.order_id
			)
		THEN
			UPDATE	pages
			SET		order_id = order_id + 1
			WHERE	parent_id = NEW.parent_id
			AND		main_id <> NEW.main_id
			AND		order_id >= NEW.order_id;
		END IF;
	END IF;
	
	-- propagate parent_id and order_id
	UPDATE	pages
	SET		parent_id = NEW.parent_id,
			order_id = NEW.order_id
	WHERE	main_id = NEW.main_id
	AND		ROW(parent_id, order_id) IS DISTINCT FROM ROW(NEW.parent_id, NEW.order_id);
	
	IF FOUND OR NEW.id = NEW.main_id
	THEN
		-- reindex doc tree
		UPDATE	pages
		SET		lft = tree.lft,
				rgt = tree.rgt,
				sort_num = tree.sort_num,
				order_id = tree.sort_num[array_upper(tree.sort_num, 1)],
				depth = array_upper(tree.sort_num, 1) - 1
		FROM	page_tree() as tree
		WHERE	main_id = tree.id;
	END IF;
		
	IF NEW.id = NEW.main_id
	THEN
		-- insert into guest_pages
		INSERT INTO guest_nodes
		SELECT	pages.id as id,
				bool_and(parents.r IS NOT FALSE AND parents.status = 'active')
				OR bool_or(parents.w IS TRUE) as read,
				bool_or(parents.w IS TRUE) as write
		FROM	pages
		JOIN	pages as parents
		ON		parents.id = parents.main_id
		AND		parents.lft <= pages.lft
		AND		parents.rgt >= pages.rgt
		WHERE	pages.id = NEW.id
		GROUP BY pages.id;
	ELSEIF do_perms
	THEN
		-- update guest_pages
		UPDATE	guest_nodes as guest_pages
		SET		read = new_perms.read,
				write = new_perms.write
		FROM	(
				SELECT	children.id,
						bool_and(parents.r IS NOT FALSE AND parents.status = 'active')
						OR bool_or(parents.w IS TRUE) as read,
						bool_or(parents.w IS TRUE) as write
				FROM	pages
				JOIN	pages as children
				ON		children.id = children.main_id
				AND		children.lft >= pages.lft
				AND		children.rgt <= pages.rgt
				JOIN	pages as parents
				ON		parents.id = parents.main_id
				AND		parents.lft <= children.lft
				AND		parents.rgt >= children.rgt
				WHERE	pages.id = NEW.main_id
				GROUP BY children.id
				) as new_perms
		WHERE	guest_pages.id = new_perms.id
		AND		ROW(guest_pages.read, guest_pages.write) <> ROW(new_perms.read, new_perms.write);
		
		FOR profile_rec IN
		SELECT	perms.profile_id
		FROM	pages
		JOIN	pages as children
		ON		children.id = children.main_id
		AND		children.lft >= pages.lft
		AND		children.rgt <= pages.rgt
		JOIN	profile2node as perms
		ON		perms.id = children.id
		WHERE	pages.id = NEW.main_id
		GROUP BY perms.profile_id
		LOOP
			SELECT	parents.lft,
					parents.rgt
			INTO	page_rec
			FROM	pages
			JOIN	pages as children
			ON		children.id = children.main_id
			AND		children.lft >= pages.lft
			AND		children.rgt <= pages.rgt
			JOIN	pages as parents
			ON		parents.id = parents.main_id
			AND		parents.lft <= children.lft
			AND		parents.rgt >= children.rgt
			JOIN	page_perms as perms
			ON		perms.profile_id = profile_rec.profile_id
			AND		perms.id = parents.id
			WHERE	pages.id = NEW.main_id
			ORDER BY parents.lft
			LIMIT	1;

			IF NOT FOUND
			THEN
				-- delete from profile_pages
				DELETE FROM profile2node as perms
				USING	pages
				JOIN	pages as children
				ON		children.id = children.main_id
				AND		children.lft >= pages.lft
				AND		children.rgt <= pages.rgt
				WHERE	perms.profile_id = profile_rec.profile_id
				AND		perms.id = children.id
				AND		pages.id = NEW.main_id;
			ELSE
				-- delete from profile_pages
				DELETE FROM profile2node as perms
				USING	pages as parents
				WHERE	perms.profile_id = profile_rec.profile_id
				AND		perms.id = parents.id
				AND		parents.id = parents.main_id
				AND		parents.lft < page_rec.lft
				AND		parents.rgt > page_rec.rgt;

				-- update profile_pages
				UPDATE	profile2node as perms
				SET		read = new_perms.read,
						write = new_perms.write,
						sticky = new_perms.sticky
				FROM	(
						SELECT	children.id,
								bool_and(perms.read AND parents.status = 'active')
								OR bool_or(perms.write) as read,
								bool_or(perms.write) as write,
								bool_or(perms.sticky) as sticky
						FROM	pages
						JOIN	pages as children
						ON		children.id = children.main_id
						AND		children.lft >= pages.lft
						AND		children.rgt <= pages.rgt
						JOIN	pages as parents
						ON		parents.id = parents.main_id
						AND		parents.lft <= children.lft
						AND		parents.rgt >= children.rgt
						JOIN	page_perms as perms
						ON		perms.profile_id = profile_rec.profile_id
						AND		perms.id = parents.id
						WHERE	pages.id = NEW.main_id
						GROUP BY children.id
						) as new_perms
				WHERE	perms.profile_id = profile_rec.profile_id
				AND		perms.id = new_perms.id
				AND		ROW(perms.read, perms.write, perms.sticky) <> ROW(new_perms.read, new_perms.write, new_perms.sticky);
			END IF;
		END LOOP;
	END IF;
	
	RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_page AFTER INSERT ON pages
	FOR EACH ROW EXECUTE PROCEDURE insert_page();


/*
 * Trigger: _cascade_page()
 */

CREATE OR REPLACE FUNCTION _cascade_page()
	RETURNS trigger
AS $$
BEGIN
	IF OLD.id = OLD.main_id
	THEN
		-- delete related pages
		DELETE FROM pages
		WHERE	main_id = OLD.main_id
		AND		id <> OLD.id;
	END IF;
	
	RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER _cascade_page BEFORE DELETE ON pages
	FOR EACH ROW EXECUTE PROCEDURE _cascade_page();


/*
 * Trigger: delete_page()
 */

CREATE OR REPLACE FUNCTION delete_page()
	RETURNS trigger
AS $$
BEGIN
	IF OLD.id = OLD.main_id
	THEN
		-- reindex tree
		UPDATE	pages
		SET		lft = tree.lft,
				rgt = tree.rgt,
				sort_num = tree.sort_num,
				order_id = tree.sort_num[array_upper(tree.sort_num, 1)],
				depth = array_upper(tree.sort_num, 1) - 1
		FROM	page_tree() as tree
		WHERE	main_id = tree.id;
	END IF;
	
	RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER delete_page AFTER DELETE ON pages
	FOR EACH ROW EXECUTE PROCEDURE delete_page();


/*
 * Table: page_perms
 */

CREATE TABLE page_perms (
	profile_id		uuid			NOT NULL REFERENCES profiles ( id ) ON DELETE CASCADE
									DEFERRABLE INITIALLY DEFERRED,
	id				uuid			NOT NULL REFERENCES pages ( id ) ON DELETE CASCADE
									DEFERRABLE INITIALLY DEFERRED,
	read			boolean			NOT NULL DEFAULT true,
	write			boolean			NOT NULL DEFAULT false,
	sticky			boolean			NOT NULL DEFAULT false,
	PRIMARY KEY ( profile_id, id ),
	CHECK ( NOT ( NOT read AND write ) ),
	CHECK ( NOT ( NOT sticky AND NOT read ) ),
	CHECK ( NOT ( NOT sticky AND write ) ),
	CONSTRAINT page_perms_valid_id_fkey FOREIGN KEY ( id, id )
									REFERENCES pages ( main_id, id ) ON DELETE CASCADE
									DEFERRABLE INITIALLY DEFERRED
) WITH ( FILLFACTOR = 80 );

CREATE UNIQUE INDEX page_perms_reverse ON page_perms ( id, profile_id );
CREATE INDEX page_perms_profile_id ON page_perms ( profile_id );
CREATE INDEX page_perms_id ON page_perms ( id );


/*
 * Trigger: insert_page_perms()
 */

CREATE OR REPLACE FUNCTION insert_page_perms()
	RETURNS trigger
AS $$
BEGIN
	-- insert into profile_pages
	INSERT INTO profile2node
	SELECT	NEW.profile_id,
			children.id,
			bool_and(perms.read AND parents.status = 'active')
			OR bool_or(perms.write) as read,
			bool_or(perms.write) as write,
			bool_or(perms.sticky) as sticky
	FROM	pages
	JOIN	pages as children
	ON		children.id = children.main_id
	AND		children.lft >= pages.lft
	AND		children.rgt <= pages.rgt
	JOIN	pages as parents
	ON		parents.id = parents.main_id
	AND		parents.lft <= children.lft
	AND		parents.rgt >= children.rgt
	JOIN	page_perms as perms
	ON		perms.profile_id = NEW.profile_id
	AND		perms.id = parents.id
	LEFT JOIN profile2node
	ON		profile2node.profile_id = perms.profile_id
	AND		profile2node.id = children.id
	WHERE	pages.id = NEW.id
	AND		profile2node.id IS NULL
	GROUP BY children.id;
	
	-- update profile_pages
	UPDATE	profile2node as perms
	SET		read = new_perms.read,
			write = new_perms.write,
			sticky = new_perms.sticky
	FROM	(
			SELECT	children.id,
					bool_and(perms.read AND parents.status = 'active')
					OR bool_or(perms.write) as read,
					bool_or(perms.write) as write,
					bool_or(perms.sticky) as sticky
			FROM	pages
			JOIN	pages as children
			ON		children.id = children.main_id
			AND		children.lft >= pages.lft
			AND		children.rgt <= pages.rgt
			JOIN	pages as parents
			ON		parents.id = parents.main_id
			AND		parents.lft <= children.lft
			AND		parents.rgt >= children.rgt
			JOIN	page_perms as perms
			ON		perms.profile_id = NEW.profile_id
			AND		perms.id = parents.id
			WHERE	pages.id = NEW.id
			GROUP BY children.id
			) as new_perms
	WHERE	perms.profile_id = NEW.profile_id
	AND		perms.id = new_perms.id
	AND		ROW(perms.read, perms.write, perms.sticky) <> ROW(new_perms.read, new_perms.write, new_perms.sticky);
	
	RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_page_perms AFTER INSERT ON page_perms
	FOR EACH ROW EXECUTE PROCEDURE insert_page_perms();


/*
 * Trigger: update_page_perms()
 */

CREATE OR REPLACE FUNCTION update_page_perms()
	RETURNS trigger
AS $$
BEGIN
	IF ROW(NEW.read, NEW.write, NEW.sticky) <> ROW(OLD.read, OLD.write, OLD.sticky)
	THEN
		-- update profile_pages
		UPDATE	profile2node as perms
		SET		read = new_perms.read,
				write = new_perms.write,
				sticky = new_perms.sticky
		FROM	(
				SELECT	children.id,
						bool_and(perms.read AND parents.status = 'active')
						OR bool_or(perms.write) as read,
						bool_or(perms.write) as write,
						bool_or(perms.sticky) as sticky
				FROM	pages
				JOIN	pages as children
				ON		children.id = children.main_id
				AND		children.lft >= pages.lft
				AND		children.rgt <= pages.rgt
				JOIN	pages as parents
				ON		parents.id = parents.main_id
				AND		parents.lft <= children.lft
				AND		parents.rgt >= children.rgt
				JOIN	page_perms as perms
				ON		perms.profile_id = NEW.profile_id
				AND		perms.id = parents.id
				WHERE	pages.id = NEW.id
				GROUP BY children.id
				) as new_perms
		WHERE	perms.profile_id = NEW.profile_id
		AND		perms.id = new_perms.id
		AND		ROW(perms.read, perms.write, perms.sticky) <> ROW(new_perms.read, new_perms.write, new_perms.sticky);
	END IF;
	
	RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_page_perms AFTER UPDATE ON page_perms
	FOR EACH ROW EXECUTE PROCEDURE update_page_perms();


/*
 * Trigger: delete_page_perms()
 */

CREATE OR REPLACE FUNCTION delete_page_perms()
	RETURNS trigger
AS $$
DECLARE
	rec	record;
BEGIN
	SELECT	parents.lft,
			parents.rgt
	INTO	rec
	FROM	pages
	JOIN	pages as children
	ON		children.id = children.main_id
	AND		children.lft >= pages.lft
	AND		children.rgt <= pages.rgt
	JOIN	pages as parents
	ON		parents.id = parents.main_id
	AND		parents.lft <= children.lft
	AND		parents.rgt >= children.rgt
	JOIN	page_perms as perms
	ON		perms.profile_id = OLD.profile_id
	AND		perms.id = parents.id
	WHERE	pages.id = OLD.id
	ORDER BY parents.lft
	LIMIT	1;
	
	IF NOT FOUND
	THEN
		-- delete from profile_pages
		DELETE FROM profile2node as perms
		USING	pages
		JOIN	pages as children
		ON		children.id = children.main_id
		AND		children.lft >= pages.lft
		AND		children.rgt <= pages.rgt
		WHERE	perms.profile_id = OLD.profile_id
		AND		perms.id = children.id
		AND		pages.id = OLD.id;
	ELSE
		-- delete from profile_pages
		DELETE FROM profile2node as perms
		USING	pages as parents
		WHERE	perms.profile_id = OLD.profile_id
		AND		perms.id = parents.id
		AND		parents.id = parents.main_id
		AND		parents.lft < rec.lft
		AND		parents.rgt > rec.rgt;
		
		-- update profile_pages
		UPDATE	profile2node as perms
		SET		read = new_perms.read,
				write = new_perms.write,
				sticky = new_perms.sticky
		FROM	(
				SELECT	children.id,
						bool_and(perms.read AND parents.status = 'active')
						OR bool_or(perms.write) as read,
						bool_or(perms.write) as write,
						bool_or(perms.sticky) as sticky
				FROM	pages
				JOIN	pages as children
				ON		children.id = children.main_id
				AND		children.lft >= pages.lft
				AND		children.rgt <= pages.rgt
				JOIN	pages as parents
				ON		parents.id = parents.main_id
				AND		parents.lft <= children.lft
				AND		parents.rgt >= children.rgt
				JOIN	page_perms as perms
				ON		perms.profile_id = OLD.profile_id
				AND		perms.id = parents.id
				WHERE	pages.id = OLD.id
				GROUP BY children.id
				) as new_perms
		WHERE	perms.profile_id = OLD.profile_id
		AND		perms.id = new_perms.id
		AND		ROW(perms.read, perms.write, perms.sticky) <> ROW(new_perms.read, new_perms.write, new_perms.sticky);
	END IF;
	
	RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER delete_page_perms AFTER DELETE ON page_perms
	FOR EACH ROW EXECUTE PROCEDURE delete_page_perms();