//! Health check storage and the queries built on it: history, latest snapshot,
//! uptime percentage, and the response-time series used for drift detection.

use super::{HealthDetails, HealthSnapshot, HealthStatus, Result, SqlitePool};
use tracing::instrument;

#[instrument(skip_all)]
pub async fn insert_health_check(pool: &SqlitePool, snapshot: &HealthSnapshot) -> Result<i64> {
    let status = snapshot.status.to_string();
    let details_json = snapshot
        .details
        .as_ref()
        .map(|d| serde_json::to_string(d).unwrap_or_default());

    let result = sqlx::query(
        "INSERT INTO health_checks (target, status, checked_at, response_time_ms, details_json, error)
         VALUES (?, ?, ?, ?, ?, ?)",
    )
    .bind(&snapshot.target)
    .bind(&status)
    .bind(&snapshot.checked_at)
    .bind(snapshot.response_time_ms)
    .bind(&details_json)
    .bind(&snapshot.error)
    .execute(pool)
    .await?;

    Ok(result.last_insert_rowid())
}

#[instrument(skip_all)]
pub async fn get_health_history(
    pool: &SqlitePool,
    target: Option<&str>,
    limit: i64,
) -> Result<Vec<HealthSnapshot>> {
    let rows = match target {
        Some(t) => {
            sqlx::query_as::<_, HealthCheckRow>(
                "SELECT id, target, status, checked_at, response_time_ms, details_json, error
                 FROM health_checks WHERE target = ? ORDER BY id DESC LIMIT ?",
            )
            .bind(t)
            .bind(limit)
            .fetch_all(pool)
            .await?
        }
        None => {
            sqlx::query_as::<_, HealthCheckRow>(
                "SELECT id, target, status, checked_at, response_time_ms, details_json, error
                 FROM health_checks ORDER BY id DESC LIMIT ?",
            )
            .bind(limit)
            .fetch_all(pool)
            .await?
        }
    };

    Ok(rows
        .into_iter()
        .map(HealthCheckRow::into_snapshot)
        .collect())
}

#[instrument(skip_all)]
pub async fn get_latest_health(pool: &SqlitePool, target: &str) -> Result<Option<HealthSnapshot>> {
    let row = sqlx::query_as::<_, HealthCheckRow>(
        "SELECT id, target, status, checked_at, response_time_ms, details_json, error
         FROM health_checks WHERE target = ? ORDER BY id DESC LIMIT 1",
    )
    .bind(target)
    .fetch_optional(pool)
    .await?;

    Ok(row.map(HealthCheckRow::into_snapshot))
}

/// Calculate uptime percentage for a target over the given number of hours.
/// Returns the percentage of health checks with "operational" status.
#[instrument(skip_all)]
pub async fn get_uptime_percent(
    pool: &SqlitePool,
    target: &str,
    hours: i64,
) -> Result<Option<f64>> {
    let cutoff = chrono::Utc::now() - chrono::Duration::hours(hours);
    let cutoff_str = cutoff.to_rfc3339();

    let row = sqlx::query_as::<_, (i64, i64)>(
        "SELECT
            COUNT(*) as total,
            SUM(CASE WHEN status = 'operational' THEN 1 ELSE 0 END) as operational
         FROM health_checks
         WHERE target = ? AND checked_at >= ?",
    )
    .bind(target)
    .bind(&cutoff_str)
    .fetch_one(pool)
    .await?;

    if row.0 == 0 {
        Ok(None)
    } else {
        Ok(Some(row.1 as f64 / row.0 as f64 * 100.0))
    }
}

/// Fetch all response times for a target since a given timestamp, ordered ASC.
#[instrument(skip_all)]
pub async fn get_response_times(
    pool: &SqlitePool,
    target: &str,
    since_rfc3339: &str,
) -> Result<Vec<(String, i64)>> {
    let rows = sqlx::query_as::<_, (String, i64)>(
        "SELECT checked_at, response_time_ms FROM health_checks
         WHERE target = ? AND checked_at >= ?
         ORDER BY checked_at ASC",
    )
    .bind(target)
    .bind(since_rfc3339)
    .fetch_all(pool)
    .await?;
    Ok(rows)
}

/// Fetch the last N response times for **operational** checks only (most recent first).
#[instrument(skip_all)]
pub async fn get_recent_response_times(
    pool: &SqlitePool,
    target: &str,
    count: i64,
) -> Result<Vec<i64>> {
    let rows = sqlx::query_as::<_, (i64,)>(
        "SELECT response_time_ms FROM health_checks
         WHERE target = ? AND status = 'operational'
         ORDER BY id DESC LIMIT ?",
    )
    .bind(target)
    .bind(count)
    .fetch_all(pool)
    .await?;
    Ok(rows.into_iter().map(|r| r.0).collect())
}

#[derive(sqlx::FromRow)]
struct HealthCheckRow {
    id: i64,
    target: String,
    status: String,
    checked_at: String,
    response_time_ms: i64,
    details_json: Option<String>,
    error: Option<String>,
}

impl HealthCheckRow {
    fn into_snapshot(self) -> HealthSnapshot {
        let status = self
            .status
            .parse::<HealthStatus>()
            .unwrap_or(HealthStatus::Error);
        let details = self
            .details_json
            .as_deref()
            .and_then(|s| serde_json::from_str::<HealthDetails>(s).ok());

        HealthSnapshot {
            id: Some(self.id),
            target: self.target,
            status,
            checked_at: self.checked_at,
            response_time_ms: self.response_time_ms,
            details,
            error: self.error,
        }
    }
}