//! Avatar-related database queries.

use std::collections::HashMap;

use sqlx::{PgExecutor, PgPool, postgres::PgConnection};
use uuid::Uuid;

use crate::models::{ActiveAvatar, AvatarWithPaths, EmotionAvailability, EmotionState};
use chattyness_error::AppError;

/// Get the active avatar for a user in a realm.
pub async fn get_active_avatar<'e>(
    executor: impl PgExecutor<'e>,
    user_id: Uuid,
    realm_id: Uuid,
) -> Result<Option<ActiveAvatar>, AppError> {
    let avatar = sqlx::query_as::<_, ActiveAvatar>(
        r#"
        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
        "#,
    )
    .bind(user_id)
    .bind(realm_id)
    .fetch_optional(executor)
    .await?;

    Ok(avatar)
}

/// 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>(
    conn: &mut PgConnection,
    pool: &PgPool,
    user_id: Uuid,
    realm_id: Uuid,
    emotion: EmotionState,
) -> Result<[Option<String>; 9], AppError> {
    // First, update the emotion in active_avatars (uses RLS connection)
    let update_result = sqlx::query(
        r#"
        UPDATE auth.active_avatars
        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.to_string())
    .execute(&mut *conn)
    .await?;

    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])
        }
    }
}


/// Get emotion availability for a user's avatar in a realm.
///
/// Returns which emotions have assets configured (any of positions 0-8 non-null)
/// and the center position (4) preview path for each emotion.
pub async fn get_emotion_availability<'e>(
    executor: impl PgExecutor<'e>,
    user_id: Uuid,
    realm_id: Uuid,
) -> Result<EmotionAvailability, AppError> {
    let row = sqlx::query_as::<_, EmotionAvailabilityRow>(
        r#"
        SELECT
            -- Neutral (0): check if any position has asset
            (a.e_neutral_0 IS NOT NULL OR a.e_neutral_1 IS NOT NULL OR a.e_neutral_2 IS NOT NULL OR
             a.e_neutral_3 IS NOT NULL OR a.e_neutral_4 IS NOT NULL OR a.e_neutral_5 IS NOT NULL OR
             a.e_neutral_6 IS NOT NULL OR a.e_neutral_7 IS NOT NULL OR a.e_neutral_8 IS NOT NULL) as avail_0,
            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.e_neutral_4) as preview_0,

            -- Happy (1)
            (a.e_happy_0 IS NOT NULL OR a.e_happy_1 IS NOT NULL OR a.e_happy_2 IS NOT NULL OR
             a.e_happy_3 IS NOT NULL OR a.e_happy_4 IS NOT NULL OR a.e_happy_5 IS NOT NULL OR
             a.e_happy_6 IS NOT NULL OR a.e_happy_7 IS NOT NULL OR a.e_happy_8 IS NOT NULL) as avail_1,
            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.e_happy_4) as preview_1,

            -- Sad (2)
            (a.e_sad_0 IS NOT NULL OR a.e_sad_1 IS NOT NULL OR a.e_sad_2 IS NOT NULL OR
             a.e_sad_3 IS NOT NULL OR a.e_sad_4 IS NOT NULL OR a.e_sad_5 IS NOT NULL OR
             a.e_sad_6 IS NOT NULL OR a.e_sad_7 IS NOT NULL OR a.e_sad_8 IS NOT NULL) as avail_2,
            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.e_sad_4) as preview_2,

            -- Angry (3)
            (a.e_angry_0 IS NOT NULL OR a.e_angry_1 IS NOT NULL OR a.e_angry_2 IS NOT NULL OR
             a.e_angry_3 IS NOT NULL OR a.e_angry_4 IS NOT NULL OR a.e_angry_5 IS NOT NULL OR
             a.e_angry_6 IS NOT NULL OR a.e_angry_7 IS NOT NULL OR a.e_angry_8 IS NOT NULL) as avail_3,
            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.e_angry_4) as preview_3,

            -- Surprised (4)
            (a.e_surprised_0 IS NOT NULL OR a.e_surprised_1 IS NOT NULL OR a.e_surprised_2 IS NOT NULL OR
             a.e_surprised_3 IS NOT NULL OR a.e_surprised_4 IS NOT NULL OR a.e_surprised_5 IS NOT NULL OR
             a.e_surprised_6 IS NOT NULL OR a.e_surprised_7 IS NOT NULL OR a.e_surprised_8 IS NOT NULL) as avail_4,
            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.e_surprised_4) as preview_4,

            -- Thinking (5)
            (a.e_thinking_0 IS NOT NULL OR a.e_thinking_1 IS NOT NULL OR a.e_thinking_2 IS NOT NULL OR
             a.e_thinking_3 IS NOT NULL OR a.e_thinking_4 IS NOT NULL OR a.e_thinking_5 IS NOT NULL OR
             a.e_thinking_6 IS NOT NULL OR a.e_thinking_7 IS NOT NULL OR a.e_thinking_8 IS NOT NULL) as avail_5,
            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.e_thinking_4) as preview_5,

            -- Laughing (6)
            (a.e_laughing_0 IS NOT NULL OR a.e_laughing_1 IS NOT NULL OR a.e_laughing_2 IS NOT NULL OR
             a.e_laughing_3 IS NOT NULL OR a.e_laughing_4 IS NOT NULL OR a.e_laughing_5 IS NOT NULL OR
             a.e_laughing_6 IS NOT NULL OR a.e_laughing_7 IS NOT NULL OR a.e_laughing_8 IS NOT NULL) as avail_6,
            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.e_laughing_4) as preview_6,

            -- Crying (7)
            (a.e_crying_0 IS NOT NULL OR a.e_crying_1 IS NOT NULL OR a.e_crying_2 IS NOT NULL OR
             a.e_crying_3 IS NOT NULL OR a.e_crying_4 IS NOT NULL OR a.e_crying_5 IS NOT NULL OR
             a.e_crying_6 IS NOT NULL OR a.e_crying_7 IS NOT NULL OR a.e_crying_8 IS NOT NULL) as avail_7,
            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.e_crying_4) as preview_7,

            -- Love (8)
            (a.e_love_0 IS NOT NULL OR a.e_love_1 IS NOT NULL OR a.e_love_2 IS NOT NULL OR
             a.e_love_3 IS NOT NULL OR a.e_love_4 IS NOT NULL OR a.e_love_5 IS NOT NULL OR
             a.e_love_6 IS NOT NULL OR a.e_love_7 IS NOT NULL OR a.e_love_8 IS NOT NULL) as avail_8,
            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.e_love_4) as preview_8,

            -- Confused (9)
            (a.e_confused_0 IS NOT NULL OR a.e_confused_1 IS NOT NULL OR a.e_confused_2 IS NOT NULL OR
             a.e_confused_3 IS NOT NULL OR a.e_confused_4 IS NOT NULL OR a.e_confused_5 IS NOT NULL OR
             a.e_confused_6 IS NOT NULL OR a.e_confused_7 IS NOT NULL OR a.e_confused_8 IS NOT NULL) as avail_9,
            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.e_confused_4) as preview_9,

            -- Sleeping (10)
            (a.e_sleeping_0 IS NOT NULL OR a.e_sleeping_1 IS NOT NULL OR a.e_sleeping_2 IS NOT NULL OR
             a.e_sleeping_3 IS NOT NULL OR a.e_sleeping_4 IS NOT NULL OR a.e_sleeping_5 IS NOT NULL OR
             a.e_sleeping_6 IS NOT NULL OR a.e_sleeping_7 IS NOT NULL OR a.e_sleeping_8 IS NOT NULL) as avail_10,
            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.e_sleeping_4) as preview_10,

            -- Wink (11)
            (a.e_wink_0 IS NOT NULL OR a.e_wink_1 IS NOT NULL OR a.e_wink_2 IS NOT NULL OR
             a.e_wink_3 IS NOT NULL OR a.e_wink_4 IS NOT NULL OR a.e_wink_5 IS NOT NULL OR
             a.e_wink_6 IS NOT NULL OR a.e_wink_7 IS NOT NULL OR a.e_wink_8 IS NOT NULL) as avail_11,
            (SELECT prop_asset_path FROM auth.inventory WHERE id = a.e_wink_4) as preview_11

        FROM auth.active_avatars aa
        JOIN auth.avatars a ON aa.avatar_id = a.id
        WHERE aa.user_id = $1 AND aa.realm_id = $2
        "#,
    )
    .bind(user_id)
    .bind(realm_id)
    .fetch_optional(executor)
    .await?;

    match row {
        Some(r) => Ok(r.into()),
        None => Ok(EmotionAvailability::default()),
    }
}

/// Row type for emotion availability query.
#[derive(Debug, sqlx::FromRow)]
struct EmotionAvailabilityRow {
    avail_0: Option<bool>,
    preview_0: Option<String>,
    avail_1: Option<bool>,
    preview_1: Option<String>,
    avail_2: Option<bool>,
    preview_2: Option<String>,
    avail_3: Option<bool>,
    preview_3: Option<String>,
    avail_4: Option<bool>,
    preview_4: Option<String>,
    avail_5: Option<bool>,
    preview_5: Option<String>,
    avail_6: Option<bool>,
    preview_6: Option<String>,
    avail_7: Option<bool>,
    preview_7: Option<String>,
    avail_8: Option<bool>,
    preview_8: Option<String>,
    avail_9: Option<bool>,
    preview_9: Option<String>,
    avail_10: Option<bool>,
    preview_10: Option<String>,
    avail_11: Option<bool>,
    preview_11: Option<String>,
}

impl From<EmotionAvailabilityRow> for EmotionAvailability {
    fn from(row: EmotionAvailabilityRow) -> Self {
        Self {
            available: [
                row.avail_0.unwrap_or(false),
                row.avail_1.unwrap_or(false),
                row.avail_2.unwrap_or(false),
                row.avail_3.unwrap_or(false),
                row.avail_4.unwrap_or(false),
                row.avail_5.unwrap_or(false),
                row.avail_6.unwrap_or(false),
                row.avail_7.unwrap_or(false),
                row.avail_8.unwrap_or(false),
                row.avail_9.unwrap_or(false),
                row.avail_10.unwrap_or(false),
                row.avail_11.unwrap_or(false),
            ],
            preview_paths: [
                row.preview_0,
                row.preview_1,
                row.preview_2,
                row.preview_3,
                row.preview_4,
                row.preview_5,
                row.preview_6,
                row.preview_7,
                row.preview_8,
                row.preview_9,
                row.preview_10,
                row.preview_11,
            ],
        }
    }
}

/// Get the full avatar with all inventory UUIDs resolved to asset paths.
///
/// This function uses two queries:
/// 1. Fetch the avatar row with all 135 UUID slots
/// 2. Bulk resolve all UUIDs to asset paths with a single inventory query
///
/// The result enables client-side emotion availability computation and rendering.
pub async fn get_avatar_with_paths(
    pool: &PgPool,
    user_id: Uuid,
    realm_id: Uuid,
) -> Result<Option<AvatarWithPaths>, AppError> {
    // Query 1: Get the avatar row with current_emotion from active_avatars
    let avatar_row = sqlx::query_as::<_, AvatarWithEmotion>(
        r#"
        SELECT
            a.*,
            aa.current_emotion
        FROM auth.active_avatars aa
        JOIN auth.avatars a ON aa.avatar_id = a.id
        WHERE aa.user_id = $1 AND aa.realm_id = $2
        "#,
    )
    .bind(user_id)
    .bind(realm_id)
    .fetch_optional(pool)
    .await?;

    let Some(avatar) = avatar_row else {
        return Ok(None);
    };

    // Collect all non-null UUIDs from the avatar slots
    let mut uuids: Vec<Uuid> = Vec::new();

    // Content layers
    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,
        ],
    );

    // Emotion layers (12 emotions × 9 positions)
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );

    // Query 2: Bulk resolve all UUIDs to 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(pool)
        .await?
        .into_iter()
        .collect()
    };

    // Build the AvatarWithPaths
    let resolve =
        |uuid: Option<Uuid>| -> Option<String> { uuid.and_then(|id| paths.get(&id).cloned()) };

    // Check if any UUID in the array is non-null (emotion is available)
    let has_any = |slots: &[Option<Uuid>]| -> bool { slots.iter().any(|u| u.is_some()) };

    // Compute emotions_available from UUID presence (not path resolution)
    let emotions_available = [
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
    ];

    Ok(Some(AvatarWithPaths {
        avatar_id: avatar.id,
        current_emotion: avatar.current_emotion,
        skin_layer: [
            resolve(avatar.l_skin_0),
            resolve(avatar.l_skin_1),
            resolve(avatar.l_skin_2),
            resolve(avatar.l_skin_3),
            resolve(avatar.l_skin_4),
            resolve(avatar.l_skin_5),
            resolve(avatar.l_skin_6),
            resolve(avatar.l_skin_7),
            resolve(avatar.l_skin_8),
        ],
        clothes_layer: [
            resolve(avatar.l_clothes_0),
            resolve(avatar.l_clothes_1),
            resolve(avatar.l_clothes_2),
            resolve(avatar.l_clothes_3),
            resolve(avatar.l_clothes_4),
            resolve(avatar.l_clothes_5),
            resolve(avatar.l_clothes_6),
            resolve(avatar.l_clothes_7),
            resolve(avatar.l_clothes_8),
        ],
        accessories_layer: [
            resolve(avatar.l_accessories_0),
            resolve(avatar.l_accessories_1),
            resolve(avatar.l_accessories_2),
            resolve(avatar.l_accessories_3),
            resolve(avatar.l_accessories_4),
            resolve(avatar.l_accessories_5),
            resolve(avatar.l_accessories_6),
            resolve(avatar.l_accessories_7),
            resolve(avatar.l_accessories_8),
        ],
        emotions: [
            // Neutral (0)
            [
                resolve(avatar.e_neutral_0),
                resolve(avatar.e_neutral_1),
                resolve(avatar.e_neutral_2),
                resolve(avatar.e_neutral_3),
                resolve(avatar.e_neutral_4),
                resolve(avatar.e_neutral_5),
                resolve(avatar.e_neutral_6),
                resolve(avatar.e_neutral_7),
                resolve(avatar.e_neutral_8),
            ],
            // Happy (1)
            [
                resolve(avatar.e_happy_0),
                resolve(avatar.e_happy_1),
                resolve(avatar.e_happy_2),
                resolve(avatar.e_happy_3),
                resolve(avatar.e_happy_4),
                resolve(avatar.e_happy_5),
                resolve(avatar.e_happy_6),
                resolve(avatar.e_happy_7),
                resolve(avatar.e_happy_8),
            ],
            // Sad (2)
            [
                resolve(avatar.e_sad_0),
                resolve(avatar.e_sad_1),
                resolve(avatar.e_sad_2),
                resolve(avatar.e_sad_3),
                resolve(avatar.e_sad_4),
                resolve(avatar.e_sad_5),
                resolve(avatar.e_sad_6),
                resolve(avatar.e_sad_7),
                resolve(avatar.e_sad_8),
            ],
            // Angry (3)
            [
                resolve(avatar.e_angry_0),
                resolve(avatar.e_angry_1),
                resolve(avatar.e_angry_2),
                resolve(avatar.e_angry_3),
                resolve(avatar.e_angry_4),
                resolve(avatar.e_angry_5),
                resolve(avatar.e_angry_6),
                resolve(avatar.e_angry_7),
                resolve(avatar.e_angry_8),
            ],
            // Surprised (4)
            [
                resolve(avatar.e_surprised_0),
                resolve(avatar.e_surprised_1),
                resolve(avatar.e_surprised_2),
                resolve(avatar.e_surprised_3),
                resolve(avatar.e_surprised_4),
                resolve(avatar.e_surprised_5),
                resolve(avatar.e_surprised_6),
                resolve(avatar.e_surprised_7),
                resolve(avatar.e_surprised_8),
            ],
            // Thinking (5)
            [
                resolve(avatar.e_thinking_0),
                resolve(avatar.e_thinking_1),
                resolve(avatar.e_thinking_2),
                resolve(avatar.e_thinking_3),
                resolve(avatar.e_thinking_4),
                resolve(avatar.e_thinking_5),
                resolve(avatar.e_thinking_6),
                resolve(avatar.e_thinking_7),
                resolve(avatar.e_thinking_8),
            ],
            // Laughing (6)
            [
                resolve(avatar.e_laughing_0),
                resolve(avatar.e_laughing_1),
                resolve(avatar.e_laughing_2),
                resolve(avatar.e_laughing_3),
                resolve(avatar.e_laughing_4),
                resolve(avatar.e_laughing_5),
                resolve(avatar.e_laughing_6),
                resolve(avatar.e_laughing_7),
                resolve(avatar.e_laughing_8),
            ],
            // Crying (7)
            [
                resolve(avatar.e_crying_0),
                resolve(avatar.e_crying_1),
                resolve(avatar.e_crying_2),
                resolve(avatar.e_crying_3),
                resolve(avatar.e_crying_4),
                resolve(avatar.e_crying_5),
                resolve(avatar.e_crying_6),
                resolve(avatar.e_crying_7),
                resolve(avatar.e_crying_8),
            ],
            // Love (8)
            [
                resolve(avatar.e_love_0),
                resolve(avatar.e_love_1),
                resolve(avatar.e_love_2),
                resolve(avatar.e_love_3),
                resolve(avatar.e_love_4),
                resolve(avatar.e_love_5),
                resolve(avatar.e_love_6),
                resolve(avatar.e_love_7),
                resolve(avatar.e_love_8),
            ],
            // Confused (9)
            [
                resolve(avatar.e_confused_0),
                resolve(avatar.e_confused_1),
                resolve(avatar.e_confused_2),
                resolve(avatar.e_confused_3),
                resolve(avatar.e_confused_4),
                resolve(avatar.e_confused_5),
                resolve(avatar.e_confused_6),
                resolve(avatar.e_confused_7),
                resolve(avatar.e_confused_8),
            ],
            // Sleeping (10)
            [
                resolve(avatar.e_sleeping_0),
                resolve(avatar.e_sleeping_1),
                resolve(avatar.e_sleeping_2),
                resolve(avatar.e_sleeping_3),
                resolve(avatar.e_sleeping_4),
                resolve(avatar.e_sleeping_5),
                resolve(avatar.e_sleeping_6),
                resolve(avatar.e_sleeping_7),
                resolve(avatar.e_sleeping_8),
            ],
            // Wink (11)
            [
                resolve(avatar.e_wink_0),
                resolve(avatar.e_wink_1),
                resolve(avatar.e_wink_2),
                resolve(avatar.e_wink_3),
                resolve(avatar.e_wink_4),
                resolve(avatar.e_wink_5),
                resolve(avatar.e_wink_6),
                resolve(avatar.e_wink_7),
                resolve(avatar.e_wink_8),
            ],
        ],
        emotions_available,
    }))
}

/// Get full avatar with all inventory UUIDs resolved to asset paths (connection variant).
///
/// This variant accepts a mutable connection reference for use with RLS-enabled connections.
pub async fn get_avatar_with_paths_conn(
    conn: &mut PgConnection,
    user_id: Uuid,
    realm_id: Uuid,
) -> Result<Option<AvatarWithPaths>, AppError> {
    // Query 1: Get the avatar row with current_emotion from active_avatars
    let avatar_row = sqlx::query_as::<_, AvatarWithEmotion>(
        r#"
        SELECT
            a.*,
            aa.current_emotion
        FROM auth.active_avatars aa
        JOIN auth.avatars a ON aa.avatar_id = a.id
        WHERE aa.user_id = $1 AND aa.realm_id = $2
        "#,
    )
    .bind(user_id)
    .bind(realm_id)
    .fetch_optional(&mut *conn)
    .await?;

    let Some(avatar) = avatar_row else {
        return Ok(None);
    };

    // Collect all non-null UUIDs from the avatar slots
    let mut uuids: Vec<Uuid> = Vec::new();

    // Content layers
    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,
        ],
    );

    // Emotion layers (12 emotions × 9 positions)
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );
    collect_uuids(
        &mut uuids,
        &[
            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,
        ],
    );

    // Query 2: Bulk resolve all UUIDs to 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(&mut *conn)
        .await?
        .into_iter()
        .collect()
    };

    // Build the AvatarWithPaths
    let resolve =
        |uuid: Option<Uuid>| -> Option<String> { uuid.and_then(|id| paths.get(&id).cloned()) };

    // Check if any UUID in the array is non-null (emotion is available)
    let has_any = |slots: &[Option<Uuid>]| -> bool { slots.iter().any(|u| u.is_some()) };

    // Compute emotions_available from UUID presence (not path resolution)
    let emotions_available = [
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
        has_any(&[
            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,
        ]),
    ];

    Ok(Some(AvatarWithPaths {
        avatar_id: avatar.id,
        current_emotion: avatar.current_emotion,
        skin_layer: [
            resolve(avatar.l_skin_0),
            resolve(avatar.l_skin_1),
            resolve(avatar.l_skin_2),
            resolve(avatar.l_skin_3),
            resolve(avatar.l_skin_4),
            resolve(avatar.l_skin_5),
            resolve(avatar.l_skin_6),
            resolve(avatar.l_skin_7),
            resolve(avatar.l_skin_8),
        ],
        clothes_layer: [
            resolve(avatar.l_clothes_0),
            resolve(avatar.l_clothes_1),
            resolve(avatar.l_clothes_2),
            resolve(avatar.l_clothes_3),
            resolve(avatar.l_clothes_4),
            resolve(avatar.l_clothes_5),
            resolve(avatar.l_clothes_6),
            resolve(avatar.l_clothes_7),
            resolve(avatar.l_clothes_8),
        ],
        accessories_layer: [
            resolve(avatar.l_accessories_0),
            resolve(avatar.l_accessories_1),
            resolve(avatar.l_accessories_2),
            resolve(avatar.l_accessories_3),
            resolve(avatar.l_accessories_4),
            resolve(avatar.l_accessories_5),
            resolve(avatar.l_accessories_6),
            resolve(avatar.l_accessories_7),
            resolve(avatar.l_accessories_8),
        ],
        emotions: [
            // Neutral (0)
            [
                resolve(avatar.e_neutral_0),
                resolve(avatar.e_neutral_1),
                resolve(avatar.e_neutral_2),
                resolve(avatar.e_neutral_3),
                resolve(avatar.e_neutral_4),
                resolve(avatar.e_neutral_5),
                resolve(avatar.e_neutral_6),
                resolve(avatar.e_neutral_7),
                resolve(avatar.e_neutral_8),
            ],
            // Happy (1)
            [
                resolve(avatar.e_happy_0),
                resolve(avatar.e_happy_1),
                resolve(avatar.e_happy_2),
                resolve(avatar.e_happy_3),
                resolve(avatar.e_happy_4),
                resolve(avatar.e_happy_5),
                resolve(avatar.e_happy_6),
                resolve(avatar.e_happy_7),
                resolve(avatar.e_happy_8),
            ],
            // Sad (2)
            [
                resolve(avatar.e_sad_0),
                resolve(avatar.e_sad_1),
                resolve(avatar.e_sad_2),
                resolve(avatar.e_sad_3),
                resolve(avatar.e_sad_4),
                resolve(avatar.e_sad_5),
                resolve(avatar.e_sad_6),
                resolve(avatar.e_sad_7),
                resolve(avatar.e_sad_8),
            ],
            // Angry (3)
            [
                resolve(avatar.e_angry_0),
                resolve(avatar.e_angry_1),
                resolve(avatar.e_angry_2),
                resolve(avatar.e_angry_3),
                resolve(avatar.e_angry_4),
                resolve(avatar.e_angry_5),
                resolve(avatar.e_angry_6),
                resolve(avatar.e_angry_7),
                resolve(avatar.e_angry_8),
            ],
            // Surprised (4)
            [
                resolve(avatar.e_surprised_0),
                resolve(avatar.e_surprised_1),
                resolve(avatar.e_surprised_2),
                resolve(avatar.e_surprised_3),
                resolve(avatar.e_surprised_4),
                resolve(avatar.e_surprised_5),
                resolve(avatar.e_surprised_6),
                resolve(avatar.e_surprised_7),
                resolve(avatar.e_surprised_8),
            ],
            // Thinking (5)
            [
                resolve(avatar.e_thinking_0),
                resolve(avatar.e_thinking_1),
                resolve(avatar.e_thinking_2),
                resolve(avatar.e_thinking_3),
                resolve(avatar.e_thinking_4),
                resolve(avatar.e_thinking_5),
                resolve(avatar.e_thinking_6),
                resolve(avatar.e_thinking_7),
                resolve(avatar.e_thinking_8),
            ],
            // Laughing (6)
            [
                resolve(avatar.e_laughing_0),
                resolve(avatar.e_laughing_1),
                resolve(avatar.e_laughing_2),
                resolve(avatar.e_laughing_3),
                resolve(avatar.e_laughing_4),
                resolve(avatar.e_laughing_5),
                resolve(avatar.e_laughing_6),
                resolve(avatar.e_laughing_7),
                resolve(avatar.e_laughing_8),
            ],
            // Crying (7)
            [
                resolve(avatar.e_crying_0),
                resolve(avatar.e_crying_1),
                resolve(avatar.e_crying_2),
                resolve(avatar.e_crying_3),
                resolve(avatar.e_crying_4),
                resolve(avatar.e_crying_5),
                resolve(avatar.e_crying_6),
                resolve(avatar.e_crying_7),
                resolve(avatar.e_crying_8),
            ],
            // Love (8)
            [
                resolve(avatar.e_love_0),
                resolve(avatar.e_love_1),
                resolve(avatar.e_love_2),
                resolve(avatar.e_love_3),
                resolve(avatar.e_love_4),
                resolve(avatar.e_love_5),
                resolve(avatar.e_love_6),
                resolve(avatar.e_love_7),
                resolve(avatar.e_love_8),
            ],
            // Confused (9)
            [
                resolve(avatar.e_confused_0),
                resolve(avatar.e_confused_1),
                resolve(avatar.e_confused_2),
                resolve(avatar.e_confused_3),
                resolve(avatar.e_confused_4),
                resolve(avatar.e_confused_5),
                resolve(avatar.e_confused_6),
                resolve(avatar.e_confused_7),
                resolve(avatar.e_confused_8),
            ],
            // Sleeping (10)
            [
                resolve(avatar.e_sleeping_0),
                resolve(avatar.e_sleeping_1),
                resolve(avatar.e_sleeping_2),
                resolve(avatar.e_sleeping_3),
                resolve(avatar.e_sleeping_4),
                resolve(avatar.e_sleeping_5),
                resolve(avatar.e_sleeping_6),
                resolve(avatar.e_sleeping_7),
                resolve(avatar.e_sleeping_8),
            ],
            // Wink (11)
            [
                resolve(avatar.e_wink_0),
                resolve(avatar.e_wink_1),
                resolve(avatar.e_wink_2),
                resolve(avatar.e_wink_3),
                resolve(avatar.e_wink_4),
                resolve(avatar.e_wink_5),
                resolve(avatar.e_wink_6),
                resolve(avatar.e_wink_7),
                resolve(avatar.e_wink_8),
            ],
        ],
        emotions_available,
    }))
}

/// Helper to collect non-null UUIDs into a Vec.
fn collect_uuids(dest: &mut Vec<Uuid>, sources: &[Option<Uuid>]) {
    for uuid in sources {
        if let Some(id) = uuid {
            dest.push(*id);
        }
    }
}

/// Avatar row with current_emotion from active_avatars join.
#[derive(Debug, sqlx::FromRow)]
struct AvatarWithEmotion {
    pub id: Uuid,
    pub current_emotion: EmotionState,
    // Content layers
    pub l_skin_0: Option<Uuid>,
    pub l_skin_1: Option<Uuid>,
    pub l_skin_2: Option<Uuid>,
    pub l_skin_3: Option<Uuid>,
    pub l_skin_4: Option<Uuid>,
    pub l_skin_5: Option<Uuid>,
    pub l_skin_6: Option<Uuid>,
    pub l_skin_7: Option<Uuid>,
    pub l_skin_8: Option<Uuid>,
    pub l_clothes_0: Option<Uuid>,
    pub l_clothes_1: Option<Uuid>,
    pub l_clothes_2: Option<Uuid>,
    pub l_clothes_3: Option<Uuid>,
    pub l_clothes_4: Option<Uuid>,
    pub l_clothes_5: Option<Uuid>,
    pub l_clothes_6: Option<Uuid>,
    pub l_clothes_7: Option<Uuid>,
    pub l_clothes_8: Option<Uuid>,
    pub l_accessories_0: Option<Uuid>,
    pub l_accessories_1: Option<Uuid>,
    pub l_accessories_2: Option<Uuid>,
    pub l_accessories_3: Option<Uuid>,
    pub l_accessories_4: Option<Uuid>,
    pub l_accessories_5: Option<Uuid>,
    pub l_accessories_6: Option<Uuid>,
    pub l_accessories_7: Option<Uuid>,
    pub l_accessories_8: Option<Uuid>,
    // Emotion layers
    pub e_neutral_0: Option<Uuid>,
    pub e_neutral_1: Option<Uuid>,
    pub e_neutral_2: Option<Uuid>,
    pub e_neutral_3: Option<Uuid>,
    pub e_neutral_4: Option<Uuid>,
    pub e_neutral_5: Option<Uuid>,
    pub e_neutral_6: Option<Uuid>,
    pub e_neutral_7: Option<Uuid>,
    pub e_neutral_8: Option<Uuid>,
    pub e_happy_0: Option<Uuid>,
    pub e_happy_1: Option<Uuid>,
    pub e_happy_2: Option<Uuid>,
    pub e_happy_3: Option<Uuid>,
    pub e_happy_4: Option<Uuid>,
    pub e_happy_5: Option<Uuid>,
    pub e_happy_6: Option<Uuid>,
    pub e_happy_7: Option<Uuid>,
    pub e_happy_8: Option<Uuid>,
    pub e_sad_0: Option<Uuid>,
    pub e_sad_1: Option<Uuid>,
    pub e_sad_2: Option<Uuid>,
    pub e_sad_3: Option<Uuid>,
    pub e_sad_4: Option<Uuid>,
    pub e_sad_5: Option<Uuid>,
    pub e_sad_6: Option<Uuid>,
    pub e_sad_7: Option<Uuid>,
    pub e_sad_8: Option<Uuid>,
    pub e_angry_0: Option<Uuid>,
    pub e_angry_1: Option<Uuid>,
    pub e_angry_2: Option<Uuid>,
    pub e_angry_3: Option<Uuid>,
    pub e_angry_4: Option<Uuid>,
    pub e_angry_5: Option<Uuid>,
    pub e_angry_6: Option<Uuid>,
    pub e_angry_7: Option<Uuid>,
    pub e_angry_8: Option<Uuid>,
    pub e_surprised_0: Option<Uuid>,
    pub e_surprised_1: Option<Uuid>,
    pub e_surprised_2: Option<Uuid>,
    pub e_surprised_3: Option<Uuid>,
    pub e_surprised_4: Option<Uuid>,
    pub e_surprised_5: Option<Uuid>,
    pub e_surprised_6: Option<Uuid>,
    pub e_surprised_7: Option<Uuid>,
    pub e_surprised_8: Option<Uuid>,
    pub e_thinking_0: Option<Uuid>,
    pub e_thinking_1: Option<Uuid>,
    pub e_thinking_2: Option<Uuid>,
    pub e_thinking_3: Option<Uuid>,
    pub e_thinking_4: Option<Uuid>,
    pub e_thinking_5: Option<Uuid>,
    pub e_thinking_6: Option<Uuid>,
    pub e_thinking_7: Option<Uuid>,
    pub e_thinking_8: Option<Uuid>,
    pub e_laughing_0: Option<Uuid>,
    pub e_laughing_1: Option<Uuid>,
    pub e_laughing_2: Option<Uuid>,
    pub e_laughing_3: Option<Uuid>,
    pub e_laughing_4: Option<Uuid>,
    pub e_laughing_5: Option<Uuid>,
    pub e_laughing_6: Option<Uuid>,
    pub e_laughing_7: Option<Uuid>,
    pub e_laughing_8: Option<Uuid>,
    pub e_crying_0: Option<Uuid>,
    pub e_crying_1: Option<Uuid>,
    pub e_crying_2: Option<Uuid>,
    pub e_crying_3: Option<Uuid>,
    pub e_crying_4: Option<Uuid>,
    pub e_crying_5: Option<Uuid>,
    pub e_crying_6: Option<Uuid>,
    pub e_crying_7: Option<Uuid>,
    pub e_crying_8: Option<Uuid>,
    pub e_love_0: Option<Uuid>,
    pub e_love_1: Option<Uuid>,
    pub e_love_2: Option<Uuid>,
    pub e_love_3: Option<Uuid>,
    pub e_love_4: Option<Uuid>,
    pub e_love_5: Option<Uuid>,
    pub e_love_6: Option<Uuid>,
    pub e_love_7: Option<Uuid>,
    pub e_love_8: Option<Uuid>,
    pub e_confused_0: Option<Uuid>,
    pub e_confused_1: Option<Uuid>,
    pub e_confused_2: Option<Uuid>,
    pub e_confused_3: Option<Uuid>,
    pub e_confused_4: Option<Uuid>,
    pub e_confused_5: Option<Uuid>,
    pub e_confused_6: Option<Uuid>,
    pub e_confused_7: Option<Uuid>,
    pub e_confused_8: Option<Uuid>,
    pub e_sleeping_0: Option<Uuid>,
    pub e_sleeping_1: Option<Uuid>,
    pub e_sleeping_2: Option<Uuid>,
    pub e_sleeping_3: Option<Uuid>,
    pub e_sleeping_4: Option<Uuid>,
    pub e_sleeping_5: Option<Uuid>,
    pub e_sleeping_6: Option<Uuid>,
    pub e_sleeping_7: Option<Uuid>,
    pub e_sleeping_8: Option<Uuid>,
    pub e_wink_0: Option<Uuid>,
    pub e_wink_1: Option<Uuid>,
    pub e_wink_2: Option<Uuid>,
    pub e_wink_3: Option<Uuid>,
    pub e_wink_4: Option<Uuid>,
    pub e_wink_5: Option<Uuid>,
    pub e_wink_6: Option<Uuid>,
    pub e_wink_7: Option<Uuid>,
    pub e_wink_8: Option<Uuid>,
}

/// Set the current emotion for a user (simplified - no path lookup).
///
/// This is used when the user's client has the full avatar cached locally
/// and can render the emotion from its local cache.
pub async fn set_emotion_simple<'e>(
    executor: impl PgExecutor<'e>,
    user_id: Uuid,
    realm_id: Uuid,
    emotion: EmotionState,
) -> Result<(), AppError> {
    let result = sqlx::query(
        r#"
        UPDATE auth.active_avatars
        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.to_string())
    .execute(executor)
    .await?;

    if result.rows_affected() == 0 {
        return Err(AppError::NotFound(
            "No active avatar for this user in this realm".to_string(),
        ));
    }

    Ok(())
}

/// Update an avatar slot by assigning an inventory item to it.
///
/// The column_name should be one of:
/// - "l_skin_0" through "l_skin_8"
/// - "l_clothes_0" through "l_clothes_8"
/// - "l_accessories_0" through "l_accessories_8"
/// - "e_{emotion}_0" through "e_{emotion}_8" (e.g., "e_happy_4")
pub async fn update_avatar_slot(
    conn: &mut PgConnection,
    user_id: Uuid,
    realm_id: Uuid,
    column_name: &str,
    inventory_id: Option<Uuid>,
) -> Result<(), AppError> {
    // Validate column name format to prevent SQL injection
    let valid_prefixes = [
        "l_skin_",
        "l_clothes_",
        "l_accessories_",
        "e_neutral_",
        "e_happy_",
        "e_sad_",
        "e_angry_",
        "e_surprised_",
        "e_thinking_",
        "e_laughing_",
        "e_crying_",
        "e_love_",
        "e_confused_",
        "e_sleeping_",
        "e_wink_",
    ];

    let is_valid = valid_prefixes
        .iter()
        .any(|prefix| column_name.starts_with(prefix))
        && column_name
            .chars()
            .last()
            .map(|c| c.is_ascii_digit() && c <= '8')
            .unwrap_or(false);

    if !is_valid {
        return Err(AppError::Validation(format!(
            "Invalid column name: {}",
            column_name
        )));
    }

    // Build dynamic UPDATE query
    // Note: We've validated the column name format above to prevent SQL injection
    let query = format!(
        r#"
        UPDATE auth.avatars
        SET {} = $3, updated_at = now()
        WHERE id = (
            SELECT avatar_id FROM auth.active_avatars
            WHERE user_id = $1 AND realm_id = $2
        )
        "#,
        column_name
    );

    let result = sqlx::query(&query)
        .bind(user_id)
        .bind(realm_id)
        .bind(inventory_id)
        .execute(&mut *conn)
        .await?;

    if result.rows_affected() == 0 {
        return Err(AppError::NotFound(
            "No active avatar for this user in this realm".to_string(),
        ));
    }

    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(())
}