-- Zero-downtime rename example:
-- Move public.profiles.name to public.profiles.display_name without breaking
-- old Vercel functions that may still read or write the old column.
--
-- Run these sections as separate migrations/releases. Do not execute this
-- whole file as one production migration.

-- ============================================================================
-- 001_expand_profiles_display_name.sql
-- ============================================================================

set lock_timeout = '5s';
set statement_timeout = '2min';

alter table public.profiles
  add column if not exists display_name text;

comment on column public.profiles.display_name is
  'New profile display name. During rollout, app code dual-writes name and display_name.';

-- Deploy app code after this migration:
-- - writes both name and display_name
-- - reads display_name ?? name

-- ============================================================================
-- 002_backfill_profiles_display_name_function.sql
-- ============================================================================

set lock_timeout = '5s';
set statement_timeout = '2min';

create or replace function public.backfill_profile_display_names(batch_size integer default 1000)
returns integer
language plpgsql
security definer
set search_path = public
as $$
declare
  updated_count integer;
begin
  with batch as (
    select id
    from public.profiles
    where display_name is null
      and name is not null
    order by id
    limit batch_size
    for update skip locked
  )
  update public.profiles p
  set
    display_name = p.name,
    updated_at = coalesce(p.updated_at, now())
  from batch
  where p.id = batch.id;

  get diagnostics updated_count = row_count;
  return updated_count;
end;
$$;

comment on function public.backfill_profile_display_names(integer) is
  'Backfills profiles.display_name from profiles.name in small, retryable batches.';

-- Run repeatedly from a script or SQL editor until this returns 0:
-- select public.backfill_profile_display_names(1000);

-- ============================================================================
-- 003_validate_profiles_display_name.sql
-- ============================================================================

set lock_timeout = '5s';
set statement_timeout = '10min';

-- Check before validating.
select count(*) as missing_display_names
from public.profiles
where display_name is null;

alter table public.profiles
  add constraint profiles_display_name_present
  check (display_name is not null)
  not valid;

alter table public.profiles
  validate constraint profiles_display_name_present;

-- Deploy app code after this migration:
-- - reads display_name only
-- - still writes name for one release if old code may exist

-- ============================================================================
-- 004_contract_profiles_name.sql
-- ============================================================================

set lock_timeout = '5s';
set statement_timeout = '2min';

-- Only run this after old app code is gone and metrics confirm display_name is
-- populated for all live writes.

alter table public.profiles
  drop column if exists name;

drop function if exists public.backfill_profile_display_names(integer);
