-- Keep raw casing on usernames at signup.
--
-- `profiles.username` is already `citext` so unique + lookup checks are
-- case-insensitive regardless of stored casing. Previously the signup
-- trigger force-lowercased via `lower(trim(...))`, losing the user's
-- preferred display case. Drop the lower(), widen the validation regex to
-- accept A-Z, keep trim() + uniqueness semantics.

create or replace function public.handle_new_user()
returns trigger language plpgsql security definer set search_path = public as $$
declare
  v_invite_code  text;
  v_username     text;
  v_display_name text;
  v_locale       text;
  v_invite       public.invites%rowtype;
begin
  v_invite_code  := new.raw_user_meta_data->>'invite_code';
  v_username     := trim(new.raw_user_meta_data->>'username');
  v_display_name := nullif(trim(new.raw_user_meta_data->>'display_name'), '');
  v_locale       := lower(trim(new.raw_user_meta_data->>'locale'));

  if v_display_name is null then
    v_display_name := v_username;
  end if;
  if v_locale is null or v_locale not in ('en', 'de') then
    v_locale := 'en';
  end if;

  if v_invite_code is null or length(v_invite_code) = 0 then
    raise exception 'ERR_INVITE_CODE_REQUIRED';
  end if;

  if v_username is null or v_username !~ '^[A-Za-z0-9_]{3,32}$' then
    raise exception 'ERR_USERNAME_INVALID';
  end if;

  if not coalesce((select (value)::boolean from public.admin_settings where key = 'invites_enabled'), true) then
    raise exception 'ERR_INVITES_DISABLED';
  end if;

  select * into v_invite from public.invites
   where code = v_invite_code
     for update;

  if not found then
    raise exception 'ERR_INVITE_NOT_FOUND';
  end if;

  if v_invite.disabled then
    raise exception 'ERR_INVITE_DISABLED';
  end if;

  if v_invite.expires_at is not null and v_invite.expires_at < now() then
    raise exception 'ERR_INVITE_EXPIRED';
  end if;

  if v_invite.uses_limit is not null and v_invite.uses_count >= v_invite.uses_limit then
    raise exception 'ERR_INVITE_EXHAUSTED';
  end if;

  update public.invites
     set uses_count = uses_count + 1
   where code = v_invite.code;

  insert into public.profiles (user_id, username, display_name, locale)
  values (new.id, v_username, v_display_name, v_locale);

  return new;
end;
$$;