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

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

/// 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!(
        "SELECT id FROM threads WHERE external_ref = $1",
        external_ref,
    )
    .fetch_optional(pool)
    .await
    .map(|opt| opt.map(|r| (r.id,)))
}

/// 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> {
    // This count surfaces on the MNW server (the internal /stats endpoint), which
    // can't render mt's tombstones, so it must be the canonical *active* count,
    // excluding mod-removed and soft-deleted posts (matching the m029 trigger and
    // the profile tallies). Contrast `count_posts_in_thread`, which deliberately
    // counts tombstones because the in-app thread list renders them.
    sqlx::query!(
        r#"SELECT COUNT(p.id) AS "count!", MAX(p.created_at) AS "max_created: chrono::DateTime<chrono::Utc>"
           FROM posts p
           WHERE p.thread_id = $1
             AND p.removed_at IS NULL
             AND p.deleted_at IS NULL"#,
        thread_id,
    )
    .fetch_optional(pool)
    .await
    .map(|opt| opt.map(|r| (r.count, r.max_created)))
}

#[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,
        r#"SELECT t.id, t.title,
                COALESCE(u.display_name, u.username) AS "author_name!",
                u.username AS author_username,
                GREATEST(t.post_count - 1, 0)::BIGINT AS "reply_count!",
                t.last_activity_at AS "last_activity_at: chrono::DateTime<chrono::Utc>",
                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"#,
        community_slug,
        category_slug,
        limit,
        offset,
    )
    .fetch_all(pool)
    .await
}

/// List threads in a category, sorted, optionally filtered to one tag.
///
/// Pinned threads always sort first. `sort`/`order` are typed enums, so the
/// `ORDER BY` clause is chosen from an exhaustive match of fixed literals (no
/// user string reaches the SQL); when `tag_slug` is `Some`, an extra join
/// restricts the results to threads carrying that tag in the same community.
#[tracing::instrument(skip_all)]
#[allow(clippy::too_many_arguments)]
pub async fn list_threads_in_category_sorted(
    pool: &PgPool,
    community_slug: &str,
    category_slug: &str,
    sort: SortColumn,
    order: SortOrder,
    limit: i64,
    offset: i64,
    tag_slug: Option<&str>,
) -> Result<Vec<ThreadWithMeta>, sqlx::Error> {
    let order_clause = match (sort, order) {
        (SortColumn::Replies, SortOrder::Asc) => {
            "ORDER BY t.pinned DESC, reply_count ASC, t.last_activity_at DESC"
        }
        (SortColumn::Replies, SortOrder::Desc) => {
            "ORDER BY t.pinned DESC, reply_count DESC, t.last_activity_at DESC"
        }
        (SortColumn::Activity, SortOrder::Asc) => "ORDER BY t.pinned DESC, t.last_activity_at ASC",
        (SortColumn::Activity, SortOrder::Desc) => {
            "ORDER BY t.pinned DESC, t.last_activity_at DESC"
        }
    };

    // When a tag filter is present it binds as $3, pushing limit/offset to $4/$5.
    let (tag_join, limit_ph, offset_ph) = if tag_slug.is_some() {
        (
            "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",
            "$4",
            "$5",
        )
    } else {
        ("", "$3", "$4")
    };

    let query = format!(
        "SELECT t.id, t.title,
                COALESCE(u.display_name, u.username) AS author_name,
                u.username AS author_username,
                GREATEST(t.post_count - 1, 0)::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
         {tag_join}
         WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL
         {order_clause}
         LIMIT {limit_ph} OFFSET {offset_ph}"
    );

    // runtime-checked: dynamic SQL (cannot use compile-time macro)
    let mut q = sqlx::query_as::<_, ThreadWithMeta>(&query)
        .bind(community_slug)
        .bind(category_slug);
    if let Some(tag) = tag_slug {
        q = q.bind(tag);
    }
    q.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!(
        r#"SELECT COUNT(*) AS "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"#,
        community_slug,
        category_slug,
    )
    .fetch_one(pool)
    .await
}

#[tracing::instrument(skip_all)]
pub async fn get_thread_with_breadcrumb(
    pool: &PgPool,
    thread_id: Uuid,
) -> Result<Option<super::Unscoped<ThreadWithBreadcrumb>>, sqlx::Error> {
    sqlx::query_as!(
        ThreadWithBreadcrumb,
        r#"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 AND t.deleted_at IS NULL"#,
        thread_id,
    )
    .fetch_optional(pool)
    .await
    .map(|opt| {
        opt.map(|row| {
            let community_id = row.community_id;
            super::Unscoped::new(row, community_id)
        })
    })
}

/// 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!(
            r#"SELECT COUNT(DISTINCT t.id) AS "count!"
             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"#,
            community_slug,
            category_slug,
            tag,
        )
        .fetch_one(pool)
        .await
    } else {
        count_threads_in_category(pool, community_slug, category_slug).await
    }
}

/// 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!(
        r#"SELECT EXISTS(SELECT 1 FROM threads WHERE id = $1) AS "exists!""#,
        thread_id,
    )
    .fetch_one(pool)
    .await
}