fix: problems with prop dropping

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

@@ -47,45 +47,26 @@ pub async fn list_channel_loose_props<'e>(
 /// 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(
-    pool: &sqlx::PgPool,
+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> {
-    // First check if the item exists and is droppable
-    let item_check: Option<(bool,)> = sqlx::query_as(
-        r#"SELECT is_droppable FROM props.inventory WHERE id = $1 AND user_id = $2"#,
-    )
-    .bind(inventory_item_id)
-    .bind(user_id)
-    .fetch_optional(pool)
-    .await?;
-
-    match item_check {
-        None => {
-            return Err(AppError::NotFound(
-                "Inventory item not found or not owned by user".to_string(),
-            ));
-        }
-        Some((false,)) => {
-            return Err(AppError::Forbidden(
-                "This prop cannot be dropped - it is an essential prop".to_string(),
-            ));
-        }
-        Some((true,)) => {
-            // Item is droppable, proceed with the drop operation
-        }
-    }
-
-    // Use a CTE to delete from inventory and insert to loose_props in one query
-    let prop = sqlx::query_as::<_, LooseProp>(
+    // Single CTE that checks existence/droppability and performs the operation atomically.
+    // Returns status flags plus the LooseProp data (if successful).
+    let result: Option<(bool, bool, bool, Option<Uuid>, Option<Uuid>, Option<Uuid>, Option<Uuid>, 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 deleted_item AS (
-            DELETE FROM props.inventory
+        WITH item_info AS (
+            SELECT id, is_droppable, server_prop_id, realm_prop_id, prop_name, prop_asset_path
+            FROM props.inventory
             WHERE id = $1 AND user_id = $2
+        ),
+        deleted_item AS (
+            DELETE FROM props.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 (
@@ -117,6 +98,9 @@ pub async fn drop_prop_to_canvas(
                 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,
@@ -128,8 +112,9 @@ pub async fn drop_prop_to_canvas(
             ip.created_at,
             di.prop_name,
             di.prop_asset_path
-        FROM inserted_prop ip
-        CROSS JOIN deleted_item di
+        FROM (SELECT 1) AS dummy
+        LEFT JOIN inserted_prop ip ON true
+        LEFT JOIN deleted_item di ON true
         "#,
     )
     .bind(inventory_item_id)
@@ -137,13 +122,72 @@ pub async fn drop_prop_to_canvas(
     .bind(channel_id)
     .bind(position_x as f32)
     .bind(position_y as f32)
-    .fetch_optional(pool)
-    .await?
-    .ok_or_else(|| {
-        AppError::Internal("Unexpected error dropping prop to canvas".to_string())
-    })?;
+    .fetch_optional(executor)
+    .await?;
 
-    Ok(prop)
+    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),
+            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(),
+                dropped_by,
+                expires_at: Some(expires_at),
+                created_at,
+                prop_name,
+                prop_asset_path,
+            })
+        }
+        _ => {
+            // 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).