// src/lib/keywords/queries.test.ts
// Integration test — gated on DB_TESTS=1, run against the *_test DB only.
import { beforeEach, describe, expect, mock, test } from "bun:test";

// queries.ts imports "server-only" (throws outside Next) — stub it for bun test.
mock.module("server-only", () => ({}));

const RUN_DB = process.env.DB_TESTS === "1";

describe.skipIf(!RUN_DB)("getKeywordsForSite (postgres)", () => {
  let db: typeof import("@/lib/db/client").db;
  let schema: typeof import("@/lib/db/domain-schema");
  let queries: typeof import("./queries");

  // Two sites seeded in seo_insight_test (from db:seed).
  const SITE_A = "lustre-lab";
  const SITE_B = "atelier-forge";

  beforeEach(async () => {
    ({ db } = await import("@/lib/db/client"));
    schema = await import("@/lib/db/domain-schema");
    queries = await import("./queries");
    const { assertTestDb } = await import("@/lib/db/assert-test-db");
    await assertTestDb();
    // Clean slate (children first for FKs).
    await db.delete(schema.keywords);
    await db.delete(schema.sites);
    // Insert the two sites the keyword FK requires (auth anchors demo-org /
    // seed-owner are guaranteed by the test-DB migration setup).
    await db.insert(schema.sites).values([
      {
        id: SITE_A,
        orgId: "demo-org",
        ownerUserId: "seed-owner",
        name: "Lustre Lab",
        url: "https://lustrelab.com",
        primaryLanguage: "fr",
        languages: [],
        targetCountries: ["FR"],
      },
      {
        id: SITE_B,
        orgId: "demo-org",
        ownerUserId: "seed-owner",
        name: "Atelier Forge",
        url: "https://atelierforge.com",
        primaryLanguage: "fr",
        languages: [],
        targetCountries: ["FR"],
      },
    ]);
  });

  async function insertKeyword(id: string, siteId: string, term: string) {
    await db.insert(schema.keywords).values({
      id,
      siteId,
      term,
      lang: "fr",
      country: "FR",
      theme: "lab-diamonds",
      status: "idea",
      intent: ["informational"],
      primarySource: "mangools",
      sources: ["mangools"],
      metrics: {
        mangools: {
          volume: 100,
          kd: 10,
          cpc: 1.2,
          growthPct: 5,
          updatedAt: "2025-12-01",
        },
      },
      trend: [{ month: "2025-12", volume: 100 }],
    });
  }

  test("returns only the site's keywords, sorted by term", async () => {
    await insertKeyword("k-b", SITE_A, "bravo");
    await insertKeyword("k-a", SITE_A, "alpha");
    await insertKeyword("k-x", SITE_B, "foreign");
    const rows = await queries.getKeywordsForSite(SITE_A);
    expect(rows.map((k) => k.term)).toEqual(["alpha", "bravo"]);
  });

  test("maps lang/country to locale and preserves jsonb metrics/trend", async () => {
    await insertKeyword("k-a", SITE_A, "alpha");
    const [k] = await queries.getKeywordsForSite(SITE_A);
    expect(k.locale).toEqual({ lang: "fr", country: "FR" });
    expect(k.metrics.mangools?.volume).toBe(100);
    expect(k.trend).toEqual([{ month: "2025-12", volume: 100 }]);
  });

  test("returns the full Keyword shape and excludes serp even when present (ELEVA-74)", async () => {
    // Seed a keyword carrying every mapped field PLUS a heavy serp jsonb — the
    // lean projection must drop serp from the read while keeping the mapped
    // Keyword object byte-identical to the legacy full-select mapping.
    const serpPayload = {
      FR: {
        fetchedAt: "2025-12-01T00:00:00.000Z",
        results: [{ rank: 1, url: "https://x.test", title: "X", domain: "x" }],
      },
    };
    await db.insert(schema.keywords).values({
      id: "k-serp",
      siteId: SITE_A,
      term: "with serp",
      lang: "fr",
      country: "FR",
      theme: "lab-diamonds",
      status: "active",
      intent: ["commercial"],
      stages: ["research", "planning"],
      primarySource: "ahrefs",
      sources: ["ahrefs", "gsc"],
      metrics: {
        ahrefs: {
          volume: 200,
          kd: 30,
          cpc: 2.5,
          growthPct: null,
          updatedAt: "2025-12-01",
        },
      },
      trend: [{ month: "2025-12", volume: 200 }],
      trendBySource: {},
      serpFeatures: ["featured_snippet"],
      briefs: {},
      gsc: {
        clicks: 5,
        impressions: 100,
        ctr: 0.05,
        position: 8,
        updatedAt: "2025-12-01",
        page: "https://x.test/p",
      },
      gscByCountry: {
        FR: {
          clicks: 3,
          impressions: 60,
          ctr: 0.05,
          position: 7,
          updatedAt: "2025-12-01",
        },
      },
      enrichmentExports: { ahrefs: "2025-12-01" },
      // biome-ignore lint/suspicious/noExplicitAny: test fixture jsonb shape
      serp: serpPayload as any,
    });

    const rows = await queries.getKeywordsForSite(SITE_A);
    const k = rows.find((r) => r.id === "k-serp");
    expect(k).toBeDefined();
    // The mapped object has exactly the Keyword fields — no `serp` leaks through.
    expect(k && "serp" in k).toBe(false);
    expect(k).toEqual({
      id: "k-serp",
      siteId: SITE_A,
      term: "with serp",
      locale: { lang: "fr", country: "FR" },
      theme: "lab-diamonds",
      status: "active",
      intent: ["commercial"],
      stages: ["research", "planning"],
      primarySource: "ahrefs",
      sources: ["ahrefs", "gsc"],
      metrics: {
        ahrefs: {
          volume: 200,
          kd: 30,
          cpc: 2.5,
          growthPct: null,
          updatedAt: "2025-12-01",
        },
      },
      trend: [{ month: "2025-12", volume: 200 }],
      trendBySource: {},
      serpFeatures: ["featured_snippet"],
      briefs: {},
      gsc: {
        clicks: 5,
        impressions: 100,
        ctr: 0.05,
        position: 8,
        updatedAt: "2025-12-01",
        page: "https://x.test/p",
      },
      gscByCountry: {
        FR: {
          clicks: 3,
          impressions: 60,
          ctr: 0.05,
          position: 7,
          updatedAt: "2025-12-01",
        },
      },
      enrichmentExports: { ahrefs: "2025-12-01" },
      // Pas de page ciblée assignée (targetPageId NULL) → LEFT JOIN → null.
      targetPage: null,
    });
  });
});

describe.skipIf(!RUN_DB)("getPlanningKeywords targetPage (postgres)", () => {
  let db: typeof import("@/lib/db/client").db;
  let schema: typeof import("@/lib/db/domain-schema");
  let queries: typeof import("./queries");

  const SITE = "lustre-lab";
  const PAGE_ID = "lustre-lab-page-a";
  const PAGE_URL = "https://lustrelab.com/guides/lab-diamonds";

  beforeEach(async () => {
    ({ db } = await import("@/lib/db/client"));
    schema = await import("@/lib/db/domain-schema");
    queries = await import("./queries");
    const { assertTestDb } = await import("@/lib/db/assert-test-db");
    await assertTestDb();
    // Clean slate (children first: keywords references site_pages + sites).
    await db.delete(schema.keywords);
    await db.delete(schema.sitePages);
    await db.delete(schema.sites);
    await db.insert(schema.sites).values({
      id: SITE,
      orgId: "demo-org",
      ownerUserId: "seed-owner",
      name: "Lustre Lab",
      url: "https://lustrelab.com",
      primaryLanguage: "fr",
      languages: [],
      targetCountries: ["FR"],
    });
    await db.insert(schema.sitePages).values({
      id: PAGE_ID,
      siteId: SITE,
      url: PAGE_URL,
      status: 200,
      title: "Guide lab diamonds",
      crawledAt: new Date("2026-01-01T00:00:00.000Z"),
    });
  });

  async function insertPlanningKeyword(
    id: string,
    term: string,
    target?: { pageId: string; source: "manual" | "content" | "text" | "gsc" },
  ) {
    await db.insert(schema.keywords).values({
      id,
      siteId: SITE,
      term,
      lang: "fr",
      country: "FR",
      theme: "lab-diamonds",
      status: "active",
      intent: ["informational"],
      stages: ["planning"],
      primarySource: "mangools",
      sources: ["mangools"],
      metrics: {
        mangools: {
          volume: 100,
          kd: 10,
          cpc: 1.2,
          growthPct: 5,
          updatedAt: "2025-12-01",
        },
      },
      trend: [{ month: "2025-12", volume: 100 }],
      targetPageId: target?.pageId ?? null,
      targetPageSource: target?.source ?? null,
    });
  }

  test("populates keyword.targetPage from the site_pages LEFT JOIN", async () => {
    await insertPlanningKeyword("k-mapped", "bravo", {
      pageId: PAGE_ID,
      source: "content",
    });
    await insertPlanningKeyword("k-gap", "alpha");

    const rows = await queries.getPlanningKeywords(SITE);
    const mapped = rows.find((r) => r.keyword.id === "k-mapped");
    const gap = rows.find((r) => r.keyword.id === "k-gap");

    expect(mapped?.keyword.targetPage).toEqual({
      url: PAGE_URL,
      source: "content",
      score: null,
    });
    // Aucune page assignée (targetPageId NULL) → LEFT JOIN → targetPage null.
    expect(gap?.keyword.targetPage).toBeNull();
  });
});

describe.skipIf(!RUN_DB)("getKeywordsSyncStatus (postgres)", () => {
  let db: typeof import("@/lib/db/client").db;
  let schema: typeof import("@/lib/db/domain-schema");
  let queries: typeof import("./queries");

  const SITE_A = "lustre-lab";
  const SITE_B = "atelier-forge";

  beforeEach(async () => {
    ({ db } = await import("@/lib/db/client"));
    schema = await import("@/lib/db/domain-schema");
    queries = await import("./queries");
    const { assertTestDb } = await import("@/lib/db/assert-test-db");
    await assertTestDb();
    await db.delete(schema.keywords);
    await db.delete(schema.sites);
    await db.insert(schema.sites).values([
      {
        id: SITE_A,
        orgId: "demo-org",
        ownerUserId: "seed-owner",
        name: "Lustre Lab",
        url: "https://lustrelab.com",
        primaryLanguage: "fr",
        languages: [],
        targetCountries: ["FR"],
      },
      {
        id: SITE_B,
        orgId: "demo-org",
        ownerUserId: "seed-owner",
        name: "Atelier Forge",
        url: "https://atelierforge.com",
        primaryLanguage: "fr",
        languages: [],
        targetCountries: ["FR"],
      },
    ]);
  });

  async function insertKw(
    id: string,
    siteId: string,
    opts: { gscAt?: string; serankingAt?: string; mangoolsAt?: string },
  ) {
    await db.insert(schema.keywords).values({
      id,
      siteId,
      term: id,
      lang: "fr",
      country: "FR",
      theme: "lab-diamonds",
      status: "idea",
      intent: ["informational"],
      primarySource: "mangools",
      sources: ["mangools"],
      metrics: {
        ...(opts.serankingAt
          ? {
              seranking: {
                volume: 50,
                kd: 20,
                cpc: 0.5,
                growthPct: null,
                updatedAt: opts.serankingAt,
              },
            }
          : {}),
        ...(opts.mangoolsAt
          ? {
              mangools: {
                volume: 40,
                kd: 15,
                cpc: 0.4,
                growthPct: null,
                updatedAt: opts.mangoolsAt,
              },
            }
          : {}),
      },
      trend: [],
      gsc: opts.gscAt
        ? {
            clicks: 1,
            impressions: 10,
            ctr: 0.1,
            position: 5,
            updatedAt: opts.gscAt,
          }
        : null,
    });
  }

  test("returns the max gsc + seranking + mangools updatedAt for the site only", async () => {
    await insertKw("k1", SITE_A, {
      gscAt: "2026-01-01T00:00:00.000Z",
      serankingAt: "2026-02-01T00:00:00.000Z",
      mangoolsAt: "2026-02-10T00:00:00.000Z",
    });
    await insertKw("k2", SITE_A, {
      gscAt: "2026-03-01T00:00:00.000Z",
      serankingAt: "2026-01-15T00:00:00.000Z",
      mangoolsAt: "2026-04-01T00:00:00.000Z",
    });
    // Foreign site with later stamps — must NOT leak into SITE_A's status.
    await insertKw("k3", SITE_B, {
      gscAt: "2026-12-01T00:00:00.000Z",
      serankingAt: "2026-12-01T00:00:00.000Z",
      mangoolsAt: "2026-12-01T00:00:00.000Z",
    });

    const status = await queries.getKeywordsSyncStatus(SITE_A);
    expect(status).toEqual({
      lastGscSyncAt: "2026-03-01T00:00:00.000Z",
      lastSerankingSyncAt: "2026-02-01T00:00:00.000Z",
      lastMangoolsSyncAt: "2026-04-01T00:00:00.000Z",
    });
  });

  test("returns nulls when a source was never synced", async () => {
    await insertKw("k1", SITE_A, { gscAt: "2026-01-01T00:00:00.000Z" });
    const status = await queries.getKeywordsSyncStatus(SITE_A);
    expect(status).toEqual({
      lastGscSyncAt: "2026-01-01T00:00:00.000Z",
      lastSerankingSyncAt: null,
      lastMangoolsSyncAt: null,
    });
  });
});

describe.skipIf(!RUN_DB)("getTargetCandidates (postgres)", () => {
  let db: typeof import("@/lib/db/client").db;
  let schema: typeof import("@/lib/db/domain-schema");
  let queries: typeof import("./queries");

  const SITE = "lustre-lab";
  const OTHER_SITE = "atelier-forge";

  beforeEach(async () => {
    ({ db } = await import("@/lib/db/client"));
    schema = await import("@/lib/db/domain-schema");
    queries = await import("./queries");
    const { assertTestDb } = await import("@/lib/db/assert-test-db");
    await assertTestDb();
    await db.delete(schema.keywordTargets);
    await db.delete(schema.keywords);
    await db.delete(schema.sites);
    await db.insert(schema.sites).values([
      {
        id: SITE,
        orgId: "demo-org",
        ownerUserId: "seed-owner",
        name: "Lustre Lab",
        url: "https://lustrelab.com",
        primaryLanguage: "fr",
        languages: [],
        targetCountries: ["FR"],
      },
      {
        id: OTHER_SITE,
        orgId: "demo-org",
        ownerUserId: "seed-owner",
        name: "Atelier Forge",
        url: "https://atelierforge.com",
        primaryLanguage: "fr",
        languages: [],
        targetCountries: ["FR"],
      },
    ]);
  });

  /** `volume` nul = source primaire sans métrique : c'est exactement ce que
   *  produit un mot-clé saisi à la main ou importé de Search Console. */
  async function seed(
    id: string,
    term: string,
    volume: number | null,
    siteId = SITE,
  ) {
    await db.insert(schema.keywords).values({
      id,
      siteId,
      term,
      lang: "fr",
      country: "FR",
      status: "idea",
      intent: ["informational"],
      primarySource: "mangools",
      sources: ["mangools"],
      metrics:
        volume === null
          ? {}
          : {
              mangools: {
                volume,
                kd: 10,
                cpc: 1,
                growthPct: null,
                updatedAt: "2026-01-01",
              },
            },
    });
  }

  test("ne rend que les mots-clés du site", async () => {
    await seed("k-a", "alpha", 100);
    await seed("k-x", "étranger", 900, OTHER_SITE);
    const rows = await queries.getTargetCandidates(SITE, "FR");
    expect(rows.map((r) => r.id)).toEqual(["k-a"]);
  });

  test("trie par volume décroissant, valeurs nulles en dernier", async () => {
    await seed("k-null", "sans volume", null);
    await seed("k-low", "petit", 10);
    await seed("k-high", "gros", 5000);
    const rows = await queries.getTargetCandidates(SITE, "FR");
    expect(rows.map((r) => r.id)).toEqual(["k-high", "k-low", "k-null"]);
    expect(rows.map((r) => r.volume)).toEqual([5000, 10, null]);
  });

  test("exclut un mot-clé déjà ciblé sur ce pays en planned ou live", async () => {
    // Le cas qui coûte des données : `live` est AU-DESSUS de `planned`, et
    // l'écriture de l'étape est un onConflictDoUpdate aveugle. Reproposer un
    // mot-clé publié le ferait régresser en `planned` au premier clic.
    await seed("k-live", "publié", 5000);
    await seed("k-planned", "planifié", 4000);
    await seed("k-free", "libre", 10);
    await db.insert(schema.keywordTargets).values([
      { keywordId: "k-live", siteId: SITE, country: "FR", status: "live" },
      {
        keywordId: "k-planned",
        siteId: SITE,
        country: "FR",
        status: "planned",
      },
    ]);
    const rows = await queries.getTargetCandidates(SITE, "FR");
    expect(rows.map((r) => r.id)).toEqual(["k-free"]);
  });

  test("garde un mot-clé au statut candidate — le re-cibler est une promotion", async () => {
    // Test symétrique du précédent, et il asservit la frontière : `candidate`
    // est le statut PAR DÉFAUT du ciblage en masse et de la matrice Recherche,
    // et il ne coche PAS `target-country` (dérivé de `planned` seul). L'exclure
    // ferait dire « ton réservoir est encore vide » à un utilisateur qui vient
    // d'en marquer la moitié — message faux, geste proposé inutile.
    await seed("k-candidate", "candidat", 3000);
    await db.insert(schema.keywordTargets).values({
      keywordId: "k-candidate",
      siteId: SITE,
      country: "FR",
      status: "candidate",
    });
    const rows = await queries.getTargetCandidates(SITE, "FR");
    expect(rows.map((r) => r.id)).toEqual(["k-candidate"]);
  });

  test("départage les volumes nuls par terme, de façon stable", async () => {
    // Sans clé secondaire, Postgres rend un ordre arbitraire quand tous les
    // volumes sont nuls — cas normal d'un réservoir Search Console ou saisi à
    // la main — et `pickFirstTargetCandidate` (sort stable) proposerait un
    // terme différent d'un rendu à l'autre.
    await seed("k-3", "gamma", null);
    await seed("k-1", "alpha", null);
    await seed("k-2", "beta", null);
    const first = await queries.getTargetCandidates(SITE, "FR");
    const second = await queries.getTargetCandidates(SITE, "FR");
    expect(first.map((r) => r.term)).toEqual(["alpha", "beta", "gamma"]);
    expect(second.map((r) => r.term)).toEqual(first.map((r) => r.term));
  });

  test("une cible sur un AUTRE pays ne retire pas le candidat", async () => {
    await seed("k-a", "alpha", 100);
    await db.insert(schema.keywordTargets).values({
      keywordId: "k-a",
      siteId: SITE,
      country: "BE",
      status: "live",
    });
    const rows = await queries.getTargetCandidates(SITE, "FR");
    expect(rows.map((r) => r.id)).toEqual(["k-a"]);
  });

  test("sans pays : aucune exclusion, l'étape rendra son état no-country", async () => {
    await seed("k-a", "alpha", 100);
    await db.insert(schema.keywordTargets).values({
      keywordId: "k-a",
      siteId: SITE,
      country: "FR",
      status: "live",
    });
    const rows = await queries.getTargetCandidates(SITE, null);
    expect(rows.map((r) => r.id)).toEqual(["k-a"]);
  });
});