smriti/db/
album_suggestion_repo.rs

1//! Album suggestion database operations
2//!
3//! Stores detected trip/event suggestions with lifecycle tracking.
4//! Suggestions are fingerprinted so dismissed or accepted patterns are
5//! never re-surfaced.
6
7use rusqlite::{params, Connection, Result as SqliteResult};
8use std::collections::{HashMap, HashSet};
9
10/// A persisted album suggestion.
11#[allow(dead_code)]
12#[derive(Debug, Clone)]
13pub struct AlbumSuggestionRecord {
14    pub id: i64,
15    pub kind: String, // "trip" | "event"
16    pub title: String,
17    pub photo_ids_json: String, // JSON array of i64
18    pub cover_photo_id: Option<i64>,
19    pub fingerprint: String,
20    pub status: String, // "pending" | "accepted" | "dismissed"
21    pub seen_count: i64,
22    pub created_at: String,
23    /// Resolved absolute thumbnail path (set during loading, not from DB)
24    pub cover_thumbnail_path: Option<String>,
25}
26
27impl AlbumSuggestionRecord {
28    /// Deserialise the photo_ids JSON into a `Vec<i64>`.
29    pub fn photo_ids(&self) -> Vec<i64> {
30        serde_json::from_str(&self.photo_ids_json).unwrap_or_default()
31    }
32
33    pub fn photo_count(&self) -> usize {
34        self.photo_ids().len()
35    }
36}
37
38pub struct AlbumSuggestionRepo<'a> {
39    conn: &'a Connection,
40}
41
42impl<'a> AlbumSuggestionRepo<'a> {
43    pub fn new(conn: &'a Connection) -> Self {
44        Self { conn }
45    }
46
47    /// Insert a new suggestion. Returns the new row id.
48    pub fn insert(
49        &self,
50        kind: &str,
51        title: &str,
52        photo_ids: &[i64],
53        cover_photo_id: Option<i64>,
54        fingerprint: &str,
55    ) -> SqliteResult<i64> {
56        let json = serde_json::to_string(photo_ids).unwrap_or_else(|_| "[]".to_string());
57        self.conn.execute(
58            r#"INSERT INTO album_suggestions
59               (kind, title, photo_ids_json, cover_photo_id, fingerprint, status, seen_count)
60               VALUES (?1, ?2, ?3, ?4, ?5, 'pending', 0)"#,
61            params![kind, title, json, cover_photo_id, fingerprint],
62        )?;
63        Ok(self.conn.last_insert_rowid())
64    }
65
66    /// Get all pending suggestions, ordered by newest first.
67    pub fn get_pending(&self) -> SqliteResult<Vec<AlbumSuggestionRecord>> {
68        let mut stmt = self.conn.prepare(
69            r#"SELECT s.id, s.kind, s.title, s.photo_ids_json, s.cover_photo_id,
70                      s.fingerprint, s.status, s.seen_count, s.created_at,
71                      pcov.thumbnail_path
72               FROM album_suggestions s
73               LEFT JOIN photos pcov
74                      ON pcov.id = s.cover_photo_id
75                     AND pcov.is_trashed = FALSE
76               WHERE s.status = 'pending'
77               ORDER BY s.created_at DESC"#,
78        )?;
79        let rows = stmt.query_map([], Self::map_row)?;
80        let mut out = Vec::new();
81        for r in rows {
82            if let Some(record) = self.with_active_photos_only(r?)? {
83                out.push(record);
84            }
85        }
86        Ok(out)
87    }
88
89    /// Mark a suggestion as accepted.
90    pub fn accept(&self, id: i64) -> SqliteResult<()> {
91        let affected = self.conn.execute(
92            "UPDATE album_suggestions SET status = 'accepted' WHERE id = ?1",
93            params![id],
94        )?;
95        if affected == 0 {
96            return Err(rusqlite::Error::QueryReturnedNoRows);
97        }
98        Ok(())
99    }
100
101    /// Mark a suggestion as dismissed (will never re-surface due to fingerprint).
102    pub fn dismiss(&self, id: i64) -> SqliteResult<()> {
103        let affected = self.conn.execute(
104            "UPDATE album_suggestions SET status = 'dismissed' WHERE id = ?1",
105            params![id],
106        )?;
107        if affected == 0 {
108            return Err(rusqlite::Error::QueryReturnedNoRows);
109        }
110        Ok(())
111    }
112
113    /// Increment seen_count for all pending suggestions by 1.
114    /// Suggestions with seen_count > 10 are auto-expired to 'dismissed'.
115    pub fn increment_seen_counts(&self) -> SqliteResult<()> {
116        self.conn.execute(
117            "UPDATE album_suggestions SET seen_count = seen_count + 1 WHERE status = 'pending'",
118            [],
119        )?;
120        self.conn.execute(
121            "UPDATE album_suggestions SET status = 'dismissed' WHERE status = 'pending' AND seen_count > 10",
122            [],
123        )?;
124        Ok(())
125    }
126
127    /// Get all fingerprints (accepted + dismissed) to avoid re-suggesting.
128    pub fn get_all_fingerprints(&self) -> SqliteResult<Vec<String>> {
129        let mut stmt = self.conn.prepare(
130            "SELECT fingerprint FROM album_suggestions WHERE status IN ('accepted', 'dismissed', 'pending')",
131        )?;
132        let rows = stmt.query_map([], |row| row.get(0))?;
133        let mut out = Vec::new();
134        for r in rows {
135            out.push(r?);
136        }
137        Ok(out)
138    }
139
140    /// Delete suggestions older than `days` that are not pending.
141    pub fn cleanup_old(&self, days: u32) -> SqliteResult<usize> {
142        let affected = self.conn.execute(
143            r#"DELETE FROM album_suggestions
144               WHERE status != 'pending'
145                 AND created_at < datetime('now', ?1)"#,
146            params![format!("-{} days", days)],
147        )?;
148        Ok(affected)
149    }
150
151    fn map_row(row: &rusqlite::Row<'_>) -> rusqlite::Result<AlbumSuggestionRecord> {
152        Ok(AlbumSuggestionRecord {
153            id: row.get(0)?,
154            kind: row.get(1)?,
155            title: row.get(2)?,
156            photo_ids_json: row.get(3)?,
157            cover_photo_id: row.get(4)?,
158            fingerprint: row.get(5)?,
159            status: row.get(6)?,
160            seen_count: row.get(7)?,
161            created_at: row.get(8)?,
162            cover_thumbnail_path: row.get(9)?,
163        })
164    }
165
166    fn with_active_photos_only(
167        &self,
168        mut record: AlbumSuggestionRecord,
169    ) -> SqliteResult<Option<AlbumSuggestionRecord>> {
170        let ids = record.photo_ids();
171        if ids.is_empty() {
172            return Ok(None);
173        }
174        let active = self.active_photo_rows(&ids)?;
175        if active.is_empty() {
176            return Ok(None);
177        }
178        let active_set: HashSet<i64> = active.iter().map(|p| p.id).collect();
179        let active_by_id: HashMap<i64, ActivePhoto> =
180            active.into_iter().map(|p| (p.id, p)).collect();
181        let filtered: Vec<i64> = ids
182            .into_iter()
183            .filter(|id| active_set.contains(id))
184            .collect();
185        record.photo_ids_json = serde_json::to_string(&filtered).unwrap_or_else(|_| "[]".into());
186        let cover_is_active = record
187            .cover_photo_id
188            .map(|id| active_set.contains(&id))
189            .unwrap_or(false);
190        let cover_is_renderable = record
191            .cover_photo_id
192            .and_then(|id| active_by_id.get(&id))
193            .map(|p| p.media_type == "photo" || p.thumbnail_path.is_some())
194            .unwrap_or(false);
195        if !cover_is_active || !cover_is_renderable {
196            record.cover_photo_id = filtered
197                .iter()
198                .copied()
199                .find(|id| {
200                    active_by_id
201                        .get(id)
202                        .map(|p| p.media_type == "photo")
203                        .unwrap_or(false)
204                })
205                .or_else(|| {
206                    filtered.iter().copied().find(|id| {
207                        active_by_id
208                            .get(id)
209                            .and_then(|p| p.thumbnail_path.as_ref())
210                            .is_some()
211                    })
212                })
213                .or_else(|| filtered.first().copied());
214        }
215        record.cover_thumbnail_path = record
216            .cover_photo_id
217            .and_then(|id| active_by_id.get(&id))
218            .and_then(|p| p.thumbnail_path.clone());
219        let cover = record.cover_photo_id.and_then(|id| active_by_id.get(&id));
220        let has_renderable_cover = cover
221            .map(|p| p.media_type == "photo" || p.thumbnail_path.is_some())
222            .unwrap_or(false);
223        if has_renderable_cover {
224            Ok(Some(record))
225        } else {
226            Ok(None)
227        }
228    }
229
230    fn active_photo_rows(&self, ids: &[i64]) -> SqliteResult<Vec<ActivePhoto>> {
231        let mut out = Vec::new();
232        for chunk in ids.chunks(900) {
233            let placeholders = (0..chunk.len()).map(|_| "?").collect::<Vec<_>>().join(",");
234            let sql = format!(
235                "SELECT id, media_type, thumbnail_path FROM photos WHERE is_trashed = FALSE AND id IN ({})",
236                placeholders
237            );
238            let mut stmt = self.conn.prepare(&sql)?;
239            let rows =
240                stmt.query_map(rusqlite::params_from_iter(chunk.iter().copied()), |row| {
241                    Ok(ActivePhoto {
242                        id: row.get(0)?,
243                        media_type: row.get(1)?,
244                        thumbnail_path: row.get(2)?,
245                    })
246                })?;
247            for row in rows {
248                out.push(row?);
249            }
250        }
251        Ok(out)
252    }
253}
254
255#[derive(Debug, Clone)]
256struct ActivePhoto {
257    id: i64,
258    media_type: String,
259    thumbnail_path: Option<String>,
260}
261
262#[cfg(test)]
263mod tests {
264    use super::*;
265    use crate::db::create_schema;
266
267    #[test]
268    fn accept_and_dismiss_report_missing_suggestions() {
269        let conn = Connection::open_in_memory().unwrap();
270        create_schema(&conn).unwrap();
271        let repo = AlbumSuggestionRepo::new(&conn);
272
273        assert!(matches!(
274            repo.accept(999),
275            Err(rusqlite::Error::QueryReturnedNoRows)
276        ));
277        assert!(matches!(
278            repo.dismiss(999),
279            Err(rusqlite::Error::QueryReturnedNoRows)
280        ));
281    }
282}
smriti/db/album_suggestion_repo.rs

smriti/db/
album_suggestion_repo.rs
