nightmare.sql

nightmare-2.sql

drop function if exists create_workflow(
    name text, created_by varchar(24), workflow_type workflow_type, milestone_name text
);


create function create_workflow(name text, created_by varchar(24), workflow_type workflow_type, milestone_name text)
    returns uuid
    language plpgsql
as
$$
declare
    workflow_id  uuid;
    root_id      uuid;
    tail_id      uuid;
    milestone_id uuid;
begin
    insert into workflow (name, created_by, milestone_name)
    values (name, created_by, milestone_name)
    returning id into workflow_id;

    insert into action (workflow_id, name, type, day, time)
    values (workflow_id, 'root', 'root', -999999, '0 days')
    returning id into root_id;

    insert into action (workflow_id, name, type, day, time)
    values (workflow_id, 'tail', 'tail', 999999, '00:00')
    returning id into tail_id;

    insert into action (workflow_id, name, type, day, time)
    values (workflow_id, milestone_name, 'milestone', 0, '00:00')
    returning id into milestone_id;

    insert into action_child (parent_id, child_id, preconditions)
    values (root_id, milestone_id, null),
           (milestone_id, tail_id, null);

    return workflow_id;
end ;
$$;



drop function if exists create_action(
    name text, payload json, parent_id uuid, day integer,
    "time" interval, type action_type, task_type action_task_type
);


create function "create_action"("name" text,
                                "payload" json,
                                "parent_id" uuid,
                                "day" integer,
                                "time" interval,
                                "type" action_type,
                                "task_type" action_task_type) returns uuid
    language plpgsql
as
$$
declare
    action_id             uuid;
    prev_child_id         uuid;
    p_id                  uuid := parent_id;
    w_id                  uuid;
    milestone_id          uuid;
    parent_day            integer;
    parent_time           interval;
    parent_execute_at     interval;
    prev_child_day        integer;
    prev_child_time       interval;
    prev_child_execute_at interval;
    execute_at            interval;
    _time                 interval;
begin

    select time into _time;
    select a.day, a.time, a.workflow_id into parent_day, parent_time, w_id from action a where id = p_id;
    select a.id into milestone_id from action a where a.workflow_id = w_id and a.day = 0 and a.time = '00:00'::time;
    select ac.child_id into prev_child_id from action_child ac where ac.parent_id = p_id;
    select a.day, a.time into prev_child_day, prev_child_time from action a where a.id = prev_child_id;

    select parent_time::interval + format('%s days', parent_day)::interval into parent_execute_at;
    select prev_child_time::interval + format('%s days', prev_child_day)::interval into prev_child_execute_at;
    select _time::interval + format('%s days', day)::interval into execute_at;

    if execute_at < parent_execute_at or execute_at > prev_child_execute_at then
        raise exception 'out of bounds';
    end if;

    insert into action (workflow_id, name, payload, day, time, type, task_type)
    VALUES (w_id, name, payload, day, time, type, task_type)
    returning id into action_id;

    insert into action_child (parent_id, child_id, preconditions)
    values (parent_id, action_id, null),
           (action_id, prev_child_id, null);

    delete from action_child ac where ac.child_id = prev_child_id and ac.parent_id = p_id;

    return action_id;
end;
$$;



drop function if exists "update_action"("action_id" uuid, "name" text, "payload" json, "day" integer, "time" interval);

create function "update_action"("action_id" uuid,
                                "name" text,
                                "payload" json,
                                "day" integer,
                                "time" interval
) returns uuid
    language plpgsql
as
$$
declare
    _time             time;
    p_id              uuid;
    c_id              uuid;
    parent_execute_at interval;
    child_execute_at  interval;
    execute_at        interval;
    _name             text    := name;
    _payload          json    := payload;
    _day              integer := day;
begin
    select time::time into _time;
    select parent_id into p_id from action_child where child_id = action_id;
    select child_id into c_id from action_child where parent_id = action_id limit 1;

    select a."time"::interval + format('%s days', a.day)::interval
    into parent_execute_at
    from action a
    where action_id = p_id;

    select a."time"::interval + format('%s days', a.day)::interval
    into child_execute_at
    from action a
    where action_id = c_id;

    select _time::interval + format('%s days', day)::interval into execute_at;

    if execute_at < parent_execute_at or execute_at > child_execute_at then
        raise exception 'out of bounds';
    end if;

    update action a
    set time    = coalesce(_time, a.time),
        name    = coalesce(_name, a.name),
        payload = coalesce(_payload, a.payload),
        day     = coalesce(_day, a.day)
    where id = action_id;

    return action_id;
end;
$$;

drop function if exists create_branch_no_descendents(parent_id uuid, branch_type branch_type, branch_name text,
                                                     condition json);

-- There are no branches.
create function create_branch_no_descendents(parent_id uuid, branch_type branch_type, branch_name text, condition json)
    returns void
    language plpgsql
as
$$
declare
    p_id                uuid := parent_id;
    prev_child_id       uuid;
    w_id                uuid;
    tail1_id            uuid;
    tail2_id            uuid;
    condition_branch_id uuid;
    default_branch_id   uuid;
    branch_root_id      uuid;
    parent_day          integer;
    parent_time         interval;
begin
    select workflow_id, day, time into w_id, parent_day, parent_time from action where id = p_id;
    select ac.child_id into prev_child_id from action_child ac where ac.parent_id = p_id;

    insert into action (workflow_id, name, type, day, time)
    values (w_id, 'tail', 'tail', 999999, '00:00')
    returning id into tail1_id;

    insert into action (workflow_id, name, type, day, time)
    values (w_id, 'tail', 'tail', 999999, '00:00')
    returning id into tail2_id;

    insert into action (workflow_id, name, type, day, time)
    values (w_id, branch_name, 'branch', parent_day, parent_time)
    returning id into condition_branch_id;

    insert into action (workflow_id, name, type, day, time)
    values (w_id, 'Default branch', 'branch', parent_day, parent_time)
    returning id into default_branch_id;

    insert into action (workflow_id, name, type, day, time)
    values (w_id, '', 'branch-root', parent_day, parent_time)
    returning id into branch_root_id;

    insert into action_child (parent_id, child_id, preconditions, type)
    values (condition_branch_id, tail1_id, null, null),
           (default_branch_id, tail2_id, null, null),
           (branch_root_id, condition_branch_id, condition, branch_type),
           (branch_root_id, default_branch_id, '{}'::jsonb, branch_type),
           (parent_id, branch_root_id, null, null);

    delete from action_child ac where ac.parent_id = p_id and child_id = prev_child_id;
end;
$$;


drop type if exists "move_descendents_to" cascade;
create type "move_descendents_to" as enum ('default', 'condition', 'join');



drop function if exists create_branch_move_descendents(parent_id uuid,
                                                       branch_type branch_type,
                                                       branch_name text,
                                                       condition json,
                                                       move_descendents_to move_descendents_to);

-- There already are actions below this, so the end user should pick
-- which branch, the default or condition branch, they want the
-- previous actions to go to
create function create_branch_move_descendents(parent_id uuid,
                                               branch_type branch_type,
                                               branch_name text,
                                               condition json,
                                               move_descendents_to move_descendents_to)
    returns uuid
    language plpgsql
as
$$
declare
    p_id                uuid := parent_id;
    prev_child_id       uuid;
    w_id                uuid;
    parent_day          integer;
    parent_time         interval;
    condition_branch_id uuid;
    default_branch_id   uuid;
    branch_root_id      uuid;
    tail_id             uuid;
begin
    select ac.child_id into prev_child_id from action_child ac where ac.parent_id = p_id;
    select workflow_id, day, time into w_id, parent_day, parent_time from action where id = p_id;

    insert into action (workflow_id, name, type, day, time)
    values (w_id, branch_name, 'branch', parent_day, parent_time)
    returning id into condition_branch_id;

    insert into action (workflow_id, name, type, day, time)
    values (w_id, 'Default Branch', 'branch', parent_day, parent_time)
    returning id into default_branch_id;

    insert into action (workflow_id, name, type, day, time)
    values (w_id, '', 'branch-root', parent_day, parent_time)
    returning id into branch_root_id;

    insert into action_child (parent_id, child_id, preconditions, type)
    values (p_id, branch_root_id, null, null),
           (branch_root_id, condition_branch_id, condition, branch_type),
           (branch_root_id, default_branch_id, '{}'::jsonb, branch_type);

    delete
    from action_child ac
    where ac.parent_id = p_id
      and ac.child_id = prev_child_id;

    if move_descendents_to = 'default' then
        insert into action (workflow_id, name, type, day, time)
        values (w_id, 'tail', 'tail', 999999, '00:00')
        returning id into tail_id;

        insert into action_child (parent_id, child_id, preconditions, type)
        values (default_branch_id, prev_child_id, null, null),
               (condition_branch_id, tail_id, null, null);

    elseif move_descendents_to = 'condition' then
        insert into action (workflow_id, name, type, day, time)
        values (w_id, 'tail', 'tail', 999999, '00:00')
        returning id into tail_id;

        insert into action_child (parent_id, child_id, preconditions, type)
        values (default_branch_id, tail_id, null, null),
               (condition_branch_id, prev_child_id, null, null);
    else
        insert into action_child (parent_id, child_id, preconditions, type)
        values (default_branch_id, prev_child_id, null, null),
               (condition_branch_id, prev_child_id, null, null);
    end if;

    return branch_root_id;
end;
$$;


drop function if exists add_branch(parent_id uuid, branch_name text, branch_type branch_type, condition json);

-- Branch already exists. Add another branch to it.
create function add_branch(parent_id uuid, branch_name text, branch_type branch_type, condition json)
    returns void
    language plpgsql
as
$$
declare
    p_id                uuid := parent_id;
    prev_child_id       uuid;
    w_id                uuid;
    parent_day          integer;
    parent_time         interval;
    condition_branch_id uuid;
    unique_children     integer;
    tail_id             uuid;
begin
    select workflow_id, day, time into w_id, parent_day, parent_time from action where id = p_id;
    select ac.child_id into prev_child_id from action_child ac where ac.parent_id = p_id;

    insert into action (workflow_id, name, type, day, time)
    values (w_id, branch_name, 'branch', parent_day, parent_time)
    returning id into condition_branch_id;

    insert into action_child(parent_id, child_id, preconditions, type)
    values (parent_id, condition_branch_id, condition, branch_type);

    select count(distinct child_id) into unique_children from action_child ac where ac.parent_id = p_id;
    if unique_children = 1 then
        -- all of the children have the same tail
        -- TODO: make sure this condition is going to be enough!
        declare
            sibling_id uuid;
            c_id       uuid;
        begin
            select ac.child_id into sibling_id from action_child ac where ac.parent_id = p_id limit 1;
            if found then
                select ac.child_id into c_id from action_child ac where ac.parent_id = sibling_id limit 1;
                insert into action_child(parent_id, child_id, preconditions)
                values (condition_branch_id, c_id, null);
            end if;
        end;
    else
        -- the children do _not_ have the same tail
        insert into action (workflow_id, name, type, day, time)
        values (w_id, 'tail', 'tail', 999999, '00:00`')
        returning id into tail_id;

        insert into action_child(parent_id, child_id, preconditions)
        values (condition_branch_id, tail_id, null);
    end if;
end;
$$;

drop function if exists assign_workflow;

create function assign_workflow(workflow_id uuid) returns void
    language plpgsql
as
$$
declare
    w_id       uuid := workflow_id;
    action_ids uuid[];
    u_         record;
begin
    select array_agg(a.id) into action_ids from action a where a.workflow_id = w_id;
    for u_ in select id from users offset 30
        loop
            insert into action_instance(instance_of, assigned_to) values (unnest(action_ids), u_.id);
        end loop;
end;
$$;

drop function if exists assign_workflow_to_userlist(workflow_id uuid, userlist_id uuid, assigned_by varchar(24));

create function assign_workflow_to_userlist(workflow_id uuid, userlist_id uuid, assigned_by varchar(24)) returns void
    language plpgsql
as
$$
declare
    w_id       uuid := workflow_id;
    ul_id      uuid := userlist_id;
    action_ids uuid[];
    u_         record;
begin
    select array_agg(a.id) into action_ids from action a where a.workflow_id = w_id;
    for u_ in select um.user_id from userlist_members um where um.userlist_id = ul_id
        loop
            insert into action_instance(instance_of, assigned_to) values (unnest(action_ids), u_.user_id);
        end loop;

    insert into workflow_userlist(workflow_id, userlist_id, assigned_by) values (w_id, ul_id, assigned_by);
end;
$$;


drop function if exists create_action_before("action_id" uuid,
                                             "task_type" action_task_type);

create function create_action_before("action_id" uuid,
                                     "task_type" action_task_type) returns uuid
    language plpgsql
as
$$
declare
    prev_action_id       uuid             := action_id;
    prev_action_day      integer;
    prev_action_time     interval;
    workflow_id          uuid;
    new_action_id        uuid;
    new_action_task_type action_task_type := task_type;
    prev_parent_id       uuid;
begin
    select a.day, a.time, a.workflow_id
    into prev_action_day, prev_action_time, workflow_id
    from action a
    where id = prev_action_id;

    insert into action (workflow_id, name, payload, day, time, type, task_type)
    VALUES (workflow_id, 'Action Name', '{}'::json, prev_action_day, prev_action_time - (1 * interval '1 second'),
            'action', new_action_task_type)
    returning id into new_action_id;


    select ac.parent_id into prev_parent_id from "action_child" ac where ac.child_id = prev_action_id;
    insert into action_child (parent_id, child_id, preconditions)
    values (prev_parent_id, new_action_id, null),
           (new_action_id, prev_action_id, null);

    delete from action_child ac where ac.child_id = prev_action_id and ac.parent_id = prev_parent_id;
    return new_action_id;
end;
$$;

drop function if exists create_action_after("action_id" uuid,
                                            "task_type" action_task_type);

create function create_action_after("action_id" uuid,
                                    "task_type" action_task_type) returns uuid
    language plpgsql
as
$$
declare
    prev_action_id       uuid             := action_id;
    prev_action_day      integer;
    prev_action_time     interval;
    workflow_id          uuid;
    new_action_id        uuid;
    new_action_task_type action_task_type := task_type;
    prev_child_id        uuid;
begin
    select a.day, a.time, a.workflow_id
    into prev_action_day, prev_action_time, workflow_id
    from action a
    where id = prev_action_id;

    insert into action (workflow_id, name, payload, day, time, type, task_type)
    VALUES (workflow_id, 'Action Name', '{}'::json, prev_action_day,
            prev_action_time + (1 * interval '1 second'), 'action', new_action_task_type)
    returning id into new_action_id;


    select ac.child_id into prev_child_id from "action_child" ac where ac.parent_id = prev_action_id;
    insert into action_child (parent_id, child_id, preconditions)
    values (new_action_id, prev_child_id, null),
           (prev_action_id, new_action_id, null);

    delete from action_child ac where ac.child_id = prev_child_id and ac.parent_id = prev_action_id;
    return new_action_id;
end;
$$;

nightmare.sql

drop function if exists create_workflow(
    name text, created_by varchar(24), workflow_type workflow_type, milestone_name text
);


create function create_workflow(name text, created_by varchar(24), workflow_type workflow_type, milestone_name text)
    returns uuid
    language plpgsql
as
$$
declare
    workflow_id  uuid;
    root_id      uuid;
    tail_id      uuid;
    milestone_id uuid;
begin
    insert into workflow (name, created_by, milestone_name)
    values (name, created_by, milestone_name)
    returning id into workflow_id;

    insert into action (workflow_id, name, type, day, time)
    values (workflow_id, 'root', 'root', -999999, '00:00')
    returning id into root_id;

    insert into action (workflow_id, name, type, day, time)
    values (workflow_id, 'tail', 'tail', 999999, '00:00')
    returning id into tail_id;

    insert into action (workflow_id, name, type, day, time)
    values (workflow_id, milestone_name, 'milestone', 0, '00:00')
    returning id into milestone_id;

    insert into action_child (parent_id, child_id, preconditions)
    values (root_id, milestone_id, null),
           (milestone_id, tail_id, null);

    return workflow_id;
end ;
$$;



drop function if exists create_action(
    name text, payload json, parent_id uuid, day integer,
    "time" time, type action_type, task_type action_task_type
);


create function "create_action"("name" text,
                                "payload" json,
                                "parent_id" uuid,
                                "day" integer,
                                "time" time,
                                "type" action_type,
                                "task_type" action_task_type) returns uuid
    language plpgsql
as
$$
declare
    action_id             uuid;
    prev_child_id         uuid;
    p_id                  uuid := parent_id;
    w_id                  uuid;
    milestone_id          uuid;
    parent_day            integer;
    parent_time           time;
    parent_execute_at     interval;
    prev_child_day        integer;
    prev_child_time       time;
    prev_child_execute_at interval;
    execute_at            interval;
    _time                 time;
begin

    select time::time into _time;
    select a.day, a.time, a.workflow_id into parent_day, parent_time, w_id from action a where id = p_id;
    select a.id into milestone_id from action a where a.workflow_id = w_id and a.day = 0 and a.time = '00:00'::time;
    select ac.child_id into prev_child_id from action_child ac where ac.parent_id = p_id;
    select a.day, a.time into prev_child_day, prev_child_time from action a where a.id = prev_child_id;

    select parent_time::interval + format('%s days', parent_day)::interval into parent_execute_at;
    select prev_child_time::interval + format('%s days', prev_child_day)::interval into prev_child_execute_at;
    select _time::interval + format('%s days', day)::interval into execute_at;

    if execute_at < parent_execute_at or execute_at > prev_child_execute_at then
        raise exception 'out of bounds';
    end if;

    insert into action (workflow_id, name, payload, day, time, type, task_type)
    VALUES (w_id, name, payload, day, time, type, task_type)
    returning id into action_id;

    insert into action_child (parent_id, child_id, preconditions)
    values (parent_id, action_id, null),
           (action_id, prev_child_id, null);

    delete from action_child ac where ac.child_id = prev_child_id and ac.parent_id = p_id;

    return action_id;
end;
$$;



drop function if exists "update_action"("action_id" uuid, "name" text, "payload" json, "day" integer, "time" time);

create function "update_action"("action_id" uuid,
                                "name" text,
                                "payload" json,
                                "day" integer,
                                "time" time
) returns uuid
    language plpgsql
as
$$
declare
    _time             time;
    p_id              uuid;
    c_id              uuid;
    parent_execute_at interval;
    child_execute_at  interval;
    execute_at        interval;
    _name             text    := name;
    _payload          json    := payload;
    _day              integer := day;
begin
    select time::time into _time;
    select parent_id into p_id from action_child where child_id = action_id;
    select child_id into c_id from action_child where parent_id = action_id limit 1;

    select a."time"::interval + format('%s days', a.day)::interval
    into parent_execute_at
    from action a
    where action_id = p_id;

    select a."time"::interval + format('%s days', a.day)::interval
    into child_execute_at
    from action a
    where action_id = c_id;

    select _time::interval + format('%s days', day)::interval into execute_at;

    if execute_at < parent_execute_at or execute_at > child_execute_at then
        raise exception 'out of bounds';
    end if;

    update action a
    set time    = coalesce(_time, a.time),
        name    = coalesce(_name, a.name),
        payload = coalesce(_payload, a.payload),
        day     = coalesce(_day, a.day)
    where id = action_id;

    return action_id;
end;
$$;

drop function if exists create_branch_no_descendents(parent_id uuid, branch_type branch_type, branch_name text,
                                                     condition json);

-- There are no branches.
create function create_branch_no_descendents(parent_id uuid, branch_type branch_type, branch_name text, condition json)
    returns void
    language plpgsql
as
$$
declare
    p_id                uuid := parent_id;
    prev_child_id       uuid;
    w_id                uuid;
    tail1_id            uuid;
    tail2_id            uuid;
    condition_branch_id uuid;
    default_branch_id   uuid;
    branch_root_id      uuid;
    parent_day          integer;
    parent_time         time;
begin
    select workflow_id, day, time into w_id, parent_day, parent_time from action where id = p_id;
    select ac.child_id into prev_child_id from action_child ac where ac.parent_id = p_id;

    insert into action (workflow_id, name, type, day, time)
    values (w_id, 'tail', 'tail', 999999, '00:00')
    returning id into tail1_id;

    insert into action (workflow_id, name, type, day, time)
    values (w_id, 'tail', 'tail', 999999, '00:00')
    returning id into tail2_id;

    insert into action (workflow_id, name, type, day, time)
    values (w_id, branch_name, 'branch', parent_day, parent_time)
    returning id into condition_branch_id;

    insert into action (workflow_id, name, type, day, time)
    values (w_id, 'Default branch', 'branch', parent_day, parent_time)
    returning id into default_branch_id;

    insert into action (workflow_id, name, type, day, time)
    values (w_id, '', 'branch-root', parent_day, parent_time)
    returning id into branch_root_id;

    insert into action_child (parent_id, child_id, preconditions, type)
    values (condition_branch_id, tail1_id, null, null),
           (default_branch_id, tail2_id, null, null),
           (branch_root_id, condition_branch_id, condition, branch_type),
           (branch_root_id, default_branch_id, '{}'::jsonb, branch_type),
           (parent_id, branch_root_id, null, null);

    delete from action_child ac where ac.parent_id = p_id and child_id = prev_child_id;
end;
$$;


drop type if exists "move_descendents_to" cascade;
create type "move_descendents_to" as enum ('default', 'condition', 'join');



drop function if exists create_branch_move_descendents(parent_id uuid,
                                                       branch_type branch_type,
                                                       branch_name text,
                                                       condition json,
                                                       move_descendents_to move_descendents_to);

-- There already are actions below this, so the end user should pick
-- which branch, the default or condition branch, they want the
-- previous actions to go to
create function create_branch_move_descendents(parent_id uuid,
                                               branch_type branch_type,
                                               branch_name text,
                                               condition json,
                                               move_descendents_to move_descendents_to)
    returns uuid
    language plpgsql
as
$$
declare
    p_id                uuid := parent_id;
    prev_child_id       uuid;
    w_id                uuid;
    parent_day          integer;
    parent_time         time;
    condition_branch_id uuid;
    default_branch_id   uuid;
    branch_root_id      uuid;
    tail_id             uuid;
begin
    select ac.child_id into prev_child_id from action_child ac where ac.parent_id = p_id;
    select workflow_id, day, time into w_id, parent_day, parent_time from action where id = p_id;

    insert into action (workflow_id, name, type, day, time)
    values (w_id, branch_name, 'branch', parent_day, parent_time)
    returning id into condition_branch_id;

    insert into action (workflow_id, name, type, day, time)
    values (w_id, 'Default Branch', 'branch', parent_day, parent_time)
    returning id into default_branch_id;

    insert into action (workflow_id, name, type, day, time)
    values (w_id, '', 'branch-root', parent_day, parent_time)
    returning id into branch_root_id;

    insert into action_child (parent_id, child_id, preconditions, type)
    values (p_id, branch_root_id, null, null),
           (branch_root_id, condition_branch_id, condition, branch_type),
           (branch_root_id, default_branch_id, '{}'::jsonb, branch_type);

    delete
    from action_child ac
    where ac.parent_id = p_id
      and ac.child_id = prev_child_id;

    if move_descendents_to = 'default' then
        insert into action (workflow_id, name, type, day, time)
        values (w_id, 'tail', 'tail', 999999, '00:00')
        returning id into tail_id;

        insert into action_child (parent_id, child_id, preconditions, type)
        values (default_branch_id, prev_child_id, null, null),
               (condition_branch_id, tail_id, null, null);

    elseif move_descendents_to = 'condition' then
        insert into action (workflow_id, name, type, day, time)
        values (w_id, 'tail', 'tail', 999999, '00:00')
        returning id into tail_id;

        insert into action_child (parent_id, child_id, preconditions, type)
        values (default_branch_id, tail_id, null, null),
               (condition_branch_id, prev_child_id, null, null);
    else
        insert into action_child (parent_id, child_id, preconditions, type)
        values (default_branch_id, prev_child_id, null, null),
               (condition_branch_id, prev_child_id, null, null);
    end if;

    return branch_root_id;
end;
$$;


drop function if exists add_branch(parent_id uuid, branch_name text, branch_type branch_type, condition json);

-- Branch already exists. Add another branch to it.
create function add_branch(parent_id uuid, branch_name text, branch_type branch_type, condition json)
    returns void
    language plpgsql
as
$$
declare
    p_id                uuid := parent_id;
    prev_child_id       uuid;
    w_id                uuid;
    parent_day          integer;
    parent_time         time;
    condition_branch_id uuid;
    unique_children     integer;
    tail_id             uuid;
begin
    select workflow_id, day, time into w_id, parent_day, parent_time from action where id = p_id;
    select ac.child_id into prev_child_id from action_child ac where ac.parent_id = p_id;

    insert into action (workflow_id, name, type, day, time)
    values (w_id, branch_name, 'branch', parent_day, parent_time)
    returning id into condition_branch_id;

    insert into action_child(parent_id, child_id, preconditions, type)
    values (parent_id, condition_branch_id, condition, branch_type);

    select count(distinct child_id) into unique_children from action_child ac where ac.parent_id = p_id;
    if unique_children = 1 then
        -- all of the children have the same tail
        -- TODO: make sure this condition is going to be enough!
        declare
            sibling_id uuid;
            c_id       uuid;
        begin
            select ac.child_id into sibling_id from action_child ac where ac.parent_id = p_id limit 1;
            if found then
                select ac.child_id into c_id from action_child ac where ac.parent_id = sibling_id limit 1;
                insert into action_child(parent_id, child_id, preconditions)
                values (condition_branch_id, c_id, null);
            end if;
        end;
    else
        -- the children do _not_ have the same tail
        insert into action (workflow_id, name, type, day, time)
        values (w_id, 'tail', 'tail', 999999, '00:00`')
        returning id into tail_id;

        insert into action_child(parent_id, child_id, preconditions)
        values (condition_branch_id, tail_id, null);
    end if;
end;
$$;

drop function if exists assign_workflow;

create function assign_workflow(workflow_id uuid) returns void
    language plpgsql
as
$$
declare
    w_id       uuid := workflow_id;
    action_ids uuid[];
    u_         record;
begin
    select array_agg(a.id) into action_ids from action a where a.workflow_id = w_id;
    for u_ in select id from users offset 30
        loop
            insert into action_instance(instance_of, assigned_to) values (unnest(action_ids), u_.id);
        end loop;
end;
$$;

drop function if exists assign_workflow_to_userlist(workflow_id uuid, userlist_id uuid, assigned_by varchar(24));

create function assign_workflow_to_userlist(workflow_id uuid, userlist_id uuid, assigned_by varchar(24)) returns void
    language plpgsql
as
$$
declare
    w_id       uuid := workflow_id;
    ul_id      uuid := userlist_id;
    action_ids uuid[];
    u_         record;
begin
    select array_agg(a.id) into action_ids from action a where a.workflow_id = w_id;
    for u_ in select um.user_id from userlist_members um where um.userlist_id = ul_id
        loop
            insert into action_instance(instance_of, assigned_to) values (unnest(action_ids), u_.user_id);
        end loop;

    insert into workflow_userlist(workflow_id, userlist_id, assigned_by) values (w_id, ul_id, assigned_by);
end;
$$;



select a.id, p.id, c.id
from action a
         left outer join action_child p on p.child_id = a.id
         left outer join action_child c on c.parent_id = a.id
where a.workflow_id = '83130715-62ca-4402-991c-bc25a770f183';



drop function if exists create_action_before("action_id" uuid,
                                             "task_type" action_task_type);

create function create_action_before("action_id" uuid,
                                     "task_type" action_task_type) returns uuid
    language plpgsql
as
$$
declare
    prev_action_id       uuid             := action_id;
    prev_action_day      integer;
    prev_action_time     time;
    workflow_id          uuid;
    new_action_id        uuid;
    new_action_task_type action_task_type := task_type;
    prev_parent_id       uuid;
begin
    select a.day, a.time, a.workflow_id
    into prev_action_day, prev_action_time, workflow_id
    from action a
    where id = prev_action_id;

    insert into action (workflow_id, name, payload, day, time, type, task_type)
    VALUES (workflow_id, 'Action Name', '{}'::json, prev_action_day, prev_action_time - (1 * interval '1 second'),
            'action', new_action_task_type)
    returning id into new_action_id;


    select ac.parent_id into prev_parent_id from "action_child" ac where ac.child_id = prev_action_id;
    insert into action_child (parent_id, child_id, preconditions)
    values (prev_parent_id, new_action_id, null),
           (new_action_id, prev_action_id, null);

    delete from action_child ac where ac.child_id = prev_action_id and ac.parent_id = prev_parent_id;
    return new_action_id;
end;
$$;

drop function if exists create_action_after("action_id" uuid,
                                            "task_type" action_task_type);

create function create_action_after("action_id" uuid,
                                    "task_type" action_task_type) returns uuid
    language plpgsql
as
$$
declare
    prev_action_id       uuid             := action_id;
    prev_action_day      integer;
    prev_action_time     time;
    workflow_id          uuid;
    new_action_id        uuid;
    new_action_task_type action_task_type := task_type;
    prev_child_id        uuid;
begin
    select a.day, a.time, a.workflow_id
    into prev_action_day, prev_action_time, workflow_id
    from action a
    where id = prev_action_id;

    insert into action (workflow_id, name, payload, day, time, type, task_type)
    VALUES (workflow_id, 'Action Name', '{}'::json, prev_action_day,
            prev_action_time + (1 * interval '1 second'), 'action', new_action_task_type)
    returning id into new_action_id;


    select ac.child_id into prev_child_id from "action_child" ac where ac.parent_id = prev_action_id;
    insert into action_child (parent_id, child_id, preconditions)
    values (new_action_id, prev_child_id, null),
           (prev_action_id, new_action_id, null);

    delete from action_child ac where ac.child_id = prev_child_id and ac.parent_id = prev_action_id;
    return new_action_id;
end;
$$;