//! Ask Ash AI credits background jobs (Phase 2 of
//! docs/ASK_ASH_BILLING_ARCHITECTURE.md in nxtgauge-ai-assistant).
//!
//! Phase 1 shipped the reserve/capture/release wallet primitives
//! (crates/db/src/models/ai_credits.rs) with an explicit note that
//! abandoned reservation holds have no reaper yet -- this is that reaper.

use sqlx::PgPool;

/// Release any `held` reservation past its `expires_at` back to the
/// owning wallet's available balance. A hold is only left in this state
/// by a crash or bug between `try_reserve_credits` and the matching
/// `try_capture_reservation`/`try_release_reservation` call (normal
/// success/failure paths always resolve the hold immediately via
/// `apps/users/src/ai_credits.rs::charge_ai_feature`) -- this job is the
/// backstop for that abnormal case, not the primary release path.
pub async fn sweep_expired_reservation_holds(
    pool: &PgPool,
) -> Result<u64, Box<dyn std::error::Error + Send + Sync>> {
    let expired: Vec<uuid::Uuid> = sqlx::query_scalar(
        "SELECT id FROM ai_reservation_holds WHERE status = 'held' AND expires_at < NOW()",
    )
    .fetch_all(pool)
    .await?;

    let mut released = 0u64;
    for hold_id in expired {
        match db::models::ai_credits::AiCreditsRepository::try_release_reservation(pool, hold_id).await {
            Ok(true) => released += 1,
            Ok(false) => {} // already resolved by the normal path between our SELECT and this call
            Err(e) => tracing::error!("Failed to release expired AI credit hold {}: {}", hold_id, e),
        }
    }

    if released > 0 {
        tracing::info!("Released {} expired AI credit reservation hold(s).", released);
    }
    Ok(released)
}

/// Roll `daily_actions_used`/`daily_credits_used` back to zero for any
/// wallet whose `daily_usage_date` has fallen behind today. Per-wallet
/// counters also self-heal inline on next use
/// (`AiCreditsRepository::try_reserve_credits` resets a stale wallet the
/// moment it's touched), so this batch job exists only so admin-facing
/// "today's usage" figures are accurate even for wallets nobody has
/// touched yet today -- it is not required for charge-path correctness.
pub async fn reset_stale_daily_ai_usage(
    pool: &PgPool,
) -> Result<u64, Box<dyn std::error::Error + Send + Sync>> {
    let result = sqlx::query(
        r#"
        UPDATE user_ai_subscriptions
        SET daily_actions_used = 0, daily_credits_used = 0, daily_usage_date = CURRENT_DATE
        WHERE daily_usage_date < CURRENT_DATE
        "#,
    )
    .execute(pool)
    .await?;

    let rows = result.rows_affected();
    if rows > 0 {
        tracing::info!("Reset stale daily AI usage counters for {} wallet(s).", rows);
    }
    Ok(rows)
}

/// Apply scheduled plan downgrades that have reached their effective date
/// (Task 7 - Subscription Lifecycle)
pub async fn apply_scheduled_downgrades(
    pool: &PgPool,
) -> Result<u64, Box<dyn std::error::Error + Send + Sync>> {
    let now = chrono::Utc::now();
    
    // Find all subscriptions with scheduled downgrades that should be applied
    let to_downgrade: Vec<(uuid::Uuid, uuid::Uuid, uuid::Uuid)> = sqlx::query_as(
        r#"
        SELECT w.id as wallet_id, w.user_id, w.downgrade_scheduled_to as new_plan_id
        FROM user_ai_subscriptions w
        WHERE w.downgrade_scheduled_to IS NOT NULL 
          AND w.current_period_end <= $1
        "#
    )
    .bind(now)
    .fetch_all(pool)
    .await?;
    
    let mut applied_count = 0u64;
    
    for (wallet_id, user_id, new_plan_id) in to_downgrade {
        let mut tx = pool.begin().await?;
        
        // Get new plan credits
        let new_monthly_credits: i32 = sqlx::query_scalar(
            "SELECT monthly_credits FROM ai_plans WHERE id = $1"
        )
        .bind(new_plan_id)
        .fetch_one(&mut *tx)
        .await?;
        
        // Update subscription
        sqlx::query(
            r#"
            UPDATE user_ai_subscriptions 
            SET plan_id = downgrade_scheduled_to,
                monthly_credits_total = $1,
                monthly_credits_used = 0,
                downgrade_scheduled_to = NULL,
                current_period_start = NOW(),
                current_period_end = NOW() + INTERVAL '30 days',
                updated_at = NOW()
            WHERE id = $2
            "#
        )
        .bind(new_monthly_credits)
        .bind(wallet_id)
        .execute(&mut *tx)
        .await?;
        
        // Update history record
        sqlx::query(
            r#"
            UPDATE ai_subscription_history 
            SET status = 'completed', effective_at = NOW()
            WHERE user_id = $1 AND status = 'scheduled' AND change_type = 'downgrade'
            "#
        )
        .bind(user_id)
        .execute(&mut *tx)
        .await?;
        
        tx.commit().await?;
        applied_count += 1;
    }
    
    if applied_count > 0 {
        tracing::info!("Applied {} scheduled plan downgrades.", applied_count);
    }
    Ok(applied_count)
}

/// Expire trials that have reached their end date
/// (Task 7 - Subscription Lifecycle)
pub async fn expire_trials(
    pool: &PgPool,
) -> Result<u64, Box<dyn std::error::Error + Send + Sync>> {
    let now = chrono::Utc::now();
    
    // Get free plan
    let free_plan: Option<(uuid::Uuid, i32)> = sqlx::query_as(
        "SELECT id, monthly_credits FROM ai_plans WHERE code = 'free' AND is_active = TRUE"
    )
    .fetch_optional(pool)
    .await?;
    
    let Some((free_plan_id, free_monthly)) = free_plan else {
        return Ok(0);
    };
    
    // Find expired trials
    let expired: Vec<(uuid::Uuid, uuid::Uuid)> = sqlx::query_as(
        r#"
        SELECT user_id, plan_id
        FROM user_ai_subscriptions
        WHERE is_trial = TRUE AND trial_ends_at <= $1
        "#
    )
    .bind(now)
    .fetch_all(pool)
    .await?;
    
    let mut expired_count = 0u64;
    
    for (user_id, old_plan_id) in expired {
        sqlx::query(
            r#"
            UPDATE user_ai_subscriptions 
            SET plan_id = $1,
                monthly_credits_total = $2,
                is_trial = FALSE,
                trial_days = NULL,
                trial_ends_at = NULL,
                current_period_end = NOW() + INTERVAL '30 days',
                updated_at = NOW()
            WHERE user_id = $3
            "#
        )
        .bind(free_plan_id)
        .bind(free_monthly)
        .bind(user_id)
        .execute(pool)
        .await?;
        
        // Record trial expiration
        sqlx::query(
            r#"
            INSERT INTO ai_subscription_history 
                (user_id, from_plan_id, to_plan_id, change_type, effective_at, status)
            VALUES ($1, $2, $3, 'trial_expired', NOW(), 'completed')
            "#
        )
        .bind(user_id)
        .bind(old_plan_id)
        .bind(free_plan_id)
        .execute(pool)
        .await?;
        
        expired_count += 1;
    }
    
    if expired_count > 0 {
        tracing::info!("Expired {} trials.", expired_count);
    }
    Ok(expired_count)
}

/// Expire purchased credits that have passed their expiration date
/// (Task 8 - Credit Expiration Enforcement)
pub async fn expire_purchased_credits(
    pool: &PgPool,
) -> Result<u64, Box<dyn std::error::Error + Send + Sync>> {
    let expired: Vec<(uuid::Uuid, i32, i32)> = sqlx::query_as(
        r#"
        SELECT id, user_id, 
               (purchased_credits_total - purchased_credits_used) as unused_credits
        FROM user_ai_subscriptions
        WHERE purchased_credits_expire_at IS NOT NULL
          AND purchased_credits_expire_at < NOW()
          AND purchased_credits_total > purchased_credits_used
        "#
    )
    .fetch_all(pool)
    .await?;

    let mut expired_count = 0u64;
    
    for (wallet_id, user_id, unused_credits) in expired {
        if unused_credits <= 0 {
            continue;
        }
        
        let mut tx = pool.begin().await?;
        
        // Mark credits as used (expired)
        sqlx::query(
            "UPDATE user_ai_subscriptions SET purchased_credits_used = purchased_credits_total, updated_at = NOW() WHERE id = $1"
        )
        .bind(wallet_id)
        .execute(&mut *tx)
        .await?;
        
        // Create ledger entry for expiration
        sqlx::query(
            r#"
            INSERT INTO ai_credit_ledger
                (wallet_id, entry_type, credits, balance_after, reference_type, actor_type, metadata)
            SELECT 
                $1, 
                'expiration', 
                -$2,
                (bonus_credits_total - bonus_credits_used + monthly_credits_total - monthly_credits_used + 0 - reserved_credits - locked_credits),
                'expiration',
                'system',
                jsonb_build_object('reason', 'purchased_credits_expired', 'expired_credits', $2)
            FROM user_ai_subscriptions WHERE id = $1
            "#
        )
        .bind(wallet_id)
        .bind(unused_credits)
        .execute(&mut *tx)
        .await?;
        
        tx.commit().await?;
        expired_count += 1;
        
        tracing::info!(
            "Expired {} purchased credits for user {}",
            unused_credits, user_id
        );
    }
    
    if expired_count > 0 {
        tracing::info!("Expired purchased credits for {} wallet(s).", expired_count);
    }
    
    Ok(expired_count)
}
