/**
 * @factory/shared — database DTOs (PRODUCT-SPEC §9; prisma `Database*`).
 *
 * NOTE: the spec header says "22 property types" but both the spec list and
 * the prisma `PropertyType` enum define 24 (Title … Unique ID). We implement
 * the full enum; see the recorded conflict in the package README.
 */

import { z } from "zod";

import { LIMITS } from "../constants.js";

// --- property types ------------------------------------------------------------

/** All property types (lowercase snake_case; prisma `PropertyType`). */
export const propertyTypeSchema = z.enum([
  "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",
]);
export type PropertyType = z.infer<typeof propertyTypeSchema>;

export const selectOptionSchema = z
  .object({
    id: z.string().min(1),
    name: z.string().min(1).max(100),
    color: z
      .string()
      .regex(/^(default|gray|brown|orange|yellow|green|blue|purple|pink|red)(_bg)?$/),
    position: z.string().min(1).optional(),
  })
  .strict();
export type SelectOption = z.infer<typeof selectOptionSchema>;

const optionsConfig = z.object({
  options: z.array(selectOptionSchema).max(100),
});

/**
 * The option-id wire contract for select-family properties (SELECT /
 * MULTI_SELECT / STATUS — docs/api/databases.md): options are
 * `{ id, name, color }` and stored values reference the option via
 * `{ optionId: <option.id> }`.
 *
 * `optionIdForName` is the ONE deterministic id generator (id-less options
 * get it on write — `normalizeSelectOptions` — and on every read boundary,
 * server AND client), so a stored NAME and its canonical id always resolve
 * to the same option no matter which side saw the raw config first.
 *
 * Moved from `@factory/ai` (TASK-0124) into this package so the WEB read
 * boundary can share the exact same generator (the web does not depend on
 * `@factory/ai`). `@factory/ai` re-exports both names unchanged — existing
 * import sites keep working.
 */

/**
 * Deterministic, slug-safe option id derived from an option NAME
 * (e.g. "In Progress" → `opt-in-progress`). NFKD-strips diacritics and
 * non-alphanumerics; falls back to a length-derived id for names with no
 * surviving slug (e.g. all-whitespace). Pure (unit-tested in
 * `database.dto.test.ts`).
 */
export function optionIdForName(name: string): string {
  const slug = name
    .trim()
    .toLowerCase()
    .normalize("NFKD")
    .replace(/[\u0300-\u036f]/g, "")
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/^-+|-+$/g, "");
  return slug ? `opt-${slug}` : `opt-${name.length}`;
}

/**
 * Backfill a DETERMINISTIC id on any select/status/multi-select option that
 * lacks one (the AI generation path historically emitted `config.options` as
 * bare `{ name, color }`, which broke every id-based reader: empty chips,
 * broken preselect, `optionId: undefined` commits).
 *
 * `existingOptions` (the option list already persisted for the property)
 * reuses an option's id by NAME when an incoming patch re-lists that option
 * without its id — INCLUDING a custom (non-deterministic) id — so existing
 * `{ optionId }` values stay resolvable; a genuinely new option gets the
 * deterministic `optionIdForName(name)` id.
 *
 * Idempotent: an already-conformant config is returned BY REFERENCE
 * unchanged (callers may rely on referential stability). Pure (unit-tested).
 */
export function normalizeSelectOptions(config: unknown, existingOptions?: unknown): unknown {
  const cfg = (config ?? {}) as Record<string, unknown>;
  const options = cfg.options;
  if (!Array.isArray(options)) return config;
  const byName = new Map<string, string>();
  if (Array.isArray(existingOptions)) {
    for (const o of existingOptions) {
      const e = o as { id?: unknown; name?: unknown } | null;
      if (e && typeof e.name === "string" && typeof e.id === "string" && e.id) {
        byName.set(e.name, e.id);
      }
    }
  }
  let changed = false;
  const next = options.map((o) => {
    const opt = o as { id?: string; name?: string };
    if (opt && typeof opt.name === "string" && (!opt.id || typeof opt.id !== "string" || opt.id.length === 0)) {
      const id = byName.get(opt.name) ?? optionIdForName(opt.name);
      changed = true;
      return { ...opt, id };
    }
    return o;
  });
  return changed ? { ...cfg, options: next } : config;
}

const numberFormatSchema = z
  .string()
  .regex(/^(number|integer|decimal|percent|dollar|euro|pound|yen|cny|inr)$/);

/**
 * Per-type property `config` (stored in `database_properties.config`).
 * Types without settings use an empty object.
 */
export const propertyConfigSchema = z.discriminatedUnion("type", [
  z.object({ type: z.literal("title"), config: z.object({}).strict() }),
  z.object({ type: z.literal("text"), config: z.object({}).strict() }),
  z.object({ type: z.literal("rich_text"), config: z.object({}).strict() }),
  z.object({
    type: z.literal("number"),
    config: z
      .object({
        format: numberFormatSchema.default("number"),
        decimals: z.number().int().min(0).max(8).optional(),
        prefix: z.string().max(16).optional(),
        suffix: z.string().max(16).optional(),
      })
      .strict(),
  }),
  z.object({ type: z.literal("select"), config: optionsConfig }),
  z.object({ type: z.literal("multi_select"), config: optionsConfig }),
  z.object({ type: z.literal("status"), config: optionsConfig }),
  z.object({
    type: z.literal("date"),
    config: z
      .object({
        format: z
          .string()
          .regex(/^(yyyy-mm-dd|yyyy\/mm\/dd|mm\/dd\/yyyy|dd\/mm\/yyyy|dd-mm-yyyy)$/),
        timeEnabled: z.boolean().default(false),
      })
      .strict(),
  }),
  z.object({
    type: z.literal("date_range"),
    config: z
      .object({
        format: z
          .string()
          .regex(/^(yyyy-mm-dd|yyyy\/mm\/dd|mm\/dd\/yyyy|dd\/mm\/yyyy|dd-mm-yyyy)$/),
      })
      .strict(),
  }),
  z.object({
    type: z.literal("person"),
    config: z
      .object({
        allowMultiple: z.boolean().default(true),
      })
      .strict(),
  }),
  z.object({ type: z.literal("email"), config: z.object({}).strict() }),
  z.object({ type: z.literal("phone"), config: z.object({}).strict() }),
  z.object({ type: z.literal("url"), config: z.object({}).strict() }),
  z.object({ type: z.literal("checkbox"), config: z.object({}).strict() }),
  z.object({
    type: z.literal("files"),
    config: z
      .object({
        allowMultiple: z.boolean().default(true),
      })
      .strict(),
  }),
  z.object({ type: z.literal("created_time"), config: z.object({}).strict() }),
  z.object({ type: z.literal("created_by"), config: z.object({}).strict() }),
  z.object({ type: z.literal("last_edited_time"), config: z.object({}).strict() }),
  z.object({ type: z.literal("last_edited_by"), config: z.object({}).strict() }),
  z.object({
    type: z.literal("relation"),
    config: z
      .object({
        relationId: z.string().min(1),
      })
      .strict(),
  }),
  z.object({
    type: z.literal("rollup"),
    config: z
      .object({
        /** Relation property used to reach the linked records. */
        relationPropertyId: z.string().min(1),
        /** Property on the linked records to aggregate. */
        targetPropertyId: z.string().min(1),
        aggregate: z.enum(["count", "sum", "average", "min", "max", "percent", "unique"]),
      })
      .strict(),
  }),
  z.object({
    type: z.literal("formula"),
    config: z
      .object({
        /** Safe expression source (evaluated by the AST engine, never JS). */
        expression: z.string().min(1).max(2000),
      })
      .strict(),
  }),
  z.object({
    type: z.literal("button"),
    config: z
      .object({
        label: z.string().min(1).max(64).optional(),
        actions: z
          .array(
            z
              .object({
                type: z.enum([
                  "set_property",
                  "create_page",
                  "update_page",
                  "archive_page",
                  "duplicate_page",
                  "send_webhook",
                  "trigger_automation",
                ]),
                config: z.record(z.unknown()),
              })
              .strict(),
          )
          .min(1)
          .max(10),
      })
      .strict(),
  }),
  z.object({
    type: z.literal("unique_id"),
    config: z
      .object({
        format: z.enum(["number", "uuid"]).default("number"),
        prefix: z.string().max(16).optional(),
      })
      .strict(),
  }),
]);
export type PropertyConfig = z.infer<typeof propertyConfigSchema>;

/** A database property definition (schema column). */
export const databasePropertySchema = z
  .object({
    id: z.string().min(1).optional(),
    name: z.string().min(1).max(100),
    type: propertyTypeSchema,
    /** Positional order of the column (fractional). */
    position: z.string().min(1).optional(),
    isTitle: z.boolean().default(false),
    required: z.boolean().default(false),
    config: z.record(z.unknown()).default({}),
    default: z.unknown().optional(),
  })
  .strict()
  .superRefine((val, ctx) => {
    const result = propertyConfigSchema.safeParse({ type: val.type, config: val.config });
    if (!result.success) {
      for (const issue of result.error.issues) {
        ctx.addIssue({
          code: z.ZodIssueCode.custom,
          message: `invalid config for property type "${val.type}": ${issue.message}`,
          path: ["config", ...issue.path],
        });
      }
    }
  });
export type DatabaseProperty = z.infer<typeof databasePropertySchema>;

// --- databases -----------------------------------------------------------------

export const createDatabaseSchema = z
  .object({
    title: z.string().min(1).max(200),
    icon: z.string().max(32).optional().nullable(),
    description: z.string().max(500).optional().nullable(),
    /** Initial properties; one must be (or become) the title. */
    properties: z.array(databasePropertySchema).max(100).optional(),
  })
  .strict();
export type CreateDatabase = z.infer<typeof createDatabaseSchema>;

export const createPropertySchema = databasePropertySchema;
export type CreateProperty = z.infer<typeof createPropertySchema>;

// --- views ---------------------------------------------------------------------

/** All 9 view types (prisma `ViewType`). */
export const viewTypeSchema = z.enum([
  "table",
  "list",
  "board",
  "gallery",
  "calendar",
  "timeline",
  "chart",
  "dashboard",
  "form",
]);
export type ViewType = z.infer<typeof viewTypeSchema>;

// --- filters / sorts / groups ---------------------------------------------------

// Leaf operators the TASK-0067 filter engine implements (the per-type matrix in
// docs/tasks/phase-05-databases.md #TASK-0067). The shared union MUST cover the
// full set the engine evaluates — a narrower enum both (a) breaks the engine's
// own `op === "<leaf>"` type-narrowing (TS2367 "no overlap") and (b) makes the
// API's `filterNodeSchema` reject valid filter trees (e.g. a checkbox
// `isChecked` or a file `has_files`) that the engine fully supports.
// (T-187: was 11 ops — the engine grew to the full matrix without the enum
// being extended, so 10 spec'd operators were silently invalid at the boundary.)
export const filterOperatorSchema = z.enum([
  "=",
  "!=",
  "contains",
  "doesNotContain",
  "starts_with",
  "ends_with",
  ">",
  ">=",
  "<",
  "<=",
  "is_empty",
  "is_not_empty",
  "isBetween",
  "isBefore",
  "isAfter",
  "isOnOrAfter",
  "isOnOrBefore",
  "isChecked",
  "isUnchecked",
  "has_files",
  "has_no_files",
]);
export type FilterOperator = z.infer<typeof filterOperatorSchema>;

/**
 * A recursive boolean filter tree (PRODUCT-SPEC §9.3):
 *
 * ```
 * (Status = In Progress OR Status = Review) AND Owner = Current User
 * ```
 *
 * Groups: `{op: "and"|"or"|"not", conditions: FilterNode[]}`.
 * Leaf:   `{propertyId, operator, value?}` — `value` is omitted for
 * `is_empty`/`is_not_empty`.
 */
export type FilterNode =
  | { op: "and" | "or" | "not"; conditions: FilterNode[] }
  | { propertyId: string; operator: FilterOperator; value?: unknown };

export const filterNodeSchema: z.ZodType<FilterNode> = z.lazy(() =>
  z.union([
    z.object({
      op: z.enum(["and", "or", "not"]),
      conditions: z.array(filterNodeSchema).min(1).max(100),
    }),
    z
      .object({
        propertyId: z.string().min(1),
        operator: filterOperatorSchema,
        value: z.unknown().optional(),
      })
      .superRefine((val, ctx) => {
        const isEmptyOp = val.operator === "is_empty" || val.operator === "is_not_empty";
        if (!isEmptyOp && val.value === undefined) {
          ctx.addIssue({
            code: z.ZodIssueCode.custom,
            message: `operator "${val.operator}" requires a value`,
          });
        }
      }),
  ]),
);

/** A single sort spec. */
export const sortSpecSchema = z
  .object({
    propertyId: z.string().min(1),
    direction: z.enum(["asc", "desc"]).default("asc"),
  })
  .strict();
export type SortSpec = z.infer<typeof sortSpecSchema>;

/** A single group spec (board/calendar/timeline group-by). */
export const groupSpecSchema = z
  .object({
    propertyId: z.string().min(1),
    direction: z.enum(["asc", "desc"]).default("asc"),
  })
  .strict();
export type GroupSpec = z.infer<typeof groupSpecSchema>;

// --- records / queries -----------------------------------------------------------

export const createRecordSchema = z
  .object({
    /** Property id or name → value (typed per property; validated in the
     *  database service where property configs are known). */
    properties: z.record(z.string(), z.unknown()),
  })
  .strict();
export type CreateRecord = z.infer<typeof createRecordSchema>;

export const updateRecordSchema = z
  .object({
    properties: z.record(z.string(), z.unknown()),
  })
  .strict();
export type UpdateRecord = z.infer<typeof updateRecordSchema>;

/**
 * A paged, filtered database query (`POST /databases/:id/query`). The engine
 * (packages/database-engine) evaluates filter/sort/group; see the query
 * strategy documented there.
 */
export const recordQuerySchema = z
  .object({
    viewId: z.string().min(1).optional(),
    filter: filterNodeSchema.optional(),
    sort: z.array(sortSpecSchema).max(20).optional(),
    group: z.array(groupSpecSchema).max(5).optional(),
    /** Opaque cursor (record position or id) for the next page. */
    cursor: z.string().min(1).optional(),
    pageSize: z.number().int().min(1).max(LIMITS.MAX_RECORDS_PER_PAGE).default(50),
  })
  .strict();
export type RecordQuery = z.infer<typeof recordQuerySchema>;

// --- views (continued) -------------------------------------------------------------

export const createViewSchema = z
  .object({
    name: z.string().min(1).max(100),
    type: viewTypeSchema,
    filter: filterNodeSchema.optional(),
    sort: z.array(sortSpecSchema).max(20).optional(),
    group: z.array(groupSpecSchema).max(5).optional(),
    /** View config: visiblePropertyIds, columnWidths, cardLayout, color, density… */
    config: z.record(z.unknown()).default({}),
  })
  .strict();
export type CreateView = z.infer<typeof createViewSchema>;