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

#[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> {
    // The trigram fuzzy branch uses the `%` operator (not `similarity(...) > k`)
    // so `idx_threads_title_trgm` (gin_trgm_ops) can serve it and the planner can
    // BitmapOr it with the tsvector GIN scan instead of seq-scanning every live
    // thread. `%` compares against `pg_trgm.similarity_threshold`, so we pin that
    // to 0.1 (the old inline constant) with `SET LOCAL` inside a transaction:
    // session-scoped, so it can't leak to other pooled connections. `similarity()`
    // still appears in the rank expression, but only computed for matched rows.
    let mut tx = pool.begin().await?;
    sqlx::query("SET LOCAL pg_trgm.similarity_threshold = 0.1")
        .execute(&mut *tx)
        .await?;
    let rows = sqlx::query_as!(
        SearchResultRow,
        // `q` parses the tsquery once (it was parsed 5× inline); cross-joining
        // the single-row CTE feeds it to every rank/match without re-parsing.
        // post_matches uses NOT EXISTS rather than NOT IN (better anti-join,
        // and NULL-safe).
        r#"WITH q AS (
            SELECT websearch_to_tsquery('english', $1) AS tsq
        ),
        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, q.tsq) * 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
            CROSS JOIN q
            WHERE t.deleted_at IS NULL
              AND co.suspended_at IS NULL
              AND (t.search_tsv @@ q.tsq
                   OR t.title % $2)
              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, q.tsq) 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
            -- Author of the matched *reply* (`p.author_id`), not the thread OP
            -- (`t.author_id`): the snippet is the reply's body, so it must be
            -- attributed to whoever wrote it.
            JOIN users pu ON pu.mnw_account_id = p.author_id
            CROSS JOIN q
            WHERE t.deleted_at IS NULL
              AND co.suspended_at IS NULL
              AND p.removed_at IS NULL
              AND p.search_tsv @@ q.tsq
              AND ($3::text IS NULL OR co.slug = $3)
              AND NOT EXISTS (SELECT 1 FROM thread_matches tm WHERE tm.thread_id = t.id)
            ORDER BY t.id, ts_rank(p.search_tsv, q.tsq) DESC
        )
        SELECT
            thread_id AS "thread_id!",
            thread_title AS "thread_title!",
            author_username AS "author_username!",
            community_name AS "community_name!",
            community_slug AS "community_slug!",
            category_name AS "category_name!",
            category_slug AS "category_slug!",
            snippet AS "snippet!",
            last_activity_at AS "last_activity_at!: chrono::DateTime<chrono::Utc>",
            rank AS "rank!"
        FROM (
            SELECT * FROM thread_matches
            UNION ALL
            SELECT * FROM post_matches
        ) results
        ORDER BY rank DESC, last_activity_at DESC
        LIMIT $4"#,
        query,
        query,
        community_slug,
        limit,
    )
    .fetch_all(&mut *tx)
    .await?;
    tx.commit().await?;
    Ok(rows)
}