chattyness/db/schema/functions/002_user_init.sql

-- Chattyness User Initialization Functions
-- PostgreSQL 18
--
-- Functions to initialize new users with default props and avatars.
-- Load via: psql -f schema/functions/002_user_init.sql

\set ON_ERROR_STOP on

BEGIN;

-- =============================================================================
-- Initialize New User with Default Props and Avatar
-- =============================================================================
-- Called when a new user is created to give them:
--   1. All face-tagged server props in their inventory
--   2. A default avatar (slot 0) with the Face prop and all emotions configured
--
-- Note: active_avatars entry is NOT created here - it's created when the user
-- joins a realm for the first time (per-realm avatar state).
-- =============================================================================

CREATE OR REPLACE FUNCTION auth.initialize_new_user(p_user_id UUID)
RETURNS VOID AS $$
DECLARE
    v_avatar_id UUID;
    v_face_inventory_id UUID;
    v_neutral_inventory_id UUID;
    v_happy_inventory_id UUID;
    v_sad_inventory_id UUID;
    v_angry_inventory_id UUID;
    v_surprised_inventory_id UUID;
    v_thinking_inventory_id UUID;
    v_laughing_inventory_id UUID;
    v_crying_inventory_id UUID;
    v_love_inventory_id UUID;
    v_confused_inventory_id UUID;
    v_sleeping_inventory_id UUID;
    v_wink_inventory_id UUID;
    v_prop RECORD;
BEGIN
    -- Insert all face-tagged server props into user's inventory
    -- Note: inventory layer/position are only for content layer props (skin/clothes/accessories).
    -- Emotion props have default_emotion instead of default_layer, so they get NULL layer/position.
    FOR v_prop IN
        SELECT id, name, asset_path, default_layer, default_emotion, default_position, slug,
               is_transferable, is_portable, is_droppable
        FROM server.props
        WHERE tags @> ARRAY['face']
        AND is_active = true
    LOOP
        -- Use a local variable for the inserted inventory ID
        DECLARE
            v_new_inventory_id UUID;
        BEGIN
            INSERT INTO auth.inventory (
                user_id,
                server_prop_id,
                prop_name,
                prop_asset_path,
                layer,
                position,
                origin,
                is_transferable,
                is_portable,
                is_droppable
            )
            VALUES (
                p_user_id,
                v_prop.id,
                v_prop.name,
                v_prop.asset_path,
                v_prop.default_layer,  -- NULL for emotion props
                CASE WHEN v_prop.default_layer IS NOT NULL THEN v_prop.default_position ELSE NULL END,
                'server_library',
                v_prop.is_transferable,
                v_prop.is_portable,
                v_prop.is_droppable
            )
            RETURNING id INTO v_new_inventory_id;

            -- Track inventory IDs for avatar assignment based on slug
            CASE v_prop.slug
                WHEN 'face' THEN v_face_inventory_id := v_new_inventory_id;
                WHEN 'neutral' THEN v_neutral_inventory_id := v_new_inventory_id;
                WHEN 'smile' THEN v_happy_inventory_id := v_new_inventory_id;
                WHEN 'sad' THEN v_sad_inventory_id := v_new_inventory_id;
                WHEN 'angry' THEN v_angry_inventory_id := v_new_inventory_id;
                WHEN 'surprised' THEN v_surprised_inventory_id := v_new_inventory_id;
                WHEN 'thinking' THEN v_thinking_inventory_id := v_new_inventory_id;
                WHEN 'laughing' THEN v_laughing_inventory_id := v_new_inventory_id;
                WHEN 'crying' THEN v_crying_inventory_id := v_new_inventory_id;
                WHEN 'love' THEN v_love_inventory_id := v_new_inventory_id;
                WHEN 'confused' THEN v_confused_inventory_id := v_new_inventory_id;
                WHEN 'sleeping' THEN v_sleeping_inventory_id := v_new_inventory_id;
                WHEN 'wink' THEN v_wink_inventory_id := v_new_inventory_id;
                ELSE NULL;
            END CASE;
        END;
    END LOOP;

    -- Create default avatar (slot 0) with the Face prop in skin layer
    -- and all emotion props in their respective emotion slots at position 4 (center)
    INSERT INTO auth.avatars (
        user_id,
        name,
        slot_number,
        last_emotion,
        -- Content layer: Face goes in skin layer, center position
        l_skin_4,
        -- Emotion layers: Each emotion prop goes to its matching emotion at center position
        e_neutral_4,
        e_happy_4,
        e_sad_4,
        e_angry_4,
        e_surprised_4,
        e_thinking_4,
        e_laughing_4,
        e_crying_4,
        e_love_4,
        e_confused_4,
        e_sleeping_4,
        e_wink_4
    )
    VALUES (
        p_user_id,
        'Default',
        0,
        0, -- Start with neutral emotion
        v_face_inventory_id,
        v_neutral_inventory_id,
        v_happy_inventory_id,
        v_sad_inventory_id,
        v_angry_inventory_id,
        v_surprised_inventory_id,
        v_thinking_inventory_id,
        v_laughing_inventory_id,
        v_crying_inventory_id,
        v_love_inventory_id,
        v_confused_inventory_id,
        v_sleeping_inventory_id,
        v_wink_inventory_id
    )
    RETURNING id INTO v_avatar_id;

    -- Note: We don't create an active_avatars entry here because that's per-realm.
    -- The active_avatars entry will be created when the user first joins a realm.
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

COMMENT ON FUNCTION auth.initialize_new_user(UUID) IS
    'Initialize a new user with default props in inventory and a default avatar configuration';

-- =============================================================================
-- Trigger Function for User Registration
-- =============================================================================
-- Wrapper trigger function that calls initialize_new_user.
-- =============================================================================

CREATE OR REPLACE FUNCTION auth.initialize_new_user_trigger()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM auth.initialize_new_user(NEW.id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

COMMENT ON FUNCTION auth.initialize_new_user_trigger() IS
    'Trigger function to initialize new users on registration';

COMMIT;