begin;

alter table public.profiles
  add column if not exists login_username text,
  add column if not exists must_change_password boolean not null default false;

update public.profiles
set login_username = case
  when phone ~ '^\+249[0-9]{9}$' then '0' || substring(phone from 5)
  when phone ~ '^\+966[0-9]{9}$' then '0' || substring(phone from 5)
  when phone ~ '^\+971[0-9]{9}$' then '0' || substring(phone from 5)
  when phone ~ '^\+968[0-9]{8}$' then '0' || substring(phone from 5)
  else null
end
where role = 'donor'
  and login_username is null;

do $$
begin
  if exists (
    select 1
    from public.profiles
    where login_username is not null
    group by login_username
    having count(*) > 1
  ) then
    raise exception 'Duplicate sponsor login usernames must be resolved before applying this migration';
  end if;
end;
$$;

alter table public.profiles
  drop constraint if exists profiles_login_username_format;
alter table public.profiles
  add constraint profiles_login_username_format
  check (login_username is null or login_username ~ '^0[0-9]{8,9}$');

create unique index if not exists profiles_login_username_unique
  on public.profiles(login_username)
  where login_username is not null;

create or replace function private.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
  profile_role public.app_role;
  raw_phone text;
  normalized_phone text;
  phone_digits text;
  derived_login_username text;
  requested_login_username text;
  force_password_change boolean;
begin
  profile_role := case
    when new.raw_user_meta_data ->> 'initial_role' = 'admin' then 'admin'::public.app_role
    else 'donor'::public.app_role
  end;

  raw_phone := coalesce(
    nullif(trim(new.raw_user_meta_data ->> 'phone'), ''),
    nullif(trim(new.phone), '')
  );

  if raw_phone is not null then
    phone_digits := regexp_replace(raw_phone, '[^0-9]', '', 'g');
    normalized_phone := case
      when raw_phone like '+%' then '+' || phone_digits
      when phone_digits like '00%' then '+' || substring(phone_digits from 3)
      when phone_digits like '0%' then '+249' || substring(phone_digits from 2)
      else '+' || phone_digits
    end;

    derived_login_username := case
      when normalized_phone ~ '^\+249[0-9]{9}$' then '0' || substring(normalized_phone from 5)
      when normalized_phone ~ '^\+966[0-9]{9}$' then '0' || substring(normalized_phone from 5)
      when normalized_phone ~ '^\+971[0-9]{9}$' then '0' || substring(normalized_phone from 5)
      when normalized_phone ~ '^\+968[0-9]{8}$' then '0' || substring(normalized_phone from 5)
      else null
    end;
  end if;

  requested_login_username := nullif(trim(new.raw_user_meta_data ->> 'login_username'), '');
  if requested_login_username is not null
    and requested_login_username is distinct from derived_login_username then
    raise exception 'Sponsor login username does not match the Auth phone';
  end if;

  force_password_change := profile_role = 'donor'
    and coalesce(new.raw_user_meta_data ->> 'must_change_password', 'false') = 'true';

  insert into public.profiles (
    id,
    role,
    full_name,
    phone,
    login_username,
    must_change_password
  )
  values (
    new.id,
    profile_role,
    coalesce(nullif(trim(new.raw_user_meta_data ->> 'full_name'), ''), 'مستخدم جديد'),
    normalized_phone,
    derived_login_username,
    force_password_change
  )
  on conflict (id) do nothing;
  return new;
end;
$$;

notify pgrst, 'reload schema';

commit;