spb/cancerindex

1import { listTrialIntelligence } from '@/lib/queries/trial-intelligence';2import { sourceInfoById } from '@/lib/queries/provenance';3import { SITE_URL } from '@/lib/site';4import { isoDate, toDate } from '@/lib/format';5import { csvCell } from '@/lib/trial-intel';67export const dynamic = 'force-dynamic';89/**10 * GET /api/export/trial-intelligence.csv?level=top|all11 * The full `trial_intelligence` layer for one entity level as CSV (attribution header rows first).12 */13export async function GET(req: Request) {14  const url = new URL(req.url);15  const levelRaw = url.searchParams.get('level') ?? 'top';16  if (levelRaw !== 'top' && levelRaw !== 'all') return new Response('invalid level (top|all)', { status: 400 });17  const level = levelRaw;18  const rows = await listTrialIntelligence(level);19  if (rows.length === 0) return new Response('trial intelligence not computed on this environment', { status: 404 });20  const burdenIds = [...new Set(rows.map((r) => r.burden_source_id).filter((s): s is string => !!s))];21  const src = await sourceInfoById(burdenIds);22  const burdenSources = burdenIds.map((id) => src.get(id)?.name ?? id).join('; ');23  const computedAt = rows.reduce<Date | null>((m, r) => {24    const d = toDate(r.computed_at);25    return d && (!m || d > m) ? d : m;26  }, null);27  const first = rows[0]!;28  const th = JSON.stringify(first.inputs.thresholds ?? {});29  const windows = JSON.stringify(first.inputs.windows ?? {});3031  const header = [32    `# CancerIndex trial intelligence export — entity level: ${level}`,33    `# Source: CancerIndex (${SITE_URL}) — derived data, CC BY 4.0. Underlying records: ClinicalTrials.gov (U.S. National Library of Medicine, public domain)${burdenSources ? `; burden denominators: ${burdenSources}` : ''}.`,34    `# formula_version: ${first.formula_version} · study_type: INTERVENTIONAL · aggregation: entity + NCIt descendants (depth ≤ 12) · active statuses: ${(first.inputs.activeStatuses as string[] | undefined)?.join('|') ?? ''}`,35    `# thresholds: ${th} · growth windows: ${windows} · computed_at: ${computedAt?.toISOString() ?? ''}`,36    `# Every value is a computed metric (claim category computed_metric). Multinational studies contribute to several countries. Registrant-reported statuses; a TERMINATED status does not imply a negative result.`,37  ];38  const cols = [39    'cancer_id', 'slug', 'canonical_name', 'top_level', 'entity_level',40    'total_trials', 'active_trials', 'recruiting_trials', 'phase1_active', 'phase2_active', 'phase3_active', 'phase3_recruiting', 'phase4_active',41    'completed_trials', 'terminated_trials', 'withdrawn_trials', 'suspended_trials', 'with_results',42    'new_trials_12m', 'new_trials_prior_12m', 'trial_growth_yoy', 'avg_enrollment', 'median_enrollment', 'total_enrollment_active',43    'distinct_sponsors', 'industry_share', 'sponsor_hhi', 'top_sponsor', 'top_sponsor_share',44    'distinct_countries', 'us_share', 'top_country', 'top_country_share', 'country_hhi',45    'termination_share', 'why_stopped_breakdown_json', 'trials_per_1000_deaths', 'trials_per_100k_cases', 'burden_geography', 'burden_year', 'burden_source_id', 'burden_source',46    'formula_version', 'computed_at', 'inputs_json',47  ];48  const lines = rows.map((r) =>49    [50      r.cancer_id, r.cancer_slug, r.cancer_name, r.top_level, r.entity_level,51      r.total_trials, r.active_trials, r.recruiting_trials, r.phase1_active, r.phase2_active, r.phase3_active, r.phase3_recruiting, r.phase4_active,52      r.completed_trials, r.terminated_trials, r.withdrawn_trials, r.suspended_trials, r.with_results,53      r.new_trials_12m, r.new_trials_prior_12m, r.trial_growth_yoy, r.avg_enrollment, r.median_enrollment, r.total_enrollment_active,54      r.distinct_sponsors, r.industry_share, r.sponsor_hhi, r.top_sponsor, r.top_sponsor_share,55      r.distinct_countries, r.us_share, r.top_country, r.top_country_share, r.country_hhi,56      r.termination_share, r.why_stopped_breakdown, r.trials_per_1000_deaths, r.trials_per_100k_cases, r.burden_geography, r.burden_year, r.burden_source_id, r.burden_source_slug,57      r.formula_version, toDate(r.computed_at)?.toISOString() ?? '', r.inputs,58    ]59      .map(csvCell)60      .join(','),61  );62  const body = [...header, cols.join(','), ...lines].join('\n') + '\n';63  const fname = `cancerindex-trial-intelligence-${level}-${isoDate(computedAt)}.csv`;64  return new Response(body, { headers: { 'Content-Type': 'text/csv; charset=utf-8', 'Content-Disposition': `attachment; filename="${fname}"`, 'Cache-Control': 'public, max-age=300' } });65}66