//! SQLite through `sqlx` (hub spec §3.1, §17.2): WAL, foreign keys on,

//! migrations embedded and applied at start. One module per table group.

pub mod accounts;

pub mod activity;

pub mod flow;

pub mod hooks;

pub mod issues;

pub mod notify;

pub mod projects;

pub mod proposals;

pub mod releases;

pub mod repos;

pub mod runners;

use sqlx::sqlite::{SqliteConnectOptions, SqliteJournalMode, SqlitePoolOptions, SqliteSynchronous};

use sqlx::SqlitePool;

use std::path::Path;

pub type Pool = SqlitePool;

/// Open (creating if needed) and migrate the database at `path`.

/// How long a statement waits for SQLite's write lock. Most writers are

/// background clients (runners appending logs, webhook deliveries), and a

/// commit can stall for seconds while a build saturates the host's disk, so

/// waiting beats failing.

const BUSY_TIMEOUT: std::time::Duration = std::time::Duration::from_secs(30);

pub async fn open(path: &Path) -> anyhow::Result<Pool> {

    let options = SqliteConnectOptions::new()

        .filename(path)

        .create_if_missing(true)

        .journal_mode(SqliteJournalMode::Wal)

        .synchronous(SqliteSynchronous::Normal)

        .foreign_keys(true)

        .busy_timeout(BUSY_TIMEOUT);

    migrate(options.clone()).await?;

    let pool = SqlitePoolOptions::new().max_connections(8).connect_with(options).await?;

    Ok(pool)

}

/// Apply the migrations on a connection of their own with foreign keys

/// off, as SQLite's way to change a table (rebuild it, drop the old one,

/// rename) needs: with them on, dropping a table its children reference

/// deletes the children too. Every reference must hold afterwards.

async fn migrate(options: SqliteConnectOptions) -> anyhow::Result<()> {

    use sqlx::Connection;

    let mut conn = sqlx::SqliteConnection::connect_with(&options.foreign_keys(false)).await?;

    sqlx::migrate!("./migrations").run(&mut conn).await?;

    let broken: Vec<(String,)> = sqlx::query_as("SELECT \"table\" FROM pragma_foreign_key_check").fetch_all(&mut conn).await?;

    if let Some((table,)) = broken.first() {

        anyhow::bail!("after the migrations, {} rows break a foreign key (the first in {table})", broken.len());

    }

    conn.close().await?;

    Ok(())

}

/// Milliseconds since the epoch, the hub's timestamp unit (as `oxy`'s).

pub fn now_ms() -> i64 {

    std::time::SystemTime::now().duration_since(std::time::UNIX_EPOCH).map(|d| d.as_millis() as i64).unwrap_or(0)

}

/// A write transaction that takes SQLite's write lock at `BEGIN`

/// (`BEGIN IMMEDIATE`). A deferred transaction that reads and then writes

/// fails at once with "database is locked" when another writer holds the

/// lock, whatever the busy timeout; an immediate one waits its turn.

pub async fn begin_write(pool: &Pool) -> sqlx::Result<sqlx::Transaction<'static, sqlx::Sqlite>> {

    pool.begin_with("BEGIN IMMEDIATE").await

}