bones_core/db/
query.rs

1//! `SQLite` query helpers for the projection database.
2//!
3//! Provides typed Rust structs and composable query functions for common
4//! access patterns: list/filter items, get by ID, search FTS5, retrieve
5//! dependencies, labels, assignees, and comments.
6//!
7//! All functions take a shared `&Connection` reference and return
8//! `anyhow::Result<T>` with typed structs (never raw rows).
9
10use anyhow::{Context, Result, bail};
11use rusqlite::{Connection, params, params_from_iter};
12use std::collections::HashMap;
13use std::fmt::{self, Write as _};
14use std::str::FromStr;
15
16// ---------------------------------------------------------------------------
17// Result types
18// ---------------------------------------------------------------------------
19
20/// A projected work item row from the `items` table.
21#[derive(Debug, Clone, PartialEq, Eq)]
22pub struct QueryItem {
23    pub item_id: String,
24    pub title: String,
25    pub description: Option<String>,
26    pub kind: String,
27    pub state: String,
28    pub urgency: String,
29    pub size: Option<String>,
30    pub parent_id: Option<String>,
31    pub compact_summary: Option<String>,
32    pub is_deleted: bool,
33    pub deleted_at_us: Option<i64>,
34    pub search_labels: String,
35    pub created_at_us: i64,
36    pub updated_at_us: i64,
37}
38
39/// A comment attached to a work item.
40#[derive(Debug, Clone, PartialEq, Eq)]
41pub struct QueryComment {
42    pub comment_id: i64,
43    pub item_id: String,
44    pub event_hash: String,
45    pub author: String,
46    pub body: String,
47    pub created_at_us: i64,
48}
49
50/// A dependency edge between two items.
51#[derive(Debug, Clone, PartialEq, Eq)]
52pub struct QueryDependency {
53    pub item_id: String,
54    pub depends_on_item_id: String,
55    pub link_type: String,
56    pub created_at_us: i64,
57}
58
59/// A label attached to an item.
60#[derive(Debug, Clone, PartialEq, Eq)]
61pub struct QueryLabel {
62    pub item_id: String,
63    pub label: String,
64    pub created_at_us: i64,
65}
66
67/// Global label inventory row with usage count.
68#[derive(Debug, Clone, PartialEq, Eq)]
69pub struct LabelCount {
70    pub name: String,
71    pub count: usize,
72}
73
74/// An assignee of an item.
75#[derive(Debug, Clone, PartialEq, Eq)]
76pub struct QueryAssignee {
77    pub item_id: String,
78    pub agent: String,
79    pub created_at_us: i64,
80}
81
82/// An FTS5 search hit with BM25 relevance score.
83#[derive(Debug, Clone, PartialEq)]
84pub struct SearchHit {
85    pub item_id: String,
86    pub title: String,
87    pub rank: f64,
88}
89
90/// Aggregate counters for project-level stats used by reporting commands.
91#[derive(Debug, Clone, PartialEq, Eq)]
92pub struct ProjectStats {
93    /// Open items by state (excluding deleted).
94    pub by_state: HashMap<String, usize>,
95    /// Open items by kind (excluding deleted).
96    pub by_kind: HashMap<String, usize>,
97    /// Open items by urgency (excluding deleted).
98    pub by_urgency: HashMap<String, usize>,
99    /// Events by type from the projected event tracker (empty when unavailable).
100    pub events_by_type: HashMap<String, usize>,
101    /// Events by agent from the projected event tracker (empty when unavailable).
102    pub events_by_agent: HashMap<String, usize>,
103}
104
105// ---------------------------------------------------------------------------
106// Sorting
107// ---------------------------------------------------------------------------
108
109/// Sort order for item listings.
110#[derive(Debug, Clone, Copy, PartialEq, Eq, Default)]
111pub enum SortOrder {
112    /// Most recently created first.
113    CreatedDesc,
114    /// Oldest first.
115    CreatedAsc,
116    /// Most recently updated first.
117    #[default]
118    UpdatedDesc,
119    /// Oldest update first.
120    UpdatedAsc,
121    /// Urgency order: urgent > default > punt, then `updated_at` DESC.
122    Priority,
123}
124
125impl SortOrder {
126    const fn sql_clause(self) -> &'static str {
127        match self {
128            Self::CreatedDesc => "ORDER BY created_at_us DESC, i.item_id ASC",
129            Self::CreatedAsc => "ORDER BY created_at_us ASC, i.item_id ASC",
130            Self::UpdatedDesc => "ORDER BY updated_at_us DESC, i.item_id ASC",
131            Self::UpdatedAsc => "ORDER BY updated_at_us ASC, i.item_id ASC",
132            Self::Priority => {
133                "ORDER BY CASE urgency \
134                 WHEN 'urgent' THEN 0 \
135                 WHEN 'default' THEN 1 \
136                 WHEN 'punt' THEN 2 \
137                 END ASC, updated_at_us DESC, item_id ASC"
138            }
139        }
140    }
141}
142
143impl fmt::Display for SortOrder {
144    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
145        match self {
146            Self::CreatedDesc => f.write_str("created_desc"),
147            Self::CreatedAsc => f.write_str("created_asc"),
148            Self::UpdatedDesc => f.write_str("updated_desc"),
149            Self::UpdatedAsc => f.write_str("updated_asc"),
150            Self::Priority => f.write_str("priority"),
151        }
152    }
153}
154
155impl FromStr for SortOrder {
156    type Err = anyhow::Error;
157
158    fn from_str(s: &str) -> Result<Self> {
159        match s.trim().to_ascii_lowercase().as_str() {
160            "created_desc" | "created-desc" | "newest" => Ok(Self::CreatedDesc),
161            "created_asc" | "created-asc" | "oldest" => Ok(Self::CreatedAsc),
162            "updated_desc" | "updated-desc" | "recent" => Ok(Self::UpdatedDesc),
163            "updated_asc" | "updated-asc" | "stale" => Ok(Self::UpdatedAsc),
164            "priority" | "triage" => Ok(Self::Priority),
165            other => bail!(
166                "unknown sort order '{other}': expected one of created_desc, created_asc, updated_desc, updated_asc, priority"
167            ),
168        }
169    }
170}
171
172// ---------------------------------------------------------------------------
173// Filters
174// ---------------------------------------------------------------------------
175
176/// Filter criteria for item listings.
177///
178/// All fields are optional. When multiple fields are set, they are combined
179/// with AND semantics.
180#[derive(Debug, Clone, Default)]
181pub struct ItemFilter {
182    /// Filter by lifecycle state (exact match).
183    pub state: Option<String>,
184    /// Filter by item kind (exact match).
185    pub kind: Option<String>,
186    /// Filter by urgency (exact match).
187    pub urgency: Option<String>,
188    /// Filter by label (item must have this label).
189    pub label: Option<String>,
190    /// Filter by assignee (item must be assigned to this agent).
191    pub assignee: Option<String>,
192    /// Filter by `parent_id` (exact match).
193    pub parent_id: Option<String>,
194    /// Include soft-deleted items (default: false).
195    pub include_deleted: bool,
196    /// Maximum number of results.
197    pub limit: Option<u32>,
198    /// Offset for pagination.
199    pub offset: Option<u32>,
200    /// Sort order.
201    pub sort: SortOrder,
202}
203
204// ---------------------------------------------------------------------------
205// Aggregate helper query APIs
206// ---------------------------------------------------------------------------
207
208/// Count projected items grouped by `state`, excluding deleted rows.
209///
210/// # Errors
211///
212/// Returns an error if the database query fails.
213pub fn item_counts_by_state(conn: &Connection) -> Result<HashMap<String, usize>> {
214    count_items_grouped(conn, "state")
215}
216
217/// Count projected items grouped by `kind`, excluding deleted rows.
218///
219/// # Errors
220///
221/// Returns an error if the database query fails.
222pub fn item_counts_by_kind(conn: &Connection) -> Result<HashMap<String, usize>> {
223    count_items_grouped(conn, "kind")
224}
225
226/// Count projected items grouped by `urgency`, excluding deleted rows.
227///
228/// # Errors
229///
230/// Returns an error if the database query fails.
231pub fn item_counts_by_urgency(conn: &Connection) -> Result<HashMap<String, usize>> {
232    count_items_grouped(conn, "urgency")
233}
234
235/// Count projected events by `event_type` from `projected_events`.
236///
237/// Returns an empty map when `projected_events` is not yet available.
238///
239/// # Errors
240///
241/// Returns an error if the database query fails.
242pub fn event_counts_by_type(conn: &Connection) -> Result<HashMap<String, usize>> {
243    count_grouped_events(conn, "event_type")
244}
245
246/// Count projected events by `agent` from `projected_events`.
247///
248/// Returns an empty map when `projected_events` is not yet available.
249///
250/// # Errors
251///
252/// Returns an error if the database query fails.
253pub fn event_counts_by_agent(conn: &Connection) -> Result<HashMap<String, usize>> {
254    count_grouped_events(conn, "agent")
255}
256
257// ---------------------------------------------------------------------------
258// Core query functions
259// ---------------------------------------------------------------------------
260
261/// Fetch a single item by exact `item_id`.
262///
263/// Returns `None` if the item does not exist (or is soft-deleted unless
264/// `include_deleted` is true).
265///
266/// # Errors
267///
268/// Returns an error if the database query fails.
269pub fn get_item(
270    conn: &Connection,
271    item_id: &str,
272    include_deleted: bool,
273) -> Result<Option<QueryItem>> {
274    let sql = if include_deleted {
275        "SELECT item_id, title, description, kind, state, urgency, size, \
276         parent_id, compact_summary, is_deleted, deleted_at_us, \
277         search_labels, created_at_us, updated_at_us \
278         FROM items WHERE item_id = ?1"
279    } else {
280        "SELECT item_id, title, description, kind, state, urgency, size, \
281         parent_id, compact_summary, is_deleted, deleted_at_us, \
282         search_labels, created_at_us, updated_at_us \
283         FROM items WHERE item_id = ?1 AND is_deleted = 0"
284    };
285
286    let mut stmt = conn.prepare(sql).context("prepare get_item query")?;
287
288    let result = stmt.query_row(params![item_id], row_to_query_item);
289
290    match result {
291        Ok(item) => Ok(Some(item)),
292        Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
293        Err(e) => Err(e).context(format!("get_item for '{item_id}'")),
294    }
295}
296
297/// List items matching the given filter criteria.
298///
299/// Returns items in the requested sort order, limited by `filter.limit`
300/// and offset by `filter.offset`.
301///
302/// # Errors
303///
304/// Returns an error if the database query fails.
305pub fn list_items(conn: &Connection, filter: &ItemFilter) -> Result<Vec<QueryItem>> {
306    let mut conditions: Vec<String> = Vec::new();
307    let mut param_values: Vec<Box<dyn rusqlite::types::ToSql>> = Vec::new();
308
309    if !filter.include_deleted {
310        conditions.push("i.is_deleted = 0".to_string());
311    }
312
313    if let Some(ref state) = filter.state {
314        param_values.push(Box::new(state.clone()));
315        conditions.push(format!("i.state = ?{}", param_values.len()));
316    }
317
318    if let Some(ref kind) = filter.kind {
319        param_values.push(Box::new(kind.clone()));
320        conditions.push(format!("i.kind = ?{}", param_values.len()));
321    }
322
323    if let Some(ref urgency) = filter.urgency {
324        param_values.push(Box::new(urgency.clone()));
325        conditions.push(format!("i.urgency = ?{}", param_values.len()));
326    }
327
328    if let Some(ref parent_id) = filter.parent_id {
329        param_values.push(Box::new(parent_id.clone()));
330        conditions.push(format!("i.parent_id = ?{}", param_values.len()));
331    }
332
333    // Label and assignee filters require JOINs
334    let mut joins = String::new();
335    if let Some(ref label) = filter.label {
336        param_values.push(Box::new(label.clone()));
337        let _ = write!(
338            joins,
339            " INNER JOIN item_labels il ON il.item_id = i.item_id AND il.label = ?{}",
340            param_values.len()
341        );
342    }
343
344    if let Some(ref assignee) = filter.assignee {
345        param_values.push(Box::new(assignee.clone()));
346        let _ = write!(
347            joins,
348            " INNER JOIN item_assignees ia ON ia.item_id = i.item_id AND ia.agent = ?{}",
349            param_values.len()
350        );
351    }
352
353    let where_clause = if conditions.is_empty() {
354        String::new()
355    } else {
356        format!(" WHERE {}", conditions.join(" AND "))
357    };
358
359    let sort_clause = filter.sort.sql_clause();
360
361    let limit_clause = match (filter.limit, filter.offset) {
362        (Some(limit), Some(offset)) => format!(" LIMIT {limit} OFFSET {offset}"),
363        (Some(limit), None) => format!(" LIMIT {limit}"),
364        (None, Some(offset)) => format!(" LIMIT -1 OFFSET {offset}"),
365        (None, None) => String::new(),
366    };
367
368    let sql = format!(
369        "SELECT i.item_id, i.title, i.description, i.kind, i.state, i.urgency, i.size, \
370         i.parent_id, i.compact_summary, i.is_deleted, i.deleted_at_us, \
371         i.search_labels, i.created_at_us, i.updated_at_us \
372         FROM items i{joins}{where_clause} {sort_clause}{limit_clause}"
373    );
374
375    let mut stmt = conn
376        .prepare(&sql)
377        .with_context(|| format!("prepare list_items query: {sql}"))?;
378
379    let params_ref: Vec<&dyn rusqlite::types::ToSql> =
380        param_values.iter().map(AsRef::as_ref).collect();
381
382    let rows = stmt
383        .query_map(params_from_iter(params_ref), row_to_query_item)
384        .context("execute list_items query")?;
385
386    let mut items = Vec::new();
387    for row in rows {
388        items.push(row.context("read list_items row")?);
389    }
390    Ok(items)
391}
392
393/// Search items via FTS5 full-text search with BM25 ranking.
394///
395/// Column weights: title 3×, description 2×, labels 1×.
396///
397/// Returns up to `limit` results sorted by BM25 relevance (best match first).
398///
399/// # Errors
400///
401/// Returns an error if the FTS5 query fails (e.g., syntax error in query).
402pub fn search(conn: &Connection, query: &str, limit: u32) -> Result<Vec<SearchHit>> {
403    let sql = "SELECT f.item_id, i.title, bm25(items_fts, 3.0, 2.0, 1.0) AS rank \
404               FROM items_fts f \
405               INNER JOIN items i ON i.item_id = f.item_id \
406               WHERE items_fts MATCH ?1 AND i.is_deleted = 0 \
407               ORDER BY rank \
408               LIMIT ?2";
409
410    let mut stmt = conn.prepare(sql).context("prepare FTS5 search query")?;
411
412    let rows = stmt
413        .query_map(params![query, limit], |row| {
414            Ok(SearchHit {
415                item_id: row.get(0)?,
416                title: row.get(1)?,
417                rank: row.get(2)?,
418            })
419        })
420        .with_context(|| format!("execute FTS5 search for '{query}'"))?;
421
422    let mut hits = Vec::new();
423    for row in rows {
424        hits.push(row.context("read search hit")?);
425    }
426    Ok(hits)
427}
428
429/// Get all labels for an item.
430///
431/// # Errors
432///
433/// Returns an error if the query fails.
434pub fn get_labels(conn: &Connection, item_id: &str) -> Result<Vec<QueryLabel>> {
435    let sql = "SELECT item_id, label, created_at_us \
436               FROM item_labels WHERE item_id = ?1 \
437               ORDER BY label";
438
439    let mut stmt = conn.prepare(sql).context("prepare get_labels")?;
440    let rows = stmt
441        .query_map(params![item_id], |row| {
442            Ok(QueryLabel {
443                item_id: row.get(0)?,
444                label: row.get(1)?,
445                created_at_us: row.get(2)?,
446            })
447        })
448        .context("execute get_labels")?;
449
450    let mut labels = Vec::new();
451    for row in rows {
452        labels.push(row.context("read label row")?);
453    }
454    Ok(labels)
455}
456
457/// List global label usage counts across all items.
458///
459/// # Errors
460///
461/// Returns an error if the aggregate query fails.
462pub fn list_labels(
463    conn: &Connection,
464    limit: Option<u32>,
465    offset: Option<u32>,
466) -> Result<Vec<LabelCount>> {
467    let limit_clause = match (limit, offset) {
468        (Some(limit), Some(offset)) => format!(" LIMIT {limit} OFFSET {offset}"),
469        (Some(limit), None) => format!(" LIMIT {limit}"),
470        (None, Some(offset)) => format!(" LIMIT -1 OFFSET {offset}"),
471        (None, None) => String::new(),
472    };
473
474    let sql = format!(
475        "SELECT label, COUNT(*) as count \
476         FROM item_labels \
477         GROUP BY label \
478         ORDER BY count DESC, label ASC{limit_clause}"
479    );
480
481    let mut stmt = conn.prepare(&sql).context("prepare list_labels")?;
482    let rows = stmt
483        .query_map([], |row| {
484            let count: i64 = row.get(1)?;
485            Ok(LabelCount {
486                name: row.get(0)?,
487                count: usize::try_from(count).unwrap_or(usize::MAX),
488            })
489        })
490        .context("execute list_labels")?;
491
492    let mut labels = Vec::new();
493    for row in rows {
494        labels.push(row.context("read list_labels row")?);
495    }
496    Ok(labels)
497}
498
499/// Get all assignees for an item.
500///
501/// # Errors
502///
503/// Returns an error if the query fails.
504pub fn get_assignees(conn: &Connection, item_id: &str) -> Result<Vec<QueryAssignee>> {
505    let sql = "SELECT item_id, agent, created_at_us \
506               FROM item_assignees WHERE item_id = ?1 \
507               ORDER BY agent";
508
509    let mut stmt = conn.prepare(sql).context("prepare get_assignees")?;
510    let rows = stmt
511        .query_map(params![item_id], |row| {
512            Ok(QueryAssignee {
513                item_id: row.get(0)?,
514                agent: row.get(1)?,
515                created_at_us: row.get(2)?,
516            })
517        })
518        .context("execute get_assignees")?;
519
520    let mut assignees = Vec::new();
521    for row in rows {
522        assignees.push(row.context("read assignee row")?);
523    }
524    Ok(assignees)
525}
526
527/// Get all comments for an item, newest first.
528///
529/// # Errors
530///
531/// Returns an error if the query fails.
532pub fn get_comments(
533    conn: &Connection,
534    item_id: &str,
535    limit: Option<u32>,
536    offset: Option<u32>,
537) -> Result<Vec<QueryComment>> {
538    let limit_clause = match (limit, offset) {
539        (Some(limit), Some(offset)) => format!(" LIMIT {limit} OFFSET {offset}"),
540        (Some(limit), None) => format!(" LIMIT {limit}"),
541        (None, Some(offset)) => format!(" LIMIT -1 OFFSET {offset}"),
542        (None, None) => String::new(),
543    };
544
545    let sql = format!(
546        "SELECT comment_id, item_id, event_hash, author, body, created_at_us \
547         FROM item_comments WHERE item_id = ?1 \
548         ORDER BY created_at_us DESC{limit_clause}"
549    );
550
551    let mut stmt = conn.prepare(&sql).context("prepare get_comments")?;
552    let rows = stmt
553        .query_map(params![item_id], |row| {
554            Ok(QueryComment {
555                comment_id: row.get(0)?,
556                item_id: row.get(1)?,
557                event_hash: row.get(2)?,
558                author: row.get(3)?,
559                body: row.get(4)?,
560                created_at_us: row.get(5)?,
561            })
562        })
563        .context("execute get_comments")?;
564
565    let mut comments = Vec::new();
566    for row in rows {
567        comments.push(row.context("read comment row")?);
568    }
569    Ok(comments)
570}
571
572/// Get items that a given item depends on (its blockers).
573///
574/// # Errors
575///
576/// Returns an error if the query fails.
577pub fn get_dependencies(conn: &Connection, item_id: &str) -> Result<Vec<QueryDependency>> {
578    let sql = "SELECT item_id, depends_on_item_id, link_type, created_at_us \
579               FROM item_dependencies WHERE item_id = ?1 \
580               ORDER BY depends_on_item_id";
581
582    let mut stmt = conn.prepare(sql).context("prepare get_dependencies")?;
583    let rows = stmt
584        .query_map(params![item_id], |row| {
585            Ok(QueryDependency {
586                item_id: row.get(0)?,
587                depends_on_item_id: row.get(1)?,
588                link_type: row.get(2)?,
589                created_at_us: row.get(3)?,
590            })
591        })
592        .context("execute get_dependencies")?;
593
594    let mut deps = Vec::new();
595    for row in rows {
596        deps.push(row.context("read dependency row")?);
597    }
598    Ok(deps)
599}
600
601/// Get items that depend on the given item (its dependents / reverse deps).
602///
603/// # Errors
604///
605/// Returns an error if the query fails.
606pub fn get_dependents(conn: &Connection, item_id: &str) -> Result<Vec<QueryDependency>> {
607    let sql = "SELECT item_id, depends_on_item_id, link_type, created_at_us \
608               FROM item_dependencies WHERE depends_on_item_id = ?1 \
609               ORDER BY item_id";
610
611    let mut stmt = conn.prepare(sql).context("prepare get_dependents")?;
612    let rows = stmt
613        .query_map(params![item_id], |row| {
614            Ok(QueryDependency {
615                item_id: row.get(0)?,
616                depends_on_item_id: row.get(1)?,
617                link_type: row.get(2)?,
618                created_at_us: row.get(3)?,
619            })
620        })
621        .context("execute get_dependents")?;
622
623    let mut deps = Vec::new();
624    for row in rows {
625        deps.push(row.context("read dependent row")?);
626    }
627    Ok(deps)
628}
629
630/// Get child items of the given parent item.
631///
632/// # Errors
633///
634/// Returns an error if the query fails.
635pub fn get_children(conn: &Connection, parent_id: &str) -> Result<Vec<QueryItem>> {
636    let sql = "SELECT item_id, title, description, kind, state, urgency, size, \
637               parent_id, compact_summary, is_deleted, deleted_at_us, \
638               search_labels, created_at_us, updated_at_us \
639               FROM items WHERE parent_id = ?1 AND is_deleted = 0 \
640               ORDER BY created_at_us ASC";
641
642    let mut stmt = conn.prepare(sql).context("prepare get_children")?;
643    let rows = stmt
644        .query_map(params![parent_id], row_to_query_item)
645        .context("execute get_children")?;
646
647    let mut children = Vec::new();
648    for row in rows {
649        children.push(row.context("read child row")?);
650    }
651    Ok(children)
652}
653
654/// Count items matching the given filter criteria.
655///
656/// # Errors
657///
658/// Returns an error if the query fails.
659pub fn count_items(conn: &Connection, filter: &ItemFilter) -> Result<u64> {
660    let mut conditions: Vec<String> = Vec::new();
661    let mut param_values: Vec<Box<dyn rusqlite::types::ToSql>> = Vec::new();
662
663    if !filter.include_deleted {
664        conditions.push("i.is_deleted = 0".to_string());
665    }
666
667    if let Some(ref state) = filter.state {
668        param_values.push(Box::new(state.clone()));
669        conditions.push(format!("i.state = ?{}", param_values.len()));
670    }
671
672    if let Some(ref kind) = filter.kind {
673        param_values.push(Box::new(kind.clone()));
674        conditions.push(format!("i.kind = ?{}", param_values.len()));
675    }
676
677    if let Some(ref urgency) = filter.urgency {
678        param_values.push(Box::new(urgency.clone()));
679        conditions.push(format!("i.urgency = ?{}", param_values.len()));
680    }
681
682    if let Some(ref parent_id) = filter.parent_id {
683        param_values.push(Box::new(parent_id.clone()));
684        conditions.push(format!("i.parent_id = ?{}", param_values.len()));
685    }
686
687    let mut joins = String::new();
688    if let Some(ref label) = filter.label {
689        param_values.push(Box::new(label.clone()));
690        let _ = write!(
691            joins,
692            " INNER JOIN item_labels il ON il.item_id = i.item_id AND il.label = ?{}",
693            param_values.len()
694        );
695    }
696
697    if let Some(ref assignee) = filter.assignee {
698        param_values.push(Box::new(assignee.clone()));
699        let _ = write!(
700            joins,
701            " INNER JOIN item_assignees ia ON ia.item_id = i.item_id AND ia.agent = ?{}",
702            param_values.len()
703        );
704    }
705
706    let where_clause = if conditions.is_empty() {
707        String::new()
708    } else {
709        format!(" WHERE {}", conditions.join(" AND "))
710    };
711
712    let sql = format!("SELECT COUNT(*) FROM items i{joins}{where_clause}");
713
714    let mut stmt = conn
715        .prepare(&sql)
716        .with_context(|| format!("prepare count_items: {sql}"))?;
717
718    let params_ref: Vec<&dyn rusqlite::types::ToSql> =
719        param_values.iter().map(AsRef::as_ref).collect();
720
721    let count: i64 = stmt
722        .query_row(params_from_iter(params_ref), |row| row.get(0))
723        .context("execute count_items")?;
724
725    Ok(u64::try_from(count).unwrap_or(0))
726}
727
728/// Check if an item exists in the projection.
729///
730/// # Errors
731///
732/// Returns an error if the query fails.
733pub fn item_exists(conn: &Connection, item_id: &str) -> Result<bool> {
734    let sql = "SELECT EXISTS(SELECT 1 FROM items WHERE item_id = ?1)";
735    let exists: bool = conn
736        .query_row(sql, params![item_id], |row| row.get(0))
737        .context("check item_exists")?;
738    Ok(exists)
739}
740
741/// Get the projection cursor metadata (last replay offset and hash).
742///
743/// Returns `(last_event_offset, last_event_hash)`.
744///
745/// # Errors
746///
747/// Returns an error if the query fails or no `projection_meta` row exists.
748pub fn get_projection_cursor(conn: &Connection) -> Result<(i64, Option<String>)> {
749    let sql = "SELECT last_event_offset, last_event_hash FROM projection_meta WHERE id = 1";
750    conn.query_row(sql, [], |row| {
751        Ok((row.get::<_, i64>(0)?, row.get::<_, Option<String>>(1)?))
752    })
753    .context("read projection cursor")
754}
755
756/// Update the projection cursor after replaying events.
757///
758/// # Errors
759///
760/// Returns an error if the update fails.
761pub fn update_projection_cursor(
762    conn: &Connection,
763    offset: i64,
764    event_hash: Option<&str>,
765) -> Result<()> {
766    let now_us = chrono::Utc::now().timestamp_micros();
767    conn.execute(
768        "UPDATE projection_meta SET last_event_offset = ?1, last_event_hash = ?2, \
769         last_rebuild_at_us = ?3 WHERE id = 1",
770        params![offset, event_hash, now_us],
771    )
772    .context("update projection cursor")?;
773    Ok(())
774}
775
776// ---------------------------------------------------------------------------
777// Internal helpers
778// ---------------------------------------------------------------------------
779
780fn count_items_grouped(conn: &Connection, column: &str) -> Result<HashMap<String, usize>> {
781    let sql =
782        format!("SELECT {column}, COUNT(*) FROM items WHERE is_deleted = 0 GROUP BY {column}");
783    let mut stmt = conn
784        .prepare(&sql)
785        .context("prepare aggregate count query")?;
786    let rows = stmt.query_map([], |row| {
787        let key: String = row.get(0)?;
788        let count: i64 = row.get(1)?;
789        Ok((key, usize::try_from(count).unwrap_or(usize::MAX)))
790    })?;
791
792    let mut counts = HashMap::new();
793    for row in rows {
794        let (key, count) = row.context("read aggregate count")?;
795        counts.insert(key, count);
796    }
797
798    Ok(counts)
799}
800
801fn count_grouped_events(conn: &Connection, group_by: &str) -> Result<HashMap<String, usize>> {
802    if !table_exists(conn, "projected_events")? {
803        return Ok(HashMap::new());
804    }
805
806    if !table_has_column(conn, "projected_events", group_by)? {
807        return Ok(HashMap::new());
808    }
809
810    let sql = format!(
811        "SELECT {group_by}, COUNT(*) FROM projected_events WHERE {group_by} IS NOT NULL GROUP BY {group_by}"
812    );
813    let mut stmt = conn
814        .prepare(&sql)
815        .context("prepare projected event aggregate query")?;
816    let rows = stmt.query_map([], |row| {
817        let key: String = row.get(0)?;
818        let count: i64 = row.get(1)?;
819        Ok((key, usize::try_from(count).unwrap_or(usize::MAX)))
820    })?;
821
822    let mut counts = HashMap::new();
823    for row in rows {
824        let (key, count) = row.context("read projected event aggregate")?;
825        counts.insert(key, count);
826    }
827
828    Ok(counts)
829}
830
831fn table_exists(conn: &Connection, table: &str) -> Result<bool> {
832    let exists: bool = conn
833        .query_row(
834            "SELECT EXISTS(SELECT 1 FROM sqlite_master WHERE type='table' AND name = ?1)",
835            [table],
836            |row| row.get(0),
837        )
838        .context("check table exists")?;
839    Ok(exists)
840}
841
842fn table_has_column(conn: &Connection, table: &str, column: &str) -> Result<bool> {
843    let mut stmt = conn
844        .prepare(&format!("PRAGMA table_info({table})"))
845        .context("prepare table_info pragma")?;
846    let rows = stmt.query_map([], |row| row.get::<_, String>(1))?;
847
848    for row in rows {
849        let name = row.context("read table_info column")?;
850        if name == column {
851            return Ok(true);
852        }
853    }
854
855    Ok(false)
856}
857
858fn row_to_query_item(row: &rusqlite::Row<'_>) -> rusqlite::Result<QueryItem> {
859    Ok(QueryItem {
860        item_id: row.get(0)?,
861        title: row.get(1)?,
862        description: row.get(2)?,
863        kind: row.get(3)?,
864        state: row.get(4)?,
865        urgency: row.get(5)?,
866        size: row.get(6)?,
867        parent_id: row.get(7)?,
868        compact_summary: row.get(8)?,
869        is_deleted: row.get::<_, i64>(9)? != 0,
870        deleted_at_us: row.get(10)?,
871        search_labels: row.get(11)?,
872        created_at_us: row.get(12)?,
873        updated_at_us: row.get(13)?,
874    })
875}
876
877// ---------------------------------------------------------------------------
878// Graceful recovery
879// ---------------------------------------------------------------------------
880
881/// Attempt to open the projection database with graceful recovery.
882///
883/// If the database file is missing or corrupt, returns `Ok(None)` instead
884/// of an error. The caller can then trigger a full rebuild.
885///
886/// # Errors
887///
888/// Returns an error only for unexpected I/O failures (not missing/corrupt DB).
889pub fn try_open_projection(path: &std::path::Path) -> Result<Option<Connection>> {
890    if !path.exists() {
891        return Ok(None);
892    }
893
894    match super::open_projection(path) {
895        Ok(conn) => {
896            // Quick integrity check — verify projection_meta is readable
897            if get_projection_cursor(&conn).is_ok() {
898                Ok(Some(conn))
899            } else {
900                tracing::warn!(
901                    path = %path.display(),
902                    "projection database corrupt, needs rebuild"
903                );
904                Ok(None)
905            }
906        }
907        Err(e) => {
908            // Include the full causal chain so pragma/lock/IO failures are diagnosable.
909            tracing::warn!(
910                path = %path.display(),
911                error = format!("{e:#}"),
912                "failed to open projection database, needs rebuild"
913            );
914            Ok(None)
915        }
916    }
917}
918
919// ---------------------------------------------------------------------------
920// Tests
921// ---------------------------------------------------------------------------
922
923#[cfg(test)]
924mod tests {
925    use super::*;
926    use crate::db::{migrations, open_projection};
927    use rusqlite::{Connection, params};
928
929    /// Create an in-memory migrated database with test data.
930    fn test_db() -> Connection {
931        let mut conn = Connection::open_in_memory().expect("open in-memory db");
932        migrations::migrate(&mut conn).expect("migrate");
933        conn
934    }
935
936    /// Insert a test item with minimal required fields.
937    fn insert_item(conn: &Connection, id: &str, title: &str, state: &str, urgency: &str) {
938        conn.execute(
939            "INSERT INTO items (item_id, title, kind, state, urgency, \
940             is_deleted, search_labels, created_at_us, updated_at_us) \
941             VALUES (?1, ?2, 'task', ?3, ?4, 0, '', ?5, ?6)",
942            params![id, title, state, urgency, 1000_i64, 2000_i64],
943        )
944        .expect("insert item");
945    }
946
947    fn insert_item_full(
948        conn: &Connection,
949        id: &str,
950        title: &str,
951        desc: Option<&str>,
952        kind: &str,
953        state: &str,
954        urgency: &str,
955        parent_id: Option<&str>,
956        labels: &str,
957        created: i64,
958        updated: i64,
959    ) {
960        conn.execute(
961            "INSERT INTO items (item_id, title, description, kind, state, urgency, \
962             parent_id, is_deleted, search_labels, created_at_us, updated_at_us) \
963             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, 0, ?8, ?9, ?10)",
964            params![
965                id, title, desc, kind, state, urgency, parent_id, labels, created, updated
966            ],
967        )
968        .expect("insert full item");
969    }
970
971    fn insert_label(conn: &Connection, item_id: &str, label: &str) {
972        conn.execute(
973            "INSERT INTO item_labels (item_id, label, created_at_us) VALUES (?1, ?2, 100)",
974            params![item_id, label],
975        )
976        .expect("insert label");
977    }
978
979    fn insert_assignee(conn: &Connection, item_id: &str, agent: &str) {
980        conn.execute(
981            "INSERT INTO item_assignees (item_id, agent, created_at_us) VALUES (?1, ?2, 100)",
982            params![item_id, agent],
983        )
984        .expect("insert assignee");
985    }
986
987    fn _insert_comment(conn: &Connection, item_id: &str, hash: &str, author: &str, body: &str) {
988        conn.execute(
989            "INSERT INTO item_comments (item_id, event_hash, author, body, created_at_us) \
990             VALUES (?1, ?2, ?3, ?4, 100)",
991            params![item_id, hash, author, body],
992        )
993        .expect("insert comment");
994    }
995
996    fn insert_dependency(conn: &Connection, item_id: &str, depends_on: &str) {
997        conn.execute(
998            "INSERT INTO item_dependencies (item_id, depends_on_item_id, link_type, created_at_us) \
999             VALUES (?1, ?2, 'blocks', 100)",
1000            params![item_id, depends_on],
1001        )
1002        .expect("insert dependency");
1003    }
1004
1005    // -----------------------------------------------------------------------
1006    // get_item tests
1007    // -----------------------------------------------------------------------
1008
1009    #[test]
1010    fn get_item_found() {
1011        let conn = test_db();
1012        insert_item(&conn, "bn-001", "Fix auth timeout", "open", "urgent");
1013
1014        let item = get_item(&conn, "bn-001", false).unwrap().unwrap();
1015        assert_eq!(item.item_id, "bn-001");
1016        assert_eq!(item.title, "Fix auth timeout");
1017        assert_eq!(item.state, "open");
1018        assert_eq!(item.urgency, "urgent");
1019        assert!(!item.is_deleted);
1020    }
1021
1022    #[test]
1023    fn get_item_not_found() {
1024        let conn = test_db();
1025        let item = get_item(&conn, "bn-nonexistent", false).unwrap();
1026        assert!(item.is_none());
1027    }
1028
1029    #[test]
1030    fn get_item_excludes_deleted() {
1031        let conn = test_db();
1032        insert_item(&conn, "bn-del", "Deleted item", "open", "default");
1033        conn.execute(
1034            "UPDATE items SET is_deleted = 1, deleted_at_us = 3000 WHERE item_id = 'bn-del'",
1035            [],
1036        )
1037        .unwrap();
1038
1039        assert!(get_item(&conn, "bn-del", false).unwrap().is_none());
1040        let item = get_item(&conn, "bn-del", true).unwrap().unwrap();
1041        assert!(item.is_deleted);
1042        assert_eq!(item.deleted_at_us, Some(3000));
1043    }
1044
1045    // -----------------------------------------------------------------------
1046    // list_items tests
1047    // -----------------------------------------------------------------------
1048
1049    #[test]
1050    fn list_items_no_filter() {
1051        let conn = test_db();
1052        insert_item(&conn, "bn-001", "First", "open", "default");
1053        insert_item(&conn, "bn-002", "Second", "doing", "urgent");
1054
1055        let items = list_items(&conn, &ItemFilter::default()).unwrap();
1056        assert_eq!(items.len(), 2);
1057    }
1058
1059    #[test]
1060    fn list_items_filter_by_state() {
1061        let conn = test_db();
1062        insert_item(&conn, "bn-001", "Open item", "open", "default");
1063        insert_item(&conn, "bn-002", "Doing item", "doing", "default");
1064
1065        let filter = ItemFilter {
1066            state: Some("open".to_string()),
1067            ..Default::default()
1068        };
1069        let items = list_items(&conn, &filter).unwrap();
1070        assert_eq!(items.len(), 1);
1071        assert_eq!(items[0].item_id, "bn-001");
1072    }
1073
1074    #[test]
1075    fn list_items_filter_by_kind() {
1076        let conn = test_db();
1077        insert_item_full(
1078            &conn, "bn-001", "A bug", None, "bug", "open", "default", None, "", 100, 200,
1079        );
1080        insert_item_full(
1081            &conn, "bn-002", "A task", None, "task", "open", "default", None, "", 100, 200,
1082        );
1083
1084        let filter = ItemFilter {
1085            kind: Some("bug".to_string()),
1086            ..Default::default()
1087        };
1088        let items = list_items(&conn, &filter).unwrap();
1089        assert_eq!(items.len(), 1);
1090        assert_eq!(items[0].item_id, "bn-001");
1091    }
1092
1093    #[test]
1094    fn list_items_filter_by_urgency() {
1095        let conn = test_db();
1096        insert_item(&conn, "bn-001", "Urgent item", "open", "urgent");
1097        insert_item(&conn, "bn-002", "Default item", "open", "default");
1098        insert_item(&conn, "bn-003", "Punt item", "open", "punt");
1099
1100        let filter = ItemFilter {
1101            urgency: Some("urgent".to_string()),
1102            ..Default::default()
1103        };
1104        let items = list_items(&conn, &filter).unwrap();
1105        assert_eq!(items.len(), 1);
1106        assert_eq!(items[0].item_id, "bn-001");
1107    }
1108
1109    #[test]
1110    fn list_items_filter_by_label() {
1111        let conn = test_db();
1112        insert_item(&conn, "bn-001", "Backend bug", "open", "default");
1113        insert_item(&conn, "bn-002", "Frontend bug", "open", "default");
1114        insert_label(&conn, "bn-001", "backend");
1115        insert_label(&conn, "bn-002", "frontend");
1116
1117        let filter = ItemFilter {
1118            label: Some("backend".to_string()),
1119            ..Default::default()
1120        };
1121        let items = list_items(&conn, &filter).unwrap();
1122        assert_eq!(items.len(), 1);
1123        assert_eq!(items[0].item_id, "bn-001");
1124    }
1125
1126    #[test]
1127    fn list_items_filter_by_assignee() {
1128        let conn = test_db();
1129        insert_item(&conn, "bn-001", "Alice's task", "open", "default");
1130        insert_item(&conn, "bn-002", "Bob's task", "open", "default");
1131        insert_assignee(&conn, "bn-001", "alice");
1132        insert_assignee(&conn, "bn-002", "bob");
1133
1134        let filter = ItemFilter {
1135            assignee: Some("alice".to_string()),
1136            ..Default::default()
1137        };
1138        let items = list_items(&conn, &filter).unwrap();
1139        assert_eq!(items.len(), 1);
1140        assert_eq!(items[0].item_id, "bn-001");
1141    }
1142
1143    #[test]
1144    fn list_items_filter_by_parent() {
1145        let conn = test_db();
1146        insert_item(&conn, "bn-parent", "Parent", "open", "default");
1147        insert_item_full(
1148            &conn,
1149            "bn-child1",
1150            "Child 1",
1151            None,
1152            "task",
1153            "open",
1154            "default",
1155            Some("bn-parent"),
1156            "",
1157            100,
1158            200,
1159        );
1160        insert_item_full(
1161            &conn,
1162            "bn-child2",
1163            "Child 2",
1164            None,
1165            "task",
1166            "open",
1167            "default",
1168            Some("bn-parent"),
1169            "",
1170            101,
1171            201,
1172        );
1173        insert_item(&conn, "bn-other", "Other", "open", "default");
1174
1175        let filter = ItemFilter {
1176            parent_id: Some("bn-parent".to_string()),
1177            ..Default::default()
1178        };
1179        let items = list_items(&conn, &filter).unwrap();
1180        assert_eq!(items.len(), 2);
1181    }
1182
1183    #[test]
1184    fn list_items_combined_filters() {
1185        let conn = test_db();
1186        insert_item(&conn, "bn-001", "Urgent open", "open", "urgent");
1187        insert_item(&conn, "bn-002", "Default open", "open", "default");
1188        insert_item(&conn, "bn-003", "Urgent doing", "doing", "urgent");
1189
1190        let filter = ItemFilter {
1191            state: Some("open".to_string()),
1192            urgency: Some("urgent".to_string()),
1193            ..Default::default()
1194        };
1195        let items = list_items(&conn, &filter).unwrap();
1196        assert_eq!(items.len(), 1);
1197        assert_eq!(items[0].item_id, "bn-001");
1198    }
1199
1200    #[test]
1201    fn list_items_with_limit_and_offset() {
1202        let conn = test_db();
1203        for i in 0..10 {
1204            insert_item_full(
1205                &conn,
1206                &format!("bn-{i:03}"),
1207                &format!("Item {i}"),
1208                None,
1209                "task",
1210                "open",
1211                "default",
1212                None,
1213                "",
1214                i * 100,
1215                i * 100 + 50,
1216            );
1217        }
1218
1219        let filter = ItemFilter {
1220            limit: Some(3),
1221            sort: SortOrder::CreatedAsc,
1222            ..Default::default()
1223        };
1224        let items = list_items(&conn, &filter).unwrap();
1225        assert_eq!(items.len(), 3);
1226        assert_eq!(items[0].item_id, "bn-000");
1227
1228        let filter2 = ItemFilter {
1229            limit: Some(3),
1230            offset: Some(3),
1231            sort: SortOrder::CreatedAsc,
1232            ..Default::default()
1233        };
1234        let items2 = list_items(&conn, &filter2).unwrap();
1235        assert_eq!(items2.len(), 3);
1236        assert_eq!(items2[0].item_id, "bn-003");
1237    }
1238
1239    #[test]
1240    fn list_items_stable_tie_breaks_use_item_id() {
1241        let conn = test_db();
1242
1243        // Same timestamps force ORDER BY tie-break behavior.
1244        insert_item_full(
1245            &conn, "bn-010", "Ten", None, "task", "open", "default", None, "", 100, 200,
1246        );
1247        insert_item_full(
1248            &conn, "bn-002", "Two", None, "task", "open", "default", None, "", 100, 200,
1249        );
1250        insert_item_full(
1251            &conn, "bn-001", "One", None, "task", "open", "default", None, "", 100, 200,
1252        );
1253
1254        let asc = ItemFilter {
1255            sort: SortOrder::CreatedAsc,
1256            ..Default::default()
1257        };
1258        let asc_items = list_items(&conn, &asc).unwrap();
1259        assert_eq!(asc_items[0].item_id, "bn-001");
1260        assert_eq!(asc_items[1].item_id, "bn-002");
1261        assert_eq!(asc_items[2].item_id, "bn-010");
1262
1263        let desc = ItemFilter {
1264            sort: SortOrder::UpdatedDesc,
1265            ..Default::default()
1266        };
1267        let desc_items = list_items(&conn, &desc).unwrap();
1268        assert_eq!(desc_items[0].item_id, "bn-001");
1269        assert_eq!(desc_items[1].item_id, "bn-002");
1270        assert_eq!(desc_items[2].item_id, "bn-010");
1271    }
1272
1273    #[test]
1274    fn list_items_excludes_deleted() {
1275        let conn = test_db();
1276        insert_item(&conn, "bn-001", "Active", "open", "default");
1277        insert_item(&conn, "bn-002", "Deleted", "open", "default");
1278        conn.execute(
1279            "UPDATE items SET is_deleted = 1 WHERE item_id = 'bn-002'",
1280            [],
1281        )
1282        .unwrap();
1283
1284        let items = list_items(&conn, &ItemFilter::default()).unwrap();
1285        assert_eq!(items.len(), 1);
1286        assert_eq!(items[0].item_id, "bn-001");
1287
1288        let filter = ItemFilter {
1289            include_deleted: true,
1290            ..Default::default()
1291        };
1292        let items_with_deleted = list_items(&conn, &filter).unwrap();
1293        assert_eq!(items_with_deleted.len(), 2);
1294    }
1295
1296    // -----------------------------------------------------------------------
1297    // Sort order tests
1298    // -----------------------------------------------------------------------
1299
1300    #[test]
1301    fn list_items_priority_sort() {
1302        let conn = test_db();
1303        insert_item(&conn, "bn-001", "Default", "open", "default");
1304        insert_item(&conn, "bn-002", "Urgent", "open", "urgent");
1305        insert_item(&conn, "bn-003", "Punt", "open", "punt");
1306
1307        let filter = ItemFilter {
1308            sort: SortOrder::Priority,
1309            ..Default::default()
1310        };
1311        let items = list_items(&conn, &filter).unwrap();
1312        assert_eq!(items[0].urgency, "urgent");
1313        assert_eq!(items[1].urgency, "default");
1314        assert_eq!(items[2].urgency, "punt");
1315    }
1316
1317    #[test]
1318    fn sort_order_parse_roundtrip() {
1319        for order in [
1320            SortOrder::CreatedDesc,
1321            SortOrder::CreatedAsc,
1322            SortOrder::UpdatedDesc,
1323            SortOrder::UpdatedAsc,
1324            SortOrder::Priority,
1325        ] {
1326            let s = order.to_string();
1327            let parsed: SortOrder = s.parse().unwrap();
1328            assert_eq!(order, parsed);
1329        }
1330    }
1331
1332    #[test]
1333    fn sort_order_parse_aliases() {
1334        assert_eq!(
1335            "newest".parse::<SortOrder>().unwrap(),
1336            SortOrder::CreatedDesc
1337        );
1338        assert_eq!(
1339            "oldest".parse::<SortOrder>().unwrap(),
1340            SortOrder::CreatedAsc
1341        );
1342        assert_eq!(
1343            "recent".parse::<SortOrder>().unwrap(),
1344            SortOrder::UpdatedDesc
1345        );
1346        assert_eq!("stale".parse::<SortOrder>().unwrap(), SortOrder::UpdatedAsc);
1347        assert_eq!("triage".parse::<SortOrder>().unwrap(), SortOrder::Priority);
1348    }
1349
1350    // -----------------------------------------------------------------------
1351    // Search tests
1352    // -----------------------------------------------------------------------
1353
1354    #[test]
1355    fn search_fts5_finds_by_title() {
1356        let conn = test_db();
1357        insert_item_full(
1358            &conn,
1359            "bn-001",
1360            "Authentication timeout regression",
1361            Some("Retries fail after 30 seconds"),
1362            "task",
1363            "open",
1364            "urgent",
1365            None,
1366            "auth backend",
1367            100,
1368            200,
1369        );
1370        insert_item_full(
1371            &conn,
1372            "bn-002",
1373            "Update documentation",
1374            Some("Fix typos in README"),
1375            "task",
1376            "open",
1377            "default",
1378            None,
1379            "docs",
1380            101,
1381            201,
1382        );
1383
1384        let hits = search(&conn, "authentication", 10).unwrap();
1385        assert_eq!(hits.len(), 1);
1386        assert_eq!(hits[0].item_id, "bn-001");
1387    }
1388
1389    #[test]
1390    fn search_fts5_stemming() {
1391        let conn = test_db();
1392        insert_item_full(
1393            &conn,
1394            "bn-001",
1395            "Running tests slowly",
1396            None,
1397            "task",
1398            "open",
1399            "default",
1400            None,
1401            "",
1402            100,
1403            200,
1404        );
1405
1406        // Porter stemmer: "run" should match "running"
1407        let hits = search(&conn, "run", 10).unwrap();
1408        assert_eq!(hits.len(), 1);
1409        assert_eq!(hits[0].item_id, "bn-001");
1410    }
1411
1412    #[test]
1413    fn search_fts5_prefix() {
1414        let conn = test_db();
1415        insert_item_full(
1416            &conn,
1417            "bn-001",
1418            "Authentication service broken",
1419            None,
1420            "task",
1421            "open",
1422            "default",
1423            None,
1424            "",
1425            100,
1426            200,
1427        );
1428
1429        // Prefix search: "auth*" matches "authentication"
1430        let hits = search(&conn, "auth*", 10).unwrap();
1431        assert_eq!(hits.len(), 1);
1432    }
1433
1434    #[test]
1435    fn search_fts5_excludes_deleted() {
1436        let conn = test_db();
1437        insert_item_full(
1438            &conn,
1439            "bn-001",
1440            "Important authentication bug",
1441            None,
1442            "task",
1443            "open",
1444            "default",
1445            None,
1446            "",
1447            100,
1448            200,
1449        );
1450        conn.execute(
1451            "UPDATE items SET is_deleted = 1 WHERE item_id = 'bn-001'",
1452            [],
1453        )
1454        .unwrap();
1455
1456        let hits = search(&conn, "authentication", 10).unwrap();
1457        assert!(hits.is_empty());
1458    }
1459
1460    #[test]
1461    fn search_fts5_limit() {
1462        let conn = test_db();
1463        for i in 0..20 {
1464            insert_item_full(
1465                &conn,
1466                &format!("bn-{i:03}"),
1467                &format!("Authentication bug {i}"),
1468                None,
1469                "task",
1470                "open",
1471                "default",
1472                None,
1473                "",
1474                i * 100,
1475                i * 100 + 50,
1476            );
1477        }
1478
1479        let hits = search(&conn, "authentication", 5).unwrap();
1480        assert_eq!(hits.len(), 5);
1481    }
1482
1483    // -----------------------------------------------------------------------
1484    // Label / Assignee / Comment / Dependency tests
1485    // -----------------------------------------------------------------------
1486
1487    #[test]
1488    fn get_labels_returns_sorted() {
1489        let conn = test_db();
1490        insert_item(&conn, "bn-001", "Item", "open", "default");
1491        insert_label(&conn, "bn-001", "zulu");
1492        insert_label(&conn, "bn-001", "alpha");
1493        insert_label(&conn, "bn-001", "mike");
1494
1495        let labels = get_labels(&conn, "bn-001").unwrap();
1496        assert_eq!(labels.len(), 3);
1497        assert_eq!(labels[0].label, "alpha");
1498        assert_eq!(labels[1].label, "mike");
1499        assert_eq!(labels[2].label, "zulu");
1500    }
1501
1502    #[test]
1503    fn list_labels_returns_counts() {
1504        let conn = test_db();
1505        insert_item(&conn, "bn-001", "Item 1", "open", "default");
1506        insert_item(&conn, "bn-002", "Item 2", "open", "default");
1507        insert_item(&conn, "bn-003", "Item 3", "open", "default");
1508
1509        insert_label(&conn, "bn-001", "area:backend");
1510        insert_label(&conn, "bn-002", "area:backend");
1511        insert_label(&conn, "bn-003", "type:bug");
1512
1513        let labels = list_labels(&conn, None, None).unwrap();
1514        assert_eq!(labels.len(), 2);
1515        assert_eq!(labels[0].name, "area:backend");
1516        assert_eq!(labels[0].count, 2);
1517        assert_eq!(labels[1].name, "type:bug");
1518        assert_eq!(labels[1].count, 1);
1519    }
1520
1521    #[test]
1522    fn get_assignees_returns_sorted() {
1523        let conn = test_db();
1524        insert_item(&conn, "bn-001", "Item", "open", "default");
1525        insert_assignee(&conn, "bn-001", "charlie");
1526        insert_assignee(&conn, "bn-001", "alice");
1527        insert_assignee(&conn, "bn-001", "bob");
1528
1529        let assignees = get_assignees(&conn, "bn-001").unwrap();
1530        assert_eq!(assignees.len(), 3);
1531        assert_eq!(assignees[0].agent, "alice");
1532        assert_eq!(assignees[1].agent, "bob");
1533        assert_eq!(assignees[2].agent, "charlie");
1534    }
1535
1536    #[test]
1537    fn get_comments_newest_first() {
1538        let conn = test_db();
1539        insert_item(&conn, "bn-001", "Item", "open", "default");
1540        conn.execute(
1541            "INSERT INTO item_comments (item_id, event_hash, author, body, created_at_us) \
1542             VALUES ('bn-001', 'hash1', 'alice', 'First comment', 100)",
1543            [],
1544        )
1545        .unwrap();
1546        conn.execute(
1547            "INSERT INTO item_comments (item_id, event_hash, author, body, created_at_us) \
1548             VALUES ('bn-001', 'hash2', 'bob', 'Second comment', 200)",
1549            [],
1550        )
1551        .unwrap();
1552
1553        let comments = get_comments(&conn, "bn-001", None, None).unwrap();
1554        assert_eq!(comments.len(), 2);
1555        assert_eq!(comments[0].body, "Second comment");
1556        assert_eq!(comments[1].body, "First comment");
1557    }
1558
1559    #[test]
1560    fn get_dependencies_and_dependents() {
1561        let conn = test_db();
1562        insert_item(&conn, "bn-001", "Blocker", "open", "default");
1563        insert_item(&conn, "bn-002", "Blocked", "open", "default");
1564        insert_item(&conn, "bn-003", "Also blocked", "open", "default");
1565        insert_dependency(&conn, "bn-002", "bn-001");
1566        insert_dependency(&conn, "bn-003", "bn-001");
1567
1568        // bn-002 depends on bn-001
1569        let deps = get_dependencies(&conn, "bn-002").unwrap();
1570        assert_eq!(deps.len(), 1);
1571        assert_eq!(deps[0].depends_on_item_id, "bn-001");
1572
1573        // bn-001 has two dependents
1574        let dependents = get_dependents(&conn, "bn-001").unwrap();
1575        assert_eq!(dependents.len(), 2);
1576    }
1577
1578    #[test]
1579    fn get_children_returns_ordered() {
1580        let conn = test_db();
1581        insert_item(&conn, "bn-parent", "Parent", "open", "default");
1582        insert_item_full(
1583            &conn,
1584            "bn-child2",
1585            "Second child",
1586            None,
1587            "task",
1588            "open",
1589            "default",
1590            Some("bn-parent"),
1591            "",
1592            200,
1593            200,
1594        );
1595        insert_item_full(
1596            &conn,
1597            "bn-child1",
1598            "First child",
1599            None,
1600            "task",
1601            "open",
1602            "default",
1603            Some("bn-parent"),
1604            "",
1605            100,
1606            100,
1607        );
1608
1609        let children = get_children(&conn, "bn-parent").unwrap();
1610        assert_eq!(children.len(), 2);
1611        assert_eq!(children[0].item_id, "bn-child1");
1612        assert_eq!(children[1].item_id, "bn-child2");
1613    }
1614
1615    // -----------------------------------------------------------------------
1616    // count_items / item_exists
1617    // -----------------------------------------------------------------------
1618
1619    #[test]
1620    fn count_items_with_filter() {
1621        let conn = test_db();
1622        insert_item(&conn, "bn-001", "Open 1", "open", "default");
1623        insert_item(&conn, "bn-002", "Open 2", "open", "default");
1624        insert_item(&conn, "bn-003", "Doing 1", "doing", "default");
1625
1626        let filter = ItemFilter {
1627            state: Some("open".to_string()),
1628            ..Default::default()
1629        };
1630        assert_eq!(count_items(&conn, &filter).unwrap(), 2);
1631        assert_eq!(count_items(&conn, &ItemFilter::default()).unwrap(), 3);
1632    }
1633
1634    #[test]
1635    fn item_exists_works() {
1636        let conn = test_db();
1637        insert_item(&conn, "bn-001", "Exists", "open", "default");
1638
1639        assert!(item_exists(&conn, "bn-001").unwrap());
1640        assert!(!item_exists(&conn, "bn-nope").unwrap());
1641    }
1642
1643    // -----------------------------------------------------------------------
1644    // Projection cursor
1645    // -----------------------------------------------------------------------
1646
1647    #[test]
1648    fn projection_cursor_roundtrip() {
1649        let conn = test_db();
1650
1651        let (offset, hash) = get_projection_cursor(&conn).unwrap();
1652        assert_eq!(offset, 0);
1653        assert!(hash.is_none());
1654
1655        update_projection_cursor(&conn, 42, Some("abc123")).unwrap();
1656
1657        let (offset, hash) = get_projection_cursor(&conn).unwrap();
1658        assert_eq!(offset, 42);
1659        assert_eq!(hash.as_deref(), Some("abc123"));
1660    }
1661
1662    // -----------------------------------------------------------------------
1663    // Graceful recovery
1664    // -----------------------------------------------------------------------
1665
1666    #[test]
1667    fn try_open_projection_missing_file() {
1668        let dir = tempfile::tempdir().expect("tempdir");
1669        let path = dir.path().join("nonexistent.db");
1670        let result = try_open_projection(&path).unwrap();
1671        assert!(result.is_none());
1672    }
1673
1674    #[test]
1675    fn try_open_projection_valid_file() {
1676        let dir = tempfile::tempdir().expect("tempdir");
1677        let path = dir.path().join("test.db");
1678        // Create a valid DB
1679        let _conn = open_projection(&path).unwrap();
1680        drop(_conn);
1681
1682        let conn = try_open_projection(&path).unwrap();
1683        assert!(conn.is_some());
1684    }
1685
1686    #[test]
1687    fn try_open_projection_corrupt_file() {
1688        let dir = tempfile::tempdir().expect("tempdir");
1689        let path = dir.path().join("corrupt.db");
1690        std::fs::write(&path, b"this is not a sqlite database").unwrap();
1691
1692        let result = try_open_projection(&path).unwrap();
1693        assert!(result.is_none());
1694    }
1695
1696    #[test]
1697    fn item_counts_by_state_groups_non_deleted_rows_only() {
1698        let conn = test_db();
1699        conn.execute(
1700            "INSERT INTO items (item_id, title, kind, state, urgency, is_deleted, search_labels, created_at_us, updated_at_us) \
1701             VALUES ('bn-001', 'Open item', 'task', 'open', 'default', 0, '', 1000, 1000)",
1702            [],
1703        )
1704        .unwrap();
1705        conn.execute(
1706            "INSERT INTO items (item_id, title, kind, state, urgency, is_deleted, search_labels, created_at_us, updated_at_us) \
1707             VALUES ('bn-002', 'Deleted item', 'task', 'done', 'default', 1, '', 1000, 1000)",
1708            [],
1709        )
1710        .unwrap();
1711
1712        let by_state = item_counts_by_state(&conn).unwrap();
1713        assert_eq!(by_state.get("open").copied().unwrap_or(0), 1);
1714        assert!(!by_state.contains_key("deleted"));
1715    }
1716
1717    #[test]
1718    fn item_counts_by_kind_and_urgency_include_expected_groups() {
1719        let conn = test_db();
1720        conn.execute(
1721            "INSERT INTO items (item_id, title, kind, state, urgency, is_deleted, search_labels, created_at_us, updated_at_us) \
1722             VALUES ('bn-001', 'Bug item', 'bug', 'open', 'urgent', 0, '', 1000, 1000)",
1723            [],
1724        )
1725        .unwrap();
1726        conn.execute(
1727            "INSERT INTO items (item_id, title, kind, state, urgency, is_deleted, search_labels, created_at_us, updated_at_us) \
1728             VALUES ('bn-002', 'Task item', 'task', 'open', 'default', 0, '', 1000, 1000)",
1729            [],
1730        )
1731        .unwrap();
1732
1733        let by_kind = item_counts_by_kind(&conn).unwrap();
1734        let by_urgency = item_counts_by_urgency(&conn).unwrap();
1735        assert_eq!(by_kind.get("bug").copied().unwrap_or(0), 1);
1736        assert_eq!(by_urgency.get("urgent").copied().unwrap_or(0), 1);
1737        assert_eq!(by_urgency.get("default").copied().unwrap_or(0), 1);
1738    }
1739
1740    #[test]
1741    fn event_counts_from_projected_events_are_counted_by_type_and_agent() {
1742        let conn = test_db();
1743        ensure_tracking_table_for_query_tests(&conn);
1744
1745        conn.execute(
1746            "INSERT INTO projected_events (event_hash, item_id, event_type, projected_at_us, agent) \
1747             VALUES ('blake3:a', 'bn-001', 'item.create', 1, 'alice')",
1748            [],
1749        )
1750        .unwrap();
1751        conn.execute(
1752            "INSERT INTO projected_events (event_hash, item_id, event_type, projected_at_us, agent) \
1753             VALUES ('blake3:b', 'bn-002', 'item.update', 2, 'bob')",
1754            [],
1755        )
1756        .unwrap();
1757
1758        let by_type = event_counts_by_type(&conn).unwrap();
1759        let by_agent = event_counts_by_agent(&conn).unwrap();
1760        assert_eq!(by_type.get("item.create").copied().unwrap_or(0), 1);
1761        assert_eq!(by_type.get("item.update").copied().unwrap_or(0), 1);
1762        assert_eq!(by_agent.get("alice").copied().unwrap_or(0), 1);
1763        assert_eq!(by_agent.get("bob").copied().unwrap_or(0), 1);
1764    }
1765
1766    #[test]
1767    fn get_comments_paginated() {
1768        let conn = test_db();
1769        insert_item(&conn, "bn-001", "Item", "open", "default");
1770        for i in 0..5 {
1771            conn.execute(
1772                "INSERT INTO item_comments (item_id, event_hash, author, body, created_at_us) \
1773                 VALUES (?1, ?2, 'alice', ?3, ?4)",
1774                params![
1775                    "bn-001",
1776                    format!("hash{i}"),
1777                    format!("Comment {i}"),
1778                    100 + i as i64
1779                ],
1780            )
1781            .unwrap();
1782        }
1783
1784        // Newest first order: Comment 4 (104), Comment 3 (103), ...
1785
1786        let page1 = get_comments(&conn, "bn-001", Some(2), None).unwrap();
1787        assert_eq!(page1.len(), 2);
1788        assert_eq!(page1[0].body, "Comment 4");
1789        assert_eq!(page1[1].body, "Comment 3");
1790
1791        let page2 = get_comments(&conn, "bn-001", Some(2), Some(2)).unwrap();
1792        assert_eq!(page2.len(), 2);
1793        assert_eq!(page2[0].body, "Comment 2");
1794        assert_eq!(page2[1].body, "Comment 1");
1795    }
1796
1797    fn ensure_tracking_table_for_query_tests(conn: &Connection) {
1798        let sql = "CREATE TABLE IF NOT EXISTS projected_events (
1799            event_hash TEXT PRIMARY KEY,
1800            item_id TEXT NOT NULL,
1801            event_type TEXT NOT NULL,
1802            projected_at_us INTEGER NOT NULL,
1803            agent TEXT NOT NULL DEFAULT ''
1804        );";
1805        conn.execute(sql, []).expect("create projected_events");
1806    }
1807}
bones_core/db/query.rs

bones_core/db/
query.rs
