//! Database read queries — projection structs and SQL.

use chrono::{DateTime, Utc};
use mt_core::types::{BanType, CommunityRole, CommunityState, ModAction};
use sqlx::PgPool;
use uuid::Uuid;

// ============================================================================
// Projection structs — shaped for templates, not domain models
// ============================================================================

#[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 CategoryWithCount {
    pub name: String,
    pub slug: String,
    pub description: Option<String>,
    pub thread_count: i64,
}

#[derive(sqlx::FromRow)]
pub struct CategoryRow {
    pub name: String,
    pub slug: String,
}

#[derive(sqlx::FromRow)]
pub struct ThreadWithMeta {
    pub id: Uuid,
    pub title: String,
    pub author_name: String,
    pub author_username: String,
    pub reply_count: i64,
    pub last_activity_at: DateTime<Utc>,
    pub pinned: bool,
    pub locked: bool,
}

#[derive(sqlx::FromRow)]
pub struct ThreadWithBreadcrumb {
    pub id: Uuid,
    pub title: String,
    pub locked: bool,
    pub pinned: bool,
    pub author_id: Uuid,
    pub community_id: Uuid,
    pub community_name: String,
    pub community_slug: String,
    pub category_name: String,
    pub category_slug: String,
}

#[derive(sqlx::FromRow)]
pub struct PostWithAuthor {
    pub id: Uuid,
    pub author_id: Uuid,
    pub author_name: String,
    pub author_username: String,
    pub body_html: String,
    pub created_at: DateTime<Utc>,
    pub edited_at: Option<DateTime<Utc>>,
    pub deleted_at: Option<DateTime<Utc>>,
    pub removed_at: Option<DateTime<Utc>>,
    /// Author's current Fan+ status (denormalised on users; refreshed at
    /// OAuth callback / `POST /auth/refresh`). Used for the + badge and
    /// for signature visibility — signatures only render for current
    /// Fan+ subscribers.
    pub author_is_fan_plus: bool,
    /// Author's saved signature HTML (rendered at save time). Render only
    /// when `author_is_fan_plus` is true.
    pub author_signature_html: Option<String>,
}

#[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>,
}

#[derive(sqlx::FromRow)]
pub struct PostForEdit {
    pub id: Uuid,
    pub author_id: Uuid,
    pub body_markdown: String,
    pub created_at: DateTime<Utc>,
    pub deleted_at: Option<DateTime<Utc>>,
    pub thread_id: Uuid,
    pub thread_title: String,
    pub community_name: String,
    pub community_slug: String,
    pub community_id: Uuid,
    pub category_name: String,
    pub category_slug: String,
}

/// A category row with its ID for internal API lookups.
#[derive(sqlx::FromRow)]
pub struct CategoryIdRow {
    pub id: Uuid,
    pub name: String,
    pub slug: String,
}

/// Look up a category by community_id and category slug. Returns the category ID.
#[tracing::instrument(skip_all)]
pub async fn get_category_by_community_and_slug(
    pool: &PgPool,
    community_id: Uuid,
    category_slug: &str,
) -> Result<Option<CategoryIdRow>, sqlx::Error> {
    sqlx::query_as::<_, CategoryIdRow>(
        "SELECT id, name, slug FROM categories WHERE community_id = $1 AND slug = $2",
    )
    .bind(community_id)
    .bind(category_slug)
    .fetch_optional(pool)
    .await
}

/// Look up a thread by its external reference (e.g., "mnw:item:uuid").
#[tracing::instrument(skip_all)]
pub async fn get_thread_by_external_ref(
    pool: &PgPool,
    external_ref: &str,
) -> Result<Option<(Uuid,)>, sqlx::Error> {
    sqlx::query_as::<_, (Uuid,)>(
        "SELECT id FROM threads WHERE external_ref = $1",
    )
    .bind(external_ref)
    .fetch_optional(pool)
    .await
}

/// Get thread stats: post count and last activity timestamp.
#[tracing::instrument(skip_all)]
#[allow(clippy::type_complexity)]
pub async fn get_thread_stats(
    pool: &PgPool,
    thread_id: Uuid,
) -> Result<Option<(i64, Option<DateTime<Utc>>)>, sqlx::Error> {
    sqlx::query_as::<_, (i64, Option<DateTime<Utc>>)>(
        "SELECT COUNT(p.id), MAX(p.created_at)
         FROM posts p
         WHERE p.thread_id = $1",
    )
    .bind(thread_id)
    .fetch_optional(pool)
    .await
}

// ============================================================================
// Queries
// ============================================================================

#[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>(
        "SELECT co.name, co.slug, co.description,
                COUNT(DISTINCT c.id) AS category_count,
                COUNT(DISTINCT t.id) AS thread_count
         FROM communities co
         LEFT JOIN categories c ON c.community_id = co.id
         LEFT JOIN threads t ON t.category_id = c.id
         WHERE co.suspended_at IS NULL
           AND co.state <> 'archived'
         GROUP BY co.id
         ORDER BY co.name
         LIMIT $1 OFFSET $2",
    )
    .bind(limit)
    .bind(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(
        "SELECT 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>(
        "SELECT co.name, co.slug, co.description,
                COUNT(DISTINCT c.id) AS category_count,
                COUNT(DISTINCT t.id) AS thread_count
         FROM communities co
         LEFT JOIN categories c ON c.community_id = co.id
         LEFT JOIN threads t ON t.category_id = c.id
         WHERE co.suspended_at IS NULL
           AND co.state = 'archived'
         GROUP BY co.id
         ORDER BY co.name
         LIMIT $1 OFFSET $2",
    )
    .bind(limit)
    .bind(offset)
    .fetch_all(pool)
    .await
}

#[tracing::instrument(skip_all)]
pub async fn count_archived_communities(pool: &PgPool) -> Result<i64, sqlx::Error> {
    sqlx::query_scalar(
        "SELECT 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>(
        "SELECT id, name, slug, description, suspended_at, auto_hide_threshold, state
         FROM communities WHERE slug = $1",
    )
    .bind(slug)
    .fetch_optional(pool)
    .await
}

#[tracing::instrument(skip_all)]
pub async fn list_categories_with_counts(
    pool: &PgPool,
    community_slug: &str,
) -> Result<Vec<CategoryWithCount>, sqlx::Error> {
    sqlx::query_as::<_, CategoryWithCount>(
        "SELECT c.name, c.slug, c.description,
                COUNT(t.id) AS thread_count
         FROM categories c
         JOIN communities co ON co.id = c.community_id
         LEFT JOIN threads t ON t.category_id = c.id AND t.deleted_at IS NULL
         WHERE co.slug = $1
         GROUP BY c.id, c.name, c.slug, c.description, c.sort_order
         ORDER BY c.sort_order",
    )
    .bind(community_slug)
    .fetch_all(pool)
    .await
}

#[tracing::instrument(skip_all)]
pub async fn get_category_by_slugs(
    pool: &PgPool,
    community_slug: &str,
    category_slug: &str,
) -> Result<Option<CategoryRow>, sqlx::Error> {
    sqlx::query_as::<_, CategoryRow>(
        "SELECT c.name, c.slug
         FROM categories c
         JOIN communities co ON co.id = c.community_id
         WHERE co.slug = $1 AND c.slug = $2",
    )
    .bind(community_slug)
    .bind(category_slug)
    .fetch_optional(pool)
    .await
}

#[tracing::instrument(skip_all)]
pub async fn list_threads_in_category_paginated(
    pool: &PgPool,
    community_slug: &str,
    category_slug: &str,
    limit: i64,
    offset: i64,
) -> Result<Vec<ThreadWithMeta>, sqlx::Error> {
    sqlx::query_as::<_, ThreadWithMeta>(
        "SELECT t.id, t.title,
                COALESCE(u.display_name, u.username) AS author_name,
                u.username AS author_username,
                t.reply_count::BIGINT AS reply_count,
                t.last_activity_at,
                t.pinned, t.locked
         FROM threads t
         JOIN categories c ON c.id = t.category_id
         JOIN communities co ON co.id = c.community_id
         JOIN users u ON u.mnw_account_id = t.author_id
         WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL
         ORDER BY t.pinned DESC, t.last_activity_at DESC
         LIMIT $3 OFFSET $4",
    )
    .bind(community_slug)
    .bind(category_slug)
    .bind(limit)
    .bind(offset)
    .fetch_all(pool)
    .await
}

/// List threads with sorting. `sort` must be "replies" or "activity".
/// `order` must be "asc" or "desc". Pinned threads always sort first.
#[tracing::instrument(skip_all)]
pub async fn list_threads_in_category_sorted(
    pool: &PgPool,
    community_slug: &str,
    category_slug: &str,
    sort: &str,
    order: &str,
    limit: i64,
    offset: i64,
) -> Result<Vec<ThreadWithMeta>, sqlx::Error> {
    let order_clause = match (sort, order) {
        ("replies", "asc") => "ORDER BY t.pinned DESC, reply_count ASC, t.last_activity_at DESC",
        ("replies", _) => "ORDER BY t.pinned DESC, reply_count DESC, t.last_activity_at DESC",
        (_, "asc") => "ORDER BY t.pinned DESC, t.last_activity_at ASC",
        _ => "ORDER BY t.pinned DESC, t.last_activity_at DESC",
    };

    let query = format!(
        "SELECT t.id, t.title,
                COALESCE(u.display_name, u.username) AS author_name,
                u.username AS author_username,
                t.reply_count::BIGINT AS reply_count,
                t.last_activity_at,
                t.pinned, t.locked
         FROM threads t
         JOIN categories c ON c.id = t.category_id
         JOIN communities co ON co.id = c.community_id
         JOIN users u ON u.mnw_account_id = t.author_id
         WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL
         {order_clause}
         LIMIT $3 OFFSET $4"
    );

    sqlx::query_as::<_, ThreadWithMeta>(&query)
        .bind(community_slug)
        .bind(category_slug)
        .bind(limit)
        .bind(offset)
        .fetch_all(pool)
        .await
}

#[tracing::instrument(skip_all)]
pub async fn count_threads_in_category(
    pool: &PgPool,
    community_slug: &str,
    category_slug: &str,
) -> Result<i64, sqlx::Error> {
    sqlx::query_scalar(
        "SELECT COUNT(*)
         FROM threads t
         JOIN categories c ON c.id = t.category_id
         JOIN communities co ON co.id = c.community_id
         WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL",
    )
    .bind(community_slug)
    .bind(category_slug)
    .fetch_one(pool)
    .await
}

#[tracing::instrument(skip_all)]
pub async fn get_thread_with_breadcrumb(
    pool: &PgPool,
    thread_id: Uuid,
) -> Result<Option<ThreadWithBreadcrumb>, sqlx::Error> {
    sqlx::query_as::<_, ThreadWithBreadcrumb>(
        "SELECT t.id, t.title, t.locked, t.pinned, t.author_id,
                co.id AS community_id,
                co.name AS community_name, co.slug AS community_slug,
                c.name AS category_name, c.slug AS category_slug
         FROM threads t
         JOIN categories c ON c.id = t.category_id
         JOIN communities co ON co.id = c.community_id
         WHERE t.id = $1",
    )
    .bind(thread_id)
    .fetch_optional(pool)
    .await
}

#[tracing::instrument(skip_all)]
pub async fn list_posts_in_thread(
    pool: &PgPool,
    thread_id: Uuid,
) -> Result<Vec<PostWithAuthor>, sqlx::Error> {
    sqlx::query_as::<_, PostWithAuthor>(
        "SELECT p.id, p.author_id,
                COALESCE(u.display_name, u.username) AS author_name,
                u.username AS author_username,
                p.body_html, p.created_at, p.edited_at, p.deleted_at,
                p.removed_at,
                u.is_fan_plus AS author_is_fan_plus,
                u.signature_html AS author_signature_html
         FROM posts p
         JOIN users u ON u.mnw_account_id = p.author_id
         WHERE p.thread_id = $1
         ORDER BY p.created_at",
    )
    .bind(thread_id)
    .fetch_all(pool)
    .await
}

#[tracing::instrument(skip_all)]
pub async fn list_posts_in_thread_paginated(
    pool: &PgPool,
    thread_id: Uuid,
    limit: i64,
    offset: i64,
) -> Result<Vec<PostWithAuthor>, sqlx::Error> {
    sqlx::query_as::<_, PostWithAuthor>(
        "SELECT p.id, p.author_id,
                COALESCE(u.display_name, u.username) AS author_name,
                u.username AS author_username,
                p.body_html, p.created_at, p.edited_at, p.deleted_at,
                p.removed_at,
                u.is_fan_plus AS author_is_fan_plus,
                u.signature_html AS author_signature_html
         FROM posts p
         JOIN users u ON u.mnw_account_id = p.author_id
         WHERE p.thread_id = $1
         ORDER BY p.created_at
         LIMIT $2 OFFSET $3",
    )
    .bind(thread_id)
    .bind(limit)
    .bind(offset)
    .fetch_all(pool)
    .await
}

#[tracing::instrument(skip_all)]
pub async fn count_posts_in_thread(
    pool: &PgPool,
    thread_id: Uuid,
) -> Result<i64, sqlx::Error> {
    sqlx::query_scalar(
        "SELECT COUNT(*) FROM posts WHERE thread_id = $1",
    )
    .bind(thread_id)
    .fetch_one(pool)
    .await
}

/// Count posts + footnotes by a user in the last N seconds (for per-user rate limiting).
#[tracing::instrument(skip_all)]
pub async fn count_recent_posts_by_user(
    pool: &PgPool,
    user_id: Uuid,
    seconds: i64,
) -> Result<i64, sqlx::Error> {
    sqlx::query_scalar(
        "SELECT (SELECT COUNT(*) FROM posts WHERE author_id = $1 AND created_at > NOW() - make_interval(secs => $2))
              + (SELECT COUNT(*) FROM post_footnotes WHERE author_id = $1 AND created_at > NOW() - make_interval(secs => $2))",
    )
    .bind(user_id)
    .bind(seconds as f64)
    .fetch_one(pool)
    .await
}

#[tracing::instrument(skip_all)]
pub async fn get_post_for_edit(
    pool: &PgPool,
    post_id: Uuid,
) -> Result<Option<PostForEdit>, sqlx::Error> {
    sqlx::query_as::<_, PostForEdit>(
        "SELECT p.id, p.author_id, p.body_markdown, p.created_at, p.deleted_at,
                p.thread_id, t.title AS thread_title,
                co.name AS community_name, co.slug AS community_slug,
                co.id AS community_id,
                c.name AS category_name, c.slug AS category_slug
         FROM posts p
         JOIN threads t ON t.id = p.thread_id
         JOIN categories c ON c.id = t.category_id
         JOIN communities co ON co.id = c.community_id
         WHERE p.id = $1",
    )
    .bind(post_id)
    .fetch_optional(pool)
    .await
}

#[tracing::instrument(skip_all)]
pub async fn get_user_role(
    pool: &PgPool,
    user_id: Uuid,
    community_id: Uuid,
) -> Result<Option<CommunityRole>, sqlx::Error> {
    let row: Option<(CommunityRole,)> = sqlx::query_as(
        "SELECT role FROM memberships WHERE user_id = $1 AND community_id = $2",
    )
    .bind(user_id)
    .bind(community_id)
    .fetch_optional(pool)
    .await?;
    Ok(row.map(|r| r.0))
}

#[derive(sqlx::FromRow)]
pub struct CategoryForSettings {
    pub id: Uuid,
    pub name: String,
    pub slug: String,
    pub description: Option<String>,
    pub sort_order: i32,
}

#[tracing::instrument(skip_all)]
pub async fn list_categories_for_settings(
    pool: &PgPool,
    community_id: Uuid,
) -> Result<Vec<CategoryForSettings>, sqlx::Error> {
    sqlx::query_as::<_, CategoryForSettings>(
        "SELECT id, name, slug, description, sort_order
         FROM categories
         WHERE community_id = $1
         ORDER BY sort_order",
    )
    .bind(community_id)
    .fetch_all(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>(
        "SELECT u.username,
                u.display_name,
                m.role,
                m.joined_at
         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
                 ELSE 3
             END,
             m.joined_at
         LIMIT $2 OFFSET $3",
    )
    .bind(community_id)
    .bind(limit)
    .bind(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("SELECT COUNT(*) FROM memberships WHERE community_id = $1")
        .bind(community_id)
        .fetch_one(pool)
        .await
}

#[tracing::instrument(skip_all)]
pub async fn get_category_by_id(
    pool: &PgPool,
    category_id: Uuid,
) -> Result<Option<CategoryForSettings>, sqlx::Error> {
    sqlx::query_as::<_, CategoryForSettings>(
        "SELECT id, name, slug, description, sort_order
         FROM categories
         WHERE id = $1",
    )
    .bind(category_id)
    .fetch_optional(pool)
    .await
}

// ============================================================================
// Footnote queries
// ============================================================================

/// 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>(
        "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
         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",
    )
    .bind(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("SELECT COUNT(*) FROM post_footnotes WHERE post_id = $1")
        .bind(post_id)
        .fetch_one(pool)
        .await
}

/// Fetch a post's author_id and body_markdown for quote verification.
#[tracing::instrument(skip_all)]
pub async fn get_post_body_markdown(
    pool: &PgPool,
    post_id: Uuid,
) -> Result<Option<(Uuid, String)>, sqlx::Error> {
    let row: Option<(Uuid, String)> = sqlx::query_as(
        "SELECT author_id, body_markdown FROM posts WHERE id = $1",
    )
    .bind(post_id)
    .fetch_optional(pool)
    .await?;
    Ok(row)
}

// ============================================================================
// Ban / mute queries
// ============================================================================

/// 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(
        "SELECT EXISTS(SELECT 1 FROM users WHERE mnw_account_id = $1 AND suspended_at IS NOT NULL)",
    )
    .bind(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(
        "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()))",
    )
    .bind(community_id)
    .bind(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(
        "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()))",
    )
    .bind(community_id)
    .bind(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 all active bans and mutes in a community.
#[tracing::instrument(skip_all)]
pub async fn list_community_bans(
    pool: &PgPool,
    community_id: Uuid,
) -> Result<Vec<CommunityBanRow>, sqlx::Error> {
    sqlx::query_as::<_, CommunityBanRow>(
        "SELECT cb.id, cb.user_id,
                u.username, u.display_name,
                cb.ban_type, cb.reason, cb.expires_at, cb.created_at,
                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",
    )
    .bind(community_id)
    .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>(
        "SELECT ml.id,
                actor.username AS actor_username,
                ml.action,
                target.username AS target_username,
                ml.reason,
                ml.created_at
         FROM mod_log ml
         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",
    )
    .bind(community_id)
    .bind(limit)
    .bind(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(
        "SELECT COUNT(*) FROM mod_log WHERE community_id = $1",
    )
    .bind(community_id)
    .fetch_one(pool)
    .await
}

/// Look up a user's UUID by username.
#[tracing::instrument(skip_all)]
pub async fn get_user_by_username(
    pool: &PgPool,
    username: &str,
) -> Result<Option<Uuid>, sqlx::Error> {
    let row: Option<(Uuid,)> = sqlx::query_as(
        "SELECT mnw_account_id FROM users WHERE username = $1",
    )
    .bind(username)
    .fetch_optional(pool)
    .await?;
    Ok(row.map(|r| r.0))
}

// ============================================================================
// User profile queries
// ============================================================================

#[derive(sqlx::FromRow)]
pub struct UserProfileRow {
    pub user_id: Uuid,
    pub username: String,
    pub display_name: Option<String>,
    pub avatar_url: Option<String>,
    pub role: CommunityRole,
    pub joined_at: DateTime<Utc>,
    pub post_count: i64,
    pub endorsement_count: i64,
}

/// Fetch a user's profile within a specific community.
/// Returns None if the user is not a member of the community.
#[tracing::instrument(skip_all)]
pub async fn get_user_profile_in_community(
    pool: &PgPool,
    community_slug: &str,
    username: &str,
) -> Result<Option<UserProfileRow>, sqlx::Error> {
    sqlx::query_as::<_, UserProfileRow>(
        "SELECT u.mnw_account_id AS user_id,
                u.username,
                u.display_name,
                u.avatar_url,
                m.role,
                m.joined_at,
                (SELECT COUNT(*) FROM posts p
                 JOIN threads t ON t.id = p.thread_id
                 JOIN categories c ON c.id = t.category_id
                 WHERE p.author_id = u.mnw_account_id
                   AND c.community_id = co.id
                   AND p.deleted_at IS NULL
                   AND t.deleted_at IS NULL) AS post_count,
                (SELECT COUNT(*) FROM post_endorsements pe
                 JOIN posts p ON p.id = pe.post_id
                 JOIN threads t ON t.id = p.thread_id
                 JOIN categories c ON c.id = t.category_id
                 WHERE p.author_id = u.mnw_account_id
                   AND c.community_id = co.id
                   AND p.deleted_at IS NULL
                   AND t.deleted_at IS NULL) AS endorsement_count
         FROM users u
         JOIN memberships m ON m.user_id = u.mnw_account_id
         JOIN communities co ON co.id = m.community_id
         WHERE co.slug = $1 AND u.username = $2",
    )
    .bind(community_slug)
    .bind(username)
    .fetch_optional(pool)
    .await
}

#[derive(sqlx::FromRow)]
pub struct UserActivityRow {
    pub thread_id: Uuid,
    pub thread_title: String,
    pub category_name: String,
    pub category_slug: String,
    pub post_created_at: DateTime<Utc>,
    pub is_thread_author: bool,
}

/// Fetch a user's recent activity (posts) within a community.
#[tracing::instrument(skip_all)]
pub async fn get_user_activity_in_community(
    pool: &PgPool,
    community_id: Uuid,
    user_id: Uuid,
    limit: i64,
) -> Result<Vec<UserActivityRow>, sqlx::Error> {
    sqlx::query_as::<_, UserActivityRow>(
        "SELECT t.id AS thread_id,
                t.title AS thread_title,
                c.name AS category_name,
                c.slug AS category_slug,
                p.created_at AS post_created_at,
                (t.author_id = $2) AS is_thread_author
         FROM posts p
         JOIN threads t ON t.id = p.thread_id
         JOIN categories c ON c.id = t.category_id
         WHERE c.community_id = $1
           AND p.author_id = $2
           AND p.deleted_at IS NULL
           AND t.deleted_at IS NULL
         ORDER BY p.created_at DESC
         LIMIT $3",
    )
    .bind(community_id)
    .bind(user_id)
    .bind(limit)
    .fetch_all(pool)
    .await
}

#[derive(sqlx::FromRow, serde::Serialize)]
pub struct UserMembershipSummary {
    pub community_name: String,
    pub community_slug: String,
    pub role: CommunityRole,
    pub joined_at: DateTime<Utc>,
    pub post_count: i64,
}

/// Fetch all community memberships for a user with post counts.
#[tracing::instrument(skip_all)]
pub async fn get_user_membership_summary(
    pool: &PgPool,
    user_id: Uuid,
) -> Result<Vec<UserMembershipSummary>, sqlx::Error> {
    sqlx::query_as::<_, UserMembershipSummary>(
        "SELECT co.name AS community_name,
                co.slug AS community_slug,
                m.role,
                m.joined_at,
                (SELECT COUNT(*) FROM posts p
                 JOIN threads t ON t.id = p.thread_id
                 JOIN categories c ON c.id = t.category_id
                 WHERE p.author_id = $1
                   AND c.community_id = co.id
                   AND p.deleted_at IS NULL
                   AND t.deleted_at IS NULL) AS post_count
         FROM memberships m
         JOIN communities co ON co.id = m.community_id
         WHERE m.user_id = $1
           AND co.suspended_at IS NULL
         ORDER BY co.name",
    )
    .bind(user_id)
    .fetch_all(pool)
    .await
}

// ============================================================================
// Admin queries
// ============================================================================

#[derive(sqlx::FromRow)]
pub struct AdminCommunityRow {
    pub id: Uuid,
    pub name: String,
    pub slug: String,
    pub suspended_at: Option<DateTime<Utc>>,
    pub suspension_reason: Option<String>,
}

/// List all communities for the admin dashboard (capped at 500).
#[tracing::instrument(skip_all)]
pub async fn list_all_communities(
    pool: &PgPool,
) -> Result<Vec<AdminCommunityRow>, sqlx::Error> {
    sqlx::query_as::<_, AdminCommunityRow>(
        "SELECT id, name, slug, suspended_at, suspension_reason
         FROM communities ORDER BY name LIMIT 500",
    )
    .fetch_all(pool)
    .await
}

#[derive(sqlx::FromRow)]
pub struct AdminUserRow {
    pub id: Uuid,
    pub username: String,
    pub display_name: Option<String>,
    pub suspended_at: Option<DateTime<Utc>>,
    pub suspension_reason: Option<String>,
}

/// Search users by username prefix for the admin dashboard.
#[tracing::instrument(skip_all)]
pub async fn search_users(
    pool: &PgPool,
    query: &str,
) -> Result<Vec<AdminUserRow>, sqlx::Error> {
    let escaped = query
        .replace('\\', "\\\\")
        .replace('%', "\\%")
        .replace('_', "\\_");
    sqlx::query_as::<_, AdminUserRow>(
        "SELECT mnw_account_id AS id, username, display_name, suspended_at, suspension_reason
         FROM users
         WHERE username ILIKE $1 ESCAPE '\\'
         ORDER BY username
         LIMIT 50",
    )
    .bind(format!("{escaped}%"))
    .fetch_all(pool)
    .await
}

// ============================================================================
// Link preview queries
// ============================================================================

#[derive(sqlx::FromRow)]
pub struct LinkPreviewRow {
    pub post_id: Uuid,
    pub url: String,
    pub title: Option<String>,
    pub description: Option<String>,
}

/// Batch-fetch link previews for a set of post IDs.
#[tracing::instrument(skip_all)]
pub async fn list_link_previews_for_posts(
    pool: &PgPool,
    post_ids: &[Uuid],
) -> Result<Vec<LinkPreviewRow>, sqlx::Error> {
    sqlx::query_as::<_, LinkPreviewRow>(
        "SELECT post_id, url, title, description
         FROM link_previews
         WHERE post_id = ANY($1)
         ORDER BY fetched_at",
    )
    .bind(post_ids)
    .fetch_all(pool)
    .await
}

// ============================================================================
// Mention queries
// ============================================================================

/// Batch-check which threads have at least one mention of the given user.
#[tracing::instrument(skip_all)]
pub async fn get_threads_with_mentions_for_user(
    pool: &PgPool,
    user_id: Uuid,
    thread_ids: &[Uuid],
) -> Result<Vec<Uuid>, sqlx::Error> {
    if thread_ids.is_empty() {
        return Ok(Vec::new());
    }
    sqlx::query_scalar(
        "SELECT DISTINCT p.thread_id
         FROM post_mentions pm
         JOIN posts p ON p.id = pm.post_id
         WHERE pm.mentioned_user_id = $1
           AND p.thread_id = ANY($2)",
    )
    .bind(user_id)
    .bind(thread_ids)
    .fetch_all(pool)
    .await
}

/// Resolve usernames to user IDs, filtered to community members.
#[tracing::instrument(skip_all)]
pub async fn resolve_usernames_in_community(
    pool: &PgPool,
    community_id: Uuid,
    usernames: &[String],
) -> Result<std::collections::HashMap<String, Uuid>, sqlx::Error> {
    if usernames.is_empty() {
        return Ok(std::collections::HashMap::new());
    }
    let rows: Vec<(String, Uuid)> = sqlx::query_as(
        "SELECT u.username, u.mnw_account_id
         FROM users u
         WHERE u.username = ANY($1)
           AND u.mnw_account_id IN (SELECT user_id FROM memberships WHERE community_id = $2)",
    )
    .bind(usernames)
    .bind(community_id)
    .fetch_all(pool)
    .await?;
    Ok(rows.into_iter().collect())
}

// ============================================================================
// Endorsement queries
// ============================================================================

#[derive(sqlx::FromRow)]
pub struct EndorsementRow {
    pub post_id: Uuid,
    pub endorser_id: Uuid,
}

// ============================================================================
// Tracked thread queries
// ============================================================================

/// Check if a user is tracking a specific thread.
#[tracing::instrument(skip_all)]
pub async fn is_thread_tracked(
    pool: &PgPool,
    user_id: Uuid,
    thread_id: Uuid,
) -> Result<bool, sqlx::Error> {
    sqlx::query_scalar(
        "SELECT EXISTS(SELECT 1 FROM tracked_threads WHERE user_id = $1 AND thread_id = $2)",
    )
    .bind(user_id)
    .bind(thread_id)
    .fetch_one(pool)
    .await
}

#[derive(sqlx::FromRow)]
pub struct TrackedThreadRow {
    pub thread_id: Uuid,
    pub thread_title: String,
    pub community_name: String,
    pub community_slug: String,
    pub category_slug: String,
    pub unread_count: i64,
    pub has_mention: bool,
    pub tracked_at: DateTime<Utc>,
}

/// List a user's tracked threads with unread post counts.
#[tracing::instrument(skip_all)]
pub async fn list_tracked_threads(
    pool: &PgPool,
    user_id: Uuid,
) -> Result<Vec<TrackedThreadRow>, sqlx::Error> {
    sqlx::query_as::<_, TrackedThreadRow>(
        "SELECT tt.thread_id,
                t.title AS thread_title,
                co.name AS community_name,
                co.slug AS community_slug,
                cat.slug AS category_slug,
                (SELECT COUNT(*) FROM posts p
                 WHERE p.thread_id = tt.thread_id
                   AND (tt.last_read_post_id IS NULL OR p.created_at > (
                       SELECT created_at FROM posts WHERE id = tt.last_read_post_id
                   ))
                ) AS unread_count,
                EXISTS (
                    SELECT 1 FROM post_mentions pm
                    JOIN posts p ON p.id = pm.post_id
                    WHERE pm.mentioned_user_id = tt.user_id
                      AND p.thread_id = tt.thread_id
                ) AS has_mention,
                tt.tracked_at
         FROM tracked_threads tt
         JOIN threads t ON t.id = tt.thread_id
         JOIN categories cat ON cat.id = t.category_id
         JOIN communities co ON co.id = cat.community_id
         WHERE tt.user_id = $1 AND t.deleted_at IS NULL AND co.suspended_at IS NULL
         ORDER BY t.last_activity_at DESC",
    )
    .bind(user_id)
    .fetch_all(pool)
    .await
}

// ============================================================================
// Tag queries
// ============================================================================

#[derive(sqlx::FromRow)]
pub struct TagRow {
    pub id: Uuid,
    pub name: String,
    pub slug: String,
}

/// List all tags for a community.
#[tracing::instrument(skip_all)]
pub async fn list_tags_for_community(
    pool: &PgPool,
    community_id: Uuid,
) -> Result<Vec<TagRow>, sqlx::Error> {
    sqlx::query_as::<_, TagRow>(
        "SELECT id, name, slug FROM tags WHERE community_id = $1 ORDER BY name",
    )
    .bind(community_id)
    .fetch_all(pool)
    .await
}

#[derive(sqlx::FromRow)]
pub struct ThreadTagRow {
    pub thread_id: Uuid,
    pub tag_name: String,
    pub tag_slug: String,
}

/// Batch-fetch tags for a set of thread IDs.
#[tracing::instrument(skip_all)]
pub async fn list_tags_for_threads(
    pool: &PgPool,
    thread_ids: &[Uuid],
) -> Result<Vec<ThreadTagRow>, sqlx::Error> {
    sqlx::query_as::<_, ThreadTagRow>(
        "SELECT tt.thread_id, t.name AS tag_name, t.slug AS tag_slug
         FROM thread_tags tt
         JOIN tags t ON t.id = tt.tag_id
         WHERE tt.thread_id = ANY($1)
         ORDER BY t.name",
    )
    .bind(thread_ids)
    .fetch_all(pool)
    .await
}

/// Count threads in a category, optionally filtered by tag slug.
#[tracing::instrument(skip_all)]
pub async fn count_threads_in_category_filtered(
    pool: &PgPool,
    community_slug: &str,
    category_slug: &str,
    tag_slug: Option<&str>,
) -> Result<i64, sqlx::Error> {
    if let Some(tag) = tag_slug {
        sqlx::query_scalar(
            "SELECT COUNT(DISTINCT t.id)
             FROM threads t
             JOIN categories c ON c.id = t.category_id
             JOIN communities co ON co.id = c.community_id
             JOIN thread_tags tt ON tt.thread_id = t.id
             JOIN tags tg ON tg.id = tt.tag_id AND tg.slug = $3 AND tg.community_id = co.id
             WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL",
        )
        .bind(community_slug)
        .bind(category_slug)
        .bind(tag)
        .fetch_one(pool)
        .await
    } else {
        count_threads_in_category(pool, community_slug, category_slug).await
    }
}

/// List threads with sorting, optionally filtered by tag slug.
#[tracing::instrument(skip_all)]
#[allow(clippy::too_many_arguments)]
pub async fn list_threads_in_category_sorted_filtered(
    pool: &PgPool,
    community_slug: &str,
    category_slug: &str,
    sort: &str,
    order: &str,
    limit: i64,
    offset: i64,
    tag_slug: Option<&str>,
) -> Result<Vec<ThreadWithMeta>, sqlx::Error> {
    if tag_slug.is_none() {
        return list_threads_in_category_sorted(pool, community_slug, category_slug, sort, order, limit, offset).await;
    }

    let tag = tag_slug.unwrap();
    let order_clause = match (sort, order) {
        ("replies", "asc") => "ORDER BY t.pinned DESC, reply_count ASC, t.last_activity_at DESC",
        ("replies", _) => "ORDER BY t.pinned DESC, reply_count DESC, t.last_activity_at DESC",
        (_, "asc") => "ORDER BY t.pinned DESC, t.last_activity_at ASC",
        _ => "ORDER BY t.pinned DESC, t.last_activity_at DESC",
    };

    let query = format!(
        "SELECT t.id, t.title,
                COALESCE(u.display_name, u.username) AS author_name,
                u.username AS author_username,
                t.reply_count::BIGINT AS reply_count,
                t.last_activity_at,
                t.pinned, t.locked
         FROM threads t
         JOIN categories c ON c.id = t.category_id
         JOIN communities co ON co.id = c.community_id
         JOIN users u ON u.mnw_account_id = t.author_id
         JOIN thread_tags tt ON tt.thread_id = t.id
         JOIN tags tg ON tg.id = tt.tag_id AND tg.slug = $3 AND tg.community_id = co.id
         WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL
         {order_clause}
         LIMIT $4 OFFSET $5"
    );

    sqlx::query_as::<_, ThreadWithMeta>(&query)
        .bind(community_slug)
        .bind(category_slug)
        .bind(tag)
        .bind(limit)
        .bind(offset)
        .fetch_all(pool)
        .await
}

// ============================================================================
// Flag queries
// ============================================================================

#[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.
#[tracing::instrument(skip_all)]
pub async fn list_pending_flags(
    pool: &PgPool,
    community_id: Uuid,
) -> Result<Vec<PendingFlagRow>, sqlx::Error> {
    sqlx::query_as::<_, PendingFlagRow>(
        "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
         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",
    )
    .bind(community_id)
    .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(
        "SELECT EXISTS(SELECT 1 FROM post_flags WHERE post_id = $1 AND flagger_id = $2)",
    )
    .bind(post_id)
    .bind(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(
        "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
        )",
    )
    .bind(flag_id)
    .bind(community_id)
    .fetch_one(pool)
    .await
}


// ============================================================================
// Endorsement queries
// ============================================================================

// ============================================================================
// Search queries
// ============================================================================

#[derive(sqlx::FromRow)]
pub struct SearchResultRow {
    pub thread_id: Uuid,
    pub thread_title: String,
    pub author_username: String,
    pub community_name: String,
    pub community_slug: String,
    pub category_name: String,
    pub category_slug: String,
    pub snippet: String,
    pub last_activity_at: DateTime<Utc>,
    pub rank: f64,
}

/// Full-text search across threads and posts. Combines tsvector ranking with
/// trigram similarity for typo tolerance. Title matches ranked above body.
/// Optionally scoped to a single community by slug.
#[tracing::instrument(skip_all)]
pub async fn search_threads(
    pool: &PgPool,
    query: &str,
    community_slug: Option<&str>,
    limit: i64,
) -> Result<Vec<SearchResultRow>, sqlx::Error> {
    sqlx::query_as::<_, SearchResultRow>(
        "WITH thread_matches AS (
            SELECT t.id AS thread_id,
                   t.title AS thread_title,
                   COALESCE(u.display_name, u.username) AS author_username,
                   co.name AS community_name,
                   co.slug AS community_slug,
                   c.name AS category_name,
                   c.slug AS category_slug,
                   LEFT(t.title, 200) AS snippet,
                   t.last_activity_at,
                   (ts_rank(t.search_tsv, websearch_to_tsquery('english', $1)) * 2.0
                    + similarity(t.title, $2)) AS rank
            FROM threads t
            JOIN categories c ON c.id = t.category_id
            JOIN communities co ON co.id = c.community_id
            JOIN users u ON u.mnw_account_id = t.author_id
            WHERE t.deleted_at IS NULL
              AND co.suspended_at IS NULL
              AND (t.search_tsv @@ websearch_to_tsquery('english', $1)
                   OR similarity(t.title, $2) > 0.1)
              AND ($3::text IS NULL OR co.slug = $3)
        ),
        post_matches AS (
            SELECT DISTINCT ON (t.id)
                   t.id AS thread_id,
                   t.title AS thread_title,
                   COALESCE(pu.display_name, pu.username) AS author_username,
                   co.name AS community_name,
                   co.slug AS community_slug,
                   c.name AS category_name,
                   c.slug AS category_slug,
                   LEFT(p.body_markdown, 200) AS snippet,
                   t.last_activity_at,
                   ts_rank(p.search_tsv, websearch_to_tsquery('english', $1)) AS rank
            FROM posts p
            JOIN threads t ON t.id = p.thread_id
            JOIN categories c ON c.id = t.category_id
            JOIN communities co ON co.id = c.community_id
            JOIN users pu ON pu.mnw_account_id = t.author_id
            WHERE t.deleted_at IS NULL
              AND co.suspended_at IS NULL
              AND p.removed_at IS NULL
              AND p.search_tsv @@ websearch_to_tsquery('english', $1)
              AND ($3::text IS NULL OR co.slug = $3)
              AND t.id NOT IN (SELECT thread_id FROM thread_matches)
            ORDER BY t.id, ts_rank(p.search_tsv, websearch_to_tsquery('english', $1)) DESC
        )
        SELECT * FROM thread_matches
        UNION ALL
        SELECT * FROM post_matches
        ORDER BY rank DESC, last_activity_at DESC
        LIMIT $4",
    )
    .bind(query)
    .bind(query)
    .bind(community_slug)
    .bind(limit)
    .fetch_all(pool)
    .await
}

// ============================================================================
// Endorsement read queries
// ============================================================================

/// Batch-fetch endorsements for a set of post IDs.
#[tracing::instrument(skip_all)]
pub async fn list_endorsements_for_posts(
    pool: &PgPool,
    post_ids: &[Uuid],
) -> Result<Vec<EndorsementRow>, sqlx::Error> {
    sqlx::query_as::<_, EndorsementRow>(
        "SELECT post_id, endorser_id FROM post_endorsements WHERE post_id = ANY($1)",
    )
    .bind(post_ids)
    .fetch_all(pool)
    .await
}

// ============================================================================
// Image queries
// ============================================================================

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

/// Fetch an image by ID (for serving).
#[tracing::instrument(skip_all)]
pub async fn get_image(
    pool: &PgPool,
    image_id: Uuid,
) -> Result<Option<ImageRow>, sqlx::Error> {
    sqlx::query_as::<_, ImageRow>(
        "SELECT id, s3_key, content_type, removed_at FROM images WHERE id = $1",
    )
    .bind(image_id)
    .fetch_optional(pool)
    .await
}

/// Get the maximum sort_order among categories in a community. Returns -1 if no categories exist.
#[tracing::instrument(skip_all)]
pub async fn get_max_category_order(
    pool: &PgPool,
    community_id: Uuid,
) -> Result<i32, sqlx::Error> {
    let row: (Option<i32>,) = sqlx::query_as(
        "SELECT MAX(sort_order) FROM categories WHERE community_id = $1",
    )
    .bind(community_id)
    .fetch_one(pool)
    .await?;
    Ok(row.0.unwrap_or(-1))
}

/// Check if a thread exists by ID.
#[tracing::instrument(skip_all)]
pub async fn thread_exists(
    pool: &PgPool,
    thread_id: Uuid,
) -> Result<bool, sqlx::Error> {
    sqlx::query_scalar(
        "SELECT EXISTS(SELECT 1 FROM threads WHERE id = $1)",
    )
    .bind(thread_id)
    .fetch_one(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(
        "SELECT COUNT(*) FROM images
         WHERE uploader_id = $1
           AND created_at > now() - make_interval(secs => $2::float8)",
    )
    .bind(user_id)
    .bind(window_secs as f64)
    .fetch_one(pool)
    .await
}