alter table public.campaigns
  add column if not exists created_by_auth_user_id uuid references auth.users(id) on delete set null,
  add column if not exists updated_by_auth_user_id uuid references auth.users(id) on delete set null,
  add column if not exists last_test_sent_at timestamptz,
  add column if not exists last_test_sent_by_auth_user_id uuid references auth.users(id) on delete set null,
  add column if not exists last_delivery_requested_at timestamptz,
  add column if not exists last_delivery_requested_by_auth_user_id uuid references auth.users(id) on delete set null,
  add column if not exists last_delivery_action text,
  add column if not exists last_activity_at timestamptz;

alter table public.contacts
  add column if not exists created_by_auth_user_id uuid references auth.users(id) on delete set null,
  add column if not exists updated_by_auth_user_id uuid references auth.users(id) on delete set null,
  add column if not exists last_import_job_id uuid references public.contact_import_jobs(id) on delete set null,
  add column if not exists last_imported_at timestamptz,
  add column if not exists last_imported_by_auth_user_id uuid references auth.users(id) on delete set null,
  add column if not exists last_activity_at timestamptz;

alter table public.contact_import_jobs
  add column if not exists created_by_auth_user_id uuid references auth.users(id) on delete set null,
  add column if not exists completed_by_auth_user_id uuid references auth.users(id) on delete set null,
  add column if not exists total_rows integer not null default 0;

alter table public.campaign_sends
  add column if not exists initiated_by_auth_user_id uuid references auth.users(id) on delete set null,
  add column if not exists contact_email text,
  add column if not exists contact_full_name text,
  add column if not exists contact_tags jsonb not null default '[]'::jsonb;

alter table public.email_events
  add column if not exists contact_id uuid references public.contacts(id) on delete set null,
  add column if not exists event_source text not null default 'provider_webhook';

create table if not exists public.campaign_activity_logs (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  campaign_id uuid,
  campaign_send_id uuid,
  actor_auth_user_id uuid references auth.users(id) on delete set null,
  action text not null,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table if not exists public.contact_activity_logs (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  contact_id uuid,
  import_job_id uuid,
  actor_auth_user_id uuid references auth.users(id) on delete set null,
  action text not null,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index if not exists campaigns_creator_idx
  on public.campaigns (organization_id, created_by_auth_user_id);

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

create index if not exists contacts_creator_idx
  on public.contacts (organization_id, created_by_auth_user_id);

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

create index if not exists campaign_sends_initiated_by_idx
  on public.campaign_sends (organization_id, initiated_by_auth_user_id);

create index if not exists email_events_contact_idx
  on public.email_events (organization_id, contact_id, created_at desc);

create index if not exists campaign_activity_logs_org_created_idx
  on public.campaign_activity_logs (organization_id, created_at desc);

create index if not exists campaign_activity_logs_campaign_created_idx
  on public.campaign_activity_logs (campaign_id, created_at desc);

create index if not exists contact_activity_logs_org_created_idx
  on public.contact_activity_logs (organization_id, created_at desc);

create index if not exists contact_activity_logs_contact_created_idx
  on public.contact_activity_logs (contact_id, created_at desc);

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

update public.contacts
set last_activity_at = coalesce(last_activity_at, updated_at, created_at)
where last_activity_at is null;

alter table public.campaign_activity_logs enable row level security;
alter table public.contact_activity_logs enable row level security;