/**
 * AI Software Factory — Integration: Databases (Phases 5-6)
 *
 * Record CRUD, the nested filter tree (AND/OR/NOT + property ops), sort,
 * group, view defaults, and a 10k-record query performance floor (soft —
 * the hard budget is enforced by the k6 suite in tests/performance).
 */
import { afterAll, beforeAll, describe, expect, it } from "vitest";
import { createTestApp, type TestAppHandle } from "./helpers/api";
import { authHeaders, createWorkspace, createUser, seedRecords } from "./helpers/seed";

let app: TestAppHandle;

beforeAll(async () => {
  app = await createTestApp();
}, 180_000);
afterAll(async () => {
  await app?.close();
});

const json = async (res: Response): Promise<Record<string, unknown>> =>
  (await res.json().catch(() => ({}))) as Record<string, unknown>;

async function setupDb(
  extraProperties: { name: string; type: string; config?: unknown }[] = [],
): Promise<{ headers: Record<string, string>; workspaceId: string; databaseId: string }> {
  const user = await createUser(app.request, { name: "DB Owner" });
  const { workspaceId } = await createWorkspace(
    app.request,
    authHeaders(user.accessToken!),
    "DB WS",
  );
  // Databases are PAGES — there is no `POST /workspaces/:id/databases`
  // endpoint. The canonical setup (every other integration suite) is
  // `POST /pages { type: "database" }` → `{ id, databaseId }`, then add
  // custom properties via `POST /databases/:id/properties`.
  const res = await app.request("POST", `/api/v1/workspaces/${workspaceId}/pages`, {
    headers: authHeaders(user.accessToken!),
    body: { title: "Test DB", type: "database" },
  });
  expect([200, 201]).toContain(res.status);
  const { databaseId } = (await json(res)) as { databaseId: string };
  for (const p of extraProperties) {
    const propRes = await app.request("POST", `/api/v1/databases/${databaseId}/properties`, {
      headers: authHeaders(user.accessToken!),
      body: { name: p.name, type: p.type, config: p.config ?? {} },
    });
    if (propRes.status === 409) {
      // A fresh database seeds `Title` + `Status` (To Do/In Progress/Done);
      // a duplicate name 409s. Reuse the seeded property — set its config to
      // the options this test's records/filters rely on (Status → todo/done).
      const prop = await app.prisma.databaseProperty.findUniqueOrThrow({
        where: { databaseId_name: { databaseId, name: p.name } },
      });
      const patchRes = await app.request(
        "PATCH",
        `/api/v1/databases/${databaseId}/properties/${prop.id}`,
        { headers: authHeaders(user.accessToken!), body: { config: p.config ?? {} } },
      );
      expect([200]).toContain(patchRes.status);
    } else {
      expect([200, 201]).toContain(propRes.status);
    }
  }
  return {
    headers: authHeaders(user.accessToken!),
    workspaceId,
    databaseId,
  };
}

/**
 * POST /databases/:id/records — `properties` is propertyId-keyed and the
 * title goes through the separate `title` field (the API rejects name-keyed
 * writes: "Unknown property"). Call sites stay readable by passing
 * name-keyed values; this helper resolves name → propertyId (title name →
 * the `title` field).
 */
async function addRecord(
  headers: Record<string, string>,
  databaseId: string,
  byName: Record<string, unknown>,
): Promise<string> {
  const props = await app.prisma.databaseProperty.findMany({
    where: { databaseId, deletedAt: null },
    select: { id: true, name: true, type: true, isTitle: true },
  });
  const byId = new Map(props.map((p) => [p.name, p]));
  const properties: Record<string, unknown> = {};
  let title: string | undefined;
  for (const [name, value] of Object.entries(byName)) {
    const prop = byId.get(name);
    if (!prop) throw new Error(`addRecord: unknown property "${name}"`);
    if (prop.isTitle || prop.type === "TITLE") title = String(value);
    else properties[prop.id] = value;
  }
  const res = await app.request("POST", `/api/v1/databases/${databaseId}/records`, {
    headers,
    body: { ...(title !== undefined ? { title } : {}), properties },
  });
  expect([200, 201]).toContain(res.status);
  return ((await json(res)).id as string);
}

/**
 * The query DSL endpoint (docs §6): `POST /databases/:id/query` →
 * `{ data: { records, totalCount, nextCursor } }` (records branch) or
 * `{ data: { groups, totalCount, nextCursor } }` (group branch). `filter`
 * is a native tree — branches `{ op: "and"|"or", conditions: [] }` /
 * `{ op: "not", conditions: [one] }`, leaves `{ propertyId, op, value }`
 * with ops `eq/neq/contains/gt/gte/lt/lte/...` (NOT the engine's
 * `=`/`>`/`<` or the web's `children` key — those are rejected).
 * Cursor-paginated (no offset); the read is a bounded fetch (take ≤ 5000).
 */
async function query(
  headers: Record<string, string>,
  databaseId: string,
  body: {
    filter?: unknown;
    sort?: { propertyId: string; direction?: "asc" | "desc" }[];
    group?: { propertyId: string; direction?: "asc" | "desc" }[];
    pageSize?: number;
    cursor?: string | null;
    includeArchived?: boolean;
  } = {},
): Promise<{
  records: { id: string; title?: string; properties: Record<string, unknown> }[];
  groups: { key: { propertyId: string; value: unknown; label: string }; count: number }[];
  total: number;
  nextCursor: string | null;
}> {
  const res = await app.request("POST", `/api/v1/databases/${databaseId}/query`, {
    headers,
    body,
  });
  expect(res.status).toBe(200);
  const data = (await json(res)).data as {
    records?: { id: string; title?: string; properties: Record<string, unknown> }[];
    groups?: { key: { propertyId: string; value: unknown; label: string }; count: number }[];
    totalCount?: number;
    nextCursor?: string | null;
  };
  return {
    records: data.records ?? [],
    groups: data.groups ?? [],
    total: data.totalCount ?? 0,
    nextCursor: data.nextCursor ?? null,
  };
}

describe("Databases integration", () => {
  it("create: title property always present, default TABLE view exists", async () => {
    const { databaseId } = await setupDb();
    const db = await app.prisma.database.findUniqueOrThrow({
      where: { id: databaseId },
      include: { properties: true, views: true },
    });
    const titleProp = db.properties.find((p) => p.type === "TITLE");
    expect(titleProp, "a TITLE property must always exist").toBeTruthy();
    expect(db.views.some((v) => v.type === "TABLE")).toBe(true);
  });

  it("records CRUD: create / read / update / delete", async () => {
    const { headers, databaseId } = await setupDb([
      { name: "Status", type: "SELECT", config: { options: [{ name: "todo" }, { name: "done" }] } },
    ]);
    const propId = (await app.prisma.databaseProperty.findUniqueOrThrow({
      where: { databaseId_name: { databaseId, name: "Status" } },
    })).id;

    const id = await addRecord(headers, databaseId, { Title: "Task 1", Status: "todo" });
    const rec = await app.prisma.databaseRecord.findUniqueOrThrow({
      where: { id },
      include: { properties: true },
    });
    const statusVal = rec.properties.find((p) => p.propertyId === propId)?.value;
    expect(statusVal).toBeTruthy();

    // Update (propertyId-keyed; the value is canonicalized name → option id
    // the same way create is).
    const up = await app.request("PATCH", `/api/v1/records/${id}`, {
      headers,
      body: { properties: { [propId]: "done" } },
    });
    expect([200]).toContain(up.status);
    const after = await app.prisma.databaseRecord.findUniqueOrThrow({
      where: { id },
      include: { properties: true },
    });
    const doneVal = after.properties.find((p) => p.propertyId === propId)?.value;
    expect(JSON.stringify(doneVal)).not.toBe(JSON.stringify(statusVal));
    expect(after.version).toBeGreaterThan(rec.version);

    // Hard delete (the API implements DELETE /records/:id as a hard delete —
    // the spec's `POST .../records/:rid/archive` soft-archive endpoint does
    // not exist; recorded rule-29 conflict). The row, its 1:1 page, blocks,
    // properties and links are all removed.
    const del = await app.request("DELETE", `/api/v1/records/${id}`, {
      headers,
    });
    expect([200, 204]).toContain(del.status);
    expect(await app.prisma.databaseRecord.findUnique({ where: { id } })).toBeNull();
    const pageGone = await app.prisma.page.findUnique({
      where: { id: rec.pageId },
      select: { id: true },
    });
    expect(pageGone).toBeNull();

    // Gone from queries.
    const { total } = await query(headers, databaseId, {});
    expect(total).toBe(0);
  });

  it("record update: unknown property → 400", async () => {
    const { headers, databaseId } = await setupDb();
    const id = await addRecord(headers, databaseId, { Title: "X" });
    const res = await app.request("PATCH", `/api/v1/records/${id}`, {
      headers,
      body: { properties: { NoSuchProperty: 1 } },
    });
    expect(res.status).toBe(400);
  });

  it("filter tree: AND of (Title contains) OR (Status = done)", async () => {
    const { headers, databaseId } = await setupDb([
      { name: "Status", type: "SELECT", config: { options: [{ name: "todo" }, { name: "done" }] } },
    ]);
    const titleProp = await app.prisma.databaseProperty.findUniqueOrThrow({
      where: { databaseId_name: { databaseId, name: "Title" } },
    });
    const statusProp = await app.prisma.databaseProperty.findUniqueOrThrow({
      where: { databaseId_name: { databaseId, name: "Status" } },
    });
    await addRecord(headers, databaseId, { Title: "alpha apple", Status: "todo" });
    await addRecord(headers, databaseId, { Title: "beta banana", Status: "done" });
    await addRecord(headers, databaseId, { Title: "gamma cherry", Status: "todo" });

    // (Title contains "apple") OR (Status = done)
    const filter = {
      op: "or",
      conditions: [
        { propertyId: titleProp.id, op: "contains", value: "apple" },
        { propertyId: statusProp.id, op: "eq", value: "done" },
      ],
    };
    const { records, total } = await query(headers, databaseId, { filter });
    expect(total).toBe(2);
    const ids = new Set(records.map((r) => r.id));
    expect(ids.size).toBe(2);
  });

  it("filter tree: NOT + nested AND", async () => {
    const { headers, databaseId } = await setupDb([
      { name: "N", type: "NUMBER" },
    ]);
    const nProp = await app.prisma.databaseProperty.findUniqueOrThrow({
      where: { databaseId_name: { databaseId, name: "N" } },
    });
    await addRecord(headers, databaseId, { Title: "a", N: 1 });
    await addRecord(headers, databaseId, { Title: "b", N: 5 });
    await addRecord(headers, databaseId, { Title: "c", N: 9 });

    // NOT (N eq 5) → {1, 9}
    const filter = { op: "not", conditions: [{ propertyId: nProp.id, op: "eq", value: 5 }] };
    const { total } = await query(headers, databaseId, { filter });
    expect(total).toBe(2);

    // AND (N gt 0) (N lt 10) → all 3
    const andFilter = {
      op: "and",
      conditions: [
        { propertyId: nProp.id, op: "gt", value: 0 },
        { propertyId: nProp.id, op: "lt", value: 10 },
      ],
    };
    const { total: t2 } = await query(headers, databaseId, { filter: andFilter });
    expect(t2).toBe(3);
  });

  it("sort: title asc then desc; multi-key sort stable", async () => {
    const { headers, databaseId } = await setupDb([
      { name: "N", type: "NUMBER" },
    ]);
    const titleProp = await app.prisma.databaseProperty.findUniqueOrThrow({
      where: { databaseId_name: { databaseId, name: "Title" } },
    });
    const nProp = await app.prisma.databaseProperty.findUniqueOrThrow({
      where: { databaseId_name: { databaseId, name: "N" } },
    });
    for (const [t, n] of [
      ["zeta", 1],
      ["alpha", 2],
      ["mid", 1],
    ] as const) {
      await addRecord(headers, databaseId, { Title: t, N: n });
    }

    const asc = await query(headers, databaseId, {
      sort: [{ propertyId: titleProp.id, direction: "asc" }],
    });
    expect(asc.records.length).toBe(3);

    const desc = await query(headers, databaseId, {
      sort: [{ propertyId: titleProp.id, direction: "desc" }],
    });
    expect(desc.records.length).toBe(3);
    expect(
      JSON.stringify(desc.records.map((r) => r.id)),
    ).not.toBe(JSON.stringify(asc.records.map((r) => r.id)));

    // Multi-key: N asc, then Title asc.
    const multi = await query(headers, databaseId, {
      sort: [
        { propertyId: nProp.id, direction: "asc" },
        { propertyId: titleProp.id, direction: "asc" },
      ],
    });
    expect(multi.records.length).toBe(3);
  });

  it("group: group by SELECT property returns groups with counts", async () => {
    const { headers, databaseId } = await setupDb([
      { name: "Status", type: "SELECT", config: { options: [{ name: "todo" }, { name: "done" }] } },
    ]);
    const statusProp = await app.prisma.databaseProperty.findUniqueOrThrow({
      where: { databaseId_name: { databaseId, name: "Status" } },
    });
    await addRecord(headers, databaseId, { Title: "a", Status: "todo" });
    await addRecord(headers, databaseId, { Title: "b", Status: "todo" });
    await addRecord(headers, databaseId, { Title: "c", Status: "done" });

    const { groups, total } = await query(headers, databaseId, {
      group: [{ propertyId: statusProp.id, direction: "asc" }],
    });
    // Two groups (todo: 2, done: 1); totalCount is the row count, not the
    // group count.
    expect(total).toBe(3);
    const counts = new Map(groups.map((g) => [g.key.label, g.count]));
    expect(counts.get("todo")).toBe(2);
    expect(counts.get("done")).toBe(1);
  });

  it("pagination: cursor + pageSize slice the result set", async () => {
    const { headers, databaseId } = await setupDb();
    for (let i = 0; i < 10; i++) {
      await addRecord(headers, databaseId, { Title: `row-${i}` });
    }
    const page1 = await query(headers, databaseId, { pageSize: 4 });
    expect(page1.nextCursor).not.toBeNull();
    const page2 = await query(headers, databaseId, { pageSize: 4, cursor: page1.nextCursor });
    expect(page1.total).toBe(10);
    expect(page1.records.length).toBe(4);
    expect(page2.records.length).toBe(4);
    const ids1 = new Set(page1.records.map((r) => r.id));
    const ids2 = new Set(page2.records.map((r) => r.id));
    for (const id of ids2) expect(ids1.has(id)).toBe(false); // no overlap
  });

  it("10k records: seeded set queries back within the documented bound (soft perf floor)", async () => {
    const { headers, workspaceId, databaseId } = await setupDb();
    const seeded = await seedRecords(app.prisma, databaseId, workspaceId, 10_000);
    expect(seeded).toBe(10_000);

    // `POST /databases/:id/query` is a BOUNDED read (take ≤ 5000, per the
    // TASK-0135 comment): over a 10k set the fetch caps at 5000, so `totalCount`
    // reports the cap (5000), not the full 10_000. This is the documented
    // behavior the hard budget enforces in tests/performance — pin it.
    const started = Date.now();
    const res = await query(headers, databaseId, { pageSize: 50 });
    const ms = Date.now() - started;
    console.log(`[perf] 10k-record page query = ${ms}ms (soft floor 2000ms)`);
    expect(res.total).toBe(5_000);
    expect(res.records.length).toBe(50);
    expect(res.nextCursor).not.toBeNull();
    expect(ms, `10k page query took ${ms}ms`).toBeLessThan(2_000);
  }, 120_000);

  it("authorization: outsider cannot read or write another workspace's database", async () => {
    const owner = await createUser(app.request, { name: "DBOwner" });
    const { workspaceId } = await createWorkspace(
      app.request,
      authHeaders(owner.accessToken!),
      "DBX WS",
    );
    const dbRes = await app.request("POST", `/api/v1/workspaces/${workspaceId}/pages`, {
      headers: authHeaders(owner.accessToken!),
      body: { title: "X DB", type: "database" },
    });
    expect([200, 201]).toContain(dbRes.status);
    const { databaseId } = (await json(dbRes)) as { databaseId: string };

    const outsider = await createUser(app.request, { name: "DBX" });
    const read = await app.request("GET", `/api/v1/databases/${databaseId}/records`, {
      headers: authHeaders(outsider.accessToken!),
    });
    expect([403, 404]).toContain(read.status);
    // Valid-shaped body (title field) so the 403/404 is the authorization
    // decision, not a 400 validation error masking it.
    const write = await app.request("POST", `/api/v1/databases/${databaseId}/records`, {
      headers: authHeaders(outsider.accessToken!),
      body: { title: "intrusion" },
    });
    expect([403, 404]).toContain(write.status);
  });
});