Skip to main content

max / makenotwork

10.0 KB · 380 lines History Blame Raw
1 //! CRUD operations for fan collections (user-curated lists of items).
2
3 use sqlx::PgPool;
4
5 use super::models::*;
6 use super::{CollectionId, ItemId, Slug, UserId};
7 use crate::error::Result;
8
9 /// Create a new collection for a user.
10 #[tracing::instrument(skip_all)]
11 pub async fn create_collection(
12 pool: &PgPool,
13 user_id: UserId,
14 slug: &Slug,
15 title: &str,
16 description: Option<&str>,
17 is_public: bool,
18 ) -> Result<DbCollection> {
19 let collection = sqlx::query_as::<_, DbCollection>(
20 r#"
21 INSERT INTO collections (user_id, slug, title, description, is_public)
22 VALUES ($1, $2, $3, $4, $5)
23 RETURNING *
24 "#,
25 )
26 .bind(user_id)
27 .bind(slug)
28 .bind(title)
29 .bind(description)
30 .bind(is_public)
31 .fetch_one(pool)
32 .await?;
33
34 Ok(collection)
35 }
36
37 /// Update a collection's title, description, and visibility.
38 #[tracing::instrument(skip_all)]
39 pub async fn update_collection(
40 pool: &PgPool,
41 collection_id: CollectionId,
42 title: &str,
43 description: Option<&str>,
44 is_public: bool,
45 ) -> Result<DbCollection> {
46 let collection = sqlx::query_as::<_, DbCollection>(
47 r#"
48 UPDATE collections
49 SET title = $2, description = $3, is_public = $4, updated_at = NOW()
50 WHERE id = $1
51 RETURNING *
52 "#,
53 )
54 .bind(collection_id)
55 .bind(title)
56 .bind(description)
57 .bind(is_public)
58 .fetch_one(pool)
59 .await?;
60
61 Ok(collection)
62 }
63
64 /// Delete a collection by ID. Returns true if a row was deleted.
65 #[tracing::instrument(skip_all)]
66 pub async fn delete_collection(pool: &PgPool, collection_id: CollectionId) -> Result<bool> {
67 let result = sqlx::query("DELETE FROM collections WHERE id = $1")
68 .bind(collection_id)
69 .execute(pool)
70 .await?;
71
72 Ok(result.rows_affected() > 0)
73 }
74
75 /// Get a collection by ID.
76 #[tracing::instrument(skip_all)]
77 pub async fn get_collection_by_id(
78 pool: &PgPool,
79 collection_id: CollectionId,
80 ) -> Result<Option<DbCollection>> {
81 let collection = sqlx::query_as::<_, DbCollection>(
82 "SELECT * FROM collections WHERE id = $1",
83 )
84 .bind(collection_id)
85 .fetch_optional(pool)
86 .await?;
87
88 Ok(collection)
89 }
90
91 /// Get a collection by user ID and slug.
92 #[tracing::instrument(skip_all)]
93 pub async fn get_collection_by_user_and_slug(
94 pool: &PgPool,
95 user_id: UserId,
96 slug: &Slug,
97 ) -> Result<Option<DbCollection>> {
98 let collection = sqlx::query_as::<_, DbCollection>(
99 "SELECT * FROM collections WHERE user_id = $1 AND slug = $2",
100 )
101 .bind(user_id)
102 .bind(slug)
103 .fetch_optional(pool)
104 .await?;
105
106 Ok(collection)
107 }
108
109 /// Get all collections for a user (with item counts), for the dashboard.
110 #[tracing::instrument(skip_all)]
111 pub async fn get_collections_by_user(
112 pool: &PgPool,
113 user_id: UserId,
114 ) -> Result<Vec<DbCollectionWithCount>> {
115 let collections = sqlx::query_as::<_, DbCollectionWithCount>(
116 r#"
117 SELECT c.*, COUNT(ci.item_id) AS item_count
118 FROM collections c
119 LEFT JOIN collection_items ci ON ci.collection_id = c.id
120 WHERE c.user_id = $1
121 GROUP BY c.id
122 ORDER BY c.updated_at DESC
123 LIMIT 500
124 "#,
125 )
126 .bind(user_id)
127 .fetch_all(pool)
128 .await?;
129
130 Ok(collections)
131 }
132
133 /// Get public collections for a user (with item counts), for the profile page.
134 #[tracing::instrument(skip_all)]
135 pub async fn get_public_collections_by_user(
136 pool: &PgPool,
137 user_id: UserId,
138 ) -> Result<Vec<DbCollectionWithCount>> {
139 let collections = sqlx::query_as::<_, DbCollectionWithCount>(
140 r#"
141 SELECT c.*, COUNT(ci.item_id) AS item_count
142 FROM collections c
143 LEFT JOIN collection_items ci ON ci.collection_id = c.id
144 WHERE c.user_id = $1 AND c.is_public = true
145 GROUP BY c.id
146 ORDER BY c.updated_at DESC
147 "#,
148 )
149 .bind(user_id)
150 .fetch_all(pool)
151 .await?;
152
153 Ok(collections)
154 }
155
156 /// Count collections owned by a user.
157 #[tracing::instrument(skip_all)]
158 pub async fn count_collections_by_user(pool: &PgPool, user_id: UserId) -> Result<i64> {
159 let count: i64 = sqlx::query_scalar(
160 "SELECT COUNT(*) FROM collections WHERE user_id = $1",
161 )
162 .bind(user_id)
163 .fetch_one(pool)
164 .await?;
165
166 Ok(count)
167 }
168
169 /// Add an item to a collection. Idempotent (ON CONFLICT DO NOTHING).
170 /// Appends at max(position)+1 atomically via INSERT...SELECT.
171 #[tracing::instrument(skip_all)]
172 pub async fn add_item_to_collection(
173 pool: &PgPool,
174 collection_id: CollectionId,
175 item_id: ItemId,
176 ) -> Result<()> {
177 sqlx::query(
178 r#"
179 INSERT INTO collection_items (collection_id, item_id, position)
180 VALUES ($1, $2, COALESCE((SELECT MAX(position) FROM collection_items WHERE collection_id = $1), -1) + 1)
181 ON CONFLICT (collection_id, item_id) DO NOTHING
182 "#,
183 )
184 .bind(collection_id)
185 .bind(item_id)
186 .execute(pool)
187 .await?;
188
189 // Touch the collection's updated_at
190 sqlx::query("UPDATE collections SET updated_at = NOW() WHERE id = $1")
191 .bind(collection_id)
192 .execute(pool)
193 .await?;
194
195 Ok(())
196 }
197
198 /// Remove an item from a collection. Returns true if a row was deleted.
199 #[tracing::instrument(skip_all)]
200 pub async fn remove_item_from_collection(
201 pool: &PgPool,
202 collection_id: CollectionId,
203 item_id: ItemId,
204 ) -> Result<bool> {
205 let result = sqlx::query(
206 "DELETE FROM collection_items WHERE collection_id = $1 AND item_id = $2",
207 )
208 .bind(collection_id)
209 .bind(item_id)
210 .execute(pool)
211 .await?;
212
213 if result.rows_affected() > 0 {
214 sqlx::query("UPDATE collections SET updated_at = NOW() WHERE id = $1")
215 .bind(collection_id)
216 .execute(pool)
217 .await?;
218 }
219
220 Ok(result.rows_affected() > 0)
221 }
222
223 /// Get items in a collection, joined with item/project/user data, ordered by position.
224 #[tracing::instrument(skip_all)]
225 pub async fn get_collection_items(
226 pool: &PgPool,
227 collection_id: CollectionId,
228 ) -> Result<Vec<DbCollectionItemRow>> {
229 let items = sqlx::query_as::<_, DbCollectionItemRow>(
230 r#"
231 SELECT
232 ci.item_id,
233 i.title,
234 i.description,
235 i.price_cents,
236 i.item_type,
237 u.username,
238 p.title AS project_title,
239 ci.position,
240 ci.added_at
241 FROM collection_items ci
242 JOIN items i ON i.id = ci.item_id
243 JOIN projects p ON p.id = i.project_id
244 JOIN users u ON u.id = p.user_id
245 WHERE ci.collection_id = $1
246 ORDER BY ci.position
247 "#,
248 )
249 .bind(collection_id)
250 .fetch_all(pool)
251 .await?;
252
253 Ok(items)
254 }
255
256 /// Get item IDs for multiple collections in a single query (batch, avoids N+1).
257 #[tracing::instrument(skip_all)]
258 pub async fn get_item_ids_by_collections(
259 pool: &PgPool,
260 collection_ids: &[CollectionId],
261 ) -> Result<std::collections::HashMap<CollectionId, Vec<ItemId>>> {
262 if collection_ids.is_empty() {
263 return Ok(std::collections::HashMap::new());
264 }
265 let rows: Vec<(CollectionId, ItemId)> = sqlx::query_as(
266 r#"
267 SELECT collection_id, item_id
268 FROM collection_items
269 WHERE collection_id = ANY($1)
270 ORDER BY collection_id, position
271 "#,
272 )
273 .bind(collection_ids)
274 .fetch_all(pool)
275 .await?;
276
277 let mut map: std::collections::HashMap<CollectionId, Vec<ItemId>> = std::collections::HashMap::new();
278 for (cid, iid) in rows {
279 map.entry(cid).or_default().push(iid);
280 }
281 Ok(map)
282 }
283
284 /// Count items in a collection.
285 #[tracing::instrument(skip_all)]
286 pub async fn count_collection_items(
287 pool: &PgPool,
288 collection_id: CollectionId,
289 ) -> Result<i64> {
290 let count: i64 = sqlx::query_scalar(
291 "SELECT COUNT(*) FROM collection_items WHERE collection_id = $1",
292 )
293 .bind(collection_id)
294 .fetch_one(pool)
295 .await?;
296
297 Ok(count)
298 }
299
300 /// Reorder items in a collection by assigning position from the given ID sequence.
301 /// Wrapped in a transaction so a crash mid-reorder doesn't leave inconsistent state.
302 #[tracing::instrument(skip_all)]
303 pub async fn reorder_collection_items(
304 pool: &PgPool,
305 collection_id: CollectionId,
306 item_ids: &[ItemId],
307 ) -> Result<()> {
308 let mut tx = pool.begin().await?;
309 for (index, item_id) in item_ids.iter().enumerate() {
310 sqlx::query(
311 "UPDATE collection_items SET position = $1 WHERE collection_id = $2 AND item_id = $3",
312 )
313 .bind(index as i32)
314 .bind(collection_id)
315 .bind(item_id)
316 .execute(&mut *tx)
317 .await?;
318 }
319
320 sqlx::query("UPDATE collections SET updated_at = NOW() WHERE id = $1")
321 .bind(collection_id)
322 .execute(&mut *tx)
323 .await?;
324 tx.commit().await?;
325
326 Ok(())
327 }
328
329 /// Count how many of a user's collections contain a specific item.
330 #[tracing::instrument(skip_all)]
331 pub async fn count_user_collections_containing_item(
332 pool: &PgPool,
333 user_id: UserId,
334 item_id: ItemId,
335 ) -> Result<i64> {
336 let (count,): (i64,) = sqlx::query_as(
337 r#"
338 SELECT COUNT(*) FROM collections c
339 JOIN collection_items ci ON ci.collection_id = c.id
340 WHERE c.user_id = $1 AND ci.item_id = $2
341 "#,
342 )
343 .bind(user_id)
344 .bind(item_id)
345 .fetch_one(pool)
346 .await?;
347
348 Ok(count)
349 }
350
351 /// Get a user's collections with membership state for a specific item.
352 /// Returns (collection_id, title, is_in_collection) for the "add to collection" dropdown.
353 #[tracing::instrument(skip_all)]
354 pub async fn get_user_collections_for_item(
355 pool: &PgPool,
356 user_id: UserId,
357 item_id: ItemId,
358 ) -> Result<Vec<(CollectionId, String, bool)>> {
359 let rows: Vec<(CollectionId, String, bool)> = sqlx::query_as(
360 r#"
361 SELECT
362 c.id,
363 c.title,
364 EXISTS(
365 SELECT 1 FROM collection_items ci
366 WHERE ci.collection_id = c.id AND ci.item_id = $2
367 ) AS is_in_collection
368 FROM collections c
369 WHERE c.user_id = $1
370 ORDER BY c.updated_at DESC
371 "#,
372 )
373 .bind(user_id)
374 .bind(item_id)
375 .fetch_all(pool)
376 .await?;
377
378 Ok(rows)
379 }
380