CREATE OR REPLACE FUNCTION public.handle_new_user()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
DECLARE
  jumlah_admin int;
  pilihan text;
  peran_akhir public.app_role;
BEGIN
  INSERT INTO public.profiles (id, nama, email)
  VALUES (
    NEW.id,
    COALESCE(NEW.raw_user_meta_data ->> 'nama', NEW.raw_user_meta_data ->> 'full_name', split_part(NEW.email, '@', 1)),
    NEW.email
  )
  ON CONFLICT (id) DO NOTHING;

  SELECT count(*) INTO jumlah_admin FROM public.user_roles WHERE role = 'admin';
  pilihan := lower(COALESCE(NEW.raw_user_meta_data ->> 'peran', ''));

  IF pilihan = 'admin' OR jumlah_admin = 0 THEN
    peran_akhir := 'admin'::public.app_role;
  ELSE
    peran_akhir := 'user'::public.app_role;
  END IF;

  INSERT INTO public.user_roles (user_id, role)
  VALUES (NEW.id, peran_akhir)
  ON CONFLICT DO NOTHING;

  RETURN NEW;
END;
$function$;