/* To get the place trigger, replace taxonlabel with place and remove references
to matched_label_id. */

----- Functions

CREATE OR REPLACE FUNCTION taxonlabel_update_ancestors(new taxonlabel, parent_id_ integer, old_parent_id integer DEFAULT NULL::integer)
  RETURNS void AS
$BODY$
BEGIN
    IF parent_id_ IS DISTINCT FROM old_parent_id THEN
        DECLARE
            -- These include the parent itself
            old_ancestors integer[] := (
                SELECT array_agg(ancestor_id) FROM taxonlabel_relationship
                WHERE descendant_id = old_parent_id
            );
            new_ancestors integer[] := (
                SELECT array_agg(ancestor_id) FROM taxonlabel_relationship
                WHERE descendant_id = parent_id_
            );
            descendant_id_ taxonlabel_relationship.descendant_id%TYPE;
            ancestor_id_ taxonlabel_relationship.ancestor_id%TYPE;
        BEGIN
            FOR descendant_id_ IN -- also includes self
                SELECT descendant_id
                FROM taxonlabel_relationship
                WHERE ancestor_id = new.taxonlabel_id
            LOOP
                -- Delete old parent's ancestors
                DELETE FROM taxonlabel_relationship
                WHERE descendant_id = descendant_id_
                AND ancestor_id = ANY (old_ancestors)
                ;
                
                -- Add new parent's ancestors
                FOR ancestor_id_ IN
                    SELECT unnest(new_ancestors)
                LOOP
                    BEGIN
                        INSERT INTO taxonlabel_relationship
                        (descendant_id, ancestor_id)
                        SELECT descendant_id_, ancestor_id_
                        ;
                    EXCEPTION
                        WHEN unique_violation THEN
                            RAISE WARNING '%', SQLERRM;
                    END;
                END LOOP;
            END LOOP;
        END;
    END IF;
    
    /* Note: We don't need an ON DELETE trigger to update the descendants'
    ancestors when a node is deleted, because the
    taxonlabel_relationship.ancestor_id foreign key is ON DELETE CASCADE. */
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE OR REPLACE FUNCTION taxonlabel_update_ancestors_on_insert()
  RETURNS trigger AS
$BODY$
BEGIN
    /* Add "ancestor" for this node. This is useful for queries, because you
    don't have to separately test if the leaf node is the one you're looking
    for, in addition to that leaf node's ancestors. */
    INSERT
    INTO taxonlabel_relationship
    (descendant_id, ancestor_id)
    VALUES (new.taxonlabel_id, new.taxonlabel_id)
    ;
    
    PERFORM taxonlabel_update_ancestors(new, new.parent_id);
    PERFORM taxonlabel_update_ancestors(new,
        NULLIF(new.matched_label_id, new.taxonlabel_id));
    
    RETURN new;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE OR REPLACE FUNCTION taxonlabel_update_ancestors_on_update()
  RETURNS trigger AS
$BODY$
BEGIN
    PERFORM taxonlabel_update_ancestors(new, new.parent_id, old.parent_id);
    PERFORM taxonlabel_update_ancestors(new,
        NULLIF(new.matched_label_id, new.taxonlabel_id),
        NULLIF(old.matched_label_id, old.taxonlabel_id));
    
    RETURN new;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

----- Ancestors table

CREATE TABLE taxonlabel_relationship
(
  descendant_id integer NOT NULL,
  ancestor_id integer NOT NULL,
  relationship relationship NOT NULL DEFAULT 'is included in'::relationship, -- The type of relationship.
  CONSTRAINT taxonlabel_relationship_pkey PRIMARY KEY (descendant_id , ancestor_id ),
  CONSTRAINT taxonlabel_relationship_ancestor_id_fkey FOREIGN KEY (ancestor_id)
      REFERENCES taxonlabel (taxonlabel_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT taxonlabel_relationship_descendant_id_fkey FOREIGN KEY (descendant_id)
      REFERENCES taxonlabel (taxonlabel_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);
ALTER TABLE taxonlabel_relationship
  OWNER TO bien;
COMMENT ON TABLE taxonlabel_relationship
  IS 'Stores the accepted ancestors of a taxonlabel. Auto-populated, so should not be manually modified.';
COMMENT ON COLUMN taxonlabel_relationship.relationship IS 'The type of relationship.';

CREATE INDEX taxonlabel_relationship_descendants
  ON taxonlabel_relationship
  USING btree
  (ancestor_id , descendant_id );

----- Triggers

CREATE TRIGGER taxonlabel_update_ancestors_on_insert
  AFTER INSERT
  ON taxonlabel
  FOR EACH ROW
  EXECUTE PROCEDURE taxonlabel_update_ancestors_on_insert();

CREATE TRIGGER taxonlabel_update_ancestors_on_update
  AFTER UPDATE
  ON taxonlabel
  FOR EACH ROW
  EXECUTE PROCEDURE taxonlabel_update_ancestors_on_update();