add_functions_and_triggers.sql

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

DROP TRIGGER IF EXISTS T_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 T_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 T_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 T_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 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();

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

DROP TRIGGER IF EXISTS T_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 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();

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

DROP TRIGGER IF EXISTS T_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 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();

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

DROP TRIGGER IF EXISTS T_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 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();

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

DROP TRIGGER IF EXISTS T_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 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();

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

DROP TRIGGER IF EXISTS T_claim_insertupdate_check_startendtimes ON resource_allocation.resource_claim;
DROP FUNCTION IF EXISTS resource_allocation.on_claim_insertupdate_check_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;

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();

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


DROP FUNCTION IF EXISTS resource_monitoring.process_new_claim_into_resource_usages(new_claim resource_allocation.resource_claim);

CREATE OR REPLACE FUNCTION resource_monitoring.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
    --RAISE NOTICE '';
    --RAISE NOTICE '-------- resource_monitoring.process_new_claim_into_resource_usages ------';
    --RAISE NOTICE 'new_claim %', new_claim;

    -- find last known resource_usage at or before claim starttime
    SELECT * FROM resource_monitoring.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
    ORDER BY ru.as_of_timestamp DESC
    LIMIT 1
    INTO usage_at_or_before_start;

    -- find last known resource_usage before claim endtime
    SELECT * FROM resource_monitoring.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.endtime
    ORDER BY ru.as_of_timestamp DESC
    LIMIT 1
    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_monitoring.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_monitoring.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_monitoring.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_monitoring.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_monitoring.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_monitoring.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_monitoring.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_monitoring.process_new_claim_into_resource_usages(new_claim resource_allocation.resource_claim) OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_monitoring.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_monitoring.resource_usage table with timeseries of accumulated resource_claim.sizes';

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

DROP FUNCTION IF EXISTS resource_monitoring.process_old_claim_outof_resource_usages(old_claim resource_allocation.resource_claim);

CREATE OR REPLACE FUNCTION resource_monitoring.process_old_claim_outof_resource_usages(old_claim resource_allocation.resource_claim)
  RETURNS void AS
$$
DECLARE
usage_at_start RECORD;
usage_at_end RECORD;
intermediate_usage RECORD;
BEGIN
    --RAISE NOTICE '';
    --RAISE NOTICE '-------- resource_monitoring.process_old_claim_outof_resource_usages ------';
    --RAISE NOTICE 'old_claim: %', old_claim;

    -- find resource_usage at claim starttime
    SELECT * FROM resource_monitoring.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
    LIMIT 1
    INTO usage_at_start;

    -- find last known resource_usage before claim endtime
    SELECT * FROM resource_monitoring.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.endtime
    LIMIT 1
    INTO usage_at_end;

    --RAISE NOTICE 'usage_at_start: %', usage_at_start;
    --RAISE NOTICE 'usage_at_end  : %', usage_at_end;

    -- both usage_at_start and usage_at_end should exist (NOT NULL)
    IF usage_at_start IS NULL THEN
        RAISE EXCEPTION 'resource_monitoring.on_delete_claim_update_resource_usages: usage_at_start should not be NULL.';
    END IF;
    IF usage_at_end IS NULL THEN
        RAISE EXCEPTION 'resource_monitoring.on_delete_claim_update_resource_usages: usage_at_end should not be NULL.';
    END IF;

    IF usage_at_start.usage = old_claim.claim_size THEN
        IF usage_at_end.usage = 0 THEN
            --RAISE NOTICE 'UPDATE resource_monitoring.resource_usage ru SET usage = 0 WHERE ru.id = %', usage_at_start.id;
            UPDATE resource_monitoring.resource_usage ru SET usage = 0 WHERE ru.id = usage_at_start.id;
        ELSE
            --usage_at_start was 'caused' by this deleted claim only, so delete it
            --RAISE NOTICE 'DELETE FROM resource_monitoring.resource_usage ru WHERE ru.id = %', usage_at_start.id;
            DELETE FROM resource_monitoring.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
        --RAISE NOTICE 'UPDATE resource_monitoring.resource_usage ru SET usage = % WHERE ru.id = %', usage_at_start.usage - old_claim.claim_size, usage_at_start.id;
        UPDATE resource_monitoring.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
        --RAISE NOTICE 'DELETE FROM resource_monitoring.resource_usage ru WHERE ru.id = %', usage_at_end.id;
        DELETE FROM resource_monitoring.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_monitoring.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
        --RAISE NOTICE 'LOOP UPDATE resource_monitoring.resource_usage ru SET usage = % WHERE ru.id = %', intermediate_usage.usage - old_claim.claim_size, intermediate_usage.id;
        UPDATE resource_monitoring.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_monitoring.process_old_claim_outof_resource_usages(old_claim resource_allocation.resource_claim) OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_monitoring.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_monitoring.resource_usage table with timeseries of accumulated resource_claim.sizes';

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

DROP FUNCTION IF EXISTS resource_monitoring.get_resource_usage_at(_resource_id int, _claim_status_id int, _timestamp timestamp);

CREATE OR REPLACE FUNCTION resource_monitoring.get_resource_usage_at(_resource_id int, _claim_status_id int, _timestamp timestamp)
  RETURNS resource_monitoring.resource_usage AS
$$
DECLARE
    result record;
BEGIN
    SELECT * FROM resource_monitoring.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;

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

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

DROP FUNCTION IF EXISTS resource_monitoring.get_max_resource_usage_between(_resource_id int, _claim_status_id int, _lower timestamp, _upper timestamp);

CREATE OR REPLACE FUNCTION resource_monitoring.get_max_resource_usage_between(_resource_id int, _claim_status_id int, _lower timestamp, _upper timestamp)
  RETURNS resource_monitoring.resource_usage AS
$$
DECLARE
    result record;
BEGIN
    SELECT * FROM resource_monitoring.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 result;

    RETURN result;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION resource_monitoring.get_max_resource_usage_between(_resource_id int, _claim_status_id int, _lower timestamp, _upper timestamp) OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_monitoring.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';

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

DROP FUNCTION IF EXISTS resource_monitoring.get_resource_claimable_capacity_between(_resource_id int, _lower timestamp, _upper timestamp);

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

    IF max_resource_usage IS NULL THEN
        max_resource_usage_value := 0;
    ELSE
        max_resource_usage_value := max_resource_usage.usage;
    END IF;

    SELECT available FROM resource_monitoring.resource_capacity WHERE resource_id = _resource_id LIMIT 1 INTO available_capacity;

    RETURN available_capacity - max_resource_usage_value;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION resource_monitoring.get_resource_claimable_capacity_between(_resource_id int, _lower timestamp, _upper timestamp) OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_monitoring.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';

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

DROP FUNCTION IF EXISTS resource_monitoring.check_new_claim_for_conflicts(new_claim resource_allocation.resource_claim);

CREATE OR REPLACE FUNCTION resource_monitoring.check_new_claim_for_conflicts(new_claim resource_allocation.resource_claim)
  RETURNS void AS
$$
DECLARE
claimed_status_id int := 0; --beware: hard coded instead of lookup for performance
conflict_status_id int := 2; --beware: hard coded instead of lookup for performance
free_claimable_capacity bigint;
BEGIN
    --only check claimes in claimed status
    IF new_claim.status_id = claimed_status_id THEN
        -- get the free free_claimable_capacity for this resource for this claim's time window
        SELECT * FROM resource_monitoring.get_resource_claimable_capacity_between(new_claim.resource_id, new_claim.starttime, new_claim.endtime) INTO free_claimable_capacity;

        -- if new claim to large, set claim status to conflict
        IF new_claim.claim_size > free_claimable_capacity THEN
            UPDATE resource_allocation.resource_claim SET status_id=conflict_status_id WHERE id=new_claim.id;
        END IF;
    END IF;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION resource_monitoring.check_new_claim_for_conflicts(new_claim resource_allocation.resource_claim) OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_monitoring.check_new_claim_for_conflicts(new_claim resource_allocation.resource_claim)
  IS 'helper function which is called by resource_claim table insert and update triggers, which checks if the new claim fits in the free capacity of its resource, else put claim in conflict status';

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

DROP TRIGGER IF EXISTS T_on_claim_insertupdatedelete ON resource_allocation.resource_claim CASCADE;
DROP FUNCTION IF EXISTS resource_monitoring.on_claim_insertupdatedelete();

CREATE OR REPLACE FUNCTION resource_monitoring.on_claim_insertupdatedelete()
  RETURNS trigger AS
$BODY$
BEGIN
    --first update the resource usages affected by this claim
    --order is important, first clear old claim, then apply new claim
    IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
        PERFORM resource_monitoring.process_old_claim_outof_resource_usages(OLD);
    END IF;
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        PERFORM resource_monitoring.process_new_claim_into_resource_usages(NEW);
    END IF;

    --then check if claim fits
    PERFORM resource_monitoring.check_new_claim_for_conflicts(NEW);

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

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

CREATE TRIGGER T_on_claim_insertupdatedelete
  AFTER INSERT OR UPDATE OR DELETE
  ON resource_allocation.resource_claim
  FOR EACH ROW
  EXECUTE PROCEDURE resource_monitoring.on_claim_insertupdatedelete();

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

DROP TRIGGER IF EXISTS T_before_claim_insertupdatedelete ON resource_allocation.resource_claim CASCADE;
DROP FUNCTION IF EXISTS resource_monitoring.before_claim_insertupdatedelete();

CREATE OR REPLACE FUNCTION resource_monitoring.before_claim_insertupdatedelete()
  RETURNS trigger AS
$BODY$
DECLARE
    claimed_status_id int := 0; --beware: hard coded instead of lookup for performance
BEGIN
    IF NEW.status_id != claimed_status_id THEN
        RAISE EXCEPTION 'newly inserted claims should not have status other than claimed; claim: %', NEW;
    END IF;
    RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION resource_monitoring.before_claim_insertupdatedelete()
  OWNER TO resourceassignment;

CREATE TRIGGER T_before_claim_insertupdatedelete
  BEFORE INSERT
  ON resource_allocation.resource_claim
  FOR EACH ROW
  EXECUTE PROCEDURE resource_monitoring.before_claim_insertupdatedelete();

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


COMMIT;