// src/lib/keywords/overview-query.test.ts
// Integration/parity test — gated on DB_TESTS=1, run against the *_test DB
// only. `queryKeywordOverview` (SQL, P7) must produce the exact same
// `KeywordOverview` shape as `buildKeywordOverview` (overview.ts, the pure-JS
// aggregation kept as the reference — see overview.test.ts for its own unit
// tests). Both aggregation paths run over the SAME seeded rows: the DB is the
// single source of truth for the dataset, so there is nothing to keep in
// sync by hand between "the JS input" and "the SQL input".
import { beforeEach, describe, expect, mock, test } from "bun:test";
import { eq } from "drizzle-orm";

mock.module("server-only", () => ({}));

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

type MetricsIn = {
  volume: number;
  kd: number;
};

type SeedRow = {
  id: string;
  term: string;
  theme: string | null;
  intent: string[];
  stages: string[];
  source: "mangools" | "ahrefs" | "semrush" | "seranking" | "manual";
  metrics?: MetricsIn;
  gsc?: { position: number; impressions: number; clicks: number } | null;
};

// 30 keywords engineered to exercise every bucket/edge-case the 8 SQL
// aggregates touch:
// - positionDistribution: top3 x1 (k11), top10 x2 (k12,k13), quickwin x10
//   (k04,k05,k14..k21 — > the 8-row `opportunities` LIMIT, so the SQL
//   ORDER BY score DESC, term ASC tie-break actually gets exercised), deep x2
//   (k06,k07), far x1 (k08), none x14 (no `gsc`).
// - kdDistribution: all 5 buckets hit, incl. kd=0 (k10 — a real 0, distinct
//   from the 3 rows with NO primary-source metrics at all: k06/k09/k22,
//   which must be EXCLUDED from kdDistribution/avgKd, same as `totalVolume`
//   coalescing them to 0 rather than dropping them).
// - byIntent: every SearchIntent used, several keywords multi-intent.
// - byTheme: 10 distinct theme groups (5 canonical + null "—" + 4 ad-hoc
//   per-site slugs) so the LIMIT 6 actually truncates; a genuine COUNT TIE
//   at 5 (lab-diamonds vs manufacturing) proves the SQL `min(term) ASC`
//   tie-break reproduces the JS Map's insertion-order tie-break exactly.
// - topByVolume: > 10 keywords with a positive volume (so k06/k09/k10/k22 —
//   volume 0 or NULL — never contend for the LIMIT 10 cutoff either way,
//   sidestepping the one case where "NULLS LAST" would diverge from `?? 0`);
//   several volume ties (k11/k13 @1500, k15/k16 @250, k18/k19 @5000) exercise
//   the `term ASC` tie-break.
// - backlinks byStatus: 4 distinct counts (no ties) — the old `queries.ts`
//   read had NO `ORDER BY`, so tie-break order was never a guaranteed
//   contract; avoiding a tie here keeps the test deterministic regardless.
const ROWS: SeedRow[] = [
  {
    id: "k01",
    term: "aa-diamond-ring-price",
    theme: "lab-diamonds",
    intent: ["informational"],
    stages: [],
    source: "mangools",
    metrics: { volume: 800, kd: 15 },
    gsc: null,
  },
  {
    id: "k02",
    term: "ab-diamond-ring-cost",
    theme: "lab-diamonds",
    intent: ["commercial", "transactional"],
    stages: ["research"],
    source: "ahrefs",
    metrics: { volume: 1200, kd: 25 },
    gsc: { position: 2, impressions: 500, clicks: 50 },
  },
  {
    id: "k03",
    term: "ac-diamond-earrings",
    theme: "lab-diamonds",
    intent: ["informational"],
    stages: ["research", "planning"],
    source: "semrush",
    metrics: { volume: 600, kd: 45 },
    gsc: { position: 8, impressions: 300, clicks: 10 },
  },
  {
    id: "k04",
    term: "ad-diamond-necklace",
    theme: "lab-diamonds",
    intent: ["local"],
    stages: [],
    source: "mangools",
    metrics: { volume: 2000, kd: 55 },
    gsc: { position: 11, impressions: 1000, clicks: 5 },
  },
  {
    id: "k05",
    term: "ae-diamond-bracelet",
    theme: "lab-diamonds",
    intent: ["navigational"],
    stages: ["planning"],
    source: "ahrefs",
    metrics: { volume: 300, kd: 65 },
    gsc: { position: 12, impressions: 400, clicks: 2 },
  },
  {
    id: "k06",
    term: "af-diamond-cufflinks",
    theme: "metals",
    intent: ["informational", "local"],
    stages: [],
    source: "manual", // no metrics entry -> volume_primary/kd_primary NULL
    gsc: { position: 30, impressions: 200, clicks: 1 },
  },
  {
    id: "k07",
    term: "ag-diamond-tiara",
    theme: "metals",
    intent: ["commercial"],
    stages: [],
    source: "seranking",
    metrics: { volume: 50, kd: 95 },
    gsc: { position: 45, impressions: 150, clicks: 1 },
  },
  {
    id: "k08",
    term: "ah-diamond-pendant",
    theme: null,
    intent: ["transactional"],
    stages: ["research"],
    source: "mangools",
    metrics: { volume: 10, kd: 10 },
    gsc: { position: 60, impressions: 80, clicks: 1 },
  },
  {
    id: "k09",
    term: "ai-diamond-cluster",
    theme: null,
    intent: [],
    stages: ["planning"],
    source: "manual",
    gsc: null,
  },
  {
    id: "k10",
    term: "aj-diamond-solitaire",
    theme: "manufacturing",
    intent: ["informational"],
    stages: [],
    source: "ahrefs",
    metrics: { volume: 0, kd: 0 }, // real zero, NOT missing — distinct from k06/k09/k22
    gsc: null,
  },
  {
    id: "k11",
    term: "ba-gold-bar-price",
    theme: "manufacturing",
    intent: ["commercial", "local"],
    stages: ["research"],
    source: "mangools",
    metrics: { volume: 1500, kd: 33 },
    gsc: { position: 3, impressions: 700, clicks: 60 }, // top3
  },
  {
    id: "k12",
    term: "bb-gold-coin-value",
    theme: "ethics-industry",
    intent: ["informational"],
    stages: [],
    source: "semrush",
    metrics: { volume: 900, kd: 41 },
    gsc: { position: 5, impressions: 250, clicks: 20 }, // top10
  },
  {
    id: "k13",
    term: "bc-gold-titrage",
    theme: "ethics-industry",
    intent: ["informational"],
    stages: ["research"],
    source: "ahrefs",
    metrics: { volume: 1500, kd: 18 }, // volume tie w/ k11
    gsc: { position: 9, impressions: 220, clicks: 8 }, // top10
  },
  {
    id: "k14",
    term: "bd-gold-refining",
    theme: "ethics-industry",
    intent: ["transactional"],
    stages: [],
    source: "mangools",
    metrics: { volume: 400, kd: 72 },
    gsc: { position: 13, impressions: 600, clicks: 3 }, // quickwin
  },
  {
    id: "k15",
    term: "be-gold-melting",
    theme: "supplies",
    intent: ["local", "navigational"],
    stages: ["planning"],
    source: "ahrefs",
    metrics: { volume: 250, kd: 88 },
    gsc: { position: 14, impressions: 600, clicks: 3 }, // quickwin, impressions tie w/ k14
  },
  {
    id: "k16",
    term: "bf-gold-supplies-kit",
    theme: "supplies",
    intent: ["commercial"],
    stages: [],
    source: "semrush",
    metrics: { volume: 250, kd: 22 }, // volume tie w/ k15
    gsc: { position: 16, impressions: 500, clicks: 2 }, // quickwin
  },
  {
    id: "k17",
    term: "bg-supplies-catalog",
    theme: "supplies",
    intent: ["informational"],
    stages: [],
    source: "mangools",
    metrics: { volume: 180, kd: 60 },
    gsc: { position: 17, impressions: 500, clicks: 1 }, // quickwin, impressions tie w/ k16
  },
  {
    id: "k18",
    term: "bh-cad-design-tool",
    theme: "manufacturing",
    intent: ["navigational"],
    stages: ["research"],
    source: "ahrefs",
    metrics: { volume: 5000, kd: 5 },
    gsc: { position: 19, impressions: 900, clicks: 9 }, // quickwin
  },
  {
    id: "k19",
    term: "bi-cao-jewelry-software",
    theme: "manufacturing",
    intent: ["informational", "commercial"],
    stages: [],
    source: "semrush",
    metrics: { volume: 5000, kd: 38 }, // volume + impressions tie w/ k18
    gsc: { position: 20, impressions: 900, clicks: 2 }, // quickwin
  },
  {
    id: "k20",
    term: "bj-jewelry-cad-course",
    theme: "manufacturing",
    intent: ["transactional"],
    stages: ["planning"],
    source: "mangools",
    metrics: { volume: 120, kd: 12 },
    gsc: { position: 11, impressions: 50, clicks: 0 }, // quickwin, low score
  },
  {
    id: "k21",
    term: "bk-jewelry-mold-price",
    theme: "ethics-industry",
    intent: ["local"],
    stages: [],
    source: "ahrefs",
    metrics: { volume: 75, kd: 48 },
    gsc: { position: 15, impressions: 100, clicks: 1 }, // quickwin (10th)
  },
  {
    id: "k22",
    term: "ca-ethics-report",
    theme: "ethics-industry",
    intent: ["informational"],
    stages: [],
    source: "manual", // no metrics entry
    gsc: null,
  },
  {
    id: "k23",
    term: "cb-fair-trade-gold",
    theme: "ethics-industry",
    intent: ["informational", "commercial"],
    stages: ["research"],
    source: "mangools",
    metrics: { volume: 40, kd: 8 },
    gsc: null,
  },
  {
    id: "k24",
    term: "cc-conflict-free-diamonds",
    theme: "theme-extra-1",
    intent: ["transactional"],
    stages: [],
    source: "ahrefs",
    metrics: { volume: 60, kd: 99 },
    gsc: null,
  },
  {
    id: "k25",
    term: "cd-blood-diamond-facts",
    theme: "theme-extra-1", // theme tie w/ k24 (count 2)
    intent: ["informational"],
    stages: ["planning"],
    source: "semrush",
    metrics: { volume: 60, kd: 99 }, // volume tie w/ k24
    gsc: null,
  },
  {
    id: "k26",
    term: "ce-titanium-supplies",
    theme: "theme-extra-2",
    intent: ["local"],
    stages: [],
    source: "mangools",
    metrics: { volume: 30, kd: 52 },
    gsc: null,
  },
  {
    id: "k27",
    term: "cf-platinum-metals",
    theme: "theme-extra-2", // theme tie w/ k26 (count 2)
    intent: ["navigational"],
    stages: [],
    source: "ahrefs",
    metrics: { volume: 30, kd: 53 }, // volume tie w/ k26
    gsc: null,
  },
  {
    id: "k28",
    term: "cg-silver-titrage",
    theme: "theme-extra-3",
    intent: ["commercial"],
    stages: [],
    source: "semrush",
    metrics: { volume: 20, kd: 20 },
    gsc: null,
  },
  {
    id: "k29",
    term: "ch-alloy-testing",
    theme: "theme-extra-3", // theme tie w/ k28 (count 2)
    intent: ["informational"],
    stages: [],
    source: "mangools",
    metrics: { volume: 15, kd: 19 },
    gsc: null,
  },
  {
    id: "k30",
    term: "ci-casting-wax",
    theme: "theme-extra-4",
    intent: ["transactional", "local"],
    stages: [],
    source: "ahrefs",
    metrics: { volume: 5, kd: 2 },
    gsc: null,
  },
];

describe.skipIf(!RUN_DB)(
  "queryKeywordOverview — parity with buildKeywordOverview (postgres)",
  () => {
    let db: typeof import("@/lib/db/client").db;
    let schema: typeof import("@/lib/db/domain-schema");
    let queries: typeof import("./queries");
    let overview: typeof import("./overview");
    let overviewQuery: typeof import("./overview-query");

    const SITE = "ov-parity-site";
    const OTHER_SITE = "ov-parity-other-site";
    const ORG = "demo-org";

    async function insertSource(id: string) {
      await db.insert(schema.linkSources).values({
        id,
        orgId: ORG,
        platform: id,
        url: `https://${id.toLowerCase()}.com`,
        domainNorm: `${id.toLowerCase()}.com`,
        kind: "blog",
        da: 40,
        effort: "low",
        linkType: "dofollow",
        language: "fr",
        // biome-ignore lint/suspicious/noExplicitAny: test fixture jsonb shape
      } as any);
    }

    beforeEach(async () => {
      ({ db } = await import("@/lib/db/client"));
      schema = await import("@/lib/db/domain-schema");
      queries = await import("./queries");
      overview = await import("./overview");
      overviewQuery = await import("./overview-query");
      const { assertTestDb } = await import("@/lib/db/assert-test-db");
      const { inArray } = await import("drizzle-orm");
      await assertTestDb();
      await db.delete(schema.backlinkOutreach);
      await db.delete(schema.backlinks);
      await db.delete(schema.linkSources);
      await db.delete(schema.keywords);
      await db
        .delete(schema.sites)
        .where(inArray(schema.sites.id, [SITE, OTHER_SITE]));
      await db.insert(schema.sites).values(
        [SITE, OTHER_SITE].map((id) => ({
          id,
          orgId: ORG,
          ownerUserId: "seed-owner",
          name: id,
          url: `https://${id}.com`,
          primaryLanguage: "fr",
          languages: [],
          targetCountries: ["FR"],
        })),
      );

      await db.insert(schema.keywords).values(
        ROWS.map((r) => ({
          id: r.id,
          siteId: SITE,
          term: r.term,
          lang: "fr",
          country: "FR",
          theme: r.theme as never,
          status: "idea" as const,
          intent: r.intent as never,
          stages: r.stages as never,
          primarySource: r.source as never,
          sources: [r.source] as never,
          metrics: (r.metrics
            ? {
                [r.source]: {
                  volume: r.metrics.volume,
                  kd: r.metrics.kd,
                  cpc: 1,
                  growthPct: null,
                  updatedAt: "2026-06-01",
                },
              }
            : {}) as never,
          trend: [],
          gsc: (r.gsc
            ? {
                position: r.gsc.position,
                impressions: r.gsc.impressions,
                clicks: r.gsc.clicks,
                ctr: r.gsc.clicks / Math.max(1, r.gsc.impressions),
                updatedAt: "2026-06-01",
              }
            : null) as never,
        })),
      );

      // Distinct per-status counts (see ROWS comment): no tie, so byStatus
      // order is unambiguous regardless of the underlying row-scan order.
      await insertSource("BL1");
      await insertSource("BL2");
      await insertSource("BL3");
      await insertSource("BL4");
      await insertSource("BL5");
      await insertSource("BL6");
      await insertSource("BL7");
      await insertSource("BL8");
      await insertSource("BL9");
      await insertSource("BL10");
      await insertSource("BL11");
      await insertSource("BL12");
      await insertSource("BL13");
      await insertSource("BL14");
      await insertSource("BL15");
      const statuses: { id: string; status: string }[] = [
        ...["BL1", "BL2", "BL3", "BL4", "BL5"].map((id) => ({
          id,
          status: "live",
        })), // 5
        ...["BL6", "BL7", "BL8", "BL9"].map((id) => ({
          id,
          status: "prospect",
        })), // 4
        ...["BL10", "BL11", "BL12"].map((id) => ({
          id,
          status: "in_progress",
        })), // 3
        ...["BL13", "BL14"].map((id) => ({ id, status: "lost" })), // 2
      ];
      await db.insert(schema.backlinks).values(
        statuses.map((s, i) => ({
          id: `bl-${i}`,
          linkSourceId: s.id,
          siteId: SITE,
          status: s.status as never,
        })),
      );
    });

    test("SQL aggregate matches the JS reference over 30 keywords + 14 backlinks", async () => {
      const kws = await queries.getKeywordsForSite(SITE);
      expect(kws.length).toBe(ROWS.length);

      const bl = await db
        .select({ status: schema.backlinks.status })
        .from(schema.backlinks)
        .where(eq(schema.backlinks.siteId, SITE));
      expect(bl.length).toBe(14);

      const jsResult = overview.buildKeywordOverview(kws, bl);
      const sqlResult = await overviewQuery.queryKeywordOverview(SITE);

      // Sanity — guards against a vacuous pass (e.g. a WHERE bug scoping the
      // wrong site would make both sides trivially `{totalKeywords: 0, ...}`).
      expect(sqlResult.totalKeywords).toBe(30);
      expect(sqlResult.positioned).toBeGreaterThan(0);
      expect(
        sqlResult.positionDistribution.find((b) => b.key === "quickwin")?.count,
      ).toBe(10);
      expect(sqlResult.opportunities.length).toBe(8);
      expect(sqlResult.topByVolume.length).toBe(10);
      expect(sqlResult.byTheme.length).toBe(6);
      expect(sqlResult.backlinks?.total).toBe(14);

      expect(sqlResult).toEqual(jsResult);
    });

    test("empty site — no keywords, no backlinks", async () => {
      const kws = await queries.getKeywordsForSite(OTHER_SITE);
      expect(kws.length).toBe(0);
      const jsResult = overview.buildKeywordOverview(kws, null);
      const sqlResult = await overviewQuery.queryKeywordOverview(OTHER_SITE);
      expect(sqlResult).toEqual(jsResult);
      expect(sqlResult.totalKeywords).toBe(0);
      expect(sqlResult.avgPosition).toBeNull();
      expect(sqlResult.avgKd).toBeNull();
      expect(sqlResult.backlinks).toBeNull();
    });
  },
);