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

BEGIN;

-- only issue >warnings log messages. (only during this transaction)
SET LOCAL client_min_messages=warning;

CREATE OR REPLACE FUNCTION resource_allocation.on_task_updated()
  RETURNS trigger AS
$BODY$
DECLARE
    claim_tentative_status_id int := 0; --beware: hard coded instead of lookup for performance
    claim_claimed_status_id int := 1; --beware: hard coded instead of lookup for performance
    task_approved_status_id int := 300; --beware: hard coded instead of lookup for performance
BEGIN
  IF NEW.status_id <> OLD.status_id THEN
    IF NEW.status_id = task_approved_status_id THEN
        UPDATE resource_allocation.resource_claim rc SET status_id=claim_tentative_status_id WHERE rc.task_id=NEW.id AND rc.status_id <> claim_tentative_status_id;
    ELSIF NEW.status_id = ANY(ARRAY[400, 500, 600, 900, 1000, 1100]) THEN
        --prevent task status to be upgraded to scheduled (or beyond) when not all its claims are claimed
        IF EXISTS (SELECT id FROM resource_allocation.resource_claim WHERE task_id = NEW.id AND status_id <> claim_claimed_status_id) THEN
            RAISE EXCEPTION 'Cannot update task status from % to % when not all its claims are claimed', OLD.status_id, NEW.status_id;
        END IF;
    END IF;
END IF;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION resource_allocation.on_task_updated()
  OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.on_task_updated()
  IS 'function which is called by task table update trigger, which updates all the tasks resource claims to tentative state.';

DROP TRIGGER IF EXISTS T_on_task_updated ON resource_allocation.task CASCADE;
CREATE TRIGGER T_on_task_updated
  AFTER UPDATE
  ON resource_allocation.task
  FOR EACH ROW
  EXECUTE PROCEDURE resource_allocation.on_task_updated();
COMMENT ON TRIGGER T_on_task_updated ON resource_allocation.task
  IS 'task table update trigger, calls the resource_allocation.on_task_updated() function.';

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

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.';

DROP TRIGGER IF EXISTS T_delete_conflict_reasons_after_resource_claim_update ON resource_allocation.resource_claim CASCADE;
CREATE TRIGGER T_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();

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

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';

DROP TRIGGER IF EXISTS T_before_insert_conflict_reason_do_resource_claim_status_check ON resource_allocation.resource_claim_conflict_reason CASCADE;
CREATE TRIGGER T_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();

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

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.';

DROP TRIGGER IF EXISTS T_delete_conflict_reasons_after_task_update ON resource_allocation.task CASCADE;
CREATE TRIGGER T_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();

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

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';

DROP TRIGGER IF EXISTS T_before_insert_conflict_reason_do_task_status_check ON resource_allocation.task_conflict_reason CASCADE;
CREATE TRIGGER T_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();

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

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
    IF NEW.starttime >= NEW.endtime THEN
        RAISE EXCEPTION 'task specification starttime >= endtime: %', NEW;
    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;

DROP TRIGGER IF EXISTS T_specification_insertupdate_check_startendtimes ON resource_allocation.specification;
CREATE TRIGGER T_specification_insertupdate_check_startendtimes
  BEFORE INSERT OR UPDATE
  ON resource_allocation.specification
  FOR EACH ROW
  EXECUTE PROCEDURE resource_allocation.on_insertupdate_check_specification_startendtimes();

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

CREATE OR REPLACE FUNCTION resource_allocation.on_claim_insertupdate_check_startendtimes()
  RETURNS trigger AS
$BODY$
BEGIN
    IF NEW.starttime >= NEW.endtime THEN
        RAISE EXCEPTION 'claim starttime >= endtime: %', NEW;
    END IF;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION resource_allocation.on_claim_insertupdate_check_startendtimes()
  OWNER TO resourceassignment;

DROP TRIGGER IF EXISTS T_claim_insertupdate_check_startendtimes ON resource_allocation.resource_claim;
CREATE TRIGGER T_claim_insertupdate_check_startendtimes
  BEFORE INSERT OR UPDATE
  ON resource_allocation.resource_claim
  FOR EACH ROW
  EXECUTE PROCEDURE resource_allocation.on_claim_insertupdate_check_startendtimes();

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

CREATE OR REPLACE FUNCTION resource_allocation.process_new_claim_into_resource_usages(new_claim resource_allocation.resource_claim)
  RETURNS void AS
$$
DECLARE
    usage_at_or_before_start RECORD;
    usage_at_or_before_end RECORD;
    intermediate_usage RECORD;
BEGIN
    -- find resource_usage at claim starttime
    SELECT * FROM resource_allocation.get_resource_usage_at_or_before(new_claim.resource_id, new_claim.status_id, new_claim.starttime, false, false, false) into usage_at_or_before_start;
    SELECT * FROM resource_allocation.get_resource_usage_at_or_before(new_claim.resource_id, new_claim.status_id, new_claim.endtime, false, false, false) into usage_at_or_before_end;

    --add new_claim.claim_size at claim starttime to resource_usage depending on state of usage_at_or_before_start
    IF usage_at_or_before_start IS NOT NULL THEN
        IF usage_at_or_before_start.as_of_timestamp = new_claim.starttime THEN
            --update at the claim starttime the already existing usage value at the claim starttime + new claim size
            UPDATE resource_allocation.resource_usage ru SET usage = usage_at_or_before_start.usage + new_claim.claim_size
            WHERE ru.id = usage_at_or_before_start.id;
        ELSE
            --insert at the claim starttime the existing usage value before claim starttime + new claim size
            INSERT INTO resource_allocation.resource_usage (resource_id, status_id, as_of_timestamp, usage)
            VALUES (new_claim.resource_id, new_claim.status_id, new_claim.starttime, usage_at_or_before_start.usage + new_claim.claim_size);
        END IF;
    ELSE
        -- no previous usage known, so insert this claim's size as the first usage
        INSERT INTO resource_allocation.resource_usage (resource_id, status_id, as_of_timestamp, usage)
        VALUES (new_claim.resource_id, new_claim.status_id, new_claim.starttime, new_claim.claim_size);
    END IF;

    --close resource_usage for this new_claim claim at claim endtime depending on state of usage_at_or_before_end
    IF usage_at_or_before_end IS NOT NULL THEN
        IF usage_at_or_before_end.as_of_timestamp <> new_claim.endtime THEN
            --insert at the claim endtime the existing usage value before claim endtime
            INSERT INTO resource_allocation.resource_usage (resource_id, status_id, as_of_timestamp, usage)
            VALUES (new_claim.resource_id, new_claim.status_id, new_claim.endtime, usage_at_or_before_end.usage);
        END IF;
    ELSE
        -- no previous usage known, so insert 0 as the last usage
        INSERT INTO resource_allocation.resource_usage (resource_id, status_id, as_of_timestamp, usage)
        VALUES (new_claim.resource_id, new_claim.status_id, new_claim.endtime, 0);
    END IF;

    --now modify any existing usages between new_claim claim starttime and endtime
    FOR intermediate_usage IN SELECT * FROM resource_allocation.resource_usage ru
    WHERE ru.resource_id = new_claim.resource_id
    AND ru.status_id = new_claim.status_id
    AND ru.as_of_timestamp > new_claim.starttime
    AND ru.as_of_timestamp < new_claim.endtime
    LOOP
        UPDATE resource_allocation.resource_usage ru SET usage = intermediate_usage.usage + new_claim.claim_size
        WHERE ru.id = intermediate_usage.id;
    END LOOP;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION resource_allocation.process_new_claim_into_resource_usages(new_claim resource_allocation.resource_claim) OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.process_new_claim_into_resource_usages(new_claim resource_allocation.resource_claim)
  IS 'helper function which is called by resource_claim table insert and update triggers, which fills/updates the resource_allocation.resource_usage table with timeseries of accumulated resource_claim.sizes';

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

CREATE OR REPLACE FUNCTION resource_allocation.rebuild_resource_usages_from_claims()
  RETURNS void AS
$$
DECLARE
    resource virtual_instrument.resource;
BEGIN
    FOR resource IN (SELECT * FROM virtual_instrument.resource ORDER BY id) LOOP
        PERFORM resource_allocation.rebuild_resource_usages_from_claims_for_resource(resource.id);
    END LOOP;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION resource_allocation.rebuild_resource_usages_from_claims() OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.rebuild_resource_usages_from_claims()
  IS 'function which truncates the resource_usages table, and repopulates it by calling process_new_claim_into_resource_usages for each known claim.';

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

CREATE OR REPLACE FUNCTION resource_allocation.rebuild_resource_usages_from_claims_for_resource(_resource_id int)
  RETURNS void AS
$$
DECLARE
    status resource_allocation.resource_claim_status;
BEGIN
    FOR status IN (SELECT * FROM resource_allocation.resource_claim_status ORDER BY id) LOOP
        PERFORM resource_allocation.rebuild_resource_usages_from_claims_for_resource_of_status(_resource_id, status.id);
    END LOOP;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION resource_allocation.rebuild_resource_usages_from_claims_for_resource(_resource_id int) OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.rebuild_resource_usages_from_claims_for_resource(_resource_id int)
  IS 'function which rebuilds the resource_usages table for a specific resource.';

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

CREATE OR REPLACE FUNCTION resource_allocation.rebuild_resource_usages_from_claims_for_resource_of_status(_resource_id int, _status_id int)
  RETURNS void AS
$$
DECLARE
    claim resource_allocation.resource_claim;
    finished_claim resource_allocation.resource_claim;
    tmp_usage resource_allocation.resource_usage;
    new_usage_value bigint := 0;
    new_usage_id integer;
BEGIN
    -- make sure nobody thouches the these tables while running this function
    LOCK TABLE resource_allocation.resource_claim IN ACCESS SHARE MODE;
    LOCK TABLE resource_allocation.resource_usage IN EXCLUSIVE MODE;
    LOCK TABLE resource_allocation._rebuild_usages_active_claims IN EXCLUSIVE MODE;

    -- delete the relevant usages (so we can re-enter them in this method)
    DELETE FROM resource_allocation.resource_usage WHERE resource_id = _resource_id AND status_id = _status_id;

    -- make sure the helper tables are empty
    TRUNCATE resource_allocation._rebuild_usages_active_claims; --tracks the 'open'/'active' claims (starttime < loop_timestamp < endtime)
    TRUNCATE resource_allocation._rebuild_usages_active_usages; --will be filled with small subset of usages-table for faster lookups than in the big reource_usage table.

    -- process each claim for this _resource_id with _status_id
    FOR claim IN (SELECT * FROM resource_allocation.resource_claim
                  WHERE resource_id = _resource_id
                  AND status_id = _status_id
                  ORDER BY starttime, endtime)
    LOOP
        -- keep helper table _rebuild_usages_active_usages small and quick-to-search in each iteration.
        -- delete all 'closed'/'obsolete' usages from
        -- any usage before the first usage before min(endtime) of the active_claims is obsolete. (yes, that's twice before)
        SELECT * FROM resource_allocation._rebuild_usages_active_usages ru
                 WHERE ru.as_of_timestamp < (SELECT MIN(endtime) FROM resource_allocation._rebuild_usages_active_claims)
                 ORDER BY ru.as_of_timestamp DESC
                 LIMIT 1
                 INTO tmp_usage;
        IF tmp_usage IS NOT NULL THEN
            -- remember from above? any usage before the first usage before min(starttime) of the active_claims is obsolete.
            -- so, select the first usage before the usage we just found.
            SELECT * FROM resource_allocation._rebuild_usages_active_usages ru
                     WHERE ru.as_of_timestamp < tmp_usage.as_of_timestamp
                     ORDER BY ru.as_of_timestamp DESC
                     LIMIT 1
                     INTO tmp_usage;

            IF tmp_usage IS NOT NULL THEN
                DELETE FROM resource_allocation._rebuild_usages_active_usages ru WHERE ru.as_of_timestamp < tmp_usage.as_of_timestamp;
            END IF;
        END IF;

        --'close' all finished claims (if any)
        FOR finished_claim IN (SELECT * FROM resource_allocation._rebuild_usages_active_claims ac
                               WHERE ac.endtime <= claim.starttime
                               ORDER BY endtime)
        LOOP
            --(quick) search in the (small) _rebuild_usages_active_usages which holds only relevant usages
            --find last usage at or before finished_claim.endtime
            SELECT * FROM resource_allocation._rebuild_usages_active_usages ru
                     WHERE ru.as_of_timestamp <= finished_claim.endtime
                     ORDER BY ru.as_of_timestamp DESC
                     LIMIT 1
                     INTO tmp_usage;

            IF tmp_usage IS NULL THEN
                RAISE EXCEPTION 'tmp_usage should not be NULL while finishing active claims for claim % in rebuild_resource_usages_from_claims_for_resource_of_status(%, %)', finished_claim, _resource_id, _status_id;
            END IF;

            -- integrate (add current value to previous value)
            new_usage_value := tmp_usage.usage - finished_claim.claim_size;

            --a finished claim is 'closed' by subtracting the claim_size from the last usage value
            --this happens either at an already existing usage timestamp, or at a new usage timestamp.
            IF finished_claim.endtime = tmp_usage.as_of_timestamp THEN
                --claim's endtime coincides with existing usage timestamp
                --update the existing usage into the table
                UPDATE resource_allocation.resource_usage
                SET usage = new_usage_value
                WHERE id = tmp_usage.id;

                --also update the usage in the the small _rebuild_usages_active_usages table.
                UPDATE resource_allocation._rebuild_usages_active_usages
                SET usage = new_usage_value
                WHERE id = tmp_usage.id;
            ELSE
                --claim's endtime does not coincide with existing usage timestamp
                --insert the new usage into the table
                INSERT INTO resource_allocation.resource_usage (resource_id, status_id, as_of_timestamp, usage)
                VALUES (_resource_id, _status_id, finished_claim.endtime, new_usage_value) RETURNING id INTO new_usage_id;

                --also add the usage to the small _rebuild_usages_active_usages table, so it can be (quickly) searched.
                INSERT INTO resource_allocation._rebuild_usages_active_usages (id, resource_id, status_id, as_of_timestamp, usage)
                VALUES (new_usage_id, _resource_id, _status_id, finished_claim.endtime, new_usage_value);
            END IF;

            --now that the claim has been 'closed', remove it from the active claims
            DELETE FROM resource_allocation._rebuild_usages_active_claims WHERE id = finished_claim.id;
        END LOOP; -- end loop over finished claims

        --all claims which finished at or before this claim's starttime are now closed.
        --now, handle the new 'active' claim

        --(quick) search in the (small) _rebuild_usages_active_usages which holds only relevant usages
        --find last usage at or before claim.starttime
        SELECT * FROM resource_allocation._rebuild_usages_active_usages ru
                 WHERE ru.as_of_timestamp <= claim.starttime
                 ORDER BY ru.as_of_timestamp DESC
                 LIMIT 1
                 INTO tmp_usage;

        --this 'active' claim 'opens' also either at an already existing usage timestamp or at a new usage timestamp.
        IF tmp_usage IS NOT NULL AND claim.starttime = tmp_usage.as_of_timestamp THEN
            --claim's starttime coincides with existing usage timestamp
            -- integrate (add current value to previous value)
            new_usage_value := tmp_usage.usage + claim.claim_size;

            --update the existing usage with the new_usage_value
            UPDATE resource_allocation.resource_usage
            SET usage = new_usage_value
            WHERE id = tmp_usage.id;

            --also update the small _rebuild_usages_active_usages table, so it can be (quickly) searched.
            UPDATE resource_allocation._rebuild_usages_active_usages
            SET usage = new_usage_value
            WHERE id = tmp_usage.id;
        ELSE
            --claim's starttime does not coincide with existing usage timestamp
            IF tmp_usage IS NULL THEN
                -- integrate (no previous value, so start of integral)
                new_usage_value := claim.claim_size;
            ELSE
                -- integrate (add current value to previous value)
                new_usage_value := tmp_usage.usage + claim.claim_size;
            END IF;

            --and insert the new usage into the table
            INSERT INTO resource_allocation.resource_usage (resource_id, status_id, as_of_timestamp, usage)
            VALUES (_resource_id, _status_id, claim.starttime, new_usage_value) RETURNING id INTO new_usage_id;

            --also add the usage to the small _rebuild_usages_active_usages table, so it can be (quickly) searched.
            INSERT INTO resource_allocation._rebuild_usages_active_usages (id, resource_id, status_id, as_of_timestamp, usage)
            VALUES (new_usage_id, _resource_id, _status_id, claim.starttime, new_usage_value);
        END IF;

        --now that the claim has been 'opened', add it to the active claims
        INSERT INTO resource_allocation._rebuild_usages_active_claims (id, resource_id, task_id, starttime, endtime, status_id, claim_size)
               VALUES (claim.id, claim.resource_id, claim.task_id, claim.starttime, claim.endtime, claim.status_id, claim.claim_size);
    END LOOP;

    --all claims were processed and at least opened
    --so, conclude with 'closing' all still active claims
    FOR finished_claim IN (SELECT * FROM resource_allocation._rebuild_usages_active_claims ac
                           ORDER BY endtime)
    LOOP
        -- (quick) search in the (small) _rebuild_usages_active_usages which holds only relevant usages
        SELECT * FROM resource_allocation._rebuild_usages_active_usages ru
                 WHERE ru.as_of_timestamp <= finished_claim.endtime
                 ORDER BY ru.as_of_timestamp DESC
                 LIMIT 1
                 INTO tmp_usage;

        IF tmp_usage IS NULL THEN
            RAISE EXCEPTION 'tmp_usage should not be NULL while finishing processing opened claims for claim % in rebuild_resource_usages_from_claims_for_resource_of_status(%, %)', finished_claim, _resource_id, _status_id;
        END IF;

        -- integrate (add current value to previous value)
        new_usage_value := tmp_usage.usage - finished_claim.claim_size;

        --a finished claim is 'closed' by subtracting the claim_size from the last_usage_value
        --this happens either at an already existing usage timestamp, or at a new usage timestamp.
        IF finished_claim.endtime = tmp_usage.as_of_timestamp THEN
            --claim's endtime coincides with existing usage timestamp
            UPDATE resource_allocation.resource_usage
            SET usage = new_usage_value
            WHERE id = tmp_usage.id;

            --also update the small _rebuild_usages_active_usages table, so it can be (quickly) searched.
            UPDATE resource_allocation._rebuild_usages_active_usages
            SET usage = new_usage_value
            WHERE id = tmp_usage.id;
        ELSE
            --claim's endtime does not coincide with existing usage timestamp
            --insert the new usage into the table
            INSERT INTO resource_allocation.resource_usage (resource_id, status_id, as_of_timestamp, usage)
            VALUES (_resource_id, _status_id, finished_claim.endtime, new_usage_value) RETURNING id INTO new_usage_id;

            --also add the usage to the small _rebuild_usages_active_usages table, so it can be (quickly) searched.
            INSERT INTO resource_allocation._rebuild_usages_active_usages (id, resource_id, status_id, as_of_timestamp, usage)
            VALUES (new_usage_id, _resource_id, _status_id, finished_claim.endtime, new_usage_value);
        END IF;

        --now that the claim has been 'closed', remove it from the active claims
        DELETE FROM resource_allocation._rebuild_usages_active_claims WHERE id = finished_claim.id;
    END LOOP;

    -- wipe the helper tables
    TRUNCATE resource_allocation._rebuild_usages_active_claims;
    TRUNCATE resource_allocation._rebuild_usages_active_usages;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION resource_allocation.rebuild_resource_usages_from_claims_for_resource_of_status(int, int) OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.rebuild_resource_usages_from_claims_for_resource_of_status(int, int)
  IS 'function which rebuilds the resource_usages table for the claims with a specific status for a specific resource.';

---------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION resource_allocation.process_old_claim_outof_resource_usages(old_claim resource_allocation.resource_claim)
  RETURNS void AS
$$
DECLARE
    usage_at_start RECORD;
    usage_before_start RECORD;
    usage_at_end RECORD;
    intermediate_usage RECORD;
BEGIN
    -- find resource_usage at claim starttime
    SELECT * FROM resource_allocation.get_resource_usage_at_or_before(old_claim.resource_id, old_claim.status_id, old_claim.starttime, true, false, true) into usage_at_start;

    IF usage_at_start IS NULL THEN
        RAISE EXCEPTION 'process_old_claim_outof_resource_usages(%) cannot find usage_at_start', old_claim;
    END IF;


    -- and find resource_usage at claim endtime
    SELECT * FROM resource_allocation.get_resource_usage_at_or_before(old_claim.resource_id, old_claim.status_id, old_claim.endtime, true, false, true) into usage_at_end;

    IF usage_at_end IS NULL THEN
        RAISE EXCEPTION 'process_old_claim_outof_resource_usages(%) cannot find usage_at_end', old_claim;
    END IF;


    IF usage_at_start.usage = old_claim.claim_size THEN
        IF usage_at_end.usage = 0 THEN
            -- find resource_usage before claim starttime
            SELECT * FROM resource_allocation.get_resource_usage_at_or_before(old_claim.resource_id, old_claim.status_id, old_claim.starttime, false, true, false) into usage_before_start;

            IF usage_before_start IS NULL OR (usage_before_start IS NOT NULL AND usage_before_start.usage = 0) THEN
                --usage_at_start was 'caused' by this deleted claim only, so delete it
                DELETE FROM resource_allocation.resource_usage ru WHERE ru.id = usage_at_start.id;
            ELSE
                UPDATE resource_allocation.resource_usage ru SET usage = 0 WHERE ru.id = usage_at_start.id;
            END IF;
        ELSE
            --usage_at_start was 'caused' by this deleted claim only, so delete it
            DELETE FROM resource_allocation.resource_usage ru WHERE ru.id = usage_at_start.id;
        END IF;
    ELSE
        --update the usage_at_start.usage by subtracting the deleted claim size
        UPDATE resource_allocation.resource_usage ru SET usage = usage_at_start.usage - old_claim.claim_size
        WHERE ru.id = usage_at_start.id;
    END IF;

    IF usage_at_end.usage = 0 THEN
        --usage_at_end was 'caused' by this deleted claim only, so delete it
        DELETE FROM resource_allocation.resource_usage ru WHERE ru.id = usage_at_end.id;
    END IF;

    --now modify any existing usages between old_claim claim starttime and endtime
    FOR intermediate_usage IN SELECT * FROM resource_allocation.resource_usage ru
    WHERE ru.resource_id = old_claim.resource_id
    AND ru.status_id = old_claim.status_id
    AND ru.as_of_timestamp > old_claim.starttime
    AND ru.as_of_timestamp < old_claim.endtime
    LOOP
        UPDATE resource_allocation.resource_usage ru SET usage = intermediate_usage.usage - old_claim.claim_size
        WHERE ru.id = intermediate_usage.id;
    END LOOP;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION resource_allocation.process_old_claim_outof_resource_usages(old_claim resource_allocation.resource_claim) OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.process_old_claim_outof_resource_usages(old_claim resource_allocation.resource_claim)
  IS 'helper function which is called by resource_claim table update and delete triggers, which updates/clears the resource_allocation.resource_usage table with timeseries of accumulated resource_claim.sizes';

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

CREATE OR REPLACE FUNCTION resource_allocation.get_resource_usage_at_or_before(_resource_id int, _claim_status_id int, _timestamp timestamp, exactly_at boolean default false, only_before boolean default false, rebuild_usage_when_not_found boolean default false)
  RETURNS resource_allocation.resource_usage AS
$$
DECLARE
    result resource_allocation.resource_usage;
BEGIN
    SELECT * FROM resource_allocation.resource_usage ru
    WHERE ru.resource_id = _resource_id
    AND ru.status_id = _claim_status_id
    AND ru.as_of_timestamp <= _timestamp
    ORDER BY ru.as_of_timestamp DESC
    LIMIT 1 INTO result;

    -- check if as_of_timestamp is exactly_at _timestamp
    IF exactly_at AND result IS NOT NULL THEN
        IF result.as_of_timestamp <> _timestamp THEN
            result := NULL;
        END IF;
    END IF;

    -- check if as_of_timestamp is before _timestamp
    IF only_before AND result IS NOT NULL THEN
        IF result.as_of_timestamp >= _timestamp THEN
            result := NULL;
        END IF;
    END IF;

    -- rebuild usage when not found
    IF rebuild_usage_when_not_found AND result IS NULL THEN
        RAISE NOTICE 'get_resource_usage_at_or_before(_resource_id=%, status_id=%, timestamp=%, exactly_at=%, only_before=%, rebuild_usage_when_not_found=%): result should not be NULL. Rebuilding usages table for resource %.', _resource_id, _claim_status_id, _timestamp, exactly_at, only_before, rebuild_usage_when_not_found, _resource_id;
        PERFORM resource_allocation.rebuild_resource_usages_from_claims_for_resource_of_status(_resource_id, _claim_status_id);
        RAISE NOTICE 'get_resource_usage_at_or_before(_resource_id=%, status_id=%, timestamp=%, exactly_at=%, only_before=%, rebuild_usage_when_not_found=%): Finished rebuilding usages table for resource %.', _resource_id, _claim_status_id, _timestamp, exactly_at, only_before, rebuild_usage_when_not_found, _resource_id;

        -- try again, but now without the option to rebuild_usage_when_not_found (to prevent endless recursion)
        SELECT * FROM resource_allocation.get_resource_usage_at_or_before(_resource_id, _claim_status_id, _timestamp, exactly_at, only_before, false) INTO result;
        RAISE NOTICE 'get_resource_usage_at_or_before(_resource_id=%, status_id=%, timestamp=%, exactly_at=%, only_before=%, rebuild_usage_when_not_found=%): after rebuild, result=%.', _resource_id, _claim_status_id, _timestamp, exactly_at, only_before, rebuild_usage_when_not_found, result;
    END IF;

    IF result IS NULL THEN
        -- if result is still null (after possible rebuild etc), then return a 'default' usage of 0
        result.resource_id = _resource_id;
        result.status_id = _claim_status_id;
        result.as_of_timestamp = _timestamp;
        result.usage = 0;
    END IF;

    RETURN result;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION resource_allocation.get_resource_usage_at_or_before(int, int, timestamp, boolean, boolean, boolean) OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.get_resource_usage_at_or_before(int, int, timestamp, boolean, boolean, boolean)
  IS 'get the resource usage for the given _resource_id for claims with given _claim_status_id at the given _timestamp';

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

CREATE OR REPLACE FUNCTION utcnow()
  RETURNS timestamp AS
$$
  SELECT NOW() AT TIME ZONE 'UTC';
$$ LANGUAGE SQL;
ALTER FUNCTION utcnow() OWNER TO resourceassignment;
COMMENT ON FUNCTION utcnow()
  IS 'get the current time in utc timezone as timestamp (without timezone)';

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

CREATE OR REPLACE FUNCTION resource_allocation.get_current_resource_usage(_resource_id int, _claim_status_id int)
  RETURNS resource_allocation.resource_usage AS
$$
DECLARE
    result resource_allocation.resource_usage;
    now timestamp;
BEGIN
    SELECT * FROM utcnow() INTO now;
    SELECT * FROM resource_allocation.get_resource_usage_at_or_before(_resource_id, _claim_status_id, now, false, false, false) into result;
    RETURN result;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION resource_allocation.get_current_resource_usage(_resource_id int, _claim_status_id int) OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.get_current_resource_usage(_resource_id int, _claim_status_id int)
  IS 'get the current resource usage for the given _resource_id for claims with given _claim_status_id';

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

CREATE OR REPLACE FUNCTION resource_allocation.get_max_resource_usage_between(_resource_id int, _claim_status_id int, _lower timestamp, _upper timestamp)
  RETURNS resource_allocation.resource_usage AS
$$
DECLARE
    max_resource_usage_in_time_window record;
    max_resource_at_or_before_starttime record;
BEGIN
    SELECT * FROM resource_allocation.get_resource_usage_at_or_before(_resource_id, _claim_status_id, _lower, false, false, false) into max_resource_at_or_before_starttime;

    SELECT * FROM resource_allocation.resource_usage ru
    WHERE ru.resource_id = _resource_id
    AND ru.status_id = _claim_status_id
    AND ru.as_of_timestamp >= _lower
    AND ru.as_of_timestamp < _upper
    ORDER BY ru.usage DESC
    LIMIT 1 INTO max_resource_usage_in_time_window;

    IF max_resource_usage_in_time_window IS NOT NULL THEN
        IF max_resource_usage_in_time_window.usage > max_resource_at_or_before_starttime.usage THEN
             RETURN max_resource_usage_in_time_window;
        ELSE
             RETURN max_resource_at_or_before_starttime;
        END IF;
    ELSE
        -- could also be NULL but that is checked for elsewhere
        RETURN max_resource_at_or_before_starttime;
    END IF;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION resource_allocation.get_max_resource_usage_between(_resource_id int, _claim_status_id int, _lower timestamp, _upper timestamp) OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.get_max_resource_usage_between(_resource_id int, _claim_status_id int, _lower timestamp, _upper timestamp)
  IS 'get the maximum resource usage for the given _resource_id for claims with given _claim_status_id in the period between the given _lower and _upper timestamps';

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

CREATE OR REPLACE FUNCTION resource_allocation.get_resource_claimable_capacity_between(_resource_id int, _lower timestamp, _upper timestamp)
  RETURNS bigint AS
$$
DECLARE
    claimed_status_id int := 1; --beware: hard coded instead of lookup for performance
    max_resource_usage resource_allocation.resource_usage;
    max_resource_usage_value bigint;
    available_capacity bigint;
    total_capacity bigint;
    current_claimed_usage bigint;
BEGIN
    SELECT usage FROM resource_allocation.get_max_resource_usage_between(_resource_id, claimed_status_id, _lower, _upper) INTO max_resource_usage_value;

    RAISE NOTICE 'get_max_resource_usage_between: max_resource_usage_value = %', max_resource_usage_value;

    IF max_resource_usage_value IS NULL THEN
        max_resource_usage_value := 0;
    END IF;

    -- determine the available_capacity for this resource
    -- available_capacity is a truly measured metric (by tools like df (disk-free))
    SELECT available, total FROM resource_monitoring.resource_capacity WHERE resource_id = _resource_id LIMIT 1 INTO available_capacity, total_capacity;

    -- determine how much of the used_capacity is 'accounted for' by claims.
    -- this is a best guess of the amount of data which we know that should be on the resource.
    -- we can only 'measure' that at this moment,
    -- so take the current resource usage
    SELECT usage FROM resource_allocation.get_current_resource_usage(_resource_id, claimed_status_id) INTO current_claimed_usage;

    IF available_capacity = total_capacity THEN
        --this is not a monitored resource, and hence we do not know how much space is actually available.
        --make a best guess by subtracting the current_claimed_usage from the total_capacity

        RAISE NOTICE 'get_max_resource_usage_between(%, %, %, %) available_capacity=%, total_capacity=% current_claimed_usage=% max_resource_usage=% free_claimable_capacity=%', _resource_id, claimed_status_id, _lower, _upper, available_capacity, total_capacity, current_claimed_usage, max_resource_usage_value, total_capacity - max_resource_usage_value;

        RETURN total_capacity - max_resource_usage_value;
    ELSE
        --this is a monitored resource, and the claimable_capacity is not just the free space (available_capacity) at this moment!
        -- we have to take into account what we know about already claimed portions,
        -- both at this moment (current_claimed_usage) and for the planned claim (max_resource_usage_value, between _lower and _upper)

        RAISE NOTICE 'get_max_resource_usage_between(%, %, %, %) available_capacity=%, total_capacity=% current_claimed_usage=% max_resource_usage=% free_claimable_capacity=%', _resource_id, claimed_status_id, _lower, _upper, available_capacity, total_capacity, current_claimed_usage, max_resource_usage, available_capacity + current_claimed_usage - max_resource_usage_value;

        RETURN available_capacity + current_claimed_usage - max_resource_usage_value;
    END IF;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION resource_allocation.get_resource_claimable_capacity_between(_resource_id int, _lower timestamp, _upper timestamp) 
  OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.get_resource_claimable_capacity_between(_resource_id int, _lower timestamp, _upper timestamp)
  IS 'get the maximum resource usage for the given _resource_id for claims with given _claim_status_id in the period between the given _lower and _upper timestamps';

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

CREATE OR REPLACE FUNCTION resource_allocation.has_conflict_with_overlapping_claims(claim resource_allocation.resource_claim)
  RETURNS boolean AS
$$
DECLARE
    free_claimable_capacity bigint;
BEGIN
    -- get the free free_claimable_capacity for this resource for the claim's time window
    -- this does not include the current claim which is (or at least should be) tentative.
    SELECT * FROM resource_allocation.get_resource_claimable_capacity_between(claim.resource_id, claim.starttime, claim.endtime) INTO free_claimable_capacity;

    return claim.claim_size > free_claimable_capacity;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION resource_allocation.has_conflict_with_overlapping_claims(claim resource_allocation.resource_claim) OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.has_conflict_with_overlapping_claims(claim resource_allocation.resource_claim)
  IS 'checks if the claim fits in the free capacity of its resource.';

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

CREATE OR REPLACE FUNCTION resource_allocation.get_overlapping_claims(claim_id int, status int default 1) --beware: hard coded status=1 for claimed claims
  RETURNS SETOF resource_allocation.resource_claim AS
$$
DECLARE
    claim resource_allocation.resource_claim;
BEGIN
    SELECT * FROM resource_allocation.resource_claim
    WHERE id = claim_id
    LIMIT 1
    INTO claim;

    RETURN QUERY SELECT * FROM resource_allocation.get_overlapping_claims(claim, status);
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION resource_allocation.get_overlapping_claims(int, int) OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.get_overlapping_claims(int, int)
  IS 'get set of (claimed) claims which cause the given claim to have conflict status.';

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

CREATE OR REPLACE FUNCTION resource_allocation.get_overlapping_claims(claim resource_allocation.resource_claim, status int default 1) --beware: hard coded status=1 for claimed claims
  RETURNS SETOF resource_allocation.resource_claim AS
$$
BEGIN
    -- this function is quite similar to resource_allocation.has_conflict_with_overlapping_claims
    -- for performance reasons we repeat the common code here instead of wrapping the common code in a function

    --get all overlapping_claims, check whether they cause a conflict or not.
    RETURN QUERY SELECT * FROM resource_allocation.resource_claim rc
    WHERE rc.resource_id = claim.resource_id
    AND rc.status_id = status
    AND rc.id <> claim.id
    AND rc.endtime >= claim.starttime
    AND rc.starttime < claim.endtime;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION resource_allocation.get_overlapping_claims(resource_allocation.resource_claim, int) OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.get_overlapping_claims(resource_allocation.resource_claim, int)
  IS 'get set of claims (with given status) which overlap with the given claim.';

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

CREATE OR REPLACE FUNCTION resource_allocation.before_claim_insertupdatedelete()
  RETURNS trigger AS
$BODY$
DECLARE
    claim_tentative_status_id int := 0; --beware: hard coded instead of lookup for performance
    claim_claimed_status_id int := 1; --beware: hard coded instead of lookup for performance
    claim_conflict_status_id int := 2; --beware: hard coded instead of lookup for performance
    task_approved_status_id int := 300; --beware: hard coded instead of lookup for performance
    task_conflict_status_id int := 335; --beware: hard coded instead of lookup for performance
    task_prescheduled_status_id int := 350; --beware: hard coded instead of lookup for performance
    task_scheduled_status_id int := 400; --beware: hard coded instead of lookup for performance
    task_queued_status_id int := 500; --beware: hard coded instead of lookup for performance
    claim_has_conflicts boolean;
BEGIN
    --order of following steps is important, do not reorder the steps

    -- bounce any inserted claim which is not tentative
    IF TG_OP = 'INSERT' THEN
        IF NEW.status_id <> claim_tentative_status_id THEN
            RAISE EXCEPTION 'newly inserted claims should not have status other than tentative (%); new claim: % has status %', claim_tentative_status_id, NEW, NEW.status_id;
        END IF;
    END IF;

    IF TG_OP = 'UPDATE' THEN
        -- bounce any updated claim which has conflict state, but is tried to be updated to claimed
        -- only this function can 'reset' the conflict state back to tentative!
        IF NEW.status_id = claim_claimed_status_id AND OLD.status_id = claim_conflict_status_id THEN
            RAISE EXCEPTION 'cannot update claim-in-conflict to status claimed; old:% new:%', OLD, NEW;
        END IF;

        -- bounce any claim_size updates on claimed claims
        IF NEW.status_id = claim_claimed_status_id AND OLD.claim_size <> NEW.claim_size THEN
            RAISE EXCEPTION 'cannot update claim size on claimed claim; old:% new:%', OLD, NEW;
        END IF;

        -- bounce any task_id updates
        IF OLD.task_id <> NEW.task_id THEN
            RAISE EXCEPTION 'cannot change the task to which a claim belongs; old:% new:%', OLD, NEW;
        END IF;

        -- bounce any resource_id updates
        IF OLD.resource_id <> NEW.resource_id THEN
            RAISE EXCEPTION 'cannot change the resource to which a claim belongs; old:% new:%', OLD, NEW;
        END IF;
    END IF;

    IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
        --update the resource usages affected by this claim
        --do this before we check for conflicts, because this claim might be shifted for example
        --which might influence the resource_usages which determine wheter a claim fits.
        PERFORM resource_allocation.process_old_claim_outof_resource_usages(OLD);
    END IF;

    --only check claim if status and/or claim_size and/or start/end time changed
    IF TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND (OLD.status_id <> NEW.status_id OR
                                                  OLD.claim_size <> NEW.claim_size OR
                                                  OLD.starttime <> NEW.starttime OR
                                                  OLD.endtime <> NEW.endtime)) THEN
        --check if claim fits or has conflicts
        SELECT * FROM resource_allocation.has_conflict_with_overlapping_claims(NEW) INTO claim_has_conflicts;

        IF claim_has_conflicts THEN
            IF NEW.status_id <> claim_conflict_status_id THEN
                -- only set claims to conflict if task status <= queued
                -- when a claim goes to conflict, then so does it's task, and we don't want that for running/finished/aborted tasks
                IF EXISTS (SELECT 1 FROM resource_allocation.task
                           WHERE id=NEW.task_id
                           AND status_id = ANY(ARRAY[300, 335, 350, 400, 500])) THEN -- hardcoded tasks statuses <= queued
                    -- conflict with others, so set claim status to conflict
                    NEW.status_id := claim_conflict_status_id;
                END IF;
            END IF;
        ELSE
            -- no conflict (anymore) with others, so set claim status to tentative if currently in conflict
            IF NEW.status_id = claim_conflict_status_id THEN
                NEW.status_id := claim_tentative_status_id;
            END IF;
        END IF;
    END IF;

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    END IF;

    RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION resource_allocation.before_claim_insertupdatedelete()
  OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.before_claim_insertupdatedelete()
  IS 'trigger function which is called by resource_claim table insert/update/delete trigger, which fills/updates/clears the resource_allocation.resource_usage table with timeseries of accumulated resource_claim.sizes, and checks if claims fit in the free capacity of a resource';

DROP TRIGGER IF EXISTS T_before_claim_insertupdatedelete ON resource_allocation.resource_claim CASCADE;
CREATE TRIGGER T_before_claim_insertupdatedelete
  BEFORE INSERT OR UPDATE OR DELETE
  ON resource_allocation.resource_claim
  FOR EACH ROW
  EXECUTE PROCEDURE resource_allocation.before_claim_insertupdatedelete();

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

CREATE OR REPLACE FUNCTION resource_allocation.after_claim_insertupdatedelete()
  RETURNS trigger AS
$BODY$
DECLARE
    claim_tentative_status_id int := 0; --beware: hard coded instead of lookup for performance
    claim_claimed_status_id int := 1; --beware: hard coded instead of lookup for performance
    claim_conflict_status_id int := 2; --beware: hard coded instead of lookup for performance
    task_approved_status_id int := 300; --beware: hard coded instead of lookup for performance
    task_conflict_status_id int := 335; --beware: hard coded instead of lookup for performance
    affected_claim resource_allocation.resource_claim;
    claim_has_conflicts boolean;
BEGIN
    --do not process_old_claim_outof_resource_usages(OLD)
    --because that has been done already in before_claim_insertupdatedelete

    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        --update the resource usages affected by this claim
        PERFORM resource_allocation.process_new_claim_into_resource_usages(NEW);
    END IF;

    -- in the before trigger function, everything on the claim has been checked and adapted.
    -- now (in the after trigger, when all claims were inserted/updated in the database), let's check if the task should also be updated (to conflict status for example)
    -- only if claim status was changed or inserted...
    IF TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND (OLD.status_id <> NEW.status_id)) THEN
        IF NEW.status_id = claim_conflict_status_id THEN
            --if claim status went to conflict, then set the task status to conflict as well
            UPDATE resource_allocation.task SET status_id=task_conflict_status_id WHERE id=NEW.task_id AND status_id <> task_conflict_status_id;
        ELSIF NEW.status_id = claim_tentative_status_id THEN
            IF NOT EXISTS (SELECT id FROM resource_allocation.resource_claim WHERE task_id = NEW.task_id AND status_id = claim_conflict_status_id) THEN
                UPDATE resource_allocation.task SET status_id=task_approved_status_id WHERE id=NEW.task_id AND status_id <> task_approved_status_id;
            END IF;
        END IF;
    END IF;

    -- if this claim was moved or went from claimed to other status
    -- then check all other claims in conflict which might be affected by this change
    -- maybe they can be updated from conflict status to tentative...
    IF (TG_OP = 'UPDATE' AND (OLD.status_id = claim_claimed_status_id OR OLD.starttime <> NEW.starttime OR OLD.endtime <> NEW.endtime OR OLD.claim_size <> NEW.claim_size)) OR
        TG_OP = 'DELETE' THEN
        FOR affected_claim IN SELECT * FROM resource_allocation.resource_claim rc
                                        WHERE rc.resource_id = OLD.resource_id
                                        AND rc.status_id = claim_conflict_status_id
                                        AND rc.id <> OLD.id
                                        AND rc.endtime >= OLD.starttime
                                        AND rc.starttime < OLD.endtime LOOP

            --check if claim fits or has conflicts
            SELECT * FROM resource_allocation.has_conflict_with_overlapping_claims(affected_claim) INTO claim_has_conflicts;

            IF NOT claim_has_conflicts THEN
                -- no conflict (anymore) with others, so set claim status to tentative
                UPDATE resource_allocation.resource_claim SET status_id=claim_tentative_status_id WHERE id = affected_claim.id;
            END IF;
        END LOOP;
    END IF;

    -- if this claim went from to claimed status
    -- then check all other claims in tentative state which might be affected by this change
    -- maybe they should be updated from tentative status to conflict...
    IF TG_OP = 'UPDATE' AND NEW.status_id = claim_claimed_status_id AND (OLD.status_id <> NEW.status_id OR OLD.claim_size <> NEW.claim_size)THEN
        FOR affected_claim IN SELECT * FROM resource_allocation.resource_claim rc
                                        WHERE rc.resource_id = NEW.resource_id
                                        AND rc.status_id = claim_tentative_status_id
                                        AND rc.id <> NEW.id
                                        AND rc.endtime >= NEW.starttime
                                        AND rc.starttime < NEW.endtime LOOP

            --check if claim fits or has conflicts
            SELECT * FROM resource_allocation.has_conflict_with_overlapping_claims(affected_claim) INTO claim_has_conflicts;

            IF claim_has_conflicts THEN
                -- new conflict for affected_claim because this NEW claim is now claimed
                UPDATE resource_allocation.resource_claim SET status_id=claim_conflict_status_id WHERE id = affected_claim.id;
            END IF;
        END LOOP;
    END IF;
    
    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    END IF;

    RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION resource_allocation.after_claim_insertupdatedelete()
  OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.after_claim_insertupdatedelete()
  IS '';

DROP TRIGGER IF EXISTS T_after_claim_insertupdatedelete ON resource_allocation.resource_claim CASCADE;
CREATE TRIGGER T_after_claim_insertupdatedelete
  AFTER INSERT OR UPDATE OR DELETE
  ON resource_allocation.resource_claim
  FOR EACH ROW
  EXECUTE PROCEDURE resource_allocation.after_claim_insertupdatedelete();

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


COMMIT;