# Slug Consistency Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Make every slug path (Rust/wasm, app, analytics) transliterate accents identically (é→e), and migrate existing drop-accent keyword ids to the canonical form, merging collisions.

**Architecture:** The Rust `slugify` in `import-core` becomes the single canonical implementation for the app (GSC sync, discovery, import, manual add, themes, exclusions all use `wasm.slugify`); the standalone analytics Bun service keeps a TS mirror pinned to Rust by a shared golden-fixture test. A one-off, env-guarded migration re-slugs the 231 affected keyword ids and merges the 52 collision groups, repointing all FK children.

**Tech Stack:** Rust (`import-core`, `unicode-normalization`, `wasm-pack`), TypeScript (Next app, `@seo-insight/analytics` Bun service), Drizzle + Postgres, `bun:test`, `cargo test`.

**Context the implementer must know:**
- Rebuilding the wasm requires `bun run build:wasm` then `bun install` then a dev-server restart — the wasm is external, not HMR'd. See memory `import-core-wasm-rebuild`.
- DB tests: never `db:push`. Run keyword tests via `bun run test src/lib/keywords/...` (loads `.env.local` + test DB). The combined `bun run test` has ~46 pre-existing failures (DB isolation + bun mock leak) — gate per-directory.
- Install new deps prefixed with `sfw` (e.g. `sfw cargo add ...`).
- The `actions.ts` local slugify ALREADY transliterates (NFD), so replacing it with `wasm.slugify` is a behavioral no-op once Rust transliterates — it's pure de-duplication. The real behavioral change is `wasm.slugify` itself (drop-accent → transliterate) and analytics.

**Shared golden fixture (term → expected slug)** — used verbatim in BOTH the Rust test (Task 1) and the analytics test (Task 3):

| term | expected slug |
|---|---|
| `""` | `""` |
| `"Foo Bar"` | `"foo-bar"` |
| `"123 ABC"` | `"123-abc"` |
| `"éclairage"` | `"eclairage"` |
| `"électricité entreprise"` | `"electricite-entreprise"` |
| `"café crème"` | `"cafe-creme"` |
| `"déjà-vu"` | `"deja-vu"` |
| `"façade"` | `"facade"` |
| `"naïve"` | `"naive"` |
| `"groupé"` | `"groupe"` |
| `"L'énergie"` | `"l-energie"` |
| `"Çà et là"` | `"ca-et-la"` |
| `"Süßwaren"` | `"su-waren"` |
| `"œuvre d'art"` | `"uvre-d-art"` |
| `"cœur"` | `"c-ur"` |
| `"  spaced  out  "` | `"spaced-out"` |
| `"---trim---"` | `"trim"` |

---

### Task 1: Rust `slugify` transliterates accents

**Files:**
- Modify: `crates/import-core/Cargo.toml` (add dependency)
- Modify: `crates/import-core/src/normalize.rs:62-75` (the private `fn slugify`) + add a test module case
- Regenerate: `src/lib/backlink/import-core/import_core_bg.wasm` (+ `.js`, `.d.ts`) via build

- [ ] **Step 1: Add the dependency**

Run: `cd crates/import-core && sfw cargo add unicode-normalization`
Expected: `Cargo.toml` gains `unicode-normalization = "0.1"` under `[dependencies]`.

- [ ] **Step 2: Write the failing Rust test (golden fixture)**

Add to the `#[cfg(test)] mod tests` block in `crates/import-core/src/normalize.rs` (use `super::slugify_public`):

```rust
#[test]
fn slugify_transliterates_accents() {
    let cases = [
        ("", ""),
        ("Foo Bar", "foo-bar"),
        ("123 ABC", "123-abc"),
        ("éclairage", "eclairage"),
        ("électricité entreprise", "electricite-entreprise"),
        ("café crème", "cafe-creme"),
        ("déjà-vu", "deja-vu"),
        ("façade", "facade"),
        ("naïve", "naive"),
        ("groupé", "groupe"),
        ("L'énergie", "l-energie"),
        ("Çà et là", "ca-et-la"),
        ("Süßwaren", "su-waren"),
        ("œuvre d'art", "uvre-d-art"),
        ("cœur", "c-ur"),
        ("  spaced  out  ", "spaced-out"),
        ("---trim---", "trim"),
    ];
    for (input, expected) in cases {
        assert_eq!(slugify_public(input), expected, "input={input:?}");
    }
}
```

- [ ] **Step 3: Run the test to verify it fails**

Run: `cd crates/import-core && cargo test slugify_transliterates_accents`
Expected: FAIL — e.g. `éclairage` yields `clairage`, not `eclairage`.

- [ ] **Step 4: Implement transliteration in `fn slugify`**

Replace `fn slugify` (lines ~62-75 of `crates/import-core/src/normalize.rs`) with:

```rust
fn slugify(s: &str) -> String {
    use unicode_normalization::char::is_combining_mark;
    use unicode_normalization::UnicodeNormalization;
    let mut out = String::new();
    let mut prev_dash = false;
    for c in s.nfd() {
        if is_combining_mark(c) {
            continue; // drop NFD-decomposed accents (é → e + ◌́ → e)
        }
        if c.is_ascii_alphanumeric() {
            out.push(c.to_ascii_lowercase());
            prev_dash = false;
        } else if !out.is_empty() && !prev_dash {
            out.push('-');
            prev_dash = true;
        }
    }
    out.trim_matches('-').to_string()
}
```

(If `is_combining_mark` is not exported by the installed version, substitute `unicode_normalization::char::canonical_combining_class(c) != 0` — the golden test is the gate.)

- [ ] **Step 5: Run the Rust tests to verify they pass**

Run: `cd crates/import-core && cargo test`
Expected: PASS — `slugify_transliterates_accents` green, and the existing `keyword.rs` test (`slug == "personal-jeweler"`) still green.

- [ ] **Step 6: Rebuild the wasm**

Run: `bun run build:wasm && bun install`
Expected: `src/lib/backlink/import-core/import_core_bg.wasm` (+ `.js`/`.d.ts`) regenerated.

- [ ] **Step 7: Verify the rebuilt wasm transliterates from JS**

Run:
```bash
bun -e 'import("./src/lib/backlink/import-core/import_core.js").then(w=>{console.log(w.slugify("électricité"), w.slugify("œuvre d'\''art"))})'
```
Expected: `electricite uvre-d-art`

- [ ] **Step 8: Commit**

```bash
git add crates/import-core/Cargo.toml crates/import-core/Cargo.lock crates/import-core/src/normalize.rs src/lib/backlink/import-core
git commit -m "feat(import-core): slugify transliterates accents (é→e), rebuild wasm"
```

---

### Task 2: App converges on `wasm.slugify`

**Files:**
- Modify: `src/lib/keywords/actions.ts` (delete local `slugify` ≈ lines 297-305; update its two callers `createThemeAction` and `addKeywordAction`)

- [ ] **Step 1: Confirm the call sites**

Run: `grep -n "slugify(" src/lib/keywords/actions.ts`
Expected: the local `function slugify` definition plus two calls (`createThemeAction`, `addKeywordAction`). Note: `wasm.slugify` is already imported (used by `excludeKeywordsAction`).

- [ ] **Step 2: Delete the local `slugify` function**

Remove this block from `src/lib/keywords/actions.ts`:

```ts
function slugify(term: string): string {
  return term
    .toLowerCase()
    .normalize("NFD")
    .replace(/\p{Diacritic}/gu, "")
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/(^-|-$)/g, "");
}
```

- [ ] **Step 3: Replace the two call sites**

In `createThemeAction`: change `const slug = slugify(trimmed);` → `const slug = wasm.slugify(trimmed);`
In `addKeywordAction`: change `const slug = slugify(term);` → `const slug = wasm.slugify(term);`

- [ ] **Step 4: Verify types + existing keyword tests still pass**

Run: `npx tsc --noEmit && bun run test src/lib/keywords/actions.test.ts`
Expected: tsc clean; tests pass (the existing tests use `wasm.slugify("Foo Bar")` = `foo-bar`, unaffected — behavior is identical because the deleted local slugify also transliterated).

- [ ] **Step 5: Commit**

```bash
git add src/lib/keywords/actions.ts
git commit -m "refactor(keywords): drop duplicate local slugify, use wasm.slugify everywhere"
```

---

### Task 3: Analytics TS mirror + parity test

**Files:**
- Modify: `services/analytics/src/processor.ts:6-12` (the `slugify` function)
- Create: `services/analytics/test/slugify.test.ts`
- Modify: `services/analytics/test/processor.test.ts` (the ahrefs assertion that hard-codes the old drop-accent slug)

- [ ] **Step 1: Write the failing parity test**

Create `services/analytics/test/slugify.test.ts`:

```ts
import { expect, test } from "bun:test";
import { slugifyTerm } from "../src/processor";

test("slugify transliterates accents (parity with Rust golden fixture)", () => {
  const cases: [string, string][] = [
    ["", ""],
    ["Foo Bar", "foo-bar"],
    ["123 ABC", "123-abc"],
    ["éclairage", "eclairage"],
    ["électricité entreprise", "electricite-entreprise"],
    ["café crème", "cafe-creme"],
    ["déjà-vu", "deja-vu"],
    ["façade", "facade"],
    ["naïve", "naive"],
    ["groupé", "groupe"],
    ["L'énergie", "l-energie"],
    ["Çà et là", "ca-et-la"],
    ["Süßwaren", "su-waren"],
    ["œuvre d'art", "uvre-d-art"],
    ["cœur", "c-ur"],
    ["  spaced  out  ", "spaced-out"],
    ["---trim---", "trim"],
  ];
  for (const [input, expected] of cases) {
    expect(slugifyTerm(input)).toBe(expected);
  }
});
```

- [ ] **Step 2: Run it to verify it fails**

Run: `cd services/analytics && bun test test/slugify.test.ts`
Expected: FAIL — `slugifyTerm` is not exported yet (and the current impl drops accents).

- [ ] **Step 3: Update the analytics slugify + export it**

In `services/analytics/src/processor.ts`, replace the existing `function slugify(s: string)` with a transliterating version, exported under a test-visible name and used internally:

```ts
export function slugifyTerm(s: string): string {
  return s
    .toLowerCase()
    .normalize("NFD")
    .replace(/\p{Diacritic}/gu, "")
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/(^-|-$)/g, "");
}
```

Then update the internal call site `const slug = slugify(term);` (≈ line 130) to `const slug = slugifyTerm(term);` and remove the old `function slugify`.

- [ ] **Step 4: Run the parity test to verify it passes**

Run: `cd services/analytics && bun test test/slugify.test.ts`
Expected: PASS (17 cases).

- [ ] **Step 5: Fix the ahrefs processor test expectation**

In `services/analytics/test/processor.test.ts`, the ahrefs test asserts the OLD drop-accent slug. Update it:
- Remove the `// NB: this processor's slugify strips accents'...` comment block.
- Change `r.slug === "subvention-clairage-led-entreprise"` → `r.slug === "subvention-eclairage-led-entreprise"`.

- [ ] **Step 6: Run the full analytics test suite**

Run: `cd services/analytics && bun test`
Expected: PASS (all files, incl. processor + slugify).

- [ ] **Step 7: Commit**

```bash
git add services/analytics/src/processor.ts services/analytics/test/slugify.test.ts services/analytics/test/processor.test.ts
git commit -m "feat(analytics): slugify transliterates accents (parity-tested with Rust)"
```

---

### Task 4: Pure `mergeKeywords` function

**Files:**
- Create: `src/lib/keywords/merge-keywords.ts`
- Test: `src/lib/keywords/merge-keywords.test.ts`

Merges N keyword DB rows (same canonical id) into one survivor. Operates on the Drizzle row shape.

- [ ] **Step 1: Write the failing test**

Create `src/lib/keywords/merge-keywords.test.ts`:

```ts
import { describe, expect, test } from "bun:test";
import { mergeKeywords, type MergeableKeyword } from "./merge-keywords";

const base: MergeableKeyword = {
  id: "s-electricite",
  siteId: "s",
  term: "electricite",
  lang: "fr",
  country: "FR",
  theme: null,
  status: "idea",
  intent: [],
  stages: [],
  primarySource: "gsc",
  sources: ["gsc"],
  metrics: {},
  trend: [],
  serpFeatures: null,
  briefs: {},
  gsc: null,
  serp: {},
};

test("unions sources/stages/intent and merges metrics by source", () => {
  const a: MergeableKeyword = {
    ...base,
    term: "électricité",
    status: "watch",
    stages: ["research"],
    intent: ["informational"],
    sources: ["gsc"],
    metrics: { gsc: { volume: 10 } as never },
    trend: [{ month: "2025-01", volume: 10 }],
  };
  const b: MergeableKeyword = {
    ...base,
    id: "s-electricite",
    term: "electricite",
    status: "active",
    stages: ["planning"],
    intent: ["commercial"],
    sources: ["ahrefs"],
    metrics: { ahrefs: { volume: 20, kd: 5 } as never },
    trend: [
      { month: "2025-01", volume: 10 },
      { month: "2025-02", volume: 12 },
    ],
  };
  const m = mergeKeywords("s-electricite", [a, b]);
  expect(m.id).toBe("s-electricite");
  expect(m.term).toBe("électricité"); // accented term preferred
  expect(m.status).toBe("active"); // most-advanced
  expect(new Set(m.stages)).toEqual(new Set(["research", "planning"]));
  expect(new Set(m.intent)).toEqual(new Set(["informational", "commercial"]));
  expect(new Set(m.sources)).toEqual(new Set(["gsc", "ahrefs"]));
  expect(m.metrics).toEqual({ gsc: { volume: 10 }, ahrefs: { volume: 20, kd: 5 } } as never);
  expect(m.trend.length).toBe(2); // longest trend wins
});

test("coalesces gsc preferring the better (lower) position", () => {
  const a = { ...base, gsc: { position: 8 } as never };
  const b = { ...base, gsc: { position: 3 } as never };
  expect(mergeKeywords(base.id, [a, b]).gsc).toEqual({ position: 3 } as never);
  expect(mergeKeywords(base.id, [{ ...base, gsc: null }, a]).gsc).toEqual({ position: 8 } as never);
});

test("merges briefs by country key", () => {
  const a = { ...base, briefs: { FR: { country: "FR" } as never } };
  const b = { ...base, briefs: { US: { country: "US" } as never } };
  expect(Object.keys(mergeKeywords(base.id, [a, b]).briefs).sort()).toEqual(["FR", "US"]);
});
```

- [ ] **Step 2: Run it to verify it fails**

Run: `bun run test src/lib/keywords/merge-keywords.test.ts`
Expected: FAIL — module not found.

- [ ] **Step 3: Implement `mergeKeywords`**

Create `src/lib/keywords/merge-keywords.ts`:

```ts
import type { KeywordSource, MonthlyVolume, SourceMetrics } from "./types";

// The subset of the keywords row the merge cares about (Drizzle select shape).
export type MergeableKeyword = {
  id: string;
  siteId: string;
  term: string;
  lang: string;
  country: string;
  theme: string | null;
  status: string;
  intent: string[];
  stages: string[];
  primarySource: string;
  sources: string[];
  metrics: Partial<Record<KeywordSource, SourceMetrics>>;
  trend: MonthlyVolume[];
  serpFeatures: string[] | null;
  briefs: Record<string, unknown>;
  gsc: { position?: number } | null;
  serp: Record<string, unknown>;
};

const STATUS_RANK: Record<string, number> = {
  active: 3,
  watch: 2,
  idea: 1,
  archived: 0,
};

const uniq = <T>(xs: T[]): T[] => [...new Set(xs)];

/** Merge keyword rows that collapse to `canonicalId` into one survivor row. */
export function mergeKeywords(
  canonicalId: string,
  rows: MergeableKeyword[],
): MergeableKeyword {
  // Survivor: the row already at canonicalId, else the first.
  const survivor =
    rows.find((r) => r.id === canonicalId) ?? rows[0];
  // Prefer an accented term (richer) for display.
  const term =
    rows.find((r) => /[^-]/.test(r.term))?.term ?? survivor.term;

  let status = survivor.status;
  let gsc = survivor.gsc;
  let trend = survivor.trend;
  const metrics: Partial<Record<KeywordSource, SourceMetrics>> = {};
  const briefs: Record<string, unknown> = {};
  let intent: string[] = [];
  let stages: string[] = [];
  let sources: string[] = [];

  for (const r of rows) {
    if ((STATUS_RANK[r.status] ?? 0) > (STATUS_RANK[status] ?? 0)) status = r.status;
    if (r.gsc && (!gsc || (r.gsc.position ?? Infinity) < (gsc.position ?? Infinity))) gsc = r.gsc;
    if (r.trend.length > trend.length) trend = r.trend;
    Object.assign(metrics, r.metrics);
    Object.assign(briefs, r.briefs);
    intent = intent.concat(r.intent);
    stages = stages.concat(r.stages);
    sources = sources.concat(r.sources);
  }

  return {
    ...survivor,
    id: canonicalId,
    term,
    status,
    gsc,
    trend,
    metrics,
    briefs,
    intent: uniq(intent),
    stages: uniq(stages),
    sources: uniq(sources),
  };
}
```

- [ ] **Step 4: Run the test to verify it passes**

Run: `bun run test src/lib/keywords/merge-keywords.test.ts`
Expected: PASS (3 tests).

- [ ] **Step 5: Commit**

```bash
git add src/lib/keywords/merge-keywords.ts src/lib/keywords/merge-keywords.test.ts
git commit -m "feat(keywords): pure mergeKeywords for slug-collision consolidation"
```

---

### Task 5: Migration script

**Files:**
- Create: `src/lib/keywords/migrate-slugs.ts`
- Modify: `package.json` (add `migrate:slugs` script)

Re-slugs every keyword to `${siteId}-${wasm.slugify(term)}`, merges collisions via `mergeKeywords`, repoints FK children, re-slugs exclusions. Env-guarded (`MIGRATE_SLUGS=1`).

- [ ] **Step 1: Write the migration script**

Create `src/lib/keywords/migrate-slugs.ts`:

```ts
import { and, eq, inArray } from "drizzle-orm";
import * as wasm from "@/lib/backlink/import-core/import_core.js";
import { db } from "@/lib/db/client";
import {
  contentItems,
  keywordExclusions,
  keywords,
  keywordShortlist,
  keywordTargets,
} from "@/lib/db/domain-schema";
import { mergeKeywords, type MergeableKeyword } 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)}`;
    (byNew.get(newId) ?? byNew.set(newId, []).get(newId)!).push(k);
  }

  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) => {
      // 1. Ensure the survivor row exists at newId with merged data.
      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 never) });
        renamed++;
      }

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

      // 2. Repoint keyword_targets (most-advanced status on conflict), then delete losers.
      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,
          })),
        );
      }

      // 3. Repoint keyword_shortlist (onConflictDoNothing), then delete losers.
      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));

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

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

  // 6. Re-slug exclusions 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);
    });
}
```

- [ ] **Step 2: Add the package script**

In `package.json` scripts, add:

```json
"migrate:slugs": "MIGRATE_SLUGS=1 bun --env-file=.env.local run src/lib/keywords/migrate-slugs.ts"
```

- [ ] **Step 3: Typecheck**

Run: `npx tsc --noEmit`
Expected: No errors.

- [ ] **Step 4: Commit**

```bash
git add src/lib/keywords/migrate-slugs.ts package.json
git commit -m "feat(keywords): one-off slug migration (re-slug + merge collisions)"
```

---

### Task 6: Migration DB integration test

**Files:**
- Test: `src/lib/keywords/migrate-slugs.test.ts`

Seeds a site with colliding keywords + FK children, runs `migrateSlugs`, asserts consolidation.

- [ ] **Step 1: Write the integration test**

Create `src/lib/keywords/migrate-slugs.test.ts`. Follow the existing DB-test setup in `src/lib/keywords/actions.test.ts` (same imports for `db`, schema tables, a `SITE_A` + site insert, and `beforeEach` cleanup). Then:

```ts
import { beforeEach, expect, test } from "bun:test";
import { eq } from "drizzle-orm";
import { db } from "@/lib/db/client";
import {
  keywords,
  keywordTargets,
  keywordShortlist,
} from "@/lib/db/domain-schema";
import { migrateSlugs } from "./migrate-slugs";
// reuse the site/cleanup helpers pattern from actions.test.ts (insert SITE_A, wipe tables)

const SITE = "mig-site";

async function seedKeyword(id: string, term: string, extra: Record<string, unknown> = {}) {
  await db.insert(keywords).values({
    id,
    siteId: SITE,
    term,
    lang: "fr",
    country: "FR",
    status: "idea",
    primarySource: "gsc",
    sources: ["gsc"],
    ...(extra as never),
  });
}

test("merges drop-accent + transliterated collisions into one canonical keyword", async () => {
  // Two rows for the same intent: GSC drop-accent + Ahrefs transliterated.
  await seedKeyword(`${SITE}-lectricit`, "électricité", {
    status: "watch",
    stages: ["research"],
    metrics: { gsc: { volume: 10 } },
  });
  await seedKeyword(`${SITE}-electricite`, "electricite", {
    status: "active",
    sources: ["ahrefs"],
    metrics: { ahrefs: { volume: 20 } },
  });
  // FK children on the loser (the drop-accent row).
  await db.insert(keywordTargets).values({ keywordId: `${SITE}-lectricit`, country: "FR", status: "candidate" });
  await db.insert(keywordShortlist).values({ siteId: SITE, keywordId: `${SITE}-lectricit` });

  const res = await migrateSlugs();
  expect(res.merged).toBeGreaterThanOrEqual(1);

  const rows = await db.select().from(keywords).where(eq(keywords.siteId, SITE));
  expect(rows.length).toBe(1);
  const k = rows[0];
  expect(k.id).toBe(`${SITE}-electricite`); // canonical
  expect(k.term).toBe("électricité"); // accented term preferred
  expect(k.status).toBe("active"); // most-advanced
  expect(new Set(k.stages)).toEqual(new Set(["research"]));
  expect(k.metrics).toEqual({ gsc: { volume: 10 }, ahrefs: { volume: 20 } } as never);

  const tgs = await db.select().from(keywordTargets).where(eq(keywordTargets.keywordId, `${SITE}-electricite`));
  expect(tgs.length).toBe(1); // repointed
  const sl = await db.select().from(keywordShortlist).where(eq(keywordShortlist.keywordId, `${SITE}-electricite`));
  expect(sl.length).toBe(1); // repointed
});

test("idempotent: a second run is a no-op", async () => {
  await seedKeyword(`${SITE}-electricite`, "électricité");
  await migrateSlugs();
  const res2 = await migrateSlugs();
  expect(res2.groups).toBe(0);
});
```

- [ ] **Step 2: Run it to verify it passes**

Run: `bun run test src/lib/keywords/migrate-slugs.test.ts`
Expected: PASS (2 tests). If the site-seed/cleanup helpers differ, mirror exactly what `actions.test.ts` does (read it first).

- [ ] **Step 3: Commit**

```bash
git add src/lib/keywords/migrate-slugs.test.ts
git commit -m "test(keywords): slug migration consolidates collisions + repoints FKs"
```

---

### Task 7: Run migration on dev + live verify + roadmap

**Files:**
- Modify: `docs/project/roadmap.md` (mark shipped + add prod runbook note)

- [ ] **Step 1: Re-run the migration test DB migrations (schema unchanged, sanity)**

Run: `npx tsc --noEmit && bun run test src/lib/keywords/`
Expected: tsc clean; all keyword tests pass.

- [ ] **Step 2: Snapshot dev counts before**

Run:
```bash
DBURL=$(grep -E '^DATABASE_URL' .env.local | cut -d= -f2- | tr -d '"'); psql "$DBURL" -c "SELECT count(*) FROM keywords; SELECT count(*) FROM keywords WHERE term ~ '[^[:ascii:]]';"
```
Expected: ~467 total, ~231 accented.

- [ ] **Step 3: Run the migration on dev**

Run: `bun run migrate:slugs`
Expected: prints e.g. `slug migration: {"groups":52,"merged":60,"renamed":...,"exclusionsUpdated":1}`.

- [ ] **Step 4: Verify dev data consolidated**

Run:
```bash
psql "$DBURL" -c "SELECT count(*) FROM keywords; SELECT id FROM keywords WHERE id LIKE '%-lectricit%' LIMIT 3;"
```
Expected: total dropped by ~60 (merged); no remaining `-lectricit` drop-accent ids (all transliterated).

- [ ] **Step 5: Live verify (d3k, static reads)**

Restart dev server (wasm changed). Open Acieb `/keywords/recherche`; confirm accented terms now show transliterated ids and that a previously-split keyword is single. Re-import the Ahrefs fixture and confirm it merges into existing GSC keywords (cross-source consensus columns populate).

- [ ] **Step 6: Update roadmap**

In `docs/project/roadmap.md`, change the "Cohérence des slugs" backlog item to ✅ shipped, noting: unified on transliteration (Rust `wasm.slugify`), analytics parity-tested, dev migrated (N merged), and a **prod runbook**: after deploy rebuild wasm + run `MIGRATE_SLUGS=1` once against prod.

- [ ] **Step 7: Commit**

```bash
git add docs/project/roadmap.md
git commit -m "docs(roadmap): slug consistency shipped (transliteration unified) + prod runbook"
```

---

## Notes for the executor
- Tasks 1→7 are sequential: Task 1 rebuilds the wasm that every later task depends on. Do not parallelize.
- After Task 1, restart any running dev server / analytics service so they pick up the new wasm.
- The migration (Task 7 Step 3) mutates the dev DB. It is idempotent, but snapshot counts first (Step 2).