reminders.rs (19117B)
1 use radroots_app_view::{ 2 FarmId, ReminderDeadlineProjection, ReminderDeliveryState, ReminderFeedProjection, 3 ReminderKind, ReminderLogEntryProjection, ReminderLogProjection, ReminderSurface, 4 ReminderUrgency, 5 }; 6 use rusqlite::{Connection, params}; 7 use std::str::FromStr; 8 use uuid::Uuid; 9 10 use crate::AppSqliteError; 11 12 pub struct AppRemindersRepository<'a> { 13 connection: &'a Connection, 14 } 15 16 impl<'a> AppRemindersRepository<'a> { 17 pub const fn new(connection: &'a Connection) -> Self { 18 Self { connection } 19 } 20 21 pub fn load_reminder_schedule( 22 &self, 23 account_id: &str, 24 farm_id: FarmId, 25 ) -> Result<ReminderFeedProjection, AppSqliteError> { 26 let mut statement = self 27 .connection 28 .prepare( 29 "SELECT 30 reminder_id, 31 order_id, 32 fulfillment_window_id, 33 reminder_kind, 34 reminder_surface, 35 reminder_urgency, 36 title, 37 detail, 38 deadline_at, 39 action_label, 40 delivery_state 41 FROM reminder_schedules 42 WHERE account_id = ?1 AND farm_id = ?2 43 ORDER BY deadline_at ASC, reminder_id ASC", 44 ) 45 .map_err(|source| AppSqliteError::Query { 46 operation: "prepare reminder schedule query", 47 source, 48 })?; 49 let rows = statement 50 .query_map(params![account_id, farm_id.to_string()], |row| { 51 Ok(( 52 row.get::<_, String>(0)?, 53 row.get::<_, Option<String>>(1)?, 54 row.get::<_, Option<String>>(2)?, 55 row.get::<_, String>(3)?, 56 row.get::<_, String>(4)?, 57 row.get::<_, String>(5)?, 58 row.get::<_, String>(6)?, 59 row.get::<_, String>(7)?, 60 row.get::<_, String>(8)?, 61 row.get::<_, Option<String>>(9)?, 62 row.get::<_, String>(10)?, 63 )) 64 }) 65 .map_err(|source| AppSqliteError::Query { 66 operation: "query reminder schedule", 67 source, 68 })?; 69 70 let items = rows 71 .map(|row| { 72 let ( 73 reminder_id, 74 order_id, 75 fulfillment_window_id, 76 reminder_kind, 77 reminder_surface, 78 reminder_urgency, 79 title, 80 detail, 81 deadline_at, 82 action_label, 83 delivery_state, 84 ) = row.map_err(|source| AppSqliteError::Query { 85 operation: "read reminder schedule row", 86 source, 87 })?; 88 89 Ok(ReminderDeadlineProjection { 90 reminder_id: parse_typed_id("reminder_schedules.reminder_id", reminder_id)?, 91 farm_id, 92 order_id: parse_optional_typed_id("reminder_schedules.order_id", order_id)?, 93 fulfillment_window_id: parse_optional_typed_id( 94 "reminder_schedules.fulfillment_window_id", 95 fulfillment_window_id, 96 )?, 97 kind: parse_reminder_kind(reminder_kind)?, 98 surface: parse_reminder_surface(reminder_surface)?, 99 urgency: parse_reminder_urgency(reminder_urgency)?, 100 title, 101 detail, 102 deadline_at, 103 action_label, 104 delivery_state: parse_reminder_delivery_state(delivery_state)?, 105 }) 106 }) 107 .collect::<Result<Vec<_>, AppSqliteError>>()?; 108 109 Ok(ReminderFeedProjection { items }) 110 } 111 112 pub fn replace_reminder_schedule( 113 &self, 114 account_id: &str, 115 farm_id: FarmId, 116 projection: &ReminderFeedProjection, 117 ) -> Result<(), AppSqliteError> { 118 self.apply_reminder_schedule_update(account_id, farm_id, projection, &[]) 119 } 120 121 pub fn apply_reminder_schedule_update( 122 &self, 123 account_id: &str, 124 farm_id: FarmId, 125 projection: &ReminderFeedProjection, 126 log_entries: &[ReminderLogEntryProjection], 127 ) -> Result<(), AppSqliteError> { 128 let transaction = 129 self.connection 130 .unchecked_transaction() 131 .map_err(|source| AppSqliteError::Query { 132 operation: "begin reminder schedule replacement", 133 source, 134 })?; 135 136 transaction 137 .execute( 138 "DELETE FROM reminder_schedules WHERE account_id = ?1 AND farm_id = ?2", 139 params![account_id, farm_id.to_string()], 140 ) 141 .map_err(|source| AppSqliteError::Query { 142 operation: "clear reminder schedule", 143 source, 144 })?; 145 146 { 147 let mut statement = transaction 148 .prepare( 149 "INSERT INTO reminder_schedules ( 150 reminder_id, 151 account_id, 152 farm_id, 153 order_id, 154 fulfillment_window_id, 155 reminder_kind, 156 reminder_surface, 157 reminder_urgency, 158 title, 159 detail, 160 deadline_at, 161 action_label, 162 delivery_state 163 ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13)", 164 ) 165 .map_err(|source| AppSqliteError::Query { 166 operation: "prepare reminder schedule insert", 167 source, 168 })?; 169 170 for reminder in &projection.items { 171 statement 172 .execute(params![ 173 reminder.reminder_id.to_string(), 174 account_id, 175 reminder.farm_id.to_string(), 176 reminder.order_id.map(|value| value.to_string()), 177 reminder 178 .fulfillment_window_id 179 .map(|value| value.to_string()), 180 reminder.kind.storage_key(), 181 reminder.surface.storage_key(), 182 reminder.urgency.storage_key(), 183 reminder.title, 184 reminder.detail, 185 reminder.deadline_at, 186 reminder.action_label, 187 reminder.delivery_state.storage_key(), 188 ]) 189 .map_err(|source| AppSqliteError::Query { 190 operation: "insert reminder schedule row", 191 source, 192 })?; 193 } 194 } 195 196 for entry in log_entries { 197 let log_entry_id = Uuid::now_v7().to_string(); 198 199 transaction 200 .execute( 201 "INSERT INTO reminder_log_entries ( 202 log_entry_id, 203 account_id, 204 farm_id, 205 reminder_id, 206 reminder_kind, 207 title, 208 recorded_at, 209 delivery_state, 210 detail 211 ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)", 212 params![ 213 log_entry_id, 214 account_id, 215 farm_id.to_string(), 216 entry.reminder_id.to_string(), 217 entry.kind.storage_key(), 218 entry.title, 219 entry.recorded_at, 220 entry.delivery_state.storage_key(), 221 entry.detail, 222 ], 223 ) 224 .map_err(|source| AppSqliteError::Query { 225 operation: "record reminder log entry", 226 source, 227 })?; 228 } 229 230 transaction 231 .commit() 232 .map_err(|source| AppSqliteError::Query { 233 operation: "commit reminder schedule replacement", 234 source, 235 })?; 236 237 Ok(()) 238 } 239 240 pub fn record_reminder_log_entry( 241 &self, 242 account_id: &str, 243 farm_id: FarmId, 244 entry: &ReminderLogEntryProjection, 245 ) -> Result<String, AppSqliteError> { 246 let log_entry_id = Uuid::now_v7().to_string(); 247 248 self.connection 249 .execute( 250 "INSERT INTO reminder_log_entries ( 251 log_entry_id, 252 account_id, 253 farm_id, 254 reminder_id, 255 reminder_kind, 256 title, 257 recorded_at, 258 delivery_state, 259 detail 260 ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)", 261 params![ 262 log_entry_id, 263 account_id, 264 farm_id.to_string(), 265 entry.reminder_id.to_string(), 266 entry.kind.storage_key(), 267 entry.title, 268 entry.recorded_at, 269 entry.delivery_state.storage_key(), 270 entry.detail, 271 ], 272 ) 273 .map_err(|source| AppSqliteError::Query { 274 operation: "record reminder log entry", 275 source, 276 })?; 277 278 Ok(log_entry_id) 279 } 280 281 pub fn load_reminder_log( 282 &self, 283 account_id: &str, 284 farm_id: FarmId, 285 limit: usize, 286 ) -> Result<ReminderLogProjection, AppSqliteError> { 287 let mut statement = self 288 .connection 289 .prepare( 290 "SELECT 291 reminder_id, 292 reminder_kind, 293 title, 294 recorded_at, 295 delivery_state, 296 detail 297 FROM reminder_log_entries 298 WHERE account_id = ?1 AND farm_id = ?2 299 ORDER BY recorded_at DESC, log_entry_id DESC 300 LIMIT ?3", 301 ) 302 .map_err(|source| AppSqliteError::Query { 303 operation: "prepare reminder log query", 304 source, 305 })?; 306 let rows = statement 307 .query_map( 308 params![account_id, farm_id.to_string(), limit as i64], 309 |row| { 310 Ok(( 311 row.get::<_, String>(0)?, 312 row.get::<_, String>(1)?, 313 row.get::<_, String>(2)?, 314 row.get::<_, String>(3)?, 315 row.get::<_, String>(4)?, 316 row.get::<_, Option<String>>(5)?, 317 )) 318 }, 319 ) 320 .map_err(|source| AppSqliteError::Query { 321 operation: "query reminder log", 322 source, 323 })?; 324 325 let entries = rows 326 .map(|row| { 327 let (reminder_id, reminder_kind, title, recorded_at, delivery_state, detail) = row 328 .map_err(|source| AppSqliteError::Query { 329 operation: "read reminder log row", 330 source, 331 })?; 332 333 Ok(ReminderLogEntryProjection { 334 reminder_id: parse_typed_id("reminder_log_entries.reminder_id", reminder_id)?, 335 kind: parse_reminder_kind(reminder_kind)?, 336 title, 337 recorded_at, 338 delivery_state: parse_reminder_delivery_state(delivery_state)?, 339 detail, 340 }) 341 }) 342 .collect::<Result<Vec<_>, AppSqliteError>>()?; 343 344 Ok(ReminderLogProjection { entries }) 345 } 346 } 347 348 fn parse_reminder_kind(value: String) -> Result<ReminderKind, AppSqliteError> { 349 match value.as_str() { 350 "fulfillment_window" => Ok(ReminderKind::FulfillmentWindow), 351 "order_action" => Ok(ReminderKind::OrderAction), 352 "sync_impact" => Ok(ReminderKind::SyncImpact), 353 _ => Err(AppSqliteError::DecodeEnum { 354 field: "reminder_schedules.reminder_kind", 355 value, 356 }), 357 } 358 } 359 360 fn parse_reminder_surface(value: String) -> Result<ReminderSurface, AppSqliteError> { 361 match value.as_str() { 362 "today" => Ok(ReminderSurface::Today), 363 "orders" => Ok(ReminderSurface::Orders), 364 "pack_day" => Ok(ReminderSurface::PackDay), 365 _ => Err(AppSqliteError::DecodeEnum { 366 field: "reminder_schedules.reminder_surface", 367 value, 368 }), 369 } 370 } 371 372 fn parse_reminder_urgency(value: String) -> Result<ReminderUrgency, AppSqliteError> { 373 match value.as_str() { 374 "upcoming" => Ok(ReminderUrgency::Upcoming), 375 "due_soon" => Ok(ReminderUrgency::DueSoon), 376 "overdue" => Ok(ReminderUrgency::Overdue), 377 "blocking" => Ok(ReminderUrgency::Blocking), 378 _ => Err(AppSqliteError::DecodeEnum { 379 field: "reminder_schedules.reminder_urgency", 380 value, 381 }), 382 } 383 } 384 385 fn parse_reminder_delivery_state(value: String) -> Result<ReminderDeliveryState, AppSqliteError> { 386 match value.as_str() { 387 "scheduled" => Ok(ReminderDeliveryState::Scheduled), 388 "presented" => Ok(ReminderDeliveryState::Presented), 389 "acknowledged" => Ok(ReminderDeliveryState::Acknowledged), 390 "resolved" => Ok(ReminderDeliveryState::Resolved), 391 _ => Err(AppSqliteError::DecodeEnum { 392 field: "reminder delivery_state", 393 value, 394 }), 395 } 396 } 397 398 fn parse_typed_id<T>(field: &'static str, value: String) -> Result<T, AppSqliteError> 399 where 400 T: FromStr<Err = uuid::Error>, 401 { 402 T::from_str(&value).map_err(|_| AppSqliteError::DecodeId { field, value }) 403 } 404 405 fn parse_optional_typed_id<T>( 406 field: &'static str, 407 value: Option<String>, 408 ) -> Result<Option<T>, AppSqliteError> 409 where 410 T: FromStr<Err = uuid::Error>, 411 { 412 value.map(|value| parse_typed_id(field, value)).transpose() 413 } 414 415 #[cfg(test)] 416 mod tests { 417 use super::AppRemindersRepository; 418 use crate::{AppSqliteStore, DatabaseTarget}; 419 use radroots_app_view::{ 420 FarmId, OrderId, ReminderDeadlineProjection, ReminderDeliveryState, ReminderFeedProjection, 421 ReminderId, ReminderKind, ReminderLogEntryProjection, ReminderSurface, ReminderUrgency, 422 }; 423 424 #[test] 425 fn reminder_schedule_round_trips_and_is_account_scoped() { 426 let store = AppSqliteStore::open(DatabaseTarget::InMemory).expect("store should open"); 427 let repository = AppRemindersRepository::new(store.connection()); 428 let farm_id = FarmId::new(); 429 let other_farm_id = FarmId::new(); 430 let order_id = OrderId::new(); 431 let reminder = ReminderDeadlineProjection { 432 reminder_id: ReminderId::new(), 433 farm_id, 434 order_id: Some(order_id), 435 fulfillment_window_id: None, 436 kind: ReminderKind::OrderAction, 437 surface: ReminderSurface::Orders, 438 urgency: ReminderUrgency::DueSoon, 439 title: "Pack CSA order".to_owned(), 440 detail: "Order R-1001 still needs packing.".to_owned(), 441 deadline_at: "2026-04-25T14:00:00Z".to_owned(), 442 action_label: Some("Review order".to_owned()), 443 delivery_state: ReminderDeliveryState::Scheduled, 444 }; 445 446 repository 447 .replace_reminder_schedule( 448 "acct_farmer", 449 farm_id, 450 &ReminderFeedProjection { 451 items: vec![reminder.clone()], 452 }, 453 ) 454 .expect("schedule should save"); 455 repository 456 .replace_reminder_schedule( 457 "acct_other", 458 other_farm_id, 459 &ReminderFeedProjection { 460 items: vec![ReminderDeadlineProjection { 461 farm_id: other_farm_id, 462 ..reminder.clone() 463 }], 464 }, 465 ) 466 .expect("other schedule should save"); 467 468 let loaded = repository 469 .load_reminder_schedule("acct_farmer", farm_id) 470 .expect("schedule should load"); 471 let other = repository 472 .load_reminder_schedule("acct_other", other_farm_id) 473 .expect("other schedule should load"); 474 475 assert_eq!(loaded.items, vec![reminder]); 476 assert_eq!(other.items.len(), 1); 477 assert_eq!(other.items[0].reminder_id, loaded.items[0].reminder_id); 478 assert_eq!(other.items[0].farm_id, other_farm_id); 479 } 480 481 #[test] 482 fn reminder_log_records_and_loads_recent_entries() { 483 let store = AppSqliteStore::open(DatabaseTarget::InMemory).expect("store should open"); 484 let repository = AppRemindersRepository::new(store.connection()); 485 let farm_id = FarmId::new(); 486 let first_reminder_id = ReminderId::new(); 487 let second_reminder_id = ReminderId::new(); 488 489 repository 490 .record_reminder_log_entry( 491 "acct_farmer", 492 farm_id, 493 &ReminderLogEntryProjection { 494 reminder_id: first_reminder_id, 495 kind: ReminderKind::FulfillmentWindow, 496 title: "Window closes today".to_owned(), 497 recorded_at: "2026-04-25T12:00:00Z".to_owned(), 498 delivery_state: ReminderDeliveryState::Presented, 499 detail: None, 500 }, 501 ) 502 .expect("first log entry should save"); 503 repository 504 .record_reminder_log_entry( 505 "acct_farmer", 506 farm_id, 507 &ReminderLogEntryProjection { 508 reminder_id: second_reminder_id, 509 kind: ReminderKind::SyncImpact, 510 title: "Sync attention needed".to_owned(), 511 recorded_at: "2026-04-25T13:00:00Z".to_owned(), 512 delivery_state: ReminderDeliveryState::Acknowledged, 513 detail: Some("A local sync issue needs review.".to_owned()), 514 }, 515 ) 516 .expect("second log entry should save"); 517 518 let loaded = repository 519 .load_reminder_log("acct_farmer", farm_id, 1) 520 .expect("log should load"); 521 522 assert_eq!(loaded.entries.len(), 1); 523 assert_eq!(loaded.entries[0].reminder_id, second_reminder_id); 524 assert_eq!( 525 loaded.entries[0].delivery_state, 526 ReminderDeliveryState::Acknowledged 527 ); 528 } 529 }