import { and, eq, sql, type SQLWrapper } from "drizzle-orm";
import {
  assertTransactionIdentity,
  isTransactionCapabilityError,
  type Querier,
  type TransactionIdentity,
  type TransactionalDatabase,
} from "@platform-modules/db";
import type {
  Actor,
  EntityRef,
  FieldDefinition,
  FieldGroup,
  FieldValue,
  FieldValueMap,
  MediaRef,
  RelRef,
  RelRefResolver,
  ResolvedFieldGroup,
} from "./model.js";
import { assertCanEditFields, assertCanManageGroups } from "./authz.js";
import { FieldStoreError, FieldValidationError } from "./errors.js";
import {
  assertNoCodeKeyShadow,
  assertNoCrossGroupFieldKeyConflict,
  defineFieldGroup,
  resolveGroups,
  type CodeFieldGroup,
} from "./registry.js";
import { validateValues } from "./validate.js";
import {
  fieldGroups,
  fieldValues,
  type FieldsSchema,
  type FieldsTransaction,
  type FieldStorageValue,
  type FlatFieldValue,
} from "./schema.js";

export type FieldsAdapterKind = "d1" | "postgres";

/** Dialect-neutral flat-corpus reader, backed by the DB callback `execute()` seam. */
export interface FieldsFlatCorpusReader {
  readonly adapter: FieldsAdapterKind;
  execute<Row extends Record<string, unknown> = Record<string, unknown>>(
    query: SQLWrapper
  ): Promise<readonly Row[]>;
}

export type FieldsStoreErrorCode =
  | "invalid-flat-row"
  | "unsupported-adapter"
  | "transaction-capability";

/** Typed structural error for flat reads and composite transaction capability checks. */
export class FieldsStoreContractError extends Error {
  override readonly name = "FieldsStoreContractError";

  constructor(
    readonly code: FieldsStoreErrorCode,
    readonly detail: string,
    readonly field?: string
  ) {
    super(`fields store ${code}: ${detail}`);
  }
}

export function isFieldsStoreContractError(
  error: unknown
): error is FieldsStoreContractError {
  if (typeof error !== "object" || error === null) return false;
  const candidate = error as { code?: unknown; detail?: unknown };
  return (
    (candidate.code === "invalid-flat-row" ||
      candidate.code === "unsupported-adapter" ||
      candidate.code === "transaction-capability") &&
    typeof candidate.detail === "string"
  );
}

function trustedFlatRow(row: unknown): Record<string, unknown> {
  if (
    typeof row !== "object" ||
    row === null ||
    Array.isArray(row) ||
    Object.getPrototypeOf(row) !== Object.prototype ||
    Object.getOwnPropertySymbols(row).length !== 0
  ) {
    throw new FieldsStoreContractError(
      "invalid-flat-row",
      "must be a plain data row",
      "row"
    );
  }
  for (const key of Object.getOwnPropertyNames(row)) {
    const descriptor = Object.getOwnPropertyDescriptor(row, key)!;
    if (!descriptor.enumerable || !("value" in descriptor)) {
      throw new FieldsStoreContractError(
        "invalid-flat-row",
        "must not contain non-enumerable properties or accessors",
        "row"
      );
    }
  }
  return row as Record<string, unknown>;
}

function flatString(
  row: Record<string, unknown>,
  field: string,
  nullable = false
): string | null {
  const value = row[field];
  if (nullable && value === null) return null;
  if (typeof value !== "string") {
    throw new FieldsStoreContractError(
      "invalid-flat-row",
      "must be a string",
      field
    );
  }
  return value;
}

function flatBoolean(
  row: Record<string, unknown>,
  field: string
): boolean | null {
  const value = row[field];
  if (value === null) return null;
  if (typeof value === "boolean") return value;
  // SQLite/D1 returns INTEGER for boolean lanes while Postgres returns boolean.
  if (value === 0 || value === 1) return value === 1;
  throw new FieldsStoreContractError(
    "invalid-flat-row",
    "must be a boolean, D1 0/1, or null",
    field
  );
}

function flatOrdinal(row: Record<string, unknown>): number {
  const value = row.ordinal;
  if (typeof value !== "number" || !Number.isInteger(value) || value < 0) {
    throw new FieldsStoreContractError(
      "invalid-flat-row",
      "must be a non-negative integer",
      "ordinal"
    );
  }
  return value;
}

function flatDate(
  row: Record<string, unknown>,
  field: string,
  nullable = false
): Date | null {
  const value = row[field];
  if (nullable && value === null) return null;
  const date =
    value instanceof Date
      ? new Date(Date.prototype.getTime.call(value))
      : typeof value === "string"
      ? new Date(value)
      : null;
  if (date === null || Number.isNaN(date.getTime())) {
    throw new FieldsStoreContractError(
      "invalid-flat-row",
      "must be an ISO timestamp or Date",
      field
    );
  }
  return date;
}

function freezeStorageValue(
  value: unknown,
  field: string,
  ancestors = new Set<object>()
): FieldStorageValue {
  if (value === null || typeof value === "string" || typeof value === "boolean")
    return value;
  if (typeof value === "number") {
    if (!Number.isFinite(value)) {
      throw new FieldsStoreContractError(
        "invalid-flat-row",
        "must be finite",
        field
      );
    }
    return value;
  }
  if (typeof value !== "object" || value === null || ancestors.has(value)) {
    throw new FieldsStoreContractError(
      "invalid-flat-row",
      "must be an acyclic plain serializable value",
      field
    );
  }
  if (Object.getOwnPropertySymbols(value).length !== 0) {
    throw new FieldsStoreContractError(
      "invalid-flat-row",
      "must not contain symbols",
      field
    );
  }

  ancestors.add(value);
  try {
    if (Array.isArray(value)) {
      const names = Object.getOwnPropertyNames(value);
      if (names.length !== value.length + 1 || !names.includes("length")) {
        throw new FieldsStoreContractError(
          "invalid-flat-row",
          "must be a dense data array",
          field
        );
      }
      const values: FieldStorageValue[] = [];
      for (let index = 0; index < value.length; index += 1) {
        const descriptor = Object.getOwnPropertyDescriptor(value, String(index));
        if (!descriptor?.enumerable || !("value" in descriptor)) {
          throw new FieldsStoreContractError(
            "invalid-flat-row",
            "must not contain accessors",
            field
          );
        }
        values.push(freezeStorageValue(descriptor.value, field, ancestors));
      }
      return Object.freeze(values);
    }
    if (Object.getPrototypeOf(value) !== Object.prototype) {
      throw new FieldsStoreContractError(
        "invalid-flat-row",
        "must be a plain serializable value",
        field
      );
    }
    const output: Record<string, FieldStorageValue> = {};
    for (const key of Object.getOwnPropertyNames(value)) {
      const descriptor = Object.getOwnPropertyDescriptor(value, key)!;
      if (!descriptor.enumerable || !("value" in descriptor)) {
        throw new FieldsStoreContractError(
          "invalid-flat-row",
          "must not contain non-enumerable properties or accessors",
          field
        );
      }
      if (key === "__proto__" || key === "constructor" || key === "prototype") {
        throw new FieldsStoreContractError(
          "invalid-flat-row",
          "contains a forbidden object key",
          field
        );
      }
      output[key] = freezeStorageValue(descriptor.value, field, ancestors);
    }
    return Object.freeze(output);
  } finally {
    ancestors.delete(value);
  }
}

function assertExactlyOneFlatLane(row: Record<string, unknown>): void {
  const lanes = ["valueText", "valueNum", "valueBool", "valueDate", "refId"];
  const reference = row.refId !== null;
  if (lanes.filter((lane) => row[lane] !== null).length !== 1) {
    throw new FieldsStoreContractError(
      "invalid-flat-row",
      "must contain exactly one populated value lane"
    );
  }
  if (reference && typeof row.refType !== "string") {
    throw new FieldsStoreContractError(
      "invalid-flat-row",
      "must be a string when refId is populated",
      "refType"
    );
  }
  if (!reference && (row.refType !== null || row.refMeta !== null)) {
    throw new FieldsStoreContractError(
      "invalid-flat-row",
      "must be null without a reference lane",
      row.refType !== null ? "refType" : "refMeta"
    );
  }
}

/**
 * Reads the current flat EAV corpus without interpreting definitions or
 * converting it to recursive nodes. The output preserves each typed lane for
 * the migration's later count/hash/value comparison.
 */
export async function readFlatFieldValues(
  reader: FieldsFlatCorpusReader
): Promise<readonly FlatFieldValue[]> {
  if (reader.adapter !== "d1" && reader.adapter !== "postgres") {
    throw new FieldsStoreContractError(
      "unsupported-adapter",
      `unknown adapter ${String(reader.adapter)}`
    );
  }

  const rows = await reader.execute(sql`
    SELECT id, entity_type AS "entityType", entity_id AS "entityId",
      group_id AS "groupId", field_key AS "fieldKey", ordinal,
      value_text AS "valueText", value_num AS "valueNum",
      value_bool AS "valueBool", value_date AS "valueDate",
      ref_type AS "refType", ref_id AS "refId", ref_meta AS "refMeta",
      created_at AS "createdAt", updated_at AS "updatedAt"
    FROM field_values
    ORDER BY entity_type ASC, entity_id ASC, group_id ASC, field_key ASC, ordinal ASC
  `);

  return Object.freeze(
    rows.map((candidate) => {
      const row = trustedFlatRow(candidate);
      assertExactlyOneFlatLane(row);
      const valueNum = row.valueNum;
      if (valueNum !== null && (typeof valueNum !== "string" || !/^-?(?:0|[1-9]\d*)(?:\.\d+)?(?:[eE][+-]?\d+)?$/.test(valueNum))) {
        throw new FieldsStoreContractError(
          "invalid-flat-row",
          "must be an exact finite decimal string",
          "valueNum"
        );
      }
      const refMeta =
        typeof row.refMeta === "string"
          ? (() => {
              try {
                return JSON.parse(row.refMeta) as unknown;
              } catch {
                throw new FieldsStoreContractError(
                  "invalid-flat-row",
                  "must be valid JSON when returned as D1 text",
                  "refMeta"
                );
              }
            })()
          : row.refMeta;
      return Object.freeze({
        id: flatString(row, "id")!,
        entityType: flatString(row, "entityType")!,
        entityId: flatString(row, "entityId")!,
        groupId: flatString(row, "groupId")!,
        fieldKey: flatString(row, "fieldKey")!,
        ordinal: flatOrdinal(row),
        valueText: flatString(row, "valueText", true),
        // D1 returns numeric lanes as text while Postgres may return text or number.
        valueNum: valueNum === null ? null : valueNum,
        valueBool: flatBoolean(row, "valueBool"),
        valueDate: flatDate(row, "valueDate", true),
        refType: flatString(row, "refType", true),
        refId: flatString(row, "refId", true),
        refMeta:
          refMeta === null ? null : freezeStorageValue(refMeta, "refMeta"),
        createdAt: flatDate(row, "createdAt")!,
        updatedAt: flatDate(row, "updatedAt")!,
      });
    })
  );
}

/** Require the same callback-minted identity supplied by a parent composite. */
export function assertFieldsTransactionIdentity(
  tx: FieldsTransaction,
  expectedIdentity: TransactionIdentity
): void {
  try {
    assertTransactionIdentity(tx, expectedIdentity);
  } catch (error) {
    if (isTransactionCapabilityError(error)) {
      throw new FieldsStoreContractError(
        "transaction-capability",
        error.reason
      );
    }
    throw error;
  }
}

type ValueRow = typeof fieldValues.$inferSelect;
type ValueInsert = typeof fieldValues.$inferInsert;

function fieldDefByKey(
  groups: ResolvedFieldGroup[]
): Map<string, FieldDefinition> {
  const m = new Map<string, FieldDefinition>();
  for (const g of groups) {
    for (const f of g.fields) m.set(f.key, f);
  }
  return m;
}

function valueToRows(
  ref: EntityRef,
  groupId: string,
  field: FieldDefinition,
  value: FieldValue
): ValueInsert[] {
  const multiple = "multiple" in field && field.multiple === true;
  if (multiple && Array.isArray(value)) {
    return value.map((item, ordinal) =>
      scalarToRow(ref, groupId, field, item, ordinal)
    );
  }
  return [scalarToRow(ref, groupId, field, value as FieldValue, 0)];
}

function scalarToRow(
  ref: EntityRef,
  groupId: string,
  field: FieldDefinition,
  value: FieldValue,
  ordinal: number
): ValueInsert {
  const base = {
    entityType: ref.entityType,
    entityId: ref.entityId,
    groupId,
    fieldKey: field.key,
    ordinal,
  };
  switch (field.type) {
    case "text":
    case "textarea":
    case "select":
    case "color":
    case "url":
    case "email":
      return { ...base, valueText: String(value) };
    case "number":
      return { ...base, valueNum: String(value) };
    case "boolean":
      return { ...base, valueBool: value as boolean };
    case "date": {
      const d = value instanceof Date ? value : new Date(value as string);
      return { ...base, valueDate: d };
    }
    case "media": {
      const m = value as MediaRef;
      const { key, url, mime, meta } = m;
      const refMeta: Record<string, unknown> = {};
      if (url !== undefined) refMeta.url = url;
      if (mime !== undefined) refMeta.mime = mime;
      if (meta !== undefined) refMeta.meta = meta;
      return {
        ...base,
        refType: "media",
        refId: key,
        refMeta: Object.keys(refMeta).length > 0 ? refMeta : null,
      };
    }
    case "relationship": {
      const r = value as RelRef;
      return { ...base, refType: r.entityType, refId: r.entityId };
    }
  }
}

function rowToValue(
  field: FieldDefinition,
  rows: ValueRow[]
): FieldValue | undefined {
  if (rows.length === 0) return undefined;
  const multiple = "multiple" in field && field.multiple === true;
  const sorted = [...rows].sort((a, b) => a.ordinal - b.ordinal);
  if (multiple) return sorted.map((r) => rowScalar(field, r)) as FieldValue;
  return rowScalar(field, sorted[0]!);
}

function rowScalar(field: FieldDefinition, row: ValueRow): FieldValue {
  switch (field.type) {
    case "text":
    case "textarea":
    case "select":
    case "color":
    case "url":
    case "email":
      return row.valueText ?? "";
    case "number":
      return row.valueNum !== null && row.valueNum !== undefined
        ? Number(row.valueNum)
        : 0;
    case "boolean":
      return row.valueBool ?? false;
    case "date":
      return row.valueDate ?? new Date(0);
    case "media": {
      const meta = (row.refMeta ?? {}) as Record<string, unknown>;
      const out: MediaRef = { key: row.refId ?? "" };
      if (typeof meta.url === "string") out.url = meta.url;
      if (typeof meta.mime === "string") out.mime = meta.mime;
      if (
        meta.meta !== undefined &&
        typeof meta.meta === "object" &&
        meta.meta !== null
      ) {
        out.meta = meta.meta as Record<string, unknown>;
      }
      return out;
    }
    case "relationship":
      return { entityType: row.refType ?? "", entityId: row.refId ?? "" };
  }
}

async function assertRefsExist(
  resolved: ResolvedFieldGroup,
  values: FieldValueMap,
  resolveRef?: RelRefResolver
): Promise<void> {
  if (!resolveRef) return;
  for (const f of resolved.fields) {
    if (f.type !== "relationship") continue;
    const v = values[f.key];
    if (v === undefined || v === null) continue;
    const refs = Array.isArray(v) ? v : [v];
    for (const ref of refs) {
      const r = ref as RelRef;
      const ok = await resolveRef(r);
      if (!ok)
        throw new FieldValidationError(f.key, "referenced entity not found");
    }
  }
}

function wrapStoreError(e: unknown): never {
  if (e instanceof FieldValidationError || e instanceof FieldStoreError)
    throw e;
  const detail = e instanceof Error ? e.message : String(e);
  throw new FieldStoreError(detail);
}

export async function getEntityValues(
  db: Querier<FieldsSchema>,
  ref: EntityRef,
  opts: { entityType: string; subType?: string; codeGroups?: CodeFieldGroup[] }
): Promise<FieldValueMap> {
  const groups = await resolveGroups(db, {
    entityType: opts.entityType,
    subType: opts.subType,
    codeGroups: opts.codeGroups,
  });
  const defs = fieldDefByKey(groups);
  const rows = await db
    .select()
    .from(fieldValues)
    .where(
      and(
        eq(fieldValues.entityType, ref.entityType),
        eq(fieldValues.entityId, ref.entityId)
      )
    );

  const byKey = new Map<string, ValueRow[]>();
  for (const row of rows) {
    const list = byKey.get(row.fieldKey) ?? [];
    list.push(row);
    byKey.set(row.fieldKey, list);
  }

  const out: FieldValueMap = {};
  for (const [key, fieldRows] of byKey) {
    const def = defs.get(key);
    if (!def) continue;
    out[key] = rowToValue(def, fieldRows);
  }
  return out;
}

export async function setEntityValues(
  db: TransactionalDatabase<FieldsSchema>,
  actor: Actor,
  input: {
    ref: EntityRef;
    groupId: string;
    values: FieldValueMap;
    resolved: ResolvedFieldGroup;
    resolveRef?: RelRefResolver;
  }
): Promise<void> {
  assertCanEditFields(actor);
  validateValues(input.resolved, input.values);
  await assertRefsExist(input.resolved, input.values, input.resolveRef);

  const rows: ValueInsert[] = [];
  for (const f of input.resolved.fields) {
    const v = input.values[f.key];
    if (v === undefined || v === null) continue;
    rows.push(...valueToRows(input.ref, input.groupId, f, v));
  }

  try {
    await db.transaction(async (tx) => {
      await tx
        .delete(fieldValues)
        .where(
          and(
            eq(fieldValues.entityType, input.ref.entityType),
            eq(fieldValues.entityId, input.ref.entityId),
            eq(fieldValues.groupId, input.groupId)
          )
        );
      if (rows.length > 0) await tx.insert(fieldValues).values(rows);
    });
  } catch (e) {
    wrapStoreError(e);
  }
}

/**
 * Host-cascade op: removes ALL field values for an entity (call on parent-entity delete, spec §8.1).
 * Takes NO `Actor` and runs NO capability check by design — so the HOST MUST gate BOTH capability
 * AND object-level ownership BEFORE calling this. The engine never knows who owns `entityId`
 * (no owner column; spec §5). Never assume the engine authorizes this delete.
 */
export async function deleteEntityValues(
  db: Querier<FieldsSchema>,
  ref: EntityRef
): Promise<void> {
  await db
    .delete(fieldValues)
    .where(
      and(
        eq(fieldValues.entityType, ref.entityType),
        eq(fieldValues.entityId, ref.entityId)
      )
    );
}

export async function createGroup(
  db: Querier<FieldsSchema>,
  actor: Actor,
  group: FieldGroup,
  codeGroups?: CodeFieldGroup[]
): Promise<ResolvedFieldGroup> {
  assertCanManageGroups(actor);
  defineFieldGroup(group);
  assertNoCodeKeyShadow(group.key, group.location.entityType, codeGroups);

  const existing = await resolveGroups(db, {
    entityType: group.location.entityType,
    subType: group.location.subType,
    codeGroups,
  });
  const candidate: ResolvedFieldGroup = { ...group, origin: "db" };
  assertNoCrossGroupFieldKeyConflict([...existing, candidate]);

  try {
    const [row] = await db
      .insert(fieldGroups)
      .values({
        entityType: group.location.entityType,
        subType: group.location.subType ?? null,
        key: group.key,
        label: group.label,
        fields: group.fields,
        position: group.position ?? 0,
      })
      .returning();
    return {
      key: row!.key,
      label: row!.label,
      location: {
        entityType: row!.entityType,
        subType: row!.subType ?? undefined,
      },
      fields: row!.fields as FieldDefinition[],
      position: row!.position,
      origin: "db",
      id: row!.id,
    };
  } catch (e) {
    wrapStoreError(e);
  }
}

export async function updateGroup(
  db: Querier<FieldsSchema>,
  actor: Actor,
  id: string,
  patch: Partial<FieldGroup>,
  codeGroups?: CodeFieldGroup[]
): Promise<ResolvedFieldGroup> {
  assertCanManageGroups(actor);
  const [current] = await db
    .select()
    .from(fieldGroups)
    .where(eq(fieldGroups.id, id))
    .limit(1);
  if (!current) throw new FieldStoreError(`group not found: ${id}`);

  const merged: FieldGroup = {
    key: patch.key ?? current.key,
    label: patch.label ?? current.label,
    location: patch.location ?? {
      entityType: current.entityType,
      subType: current.subType ?? undefined,
    },
    fields: patch.fields ?? (current.fields as FieldDefinition[]),
    position: patch.position ?? current.position,
  };
  defineFieldGroup(merged);
  if (merged.key !== current.key) {
    assertNoCodeKeyShadow(merged.key, merged.location.entityType, codeGroups);
  }

  const others = (
    await resolveGroups(db, {
      entityType: merged.location.entityType,
      subType: merged.location.subType,
      codeGroups,
    })
  ).filter((g) => g.id !== id);
  assertNoCrossGroupFieldKeyConflict([
    ...others,
    { ...merged, origin: "db", id },
  ]);

  try {
    const [row] = await db
      .update(fieldGroups)
      .set({
        entityType: merged.location.entityType,
        subType: merged.location.subType ?? null,
        key: merged.key,
        label: merged.label,
        fields: merged.fields,
        position: merged.position ?? 0,
        updatedAt: new Date(),
      })
      .where(eq(fieldGroups.id, id))
      .returning();
    return {
      key: row!.key,
      label: row!.label,
      location: {
        entityType: row!.entityType,
        subType: row!.subType ?? undefined,
      },
      fields: row!.fields as FieldDefinition[],
      position: row!.position,
      origin: "db",
      id: row!.id,
    };
  } catch (e) {
    wrapStoreError(e);
  }
}

export async function deleteGroup(
  db: Querier<FieldsSchema>,
  actor: Actor,
  id: string
): Promise<void> {
  assertCanManageGroups(actor);
  await db.delete(fieldGroups).where(eq(fieldGroups.id, id));
}

export async function listGroups(
  db: Querier<FieldsSchema>,
  opts: { entityType?: string; subType?: string }
): Promise<ResolvedFieldGroup[]> {
  const filters = [];
  if (opts.entityType !== undefined)
    filters.push(eq(fieldGroups.entityType, opts.entityType));
  if (opts.subType !== undefined)
    filters.push(eq(fieldGroups.subType, opts.subType));

  const rows = await db
    .select()
    .from(fieldGroups)
    .where(filters.length > 0 ? and(...filters) : undefined);

  return rows.map((row) => ({
    key: row.key,
    label: row.label,
    location: { entityType: row.entityType, subType: row.subType ?? undefined },
    fields: row.fields as FieldDefinition[],
    position: row.position,
    origin: "db" as const,
    id: row.id,
  }));
}