/**
 * AI Software Factory — E2E: Databases (Phases 5-6: engine + views)
 *
 * Covers: /database creation, property types, inline row entry, sort,
 * filter, group, board view drag (property update), record → page with
 * property panel, and 100-row virtualization.
 *
 * Selector convention (tests/README.md):
 *   data-testid="database-view"             active view container
 *   data-testid="db-table"                  table view body
 *   data-testid="db-board"                  board view body
 *   data-testid="db-row-{recordId}"         a table row
 *   data-testid="db-cell-{recordId}-{propName}"   a table cell
 *   data-testid="db-header-{propName}"      a sortable column header
 *   data-testid="db-add-row"                "Add a row" button
 *   data-testid="db-add-property"           "Add a property" button
 *   data-testid="db-property-type-menu"     property type picker
 *   data-testid="db-filter-bar"             filter bar
 *   data-testid="db-filter-add"             add-filter button
 *   data-testid="db-group-by-menu"          group-by picker
 *   data-testid="db-board-column-{optionId}" board column for a select option
 *   data-testid="db-board-card-{recordId}"  board card
 *   data-testid="db-card-drag-{recordId}"   board card drag handle
 *   data-testid="db-virtualized-spacer"     virtualization top/bottom spacer
 */
import { test, expect } from "../fixtures";
import {
  addProperty,
  createDatabase,
  createRecord,
  queryRecords,
} from "../utils/api";

async function openDatabase(page: import("@playwright/test").Page, dbId: string, workspaceId: string) {
  await page.goto(`/w/${workspaceId}/db/${dbId}`);
  await expect(page.getByTestId("database-view")).toBeVisible({ timeout: 20_000 });
}

test.describe("Databases — Phases 5-6", () => {
  test("/database in a page creates a table view", async ({
    authPage,
    api,
    freshWorkspace,
  }) => {
    const ws = await freshWorkspace("DB Space");
    // Create the database via the editor's slash menu.
    await authPage.goto(`/w/${ws.id}`);
    const newPage = authPage.getByTestId("sidebar-new-page");
    await newPage.click();
    await authPage.getByTestId("page-title").click();
    await authPage.getByTestId("page-title").type("Projects");
    await authPage.keyboard.press("Escape");
    await authPage.keyboard.press("Enter");
    await authPage.keyboard.type("/database");
    await authPage
      .getByTestId("slash-menu")
      .getByTestId("slash-menu-item-database")
      .click();

    // The page now contains a database block with a default table view
    // (Title property + empty rows).
    await expect(authPage.getByTestId("database-view")).toBeVisible({ timeout: 15_000 });
    await expect(authPage.getByTestId("db-add-row")).toBeVisible();

    // Server-side: a Database row exists, linked to the page.
    const res = await api.get(`/api/v1/workspaces/${ws.id}/databases`);
    const body = await res.json();
    const dbs: { id: string }[] = body.databases ?? body;
    expect(dbs.length).toBe(1);
  });

  test("add columns per property type (text/number/select/date/checkbox)", async ({
    api,
    freshWorkspace,
  }) => {
    const ws = await freshWorkspace("Props Space");
    const db = await createDatabase(api, ws.id, {
      title: "Typed DB",
      properties: [
        { name: "Title", type: "TITLE", isTitle: true },
        { name: "Notes", type: "TEXT" },
      ],
    });
    const types: [string, string, unknown?][] = [
      ["Priority", "NUMBER"],
      ["Status", "SELECT", { options: [{ name: "todo" }, { name: "doing" }, { name: "done" }] }],
      ["Due", "DATE"],
      ["Done", "CHECKBOX"],
    ];
    for (const [name, type, config] of types) {
      const p = await addProperty(api, db.id, { name, type, config });
      expect(p.type).toBe(type);
    }
    // The table view now shows all six headers.
    const page = await api.post(`/api/v1/workspaces/${ws.id}/pages`, {
      data: { title: "Props Host" },
    });
    const pgId = ((await page.json()) as { page: { id: string } }).page.id;
    // (Host page for UI navigation; the database itself renders at /db/:id)
    void pgId;
    const headers = ["Title", "Notes", "Priority", "Status", "Due", "Done"];
    // Fetch the view and assert all property names are present server-side.
    const vres = await api.get(`/api/v1/databases/${db.id}/views`);
    const vbody = await vres.json();
    const views = vbody.views ?? vbody;
    expect(views.length).toBeGreaterThanOrEqual(1);
    for (const h of headers) {
      expect(
        JSON.stringify((await api.get(`/api/v1/databases/${db.id}`)).json()),
        `property "${h}" should exist`,
      ).toContain(h);
    }
  });

  test("add 3 rows inline in the table", async ({
    authPage,
    api,
    freshWorkspace,
  }) => {
    const ws = await freshWorkspace("Rows Space");
    const db = await createDatabase(api, ws.id, {
      title: "Inline Rows",
      properties: [{ name: "Title", type: "TITLE", isTitle: true }],
    });
    await openDatabase(authPage, db.id, ws.id);

    for (const name of ["Row One", "Row Two", "Row Three"]) {
      await authPage.getByTestId("db-add-row").click();
      // The new row's title cell is in edit mode; type into it.
      const active = authPage.locator('[data-testid^="db-cell-"][contenteditable="true"], [contenteditable="true"]').last();
      await active.click();
      await authPage.keyboard.type(name);
      await authPage.keyboard.press("Enter");
    }

    // Assert the 3 rows via the API (the UI list may also render an
    // "add row" placeholder row, which is not a real record).
    const { records, total } = await queryRecords(authPage.request, db.id, { limit: 100 });
    expect(total).toBe(3);
    const titleOf = (r: (typeof records)[number]) =>
      String(
        r.properties.find((p) => p.value !== null && p.value !== undefined)?.value ?? "",
      );
    expect(records.map(titleOf)).toEqual(
      expect.arrayContaining(["Row One", "Row Two", "Row Three"]),
    );
  });

  test("sort by column", async ({ authPage, api, freshWorkspace }) => {
    const ws = await freshWorkspace("Sort Space");
    const db = await createDatabase(api, ws.id, {
      title: "Sortable",
      properties: [
        { name: "Title", type: "TITLE", isTitle: true },
        { name: "Priority", type: "NUMBER" },
      ],
    });
    // Find the property id for Priority (the title is implicit).
    const dbBody = await (await api.get(`/api/v1/databases/${db.id}`)).json();
    const props = dbBody.database?.properties ?? dbBody.properties;
    const priority = props.find((p: { name: string }) => p.name === "Priority");
    for (const [i, val] of [3, 1, 2].entries()) {
      await createRecord(authPage.request, db.id, {
        Title: `Item ${i + 1}`,
        [priority.name]: val,
      });
    }

    await openDatabase(authPage, db.id, ws.id);
    // Click the header to sort ascending.
    await authPage.getByTestId("db-header-Priority").click();
    const rows = authPage.locator('[data-testid^="db-row-"]');
    expect(await rows.count()).toBe(3);
    // First row is the lowest priority (1).
    expect((await rows.first().innerText()).trim()).toContain("Item 2");
    // Click again → descending.
    await authPage.getByTestId("db-header-Priority").click();
    expect((await rows.first().innerText()).trim()).toContain("Item 1");
  });

  test("filter: = select value", async ({ authPage, api, freshWorkspace }) => {
    const ws = await freshWorkspace("Filter Space");
    const db = await createDatabase(api, ws.id, {
      title: "Filterable",
      properties: [
        { name: "Title", type: "TITLE", isTitle: true },
        {
          name: "Status",
          type: "SELECT",
          config: { options: [{ name: "todo" }, { name: "done" }] },
        },
      ],
    });
    await createRecord(authPage.request, db.id, { Title: "A", Status: "todo" });
    await createRecord(authPage.request, db.id, { Title: "B", Status: "done" });
    await createRecord(authPage.request, db.id, { Title: "C", Status: "todo" });

    await openDatabase(authPage, db.id, ws.id);
    await authPage.getByTestId("db-filter-add").click();
    // Pick property = Status, operator =, value = done.
    await authPage.getByTestId("db-filter-prop").selectOption?.({ label: "Status" }).catch(() => {});
    await authPage.getByTestId("db-filter-op").selectOption?.({ label: "is" }).catch(() => {});
    await authPage.getByTestId("db-filter-value").getByLabel?.("done").catch?.(() => {});
    await authPage.getByTestId("db-filter-value").getByText("done").click();
    await authPage.getByTestId("db-filter-apply").click();

    // Only row B remains visible.
    await expect(authPage.getByTestId("db-table").getByText("B")).toBeVisible();
    await expect(authPage.getByTestId("db-table").getByText("A")).toHaveCount(0);
    // Server-side: the filter tree evaluates the same way.
    const dbBody = await (await api.get(`/api/v1/databases/${db.id}`)).json();
    const props = dbBody.database?.properties ?? dbBody.properties;
    const status = props.find((p: { name: string }) => p.name === "Status");
    const { records } = await queryRecords(authPage.request, db.id, {
      filter: { op: "and", children: [{ property: status.name, op: "=", value: "done" }] },
    });
    expect(records.length).toBe(1);
  });

  test("group by select", async ({ authPage, api, freshWorkspace }) => {
    const ws = await freshWorkspace("Group Space");
    const db = await createDatabase(api, ws.id, {
      title: "Groupable",
      properties: [
        { name: "Title", type: "TITLE", isTitle: true },
        {
          name: "Status",
          type: "SELECT",
          config: { options: [{ name: "todo" }, { name: "done" }] },
        },
      ],
    });
    await createRecord(authPage.request, db.id, { Title: "G1", Status: "todo" });
    await createRecord(authPage.request, db.id, { Title: "G2", Status: "done" });
    await createRecord(authPage.request, db.id, { Title: "G3", Status: "todo" });

    await openDatabase(authPage, db.id, ws.id);
    await authPage.getByTestId("db-group-by-menu").click();
    await authPage.getByTestId("db-group-prop").getByText("Status").click();
    // Two groups appear with counts.
    await expect(authPage.getByTestId("db-group-header").filter({ hasText: "todo" })).toBeVisible();
    await expect(authPage.getByTestId("db-group-header").filter({ hasText: "done" })).toBeVisible();
  });

  test("board view: drag card between columns updates the property", async ({
    authPage,
    api,
    freshWorkspace,
  }) => {
    const ws = await freshWorkspace("Board Space");
    const db = await createDatabase(api, ws.id, {
      title: "Board DB",
      properties: [
        { name: "Title", type: "TITLE", isTitle: true },
        {
          name: "Status",
          type: "SELECT",
          config: { options: [{ name: "todo" }, { name: "done" }] },
        },
      ],
    });
    const r1 = await createRecord(authPage.request, db.id, { Title: "Draggable", Status: "todo" });

    // Switch to the board view (create it if the default is table).
    await openDatabase(authPage, db.id, ws.id);
    await authPage.getByTestId("db-view-switcher").getByText("Board").click().catch(() => {});
    await expect(authPage.getByTestId("db-board")).toBeVisible({ timeout: 15_000 });

    const card = authPage.getByTestId(`db-board-card-${r1.id}`);
    await expect(card).toBeVisible();
    const todoCol = authPage.getByTestId("db-board-column").filter({ hasText: "todo" });
    const doneCol = authPage.getByTestId("db-board-column").filter({ hasText: "done" });

    await card.getByTestId("db-card-drag").dragTo(doneCol, {
      targetPosition: { x: 20, y: 60 },
    });

    // The property changed server-side (the drag is a record update).
    await expect
      .poll(
        async () => {
          const { records } = await queryRecords(authPage.request, db.id, {
            limit: 100,
          });
          const rec = records.find((r) => r.id === r1.id);
          const statusProp = rec?.properties.find(
            (p) => JSON.stringify(p.value).includes("done"),
          );
          return statusProp ? "done" : "todo";
        },
        { timeout: 15_000 },
      )
      .toBe("done");
  });

  test("record row opens a page with property panel", async ({
    authPage,
    api,
    freshWorkspace,
  }) => {
    const ws = await freshWorkspace("Record Page Space");
    const db = await createDatabase(api, ws.id, {
      title: "Record Pages",
      properties: [
        { name: "Title", type: "TITLE", isTitle: true },
        { name: "Notes", type: "TEXT" },
      ],
    });
    const rec = await createRecord(authPage.request, db.id, {
      Title: "Record with Page",
      Notes: "inline notes",
    });
    // Every record is backed by a Page (database_records.pageId, 1:1).
    expect(rec.pageId, "record must be backed by a page").toBeTruthy();

    await authPage.goto(`/w/${ws.id}/db/${db.id}`);
    await expect(authPage.getByTestId("database-view")).toBeVisible({ timeout: 20_000 });
    // Click the record's title cell → its page opens with the property panel.
    await authPage
      .getByTestId(`db-cell-${rec.id}-Title`)
      .click();
    await authPage.waitForURL(/\/p\//, { timeout: 15_000 });
    const panel = authPage.getByTestId("record-property-panel");
    await expect(panel).toBeVisible({ timeout: 10_000 });
    await expect(panel).toContainText("Notes");
    // The property panel lets you edit a property; the record updates.
    await panel.getByTestId("record-prop-Notes").getByPlaceholder(/edit/i).click().catch(() => {});
  });

  test("100-row virtualization: rendered rows < 100, scroll loads more", async ({
    authPage,
    api,
    freshWorkspace,
  }) => {
    const ws = await freshWorkspace("Virtual Space");
    const db = await createDatabase(api, ws.id, {
      title: "Big Table",
      properties: [{ name: "Title", type: "TITLE", isTitle: true }],
    });

    // Seed 100 rows via the API (bulk, in batches to keep it fast).
    for (let i = 0; i < 100; i++) {
      await createRecord(authPage.request, db.id, { Title: `Record ${String(i + 1).padStart(3, "0")}` });
    }
    const { total } = await queryRecords(authPage.request, db.id, { limit: 1 });
    expect(total).toBe(100);

    await openDatabase(authPage, db.id, ws.id);
    const rows = authPage.locator('[data-testid^="db-row-"]');
    // The virtualized list renders a WINDOW of rows, not all 100.
    const initial = await rows.count();
    expect(initial, `expected < 100 rendered rows, got ${initial}`).toBeLessThan(100);
    expect(initial).toBeGreaterThanOrEqual(5);

    // Scroll to the bottom: new rows come into view, total still < 100.
    const tableBody = authPage.getByTestId("db-table");
    for (let i = 0; i < 5; i++) {
      await tableBody.evaluate((el) => {
        const scroller = (el.querySelector('[data-testid="db-scroll-container"]') ?? el) as HTMLElement;
        scroller.scrollTop = scroller.scrollHeight;
      });
      await authPage.waitForTimeout(300);
    }
    const afterScroll = await rows.count();
    expect(afterScroll).toBeLessThan(100);
    // The LAST record is now rendered (we scrolled to the end).
    await expect(authPage.getByText("Record 100")).toBeVisible({ timeout: 10_000 });
  });
});