//! SQLite connection helper.
//!
//! Migrations stay per-tool: `sqlx::migrate!` resolves its path at compile
//! time relative to the calling crate, so each daemon runs its own
//! `sqlx::migrate!("./migrations").run(&pool)`. This module owns only the
//! connect-with-sane-defaults step, which is identical everywhere.

use anyhow::Result;
use sqlx::SqlitePool;
use sqlx::sqlite::{SqliteConnectOptions, SqliteJournalMode, SqlitePoolOptions, SqliteSynchronous};
use std::path::Path;
use std::str::FromStr;
use std::time::Duration;

/// Open (creating if missing) a pooled SQLite connection with sane defaults for
/// a concurrent multi-writer daemon. Bento fans builds out across targets, each
/// on its own blocking thread writing to this pool, so the default
/// rollback-journal mode with `busy_timeout = 0` would surface spurious
/// `SQLITE_BUSY` errors under contention. WAL lets readers run alongside a
/// writer; `busy_timeout` makes a contending writer wait rather than fail;
/// `synchronous = NORMAL` is the standard durable-enough pairing for WAL.
pub async fn connect(path: &Path) -> Result<SqlitePool> {
    let url = format!("sqlite://{}?mode=rwc", path.display());
    let opts = SqliteConnectOptions::from_str(&url)?
        .create_if_missing(true)
        .foreign_keys(true)
        .journal_mode(SqliteJournalMode::Wal)
        .synchronous(SqliteSynchronous::Normal)
        .busy_timeout(Duration::from_secs(5));
    let pool = SqlitePoolOptions::new()
        .max_connections(4)
        .connect_with(opts)
        .await?;
    Ok(pool)
}

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

    #[tokio::test]
    async fn connect_creates_and_enables_foreign_keys() {
        let dir = tempfile::tempdir().unwrap();
        let pool = connect(&dir.path().join("t.db")).await.unwrap();
        let fk: i64 = sqlx::query_scalar("PRAGMA foreign_keys")
            .fetch_one(&pool)
            .await
            .unwrap();
        assert_eq!(fk, 1);
    }
}