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

#[derive(sqlx::FromRow)]
pub struct FootnoteWithAuthor {
    pub id: Uuid,
    pub post_id: Uuid,
    pub author_id: Uuid,
    pub author_name: String,
    pub author_username: String,
    pub body_html: String,
    pub created_at: DateTime<Utc>,
}

/// Batch-fetch footnotes for a set of post IDs, joined with author info.
#[tracing::instrument(skip_all)]
pub async fn list_footnotes_for_posts(
    pool: &PgPool,
    post_ids: &[Uuid],
) -> Result<Vec<FootnoteWithAuthor>, sqlx::Error> {
    sqlx::query_as!(
        FootnoteWithAuthor,
        r#"SELECT f.id, f.post_id, f.author_id,
                COALESCE(u.display_name, u.username) AS "author_name!",
                u.username AS author_username,
                f.body_html,
                f.created_at AS "created_at: chrono::DateTime<chrono::Utc>"
         FROM post_footnotes f
         JOIN users u ON u.mnw_account_id = f.author_id
         WHERE f.post_id = ANY($1)
         ORDER BY f.created_at"#,
        post_ids,
    )
    .fetch_all(pool)
    .await
}

/// Count footnotes on a specific post.
#[tracing::instrument(skip_all)]
pub async fn count_footnotes_for_post(pool: &PgPool, post_id: Uuid) -> Result<i64, sqlx::Error> {
    sqlx::query_scalar!(
        r#"SELECT COUNT(*) AS "count!" FROM post_footnotes WHERE post_id = $1"#,
        post_id,
    )
    .fetch_one(pool)
    .await
}