-- copied from WebApp/MPA/Tickets/Development/db_init/tsclosingreason/migrate_duplicate_q


-- add closing reason for all tickets "closed as duplicate"
insert into tsticketxclosingreason (
 tsticketxclosingreason_creator,
 tsticketxclosingreason_author,
 tsticketxclosingreason_tsticket_id,
 tsticketxclosingreason_tsclosingreason_id
) select
  'tsclosingreason migration',
  'tsclosingreason migration',
  tsticket_id,
  tsclosingreason_id
from tsticket
join tsclosingreason
  on tsclosingreason_shortname = 'closed_as_duplicate' -- unique shortname, can be used for referencing
where tsticket_tsticketlc_id = 9
;

-- change all tickets that were "closed as duplicate" to be "closed"
update tsticket
set tsticket_tsticketlc_id = 3
where tsticket_tsticketlc_id = 9
;

-- find all history entries that went to "closed as duplicate"
with data as (
  select
    tsticketlch_id as data_id,
    tsticketlct_from_tsticketlc_id as src_lc
  from tsticketlch
  join tsticketlct
    on tsticketlct_id = tsticketlch_tsticketlct_id
  where tsticketlct_to_tsticketlc_id = 9
)
-- update them to be "closed", but from the same source lifecycle
update tsticketlch set
  tsticketlch_author = 'tsclosingreason migration',
  tsticketlch_tsticketlct_id = (
    select tsticketlct_id
    from tsticketlct
    where tsticketlct_from_tsticketlc_id = data.src_lc
    and tsticketlct_to_tsticketlc_id = 3
  )
from data
where data_id = tsticketlch_id
;

-- update all history entries that returned "closed as duplicate" to dispatch
update tsticketlch set
  tsticketlch_author = 'tsclosingreason migration',
  tsticketlch_tsticketlct_id = (
    select tsticketlct_id
    from tsticketlct
    where tsticketlct_from_tsticketlc_id = 3
    and tsticketlct_to_tsticketlc_id = 1 -- dispatch, this magic number is constant on all systems
  )
where tsticketlch_tsticketlct_id = (
  select tsticketlct_id
  from tsticketlct
  where tsticketlct_from_tsticketlc_id = 9
);

-- remove lifecycle transitions leading to "closed as duplicate"
delete from tsticketlct
where tsticketlct_to_tsticketlc_id = 9
;

-- remove lifecycle "closed as duplicate"
delete from tsticketlc
where tsticketlc_id = 9
;

