ALTER TABLE public.profiles ADD COLUMN IF NOT EXISTS referral_code TEXT NOT NULL DEFAULT 'FF90';

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $function$
BEGIN
  INSERT INTO public.profiles (id, display_name, referral_code, account_code)
  VALUES (
    NEW.id,
    COALESCE(NEW.raw_user_meta_data->>'full_name', NEW.raw_user_meta_data->>'display_name', NULLIF(split_part(COALESCE(NEW.email, ''), '@', 1), '')),
    COALESCE(NULLIF(NEW.raw_user_meta_data->>'referral_code', ''), 'FF90'),
    public.gen_account_code()
  )
  ON CONFLICT (id) DO NOTHING;
  RETURN NEW;
END;
$function$;