do $$
begin
  alter type public.campaign_status add value if not exists 'partial';
exception
  when duplicate_object then null;
end
$$;

do $$
begin
  alter type public.campaign_status add value if not exists 'cancelled';
exception
  when duplicate_object then null;
end
$$;

alter table public.campaigns
  add column if not exists sent_html_body text,
  add column if not exists sent_text_body text,
  add column if not exists sent_subject text,
  add column if not exists sent_from_name text,
  add column if not exists sent_from_email text,
  add column if not exists sent_snapshot_at timestamptz;

do $$
begin
  if exists (
    select 1
    from pg_type
    where typnamespace = 'public'::regnamespace
      and typname = 'editor_mode'
  ) then
    execute 'alter table public.campaigns add column if not exists sent_editor_mode public.editor_mode';
  elsif exists (
    select 1
    from pg_type
    where typnamespace = 'public'::regnamespace
      and typname = 'campaign_editor_mode'
  ) then
    execute 'alter table public.campaigns add column if not exists sent_editor_mode public.campaign_editor_mode';
  else
    execute 'alter table public.campaigns add column if not exists sent_editor_mode text';
  end if;
end
$$;

update public.campaigns
set
  sent_html_body = coalesce(sent_html_body, html_body),
  sent_text_body = coalesce(sent_text_body, text_body),
  sent_subject = coalesce(sent_subject, subject),
  sent_from_name = coalesce(sent_from_name, from_name),
  sent_from_email = coalesce(sent_from_email, from_email),
  sent_editor_mode = coalesce(sent_editor_mode, editor_mode),
  sent_snapshot_at = coalesce(sent_snapshot_at, sent_at, updated_at)
where status::text in ('sent', 'partial')
  and (
    sent_html_body is null
    or sent_text_body is null
    or sent_subject is null
    or sent_from_name is null
    or sent_from_email is null
    or sent_editor_mode is null
    or sent_snapshot_at is null
  );

create index if not exists campaigns_status_updated_idx
  on public.campaigns (organization_id, status, updated_at desc);

create index if not exists campaign_sends_campaign_status_sent_idx
  on public.campaign_sends (campaign_id, status, sent_at desc);