//! Loose props database queries.
//!
//! Handles props dropped in channels that can be picked up by users.

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

use crate::models::{InventoryItem, LooseProp};
use chattyness_error::AppError;

/// Ensure an instance exists for a scene.
///
/// In this system, scenes are used directly as instances (channel_id = scene_id).
/// This creates an instance record if one doesn't exist, using the scene_id as the instance_id.
/// This is needed for loose_props foreign key constraint.
pub async fn ensure_scene_instance<'e>(
    executor: impl PgExecutor<'e>,
    scene_id: Uuid,
) -> Result<(), AppError> {
    sqlx::query(
        r#"
        INSERT INTO scene.instances (id, scene_id, instance_type)
        SELECT $1, $1, 'public'::scene.instance_type
        WHERE EXISTS (SELECT 1 FROM realm.scenes WHERE id = $1)
        ON CONFLICT (id) DO NOTHING
        "#,
    )
    .bind(scene_id)
    .execute(executor)
    .await?;

    Ok(())
}

/// List all loose props in a channel (excluding expired).
pub async fn list_channel_loose_props<'e>(
    executor: impl PgExecutor<'e>,
    channel_id: Uuid,
) -> Result<Vec<LooseProp>, AppError> {
    let props = sqlx::query_as::<_, LooseProp>(
        r#"
        SELECT
            lp.id,
            lp.instance_id as channel_id,
            lp.server_prop_id,
            lp.realm_prop_id,
            ST_X(lp.position) as position_x,
            ST_Y(lp.position) as position_y,
            lp.scale,
            lp.dropped_by,
            lp.expires_at,
            lp.created_at,
            COALESCE(sp.name, rp.name) as prop_name,
            COALESCE(sp.asset_path, rp.asset_path) as prop_asset_path,
            lp.is_locked,
            lp.locked_by
        FROM scene.loose_props lp
        LEFT JOIN server.props sp ON lp.server_prop_id = sp.id
        LEFT JOIN realm.props rp ON lp.realm_prop_id = rp.id
        WHERE lp.instance_id = $1
          AND (lp.expires_at IS NULL OR lp.expires_at > now())
        ORDER BY lp.created_at ASC
        "#,
    )
    .bind(channel_id)
    .fetch_all(executor)
    .await?;

    Ok(props)
}

/// Drop a prop from inventory to the canvas.
///
/// Deletes from inventory and inserts into loose_props with 30-minute expiry.
/// Returns the created loose prop.
/// Returns an error if the prop is non-droppable (essential prop).
pub async fn drop_prop_to_canvas<'e>(
    executor: impl PgExecutor<'e>,
    inventory_item_id: Uuid,
    user_id: Uuid,
    channel_id: Uuid,
    position_x: f64,
    position_y: f64,
) -> Result<LooseProp, AppError> {
    // Single CTE that checks existence/droppability and performs the operation atomically.
    // Returns status flags plus the LooseProp data (if successful).
    // Includes scale inherited from the source prop's default_scale.
    let result: Option<(
        bool,
        bool,
        bool,
        Option<Uuid>,
        Option<Uuid>,
        Option<Uuid>,
        Option<Uuid>,
        Option<f32>,
        Option<f32>,
        Option<f32>,
        Option<Uuid>,
        Option<chrono::DateTime<chrono::Utc>>,
        Option<chrono::DateTime<chrono::Utc>>,
        Option<String>,
        Option<String>,
    )> = sqlx::query_as(
        r#"
        WITH item_info AS (
            SELECT
                inv.id,
                inv.is_droppable,
                inv.server_prop_id,
                inv.realm_prop_id,
                inv.prop_name,
                inv.prop_asset_path,
                COALESCE(sp.default_scale, rp.default_scale, 1.0) as default_scale
            FROM auth.inventory inv
            LEFT JOIN server.props sp ON inv.server_prop_id = sp.id
            LEFT JOIN realm.props rp ON inv.realm_prop_id = rp.id
            WHERE inv.id = $1 AND inv.user_id = $2
        ),
        deleted_item AS (
            DELETE FROM auth.inventory
            WHERE id = $1 AND user_id = $2 AND is_droppable = true
            RETURNING id, server_prop_id, realm_prop_id, prop_name, prop_asset_path
        ),
        inserted_prop AS (
            INSERT INTO scene.loose_props (
                instance_id,
                server_prop_id,
                realm_prop_id,
                position,
                scale,
                dropped_by,
                expires_at
            )
            SELECT
                $3,
                di.server_prop_id,
                di.realm_prop_id,
                public.make_virtual_point($4::real, $5::real),
                (SELECT default_scale FROM item_info),
                $2,
                now() + interval '30 minutes'
            FROM deleted_item di
            RETURNING
                id,
                instance_id as channel_id,
                server_prop_id,
                realm_prop_id,
                ST_X(position)::real as position_x,
                ST_Y(position)::real as position_y,
                scale,
                dropped_by,
                expires_at,
                created_at
        )
        SELECT
            EXISTS(SELECT 1 FROM item_info) AS item_existed,
            COALESCE((SELECT is_droppable FROM item_info), false) AS was_droppable,
            EXISTS(SELECT 1 FROM deleted_item) AS was_deleted,
            ip.id,
            ip.channel_id,
            ip.server_prop_id,
            ip.realm_prop_id,
            ip.position_x,
            ip.position_y,
            ip.scale,
            ip.dropped_by,
            ip.expires_at,
            ip.created_at,
            di.prop_name,
            di.prop_asset_path
        FROM (SELECT 1) AS dummy
        LEFT JOIN inserted_prop ip ON true
        LEFT JOIN deleted_item di ON true
        "#,
    )
    .bind(inventory_item_id)
    .bind(user_id)
    .bind(channel_id)
    .bind(position_x as f32)
    .bind(position_y as f32)
    .fetch_optional(executor)
    .await?;

    match result {
        None => {
            // Query returned no rows (shouldn't happen with our dummy table)
            Err(AppError::Internal(
                "Unexpected error dropping prop to canvas".to_string(),
            ))
        }
        Some((false, _, _, _, _, _, _, _, _, _, _, _, _, _, _)) => {
            // Item didn't exist
            Err(AppError::NotFound(
                "Inventory item not found or not owned by user".to_string(),
            ))
        }
        Some((true, false, _, _, _, _, _, _, _, _, _, _, _, _, _)) => {
            // Item existed but is not droppable
            Err(AppError::Forbidden(
                "This prop cannot be dropped - it is an essential prop".to_string(),
            ))
        }
        Some((true, true, false, _, _, _, _, _, _, _, _, _, _, _, _)) => {
            // Item was droppable but delete failed (shouldn't happen)
            Err(AppError::Internal(
                "Unexpected error dropping prop to canvas".to_string(),
            ))
        }
        Some((
            true,
            true,
            true,
            Some(id),
            Some(channel_id),
            server_prop_id,
            realm_prop_id,
            Some(position_x),
            Some(position_y),
            Some(scale),
            dropped_by,
            Some(expires_at),
            Some(created_at),
            Some(prop_name),
            Some(prop_asset_path),
        )) => {
            // Success! Convert f32 positions to f64.
            Ok(LooseProp {
                id,
                channel_id,
                server_prop_id,
                realm_prop_id,
                position_x: position_x.into(),
                position_y: position_y.into(),
                scale,
                dropped_by,
                expires_at: Some(expires_at),
                created_at,
                prop_name,
                prop_asset_path,
                is_locked: false,
                locked_by: None,
            })
        }
        _ => {
            // Some fields were unexpectedly null
            Err(AppError::Internal(
                "Unexpected null values in drop prop result".to_string(),
            ))
        }
    }
}

/// Pick up a loose prop (delete from loose_props, insert to inventory).
///
/// Returns the created inventory item.
pub async fn pick_up_loose_prop<'e>(
    executor: impl PgExecutor<'e>,
    loose_prop_id: Uuid,
    user_id: Uuid,
) -> Result<InventoryItem, AppError> {
    // Use a CTE to delete from loose_props and insert to inventory
    let item = sqlx::query_as::<_, InventoryItem>(
        r#"
        WITH deleted_prop AS (
            DELETE FROM scene.loose_props
            WHERE id = $1
              AND (expires_at IS NULL OR expires_at > now())
            RETURNING id, server_prop_id, realm_prop_id
        ),
        source_info AS (
            SELECT
                COALESCE(sp.name, rp.name) as prop_name,
                COALESCE(sp.asset_path, rp.asset_path) as prop_asset_path,
                COALESCE(sp.default_layer, rp.default_layer) as layer,
                COALESCE(sp.is_transferable, rp.is_transferable) as is_transferable,
                COALESCE(sp.is_portable, true) as is_portable,
                COALESCE(sp.is_droppable, rp.is_droppable, true) as is_droppable,
                dp.server_prop_id,
                dp.realm_prop_id
            FROM deleted_prop dp
            LEFT JOIN server.props sp ON dp.server_prop_id = sp.id
            LEFT JOIN realm.props rp ON dp.realm_prop_id = rp.id
        ),
        inserted_item AS (
            INSERT INTO auth.inventory (
                user_id,
                server_prop_id,
                realm_prop_id,
                prop_name,
                prop_asset_path,
                layer,
                origin,
                is_transferable,
                is_portable,
                is_droppable,
                provenance,
                acquired_at
            )
            SELECT
                $2,
                si.server_prop_id,
                si.realm_prop_id,
                si.prop_name,
                si.prop_asset_path,
                si.layer,
                'server_library'::server.prop_origin,
                COALESCE(si.is_transferable, true),
                COALESCE(si.is_portable, true),
                COALESCE(si.is_droppable, true),
                '[]'::jsonb,
                now()
            FROM source_info si
            RETURNING id, prop_name, prop_asset_path, layer, is_transferable, is_portable, is_droppable, acquired_at
        )
        SELECT
            ii.id,
            ii.prop_name,
            ii.prop_asset_path,
            ii.layer,
            ii.is_transferable,
            ii.is_portable,
            ii.is_droppable,
            'server_library'::server.prop_origin as origin,
            ii.acquired_at
        FROM inserted_item ii
        "#,
    )
    .bind(loose_prop_id)
    .bind(user_id)
    .fetch_optional(executor)
    .await?
    .ok_or_else(|| AppError::NotFound("Loose prop not found or has expired".to_string()))?;

    Ok(item)
}

/// Update the scale of a loose prop.
///
/// Server admins can update any loose prop.
/// Realm admins can update loose props in their realm.
pub async fn update_loose_prop_scale<'e>(
    executor: impl PgExecutor<'e>,
    loose_prop_id: Uuid,
    scale: f32,
) -> Result<LooseProp, AppError> {
    // Validate scale range
    if !(0.1..=10.0).contains(&scale) {
        return Err(AppError::Validation(
            "Scale must be between 0.1 and 10.0".to_string(),
        ));
    }

    let prop = sqlx::query_as::<_, LooseProp>(
        r#"
        WITH updated AS (
            UPDATE scene.loose_props
            SET scale = $2
            WHERE id = $1
              AND (expires_at IS NULL OR expires_at > now())
            RETURNING
                id,
                instance_id as channel_id,
                server_prop_id,
                realm_prop_id,
                ST_X(position) as position_x,
                ST_Y(position) as position_y,
                scale,
                dropped_by,
                expires_at,
                created_at,
                is_locked,
                locked_by
        )
        SELECT
            u.id,
            u.channel_id,
            u.server_prop_id,
            u.realm_prop_id,
            u.position_x,
            u.position_y,
            u.scale,
            u.dropped_by,
            u.expires_at,
            u.created_at,
            COALESCE(sp.name, rp.name) as prop_name,
            COALESCE(sp.asset_path, rp.asset_path) as prop_asset_path,
            u.is_locked,
            u.locked_by
        FROM updated u
        LEFT JOIN server.props sp ON u.server_prop_id = sp.id
        LEFT JOIN realm.props rp ON u.realm_prop_id = rp.id
        "#,
    )
    .bind(loose_prop_id)
    .bind(scale)
    .fetch_optional(executor)
    .await?
    .ok_or_else(|| AppError::NotFound("Loose prop not found or has expired".to_string()))?;

    Ok(prop)
}

/// Get a loose prop by ID.
pub async fn get_loose_prop_by_id<'e>(
    executor: impl PgExecutor<'e>,
    loose_prop_id: Uuid,
) -> Result<Option<LooseProp>, AppError> {
    let prop = sqlx::query_as::<_, LooseProp>(
        r#"
        SELECT
            lp.id,
            lp.instance_id as channel_id,
            lp.server_prop_id,
            lp.realm_prop_id,
            ST_X(lp.position) as position_x,
            ST_Y(lp.position) as position_y,
            lp.scale,
            lp.dropped_by,
            lp.expires_at,
            lp.created_at,
            COALESCE(sp.name, rp.name) as prop_name,
            COALESCE(sp.asset_path, rp.asset_path) as prop_asset_path,
            lp.is_locked,
            lp.locked_by
        FROM scene.loose_props lp
        LEFT JOIN server.props sp ON lp.server_prop_id = sp.id
        LEFT JOIN realm.props rp ON lp.realm_prop_id = rp.id
        WHERE lp.id = $1
          AND (lp.expires_at IS NULL OR lp.expires_at > now())
        "#,
    )
    .bind(loose_prop_id)
    .fetch_optional(executor)
    .await?;

    Ok(prop)
}

/// Move a loose prop to a new position.
pub async fn move_loose_prop<'e>(
    executor: impl PgExecutor<'e>,
    loose_prop_id: Uuid,
    x: f64,
    y: f64,
) -> Result<LooseProp, AppError> {
    let prop = sqlx::query_as::<_, LooseProp>(
        r#"
        WITH updated AS (
            UPDATE scene.loose_props
            SET position = public.make_virtual_point($2::real, $3::real)
            WHERE id = $1
              AND (expires_at IS NULL OR expires_at > now())
            RETURNING
                id,
                instance_id as channel_id,
                server_prop_id,
                realm_prop_id,
                ST_X(position) as position_x,
                ST_Y(position) as position_y,
                scale,
                dropped_by,
                expires_at,
                created_at,
                is_locked,
                locked_by
        )
        SELECT
            u.id,
            u.channel_id,
            u.server_prop_id,
            u.realm_prop_id,
            u.position_x,
            u.position_y,
            u.scale,
            u.dropped_by,
            u.expires_at,
            u.created_at,
            COALESCE(sp.name, rp.name) as prop_name,
            COALESCE(sp.asset_path, rp.asset_path) as prop_asset_path,
            u.is_locked,
            u.locked_by
        FROM updated u
        LEFT JOIN server.props sp ON u.server_prop_id = sp.id
        LEFT JOIN realm.props rp ON u.realm_prop_id = rp.id
        "#,
    )
    .bind(loose_prop_id)
    .bind(x as f32)
    .bind(y as f32)
    .fetch_optional(executor)
    .await?
    .ok_or_else(|| AppError::NotFound("Loose prop not found or has expired".to_string()))?;

    Ok(prop)
}

/// Lock a loose prop (moderator only).
pub async fn lock_loose_prop<'e>(
    executor: impl PgExecutor<'e>,
    loose_prop_id: Uuid,
    locked_by: Uuid,
) -> Result<LooseProp, AppError> {
    let prop = sqlx::query_as::<_, LooseProp>(
        r#"
        WITH updated AS (
            UPDATE scene.loose_props
            SET is_locked = true, locked_by = $2
            WHERE id = $1
              AND (expires_at IS NULL OR expires_at > now())
            RETURNING
                id,
                instance_id as channel_id,
                server_prop_id,
                realm_prop_id,
                ST_X(position) as position_x,
                ST_Y(position) as position_y,
                scale,
                dropped_by,
                expires_at,
                created_at,
                is_locked,
                locked_by
        )
        SELECT
            u.id,
            u.channel_id,
            u.server_prop_id,
            u.realm_prop_id,
            u.position_x,
            u.position_y,
            u.scale,
            u.dropped_by,
            u.expires_at,
            u.created_at,
            COALESCE(sp.name, rp.name) as prop_name,
            COALESCE(sp.asset_path, rp.asset_path) as prop_asset_path,
            u.is_locked,
            u.locked_by
        FROM updated u
        LEFT JOIN server.props sp ON u.server_prop_id = sp.id
        LEFT JOIN realm.props rp ON u.realm_prop_id = rp.id
        "#,
    )
    .bind(loose_prop_id)
    .bind(locked_by)
    .fetch_optional(executor)
    .await?
    .ok_or_else(|| AppError::NotFound("Loose prop not found or has expired".to_string()))?;

    Ok(prop)
}

/// Unlock a loose prop (moderator only).
pub async fn unlock_loose_prop<'e>(
    executor: impl PgExecutor<'e>,
    loose_prop_id: Uuid,
) -> Result<LooseProp, AppError> {
    let prop = sqlx::query_as::<_, LooseProp>(
        r#"
        WITH updated AS (
            UPDATE scene.loose_props
            SET is_locked = false, locked_by = NULL
            WHERE id = $1
              AND (expires_at IS NULL OR expires_at > now())
            RETURNING
                id,
                instance_id as channel_id,
                server_prop_id,
                realm_prop_id,
                ST_X(position) as position_x,
                ST_Y(position) as position_y,
                scale,
                dropped_by,
                expires_at,
                created_at,
                is_locked,
                locked_by
        )
        SELECT
            u.id,
            u.channel_id,
            u.server_prop_id,
            u.realm_prop_id,
            u.position_x,
            u.position_y,
            u.scale,
            u.dropped_by,
            u.expires_at,
            u.created_at,
            COALESCE(sp.name, rp.name) as prop_name,
            COALESCE(sp.asset_path, rp.asset_path) as prop_asset_path,
            u.is_locked,
            u.locked_by
        FROM updated u
        LEFT JOIN server.props sp ON u.server_prop_id = sp.id
        LEFT JOIN realm.props rp ON u.realm_prop_id = rp.id
        "#,
    )
    .bind(loose_prop_id)
    .fetch_optional(executor)
    .await?
    .ok_or_else(|| AppError::NotFound("Loose prop not found or has expired".to_string()))?;

    Ok(prop)
}

/// Delete expired loose props.
///
/// Returns the number of props deleted.
pub async fn cleanup_expired_props<'e>(executor: impl PgExecutor<'e>) -> Result<u64, AppError> {
    let result = sqlx::query(
        r#"
        DELETE FROM scene.loose_props
        WHERE expires_at IS NOT NULL AND expires_at <= now()
        "#,
    )
    .execute(executor)
    .await?;

    Ok(result.rows_affected())
}