Skip to main content

max / goingson

4.1 KB · 98 lines History Blame Raw
1 //! SQLite implementation of the StatsRepository.
2 //!
3 //! Provides aggregated statistics for the dashboard view including:
4 //! - Task counts (overdue, due today, due this week)
5 //! - Unread email counts
6 //! - Upcoming events
7 //! - High-urgency task list
8
9 use async_trait::async_trait;
10 use sqlx::SqlitePool;
11 use goingson_core::{CoreError, DashboardStats, HighUrgencyTask, Result, StatsRepository, UserId};
12
13 use crate::utils::parse_datetime;
14
15 /// SQLite-backed implementation of [`StatsRepository`].
16 ///
17 /// Computes dashboard statistics via optimized COUNT queries.
18 /// Returns aggregated metrics across tasks, emails, events, and projects.
19 pub struct SqliteStatsRepository { pool: SqlitePool }
20
21 impl SqliteStatsRepository {
22 /// Creates a new repository instance with the given connection pool.
23 #[tracing::instrument(skip_all)]
24 pub fn new(pool: SqlitePool) -> Self { Self { pool } }
25 }
26
27 #[async_trait]
28 impl StatsRepository for SqliteStatsRepository {
29 #[tracing::instrument(skip_all)]
30 async fn get_dashboard_stats(&self, user_id: UserId) -> Result<DashboardStats> {
31 let user_id_str = user_id.to_string();
32
33 // Batch all 6 scalar counts into a single query using subqueries.
34 #[derive(sqlx::FromRow)]
35 struct StatsRow {
36 tasks_due_today: i64,
37 tasks_due_this_week: i64,
38 overdue_count: i64,
39 unread_emails: i64,
40 upcoming_events: i64,
41 active_projects: i64,
42 }
43
44 let stats: StatsRow = sqlx::query_as(
45 "SELECT \
46 (SELECT COUNT(*) FROM tasks WHERE user_id = ?1 \
47 AND status NOT IN ('Completed', 'Deleted') \
48 AND due IS NOT NULL AND date(due) = date('now')) AS tasks_due_today, \
49 (SELECT COUNT(*) FROM tasks WHERE user_id = ?1 \
50 AND status NOT IN ('Completed', 'Deleted') \
51 AND due IS NOT NULL AND date(due) >= date('now') \
52 AND date(due) <= date('now', '+7 days')) AS tasks_due_this_week, \
53 (SELECT COUNT(*) FROM tasks WHERE user_id = ?1 \
54 AND status NOT IN ('Completed', 'Deleted') \
55 AND due IS NOT NULL AND datetime(due) < datetime('now')) AS overdue_count, \
56 (SELECT COUNT(*) FROM emails WHERE user_id = ?1 \
57 AND is_read = 0) AS unread_emails, \
58 (SELECT COUNT(*) FROM events WHERE user_id = ?1 \
59 AND datetime(start_time) >= datetime('now') \
60 AND datetime(start_time) <= datetime('now', '+7 days')) AS upcoming_events, \
61 (SELECT COUNT(*) FROM projects WHERE user_id = ?1 \
62 AND status = 'Active') AS active_projects")
63 .bind(&user_id_str)
64 .fetch_one(&self.pool).await.map_err(CoreError::database)?;
65
66 // High-urgency tasks returns rows, so it remains a separate query.
67 #[derive(sqlx::FromRow)]
68 struct HighUrgencyRow { id: String, description: String, urgency: f64, status: String, due: Option<String> }
69
70 let rows: Vec<HighUrgencyRow> = sqlx::query_as(
71 "SELECT id, description, urgency, status, due FROM tasks \
72 WHERE user_id = ? AND status NOT IN ('Completed', 'Deleted') \
73 ORDER BY urgency DESC LIMIT 5")
74 .bind(&user_id_str)
75 .fetch_all(&self.pool).await.map_err(CoreError::database)?;
76
77 let high_urgency_tasks = rows.into_iter().map(|row| {
78 HighUrgencyTask {
79 id: row.id,
80 description: row.description,
81 urgency: row.urgency,
82 status: row.status,
83 due: row.due.and_then(|d| parse_datetime(&d).ok()).map(|dt| dt.to_rfc3339()),
84 }
85 }).collect();
86
87 Ok(DashboardStats {
88 tasks_due_today: stats.tasks_due_today,
89 tasks_due_this_week: stats.tasks_due_this_week,
90 overdue_count: stats.overdue_count,
91 unread_emails: stats.unread_emails,
92 upcoming_events: stats.upcoming_events,
93 active_projects: stats.active_projects,
94 high_urgency_tasks,
95 })
96 }
97 }
98