import 'server-only';
import { run, sql, safe } from '@/lib/db';
import type { EvidenceItem } from '@/lib/queries/evidence';
import { EVIDENCE_DESCRIPTION_CHARS } from '@/lib/queries/evidence';
import type { ApprovalRow } from '@/lib/queries/drugs';
import type { TrialListRow } from '@/lib/queries/trials';
import { ACTIVE_STATUSES } from '@/lib/queries/trials';

/**
 * Biomarkers (SPEC §17, §52, §121). The `biomarkers` table holds curated metadata only (identity,
 * verified NCIt concept, anchor gene, aliases, assay conventions — seeded from
 * packages/database/src/seed-data/biomarkers.ts). Every link to cancers, drugs, approvals, trials
 * and publications is DERIVED HERE at query time from source-native records, with these rules
 * (docs/methodology/biomarkers.md, formula `biomarker-links-v1`):
 *
 *   scope      gene_ids = anchor gene ∪ measurement.genes (HGNC symbols → genes.id);
 *              variant_ids = measurement.variantSlugs → variants.id. When variant_ids is non-empty
 *              the marker is molecular-level and evidence is restricted to those variants; otherwise
 *              the whole gene(s).
 *   evidence   CIViC items with status ACCEPTED and type PREDICTIVE | PROGNOSTIC | DIAGNOSTIC whose
 *              variant_ids (or gene_ids) overlap the scope. Levels A–E kept native.
 *   cancers    distinct mapped cancer_id of that evidence.
 *   drugs      therapy_ids of the PREDICTIVE evidence ∪ targets of active PREDICTS_RESPONSE_TO
 *              knowledge edges whose source variant is in scope. Direction from the source.
 *   approvals  drug_approvals of those drugs (matched_by = drug), plus rows whose indication text
 *              contains a curated `indicationTerms` phrase (matched_by = indication). tumor_agnostic
 *              is the source flag, never inferred.
 *   trials     clinical_trials with an intervention mapped to a scope drug AND a condition mapped to
 *              a scope cancer; "active" = ClinicalTrials.gov statuses in ACTIVE_STATUSES.
 *   literature publication_entity_edges on the scope variants (molecular markers) or genes.
 */
export const BIOMARKER_LINKS_FORMULA = 'biomarker-links-v1';

export const BIOMARKER_KINDS = ['gene_mutation', 'protein_expression', 'hormone_receptor', 'immune_marker', 'msi', 'tmb', 'hrd', 'ctdna', 'methylation', 'signature', 'cell_surface', 'other'] as const;
export type BiomarkerKind = (typeof BIOMARKER_KINDS)[number];
export const BIOMARKER_KIND_LABEL: Record<string, string> = {
  gene_mutation: 'Gene alteration',
  protein_expression: 'Protein expression',
  hormone_receptor: 'Hormone receptor',
  immune_marker: 'Immune marker',
  msi: 'MSI / MMR',
  tmb: 'Tumor mutational burden',
  hrd: 'HR deficiency',
  ctdna: 'Circulating tumor DNA',
  methylation: 'Methylation',
  signature: 'Signature',
  cell_surface: 'Cell-surface target',
  other: 'Other',
};
export function kindLabel(kind: string): string {
  return BIOMARKER_KIND_LABEL[kind] ?? kind.replace(/_/g, ' ');
}

/** Mirror of the seed's `measurement` jsonb (written only by the seed). */
export interface BiomarkerMeasurement {
  assays?: string[];
  scoring?: string;
  notes?: string;
  sources?: Array<{ label: string; url: string }>;
  aliases?: string[];
  genes?: string[];
  variantSlugs?: string[];
  indicationTerms?: string[];
  tumorAgnostic?: boolean;
  ncit?: { code: string; name: string; conceptKind: string };
  verification?: { authority: string; endpoint: string; ncitVersion: string; verifiedAt: string };
}

export interface BiomarkerRow {
  id: string;
  slug: string;
  name: string;
  kind: string;
  gene_id: string | null;
  ncit_code: string | null;
  description: string | null;
  measurement: BiomarkerMeasurement;
  updated_at: Date;
  /** Resolved scope (see module doc). */
  gene_ids: string[];
  variant_ids: string[];
  gene_symbols: string[];
}

export interface BiomarkerListRow extends BiomarkerRow {
  cancers_n: number;
  drugs_n: number;
  approvals_n: number;
  tumor_agnostic_n: number;
  active_trials_n: number;
}

/* ------------------------------------------------------------------------------------------------
 * Scope fragments — one CTE `b` carrying gene_ids / variant_ids, reused by every derived block.
 * ---------------------------------------------------------------------------------------------- */

const SCOPE_COLUMNS = sql`
  b.id, b.slug, b.name, b.kind, b.gene_id, b.ncit_code, b.description, b.measurement, b.updated_at,
  ARRAY(SELECT g.id FROM genes g WHERE g.id = b.gene_id OR g.symbol IN (SELECT jsonb_array_elements_text(coalesce(b.measurement->'genes', '[]'::jsonb))))::text[] AS gene_ids,
  ARRAY(SELECT v.id FROM variants v WHERE v.slug IN (SELECT jsonb_array_elements_text(coalesce(b.measurement->'variantSlugs', '[]'::jsonb))))::text[] AS variant_ids`;

// MATERIALIZED: the scope arrays are sub-selects over 45k genes / 417k variants; if PostgreSQL inlines
// this single-reference CTE they are re-evaluated per joined evidence row (22 s for MSI-H, measured
// 2026-09-11) instead of once.
const scopeCte = (where: ReturnType<typeof sql>) => sql`WITH b AS MATERIALIZED (SELECT ${SCOPE_COLUMNS} FROM biomarkers b WHERE ${where})`;
const scopeById = (id: string) => scopeCte(sql`b.id = ${id}`);

/** Evidence in scope (alias `e` = civic_evidence_items, `b` = scope CTE). */
const EV_SCOPE = sql`e.status = 'ACCEPTED' AND e.evidence_type IN ('PREDICTIVE', 'PROGNOSTIC', 'DIAGNOSTIC')
  AND CASE WHEN cardinality(b.variant_ids) > 0 THEN e.variant_ids && b.variant_ids ELSE cardinality(b.gene_ids) > 0 AND e.gene_ids && b.gene_ids END`;

/** Variants in scope (alias `v`). */
const VAR_SCOPE = sql`CASE WHEN cardinality(b.variant_ids) > 0 THEN v.id = ANY(b.variant_ids) ELSE v.gene_id = ANY(b.gene_ids) END`;

/** Derived drug set per biomarker: (bid, drug_id). */
const DSET = sql`dset AS (
  SELECT DISTINCT bid, drug_id FROM (
    SELECT b.id AS bid, t AS drug_id FROM b JOIN civic_evidence_items e ON ${EV_SCOPE} AND e.evidence_type = 'PREDICTIVE' CROSS JOIN LATERAL unnest(e.therapy_ids) t
    UNION
    SELECT b.id, k.target_entity_id FROM b JOIN variants v ON ${VAR_SCOPE}
      JOIN knowledge_edges k ON k.source_entity_type = 'variant' AND k.source_entity_id = v.id AND k.target_entity_type = 'drug' AND k.relationship_type = 'PREDICTS_RESPONSE_TO' AND k.status = 'active'
  ) u)`;
/** Derived cancer set per biomarker: (bid, cancer_id). */
const CSET = sql`cset AS (SELECT DISTINCT b.id AS bid, e.cancer_id FROM b JOIN civic_evidence_items e ON ${EV_SCOPE} WHERE e.cancer_id IS NOT NULL)`;

/**
 * Derived trial set per biomarker: (bid, trial_id) = trials with an intervention mapped to a scope
 * drug AND a condition mapped to a scope cancer. Plain hash joins, MATERIALIZED once: a LIMIT-ed
 * outer query must never walk clinical_trials in date order evaluating EXISTS per row (20 s for HER2
 * vs ~120 ms for this form, measured 2026-09-11).
 */
const TSET = sql`tset AS MATERIALIZED (
  SELECT DISTINCT d.bid, ti.trial_id FROM dset d JOIN trial_interventions ti ON ti.drug_id = d.drug_id
  JOIN trial_conditions tc ON tc.trial_id = ti.trial_id JOIN cset c ON c.bid = d.bid AND c.cancer_id = tc.cancer_id)`;

/** Indication-text match on curated terms (alias `a` = drug_approvals). */
const TERM_MATCH = sql`EXISTS (SELECT 1 FROM jsonb_array_elements_text(coalesce(b.measurement->'indicationTerms', '[]'::jsonb)) term WHERE a.indication ILIKE '%' || term || '%')`;

// sql.param binds the whole array as ONE parameter; interpolating a JS array directly expands to a
// ($1, $2, …) tuple, which is not a valid text[] cast.
const ACTIVE = sql`t.overall_status = ANY(${sql.param(ACTIVE_STATUSES)}::text[])`;

/* ------------------------------------------------------------------------------------------------
 * List
 * ---------------------------------------------------------------------------------------------- */

export async function biomarkerKindFacets(): Promise<Array<{ kind: string; n: number }>> {
  const rows = await safe(() => run<{ kind: string; n: string }>(sql`SELECT kind, count(*) AS n FROM biomarkers GROUP BY kind ORDER BY n DESC, kind`), []);
  return rows.map((r) => ({ kind: r.kind, n: Number(r.n) }));
}

export async function listBiomarkers(opts: { kind?: string; q?: string } = {}): Promise<BiomarkerListRow[]> {
  const conds = [sql`true`];
  if (opts.kind) conds.push(sql`b.kind = ${opts.kind}`);
  if (opts.q) {
    const like = `%${opts.q}%`;
    conds.push(sql`(b.name ILIKE ${like} OR b.slug ILIKE ${like} OR b.ncit_code ILIKE ${opts.q + '%'}
      OR EXISTS (SELECT 1 FROM jsonb_array_elements_text(coalesce(b.measurement->'aliases', '[]'::jsonb)) a WHERE a ILIKE ${like})
      OR EXISTS (SELECT 1 FROM genes g WHERE (g.id = b.gene_id OR g.symbol IN (SELECT jsonb_array_elements_text(coalesce(b.measurement->'genes', '[]'::jsonb)))) AND g.symbol ILIKE ${opts.q + '%'}))`);
  }
  const rows = await safe(
    () =>
      run<Omit<BiomarkerListRow, 'cancers_n' | 'drugs_n' | 'approvals_n' | 'tumor_agnostic_n' | 'active_trials_n'> & { cancers_n: string; drugs_n: string; approvals_n: string; tumor_agnostic_n: string; active_trials_n: string }>(sql`
        ${scopeCte(sql.join(conds, sql` AND `))}, ${DSET}, ${CSET}, ${TSET}
        SELECT b.*,
          coalesce((SELECT array_agg(g.symbol ORDER BY g.symbol) FROM genes g WHERE g.id = ANY(b.gene_ids)), '{}') AS gene_symbols,
          (SELECT count(*) FROM cset c WHERE c.bid = b.id) AS cancers_n,
          (SELECT count(*) FROM dset d WHERE d.bid = b.id) AS drugs_n,
          (SELECT count(*) FROM drug_approvals a WHERE a.drug_id IN (SELECT d.drug_id FROM dset d WHERE d.bid = b.id) OR ${TERM_MATCH}) AS approvals_n,
          (SELECT count(*) FROM drug_approvals a WHERE a.tumor_agnostic AND (a.drug_id IN (SELECT d.drug_id FROM dset d WHERE d.bid = b.id) OR ${TERM_MATCH})) AS tumor_agnostic_n,
          (SELECT count(*) FROM tset x JOIN clinical_trials t ON t.id = x.trial_id WHERE x.bid = b.id AND ${ACTIVE}) AS active_trials_n
        FROM b ORDER BY b.kind, b.name`),
    [],
  );
  return rows.map((r) => ({ ...r, cancers_n: Number(r.cancers_n), drugs_n: Number(r.drugs_n), approvals_n: Number(r.approvals_n), tumor_agnostic_n: Number(r.tumor_agnostic_n), active_trials_n: Number(r.active_trials_n) }));
}

export async function countBiomarkers(): Promise<number> {
  const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM biomarkers`), [{ n: '0' }]);
  return Number(r[0]?.n ?? 0);
}

export async function biomarkerSlugsForSitemap(): Promise<Array<{ slug: string; updated_at: Date }>> {
  return safe(() => run<{ slug: string; updated_at: Date }>(sql`SELECT slug, updated_at FROM biomarkers ORDER BY id`), []);
}

/* ------------------------------------------------------------------------------------------------
 * Detail
 * ---------------------------------------------------------------------------------------------- */

export interface BiomarkerGene {
  id: string;
  symbol: string;
  name: string | null;
  is_cancer_gene: boolean;
}
export interface BiomarkerVariant {
  id: string;
  slug: string;
  name: string;
  gene_symbol: string | null;
  variant_type: string | null;
}

export async function getBiomarkerBySlug(slug: string): Promise<BiomarkerRow | null> {
  const rows = await safe(
    () =>
      run<BiomarkerRow>(sql`
        ${scopeCte(sql`b.slug = ${slug} OR b.id = ${slug}`)}
        SELECT b.*, coalesce((SELECT array_agg(g.symbol ORDER BY g.symbol) FROM genes g WHERE g.id = ANY(b.gene_ids)), '{}') AS gene_symbols FROM b LIMIT 1`),
    [] as BiomarkerRow[],
  );
  return rows[0] ?? null;
}

export async function biomarkerGenes(b: BiomarkerRow): Promise<BiomarkerGene[]> {
  if (b.gene_ids.length === 0) return [];
  return safe(() => run<BiomarkerGene>(sql`SELECT g.id, g.symbol, g.name, g.is_cancer_gene FROM genes g WHERE g.id = ANY(${sql.param(b.gene_ids)}::text[]) ORDER BY g.symbol`), []);
}

export async function biomarkerVariants(b: BiomarkerRow): Promise<BiomarkerVariant[]> {
  if (b.variant_ids.length === 0) return [];
  return safe(() => run<BiomarkerVariant>(sql`SELECT v.id, v.slug, v.name, v.gene_symbol, v.variant_type FROM variants v WHERE v.id = ANY(${sql.param(b.variant_ids)}::text[]) ORDER BY v.gene_symbol, v.name`), []);
}

export interface BiomarkerCancerRow {
  cancer_id: string;
  slug: string;
  name: string;
  n: number;
  predictive: number;
  prognostic: number;
  diagnostic: number;
  level_a: number;
  level_b: number;
  level_c: number;
  level_d: number;
  level_e: number;
}

/** Associated cancers = mapped cancers of the in-scope evidence, with counts by type and native level. */
export async function biomarkerCancers(b: BiomarkerRow): Promise<BiomarkerCancerRow[]> {
  const rows = await safe(
    () =>
      run<Record<keyof BiomarkerCancerRow, string>>(sql`
        ${scopeById(b.id)}
        SELECT c.id AS cancer_id, c.slug, c.canonical_name AS name, count(*) AS n,
          count(*) FILTER (WHERE e.evidence_type = 'PREDICTIVE') AS predictive, count(*) FILTER (WHERE e.evidence_type = 'PROGNOSTIC') AS prognostic, count(*) FILTER (WHERE e.evidence_type = 'DIAGNOSTIC') AS diagnostic,
          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 = 'D') AS level_d, count(*) FILTER (WHERE e.evidence_level = 'E') AS level_e
        FROM b JOIN civic_evidence_items e ON ${EV_SCOPE} JOIN cancers c ON c.id = e.cancer_id
        GROUP BY c.id, c.slug, c.canonical_name ORDER BY n DESC, c.canonical_name`),
    [],
  );
  return rows.map((r) => ({ cancer_id: r.cancer_id, slug: r.slug, name: r.name, n: Number(r.n), predictive: Number(r.predictive), prognostic: Number(r.prognostic), diagnostic: Number(r.diagnostic), level_a: Number(r.level_a), level_b: Number(r.level_b), level_c: Number(r.level_c), level_d: Number(r.level_d), level_e: Number(r.level_e) }));
}

export interface BiomarkerDrugRow {
  drug_id: string;
  slug: string;
  name: string;
  kind: string | null;
  /** PREDICTIVE CIViC items in scope naming this therapy. */
  evidence_n: number;
  sensitivity: number;
  resistance: number;
  /** Best (lowest letter) native CIViC level among those items; null when only knowledge edges link the drug. */
  best_level: string | null;
  /** Active PREDICTS_RESPONSE_TO knowledge edges from in-scope variants to this drug. */
  edge_n: number;
  edge_sensitivity: number;
  edge_resistance: number;
  cancer_names: string[];
  cancer_slugs: string[];
}

/** Drugs with predictive evidence: CIViC therapies ∪ knowledge-edge targets, with direction counts. */
export async function biomarkerDrugs(b: BiomarkerRow): Promise<BiomarkerDrugRow[]> {
  const rows = await safe(
    () =>
      run<Record<string, unknown>>(sql`
        ${scopeById(b.id)}, ${DSET},
        ev AS (
          SELECT t AS drug_id, e.significance, e.evidence_level, e.cancer_id
          FROM b JOIN civic_evidence_items e ON ${EV_SCOPE} AND e.evidence_type = 'PREDICTIVE' CROSS JOIN LATERAL unnest(e.therapy_ids) t),
        ke AS (
          SELECT k.target_entity_id AS drug_id, k.direction
          FROM b JOIN variants v ON ${VAR_SCOPE}
          JOIN knowledge_edges k ON k.source_entity_type = 'variant' AND k.source_entity_id = v.id AND k.target_entity_type = 'drug' AND k.relationship_type = 'PREDICTS_RESPONSE_TO' AND k.status = 'active')
        SELECT d.id AS drug_id, d.slug, d.name, d.kind,
          (SELECT count(*) FROM ev WHERE ev.drug_id = d.id) AS evidence_n,
          (SELECT count(*) FROM ev WHERE ev.drug_id = d.id AND ev.significance ILIKE '%SENSITIV%') AS sensitivity,
          (SELECT count(*) FROM ev WHERE ev.drug_id = d.id AND ev.significance ILIKE '%RESIST%') AS resistance,
          (SELECT min(ev.evidence_level) FROM ev WHERE ev.drug_id = d.id) AS best_level,
          (SELECT count(*) FROM ke WHERE ke.drug_id = d.id) AS edge_n,
          (SELECT count(*) FROM ke WHERE ke.drug_id = d.id AND ke.direction = 'sensitivity') AS edge_sensitivity,
          (SELECT count(*) FROM ke WHERE ke.drug_id = d.id AND ke.direction = 'resistance') AS edge_resistance,
          coalesce((SELECT array_agg(DISTINCT c.canonical_name ORDER BY c.canonical_name) FROM ev JOIN cancers c ON c.id = ev.cancer_id WHERE ev.drug_id = d.id), '{}') AS cancer_names,
          coalesce((SELECT array_agg(DISTINCT c.slug ORDER BY c.slug) FROM ev JOIN cancers c ON c.id = ev.cancer_id WHERE ev.drug_id = d.id), '{}') AS cancer_slugs
        FROM dset JOIN drugs d ON d.id = dset.drug_id
        ORDER BY evidence_n DESC, edge_n DESC, d.name`),
    [],
  );
  return rows.map((r) => ({
    drug_id: r.drug_id as string,
    slug: r.slug as string,
    name: r.name as string,
    kind: (r.kind as string | null) ?? null,
    evidence_n: Number(r.evidence_n),
    sensitivity: Number(r.sensitivity),
    resistance: Number(r.resistance),
    best_level: (r.best_level as string | null) ?? null,
    edge_n: Number(r.edge_n),
    edge_sensitivity: Number(r.edge_sensitivity),
    edge_resistance: Number(r.edge_resistance),
    cancer_names: (r.cancer_names as string[]) ?? [],
    cancer_slugs: (r.cancer_slugs as string[]) ?? [],
  }));
}

/* Evidence table rows — same shape as lib/queries/evidence.ts so <EvidenceTable> renders them. */
const EVIDENCE_SELECT = sql`
  SELECT e.id, e.civic_id, e.name, e.molecular_profile_id, e.molecular_profile_name, e.gene_symbols, e.gene_ids, e.variant_ids, e.disease_name, e.cancer_id, e.cancer_match_type,
         e.therapy_names, e.therapy_ids, e.therapy_interaction_type, e.evidence_type, e.evidence_level, e.evidence_direction, e.significance, e.evidence_rating, e.status,
         e.pmid, e.source_citation, e.provenance_id, e.updated_at,
         c.slug AS cancer_slug, c.canonical_name AS cancer_name,
         (SELECT array_agg(v.slug ORDER BY v.slug) FROM variants v WHERE v.id = ANY(e.variant_ids)) AS variant_slugs,
         (SELECT array_agg(coalesce(v.gene_symbol || ' ', '') || v.name ORDER BY v.slug) FROM variants v WHERE v.id = ANY(e.variant_ids)) AS variant_names,
         (SELECT array_agg(d.slug ORDER BY d.slug) FROM drugs d WHERE d.id = ANY(e.therapy_ids)) AS therapy_slugs,
         (SELECT array_agg(d.name ORDER BY d.slug) FROM drugs d WHERE d.id = ANY(e.therapy_ids)) AS therapy_slug_names,
         left(e.description, ${EVIDENCE_DESCRIPTION_CHARS}) AS description
  FROM b JOIN civic_evidence_items e ON ${EV_SCOPE} LEFT JOIN cancers c ON c.id = e.cancer_id`;

export type BiomarkerEvidenceType = 'PREDICTIVE' | 'PROGNOSTIC' | 'DIAGNOSTIC';

export async function biomarkerEvidence(b: BiomarkerRow, opts: { type?: BiomarkerEvidenceType; page: number; pageSize: number }): Promise<EvidenceItem[]> {
  const typeFilter = opts.type ? sql`WHERE e.evidence_type = ${opts.type}` : sql``;
  return safe(
    () =>
      run<EvidenceItem>(sql`${scopeById(b.id)} ${EVIDENCE_SELECT} ${typeFilter}
        ORDER BY c.canonical_name NULLS LAST, e.disease_name NULLS LAST, array_to_string(e.therapy_names, '+'), e.evidence_level NULLS LAST, e.civic_id
        LIMIT ${opts.pageSize} OFFSET ${(Math.max(1, opts.page) - 1) * opts.pageSize}`),
    [] as EvidenceItem[],
  );
}
export async function biomarkerEvidenceCount(b: BiomarkerRow, type?: BiomarkerEvidenceType): Promise<number> {
  const typeFilter = type ? sql`AND e.evidence_type = ${type}` : sql``;
  const r = await safe(() => run<{ n: string }>(sql`${scopeById(b.id)} SELECT count(*) AS n FROM b JOIN civic_evidence_items e ON ${EV_SCOPE} ${typeFilter}`), [{ n: '0' }]);
  return Number(r[0]?.n ?? 0);
}

export type BiomarkerApprovalRow = ApprovalRow & { matched_by: 'drug' | 'indication' | 'both' };

/** Approvals of the scope drugs (matched_by = drug) and/or whose indication names the biomarker (matched_by = indication). */
export async function biomarkerApprovals(b: BiomarkerRow): Promise<BiomarkerApprovalRow[]> {
  return safe(
    () =>
      run<BiomarkerApprovalRow>(sql`
        ${scopeById(b.id)}, ${DSET}
        SELECT a.*, a.raw->>'dpdStatus' AS source_status, d.slug AS drug_slug, d.name AS drug_name, c.slug AS cancer_slug, c.canonical_name AS cancer_name, s.slug AS source_slug, s.name AS source_name,
          CASE WHEN a.drug_id IN (SELECT d2.drug_id FROM dset d2) AND ${TERM_MATCH} THEN 'both' WHEN a.drug_id IN (SELECT d2.drug_id FROM dset d2) THEN 'drug' ELSE 'indication' END AS matched_by
        FROM b, drug_approvals a JOIN drugs d ON d.id = a.drug_id LEFT JOIN cancers c ON c.id = a.cancer_id JOIN sources s ON s.id = a.source_id
        WHERE a.drug_id IN (SELECT d2.drug_id FROM dset d2) OR ${TERM_MATCH}
        ORDER BY a.tumor_agnostic DESC, d.name, a.jurisdiction, a.approval_date DESC NULLS LAST LIMIT 500`),
    [] as BiomarkerApprovalRow[],
  );
}

export interface BiomarkerTrialCounts {
  total: number;
  active: number;
  recruiting: number;
  phase3: number;
}

/** Trials of the scope drugs in the scope cancers (counts by status/phase). */
export async function biomarkerTrialCounts(b: BiomarkerRow): Promise<BiomarkerTrialCounts> {
  const r = await safe(
    () =>
      run<{ total: string; active: string; recruiting: string; phase3: string }>(sql`
        ${scopeById(b.id)}, ${DSET}, ${CSET}, ${TSET}
        SELECT count(*) AS total, count(*) FILTER (WHERE ${ACTIVE}) AS active, count(*) FILTER (WHERE t.overall_status = 'RECRUITING') AS recruiting,
               count(*) FILTER (WHERE ${ACTIVE} AND 'PHASE3' = ANY(t.phases)) AS phase3
        FROM tset x JOIN clinical_trials t ON t.id = x.trial_id`),
    [{ total: '0', active: '0', recruiting: '0', phase3: '0' }],
  );
  const x = r[0]!;
  return { total: Number(x.total), active: Number(x.active), recruiting: Number(x.recruiting), phase3: Number(x.phase3) };
}

const TRIAL_LIST_COLUMNS = sql`t.id, t.nct_id, t.brief_title, t.acronym, t.phases, t.overall_status, t.enrollment_count, t.lead_sponsor, t.lead_sponsor_class, t.countries, t.last_update_posted_date, t.updated_at`;

/** Active trials in scope, most recently updated first. */
export async function biomarkerActiveTrials(b: BiomarkerRow, p: { page: number; pageSize: number }): Promise<TrialListRow[]> {
  return safe(
    () =>
      run<TrialListRow>(sql`
        ${scopeById(b.id)}, ${DSET}, ${CSET}, ${TSET}
        SELECT ${TRIAL_LIST_COLUMNS} FROM tset x JOIN clinical_trials t ON t.id = x.trial_id WHERE ${ACTIVE}
        ORDER BY t.last_update_posted_date DESC NULLS LAST, t.nct_id LIMIT ${p.pageSize} OFFSET ${(Math.max(1, p.page) - 1) * p.pageSize}`),
    [] as TrialListRow[],
  );
}

/** Which entity type / ids the literature block should query (variants for molecular markers, genes otherwise). */
export function literatureScope(b: BiomarkerRow): { entityType: 'variant' | 'gene'; ids: string[] } {
  return b.variant_ids.length ? { entityType: 'variant', ids: b.variant_ids } : { entityType: 'gene', ids: b.gene_ids };
}

/** Why a derived block is empty for non-gene markers (rendered in the EmptyState). */
export function noScopeReason(b: BiomarkerRow): string | null {
  if (b.gene_ids.length || b.variant_ids.length) return null;
  return b.measurement.notes ?? 'This biomarker is not anchored to a gene or variant entity, so gene-derived links cannot be computed.';
}