//! Health monitor database operations.

use chrono::{DateTime, Utc};
use sqlx::PgPool;

/// Insert a health check snapshot into the history table.
#[tracing::instrument(skip_all)]
pub async fn insert_health_history(
    pool: &PgPool,
    status: &str,
    db_ok: bool,
    s3_ok: bool,
    sessions_ok: bool,
    check_duration_ms: i32,
    details: Option<&serde_json::Value>,
) -> Result<(), sqlx::Error> {
    sqlx::query(
        "INSERT INTO health_history (status, db_ok, s3_ok, sessions_ok, check_duration_ms, details)
         VALUES ($1, $2, $3, $4, $5, $6)",
    )
    .bind(status)
    .bind(db_ok)
    .bind(s3_ok)
    .bind(sessions_ok)
    .bind(check_duration_ms)
    .bind(details)
    .execute(pool)
    .await?;
    Ok(())
}

/// A single health check snapshot from the database.
#[derive(Debug, sqlx::FromRow)]
#[allow(dead_code)] // Fields used by the FromRow derive and available for future display
pub struct DbHealthSnapshot {
    pub checked_at: DateTime<Utc>,
    pub status: String,
    pub db_ok: bool,
    pub s3_ok: bool,
    pub sessions_ok: bool,
    pub check_duration_ms: i32,
}

/// Fetch the most recent health check snapshots.
#[tracing::instrument(skip_all)]
pub async fn get_recent_health_history(
    pool: &PgPool,
    limit: i64,
) -> Result<Vec<DbHealthSnapshot>, sqlx::Error> {
    sqlx::query_as::<_, DbHealthSnapshot>(
        "SELECT checked_at, status, db_ok, s3_ok, sessions_ok, check_duration_ms
         FROM health_history
         ORDER BY checked_at DESC
         LIMIT $1",
    )
    .bind(limit)
    .fetch_all(pool)
    .await
}

/// Calculate uptime percentage over the last `hours` hours.
///
/// Returns `None` if there are no records in the time window.
#[tracing::instrument(skip_all)]
pub async fn get_health_uptime_percent(
    pool: &PgPool,
    hours: i32,
) -> Result<Option<f64>, sqlx::Error> {
    let row: Option<(i64, i64)> = sqlx::query_as(
        "SELECT
            COUNT(*) AS total,
            COUNT(*) FILTER (WHERE status = 'operational') AS up
         FROM health_history
         WHERE checked_at >= NOW() - make_interval(hours => $1)",
    )
    .bind(hours)
    .fetch_optional(pool)
    .await?;

    Ok(row.and_then(|(total, up)| {
        if total == 0 {
            None
        } else {
            Some((up as f64 / total as f64) * 100.0)
        }
    }))
}

/// Find the most recent non-operational snapshot (for "last incident" display).
#[tracing::instrument(skip_all)]
pub async fn get_last_incident(
    pool: &PgPool,
) -> Result<Option<DateTime<Utc>>, sqlx::Error> {
    sqlx::query_scalar(
        "SELECT checked_at FROM health_history
         WHERE status != 'operational'
         ORDER BY checked_at DESC
         LIMIT 1",
    )
    .fetch_optional(pool)
    .await
}

/// Delete health history records older than `retain_days`.
#[tracing::instrument(skip_all)]
pub async fn prune_health_history(pool: &PgPool, retain_days: i64) -> Result<u64, sqlx::Error> {
    let result = sqlx::query(
        "DELETE FROM health_history WHERE checked_at < NOW() - make_interval(days => $1::int)",
    )
    .bind(retain_days as i32)
    .execute(pool)
    .await?;
    Ok(result.rows_affected())
}