# Content Online 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:** A per-site `/content/online` table of published content items with their linked keyword's real GSC performance + a KPI strip.

**Architecture:** A query `getPublishedContentForSite` (join keyword gsc), a pure tested `buildOnlineStats`, a server action, and a thin client container + KPI/table modules (reusing `StatCard`).

**Tech Stack:** Next 16 server actions, Drizzle/Postgres, bun + biome, shadcn. Reuses `StatCard`, `countryFlag`, `useActiveSite`.

Spec: `docs/superpowers/specs/2026-06-06-content-online-design.md`

---

## File structure

- Create `src/lib/content/online.ts` + `online.test.ts`.
- Modify `src/lib/content/queries.ts` — `getPublishedContentForSite`.
- Modify `src/lib/content/actions.ts` — `getPublishedContentAction`.
- Create `src/components/content/online/{online-container,online-kpis,online-table}.tsx`.
- Create `src/app/(app)/content/online/page.tsx`.
- Modify `src/components/app-sidebar.tsx` — add Online to the Contenu section.

Tooling: tsc `bunx tsc --noEmit`; pure tests `bun run test <file>`; DB tests `DB_TESTS=1 DATABASE_URL=postgresql://humperdoo@localhost:5432/seo_insight_test bun --conditions=react-server test <file>`; lint `bunx biome check --write <files>`. Dev server runs under d3k — do NOT start/restart it.

---

## Task 1: `online.ts` (types + buildOnlineStats) + query + action + tests

**Files:**
- Create: `src/lib/content/online.ts`
- Test: `src/lib/content/online.test.ts`
- Modify: `src/lib/content/queries.ts`
- Modify: `src/lib/content/actions.ts`

- [ ] **Step 1: `online.ts`**

```ts
// src/lib/content/online.ts — published-content performance rows + KPI stats.
export interface OnlineRow {
  id: string;
  title: string;
  url: string | null;
  keywordTerm: string | null;
  keywordCountry: string | null;
  position: number | null;
  clicks: number;
  impressions: number;
  ctr: number | null;
}

export interface OnlineStats {
  published: number;
  totalClicks: number;
  totalImpressions: number;
  avgPosition: number | null;
}

export function buildOnlineStats(rows: OnlineRow[]): OnlineStats {
  const withPos = rows.filter((r) => r.position != null);
  return {
    published: rows.length,
    totalClicks: rows.reduce((s, r) => s + r.clicks, 0),
    totalImpressions: rows.reduce((s, r) => s + r.impressions, 0),
    avgPosition: withPos.length
      ? Math.round(
          (withPos.reduce((s, r) => s + (r.position as number), 0) /
            withPos.length) *
            10,
        ) / 10
      : null,
  };
}
```

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

`src/lib/content/online.test.ts`:
```ts
import { expect, test } from "bun:test";
import { buildOnlineStats } from "./online";
import type { OnlineRow } from "./online";

let n = 0;
function row(over: Partial<OnlineRow> = {}): OnlineRow {
  n += 1;
  return {
    id: `o${n}`,
    title: `art ${n}`,
    url: null,
    keywordTerm: null,
    keywordCountry: null,
    position: null,
    clicks: 0,
    impressions: 0,
    ctr: null,
    ...over,
  };
}

test("buildOnlineStats sums clicks/impressions and averages position over rows-with-position", () => {
  const s = buildOnlineStats([
    row({ position: 8, clicks: 120, impressions: 2000 }),
    row({ position: 12, clicks: 30, impressions: 500 }),
    row({ position: null, clicks: 5, impressions: 100 }),
  ]);
  expect(s.published).toBe(3);
  expect(s.totalClicks).toBe(155);
  expect(s.totalImpressions).toBe(2600);
  expect(s.avgPosition).toBe(10); // (8+12)/2
});

test("avgPosition null when no row has a position; empty input", () => {
  expect(buildOnlineStats([row()]).avgPosition).toBeNull();
  expect(buildOnlineStats([])).toEqual({
    published: 0,
    totalClicks: 0,
    totalImpressions: 0,
    avgPosition: null,
  });
});
```

- [ ] **Step 3: Run — verify pass**

Run: `bun run test src/lib/content/online.test.ts`
Expected: PASS (2 tests).

- [ ] **Step 4: Add the query**

In `src/lib/content/queries.ts`, add `getPublishedContentForSite` (reuse the existing `db`, `eq`, `contentItems`, `keywords` imports — `and` may need adding from "drizzle-orm"):
```ts
import { and, eq } from "drizzle-orm";
import type { OnlineRow } from "./online";

export async function getPublishedContentForSite(
  siteId: string,
): Promise<OnlineRow[]> {
  const rows = await db
    .select({
      id: contentItems.id,
      title: contentItems.title,
      url: contentItems.url,
      keywordTerm: keywords.term,
      keywordCountry: keywords.country,
      gsc: keywords.gsc,
    })
    .from(contentItems)
    .leftJoin(keywords, eq(keywords.id, contentItems.primaryKeywordId))
    .where(
      and(eq(contentItems.siteId, siteId), eq(contentItems.status, "published")),
    );

  return rows.map((r) => ({
    id: r.id,
    title: r.title,
    url: r.url,
    keywordTerm: r.keywordTerm ?? null,
    keywordCountry: r.keywordCountry ?? null,
    position: r.gsc ? r.gsc.position : null,
    clicks: r.gsc ? r.gsc.clicks : 0,
    impressions: r.gsc ? r.gsc.impressions : 0,
    ctr: r.gsc ? r.gsc.ctr : null,
  }));
}
```
(Merge the `and, eq` import with the existing `eq` import line; do not duplicate.)

- [ ] **Step 5: Add the action**

In `src/lib/content/actions.ts`, add (merge imports):
```ts
import { getPublishedContentForSite } from "./queries";
import type { OnlineRow } from "./online";

export async function getPublishedContentAction(
  siteId: string,
): Promise<OnlineRow[]> {
  return getPublishedContentForSite(siteId);
}
```
(`getContentItemsForSite` is already imported from "./queries" — add `getPublishedContentForSite` to that same import.)

- [ ] **Step 6: Lint + typecheck + commit**

```bash
bunx biome check --write src/lib/content/online.ts src/lib/content/online.test.ts src/lib/content/queries.ts src/lib/content/actions.ts
bunx tsc --noEmit
git add src/lib/content/online.ts src/lib/content/online.test.ts src/lib/content/queries.ts src/lib/content/actions.ts
git commit -m "feat(content): online published-perf query + buildOnlineStats + action"
```

---

## Task 2: Modules + container + page + nav

**Files:**
- Create: the three components under `src/components/content/online/`
- Create: `src/app/(app)/content/online/page.tsx`
- Modify: `src/components/app-sidebar.tsx`

- [ ] **Step 1: `online-kpis.tsx`**

```tsx
// src/components/content/online/online-kpis.tsx
import { StatCard } from "@/components/keywords/overview/stat-card";
import type { OnlineStats } from "@/lib/content/online";

export function OnlineKpis({ stats }: { stats: OnlineStats }) {
  return (
    <div className="grid grid-cols-2 gap-3 sm:grid-cols-4">
      <StatCard label="Publiés" value={stats.published.toLocaleString("fr-FR")} />
      <StatCard
        label="Clics totaux"
        value={stats.totalClicks.toLocaleString("fr-FR")}
      />
      <StatCard
        label="Impressions"
        value={stats.totalImpressions.toLocaleString("fr-FR")}
      />
      <StatCard
        label="Position moyenne"
        value={stats.avgPosition?.toFixed(1) ?? "—"}
      />
    </div>
  );
}
```

- [ ] **Step 2: `online-table.tsx`**

```tsx
// src/components/content/online/online-table.tsx
import {
  Card,
  CardContent,
  CardHeader,
  CardTitle,
} from "@/components/ui/card";
import { countryFlag } from "@/lib/keywords/country-flag";
import type { OnlineRow } from "@/lib/content/online";

export function OnlineTable({ rows }: { rows: OnlineRow[] }) {
  return (
    <Card>
      <CardHeader>
        <CardTitle className="text-sm">Contenu en ligne</CardTitle>
      </CardHeader>
      <CardContent>
        <table className="w-full text-sm">
          <thead>
            <tr className="text-left text-muted-foreground text-xs">
              <th className="pb-2 font-medium">Titre</th>
              <th className="pb-2 font-medium">URL</th>
              <th className="pb-2 font-medium">Mot-clé</th>
              <th className="pb-2 text-right font-medium">Pos.</th>
              <th className="pb-2 text-right font-medium">Clics</th>
              <th className="pb-2 text-right font-medium">Impr.</th>
              <th className="pb-2 text-right font-medium">CTR</th>
            </tr>
          </thead>
          <tbody>
            {rows.map((r) => (
              <tr key={r.id} className="border-border/50 border-t">
                <td className="py-1.5 pr-2">{r.title}</td>
                <td className="py-1.5 pr-2">
                  {r.url ? (
                    <a
                      href={r.url}
                      target="_blank"
                      rel="noopener noreferrer"
                      className="max-w-[14rem] truncate text-primary hover:underline"
                    >
                      {r.url}
                    </a>
                  ) : (
                    <span className="text-muted-foreground">—</span>
                  )}
                </td>
                <td className="py-1.5 pr-2">
                  {r.keywordTerm ? (
                    <span className="inline-flex items-center gap-1">
                      <span aria-hidden>
                        {countryFlag(r.keywordCountry ?? "")}
                      </span>
                      <span className="max-w-[10rem] truncate">
                        {r.keywordTerm}
                      </span>
                    </span>
                  ) : (
                    <span className="text-muted-foreground">—</span>
                  )}
                </td>
                <td className="py-1.5 text-right tabular-nums">
                  {r.position != null ? r.position.toFixed(1) : "—"}
                </td>
                <td className="py-1.5 text-right tabular-nums">
                  {r.clicks.toLocaleString("fr-FR")}
                </td>
                <td className="py-1.5 text-right tabular-nums">
                  {r.impressions.toLocaleString("fr-FR")}
                </td>
                <td className="py-1.5 text-right tabular-nums">
                  {r.ctr != null ? `${(r.ctr * 100).toFixed(1)}%` : "—"}
                </td>
              </tr>
            ))}
          </tbody>
        </table>
      </CardContent>
    </Card>
  );
}
```

- [ ] **Step 3: `online-container.tsx`**

```tsx
// src/components/content/online/online-container.tsx
"use client";

import Link from "next/link";
import { useCallback, useEffect, useState } from "react";
import { Skeleton } from "@/components/ui/skeleton";
import { getPublishedContentAction } from "@/lib/content/actions";
import { buildOnlineStats } from "@/lib/content/online";
import type { OnlineRow } from "@/lib/content/online";
import { useActiveSite } from "@/lib/keywords/site-context";
import { OnlineKpis } from "./online-kpis";
import { OnlineTable } from "./online-table";

export function OnlineContainer() {
  const { activeSiteId } = useActiveSite();
  const [rows, setRows] = useState<OnlineRow[] | null>(null);
  const [loading, setLoading] = useState(true);

  const load = useCallback(async () => {
    setLoading(true);
    setRows(await getPublishedContentAction(activeSiteId));
    setLoading(false);
  }, [activeSiteId]);

  useEffect(() => {
    void load().catch(() => setLoading(false));
  }, [load]);

  if (loading || !rows) {
    return (
      <div className="space-y-3 p-4">
        <Skeleton className="h-20 w-full" />
        <Skeleton className="h-64 w-full" />
      </div>
    );
  }

  if (rows.length === 0) {
    return (
      <div className="flex h-64 flex-col items-center justify-center gap-2 text-muted-foreground">
        <p>Aucun contenu publié.</p>
        <Link
          href="/content/planning"
          className="text-sm underline hover:text-foreground"
        >
          Passer des articles en « Publié » dans le Planning
        </Link>
      </div>
    );
  }

  return (
    <div className="space-y-4 p-4">
      <OnlineKpis stats={buildOnlineStats(rows)} />
      <OnlineTable rows={rows} />
    </div>
  );
}
```

- [ ] **Step 4: `page.tsx`**

```tsx
// src/app/(app)/content/online/page.tsx
import { OnlineContainer } from "@/components/content/online/online-container";
import { DashboardHeader } from "@/components/dashboard-header";
import {
  Breadcrumb,
  BreadcrumbItem,
  BreadcrumbList,
  BreadcrumbPage,
  BreadcrumbSeparator,
} from "@/components/ui/breadcrumb";

export default function Page() {
  return (
    <>
      <DashboardHeader>
        <Breadcrumb>
          <BreadcrumbList>
            <BreadcrumbItem>Contenu</BreadcrumbItem>
            <BreadcrumbSeparator />
            <BreadcrumbItem>
              <BreadcrumbPage>Online</BreadcrumbPage>
            </BreadcrumbItem>
          </BreadcrumbList>
        </Breadcrumb>
      </DashboardHeader>
      <OnlineContainer />
    </>
  );
}
```

- [ ] **Step 5: Add "Online" to the Contenu nav**

In `src/components/app-sidebar.tsx`, the Contenu section's `items` becomes:
```tsx
      items: [
        { title: "Overview", url: "/content/overview" },
        { title: "Planning", url: "/content/planning" },
        { title: "Online", url: "/content/online" },
      ],
```

- [ ] **Step 6: Typecheck + lint + commit**

```bash
bunx tsc --noEmit
bunx biome check --write src/components/content/online/ "src/app/(app)/content/online/page.tsx" src/components/app-sidebar.tsx
git add src/components/content/online/ "src/app/(app)/content/online/page.tsx" src/components/app-sidebar.tsx
git commit -m "feat(content): online published-perf table + KPIs + page + nav"
```

---

## Task 3: Full checks + live verification

- [ ] **Step 1: Static checks**

```bash
bunx tsc --noEmit
bun run test src/lib/content/online.test.ts
bunx biome check src/lib/content src/components/content "src/app/(app)/content/online/page.tsx" src/components/app-sidebar.tsx
```
Expected: tsc 0; tests pass; biome clean.

- [ ] **Step 2: Live (d3k)**

`d3k agent-browser open http://localhost:3000/content/online`:
- On a site with no published content → empty-state with the Planning link.
- To exercise the populated view, on `/content/planning` set a content item (whose keyword has GSC, e.g. one created via "Vers Contenu" from a GSC keyword) to status "Publié" (via the card sheet) and give it a URL → the Online table shows it with the keyword flag + position/clics/impressions/CTR; KPIs match.
- The Contenu nav has Overview · Planning · Online. `d3k errors` clean.

- [ ] **Step 3: Final fixups commit (if any)**

```bash
git add -A && git commit -m "chore(content): online verification fixups"
```

---

## Notes / risks

- `keywords.gsc` is a jsonb `GscTracking | null`; the query reads it directly in the select and the mapper guards null → position null / clicks 0 / ctr null.
- Only `status = "published"` rows are listed; a published item with no URL shows "—" (still counts in KPIs).
- `StatCard` reused from `@/components/keywords/overview/`. CSS table consistent with the opportunities table.
- Branch: `feature/content-online` (already created off `dev`).
```