spb/cancerindex

1import { sql } from 'drizzle-orm';2import type { FastifyPluginAsyncZod } from 'fastify-type-provider-zod';3import { z } from 'zod';4import { normalizeLabel } from '@cancerindex/shared';5import { paginate } from '../lib/envelope.js';6import { pageQuery } from '../lib/pagination.js';7import { resolveDrug } from '../lib/resolve.js';8import { AnyList, AnyRecord, camel, camelRows, num, ok, respond } from '../lib/respond.js';9import { pluck } from '../lib/sources.js';1011export const drugRoutes: FastifyPluginAsyncZod = async (app) => {12  app.get('/drugs', { schema: { tags: ['drugs'], summary: 'List drugs (generic/INN names; brands are aliases)', querystring: z.object({ q: z.string().trim().min(1).max(100).optional(), kind: z.string().optional(), ...pageQuery }), response: ok(AnyList, true) } }, async (req) => {13    const q = req.query;14    const conds = [sql`true`];15    if (q.kind) conds.push(sql`d.kind = ${q.kind}`);16    if (q.q) {17      const norm = normalizeLabel(q.q);18      conds.push(sql`EXISTS (SELECT 1 FROM drug_aliases a WHERE a.drug_id = d.id AND (a.normalized = ${norm} OR a.normalized LIKE ${norm + '%'}))`);19    }20    const rows = await app.db.execute<Record<string, unknown> & { total: string }>(sql`21      SELECT d.id, d.slug, d.name, d.kind, d.ncit_code, d.chembl_id, d.civic_therapy_id, d.drugbank_id, d.mechanism, d.development_status,22        (SELECT count(*) FROM civic_evidence_items e WHERE e.status = 'ACCEPTED' AND d.id = ANY(e.therapy_ids)) AS evidence_items,23        (SELECT count(*) FROM drug_approvals a WHERE a.drug_id = d.id AND a.status IN ('approved','accelerated','conditional')) AS approvals,24        count(*) OVER() AS total25      FROM drugs d WHERE ${sql.join(conds, sql` AND `)} ORDER BY d.name LIMIT ${q.limit} OFFSET ${q.offset}`);26    const total = rows.length ? num(rows[0]!.total) : 0;27    const data = rows.map((r) => {28      const { total: _t, ...rest } = r;29      return camel(rest);30    });31    const sourceRows = data.length ? await app.db.execute<{ source_id: string }>(sql`SELECT DISTINCT source_id FROM drug_aliases WHERE source_id IS NOT NULL AND drug_id = ANY(${sql.param(data.map((d) => d.id as string))}::text[])`) : [];32    return respond(app, data, pluck(sourceRows, 'source_id'), paginate(total, q.limit, q.offset));33  });3435  app.get('/drugs/:id', { schema: { tags: ['drugs'], summary: 'Drug: aliases, curated evidence grouped by cancer, jurisdiction-aware approvals, trials via interventions', params: z.object({ id: z.string().min(1).describe('CI-DRUG-… id or slug') }), response: ok(AnyRecord) } }, async (req) => {36    const { id } = await resolveDrug(app.db, req.params.id);37    const db = app.db;38    const [drug, aliases, evidence, approvals, trials, targets] = await Promise.all([39      db.execute<Record<string, unknown>>(sql`SELECT * FROM drugs WHERE id = ${id}`),40      db.execute<Record<string, unknown>>(sql`SELECT alias, alias_type, source_id FROM drug_aliases WHERE drug_id = ${id} ORDER BY CASE alias_type WHEN 'generic' THEN 0 WHEN 'brand' THEN 1 ELSE 2 END, alias`),41      db.execute<Record<string, unknown>>(sql`42        SELECT e.cancer_id, c.slug AS cancer_slug, c.canonical_name AS cancer_name, e.disease_name, count(*) AS evidence_items,43          count(*) FILTER (WHERE e.evidence_level = 'A') AS level_a, count(*) FILTER (WHERE e.evidence_level = 'B') AS level_b, count(*) FILTER (WHERE e.evidence_level = 'C') AS level_c, count(*) FILTER (WHERE e.evidence_level IN ('D','E')) AS level_de,44          count(*) FILTER (WHERE e.evidence_direction = 'SUPPORTS') AS supports, count(*) FILTER (WHERE e.evidence_direction = 'DOES_NOT_SUPPORT') AS does_not_support,45          count(*) FILTER (WHERE e.significance = 'SENSITIVITYRESPONSE') AS sensitivity, count(*) FILTER (WHERE e.significance = 'RESISTANCE') AS resistance,46          array_agg(DISTINCT gs) FILTER (WHERE gs IS NOT NULL) AS gene_symbols, array_agg(DISTINCT e.civic_id) AS civic_ids, min(p.source_id) AS source_id47        FROM civic_evidence_items e LEFT JOIN cancers c ON c.id = e.cancer_id LEFT JOIN LATERAL unnest(e.gene_symbols) gs ON true LEFT JOIN provenance p ON p.id = e.provenance_id48        WHERE e.status = 'ACCEPTED' AND ${id} = ANY(e.therapy_ids)49        GROUP BY e.cancer_id, c.slug, c.canonical_name, e.disease_name ORDER BY evidence_items DESC`),50      db.execute<Record<string, unknown>>(sql`51        SELECT a.*, c.slug AS cancer_slug, c.canonical_name AS cancer_name, p.source_url, p.retrieved_at, p.dataset_version52        FROM drug_approvals a LEFT JOIN cancers c ON c.id = a.cancer_id LEFT JOIN provenance p ON p.id = a.provenance_id53        WHERE a.drug_id = ${id} ORDER BY a.approval_date DESC NULLS LAST`),54      db.execute<Record<string, unknown>>(sql`55        SELECT t.id, t.nct_id, t.brief_title, t.phases, t.overall_status, t.study_type, t.start_date, t.enrollment_count, t.lead_sponsor, ti.name AS intervention_name, ti.match_type56        FROM trial_interventions ti JOIN clinical_trials t ON t.id = ti.trial_id WHERE ti.drug_id = ${id}57        ORDER BY t.last_update_posted_date DESC NULLS LAST LIMIT 200`),58      db.execute<Record<string, unknown>>(sql`SELECT g.id, g.symbol, g.name FROM drugs d CROSS JOIN LATERAL unnest(d.target_gene_ids) gid JOIN genes g ON g.id = gid WHERE d.id = ${id}`),59    ]);60    const data = {61      ...camel(drug[0]!),62      targets: camelRows(targets),63      aliases: camelRows(aliases),64      evidenceByCancer: evidence.map((r) => ({65        cancer: r.cancer_id ? { id: r.cancer_id, slug: r.cancer_slug, name: r.cancer_name } : null,66        diseaseName: r.disease_name,67        evidenceItems: num(r.evidence_items),68        byLevel: { A: num(r.level_a), B: num(r.level_b), C: num(r.level_c), DE: num(r.level_de) },69        byDirection: { supports: num(r.supports), doesNotSupport: num(r.does_not_support) },70        bySignificance: { sensitivity: num(r.sensitivity), resistance: num(r.resistance) },71        geneSymbols: r.gene_symbols ?? [],72        civicEvidenceIds: r.civic_ids,73        sourceId: r.source_id,74        category: 'curated_evidence',75      })),76      approvals: approvals.map((r) => {77        const { cancer_slug, cancer_name, source_url, retrieved_at, dataset_version, raw: _raw, provenance_id: _p, ...rest } = r;78        return { ...camel(rest), cancer: r.cancer_id ? { id: r.cancer_id, slug: cancer_slug, name: cancer_name } : null, provenance: { sourceId: r.source_id, url: source_url, retrievedAt: retrieved_at, datasetVersion: dataset_version, category: 'regulatory_status' } };79      }),80      trials: camelRows(trials),81    };82    return respond(app, data, [...pluck(aliases, 'source_id'), ...pluck(evidence, 'source_id'), ...pluck(approvals, 'source_id'), ...(trials.length ? ['clinicaltrials'] : [])]);83  });84};85