-- Customer handles are private. This migration grants no administrator access.
begin;
create table if not exists marketplace_private.customer_profiles (
 user_id uuid primary key references auth.users(id) on delete cascade,
 username text not null unique check (username ~ '^[a-z][a-z0-9_]{2,31}$'),
 first_name text not null check (length(first_name)<=80),
 last_name text not null check (length(last_name)<=80),
 created_at timestamptz not null default now()
);
alter table marketplace_private.customer_profiles enable row level security;
revoke all on marketplace_private.customer_profiles from public, anon, authenticated;

create or replace function marketplace_private.create_customer_profile()
returns trigger language plpgsql security definer set search_path='' as $$
declare requested text; fallback_name text;
begin
 if new.phone_confirmed_at is null or new.raw_user_meta_data->>'marketplace_customer' is distinct from 'true' then return new; end if;
 if exists(select 1 from marketplace_private.customer_profiles where user_id=new.id) then return new; end if;
 requested:=lower(trim(new.raw_user_meta_data->>'requested_username'));
 fallback_name:='u'||left(replace(new.id::text,'-',''),31);
 if requested is not null and requested ~ '^[a-z][a-z0-9_]{2,31}$' then
  insert into marketplace_private.customer_profiles(user_id,username,first_name,last_name)
  values(new.id,requested,left(coalesce(new.raw_user_meta_data->>'first_name',''),80),left(coalesce(new.raw_user_meta_data->>'last_name',''),80))
  on conflict do nothing;
 end if;
 if not exists(select 1 from marketplace_private.customer_profiles where user_id=new.id) then
  insert into marketplace_private.customer_profiles(user_id,username,first_name,last_name)
  values(new.id,fallback_name,left(coalesce(new.raw_user_meta_data->>'first_name',''),80),left(coalesce(new.raw_user_meta_data->>'last_name',''),80))
  on conflict(user_id) do nothing;
 end if;
 return new;
end $$;
revoke all on function marketplace_private.create_customer_profile() from public,anon,authenticated;
drop trigger if exists marketplace_customer_profile on auth.users;
create trigger marketplace_customer_profile after insert or update of phone_confirmed_at on auth.users
for each row execute function marketplace_private.create_customer_profile();

create or replace function public.marketplace_my_username()
returns text language sql stable security definer set search_path='' as $$
 select username from marketplace_private.customer_profiles where user_id=auth.uid();
$$;
revoke all on function public.marketplace_my_username() from public,anon;
grant execute on function public.marketplace_my_username() to authenticated;

-- Only the isolated server-side lookup credential can resolve a username.
-- Never expose this RPC to anon or authenticated clients.
create or replace function public.marketplace_resolve_login(login_name text)
returns jsonb language sql stable security definer set search_path='' as $$
 select jsonb_build_object(
  'phone',case when u.phone_confirmed_at is not null then u.phone end,
  'email',case when u.email_confirmed_at is not null then u.email end
 ) from marketplace_private.customer_profiles p join auth.users u on u.id=p.user_id
 where p.username=lower(trim(login_name));
$$;
revoke all on function public.marketplace_resolve_login(text) from public,anon,authenticated;
grant execute on function public.marketplace_resolve_login(text) to service_role;
commit;