--add triggers and trigger functions to radb (note, there are also the notification triggers in the add_notifications.sql file)

DROP TRIGGER IF EXISTS trigger_delete_resource_claims_for_approved_task ON resource_allocation.task CASCADE;
DROP FUNCTION IF EXISTS resource_allocation.delete_resource_claims_for_approved_task();

CREATE OR REPLACE FUNCTION resource_allocation.delete_resource_claims_for_approved_task()
  RETURNS trigger AS
$BODY$
BEGIN
  IF NEW.status_id <> OLD.status_id AND NEW.status_id = 300 THEN
    DELETE FROM resource_allocation.resource_claim rc WHERE rc.task_id = NEW.id;
  END IF;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION resource_allocation.delete_resource_claims_for_approved_task()
  OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.delete_resource_claims_for_approved_task()
  IS 'function which is called by task table update trigger, which deletes all the tasks resource claims.';

CREATE TRIGGER trigger_delete_resource_claims_for_approved_task
  AFTER UPDATE
  ON resource_allocation.task
  FOR EACH ROW
  EXECUTE PROCEDURE resource_allocation.delete_resource_claims_for_approved_task();
COMMENT ON TRIGGER trigger_delete_resource_claims_for_approved_task ON resource_allocation.task
  IS 'task table update trigger, calls the resource_allocation.delete_resource_claims_for_approved_task() function.';

---------------------------------------------------------------------------------------------------------------------

DROP TRIGGER IF EXISTS trigger_delete_conflict_reasons_after_resource_claim_update ON resource_allocation.resource_claim CASCADE;
DROP FUNCTION IF EXISTS resource_allocation.delete_conflict_reasons_after_resource_claim_update();

CREATE OR REPLACE FUNCTION resource_allocation.delete_conflict_reasons_after_resource_claim_update()
  RETURNS trigger AS
$BODY$
BEGIN
  IF OLD.status_id = 2 AND NEW.status_id <> 2 THEN   --new status is not conflict
    DELETE FROM resource_allocation.resource_claim_conflict_reason rccr WHERE rccr.resource_claim_id = NEW.id;
  END IF;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION resource_allocation.delete_conflict_reasons_after_resource_claim_update()
  OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.delete_conflict_reasons_after_resource_claim_update()
  IS 'function which is called by resource_claim table update trigger, which deletes resource_claim_conflict_reasons when the claim status is updated to !conflict.';

CREATE TRIGGER trigger_delete_conflict_reasons_after_resource_claim_update
  AFTER UPDATE
  ON resource_allocation.resource_claim
  FOR EACH ROW
  EXECUTE PROCEDURE resource_allocation.delete_conflict_reasons_after_resource_claim_update();

---------------------------------------------------------------------------------------------------------------------

DROP TRIGGER IF EXISTS trigger_before_insert_conflict_reason_do_resource_claim_status_check ON resource_allocation.resource_claim_conflict_reason CASCADE;
DROP FUNCTION IF EXISTS resource_allocation.before_insert_conflict_reason_do_resource_claim_status_check();

CREATE OR REPLACE FUNCTION resource_allocation.before_insert_conflict_reason_do_resource_claim_status_check()
  RETURNS trigger AS
$BODY$
BEGIN
  -- check if referred resource_claim is in conflict status, else raise
  IF (SELECT COUNT(id) FROM resource_allocation.resource_claim rc WHERE rc.id = NEW.resource_claim_id AND rc.status_id = 2) = 0 THEN
    RAISE EXCEPTION 'resource_claim has no conflict status';
  END IF;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION resource_allocation.before_insert_conflict_reason_do_resource_claim_status_check()
  OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.before_insert_conflict_reason_do_resource_claim_status_check()
  IS 'check if referred resource_claim is in conflict status, else raise';

CREATE TRIGGER trigger_before_insert_conflict_reason_do_resource_claim_status_check
  BEFORE INSERT
  ON resource_allocation.resource_claim_conflict_reason
  FOR EACH ROW
  EXECUTE PROCEDURE resource_allocation.before_insert_conflict_reason_do_resource_claim_status_check();

---------------------------------------------------------------------------------------------------------------------

DROP TRIGGER IF EXISTS trigger_delete_conflict_reasons_after_task_update ON resource_allocation.task CASCADE;
DROP FUNCTION IF EXISTS resource_allocation.delete_conflict_reasons_after_task_update();

CREATE OR REPLACE FUNCTION resource_allocation.delete_conflict_reasons_after_task_update()
  RETURNS trigger AS
$BODY$
BEGIN
  IF OLD.status_id = 335 AND NEW.status_id <> 335 THEN   --new status is not conflict
    DELETE FROM resource_allocation.task_conflict_reason tcr WHERE tcr.task_id = NEW.id;
  END IF;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION resource_allocation.delete_conflict_reasons_after_task_update()
  OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.delete_conflict_reasons_after_task_update()
  IS 'function which is called by task table update trigger, which deletes task_conflict_reasons when the task status is updated to !conflict.';

CREATE TRIGGER trigger_delete_conflict_reasons_after_task_update
  AFTER UPDATE
  ON resource_allocation.task
  FOR EACH ROW
  EXECUTE PROCEDURE resource_allocation.delete_conflict_reasons_after_task_update();

---------------------------------------------------------------------------------------------------------------------

DROP TRIGGER IF EXISTS trigger_before_insert_conflict_reason_do_task_status_check ON resource_allocation.task_conflict_reason CASCADE;
DROP FUNCTION IF EXISTS resource_allocation.before_insert_conflict_reason_do_task_status_check();

CREATE OR REPLACE FUNCTION resource_allocation.before_insert_conflict_reason_do_task_status_check()
  RETURNS trigger AS
$BODY$
BEGIN
  -- check if referred task is in conflict status, else raise
  IF (SELECT COUNT(id) FROM resource_allocation.task task WHERE task.id = NEW.task_id AND task.status_id = 335) = 0 THEN
    RAISE EXCEPTION 'task has no conflict status';
  END IF;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION resource_allocation.before_insert_conflict_reason_do_task_status_check()
  OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.before_insert_conflict_reason_do_task_status_check()
  IS 'check if referred task is in conflict status, else raise';

CREATE TRIGGER trigger_before_insert_conflict_reason_do_task_status_check
  BEFORE INSERT
  ON resource_allocation.task_conflict_reason
  FOR EACH ROW
  EXECUTE PROCEDURE resource_allocation.before_insert_conflict_reason_do_task_status_check();

---------------------------------------------------------------------------------------------------------------------

DROP TRIGGER IF EXISTS trigger_specification_insertupdate_check_startendtimes ON resource_allocation.specification;
DROP FUNCTION IF EXISTS resource_allocation.on_insertupdate_check_specification_startendtimes();

CREATE OR REPLACE FUNCTION resource_allocation.on_insertupdate_check_specification_startendtimes()
  RETURNS trigger AS
$BODY$
DECLARE
task RECORD;
pred_task RECORD;
suc_task RECORD;
predecessor_task_id int;
successor_task_id int;
moved_seconds double precision;
duration double precision;
max_pred_endtime timestamp := '1900-01-01 00:00:00';
tmp_time timestamp;
min_starttime timestamp;
min_inter_task_delay int;
BEGIN
    --swap start/end time if needed
    IF NEW.starttime > NEW.endtime THEN
        RAISE NOTICE 'NEW.starttime > NEW.endtime';
        tmp_time := NEW.starttime;
        NEW.starttime := NEW.endtime;
        NEW.endtime := tmp_time;
    END IF;

    --store task duration
    SELECT EXTRACT(epoch FROM age(NEW.endtime, NEW.starttime)) INTO duration;

    --deterimine max_pred_endtime
    FOR task IN SELECT * FROM resource_allocation.task_view tv WHERE tv.specification_id = NEW.id LOOP
        IF task.predecessor_ids IS NOT NULL THEN
            FOREACH predecessor_task_id IN ARRAY task.predecessor_ids LOOP
                FOR pred_task IN SELECT * FROM resource_allocation.task_view tv WHERE tv.id = predecessor_task_id LOOP
                    IF pred_task.endtime > max_pred_endtime THEN
                        max_pred_endtime := pred_task.endtime;
                    END IF;
                END LOOP;
            END LOOP;
        END IF;
    END LOOP;

    --check if spec is before max_pred_endtime, correct if needed.
    IF max_pred_endtime > '1900-01-01 00:00:00' THEN
        SELECT c.value::integer INTO min_inter_task_delay FROM resource_allocation.config c WHERE c.name = 'min_inter_task_delay';
        IF min_inter_task_delay IS NULL THEN
            min_inter_task_delay := 0;
        END IF;
        min_starttime := max_pred_endtime + min_inter_task_delay * interval '1 second';
        IF min_starttime > NEW.starttime THEN
            NEW.starttime := min_starttime;
            NEW.endtime := min_starttime + duration * interval '1 second';
        END IF;
    END IF;

    --move successor tasks by same amount if needed
    IF TG_OP = 'UPDATE' THEN
        IF NEW.endtime <> OLD.endtime THEN
            SELECT EXTRACT(epoch FROM age(NEW.endtime, OLD.endtime)) INTO moved_seconds;
            FOR task IN SELECT * FROM resource_allocation.task_view tv WHERE tv.specification_id = NEW.id LOOP
                IF task.successor_ids IS NOT NULL THEN
                    FOREACH successor_task_id IN ARRAY task.successor_ids LOOP
                        FOR suc_task IN SELECT * FROM resource_allocation.task_view tv WHERE tv.id = successor_task_id LOOP
                            UPDATE resource_allocation.specification SET (starttime, endtime) = (starttime + moved_seconds * interval '1 second', endtime + moved_seconds * interval '1 second') WHERE id = suc_task.specification_id;
                        END LOOP;
                    END LOOP;
                END IF;
            END LOOP;
        END IF;
    END IF;

RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION resource_allocation.on_insertupdate_check_specification_startendtimes()
  OWNER TO resourceassignment;

CREATE TRIGGER trigger_specification_insertupdate_check_startendtimes
  BEFORE INSERT OR UPDATE
  ON resource_allocation.specification
  FOR EACH ROW
  EXECUTE PROCEDURE resource_allocation.on_insertupdate_check_specification_startendtimes();

---------------------------------------------------------------------------------------------------------------------