Rework avatars.

Now we have a concept of an avatar at the server, realm, and scene level
and we have the groundwork for a realm store. New uesrs no longer props,
they get a default avatar. New system supports gender
{male,female,neutral} and {child,adult}.

crates/chattyness-db/src/queries/avatars.rs

@@ -16,7 +16,11 @@ pub async fn get_active_avatar<'e>(
 ) -> Result<Option<ActiveAvatar>, AppError> {
     let avatar = sqlx::query_as::<_, ActiveAvatar>(
         r#"
-        SELECT user_id, realm_id, avatar_id, current_emotion, updated_at
+        SELECT
+            user_id, realm_id, avatar_id,
+            selected_server_avatar_id, selected_realm_avatar_id,
+            current_emotion, updated_at,
+            forced_avatar_id, forced_avatar_source, forced_by, forced_until
         FROM auth.active_avatars
         WHERE user_id = $1 AND realm_id = $2
         "#,
@@ -31,86 +35,54 @@ pub async fn get_active_avatar<'e>(
 
 /// Set the current emotion for a user in a realm.
 /// Returns the full emotion layer (9 asset paths) for the new emotion.
+///
+/// This function works with any avatar source:
+/// - Custom user avatars (auth.avatars)
+/// - Selected server avatars (server.avatars)
+/// - Selected realm avatars (realm.avatars)
+/// - Server default avatars (server.avatars via server.config)
+/// - Realm default avatars (realm.avatars via realm.realms)
+///
+/// Takes both a connection (for RLS-protected update) and a pool (for avatar resolution).
 pub async fn set_emotion<'e>(
-    executor: impl PgExecutor<'e>,
+    conn: &mut PgConnection,
+    pool: &PgPool,
     user_id: Uuid,
     realm_id: Uuid,
     emotion: EmotionState,
 ) -> Result<[Option<String>; 9], AppError> {
-    // Map emotion to column prefix
-    let emotion_prefix = match emotion {
-        EmotionState::Neutral => "e_neutral",
-        EmotionState::Happy => "e_happy",
-        EmotionState::Sad => "e_sad",
-        EmotionState::Angry => "e_angry",
-        EmotionState::Surprised => "e_surprised",
-        EmotionState::Thinking => "e_thinking",
-        EmotionState::Laughing => "e_laughing",
-        EmotionState::Crying => "e_crying",
-        EmotionState::Love => "e_love",
-        EmotionState::Confused => "e_confused",
-        EmotionState::Sleeping => "e_sleeping",
-        EmotionState::Wink => "e_wink",
-    };
-
-    // Get the numeric index for the database
-    let emotion_index = emotion.to_index() as i16;
-
-    // Build dynamic query for the specific emotion's 9 positions
-    let query = format!(
+    // First, update the emotion in active_avatars (uses RLS connection)
+    let update_result = sqlx::query(
         r#"
-        WITH updated AS (
-            UPDATE auth.active_avatars
-            SET current_emotion = $3, updated_at = now()
-            WHERE user_id = $1 AND realm_id = $2
-            RETURNING avatar_id
-        )
-        SELECT
-            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.{prefix}_0) as p0,
-            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.{prefix}_1) as p1,
-            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.{prefix}_2) as p2,
-            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.{prefix}_3) as p3,
-            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.{prefix}_4) as p4,
-            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.{prefix}_5) as p5,
-            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.{prefix}_6) as p6,
-            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.{prefix}_7) as p7,
-            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.{prefix}_8) as p8
-        FROM updated u
-        JOIN auth.avatars a ON a.id = u.avatar_id
+        UPDATE auth.active_avatars
+        SET current_emotion = $3::server.emotion_state, updated_at = now()
+        WHERE user_id = $1 AND realm_id = $2
         "#,
-        prefix = emotion_prefix
-    );
+    )
+    .bind(user_id)
+    .bind(realm_id)
+    .bind(emotion.to_string())
+    .execute(&mut *conn)
+    .await?;
 
-    let result = sqlx::query_as::<_, EmotionLayerRow>(&query)
-        .bind(user_id)
-        .bind(realm_id)
-        .bind(emotion_index)
-        .fetch_optional(executor)
-        .await?;
-
-    match result {
-        Some(row) => Ok([
-            row.p0, row.p1, row.p2, row.p3, row.p4, row.p5, row.p6, row.p7, row.p8,
-        ]),
-        None => Err(AppError::NotFound(
+    if update_result.rows_affected() == 0 {
+        return Err(AppError::NotFound(
             "No active avatar for this user in this realm".to_string(),
-        )),
+        ));
+    }
+
+    // Now get the effective avatar and return the emotion layer (uses pool for multiple queries)
+    let render_data = get_effective_avatar_render_data(pool, user_id, realm_id).await?;
+
+    match render_data {
+        Some((data, _source)) => Ok(data.emotion_layer),
+        None => {
+            // No avatar found - return empty layer
+            Ok([None, None, None, None, None, None, None, None, None])
+        }
     }
 }
 
-/// Row type for emotion layer query.
-#[derive(Debug, sqlx::FromRow)]
-struct EmotionLayerRow {
-    p0: Option<String>,
-    p1: Option<String>,
-    p2: Option<String>,
-    p3: Option<String>,
-    p4: Option<String>,
-    p5: Option<String>,
-    p6: Option<String>,
-    p7: Option<String>,
-    p8: Option<String>,
-}
 
 /// Get emotion availability for a user's avatar in a realm.
 ///
@@ -1469,7 +1441,7 @@ fn collect_uuids(dest: &mut Vec<Uuid>, sources: &[Option<Uuid>]) {
 #[derive(Debug, sqlx::FromRow)]
 struct AvatarWithEmotion {
     pub id: Uuid,
-    pub current_emotion: i16,
+    pub current_emotion: EmotionState,
     // Content layers
     pub l_skin_0: Option<Uuid>,
     pub l_skin_1: Option<Uuid>,
@@ -1617,22 +1589,18 @@ pub async fn set_emotion_simple<'e>(
     executor: impl PgExecutor<'e>,
     user_id: Uuid,
     realm_id: Uuid,
-    emotion: i16,
+    emotion: EmotionState,
 ) -> Result<(), AppError> {
-    if emotion < 0 || emotion > 11 {
-        return Err(AppError::Validation("Emotion must be 0-11".to_string()));
-    }
-
     let result = sqlx::query(
         r#"
         UPDATE auth.active_avatars
-        SET current_emotion = $3, updated_at = now()
+        SET current_emotion = $3::server.emotion_state, updated_at = now()
         WHERE user_id = $1 AND realm_id = $2
         "#,
     )
     .bind(user_id)
     .bind(realm_id)
-    .bind(emotion)
+    .bind(emotion.to_string())
     .execute(executor)
     .await?;
 
@@ -1723,3 +1691,472 @@ pub async fn update_avatar_slot(
 
     Ok(())
 }
+
+/// Data needed to resolve effective avatar for a user.
+#[derive(Debug, sqlx::FromRow)]
+pub struct AvatarResolutionContext {
+    // Active avatar row data
+    pub avatar_id: Option<Uuid>,
+    pub selected_server_avatar_id: Option<Uuid>,
+    pub selected_realm_avatar_id: Option<Uuid>,
+    pub current_emotion: EmotionState,
+    // Forced avatar data
+    pub forced_avatar_id: Option<Uuid>,
+    pub forced_avatar_source: Option<String>,
+    pub forced_until: Option<chrono::DateTime<chrono::Utc>>,
+    // User preferences
+    pub gender_preference: crate::models::GenderPreference,
+    pub age_category: crate::models::AgeCategory,
+}
+
+/// Source of the resolved avatar.
+#[derive(Debug, Clone, Copy, PartialEq, Eq)]
+pub enum AvatarSource {
+    /// User's custom avatar from auth.avatars
+    Custom,
+    /// User-selected realm avatar from avatar store
+    SelectedRealm,
+    /// User-selected server avatar from avatar store
+    SelectedServer,
+    /// Realm default avatar based on gender/age
+    RealmDefault,
+    /// Server default avatar based on gender/age
+    ServerDefault,
+    /// Forced avatar (mod command or scene)
+    Forced,
+}
+
+/// Get the effective avatar render data for a user in a realm.
+///
+/// This function implements the avatar resolution priority chain:
+/// 1. Forced avatar (mod command or scene) - highest priority
+/// 2. User's custom avatar (auth.avatars via avatar_id)
+/// 3. User-selected realm avatar (selected_realm_avatar_id)
+/// 4. User-selected server avatar (selected_server_avatar_id)
+/// 5. Realm default (based on gender+age)
+/// 6. Server default (based on gender+age) - lowest priority
+pub async fn get_effective_avatar_render_data<'e>(
+    executor: impl PgExecutor<'e> + Copy,
+    user_id: Uuid,
+    realm_id: Uuid,
+) -> Result<Option<(crate::models::AvatarRenderData, AvatarSource)>, AppError> {
+
+    // Get the resolution context with all necessary data
+    // Use LEFT JOIN so we can still get user preferences even without an active_avatars entry
+    let ctx = sqlx::query_as::<_, AvatarResolutionContext>(
+        r#"
+        SELECT
+            aa.avatar_id,
+            aa.selected_server_avatar_id,
+            aa.selected_realm_avatar_id,
+            COALESCE(aa.current_emotion, 'happy'::server.emotion_state) as current_emotion,
+            aa.forced_avatar_id,
+            aa.forced_avatar_source,
+            aa.forced_until,
+            u.gender_preference,
+            u.age_category
+        FROM auth.users u
+        LEFT JOIN auth.active_avatars aa ON aa.user_id = u.id AND aa.realm_id = $2
+        WHERE u.id = $1
+        "#,
+    )
+    .bind(user_id)
+    .bind(realm_id)
+    .fetch_optional(executor)
+    .await?;
+
+    let Some(ctx) = ctx else {
+        // User doesn't exist
+        return Ok(None);
+    };
+
+    // Priority 1: Check for forced avatar (not expired)
+    if let Some(forced_id) = ctx.forced_avatar_id {
+        let is_expired = ctx.forced_until.map(|t| t < chrono::Utc::now()).unwrap_or(false);
+        if !is_expired {
+            if let Some(source) = &ctx.forced_avatar_source {
+                match source.as_str() {
+                    "server" | "scene" => {
+                        // Resolve from server.avatars
+                        if let Some(avatar) = super::server_avatars::get_server_avatar_by_id(executor, forced_id).await? {
+                            let render = super::server_avatars::resolve_server_avatar_to_render_data(
+                                executor, &avatar, ctx.current_emotion
+                            ).await?;
+                            return Ok(Some((render, AvatarSource::Forced)));
+                        }
+                    }
+                    "realm" => {
+                        // Resolve from realm.avatars
+                        if let Some(avatar) = super::realm_avatars::get_realm_avatar_by_id(executor, forced_id).await? {
+                            let render = super::realm_avatars::resolve_realm_avatar_to_render_data(
+                                executor, &avatar, ctx.current_emotion
+                            ).await?;
+                            return Ok(Some((render, AvatarSource::Forced)));
+                        }
+                    }
+                    _ => {}
+                }
+            }
+        }
+    }
+
+    // Priority 2: User's custom avatar
+    if let Some(avatar_id) = ctx.avatar_id {
+        if let Some(render) = resolve_user_avatar_to_render_data(executor, avatar_id, ctx.current_emotion).await? {
+            return Ok(Some((render, AvatarSource::Custom)));
+        }
+    }
+
+    // Priority 3: User-selected realm avatar
+    if let Some(realm_avatar_id) = ctx.selected_realm_avatar_id {
+        if let Some(avatar) = super::realm_avatars::get_realm_avatar_by_id(executor, realm_avatar_id).await? {
+            let render = super::realm_avatars::resolve_realm_avatar_to_render_data(
+                executor, &avatar, ctx.current_emotion
+            ).await?;
+            return Ok(Some((render, AvatarSource::SelectedRealm)));
+        }
+    }
+
+    // Priority 4: User-selected server avatar
+    if let Some(server_avatar_id) = ctx.selected_server_avatar_id {
+        if let Some(avatar) = super::server_avatars::get_server_avatar_by_id(executor, server_avatar_id).await? {
+            let render = super::server_avatars::resolve_server_avatar_to_render_data(
+                executor, &avatar, ctx.current_emotion
+            ).await?;
+            return Ok(Some((render, AvatarSource::SelectedServer)));
+        }
+    }
+
+    // Priority 5: Realm default avatar (based on gender+age)
+    let realm_default_id = get_realm_default_avatar_id(executor, realm_id, ctx.gender_preference, ctx.age_category).await?;
+    if let Some(avatar_id) = realm_default_id {
+        if let Some(avatar) = super::realm_avatars::get_realm_avatar_by_id(executor, avatar_id).await? {
+            let render = super::realm_avatars::resolve_realm_avatar_to_render_data(
+                executor, &avatar, ctx.current_emotion
+            ).await?;
+            return Ok(Some((render, AvatarSource::RealmDefault)));
+        }
+    }
+
+    // Priority 6: Server default avatar (based on gender+age)
+    let server_default_id = get_server_default_avatar_id(executor, ctx.gender_preference, ctx.age_category).await?;
+    if let Some(avatar_id) = server_default_id {
+        if let Some(avatar) = super::server_avatars::get_server_avatar_by_id(executor, avatar_id).await? {
+            let render = super::server_avatars::resolve_server_avatar_to_render_data(
+                executor, &avatar, ctx.current_emotion
+            ).await?;
+            return Ok(Some((render, AvatarSource::ServerDefault)));
+        }
+    }
+
+    Ok(None)
+}
+
+/// Resolve a user's custom avatar (from auth.avatars) to render data.
+async fn resolve_user_avatar_to_render_data<'e>(
+    executor: impl PgExecutor<'e> + Copy,
+    avatar_id: Uuid,
+    current_emotion: EmotionState,
+) -> Result<Option<crate::models::AvatarRenderData>, AppError> {
+    // Get the avatar with inventory joins
+    let avatar = sqlx::query_as::<_, AvatarWithEmotion>(
+        r#"
+        SELECT
+            a.id, $2::server.emotion_state as current_emotion,
+            a.l_skin_0, a.l_skin_1, a.l_skin_2, a.l_skin_3, a.l_skin_4,
+            a.l_skin_5, a.l_skin_6, a.l_skin_7, a.l_skin_8,
+            a.l_clothes_0, a.l_clothes_1, a.l_clothes_2, a.l_clothes_3, a.l_clothes_4,
+            a.l_clothes_5, a.l_clothes_6, a.l_clothes_7, a.l_clothes_8,
+            a.l_accessories_0, a.l_accessories_1, a.l_accessories_2, a.l_accessories_3, a.l_accessories_4,
+            a.l_accessories_5, a.l_accessories_6, a.l_accessories_7, a.l_accessories_8,
+            a.e_neutral_0, a.e_neutral_1, a.e_neutral_2, a.e_neutral_3, a.e_neutral_4,
+            a.e_neutral_5, a.e_neutral_6, a.e_neutral_7, a.e_neutral_8,
+            a.e_happy_0, a.e_happy_1, a.e_happy_2, a.e_happy_3, a.e_happy_4,
+            a.e_happy_5, a.e_happy_6, a.e_happy_7, a.e_happy_8,
+            a.e_sad_0, a.e_sad_1, a.e_sad_2, a.e_sad_3, a.e_sad_4,
+            a.e_sad_5, a.e_sad_6, a.e_sad_7, a.e_sad_8,
+            a.e_angry_0, a.e_angry_1, a.e_angry_2, a.e_angry_3, a.e_angry_4,
+            a.e_angry_5, a.e_angry_6, a.e_angry_7, a.e_angry_8,
+            a.e_surprised_0, a.e_surprised_1, a.e_surprised_2, a.e_surprised_3, a.e_surprised_4,
+            a.e_surprised_5, a.e_surprised_6, a.e_surprised_7, a.e_surprised_8,
+            a.e_thinking_0, a.e_thinking_1, a.e_thinking_2, a.e_thinking_3, a.e_thinking_4,
+            a.e_thinking_5, a.e_thinking_6, a.e_thinking_7, a.e_thinking_8,
+            a.e_laughing_0, a.e_laughing_1, a.e_laughing_2, a.e_laughing_3, a.e_laughing_4,
+            a.e_laughing_5, a.e_laughing_6, a.e_laughing_7, a.e_laughing_8,
+            a.e_crying_0, a.e_crying_1, a.e_crying_2, a.e_crying_3, a.e_crying_4,
+            a.e_crying_5, a.e_crying_6, a.e_crying_7, a.e_crying_8,
+            a.e_love_0, a.e_love_1, a.e_love_2, a.e_love_3, a.e_love_4,
+            a.e_love_5, a.e_love_6, a.e_love_7, a.e_love_8,
+            a.e_confused_0, a.e_confused_1, a.e_confused_2, a.e_confused_3, a.e_confused_4,
+            a.e_confused_5, a.e_confused_6, a.e_confused_7, a.e_confused_8,
+            a.e_sleeping_0, a.e_sleeping_1, a.e_sleeping_2, a.e_sleeping_3, a.e_sleeping_4,
+            a.e_sleeping_5, a.e_sleeping_6, a.e_sleeping_7, a.e_sleeping_8,
+            a.e_wink_0, a.e_wink_1, a.e_wink_2, a.e_wink_3, a.e_wink_4,
+            a.e_wink_5, a.e_wink_6, a.e_wink_7, a.e_wink_8
+        FROM auth.avatars a
+        WHERE a.id = $1
+        "#,
+    )
+    .bind(avatar_id)
+    .bind(current_emotion)
+    .fetch_optional(executor)
+    .await?;
+
+    let Some(avatar) = avatar else {
+        return Ok(None);
+    };
+
+    // Collect all inventory UUIDs
+    let mut uuids: Vec<Uuid> = Vec::new();
+    collect_uuids(
+        &mut uuids,
+        &[
+            avatar.l_skin_0, avatar.l_skin_1, avatar.l_skin_2,
+            avatar.l_skin_3, avatar.l_skin_4, avatar.l_skin_5,
+            avatar.l_skin_6, avatar.l_skin_7, avatar.l_skin_8,
+        ],
+    );
+    collect_uuids(
+        &mut uuids,
+        &[
+            avatar.l_clothes_0, avatar.l_clothes_1, avatar.l_clothes_2,
+            avatar.l_clothes_3, avatar.l_clothes_4, avatar.l_clothes_5,
+            avatar.l_clothes_6, avatar.l_clothes_7, avatar.l_clothes_8,
+        ],
+    );
+    collect_uuids(
+        &mut uuids,
+        &[
+            avatar.l_accessories_0, avatar.l_accessories_1, avatar.l_accessories_2,
+            avatar.l_accessories_3, avatar.l_accessories_4, avatar.l_accessories_5,
+            avatar.l_accessories_6, avatar.l_accessories_7, avatar.l_accessories_8,
+        ],
+    );
+
+    // Get emotion slots for current emotion
+    let emotion_slots: [Option<Uuid>; 9] = match current_emotion {
+        EmotionState::Neutral => [avatar.e_neutral_0, avatar.e_neutral_1, avatar.e_neutral_2,
+              avatar.e_neutral_3, avatar.e_neutral_4, avatar.e_neutral_5,
+              avatar.e_neutral_6, avatar.e_neutral_7, avatar.e_neutral_8],
+        EmotionState::Happy => [avatar.e_happy_0, avatar.e_happy_1, avatar.e_happy_2,
+              avatar.e_happy_3, avatar.e_happy_4, avatar.e_happy_5,
+              avatar.e_happy_6, avatar.e_happy_7, avatar.e_happy_8],
+        EmotionState::Sad => [avatar.e_sad_0, avatar.e_sad_1, avatar.e_sad_2,
+              avatar.e_sad_3, avatar.e_sad_4, avatar.e_sad_5,
+              avatar.e_sad_6, avatar.e_sad_7, avatar.e_sad_8],
+        EmotionState::Angry => [avatar.e_angry_0, avatar.e_angry_1, avatar.e_angry_2,
+              avatar.e_angry_3, avatar.e_angry_4, avatar.e_angry_5,
+              avatar.e_angry_6, avatar.e_angry_7, avatar.e_angry_8],
+        EmotionState::Surprised => [avatar.e_surprised_0, avatar.e_surprised_1, avatar.e_surprised_2,
+              avatar.e_surprised_3, avatar.e_surprised_4, avatar.e_surprised_5,
+              avatar.e_surprised_6, avatar.e_surprised_7, avatar.e_surprised_8],
+        EmotionState::Thinking => [avatar.e_thinking_0, avatar.e_thinking_1, avatar.e_thinking_2,
+              avatar.e_thinking_3, avatar.e_thinking_4, avatar.e_thinking_5,
+              avatar.e_thinking_6, avatar.e_thinking_7, avatar.e_thinking_8],
+        EmotionState::Laughing => [avatar.e_laughing_0, avatar.e_laughing_1, avatar.e_laughing_2,
+              avatar.e_laughing_3, avatar.e_laughing_4, avatar.e_laughing_5,
+              avatar.e_laughing_6, avatar.e_laughing_7, avatar.e_laughing_8],
+        EmotionState::Crying => [avatar.e_crying_0, avatar.e_crying_1, avatar.e_crying_2,
+              avatar.e_crying_3, avatar.e_crying_4, avatar.e_crying_5,
+              avatar.e_crying_6, avatar.e_crying_7, avatar.e_crying_8],
+        EmotionState::Love => [avatar.e_love_0, avatar.e_love_1, avatar.e_love_2,
+              avatar.e_love_3, avatar.e_love_4, avatar.e_love_5,
+              avatar.e_love_6, avatar.e_love_7, avatar.e_love_8],
+        EmotionState::Confused => [avatar.e_confused_0, avatar.e_confused_1, avatar.e_confused_2,
+              avatar.e_confused_3, avatar.e_confused_4, avatar.e_confused_5,
+              avatar.e_confused_6, avatar.e_confused_7, avatar.e_confused_8],
+        EmotionState::Sleeping => [avatar.e_sleeping_0, avatar.e_sleeping_1, avatar.e_sleeping_2,
+               avatar.e_sleeping_3, avatar.e_sleeping_4, avatar.e_sleeping_5,
+               avatar.e_sleeping_6, avatar.e_sleeping_7, avatar.e_sleeping_8],
+        EmotionState::Wink => [avatar.e_wink_0, avatar.e_wink_1, avatar.e_wink_2,
+               avatar.e_wink_3, avatar.e_wink_4, avatar.e_wink_5,
+               avatar.e_wink_6, avatar.e_wink_7, avatar.e_wink_8],
+    };
+    collect_uuids(&mut uuids, &emotion_slots);
+
+    // Bulk resolve inventory UUIDs to asset paths
+    let paths: HashMap<Uuid, String> = if uuids.is_empty() {
+        HashMap::new()
+    } else {
+        sqlx::query_as::<_, (Uuid, String)>(
+            "SELECT id, prop_asset_path FROM auth.inventory WHERE id = ANY($1)",
+        )
+        .bind(&uuids)
+        .fetch_all(executor)
+        .await?
+        .into_iter()
+        .collect()
+    };
+
+    let get_path = |id: Option<Uuid>| -> Option<String> {
+        id.and_then(|id| paths.get(&id).cloned())
+    };
+
+    Ok(Some(crate::models::AvatarRenderData {
+        avatar_id,
+        current_emotion,
+        skin_layer: [
+            get_path(avatar.l_skin_0), get_path(avatar.l_skin_1), get_path(avatar.l_skin_2),
+            get_path(avatar.l_skin_3), get_path(avatar.l_skin_4), get_path(avatar.l_skin_5),
+            get_path(avatar.l_skin_6), get_path(avatar.l_skin_7), get_path(avatar.l_skin_8),
+        ],
+        clothes_layer: [
+            get_path(avatar.l_clothes_0), get_path(avatar.l_clothes_1), get_path(avatar.l_clothes_2),
+            get_path(avatar.l_clothes_3), get_path(avatar.l_clothes_4), get_path(avatar.l_clothes_5),
+            get_path(avatar.l_clothes_6), get_path(avatar.l_clothes_7), get_path(avatar.l_clothes_8),
+        ],
+        accessories_layer: [
+            get_path(avatar.l_accessories_0), get_path(avatar.l_accessories_1), get_path(avatar.l_accessories_2),
+            get_path(avatar.l_accessories_3), get_path(avatar.l_accessories_4), get_path(avatar.l_accessories_5),
+            get_path(avatar.l_accessories_6), get_path(avatar.l_accessories_7), get_path(avatar.l_accessories_8),
+        ],
+        emotion_layer: [
+            get_path(emotion_slots[0]), get_path(emotion_slots[1]), get_path(emotion_slots[2]),
+            get_path(emotion_slots[3]), get_path(emotion_slots[4]), get_path(emotion_slots[5]),
+            get_path(emotion_slots[6]), get_path(emotion_slots[7]), get_path(emotion_slots[8]),
+        ],
+    }))
+}
+
+/// Get the realm default avatar ID based on gender and age preferences.
+async fn get_realm_default_avatar_id<'e>(
+    executor: impl PgExecutor<'e>,
+    realm_id: Uuid,
+    gender: crate::models::GenderPreference,
+    age: crate::models::AgeCategory,
+) -> Result<Option<Uuid>, AppError> {
+    use crate::models::{AgeCategory, GenderPreference};
+
+    // Build column name based on gender and age
+    let column = match (gender, age) {
+        (GenderPreference::GenderNeutral, AgeCategory::Child) => "default_avatar_neutral_child",
+        (GenderPreference::GenderNeutral, AgeCategory::Adult) => "default_avatar_neutral_adult",
+        (GenderPreference::GenderMale, AgeCategory::Child) => "default_avatar_male_child",
+        (GenderPreference::GenderMale, AgeCategory::Adult) => "default_avatar_male_adult",
+        (GenderPreference::GenderFemale, AgeCategory::Child) => "default_avatar_female_child",
+        (GenderPreference::GenderFemale, AgeCategory::Adult) => "default_avatar_female_adult",
+    };
+
+    let query = format!(
+        "SELECT {} FROM realm.realms WHERE id = $1",
+        column
+    );
+
+    let result: Option<(Option<Uuid>,)> = sqlx::query_as(&query)
+        .bind(realm_id)
+        .fetch_optional(executor)
+        .await?;
+
+    Ok(result.and_then(|r| r.0))
+}
+
+/// Get the server default avatar ID based on gender and age preferences.
+async fn get_server_default_avatar_id<'e>(
+    executor: impl PgExecutor<'e>,
+    gender: crate::models::GenderPreference,
+    age: crate::models::AgeCategory,
+) -> Result<Option<Uuid>, AppError> {
+    use crate::models::{AgeCategory, GenderPreference};
+
+    // Build column name based on gender and age
+    let column = match (gender, age) {
+        (GenderPreference::GenderNeutral, AgeCategory::Child) => "default_avatar_neutral_child",
+        (GenderPreference::GenderNeutral, AgeCategory::Adult) => "default_avatar_neutral_adult",
+        (GenderPreference::GenderMale, AgeCategory::Child) => "default_avatar_male_child",
+        (GenderPreference::GenderMale, AgeCategory::Adult) => "default_avatar_male_adult",
+        (GenderPreference::GenderFemale, AgeCategory::Child) => "default_avatar_female_child",
+        (GenderPreference::GenderFemale, AgeCategory::Adult) => "default_avatar_female_adult",
+    };
+
+    let query = format!(
+        "SELECT {} FROM server.config WHERE id = '00000000-0000-0000-0000-000000000001'",
+        column
+    );
+
+    let result: Option<(Option<Uuid>,)> = sqlx::query_as(&query)
+        .fetch_optional(executor)
+        .await?;
+
+    Ok(result.and_then(|r| r.0))
+}
+
+/// Select a server avatar for a user in a realm.
+/// This updates the selected_server_avatar_id in active_avatars.
+/// Uses UPSERT to create the record if it doesn't exist.
+pub async fn select_server_avatar<'e>(
+    executor: impl PgExecutor<'e>,
+    user_id: Uuid,
+    realm_id: Uuid,
+    server_avatar_id: Uuid,
+) -> Result<(), AppError> {
+    sqlx::query(
+        r#"
+        INSERT INTO auth.active_avatars (user_id, realm_id, selected_server_avatar_id, updated_at)
+        VALUES ($1, $2, $3, now())
+        ON CONFLICT (user_id, realm_id) DO UPDATE
+        SET selected_server_avatar_id = EXCLUDED.selected_server_avatar_id,
+            updated_at = EXCLUDED.updated_at
+        "#,
+    )
+    .bind(user_id)
+    .bind(realm_id)
+    .bind(server_avatar_id)
+    .execute(executor)
+    .await?;
+
+    Ok(())
+}
+
+/// Select a realm avatar for a user in a realm.
+/// This updates the selected_realm_avatar_id in active_avatars.
+/// Uses UPSERT to create the record if it doesn't exist.
+pub async fn select_realm_avatar<'e>(
+    executor: impl PgExecutor<'e>,
+    user_id: Uuid,
+    realm_id: Uuid,
+    realm_avatar_id: Uuid,
+) -> Result<(), AppError> {
+    sqlx::query(
+        r#"
+        INSERT INTO auth.active_avatars (user_id, realm_id, selected_realm_avatar_id, updated_at)
+        VALUES ($1, $2, $3, now())
+        ON CONFLICT (user_id, realm_id) DO UPDATE
+        SET selected_realm_avatar_id = EXCLUDED.selected_realm_avatar_id,
+            updated_at = EXCLUDED.updated_at
+        "#,
+    )
+    .bind(user_id)
+    .bind(realm_id)
+    .bind(realm_avatar_id)
+    .execute(executor)
+    .await?;
+
+    Ok(())
+}
+
+/// Clear avatar selection for a user in a realm.
+/// Clears both selected_server_avatar_id and selected_realm_avatar_id.
+/// If no record exists, this is a no-op (clearing nothing is success).
+pub async fn clear_avatar_selection<'e>(
+    executor: impl PgExecutor<'e>,
+    user_id: Uuid,
+    realm_id: Uuid,
+) -> Result<(), AppError> {
+    sqlx::query(
+        r#"
+        UPDATE auth.active_avatars
+        SET
+            selected_server_avatar_id = NULL,
+            selected_realm_avatar_id = NULL,
+            updated_at = now()
+        WHERE user_id = $1 AND realm_id = $2
+        "#,
+    )
+    .bind(user_id)
+    .bind(realm_id)
+    .execute(executor)
+    .await?;
+
+    // No error if record doesn't exist - clearing nothing is success
+    Ok(())
+}