tempo_cli/db/
queries.rs

1use crate::models::{Project, Session, Tag};
2use anyhow::Result;
3use rusqlite::{params, Connection, OptionalExtension};
4use std::path::PathBuf;
5
6pub struct ProjectQueries;
7
8impl ProjectQueries {
9    pub fn create(conn: &Connection, project: &Project) -> Result<i64> {
10        let mut stmt = conn.prepare(
11            "INSERT INTO projects (name, path, git_hash, description, is_archived)
12             VALUES (?1, ?2, ?3, ?4, ?5)",
13        )?;
14
15        stmt.execute(params![
16            project.name,
17            project.path.to_string_lossy().to_string(),
18            project.git_hash,
19            project.description,
20            project.is_archived
21        ])?;
22
23        Ok(conn.last_insert_rowid())
24    }
25
26    pub fn find_by_path(conn: &Connection, path: &PathBuf) -> Result<Option<Project>> {
27        let mut stmt = conn.prepare(
28            "SELECT id, name, path, git_hash, created_at, updated_at, is_archived, description
29             FROM projects WHERE path = ?1",
30        )?;
31
32        let project = stmt
33            .query_row([path.to_string_lossy().to_string()], |row| {
34                Ok(Project {
35                    id: Some(row.get(0)?),
36                    name: row.get(1)?,
37                    path: PathBuf::from(row.get::<_, String>(2)?),
38                    git_hash: row.get(3)?,
39                    created_at: row.get(4)?,
40                    updated_at: row.get(5)?,
41                    is_archived: row.get(6)?,
42                    description: row.get(7)?,
43                })
44            })
45            .optional()?;
46
47        Ok(project)
48    }
49
50    pub fn list_all(conn: &Connection, include_archived: bool) -> Result<Vec<Project>> {
51        let sql = if include_archived {
52            "SELECT id, name, path, git_hash, created_at, updated_at, is_archived, description
53             FROM projects ORDER BY name"
54        } else {
55            "SELECT id, name, path, git_hash, created_at, updated_at, is_archived, description
56             FROM projects WHERE is_archived = 0 ORDER BY name"
57        };
58
59        let mut stmt = conn.prepare(sql)?;
60        let projects = stmt
61            .query_map([], |row| {
62                Ok(Project {
63                    id: Some(row.get(0)?),
64                    name: row.get(1)?,
65                    path: PathBuf::from(row.get::<_, String>(2)?),
66                    git_hash: row.get(3)?,
67                    created_at: row.get(4)?,
68                    updated_at: row.get(5)?,
69                    is_archived: row.get(6)?,
70                    description: row.get(7)?,
71                })
72            })?
73            .collect::<Result<Vec<_>, _>>()?;
74
75        Ok(projects)
76    }
77
78    pub fn find_by_id(conn: &Connection, project_id: i64) -> Result<Option<Project>> {
79        let mut stmt = conn.prepare(
80            "SELECT id, name, path, git_hash, created_at, updated_at, is_archived, description
81             FROM projects WHERE id = ?1",
82        )?;
83
84        let project = stmt
85            .query_row([project_id], |row| {
86                Ok(Project {
87                    id: Some(row.get(0)?),
88                    name: row.get(1)?,
89                    path: PathBuf::from(row.get::<_, String>(2)?),
90                    git_hash: row.get(3)?,
91                    created_at: row.get(4)?,
92                    updated_at: row.get(5)?,
93                    is_archived: row.get(6)?,
94                    description: row.get(7)?,
95                })
96            })
97            .optional()?;
98
99        Ok(project)
100    }
101
102    pub fn find_by_name(conn: &Connection, name: &str) -> Result<Option<Project>> {
103        let mut stmt = conn.prepare(
104            "SELECT id, name, path, git_hash, created_at, updated_at, is_archived, description
105             FROM projects WHERE name = ?1",
106        )?;
107
108        let project = stmt
109            .query_row([name], |row| {
110                Ok(Project {
111                    id: Some(row.get(0)?),
112                    name: row.get(1)?,
113                    path: PathBuf::from(row.get::<_, String>(2)?),
114                    git_hash: row.get(3)?,
115                    created_at: row.get(4)?,
116                    updated_at: row.get(5)?,
117                    is_archived: row.get(6)?,
118                    description: row.get(7)?,
119                })
120            })
121            .optional()?;
122
123        Ok(project)
124    }
125
126    pub fn archive_project(conn: &Connection, project_id: i64) -> Result<bool> {
127        let mut stmt = conn.prepare(
128            "UPDATE projects SET is_archived = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?1",
129        )?;
130        let changes = stmt.execute([project_id])?;
131        Ok(changes > 0)
132    }
133
134    pub fn unarchive_project(conn: &Connection, project_id: i64) -> Result<bool> {
135        let mut stmt = conn.prepare(
136            "UPDATE projects SET is_archived = 0, updated_at = CURRENT_TIMESTAMP WHERE id = ?1",
137        )?;
138        let changes = stmt.execute([project_id])?;
139        Ok(changes > 0)
140    }
141
142    pub fn update_project_path(
143        conn: &Connection,
144        project_id: i64,
145        new_path: &PathBuf,
146    ) -> Result<bool> {
147        let mut stmt = conn.prepare(
148            "UPDATE projects SET path = ?1, updated_at = CURRENT_TIMESTAMP WHERE id = ?2",
149        )?;
150        let changes = stmt.execute([
151            new_path.to_string_lossy().to_string(),
152            project_id.to_string(),
153        ])?;
154        Ok(changes > 0)
155    }
156
157    pub fn update_project_description(
158        conn: &Connection,
159        project_id: i64,
160        description: Option<String>,
161    ) -> Result<bool> {
162        let mut stmt = conn.prepare(
163            "UPDATE projects SET description = ?1, updated_at = CURRENT_TIMESTAMP WHERE id = ?2",
164        )?;
165        let changes = stmt.execute([description, Some(project_id.to_string())])?;
166        Ok(changes > 0)
167    }
168
169    pub fn delete_project(conn: &Connection, project_id: i64) -> Result<bool> {
170        let mut stmt = conn.prepare("DELETE FROM projects WHERE id = ?1")?;
171        let changes = stmt.execute([project_id])?;
172        Ok(changes > 0)
173    }
174
175    pub fn get_project_stats(
176        conn: &Connection,
177        project_id: i64,
178    ) -> Result<Option<(i64, i64, i64)>> {
179        // (total_sessions, total_time_seconds, avg_session_seconds)
180        let mut stmt = conn.prepare(
181            "SELECT 
182                COUNT(*) as session_count,
183                COALESCE(SUM(CASE 
184                    WHEN end_time IS NOT NULL THEN 
185                        (julianday(end_time) - julianday(start_time)) * 86400 - paused_duration
186                    ELSE 0
187                END), 0) as total_time,
188                COALESCE(AVG(CASE 
189                    WHEN end_time IS NOT NULL THEN 
190                        (julianday(end_time) - julianday(start_time)) * 86400 - paused_duration
191                    ELSE 0
192                END), 0) as avg_time
193             FROM sessions WHERE project_id = ?1 AND end_time IS NOT NULL",
194        )?;
195
196        let stats = stmt
197            .query_row([project_id], |row| {
198                Ok((
199                    row.get::<_, i64>(0)?,
200                    row.get::<_, f64>(1)? as i64,
201                    row.get::<_, f64>(2)? as i64,
202                ))
203            })
204            .optional()?;
205
206        Ok(stats)
207    }
208
209    pub fn update_name(conn: &Connection, project_id: i64, name: String) -> Result<bool> {
210        let mut stmt = conn.prepare(
211            "UPDATE projects SET name = ?1, updated_at = CURRENT_TIMESTAMP WHERE id = ?2",
212        )?;
213        let changes = stmt.execute([&name, &project_id.to_string()])?;
214        Ok(changes > 0)
215    }
216
217    pub fn update_archived(conn: &Connection, project_id: i64, archived: bool) -> Result<bool> {
218        let mut stmt = conn.prepare(
219            "UPDATE projects SET is_archived = ?1, updated_at = CURRENT_TIMESTAMP WHERE id = ?2",
220        )?;
221        let changes = stmt.execute(params![archived, project_id])?;
222        Ok(changes > 0)
223    }
224}
225
226pub struct SessionQueries;
227
228impl SessionQueries {
229    pub fn create(conn: &Connection, session: &Session) -> Result<i64> {
230        let mut stmt = conn.prepare(
231            "INSERT INTO sessions (project_id, start_time, end_time, context, paused_duration, notes)
232             VALUES (?1, ?2, ?3, ?4, ?5, ?6)"
233        )?;
234
235        stmt.execute(params![
236            session.project_id,
237            session.start_time,
238            session.end_time,
239            session.context.to_string(),
240            session.paused_duration.num_seconds(),
241            session.notes
242        ])?;
243
244        Ok(conn.last_insert_rowid())
245    }
246
247    pub fn find_active_session(conn: &Connection) -> Result<Option<Session>> {
248        let mut stmt = conn.prepare(
249            "SELECT id, project_id, start_time, end_time, context, paused_duration, notes, created_at
250             FROM sessions WHERE end_time IS NULL LIMIT 1"
251        )?;
252
253        let session = stmt
254            .query_row([], |row| {
255                Ok(Session {
256                    id: Some(row.get(0)?),
257                    project_id: row.get(1)?,
258                    start_time: row.get(2)?,
259                    end_time: row.get(3)?,
260                    context: row.get::<_, String>(4)?.parse().map_err(|_e| {
261                        rusqlite::Error::InvalidColumnType(
262                            4,
263                            "context".to_string(),
264                            rusqlite::types::Type::Text,
265                        )
266                    })?,
267                    paused_duration: chrono::Duration::seconds(row.get::<_, i64>(5)?),
268                    notes: row.get(6)?,
269                    created_at: row.get(7)?,
270                })
271            })
272            .optional()?;
273
274        Ok(session)
275    }
276
277    pub fn end_session(conn: &Connection, session_id: i64) -> Result<()> {
278        let mut stmt =
279            conn.prepare("UPDATE sessions SET end_time = CURRENT_TIMESTAMP WHERE id = ?1")?;
280
281        stmt.execute([session_id])?;
282        Ok(())
283    }
284
285    pub fn list_recent(conn: &Connection, limit: usize) -> Result<Vec<Session>> {
286        let mut stmt = conn.prepare(
287            "SELECT id, project_id, start_time, end_time, context, paused_duration, notes, created_at
288             FROM sessions ORDER BY start_time DESC LIMIT ?1"
289        )?;
290
291        let sessions = stmt
292            .query_map([limit], |row| {
293                Ok(Session {
294                    id: Some(row.get(0)?),
295                    project_id: row.get(1)?,
296                    start_time: row.get(2)?,
297                    end_time: row.get(3)?,
298                    context: row.get::<_, String>(4)?.parse().map_err(|_e| {
299                        rusqlite::Error::InvalidColumnType(
300                            4,
301                            "context".to_string(),
302                            rusqlite::types::Type::Text,
303                        )
304                    })?,
305                    paused_duration: chrono::Duration::seconds(row.get::<_, i64>(5)?),
306                    notes: row.get(6)?,
307                    created_at: row.get(7)?,
308                })
309            })?
310            .collect::<Result<Vec<_>, _>>()?;
311
312        Ok(sessions)
313    }
314
315    pub fn find_by_id(conn: &Connection, session_id: i64) -> Result<Option<Session>> {
316        let mut stmt = conn.prepare(
317            "SELECT id, project_id, start_time, end_time, context, paused_duration, notes, created_at
318             FROM sessions WHERE id = ?1"
319        )?;
320
321        let session = stmt
322            .query_row([session_id], |row| {
323                Ok(Session {
324                    id: Some(row.get(0)?),
325                    project_id: row.get(1)?,
326                    start_time: row.get(2)?,
327                    end_time: row.get(3)?,
328                    context: row.get::<_, String>(4)?.parse().map_err(|_e| {
329                        rusqlite::Error::InvalidColumnType(
330                            4,
331                            "context".to_string(),
332                            rusqlite::types::Type::Text,
333                        )
334                    })?,
335                    paused_duration: chrono::Duration::seconds(row.get::<_, i64>(5)?),
336                    notes: row.get(6)?,
337                    created_at: row.get(7)?,
338                })
339            })
340            .optional()?;
341
342        Ok(session)
343    }
344
345    pub fn update_session(
346        conn: &Connection,
347        session_id: i64,
348        start_time: Option<chrono::DateTime<chrono::Utc>>,
349        end_time: Option<Option<chrono::DateTime<chrono::Utc>>>,
350        project_id: Option<i64>,
351        notes: Option<Option<String>>,
352    ) -> Result<()> {
353        let mut updates = Vec::new();
354        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
355
356        if let Some(st) = start_time {
357            updates.push("start_time = ?");
358            params.push(Box::new(st));
359        }
360
361        if let Some(et) = end_time {
362            updates.push("end_time = ?");
363            params.push(Box::new(et));
364        }
365
366        if let Some(pid) = project_id {
367            updates.push("project_id = ?");
368            params.push(Box::new(pid));
369        }
370
371        if let Some(n) = notes {
372            updates.push("notes = ?");
373            params.push(Box::new(n));
374        }
375
376        if updates.is_empty() {
377            return Ok(());
378        }
379
380        params.push(Box::new(session_id));
381
382        let sql = format!("UPDATE sessions SET {} WHERE id = ?", updates.join(", "));
383        let mut stmt = conn.prepare(&sql)?;
384        let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();
385        stmt.execute(&param_refs[..])?;
386
387        Ok(())
388    }
389
390    pub fn delete_session(conn: &Connection, session_id: i64) -> Result<()> {
391        let mut stmt = conn.prepare("DELETE FROM sessions WHERE id = ?1")?;
392        stmt.execute([session_id])?;
393        Ok(())
394    }
395
396    pub fn list_with_filter(
397        conn: &Connection,
398        project_id: Option<i64>,
399        start_date: Option<chrono::NaiveDate>,
400        end_date: Option<chrono::NaiveDate>,
401        limit: Option<usize>,
402    ) -> Result<Vec<Session>> {
403        let mut sql = "SELECT id, project_id, start_time, end_time, context, paused_duration, notes, created_at FROM sessions WHERE 1=1".to_string();
404        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
405
406        if let Some(pid) = project_id {
407            sql.push_str(" AND project_id = ?");
408            params.push(Box::new(pid));
409        }
410
411        if let Some(sd) = start_date {
412            sql.push_str(" AND date(start_time) >= ?");
413            params.push(Box::new(sd.format("%Y-%m-%d").to_string()));
414        }
415
416        if let Some(ed) = end_date {
417            sql.push_str(" AND date(start_time) <= ?");
418            params.push(Box::new(ed.format("%Y-%m-%d").to_string()));
419        }
420
421        sql.push_str(" ORDER BY start_time DESC");
422
423        if let Some(lim) = limit {
424            sql.push_str(" LIMIT ?");
425            params.push(Box::new(lim));
426        }
427
428        let mut stmt = conn.prepare(&sql)?;
429        let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();
430
431        let sessions = stmt
432            .query_map(&param_refs[..], |row| {
433                Ok(Session {
434                    id: Some(row.get(0)?),
435                    project_id: row.get(1)?,
436                    start_time: row.get(2)?,
437                    end_time: row.get(3)?,
438                    context: row.get::<_, String>(4)?.parse().map_err(|_e| {
439                        rusqlite::Error::InvalidColumnType(
440                            4,
441                            "context".to_string(),
442                            rusqlite::types::Type::Text,
443                        )
444                    })?,
445                    paused_duration: chrono::Duration::seconds(row.get::<_, i64>(5)?),
446                    notes: row.get(6)?,
447                    created_at: row.get(7)?,
448                })
449            })?
450            .collect::<Result<Vec<_>, _>>()?;
451
452        Ok(sessions)
453    }
454
455    pub fn bulk_update_project(
456        conn: &Connection,
457        session_ids: &[i64],
458        new_project_id: i64,
459    ) -> Result<usize> {
460        let placeholders = vec!["?"; session_ids.len()].join(",");
461        let sql = format!(
462            "UPDATE sessions SET project_id = ? WHERE id IN ({})",
463            placeholders
464        );
465
466        let mut stmt = conn.prepare(&sql)?;
467        let mut params: Vec<&dyn rusqlite::ToSql> = vec![&new_project_id];
468        for id in session_ids {
469            params.push(id);
470        }
471
472        let changes = stmt.execute(&params[..])?;
473        Ok(changes)
474    }
475
476    pub fn bulk_delete(conn: &Connection, session_ids: &[i64]) -> Result<usize> {
477        let placeholders = vec!["?"; session_ids.len()].join(",");
478        let sql = format!("DELETE FROM sessions WHERE id IN ({})", placeholders);
479
480        let mut stmt = conn.prepare(&sql)?;
481        let params: Vec<&dyn rusqlite::ToSql> = session_ids
482            .iter()
483            .map(|id| id as &dyn rusqlite::ToSql)
484            .collect();
485
486        let changes = stmt.execute(&params[..])?;
487        Ok(changes)
488    }
489
490    pub fn merge_sessions(
491        conn: &Connection,
492        session_ids: &[i64],
493        target_project_id: Option<i64>,
494        notes: Option<String>,
495    ) -> Result<i64> {
496        if session_ids.is_empty() {
497            return Err(anyhow::anyhow!("No sessions to merge"));
498        }
499
500        // Get all sessions to merge
501        let placeholders = vec!["?"; session_ids.len()].join(",");
502        let sql = format!(
503            "SELECT id, project_id, start_time, end_time, context, paused_duration, notes, created_at 
504             FROM sessions WHERE id IN ({}) ORDER BY start_time", 
505            placeholders
506        );
507
508        let mut stmt = conn.prepare(&sql)?;
509        let params: Vec<&dyn rusqlite::ToSql> = session_ids
510            .iter()
511            .map(|id| id as &dyn rusqlite::ToSql)
512            .collect();
513
514        let sessions: Result<Vec<Session>, _> = stmt
515            .query_map(&params[..], |row| {
516                Ok(Session {
517                    id: Some(row.get(0)?),
518                    project_id: row.get(1)?,
519                    start_time: row.get(2)?,
520                    end_time: row.get(3)?,
521                    context: row.get::<_, String>(4)?.parse().map_err(|_e| {
522                        rusqlite::Error::InvalidColumnType(
523                            4,
524                            "context".to_string(),
525                            rusqlite::types::Type::Text,
526                        )
527                    })?,
528                    paused_duration: chrono::Duration::seconds(row.get::<_, i64>(5)?),
529                    notes: row.get(6)?,
530                    created_at: row.get(7)?,
531                })
532            })?
533            .collect();
534
535        let sessions = sessions?;
536        if sessions.is_empty() {
537            return Err(anyhow::anyhow!("No valid sessions found to merge"));
538        }
539
540        // Calculate merged session properties
541        let earliest_start =
542            sessions.iter().map(|s| s.start_time).min().ok_or_else(|| {
543                anyhow::anyhow!("No sessions found to determine earliest start time")
544            })?;
545        let latest_end = sessions.iter().filter_map(|s| s.end_time).max();
546        let total_paused = sessions
547            .iter()
548            .map(|s| s.paused_duration)
549            .fold(chrono::Duration::zero(), |acc, d| acc + d);
550        let merged_project_id = target_project_id.unwrap_or(sessions[0].project_id);
551        let merged_context = sessions[0].context; // Use first session's context
552
553        // Create merged session
554        let merged_session = Session {
555            id: None,
556            project_id: merged_project_id,
557            start_time: earliest_start,
558            end_time: latest_end,
559            context: merged_context,
560            paused_duration: total_paused,
561            notes,
562            created_at: chrono::Utc::now(),
563        };
564
565        // Insert merged session
566        let merged_id = Self::create(conn, &merged_session)?;
567
568        // Create audit records for the merge
569        for session in &sessions {
570            if let Some(session_id) = session.id {
571                SessionEditQueries::create_edit_record(
572                    conn,
573                    session_id,
574                    session.start_time,
575                    session.end_time,
576                    merged_session.start_time,
577                    merged_session.end_time,
578                    Some(format!("Merged into session {}", merged_id)),
579                )?;
580            }
581        }
582
583        // Delete original sessions
584        Self::bulk_delete(conn, session_ids)?;
585
586        Ok(merged_id)
587    }
588
589    pub fn split_session(
590        conn: &Connection,
591        session_id: i64,
592        split_times: &[chrono::DateTime<chrono::Utc>],
593        notes_list: Option<Vec<String>>,
594    ) -> Result<Vec<i64>> {
595        // Get the original session
596        let original_session = Self::find_by_id(conn, session_id)?
597            .ok_or_else(|| anyhow::anyhow!("Session {} not found", session_id))?;
598
599        if split_times.is_empty() {
600            return Err(anyhow::anyhow!("No split times provided"));
601        }
602
603        // Validate split times are within session bounds
604        for &split_time in split_times {
605            if split_time <= original_session.start_time {
606                return Err(anyhow::anyhow!(
607                    "Split time {} is before session start",
608                    split_time
609                ));
610            }
611            if let Some(end_time) = original_session.end_time {
612                if split_time >= end_time {
613                    return Err(anyhow::anyhow!(
614                        "Split time {} is after session end",
615                        split_time
616                    ));
617                }
618            }
619        }
620
621        // Sort split times
622        let mut sorted_splits = split_times.to_vec();
623        sorted_splits.sort();
624
625        let mut new_session_ids = Vec::new();
626        let mut current_start = original_session.start_time;
627
628        // Create sessions for each split segment
629        for (i, &split_time) in sorted_splits.iter().enumerate() {
630            let segment_notes = notes_list
631                .as_ref()
632                .and_then(|list| list.get(i))
633                .cloned()
634                .or_else(|| original_session.notes.clone());
635
636            let split_session = Session {
637                id: None,
638                project_id: original_session.project_id,
639                start_time: current_start,
640                end_time: Some(split_time),
641                context: original_session.context,
642                paused_duration: chrono::Duration::zero(), // Reset paused duration for splits
643                notes: segment_notes,
644                created_at: chrono::Utc::now(),
645            };
646
647            let split_id = Self::create(conn, &split_session)?;
648            new_session_ids.push(split_id);
649            current_start = split_time;
650        }
651
652        // Create final segment (from last split to original end)
653        let final_notes = notes_list
654            .as_ref()
655            .and_then(|list| list.get(sorted_splits.len()))
656            .cloned()
657            .or_else(|| original_session.notes.clone());
658
659        let final_session = Session {
660            id: None,
661            project_id: original_session.project_id,
662            start_time: current_start,
663            end_time: original_session.end_time,
664            context: original_session.context,
665            paused_duration: chrono::Duration::zero(),
666            notes: final_notes,
667            created_at: chrono::Utc::now(),
668        };
669
670        let final_id = Self::create(conn, &final_session)?;
671        new_session_ids.push(final_id);
672
673        // Create audit record for the split
674        SessionEditQueries::create_edit_record(
675            conn,
676            session_id,
677            original_session.start_time,
678            original_session.end_time,
679            original_session.start_time,
680            original_session.end_time,
681            Some(format!("Split into sessions: {:?}", new_session_ids)),
682        )?;
683
684        // Delete original session
685        Self::delete_session(conn, session_id)?;
686
687        Ok(new_session_ids)
688    }
689
690    pub fn list_by_date_range(
691        conn: &Connection,
692        from: chrono::DateTime<chrono::Utc>,
693        to: chrono::DateTime<chrono::Utc>,
694    ) -> Result<Vec<Session>> {
695        let mut stmt = conn.prepare(
696            "SELECT id, project_id, start_time, end_time, context, paused_duration, notes, created_at
697             FROM sessions 
698             WHERE start_time >= ?1 AND start_time <= ?2
699             ORDER BY start_time DESC"
700        )?;
701
702        let sessions = stmt
703            .query_map([from, to], |row| {
704                Ok(Session {
705                    id: Some(row.get(0)?),
706                    project_id: row.get(1)?,
707                    start_time: row.get(2)?,
708                    end_time: row.get(3)?,
709                    context: row.get::<_, String>(4)?.parse().map_err(|_e| {
710                        rusqlite::Error::InvalidColumnType(
711                            4,
712                            "context".to_string(),
713                            rusqlite::types::Type::Text,
714                        )
715                    })?,
716                    paused_duration: chrono::Duration::seconds(row.get::<_, i64>(5)?),
717                    notes: row.get(6)?,
718                    created_at: row.get(7)?,
719                })
720            })?
721            .collect::<Result<Vec<_>, _>>()?;
722
723        Ok(sessions)
724    }
725}
726
727pub struct SessionEditQueries;
728
729impl SessionEditQueries {
730    pub fn create_edit_record(
731        conn: &Connection,
732        session_id: i64,
733        original_start: chrono::DateTime<chrono::Utc>,
734        original_end: Option<chrono::DateTime<chrono::Utc>>,
735        new_start: chrono::DateTime<chrono::Utc>,
736        new_end: Option<chrono::DateTime<chrono::Utc>>,
737        reason: Option<String>,
738    ) -> Result<i64> {
739        let mut stmt = conn.prepare(
740            "INSERT INTO session_edits (session_id, original_start_time, original_end_time, new_start_time, new_end_time, edit_reason)
741             VALUES (?1, ?2, ?3, ?4, ?5, ?6)"
742        )?;
743
744        stmt.execute(params![
745            session_id,
746            original_start,
747            original_end,
748            new_start,
749            new_end,
750            reason
751        ])?;
752
753        Ok(conn.last_insert_rowid())
754    }
755
756    pub fn list_session_edits(
757        conn: &Connection,
758        session_id: i64,
759    ) -> Result<Vec<crate::models::SessionEdit>> {
760        let mut stmt = conn.prepare(
761            "SELECT id, session_id, original_start_time, original_end_time, new_start_time, new_end_time, edit_reason, created_at
762             FROM session_edits WHERE session_id = ?1 ORDER BY created_at DESC"
763        )?;
764
765        let edits = stmt
766            .query_map([session_id], |row| {
767                Ok(crate::models::SessionEdit {
768                    id: Some(row.get(0)?),
769                    session_id: row.get(1)?,
770                    original_start_time: row.get(2)?,
771                    original_end_time: row.get(3)?,
772                    new_start_time: row.get(4)?,
773                    new_end_time: row.get(5)?,
774                    edit_reason: row.get(6)?,
775                    created_at: row.get(7)?,
776                })
777            })?
778            .collect::<Result<Vec<_>, _>>()?;
779
780        Ok(edits)
781    }
782}
783
784pub struct TagQueries;
785
786impl TagQueries {
787    pub fn create(conn: &Connection, tag: &Tag) -> Result<i64> {
788        let mut stmt =
789            conn.prepare("INSERT INTO tags (name, color, description) VALUES (?1, ?2, ?3)")?;
790
791        stmt.execute(params![tag.name, tag.color, tag.description])?;
792
793        Ok(conn.last_insert_rowid())
794    }
795
796    pub fn list_all(conn: &Connection) -> Result<Vec<Tag>> {
797        let mut stmt = conn
798            .prepare("SELECT id, name, color, description, created_at FROM tags ORDER BY name")?;
799
800        let tags = stmt
801            .query_map([], |row| {
802                Ok(Tag {
803                    id: Some(row.get(0)?),
804                    name: row.get(1)?,
805                    color: row.get(2)?,
806                    description: row.get(3)?,
807                    created_at: row.get(4)?,
808                })
809            })?
810            .collect::<Result<Vec<_>, _>>()?;
811
812        Ok(tags)
813    }
814
815    pub fn find_by_name(conn: &Connection, name: &str) -> Result<Option<Tag>> {
816        let mut stmt = conn
817            .prepare("SELECT id, name, color, description, created_at FROM tags WHERE name = ?1")?;
818
819        let tag = stmt
820            .query_row([name], |row| {
821                Ok(Tag {
822                    id: Some(row.get(0)?),
823                    name: row.get(1)?,
824                    color: row.get(2)?,
825                    description: row.get(3)?,
826                    created_at: row.get(4)?,
827                })
828            })
829            .optional()?;
830
831        Ok(tag)
832    }
833
834    pub fn delete_by_name(conn: &Connection, name: &str) -> Result<bool> {
835        let mut stmt = conn.prepare("DELETE FROM tags WHERE name = ?1")?;
836        let changes = stmt.execute([name])?;
837        Ok(changes > 0)
838    }
839
840    pub fn update_tag(
841        conn: &Connection,
842        name: &str,
843        color: Option<String>,
844        description: Option<String>,
845    ) -> Result<bool> {
846        let mut updates = Vec::new();
847        let mut params: Vec<&dyn rusqlite::ToSql> = Vec::new();
848
849        if let Some(c) = &color {
850            updates.push("color = ?");
851            params.push(c);
852        }
853
854        if let Some(d) = &description {
855            updates.push("description = ?");
856            params.push(d);
857        }
858
859        if updates.is_empty() {
860            return Ok(false);
861        }
862
863        params.push(&name);
864
865        let sql = format!("UPDATE tags SET {} WHERE name = ?", updates.join(", "));
866        let mut stmt = conn.prepare(&sql)?;
867        let changes = stmt.execute(&params[..])?;
868
869        Ok(changes > 0)
870    }
871}
tempo_cli/db/queries.rs

tempo_cli/db/
queries.rs
