//! TLS certificate check storage.

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

#[derive(Debug, sqlx::FromRow, serde::Serialize)]
pub struct TlsCheckRow {
    pub id: i64,
    pub target: String,
    pub host: String,
    pub valid: bool,
    pub days_remaining: i64,
    pub not_before: String,
    pub not_after: String,
    pub subject: String,
    pub issuer: String,
    pub checked_at: String,
    pub error: Option<String>,
    /// Whether the chain validated against the bundled web-PKI roots. `None` on
    /// rows written before migration 14, which carry no trust readings.
    pub webpki_trusted: Option<bool>,
    /// Whether the chain validated against this host's own trust store. `None`
    /// as above. `Some(false)` here alongside `Some(true)` for the web PKI is
    /// the host CA bundle failing while the public one is fine.
    pub platform_trusted: Option<bool>,
    pub webpki_error: Option<String>,
    pub platform_error: Option<String>,
}

#[instrument(skip_all)]
pub async fn insert_tls_check(pool: &SqlitePool, status: &TlsStatus) -> Result<i64> {
    let result = sqlx::query(
        "INSERT INTO tls_checks (target, host, valid, days_remaining, not_before, not_after, subject, issuer, checked_at, error, webpki_trusted, platform_trusted, webpki_error, platform_error)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
    )
    .bind(&status.target)
    .bind(&status.host)
    .bind(status.valid)
    .bind(status.days_remaining)
    .bind(&status.not_before)
    .bind(&status.not_after)
    .bind(&status.subject)
    .bind(&status.issuer)
    .bind(&status.checked_at)
    .bind(&status.error)
    .bind(status.webpki_trusted)
    .bind(status.platform_trusted)
    .bind(&status.webpki_error)
    .bind(&status.platform_error)
    .execute(pool)
    .await?;

    Ok(result.last_insert_rowid())
}

#[instrument(skip_all)]
pub async fn get_latest_tls_check(pool: &SqlitePool, target: &str) -> Result<Option<TlsCheckRow>> {
    Ok(sqlx::query_as::<_, TlsCheckRow>(
        "SELECT id, target, host, valid, days_remaining, not_before, not_after, subject, issuer, checked_at, error, webpki_trusted, platform_trusted, webpki_error, platform_error
         FROM tls_checks WHERE target = ? ORDER BY id DESC LIMIT 1",
    )
    .bind(target)
    .fetch_optional(pool)
    .await?)
}