/**
 * TASK-0124 — Database AI: shared, deterministic schemas + helpers.
 *
 * Single source of truth for the three database-generation tool calls
 * (`createDatabase`, `insertRecords`, `generateFormula`) — the SAME zod
 * shapes the LLM is told to emit AND the server validates against BEFORE any
 * write (spec §Security: "validated by zod before use"). Importable by the
 * API service, the MockProvider (deterministic CI mode), and the unit tests.
 *
 * Zero runtime deps beyond `zod` (the package's one allowed dep). No app
 * imports here — this is domain-schema code, not a NestJS module.
 */
import { z } from "zod";

/**
 * The 24 `PropertyType` values — a MIRROR of the Prisma `enum PropertyType`
 * (`prisma/schema.prisma`) and `apps/api/src/constants.ts` `PROPERTY_TYPES`.
 *
 * NOTE (recorded, spec-vs-reality): the TASK-0124 spec says "the 22
 * `PropertyType` values" and names an `extraction` type. The actual enum has
 * **24** values and has no `EXTRACTION` member. We mirror the real enum (the
 * architecture is the source of truth — AGENTS.md §29) and express the spec's
 * generated-property allowlist as "all real types minus the ones that cannot
 * be created standalone" (see {@link NON_GENERATABLE_PROPERTY_TYPES}).
 */
export const PROPERTY_TYPES = [
  "TITLE",
  "TEXT",
  "RICH_TEXT",
  "NUMBER",
  "SELECT",
  "MULTI_SELECT",
  "STATUS",
  "DATE",
  "DATE_RANGE",
  "PERSON",
  "EMAIL",
  "PHONE",
  "URL",
  "CHECKBOX",
  "FILES",
  "CREATED_TIME",
  "CREATED_BY",
  "LAST_EDITED_TIME",
  "LAST_EDITED_BY",
  "RELATION",
  "ROLLUP",
  "FORMULA",
  "BUTTON",
  "UNIQUE_ID",
] as const;
export type PropertyType = (typeof PROPERTY_TYPES)[number];

/** True when `v` is one of the 24 enum values. */
export function isPropertyType(v: unknown): v is PropertyType {
  return typeof v === "string" && (PROPERTY_TYPES as readonly string[]).includes(v);
}

/**
 * The generated-property allowlist: the types the model may CREATE standalone
 * in a generated database. Every other type is REJECTED (with a reason) before
 * any write. The complement of {@link GENERATABLE_PROPERTY_TYPES} is
 * {@link NON_GENERATABLE_PROPERTY_TYPES} — the spec's "relation/rollup/
 * button/formula/extraction not allowed in generated properties" plus `TITLE`
 * (reserved: a database seeds exactly one title property, and creating a
 * second throws `title_exists`), which the spec omits but the schema forbids.
 *
 * `EXTRACTION` is intentionally absent: it is not a `PropertyType` value.
 */
export const GENERATABLE_PROPERTY_TYPES: readonly PropertyType[] = [
  "TEXT",
  "RICH_TEXT",
  "NUMBER",
  "SELECT",
  "MULTI_SELECT",
  "STATUS",
  "DATE",
  "DATE_RANGE",
  "PERSON",
  "EMAIL",
  "PHONE",
  "URL",
  "CHECKBOX",
  "FILES",
  "CREATED_TIME",
  "CREATED_BY",
  "LAST_EDITED_TIME",
  "LAST_EDITED_BY",
  "UNIQUE_ID",
];

/** The reject table — every non-generatable type with its reason. */
export const NON_GENERATABLE_PROPERTY_TYPES: Record<string, string> = {
  TITLE: "reserved: a database seeds exactly one title property (use the `title` field)",
  RELATION: "requires a target database (create a relation separately)",
  ROLLUP: "requires a relation (create a relation separately)",
  FORMULA: "generated by the dedicated formula tool, not database generation",
  BUTTON: "requires view-action config",
};

export function isGeneratablePropertyType(v: PropertyType): boolean {
  return (GENERATABLE_PROPERTY_TYPES as readonly string[]).includes(v);
}

/**
 * LLM-vocabulary → PropertyType normalization. The model is instructed to emit
 * one of the enum values but may also emit common synonyms ("multiSelect",
 * "date range", "checkbox", "boolean", …). Case/punctuation-insensitive.
 * Returns `null` for anything not mappable (the property is then rejected).
 */
const TYPE_ALIASES: Record<string, PropertyType> = {
  // exact enum values (lowercased)
  title: "TITLE",
  text: "TEXT",
  rich_text: "RICH_TEXT",
  richtext: "RICH_TEXT",
  "rich text": "RICH_TEXT",
  number: "NUMBER",
  numeric: "NUMBER",
  integer: "NUMBER",
  float: "NUMBER",
  select: "SELECT",
  "single select": "SELECT",
  "single-select": "SELECT",
  dropdown: "SELECT",
  multi_select: "MULTI_SELECT",
  multiselect: "MULTI_SELECT",
  "multi select": "MULTI_SELECT",
  "multi-select": "MULTI_SELECT",
  tags: "MULTI_SELECT",
  status: "STATUS",
  date: "DATE",
  date_range: "DATE_RANGE",
  daterange: "DATE_RANGE",
  "date range": "DATE_RANGE",
  person: "PERSON",
  user: "PERSON",
  assignee: "PERSON",
  email: "EMAIL",
  phone: "PHONE",
  url: "URL",
  link: "URL",
  web_link: "URL",
  "web link": "URL",
  checkbox: "CHECKBOX",
  check: "CHECKBOX",
  boolean: "CHECKBOX",
  bool: "CHECKBOX",
  yes_no: "CHECKBOX",
  files: "FILES",
  file: "FILES",
  attachment: "FILES",
  attachments: "FILES",
  created_time: "CREATED_TIME",
  "created time": "CREATED_TIME",
  created: "CREATED_TIME",
  created_by: "CREATED_BY",
  "created by": "CREATED_BY",
  last_edited_time: "LAST_EDITED_TIME",
  "last edited time": "LAST_EDITED_TIME",
  last_edited_by: "LAST_EDITED_BY",
  "last edited by": "LAST_EDITED_BY",
  relation: "RELATION",
  "many to many": "RELATION",
  rollup: "ROLLUP",
  formula: "FORMULA",
  computed: "FORMULA",
  button: "BUTTON",
  action: "BUTTON",
  unique_id: "UNIQUE_ID",
  "unique id": "UNIQUE_ID",
  id: "UNIQUE_ID",
};

/** Normalize a raw LLM `type` string to a PropertyType, or `null`. */
export function normalizePropertyType(raw: unknown): PropertyType | null {
  if (typeof raw !== "string") return null;
  const key = raw.trim().toLowerCase().replace(/\s+/g, " ").replace(/[\s-]+/g, "_").replace(/_/g, "_");
  // Try the exact (lowercased) key, then the spaced form, then the enum name.
  const candidates = [key, key.replace(/_/g, " "), raw.trim().toLowerCase()];
  for (const c of candidates) {
    const hit = TYPE_ALIASES[c];
    if (hit) return hit;
  }
  if (isPropertyType(raw)) return raw;
  return null;
}

// ---------------------------------------------------------------------------
// Tool-call schemas (the LLM emits these; the server validates them pre-write)
// ---------------------------------------------------------------------------

/** One generated property. `options` is required for select/multiSelect/status. */
export const GeneratedPropertySchema = z.object({
  name: z.string().min(1).max(100),
  /** The model's raw type token — normalized via {@link normalizePropertyType}. */
  type: z.string().min(1),
  /** For select / multi_select / status: the option names (+ optional color). */
  options: z
    .array(
      z.object({
        name: z.string().min(1).max(50),
        color: z.string().max(20).optional(),
      }),
    )
    .optional(),
  required: z.boolean().optional(),
});
export type GeneratedProperty = z.infer<typeof GeneratedPropertySchema>;

/** One sample record (≤10). `properties` is keyed by property NAME (the
 *  properties are created in the same call, so no ids exist yet). */
export const GeneratedSampleRecordSchema = z.object({
  properties: z.record(z.string(), z.unknown()),
});
export type GeneratedSampleRecord = z.infer<typeof GeneratedSampleRecordSchema>;

/**
 * The `createDatabase` tool call (generate). Validated before ANY write.
 * Limits: ≤30 properties, ≤10 sample records (spec).
 */
export const CreateDatabaseSchema = z.object({
  title: z.string().min(1).max(200),
  description: z.string().max(500).optional(),
  properties: z.array(GeneratedPropertySchema).min(1).max(30),
  sampleRecords: z.array(GeneratedSampleRecordSchema).max(10).optional(),
});
export type CreateDatabaseArgs = z.infer<typeof CreateDatabaseSchema>;

/** One extracted record. `properties` is keyed by property ID (the database +
 *  its properties already exist). ≤200 records per call (spec). */
export const InsertedRecordSchema = z.object({
  properties: z.record(z.string(), z.unknown()),
});
export type InsertedRecord = z.infer<typeof InsertedRecordSchema>;

/** The `insertRecords` tool call (extract). Validated before ANY write. */
export const InsertRecordsSchema = z.object({
  records: z.array(InsertedRecordSchema).min(1).max(200),
  /** Column names the model could not map to a property (informational). */
  ignoredColumns: z.array(z.string()).optional(),
});
export type InsertRecordsArgs = z.infer<typeof InsertRecordsSchema>;

/** The `generateFormula` tool call. Validated by the formula engine BEFORE
 *  saving (spec: never store an invalid expression). */
export const GenerateFormulaSchema = z.object({
  expression: z.string().min(1).max(2000),
  explanation: z.string().max(500).optional(),
});
export type GenerateFormulaArgs = z.infer<typeof GenerateFormulaSchema>;

/**
 * RE-EXPORT — `optionIdForName` (a deterministic, stable option `id` from an
 * option NAME: select/status/multi-select options carry a string `id` and the
 * engine's `values` registry resolves a stored `optionId` against
 * `config.options[].id`; the AI path generates these ids so it can map the
 * model's option NAMES → the stored option IDs without a round-trip) and
 * `normalizeSelectOptions` MOVED to `@factory/shared` (`dto/database.dto.ts`)
 * so the web read boundary (which does not depend on `@factory/ai`) shares
 * the EXACT same generator the server uses. Import from `@factory/shared`
 * for new code; this alias keeps the existing `@factory/ai` import sites
 * (the API, this package's tests) working unchanged.
 */
export { optionIdForName, normalizeSelectOptions } from "@factory/shared";

/** The property-type the engine's `propertyTypes` map wants (lowercase). */
export function lowerPropertyType(t: string): string {
  return t.toLowerCase();
}

// ---------------------------------------------------------------------------
// Limits (single source for the prompts + the service gates)
// ---------------------------------------------------------------------------
export const DB_GEN_MAX_PROPERTIES = 30;
export const DB_GEN_MAX_SAMPLE_RECORDS = 10;
export const DB_EXTRACT_MAX_RECORDS = 200;
export const DB_EXTRACT_MAX_TEXT_CHARS = 50_000;
export const DB_FORMULA_MAX_SAMPLE_ROWS = 5;

// ---------------------------------------------------------------------------
// Markdown table parser (deterministic — the spec's extract path + unit tests)
// ---------------------------------------------------------------------------

/**
 * Parse a markdown table into `{ columns, rows }`.
 *
 * Handles: leading/trailing pipes, escaped pipes (`\|`), a missing header row
 * (a table whose first line is a data row — columns are then `column_1..n`),
 * and the `---` separator row (skipped). A non-table (no `|`, or a single
 * row with no separator) returns `{ isTable: false, columns: [], rows: [] }`.
 *
 * A line is a table row iff it contains at least one unescaped `|`. The FIRST
 * such line is the header; a following `---…` line (only `-`, `:`, spaces) is
 * the separator and is skipped; the rest are data rows.
 */
export interface ParsedMarkdownTable {
  isTable: boolean;
  columns: string[];
  /** Each row is `column → cell` (header-aligned; missing cells → ""). */
  rows: Array<Record<string, string>>;
}

function splitCells(line: string): string[] {
  // Strip a leading/trailing pipe, then split on unescaped pipes.
  let s = line.trim();
  if (s.startsWith("|")) s = s.slice(1);
  if (s.endsWith("|")) s = s.slice(0, -1);
  // Un-escape `\|` to a sentinel, split, then restore.
  const SENTINEL = "\u0000PIPE\u0000";
  s = s.replace(/\\\|/g, SENTINEL);
  const cells = s.split("|").map((c) => c.replace(new RegExp(SENTINEL, "g"), "|").trim());
  return cells;
}

/** A separator cell: only `-`, `:` (and alignment colons), e.g. `---`, `:---:`,
 *  `-`. Empty cells are allowed (trailing alignment). */
function isSeparatorCell(c: string): boolean {
  const t = c.trim();
  return t === "" || /^:?-+:?$/.test(t);
}

/** A markdown separator row: every cell is a dash/colon alignment marker. */
function isSeparatorRow(line: string): boolean {
  const s = line.trim();
  if (s === "") return false;
  const cells = splitCells(s);
  if (cells.length === 0) return false;
  const nonEmpty = cells.filter((c) => c.trim() !== "");
  if (nonEmpty.length === 0) return false; // all-empty row is not a separator
  return cells.every(isSeparatorCell);
}

export function parseMarkdownTable(text: string): ParsedMarkdownTable {
  const lines = text.split(/\r?\n/).filter((l) => l.trim() !== "");
  // Table rows contain an unescaped pipe (or the un-escaped sentinel, which
  // cannot appear in raw text). Prose lines are ignored.
  const rowLines = lines.filter((l) => l.includes("|"));
  const hasSeparator = rowLines.some((l) => isSeparatorRow(l));
  // A table needs a header + at least one data row, OR a header + separator.
  // (A lone header line, or prose, is not a table.)
  if (rowLines.length < 2) return { isTable: false, columns: [], rows: [] };
  const isTable = hasSeparator || rowLines.length >= 2;
  if (!isTable) return { isTable: false, columns: [], rows: [] };

  const header: string[] = [];
  const dataLines: string[] = [];
  for (const line of rowLines) {
    if (isSeparatorRow(line)) continue;
    if (header.length === 0 && dataLines.length === 0) {
      // First non-separator row = header.
      header.push(...splitCells(line));
    } else {
      dataLines.push(line);
    }
  }
  if (header.length === 0) return { isTable: false, columns: [], rows: [] };

  // Header cells that are empty (a "missing header" column) get a synthetic
  // `column_i` name so the mapping step can still address them (they simply
  // never match a property → reported as `ignored`).
  const columns = header.map((h, i) => (h === "" ? `column_${i + 1}` : h));

  const rows: Array<Record<string, string>> = dataLines.map((line) => {
    const cells = splitCells(line);
    const row: Record<string, string> = {};
    columns.forEach((col, i) => {
      row[col] = cells[i] ?? "";
    });
    return row;
  });

  return { isTable: true, columns, rows };
}

/**
 * Map markdown COLUMNS to existing properties BY NAME (case-insensitive,
 * whitespace-normalized). Returns the matched `{column, propertyId}` pairs and
 * the `ignored` columns (no property with that name). Deterministic — the
 * server uses it for `ignoredColumns` in the response, the mock uses it to
 * build its `insertRecords` records.
 */
export function mapColumnsToProperties(
  columns: string[],
  properties: Array<{ id: string; name: string }>,
): { mapped: Array<{ column: string; propertyId: string }>; ignored: string[] } {
  const byName = new Map<string, string>();
  for (const p of properties) byName.set(normalizeName(p.name), p.id);
  const mapped: Array<{ column: string; propertyId: string }> = [];
  const ignored: string[] = [];
  for (const col of columns) {
    const id = byName.get(normalizeName(col));
    if (id) mapped.push({ column: col, propertyId: id });
    else ignored.push(col);
  }
  return { mapped, ignored };
}

function normalizeName(s: string): string {
  return s.trim().toLowerCase().replace(/\s+/g, " ");
}