smriti/db/

duplicate_repo.rs

//! Duplicate groups database operations

use std::collections::HashMap;

use rusqlite::{params, params_from_iter, types::ToSql, Connection, Result as SqliteResult};

use super::MAX_ROWS_PER_INSERT;

/// Duplicate group record
#[derive(Debug, Clone)]
pub struct DuplicateGroupRecord {
    pub id: i64,
    pub member_count: i64,
    /// Relative thumbnail path of one member — the suggested-keep when set,
    /// otherwise the first member by photo id. The listing UI shows this
    /// as the group's cover so the user can recognise the photo at a
    /// glance instead of just seeing "Group of 4".
    pub cover_thumbnail_path: Option<String>,
    /// Photo id matching the cover_thumbnail_path. Lets the listing
    /// route directly to the photo viewer on click (which then uses
    /// `member_photo_ids` as its prev/next scope).
    pub cover_photo_id: Option<i64>,
    /// All members in display order — same scope used by PhotoDetail's
    /// arrow navigation when the user clicks a thumb in the listing.
    pub member_photo_ids: Vec<i64>,
}

/// Duplicate group member record
#[derive(Debug, Clone)]
pub struct DuplicateGroupMemberRecord {
    pub photo_id: i64,
    pub is_suggested_keep: bool,

    // Joined from photos table
    pub file_path: Option<String>,
    pub thumbnail_path: Option<String>,
    pub file_size: Option<i64>,
    pub date_taken: Option<String>,
}

/// Duplicate repository
pub struct DuplicateRepo<'a> {
    conn: &'a Connection,
}

impl<'a> DuplicateRepo<'a> {
    pub fn new(conn: &'a Connection) -> Self {
        Self { conn }
    }

    /// Create or update duplicate groups from detection results, preserving user decisions.
    ///
    /// Uses merge-based approach: existing groups whose hash matches are kept
    /// intact (preserving `is_suggested_keep`), new groups are created, and groups
    /// whose hash no longer has duplicates are removed.
    pub fn sync_duplicate_groups(
        &self,
        groups: &[(String, Vec<i64>, Option<i64>, &'static str)], // (hash, photo_ids, suggested_keep, duplicate_type)
    ) -> SqliteResult<()> {
        use std::collections::{HashMap, HashSet};

        // Load existing groups by hash
        let mut existing_hashes: HashMap<String, (i64, bool)> = HashMap::new();
        {
            let mut stmt = self
                .conn
                .prepare("SELECT id, group_hash, resolved FROM duplicate_groups")?;
            let rows = stmt.query_map([], |row| {
                Ok((
                    row.get::<_, i64>(0)?,
                    row.get::<_, String>(1)?,
                    row.get::<_, bool>(2)?,
                ))
            })?;
            for row in rows {
                let (id, hash, resolved) = row?;
                existing_hashes.insert(hash, (id, resolved));
            }
        }

        let tx = self.conn.unchecked_transaction()?;
        let mut seen_hashes: HashSet<String> = HashSet::new();

        for (hash, photo_ids, suggested_keep, dup_type) in groups {
            seen_hashes.insert(hash.clone());

            if let Some((group_id, resolved)) = existing_hashes.get(hash).copied() {
                if resolved {
                    continue;
                }
                let existing_keep: Option<i64> = tx
                    .query_row(
                        "SELECT photo_id FROM duplicate_group_members
                         WHERE group_id = ?1 AND is_suggested_keep = TRUE
                         LIMIT 1",
                        params![group_id],
                        |row| row.get(0),
                    )
                    .ok();
                let keep = existing_keep
                    .filter(|id| photo_ids.contains(id))
                    .or(*suggested_keep);

                tx.execute(
                    "DELETE FROM duplicate_group_members WHERE group_id = ?1",
                    params![group_id],
                )?;
                tx.execute(
                    "UPDATE duplicate_groups SET duplicate_type = ?2 WHERE id = ?1",
                    params![group_id, dup_type],
                )?;
                insert_duplicate_members(&tx, group_id, photo_ids, keep)?;
                continue;
            }

            // Create new duplicate group. Use the tx handle (already open
            // above); nesting unchecked_transaction would conflict.
            tx.execute(
                r#"
                INSERT INTO duplicate_groups (group_hash, duplicate_type)
                VALUES (?1, ?2)
                "#,
                params![hash, dup_type],
            )?;

            let group_id = tx.last_insert_rowid();
            insert_duplicate_members(&tx, group_id, photo_ids, *suggested_keep)?;
        }

        // Remove groups whose hash no longer has duplicates
        for (hash, (group_id, _)) in &existing_hashes {
            if !seen_hashes.contains(hash) {
                tx.execute(
                    "DELETE FROM duplicate_group_members WHERE group_id = ?1",
                    params![group_id],
                )?;
                tx.execute(
                    "DELETE FROM duplicate_groups WHERE id = ?1",
                    params![group_id],
                )?;
            }
        }

        tx.commit()
    }

    /// Insert or refresh the supplied groups without deleting groups
    /// absent from this batch. Used by long-running detectors so
    /// already-found results can stream into the UI and survive cancel.
    pub fn upsert_duplicate_groups(
        &self,
        groups: &[(String, Vec<i64>, Option<i64>, &'static str)],
    ) -> SqliteResult<()> {
        use std::collections::HashMap;

        let mut existing_hashes: HashMap<String, (i64, bool)> = HashMap::new();
        {
            let mut stmt = self
                .conn
                .prepare("SELECT id, group_hash, resolved FROM duplicate_groups")?;
            let rows = stmt.query_map([], |row| {
                Ok((
                    row.get::<_, i64>(0)?,
                    row.get::<_, String>(1)?,
                    row.get::<_, bool>(2)?,
                ))
            })?;
            for row in rows {
                let (id, hash, resolved) = row?;
                existing_hashes.insert(hash, (id, resolved));
            }
        }

        let tx = self.conn.unchecked_transaction()?;
        for (hash, photo_ids, suggested_keep, dup_type) in groups {
            if let Some((group_id, resolved)) = existing_hashes.get(hash).copied() {
                if resolved {
                    continue;
                }
                let existing_keep: Option<i64> = tx
                    .query_row(
                        "SELECT photo_id FROM duplicate_group_members
                         WHERE group_id = ?1 AND is_suggested_keep = TRUE
                         LIMIT 1",
                        params![group_id],
                        |row| row.get(0),
                    )
                    .ok();
                let keep = existing_keep
                    .filter(|id| photo_ids.contains(id))
                    .or(*suggested_keep);
                tx.execute(
                    "DELETE FROM duplicate_group_members WHERE group_id = ?1",
                    params![group_id],
                )?;
                tx.execute(
                    "UPDATE duplicate_groups SET duplicate_type = ?2 WHERE id = ?1",
                    params![group_id, dup_type],
                )?;
                insert_duplicate_members(&tx, group_id, photo_ids, keep)?;
            } else {
                tx.execute(
                    "INSERT INTO duplicate_groups (group_hash, duplicate_type) VALUES (?1, ?2)",
                    params![hash, dup_type],
                )?;
                let group_id = tx.last_insert_rowid();
                insert_duplicate_members(&tx, group_id, photo_ids, *suggested_keep)?;
            }
        }
        tx.commit()
    }

    /// Get all duplicate groups with member counts and a cover thumbnail.
    ///
    /// The cover comes from the suggested-keep member when one is set,
    /// otherwise the lowest-id member. We pull the cover with a
    /// correlated sub-query so the result is one round-trip per listing.
    pub fn get_all_groups(&self) -> SqliteResult<Vec<DuplicateGroupRecord>> {
        self.get_groups(i64::MAX, 0)
    }

    pub fn get_groups(&self, limit: i64, offset: i64) -> SqliteResult<Vec<DuplicateGroupRecord>> {
        let mut stmt = self.conn.prepare(
            r#"
            SELECT
                dg.id,
                COUNT(p.id) AS member_count,
                (
                    SELECT p.thumbnail_path
                     FROM duplicate_group_members m
                      JOIN photos p ON p.id = m.photo_id
                     WHERE m.group_id = dg.id
                       AND p.is_trashed = FALSE
                  ORDER BY m.is_suggested_keep DESC, m.photo_id ASC
                     LIMIT 1
                ) AS cover_thumbnail_path,
                (
                    SELECT m.photo_id
                      FROM duplicate_group_members m
                      JOIN photos p ON p.id = m.photo_id
                     WHERE m.group_id = dg.id
                       AND p.is_trashed = FALSE
                  ORDER BY m.is_suggested_keep DESC, m.photo_id ASC
                     LIMIT 1
                ) AS cover_photo_id
            FROM duplicate_groups dg
            LEFT JOIN duplicate_group_members dgm ON dg.id = dgm.group_id
            LEFT JOIN photos p ON p.id = dgm.photo_id AND p.is_trashed = FALSE
            WHERE dg.resolved = FALSE
            GROUP BY dg.id
            HAVING COUNT(p.id) > 1
            ORDER BY member_count DESC
            LIMIT ?1 OFFSET ?2
            "#,
        )?;

        let rows = stmt.query_map(params![limit.max(0), offset.max(0)], |row| {
            Ok(DuplicateGroupRecord {
                id: row.get(0)?,
                member_count: row.get(1)?,
                cover_thumbnail_path: row.get(2)?,
                cover_photo_id: row.get(3)?,
                member_photo_ids: Vec::new(),
            })
        })?;

        let mut groups = Vec::new();
        for row in rows {
            groups.push(row?);
        }

        // Pull every member's photo_id per group — drives the
        // browseContext scope when the user clicks a duplicate's
        // thumb in the listing.
        if !groups.is_empty() {
            let placeholders = vec!["?"; groups.len()].join(",");
            let sql = format!(
                "SELECT m.group_id, m.photo_id
                   FROM duplicate_group_members m
                   JOIN photos p ON p.id = m.photo_id
                  WHERE m.group_id IN ({placeholders}) AND p.is_trashed = FALSE
               ORDER BY m.group_id, m.is_suggested_keep DESC, m.photo_id ASC"
            );
            let group_ids: Vec<i64> = groups.iter().map(|g| g.id).collect();
            let mut by_group: HashMap<i64, Vec<i64>> = HashMap::with_capacity(groups.len());
            let mut members_stmt = self.conn.prepare(&sql)?;
            let rows = members_stmt.query_map(params_from_iter(group_ids), |row| {
                Ok((row.get::<_, i64>(0)?, row.get::<_, i64>(1)?))
            })?;
            for row in rows {
                let (group_id, photo_id) = row?;
                by_group.entry(group_id).or_default().push(photo_id);
            }
            for group in &mut groups {
                group.member_photo_ids = by_group.remove(&group.id).unwrap_or_default();
            }
        }

        Ok(groups)
    }

    /// Get members of a specific group
    pub fn get_group_members(
        &self,
        group_id: i64,
    ) -> SqliteResult<Vec<DuplicateGroupMemberRecord>> {
        let mut stmt = self.conn.prepare(
            r#"
            SELECT 
                dgm.photo_id,
                dgm.is_suggested_keep,
                p.file_path,
                p.thumbnail_path,
                p.file_size,
                p.date_taken
            FROM duplicate_group_members dgm
            JOIN duplicate_groups dg ON dg.id = dgm.group_id
            JOIN photos p ON dgm.photo_id = p.id
            WHERE dgm.group_id = ?1 AND dg.resolved = FALSE AND p.is_trashed = FALSE
            ORDER BY dgm.is_suggested_keep DESC, p.date_taken ASC
            "#,
        )?;

        let rows = stmt.query_map(params![group_id], |row| {
            Ok(DuplicateGroupMemberRecord {
                photo_id: row.get(0)?,
                is_suggested_keep: row.get(1)?,
                file_path: row.get(2)?,
                thumbnail_path: row.get(3)?,
                file_size: row.get(4)?,
                date_taken: row.get(5)?,
            })
        })?;

        let mut members = Vec::new();
        for row in rows {
            members.push(row?);
        }

        Ok(members)
    }

    /// Mark a photo as the one to keep in a group (atomic)
    pub fn set_keep_photo(&self, group_id: i64, photo_id: i64) -> SqliteResult<()> {
        let tx = self.conn.unchecked_transaction()?;

        let exists: i64 = tx.query_row(
            "SELECT COUNT(*)
               FROM duplicate_group_members dgm
               JOIN duplicate_groups dg ON dg.id = dgm.group_id
              WHERE dgm.group_id = ?1
                AND dgm.photo_id = ?2
                AND dg.resolved = FALSE",
            params![group_id, photo_id],
            |row| row.get(0),
        )?;
        if exists == 0 {
            return Err(rusqlite::Error::QueryReturnedNoRows);
        }

        tx.execute(
            "UPDATE duplicate_group_members SET is_suggested_keep = FALSE WHERE group_id = ?1",
            params![group_id],
        )?;

        tx.execute(
            "UPDATE duplicate_group_members SET is_suggested_keep = TRUE WHERE group_id = ?1 AND photo_id = ?2",
            params![group_id, photo_id],
        )?;

        tx.commit()
    }

    /// Delete a duplicate group (after resolution)
    pub fn delete_group(&self, group_id: i64) -> SqliteResult<()> {
        self.conn.execute(
            "DELETE FROM duplicate_group_members WHERE group_id = ?1",
            params![group_id],
        )?;
        self.conn.execute(
            "DELETE FROM duplicate_groups WHERE id = ?1",
            params![group_id],
        )?;
        Ok(())
    }

    /// Mark a duplicate group as handled without deleting its hash.
    pub fn dismiss_group(&self, group_id: i64) -> SqliteResult<()> {
        let updated = self.conn.execute(
            "UPDATE duplicate_groups SET resolved = TRUE WHERE id = ?1",
            params![group_id],
        )?;
        if updated == 0 {
            return Err(rusqlite::Error::QueryReturnedNoRows);
        }
        Ok(())
    }

    /// Get photos to trash (all members except the one to keep)
    pub fn get_photos_to_trash(&self, group_id: i64) -> SqliteResult<Vec<i64>> {
        let mut stmt = self.conn.prepare(
            r#"
            SELECT dgm.photo_id
              FROM duplicate_group_members dgm
              JOIN duplicate_groups dg ON dg.id = dgm.group_id
              JOIN photos p ON p.id = dgm.photo_id
             WHERE dgm.group_id = ?1
               AND dgm.is_suggested_keep = FALSE
               AND dg.resolved = FALSE
               AND p.is_trashed = FALSE
            "#,
        )?;

        let rows = stmt.query_map(params![group_id], |row| row.get(0))?;

        let mut photo_ids = Vec::new();
        for row in rows {
            photo_ids.push(row?);
        }

        Ok(photo_ids)
    }
}

/// Batch-insert duplicate-group members via multi-row VALUES inside
/// the caller's transaction. ~3× faster than one INSERT per row for
/// large duplicate groups.
fn insert_duplicate_members(
    conn: &Connection,
    group_id: i64,
    photo_ids: &[i64],
    suggested_keep: Option<i64>,
) -> SqliteResult<()> {
    if photo_ids.is_empty() {
        return Ok(());
    }
    for chunk in photo_ids.chunks(MAX_ROWS_PER_INSERT) {
        let placeholders: String = (0..chunk.len())
            .map(|_| "(?, ?, ?)")
            .collect::<Vec<_>>()
            .join(", ");
        let sql = format!(
            "INSERT INTO duplicate_group_members (group_id, photo_id, is_suggested_keep) VALUES {}",
            placeholders
        );
        let mut params_vec: Vec<Box<dyn ToSql>> = Vec::with_capacity(chunk.len() * 3);
        for pid in chunk {
            let is_suggested = suggested_keep.map(|s| s == *pid).unwrap_or(false);
            params_vec.push(Box::new(group_id));
            params_vec.push(Box::new(*pid));
            params_vec.push(Box::new(is_suggested));
        }
        let params_refs: Vec<&dyn ToSql> = params_vec.iter().map(|b| b.as_ref()).collect();
        conn.execute(&sql, params_refs.as_slice())?;
    }
    Ok(())
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::db::create_schema;

    #[test]
    fn invalid_keep_photo_does_not_clear_existing_keep() {
        let conn = Connection::open_in_memory().unwrap();
        create_schema(&conn).unwrap();
        conn.execute(
            "INSERT INTO photos (id, file_path, file_name, file_hash, file_size)
             VALUES (1, 'a.jpg', 'a.jpg', 'a', 10),
                    (2, 'b.jpg', 'b.jpg', 'b', 10)",
            [],
        )
        .unwrap();

        let repo = DuplicateRepo::new(&conn);
        repo.sync_duplicate_groups(&[("group".into(), vec![1, 2], Some(1), "exact")])
            .unwrap();
        let group_id = repo.get_all_groups().unwrap()[0].id;

        assert!(repo.set_keep_photo(group_id, 999).is_err());

        let keep_id: i64 = conn
            .query_row(
                "SELECT photo_id FROM duplicate_group_members
                 WHERE group_id = ?1 AND is_suggested_keep = TRUE",
                params![group_id],
                |row| row.get(0),
            )
            .unwrap();
        assert_eq!(keep_id, 1);
    }

    #[test]
    fn dismissed_group_does_not_reappear_on_next_detection() {
        let conn = Connection::open_in_memory().unwrap();
        create_schema(&conn).unwrap();
        conn.execute(
            "INSERT INTO photos (id, file_path, file_name, file_hash, file_size)
             VALUES (1, 'a.jpg', 'a.jpg', 'a', 10),
                    (2, 'b.jpg', 'b.jpg', 'b', 10)",
            [],
        )
        .unwrap();

        let repo = DuplicateRepo::new(&conn);
        let detected = [("group".into(), vec![1, 2], Some(1), "exact")];
        repo.sync_duplicate_groups(&detected).unwrap();
        let group_id = repo.get_all_groups().unwrap()[0].id;

        repo.dismiss_group(group_id).unwrap();
        assert!(repo.get_all_groups().unwrap().is_empty());

        repo.upsert_duplicate_groups(&detected).unwrap();
        assert!(repo.get_all_groups().unwrap().is_empty());

        repo.sync_duplicate_groups(&detected).unwrap();
        assert!(repo.get_all_groups().unwrap().is_empty());
    }

    #[test]
    fn dismissed_group_cannot_be_mutated_or_trashed_from_stale_detail() {
        let conn = Connection::open_in_memory().unwrap();
        create_schema(&conn).unwrap();
        conn.execute(
            "INSERT INTO photos (id, file_path, file_name, file_hash, file_size)
             VALUES (1, 'a.jpg', 'a.jpg', 'a', 10),
                    (2, 'b.jpg', 'b.jpg', 'b', 10)",
            [],
        )
        .unwrap();

        let repo = DuplicateRepo::new(&conn);
        repo.sync_duplicate_groups(&[("group".into(), vec![1, 2], Some(1), "exact")])
            .unwrap();
        let group_id = repo.get_all_groups().unwrap()[0].id;

        repo.dismiss_group(group_id).unwrap();

        assert!(repo.set_keep_photo(group_id, 2).is_err());
        assert!(repo.get_photos_to_trash(group_id).unwrap().is_empty());
    }
}