-- Function to handle new user signup from Supabase Auth
-- This runs when a user is inserted into auth.users

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER  -- Run with elevated privileges
SET search_path = public
AS $$
DECLARE
    new_owner_id UUID;
    new_group_id UUID;
    user_name TEXT;
BEGIN
    -- Extract display name from metadata, fallback to email
    user_name := COALESCE(
        NEW.raw_user_meta_data->>'full_name',
        NEW.raw_user_meta_data->>'name',
        split_part(NEW.email, '@', 1)
    );

    -- Create personal owner for this user
    INSERT INTO public.owners (name, owner_type)
    VALUES (user_name, 'personal')
    RETURNING id INTO new_owner_id;

    -- Create local user record
    INSERT INTO public.users (id, email, display_name, default_owner_id)
    VALUES (
        NEW.id,
        NEW.email,
        user_name,
        new_owner_id
    );

    -- Create default group for personal owner
    INSERT INTO public.groups (owner_id, name, is_default)
    VALUES (new_owner_id, 'Personal', true)
    RETURNING id INTO new_group_id;

    -- Add user as owner of their personal group
    INSERT INTO public.group_members (group_id, user_id, role)
    VALUES (new_group_id, NEW.id, 'owner');

    RETURN NEW;
EXCEPTION WHEN OTHERS THEN
    -- Log error but don't fail the signup
    RAISE WARNING 'handle_new_user failed for %: %', NEW.id, SQLERRM;
    RETURN NEW;
END;
$$;

-- NOTE: The trigger on auth.users must be created in the Supabase dashboard
-- or via supabase CLI because we can't directly access auth schema in migrations.
--
-- Run this SQL in Supabase SQL Editor to create the trigger:
--
-- CREATE TRIGGER on_auth_user_created
--     AFTER INSERT ON auth.users
--     FOR EACH ROW
--     EXECUTE FUNCTION public.handle_new_user();

COMMENT ON FUNCTION handle_new_user IS 'Creates owner, user, group, and membership when a new user signs up via Supabase Auth';