use chrono::{DateTime, Utc};
use sqlx::postgres::PgPool;
use uuid::Uuid;

use crate::drivers::postgresql::pool_manager::ConnectionPoolSnapshot;

/// Persist a snapshot of pool occupancy into the logging database.
pub async fn insert_connection_snapshot(
    pool: &PgPool,
    snapshot: &ConnectionPoolSnapshot,
    host: &str,
    instance_id: Uuid,
) -> Result<(), sqlx::Error> {
    sqlx::query(
        r#"
        INSERT INTO client_connections (
            recorded_at,
            client_name,
            host,
            instance_id,
            pool_size,
            idle_connections,
            active_connections,
            max_connections
        )
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
        "#,
    )
    .bind(snapshot.recorded_at)
    .bind(&snapshot.client_name)
    .bind(host)
    .bind(instance_id)
    .bind(snapshot.pool_size as i64)
    .bind(snapshot.idle_connections as i64)
    .bind(snapshot.active_connections as i64)
    .bind(snapshot.max_connections as i64)
    .execute(pool)
    .await?;

    Ok(())
}

/// Remove connection snapshots older than the provided age (in hours) to keep the table bounded.
pub async fn prune_connection_snapshots(
    pool: &PgPool,
    max_age_hours: i64,
) -> Result<u64, sqlx::Error> {
    let cutoff: DateTime<Utc> = Utc::now() - chrono::Duration::hours(max_age_hours);
    let affected = sqlx::query("DELETE FROM client_connections WHERE recorded_at < $1")
        .bind(cutoff)
        .execute(pool)
        .await?
        .rows_affected();

    Ok(affected)
}
