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

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

    use uuid::Uuid;

    #[derive(sqlx::FromRow)]
    struct LeadRecord {
        lead_id: Uuid,
        title: String,
        email: String,
        name: String,
    }

    let records = sqlx::query_as::<_, LeadRecord>(
        r#"
        UPDATE leads
        SET status = 'EXPIRED'
        FROM users u
        WHERE leads.created_by_user_id = u.id
          AND leads.status = 'OPEN'
          AND leads.expires_at < $1
        RETURNING leads.id as lead_id, leads.title, u.email, CONCAT(u.first_name, ' ', u.last_name) AS name
        "#
    )
    .bind(now)
    .fetch_all(pool)
    .await?;

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

    tracing::info!("Expired {} stale leads.", records.len());

    for rec in records {
        let _ = mailer.send_requirement_expired_email(&rec.email, &rec.name, &rec.title).await;
        tracing::info!("Sent expiry email to {} for lead {}", rec.email, rec.lead_id);
    }

    Ok(())
}
