--
-- This function adds a story to a container at the specified position, or moves an existing story to a new
-- position. It also ensures that the story appears in only a single container, although that is a convenience
-- of this function, rather than a requirement of the design.
--
-- container.items is an array of container items (which might be stories, other containers, or other things)
--
-- The result is similar to creating a many-many table and indexing it, but is much faster in a large,
-- shared table with sparse data, because it requires much less I/O to find the list of contained items,
-- since we don't need to look up an index on, or update, the many-many table, at the cost of only a little
-- more complexity (ie, this function).
--
--   _tid         team ID
--   _container   container ID to add story to
--   _story       story ID to add
--   _position    position to add story (1-based)
-- 
create or replace function backlog.container_add_story(_tid int, _container int, _story int, _position int) returns void language plpgsql as $$
declare
    _backlog backlog.backlog_t;
    _new_container backlog.container_t;
    _new_item backlog.container_item_t;
    _items backlog.container_item_t[];
    _items_left backlog.container_item_t[];
    _items_right backlog.container_item_t[];

begin
    _new_item = ('STORY', _story)::backlog.container_item_t;

    -- Referential integrity check
    select backlog into _backlog from backlog.story where tid = _tid and story = _story;
    if not found then
        raise exception 'story not found: %', _story;
    end if;

    --
    -- Remove the story from any existing container(s). This is an intentional feature of this
    -- function, but a decision that might be revisited in the future; being able to add a story
    -- to multiple containers might later become a feature.
    --
    update backlog.container set items = array_remove(items, _new_item) where tid=_tid and backlog=_backlog and _new_item = any (items);

    --
    -- Now, add the story to the specific, identified container. The story was already removed, if it
    -- existed, by the previous update.
    --
    select container, items into _new_container, _items from backlog.container where tid = _tid and container = _container;
    if not found then
        raise exception 'container not found: %', _container;
    end if;

    --
    -- Compute the slice to the left and right of the position in which the story is to be
    -- added. (This could be done directly in the `select` statement, but it seems a bit clearer to do it
    -- explicity)
    --
    _items_left = _items[:_position - 1];
    _items_right = _items[_position:];

    update backlog.container set items = _items_left || _new_item || _items_right where tid = _tid and backlog=_backlog and container = _container;
end;
$$;