Skip to main content

max / goingson

4.9 KB · 117 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 chrono::{DateTime, Duration, Utc};
11 use sqlx::SqlitePool;
12 use goingson_core::{CoreError, DashboardStats, HighUrgencyTask, Result, StatsRepository, UserId};
13
14 use crate::utils::{format_datetime, parse_datetime};
15
16 /// SQLite-backed implementation of [`StatsRepository`].
17 ///
18 /// Computes dashboard statistics via optimized COUNT queries.
19 /// Returns aggregated metrics across tasks, emails, events, and projects.
20 pub struct SqliteStatsRepository { pool: SqlitePool }
21
22 impl SqliteStatsRepository {
23 /// Creates a new repository instance with the given connection pool.
24 #[tracing::instrument(skip_all)]
25 pub fn new(pool: SqlitePool) -> Self { Self { pool } }
26 }
27
28 #[async_trait]
29 impl StatsRepository for SqliteStatsRepository {
30 #[tracing::instrument(skip_all)]
31 async fn get_dashboard_stats(
32 &self,
33 user_id: UserId,
34 now: DateTime<Utc>,
35 today_start: DateTime<Utc>,
36 tomorrow_start: DateTime<Utc>,
37 week_end: DateTime<Utc>,
38 ) -> Result<DashboardStats> {
39 let user_id_str = user_id.to_string();
40 let now_str = format_datetime(&now);
41 let today_start_str = format_datetime(&today_start);
42 let tomorrow_start_str = format_datetime(&tomorrow_start);
43 let week_end_str = format_datetime(&week_end);
44 let events_end_str = format_datetime(&(now + Duration::days(7)));
45
46 // Batch all 6 scalar counts into a single query using subqueries.
47 // Day windows are bound UTC instants of the user's local day; all
48 // predicates compare the bare indexed column (no date()/datetime()
49 // wrapper) so they stay sargable on idx_tasks_due / start_time.
50 #[derive(sqlx::FromRow)]
51 struct StatsRow {
52 tasks_due_today: i64,
53 tasks_due_this_week: i64,
54 overdue_count: i64,
55 unread_emails: i64,
56 upcoming_events: i64,
57 active_projects: i64,
58 }
59
60 let stats: StatsRow = sqlx::query_as(
61 "SELECT \
62 (SELECT COUNT(*) FROM tasks WHERE user_id = ?1 \
63 AND status NOT IN ('Completed', 'Deleted') \
64 AND due IS NOT NULL AND due >= ?3 AND due < ?4) AS tasks_due_today, \
65 (SELECT COUNT(*) FROM tasks WHERE user_id = ?1 \
66 AND status NOT IN ('Completed', 'Deleted') \
67 AND due IS NOT NULL AND due >= ?3 AND due < ?5) AS tasks_due_this_week, \
68 (SELECT COUNT(*) FROM tasks WHERE user_id = ?1 \
69 AND status NOT IN ('Completed', 'Deleted') \
70 AND due IS NOT NULL AND due < ?2) AS overdue_count, \
71 (SELECT COUNT(*) FROM emails WHERE user_id = ?1 \
72 AND is_read = 0) AS unread_emails, \
73 (SELECT COUNT(*) FROM events WHERE user_id = ?1 \
74 AND start_time >= ?2 AND start_time <= ?6) AS upcoming_events, \
75 (SELECT COUNT(*) FROM projects WHERE user_id = ?1 \
76 AND status = 'Active') AS active_projects")
77 .bind(&user_id_str) // ?1
78 .bind(&now_str) // ?2
79 .bind(&today_start_str) // ?3
80 .bind(&tomorrow_start_str) // ?4
81 .bind(&week_end_str) // ?5
82 .bind(&events_end_str) // ?6
83 .fetch_one(&self.pool).await.map_err(CoreError::database)?;
84
85 // High-urgency tasks returns rows, so it remains a separate query.
86 #[derive(sqlx::FromRow)]
87 struct HighUrgencyRow { id: String, description: String, urgency: f64, status: String, due: Option<String> }
88
89 let rows: Vec<HighUrgencyRow> = sqlx::query_as(
90 "SELECT id, description, urgency, status, due FROM tasks \
91 WHERE user_id = ? AND status NOT IN ('Completed', 'Deleted') \
92 ORDER BY urgency DESC LIMIT 5")
93 .bind(&user_id_str)
94 .fetch_all(&self.pool).await.map_err(CoreError::database)?;
95
96 let high_urgency_tasks = rows.into_iter().map(|row| {
97 HighUrgencyTask {
98 id: row.id,
99 description: row.description,
100 urgency: row.urgency,
101 status: row.status,
102 due: row.due.and_then(|d| parse_datetime(&d).ok()).map(|dt| dt.to_rfc3339()),
103 }
104 }).collect();
105
106 Ok(DashboardStats {
107 tasks_due_today: stats.tasks_due_today,
108 tasks_due_this_week: stats.tasks_due_this_week,
109 overdue_count: stats.overdue_count,
110 unread_emails: stats.unread_emails,
111 upcoming_events: stats.upcoming_events,
112 active_projects: stats.active_projects,
113 high_urgency_tasks,
114 })
115 }
116 }
117