import 'server-only';
import { run, sql, safe } from '@/lib/db';

export interface EvidenceItem {
  id: number;
  civic_id: number;
  name: string | null;
  molecular_profile_id: number | null;
  molecular_profile_name: string | null;
  gene_symbols: string[];
  gene_ids: string[];
  variant_ids: string[];
  disease_name: string | null;
  cancer_id: string | null;
  cancer_slug: string | null;
  cancer_name: string | null;
  cancer_match_type: string | null;
  therapy_names: string[];
  therapy_ids: string[];
  therapy_interaction_type: string | null;
  evidence_type: string | null;
  evidence_level: string | null;
  evidence_direction: string | null;
  significance: string | null;
  evidence_rating: number | null;
  status: string | null;
  /** Excerpt (EVIDENCE_DESCRIPTION_CHARS), never the full text. */
  description: string | null;
  pmid: string | null;
  source_citation: string | null;
  provenance_id: number;
  updated_at: Date;
  variant_slugs: string[] | null;
  variant_names: string[] | null;
  therapy_slugs: string[] | null;
  /** Drug names aligned with therapy_slugs (same ORDER BY) — never zip with therapy_names, whose order is CIViC's. */
  therapy_slug_names: string[] | null;
}

/** Description excerpt length shipped per row (the full text is one click away at CIViC). */
export const EVIDENCE_DESCRIPTION_CHARS = 200;

const 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 civic_evidence_items e LEFT JOIN cancers c ON c.id = e.cancer_id`;

export const EVIDENCE_PAGE_SIZE = 50;

export interface Page {
  page: number;
  pageSize: number;
}

/**
 * Stable, group-preserving order so server-side pages never split a (profile → therapy) group at
 * random: molecular profile, then therapy, then level, then the CIViC id. `showCancer` contexts
 * (gene / drug / publication pages) sort by cancer first so the per-cancer grouping stays contiguous.
 */
const ORDER_BY_PROFILE = sql`ORDER BY coalesce(e.molecular_profile_name, e.name) NULLS LAST, array_to_string(e.therapy_names, '+'), e.evidence_level NULLS LAST, e.evidence_rating DESC NULLS LAST, e.civic_id`;
const ORDER_BY_CANCER = sql`ORDER BY c.canonical_name NULLS LAST, e.disease_name NULLS LAST, coalesce(e.molecular_profile_name, e.name) NULLS LAST, array_to_string(e.therapy_names, '+'), e.evidence_level NULLS LAST, e.civic_id`;

function pageClause(p: Page) {
  return sql`LIMIT ${p.pageSize} OFFSET ${(Math.max(1, p.page) - 1) * p.pageSize}`;
}

async function count(where: ReturnType<typeof sql>): Promise<number> {
  const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM civic_evidence_items e WHERE ${where}`), [{ n: '0' }]);
  return Number(r[0]?.n ?? 0);
}

const whereCancer = (cancerIds: string[]) => sql`e.cancer_id IN (${sql.join(cancerIds.map((i) => sql`${i}`), sql`, `)})`;
const whereVariant = (variantId: string) => sql`${variantId} = ANY(e.variant_ids)`;
const whereGene = (geneId: string, symbol: string) => sql`(${geneId} = ANY(e.gene_ids) OR ${symbol} = ANY(e.gene_symbols))`;
const whereDrug = (drugId: string) => sql`${drugId} = ANY(e.therapy_ids)`;
const wherePmid = (pmid: string) => sql`e.pmid = ${pmid}`;

export async function evidenceForCancer(cancerIds: string[], p: Page = { page: 1, pageSize: EVIDENCE_PAGE_SIZE }): Promise<EvidenceItem[]> {
  if (cancerIds.length === 0) return [];
  return safe(() => run<EvidenceItem>(sql`${SELECT} WHERE ${whereCancer(cancerIds)} ${ORDER_BY_PROFILE} ${pageClause(p)}`), [] as EvidenceItem[]);
}
export async function evidenceForCancerCount(cancerIds: string[]): Promise<number> {
  if (cancerIds.length === 0) return 0;
  return count(whereCancer(cancerIds));
}

/** Lightweight rows for the Drugs tab "therapies in evidence" aggregate: no description, no joins. */
export async function therapyMentionsForCancer(cancerIds: string[]): Promise<Array<{ slug: string; name: string; n: number; sensitivity: number; resistance: number }>> {
  if (cancerIds.length === 0) return [];
  const rows = await safe(
    () =>
      run<{ slug: string; name: string; n: string; sensitivity: string; resistance: string }>(sql`
        SELECT d.slug, d.name, count(*) AS n,
               count(*) FILTER (WHERE e.significance ILIKE '%SENSITIV%') AS sensitivity,
               count(*) FILTER (WHERE e.significance ILIKE '%RESIST%') AS resistance
        FROM civic_evidence_items e JOIN drugs d ON d.id = ANY(e.therapy_ids)
        WHERE ${whereCancer(cancerIds)} GROUP BY d.slug, d.name ORDER BY n DESC, d.name`),
    [],
  );
  return rows.map((r) => ({ slug: r.slug, name: r.name, n: Number(r.n), sensitivity: Number(r.sensitivity), resistance: Number(r.resistance) }));
}

export async function evidenceForVariant(variantId: string, p: Page = { page: 1, pageSize: EVIDENCE_PAGE_SIZE }): Promise<EvidenceItem[]> {
  return safe(() => run<EvidenceItem>(sql`${SELECT} WHERE ${whereVariant(variantId)} ${ORDER_BY_CANCER} ${pageClause(p)}`), [] as EvidenceItem[]);
}
export async function evidenceForVariantCount(variantId: string): Promise<number> {
  return count(whereVariant(variantId));
}

export async function evidenceForGene(geneId: string, symbol: string, p: Page = { page: 1, pageSize: EVIDENCE_PAGE_SIZE }): Promise<EvidenceItem[]> {
  return safe(() => run<EvidenceItem>(sql`${SELECT} WHERE ${whereGene(geneId, symbol)} ${ORDER_BY_PROFILE} ${pageClause(p)}`), [] as EvidenceItem[]);
}
export async function evidenceForGeneCount(geneId: string, symbol: string): Promise<number> {
  return count(whereGene(geneId, symbol));
}
/** Cancers appearing in a gene's evidence with item counts (facet chips above the paginated table). */
export async function evidenceCancersForGene(geneId: string, symbol: string, limit = 40): Promise<Array<{ slug: string; name: string; n: number }>> {
  const rows = await safe(
    () => run<{ slug: string; name: string; n: string }>(sql`SELECT c.slug, c.canonical_name AS name, count(*) AS n FROM civic_evidence_items e JOIN cancers c ON c.id = e.cancer_id WHERE ${whereGene(geneId, symbol)} GROUP BY c.slug, c.canonical_name ORDER BY n DESC, c.canonical_name LIMIT ${limit}`),
    [],
  );
  return rows.map((r) => ({ slug: r.slug, name: r.name, n: Number(r.n) }));
}

export async function evidenceForDrug(drugId: string, p: Page = { page: 1, pageSize: EVIDENCE_PAGE_SIZE }): Promise<EvidenceItem[]> {
  return safe(() => run<EvidenceItem>(sql`${SELECT} WHERE ${whereDrug(drugId)} ${ORDER_BY_CANCER} ${pageClause(p)}`), [] as EvidenceItem[]);
}
export async function evidenceForDrugCount(drugId: string): Promise<number> {
  return count(whereDrug(drugId));
}

export async function evidenceForPublication(pmid: string, p: Page = { page: 1, pageSize: EVIDENCE_PAGE_SIZE }): Promise<EvidenceItem[]> {
  return safe(() => run<EvidenceItem>(sql`${SELECT} WHERE ${wherePmid(pmid)} ${ORDER_BY_CANCER} ${pageClause(p)}`), [] as EvidenceItem[]);
}
export async function evidenceForPublicationCount(pmid: string): Promise<number> {
  return count(wherePmid(pmid));
}

export const EVIDENCE_LEVEL_LABEL: Record<string, string> = {
  A: 'A — Validated association',
  B: 'B — Clinical evidence',
  C: 'C — Case study',
  D: 'D — Preclinical evidence',
  E: 'E — Inferential association',
};

export type EvidenceGroupBy = 'variant' | 'cancer' | 'none';

export interface EvidenceGroup {
  key: string;
  label: string;
  href: string | null;
  genes: string[];
  unmapped: boolean;
  items: EvidenceItem[];
}

/** Therapy key used to collapse consecutive rows of the same therapy inside a group (〃 marker). */
export function therapyKey(e: EvidenceItem): string {
  return e.therapy_names.length ? e.therapy_names.join(' + ') : e.evidence_type === 'PREDICTIVE' ? 'Unspecified therapy' : `(${(e.evidence_type ?? 'evidence').toLowerCase()})`;
}

/**
 * Group evidence items (never collapsed to works/doesn't work). Groups and rows keep the incoming
 * (SQL) order so a paginated table reads the same way as the query orders it; within a variant
 * group, rows of the same therapy stay adjacent because the query orders by therapy.
 */
export function groupEvidence(items: EvidenceItem[], by: EvidenceGroupBy = 'variant'): EvidenceGroup[] {
  if (by === 'none') return items.length ? [{ key: 'all', label: '', href: null, genes: [], unmapped: false, items }] : [];
  const groups = new Map<string, EvidenceGroup>();
  for (const e of items) {
    let key: string;
    let g: EvidenceGroup | undefined;
    if (by === 'cancer') {
      key = e.cancer_id ?? `unmapped:${e.disease_name ?? 'unknown'}`;
      g = groups.get(key);
      if (!g) g = { key, label: e.cancer_name ?? e.disease_name ?? 'Unmapped disease', href: e.cancer_slug ? `/cancer/${e.cancer_slug}/evidence` : null, genes: [], unmapped: !e.cancer_slug, items: [] };
    } else {
      key = e.variant_ids.length ? e.variant_ids.join('+') : `mp-${e.molecular_profile_id ?? e.civic_id}`;
      g = groups.get(key);
      if (!g) g = { key, label: e.variant_names?.join(' + ') ?? e.molecular_profile_name ?? e.name ?? `CIViC EID${e.civic_id}`, href: e.variant_slugs?.length === 1 ? `/variant/${e.variant_slugs[0]!}` : null, genes: e.gene_symbols, unmapped: false, items: [] };
    }
    groups.set(key, g);
    g.items.push(e);
  }
  return [...groups.values()];
}