//! Collections: named groups of samples (manual or dynamic/saved-search).

use crate::db::Database;
use crate::error::{unix_now, CoreError, Result};
use crate::id_types::CollectionId;
use crate::search::SearchFilter;
use tracing::instrument;

/// A named collection of samples.
///
/// When `filter` is `None`, the collection is manual (explicit membership).
/// When `filter` is `Some`, the collection is dynamic (re-computed from the filter on access).
#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
pub struct Collection {
    pub id: CollectionId,
    pub name: String,
    pub description: Option<String>,
    pub created_at: i64,
    pub member_count: usize,
    /// Dynamic filter — if set, this collection is a saved search.
    pub filter: Option<SearchFilter>,
}

impl Collection {
    /// Returns true if this is a dynamic (saved search) collection.
    pub fn is_dynamic(&self) -> bool {
        self.filter.is_some()
    }
}

/// Create a manual collection, returning its ID.
#[instrument(skip_all)]
pub fn create_collection(
    db: &Database,
    name: &str,
    description: Option<&str>,
) -> Result<CollectionId> {
    let now = unix_now();
    db.conn().execute(
        "INSERT INTO collections (name, description, created_at) VALUES (?1, ?2, ?3)",
        rusqlite::params![name, description, now],
    )?;
    Ok(CollectionId::from(db.conn().last_insert_rowid()))
}

/// Create a dynamic collection (saved search) with a filter, returning its ID.
#[instrument(skip_all)]
pub fn create_dynamic_collection(
    db: &Database,
    name: &str,
    filter: &SearchFilter,
) -> Result<CollectionId> {
    let now = unix_now();
    let filter_json = serde_json::to_string(filter)
        .map_err(|e| CoreError::Internal(format!("Failed to serialize filter: {e}")))?;
    db.conn().execute(
        "INSERT INTO collections (name, created_at, filter_json) VALUES (?1, ?2, ?3)",
        rusqlite::params![name, now, filter_json],
    )?;
    Ok(CollectionId::from(db.conn().last_insert_rowid()))
}

/// Update the filter on a dynamic collection.
#[instrument(skip_all)]
pub fn update_collection_filter(
    db: &Database,
    id: CollectionId,
    filter: &SearchFilter,
) -> Result<()> {
    let filter_json = serde_json::to_string(filter)
        .map_err(|e| CoreError::Internal(format!("Failed to serialize filter: {e}")))?;
    let changed = db.conn().execute(
        "UPDATE collections SET filter_json = ?1 WHERE id = ?2",
        rusqlite::params![filter_json, id],
    )?;
    if changed == 0 {
        return Err(CoreError::CollectionNotFound(id));
    }
    Ok(())
}

/// List all collections with member counts, ordered by name.
#[instrument(skip_all)]
pub fn list_collections(db: &Database) -> Result<Vec<Collection>> {
    let mut stmt = db.conn().prepare(
        "SELECT c.id, c.name, c.description, c.created_at, COUNT(cm.sample_hash), c.filter_json
         FROM collections c
         LEFT JOIN collection_members cm ON cm.collection_id = c.id
         GROUP BY c.id
         ORDER BY c.name ASC",
    )?;
    let rows = stmt.query_map([], |row| {
        let filter_json: Option<String> = row.get(5)?;
        let filter = filter_json.and_then(|json| {
            match serde_json::from_str(&json) {
                Ok(f) => Some(f),
                Err(e) => {
                    tracing::warn!("Malformed filter JSON in collection: {e}");
                    None
                }
            }
        });
        Ok(Collection {
            id: row.get(0)?,
            name: row.get(1)?,
            description: row.get(2)?,
            created_at: row.get(3)?,
            member_count: row.get::<_, i64>(4)?.max(0) as usize,
            filter,
        })
    })?;
    Ok(rows.collect::<std::result::Result<Vec<_>, _>>()?)
}

/// Rename a collection.
#[instrument(skip_all)]
pub fn rename_collection(db: &Database, id: CollectionId, new_name: &str) -> Result<()> {
    let changed = db.conn().execute(
        "UPDATE collections SET name = ?1 WHERE id = ?2",
        rusqlite::params![new_name, id],
    )?;
    if changed == 0 {
        return Err(CoreError::CollectionNotFound(id));
    }
    Ok(())
}

/// Delete a collection by ID. Members are cascaded by the FK constraint.
#[instrument(skip_all)]
pub fn delete_collection(db: &Database, id: CollectionId) -> Result<()> {
    let changed = db.conn().execute(
        "DELETE FROM collections WHERE id = ?1",
        rusqlite::params![id],
    )?;
    if changed == 0 {
        return Err(CoreError::CollectionNotFound(id));
    }
    Ok(())
}

/// Add a sample to a collection. No-op if already a member.
#[instrument(skip_all)]
pub fn add_to_collection(db: &Database, collection_id: CollectionId, sample_hash: &str) -> Result<()> {
    let now = unix_now();
    db.conn().execute(
        "INSERT OR IGNORE INTO collection_members (collection_id, sample_hash, added_at) VALUES (?1, ?2, ?3)",
        rusqlite::params![collection_id, sample_hash, now],
    )?;
    Ok(())
}

/// Remove a sample from a collection.
#[instrument(skip_all)]
pub fn remove_from_collection(db: &Database, collection_id: CollectionId, sample_hash: &str) -> Result<()> {
    db.conn().execute(
        "DELETE FROM collection_members WHERE collection_id = ?1 AND sample_hash = ?2",
        rusqlite::params![collection_id, sample_hash],
    )?;
    Ok(())
}

/// List all sample hashes in a collection.
#[instrument(skip_all)]
pub fn list_collection_members(db: &Database, collection_id: CollectionId) -> Result<Vec<String>> {
    let mut stmt = db.conn().prepare(
        "SELECT sample_hash FROM collection_members WHERE collection_id = ?1 ORDER BY added_at ASC",
    )?;
    let rows = stmt.query_map([collection_id], |row| row.get::<_, String>(0))?;
    Ok(rows.collect::<std::result::Result<Vec<_>, _>>()?)
}

/// Get all collections that contain a given sample.
#[instrument(skip_all)]
pub fn get_sample_collections(db: &Database, sample_hash: &str) -> Result<Vec<Collection>> {
    let mut stmt = db.conn().prepare(
        "SELECT c.id, c.name, c.description, c.created_at, COUNT(cm2.sample_hash), c.filter_json
         FROM collections c
         INNER JOIN collection_members cm ON cm.collection_id = c.id AND cm.sample_hash = ?1
         LEFT JOIN collection_members cm2 ON cm2.collection_id = c.id
         GROUP BY c.id
         ORDER BY c.name ASC",
    )?;
    let rows = stmt.query_map([sample_hash], |row| {
        let filter_json: Option<String> = row.get(5)?;
        let filter = filter_json.and_then(|json| {
            match serde_json::from_str(&json) {
                Ok(f) => Some(f),
                Err(e) => {
                    tracing::warn!("Malformed filter JSON in collection: {e}");
                    None
                }
            }
        });
        Ok(Collection {
            id: row.get(0)?,
            name: row.get(1)?,
            description: row.get(2)?,
            created_at: row.get(3)?,
            member_count: row.get::<_, i64>(4)?.max(0) as usize,
            filter,
        })
    })?;
    Ok(rows.collect::<std::result::Result<Vec<_>, _>>()?)
}

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

    fn setup() -> Database {
        let db = Database::open_in_memory().unwrap();
        // Insert a fake sample for FK constraints
        db.conn()
            .execute(
                "INSERT INTO samples (hash, original_name, file_extension, file_size, import_date, last_modified)
                 VALUES ('aaa', 'kick.wav', 'wav', 100, 0, 0)",
                [],
            )
            .unwrap();
        db.conn()
            .execute(
                "INSERT INTO samples (hash, original_name, file_extension, file_size, import_date, last_modified)
                 VALUES ('bbb', 'snare.wav', 'wav', 200, 0, 0)",
                [],
            )
            .unwrap();
        db
    }

    #[test]
    fn create_and_list() {
        let db = setup();
        let id = create_collection(&db, "Favorites", Some("My best samples")).unwrap();
        assert!(id.as_i64() > 0);

        let collections = list_collections(&db).unwrap();
        assert_eq!(collections.len(), 1);
        assert_eq!(collections[0].name, "Favorites");
        assert_eq!(collections[0].description.as_deref(), Some("My best samples"));
        assert_eq!(collections[0].member_count, 0);
    }

    #[test]
    fn rename() {
        let db = setup();
        let id = create_collection(&db, "Old", None).unwrap();
        rename_collection(&db, id, "New").unwrap();
        let collections = list_collections(&db).unwrap();
        assert_eq!(collections[0].name, "New");
    }

    #[test]
    fn delete() {
        let db = setup();
        let id = create_collection(&db, "Temp", None).unwrap();
        delete_collection(&db, id).unwrap();
        assert!(list_collections(&db).unwrap().is_empty());
    }

    #[test]
    fn add_remove_members() {
        let db = setup();
        let id = create_collection(&db, "Test", None).unwrap();

        add_to_collection(&db, id, "aaa").unwrap();
        add_to_collection(&db, id, "bbb").unwrap();

        let members = list_collection_members(&db, id).unwrap();
        assert_eq!(members.len(), 2);

        // member_count reflects in list
        let collections = list_collections(&db).unwrap();
        assert_eq!(collections[0].member_count, 2);

        remove_from_collection(&db, id, "aaa").unwrap();
        let members = list_collection_members(&db, id).unwrap();
        assert_eq!(members.len(), 1);
        assert_eq!(members[0], "bbb");
    }

    #[test]
    fn duplicate_add_is_noop() {
        let db = setup();
        let id = create_collection(&db, "Test", None).unwrap();
        add_to_collection(&db, id, "aaa").unwrap();
        add_to_collection(&db, id, "aaa").unwrap(); // no error
        let members = list_collection_members(&db, id).unwrap();
        assert_eq!(members.len(), 1);
    }

    #[test]
    fn nonexistent_errors() {
        let db = setup();
        let bad_id = CollectionId::from(9999);
        assert!(rename_collection(&db, bad_id, "X").is_err());
        assert!(delete_collection(&db, bad_id).is_err());
    }

    #[test]
    fn get_sample_collections_works() {
        let db = setup();
        let c1 = create_collection(&db, "Alpha", None).unwrap();
        let c2 = create_collection(&db, "Beta", None).unwrap();
        add_to_collection(&db, c1, "aaa").unwrap();
        add_to_collection(&db, c2, "aaa").unwrap();
        add_to_collection(&db, c2, "bbb").unwrap();

        let sample_colls = get_sample_collections(&db, "aaa").unwrap();
        assert_eq!(sample_colls.len(), 2);

        let sample_colls_b = get_sample_collections(&db, "bbb").unwrap();
        assert_eq!(sample_colls_b.len(), 1);
        assert_eq!(sample_colls_b[0].name, "Beta");
    }

    #[test]
    fn delete_cascades_members() {
        let db = setup();
        let id = create_collection(&db, "Test", None).unwrap();
        add_to_collection(&db, id, "aaa").unwrap();
        delete_collection(&db, id).unwrap();
        // members table should be clean — verify by creating a new collection
        // with the same name and checking it has no members
        let id2 = create_collection(&db, "Test", None).unwrap();
        assert_eq!(list_collection_members(&db, id2).unwrap().len(), 0);
    }
}