Skip to main content

max / goingson

11.5 KB · 385 lines History Blame Raw
1 //! SQLite implementation of time session operations.
2 //!
3 //! Manages start/stop/discard timer sessions and aggregation queries.
4 //! At most one session per user can be active (ended_at IS NULL) at any time.
5
6 use std::collections::HashMap;
7 use chrono::{DateTime, Utc};
8 use sqlx::SqlitePool;
9
10 use goingson_core::{
11 CoreError, Result, TaskId, TimeSession, TimeSessionId, TimeTrackingSummary, UserId,
12 };
13
14 use crate::utils::{format_datetime, format_datetime_now, parse_datetime, parse_uuid};
15
16 /// Row struct for time session queries.
17 #[derive(Debug, sqlx::FromRow)]
18 struct TimeSessionRow {
19 id: String,
20 task_id: String,
21 user_id: String,
22 started_at: String,
23 ended_at: Option<String>,
24 duration_minutes: Option<i32>,
25 created_at: String,
26 }
27
28 impl TimeSessionRow {
29 fn into_session(self) -> Result<TimeSession> {
30 Ok(TimeSession {
31 id: parse_uuid(&self.id)?.into(),
32 task_id: parse_uuid(&self.task_id)?.into(),
33 user_id: parse_uuid(&self.user_id)?.into(),
34 started_at: parse_datetime(&self.started_at)?,
35 ended_at: self.ended_at.as_ref().map(|s| parse_datetime(s)).transpose()?,
36 duration_minutes: self.duration_minutes,
37 created_at: parse_datetime(&self.created_at)?,
38 })
39 }
40 }
41
42 /// Batch-fetch active sessions for a set of tasks.
43 /// Returns a map from TaskId to the active TimeSession (if any).
44 pub(crate) async fn get_active_sessions_for_tasks(
45 pool: &SqlitePool,
46 task_ids: &[String],
47 ) -> Result<HashMap<TaskId, TimeSession>> {
48 if task_ids.is_empty() {
49 return Ok(HashMap::new());
50 }
51
52 let placeholders = task_ids.iter().map(|_| "?").collect::<Vec<_>>().join(",");
53 let sql = format!(
54 "SELECT id, task_id, user_id, started_at, ended_at, duration_minutes, created_at
55 FROM time_sessions WHERE task_id IN ({}) AND ended_at IS NULL",
56 placeholders
57 );
58
59 let mut query = sqlx::query_as::<_, TimeSessionRow>(&sql);
60 for id in task_ids {
61 query = query.bind(id);
62 }
63
64 let rows = query.fetch_all(pool).await.map_err(CoreError::database)?;
65
66 let mut map = HashMap::new();
67 for row in rows {
68 let task_id: TaskId = parse_uuid(&row.task_id)?.into();
69 map.insert(task_id, row.into_session()?);
70 }
71
72 Ok(map)
73 }
74
75 /// Start a timer on a task. Fails if any session is already active for the user.
76 /// Uses a transaction to prevent double-start from concurrent requests.
77 pub(crate) async fn start_timer(
78 pool: &SqlitePool,
79 task_id: TaskId,
80 user_id: UserId,
81 ) -> Result<TimeSession> {
82 let mut tx = pool.begin().await.map_err(CoreError::database)?;
83
84 // Check for existing active session (inside transaction to prevent race)
85 let existing: Option<(String,)> = sqlx::query_as(
86 "SELECT id FROM time_sessions WHERE user_id = ? AND ended_at IS NULL LIMIT 1"
87 )
88 .bind(user_id.to_string())
89 .fetch_optional(&mut *tx)
90 .await
91 .map_err(CoreError::database)?;
92
93 if existing.is_some() {
94 return Err(CoreError::validation(
95 "timer",
96 "A timer is already running. Stop or discard it first.",
97 ));
98 }
99
100 let id = TimeSessionId::new();
101 let now = format_datetime_now();
102
103 sqlx::query(
104 "INSERT INTO time_sessions (id, task_id, user_id, started_at, created_at) VALUES (?, ?, ?, ?, ?)"
105 )
106 .bind(id.to_string())
107 .bind(task_id.to_string())
108 .bind(user_id.to_string())
109 .bind(&now)
110 .bind(&now)
111 .execute(&mut *tx)
112 .await
113 .map_err(CoreError::database)?;
114
115 let row = sqlx::query_as::<_, TimeSessionRow>(
116 "SELECT id, task_id, user_id, started_at, ended_at, duration_minutes, created_at FROM time_sessions WHERE id = ?"
117 )
118 .bind(id.to_string())
119 .fetch_one(&mut *tx)
120 .await
121 .map_err(CoreError::database)?;
122
123 tx.commit().await.map_err(CoreError::database)?;
124 row.into_session()
125 }
126
127 /// Stop the active timer on a task. Updates duration_minutes and the task's actual_minutes cache.
128 /// Uses a transaction so session end and task actual_minutes are updated atomically.
129 pub(crate) async fn stop_timer(
130 pool: &SqlitePool,
131 task_id: TaskId,
132 user_id: UserId,
133 ) -> Result<Option<TimeSession>> {
134 // Find active session for this task
135 let row = sqlx::query_as::<_, TimeSessionRow>(
136 "SELECT id, task_id, user_id, started_at, ended_at, duration_minutes, created_at
137 FROM time_sessions WHERE task_id = ? AND user_id = ? AND ended_at IS NULL"
138 )
139 .bind(task_id.to_string())
140 .bind(user_id.to_string())
141 .fetch_optional(pool)
142 .await
143 .map_err(CoreError::database)?;
144
145 let row = match row {
146 Some(r) => r,
147 None => return Ok(None),
148 };
149
150 let started_at = parse_datetime(&row.started_at)?;
151 let now = Utc::now();
152 let duration = (now - started_at).num_minutes().max(0) as i32;
153 let now_str = format_datetime(&now);
154
155 let mut tx = pool.begin().await.map_err(CoreError::database)?;
156
157 // Update the session
158 sqlx::query(
159 "UPDATE time_sessions SET ended_at = ?, duration_minutes = ? WHERE id = ?"
160 )
161 .bind(&now_str)
162 .bind(duration)
163 .bind(&row.id)
164 .execute(&mut *tx)
165 .await
166 .map_err(CoreError::database)?;
167
168 // Update the task's actual_minutes cache
169 sqlx::query(
170 "UPDATE tasks SET actual_minutes = actual_minutes + ? WHERE id = ?"
171 )
172 .bind(duration)
173 .bind(task_id.to_string())
174 .execute(&mut *tx)
175 .await
176 .map_err(CoreError::database)?;
177
178 tx.commit().await.map_err(CoreError::database)?;
179
180 // Fetch updated session
181 let updated = sqlx::query_as::<_, TimeSessionRow>(
182 "SELECT id, task_id, user_id, started_at, ended_at, duration_minutes, created_at FROM time_sessions WHERE id = ?"
183 )
184 .bind(&row.id)
185 .fetch_one(pool)
186 .await
187 .map_err(CoreError::database)?;
188
189 Ok(Some(updated.into_session()?))
190 }
191
192 /// Discard the active timer without updating actual_minutes.
193 pub(crate) async fn discard_timer(
194 pool: &SqlitePool,
195 task_id: TaskId,
196 user_id: UserId,
197 ) -> Result<bool> {
198 let result = sqlx::query(
199 "DELETE FROM time_sessions WHERE task_id = ? AND user_id = ? AND ended_at IS NULL"
200 )
201 .bind(task_id.to_string())
202 .bind(user_id.to_string())
203 .execute(pool)
204 .await
205 .map_err(CoreError::database)?;
206
207 Ok(result.rows_affected() > 0)
208 }
209
210 /// Get the currently active timer for a user with the task description.
211 pub(crate) async fn get_active_timer(
212 pool: &SqlitePool,
213 user_id: UserId,
214 ) -> Result<Option<(TimeSession, String)>> {
215 #[derive(sqlx::FromRow)]
216 #[allow(dead_code)]
217 struct ActiveTimerRow {
218 id: String,
219 task_id: String,
220 user_id: String,
221 started_at: String,
222 ended_at: Option<String>,
223 duration_minutes: Option<i32>,
224 created_at: String,
225 task_description: String,
226 }
227
228 let row = sqlx::query_as::<_, ActiveTimerRow>(
229 "SELECT ts.id, ts.task_id, ts.user_id, ts.started_at, ts.ended_at, ts.duration_minutes, ts.created_at,
230 t.description as task_description
231 FROM time_sessions ts
232 JOIN tasks t ON t.id = ts.task_id
233 WHERE ts.user_id = ? AND ts.ended_at IS NULL
234 LIMIT 1"
235 )
236 .bind(user_id.to_string())
237 .fetch_optional(pool)
238 .await
239 .map_err(CoreError::database)?;
240
241 match row {
242 Some(r) => {
243 let session = TimeSession {
244 id: parse_uuid(&r.id)?.into(),
245 task_id: parse_uuid(&r.task_id)?.into(),
246 user_id: parse_uuid(&r.user_id)?.into(),
247 started_at: parse_datetime(&r.started_at)?,
248 ended_at: None,
249 duration_minutes: r.duration_minutes,
250 created_at: parse_datetime(&r.created_at)?,
251 };
252 Ok(Some((session, r.task_description)))
253 }
254 None => Ok(None),
255 }
256 }
257
258 /// List all time sessions for a task.
259 pub(crate) async fn list_time_sessions(
260 pool: &SqlitePool,
261 task_id: TaskId,
262 user_id: UserId,
263 ) -> Result<Vec<TimeSession>> {
264 let rows = sqlx::query_as::<_, TimeSessionRow>(
265 "SELECT id, task_id, user_id, started_at, ended_at, duration_minutes, created_at
266 FROM time_sessions WHERE task_id = ? AND user_id = ?
267 ORDER BY started_at DESC"
268 )
269 .bind(task_id.to_string())
270 .bind(user_id.to_string())
271 .fetch_all(pool)
272 .await
273 .map_err(CoreError::database)?;
274
275 rows.into_iter().map(|r| r.into_session()).collect()
276 }
277
278 /// Get aggregated time tracking summary grouped by project and date.
279 pub(crate) async fn get_time_summary(
280 pool: &SqlitePool,
281 user_id: UserId,
282 start: DateTime<Utc>,
283 end: DateTime<Utc>,
284 ) -> Result<Vec<TimeTrackingSummary>> {
285 #[derive(sqlx::FromRow)]
286 struct SummaryRow {
287 project_id: Option<String>,
288 project_name: Option<String>,
289 date: String,
290 total_minutes: i32,
291 session_count: i32,
292 }
293
294 let start_str = format_datetime(&start);
295 let end_str = format_datetime(&end);
296
297 let rows = sqlx::query_as::<_, SummaryRow>(
298 "SELECT t.project_id, p.name as project_name,
299 date(ts.started_at) as date,
300 CAST(COALESCE(SUM(ts.duration_minutes), 0) AS INTEGER) as total_minutes,
301 CAST(COUNT(*) AS INTEGER) as session_count
302 FROM time_sessions ts
303 JOIN tasks t ON t.id = ts.task_id
304 LEFT JOIN projects p ON p.id = t.project_id
305 WHERE ts.user_id = ? AND ts.ended_at IS NOT NULL
306 AND ts.started_at >= ? AND ts.started_at < ?
307 GROUP BY t.project_id, date(ts.started_at)
308 ORDER BY date DESC, total_minutes DESC"
309 )
310 .bind(user_id.to_string())
311 .bind(&start_str)
312 .bind(&end_str)
313 .fetch_all(pool)
314 .await
315 .map_err(CoreError::database)?;
316
317 rows.into_iter().map(|r| {
318 Ok(TimeTrackingSummary {
319 project_id: r.project_id.as_ref().map(|s| parse_uuid(s)).transpose()?.map(Into::into),
320 project_name: r.project_name,
321 date: r.date,
322 total_minutes: r.total_minutes,
323 session_count: r.session_count,
324 })
325 }).collect()
326 }
327
328 /// Log a manual time entry (completed session, no live timer).
329 pub(crate) async fn log_manual_time(
330 pool: &SqlitePool,
331 task_id: TaskId,
332 user_id: UserId,
333 minutes: i32,
334 date: DateTime<Utc>,
335 ) -> Result<TimeSession> {
336 use chrono::Duration;
337
338 let id = TimeSessionId::new();
339 let started_at = date;
340 let ended_at = date + Duration::minutes(minutes as i64);
341 let now = Utc::now();
342
343 let started_str = format_datetime(&started_at);
344 let ended_str = format_datetime(&ended_at);
345 let created_str = format_datetime(&now);
346
347 sqlx::query(
348 "INSERT INTO time_sessions (id, task_id, user_id, started_at, ended_at, duration_minutes, created_at)
349 VALUES (?, ?, ?, ?, ?, ?, ?)"
350 )
351 .bind(id.to_string())
352 .bind(task_id.to_string())
353 .bind(user_id.to_string())
354 .bind(&started_str)
355 .bind(&ended_str)
356 .bind(minutes)
357 .bind(&created_str)
358 .execute(pool)
359 .await
360 .map_err(CoreError::database)?;
361
362 // Update task's cached actual_minutes
363 sqlx::query(
364 "UPDATE tasks SET actual_minutes = COALESCE(actual_minutes, 0) + ?, updated_at = ?
365 WHERE id = ? AND user_id = ?"
366 )
367 .bind(minutes)
368 .bind(&created_str)
369 .bind(task_id.to_string())
370 .bind(user_id.to_string())
371 .execute(pool)
372 .await
373 .map_err(CoreError::database)?;
374
375 Ok(TimeSession {
376 id,
377 task_id,
378 user_id,
379 started_at,
380 ended_at: Some(ended_at),
381 duration_minutes: Some(minutes),
382 created_at: now,
383 })
384 }
385