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

#[derive(sqlx::FromRow)]
pub struct CommunityRow {
    pub id: Uuid,
    pub name: String,
    pub slug: String,
    pub description: Option<String>,
    pub suspended_at: Option<DateTime<Utc>>,
    pub auto_hide_threshold: Option<i32>,
    pub state: CommunityState,
}

#[derive(sqlx::FromRow)]
pub struct CommunityListRow {
    pub name: String,
    pub slug: String,
    pub description: Option<String>,
    pub category_count: i64,
    pub thread_count: i64,
}

/// List non-suspended, non-archived communities with category and thread counts (paginated).
///
/// Archived communities are hidden from the default listing, see
/// [`list_archived_communities`] for the explicit archived view.
#[tracing::instrument(skip_all)]
pub async fn list_communities(
    pool: &PgPool,
    limit: i64,
    offset: i64,
) -> Result<Vec<CommunityListRow>, sqlx::Error> {
    sqlx::query_as!(
        CommunityListRow,
        // Per-community counts as scalar subqueries rather than a
        // categories⋈threads join + GROUP BY COUNT(DISTINCT): the join
        // multiplied rows (community × categories × threads) and forced
        // aggregating *every* community's threads before LIMIT. As scalar
        // subqueries in the target list they're evaluated only for the page's
        // output rows (after ORDER BY + LIMIT), bounding the work to one page.
        r#"SELECT co.name, co.slug, co.description,
                (SELECT COUNT(*) FROM categories c WHERE c.community_id = co.id) AS "category_count!",
                (SELECT COUNT(*) FROM threads t
                   JOIN categories c2 ON c2.id = t.category_id
                  WHERE c2.community_id = co.id) AS "thread_count!"
         FROM communities co
         WHERE co.suspended_at IS NULL
           AND co.state <> 'archived'
         ORDER BY co.name
         LIMIT $1 OFFSET $2"#,
        limit,
        offset,
    )
    .fetch_all(pool)
    .await
}

/// Count non-suspended, non-archived communities.
#[tracing::instrument(skip_all)]
pub async fn count_communities(pool: &PgPool) -> Result<i64, sqlx::Error> {
    sqlx::query_scalar!(
        r#"SELECT COUNT(*) AS "count!" FROM communities WHERE suspended_at IS NULL AND state <> 'archived'"#,
    )
    .fetch_one(pool)
    .await
}

/// List archived communities. Used by the explicit `?filter=archived` view; never
/// merged with the default listing.
#[tracing::instrument(skip_all)]
pub async fn list_archived_communities(
    pool: &PgPool,
    limit: i64,
    offset: i64,
) -> Result<Vec<CommunityListRow>, sqlx::Error> {
    sqlx::query_as!(
        CommunityListRow,
        // Scalar-subquery counts (see `list_communities`), bounded to the page.
        r#"SELECT co.name, co.slug, co.description,
                (SELECT COUNT(*) FROM categories c WHERE c.community_id = co.id) AS "category_count!",
                (SELECT COUNT(*) FROM threads t
                   JOIN categories c2 ON c2.id = t.category_id
                  WHERE c2.community_id = co.id) AS "thread_count!"
         FROM communities co
         WHERE co.suspended_at IS NULL
           AND co.state = 'archived'
         ORDER BY co.name
         LIMIT $1 OFFSET $2"#,
        limit,
        offset,
    )
    .fetch_all(pool)
    .await
}

#[tracing::instrument(skip_all)]
pub async fn count_archived_communities(pool: &PgPool) -> Result<i64, sqlx::Error> {
    sqlx::query_scalar!(
        r#"SELECT COUNT(*) AS "count!" FROM communities WHERE suspended_at IS NULL AND state = 'archived'"#,
    )
    .fetch_one(pool)
    .await
}

#[tracing::instrument(skip_all)]
pub async fn get_community_by_slug(
    pool: &PgPool,
    slug: &str,
) -> Result<Option<CommunityRow>, sqlx::Error> {
    sqlx::query_as!(
        CommunityRow,
        r#"SELECT id, name, slug, description,
                  suspended_at AS "suspended_at: chrono::DateTime<chrono::Utc>",
                  auto_hide_threshold,
                  state AS "state: CommunityState"
           FROM communities WHERE slug = $1"#,
        slug,
    )
    .fetch_optional(pool)
    .await
}

#[tracing::instrument(skip_all)]
pub async fn get_community_by_id(
    pool: &PgPool,
    id: Uuid,
) -> Result<Option<CommunityRow>, sqlx::Error> {
    sqlx::query_as!(
        CommunityRow,
        r#"SELECT id, name, slug, description,
                  suspended_at AS "suspended_at: chrono::DateTime<chrono::Utc>",
                  auto_hide_threshold,
                  state AS "state: CommunityState"
           FROM communities WHERE id = $1"#,
        id,
    )
    .fetch_optional(pool)
    .await
}

/// Count all threads in a community (across every category, including
/// soft-deleted, matches the admin dashboard's historical total).
#[tracing::instrument(skip_all)]
pub async fn count_threads_in_community(
    pool: &PgPool,
    community_id: Uuid,
) -> Result<i64, sqlx::Error> {
    sqlx::query_scalar!(
        r#"SELECT COUNT(*) AS "count!" FROM threads t
           JOIN categories c ON c.id = t.category_id
           WHERE c.community_id = $1"#,
        community_id,
    )
    .fetch_one(pool)
    .await
}

/// The stored suspension reason for a community, if any.
#[tracing::instrument(skip_all)]
pub async fn get_community_suspension_reason(
    pool: &PgPool,
    community_id: Uuid,
) -> Result<Option<String>, sqlx::Error> {
    sqlx::query_scalar!(
        "SELECT suspension_reason FROM communities WHERE id = $1",
        community_id,
    )
    .fetch_one(pool)
    .await
}