spb/cancerindex

1import { sql } from 'drizzle-orm';2import type { Database } from '@cancerindex/database';3import { normalizeLabel } from '@cancerindex/shared';45export type SearchType = 'cancer' | 'gene' | 'variant' | 'drug' | 'trial' | 'publication';67/** Match tier — lower is better (CLAUDE.md §68, §312: exact > alias > prefix > fuzzy). */8export const TIER = { exact: 0, alias: 1, prefix: 2, fuzzy: 3 } as const;9export type Tier = (typeof TIER)[keyof typeof TIER];1011export interface SearchHit {12  type: SearchType;13  id: string;14  slug: string;15  name: string;16  subtitle: string | null;17  tier: Tier;18  /** Similarity or secondary signal used inside a tier (higher is better). */19  score: number;20}2122export interface SearchResult {23  type: SearchType;24  id: string;25  slug: string;26  name: string;27  subtitle: string | null;28  score: number;29  match: 'exact' | 'alias' | 'prefix' | 'fuzzy';30}3132const TIER_NAME: Record<Tier, SearchResult['match']> = { 0: 'exact', 1: 'alias', 2: 'prefix', 3: 'fuzzy' };33/** Entity ordering inside a tier: cancers first (the index is cancer-centric), then genes, drugs, variants, trials, publications. */34const TYPE_ORDER: Record<SearchType, number> = { cancer: 0, gene: 1, drug: 2, variant: 3, trial: 4, publication: 5 };3536/**37 * Deterministic ordering (pure, unit-tested): tier asc, score desc, type order, shorter name first,38 * then name, then id. One result per (type, id). Returns the top `limit` results.39 */40export function rankSearchHits(hits: SearchHit[], limit = 20): SearchResult[] {41  const best = new Map<string, SearchHit>();42  for (const h of hits) {43    const key = `${h.type}:${h.id}`;44    const prev = best.get(key);45    if (!prev || h.tier < prev.tier || (h.tier === prev.tier && h.score > prev.score)) best.set(key, h);46  }47  return [...best.values()]48    .sort((a, b) => a.tier - b.tier || b.score - a.score || TYPE_ORDER[a.type] - TYPE_ORDER[b.type] || a.name.length - b.name.length || a.name.localeCompare(b.name) || a.id.localeCompare(b.id))49    .slice(0, limit)50    .map((h) => ({ type: h.type, id: h.id, slug: h.slug, name: h.name, subtitle: h.subtitle, score: Math.round((4 - h.tier + h.score) * 1000) / 1000, match: TIER_NAME[h.tier] }));51}5253/** Cross-entity search over aliases, symbols, slugs and registry ids. */54export async function searchAll(db: Database, q: string, types?: SearchType[], limit = 20): Promise<SearchResult[]> {55  const raw = q.trim();56  const norm = normalizeLabel(raw);57  if (!norm) return [];58  const want = new Set<SearchType>(types && types.length ? types : ['cancer', 'gene', 'variant', 'drug', 'trial', 'publication']);59  const hits: SearchHit[] = [];60  const per = Math.max(limit, 20);61  const fuzzyOk = norm.length >= 4;6263  const tasks: Array<Promise<void>> = [];6465  if (want.has('cancer')) {66    tasks.push(67      db68        .execute<{ id: string; slug: string; name: string; subtitle: string | null; tier: number; score: number }>(sql`69        SELECT c.id, c.slug, c.canonical_name AS name, c.primary_ncit_code AS subtitle,70          min(CASE WHEN a.normalized = ${norm} AND a.alias_type = 'preferred' THEN 071                   WHEN a.normalized = ${norm} THEN 172                   WHEN a.normalized LIKE ${norm + '%'} THEN 273                   ELSE 3 END) AS tier,74          max(similarity(a.normalized, ${norm})) AS score75        FROM cancers c JOIN cancer_aliases a ON a.cancer_id = c.id76        WHERE c.status = 'active' AND (a.normalized = ${norm} OR a.normalized LIKE ${norm + '%'} ${fuzzyOk ? sql`OR a.normalized % ${norm}` : sql``})77        GROUP BY c.id, c.slug, c.canonical_name, c.primary_ncit_code78        ORDER BY tier, score DESC, c.canonical_name LIMIT ${per}`)79        .then((rows) => {80          for (const r of rows) hits.push({ type: 'cancer', id: r.id, slug: r.slug, name: r.name, subtitle: r.subtitle ? `NCIt ${r.subtitle}` : null, tier: Number(r.tier) as Tier, score: Number(r.score) });81        }),82    );83  }84  if (want.has('gene')) {85    const up = raw.toUpperCase();86    tasks.push(87      db88        .execute<{ id: string; symbol: string; name: string | null; tier: number; score: number }>(sql`89        SELECT g.id, g.symbol, g.name,90          min(CASE WHEN upper(g.symbol) = ${up} THEN 091                   WHEN upper(a.alias) = ${up} THEN 192                   WHEN upper(g.symbol) LIKE ${up + '%'} OR upper(a.alias) LIKE ${up + '%'} THEN 293                   ELSE 3 END) AS tier,94          greatest(max(similarity(upper(g.symbol), ${up})), max(similarity(upper(coalesce(a.alias, '')), ${up})), max(similarity(lower(coalesce(g.name,'')), ${norm}))) AS score95        FROM genes g LEFT JOIN gene_aliases a ON a.gene_id = g.id96        WHERE upper(g.symbol) LIKE ${up + '%'} OR upper(a.alias) LIKE ${up + '%'} ${fuzzyOk ? sql`OR lower(g.name) % ${norm}` : sql``}97        GROUP BY g.id, g.symbol, g.name98        ORDER BY tier, score DESC, g.symbol LIMIT ${per}`)99        .then((rows) => {100          for (const r of rows) hits.push({ type: 'gene', id: r.id, slug: r.symbol, name: r.symbol, subtitle: r.name, tier: Number(r.tier) as Tier, score: Number(r.score) });101        }),102    );103  }104  if (want.has('drug')) {105    tasks.push(106      db107        .execute<{ id: string; slug: string; name: string; kind: string | null; tier: number; score: number }>(sql`108        SELECT d.id, d.slug, d.name, d.kind,109          min(CASE WHEN a.normalized = ${norm} AND a.alias_type = 'generic' THEN 0110                   WHEN a.normalized = ${norm} THEN 1111                   WHEN a.normalized LIKE ${norm + '%'} THEN 2 ELSE 3 END) AS tier,112          max(similarity(a.normalized, ${norm})) AS score113        FROM drugs d JOIN drug_aliases a ON a.drug_id = d.id114        WHERE a.normalized = ${norm} OR a.normalized LIKE ${norm + '%'} ${fuzzyOk ? sql`OR a.normalized % ${norm}` : sql``}115        GROUP BY d.id, d.slug, d.name, d.kind116        ORDER BY tier, score DESC, d.name LIMIT ${per}`)117        .then((rows) => {118          for (const r of rows) hits.push({ type: 'drug', id: r.id, slug: r.slug, name: r.name, subtitle: r.kind, tier: Number(r.tier) as Tier, score: Number(r.score) });119        }),120    );121  }122  if (want.has('variant')) {123    const slugLike = norm.replace(/ /g, '-');124    tasks.push(125      db126        .execute<{ id: string; slug: string; name: string; gene_symbol: string | null; tier: number; score: number }>(sql`127        SELECT v.id, v.slug, v.name, v.gene_symbol,128          CASE WHEN v.slug = ${slugLike} OR lower(coalesce(v.gene_symbol,'') || ' ' || v.name) = ${norm} THEN 0129               WHEN lower(v.name) = ${norm} THEN 1130               WHEN v.slug LIKE ${slugLike + '%'} OR lower(coalesce(v.gene_symbol,'') || ' ' || v.name) LIKE ${norm + '%'} THEN 2 ELSE 3 END AS tier,131          similarity(lower(coalesce(v.gene_symbol,'') || ' ' || v.name), ${norm}) AS score132        FROM variants v133        WHERE v.slug LIKE ${slugLike + '%'} OR lower(coalesce(v.gene_symbol,'') || ' ' || v.name) LIKE ${norm + '%'} OR lower(v.name) = ${norm}134          ${fuzzyOk ? sql`OR lower(coalesce(v.gene_symbol,'') || ' ' || v.name) % ${norm}` : sql``}135        ORDER BY tier, score DESC, v.name LIMIT ${per}`)136        .then((rows) => {137          for (const r of rows) hits.push({ type: 'variant', id: r.id, slug: r.slug, name: r.gene_symbol ? `${r.gene_symbol} ${r.name}` : r.name, subtitle: 'variant', tier: Number(r.tier) as Tier, score: Number(r.score) });138        }),139    );140  }141  if (want.has('trial')) {142    const up = raw.toUpperCase();143    tasks.push(144      db145        .execute<{ id: string; nct_id: string; brief_title: string; overall_status: string | null; tier: number; score: number }>(sql`146        SELECT t.id, t.nct_id, t.brief_title, t.overall_status,147          CASE WHEN t.nct_id = ${up} THEN 0148               WHEN upper(coalesce(t.acronym,'')) = ${up} THEN 1149               WHEN t.nct_id LIKE ${up + '%'} THEN 2 ELSE 3 END AS tier,150          similarity(lower(t.brief_title), ${norm}) AS score151        FROM clinical_trials t152        WHERE t.nct_id LIKE ${up + '%'} OR upper(coalesce(t.acronym,'')) = ${up} ${fuzzyOk ? sql`OR lower(t.brief_title) % ${norm}` : sql``}153        ORDER BY tier, score DESC, t.nct_id LIMIT ${per}`)154        .then((rows) => {155          for (const r of rows) hits.push({ type: 'trial', id: r.id, slug: r.nct_id, name: r.nct_id, subtitle: r.brief_title, tier: Number(r.tier) as Tier, score: Number(r.score) });156        }),157    );158  }159  if (want.has('publication') && /^\d{1,9}$/.test(raw)) {160    tasks.push(161      db162        .execute<{ id: string; pmid: string; title: string; journal: string | null; pub_year: number | null }>(sql`163        SELECT id, pmid, title, journal, pub_year FROM publications WHERE pmid = ${raw} LIMIT 1`)164        .then((rows) => {165          for (const r of rows) hits.push({ type: 'publication', id: r.id, slug: r.pmid, name: r.title, subtitle: [r.journal, r.pub_year].filter(Boolean).join(' · ') || `PMID ${r.pmid}`, tier: TIER.exact, score: 1 });166        }),167    );168  }169  await Promise.all(tasks);170  return rankSearchHits(hits, limit);171}172