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

export interface TreeNode {
  id: string;
  slug: string;
  canonical_name: string;
  entity_type: string;
  malignant: boolean;
  primary_oncotree_code: string | null;
  primary_ncit_code: string | null;
  child_count: number;
}

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

/** Root nodes of a hierarchy: nodes that are parents but have no parent of the same type. */
export async function rootsOf(hierarchyType: string, limit = 400): Promise<TreeNode[]> {
  return safe(
    () =>
      run<TreeNode>(sql`
        SELECT c.id, c.slug, c.canonical_name, c.entity_type, c.malignant, c.primary_oncotree_code, c.primary_ncit_code,
               (SELECT count(*) FROM cancer_hierarchy x WHERE x.parent_id = c.id AND x.hierarchy_type = ${hierarchyType})::int AS child_count
        FROM cancers c
        WHERE c.status = 'active'
          AND EXISTS (SELECT 1 FROM cancer_hierarchy h WHERE h.parent_id = c.id AND h.hierarchy_type = ${hierarchyType})
          AND NOT EXISTS (SELECT 1 FROM cancer_hierarchy h WHERE h.child_id = c.id AND h.hierarchy_type = ${hierarchyType})
        ORDER BY c.canonical_name LIMIT ${limit}`),
    [] as TreeNode[],
  );
}

export async function childrenOf(parentId: string, hierarchyType: string, limit = 500): Promise<TreeNode[]> {
  return safe(
    () =>
      run<TreeNode>(sql`
        SELECT c.id, c.slug, c.canonical_name, c.entity_type, c.malignant, c.primary_oncotree_code, c.primary_ncit_code,
               (SELECT count(*) FROM cancer_hierarchy x WHERE x.parent_id = c.id AND x.hierarchy_type = ${hierarchyType})::int AS child_count
        FROM cancer_hierarchy h JOIN cancers c ON c.id = h.child_id
        WHERE h.parent_id = ${parentId} AND h.hierarchy_type = ${hierarchyType} AND c.status = 'active'
        ORDER BY c.canonical_name LIMIT ${limit}`),
    [] as TreeNode[],
  );
}

/** Orphan nodes (no parent in the given hierarchy and no children) — listed separately so nothing is hidden. */
export async function orphanCount(hierarchyType: string): Promise<number> {
  const rows = await safe(
    () =>
      run<{ n: string }>(sql`
        SELECT count(*) AS n FROM cancers c WHERE c.status = 'active'
          AND NOT EXISTS (SELECT 1 FROM cancer_hierarchy h WHERE (h.parent_id = c.id OR h.child_id = c.id) AND h.hierarchy_type = ${hierarchyType})`),
    [{ n: '0' }],
  );
  return Number(rows[0]?.n ?? 0);
}

export interface SiteGroup {
  id: string;
  slug: string;
  name: string;
  system: string | null;
  ncit_code: string | null;
  cancers: Array<{ id: string; slug: string; canonical_name: string; entity_type: string; malignant: boolean; relation: string }>;
}

export async function anatomicalView(): Promise<SiteGroup[]> {
  const sites = await safe(() => run<{ id: string; slug: string; name: string; system: string | null; ncit_code: string | null }>(sql`SELECT id, slug, name, system, ncit_code FROM anatomical_sites ORDER BY name`), []);
  const links = await safe(
    () =>
      run<{ site_id: string; id: string; slug: string; canonical_name: string; entity_type: string; malignant: boolean; relation: string }>(sql`
        SELECT ca.site_id, c.id, c.slug, c.canonical_name, c.entity_type, c.malignant, ca.relation
        FROM cancer_anatomy ca JOIN cancers c ON c.id = ca.cancer_id WHERE c.status = 'active' ORDER BY c.canonical_name`),
    [],
  );
  const bySite = new Map<string, SiteGroup>();
  for (const s of sites) bySite.set(s.id, { ...s, cancers: [] });
  for (const l of links) bySite.get(l.site_id)?.cancers.push(l);
  return [...bySite.values()];
}