Skip to main content

max / makenotwork

28.0 KB · 837 lines History Blame Raw
1 //! SQLite data layer for tickets with sync support.
2
3 use std::fmt::Write as _;
4
5 use chrono::{DateTime, Utc};
6 use color_eyre::eyre::{Result, WrapErr, eyre};
7 use rusqlite::{Connection, Row, params};
8
9 use crate::types::{Channel, NewTicket, Priority, Status, Ticket};
10
11 /// Resolve (creating if absent) the per-user data directory that holds the
12 /// database and the shared-token file. Shared by `open_db` and the API's token
13 /// resolution so both agree on one location.
14 pub(crate) fn data_dir() -> Result<std::path::PathBuf> {
15 let dirs = directories::ProjectDirs::from("work", "makenot", "wam")
16 .ok_or_else(|| eyre!("cannot determine data directory"))?;
17 let dir = dirs.data_dir().to_path_buf();
18 std::fs::create_dir_all(&dir)
19 .wrap_err_with(|| format!("create data dir: {}", dir.display()))?;
20 Ok(dir)
21 }
22
23 /// Open (or create) the WAM database and run migrations.
24 pub(crate) fn open_db() -> Result<Connection> {
25 let db_path = data_dir()?.join("wam.db");
26 let conn = Connection::open(&db_path)
27 .wrap_err_with(|| format!("open database: {}", db_path.display()))?;
28
29 conn.execute_batch("PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;")?;
30 migrate(&conn)?;
31 Ok(conn)
32 }
33
34 /// Open an in-memory database for testing.
35 #[cfg(test)]
36 pub(crate) fn open_memory() -> Result<Connection> {
37 let conn = Connection::open_in_memory()?;
38 migrate(&conn)?;
39 Ok(conn)
40 }
41
42 fn migrate(conn: &Connection) -> Result<()> {
43 conn.execute_batch(
44 "CREATE TABLE IF NOT EXISTS tickets (
45 id TEXT PRIMARY KEY,
46 title TEXT NOT NULL,
47 body TEXT,
48 pain INTEGER NOT NULL DEFAULT 3,
49 scale INTEGER NOT NULL DEFAULT 3,
50 status TEXT NOT NULL DEFAULT 'open',
51 channel TEXT NOT NULL DEFAULT 'system',
52 node_id TEXT NOT NULL DEFAULT '',
53 source TEXT,
54 source_ref TEXT,
55 created_at TEXT NOT NULL,
56 updated_at TEXT NOT NULL,
57 resolved_at TEXT
58 );
59 CREATE TABLE IF NOT EXISTS meta (
60 key TEXT PRIMARY KEY,
61 value TEXT NOT NULL
62 );
63 CREATE TABLE IF NOT EXISTS sync_cursors (
64 peer_url TEXT PRIMARY KEY,
65 last_synced TEXT NOT NULL
66 );",
67 )?;
68
69 // Add channel/node_id columns if migrating from v0.1 schema
70 let has_channel: bool = conn.prepare("SELECT channel FROM tickets LIMIT 0").is_ok();
71 if !has_channel {
72 conn.execute_batch(
73 "ALTER TABLE tickets ADD COLUMN channel TEXT NOT NULL DEFAULT 'system';
74 ALTER TABLE tickets ADD COLUMN node_id TEXT NOT NULL DEFAULT '';",
75 )?;
76 }
77
78 // Migrate the stored `priority` enum to the painhours `pain`/`scale`
79 // factors. The old enum only expressed severity, so it seeds `pain`
80 // (low->2, medium->3, high->4, critical->5) and `scale` defaults to 3.
81 let has_pain: bool = conn.prepare("SELECT pain FROM tickets LIMIT 0").is_ok();
82 if !has_pain {
83 conn.execute_batch(
84 "ALTER TABLE tickets ADD COLUMN pain INTEGER NOT NULL DEFAULT 3;
85 ALTER TABLE tickets ADD COLUMN scale INTEGER NOT NULL DEFAULT 3;",
86 )?;
87 // Backfill pain from the legacy priority column if it still exists.
88 let has_priority: bool = conn.prepare("SELECT priority FROM tickets LIMIT 0").is_ok();
89 if has_priority {
90 conn.execute_batch(
91 "UPDATE tickets SET pain = CASE priority
92 WHEN 'critical' THEN 5
93 WHEN 'high' THEN 4
94 WHEN 'medium' THEN 3
95 WHEN 'low' THEN 2
96 ELSE 3
97 END;",
98 )?;
99 }
100 }
101
102 Ok(())
103 }
104
105 /// Get or create this node's persistent identity.
106 pub(crate) fn get_or_create_node_id(conn: &Connection) -> Result<String> {
107 let existing: Option<String> = conn
108 .query_row("SELECT value FROM meta WHERE key = 'node_id'", [], |row| {
109 row.get(0)
110 })
111 .ok();
112
113 if let Some(id) = existing {
114 return Ok(id);
115 }
116
117 let id = uuid::Uuid::new_v4().to_string();
118 conn.execute(
119 "INSERT INTO meta (key, value) VALUES ('node_id', ?1)",
120 params![id],
121 )?;
122 Ok(id)
123 }
124
125 fn row_to_ticket(row: &Row) -> rusqlite::Result<Ticket> {
126 let status_str: String = row.get("status")?;
127 let channel_str: String = row.get("channel")?;
128 let created_str: String = row.get("created_at")?;
129 let updated_str: String = row.get("updated_at")?;
130 let resolved_str: Option<String> = row.get("resolved_at")?;
131
132 Ok(Ticket {
133 id: row.get("id")?,
134 title: row.get("title")?,
135 body: row.get("body")?,
136 pain: row.get::<_, i64>("pain")?.clamp(1, 5) as u8,
137 scale: row.get::<_, i64>("scale")?.clamp(1, 5) as u8,
138 status: status_str.parse().unwrap_or(Status::Open),
139 channel: channel_str.parse().unwrap_or(Channel::System),
140 node_id: row.get("node_id")?,
141 source: row.get("source")?,
142 source_ref: row.get("source_ref")?,
143 created_at: DateTime::parse_from_rfc3339(&created_str)
144 .map_or_else(|_| Utc::now(), |dt| dt.with_timezone(&Utc)),
145 updated_at: DateTime::parse_from_rfc3339(&updated_str)
146 .map_or_else(|_| Utc::now(), |dt| dt.with_timezone(&Utc)),
147 resolved_at: resolved_str.and_then(|s| {
148 DateTime::parse_from_rfc3339(&s)
149 .map(|dt| dt.with_timezone(&Utc))
150 .ok()
151 }),
152 })
153 }
154
155 /// Create a new ticket. Returns the created ticket.
156 pub(crate) fn create_ticket(conn: &Connection, new: &NewTicket, node_id: &str) -> Result<Ticket> {
157 let id = uuid::Uuid::new_v4().to_string();
158 let now = Utc::now().to_rfc3339();
159
160 conn.execute(
161 "INSERT INTO tickets (id, title, body, pain, scale, status, channel, node_id, source, source_ref, created_at, updated_at)
162 VALUES (?1, ?2, ?3, ?4, ?5, 'open', ?6, ?7, ?8, ?9, ?10, ?10)",
163 params![
164 id,
165 new.title,
166 new.body,
167 new.pain.clamp(1, 5),
168 new.scale.clamp(1, 5),
169 new.channel.to_string(),
170 node_id,
171 new.source,
172 new.source_ref,
173 now,
174 ],
175 )?;
176
177 get_ticket(conn, &id)
178 }
179
180 /// Get a ticket by exact ID or unique prefix match.
181 pub(crate) fn get_ticket(conn: &Connection, id_prefix: &str) -> Result<Ticket> {
182 let mut stmt = conn.prepare("SELECT * FROM tickets WHERE id LIKE ?1 || '%'")?;
183 let tickets: Vec<Ticket> = stmt
184 .query_map(params![id_prefix], row_to_ticket)?
185 .collect::<rusqlite::Result<Vec<_>>>()?;
186
187 match tickets.len() {
188 0 => Err(eyre!("no ticket matching '{id_prefix}'")),
189 1 => Ok(tickets.into_iter().next().unwrap()),
190 n => Err(eyre!("ambiguous prefix '{id_prefix}' matches {n} tickets")),
191 }
192 }
193
194 /// Filter criteria for listing tickets.
195 #[derive(Default)]
196 pub(crate) struct ListFilter<'a> {
197 pub status: Option<Status>,
198 /// Filter by painhours color band (see [`Priority`]).
199 pub priority: Option<Priority>,
200 pub channel: Option<Channel>,
201 pub source: Option<&'a str>,
202 pub search: Option<&'a str>,
203 }
204
205 /// List tickets with optional filters, ordered by painhours score (desc) then
206 /// creation date (desc).
207 ///
208 /// The painhours score depends on the current time and the resolve-freeze rule,
209 /// so it can't be expressed in SQL's `ORDER BY`; the band filter and ranking are
210 /// applied in Rust after the row-level filters run in the query.
211 pub(crate) fn list_tickets(conn: &Connection, filter: &ListFilter) -> Result<Vec<Ticket>> {
212 let mut sql = String::from("SELECT * FROM tickets WHERE 1=1");
213 let mut bind_values: Vec<String> = Vec::new();
214
215 if let Some(status) = filter.status {
216 bind_values.push(status.to_string());
217 let _ = write!(sql, " AND status = ?{}", bind_values.len());
218 }
219 if let Some(channel) = filter.channel {
220 bind_values.push(channel.to_string());
221 let _ = write!(sql, " AND channel = ?{}", bind_values.len());
222 }
223 if let Some(source) = filter.source {
224 bind_values.push(source.to_string());
225 let _ = write!(sql, " AND source = ?{}", bind_values.len());
226 }
227 if let Some(search) = filter.search {
228 bind_values.push(format!("%{search}%"));
229 let _ = write!(sql, " AND title LIKE ?{}", bind_values.len());
230 }
231
232 let mut stmt = conn.prepare(&sql)?;
233 let params_refs: Vec<&dyn rusqlite::types::ToSql> = bind_values
234 .iter()
235 .map(|v| v as &dyn rusqlite::types::ToSql)
236 .collect();
237 let mut tickets = stmt
238 .query_map(params_refs.as_slice(), row_to_ticket)?
239 .collect::<rusqlite::Result<Vec<_>>>()?;
240
241 if let Some(band) = filter.priority {
242 tickets.retain(|t| t.band() == band);
243 }
244
245 // Highest painhours first, ties broken by newest.
246 tickets.sort_by(|a, b| {
247 b.painhours()
248 .cmp(&a.painhours())
249 .then_with(|| b.created_at.cmp(&a.created_at))
250 });
251
252 Ok(tickets)
253 }
254
255 /// Update a ticket's status. Sets resolved_at when moving to Resolved.
256 pub(crate) fn update_status(conn: &Connection, id: &str, status: Status) -> Result<()> {
257 let now = Utc::now().to_rfc3339();
258 let resolved_at = if status == Status::Resolved {
259 Some(now.clone())
260 } else {
261 None
262 };
263
264 let rows = conn.execute(
265 "UPDATE tickets SET status = ?1, updated_at = ?2, resolved_at = ?3 WHERE id = ?4",
266 params![status.to_string(), now, resolved_at, id],
267 )?;
268
269 if rows == 0 {
270 return Err(eyre!("no ticket with id '{id}'"));
271 }
272 Ok(())
273 }
274
275 /// Aggregate stats across all tickets.
276 #[derive(Debug, Default)]
277 pub(crate) struct Stats {
278 pub total: usize,
279 pub by_status: Vec<(Status, usize)>,
280 /// Open tickets bucketed by painhours color band.
281 pub open_by_priority: Vec<(Priority, usize)>,
282 pub open_by_source: Vec<(String, usize)>,
283 /// Average seconds between `created_at` and `resolved_at` for tickets that
284 /// were resolved. `None` when nothing has been resolved yet.
285 pub avg_resolution_seconds: Option<i64>,
286 }
287
288 pub(crate) fn stats(conn: &Connection) -> Result<Stats> {
289 let tickets = list_tickets(conn, &ListFilter::default())?;
290 let mut s = Stats {
291 total: tickets.len(),
292 ..Default::default()
293 };
294
295 let order_status = [
296 Status::Open,
297 Status::InProgress,
298 Status::Resolved,
299 Status::Closed,
300 ];
301 for status in order_status {
302 let count = tickets.iter().filter(|t| t.status == status).count();
303 if count > 0 {
304 s.by_status.push((status, count));
305 }
306 }
307
308 let order_pri = [
309 Priority::Critical,
310 Priority::High,
311 Priority::Medium,
312 Priority::Low,
313 ];
314 for pri in order_pri {
315 let count = tickets
316 .iter()
317 .filter(|t| t.status == Status::Open && t.band() == pri)
318 .count();
319 if count > 0 {
320 s.open_by_priority.push((pri, count));
321 }
322 }
323
324 let mut by_source: std::collections::HashMap<String, usize> = std::collections::HashMap::new();
325 for t in tickets.iter().filter(|t| t.status == Status::Open) {
326 let key = t.source.clone().unwrap_or_else(|| "-".to_string());
327 *by_source.entry(key).or_insert(0) += 1;
328 }
329 let mut by_source: Vec<(String, usize)> = by_source.into_iter().collect();
330 by_source.sort_by(|a, b| b.1.cmp(&a.1).then_with(|| a.0.cmp(&b.0)));
331 s.open_by_source = by_source;
332
333 let resolved: Vec<&Ticket> = tickets.iter().filter(|t| t.resolved_at.is_some()).collect();
334 if !resolved.is_empty() {
335 let total: i64 = resolved
336 .iter()
337 .map(|t| {
338 let resolved_at = t.resolved_at.unwrap();
339 (resolved_at - t.created_at).num_seconds().max(0)
340 })
341 .sum();
342 s.avg_resolution_seconds = Some(total / resolved.len() as i64);
343 }
344
345 Ok(s)
346 }
347
348 /// Delete tickets older than `older_than` with the given status.
349 /// Returns the number of rows deleted.
350 pub(crate) fn prune_tickets(
351 conn: &Connection,
352 older_than: chrono::Duration,
353 status: Status,
354 ) -> Result<usize> {
355 let cutoff = (Utc::now() - older_than).to_rfc3339();
356 let rows = conn.execute(
357 "DELETE FROM tickets WHERE status = ?1 AND updated_at < ?2",
358 params![status.to_string(), cutoff],
359 )?;
360 Ok(rows)
361 }
362
363 // -- Sync operations ----------------------------------------------------------
364
365 /// Get all tickets updated after the given timestamp.
366 pub(crate) fn tickets_since(conn: &Connection, since: &str) -> Result<Vec<Ticket>> {
367 let mut stmt =
368 conn.prepare("SELECT * FROM tickets WHERE updated_at > ?1 ORDER BY updated_at ASC")?;
369 let tickets = stmt
370 .query_map(params![since], row_to_ticket)?
371 .collect::<rusqlite::Result<Vec<_>>>()?;
372 Ok(tickets)
373 }
374
375 /// Upsert a ticket from a peer. Last-writer-wins based on updated_at.
376 /// Returns true if the ticket was inserted or updated.
377 pub(crate) fn upsert_synced_ticket(conn: &Connection, ticket: &Ticket) -> Result<bool> {
378 // Check if we have this ticket and if ours is newer
379 let existing_updated: Option<String> = conn
380 .query_row(
381 "SELECT updated_at FROM tickets WHERE id = ?1",
382 params![ticket.id],
383 |row| row.get(0),
384 )
385 .ok();
386
387 if let Some(ref existing) = existing_updated {
388 let existing_dt = DateTime::parse_from_rfc3339(existing)
389 .map_or_else(|_| Utc::now(), |dt| dt.with_timezone(&Utc));
390 if existing_dt >= ticket.updated_at {
391 return Ok(false); // Ours is same or newer
392 }
393 }
394
395 let resolved_at = ticket.resolved_at.map(|dt| dt.to_rfc3339());
396
397 conn.execute(
398 "INSERT INTO tickets (id, title, body, pain, scale, status, channel, node_id, source, source_ref, created_at, updated_at, resolved_at)
399 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13)
400 ON CONFLICT(id) DO UPDATE SET
401 title = excluded.title,
402 body = excluded.body,
403 pain = excluded.pain,
404 scale = excluded.scale,
405 status = excluded.status,
406 channel = excluded.channel,
407 source = excluded.source,
408 source_ref = excluded.source_ref,
409 updated_at = excluded.updated_at,
410 resolved_at = excluded.resolved_at",
411 params![
412 ticket.id,
413 ticket.title,
414 ticket.body,
415 ticket.pain.clamp(1, 5),
416 ticket.scale.clamp(1, 5),
417 ticket.status.to_string(),
418 ticket.channel.to_string(),
419 ticket.node_id,
420 ticket.source,
421 ticket.source_ref,
422 ticket.created_at.to_rfc3339(),
423 ticket.updated_at.to_rfc3339(),
424 resolved_at,
425 ],
426 )?;
427
428 Ok(true)
429 }
430
431 /// Get the sync cursor for a peer (last synced timestamp).
432 pub(crate) fn get_sync_cursor(conn: &Connection, peer_url: &str) -> Result<Option<String>> {
433 let cursor: Option<String> = conn
434 .query_row(
435 "SELECT last_synced FROM sync_cursors WHERE peer_url = ?1",
436 params![peer_url],
437 |row| row.get(0),
438 )
439 .ok();
440 Ok(cursor)
441 }
442
443 /// Update the sync cursor for a peer.
444 pub(crate) fn set_sync_cursor(conn: &Connection, peer_url: &str, last_synced: &str) -> Result<()> {
445 conn.execute(
446 "INSERT INTO sync_cursors (peer_url, last_synced) VALUES (?1, ?2)
447 ON CONFLICT(peer_url) DO UPDATE SET last_synced = excluded.last_synced",
448 params![peer_url, last_synced],
449 )?;
450 Ok(())
451 }
452
453 #[cfg(test)]
454 mod tests {
455 use super::*;
456
457 fn test_new_ticket(title: &str) -> NewTicket {
458 NewTicket {
459 title: title.to_string(),
460 body: None,
461 pain: 3,
462 scale: 3,
463 channel: Channel::System,
464 source: Some("test".to_string()),
465 source_ref: None,
466 }
467 }
468
469 /// Backdate a ticket's `created_at` so its age-driven painhours climbs.
470 fn backdate_created(conn: &Connection, id: &str, weeks: i64) {
471 let ts = (Utc::now() - chrono::Duration::weeks(weeks)).to_rfc3339();
472 conn.execute(
473 "UPDATE tickets SET created_at = ?1 WHERE id = ?2",
474 params![ts, id],
475 )
476 .unwrap();
477 }
478
479 #[test]
480 fn create_and_get() {
481 let conn = open_memory().unwrap();
482 let node = get_or_create_node_id(&conn).unwrap();
483 let t = create_ticket(&conn, &test_new_ticket("fix the thing"), &node).unwrap();
484 assert_eq!(t.title, "fix the thing");
485 assert_eq!(t.status, Status::Open);
486 assert_eq!(t.channel, Channel::System);
487 assert_eq!(t.node_id, node);
488
489 let fetched = get_ticket(&conn, &t.id).unwrap();
490 assert_eq!(fetched.id, t.id);
491 }
492
493 #[test]
494 fn prefix_match() {
495 let conn = open_memory().unwrap();
496 let node = get_or_create_node_id(&conn).unwrap();
497 let t = create_ticket(&conn, &test_new_ticket("test"), &node).unwrap();
498 let fetched = get_ticket(&conn, &t.id[..8]).unwrap();
499 assert_eq!(fetched.id, t.id);
500 }
501
502 #[test]
503 fn list_with_filter() {
504 let conn = open_memory().unwrap();
505 let node = get_or_create_node_id(&conn).unwrap();
506 create_ticket(
507 &conn,
508 &NewTicket {
509 title: "urgent".into(),
510 body: None,
511 pain: 5,
512 scale: 5,
513 channel: Channel::Request,
514 source: Some("pom".into()),
515 source_ref: None,
516 },
517 &node,
518 )
519 .unwrap();
520 create_ticket(&conn, &test_new_ticket("normal"), &node).unwrap();
521
522 let all = list_tickets(&conn, &ListFilter::default()).unwrap();
523 assert_eq!(all.len(), 2);
524 // Higher pain*scale => higher painhours => sorts first.
525 assert_eq!(all[0].title, "urgent");
526
527 let requests = list_tickets(
528 &conn,
529 &ListFilter {
530 channel: Some(Channel::Request),
531 ..Default::default()
532 },
533 )
534 .unwrap();
535 assert_eq!(requests.len(), 1);
536 }
537
538 #[test]
539 fn update_status_sets_resolved_at() {
540 let conn = open_memory().unwrap();
541 let node = get_or_create_node_id(&conn).unwrap();
542 let t = create_ticket(&conn, &test_new_ticket("resolve me"), &node).unwrap();
543 assert!(t.resolved_at.is_none());
544
545 update_status(&conn, &t.id, Status::Resolved).unwrap();
546 let updated = get_ticket(&conn, &t.id).unwrap();
547 assert_eq!(updated.status, Status::Resolved);
548 assert!(updated.resolved_at.is_some());
549 }
550
551 #[test]
552 fn sync_upsert_last_writer_wins() {
553 let conn = open_memory().unwrap();
554 let node = get_or_create_node_id(&conn).unwrap();
555 let t = create_ticket(&conn, &test_new_ticket("original"), &node).unwrap();
556
557 // Simulate a peer's version with a newer timestamp
558 let mut peer_ticket = t.clone();
559 peer_ticket.title = "updated by peer".to_string();
560 peer_ticket.updated_at = Utc::now() + chrono::Duration::seconds(10);
561
562 let changed = upsert_synced_ticket(&conn, &peer_ticket).unwrap();
563 assert!(changed);
564
565 let fetched = get_ticket(&conn, &peer_ticket.id).unwrap();
566 assert_eq!(fetched.title, "updated by peer");
567
568 // Older update should be rejected
569 let mut stale = peer_ticket.clone();
570 stale.title = "stale update".to_string();
571 stale.updated_at = Utc::now() - chrono::Duration::seconds(100);
572 let changed = upsert_synced_ticket(&conn, &stale).unwrap();
573 assert!(!changed);
574
575 let fetched = get_ticket(&conn, &peer_ticket.id).unwrap();
576 assert_eq!(fetched.title, "updated by peer"); // Not "stale update"
577 }
578
579 #[test]
580 fn search_filter() {
581 let conn = open_memory().unwrap();
582 let node = get_or_create_node_id(&conn).unwrap();
583 create_ticket(&conn, &test_new_ticket("refund issue"), &node).unwrap();
584 create_ticket(&conn, &test_new_ticket("build failure"), &node).unwrap();
585
586 let results = list_tickets(
587 &conn,
588 &ListFilter {
589 search: Some("refund"),
590 ..Default::default()
591 },
592 )
593 .unwrap();
594 assert_eq!(results.len(), 1);
595 assert_eq!(results[0].title, "refund issue");
596 }
597
598 #[test]
599 fn stats_aggregates() {
600 let conn = open_memory().unwrap();
601 let node = get_or_create_node_id(&conn).unwrap();
602 let a = create_ticket(
603 &conn,
604 &NewTicket {
605 title: "a".into(),
606 body: None,
607 pain: 5,
608 scale: 5,
609 channel: Channel::System,
610 source: Some("pom".into()),
611 source_ref: None,
612 },
613 &node,
614 )
615 .unwrap();
616 // Age it four weeks so its painhours reaches the Critical band.
617 backdate_created(&conn, &a.id, 4);
618 create_ticket(
619 &conn,
620 &NewTicket {
621 title: "b".into(),
622 body: None,
623 pain: 1,
624 scale: 1,
625 channel: Channel::Task,
626 source: Some("manual".into()),
627 source_ref: None,
628 },
629 &node,
630 )
631 .unwrap();
632 let c = create_ticket(&conn, &test_new_ticket("c"), &node).unwrap();
633 update_status(&conn, &c.id, Status::Resolved).unwrap();
634
635 let s = stats(&conn).unwrap();
636 assert_eq!(s.total, 3);
637 assert!(
638 s.by_status
639 .iter()
640 .any(|(st, n)| *st == Status::Open && *n == 2)
641 );
642 assert!(
643 s.by_status
644 .iter()
645 .any(|(st, n)| *st == Status::Resolved && *n == 1)
646 );
647 // Two open tickets: an aged 5x5 (Critical band) and a fresh 1x1 (Low band).
648 assert!(
649 s.open_by_priority
650 .iter()
651 .any(|(p, n)| *p == Priority::Critical && *n == 1)
652 );
653 assert!(
654 s.open_by_priority
655 .iter()
656 .any(|(p, n)| *p == Priority::Low && *n == 1)
657 );
658 // Source counts only the open tickets
659 let pom = s
660 .open_by_source
661 .iter()
662 .find(|(k, _)| k == "pom")
663 .map(|(_, n)| *n);
664 assert_eq!(pom, Some(1));
665 assert!(s.avg_resolution_seconds.is_some());
666 // First ticket is still open
667 let _ = a;
668 }
669
670 #[test]
671 fn prune_deletes_matching() {
672 let conn = open_memory().unwrap();
673 let node = get_or_create_node_id(&conn).unwrap();
674 let closed = create_ticket(&conn, &test_new_ticket("old closed"), &node).unwrap();
675 update_status(&conn, &closed.id, Status::Closed).unwrap();
676 // Backdate updated_at by 100 days
677 let backdate = (Utc::now() - chrono::Duration::days(100)).to_rfc3339();
678 conn.execute(
679 "UPDATE tickets SET updated_at = ?1 WHERE id = ?2",
680 params![backdate, closed.id],
681 )
682 .unwrap();
683
684 // A recent closed ticket should not be pruned
685 let recent = create_ticket(&conn, &test_new_ticket("recent closed"), &node).unwrap();
686 update_status(&conn, &recent.id, Status::Closed).unwrap();
687
688 // An old but still-open ticket should not be pruned
689 let open = create_ticket(&conn, &test_new_ticket("old open"), &node).unwrap();
690 conn.execute(
691 "UPDATE tickets SET updated_at = ?1 WHERE id = ?2",
692 params![backdate, open.id],
693 )
694 .unwrap();
695
696 let n = prune_tickets(&conn, chrono::Duration::days(90), Status::Closed).unwrap();
697 assert_eq!(n, 1);
698 assert!(get_ticket(&conn, &closed.id).is_err());
699 assert!(get_ticket(&conn, &recent.id).is_ok());
700 assert!(get_ticket(&conn, &open.id).is_ok());
701 }
702
703 #[test]
704 fn node_id_persists() {
705 let conn = open_memory().unwrap();
706 let id1 = get_or_create_node_id(&conn).unwrap();
707 let id2 = get_or_create_node_id(&conn).unwrap();
708 assert_eq!(id1, id2);
709 }
710
711 #[test]
712 fn painhours_climbs_with_age() {
713 let conn = open_memory().unwrap();
714 let node = get_or_create_node_id(&conn).unwrap();
715 let t = create_ticket(
716 &conn,
717 &NewTicket {
718 title: "aging bug".into(),
719 body: None,
720 pain: 4,
721 scale: 4,
722 channel: Channel::System,
723 source: None,
724 source_ref: None,
725 },
726 &node,
727 )
728 .unwrap();
729 let fresh_score = get_ticket(&conn, &t.id).unwrap().painhours();
730
731 backdate_created(&conn, &t.id, 6);
732 let aged_score = get_ticket(&conn, &t.id).unwrap().painhours();
733
734 assert!(
735 aged_score > fresh_score,
736 "aged {aged_score} !> fresh {fresh_score}"
737 );
738 }
739
740 #[test]
741 fn painhours_freezes_on_resolve() {
742 let conn = open_memory().unwrap();
743 let node = get_or_create_node_id(&conn).unwrap();
744 // Low factors so the two ages stay below the saturation ceiling and
745 // produce distinguishable scores.
746 let t = create_ticket(
747 &conn,
748 &NewTicket {
749 title: "freeze me".into(),
750 body: None,
751 pain: 1,
752 scale: 1,
753 channel: Channel::System,
754 source: None,
755 source_ref: None,
756 },
757 &node,
758 )
759 .unwrap();
760
761 // Created 10 weeks ago but resolved 4 weeks ago: a resolved ticket's age
762 // is anchored at resolved_at, so it should read 6 weeks (10 - 4), not the
763 // 10 weeks an equivalent still-open ticket would show.
764 let created = (Utc::now() - chrono::Duration::weeks(10)).to_rfc3339();
765 let resolved = (Utc::now() - chrono::Duration::weeks(4)).to_rfc3339();
766 conn.execute(
767 "UPDATE tickets SET created_at = ?1, status = 'resolved', resolved_at = ?2 WHERE id = ?3",
768 params![created, resolved, t.id],
769 ).unwrap();
770
771 let frozen = get_ticket(&conn, &t.id).unwrap();
772 assert_eq!(
773 frozen.age_weeks(),
774 6,
775 "age is frozen at (resolved_at - created_at)"
776 );
777
778 // Reopening un-freezes the anchor: age jumps to the full 10 weeks.
779 update_status(&conn, &t.id, Status::Open).unwrap();
780 conn.execute(
781 "UPDATE tickets SET created_at = ?1 WHERE id = ?2",
782 params![created, t.id],
783 )
784 .unwrap();
785 let reopened = get_ticket(&conn, &t.id).unwrap();
786 assert_eq!(
787 reopened.age_weeks(),
788 10,
789 "an open ticket measures age to now"
790 );
791 assert!(reopened.painhours() > frozen.painhours());
792 }
793
794 #[test]
795 fn scale_drives_over_pain() {
796 // At equal age, a widespread-but-mild bug outranks a narrow-but-severe
797 // one: scale is the driving factor, pain only sub-orders within a tier.
798 let conn = open_memory().unwrap();
799 let node = get_or_create_node_id(&conn).unwrap();
800 create_ticket(
801 &conn,
802 &NewTicket {
803 title: "widespread mild".into(),
804 body: None,
805 pain: 1,
806 scale: 5,
807 channel: Channel::System,
808 source: None,
809 source_ref: None,
810 },
811 &node,
812 )
813 .unwrap();
814 create_ticket(
815 &conn,
816 &NewTicket {
817 title: "narrow severe".into(),
818 body: None,
819 pain: 5,
820 scale: 1,
821 channel: Channel::System,
822 source: None,
823 source_ref: None,
824 },
825 &node,
826 )
827 .unwrap();
828
829 let all = list_tickets(&conn, &ListFilter::default()).unwrap();
830 assert_eq!(all[0].title, "widespread mild");
831 assert!(all[0].painhours() > all[1].painhours());
832 }
833
834 // The color-band cutoffs are a pure function of the score and are tested in
835 // the `painhours` crate, alongside the constants that set them.
836 }
837