Skip to main content

max / goingson

7.8 KB · 258 lines History Blame Raw
1 //! Task read queries: the plain listings and the dynamic filter builder.
2
3 use goingson_core::{
4 ContactId, CoreError, DbValue, ProjectId, Result, SortDirection, Task, TaskFilterQuery, TaskId,
5 TaskSortColumn, UserId,
6 };
7 use rusqlite::{Connection, params_from_iter};
8
9 use crate::utils::query_all;
10
11 use super::fetch::{query_tasks, rows_to_tasks};
12 use super::row::{
13 TASK_SELECT_COLUMNS, TaskRowWithProject, sort_column_nulls_last, sort_column_sql,
14 };
15
16 pub(super) fn list_all(conn: &Connection, user_id: UserId) -> Result<Vec<Task>> {
17 let sql = format!(
18 r"
19 SELECT {TASK_SELECT_COLUMNS}
20 FROM tasks t
21 LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ?
22 LEFT JOIN contacts ct ON ct.id = t.contact_id
23 WHERE t.user_id = ? AND t.status != 'Deleted'
24 ORDER BY t.urgency DESC, t.created_at DESC
25 "
26 );
27 query_tasks(conn, &sql, &[user_id.to_string(), user_id.to_string()])
28 }
29
30 pub(super) fn list_all_for_backup(conn: &Connection, user_id: UserId) -> Result<Vec<Task>> {
31 let sql = format!(
32 r"
33 SELECT {TASK_SELECT_COLUMNS}
34 FROM tasks t
35 LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ?
36 LEFT JOIN contacts ct ON ct.id = t.contact_id
37 WHERE t.user_id = ?
38 ORDER BY t.urgency DESC, t.created_at DESC
39 "
40 );
41 query_tasks(conn, &sql, &[user_id.to_string(), user_id.to_string()])
42 }
43
44 pub(super) fn list_by_project(
45 conn: &Connection,
46 user_id: UserId,
47 project_id: ProjectId,
48 ) -> Result<Vec<Task>> {
49 let sql = format!(
50 r"
51 SELECT {TASK_SELECT_COLUMNS}
52 FROM tasks t
53 LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ?
54 LEFT JOIN contacts ct ON ct.id = t.contact_id
55 WHERE t.user_id = ? AND t.project_id = ? AND t.status != 'Deleted'
56 ORDER BY t.urgency DESC, t.created_at DESC
57 "
58 );
59 query_tasks(
60 conn,
61 &sql,
62 &[
63 user_id.to_string(),
64 user_id.to_string(),
65 project_id.to_string(),
66 ],
67 )
68 }
69
70 pub(super) fn list_by_contact(
71 conn: &Connection,
72 user_id: UserId,
73 contact_id: ContactId,
74 ) -> Result<Vec<Task>> {
75 let sql = format!(
76 r"
77 SELECT {TASK_SELECT_COLUMNS}
78 FROM tasks t
79 LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ?
80 LEFT JOIN contacts ct ON ct.id = t.contact_id
81 WHERE t.user_id = ? AND t.contact_id = ? AND t.status != 'Deleted'
82 ORDER BY t.created_at DESC
83 "
84 );
85 query_tasks(
86 conn,
87 &sql,
88 &[
89 user_id.to_string(),
90 user_id.to_string(),
91 contact_id.to_string(),
92 ],
93 )
94 }
95
96 pub(super) fn list_filtered(
97 conn: &Connection,
98 user_id: UserId,
99 query: &TaskFilterQuery,
100 ) -> Result<(Vec<Task>, i64)> {
101 // Build dynamic WHERE clause
102 let mut conditions = vec![
103 "t.user_id = ?".to_string(),
104 "t.status != 'Deleted'".to_string(),
105 ];
106 let mut bind_values: Vec<String> = vec![user_id.to_string()];
107
108 // Status filter
109 if let Some(ref status) = query.status {
110 conditions.push("t.status = ?".to_string());
111 bind_values.push(status.db_value().to_string());
112 }
113
114 // Project filter
115 if let Some(ref project_id) = query.project_id {
116 conditions.push("t.project_id = ?".to_string());
117 bind_values.push(project_id.to_string());
118 }
119
120 // Priority filter
121 if let Some(ref priority) = query.priority {
122 conditions.push("t.priority = ?".to_string());
123 bind_values.push(priority.db_value().to_string());
124 }
125
126 // Milestone filter
127 if let Some(ref milestone_id) = query.milestone_id {
128 conditions.push("t.milestone_id = ?".to_string());
129 bind_values.push(milestone_id.to_string());
130 }
131
132 // Snoozed filter - hide snoozed tasks unless explicitly requested
133 if !query.show_snoozed {
134 conditions.push(
135 "(t.snoozed_until IS NULL OR datetime(t.snoozed_until) <= datetime('now'))".to_string(),
136 );
137 }
138
139 // Waiting only filter
140 if query.waiting_only {
141 conditions.push("t.waiting_for_response = 1".to_string());
142 }
143
144 let where_clause = conditions.join(" AND ");
145
146 // Get total count for pagination
147 let count_sql = format!("SELECT COUNT(*) FROM tasks t WHERE {where_clause}");
148 let total: i64 = conn
149 .query_row(&count_sql, params_from_iter(&bind_values), |row| row.get(0))
150 .map_err(CoreError::database)?;
151
152 if total == 0 {
153 return Ok((vec![], 0));
154 }
155
156 // Build paginated query with parameterized LIMIT/OFFSET.
157 //
158 // Defense-in-depth: clamp before binding. A negative LIMIT means
159 // "unbounded" in SQLite and an absurd LIMIT/negative OFFSET could blow up
160 // the result set. The UI already paginates; this is a backstop against a
161 // bad caller, not the primary guard.
162 const MAX_PAGE_LIMIT: i64 = 1000;
163 let mut pagination_binds: Vec<i64> = Vec::new();
164 let limit = query.limit.map(|l| l.clamp(0, MAX_PAGE_LIMIT));
165 let offset = query.offset.map(|o| o.max(0));
166 let pagination = match (limit, offset) {
167 (Some(limit), Some(offset)) => {
168 pagination_binds.push(limit);
169 pagination_binds.push(offset);
170 " LIMIT ? OFFSET ?".to_string()
171 }
172 (Some(limit), None) => {
173 pagination_binds.push(limit);
174 " LIMIT ?".to_string()
175 }
176 _ => String::new(),
177 };
178
179 // Build dynamic ORDER BY clause
180 let sort_column = query.sort_column.unwrap_or(TaskSortColumn::Urgency);
181 let sort_direction = query.sort_direction.unwrap_or_else(|| {
182 // Default to DESC for urgency (highest first), ASC for others
183 if sort_column == TaskSortColumn::Urgency {
184 SortDirection::Desc
185 } else {
186 SortDirection::Asc
187 }
188 });
189
190 let order_by = if sort_column_nulls_last(sort_column) {
191 // For nullable columns (project, due), put NULLs last regardless of sort direction
192 format!(
193 "{} {} NULLS LAST, t.created_at DESC",
194 sort_column_sql(sort_column),
195 sort_direction.sql()
196 )
197 } else {
198 format!(
199 "{} {}, t.created_at DESC",
200 sort_column_sql(sort_column),
201 sort_direction.sql()
202 )
203 };
204
205 let sql = format!(
206 r"
207 SELECT {TASK_SELECT_COLUMNS}
208 FROM tasks t
209 LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ?
210 LEFT JOIN contacts ct ON ct.id = t.contact_id
211 WHERE {where_clause}
212 ORDER BY {order_by}{pagination}
213 "
214 );
215
216 // Bind, in the order the placeholders appear: user_id for the JOIN, then
217 // every WHERE clause value, then LIMIT/OFFSET.
218 let mut binds: Vec<rusqlite::types::Value> =
219 Vec::with_capacity(bind_values.len() + pagination_binds.len() + 1);
220 binds.push(user_id.to_string().into());
221 binds.extend(bind_values.into_iter().map(rusqlite::types::Value::from));
222 binds.extend(
223 pagination_binds
224 .into_iter()
225 .map(rusqlite::types::Value::from),
226 );
227
228 let rows = query_all(
229 conn,
230 &sql,
231 params_from_iter(binds),
232 TaskRowWithProject::from_row,
233 )?;
234
235 let tasks = rows_to_tasks(conn, rows)?;
236 Ok((tasks, total))
237 }
238
239 pub(super) fn list_recurrence_chain(
240 conn: &Connection,
241 root_id: TaskId,
242 user_id: UserId,
243 ) -> Result<Vec<Task>> {
244 let sql = format!(
245 "SELECT {TASK_SELECT_COLUMNS} 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.recurrence_parent_id = ? OR t.id = ?) AND t.user_id = ? ORDER BY t.created_at DESC"
246 );
247 query_tasks(
248 conn,
249 &sql,
250 &[
251 user_id.to_string(),
252 root_id.to_string(),
253 root_id.to_string(),
254 user_id.to_string(),
255 ],
256 )
257 }
258