# Content Table View — Design

- **Date**: 2026-06-08
- **Status**: approved (design)
- **Section**: contenu / planning
- **Depends on**: content planning board + `ContentCardSheet`, keyword overlays `serp` (slices 1-2) and `briefs` (YTG), on `dev`.

## Goal

Add a **table view** to Content → Planning (toggle with the existing kanban) so the pipeline + the **analysis-for-writing** signals are readable at a glance, without opening each card. The rich SERP/YTG analysis is condensed into **three tri-state status dots** (green/orange/red) with hover tooltips; clicking a row still opens the full `ContentCardSheet`.

## Layout

`ContentBoard` (the Planning container) gains `view: "board" | "table"` + a **Kanban · Tableau** toggle in its header (default `board`, unchanged). Both views share the loaded `items` and the same sheet/new-dialog. `table` renders a new `<ContentTable items onOpen={setOpen} />`.

```
[ Planning du contenu ]              [Kanban · Tableau]   [+ Nouvel article]
Titre        Statut  Mot-clé     Assigné  Échéance   URL  SERP On-page Brief
Guide solaire Brief  🇫🇷 panneau  Léa      12/06      ↗     🟢    🟠      🟢
```

The three dots replace verbose columns; their detail lives in tooltips.

## Dots (tri-state) + tooltips

Reuse shadcn `Tooltip` (as in `keywords/columns.tsx`). A `ContentDot` component renders a colored circle (`bg-green-500` / `bg-amber-500` / `bg-red-500`) wrapped in a tooltip.

- **SERP** — `ok` (green) = export imported for the item's locale; `none` (red) = none/no keyword.
  - tooltip(ok): `N concurrents · features: PAA, Video · médian backlinks X · autorité Y`
  - tooltip(none): `Aucun export SERP`
- **On-page** — `ok` (green) = snapshot crawled; `partial` (amber) = SERP imported but not crawled; `none` (red) = no SERP.
  - tooltip(ok): `Crawlé le DATE · img moy N · JSON-LD Z%`
  - tooltip(partial): `SERP importé — pas encore crawlé`
  - tooltip(none): `Pas de SERP à crawler`
- **Brief (YTG)** — `ok` (green) = brief for the item's locale; `other-locale` (amber) = brief exists for another locale; `none` (red) = none.
  - tooltip(ok): `Intention · sujets: a, b · N questions · entités: x, y`
  - tooltip(other-locale): `Brief dispo pour LOCALE (≠ pays de l'article)`
  - tooltip(none): `Pas de brief`

## Data model

Per content item we derive an analysis summary from the linked keyword's `serp` + `briefs`, keyed by the item's `keywordCountry`.

`src/lib/content/types.ts` — `ContentItem` gains `analysis: ContentAnalysis`:
```ts
export interface ContentAnalysis {
  serp: {
    state: "ok" | "none";
    count: number;
    features: string[];
    medianBacklinks: number | null;
    medianAuthority: number | null; // median pageAuthority
  };
  onpage: {
    state: "ok" | "partial" | "none";
    crawledAt: string | null;
    avgImages: number | null;
    jsonLdPct: number | null; // 0..100 over crawled competitors
  };
  brief: {
    state: "ok" | "other-locale" | "none";
    locale: string | null; // the locale the shown brief belongs to
    intent: string | null;
    subjects: string[];
    questions: number;
    entities: string[];
  };
}
```

## Derivation (pure, tested)

`src/lib/content/analysis.ts` — `buildContentAnalysis(serp, briefs, locale)`:
- `serp` = `Record<string, SerpSnapshot> | null`, `briefs` = `Record<string, KeywordBrief> | null`, `locale` = `keywordCountry | null`.
- **serp**: snap = `serp?.[locale]`. `ok` if snap with competitors; else `none`. `count` = competitors length. `features` = snap.features. `medianBacklinks` = median of competitor `backlinks` (non-null); `medianAuthority` = median of `pageAuthority`.
- **onpage**: `ok` if `snap.crawledAt`; `partial` if snap exists but no crawledAt; `none` if no snap. `avgImages` = mean of competitor `images` (non-null), rounded; `jsonLdPct` = round(100 × count(hasJsonLd===true) / count(hasJsonLd!=null)) or null.
- **brief**: `ok` if `briefs?.[locale]`; else `other-locale` if briefs non-empty; else `none`. From the chosen brief (locale's, else first): `intent` = primaryIntent || null, `subjects` = keySubjects, `questions` = questions.length, `entities` = entities.
- No keyword/locale → all `none`.

Helpers `median(nums)` / `mean(nums)` are local pure functions.

## Query

`src/lib/content/queries.ts` — `getContentItemsForSite` already left-joins `keywords`. Add `serp: keywords.serp` to the select (`briefs` is already selected as `keywordBriefs`), and set `analysis: buildContentAnalysis(r.serp, r.keywordBriefs, r.keywordCountry)` in the map. `hasBrief` stays (still used by the sheet).

## Components / files

```
src/lib/content/types.ts          — ContentItem += analysis; ContentAnalysis type
src/lib/content/analysis.ts       — buildContentAnalysis (pure) + median/mean
src/lib/content/analysis.test.ts  — unit tests
src/lib/content/queries.ts        — select serp + call buildContentAnalysis
src/components/content/content-dot.tsx      — tri-state dot + tooltip
src/components/content/content-columns.tsx  — ColumnDef<ContentItem>[] (pipeline + 3 dots)
src/components/content/content-table.tsx    — wraps DataTable (onRowClick = onOpen)
src/components/content/content-board.tsx     — view toggle + conditional render
```

Columns (sortable where it helps): **Titre** (truncate), **Statut** (Badge + `CONTENT_STATUS_LABEL`), **Mot-clé** (`countryFlag(keywordCountry)` + term, or "—"), **Assigné** (or "—"), **Échéance** (date, red when `isOverdue(dueDate, now)` && status≠published), **URL** (external link ↗, `stopPropagation`, "—" if null), **SERP** · **On-page** · **Brief** (the three `ContentDot`s). Row click → `onRowClick` → `onOpen(item)`.

## Toggle

Two segmented buttons (Kanban · Tableau) in the `ContentBoard` header, left of "+ Nouvel article". Local `useState`, default `"board"`. (View choice not persisted — YAGNI.)

## Error / empty states

- No active site / no items → existing empty/skeleton handling (shared with board).
- Item with no keyword → all three dots red, tooltips "Pas de …".
- Tooltip numbers null → omit that fragment (e.g. no median → skip "médian backlinks").

## Testing

- **Unit** (`analysis.test.ts`, pure): `buildContentAnalysis` — serp ok/none + median backlinks/authority; onpage ok/partial/none + avgImages + jsonLdPct; brief ok/other-locale/none + intent/subjects/questions/entities; no-keyword → all none; empty competitors.
- **Manual (d3k)**: toggle to Tableau → rows show pipeline + 3 dots; hover each dot → correct tooltip (SERP features/medians, on-page norms, brief essentials); a partially-processed item shows amber on-page; click a row → `ContentCardSheet` opens; toggle back to Kanban → drag-drop intact; `d3k errors` clean.

## Non-goals (YAGNI)

- Inline editing in the table (status/assignee) — stays in the sheet.
- Detailed on-page columns / expandable analysis row (tooltips suffice).
- Persisting the view choice; column show/hide; CSV export of the table.