/**
 * TASK-0124 — unit tests for the database-AI validators + the retry-once
 * contract.
 *
 * The spec's unit acceptance criteria:
 *   - `validateCreateDatabase`: a clean call normalizes (options get ids,
 *     sample records keyed by NAME, per-type coercion); a non-generatable
 *     type / a seeded Title-Status collision / a duplicate name / a
 *     select-without-options / a wrong-type sample value → `ai_invalid_structure`;
 *   - `validateInsertRecords`: propertyId-keyed records, unknown propertyId
 *     rejected, title → `__title__`, wrong-type rejected, >200 rejected;
 *   - `prepareAiValue`: per-type coercion (checkbox yes/1/x, number
 *     string→number, select name→id, multi_select comma-split, date ISO);
 *   - `parseMarkdownTable`: leading/trailing + escaped pipes, a missing
 *     header row, the separator row, a non-table;
 *   - `mapColumnsToProperties`: case-insensitive match, ignored columns;
 *   - the three tool specs pass the `@factory/ai` STRICT tool validator
 *     (the gateway rejects anything else);
 *   - the allowlist is exhaustive: GENERATABLE ∪ NON_GENERATABLE = the 24
 *     real PropertyType values, disjoint;
 *   - the RETRY-ONCE contract: an invalid tool call → exactly a SECOND
 *     `gateway.chat` (with the validator's feedback in the prompt) and NO
 *     partial write.
 */
import { describe, expect, it, vi, beforeEach } from "vitest";

import {
  validateTools,
  PROPERTY_TYPES,
  GENERATABLE_PROPERTY_TYPES,
  NON_GENERATABLE_PROPERTY_TYPES,
  optionIdForName,
  normalizePropertyType,
  parseMarkdownTable,
  mapColumnsToProperties,
  DB_GEN_MAX_PROPERTIES,
  DB_GEN_MAX_SAMPLE_RECORDS,
  DB_EXTRACT_MAX_RECORDS,
} from "@factory/ai";
import {
  validateCreateDatabase,
  validateInsertRecords,
  prepareAiValue,
  previewExtractMapping,
  CREATE_DATABASE_TOOL,
  INSERT_RECORDS_TOOL,
  GENERATE_FORMULA_TOOL,
  renderExtractPrompt,
  renderFormulaPrompt,
  renderGeneratePrompt,
  DB_GEN_SYSTEM_PROMPT,
  type DbPropMeta,
} from "../db-prompts.js";
import { DatabaseAiService } from "../database-ai.service.js";

vi.mock("../../../lib/authorize.js", async (importOriginal) => {
  const actual = await importOriginal<typeof import("../../../lib/authorize.js")>();
  return { ...actual, assertCan: vi.fn(async () => undefined) };
});

const WS = "ws1";
const USER = "u1";

// ---------------------------------------------------------------------------
// validateCreateDatabase
// ---------------------------------------------------------------------------
describe("validateCreateDatabase (generate)", () => {
  it("normalizes a clean call: options get deterministic ids, sample records keyed by NAME", () => {
    const r = validateCreateDatabase({
      title: "Tasks",
      description: "a task tracker",
      properties: [
        { name: "Priority", type: "select", options: [{ name: "High" }, { name: "Low" }] },
        { name: "Due", type: "date" },
        { name: "Done", type: "checkbox" },
      ],
      sampleRecords: [{ properties: { Title: "Ship it", Priority: "High", Due: "2026-01-01", Done: "yes" } }],
    });
    expect(r.ok).toBe(true);
    if (!r.ok) return;
    expect(r.value.title).toBe("Tasks");
    const priority = r.value.properties.find((p) => p.name === "Priority")!;
    expect(priority.type).toBe("SELECT");
    expect(priority.options!.map((o) => o.id)).toEqual([optionIdForName("High"), optionIdForName("Low")]);
    expect(priority.optionNameToId.get("high")).toBe(optionIdForName("High"));
    const rec = r.value.sampleRecords[0]!;
    expect(rec.Title).toBe("Ship it");
    expect(rec.Priority).toBe(optionIdForName("High")); // select name → id
    expect(rec.Due).toBe("2026-01-01"); // date stays ISO
    expect(rec.Done).toBe(true); // checkbox "yes" → true
  });

  it("rejects a non-generatable type (relation) with a reason", () => {
    const r = validateCreateDatabase({ title: "X", properties: [{ name: "Link", type: "relation" }] });
    expect(r.ok).toBe(false);
    if (r.ok) return;
    expect(r.reason).toBe("ai_invalid_structure");
    expect(r.feedback).toMatch(/RELATION/i);
  });

  it("rejects re-declaring the seeded Title / Status", () => {
    expect(validateCreateDatabase({ title: "X", properties: [{ name: "Title", type: "text" }] }).ok).toBe(false);
    expect(validateCreateDatabase({ title: "X", properties: [{ name: "Status", type: "select", options: [{ name: "A" }] }] }).ok).toBe(false);
  });

  it("rejects a duplicate property name (case-insensitive)", () => {
    const r = validateCreateDatabase({ title: "X", properties: [{ name: "Due", type: "date" }, { name: "due", type: "text" }] });
    expect(r.ok).toBe(false);
    if (!r.ok) expect(r.feedback).toMatch(/duplicate/i);
  });

  it("rejects a select without options", () => {
    expect(validateCreateDatabase({ title: "X", properties: [{ name: "P", type: "select" }] }).ok).toBe(false);
  });

  it("rejects a sample value that is wrong for the property type", () => {
    const r = validateCreateDatabase({
      title: "X",
      properties: [{ name: "Due", type: "date" }],
      sampleRecords: [{ properties: { Due: true } }], // bool is not a date
    });
    expect(r.ok).toBe(false);
    if (!r.ok) expect(r.feedback).toMatch(/not valid for type/i);
  });

  it("enforces the ≤30 property and ≤10 sample-record caps (schema-level)", () => {
    const props = Array.from({ length: DB_GEN_MAX_PROPERTIES + 1 }, (_, i) => ({ name: `p${i}`, type: "text" }));
    expect(validateCreateDatabase({ title: "X", properties: props }).ok).toBe(false);
    const recs = Array.from({ length: DB_GEN_MAX_SAMPLE_RECORDS + 1 }, (_, i) => {
      const key = "c" + i;
      const props: Record<string, unknown> = {};
      props[key] = i;
      return { properties: props };
    });
    expect(validateCreateDatabase({ title: "X", properties: [{ name: "a", type: "text" }], sampleRecords: recs }).ok).toBe(false);
  });

  it("normalizes common LLM type synonyms (multiselect, boolean, …)", () => {
    const r = validateCreateDatabase({
      title: "X",
      properties: [
        { name: "Tags", type: "multiSelect", options: [{ name: "a" }] },
        { name: "Flag", type: "boolean" },
      ],
    });
    expect(r.ok).toBe(true);
    if (!r.ok) return;
    expect(r.value.properties[0]!.type).toBe("MULTI_SELECT");
    expect(r.value.properties[1]!.type).toBe("CHECKBOX");
  });
});

// ---------------------------------------------------------------------------
// validateInsertRecords
// ---------------------------------------------------------------------------
const PROPS: DbPropMeta[] = [
  { id: "prop_title", name: "Title", type: "TITLE", isTitle: true },
  { id: "prop_name", name: "Name", type: "TEXT" },
  { id: "prop_due", name: "Due", type: "DATE" },
  { id: "prop_state", name: "State", type: "SELECT", config: { options: [{ id: "opt-a", name: "A" }, { id: "opt-b", name: "B" }] } },
];

describe("validateInsertRecords (extract)", () => {
  it("maps propertyId-keyed records; title → __title__; select name → id; checkbox → bool", () => {
    const r = validateInsertRecords(
      {
        records: [
          { properties: { prop_title: "Row 1", prop_name: "n1", prop_due: "2026-01-01", prop_state: "A" } },
          { properties: { prop_name: "n2" } },
        ],
        ignoredColumns: ["Notes"],
      },
      PROPS,
    );
    expect(r.ok).toBe(true);
    if (!r.ok) return;
    expect(r.value.records[0]!.properties.__title__).toBe("Row 1");
    expect(r.value.records[0]!.properties.prop_state).toBe("opt-a"); // name → id
    expect(r.value.records[0]!.properties.prop_due).toBe("2026-01-01");
    expect(r.value.records[1]!.properties.prop_name).toBe("n2");
    expect(r.value.ignoredColumns).toEqual(["Notes"]);
  });

  it("rejects an unknown propertyId (the model never invents ids)", () => {
    const r = validateInsertRecords({ records: [{ properties: { does_not_exist: "x" } }] }, PROPS);
    expect(r.ok).toBe(false);
    if (!r.ok) expect(r.feedback).toMatch(/unknown propertyId/i);
  });

  it("rejects a value that is wrong for the property type (a bool for a date)", () => {
    const r = validateInsertRecords({ records: [{ properties: { prop_due: true } }] }, PROPS);
    expect(r.ok).toBe(false);
    if (!r.ok) expect(r.feedback).toMatch(/invalid/i);
  });

  it("rejects > 200 records (the InsertRecordsSchema caps at 200)", () => {
    const records = Array.from({ length: DB_EXTRACT_MAX_RECORDS + 1 }, () => ({ properties: { prop_name: "x" } }));
    const r = validateInsertRecords({ records }, PROPS);
    expect(r.ok).toBe(false);
    if (r.ok) return;
    // The zod .max(200) fires first → an invalid-structure rejection that names
    // the cap. (The service also has a defense-in-depth too_many_records check.)
    expect(r.reason).toBe("ai_invalid_structure");
    expect(r.feedback).toMatch(/200/);
  });

  it("rejects a malformed shape (records missing `properties`)", () => {
    const r = validateInsertRecords({ records: [{}] }, PROPS);
    expect(r.ok).toBe(false);
  });
});

// ---------------------------------------------------------------------------
// prepareAiValue (per-type coercion)
// ---------------------------------------------------------------------------
describe("prepareAiValue (per-type coercion)", () => {
  const selectMap = new Map([["a", "opt-a"], ["b", "opt-b"]]);
  it("SELECT/STATUS: option name → id (unknown name kept as-is)", () => {
    expect(prepareAiValue("A", "SELECT", selectMap)).toBe("opt-a");
    expect(prepareAiValue("B", "STATUS", selectMap)).toBe("opt-b");
    expect(prepareAiValue("Nope", "SELECT", selectMap)).toBe("Nope");
    expect(prepareAiValue(42, "SELECT", selectMap)).toBeUndefined();
  });
  it("MULTI_SELECT: comma/;-split → ids", () => {
    expect(prepareAiValue("a, b", "MULTI_SELECT", selectMap)).toEqual(["opt-a", "opt-b"]);
    expect(prepareAiValue(["a", "b"], "MULTI_SELECT", selectMap)).toEqual(["opt-a", "opt-b"]);
    expect(prepareAiValue("nope", "MULTI_SELECT", selectMap)).toEqual(["nope"]);
  });
  it("CHECKBOX: yes/1/on/x/✓ → true, no/0/off → false, bool passthrough", () => {
    expect(prepareAiValue("yes", "CHECKBOX", selectMap)).toBe(true);
    expect(prepareAiValue("1", "CHECKBOX", selectMap)).toBe(true);
    expect(prepareAiValue("✓", "CHECKBOX", selectMap)).toBe(true);
    expect(prepareAiValue("no", "CHECKBOX", selectMap)).toBe(false);
    expect(prepareAiValue("0", "CHECKBOX", selectMap)).toBe(false);
    expect(prepareAiValue(true, "CHECKBOX", selectMap)).toBe(true);
    expect(prepareAiValue("maybe", "CHECKBOX", selectMap)).toBeUndefined();
  });
  it("NUMBER: number passthrough, numeric string → number, junk → undefined", () => {
    expect(prepareAiValue(3.14, "NUMBER", selectMap)).toBe(3.14);
    expect(prepareAiValue("42", "NUMBER", selectMap)).toBe(42);
    expect(prepareAiValue("abc", "NUMBER", selectMap)).toBeUndefined();
  });
  it("DATE/DATE_RANGE: ISO string or {start}", () => {
    expect(prepareAiValue("2026-01-01", "DATE", selectMap)).toBe("2026-01-01");
    expect(prepareAiValue({ start: "2026-01-01" }, "DATE_RANGE", selectMap)).toEqual({ start: "2026-01-01" });
    expect(prepareAiValue(123, "DATE", selectMap)).toBeUndefined();
  });
  it("PERSON: array or single id; TEXT/RICH_TEXT: string (number → string)", () => {
    expect(prepareAiValue(["u1", "u2"], "PERSON", selectMap)).toEqual(["u1", "u2"]);
    expect(prepareAiValue("u1", "PERSON", selectMap)).toEqual(["u1"]);
    expect(prepareAiValue("hi", "TEXT", selectMap)).toBe("hi");
    expect(prepareAiValue(7, "RICH_TEXT", selectMap)).toBe("7");
  });
  it("empty/null → undefined (the cell is omitted)", () => {
    expect(prepareAiValue("", "TEXT", selectMap)).toBeUndefined();
    expect(prepareAiValue(null, "NUMBER", selectMap)).toBeUndefined();
    expect(prepareAiValue(undefined, "CHECKBOX", selectMap)).toBeUndefined();
  });
});

// ---------------------------------------------------------------------------
// parseMarkdownTable + mapColumnsToProperties
// ---------------------------------------------------------------------------
describe("parseMarkdownTable (deterministic parser)", () => {
  it("parses a standard table (header + separator + rows)", () => {
    const t = parseMarkdownTable("| Name | Due |\n| --- | --- |\n| a | 2026-01-01 |\n| b | 2026-01-02 |");
    expect(t.isTable).toBe(true);
    expect(t.columns).toEqual(["Name", "Due"]);
    expect(t.rows).toEqual([
      { Name: "a", Due: "2026-01-01" },
      { Name: "b", Due: "2026-01-02" },
    ]);
  });

  it("handles escaped pipes (\\|) inside a cell", () => {
    const t = parseMarkdownTable("| A |\n| --- |\n| x\\|y |");
    expect(t.columns).toEqual(["A"]);
    expect(t.rows[0]!.A).toBe("x|y");
  });

  it("fills empty header cells with a synthetic column_i name", () => {
    const t = parseMarkdownTable("|  | Name |\n| --- | --- |\n| 1 | a |");
    expect(t.columns).toEqual(["column_1", "Name"]);
    expect(t.rows[0]!.column_1).toBe("1");
  });

  it("returns a non-table for prose / a single row / no pipes", () => {
    expect(parseMarkdownTable("just some words").isTable).toBe(false);
    expect(parseMarkdownTable("| only one |").isTable).toBe(false);
    expect(parseMarkdownTable("").isTable).toBe(false);
  });

  it("skips the separator row and aligns missing cells to ''", () => {
    const t = parseMarkdownTable("| A | B |\n| --- | --- |\n| only-a |");
    expect(t.columns).toEqual(["A", "B"]);
    expect(t.rows).toEqual([{ A: "only-a", B: "" }]);
  });
});

describe("mapColumnsToProperties (name match)", () => {
  it("matches case-insensitively (whitespace-normalized) and reports ignored columns", () => {
    const { mapped, ignored } = mapColumnsToProperties(
      ["name", " Due ", "Notes"],
      [
        { id: "p1", name: "Name" },
        { id: "p2", name: "Due" },
      ],
    );
    expect(mapped).toEqual([
      { column: "name", propertyId: "p1" },
      { column: " Due ", propertyId: "p2" },
    ]);
    expect(ignored).toEqual(["Notes"]);
  });
});

describe("previewExtractMapping (the dry-run mapping)", () => {
  it("combines parse + map + rowCount (no write)", () => {
    const m = previewExtractMapping("| Name | Unknown |\n| --- | --- |\n| a | z |", PROPS);
    expect(m.isTable).toBe(true);
    expect(m.rowCount).toBe(1);
    expect(m.columns).toEqual(["Name", "Unknown"]);
    expect(m.mapped).toEqual([{ column: "Name", propertyId: "prop_name" }]);
    expect(m.ignored).toEqual(["Unknown"]);
  });
});

// ---------------------------------------------------------------------------
// tool specs + allowlist exhaustiveness + prompt framing
// ---------------------------------------------------------------------------
describe("tool specs + allowlist", () => {
  it("the three tool specs pass the @factory/ai STRICT tool validator", () => {
    // validateTools returns {ok, issues} for each spec — none may fail.
    for (const tool of [CREATE_DATABASE_TOOL, INSERT_RECORDS_TOOL, GENERATE_FORMULA_TOOL]) {
      const v = validateTools([tool as never]);
      expect(v.ok).toBe(true);
    }
  });

  it("the allowlist is exhaustive: GENERATABLE ∪ NON_GENERATABLE = the 24 real types, disjoint", () => {
    const gen = new Set(GENERATABLE_PROPERTY_TYPES as readonly string[]);
    const nonGen = new Set(Object.keys(NON_GENERATABLE_PROPERTY_TYPES));
    const all = new Set(PROPERTY_TYPES as readonly string[]);
    expect(all.size).toBe(24);
    // Every real type is either generatable or in the reject table.
    for (const t of all) expect(gen.has(t) || nonGen.has(t)).toBe(true);
    // Disjoint.
    for (const t of gen) expect(nonGen.has(t)).toBe(false);
    // No phantom types in either set.
    for (const t of gen) expect(all.has(t)).toBe(true);
    for (const t of nonGen) expect(all.has(t)).toBe(true);
    // The spec's `extraction` is NOT a PropertyType (recorded conflict).
    expect((PROPERTY_TYPES as readonly string[]).includes("EXTRACTION")).toBe(false);
  });

  it("normalizePropertyType: exact enum, synonyms, and null for unknown", () => {
    expect(normalizePropertyType("SELECT")).toBe("SELECT");
    expect(normalizePropertyType("multiSelect")).toBe("MULTI_SELECT");
    expect(normalizePropertyType("date range")).toBe("DATE_RANGE");
    expect(normalizePropertyType("bogus-type")).toBeNull();
    expect(normalizePropertyType(42)).toBeNull();
  });

  it("optionIdForName is deterministic + slug-safe", () => {
    expect(optionIdForName("To Do")).toBe(optionIdForName("to-do"));
    expect(optionIdForName("Àccent")).toBe(optionIdForName("accent"));
    expect(optionIdForName("  ")).toMatch(/^opt-\d+$/);
  });

  it("the system prompts frame the pasted/described text as UNTRUSTED + pin the tool contract", () => {
    expect(DB_GEN_SYSTEM_PROMPT).toMatch(/EXACTLY ONCE/i);
    expect(DB_GEN_SYSTEM_PROMPT).toMatch(/untrusted/i);
    expect(renderGeneratePrompt("a db")).toContain("a db");
  });

  it("renderExtractPrompt lists [PROP <id>] lines + wraps the text in <<<PASTE…PASTE>>>", () => {
    const p = renderExtractPrompt({ databaseTitle: "T", properties: PROPS.filter((x) => !x.isTitle), text: "hello" });
    expect(p).toContain("[PROP prop_name] Name (TEXT)");
    expect(p).toContain("<<<PASTE\nhello\nPASTE>>>");
    // The TITLE property is omitted from the [PROP] list (records key title separately).
    expect(p).not.toContain("prop_title");
  });

  it("renderFormulaPrompt lists [PROP <name>] with LOWERCASE engine types", () => {
    const p = renderFormulaPrompt({ properties: [{ name: "State", type: "SELECT" }, { name: "Due", type: "DATE" }], description: "is done?" });
    expect(p).toContain('[PROP State] select');
    expect(p).toContain('[PROP Due] date');
    expect(p).toContain("is done?");
  });
});

// ---------------------------------------------------------------------------
// RETRY-ONCE contract (service level, stubbed gateway + domain services)
// ---------------------------------------------------------------------------
describe("retry-once contract (DatabaseAiService)", () => {
  function makeRes() {
    const written: string[] = [];
    return {
      written,
      writableEnded: false,
      status() { return this as never; },
      setHeader() { return this as never; },
      flushHeaders() { /* noop */ },
      socket: { setNoDelay() {} },
      on() { return this as never; },
      off() { return this as never; },
      write(s: string) { written.push(s); return true; },
      end() { (this as { writableEnded: boolean }).writableEnded = true; return this as never; },
    };
  }

  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  function makeService(gatewayChat: (...a: any[]) => AsyncIterable<unknown>) {
    const prisma = {
      workspace: { findUnique: async () => ({ settings: {}, usageLimits: {}, billingPlan: "free" }) },
      aiConversation: { create: async () => ({ id: "conv1" }) },
      aiMessage: { create: async () => ({ id: "msg1" }) },
      database: { findFirst: async () => ({ id: "db1", title: "T", workspaceId: WS }) },
      databaseProperty: { findMany: async () => PROPS.map(({ id, name, type, isTitle, config }) => ({ id, name, type, isTitle, config })) },
      formula: { create: async () => ({ id: "f1", propertyId: "prop_f" }) },
    };
    const gateway = {
      resolve: () => ({ chatModel: "mock" }),
      chat: gatewayChat as never,
    };
    const writes = { page: 0, prop: 0, record: 0, formula: 0 };
    const service = new DatabaseAiService(
      prisma as never,
      gateway as never,
      { guard: { check: async () => undefined } } as never,
      { check: async () => ({ allowed: true }) } as never,
      { createProperty: async () => { writes.prop++; return { id: "p_new" }; }, createFormula: async () => { writes.formula++; return { id: "f1", propertyId: "prop_f" }; } } as never,
      { createRecord: async () => { writes.record++; return { id: "r1" }; } } as never,
      { create: async () => { writes.page++; return { id: "pg1", databaseId: "db1", blockIds: [] }; } } as never,
      { audit: async () => undefined } as never,
    );
    return { service, writes };
  }

  it("an invalid createDatabase call → EXACTLY a second chat (with feedback) and NO partial write", async () => {
    const calls: Array<{ user: string }> = [];
    // The mock emits a hostile shape (a non-generatable type) on BOTH calls —
    // the arming persists across the retry, so both attempts fail.
    const hostileArgs = { title: "X", properties: [{ name: "Broken", type: "relation" }], sampleRecords: [] };
    // eslint-disable-next-line @typescript-eslint/no-explicit-any
    const chat = async function* (params: any) {
      const user = [...params.messages].reverse().find((m: { role: string; content: string }) => m.role === "user")!.content;
      calls.push({ user });
      yield { toolCallDelta: { id: "t0", name: "createDatabase", arguments: hostileArgs }, done: false };
      yield { usage: { in: 1, out: 1, total: 2 }, finishReason: "tool_calls", done: true };
    };
    const { service, writes } = makeService(chat);
    const res = makeRes();
    let threw: unknown;
    await service.generate(WS, USER, { prompt: "make a db" }, { auth: { userId: USER } } as never, res as never).catch((e) => (threw = e));
    // generate() ends the stream with error+done rather than throwing for an
    // invalid structure; assert the retry happened and nothing was written.
    expect(calls.length).toBe(2); // first attempt + the retry-once
    expect(calls[0]!.user).not.toContain("INVALID");
    expect(calls[1]!.user).toContain("INVALID"); // the feedback was appended
    expect(writes.page).toBe(0);
    expect(writes.prop).toBe(0);
    expect(writes.record).toBe(0);
    // The stream carries the terminal error + done frames.
    expect(res.written.some((s) => s.includes("ai_invalid_structure"))).toBe(true);
    expect(res.written.some((s) => s.includes('"reason":"error"'))).toBe(true);
    void threw;
  });

  it("a valid createDatabase call → ONE chat, and the writes happen", async () => {
    const goodArgs = {
      title: "Tasks",
      properties: [{ name: "Due", type: "date" }],
      sampleRecords: [{ properties: { Title: "Row", Due: "2026-01-01" } }],
    };
    const calls = 0;
    const chat = async function* () {
      calls + 0;
      yield { toolCallDelta: { id: "t0", name: "createDatabase", arguments: goodArgs }, done: false };
      yield { usage: { in: 1, out: 1, total: 2 }, finishReason: "tool_calls", done: true };
    };
    const { service, writes } = makeService(chat);
    const res = makeRes();
    await service.generate(WS, USER, { prompt: "make a db" }, { auth: { userId: USER } } as never, res as never);
    expect(writes.page).toBe(1);
    expect(writes.prop).toBe(1); // the generated "Due" (Title/Status are seeded by PageService, not re-created)
    expect(writes.record).toBe(1);
    expect(res.written.some((s) => s.includes('"reason":"ok"'))).toBe(true);
    expect(res.written.some((s) => s.includes("db1"))).toBe(true);
  });
});