#![allow(dead_code)]
use anyhow::{Context, Result};
use chrono::Utc;
use rusqlite::{params, Connection};
use serde::{Deserialize, Serialize};
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct Advice {
pub id: String,
pub user_id: String,
pub project: String,
pub text: String,
pub severity: String,
pub paths: Vec<String>,
pub verifiable: bool,
pub state: String,
pub confirmed_at: Option<String>,
pub confirmed_by: Option<String>,
pub metadata: serde_json::Value,
pub created_at: String,
pub updated_at: String,
pub synced_at: Option<String>,
}
#[derive(Debug, Clone, Deserialize)]
pub struct AdviceInput {
pub user_id: String,
pub project: String,
pub text: String,
pub severity: String,
#[serde(default)]
pub paths: Vec<String>,
#[serde(default)]
pub verifiable: bool,
#[serde(default)]
pub metadata: Option<serde_json::Value>,
}
pub fn insert(conn: &Connection, input: AdviceInput) -> Result<Advice> {
let id = super::uuid_like();
let now = Utc::now().to_rfc3339();
let paths_json = serde_json::to_string(&input.paths).unwrap_or_else(|_| "[]".into());
let meta_json = input
.metadata
.map(|m| m.to_string())
.unwrap_or_else(|| "{}".into());
conn.execute(
r#"INSERT INTO advice
(id, user_id, project, text, severity, paths, verifiable, state,
metadata, created_at, updated_at)
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, 'pending', ?8, ?9, ?9)"#,
params![
id,
input.user_id,
input.project,
input.text,
input.severity,
paths_json,
input.verifiable as i32,
meta_json,
now,
],
)
.context("insert advice")?;
get(conn, &input.user_id, &id)?.context("advice missing after insert")
}
pub fn get(conn: &Connection, user_id: &str, id: &str) -> Result<Option<Advice>> {
let mut stmt = conn.prepare(
r#"SELECT id, user_id, project, text, severity, paths, verifiable, state,
confirmed_at, confirmed_by, metadata, created_at, updated_at, synced_at
FROM advice
WHERE user_id = ?1 AND id = ?2"#,
)?;
Ok(stmt.query_row(params![user_id, id], row_to_advice).ok())
}
pub fn list_pending(
conn: &Connection,
user_id: &str,
project: Option<&str>,
limit: usize,
) -> Result<Vec<Advice>> {
let rows: Vec<Advice> = if let Some(p) = project {
let mut stmt = conn.prepare(
r#"SELECT id, user_id, project, text, severity, paths, verifiable, state,
confirmed_at, confirmed_by, metadata, created_at, updated_at, synced_at
FROM advice
WHERE user_id = ?1 AND project = ?2 AND confirmed_at IS NULL
ORDER BY created_at DESC LIMIT ?3"#,
)?;
let rs: Vec<Advice> = stmt
.query_map(params![user_id, p, limit as i64], row_to_advice)?
.filter_map(|r| r.ok())
.collect();
rs
} else {
let mut stmt = conn.prepare(
r#"SELECT id, user_id, project, text, severity, paths, verifiable, state,
confirmed_at, confirmed_by, metadata, created_at, updated_at, synced_at
FROM advice
WHERE user_id = ?1 AND confirmed_at IS NULL
ORDER BY created_at DESC LIMIT ?2"#,
)?;
let rs: Vec<Advice> = stmt
.query_map(params![user_id, limit as i64], row_to_advice)?
.filter_map(|r| r.ok())
.collect();
rs
};
Ok(rows)
}
pub fn confirm(conn: &Connection, user_id: &str, id: &str, by: &str) -> Result<bool> {
let now = Utc::now().to_rfc3339();
let n = conn.execute(
r#"UPDATE advice
SET confirmed_at = ?1, confirmed_by = ?2, updated_at = ?1
WHERE user_id = ?3 AND id = ?4 AND confirmed_at IS NULL"#,
params![now, by, user_id, id],
)?;
Ok(n > 0)
}
pub fn unconfirm(conn: &Connection, user_id: &str, id: &str) -> Result<bool> {
let now = Utc::now().to_rfc3339();
let n = conn.execute(
r#"UPDATE advice
SET confirmed_at = NULL, confirmed_by = NULL, updated_at = ?1
WHERE user_id = ?2 AND id = ?3"#,
params![now, user_id, id],
)?;
Ok(n > 0)
}
pub fn set_state(conn: &Connection, user_id: &str, id: &str, new_state: &str) -> Result<bool> {
let now = Utc::now().to_rfc3339();
let n = conn.execute(
r#"UPDATE advice
SET state = ?1, updated_at = ?2
WHERE user_id = ?3 AND id = ?4"#,
params![new_state, now, user_id, id],
)?;
Ok(n > 0)
}
pub fn list_unsynced(conn: &Connection, limit: usize) -> Result<Vec<Advice>> {
let mut stmt = conn.prepare(
r#"SELECT id, user_id, project, text, severity, paths, verifiable, state,
confirmed_at, confirmed_by, metadata, created_at, updated_at, synced_at
FROM advice
WHERE synced_at IS NULL OR updated_at > synced_at
ORDER BY updated_at ASC
LIMIT ?1"#,
)?;
let rows: Vec<Advice> = stmt
.query_map(params![limit as i64], row_to_advice)?
.filter_map(|r| r.ok())
.collect();
Ok(rows)
}
pub fn mark_synced(conn: &Connection, ids: &[&str], when: &str) -> Result<()> {
if ids.is_empty() {
return Ok(());
}
let placeholders = ids.iter().map(|_| "?").collect::<Vec<_>>().join(",");
let sql = format!(
"UPDATE advice SET synced_at = ? WHERE id IN ({})",
placeholders
);
let mut stmt = conn.prepare(&sql)?;
let mut binds: Vec<rusqlite::types::Value> = Vec::with_capacity(ids.len() + 1);
binds.push(when.to_string().into());
for id in ids {
binds.push((*id).to_string().into());
}
stmt.execute(rusqlite::params_from_iter(binds.iter()))?;
Ok(())
}
fn row_to_advice(row: &rusqlite::Row<'_>) -> rusqlite::Result<Advice> {
let paths_str: String = row.get(5)?;
let paths: Vec<String> = serde_json::from_str(&paths_str).unwrap_or_default();
let verifiable: i32 = row.get(6)?;
let meta_str: String = row.get(10)?;
let metadata = serde_json::from_str(&meta_str).unwrap_or_else(|_| serde_json::json!({}));
Ok(Advice {
id: row.get(0)?,
user_id: row.get(1)?,
project: row.get(2)?,
text: row.get(3)?,
severity: row.get(4)?,
paths,
verifiable: verifiable != 0,
state: row.get(7)?,
confirmed_at: row.get(8)?,
confirmed_by: row.get(9)?,
metadata,
created_at: row.get(11)?,
updated_at: row.get(12)?,
synced_at: row.get(13)?,
})
}