//! Database access. Connection comes from `ops_core::sqlite`; migrations are
//! per-crate (the `sqlx::migrate!` path is compile-time, relative to here).

use anyhow::Result;
use sqlx::SqlitePool;
use std::path::Path;

pub use ops_core::sqlite::connect;

pub async fn open(path: &Path) -> Result<SqlitePool> {
    let pool = connect(path).await?;
    sqlx::migrate!("./migrations").run(&pool).await?;
    Ok(pool)
}

/// Reconcile state left `running` by a previous process. The finalizer and the
/// per-target tasks die with the daemon, so any `running` build/target/step row
/// at startup is orphaned — nothing will ever stamp it terminal. Mark them all
/// `failed` so `/state` reflects reality and a re-run isn't blocked by a ghost.
/// Returns the number of build rows reconciled (for logging). Idempotent: a
/// clean DB sweeps zero rows.
pub async fn recover_orphaned_running(pool: &SqlitePool) -> Result<u64> {
    let now = chrono::Utc::now().to_rfc3339();
    let mut tx = pool.begin().await?;
    sqlx::query("UPDATE step_runs SET status = 'failed', finished_at = ? WHERE status = 'running'")
        .bind(&now)
        .execute(&mut *tx)
        .await?;
    sqlx::query(
        "UPDATE target_runs SET status = 'failed', current_step = NULL, \
         error = COALESCE(error, 'daemon restarted while running'), finished_at = ? \
         WHERE status = 'running'",
    )
    .bind(&now)
    .execute(&mut *tx)
    .await?;
    let builds = sqlx::query(
        "UPDATE builds SET status = 'failed', finished_at = ? WHERE status = 'running'",
    )
    .bind(&now)
    .execute(&mut *tx)
    .await?
    .rows_affected();
    tx.commit().await?;
    Ok(builds)
}

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

    #[tokio::test]
    async fn recovery_marks_orphaned_running_failed_and_leaves_terminal_rows() {
        let dir = tempfile::tempdir().unwrap();
        let pool = open(&dir.path().join("t.db")).await.unwrap();

        // One running build with a running target+step, and one already-ok build.
        let now = chrono::Utc::now().to_rfc3339();
        let running: i64 = sqlx::query_scalar(
            "INSERT INTO builds (app, version, status, created_at) VALUES ('demo','0.1.0','running',?) RETURNING id",
        )
        .bind(&now).fetch_one(&pool).await.unwrap();
        let tr: i64 = sqlx::query_scalar(
            "INSERT INTO target_runs (build_id, app, version, target, status, started_at) \
             VALUES (?, 'demo','0.1.0','linux/x86_64','running',?) RETURNING id",
        )
        .bind(running)
        .bind(&now)
        .fetch_one(&pool)
        .await
        .unwrap();
        sqlx::query(
            "INSERT INTO step_runs (target_run_id, step, status, log_ref, started_at) \
             VALUES (?, 'build','running','x',?)",
        )
        .bind(tr)
        .bind(&now)
        .execute(&pool)
        .await
        .unwrap();
        sqlx::query(
            "INSERT INTO builds (app, version, status, created_at) VALUES ('demo','0.0.9','ok',?)",
        )
        .bind(&now)
        .execute(&pool)
        .await
        .unwrap();

        let reconciled = recover_orphaned_running(&pool).await.unwrap();
        assert_eq!(reconciled, 1, "only the running build is reconciled");

        // The orphaned chain is now failed; the ok build is untouched.
        let running_left: i64 =
            sqlx::query_scalar("SELECT COUNT(*) FROM builds WHERE status='running'")
                .fetch_one(&pool)
                .await
                .unwrap();
        assert_eq!(running_left, 0);
        let ok_left: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM builds WHERE status='ok'")
            .fetch_one(&pool)
            .await
            .unwrap();
        assert_eq!(ok_left, 1);
        let err: Option<String> = sqlx::query_scalar("SELECT error FROM target_runs WHERE id = ?")
            .bind(tr)
            .fetch_one(&pool)
            .await
            .unwrap();
        assert_eq!(err.as_deref(), Some("daemon restarted while running"));

        // Idempotent: a second sweep finds nothing.
        assert_eq!(recover_orphaned_running(&pool).await.unwrap(), 0);
    }
}