spb/cancerindex

1import 'server-only';2import { run, sql, safe } from '@/lib/db';34export interface PublicationRow {5  id: string;6  pmid: string | null;7  doi: string | null;8  pmcid: string | null;9  title: string;10  abstract: string | null;11  journal: string | null;12  journal_iso: string | null;13  pub_date: string | null;14  pub_year: number | null;15  publication_types: string[];16  mesh_terms: Array<{ descriptor: string; ui?: string; major: boolean; qualifiers?: string[] }>;17  authors: Array<{ name: string; affiliation?: string; orcid?: string }>;18  language: string | null;19  is_preprint: boolean;20  retracted: boolean;21  retraction_notice: string | null;22  nct_ids: string[];23  cited_by_count: number | null;24  ingest_run_id: string | null;25  updated_at: Date;26}2728export async function getPublicationByPmid(pmid: string): Promise<PublicationRow | null> {29  const rows = await safe(() => run<PublicationRow>(sql`SELECT * FROM publications WHERE pmid = ${pmid} LIMIT 1`), [] as PublicationRow[]);30  return rows[0] ?? null;31}3233export interface PubEdge {34  entity_type: string;35  entity_id: string;36  method: string;37  confidence: number | null;38  status: string;39  label: string | null;40  href: string | null;41}42export async function publicationEntities(publicationId: string): Promise<PubEdge[]> {43  return safe(44    () =>45      run<PubEdge>(sql`46        SELECT e.entity_type, e.entity_id, e.method, e.confidence, e.status,47          CASE e.entity_type WHEN 'cancer' THEN (SELECT canonical_name FROM cancers WHERE id = e.entity_id)48                             WHEN 'gene' THEN (SELECT symbol FROM genes WHERE id = e.entity_id)49                             WHEN 'variant' THEN (SELECT coalesce(gene_symbol || ' ', '') || name FROM variants WHERE id = e.entity_id)50                             WHEN 'drug' THEN (SELECT name FROM drugs WHERE id = e.entity_id)51                             WHEN 'trial' THEN (SELECT nct_id FROM clinical_trials WHERE id = e.entity_id) END AS label,52          CASE e.entity_type WHEN 'cancer' THEN (SELECT '/cancer/' || slug FROM cancers WHERE id = e.entity_id)53                             WHEN 'gene' THEN (SELECT '/gene/' || symbol FROM genes WHERE id = e.entity_id)54                             WHEN 'variant' THEN (SELECT '/variant/' || slug FROM variants WHERE id = e.entity_id)55                             WHEN 'drug' THEN (SELECT '/drug/' || slug FROM drugs WHERE id = e.entity_id)56                             WHEN 'trial' THEN (SELECT '/trial/' || nct_id FROM clinical_trials WHERE id = e.entity_id) END AS href57        FROM publication_entity_edges e WHERE e.publication_id = ${publicationId} ORDER BY e.status, e.entity_type, label`),58    [] as PubEdge[],59  );60}6162export interface LitCount {63  id: number;64  window_key: string;65  window_start: string | null;66  window_end: string | null;67  query: string;68  count: number;69  provenance_id: number;70  updated_at: Date; // schema `computedAt` uses the updatedAt() helper → column `updated_at`71}72export async function literatureCountsFor(cancerId: string): Promise<LitCount[]> {73  return safe(() => run<LitCount>(sql`SELECT * FROM literature_counts WHERE cancer_id = ${cancerId} ORDER BY CASE window_key WHEN 'all' THEN 0 WHEN '10y' THEN 1 WHEN '5y' THEN 2 WHEN '5y_prior' THEN 3 WHEN '12m' THEN 4 ELSE 5 END, window_key`), [] as LitCount[]);74}7576export const PUBLICATION_PAGE_SIZE = 25;7778/** Columns the publication lists render (never the abstract or the full MeSH/author arrays). */79export type PublicationListRow = Pick<PublicationRow, 'id' | 'pmid' | 'title' | 'journal' | 'journal_iso' | 'pub_date' | 'pub_year' | 'is_preprint' | 'retracted' | 'updated_at' | 'ingest_run_id'> & {80  /** First three authors only. */81  authors: Array<{ name: string }>;82  author_count: number;83  method?: string;84  edge_status?: string;85};8687const LIST_COLUMNS = sql`p.id, p.pmid, p.title, p.journal, p.journal_iso, p.pub_date, p.pub_year, p.is_preprint, p.retracted, p.updated_at, p.ingest_run_id,88  (SELECT coalesce(jsonb_agg(jsonb_build_object('name', a->>'name')), '[]'::jsonb) FROM (SELECT a FROM jsonb_array_elements(p.authors) WITH ORDINALITY x(a, i) ORDER BY i LIMIT 3) s) AS authors,89  jsonb_array_length(p.authors) AS author_count`;9091const whereEntity = (entityType: string, entityIds: string[]) => sql`e.entity_type = ${entityType} AND e.entity_id IN (${sql.join(entityIds.map((i) => sql`${i}`), sql`, `)}) AND e.status <> 'rejected'`;9293/** Publications linked to any of the entities, newest first, one row per publication (best edge status). */94export async function recentPublicationsFor(entityType: string, entityIds: string[], p: { page: number; pageSize: number } = { page: 1, pageSize: PUBLICATION_PAGE_SIZE }): Promise<PublicationListRow[]> {95  if (entityIds.length === 0) return [];96  return safe(97    () =>98      run<PublicationListRow>(sql`99        SELECT ${LIST_COLUMNS}, x.method, x.edge_status FROM (100          SELECT DISTINCT ON (e.publication_id) e.publication_id, e.method, e.status AS edge_status101          FROM publication_entity_edges e WHERE ${whereEntity(entityType, entityIds)} ORDER BY e.publication_id, e.status102        ) x JOIN publications p ON p.id = x.publication_id103        ORDER BY p.pub_date DESC NULLS LAST, p.id LIMIT ${p.pageSize} OFFSET ${(Math.max(1, p.page) - 1) * p.pageSize}`),104    [] as PublicationListRow[],105  );106}107export async function recentPublicationsForCount(entityType: string, entityIds: string[]): Promise<number> {108  if (entityIds.length === 0) return 0;109  const r = await safe(() => run<{ n: string }>(sql`SELECT count(DISTINCT e.publication_id) AS n FROM publication_entity_edges e WHERE ${whereEntity(entityType, entityIds)}`), [{ n: '0' }]);110  return Number(r[0]?.n ?? 0);111}112113export async function publicationsForTrial(nctId: string, p: { page: number; pageSize: number } = { page: 1, pageSize: PUBLICATION_PAGE_SIZE }): Promise<PublicationListRow[]> {114  return safe(() => run<PublicationListRow>(sql`SELECT ${LIST_COLUMNS} FROM publications p WHERE ${nctId} = ANY(p.nct_ids) ORDER BY p.pub_date DESC NULLS LAST, p.id LIMIT ${p.pageSize} OFFSET ${(Math.max(1, p.page) - 1) * p.pageSize}`), [] as PublicationListRow[]);115}116export async function publicationsForTrialCount(nctId: string): Promise<number> {117  const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM publications p WHERE ${nctId} = ANY(p.nct_ids)`), [{ n: '0' }]);118  return Number(r[0]?.n ?? 0);119}120