-- Migration: Fix handle_new_user() trigger to handle invalid user types
-- Issue: H12 - Handle invalid user type in handle_new_user() trigger
-- Date: 2025-02-08

-- ============================================
-- PROBLEM:
-- The original handle_new_user() function attempts to cast raw_user_meta_data->>'type'
-- directly to user_type enum. If the type is missing or invalid, the cast fails
-- with a PostgreSQL error before COALESCE can provide the default value.
--
-- SOLUTION:
-- Use a CASE statement to validate the user_type value before casting.
-- Only cast to user_type enum if the value is one of the allowed values ('worker', 'contractor').
-- Otherwise, default to 'worker'.
-- ============================================

-- Drop the existing trigger first
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;

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

-- Create the fixed function with proper validation
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger AS $$
DECLARE
    v_user_type text;
    v_valid_type user_type;
BEGIN
    -- Get the user_type from metadata, default to null if not present
    v_user_type := NEW.raw_user_meta_data->>'type';
    
    -- Validate the user_type before casting
    -- Only allow 'worker' or 'contractor', default to 'worker' for any other value
    CASE v_user_type
        WHEN 'worker' THEN
            v_valid_type := 'worker'::user_type;
        WHEN 'contractor' THEN
            v_valid_type := 'contractor'::user_type;
        ELSE
            -- Handle missing, null, or invalid user_type
            v_valid_type := 'worker'::user_type;
    END CASE;
    
    -- Insert the profile with validated type
    INSERT INTO public.profiles (id, email, type)
    VALUES (
        NEW.id,
        NEW.email,
        v_valid_type
    );
    
    RETURN NEW;
EXCEPTION
    WHEN OTHERS THEN
        -- Log the error details (available in PostgreSQL logs)
        RAISE WARNING 'handle_new_user() failed for user %: %', NEW.id, SQLERRM;
        
        -- Re-raise the exception to prevent the transaction from continuing
        -- This ensures auth.users insert fails if profiles insert fails
        RAISE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

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

-- ============================================
-- VERIFICATION:
-- ============================================

-- Test comment (run manually to verify):
-- 
-- Test 1: Valid 'worker' type
-- SELECT auth.sign_up_test_user('{"type": "worker"}');
-- Expected: Creates profile with type = 'worker'
--
-- Test 2: Valid 'contractor' type
-- SELECT auth.sign_up_test_user('{"type": "contractor"}');
-- Expected: Creates profile with type = 'contractor'
--
-- Test 3: Missing type
-- SELECT auth.sign_up_test_user('{}');
-- Expected: Creates profile with type = 'worker' (default)
--
-- Test 4: Invalid type
-- SELECT auth.sign_up_test_user('{"type": "invalid_type"}');
-- Expected: Creates profile with type = 'worker' (default)
--
-- Test 5: Null type
-- SELECT auth.sign_up_test_user('{"type": null}');
-- Expected: Creates profile with type = 'worker' (default)

-- ============================================
-- NOTES:
-- ============================================
-- This migration ensures backward compatibility.
-- Existing users are not affected.
-- The function now gracefully handles:
--   - Missing 'type' key in metadata
--   - Null 'type' value
--   - Invalid 'type' values (not 'worker' or 'contractor')
--   - Empty string 'type' value
