-- Unify events: drop legacy v1/v2 and set v3 as canonical name 'motie_org_events'

-- 0) Drop legacy views if exist to avoid dependency conflicts
drop view if exists public.motie_org_events_v2 cascade;
drop view if exists public.motie_org_events cascade;

-- 1) Recreate 'motie_org_events' with v3 definition (run-aware moved pairing)
create view public.motie_org_events as
with base as (
  select h.key_hash, h.name, h.department, h."position", h.phone, h.task,
         h.valid_from, h.valid_to, h.open_run_id, h.close_run_id
  from public.motie_org_hist h
),
closed as (
  select * from base where valid_to is not null
),
added as (
  select * from base where valid_to is null
),
pairs as (
  select
    c.key_hash   as key_hash_before,
    a.key_hash   as key_hash_after,
    c.name,
    c.department as dep_before,
    a.department as dep_after,
    c."position" as pos_before,
    a."position" as pos_after,
    c.phone      as phone_before,
    a.phone      as phone_after,
    coalesce(a.task, c.task) as task_after,
    a.valid_from as valid_from,
    c.valid_to   as valid_to,
    a.open_run_id  as run_after,
    c.close_run_id as run_before,
    (a.open_run_id is not null and a.open_run_id = c.close_run_id) as same_run,
    abs(extract(epoch from (a.valid_from - c.valid_to)))::integer as pair_delta_sec,
    array_remove(array[
      case when a.department is distinct from c.department then 'department' end,
      case when a."position" is distinct from c."position" then 'position' end,
      case when a.phone      is distinct from c.phone      then 'phone' end,
      case when a.task       is distinct from c.task       then 'task' end
    ], null) as changed_fields
  from closed c
  join lateral (
    select a.*
    from added a
    where a.name = c.name
      and (
        (a.open_run_id is not null and a.open_run_id = c.close_run_id)
        or (a.valid_from between c.valid_to - interval '3 minutes' and c.valid_to + interval '3 minutes')
      )
    order by (a.open_run_id is not null and a.open_run_id = c.close_run_id) desc,
             abs(extract(epoch from (a.valid_from - c.valid_to))) asc
    limit 1
  ) a on true
),
moved as (
  select
    coalesce(p.key_hash_after, p.key_hash_before) as key_hash,
    p.name,
    p.dep_after as department,
    p.pos_after as "position",
    p.phone_after as phone,
    p.task_after as task,
    p.valid_from, p.valid_to,
    p.changed_fields,
    'updated'::text as event_type,
    exists (
      select 1
      from public.motie_org_hist h
      where h.key_hash = coalesce(p.key_hash_after, p.key_hash_before)
        and h.valid_to is null
    ) as is_current,
    p.run_after as run_id,
    p.dep_before, p.pos_before, p.phone_before,
    p.same_run, p.pair_delta_sec
  from pairs p
  where coalesce(array_length(p.changed_fields, 1), 0) > 0
),
closed_only as (
  select
    c.key_hash, c.name, c.department, c."position", c.phone, c.task,
    c.valid_from, c.valid_to,
    array[]::text[] as changed_fields,
    'closed'::text as event_type,
    false as is_current,
    c.close_run_id as run_id,
    c.department as dep_before, c."position" as pos_before, c.phone as phone_before,
    false as same_run, null::integer as pair_delta_sec
  from closed c
  where not exists (select 1 from pairs p where p.key_hash_before = c.key_hash)
),
added_only as (
  select
    a.key_hash, a.name, a.department, a."position", a.phone, a.task,
    a.valid_from, a.valid_to,
    array['department','position','phone','task']::text[] as changed_fields,
    'added'::text as event_type,
    true as is_current,
    a.open_run_id as run_id,
    null::text as dep_before, null::text as pos_before, null::text as phone_before,
    false as same_run, null::integer as pair_delta_sec
  from added a
  where not exists (select 1 from pairs p where p.key_hash_after = a.key_hash)
)
select * from moved
union all select * from closed_only
union all select * from added_only;

-- 2) Drop the temporary v3 view if it still exists (cleanup)
drop view if exists public.motie_org_events_v3 cascade;
