\set ECHO none

--------------------------------------------------------------------------------
-- Fonction trigger sur les insertions
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION set_path () RETURNS trigger AS $$
DECLARE 
	parent_path varchar;
	_table      RECORD;
	_element_id RECORD;
BEGIN

	-- We are dealing with a row update.
  
    IF (TG_OP = 'UPDATE') THEN
	
		IF (((OLD.parent_id != NEW.parent_id) AND
			 (NEW.parent_id IS NOT NULL))) THEN
		
		  -- Trying to get the parent path
		 
		  SELECT INTO parent_path path
			FROM  _element
			WHERE id = NEW.parent_id;
		  
		  NEW.path := parent_path || '.' || NEW.id;
		  
		END IF;

		-- Mise a jour recursive des path 
		
		IF (OLD.path != NEW.path) THEN
		
			PERFORM disable_elements_triggers ();
		
			UPDATE   _element
			  SET    path =
			  	(
					NEW.path
					||
					subpath (_element.path, nlevel(OLD.path))
				)
			  WHERE  _element.path <@ OLD.path
			  AND    nlevel(_element.path) > nlevel(OLD.path);
			  
			PERFORM enable_elements_triggers ();
			
		END IF;
		
	-- We are dealing with different cases about inserts		
		
	ELSEIF (TG_OP = 'INSERT') THEN

		IF (NEW.parent_id IS NOT NULL) THEN
	
		  -- Trying to get the parent path
		
		  SELECT INTO parent_path path
			FROM  _element
			WHERE id = NEW.parent_id;
		  
		  NEW.path := parent_path || '.' || NEW.id;
		  
		  -- Updating counters

 		  PERFORM disable_elements_triggers ();
		  
		  FOR _element_id IN 
		  	SELECT id 
			FROM _element WHERE _element.path @> NEW.path LOOP
			
			  UPDATE _element 
			  SET 	 counters = 
				COALESCE 
				(
					counters || 
					(
						TG_RELNAME::text => 
						(
							SELECT (COALESCE ((counters->(TG_RELNAME::text))::int, 0) + 1)::text 
							FROM   _element
							WHERE  _element.id = _element_id.id
						)
					), 
					(
						TG_RELNAME::text => 
						(
							SELECT (COALESCE ((counters->(TG_RELNAME::text))::int, 0) + 1)::text 
							FROM   _element
							WHERE  _element.id = _element_id.id
						)
					)
				)		  
			  WHERE  _element.id  = _element_id.id;
			
		  END LOOP;

		  PERFORM enable_elements_triggers ();
		
		ELSEIF (NEW.parent_id IS NULL) THEN
		
		  NEW.path := NEW.id;
	
		  UPDATE _counter
			  SET table_count = table_count + 1
			  WHERE table_name = TG_RELNAME;
			  
		END IF;
	
	ELSEIF (TG_OP = 'DELETE') THEN

		-- Updating counters

 		  PERFORM disable_elements_triggers ();

		  FOR _element_id IN 
		  	SELECT id 
			FROM _element 
			WHERE _element.path @> OLD.path 
			AND    nlevel(_element.path) < nlevel(OLD.path) LOOP
			
			  UPDATE _element 
			  SET 	 counters = 
				COALESCE 
				(
					counters || 
					(
						TG_RELNAME::text => 
						(
							SELECT (COALESCE ((counters->(TG_RELNAME::text))::int, 0) - 1)::text 
							FROM   _element
							WHERE  _element.id = _element_id.id
						)
					), 
					(
						TG_RELNAME::text => 
						(
							SELECT (COALESCE ((counters->(TG_RELNAME::text))::int, 0) - 1)::text 
							FROM   _element
							WHERE  _element.id = _element_id.id
						)
					)
				)		  
			  WHERE  _element.id  = _element_id.id;
			
		END LOOP;

		PERFORM enable_elements_triggers ();
		
		RETURN OLD;
		
	END IF;
		
	RETURN NEW;
	
  END;
$$ LANGUAGE plpgsql;

--------------------------------------------------------------------------------
-- Fonction trigger sur les delete
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION recursive_delete () RETURNS trigger AS $$
DECLARE 
BEGIN

	DELETE 
	FROM   _element
	WHERE  _element.path <@ OLD.path;
  
	RETURN NULL;
END;
$$ LANGUAGE plpgsql;

--------------------------------------------------------------------------------
-- Functions to disable or enable the elements triggers
--------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION disable_elements_triggers () RETURNS INTEGER AS $$
DECLARE 
BEGIN
	
	ALTER TABLE _site DISABLE TRIGGER ALL;
	ALTER TABLE _site_tmp DISABLE TRIGGER ALL;
	ALTER TABLE _community DISABLE TRIGGER ALL;
	ALTER TABLE _community_tmp DISABLE TRIGGER ALL;
	ALTER TABLE _group DISABLE TRIGGER ALL;
	ALTER TABLE _blog DISABLE TRIGGER ALL;
	ALTER TABLE _blog_tmp DISABLE TRIGGER ALL;
	ALTER TABLE _topic DISABLE TRIGGER ALL;
	ALTER TABLE _topic_tmp DISABLE TRIGGER ALL;
	ALTER TABLE _article DISABLE TRIGGER ALL;
	ALTER TABLE _article_tmp DISABLE TRIGGER ALL;
	ALTER TABLE _comment DISABLE TRIGGER ALL;
	ALTER TABLE _comment_tmp DISABLE TRIGGER ALL;
	ALTER TABLE _trackback DISABLE TRIGGER ALL;
	ALTER TABLE _trackback_tmp DISABLE TRIGGER ALL;
	ALTER TABLE _link DISABLE TRIGGER ALL;
	ALTER TABLE _link_tmp DISABLE TRIGGER ALL;
	ALTER TABLE _album DISABLE TRIGGER ALL;
	ALTER TABLE _album_tmp DISABLE TRIGGER ALL;
	ALTER TABLE _text DISABLE TRIGGER ALL;
	ALTER TABLE _text_tmp DISABLE TRIGGER ALL;
	ALTER TABLE _relation DISABLE TRIGGER ALL;
	ALTER TABLE _relation_tmp DISABLE TRIGGER ALL;
	ALTER TABLE _log DISABLE TRIGGER ALL;
	ALTER TABLE _log_tmp DISABLE TRIGGER ALL;
	
	ALTER TABLE _product DISABLE TRIGGER ALL;
	ALTER TABLE _product_association  DISABLE TRIGGER ALL;
	ALTER TABLE _product_association_tmp  DISABLE TRIGGER ALL;
	ALTER TABLE _product_billing DISABLE TRIGGER ALL;
	ALTER TABLE _product_billing_tmp DISABLE TRIGGER ALL;
	ALTER TABLE _product_billing_operation DISABLE TRIGGER ALL	;

	RETURN 1;
	
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION enable_elements_triggers () RETURNS INTEGER AS $$
DECLARE 
BEGIN
	
	ALTER TABLE _site ENABLE TRIGGER ALL;
	ALTER TABLE _site_tmp ENABLE TRIGGER ALL;
	ALTER TABLE _community ENABLE TRIGGER ALL;
	ALTER TABLE _community_tmp ENABLE TRIGGER ALL;
	ALTER TABLE _group ENABLE TRIGGER ALL;
	ALTER TABLE _blog ENABLE TRIGGER ALL;
	ALTER TABLE _blog_tmp ENABLE TRIGGER ALL;
	ALTER TABLE _topic ENABLE TRIGGER ALL;
	ALTER TABLE _topic_tmp ENABLE TRIGGER ALL;
	ALTER TABLE _article ENABLE TRIGGER ALL;
	ALTER TABLE _article_tmp ENABLE TRIGGER ALL;
	ALTER TABLE _comment ENABLE TRIGGER ALL;
	ALTER TABLE _comment_tmp ENABLE TRIGGER ALL;
	ALTER TABLE _trackback ENABLE TRIGGER ALL;
	ALTER TABLE _trackback_tmp ENABLE TRIGGER ALL;
	ALTER TABLE _link ENABLE TRIGGER ALL;
	ALTER TABLE _link_tmp ENABLE TRIGGER ALL;
	ALTER TABLE _album ENABLE TRIGGER ALL;
	ALTER TABLE _album_tmp ENABLE TRIGGER ALL;
	ALTER TABLE _text ENABLE TRIGGER ALL;
	ALTER TABLE _text_tmp ENABLE TRIGGER ALL;
	ALTER TABLE _relation ENABLE TRIGGER ALL;
	ALTER TABLE _relation_tmp ENABLE TRIGGER ALL;
	ALTER TABLE _log ENABLE TRIGGER ALL;
	ALTER TABLE _log_tmp ENABLE TRIGGER ALL;
	
	ALTER TABLE _product ENABLE TRIGGER ALL;
	ALTER TABLE _product_association  ENABLE TRIGGER ALL;
	ALTER TABLE _product_association_tmp  ENABLE TRIGGER ALL;
	ALTER TABLE _product_billing ENABLE TRIGGER ALL;
	ALTER TABLE _product_billing_tmp ENABLE TRIGGER ALL;
	ALTER TABLE _product_billing_operation ENABLE TRIGGER ALL;	

	RETURN 1;
	
END;
$$ LANGUAGE plpgsql;