// scripts/seed-keywords-bench.ts
// Synthetic bulk seed for perf QA of the réservoir (T6 — chantier réservoir).
// Generates realistic-shaped `keywords` rows (id `${siteId}-bench-<i>`, term
// `bench-<i>-<mots aléatoires>`) so EXPLAIN/timing can be measured against a
// large table without touching real customer data. Never wipes anything but
// its own rows (`term LIKE 'bench-%'`, scoped to one site).
//
//   bun --env-file=.env.local run scripts/seed-keywords-bench.ts --site <slug|id> --count 50000
//   bun --env-file=.env.local run scripts/seed-keywords-bench.ts --site <slug|id> --wipe
//
// `--site` matches sites.id first, then a case-insensitive sites.name (there
// is no separate slug column). Guarded by assertSeedableDb() — refuses to run
// outside seo_insight_dev/test*, same as src/lib/db/seed.ts.
//
// Intentionally bypasses the shared write channels (upsertCanonical /
// upsertLinkSources): this is synthetic, randomly-generated perf-QA volume
// with no real provider/import identity, not a keyword import — routing it
// through the canonical upsert would be semantically wrong (no source CSV,
// no slug, no quota to account for). Plain insert + onConflictDoNothing is
// deliberate and out of scope for the import-channel consolidation.
import { and, eq, sql } from "drizzle-orm";
import { assertSeedableDb } from "@/lib/db/assert-test-db";
import { chunk } from "@/lib/db/chunk";
import { client, db } from "@/lib/db/client";
import { keywords, sites } from "@/lib/db/domain-schema";
import {
  KEYWORD_STATUSES,
  type KeywordSource,
  type KeywordStage,
  type KeywordStatus,
  type KeywordTheme,
  type MonthlyVolume,
  type SearchIntent,
  type SourceMetrics,
} from "@/lib/keywords/types";

const PRIMARY_SOURCES: KeywordSource[] = [
  "semrush",
  "seranking",
  "mangools",
  "gsc",
  "manual",
];
const INTENTS: SearchIntent[] = [
  "informational",
  "commercial",
  "transactional",
  "navigational",
  "local",
];
const THEMES: KeywordTheme[] = [
  "lab-diamonds",
  "supplies",
  "metals",
  "manufacturing",
  "ethics-industry",
];

const FR_WORDS = [
  "comment",
  "meilleur",
  "prix",
  "avis",
  "comparatif",
  "guide",
  "top",
  "astuce",
  "pas-cher",
  "location",
  "achat",
  "fabricant",
  "fournisseur",
  "norme",
  "certification",
  "poids",
  "carat",
  "laboratoire",
  "artisanal",
  "occasion",
];
const EN_WORDS = [
  "how-to",
  "best",
  "price",
  "review",
  "compare",
  "guide",
  "top",
  "tips",
  "cheap",
  "rental",
  "buy",
  "manufacturer",
  "supplier",
  "standard",
  "certification",
  "carat",
  "weight",
  "lab",
  "handmade",
  "wholesale",
];

function getArg(flag: string): string | undefined {
  const i = process.argv.indexOf(flag);
  return i >= 0 ? process.argv[i + 1] : undefined;
}
function hasFlag(flag: string): boolean {
  return process.argv.includes(flag);
}

function randInt(min: number, max: number): number {
  return Math.floor(min + Math.random() * (max - min + 1));
}
function pick<T>(arr: readonly T[]): T {
  const item = arr[randInt(0, arr.length - 1)];
  if (item === undefined) throw new Error("pick: empty array");
  return item;
}
function randomWords(lang: "fr" | "en"): string {
  const pool = lang === "fr" ? FR_WORDS : EN_WORDS;
  const n = randInt(2, 4);
  const words = new Set<string>();
  while (words.size < n) words.add(pick(pool));
  return [...words].join("-");
}

/** Approximate standard normal via Box-Muller. */
function randNormal(): number {
  const u1 = Math.random() || Number.EPSILON;
  const u2 = Math.random();
  return Math.sqrt(-2 * Math.log(u1)) * Math.cos(2 * Math.PI * u2);
}

/** Log-normal volume, clamped to [0, 90000] — most keywords low-volume, a
 *  long tail of high-volume ones, matching real SEO data shape. */
function randVolume(): number {
  const mu = Math.log(300);
  const sigma = 1.8;
  const v = Math.exp(mu + sigma * randNormal());
  return Math.min(90000, Math.max(0, Math.round(v)));
}

function monthsBack(n: number): string[] {
  const out: string[] = [];
  const now = new Date();
  for (let i = n - 1; i >= 0; i--) {
    const d = new Date(now.getFullYear(), now.getMonth() - i, 1);
    out.push(`${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, "0")}`);
  }
  return out;
}

interface BenchRow {
  id: string;
  siteId: string;
  term: string;
  lang: string;
  country: string;
  theme: KeywordTheme | null;
  status: KeywordStatus;
  intent: SearchIntent[];
  stages: KeywordStage[];
  primarySource: KeywordSource;
  sources: KeywordSource[];
  metrics: Partial<Record<KeywordSource, SourceMetrics>>;
  trend: MonthlyVolume[];
  gsc: {
    clicks: number;
    impressions: number;
    ctr: number;
    position: number;
    updatedAt: string;
  } | null;
}

function buildRow(siteId: string, i: number, nowIso: string): BenchRow {
  const lang: "fr" | "en" = Math.random() < 0.8 ? "fr" : "en";
  const country = lang === "fr" ? "FR" : "US";
  const status = KEYWORD_STATUSES[i % KEYWORD_STATUSES.length] as KeywordStatus;
  const primarySource = PRIMARY_SOURCES[
    i % PRIMARY_SOURCES.length
  ] as KeywordSource;
  const theme = i % 13 === 0 ? null : (THEMES[i % THEMES.length] ?? null);
  const intent: SearchIntent[] = [INTENTS[i % INTENTS.length] as SearchIntent];
  if (i % 7 === 0)
    intent.push(INTENTS[(i + 2) % INTENTS.length] as SearchIntent);
  const stages: KeywordStage[] = i % 5 === 0 ? ["research"] : [];

  const hasMetrics = Math.random() >= 0.15; // 15% sans métriques
  const metrics: Partial<Record<KeywordSource, SourceMetrics>> = hasMetrics
    ? {
        [primarySource]: {
          volume: randVolume(),
          kd: randInt(1, 95),
          cpc: Math.round(Math.random() * 12 * 100) / 100,
          growthPct: randInt(-50, 120),
          updatedAt: nowIso,
        },
      }
    : {};

  const trend: MonthlyVolume[] =
    Math.random() < 0.3
      ? monthsBack(12).map((month) => ({ month, volume: randVolume() }))
      : [];

  const gsc =
    Math.random() < 0.25
      ? (() => {
          const impressions = randInt(20, 5000);
          const clicks = randInt(0, Math.floor(impressions * 0.3));
          return {
            clicks,
            impressions,
            ctr: impressions > 0 ? clicks / impressions : 0,
            position: randInt(1, 80),
            updatedAt: nowIso,
          };
        })()
      : null;

  return {
    id: `${siteId}-bench-${i}`,
    siteId,
    term: `bench-${i}-${randomWords(lang)}`,
    lang,
    country,
    theme,
    status,
    intent,
    stages,
    primarySource,
    sources: [primarySource],
    metrics,
    trend,
    gsc,
  };
}

async function resolveSite(arg: string): Promise<{ id: string; name: string }> {
  const byId = await db
    .select({ id: sites.id, name: sites.name })
    .from(sites)
    .where(eq(sites.id, arg));
  if (byId[0]) return byId[0];

  const byName = await db
    .select({ id: sites.id, name: sites.name })
    .from(sites)
    .where(sql`lower(${sites.name}) = lower(${arg})`);
  if (byName.length === 1 && byName[0]) return byName[0];
  if (byName.length > 1) {
    throw new Error(
      `resolveSite: "${arg}" matches ${byName.length} sites by name — pass the id instead.`,
    );
  }
  throw new Error(
    `resolveSite: no site found for "${arg}" (tried id, then name).`,
  );
}

async function countBench(siteId: string): Promise<number> {
  const [row] = await db
    .select({ n: sql<number>`count(*)::int` })
    .from(keywords)
    .where(
      and(eq(keywords.siteId, siteId), sql`${keywords.term} LIKE 'bench-%'`),
    );
  return row?.n ?? 0;
}

async function countAll(siteId: string): Promise<number> {
  const [row] = await db
    .select({ n: sql<number>`count(*)::int` })
    .from(keywords)
    .where(eq(keywords.siteId, siteId));
  return row?.n ?? 0;
}

async function wipe(siteId: string): Promise<void> {
  const before = await countAll(siteId);
  const benchBefore = await countBench(siteId);
  await db
    .delete(keywords)
    .where(
      and(eq(keywords.siteId, siteId), sql`${keywords.term} LIKE 'bench-%'`),
    );
  const after = await countAll(siteId);
  console.log(
    `wipe: site ${siteId} — ${benchBefore} lignes bench supprimées ` +
      `(total keywords du site: ${before} → ${after}).`,
  );
}

async function seedBench(siteId: string, count: number): Promise<void> {
  const nowIso = new Date().toISOString();
  const rows: BenchRow[] = [];
  for (let i = 0; i < count; i++) rows.push(buildRow(siteId, i, nowIso));

  const t0 = performance.now();
  await db.transaction(async (tx) => {
    for (const slice of chunk(rows, 1000)) {
      await tx.insert(keywords).values(slice).onConflictDoNothing();
    }
  });
  const elapsedMs = Math.round(performance.now() - t0);

  const total = await countAll(siteId);
  console.log(
    `seed: ${rows.length} keywords bench insérés sur le site ${siteId} en ${elapsedMs} ms ` +
      `(total keywords du site: ${total}).`,
  );
}

async function main() {
  // SEC-P4 — same guard as src/lib/db/seed.ts: refuses to run against
  // anything but seo_insight_dev/test* (never a leaked prod DATABASE_URL).
  await assertSeedableDb();

  const siteArg = getArg("--site");
  if (!siteArg) throw new Error("usage: --site <slug|id> [--count N] [--wipe]");
  const site = await resolveSite(siteArg);

  if (hasFlag("--wipe")) {
    await wipe(site.id);
    return;
  }

  const countArg = getArg("--count");
  const count = countArg ? Number.parseInt(countArg, 10) : Number.NaN;
  if (!Number.isFinite(count) || count <= 0) {
    throw new Error("usage: --site <slug|id> --count <n>  (positive integer)");
  }
  await seedBench(site.id, count);
}

await main();
await client.end();