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

use sqlx::{FromRow, PgPool};

use super::enums::{AiTierFilter, DiscoverSort, ItemType};
use super::models::*;
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>,
    pub item_type: Option<ItemType>,
    pub tag: Option<&'a str>,
    pub min_price: Option<i32>,
    pub max_price: Option<i32>,
    pub sort_by: Option<DiscoverSort>,
    pub ai_tier: Option<AiTierFilter>,
}

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

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

const PROJECT_SEARCH_CLAUSE: &str = r#" AND (
                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, '\', '\\'), '%', '\%'), '_', '\_') || '%'
            )"#;

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

/// 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.
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(|(i, c)| i + c.len_utf8())
            .unwrap_or(MAX_SEARCH_LEN);
        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).
fn is_short_query(term: &str) -> bool {
    term.trim().len() <= 2
}

/// Append discover-item filter clauses to a dynamic query.
/// Parameter positions: $1=search, $2=item_type, $3=min_price, $4=max_price, $5=tag, $6=ai_tier.
///
/// 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_type.is_some() {
        query.push_str(" AND i.item_type = $2");
    }
    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.tag.is_some() {
        // Match exact slug or any descendant (e.g. "audio.genre" matches "audio.genre.electronic")
        query.push_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 = $5 OR t2.path LIKE $5 || '.%')
            )"#,
        );
    }
    // 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 5 discover-filter parameters ($1-$5) to a sqlx query.
/// The AI-tier filter is appended to the WHERE as a literal SQL fragment,
/// so it occupies no bind position.
macro_rules! bind_item_discover_filters {
    ($q:expr, $filters:expr, $search_term:expr) => {
        $q.bind($search_term.unwrap_or(""))
            .bind($filters.item_type.map(|t| t.to_string()).unwrap_or_default())
            .bind($filters.min_price.unwrap_or(0))
            .bind($filters.max_price.unwrap_or(i32::MAX))
            .bind($filters.tag.unwrap_or(""))
    };
}

/// Search/browse public items with optional text search, item_type, tag, and price filters.
///
/// Uses PostgreSQL `pg_trgm` for fuzzy text matching. The search strategy:
/// - `%` (trigram similarity operator) catches misspellings and near-matches
/// - `ILIKE` fallback catches exact substrings that trigrams might miss for short queries
/// - `similarity(description) * 0.5` weights description matches lower than title matches
///   so that a title hit always ranks above a description-only hit
///
/// When a search term is present but no explicit sort is requested, results
/// are ordered by relevance (`match_score DESC`). 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 {
        String::from(
            r#"
            SELECT
                i.id,
                i.title,
                i.description,
                i.price_cents,
                i.item_type,
                i.created_at,
                u.username,
                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,
                GREATEST(
                    similarity(i.title, $1),
                    similarity(COALESCE(i.description, ''), $1) * 0.5
                )::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 != 'quarantined' AND u.is_sandbox = FALSE AND i.deleted_at IS NULL
            "#,
        )
    } 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,
                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,
                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 != 'quarantined' 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,
                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::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 != 'quarantined' 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)) {
        "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",
        }
    };

    query.push_str(&format!(" ORDER BY {} LIMIT $6 OFFSET $7", order));

    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 != 'quarantined' 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 {
        String::from(
            r#"
            SELECT
                p.slug,
                p.title,
                p.description,
                p.project_type,
                p.created_at,
                u.username,
                COUNT(i.id) FILTER (WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL) as item_count,
                GREATEST(
                    similarity(p.title, $1),
                    similarity(COALESCE(p.description, ''), $1) * 0.5
                )::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 items i ON i.project_id = p.id
            LEFT JOIN project_categories pc ON pc.id = p.category_id
            WHERE p.is_public = true AND u.is_sandbox = FALSE
            "#,
        )
    } 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,
                COUNT(i.id) FILTER (WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL) as item_count,
                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 items i ON i.project_id = p.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,
                COUNT(i.id) FILTER (WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL) as item_count,
                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 items i ON i.project_id = p.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)");
    }

    query.push_str(" GROUP BY p.id, u.username, pc.name, pc.slug");

    let order = if has_search && (sort_by.is_none() || sort_by == Some(DiscoverSort::Newest)) {
        "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",
        }
    };

    query.push_str(&format!(" ORDER BY {} LIMIT $3 OFFSET $4", order));

    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).
#[tracing::instrument(skip_all)]
pub async fn get_item_type_counts(
    pool: &PgPool,
    search: Option<&str>,
    tag: Option<&str>,
    min_price: Option<i32>,
    max_price: Option<i32>,
) -> Result<Vec<DbItemTypeCount>> {
    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 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
        WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL AND p.is_public = true AND i.scan_status != 'quarantined' AND u.is_sandbox = FALSE
        "#,
    );

    if has_search {
        if short_query {
            query.push_str(ITEM_SEARCH_CLAUSE_SHORT);
        } else {
            query.push_str(ITEM_SEARCH_CLAUSE);
        }
    }

    if tag.is_some() {
        query.push_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 = $2 OR t2.parent_id = (SELECT id FROM tags WHERE slug = $2))
            )"#,
        );
    }

    if min_price.is_some() {
        query.push_str(" AND i.price_cents >= $3");
    }

    if max_price.is_some() {
        query.push_str(" AND i.price_cents <= $4");
    }

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

    let counts = sqlx::query_as::<_, DbItemTypeCount>(&query)
        .bind(search_term.as_deref().unwrap_or(""))
        .bind(tag.unwrap_or(""))
        .bind(min_price.unwrap_or(0))
        .bind(max_price.unwrap_or(i32::MAX))
        .fetch_all(pool)
        .await?;

    Ok(counts)
}

/// 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,
    search: Option<&str>,
    item_type: Option<ItemType>,
    tag: Option<&str>,
) -> Result<DbPriceRangeCounts> {
    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(*) FILTER (WHERE i.price_cents = 0) as free,
            COUNT(*) FILTER (WHERE i.price_cents > 0 AND i.price_cents < 2500) as under_25,
            COUNT(*) FILTER (WHERE i.price_cents >= 2500 AND i.price_cents < 5000) as range_25_50,
            COUNT(*) FILTER (WHERE i.price_cents >= 5000 AND i.price_cents < 10000) as range_50_100,
            COUNT(*) FILTER (WHERE i.price_cents >= 10000) as over_100
        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 != 'quarantined' AND u.is_sandbox = FALSE
        "#,
    );

    if has_search {
        if short_query {
            query.push_str(ITEM_SEARCH_CLAUSE_SHORT);
        } else {
            query.push_str(ITEM_SEARCH_CLAUSE);
        }
    }

    if item_type.is_some() {
        query.push_str(" AND i.item_type = $2");
    }

    if tag.is_some() {
        query.push_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 = $3 OR t2.parent_id = (SELECT id FROM tags WHERE slug = $3))
            )"#,
        );
    }

    #[derive(FromRow)]
    struct PriceRow {
        free: Option<i64>,
        under_25: Option<i64>,
        range_25_50: Option<i64>,
        range_50_100: Option<i64>,
        over_100: Option<i64>,
    }

    let row = sqlx::query_as::<_, PriceRow>(&query)
        .bind(search_term.as_deref().unwrap_or(""))
        .bind(item_type.map(|t| t.to_string()).unwrap_or_default())
        .bind(tag.unwrap_or(""))
        .fetch_one(pool)
        .await?;

    Ok(DbPriceRangeCounts {
        free: row.free.unwrap_or(0),
        under_25: row.under_25.unwrap_or(0),
        range_25_50: row.range_25_50.unwrap_or(0),
        range_50_100: row.range_50_100.unwrap_or(0),
        over_100: row.over_100.unwrap_or(0),
    })
}

/// 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,
    search: Option<&str>,
    item_type: Option<ItemType>,
    tag: Option<&str>,
) -> Result<Vec<DbItemTypeCount>> {
    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 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
        WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL AND p.is_public = true AND i.scan_status != 'quarantined' AND u.is_sandbox = FALSE
        "#,
    );

    if has_search {
        if short_query {
            query.push_str(ITEM_SEARCH_CLAUSE_SHORT);
        } else {
            query.push_str(ITEM_SEARCH_CLAUSE);
        }
    }

    if item_type.is_some() {
        query.push_str(" AND i.item_type = $2");
    }

    if tag.is_some() {
        query.push_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 = $3 OR t2.parent_id = (SELECT id FROM tags WHERE slug = $3))
            )"#,
        );
    }

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

    let counts = sqlx::query_as::<_, DbItemTypeCount>(&query)
        .bind(search_term.as_deref().unwrap_or(""))
        .bind(item_type.map(|t| t.to_string()).unwrap_or_default())
        .bind(tag.unwrap_or(""))
        .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_type: None, tag: None,
            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_type: Some(ItemType::Audio), tag: None,
            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 = $2"));
    }

    #[test]
    fn append_filters_search_uses_short_clause() {
        let filters = DiscoverFilters {
            search: Some("ab"), item_type: None, tag: None,
            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_type: None, tag: None,
            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"));
    }

    #[test]
    fn append_filters_handmade_only_narrows_to_one_tier() {
        let filters = DiscoverFilters {
            search: None, item_type: None, tag: None,
            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_type: None, tag: None,
            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");
    }
}