-- Debug profile trigger to see what's happening with username
-- Run this in your Supabase SQL Editor

-- 1. First, drop the existing trigger if it exists
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
DROP FUNCTION IF EXISTS public.handle_new_user();

-- 2. Create a debug trigger function
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
DECLARE
  user_username TEXT;
  meta_data JSONB;
BEGIN
  -- Log all the data we receive for debugging
  RAISE NOTICE '=== DEBUG: New user created ===';
  RAISE NOTICE 'User ID: %', NEW.id;
  RAISE NOTICE 'User Email: %', NEW.email;
  RAISE NOTICE 'Raw user meta data: %', NEW.raw_user_meta_data;
  RAISE NOTICE 'User meta data type: %', jsonb_typeof(NEW.raw_user_meta_data);
  
  -- Try to extract username from different possible locations
  meta_data := NEW.raw_user_meta_data;
  
  -- Method 1: Direct username field
  user_username := meta_data->>'username';
  RAISE NOTICE 'Method 1 - Direct username: %', user_username;
  
  -- Method 2: Check if it's nested differently
  IF user_username IS NULL THEN
    user_username := meta_data->>'user_metadata'->>'username';
    RAISE NOTICE 'Method 2 - Nested username: %', user_username;
  END IF;
  
  -- Method 3: Check if it's in app_metadata
  IF user_username IS NULL THEN
    user_username := NEW.app_metadata->>'username';
    RAISE NOTICE 'Method 3 - App metadata username: %', user_username;
  END IF;
  
  -- Method 4: Check if it's in raw_user_meta_data differently
  IF user_username IS NULL THEN
    user_username := meta_data->>'name';
    RAISE NOTICE 'Method 4 - Name field: %', user_username;
  END IF;
  
  -- If still no username, generate one
  IF user_username IS NULL OR user_username = '' THEN
    user_username := 'User' || substr(NEW.id::text, 1, 8);
    RAISE NOTICE 'Method 5 - Generated username: %', user_username;
  END IF;
  
  RAISE NOTICE 'Final username to use: %', user_username;
  
  -- Now create the profile
  INSERT INTO public.profiles (
    id, 
    username, 
    avatar_url,
    level, 
    badge,
    points,
    followers_count,
    created_at, 
    updated_at, 
    last_seen_at,
    privacy,
    notify_chat,
    notify_events,
    notify_forum,
    notify_payments
  )
  VALUES (
    NEW.id,
    user_username,
    NULL, -- avatar_url
    'Brons', -- level
    'SuperBoy', -- badge
    0, -- points
    0, -- followers_count
    NOW(), -- created_at
    NOW(), -- updated_at
    NOW(), -- last_seen_at
    'public', -- privacy
    true, -- notify_chat
    true, -- notify_events
    true, -- notify_forum
    false -- notify_payments
  );
  
  RAISE NOTICE 'Profile created successfully for user % with username: %', NEW.id, user_username;
  RETURN NEW;
EXCEPTION
  WHEN unique_violation THEN
    RAISE NOTICE 'Profile already exists for user %', NEW.id;
    RETURN NEW;
  WHEN OTHERS THEN
    RAISE WARNING 'Failed to create profile for user %: %', NEW.id, SQLERRM;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 3. Create the trigger
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- 4. Grant necessary permissions
GRANT USAGE ON SCHEMA public TO authenticated;
GRANT ALL ON public.profiles TO authenticated;

-- 5. Enable RLS if not already enabled
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

-- 6. Create/Update RLS policies
DROP POLICY IF EXISTS "Users can view own profile" ON public.profiles;
CREATE POLICY "Users can view own profile" ON public.profiles
    FOR SELECT USING (auth.uid() = id);

DROP POLICY IF EXISTS "Users can update own profile" ON public.profiles;
CREATE POLICY "Users can update own profile" ON public.profiles
    FOR UPDATE USING (auth.uid() = id);

DROP POLICY IF EXISTS "Users can insert own profile" ON public.profiles;
CREATE POLICY "Users can insert own profile" ON public.profiles
    FOR INSERT WITH CHECK (auth.uid() = id);

-- 7. Test the setup
SELECT 'Debug profile trigger created successfully!' as status;

-- 8. Check if trigger is working
SELECT 
    trigger_name,
    event_manipulation,
    action_statement
FROM information_schema.triggers 
WHERE trigger_name = 'on_auth_user_created';
