create or replace function recalculate_duration_for(timeline_item_id integer, group_hash varchar,
                                                    new_duration     int4range,
                                                    old_duration     int4range default null)
  returns jsonb as $$
declare timeline_item          record;
        shifted_timeline_items jsonb default '[]';
        timeline_item_length   integer;
        project                projects;
        overlapped_data        record;
        updated_model          timeline_items;
        initital_duration      int4range;
        recalculated_duration  int4range;
        new_lower_bound        integer;
        new_upper_bound        integer;
  overlapped_data_middle     integer;
  overlapped_rec_lower_bound integer;
  undo_redo_data             record;
  updated_durations          jsonb default '{}';
  split_zone                 int4range;
  split_diff                 integer;
begin
  if old_duration notnull and new_duration = old_duration
  then
    select _can_undo, _can_redo
        into undo_redo_data
    from get_data_for_redo_or_undo(project.id, project.locked_by_user_id);

    return jsonb_build_object('shifted_timeline_items', shifted_timeline_items,
                              'can_undo', undo_redo_data._can_undo, 'can_redo', undo_redo_data._can_redo);
  end if;

  select timeline_items.*,
         timelines.parent_id,
         timelines.initiate_event_id,
         external_id,
         type,
         coalesce((events.metadata -> 'editMode' ->> 'split') :: boolean, false) as is_split,
         events.metadata ->> 'type' = 'end_split'                                as is_end_split
      into timeline_item
  from timeline_items
         inner join timelines on timelines.id = timeline_items.timeline_id
         left join events on timeline_items.external_id = events.id and timeline_items.type = 'event'
  where timeline_items.id = timeline_item_id;

  if timeline_item.id isnull
  then
    raise exception 'Timeline item not found';
  end if;

  updated_durations := updated_durations ||
                       jsonb_build_object(case when timeline_item.type in ('image', 'video')
                         then 'media_'
                                          else 'event_'
                                          end || timeline_item.timeline_id, new_duration);

  if timeline_item.is_split
  then
    updated_durations := updated_durations ||
                         jsonb_build_object('split_' || timeline_item.external_id, new_duration);
    split_diff := lower(new_duration) - lower(old_duration);
  elseif timeline_item.is_end_split
    then
      updated_durations := updated_durations ||
                           jsonb_build_object('end_split_' || timeline_item.timeline_id, new_duration) ||
                           jsonb_build_object('media_' || timeline_item.parent_id, new_duration) ||
                           jsonb_build_object('event_' || timeline_item.parent_id, new_duration);
  end if;

  initital_duration := case when timeline_item.is_end_split then old_duration
                       else new_duration end;
  new_lower_bound := lower(new_duration);
  new_upper_bound := upper(new_duration);
  timeline_item_length := new_upper_bound - new_lower_bound;
  split_zone := get_closer_split_zone(new_duration);

  select *
      into project
  from projects
         inner join timelines on projects.id = timelines.project_id
         inner join timeline_items on timelines.id = timeline_items.timeline_id
                                        and timeline_items.id = timeline_item_id;

  select timeline_items.id,
         type,
         external_id,
         parent_id,
         initiate_event_id,
         timeline_id,
         duration,
         lower(duration) as lower_bound,
         upper(duration) as upper_bound
      into overlapped_data
  from timeline_items
         inner join timelines on timeline_items.timeline_id = timelines.id
  where new_duration && timeline_items.duration
    and timeline_items.id != timeline_item.id
    and (timeline_item.timeline_id = timeline_items.timeline_id or
         (timeline_item.parent_id isnull and timelines.parent_id notnull))
    and not timeline_items.deleted
    and timelines.project_id = project.id
    and (timeline_items.type = timeline_item.type
           or case
                when timeline_item.type in ('image', 'video')
                        then timeline_items.type in ('image', 'video') end)
  order by duration;

  -- start set first element
  if (overlapped_data.id notnull)
  then
    overlapped_data_middle := (overlapped_data.lower_bound + overlapped_data.upper_bound) / 2;

    if overlapped_data.timeline_id = timeline_item.timeline_id
    then
      if overlapped_data.duration @> (new_lower_bound + new_upper_bound) / 2
      then
        recalculated_duration := case
                                 when new_lower_bound < overlapped_data_middle
                                   then int4range(overlapped_data.lower_bound,
                                                  overlapped_data.lower_bound + timeline_item_length)
                                 else int4range(overlapped_data.upper_bound,
                                                overlapped_data.upper_bound + timeline_item_length)
                                 end;
      elseif overlapped_data.lower_bound < new_lower_bound
        then
          recalculated_duration := int4range(overlapped_data.upper_bound,
                                             overlapped_data.upper_bound + timeline_item_length);
      elseif overlapped_data.lower_bound > new_lower_bound
        then
          overlapped_rec_lower_bound := overlapped_data.lower_bound - timeline_item_length;
          overlapped_rec_lower_bound := case
                                        when overlapped_rec_lower_bound < 0
                                          then 0
                                        else overlapped_rec_lower_bound
                                        end;
          recalculated_duration := int4range(overlapped_rec_lower_bound,
                                             overlapped_rec_lower_bound + timeline_item_length);
      end if;
    end if;
  end if;

  if timeline_item.parent_id isnull and new_duration && split_zone
  then
    overlapped_data_middle := (lower(split_zone) + upper(split_zone)) / 2;
    recalculated_duration := case
                             when (new_lower_bound + new_upper_bound) / 2 < overlapped_data_middle
                               then int4range(lower(split_zone) - timeline_item_length,
                                              lower(split_zone))
                             else int4range(upper(split_zone),
                                            upper(split_zone) + timeline_item_length)
                             end;
  end if;

  -- process split
  if timeline_item.parent_id notnull
  then
    with split_lower_bound as (select upper(duration)
                               from timeline_items
                                      inner join events on events.id = timeline_items.external_id
                               where timeline_items.type = 'event'
                                 and events.id = timeline_item.initiate_event_id),
         split_upper_bound as (select max(upper(duration))
                               from timelines
                                      inner join timeline_items
                                        on timelines.id = timeline_items.timeline_id and timelines.deleted is false
                                      inner join events
                                        on events.id = timeline_items.external_id and timeline_items.type = 'event'
                               where timelines.deleted is false
                                 and (timeline_items.duration > timeline_item.duration or
                                      timeline_items.duration && timeline_item.duration)
                                 and events.metadata ->> 'type' = 'end_split')
    select int4range(split_lower_bound.upper, split_upper_bound.max)
        into split_zone
    from split_lower_bound,
         split_upper_bound;

    if split_zone notnull and not split_zone @> new_lower_bound
    then
      recalculated_duration := int4range(lower(split_zone), lower(split_zone) + timeline_item_length);
    end if;
  end if;
  -- end process split

  if recalculated_duration notnull
  then
    update timeline_items
    set duration = recalculated_duration
    where id = timeline_item.id
        returning *
          into updated_model;

    insert into project_histories (table_name,
                                   action,
                                   user_id,
                                   project_id,
                                   new_state,
                                   old_state,
                                   group_hash,
                                   created_at,
                                   updated_at)
    values ('timeline_items',
            'UPDATE',
            project.locked_by_user_id,
            project.id,
            row_to_json(updated_model) :: jsonb,
            jsonb_build_object('id', timeline_item.id, 'timeline_id', timeline_item.timeline_id,
                               'type', timeline_item.type, 'external_id', timeline_item.external_id,
                               'duration', timeline_item.duration, 'created_at', timeline_item.created_at,
                               'updated_at', timeline_item.updated_at, 'deleted', timeline_item.deleted),
            group_hash,
            current_timestamp + (1 * interval '1 second'),
            current_timestamp + (1 * interval '1 second'));

    updated_durations := updated_durations ||
                         jsonb_build_object(case when timeline_item.type in ('image', 'video')
                           then 'media_'
                                            else 'event_'
                                            end || timeline_item.timeline_id,
                                            updated_model.duration);

    if timeline_item.is_split
    then
      updated_durations := updated_durations ||
                           jsonb_build_object('split_' || timeline_item.external_id, updated_model.duration);
      split_diff := lower(updated_model.duration) - lower(old_duration);
    elseif timeline_item.is_end_split
      then
        updated_durations := updated_durations ||
                             jsonb_build_object('end_split_' || timeline_item.timeline_id, updated_model.duration) ||
                             jsonb_build_object('media_' || timeline_item.parent_id, updated_model.duration) ||
                             jsonb_build_object('event_' || timeline_item.parent_id, updated_model.duration);
    end if;

    new_duration := updated_model.duration;
  end if;
  -- end set first element

  select timeline_items.*,
         convert_duration(timeline_items.duration)                                     as duration,
         coalesce(row_to_json(images.*), row_to_json(videos.*), row_to_json(events.*)) as item
      into timeline_item
  from timeline_items
         left join images on timeline_items.external_id = images.id and timeline_items.type = 'image'
         left join videos on timeline_items.external_id = videos.id and timeline_items.type = 'video'
         left join events on timeline_items.external_id = events.id and timeline_items.type = 'event'
  where timeline_items.id = timeline_item.id;

  shifted_timeline_items := shifted_timeline_items || row_to_json(timeline_item) :: jsonb;

  raise warning 'ID: %, DURATION: %, DURATIONS: %', timeline_item.id, timeline_item.duration, updated_durations;

  for timeline_item in
  select timeline_items.*,
         timelines.parent_id,
         timelines.initiate_event_id,
         external_id,
         type,
         (events.metadata -> 'editMode' ->> 'split') :: boolean as is_split,
         events.metadata ->> 'type' = 'end_split'               as is_end_split
  from timeline_items
         inner join timelines on timeline_items.timeline_id = timelines.id
         left join events on timeline_items.external_id = events.id and timeline_items.type = 'event'
  where (timeline_items.duration && (updated_durations ->>
                                     (case when timelines.initiate_event_id notnull
                                            then 'split_' || timelines.initiate_event_id
                                           when timeline_items.type in ('video', 'image')
                                            then 'media_' || timelines.id
                                           else 'event_' || timelines.id end
                                         ) :: varchar) :: int4range
           or timeline_items.duration > initital_duration
           or timeline_items.duration && new_duration
           or (timeline_item.type = 'event' and timeline_item.external_id = timelines.initiate_event_id))
    and timeline_items.id != timeline_item_id
    and not timeline_items.deleted
    and timelines.project_id = project.id
  order by duration
  loop

    if timeline_item.id isnull
    then
      exit;
    end if;

    timeline_item_length := upper(timeline_item.duration) - lower(timeline_item.duration);
    new_duration := (updated_durations ->> (case when timeline_item.type in ('video', 'image')
      then 'media_'
                                            else 'event_'
                                            end || timeline_item.timeline_id) :: varchar) :: int4range;

    if timeline_item.parent_id notnull and new_duration isnull
    then
      new_duration := (updated_durations ->>
                       ('split_' || timeline_item.initiate_event_id) :: varchar) :: int4range;
    end if;
    split_zone := get_closer_split_zone(new_duration);
    if (split_diff < 0 and split_zone notnull) then
      split_zone := int4range(lower(split_zone), upper(split_zone) + split_diff);
    end if;

    raise warning '%, %', new_duration, split_zone;

    if new_duration notnull
    then
      update timeline_items
      set duration = case
                       when timeline_item.parent_id notnull and split_diff notnull
                               then int4range(
                                      lower(timeline_item.duration) + split_diff,
                                      upper(timeline_item.duration) + split_diff)
                       when upper(new_duration) + timeline_item_length <@ split_zone
                            and timeline_item.parent_id isnull
                            and lower(timeline_item.duration) - upper(new_duration) < 0
                               then int4range(
                                  upper(split_zone),
                                  upper(split_zone) + timeline_item_length)
                       when lower(timeline_item.duration) - upper(new_duration) < 0
                               then int4range(
                                      upper(new_duration),
                                      upper(new_duration) + timeline_item_length)
                       else timeline_item.duration
          end
      where id = timeline_item.id
          returning *
            into updated_model;

      updated_durations := updated_durations ||
                           jsonb_build_object(case when timeline_item.type in ('image', 'video')
                             then 'media_'
                                              else 'event_'
                                              end || timeline_item.timeline_id,
                                              updated_model.duration);

      if timeline_item.is_split
      then
        updated_durations := updated_durations ||
                             jsonb_build_object('split_' || timeline_item.external_id, updated_model.duration);
        split_diff := lower(updated_model.duration) - lower(timeline_item.duration);
      elseif timeline_item.is_end_split
        then
          updated_durations := updated_durations ||
                               jsonb_build_object('end_split_' || timeline_item.timeline_id, updated_model.duration) ||
                               jsonb_build_object('media_' || timeline_item.parent_id, updated_model.duration) ||
                               jsonb_build_object('event_' || timeline_item.parent_id, updated_model.duration);
      end if;

      insert into project_histories (table_name,
                                     action,
                                     user_id,
                                     project_id,
                                     new_state,
                                     old_state,
                                     group_hash,
                                     created_at,
                                     updated_at)
      values ('timeline_items',
              'UPDATE',
              project.locked_by_user_id,
              project.id,
              row_to_json(updated_model) :: jsonb,
              jsonb_build_object('id', timeline_item.id, 'timeline_id', timeline_item.timeline_id,
                                 'type', timeline_item.type, 'external_id', timeline_item.external_id,
                                 'duration', timeline_item.duration, 'created_at', timeline_item.created_at,
                                 'updated_at', timeline_item.updated_at, 'deleted', timeline_item.deleted),
              group_hash,
              current_timestamp + (1 * interval '1 second'),
              current_timestamp + (1 * interval '1 second'));
    end if;

    select timeline_items.*,
           convert_duration(timeline_items.duration)                                     as duration,
           coalesce(row_to_json(images.*), row_to_json(videos.*), row_to_json(events.*)) as item
        into timeline_item
    from timeline_items
           left join images on timeline_items.external_id = images.id and timeline_items.type = 'image'
           left join videos on timeline_items.external_id = videos.id and timeline_items.type = 'video'
           left join events on timeline_items.external_id = events.id and timeline_items.type = 'event'
    where timeline_items.id = timeline_item.id;

    shifted_timeline_items := shifted_timeline_items || row_to_json(timeline_item) :: jsonb;

    raise warning 'ID: %, DURATION: %, DURATIONS: %', timeline_item.id, timeline_item.duration, updated_durations;
  end loop;

--   raise exception '1';

  select _can_undo, _can_redo
      into undo_redo_data
  from get_data_for_redo_or_undo(project.id, project.locked_by_user_id);

  return jsonb_build_object('shifted_timeline_items', shifted_timeline_items,
                            'can_undo', undo_redo_data._can_undo, 'can_redo', undo_redo_data._can_redo);
end;
$$
language plpgsql;