use std::path::Path;
use std::sync::Arc;

pub use api_types::Mode;
use rusqlite::types::{FromSql, FromSqlError, FromSqlResult, ToSql, ToSqlOutput, ValueRef};
use rusqlite::{Connection, OptionalExtension, params};

const SCHEMA_VERSION: i64 = 9;

/// SQL adapter for [`Mode`]. A newtype is needed because both the rusqlite
/// traits and `Mode` are foreign to this crate.
///
/// The stored strings are unchanged ("rw"/"ro"), so old databases still read.
struct SqlMode(Mode);

impl FromSql for SqlMode {
    fn column_result(v: ValueRef<'_>) -> FromSqlResult<Self> {
        let s = v.as_str()?;
        Mode::from_wire(s)
            .map(SqlMode)
            .ok_or_else(|| FromSqlError::Other(format!("unknown mode {s:?}").into()))
    }
}

impl ToSql for SqlMode {
    fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
        Ok(ToSqlOutput::from(self.0.as_str()))
    }
}

#[derive(Debug, Clone)]
pub struct User {
    pub id: i64,
    pub name: String,
    pub is_admin: bool,
    /// Disabled users cannot sign in and their sessions are rejected.
    pub active: bool,
    /// Profile setting: single click opens entries (off = click selects).
    pub single_click: bool,
    /// Profile setting: show thumbnails in the grid.
    pub thumbnails: bool,
    /// Preferred UI language tag ("en", "de", "fr"); None = follow the
    /// browser.
    pub language: Option<String>,
    /// Profile setting: the root the UI opens by default. May point at a
    /// root the user no longer has; the API filters that out.
    pub default_root_id: Option<i64>,
}

#[derive(Debug, Clone)]
pub struct RootRow {
    pub id: i64,
    /// Path relative to the server root; "." means the whole root.
    pub path: String,
    pub mode: Mode,
}

#[derive(Debug, Clone)]
pub struct ShareRow {
    pub id: i64,
    pub token: String,
    pub creator_id: i64,
    /// Path of the shared item relative to the server root.
    pub target: String,
    pub is_file: bool,
    pub mode: Mode,
    pub created_at: String,
    pub expires_at: Option<String>,
    /// Argon2 hash of the share's password, when it has one. Resolve,
    /// listing and download all stay locked until the visitor enters it and
    /// gets an unlock cookie.
    pub password_hash: Option<String>,
}

impl ShareRow {
    pub fn is_expired(&self) -> bool {
        match &self.expires_at {
            Some(e) => chrono::DateTime::parse_from_rfc3339(e)
                .map(|t| chrono::Utc::now() >= t.with_timezone(&chrono::Utc))
                .unwrap_or(false),
            None => false,
        }
    }
}

/// A [`ShareRow`] together with the account that created it.
#[derive(Debug, Clone)]
pub struct ShareWithCreator {
    pub share: ShareRow,
    pub creator_name: String,
    /// Whether that account can still sign in. Deactivating an account leaves
    /// its shares live.
    pub creator_active: bool,
}

/// Every query can fail, and every caller decides what to do about it.
///
/// Earlier versions swallowed read errors and returned a default (an empty
/// root list, a count of 0). That turned a broken database into a plausible
/// answer: "you have no folders" instead of an error. One contract now.
pub type DbResult<T> = Result<T, rusqlite::Error>;

#[derive(Clone)]
pub struct Db(Arc<tokio::sync::Mutex<Connection>>);

impl std::fmt::Debug for Db {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        f.debug_struct("Db").finish()
    }
}

impl Db {
    pub async fn open(path: &Path) -> anyhow::Result<Self> {
        if let Some(parent) = path.parent()
            && !parent.as_os_str().is_empty()
        {
            std::fs::create_dir_all(parent)?;
        }
        let conn = Connection::open(path)?;
        conn.pragma_update(None, "journal_mode", "WAL")?;
        // WAL plus NORMAL: fsync only at checkpoints. A crash can lose the
        // last commits, never the database file.
        conn.pragma_update(None, "synchronous", "NORMAL")?;
        conn.pragma_update(None, "foreign_keys", "ON")?;
        conn.pragma_update(None, "busy_timeout", "5000")?;
        Self::migrate(&conn)?;
        Ok(Self(Arc::new(tokio::sync::Mutex::new(conn))))
    }

    /// Open a fresh in-memory database (used by tests — no temp file needed).
    pub async fn open_in_memory() -> anyhow::Result<Self> {
        let conn = Connection::open_in_memory()?;
        conn.pragma_update(None, "foreign_keys", "ON")?;
        conn.pragma_update(None, "busy_timeout", "5000")?;
        Self::migrate(&conn)?;
        Ok(Self(Arc::new(tokio::sync::Mutex::new(conn))))
    }

    fn migrate(conn: &Connection) -> rusqlite::Result<()> {
        conn.execute(
            "CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL)",
            [],
        )?;
        let version: i64 = conn
            .query_row(
                "SELECT value FROM meta WHERE key = 'schema_version'",
                [],
                |r| r.get::<_, String>(0),
            )
            .optional()?
            .and_then(|v| v.parse().ok())
            .unwrap_or(0);

        if version < 1 {
            conn.execute_batch(SCHEMA_V1)?;
        }
        if version < 2 {
            // User management (M7): a disabled flag so admins can suspend
            // accounts without deleting them.
            conn.execute_batch("ALTER TABLE users ADD COLUMN active INTEGER NOT NULL DEFAULT 1")?;
        }
        if version < 3 {
            // Per-user profile settings: click-to-open mode. No migration
            // from the old behaviour — everyone starts on the new default
            // (off: single click selects, double click opens).
            conn.execute_batch(
                "ALTER TABLE users ADD COLUMN single_click INTEGER NOT NULL DEFAULT 0",
            )?;
        }
        if version < 4 {
            // Per-user UI language preference; NULL means "follow the
            // browser".
            conn.execute_batch("ALTER TABLE users ADD COLUMN language TEXT")?;
        }
        if version < 5 {
            // The share list is queried by creator on every shares page.
            conn.execute_batch(
                "CREATE INDEX IF NOT EXISTS idx_shares_creator ON shares(creator_id)",
            )?;
        }
        if version < 6 {
            // Unlocks cascade with their share, which cascades with its
            // creator's account.
            conn.execute_batch(
                "ALTER TABLE shares ADD COLUMN password_hash TEXT;
                 CREATE TABLE IF NOT EXISTS share_unlocks (
                     token TEXT PRIMARY KEY,
                     share_id INTEGER NOT NULL REFERENCES shares(id) ON DELETE CASCADE,
                     created_at TEXT NOT NULL
                 );",
            )?;
        }
        if version < 7 {
            // `delete_share` cascades into share_unlocks, which is a full
            // scan of that table without this.
            conn.execute_batch(
                "CREATE INDEX IF NOT EXISTS idx_share_unlocks_share
                 ON share_unlocks(share_id)",
            )?;
        }
        if version < 8 {
            // On by default, so `--cache` is the only step needed to get
            // thumbnails.
            conn.execute_batch(
                "ALTER TABLE users ADD COLUMN thumbnails INTEGER NOT NULL DEFAULT 1",
            )?;
        }
        if version < 9 {
            // Per-user default root. No foreign key on purpose: removing a
            // root must not fail because of this column.
            conn.execute_batch("ALTER TABLE users ADD COLUMN default_root_id INTEGER")?;
        }
        conn.execute(
            "INSERT OR REPLACE INTO meta (key, value) VALUES ('schema_version', ?1)",
            [SCHEMA_VERSION.to_string()],
        )?;
        Ok(())
    }

    // ---------- users ----------

    pub async fn user_count(&self) -> DbResult<i64> {
        let c = self.0.lock().await;
        let mut stmt = c.prepare_cached("SELECT COUNT(*) FROM users")?;
        stmt.query_row([], |r| r.get(0))
    }

    /// Create the first admin account with the whole root visible (read-write).
    ///
    /// `None` means a user already existed. The `WHERE NOT EXISTS` guard runs
    /// inside the same transaction as the insert, so two concurrent first-boot
    /// setups cannot both win; a caller's earlier `user_count` check is only
    /// an optimization, not the guarantee.
    pub async fn create_admin(&self, name: &str, pass_hash: &str) -> DbResult<Option<User>> {
        let mut c = self.0.lock().await;
        let tx = c.transaction()?;
        let inserted = tx.execute(
            "INSERT INTO users (name, pass_hash, is_admin, created_at)
             SELECT ?1, ?2, 1, ?3 WHERE NOT EXISTS (SELECT 1 FROM users)",
            params![name, pass_hash, now()],
        )?;
        if inserted == 0 {
            return Ok(None); // dropping `tx` rolls back
        }
        let user_id = tx.last_insert_rowid();
        tx.execute(
            "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, '.', 'rw')",
            params![user_id],
        )?;
        tx.commit()?;
        Ok(Some(User {
            id: user_id,
            name: name.to_string(),
            is_admin: true,
            active: true,
            single_click: false,
            thumbnails: true,
            language: None,
            default_root_id: None,
        }))
    }

    pub async fn verify_password(&self, name: &str, password: &str) -> DbResult<Option<User>> {
        // The guard is scoped to the query alone. Argon2 below is slow by
        // design; holding the single connection lock across it would make one
        // login serialize every other database access.
        type UserRow = (
            i64,
            String,
            bool,
            String,
            bool,
            bool,
            bool,
            Option<String>,
            Option<i64>,
        );
        let row: Option<UserRow> = {
            let c = self.0.lock().await;
            c.query_row(
                "SELECT id, name, is_admin != 0, pass_hash, active != 0, single_click != 0,
                        thumbnails != 0, language, default_root_id
                 FROM users WHERE name = ?1",
                [name],
                |r| {
                    Ok((
                        r.get(0)?,
                        r.get(1)?,
                        r.get(2)?,
                        r.get(3)?,
                        r.get(4)?,
                        r.get(5)?,
                        r.get(6)?,
                        r.get(7)?,
                        r.get(8)?,
                    ))
                },
            )
            .optional()?
        };
        // An unknown or disabled name still pays for one Argon2 verify, so the
        // response time does not reveal which names exist.
        let (row, hash) = match row {
            Some((
                id,
                name,
                is_admin,
                hash,
                active,
                single_click,
                thumbnails,
                language,
                default_root_id,
            )) if active => (
                Some((
                    id,
                    name,
                    is_admin,
                    single_click,
                    thumbnails,
                    language,
                    default_root_id,
                )),
                hash,
            ),
            _ => (None, DUMMY_HASH.clone()),
        };
        let ok = crate::auth::verify_password_async(password, &hash).await;
        let Some((id, name, is_admin, single_click, thumbnails, language, default_root_id)) = row
        else {
            return Ok(None);
        };
        Ok(ok.then_some(User {
            id,
            name,
            is_admin,
            active: true,
            single_click,
            thumbnails,
            language,
            default_root_id,
        }))
    }

    // ---------- sessions ----------

    pub async fn create_session(&self, user_id: i64, token: &str) -> DbResult<()> {
        let c = self.0.lock().await;
        c.execute(
            "INSERT INTO sessions (token, user_id, created_at, last_seen_at)
             VALUES (?1, ?2, ?3, ?4)",
            params![token, user_id, now(), now()],
        )?;
        Ok(())
    }

    pub async fn delete_session(&self, token: &str) -> DbResult<()> {
        let c = self.0.lock().await;
        c.execute("DELETE FROM sessions WHERE token = ?1", [token])?;
        Ok(())
    }

    /// The session's user plus that user's roots, in one round trip. Every
    /// authenticated request needs both, so they are not two queries.
    pub async fn session_user_with_roots(
        &self,
        token: &str,
    ) -> DbResult<Option<(User, Vec<RootRow>)>> {
        let c = self.0.lock().await;
        let mut stmt = c.prepare_cached(
            "SELECT u.id, u.name, u.is_admin != 0, u.active != 0, u.single_click != 0,
                    u.thumbnails != 0, u.language, u.default_root_id,
                    r.id, r.path, r.mode
             FROM sessions s
             JOIN users u ON u.id = s.user_id
             LEFT JOIN user_roots r ON r.user_id = u.id
             WHERE s.token = ?1 AND u.active = 1
             ORDER BY r.id",
        )?;
        // One row per root; a user without roots still returns one row, with
        // the root columns NULL.
        let mut user: Option<User> = None;
        let mut roots: Vec<RootRow> = Vec::new();
        let mut rows = stmt.query([token])?;
        while let Some(r) = rows.next()? {
            if user.is_none() {
                user = Some(map_user(r)?);
            }
            if let Some(id) = r.get::<_, Option<i64>>(8)? {
                roots.push(RootRow {
                    id,
                    path: r.get(9)?,
                    mode: r.get::<_, SqlMode>(10)?.0,
                });
            }
        }
        Ok(user.map(|u| (u, roots)))
    }

    // ---------- roots ----------

    pub async fn user_roots(&self, user_id: i64) -> DbResult<Vec<RootRow>> {
        let c = self.0.lock().await;
        let mut stmt = c.prepare_cached(
            "SELECT id, path, mode FROM user_roots WHERE user_id = ?1 ORDER BY id",
        )?;
        let rows = stmt.query_map([user_id], |r| {
            Ok(RootRow {
                id: r.get(0)?,
                path: r.get(1)?,
                mode: r.get::<_, SqlMode>(2)?.0,
            })
        })?;
        rows.collect()
    }

    // ---------- admin: user management (M7) ----------

    /// Every user with their roots, in one query. The admin user list needs
    /// both, and a per-user roots query would be one round trip per user.
    pub async fn all_users_with_roots(&self) -> DbResult<Vec<(User, Vec<RootRow>)>> {
        let c = self.0.lock().await;
        let mut stmt = c.prepare_cached(
            "SELECT u.id, u.name, u.is_admin != 0, u.active != 0, u.single_click != 0,
                    u.thumbnails != 0, u.language, u.default_root_id,
                    r.id, r.path, r.mode
             FROM users u
             LEFT JOIN user_roots r ON r.user_id = u.id
             ORDER BY u.id, r.id",
        )?;
        // Rows arrive grouped by user, so a new user id starts a new group.
        let mut out: Vec<(User, Vec<RootRow>)> = Vec::new();
        let mut rows = stmt.query([])?;
        while let Some(r) = rows.next()? {
            let uid: i64 = r.get(0)?;
            if out.last().is_none_or(|(u, _)| u.id != uid) {
                out.push((map_user(r)?, Vec::new()));
            }
            if let Some(id) = r.get::<_, Option<i64>>(8)? {
                out.last_mut().expect("pushed above").1.push(RootRow {
                    id,
                    path: r.get(9)?,
                    mode: r.get::<_, SqlMode>(10)?.0,
                });
            }
        }
        Ok(out)
    }

    pub async fn find_user_by_id(&self, id: i64) -> DbResult<Option<User>> {
        let c = self.0.lock().await;
        c.query_row(
            "SELECT id, name, is_admin != 0, active != 0, single_click != 0, thumbnails != 0,
                    language, default_root_id
             FROM users WHERE id = ?1",
            [id],
            map_user,
        )
        .optional()
    }

    pub async fn find_user_by_name(&self, name: &str) -> DbResult<Option<User>> {
        let c = self.0.lock().await;
        c.query_row(
            "SELECT id, name, is_admin != 0, active != 0, single_click != 0, thumbnails != 0,
                    language, default_root_id
             FROM users WHERE name = ?1",
            [name],
            map_user,
        )
        .optional()
    }

    pub async fn count_admins(&self) -> DbResult<i64> {
        let c = self.0.lock().await;
        c.query_row(
            "SELECT COUNT(*) FROM users WHERE is_admin = 1 AND active = 1",
            [],
            |r| r.get(0),
        )
    }

    /// Create a user with the given roots (path, mode) pairs.
    pub async fn create_user(
        &self,
        name: &str,
        pass_hash: &str,
        is_admin: bool,
        roots: &[(String, Mode)],
    ) -> DbResult<User> {
        let mut c = self.0.lock().await;
        let tx = c.transaction()?;
        tx.execute(
            "INSERT INTO users (name, pass_hash, is_admin, active, created_at)
             VALUES (?1, ?2, ?3, 1, ?4)",
            params![name, pass_hash, is_admin as i64, now()],
        )?;
        let user_id = tx.last_insert_rowid();
        for (path, mode) in roots {
            tx.execute(
                "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, ?2, ?3)",
                params![user_id, path, SqlMode(*mode)],
            )?;
        }
        tx.commit()?;
        Ok(User {
            id: user_id,
            name: name.to_string(),
            is_admin,
            active: true,
            single_click: false,
            thumbnails: true,
            language: None,
            default_root_id: None,
        })
    }

    pub async fn set_user_single_click(&self, id: i64, single_click: bool) -> DbResult<()> {
        let c = self.0.lock().await;
        c.execute(
            "UPDATE users SET single_click = ?1 WHERE id = ?2",
            params![single_click as i64, id],
        )?;
        Ok(())
    }

    pub async fn set_user_thumbnails(&self, id: i64, thumbnails: bool) -> DbResult<()> {
        let c = self.0.lock().await;
        c.execute(
            "UPDATE users SET thumbnails = ?1 WHERE id = ?2",
            params![thumbnails as i64, id],
        )?;
        Ok(())
    }

    pub async fn set_user_default_root(&self, id: i64, root_id: Option<i64>) -> DbResult<()> {
        let c = self.0.lock().await;
        c.execute(
            "UPDATE users SET default_root_id = ?1 WHERE id = ?2",
            params![root_id, id],
        )?;
        Ok(())
    }

    pub async fn set_user_language(&self, id: i64, language: Option<&str>) -> DbResult<()> {
        let c = self.0.lock().await;
        c.execute(
            "UPDATE users SET language = ?1 WHERE id = ?2",
            params![language, id],
        )?;
        Ok(())
    }

    /// Apply an admin edit atomically: every `Some` field is written in one
    /// transaction, so a failure midway leaves the user unchanged.
    pub async fn update_user(
        &self,
        id: i64,
        pass_hash: Option<&str>,
        is_admin: Option<bool>,
        active: Option<bool>,
        roots: Option<&[(String, Mode)]>,
    ) -> DbResult<()> {
        let mut c = self.0.lock().await;
        let tx = c.transaction()?;
        if let Some(h) = pass_hash {
            set_password(&tx, id, h)?;
        }
        if let Some(a) = is_admin {
            tx.execute(
                "UPDATE users SET is_admin = ?1 WHERE id = ?2",
                params![a as i64, id],
            )?;
        }
        if let Some(a) = active {
            tx.execute(
                "UPDATE users SET active = ?1 WHERE id = ?2",
                params![a as i64, id],
            )?;
        }
        if let Some(roots) = roots {
            tx.execute("DELETE FROM user_roots WHERE user_id = ?1", [id])?;
            for (path, mode) in roots {
                tx.execute(
                    "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, ?2, ?3)",
                    params![id, path, SqlMode(*mode)],
                )?;
            }
        }
        tx.commit()
    }

    /// Delete a user. `false` means no row matched.
    pub async fn delete_user(&self, id: i64) -> DbResult<bool> {
        let c = self.0.lock().await;
        Ok(c.execute("DELETE FROM users WHERE id = ?1", [id])? > 0)
    }

    // ---------- shares ----------

    #[allow(clippy::too_many_arguments)] // one row's columns, all required
    pub async fn create_share(
        &self,
        creator_id: i64,
        token: &str,
        target: &str,
        is_file: bool,
        mode: Mode,
        expires_at: Option<&str>,
        password_hash: Option<&str>,
    ) -> DbResult<ShareRow> {
        let c = self.0.lock().await;
        c.execute(
            "INSERT INTO shares
               (token, creator_id, target, is_file, mode, created_at, expires_at, password_hash)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)",
            params![
                token,
                creator_id,
                target,
                is_file as i64,
                SqlMode(mode),
                now(),
                expires_at,
                password_hash
            ],
        )?;
        let id = c.last_insert_rowid();
        Ok(ShareRow {
            id,
            token: token.to_string(),
            creator_id,
            target: target.to_string(),
            is_file,
            mode,
            created_at: now(),
            expires_at: expires_at.map(|s| s.to_string()),
            password_hash: password_hash.map(|s| s.to_string()),
        })
    }

    /// Record that a visitor entered `share_id`'s password, and return the
    /// token that proves it (the value of their unlock cookie).
    pub async fn create_share_unlock(&self, share_id: i64) -> DbResult<String> {
        let token = crate::auth::random_token();
        let c = self.0.lock().await;
        // Old unlocks go first. The cookie carrying them is a session
        // cookie, so it is already gone from every browser; without this the
        // rows would accumulate forever, one per unlock.
        c.execute(
            "DELETE FROM share_unlocks WHERE created_at < ?1",
            [expiry_cutoff()],
        )?;
        c.execute(
            "INSERT INTO share_unlocks (token, share_id, created_at) VALUES (?1, ?2, ?3)",
            params![token, share_id, now()],
        )?;
        Ok(token)
    }

    /// Whether `token` is a live unlock for `share_id`.
    ///
    /// The share id is part of the lookup, so an unlock for one share cannot
    /// open another.
    pub async fn share_unlock_valid(&self, token: &str, share_id: i64) -> DbResult<bool> {
        let c = self.0.lock().await;
        let mut stmt =
            c.prepare_cached("SELECT 1 FROM share_unlocks WHERE token = ?1 AND share_id = ?2")?;
        Ok(stmt
            .query_row(params![token, share_id], |_| Ok(()))
            .optional()?
            .is_some())
    }

    pub async fn share_by_token(&self, token: &str) -> DbResult<Option<ShareRow>> {
        let c = self.0.lock().await;
        let sql = "SELECT id, token, creator_id, target, is_file, mode, created_at, expires_at,
                          password_hash
                   FROM shares WHERE token = ?1";
        let mut stmt = c.prepare_cached(sql)?;
        stmt.query_row([token], map_share).optional()
    }

    pub async fn user_shares(&self, creator_id: i64) -> DbResult<Vec<ShareRow>> {
        let c = self.0.lock().await;
        let sql = "SELECT id, token, creator_id, target, is_file, mode, created_at, expires_at,
                          password_hash
                   FROM shares WHERE creator_id = ?1 ORDER BY id DESC";
        let mut stmt = c.prepare_cached(sql)?;
        let rows = stmt.query_map([creator_id], map_share)?;
        rows.collect()
    }

    /// Revoke every share on `target` or on anything beneath it. Returns how
    /// many were dropped.
    ///
    /// Called when a path stops meaning what it meant: the item was deleted,
    /// renamed, or moved away. A share names a path, and a path is not a
    /// stable identity, so leaving the row behind would let a *new* item that
    /// later takes the freed path inherit the old link's audience.
    ///
    /// `substr` rather than `LIKE`: a target containing `%` or `_` would make
    /// a `LIKE` pattern over-match and revoke unrelated shares.
    pub async fn revoke_shares_at(&self, target: &str) -> DbResult<usize> {
        let c = self.0.lock().await;
        c.execute(
            "DELETE FROM shares
             WHERE target = ?1 OR substr(target, 1, length(?1) + 1) = ?1 || '/'",
            [target],
        )
    }

    /// Delete one of `creator_id`'s shares. `false` means no row matched.
    pub async fn delete_share(&self, id: i64, creator_id: i64) -> DbResult<bool> {
        let c = self.0.lock().await;
        let n = c.execute(
            "DELETE FROM shares WHERE id = ?1 AND creator_id = ?2",
            params![id, creator_id],
        )?;
        Ok(n > 0)
    }

    /// Every share on the server with its creator. Grouped by account name,
    /// newest link within an account first.
    ///
    /// The join cannot miss: `shares.creator_id` cascades on delete, so a share
    /// never outlives the account that made it.
    pub async fn all_shares_with_creators(&self) -> DbResult<Vec<ShareWithCreator>> {
        let c = self.0.lock().await;
        // Columns 0..8 are `map_share`'s order, unchanged from `user_shares`.
        let sql = "SELECT s.id, s.token, s.creator_id, s.target, s.is_file, s.mode,
                          s.created_at, s.expires_at, s.password_hash,
                          u.name, u.active != 0
                   FROM shares s
                   JOIN users u ON u.id = s.creator_id
                   ORDER BY u.name COLLATE NOCASE, s.id DESC";
        let mut stmt = c.prepare_cached(sql)?;
        let rows = stmt.query_map([], |r| {
            Ok(ShareWithCreator {
                share: map_share(r)?,
                creator_name: r.get(9)?,
                creator_active: r.get(10)?,
            })
        })?;
        rows.collect()
    }

    /// Revoke a share whoever created it. The owner-scoped
    /// [`Self::delete_share`] is what the user-facing API uses.
    pub async fn admin_delete_share(&self, id: i64) -> DbResult<bool> {
        let c = self.0.lock().await;
        Ok(c.execute("DELETE FROM shares WHERE id = ?1", [id])? > 0)
    }

    // ---------- settings ----------

    /// Folders excluded from search, as paths relative to the server root.
    ///
    /// Stored as one JSON array in a settings row. A table of its own would
    /// be overkill for a hand-edited list read once per search.
    pub async fn search_excludes(&self) -> DbResult<Vec<String>> {
        let raw = self.get_setting("search_excludes").await?;
        // Normalised on read as well as on write. A value edited straight
        // into the database would otherwise never match: `is_excluded`
        // compares against paths with no slash at either end.
        let clean = |v: Vec<String>| -> Vec<String> {
            v.into_iter()
                .map(|p| p.trim().replace('\\', "/").trim_matches('/').to_string())
                .filter(|p| !p.is_empty() && p != ".")
                .collect()
        };
        // A hand-edited, unparseable value falls back to no exclusions,
        // the same as an absent row.
        Ok(raw
            .as_deref()
            .and_then(|v| serde_json::from_str::<Vec<String>>(v).ok())
            .map(clean)
            .unwrap_or_default())
    }

    pub async fn set_search_excludes(&self, paths: &[String]) -> DbResult<()> {
        let json = serde_json::to_string(paths).unwrap_or_else(|_| "[]".to_string());
        self.set_setting("search_excludes", &json).await
    }

    pub async fn get_setting(&self, key: &str) -> DbResult<Option<String>> {
        let c = self.0.lock().await;
        let mut stmt = c.prepare_cached("SELECT value FROM settings WHERE key = ?1")?;
        stmt.query_row([key], |r| r.get(0)).optional()
    }

    pub async fn set_setting(&self, key: &str, value: &str) -> DbResult<()> {
        let c = self.0.lock().await;
        c.execute(
            "INSERT INTO settings (key, value) VALUES (?1, ?2)
             ON CONFLICT(key) DO UPDATE SET value = ?2",
            params![key, value],
        )?;
        Ok(())
    }

    /// Whether users may create writable (read-write) shares. Off by default;
    /// the admin setting gates it.
    pub async fn allow_writable_shares(&self) -> DbResult<bool> {
        Ok(self.get_setting("allow_writable_shares").await?.as_deref() == Some("1"))
    }

    pub async fn set_allow_writable_shares(&self, v: bool) -> DbResult<()> {
        self.set_setting("allow_writable_shares", if v { "1" } else { "0" })
            .await
    }
}

/// Write a new password hash and drop every session that was opened with the
/// old one.
///
/// The two belong together: a password is changed because the old one is
/// suspect (an admin resetting a compromised account), and a session that
/// survives the reset leaves whoever holds it signed in. Takes the
/// transaction so the caller can bundle it with its other edits.
fn set_password(tx: &rusqlite::Transaction<'_>, id: i64, pass_hash: &str) -> DbResult<()> {
    tx.execute(
        "UPDATE users SET pass_hash = ?1 WHERE id = ?2",
        params![pass_hash, id],
    )?;
    tx.execute("DELETE FROM sessions WHERE user_id = ?1", [id])?;
    Ok(())
}

/// Column order matched by the four `users` SELECTs above.
fn map_user(r: &rusqlite::Row) -> DbResult<User> {
    Ok(User {
        id: r.get(0)?,
        name: r.get(1)?,
        is_admin: r.get(2)?,
        active: r.get(3)?,
        single_click: r.get(4)?,
        thumbnails: r.get(5)?,
        language: r.get(6)?,
        default_root_id: r.get(7)?,
    })
}

/// Column order matched by the two `shares` SELECTs above.
fn map_share(r: &rusqlite::Row) -> DbResult<ShareRow> {
    Ok(ShareRow {
        id: r.get(0)?,
        token: r.get(1)?,
        creator_id: r.get(2)?,
        target: r.get(3)?,
        is_file: r.get::<_, i64>(4)? != 0,
        mode: r.get::<_, SqlMode>(5)?.0,
        created_at: r.get(6)?,
        expires_at: r.get(7)?,
        password_hash: r.get(8)?,
    })
}

/// A hash of a random string nobody knows. Verified against when the login
/// name does not exist, so both paths cost one Argon2 run.
static DUMMY_HASH: std::sync::LazyLock<String> = std::sync::LazyLock::new(|| {
    crate::auth::hash_password(&crate::auth::random_token()).expect("argon2 hash")
});

/// How long an unlock row outlives its cookie. The cookie dies with the
/// browser, so this only bounds the rows left behind by closed sessions.
const UNLOCK_MAX_AGE_DAYS: i64 = 7;

/// The timestamp an unlock row must be newer than to survive a cleanup.
fn expiry_cutoff() -> String {
    (chrono::Utc::now() - chrono::Duration::days(UNLOCK_MAX_AGE_DAYS))
        .to_rfc3339_opts(chrono::SecondsFormat::Secs, true)
}

fn now() -> String {
    chrono::Utc::now().to_rfc3339_opts(chrono::SecondsFormat::Secs, true)
}

const SCHEMA_V1: &str = r#"
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE COLLATE NOCASE,
    pass_hash TEXT NOT NULL,
    is_admin INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS user_roots (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    path TEXT NOT NULL,
    mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
    UNIQUE (user_id, path)
);

CREATE TABLE IF NOT EXISTS shares (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    token TEXT NOT NULL UNIQUE,
    creator_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    target TEXT NOT NULL,
    is_file INTEGER NOT NULL,
    mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
    created_at TEXT NOT NULL,
    expires_at TEXT
);

CREATE TABLE IF NOT EXISTS sessions (
    token TEXT PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at TEXT NOT NULL,
    last_seen_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
);
INSERT OR IGNORE INTO settings (key, value) VALUES ('allow_writable_shares', '0');
"#;

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

    // Most tests use an in-memory DB (the file-based path is still covered
    // by `v1_db_migrates_to_v2` and the integration harness' `Db::open`).
    async fn mem() -> Db {
        Db::open_in_memory().await.unwrap()
    }

    /// `update_user` is the only way production edits these fields, so the
    /// tests exercise that path rather than per-field helpers.
    async fn edit(db: &Db, id: i64, pass: Option<&str>, admin: Option<bool>, active: Option<bool>) {
        db.update_user(id, pass, admin, active, None).await.unwrap();
    }

    async fn db_with_admin() -> (Db, User) {
        let db = mem().await;
        let hash = crate::auth::hash_password("admin1234").unwrap();
        let admin = db.create_admin("admin", &hash).await.unwrap().unwrap();
        (db, admin)
    }

    #[tokio::test]
    async fn fresh_db_state() {
        let db = mem().await;
        assert_eq!(db.user_count().await.unwrap(), 0);
        assert_eq!(db.count_admins().await.unwrap(), 0);
        assert!(!db.allow_writable_shares().await.unwrap());
        assert!(db.find_user_by_name("nobody").await.unwrap().is_none());
        assert!(db.find_user_by_id(1).await.unwrap().is_none());
        assert!(db.all_users_with_roots().await.unwrap().is_empty());
    }

    #[tokio::test]
    async fn v1_db_migrates_to_v2() {
        let dir = tempfile::tempdir().unwrap();
        let path = dir.path().join("legacy.sqlite");
        {
            let conn = rusqlite::Connection::open(&path).unwrap();
            conn.execute_batch(SCHEMA_V1).unwrap();
            conn.execute(
                "INSERT INTO users (name, pass_hash, is_admin, created_at)
                 VALUES ('legacy', 'hash', 1, '2024-01-01T00:00:00Z')",
                [],
            )
            .unwrap();
            conn.execute(
                "INSERT INTO user_roots (user_id, path, mode) VALUES (1, 'docs', 'rw')",
                [],
            )
            .unwrap();
        }
        let db = Db::open(&path).await.unwrap();
        assert_eq!(db.user_count().await.unwrap(), 1);
        let u = db.find_user_by_name("legacy").await.unwrap().unwrap();
        assert!(u.active, "v2 migration must default active to true");
        assert!(u.is_admin);
        assert_eq!(db.user_roots(u.id).await.unwrap().len(), 1);
        // Migrations are idempotent.
        let db2 = Db::open(&path).await.unwrap();
        assert_eq!(db2.user_count().await.unwrap(), 1);
        assert!(
            db2.find_user_by_name("legacy")
                .await
                .unwrap()
                .unwrap()
                .active
        );
    }

    #[tokio::test]
    async fn admin_user_and_passwords() {
        let (db, admin) = db_with_admin().await;
        assert!(admin.is_admin);
        assert!(admin.active);
        // Root "." rw is assigned by create_admin.
        let roots = db.user_roots(admin.id).await.unwrap();
        assert_eq!(roots.len(), 1);
        assert_eq!(roots[0].path, ".");
        assert_eq!(roots[0].mode, Mode::Rw);

        assert!(
            db.verify_password("admin", "admin1234")
                .await
                .unwrap()
                .is_some()
        );
        assert!(db.verify_password("admin", "nope").await.unwrap().is_none());
        // Name lookup is case-insensitive (COLLATE NOCASE).
        assert!(
            db.verify_password("ADMIN", "admin1234")
                .await
                .unwrap()
                .is_some()
        );
        // Disabled users cannot verify.
        edit(&db, admin.id, None, None, Some(false)).await;
        assert!(
            db.verify_password("admin", "admin1234")
                .await
                .unwrap()
                .is_none()
        );
        edit(&db, admin.id, None, None, Some(true)).await;
        assert!(
            db.verify_password("admin", "admin1234")
                .await
                .unwrap()
                .is_some()
        );
    }

    #[tokio::test]
    async fn setup_is_won_by_exactly_one_caller() {
        let db = mem().await;
        let hash = crate::auth::hash_password("admin1234").unwrap();
        assert!(db.create_admin("first", &hash).await.unwrap().is_some());
        // The guard lives in the insert, so a different name loses too.
        assert!(db.create_admin("second", &hash).await.unwrap().is_none());
        assert_eq!(db.user_count().await.unwrap(), 1);
        // The loser rolled back cleanly: no orphaned root row.
        let first = db.find_user_by_name("first").await.unwrap().unwrap();
        assert_eq!(db.user_roots(first.id).await.unwrap().len(), 1);
    }

    #[tokio::test]
    async fn changing_a_password_drops_that_users_sessions() {
        let (db, admin) = db_with_admin().await;
        let h = crate::auth::hash_password("bobpass1").unwrap();
        let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
        db.create_session(admin.id, "admin-tok").await.unwrap();
        db.create_session(bob.id, "bob-tok-1").await.unwrap();
        db.create_session(bob.id, "bob-tok-2").await.unwrap();

        let new_h = crate::auth::hash_password("bobpass2").unwrap();
        edit(&db, bob.id, Some(&new_h), None, None).await;
        assert!(
            db.session_user_with_roots("bob-tok-1")
                .await
                .unwrap()
                .is_none()
        );
        assert!(
            db.session_user_with_roots("bob-tok-2")
                .await
                .unwrap()
                .is_none()
        );
        // Only the reset user is signed out.
        assert!(
            db.session_user_with_roots("admin-tok")
                .await
                .unwrap()
                .is_some()
        );

        // The admin-edit path bundles the same rule into its transaction.
        db.create_session(bob.id, "bob-tok-3").await.unwrap();
        let h3 = crate::auth::hash_password("bobpass3").unwrap();
        db.update_user(bob.id, Some(&h3), None, None, None)
            .await
            .unwrap();
        assert!(
            db.session_user_with_roots("bob-tok-3")
                .await
                .unwrap()
                .is_none()
        );
        // An edit that leaves the password alone keeps the session.
        db.create_session(bob.id, "bob-tok-4").await.unwrap();
        db.update_user(bob.id, None, Some(true), None, None)
            .await
            .unwrap();
        assert!(
            db.session_user_with_roots("bob-tok-4")
                .await
                .unwrap()
                .is_some()
        );
    }

    #[tokio::test]
    async fn sessions_lifecycle() {
        let (db, admin) = db_with_admin().await;
        assert!(
            db.session_user_with_roots("ghost-token")
                .await
                .unwrap()
                .is_none()
        );
        db.create_session(admin.id, "tok1").await.unwrap();
        let (u, _) = db.session_user_with_roots("tok1").await.unwrap().unwrap();
        assert_eq!(u.id, admin.id);
        // Disabling the user invalidates existing sessions.
        edit(&db, admin.id, None, None, Some(false)).await;
        assert!(db.session_user_with_roots("tok1").await.unwrap().is_none());
        edit(&db, admin.id, None, None, Some(true)).await;
        assert!(db.session_user_with_roots("tok1").await.unwrap().is_some());
        db.delete_session("tok1").await.unwrap();
        assert!(db.session_user_with_roots("tok1").await.unwrap().is_none());
    }

    #[tokio::test]
    async fn user_crud_and_roots() {
        let (db, _admin) = db_with_admin().await;
        let h = crate::auth::hash_password("bobpass1").unwrap();
        let bob = db
            .create_user("bob", &h, false, &[("docs".into(), Mode::Rw)])
            .await
            .unwrap();
        assert!(!bob.is_admin);
        assert!(bob.active);

        // Duplicate name (case-insensitive) is rejected.
        let h2 = crate::auth::hash_password("carolpass1").unwrap();
        assert!(db.create_user("BOB", &h2, false, &[]).await.is_err());
        assert!(db.create_user("carol", &h2, false, &[]).await.is_ok());

        // Lookup helpers.
        assert_eq!(
            db.find_user_by_name("Bob").await.unwrap().unwrap().id,
            bob.id
        );
        assert_eq!(
            db.find_user_by_id(bob.id).await.unwrap().unwrap().name,
            "bob"
        );
        assert!(db.find_user_by_name("dave").await.unwrap().is_none());
        assert_eq!(db.all_users_with_roots().await.unwrap().len(), 3);

        // Root replacement semantics.
        let roots = db.user_roots(bob.id).await.unwrap();
        assert_eq!(roots.len(), 1);
        db.update_user(
            bob.id,
            None,
            None,
            None,
            Some(&[(".".into(), Mode::Ro), ("docs".into(), Mode::Rw)]),
        )
        .await
        .unwrap();
        let roots = db.user_roots(bob.id).await.unwrap();
        assert_eq!(roots.len(), 2);
        assert!(roots.iter().any(|r| r.path == "." && r.mode == Mode::Ro));
        db.update_user(bob.id, None, None, None, Some(&[]))
            .await
            .unwrap();
        assert!(db.user_roots(bob.id).await.unwrap().is_empty());

        // Password update.
        let new_h = crate::auth::hash_password("bobpass2").unwrap();
        edit(&db, bob.id, Some(&new_h), None, None).await;
        assert!(
            db.verify_password("bob", "bobpass1")
                .await
                .unwrap()
                .is_none()
        );
        assert!(
            db.verify_password("bob", "bobpass2")
                .await
                .unwrap()
                .is_some()
        );

        // Admin flag + count (only active admins count).
        edit(&db, bob.id, None, Some(true), None).await;
        assert_eq!(db.count_admins().await.unwrap(), 2);
        edit(&db, bob.id, None, None, Some(false)).await;
        assert_eq!(db.count_admins().await.unwrap(), 1);
        edit(&db, bob.id, None, Some(false), None).await;

        // Deletion.
        assert!(db.delete_user(bob.id).await.unwrap());
        assert!(db.find_user_by_id(bob.id).await.unwrap().is_none());
        assert!(!db.delete_user(bob.id).await.unwrap());
        assert_eq!(db.user_count().await.unwrap(), 2);
    }

    fn share_row(expires_at: Option<&str>) -> ShareRow {
        ShareRow {
            id: 1,
            token: "t".into(),
            creator_id: 1,
            target: "docs".into(),
            is_file: false,
            mode: Mode::Ro,
            created_at: "2024-01-01T00:00:00Z".into(),
            expires_at: expires_at.map(str::to_string),
            password_hash: None,
        }
    }

    #[test]
    fn share_expiry_logic() {
        assert!(!share_row(None).is_expired());
        assert!(!share_row(Some("2999-01-01T00:00:00Z")).is_expired());
        assert!(share_row(Some("2000-01-01T00:00:00Z")).is_expired());
        // Unparseable expiry → treated as not expired (fail open for reads).
        assert!(!share_row(Some("not-a-date")).is_expired());
    }

    #[tokio::test]
    async fn shares_crud() {
        let (db, admin) = db_with_admin().await;
        let s1 = db
            .create_share(admin.id, "tok-a", "docs", false, Mode::Ro, None, None)
            .await
            .unwrap();
        let s2 = db
            .create_share(
                admin.id,
                "tok-b",
                "file.txt",
                true,
                Mode::Rw,
                Some("2999-01-01T00:00:00Z"),
                None,
            )
            .await
            .unwrap();
        assert!(s2.id > s1.id);

        let found = db.share_by_token("tok-b").await.unwrap().unwrap();
        assert!(found.is_file);
        assert_eq!(found.mode, Mode::Rw);
        assert!(db.share_by_token("nope").await.unwrap().is_none());

        // Listed newest-first.
        let list = db.user_shares(admin.id).await.unwrap();
        assert_eq!(list.len(), 2);
        assert_eq!(list[0].id, s2.id);
        // Other users see nothing.
        let h = crate::auth::hash_password("bobpass1").unwrap();
        let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
        assert!(db.user_shares(bob.id).await.unwrap().is_empty());

        // Only the creator can delete.
        assert!(!db.delete_share(s1.id, bob.id).await.unwrap());
        assert!(db.delete_share(s1.id, admin.id).await.unwrap());
        assert!(db.share_by_token("tok-a").await.unwrap().is_none());
        assert!(!db.delete_share(s1.id, admin.id).await.unwrap());
    }

    /// The unlock token is what a visitor's cookie carries, so an unlock
    /// that opened the wrong share would be a full bypass of the password.
    #[tokio::test]
    async fn share_unlocks_are_bound_to_one_share() {
        let (db, admin) = db_with_admin().await;
        let a = db
            .create_share(
                admin.id,
                "tok-a",
                "docs",
                false,
                Mode::Ro,
                None,
                Some("hash"),
            )
            .await
            .unwrap();
        let b = db
            .create_share(
                admin.id,
                "tok-b",
                "other",
                false,
                Mode::Ro,
                None,
                Some("hash"),
            )
            .await
            .unwrap();
        assert_eq!(
            db.share_by_token("tok-a")
                .await
                .unwrap()
                .unwrap()
                .password_hash,
            Some("hash".to_string())
        );

        let unlock = db.create_share_unlock(a.id).await.unwrap();
        assert!(db.share_unlock_valid(&unlock, a.id).await.unwrap());
        assert!(!db.share_unlock_valid(&unlock, b.id).await.unwrap());
        assert!(!db.share_unlock_valid("nonsense", a.id).await.unwrap());

        // Deleting the share takes its unlocks with it, so a re-created
        // share that happened to reuse the id could not inherit them.
        assert!(db.delete_share(a.id, admin.id).await.unwrap());
        assert!(!db.share_unlock_valid(&unlock, a.id).await.unwrap());
    }

    #[tokio::test]
    async fn revoking_a_path_takes_its_descendants_only() {
        let (db, admin) = db_with_admin().await;
        let mk = async |token: &str, target: &str| {
            db.create_share(admin.id, token, target, false, Mode::Ro, None, None)
                .await
                .unwrap();
        };
        mk("t-self", "docs").await;
        mk("t-child", "docs/a.txt").await;
        mk("t-deep", "docs/inner/b.txt").await;
        // A sibling whose name merely starts with "docs" must survive.
        mk("t-sibling", "docs2/c.txt").await;
        mk("t-other", "src").await;
        // SQL wildcards in a path are literal characters, not patterns.
        mk("t-wild", "do%s/d.txt").await;

        assert_eq!(db.revoke_shares_at("docs").await.unwrap(), 3);
        for gone in ["t-self", "t-child", "t-deep"] {
            assert!(db.share_by_token(gone).await.unwrap().is_none(), "{gone}");
        }
        for kept in ["t-sibling", "t-other", "t-wild"] {
            assert!(db.share_by_token(kept).await.unwrap().is_some(), "{kept}");
        }
        // Revoking a path nobody shared is a no-op, not an error.
        assert_eq!(db.revoke_shares_at("nothing/here").await.unwrap(), 0);
    }

    #[tokio::test]
    async fn settings_round_trip() {
        let (db, _admin) = db_with_admin().await;
        assert!(!db.allow_writable_shares().await.unwrap());
        db.set_allow_writable_shares(true).await.unwrap();
        assert!(db.allow_writable_shares().await.unwrap());
        // Upsert semantics.
        db.set_allow_writable_shares(false).await.unwrap();
        assert!(!db.allow_writable_shares().await.unwrap());
        // Generic get/set.
        db.set_setting("custom", "v").await.unwrap();
        assert_eq!(
            db.get_setting("custom").await.unwrap().as_deref(),
            Some("v")
        );
        assert_eq!(db.get_setting("missing").await.unwrap(), None);
    }
}