-- Approved MOTIE transitions storage
create table if not exists public.motie_org_transitions (
    id bigserial primary key,
    person_key text not null,
    before_person_key text,
    name text,
    latest_valid_from timestamptz not null,
    department_before text,
    position_before text,
    phone_before text,
    task_before text,
    department_after text,
    position_after text,
    phone_after text,
    task_after text,
    run_id uuid,
    event_type text,
    approved_by text,
    approved_at timestamptz default now()
);

create unique index if not exists motie_org_transitions_person_ts_idx
    on public.motie_org_transitions (person_key, latest_valid_from);

create index if not exists motie_org_transitions_before_idx
    on public.motie_org_transitions (before_person_key);

create index if not exists motie_org_transitions_name_idx
    on public.motie_org_transitions (name);
