import { sql } from 'drizzle-orm';
import type { Database } from '@cancerindex/database';
import { isCiId, type IdNamespace } from '@cancerindex/shared';
import { NotFound } from './errors.js';

export type RefKind = 'id' | 'slug' | 'symbol' | 'nct' | 'pmid';

/**
 * Classify a public reference (pure, unit-tested): CI-<NS>-00000001 → id; NCT… → nct; digits → pmid;
 * otherwise a slug/symbol. Never accepts database integers as entity references (CLAUDE.md §6).
 */
export function classifyRef(ref: string, ns: IdNamespace): RefKind {
  const r = ref.trim();
  if (isCiId(r, ns)) return 'id';
  if (ns === 'TRIAL' && /^NCT\d{8}$/i.test(r)) return 'nct';
  if (ns === 'PUB' && /^\d{1,9}$/.test(r)) return 'pmid';
  if (ns === 'GENE') return 'symbol';
  return 'slug';
}

async function one<T extends Record<string, unknown>>(db: Database, query: ReturnType<typeof sql>): Promise<T | null> {
  const rows = await db.execute<T>(query);
  return (rows[0] as T | undefined) ?? null;
}

/** Cancer by CI-CAN id or slug. Follows `merged_into` so old ids keep resolving (§70). */
export async function resolveCancer(db: Database, ref: string): Promise<{ id: string; slug: string; status: string }> {
  const kind = classifyRef(ref, 'CAN');
  const row =
    kind === 'id'
      ? await one<{ id: string; slug: string; status: string; merged_into: string | null }>(db, sql`SELECT id, slug, status, merged_into FROM cancers WHERE id = ${ref}`)
      : await one<{ id: string; slug: string; status: string; merged_into: string | null }>(db, sql`SELECT id, slug, status, merged_into FROM cancers WHERE slug = ${ref.toLowerCase()}`);
  if (!row) throw new NotFound('cancer', ref);
  if (row.status === 'merged' && row.merged_into) return resolveCancer(db, row.merged_into);
  return { id: row.id, slug: row.slug, status: row.status };
}

/** Gene by CI-GENE id, HGNC symbol (case-insensitive), previous/alias symbol or HGNC:nnnn. */
export async function resolveGene(db: Database, ref: string): Promise<{ id: string; symbol: string }> {
  const kind = classifyRef(ref, 'GENE');
  if (kind === 'id') {
    const row = await one<{ id: string; symbol: string }>(db, sql`SELECT id, symbol FROM genes WHERE id = ${ref}`);
    if (row) return row;
    throw new NotFound('gene', ref);
  }
  const sym = ref.trim();
  const row = await one<{ id: string; symbol: string }>(
    db,
    sql`SELECT g.id, g.symbol FROM genes g WHERE upper(g.symbol) = upper(${sym}) OR g.hgnc_id = ${sym}
        UNION ALL
        SELECT g.id, g.symbol FROM genes g JOIN gene_aliases a ON a.gene_id = g.id WHERE upper(a.alias) = upper(${sym})
        LIMIT 1`,
  );
  if (!row) throw new NotFound('gene', ref);
  return row;
}

export async function resolveVariant(db: Database, ref: string): Promise<{ id: string; slug: string }> {
  const kind = classifyRef(ref, 'VAR');
  const row =
    kind === 'id'
      ? await one<{ id: string; slug: string }>(db, sql`SELECT id, slug FROM variants WHERE id = ${ref}`)
      : await one<{ id: string; slug: string }>(db, sql`SELECT id, slug FROM variants WHERE slug = ${ref.toLowerCase()}`);
  if (!row) throw new NotFound('variant', ref);
  return row;
}

export async function resolveDrug(db: Database, ref: string): Promise<{ id: string; slug: string }> {
  const kind = classifyRef(ref, 'DRUG');
  const row =
    kind === 'id'
      ? await one<{ id: string; slug: string }>(db, sql`SELECT id, slug FROM drugs WHERE id = ${ref}`)
      : await one<{ id: string; slug: string }>(db, sql`SELECT id, slug FROM drugs WHERE slug = ${ref.toLowerCase()}`);
  if (!row) throw new NotFound('drug', ref);
  return row;
}

export async function resolveTrial(db: Database, ref: string): Promise<{ id: string; nctId: string }> {
  const kind = classifyRef(ref, 'TRIAL');
  const row =
    kind === 'id'
      ? await one<{ id: string; nct_id: string }>(db, sql`SELECT id, nct_id FROM clinical_trials WHERE id = ${ref}`)
      : await one<{ id: string; nct_id: string }>(db, sql`SELECT id, nct_id FROM clinical_trials WHERE nct_id = ${ref.toUpperCase()}`);
  if (!row) throw new NotFound('trial', ref);
  return { id: row.id, nctId: row.nct_id };
}

export async function resolvePublication(db: Database, ref: string): Promise<{ id: string; pmid: string | null }> {
  const kind = classifyRef(ref, 'PUB');
  const row =
    kind === 'id'
      ? await one<{ id: string; pmid: string | null }>(db, sql`SELECT id, pmid FROM publications WHERE id = ${ref}`)
      : await one<{ id: string; pmid: string | null }>(db, sql`SELECT id, pmid FROM publications WHERE pmid = ${ref}`);
  if (!row) throw new NotFound('publication', ref);
  return row;
}