1import { sql } from 'drizzle-orm';2import { getDb } from '@rareindex/database';3import { descendants, getCategory, CATEGORIES } from '@rareindex/taxonomy';4import { parseQuery, toTsQuery, type ParsedQuery } from './parse.js';56export { parseQuery, toTsQuery, type ParsedQuery };78/**9 * Hybrid asset search (§138): lexical (tsvector, weighted A/B/C) + trigram similarity on the title10 * + popularity (sales count, watchers, observations) + recency, with structured filters parsed11 * from the natural-language query. Runs entirely in PostgreSQL; vector re-ranking can be added12 * on top when `asset_embeddings` is populated.13 */14export interface SearchOptions {15 limit?: number;16 offset?: number;17 categorySlug?: string | null;18 grader?: string | null;19 grade?: string | null;20 priceMinUsd?: number | null;21 priceMaxUsd?: number | null;22 yearFrom?: number | null;23 yearTo?: number | null;24}2526export interface SearchHit {27 id: string;28 slug: string;29 title: string;30 name: string;31 categorySlug: string;32 familySlug: string;33 setName: string | null;34 number: string | null;35 year: number | null;36 variant: string | null;37 heroImageUrl: string | null;38 rivUsd: number | null;39 rivConfidence: number | null;40 latestSaleUsd: number | null;41 change30d: number | null;42 salesCount: number;43 liquidityScore: number | null;44 score: number;45}4647export interface SearchResult {48 parsed: ParsedQuery;49 hits: SearchHit[];50 total: number;51 tookMs: number;52 categories: Array<{ slug: string; name: string; count: number }>;53}5455export async function searchAssets(query: string, opts: SearchOptions = {}): Promise<SearchResult> {56 const t0 = Date.now();57 const parsed = parseQuery(query);58 const limit = Math.min(Math.max(opts.limit ?? 24, 1), 100);59 const offset = Math.max(opts.offset ?? 0, 0);60 const db = getDb();6162 const tsq = toTsQuery(parsed.text || parsed.raw);63 const likeText = (parsed.text || parsed.raw).trim();64 const catSlug = opts.categorySlug ?? (parsed.categorySlugs.length === 1 ? parsed.categorySlugs[0]! : null);65 const catSlugs = catSlug ? [catSlug, ...descendants(catSlug)] : parsed.categorySlugs.length > 1 ? parsed.categorySlugs.flatMap((s) => [s, ...descendants(s)]) : null;66 const grader = opts.grader ?? parsed.grader;67 const grade = opts.grade ?? parsed.grade;68 const priceMin = opts.priceMinUsd ?? (parsed.currency === 'USD' || parsed.currency === null ? parsed.priceMin : null);69 const priceMax = opts.priceMaxUsd ?? (parsed.currency === 'USD' || parsed.currency === null ? parsed.priceMax : null);7071 const conditions = [sql`true`];72 if (tsq && likeText.length >= 2) {73 conditions.push(sql`(a.search @@ to_tsquery('simple', ${tsq}) OR a.title % ${likeText} OR a.title ILIKE ${'%' + likeText + '%'})`);74 }75 if (catSlugs?.length) conditions.push(sql`a.category_slug IN ${catSlugs}`);76 if (opts.yearFrom) conditions.push(sql`a.year >= ${opts.yearFrom}`);77 if (opts.yearTo) conditions.push(sql`a.year <= ${opts.yearTo}`);78 if (priceMin !== null && priceMin !== undefined) conditions.push(sql`s.riv_usd >= ${priceMin}`);79 if (priceMax !== null && priceMax !== undefined) conditions.push(sql`s.riv_usd <= ${priceMax}`);80 if (grader && grader !== 'raw') {81 conditions.push(sql`EXISTS (SELECT 1 FROM asset_variants v WHERE v.asset_id = a.id AND v.grader = ${grader} ${grade ? sql`AND v.grade = ${grade}` : sql``})`);82 }83 const where = sql.join(conditions, sql` AND `);8485 const rank = tsq86 ? sql`(coalesce(ts_rank_cd(a.search, to_tsquery('simple', ${tsq})), 0) * 4 + coalesce(similarity(a.title, ${likeText}), 0) * 3 + ln(1 + coalesce(s.sales_count, 0)) * 0.35 + ln(1 + coalesce(s.observations_count, 0)) * 0.1 + ln(1 + coalesce(s.watchers, 0)) * 0.2 + (CASE WHEN a.year = ${parsed.year ?? -1} THEN 0.8 ELSE 0 END) + (CASE WHEN s.riv_usd IS NOT NULL THEN 0.3 ELSE 0 END))`87 : sql`(ln(1 + coalesce(s.sales_count, 0)) * 0.35 + ln(1 + coalesce(s.watchers, 0)) * 0.2 + (CASE WHEN s.riv_usd IS NOT NULL THEN 0.3 ELSE 0 END))`;8889 const rows = (await db.execute(sql`90 SELECT a.id, a.slug, a.title, a.name, a.category_slug, a.family_slug, a.set_name, a.number, a.year, a.variant, a.hero_image_url,91 s.riv_usd, s.riv_confidence, s.latest_sale_usd, s.change_30d, coalesce(s.sales_count, 0) AS sales_count, s.liquidity_score,92 ${rank} AS score, count(*) OVER() AS total93 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id94 WHERE ${where}95 ORDER BY score DESC, a.updated_at DESC96 LIMIT ${limit} OFFSET ${offset}97 `)) as unknown as Array<Record<string, unknown>>;9899 const hits: SearchHit[] = rows.map((r) => ({100 id: String(r.id),101 slug: String(r.slug),102 title: String(r.title),103 name: String(r.name),104 categorySlug: String(r.category_slug),105 familySlug: String(r.family_slug),106 setName: (r.set_name as string | null) ?? null,107 number: (r.number as string | null) ?? null,108 year: r.year === null ? null : Number(r.year),109 variant: (r.variant as string | null) ?? null,110 heroImageUrl: (r.hero_image_url as string | null) ?? null,111 rivUsd: r.riv_usd === null || r.riv_usd === undefined ? null : Number(r.riv_usd),112 rivConfidence: r.riv_confidence === null || r.riv_confidence === undefined ? null : Number(r.riv_confidence),113 latestSaleUsd: r.latest_sale_usd === null || r.latest_sale_usd === undefined ? null : Number(r.latest_sale_usd),114 change30d: r.change_30d === null || r.change_30d === undefined ? null : Number(r.change_30d),115 salesCount: Number(r.sales_count ?? 0),116 liquidityScore: r.liquidity_score === null || r.liquidity_score === undefined ? null : Number(r.liquidity_score),117 score: Number(r.score ?? 0),118 }));119 const total = rows.length ? Number(rows[0]!.total) : 0;120121 // Category facets for the current lexical match (cheap, capped)122 let categories: SearchResult['categories'] = [];123 if (tsq && likeText.length >= 2) {124 const facets = (await db.execute(sql`125 SELECT a.category_slug AS slug, count(*)::int AS count FROM assets a126 WHERE (a.search @@ to_tsquery('simple', ${tsq}) OR a.title % ${likeText})127 GROUP BY a.category_slug ORDER BY count DESC LIMIT 8128 `)) as unknown as Array<{ slug: string; count: number }>;129 categories = facets.map((f) => ({ slug: f.slug, name: getCategory(f.slug)?.name ?? f.slug, count: Number(f.count) }));130 }131132 return { parsed, hits, total, tookMs: Date.now() - t0, categories };133}134135export interface Suggestion {136 type: 'asset' | 'category' | 'set' | 'brand' | 'index';137 label: string;138 sublabel: string | null;139 href: string;140}141142/** Instant suggestions for the header search (prefix / trigram). */143export async function suggest(prefix: string, limit = 8): Promise<Suggestion[]> {144 const q = prefix.trim();145 if (q.length < 2) return [];146 const db = getDb();147 const out: Suggestion[] = [];148 const lower = q.toLowerCase();149 for (const c of CATEGORIES) {150 if (c.name.toLowerCase().startsWith(lower) || c.name.toLowerCase().includes(` ${lower}`)) {151 out.push({ type: 'category', label: c.name, sublabel: c.level === 0 ? 'Category' : `${getCategory(c.familySlug)?.name ?? ''} · Category`, href: `/markets/${c.slug}` });152 if (out.length >= 3) break;153 }154 }155 const tsq = toTsQuery(q);156 const rows = (await db.execute(sql`157 SELECT a.slug, a.title, a.category_slug, s.riv_usd158 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id159 WHERE a.title ILIKE ${q + '%'} OR a.title ILIKE ${'% ' + q + '%'} ${tsq ? sql`OR a.search @@ to_tsquery('simple', ${tsq})` : sql``}160 ORDER BY coalesce(s.sales_count, 0) DESC, similarity(a.title, ${q}) DESC161 LIMIT ${limit}162 `)) as unknown as Array<{ slug: string; title: string; category_slug: string; riv_usd: number | null }>;163 for (const r of rows) {164 out.push({ type: 'asset', label: r.title, sublabel: getCategory(r.category_slug)?.name ?? r.category_slug, href: `/asset/${r.slug}` });165 }166 const sets = (await db.execute(sql`SELECT slug, name, category_slug FROM sets WHERE name ILIKE ${q + '%'} ORDER BY name LIMIT 3`)) as unknown as Array<{ slug: string; name: string; category_slug: string }>;167 for (const s of sets) out.push({ type: 'set', label: s.name, sublabel: `Set · ${getCategory(s.category_slug)?.name ?? s.category_slug}`, href: `/set/${s.slug}` });168 return out.slice(0, limit + 3);169}170