/**
 * AI Software Factory — E2E: Database AI (TASK-0124).
 *
 * Drives the REAL stack (Next web + Nest API + the deterministic mock
 * provider's `mode:"database"` branch) against the acceptance criteria in
 * docs/tasks/phase-12-ai.md. The mock's database mode is deterministic:
 *   generate → a 4-property database (Name/State/Due/Done?) + 2 sample
 *     records ("First item"/"Second item");
 *   extract  → parses the pasted markdown table, maps columns by name,
 *     unmatched columns → `ignoredColumns`;
 *   formula  → `if(prop("Title") == "Done", 100, 0)` (number).
 * so the E2E can assert the exact created shape.
 *
 * Covered (the spec's E2E AC — the three happy paths + viewer 403):
 *   1. generate — database header AI → Generate → a NEW database appears
 *      (via the API `GET /workspaces/:id/databases`), its sample records are
 *      real pages, and the "Open" action lands on it (UI shows the record
 *      rows).
 *   2. extract — header AI → Extract from text → paste a 3-row markdown
 *      table (one unmatched column) → the LIVE mapping preview shows the
 *      matched + skipped columns → Apply → 3 records appear (per-type
 *      values) and the unmatched column is skipped.
 *   3. formula — header AI → Generate column with formula → a description →
 *      the preview shows the validated expression + the evaluated sample
 *      values → the FORMULA column is created.
 *   4. viewer 403 — a GUEST (workspace VIEW) member: the UI HIDES the
 *      generate/extract/formula menu items (only "Ask AI" remains), and the
 *      server returns 403 FORBIDDEN for all three endpoints (the real test).
 *
 * Selector convention (tests/README.md): this task's own data-testids —
 *   database-ask-ai, db-ai-menu-*, db-ai-generate(-prompt/-btn/-done),
 *   db-ai-extract(-text/-map/-ignored/-apply/-done), db-formula
 *   (-desc/-generate/-expression/-table/-done), db-ai-error-banner,
 *   db-ai-loading, record-row-*.
 */
import type { APIRequestContext, Page } from "@playwright/test";
import { expect, test } from "../fixtures";
import {
  addMemberViaSeam,
  createWorkspace,
  seamServiceToken,
  write
} from "../utils/api";

/** The extract markdown table (3 rows; "Notes" is unmatched). */
const EXTRACT_TABLE = [
  "| Name | Score | State | Notes |",
  "| --- | --- | --- | --- |",
  "| Alice | 42 | Open | first |",
  "| Bob | 7 | Closed | second |",
  "| Carol | 99 | Open | third |"
].join("\n");

/** Create a database through the NORMAL page route (the spec's
 *  `POST /workspaces/:id/databases` does not exist — a recorded conflict). */
async function createDbViaPage(
  api: APIRequestContext,
  workspaceId: string,
  title: string,
): Promise<{ databaseId: string; pageId: string }> {
  const body = await write<{ id: string; databaseId?: string }>(
    api,
    "POST",
    `/api/v1/workspaces/${workspaceId}/pages`,
    { title, type: "database" },
    "createDbViaPage",
  );
  if (!body.databaseId) throw new Error(`createDbViaPage: no databaseId in ${JSON.stringify(body)}`);
  return { databaseId: body.databaseId, pageId: body.id };
}

/** The workspace's databases via the API (the sidebar's source of truth). */
async function listDatabases(api: APIRequestContext, workspaceId: string): Promise<{ id: string; title: string }[]> {
  const res = await api.get(`/api/v1/workspaces/${workspaceId}/databases`);
  if (res.status() !== 200) throw new Error(`listDatabases ${res.status()}: ${await res.text()}`);
  const body = (await res.json()) as
    | { id: string; title: string }[]
    | { data: { id: string; title: string }[] };
  return Array.isArray(body) ? body : body.data;
}

/** Create a property via the API (returns the BARE property — the e2e
 *  `addProperty` helper wrongly expects a `{property}` wrapper). */
async function addProp(
  api: APIRequestContext,
  databaseId: string,
  property: { name: string; type: string; config?: unknown },
): Promise<{ id: string; name: string; type: string }> {
  return write(api, "POST", `/api/v1/databases/${databaseId}/properties`, property, "addProp");
}

async function openAiMenu(page: Page) {
  await page.getByTestId("database-ask-ai").click();
}

test.describe("Database AI (TASK-0124)", () => {
  test("generate — header AI → Generate → new database with page-backed sample records", async ({ authPage, api }) => {
    const ws = await createWorkspace(api, `Gen ${Date.now()}`);
    // A host database to mount the header (the AI dropdown lives on a DB page).
    const host = await createDbViaPage(api, ws.id, "Host DB");
    await authPage.goto(`/w/${ws.id}/databases/${host.databaseId}`);
    await expect(authPage.getByTestId("database-ask-ai")).toBeVisible({ timeout: 20_000 });

    const before = new Set((await listDatabases(api, ws.id)).map((d) => d.id));

    await openAiMenu(authPage);
    await authPage.getByTestId("db-ai-menu-generate").click();
    const dialog = authPage.getByTestId("db-ai-generate");
    await expect(dialog).toBeVisible();
    const prompt = "a bug tracker";
    await authPage.getByTestId("db-ai-generate-prompt").fill(prompt);
    await authPage.getByTestId("db-ai-generate-btn").click();

    // The success summary appears (the SSE stream finished + done frame).
    await expect(authPage.getByTestId("db-ai-generate-done")).toBeVisible({ timeout: 30_000 });

    // The new database exists via the API (identify by id-diff — the mock's
    // title derives from the rendered prompt, not the raw prompt).
    const after = await listDatabases(api, ws.id);
    const created = after.find((d) => !before.has(d.id));
    expect(created, "a new database should exist").toBeTruthy();
    expect(after.length).toBe(before.size + 1);

    // The 2 sample records are real pages (pageId set) with the mock titles.
    const records = ((await (
      await api.get(`/api/v1/databases/${created!.id}/records`)
    ).json()) as { data: { title?: string; pageId: string | null }[] }).data;
    expect(records.length).toBe(2);
    expect(records.map((r) => r.title).sort()).toEqual(["First item", "Second item"]);
    for (const r of records) expect(r.pageId).toBeTruthy();

    // The "Open" action lands on the new database (its record rows render).
    await authPage.getByTestId("db-ai-generate-done").getByRole("button", { name: "Open" }).click();
    await expect(authPage).toHaveURL(new RegExp(`/databases/${created!.id}`));
    await expect(authPage.locator('[data-testid^="record-row-"]').first()).toBeVisible({ timeout: 20_000 });
  });

  test("extract — paste markdown → live mapping preview → Apply → records appear, unmatched skipped", async ({ authPage, api }) => {
    const ws = await createWorkspace(api, `Extract ${Date.now()}`);
    const { databaseId } = await createDbViaPage(api, ws.id, "Extract DB");
    // The mock maps columns BY NAME to existing properties.
    const name = await addProp(api, databaseId, { name: "Name", type: "TEXT" });
    const score = await addProp(api, databaseId, { name: "Score", type: "NUMBER" });
    const state = await addProp(api, databaseId, {
      name: "State",
      type: "SELECT",
      config: { options: [{ name: "Open" }, { name: "Closed" }] }
    });

    await authPage.goto(`/w/${ws.id}/databases/${databaseId}`);
    await expect(authPage.getByTestId("database-ask-ai")).toBeVisible({ timeout: 20_000 });

    await openAiMenu(authPage);
    await authPage.getByTestId("db-ai-menu-extract").click();
    const dialog = authPage.getByTestId("db-ai-extract");
    await expect(dialog).toBeVisible();
    await authPage.getByTestId("db-ai-extract-text").fill(EXTRACT_TABLE);

    // Live mapping preview: Name/Score/State matched, Notes skipped.
    const map = authPage.getByTestId("db-ai-extract-map");
    await expect(map).toBeVisible({ timeout: 20_000 });
    await expect(map).toContainText("→ Name");
    await expect(map).toContainText("→ Score");
    await expect(map).toContainText("→ State");
    await expect(authPage.getByTestId("db-ai-extract-ignored")).toContainText("Notes");

    const before = ((await (await api.get(`/api/v1/databases/${databaseId}/records`)).json()) as { data: unknown[] }).data.length;

    await authPage.getByTestId("db-ai-extract-apply").click();
    const done = authPage.getByTestId("db-ai-extract-done");
    await expect(done).toBeVisible({ timeout: 30_000 });
    await expect(done).toContainText("3 record");

    // 3 records created; per-type stored values; Notes dropped.
    const after = ((await (
      await api.get(`/api/v1/databases/${databaseId}/records`)
    ).json()) as { data: { properties: Record<string, unknown> }[] }).data;
    expect(after.length).toBe(before + 3);
    const alice = after.find((r) => (r.properties?.[name.id] as { text?: string })?.text === "Alice")!;
    expect(alice).toBeTruthy();
    expect(alice.properties![score.id]).toEqual({ value: 42 });
    // "Open" → the deterministic option id (the API-created option has no id).
    expect(alice.properties![state.id]).toEqual({ optionId: "opt-open" });
  });

  test("formula — description → validated expression + evaluated samples → FORMULA column", async ({ authPage, api }) => {
    const ws = await createWorkspace(api, `Formula ${Date.now()}`);
    const { databaseId } = await createDbViaPage(api, ws.id, "Formula DB");
    // Distinctive record titles so the mock formula (prop("Title") == "Done")
    // evaluates to 100 / 0 on the sample rows. The e2e `createRecord` helper
    // doesn't set a title, so POST the record directly (CreateRecordDto.title).
    for (const title of ["Done", "Open"]) {
      await write(
        api,
        "POST",
        `/api/v1/databases/${databaseId}/records`,
        { title, properties: {}, links: {} },
        "createTitledRecord",
      );
    }

    await authPage.goto(`/w/${ws.id}/databases/${databaseId}`);
    await expect(authPage.getByTestId("database-ask-ai")).toBeVisible({ timeout: 20_000 });

    await openAiMenu(authPage);
    await authPage.getByTestId("db-ai-menu-formula").click();
    const dialog = authPage.getByTestId("db-formula");
    await expect(dialog).toBeVisible();
    await authPage.getByTestId("db-formula-desc").fill("score a row 100 when it is done");
    await authPage.getByTestId("db-formula-generate").click();

    // The validated expression + the evaluated sample table (100 / 0).
    const done = authPage.getByTestId("db-formula-done");
    await expect(done).toBeVisible({ timeout: 30_000 });
    await expect(authPage.getByTestId("db-formula-expression")).toContainText('if(prop("Title") == "Done", 100, 0)');
    const table = authPage.getByTestId("db-formula-table");
    await expect(table).toBeVisible();
    await expect(table).toContainText("100");
    await expect(table).toContainText("0");

    // The FORMULA column was created (a property of type FORMULA).
    const detail = (await (
      await api.get(`/api/v1/databases/${databaseId}`)
    ).json()) as { properties: { name: string; type: string }[] };
    expect(detail.properties.some((p) => p.type === "FORMULA")).toBe(true);
  });

  test("viewer 403 — GUEST: UI hides the write actions, server returns 403 for all three", async ({ api, secondUser }) => {
    const ws = await createWorkspace(api, `Viewer ${Date.now()}`);
    const { databaseId } = await createDbViaPage(api, ws.id, "Viewer DB");

    // A second, freshly-registered user (their own Bearer `api` + browser
    // `page`) added to the workspace as a GUEST via the internal seam.
    const { user, page, api: guestApi } = await secondUser();
    await addMemberViaSeam(seamServiceToken(), ws.id, user.email, "GUEST");

    // Server: all three endpoints are 403 FORBIDDEN for the GUEST (the real
    // test — server-side authorization, not the UI).
    for (const [path, body] of [
      [`/api/v1/workspaces/${ws.id}/ai/databases/generate`, { prompt: "a bug tracker" }],
      [`/api/v1/workspaces/${ws.id}/ai/databases/extract`, { databaseId, text: EXTRACT_TABLE }],
      [`/api/v1/workspaces/${ws.id}/ai/databases/formulas`, { databaseId, description: "x" }]
    ] as const) {
      const res = await guestApi.post(path, { data: body });
      expect(res.status(), `${path} should be 403`).toBe(403);
      const envelope = (await res.json()) as { error?: { code?: string } };
      expect(envelope.error?.code).toBe("FORBIDDEN");
    }

    // UI: the GUEST's AI dropdown hides the write actions (only "Ask AI"
    // shows). The DB route's `readOnly` now reads the caller's role from
    // `GET /workspaces/:id` (surfaced in TASK-0124).
    await page.goto(`/w/${ws.id}/databases/${databaseId}`);
    await expect(page.getByTestId("database-ask-ai")).toBeVisible({ timeout: 20_000 });
    await openAiMenu(page);
    await expect(page.getByTestId("db-ai-menu-ask")).toBeVisible();
    await expect(page.getByTestId("db-ai-menu-generate")).toHaveCount(0);
    await expect(page.getByTestId("db-ai-menu-extract")).toHaveCount(0);
    await expect(page.getByTestId("db-ai-menu-formula")).toHaveCount(0);
  });
});