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

create unique index if not exists profiles_recovery_email_unique_idx
  on public.profiles (lower(recovery_email))
  where recovery_email is not null and btrim(recovery_email) <> '';

create or replace function public.set_own_recovery_email(recovery_email_input text)
returns void
language plpgsql
security definer
set search_path = public
as $$
begin
  update public.profiles
  set recovery_email = lower(btrim(recovery_email_input))
  where id = auth.uid();

  if not found then
    raise exception 'profile not found';
  end if;
end;
$$;

revoke all on function public.set_own_recovery_email(text) from public;
grant execute on function public.set_own_recovery_email(text) to authenticated;
