begin;

-- PostgreSQL CHECK constraints accept NULL unless it is rejected explicitly.
-- Every sponsor must have one E.164 phone, while email-authenticated admins may
-- continue to have no phone.
alter table public.profiles
  drop constraint if exists profiles_sponsor_phone_required;
alter table public.profiles
  add constraint profiles_sponsor_phone_required
  check (
    role = 'admin'
    or (
      phone is not null
      and phone ~ '^\+[1-9][0-9]{7,14}$'
    )
  );

-- GoTrue may expose new.phone without the leading plus while inserting the
-- Auth row. Normalize the trusted metadata/new.phone candidate before creating
-- the public profile so the Auth and profile transaction succeeds atomically.
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;
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;
  end if;

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

commit;