spb/cancerindex

1import 'server-only';2import { run, sql, safe } from '@/lib/db';3import { normalizeLabel } from '@cancerindex/shared';4import type { SearchHit } from '@/components/layout/command-palette';5import { matchFromTier, sortHits } from '@/lib/search-util';67/**8 * Entity search (§294, §312). Ordering: exact canonical name > exact alias/identifier > prefix >9 * trigram similarity. Each entity type is queried separately, then merged by tier and score.10 */11export async function searchEntities(qRaw: string, limit = 20): Promise<SearchHit[]> {12  const q = qRaw.trim();13  if (q.length < 2) return [];14  const norm = normalizeLabel(q);15  const lower = q.toLowerCase();16  const prefix = `${lower}%`;17  const upper = q.toUpperCase();1819  type Row = { type: SearchHit['type']; id: string; title: string; subtitle: string | null; href: string; tier: number; score: number };20  const rows = await safe(21    () =>22      run<Row>(sql`23        WITH cand AS (24          -- cancers: name / alias / codes25          SELECT 'cancer'::text AS type, c.id, c.canonical_name AS title, c.entity_type || coalesce(' · ' || c.primary_oncotree_code, '') AS subtitle, '/cancer/' || c.slug AS href,26            CASE WHEN lower(c.canonical_name) = ${lower} THEN 027                 WHEN EXISTS (SELECT 1 FROM cancer_aliases a WHERE a.cancer_id = c.id AND a.normalized = ${norm}) THEN 128                 WHEN EXISTS (SELECT 1 FROM cancer_codes k WHERE k.cancer_id = c.id AND upper(k.code) = ${upper}) OR c.id = ${upper} THEN 129                 WHEN lower(c.canonical_name) LIKE ${prefix} THEN 230                 WHEN EXISTS (SELECT 1 FROM cancer_aliases a WHERE a.cancer_id = c.id AND a.normalized LIKE ${norm + '%'}) THEN 231                 ELSE 3 END AS tier,32            greatest(similarity(c.canonical_name, ${q}), (SELECT coalesce(max(similarity(a.alias, ${q})), 0) FROM cancer_aliases a WHERE a.cancer_id = c.id)) AS score33          FROM cancers c34          WHERE c.status = 'active' AND (35            c.canonical_name ILIKE ${'%' + q + '%'} OR c.canonical_name % ${q} OR c.id = ${upper}36            OR EXISTS (SELECT 1 FROM cancer_aliases a WHERE a.cancer_id = c.id AND (a.normalized LIKE ${norm + '%'} OR a.alias % ${q}))37            OR EXISTS (SELECT 1 FROM cancer_codes k WHERE k.cancer_id = c.id AND upper(k.code) = ${upper}))38          UNION ALL39          SELECT 'gene', g.id, g.symbol, g.name, '/gene/' || g.symbol,40            CASE WHEN upper(g.symbol) = ${upper} THEN 0 WHEN ${upper} = ANY(g.alias_symbols) OR ${upper} = ANY(g.prev_symbols) OR g.hgnc_id = ${upper} THEN 1 WHEN upper(g.symbol) LIKE ${upper + '%'} THEN 2 ELSE 3 END,41            similarity(g.symbol, ${q})42          FROM genes g WHERE upper(g.symbol) LIKE ${upper + '%'} OR ${upper} = ANY(g.alias_symbols) OR ${upper} = ANY(g.prev_symbols) OR g.hgnc_id = ${upper} OR g.name ILIKE ${'%' + q + '%'}43          UNION ALL44          SELECT 'variant', v.id, coalesce(v.gene_symbol || ' ', '') || v.name, v.variant_type, '/variant/' || v.slug,45            CASE WHEN lower(coalesce(v.gene_symbol || ' ', '') || v.name) = ${lower} OR lower(v.name) = ${lower} THEN 046                 WHEN v.hgvs_p = ${q} OR v.hgvs_c = ${q} OR v.clinvar_variation_id = ${q} OR ${q} = ANY(v.dbsnp_ids) THEN 147                 WHEN lower(coalesce(v.gene_symbol || ' ', '') || v.name) LIKE ${prefix} THEN 2 ELSE 3 END,48            similarity(coalesce(v.gene_symbol || ' ', '') || v.name, ${q})49          FROM variants v WHERE (coalesce(v.gene_symbol || ' ', '') || v.name) ILIKE ${'%' + q + '%'} OR v.hgvs_p = ${q} OR v.hgvs_c = ${q} OR v.clinvar_variation_id = ${q} OR ${q} = ANY(v.dbsnp_ids)50          UNION ALL51          SELECT 'drug', d.id, d.name, d.kind, '/drug/' || d.slug,52            CASE WHEN lower(d.name) = ${lower} THEN 0 WHEN EXISTS (SELECT 1 FROM drug_aliases a WHERE a.drug_id = d.id AND a.normalized = ${norm}) THEN 1 WHEN lower(d.name) LIKE ${prefix} THEN 2 ELSE 3 END,53            greatest(similarity(d.name, ${q}), (SELECT coalesce(max(similarity(a.alias, ${q})), 0) FROM drug_aliases a WHERE a.drug_id = d.id))54          FROM drugs d WHERE d.name ILIKE ${'%' + q + '%'} OR d.name % ${q} OR EXISTS (SELECT 1 FROM drug_aliases a WHERE a.drug_id = d.id AND (a.normalized LIKE ${norm + '%'} OR a.alias % ${q}))55          UNION ALL56          SELECT 'trial', t.id, t.nct_id || ' — ' || t.brief_title, t.overall_status, '/trial/' || t.nct_id,57            CASE WHEN upper(t.nct_id) = ${upper} THEN 0 WHEN upper(t.acronym) = ${upper} THEN 1 WHEN upper(t.nct_id) LIKE ${upper + '%'} THEN 2 ELSE 3 END,58            similarity(t.brief_title, ${q})59          FROM clinical_trials t WHERE upper(t.nct_id) LIKE ${upper + '%'} OR upper(t.acronym) = ${upper} OR (length(${q}) >= 4 AND t.brief_title ILIKE ${'%' + q + '%'})60          UNION ALL61          SELECT 'publication', p.id, p.title, coalesce(p.journal_iso, p.journal) || coalesce(' · ' || p.pub_year::text, ''), '/publication/' || p.pmid,62            CASE WHEN p.pmid = ${q} OR lower(p.doi) = ${lower} THEN 0 ELSE 3 END, similarity(p.title, ${q})63          FROM publications p WHERE p.pmid IS NOT NULL AND (p.pmid = ${q} OR lower(p.doi) = ${lower} OR (length(${q}) >= 5 AND p.title ILIKE ${'%' + q + '%'}))64          UNION ALL65          SELECT 'source', s.id, s.name, s.category, '/source/' || s.slug,66            CASE WHEN lower(s.name) = ${lower} OR s.slug = ${lower} THEN 0 WHEN lower(s.name) LIKE ${prefix} THEN 2 ELSE 3 END, similarity(s.name, ${q})67          FROM sources s WHERE s.name ILIKE ${'%' + q + '%'} OR s.slug ILIKE ${'%' + lower + '%'}68        )69        SELECT * FROM cand ORDER BY tier, score DESC, title LIMIT ${limit}`),70    [] as Row[],71  );72  // The SQL ORDER BY already applies the tier/score/title order; sortHits re-applies the same pure73  // comparator so the ordering contract is one function (unit-tested) rather than two copies.74  return sortHits(rows.map((r) => ({ ...r, score: Number(r.score) }))).map((r) => ({ type: r.type, id: r.id, title: r.title, subtitle: r.subtitle, href: r.href, match: matchFromTier(r.tier, r.type) }));75}76