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!(
369            "UPDATE project_templates SET {} WHERE id = ?",
370            updates.join(", ")
371        );
372        let mut stmt = conn.prepare(&sql)?;
373        let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();
374        let changes = stmt.execute(&param_refs[..])?;
375
376        Ok(changes > 0)
377    }
378
379    pub fn delete(conn: &Connection, template_id: i64) -> Result<bool> {
380        let mut stmt = conn.prepare("DELETE FROM project_templates WHERE id = ?1")?;
381        let changes = stmt.execute([template_id])?;
382        Ok(changes > 0)
383    }
384
385    pub fn delete_by_name(conn: &Connection, name: &str) -> Result<bool> {
386        let mut stmt = conn.prepare("DELETE FROM project_templates WHERE name = ?1")?;
387        let changes = stmt.execute([name])?;
388        Ok(changes > 0)
389    }
390}
391
392pub struct WorkspaceQueries;
393
394impl WorkspaceQueries {
395    pub fn create(conn: &Connection, workspace: &Workspace) -> Result<i64> {
396        let mut stmt = conn.prepare(
397            "INSERT INTO workspaces (name, description, path)
398             VALUES (?1, ?2, ?3)",
399        )?;
400
401        stmt.execute(params![
402            workspace.name,
403            workspace.description,
404            workspace
405                .path
406                .as_ref()
407                .map(|p| p.to_string_lossy().to_string())
408        ])?;
409
410        Ok(conn.last_insert_rowid())
411    }
412
413    pub fn list_all(conn: &Connection) -> Result<Vec<Workspace>> {
414        let mut stmt = conn.prepare(
415            "SELECT id, name, description, path, created_at, updated_at
416             FROM workspaces ORDER BY name",
417        )?;
418
419        let workspaces = stmt
420            .query_map([], |row| {
421                Ok(Workspace {
422                    id: Some(row.get(0)?),
423                    name: row.get(1)?,
424                    description: row.get(2)?,
425                    path: row.get::<_, Option<String>>(3)?.map(|s| s.into()),
426                    created_at: row.get(4)?,
427                    updated_at: row.get(5)?,
428                })
429            })?
430            .collect::<Result<Vec<_>, _>>()?;
431
432        Ok(workspaces)
433    }
434
435    pub fn find_by_name(conn: &Connection, name: &str) -> Result<Option<Workspace>> {
436        let mut stmt = conn.prepare(
437            "SELECT id, name, description, path, created_at, updated_at
438             FROM workspaces WHERE name = ?1",
439        )?;
440
441        let workspace = stmt
442            .query_row([name], |row| {
443                Ok(Workspace {
444                    id: Some(row.get(0)?),
445                    name: row.get(1)?,
446                    description: row.get(2)?,
447                    path: row.get::<_, Option<String>>(3)?.map(|s| s.into()),
448                    created_at: row.get(4)?,
449                    updated_at: row.get(5)?,
450                })
451            })
452            .optional()?;
453
454        Ok(workspace)
455    }
456
457    pub fn delete(conn: &Connection, workspace_id: i64) -> Result<bool> {
458        let mut stmt = conn.prepare("DELETE FROM workspaces WHERE id = ?1")?;
459        let changes = stmt.execute([workspace_id])?;
460        Ok(changes > 0)
461    }
462
463    pub fn add_project(conn: &Connection, workspace_id: i64, project_id: i64) -> Result<bool> {
464        let mut stmt = conn.prepare(
465            "INSERT OR IGNORE INTO workspace_projects (workspace_id, project_id)
466             VALUES (?1, ?2)",
467        )?;
468        let changes = stmt.execute(params![workspace_id, project_id])?;
469        Ok(changes > 0)
470    }
471
472    pub fn remove_project(conn: &Connection, workspace_id: i64, project_id: i64) -> Result<bool> {
473        let mut stmt = conn.prepare(
474            "DELETE FROM workspace_projects 
475             WHERE workspace_id = ?1 AND project_id = ?2",
476        )?;
477        let changes = stmt.execute(params![workspace_id, project_id])?;
478        Ok(changes > 0)
479    }
480
481    pub fn list_projects(
482        conn: &Connection,
483        workspace_id: i64,
484    ) -> Result<Vec<crate::models::Project>> {
485        let mut stmt = conn.prepare(
486            "SELECT p.id, p.name, p.path, p.git_hash, p.created_at, p.updated_at, p.is_archived, p.description
487             FROM projects p 
488             JOIN workspace_projects wp ON p.id = wp.project_id
489             WHERE wp.workspace_id = ?1
490             ORDER BY p.name"
491        )?;
492
493        let projects = stmt
494            .query_map([workspace_id], |row| {
495                Ok(crate::models::Project {
496                    id: Some(row.get(0)?),
497                    name: row.get(1)?,
498                    path: row.get::<_, String>(2)?.into(),
499                    git_hash: row.get(3)?,
500                    created_at: row.get(4)?,
501                    updated_at: row.get(5)?,
502                    is_archived: row.get(6)?,
503                    description: row.get(7)?,
504                })
505            })?
506            .collect::<Result<Vec<_>, _>>()?;
507
508        Ok(projects)
509    }
510
511    pub fn find_by_id(conn: &Connection, workspace_id: i64) -> Result<Option<Workspace>> {
512        let mut stmt = conn.prepare(
513            "SELECT id, name, description, path, created_at, updated_at
514             FROM workspaces WHERE id = ?1",
515        )?;
516
517        let workspace = stmt
518            .query_row([workspace_id], |row| {
519                Ok(Workspace {
520                    id: Some(row.get(0)?),
521                    name: row.get(1)?,
522                    description: row.get(2)?,
523                    path: row.get::<_, Option<String>>(3)?.map(|s| s.into()),
524                    created_at: row.get(4)?,
525                    updated_at: row.get(5)?,
526                })
527            })
528            .optional()?;
529
530        Ok(workspace)
531    }
532
533    pub fn update(
534        conn: &Connection,
535        workspace_id: i64,
536        name: Option<String>,
537        description: Option<String>,
538        path: Option<Option<std::path::PathBuf>>,
539    ) -> Result<bool> {
540        let mut updates = Vec::new();
541        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
542
543        if let Some(n) = name {
544            updates.push("name = ?");
545            params.push(Box::new(n));
546        }
547        if let Some(d) = description {
548            updates.push("description = ?");
549            params.push(Box::new(d));
550        }
551        if let Some(p) = path {
552            updates.push("path = ?");
553            params.push(Box::new(p.map(|p| p.to_string_lossy().to_string())));
554        }
555
556        if updates.is_empty() {
557            return Ok(false);
558        }
559
560        updates.push("updated_at = CURRENT_TIMESTAMP");
561        params.push(Box::new(workspace_id));
562
563        let sql = format!("UPDATE workspaces SET {} WHERE id = ?", updates.join(", "));
564        let mut stmt = conn.prepare(&sql)?;
565        let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();
566        let changes = stmt.execute(&param_refs[..])?;
567
568        Ok(changes > 0)
569    }
570}
571
572pub struct GitBranchQueries;
573
574impl GitBranchQueries {
575    pub fn create_or_update(conn: &Connection, branch: &GitBranch) -> Result<i64> {
576        // Try to find existing branch
577        let existing =
578            Self::find_by_project_and_name(conn, branch.project_id, &branch.branch_name)?;
579
580        if let Some(mut existing) = existing {
581            // Update existing
582            existing.update_time(branch.total_time_seconds);
583            let mut stmt = conn.prepare(
584                "UPDATE git_branches SET last_seen = CURRENT_TIMESTAMP, total_time_seconds = total_time_seconds + ?1
585                 WHERE project_id = ?2 AND branch_name = ?3"
586            )?;
587            stmt.execute(params![
588                branch.total_time_seconds,
589                branch.project_id,
590                branch.branch_name
591            ])?;
592            existing
593                .id
594                .ok_or_else(|| anyhow::anyhow!("Git branch ID missing after update"))
595        } else {
596            // Create new
597            let mut stmt = conn.prepare(
598                "INSERT INTO git_branches (project_id, branch_name, total_time_seconds)
599                 VALUES (?1, ?2, ?3)",
600            )?;
601            stmt.execute(params![
602                branch.project_id,
603                branch.branch_name,
604                branch.total_time_seconds
605            ])?;
606            Ok(conn.last_insert_rowid())
607        }
608    }
609
610    pub fn find_by_project_and_name(
611        conn: &Connection,
612        project_id: i64,
613        branch_name: &str,
614    ) -> Result<Option<GitBranch>> {
615        let mut stmt = conn.prepare(
616            "SELECT id, project_id, branch_name, first_seen, last_seen, total_time_seconds
617             FROM git_branches WHERE project_id = ?1 AND branch_name = ?2",
618        )?;
619
620        let branch = stmt
621            .query_row(params![project_id, branch_name], |row| {
622                Ok(GitBranch {
623                    id: Some(row.get(0)?),
624                    project_id: row.get(1)?,
625                    branch_name: row.get(2)?,
626                    first_seen: row.get(3)?,
627                    last_seen: row.get(4)?,
628                    total_time_seconds: row.get(5)?,
629                })
630            })
631            .optional()?;
632
633        Ok(branch)
634    }
635
636    pub fn list_by_project(conn: &Connection, project_id: i64) -> Result<Vec<GitBranch>> {
637        let mut stmt = conn.prepare(
638            "SELECT id, project_id, branch_name, first_seen, last_seen, total_time_seconds 
639             FROM git_branches WHERE project_id = ?1 ORDER BY total_time_seconds DESC",
640        )?;
641
642        let branches = stmt
643            .query_map([project_id], |row| {
644                Ok(GitBranch {
645                    id: Some(row.get(0)?),
646                    project_id: row.get(1)?,
647                    branch_name: row.get(2)?,
648                    first_seen: row.get(3)?,
649                    last_seen: row.get(4)?,
650                    total_time_seconds: row.get(5)?,
651                })
652            })?
653            .collect::<Result<Vec<_>, _>>()?;
654
655        Ok(branches)
656    }
657
658    pub fn update(
659        conn: &Connection,
660        branch_id: i64,
661        branch_name: Option<String>,
662        additional_time_seconds: Option<i64>,
663    ) -> Result<bool> {
664        let mut updates = Vec::new();
665        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
666
667        if let Some(name) = branch_name {
668            updates.push("branch_name = ?");
669            params.push(Box::new(name));
670        }
671        if let Some(time) = additional_time_seconds {
672            updates.push("total_time_seconds = total_time_seconds + ?");
673            params.push(Box::new(time));
674        }
675
676        if updates.is_empty() {
677            return Ok(false);
678        }
679
680        updates.push("last_seen = CURRENT_TIMESTAMP");
681        params.push(Box::new(branch_id));
682
683        let sql = format!(
684            "UPDATE git_branches SET {} WHERE id = ?",
685            updates.join(", ")
686        );
687        let mut stmt = conn.prepare(&sql)?;
688        let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();
689        let changes = stmt.execute(&param_refs[..])?;
690
691        Ok(changes > 0)
692    }
693
694    pub fn delete(conn: &Connection, branch_id: i64) -> Result<bool> {
695        let mut stmt = conn.prepare("DELETE FROM git_branches WHERE id = ?1")?;
696        let changes = stmt.execute([branch_id])?;
697        Ok(changes > 0)
698    }
699
700    pub fn delete_by_project_and_name(
701        conn: &Connection,
702        project_id: i64,
703        branch_name: &str,
704    ) -> Result<bool> {
705        let mut stmt =
706            conn.prepare("DELETE FROM git_branches WHERE project_id = ?1 AND branch_name = ?2")?;
707        let changes = stmt.execute(params![project_id, branch_name])?;
708        Ok(changes > 0)
709    }
710
711    pub fn find_by_id(conn: &Connection, branch_id: i64) -> Result<Option<GitBranch>> {
712        let mut stmt = conn.prepare(
713            "SELECT id, project_id, branch_name, first_seen, last_seen, total_time_seconds
714             FROM git_branches WHERE id = ?1",
715        )?;
716
717        let branch = stmt
718            .query_row([branch_id], |row| {
719                Ok(GitBranch {
720                    id: Some(row.get(0)?),
721                    project_id: row.get(1)?,
722                    branch_name: row.get(2)?,
723                    first_seen: row.get(3)?,
724                    last_seen: row.get(4)?,
725                    total_time_seconds: row.get(5)?,
726                })
727            })
728            .optional()?;
729
730        Ok(branch)
731    }
732}
733
734pub struct TimeEstimateQueries;
735
736impl TimeEstimateQueries {
737    pub fn create(conn: &Connection, estimate: &TimeEstimate) -> Result<i64> {
738        let mut stmt = conn.prepare(
739            "INSERT INTO time_estimates (project_id, task_name, estimated_hours, actual_hours, status, due_date, completed_at)
740             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)"
741        )?;
742
743        stmt.execute(params![
744            estimate.project_id,
745            estimate.task_name,
746            estimate.estimated_hours,
747            estimate.actual_hours,
748            estimate.status.to_string(),
749            estimate.due_date,
750            estimate.completed_at
751        ])?;
752
753        Ok(conn.last_insert_rowid())
754    }
755
756    pub fn list_by_project(conn: &Connection, project_id: i64) -> Result<Vec<TimeEstimate>> {
757        let mut stmt = conn.prepare(
758            "SELECT id, project_id, task_name, estimated_hours, actual_hours, status, due_date, completed_at, created_at, updated_at 
759             FROM time_estimates WHERE project_id = ?1 ORDER BY created_at DESC"
760        )?;
761
762        let estimates = stmt
763            .query_map([project_id], |row| {
764                Ok(TimeEstimate {
765                    id: Some(row.get(0)?),
766                    project_id: row.get(1)?,
767                    task_name: row.get(2)?,
768                    estimated_hours: row.get(3)?,
769                    actual_hours: row.get(4)?,
770                    status: match row.get::<_, String>(5)?.as_str() {
771                        "planned" => crate::models::EstimateStatus::Planned,
772                        "in_progress" => crate::models::EstimateStatus::InProgress,
773                        "completed" => crate::models::EstimateStatus::Completed,
774                        "cancelled" => crate::models::EstimateStatus::Cancelled,
775                        _ => crate::models::EstimateStatus::Planned,
776                    },
777                    due_date: row.get(6)?,
778                    completed_at: row.get(7)?,
779                    created_at: row.get(8)?,
780                    updated_at: row.get(9)?,
781                })
782            })?
783            .collect::<Result<Vec<_>, _>>()?;
784
785        Ok(estimates)
786    }
787
788    pub fn record_actual(conn: &Connection, estimate_id: i64, hours: f64) -> Result<bool> {
789        let mut stmt = conn.prepare(
790            "UPDATE time_estimates SET actual_hours = ?1, status = 'completed', completed_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP 
791             WHERE id = ?2"
792        )?;
793        let changes = stmt.execute(params![hours, estimate_id])?;
794        Ok(changes > 0)
795    }
796
797    pub fn find_by_id(conn: &Connection, estimate_id: i64) -> Result<Option<TimeEstimate>> {
798        let mut stmt = conn.prepare(
799            "SELECT id, project_id, task_name, estimated_hours, actual_hours, status, due_date, completed_at, created_at, updated_at 
800             FROM time_estimates WHERE id = ?1"
801        )?;
802
803        let estimate = stmt
804            .query_row([estimate_id], |row| {
805                Ok(TimeEstimate {
806                    id: Some(row.get(0)?),
807                    project_id: row.get(1)?,
808                    task_name: row.get(2)?,
809                    estimated_hours: row.get(3)?,
810                    actual_hours: row.get(4)?,
811                    status: match row.get::<_, String>(5)?.as_str() {
812                        "planned" => crate::models::EstimateStatus::Planned,
813                        "in_progress" => crate::models::EstimateStatus::InProgress,
814                        "completed" => crate::models::EstimateStatus::Completed,
815                        "cancelled" => crate::models::EstimateStatus::Cancelled,
816                        _ => crate::models::EstimateStatus::Planned,
817                    },
818                    due_date: row.get(6)?,
819                    completed_at: row.get(7)?,
820                    created_at: row.get(8)?,
821                    updated_at: row.get(9)?,
822                })
823            })
824            .optional()?;
825
826        Ok(estimate)
827    }
828
829    pub fn update(
830        conn: &Connection,
831        estimate_id: i64,
832        task_name: Option<String>,
833        estimated_hours: Option<f64>,
834        actual_hours: Option<Option<f64>>,
835        status: Option<crate::models::EstimateStatus>,
836        due_date: Option<Option<chrono::NaiveDate>>,
837        completed_at: Option<Option<chrono::DateTime<chrono::Utc>>>,
838    ) -> Result<bool> {
839        let mut updates = Vec::new();
840        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
841
842        if let Some(name) = task_name {
843            updates.push("task_name = ?");
844            params.push(Box::new(name));
845        }
846        if let Some(est_hours) = estimated_hours {
847            updates.push("estimated_hours = ?");
848            params.push(Box::new(est_hours));
849        }
850        if let Some(act_hours) = actual_hours {
851            updates.push("actual_hours = ?");
852            params.push(Box::new(act_hours));
853        }
854        if let Some(stat) = status {
855            updates.push("status = ?");
856            params.push(Box::new(stat.to_string()));
857        }
858        if let Some(due) = due_date {
859            updates.push("due_date = ?");
860            params.push(Box::new(due));
861        }
862        if let Some(comp) = completed_at {
863            updates.push("completed_at = ?");
864            params.push(Box::new(comp));
865        }
866
867        if updates.is_empty() {
868            return Ok(false);
869        }
870
871        updates.push("updated_at = CURRENT_TIMESTAMP");
872        params.push(Box::new(estimate_id));
873
874        let sql = format!(
875            "UPDATE time_estimates SET {} WHERE id = ?",
876            updates.join(", ")
877        );
878        let mut stmt = conn.prepare(&sql)?;
879        let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();
880        let changes = stmt.execute(&param_refs[..])?;
881
882        Ok(changes > 0)
883    }
884
885    pub fn delete(conn: &Connection, estimate_id: i64) -> Result<bool> {
886        let mut stmt = conn.prepare("DELETE FROM time_estimates WHERE id = ?1")?;
887        let changes = stmt.execute([estimate_id])?;
888        Ok(changes > 0)
889    }
890
891    pub fn set_status(
892        conn: &Connection,
893        estimate_id: i64,
894        status: crate::models::EstimateStatus,
895    ) -> Result<bool> {
896        let mut stmt = conn.prepare(
897            "UPDATE time_estimates SET status = ?1, updated_at = CURRENT_TIMESTAMP WHERE id = ?2",
898        )?;
899        let changes = stmt.execute(params![status.to_string(), estimate_id])?;
900        Ok(changes > 0)
901    }
902
903    pub fn list_all(conn: &Connection) -> Result<Vec<TimeEstimate>> {
904        let mut stmt = conn.prepare(
905            "SELECT id, project_id, task_name, estimated_hours, actual_hours, status, due_date, completed_at, created_at, updated_at 
906             FROM time_estimates ORDER BY created_at DESC"
907        )?;
908
909        let estimates = stmt
910            .query_map([], |row| {
911                Ok(TimeEstimate {
912                    id: Some(row.get(0)?),
913                    project_id: row.get(1)?,
914                    task_name: row.get(2)?,
915                    estimated_hours: row.get(3)?,
916                    actual_hours: row.get(4)?,
917                    status: match row.get::<_, String>(5)?.as_str() {
918                        "planned" => crate::models::EstimateStatus::Planned,
919                        "in_progress" => crate::models::EstimateStatus::InProgress,
920                        "completed" => crate::models::EstimateStatus::Completed,
921                        "cancelled" => crate::models::EstimateStatus::Cancelled,
922                        _ => crate::models::EstimateStatus::Planned,
923                    },
924                    due_date: row.get(6)?,
925                    completed_at: row.get(7)?,
926                    created_at: row.get(8)?,
927                    updated_at: row.get(9)?,
928                })
929            })?
930            .collect::<Result<Vec<_>, _>>()?;
931
932        Ok(estimates)
933    }
934}
935
936pub struct InsightQueries;
937
938impl InsightQueries {
939    pub fn calculate_weekly_summary(
940        conn: &Connection,
941        week_start: NaiveDate,
942    ) -> Result<InsightData> {
943        let week_end = week_start + chrono::Duration::days(6);
944
945        let mut stmt = conn.prepare(
946            "SELECT 
947                COALESCE(SUM(CASE WHEN end_time IS NOT NULL THEN 
948                    (julianday(end_time) - julianday(start_time)) * 86400 - COALESCE(paused_duration, 0)
949                ELSE 0 END), 0) as total_seconds,
950                COUNT(*) as session_count
951             FROM sessions
952             WHERE DATE(start_time) >= ?1 AND DATE(start_time) <= ?2 AND end_time IS NOT NULL "
953        )?;
954
955        let (total_seconds, session_count): (f64, i64) =
956            stmt.query_row([week_start, week_end], |row| Ok((row.get(0)?, row.get(1)?)))?;
957
958        let total_hours = total_seconds / 3600.0;
959        let avg_session_duration = if session_count > 0 {
960            total_hours / session_count as f64
961        } else {
962            0.0
963        };
964
965        Ok(InsightData {
966            total_hours,
967            sessions_count: session_count,
968            avg_session_duration,
969            most_active_day: None,
970            most_active_time: None,
971            productivity_score: None,
972            project_breakdown: vec![],
973            trends: vec![],
974        })
975    }
976
977    pub fn calculate_monthly_summary(
978        conn: &Connection,
979        month_start: NaiveDate,
980    ) -> Result<InsightData> {
981        let month_end = month_start + chrono::Duration::days(30);
982
983        let mut stmt = conn.prepare(
984            "SELECT 
985                COALESCE(SUM(CASE WHEN end_time IS NOT NULL THEN 
986                    (julianday(end_time) - julianday(start_time)) * 86400 - COALESCE(paused_duration, 0)
987                ELSE 0 END), 0) as total_seconds,
988                COUNT(*) as session_count
989             FROM sessions
990             WHERE DATE(start_time) >= ?1 AND DATE(start_time) <= ?2 AND end_time IS NOT NULL "
991        )?;
992
993        let (total_seconds, session_count): (f64, i64) = stmt
994            .query_row([month_start, month_end], |row| {
995                Ok((row.get(0)?, row.get(1)?))
996            })?;
997
998        let total_hours = total_seconds / 3600.0;
999        let avg_session_duration = if session_count > 0 {
1000            total_hours / session_count as f64
1001        } else {
1002            0.0
1003        };
1004
1005        Ok(InsightData {
1006            total_hours,
1007            sessions_count: session_count,
1008            avg_session_duration,
1009            most_active_day: None,
1010            most_active_time: None,
1011            productivity_score: None,
1012            project_breakdown: vec![],
1013            trends: vec![],
1014        })
1015    }
1016}
tempo_cli/db/advanced_queries.rs

tempo_cli/db/
advanced_queries.rs
