heroforge-core 0.2.2

use rusqlite::{Connection, OpenFlags, params};
use std::collections::HashMap;
use std::path::Path;

use crate::error::{FossilError, Result};

pub struct Database {
    conn: Connection,
}

impl Database {
    /// Open a Heroforge repository database (read-only)
    pub fn open<P: AsRef<Path>>(path: P) -> Result<Self> {
        let conn = Connection::open_with_flags(
            path,
            OpenFlags::SQLITE_OPEN_READ_ONLY | OpenFlags::SQLITE_OPEN_NO_MUTEX,
        )?;

        // Verify this is a Heroforge repository
        let is_fossil: bool = conn.query_row(
            "SELECT COUNT(*) > 0 FROM sqlite_master WHERE name='blob'",
            [],
            |row| row.get(0),
        )?;

        if !is_fossil {
            return Err(FossilError::NotARepository);
        }

        Ok(Self { conn })
    }

    /// Open for read-write
    pub fn open_rw<P: AsRef<Path>>(path: P) -> Result<Self> {
        let conn = Connection::open_with_flags(
            path,
            OpenFlags::SQLITE_OPEN_READ_WRITE | OpenFlags::SQLITE_OPEN_NO_MUTEX,
        )?;
        Ok(Self { conn })
    }

    /// Get raw blob content by hash (still compressed)
    pub fn get_blob_raw(&self, hash: &str) -> Result<(i64, Vec<u8>)> {
        // Try exact match first
        let result: std::result::Result<(i64, Vec<u8>), _> = self.conn.query_row(
            "SELECT rid, content FROM blob WHERE uuid = ?1",
            params![hash],
            |row| Ok((row.get(0)?, row.get(1)?)),
        );

        if let Ok(r) = result {
            return Ok(r);
        }

        // Try prefix match
        let mut stmt = self
            .conn
            .prepare("SELECT rid, content, uuid FROM blob WHERE uuid LIKE ?1 || '%'")?;
        let mut rows = stmt.query(params![hash])?;

        let first = rows
            .next()?
            .ok_or_else(|| FossilError::ArtifactNotFound(hash.to_string()))?;
        let rid: i64 = first.get(0)?;
        let content: Vec<u8> = first.get(1)?;

        // Check for ambiguity
        if rows.next()?.is_some() {
            return Err(FossilError::AmbiguousHash(hash.to_string()));
        }

        Ok((rid, content))
    }

    /// Get raw blob with delta source info for sync
    /// Returns (raw_content, size, delta_source_hash)
    pub fn get_blob_for_sync(&self, hash: &str) -> Result<(Vec<u8>, i64, Option<String>)> {
        let (rid, content) = self.get_blob_raw(hash)?;

        // Get the size from the blob table
        let size: i64 = self.conn.query_row(
            "SELECT size FROM blob WHERE rid = ?1",
            params![rid],
            |row| row.get(0),
        )?;

        // Check if this is a delta and get source hash
        let delta_source: Option<String> = if let Some(src_rid) = self.get_delta_source(rid)? {
            Some(self.get_hash_by_rid(src_rid)?)
        } else {
            None
        };

        Ok((content, size, delta_source))
    }

    /// Insert a raw blob directly (for sync - content already compressed)
    pub fn insert_raw_blob(&self, content: &[u8], hash: &str, size: i64) -> Result<i64> {
        // Check if blob already exists
        if let Ok(rid) = self.get_rid_by_hash(hash) {
            return Ok(rid);
        }

        self.conn.execute(
            "INSERT INTO blob(uuid, size, content) VALUES (?1, ?2, ?3)",
            params![hash, size, content],
        )?;

        Ok(self.conn.last_insert_rowid())
    }

    /// Insert delta reference
    pub fn insert_delta(&self, rid: i64, src_rid: i64) -> Result<()> {
        self.conn.execute(
            "INSERT OR REPLACE INTO delta(rid, srcid) VALUES (?1, ?2)",
            params![rid, src_rid],
        )?;
        Ok(())
    }

    /// Get blob content by rid
    pub fn get_blob_by_rid(&self, rid: i64) -> Result<Vec<u8>> {
        let content: Vec<u8> = self
            .conn
            .query_row(
                "SELECT content FROM blob WHERE rid = ?1",
                params![rid],
                |row| row.get(0),
            )
            .map_err(|_| FossilError::ArtifactNotFound(rid.to_string()))?;

        Ok(content)
    }

    /// Check if blob is a delta, return source rid if so
    pub fn get_delta_source(&self, rid: i64) -> Result<Option<i64>> {
        let result: std::result::Result<i64, _> = self.conn.query_row(
            "SELECT srcid FROM delta WHERE rid = ?1",
            params![rid],
            |row| row.get(0),
        );

        Ok(result.ok())
    }

    /// Get hash by rid
    pub fn get_hash_by_rid(&self, rid: i64) -> Result<String> {
        let hash: String = self
            .conn
            .query_row(
                "SELECT uuid FROM blob WHERE rid = ?1",
                params![rid],
                |row| row.get(0),
            )
            .map_err(|_| FossilError::ArtifactNotFound(rid.to_string()))?;

        Ok(hash)
    }

    /// Get rid by hash (supports prefix matching)
    pub fn get_rid_by_hash(&self, hash: &str) -> Result<i64> {
        // Try exact match first
        let result: std::result::Result<i64, _> = self.conn.query_row(
            "SELECT rid FROM blob WHERE uuid = ?1",
            params![hash],
            |row| row.get(0),
        );

        if let Ok(rid) = result {
            return Ok(rid);
        }

        // Try prefix match
        let mut stmt = self
            .conn
            .prepare("SELECT rid FROM blob WHERE uuid LIKE ?1 || '%'")?;
        let mut rows = stmt.query(params![hash])?;

        let first = rows
            .next()?
            .ok_or_else(|| FossilError::ArtifactNotFound(hash.to_string()))?;
        let rid: i64 = first.get(0)?;

        // Check for ambiguity
        if rows.next()?.is_some() {
            return Err(FossilError::AmbiguousHash(hash.to_string()));
        }

        Ok(rid)
    }

    /// Get project code
    pub fn get_project_code(&self) -> Result<String> {
        let code: String = self.conn.query_row(
            "SELECT value FROM config WHERE name = 'project-code'",
            [],
            |row| row.get(0),
        )?;
        Ok(code)
    }

    /// Get project name
    pub fn get_project_name(&self) -> Result<Option<String>> {
        let result: std::result::Result<String, _> = self.conn.query_row(
            "SELECT value FROM config WHERE name = 'project-name'",
            [],
            |row| row.get(0),
        );
        Ok(result.ok())
    }

    /// Get latest check-in on trunk
    pub fn get_trunk_tip(&self) -> Result<i64> {
        // Find the 'trunk' branch tag
        // Order by mtime DESC, then by rid DESC as tiebreaker for commits in the same second
        let rid: i64 = self.conn.query_row(
            r#"SELECT event.objid FROM event
               JOIN tagxref ON tagxref.rid = event.objid
               JOIN tag ON tag.tagid = tagxref.tagid
               WHERE tag.tagname = 'sym-trunk'
               AND event.type = 'ci'
               ORDER BY event.mtime DESC, event.objid DESC
               LIMIT 1"#,
            [],
            |row| row.get(0),
        )?;
        Ok(rid)
    }

    /// Get check-in by branch name
    pub fn get_branch_tip(&self, branch: &str) -> Result<i64> {
        let tag_name = format!("sym-{}", branch);
        // Order by mtime DESC, then by rid DESC as tiebreaker for commits in the same second
        let rid: i64 = self.conn.query_row(
            r#"SELECT event.objid FROM event
               JOIN tagxref ON tagxref.rid = event.objid
               JOIN tag ON tag.tagid = tagxref.tagid
               WHERE tag.tagname = ?1
               AND event.type = 'ci'
               ORDER BY event.mtime DESC, event.objid DESC
               LIMIT 1"#,
            params![tag_name],
            |row| row.get(0),
        )?;
        Ok(rid)
    }

    /// List all branches
    pub fn list_branches(&self) -> Result<Vec<String>> {
        let mut stmt = self.conn.prepare(
            r#"SELECT DISTINCT substr(tagname, 5) FROM tag
               WHERE tagname LIKE 'sym-%'
               ORDER BY tagname"#,
        )?;

        let branches = stmt
            .query_map([], |row| row.get(0))?
            .filter_map(|r| r.ok())
            .collect();

        Ok(branches)
    }

    /// Get recent check-ins
    pub fn get_recent_checkins(
        &self,
        limit: usize,
    ) -> Result<Vec<(i64, String, f64, String, String)>> {
        let mut stmt = self.conn.prepare(
            r#"SELECT objid,
                      (SELECT uuid FROM blob WHERE rid = objid),
                      mtime, user, comment
               FROM event
               WHERE type = 'ci'
               ORDER BY mtime DESC
               LIMIT ?1"#,
        )?;

        let checkins = stmt
            .query_map(params![limit as i64], |row| {
                Ok((
                    row.get::<_, i64>(0)?,
                    row.get::<_, String>(1)?,
                    row.get::<_, f64>(2)?,
                    row.get::<_, String>(3)?,
                    row.get::<_, String>(4)?,
                ))
            })?
            .filter_map(|r| r.ok())
            .collect();

        Ok(checkins)
    }

    pub fn connection(&self) -> &Connection {
        &self.conn
    }

    /// Create a new empty Heroforge repository
    pub fn init<P: AsRef<Path>>(path: P) -> Result<Self> {
        let conn = Connection::open(&path)?;

        // Create the core tables
        conn.execute_batch(
            r#"
            CREATE TABLE IF NOT EXISTS blob(
                rid INTEGER PRIMARY KEY,
                rcvid INTEGER,
                size INTEGER,
                uuid TEXT UNIQUE NOT NULL,
                content BLOB,
                CHECK( length(uuid)>=40 AND rid>0 )
            );
            CREATE TABLE IF NOT EXISTS delta(
                rid INTEGER PRIMARY KEY,
                srcid INTEGER NOT NULL REFERENCES blob
            );
            CREATE TABLE IF NOT EXISTS config(
                name TEXT PRIMARY KEY NOT NULL,
                value CLOB,
                mtime DATE
            );
            CREATE TABLE IF NOT EXISTS event(
                type TEXT,
                mtime DATETIME,
                objid INTEGER PRIMARY KEY,
                tagid INTEGER,
                uid INTEGER,
                bgcolor TEXT,
                euser TEXT,
                user TEXT,
                ecomment TEXT,
                comment TEXT,
                brief TEXT,
                omtime DATETIME
            );
            CREATE INDEX IF NOT EXISTS event_i1 ON event(mtime);
            CREATE TABLE IF NOT EXISTS tag(
                tagid INTEGER PRIMARY KEY,
                tagname TEXT UNIQUE
            );
            CREATE TABLE IF NOT EXISTS tagxref(
                tagid INTEGER REFERENCES tag,
                tagtype INTEGER,
                srcid INTEGER REFERENCES blob,
                origid INTEGER REFERENCES blob,
                value TEXT,
                mtime TIMESTAMP,
                rid INTEGER REFERENCES blob,
                UNIQUE(rid, tagid)
            );
            CREATE INDEX IF NOT EXISTS tagxref_i1 ON tagxref(tagid, mtime);
            CREATE TABLE IF NOT EXISTS plink(
                pid INTEGER REFERENCES blob,
                cid INTEGER REFERENCES blob,
                isprim BOOLEAN,
                mtime DATETIME,
                baseid INTEGER REFERENCES blob,
                UNIQUE(pid, cid)
            );
            CREATE INDEX IF NOT EXISTS plink_i2 ON plink(cid,pid);
            CREATE TABLE IF NOT EXISTS filename(
                fnid INTEGER PRIMARY KEY,
                name TEXT UNIQUE
            );
            CREATE TABLE IF NOT EXISTS mlink(
                mid INTEGER,
                fid INTEGER,
                pmid INTEGER,
                pid INTEGER,
                fnid INTEGER REFERENCES filename,
                pfnid INTEGER,
                mperm INTEGER,
                isaux BOOLEAN DEFAULT 0
            );
            CREATE INDEX IF NOT EXISTS mlink_i1 ON mlink(mid);
            CREATE INDEX IF NOT EXISTS mlink_i2 ON mlink(fnid);
            CREATE INDEX IF NOT EXISTS mlink_i3 ON mlink(fid);
            CREATE INDEX IF NOT EXISTS mlink_i4 ON mlink(pid);
            CREATE INDEX IF NOT EXISTS blob_uuid ON blob(uuid);
            CREATE TABLE IF NOT EXISTS rcvfrom(
                rcvid INTEGER PRIMARY KEY,
                uid INTEGER,
                mtime DATETIME,
                nonce TEXT UNIQUE,
                ipaddr TEXT
            );
            "#,
        )?;

        // Generate project and server codes
        let project_code = Self::generate_hash(uuid::Uuid::new_v4().to_string().as_bytes());
        let server_code = Self::generate_hash(uuid::Uuid::new_v4().to_string().as_bytes());

        // Insert required config entries
        conn.execute(
            "INSERT INTO config(name, value, mtime) VALUES ('project-code', ?1, julianday('now'))",
            params![project_code],
        )?;
        conn.execute(
            "INSERT INTO config(name, value, mtime) VALUES ('server-code', ?1, julianday('now'))",
            params![server_code],
        )?;
        conn.execute(
            "INSERT INTO config(name, value, mtime) VALUES ('content-schema', '2', julianday('now'))",
            [],
        )?;
        conn.execute(
            "INSERT INTO config(name, value, mtime) VALUES ('aux-schema', '2015-01-24', julianday('now'))",
            [],
        )?;
        conn.execute(
            "INSERT INTO config(name, value, mtime) VALUES ('hash-policy', '2', julianday('now'))",
            [],
        )?;

        // Create additional tables that heroforge expects
        conn.execute_batch(
            r#"
            CREATE TABLE IF NOT EXISTS user(
                uid INTEGER PRIMARY KEY,
                login TEXT UNIQUE,
                pw TEXT,
                cap TEXT,
                cookie TEXT,
                ipaddr TEXT,
                cexpire DATETIME,
                info TEXT,
                mtime DATE,
                photo BLOB
            );
            CREATE TABLE IF NOT EXISTS leaf(
                rid INTEGER PRIMARY KEY
            );
            CREATE TABLE IF NOT EXISTS phantom(
                rid INTEGER PRIMARY KEY
            );
            CREATE TABLE IF NOT EXISTS orphan(
                rid INTEGER PRIMARY KEY,
                baseline INTEGER
            );
            CREATE TABLE IF NOT EXISTS unclustered(
                rid INTEGER PRIMARY KEY
            );
            CREATE TABLE IF NOT EXISTS unsent(
                rid INTEGER PRIMARY KEY
            );
            CREATE TABLE IF NOT EXISTS private(
                rid INTEGER PRIMARY KEY
            );
            CREATE TABLE IF NOT EXISTS shun(
                uuid UNIQUE,
                mtime DATE,
                scom TEXT
            );
            CREATE TABLE IF NOT EXISTS reportfmt(
                rn INTEGER PRIMARY KEY,
                owner TEXT,
                title TEXT UNIQUE,
                mtime DATE,
                cols TEXT,
                sqlcode TEXT
            );
            CREATE TABLE IF NOT EXISTS concealed(
                hash TEXT PRIMARY KEY,
                content TEXT
            );
            CREATE TABLE IF NOT EXISTS cherrypick(
                parentid INT,
                childid INT,
                isExclude BOOLEAN DEFAULT false,
                PRIMARY KEY(parentid, childid)
            ) WITHOUT ROWID;
            CREATE TABLE IF NOT EXISTS backlink(
                target TEXT,
                srctype INT,
                srcid INT,
                mtime TIMESTAMP,
                UNIQUE(target, srctype, srcid)
            );
            CREATE TABLE IF NOT EXISTS attachment(
                attachid INTEGER PRIMARY KEY,
                isLatest BOOLEAN DEFAULT 0,
                mtime TIMESTAMP,
                src TEXT,
                target TEXT,
                filename TEXT,
                comment TEXT,
                user TEXT
            );
            CREATE TABLE IF NOT EXISTS ticket(tkt_id INTEGER PRIMARY KEY, tkt_uuid TEXT UNIQUE, tkt_mtime DATE, tkt_ctime DATE);
            CREATE TABLE IF NOT EXISTS ticketchng(tkt_id INTEGER PRIMARY KEY, tkt_rid INT, tkt_mtime DATE, tkt_user TEXT);
            "#,
        )?;

        let db = Self { conn };

        // Create default users with appropriate capabilities
        // Capabilities: a=admin, d=delete, e=email, g=clone, i=check-in, j=read-wiki,
        // k=write-wiki, m=moderate, n=new-wiki, o=check-out, p=password, r=read,
        // s=setup, t=ticket, u=reader, v=developer, w=write-ticket, x=private, z=download

        // anonymous - public read access
        db.create_user("anonymous", "", "cgjorz")?;

        // nobody - minimal access
        db.create_user("nobody", "", "")?;

        // developer - full developer access (will be used for commits)
        db.create_user("developer", "", "deghijkmnorstvwz")?;

        Ok(db)
    }

    /// Generate SHA3-256 hash
    pub fn generate_hash(data: &[u8]) -> String {
        use sha3::{Digest, Sha3_256};
        let mut hasher = Sha3_256::new();
        hasher.update(data);
        hex::encode(hasher.finalize())
    }

    /// Insert a blob and return its rid (uses INSERT OR IGNORE for efficiency)
    pub fn insert_blob(&self, content: &[u8], hash: &str, size: i64) -> Result<i64> {
        // Use INSERT OR IGNORE to avoid separate existence check
        self.conn.execute(
            "INSERT OR IGNORE INTO blob(uuid, size, content) VALUES (?1, ?2, ?3)",
            params![hash, size, content],
        )?;

        // Get the rid (either just inserted or existing)
        let rid: i64 = self.conn.query_row(
            "SELECT rid FROM blob WHERE uuid = ?1",
            params![hash],
            |row| row.get(0),
        )?;

        Ok(rid)
    }

    /// Batch insert blobs and return a map of hash -> rid
    /// Uses cached prepared statements for efficiency
    pub fn insert_blobs(&self, blobs: &[(&[u8], &str, i64)]) -> Result<HashMap<String, i64>> {
        // Insert all blobs using cached statement
        {
            let mut insert_stmt = self.conn.prepare_cached(
                "INSERT OR IGNORE INTO blob(uuid, size, content) VALUES (?1, ?2, ?3)",
            )?;
            for (content, hash, size) in blobs {
                insert_stmt.execute(params![hash, size, content])?;
            }
        }

        // Get all rids using cached statement
        let mut result = HashMap::new();
        {
            let mut select_stmt = self
                .conn
                .prepare_cached("SELECT rid FROM blob WHERE uuid = ?1")?;
            for (_, hash, _) in blobs {
                let rid: i64 = select_stmt.query_row(params![hash], |row| row.get(0))?;
                result.insert(hash.to_string(), rid);
            }
        }

        Ok(result)
    }

    /// Get or create filename id
    pub fn get_or_create_filename(&self, name: &str) -> Result<i64> {
        // Insert if not exists, then get the id
        self.conn.execute(
            "INSERT OR IGNORE INTO filename(name) VALUES (?1)",
            params![name],
        )?;

        let fnid: i64 = self.conn.query_row(
            "SELECT fnid FROM filename WHERE name = ?1",
            params![name],
            |row| row.get(0),
        )?;

        Ok(fnid)
    }

    /// Batch insert filenames and return a map of name -> fnid
    /// Uses cached prepared statements for efficiency
    pub fn get_or_create_filenames(&self, names: &[&str]) -> Result<HashMap<String, i64>> {
        // Use cached prepared statements for batch operations
        {
            let mut insert_stmt = self
                .conn
                .prepare_cached("INSERT OR IGNORE INTO filename(name) VALUES (?1)")?;
            for name in names {
                insert_stmt.execute(params![name])?;
            }
        }

        // Fetch all fnids using cached statement
        let mut result = HashMap::new();
        {
            let mut select_stmt = self
                .conn
                .prepare_cached("SELECT fnid FROM filename WHERE name = ?1")?;
            for name in names {
                let fnid: i64 = select_stmt.query_row(params![name], |row| row.get(0))?;
                result.insert(name.to_string(), fnid);
            }
        }

        Ok(result)
    }

    /// Get or create tag id
    pub fn get_or_create_tag(&self, tagname: &str) -> Result<i64> {
        let existing: std::result::Result<i64, _> = self.conn.query_row(
            "SELECT tagid FROM tag WHERE tagname = ?1",
            params![tagname],
            |row| row.get(0),
        );

        if let Ok(tagid) = existing {
            return Ok(tagid);
        }

        self.conn
            .execute("INSERT INTO tag(tagname) VALUES (?1)", params![tagname])?;

        Ok(self.conn.last_insert_rowid())
    }

    /// Insert event record
    pub fn insert_event(
        &self,
        event_type: &str,
        objid: i64,
        mtime: f64,
        user: &str,
        comment: &str,
    ) -> Result<()> {
        self.conn.execute(
            "INSERT INTO event(type, objid, mtime, user, comment) VALUES (?1, ?2, ?3, ?4, ?5)",
            params![event_type, objid, mtime, user, comment],
        )?;
        Ok(())
    }

    /// Insert tagxref record
    pub fn insert_tagxref(
        &self,
        tagid: i64,
        tagtype: i64,
        rid: i64,
        mtime: f64,
        value: Option<&str>,
    ) -> Result<()> {
        self.conn.execute(
            "INSERT OR REPLACE INTO tagxref(tagid, tagtype, rid, mtime, value, srcid, origid)
             VALUES (?1, ?2, ?3, ?4, ?5, ?3, ?3)",
            params![tagid, tagtype, rid, mtime, value],
        )?;
        Ok(())
    }

    /// Insert a leaf node
    pub fn insert_leaf(&self, rid: i64) -> Result<()> {
        self.conn
            .execute("INSERT OR IGNORE INTO leaf(rid) VALUES (?1)", params![rid])?;
        Ok(())
    }

    /// Insert plink (parent link) record and update leaf table
    pub fn insert_plink(&self, parent_rid: i64, child_rid: i64, mtime: f64) -> Result<()> {
        self.conn.execute(
            "INSERT OR IGNORE INTO plink(pid, cid, isprim, mtime) VALUES (?1, ?2, 1, ?3)",
            params![parent_rid, child_rid, mtime],
        )?;

        // Parent is no longer a leaf (it has a child now)
        self.conn
            .execute("DELETE FROM leaf WHERE rid = ?1", params![parent_rid])?;

        // Child is a leaf (until it gets its own child)
        self.conn.execute(
            "INSERT OR IGNORE INTO leaf(rid) VALUES (?1)",
            params![child_rid],
        )?;

        Ok(())
    }

    /// Get all files for a manifest (checkin) directly from mlink table
    /// Returns (filename, file_hash) pairs
    pub fn get_files_for_manifest(&self, manifest_rid: i64) -> Result<Vec<(String, String)>> {
        let mut stmt = self.conn.prepare(
            r#"SELECT f.name, b.uuid
               FROM mlink m
               JOIN filename f ON f.fnid = m.fnid
               JOIN blob b ON b.rid = m.fid
               WHERE m.mid = ?1 AND m.fid > 0
               ORDER BY f.name"#,
        )?;

        let files = stmt
            .query_map(params![manifest_rid], |row| {
                Ok((row.get::<_, String>(0)?, row.get::<_, String>(1)?))
            })?
            .filter_map(|r| r.ok())
            .collect();

        Ok(files)
    }

    /// Get a single file's hash from a manifest by filename
    pub fn get_file_hash_from_manifest(&self, manifest_rid: i64, filename: &str) -> Result<String> {
        let hash: String = self
            .conn
            .query_row(
                r#"SELECT b.uuid
               FROM mlink m
               JOIN filename f ON f.fnid = m.fnid
               JOIN blob b ON b.rid = m.fid
               WHERE m.mid = ?1 AND f.name = ?2 AND m.fid > 0"#,
                params![manifest_rid, filename],
                |row| row.get(0),
            )
            .map_err(|_| FossilError::ArtifactNotFound(format!("file: {}", filename)))?;

        Ok(hash)
    }

    /// Insert mlink (manifest link) record
    pub fn insert_mlink(
        &self,
        manifest_rid: i64,
        file_rid: i64,
        filename_id: i64,
        parent_file_rid: Option<i64>,
    ) -> Result<()> {
        self.conn.execute(
            "INSERT INTO mlink(mid, fid, fnid, pid) VALUES (?1, ?2, ?3, ?4)",
            params![manifest_rid, file_rid, filename_id, parent_file_rid],
        )?;
        Ok(())
    }

    /// Batch insert mlink records using cached prepared statement
    pub fn insert_mlinks(
        &self,
        manifest_rid: i64,
        entries: &[(i64, i64)], // (file_rid, filename_id)
    ) -> Result<()> {
        let mut stmt = self
            .conn
            .prepare_cached("INSERT INTO mlink(mid, fid, fnid) VALUES (?1, ?2, ?3)")?;
        for (file_rid, filename_id) in entries {
            stmt.execute(params![manifest_rid, file_rid, filename_id])?;
        }
        Ok(())
    }

    /// Create a user
    pub fn create_user(&self, login: &str, password: &str, capabilities: &str) -> Result<()> {
        self.conn.execute(
            "INSERT OR REPLACE INTO user(login, pw, cap, mtime) VALUES (?1, ?2, ?3, julianday('now'))",
            params![login, password, capabilities],
        )?;
        Ok(())
    }

    /// Set user capabilities
    pub fn set_user_capabilities(&self, login: &str, capabilities: &str) -> Result<()> {
        self.conn.execute(
            "UPDATE user SET cap = ?2, mtime = julianday('now') WHERE login = ?1",
            params![login, capabilities],
        )?;
        Ok(())
    }

    /// Get user capabilities
    pub fn get_user_capabilities(&self, login: &str) -> Result<Option<String>> {
        let result: std::result::Result<String, _> = self.conn.query_row(
            "SELECT cap FROM user WHERE login = ?1",
            params![login],
            |row| row.get(0),
        );
        Ok(result.ok())
    }

    /// List all users
    pub fn list_users(&self) -> Result<Vec<(String, String)>> {
        let mut stmt = self
            .conn
            .prepare("SELECT login, cap FROM user ORDER BY login")?;
        let users = stmt
            .query_map([], |row| Ok((row.get(0)?, row.get(1)?)))?
            .filter_map(|r| r.ok())
            .collect();
        Ok(users)
    }

    /// Begin transaction
    pub fn begin_transaction(&self) -> Result<()> {
        self.conn.execute("BEGIN IMMEDIATE", [])?;
        Ok(())
    }

    /// Commit transaction
    pub fn commit_transaction(&self) -> Result<()> {
        self.conn.execute("COMMIT", [])?;
        Ok(())
    }

    /// Rollback transaction
    pub fn rollback_transaction(&self) -> Result<()> {
        self.conn.execute("ROLLBACK", [])?;
        Ok(())
    }
}