Skip to main content

max / goingson

14.0 KB · 390 lines History Blame Raw
1 //! Task state-change and query methods delegated from SqliteTaskRepository.
2 //!
3 //! Covers snoozing, waiting-for-response, scheduling, focus mode, and
4 //! date-range reporting queries. Each function takes `&SqlitePool` directly,
5 //! following the same pattern as `annotation_repo` and `subtask_repo`.
6
7 use chrono::{DateTime, NaiveDate, Utc};
8 use sqlx::SqlitePool;
9
10 use goingson_core::{CoreError, Result, Task, TaskId, TaskStatus, UserId};
11
12 use crate::utils::{format_datetime, format_datetime_now, format_datetime_opt};
13
14 use super::task_repo::{get_task_by_id, query_tasks, TASK_SELECT_COLUMNS};
15
16 // ---- Snooze ----
17
18 /// Snooze a task until the given time. Completed/deleted tasks cannot be snoozed.
19 pub(crate) async fn snooze(
20 pool: &SqlitePool,
21 id: TaskId,
22 user_id: UserId,
23 until: DateTime<Utc>,
24 ) -> Result<Option<Task>> {
25 let until_str = format_datetime(&until);
26
27 // Atomically update only if task is not completed/deleted
28 let result = sqlx::query(
29 "UPDATE tasks SET snoozed_until = ? WHERE id = ? AND user_id = ? AND status NOT IN ('Completed', 'Deleted')"
30 )
31 .bind(&until_str)
32 .bind(id.to_string())
33 .bind(user_id.to_string())
34 .execute(pool)
35 .await
36 .map_err(CoreError::database)?;
37
38 if result.rows_affected() > 0 {
39 get_task_by_id(pool, id, user_id).await
40 } else {
41 // Distinguish "not found" from "wrong status"
42 if let Some(task) = get_task_by_id(pool, id, user_id).await? {
43 if task.status == TaskStatus::Completed {
44 return Err(CoreError::validation("status", "cannot snooze a completed task"));
45 }
46 if task.status == TaskStatus::Deleted {
47 return Err(CoreError::validation("status", "cannot snooze a deleted task"));
48 }
49 }
50 Ok(None)
51 }
52 }
53
54 /// Remove the snooze from a task.
55 pub(crate) async fn unsnooze(
56 pool: &SqlitePool,
57 id: TaskId,
58 user_id: UserId,
59 ) -> Result<Option<Task>> {
60 let result = sqlx::query(
61 "UPDATE tasks SET snoozed_until = NULL WHERE id = ? AND user_id = ?"
62 )
63 .bind(id.to_string())
64 .bind(user_id.to_string())
65 .execute(pool)
66 .await
67 .map_err(CoreError::database)?;
68
69 if result.rows_affected() > 0 {
70 get_task_by_id(pool, id, user_id).await
71 } else {
72 Ok(None)
73 }
74 }
75
76 /// List all currently snoozed tasks.
77 pub(crate) async fn list_snoozed(
78 pool: &SqlitePool,
79 user_id: UserId,
80 ) -> Result<Vec<Task>> {
81 let sql = format!(
82 "SELECT {} FROM tasks t LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ? LEFT JOIN contacts ct ON ct.id = t.contact_id WHERE t.user_id = ? AND t.status != 'Deleted' AND t.snoozed_until IS NOT NULL AND t.snoozed_until > datetime('now') ORDER BY t.snoozed_until ASC",
83 TASK_SELECT_COLUMNS
84 );
85 query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string()]).await
86 }
87
88 // ---- Waiting ----
89
90 /// Mark a task as waiting for response.
91 pub(crate) async fn mark_waiting(
92 pool: &SqlitePool,
93 id: TaskId,
94 user_id: UserId,
95 expected_response: Option<DateTime<Utc>>,
96 ) -> Result<Option<Task>> {
97 let now = format_datetime_now();
98 let expected = format_datetime_opt(expected_response);
99
100 let result = sqlx::query(
101 "UPDATE tasks SET waiting_for_response = 1, waiting_since = ?, expected_response_date = ? WHERE id = ? AND user_id = ?"
102 )
103 .bind(&now)
104 .bind(&expected)
105 .bind(id.to_string())
106 .bind(user_id.to_string())
107 .execute(pool)
108 .await
109 .map_err(CoreError::database)?;
110
111 if result.rows_affected() > 0 {
112 get_task_by_id(pool, id, user_id).await
113 } else {
114 Ok(None)
115 }
116 }
117
118 /// Clear the waiting-for-response state on a task.
119 pub(crate) async fn clear_waiting(
120 pool: &SqlitePool,
121 id: TaskId,
122 user_id: UserId,
123 ) -> Result<Option<Task>> {
124 let result = sqlx::query(
125 "UPDATE tasks SET waiting_for_response = 0, waiting_since = NULL, expected_response_date = NULL WHERE id = ? AND user_id = ?"
126 )
127 .bind(id.to_string())
128 .bind(user_id.to_string())
129 .execute(pool)
130 .await
131 .map_err(CoreError::database)?;
132
133 if result.rows_affected() > 0 {
134 get_task_by_id(pool, id, user_id).await
135 } else {
136 Ok(None)
137 }
138 }
139
140 /// List all tasks currently waiting for a response.
141 pub(crate) async fn list_waiting(
142 pool: &SqlitePool,
143 user_id: UserId,
144 ) -> Result<Vec<Task>> {
145 let sql = format!(
146 "SELECT {} FROM tasks t LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ? LEFT JOIN contacts ct ON ct.id = t.contact_id WHERE t.user_id = ? AND t.status != 'Deleted' AND t.waiting_for_response = 1 ORDER BY t.expected_response_date ASC NULLS LAST, t.waiting_since ASC",
147 TASK_SELECT_COLUMNS
148 );
149 query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string()]).await
150 }
151
152 // ---- Scheduling ----
153
154 /// List tasks scheduled for a specific date.
155 pub(crate) async fn list_scheduled_for_date(
156 pool: &SqlitePool,
157 user_id: UserId,
158 date: NaiveDate,
159 ) -> Result<Vec<Task>> {
160 let date_start = format!("{} 00:00:00", date);
161 let date_end = format!("{} 23:59:59", date);
162
163 let sql = format!(
164 "SELECT {} FROM tasks t LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ? LEFT JOIN contacts ct ON ct.id = t.contact_id WHERE t.user_id = ? AND t.status != 'Deleted' AND t.status != 'Completed' AND t.scheduled_start IS NOT NULL AND t.scheduled_start >= ? AND t.scheduled_start <= ? ORDER BY t.scheduled_start ASC",
165 TASK_SELECT_COLUMNS
166 );
167 query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string(), date_start, date_end]).await
168 }
169
170 /// List unscheduled tasks due on a specific date.
171 pub(crate) async fn list_unscheduled_due_on_date(
172 pool: &SqlitePool,
173 user_id: UserId,
174 date: NaiveDate,
175 ) -> Result<Vec<Task>> {
176 let date_start = format!("{} 00:00:00", date);
177 let date_end = format!("{} 23:59:59", date);
178
179 let sql = format!(
180 "SELECT {} FROM tasks t LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ? LEFT JOIN contacts ct ON ct.id = t.contact_id WHERE t.user_id = ? AND t.status != 'Deleted' AND t.status != 'Completed' AND t.scheduled_start IS NULL AND t.due IS NOT NULL AND t.due >= ? AND t.due <= ? ORDER BY t.urgency DESC, t.due ASC",
181 TASK_SELECT_COLUMNS
182 );
183 query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string(), date_start, date_end]).await
184 }
185
186 /// List unscheduled tasks whose `due` falls within an explicit UTC instant
187 /// window (inclusive upper bound). The caller maps a user-local civil day to
188 /// this range so the boundary respects the user's timezone, not UTC midnight.
189 pub(crate) async fn list_unscheduled_due_between(
190 pool: &SqlitePool,
191 user_id: UserId,
192 start: DateTime<Utc>,
193 end: DateTime<Utc>,
194 ) -> Result<Vec<Task>> {
195 let start_str = format_datetime(&start);
196 let end_str = format_datetime(&end);
197
198 let sql = format!(
199 "SELECT {} FROM tasks t LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ? LEFT JOIN contacts ct ON ct.id = t.contact_id WHERE t.user_id = ? AND t.status != 'Deleted' AND t.status != 'Completed' AND t.scheduled_start IS NULL AND t.due IS NOT NULL AND t.due >= ? AND t.due <= ? ORDER BY t.urgency DESC, t.due ASC",
200 TASK_SELECT_COLUMNS
201 );
202 query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string(), start_str, end_str]).await
203 }
204
205 /// Update the scheduled start time and duration for a task.
206 pub(crate) async fn update_schedule(
207 pool: &SqlitePool,
208 id: TaskId,
209 user_id: UserId,
210 start: Option<DateTime<Utc>>,
211 duration: Option<i32>,
212 ) -> Result<Option<Task>> {
213 let start_str = format_datetime_opt(start);
214
215 let result = sqlx::query(
216 "UPDATE tasks SET scheduled_start = ?, scheduled_duration = ? WHERE id = ? AND user_id = ?"
217 )
218 .bind(&start_str)
219 .bind(duration)
220 .bind(id.to_string())
221 .bind(user_id.to_string())
222 .execute(pool)
223 .await
224 .map_err(CoreError::database)?;
225
226 if result.rows_affected() > 0 {
227 get_task_by_id(pool, id, user_id).await
228 } else {
229 Ok(None)
230 }
231 }
232
233 // ---- Focus ----
234
235 /// Set or clear focus on a task.
236 pub(crate) async fn set_focus(
237 pool: &SqlitePool,
238 id: TaskId,
239 user_id: UserId,
240 is_focus: bool,
241 ) -> Result<Option<Task>> {
242 let focus_set_at = if is_focus {
243 Some(format_datetime(&Utc::now()))
244 } else {
245 None
246 };
247
248 let result = sqlx::query(
249 "UPDATE tasks SET is_focus = ?, focus_set_at = ? WHERE id = ? AND user_id = ?"
250 )
251 .bind(is_focus as i32)
252 .bind(&focus_set_at)
253 .bind(id.to_string())
254 .bind(user_id.to_string())
255 .execute(pool)
256 .await
257 .map_err(CoreError::database)?;
258
259 if result.rows_affected() > 0 {
260 get_task_by_id(pool, id, user_id).await
261 } else {
262 Ok(None)
263 }
264 }
265
266 /// List all focused tasks.
267 pub(crate) async fn list_focused(
268 pool: &SqlitePool,
269 user_id: UserId,
270 ) -> Result<Vec<Task>> {
271 let sql = format!(
272 "SELECT {} FROM tasks t LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ? LEFT JOIN contacts ct ON ct.id = t.contact_id WHERE t.user_id = ? AND t.status NOT IN ('Completed', 'Deleted') AND t.is_focus = 1 ORDER BY t.focus_set_at DESC",
273 TASK_SELECT_COLUMNS
274 );
275 query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string()]).await
276 }
277
278 /// Clear focus from all tasks for a user.
279 pub(crate) async fn clear_all_focus(
280 pool: &SqlitePool,
281 user_id: UserId,
282 ) -> Result<u64> {
283 let result = sqlx::query(
284 "UPDATE tasks SET is_focus = 0, focus_set_at = NULL WHERE user_id = ? AND is_focus = 1"
285 )
286 .bind(user_id.to_string())
287 .execute(pool)
288 .await
289 .map_err(CoreError::database)?;
290
291 Ok(result.rows_affected())
292 }
293
294 // ---- Reporting ----
295
296 /// List tasks completed within a date range.
297 pub(crate) async fn list_completed_between(
298 pool: &SqlitePool,
299 user_id: UserId,
300 start: DateTime<Utc>,
301 end: DateTime<Utc>,
302 ) -> Result<Vec<Task>> {
303 let start_str = format_datetime(&start);
304 let end_str = format_datetime(&end);
305
306 let sql = format!(
307 "SELECT {} FROM tasks t LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ? LEFT JOIN contacts ct ON ct.id = t.contact_id WHERE t.user_id = ? AND t.status = 'Completed' AND t.completed_at IS NOT NULL AND t.completed_at >= ? AND t.completed_at <= ? ORDER BY t.completed_at DESC",
308 TASK_SELECT_COLUMNS
309 );
310 query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string(), start_str, end_str]).await
311 }
312
313 /// List tasks created within a date range (for monthly review stats).
314 pub(crate) async fn list_created_between(
315 pool: &SqlitePool,
316 user_id: UserId,
317 start: DateTime<Utc>,
318 end: DateTime<Utc>,
319 ) -> Result<Vec<Task>> {
320 let start_str = format_datetime(&start);
321 let end_str = format_datetime(&end);
322
323 let sql = format!(
324 "SELECT {} FROM tasks t LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ? LEFT JOIN contacts ct ON ct.id = t.contact_id WHERE t.user_id = ? AND t.status != 'Deleted' AND t.created_at >= ? AND t.created_at <= ? ORDER BY t.created_at DESC",
325 TASK_SELECT_COLUMNS
326 );
327 query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string(), start_str, end_str]).await
328 }
329
330 /// List tasks that became overdue within a date range.
331 pub(crate) async fn list_became_overdue_between(
332 pool: &SqlitePool,
333 user_id: UserId,
334 start: DateTime<Utc>,
335 end: DateTime<Utc>,
336 ) -> Result<Vec<Task>> {
337 let start_str = format_datetime(&start);
338 let end_str = format_datetime(&end);
339
340 // Tasks whose due date is in the given range and are still pending/started (overdue)
341 let sql = format!(
342 "SELECT {} FROM tasks t LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ? LEFT JOIN contacts ct ON ct.id = t.contact_id WHERE t.user_id = ? AND t.status NOT IN ('Completed', 'Deleted') AND t.due IS NOT NULL AND t.due >= ? AND t.due <= ? ORDER BY t.due ASC",
343 TASK_SELECT_COLUMNS
344 );
345 query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string(), start_str, end_str]).await
346 }
347
348 /// List tasks due within a date range.
349 pub(crate) async fn list_due_between(
350 pool: &SqlitePool,
351 user_id: UserId,
352 start: DateTime<Utc>,
353 end: DateTime<Utc>,
354 ) -> Result<Vec<Task>> {
355 let start_str = format_datetime(&start);
356 let end_str = format_datetime(&end);
357
358 let sql = format!(
359 "SELECT {} FROM tasks t LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ? LEFT JOIN contacts ct ON ct.id = t.contact_id WHERE t.user_id = ? AND t.status NOT IN ('Completed', 'Deleted') AND t.due IS NOT NULL AND t.due >= ? AND t.due <= ? ORDER BY t.due ASC, t.urgency DESC",
360 TASK_SELECT_COLUMNS
361 );
362 query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string(), start_str, end_str]).await
363 }
364
365 /// List tasks available for focus (high priority, not snoozed, not waiting, not focused).
366 pub(crate) async fn list_available_for_focus(
367 pool: &SqlitePool,
368 user_id: UserId,
369 limit: i64,
370 ) -> Result<Vec<Task>> {
371 // Clamp the caller-supplied limit: a negative value would otherwise mean
372 // "unbounded" to SQLite and a huge value an unbounded allocation.
373 let limit = limit.clamp(0, 1000);
374 let sql = format!(
375 "SELECT {} FROM tasks t LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ? LEFT JOIN contacts ct ON ct.id = t.contact_id WHERE t.user_id = ? AND t.status NOT IN ('Completed', 'Deleted') AND t.is_focus = 0 AND (t.snoozed_until IS NULL OR t.snoozed_until <= datetime('now')) AND t.waiting_for_response = 0 ORDER BY t.urgency DESC, t.priority DESC, t.due ASC NULLS LAST LIMIT ?",
376 TASK_SELECT_COLUMNS
377 );
378
379 // This query has an extra i64 bind, so we handle it directly
380 let rows = sqlx::query_as::<_, super::task_repo::TaskRowWithProject>(&sql)
381 .bind(user_id.to_string())
382 .bind(user_id.to_string())
383 .bind(limit)
384 .fetch_all(pool)
385 .await
386 .map_err(CoreError::database)?;
387
388 super::task_repo::rows_to_tasks(pool, rows).await
389 }
390