-- Fix avatar overwrite issue: preserve custom avatars when user logs in again
-- The issue was that both handle_new_user and handle_user_update functions would 
-- overwrite custom avatars with Google avatars on every login. We need to only 
-- use Google avatars if the user hasn't uploaded a custom avatar.

-- Enhanced function to handle new user registration with Google OAuth support
-- Modified to preserve custom avatars
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
DECLARE
  user_full_name TEXT;
  user_avatar_url TEXT;
  user_email TEXT;
  current_avatar_url TEXT;
BEGIN
  -- Extract user data with fallbacks
  user_email := COALESCE(NEW.email, '');
  
  -- Handle different metadata structures from Google OAuth
  -- Google sometimes stores data in different keys
  user_full_name := COALESCE(
    NEW.raw_user_meta_data->>'full_name',
    NEW.raw_user_meta_data->>'name',
    NEW.raw_user_meta_data->>'display_name',
    SPLIT_PART(user_email, '@', 1) -- fallback to email username
  );
  
  user_avatar_url := COALESCE(
    NEW.raw_user_meta_data->>'avatar_url',
    NEW.raw_user_meta_data->>'picture',
    NEW.raw_user_meta_data->>'photo_url',
    ''
  );

  -- Insert the profile with error handling
  BEGIN
    INSERT INTO public.profiles (id, email, full_name, avatar_url, created_at, updated_at)
    VALUES (
      NEW.id,
      user_email,
      user_full_name,
      user_avatar_url,
      timezone('utc'::text, now()),
      timezone('utc'::text, now())
    );
  EXCEPTION
    WHEN unique_violation THEN
      -- Profile already exists, update it instead
      -- Get current avatar URL to preserve custom avatars
      SELECT avatar_url INTO current_avatar_url
      FROM public.profiles
      WHERE id = NEW.id;
      
      UPDATE public.profiles
      SET
        email = user_email,
        full_name = COALESCE(user_full_name, full_name),
        -- Only use Google avatar if user hasn't uploaded a custom avatar
        -- Custom avatars are stored in Supabase storage and contain 'supabase' in the URL
        avatar_url = CASE 
          WHEN current_avatar_url IS NULL OR current_avatar_url = '' THEN
            -- No existing avatar, use Google avatar
            COALESCE(NULLIF(user_avatar_url, ''), current_avatar_url)
          WHEN current_avatar_url LIKE '%supabase%' THEN
            -- User has uploaded a custom avatar, preserve it
            current_avatar_url
          ELSE
            -- User has a non-Supabase avatar (could be from another provider), preserve it
            current_avatar_url
        END,
        updated_at = timezone('utc'::text, now())
      WHERE id = NEW.id;
    WHEN OTHERS THEN
      -- Log error but don't fail the user creation
      RAISE WARNING 'Failed to create profile for user %: %', NEW.id, SQLERRM;
  END;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Enhanced function to handle user updates with Google OAuth support
-- Modified to preserve custom avatars
CREATE OR REPLACE FUNCTION public.handle_user_update()
RETURNS TRIGGER AS $$
DECLARE
  user_full_name TEXT;
  user_avatar_url TEXT;
  user_email TEXT;
  current_avatar_url TEXT;
BEGIN
  -- Extract user data with fallbacks (same logic as creation)
  user_email := COALESCE(NEW.email, OLD.email, '');
  
  user_full_name := COALESCE(
    NEW.raw_user_meta_data->>'full_name',
    NEW.raw_user_meta_data->>'name',
    NEW.raw_user_meta_data->>'display_name',
    OLD.raw_user_meta_data->>'full_name',
    OLD.raw_user_meta_data->>'name'
  );
  
  user_avatar_url := COALESCE(
    NEW.raw_user_meta_data->>'avatar_url',
    NEW.raw_user_meta_data->>'picture',
    NEW.raw_user_meta_data->>'photo_url',
    OLD.raw_user_meta_data->>'avatar_url',
    OLD.raw_user_meta_data->>'picture'
  );

  -- Get current avatar URL from profiles table
  SELECT avatar_url INTO current_avatar_url
  FROM public.profiles
  WHERE id = NEW.id;

  -- Update the profile
  BEGIN
    UPDATE public.profiles
    SET
      email = user_email,
      full_name = COALESCE(user_full_name, full_name),
      -- Only use Google avatar if user hasn't uploaded a custom avatar
      -- Custom avatars are stored in Supabase storage and contain 'supabase' in the URL
      avatar_url = CASE 
        WHEN current_avatar_url IS NULL OR current_avatar_url = '' THEN
          -- No existing avatar, use Google avatar
          COALESCE(NULLIF(user_avatar_url, ''), current_avatar_url)
        WHEN current_avatar_url LIKE '%supabase%' THEN
          -- User has uploaded a custom avatar, preserve it
          current_avatar_url
        ELSE
          -- User has a non-Supabase avatar (could be from another provider), preserve it
          current_avatar_url
      END,
      updated_at = timezone('utc'::text, now())
    WHERE id = NEW.id;
    
    -- If no profile exists, create one
    IF NOT FOUND THEN
      INSERT INTO public.profiles (id, email, full_name, avatar_url, created_at, updated_at)
      VALUES (
        NEW.id,
        user_email,
        user_full_name,
        user_avatar_url,
        timezone('utc'::text, now()),
        timezone('utc'::text, now())
      );
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE WARNING 'Failed to update profile for user %: %', NEW.id, SQLERRM;
  END;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Update the comments to reflect the new behavior
COMMENT ON FUNCTION public.handle_new_user() IS 'Automatically creates a profile when a new user signs up via auth. Handles Google OAuth metadata variations, preserves custom avatars, and includes error handling.';
COMMENT ON FUNCTION public.handle_user_update() IS 'Automatically updates the profile when user metadata changes. Handles Google OAuth metadata variations and preserves custom avatars.';