//! Avatar-related database queries.

use sqlx::PgExecutor;
use uuid::Uuid;

use crate::models::{ActiveAvatar, AvatarRenderData, EmotionAvailability};
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, current_emotion, updated_at
        FROM props.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.
pub async fn set_emotion<'e>(
    executor: impl PgExecutor<'e>,
    user_id: Uuid,
    realm_id: Uuid,
    emotion: i16,
) -> Result<[Option<String>; 9], AppError> {
    if emotion < 0 || emotion > 11 {
        return Err(AppError::Validation("Emotion must be 0-11".to_string()));
    }

    // Map emotion index to column prefix
    let emotion_prefix = match emotion {
        0 => "e_neutral",
        1 => "e_happy",
        2 => "e_sad",
        3 => "e_angry",
        4 => "e_surprised",
        5 => "e_thinking",
        6 => "e_laughing",
        7 => "e_crying",
        8 => "e_love",
        9 => "e_confused",
        10 => "e_sleeping",
        11 => "e_wink",
        _ => return Err(AppError::Validation("Emotion must be 0-11".to_string())),
    };

    // Build dynamic query for the specific emotion's 9 positions
    let query = format!(
        r#"
        WITH updated AS (
            UPDATE props.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 props.inventory WHERE id = a.{prefix}_0) as p0,
            (SELECT prop_asset_path FROM props.inventory WHERE id = a.{prefix}_1) as p1,
            (SELECT prop_asset_path FROM props.inventory WHERE id = a.{prefix}_2) as p2,
            (SELECT prop_asset_path FROM props.inventory WHERE id = a.{prefix}_3) as p3,
            (SELECT prop_asset_path FROM props.inventory WHERE id = a.{prefix}_4) as p4,
            (SELECT prop_asset_path FROM props.inventory WHERE id = a.{prefix}_5) as p5,
            (SELECT prop_asset_path FROM props.inventory WHERE id = a.{prefix}_6) as p6,
            (SELECT prop_asset_path FROM props.inventory WHERE id = a.{prefix}_7) as p7,
            (SELECT prop_asset_path FROM props.inventory WHERE id = a.{prefix}_8) as p8
        FROM updated u
        JOIN props.avatars a ON a.id = u.avatar_id
        "#,
        prefix = emotion_prefix
    );

    let result = sqlx::query_as::<_, EmotionLayerRow>(&query)
        .bind(user_id)
        .bind(realm_id)
        .bind(emotion)
        .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(
            "No active avatar for this user in this realm".to_string(),
        )),
    }
}

/// 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 render data for a user's avatar in a realm.
///
/// Returns the asset paths for all equipped props in the avatar's current state.
/// This is a simplified version that only returns the center position (position 4)
/// props for skin, clothes, accessories, and current emotion layers.
pub async fn get_avatar_render_data<'e>(
    executor: impl PgExecutor<'e>,
    user_id: Uuid,
    realm_id: Uuid,
) -> Result<AvatarRenderData, AppError> {
    // Simplified query: just get position 4 (center) props for each layer
    // This covers the common case of simple face avatars
    let render_data = sqlx::query_as::<_, SimplifiedAvatarRow>(
        r#"
        SELECT
            a.id as avatar_id,
            aa.current_emotion,
            -- Skin layer center
            skin.prop_asset_path as skin_center,
            -- Clothes layer center
            clothes.prop_asset_path as clothes_center,
            -- Accessories layer center
            acc.prop_asset_path as accessories_center,
            -- Current emotion layer center (based on current_emotion)
            CASE aa.current_emotion
                WHEN 0 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_neutral_4)
                WHEN 1 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_happy_4)
                WHEN 2 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_sad_4)
                WHEN 3 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_angry_4)
                WHEN 4 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_surprised_4)
                WHEN 5 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_thinking_4)
                WHEN 6 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_laughing_4)
                WHEN 7 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_crying_4)
                WHEN 8 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_love_4)
                WHEN 9 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_confused_4)
            END as emotion_center
        FROM props.active_avatars aa
        JOIN props.avatars a ON aa.avatar_id = a.id
        LEFT JOIN props.inventory skin ON a.l_skin_4 = skin.id
        LEFT JOIN props.inventory clothes ON a.l_clothes_4 = clothes.id
        LEFT JOIN props.inventory acc ON a.l_accessories_4 = acc.id
        WHERE aa.user_id = $1 AND aa.realm_id = $2
        "#,
    )
    .bind(user_id)
    .bind(realm_id)
    .fetch_optional(executor)
    .await?;

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

/// Simplified avatar row for center-only rendering.
#[derive(Debug, sqlx::FromRow)]
struct SimplifiedAvatarRow {
    avatar_id: Uuid,
    current_emotion: i16,
    skin_center: Option<String>,
    clothes_center: Option<String>,
    accessories_center: Option<String>,
    emotion_center: Option<String>,
}

impl From<SimplifiedAvatarRow> for AvatarRenderData {
    fn from(row: SimplifiedAvatarRow) -> Self {
        // For now, only populate position 4 (center)
        let mut skin_layer: [Option<String>; 9] = Default::default();
        let mut clothes_layer: [Option<String>; 9] = Default::default();
        let mut accessories_layer: [Option<String>; 9] = Default::default();
        let mut emotion_layer: [Option<String>; 9] = Default::default();

        skin_layer[4] = row.skin_center;
        clothes_layer[4] = row.clothes_center;
        accessories_layer[4] = row.accessories_center;
        emotion_layer[4] = row.emotion_center;

        Self {
            avatar_id: row.avatar_id,
            current_emotion: row.current_emotion,
            skin_layer,
            clothes_layer,
            accessories_layer,
            emotion_layer,
        }
    }
}

/// 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 props.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 props.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 props.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 props.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 props.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 props.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 props.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 props.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 props.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 props.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 props.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 props.inventory WHERE id = a.e_wink_4) as preview_11

        FROM props.active_avatars aa
        JOIN props.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,
            ],
        }
    }
}