// src/lib/keywords/list-query.test.ts
// Integration test — gated on DB_TESTS=1, run against the *_test DB only.
// list-query.ts imports "server-only" (throws outside Next) — stub it.
import { beforeEach, describe, expect, mock, test } from "bun:test";

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

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

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

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

  type SeedOpts = {
    id: string;
    siteId?: string;
    term: string;
    status?: "idea" | "watch" | "active" | "archived";
    theme?: string | null;
    intent?: string[];
    lang?: string;
    country?: string;
    primarySource?: string;
    sources?: string[];
    stages?: string[];
    metrics?: Record<
      string,
      {
        volume?: number;
        kd?: number;
        cpc?: number;
        growthPct?: number | null;
        updatedAt?: string;
      }
    >;
    gsc?: {
      position: number;
      impressions: number;
      clicks?: number;
      ctr?: number;
      updatedAt?: string;
    } | null;
  };

  async function seed(o: SeedOpts) {
    await db.insert(schema.keywords).values({
      id: o.id,
      siteId: o.siteId ?? SITE_A,
      term: o.term,
      lang: o.lang ?? "fr",
      country: o.country ?? "FR",
      theme: (o.theme ?? null) as never,
      status: o.status ?? "idea",
      intent: (o.intent ?? []) as never,
      stages: (o.stages ?? []) as never,
      primarySource: (o.primarySource ?? "manual") as never,
      sources: (o.sources ?? [o.primarySource ?? "manual"]) as never,
      // biome-ignore lint/suspicious/noExplicitAny: test fixture jsonb shape
      metrics: (o.metrics ?? {}) as any,
      trend: [],
      gsc:
        o.gsc === undefined
          ? null
          : o.gsc === null
            ? null
            : ({
                position: o.gsc.position,
                impressions: o.gsc.impressions,
                clicks: o.gsc.clicks ?? 0,
                ctr: o.gsc.ctr ?? 0,
                updatedAt: o.gsc.updatedAt ?? "2025-12-01",
                // 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");
    lq = await import("./list-query");
    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"],
      },
    ]);
  });

  describe("filterWhere / searchKeywords — combined filters", () => {
    test("AND's every dimension — each distractor fails exactly one", async () => {
      // Target satisfies the whole combo below.
      await seed({
        id: "t-match",
        term: "diamond ring price",
        status: "active",
        theme: "lab-diamonds",
        intent: ["commercial", "transactional"],
        lang: "fr",
        primarySource: "ahrefs",
        sources: ["ahrefs"],
        stages: ["research"],
        metrics: { ahrefs: { volume: 300, kd: 20, cpc: 2, growthPct: 10 } },
        gsc: { position: 15, impressions: 1000 },
      });
      const base = {
        term: "diamond ring price",
        status: "active" as const,
        theme: "lab-diamonds",
        intent: ["commercial", "transactional"],
        lang: "fr",
        primarySource: "ahrefs",
        sources: ["ahrefs"],
        stages: ["research"],
        metrics: { ahrefs: { volume: 300, kd: 20, cpc: 2, growthPct: 10 } },
        gsc: { position: 15, impressions: 1000 },
      };
      await seed({ ...base, id: "d-q", term: "gold ring price" }); // fails q
      await seed({ ...base, id: "d-status", status: "idea" }); // fails status
      await seed({ ...base, id: "d-theme", theme: "metals" }); // fails theme
      await seed({ ...base, id: "d-intent", intent: ["navigational"] }); // fails intent overlap
      await seed({ ...base, id: "d-lang", lang: "en" }); // fails lang
      await seed({
        ...base,
        id: "d-source",
        primarySource: "mangools",
        sources: ["mangools"],
        metrics: { mangools: base.metrics.ahrefs },
      }); // fails source containment
      await seed({ ...base, id: "d-stage", stages: ["planning"] }); // fails stage containment
      await seed({
        ...base,
        id: "d-volume",
        metrics: { ahrefs: { ...base.metrics.ahrefs, volume: 5000 } },
      }); // fails volume range
      await seed({
        ...base,
        id: "d-gscband",
        gsc: { position: 5, impressions: 1000 },
      }); // fails gscBand (top10, not quickwin)

      const page = await lq.searchKeywords(SITE_A, {
        filter: {
          q: "diamond ring",
          status: ["active"],
          theme: ["lab-diamonds"],
          intent: ["commercial"],
          lang: "fr",
          source: "ahrefs",
          stage: "research",
          volume: { min: 100, max: 1000 },
          kd: { min: 0, max: 50 },
          cpc: { min: 0, max: 5 },
          growth: { min: -100, max: 100 },
          gscBand: "quickwin",
        },
        sort: { field: "term", desc: false },
        limit: 50,
      });
      expect(page.rows.map((r) => r.id)).toEqual(["t-match"]);
      expect(page.total).toBe(1);
      expect(page.nextCursor).toBeNull();
    });

    test("ILIKE q is escaped — literal `_`/`%` don't act as wildcards", async () => {
      await seed({ id: "k-accent", term: "café" });
      await seed({ id: "k-underscore", term: "caf_e" });
      const page = await lq.searchKeywords(SITE_A, {
        filter: { q: "caf_" },
        sort: { field: "term", desc: false },
        limit: 50,
      });
      // Unescaped, `_` would match any single char and "café" would match too.
      expect(page.rows.map((r) => r.id)).toEqual(["k-underscore"]);
    });
  });

  describe("keyset pagination — volume DESC with NULLs", () => {
    const nonNull: [string, number][] = [
      ["v-a", 500],
      ["v-b", 500],
      ["v-c", 300],
      ["v-d", 300],
      ["v-e", 300],
      ["v-f", 200],
      ["v-g", 100],
      ["v-h", 50],
      ["v-i", 10],
      ["v-j", 5],
      ["v-k", 1],
      ["v-l", 0],
    ];
    const nullIds = ["n-a", "n-b", "n-c", "n-d", "n-e", "n-f", "n-g", "n-h"];

    async function seedDataset() {
      for (const [id, volume] of nonNull) {
        await seed({
          id,
          term: id,
          primarySource: "mangools",
          sources: ["mangools"],
          metrics: { mangools: { volume, kd: 10, cpc: 1, growthPct: 0 } },
        });
      }
      for (const id of nullIds) {
        // primarySource "manual" with empty metrics -> volume_primary is NULL.
        await seed({
          id,
          term: id,
          primarySource: "manual",
          sources: ["manual"],
        });
      }
    }

    async function collectAllPages(desc: boolean): Promise<string[]> {
      const ids: string[] = [];
      let cursor: string | null | undefined;
      for (let guard = 0; guard < 20; guard++) {
        const page = await lq.searchKeywords(SITE_A, {
          filter: {},
          sort: { field: "volume", desc },
          limit: 5,
          cursor,
        });
        ids.push(...page.rows.map((r) => r.id));
        expect(page.total).toBe(20);
        if (page.nextCursor === null) return ids;
        cursor = page.nextCursor;
      }
      throw new Error("pagination did not terminate");
    }

    test("DESC: successive pages are the exact union, no dup, no gap", async () => {
      await seedDataset();
      const ids = await collectAllPages(true);
      expect(ids).toHaveLength(20);
      expect(new Set(ids).size).toBe(20); // no duplicate
      const expected = [
        "v-a",
        "v-b", // 500 tier, id ASC tiebreak
        "v-c",
        "v-d",
        "v-e", // 300 tier
        "v-f",
        "v-g",
        "v-h",
        "v-i",
        "v-j",
        "v-k",
        "v-l",
        ...nullIds, // NULLS LAST, id ASC
      ];
      expect(ids).toEqual(expected);
    });

    test("ASC: successive pages are the exact union, NULLs still last", async () => {
      await seedDataset();
      const ids = await collectAllPages(false);
      expect(ids).toHaveLength(20);
      expect(new Set(ids).size).toBe(20);
      const expected = [
        "v-l",
        "v-k",
        "v-j",
        "v-i",
        "v-h",
        "v-g",
        "v-f",
        "v-c",
        "v-d",
        "v-e", // 300 tier, id ASC tiebreak (NOT reversed by DESC-of-value)
        "v-a",
        "v-b", // 500 tier
        ...nullIds, // NULLS LAST regardless of ASC/DESC
      ];
      expect(ids).toEqual(expected);
    });

    test("invalid/undecodable cursor resets to page 1 (no throw)", async () => {
      await seedDataset();
      const params = {
        filter: {},
        sort: { field: "volume" as const, desc: true },
        limit: 3,
      };
      const page1 = await lq.searchKeywords(SITE_A, {
        ...params,
        cursor: null,
      });
      const withBadCursor = await lq.searchKeywords(SITE_A, {
        ...params,
        cursor: "not-valid-base64url-json!!!",
      });
      expect(withBadCursor.rows.map((r) => r.id)).toEqual(
        page1.rows.map((r) => r.id),
      );
      const withGarbageJson = await lq.searchKeywords(SITE_A, {
        ...params,
        // valid base64url, but decodes to JSON that fails the zod cursor shape
        cursor: Buffer.from(JSON.stringify({ nope: true })).toString(
          "base64url",
        ),
      });
      expect(withGarbageJson.rows.map((r) => r.id)).toEqual(
        page1.rows.map((r) => r.id),
      );
    });
  });

  describe("tenancy", () => {
    test("site B rows never appear in site A results/facets/selection", async () => {
      await seed({
        id: "a1",
        siteId: SITE_A,
        term: "alpha",
        lang: "fr",
        primarySource: "mangools",
        sources: ["mangools"],
        metrics: { mangools: { volume: 10, kd: 1, cpc: 1, growthPct: 0 } },
      });
      await seed({
        id: "b1",
        siteId: SITE_B,
        term: "beta",
        lang: "es",
        primarySource: "semrush",
        sources: ["semrush"],
        metrics: { semrush: { volume: 999, kd: 1, cpc: 1, growthPct: 0 } },
      });

      const page = await lq.searchKeywords(SITE_A, {
        filter: {},
        sort: { field: "term", desc: false },
        limit: 50,
      });
      expect(page.rows.map((r) => r.id)).toEqual(["a1"]);
      expect(page.total).toBe(1);

      const facets = await lq.getKeywordFacets(SITE_A);
      expect(facets.languages).toEqual(["fr"]);
      expect(facets.total).toBe(1);

      const { eq } = await import("drizzle-orm");
      const rows = await db
        .select({ id: schema.keywords.id })
        .from(schema.keywords)
        .where(lq.selectionWhere(SITE_A, { mode: "ids", ids: ["a1", "b1"] }));
      expect(rows.map((r) => r.id)).toEqual(["a1"]); // b1 dropped (wrong site)
      void eq;
    });
  });

  describe("getKeywordFacets", () => {
    test("languages, sources, cpc range, total — optionally scoped to a stage", async () => {
      await seed({
        id: "f1",
        term: "one",
        lang: "fr",
        primarySource: "mangools",
        sources: ["mangools", "gsc"],
        stages: ["research"],
        metrics: { mangools: { volume: 10, kd: 1, cpc: 2, growthPct: 0 } },
      });
      await seed({
        id: "f2",
        term: "two",
        lang: "en",
        primarySource: "ahrefs",
        sources: ["ahrefs"],
        stages: [],
        metrics: { ahrefs: { volume: 20, kd: 1, cpc: 8, growthPct: 0 } },
      });
      await seed({
        id: "f3",
        term: "three",
        lang: "fr",
        primarySource: "manual",
        sources: ["manual"],
        stages: [],
      }); // no cpc (manual, empty metrics)

      const all = await lq.getKeywordFacets(SITE_A);
      expect(all.languages).toEqual(["en", "fr"]);
      const expectedSources: string[] = ["ahrefs", "gsc", "mangools", "manual"];
      expect([...all.sources].sort() as string[]).toEqual(
        expectedSources.sort(),
      );
      expect(all.cpcRange).toEqual([2, 8]);
      expect(all.total).toBe(3);

      const research = await lq.getKeywordFacets(SITE_A, "research");
      expect(research.total).toBe(1);
      expect(research.languages).toEqual(["fr"]);
    });

    test("quickwinCount — only site-scoped rows in the 11–20 band", async () => {
      // top10 (pos 5) → not a quick-win.
      await seed({
        id: "qw-top10",
        term: "qw top10",
        gsc: { position: 5, impressions: 100 },
      });
      // quick-win (pos 15) → counts.
      await seed({
        id: "qw-in",
        term: "qw in",
        gsc: { position: 15, impressions: 100 },
      });
      // no gsc → not a quick-win.
      await seed({ id: "qw-nogsc", term: "qw nogsc", gsc: null });
      // quick-win but on SITE_B → must not leak into SITE_A's count.
      await seed({
        id: "qw-b",
        siteId: SITE_B,
        term: "qw b",
        gsc: { position: 15, impressions: 100 },
      });

      const facets = await lq.getKeywordFacets(SITE_A);
      expect(facets.quickwinCount).toBe(1);
      // Sanity: SITE_B sees its own quick-win, not SITE_A's.
      const facetsB = await lq.getKeywordFacets(SITE_B);
      expect(facetsB.quickwinCount).toBe(1);
    });
  });

  describe("filter.shortlisted", () => {
    async function shortlist(siteId: string, keywordId: string) {
      await db.insert(schema.keywordShortlist).values({ siteId, keywordId });
    }

    test("only site-scoped shortlisted rows survive; total matches", async () => {
      // SITE_A: one shortlisted, one not.
      await seed({ id: "sl-in", term: "sl in", status: "active" });
      await seed({ id: "sl-out", term: "sl out", status: "active" });
      await shortlist(SITE_A, "sl-in");
      // SITE_B: a shortlisted row — must never leak into SITE_A results.
      await seed({ id: "sl-b", siteId: SITE_B, term: "sl b" });
      await shortlist(SITE_B, "sl-b");

      const page = await lq.searchKeywords(SITE_A, {
        filter: { shortlisted: true },
        sort: { field: "term", desc: false },
        limit: 50,
      });
      expect(page.rows.map((r) => r.id)).toEqual(["sl-in"]);
      expect(page.total).toBe(1);
    });

    test("absent/false leaves the result unchanged", async () => {
      await seed({ id: "u1", term: "u1" });
      await seed({ id: "u2", term: "u2" });
      await shortlist(SITE_A, "u1");
      for (const filter of [{}, { shortlisted: false }]) {
        const page = await lq.searchKeywords(SITE_A, {
          filter,
          sort: { field: "term", desc: false },
          limit: 50,
        });
        expect(page.rows.map((r) => r.id)).toEqual(["u1", "u2"]);
        expect(page.total).toBe(2);
      }
    });

    test("AND's with another filter (status)", async () => {
      await seed({ id: "c-active", term: "c active", status: "active" });
      await seed({ id: "c-idea", term: "c idea", status: "idea" });
      await seed({ id: "c-noSL", term: "c no sl", status: "active" });
      await shortlist(SITE_A, "c-active");
      await shortlist(SITE_A, "c-idea");

      const page = await lq.searchKeywords(SITE_A, {
        filter: { shortlisted: true, status: ["active"] },
        sort: { field: "term", desc: false },
        limit: 50,
      });
      // c-active: shortlisted AND active. c-idea fails status; c-noSL not shortlisted.
      expect(page.rows.map((r) => r.id)).toEqual(["c-active"]);
      expect(page.total).toBe(1);
    });
  });

  describe("selectionWhere — filter mode + except", () => {
    test("matches the filter WHERE minus the excepted ids", async () => {
      await seed({ id: "s1", term: "s1", status: "active" });
      await seed({ id: "s2", term: "s2", status: "active" });
      await seed({ id: "s3", term: "s3", status: "active" });
      await seed({ id: "s4", term: "s4", status: "idea" });

      const rows = await db
        .select({ id: schema.keywords.id })
        .from(schema.keywords)
        .where(
          lq.selectionWhere(SITE_A, {
            mode: "filter",
            filter: { status: ["active"] },
            except: ["s2"],
          }),
        );
      expect(rows.map((r) => r.id).sort()).toEqual(["s1", "s3"]);
    });

    test("filter mode without except matches the whole filter WHERE", async () => {
      await seed({ id: "s1", term: "s1", status: "active" });
      await seed({ id: "s2", term: "s2", status: "idea" });
      const rows = await db
        .select({ id: schema.keywords.id })
        .from(schema.keywords)
        .where(
          lq.selectionWhere(SITE_A, {
            mode: "filter",
            filter: { status: ["active"] },
          }),
        );
      expect(rows.map((r) => r.id)).toEqual(["s1"]);
    });
  });
});