use super::{DateTime, PgPool, Utc, Uuid};

#[derive(sqlx::FromRow)]
pub struct ImageRow {
    pub id: Uuid,
    pub community_id: Uuid,
    pub s3_key: String,
    pub content_type: String,
    pub removed_at: Option<DateTime<Utc>>,
}

/// Fetch an image by ID (for serving). Carries `community_id` so the serve
/// handler can enforce the owning community's access policy.
#[tracing::instrument(skip_all)]
pub async fn get_image(pool: &PgPool, image_id: Uuid) -> Result<Option<ImageRow>, sqlx::Error> {
    sqlx::query_as!(
        ImageRow,
        r#"SELECT id, community_id, s3_key, content_type,
                  removed_at AS "removed_at: chrono::DateTime<chrono::Utc>"
           FROM images WHERE id = $1"#,
        image_id,
    )
    .fetch_optional(pool)
    .await
}

/// A removed image whose backing S3 object still needs to be purged.
#[derive(sqlx::FromRow)]
pub struct PendingPurgeImage {
    pub id: Uuid,
    pub s3_key: String,
}

/// List removed images whose S3 object has not been purged yet, oldest first.
/// Drives the background reconcile sweep; `limit` bounds one batch (S3's
/// batch-delete cap is 1000).
#[tracing::instrument(skip_all)]
pub async fn list_images_pending_s3_purge(
    pool: &PgPool,
    limit: i64,
) -> Result<Vec<PendingPurgeImage>, sqlx::Error> {
    sqlx::query_as!(
        PendingPurgeImage,
        "SELECT id, s3_key FROM images
         WHERE removed_at IS NOT NULL AND s3_purged_at IS NULL
         ORDER BY removed_at ASC
         LIMIT $1",
        limit,
    )
    .fetch_all(pool)
    .await
}

/// Count images uploaded by a user in the last N seconds (rate limiting).
#[tracing::instrument(skip_all)]
pub async fn count_recent_uploads_by_user(
    pool: &PgPool,
    user_id: Uuid,
    window_secs: i64,
) -> Result<i64, sqlx::Error> {
    sqlx::query_scalar!(
        r#"SELECT COUNT(*) AS "count!" FROM images
         WHERE uploader_id = $1
           AND created_at > now() - make_interval(secs => $2::float8)"#,
        user_id,
        window_secs as f64,
    )
    .fetch_one(pool)
    .await
}