# Content Table View 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:** Add a Kanban/Tableau toggle to Content → Planning; the table shows pipeline columns + three tri-state analysis dots (SERP, On-page, Brief) with rich tooltips, reusing the existing DataTable and ContentCardSheet.

**Architecture:** A pure `buildContentAnalysis(serp, briefs, locale)` derives an analysis summary per content item from its linked keyword's `serp`/`briefs`; the query attaches it to `ContentItem.analysis`. New `ContentTable` wraps the TanStack `DataTable` with `contentColumns` (pipeline + 3 `ContentDot`s). `ContentBoard` gains a `view` toggle and renders board or table over the same loaded items.

**Tech Stack:** TanStack Table v8 (`@/components/ui/data-table`), shadcn Tooltip/Badge, Drizzle query, bun:test.

---

### Task 1: Analysis types + `buildContentAnalysis` (pure)

**Files:**
- Modify: `src/lib/content/types.ts`
- Create: `src/lib/content/analysis.ts`
- Test: `src/lib/content/analysis.test.ts`

- [ ] **Step 1: Add the `ContentAnalysis` type + `analysis` field**

In `src/lib/content/types.ts`, add the `ContentAnalysis` interface (above `ContentItem`) and add `analysis: ContentAnalysis;` as the last field of `ContentItem`:

```ts
export interface ContentAnalysis {
  serp: {
    state: "ok" | "none";
    count: number;
    features: string[];
    medianBacklinks: number | null;
    medianAuthority: number | null;
  };
  onpage: {
    state: "ok" | "partial" | "none";
    crawledAt: string | null;
    avgImages: number | null;
    jsonLdPct: number | null;
  };
  brief: {
    state: "ok" | "other-locale" | "none";
    locale: string | null;
    intent: string | null;
    subjects: string[];
    questions: number;
    entities: string[];
  };
}
```

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

Create `src/lib/content/analysis.test.ts`:

```ts
import { describe, expect, it } from "bun:test";
import { buildContentAnalysis } from "./analysis";

const comp = (over: Record<string, unknown>) => ({
  position: 1,
  domain: "d",
  url: "u",
  backlinks: null,
  refDomains: null,
  pageAuthority: null,
  searchTraffic: null,
  urlKeywords: null,
  title: null,
  metaDescription: null,
  lang: null,
  images: null,
  imagesWithoutAlt: null,
  imageAlts: null,
  missingAltSrcs: null,
  hasJsonLd: null,
  jsonLdTypes: null,
  crawledAt: null,
  ...over,
});

// biome-ignore lint/suspicious/noExplicitAny: test fixture
const snap = (over: Record<string, unknown> = {}): any => ({
  source: "se-ranking",
  importedAt: "x",
  features: ["People also ask"],
  competitors: [
    comp({ backlinks: 100, pageAuthority: 50, images: 10, hasJsonLd: true }),
    comp({ backlinks: 300, pageAuthority: 70, images: 20, hasJsonLd: false }),
  ],
  ...over,
});

// biome-ignore lint/suspicious/noExplicitAny: test fixture
const brief = (over: Record<string, unknown> = {}): any => ({
  source: "ytg",
  query: "",
  language: "",
  lang: "fr",
  country: "FR",
  importedAt: "",
  primaryIntent: "commercial",
  objectives: [],
  keySubjects: ["panneaux"],
  questions: ["q1", "q2"],
  tone: [],
  style: [],
  valueProps: [],
  topTerms: [],
  twoGrams: [],
  threeGrams: [],
  entities: ["EDF"],
  rawBrief: "",
  rawGuide: "",
  ...over,
});

describe("buildContentAnalysis", () => {
  it("derives serp ok + medians + features", () => {
    const a = buildContentAnalysis({ FR: snap() }, null, "FR");
    expect(a.serp.state).toBe("ok");
    expect(a.serp.count).toBe(2);
    expect(a.serp.medianBacklinks).toBe(200);
    expect(a.serp.medianAuthority).toBe(60);
    expect(a.serp.features).toContain("People also ask");
  });

  it("onpage ok when crawled (avgImages + jsonLdPct); partial otherwise", () => {
    const ok = buildContentAnalysis({ FR: snap({ crawledAt: "2026-06-08" }) }, null, "FR");
    expect(ok.onpage.state).toBe("ok");
    expect(ok.onpage.avgImages).toBe(15);
    expect(ok.onpage.jsonLdPct).toBe(50);
    const partial = buildContentAnalysis({ FR: snap() }, null, "FR");
    expect(partial.onpage.state).toBe("partial");
  });

  it("brief ok / other-locale / none", () => {
    expect(buildContentAnalysis(null, { FR: brief() }, "FR").brief.state).toBe("ok");
    const ok = buildContentAnalysis(null, { FR: brief() }, "FR").brief;
    expect(ok.intent).toBe("commercial");
    expect(ok.questions).toBe(2);
    expect(ok.subjects).toContain("panneaux");
    expect(buildContentAnalysis(null, { US: brief() }, "FR").brief.state).toBe("other-locale");
    expect(buildContentAnalysis(null, {}, "FR").brief.state).toBe("none");
  });

  it("no keyword/locale → all none", () => {
    const a = buildContentAnalysis(null, null, null);
    expect(a.serp.state).toBe("none");
    expect(a.onpage.state).toBe("none");
    expect(a.brief.state).toBe("none");
  });
});
```

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

Run: `bun --env-file=.env.local test src/lib/content/analysis.test.ts`
Expected: FAIL ("Cannot find module './analysis'").

- [ ] **Step 4: Implement `buildContentAnalysis`**

Create `src/lib/content/analysis.ts`:

```ts
// src/lib/content/analysis.ts — derive the at-a-glance writing analysis for a content item.
import type { SerpSnapshot } from "@/lib/keywords/serp-types";
import type { KeywordBrief } from "@/lib/keywords/ytg-types";
import type { ContentAnalysis } from "./types";

function median(nums: number[]): number | null {
  if (nums.length === 0) return null;
  const s = [...nums].sort((a, b) => a - b);
  const mid = Math.floor(s.length / 2);
  return s.length % 2 ? s[mid] : Math.round((s[mid - 1] + s[mid]) / 2);
}

function mean(nums: number[]): number | null {
  if (nums.length === 0) return null;
  return Math.round(nums.reduce((a, b) => a + b, 0) / nums.length);
}

export function buildContentAnalysis(
  serp: Record<string, SerpSnapshot> | null,
  briefs: Record<string, KeywordBrief> | null,
  locale: string | null,
): ContentAnalysis {
  const snap = locale && serp ? serp[locale] : undefined;
  const comps = snap?.competitors ?? [];
  const nn = (xs: (number | null)[]) => xs.filter((n): n is number => n != null);
  const jsonLdKnown = comps.filter((c) => c.hasJsonLd != null);
  const jsonLdTrue = jsonLdKnown.filter((c) => c.hasJsonLd === true);

  const briefMap = briefs ?? {};
  const briefKeys = Object.keys(briefMap);
  const chosen =
    (locale && briefMap[locale]) || (briefKeys.length ? briefMap[briefKeys[0]] : undefined);
  const briefState: ContentAnalysis["brief"]["state"] =
    locale && briefMap[locale] ? "ok" : briefKeys.length ? "other-locale" : "none";

  return {
    serp: {
      state: snap && comps.length > 0 ? "ok" : "none",
      count: comps.length,
      features: snap?.features ?? [],
      medianBacklinks: median(nn(comps.map((c) => c.backlinks))),
      medianAuthority: median(nn(comps.map((c) => c.pageAuthority))),
    },
    onpage: {
      state: !snap ? "none" : snap.crawledAt ? "ok" : "partial",
      crawledAt: snap?.crawledAt ?? null,
      avgImages: mean(nn(comps.map((c) => c.images))),
      jsonLdPct: jsonLdKnown.length
        ? Math.round((jsonLdTrue.length / jsonLdKnown.length) * 100)
        : null,
    },
    brief: {
      state: briefState,
      locale: chosen ? chosen.country || chosen.lang || null : null,
      intent: chosen?.primaryIntent || null,
      subjects: chosen?.keySubjects ?? [],
      questions: chosen?.questions.length ?? 0,
      entities: chosen?.entities ?? [],
    },
  };
}
```

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

Run: `bun --env-file=.env.local test src/lib/content/analysis.test.ts`
Expected: PASS (4 tests). Then `bunx tsc --noEmit` → PASS.

- [ ] **Step 6: Commit**

```bash
git add src/lib/content/types.ts src/lib/content/analysis.ts src/lib/content/analysis.test.ts
git commit -m "feat(content): buildContentAnalysis (SERP/on-page/brief writing summary)"
```

---

### Task 2: Attach `analysis` in the query

**Files:**
- Modify: `src/lib/content/queries.ts`

- [ ] **Step 1: Select `serp` + compute `analysis`**

In `src/lib/content/queries.ts`:
1. Add the import at the top: `import { buildContentAnalysis } from "./analysis";`
2. In `getContentItemsForSite`'s `.select({...})`, add `serp: keywords.serp,` next to `keywordBriefs: keywords.briefs,`.
3. In the `.map((r) => ({ ... }))` return object, add as the last property:

```ts
    analysis: buildContentAnalysis(r.serp ?? null, r.keywordBriefs ?? null, r.keywordCountry ?? null),
```

- [ ] **Step 2: Typecheck + existing content tests still pass**

Run:
```bash
bunx tsc --noEmit
psql seo_insight_test -tAc "truncate table content_items, keywords, backlinks, link_sources, keyword_themes, sites cascade;" >/dev/null
bun run test src/lib/content
```
Expected: tsc PASS; content suite all pass (existing tests don't assert `analysis`, so they remain green; the new field is additive).

- [ ] **Step 3: Commit**

```bash
git add src/lib/content/queries.ts
git commit -m "feat(content): attach analysis summary to content items query"
```

---

### Task 3: `ContentDot` (tri-state dot + tooltip)

**Files:**
- Create: `src/components/content/content-dot.tsx`

- [ ] **Step 1: Write the component**

```tsx
// src/components/content/content-dot.tsx — tri-state analysis indicator with tooltip.
"use client";

import type { ReactNode } from "react";
import {
  Tooltip,
  TooltipContent,
  TooltipProvider,
  TooltipTrigger,
} from "@/components/ui/tooltip";

const COLOR = {
  ok: "bg-green-500",
  partial: "bg-amber-500",
  none: "bg-red-500",
} as const;

export function ContentDot({
  state,
  tooltip,
}: {
  state: "ok" | "partial" | "none";
  tooltip: ReactNode;
}) {
  return (
    <TooltipProvider>
      <Tooltip>
        <TooltipTrigger asChild>
          <span
            className={`inline-block h-2.5 w-2.5 rounded-full ${COLOR[state]}`}
          />
        </TooltipTrigger>
        <TooltipContent className="max-w-xs">{tooltip}</TooltipContent>
      </Tooltip>
    </TooltipProvider>
  );
}
```

- [ ] **Step 2: Typecheck + lint**

Run: `bunx tsc --noEmit && bunx biome check src/components/content/content-dot.tsx`
Expected: PASS.

- [ ] **Step 3: Commit**

```bash
git add src/components/content/content-dot.tsx
git commit -m "feat(content): tri-state ContentDot with tooltip"
```

---

### Task 4: `contentColumns`

**Files:**
- Create: `src/components/content/content-columns.tsx`

- [ ] **Step 1: Write the column defs**

```tsx
// src/components/content/content-columns.tsx — table columns for the content table view.
"use client";

import type { ColumnDef } from "@tanstack/react-table";
import { Badge } from "@/components/ui/badge";
import { isOverdue } from "@/lib/content/board";
import type { ContentAnalysis, ContentItem } from "@/lib/content/types";
import { CONTENT_STATUS_LABEL } from "@/lib/content/types";
import { countryFlag } from "@/lib/keywords/country-flag";
import { ContentDot } from "./content-dot";

const dash = <span className="text-muted-foreground">—</span>;

function serpTooltip(s: ContentAnalysis["serp"]) {
  if (s.state === "none") return "Aucun export SERP";
  const parts = [`${s.count} concurrents`];
  if (s.features.length) parts.push(`features: ${s.features.join(", ")}`);
  if (s.medianBacklinks != null) parts.push(`médian backlinks ${s.medianBacklinks}`);
  if (s.medianAuthority != null) parts.push(`autorité ${s.medianAuthority}`);
  return parts.join(" · ");
}

function onpageTooltip(o: ContentAnalysis["onpage"]) {
  if (o.state === "none") return "Pas de SERP à crawler";
  if (o.state === "partial") return "SERP importé — pas encore crawlé";
  const parts = [
    `Crawlé le ${o.crawledAt ? new Date(o.crawledAt).toLocaleDateString("fr-FR") : "—"}`,
  ];
  if (o.avgImages != null) parts.push(`img moy ${o.avgImages}`);
  if (o.jsonLdPct != null) parts.push(`JSON-LD ${o.jsonLdPct}%`);
  return parts.join(" · ");
}

function briefTooltip(b: ContentAnalysis["brief"]) {
  if (b.state === "none") return "Pas de brief";
  if (b.state === "other-locale")
    return `Brief dispo pour ${b.locale ?? "autre locale"} (≠ pays de l'article)`;
  const parts: string[] = [];
  if (b.intent) parts.push(`Intention: ${b.intent}`);
  if (b.subjects.length) parts.push(`sujets: ${b.subjects.slice(0, 4).join(", ")}`);
  parts.push(`${b.questions} questions`);
  if (b.entities.length) parts.push(`entités: ${b.entities.slice(0, 4).join(", ")}`);
  return parts.join(" · ");
}

export const contentColumns: ColumnDef<ContentItem>[] = [
  {
    accessorKey: "title",
    header: "Titre",
    cell: ({ row }) => (
      <span className="block max-w-[260px] truncate">{row.original.title}</span>
    ),
  },
  {
    accessorKey: "status",
    header: "Statut",
    cell: ({ row }) => (
      <Badge variant="secondary">
        {CONTENT_STATUS_LABEL[row.original.status]}
      </Badge>
    ),
  },
  {
    id: "keyword",
    header: "Mot-clé",
    cell: ({ row }) =>
      row.original.keywordTerm ? (
        <span className="whitespace-nowrap">
          {countryFlag(row.original.keywordCountry)} {row.original.keywordTerm}
        </span>
      ) : (
        dash
      ),
  },
  {
    accessorKey: "assignee",
    header: "Assigné",
    cell: ({ row }) => row.original.assignee ?? dash,
  },
  {
    accessorKey: "dueDate",
    header: "Échéance",
    cell: ({ row }) => {
      const d = row.original.dueDate;
      if (!d) return dash;
      const over =
        isOverdue(d, new Date().toISOString()) &&
        row.original.status !== "published";
      return (
        <span className={over ? "text-destructive" : ""}>
          {new Date(d).toLocaleDateString("fr-FR")}
        </span>
      );
    },
  },
  {
    id: "url",
    header: "URL",
    cell: ({ row }) =>
      row.original.url ? (
        <a
          href={row.original.url}
          target="_blank"
          rel="noopener noreferrer"
          onClick={(e) => e.stopPropagation()}
          className="text-primary hover:underline"
        >
          ↗
        </a>
      ) : (
        dash
      ),
  },
  {
    id: "serp",
    header: "SERP",
    cell: ({ row }) => (
      <ContentDot
        state={row.original.analysis.serp.state}
        tooltip={serpTooltip(row.original.analysis.serp)}
      />
    ),
  },
  {
    id: "onpage",
    header: "On-page",
    cell: ({ row }) => (
      <ContentDot
        state={row.original.analysis.onpage.state}
        tooltip={onpageTooltip(row.original.analysis.onpage)}
      />
    ),
  },
  {
    id: "brief",
    header: "Brief",
    cell: ({ row }) => (
      <ContentDot
        state={row.original.analysis.brief.state}
        tooltip={briefTooltip(row.original.analysis.brief)}
      />
    ),
  },
];
```

- [ ] **Step 2: Typecheck + lint**

Run: `bunx tsc --noEmit && bunx biome check --write src/components/content/content-columns.tsx`
Expected: tsc PASS; biome fixes formatting only.

- [ ] **Step 3: Commit**

```bash
git add src/components/content/content-columns.tsx
git commit -m "feat(content): content table columns (pipeline + analysis dots)"
```

---

### Task 5: `ContentTable`

**Files:**
- Create: `src/components/content/content-table.tsx`

- [ ] **Step 1: Write the wrapper**

```tsx
// src/components/content/content-table.tsx — table view of content items.
"use client";

import { DataTable } from "@/components/ui/data-table";
import type { ContentItem } from "@/lib/content/types";
import { contentColumns } from "./content-columns";

export function ContentTable({
  items,
  onOpen,
}: {
  items: ContentItem[];
  onOpen: (item: ContentItem) => void;
}) {
  return (
    <DataTable
      columns={contentColumns}
      data={items}
      getRowId={(r) => r.id}
      onRowClick={onOpen}
    />
  );
}
```

- [ ] **Step 2: Typecheck**

Run: `bunx tsc --noEmit`
Expected: PASS.

- [ ] **Step 3: Commit**

```bash
git add src/components/content/content-table.tsx
git commit -m "feat(content): ContentTable wrapping DataTable"
```

---

### Task 6: Kanban/Tableau toggle in `ContentBoard`

**Files:**
- Modify: `src/components/content/content-board.tsx`

- [ ] **Step 1: Add the view state, toggle, and conditional render**

In `src/components/content/content-board.tsx`:

1. Add the import (with the other `./` imports):
```tsx
import { ContentTable } from "./content-table";
```
2. Add the view state next to the others (after `const [newOpen, setNewOpen] = useState(false);`):
```tsx
  const [view, setView] = useState<"board" | "table">("board");
```
3. Replace the header `<div className="flex items-center justify-between">…</div>` block with a version that has the toggle between the title and the button:
```tsx
      <div className="flex items-center justify-between">
        <h1 className="font-semibold text-lg">Planning du contenu</h1>
        <div className="flex items-center gap-2">
          <div className="flex rounded-md border p-0.5">
            <Button
              size="sm"
              variant={view === "board" ? "secondary" : "ghost"}
              className="h-7"
              onClick={() => setView("board")}
            >
              Kanban
            </Button>
            <Button
              size="sm"
              variant={view === "table" ? "secondary" : "ghost"}
              className="h-7"
              onClick={() => setView("table")}
            >
              Tableau
            </Button>
          </div>
          <Button size="sm" onClick={() => setNewOpen(true)}>
            + Nouvel article
          </Button>
        </div>
      </div>
```
4. Replace the loading/board block (the `{loading ? <Skeleton…/> : <DndContext>…</DndContext>}`) so the table is shown when `view === "table"`:
```tsx
      {loading ? (
        <Skeleton className="h-64 w-full" />
      ) : view === "table" ? (
        <ContentTable items={items} onOpen={setOpen} />
      ) : (
        <DndContext sensors={sensors} onDragEnd={onDragEnd}>
          <div className="flex gap-3 overflow-x-auto pb-2">
            {columns.map((c) => (
              <ContentColumn
                key={c.status}
                status={c.status}
                items={c.items}
                nowIso={nowIso}
                onOpen={setOpen}
              />
            ))}
          </div>
        </DndContext>
      )}
```

- [ ] **Step 2: Typecheck + lint**

Run: `bunx tsc --noEmit && bunx biome check --write src/components/content/content-board.tsx`
Expected: tsc PASS; biome fixes formatting only.

- [ ] **Step 3: Commit**

```bash
git add src/components/content/content-board.tsx
git commit -m "feat(content): Kanban/Tableau view toggle on Planning"
```

---

### Task 7: Live verification (d3k) + sweep

**Files:** none (verification only).

- [ ] **Step 1: Full sweep**

Run:
```bash
bunx tsc --noEmit
psql seo_insight_test -tAc "truncate table content_items, keywords, backlinks, link_sources, keyword_themes, sites cascade;" >/dev/null
bun run test src/lib/content
```
Expected: tsc clean; content suite (incl. `analysis.test.ts`) all green.

- [ ] **Step 2: Seed a verifiable item + crawl state**

On the acieb dev keyword `…-comparateur-energie-pro`: import the real SERP CSV (so `serp.FR` has competitors) and optionally crawl (or seed a `crawledAt`) so dots differ; link a content item; (optionally seed a brief) so all three dots show meaningful states.

- [ ] **Step 3: Verify via d3k agent-browser**

```bash
d3k agent-browser open "http://localhost:3000/content/planning"
# click the "Tableau" toggle:
d3k agent-browser eval '(()=>{const b=[...document.querySelectorAll("button")].find(x=>x.textContent.trim()==="Tableau"); b?.click(); return !!b})()'
d3k agent-browser wait 600
d3k agent-browser screenshot /tmp/content-table.png
```
Verify (screenshot + eval): rows render with Titre/Statut/Mot-clé(flag)/Assigné/Échéance/URL + three colored dots; hovering a dot shows the right tooltip (SERP medians/features, on-page norms, brief essentials); a partially-processed item shows an amber on-page dot; clicking a row opens `ContentCardSheet`; clicking "Kanban" returns to the board and drag-drop still works. `d3k errors` → clean.

- [ ] **Step 4: Clean up** any throwaway seed.

---

## Done criteria

- `buildContentAnalysis` shipped + tested; `ContentItem.analysis` populated by the query.
- `ContentDot`, `contentColumns`, `ContentTable` shipped; `ContentBoard` toggles Kanban/Tableau over shared items.
- Three tri-state dots with correct tooltips; row click opens the sheet; kanban drag-drop intact.
- tsc clean, content suite green, d3k verified, `d3k errors` clean.
- Branch `feature/content-table-view` merged into `dev` (ff-only), deleted, pushed.
```