remem/db/query/
user_context_stats.rs

1use anyhow::Result;
2use rusqlite::Connection;
3
4use super::shared::collect_rows;
5
6#[derive(Debug, Clone, Default, PartialEq, Eq)]
7pub struct UserContextStats {
8    pub claims_total: i64,
9    pub claims_active: i64,
10    pub claims_suppressed: i64,
11    pub claims_deleted: i64,
12    pub candidates_total: i64,
13    pub candidates_pending_review: i64,
14    pub candidates_auto_promoted: i64,
15    pub candidate_block_reasons: Vec<UserContextBlockReasonStat>,
16}
17
18#[derive(Debug, Clone, PartialEq, Eq)]
19pub struct UserContextBlockReasonStat {
20    pub reason: Option<String>,
21    pub pending: i64,
22}
23
24pub fn query_user_context_stats(conn: &Connection) -> Result<UserContextStats> {
25    if !crate::retrieval::temporal::sqlite_table_exists(conn, "user_context_claims")?
26        || !crate::retrieval::temporal::sqlite_table_exists(conn, "user_context_candidates")?
27    {
28        return Ok(UserContextStats::default());
29    }
30
31    let candidate_block_reasons = {
32        let mut stmt = conn.prepare(
33            "SELECT auto_promote_block_reason, COUNT(*) AS pending
34             FROM user_context_candidates
35             WHERE review_status = 'pending_review'
36             GROUP BY auto_promote_block_reason
37             ORDER BY pending DESC, auto_promote_block_reason ASC",
38        )?;
39        let rows = stmt.query_map([], |row| {
40            Ok(UserContextBlockReasonStat {
41                reason: row.get(0)?,
42                pending: row.get(1)?,
43            })
44        })?;
45        collect_rows(rows)?
46    };
47
48    Ok(UserContextStats {
49        claims_total: count_claims(conn, None)?,
50        claims_active: count_claims(conn, Some("active"))?,
51        claims_suppressed: count_claims(conn, Some("suppressed"))?,
52        claims_deleted: count_claims(conn, Some("deleted"))?,
53        candidates_total: count_candidates(conn, None)?,
54        candidates_pending_review: count_candidates(conn, Some("pending_review"))?,
55        candidates_auto_promoted: count_candidates(conn, Some("auto_promoted"))?,
56        candidate_block_reasons,
57    })
58}
59
60fn count_claims(conn: &Connection, status: Option<&str>) -> Result<i64> {
61    match status {
62        Some(status) => conn
63            .query_row(
64                "SELECT COUNT(*) FROM user_context_claims WHERE status = ?1",
65                [status],
66                |row| row.get(0),
67            )
68            .map_err(Into::into),
69        None => conn
70            .query_row("SELECT COUNT(*) FROM user_context_claims", [], |row| {
71                row.get(0)
72            })
73            .map_err(Into::into),
74    }
75}
76
77fn count_candidates(conn: &Connection, review_status: Option<&str>) -> Result<i64> {
78    match review_status {
79        Some(review_status) => conn
80            .query_row(
81                "SELECT COUNT(*) FROM user_context_candidates WHERE review_status = ?1",
82                [review_status],
83                |row| row.get(0),
84            )
85            .map_err(Into::into),
86        None => conn
87            .query_row("SELECT COUNT(*) FROM user_context_candidates", [], |row| {
88                row.get(0)
89            })
90            .map_err(Into::into),
91    }
92}
93
94#[cfg(test)]
95mod tests {
96    use rusqlite::Connection;
97
98    use super::*;
99
100    #[test]
101    fn user_context_stats_report_claims_candidates_and_block_reasons() -> Result<()> {
102        let conn = Connection::open_in_memory()?;
103        conn.execute_batch(
104            "CREATE TABLE user_context_claims (
105                id INTEGER PRIMARY KEY,
106                status TEXT NOT NULL
107            );
108            CREATE TABLE user_context_candidates (
109                id INTEGER PRIMARY KEY,
110                review_status TEXT NOT NULL,
111                auto_promote_block_reason TEXT
112            );
113            INSERT INTO user_context_claims (status) VALUES
114                ('active'),
115                ('active'),
116                ('suppressed'),
117                ('deleted'),
118                ('superseded');
119            INSERT INTO user_context_candidates (review_status, auto_promote_block_reason) VALUES
120                ('pending_review', 'source_not_user_authored'),
121                ('pending_review', 'source_not_user_authored'),
122                ('pending_review', 'low_confidence'),
123                ('auto_promoted', NULL),
124                ('approved', NULL);",
125        )?;
126
127        let stats = query_user_context_stats(&conn)?;
128
129        assert_eq!(
130            stats,
131            UserContextStats {
132                claims_total: 5,
133                claims_active: 2,
134                claims_suppressed: 1,
135                claims_deleted: 1,
136                candidates_total: 5,
137                candidates_pending_review: 3,
138                candidates_auto_promoted: 1,
139                candidate_block_reasons: vec![
140                    UserContextBlockReasonStat {
141                        reason: Some("source_not_user_authored".to_string()),
142                        pending: 2,
143                    },
144                    UserContextBlockReasonStat {
145                        reason: Some("low_confidence".to_string()),
146                        pending: 1,
147                    },
148                ],
149            }
150        );
151        Ok(())
152    }
153
154    #[test]
155    fn user_context_stats_default_when_tables_are_absent() -> Result<()> {
156        let conn = Connection::open_in_memory()?;
157
158        let stats = query_user_context_stats(&conn)?;
159
160        assert_eq!(stats, UserContextStats::default());
161        Ok(())
162    }
163}
remem/db/query/user_context_stats.rs

remem/db/query/
user_context_stats.rs
