//! Route check storage, including pruning of paths no longer in `expected_routes`.

use super::{Result, SqlitePool};
use tracing::instrument;

#[derive(Debug, Clone, sqlx::FromRow, serde::Serialize)]
pub struct RouteCheckRow {
    pub id: i64,
    pub target: String,
    pub path: String,
    pub status_code: i64,
    pub ok: bool,
    pub response_time_ms: i64,
    pub checked_at: String,
    pub error: Option<String>,
}

#[instrument(skip_all)]
pub async fn insert_route_check(
    pool: &SqlitePool,
    result: &crate::checks::routes::RouteCheckResult,
) -> Result<i64> {
    let row = sqlx::query(
        "INSERT INTO route_checks (target, path, status_code, ok, response_time_ms, checked_at, error)
         VALUES (?, ?, ?, ?, ?, ?, ?)",
    )
    .bind(&result.target)
    .bind(&result.path)
    .bind(result.status_code as i64)
    .bind(result.ok)
    .bind(result.response_time_ms)
    .bind(&result.checked_at)
    .bind(&result.error)
    .execute(pool)
    .await?;
    Ok(row.last_insert_rowid())
}

/// Get the latest route check per path for a target.
#[instrument(skip_all)]
pub async fn get_latest_route_checks(
    pool: &SqlitePool,
    target: &str,
) -> Result<Vec<RouteCheckRow>> {
    Ok(sqlx::query_as::<_, RouteCheckRow>(
        "SELECT r.id, r.target, r.path, r.status_code, r.ok, r.response_time_ms, r.checked_at, r.error
         FROM route_checks r
         INNER JOIN (SELECT path, MAX(id) as max_id FROM route_checks WHERE target = ? GROUP BY path) latest
         ON r.id = latest.max_id
         ORDER BY r.path",
    )
    .bind(target)
    .fetch_all(pool)
    .await?)
}

/// Delete route_checks for paths no longer in the config.
#[instrument(skip_all)]
pub async fn prune_stale_routes(
    pool: &SqlitePool,
    target: &str,
    expected_routes: &[String],
) -> Result<u64> {
    if expected_routes.is_empty() {
        // No configured routes, delete all route checks for this target
        let result = sqlx::query("DELETE FROM route_checks WHERE target = ?")
            .bind(target)
            .execute(pool)
            .await?;
        return Ok(result.rows_affected());
    }

    // Build placeholders for the IN clause
    let placeholders: Vec<&str> = expected_routes.iter().map(|_| "?").collect();
    let sql = format!(
        "DELETE FROM route_checks WHERE target = ? AND path NOT IN ({})",
        placeholders.join(", ")
    );
    let mut query = sqlx::query(sqlx::AssertSqlSafe(sql)).bind(target);
    for route in expected_routes {
        query = query.bind(route);
    }
    let result = query.execute(pool).await?;
    Ok(result.rows_affected())
}