--------------------------------------------------------------------------------
--      Copyright Richard Emberson 
--      Use but remember me (copyright must appear in all copies).
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- group limits table
--      keeps the available ranges of group bounds
--      starts from 0 to 9223372036854775807
--      hold negative in reserve
--------------------------------------------------------------------------------
DROP INDEX group_limits_lower_bound_index;
DROP INDEX group_limits_upper_bound_index;
DROP TABLE group_limits;

CREATE TABLE group_limits (
    lower_bound                 BIGINT NOT NULL,
    size                        BIGINT NOT NULL,
    upper_bound                 BIGINT NOT NULL
);
CREATE INDEX group_limits_lower_bound_index ON group_limits (lower_bound);
CREATE INDEX group_limits_upper_bound_index ON group_limits (upper_bound);


--------------------------------------------------------------------------------
-- load initial values
INSERT INTO group_limits (
    lower_bound,
    size,
    upper_bound
) VALUES (
    0,
    9223372036854775807,
    9223372036854775807
);

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

/*
    Get a group range of the size specified by the input parameter.
    This gets from the group_limits table the range from the row that
    has the smallest range greater than size.
    If it is equals to the size, the row is deleted.
    If it is greater than the size, the row's range is adjusted.
    input:
        size
    return:
       if > 0 then it is the lower_bound of the segment
       -1100011 - no more segments;

    select group_limits_get(10);
    select * from group_limits;
*/
CREATE OR REPLACE FUNCTION group_limits_get(BIGINT)
RETURNS BIGINT AS '            
DECLARE
    -- parameters
    size_p ALIAS FOR $1;
    -- variables
    lb_v BIGINT;
    ub_v BIGINT;
    size_v BIGINT;
    row_count_v INTEGER;
BEGIN
    SELECT gl1.lower_bound, gl1.size, gl1.upper_bound INTO lb_v, size_v, ub_v
        FROM group_limits gl1
        WHERE gl1.size IN (SELECT MIN(gl2.size)
            FROM group_limits gl2
            WHERE gl2.size >= size_p);

    IF size_v IS NULL THEN
        -- some sort of fatal error
        return -1100011;
    ELSIF size_v = size_p THEN
        -- exact match so remove the whole row
        DELETE FROM group_limits WHERE lower_bound = lb_v;
    ELSE
        -- its bigger than needed so modify row
        UPDATE group_limits 
            SET lower_bound = (lb_v+size_p), size = (size_v-size_p)
            WHERE lower_bound = lb_v;
    END IF;
    GET DIAGNOSTICS row_count_v = ROW_COUNT;
    IF row_count_v <> 1 THEN
        RETURN -row_count_v;
    END IF;

    RETURN lb_v;

END; ' LANGUAGE 'plpgsql';

/*
    Return a group range.
    If a row exists such that the lb_p+size_p == lower_bound, then
    modify the row. Also then check if there is another row
    where its lower_bound+size == lb_p+size_p. If so, then coalesce
    the two rows.
    If no row exists such that lb_p+size_p == lower_bound, then add
    a new row.
    input:
        lower_bound
        size
    return:
       1 - no higher, no lower, insert new row
       2 - coalesce with lower
       3 - coalesce with higher
       4 - coalesce with both lower and higher
       -1100012 - lower_bound < lb_p AND upper_bound > lb_p;
       -1100013 -- lower_bound < (lb_p+size_p) AND upper_bound > (lb_p+size_p);

    \i group_limits.sql
    select * from group_limits;
tests
    select group_limits_get(10);
    select group_limits_return(0,10);
    0-100
    select group_limits_get(10);
    select group_limits_get(10);
    select group_limits_return(0,10);
    0-10, 10,100
    select group_limits_return(10,10);
    0-100
    select group_limits_get(10);
    select group_limits_get(10);
    select group_limits_return(10,10);
    10-100
    select group_limits_return(0,10);
    0-100

    select group_limits_return(0,10);
    select group_limits_return(10,10);
    select group_limits_return(20,10);
*/
CREATE OR REPLACE FUNCTION group_limits_return(BIGINT, BIGINT)
RETURNS BIGINT AS '            
DECLARE
    -- parameters
    lb_p ALIAS FOR $1;
    size_p ALIAS FOR $2;
    -- variables
    lb_h_v BIGINT;
    size_h_v BIGINT;
    lb_l_v BIGINT;
    size_l_v BIGINT;

    row_count_v INTEGER;
BEGIN
    -- These two check are just in case someone at a high application level
    -- tries to do something bad. It would be a real screw up if bad
    -- data was passed in.

    -- check: is lb_p between an existing rows lower and upper values
    SELECT count(*) INTO row_count_v
        FROM group_limits
        WHERE lower_bound < lb_p AND upper_bound > lb_p;
    IF row_count_v <> 0 THEN
        RETURN -1100012; 
    END IF;

    -- check: is lb_p+size_p between an existing rows lower and upper values
    SELECT count(*) INTO row_count_v
        FROM group_limits
        WHERE lower_bound < (lb_p+size_p) AND upper_bound > (lb_p+size_p);
    IF row_count_v <> 0 THEN
        RETURN -1100013; 
    END IF;

    -- look for the higher row
    SELECT lower_bound, size INTO lb_h_v, size_h_v
        FROM group_limits
        WHERE lower_bound = (lb_p+size_p);
    -- look for the lower row
    SELECT lower_bound, size INTO lb_l_v, size_l_v
        FROM group_limits
        WHERE upper_bound = lb_p;

    IF lb_h_v IS NULL THEN
        -- no higher row, so see if there is a lower row
        IF lb_l_v IS NULL THEN
            -- no lower row, so just add a new row
            INSERT INTO group_limits (
                lower_bound,
                size,
                upper_bound
            ) VALUES (
                lb_p,
                size_p,
                size_p+lb_p
            );
            RETURN 1;
        ELSE
            -- lower row, so coalesce
            -- lower_bound remains the same
            -- size += size_p
            -- upper_bound += size_p
            UPDATE group_limits 
                SET size = (size_l_v+size_p), 
                    upper_bound = (lb_p+size_p)
                WHERE upper_bound = lb_p;
            RETURN 2;
        END IF;

    ELSE
        -- higher row
        IF lb_l_v IS NULL THEN
            -- no lower row, so coalesce with higher
            -- lower_bound = lb_p
            -- size += size_p 
            UPDATE group_limits 
                SET lower_bound = lb_p, 
                    size = (size_h_v+size_p)
                WHERE lower_bound = (lb_p+size_p);
            RETURN 3;
        ELSE
            -- lower row, coalesce with both lower and higher
            -- delete higher row (must delete one of them)
            DELETE FROM group_limits
                WHERE lower_bound = (lb_p+size_p);
            -- lower_bound = lb_l_v
            -- size += size_p+size_h_v
            -- upper_bound = rb_h_v = lb_h_v+size_h_v
            UPDATE group_limits 
                SET size = (size_h_v+size_l_v+size_p), 
                    upper_bound = (lb_h_v+size_h_v)
                WHERE lower_bound = lb_l_v;
            RETURN 4;
        END IF;
    END IF;
/*
    GET DIAGNOSTICS row_count_v = ROW_COUNT;
    IF row_count_v <> 1 THEN
        RETURN -row_count_v;
    END IF;

    RETURN lb_v;
*/

END; ' LANGUAGE 'plpgsql';
