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    // Derive the .bones/ directory from the db path (e.g. .bones/bones.db → .bones/).
891    // If we can locate it, delegate to ensure_projection which auto-rebuilds
892    // a stale or missing projection from the event log.
893    if let Some(bones_dir) = path.parent()
894        && bones_dir.join("events").is_dir()
895    {
896        return super::ensure_projection(bones_dir);
897    }
898
899    // Fallback: no events directory found — open without auto-rebuild.
900    try_open_projection_raw(path)
901}
902
903/// Open the projection without triggering an auto-rebuild.
904///
905/// Used internally by [`ensure_projection`](super::ensure_projection) to
906/// avoid infinite recursion, and as a fallback when the events directory
907/// is not discoverable from the db path.
908///
909/// # Errors
910///
911/// Returns an error if the database exists but cannot be opened or fails
912/// an integrity check.
913pub fn try_open_projection_raw(path: &std::path::Path) -> Result<Option<Connection>> {
914    if !path.exists() {
915        return Ok(None);
916    }
917
918    match super::open_projection(path) {
919        Ok(conn) => {
920            // Quick integrity check — verify projection_meta is readable
921            if get_projection_cursor(&conn).is_ok() {
922                Ok(Some(conn))
923            } else {
924                tracing::warn!(
925                    path = %path.display(),
926                    "projection database corrupt, needs rebuild"
927                );
928                Ok(None)
929            }
930        }
931        Err(e) => {
932            // Include the full causal chain so pragma/lock/IO failures are diagnosable.
933            tracing::warn!(
934                path = %path.display(),
935                error = format!("{e:#}"),
936                "failed to open projection database, needs rebuild"
937            );
938            Ok(None)
939        }
940    }
941}
942
943// ---------------------------------------------------------------------------
944// Tests
945// ---------------------------------------------------------------------------
946
947#[cfg(test)]
948mod tests {
949    use super::*;
950    use crate::db::{migrations, open_projection};
951    use rusqlite::{Connection, params};
952
953    /// Create an in-memory migrated database with test data.
954    fn test_db() -> Connection {
955        let mut conn = Connection::open_in_memory().expect("open in-memory db");
956        migrations::migrate(&mut conn).expect("migrate");
957        conn
958    }
959
960    /// Insert a test item with minimal required fields.
961    fn insert_item(conn: &Connection, id: &str, title: &str, state: &str, urgency: &str) {
962        conn.execute(
963            "INSERT INTO items (item_id, title, kind, state, urgency, \
964             is_deleted, search_labels, created_at_us, updated_at_us) \
965             VALUES (?1, ?2, 'task', ?3, ?4, 0, '', ?5, ?6)",
966            params![id, title, state, urgency, 1000_i64, 2000_i64],
967        )
968        .expect("insert item");
969    }
970
971    fn insert_item_full(
972        conn: &Connection,
973        id: &str,
974        title: &str,
975        desc: Option<&str>,
976        kind: &str,
977        state: &str,
978        urgency: &str,
979        parent_id: Option<&str>,
980        labels: &str,
981        created: i64,
982        updated: i64,
983    ) {
984        conn.execute(
985            "INSERT INTO items (item_id, title, description, kind, state, urgency, \
986             parent_id, is_deleted, search_labels, created_at_us, updated_at_us) \
987             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, 0, ?8, ?9, ?10)",
988            params![
989                id, title, desc, kind, state, urgency, parent_id, labels, created, updated
990            ],
991        )
992        .expect("insert full item");
993    }
994
995    fn insert_label(conn: &Connection, item_id: &str, label: &str) {
996        conn.execute(
997            "INSERT INTO item_labels (item_id, label, created_at_us) VALUES (?1, ?2, 100)",
998            params![item_id, label],
999        )
1000        .expect("insert label");
1001    }
1002
1003    fn insert_assignee(conn: &Connection, item_id: &str, agent: &str) {
1004        conn.execute(
1005            "INSERT INTO item_assignees (item_id, agent, created_at_us) VALUES (?1, ?2, 100)",
1006            params![item_id, agent],
1007        )
1008        .expect("insert assignee");
1009    }
1010
1011    fn _insert_comment(conn: &Connection, item_id: &str, hash: &str, author: &str, body: &str) {
1012        conn.execute(
1013            "INSERT INTO item_comments (item_id, event_hash, author, body, created_at_us) \
1014             VALUES (?1, ?2, ?3, ?4, 100)",
1015            params![item_id, hash, author, body],
1016        )
1017        .expect("insert comment");
1018    }
1019
1020    fn insert_dependency(conn: &Connection, item_id: &str, depends_on: &str) {
1021        conn.execute(
1022            "INSERT INTO item_dependencies (item_id, depends_on_item_id, link_type, created_at_us) \
1023             VALUES (?1, ?2, 'blocks', 100)",
1024            params![item_id, depends_on],
1025        )
1026        .expect("insert dependency");
1027    }
1028
1029    // -----------------------------------------------------------------------
1030    // get_item tests
1031    // -----------------------------------------------------------------------
1032
1033    #[test]
1034    fn get_item_found() {
1035        let conn = test_db();
1036        insert_item(&conn, "bn-001", "Fix auth timeout", "open", "urgent");
1037
1038        let item = get_item(&conn, "bn-001", false).unwrap().unwrap();
1039        assert_eq!(item.item_id, "bn-001");
1040        assert_eq!(item.title, "Fix auth timeout");
1041        assert_eq!(item.state, "open");
1042        assert_eq!(item.urgency, "urgent");
1043        assert!(!item.is_deleted);
1044    }
1045
1046    #[test]
1047    fn get_item_not_found() {
1048        let conn = test_db();
1049        let item = get_item(&conn, "bn-nonexistent", false).unwrap();
1050        assert!(item.is_none());
1051    }
1052
1053    #[test]
1054    fn get_item_excludes_deleted() {
1055        let conn = test_db();
1056        insert_item(&conn, "bn-del", "Deleted item", "open", "default");
1057        conn.execute(
1058            "UPDATE items SET is_deleted = 1, deleted_at_us = 3000 WHERE item_id = 'bn-del'",
1059            [],
1060        )
1061        .unwrap();
1062
1063        assert!(get_item(&conn, "bn-del", false).unwrap().is_none());
1064        let item = get_item(&conn, "bn-del", true).unwrap().unwrap();
1065        assert!(item.is_deleted);
1066        assert_eq!(item.deleted_at_us, Some(3000));
1067    }
1068
1069    // -----------------------------------------------------------------------
1070    // list_items tests
1071    // -----------------------------------------------------------------------
1072
1073    #[test]
1074    fn list_items_no_filter() {
1075        let conn = test_db();
1076        insert_item(&conn, "bn-001", "First", "open", "default");
1077        insert_item(&conn, "bn-002", "Second", "doing", "urgent");
1078
1079        let items = list_items(&conn, &ItemFilter::default()).unwrap();
1080        assert_eq!(items.len(), 2);
1081    }
1082
1083    #[test]
1084    fn list_items_filter_by_state() {
1085        let conn = test_db();
1086        insert_item(&conn, "bn-001", "Open item", "open", "default");
1087        insert_item(&conn, "bn-002", "Doing item", "doing", "default");
1088
1089        let filter = ItemFilter {
1090            state: Some("open".to_string()),
1091            ..Default::default()
1092        };
1093        let items = list_items(&conn, &filter).unwrap();
1094        assert_eq!(items.len(), 1);
1095        assert_eq!(items[0].item_id, "bn-001");
1096    }
1097
1098    #[test]
1099    fn list_items_filter_by_kind() {
1100        let conn = test_db();
1101        insert_item_full(
1102            &conn, "bn-001", "A bug", None, "bug", "open", "default", None, "", 100, 200,
1103        );
1104        insert_item_full(
1105            &conn, "bn-002", "A task", None, "task", "open", "default", None, "", 100, 200,
1106        );
1107
1108        let filter = ItemFilter {
1109            kind: Some("bug".to_string()),
1110            ..Default::default()
1111        };
1112        let items = list_items(&conn, &filter).unwrap();
1113        assert_eq!(items.len(), 1);
1114        assert_eq!(items[0].item_id, "bn-001");
1115    }
1116
1117    #[test]
1118    fn list_items_filter_by_urgency() {
1119        let conn = test_db();
1120        insert_item(&conn, "bn-001", "Urgent item", "open", "urgent");
1121        insert_item(&conn, "bn-002", "Default item", "open", "default");
1122        insert_item(&conn, "bn-003", "Punt item", "open", "punt");
1123
1124        let filter = ItemFilter {
1125            urgency: Some("urgent".to_string()),
1126            ..Default::default()
1127        };
1128        let items = list_items(&conn, &filter).unwrap();
1129        assert_eq!(items.len(), 1);
1130        assert_eq!(items[0].item_id, "bn-001");
1131    }
1132
1133    #[test]
1134    fn list_items_filter_by_label() {
1135        let conn = test_db();
1136        insert_item(&conn, "bn-001", "Backend bug", "open", "default");
1137        insert_item(&conn, "bn-002", "Frontend bug", "open", "default");
1138        insert_label(&conn, "bn-001", "backend");
1139        insert_label(&conn, "bn-002", "frontend");
1140
1141        let filter = ItemFilter {
1142            label: Some("backend".to_string()),
1143            ..Default::default()
1144        };
1145        let items = list_items(&conn, &filter).unwrap();
1146        assert_eq!(items.len(), 1);
1147        assert_eq!(items[0].item_id, "bn-001");
1148    }
1149
1150    #[test]
1151    fn list_items_filter_by_assignee() {
1152        let conn = test_db();
1153        insert_item(&conn, "bn-001", "Alice's task", "open", "default");
1154        insert_item(&conn, "bn-002", "Bob's task", "open", "default");
1155        insert_assignee(&conn, "bn-001", "alice");
1156        insert_assignee(&conn, "bn-002", "bob");
1157
1158        let filter = ItemFilter {
1159            assignee: Some("alice".to_string()),
1160            ..Default::default()
1161        };
1162        let items = list_items(&conn, &filter).unwrap();
1163        assert_eq!(items.len(), 1);
1164        assert_eq!(items[0].item_id, "bn-001");
1165    }
1166
1167    #[test]
1168    fn list_items_filter_by_parent() {
1169        let conn = test_db();
1170        insert_item(&conn, "bn-parent", "Parent", "open", "default");
1171        insert_item_full(
1172            &conn,
1173            "bn-child1",
1174            "Child 1",
1175            None,
1176            "task",
1177            "open",
1178            "default",
1179            Some("bn-parent"),
1180            "",
1181            100,
1182            200,
1183        );
1184        insert_item_full(
1185            &conn,
1186            "bn-child2",
1187            "Child 2",
1188            None,
1189            "task",
1190            "open",
1191            "default",
1192            Some("bn-parent"),
1193            "",
1194            101,
1195            201,
1196        );
1197        insert_item(&conn, "bn-other", "Other", "open", "default");
1198
1199        let filter = ItemFilter {
1200            parent_id: Some("bn-parent".to_string()),
1201            ..Default::default()
1202        };
1203        let items = list_items(&conn, &filter).unwrap();
1204        assert_eq!(items.len(), 2);
1205    }
1206
1207    #[test]
1208    fn list_items_combined_filters() {
1209        let conn = test_db();
1210        insert_item(&conn, "bn-001", "Urgent open", "open", "urgent");
1211        insert_item(&conn, "bn-002", "Default open", "open", "default");
1212        insert_item(&conn, "bn-003", "Urgent doing", "doing", "urgent");
1213
1214        let filter = ItemFilter {
1215            state: Some("open".to_string()),
1216            urgency: Some("urgent".to_string()),
1217            ..Default::default()
1218        };
1219        let items = list_items(&conn, &filter).unwrap();
1220        assert_eq!(items.len(), 1);
1221        assert_eq!(items[0].item_id, "bn-001");
1222    }
1223
1224    #[test]
1225    fn list_items_with_limit_and_offset() {
1226        let conn = test_db();
1227        for i in 0..10 {
1228            insert_item_full(
1229                &conn,
1230                &format!("bn-{i:03}"),
1231                &format!("Item {i}"),
1232                None,
1233                "task",
1234                "open",
1235                "default",
1236                None,
1237                "",
1238                i * 100,
1239                i * 100 + 50,
1240            );
1241        }
1242
1243        let filter = ItemFilter {
1244            limit: Some(3),
1245            sort: SortOrder::CreatedAsc,
1246            ..Default::default()
1247        };
1248        let items = list_items(&conn, &filter).unwrap();
1249        assert_eq!(items.len(), 3);
1250        assert_eq!(items[0].item_id, "bn-000");
1251
1252        let filter2 = ItemFilter {
1253            limit: Some(3),
1254            offset: Some(3),
1255            sort: SortOrder::CreatedAsc,
1256            ..Default::default()
1257        };
1258        let items2 = list_items(&conn, &filter2).unwrap();
1259        assert_eq!(items2.len(), 3);
1260        assert_eq!(items2[0].item_id, "bn-003");
1261    }
1262
1263    #[test]
1264    fn list_items_stable_tie_breaks_use_item_id() {
1265        let conn = test_db();
1266
1267        // Same timestamps force ORDER BY tie-break behavior.
1268        insert_item_full(
1269            &conn, "bn-010", "Ten", None, "task", "open", "default", None, "", 100, 200,
1270        );
1271        insert_item_full(
1272            &conn, "bn-002", "Two", None, "task", "open", "default", None, "", 100, 200,
1273        );
1274        insert_item_full(
1275            &conn, "bn-001", "One", None, "task", "open", "default", None, "", 100, 200,
1276        );
1277
1278        let asc = ItemFilter {
1279            sort: SortOrder::CreatedAsc,
1280            ..Default::default()
1281        };
1282        let asc_items = list_items(&conn, &asc).unwrap();
1283        assert_eq!(asc_items[0].item_id, "bn-001");
1284        assert_eq!(asc_items[1].item_id, "bn-002");
1285        assert_eq!(asc_items[2].item_id, "bn-010");
1286
1287        let desc = ItemFilter {
1288            sort: SortOrder::UpdatedDesc,
1289            ..Default::default()
1290        };
1291        let desc_items = list_items(&conn, &desc).unwrap();
1292        assert_eq!(desc_items[0].item_id, "bn-001");
1293        assert_eq!(desc_items[1].item_id, "bn-002");
1294        assert_eq!(desc_items[2].item_id, "bn-010");
1295    }
1296
1297    #[test]
1298    fn list_items_excludes_deleted() {
1299        let conn = test_db();
1300        insert_item(&conn, "bn-001", "Active", "open", "default");
1301        insert_item(&conn, "bn-002", "Deleted", "open", "default");
1302        conn.execute(
1303            "UPDATE items SET is_deleted = 1 WHERE item_id = 'bn-002'",
1304            [],
1305        )
1306        .unwrap();
1307
1308        let items = list_items(&conn, &ItemFilter::default()).unwrap();
1309        assert_eq!(items.len(), 1);
1310        assert_eq!(items[0].item_id, "bn-001");
1311
1312        let filter = ItemFilter {
1313            include_deleted: true,
1314            ..Default::default()
1315        };
1316        let items_with_deleted = list_items(&conn, &filter).unwrap();
1317        assert_eq!(items_with_deleted.len(), 2);
1318    }
1319
1320    // -----------------------------------------------------------------------
1321    // Sort order tests
1322    // -----------------------------------------------------------------------
1323
1324    #[test]
1325    fn list_items_priority_sort() {
1326        let conn = test_db();
1327        insert_item(&conn, "bn-001", "Default", "open", "default");
1328        insert_item(&conn, "bn-002", "Urgent", "open", "urgent");
1329        insert_item(&conn, "bn-003", "Punt", "open", "punt");
1330
1331        let filter = ItemFilter {
1332            sort: SortOrder::Priority,
1333            ..Default::default()
1334        };
1335        let items = list_items(&conn, &filter).unwrap();
1336        assert_eq!(items[0].urgency, "urgent");
1337        assert_eq!(items[1].urgency, "default");
1338        assert_eq!(items[2].urgency, "punt");
1339    }
1340
1341    #[test]
1342    fn sort_order_parse_roundtrip() {
1343        for order in [
1344            SortOrder::CreatedDesc,
1345            SortOrder::CreatedAsc,
1346            SortOrder::UpdatedDesc,
1347            SortOrder::UpdatedAsc,
1348            SortOrder::Priority,
1349        ] {
1350            let s = order.to_string();
1351            let parsed: SortOrder = s.parse().unwrap();
1352            assert_eq!(order, parsed);
1353        }
1354    }
1355
1356    #[test]
1357    fn sort_order_parse_aliases() {
1358        assert_eq!(
1359            "newest".parse::<SortOrder>().unwrap(),
1360            SortOrder::CreatedDesc
1361        );
1362        assert_eq!(
1363            "oldest".parse::<SortOrder>().unwrap(),
1364            SortOrder::CreatedAsc
1365        );
1366        assert_eq!(
1367            "recent".parse::<SortOrder>().unwrap(),
1368            SortOrder::UpdatedDesc
1369        );
1370        assert_eq!("stale".parse::<SortOrder>().unwrap(), SortOrder::UpdatedAsc);
1371        assert_eq!("triage".parse::<SortOrder>().unwrap(), SortOrder::Priority);
1372    }
1373
1374    // -----------------------------------------------------------------------
1375    // Search tests
1376    // -----------------------------------------------------------------------
1377
1378    #[test]
1379    fn search_fts5_finds_by_title() {
1380        let conn = test_db();
1381        insert_item_full(
1382            &conn,
1383            "bn-001",
1384            "Authentication timeout regression",
1385            Some("Retries fail after 30 seconds"),
1386            "task",
1387            "open",
1388            "urgent",
1389            None,
1390            "auth backend",
1391            100,
1392            200,
1393        );
1394        insert_item_full(
1395            &conn,
1396            "bn-002",
1397            "Update documentation",
1398            Some("Fix typos in README"),
1399            "task",
1400            "open",
1401            "default",
1402            None,
1403            "docs",
1404            101,
1405            201,
1406        );
1407
1408        let hits = search(&conn, "authentication", 10).unwrap();
1409        assert_eq!(hits.len(), 1);
1410        assert_eq!(hits[0].item_id, "bn-001");
1411    }
1412
1413    #[test]
1414    fn search_fts5_stemming() {
1415        let conn = test_db();
1416        insert_item_full(
1417            &conn,
1418            "bn-001",
1419            "Running tests slowly",
1420            None,
1421            "task",
1422            "open",
1423            "default",
1424            None,
1425            "",
1426            100,
1427            200,
1428        );
1429
1430        // Porter stemmer: "run" should match "running"
1431        let hits = search(&conn, "run", 10).unwrap();
1432        assert_eq!(hits.len(), 1);
1433        assert_eq!(hits[0].item_id, "bn-001");
1434    }
1435
1436    #[test]
1437    fn search_fts5_prefix() {
1438        let conn = test_db();
1439        insert_item_full(
1440            &conn,
1441            "bn-001",
1442            "Authentication service broken",
1443            None,
1444            "task",
1445            "open",
1446            "default",
1447            None,
1448            "",
1449            100,
1450            200,
1451        );
1452
1453        // Prefix search: "auth*" matches "authentication"
1454        let hits = search(&conn, "auth*", 10).unwrap();
1455        assert_eq!(hits.len(), 1);
1456    }
1457
1458    #[test]
1459    fn search_fts5_excludes_deleted() {
1460        let conn = test_db();
1461        insert_item_full(
1462            &conn,
1463            "bn-001",
1464            "Important authentication bug",
1465            None,
1466            "task",
1467            "open",
1468            "default",
1469            None,
1470            "",
1471            100,
1472            200,
1473        );
1474        conn.execute(
1475            "UPDATE items SET is_deleted = 1 WHERE item_id = 'bn-001'",
1476            [],
1477        )
1478        .unwrap();
1479
1480        let hits = search(&conn, "authentication", 10).unwrap();
1481        assert!(hits.is_empty());
1482    }
1483
1484    #[test]
1485    fn search_fts5_limit() {
1486        let conn = test_db();
1487        for i in 0..20 {
1488            insert_item_full(
1489                &conn,
1490                &format!("bn-{i:03}"),
1491                &format!("Authentication bug {i}"),
1492                None,
1493                "task",
1494                "open",
1495                "default",
1496                None,
1497                "",
1498                i * 100,
1499                i * 100 + 50,
1500            );
1501        }
1502
1503        let hits = search(&conn, "authentication", 5).unwrap();
1504        assert_eq!(hits.len(), 5);
1505    }
1506
1507    // -----------------------------------------------------------------------
1508    // Label / Assignee / Comment / Dependency tests
1509    // -----------------------------------------------------------------------
1510
1511    #[test]
1512    fn get_labels_returns_sorted() {
1513        let conn = test_db();
1514        insert_item(&conn, "bn-001", "Item", "open", "default");
1515        insert_label(&conn, "bn-001", "zulu");
1516        insert_label(&conn, "bn-001", "alpha");
1517        insert_label(&conn, "bn-001", "mike");
1518
1519        let labels = get_labels(&conn, "bn-001").unwrap();
1520        assert_eq!(labels.len(), 3);
1521        assert_eq!(labels[0].label, "alpha");
1522        assert_eq!(labels[1].label, "mike");
1523        assert_eq!(labels[2].label, "zulu");
1524    }
1525
1526    #[test]
1527    fn list_labels_returns_counts() {
1528        let conn = test_db();
1529        insert_item(&conn, "bn-001", "Item 1", "open", "default");
1530        insert_item(&conn, "bn-002", "Item 2", "open", "default");
1531        insert_item(&conn, "bn-003", "Item 3", "open", "default");
1532
1533        insert_label(&conn, "bn-001", "area:backend");
1534        insert_label(&conn, "bn-002", "area:backend");
1535        insert_label(&conn, "bn-003", "type:bug");
1536
1537        let labels = list_labels(&conn, None, None).unwrap();
1538        assert_eq!(labels.len(), 2);
1539        assert_eq!(labels[0].name, "area:backend");
1540        assert_eq!(labels[0].count, 2);
1541        assert_eq!(labels[1].name, "type:bug");
1542        assert_eq!(labels[1].count, 1);
1543    }
1544
1545    #[test]
1546    fn get_assignees_returns_sorted() {
1547        let conn = test_db();
1548        insert_item(&conn, "bn-001", "Item", "open", "default");
1549        insert_assignee(&conn, "bn-001", "charlie");
1550        insert_assignee(&conn, "bn-001", "alice");
1551        insert_assignee(&conn, "bn-001", "bob");
1552
1553        let assignees = get_assignees(&conn, "bn-001").unwrap();
1554        assert_eq!(assignees.len(), 3);
1555        assert_eq!(assignees[0].agent, "alice");
1556        assert_eq!(assignees[1].agent, "bob");
1557        assert_eq!(assignees[2].agent, "charlie");
1558    }
1559
1560    #[test]
1561    fn get_comments_newest_first() {
1562        let conn = test_db();
1563        insert_item(&conn, "bn-001", "Item", "open", "default");
1564        conn.execute(
1565            "INSERT INTO item_comments (item_id, event_hash, author, body, created_at_us) \
1566             VALUES ('bn-001', 'hash1', 'alice', 'First comment', 100)",
1567            [],
1568        )
1569        .unwrap();
1570        conn.execute(
1571            "INSERT INTO item_comments (item_id, event_hash, author, body, created_at_us) \
1572             VALUES ('bn-001', 'hash2', 'bob', 'Second comment', 200)",
1573            [],
1574        )
1575        .unwrap();
1576
1577        let comments = get_comments(&conn, "bn-001", None, None).unwrap();
1578        assert_eq!(comments.len(), 2);
1579        assert_eq!(comments[0].body, "Second comment");
1580        assert_eq!(comments[1].body, "First comment");
1581    }
1582
1583    #[test]
1584    fn get_dependencies_and_dependents() {
1585        let conn = test_db();
1586        insert_item(&conn, "bn-001", "Blocker", "open", "default");
1587        insert_item(&conn, "bn-002", "Blocked", "open", "default");
1588        insert_item(&conn, "bn-003", "Also blocked", "open", "default");
1589        insert_dependency(&conn, "bn-002", "bn-001");
1590        insert_dependency(&conn, "bn-003", "bn-001");
1591
1592        // bn-002 depends on bn-001
1593        let deps = get_dependencies(&conn, "bn-002").unwrap();
1594        assert_eq!(deps.len(), 1);
1595        assert_eq!(deps[0].depends_on_item_id, "bn-001");
1596
1597        // bn-001 has two dependents
1598        let dependents = get_dependents(&conn, "bn-001").unwrap();
1599        assert_eq!(dependents.len(), 2);
1600    }
1601
1602    #[test]
1603    fn get_children_returns_ordered() {
1604        let conn = test_db();
1605        insert_item(&conn, "bn-parent", "Parent", "open", "default");
1606        insert_item_full(
1607            &conn,
1608            "bn-child2",
1609            "Second child",
1610            None,
1611            "task",
1612            "open",
1613            "default",
1614            Some("bn-parent"),
1615            "",
1616            200,
1617            200,
1618        );
1619        insert_item_full(
1620            &conn,
1621            "bn-child1",
1622            "First child",
1623            None,
1624            "task",
1625            "open",
1626            "default",
1627            Some("bn-parent"),
1628            "",
1629            100,
1630            100,
1631        );
1632
1633        let children = get_children(&conn, "bn-parent").unwrap();
1634        assert_eq!(children.len(), 2);
1635        assert_eq!(children[0].item_id, "bn-child1");
1636        assert_eq!(children[1].item_id, "bn-child2");
1637    }
1638
1639    // -----------------------------------------------------------------------
1640    // count_items / item_exists
1641    // -----------------------------------------------------------------------
1642
1643    #[test]
1644    fn count_items_with_filter() {
1645        let conn = test_db();
1646        insert_item(&conn, "bn-001", "Open 1", "open", "default");
1647        insert_item(&conn, "bn-002", "Open 2", "open", "default");
1648        insert_item(&conn, "bn-003", "Doing 1", "doing", "default");
1649
1650        let filter = ItemFilter {
1651            state: Some("open".to_string()),
1652            ..Default::default()
1653        };
1654        assert_eq!(count_items(&conn, &filter).unwrap(), 2);
1655        assert_eq!(count_items(&conn, &ItemFilter::default()).unwrap(), 3);
1656    }
1657
1658    #[test]
1659    fn item_exists_works() {
1660        let conn = test_db();
1661        insert_item(&conn, "bn-001", "Exists", "open", "default");
1662
1663        assert!(item_exists(&conn, "bn-001").unwrap());
1664        assert!(!item_exists(&conn, "bn-nope").unwrap());
1665    }
1666
1667    // -----------------------------------------------------------------------
1668    // Projection cursor
1669    // -----------------------------------------------------------------------
1670
1671    #[test]
1672    fn projection_cursor_roundtrip() {
1673        let conn = test_db();
1674
1675        let (offset, hash) = get_projection_cursor(&conn).unwrap();
1676        assert_eq!(offset, 0);
1677        assert!(hash.is_none());
1678
1679        update_projection_cursor(&conn, 42, Some("abc123")).unwrap();
1680
1681        let (offset, hash) = get_projection_cursor(&conn).unwrap();
1682        assert_eq!(offset, 42);
1683        assert_eq!(hash.as_deref(), Some("abc123"));
1684    }
1685
1686    // -----------------------------------------------------------------------
1687    // Graceful recovery
1688    // -----------------------------------------------------------------------
1689
1690    #[test]
1691    fn try_open_projection_missing_file() {
1692        let dir = tempfile::tempdir().expect("tempdir");
1693        let path = dir.path().join("nonexistent.db");
1694        let result = try_open_projection(&path).unwrap();
1695        assert!(result.is_none());
1696    }
1697
1698    #[test]
1699    fn try_open_projection_valid_file() {
1700        let dir = tempfile::tempdir().expect("tempdir");
1701        let path = dir.path().join("test.db");
1702        // Create a valid DB
1703        let _conn = open_projection(&path).unwrap();
1704        drop(_conn);
1705
1706        let conn = try_open_projection(&path).unwrap();
1707        assert!(conn.is_some());
1708    }
1709
1710    #[test]
1711    fn try_open_projection_corrupt_file() {
1712        let dir = tempfile::tempdir().expect("tempdir");
1713        let path = dir.path().join("corrupt.db");
1714        std::fs::write(&path, b"this is not a sqlite database").unwrap();
1715
1716        let result = try_open_projection(&path).unwrap();
1717        assert!(result.is_none());
1718    }
1719
1720    #[test]
1721    fn item_counts_by_state_groups_non_deleted_rows_only() {
1722        let conn = test_db();
1723        conn.execute(
1724            "INSERT INTO items (item_id, title, kind, state, urgency, is_deleted, search_labels, created_at_us, updated_at_us) \
1725             VALUES ('bn-001', 'Open item', 'task', 'open', 'default', 0, '', 1000, 1000)",
1726            [],
1727        )
1728        .unwrap();
1729        conn.execute(
1730            "INSERT INTO items (item_id, title, kind, state, urgency, is_deleted, search_labels, created_at_us, updated_at_us) \
1731             VALUES ('bn-002', 'Deleted item', 'task', 'done', 'default', 1, '', 1000, 1000)",
1732            [],
1733        )
1734        .unwrap();
1735
1736        let by_state = item_counts_by_state(&conn).unwrap();
1737        assert_eq!(by_state.get("open").copied().unwrap_or(0), 1);
1738        assert!(!by_state.contains_key("deleted"));
1739    }
1740
1741    #[test]
1742    fn item_counts_by_kind_and_urgency_include_expected_groups() {
1743        let conn = test_db();
1744        conn.execute(
1745            "INSERT INTO items (item_id, title, kind, state, urgency, is_deleted, search_labels, created_at_us, updated_at_us) \
1746             VALUES ('bn-001', 'Bug item', 'bug', 'open', 'urgent', 0, '', 1000, 1000)",
1747            [],
1748        )
1749        .unwrap();
1750        conn.execute(
1751            "INSERT INTO items (item_id, title, kind, state, urgency, is_deleted, search_labels, created_at_us, updated_at_us) \
1752             VALUES ('bn-002', 'Task item', 'task', 'open', 'default', 0, '', 1000, 1000)",
1753            [],
1754        )
1755        .unwrap();
1756
1757        let by_kind = item_counts_by_kind(&conn).unwrap();
1758        let by_urgency = item_counts_by_urgency(&conn).unwrap();
1759        assert_eq!(by_kind.get("bug").copied().unwrap_or(0), 1);
1760        assert_eq!(by_urgency.get("urgent").copied().unwrap_or(0), 1);
1761        assert_eq!(by_urgency.get("default").copied().unwrap_or(0), 1);
1762    }
1763
1764    #[test]
1765    fn event_counts_from_projected_events_are_counted_by_type_and_agent() {
1766        let conn = test_db();
1767        ensure_tracking_table_for_query_tests(&conn);
1768
1769        conn.execute(
1770            "INSERT INTO projected_events (event_hash, item_id, event_type, projected_at_us, agent) \
1771             VALUES ('blake3:a', 'bn-001', 'item.create', 1, 'alice')",
1772            [],
1773        )
1774        .unwrap();
1775        conn.execute(
1776            "INSERT INTO projected_events (event_hash, item_id, event_type, projected_at_us, agent) \
1777             VALUES ('blake3:b', 'bn-002', 'item.update', 2, 'bob')",
1778            [],
1779        )
1780        .unwrap();
1781
1782        let by_type = event_counts_by_type(&conn).unwrap();
1783        let by_agent = event_counts_by_agent(&conn).unwrap();
1784        assert_eq!(by_type.get("item.create").copied().unwrap_or(0), 1);
1785        assert_eq!(by_type.get("item.update").copied().unwrap_or(0), 1);
1786        assert_eq!(by_agent.get("alice").copied().unwrap_or(0), 1);
1787        assert_eq!(by_agent.get("bob").copied().unwrap_or(0), 1);
1788    }
1789
1790    #[test]
1791    fn get_comments_paginated() {
1792        let conn = test_db();
1793        insert_item(&conn, "bn-001", "Item", "open", "default");
1794        for i in 0..5 {
1795            conn.execute(
1796                "INSERT INTO item_comments (item_id, event_hash, author, body, created_at_us) \
1797                 VALUES (?1, ?2, 'alice', ?3, ?4)",
1798                params![
1799                    "bn-001",
1800                    format!("hash{i}"),
1801                    format!("Comment {i}"),
1802                    100 + i as i64
1803                ],
1804            )
1805            .unwrap();
1806        }
1807
1808        // Newest first order: Comment 4 (104), Comment 3 (103), ...
1809
1810        let page1 = get_comments(&conn, "bn-001", Some(2), None).unwrap();
1811        assert_eq!(page1.len(), 2);
1812        assert_eq!(page1[0].body, "Comment 4");
1813        assert_eq!(page1[1].body, "Comment 3");
1814
1815        let page2 = get_comments(&conn, "bn-001", Some(2), Some(2)).unwrap();
1816        assert_eq!(page2.len(), 2);
1817        assert_eq!(page2[0].body, "Comment 2");
1818        assert_eq!(page2[1].body, "Comment 1");
1819    }
1820
1821    fn ensure_tracking_table_for_query_tests(conn: &Connection) {
1822        let sql = "CREATE TABLE IF NOT EXISTS projected_events (
1823            event_hash TEXT PRIMARY KEY,
1824            item_id TEXT NOT NULL,
1825            event_type TEXT NOT NULL,
1826            projected_at_us INTEGER NOT NULL,
1827            agent TEXT NOT NULL DEFAULT ''
1828        );";
1829        conn.execute(sql, []).expect("create projected_events");
1830    }
1831}
bones_core/db/query.rs

bones_core/db/
query.rs
