alter table public.campaigns
  add column if not exists last_composed_at timestamptz,
  add column if not exists last_published_at timestamptz,
  add column if not exists last_published_by_auth_user_id uuid references auth.users(id) on delete set null,
  add column if not exists last_published_version_id uuid references public.campaign_versions(id) on delete set null;

update public.campaigns
set last_composed_at = coalesce(last_composed_at, updated_at, created_at, now())
where last_composed_at is null;

update public.campaigns
set
  last_published_at = coalesce(sent_snapshot_at, sent_at, updated_at),
  last_published_by_auth_user_id = coalesce(last_published_by_auth_user_id, updated_by_auth_user_id)
where last_published_at is null
  and status::text in ('queued', 'scheduled', 'sending', 'sent', 'partial', 'cancelled');

create index if not exists campaigns_org_published_idx
  on public.campaigns (organization_id, last_published_at desc nulls last);

create index if not exists campaigns_org_composed_idx
  on public.campaigns (organization_id, last_composed_at desc nulls last);