-- Initial authentication and profiles setup
-- Consolidates: 002_initial_setup.sql + 012_fix_profile_creation_trigger.sql + 014_fix_google_profile_creation.sql

-- Create profiles table
CREATE TABLE IF NOT EXISTS public.profiles (
  id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
  email TEXT,
  full_name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- Enable RLS on profiles
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

-- Create policies for profiles
DROP POLICY IF EXISTS "Public profiles are viewable by everyone." ON public.profiles;
CREATE POLICY "Public profiles are viewable by everyone." ON public.profiles
  FOR SELECT USING (true);

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

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

-- Enhanced function to handle new user registration with Google OAuth support
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
DECLARE
  user_full_name TEXT;
  user_avatar_url TEXT;
  user_email 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
      UPDATE public.profiles
      SET
        email = user_email,
        full_name = COALESCE(user_full_name, full_name),
        avatar_url = COALESCE(NULLIF(user_avatar_url, ''), avatar_url),
        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
CREATE OR REPLACE FUNCTION public.handle_user_update()
RETURNS TRIGGER AS $$
DECLARE
  user_full_name TEXT;
  user_avatar_url TEXT;
  user_email 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'
  );

  -- Update the profile
  BEGIN
    UPDATE public.profiles
    SET
      email = user_email,
      full_name = COALESCE(user_full_name, full_name),
      avatar_url = COALESCE(NULLIF(user_avatar_url, ''), avatar_url),
      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;

-- Drop existing triggers to recreate them
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
DROP TRIGGER IF EXISTS on_auth_user_updated ON auth.users;

-- Create triggers using the correct syntax (EXECUTE FUNCTION for PostgreSQL 11+)
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW 
  EXECUTE FUNCTION public.handle_new_user();

CREATE TRIGGER on_auth_user_updated
  AFTER UPDATE ON auth.users
  FOR EACH ROW 
  EXECUTE FUNCTION public.handle_user_update();

-- Add helpful comments
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 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 includes error handling.';

-- Create a helper function to backfill any missing profiles for existing users
CREATE OR REPLACE FUNCTION public.backfill_missing_profiles()
RETURNS INTEGER AS $$
DECLARE
  missing_count INTEGER := 0;
  user_record RECORD;
BEGIN
  -- Find auth.users without profiles
  FOR user_record IN 
    SELECT u.id, u.email, u.raw_user_meta_data
    FROM auth.users u
    LEFT JOIN public.profiles p ON u.id = p.id
    WHERE p.id IS NULL
  LOOP
    -- Create profile for this user
    INSERT INTO public.profiles (id, email, full_name, avatar_url, created_at, updated_at)
    VALUES (
      user_record.id,
      COALESCE(user_record.email, ''),
      COALESCE(
        user_record.raw_user_meta_data->>'full_name',
        user_record.raw_user_meta_data->>'name',
        user_record.raw_user_meta_data->>'display_name',
        SPLIT_PART(COALESCE(user_record.email, ''), '@', 1)
      ),
      COALESCE(
        user_record.raw_user_meta_data->>'avatar_url',
        user_record.raw_user_meta_data->>'picture',
        user_record.raw_user_meta_data->>'photo_url',
        ''
      ),
      timezone('utc'::text, now()),
      timezone('utc'::text, now())
    );
    
    missing_count := missing_count + 1;
  END LOOP;
  
  RETURN missing_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Run the backfill function to create profiles for any existing users without them
SELECT public.backfill_missing_profiles() as profiles_created;

-- Clean up the backfill function (we don't need it after this migration)
DROP FUNCTION public.backfill_missing_profiles();