Skip to main content

max / goingson

27.9 KB · 833 lines History Blame Raw
1 //! SQLite implementation of the SearchRepository.
2 //!
3 //! Provides full-text search across tasks, emails, and events using SQLite FTS5.
4 //! Search results are ranked by relevance and include preview snippets.
5 //! Supports structured filters like `is:overdue`, `priority:high`, `in:Project`.
6
7 use async_trait::async_trait;
8 use sqlx::SqlitePool;
9 use uuid::Uuid;
10
11 use goingson_core::{
12 search_parser::IsFilter, CoreError, Priority, ProjectId, Result, SearchQuery,
13 SearchRepository, SearchResultItem, SearchResultType, UserId,
14 };
15
16 use crate::utils::{escape_like, format_datetime, parse_uuid};
17
18 /// SQLite-backed implementation of [`SearchRepository`].
19 ///
20 /// Provides full-text search via FTS5 across tasks, emails, events, and projects.
21 /// Supports structured filters (`is:overdue`, `priority:high`, `in:ProjectName`)
22 /// combined with text search.
23 pub struct SqliteSearchRepository {
24 pool: SqlitePool,
25 }
26
27 impl SqliteSearchRepository {
28 /// Creates a new repository instance with the given connection pool.
29 #[tracing::instrument(skip_all)]
30 pub fn new(pool: SqlitePool) -> Self {
31 Self { pool }
32 }
33 }
34
35 #[async_trait]
36 impl SearchRepository for SqliteSearchRepository {
37 #[tracing::instrument(skip_all)]
38 async fn search(&self, user_id: UserId, query: SearchQuery) -> Result<(Vec<SearchResultItem>, usize)> {
39 // If no text and no filters, return empty
40 let has_text = !query.query.trim().is_empty();
41 let has_filters = !query.is_filters.is_empty()
42 || query.priority.is_some()
43 || query.project_name.is_some()
44 || query.project_id.is_some()
45 || !query.tags_include.is_empty()
46 || !query.tags_exclude.is_empty()
47 || query.date_from.is_some()
48 || query.date_to.is_some();
49
50 if !has_text && !has_filters {
51 return Ok((vec![], 0));
52 }
53
54 let user_id_str = user_id.to_string();
55 // Clamp to non-negative: a negative limit/offset would otherwise be
56 // interpolated as `LIMIT -N`, which SQLite treats as unbounded.
57 let limit = query.limit.unwrap_or(50).max(0);
58 let offset = query.offset.unwrap_or(0).max(0);
59 let per_type_cap = offset.saturating_add(limit);
60
61 // Prepare search term for FTS5 (escape special characters and add prefix matching)
62 let search_term = if has_text {
63 Some(prepare_fts5_query(&query.query))
64 } else {
65 None
66 };
67
68 // Determine which types to search
69 let search_tasks = query
70 .types
71 .as_ref()
72 .is_none_or(|t| t.contains(&SearchResultType::Task));
73 let search_emails = query
74 .types
75 .as_ref()
76 .is_none_or(|t| t.contains(&SearchResultType::Email))
77 && can_search_emails(&query);
78 let search_projects = query
79 .types
80 .as_ref()
81 .is_none_or(|t| t.contains(&SearchResultType::Project))
82 && can_search_projects(&query);
83 let search_events = query
84 .types
85 .as_ref()
86 .is_none_or(|t| t.contains(&SearchResultType::Event))
87 && can_search_events(&query);
88 let search_contacts = query
89 .types
90 .as_ref()
91 .is_none_or(|t| t.contains(&SearchResultType::Contact))
92 && can_search_contacts(&query);
93
94 // Run all applicable FTS queries in parallel with capped per-type limits
95 let (task_r, email_r, project_r, event_r, contact_r) = tokio::join!(
96 async {
97 if search_tasks { search_tasks_fts(&self.pool, &user_id_str, &query, search_term.as_deref(), per_type_cap).await } else { Ok(vec![]) }
98 },
99 async {
100 if search_emails { search_emails_fts(&self.pool, &user_id_str, &query, search_term.as_deref(), per_type_cap).await } else { Ok(vec![]) }
101 },
102 async {
103 if search_projects { search_projects_fts(&self.pool, &user_id_str, &query, search_term.as_deref(), per_type_cap).await } else { Ok(vec![]) }
104 },
105 async {
106 if search_events { search_events_fts(&self.pool, &user_id_str, &query, search_term.as_deref(), per_type_cap).await } else { Ok(vec![]) }
107 },
108 async {
109 if search_contacts { search_contacts_fts(&self.pool, &user_id_str, search_term.as_deref(), per_type_cap).await } else { Ok(vec![]) }
110 },
111 );
112
113 let mut results = Vec::new();
114 results.extend(task_r?);
115 results.extend(email_r?);
116 results.extend(project_r?);
117 results.extend(event_r?);
118 results.extend(contact_r?);
119
120 // Sort by rank (higher is better)
121 results.sort_by(|a, b| {
122 b.rank
123 .partial_cmp(&a.rank)
124 .unwrap_or(std::cmp::Ordering::Equal)
125 });
126
127 // Total is a LOWER BOUND, not an exact match count: each per-type query
128 // is capped at `per_type_cap` (offset + limit), so a type that saturates
129 // its cap contributes only that many rows here. This is deliberate —
130 // computing an exact total would mean five extra COUNT queries on every
131 // keystroke-driven search. Callers use it only to decide whether another
132 // page exists, for which a lower bound of `> offset + limit` is correct.
133 let total = results.len();
134
135 // Apply pagination
136 let results: Vec<_> = results
137 .into_iter()
138 .skip(offset as usize)
139 .take(limit as usize)
140 .collect();
141
142 Ok((results, total))
143 }
144 }
145
146 /// Check if the query can apply to emails (doesn't have task-specific filters).
147 fn can_search_emails(query: &SearchQuery) -> bool {
148 // Emails don't have status, priority, or tags, so skip if these filters are set
149 query.is_filters.iter().all(|f| matches!(f, IsFilter::Snoozed)) // emails don't have most is: filters
150 && query.priority.is_none()
151 && query.tags_include.is_empty()
152 && query.tags_exclude.is_empty()
153 // If is_filters contains task-specific filters, skip emails
154 && !query.is_filters.iter().any(|f| {
155 matches!(
156 f,
157 IsFilter::Overdue
158 | IsFilter::Today
159 | IsFilter::Tomorrow
160 | IsFilter::ThisWeek
161 | IsFilter::Pending
162 | IsFilter::Started
163 | IsFilter::Completed
164 | IsFilter::Waiting
165 )
166 })
167 }
168
169 /// Check if the query can apply to projects.
170 fn can_search_projects(query: &SearchQuery) -> bool {
171 // Projects don't have most filters
172 query.is_filters.is_empty()
173 && query.priority.is_none()
174 && query.tags_include.is_empty()
175 && query.tags_exclude.is_empty()
176 && query.project_id.is_none()
177 && query.project_name.is_none()
178 }
179
180 /// Check if the query can apply to events.
181 fn can_search_events(query: &SearchQuery) -> bool {
182 // Events have date filters but not status/priority/tags
183 query.priority.is_none()
184 && query.tags_include.is_empty()
185 && query.tags_exclude.is_empty()
186 && !query.is_filters.iter().any(|f| {
187 matches!(
188 f,
189 IsFilter::Pending
190 | IsFilter::Started
191 | IsFilter::Completed
192 | IsFilter::Waiting
193 | IsFilter::Snoozed
194 )
195 })
196 }
197
198 /// Prepare a search query for FTS5
199 fn prepare_fts5_query(query: &str) -> String {
200 // Split into words and add prefix matching
201 query
202 .split_whitespace()
203 .filter_map(|word| {
204 // Escape special FTS5 characters
205 let escaped = word
206 .replace('"', "\"\"")
207 .replace(['*', '(', ')', ':'], "");
208 if escaped.is_empty() {
209 None
210 } else {
211 Some(format!("\"{}\"*", escaped))
212 }
213 })
214 .collect::<Vec<_>>()
215 .join(" ")
216 }
217
218 /// Build WHERE clauses for `is:` filters on tasks.
219 fn build_is_filter_clauses(is_filters: &[IsFilter]) -> Vec<String> {
220 is_filters
221 .iter()
222 .map(|f| {
223 match f {
224 IsFilter::Overdue => "(t.due IS NOT NULL AND t.due < datetime('now'))".to_string(),
225 // Today/Tomorrow must convert the UTC-stored due to the local
226 // calendar day, so date(..., 'localtime') is required and these
227 // two predicates are intentionally non-sargable (a bare-column
228 // compare would test the UTC date, not the user's local date).
229 IsFilter::Today => "(t.due IS NOT NULL AND date(t.due, 'localtime') = date('now', 'localtime'))".to_string(),
230 IsFilter::Tomorrow => "(t.due IS NOT NULL AND date(t.due, 'localtime') = date('now', '+1 day', 'localtime'))".to_string(),
231 IsFilter::ThisWeek => {
232 // Due on or before end of this week (Sunday).
233 // weekday 1 = next Monday; < Monday midnight = through Sunday 23:59:59.
234 "(t.due IS NOT NULL AND t.due < datetime('now', 'weekday 1'))".to_string()
235 }
236 IsFilter::Snoozed => "(t.snoozed_until IS NOT NULL AND t.snoozed_until > datetime('now'))".to_string(),
237 IsFilter::Pending => "t.status = 'Pending'".to_string(),
238 IsFilter::Started => "t.status = 'Started'".to_string(),
239 IsFilter::Completed => "t.status = 'Completed'".to_string(),
240 IsFilter::Waiting => "t.waiting_for_response = 1".to_string(),
241 }
242 })
243 .collect()
244 }
245
246 /// Append the shared `project:` filters (by ID, then by partial name) to a search
247 /// query being assembled, binding their parameters and advancing `param_idx`.
248 /// `alias` is the SQL table alias the `project_id` column hangs off (e.g. `t`, `e`, `ev`).
249 fn push_project_filters(
250 sql: &mut String,
251 params: &mut Vec<String>,
252 param_idx: &mut i32,
253 query: &SearchQuery,
254 alias: &str,
255 ) {
256 // Project filter by ID
257 if let Some(pid) = &query.project_id {
258 sql.push_str(&format!(" AND {}.project_id = ${}", alias, param_idx));
259 params.push(pid.to_string());
260 *param_idx += 1;
261 }
262
263 // Project filter by name (partial match)
264 if let Some(pname) = &query.project_name {
265 sql.push_str(&format!(
266 " AND {}.project_id IN (SELECT id FROM projects WHERE name LIKE ${} ESCAPE '\\' COLLATE NOCASE)",
267 alias, param_idx
268 ));
269 params.push(format!("%{}%", escape_like(pname)));
270 *param_idx += 1;
271 }
272 }
273
274 /// Run an assembled FTS search: bind the optional MATCH term and `user_id`, then the
275 /// positional `params`, and fetch typed rows. When `search_term` is `None` the term
276 /// bind is skipped (the direct, filter-only query path). Every `search_*_fts` helper
277 /// shares this bind-and-fetch tail; only the row type and result mapping differ.
278 async fn run_fts_query<R>(
279 pool: &SqlitePool,
280 sql: &str,
281 search_term: Option<&str>,
282 user_id: &str,
283 params: Vec<String>,
284 ) -> Result<Vec<R>>
285 where
286 R: for<'r> sqlx::FromRow<'r, sqlx::sqlite::SqliteRow> + Send + Unpin,
287 {
288 let mut db_query = sqlx::query_as::<_, R>(sql);
289
290 if let Some(term) = search_term {
291 db_query = db_query.bind(term).bind(user_id);
292 } else {
293 db_query = db_query.bind(user_id);
294 }
295
296 for param in params {
297 db_query = db_query.bind(param);
298 }
299
300 db_query.fetch_all(pool).await.map_err(CoreError::database)
301 }
302
303 /// Searches tasks via FTS5 MATCH with BM25 relevance ranking, or direct query when
304 /// no search text is provided. Applies optional filters: `is:` status/date, project
305 /// (by ID or name), priority, tag include/exclude, and date range on the due field.
306 async fn search_tasks_fts(
307 pool: &SqlitePool,
308 user_id: &str,
309 query: &SearchQuery,
310 search_term: Option<&str>,
311 per_type_limit: i64,
312 ) -> Result<Vec<SearchResultItem>> {
313 #[derive(sqlx::FromRow)]
314 struct Row {
315 id: String,
316 description: String,
317 project_id: Option<String>,
318 project_name: Option<String>,
319 rank: f64,
320 }
321
322 // Build dynamic query with optional filters
323 // When there's no search term, we use a direct query instead of FTS
324 let (base_sql, uses_fts) = if search_term.is_some() {
325 (
326 r#"
327 SELECT
328 t.id,
329 t.description,
330 t.project_id,
331 p.name as project_name,
332 bm25(tasks_fts) as rank
333 FROM tasks_fts
334 JOIN tasks t ON tasks_fts.id = t.id
335 LEFT JOIN projects p ON t.project_id = p.id
336 WHERE tasks_fts MATCH $1
337 AND tasks_fts.user_id = $2
338 AND t.status != 'Deleted'
339 "#
340 .to_string(),
341 true,
342 )
343 } else {
344 (
345 r#"
346 SELECT
347 t.id,
348 t.description,
349 t.project_id,
350 p.name as project_name,
351 0.0 as rank
352 FROM tasks t
353 LEFT JOIN projects p ON t.project_id = p.id
354 WHERE t.user_id = $1
355 AND t.status != 'Deleted'
356 "#
357 .to_string(),
358 false,
359 )
360 };
361
362 let mut sql = base_sql;
363 let mut params: Vec<String> = Vec::new();
364 let mut param_idx = if uses_fts { 3 } else { 2 };
365
366 // Add is: filter clauses
367 let is_clauses = build_is_filter_clauses(&query.is_filters);
368 for clause in is_clauses {
369 sql.push_str(&format!(" AND {}", clause));
370 }
371
372 push_project_filters(&mut sql, &mut params, &mut param_idx, query, "t");
373
374 // Priority filter
375 if let Some(priority) = &query.priority {
376 let priority_str = match priority {
377 Priority::High => "High",
378 Priority::Medium => "Medium",
379 Priority::Low => "Low",
380 };
381 sql.push_str(&format!(" AND t.priority = ${}", param_idx));
382 params.push(priority_str.to_string());
383 param_idx += 1;
384 }
385
386 // Tag filters (include) — tags stored as JSON array in tasks.tags column
387 for tag in &query.tags_include {
388 sql.push_str(&format!(" AND t.tags LIKE ${} ESCAPE '\\'", param_idx));
389 params.push(format!("%\"{}\"%" , escape_like(tag)));
390 param_idx += 1;
391 }
392
393 // Tag filters (exclude)
394 for tag in &query.tags_exclude {
395 sql.push_str(&format!(" AND t.tags NOT LIKE ${} ESCAPE '\\'", param_idx));
396 params.push(format!("%\"{}\"%" , escape_like(tag)));
397 param_idx += 1;
398 }
399
400 // Date filters. Compare the stored `due` text directly (sortable
401 // "%Y-%m-%d %H:%M:%S" format) and bind the bound in the same format, so the
402 // predicate is sargable against the due index instead of wrapping the column
403 // in datetime().
404 if let Some(df) = &query.date_from {
405 sql.push_str(&format!(
406 " AND (t.due IS NULL OR t.due >= ${}) ",
407 param_idx
408 ));
409 params.push(format_datetime(df));
410 param_idx += 1;
411 }
412 if let Some(dt) = &query.date_to {
413 sql.push_str(&format!(
414 " AND (t.due IS NULL OR t.due <= ${}) ",
415 param_idx
416 ));
417 params.push(format_datetime(dt));
418 }
419
420 sql.push_str(&format!(" ORDER BY rank LIMIT {}", per_type_limit));
421
422 let rows: Vec<Row> = run_fts_query(pool, &sql, search_term, user_id, params).await?;
423
424 rows.into_iter()
425 .map(|row| {
426 Ok(SearchResultItem {
427 id: parse_uuid(&row.id)?,
428 result_type: SearchResultType::Task,
429 title: row.description,
430 snippet: None,
431 project_id: row
432 .project_id
433 .as_ref()
434 .and_then(|s| Uuid::parse_str(s).ok().map(ProjectId::from)),
435 project_name: row.project_name,
436 rank: -row.rank, // BM25 returns negative values, lower is better
437 })
438 })
439 .collect()
440 }
441
442 /// Searches emails by subject and body via FTS5 MATCH with BM25 ranking.
443 /// Requires a search term (returns empty for filter-only queries). Applies optional
444 /// project filter (by ID or name) and date range filter on the email date field.
445 async fn search_emails_fts(
446 pool: &SqlitePool,
447 user_id: &str,
448 query: &SearchQuery,
449 search_term: Option<&str>,
450 per_type_limit: i64,
451 ) -> Result<Vec<SearchResultItem>> {
452 #[derive(sqlx::FromRow)]
453 struct Row {
454 id: String,
455 subject: String,
456 body: String,
457 project_id: Option<String>,
458 project_name: Option<String>,
459 rank: f64,
460 }
461
462 // Email search requires FTS text for now
463 let search_term = match search_term {
464 Some(t) => t,
465 None => return Ok(vec![]),
466 };
467
468 // Build dynamic query with optional filters
469 let mut sql = String::from(
470 r#"
471 SELECT
472 e.id,
473 e.subject,
474 e.body,
475 e.project_id,
476 p.name as project_name,
477 bm25(emails_fts) as rank
478 FROM emails_fts
479 JOIN emails e ON emails_fts.id = e.id
480 LEFT JOIN projects p ON e.project_id = p.id
481 WHERE emails_fts MATCH $1
482 AND emails_fts.user_id = $2
483 "#,
484 );
485
486 let mut params: Vec<String> = Vec::new();
487 let mut param_idx = 3;
488
489 push_project_filters(&mut sql, &mut params, &mut param_idx, query, "e");
490
491 // Date filters. Compare the stored `received_at` text directly (it is written
492 // in the sortable "%Y-%m-%d %H:%M:%S" format, so lexicographic order is
493 // chronological) rather than wrapping it in datetime(): the old code queried a
494 // nonexistent `e.date` column (a runtime error on any dated search) and the
495 // datetime() wrap was also non-sargable. Bind the bound in the same format so
496 // the string comparison is correct and can use idx_emails_received_at.
497 if let Some(df) = &query.date_from {
498 sql.push_str(&format!(" AND e.received_at >= ${}", param_idx));
499 params.push(format_datetime(df));
500 param_idx += 1;
501 }
502 if let Some(dt) = &query.date_to {
503 sql.push_str(&format!(" AND e.received_at <= ${}", param_idx));
504 params.push(format_datetime(dt));
505 }
506
507 sql.push_str(&format!(" ORDER BY rank LIMIT {}", per_type_limit));
508
509 let rows: Vec<Row> = run_fts_query(pool, &sql, Some(search_term), user_id, params).await?;
510
511 rows.into_iter()
512 .map(|row| {
513 // Create a snippet from the body
514 let snippet = create_snippet(&row.body, 150);
515
516 Ok(SearchResultItem {
517 id: parse_uuid(&row.id)?,
518 result_type: SearchResultType::Email,
519 title: row.subject,
520 snippet: Some(snippet),
521 project_id: row
522 .project_id
523 .as_ref()
524 .and_then(|s| Uuid::parse_str(s).ok().map(ProjectId::from)),
525 project_name: row.project_name,
526 rank: -row.rank,
527 })
528 })
529 .collect()
530 }
531
532 /// Searches projects by name and description via FTS5 MATCH with BM25 ranking.
533 /// Requires a search term (returns empty for filter-only queries). Only applies
534 /// date range filters (on `created_at`); skipped entirely when structured filters are present.
535 async fn search_projects_fts(
536 pool: &SqlitePool,
537 user_id: &str,
538 query: &SearchQuery,
539 search_term: Option<&str>,
540 per_type_limit: i64,
541 ) -> Result<Vec<SearchResultItem>> {
542 #[derive(sqlx::FromRow)]
543 struct Row {
544 id: String,
545 name: String,
546 description: String,
547 rank: f64,
548 }
549
550 // Project search requires FTS text
551 let search_term = match search_term {
552 Some(t) => t,
553 None => return Ok(vec![]),
554 };
555
556 // Build dynamic query with optional filters
557 let mut sql = String::from(
558 r#"
559 SELECT
560 p.id,
561 p.name,
562 p.description,
563 bm25(projects_fts) as rank
564 FROM projects_fts
565 JOIN projects p ON projects_fts.id = p.id
566 WHERE projects_fts MATCH $1
567 AND projects_fts.user_id = $2
568 "#,
569 );
570
571 let mut params: Vec<String> = Vec::new();
572 let mut param_idx = 3;
573
574 // Date filters (on created_at). Sargable bare-column comparison against a
575 // same-format bound (see the task/email date filters).
576 if let Some(df) = &query.date_from {
577 sql.push_str(&format!(
578 " AND p.created_at >= ${}",
579 param_idx
580 ));
581 params.push(format_datetime(df));
582 param_idx += 1;
583 }
584 if let Some(dt) = &query.date_to {
585 sql.push_str(&format!(
586 " AND p.created_at <= ${}",
587 param_idx
588 ));
589 params.push(format_datetime(dt));
590 }
591
592 sql.push_str(&format!(" ORDER BY rank LIMIT {}", per_type_limit));
593
594 let rows: Vec<Row> = run_fts_query(pool, &sql, Some(search_term), user_id, params).await?;
595
596 rows.into_iter()
597 .map(|row| {
598 let snippet = if !row.description.is_empty() {
599 Some(create_snippet(&row.description, 150))
600 } else {
601 None
602 };
603
604 Ok(SearchResultItem {
605 id: parse_uuid(&row.id)?,
606 result_type: SearchResultType::Project,
607 title: row.name,
608 snippet,
609 project_id: None,
610 project_name: None,
611 rank: -row.rank,
612 })
613 })
614 .collect()
615 }
616
617 /// Searches events via FTS5 MATCH with BM25 ranking, or direct query for filter-only
618 /// searches. Supports time-based `is:` filters (today, tomorrow, this_week, overdue) on
619 /// `start_time`, project filter (by ID or name), and date range on `start_time`.
620 async fn search_events_fts(
621 pool: &SqlitePool,
622 user_id: &str,
623 query: &SearchQuery,
624 search_term: Option<&str>,
625 per_type_limit: i64,
626 ) -> Result<Vec<SearchResultItem>> {
627 #[derive(sqlx::FromRow)]
628 struct Row {
629 id: String,
630 title: String,
631 description: String,
632 project_id: Option<String>,
633 project_name: Option<String>,
634 rank: f64,
635 }
636
637 // Build query based on whether we have FTS text
638 let (base_sql, uses_fts) = if search_term.is_some() {
639 (
640 r#"
641 SELECT
642 ev.id,
643 ev.title,
644 ev.description,
645 ev.project_id,
646 p.name as project_name,
647 bm25(events_fts) as rank
648 FROM events_fts
649 JOIN events ev ON events_fts.id = ev.id
650 LEFT JOIN projects p ON ev.project_id = p.id
651 WHERE events_fts MATCH $1
652 AND events_fts.user_id = $2
653 "#
654 .to_string(),
655 true,
656 )
657 } else {
658 // Filter-only search for events (e.g., is:today for events)
659 (
660 r#"
661 SELECT
662 ev.id,
663 ev.title,
664 ev.description,
665 ev.project_id,
666 p.name as project_name,
667 0.0 as rank
668 FROM events ev
669 LEFT JOIN projects p ON ev.project_id = p.id
670 WHERE ev.user_id = $1
671 "#
672 .to_string(),
673 false,
674 )
675 };
676
677 let mut sql = base_sql;
678 let mut params: Vec<String> = Vec::new();
679 let mut param_idx = if uses_fts { 3 } else { 2 };
680
681 // is: filters for events (only time-based ones apply)
682 for f in &query.is_filters {
683 match f {
684 IsFilter::Today => {
685 sql.push_str(" AND date(ev.start_time, 'localtime') = date('now', 'localtime')");
686 }
687 IsFilter::Tomorrow => {
688 sql.push_str(
689 " AND date(ev.start_time, 'localtime') = date('now', '+1 day', 'localtime')",
690 );
691 }
692 IsFilter::ThisWeek => {
693 sql.push_str(" AND ev.start_time < datetime('now', 'weekday 1')");
694 }
695 IsFilter::Overdue => {
696 sql.push_str(" AND ev.start_time < datetime('now')");
697 }
698 _ => {} // Other is: filters don't apply to events
699 }
700 }
701
702 push_project_filters(&mut sql, &mut params, &mut param_idx, query, "ev");
703
704 // Date filters. Sargable bare-column comparison against a same-format bound.
705 if let Some(df) = &query.date_from {
706 sql.push_str(&format!(
707 " AND ev.start_time >= ${}",
708 param_idx
709 ));
710 params.push(format_datetime(df));
711 param_idx += 1;
712 }
713 if let Some(dt) = &query.date_to {
714 sql.push_str(&format!(
715 " AND ev.start_time <= ${}",
716 param_idx
717 ));
718 params.push(format_datetime(dt));
719 }
720
721 sql.push_str(&format!(" ORDER BY rank LIMIT {}", per_type_limit));
722
723 let rows: Vec<Row> = run_fts_query(pool, &sql, search_term, user_id, params).await?;
724
725 rows.into_iter()
726 .map(|row| {
727 let snippet = if !row.description.is_empty() {
728 Some(create_snippet(&row.description, 150))
729 } else {
730 None
731 };
732
733 Ok(SearchResultItem {
734 id: parse_uuid(&row.id)?,
735 result_type: SearchResultType::Event,
736 title: row.title,
737 snippet,
738 project_id: row
739 .project_id
740 .as_ref()
741 .and_then(|s| Uuid::parse_str(s).ok().map(ProjectId::from)),
742 project_name: row.project_name,
743 rank: -row.rank,
744 })
745 })
746 .collect()
747 }
748
749 /// Check if the query can apply to contacts.
750 fn can_search_contacts(query: &SearchQuery) -> bool {
751 // Contacts don't have most filters (similar to projects)
752 query.is_filters.is_empty()
753 && query.priority.is_none()
754 && query.tags_include.is_empty()
755 && query.tags_exclude.is_empty()
756 && query.project_id.is_none()
757 && query.project_name.is_none()
758 }
759
760 /// Searches contacts by display name and company via FTS5 MATCH with BM25 ranking.
761 /// Requires a search term (returns empty for filter-only queries). No additional
762 /// filters are applied; skipped entirely when structured filters are present.
763 async fn search_contacts_fts(
764 pool: &SqlitePool,
765 user_id: &str,
766 search_term: Option<&str>,
767 per_type_limit: i64,
768 ) -> Result<Vec<SearchResultItem>> {
769 #[derive(sqlx::FromRow)]
770 struct Row {
771 id: String,
772 display_name: String,
773 company: Option<String>,
774 rank: f64,
775 }
776
777 // Contact search requires FTS text
778 let search_term = match search_term {
779 Some(t) => t,
780 None => return Ok(vec![]),
781 };
782
783 let sql = format!(r#"
784 SELECT
785 c.id,
786 c.display_name,
787 c.company,
788 bm25(contacts_fts) as rank
789 FROM contacts_fts
790 JOIN contacts c ON contacts_fts.id = c.id
791 WHERE contacts_fts MATCH $1
792 AND contacts_fts.user_id = $2
793 ORDER BY rank
794 LIMIT {}
795 "#, per_type_limit);
796
797 let rows: Vec<Row> = run_fts_query(pool, &sql, Some(search_term), user_id, Vec::new()).await?;
798
799 rows.into_iter()
800 .map(|row| {
801 let snippet = row.company.as_deref()
802 .filter(|c| !c.is_empty())
803 .map(|c| c.to_string());
804
805 Ok(SearchResultItem {
806 id: parse_uuid(&row.id)?,
807 result_type: SearchResultType::Contact,
808 title: row.display_name,
809 snippet,
810 project_id: None,
811 project_name: None,
812 rank: -row.rank,
813 })
814 })
815 .collect()
816 }
817
818 /// Create a snippet from text, truncating to max_len characters
819 fn create_snippet(text: &str, max_len: usize) -> String {
820 let text = text.trim();
821 if text.len() <= max_len {
822 text.to_string()
823 } else {
824 let mut result = text.chars().take(max_len).collect::<String>();
825 // Try to break at a word boundary
826 if let Some(last_space) = result.rfind(' ') {
827 result.truncate(last_space);
828 }
829 result.push_str("...");
830 result
831 }
832 }
833