import { sql } from 'drizzle-orm';
import type { Database } from '@cancerindex/database';

export const MAX_HIERARCHY_DEPTH = 12;

/**
 * Cancer + all descendants across every hierarchy type (ncit, oncotree, …), depth ≤ 12 — the same
 * traversal the ranking counters use (packages/ranking/src/counters.ts) so API numbers match rankings.
 */
export async function descendantIds(db: Database, cancerId: string, hierarchyType?: string): Promise<string[]> {
  const rows = await db.execute<{ 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 < ${MAX_HIERARCHY_DEPTH} ${hierarchyType ? sql`AND h.hierarchy_type = ${hierarchyType}` : sql``}
    )
    SELECT DISTINCT id FROM d`);
  return rows.map((r) => r.id);
}

/** Ancestors (for breadcrumbs), nearest first, depth ≤ 12. */
export async function ancestorChain(db: Database, cancerId: string, hierarchyType = 'ncit'): Promise<Array<{ id: string; slug: string; name: string; depth: number }>> {
  const rows = await db.execute<{ id: string; slug: string; name: string; depth: number }>(sql`
    WITH RECURSIVE a AS (
      SELECT h.parent_id AS id, 1 AS depth FROM cancer_hierarchy h WHERE h.child_id = ${cancerId} AND h.hierarchy_type = ${hierarchyType}
      UNION
      SELECT h.parent_id, a.depth + 1 FROM a JOIN cancer_hierarchy h ON h.child_id = a.id AND h.hierarchy_type = ${hierarchyType}
      WHERE a.depth < ${MAX_HIERARCHY_DEPTH}
    )
    SELECT DISTINCT ON (a.id) a.id, c.slug, c.canonical_name AS name, a.depth FROM a JOIN cancers c ON c.id = a.id ORDER BY a.id, a.depth`);
  return rows.map((r) => ({ ...r, depth: Number(r.depth) })).sort((x, y) => x.depth - y.depth);
}