Skip to main content

max / makenotwork

27.8 KB · 838 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::{AiTier, 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<AiTier>,
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 if filters.ai_tier.is_some() {
117 query.push_str(" AND i.ai_tier = $6");
118 }
119 }
120
121 /// Bind the 6 discover-filter parameters ($1-$6) to a sqlx query.
122 macro_rules! bind_item_discover_filters {
123 ($q:expr, $filters:expr, $search_term:expr) => {
124 $q.bind($search_term.unwrap_or(""))
125 .bind($filters.item_type.map(|t| t.to_string()).unwrap_or_default())
126 .bind($filters.min_price.unwrap_or(0))
127 .bind($filters.max_price.unwrap_or(i32::MAX))
128 .bind($filters.tag.unwrap_or(""))
129 .bind($filters.ai_tier.map(|t| t.to_string()).unwrap_or_default())
130 };
131 }
132
133 /// Search/browse public items with optional text search, item_type, tag, and price filters.
134 ///
135 /// Uses PostgreSQL `pg_trgm` for fuzzy text matching. The search strategy:
136 /// - `%` (trigram similarity operator) catches misspellings and near-matches
137 /// - `ILIKE` fallback catches exact substrings that trigrams might miss for short queries
138 /// - `similarity(description) * 0.5` weights description matches lower than title matches
139 /// so that a title hit always ranks above a description-only hit
140 ///
141 /// When a search term is present but no explicit sort is requested, results
142 /// are ordered by relevance (`match_score DESC`). Otherwise, the caller can
143 /// choose `most_sold`, `price_asc`, `price_desc`, or the default `newest`.
144 #[tracing::instrument(skip_all)]
145 pub async fn discover_items(
146 pool: &PgPool,
147 filters: &DiscoverFilters<'_>,
148 limit: i64,
149 offset: i64,
150 ) -> Result<Vec<DbDiscoverItemRow>> {
151 let search_term = normalize_search(filters.search);
152 let has_search = search_term.is_some();
153 let short_query = search_term.as_deref().is_some_and(is_short_query);
154
155 // Build the base query with optional similarity score.
156 // For short queries (1-2 chars) use a constant match_score since trigram
157 // similarity is unreliable at that length.
158 // Use LEFT JOIN with pre-aggregated transaction counts to avoid N+1 subquery per row
159 // LEFT JOIN item_tags/tags for primary tag display
160 let mut query = if has_search && !short_query {
161 String::from(
162 r#"
163 SELECT
164 i.id,
165 i.title,
166 i.description,
167 i.price_cents,
168 i.item_type,
169 i.created_at,
170 u.username,
171 p.title as project_title,
172 i.sales_count::bigint,
173 pt.name as primary_tag_name,
174 i.pwyw_enabled,
175 i.pwyw_min_cents,
176 GREATEST(
177 similarity(i.title, $1),
178 similarity(COALESCE(i.description, ''), $1) * 0.5
179 )::real as match_score
180 FROM items i
181 JOIN projects p ON i.project_id = p.id
182 JOIN users u ON p.user_id = u.id
183 LEFT JOIN item_tags pit ON pit.item_id = i.id AND pit.is_primary = true
184 LEFT JOIN tags pt ON pt.id = pit.tag_id
185 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
186 "#,
187 )
188 } else if has_search {
189 // Short query: constant match_score, skip trigram similarity computation
190 String::from(
191 r#"
192 SELECT
193 i.id,
194 i.title,
195 i.description,
196 i.price_cents,
197 i.item_type,
198 i.created_at,
199 u.username,
200 p.title as project_title,
201 i.sales_count::bigint,
202 pt.name as primary_tag_name,
203 i.pwyw_enabled,
204 i.pwyw_min_cents,
205 1.0::real as match_score
206 FROM items i
207 JOIN projects p ON i.project_id = p.id
208 JOIN users u ON p.user_id = u.id
209 LEFT JOIN item_tags pit ON pit.item_id = i.id AND pit.is_primary = true
210 LEFT JOIN tags pt ON pt.id = pit.tag_id
211 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
212 "#,
213 )
214 } else {
215 String::from(
216 r#"
217 SELECT
218 i.id,
219 i.title,
220 i.description,
221 i.price_cents,
222 i.item_type,
223 i.created_at,
224 u.username,
225 p.title as project_title,
226 i.sales_count::bigint,
227 pt.name as primary_tag_name,
228 i.pwyw_enabled,
229 i.pwyw_min_cents,
230 NULL::real as match_score
231 FROM items i
232 JOIN projects p ON i.project_id = p.id
233 JOIN users u ON p.user_id = u.id
234 LEFT JOIN item_tags pit ON pit.item_id = i.id AND pit.is_primary = true
235 LEFT JOIN tags pt ON pt.id = pit.tag_id
236 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
237 "#,
238 )
239 };
240
241 append_item_discover_filters(&mut query, filters, has_search, short_query);
242
243 // Determine ordering
244 let order = if has_search && (filters.sort_by.is_none() || filters.sort_by == Some(DiscoverSort::Newest)) {
245 "match_score DESC NULLS LAST, i.created_at DESC"
246 } else {
247 match filters.sort_by {
248 Some(DiscoverSort::MostSold) => "sales_count DESC, i.created_at DESC",
249 Some(DiscoverSort::PriceAsc) => "i.price_cents ASC, i.created_at DESC",
250 Some(DiscoverSort::PriceDesc) => "i.price_cents DESC, i.created_at DESC",
251 _ => "i.created_at DESC",
252 }
253 };
254
255 query.push_str(&format!(" ORDER BY {} LIMIT $7 OFFSET $8", order));
256
257 let items = bind_item_discover_filters!(
258 sqlx::query_as::<_, DbDiscoverItemRow>(&query),
259 filters,
260 search_term.as_deref()
261 )
262 .bind(limit)
263 .bind(offset)
264 .fetch_all(pool)
265 .await?;
266
267 Ok(items)
268 }
269
270 /// Count total matching items for pagination (same filters as [`discover_items`]).
271 #[tracing::instrument(skip_all)]
272 pub async fn count_discover_items(
273 pool: &PgPool,
274 filters: &DiscoverFilters<'_>,
275 ) -> Result<i64> {
276 let search_term = normalize_search(filters.search);
277 let has_search = search_term.is_some();
278 let short_query = search_term.as_deref().is_some_and(is_short_query);
279
280 let mut query = String::from(
281 r#"
282 SELECT COUNT(*)
283 FROM items i
284 JOIN projects p ON i.project_id = p.id
285 JOIN users u ON p.user_id = u.id
286 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
287 "#,
288 );
289
290 append_item_discover_filters(&mut query, filters, has_search, short_query);
291
292 let count: i64 = bind_item_discover_filters!(
293 sqlx::query_scalar(&query),
294 filters,
295 search_term.as_deref()
296 )
297 .fetch_one(pool)
298 .await?;
299
300 Ok(count)
301 }
302
303 /// Search/browse public projects with optional text search and category filters.
304 ///
305 /// Same trigram + ILIKE strategy as [`discover_items`], but without price
306 /// filters. Aggregates a `item_count` via LEFT JOIN so the discover UI can
307 /// show "N items" per project without a separate query.
308 #[tracing::instrument(skip_all)]
309 pub async fn discover_projects(
310 pool: &PgPool,
311 search: Option<&str>,
312 category_slug: Option<&str>,
313 sort_by: Option<DiscoverSort>,
314 has_source_code: bool,
315 limit: i64,
316 offset: i64,
317 ) -> Result<Vec<DbDiscoverProjectRow>> {
318 let search_term = normalize_search(search);
319 let has_search = search_term.is_some();
320 let short_query = search_term.as_deref().is_some_and(is_short_query);
321
322 let mut query = if has_search && !short_query {
323 String::from(
324 r#"
325 SELECT
326 p.slug,
327 p.title,
328 p.description,
329 p.project_type,
330 p.created_at,
331 u.username,
332 COUNT(i.id) FILTER (WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL) as item_count,
333 GREATEST(
334 similarity(p.title, $1),
335 similarity(COALESCE(p.description, ''), $1) * 0.5
336 )::real as match_score,
337 pc.name as category_name,
338 pc.slug as category_slug
339 FROM projects p
340 JOIN users u ON p.user_id = u.id
341 LEFT JOIN items i ON i.project_id = p.id
342 LEFT JOIN project_categories pc ON pc.id = p.category_id
343 WHERE p.is_public = true AND u.is_sandbox = FALSE
344 "#,
345 )
346 } else if has_search {
347 // Short query: constant match_score, skip trigram similarity computation
348 String::from(
349 r#"
350 SELECT
351 p.slug,
352 p.title,
353 p.description,
354 p.project_type,
355 p.created_at,
356 u.username,
357 COUNT(i.id) FILTER (WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL) as item_count,
358 1.0::real as match_score,
359 pc.name as category_name,
360 pc.slug as category_slug
361 FROM projects p
362 JOIN users u ON p.user_id = u.id
363 LEFT JOIN items i ON i.project_id = p.id
364 LEFT JOIN project_categories pc ON pc.id = p.category_id
365 WHERE p.is_public = true AND u.is_sandbox = FALSE
366 "#,
367 )
368 } else {
369 String::from(
370 r#"
371 SELECT
372 p.slug,
373 p.title,
374 p.description,
375 p.project_type,
376 p.created_at,
377 u.username,
378 COUNT(i.id) FILTER (WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL) as item_count,
379 NULL::real as match_score,
380 pc.name as category_name,
381 pc.slug as category_slug
382 FROM projects p
383 JOIN users u ON p.user_id = u.id
384 LEFT JOIN items i ON i.project_id = p.id
385 LEFT JOIN project_categories pc ON pc.id = p.category_id
386 WHERE p.is_public = true AND u.is_sandbox = FALSE
387 "#,
388 )
389 };
390
391 if has_search {
392 if short_query {
393 query.push_str(PROJECT_SEARCH_CLAUSE_SHORT);
394 } else {
395 query.push_str(PROJECT_SEARCH_CLAUSE);
396 }
397 }
398
399 if category_slug.is_some() {
400 query.push_str(" AND pc.slug = $2");
401 }
402
403 if has_source_code {
404 query.push_str(" AND EXISTS (SELECT 1 FROM git_repos gr WHERE gr.project_id = p.id)");
405 }
406
407 query.push_str(" GROUP BY p.id, u.username, pc.name, pc.slug");
408
409 let order = if has_search && (sort_by.is_none() || sort_by == Some(DiscoverSort::Newest)) {
410 "match_score DESC NULLS LAST, p.created_at DESC"
411 } else {
412 match sort_by {
413 Some(DiscoverSort::MostSold) => "item_count DESC, p.created_at DESC",
414 _ => "p.created_at DESC",
415 }
416 };
417
418 query.push_str(&format!(" ORDER BY {} LIMIT $3 OFFSET $4", order));
419
420 let projects = sqlx::query_as::<_, DbDiscoverProjectRow>(&query)
421 .bind(search_term.as_deref().unwrap_or(""))
422 .bind(category_slug.unwrap_or(""))
423 .bind(limit)
424 .bind(offset)
425 .fetch_all(pool)
426 .await?;
427
428 Ok(projects)
429 }
430
431 /// Count total matching projects for pagination (same filters as [`discover_projects`]).
432 #[tracing::instrument(skip_all)]
433 pub async fn count_discover_projects(
434 pool: &PgPool,
435 search: Option<&str>,
436 category_slug: Option<&str>,
437 has_source_code: bool,
438 ) -> Result<i64> {
439 let search_term = normalize_search(search);
440 let has_search = search_term.is_some();
441 let short_query = search_term.as_deref().is_some_and(is_short_query);
442
443 let mut query = String::from(
444 r#"
445 SELECT COUNT(*)
446 FROM projects p
447 JOIN users u ON p.user_id = u.id
448 WHERE p.is_public = true AND u.is_sandbox = FALSE
449 "#,
450 );
451
452 if has_search {
453 if short_query {
454 query.push_str(PROJECT_SEARCH_CLAUSE_SHORT);
455 } else {
456 query.push_str(PROJECT_SEARCH_CLAUSE);
457 }
458 }
459
460 if category_slug.is_some() {
461 query.push_str(
462 " AND EXISTS (SELECT 1 FROM project_categories pc WHERE pc.id = p.category_id AND pc.slug = $2)",
463 );
464 }
465
466 if has_source_code {
467 query.push_str(" AND EXISTS (SELECT 1 FROM git_repos gr WHERE gr.project_id = p.id)");
468 }
469
470 let count: i64 = sqlx::query_scalar(&query)
471 .bind(search_term.as_deref().unwrap_or(""))
472 .bind(category_slug.unwrap_or(""))
473 .fetch_one(pool)
474 .await?;
475
476 Ok(count)
477 }
478
479 /// Get item type counts for discover page (items mode).
480 #[tracing::instrument(skip_all)]
481 pub async fn get_item_type_counts(
482 pool: &PgPool,
483 search: Option<&str>,
484 tag: Option<&str>,
485 min_price: Option<i32>,
486 max_price: Option<i32>,
487 ) -> Result<Vec<DbItemTypeCount>> {
488 let search_term = normalize_search(search);
489 let has_search = search_term.is_some();
490 let short_query = search_term.as_deref().is_some_and(is_short_query);
491
492 let mut query = String::from(
493 r#"
494 SELECT i.item_type as category, COUNT(*) as count
495 FROM items i
496 JOIN projects p ON i.project_id = p.id
497 JOIN users u ON p.user_id = u.id
498 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
499 "#,
500 );
501
502 if has_search {
503 if short_query {
504 query.push_str(ITEM_SEARCH_CLAUSE_SHORT);
505 } else {
506 query.push_str(ITEM_SEARCH_CLAUSE);
507 }
508 }
509
510 if tag.is_some() {
511 query.push_str(
512 r#" AND EXISTS (
513 SELECT 1 FROM item_tags it2
514 JOIN tags t2 ON t2.id = it2.tag_id
515 WHERE it2.item_id = i.id
516 AND (t2.slug = $2 OR t2.parent_id = (SELECT id FROM tags WHERE slug = $2))
517 )"#,
518 );
519 }
520
521 if min_price.is_some() {
522 query.push_str(" AND i.price_cents >= $3");
523 }
524
525 if max_price.is_some() {
526 query.push_str(" AND i.price_cents <= $4");
527 }
528
529 query.push_str(" GROUP BY i.item_type ORDER BY count DESC");
530
531 let counts = sqlx::query_as::<_, DbItemTypeCount>(&query)
532 .bind(search_term.as_deref().unwrap_or(""))
533 .bind(tag.unwrap_or(""))
534 .bind(min_price.unwrap_or(0))
535 .bind(max_price.unwrap_or(i32::MAX))
536 .fetch_all(pool)
537 .await?;
538
539 Ok(counts)
540 }
541
542 /// Get price range counts for the discover page sidebar (items mode only).
543 ///
544 /// Buckets are in cents: free (0), under $25 (1..2499), $25-$50 (2500..4999),
545 /// $50-$100 (5000..9999), over $100 (10000+). Uses PostgreSQL `FILTER (WHERE ...)`
546 /// to compute all five counts in a single table scan.
547 #[tracing::instrument(skip_all)]
548 pub async fn get_price_range_counts(
549 pool: &PgPool,
550 search: Option<&str>,
551 item_type: Option<ItemType>,
552 tag: Option<&str>,
553 ) -> Result<DbPriceRangeCounts> {
554 let search_term = normalize_search(search);
555 let has_search = search_term.is_some();
556 let short_query = search_term.as_deref().is_some_and(is_short_query);
557
558 let mut query = String::from(
559 r#"
560 SELECT
561 COUNT(*) FILTER (WHERE i.price_cents = 0) as free,
562 COUNT(*) FILTER (WHERE i.price_cents > 0 AND i.price_cents < 2500) as under_25,
563 COUNT(*) FILTER (WHERE i.price_cents >= 2500 AND i.price_cents < 5000) as range_25_50,
564 COUNT(*) FILTER (WHERE i.price_cents >= 5000 AND i.price_cents < 10000) as range_50_100,
565 COUNT(*) FILTER (WHERE i.price_cents >= 10000) as over_100
566 FROM items i
567 JOIN projects p ON i.project_id = p.id
568 JOIN users u ON p.user_id = u.id
569 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
570 "#,
571 );
572
573 if has_search {
574 if short_query {
575 query.push_str(ITEM_SEARCH_CLAUSE_SHORT);
576 } else {
577 query.push_str(ITEM_SEARCH_CLAUSE);
578 }
579 }
580
581 if item_type.is_some() {
582 query.push_str(" AND i.item_type = $2");
583 }
584
585 if tag.is_some() {
586 query.push_str(
587 r#" AND EXISTS (
588 SELECT 1 FROM item_tags it2
589 JOIN tags t2 ON t2.id = it2.tag_id
590 WHERE it2.item_id = i.id
591 AND (t2.slug = $3 OR t2.parent_id = (SELECT id FROM tags WHERE slug = $3))
592 )"#,
593 );
594 }
595
596 #[derive(FromRow)]
597 struct PriceRow {
598 free: Option<i64>,
599 under_25: Option<i64>,
600 range_25_50: Option<i64>,
601 range_50_100: Option<i64>,
602 over_100: Option<i64>,
603 }
604
605 let row = sqlx::query_as::<_, PriceRow>(&query)
606 .bind(search_term.as_deref().unwrap_or(""))
607 .bind(item_type.map(|t| t.to_string()).unwrap_or_default())
608 .bind(tag.unwrap_or(""))
609 .fetch_one(pool)
610 .await?;
611
612 Ok(DbPriceRangeCounts {
613 free: row.free.unwrap_or(0),
614 under_25: row.under_25.unwrap_or(0),
615 range_25_50: row.range_25_50.unwrap_or(0),
616 range_50_100: row.range_50_100.unwrap_or(0),
617 over_100: row.over_100.unwrap_or(0),
618 })
619 }
620
621 /// Get AI tier counts for the discover page sidebar (items mode only).
622 #[tracing::instrument(skip_all)]
623 pub async fn get_ai_tier_counts(
624 pool: &PgPool,
625 search: Option<&str>,
626 item_type: Option<ItemType>,
627 tag: Option<&str>,
628 ) -> Result<Vec<DbItemTypeCount>> {
629 let search_term = normalize_search(search);
630 let has_search = search_term.is_some();
631 let short_query = search_term.as_deref().is_some_and(is_short_query);
632
633 let mut query = String::from(
634 r#"
635 SELECT i.ai_tier as category, COUNT(*) as count
636 FROM items i
637 JOIN projects p ON i.project_id = p.id
638 JOIN users u ON p.user_id = u.id
639 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
640 "#,
641 );
642
643 if has_search {
644 if short_query {
645 query.push_str(ITEM_SEARCH_CLAUSE_SHORT);
646 } else {
647 query.push_str(ITEM_SEARCH_CLAUSE);
648 }
649 }
650
651 if item_type.is_some() {
652 query.push_str(" AND i.item_type = $2");
653 }
654
655 if tag.is_some() {
656 query.push_str(
657 r#" AND EXISTS (
658 SELECT 1 FROM item_tags it2
659 JOIN tags t2 ON t2.id = it2.tag_id
660 WHERE it2.item_id = i.id
661 AND (t2.slug = $3 OR t2.parent_id = (SELECT id FROM tags WHERE slug = $3))
662 )"#,
663 );
664 }
665
666 query.push_str(" GROUP BY i.ai_tier ORDER BY count DESC");
667
668 let counts = sqlx::query_as::<_, DbItemTypeCount>(&query)
669 .bind(search_term.as_deref().unwrap_or(""))
670 .bind(item_type.map(|t| t.to_string()).unwrap_or_default())
671 .bind(tag.unwrap_or(""))
672 .fetch_all(pool)
673 .await?;
674
675 Ok(counts)
676 }
677
678 /// A search suggestion with a category label (tag, project, or creator).
679 #[derive(Debug, FromRow)]
680 pub struct DbSearchSuggestion {
681 pub label: String,
682 pub category: String,
683 pub url: String,
684 }
685
686 /// Return combined search suggestions from tags, projects, and creators.
687 /// Uses ILIKE prefix match for fast results, limited to 8 total.
688 #[tracing::instrument(skip_all)]
689 pub async fn search_suggestions(pool: &PgPool, query: &str) -> Result<Vec<DbSearchSuggestion>> {
690 let q = query.trim();
691 if q.is_empty() {
692 return Ok(vec![]);
693 }
694
695 let pattern = format!("{}%", q.replace('\\', "\\\\").replace('%', "\\%").replace('_', "\\_"));
696
697 let rows = sqlx::query_as::<_, DbSearchSuggestion>(
698 r#"
699 (
700 SELECT name AS label, 'tag' AS category, '/discover?mode=items&tag=' || slug AS url
701 FROM tags
702 WHERE name ILIKE $1
703 ORDER BY name
704 LIMIT 3
705 )
706 UNION ALL
707 (
708 SELECT title AS label, 'project' AS category, '/p/' || slug AS url
709 FROM projects
710 WHERE is_public = true AND title ILIKE $1
711 ORDER BY title
712 LIMIT 3
713 )
714 UNION ALL
715 (
716 SELECT username AS label, 'creator' AS category, '/u/' || username AS url
717 FROM users
718 WHERE is_sandbox = false AND suspended_at IS NULL AND deactivated_at IS NULL AND username ILIKE $1
719 ORDER BY username
720 LIMIT 2
721 )
722 "#,
723 )
724 .bind(&pattern)
725 .fetch_all(pool)
726 .await?;
727
728 Ok(rows)
729 }
730
731 #[cfg(test)]
732 mod tests {
733 use super::*;
734
735 #[test]
736 fn normalize_search_none() {
737 assert_eq!(normalize_search(None), None);
738 }
739
740 #[test]
741 fn normalize_search_empty() {
742 assert_eq!(normalize_search(Some("")), None);
743 }
744
745 #[test]
746 fn normalize_search_whitespace_only() {
747 assert_eq!(normalize_search(Some(" ")), None);
748 }
749
750 #[test]
751 fn normalize_search_trims() {
752 assert_eq!(normalize_search(Some(" hello ")), Some("hello".to_string()));
753 }
754
755 #[test]
756 fn normalize_search_truncates_long() {
757 let long = "a".repeat(300);
758 let result = normalize_search(Some(&long)).unwrap();
759 assert_eq!(result.len(), MAX_SEARCH_LEN);
760 }
761
762 #[test]
763 fn normalize_search_truncates_at_char_boundary() {
764 // Multi-byte chars: each is 2 bytes. 150 chars = 300 bytes.
765 let long: String = std::iter::repeat_n('\u{00E9}', 150).collect();
766 let result = normalize_search(Some(&long)).unwrap();
767 assert!(result.len() <= MAX_SEARCH_LEN);
768 // Must end at a valid char boundary (no panic on indexing)
769 assert!(result.is_char_boundary(result.len()));
770 }
771
772 #[test]
773 fn normalize_search_exact_limit() {
774 let exact = "b".repeat(MAX_SEARCH_LEN);
775 assert_eq!(normalize_search(Some(&exact)), Some(exact));
776 }
777
778 #[test]
779 fn is_short_query_empty() {
780 assert!(is_short_query(""));
781 }
782
783 #[test]
784 fn is_short_query_two_chars() {
785 assert!(is_short_query("ab"));
786 }
787
788 #[test]
789 fn is_short_query_three_chars() {
790 assert!(!is_short_query("abc"));
791 }
792
793 #[test]
794 fn append_filters_no_filters() {
795 let filters = DiscoverFilters {
796 search: None, item_type: None, tag: None,
797 min_price: None, max_price: None, sort_by: None, ai_tier: None,
798 };
799 let mut q = String::from("SELECT 1 WHERE true");
800 append_item_discover_filters(&mut q, &filters, false, false);
801 assert_eq!(q, "SELECT 1 WHERE true");
802 }
803
804 #[test]
805 fn append_filters_with_item_type() {
806 let filters = DiscoverFilters {
807 search: None, item_type: Some(ItemType::Audio), tag: None,
808 min_price: None, max_price: None, sort_by: None, ai_tier: None,
809 };
810 let mut q = String::new();
811 append_item_discover_filters(&mut q, &filters, false, false);
812 assert!(q.contains("i.item_type = $2"));
813 }
814
815 #[test]
816 fn append_filters_search_uses_short_clause() {
817 let filters = DiscoverFilters {
818 search: Some("ab"), item_type: None, tag: None,
819 min_price: None, max_price: None, sort_by: None, ai_tier: None,
820 };
821 let mut q = String::new();
822 append_item_discover_filters(&mut q, &filters, true, true);
823 assert!(q.contains("ILIKE"));
824 assert!(!q.contains("i.title % $1"));
825 }
826
827 #[test]
828 fn append_filters_search_uses_trigram_clause() {
829 let filters = DiscoverFilters {
830 search: Some("hello"), 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, false);
835 assert!(q.contains("i.title % $1"));
836 }
837 }
838