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

use rusqlite::{params, Connection, OptionalExtension};

const SCHEMA_VERSION: i64 = 2;

#[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,
}

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

#[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,
    /// "rw" or "ro"
    pub mode: String,
    pub created_at: String,
    pub expires_at: 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,
        }
    }
}

#[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() {
            if !parent.as_os_str().is_empty() {
                std::fs::create_dir_all(parent)?;
            }
        }
        let conn = Connection::open(path)?;
        conn.pragma_update(None, "journal_mode", "WAL")?;
        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")?;
        }
        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) -> i64 {
        let c = self.0.lock().await;
        c.query_row("SELECT COUNT(*) FROM users", [], |r| r.get(0))
            .unwrap_or(0)
    }

    /// Create the first admin account with the whole root visible (read-write).
    /// Only valid while no users exist (enforced by the caller).
    pub async fn create_admin(&self, name: &str, pass_hash: &str) -> Result<User, rusqlite::Error> {
        let mut c = self.0.lock().await;
        let tx = c.transaction()?;
        tx.execute(
            "INSERT INTO users (name, pass_hash, is_admin, created_at)
             VALUES (?1, ?2, 1, ?3)",
            params![name, pass_hash, now()],
        )?;
        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(User {
            id: user_id,
            name: name.to_string(),
            is_admin: true,
            active: true,
        })
    }

    pub async fn verify_password(&self, name: &str, password: &str) -> Option<User> {
        let c = self.0.lock().await;
        let row: Option<(i64, String, bool, String, bool)> = c
            .query_row(
                "SELECT id, name, is_admin != 0, pass_hash, active != 0 FROM users WHERE name = ?1",
                [name],
                |r| Ok((r.get(0)?, r.get(1)?, r.get(2)?, r.get(3)?, r.get(4)?)),
            )
            .optional()
            .ok()
            .flatten();
        let (id, name, is_admin, hash, active) = row?;
        if !active {
            return None;
        }
        crate::auth::verify_password(password, &hash).then_some(User {
            id,
            name,
            is_admin,
            active,
        })
    }

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

    pub async fn create_session(&self, user_id: i64, token: &str) -> Result<(), rusqlite::Error> {
        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) -> Result<(), rusqlite::Error> {
        let c = self.0.lock().await;
        c.execute("DELETE FROM sessions WHERE token = ?1", [token])?;
        Ok(())
    }

    pub async fn session_user(&self, token: &str) -> Option<User> {
        let c = self.0.lock().await;
        c.query_row(
            "SELECT u.id, u.name, u.is_admin != 0, u.active != 0
             FROM sessions s JOIN users u ON u.id = s.user_id
             WHERE s.token = ?1 AND u.active = 1",
            [token],
            |r| {
                Ok(User {
                    id: r.get(0)?,
                    name: r.get(1)?,
                    is_admin: r.get(2)?,
                    active: r.get(3)?,
                })
            },
        )
        .ok()
    }

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

    pub async fn user_roots(&self, user_id: i64) -> Vec<RootRow> {
        let c = self.0.lock().await;
        let mut out = Vec::new();
        let Ok(mut stmt) =
            c.prepare("SELECT id, path, mode FROM user_roots WHERE user_id = ?1 ORDER BY id")
        else {
            return out;
        };
        if let Ok(rows) = stmt.query_map([user_id], |r| {
            Ok(RootRow {
                id: r.get(0)?,
                path: r.get(1)?,
                mode: r.get(2)?,
            })
        }) {
            out.extend(rows.flatten());
        }
        out
    }

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

    pub async fn all_users(&self) -> Vec<User> {
        let c = self.0.lock().await;
        let mut out = Vec::new();
        if let Ok(mut stmt) =
            c.prepare("SELECT id, name, is_admin != 0, active != 0 FROM users ORDER BY id")
        {
            if let Ok(rows) = stmt.query_map([], |r| {
                Ok(User {
                    id: r.get(0)?,
                    name: r.get(1)?,
                    is_admin: r.get(2)?,
                    active: r.get(3)?,
                })
            }) {
                out.extend(rows.flatten());
            }
        }
        out
    }

    pub async fn find_user_by_id(&self, id: i64) -> Option<User> {
        let c = self.0.lock().await;
        c.query_row(
            "SELECT id, name, is_admin != 0, active != 0 FROM users WHERE id = ?1",
            [id],
            |r| {
                Ok(User {
                    id: r.get(0)?,
                    name: r.get(1)?,
                    is_admin: r.get(2)?,
                    active: r.get(3)?,
                })
            },
        )
        .ok()
    }

    pub async fn find_user_by_name(&self, name: &str) -> Option<User> {
        let c = self.0.lock().await;
        c.query_row(
            "SELECT id, name, is_admin != 0, active != 0 FROM users WHERE name = ?1",
            [name],
            |r| {
                Ok(User {
                    id: r.get(0)?,
                    name: r.get(1)?,
                    is_admin: r.get(2)?,
                    active: r.get(3)?,
                })
            },
        )
        .ok()
    }

    pub async fn count_admins(&self) -> 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),
        )
        .unwrap_or(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, String)],
    ) -> Result<User, rusqlite::Error> {
        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, mode],
            )?;
        }
        tx.commit()?;
        Ok(User {
            id: user_id,
            name: name.to_string(),
            is_admin,
            active: true,
        })
    }

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

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

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

    pub async fn delete_user(&self, id: i64) -> bool {
        let c = self.0.lock().await;
        c.execute("DELETE FROM users WHERE id = ?1", [id])
            .ok()
            .map(|n| n > 0)
            .unwrap_or(false)
    }

    /// Replace a user's roots with the given (path, mode) pairs.
    pub async fn set_user_roots(
        &self,
        user_id: i64,
        roots: &[(String, String)],
    ) -> Result<(), rusqlite::Error> {
        let mut c = self.0.lock().await;
        let tx = c.transaction()?;
        tx.execute("DELETE FROM user_roots WHERE user_id = ?1", [user_id])?;
        for (path, mode) in roots {
            tx.execute(
                "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, ?2, ?3)",
                params![user_id, path, mode],
            )?;
        }
        tx.commit()?;
        Ok(())
    }

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

    pub async fn create_share(
        &self,
        creator_id: i64,
        token: &str,
        target: &str,
        is_file: bool,
        mode: &str,
        expires_at: Option<&str>,
    ) -> Result<ShareRow, rusqlite::Error> {
        let c = self.0.lock().await;
        c.execute(
            "INSERT INTO shares (token, creator_id, target, is_file, mode, created_at, expires_at)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
            params![
                token,
                creator_id,
                target,
                is_file as i64,
                mode,
                now(),
                expires_at
            ],
        )?;
        let id = c.last_insert_rowid();
        Ok(ShareRow {
            id,
            token: token.to_string(),
            creator_id,
            target: target.to_string(),
            is_file,
            mode: mode.to_string(),
            created_at: now(),
            expires_at: expires_at.map(|s| s.to_string()),
        })
    }

    pub async fn share_by_token(&self, token: &str) -> Option<ShareRow> {
        let c = self.0.lock().await;
        let sql = SHARE_BY_TOKEN.as_str();
        c.query_row(sql, [token], map_share).ok()
    }

    pub async fn user_shares(&self, creator_id: i64) -> Vec<ShareRow> {
        let c = self.0.lock().await;
        let mut out = Vec::new();
        let sql = USER_SHARES.as_str();
        if let Ok(mut stmt) = c.prepare(sql) {
            if let Ok(rows) = stmt.query_map([creator_id], map_share) {
                out.extend(rows.flatten());
            }
        }
        out
    }

    pub async fn delete_share(&self, id: i64, creator_id: i64) -> bool {
        let c = self.0.lock().await;
        c.execute(
            "DELETE FROM shares WHERE id = ?1 AND creator_id = ?2",
            params![id, creator_id],
        )
        .ok()
        .map(|n| n > 0)
        .unwrap_or(false)
    }

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

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

    pub async fn set_setting(&self, key: &str, value: &str) -> Result<(), rusqlite::Error> {
        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) -> bool {
        self.get_setting("allow_writable_shares").await.as_deref() == Some("1")
    }

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

const SHARE_COLS: &str = "id, token, creator_id, target, is_file, mode, created_at, expires_at";
static SHARE_BY_TOKEN: std::sync::LazyLock<String> =
    std::sync::LazyLock::new(|| format!("SELECT {SHARE_COLS} FROM shares WHERE token = ?1"));
static USER_SHARES: std::sync::LazyLock<String> = std::sync::LazyLock::new(|| {
    format!("SELECT {SHARE_COLS} FROM shares WHERE creator_id = ?1 ORDER BY id DESC")
});

fn map_share(r: &rusqlite::Row) -> rusqlite::Result<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(5)?,
        created_at: r.get(6)?,
        expires_at: r.get(7)?,
    })
}

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()
    }

    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();
        (db, admin)
    }

    #[tokio::test]
    async fn fresh_db_state() {
        let db = mem().await;
        assert_eq!(db.user_count().await, 0);
        assert_eq!(db.count_admins().await, 0);
        assert!(!db.allow_writable_shares().await);
        assert!(db.find_user_by_name("nobody").await.is_none());
        assert!(db.find_user_by_id(1).await.is_none());
        assert!(db.all_users().await.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, 1);
        let u = db.find_user_by_name("legacy").await.unwrap();
        assert!(u.active, "v2 migration must default active to true");
        assert!(u.is_admin);
        assert_eq!(db.user_roots(u.id).await.len(), 1);
        // Migrations are idempotent.
        let db2 = Db::open(&path).await.unwrap();
        assert_eq!(db2.user_count().await, 1);
        assert!(db2.find_user_by_name("legacy").await.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;
        assert_eq!(roots.len(), 1);
        assert_eq!(roots[0].path, ".");
        assert_eq!(roots[0].mode, "rw");

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

    #[tokio::test]
    async fn sessions_lifecycle() {
        let (db, admin) = db_with_admin().await;
        assert!(db.session_user("ghost-token").await.is_none());
        db.create_session(admin.id, "tok1").await.unwrap();
        let u = db.session_user("tok1").await.unwrap();
        assert_eq!(u.id, admin.id);
        // Disabling the user invalidates existing sessions.
        db.set_user_active(admin.id, false).await.unwrap();
        assert!(db.session_user("tok1").await.is_none());
        db.set_user_active(admin.id, true).await.unwrap();
        assert!(db.session_user("tok1").await.is_some());
        db.delete_session("tok1").await.unwrap();
        assert!(db.session_user("tok1").await.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(), "rw".into())])
            .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().id, bob.id);
        assert_eq!(db.find_user_by_id(bob.id).await.unwrap().name, "bob");
        assert!(db.find_user_by_name("dave").await.is_none());
        assert_eq!(db.all_users().await.len(), 3);

        // Root replacement semantics.
        let roots = db.user_roots(bob.id).await;
        assert_eq!(roots.len(), 1);
        db.set_user_roots(
            bob.id,
            &[(".".into(), "ro".into()), ("docs".into(), "rw".into())],
        )
        .await
        .unwrap();
        let roots = db.user_roots(bob.id).await;
        assert_eq!(roots.len(), 2);
        assert!(roots.iter().any(|r| r.path == "." && r.mode == "ro"));
        db.set_user_roots(bob.id, &[]).await.unwrap();
        assert!(db.user_roots(bob.id).await.is_empty());

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

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

        // Deletion.
        assert!(db.delete_user(bob.id).await);
        assert!(db.find_user_by_id(bob.id).await.is_none());
        assert!(!db.delete_user(bob.id).await);
        assert_eq!(db.user_count().await, 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: "ro".into(),
            created_at: "2024-01-01T00:00:00Z".into(),
            expires_at: expires_at.map(str::to_string),
        }
    }

    #[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, "ro", None)
            .await
            .unwrap();
        let s2 = db
            .create_share(
                admin.id,
                "tok-b",
                "file.txt",
                true,
                "rw",
                Some("2999-01-01T00:00:00Z"),
            )
            .await
            .unwrap();
        assert!(s2.id > s1.id);

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

        // Listed newest-first.
        let list = db.user_shares(admin.id).await;
        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.is_empty());

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

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