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