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

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

/// Weekly storage drift correction, batch recalculates storage_used_bytes for all creators.
#[tracing::instrument(skip_all, name = "scheduler::recalculate_all_storage_used")]
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.
#[tracing::instrument(skip_all, name = "scheduler::enforce_post_grace_hiding")]
pub(super) async fn enforce_post_grace_hiding(state: &AppState) {
    let user_ids = match db::creator_tiers::get_expired_grace_creators(&state.db).await {
        Ok(ids) => ids,
        Err(e) => {
            tracing::error!(error = ?e, "failed to query expired grace creators");
            return;
        }
    };
    if user_ids.is_empty() {
        return;
    }

    // Set-based hide + mark in two statements instead of 2N per-creator round-trips
    // inline on the lock-held tick (Perf-S4, Run 9). Both are idempotent, so if the
    // mark fails after the hide the next sweep re-hides (a no-op) and re-marks.
    let hidden = match db::items::hide_all_items_for_users(&state.db, &user_ids).await {
        Ok(count) => count,
        Err(e) => {
            tracing::error!(error = ?e, "failed to hide items for post-grace enforcement");
            return;
        }
    };
    if let Err(e) = db::creator_tiers::mark_grace_enforced_batch(&state.db, &user_ids).await {
        tracing::error!(error = ?e, "failed to mark grace enforced");
        return;
    }
    if hidden > 0 {
        tracing::info!(
            creators = user_ids.len(),
            items_hidden = hidden,
            "post-grace enforcement: items hidden"
        );
    }
}

/// Detect items where denormalized sales_count has drifted from actual transaction count.
#[tracing::instrument(skip_all, name = "scheduler::check_sales_count_drift")]
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).
#[tracing::instrument(skip_all, name = "scheduler::check_stale_subscriptions")]
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).
#[tracing::instrument(skip_all, name = "scheduler::check_email_bounce_spike")]
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;
        }
    }
}