use sqlx::PgPool;
use email::Mailer;
use chrono::Utc;
use uuid::Uuid;

pub async fn expire_stale_lead_requests(
    pool: &PgPool,
    mailer: &Mailer,
) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
    let cutoff = Utc::now() - chrono::Duration::hours(24);

    #[derive(sqlx::FromRow)]
    struct Record {
        lead_request_id: Uuid,
        tracecoins_reserved: i32,
        user_id: Uuid,
        email: String,
        name: String,
    }

    let records = sqlx::query_as::<_, Record>(
        r#"
        SELECT
            lr.id AS lead_request_id,
            lr.user_role_profile_id,
            lr.tracecoins_reserved,
            urp.user_id,
            u.email,
            CONCAT(u.first_name, ' ', u.last_name) AS name
        FROM lead_requests lr
        INNER JOIN user_role_profiles urp ON urp.id = lr.user_role_profile_id
        INNER JOIN users u ON u.id = urp.user_id
        WHERE lr.status = 'PENDING'
          AND lr.requested_at < $1
        "#
    )
    .bind(cutoff)
    .fetch_all(pool)
    .await?;

    if records.is_empty() {
        return Ok(());
    }

    tracing::info!("Found {} stale lead requests to expire.", records.len());

    for rec in records {
        let mut tx = pool.begin().await?;

        let updated = sqlx::query(
            "UPDATE lead_requests SET status = 'EXPIRED', resolved_at = $1 WHERE id = $2 AND status = 'PENDING'"
        )
        .bind(Utc::now())
        .bind(rec.lead_request_id)
        .execute(&mut *tx)
        .await?;

        if updated.rows_affected() == 0 {
            tx.rollback().await?;
            continue;
        }

        if rec.tracecoins_reserved > 0 {
            sqlx::query(
                "UPDATE tracecoin_wallets SET current_balance = current_balance + $1, updated_at = NOW() WHERE user_id = $2"
            )
            .bind(rec.tracecoins_reserved)
            .bind(rec.user_id)
            .execute(&mut *tx)
            .await?;

            sqlx::query(
                r#"
                INSERT INTO tracecoin_ledger (wallet_id, amount, transaction_type, reference_type, reference_id, created_at)
                SELECT w.id, $1, 'RELEASE', 'Lead Request Expired', $2, $3
                FROM tracecoin_wallets w WHERE w.user_id = $4
                "#
            )
            .bind(rec.tracecoins_reserved)
            .bind(rec.lead_request_id)
            .bind(Utc::now())
            .bind(rec.user_id)
            .execute(&mut *tx)
            .await?;
        }

        tx.commit().await?;

        let _ = mailer.send_lead_expired_email(&rec.email, &rec.name, rec.tracecoins_reserved).await;
        
        tracing::info!("Expired lead request {} and refunded {} tracecoins to {}", rec.lead_request_id, rec.tracecoins_reserved, rec.email);
    }

    Ok(())
}
