// src/lib/content/actions.ts — content item CRUD + keyword bridge.
"use server";
import { randomUUID } from "node:crypto";
import { and, eq, inArray, sql } from "drizzle-orm";
import { revalidatePath, revalidateTag } from "next/cache";
import { db } from "@/lib/db/client";
import type { ContentStatus } from "@/lib/db/domain-schema";
import {
  contentItems,
  contentKeywords,
  keywords,
  keywordTargets,
  sitePages,
} from "@/lib/db/domain-schema";
import {
  filterKeywordIdsForSite,
  keywordOverviewTag,
} from "@/lib/keywords/queries";
import type { KeywordBrief } from "@/lib/keywords/ytg-types";
import { resolvePageForKeyword } from "@/lib/pages/attach";
import type { PageRef } from "@/lib/pages/mapping";
import {
  assertContentItemInActiveOrg,
  assertKeywordInActiveOrg,
  assertSiteInActiveOrg,
} from "@/lib/sites/assert-site";
import type { ContentExportRow } from "./csv";
import type { OnlineRow } from "./online";
import { buildContentOverview, type ContentOverview } from "./overview";
import { getContentItemsForSite, getPublishedContentForSite } from "./queries";
import { normalizeUrl } from "./stats-logic";
import type { ContentItem } from "./types";

export async function getContentItemsForSiteAction(
  siteId: string,
): Promise<ContentItem[]> {
  await assertSiteInActiveOrg(siteId);
  return getContentItemsForSite(siteId);
}

export async function getContentOverviewAction(
  siteId: string,
): Promise<ContentOverview> {
  await assertSiteInActiveOrg(siteId);
  const items = await getContentItemsForSite(siteId);
  return buildContentOverview(items, new Date().toISOString());
}

/** Rows complètes pour l'export CSV (items + termes principal/secondaires). */
export async function getContentExportRowsAction(
  siteId: string,
): Promise<ContentExportRow[]> {
  await assertSiteInActiveOrg(siteId);
  const items = await db
    .select({
      id: contentItems.id,
      title: contentItems.title,
      status: contentItems.status,
      lang: contentItems.lang,
      country: contentItems.country,
      assignee: contentItems.assignee,
      dueDate: contentItems.dueDate,
      url: contentItems.url,
      primaryKeywordTerm: keywords.term,
    })
    .from(contentItems)
    .leftJoin(
      keywords,
      and(
        eq(keywords.id, contentItems.primaryKeywordId),
        eq(keywords.siteId, contentItems.siteId),
      ),
    )
    .where(eq(contentItems.siteId, siteId))
    .orderBy(contentItems.createdAt);
  const secondaries = await db
    .select({
      contentItemId: contentKeywords.contentItemId,
      term: keywords.term,
    })
    .from(contentKeywords)
    .innerJoin(
      keywords,
      and(
        eq(keywords.id, contentKeywords.keywordId),
        eq(keywords.siteId, contentKeywords.siteId),
      ),
    )
    .innerJoin(contentItems, eq(contentItems.id, contentKeywords.contentItemId))
    .where(
      and(
        eq(contentItems.siteId, siteId),
        eq(contentKeywords.role, "secondary"),
      ),
    );
  const secondaryByItem = new Map<string, string[]>();
  for (const s of secondaries) {
    const terms = secondaryByItem.get(s.contentItemId) ?? [];
    terms.push(s.term);
    secondaryByItem.set(s.contentItemId, terms);
  }
  return items.map((r) => ({
    title: r.title,
    status: r.status,
    primaryKeywordTerm: r.primaryKeywordTerm ?? null,
    secondaryKeywordTerms: secondaryByItem.get(r.id) ?? [],
    lang: r.lang,
    country: r.country,
    assignee: r.assignee,
    dueDate: r.dueDate ? r.dueDate.toISOString().slice(0, 10) : null,
    url: r.url,
  }));
}

export async function getPublishedContentAction(
  siteId: string,
): Promise<OnlineRow[]> {
  await assertSiteInActiveOrg(siteId);
  return getPublishedContentForSite(siteId);
}

export async function createContentItemFromFormAction(
  siteId: string,
  input: {
    title: string;
    primaryKeywordId?: string | null;
    lang?: string;
    country?: string;
  },
): Promise<void> {
  await assertSiteInActiveOrg(siteId);
  if (input.primaryKeywordId) {
    // ELEVA-72 — le mot-clé principal doit appartenir au même site.
    const kwSiteId = await assertKeywordInActiveOrg(input.primaryKeywordId);
    if (kwSiteId !== siteId) {
      throw new Error("Keyword does not belong to the content item's site");
    }
  }
  const title = input.title.trim();
  if (!title) return;
  await db.insert(contentItems).values({
    id: randomUUID(),
    siteId,
    title,
    status: "idea",
    primaryKeywordId: input.primaryKeywordId ?? null,
    lang: input.lang ?? "fr",
    country: input.country ?? "FR",
  });
  revalidatePath("/content/planning");
}

/** Dispatch transmutation: creates a "brief" content item from a planned
 *  keyword × country target, and promotes that target to "live". */
export async function createContentItemAction(
  keywordId: string,
  country: string,
  payload: { title: string; dueDate: string | null; assignee: string | null },
): Promise<{ id: string }> {
  await assertKeywordInActiveOrg(keywordId);
  const [kw] = await db
    .select({
      siteId: keywords.siteId,
      lang: keywords.lang,
      term: keywords.term,
    })
    .from(keywords)
    .where(eq(keywords.id, keywordId))
    .limit(1);
  if (!kw) throw new Error("Keyword not found");
  const id = randomUUID();
  await db.transaction(async (tx) => {
    await tx.insert(contentItems).values({
      id,
      siteId: kw.siteId,
      title: payload.title.trim() || kw.term,
      status: "brief",
      primaryKeywordId: keywordId,
      lang: kw.lang,
      country,
      assignee: payload.assignee,
      dueDate: payload.dueDate ? new Date(payload.dueDate) : null,
    });
    await tx
      .update(keywordTargets)
      .set({ status: "live" })
      .where(
        and(
          eq(keywordTargets.keywordId, keywordId),
          eq(keywordTargets.country, country),
        ),
      );
  });
  revalidatePath("/content/planning");
  return { id };
}

/**
 * Décision de promotion par-keyword (mode `per-keyword` uniquement) :
 * - `optimize` : fusionner dans / créer une fiche liée à une page existante ;
 * - `create` : créer une fiche neuve (`warnedPageUrl` = trace de cannibalisation
 *   si l'utilisateur ignore une page résolue).
 */
export type PromoteDecision =
  | { action: "optimize"; pageUrl: string; pageId: string }
  | { action: "create"; warnedPageUrl?: string };

export type PromoteResult = {
  created: number;
  optimized: number;
  merged: number;
  skipped: string[];
};

/** Promote keywords to contenu. "per-keyword": N items (skip ids already primary of an
 *  item); "one-page": 1 item, `primaryId` (default first) primary, rest secondary.
 *
 *  `decisions` (mode per-keyword uniquement) bifurque chaque kw en optimize/create.
 *  Sans `decisions`, le comportement des DEUX modes est inchangé (create, kind "new"). */
export async function promoteKeywordsToContentAction(
  siteId: string,
  keywordIds: string[],
  mode: "per-keyword" | "one-page",
  primaryId?: string,
  decisions?: Record<string, PromoteDecision>,
): Promise<PromoteResult> {
  await assertSiteInActiveOrg(siteId);
  if (keywordIds.length === 0)
    return { created: 0, optimized: 0, merged: 0, skipped: [] };
  const kws = await db
    .select({
      id: keywords.id,
      term: keywords.term,
      lang: keywords.lang,
      country: keywords.country,
    })
    .from(keywords)
    .where(and(eq(keywords.siteId, siteId), inArray(keywords.id, keywordIds)));

  if (mode === "one-page") {
    // Le mode « 1 page (groupe) » ignore toujours `decisions` — inchangé.
    const primary = kws.find((k) => k.id === primaryId) ?? kws[0];
    if (!primary) return { created: 0, optimized: 0, merged: 0, skipped: [] };
    const itemId = randomUUID();
    await db.transaction(async (tx) => {
      await tx.insert(contentItems).values({
        id: itemId,
        siteId,
        title: primary.term,
        status: "idea" as ContentStatus,
        primaryKeywordId: primary.id,
        lang: primary.lang,
        country: primary.country,
      });
      await tx.insert(contentKeywords).values(
        kws.map((k) => ({
          contentItemId: itemId,
          keywordId: k.id,
          siteId,
          role: (k.id === primary.id ? "primary" : "secondary") as
            | "primary"
            | "secondary",
        })),
      );
    });
    revalidatePath("/content/planning");
    return { created: 1, optimized: 0, merged: 0, skipped: [] };
  }

  // ── per-keyword avec décisions (optimize / create) ──
  if (decisions) {
    return promotePerKeywordWithDecisions(siteId, keywordIds, kws, decisions);
  }

  // ── per-keyword historique (create, kind "new") ──
  const covered = new Set(
    (
      await db
        .select({ kid: contentItems.primaryKeywordId })
        .from(contentItems)
        .where(eq(contentItems.siteId, siteId))
    ).map((r) => r.kid),
  );
  const toCreate = kws.filter((k) => !covered.has(k.id));
  const skipped = kws.filter((k) => covered.has(k.id)).map((k) => k.term);
  if (toCreate.length === 0)
    return { created: 0, optimized: 0, merged: 0, skipped };
  const rows = toCreate.map((k) => ({ itemId: randomUUID(), k }));
  await db.transaction(async (tx) => {
    await tx.insert(contentItems).values(
      rows.map(({ itemId, k }) => ({
        id: itemId,
        siteId,
        title: k.term,
        status: "idea" as ContentStatus,
        primaryKeywordId: k.id,
        lang: k.lang,
        country: k.country,
      })),
    );
    await tx.insert(contentKeywords).values(
      rows.map(({ itemId, k }) => ({
        contentItemId: itemId,
        keywordId: k.id,
        siteId,
        role: "primary" as const,
      })),
    );
  });
  revalidatePath("/content/planning");
  return { created: toCreate.length, optimized: 0, merged: 0, skipped };
}

type PromoteKw = {
  id: string;
  term: string;
  lang: string;
  country: string;
};

/**
 * Cœur de la bifurcation optimize/create. Un seul lot transactionnel.
 * - `create` : fiche neuve `kind: "new"` (+ warnedPageUrl si fourni) ; skip si
 *   le kw est déjà primaire d'une fiche.
 * - `optimize` : la page (`pageId`) DOIT appartenir au site (tenancy) ; on
 *   fusionne par URL normalisée dans une fiche managée existante (kind bascule,
 *   published→writing, kw ajouté en secondary idempotent) ou on crée une fiche
 *   `kind: "optimize"` ; le kw reçoit `targetPage = manual`.
 */
async function promotePerKeywordWithDecisions(
  siteId: string,
  keywordIds: string[],
  kws: PromoteKw[],
  decisions: Record<string, PromoteDecision>,
): Promise<PromoteResult> {
  const kwById = new Map(kws.map((k) => [k.id, k]));

  // Fiches managées du site : url BRUTE en base → clé normalisée (fusion JS).
  const itemRows = await db
    .select({
      id: contentItems.id,
      url: contentItems.url,
      primaryKeywordId: contentItems.primaryKeywordId,
    })
    .from(contentItems)
    .where(eq(contentItems.siteId, siteId));
  const managedByUrl = new Map<
    string,
    { id: string; primaryKeywordId: string | null }
  >();
  for (const it of itemRows) {
    if (!it.url) continue;
    const key = normalizeUrl(it.url);
    if (!managedByUrl.has(key))
      managedByUrl.set(key, {
        id: it.id,
        primaryKeywordId: it.primaryKeywordId,
      });
  }

  // Liens existants (pour idempotence + matérialisation du primary legacy).
  const linkRows = await db
    .select({
      contentItemId: contentKeywords.contentItemId,
      keywordId: contentKeywords.keywordId,
      role: contentKeywords.role,
    })
    .from(contentKeywords)
    .where(eq(contentKeywords.siteId, siteId));
  const linksByItem = new Map<
    string,
    { keywordId: string; role: "primary" | "secondary" }[]
  >();
  for (const l of linkRows) {
    const arr = linksByItem.get(l.contentItemId) ?? [];
    arr.push({ keywordId: l.keywordId, role: l.role });
    linksByItem.set(l.contentItemId, arr);
  }

  // Inventaire des pages du site : tenancy (page hors site refusée) + url/titre.
  // L'URL vient TOUJOURS d'ici (serveur), jamais de `decision.pageUrl` (client).
  const pageRows = await db
    .select({ id: sitePages.id, url: sitePages.url, title: sitePages.title })
    .from(sitePages)
    .where(eq(sitePages.siteId, siteId));
  const pageById = new Map(pageRows.map((p) => [p.id, p]));

  // Kw déjà primaires d'une fiche (skip des créations en double).
  const coveredPrimary = new Set(
    itemRows.map((r) => r.primaryKeywordId).filter((k): k is string => !!k),
  );
  // Fiche dont le kw est déjà primaire (ex. « Créer quand même » : url NULL,
  // hors managedByUrl). Une re-promotion « Optimiser » fusionne dans CETTE fiche.
  const itemByPrimaryKw = new Map<string, string>();
  for (const it of itemRows) {
    if (it.primaryKeywordId && !itemByPrimaryKw.has(it.primaryKeywordId))
      itemByPrimaryKw.set(it.primaryKeywordId, it.id);
  }

  let created = 0;
  let optimized = 0;
  let merged = 0;
  const skipped: string[] = [];

  await db.transaction(async (tx) => {
    for (const kwId of keywordIds) {
      const kw = kwById.get(kwId);
      if (!kw) continue; // kw inconnu / hors site
      const decision = decisions[kwId] ?? { action: "create" as const };

      if (decision.action === "create") {
        if (coveredPrimary.has(kwId)) {
          skipped.push(kw.term);
          continue;
        }
        const id = randomUUID();
        await tx.insert(contentItems).values({
          id,
          siteId,
          title: kw.term,
          status: "idea" as ContentStatus,
          primaryKeywordId: kwId,
          lang: kw.lang,
          country: kw.country,
          kind: "new",
          warnedPageUrl: decision.warnedPageUrl ?? null,
        });
        await tx.insert(contentKeywords).values({
          contentItemId: id,
          keywordId: kwId,
          siteId,
          role: "primary",
        });
        coveredPrimary.add(kwId);
        created++;
        continue;
      }

      // ── optimize ──
      const page = pageById.get(decision.pageId);
      if (!page) {
        // Tenancy : la page cible n'appartient pas au site → jamais touchée.
        skipped.push(kw.term);
        continue;
      }
      // URL server-trusted : la page validée en tenancy, PAS l'arg client.
      const key = normalizeUrl(page.url);
      const managed = managedByUrl.get(key);

      if (managed) {
        const links = linksByItem.get(managed.id) ?? [];
        const alreadyLinked =
          managed.primaryKeywordId === kwId ||
          links.some((l) => l.keywordId === kwId);
        if (alreadyLinked) {
          skipped.push(kw.term);
          continue;
        }
        // Matérialise le primary legacy (fiche sans lignes content_keywords) pour
        // ne pas le perdre en ajoutant une seule ligne secondary.
        const rows: {
          contentItemId: string;
          keywordId: string;
          siteId: string;
          role: "primary" | "secondary";
        }[] = [];
        if (links.length === 0 && managed.primaryKeywordId) {
          rows.push({
            contentItemId: managed.id,
            keywordId: managed.primaryKeywordId,
            siteId,
            role: "primary",
          });
        }
        rows.push({
          contentItemId: managed.id,
          keywordId: kwId,
          siteId,
          role: "secondary",
        });
        await tx.insert(contentKeywords).values(rows).onConflictDoNothing();
        await tx
          .update(contentItems)
          .set({
            kind: "optimize",
            status: sql`CASE WHEN ${contentItems.status} = 'published' THEN 'writing' ELSE ${contentItems.status} END`,
          })
          .where(
            and(
              eq(contentItems.id, managed.id),
              eq(contentItems.siteId, siteId),
            ),
          );
        await tx
          .update(keywords)
          .set({
            targetPageId: decision.pageId,
            targetPageSource: "manual",
            targetPageScore: null,
          })
          .where(and(eq(keywords.id, kwId), eq(keywords.siteId, siteId)));
        // État mémoire : le prochain kw du même lot voit la fusion.
        linksByItem.set(managed.id, [
          ...rows.map((r) => ({ keywordId: r.keywordId, role: r.role })),
          ...links,
        ]);
        merged++;
        continue;
      }

      // Le kw est DÉJÀ primaire d'une fiche non managée (url NULL, ex. « Créer
      // quand même ») → on l'adopte comme fiche optimize au lieu de dupliquer.
      const existingId = itemByPrimaryKw.get(kwId);
      if (existingId) {
        await tx
          .update(contentItems)
          .set({
            url: key,
            kind: "optimize",
            status: sql`CASE WHEN ${contentItems.status} = 'published' THEN 'writing' ELSE ${contentItems.status} END`,
          })
          .where(
            and(
              eq(contentItems.id, existingId),
              eq(contentItems.siteId, siteId),
            ),
          );
        await tx
          .update(keywords)
          .set({
            targetPageId: decision.pageId,
            targetPageSource: "manual",
            targetPageScore: null,
          })
          .where(and(eq(keywords.id, kwId), eq(keywords.siteId, siteId)));
        // La fiche porte désormais l'URL : un kw suivant du lot la retrouve.
        managedByUrl.set(key, { id: existingId, primaryKeywordId: kwId });
        merged++;
        continue;
      }

      // URL non managée → nouvelle fiche optimize (retrouvée en re-promotion).
      const id = randomUUID();
      await tx.insert(contentItems).values({
        id,
        siteId,
        title: page.title ?? kw.term,
        status: "idea" as ContentStatus,
        primaryKeywordId: kwId,
        lang: kw.lang,
        country: kw.country,
        kind: "optimize",
        url: key,
      });
      await tx.insert(contentKeywords).values({
        contentItemId: id,
        keywordId: kwId,
        siteId,
        role: "primary",
      });
      await tx
        .update(keywords)
        .set({
          targetPageId: decision.pageId,
          targetPageSource: "manual",
          targetPageScore: null,
        })
        .where(and(eq(keywords.id, kwId), eq(keywords.siteId, siteId)));
      // Fusion intra-lot : un 2ᵉ kw vers la même URL fusionne au lieu de dupliquer.
      managedByUrl.set(key, { id, primaryKeywordId: kwId });
      linksByItem.set(id, [{ keywordId: kwId, role: "primary" }]);
      coveredPrimary.add(kwId);
      optimized++;
    }
  });

  revalidatePath("/content/planning");
  // Les décisions optimize mutent keywords.targetPage* → resync réservoir/recherche.
  if (optimized > 0 || merged > 0) {
    revalidateTag(keywordOverviewTag(siteId), { expire: 0 });
    revalidatePath("/keywords/reservoir");
  }
  return { created, optimized, merged, skipped };
}

export type ResolvedForDialog = {
  keywordId: string;
  term: string;
  page: {
    pageId: string;
    url: string;
    source: string;
    confidence: "mapped" | "suggested";
    score: number | null;
  } | null;
};

/** Résout la page cible de chaque keyword (mapped en base > suggestion live > gap)
 *  pour alimenter le dialog de promotion. */
export async function resolvePagesForKeywordsAction(
  siteId: string,
  keywordIds: string[],
): Promise<ResolvedForDialog[]> {
  await assertSiteInActiveOrg(siteId);
  if (keywordIds.length === 0) return [];

  const kwRows = await db
    .select({
      id: keywords.id,
      term: keywords.term,
      targetPageId: keywords.targetPageId,
      targetPageSource: keywords.targetPageSource,
      targetPageScore: keywords.targetPageScore,
      targetPageUrl: sitePages.url,
    })
    .from(keywords)
    .leftJoin(sitePages, eq(sitePages.id, keywords.targetPageId))
    .where(and(eq(keywords.siteId, siteId), inArray(keywords.id, keywordIds)));

  const pages: PageRef[] = await db
    .select({
      id: sitePages.id,
      url: sitePages.url,
      title: sitePages.title,
      h1: sitePages.h1,
    })
    .from(sitePages)
    .where(eq(sitePages.siteId, siteId));

  return kwRows.map((kw) => {
    const resolved = resolvePageForKeyword(
      {
        id: kw.id,
        term: kw.term,
        targetPageId: kw.targetPageId,
        targetPage:
          kw.targetPageId && kw.targetPageUrl
            ? {
                url: kw.targetPageUrl,
                source: kw.targetPageSource ?? "manual",
                score: kw.targetPageScore,
              }
            : null,
      },
      pages,
    );
    return {
      keywordId: kw.id,
      term: kw.term,
      page: resolved
        ? {
            pageId: resolved.pageId,
            url: resolved.url,
            source: resolved.source,
            confidence: resolved.confidence,
            score: resolved.score,
          }
        : null,
    };
  });
}

export type ContentKeywordLink = {
  keywordId: string;
  term: string;
  role: "primary" | "secondary";
};

/** Linked keywords of an item; falls back to primaryKeywordId for pre-link items. */
export async function getContentKeywordsAction(
  contentItemId: string,
): Promise<ContentKeywordLink[]> {
  await assertContentItemInActiveOrg(contentItemId);
  const rows = await db
    .select({
      keywordId: contentKeywords.keywordId,
      term: keywords.term,
      role: contentKeywords.role,
    })
    .from(contentKeywords)
    .innerJoin(
      keywords,
      and(
        eq(keywords.id, contentKeywords.keywordId),
        eq(keywords.siteId, contentKeywords.siteId),
      ),
    )
    .where(eq(contentKeywords.contentItemId, contentItemId));
  if (rows.length > 0) {
    return rows.sort((a, b) =>
      a.role === "primary" ? -1 : b.role === "primary" ? 1 : 0,
    );
  }
  const item = (
    await db
      .select()
      .from(contentItems)
      .where(eq(contentItems.id, contentItemId))
  )[0];
  if (!item?.primaryKeywordId) return [];
  const kw = (
    await db
      .select({ term: keywords.term })
      .from(keywords)
      .where(eq(keywords.id, item.primaryKeywordId))
  )[0];
  if (!kw) return [];
  return [{ keywordId: item.primaryKeywordId, term: kw.term, role: "primary" }];
}

/** Make `keywordId` the item's primary (old primary → secondary); syncs primaryKeywordId. */
export async function setContentPrimaryKeywordAction(
  contentItemId: string,
  keywordId: string,
): Promise<void> {
  const itemSiteId = await assertContentItemInActiveOrg(contentItemId);
  const keywordSiteId = await assertKeywordInActiveOrg(keywordId);
  // ELEVA-72 — le mot-clé doit appartenir au même site que l'item (un id d'un
  // autre site de l'org passerait la garde org seule).
  if (keywordSiteId !== itemSiteId) {
    throw new Error("Keyword does not belong to the content item's site");
  }
  const links = await getContentKeywordsAction(contentItemId); // materialized or fallback view
  await db.transaction(async (tx) => {
    await tx
      .delete(contentKeywords)
      .where(eq(contentKeywords.contentItemId, contentItemId));
    const all = links.some((l) => l.keywordId === keywordId)
      ? links
      : [...links, { keywordId, term: "", role: "secondary" as const }];
    await tx.insert(contentKeywords).values(
      all.map((l) => ({
        contentItemId,
        keywordId: l.keywordId,
        siteId: itemSiteId,
        role: (l.keywordId === keywordId ? "primary" : "secondary") as
          | "primary"
          | "secondary",
      })),
    );
    await tx
      .update(contentItems)
      .set({ primaryKeywordId: keywordId })
      .where(eq(contentItems.id, contentItemId));
  });
  revalidatePath("/content/planning");
}

/** Remove a linked keyword; the primary is protected (set another primary first). */
export async function removeContentKeywordAction(
  contentItemId: string,
  keywordId: string,
): Promise<{ ok: boolean; error?: string }> {
  await assertContentItemInActiveOrg(contentItemId);
  const item = (
    await db
      .select()
      .from(contentItems)
      .where(eq(contentItems.id, contentItemId))
  )[0];
  if (item?.primaryKeywordId === keywordId) {
    return { ok: false, error: "Définis d'abord un autre mot-clé principal." };
  }
  await db
    .delete(contentKeywords)
    .where(
      and(
        eq(contentKeywords.contentItemId, contentItemId),
        eq(contentKeywords.keywordId, keywordId),
      ),
    );
  revalidatePath("/content/planning");
  return { ok: true };
}

/** Link keywords as secondaries (idempotent). Materializes the current view
 *  (legacy items only have primaryKeywordId) so the primary link survives. */
export async function addContentKeywordsAction(
  contentItemId: string,
  keywordIds: string[],
): Promise<void> {
  const siteId = await assertContentItemInActiveOrg(contentItemId);
  // ELEVA-72 — content_keywords est clé par id brut : ne lier que des
  // mots-clés du site de l'item (les ids étrangers sont écartés en silence).
  const unique = await filterKeywordIdsForSite(siteId, [
    ...new Set(keywordIds),
  ]);
  if (unique.length === 0) return;
  const current = await getContentKeywordsAction(contentItemId); // materialized or fallback view
  // Dedupe by keywordId, current links first: an added id that's already
  // linked keeps its existing role, and Postgres rejects intra-statement
  // duplicates under ON CONFLICT DO NOTHING ("cannot affect row a second time").
  const byKeywordId = new Map<string, "primary" | "secondary">();
  for (const l of current) byKeywordId.set(l.keywordId, l.role);
  for (const keywordId of unique) {
    if (!byKeywordId.has(keywordId)) byKeywordId.set(keywordId, "secondary");
  }
  await db
    .insert(contentKeywords)
    .values(
      [...byKeywordId].map(([keywordId, role]) => ({
        contentItemId,
        keywordId,
        siteId,
        role,
      })),
    )
    .onConflictDoNothing();
  revalidatePath("/content/planning");
}

/** Create one "idea" content item per selected keyword (title = term). Skips
 *  keywords that already have a content item. */
export async function createContentFromKeywordsAction(
  siteId: string,
  keywordIds: string[],
): Promise<{ created: number }> {
  await assertSiteInActiveOrg(siteId);
  const { created } = await promoteKeywordsToContentAction(
    siteId,
    keywordIds,
    "per-keyword",
  );
  return { created };
}

export async function updateContentStatusAction(
  id: string,
  status: ContentStatus,
): Promise<void> {
  await assertContentItemInActiveOrg(id);
  await db.update(contentItems).set({ status }).where(eq(contentItems.id, id));
  revalidatePath("/content/planning");
}

export async function updateContentItemAction(
  id: string,
  patch: {
    title?: string;
    assignee?: string | null;
    dueDate?: string | null;
    url?: string | null;
    status?: ContentStatus;
    primaryKeywordId?: string | null;
  },
): Promise<void> {
  const itemSiteId = await assertContentItemInActiveOrg(id);
  if (patch.primaryKeywordId) {
    // ELEVA-72 — le mot-clé principal doit appartenir au même site que l'item.
    const kwSiteId = await assertKeywordInActiveOrg(patch.primaryKeywordId);
    if (kwSiteId !== itemSiteId) {
      throw new Error("Keyword does not belong to the content item's site");
    }
  }
  await db
    .update(contentItems)
    .set({
      ...(patch.title !== undefined ? { title: patch.title } : {}),
      ...(patch.assignee !== undefined ? { assignee: patch.assignee } : {}),
      ...(patch.dueDate !== undefined
        ? { dueDate: patch.dueDate ? new Date(patch.dueDate) : null }
        : {}),
      ...(patch.url !== undefined ? { url: patch.url } : {}),
      ...(patch.status !== undefined ? { status: patch.status } : {}),
      ...(patch.primaryKeywordId !== undefined
        ? { primaryKeywordId: patch.primaryKeywordId }
        : {}),
    })
    .where(eq(contentItems.id, id));
  revalidatePath("/content/planning");
}

export async function deleteContentItemAction(id: string): Promise<void> {
  await assertContentItemInActiveOrg(id);
  await db.delete(contentItems).where(eq(contentItems.id, id));
  revalidatePath("/content/planning");
}

/** The linked keyword's briefs (for the detail sheet preview). */
export async function getKeywordBriefsAction(
  keywordId: string,
): Promise<KeywordBrief[]> {
  await assertKeywordInActiveOrg(keywordId);
  const [row] = await db
    .select({ briefs: keywords.briefs })
    .from(keywords)
    .where(eq(keywords.id, keywordId));
  return row?.briefs ? Object.values(row.briefs) : [];
}