-- Script to fix the seqnumpath of both technical and logical navigation after
-- a merge of two navigation sets. Disable if not needed.
-- TODO: Move into a database function

alter table appnavcustom disable trigger appnavcustom_upd;

with recursive nodes as (
  select
    appnavcache_id as id,
    array[]::bigint[] as path
  from appnavcache
  where appnavcache_path = ''

  union select all
  
    child.appnavcache_id,
    path || array[
      row_number() over (
        partition by parent.id
        order by child.appnavcache_seqnum, child.appnavcache_id
      ) - 1
    ]::bigint[]
  from appnavcache as child
  join nodes as parent
    on parent.id = child.appnavcache_parent_appnavcache_id
)
update appnavcache set
  appnavcache_author = 'fix tree',
  appnavcache_modtime = now(),
  appnavcache_seqnumpath = path
from nodes
where id = appnavcache_id
  and path != appnavcache_seqnumpath;

with recursive nodes as (
  select
    appnavcustom_id as id,
    array[]::bigint[] as path
  from appnavcustom
  join appnavcache
    on appnavcache_id = appnavcustom_appnavcache_id
  where appnavcache_path = ''

  union select all
  
    child.appnavcustom_id,
    path || array[
      row_number() over (
        partition by parent.id
        order by child.appnavcustom_seqnum, child.appnavcustom_id
      ) - 1
    ]::bigint[]
  from appnavcustom as child
  join nodes as parent
    on parent.id = child.appnavcustom_parent_appnavcustom_id
)
update appnavcustom set
  appnavcustom_author = 'fix tree',
  appnavcustom_modtime = now(),
  appnavcustom_seqnumpath = path
from nodes
where id = appnavcustom_id
  and path != appnavcustom_seqnumpath;

alter table appnavcustom enable trigger appnavcustom_upd;
