Skip to main content

max / makenotwork

30.2 KB · 891 lines History Blame Raw
1 //! Public discovery queries: search, browse, type/tag and price facets.
2 //!
3 //! Uses `pg_trgm` trigram indexes for fuzzy text matching.
4
5 use sqlx::{FromRow, PgPool};
6
7 use super::enums::{AiTierFilter, DiscoverSort, ItemType};
8 use super::models::*;
9 use crate::error::Result;
10
11 /// Shared filter parameters for discover item queries.
12 ///
13 /// Used by both [`discover_items`] and [`count_discover_items`] to keep
14 /// their filter logic in sync. The `sort_by` field is only relevant for
15 /// `discover_items`; `count_discover_items` ignores it.
16 pub struct DiscoverFilters<'a> {
17 pub search: Option<&'a str>,
18 pub item_type: Option<ItemType>,
19 pub tag: Option<&'a str>,
20 pub min_price: Option<i32>,
21 pub max_price: Option<i32>,
22 pub sort_by: Option<DiscoverSort>,
23 pub ai_tier: Option<AiTierFilter>,
24 }
25
26 // Shared SQL fragments for fuzzy search (trigram + ILIKE fallback).
27 // The ILIKE escapes \, %, _ in the search term to prevent LIKE metacharacter interpretation.
28
29 const ITEM_SEARCH_CLAUSE: &str = r#" AND (
30 i.title % $1
31 OR i.title ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
32 )"#;
33
34 const PROJECT_SEARCH_CLAUSE: &str = r#" AND (
35 p.title % $1
36 OR p.title ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
37 )"#;
38
39 // ILIKE-only variants for short queries (1-2 chars) where trigram similarity is unreliable.
40
41 const ITEM_SEARCH_CLAUSE_SHORT: &str = r#" AND (
42 i.title ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
43 )"#;
44
45 const PROJECT_SEARCH_CLAUSE_SHORT: &str = r#" AND (
46 p.title ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
47 )"#;
48
49 /// Maximum allowed search term length. Queries longer than this are truncated.
50 const MAX_SEARCH_LEN: usize = 200;
51
52 /// Normalize a search term: trim whitespace, truncate to [`MAX_SEARCH_LEN`],
53 /// and return `None` if the result is empty.
54 fn normalize_search(raw: Option<&str>) -> Option<String> {
55 let trimmed = raw?.trim();
56 if trimmed.is_empty() {
57 return None;
58 }
59 if trimmed.len() > MAX_SEARCH_LEN {
60 // Truncate at a char boundary
61 let end = trimmed
62 .char_indices()
63 .take_while(|(i, _)| *i < MAX_SEARCH_LEN)
64 .last()
65 .map(|(i, c)| i + c.len_utf8())
66 .unwrap_or(MAX_SEARCH_LEN);
67 Some(trimmed[..end].to_string())
68 } else {
69 Some(trimmed.to_string())
70 }
71 }
72
73 /// Returns `true` when the search term is too short for trigram matching (1-2 chars).
74 fn is_short_query(term: &str) -> bool {
75 term.trim().len() <= 2
76 }
77
78 /// Append discover-item filter clauses to a dynamic query.
79 /// Parameter positions: $1=search, $2=item_type, $3=min_price, $4=max_price, $5=tag, $6=ai_tier.
80 ///
81 /// When `short_query` is `true`, the ILIKE-only clause is used instead of the
82 /// full trigram + ILIKE clause (trigram matching is unreliable for 1-2 char terms).
83 fn append_item_discover_filters(
84 query: &mut String,
85 filters: &DiscoverFilters<'_>,
86 has_search: bool,
87 short_query: bool,
88 ) {
89 if has_search {
90 if short_query {
91 query.push_str(ITEM_SEARCH_CLAUSE_SHORT);
92 } else {
93 query.push_str(ITEM_SEARCH_CLAUSE);
94 }
95 }
96 if filters.item_type.is_some() {
97 query.push_str(" AND i.item_type = $2");
98 }
99 if filters.min_price.is_some() {
100 query.push_str(" AND i.price_cents >= $3");
101 }
102 if filters.max_price.is_some() {
103 query.push_str(" AND i.price_cents <= $4");
104 }
105 if filters.tag.is_some() {
106 // Match exact slug or any descendant (e.g. "audio.genre" matches "audio.genre.electronic")
107 query.push_str(
108 r#" AND EXISTS (
109 SELECT 1 FROM item_tags it2
110 JOIN tags t2 ON t2.id = it2.tag_id
111 WHERE it2.item_id = i.id
112 AND (t2.slug = $5 OR t2.path LIKE $5 || '.%')
113 )"#,
114 );
115 }
116 // AI disclosure filter: `Handmade only` narrows to handmade; `Human-led`
117 // accepts handmade ∪ assisted. Values are enum-derived constants (not user
118 // input), so inlining the literals is safe and keeps the bind-position
119 // count stable across queries that use this fragment.
120 match filters.ai_tier {
121 Some(AiTierFilter::HandmadeOnly) => query.push_str(" AND i.ai_tier = 'handmade'"),
122 Some(AiTierFilter::HumanLed) => {
123 query.push_str(" AND i.ai_tier IN ('handmade', 'assisted')")
124 }
125 None => {}
126 }
127 }
128
129 /// Bind the 5 discover-filter parameters ($1-$5) to a sqlx query.
130 /// The AI-tier filter is appended to the WHERE as a literal SQL fragment,
131 /// so it occupies no bind position.
132 macro_rules! bind_item_discover_filters {
133 ($q:expr, $filters:expr, $search_term:expr) => {
134 $q.bind($search_term.unwrap_or(""))
135 .bind($filters.item_type.map(|t| t.to_string()).unwrap_or_default())
136 .bind($filters.min_price.unwrap_or(0))
137 .bind($filters.max_price.unwrap_or(i32::MAX))
138 .bind($filters.tag.unwrap_or(""))
139 };
140 }
141
142 /// Search/browse public items with optional text search, item_type, tag, and price filters.
143 ///
144 /// Uses PostgreSQL `pg_trgm` for fuzzy text matching. The search strategy:
145 /// - `%` (trigram similarity operator) catches misspellings and near-matches
146 /// - `ILIKE` fallback catches exact substrings that trigrams might miss for short queries
147 /// - `similarity(description) * 0.5` weights description matches lower than title matches
148 /// so that a title hit always ranks above a description-only hit
149 ///
150 /// When a search term is present but no explicit sort is requested, results
151 /// are ordered by relevance (`match_score DESC`). Otherwise, the caller can
152 /// choose `most_sold`, `price_asc`, `price_desc`, or the default `newest`.
153 #[tracing::instrument(skip_all)]
154 pub async fn discover_items(
155 pool: &PgPool,
156 filters: &DiscoverFilters<'_>,
157 limit: i64,
158 offset: i64,
159 ) -> Result<Vec<DbDiscoverItemRow>> {
160 let search_term = normalize_search(filters.search);
161 let has_search = search_term.is_some();
162 let short_query = search_term.as_deref().is_some_and(is_short_query);
163
164 // Build the base query with optional similarity score.
165 // For short queries (1-2 chars) use a constant match_score since trigram
166 // similarity is unreliable at that length.
167 // Use LEFT JOIN with pre-aggregated transaction counts to avoid N+1 subquery per row
168 // LEFT JOIN item_tags/tags for primary tag display
169 let mut query = if has_search && !short_query {
170 String::from(
171 r#"
172 SELECT
173 i.id,
174 i.title,
175 i.description,
176 i.price_cents,
177 i.item_type,
178 i.created_at,
179 u.username,
180 p.title as project_title,
181 i.sales_count::bigint,
182 pt.name as primary_tag_name,
183 i.pwyw_enabled,
184 i.pwyw_min_cents,
185 i.ai_tier,
186 GREATEST(
187 similarity(i.title, $1),
188 similarity(COALESCE(i.description, ''), $1) * 0.5
189 )::real as match_score
190 FROM items i
191 JOIN projects p ON i.project_id = p.id
192 JOIN users u ON p.user_id = u.id
193 LEFT JOIN item_tags pit ON pit.item_id = i.id AND pit.is_primary = true
194 LEFT JOIN tags pt ON pt.id = pit.tag_id
195 WHERE i.is_public = true AND i.listed = true AND p.is_public = true AND i.scan_status != 'quarantined' AND u.is_sandbox = FALSE AND i.deleted_at IS NULL
196 "#,
197 )
198 } else if has_search {
199 // Short query: constant match_score, skip trigram similarity computation
200 String::from(
201 r#"
202 SELECT
203 i.id,
204 i.title,
205 i.description,
206 i.price_cents,
207 i.item_type,
208 i.created_at,
209 u.username,
210 p.title as project_title,
211 i.sales_count::bigint,
212 pt.name as primary_tag_name,
213 i.pwyw_enabled,
214 i.pwyw_min_cents,
215 i.ai_tier,
216 1.0::real as match_score
217 FROM items i
218 JOIN projects p ON i.project_id = p.id
219 JOIN users u ON p.user_id = u.id
220 LEFT JOIN item_tags pit ON pit.item_id = i.id AND pit.is_primary = true
221 LEFT JOIN tags pt ON pt.id = pit.tag_id
222 WHERE i.is_public = true AND i.listed = true AND p.is_public = true AND i.scan_status != 'quarantined' AND u.is_sandbox = FALSE AND i.deleted_at IS NULL
223 "#,
224 )
225 } else {
226 String::from(
227 r#"
228 SELECT
229 i.id,
230 i.title,
231 i.description,
232 i.price_cents,
233 i.item_type,
234 i.created_at,
235 u.username,
236 p.title as project_title,
237 i.sales_count::bigint,
238 pt.name as primary_tag_name,
239 i.pwyw_enabled,
240 i.pwyw_min_cents,
241 i.ai_tier,
242 NULL::real as match_score
243 FROM items i
244 JOIN projects p ON i.project_id = p.id
245 JOIN users u ON p.user_id = u.id
246 LEFT JOIN item_tags pit ON pit.item_id = i.id AND pit.is_primary = true
247 LEFT JOIN tags pt ON pt.id = pit.tag_id
248 WHERE i.is_public = true AND i.listed = true AND p.is_public = true AND i.scan_status != 'quarantined' AND u.is_sandbox = FALSE AND i.deleted_at IS NULL
249 "#,
250 )
251 };
252
253 append_item_discover_filters(&mut query, filters, has_search, short_query);
254
255 // Determine ordering
256 let order = if has_search && (filters.sort_by.is_none() || filters.sort_by == Some(DiscoverSort::Newest)) {
257 "match_score DESC NULLS LAST, i.created_at DESC"
258 } else {
259 match filters.sort_by {
260 Some(DiscoverSort::MostSold) => "sales_count DESC, i.created_at DESC",
261 Some(DiscoverSort::PriceAsc) => "i.price_cents ASC, i.created_at DESC",
262 Some(DiscoverSort::PriceDesc) => "i.price_cents DESC, i.created_at DESC",
263 _ => "i.created_at DESC",
264 }
265 };
266
267 query.push_str(&format!(" ORDER BY {} LIMIT $6 OFFSET $7", order));
268
269 let items = bind_item_discover_filters!(
270 sqlx::query_as::<_, DbDiscoverItemRow>(&query),
271 filters,
272 search_term.as_deref()
273 )
274 .bind(limit)
275 .bind(offset)
276 .fetch_all(pool)
277 .await?;
278
279 Ok(items)
280 }
281
282 /// Count total matching items for pagination (same filters as [`discover_items`]).
283 #[tracing::instrument(skip_all)]
284 pub async fn count_discover_items(
285 pool: &PgPool,
286 filters: &DiscoverFilters<'_>,
287 ) -> Result<i64> {
288 let search_term = normalize_search(filters.search);
289 let has_search = search_term.is_some();
290 let short_query = search_term.as_deref().is_some_and(is_short_query);
291
292 let mut query = String::from(
293 r#"
294 SELECT COUNT(*)
295 FROM items i
296 JOIN projects p ON i.project_id = p.id
297 JOIN users u ON p.user_id = u.id
298 WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL AND p.is_public = true AND i.scan_status != 'quarantined' AND u.is_sandbox = FALSE
299 "#,
300 );
301
302 append_item_discover_filters(&mut query, filters, has_search, short_query);
303
304 let count: i64 = bind_item_discover_filters!(
305 sqlx::query_scalar(&query),
306 filters,
307 search_term.as_deref()
308 )
309 .fetch_one(pool)
310 .await?;
311
312 Ok(count)
313 }
314
315 /// Search/browse public projects with optional text search and category filters.
316 ///
317 /// Same trigram + ILIKE strategy as [`discover_items`], but without price
318 /// filters. Aggregates a `item_count` via LEFT JOIN so the discover UI can
319 /// show "N items" per project without a separate query.
320 #[tracing::instrument(skip_all)]
321 pub async fn discover_projects(
322 pool: &PgPool,
323 search: Option<&str>,
324 category_slug: Option<&str>,
325 sort_by: Option<DiscoverSort>,
326 has_source_code: bool,
327 limit: i64,
328 offset: i64,
329 ) -> Result<Vec<DbDiscoverProjectRow>> {
330 let search_term = normalize_search(search);
331 let has_search = search_term.is_some();
332 let short_query = search_term.as_deref().is_some_and(is_short_query);
333
334 let mut query = if has_search && !short_query {
335 String::from(
336 r#"
337 SELECT
338 p.slug,
339 p.title,
340 p.description,
341 p.project_type,
342 p.created_at,
343 u.username,
344 COUNT(i.id) FILTER (WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL) as item_count,
345 GREATEST(
346 similarity(p.title, $1),
347 similarity(COALESCE(p.description, ''), $1) * 0.5
348 )::real as match_score,
349 pc.name as category_name,
350 pc.slug as category_slug
351 FROM projects p
352 JOIN users u ON p.user_id = u.id
353 LEFT JOIN items i ON i.project_id = p.id
354 LEFT JOIN project_categories pc ON pc.id = p.category_id
355 WHERE p.is_public = true AND u.is_sandbox = FALSE
356 "#,
357 )
358 } else if has_search {
359 // Short query: constant match_score, skip trigram similarity computation
360 String::from(
361 r#"
362 SELECT
363 p.slug,
364 p.title,
365 p.description,
366 p.project_type,
367 p.created_at,
368 u.username,
369 COUNT(i.id) FILTER (WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL) as item_count,
370 1.0::real as match_score,
371 pc.name as category_name,
372 pc.slug as category_slug
373 FROM projects p
374 JOIN users u ON p.user_id = u.id
375 LEFT JOIN items i ON i.project_id = p.id
376 LEFT JOIN project_categories pc ON pc.id = p.category_id
377 WHERE p.is_public = true AND u.is_sandbox = FALSE
378 "#,
379 )
380 } else {
381 String::from(
382 r#"
383 SELECT
384 p.slug,
385 p.title,
386 p.description,
387 p.project_type,
388 p.created_at,
389 u.username,
390 COUNT(i.id) FILTER (WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL) as item_count,
391 NULL::real as match_score,
392 pc.name as category_name,
393 pc.slug as category_slug
394 FROM projects p
395 JOIN users u ON p.user_id = u.id
396 LEFT JOIN items i ON i.project_id = p.id
397 LEFT JOIN project_categories pc ON pc.id = p.category_id
398 WHERE p.is_public = true AND u.is_sandbox = FALSE
399 "#,
400 )
401 };
402
403 if has_search {
404 if short_query {
405 query.push_str(PROJECT_SEARCH_CLAUSE_SHORT);
406 } else {
407 query.push_str(PROJECT_SEARCH_CLAUSE);
408 }
409 }
410
411 if category_slug.is_some() {
412 query.push_str(" AND pc.slug = $2");
413 }
414
415 if has_source_code {
416 query.push_str(" AND EXISTS (SELECT 1 FROM git_repos gr WHERE gr.project_id = p.id)");
417 }
418
419 query.push_str(" GROUP BY p.id, u.username, pc.name, pc.slug");
420
421 let order = if has_search && (sort_by.is_none() || sort_by == Some(DiscoverSort::Newest)) {
422 "match_score DESC NULLS LAST, p.created_at DESC"
423 } else {
424 match sort_by {
425 Some(DiscoverSort::MostSold) => "item_count DESC, p.created_at DESC",
426 _ => "p.created_at DESC",
427 }
428 };
429
430 query.push_str(&format!(" ORDER BY {} LIMIT $3 OFFSET $4", order));
431
432 let projects = sqlx::query_as::<_, DbDiscoverProjectRow>(&query)
433 .bind(search_term.as_deref().unwrap_or(""))
434 .bind(category_slug.unwrap_or(""))
435 .bind(limit)
436 .bind(offset)
437 .fetch_all(pool)
438 .await?;
439
440 Ok(projects)
441 }
442
443 /// Count total matching projects for pagination (same filters as [`discover_projects`]).
444 #[tracing::instrument(skip_all)]
445 pub async fn count_discover_projects(
446 pool: &PgPool,
447 search: Option<&str>,
448 category_slug: Option<&str>,
449 has_source_code: bool,
450 ) -> Result<i64> {
451 let search_term = normalize_search(search);
452 let has_search = search_term.is_some();
453 let short_query = search_term.as_deref().is_some_and(is_short_query);
454
455 let mut query = String::from(
456 r#"
457 SELECT COUNT(*)
458 FROM projects p
459 JOIN users u ON p.user_id = u.id
460 WHERE p.is_public = true AND u.is_sandbox = FALSE
461 "#,
462 );
463
464 if has_search {
465 if short_query {
466 query.push_str(PROJECT_SEARCH_CLAUSE_SHORT);
467 } else {
468 query.push_str(PROJECT_SEARCH_CLAUSE);
469 }
470 }
471
472 if category_slug.is_some() {
473 query.push_str(
474 " AND EXISTS (SELECT 1 FROM project_categories pc WHERE pc.id = p.category_id AND pc.slug = $2)",
475 );
476 }
477
478 if has_source_code {
479 query.push_str(" AND EXISTS (SELECT 1 FROM git_repos gr WHERE gr.project_id = p.id)");
480 }
481
482 let count: i64 = sqlx::query_scalar(&query)
483 .bind(search_term.as_deref().unwrap_or(""))
484 .bind(category_slug.unwrap_or(""))
485 .fetch_one(pool)
486 .await?;
487
488 Ok(count)
489 }
490
491 /// Get item type counts for discover page (items mode).
492 #[tracing::instrument(skip_all)]
493 pub async fn get_item_type_counts(
494 pool: &PgPool,
495 search: Option<&str>,
496 tag: Option<&str>,
497 min_price: Option<i32>,
498 max_price: Option<i32>,
499 ) -> Result<Vec<DbItemTypeCount>> {
500 let search_term = normalize_search(search);
501 let has_search = search_term.is_some();
502 let short_query = search_term.as_deref().is_some_and(is_short_query);
503
504 let mut query = String::from(
505 r#"
506 SELECT i.item_type as category, COUNT(*) as count
507 FROM items i
508 JOIN projects p ON i.project_id = p.id
509 JOIN users u ON p.user_id = u.id
510 WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL AND p.is_public = true AND i.scan_status != 'quarantined' AND u.is_sandbox = FALSE
511 "#,
512 );
513
514 if has_search {
515 if short_query {
516 query.push_str(ITEM_SEARCH_CLAUSE_SHORT);
517 } else {
518 query.push_str(ITEM_SEARCH_CLAUSE);
519 }
520 }
521
522 if tag.is_some() {
523 query.push_str(
524 r#" AND EXISTS (
525 SELECT 1 FROM item_tags it2
526 JOIN tags t2 ON t2.id = it2.tag_id
527 WHERE it2.item_id = i.id
528 AND (t2.slug = $2 OR t2.parent_id = (SELECT id FROM tags WHERE slug = $2))
529 )"#,
530 );
531 }
532
533 if min_price.is_some() {
534 query.push_str(" AND i.price_cents >= $3");
535 }
536
537 if max_price.is_some() {
538 query.push_str(" AND i.price_cents <= $4");
539 }
540
541 query.push_str(" GROUP BY i.item_type ORDER BY count DESC");
542
543 let counts = sqlx::query_as::<_, DbItemTypeCount>(&query)
544 .bind(search_term.as_deref().unwrap_or(""))
545 .bind(tag.unwrap_or(""))
546 .bind(min_price.unwrap_or(0))
547 .bind(max_price.unwrap_or(i32::MAX))
548 .fetch_all(pool)
549 .await?;
550
551 Ok(counts)
552 }
553
554 /// Get price range counts for the discover page sidebar (items mode only).
555 ///
556 /// Buckets are in cents: free (0), under $25 (1..2499), $25-$50 (2500..4999),
557 /// $50-$100 (5000..9999), over $100 (10000+). Uses PostgreSQL `FILTER (WHERE ...)`
558 /// to compute all five counts in a single table scan.
559 #[tracing::instrument(skip_all)]
560 pub async fn get_price_range_counts(
561 pool: &PgPool,
562 search: Option<&str>,
563 item_type: Option<ItemType>,
564 tag: Option<&str>,
565 ) -> Result<DbPriceRangeCounts> {
566 let search_term = normalize_search(search);
567 let has_search = search_term.is_some();
568 let short_query = search_term.as_deref().is_some_and(is_short_query);
569
570 let mut query = String::from(
571 r#"
572 SELECT
573 COUNT(*) FILTER (WHERE i.price_cents = 0) as free,
574 COUNT(*) FILTER (WHERE i.price_cents > 0 AND i.price_cents < 2500) as under_25,
575 COUNT(*) FILTER (WHERE i.price_cents >= 2500 AND i.price_cents < 5000) as range_25_50,
576 COUNT(*) FILTER (WHERE i.price_cents >= 5000 AND i.price_cents < 10000) as range_50_100,
577 COUNT(*) FILTER (WHERE i.price_cents >= 10000) as over_100
578 FROM items i
579 JOIN projects p ON i.project_id = p.id
580 JOIN users u ON p.user_id = u.id
581 WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL AND p.is_public = true AND i.scan_status != 'quarantined' AND u.is_sandbox = FALSE
582 "#,
583 );
584
585 if has_search {
586 if short_query {
587 query.push_str(ITEM_SEARCH_CLAUSE_SHORT);
588 } else {
589 query.push_str(ITEM_SEARCH_CLAUSE);
590 }
591 }
592
593 if item_type.is_some() {
594 query.push_str(" AND i.item_type = $2");
595 }
596
597 if tag.is_some() {
598 query.push_str(
599 r#" AND EXISTS (
600 SELECT 1 FROM item_tags it2
601 JOIN tags t2 ON t2.id = it2.tag_id
602 WHERE it2.item_id = i.id
603 AND (t2.slug = $3 OR t2.parent_id = (SELECT id FROM tags WHERE slug = $3))
604 )"#,
605 );
606 }
607
608 #[derive(FromRow)]
609 struct PriceRow {
610 free: Option<i64>,
611 under_25: Option<i64>,
612 range_25_50: Option<i64>,
613 range_50_100: Option<i64>,
614 over_100: Option<i64>,
615 }
616
617 let row = sqlx::query_as::<_, PriceRow>(&query)
618 .bind(search_term.as_deref().unwrap_or(""))
619 .bind(item_type.map(|t| t.to_string()).unwrap_or_default())
620 .bind(tag.unwrap_or(""))
621 .fetch_one(pool)
622 .await?;
623
624 Ok(DbPriceRangeCounts {
625 free: row.free.unwrap_or(0),
626 under_25: row.under_25.unwrap_or(0),
627 range_25_50: row.range_25_50.unwrap_or(0),
628 range_50_100: row.range_50_100.unwrap_or(0),
629 over_100: row.over_100.unwrap_or(0),
630 })
631 }
632
633 /// Get AI tier counts for the discover page sidebar (items mode only).
634 #[tracing::instrument(skip_all)]
635 pub async fn get_ai_tier_counts(
636 pool: &PgPool,
637 search: Option<&str>,
638 item_type: Option<ItemType>,
639 tag: Option<&str>,
640 ) -> Result<Vec<DbItemTypeCount>> {
641 let search_term = normalize_search(search);
642 let has_search = search_term.is_some();
643 let short_query = search_term.as_deref().is_some_and(is_short_query);
644
645 let mut query = String::from(
646 r#"
647 SELECT i.ai_tier as category, COUNT(*) as count
648 FROM items i
649 JOIN projects p ON i.project_id = p.id
650 JOIN users u ON p.user_id = u.id
651 WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL AND p.is_public = true AND i.scan_status != 'quarantined' AND u.is_sandbox = FALSE
652 "#,
653 );
654
655 if has_search {
656 if short_query {
657 query.push_str(ITEM_SEARCH_CLAUSE_SHORT);
658 } else {
659 query.push_str(ITEM_SEARCH_CLAUSE);
660 }
661 }
662
663 if item_type.is_some() {
664 query.push_str(" AND i.item_type = $2");
665 }
666
667 if tag.is_some() {
668 query.push_str(
669 r#" AND EXISTS (
670 SELECT 1 FROM item_tags it2
671 JOIN tags t2 ON t2.id = it2.tag_id
672 WHERE it2.item_id = i.id
673 AND (t2.slug = $3 OR t2.parent_id = (SELECT id FROM tags WHERE slug = $3))
674 )"#,
675 );
676 }
677
678 query.push_str(" GROUP BY i.ai_tier ORDER BY count DESC");
679
680 let counts = sqlx::query_as::<_, DbItemTypeCount>(&query)
681 .bind(search_term.as_deref().unwrap_or(""))
682 .bind(item_type.map(|t| t.to_string()).unwrap_or_default())
683 .bind(tag.unwrap_or(""))
684 .fetch_all(pool)
685 .await?;
686
687 Ok(counts)
688 }
689
690 /// A search suggestion with a category label (tag, project, or creator).
691 #[derive(Debug, FromRow)]
692 pub struct DbSearchSuggestion {
693 pub label: String,
694 pub category: String,
695 pub url: String,
696 }
697
698 /// Return combined search suggestions from tags, projects, and creators.
699 /// Uses ILIKE prefix match for fast results, limited to 8 total.
700 #[tracing::instrument(skip_all)]
701 pub async fn search_suggestions(pool: &PgPool, query: &str) -> Result<Vec<DbSearchSuggestion>> {
702 let q = query.trim();
703 if q.is_empty() {
704 return Ok(vec![]);
705 }
706
707 let pattern = format!("{}%", q.replace('\\', "\\\\").replace('%', "\\%").replace('_', "\\_"));
708
709 let rows = sqlx::query_as::<_, DbSearchSuggestion>(
710 r#"
711 (
712 SELECT name AS label, 'tag' AS category, '/discover?mode=items&tag=' || slug AS url
713 FROM tags
714 WHERE name ILIKE $1
715 ORDER BY name
716 LIMIT 3
717 )
718 UNION ALL
719 (
720 SELECT title AS label, 'project' AS category, '/p/' || slug AS url
721 FROM projects
722 WHERE is_public = true AND title ILIKE $1
723 ORDER BY title
724 LIMIT 3
725 )
726 UNION ALL
727 (
728 SELECT username AS label, 'creator' AS category, '/u/' || username AS url
729 FROM users
730 WHERE is_sandbox = false AND suspended_at IS NULL AND deactivated_at IS NULL AND username ILIKE $1
731 ORDER BY username
732 LIMIT 2
733 )
734 "#,
735 )
736 .bind(&pattern)
737 .fetch_all(pool)
738 .await?;
739
740 Ok(rows)
741 }
742
743 #[cfg(test)]
744 mod tests {
745 use super::*;
746
747 #[test]
748 fn normalize_search_none() {
749 assert_eq!(normalize_search(None), None);
750 }
751
752 #[test]
753 fn normalize_search_empty() {
754 assert_eq!(normalize_search(Some("")), None);
755 }
756
757 #[test]
758 fn normalize_search_whitespace_only() {
759 assert_eq!(normalize_search(Some(" ")), None);
760 }
761
762 #[test]
763 fn normalize_search_trims() {
764 assert_eq!(normalize_search(Some(" hello ")), Some("hello".to_string()));
765 }
766
767 #[test]
768 fn normalize_search_truncates_long() {
769 let long = "a".repeat(300);
770 let result = normalize_search(Some(&long)).unwrap();
771 assert_eq!(result.len(), MAX_SEARCH_LEN);
772 }
773
774 #[test]
775 fn normalize_search_truncates_at_char_boundary() {
776 // Multi-byte chars: each is 2 bytes. 150 chars = 300 bytes.
777 let long: String = std::iter::repeat_n('\u{00E9}', 150).collect();
778 let result = normalize_search(Some(&long)).unwrap();
779 assert!(result.len() <= MAX_SEARCH_LEN);
780 // Must end at a valid char boundary (no panic on indexing)
781 assert!(result.is_char_boundary(result.len()));
782 }
783
784 #[test]
785 fn normalize_search_exact_limit() {
786 let exact = "b".repeat(MAX_SEARCH_LEN);
787 assert_eq!(normalize_search(Some(&exact)), Some(exact));
788 }
789
790 #[test]
791 fn is_short_query_empty() {
792 assert!(is_short_query(""));
793 }
794
795 #[test]
796 fn is_short_query_two_chars() {
797 assert!(is_short_query("ab"));
798 }
799
800 #[test]
801 fn is_short_query_three_chars() {
802 assert!(!is_short_query("abc"));
803 }
804
805 #[test]
806 fn append_filters_no_filters() {
807 let filters = DiscoverFilters {
808 search: None, item_type: None, tag: None,
809 min_price: None, max_price: None, sort_by: None, ai_tier: None,
810 };
811 let mut q = String::from("SELECT 1 WHERE true");
812 append_item_discover_filters(&mut q, &filters, false, false);
813 assert_eq!(q, "SELECT 1 WHERE true");
814 }
815
816 #[test]
817 fn append_filters_with_item_type() {
818 let filters = DiscoverFilters {
819 search: None, item_type: Some(ItemType::Audio), tag: None,
820 min_price: None, max_price: None, sort_by: None, ai_tier: None,
821 };
822 let mut q = String::new();
823 append_item_discover_filters(&mut q, &filters, false, false);
824 assert!(q.contains("i.item_type = $2"));
825 }
826
827 #[test]
828 fn append_filters_search_uses_short_clause() {
829 let filters = DiscoverFilters {
830 search: Some("ab"), item_type: None, tag: None,
831 min_price: None, max_price: None, sort_by: None, ai_tier: None,
832 };
833 let mut q = String::new();
834 append_item_discover_filters(&mut q, &filters, true, true);
835 assert!(q.contains("ILIKE"));
836 assert!(!q.contains("i.title % $1"));
837 }
838
839 #[test]
840 fn append_filters_search_uses_trigram_clause() {
841 let filters = DiscoverFilters {
842 search: Some("hello"), item_type: None, tag: None,
843 min_price: None, max_price: None, sort_by: None, ai_tier: None,
844 };
845 let mut q = String::new();
846 append_item_discover_filters(&mut q, &filters, true, false);
847 assert!(q.contains("i.title % $1"));
848 }
849
850 #[test]
851 fn append_filters_handmade_only_narrows_to_one_tier() {
852 let filters = DiscoverFilters {
853 search: None, item_type: None, tag: None,
854 min_price: None, max_price: None, sort_by: None,
855 ai_tier: Some(AiTierFilter::HandmadeOnly),
856 };
857 let mut q = String::new();
858 append_item_discover_filters(&mut q, &filters, false, false);
859 assert!(q.contains("i.ai_tier = 'handmade'"));
860 assert!(!q.contains("assisted"));
861 }
862
863 #[test]
864 fn append_filters_human_led_includes_handmade_and_assisted() {
865 // Locks the policy commitment that Human-led covers BOTH handmade
866 // and assisted. A future rename of the literals or a swap to a
867 // single-tier match would silently weaken the filter.
868 let filters = DiscoverFilters {
869 search: None, item_type: None, tag: None,
870 min_price: None, max_price: None, sort_by: None,
871 ai_tier: Some(AiTierFilter::HumanLed),
872 };
873 let mut q = String::new();
874 append_item_discover_filters(&mut q, &filters, false, false);
875 assert!(q.contains("i.ai_tier IN ('handmade', 'assisted')"));
876 assert!(!q.contains("generated"));
877 }
878
879 #[test]
880 fn ai_tier_filter_round_trip() {
881 // Parses the query-string value the route receives back into the
882 // typed enum the SQL builder expects.
883 assert_eq!("handmade_only".parse::<AiTierFilter>().unwrap(), AiTierFilter::HandmadeOnly);
884 assert_eq!("human_led".parse::<AiTierFilter>().unwrap(), AiTierFilter::HumanLed);
885 assert!("everything".parse::<AiTierFilter>().is_err());
886 assert!("assisted".parse::<AiTierFilter>().is_err());
887 assert_eq!(AiTierFilter::HumanLed.to_string(), "human_led");
888 assert_eq!(AiTierFilter::HandmadeOnly.label(), "Handmade only");
889 }
890 }
891