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

export interface CancerCore {
  id: string;
  slug: string;
  canonical_name: string;
  short_name: string | null;
  entity_type: string;
  malignant: boolean;
  solid_tumor: boolean;
  hematologic: boolean;
  pediatric_relevant: boolean;
  rare_cancer: boolean | null;
  top_level: boolean;
  description: string | null;
  description_provenance_id: number | null;
  primary_ncit_code: string | null;
  primary_oncotree_code: string | null;
  depth: number;
  status: string;
  merged_into: string | null;
  deprecated_reason: string | null;
  classification_version: string | null;
  semantic_types: string[];
  created_at: Date;
  updated_at: Date;
}

export interface Counters {
  trial_count: number;
  active_trial_count: number;
  recruiting_trial_count: number;
  phase3_trial_count: number;
  publication_count: number;
  publication_count_5y: number;
  publication_count_12m: number;
  gene_count: number;
  variant_count: number;
  drug_count: number;
  approved_drug_count: number;
  evidence_count: number;
  cohort_count: number;
  subtype_count: number;
  descendant_count: number;
  epidemiology_obs_count: number;
  survival_obs_count: number;
  completeness: Record<string, number>;
  updated_at: Date; // schema field `computedAt` is declared with the updatedAt() helper → column `updated_at`
}

export const ENTITY_TYPES = ['cancer', 'cancer_family', 'histology', 'subtype', 'molecular_subtype', 'hematologic_malignancy', 'precursor_condition', 'other'] as const;
export const SORTS = ['name', 'trials', 'publications', 'evidence', 'descendants'] as const;
export type Sort = (typeof SORTS)[number];

export interface ExplorerFilters {
  q: string;
  level: 'top' | 'all';
  entityType: string;
  malignant: boolean | null;
  hematologic: boolean | null;
  pediatric: boolean | null;
  site: string; // anatomical site slug
  sort: Sort;
  page: number;
  pageSize: number;
}

export interface ExplorerRow extends CancerCore {
  parents: Array<{ slug: string; name: string }> | null;
  active_trial_count: number | null;
  publication_count_5y: number | null;
  evidence_count: number | null;
  descendant_count: number | null;
  epidemiology_obs_count: number | null;
  survival_obs_count: number | null;
  gene_count: number | null;
  drug_count: number | null;
  child_count: number;
}

function explorerWhere(f: ExplorerFilters) {
  const parts = [sql`c.status = 'active'`];
  if (f.level === 'top') parts.push(sql`c.top_level`);
  if (f.q) {
    const q = f.q.trim();
    parts.push(sql`(c.canonical_name ILIKE ${'%' + q + '%'} OR c.slug ILIKE ${'%' + q + '%'} OR c.primary_oncotree_code ILIKE ${q} OR c.primary_ncit_code ILIKE ${q} OR c.id = ${q}
      OR EXISTS (SELECT 1 FROM cancer_aliases a WHERE a.cancer_id = c.id AND a.alias ILIKE ${'%' + q + '%'})
      OR EXISTS (SELECT 1 FROM cancer_codes k WHERE k.cancer_id = c.id AND k.code ILIKE ${q}))`);
  }
  if (f.entityType) parts.push(sql`c.entity_type = ${f.entityType}`);
  if (f.malignant != null) parts.push(sql`c.malignant = ${f.malignant}`);
  if (f.hematologic != null) parts.push(sql`c.hematologic = ${f.hematologic}`);
  if (f.pediatric != null) parts.push(sql`c.pediatric_relevant = ${f.pediatric}`);
  if (f.site) parts.push(sql`EXISTS (SELECT 1 FROM cancer_anatomy ca JOIN anatomical_sites s ON s.id = ca.site_id WHERE ca.cancer_id = c.id AND s.slug = ${f.site})`);
  return sql.join(parts, sql` AND `);
}

export async function explorerCount(f: ExplorerFilters): Promise<number> {
  const rows = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM cancers c WHERE ${explorerWhere(f)}`), [{ n: '0' }]);
  return Number(rows[0]?.n ?? 0);
}

export async function explorerRows(f: ExplorerFilters): Promise<ExplorerRow[]> {
  const order =
    f.sort === 'trials'
      ? sql`coalesce(ec.active_trial_count, 0) DESC, c.canonical_name`
      : f.sort === 'publications'
        ? sql`coalesce(ec.publication_count_5y, 0) DESC, c.canonical_name`
        : f.sort === 'evidence'
          ? sql`coalesce(ec.evidence_count, 0) DESC, c.canonical_name`
          : f.sort === 'descendants'
            ? sql`coalesce(ec.descendant_count, child.n) DESC, c.canonical_name`
            : sql`c.canonical_name`;
  return safe(
    () =>
      run<ExplorerRow>(sql`
        SELECT c.*, ec.active_trial_count, ec.publication_count_5y, ec.evidence_count, ec.descendant_count, ec.epidemiology_obs_count, ec.survival_obs_count, ec.gene_count, ec.drug_count,
               child.n AS child_count,
               (SELECT json_agg(json_build_object('slug', p.slug, 'name', p.canonical_name) ORDER BY p.canonical_name)
                  FROM (SELECT DISTINCT pc.slug, pc.canonical_name FROM cancer_hierarchy h JOIN cancers pc ON pc.id = h.parent_id WHERE h.child_id = c.id LIMIT 3) p) AS parents
        FROM cancers c
        LEFT JOIN entity_counters ec ON ec.entity_type = 'cancer' AND ec.entity_id = c.id
        LEFT JOIN LATERAL (SELECT count(*) AS n FROM cancer_hierarchy h WHERE h.parent_id = c.id) child ON true
        WHERE ${explorerWhere(f)}
        ORDER BY ${order}
        LIMIT ${f.pageSize} OFFSET ${(f.page - 1) * f.pageSize}`),
    [] as ExplorerRow[],
  );
}

export async function listAnatomicalSites(): Promise<Array<{ id: string; slug: string; name: string; system: string | null; n: number }>> {
  const rows = await safe(
    () => run<{ id: string; slug: string; name: string; system: string | null; n: string }>(sql`SELECT s.id, s.slug, s.name, s.system, (SELECT count(*) FROM cancer_anatomy ca WHERE ca.site_id = s.id) AS n FROM anatomical_sites s ORDER BY s.name`),
    [] as Array<{ id: string; slug: string; name: string; system: string | null; n: string }>,
  );
  return rows.map((r) => ({ ...r, n: Number(r.n) }));
}

// ---------- Detail ----------

export async function getCancerBySlug(slug: string): Promise<CancerCore | null> {
  const rows = await safe(() => run<CancerCore>(sql`SELECT * FROM cancers WHERE slug = ${slug} LIMIT 1`), [] as CancerCore[]);
  return rows[0] ?? null;
}

export async function getCancerById(id: string): Promise<CancerCore | null> {
  const rows = await safe(() => run<CancerCore>(sql`SELECT * FROM cancers WHERE id = ${id} LIMIT 1`), [] as CancerCore[]);
  return rows[0] ?? null;
}

export async function getCounters(cancerId: string): Promise<Counters | null> {
  const rows = await safe(() => run<Counters>(sql`SELECT * FROM entity_counters WHERE entity_type = 'cancer' AND entity_id = ${cancerId}`), [] as Counters[]);
  return rows[0] ?? null;
}

export interface CodeRow {
  system: string;
  code: string;
  match_type: string;
  source_slug: string | null;
}
export async function getCodes(cancerId: string): Promise<CodeRow[]> {
  return safe(() => run<CodeRow>(sql`SELECT k.system, k.code, k.match_type, s.slug AS source_slug FROM cancer_codes k LEFT JOIN sources s ON s.id = k.source_id WHERE k.cancer_id = ${cancerId} ORDER BY k.system, k.code`), [] as CodeRow[]);
}

export interface AliasRow {
  alias: string;
  alias_type: string;
  source_terminology: string | null;
  source_slug: string | null;
}
export async function getAliases(cancerId: string): Promise<AliasRow[]> {
  return safe(() => run<AliasRow>(sql`SELECT a.alias, a.alias_type, a.source_terminology, s.slug AS source_slug FROM cancer_aliases a LEFT JOIN sources s ON s.id = a.source_id WHERE a.cancer_id = ${cancerId} ORDER BY a.alias_type, a.alias`), [] as AliasRow[]);
}

export interface RelRow {
  id: string;
  slug: string;
  canonical_name: string;
  entity_type: string;
  hierarchy_type: string;
  malignant: boolean;
  child_count: number;
}
export async function getParents(cancerId: string): Promise<RelRow[]> {
  return safe(
    () =>
      run<RelRow>(sql`
        SELECT p.id, p.slug, p.canonical_name, p.entity_type, h.hierarchy_type, p.malignant, (SELECT count(*) FROM cancer_hierarchy x WHERE x.parent_id = p.id)::int AS child_count
        FROM cancer_hierarchy h JOIN cancers p ON p.id = h.parent_id WHERE h.child_id = ${cancerId} ORDER BY h.hierarchy_type, p.canonical_name`),
    [] as RelRow[],
  );
}
export async function getChildren(cancerId: string, limit = 500): Promise<RelRow[]> {
  return safe(
    () =>
      run<RelRow>(sql`
        SELECT c.id, c.slug, c.canonical_name, c.entity_type, h.hierarchy_type, c.malignant, (SELECT count(*) FROM cancer_hierarchy x WHERE x.parent_id = c.id)::int AS child_count
        FROM cancer_hierarchy h JOIN cancers c ON c.id = h.child_id WHERE h.parent_id = ${cancerId} ORDER BY h.hierarchy_type, c.canonical_name LIMIT ${limit}`),
    [] as RelRow[],
  );
}

/** Shortest path from a root (node without parent) to this cancer, preferring NCIt, then OncoTree. */
export async function getBreadcrumbPath(cancerId: string): Promise<Array<{ id: string; slug: string; canonical_name: string }>> {
  const rows = await safe(
    () =>
      run<{ path_ids: string[]; hierarchy_type: string }>(sql`
        WITH RECURSIVE up AS (
          SELECT c.id, ARRAY[c.id]::varchar[] AS path_ids, NULL::text AS hierarchy_type, 0 AS depth FROM cancers c WHERE c.id = ${cancerId}
          UNION ALL
          SELECT h.parent_id, h.parent_id || up.path_ids, coalesce(up.hierarchy_type, h.hierarchy_type), up.depth + 1
          FROM up JOIN cancer_hierarchy h ON h.child_id = up.id
          WHERE up.depth < 14 AND NOT (h.parent_id = ANY(up.path_ids)) AND (up.hierarchy_type IS NULL OR h.hierarchy_type = up.hierarchy_type)
        )
        SELECT path_ids, hierarchy_type FROM up
        WHERE NOT EXISTS (SELECT 1 FROM cancer_hierarchy h2 WHERE h2.child_id = up.id AND (up.hierarchy_type IS NULL OR h2.hierarchy_type = up.hierarchy_type))
        ORDER BY (hierarchy_type = 'ncit') DESC, array_length(path_ids, 1) ASC LIMIT 1`),
    [] as Array<{ path_ids: string[]; hierarchy_type: string }>,
  );
  const ids = rows[0]?.path_ids ?? [];
  if (ids.length <= 1) return [];
  const nodes = await safe(
    () => run<{ id: string; slug: string; canonical_name: string }>(sql`SELECT id, slug, canonical_name FROM cancers WHERE id IN (${sql.join(ids.map((i) => sql`${i}`), sql`, `)})`),
    [] as Array<{ id: string; slug: string; canonical_name: string }>,
  );
  const byId = new Map(nodes.map((n) => [n.id, n]));
  return ids.map((i) => byId.get(i)).filter((n): n is { id: string; slug: string; canonical_name: string } => !!n);
}

/** All descendant ids (inclusive) across hierarchy types — used for trial/evidence roll-ups. */
export async function getDescendantIds(cancerId: string): Promise<string[]> {
  const rows = await safe(
    () =>
      run<{ id: string }>(sql`
        WITH RECURSIVE d AS (
          SELECT ${cancerId}::varchar AS id, 0 AS depth
          UNION
          SELECT h.child_id, d.depth + 1 FROM d JOIN cancer_hierarchy h ON h.parent_id = d.id WHERE d.depth < 12
        ) SELECT DISTINCT id FROM d`),
    [{ id: cancerId }],
  );
  return rows.map((r) => r.id);
}

export interface AnatomyRow {
  site_id: string;
  slug: string;
  name: string;
  system: string | null;
  relation: string;
  ncit_code: string | null;
}
export async function getAnatomy(cancerId: string): Promise<AnatomyRow[]> {
  return safe(() => run<AnatomyRow>(sql`SELECT s.id AS site_id, s.slug, s.name, s.system, ca.relation, s.ncit_code FROM cancer_anatomy ca JOIN anatomical_sites s ON s.id = ca.site_id WHERE ca.cancer_id = ${cancerId} ORDER BY ca.relation, s.name`), [] as AnatomyRow[]);
}

export interface ChangeEvent {
  id: number;
  kind: string;
  summary: string;
  before: unknown;
  after: unknown;
  ingest_run_id: string | null;
  created_at: Date;
}
export async function getChangeEvents(entityType: string, entityId: string, limit = 50): Promise<ChangeEvent[]> {
  return safe(() => run<ChangeEvent>(sql`SELECT id, kind, summary, before, after, ingest_run_id, created_at FROM change_events WHERE entity_type = ${entityType} AND entity_id = ${entityId} ORDER BY created_at DESC LIMIT ${limit}`), [] as ChangeEvent[]);
}

/** Sources (with run ids) that contributed anything to this cancer — union across tables. */
export async function getContributingSources(cancerId: string, descendantIds: string[]): Promise<Array<{ slug: string; name: string; license_status: string; kinds: string[]; run_ids: string[]; last_retrieved: Date | null }>> {
  const ids = sql.join(descendantIds.map((i) => sql`${i}`), sql`, `);
  return safe(
    () =>
      run<{ slug: string; name: string; license_status: string; kinds: string[]; run_ids: string[]; last_retrieved: Date | null }>(sql`
        WITH contrib AS (
          SELECT k.source_id, 'codes' AS kind, NULL::text AS run_id, NULL::timestamptz AS at FROM cancer_codes k WHERE k.cancer_id = ${cancerId} AND k.source_id IS NOT NULL
          UNION ALL SELECT a.source_id, 'aliases', NULL, NULL FROM cancer_aliases a WHERE a.cancer_id = ${cancerId} AND a.source_id IS NOT NULL
          UNION ALL SELECT h.source_id, 'hierarchy', NULL, NULL FROM cancer_hierarchy h WHERE (h.child_id = ${cancerId} OR h.parent_id = ${cancerId}) AND h.source_id IS NOT NULL
          UNION ALL SELECT r.source_id, 'source_records', r.last_seen_run, r.retrieved_at FROM source_records r WHERE r.canonical_type = 'cancer' AND r.canonical_id = ${cancerId}
          UNION ALL SELECT o.source_id, 'epidemiology', o.ingest_run_id, o.updated_at FROM epidemiology_observations o WHERE o.cancer_id = ${cancerId}
          UNION ALL SELECT o.source_id, 'survival', o.ingest_run_id, o.updated_at FROM survival_observations o WHERE o.cancer_id = ${cancerId}
          UNION ALL SELECT p.source_id, 'evidence', e.ingest_run_id, e.updated_at FROM civic_evidence_items e JOIN provenance p ON p.id = e.provenance_id WHERE e.cancer_id IN (${ids})
          UNION ALL SELECT gc.source_id, 'genomic_cohorts', NULL, gc.updated_at FROM genomic_cohorts gc WHERE gc.cancer_id IN (${ids})
          UNION ALL SELECT p.source_id, 'literature_counts', p.ingest_run_id, lc.updated_at FROM literature_counts lc JOIN provenance p ON p.id = lc.provenance_id WHERE lc.cancer_id = ${cancerId}
          UNION ALL SELECT da.source_id, 'approvals', NULL, da.updated_at FROM drug_approvals da WHERE da.cancer_id IN (${ids})
          UNION ALL SELECT p.source_id, 'description', p.ingest_run_id, p.retrieved_at FROM cancers c JOIN provenance p ON p.id = c.description_provenance_id WHERE c.id = ${cancerId}
        )
        SELECT s.slug, s.name, s.license_status, array_agg(DISTINCT c.kind) AS kinds, array_remove(array_agg(DISTINCT c.run_id), NULL) AS run_ids, max(c.at) AS last_retrieved
        FROM contrib c JOIN sources s ON s.id = c.source_id GROUP BY s.id ORDER BY s.name`),
    [],
  );
}

/** Top-level ranking set: TOP_LEVEL_CANCERS resolved through NCIt codes (or the top_level flag). */
export async function resolveTopLevel(ncitCodes: string[]): Promise<Map<string, { id: string; slug: string; canonical_name: string; descendant_count: number | null; active_trial_count: number | null; publication_count_5y: number | null }>> {
  if (ncitCodes.length === 0) return new Map();
  const rows = await safe(
    () =>
      run<{ ncit: string; id: string; slug: string; canonical_name: string; descendant_count: number | null; active_trial_count: number | null; publication_count_5y: number | null }>(sql`
        SELECT k.code AS ncit, c.id, c.slug, c.canonical_name, ec.descendant_count, ec.active_trial_count, ec.publication_count_5y
        FROM cancer_codes k JOIN cancers c ON c.id = k.cancer_id
        LEFT JOIN entity_counters ec ON ec.entity_type = 'cancer' AND ec.entity_id = c.id
        WHERE k.system = 'ncit' AND k.code IN (${sql.join(ncitCodes.map((c) => sql`${c}`), sql`, `)}) AND c.status = 'active'`),
    [],
  );
  return new Map(rows.map((r) => [r.ncit, r]));
}

export async function cancerSlugsForSitemap(offset: number, limit: number): Promise<Array<{ slug: string; updated_at: Date }>> {
  return safe(() => run<{ slug: string; updated_at: Date }>(sql`SELECT slug, updated_at FROM cancers WHERE status = 'active' ORDER BY id LIMIT ${limit} OFFSET ${offset}`), []);
}
export async function countActiveCancers(): Promise<number> {
  const r = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM cancers WHERE status = 'active'`), [{ n: '0' }]);
  return Number(r[0]?.n ?? 0);
}