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;

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 last known resource_usage at or before claim starttime
    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
    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_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.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_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_table_from_claims()
  RETURNS void AS
$$
DECLARE
    claim resource_allocation.resource_claim;
BEGIN
    LOCK TABLE resource_allocation.resource_claim IN EXCLUSIVE MODE;
    LOCK TABLE resource_allocation.resource_usage IN EXCLUSIVE MODE;
    TRUNCATE TABLE resource_allocation.resource_usage RESTART IDENTITY CASCADE;

    FOR claim IN SELECT * FROM resource_allocation.resource_claim LOOP
        PERFORM resource_allocation.process_new_claim_into_resource_usages(claim);
    END LOOP;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION resource_allocation.rebuild_resource_usages_table_from_claims() OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.rebuild_resource_usages_table_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.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.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 at claim endtime
    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.endtime
    LIMIT 1
    INTO 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_allocation.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_allocation.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
            -- find resource_usage before claim starttime
            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
            ORDER BY ru.as_of_timestamp DESC
            LIMIT 1
            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(_resource_id int, _claim_status_id int, _timestamp timestamp)
  RETURNS resource_allocation.resource_usage AS
$$
DECLARE
    result record;
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;

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

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

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
    result record;
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 >= _lower
    AND ru.as_of_timestamp <= _upper
    ORDER BY ru.usage DESC
    LIMIT 1 INTO result;

    RETURN result;
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;
BEGIN
    SELECT * FROM resource_allocation.get_max_resource_usage_between(_resource_id, claimed_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_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
    claim_claimed_status_id int := 1; --beware: hard coded instead of lookup for performance
    free_claimable_capacity bigint;
    overlapping_claims_min_starttime timestamp;
    overlapping_claims_max_endtime timestamp;
BEGIN
    -- this function is quite similar to resource_allocation.get_conflicting_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.
    SET LOCAL client_min_messages=warning; --prevent "table overlapping_claims does not exist, skipping" message
    DROP TABLE IF EXISTS overlapping_claims; -- TODO: use  CREATE TEMPORARY TABLE IF NOT EXISTS when we will use postgres 9.5+
    SET LOCAL client_min_messages=info; --back to normal log level
    CREATE TEMPORARY TABLE overlapping_claims
    ON COMMIT DROP
    AS SELECT * FROM resource_allocation.resource_claim rc
    WHERE rc.resource_id = claim.resource_id
    AND rc.status_id = claim_claimed_status_id
    AND rc.id <> claim.id
    AND rc.endtime >= claim.starttime
    AND rc.starttime < claim.endtime;

    --get the full time window of the overlapping claims
    SELECT min(starttime) FROM overlapping_claims INTO overlapping_claims_min_starttime;
    SELECT max(endtime) FROM overlapping_claims INTO overlapping_claims_max_endtime;

    -- get the free free_claimable_capacity for this resource for the full overlapping claim 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, overlapping_claims_min_starttime, overlapping_claims_max_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_conflicting_overlapping_claims(claim_id int)
  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_conflicting_overlapping_claims(claim);
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION resource_allocation.get_conflicting_overlapping_claims(claim_id int) OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.get_conflicting_overlapping_claims(claim_id int)
  IS 'get set of (claimed) claims which cause the given claim to have conflict status.';

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

CREATE OR REPLACE FUNCTION resource_allocation.get_conflicting_overlapping_claims(claim resource_allocation.resource_claim)
  RETURNS SETOF resource_allocation.resource_claim AS
$$
DECLARE
    claim_claimed_status_id int := 1; --beware: hard coded instead of lookup for performance
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.
    SET LOCAL client_min_messages=warning; --prevent "table overlapping_claims does not exist, skipping" message
    DROP TABLE IF EXISTS overlapping_claims; -- TODO: use  CREATE TEMPORARY TABLE IF NOT EXISTS when we will use postgres 9.5+
    CREATE TEMPORARY TABLE overlapping_claims
    ON COMMIT DROP
    AS SELECT * FROM resource_allocation.resource_claim rc
    WHERE rc.resource_id = claim.resource_id
    AND rc.status_id = claim_claimed_status_id
    AND rc.id <> claim.id
    AND rc.endtime >= claim.starttime
    AND rc.starttime < claim.endtime;

    RETURN QUERY SELECT *
    FROM overlapping_claims oc
    WHERE (SELECT * FROM resource_allocation.get_resource_claimable_capacity_between(claim.resource_id, oc.starttime, oc.endtime)) < claim.claim_size;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION resource_allocation.get_conflicting_overlapping_claims(claim resource_allocation.resource_claim) OWNER TO resourceassignment;
COMMENT ON FUNCTION resource_allocation.get_conflicting_overlapping_claims(claim resource_allocation.resource_claim)
  IS 'get set of (claimed) claims which cause the given claim to have conflict status.';

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

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; claim: %', NEW;
        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;

    --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
    IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
        --update the resource usages affected by this claim
        PERFORM resource_allocation.process_old_claim_outof_resource_usages(OLD);
    END IF;

    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;