-- Modifying the trigger to fire when email is confirmed, as per mandatory login requirements
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER SET search_path = public
AS $$
DECLARE
  user_count int;
  username_val text;
BEGIN
  SELECT COUNT(*) INTO user_count FROM public.profiles;
  
  -- Extract username from email or metadata
  username_val := COALESCE(
    NEW.raw_user_meta_data->>'username', 
    SPLIT_PART(NEW.email, '@', 1)
  );

  -- Insert profile synced with fields collected at signup.
  -- Initial user becomes admin if roles were intended (based on user_role enum if it exists, otherwise we just insert)
  -- For this specific app, we use a simple text role or separate roles table if needed.
  -- Looking at existing migrations, profiles doesn't have a 'role' column yet in migration 1.
  -- Let's check if 'user_role' type exists.
  
  INSERT INTO public.profiles (id, username, full_name)
  VALUES (
    NEW.id,
    username_val,
    COALESCE(NEW.raw_user_meta_data->>'full_name', username_val)
  );
  
  RETURN NEW;
END;
$$;

-- Drop old trigger
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;

-- Create new trigger with correct timing
DROP TRIGGER IF EXISTS on_auth_user_confirmed ON auth.users;
CREATE TRIGGER on_auth_user_confirmed
  AFTER UPDATE ON auth.users
  FOR EACH ROW
  WHEN (OLD.confirmed_at IS NULL AND NEW.confirmed_at IS NOT NULL)
  EXECUTE FUNCTION public.handle_new_user();
