1use crate::models::{
2 GitBranch, Goal, GoalStatus, InsightData, ProjectTemplate, TimeEstimate, Workspace,
3};
4use anyhow::Result;
5use chrono::NaiveDate;
6use rusqlite::{params, Connection, OptionalExtension};
7
8pub struct GoalQueries;
9
10impl GoalQueries {
11 pub fn create(conn: &Connection, goal: &Goal) -> Result<i64> {
12 let mut stmt = conn.prepare(
13 "INSERT INTO goals (project_id, name, description, target_hours, start_date, end_date, current_progress, status)
14 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)"
15 )?;
16
17 stmt.execute(params![
18 goal.project_id,
19 goal.name,
20 goal.description,
21 goal.target_hours,
22 goal.start_date,
23 goal.end_date,
24 goal.current_progress,
25 goal.status.to_string()
26 ])?;
27
28 Ok(conn.last_insert_rowid())
29 }
30
31 pub fn find_by_id(conn: &Connection, goal_id: i64) -> Result<Option<Goal>> {
32 let mut stmt = conn.prepare(
33 "SELECT id, project_id, name, description, target_hours, start_date, end_date, current_progress, status, created_at, updated_at
34 FROM goals WHERE id = ?1"
35 )?;
36
37 let goal = stmt
38 .query_row([goal_id], |row| {
39 Ok(Goal {
40 id: Some(row.get(0)?),
41 project_id: row.get(1)?,
42 name: row.get(2)?,
43 description: row.get(3)?,
44 target_hours: row.get(4)?,
45 start_date: row.get(5)?,
46 end_date: row.get(6)?,
47 current_progress: row.get(7)?,
48 status: row
49 .get::<_, String>(8)?
50 .parse()
51 .unwrap_or(GoalStatus::Active),
52 created_at: row.get(9)?,
53 updated_at: row.get(10)?,
54 })
55 })
56 .optional()?;
57
58 Ok(goal)
59 }
60
61 pub fn list_by_project(conn: &Connection, project_id: Option<i64>) -> Result<Vec<Goal>> {
62 let sql = if let Some(_pid) = project_id {
63 "SELECT id, project_id, name, description, target_hours, start_date, end_date, current_progress, status, created_at, updated_at
64 FROM goals WHERE project_id = ?1 ORDER BY created_at DESC"
65 } else {
66 "SELECT id, project_id, name, description, target_hours, start_date, end_date, current_progress, status, created_at, updated_at
67 FROM goals ORDER BY created_at DESC"
68 };
69
70 let mut stmt = conn.prepare(sql)?;
71 let goals = if let Some(pid) = project_id {
72 stmt.query_map([pid], |row| {
73 Ok(Goal {
74 id: Some(row.get(0)?),
75 project_id: row.get(1)?,
76 name: row.get(2)?,
77 description: row.get(3)?,
78 target_hours: row.get(4)?,
79 start_date: row.get(5)?,
80 end_date: row.get(6)?,
81 current_progress: row.get(7)?,
82 status: row
83 .get::<_, String>(8)?
84 .parse()
85 .unwrap_or(GoalStatus::Active),
86 created_at: row.get(9)?,
87 updated_at: row.get(10)?,
88 })
89 })?
90 .collect::<Result<Vec<_>, _>>()?
91 } else {
92 stmt.query_map([], |row| {
93 Ok(Goal {
94 id: Some(row.get(0)?),
95 project_id: row.get(1)?,
96 name: row.get(2)?,
97 description: row.get(3)?,
98 target_hours: row.get(4)?,
99 start_date: row.get(5)?,
100 end_date: row.get(6)?,
101 current_progress: row.get(7)?,
102 status: row
103 .get::<_, String>(8)?
104 .parse()
105 .unwrap_or(GoalStatus::Active),
106 created_at: row.get(9)?,
107 updated_at: row.get(10)?,
108 })
109 })?
110 .collect::<Result<Vec<_>, _>>()?
111 };
112
113 Ok(goals)
114 }
115
116 pub fn update_progress(conn: &Connection, goal_id: i64, hours: f64) -> Result<bool> {
117 let mut stmt = conn.prepare(
118 "UPDATE goals SET current_progress = current_progress + ?1, updated_at = CURRENT_TIMESTAMP
119 WHERE id = ?2"
120 )?;
121 let changes = stmt.execute(params![hours, goal_id])?;
122 Ok(changes > 0)
123 }
124}
125
126pub struct TemplateQueries;
127
128impl TemplateQueries {
129 pub fn create(conn: &Connection, template: &ProjectTemplate) -> Result<i64> {
130 let tags_json = serde_json::to_string(&template.default_tags)?;
131 let goals_json = serde_json::to_string(&template.default_goals)?;
132
133 let mut stmt = conn.prepare(
134 "INSERT INTO project_templates (name, description, default_tags, default_goals, workspace_path)
135 VALUES (?1, ?2, ?3, ?4, ?5)"
136 )?;
137
138 stmt.execute(params![
139 template.name,
140 template.description,
141 tags_json,
142 goals_json,
143 template
144 .workspace_path
145 .as_ref()
146 .map(|p| p.to_string_lossy().to_string())
147 ])?;
148
149 Ok(conn.last_insert_rowid())
150 }
151
152 pub fn list_all(conn: &Connection) -> Result<Vec<ProjectTemplate>> {
153 let mut stmt = conn.prepare(
154 "SELECT id, name, description, default_tags, default_goals, workspace_path, created_at
155 FROM project_templates ORDER BY name",
156 )?;
157
158 let templates = stmt
159 .query_map([], |row| {
160 let tags_json: String = row.get(3)?;
161 let goals_json: String = row.get(4)?;
162
163 Ok(ProjectTemplate {
164 id: Some(row.get(0)?),
165 name: row.get(1)?,
166 description: row.get(2)?,
167 default_tags: serde_json::from_str(&tags_json).unwrap_or_default(),
168 default_goals: serde_json::from_str(&goals_json).unwrap_or_default(),
169 workspace_path: row.get::<_, Option<String>>(5)?.map(|s| s.into()),
170 created_at: row.get(6)?,
171 })
172 })?
173 .collect::<Result<Vec<_>, _>>()?;
174
175 Ok(templates)
176 }
177}
178
179pub struct WorkspaceQueries;
180
181impl WorkspaceQueries {
182 pub fn create(conn: &Connection, workspace: &Workspace) -> Result<i64> {
183 let mut stmt = conn.prepare(
184 "INSERT INTO workspaces (name, description, path)
185 VALUES (?1, ?2, ?3)",
186 )?;
187
188 stmt.execute(params![
189 workspace.name,
190 workspace.description,
191 workspace
192 .path
193 .as_ref()
194 .map(|p| p.to_string_lossy().to_string())
195 ])?;
196
197 Ok(conn.last_insert_rowid())
198 }
199
200 pub fn list_all(conn: &Connection) -> Result<Vec<Workspace>> {
201 let mut stmt = conn.prepare(
202 "SELECT id, name, description, path, created_at, updated_at
203 FROM workspaces ORDER BY name",
204 )?;
205
206 let workspaces = stmt
207 .query_map([], |row| {
208 Ok(Workspace {
209 id: Some(row.get(0)?),
210 name: row.get(1)?,
211 description: row.get(2)?,
212 path: row.get::<_, Option<String>>(3)?.map(|s| s.into()),
213 created_at: row.get(4)?,
214 updated_at: row.get(5)?,
215 })
216 })?
217 .collect::<Result<Vec<_>, _>>()?;
218
219 Ok(workspaces)
220 }
221
222 pub fn find_by_name(conn: &Connection, name: &str) -> Result<Option<Workspace>> {
223 let mut stmt = conn.prepare(
224 "SELECT id, name, description, path, created_at, updated_at
225 FROM workspaces WHERE name = ?1",
226 )?;
227
228 let workspace = stmt
229 .query_row([name], |row| {
230 Ok(Workspace {
231 id: Some(row.get(0)?),
232 name: row.get(1)?,
233 description: row.get(2)?,
234 path: row.get::<_, Option<String>>(3)?.map(|s| s.into()),
235 created_at: row.get(4)?,
236 updated_at: row.get(5)?,
237 })
238 })
239 .optional()?;
240
241 Ok(workspace)
242 }
243
244 pub fn delete(conn: &Connection, workspace_id: i64) -> Result<bool> {
245 let mut stmt = conn.prepare("DELETE FROM workspaces WHERE id = ?1")?;
246 let changes = stmt.execute([workspace_id])?;
247 Ok(changes > 0)
248 }
249
250 pub fn add_project(conn: &Connection, workspace_id: i64, project_id: i64) -> Result<bool> {
251 let mut stmt = conn.prepare(
252 "INSERT OR IGNORE INTO workspace_projects (workspace_id, project_id)
253 VALUES (?1, ?2)",
254 )?;
255 let changes = stmt.execute(params![workspace_id, project_id])?;
256 Ok(changes > 0)
257 }
258
259 pub fn remove_project(conn: &Connection, workspace_id: i64, project_id: i64) -> Result<bool> {
260 let mut stmt = conn.prepare(
261 "DELETE FROM workspace_projects
262 WHERE workspace_id = ?1 AND project_id = ?2",
263 )?;
264 let changes = stmt.execute(params![workspace_id, project_id])?;
265 Ok(changes > 0)
266 }
267
268 pub fn list_projects(
269 conn: &Connection,
270 workspace_id: i64,
271 ) -> Result<Vec<crate::models::Project>> {
272 let mut stmt = conn.prepare(
273 "SELECT p.id, p.name, p.path, p.git_hash, p.created_at, p.updated_at, p.is_archived, p.description
274 FROM projects p
275 JOIN workspace_projects wp ON p.id = wp.project_id
276 WHERE wp.workspace_id = ?1
277 ORDER BY p.name"
278 )?;
279
280 let projects = stmt
281 .query_map([workspace_id], |row| {
282 Ok(crate::models::Project {
283 id: Some(row.get(0)?),
284 name: row.get(1)?,
285 path: row.get::<_, String>(2)?.into(),
286 git_hash: row.get(3)?,
287 created_at: row.get(4)?,
288 updated_at: row.get(5)?,
289 is_archived: row.get(6)?,
290 description: row.get(7)?,
291 })
292 })?
293 .collect::<Result<Vec<_>, _>>()?;
294
295 Ok(projects)
296 }
297}
298
299pub struct GitBranchQueries;
300
301impl GitBranchQueries {
302 pub fn create_or_update(conn: &Connection, branch: &GitBranch) -> Result<i64> {
303 let existing =
305 Self::find_by_project_and_name(conn, branch.project_id, &branch.branch_name)?;
306
307 if let Some(mut existing) = existing {
308 existing.update_time(branch.total_time_seconds);
310 let mut stmt = conn.prepare(
311 "UPDATE git_branches SET last_seen = CURRENT_TIMESTAMP, total_time_seconds = total_time_seconds + ?1
312 WHERE project_id = ?2 AND branch_name = ?3"
313 )?;
314 stmt.execute(params![
315 branch.total_time_seconds,
316 branch.project_id,
317 branch.branch_name
318 ])?;
319 existing
320 .id
321 .ok_or_else(|| anyhow::anyhow!("Git branch ID missing after update"))
322 } else {
323 let mut stmt = conn.prepare(
325 "INSERT INTO git_branches (project_id, branch_name, total_time_seconds)
326 VALUES (?1, ?2, ?3)",
327 )?;
328 stmt.execute(params![
329 branch.project_id,
330 branch.branch_name,
331 branch.total_time_seconds
332 ])?;
333 Ok(conn.last_insert_rowid())
334 }
335 }
336
337 pub fn find_by_project_and_name(
338 conn: &Connection,
339 project_id: i64,
340 branch_name: &str,
341 ) -> Result<Option<GitBranch>> {
342 let mut stmt = conn.prepare(
343 "SELECT id, project_id, branch_name, first_seen, last_seen, total_time_seconds
344 FROM git_branches WHERE project_id = ?1 AND branch_name = ?2",
345 )?;
346
347 let branch = stmt
348 .query_row(params![project_id, branch_name], |row| {
349 Ok(GitBranch {
350 id: Some(row.get(0)?),
351 project_id: row.get(1)?,
352 branch_name: row.get(2)?,
353 first_seen: row.get(3)?,
354 last_seen: row.get(4)?,
355 total_time_seconds: row.get(5)?,
356 })
357 })
358 .optional()?;
359
360 Ok(branch)
361 }
362
363 pub fn list_by_project(conn: &Connection, project_id: i64) -> Result<Vec<GitBranch>> {
364 let mut stmt = conn.prepare(
365 "SELECT id, project_id, branch_name, first_seen, last_seen, total_time_seconds
366 FROM git_branches WHERE project_id = ?1 ORDER BY total_time_seconds DESC",
367 )?;
368
369 let branches = stmt
370 .query_map([project_id], |row| {
371 Ok(GitBranch {
372 id: Some(row.get(0)?),
373 project_id: row.get(1)?,
374 branch_name: row.get(2)?,
375 first_seen: row.get(3)?,
376 last_seen: row.get(4)?,
377 total_time_seconds: row.get(5)?,
378 })
379 })?
380 .collect::<Result<Vec<_>, _>>()?;
381
382 Ok(branches)
383 }
384}
385
386pub struct TimeEstimateQueries;
387
388impl TimeEstimateQueries {
389 pub fn create(conn: &Connection, estimate: &TimeEstimate) -> Result<i64> {
390 let mut stmt = conn.prepare(
391 "INSERT INTO time_estimates (project_id, task_name, estimated_hours, actual_hours, status, due_date, completed_at)
392 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)"
393 )?;
394
395 stmt.execute(params![
396 estimate.project_id,
397 estimate.task_name,
398 estimate.estimated_hours,
399 estimate.actual_hours,
400 estimate.status.to_string(),
401 estimate.due_date,
402 estimate.completed_at
403 ])?;
404
405 Ok(conn.last_insert_rowid())
406 }
407
408 pub fn list_by_project(conn: &Connection, project_id: i64) -> Result<Vec<TimeEstimate>> {
409 let mut stmt = conn.prepare(
410 "SELECT id, project_id, task_name, estimated_hours, actual_hours, status, due_date, completed_at, created_at, updated_at
411 FROM time_estimates WHERE project_id = ?1 ORDER BY created_at DESC"
412 )?;
413
414 let estimates = stmt
415 .query_map([project_id], |row| {
416 Ok(TimeEstimate {
417 id: Some(row.get(0)?),
418 project_id: row.get(1)?,
419 task_name: row.get(2)?,
420 estimated_hours: row.get(3)?,
421 actual_hours: row.get(4)?,
422 status: match row.get::<_, String>(5)?.as_str() {
423 "planned" => crate::models::EstimateStatus::Planned,
424 "in_progress" => crate::models::EstimateStatus::InProgress,
425 "completed" => crate::models::EstimateStatus::Completed,
426 "cancelled" => crate::models::EstimateStatus::Cancelled,
427 _ => crate::models::EstimateStatus::Planned,
428 },
429 due_date: row.get(6)?,
430 completed_at: row.get(7)?,
431 created_at: row.get(8)?,
432 updated_at: row.get(9)?,
433 })
434 })?
435 .collect::<Result<Vec<_>, _>>()?;
436
437 Ok(estimates)
438 }
439
440 pub fn record_actual(conn: &Connection, estimate_id: i64, hours: f64) -> Result<bool> {
441 let mut stmt = conn.prepare(
442 "UPDATE time_estimates SET actual_hours = ?1, status = 'completed', completed_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
443 WHERE id = ?2"
444 )?;
445 let changes = stmt.execute(params![hours, estimate_id])?;
446 Ok(changes > 0)
447 }
448}
449
450pub struct InsightQueries;
451
452impl InsightQueries {
453 pub fn calculate_weekly_summary(
454 conn: &Connection,
455 week_start: NaiveDate,
456 ) -> Result<InsightData> {
457 let week_end = week_start + chrono::Duration::days(6);
458
459 let mut stmt = conn.prepare(
460 "SELECT
461 COALESCE(SUM(CASE WHEN end_time IS NOT NULL THEN
462 (julianday(end_time) - julianday(start_time)) * 86400 - COALESCE(paused_duration, 0)
463 ELSE 0 END), 0) as total_seconds,
464 COUNT(*) as session_count
465 FROM sessions
466 WHERE DATE(start_time) >= ?1 AND DATE(start_time) <= ?2 AND end_time IS NOT NULL "
467 )?;
468
469 let (total_seconds, session_count): (i64, i64) =
470 stmt.query_row([week_start, week_end], |row| Ok((row.get(0)?, row.get(1)?)))?;
471
472 let total_hours = total_seconds as f64 / 3600.0;
473 let avg_session_duration = if session_count > 0 {
474 total_hours / session_count as f64
475 } else {
476 0.0
477 };
478
479 Ok(InsightData {
480 total_hours,
481 sessions_count: session_count,
482 avg_session_duration,
483 most_active_day: None,
484 most_active_time: None,
485 productivity_score: None,
486 project_breakdown: vec![],
487 trends: vec![],
488 })
489 }
490
491 pub fn calculate_monthly_summary(
492 conn: &Connection,
493 month_start: NaiveDate,
494 ) -> Result<InsightData> {
495 let month_end = month_start + chrono::Duration::days(30);
496
497 let mut stmt = conn.prepare(
498 "SELECT
499 COALESCE(SUM(CASE WHEN end_time IS NOT NULL THEN
500 (julianday(end_time) - julianday(start_time)) * 86400 - COALESCE(paused_duration, 0)
501 ELSE 0 END), 0) as total_seconds,
502 COUNT(*) as session_count
503 FROM sessions
504 WHERE DATE(start_time) >= ?1 AND DATE(start_time) <= ?2 AND end_time IS NOT NULL "
505 )?;
506
507 let (total_seconds, session_count): (i64, i64) = stmt
508 .query_row([month_start, month_end], |row| {
509 Ok((row.get(0)?, row.get(1)?))
510 })?;
511
512 let total_hours = total_seconds as f64 / 3600.0;
513 let avg_session_duration = if session_count > 0 {
514 total_hours / session_count as f64
515 } else {
516 0.0
517 };
518
519 Ok(InsightData {
520 total_hours,
521 sessions_count: session_count,
522 avg_session_duration,
523 most_active_day: None,
524 most_active_time: None,
525 productivity_score: None,
526 project_breakdown: vec![],
527 trends: vec![],
528 })
529 }
530}