//! Public discovery queries: search, browse, type/tag and price facets.
//!
//! Uses `pg_trgm` trigram indexes for fuzzy text matching.

use sqlx::{FromRow, PgPool, Row};
use std::fmt::Write as _;

use super::enums::{AiTierFilter, DiscoverSort, ItemType};
use super::models::{DbDiscoverItemRow, DbDiscoverProjectRow, DbItemTypeCount};
use crate::error::Result;

/// Shared filter parameters for discover item queries.
///
/// Used by both [`discover_items`] and [`count_discover_items`] to keep
/// their filter logic in sync. The `sort_by` field is only relevant for
/// `discover_items`; `count_discover_items` ignores it.
pub struct DiscoverFilters<'a> {
    pub search: Option<&'a str>,
    /// Selected item types. Empty means unfiltered; multiple values are OR'd.
    pub item_types: &'a [ItemType],
    /// Selected tag slugs. Empty means unfiltered; multiple values are OR'd,
    /// and each matches its own subtree.
    pub tags: &'a [String],
    pub min_price: Option<i32>,
    pub max_price: Option<i32>,
    pub sort_by: Option<DiscoverSort>,
    /// Single-valued by nature: the three AI-disclosure choices are nested
    /// ranges (everything ⊇ human-led ⊇ handmade), not independent values, so
    /// OR-ing them would only ever widen back to the looser one.
    pub ai_tier: Option<AiTierFilter>,
}

impl DiscoverFilters<'_> {
    /// Item-type slugs for the `= ANY($2)` bind.
    fn item_type_slugs(&self) -> Vec<String> {
        self.item_types
            .iter()
            .map(std::string::ToString::to_string)
            .collect()
    }

    /// `LIKE` patterns matching each selected tag's descendants, via tagtree so
    /// the dot-path convention and the LIKE metacharacter escaping stay in one
    /// place. Pairs with [`Self::tags`] as the exact-slug bind.
    fn tag_descendant_patterns(&self) -> Vec<String> {
        self.tags
            .iter()
            .map(|slug| tagtree::like_descendant_pattern(slug))
            .collect()
    }
}

// Shared SQL fragments for search (lexical tsvector + trigram/ILIKE fallback).
// The ILIKE escapes \, %, _ in the search term to prevent LIKE metacharacter interpretation.

/// Candidate membership: any word the searcher typed, none they excluded.
///
/// A macro rather than a `const` so `concat!` can splice it into the clauses at
/// compile time; written once, it cannot drift between the predicate that
/// decides a row matches and the expression that counts how well.
///
/// This is deliberately not `websearch_to_tsquery`, which ANDs every term. That
/// put a cliff at 100% coverage: a row holding four of five typed words was
/// excluded from the word tier alongside rows holding none, and the UI then
/// told both of them they had matched on spelling only. The rule is now the one
/// stated at the top of migration 179 -- exclusions filter, everything else
/// ranks -- and the tier is a count rather than a yes/no.
///
/// The function is IMMUTABLE and takes a constant config, so repeating it in
/// the same statement does not repeat the work.
macro_rules! any_term_query {
    () => {
        "mnw_any_term_query($1)"
    };
}

// The description terms are written as `COALESCE(i.description, '')` to match
// `idx_items_desc_trgm`, which is built on that expression rather than on the
// bare column. Written any other way Postgres will not use the index, and this
// clause runs on the busiest public page.
const ITEM_SEARCH_CLAUSE: &str = concat!(
    r" AND (
                i.search_tsv @@ ",
    any_term_query!(),
    r"
                OR i.title % $1
                OR i.title ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
                OR COALESCE(i.description, '') % $1
                OR COALESCE(i.description, '') ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
            )"
);

const PROJECT_SEARCH_CLAUSE: &str = concat!(
    r" AND (
                p.search_tsv @@ ",
    any_term_query!(),
    r"
                OR p.title % $1
                OR p.title ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
            )"
);

// ILIKE-only variants for short queries (1-2 chars) where trigram similarity is unreliable.

const ITEM_SEARCH_CLAUSE_SHORT: &str = r" AND (
                i.title ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
                OR COALESCE(i.description, '') ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
            )";

const PROJECT_SEARCH_CLAUSE_SHORT: &str = r" AND (
                p.title ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
            )";

// Tiered relevance. Full rationale in migration 179; the short version:
//
// One cut, placed at the truth condition of the heading it drives.
//
//   tier 2  -- holds at least one word the searcher typed. Renders in the main
//              block; a row with some but not all of them prints its own count.
//   tier 1  -- holds none of them, and is here on character overlap alone. Sits
//              under "Near matches. Matched on spelling, not on words", which is
//              true of every row in the tier by construction.
//
// The two tiers rank by different measures and never compare across the
// boundary. `ts_rank` above (frequency and length normalization over words that
// are actually there), `mnw_word_match_score` below (per-word trigram, so a
// misspelling costs one word instead of poisoning the comparison). They do not
// share a scale, which is fine precisely because the tier is decided first, by
// a count rather than by either score.
//
// See `templates/partials/discover_results.html` for the rendering.

/// Holds at least one word the searcher typed. Ranked by `ts_rank`.
pub const MATCH_TIER_LEXICAL: i16 = 2;

/// Holds none of the typed words; character overlap only. Ranked by per-word
/// trigram scoring (`mnw_word_match_score`, migration 179).
///
/// Only the long-query path assigns tiers. A 1-2 character search is
/// substring-only by construction, so every row would land here and the
/// distinction would say nothing; those rows get `NULL` instead and the UI
/// draws no boundary.
pub const MATCH_TIER_FUZZY: i16 = 1;

/// Tier 2 when the row holds any typed word, tier 1 when it holds none.
/// `smallint` to match [`DbDiscoverItemRow::match_tier`]'s `Option<i16>`.
///
/// The literals here and the constants above are checked against each other by
/// `match_tier_literals_match_constants`.
macro_rules! match_tier_expr {
    ($tsv:expr) => {
        concat!(
            "CASE WHEN mnw_exact_terms(",
            $tsv,
            ", $1) > 0 THEN 2 ELSE 1 END::smallint as match_tier"
        )
    };
}

/// How many of the typed words this row holds, and how many were typed.
///
/// Projected so the template can print "Matched 3 of 5 words" on a row that has
/// some but not all. The denominator is constant per query, but it is stemmed
/// in Postgres, so it comes back per row rather than being recomputed in Rust
/// off the raw string and disagreeing with the numerator.
macro_rules! match_terms_expr {
    ($tsv:expr) => {
        concat!(
            "mnw_exact_terms(",
            $tsv,
            ", $1)::int as matched_terms,
                cardinality(mnw_query_terms($1))::int as query_terms"
        )
    };
}

/// Rank within a tier: `ts_rank` for lexical rows, per-word trigram scoring for
/// fuzzy ones.
///
/// Both arms read their weighting off the `setweight` A/B split in the
/// generated `search_tsv` column (migration 176) rather than carrying a
/// constant: `ts_rank` honours lexeme weights natively, and
/// `mnw_word_match_score` (migration 179) applies the same halving to
/// description lexemes that used to be written into this expression.
///
/// The fuzzy arm scores each query word separately and averages, instead of
/// comparing the whole query string against the whole title. Whole-phrase
/// `similarity` is worst on precisely the queries that reach this tier: a long
/// query against a short title scores low however well the words line up,
/// because the query's surplus trigrams count against it. Per-word, a
/// misspelling costs one word.
macro_rules! match_score_expr {
    ($tsv:expr) => {
        concat!(
            "CASE WHEN mnw_exact_terms(",
            $tsv,
            ", $1) > 0 THEN ts_rank(",
            $tsv,
            ", ",
            any_term_query!(),
            ")
                     ELSE mnw_word_match_score(",
            $tsv,
            ", $1) END::real as match_score"
        )
    };
}

/// Maximum allowed search term length. Queries longer than this are truncated.
const MAX_SEARCH_LEN: usize = 200;

/// Normalize a search term: trim whitespace, truncate to [`MAX_SEARCH_LEN`],
/// and return `None` if the result is empty.
pub(crate) fn normalize_search(raw: Option<&str>) -> Option<String> {
    let trimmed = raw?.trim();
    if trimmed.is_empty() {
        return None;
    }
    if trimmed.len() > MAX_SEARCH_LEN {
        // Truncate at a char boundary
        let end = trimmed
            .char_indices()
            .take_while(|(i, _)| *i < MAX_SEARCH_LEN)
            .last()
            .map_or(MAX_SEARCH_LEN, |(i, c)| i + c.len_utf8());
        Some(trimmed[..end].to_string())
    } else {
        Some(trimmed.to_string())
    }
}

/// Returns `true` when the search term is too short for trigram matching (1-2 chars).
pub(crate) fn is_short_query(term: &str) -> bool {
    term.trim().len() <= 2
}

/// The item-type clause. OR within the facet: any selected type matches.
const ITEM_TYPE_CLAUSE: &str = " AND i.item_type = ANY($2)";

/// The tag clause. OR within the facet, and each selected tag matches its own
/// subtree: `$5` holds the exact slugs, `$6` the `tagtree`-built descendant
/// patterns. LIKE's default escape character is backslash, which is what
/// `tagtree::like_descendant_pattern` escapes with.
const TAG_CLAUSE: &str = r" AND EXISTS (
                SELECT 1 FROM item_tags it2
                JOIN tags t2 ON t2.id = it2.tag_id
                WHERE it2.item_id = i.id
                AND (t2.slug = ANY($5) OR t2.path LIKE ANY($6))
            )";

/// Append discover-item filter clauses to a dynamic query.
/// Parameter positions: $1=search, $2=item_types, $3=min_price, $4=max_price,
/// $5=tag_slugs, $6=tag_descendant_patterns. AI tier is inlined (see below).
///
/// When `short_query` is `true`, the ILIKE-only clause is used instead of the
/// full trigram + ILIKE clause (trigram matching is unreliable for 1-2 char terms).
fn append_item_discover_filters(
    query: &mut String,
    filters: &DiscoverFilters<'_>,
    has_search: bool,
    short_query: bool,
) {
    if has_search {
        if short_query {
            query.push_str(ITEM_SEARCH_CLAUSE_SHORT);
        } else {
            query.push_str(ITEM_SEARCH_CLAUSE);
        }
    }
    if !filters.item_types.is_empty() {
        query.push_str(ITEM_TYPE_CLAUSE);
    }
    if filters.min_price.is_some() {
        query.push_str(" AND i.price_cents >= $3");
    }
    if filters.max_price.is_some() {
        query.push_str(" AND i.price_cents <= $4");
    }
    if !filters.tags.is_empty() {
        query.push_str(TAG_CLAUSE);
    }
    // AI disclosure filter: `Handmade only` narrows to handmade; `Human-led`
    // accepts handmade ∪ assisted. Values are enum-derived constants (not user
    // input), so inlining the literals is safe and keeps the bind-position
    // count stable across queries that use this fragment.
    match filters.ai_tier {
        Some(AiTierFilter::HandmadeOnly) => query.push_str(" AND i.ai_tier = 'handmade'"),
        Some(AiTierFilter::HumanLed) => {
            query.push_str(" AND i.ai_tier IN ('handmade', 'assisted')");
        }
        None => {}
    }
}

/// Bind the 6 discover-filter parameters ($1-$6) to a sqlx query.
/// The AI-tier filter is appended to the WHERE as a literal SQL fragment,
/// so it occupies no bind position.
///
/// Array binds are always supplied even when the corresponding clause is
/// omitted, so every discover query has the same bind arity regardless of which
/// filters are active.
macro_rules! bind_item_discover_filters {
    ($q:expr, $filters:expr, $search_term:expr) => {
        $q.bind($search_term.unwrap_or(""))
            .bind($filters.item_type_slugs())
            .bind($filters.min_price.unwrap_or(0))
            .bind($filters.max_price.unwrap_or(i32::MAX))
            .bind($filters.tags.to_vec())
            .bind($filters.tag_descendant_patterns())
    };
}

/// The visibility predicate every public item query must carry.
///
/// Kept as one constant because the facet counts and the result set drifted apart
/// once already: `get_tag_counts` was missing `scan_status` and `is_sandbox`
/// entirely, so it counted items the results would never show.
pub(crate) const ITEM_VISIBILITY_WHERE: &str = " WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL AND p.is_public = true AND i.scan_status = 'clean' AND u.is_sandbox = FALSE";

/// Which facet is being counted, and therefore which filter to omit.
///
/// A facet's counts answer "what would I get if I picked this value", so the
/// facet's own current selection must not constrain them. Selecting
/// `item_type=audio` and then applying that filter to the item-type counts
/// would zero every other type and hide the 12 samples also available.
/// Every *other* active filter still applies.
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum FacetAxis {
    ItemType,
    Tag,
    Price,
    AiTier,
}

/// Append facet-count filter clauses, omitting `axis`'s own dimension.
///
/// Uses the same bind layout as [`append_item_discover_filters`]
/// ($1=search, $2=item_type, $3=min_price, $4=max_price, $5=tag) so every
/// discover query binds identically, and `ai_tier` is inlined as an
/// enum-derived literal exactly as the result queries do.
pub(crate) fn append_facet_filters(
    query: &mut String,
    filters: &DiscoverFilters<'_>,
    axis: FacetAxis,
    has_search: bool,
    short_query: bool,
) {
    if has_search {
        if short_query {
            query.push_str(ITEM_SEARCH_CLAUSE_SHORT);
        } else {
            query.push_str(ITEM_SEARCH_CLAUSE);
        }
    }
    if axis != FacetAxis::ItemType && !filters.item_types.is_empty() {
        query.push_str(ITEM_TYPE_CLAUSE);
    }
    if axis != FacetAxis::Price {
        if filters.min_price.is_some() {
            query.push_str(" AND i.price_cents >= $3");
        }
        if filters.max_price.is_some() {
            query.push_str(" AND i.price_cents <= $4");
        }
    }
    if axis != FacetAxis::Tag && !filters.tags.is_empty() {
        // Descendant match via the materialized dot-path, matching the result
        // query. This previously used `parent_id`, which matched direct children
        // only, so any tag with grandchildren counted differently than it filtered.
        query.push_str(TAG_CLAUSE);
    }
    if axis != FacetAxis::AiTier {
        match filters.ai_tier {
            Some(AiTierFilter::HandmadeOnly) => query.push_str(" AND i.ai_tier = 'handmade'"),
            Some(AiTierFilter::HumanLed) => {
                query.push_str(" AND i.ai_tier IN ('handmade', 'assisted')");
            }
            None => {}
        }
    }
}

/// Search/browse public items with optional text search, item_type, tag, and price filters.
///
/// Search is tiered (see the `match_tier` notes above the tier/score
/// expressions):
/// - `search_tsv @@ websearch_to_tsquery` is the lexical tier: stemmed,
///   word-order independent, honours quoted phrases and `-exclusions`
/// - `%` (trigram similarity) and `ILIKE` form the fuzzy tier: misspellings,
///   partial words, and substrings a stemmer will not reach
///
/// When a search term is present but no explicit sort is requested, results
/// are ordered by relevance (`match_tier DESC, match_score DESC`) and the UI
/// draws the tier boundary. Otherwise, the caller can choose `most_sold`,
/// `price_asc`, `price_desc`, or the default `newest`.
#[tracing::instrument(skip_all)]
pub async fn discover_items(
    pool: &PgPool,
    filters: &DiscoverFilters<'_>,
    limit: i64,
    offset: i64,
) -> Result<Vec<DbDiscoverItemRow>> {
    let search_term = normalize_search(filters.search);
    let has_search = search_term.is_some();
    let short_query = search_term.as_deref().is_some_and(is_short_query);

    // Build the base query with optional similarity score.
    // For short queries (1-2 chars) use a constant match_score since trigram
    // similarity is unreliable at that length.
    // Use LEFT JOIN with pre-aggregated transaction counts to avoid N+1 subquery per row
    // LEFT JOIN item_tags/tags for primary tag display
    let mut query = if has_search && !short_query {
        format!(
            r"
            SELECT
                i.id,
                i.title,
                i.description,
                i.price_cents,
                i.item_type,
                i.created_at,
                u.username,
                u.settlement_currency,
                p.title as project_title,
                i.sales_count::bigint,
                pt.name as primary_tag_name,
                i.pwyw_enabled,
                i.pwyw_min_cents,
                i.ai_tier,
                {tier},
                {terms},
                {score}
            FROM items i
            JOIN projects p ON i.project_id = p.id
            JOIN users u ON p.user_id = u.id
            LEFT JOIN item_tags pit ON pit.item_id = i.id AND pit.is_primary = true
            LEFT JOIN tags pt ON pt.id = pit.tag_id
            WHERE i.is_public = true AND i.listed = true AND p.is_public = true AND i.scan_status = 'clean' AND u.is_sandbox = FALSE AND i.deleted_at IS NULL
            ",
            tier = match_tier_expr!("i.search_tsv"),
            terms = match_terms_expr!("i.search_tsv"),
            score = match_score_expr!("i.search_tsv"),
        )
    } else if has_search {
        // Short query: constant match_score, skip trigram similarity computation
        String::from(
            r"
            SELECT
                i.id,
                i.title,
                i.description,
                i.price_cents,
                i.item_type,
                i.created_at,
                u.username,
                u.settlement_currency,
                p.title as project_title,
                i.sales_count::bigint,
                pt.name as primary_tag_name,
                i.pwyw_enabled,
                i.pwyw_min_cents,
                i.ai_tier,
                NULL::smallint as match_tier,
                NULL::int as matched_terms,
                NULL::int as query_terms,
                1.0::real as match_score
            FROM items i
            JOIN projects p ON i.project_id = p.id
            JOIN users u ON p.user_id = u.id
            LEFT JOIN item_tags pit ON pit.item_id = i.id AND pit.is_primary = true
            LEFT JOIN tags pt ON pt.id = pit.tag_id
            WHERE i.is_public = true AND i.listed = true AND p.is_public = true AND i.scan_status = 'clean' AND u.is_sandbox = FALSE AND i.deleted_at IS NULL
            ",
        )
    } else {
        String::from(
            r"
            SELECT
                i.id,
                i.title,
                i.description,
                i.price_cents,
                i.item_type,
                i.created_at,
                u.username,
                u.settlement_currency,
                p.title as project_title,
                i.sales_count::bigint,
                pt.name as primary_tag_name,
                i.pwyw_enabled,
                i.pwyw_min_cents,
                i.ai_tier,
                NULL::smallint as match_tier,
                NULL::int as matched_terms,
                NULL::int as query_terms,
                NULL::real as match_score
            FROM items i
            JOIN projects p ON i.project_id = p.id
            JOIN users u ON p.user_id = u.id
            LEFT JOIN item_tags pit ON pit.item_id = i.id AND pit.is_primary = true
            LEFT JOIN tags pt ON pt.id = pit.tag_id
            WHERE i.is_public = true AND i.listed = true AND p.is_public = true AND i.scan_status = 'clean' AND u.is_sandbox = FALSE AND i.deleted_at IS NULL
            ",
        )
    };

    append_item_discover_filters(&mut query, filters, has_search, short_query);

    // Determine ordering
    let order = if has_search
        && (filters.sort_by.is_none() || filters.sort_by == Some(DiscoverSort::Newest))
    {
        // Tier first: a lexical match outranks every fuzzy one, whatever their
        // scores say. The two scores are not on one scale, so this is the only
        // ordering that means anything.
        "match_tier DESC NULLS LAST, match_score DESC NULLS LAST, i.created_at DESC"
    } else {
        match filters.sort_by {
            Some(DiscoverSort::MostSold) => "sales_count DESC, i.created_at DESC",
            Some(DiscoverSort::PriceAsc) => "i.price_cents ASC, i.created_at DESC",
            Some(DiscoverSort::PriceDesc) => "i.price_cents DESC, i.created_at DESC",
            _ => "i.created_at DESC",
        }
    };

    write!(query, " ORDER BY {order} LIMIT $7 OFFSET $8").unwrap();

    let items = bind_item_discover_filters!(
        sqlx::query_as::<_, DbDiscoverItemRow>(&query),
        filters,
        search_term.as_deref()
    )
    .bind(limit)
    .bind(offset)
    .fetch_all(pool)
    .await?;

    Ok(items)
}

/// Count total matching items for pagination (same filters as [`discover_items`]).
#[tracing::instrument(skip_all)]
pub async fn count_discover_items(pool: &PgPool, filters: &DiscoverFilters<'_>) -> Result<i64> {
    let search_term = normalize_search(filters.search);
    let has_search = search_term.is_some();
    let short_query = search_term.as_deref().is_some_and(is_short_query);

    let mut query = String::from(
        r"
        SELECT COUNT(*)
        FROM items i
        JOIN projects p ON i.project_id = p.id
        JOIN users u ON p.user_id = u.id
        WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL AND p.is_public = true AND i.scan_status = 'clean' AND u.is_sandbox = FALSE
        ",
    );

    append_item_discover_filters(&mut query, filters, has_search, short_query);

    let count: i64 =
        bind_item_discover_filters!(sqlx::query_scalar(&query), filters, search_term.as_deref())
            .fetch_one(pool)
            .await?;

    Ok(count)
}

/// Search/browse public projects with optional text search and category filters.
///
/// Same trigram + ILIKE strategy as [`discover_items`], but without price
/// filters. Aggregates a `item_count` via LEFT JOIN so the discover UI can
/// show "N items" per project without a separate query.
#[tracing::instrument(skip_all)]
pub async fn discover_projects(
    pool: &PgPool,
    search: Option<&str>,
    category_slug: Option<&str>,
    sort_by: Option<DiscoverSort>,
    has_source_code: bool,
    limit: i64,
    offset: i64,
) -> Result<Vec<DbDiscoverProjectRow>> {
    let search_term = normalize_search(search);
    let has_search = search_term.is_some();
    let short_query = search_term.as_deref().is_some_and(is_short_query);

    let mut query = if has_search && !short_query {
        format!(
            r"
            SELECT
                p.slug,
                p.title,
                p.description,
                p.project_type,
                p.created_at,
                u.username,
                p.item_count::bigint as item_count,
                {tier},
                {terms},
                {score},
                pc.name as category_name,
                pc.slug as category_slug
            FROM projects p
            JOIN users u ON p.user_id = u.id
            LEFT JOIN project_categories pc ON pc.id = p.category_id
            WHERE p.is_public = true AND u.is_sandbox = FALSE
            ",
            tier = match_tier_expr!("p.search_tsv"),
            terms = match_terms_expr!("p.search_tsv"),
            score = match_score_expr!("p.search_tsv"),
        )
    } else if has_search {
        // Short query: constant match_score, skip trigram similarity computation
        String::from(
            r"
            SELECT
                p.slug,
                p.title,
                p.description,
                p.project_type,
                p.created_at,
                u.username,
                p.item_count::bigint as item_count,
                NULL::smallint as match_tier,
                NULL::int as matched_terms,
                NULL::int as query_terms,
                1.0::real as match_score,
                pc.name as category_name,
                pc.slug as category_slug
            FROM projects p
            JOIN users u ON p.user_id = u.id
            LEFT JOIN project_categories pc ON pc.id = p.category_id
            WHERE p.is_public = true AND u.is_sandbox = FALSE
            ",
        )
    } else {
        String::from(
            r"
            SELECT
                p.slug,
                p.title,
                p.description,
                p.project_type,
                p.created_at,
                u.username,
                p.item_count::bigint as item_count,
                NULL::smallint as match_tier,
                NULL::int as matched_terms,
                NULL::int as query_terms,
                NULL::real as match_score,
                pc.name as category_name,
                pc.slug as category_slug
            FROM projects p
            JOIN users u ON p.user_id = u.id
            LEFT JOIN project_categories pc ON pc.id = p.category_id
            WHERE p.is_public = true AND u.is_sandbox = FALSE
            ",
        )
    };

    if has_search {
        if short_query {
            query.push_str(PROJECT_SEARCH_CLAUSE_SHORT);
        } else {
            query.push_str(PROJECT_SEARCH_CLAUSE);
        }
    }

    if category_slug.is_some() {
        query.push_str(" AND pc.slug = $2");
    }

    if has_source_code {
        query.push_str(" AND EXISTS (SELECT 1 FROM git_repos gr WHERE gr.project_id = p.id)");
    }

    // No GROUP BY: item_count is now a denormalized column on projects (maintained
    // by a trigger), so the query has no aggregate to group (Run 11 Perf SER-3).

    let order = if has_search && (sort_by.is_none() || sort_by == Some(DiscoverSort::Newest)) {
        "match_tier DESC NULLS LAST, match_score DESC NULLS LAST, p.created_at DESC"
    } else {
        match sort_by {
            Some(DiscoverSort::MostSold) => "item_count DESC, p.created_at DESC",
            _ => "p.created_at DESC",
        }
    };

    write!(query, " ORDER BY {order} LIMIT $3 OFFSET $4").unwrap();

    let projects = sqlx::query_as::<_, DbDiscoverProjectRow>(&query)
        .bind(search_term.as_deref().unwrap_or(""))
        .bind(category_slug.unwrap_or(""))
        .bind(limit)
        .bind(offset)
        .fetch_all(pool)
        .await?;

    Ok(projects)
}

/// Count total matching projects for pagination (same filters as [`discover_projects`]).
#[tracing::instrument(skip_all)]
pub async fn count_discover_projects(
    pool: &PgPool,
    search: Option<&str>,
    category_slug: Option<&str>,
    has_source_code: bool,
) -> Result<i64> {
    let search_term = normalize_search(search);
    let has_search = search_term.is_some();
    let short_query = search_term.as_deref().is_some_and(is_short_query);

    let mut query = String::from(
        r"
        SELECT COUNT(*)
        FROM projects p
        JOIN users u ON p.user_id = u.id
        WHERE p.is_public = true AND u.is_sandbox = FALSE
        ",
    );

    if has_search {
        if short_query {
            query.push_str(PROJECT_SEARCH_CLAUSE_SHORT);
        } else {
            query.push_str(PROJECT_SEARCH_CLAUSE);
        }
    }

    if category_slug.is_some() {
        query.push_str(
            " AND EXISTS (SELECT 1 FROM project_categories pc WHERE pc.id = p.category_id AND pc.slug = $2)",
        );
    }

    if has_source_code {
        query.push_str(" AND EXISTS (SELECT 1 FROM git_repos gr WHERE gr.project_id = p.id)");
    }

    let count: i64 = sqlx::query_scalar(&query)
        .bind(search_term.as_deref().unwrap_or(""))
        .bind(category_slug.unwrap_or(""))
        .fetch_one(pool)
        .await?;

    Ok(count)
}

/// Get item type counts for discover page (items mode).
///
/// Counts what each type *would* yield, so `filters.item_type` is omitted
/// (see [`FacetAxis`]). Every other active filter applies.
#[tracing::instrument(skip_all)]
pub async fn get_item_type_counts(
    pool: &PgPool,
    filters: &DiscoverFilters<'_>,
) -> Result<Vec<DbItemTypeCount>> {
    let search_term = normalize_search(filters.search);
    let has_search = search_term.is_some();
    let short_query = search_term.as_deref().is_some_and(is_short_query);

    let mut query = String::from(
        r"
        SELECT i.item_type as category, COUNT(*) as count
        FROM items i
        JOIN projects p ON i.project_id = p.id
        JOIN users u ON p.user_id = u.id
        ",
    );
    query.push_str(ITEM_VISIBILITY_WHERE);

    append_facet_filters(
        &mut query,
        filters,
        FacetAxis::ItemType,
        has_search,
        short_query,
    );

    query.push_str(" GROUP BY i.item_type ORDER BY count DESC");

    let counts = bind_item_discover_filters!(
        sqlx::query_as::<_, DbItemTypeCount>(&query),
        filters,
        search_term.as_deref()
    )
    .fetch_all(pool)
    .await?;

    Ok(counts)
}

/// The discover price buckets: label, inclusive lower bound, inclusive upper
/// bound (`None` = unbounded).
///
/// The single source of truth for both the `COUNT(*) FILTER` clauses that
/// produce the sidebar numbers and the `min_price`/`max_price` links those
/// numbers are clickable through. They were duplicated across two files and
/// kept in step only by a comment at each site; a bucket whose bounds drifted
/// from its own count would display a number its link could not reproduce.
pub const PRICE_BUCKETS: [(&str, i32, Option<i32>); 5] = [
    ("Free", 0, Some(0)),
    ("Under $25", 1, Some(2499)),
    ("$25-50", 2500, Some(4999)),
    ("$50-100", 5000, Some(9999)),
    ("$100+", 10000, None),
];

/// The `FILTER (WHERE ...)` predicate for one bucket.
fn price_bucket_predicate(min: i32, max: Option<i32>) -> String {
    match max {
        Some(max) => format!("i.price_cents >= {min} AND i.price_cents <= {max}"),
        None => format!("i.price_cents >= {min}"),
    }
}

/// Get price range counts for the discover page sidebar (items mode only).
///
/// Buckets are in cents: free (0), under $25 (1..2499), $25-$50 (2500..4999),
/// $50-$100 (5000..9999), over $100 (10000+). Uses PostgreSQL `FILTER (WHERE ...)`
/// to compute all five counts in a single table scan.
#[tracing::instrument(skip_all)]
pub async fn get_price_range_counts(
    pool: &PgPool,
    filters: &DiscoverFilters<'_>,
) -> Result<Vec<i64>> {
    let search_term = normalize_search(filters.search);
    let has_search = search_term.is_some();
    let short_query = search_term.as_deref().is_some_and(is_short_query);

    // One aggregate per bucket, generated from PRICE_BUCKETS so the counts and
    // the bucket links can never describe different ranges. Bounds are integer
    // literals from a const, not user input.
    let selects: Vec<String> = PRICE_BUCKETS
        .iter()
        .enumerate()
        .map(|(i, (_, min, max))| {
            format!(
                "COUNT(*) FILTER (WHERE {}) as bucket_{i}",
                price_bucket_predicate(*min, *max)
            )
        })
        .collect();

    let mut query = format!(
        r"
        SELECT
            {}
        FROM items i
        JOIN projects p ON i.project_id = p.id
        JOIN users u ON p.user_id = u.id
        ",
        selects.join(",\n            ")
    );
    query.push_str(ITEM_VISIBILITY_WHERE);

    append_facet_filters(
        &mut query,
        filters,
        FacetAxis::Price,
        has_search,
        short_query,
    );

    let row = bind_item_discover_filters!(sqlx::query(&query), filters, search_term.as_deref())
        .fetch_one(pool)
        .await?;

    // Positional read, in PRICE_BUCKETS order.
    let counts = (0..PRICE_BUCKETS.len())
        .map(|i| row.try_get::<Option<i64>, _>(i).ok().flatten().unwrap_or(0))
        .collect();

    Ok(counts)
}

/// Get AI tier counts for the discover page sidebar (items mode only).
#[tracing::instrument(skip_all)]
pub async fn get_ai_tier_counts(
    pool: &PgPool,
    filters: &DiscoverFilters<'_>,
) -> Result<Vec<DbItemTypeCount>> {
    let search_term = normalize_search(filters.search);
    let has_search = search_term.is_some();
    let short_query = search_term.as_deref().is_some_and(is_short_query);

    let mut query = String::from(
        r"
        SELECT i.ai_tier as category, COUNT(*) as count
        FROM items i
        JOIN projects p ON i.project_id = p.id
        JOIN users u ON p.user_id = u.id
        ",
    );
    query.push_str(ITEM_VISIBILITY_WHERE);

    append_facet_filters(
        &mut query,
        filters,
        FacetAxis::AiTier,
        has_search,
        short_query,
    );

    query.push_str(" GROUP BY i.ai_tier ORDER BY count DESC");

    let counts = bind_item_discover_filters!(
        sqlx::query_as::<_, DbItemTypeCount>(&query),
        filters,
        search_term.as_deref()
    )
    .fetch_all(pool)
    .await?;

    Ok(counts)
}

/// A search suggestion with a category label (tag, project, or creator).
#[derive(Debug, FromRow)]
pub struct DbSearchSuggestion {
    pub label: String,
    pub category: String,
    pub url: String,
}

/// Return combined search suggestions from tags, projects, and creators.
/// Uses ILIKE prefix match for fast results, limited to 8 total.
#[tracing::instrument(skip_all)]
pub async fn search_suggestions(pool: &PgPool, query: &str) -> Result<Vec<DbSearchSuggestion>> {
    let q = query.trim();
    if q.is_empty() {
        return Ok(vec![]);
    }

    let pattern = format!(
        "{}%",
        q.replace('\\', "\\\\")
            .replace('%', "\\%")
            .replace('_', "\\_")
    );

    let rows = sqlx::query_as::<_, DbSearchSuggestion>(
        r"
        (
            SELECT name AS label, 'tag' AS category, '/discover?mode=items&tag=' || slug AS url
            FROM tags
            WHERE name ILIKE $1
            ORDER BY name
            LIMIT 3
        )
        UNION ALL
        (
            SELECT title AS label, 'project' AS category, '/p/' || slug AS url
            FROM projects
            WHERE is_public = true AND title ILIKE $1
            ORDER BY title
            LIMIT 3
        )
        UNION ALL
        (
            SELECT username AS label, 'creator' AS category, '/u/' || username AS url
            FROM users
            WHERE is_sandbox = false AND suspended_at IS NULL AND deactivated_at IS NULL AND username ILIKE $1
            ORDER BY username
            LIMIT 2
        )
        ",
    )
    .bind(&pattern)
    .fetch_all(pool)
    .await?;

    Ok(rows)
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn normalize_search_none() {
        assert_eq!(normalize_search(None), None);
    }

    #[test]
    fn normalize_search_empty() {
        assert_eq!(normalize_search(Some("")), None);
    }

    #[test]
    fn normalize_search_whitespace_only() {
        assert_eq!(normalize_search(Some("   ")), None);
    }

    #[test]
    fn normalize_search_trims() {
        assert_eq!(
            normalize_search(Some("  hello  ")),
            Some("hello".to_string())
        );
    }

    #[test]
    fn normalize_search_truncates_long() {
        let long = "a".repeat(300);
        let result = normalize_search(Some(&long)).unwrap();
        assert_eq!(result.len(), MAX_SEARCH_LEN);
    }

    #[test]
    fn normalize_search_truncates_at_char_boundary() {
        // Multi-byte chars: each is 2 bytes. 150 chars = 300 bytes.
        let long: String = std::iter::repeat_n('\u{00E9}', 150).collect();
        let result = normalize_search(Some(&long)).unwrap();
        assert!(result.len() <= MAX_SEARCH_LEN);
        // Must end at a valid char boundary (no panic on indexing)
        assert!(result.is_char_boundary(result.len()));
    }

    #[test]
    fn normalize_search_exact_limit() {
        let exact = "b".repeat(MAX_SEARCH_LEN);
        assert_eq!(normalize_search(Some(&exact)), Some(exact));
    }

    #[test]
    fn is_short_query_empty() {
        assert!(is_short_query(""));
    }

    #[test]
    fn is_short_query_two_chars() {
        assert!(is_short_query("ab"));
    }

    #[test]
    fn is_short_query_three_chars() {
        assert!(!is_short_query("abc"));
    }

    #[test]
    fn append_filters_no_filters() {
        let filters = DiscoverFilters {
            search: None,
            item_types: &[],
            tags: &[],
            min_price: None,
            max_price: None,
            sort_by: None,
            ai_tier: None,
        };
        let mut q = String::from("SELECT 1 WHERE true");
        append_item_discover_filters(&mut q, &filters, false, false);
        assert_eq!(q, "SELECT 1 WHERE true");
    }

    #[test]
    fn append_filters_with_item_type() {
        let filters = DiscoverFilters {
            search: None,
            item_types: &[ItemType::Audio],
            tags: &[],
            min_price: None,
            max_price: None,
            sort_by: None,
            ai_tier: None,
        };
        let mut q = String::new();
        append_item_discover_filters(&mut q, &filters, false, false);
        assert!(q.contains("i.item_type = ANY($2)"));
    }

    #[test]
    fn append_filters_tags_match_slug_or_subtree() {
        let tags = vec!["audio.genre".to_string()];
        let filters = DiscoverFilters {
            search: None,
            item_types: &[],
            tags: &tags,
            min_price: None,
            max_price: None,
            sort_by: None,
            ai_tier: None,
        };
        let mut q = String::new();
        append_item_discover_filters(&mut q, &filters, false, false);
        // Exact slug OR descendant path, both array-bound so the facet can
        // hold several selections at once.
        assert!(q.contains("t2.slug = ANY($5)"));
        assert!(q.contains("t2.path LIKE ANY($6)"));
    }

    #[test]
    fn tag_descendant_patterns_escape_like_metacharacters() {
        // tagtree owns the escaping; pin that we actually route through it,
        // since an unescaped `_` in a slug would silently widen the match.
        let tags = vec!["audio.genre_x".to_string()];
        let filters = DiscoverFilters {
            search: None,
            item_types: &[],
            tags: &tags,
            min_price: None,
            max_price: None,
            sort_by: None,
            ai_tier: None,
        };
        let patterns = filters.tag_descendant_patterns();
        assert_eq!(patterns.len(), 1);
        assert!(
            patterns[0].contains("\\_"),
            "underscore must be escaped, got {:?}",
            patterns[0]
        );
        assert!(patterns[0].ends_with(".%"));
    }

    #[test]
    fn append_filters_search_uses_short_clause() {
        let filters = DiscoverFilters {
            search: Some("ab"),
            item_types: &[],
            tags: &[],
            min_price: None,
            max_price: None,
            sort_by: None,
            ai_tier: None,
        };
        let mut q = String::new();
        append_item_discover_filters(&mut q, &filters, true, true);
        assert!(q.contains("ILIKE"));
        assert!(!q.contains("i.title % $1"));
    }

    #[test]
    fn append_filters_search_uses_trigram_clause() {
        let filters = DiscoverFilters {
            search: Some("hello"),
            item_types: &[],
            tags: &[],
            min_price: None,
            max_price: None,
            sort_by: None,
            ai_tier: None,
        };
        let mut q = String::new();
        append_item_discover_filters(&mut q, &filters, true, false);
        assert!(q.contains("i.title % $1"));
        // The word branch has to be OR'd in alongside the fuzzy one, or a
        // stemmed-only match ("loops" for "looping") never reaches the result
        // set and the tier it would have been sorted into stays empty.
        assert!(q.contains("i.search_tsv @@ mnw_any_term_query($1)"));
        // Membership must be ANY typed word, never all of them. websearch_to_tsquery
        // ANDs its terms, which is the cliff this replaced: four of five words
        // present ranked with none of them, under a heading that said so falsely.
        assert!(!q.contains("websearch_to_tsquery"));
    }

    /// The tier values live twice: as SQL literals inside `match_tier_expr!`,
    /// and as the constants Rust compares against. Nothing links them, so a
    /// change to one silently inverts the ordering rather than failing.
    #[test]
    fn match_tier_literals_match_constants() {
        let expr = match_tier_expr!("i.search_tsv");
        assert!(
            expr.contains(&format!(
                "THEN {MATCH_TIER_LEXICAL} ELSE {MATCH_TIER_FUZZY} END"
            )),
            "tier literals in the SQL drifted from the constants: {expr}"
        );
        // The cut is "holds any typed word", not "holds all of them". Anything
        // stricter puts partial matches under a heading that denies they matched
        // words at all.
        assert!(
            expr.contains("mnw_exact_terms(i.search_tsv, $1) > 0"),
            "tier is no longer a coverage cut: {expr}"
        );
        // Both sides are constants, so this is a compile-time check rather than
        // a runtime one: a bad edit fails to build instead of failing this test.
        const _: () = assert!(
            MATCH_TIER_LEXICAL > MATCH_TIER_FUZZY,
            "ORDER BY match_tier DESC puts lexical first only if it is the larger value"
        );
    }

    /// The fuzzy arm calls a function defined in a migration, so a rename on
    /// either side only fails at runtime, against the busiest public page.
    /// Pin the name here and the pairing is at least stated in one place.
    /// The label's denominator and the tier's numerator must come from one
    /// place. Counting query words in Rust off the raw string would disagree
    /// with Postgres the moment stemming or stopword removal applied, and the
    /// row would read "Matched 3 of 7 words" against a 5-word tsquery.
    #[test]
    fn label_counts_come_from_the_same_stemmer_as_the_tier() {
        let terms = match_terms_expr!("i.search_tsv");
        assert!(terms.contains("mnw_exact_terms(i.search_tsv, $1)"));
        assert!(terms.contains("cardinality(mnw_query_terms($1))"));
        assert!(match_tier_expr!("i.search_tsv").contains("mnw_exact_terms"));
    }

    #[test]
    fn fuzzy_arm_calls_the_word_match_function() {
        let expr = match_score_expr!("i.search_tsv");
        assert!(
            expr.contains("mnw_word_match_score(i.search_tsv, $1)"),
            "fuzzy ranking no longer calls mnw_word_match_score (migrations/179): {expr}"
        );
        // Whole-phrase similarity is what 179 replaced. If it comes back here,
        // the long-query case it fails on is back too.
        assert!(!expr.contains("similarity("));
    }

    /// A tier is only meaningful when both kinds of match can occur. The short
    /// path is ILIKE-only, so it must not claim one.
    #[test]
    fn short_query_rows_are_untiered() {
        assert!(!ITEM_SEARCH_CLAUSE_SHORT.contains("search_tsv"));
        assert!(!PROJECT_SEARCH_CLAUSE_SHORT.contains("search_tsv"));
    }

    #[test]
    fn append_filters_handmade_only_narrows_to_one_tier() {
        let filters = DiscoverFilters {
            search: None,
            item_types: &[],
            tags: &[],
            min_price: None,
            max_price: None,
            sort_by: None,
            ai_tier: Some(AiTierFilter::HandmadeOnly),
        };
        let mut q = String::new();
        append_item_discover_filters(&mut q, &filters, false, false);
        assert!(q.contains("i.ai_tier = 'handmade'"));
        assert!(!q.contains("assisted"));
    }

    #[test]
    fn append_filters_human_led_includes_handmade_and_assisted() {
        // Locks the policy commitment that Human-led covers BOTH handmade
        // and assisted. A future rename of the literals or a swap to a
        // single-tier match would silently weaken the filter.
        let filters = DiscoverFilters {
            search: None,
            item_types: &[],
            tags: &[],
            min_price: None,
            max_price: None,
            sort_by: None,
            ai_tier: Some(AiTierFilter::HumanLed),
        };
        let mut q = String::new();
        append_item_discover_filters(&mut q, &filters, false, false);
        assert!(q.contains("i.ai_tier IN ('handmade', 'assisted')"));
        assert!(!q.contains("generated"));
    }

    #[test]
    fn ai_tier_filter_round_trip() {
        // Parses the query-string value the route receives back into the
        // typed enum the SQL builder expects.
        assert_eq!(
            "handmade_only".parse::<AiTierFilter>().unwrap(),
            AiTierFilter::HandmadeOnly
        );
        assert_eq!(
            "human_led".parse::<AiTierFilter>().unwrap(),
            AiTierFilter::HumanLed
        );
        assert!("everything".parse::<AiTierFilter>().is_err());
        assert!("assisted".parse::<AiTierFilter>().is_err());
        assert_eq!(AiTierFilter::HumanLed.to_string(), "human_led");
        assert_eq!(AiTierFilter::HandmadeOnly.label(), "Handmade only");
    }
}