// Integration test — gated on DB_TESTS=1, run against the *_test DB only.
// Validates the STORED generated columns on `keywords` (0058): volume_primary,
// kd_primary, cpc_primary, growth_primary — extracted from `metrics` keyed by
// `primary_source`, recomputed on UPDATE, NULL when the primary source carries
// no metric (GSC-only / manual).
import { afterEach, beforeEach, describe, expect, mock, test } from "bun:test";

// server-only throws outside Next.js; stub it for the test runner.
mock.module("server-only", () => ({}));

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

describe.skipIf(!RUN)("keywords generated metric columns (postgres)", () => {
  let db: typeof import("@/lib/db/client").db;
  let schema: typeof import("@/lib/db/domain-schema");
  let eq: typeof import("drizzle-orm").eq;

  const ORG = "demo-org"; // guaranteed by db:seed / test DB setup
  const SITE = "kw-generated-site";
  const KID = `${SITE}-kw1`;

  beforeEach(async () => {
    ({ db } = await import("@/lib/db/client"));
    schema = await import("@/lib/db/domain-schema");
    ({ eq } = await import("drizzle-orm"));
    const { assertTestDb } = await import("@/lib/db/assert-test-db");
    await assertTestDb();
    await db.delete(schema.keywords).where(eq(schema.keywords.siteId, SITE));
    await db.delete(schema.sites).where(eq(schema.sites.id, SITE));
    await db.insert(schema.sites).values({
      id: SITE,
      orgId: ORG,
      ownerUserId: "seed-owner",
      name: "Keywords Generated Columns Test Site",
      url: `https://${SITE}.com`,
      primaryLanguage: "fr",
      languages: [],
      targetCountries: ["FR"],
    });
  });

  afterEach(async () => {
    await db.delete(schema.keywords).where(eq(schema.keywords.siteId, SITE));
    await db.delete(schema.sites).where(eq(schema.sites.id, SITE));
  });

  test("extracts volume/kd/cpc/growth from the primary source's metrics", async () => {
    await db.insert(schema.keywords).values({
      id: KID,
      siteId: SITE,
      term: "t",
      status: "idea",
      primarySource: "semrush",
      intent: [],
      sources: ["semrush"],
      stages: [],
      metrics: {
        semrush: {
          volume: 1300,
          kd: 42,
          cpc: 1.5,
          growthPct: 10,
          updatedAt: "2026-07-01",
        },
      },
      trend: [],
      trendBySource: {},
      briefs: {},
      gscByCountry: {},
      serp: {},
      enrichmentExports: {},
    });

    const [row] = await db
      .select()
      .from(schema.keywords)
      .where(eq(schema.keywords.id, KID));

    expect(row?.volumePrimary).toBe(1300);
    expect(row?.kdPrimary).toBe(42);
    expect(row?.cpcPrimary).toBe(1.5);
    expect(row?.growthPrimary).toBe(10);
  });

  test("recomputes when metrics/primary_source are updated", async () => {
    await db.insert(schema.keywords).values({
      id: KID,
      siteId: SITE,
      term: "t",
      status: "idea",
      primarySource: "semrush",
      intent: [],
      sources: ["semrush", "mangools"],
      stages: [],
      metrics: {
        semrush: {
          volume: 1300,
          kd: 42,
          cpc: 1.5,
          growthPct: 10,
          updatedAt: "2026-07-01",
        },
      },
      trend: [],
      trendBySource: {},
      briefs: {},
      gscByCountry: {},
      serp: {},
      enrichmentExports: {},
    });

    await db
      .update(schema.keywords)
      .set({
        primarySource: "mangools",
        metrics: {
          semrush: {
            volume: 1300,
            kd: 42,
            cpc: 1.5,
            growthPct: 10,
            updatedAt: "2026-07-01",
          },
          mangools: {
            volume: 900,
            kd: 30,
            cpc: 0.8,
            growthPct: -5,
            updatedAt: "2026-07-02",
          },
        },
      })
      .where(eq(schema.keywords.id, KID));

    const [row] = await db
      .select()
      .from(schema.keywords)
      .where(eq(schema.keywords.id, KID));

    expect(row?.volumePrimary).toBe(900);
    expect(row?.kdPrimary).toBe(30);
    expect(row?.cpcPrimary).toBe(0.8);
    expect(row?.growthPrimary).toBe(-5);
  });

  test("NULL for all 4 columns when metrics is empty", async () => {
    await db.insert(schema.keywords).values({
      id: KID,
      siteId: SITE,
      term: "t",
      status: "idea",
      primarySource: "gsc",
      intent: [],
      sources: ["gsc"],
      stages: [],
      metrics: {},
      trend: [],
      trendBySource: {},
      briefs: {},
      gscByCountry: {},
      serp: {},
      enrichmentExports: {},
    });

    const [row] = await db
      .select()
      .from(schema.keywords)
      .where(eq(schema.keywords.id, KID));

    expect(row?.volumePrimary).toBeNull();
    expect(row?.kdPrimary).toBeNull();
    expect(row?.cpcPrimary).toBeNull();
    expect(row?.growthPrimary).toBeNull();
  });
});