---

create table tmp_dfa_status_priority(
  status text,
  priority int
);

insert into tmp_dfa_status_priority(status, priority)
values('preliminary_check', 0),
('modification', 1),
('issuer_confirmation', 2),
('teller_approval', 3),
('spec_approval', 4),
('lawyer_approval', 5),
('waiting_for_collection_started', 6),
('waiting_for_holding', 7),
('collection_started', 8),
('waiting_for_emitment', 9),
('waiting_for_emission', 10),
('emitment_success', 11),
('mature_preparation', 12),
('mature_confirmed', 13),
('waiting_for_mature', 14),
('mature', 15),
('redeemed', 16)
;

create table bak_release_status_history as
  select * from release_status_history;

update release_status_history h
   set dt_start_status = (
         select h.dt_start_status + make_interval(secs => p.priority)
           from tmp_dfa_status_priority p
          where p.status = h.status
       )
 where (h.ticker_symbol, h.dt_start_status) in (
         select ticker_symbol, dt_start_status
           from release_status_history
          group 
             by ticker_symbol, dt_start_status
         having count(*) > 1
       ) 
;

update release_status_history h
   set dt_end_status = d.next_status_start_date
  from (
select ticker_symbol, status, dt_start_status, dt_end_status
     , coalesce(
         lead(dt_start_status) over (partition by ticker_symbol order by dt_start_status)
       , '2025-12-31 23:59:59'::timestamptz
       ) as next_status_start_date
  from release_status_history
  ) d
 where h.ticker_symbol = d.ticker_symbol and h.status = d.status
   and (h.dt_start_status > h.dt_end_status
    or d.next_status_start_date > h.dt_end_status)
;

drop table tmp_dfa_status_priority;