//! Retention: deletes records older than the configured window from every table
//! and reports per-table counts.

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

/// Prune result with counts for each table.
pub struct PruneResult {
    pub health: u64,
    pub tests: u64,
    pub test_details: u64,
    pub heartbeats: u64,
    pub alerts: u64,
    pub tls: u64,
    pub incidents: u64,
    pub routes: u64,
    pub dns: u64,
    pub whois: u64,
    pub backups: u64,
}

/// Delete records older than `days` from all tables.
/// Only closed incidents (with a non-NULL `ended_at`) are pruned.
#[instrument(skip_all)]
pub async fn prune_old_records(pool: &SqlitePool, days: i64) -> Result<PruneResult> {
    // Guard: days <= 0 would set cutoff to now (or the future), deleting
    // everything. Treat this as a no-op instead.
    if days <= 0 {
        return Ok(PruneResult {
            health: 0,
            tests: 0,
            test_details: 0,
            heartbeats: 0,
            alerts: 0,
            tls: 0,
            incidents: 0,
            routes: 0,
            dns: 0,
            whois: 0,
            backups: 0,
        });
    }

    let cutoff = chrono::Utc::now() - chrono::Duration::days(days);
    let cutoff_str = cutoff.to_rfc3339();

    let health_result = sqlx::query("DELETE FROM health_checks WHERE checked_at < ?")
        .bind(&cutoff_str)
        .execute(pool)
        .await?;

    // test_details has ON DELETE CASCADE on run_id and the pool runs with
    // foreign_keys=ON, so deleting the runs below already removes their details.
    // Count them first: the orphan sweep afterwards can only ever report rows the
    // cascade missed, which is why PruneResult.test_details always read ~0.
    let cascaded_details = sqlx::query_as::<_, (i64,)>(
        "SELECT COUNT(*) FROM test_details
         WHERE run_id IN (SELECT id FROM test_runs WHERE started_at < ?)",
    )
    .bind(&cutoff_str)
    .fetch_one(pool)
    .await?;

    let test_result = sqlx::query("DELETE FROM test_runs WHERE started_at < ?")
        .bind(&cutoff_str)
        .execute(pool)
        .await?;

    // Safety net for details orphaned some other way (a database written while
    // foreign_keys was off). Normally zero.
    let orphan_result =
        sqlx::query("DELETE FROM test_details WHERE run_id NOT IN (SELECT id FROM test_runs)")
            .execute(pool)
            .await?;

    let peer_hb_result = sqlx::query("DELETE FROM peer_heartbeats WHERE checked_at < ?")
        .bind(&cutoff_str)
        .execute(pool)
        .await?;

    let alerts_result = sqlx::query("DELETE FROM alerts WHERE sent_at < ?")
        .bind(&cutoff_str)
        .execute(pool)
        .await?;

    let tls_result = sqlx::query("DELETE FROM tls_checks WHERE checked_at < ?")
        .bind(&cutoff_str)
        .execute(pool)
        .await?;

    let incidents_result =
        sqlx::query("DELETE FROM incidents WHERE ended_at IS NOT NULL AND ended_at < ?")
            .bind(&cutoff_str)
            .execute(pool)
            .await?;

    let routes_result = sqlx::query("DELETE FROM route_checks WHERE checked_at < ?")
        .bind(&cutoff_str)
        .execute(pool)
        .await?;

    let dns_result = sqlx::query("DELETE FROM dns_checks WHERE checked_at < ?")
        .bind(&cutoff_str)
        .execute(pool)
        .await?;

    let whois_result = sqlx::query("DELETE FROM whois_checks WHERE checked_at < ?")
        .bind(&cutoff_str)
        .execute(pool)
        .await?;

    let backups_result = sqlx::query("DELETE FROM backup_checks WHERE checked_at < ?")
        .bind(&cutoff_str)
        .execute(pool)
        .await?;

    Ok(PruneResult {
        health: health_result.rows_affected(),
        tests: test_result.rows_affected(),
        test_details: cascaded_details.0 as u64 + orphan_result.rows_affected(),
        heartbeats: peer_hb_result.rows_affected(),
        alerts: alerts_result.rows_affected(),
        tls: tls_result.rows_affected(),
        incidents: incidents_result.rows_affected(),
        routes: routes_result.rows_affected(),
        dns: dns_result.rows_affected(),
        whois: whois_result.rows_affected(),
        backups: backups_result.rows_affected(),
    })
}