import { fileURLToPath } from 'node:url';
import path from 'node:path';
import { eq, sql } from 'drizzle-orm';
import { getDb, closeDb } from './client.js';
import { biomarkers, geographies, metricDefinitions } from './schema/index.js';
import { mintId } from './ids.js';
import { METRIC_CATALOG } from './seed-data/metrics.js';
import { GEOGRAPHY_SEED } from './seed-data/geographies.js';
import { BIOMARKER_SEED, buildMeasurement, seedGeneSymbols } from './seed-data/biomarkers.js';
import type { Database } from './client.js';

/**
 * Canonical biomarkers (SPEC §17, §52, §121): curated metadata only — identity, verified NCIt code,
 * anchor gene, aliases, assay conventions. Every scientific link is derived at query time.
 * Idempotent by slug; `CI-BIO` ids are minted once and never reassigned. Anchor genes are resolved
 * against `genes` by HGNC symbol; an unresolved symbol is reported and left null, never invented.
 */
export async function seedBiomarkers(db: Database): Promise<{ inserted: number; updated: number; total: number; unresolvedGenes: string[] }> {
  const symbols = seedGeneSymbols();
  const rows = symbols.length ? await db.execute<{ id: string; symbol: string }>(sql`SELECT id, symbol FROM genes WHERE symbol = ANY(${sql.param(symbols)}::text[])`) : [];
  const geneId = new Map<string, string>();
  for (const r of rows) geneId.set(r.symbol, r.id);
  const unresolvedGenes = symbols.filter((s) => !geneId.has(s));
  let inserted = 0;
  let updated = 0;
  for (const b of BIOMARKER_SEED) {
    const values = {
      name: b.name,
      kind: b.kind,
      geneId: b.geneSymbol ? geneId.get(b.geneSymbol) ?? null : null,
      ncitCode: b.ncitCode,
      description: b.description,
      measurement: buildMeasurement(b) as unknown as Record<string, unknown>,
    };
    const [existing] = await db.select({ id: biomarkers.id }).from(biomarkers).where(eq(biomarkers.slug, b.slug)).limit(1);
    if (existing) {
      await db.update(biomarkers).set({ ...values, updatedAt: new Date() }).where(eq(biomarkers.id, existing.id));
      updated++;
    } else {
      await db.insert(biomarkers).values({ id: await mintId(db, 'BIO'), slug: b.slug, ...values });
      inserted++;
    }
  }
  const [{ n }] = (await db.execute<{ n: string }>(sql`SELECT count(*)::text AS n FROM biomarkers`)) as unknown as [{ n: string }];
  return { inserted, updated, total: Number(n), unresolvedGenes };
}

/**
 * Seeds only system data (CLAUDE.md §358): metric definitions, canonical geographies and the
 * curated biomarker catalogue (metadata, not scientific values). Scientific data is never seeded —
 * it comes from connectors. Sources are synced from connector manifests by `pnpm cix sources:sync`
 * (lives in the root CLI to avoid a package cycle).
 */
export async function seed(): Promise<void> {
  const db = getDb({ max: 2 });
  for (const m of METRIC_CATALOG) {
    const [existing] = await db.select({ id: metricDefinitions.id }).from(metricDefinitions).where(eq(metricDefinitions.slug, m.slug)).limit(1);
    const values = { ...m, updatedAt: new Date() };
    if (existing) await db.update(metricDefinitions).set(values).where(eq(metricDefinitions.id, existing.id));
    else await db.insert(metricDefinitions).values({ id: await mintId(db, 'METRIC'), ...m });
  }
  const bySlug = new Map<string, string>();
  for (const g of GEOGRAPHY_SEED) {
    const [existing] = await db.select({ id: geographies.id }).from(geographies).where(eq(geographies.slug, g.slug)).limit(1);
    const parentId = g.parentSlug ? bySlug.get(g.parentSlug) ?? null : null;
    if (existing) {
      await db.update(geographies).set({ name: g.name, kind: g.kind, iso2: g.iso2 ?? null, iso3: g.iso3 ?? null, parentId, whoRegion: g.whoRegion ?? null }).where(eq(geographies.id, existing.id));
      bySlug.set(g.slug, existing.id);
    } else {
      const id = await mintId(db, 'GEO');
      await db.insert(geographies).values({ id, slug: g.slug, name: g.name, kind: g.kind, iso2: g.iso2 ?? null, iso3: g.iso3 ?? null, parentId, whoRegion: g.whoRegion ?? null });
      bySlug.set(g.slug, id);
    }
  }
  const [{ n }] = (await db.execute<{ n: string }>(sql`SELECT count(*)::text AS n FROM metric_definitions`)) as unknown as [{ n: string }];
  const bio = await seedBiomarkers(db);
  console.log(`[seed] metrics=${n} geographies=${GEOGRAPHY_SEED.length} biomarkers=${bio.total} (inserted=${bio.inserted} updated=${bio.updated})`);
  if (bio.unresolvedGenes.length) console.warn(`[seed] biomarkers: unresolved HGNC symbols (gene_id left null): ${bio.unresolvedGenes.join(', ')}`);
}

const isMain = process.argv[1] && path.resolve(process.argv[1]) === fileURLToPath(import.meta.url);
if (isMain) {
  seed()
    .then(() => closeDb())
    .catch(async (e) => {
      console.error('[seed] failed', e);
      await closeDb();
      process.exit(1);
    });
}