//! Periodic integrity checks: sales count drift, stale subscriptions, bounce spikes,
//! storage recalculation, and post-grace enforcement.

use crate::db;
use crate::AppState;

/// Weekly storage drift correction — batch recalculates storage_used_bytes for all creators.
pub(super) async fn recalculate_all_storage_used(state: &AppState) {
    match db::creator_tiers::recalculate_all_storage_batch(&state.db).await {
        Ok(corrected) => {
            if corrected > 0 {
                tracing::info!(corrected = corrected, "weekly storage drift correction complete");
            }
        }
        Err(e) => {
            tracing::error!(error = ?e, "weekly storage drift correction failed");
        }
    }
}

/// Enforce post-grace item hiding for creators whose cancellation grace period has expired.
pub(super) async fn enforce_post_grace_hiding(state: &AppState) {
    match db::creator_tiers::get_expired_grace_creators(&state.db).await {
        Ok(user_ids) => {
            for user_id in user_ids {
                match db::items::hide_all_items_for_user(&state.db, user_id).await {
                    Ok(count) => {
                        if count > 0 {
                            tracing::info!(
                                user_id = %user_id,
                                items_hidden = count,
                                "post-grace enforcement: items hidden"
                            );
                        }
                    }
                    Err(e) => {
                        tracing::error!(error = ?e, user_id = %user_id, "failed to hide items for post-grace enforcement");
                        continue;
                    }
                }
                if let Err(e) = db::creator_tiers::mark_grace_enforced(&state.db, user_id).await {
                    tracing::error!(error = ?e, user_id = %user_id, "failed to mark grace enforced");
                }
            }
        }
        Err(e) => {
            tracing::error!(error = ?e, "failed to query expired grace creators");
        }
    }
}

/// Detect items where denormalized sales_count has drifted from actual transaction count.
pub(super) async fn check_sales_count_drift(state: &AppState) {
    // Pre-filter to items that either have a non-zero recorded count OR have
    // at least one completed transaction. The previous query GROUPed every
    // item in the platform's history; on a mature DB this multi-minute scan
    // pins a pool connection. `EXISTS ... LIMIT 1` short-circuits per item.
    let rows = match sqlx::query_as::<_, (db::ItemId, i32, i64)>(
        r#"
        SELECT i.id, i.sales_count, COUNT(t.id)
        FROM items i
        LEFT JOIN transactions t ON t.item_id = i.id AND t.status = 'completed'
        WHERE i.sales_count > 0
           OR EXISTS (SELECT 1 FROM transactions WHERE item_id = i.id AND status = 'completed' LIMIT 1)
        GROUP BY i.id
        HAVING i.sales_count != COUNT(t.id)
        LIMIT 50
        "#,
    )
    .fetch_all(&state.db)
    .await
    {
        Ok(r) if r.is_empty() => return,
        Ok(r) => r,
        Err(e) => {
            tracing::error!(error = ?e, "sales count drift check failed");
            return;
        }
    };

    tracing::warn!(count = rows.len(), "sales count drift detected");

    if let Some(ref wam) = state.wam {
        let items: Vec<String> = rows
            .iter()
            .map(|(id, cached, actual)| format!("  {id}: cached={cached}, actual={actual}"))
            .collect();
        let body = format!("Items with drifted sales_count:\n{}", items.join("\n"));
        wam.create_ticket(
            &format!("Sales count drift: {} items", rows.len()),
            Some(&body),
            "medium",
            "sales-count-drift",
            None,
        )
        .await;
    }
}

/// Find subscriptions stuck in past_due for >7 days (possible missed webhook).
pub(super) async fn check_stale_subscriptions(state: &AppState) {
    let count: i64 = match sqlx::query_scalar(
        r#"
        SELECT COUNT(*) FROM (
            SELECT 1 FROM creator_subscriptions WHERE status = 'past_due' AND current_period_end < NOW() - INTERVAL '7 days'
            UNION ALL
            SELECT 1 FROM subscriptions WHERE status = 'past_due' AND current_period_end < NOW() - INTERVAL '7 days'
        ) stale
        "#,
    )
    .fetch_one(&state.db)
    .await
    {
        Ok(c) => c,
        Err(e) => {
            tracing::error!(error = ?e, "stale subscription check failed");
            return;
        }
    };

    if count > 0 {
        tracing::warn!(count, "stale past_due subscriptions detected");
        if let Some(ref wam) = state.wam {
            wam.create_ticket(
                &format!("{count} subscriptions past_due >7 days"),
                Some("Subscriptions stuck in past_due for over 7 days. A Stripe webhook may have been missed. Check the Stripe dashboard."),
                "medium",
                "subscription-stale-past-due",
                None,
            ).await;
        }
    }
}

/// Detect email bounce/complaint spikes (>10 suppressions in 24h).
pub(super) async fn check_email_bounce_spike(state: &AppState) {
    let count: i64 = match sqlx::query_scalar(
        "SELECT COUNT(*) FROM email_suppressions WHERE created_at > NOW() - INTERVAL '24 hours'",
    )
    .fetch_one(&state.db)
    .await
    {
        Ok(c) => c,
        Err(e) => {
            tracing::error!(error = ?e, "email bounce spike check failed");
            return;
        }
    };

    if count > 10 {
        tracing::warn!(count, "email bounce/complaint spike");
        if let Some(ref wam) = state.wam {
            wam.create_ticket(
                &format!("Email bounce spike: {count} suppressions in 24h"),
                Some("Elevated bounce/complaint rate may indicate a deliverability problem. Check Postmark dashboard."),
                "high",
                "email-bounce-spike",
                None,
            ).await;
        }
    }
}