// Integration test — gated on DB_TESTS=1, run against the *_test DB only.
// Validates the flat (dé-partitionnée, 0057) `keywords` table: PK(id) alone,
// the upsert is idempotent on conflict `id`, id is globally unique (not just
// per-site), and deleting a keyword cascades through the simple FKs to
// keyword_targets + keyword_shortlist.
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 integrity (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-integrity-site";
  const SITE_B = "kw-integrity-site-b";
  const KID = `${SITE}-kw1`;

  async function seedSite(id: string) {
    await db.delete(schema.keywords).where(eq(schema.keywords.siteId, id));
    await db.delete(schema.sites).where(eq(schema.sites.id, id));
    await db.insert(schema.sites).values({
      id,
      orgId: ORG,
      ownerUserId: "seed-owner",
      name: "Keywords Integrity Test Site",
      url: `https://${id}.com`,
      primaryLanguage: "fr",
      languages: [],
      targetCountries: ["FR"],
    });
  }

  async function seedKeyword(id: string, siteId: string) {
    await db.insert(schema.keywords).values({
      id,
      siteId,
      term: "t",
      status: "idea",
      primarySource: "semrush",
      intent: [],
      sources: [],
      stages: [],
      metrics: {},
      trend: [],
      trendBySource: {},
      briefs: {},
      gscByCountry: {},
      serp: {},
      enrichmentExports: {},
    });
  }

  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();
    // Children first (FK cascade also handles it), then the parent sites.
    await seedSite(SITE);
    await seedSite(SITE_B);
  });

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

  test("upsert: same id is idempotent (conflict target = id)", async () => {
    await seedKeyword(KID, SITE);
    await db
      .insert(schema.keywords)
      .values({
        id: KID,
        siteId: SITE,
        term: "t2",
        status: "idea",
        primarySource: "semrush",
        intent: [],
        sources: [],
        stages: [],
        metrics: {},
        trend: [],
        trendBySource: {},
        briefs: {},
        gscByCountry: {},
        serp: {},
        enrichmentExports: {},
      })
      .onConflictDoUpdate({
        target: [schema.keywords.id],
        set: { term: "t2" },
      });
    const rows = await db
      .select()
      .from(schema.keywords)
      .where(eq(schema.keywords.id, KID));
    expect(rows).toHaveLength(1);
    expect(rows[0]?.term).toBe("t2");
  });

  test("id is globally unique: inserting the same id under another site conflicts", async () => {
    await seedKeyword(KID, SITE);
    let threw = false;
    try {
      await db.insert(schema.keywords).values({
        id: KID,
        siteId: SITE_B,
        term: "collision",
        status: "idea",
        primarySource: "semrush",
        intent: [],
        sources: [],
        stages: [],
        metrics: {},
        trend: [],
        trendBySource: {},
        briefs: {},
        gscByCountry: {},
        serp: {},
        enrichmentExports: {},
      });
    } catch {
      threw = true;
    }
    expect(threw).toBe(true);
  });

  test("deleting a keyword cascades to keyword_targets and shortlist", async () => {
    await seedKeyword(KID, SITE);
    await db.insert(schema.keywordTargets).values({
      keywordId: KID,
      siteId: SITE,
      country: "FR",
      status: "candidate",
    });
    await db
      .insert(schema.keywordShortlist)
      .values({ siteId: SITE, keywordId: KID });
    await db.delete(schema.keywords).where(eq(schema.keywords.id, KID));
    expect(
      await db
        .select()
        .from(schema.keywordTargets)
        .where(eq(schema.keywordTargets.keywordId, KID)),
    ).toHaveLength(0);
    expect(
      await db
        .select()
        .from(schema.keywordShortlist)
        .where(eq(schema.keywordShortlist.keywordId, KID)),
    ).toHaveLength(0);
  });
});