ALTER TABLE public.profiles
  ADD COLUMN IF NOT EXISTS first_name text NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS middle_name text NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS last_name text NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS gender text NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS phone text NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS address text NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS country text NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS country_code text NOT NULL DEFAULT '';

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  base_username TEXT;
  final_username TEXT;
  n INT := 0;
  meta jsonb := coalesce(NEW.raw_user_meta_data, '{}'::jsonb);
  full_name TEXT;
BEGIN
  base_username := lower(regexp_replace(coalesce(meta->>'username', split_part(NEW.email, '@', 1), 'user'), '[^a-z0-9_]', '', 'g'));
  IF base_username = '' THEN base_username := 'user'; END IF;
  final_username := base_username;
  WHILE EXISTS (SELECT 1 FROM public.profiles WHERE username = final_username) LOOP
    n := n + 1;
    final_username := base_username || n::text;
  END LOOP;

  full_name := btrim(coalesce(meta->>'name', meta->>'full_name',
    btrim(concat_ws(' ', meta->>'first_name', meta->>'middle_name', meta->>'last_name'))));
  IF full_name = '' OR full_name IS NULL THEN full_name := final_username; END IF;

  INSERT INTO public.profiles (
    id, username, name, avatar_url,
    first_name, middle_name, last_name, gender, phone, address, country, country_code
  )
  VALUES (
    NEW.id,
    final_username,
    full_name,
    meta->>'avatar_url',
    coalesce(meta->>'first_name', ''),
    coalesce(meta->>'middle_name', ''),
    coalesce(meta->>'last_name', ''),
    coalesce(meta->>'gender', ''),
    coalesce(meta->>'phone', ''),
    coalesce(meta->>'address', ''),
    coalesce(meta->>'country', ''),
    coalesce(meta->>'country_code', '')
  );
  RETURN NEW;
END;
$$;