import { fileURLToPath } from 'node:url';
import path from 'node:path';
import { readFileSync, existsSync } from 'node:fs';
import { sql } from 'drizzle-orm';
import { CATEGORIES, GRADERS, INDICES, INDEX_BASE_DATE, INDEX_BASE_VALUE } from '@rareindex/taxonomy';
import { getDb, closeDb } from './client.js';
import { categories, graders, indices, sources, connectors } from './schema/index.js';

/** Upsert taxonomy nodes + graders from data/taxonomy (idempotent). */
export async function seedTaxonomy(): Promise<void> {
  const db = getDb();
  for (const c of CATEGORIES) {
    await db
      .insert(categories)
      .values({
        slug: c.slug,
        parentSlug: c.parent ?? null,
        familySlug: c.familySlug,
        name: c.name,
        shortName: c.short ?? null,
        description: c.description ?? null,
        level: c.level,
        phase: c.phase,
        conditionScale: c.conditionScale ?? 'general',
        graders: c.graders ?? [],
        indexTicker: c.index ?? null,
        sortOrder: c.sortOrder,
        complianceFlags: c.compliance ?? [],
        icon: c.icon ?? null,
      })
      .onConflictDoUpdate({
        target: categories.slug,
        set: {
          parentSlug: c.parent ?? null,
          familySlug: c.familySlug,
          name: c.name,
          shortName: c.short ?? null,
          description: c.description ?? null,
          level: c.level,
          phase: c.phase,
          conditionScale: c.conditionScale ?? 'general',
          graders: c.graders ?? [],
          indexTicker: c.index ?? null,
          sortOrder: c.sortOrder,
          complianceFlags: c.compliance ?? [],
          icon: c.icon ?? null,
          updatedAt: new Date(),
        },
      });
  }
  for (const g of GRADERS) {
    await db
      .insert(graders)
      .values({ slug: g.slug, name: g.name, categorySlugs: g.categories, scale: g.scale, populationUrl: g.populationUrl ?? null, verifyUrl: g.verifyUrl ?? null })
      .onConflictDoUpdate({ target: graders.slug, set: { name: g.name, categorySlugs: g.categories, scale: g.scale, populationUrl: g.populationUrl ?? null, verifyUrl: g.verifyUrl ?? null } });
  }
}

/** Upsert index definitions (§3). */
export async function seedIndices(): Promise<void> {
  const db = getDb();
  for (const i of INDICES) {
    const familySlugs = i.families;
    const categorySlugs = CATEGORIES.filter((c) => familySlugs.includes(c.familySlug)).map((c) => c.slug);
    await db
      .insert(indices)
      .values({
        id: `idx_${i.ticker.toLowerCase().replace(/[^a-z0-9]+/g, '_')}`,
        ticker: i.ticker,
        name: i.name,
        description: i.description ?? null,
        categorySlugs,
        familySlugs,
        parentTicker: i.flagship ? null : 'RARE',
        baseDate: INDEX_BASE_DATE,
        baseValue: INDEX_BASE_VALUE,
        minConstituents: i.minConstituents,
        isFlagship: Boolean(i.flagship),
        color: i.color,
      })
      .onConflictDoUpdate({ target: indices.ticker, set: { name: i.name, description: i.description ?? null, categorySlugs, familySlugs, minConstituents: i.minConstituents, color: i.color } });
  }
}

/** Mirror connectors/registry.json into sources + connectors tables (state columns are preserved). */
export async function seedRegistry(): Promise<void> {
  const here = path.dirname(fileURLToPath(import.meta.url));
  const registryPath = path.resolve(here, '../../../connectors/registry.json');
  if (!existsSync(registryPath)) return;
  const reg = JSON.parse(readFileSync(registryPath, 'utf8')) as { connectors: Array<Record<string, any>> };
  const db = getDb();
  let i = 0;
  for (const c of reg.connectors) {
    i++;
    // Stagger first runs of newly registered connectors over 6 hours so a wave of new sources does not hit every
    // host at once (existing rows keep their own next_run_at — the conflict clause does not touch it).
    const firstRunAt = new Date(Date.now() + (i % 24) * 15 * 60_000);
    await db
      .insert(sources)
      .values({ id: c.sourceId, name: c.sourceName, sourceType: c.sourceType, url: c.sourceUrl, countries: c.regions ?? [], languages: c.languages ?? ['en'], currencies: c.currency ?? ['USD'], trustScore: c.trustScore ?? 0.6, attributionRequired: c.attributionRequired ?? true, termsUrl: c.termsUrl ?? null })
      .onConflictDoUpdate({ target: sources.id, set: { name: c.sourceName, sourceType: c.sourceType, url: c.sourceUrl, trustScore: c.trustScore ?? 0.6, termsUrl: c.termsUrl ?? null, updatedAt: new Date() } });
    await db
      .insert(connectors)
      .values({
        id: c.id,
        sourceId: c.sourceId,
        displayName: c.displayName,
        enginePriority: c.enginePriority,
        categories: c.categories,
        regions: c.regions ?? [],
        languages: c.languages ?? ['en'],
        currency: c.currency ?? ['USD'],
        supportsListings: c.supportsListings ?? false,
        supportsSold: c.supportsSold ?? false,
        supportsAuctions: c.supportsAuctions ?? false,
        supportsImages: c.supportsImages ?? true,
        supportsCatalog: c.supportsCatalog ?? false,
        supportsPopulation: c.supportsPopulation ?? false,
        refreshFrequencyMinutes: c.refreshFrequencyMinutes ?? 1440,
        priority: c.priority ?? 'medium',
        status: c.enabled === false ? 'disabled' : 'active',
        schemaVersion: c.schemaVersion ?? '1.0',
        config: c.config ?? {},
        meta: c,
        nextRunAt: firstRunAt,
      })
      .onConflictDoUpdate({
        target: connectors.id,
        set: {
          sourceId: c.sourceId,
          displayName: c.displayName,
          enginePriority: c.enginePriority,
          categories: c.categories,
          regions: c.regions ?? [],
          languages: c.languages ?? ['en'],
          currency: c.currency ?? ['USD'],
          supportsListings: c.supportsListings ?? false,
          supportsSold: c.supportsSold ?? false,
          supportsAuctions: c.supportsAuctions ?? false,
          supportsImages: c.supportsImages ?? true,
          supportsCatalog: c.supportsCatalog ?? false,
          supportsPopulation: c.supportsPopulation ?? false,
          refreshFrequencyMinutes: c.refreshFrequencyMinutes ?? 1440,
          priority: c.priority ?? 'medium',
          schemaVersion: c.schemaVersion ?? '1.0',
          // registry config wins on shared keys, but runtime state stored in config (cursor) survives reseeds
          config: sql`coalesce(${connectors.config}, '{}'::jsonb) || ${JSON.stringify(c.config ?? {})}::jsonb`,
          meta: c,
          updatedAt: new Date(),
          // status is intentionally NOT overwritten (admin pause/maintenance survives reseeds) except when the registry
          // disables the connector, or re-enables one that was only disabled by the registry.
          status: c.enabled === false ? 'disabled' : sql`case when ${connectors.status} = 'disabled' then 'active' else ${connectors.status} end`,
        },
      });
  }
}

export async function seedAll(): Promise<void> {
  await seedTaxonomy();
  await seedIndices();
  await seedRegistry();
  const db = getDb();
  const [{ n }] = (await db.execute(sql`select count(*)::int as n from categories`)) as unknown as [{ n: number }];
  console.log(`[seed] categories=${n} indices=${INDICES.length}`);
}

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