-- Fix the handle_new_user function to run with SECURITY DEFINER
-- This allows the trigger to bypass RLS and create profiles

-- Drop the existing function
DROP FUNCTION IF EXISTS handle_new_user() CASCADE;

-- Recreate with SECURITY DEFINER
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER
SECURITY DEFINER -- This is the key fix!
SET search_path = public
LANGUAGE plpgsql
AS $$
DECLARE
  v_username TEXT;
BEGIN
  -- Extract username from email
  v_username := SPLIT_PART(NEW.email, '@', 1);
  
  -- Check if username already exists
  IF EXISTS (SELECT 1 FROM public.profiles WHERE username = v_username) THEN
    RAISE EXCEPTION 'Bu kullanıcı adı zaten kullanılıyor. Lütfen farklı bir kullanıcı adı seçin.';
  END IF;
  
  -- Insert new profile (will bypass RLS due to SECURITY DEFINER)
  INSERT INTO public.profiles (id, username, full_name)
  VALUES (
    NEW.id,
    v_username,
    COALESCE(NEW.raw_user_meta_data->>'full_name', v_username)
  );
  
  RETURN NEW;
EXCEPTION
  WHEN unique_violation THEN
    RAISE EXCEPTION 'Bu kullanıcı adı zaten kullanılıyor. Lütfen farklı bir kullanıcı adı seçin.';
  WHEN OTHERS THEN
    RAISE EXCEPTION 'Kullanıcı profili oluşturulurken bir hata oluştu: %', SQLERRM;
END;
$$;

-- Recreate the trigger
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION handle_new_user();

-- Grant execute permission to authenticated users
GRANT EXECUTE ON FUNCTION handle_new_user() TO authenticated;
GRANT EXECUTE ON FUNCTION handle_new_user() TO service_role;