//! Alert history (for cooldown checks) and the pending-alert retry queue that
//! backs delivery to Postmark, WAM, and MNW.

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

#[derive(Debug, sqlx::FromRow)]
pub struct AlertRow {
    pub id: i64,
    pub target: String,
    pub alert_type: String,
    pub from_status: Option<String>,
    pub to_status: Option<String>,
    pub sent_at: String,
    pub error: Option<String>,
}

#[instrument(skip_all)]
pub async fn insert_alert(
    pool: &SqlitePool,
    target: &str,
    alert_type: &str,
    from_status: Option<&str>,
    to_status: Option<&str>,
    error: Option<&str>,
) -> Result<i64> {
    let now = chrono::Utc::now().to_rfc3339();
    let result = sqlx::query(
        "INSERT INTO alerts (target, alert_type, from_status, to_status, sent_at, error)
         VALUES (?, ?, ?, ?, ?, ?)",
    )
    .bind(target)
    .bind(alert_type)
    .bind(from_status)
    .bind(to_status)
    .bind(&now)
    .bind(error)
    .execute(pool)
    .await?;
    Ok(result.last_insert_rowid())
}

#[instrument(skip_all)]
pub async fn get_latest_alert_for_target(
    pool: &SqlitePool,
    target: &str,
) -> Result<Option<AlertRow>> {
    Ok(sqlx::query_as::<_, AlertRow>(
        "SELECT id, target, alert_type, from_status, to_status, sent_at, error
         FROM alerts WHERE target = ? AND alert_type NOT LIKE '%recovery%'
         ORDER BY id DESC LIMIT 1",
    )
    .bind(target)
    .fetch_optional(pool)
    .await?)
}

/// The most recent alert row for a target of a specific type-set. Unlike
/// [`get_latest_alert_for_target`] (which excludes recoveries), this can target
/// recovery rows so recoveries get their own cooldown, and so a recorded recovery
/// can clear a prior failure's cooldown (fail→recover→fail). `like` is a SQL
/// LIKE pattern matched against `alert_type` (e.g. `'%recovery%'`).
#[instrument(skip_all)]
pub async fn get_latest_alert_matching(
    pool: &SqlitePool,
    target: &str,
    like: &str,
) -> Result<Option<AlertRow>> {
    Ok(sqlx::query_as::<_, AlertRow>(
        "SELECT id, target, alert_type, from_status, to_status, sent_at, error
         FROM alerts WHERE target = ? AND alert_type LIKE ?
         ORDER BY id DESC LIMIT 1",
    )
    .bind(target)
    .bind(like)
    .fetch_optional(pool)
    .await?)
}

/// A delivery that failed and must be retried, so a transient WAM/Postmark error
/// at a status transition can't silence the whole outage (the transition fires
/// once; this queue is the durable state that outlives it).
#[derive(Debug, Clone, sqlx::FromRow)]
pub struct PendingAlertRow {
    pub id: i64,
    pub alert_key: String,
    pub category: String,
    pub channel: String,
    pub subject: String,
    pub body: String,
    pub priority: Option<String>,
    pub source: Option<String>,
    pub source_ref: Option<String>,
    pub from_status: Option<String>,
    pub to_status: Option<String>,
    pub error: Option<String>,
    pub attempts: i64,
}

/// Fields needed to enqueue an undelivered alert for retry.
#[derive(Debug, Clone)]
pub struct NewPendingAlert<'a> {
    pub alert_key: &'a str,
    pub category: &'a str,
    pub channel: &'a str,
    pub subject: &'a str,
    pub body: &'a str,
    pub priority: Option<&'a str>,
    pub source: Option<&'a str>,
    pub source_ref: Option<&'a str>,
    pub from_status: Option<&'a str>,
    pub to_status: Option<&'a str>,
    pub error: Option<&'a str>,
}

#[instrument(skip_all)]
pub async fn enqueue_pending_alert(pool: &SqlitePool, a: &NewPendingAlert<'_>) -> Result<()> {
    let now = chrono::Utc::now().to_rfc3339();
    sqlx::query(
        "INSERT INTO pending_alerts
            (alert_key, category, channel, subject, body, priority, source, source_ref,
             from_status, to_status, error, attempts, created_at, next_retry_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?, ?)",
    )
    .bind(a.alert_key)
    .bind(a.category)
    .bind(a.channel)
    .bind(a.subject)
    .bind(a.body)
    .bind(a.priority)
    .bind(a.source)
    .bind(a.source_ref)
    .bind(a.from_status)
    .bind(a.to_status)
    .bind(a.error)
    .bind(&now)
    .bind(&now) // due immediately for the first retry
    .execute(pool)
    .await?;
    Ok(())
}

/// Pending alerts whose `next_retry_at` is due (<= now), oldest first.
#[instrument(skip_all)]
pub async fn due_pending_alerts(pool: &SqlitePool, limit: i64) -> Result<Vec<PendingAlertRow>> {
    let now = chrono::Utc::now().to_rfc3339();
    Ok(sqlx::query_as::<_, PendingAlertRow>(
        "SELECT id, alert_key, category, channel, subject, body, priority, source, source_ref,
                from_status, to_status, error, attempts
         FROM pending_alerts WHERE next_retry_at <= ? ORDER BY id ASC LIMIT ?",
    )
    .bind(&now)
    .bind(limit)
    .fetch_all(pool)
    .await?)
}

#[instrument(skip_all)]
pub async fn delete_pending_alert(pool: &SqlitePool, id: i64) -> Result<()> {
    sqlx::query("DELETE FROM pending_alerts WHERE id = ?")
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

/// Record a failed retry: bump the attempt count and push `next_retry_at` out.
#[instrument(skip_all)]
pub async fn bump_pending_alert(pool: &SqlitePool, id: i64, next_retry_at: &str) -> Result<()> {
    sqlx::query(
        "UPDATE pending_alerts SET attempts = attempts + 1, next_retry_at = ? WHERE id = ?",
    )
    .bind(next_retry_at)
    .bind(id)
    .execute(pool)
    .await?;
    Ok(())
}