//! Scene-related database queries.

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

use crate::models::{CreateSceneRequest, Scene, SceneSummary, UpdateSceneRequest};
use chattyness_error::{AppError, OptionExt};

/// List all scenes for a realm.
pub async fn list_scenes_for_realm<'e>(
    executor: impl PgExecutor<'e>,
    realm_id: Uuid,
) -> Result<Vec<SceneSummary>, AppError> {
    let scenes = sqlx::query_as::<_, SceneSummary>(
        r#"
        SELECT
            id,
            name,
            slug,
            sort_order,
            is_entry_point,
            is_hidden,
            background_color,
            background_image_path
        FROM realm.scenes
        WHERE realm_id = $1
        ORDER BY sort_order ASC, name ASC
        "#,
    )
    .bind(realm_id)
    .fetch_all(executor)
    .await?;

    Ok(scenes)
}

/// Get a scene by its ID.
pub async fn get_scene_by_id<'e>(
    executor: impl PgExecutor<'e>,
    scene_id: Uuid,
) -> Result<Option<Scene>, AppError> {
    let scene = sqlx::query_as::<_, Scene>(
        r#"
        SELECT
            s.id,
            s.realm_id,
            s.name,
            s.slug,
            s.description,
            s.background_image_path,
            s.background_color,
            ST_AsText(s.bounds) as bounds_wkt,
            s.dimension_mode,
            s.ambient_audio_id,
            s.ambient_volume,
            s.sort_order,
            s.is_entry_point,
            s.is_hidden,
            s.created_at,
            s.updated_at,
            c.id as default_channel_id
        FROM realm.scenes s
        LEFT JOIN scene.instances c ON c.scene_id = s.id AND c.instance_type = 'public'
        WHERE s.id = $1
        "#,
    )
    .bind(scene_id)
    .fetch_optional(executor)
    .await?;

    Ok(scene)
}

/// Get a scene by realm ID and slug.
pub async fn get_scene_by_slug<'e>(
    executor: impl PgExecutor<'e>,
    realm_id: Uuid,
    slug: &str,
) -> Result<Option<Scene>, AppError> {
    let scene = sqlx::query_as::<_, Scene>(
        r#"
        SELECT
            s.id,
            s.realm_id,
            s.name,
            s.slug,
            s.description,
            s.background_image_path,
            s.background_color,
            ST_AsText(s.bounds) as bounds_wkt,
            s.dimension_mode,
            s.ambient_audio_id,
            s.ambient_volume,
            s.sort_order,
            s.is_entry_point,
            s.is_hidden,
            s.created_at,
            s.updated_at,
            c.id as default_channel_id
        FROM realm.scenes s
        LEFT JOIN scene.instances c ON c.scene_id = s.id AND c.instance_type = 'public'
        WHERE s.realm_id = $1 AND s.slug = $2
        "#,
    )
    .bind(realm_id)
    .bind(slug)
    .fetch_optional(executor)
    .await?;

    Ok(scene)
}

/// Check if a scene slug is available within a realm.
pub async fn is_scene_slug_available<'e>(
    executor: impl PgExecutor<'e>,
    realm_id: Uuid,
    slug: &str,
) -> Result<bool, AppError> {
    let exists: (bool,) = sqlx::query_as(
        r#"SELECT EXISTS(SELECT 1 FROM realm.scenes WHERE realm_id = $1 AND slug = $2)"#,
    )
    .bind(realm_id)
    .bind(slug)
    .fetch_one(executor)
    .await?;

    Ok(!exists.0)
}

/// Create a new scene.
pub async fn create_scene<'e>(
    executor: impl PgExecutor<'e>,
    realm_id: Uuid,
    req: &CreateSceneRequest,
) -> Result<Scene, AppError> {
    let bounds_wkt = req
        .bounds_wkt
        .as_deref()
        .unwrap_or("POLYGON((0 0, 800 0, 800 600, 0 600, 0 0))");
    let dimension_mode = req.dimension_mode.unwrap_or_default().to_string();
    let sort_order = req.sort_order.unwrap_or(0);
    let is_entry_point = req.is_entry_point.unwrap_or(false);
    let is_hidden = req.is_hidden.unwrap_or(false);

    let scene = sqlx::query_as::<_, Scene>(
        r#"
        INSERT INTO realm.scenes (
            realm_id, name, slug, description,
            background_image_path, background_color,
            bounds, dimension_mode,
            sort_order, is_entry_point, is_hidden
        )
        VALUES (
            $1, $2, $3, $4,
            $5, $6,
            ST_GeomFromText($7, 0), $8::realm.dimension_mode,
            $9, $10, $11
        )
        RETURNING
            id,
            realm_id,
            name,
            slug,
            description,
            background_image_path,
            background_color,
            ST_AsText(bounds) as bounds_wkt,
            dimension_mode,
            ambient_audio_id,
            ambient_volume,
            sort_order,
            is_entry_point,
            is_hidden,
            created_at,
            updated_at,
            NULL::uuid as default_channel_id
        "#,
    )
    .bind(realm_id)
    .bind(&req.name)
    .bind(&req.slug)
    .bind(&req.description)
    .bind(&req.background_image_path)
    .bind(&req.background_color)
    .bind(bounds_wkt)
    .bind(&dimension_mode)
    .bind(sort_order)
    .bind(is_entry_point)
    .bind(is_hidden)
    .fetch_one(executor)
    .await?;

    Ok(scene)
}

/// Create a new scene with a specific ID.
///
/// This is used when we need to know the scene ID before creating it
/// (e.g., for storing background images in the correct path).
pub async fn create_scene_with_id<'e>(
    executor: impl PgExecutor<'e>,
    scene_id: Uuid,
    realm_id: Uuid,
    req: &CreateSceneRequest,
) -> Result<Scene, AppError> {
    let bounds_wkt = req
        .bounds_wkt
        .as_deref()
        .unwrap_or("POLYGON((0 0, 800 0, 800 600, 0 600, 0 0))");
    let dimension_mode = req.dimension_mode.unwrap_or_default().to_string();
    let sort_order = req.sort_order.unwrap_or(0);
    let is_entry_point = req.is_entry_point.unwrap_or(false);
    let is_hidden = req.is_hidden.unwrap_or(false);

    let scene = sqlx::query_as::<_, Scene>(
        r#"
        INSERT INTO realm.scenes (
            id, realm_id, name, slug, description,
            background_image_path, background_color,
            bounds, dimension_mode,
            sort_order, is_entry_point, is_hidden
        )
        VALUES (
            $1, $2, $3, $4, $5,
            $6, $7,
            ST_GeomFromText($8, 0), $9::realm.dimension_mode,
            $10, $11, $12
        )
        RETURNING
            id,
            realm_id,
            name,
            slug,
            description,
            background_image_path,
            background_color,
            ST_AsText(bounds) as bounds_wkt,
            dimension_mode,
            ambient_audio_id,
            ambient_volume,
            sort_order,
            is_entry_point,
            is_hidden,
            created_at,
            updated_at,
            NULL::uuid as default_channel_id
        "#,
    )
    .bind(scene_id)
    .bind(realm_id)
    .bind(&req.name)
    .bind(&req.slug)
    .bind(&req.description)
    .bind(&req.background_image_path)
    .bind(&req.background_color)
    .bind(bounds_wkt)
    .bind(&dimension_mode)
    .bind(sort_order)
    .bind(is_entry_point)
    .bind(is_hidden)
    .fetch_one(executor)
    .await?;

    Ok(scene)
}

/// Update a scene.
pub async fn update_scene<'e>(
    executor: impl PgExecutor<'e>,
    scene_id: Uuid,
    req: &UpdateSceneRequest,
) -> Result<Scene, AppError> {
    // Build dynamic update query
    let mut set_clauses = Vec::new();
    let mut param_idx = 2; // $1 is scene_id

    if req.name.is_some() {
        set_clauses.push(format!("name = ${}", param_idx));
        param_idx += 1;
    }
    if req.description.is_some() {
        set_clauses.push(format!("description = ${}", param_idx));
        param_idx += 1;
    }
    if req.background_image_path.is_some() {
        set_clauses.push(format!("background_image_path = ${}", param_idx));
        param_idx += 1;
    }
    if req.background_color.is_some() {
        set_clauses.push(format!("background_color = ${}", param_idx));
        param_idx += 1;
    }
    if req.bounds_wkt.is_some() {
        set_clauses.push(format!("bounds = ST_GeomFromText(${}, 0)", param_idx));
        param_idx += 1;
    }
    if req.dimension_mode.is_some() {
        set_clauses.push(format!(
            "dimension_mode = ${}::realm.dimension_mode",
            param_idx
        ));
        param_idx += 1;
    }
    if req.sort_order.is_some() {
        set_clauses.push(format!("sort_order = ${}", param_idx));
        param_idx += 1;
    }
    if req.is_entry_point.is_some() {
        set_clauses.push(format!("is_entry_point = ${}", param_idx));
        param_idx += 1;
    }
    if req.is_hidden.is_some() {
        set_clauses.push(format!("is_hidden = ${}", param_idx));
    }

    // If no updates, just return the current scene
    let query = if set_clauses.is_empty() {
        r#"SELECT s.id, s.realm_id, s.name, s.slug, s.description, s.background_image_path,
                  s.background_color, ST_AsText(s.bounds) as bounds_wkt, s.dimension_mode,
                  s.ambient_audio_id, s.ambient_volume, s.sort_order, s.is_entry_point,
                  s.is_hidden, s.created_at, s.updated_at, c.id as default_channel_id
           FROM realm.scenes s
           LEFT JOIN scene.instances c ON c.scene_id = s.id AND c.instance_type = 'public'
           WHERE s.id = $1"#
            .to_string()
    } else {
        set_clauses.push("updated_at = now()".to_string());
        format!(
            r#"WITH updated AS (
                   UPDATE realm.scenes SET {}
                   WHERE id = $1
                   RETURNING id, realm_id, name, slug, description, background_image_path,
                             background_color, ST_AsText(bounds) as bounds_wkt, dimension_mode,
                             ambient_audio_id, ambient_volume, sort_order, is_entry_point,
                             is_hidden, created_at, updated_at
               )
               SELECT u.*, c.id as default_channel_id
               FROM updated u
               LEFT JOIN scene.instances c ON c.scene_id = u.id AND c.instance_type = 'public'"#,
            set_clauses.join(", ")
        )
    };

    let mut query_builder = sqlx::query_as::<_, Scene>(&query).bind(scene_id);

    if let Some(ref name) = req.name {
        query_builder = query_builder.bind(name);
    }
    if let Some(ref description) = req.description {
        query_builder = query_builder.bind(description);
    }
    if let Some(ref background_image_path) = req.background_image_path {
        query_builder = query_builder.bind(background_image_path);
    }
    if let Some(ref background_color) = req.background_color {
        query_builder = query_builder.bind(background_color);
    }
    if let Some(ref bounds_wkt) = req.bounds_wkt {
        query_builder = query_builder.bind(bounds_wkt);
    }
    if let Some(ref dimension_mode) = req.dimension_mode {
        query_builder = query_builder.bind(dimension_mode.to_string());
    }
    if let Some(sort_order) = req.sort_order {
        query_builder = query_builder.bind(sort_order);
    }
    if let Some(is_entry_point) = req.is_entry_point {
        query_builder = query_builder.bind(is_entry_point);
    }
    if let Some(is_hidden) = req.is_hidden {
        query_builder = query_builder.bind(is_hidden);
    }

    let scene = query_builder
        .fetch_optional(executor)
        .await?
        .or_not_found("Scene")?;

    Ok(scene)
}

/// Delete a scene.
pub async fn delete_scene<'e>(
    executor: impl PgExecutor<'e>,
    scene_id: Uuid,
) -> Result<(), AppError> {
    let result = sqlx::query(r#"DELETE FROM realm.scenes WHERE id = $1"#)
        .bind(scene_id)
        .execute(executor)
        .await?;

    if result.rows_affected() == 0 {
        return Err(AppError::NotFound("Scene not found".to_string()));
    }

    Ok(())
}

/// Get the next sort order for a new scene in a realm.
pub async fn get_next_sort_order<'e>(
    executor: impl PgExecutor<'e>,
    realm_id: Uuid,
) -> Result<i32, AppError> {
    let result: (Option<i32>,) =
        sqlx::query_as(r#"SELECT MAX(sort_order) FROM realm.scenes WHERE realm_id = $1"#)
            .bind(realm_id)
            .fetch_one(executor)
            .await?;

    Ok(result.0.unwrap_or(0) + 1)
}

/// Get the entry scene for a realm.
///
/// Returns the scene in this priority order:
/// 1. The scene specified by `default_scene_id` on the realm (if provided and exists)
/// 2. The first scene marked as `is_entry_point`
/// 3. The first scene by sort_order
///
/// Also includes the default public channel ID for the scene.
pub async fn get_entry_scene_for_realm<'e>(
    executor: impl PgExecutor<'e>,
    realm_id: Uuid,
    default_scene_id: Option<Uuid>,
) -> Result<Option<Scene>, AppError> {
    // Use a single query that handles the priority in SQL
    // Joins with channels to get the default public channel for the scene
    let scene = sqlx::query_as::<_, Scene>(
        r#"
        SELECT
            s.id,
            s.realm_id,
            s.name,
            s.slug,
            s.description,
            s.background_image_path,
            s.background_color,
            ST_AsText(s.bounds) as bounds_wkt,
            s.dimension_mode,
            s.ambient_audio_id,
            s.ambient_volume,
            s.sort_order,
            s.is_entry_point,
            s.is_hidden,
            s.created_at,
            s.updated_at,
            c.id as default_channel_id
        FROM realm.scenes s
        LEFT JOIN scene.instances c ON c.scene_id = s.id AND c.instance_type = 'public'
        WHERE s.realm_id = $1 AND s.is_hidden = false
        ORDER BY
            CASE WHEN s.id = $2 THEN 0 ELSE 1 END,
            s.is_entry_point DESC,
            s.sort_order ASC
        LIMIT 1
        "#,
    )
    .bind(realm_id)
    .bind(default_scene_id)
    .fetch_optional(executor)
    .await?;

    Ok(scene)
}