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

/**
 * Lineage trace (CLAUDE.md §252-253): ranked value → inputs → observation → provenance → raw record.
 * Supported: rankings/<id>, epidemiology_observations/<id>, cancer_gene_frequencies/<id>, literature_counts/<id>.
 */
export async function traceValue(db: Database, table: string, id: string): Promise<Record<string, unknown>> {
  const out: Record<string, unknown> = { table, id };
  const prov = async (pid: number | null | undefined) => {
    if (!pid) return null;
    const [p] = await db.execute<Record<string, unknown>>(sql`SELECT p.*, s.name AS source_name, s.slug AS source_slug, s.license FROM provenance p JOIN sources s ON s.id = p.source_id WHERE p.id = ${pid}`);
    if (!p) return null;
    const [rec] = await db.execute<Record<string, unknown>>(sql`SELECT id, entity_kind, source_record_id, raw_path, payload_hash, retrieved_at, status FROM source_records WHERE source_id = ${p.source_id as string} AND source_record_id = ${(p.source_record_id as string) ?? ''} LIMIT 1`);
    return { provenance: p, sourceRecord: rec ?? null };
  };
  switch (table) {
    case 'rankings': {
      const [r] = await db.execute<Record<string, unknown>>(sql`SELECT r.*, s.formula_version, s.inputs_hash, s.generated_at AS snapshot_generated_at, m.formula, m.name AS metric_name FROM rankings r JOIN ranking_snapshots s ON s.id = r.snapshot_id JOIN metric_definitions m ON m.slug = r.metric_slug WHERE r.id = ${Number(id)}`);
      if (!r) return { ...out, error: 'not found' };
      out.ranking = r;
      const inputs = (r.inputs ?? {}) as Record<string, unknown>;
      for (const k of ['observationId', 'incidenceObservationId', 'mortalityObservationId']) {
        if (inputs[k]) out[k] = await traceValue(db, 'epidemiology_observations', String(inputs[k]));
      }
      return out;
    }
    case 'epidemiology_observations': {
      const [o] = await db.execute<Record<string, unknown>>(sql`SELECT * FROM epidemiology_observations WHERE id = ${Number(id)}`);
      if (!o) return { ...out, error: 'not found' };
      return { ...out, observation: o, lineage: await prov(o.provenance_id as number) };
    }
    case 'cancer_gene_frequencies': {
      const [o] = await db.execute<Record<string, unknown>>(sql`SELECT * FROM cancer_gene_frequencies WHERE id = ${Number(id)}`);
      if (!o) return { ...out, error: 'not found' };
      return { ...out, frequency: o, lineage: await prov(o.provenance_id as number) };
    }
    case 'literature_counts': {
      const [o] = await db.execute<Record<string, unknown>>(sql`SELECT * FROM literature_counts WHERE id = ${Number(id)}`);
      if (!o) return { ...out, error: 'not found' };
      return { ...out, literatureCount: o, lineage: await prov(o.provenance_id as number) };
    }
    case 'survival_observations': {
      const [o] = await db.execute<Record<string, unknown>>(sql`SELECT * FROM survival_observations WHERE id = ${Number(id)}`);
      if (!o) return { ...out, error: 'not found' };
      return { ...out, observation: o, lineage: await prov(o.provenance_id as number) };
    }
    default:
      return { ...out, error: 'unsupported table' };
  }
}