//! Virtual filesystem: manages VFS roots, directory trees, and sample links backed by SQLite.

use crate::db::Database;
use crate::error::{unix_now, CoreError, Result};
use crate::id_types::{NodeId, SampleHash, VfsId};
use tracing::instrument;

/// A virtual filesystem root (e.g. "Library", "Project A").
#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
pub struct Vfs {
    pub id: VfsId,
    pub name: String,
    pub created_at: i64,
    pub modified_at: i64,
    /// Whether audio file blobs for this VFS should be synced to cloud (metadata always syncs).
    pub sync_files: bool,
}

/// The two kinds of VFS node: directories and sample links.
#[derive(Debug, Clone, PartialEq, Eq, serde::Serialize, serde::Deserialize)]
pub enum NodeType {
    Directory,
    Sample,
}

impl NodeType {
    /// Return the SQLite-stored string representation (`"directory"` or `"sample"`).
    pub fn as_str(&self) -> &'static str {
        match self {
            NodeType::Directory => "directory",
            NodeType::Sample => "sample",
        }
    }

    /// Parse a stored string back into a `NodeType`. Defaults to `Directory` for unknown values.
    pub fn parse(s: &str) -> Self {
        match s {
            "sample" => NodeType::Sample,
            // Default to Directory rather than returning an error because the
            // schema only stores two values and a directory is the safe
            // fallback: it has no sample_hash, so the worst case is an empty
            // folder rather than a broken or missing node.
            _ => NodeType::Directory,
        }
    }
}

/// A single node in a VFS tree (directory or sample link).
#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
pub struct VfsNode {
    pub id: NodeId,
    pub vfs_id: VfsId,
    pub parent_id: Option<NodeId>,
    pub name: String,
    pub node_type: NodeType,
    pub sample_hash: Option<SampleHash>,
    pub created_at: i64,
}

// --- VFS roots ---

/// Create a new VFS root with the given name. Returns the new VFS ID.
#[instrument(skip_all)]
pub fn create_vfs(db: &Database, name: &str) -> Result<VfsId> {
    let now = unix_now();
    db.conn().execute(
        "INSERT INTO vfs (name, created_at, modified_at) VALUES (?1, ?2, ?3)",
        rusqlite::params![name, now, now],
    )?;
    Ok(VfsId::from(db.conn().last_insert_rowid()))
}

/// List all VFS roots, ordered alphabetically by name.
#[instrument(skip_all)]
pub fn list_vfs(db: &Database) -> Result<Vec<Vfs>> {
    let mut stmt = db
        .conn()
        .prepare("SELECT id, name, created_at, modified_at, sync_files FROM vfs ORDER BY name")?;
    let rows = stmt.query_map([], |row| {
        Ok(Vfs {
            id: row.get(0)?,
            name: row.get(1)?,
            created_at: row.get(2)?,
            modified_at: row.get(3)?,
            sync_files: row.get::<_, i32>(4)? != 0,
        })
    })?;
    Ok(rows.collect::<std::result::Result<Vec<_>, _>>()?)
}

/// Rename a VFS root. Returns `VfsNotFound` if the ID doesn't exist.
#[instrument(skip_all)]
pub fn rename_vfs(db: &Database, id: VfsId, new_name: &str) -> Result<()> {
    let now = unix_now();
    let changed = db.conn().execute(
        "UPDATE vfs SET name = ?1, modified_at = ?2 WHERE id = ?3",
        rusqlite::params![new_name, now, id],
    )?;
    if changed == 0 {
        return Err(CoreError::VfsNotFound(id));
    }
    Ok(())
}

/// Set whether a VFS root should sync its audio file blobs to cloud.
#[instrument(skip_all)]
pub fn set_vfs_sync_files(db: &Database, id: VfsId, enabled: bool) -> Result<()> {
    let changed = db.conn().execute(
        "UPDATE vfs SET sync_files = ?1 WHERE id = ?2",
        rusqlite::params![enabled as i32, id],
    )?;
    if changed == 0 {
        return Err(CoreError::VfsNotFound(id));
    }
    Ok(())
}

/// Get whether a VFS root has audio file blob syncing enabled.
#[instrument(skip_all)]
pub fn get_vfs_sync_files(db: &Database, id: VfsId) -> Result<bool> {
    db.conn()
        .query_row(
            "SELECT sync_files FROM vfs WHERE id = ?1",
            [id],
            |row| row.get::<_, i32>(0),
        )
        .map(|v| v != 0)
        .map_err(|e| match e {
            rusqlite::Error::QueryReturnedNoRows => CoreError::VfsNotFound(id),
            other => CoreError::Db(other),
        })
}

/// Delete a VFS root and all its nodes (cascaded by foreign key). Returns `VfsNotFound` if missing.
#[instrument(skip_all)]
pub fn delete_vfs(db: &Database, id: VfsId) -> Result<()> {
    let changed = db
        .conn()
        .execute("DELETE FROM vfs WHERE id = ?1", [id])?;
    if changed == 0 {
        return Err(CoreError::VfsNotFound(id));
    }
    Ok(())
}

// --- Nodes ---

/// Validate a VFS node name. Rejects empty names, path separators, reserved
/// names (`.` and `..`), and null bytes.
fn validate_node_name(name: &str) -> Result<()> {
    if name.is_empty() {
        return Err(CoreError::InvalidNodeName("name must not be empty".to_string()));
    }
    if name == "." || name == ".." {
        return Err(CoreError::InvalidNodeName(format!(
            "reserved name: {name}"
        )));
    }
    if name.contains('/') || name.contains('\\') {
        return Err(CoreError::InvalidNodeName(
            "name must not contain path separators (/ or \\)".to_string(),
        ));
    }
    if name.contains('\0') {
        return Err(CoreError::InvalidNodeName(
            "name must not contain null bytes".to_string(),
        ));
    }
    Ok(())
}

/// Check for name conflicts at root level (parent_id IS NULL) since SQLite
/// UNIQUE treats each NULL as distinct.
fn check_root_name_conflict(
    db: &Database,
    vfs_id: VfsId,
    parent_id: Option<NodeId>,
    name: &str,
) -> Result<()> {
    if parent_id.is_none() {
        let count: i64 = db.conn().query_row(
            "SELECT COUNT(*) FROM vfs_nodes WHERE vfs_id = ?1 AND parent_id IS NULL AND name = ?2",
            rusqlite::params![vfs_id, name],
            |row| row.get(0),
        )?;
        if count > 0 {
            return Err(CoreError::NameConflict(name.to_string()));
        }
    }
    Ok(())
}

/// Check for sibling name conflicts, handling both root (NULL parent) and
/// non-root cases. Excludes `exclude_id` so a node doesn't conflict with itself
/// (needed for rename).
fn check_sibling_name_conflict(
    db: &Database,
    vfs_id: VfsId,
    parent_id: Option<NodeId>,
    name: &str,
    exclude_id: NodeId,
) -> Result<()> {
    let count: i64 = match parent_id {
        None => db.conn().query_row(
            "SELECT COUNT(*) FROM vfs_nodes \
             WHERE vfs_id = ?1 AND parent_id IS NULL AND name = ?2 AND id != ?3",
            rusqlite::params![vfs_id, name, exclude_id],
            |row| row.get(0),
        )?,
        Some(pid) => db.conn().query_row(
            "SELECT COUNT(*) FROM vfs_nodes \
             WHERE parent_id = ?1 AND name = ?2 AND id != ?3",
            rusqlite::params![pid, name, exclude_id],
            |row| row.get(0),
        )?,
    };
    if count > 0 {
        return Err(CoreError::NameConflict(name.to_string()));
    }
    Ok(())
}

/// Create a directory node under the given parent (or at root if `None`). Returns the new node ID.
#[instrument(skip_all)]
pub fn create_directory(
    db: &Database,
    vfs_id: VfsId,
    parent_id: Option<NodeId>,
    name: &str,
) -> Result<NodeId> {
    validate_node_name(name)?;
    check_root_name_conflict(db, vfs_id, parent_id, name)?;
    let now = unix_now();
    db.conn().execute(
        "INSERT INTO vfs_nodes (vfs_id, parent_id, name, node_type, created_at)
         VALUES (?1, ?2, ?3, 'directory', ?4)",
        rusqlite::params![vfs_id, parent_id, name, now],
    )?;
    Ok(NodeId::from(db.conn().last_insert_rowid()))
}

/// Create a sample link node pointing to a content-addressed hash. Returns the new node ID.
#[instrument(skip_all)]
pub fn create_sample_link(
    db: &Database,
    vfs_id: VfsId,
    parent_id: Option<NodeId>,
    name: &str,
    sample_hash: &str,
) -> Result<NodeId> {
    validate_node_name(name)?;
    check_root_name_conflict(db, vfs_id, parent_id, name)?;
    let now = unix_now();
    db.conn().execute(
        "INSERT INTO vfs_nodes (vfs_id, parent_id, name, node_type, sample_hash, created_at)
         VALUES (?1, ?2, ?3, 'sample', ?4, ?5)",
        rusqlite::params![vfs_id, parent_id, name, sample_hash, now],
    )?;
    Ok(NodeId::from(db.conn().last_insert_rowid()))
}

/// List direct children of a directory (or root if `parent_id` is `None`), sorted directories-first then by name.
#[instrument(skip_all)]
pub fn list_children(
    db: &Database,
    vfs_id: VfsId,
    parent_id: Option<NodeId>,
) -> Result<Vec<VfsNode>> {
    let mut stmt = db.conn().prepare(
        "SELECT id, vfs_id, parent_id, name, node_type, sample_hash, created_at
         FROM vfs_nodes
         WHERE vfs_id = ?1 AND parent_id IS ?2
         ORDER BY node_type ASC, name ASC",
    )?;
    let rows = stmt.query_map(rusqlite::params![vfs_id, parent_id], |row| {
        let nt: String = row.get(4)?;
        Ok(VfsNode {
            id: row.get(0)?,
            vfs_id: row.get(1)?,
            parent_id: row.get(2)?,
            name: row.get(3)?,
            node_type: NodeType::parse(&nt),
            sample_hash: row.get(5)?,
            created_at: row.get(6)?,
        })
    })?;
    Ok(rows.collect::<std::result::Result<Vec<_>, _>>()?)
}

/// Fetch a single VFS node by ID. Returns `NodeNotFound` if absent.
#[instrument(skip_all)]
pub fn get_node(db: &Database, id: NodeId) -> Result<VfsNode> {
    db.conn()
        .query_row(
            "SELECT id, vfs_id, parent_id, name, node_type, sample_hash, created_at
             FROM vfs_nodes WHERE id = ?1",
            [id],
            |row| {
                let nt: String = row.get(4)?;
                Ok(VfsNode {
                    id: row.get(0)?,
                    vfs_id: row.get(1)?,
                    parent_id: row.get(2)?,
                    name: row.get(3)?,
                    node_type: NodeType::parse(&nt),
                    sample_hash: row.get(5)?,
                    created_at: row.get(6)?,
                })
            },
        )
        .map_err(|e| match e {
            rusqlite::Error::QueryReturnedNoRows => CoreError::NodeNotFound(id),
            other => CoreError::Db(other),
        })
}

/// Rename a VFS node. Returns `NodeNotFound` if the ID doesn't exist,
/// `NameConflict` if a sibling with the same name already exists.
#[instrument(skip_all)]
pub fn rename_node(db: &Database, id: NodeId, new_name: &str) -> Result<()> {
    validate_node_name(new_name)?;
    let node = get_node(db, id)?;
    check_sibling_name_conflict(db, node.vfs_id, node.parent_id, new_name, id)?;
    let changed = db.conn().execute(
        "UPDATE vfs_nodes SET name = ?1 WHERE id = ?2",
        rusqlite::params![new_name, id],
    )?;
    if changed == 0 {
        return Err(CoreError::NodeNotFound(id));
    }
    Ok(())
}

/// Move a VFS node to a new parent directory (or root if `None`).
///
/// Returns an error if the move would create a circular parent reference,
/// cross a VFS boundary, or conflict with an existing sibling name.
#[instrument(skip_all)]
pub fn move_node(db: &Database, id: NodeId, new_parent_id: Option<NodeId>) -> Result<()> {
    let node = get_node(db, id)?;

    // Reject cross-VFS moves.
    if let Some(parent) = new_parent_id {
        let parent_node = get_node(db, parent)?;
        if parent_node.vfs_id != node.vfs_id {
            return Err(CoreError::Internal(
                "cannot move a node to a different VFS".to_string(),
            ));
        }
    }

    // Check for circular reference: walk from new_parent_id up to root.
    // If we encounter `id` along the way, the move would create a cycle.
    if let Some(parent) = new_parent_id {
        let mut current = Some(parent);
        while let Some(cur_id) = current {
            if cur_id == id {
                return Err(CoreError::Internal(
                    "move would create a circular parent reference".to_string(),
                ));
            }
            let cur_node = get_node(db, cur_id)?;
            current = cur_node.parent_id;
        }
    }

    // Check for name conflicts at the destination.
    check_sibling_name_conflict(db, node.vfs_id, new_parent_id, &node.name, id)?;

    let changed = db.conn().execute(
        "UPDATE vfs_nodes SET parent_id = ?1 WHERE id = ?2",
        rusqlite::params![new_parent_id, id],
    )?;
    if changed == 0 {
        return Err(CoreError::NodeNotFound(id));
    }
    Ok(())
}

/// Delete a VFS node. Child nodes are removed by `ON DELETE CASCADE`.
#[instrument(skip_all)]
pub fn delete_node(db: &Database, id: NodeId) -> Result<()> {
    let changed = db
        .conn()
        .execute("DELETE FROM vfs_nodes WHERE id = ?1", [id])?;
    if changed == 0 {
        return Err(CoreError::NodeNotFound(id));
    }
    Ok(())
}

/// A VFS node enriched with analysis data for display in the file list.
#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
pub struct VfsNodeWithAnalysis {
    pub node: VfsNode,
    pub bpm: Option<f64>,
    pub musical_key: Option<String>,
    pub duration: Option<f64>,
    pub classification: Option<String>,
    pub peak_db: Option<f64>,
    pub is_loop: Option<bool>,
    pub cloud_only: bool,
    pub tags: Vec<String>,
}

/// Map a SQLite row from the standard enriched query
/// (vfs_nodes LEFT JOIN audio_analysis LEFT JOIN samples).
/// Expected column order:
///   0: id, 1: vfs_id, 2: parent_id, 3: name, 4: node_type,
///   5: sample_hash, 6: created_at, 7: bpm, 8: musical_key,
///   9: duration, 10: classification, 11: peak_db, 12: is_loop,
///   13: cloud_only.
pub fn map_enriched_row(row: &rusqlite::Row) -> rusqlite::Result<VfsNodeWithAnalysis> {
    let nt: String = row.get(4)?;
    let cloud_only_raw: Option<i32> = row.get(13)?;
    Ok(VfsNodeWithAnalysis {
        node: VfsNode {
            id: row.get(0)?,
            vfs_id: row.get(1)?,
            parent_id: row.get(2)?,
            name: row.get(3)?,
            node_type: NodeType::parse(&nt),
            sample_hash: row.get(5)?,
            created_at: row.get(6)?,
        },
        bpm: row.get(7)?,
        musical_key: row.get(8)?,
        duration: row.get(9)?,
        classification: row.get(10)?,
        peak_db: row.get(11)?,
        is_loop: row.get(12)?,
        cloud_only: cloud_only_raw.unwrap_or(0) != 0,
        tags: Vec::new(), // filled separately if needed
    })
}

/// List children with analysis data joined in.
#[instrument(skip_all)]
pub fn list_children_enriched(
    db: &Database,
    vfs_id: VfsId,
    parent_id: Option<NodeId>,
) -> Result<Vec<VfsNodeWithAnalysis>> {
    let mut stmt = db.conn().prepare(
        "SELECT n.id, n.vfs_id, n.parent_id, n.name, n.node_type, n.sample_hash, n.created_at,
                a.bpm, a.musical_key, COALESCE(a.duration, s.duration), a.classification, a.peak_db, a.is_loop,
                s.cloud_only
         FROM vfs_nodes n
         LEFT JOIN audio_analysis a ON n.sample_hash = a.hash
         LEFT JOIN samples s ON n.sample_hash = s.hash
         WHERE n.vfs_id = ?1 AND n.parent_id IS ?2
            AND s.deleted_at IS NULL
         ORDER BY n.node_type ASC, n.name ASC",
    )?;
    let rows = stmt.query_map(rusqlite::params![vfs_id, parent_id], map_enriched_row)?;
    Ok(rows.collect::<std::result::Result<Vec<_>, _>>()?)
}

/// Recursively collect a node and all its descendants (for undo snapshot before delete).
#[instrument(skip_all)]
pub fn collect_subtree(db: &Database, node_id: NodeId) -> Result<Vec<VfsNode>> {
    let mut stmt = db.conn().prepare(
        "WITH RECURSIVE subtree(id) AS (
            SELECT ?1
            UNION ALL
            SELECT n.id FROM vfs_nodes n JOIN subtree s ON n.parent_id = s.id
        )
        SELECT n.id, n.vfs_id, n.parent_id, n.name, n.node_type, n.sample_hash, n.created_at
        FROM vfs_nodes n
        JOIN subtree s ON n.id = s.id",
    )?;
    let rows = stmt.query_map([node_id], |row| {
        let nt: String = row.get(4)?;
        Ok(VfsNode {
            id: row.get(0)?,
            vfs_id: row.get(1)?,
            parent_id: row.get(2)?,
            name: row.get(3)?,
            node_type: NodeType::parse(&nt),
            sample_hash: row.get(5)?,
            created_at: row.get(6)?,
        })
    })?;
    Ok(rows.collect::<std::result::Result<Vec<_>, _>>()?)
}

/// List all directories in a VFS with full paths (for folder picker).
#[instrument(skip_all)]
pub fn list_all_directories(db: &Database, vfs_id: VfsId) -> Result<Vec<(NodeId, String)>> {
    let mut stmt = db.conn().prepare(
        "WITH RECURSIVE dir_paths(id, path) AS (
            SELECT id, name FROM vfs_nodes
            WHERE vfs_id = ?1 AND parent_id IS NULL AND node_type = 'directory'
            UNION ALL
            SELECT n.id, dp.path || '/' || n.name
            FROM vfs_nodes n
            JOIN dir_paths dp ON n.parent_id = dp.id
            WHERE n.node_type = 'directory'
        )
        SELECT id, path FROM dir_paths ORDER BY path",
    )?;
    let rows = stmt.query_map([vfs_id], |row| Ok((row.get(0)?, row.get(1)?)))?;
    Ok(rows.collect::<std::result::Result<Vec<_>, _>>()?)
}

/// A flattened VFS node with its full path from root, used by the mirror sync.
#[derive(Debug, Clone)]
pub struct FullTreeNode {
    /// Full path from VFS root, e.g. `"Library/Drums/Kicks/808 Deep.wav"`.
    pub path: String,
    pub node_type: NodeType,
    pub sample_hash: Option<SampleHash>,
}

/// List every node across all VFS roots with full reconstructed paths.
///
/// Returns a flat list sorted by path. Each path is prefixed with the VFS name
/// (e.g. `"Library/Drums/kick.wav"`). Used by the VFS mirror to build symlink trees.
#[instrument(skip_all)]
pub fn list_full_tree(db: &Database) -> Result<Vec<FullTreeNode>> {
    let mut stmt = db.conn().prepare(
        "WITH RECURSIVE tree(id, path, node_type, sample_hash) AS (
            SELECT n.id, v.name || '/' || n.name,
                   n.node_type, n.sample_hash
            FROM vfs_nodes n JOIN vfs v ON n.vfs_id = v.id
            WHERE n.parent_id IS NULL
            UNION ALL
            SELECT n.id, t.path || '/' || n.name,
                   n.node_type, n.sample_hash
            FROM vfs_nodes n JOIN tree t ON n.parent_id = t.id
        )
        SELECT path, node_type, sample_hash FROM tree ORDER BY path",
    )?;
    let rows = stmt.query_map([], |row| {
        let nt: String = row.get(1)?;
        Ok(FullTreeNode {
            path: row.get(0)?,
            node_type: NodeType::parse(&nt),
            sample_hash: row.get(2)?,
        })
    })?;
    Ok(rows.collect::<std::result::Result<Vec<_>, _>>()?)
}

/// Re-insert a previously deleted node (for undo). Preserves original ID.
#[instrument(skip_all)]
pub fn restore_node(db: &Database, node: &VfsNode) -> Result<()> {
    match node.node_type {
        NodeType::Directory => {
            db.conn().execute(
                "INSERT OR IGNORE INTO vfs_nodes (id, vfs_id, parent_id, name, node_type, created_at)
                 VALUES (?1, ?2, ?3, ?4, 'directory', ?5)",
                rusqlite::params![node.id, node.vfs_id, node.parent_id, node.name, node.created_at],
            )?;
        }
        NodeType::Sample => {
            db.conn().execute(
                "INSERT OR IGNORE INTO vfs_nodes (id, vfs_id, parent_id, name, node_type, sample_hash, created_at)
                 VALUES (?1, ?2, ?3, ?4, 'sample', ?5, ?6)",
                rusqlite::params![node.id, node.vfs_id, node.parent_id, node.name, node.sample_hash, node.created_at],
            )?;
        }
    }
    Ok(())
}

/// Walk from a node up to the VFS root, returning the path root→node.
#[instrument(skip_all)]
pub fn get_breadcrumb(db: &Database, node_id: NodeId) -> Result<Vec<VfsNode>> {
    let mut path = Vec::new();
    let mut current_id = Some(node_id);

    while let Some(id) = current_id {
        let node = get_node(db, id)?;
        current_id = node.parent_id;
        path.push(node);
    }

    path.reverse();
    Ok(path)
}

/// Find VFS nodes by sample hashes within a specific VFS, preserving the input order.
/// Returns one node per hash (the first match if duplicates exist).
#[instrument(skip_all)]
pub fn find_nodes_by_hashes(
    db: &Database,
    vfs_id: VfsId,
    hashes: &[&str],
) -> Result<Vec<VfsNodeWithAnalysis>> {
    if hashes.is_empty() {
        return Ok(Vec::new());
    }
    let placeholders: Vec<String> = hashes
        .iter()
        .enumerate()
        .map(|(i, _)| format!("?{}", i + 2))
        .collect();
    let sql = format!(
        "SELECT n.id, n.vfs_id, n.parent_id, n.name, n.node_type, n.sample_hash, n.created_at,
                a.bpm, a.musical_key, COALESCE(a.duration, s.duration), a.classification, a.peak_db, a.is_loop,
                s.cloud_only
         FROM vfs_nodes n
         LEFT JOIN audio_analysis a ON n.sample_hash = a.hash
         LEFT JOIN samples s ON n.sample_hash = s.hash
         WHERE n.vfs_id = ?1 AND n.sample_hash IN ({})
            AND s.deleted_at IS NULL
         GROUP BY n.sample_hash",
        placeholders.join(", ")
    );
    let mut params: Vec<Box<dyn rusqlite::types::ToSql>> = Vec::with_capacity(hashes.len() + 1);
    params.push(Box::new(vfs_id));
    for h in hashes {
        params.push(Box::new(h.to_string()));
    }
    let mut stmt = db.conn().prepare(&sql)?;
    let rows = stmt.query_map(
        params.iter().map(|p| p.as_ref()).collect::<Vec<_>>().as_slice(),
        map_enriched_row,
    )?;
    let found: Vec<VfsNodeWithAnalysis> = rows.collect::<std::result::Result<Vec<_>, _>>()?;

    // Reorder to match input hash order
    let mut by_hash: std::collections::HashMap<SampleHash, VfsNodeWithAnalysis> =
        std::collections::HashMap::with_capacity(found.len());
    for node in found {
        if let Some(ref h) = node.node.sample_hash {
            by_hash.entry(h.clone()).or_insert(node);
        }
    }
    let mut ordered = Vec::with_capacity(hashes.len());
    for h in hashes {
        if let Some(node) = by_hash.remove(*h) {
            ordered.push(node);
        }
    }
    Ok(ordered)
}

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

    fn setup() -> Database {
        Database::open_in_memory().unwrap()
    }

    #[test]
    fn create_and_list_vfs() {
        let db = setup();
        let id = create_vfs(&db, "Library").unwrap();
        let list = list_vfs(&db).unwrap();
        assert_eq!(list.len(), 1);
        assert_eq!(list[0].id, id);
        assert_eq!(list[0].name, "Library");
    }

    #[test]
    fn rename_vfs_works() {
        let db = setup();
        let id = create_vfs(&db, "Old").unwrap();
        rename_vfs(&db, id, "New").unwrap();
        let list = list_vfs(&db).unwrap();
        assert_eq!(list[0].name, "New");
    }

    #[test]
    fn delete_vfs_cascades_nodes() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Test").unwrap();
        create_directory(&db, vfs_id, None, "folder").unwrap();

        delete_vfs(&db, vfs_id).unwrap();

        let count: i64 = db
            .conn()
            .query_row("SELECT COUNT(*) FROM vfs_nodes", [], |row| row.get(0))
            .unwrap();
        assert_eq!(count, 0);
    }

    #[test]
    fn directory_tree_crud() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();

        let dir_id = create_directory(&db, vfs_id, None, "Drums").unwrap();
        let sub_id = create_directory(&db, vfs_id, Some(dir_id), "Kicks").unwrap();

        let root_children = list_children(&db, vfs_id, None).unwrap();
        assert_eq!(root_children.len(), 1);
        assert_eq!(root_children[0].name, "Drums");

        let sub_children = list_children(&db, vfs_id, Some(dir_id)).unwrap();
        assert_eq!(sub_children.len(), 1);
        assert_eq!(sub_children[0].name, "Kicks");
        assert_eq!(sub_children[0].id, sub_id);
    }

    #[test]
    fn sample_links() {
        let db = setup();
        insert_fake_sample(&db, "abc123");
        let vfs_id = create_vfs(&db, "Lib").unwrap();

        let node_id =
            create_sample_link(&db, vfs_id, None, "kick.wav", "abc123").unwrap();
        let node = get_node(&db, node_id).unwrap();
        assert_eq!(node.node_type, NodeType::Sample);
        assert_eq!(node.sample_hash.as_deref(), Some("abc123"));
    }

    #[test]
    fn rename_and_move_node() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        let dir_a = create_directory(&db, vfs_id, None, "A").unwrap();
        let dir_b = create_directory(&db, vfs_id, None, "B").unwrap();
        let child = create_directory(&db, vfs_id, Some(dir_a), "Child").unwrap();

        rename_node(&db, child, "Renamed").unwrap();
        let node = get_node(&db, child).unwrap();
        assert_eq!(node.name, "Renamed");

        move_node(&db, child, Some(dir_b)).unwrap();
        let node = get_node(&db, child).unwrap();
        assert_eq!(node.parent_id, Some(dir_b));
    }

    #[test]
    fn delete_node_cascades() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        let parent = create_directory(&db, vfs_id, None, "Parent").unwrap();
        create_directory(&db, vfs_id, Some(parent), "Child").unwrap();

        delete_node(&db, parent).unwrap();

        let count: i64 = db
            .conn()
            .query_row("SELECT COUNT(*) FROM vfs_nodes", [], |row| row.get(0))
            .unwrap();
        assert_eq!(count, 0);
    }

    #[test]
    fn root_level_name_conflict() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        create_directory(&db, vfs_id, None, "Drums").unwrap();

        let result = create_directory(&db, vfs_id, None, "Drums");
        assert!(matches!(result, Err(CoreError::NameConflict(_))));
    }

    #[test]
    fn breadcrumb_trail() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        let a = create_directory(&db, vfs_id, None, "A").unwrap();
        let b = create_directory(&db, vfs_id, Some(a), "B").unwrap();
        let c = create_directory(&db, vfs_id, Some(b), "C").unwrap();

        let crumbs = get_breadcrumb(&db, c).unwrap();
        assert_eq!(crumbs.len(), 3);
        assert_eq!(crumbs[0].name, "A");
        assert_eq!(crumbs[1].name, "B");
        assert_eq!(crumbs[2].name, "C");
    }

    #[test]
    fn list_children_sorts_dirs_first() {
        let db = setup();
        insert_fake_sample(&db, "sample1");
        let vfs_id = create_vfs(&db, "Lib").unwrap();

        create_sample_link(&db, vfs_id, None, "zzz.wav", "sample1").unwrap();
        create_directory(&db, vfs_id, None, "AAA").unwrap();

        let children = list_children(&db, vfs_id, None).unwrap();
        assert_eq!(children[0].node_type, NodeType::Directory);
        assert_eq!(children[1].node_type, NodeType::Sample);
    }

    #[test]
    fn collect_subtree_flat() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        insert_fake_sample(&db, "s1");
        let dir = create_directory(&db, vfs_id, None, "Dir").unwrap();
        create_sample_link(&db, vfs_id, Some(dir), "s1.wav", "s1").unwrap();

        let subtree = collect_subtree(&db, dir).unwrap();
        assert_eq!(subtree.len(), 2); // dir + sample
        assert!(subtree.iter().any(|n| n.name == "Dir"));
        assert!(subtree.iter().any(|n| n.name == "s1.wav"));
    }

    #[test]
    fn collect_subtree_nested() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        let a = create_directory(&db, vfs_id, None, "A").unwrap();
        let b = create_directory(&db, vfs_id, Some(a), "B").unwrap();
        create_directory(&db, vfs_id, Some(b), "C").unwrap();

        let subtree = collect_subtree(&db, a).unwrap();
        assert_eq!(subtree.len(), 3);
        let names: Vec<&str> = subtree.iter().map(|n| n.name.as_str()).collect();
        assert!(names.contains(&"A"));
        assert!(names.contains(&"B"));
        assert!(names.contains(&"C"));
    }

    #[test]
    fn collect_subtree_empty_dir() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        let dir = create_directory(&db, vfs_id, None, "Empty").unwrap();

        let subtree = collect_subtree(&db, dir).unwrap();
        assert_eq!(subtree.len(), 1);
        assert_eq!(subtree[0].name, "Empty");
    }

    #[test]
    fn list_all_directories_works() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        let drums = create_directory(&db, vfs_id, None, "Drums").unwrap();
        create_directory(&db, vfs_id, Some(drums), "Kicks").unwrap();
        create_directory(&db, vfs_id, Some(drums), "Snares").unwrap();
        create_directory(&db, vfs_id, None, "Vocals").unwrap();

        let dirs = list_all_directories(&db, vfs_id).unwrap();
        let paths: Vec<&str> = dirs.iter().map(|(_, p)| p.as_str()).collect();
        assert_eq!(
            paths,
            vec!["Drums", "Drums/Kicks", "Drums/Snares", "Vocals"]
        );
    }

    #[test]
    fn list_all_directories_empty_vfs() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        let dirs = list_all_directories(&db, vfs_id).unwrap();
        assert!(dirs.is_empty());
    }

    #[test]
    fn enriched_query_includes_cloud_only_false() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        crate::test_helpers::insert_fake_sample(&db, "hash1");
        create_sample_link(&db, vfs_id, None, "kick.wav", "hash1").unwrap();

        let nodes = list_children_enriched(&db, vfs_id, None).unwrap();
        assert_eq!(nodes.len(), 1);
        assert!(!nodes[0].cloud_only);
    }

    #[test]
    fn enriched_query_includes_cloud_only_true() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        crate::test_helpers::insert_fake_sample(&db, "hash1");
        create_sample_link(&db, vfs_id, None, "kick.wav", "hash1").unwrap();

        // Set cloud_only=1 directly
        db.conn()
            .execute("UPDATE samples SET cloud_only = 1 WHERE hash = 'hash1'", [])
            .unwrap();

        let nodes = list_children_enriched(&db, vfs_id, None).unwrap();
        assert_eq!(nodes.len(), 1);
        assert!(nodes[0].cloud_only);
    }

    #[test]
    fn enriched_query_directory_cloud_only_false() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        create_directory(&db, vfs_id, None, "Drums").unwrap();

        let nodes = list_children_enriched(&db, vfs_id, None).unwrap();
        assert_eq!(nodes.len(), 1);
        // Directories have no sample_hash so cloud_only defaults to false
        assert!(!nodes[0].cloud_only);
    }

    #[test]
    fn find_nodes_by_hashes_returns_correct_results() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        insert_fake_sample(&db, "hash_a");
        insert_fake_sample(&db, "hash_b");
        insert_fake_sample(&db, "hash_c");
        create_sample_link(&db, vfs_id, None, "a.wav", "hash_a").unwrap();
        create_sample_link(&db, vfs_id, None, "b.wav", "hash_b").unwrap();
        create_sample_link(&db, vfs_id, None, "c.wav", "hash_c").unwrap();

        let results =
            find_nodes_by_hashes(&db, vfs_id, &["hash_b", "hash_a"]).unwrap();

        // Returns results in input order
        assert_eq!(results.len(), 2);
        assert_eq!(results[0].node.sample_hash.as_deref(), Some("hash_b"));
        assert_eq!(results[1].node.sample_hash.as_deref(), Some("hash_a"));
    }

    #[test]
    fn find_nodes_by_hashes_empty_input() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();

        let results = find_nodes_by_hashes(&db, vfs_id, &[]).unwrap();
        assert!(results.is_empty());
    }

    #[test]
    fn find_nodes_by_hashes_nonexistent_hash() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();

        let results =
            find_nodes_by_hashes(&db, vfs_id, &["nonexistent"]).unwrap();
        assert!(results.is_empty());
    }

    #[test]
    fn find_nodes_by_hashes_includes_cloud_only() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        insert_fake_sample(&db, "hash_cloud");
        create_sample_link(&db, vfs_id, None, "cloud.wav", "hash_cloud").unwrap();

        db.conn()
            .execute(
                "UPDATE samples SET cloud_only = 1 WHERE hash = 'hash_cloud'",
                [],
            )
            .unwrap();

        let results =
            find_nodes_by_hashes(&db, vfs_id, &["hash_cloud"]).unwrap();
        assert_eq!(results.len(), 1);
        assert!(results[0].cloud_only);
    }

    #[test]
    fn move_node_rejects_circular_parent_to_child() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        let a = create_directory(&db, vfs_id, None, "A").unwrap();
        let b = create_directory(&db, vfs_id, Some(a), "B").unwrap();
        let c = create_directory(&db, vfs_id, Some(b), "C").unwrap();

        // Moving A under C would create A -> B -> C -> A cycle
        let result = move_node(&db, a, Some(c));
        assert!(result.is_err());
        let err_msg = format!("{}", result.unwrap_err());
        assert!(err_msg.contains("circular"), "expected circular error, got: {err_msg}");
    }

    #[test]
    fn move_node_allows_valid_reparent() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        let a = create_directory(&db, vfs_id, None, "A").unwrap();
        let b = create_directory(&db, vfs_id, Some(a), "B").unwrap();
        let c = create_directory(&db, vfs_id, Some(b), "C").unwrap();

        // Moving C under A (skipping B) is valid — no cycle
        move_node(&db, c, Some(a)).unwrap();
        let node = get_node(&db, c).unwrap();
        assert_eq!(node.parent_id, Some(a));
    }

    #[test]
    fn move_node_to_root_succeeds() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        let a = create_directory(&db, vfs_id, None, "A").unwrap();
        let b = create_directory(&db, vfs_id, Some(a), "B").unwrap();

        // Moving A to root is always valid
        move_node(&db, a, None).unwrap();
        let node = get_node(&db, a).unwrap();
        assert_eq!(node.parent_id, None);

        // Moving B to root is also valid
        move_node(&db, b, None).unwrap();
        let node = get_node(&db, b).unwrap();
        assert_eq!(node.parent_id, None);
    }

    #[test]
    fn move_node_rejects_self_as_parent() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        let a = create_directory(&db, vfs_id, None, "A").unwrap();

        // Moving A under itself creates a trivial cycle
        let result = move_node(&db, a, Some(a));
        assert!(result.is_err());
    }

    // --- Node name validation tests ---

    #[test]
    fn validate_node_name_accepts_valid_names() {
        assert!(validate_node_name("kick.wav").is_ok());
        assert!(validate_node_name("My Folder").is_ok());
        assert!(validate_node_name("drums-2024").is_ok());
        assert!(validate_node_name("a").is_ok());
        assert!(validate_node_name("...").is_ok()); // three dots is fine
    }

    #[test]
    fn validate_node_name_rejects_empty() {
        let result = validate_node_name("");
        assert!(matches!(result, Err(CoreError::InvalidNodeName(_))));
    }

    #[test]
    fn validate_node_name_rejects_dot() {
        let result = validate_node_name(".");
        assert!(matches!(result, Err(CoreError::InvalidNodeName(_))));
    }

    #[test]
    fn validate_node_name_rejects_dotdot() {
        let result = validate_node_name("..");
        assert!(matches!(result, Err(CoreError::InvalidNodeName(_))));
    }

    #[test]
    fn validate_node_name_rejects_forward_slash() {
        let result = validate_node_name("foo/bar");
        assert!(matches!(result, Err(CoreError::InvalidNodeName(_))));
    }

    #[test]
    fn validate_node_name_rejects_backslash() {
        let result = validate_node_name("foo\\bar");
        assert!(matches!(result, Err(CoreError::InvalidNodeName(_))));
    }

    #[test]
    fn validate_node_name_rejects_null_byte() {
        let result = validate_node_name("foo\0bar");
        assert!(matches!(result, Err(CoreError::InvalidNodeName(_))));
    }

    #[test]
    fn create_directory_rejects_invalid_name() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        let result = create_directory(&db, vfs_id, None, "..");
        assert!(matches!(result, Err(CoreError::InvalidNodeName(_))));
    }

    #[test]
    fn create_sample_link_rejects_invalid_name() {
        let db = setup();
        insert_fake_sample(&db, "hash1");
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        let result = create_sample_link(&db, vfs_id, None, "foo/bar.wav", "hash1");
        assert!(matches!(result, Err(CoreError::InvalidNodeName(_))));
    }

    #[test]
    fn rename_node_rejects_invalid_name() {
        let db = setup();
        let vfs_id = create_vfs(&db, "Lib").unwrap();
        let dir = create_directory(&db, vfs_id, None, "Valid").unwrap();
        let result = rename_node(&db, dir, "");
        assert!(matches!(result, Err(CoreError::InvalidNodeName(_))));
    }

    // --- list_full_tree tests ---

    #[test]
    fn list_full_tree_empty_vfs() {
        let db = setup();
        create_vfs(&db, "Lib").unwrap();
        let tree = list_full_tree(&db).unwrap();
        assert!(tree.is_empty());
    }

    #[test]
    fn list_full_tree_builds_paths() {
        let db = setup();
        insert_fake_sample(&db, "s1");
        let vfs_id = create_vfs(&db, "Library").unwrap();
        let drums = create_directory(&db, vfs_id, None, "Drums").unwrap();
        let kicks = create_directory(&db, vfs_id, Some(drums), "Kicks").unwrap();
        create_sample_link(&db, vfs_id, Some(kicks), "808.wav", "s1").unwrap();

        let tree = list_full_tree(&db).unwrap();
        let paths: Vec<&str> = tree.iter().map(|n| n.path.as_str()).collect();
        assert_eq!(
            paths,
            vec![
                "Library/Drums",
                "Library/Drums/Kicks",
                "Library/Drums/Kicks/808.wav",
            ]
        );
        // Last node is a sample with the correct hash
        assert_eq!(tree[2].node_type, NodeType::Sample);
        assert_eq!(tree[2].sample_hash.as_deref(), Some("s1"));
    }

    #[test]
    fn list_full_tree_multiple_vfs() {
        let db = setup();
        insert_fake_sample(&db, "s1");
        let v1 = create_vfs(&db, "Alpha").unwrap();
        let v2 = create_vfs(&db, "Beta").unwrap();
        create_directory(&db, v1, None, "Dir1").unwrap();
        create_sample_link(&db, v2, None, "sample.wav", "s1").unwrap();

        let tree = list_full_tree(&db).unwrap();
        let paths: Vec<&str> = tree.iter().map(|n| n.path.as_str()).collect();
        assert_eq!(paths, vec!["Alpha/Dir1", "Beta/sample.wav"]);
    }
}