smriti/services/
insights.rs

1//! Insights Dashboard — compute aggregate statistics from the photo library.
2//!
3//! `compute()` runs ~12 SQL queries against the photos/faces/albums tables
4//! and returns an `InsightsData` struct with everything the view needs.
5
6use std::collections::HashMap;
7
8use rusqlite::{params, Connection, Result as SqliteResult};
9
10/// A person stat row for the top-people section.
11#[derive(Debug, Clone)]
12pub struct PersonStat {
13    pub cluster_id: i64,
14    pub name: String,
15    pub photo_count: i64,
16    /// Representative face ID (used to locate the face crop thumbnail).
17    pub face_id: Option<i64>,
18    /// Resolved absolute path to the face crop image (populated post-query).
19    pub face_crop_path: Option<String>,
20}
21
22/// A location stat row for the top-locations section.
23#[derive(Debug, Clone)]
24pub struct LocationStat {
25    pub city: String,
26    pub country: String,
27    pub photo_count: i64,
28}
29
30/// A country stat row for the top-countries section.
31#[derive(Debug, Clone)]
32pub struct CountryStat {
33    pub country: String,
34    pub photo_count: i64,
35}
36
37/// A camera stat row for the camera-breakdown section.
38#[derive(Debug, Clone)]
39pub struct CameraStat {
40    pub camera: String,
41    pub photo_count: i64,
42}
43
44/// All data needed by the Insights dashboard view. Boxed in the message
45/// because it is large.
46#[derive(Debug, Clone)]
47pub struct InsightsData {
48    pub total_photos: i64,
49    pub date_range_start: Option<String>,
50    pub date_range_end: Option<String>,
51    pub people_count: i64,
52    pub album_count: i64,
53    pub country_count: i64,
54    pub city_count: i64,
55    /// How many photos in scope have GPS coordinates. Lets the view tell
56    /// "no GPS data yet" apart from "GPS data but no geocoder result".
57    pub photos_with_gps: i64,
58    pub hero_photo_id: Option<i64>,
59    pub hero_thumbnail_path: Option<String>,
60    /// Day-level photo counts for the heatmap: "YYYY-MM-DD" -> count.
61    pub heatmap: HashMap<String, i64>,
62    /// The year the heatmap covers.
63    pub heatmap_year: i32,
64    /// Monthly photo counts (index 0 = January, 11 = December).
65    pub monthly_counts: [i64; 12],
66    pub top_people: Vec<PersonStat>,
67    pub top_locations: Vec<LocationStat>,
68    pub top_countries: Vec<CountryStat>,
69    pub top_cameras: Vec<CameraStat>,
70    /// Distinct years present in the library (descending).
71    pub available_years: Vec<i32>,
72}
73
74/// Build a year filter clause. When `year` is Some, returns
75/// `" AND strftime('%Y', date_taken) = 'YYYY'"`. Otherwise empty.
76fn year_clause(year: Option<i32>) -> String {
77    match year {
78        Some(y) => format!(" AND strftime('%Y', date_taken) = '{}'", y),
79        None => String::new(),
80    }
81}
82
83fn normalized_datetime_sql(column: &str) -> String {
84    format!(
85        "CASE WHEN instr({0}, 'T') > 0 THEN replace(substr({0}, 1, 19), 'T', ' ') ELSE substr({0}, 1, 19) END",
86        column
87    )
88}
89
90/// Compute all insights data. Designed to run inside `spawn_blocking`.
91pub fn compute(conn: &Connection, year: Option<i32>) -> SqliteResult<InsightsData> {
92    let _legacy = year_clause(year);
93    let dt = normalized_datetime_sql("date_taken");
94    let yc = match year {
95        Some(y) => format!(" AND strftime('%Y', {}) = '{}'", dt, y),
96        None => String::new(),
97    };
98
99    // 1+2. Total photos + min/max date — collapsed into a single
100    // SELECT. Three round-trips → one. The aggregations all share
101    // the same FROM / WHERE so SQLite can do them in one scan.
102    let (total_photos, date_range_start, date_range_end): (i64, Option<String>, Option<String>) =
103        conn.query_row(
104            &format!(
105                "SELECT COUNT(*),
106                        MIN(CASE WHEN date_taken IS NOT NULL THEN {dt} END),
107                        MAX(CASE WHEN date_taken IS NOT NULL THEN {dt} END)
108                 FROM photos
109                 WHERE is_trashed = FALSE{yc}",
110                dt = dt,
111                yc = yc
112            ),
113            [],
114            |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?)),
115        )?;
116
117    // 3. People count — distinct named clusters that have photos in scope
118    let people_count: i64 = conn.query_row(
119        &format!(
120            "SELECT COUNT(DISTINCT fc.id)
121             FROM face_clusters fc
122             JOIN faces f ON f.cluster_id = fc.id
123             JOIN photos p ON p.id = f.photo_id
124             WHERE fc.name IS NOT NULL
125               AND p.is_trashed = FALSE{}",
126            yc.replace(&dt, &normalized_datetime_sql("p.date_taken"))
127        ),
128        [],
129        |row| row.get(0),
130    )?;
131
132    // 4. Album count — distinct albums that have photos in scope
133    let album_count: i64 = conn.query_row(
134        &format!(
135            "SELECT COUNT(DISTINCT a.id)
136             FROM albums a
137             JOIN album_photos ap ON ap.album_id = a.id
138             JOIN photos p ON p.id = ap.photo_id
139             WHERE p.is_trashed = FALSE{}",
140            yc.replace(&dt, &normalized_datetime_sql("p.date_taken"))
141        ),
142        [],
143        |row| row.get(0),
144    )?;
145
146    // 5. Country + city counts + GPS-tagged count (one query, three aggs).
147    let (country_count, city_count, photos_with_gps): (i64, i64, i64) = conn.query_row(
148        &format!(
149            "SELECT
150                COUNT(DISTINCT CASE WHEN location_country IS NOT NULL AND location_country != ''
151                                    THEN location_country END),
152                COUNT(DISTINCT CASE WHEN location_city IS NOT NULL AND location_city != ''
153                                    THEN location_city || char(31) || COALESCE(location_country, '') END),
154                SUM(CASE WHEN gps_latitude IS NOT NULL AND gps_longitude IS NOT NULL THEN 1 ELSE 0 END)
155             FROM photos
156             WHERE is_trashed = FALSE{}",
157            yc
158        ),
159        [],
160        |row| Ok((row.get(0)?, row.get(1)?, row.get::<_, Option<i64>>(2)?.unwrap_or(0))),
161    )?;
162
163    // 6. Hero photo — prefer photos with faces, landscape orientation, newest
164    let hero_photo_id: Option<i64> = conn
165        .query_row(
166            &format!(
167                "SELECT p.id
168                 FROM photos p
169                 LEFT JOIN faces f ON f.photo_id = p.id
170                 WHERE p.is_trashed = FALSE
171                   AND p.thumbnail_path IS NOT NULL{}
172                 GROUP BY p.id
173                 ORDER BY (COUNT(f.id) > 0) DESC,
174                          (COALESCE(p.width, 0) > COALESCE(p.height, 0)) DESC,
175                           CASE WHEN instr(p.date_taken, 'T') > 0 THEN replace(substr(p.date_taken, 1, 19), 'T', ' ') ELSE substr(p.date_taken, 1, 19) END DESC
176                  LIMIT 1",
177                yc
178            ),
179            [],
180            |row| row.get(0),
181        )
182        .ok();
183
184    // Determine heatmap year: if a specific year is selected, use it;
185    // otherwise use the most recent year with photos.
186    let heatmap_year: i32 = if let Some(y) = year {
187        y
188    } else {
189        conn.query_row(
190            "SELECT CAST(strftime('%Y', MAX(CASE WHEN instr(date_taken, 'T') > 0 THEN replace(substr(date_taken, 1, 19), 'T', ' ') ELSE substr(date_taken, 1, 19) END)) AS INTEGER)
191             FROM photos
192             WHERE is_trashed = FALSE AND date_taken IS NOT NULL",
193            [],
194            |row| row.get::<_, Option<i32>>(0),
195        )
196        .ok()
197        .flatten()
198        .unwrap_or(chrono::Local::now().date_naive().year())
199    };
200
201    // 7. Heatmap day counts for the selected year
202    let mut heatmap = HashMap::new();
203    {
204        let mut stmt = conn.prepare(
205            "SELECT DATE(CASE WHEN instr(date_taken, 'T') > 0 THEN replace(substr(date_taken, 1, 19), 'T', ' ') ELSE substr(date_taken, 1, 19) END) AS d, COUNT(*) AS c
206             FROM photos
207             WHERE is_trashed = FALSE
208               AND date_taken IS NOT NULL
209                AND strftime('%Y', CASE WHEN instr(date_taken, 'T') > 0 THEN replace(substr(date_taken, 1, 19), 'T', ' ') ELSE substr(date_taken, 1, 19) END) = ?1
210              GROUP BY d",
211        )?;
212        let rows = stmt.query_map(params![heatmap_year.to_string()], |row| {
213            Ok((row.get::<_, String>(0)?, row.get::<_, i64>(1)?))
214        })?;
215        for (day, count) in rows.flatten() {
216            heatmap.insert(day, count);
217        }
218    }
219
220    // 8. Monthly counts (for the selected year if any, otherwise all time)
221    let mut monthly_counts = [0i64; 12];
222    {
223        let mut stmt = conn.prepare(&format!(
224            "SELECT CAST(strftime('%m', CASE WHEN instr(date_taken, 'T') > 0 THEN replace(substr(date_taken, 1, 19), 'T', ' ') ELSE substr(date_taken, 1, 19) END) AS INTEGER) AS m, COUNT(*) AS c
225             FROM photos
226             WHERE is_trashed = FALSE
227               AND date_taken IS NOT NULL{}
228             GROUP BY m",
229            yc
230        ))?;
231        let rows = stmt.query_map([], |row| Ok((row.get::<_, i32>(0)?, row.get::<_, i64>(1)?)))?;
232        for (month, count) in rows.flatten() {
233            if (1..=12).contains(&month) {
234                monthly_counts[(month - 1) as usize] = count;
235            }
236        }
237    }
238
239    // 9. Top 5 people (named clusters only, by photo count)
240    let mut top_people = Vec::new();
241    {
242        let mut stmt = conn.prepare(&format!(
243            "SELECT fc.id, fc.name, COUNT(DISTINCT f.photo_id) AS cnt, fc.representative_face_id
244             FROM face_clusters fc
245             JOIN faces f ON f.cluster_id = fc.id
246             JOIN photos p ON p.id = f.photo_id
247             WHERE fc.name IS NOT NULL
248               AND p.is_trashed = FALSE{}
249             GROUP BY fc.id
250             ORDER BY cnt DESC
251             LIMIT 10",
252            yc.replace(&dt, &normalized_datetime_sql("p.date_taken"))
253        ))?;
254        let rows = stmt.query_map([], |row| {
255            Ok(PersonStat {
256                cluster_id: row.get(0)?,
257                name: row.get::<_, Option<String>>(1)?.unwrap_or_default(),
258                photo_count: row.get(2)?,
259                face_id: row.get(3)?,
260                face_crop_path: None, // resolved later
261            })
262        })?;
263        for stat in rows.flatten() {
264            top_people.push(stat);
265        }
266    }
267
268    // 10. Top cities (by photo count)
269    let mut top_locations = Vec::new();
270    {
271        let mut stmt = conn.prepare(&format!(
272            "SELECT location_city, location_country, COUNT(*) AS cnt
273             FROM photos
274             WHERE is_trashed = FALSE
275               AND location_city IS NOT NULL
276               AND location_city != ''{}
277             GROUP BY location_city, location_country
278             ORDER BY cnt DESC
279             LIMIT 30",
280            yc
281        ))?;
282        let rows = stmt.query_map([], |row| {
283            Ok(LocationStat {
284                city: row.get::<_, Option<String>>(0)?.unwrap_or_default(),
285                country: row.get::<_, Option<String>>(1)?.unwrap_or_default(),
286                photo_count: row.get(2)?,
287            })
288        })?;
289        for stat in rows.flatten() {
290            top_locations.push(stat);
291        }
292    }
293
294    // 10b. Top countries (by photo count). Separate aggregation so a
295    // country with photos spread across many cities still surfaces.
296    let mut top_countries = Vec::new();
297    {
298        let mut stmt = conn.prepare(&format!(
299            "SELECT location_country, COUNT(*) AS cnt
300             FROM photos
301             WHERE is_trashed = FALSE
302               AND location_country IS NOT NULL
303               AND location_country != ''{}
304             GROUP BY location_country
305             ORDER BY cnt DESC
306             LIMIT 20",
307            yc
308        ))?;
309        let rows = stmt.query_map([], |row| {
310            Ok(CountryStat {
311                country: row.get::<_, Option<String>>(0)?.unwrap_or_default(),
312                photo_count: row.get(1)?,
313            })
314        })?;
315        for stat in rows.flatten() {
316            top_countries.push(stat);
317        }
318    }
319
320    // 11. Top cameras. Group raw EXIF make/model first, then collapse
321    // known codenames into user-facing names.
322    let mut top_cameras = Vec::new();
323    {
324        let mut stmt = conn.prepare(&format!(
325            "SELECT NULLIF(TRIM(camera_make), '') AS make,
326                    NULLIF(TRIM(camera_model), '') AS model,
327                    COUNT(*) AS cnt
328             FROM photos
329             WHERE is_trashed = FALSE
330               AND (NULLIF(TRIM(camera_make), '') IS NOT NULL
331                    OR NULLIF(TRIM(camera_model), '') IS NOT NULL){}
332             GROUP BY make, model
333             ORDER BY cnt DESC
334             LIMIT 100",
335            yc
336        ))?;
337        let rows = stmt.query_map([], |row| {
338            Ok((
339                row.get::<_, Option<String>>(0)?,
340                row.get::<_, Option<String>>(1)?,
341                row.get::<_, i64>(2)?,
342            ))
343        })?;
344        let mut grouped: HashMap<String, i64> = HashMap::new();
345        for (make, model, count) in rows.flatten() {
346            if let Some(name) = crate::services::camera_names::friendly_camera_name(
347                make.as_deref(),
348                model.as_deref(),
349            ) {
350                *grouped.entry(name).or_insert(0) += count;
351            }
352        }
353        let mut cameras: Vec<CameraStat> = grouped
354            .into_iter()
355            .map(|(camera, photo_count)| CameraStat {
356                camera,
357                photo_count,
358            })
359            .collect();
360        cameras.sort_by(|a, b| {
361            b.photo_count
362                .cmp(&a.photo_count)
363                .then(a.camera.cmp(&b.camera))
364        });
365        top_cameras.extend(cameras.into_iter().take(3));
366    }
367
368    // 12. Available years (descending)
369    let mut available_years = Vec::new();
370    {
371        let mut stmt = conn.prepare(
372            "SELECT DISTINCT CAST(strftime('%Y', CASE WHEN instr(date_taken, 'T') > 0 THEN replace(substr(date_taken, 1, 19), 'T', ' ') ELSE substr(date_taken, 1, 19) END) AS INTEGER) AS y
373             FROM photos
374             WHERE is_trashed = FALSE AND date_taken IS NOT NULL
375             ORDER BY y DESC",
376        )?;
377        let rows = stmt.query_map([], |row| row.get::<_, i32>(0))?;
378        for y in rows.flatten() {
379            available_years.push(y);
380        }
381    }
382
383    Ok(InsightsData {
384        total_photos,
385        date_range_start,
386        date_range_end,
387        people_count,
388        album_count,
389        country_count,
390        city_count,
391        photos_with_gps,
392        hero_photo_id,
393        hero_thumbnail_path: None, // resolved by the loader
394        heatmap,
395        heatmap_year,
396        monthly_counts,
397        top_people,
398        top_locations,
399        top_countries,
400        top_cameras,
401        available_years,
402    })
403}
404
405use chrono::Datelike;
406
407#[cfg(test)]
408mod tests {
409    use super::compute;
410    use crate::db::create_schema;
411    use rusqlite::Connection;
412
413    #[test]
414    fn top_locations_keep_same_city_in_different_countries_separate() {
415        let conn = Connection::open_in_memory().unwrap();
416        create_schema(&conn).unwrap();
417        conn.execute(
418            "INSERT INTO photos
419                (id, file_path, file_name, file_hash, file_size, date_taken, location_city, location_country, is_trashed)
420             VALUES
421                (1, 'a.jpg', 'a.jpg', 'hash-a', 10, '2026-01-02T00:00:00Z', 'Springfield', 'USA', 0),
422                (2, 'b.jpg', 'b.jpg', 'hash-b', 10, '2026-01-01T00:00:00Z', 'Springfield', 'Canada', 0)",
423            [],
424        )
425        .unwrap();
426
427        let data = compute(&conn, None).unwrap();
428        assert_eq!(data.city_count, 2);
429        let springfields = data
430            .top_locations
431            .iter()
432            .filter(|location| location.city == "Springfield")
433            .collect::<Vec<_>>();
434
435        assert_eq!(springfields.len(), 2);
436        assert!(springfields
437            .iter()
438            .any(|location| location.country == "USA"));
439        assert!(springfields
440            .iter()
441            .any(|location| location.country == "Canada"));
442    }
443}
smriti/services/insights.rs

smriti/services/
insights.rs
