CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  new_code text;
  ref_code text;
  ref_user uuid;
BEGIN
  new_code := upper(substr(encode(gen_random_bytes(5), 'hex'), 1, 8));
  ref_code := NEW.raw_user_meta_data->>'referral_code';
  IF ref_code IS NOT NULL THEN
    SELECT id INTO ref_user FROM public.profiles WHERE referral_code = upper(ref_code) LIMIT 1;
  END IF;

  INSERT INTO public.profiles (id, email, full_name, phone, referral_code, referred_by)
  VALUES (NEW.id, NEW.email, NEW.raw_user_meta_data->>'full_name', NEW.raw_user_meta_data->>'phone', new_code, ref_user);
  INSERT INTO public.wallets (user_id) VALUES (NEW.id);

  IF NEW.email = 'contactbillsline@gmail.com' THEN
    INSERT INTO public.user_roles (user_id, role) VALUES (NEW.id, 'admin');
  END IF;
  INSERT INTO public.user_roles (user_id, role) VALUES (NEW.id, 'user');

  IF ref_user IS NOT NULL THEN
    INSERT INTO public.referrals (referrer_id, referred_id, bonus_amount) VALUES (ref_user, NEW.id, 100);
  END IF;

  RETURN NEW;
END;
$$;
REVOKE ALL ON FUNCTION public.handle_new_user() FROM PUBLIC, anon, authenticated;