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

#[tracing::instrument(skip_all)]
pub async fn get_user_role(
    pool: &PgPool,
    user_id: Uuid,
    community_id: Uuid,
) -> Result<Option<CommunityRole>, sqlx::Error> {
    sqlx::query_scalar!(
        r#"SELECT role AS "role: CommunityRole" FROM memberships WHERE user_id = $1 AND community_id = $2"#,
        user_id,
        community_id,
    )
    .fetch_optional(pool)
    .await
}

#[derive(sqlx::FromRow)]
pub struct MemberRow {
    pub username: String,
    pub display_name: Option<String>,
    pub role: CommunityRole,
    pub joined_at: DateTime<Utc>,
}

#[tracing::instrument(skip_all)]
pub async fn list_community_members(
    pool: &PgPool,
    community_id: Uuid,
    limit: i64,
    offset: i64,
) -> Result<Vec<MemberRow>, sqlx::Error> {
    sqlx::query_as!(
        MemberRow,
        r#"SELECT u.username,
                u.display_name,
                m.role AS "role: CommunityRole",
                m.joined_at AS "joined_at: chrono::DateTime<chrono::Utc>"
         FROM memberships m
         JOIN users u ON u.mnw_account_id = m.user_id
         WHERE m.community_id = $1
         ORDER BY
             CASE m.role
                 WHEN 'owner' THEN 0
                 WHEN 'moderator' THEN 1
                 WHEN 'member' THEN 2
             END,
             m.joined_at
         LIMIT $2 OFFSET $3"#,
        community_id,
        limit,
        offset,
    )
    .fetch_all(pool)
    .await
}

/// Count members in a community.
#[tracing::instrument(skip_all)]
pub async fn count_community_members(
    pool: &PgPool,
    community_id: Uuid,
) -> Result<i64, sqlx::Error> {
    sqlx::query_scalar!(
        r#"SELECT COUNT(*) AS "count!" FROM memberships WHERE community_id = $1"#,
        community_id,
    )
    .fetch_one(pool)
    .await
}