/* To get the place trigger, replace taxonconcept with place and remove
references to matched_concept_id. */

----- Functions

CREATE OR REPLACE FUNCTION taxonconcept_update_ancestors(new taxonconcept, old_parent_id integer DEFAULT NULL::integer)
  RETURNS void AS
$BODY$
DECLARE
    -- Use matched_concept_id's ancestors instead if available
    parent_id_ taxonconcept.taxonconcept_id%TYPE := COALESCE(
        NULLIF(new.matched_concept_id, new.taxonconcept_id), new.parent_id);
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 taxonconcept_ancestor
                WHERE taxonconcept_id = old_parent_id
            );
            new_ancestors integer[] := (
                SELECT array_agg(ancestor_id) FROM taxonconcept_ancestor
                WHERE taxonconcept_id = parent_id_
            );
            descendant_id integer;
        BEGIN
            FOR descendant_id IN -- also includes self
                SELECT taxonconcept_id
                FROM taxonconcept_ancestor
                WHERE ancestor_id = new.taxonconcept_id
            LOOP
                -- Delete old parent's ancestors
                DELETE FROM taxonconcept_ancestor
                WHERE taxonconcept_id = descendant_id
                AND ancestor_id = ANY (old_ancestors)
                ;
                
                -- Add new parent's ancestors
                INSERT INTO taxonconcept_ancestor
                (taxonconcept_id, ancestor_id)
                SELECT descendant_id, unnest(new_ancestors)
                ;
            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
    taxonconcept_ancestor.ancestor_id foreign key is ON DELETE CASCADE. */
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE OR REPLACE FUNCTION taxonconcept_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 taxonconcept_ancestor
    (taxonconcept_id, ancestor_id)
    VALUES (new.taxonconcept_id, new.taxonconcept_id)
    ;
    
    PERFORM taxonconcept_update_ancestors(new);
    
    RETURN new;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE OR REPLACE FUNCTION taxonconcept_update_ancestors_on_update()
  RETURNS trigger AS
$BODY$
BEGIN
    PERFORM taxonconcept_update_ancestors(new, COALESCE(
        NULLIF(old.matched_concept_id, old.taxonconcept_id), old.parent_id));
    
    RETURN new;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

----- Ancestors table

CREATE TABLE taxonconcept_ancestor
(
  taxonconcept_id integer NOT NULL,
  ancestor_id integer NOT NULL,
  CONSTRAINT taxonconcept_ancestor_pkey PRIMARY KEY (taxonconcept_id , ancestor_id ),
  CONSTRAINT taxonconcept_ancestor_ancestor_id_fkey FOREIGN KEY (ancestor_id)
      REFERENCES taxonconcept (taxonconcept_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT taxonconcept_ancestor_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id)
      REFERENCES taxonconcept (taxonconcept_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);
COMMENT ON TABLE taxonconcept_ancestor
  IS 'Stores the accepted ancestors of a taxonconcept. Auto-populated, so should not be manually modified.';

CREATE INDEX taxonconcept_ancestor_descendants
  ON taxonconcept_ancestor
  USING btree
  (ancestor_id , taxonconcept_id );

----- Triggers

CREATE TRIGGER taxonconcept_update_ancestors_on_insert
  AFTER INSERT
  ON taxonconcept
  FOR EACH ROW
  EXECUTE PROCEDURE taxonconcept_update_ancestors_on_insert();

CREATE TRIGGER taxonconcept_update_ancestors_on_update
  AFTER UPDATE
  ON taxonconcept
  FOR EACH ROW
  EXECUTE PROCEDURE taxonconcept_update_ancestors_on_update();