import { fileURLToPath } from 'node:url';
import path from 'node:path';
import { eq, sql } from 'drizzle-orm';
import { getDb, closeDb } from './client.js';
import { 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';

/**
 * Seeds only system data (CLAUDE.md §358): metric definitions and canonical geographies.
 * 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 }];
  console.log(`[seed] metrics=${n} geographies=${GEOGRAPHY_SEED.length}`);
}

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);
    });
}