// src/lib/keywords/migrate-slugs.ts
// One-off, env-guarded migration: re-slug every keyword to `${siteId}-${wasm.slugify(term)}`
// (wasm.slugify now transliterates accents), MERGE collision groups via the pure
// mergeKeywords helper, repoint all FK children, then re-slug keyword_exclusions.
// Run via `bun run migrate:slugs` (sets MIGRATE_SLUGS=1). Importing the module is a no-op.
import { and, eq, inArray } from "drizzle-orm";
import * as wasm from "import-core";
import { db } from "@/lib/db/client";
import {
  contentItems,
  keywordExclusions,
  keywordShortlist,
  keywords,
  keywordTargets,
} from "@/lib/db/domain-schema";
import { type MergeableKeyword, mergeKeywords } from "./merge-keywords";

const TARGET_RANK: Record<string, number> = {
  live: 3,
  planned: 2,
  candidate: 1,
};
// The pre-migration drop-accent slugify (for mapping old exclusion slugs).
const oldSlugify = (s: string) =>
  s
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/^-+|-+$/g, "");

export async function migrateSlugs(): Promise<{
  groups: number;
  merged: number;
  renamed: number;
  exclusionsUpdated: number;
}> {
  const all = (await db
    .select()
    .from(keywords)) as unknown as MergeableKeyword[];
  const byNew = new Map<string, MergeableKeyword[]>();
  for (const k of all) {
    const newId = `${k.siteId}-${wasm.slugify(k.term)}`;
    const arr = byNew.get(newId) ?? [];
    arr.push(k);
    byNew.set(newId, arr);
  }

  let merged = 0;
  let renamed = 0;
  let groups = 0;

  for (const [newId, rows] of byNew) {
    const changed = rows.length > 1 || rows[0].id !== newId;
    if (!changed) continue;
    groups++;
    const survivor = mergeKeywords(newId, rows);
    const losers = rows.filter((r) => r.id !== newId);

    await db.transaction(async (tx) => {
      const existsAtNew = rows.some((r) => r.id === newId);
      if (existsAtNew) {
        await tx
          .update(keywords)
          .set({
            term: survivor.term,
            status: survivor.status as never,
            intent: survivor.intent as never,
            stages: survivor.stages as never,
            sources: survivor.sources as never,
            metrics: survivor.metrics,
            trend: survivor.trend,
            briefs: survivor.briefs as never,
            gsc: survivor.gsc as never,
          })
          .where(eq(keywords.id, newId));
      } else {
        await tx
          .insert(keywords)
          .values({ ...survivor } as typeof keywords.$inferInsert);
        renamed++;
      }

      const loserIds = losers.map((l) => l.id).filter((id) => id !== newId);
      if (loserIds.length === 0) return;
      merged += loserIds.length;

      // keyword_targets: most-advanced status on conflict
      const loserTargets = await tx
        .select()
        .from(keywordTargets)
        .where(inArray(keywordTargets.keywordId, loserIds));
      const survTargets = await tx
        .select()
        .from(keywordTargets)
        .where(eq(keywordTargets.keywordId, newId));
      const bestByCountry = new Map<string, string>();
      for (const t of survTargets)
        bestByCountry.set(t.country, t.status as string);
      for (const t of loserTargets) {
        const cur = bestByCountry.get(t.country);
        if (
          !cur ||
          (TARGET_RANK[t.status as string] ?? 0) > (TARGET_RANK[cur] ?? 0)
        ) {
          bestByCountry.set(t.country, t.status as string);
        }
      }
      await tx
        .delete(keywordTargets)
        .where(inArray(keywordTargets.keywordId, [...loserIds, newId]));
      if (bestByCountry.size > 0) {
        await tx.insert(keywordTargets).values(
          [...bestByCountry].map(([country, status]) => ({
            keywordId: newId,
            country,
            status: status as never,
            siteId: survivor.siteId,
          })),
        );
      }

      // keyword_shortlist: onConflictDoNothing
      const loserShort = await tx
        .select()
        .from(keywordShortlist)
        .where(inArray(keywordShortlist.keywordId, loserIds));
      if (loserShort.length > 0) {
        await tx
          .insert(keywordShortlist)
          .values(
            loserShort.map((s) => ({ siteId: s.siteId, keywordId: newId })),
          )
          .onConflictDoNothing();
      }
      await tx
        .delete(keywordShortlist)
        .where(inArray(keywordShortlist.keywordId, loserIds));

      // content_items.primary_keyword_id
      await tx
        .update(contentItems)
        .set({ primaryKeywordId: newId })
        .where(inArray(contentItems.primaryKeywordId, loserIds));

      // delete loser keyword rows
      await tx.delete(keywords).where(inArray(keywords.id, loserIds));
    });
  }

  // exclusions: re-slug via old→new map built from all keywords
  const oldToNew = new Map<string, string>();
  for (const k of all) oldToNew.set(oldSlugify(k.term), wasm.slugify(k.term));
  const exclusions = await db.select().from(keywordExclusions);
  let exclusionsUpdated = 0;
  for (const ex of exclusions) {
    const next = oldToNew.get(ex.slug);
    if (!next || next === ex.slug) continue;
    await db.transaction(async (tx) => {
      await tx
        .insert(keywordExclusions)
        .values({ siteId: ex.siteId, slug: next })
        .onConflictDoNothing();
      await tx
        .delete(keywordExclusions)
        .where(
          and(
            eq(keywordExclusions.siteId, ex.siteId),
            eq(keywordExclusions.slug, ex.slug),
          ),
        );
    });
    exclusionsUpdated++;
  }

  return { groups, merged, renamed, exclusionsUpdated };
}

if (process.env.MIGRATE_SLUGS === "1") {
  migrateSlugs()
    .then((r) => {
      console.log("slug migration:", JSON.stringify(r));
      process.exit(0);
    })
    .catch((e) => {
      console.error(e);
      process.exit(1);
    });
}