alter table if exists operational_events
  add column if not exists status text not null default 'open',
  add column if not exists handled_by_account_id uuid references staff_accounts(id) on delete set null,
  add column if not exists handler_note text not null default '',
  add column if not exists handled_at timestamptz,
  add column if not exists updated_at timestamptz not null default now();

alter table if exists operational_events
  drop constraint if exists operational_events_status_check;

alter table if exists operational_events
  add constraint operational_events_status_check
  check (status in ('open', 'acknowledged', 'resolved', 'ignored'));

update operational_events
set status = case when resolved_at is null then status else 'resolved' end,
    updated_at = now();

create index if not exists idx_operational_events_status_created
  on operational_events(status, severity, created_at desc);

create index if not exists idx_operational_events_resource
  on operational_events(resource_type, resource_id, created_at desc);

update staff_roles
set permissions = (
  select array_agg(distinct permission)
  from unnest(permissions || array['operations:read', 'operations:write']::text[]) as permission
),
updated_at = now()
where code = 'operations_admin';

update staff_roles
set permissions = (
  select array_agg(distinct permission)
  from unnest(permissions || array['operations:read']::text[]) as permission
),
updated_at = now()
where code = 'finance_admin';
