tempo_cli/db/
queries.rs

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

tempo_cli/db/
queries.rs
