# Recherche — site countries + Shortlist (F8) + Seasonal (F7) — Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development. Steps use checkbox (`- [ ]`).

**Goal:** (A) the Recherche targeting matrix/bar use the **site's** target countries (not the fixed proto set); (B) a server-persisted **Shortlist** (cart) of keywords; (C) a **seasonal** "peak approaching" badge column.

**Architecture:** New `keyword_shortlist` table + actions; a pure `seasonalSuggestion` util feeding a toggle-able column; `countries` threaded from the page → container → `TargetingBar`/`CoverageMatrix`; `research-columns` flags via `countryFlag`. Built on the realigned `/keywords/recherche` (shared `ResearchContainer`, `stageFilter` untouched).

**Tech Stack:** Next 16, Drizzle/Postgres, TanStack Table, shadcn Sheet, bun + biome, bun:test.

Spec: `docs/superpowers/specs/2026-06-09-recherche-site-countries-shortlist-seasonal-design.md`.

**Branch:** `git checkout dev && git checkout -b feature/recherche-countries-shortlist-seasonal`

Key facts: `MonthlyVolume = { month: string /* "YYYY-MM" */; volume: number }`; `Keyword.trend: MonthlyVolume[]`. `countryFlag(code)` from `@/lib/keywords/country-flag`. `site.targetCountries: string[]`. Keyword actions scope by `siteId` (repo pattern). The 3 country consumers: `targeting-bar.tsx`, `coverage-matrix.tsx` (both iterate `TARGET_COUNTRIES` → take a `countries` prop), `research-columns.tsx` (only needs `countryFlag` instead of its module-level `FLAG` map).

---

## Task 1: `keyword_shortlist` table + migration

**Files:** Modify `src/lib/db/domain-schema.ts`; generate `drizzle/*`.

- [ ] **Step 1: Add the table** (after `keywordExclusions`):
```ts
/** Per-site working cart of keywords (F8 shortlist) — build a selection across
 *  filters/pages before acting on it. */
export const keywordShortlist = pgTable(
  "keyword_shortlist",
  {
    siteId: text("site_id")
      .notNull()
      .references(() => sites.id, { onDelete: "cascade" }),
    keywordId: text("keyword_id")
      .notNull()
      .references(() => keywords.id, { onDelete: "cascade" }),
    createdAt: timestamp("created_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
  },
  (t) => [primaryKey({ columns: [t.siteId, t.keywordId] })],
);
```
- [ ] **Step 2:** `bun run db:generate` → read the generated `drizzle/NNNN_*.sql`, confirm it ONLY creates `keyword_shortlist` (+ FKs + composite PK). If drift, STOP/report.
- [ ] **Step 3:** `bun run db:migrate` (dev) then `bun run db:migrate:test` (test DB — now fixed to load `.env.local`).
- [ ] **Step 4:** `npx tsc --noEmit` clean; commit:
```bash
git add src/lib/db/domain-schema.ts drizzle/
git commit -m "feat(db): keyword_shortlist table (per-site triage cart)"
```

---

## Task 2: `seasonalSuggestion` util (pure, TDD)

**Files:** Create `src/lib/keywords/seasonal.ts`, Test `src/lib/keywords/seasonal.test.ts`.

- [ ] **Step 1: Failing test:**
```ts
import { expect, test } from "bun:test";
import type { MonthlyVolume } from "./types";
import { seasonalSuggestion } from "./seasonal";

const series = (vals: number[], startYM = "2025-01"): MonthlyVolume[] => {
  let [y, m] = startYM.split("-").map(Number);
  return vals.map((volume) => {
    const month = `${y}-${String(m).padStart(2, "0")}`;
    m++;
    if (m > 12) { m = 1; y++; }
    return { month, volume };
  });
};

test("null when fewer than 6 points", () => {
  expect(seasonalSuggestion(series([10, 20, 30]), new Date("2026-06-09"))).toBeNull();
});

test("null when the series is flat (no clear peak)", () => {
  expect(
    seasonalSuggestion(series([100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100]), new Date("2026-06-09")),
  ).toBeNull();
});

test("suggests when a clear peak is 1..4 months ahead", () => {
  // peak in September (month index 9), volume spikes there
  const vals = [10, 10, 10, 10, 10, 10, 10, 10, 200, 10, 10, 10]; // Jan..Dec, peak=Sep
  const r = seasonalSuggestion(series(vals), new Date("2026-06-09")); // June → Sep = 3 months
  expect(r).toEqual({ peakMonth: 9, monthsUntil: 3 });
});

test("null when the peak is too far (>4 months)", () => {
  const vals = [10, 200, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10]; // peak=Feb
  // June → next Feb = 8 months → out of window
  expect(seasonalSuggestion(series(vals), new Date("2026-06-09"))).toBeNull();
});
```
- [ ] **Step 2:** `bun test src/lib/keywords/seasonal.test.ts` → fails.
- [ ] **Step 3: Implement (`src/lib/keywords/seasonal.ts`):**
```ts
// src/lib/keywords/seasonal.ts — "peak approaching" detection from the monthly series.
import type { MonthlyVolume } from "./types";

export type SeasonalSuggestion = { peakMonth: number; monthsUntil: number };

const MIN_POINTS = 6;
const PEAK_RATIO = 1.5; // peak month avg must exceed 1.5× the overall mean
const WINDOW = 4; // suggest only when the peak is 1..4 months ahead

/** From a monthly volume series, if there's a clear seasonal peak whose next
 *  occurrence is within {WINDOW} months, return it; otherwise null. */
export function seasonalSuggestion(
  trend: MonthlyVolume[],
  now: Date,
): SeasonalSuggestion | null {
  if (!trend || trend.length < MIN_POINTS) return null;
  // Average per calendar month (1..12).
  const sum = new Array(13).fill(0);
  const cnt = new Array(13).fill(0);
  let total = 0;
  for (const p of trend) {
    const m = Number(p.month.slice(5, 7));
    if (m < 1 || m > 12) continue;
    sum[m] += p.volume;
    cnt[m] += 1;
    total += p.volume;
  }
  const mean = total / trend.length;
  if (mean <= 0) return null;
  let peakMonth = 0;
  let peakAvg = -1;
  for (let m = 1; m <= 12; m++) {
    if (cnt[m] === 0) continue;
    const avg = sum[m] / cnt[m];
    if (avg > peakAvg) {
      peakAvg = avg;
      peakMonth = m;
    }
  }
  if (peakMonth === 0 || peakAvg < mean * PEAK_RATIO) return null;
  const cur = now.getMonth() + 1; // 1..12
  let monthsUntil = peakMonth - cur;
  if (monthsUntil <= 0) monthsUntil += 12;
  if (monthsUntil < 1 || monthsUntil > WINDOW) return null;
  return { peakMonth, monthsUntil };
}
```
- [ ] **Step 4:** `bun test src/lib/keywords/seasonal.test.ts` → 4 pass. `npx tsc --noEmit` clean; `bunx biome check --write src/lib/keywords/seasonal.ts src/lib/keywords/seasonal.test.ts` clean.
- [ ] **Step 5: Commit** `git commit -m "feat(keywords): seasonalSuggestion util (peak-approaching detection)"`

---

## Task 3: shortlist query + actions

**Files:** Create `src/lib/keywords/shortlist.ts`; Modify `src/lib/keywords/actions.ts`, `src/lib/keywords/actions.test.ts`.

- [ ] **Step 1: `src/lib/keywords/shortlist.ts`** (server-only query, mirror `queries.ts` style — `import "server-only"; import { db }; import { eq }`):
```ts
import "server-only";
import { eq } from "drizzle-orm";
import { db } from "@/lib/db/client";
import { keywordShortlist } from "@/lib/db/domain-schema";

/** The keyword ids on a site's shortlist. */
export async function getShortlistForSite(siteId: string): Promise<string[]> {
  const rows = await db
    .select({ id: keywordShortlist.keywordId })
    .from(keywordShortlist)
    .where(eq(keywordShortlist.siteId, siteId));
  return rows.map((r) => r.id);
}
```
- [ ] **Step 2: actions in `actions.ts`** (`"use server"`; add `keywordShortlist` to the schema import; `getShortlistForSite` import from `./shortlist`; `and, eq, inArray` already imported):
```ts
/** Read a site's shortlist (callable from client components). */
export async function fetchShortlistAction(siteId: string): Promise<string[]> {
  return getShortlistForSite(siteId);
}

/** Add keywords to the site's shortlist (idempotent). */
export async function addToShortlistAction(
  siteId: string,
  ids: string[],
): Promise<{ count: number }> {
  if (ids.length === 0) return { count: 0 };
  await db
    .insert(keywordShortlist)
    .values(ids.map((keywordId) => ({ siteId, keywordId })))
    .onConflictDoNothing();
  return { count: ids.length };
}

/** Remove keywords from the site's shortlist. */
export async function removeFromShortlistAction(
  siteId: string,
  ids: string[],
): Promise<{ count: number }> {
  if (ids.length === 0) return { count: 0 };
  const removed = await db
    .delete(keywordShortlist)
    .where(
      and(
        eq(keywordShortlist.siteId, siteId),
        inArray(keywordShortlist.keywordId, ids),
      ),
    )
    .returning({ id: keywordShortlist.keywordId });
  return { count: removed.length };
}
```
- [ ] **Step 3: DB test** in `actions.test.ts` (match the file's lazy-import/seed pattern — `insertKeyword`): seed 2 keywords, `addToShortlistAction(SITE, ["k1","k2"])` → `{count:2}` + `getShortlistForSite` has both; `removeFromShortlistAction(SITE, ["k1"])` → `{count:1}` + only `k2` remains.
- [ ] **Step 4:** `bun run test src/lib/keywords/actions.test.ts` → green. tsc + biome clean.
- [ ] **Step 5: Commit** `git commit -m "feat(keywords): shortlist query + add/remove actions"`

---

## Task 4: site-countries in the targeting bar + matrix

**Files:** Modify `src/components/keywords/research/targeting-bar.tsx`, `coverage-matrix.tsx`, `research-columns.tsx`.

- [ ] **Step 1: `targeting-bar.tsx`** — add a `countries: string[]` prop (after `selectedCountries`). Replace the `TARGET_COUNTRIES.map((c) => …)` dropdown body with `countries.map((code) => …)` using `countryFlag(code)` (import from `@/lib/keywords/country-flag`) for the flag and `code` for the label. Drop the `TARGET_COUNTRIES` import here. When `countries.length === 0`, render the country dropdown disabled with label "Aucun pays cible".
- [ ] **Step 2: `coverage-matrix.tsx`** — add a `countries: string[]` prop. Replace both `TARGET_COUNTRIES.map((c) => …)` (header + body) with `countries.map((code) => …)`: use `countryFlag(code)` for the flag and `code` as `countryLabel`. Drop the `TARGET_COUNTRIES` import. If `countries.length === 0`, render a single message row "Configure les pays cibles de ce site (Settings)".
- [ ] **Step 3: `research-columns.tsx`** — remove the module-level `FLAG` map (built from `TARGET_COUNTRIES`) and its `TARGET_COUNTRIES` import; in the "targets" (Pays ciblés) cell, replace `FLAG.get(c.country)` with `countryFlag(c.country)` (import `countryFlag`).
- [ ] **Step 4:** `npx tsc --noEmit` → expect errors at the call sites (container passes no `countries` yet) — that's fine, Task 7 wires it. Just confirm these 3 files are internally consistent; `bunx biome check --write` the 3 files.
- [ ] **Step 5: Commit** (the call sites are wired in Task 7; this compiles after Task 7):
```bash
git add src/components/keywords/research/targeting-bar.tsx src/components/keywords/research/coverage-matrix.tsx src/components/keywords/research/research-columns.tsx
git commit -m "feat(keywords): targeting bar + matrix use site target countries"
```
> NOTE for the controller: Tasks 4 and 7 together must leave `tsc` clean. Run the Task-7 tsc gate as the real check; don't block Task 4 on a transient call-site error.

---

## Task 5: seasonal column

**Files:** Modify `src/components/keywords/columns.tsx`.

- [ ] **Step 1:** `import { seasonalSuggestion } from "@/lib/keywords/seasonal";`
- [ ] **Step 2:** After the consensus columns, add:
```tsx
  {
    id: "seasonal",
    accessorFn: (k) => seasonalSuggestion(k.trend, new Date())?.monthsUntil ?? 99,
    header: "Saisonnier",
    enableGlobalFilter: false,
    cell: ({ row }) => {
      const s = seasonalSuggestion(row.original.trend, new Date());
      return s ? (
        <span className="whitespace-nowrap">
          🌟 pic dans {s.monthsUntil} mois
        </span>
      ) : (
        <span className="text-muted-foreground/50">—</span>
      );
    },
  },
```
- [ ] **Step 3:** Register in `TOGGLEABLE_COLUMNS` (`{ id: "seasonal", label: "Saisonnier" }`) and hide by default in `DEFAULT_COLUMN_VISIBILITY` (`seasonal: false`).
- [ ] **Step 4:** tsc + biome clean. Commit `git commit -m "feat(keywords): seasonal column (peak-approaching, opt-in)"`

---

## Task 6: Shortlist Sheet

**Files:** Create `src/components/keywords/research/shortlist-sheet.tsx`.

A shadcn `Sheet` listing the shortlisted keywords (term + flag) with per-row remove + a footer "Vider". READ `src/components/ui/sheet.tsx` for the exact exports first.

- [ ] **Step 1: Component** — props `{ open, onOpenChange, keywords: Keyword[], onRemove: (id: string) => void, onClear: () => void }`. List each keyword (`countryFlag(k.locale.country)` + `k.term`) with a small "✕" remove button calling `onRemove(k.id)`. Empty state "Panier vide". Footer button "Vider" → `onClear()`. (`keywords` = the shortlisted subset, computed by the container from the shortlist ids.)
- [ ] **Step 2:** tsc + biome clean. Commit `git commit -m "feat(keywords): shortlist Sheet panel"`

---

## Task 7: Wire into Recherche (container + page)

**Files:** Modify `src/components/keywords/research/research-container.tsx`, `src/app/(app)/keywords/recherche/page.tsx`, `src/app/(app)/keywords/recherche-staged/page.tsx`.

- [ ] **Step 1: Container props + state**

Add props `countries: string[]` and `initialShortlist: string[]` (with types). Add state:
```tsx
  const [shortlist, setShortlist] = useState<string[]>(initialShortlist);
  const [shortlistOpen, setShortlistOpen] = useState(false);
```
Imports: `fetchShortlistAction, addToShortlistAction, removeFromShortlistAction` from `@/lib/keywords/actions`; `ShortlistSheet` from `./shortlist-sheet`.

- [ ] **Step 2: Handlers**
```tsx
  async function refreshShortlist() {
    setShortlist(await fetchShortlistAction(activeSiteId));
  }
  async function addSelectedToShortlist() {
    const ids = Object.entries(rowSelection).filter(([, v]) => v).map(([id]) => id);
    if (!ids.length) return;
    try {
      const { count } = await addToShortlistAction(activeSiteId, ids);
      toast.success(`${count} ajouté(s) à la shortlist`);
      setRowSelection({});
      await refreshShortlist();
    } catch { toast.error("Échec de l'ajout à la shortlist"); }
  }
  async function removeFromShortlist(id: string) {
    try { await removeFromShortlistAction(activeSiteId, [id]); await refreshShortlist(); }
    catch { toast.error("Échec du retrait"); }
  }
  async function clearShortlist() {
    if (!shortlist.length) return;
    try { await removeFromShortlistAction(activeSiteId, shortlist); await refreshShortlist(); }
    catch { toast.error("Échec"); }
  }
  const shortlistKeywords = useMemo(
    () => rows.filter((k) => shortlist.includes(k.id)),
    [rows, shortlist],
  );
```

- [ ] **Step 3: Pass `countries` to TargetingBar + CoverageMatrix**

On `<TargetingBar … />` add `countries={countries}` and `onAddToShortlist={addSelectedToShortlist}`. On `<CoverageMatrix … />` add `countries={countries}`. (TargetingBar gets a new `onAddToShortlist: () => void` prop + a "+ Shortlist" button — add that prop+button in Task 4's `targeting-bar.tsx`; if missed, add it here by editing targeting-bar.)

- [ ] **Step 4: Toolbar "Shortlist (N)" button** — next to "Exporter":
```tsx
          <Button variant="outline" size="sm" onClick={() => setShortlistOpen(true)}>
            Shortlist ({shortlist.length})
          </Button>
```

- [ ] **Step 5: Render the Sheet** (near `<ExclusionsDialog … />`):
```tsx
      <ShortlistSheet
        open={shortlistOpen}
        onOpenChange={setShortlistOpen}
        keywords={shortlistKeywords}
        onRemove={removeFromShortlist}
        onClear={clearShortlist}
      />
```

- [ ] **Step 6: Pages** — in `recherche/page.tsx`: fetch the active site object (it's in `sites`), pass `countries={site.targetCountries}` and `initialShortlist={…}` (add `fetchShortlistAction(siteId)` to the `Promise.all`). Same in `recherche-staged/page.tsx` (pass `countries` + `initialShortlist`). The active site: `const site = sites.find((s) => s.id === siteId);` → `site?.targetCountries ?? []`.

- [ ] **Step 7:** `npx tsc --noEmit` → **must be clean now** (all call sites wired). `bunx biome check --write` the touched files. Commit:
```bash
git add src/components/keywords/research/research-container.tsx "src/app/(app)/keywords/recherche/page.tsx" "src/app/(app)/keywords/recherche-staged/page.tsx" src/components/keywords/research/targeting-bar.tsx
git commit -m "feat(keywords): wire site countries + shortlist into Recherche"
```

---

## Task 8: Live verification + finish

- [ ] **Step 1:** Full `npx tsc --noEmit` clean; `bun test src/lib/keywords/seasonal.test.ts` (4) green; `bun run test src/lib/keywords/actions.test.ts` green; biome clean on all touched files.
- [ ] **Step 2: Live (d3k, dev :3000, Acieb, service running):**
  - `/keywords/recherche` → matrix view shows a **FR** column (not US/GB/CA/AU); targeting a keyword in FR persists.
  - Select keywords → "+ Shortlist" → "Shortlist (N)" updates → open the Sheet → keywords listed, remove one, "Vider".
  - Colonnes menu → enable **Saisonnier** → rows with a near peak show "🌟 pic dans N mois".
- [ ] **Step 3: Finish** — superpowers:finishing-a-development-branch (ff-merge → dev, push, delete branch).

---

## Notes
- `stageFilter` untouched; the staged page keeps working (gets `countries` + `initialShortlist` too).
- `TARGET_COUNTRIES` stays exported (no consumer should break — only the 3 research components stop importing it).
- Out of scope: F6 fan-out, free-form country add, legacy keyword_target cleanup, real per-country metrics.