Skip to main content

max / goingson

8.7 KB · 287 lines History Blame Raw
1 //! SQLite implementation of the MonthlyReviewRepository.
2 //!
3 //! Provides monthly goal and reflection persistence.
4
5 use async_trait::async_trait;
6 use chrono::Utc;
7 use sqlx::SqlitePool;
8 use goingson_core::{
9 CoreError, MonthlyGoal, MonthlyGoalId, MonthlyGoalStatus, MonthlyReflection,
10 MonthlyReflectionId, MonthlyReviewRepository, Result, UserId,
11 };
12
13 use crate::utils::{format_datetime, parse_datetime, parse_uuid};
14
15 /// SQLite-backed implementation of [`MonthlyReviewRepository`].
16 pub struct SqliteMonthlyReviewRepository {
17 pool: SqlitePool,
18 }
19
20 impl SqliteMonthlyReviewRepository {
21 #[tracing::instrument(skip_all)]
22 pub fn new(pool: SqlitePool) -> Self {
23 Self { pool }
24 }
25 }
26
27 // ============ Row Types ============
28
29 #[derive(sqlx::FromRow)]
30 struct MonthlyGoalRow {
31 id: String,
32 user_id: String,
33 month: String,
34 text: String,
35 status: String,
36 position: i32,
37 created_at: String,
38 updated_at: String,
39 }
40
41 #[derive(sqlx::FromRow)]
42 struct MonthlyReflectionRow {
43 id: String,
44 user_id: String,
45 month: String,
46 highlight_text: String,
47 change_text: String,
48 completed_at: String,
49 }
50
51 // ============ Conversions ============
52
53 impl TryFrom<MonthlyGoalRow> for MonthlyGoal {
54 type Error = CoreError;
55
56 fn try_from(row: MonthlyGoalRow) -> Result<Self> {
57 Ok(MonthlyGoal {
58 id: parse_uuid(&row.id)?.into(),
59 user_id: parse_uuid(&row.user_id)?.into(),
60 month: row.month,
61 text: row.text,
62 status: row.status.parse()?,
63 position: row.position,
64 created_at: parse_datetime(&row.created_at)?,
65 updated_at: parse_datetime(&row.updated_at)?,
66 })
67 }
68 }
69
70 impl TryFrom<MonthlyReflectionRow> for MonthlyReflection {
71 type Error = CoreError;
72
73 fn try_from(row: MonthlyReflectionRow) -> Result<Self> {
74 Ok(MonthlyReflection {
75 id: parse_uuid(&row.id)?.into(),
76 user_id: parse_uuid(&row.user_id)?.into(),
77 month: row.month,
78 highlight_text: row.highlight_text,
79 change_text: row.change_text,
80 completed_at: parse_datetime(&row.completed_at)?,
81 })
82 }
83 }
84
85 // ============ Repository Implementation ============
86
87 #[async_trait]
88 impl MonthlyReviewRepository for SqliteMonthlyReviewRepository {
89 #[tracing::instrument(skip_all)]
90 async fn list_goals(&self, user_id: UserId, month: &str) -> Result<Vec<MonthlyGoal>> {
91 let user_id_str = user_id.to_string();
92
93 let rows: Vec<MonthlyGoalRow> = sqlx::query_as(
94 "SELECT id, user_id, month, text, status, position, created_at, updated_at
95 FROM monthly_goals
96 WHERE user_id = ? AND month = ?
97 ORDER BY position"
98 )
99 .bind(&user_id_str)
100 .bind(month)
101 .fetch_all(&self.pool)
102 .await
103 .map_err(CoreError::database)?;
104
105 rows.into_iter().map(MonthlyGoal::try_from).collect()
106 }
107
108 #[tracing::instrument(skip_all)]
109 async fn upsert_goal(&self, user_id: UserId, month: &str, text: &str, position: i32) -> Result<MonthlyGoal> {
110 let user_id_str = user_id.to_string();
111 let now = format_datetime(&Utc::now());
112
113 // Check if a goal exists at this position for this month
114 let existing: Option<MonthlyGoalRow> = sqlx::query_as(
115 "SELECT id, user_id, month, text, status, position, created_at, updated_at
116 FROM monthly_goals
117 WHERE user_id = ? AND month = ? AND position = ?"
118 )
119 .bind(&user_id_str)
120 .bind(month)
121 .bind(position)
122 .fetch_optional(&self.pool)
123 .await
124 .map_err(CoreError::database)?;
125
126 if let Some(existing) = existing {
127 let id = existing.id.clone();
128 sqlx::query(
129 "UPDATE monthly_goals SET text = ?, updated_at = ? WHERE id = ?"
130 )
131 .bind(text)
132 .bind(&now)
133 .bind(&id)
134 .execute(&self.pool)
135 .await
136 .map_err(CoreError::database)?;
137
138 let mut goal = MonthlyGoal::try_from(existing)?;
139 goal.text = text.to_string();
140 goal.updated_at = Utc::now();
141 Ok(goal)
142 } else {
143 let id = MonthlyGoalId::new();
144 sqlx::query(
145 "INSERT INTO monthly_goals (id, user_id, month, text, status, position, created_at, updated_at)
146 VALUES (?, ?, ?, ?, 'active', ?, ?, ?)"
147 )
148 .bind(id.to_string())
149 .bind(&user_id_str)
150 .bind(month)
151 .bind(text)
152 .bind(position)
153 .bind(&now)
154 .bind(&now)
155 .execute(&self.pool)
156 .await
157 .map_err(CoreError::database)?;
158
159 let now_dt = Utc::now();
160 Ok(MonthlyGoal {
161 id,
162 user_id,
163 month: month.to_string(),
164 text: text.to_string(),
165 status: MonthlyGoalStatus::Active,
166 position,
167 created_at: now_dt,
168 updated_at: now_dt,
169 })
170 }
171 }
172
173 #[tracing::instrument(skip_all)]
174 async fn update_goal_status(&self, id: MonthlyGoalId, user_id: UserId, status: &MonthlyGoalStatus) -> Result<Option<MonthlyGoal>> {
175 let user_id_str = user_id.to_string();
176 let id_str = id.to_string();
177 let now = format_datetime(&Utc::now());
178
179 let result = sqlx::query(
180 "UPDATE monthly_goals SET status = ?, updated_at = ? WHERE id = ? AND user_id = ?"
181 )
182 .bind(status.as_str())
183 .bind(&now)
184 .bind(&id_str)
185 .bind(&user_id_str)
186 .execute(&self.pool)
187 .await
188 .map_err(CoreError::database)?;
189
190 if result.rows_affected() == 0 {
191 return Ok(None);
192 }
193
194 let row: MonthlyGoalRow = sqlx::query_as(
195 "SELECT id, user_id, month, text, status, position, created_at, updated_at
196 FROM monthly_goals WHERE id = ?"
197 )
198 .bind(&id_str)
199 .fetch_one(&self.pool)
200 .await
201 .map_err(CoreError::database)?;
202
203 Ok(Some(MonthlyGoal::try_from(row)?))
204 }
205
206 #[tracing::instrument(skip_all)]
207 async fn delete_goal(&self, id: MonthlyGoalId, user_id: UserId) -> Result<bool> {
208 let result = sqlx::query(
209 "DELETE FROM monthly_goals WHERE id = ? AND user_id = ?"
210 )
211 .bind(id.to_string())
212 .bind(user_id.to_string())
213 .execute(&self.pool)
214 .await
215 .map_err(CoreError::database)?;
216
217 Ok(result.rows_affected() > 0)
218 }
219
220 #[tracing::instrument(skip_all)]
221 async fn get_reflection(&self, user_id: UserId, month: &str) -> Result<Option<MonthlyReflection>> {
222 let row: Option<MonthlyReflectionRow> = sqlx::query_as(
223 "SELECT id, user_id, month, highlight_text, change_text, completed_at
224 FROM monthly_reflections
225 WHERE user_id = ? AND month = ?"
226 )
227 .bind(user_id.to_string())
228 .bind(month)
229 .fetch_optional(&self.pool)
230 .await
231 .map_err(CoreError::database)?;
232
233 row.map(MonthlyReflection::try_from).transpose()
234 }
235
236 #[tracing::instrument(skip_all)]
237 async fn upsert_reflection(&self, user_id: UserId, month: &str, highlight: &str, change: &str) -> Result<MonthlyReflection> {
238 let user_id_str = user_id.to_string();
239 let now = Utc::now();
240 let now_str = format_datetime(&now);
241
242 let existing = self.get_reflection(user_id, month).await?;
243
244 let id = if let Some(existing) = existing {
245 sqlx::query(
246 "UPDATE monthly_reflections SET highlight_text = ?, change_text = ?, completed_at = ?
247 WHERE id = ?"
248 )
249 .bind(highlight)
250 .bind(change)
251 .bind(&now_str)
252 .bind(existing.id.to_string())
253 .execute(&self.pool)
254 .await
255 .map_err(CoreError::database)?;
256
257 existing.id
258 } else {
259 let id = MonthlyReflectionId::new();
260 sqlx::query(
261 "INSERT INTO monthly_reflections (id, user_id, month, highlight_text, change_text, completed_at)
262 VALUES (?, ?, ?, ?, ?, ?)"
263 )
264 .bind(id.to_string())
265 .bind(&user_id_str)
266 .bind(month)
267 .bind(highlight)
268 .bind(change)
269 .bind(&now_str)
270 .execute(&self.pool)
271 .await
272 .map_err(CoreError::database)?;
273
274 id
275 };
276
277 Ok(MonthlyReflection {
278 id,
279 user_id,
280 month: month.to_string(),
281 highlight_text: highlight.to_string(),
282 change_text: change.to_string(),
283 completed_at: now,
284 })
285 }
286 }
287