import { Inject, Injectable } from "@nestjs/common";
import { Prisma } from "@prisma/client";
import { z } from "zod";
import { normalizeSelectOptions } from "@factory/shared";
export { normalizeSelectOptions };
import { PrismaService } from "../../common/prisma.service.js";
import { AuditService } from "../../common/audit.service.js";
import { EventBus } from "../../common/event-bus.js";
import { CacheService } from "../../common/cache.js";
import { ApiError } from "../../common/errors.js";
import { DatabaseQueryService } from "./query.service.js";
import { actorMembership, assertCan } from "../../lib/authorize.js";
import { PROPERTY_CACHE } from "./property-cache-schema.js";
export { PROPERTY_CACHE };
import { between } from "../../lib/positions.js";
import { EVENTS } from "../../constants.js";
import type {
  CreateFormulaInput,
  CreatePropertyInput,
  CreateRelationInput,
  CreateViewInput,
  UpdateDatabaseInput,
  UpdatePropertyInput,
  UpdateViewInput,
  ValidateFormulaInput
} from "./dto.js";

const DB_SELECT = {
  id: true, workspaceId: true, pageId: true, title: true, icon: true,
  description: true, schemaVersion: true, isArchived: true, createdBy: true,
  createdAt: true, updatedAt: true
} as const;
type DbRow = Prisma.DatabaseGetPayload<{ select: typeof DB_SELECT }>;

/**
 * TASK-0135: the database column metadata (property list + view settings) is
 * cached for 60 s and busted on `database.updated` (a property/view change).
 * The schemas below match the JSON shape of the LIVE `{ data: [...] }`
 * responses so a corrupt entry is a cache MISS, never a 500. `findMany` is
 * called with NO `include`/`select`, so Prisma returns every SCALAR field and
 * omits the relations — the schemas below mirror exactly those scalars
 * (Prisma JSON columns are opaque objects in JSON form; dates → ISO strings).
 */
const VIEW_CACHE = z.object({
  id: z.string(),
  databaseId: z.string(),
  name: z.string(),
  type: z.string(),
  position: z.string(),
  config: z.unknown(),
  filter: z.unknown().nullable(),
  sort: z.unknown().nullable(),
  group: z.unknown().nullable(),
  pageSize: z.number().nullable(),
  createdAt: z.string(),
  updatedAt: z.string()
});

/**
 * `normalizeSelectOptions` — the option-id backfill (deterministic ids for
 * id-less select/status/multi-select options) — moved to `@factory/shared`
 * (`dto/database.dto.ts`) as the single source shared by the SERVER read
 * boundaries, the AI generation path, AND the web read boundary (the web
 * cannot depend on `@factory/ai`). Re-exported here so the existing import
 * sites (the API unit tests) keep working unchanged.
 */

/** Ban obviously dangerous constructs in formula source before storage. */
export function assertFormulaSafe(expression: string): void {
  if (/eval\s*\(|Function\s*\(|new\s+Function|import\s*\(|require\s*\(|child_process|process\.env/i.test(expression)) {
    throw ApiError.validation("Formula contains a forbidden construct.", [
      { field: "expression", issue: "forbidden_construct" }
    ]);
  }
}

/** Very loose dependency scan: property references inside prop("...") calls. */
export function scanFormulaDependencies(expression: string): string[] {
  const refs = new Set<string>();
  const re = /prop\s*\(\s*["']([^"']+)["']\s*\)/g;
  let m: RegExpExecArray | null;
  while ((m = re.exec(expression)) !== null) refs.add(m[1]!);
  return [...refs];
}

/**
 * Database schema service: properties (22 types), views (9 types),
 * relations, formulas. Record query/CRUD lives in query.service.ts.
 */
@Injectable()
export class DatabaseService {
  constructor(
    @Inject(PrismaService) private readonly prisma: PrismaService,
    @Inject(AuditService) private readonly audit: AuditService,
    @Inject(EventBus) private readonly events: EventBus,
    @Inject(DatabaseQueryService) private readonly query: DatabaseQueryService,
    @Inject(CacheService) private readonly cache: CacheService
  ) {}

  private async getDb(databaseId: string): Promise<DbRow> {
    const db = await this.prisma.database.findUnique({ where: { id: databaseId }, select: DB_SELECT });
    if (!db || db.isArchived) throw ApiError.notFound("Database not found.");
    return db;
  }

  /**
   * List the workspace's (non-archived) databases — the mention popup's
   * database group (TASK-0094 R3). Membership-gated (any role): the popup
   * surfaces exactly what the sidebar already shows, so no extra scoping
   * beyond membership. Bounded: `limit` (default 50, capped 100) by
   * recency.
   */
  async listByWorkspace(workspaceId: string, userId: string, limit = 50) {
    // TASK-0127: actor-aware membership (an API key of the workspace is a
    // member-equivalent; the creator's membership is never probed).
    const membership = await actorMembership(this.prisma, userId, workspaceId);
    if (!membership) throw ApiError.notFound("Workspace not found.");
    const capped = Math.max(1, Math.min(limit, 100));
    const rows = await this.prisma.database.findMany({
      where: { workspaceId, isArchived: false },
      select: { id: true, title: true, icon: true },
      orderBy: { updatedAt: "desc" },
      take: capped
    });
    return rows;
  }

  private async getDbChecked(databaseId: string, userId: string, need: "VIEW" | "EDIT" | "FULL_ACCESS") {
    const db = await this.getDb(databaseId);
    await assertCan(this.prisma, { workspaceId: db.workspaceId, userId, resource: { databaseId: db.id } }, need);
    return db;
  }

  /** db + properties + views + records count. */
  async get(databaseId: string, userId: string) {
    const db = await this.getDbChecked(databaseId, userId, "VIEW");
    const [properties, views, recordsCount, relations, formulas, rollups] = await Promise.all([
      this.prisma.databaseProperty.findMany({ where: { databaseId, deletedAt: null }, orderBy: { position: "asc" } }),
      this.prisma.databaseView.findMany({ where: { databaseId }, orderBy: { position: "asc" } }),
      this.prisma.databaseRecord.count({ where: { databaseId, archivedAt: null } }),
      this.prisma.relation.findMany({ where: { databaseId } }),
      this.prisma.formula.findMany({ where: { databaseId } }),
      this.prisma.rollup.findMany({ where: { databaseId } })
    ]);
    // LEGACY rows may still carry id-less select options (AI-generated before
    // the write-boundary normalization). Normalize on read so the contract
    // holds without a data migration; values that stored the option NAME
    // resolve because `optionIdForName(name)` is deterministic.
    const normalized = properties.map((p) =>
      p.type === "SELECT" || p.type === "MULTI_SELECT" || p.type === "STATUS"
        ? { ...p, config: normalizeSelectOptions(p.config) as object }
        : p
    );
    return { database: db, properties: normalized, views, recordsCount, relations, formulas, rollups };
  }

  async update(databaseId: string, userId: string, input: UpdateDatabaseInput, req: any) {
    const db = await this.getDbChecked(databaseId, userId, "EDIT");
    const updated = await this.prisma.database.update({
      where: { id: databaseId },
      data: {
        ...(input.title !== undefined ? { title: input.title } : {}),
        ...(input.icon !== undefined ? { icon: input.icon } : {}),
        ...(input.description !== undefined ? { description: input.description } : {}),
        schemaVersion: { increment: 1 }
      },
      select: DB_SELECT
    });
    // keep the page title in sync when the database has a page
    if (input.title !== undefined && db.pageId) {
      await this.prisma.page.updateMany({ where: { id: db.pageId }, data: { title: input.title } });
    }
    await this.audit.audit(req, req?.auth, {
      action: "database.updated",
      resourceType: "database",
      resourceId: databaseId,
      workspaceId: db.workspaceId
    });
    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, fields: Object.keys(input) });
    return updated;
  }

  /** tx: views/props/records/relations/rollups + the database page. */
  async remove(databaseId: string, userId: string, req: any) {
    const db = await this.getDbChecked(databaseId, userId, "FULL_ACCESS");
    await this.prisma.$transaction(async (tx) => {
      const records = await tx.databaseRecord.findMany({ where: { databaseId }, select: { id: true, pageId: true } });
      const pageIds = records.map((r) => r.pageId).filter((p): p is string => Boolean(p));
      if (pageIds.length > 0) {
        await tx.databaseRecord.deleteMany({ where: { databaseId } });
        await tx.block.deleteMany({ where: { pageId: { in: pageIds } } });
        await tx.page.deleteMany({ where: { id: { in: pageIds } } });
      }
      await tx.database.delete({ where: { id: databaseId } });
      // the database block on the host page (if any)
      if (db.pageId) {
        await tx.block.deleteMany({
          where: { pageId: db.pageId, type: "DATABASE", content: { path: ["databaseId"], equals: databaseId } }
        });
      }
    });
    await this.audit.audit(req, req?.auth, {
      action: "database.deleted",
      resourceType: "database",
      resourceId: databaseId,
      workspaceId: db.workspaceId
    });
    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, deleted: true });
  }

  // ---------- properties ----------

  async listProperties(databaseId: string, userId: string) {
    // TASK-0135: the column metadata is cached for 60 s + busted on
    // `database.updated`. The VIEW access check runs live (cheap, correct);
    // only the findMany is cached.
    await this.getDbChecked(databaseId, userId, "VIEW");
    const { data } = await this.cache.getMetadata(
      databaseId,
      "properties",
      async () => {
        const properties = await this.prisma.databaseProperty.findMany({
          where: { databaseId, deletedAt: null },
          orderBy: { position: "asc" }
        });
        // Same legacy-id normalization as `get()` (id-less select options):
        // the cached entry stores the normalized shape (the cache is busted
        // on `database.updated`, and writes now persist normalized ids).
        const normalized = properties.map((p) =>
          p.type === "SELECT" || p.type === "MULTI_SELECT" || p.type === "STATUS"
            ? { ...p, config: normalizeSelectOptions(p.config) as object }
            : p
        );
        return { data: normalized };
      },
      z.object({ data: z.array(PROPERTY_CACHE) })
    );
    return data as { data: Prisma.DatabasePropertyGetPayload<{}>[] };
  }

  async createProperty(databaseId: string, userId: string, input: CreatePropertyInput, req: any) {
    const db = await this.getDbChecked(databaseId, userId, "EDIT");
    this.validatePropertyForType(input.type, input.config, input.name);
    // Assign deterministic option ids (the docs contract) so stored
    // `optionId` values always resolve — the AI path emits bare
    // `{ name, color }` options, which previously broke every id-based
    // reader (web chips/preselect, board grouping, filters).
    const config = normalizeSelectOptions(input.config) as Record<string, unknown>;
    if (input.type === "TITLE") {
      const existing = await this.prisma.databaseProperty.findFirst({
        where: { databaseId, isTitle: true, deletedAt: null }
      });
      if (existing) throw ApiError.validation("Database already has a title property.", [{ field: "type", issue: "title_exists" }]);
    }
    const last = await this.prisma.databaseProperty.findFirst({
      where: { databaseId, deletedAt: null },
      orderBy: { position: "desc" },
      select: { position: true }
    });
    let prop;
    try {
      prop = await this.prisma.databaseProperty.create({
        data: {
          databaseId,
          name: input.name,
          type: input.type,
          config: config as object,
          required: input.required ?? false,
          isVisible: input.isVisible ?? true,
          default: (input.default === undefined ? undefined : input.default as object) as object | undefined,
          position: between(last?.position)
        }
      });
    } catch {
      throw new ApiError("CONFLICT", 409, "Property name already exists in this database.", [
        { field: "name", issue: "already_exists" }
      ]);
    }
    await this.audit.audit(req, req?.auth, {
      action: "database.property_created",
      resourceType: "database_property",
      resourceId: prop.id,
      workspaceId: db.workspaceId,
      metadata: { databaseId, name: input.name, type: input.type }
    });
    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, propertyId: prop.id });
    return prop;
  }

  private validatePropertyForType(type: string, config: Record<string, unknown>, name: string): void {
    if (type === "SELECT" || type === "MULTI_SELECT" || type === "STATUS") {
      const options = config.options;
      if (!Array.isArray(options) || options.length === 0) {
        throw ApiError.validation("select/status property requires config.options.", [
          { field: "config.options", issue: "required" }
        ]);
      }
    }
    if (type === "RELATION" && typeof config.relationId !== "string") {
      throw ApiError.validation("relation property requires config.relationId (create a relation first).", [
        { field: "config.relationId", issue: "required" }
      ]);
    }
    if (type === "ROLLUP") {
      if (typeof config.relationId !== "string" || typeof config.aggregate !== "string") {
        throw ApiError.validation("rollup property requires config.relationId and config.aggregate.", [
          { field: "config", issue: "relationId+aggregate required" }
        ]);
      }
    }
    if (type === "FORMULA" && typeof config.relationId === "string") {
      // formula references are stored on the Formula row; fine.
    }
    void name;
  }

  async updateProperty(databaseId: string, propertyId: string, userId: string, input: UpdatePropertyInput, req: any) {
    const db = await this.getDbChecked(databaseId, userId, "EDIT");
    const prop = await this.prisma.databaseProperty.findFirst({ where: { id: propertyId, databaseId, deletedAt: null } });
    if (!prop) throw ApiError.notFound("Property not found.");
    if (input.config) this.validatePropertyForType(prop.type, input.config, input.name ?? prop.name);
    // A partial options patch keeps the ids of options already present (so
    // existing record values stay resolvable) and assigns ids to any new
    // option that arrives without one.
    const existingOptions = ((prop.config as Record<string, unknown> | null)?.options) as unknown[] | undefined;
    const config =
      input.config !== undefined ? normalizeSelectOptions(input.config, existingOptions) : (prop.config as object);
    const updated = await this.prisma.databaseProperty.update({
      where: { id: propertyId },
      data: {
        ...(input.name !== undefined ? { name: input.name } : {}),
        ...(input.config !== undefined ? { config: config as object } : {}),
        ...(input.required !== undefined ? { required: input.required } : {}),
        ...(input.isVisible !== undefined ? { isVisible: input.isVisible } : {}),
        ...(input.default !== undefined ? { default: input.default as object } : {})
      }
    });
    await this.audit.audit(req, req?.auth, {
      action: "database.property_updated",
      resourceType: "database_property",
      resourceId: propertyId,
      workspaceId: db.workspaceId,
      metadata: { databaseId }
    });
    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, propertyId });
    return updated;
  }

  /** Soft-delete a property, guarded against in-use references. */
  async deleteProperty(databaseId: string, propertyId: string, userId: string, req: any) {
    const db = await this.getDbChecked(databaseId, userId, "EDIT");
    const prop = await this.prisma.databaseProperty.findFirst({ where: { id: propertyId, databaseId, deletedAt: null } });
    if (!prop) throw ApiError.notFound("Property not found.");
    if (prop.isTitle) {
      throw ApiError.validation("The title property cannot be deleted.", [{ field: "property", issue: "title_protected" }]);
    }
    // guard: referenced by formulas/rollups/relations?
    const [formulaCount, rollupCount, relationCount] = await Promise.all([
      this.prisma.formula.count({ where: { databaseId, dependencies: { has: propertyId } } }),
      this.prisma.rollup.count({ where: { databaseId, targetPropertyId: propertyId } }),
      this.prisma.relation.count({ where: { OR: [{ propertyId }, { reversePropertyId: propertyId }] } })
    ]);
    if (formulaCount + rollupCount + relationCount > 0) {
      throw new ApiError("UNPROCESSABLE_ENTITY", 422, "Property is used by formulas, rollups or relations — remove those first.", [
        { issue: "in_use", formulas: formulaCount, rollups: rollupCount, relations: relationCount }
      ]);
    }
    await this.prisma.$transaction([
      this.prisma.databaseProperty.update({ where: { id: propertyId }, data: { deletedAt: new Date() } }),
      this.prisma.recordProperty.deleteMany({ where: { propertyId } })
    ]);
    await this.audit.audit(req, req?.auth, {
      action: "database.property_deleted",
      resourceType: "database_property",
      resourceId: propertyId,
      workspaceId: db.workspaceId,
      metadata: { databaseId, name: prop.name }
    });
    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, propertyId, deleted: true });
  }

  async reorderProperty(databaseId: string, propertyId: string, userId: string, position: { after?: string; before?: string; index?: number }, req: any) {
    await this.getDbChecked(databaseId, userId, "EDIT");
    const prop = await this.prisma.databaseProperty.findFirst({ where: { id: propertyId, databaseId, deletedAt: null } });
    if (!prop) throw ApiError.notFound("Property not found.");
    let newPos: string | undefined;
    if (position.after || position.before) {
      let before: string | undefined;
      let after: string | undefined;
      if (position.before) {
        const b = await this.prisma.databaseProperty.findUnique({ where: { id: position.before }, select: { position: true } });
        if (!b) throw ApiError.notFound("before property not found.");
        before = b.position;
      }
      if (position.after) {
        const a = await this.prisma.databaseProperty.findUnique({ where: { id: position.after }, select: { position: true } });
        if (!a) throw ApiError.notFound("after property not found.");
        after = a.position;
      }
      newPos = between(after, before);
    } else if (position.index !== undefined) {
      const props = await this.prisma.databaseProperty.findMany({
        where: { databaseId, deletedAt: null },
        orderBy: { position: "asc" },
        select: { id: true, position: true }
      });
      const at = Math.max(0, Math.min(position.index, props.length - 1));
      const sorted = [...props].sort((a, b) => a.position.localeCompare(b.position));
      const prev = at > 0 ? sorted[at - 1]?.position : undefined;
      const next = sorted[at + 1]?.position;
      newPos = between(prev, next);
    }
    if (!newPos) throw ApiError.validation("Provide after/before property ids or an index.", [{ field: "position", issue: "required" }]);
    const updated = await this.prisma.databaseProperty.update({ where: { id: propertyId }, data: { position: newPos } });
    const db2 = await this.getDb(databaseId);
    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db2.workspaceId, databaseId, propertyId, reordered: true });
    return updated;
  }

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

  async listViews(databaseId: string, userId: string) {
    // TASK-0135: view settings cached for 60 s + busted on `database.updated`
    // (a view change carries `viewId`). Access check stays live.
    await this.getDbChecked(databaseId, userId, "VIEW");
    const { data } = await this.cache.getMetadata(
      databaseId,
      "views",
      async () => {
        const views = await this.prisma.databaseView.findMany({ where: { databaseId }, orderBy: { position: "asc" } });
        return { data: views };
      },
      z.object({ data: z.array(VIEW_CACHE) })
    );
    return data as { data: Prisma.DatabaseViewGetPayload<{}>[] };
  }

  async createView(databaseId: string, userId: string, input: CreateViewInput, req: any) {
    const db = await this.getDbChecked(databaseId, userId, "EDIT");
    const last = await this.prisma.databaseView.findFirst({
      where: { databaseId },
      orderBy: { position: "desc" },
      select: { position: true }
    });
    const view = await this.prisma.databaseView.create({
      data: {
        databaseId,
        name: input.name,
        type: input.type,
        config: input.config as object,
        position: between(last?.position)
      }
    });
    await this.audit.audit(req, req?.auth, {
      action: "database.view_created",
      resourceType: "database_view",
      resourceId: view.id,
      workspaceId: db.workspaceId,
      metadata: { databaseId, name: input.name, type: input.type }
    });
    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, viewId: view.id });
    return view;
  }

  async updateView(databaseId: string, viewId: string, userId: string, input: UpdateViewInput, req: any) {
    const db = await this.getDbChecked(databaseId, userId, "EDIT");
    const view = await this.prisma.databaseView.findFirst({ where: { id: viewId, databaseId } });
    if (!view) throw ApiError.notFound("View not found.");
    const merged = { ...(view.config as object), ...(input.config ?? {}) } as object;
    const updated = await this.prisma.databaseView.update({
      where: { id: viewId },
      data: {
        ...(input.name !== undefined ? { name: input.name } : {}),
        config: merged,
        ...(input.filter !== undefined ? { filter: input.filter as object } : {}),
        ...(input.sort !== undefined ? { sort: input.sort as object } : {}),
        ...(input.group !== undefined ? { group: input.group as object } : {}),
        // T-166: rows-per-page is a top-level column (the §6 query reads it as
        // the listing default) — written only when explicitly provided.
        ...(input.pageSize !== undefined ? { pageSize: input.pageSize } : {})
      }
    });
    await this.audit.audit(req, req?.auth, {
      action: "database.view_updated",
      resourceType: "database_view",
      resourceId: viewId,
      workspaceId: db.workspaceId,
      metadata: { databaseId }
    });
    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, viewId });
    return updated;
  }

  async deleteView(databaseId: string, viewId: string, userId: string, req: any) {
    const db = await this.getDbChecked(databaseId, userId, "EDIT");
    const view = await this.prisma.databaseView.findFirst({ where: { id: viewId, databaseId } });
    if (!view) throw ApiError.notFound("View not found.");
    await this.prisma.databaseView.delete({ where: { id: viewId } });
    await this.audit.audit(req, req?.auth, {
      action: "database.view_deleted",
      resourceType: "database_view",
      resourceId: viewId,
      workspaceId: db.workspaceId,
      metadata: { databaseId, name: view.name }
    });
    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, viewId, deleted: true });
  }

  async reorderView(databaseId: string, viewId: string, userId: string, position: { after?: string; before?: string }, req: any) {
    const db = await this.getDbChecked(databaseId, userId, "EDIT");
    const view = await this.prisma.databaseView.findFirst({ where: { id: viewId, databaseId } });
    if (!view) throw ApiError.notFound("View not found.");
    let before: string | undefined;
    let after: string | undefined;
    if (position.before) {
      const b = await this.prisma.databaseView.findUnique({ where: { id: position.before }, select: { position: true } });
      if (!b) throw ApiError.notFound("before view not found.");
      before = b.position;
    }
    if (position.after) {
      const a = await this.prisma.databaseView.findUnique({ where: { id: position.after }, select: { position: true } });
      if (!a) throw ApiError.notFound("after view not found.");
      after = a.position;
    }
    const updated = await this.prisma.databaseView.update({
      where: { id: viewId },
      data: { position: between(after, before) }
    });
    await this.audit.audit(req, req?.auth, {
      action: "database.view_reordered",
      resourceType: "database_view",
      resourceId: viewId,
      workspaceId: db.workspaceId,
      metadata: { databaseId, position: updated.position }
    });
    // T-166: reorder must bust the 60 s `listViews` metadata cache (and drive
    // the client's invalidation) — the same `DATABASE_UPDATED` the sibling
    // view ops publish. Without it the web "set as default" reorder was a
    // silent no-op (stale position served for up to a minute).
    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, viewId });
    return updated;
  }

  // ---------- relations ----------

  async listRelations(databaseId: string, userId: string) {
    await this.getDbChecked(databaseId, userId, "VIEW");
    const relations = await this.prisma.relation.findMany({ where: { databaseId } });
    return { data: relations };
  }

  async createRelation(databaseId: string, userId: string, input: CreateRelationInput, req: any) {
    const db = await this.getDbChecked(databaseId, userId, "EDIT");
    const target = await this.getDb(input.targetDatabaseId);
    if (target.workspaceId !== db.workspaceId) {
      throw ApiError.validation("Both databases must be in the same workspace.", [{ field: "targetDatabaseId", issue: "workspace_mismatch" }]);
    }

    const created = await this.prisma.$transaction(async (tx) => {
      const lastSrc = await tx.databaseProperty.findFirst({ where: { databaseId, deletedAt: null }, orderBy: { position: "desc" }, select: { position: true } });
      const prop = await tx.databaseProperty.create({
        data: {
          databaseId,
          name: input.name,
          type: "RELATION",
          config: { targetDatabaseId: input.targetDatabaseId } as object,
          position: between(lastSrc?.position)
        }
      });
      let reverseProp;
      let reverseName: string | undefined;
      if (input.twoWay) {
        reverseName = input.reverseName ?? input.name;
        const lastTgt = await tx.databaseProperty.findFirst({ where: { databaseId: input.targetDatabaseId, deletedAt: null }, orderBy: { position: "desc" }, select: { position: true } });
        reverseProp = await tx.databaseProperty.create({
          data: {
            databaseId: input.targetDatabaseId,
            name: reverseName,
            type: "RELATION",
            config: { targetDatabaseId: databaseId, reverse: true } as object,
            position: between(lastTgt?.position)
          }
        });
      }
      const relation = await tx.relation.create({
        data: {
          databaseId,
          targetDatabaseId: input.targetDatabaseId,
          name: input.name,
          targetName: input.twoWay ? reverseName : undefined,
          propertyId: prop.id,
          reversePropertyId: reverseProp?.id,
          cardinality: input.cardinality,
          bidirectional: input.twoWay
        }
      });
      return { relation, prop, reverseProp };
    });

    await this.audit.audit(req, req?.auth, {
      action: "database.relation_created",
      resourceType: "relation",
      resourceId: created.relation.id,
      workspaceId: db.workspaceId,
      metadata: { databaseId, targetDatabaseId: input.targetDatabaseId, name: input.name, twoWay: input.twoWay }
    });
    await this.events.publish(EVENTS.DATABASE_UPDATED, {
      workspaceId: db.workspaceId,
      databaseId,
      relationId: created.relation.id
    });
    return created.relation;
  }

  async updateRelation(databaseId: string, relationId: string, userId: string, patch: { name?: string; targetName?: string }, req: any) {
    await this.getDbChecked(databaseId, userId, "EDIT");
    const rel = await this.prisma.relation.findFirst({ where: { id: relationId, databaseId } });
    if (!rel) throw ApiError.notFound("Relation not found.");
    const updated = await this.prisma.relation.update({
      where: { id: relationId },
      data: {
        ...(patch.name !== undefined ? { name: patch.name } : {}),
        ...(patch.targetName !== undefined ? { targetName: patch.targetName } : {})
      }
    });
    // rename the properties too
    if (patch.name !== undefined && rel.propertyId) {
      await this.prisma.databaseProperty.updateMany({ where: { id: rel.propertyId }, data: { name: patch.name } });
    }
    if (patch.targetName !== undefined && rel.reversePropertyId) {
      await this.prisma.databaseProperty.updateMany({ where: { id: rel.reversePropertyId }, data: { name: patch.targetName } });
    }
    await this.audit.audit(req, req?.auth, {
      action: "database.relation_updated",
      resourceType: "relation",
      resourceId: relationId,
      workspaceId: (await this.getDb(databaseId)).workspaceId
    });
    return updated;
  }

  /** Drop both relation properties + links + the relation row. */
  async deleteRelation(databaseId: string, relationId: string, userId: string, req: any) {
    const db = await this.getDbChecked(databaseId, userId, "EDIT");
    const rel = await this.prisma.relation.findFirst({ where: { id: relationId, databaseId } });
    if (!rel) throw ApiError.notFound("Relation not found.");
    await this.prisma.$transaction(async (tx) => {
      await tx.relationLink.deleteMany({ where: { databaseId, record: { properties: { some: { propertyId: rel.propertyId ?? undefined } } } } });
      await tx.relationLink.deleteMany({ where: { targetDatabaseId: rel.targetDatabaseId, record: { databaseId: rel.targetDatabaseId } } });
      if (rel.propertyId) await tx.databaseProperty.update({ where: { id: rel.propertyId }, data: { deletedAt: new Date() } });
      if (rel.reversePropertyId) await tx.databaseProperty.update({ where: { id: rel.reversePropertyId }, data: { deletedAt: new Date() } });
      await tx.recordProperty.deleteMany({ where: { propertyId: { in: [rel.propertyId, rel.reversePropertyId].filter(Boolean) as string[] } } });
      await tx.relation.delete({ where: { id: relationId } });
    });
    await this.audit.audit(req, req?.auth, {
      action: "database.relation_deleted",
      resourceType: "relation",
      resourceId: relationId,
      workspaceId: db.workspaceId,
      metadata: { name: rel.name, targetDatabaseId: rel.targetDatabaseId }
    });
    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, relationId, deleted: true });
  }

  // ---------- formulas ----------

  async listFormulas(databaseId: string, userId: string) {
    await this.getDbChecked(databaseId, userId, "VIEW");
    const formulas = await this.prisma.formula.findMany({ where: { databaseId } });
    return { data: formulas };
  }

  async createFormula(databaseId: string, userId: string, input: CreateFormulaInput, req: any) {
    const db = await this.getDbChecked(databaseId, userId, "EDIT");
    assertFormulaSafe(input.expression);
    const deps = await this.propertyNamesToIds(databaseId, scanFormulaDependencies(input.expression));
    const prop = await this.prisma.databaseProperty.create({
      data: {
        databaseId,
        name: input.name,
        type: "FORMULA",
        config: {} as object,
        position: "Z"
      }
    });
    const formula = await this.prisma.formula.create({
      data: {
        databaseId,
        propertyId: prop.id,
        expression: input.expression,
        // TASK-0124: an engine-validated AST (the AI formula generator) is
        // stored verbatim; otherwise the historical placeholder.
        ast: (input.ast ?? { raw: input.expression, safe: true }) as object,
        returnType: input.returnType ?? "UNKNOWN",
        dependencies: deps
      }
    });
    await this.audit.audit(req, req?.auth, {
      action: "database.formula_created",
      resourceType: "formula",
      resourceId: formula.id,
      workspaceId: db.workspaceId,
      metadata: { databaseId, name: input.name, dependencies: deps }
    });
    await this.events.publish(EVENTS.DATABASE_UPDATED, { workspaceId: db.workspaceId, databaseId, formulaId: formula.id });
    return formula;
  }

  private async propertyNamesToIds(databaseId: string, names: string[]): Promise<string[]> {
    if (names.length === 0) return [];
    const props = await this.prisma.databaseProperty.findMany({
      where: { databaseId, deletedAt: null },
      select: { id: true, name: true }
    });
    const byName = new Map(props.map((p) => [p.name.toLowerCase(), p.id]));
    return names.map((n) => byName.get(n.toLowerCase())).filter((x): x is string => Boolean(x));
  }

  /**
   * Validate with packages/database-engine when installed; otherwise a
   * lightweight syntax check (balanced parens + safe construct scan) and a
   * clear "engine not installed" note.
   */
  async validateFormula(input: ValidateFormulaInput): Promise<{ valid: boolean; error?: string; engine: string }> {
    assertFormulaSafe(input.expression);
    const fallback = "built-in (engine not installed — run pnpm install)";
    const mod = await loadFormulaEngine();
    if (mod) {
      try {
        // Save-time validation = parse + closed function table (the
        // syntax-level guarantees). Name resolution (unknown property →
        // TYPE) is checked at EVALUATION time against a real record's
        // values, not here — a formula may reference a property that only
        // exists once the caller's sample record is known.
        mod.parseFormula(input.expression);
        return { valid: true, engine: "packages/database-engine" };
      } catch (err) {
        return { valid: false, error: (err as Error).message, engine: "packages/database-engine" };
      }
    }
    if (!balanced(input.expression)) {
      return { valid: false, error: "unbalanced parentheses", engine: fallback };
    }
    return { valid: true, engine: fallback };
  }

  /**
   * TASK-0134 — evaluate a stored formula against a record with the safe
   * engine (100k-op + 50 ms wall-clock guards). VIEW-gated (reuses
   * `query.getRecord`). On a budget breach the engine throws
   * `FormulaError("FORMULA_TIMEOUT")` → canonical 422 `FORMULA_TIMEOUT`
   * (docs/api/authentication.md §9.1); other engine errors (PARSE/TYPE/
   * RUNTIME/UNKNOWN_FUNCTION) → 400. The engine is loaded lazily; if the
   * package is absent the endpoint answers `evaluated: null` + a note
   * (backward compatible with the "engine not installed" fallback).
   */
  async evaluateRecordFormula(
    recordId: string,
    formulaId: string,
    actor: string
  ): Promise<Record<string, unknown>> {
    // VIEW-gated record fetch (also yields databaseId + the id-keyed values).
    const record = await this.query.getRecord(recordId, actor);
    const { databaseId, properties: idProps } = record as {
      databaseId: string;
      properties: Record<string, unknown>;
    };
    const formula = (await this.listFormulas(databaseId, actor)) as unknown as { data: { id: string; expression: string }[] };
    const f = formula.data.find((x) => x.id === formulaId);
    if (!f) throw ApiError.notFound("Formula not found.");

    const mod = await loadFormulaEngine();
    if (!mod) {
      return { formulaId, expression: f.expression, evaluated: null, value: null, engine: "not installed (run pnpm install)" };
    }

    // Build the name-keyed evaluation context: the record's values are
    // id-keyed in the DTO; the engine resolves `prop("Name")` by name. The
    // title property is stored as a `{text, marks}` object — unwrap it to
    // its text so `prop("Title")` yields a string, not an object.
    const props = await this.prisma.databaseProperty.findMany({
      where: { databaseId, deletedAt: null },
      select: { id: true, name: true, type: true }
    });
    const ctxProps: Record<string, unknown> = {};
    const ctxTypes: Record<string, string> = {};
    for (const p of props) {
      const raw = idProps[p.id];
      ctxProps[p.name] =
        raw && typeof raw === "object" && "text" in raw ? (raw as { text: string }).text : raw == null ? null : raw;
      ctxTypes[p.name] = p.type;
    }

    let ast: unknown;
    try {
      ast = mod.parseFormula(f.expression);
    } catch (err) {
      // A stored formula that fails to parse (corrupt / hand-written) →
      // 400, never a 500.
      throw new ApiError("VALIDATION_ERROR", 400, `Formula does not parse: ${(err as Error).message}`, [
        { issue: "formula_parse", resource: "formula", received: formulaId }
      ]);
    }

    try {
      const { value, steps } = mod.evaluateFormula(ast, {
        now: Date.now(),
        properties: ctxProps,
        propertyTypes: ctxTypes
      });
      return { formulaId, expression: f.expression, evaluated: true, value: value.v, steps, engine: "packages/database-engine" };
    } catch (err) {
      if (err instanceof mod.FormulaError) {
        if (err.code === "FORMULA_TIMEOUT") {
          // The canonical 422 — a syntactically valid formula that exceeds
          // the 100k-op / 50 ms budget. Distinct from the save-time codes.
          throw new ApiError("FORMULA_TIMEOUT", 422, "Formula exceeded the evaluation budget.", [
            { reason: "formula_timeout", limit: "100k ops / 50 ms", resource: "formula", received: formulaId }
          ]);
        }
        throw new ApiError("VALIDATION_ERROR", 400, `Formula evaluation failed: ${err.message}`, [
          { issue: err.code, resource: "formula", received: formulaId }
        ]);
      }
      throw err;
    }
  }
}

/**
 * The formula-engine surface the API actually uses (TASK-0134 widened this —
 * it was a `validateFormula(expr): {valid, error?}` shape that matched a
 * non-existent `engine.ts`, so the loader silently fell back to the built-in
 * check and the endpoint NEVER evaluated. It now points at the real package
 * entry and exposes the AST evaluate/parse + FormulaError the record-eval
 * path needs to surface `FORMULA_TIMEOUT`).
 */
interface FormulaErrorLike {
  readonly code: string;
  message: string;
}
interface FormulaEngineModule {
  parseFormula(src: string): unknown;
  evaluateFormula(
    ast: unknown,
    ctx: { now: number; properties: Record<string, unknown>; propertyTypes?: Record<string, string> }
  ): { value: { v: unknown; t: string }; steps: number };
  FormulaError: new (code: string, message?: string) => FormulaErrorLike;
}

/**
 * Try to load the safe formula engine from packages/database-engine (dev
 * source or built dist). Returns undefined when the package is absent —
 * the endpoint then answers with the built-in check + an install note.
 * Robust under vitest / any subdirectory cwd (walks up to the repo root),
 * mirroring the AI service's loader.
 */
async function loadFormulaEngine(): Promise<FormulaEngineModule | undefined> {
  const { existsSync } = await import("node:fs");
  const { join, resolve } = await import("node:path");
  const { pathToFileURL } = await import("node:url");
  const rel = "packages/database-engine/src/formula/index.ts";
  const distRel = "packages/database-engine/dist/formula/index.js";
  const candidates: string[] = [join(process.cwd(), rel), join(process.cwd(), distRel)];
  // src/modules/databases and dist/modules/databases are both 5 levels under
  // the repo root. `__dirname` is CJS-only (undefined in pure ESM) — guarded.
  if (typeof __dirname !== "undefined") {
    candidates.push(
      resolve(__dirname, "..", "..", "..", "..", "..", rel),
      resolve(__dirname, "..", "..", "..", "..", "..", distRel)
    );
  }
  // Walk up from cwd — robust under vitest (cwd = a tests/ subdir) or a tool
  // launched from any subdirectory.
  let dir = process.cwd();
  for (let i = 0; i < 8; i++) {
    candidates.push(join(dir, rel), join(dir, distRel));
    const parent = resolve(dir, "..");
    if (parent === dir) break;
    dir = parent;
  }
  for (const candidate of new Set(candidates)) {
    if (existsSync(candidate)) {
      try {
        const mod = (await import(/* webpackIgnore: true */ pathToFileURL(candidate).href)) as FormulaEngineModule;
        if (typeof mod.parseFormula === "function" && typeof mod.evaluateFormula === "function") return mod;
      } catch {
        // broken source — keep trying
      }
    }
  }
  return undefined;
}

function balanced(s: string): boolean {
  let depth = 0;
  for (const ch of s) {
    if (ch === "(") depth++;
    else if (ch === ")") depth--;
    if (depth < 0) return false;
  }
  return depth === 0;
}