use crate::models::{
GitBranch, Goal, GoalStatus, InsightData, ProjectTemplate, TimeEstimate, Workspace,
};
use anyhow::Result;
use chrono::NaiveDate;
use rusqlite::{params, Connection, OptionalExtension};
pub struct GoalQueries;
impl GoalQueries {
pub fn create(conn: &Connection, goal: &Goal) -> Result<i64> {
let mut stmt = conn.prepare(
"INSERT INTO goals (project_id, name, description, target_hours, start_date, end_date, current_progress, status)
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)"
)?;
stmt.execute(params![
goal.project_id,
goal.name,
goal.description,
goal.target_hours,
goal.start_date,
goal.end_date,
goal.current_progress,
goal.status.to_string()
])?;
Ok(conn.last_insert_rowid())
}
pub fn find_by_id(conn: &Connection, goal_id: i64) -> Result<Option<Goal>> {
let mut stmt = conn.prepare(
"SELECT id, project_id, name, description, target_hours, start_date, end_date, current_progress, status, created_at, updated_at
FROM goals WHERE id = ?1"
)?;
let goal = stmt
.query_row([goal_id], |row| {
Ok(Goal {
id: Some(row.get(0)?),
project_id: row.get(1)?,
name: row.get(2)?,
description: row.get(3)?,
target_hours: row.get(4)?,
start_date: row.get(5)?,
end_date: row.get(6)?,
current_progress: row.get(7)?,
status: row
.get::<_, String>(8)?
.parse()
.unwrap_or(GoalStatus::Active),
created_at: row.get(9)?,
updated_at: row.get(10)?,
})
})
.optional()?;
Ok(goal)
}
pub fn list_by_project(conn: &Connection, project_id: Option<i64>) -> Result<Vec<Goal>> {
let sql = if let Some(_pid) = project_id {
"SELECT id, project_id, name, description, target_hours, start_date, end_date, current_progress, status, created_at, updated_at
FROM goals WHERE project_id = ?1 ORDER BY created_at DESC"
} else {
"SELECT id, project_id, name, description, target_hours, start_date, end_date, current_progress, status, created_at, updated_at
FROM goals ORDER BY created_at DESC"
};
let mut stmt = conn.prepare(sql)?;
let goals = if let Some(pid) = project_id {
stmt.query_map([pid], |row| {
Ok(Goal {
id: Some(row.get(0)?),
project_id: row.get(1)?,
name: row.get(2)?,
description: row.get(3)?,
target_hours: row.get(4)?,
start_date: row.get(5)?,
end_date: row.get(6)?,
current_progress: row.get(7)?,
status: row
.get::<_, String>(8)?
.parse()
.unwrap_or(GoalStatus::Active),
created_at: row.get(9)?,
updated_at: row.get(10)?,
})
})?
.collect::<Result<Vec<_>, _>>()?
} else {
stmt.query_map([], |row| {
Ok(Goal {
id: Some(row.get(0)?),
project_id: row.get(1)?,
name: row.get(2)?,
description: row.get(3)?,
target_hours: row.get(4)?,
start_date: row.get(5)?,
end_date: row.get(6)?,
current_progress: row.get(7)?,
status: row
.get::<_, String>(8)?
.parse()
.unwrap_or(GoalStatus::Active),
created_at: row.get(9)?,
updated_at: row.get(10)?,
})
})?
.collect::<Result<Vec<_>, _>>()?
};
Ok(goals)
}
pub fn update_progress(conn: &Connection, goal_id: i64, hours: f64) -> Result<bool> {
let mut stmt = conn.prepare(
"UPDATE goals SET current_progress = current_progress + ?1, updated_at = CURRENT_TIMESTAMP
WHERE id = ?2"
)?;
let changes = stmt.execute(params![hours, goal_id])?;
Ok(changes > 0)
}
pub fn update(
conn: &Connection,
goal_id: i64,
name: Option<String>,
description: Option<String>,
target_hours: Option<f64>,
start_date: Option<Option<NaiveDate>>,
end_date: Option<Option<NaiveDate>>,
status: Option<GoalStatus>,
) -> Result<bool> {
let mut updates = Vec::new();
let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
if let Some(n) = name {
updates.push("name = ?");
params.push(Box::new(n));
}
if let Some(d) = description {
updates.push("description = ?");
params.push(Box::new(d));
}
if let Some(th) = target_hours {
updates.push("target_hours = ?");
params.push(Box::new(th));
}
if let Some(sd) = start_date {
updates.push("start_date = ?");
params.push(Box::new(sd));
}
if let Some(ed) = end_date {
updates.push("end_date = ?");
params.push(Box::new(ed));
}
if let Some(s) = status {
updates.push("status = ?");
params.push(Box::new(s.to_string()));
}
if updates.is_empty() {
return Ok(false);
}
updates.push("updated_at = CURRENT_TIMESTAMP");
params.push(Box::new(goal_id));
let sql = format!("UPDATE goals SET {} WHERE id = ?", updates.join(", "));
let mut stmt = conn.prepare(&sql)?;
let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();
let changes = stmt.execute(¶m_refs[..])?;
Ok(changes > 0)
}
pub fn delete(conn: &Connection, goal_id: i64) -> Result<bool> {
let mut stmt = conn.prepare("DELETE FROM goals WHERE id = ?1")?;
let changes = stmt.execute([goal_id])?;
Ok(changes > 0)
}
pub fn list_active(conn: &Connection) -> Result<Vec<Goal>> {
let mut stmt = conn.prepare(
"SELECT id, project_id, name, description, target_hours, start_date, end_date, current_progress, status, created_at, updated_at
FROM goals WHERE status = 'active' ORDER BY created_at DESC"
)?;
let goals = stmt
.query_map([], |row| {
Ok(Goal {
id: Some(row.get(0)?),
project_id: row.get(1)?,
name: row.get(2)?,
description: row.get(3)?,
target_hours: row.get(4)?,
start_date: row.get(5)?,
end_date: row.get(6)?,
current_progress: row.get(7)?,
status: row
.get::<_, String>(8)?
.parse()
.unwrap_or(GoalStatus::Active),
created_at: row.get(9)?,
updated_at: row.get(10)?,
})
})?
.collect::<Result<Vec<_>, _>>()?;
Ok(goals)
}
pub fn set_status(conn: &Connection, goal_id: i64, status: GoalStatus) -> Result<bool> {
let mut stmt = conn.prepare(
"UPDATE goals SET status = ?1, updated_at = CURRENT_TIMESTAMP WHERE id = ?2",
)?;
let changes = stmt.execute(params![status.to_string(), goal_id])?;
Ok(changes > 0)
}
}
pub struct TemplateQueries;
impl TemplateQueries {
pub fn create(conn: &Connection, template: &ProjectTemplate) -> Result<i64> {
let tags_json = serde_json::to_string(&template.default_tags)?;
let goals_json = serde_json::to_string(&template.default_goals)?;
let mut stmt = conn.prepare(
"INSERT INTO project_templates (name, description, default_tags, default_goals, workspace_path)
VALUES (?1, ?2, ?3, ?4, ?5)"
)?;
stmt.execute(params![
template.name,
template.description,
tags_json,
goals_json,
template
.workspace_path
.as_ref()
.map(|p| p.to_string_lossy().to_string())
])?;
Ok(conn.last_insert_rowid())
}
pub fn list_all(conn: &Connection) -> Result<Vec<ProjectTemplate>> {
let mut stmt = conn.prepare(
"SELECT id, name, description, default_tags, default_goals, workspace_path, created_at
FROM project_templates ORDER BY name",
)?;
let templates = stmt
.query_map([], |row| {
let tags_json: String = row.get(3)?;
let goals_json: String = row.get(4)?;
Ok(ProjectTemplate {
id: Some(row.get(0)?),
name: row.get(1)?,
description: row.get(2)?,
default_tags: serde_json::from_str(&tags_json).unwrap_or_default(),
default_goals: serde_json::from_str(&goals_json).unwrap_or_default(),
workspace_path: row.get::<_, Option<String>>(5)?.map(|s| s.into()),
created_at: row.get(6)?,
})
})?
.collect::<Result<Vec<_>, _>>()?;
Ok(templates)
}
pub fn find_by_id(conn: &Connection, template_id: i64) -> Result<Option<ProjectTemplate>> {
let mut stmt = conn.prepare(
"SELECT id, name, description, default_tags, default_goals, workspace_path, created_at
FROM project_templates WHERE id = ?1",
)?;
let template = stmt
.query_row([template_id], |row| {
let tags_json: String = row.get(3)?;
let goals_json: String = row.get(4)?;
Ok(ProjectTemplate {
id: Some(row.get(0)?),
name: row.get(1)?,
description: row.get(2)?,
default_tags: serde_json::from_str(&tags_json).unwrap_or_default(),
default_goals: serde_json::from_str(&goals_json).unwrap_or_default(),
workspace_path: row.get::<_, Option<String>>(5)?.map(|s| s.into()),
created_at: row.get(6)?,
})
})
.optional()?;
Ok(template)
}
pub fn find_by_name(conn: &Connection, name: &str) -> Result<Option<ProjectTemplate>> {
let mut stmt = conn.prepare(
"SELECT id, name, description, default_tags, default_goals, workspace_path, created_at
FROM project_templates WHERE name = ?1",
)?;
let template = stmt
.query_row([name], |row| {
let tags_json: String = row.get(3)?;
let goals_json: String = row.get(4)?;
Ok(ProjectTemplate {
id: Some(row.get(0)?),
name: row.get(1)?,
description: row.get(2)?,
default_tags: serde_json::from_str(&tags_json).unwrap_or_default(),
default_goals: serde_json::from_str(&goals_json).unwrap_or_default(),
workspace_path: row.get::<_, Option<String>>(5)?.map(|s| s.into()),
created_at: row.get(6)?,
})
})
.optional()?;
Ok(template)
}
pub fn update(
conn: &Connection,
template_id: i64,
name: Option<String>,
description: Option<String>,
default_tags: Option<Vec<String>>,
default_goals: Option<Vec<String>>,
workspace_path: Option<Option<std::path::PathBuf>>,
) -> Result<bool> {
let mut updates = Vec::new();
let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
if let Some(n) = name {
updates.push("name = ?");
params.push(Box::new(n));
}
if let Some(d) = description {
updates.push("description = ?");
params.push(Box::new(d));
}
if let Some(tags) = default_tags {
updates.push("default_tags = ?");
let tags_json = serde_json::to_string(&tags).unwrap_or_default();
params.push(Box::new(tags_json));
}
if let Some(goals) = default_goals {
updates.push("default_goals = ?");
let goals_json = serde_json::to_string(&goals).unwrap_or_default();
params.push(Box::new(goals_json));
}
if let Some(path) = workspace_path {
updates.push("workspace_path = ?");
params.push(Box::new(path.map(|p| p.to_string_lossy().to_string())));
}
if updates.is_empty() {
return Ok(false);
}
params.push(Box::new(template_id));
let sql = format!(
"UPDATE project_templates SET {} WHERE id = ?",
updates.join(", ")
);
let mut stmt = conn.prepare(&sql)?;
let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();
let changes = stmt.execute(¶m_refs[..])?;
Ok(changes > 0)
}
pub fn delete(conn: &Connection, template_id: i64) -> Result<bool> {
let mut stmt = conn.prepare("DELETE FROM project_templates WHERE id = ?1")?;
let changes = stmt.execute([template_id])?;
Ok(changes > 0)
}
pub fn delete_by_name(conn: &Connection, name: &str) -> Result<bool> {
let mut stmt = conn.prepare("DELETE FROM project_templates WHERE name = ?1")?;
let changes = stmt.execute([name])?;
Ok(changes > 0)
}
}
pub struct WorkspaceQueries;
impl WorkspaceQueries {
pub fn create(conn: &Connection, workspace: &Workspace) -> Result<i64> {
let mut stmt = conn.prepare(
"INSERT INTO workspaces (name, description, path)
VALUES (?1, ?2, ?3)",
)?;
stmt.execute(params![
workspace.name,
workspace.description,
workspace
.path
.as_ref()
.map(|p| p.to_string_lossy().to_string())
])?;
Ok(conn.last_insert_rowid())
}
pub fn list_all(conn: &Connection) -> Result<Vec<Workspace>> {
let mut stmt = conn.prepare(
"SELECT id, name, description, path, created_at, updated_at
FROM workspaces ORDER BY name",
)?;
let workspaces = stmt
.query_map([], |row| {
Ok(Workspace {
id: Some(row.get(0)?),
name: row.get(1)?,
description: row.get(2)?,
path: row.get::<_, Option<String>>(3)?.map(|s| s.into()),
created_at: row.get(4)?,
updated_at: row.get(5)?,
})
})?
.collect::<Result<Vec<_>, _>>()?;
Ok(workspaces)
}
pub fn find_by_name(conn: &Connection, name: &str) -> Result<Option<Workspace>> {
let mut stmt = conn.prepare(
"SELECT id, name, description, path, created_at, updated_at
FROM workspaces WHERE name = ?1",
)?;
let workspace = stmt
.query_row([name], |row| {
Ok(Workspace {
id: Some(row.get(0)?),
name: row.get(1)?,
description: row.get(2)?,
path: row.get::<_, Option<String>>(3)?.map(|s| s.into()),
created_at: row.get(4)?,
updated_at: row.get(5)?,
})
})
.optional()?;
Ok(workspace)
}
pub fn delete(conn: &Connection, workspace_id: i64) -> Result<bool> {
let mut stmt = conn.prepare("DELETE FROM workspaces WHERE id = ?1")?;
let changes = stmt.execute([workspace_id])?;
Ok(changes > 0)
}
pub fn add_project(conn: &Connection, workspace_id: i64, project_id: i64) -> Result<bool> {
let mut stmt = conn.prepare(
"INSERT OR IGNORE INTO workspace_projects (workspace_id, project_id)
VALUES (?1, ?2)",
)?;
let changes = stmt.execute(params![workspace_id, project_id])?;
Ok(changes > 0)
}
pub fn remove_project(conn: &Connection, workspace_id: i64, project_id: i64) -> Result<bool> {
let mut stmt = conn.prepare(
"DELETE FROM workspace_projects
WHERE workspace_id = ?1 AND project_id = ?2",
)?;
let changes = stmt.execute(params![workspace_id, project_id])?;
Ok(changes > 0)
}
pub fn list_projects(
conn: &Connection,
workspace_id: i64,
) -> Result<Vec<crate::models::Project>> {
let mut stmt = conn.prepare(
"SELECT p.id, p.name, p.path, p.git_hash, p.created_at, p.updated_at, p.is_archived, p.description
FROM projects p
JOIN workspace_projects wp ON p.id = wp.project_id
WHERE wp.workspace_id = ?1
ORDER BY p.name"
)?;
let projects = stmt
.query_map([workspace_id], |row| {
Ok(crate::models::Project {
id: Some(row.get(0)?),
name: row.get(1)?,
path: row.get::<_, String>(2)?.into(),
git_hash: row.get(3)?,
created_at: row.get(4)?,
updated_at: row.get(5)?,
is_archived: row.get(6)?,
description: row.get(7)?,
})
})?
.collect::<Result<Vec<_>, _>>()?;
Ok(projects)
}
pub fn find_by_id(conn: &Connection, workspace_id: i64) -> Result<Option<Workspace>> {
let mut stmt = conn.prepare(
"SELECT id, name, description, path, created_at, updated_at
FROM workspaces WHERE id = ?1",
)?;
let workspace = stmt
.query_row([workspace_id], |row| {
Ok(Workspace {
id: Some(row.get(0)?),
name: row.get(1)?,
description: row.get(2)?,
path: row.get::<_, Option<String>>(3)?.map(|s| s.into()),
created_at: row.get(4)?,
updated_at: row.get(5)?,
})
})
.optional()?;
Ok(workspace)
}
pub fn update(
conn: &Connection,
workspace_id: i64,
name: Option<String>,
description: Option<String>,
path: Option<Option<std::path::PathBuf>>,
) -> Result<bool> {
let mut updates = Vec::new();
let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
if let Some(n) = name {
updates.push("name = ?");
params.push(Box::new(n));
}
if let Some(d) = description {
updates.push("description = ?");
params.push(Box::new(d));
}
if let Some(p) = path {
updates.push("path = ?");
params.push(Box::new(p.map(|p| p.to_string_lossy().to_string())));
}
if updates.is_empty() {
return Ok(false);
}
updates.push("updated_at = CURRENT_TIMESTAMP");
params.push(Box::new(workspace_id));
let sql = format!("UPDATE workspaces SET {} WHERE id = ?", updates.join(", "));
let mut stmt = conn.prepare(&sql)?;
let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();
let changes = stmt.execute(¶m_refs[..])?;
Ok(changes > 0)
}
}
pub struct GitBranchQueries;
impl GitBranchQueries {
pub fn create_or_update(conn: &Connection, branch: &GitBranch) -> Result<i64> {
let existing =
Self::find_by_project_and_name(conn, branch.project_id, &branch.branch_name)?;
if let Some(mut existing) = existing {
existing.update_time(branch.total_time_seconds);
let mut stmt = conn.prepare(
"UPDATE git_branches SET last_seen = CURRENT_TIMESTAMP, total_time_seconds = total_time_seconds + ?1
WHERE project_id = ?2 AND branch_name = ?3"
)?;
stmt.execute(params![
branch.total_time_seconds,
branch.project_id,
branch.branch_name
])?;
existing
.id
.ok_or_else(|| anyhow::anyhow!("Git branch ID missing after update"))
} else {
let mut stmt = conn.prepare(
"INSERT INTO git_branches (project_id, branch_name, total_time_seconds)
VALUES (?1, ?2, ?3)",
)?;
stmt.execute(params![
branch.project_id,
branch.branch_name,
branch.total_time_seconds
])?;
Ok(conn.last_insert_rowid())
}
}
pub fn find_by_project_and_name(
conn: &Connection,
project_id: i64,
branch_name: &str,
) -> Result<Option<GitBranch>> {
let mut stmt = conn.prepare(
"SELECT id, project_id, branch_name, first_seen, last_seen, total_time_seconds
FROM git_branches WHERE project_id = ?1 AND branch_name = ?2",
)?;
let branch = stmt
.query_row(params![project_id, branch_name], |row| {
Ok(GitBranch {
id: Some(row.get(0)?),
project_id: row.get(1)?,
branch_name: row.get(2)?,
first_seen: row.get(3)?,
last_seen: row.get(4)?,
total_time_seconds: row.get(5)?,
})
})
.optional()?;
Ok(branch)
}
pub fn list_by_project(conn: &Connection, project_id: i64) -> Result<Vec<GitBranch>> {
let mut stmt = conn.prepare(
"SELECT id, project_id, branch_name, first_seen, last_seen, total_time_seconds
FROM git_branches WHERE project_id = ?1 ORDER BY total_time_seconds DESC",
)?;
let branches = stmt
.query_map([project_id], |row| {
Ok(GitBranch {
id: Some(row.get(0)?),
project_id: row.get(1)?,
branch_name: row.get(2)?,
first_seen: row.get(3)?,
last_seen: row.get(4)?,
total_time_seconds: row.get(5)?,
})
})?
.collect::<Result<Vec<_>, _>>()?;
Ok(branches)
}
pub fn update(
conn: &Connection,
branch_id: i64,
branch_name: Option<String>,
additional_time_seconds: Option<i64>,
) -> Result<bool> {
let mut updates = Vec::new();
let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
if let Some(name) = branch_name {
updates.push("branch_name = ?");
params.push(Box::new(name));
}
if let Some(time) = additional_time_seconds {
updates.push("total_time_seconds = total_time_seconds + ?");
params.push(Box::new(time));
}
if updates.is_empty() {
return Ok(false);
}
updates.push("last_seen = CURRENT_TIMESTAMP");
params.push(Box::new(branch_id));
let sql = format!(
"UPDATE git_branches SET {} WHERE id = ?",
updates.join(", ")
);
let mut stmt = conn.prepare(&sql)?;
let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();
let changes = stmt.execute(¶m_refs[..])?;
Ok(changes > 0)
}
pub fn delete(conn: &Connection, branch_id: i64) -> Result<bool> {
let mut stmt = conn.prepare("DELETE FROM git_branches WHERE id = ?1")?;
let changes = stmt.execute([branch_id])?;
Ok(changes > 0)
}
pub fn delete_by_project_and_name(
conn: &Connection,
project_id: i64,
branch_name: &str,
) -> Result<bool> {
let mut stmt =
conn.prepare("DELETE FROM git_branches WHERE project_id = ?1 AND branch_name = ?2")?;
let changes = stmt.execute(params![project_id, branch_name])?;
Ok(changes > 0)
}
pub fn find_by_id(conn: &Connection, branch_id: i64) -> Result<Option<GitBranch>> {
let mut stmt = conn.prepare(
"SELECT id, project_id, branch_name, first_seen, last_seen, total_time_seconds
FROM git_branches WHERE id = ?1",
)?;
let branch = stmt
.query_row([branch_id], |row| {
Ok(GitBranch {
id: Some(row.get(0)?),
project_id: row.get(1)?,
branch_name: row.get(2)?,
first_seen: row.get(3)?,
last_seen: row.get(4)?,
total_time_seconds: row.get(5)?,
})
})
.optional()?;
Ok(branch)
}
}
pub struct TimeEstimateQueries;
impl TimeEstimateQueries {
pub fn create(conn: &Connection, estimate: &TimeEstimate) -> Result<i64> {
let mut stmt = conn.prepare(
"INSERT INTO time_estimates (project_id, task_name, estimated_hours, actual_hours, status, due_date, completed_at)
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)"
)?;
stmt.execute(params![
estimate.project_id,
estimate.task_name,
estimate.estimated_hours,
estimate.actual_hours,
estimate.status.to_string(),
estimate.due_date,
estimate.completed_at
])?;
Ok(conn.last_insert_rowid())
}
pub fn list_by_project(conn: &Connection, project_id: i64) -> Result<Vec<TimeEstimate>> {
let mut stmt = conn.prepare(
"SELECT id, project_id, task_name, estimated_hours, actual_hours, status, due_date, completed_at, created_at, updated_at
FROM time_estimates WHERE project_id = ?1 ORDER BY created_at DESC"
)?;
let estimates = stmt
.query_map([project_id], |row| {
Ok(TimeEstimate {
id: Some(row.get(0)?),
project_id: row.get(1)?,
task_name: row.get(2)?,
estimated_hours: row.get(3)?,
actual_hours: row.get(4)?,
status: match row.get::<_, String>(5)?.as_str() {
"planned" => crate::models::EstimateStatus::Planned,
"in_progress" => crate::models::EstimateStatus::InProgress,
"completed" => crate::models::EstimateStatus::Completed,
"cancelled" => crate::models::EstimateStatus::Cancelled,
_ => crate::models::EstimateStatus::Planned,
},
due_date: row.get(6)?,
completed_at: row.get(7)?,
created_at: row.get(8)?,
updated_at: row.get(9)?,
})
})?
.collect::<Result<Vec<_>, _>>()?;
Ok(estimates)
}
pub fn record_actual(conn: &Connection, estimate_id: i64, hours: f64) -> Result<bool> {
let mut stmt = conn.prepare(
"UPDATE time_estimates SET actual_hours = ?1, status = 'completed', completed_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
WHERE id = ?2"
)?;
let changes = stmt.execute(params![hours, estimate_id])?;
Ok(changes > 0)
}
pub fn find_by_id(conn: &Connection, estimate_id: i64) -> Result<Option<TimeEstimate>> {
let mut stmt = conn.prepare(
"SELECT id, project_id, task_name, estimated_hours, actual_hours, status, due_date, completed_at, created_at, updated_at
FROM time_estimates WHERE id = ?1"
)?;
let estimate = stmt
.query_row([estimate_id], |row| {
Ok(TimeEstimate {
id: Some(row.get(0)?),
project_id: row.get(1)?,
task_name: row.get(2)?,
estimated_hours: row.get(3)?,
actual_hours: row.get(4)?,
status: match row.get::<_, String>(5)?.as_str() {
"planned" => crate::models::EstimateStatus::Planned,
"in_progress" => crate::models::EstimateStatus::InProgress,
"completed" => crate::models::EstimateStatus::Completed,
"cancelled" => crate::models::EstimateStatus::Cancelled,
_ => crate::models::EstimateStatus::Planned,
},
due_date: row.get(6)?,
completed_at: row.get(7)?,
created_at: row.get(8)?,
updated_at: row.get(9)?,
})
})
.optional()?;
Ok(estimate)
}
pub fn update(
conn: &Connection,
estimate_id: i64,
task_name: Option<String>,
estimated_hours: Option<f64>,
actual_hours: Option<Option<f64>>,
status: Option<crate::models::EstimateStatus>,
due_date: Option<Option<chrono::NaiveDate>>,
completed_at: Option<Option<chrono::DateTime<chrono::Utc>>>,
) -> Result<bool> {
let mut updates = Vec::new();
let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
if let Some(name) = task_name {
updates.push("task_name = ?");
params.push(Box::new(name));
}
if let Some(est_hours) = estimated_hours {
updates.push("estimated_hours = ?");
params.push(Box::new(est_hours));
}
if let Some(act_hours) = actual_hours {
updates.push("actual_hours = ?");
params.push(Box::new(act_hours));
}
if let Some(stat) = status {
updates.push("status = ?");
params.push(Box::new(stat.to_string()));
}
if let Some(due) = due_date {
updates.push("due_date = ?");
params.push(Box::new(due));
}
if let Some(comp) = completed_at {
updates.push("completed_at = ?");
params.push(Box::new(comp));
}
if updates.is_empty() {
return Ok(false);
}
updates.push("updated_at = CURRENT_TIMESTAMP");
params.push(Box::new(estimate_id));
let sql = format!(
"UPDATE time_estimates SET {} WHERE id = ?",
updates.join(", ")
);
let mut stmt = conn.prepare(&sql)?;
let param_refs: Vec<&dyn rusqlite::ToSql> = params.iter().map(|p| p.as_ref()).collect();
let changes = stmt.execute(¶m_refs[..])?;
Ok(changes > 0)
}
pub fn delete(conn: &Connection, estimate_id: i64) -> Result<bool> {
let mut stmt = conn.prepare("DELETE FROM time_estimates WHERE id = ?1")?;
let changes = stmt.execute([estimate_id])?;
Ok(changes > 0)
}
pub fn set_status(
conn: &Connection,
estimate_id: i64,
status: crate::models::EstimateStatus,
) -> Result<bool> {
let mut stmt = conn.prepare(
"UPDATE time_estimates SET status = ?1, updated_at = CURRENT_TIMESTAMP WHERE id = ?2",
)?;
let changes = stmt.execute(params![status.to_string(), estimate_id])?;
Ok(changes > 0)
}
pub fn list_all(conn: &Connection) -> Result<Vec<TimeEstimate>> {
let mut stmt = conn.prepare(
"SELECT id, project_id, task_name, estimated_hours, actual_hours, status, due_date, completed_at, created_at, updated_at
FROM time_estimates ORDER BY created_at DESC"
)?;
let estimates = stmt
.query_map([], |row| {
Ok(TimeEstimate {
id: Some(row.get(0)?),
project_id: row.get(1)?,
task_name: row.get(2)?,
estimated_hours: row.get(3)?,
actual_hours: row.get(4)?,
status: match row.get::<_, String>(5)?.as_str() {
"planned" => crate::models::EstimateStatus::Planned,
"in_progress" => crate::models::EstimateStatus::InProgress,
"completed" => crate::models::EstimateStatus::Completed,
"cancelled" => crate::models::EstimateStatus::Cancelled,
_ => crate::models::EstimateStatus::Planned,
},
due_date: row.get(6)?,
completed_at: row.get(7)?,
created_at: row.get(8)?,
updated_at: row.get(9)?,
})
})?
.collect::<Result<Vec<_>, _>>()?;
Ok(estimates)
}
}
pub struct InsightQueries;
impl InsightQueries {
pub fn calculate_weekly_summary(
conn: &Connection,
week_start: NaiveDate,
) -> Result<InsightData> {
let week_end = week_start + chrono::Duration::days(6);
let mut stmt = conn.prepare(
"SELECT
COALESCE(SUM(CASE WHEN end_time IS NOT NULL THEN
(julianday(end_time) - julianday(start_time)) * 86400 - COALESCE(paused_duration, 0)
ELSE 0 END), 0) as total_seconds,
COUNT(*) as session_count
FROM sessions
WHERE DATE(start_time) >= ?1 AND DATE(start_time) <= ?2 AND end_time IS NOT NULL "
)?;
let (total_seconds, session_count): (f64, i64) =
stmt.query_row([week_start, week_end], |row| Ok((row.get(0)?, row.get(1)?)))?;
let total_hours = total_seconds / 3600.0;
let avg_session_duration = if session_count > 0 {
total_hours / session_count as f64
} else {
0.0
};
Ok(InsightData {
total_hours,
sessions_count: session_count,
avg_session_duration,
most_active_day: None,
most_active_time: None,
productivity_score: None,
project_breakdown: vec![],
trends: vec![],
})
}
pub fn calculate_monthly_summary(
conn: &Connection,
month_start: NaiveDate,
) -> Result<InsightData> {
let month_end = month_start + chrono::Duration::days(30);
let mut stmt = conn.prepare(
"SELECT
COALESCE(SUM(CASE WHEN end_time IS NOT NULL THEN
(julianday(end_time) - julianday(start_time)) * 86400 - COALESCE(paused_duration, 0)
ELSE 0 END), 0) as total_seconds,
COUNT(*) as session_count
FROM sessions
WHERE DATE(start_time) >= ?1 AND DATE(start_time) <= ?2 AND end_time IS NOT NULL "
)?;
let (total_seconds, session_count): (f64, i64) = stmt
.query_row([month_start, month_end], |row| {
Ok((row.get(0)?, row.get(1)?))
})?;
let total_hours = total_seconds / 3600.0;
let avg_session_duration = if session_count > 0 {
total_hours / session_count as f64
} else {
0.0
};
Ok(InsightData {
total_hours,
sessions_count: session_count,
avg_session_duration,
most_active_day: None,
most_active_time: None,
productivity_score: None,
project_breakdown: vec![],
trends: vec![],
})
}
}