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

/// Check if a user is platform-suspended (by admin).
#[tracing::instrument(skip_all)]
pub async fn is_user_suspended(pool: &PgPool, user_id: Uuid) -> Result<bool, sqlx::Error> {
    sqlx::query_scalar!(
        r#"SELECT EXISTS(SELECT 1 FROM users WHERE mnw_account_id = $1 AND suspended_at IS NOT NULL) AS "exists!""#,
        user_id,
    )
    .fetch_one(pool)
    .await
}

/// Check if user has an active ban in a community.
#[tracing::instrument(skip_all)]
pub async fn is_user_banned(
    pool: &PgPool,
    community_id: Uuid,
    user_id: Uuid,
) -> Result<bool, sqlx::Error> {
    sqlx::query_scalar!(
        r#"SELECT EXISTS(SELECT 1 FROM community_bans
         WHERE community_id = $1 AND user_id = $2 AND ban_type = 'ban'
         AND (expires_at IS NULL OR expires_at > now())) AS "exists!""#,
        community_id,
        user_id,
    )
    .fetch_one(pool)
    .await
}

/// Check if user has an active mute in a community.
#[tracing::instrument(skip_all)]
pub async fn is_user_muted(
    pool: &PgPool,
    community_id: Uuid,
    user_id: Uuid,
) -> Result<bool, sqlx::Error> {
    sqlx::query_scalar!(
        r#"SELECT EXISTS(SELECT 1 FROM community_bans
         WHERE community_id = $1 AND user_id = $2 AND ban_type = 'mute'
         AND (expires_at IS NULL OR expires_at > now())) AS "exists!""#,
        community_id,
        user_id,
    )
    .fetch_one(pool)
    .await
}

#[derive(sqlx::FromRow)]
pub struct CommunityBanRow {
    pub id: Uuid,
    pub user_id: Uuid,
    pub username: String,
    pub display_name: Option<String>,
    pub ban_type: BanType,
    pub reason: Option<String>,
    pub expires_at: Option<DateTime<Utc>>,
    pub created_at: DateTime<Utc>,
    pub banned_by_username: String,
}

/// List active bans and mutes in a community, newest first, bounded by `limit`.
/// The moderation page caps the read so a community with a large ban backlog
/// can't make a single page load materialize an unbounded result set.
#[tracing::instrument(skip_all)]
pub async fn list_community_bans(
    pool: &PgPool,
    community_id: Uuid,
    limit: i64,
) -> Result<Vec<CommunityBanRow>, sqlx::Error> {
    sqlx::query_as!(
        CommunityBanRow,
        r#"SELECT cb.id, cb.user_id,
                u.username, u.display_name,
                cb.ban_type AS "ban_type: BanType", cb.reason,
                cb.expires_at AS "expires_at: chrono::DateTime<chrono::Utc>",
                cb.created_at AS "created_at: chrono::DateTime<chrono::Utc>",
                actor.username AS banned_by_username
         FROM community_bans cb
         JOIN users u ON u.mnw_account_id = cb.user_id
         JOIN users actor ON actor.mnw_account_id = cb.banned_by
         WHERE cb.community_id = $1
         AND (cb.expires_at IS NULL OR cb.expires_at > now())
         ORDER BY cb.created_at DESC
         LIMIT $2"#,
        community_id,
        limit,
    )
    .fetch_all(pool)
    .await
}

#[derive(sqlx::FromRow)]
pub struct ModLogEntry {
    pub id: Uuid,
    pub actor_username: String,
    pub action: ModAction,
    pub target_username: Option<String>,
    pub reason: Option<String>,
    pub created_at: DateTime<Utc>,
}

/// List mod log entries for a community, paginated, newest first.
#[tracing::instrument(skip_all)]
pub async fn list_mod_log(
    pool: &PgPool,
    community_id: Uuid,
    limit: i64,
    offset: i64,
) -> Result<Vec<ModLogEntry>, sqlx::Error> {
    sqlx::query_as!(
        ModLogEntry,
        // LEFT JOIN + a "System" label for a NULL actor_id: migration 032 made
        // actor_id nullable precisely so auto-hide (flag-threshold) removals could
        // be recorded with no human actor. An INNER JOIN silently dropped every
        // such row, the auto-moderation audit trail the migration exists to keep,
        // while count_mod_log counted them, so pagination overcounted. COALESCE
        // is non-null, hence the `!` (see fuzz-2026-07-06 top DB fix).
        r#"SELECT ml.id,
                COALESCE(actor.username, 'System') AS "actor_username!",
                ml.action AS "action: ModAction",
                target.username AS "target_username?",
                ml.reason,
                ml.created_at AS "created_at: chrono::DateTime<chrono::Utc>"
         FROM mod_log ml
         LEFT JOIN users actor ON actor.mnw_account_id = ml.actor_id
         LEFT JOIN users target ON target.mnw_account_id = ml.target_user
         WHERE ml.community_id = $1
         ORDER BY ml.created_at DESC
         LIMIT $2 OFFSET $3"#,
        community_id,
        limit,
        offset,
    )
    .fetch_all(pool)
    .await
}

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

#[derive(sqlx::FromRow)]
pub struct PendingFlagRow {
    pub flag_id: Uuid,
    pub post_id: Uuid,
    pub thread_id: Uuid,
    pub thread_title: String,
    pub category_slug: String,
    pub flagger_username: String,
    pub reason: String,
    pub detail: Option<String>,
    pub created_at: DateTime<Utc>,
}

/// List unresolved flags in a community, newest first, bounded by `limit`.
/// The read is capped so a coordinated flag-spam backlog can't make the
/// moderation page build an unbounded result set on every load; mods clear the
/// newest flags and the next batch surfaces as they're resolved.
#[tracing::instrument(skip_all)]
pub async fn list_pending_flags(
    pool: &PgPool,
    community_id: Uuid,
    limit: i64,
) -> Result<Vec<PendingFlagRow>, sqlx::Error> {
    sqlx::query_as!(
        PendingFlagRow,
        r#"SELECT f.id AS flag_id, f.post_id, p.thread_id,
                t.title AS thread_title,
                cat.slug AS category_slug,
                u.username AS flagger_username,
                f.reason, f.detail,
                f.created_at AS "created_at: chrono::DateTime<chrono::Utc>"
         FROM post_flags f
         JOIN posts p ON p.id = f.post_id
         JOIN threads t ON t.id = p.thread_id
         JOIN categories cat ON cat.id = t.category_id
         JOIN users u ON u.mnw_account_id = f.flagger_id
         WHERE cat.community_id = $1 AND f.resolved_at IS NULL
         ORDER BY f.created_at DESC
         LIMIT $2"#,
        community_id,
        limit,
    )
    .fetch_all(pool)
    .await
}

/// Check if a user has already flagged a specific post.
#[tracing::instrument(skip_all)]
pub async fn has_user_flagged_post(
    pool: &PgPool,
    post_id: Uuid,
    flagger_id: Uuid,
) -> Result<bool, sqlx::Error> {
    sqlx::query_scalar!(
        r#"SELECT EXISTS(SELECT 1 FROM post_flags WHERE post_id = $1 AND flagger_id = $2) AS "exists!""#,
        post_id,
        flagger_id,
    )
    .fetch_one(pool)
    .await
}

/// Check if a flag belongs to a given community (via post → thread → category chain).
#[tracing::instrument(skip_all)]
pub async fn flag_belongs_to_community(
    pool: &PgPool,
    flag_id: Uuid,
    community_id: Uuid,
) -> Result<bool, sqlx::Error> {
    sqlx::query_scalar!(
        r#"SELECT EXISTS(
            SELECT 1 FROM post_flags pf
            JOIN posts p ON p.id = pf.post_id
            JOIN threads t ON t.id = p.thread_id
            JOIN categories c ON c.id = t.category_id
            WHERE pf.id = $1 AND c.community_id = $2
        ) AS "exists!""#,
        flag_id,
        community_id,
    )
    .fetch_one(pool)
    .await
}

/// Post-removal target for a flag, scoped to a community: `(post_id, author_id,
/// thread_id)`. Returns `None` if the flag doesn't exist or belongs to another
/// community, the community scoping is enforced here rather than in the handler.
#[tracing::instrument(skip_all)]
pub async fn get_flag_removal_target(
    pool: &PgPool,
    flag_id: Uuid,
    community_id: Uuid,
) -> Result<Option<(Uuid, Uuid, Uuid)>, sqlx::Error> {
    let row = sqlx::query!(
        r#"SELECT pf.post_id AS "post_id!", p.author_id AS "author_id!", t.id AS "thread_id!"
           FROM post_flags pf
           JOIN posts p ON p.id = pf.post_id
           JOIN threads t ON t.id = p.thread_id
           JOIN categories c ON c.id = t.category_id
           WHERE pf.id = $1 AND c.community_id = $2"#,
        flag_id,
        community_id,
    )
    .fetch_optional(pool)
    .await?;
    Ok(row.map(|r| (r.post_id, r.author_id, r.thread_id)))
}