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

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

    // Find stale jobs that are still LIVE
    use uuid::Uuid;

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

    let records = sqlx::query_as::<_, JobRecord>(
        r#"
        UPDATE jobs
        SET status = 'EXPIRED'
        FROM companies c
        JOIN users u ON u.id = c.user_id
        WHERE jobs.company_id = c.id
          AND jobs.status = 'LIVE'
          AND jobs.expires_at < $1
        RETURNING jobs.id as job_id, jobs.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 jobs.", records.len());

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

    Ok(())
}
