/**
 * T-165 — CSV import (`POST /api/v1/databases/:id/import/csv`).
 *
 * Covers: the dry-run mapping contract (header → property, title column,
 * unmapped read-only properties, sample rows, zero writes), the import
 * (records + page titles + per-type values, audit row, `record.created`
 * events), per-row error isolation (a bad cell fails that row only),
 * explicit `mappings` overrides + their 400s, the 2 MB body cap (413),
 * the 10k-row cap (400, not truncation), and authorization (VIEW member
 * 403, non-member 403, unknown id 404 no-leak).
 */
import { afterAll, beforeAll, describe, expect, it } from "vitest";
import { createTestApp, type TestAppHandle } from "./helpers/api";
import {
  addMemberViaSeam,
  authHeaders,
  countAuditEvents,
  createWorkspace,
  createUser
} 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>;

/**
 * A database with Title + Status (the default seed) plus `extraProperties`
 * added through the public property endpoint. Databases are created THROUGH
 * THE PAGES SURFACE (`POST /workspaces/:id/pages` with `type: "database"` —
 * there is no `POST /databases` route in the API). Returns a name → id map.
 */
async function setupDb(
  extraProperties: { name: string; type: string; config?: unknown }[] = [],
): Promise<{
  headers: Record<string, string>;
  workspaceId: string;
  databaseId: string;
  props: Record<string, string>;
}> {
  const user = await createUser(app.request, { name: "CSV Owner" });
  const headers = authHeaders(user.accessToken!);
  const { workspaceId } = await createWorkspace(app.request, headers, "CSV WS");
  const res = await app.request("POST", `/api/v1/workspaces/${workspaceId}/pages`, {
    headers,
    body: { title: "Imported", 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,
      body: { name: p.name, type: p.type, config: p.config ?? {} },
    });
    expect([200, 201]).toContain(propRes.status);
  }
  const rows = await app.prisma.databaseProperty.findMany({ where: { databaseId, deletedAt: null } });
  const props: Record<string, string> = {};
  for (const r of rows) props[r.name] = r.id;
  return { headers, workspaceId, databaseId, props };
}

async function importCsv(
  databaseId: string,
  body: Record<string, unknown>,
  headers: Record<string, string>,
): Promise<Response> {
  return app.request("POST", `/api/v1/databases/${databaseId}/import/csv`, { headers, body });
}

/** The test-only debug events endpoint (NODE_ENV=test + per-run token). */
async function debugEvents(name: string): Promise<{ event: string; payload: Record<string, unknown> }[]> {
  const r = await app.request("GET", `/api/v1/debug/events?name=${name}&limit=200`, {
    headers: { "X-Debug-Token": app.debugToken ?? "" },
  });
  if (r.status !== 200) throw new Error(`debug events endpoint failed (${r.status}): ${await r.text()}`);
  return ((await json(r)) as { items: { event: string; payload: Record<string, unknown> }[] }).items;
}

/** The records of a database via the public list endpoint. */
async function listRecords(
  databaseId: string,
  headers: Record<string, string>,
): Promise<Record<string, unknown>[]> {
  const r = await app.request("GET", `/api/v1/databases/${databaseId}/records`, { headers });
  if (r.status !== 200) throw new Error(`list records failed (${r.status}): ${await r.text()}`);
  return ((await json(r)) as { data: Record<string, unknown>[] }).data;
}

describe("T-165 CSV import — happy path (dry run → import)", () => {
  it("dry run maps headers to properties, reports sample rows, writes nothing; import lands records", async () => {
    const { headers, databaseId, props } = await setupDb([
      { name: "Note", type: "TEXT" },
      { name: "Score", type: "NUMBER" },
      { name: "Due", type: "DATE" },
      { name: "Done", type: "CHECKBOX" }
    ]);
    const csv = [
      "Name,Status,Note,Score,Due,Done",
      "Alpha,To Do,first,1,2026-01-05,yes",
      "Beta,In Progress,second,2.5,2026-02-10,false"
    ].join("\n");

    // ---- dry run (the default) ----
    const dry = await importCsv(databaseId, { csv, titleHeader: "Name" }, headers);
    expect(dry.status).toBe(200);
    const d = await json(dry);
    expect(d.dryRun).toBe(true);
    expect(d.databaseId).toBe(databaseId);
    expect(d.rowCount).toBe(2);
    const cols = (d.columns as { index: number; header: string; propertyId: string | null; reason: string }[]).sort(
      (a, b) => a.index - b.index,
    );
    expect(cols).toEqual([
      { index: 0, header: "Name", propertyId: props.Title, reason: "title" },
      { index: 1, header: "Status", propertyId: props.Status, reason: "" },
      { index: 2, header: "Note", propertyId: props.Note, reason: "" },
      { index: 3, header: "Score", propertyId: props.Score, reason: "" },
      { index: 4, header: "Due", propertyId: props.Due, reason: "" },
      { index: 5, header: "Done", propertyId: props.Done, reason: "" }
    ]);
    // Everything non-title is mapped → nothing left unmapped.
    expect(d.unmappedProperties).toEqual([]);
    expect(d.sampleRows).toEqual([
      { Name: "Alpha", Status: "To Do", Note: "first", Score: "1", Due: "2026-01-05", Done: "yes" },
      { Name: "Beta", Status: "In Progress", Note: "second", Score: "2.5", Due: "2026-02-10", Done: "false" }
    ]);
    // The dry run wrote nothing.
    expect(await app.prisma.databaseRecord.count({ where: { databaseId } })).toBe(0);

    // ---- the real import ----
    const imp = await importCsv(databaseId, { csv, titleHeader: "Name", dryRun: false }, headers);
    expect(imp.status).toBe(201);
    const i = await json(imp);
    expect(i).toMatchObject({ dryRun: false, databaseId, imported: 2, failed: 0, rows: 2, errors: [] });

    // Records + page titles + per-type values.
    const records = await listRecords(databaseId, headers);
    expect(records.map((r) => r.title)).toEqual(["Alpha", "Beta"]);
    const [a, b] = records as Record<string, unknown>[];
    expect(a.properties[props.Status]).toEqual({ optionId: "opt-to-do" });
    expect(a.properties[props.Note]).toEqual({ text: "first", marks: [] });
    expect(a.properties[props.Score]).toEqual({ value: 1 });
    expect(a.properties[props.Due]).toEqual({ start: "2026-01-05T00:00:00.000Z", end: null });
    expect(a.properties[props.Done]).toEqual({ value: true });
    expect(b.properties[props.Status]).toEqual({ optionId: "opt-in-progress" });
    expect(b.properties[props.Score]).toEqual({ value: 2.5 });
    expect(b.properties[props.Done]).toEqual({ value: false });
    // Records are page-backed (the record-a-page invariant).
    const stored = await app.prisma.databaseRecord.findMany({ where: { databaseId }, select: { pageId: true } });
    for (const s of stored) expect(s.pageId).not.toBeNull();

    // Audit + stream events.
    expect(
      await app.prisma.auditLog.findFirst({
        where: { action: "database.records_imported", resourceId: databaseId }
      }),
    ).not.toBeNull();
    const events = (await debugEvents("record.created")).filter(
      (e) => (e.payload as Record<string, unknown>).databaseId === databaseId,
    );
    expect(events.length).toBe(2);
  });
});

describe("T-165 CSV import — unmapped/read-only reporting", () => {
  it("reports read-only, relation-id and unmatched columns with reasons in the dry run", async () => {
    const { headers, databaseId, props } = await setupDb([
      { name: "Created", type: "CREATED_TIME" },
      { name: "Owner", type: "PERSON" }
    ]);
    const csv = ["Title,UnknownCol,Note", "A,x,hello", "B,y,world"].join("\n");
    const dry = await importCsv(databaseId, { csv }, headers);
    expect(dry.status).toBe(200);
    const d = await json(dry);
    const cols = (d.columns as { index: number; header: string; propertyId: string | null; reason: string }[]).sort(
      (x, y) => x.index - y.index,
    );
    expect(cols).toEqual([
      { index: 0, header: "Title", propertyId: props.Title, reason: "title" },
      { index: 1, header: "UnknownCol", propertyId: null, reason: "no_matching_property" },
      { index: 2, header: "Note", propertyId: null, reason: "no_matching_property" }
    ]);
    // Read-only + id-only properties are reported with a reason, never imported.
    const unmapped = (
      (d.unmappedProperties as { id: string; name: string; type: string; reason: string }[])
        .map((p) => ({ name: p.name, reason: p.reason }))
        .sort((x, y) => x.name.localeCompare(y.name))
    );
    expect(unmapped).toEqual([
      { name: "Created", reason: "read_only" },
      { name: "Owner", reason: "record_ids_only" },
      { name: "Status", reason: "not_importable" }
    ]);
    // The dry run is a pure preview — nothing is written.
    expect(await app.prisma.databaseRecord.count({ where: { databaseId } })).toBe(0);
    // A title-only import is a valid minimal import (the title is written
    // through the page title): it succeeds with one record and no property rows.
    const imp = await importCsv(databaseId, { csv, dryRun: false }, headers);
    expect(imp.status).toBe(201);
    const i = await json(imp);
    expect(i.imported).toBe(2);
    const records = await listRecords(databaseId, headers);
    expect(records.map((r) => r.title).sort()).toEqual(["A", "B"]);
    // No importable columns AT ALL (no title column, no property match) → 400.
    const nothing = await importCsv(databaseId, { csv: "Foo,Bar\n1,2", dryRun: false }, headers);
    expect(nothing.status).toBe(400);
    expect(((await json(nothing)).error as { details: { issue: string }[] }).details[0]?.issue).toBe(
      "no_importable_columns",
    );
  });
});

describe("T-165 CSV import — per-row error isolation", () => {
  it("skips rows with bad cells (row/column in errors) while good rows still import in the same call", async () => {
    const { headers, databaseId, props } = await setupDb([
      { name: "Score", type: "NUMBER" },
      { name: "Due", type: "DATE" }
    ]);
    const csv = [
      "Title,Score,Status,Due",
      "Good,1,To Do,2026-01-01",
      "BadNumber,xyz,To Do,2026-01-02",
      "BadOption,2,DoesNotExist,2026-01-03",
      "BadDate,3,Done,not-a-date"
    ].join("\n");

    const imp = await importCsv(databaseId, { csv, dryRun: false }, headers);
    expect(imp.status).toBe(201);
    const i = await json(imp);
    expect(i.imported).toBe(1);
    expect(i.failed).toBe(3);
    const errors = (i.errors as { row: number; column: string; error: string }[]).sort(
      (a, b) => a.row - b.row,
    );
    expect(errors.map((e) => [e.row, e.column])).toEqual([
      [3, "Score"],
      [4, "Status"],
      [5, "Due"]
    ]);
    for (const e of errors) expect(e.error.length).toBeGreaterThan(0);
    expect(errors[0].error).toContain("xyz");
    expect(errors[1].error).toContain("DoesNotExist");
    expect(errors[2].error).toContain("not-a-date");

    const records = await listRecords(databaseId, headers);
    expect(records.map((r) => r.title)).toEqual(["Good"]);
    expect(records[0].properties[props.Score]).toEqual({ value: 1 });

    // The audit row records both the imported and the failed counts.
    const audit = await app.prisma.auditLog.findFirst({
      where: { action: "database.records_imported", resourceId: databaseId }
    });
    expect(audit?.metadata).toMatchObject({ imported: 1, failed: 3, rows: 4 });
    expect(
      await countAuditEvents(app.prisma, { action: "database.records_imported", actorId: audit?.actorId ?? "" }),
    ).toBeGreaterThanOrEqual(1);
  });
});

describe("T-165 CSV import — explicit mappings", () => {
  it("maps a renamed header to a writable property", async () => {
    const { headers, databaseId, props } = await setupDb([{ name: "Note", type: "TEXT" }]);
    const csv = ["Renamed,Title", "value-a,Alpha"].join("\n");
    const imp = await importCsv(
      databaseId,
      { csv, dryRun: false, mappings: { Renamed: props.Note } },
      headers,
    );
    expect(imp.status).toBe(201);
    const i = await json(imp);
    expect(i.imported).toBe(1);
    const records = await listRecords(databaseId, headers);
    expect(records[0].title).toBe("Alpha");
    expect(records[0].properties[props.Note]).toEqual({ text: "value-a", marks: [] });
  });

  it("rejects mappings that target the title, read-only, relation-id or unknown properties/headers", async () => {
    const { headers, databaseId, props } = await setupDb([
      { name: "Note", type: "TEXT" },
      { name: "Created", type: "CREATED_TIME" },
      { name: "Owner", type: "PERSON" }
    ]);
    const csv = ["Title,Extra", "Alpha,x"].join("\n");
    const cases: [string, Record<string, unknown>, string][] = [
      ["unknown header in mappings", { csv, mappings: { Nope: props.Note } }, "unknown_header"],
      ["unknown property id", { csv, mappings: { Extra: "dbp_does_not_exist" } }, "unknown_property"],
      ["title via mappings", { csv, mappings: { Title: props.Title } }, "title_via_title_header"],
      ["read-only property", { csv, mappings: { Extra: props.Created } }, "read_only_property"],
      ["record-id-only property", { csv, mappings: { Extra: props.Owner } }, "record_ids_only"]
    ];
    for (const [label, body, issue] of cases) {
      const res = await importCsv(databaseId, { ...body, dryRun: false }, headers);
      expect(res.status, label).toBe(400);
      const details = ((await json(res)).error as { details: { issue: string }[] }).details;
      expect(details.some((d) => d.issue === issue), label).toBe(true);
    }
    // Unknown titleHeader is a 400 as well.
    const badTitle = await importCsv(databaseId, { csv, titleHeader: "NotACol", dryRun: false }, headers);
    expect(badTitle.status).toBe(400);
    expect(
      ((await json(badTitle)).error as { details: { issue: string; field: string }[] }).details.some(
        (d) => d.issue === "unknown_header" && d.field === "titleHeader",
      ),
    ).toBe(true);
    // None of the rejections wrote anything.
    expect(await app.prisma.databaseRecord.count({ where: { databaseId } })).toBe(0);
  });
});

describe("T-165 CSV import — caps", () => {
  it("rejects a body over the 2 MB ceiling with a 413 envelope", async () => {
    const { headers, databaseId } = await setupDb();
    // ~2.2 MB of rows (20.5k × ~107 chars) — comfortably over 2 MB.
    const rows: string[] = [];
    for (let i = 0; i < 20_500; i += 1) rows.push(`Row${i},"${"x".repeat(96)}`);
    const res = await importCsv(databaseId, { csv: ["Title,Note", ...rows].join("\n") }, headers);
    expect(res.status).toBe(413);
    const e = (await json(res)).error as { code: string };
    expect(e.code).toBe("PAYLOAD_TOO_LARGE");
    expect(await app.prisma.databaseRecord.count({ where: { databaseId } })).toBe(0);
  });

  it("rejects more than 10k data rows with a 400 too_many_rows (no truncation)", async () => {
    const { headers, databaseId } = await setupDb();
    const rows: string[] = [];
    for (let i = 0; i < 10_001; i += 1) rows.push(`Row${i}`);
    const res = await importCsv(databaseId, { csv: ["Title", ...rows].join("\n") }, headers);
    expect(res.status).toBe(400);
    const e = (await json(res)).error as { code: string; details: { issue: string; received: number; limit: number }[] };
    expect(e.code).toBe("VALIDATION_ERROR");
    expect(e.details[0]).toMatchObject({ issue: "too_many_rows", received: 10_001, limit: 10_000 });
    expect(await app.prisma.databaseRecord.count({ where: { databaseId } })).toBe(0);
  });
});

describe("T-165 CSV import — validation + authorization", () => {
  it("rejects an empty csv at the DTO boundary", async () => {
    const { headers, databaseId } = await setupDb();
    const res = await importCsv(databaseId, { csv: "" }, headers);
    expect(res.status).toBe(400);
    expect(((await json(res)).error as { code: string }).code).toBe("VALIDATION_ERROR");
  });

  it("a VIEW member (GUEST) gets 403; a non-member gets 403; an unknown id gets 404", async () => {
    const { headers, databaseId, workspaceId } = await setupDb();
    // GUEST member → resolves VIEW < EDIT → 403.
    const guest = await createUser(app.request);
    await addMemberViaSeam(app.request, app.internalServiceToken, workspaceId, guest.email, "GUEST");
    const guestRes = await importCsv(
      databaseId,
      { csv: "Title\nA" },
      authHeaders(guest.accessToken!),
    );
    expect(guestRes.status).toBe(403);
    expect(((await json(guestRes)).error as { code: string }).code).toBe("FORBIDDEN");

    // Non-member (no leak of the id: the module's house behavior is 403 for
    // a known-but-inaccessible database — same as every other db endpoint).
    const outsider = await createUser(app.request);
    const outRes = await importCsv(
      databaseId,
      { csv: "Title\nA" },
      authHeaders(outsider.accessToken!),
    );
    expect(outRes.status).toBe(403);

    // Unknown id → 404.
    const nfRes = await importCsv("dbp_does_not_exist", { csv: "Title\nA" }, headers);
    expect(nfRes.status).toBe(404);
    expect(((await json(nfRes)).error as { code: string }).code).toBe("NOT_FOUND");
  });
});