alter table public.contacts
  add column if not exists last_status_changed_at timestamptz,
  add column if not exists status_source text,
  add column if not exists status_reason text,
  add column if not exists unsubscribed_at timestamptz,
  add column if not exists unsubscribed_via text,
  add column if not exists unsubscribed_campaign_id uuid references public.campaigns(id) on delete set null,
  add column if not exists unsubscribed_campaign_send_id uuid references public.campaign_sends(id) on delete set null,
  add column if not exists bounced_at timestamptz,
  add column if not exists complained_at timestamptz;

create table if not exists public.contact_unsubscribe_tokens (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  contact_id uuid not null references public.contacts(id) on delete cascade,
  campaign_id uuid references public.campaigns(id) on delete set null,
  campaign_send_id uuid references public.campaign_sends(id) on delete set null,
  token text not null unique,
  recipient_email text,
  used_at timestamptz,
  used_via text,
  request_metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index if not exists contacts_status_changed_idx
  on public.contacts (organization_id, last_status_changed_at desc nulls last);

create index if not exists contacts_unsubscribed_idx
  on public.contacts (organization_id, unsubscribed_at desc nulls last);

create index if not exists contacts_bounced_idx
  on public.contacts (organization_id, bounced_at desc nulls last);

create index if not exists contacts_complained_idx
  on public.contacts (organization_id, complained_at desc nulls last);

create index if not exists contact_unsubscribe_tokens_contact_idx
  on public.contact_unsubscribe_tokens (contact_id, created_at desc);

create index if not exists contact_unsubscribe_tokens_campaign_send_idx
  on public.contact_unsubscribe_tokens (campaign_send_id);

update public.contacts
set
  last_status_changed_at = coalesce(last_status_changed_at, updated_at, created_at),
  unsubscribed_at = case
    when status = 'unsubscribed'::public.contact_status then coalesce(unsubscribed_at, updated_at, created_at)
    else unsubscribed_at
  end,
  bounced_at = case
    when status = 'bounced'::public.contact_status then coalesce(bounced_at, updated_at, created_at)
    else bounced_at
  end,
  complained_at = case
    when status = 'complained'::public.contact_status then coalesce(complained_at, updated_at, created_at)
    else complained_at
  end,
  status_source = case
    when status_source is not null then status_source
    when status = 'unsubscribed'::public.contact_status then 'legacy_status'
    when status = 'bounced'::public.contact_status then 'legacy_status'
    when status = 'complained'::public.contact_status then 'legacy_status'
    else status_source
  end
where
  last_status_changed_at is null
  or (status = 'unsubscribed'::public.contact_status and unsubscribed_at is null)
  or (status = 'bounced'::public.contact_status and bounced_at is null)
  or (status = 'complained'::public.contact_status and complained_at is null)
  or status_source is null;

alter table public.contact_unsubscribe_tokens enable row level security;