tempo_cli/db/
advanced_queries.rs

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    pub fn update(
126        conn: &Connection,
127        goal_id: i64,
128        name: Option<String>,
129        description: Option<String>,
130        target_hours: Option<f64>,
131        start_date: Option<Option<NaiveDate>>,
132        end_date: Option<Option<NaiveDate>>,
133        status: Option<GoalStatus>,
134    ) -> Result<bool> {
135        let mut updates = Vec::new();
136        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
137
138        if let Some(n) = name {
139            updates.push("name = ?");
140            params.push(Box::new(n));
141        }
142        if let Some(d) = description {
143            updates.push("description = ?");
144            params.push(Box::new(d));
145        }
146        if let Some(th) = target_hours {
147            updates.push("target_hours = ?");
148            params.push(Box::new(th));
149        }
150        if let Some(sd) = start_date {
151            updates.push("start_date = ?");
152            params.push(Box::new(sd));
153        }
154        if let Some(ed) = end_date {
155            updates.push("end_date = ?");
156            params.push(Box::new(ed));
157        }
158        if let Some(s) = status {
159            updates.push("status = ?");
160            params.push(Box::new(s.to_string()));
161        }
162
163        if updates.is_empty() {
164            return Ok(false);
165        }
166
167        updates.push("updated_at = CURRENT_TIMESTAMP");
168        params.push(Box::new(goal_id));
169
170        let sql = format!("UPDATE goals SET {} WHERE id = ?", updates.join(", "));
171        let mut stmt = conn.prepare(&sql)?;
172        let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();
173        let changes = stmt.execute(&param_refs[..])?;
174
175        Ok(changes > 0)
176    }
177
178    pub fn delete(conn: &Connection, goal_id: i64) -> Result<bool> {
179        let mut stmt = conn.prepare("DELETE FROM goals WHERE id = ?1")?;
180        let changes = stmt.execute([goal_id])?;
181        Ok(changes > 0)
182    }
183
184    pub fn list_active(conn: &Connection) -> Result<Vec<Goal>> {
185        let mut stmt = conn.prepare(
186            "SELECT id, project_id, name, description, target_hours, start_date, end_date, current_progress, status, created_at, updated_at
187             FROM goals WHERE status = 'active' ORDER BY created_at DESC"
188        )?;
189
190        let goals = stmt
191            .query_map([], |row| {
192                Ok(Goal {
193                    id: Some(row.get(0)?),
194                    project_id: row.get(1)?,
195                    name: row.get(2)?,
196                    description: row.get(3)?,
197                    target_hours: row.get(4)?,
198                    start_date: row.get(5)?,
199                    end_date: row.get(6)?,
200                    current_progress: row.get(7)?,
201                    status: row
202                        .get::<_, String>(8)?
203                        .parse()
204                        .unwrap_or(GoalStatus::Active),
205                    created_at: row.get(9)?,
206                    updated_at: row.get(10)?,
207                })
208            })?
209            .collect::<Result<Vec<_>, _>>()?;
210
211        Ok(goals)
212    }
213
214    pub fn set_status(conn: &Connection, goal_id: i64, status: GoalStatus) -> Result<bool> {
215        let mut stmt = conn.prepare(
216            "UPDATE goals SET status = ?1, updated_at = CURRENT_TIMESTAMP WHERE id = ?2"
217        )?;
218        let changes = stmt.execute(params![status.to_string(), goal_id])?;
219        Ok(changes > 0)
220    }
221}
222
223pub struct TemplateQueries;
224
225impl TemplateQueries {
226    pub fn create(conn: &Connection, template: &ProjectTemplate) -> Result<i64> {
227        let tags_json = serde_json::to_string(&template.default_tags)?;
228        let goals_json = serde_json::to_string(&template.default_goals)?;
229
230        let mut stmt = conn.prepare(
231            "INSERT INTO project_templates (name, description, default_tags, default_goals, workspace_path)
232             VALUES (?1, ?2, ?3, ?4, ?5)"
233        )?;
234
235        stmt.execute(params![
236            template.name,
237            template.description,
238            tags_json,
239            goals_json,
240            template
241                .workspace_path
242                .as_ref()
243                .map(|p| p.to_string_lossy().to_string())
244        ])?;
245
246        Ok(conn.last_insert_rowid())
247    }
248
249    pub fn list_all(conn: &Connection) -> Result<Vec<ProjectTemplate>> {
250        let mut stmt = conn.prepare(
251            "SELECT id, name, description, default_tags, default_goals, workspace_path, created_at
252             FROM project_templates ORDER BY name",
253        )?;
254
255        let templates = stmt
256            .query_map([], |row| {
257                let tags_json: String = row.get(3)?;
258                let goals_json: String = row.get(4)?;
259
260                Ok(ProjectTemplate {
261                    id: Some(row.get(0)?),
262                    name: row.get(1)?,
263                    description: row.get(2)?,
264                    default_tags: serde_json::from_str(&tags_json).unwrap_or_default(),
265                    default_goals: serde_json::from_str(&goals_json).unwrap_or_default(),
266                    workspace_path: row.get::<_, Option<String>>(5)?.map(|s| s.into()),
267                    created_at: row.get(6)?,
268                })
269            })?
270            .collect::<Result<Vec<_>, _>>()?;
271
272        Ok(templates)
273    }
274
275    pub fn find_by_id(conn: &Connection, template_id: i64) -> Result<Option<ProjectTemplate>> {
276        let mut stmt = conn.prepare(
277            "SELECT id, name, description, default_tags, default_goals, workspace_path, created_at
278             FROM project_templates WHERE id = ?1",
279        )?;
280
281        let template = stmt
282            .query_row([template_id], |row| {
283                let tags_json: String = row.get(3)?;
284                let goals_json: String = row.get(4)?;
285
286                Ok(ProjectTemplate {
287                    id: Some(row.get(0)?),
288                    name: row.get(1)?,
289                    description: row.get(2)?,
290                    default_tags: serde_json::from_str(&tags_json).unwrap_or_default(),
291                    default_goals: serde_json::from_str(&goals_json).unwrap_or_default(),
292                    workspace_path: row.get::<_, Option<String>>(5)?.map(|s| s.into()),
293                    created_at: row.get(6)?,
294                })
295            })
296            .optional()?;
297
298        Ok(template)
299    }
300
301    pub fn find_by_name(conn: &Connection, name: &str) -> Result<Option<ProjectTemplate>> {
302        let mut stmt = conn.prepare(
303            "SELECT id, name, description, default_tags, default_goals, workspace_path, created_at
304             FROM project_templates WHERE name = ?1",
305        )?;
306
307        let template = stmt
308            .query_row([name], |row| {
309                let tags_json: String = row.get(3)?;
310                let goals_json: String = row.get(4)?;
311
312                Ok(ProjectTemplate {
313                    id: Some(row.get(0)?),
314                    name: row.get(1)?,
315                    description: row.get(2)?,
316                    default_tags: serde_json::from_str(&tags_json).unwrap_or_default(),
317                    default_goals: serde_json::from_str(&goals_json).unwrap_or_default(),
318                    workspace_path: row.get::<_, Option<String>>(5)?.map(|s| s.into()),
319                    created_at: row.get(6)?,
320                })
321            })
322            .optional()?;
323
324        Ok(template)
325    }
326
327    pub fn update(
328        conn: &Connection,
329        template_id: i64,
330        name: Option<String>,
331        description: Option<String>,
332        default_tags: Option<Vec<String>>,
333        default_goals: Option<Vec<String>>,
334        workspace_path: Option<Option<std::path::PathBuf>>,
335    ) -> Result<bool> {
336        let mut updates = Vec::new();
337        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
338
339        if let Some(n) = name {
340            updates.push("name = ?");
341            params.push(Box::new(n));
342        }
343        if let Some(d) = description {
344            updates.push("description = ?");
345            params.push(Box::new(d));
346        }
347        if let Some(tags) = default_tags {
348            updates.push("default_tags = ?");
349            let tags_json = serde_json::to_string(&tags).unwrap_or_default();
350            params.push(Box::new(tags_json));
351        }
352        if let Some(goals) = default_goals {
353            updates.push("default_goals = ?");
354            let goals_json = serde_json::to_string(&goals).unwrap_or_default();
355            params.push(Box::new(goals_json));
356        }
357        if let Some(path) = workspace_path {
358            updates.push("workspace_path = ?");
359            params.push(Box::new(path.map(|p| p.to_string_lossy().to_string())));
360        }
361
362        if updates.is_empty() {
363            return Ok(false);
364        }
365
366        params.push(Box::new(template_id));
367
368        let sql = format!("UPDATE project_templates SET {} WHERE id = ?", updates.join(", "));
369        let mut stmt = conn.prepare(&sql)?;
370        let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();
371        let changes = stmt.execute(&param_refs[..])?;
372
373        Ok(changes > 0)
374    }
375
376    pub fn delete(conn: &Connection, template_id: i64) -> Result<bool> {
377        let mut stmt = conn.prepare("DELETE FROM project_templates WHERE id = ?1")?;
378        let changes = stmt.execute([template_id])?;
379        Ok(changes > 0)
380    }
381
382    pub fn delete_by_name(conn: &Connection, name: &str) -> Result<bool> {
383        let mut stmt = conn.prepare("DELETE FROM project_templates WHERE name = ?1")?;
384        let changes = stmt.execute([name])?;
385        Ok(changes > 0)
386    }
387}
388
389pub struct WorkspaceQueries;
390
391impl WorkspaceQueries {
392    pub fn create(conn: &Connection, workspace: &Workspace) -> Result<i64> {
393        let mut stmt = conn.prepare(
394            "INSERT INTO workspaces (name, description, path)
395             VALUES (?1, ?2, ?3)",
396        )?;
397
398        stmt.execute(params![
399            workspace.name,
400            workspace.description,
401            workspace
402                .path
403                .as_ref()
404                .map(|p| p.to_string_lossy().to_string())
405        ])?;
406
407        Ok(conn.last_insert_rowid())
408    }
409
410    pub fn list_all(conn: &Connection) -> Result<Vec<Workspace>> {
411        let mut stmt = conn.prepare(
412            "SELECT id, name, description, path, created_at, updated_at
413             FROM workspaces ORDER BY name",
414        )?;
415
416        let workspaces = stmt
417            .query_map([], |row| {
418                Ok(Workspace {
419                    id: Some(row.get(0)?),
420                    name: row.get(1)?,
421                    description: row.get(2)?,
422                    path: row.get::<_, Option<String>>(3)?.map(|s| s.into()),
423                    created_at: row.get(4)?,
424                    updated_at: row.get(5)?,
425                })
426            })?
427            .collect::<Result<Vec<_>, _>>()?;
428
429        Ok(workspaces)
430    }
431
432    pub fn find_by_name(conn: &Connection, name: &str) -> Result<Option<Workspace>> {
433        let mut stmt = conn.prepare(
434            "SELECT id, name, description, path, created_at, updated_at
435             FROM workspaces WHERE name = ?1",
436        )?;
437
438        let workspace = stmt
439            .query_row([name], |row| {
440                Ok(Workspace {
441                    id: Some(row.get(0)?),
442                    name: row.get(1)?,
443                    description: row.get(2)?,
444                    path: row.get::<_, Option<String>>(3)?.map(|s| s.into()),
445                    created_at: row.get(4)?,
446                    updated_at: row.get(5)?,
447                })
448            })
449            .optional()?;
450
451        Ok(workspace)
452    }
453
454    pub fn delete(conn: &Connection, workspace_id: i64) -> Result<bool> {
455        let mut stmt = conn.prepare("DELETE FROM workspaces WHERE id = ?1")?;
456        let changes = stmt.execute([workspace_id])?;
457        Ok(changes > 0)
458    }
459
460    pub fn add_project(conn: &Connection, workspace_id: i64, project_id: i64) -> Result<bool> {
461        let mut stmt = conn.prepare(
462            "INSERT OR IGNORE INTO workspace_projects (workspace_id, project_id)
463             VALUES (?1, ?2)",
464        )?;
465        let changes = stmt.execute(params![workspace_id, project_id])?;
466        Ok(changes > 0)
467    }
468
469    pub fn remove_project(conn: &Connection, workspace_id: i64, project_id: i64) -> Result<bool> {
470        let mut stmt = conn.prepare(
471            "DELETE FROM workspace_projects 
472             WHERE workspace_id = ?1 AND project_id = ?2",
473        )?;
474        let changes = stmt.execute(params![workspace_id, project_id])?;
475        Ok(changes > 0)
476    }
477
478    pub fn list_projects(
479        conn: &Connection,
480        workspace_id: i64,
481    ) -> Result<Vec<crate::models::Project>> {
482        let mut stmt = conn.prepare(
483            "SELECT p.id, p.name, p.path, p.git_hash, p.created_at, p.updated_at, p.is_archived, p.description
484             FROM projects p 
485             JOIN workspace_projects wp ON p.id = wp.project_id
486             WHERE wp.workspace_id = ?1
487             ORDER BY p.name"
488        )?;
489
490        let projects = stmt
491            .query_map([workspace_id], |row| {
492                Ok(crate::models::Project {
493                    id: Some(row.get(0)?),
494                    name: row.get(1)?,
495                    path: row.get::<_, String>(2)?.into(),
496                    git_hash: row.get(3)?,
497                    created_at: row.get(4)?,
498                    updated_at: row.get(5)?,
499                    is_archived: row.get(6)?,
500                    description: row.get(7)?,
501                })
502            })?
503            .collect::<Result<Vec<_>, _>>()?;
504
505        Ok(projects)
506    }
507
508    pub fn find_by_id(conn: &Connection, workspace_id: i64) -> Result<Option<Workspace>> {
509        let mut stmt = conn.prepare(
510            "SELECT id, name, description, path, created_at, updated_at
511             FROM workspaces WHERE id = ?1",
512        )?;
513
514        let workspace = stmt
515            .query_row([workspace_id], |row| {
516                Ok(Workspace {
517                    id: Some(row.get(0)?),
518                    name: row.get(1)?,
519                    description: row.get(2)?,
520                    path: row.get::<_, Option<String>>(3)?.map(|s| s.into()),
521                    created_at: row.get(4)?,
522                    updated_at: row.get(5)?,
523                })
524            })
525            .optional()?;
526
527        Ok(workspace)
528    }
529
530    pub fn update(
531        conn: &Connection,
532        workspace_id: i64,
533        name: Option<String>,
534        description: Option<String>,
535        path: Option<Option<std::path::PathBuf>>,
536    ) -> Result<bool> {
537        let mut updates = Vec::new();
538        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
539
540        if let Some(n) = name {
541            updates.push("name = ?");
542            params.push(Box::new(n));
543        }
544        if let Some(d) = description {
545            updates.push("description = ?");
546            params.push(Box::new(d));
547        }
548        if let Some(p) = path {
549            updates.push("path = ?");
550            params.push(Box::new(p.map(|p| p.to_string_lossy().to_string())));
551        }
552
553        if updates.is_empty() {
554            return Ok(false);
555        }
556
557        updates.push("updated_at = CURRENT_TIMESTAMP");
558        params.push(Box::new(workspace_id));
559
560        let sql = format!("UPDATE workspaces SET {} WHERE id = ?", updates.join(", "));
561        let mut stmt = conn.prepare(&sql)?;
562        let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();
563        let changes = stmt.execute(&param_refs[..])?;
564
565        Ok(changes > 0)
566    }
567}
568
569pub struct GitBranchQueries;
570
571impl GitBranchQueries {
572    pub fn create_or_update(conn: &Connection, branch: &GitBranch) -> Result<i64> {
573        // Try to find existing branch
574        let existing =
575            Self::find_by_project_and_name(conn, branch.project_id, &branch.branch_name)?;
576
577        if let Some(mut existing) = existing {
578            // Update existing
579            existing.update_time(branch.total_time_seconds);
580            let mut stmt = conn.prepare(
581                "UPDATE git_branches SET last_seen = CURRENT_TIMESTAMP, total_time_seconds = total_time_seconds + ?1
582                 WHERE project_id = ?2 AND branch_name = ?3"
583            )?;
584            stmt.execute(params![
585                branch.total_time_seconds,
586                branch.project_id,
587                branch.branch_name
588            ])?;
589            existing
590                .id
591                .ok_or_else(|| anyhow::anyhow!("Git branch ID missing after update"))
592        } else {
593            // Create new
594            let mut stmt = conn.prepare(
595                "INSERT INTO git_branches (project_id, branch_name, total_time_seconds)
596                 VALUES (?1, ?2, ?3)",
597            )?;
598            stmt.execute(params![
599                branch.project_id,
600                branch.branch_name,
601                branch.total_time_seconds
602            ])?;
603            Ok(conn.last_insert_rowid())
604        }
605    }
606
607    pub fn find_by_project_and_name(
608        conn: &Connection,
609        project_id: i64,
610        branch_name: &str,
611    ) -> Result<Option<GitBranch>> {
612        let mut stmt = conn.prepare(
613            "SELECT id, project_id, branch_name, first_seen, last_seen, total_time_seconds
614             FROM git_branches WHERE project_id = ?1 AND branch_name = ?2",
615        )?;
616
617        let branch = stmt
618            .query_row(params![project_id, branch_name], |row| {
619                Ok(GitBranch {
620                    id: Some(row.get(0)?),
621                    project_id: row.get(1)?,
622                    branch_name: row.get(2)?,
623                    first_seen: row.get(3)?,
624                    last_seen: row.get(4)?,
625                    total_time_seconds: row.get(5)?,
626                })
627            })
628            .optional()?;
629
630        Ok(branch)
631    }
632
633    pub fn list_by_project(conn: &Connection, project_id: i64) -> Result<Vec<GitBranch>> {
634        let mut stmt = conn.prepare(
635            "SELECT id, project_id, branch_name, first_seen, last_seen, total_time_seconds 
636             FROM git_branches WHERE project_id = ?1 ORDER BY total_time_seconds DESC",
637        )?;
638
639        let branches = stmt
640            .query_map([project_id], |row| {
641                Ok(GitBranch {
642                    id: Some(row.get(0)?),
643                    project_id: row.get(1)?,
644                    branch_name: row.get(2)?,
645                    first_seen: row.get(3)?,
646                    last_seen: row.get(4)?,
647                    total_time_seconds: row.get(5)?,
648                })
649            })?
650            .collect::<Result<Vec<_>, _>>()?;
651
652        Ok(branches)
653    }
654
655    pub fn update(
656        conn: &Connection,
657        branch_id: i64,
658        branch_name: Option<String>,
659        additional_time_seconds: Option<i64>,
660    ) -> Result<bool> {
661        let mut updates = Vec::new();
662        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
663
664        if let Some(name) = branch_name {
665            updates.push("branch_name = ?");
666            params.push(Box::new(name));
667        }
668        if let Some(time) = additional_time_seconds {
669            updates.push("total_time_seconds = total_time_seconds + ?");
670            params.push(Box::new(time));
671        }
672
673        if updates.is_empty() {
674            return Ok(false);
675        }
676
677        updates.push("last_seen = CURRENT_TIMESTAMP");
678        params.push(Box::new(branch_id));
679
680        let sql = format!("UPDATE git_branches SET {} WHERE id = ?", updates.join(", "));
681        let mut stmt = conn.prepare(&sql)?;
682        let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();
683        let changes = stmt.execute(&param_refs[..])?;
684
685        Ok(changes > 0)
686    }
687
688    pub fn delete(conn: &Connection, branch_id: i64) -> Result<bool> {
689        let mut stmt = conn.prepare("DELETE FROM git_branches WHERE id = ?1")?;
690        let changes = stmt.execute([branch_id])?;
691        Ok(changes > 0)
692    }
693
694    pub fn delete_by_project_and_name(
695        conn: &Connection,
696        project_id: i64,
697        branch_name: &str,
698    ) -> Result<bool> {
699        let mut stmt = conn.prepare("DELETE FROM git_branches WHERE project_id = ?1 AND branch_name = ?2")?;
700        let changes = stmt.execute(params![project_id, branch_name])?;
701        Ok(changes > 0)
702    }
703
704    pub fn find_by_id(conn: &Connection, branch_id: i64) -> Result<Option<GitBranch>> {
705        let mut stmt = conn.prepare(
706            "SELECT id, project_id, branch_name, first_seen, last_seen, total_time_seconds
707             FROM git_branches WHERE id = ?1",
708        )?;
709
710        let branch = stmt
711            .query_row([branch_id], |row| {
712                Ok(GitBranch {
713                    id: Some(row.get(0)?),
714                    project_id: row.get(1)?,
715                    branch_name: row.get(2)?,
716                    first_seen: row.get(3)?,
717                    last_seen: row.get(4)?,
718                    total_time_seconds: row.get(5)?,
719                })
720            })
721            .optional()?;
722
723        Ok(branch)
724    }
725}
726
727pub struct TimeEstimateQueries;
728
729impl TimeEstimateQueries {
730    pub fn create(conn: &Connection, estimate: &TimeEstimate) -> Result<i64> {
731        let mut stmt = conn.prepare(
732            "INSERT INTO time_estimates (project_id, task_name, estimated_hours, actual_hours, status, due_date, completed_at)
733             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)"
734        )?;
735
736        stmt.execute(params![
737            estimate.project_id,
738            estimate.task_name,
739            estimate.estimated_hours,
740            estimate.actual_hours,
741            estimate.status.to_string(),
742            estimate.due_date,
743            estimate.completed_at
744        ])?;
745
746        Ok(conn.last_insert_rowid())
747    }
748
749    pub fn list_by_project(conn: &Connection, project_id: i64) -> Result<Vec<TimeEstimate>> {
750        let mut stmt = conn.prepare(
751            "SELECT id, project_id, task_name, estimated_hours, actual_hours, status, due_date, completed_at, created_at, updated_at 
752             FROM time_estimates WHERE project_id = ?1 ORDER BY created_at DESC"
753        )?;
754
755        let estimates = stmt
756            .query_map([project_id], |row| {
757                Ok(TimeEstimate {
758                    id: Some(row.get(0)?),
759                    project_id: row.get(1)?,
760                    task_name: row.get(2)?,
761                    estimated_hours: row.get(3)?,
762                    actual_hours: row.get(4)?,
763                    status: match row.get::<_, String>(5)?.as_str() {
764                        "planned" => crate::models::EstimateStatus::Planned,
765                        "in_progress" => crate::models::EstimateStatus::InProgress,
766                        "completed" => crate::models::EstimateStatus::Completed,
767                        "cancelled" => crate::models::EstimateStatus::Cancelled,
768                        _ => crate::models::EstimateStatus::Planned,
769                    },
770                    due_date: row.get(6)?,
771                    completed_at: row.get(7)?,
772                    created_at: row.get(8)?,
773                    updated_at: row.get(9)?,
774                })
775            })?
776            .collect::<Result<Vec<_>, _>>()?;
777
778        Ok(estimates)
779    }
780
781    pub fn record_actual(conn: &Connection, estimate_id: i64, hours: f64) -> Result<bool> {
782        let mut stmt = conn.prepare(
783            "UPDATE time_estimates SET actual_hours = ?1, status = 'completed', completed_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP 
784             WHERE id = ?2"
785        )?;
786        let changes = stmt.execute(params![hours, estimate_id])?;
787        Ok(changes > 0)
788    }
789
790    pub fn find_by_id(conn: &Connection, estimate_id: i64) -> Result<Option<TimeEstimate>> {
791        let mut stmt = conn.prepare(
792            "SELECT id, project_id, task_name, estimated_hours, actual_hours, status, due_date, completed_at, created_at, updated_at 
793             FROM time_estimates WHERE id = ?1"
794        )?;
795
796        let estimate = stmt
797            .query_row([estimate_id], |row| {
798                Ok(TimeEstimate {
799                    id: Some(row.get(0)?),
800                    project_id: row.get(1)?,
801                    task_name: row.get(2)?,
802                    estimated_hours: row.get(3)?,
803                    actual_hours: row.get(4)?,
804                    status: match row.get::<_, String>(5)?.as_str() {
805                        "planned" => crate::models::EstimateStatus::Planned,
806                        "in_progress" => crate::models::EstimateStatus::InProgress,
807                        "completed" => crate::models::EstimateStatus::Completed,
808                        "cancelled" => crate::models::EstimateStatus::Cancelled,
809                        _ => crate::models::EstimateStatus::Planned,
810                    },
811                    due_date: row.get(6)?,
812                    completed_at: row.get(7)?,
813                    created_at: row.get(8)?,
814                    updated_at: row.get(9)?,
815                })
816            })
817            .optional()?;
818
819        Ok(estimate)
820    }
821
822    pub fn update(
823        conn: &Connection,
824        estimate_id: i64,
825        task_name: Option<String>,
826        estimated_hours: Option<f64>,
827        actual_hours: Option<Option<f64>>,
828        status: Option<crate::models::EstimateStatus>,
829        due_date: Option<Option<chrono::NaiveDate>>,
830        completed_at: Option<Option<chrono::DateTime<chrono::Utc>>>,
831    ) -> Result<bool> {
832        let mut updates = Vec::new();
833        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
834
835        if let Some(name) = task_name {
836            updates.push("task_name = ?");
837            params.push(Box::new(name));
838        }
839        if let Some(est_hours) = estimated_hours {
840            updates.push("estimated_hours = ?");
841            params.push(Box::new(est_hours));
842        }
843        if let Some(act_hours) = actual_hours {
844            updates.push("actual_hours = ?");
845            params.push(Box::new(act_hours));
846        }
847        if let Some(stat) = status {
848            updates.push("status = ?");
849            params.push(Box::new(stat.to_string()));
850        }
851        if let Some(due) = due_date {
852            updates.push("due_date = ?");
853            params.push(Box::new(due));
854        }
855        if let Some(comp) = completed_at {
856            updates.push("completed_at = ?");
857            params.push(Box::new(comp));
858        }
859
860        if updates.is_empty() {
861            return Ok(false);
862        }
863
864        updates.push("updated_at = CURRENT_TIMESTAMP");
865        params.push(Box::new(estimate_id));
866
867        let sql = format!("UPDATE time_estimates SET {} WHERE id = ?", updates.join(", "));
868        let mut stmt = conn.prepare(&sql)?;
869        let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();
870        let changes = stmt.execute(&param_refs[..])?;
871
872        Ok(changes > 0)
873    }
874
875    pub fn delete(conn: &Connection, estimate_id: i64) -> Result<bool> {
876        let mut stmt = conn.prepare("DELETE FROM time_estimates WHERE id = ?1")?;
877        let changes = stmt.execute([estimate_id])?;
878        Ok(changes > 0)
879    }
880
881    pub fn set_status(
882        conn: &Connection,
883        estimate_id: i64,
884        status: crate::models::EstimateStatus,
885    ) -> Result<bool> {
886        let mut stmt = conn.prepare(
887            "UPDATE time_estimates SET status = ?1, updated_at = CURRENT_TIMESTAMP WHERE id = ?2"
888        )?;
889        let changes = stmt.execute(params![status.to_string(), estimate_id])?;
890        Ok(changes > 0)
891    }
892
893    pub fn list_all(conn: &Connection) -> Result<Vec<TimeEstimate>> {
894        let mut stmt = conn.prepare(
895            "SELECT id, project_id, task_name, estimated_hours, actual_hours, status, due_date, completed_at, created_at, updated_at 
896             FROM time_estimates ORDER BY created_at DESC"
897        )?;
898
899        let estimates = stmt
900            .query_map([], |row| {
901                Ok(TimeEstimate {
902                    id: Some(row.get(0)?),
903                    project_id: row.get(1)?,
904                    task_name: row.get(2)?,
905                    estimated_hours: row.get(3)?,
906                    actual_hours: row.get(4)?,
907                    status: match row.get::<_, String>(5)?.as_str() {
908                        "planned" => crate::models::EstimateStatus::Planned,
909                        "in_progress" => crate::models::EstimateStatus::InProgress,
910                        "completed" => crate::models::EstimateStatus::Completed,
911                        "cancelled" => crate::models::EstimateStatus::Cancelled,
912                        _ => crate::models::EstimateStatus::Planned,
913                    },
914                    due_date: row.get(6)?,
915                    completed_at: row.get(7)?,
916                    created_at: row.get(8)?,
917                    updated_at: row.get(9)?,
918                })
919            })?
920            .collect::<Result<Vec<_>, _>>()?;
921
922        Ok(estimates)
923    }
924}
925
926pub struct InsightQueries;
927
928impl InsightQueries {
929    pub fn calculate_weekly_summary(
930        conn: &Connection,
931        week_start: NaiveDate,
932    ) -> Result<InsightData> {
933        let week_end = week_start + chrono::Duration::days(6);
934
935        let mut stmt = conn.prepare(
936            "SELECT 
937                COALESCE(SUM(CASE WHEN end_time IS NOT NULL THEN 
938                    (julianday(end_time) - julianday(start_time)) * 86400 - COALESCE(paused_duration, 0)
939                ELSE 0 END), 0) as total_seconds,
940                COUNT(*) as session_count
941             FROM sessions
942             WHERE DATE(start_time) >= ?1 AND DATE(start_time) <= ?2 AND end_time IS NOT NULL "
943        )?;
944
945        let (total_seconds, session_count): (i64, i64) =
946            stmt.query_row([week_start, week_end], |row| Ok((row.get(0)?, row.get(1)?)))?;
947
948        let total_hours = total_seconds as f64 / 3600.0;
949        let avg_session_duration = if session_count > 0 {
950            total_hours / session_count as f64
951        } else {
952            0.0
953        };
954
955        Ok(InsightData {
956            total_hours,
957            sessions_count: session_count,
958            avg_session_duration,
959            most_active_day: None,
960            most_active_time: None,
961            productivity_score: None,
962            project_breakdown: vec![],
963            trends: vec![],
964        })
965    }
966
967    pub fn calculate_monthly_summary(
968        conn: &Connection,
969        month_start: NaiveDate,
970    ) -> Result<InsightData> {
971        let month_end = month_start + chrono::Duration::days(30);
972
973        let mut stmt = conn.prepare(
974            "SELECT 
975                COALESCE(SUM(CASE WHEN end_time IS NOT NULL THEN 
976                    (julianday(end_time) - julianday(start_time)) * 86400 - COALESCE(paused_duration, 0)
977                ELSE 0 END), 0) as total_seconds,
978                COUNT(*) as session_count
979             FROM sessions
980             WHERE DATE(start_time) >= ?1 AND DATE(start_time) <= ?2 AND end_time IS NOT NULL "
981        )?;
982
983        let (total_seconds, session_count): (i64, i64) = stmt
984            .query_row([month_start, month_end], |row| {
985                Ok((row.get(0)?, row.get(1)?))
986            })?;
987
988        let total_hours = total_seconds as f64 / 3600.0;
989        let avg_session_duration = if session_count > 0 {
990            total_hours / session_count as f64
991        } else {
992            0.0
993        };
994
995        Ok(InsightData {
996            total_hours,
997            sessions_count: session_count,
998            avg_session_duration,
999            most_active_day: None,
1000            most_active_time: None,
1001            productivity_score: None,
1002            project_breakdown: vec![],
1003            trends: vec![],
1004        })
1005    }
1006}
tempo_cli/db/advanced_queries.rs

tempo_cli/db/
advanced_queries.rs
