"use client";

/**
 * Data hooks for databases, properties, views and records.
 *
 * Wire shapes follow docs/api/databases.md (the canonical contract):
 *  - GET    /databases/:id                        → database + properties + views
 *  - POST   /databases/:id/query                  → filter/sort/group/paginate (server-side engine)
 *  - GET    /databases/:id/records?viewId=...     → same, view-rendered (GET variant)
 *  - POST   /databases/:id/records                → create record
 *  - PATCH  /records/:recordId                    → update (If-Match optimistic concurrency)
 *  - POST   /records/:recordId/archive|unarchive  → soft archive
 *  - DELETE /records/:recordId                    → hard delete (confirm = recordId)
 *  - POST/PATCH/DELETE /databases/:id/properties[/:propertyId]
 *  - POST/PATCH/DELETE /databases/:id/views[/:viewId]
 *  - POST   /databases/:id/formulas/validate      → { expression, sampleRecordIds }
 *
 * Envelope note: the API returns `{ data: … }`. `unwrap()` below accepts both
 * the envelope and an already-unwrapped body so this file is independent of
 * the exact apiFetch convention the core layer settles on.
 */
import { useCallback, useMemo } from "react";
import {
  useInfiniteQuery,
  useMutation,
  useQuery,
  useQueryClient
} from "@tanstack/react-query";
import { apiFetch } from "@/lib/api";
// The SAME deterministic option-id generator + config normalizer the server
// uses (single source of truth). The web cannot depend on `@factory/ai`, so
// this lives in `@factory/shared`.
//
// IMPORTANT: import the browser-safe `@factory/shared/dto` SUBPATH, not the
// package ROOT (`@factory/shared`). The root barrel re-exports `crypto.ts`,
// `events.ts`, `webhook-delivery.ts`, `env.ts`, … which import `node:crypto`
// / `node:fs` — a root import here would pull those into the browser bundle
// and fail the Next.js build with `UnhandledSchemeError: node:crypto`.
import { normalizeSelectOptions } from "@factory/shared/dto";

// ---------------------------------------------------------------------------
// Wire types (mirror of docs/api/databases.md + prisma schema enums)
// ---------------------------------------------------------------------------

export type PropertyType =
  | "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 ViewType =
  | "table" | "list" | "board" | "gallery" | "calendar" | "timeline"
  | "chart" | "dashboard" | "form";

export interface SelectOption {
  id: string;
  name: string;
  /** gray | brown | orange | yellow | green | blue | purple | pink | red | default */
  color: string;
}

/** Free-form per-type config (docs/api/databases.md §3.4). */
export interface PropertyConfig extends Record<string, unknown> {
  options?: SelectOption[];
  format?: "number" | "decimal" | "currency" | "percent" | "scientific";
  currency?: string;
  precision?: number;
  includeTime?: boolean;
  timeZone?: string | null;
  targetDatabaseId?: string;
  cardinality?: "one_to_one" | "one_to_many" | "many_to_many";
  bidirectional?: boolean;
  reverseName?: string;
  relationPropertyId?: string;
  targetPropertyId?: string;
  aggregate?: "COUNT" | "SUM" | "AVERAGE" | "MIN" | "MAX" | "PERCENT" | "UNIQUE";
  expression?: string;
  prefix?: string;
  actions?: unknown[];
  maxFiles?: number;
  acceptedMimeTypes?: string[];
  defaultCountry?: string;
  maxLines?: number | null;
  groups?: { name: string; optionIds: string[]; color: string }[];
}

export interface DatabaseProperty {
  id: string;
  databaseId: string;
  name: string;
  type: PropertyType;
  position: string;
  isTitle: boolean;
  isVisible: boolean;
  required: boolean;
  config: PropertyConfig;
  default: unknown | null;
  createdAt?: string;
  updatedAt?: string;
}

/** Typed per-type value (docs/api/databases.md §3.4 "Stored value shape"). */
export interface RecordValue {
  text?: string;
  marks?: unknown[];
  value?: number | string | boolean;
  optionId?: string;
  optionIds?: string[];
  userIds?: string[];
  userId?: string;
  start?: string | null;
  end?: string | null;
  dateTimeTimezone?: string | null;
  fileIds?: string[];
  recordIds?: string[];
  title?: string;
  type?: string;
  [key: string]: unknown;
}

export interface RecordDto {
  id: string;
  workspaceId: string;
  databaseId: string;
  pageId: string | null;
  icon: string | null;
  coverUrl: string | null;
  archivedAt: string | null;
  isSubItem: boolean;
  position: string;
  version: number;
  createdBy: string | null;
  createdTime: string;
  lastEditedBy: string | null;
  lastEditedTime: string;
  /** Record title (the title property's text) — the API returns it top-level
   *  (`toDto`: `title: page?.title`). Used by relation chips + record links. */
  title?: string;
  /** propertyId → typed value */
  properties: Record<string, RecordValue>;
  parentRecordId?: string | null;
  subItemCount?: number;
}

export interface FilterCondition {
  propertyId: string;
  op: string;
  value?: RecordValue | Record<string, unknown>;
}
export interface FilterGroup {
  op: "and" | "or" | "not";
  conditions: (FilterGroup | FilterCondition)[];
}
export type FilterTree = FilterGroup | FilterCondition;

export interface SortSpec {
  propertyId: string;
  direction: "asc" | "desc";
}
export interface GroupSpec {
  propertyId: string;
  direction: "asc" | "desc";
}

export interface ChartConfig {
  type: "bar" | "doughnut" | "line";
  /** numeric property to aggregate (number/rollup/formula) */
  property: string;
  /** property to bucket by (select/multi_select/status/person/date); omit = single bucket */
  groupBy?: string;
  aggregation?: "COUNT" | "SUM" | "AVERAGE" | "MIN" | "MAX";
}

export interface DatabaseButtonDef {
  id?: string;
  name: string;
  icon?: string;
  color?: string;
  /** optional prompt shown before execution */
  prompt?: string;
  action:
    | { type: "update"; propertyId: string; value: RecordValue }
    | { type: "create"; template?: Record<string, RecordValue> }
    | { type: "navigate"; url?: string; pageId?: string }
    | { type: "formula" };
}

export interface ViewConfig extends Record<string, unknown> {
  visibleProperties?: string[];
  cardProperties?: string[];
  columnWidths?: Record<string, number>;
  headerRow?: boolean;
  zebra?: boolean;
  showIcons?: boolean;
  subtitlePropertyId?: string;
  cardLayout?: "cover" | "title" | "compact";
  columnWidthPx?: number;
  coverPropertyId?: string;
  cardsPerRow?: number;
  cardRatio?: string;
  startDatePropertyId?: string;
  endDatePropertyId?: string;
  weekStartsOn?: number;
  monthDensity?: string;
  scale?: "day" | "week" | "month" | "quarter";
  lanePropertyId?: string;
  chartConfig?: ChartConfig;
  chartId?: string;
  formId?: string;
  buttons?: DatabaseButtonDef[];
  color?: string | null;
  density?: "compact" | "comfortable" | "spacious";
  intro?: string;
  successMessage?: string;
  branding?: Record<string, unknown>;
}

export interface DatabaseView {
  id: string;
  databaseId: string;
  name: string;
  type: ViewType;
  position: string;
  config: ViewConfig;
  filter: FilterTree | null;
  sort: SortSpec[] | null;
  group: GroupSpec[] | null;
  pageSize: number | null;
  recordCount?: number;
  createdAt?: string;
  updatedAt?: string;
}

export interface DatabaseDto {
  id: string;
  workspaceId: string;
  pageId: string | null;
  title: string;
  icon: string | null;
  description: string | null;
  schemaVersion: number;
  isArchived: boolean;
  recordCount?: number;
  createdBy?: string | null;
  createdAt: string;
  updatedAt?: string;
  properties: DatabaseProperty[];
  views: DatabaseView[];
  buttons?: DatabaseButtonDef[];
}

export interface RecordGroup {
  key: { propertyId: string; value: RecordValue; label: string };
  count: number;
  records: RecordDto[];
}

export interface QueryResult {
  records: RecordDto[];
  groups: RecordGroup[] | null;
  aggregates: Record<string, unknown> | null;
  totalCount: number | null;
  nextCursor: string | null;
}

// ---------------------------------------------------------------------------
// Small value helpers (shared by cells, CSV export, chart, search-in-view)
// ---------------------------------------------------------------------------

function unwrap<T>(body: unknown): T {
  const b = body as { data?: T } | null;
  if (b && typeof b === "object" && "data" in (b as Record<string, unknown>)) {
    return (b as { data: T }).data;
  }
  return body as T;
}

export function titlePropertyOf(properties: DatabaseProperty[]): DatabaseProperty | undefined {
  return properties.find((p) => p.isTitle) ?? properties.find((p) => p.type === "title");
}

export function recordTitle(record: RecordDto, properties: DatabaseProperty[]): string {
  const t = titlePropertyOf(properties);
  if (!t) return "Untitled";
  const v = record.properties[t.id];
  if (!v) return "Untitled";
  if (typeof v.text === "string") return v.text || "Untitled";
  if (typeof v.value === "string") return v.value || "Untitled";
  return "Untitled";
}

/**
 * Tolerant option lookup — id first, then NAME.
 *
 * The API contract is `{ optionId: <option.id> }` and the server now
 * guarantees option ids on read (select/status/multi-select config + stored
 * values are canonicalized). A stored option NAME can still reach the client
 * through optimistic merges and realtime payloads, so every consumer resolves
 * a ref against BOTH the id and the name — legacy chips and preselects render
 * instead of blanking.
 */
export function findOption(options: SelectOption[] | undefined | null, ref: unknown): SelectOption | undefined {
  if (typeof ref !== "string" || ref === "") return undefined;
  if (!options) return undefined;
  return options.find((o) => o.id === ref) ?? options.find((o) => o.name === ref);
}

/**
 * Build the URL of a record/database page from the CURRENT pathname.
 *
 * The only page route is `/w/[workspace]/page/[id]` (app/(app)/w/[workspace]/
 * page/[id]/page.tsx) — there is NO `/p/[id]` alias. The workspace slug is the
 * SECOND path segment (the first is the literal `w`); the old code took the
 * FIRST segment and produced `/w/p/{id}` → 404 "This page does not exist".
 * Pure (unit-tested) and shared by the embedded database header ("Open" icon)
 * and the record panel (ExternalLink icon).
 */
export function pageUrlFromPath(pathname: string | null | undefined, pageId: string): string {
  const m = pathname?.match(/^\/w\/([^/]+)/);
  return m?.[1] ? `/w/${m[1]}/page/${pageId}` : `/page/${pageId}`;
}

/** Plain-text rendering of any typed value — used by CSV export, chart, search. */
export function valueToText(value: RecordValue | undefined | null, property?: DatabaseProperty): string {
  if (value == null) return "";
  if (typeof value === "string") return value;
  if (value.text != null) return String(value.text);
  if (value.value != null) {
    if (property?.config?.format === "currency" && typeof value.value === "number") {
      return `${property.config.currency ?? "USD"} ${value.value}`;
    }
    return String(value.value);
  }
  if (value.optionId != null && property) {
    return findOption(property.config.options, value.optionId)?.name ?? "";
  }
  if (value.optionIds?.length && property) {
    return value.optionIds
      .map((id) => findOption(property.config.options, id)?.name)
      .filter(Boolean)
      .join(", ");
  }
  if (value.start) return value.start.slice(0, 10);
  if (value.recordIds) return `${value.recordIds.length} linked`;
  if (value.fileIds) return `${value.fileIds.length} file(s)`;
  if (value.userIds) return `${value.userIds.length} person(s)`;
  return JSON.stringify(value);
}

/**
 * Build a "contains text" filter over the title + all text-ish properties.
 * Used by SearchInView — composes documented filter ops only (the query
 * engine itself stays server-side / in @factory/database-engine).
 */
export function searchFilter(properties: DatabaseProperty[], text: string): FilterTree | null {
  const t = text.trim();
  if (!t) return null;
  const conditions: FilterCondition[] = [];
  for (const p of properties) {
    if (p.type === "title" || p.type === "text" || p.type === "rich_text") {
      conditions.push({ propertyId: p.id, op: "contains", value: { text: t } });
    }
  }
  if (conditions.length === 0) return null;
  return conditions.length === 1 ? conditions[0] : { op: "or", conditions };
}

/** Merge a view's saved filter with ad-hoc conditions (search, quick filters). */
export function andFilters(...trees: (FilterTree | null | undefined)[]): FilterTree | null {
  const present = trees.filter((t): t is FilterTree => t != null);
  if (present.length === 0) return null;
  if (present.length === 1) return present[0];
  return { op: "and", conditions: present };
}

export function isCondition(node: FilterGroup | FilterCondition): node is FilterCondition {
  return (node as FilterCondition).propertyId != null;
}

// ---------------------------------------------------------------------------
// useDatabase
// ---------------------------------------------------------------------------

const dbKey = (id: string) => ["database", id] as const;

/**
 * Normalize a database view's `type` to the web's internal LOWERCASE contract.
 *
 * The API stores/serializes `ViewType` as the Prisma UPPERCASE enum (TABLE,
 * BOARD, …, FORM — the web's ViewRouter / view tabs / ViewDialog are all
 * lowercase). Without this translation every view falls through the
 * ViewRouter's `default` branch (→ TableView) and UI view creation is rejected
 * (the API's `z.enum(VIEW_TYPES)` wants uppercase). This is the single read
 * point for views (useDatabase) + the create/update hooks' optimistic updates
 * — the wire casing (uppercase) is re-applied on write below.
 */
function normalizeViewType(view: DatabaseView): DatabaseView {
  if (!view || typeof view.type !== "string") return view;
  const t = view.type.toLowerCase();
  // Cast is safe: we are translating the wire casing (UPPERCASE Prisma enum)
  // into the web's lowercase union; an unrecognized value falls through the
  // ViewRouter's `default` branch either way.
  return t === view.type ? view : ({ ...view, type: t } as DatabaseView);
}

/**
 * Lowercase a property's wire type (UPPERCASE Prisma enum) into the web's
 * lowercase `PropertyType` union, mirroring {@link normalizeViewType}.
 *
 * The API persists/returns property `type` UPPERCASE (`TITLE`, `SELECT`,
 * `DATE` …) but every web consumer compares lowercase
 * (`p.type === "date"`, `TYPE_META[p.type]`, the cell renderer's switch, the
 * property editor, `titlePropertySafe`). Without this the seeded Title/Status
 * survive only via `isTitle`/position fallbacks and every type-driven code
 * path (cell rendering, the `Add a title…` placeholder, filter/sort icons,
 * property editing) silently no-ops. The write path is unaffected: it sends
 * the union's own (lowercase) casing and the API's `propertyTypeSchema`
 * uppercases it. Applied once, at the read boundary (flattenDatabase).
 */
export function normalizePropertyType(property: DatabaseProperty): DatabaseProperty {
  if (!property || typeof property.type !== "string") return property;
  const t = property.type.toLowerCase();
  // Backfill deterministic ids on any id-less select/status/multi-select
  // options (the shared `normalizeSelectOptions` only acts when
  // `config.options` is an array, so non-select configs pass through). This
  // makes the cached option set id-conformant on the CLIENT side too, so
  // `findOption`'s id match succeeds even if a stale/legacy wire payload
  // still carries bare `{ name, color }` options — the root cause of the
  // "select works but the cell renders blank" symptom.
  const config = normalizeSelectOptions(property.config) as PropertyConfig;
  if (t === property.type && config === property.config) return property;
  return { ...property, type: t, config } as DatabaseProperty;
}

/**
 * Flatten the wire shape of `GET /databases/:id` into the web's flat
 * `DatabaseDto`.
 *
 * The API (database.service.get) returns the detail NESTED:
 *   `{ database: {id, workspaceId, title, icon, …}, properties, views,
 *      recordsCount, relations, formulas, rollups }`
 * but the web's `DatabaseDto` (and every consumer — the page header reads
 * `database.title`/`icon`, the view router reads `database.id`/`workspaceId`)
 * is FLAT: `id`, `workspaceId`, `title`, …, `properties`, `views`, `recordCount`.
 *
 * Without this translation `database.id`/`workspaceId`/`title` are all
 * `undefined`, so the entire database home — table view included, not just the
 * FORM view — degrades. This is the single read point, so the flatten lives
 * here (next to `normalizeViewType`); the wire stays nested. Tolerant of an
 * already-flat body (no `database` key) so the call stays idempotent.
 */
function flattenDatabase(body: unknown): DatabaseDto {
  const b = body as Record<string, unknown> | null | undefined;
  if (!b || typeof b !== "object") throw new Error("useDatabase: empty database detail");
  const nested = b.database as Record<string, unknown> | undefined;
  if (!nested) {
    // Already flat — normalize views + properties and return as-is.
    const flat = b as unknown as DatabaseDto;
    return {
      ...flat,
      properties: (flat.properties ?? []).map(normalizePropertyType),
      views: (flat.views ?? []).map(normalizeViewType)
    };
  }
  return {
    ...nested,
    properties: ((b.properties as DatabaseProperty[]) ?? []).map(normalizePropertyType),
    views: ((b.views as DatabaseView[]) ?? []).map(normalizeViewType),
    recordCount: (b.recordsCount as number | undefined) ?? undefined,
    createdBy: (nested.createdBy as string | undefined) ?? null
  } as DatabaseDto;
}

export function useDatabase(id: string | undefined) {
  return useQuery({
    queryKey: dbKey(id ?? "none"),
    enabled: !!id,
    queryFn: async (): Promise<DatabaseDto> => {
      const body: unknown = await apiFetch(`/databases/${id}`);
      const db: DatabaseDto = flattenDatabase(unwrap(body));
      return { ...db, views: (db.views ?? []).map(normalizeViewType) };
    }
  });
}

// ---------------------------------------------------------------------------
// useDatabaseRecords — POST /databases/:id/query (server-side engine)
//
// The server evaluates filter → sort → group → paginate (the 100k-record
// scale design in docs/api/databases.md), so filter/sort/group are always
// sent to the server — no local reimplementation of query logic. Cursor
// pagination is handled by useInfiniteQuery.
// ---------------------------------------------------------------------------

export interface UseDatabaseRecordsOptions {
  viewId?: string | null;
  filter?: FilterTree | null;
  sort?: SortSpec[] | null;
  group?: GroupSpec[] | null;
  pageSize?: number;
  includeArchived?: boolean;
  select?: string[];
  enabled?: boolean;
}

export interface UseDatabaseRecordsResult {
  /** All loaded pages concatenated (use `groups` when `group` is set). */
  data: QueryResult;
  isLoading: boolean;
  isFetching: boolean;
  error: Error | null;
  /** Fetch the next page (appends). */
  loadMore: () => void;
  /** Discard the last loaded page (step "back" a page). */
  goPrev: () => void;
  canPrev: boolean;
  hasMore: boolean;
  /** Reset to only the first page (after filter/view changes). */
  reset: () => void;
}

const EMPTY_RESULT: QueryResult = {
  records: [],
  groups: null,
  aggregates: null,
  totalCount: null,
  nextCursor: null
};

interface FlatPage {
  records: RecordDto[];
  groups: RecordGroup[] | null;
  totalCount: number | null;
  nextCursor: string | null;
}

export function useDatabaseRecords(
  databaseId: string | undefined,
  options: UseDatabaseRecordsOptions
): UseDatabaseRecordsResult {
  const { viewId, filter, sort, group, pageSize = 50, includeArchived = false, select, enabled = true } = options;

  const filterJson = filter ? JSON.stringify(filter) : null;
  const sortJson = sort && sort.length ? JSON.stringify(sort) : null;
  const groupJson = group && group.length ? JSON.stringify(group) : null;
  const selectKey = select?.join(",") ?? null;

  const infinite = useInfiniteQuery({
    queryKey: [
      "database",
      databaseId,
      "query",
      { viewId, filter: filterJson, sort: sortJson, group: groupJson, pageSize, includeArchived, select: selectKey }
    ],
    enabled: enabled && !!databaseId,
    initialPageParam: null as string | null,
    // TASK-0124 (repair): @tanstack/react-query v5 requires `getNextPageParam`
    // (the v4 name was `getPageParam`). It was dormant while the records fetch
    // 404'd (no successful page → never invoked); now that the §6 query
    // endpoint exists, a successful page without it threw
    // `options.getNextPageParam is not a function` and crashed the whole
    // database page. Mirrors FileBrowser.tsx (the v5 pattern).
    getNextPageParam: (last) => (last as FlatPage).nextCursor ?? undefined,
    queryFn: async ({ pageParam }): Promise<FlatPage> => {
      const body: Record<string, unknown> = { includeArchived, pageSize };
      if (viewId) body.viewId = viewId;
      if (filterJson) body.filter = JSON.parse(filterJson);
      if (sortJson) body.sort = JSON.parse(sortJson);
      if (groupJson) body.group = JSON.parse(groupJson);
      if (pageParam) body.cursor = pageParam;
      if (selectKey) body.select = selectKey.split(",");
      const data = unwrap<Record<string, unknown> | null>(
        await apiFetch(`/databases/${databaseId}/query`, {
          method: "POST",
          body: JSON.stringify(body)
        })
      );
      if (!data) return { records: [], groups: null, totalCount: null, nextCursor: null };
      // Server returns the grouped shape when a group is sent, flat otherwise.
      const hasGroups = Array.isArray((data as { groups?: unknown }).groups);
      const groups: RecordGroup[] | null = hasGroups
        ? ((data as { groups: RecordGroup[] }).groups ?? [])
        : null;
      const records: RecordDto[] = hasGroups
        ? (groups ?? []).flatMap((g) => g.records ?? [])
        : (((data as { records?: RecordDto[] }).records ?? []) as RecordDto[]);
      return {
        records,
        groups,
        totalCount: (data as { totalCount?: number }).totalCount ?? null,
        nextCursor: (data as { nextCursor?: string | null }).nextCursor ?? null
      };
    }
  });

  const reset = useCallback(() => {
    void infinite.setPageCount(1);
  }, [infinite.setPageCount]);

  const goPrev = useCallback(() => {
    if (infinite.data && infinite.data.pages.length > 1) {
      void infinite.setPageCount(infinite.data.pages.length - 1);
    }
  }, [infinite.data?.pages.length, infinite.setPageCount]);

  const data = useMemo<QueryResult>(() => {
    if (!infinite.data) return EMPTY_RESULT;
    const records = infinite.data.pages.flatMap((p) => p.records);
    const groups = infinite.data.pages.flatMap((p) => p.groups ?? []);
    const last = infinite.data.pages[infinite.data.pages.length - 1];
    return {
      records,
      groups: groups.length ? groups : null,
      aggregates: null,
      totalCount: last.totalCount,
      nextCursor: last.nextCursor
    };
  }, [infinite.data]);

  return {
    data,
    isLoading: infinite.isPending,
    isFetching: infinite.isFetching,
    error: (infinite.error as Error | null) ?? null,
    loadMore: () => {
      if (infinite.hasNextPage && !infinite.isFetchingNextPage) void infinite.fetchNextPage();
    },
    goPrev,
    canPrev: (infinite.data?.pages.length ?? 0) > 1,
    hasMore: infinite.hasNextPage,
    reset
  };
}

// ---------------------------------------------------------------------------
// useDatabaseRecord — GET /records/:recordId
// ---------------------------------------------------------------------------

export function useDatabaseRecord(_databaseId: string | undefined, recordId: string | undefined) {
  return useQuery({
    queryKey: ["record", recordId ?? "none"],
    enabled: !!recordId,
    queryFn: async (): Promise<RecordDto> => unwrap(await apiFetch(`/records/${recordId}`))
  });
}

// ---------------------------------------------------------------------------
// Cache helpers — record pages live under many query keys; patch by scanning.
// ---------------------------------------------------------------------------

/**
 * Optimistically apply a per-record transform to every cached page of this
 * database's record queries (table/board/gallery/list views all share the
 * `["database", id, "query", …]` key family).
 *
 * MUST go through `qc.setQueryData` with an IMMUTABLE new structure — never
 * mutate `query.state.data` in place. In-place mutation bypasses RQ's
 * reference-based change detection: the `useInfiniteQuery` data object keeps
 * the same identity, so consumers that memoize on `[infinite.data]` never
 * recompute and the grid renders a stale snapshot even though the cache
 * holds the right value. RQ v5 structural sharing makes this worse — when a
 * follow-up refetch returns the same value the optimistic patch already
 * wrote, it reuses the cached reference entirely. A fresh object graph from
 * `setQueryData` changes the reference, so the memo recomputes and the grid
 * reflects the edit immediately.
 */
function patchRecordsInCache(
  qc: ReturnType<typeof useQueryClient>,
  databaseId: string,
  transform: (record: RecordDto) => RecordDto
) {
  for (const query of qc.getQueryCache().getAll()) {
    const key = query.queryKey as readonly unknown[];
    if (key.length >= 3 && key[0] === "database" && key[1] === databaseId && key[2] === "query") {
      qc.setQueryData(key, (old: { pages: FlatPage[] } | undefined) => {
        if (!old?.pages) return old;
        const pages = old.pages.map((page) => ({
          ...page,
          records: page.records.map(transform),
          groups: page.groups?.map((g) => ({ ...g, records: g.records.map(transform) })) ?? null
        }));
        return { ...old, pages };
      });
    }
  }
}

// ---------------------------------------------------------------------------
// Mutations
// ---------------------------------------------------------------------------

export function useCreateDatabase() {
  const qc = useQueryClient();
  return useMutation({
    mutationFn: async (payload: {
      title: string;
      icon?: string;
      description?: string;
      pageId?: string | null;
      parentId?: string | null;
      properties?: { name: string; type: string; config?: PropertyConfig }[];
      views?: { name: string; type: string; group?: GroupSpec[] }[];
    }): Promise<DatabaseDto> =>
      unwrap(await apiFetch("/databases", { method: "POST", body: JSON.stringify(payload) })),
    onSuccess: (_db, variables) => {
      if (variables.pageId) qc.invalidateQueries({ queryKey: ["page", variables.pageId] });
    }
  });
}

export function useUpdateDatabase(databaseId: string) {
  const qc = useQueryClient();
  return useMutation({
    mutationFn: async (payload: {
      title?: string;
      icon?: string;
      description?: string;
      archived?: boolean;
    }): Promise<DatabaseDto> =>
      unwrap(await apiFetch(`/databases/${databaseId}`, {
        method: "PATCH",
        body: JSON.stringify(payload)
      })),
    onSuccess: (updated) => {
      qc.setQueryData(dbKey(databaseId), (old: DatabaseDto | undefined) =>
        old ? { ...old, ...updated } : old
      );
    }
  });
}

export function useCreateRecord(databaseId: string) {
  const qc = useQueryClient();
  return useMutation({
    mutationFn: async (payload: {
      /** Top-level record title (the title property's value). The API
       *  requires the title HERE and rejects it inside `properties`
       *  (`title_via_title_field` — query.service.ts), so callers must route
       *  the title property's value into this field instead of `properties`. */
      title?: string;
      properties: Record<string, RecordValue>;
      icon?: string;
      coverUrl?: string;
      position?: string;
      parentRecordId?: string | null;
    }): Promise<RecordDto> =>
      unwrap(await apiFetch(`/databases/${databaseId}/records`, {
        method: "POST",
        body: JSON.stringify(payload)
      })),
    onSuccess: () => {
      qc.invalidateQueries({ queryKey: dbKey(databaseId) });
    }
  });
}

export function useUpdateRecord(databaseId: string) {
  const qc = useQueryClient();
  return useMutation({
    mutationFn: async (payload: {
      recordId: string;
      /** Top-level record title (the title property's value). The API
       *  requires the title HERE and rejects it inside `properties`
       *  (`title_via_title_field` — query.service.ts). Pass
       *  `titlePropertyId` alongside so the optimistic merge below can
       *  update the title property's cached value too. */
      title?: string;
      titlePropertyId?: string;
      properties?: Record<string, RecordValue>;
      position?: string;
      icon?: string;
      version?: number;
      parentRecordId?: string | null;
    }): Promise<RecordDto> =>
      unwrap(
        await apiFetch(`/records/${payload.recordId}`, {
          method: "PATCH",
          headers: payload.version != null ? { "If-Match": String(payload.version) } : undefined,
          body: JSON.stringify({
            title: payload.title,
            properties: payload.properties,
            position: payload.position,
            icon: payload.icon,
            parentRecordId: payload.parentRecordId,
            version: payload.version
          })
        })
      ),
    onMutate: async (payload) => {
      await qc.cancelQueries({ queryKey: ["database", databaseId] });
      // Optimistic: merge the property patch (+ title) into every cached
      // page of this db (immutable — see patchRecordsInCache).
      patchRecordsInCache(qc, databaseId, (r) => {
        if (r.id !== payload.recordId) return r;
        const properties = { ...r.properties, ...(payload.properties ?? {}) };
        if (payload.titlePropertyId != null && payload.title != null) {
          properties[payload.titlePropertyId] = { text: payload.title, marks: [] };
        }
        return {
          ...r,
          version: (r.version ?? 0) + 1,
          ...(payload.title != null ? { title: payload.title } : {}),
          properties
        };
      });
      const prevRecord = qc.getQueryData<RecordDto>(["record", payload.recordId]);
      qc.setQueryData(["record", payload.recordId], (old: RecordDto | undefined) => {
        if (!old) return old;
        const properties = { ...old.properties, ...(payload.properties ?? {}) };
        if (payload.titlePropertyId != null && payload.title != null) {
          properties[payload.titlePropertyId] = { text: payload.title, marks: [] };
        }
        return {
          ...old,
          version: (old.version ?? 0) + 1,
          ...(payload.title != null ? { title: payload.title } : {}),
          properties
        };
      });
      return { prevRecord };
    },
    onError: (_err, _payload, ctx) => {
      // Rollback the optimistic write; the next refetch restores the truth.
      if (ctx?.prevRecord) qc.setQueryData(["record", _payload.recordId], ctx.prevRecord);
      qc.invalidateQueries({ queryKey: ["database", databaseId] });
    },
    onSuccess: (updated) => {
      qc.setQueryData(["record", updated.id], updated);
      // Reconcile the table/board/gallery record queries with the server
      // truth. The optimistic merge above covers the in-place flash, but the
      // infinite-query pages are a separate cache that the merge does not
      // reach for every consumer shape (e.g. a record created moments ago
      // whose version the grid hasn't seen, or a write routed through the
      // title field). Invalidating here makes every view refetch, so edits
      // never "disappear" and the cache can't drift from the DB.
      qc.invalidateQueries({ queryKey: ["database", databaseId] });
    }
  });
}

/**
 * Bulk update across many records. The documented surface is per-record
 * PATCH (docs/api/databases.md §7.3); the architecture mentions a single
 * `/records/batch` — when the API lands, swap the loop for one POST.
 */
export function useBulkUpdateRecords(databaseId: string) {
  const qc = useQueryClient();
  return useMutation({
    mutationFn: async (
      items: { recordId: string; properties: Record<string, RecordValue>; version?: number }[]
    ): Promise<{ succeeded: number; failed: { recordId: string; error: string }[] }> => {
      const results = await Promise.allSettled(
        items.map((it) =>
          apiFetch(`/records/${it.recordId}`, {
            method: "PATCH",
            headers: it.version != null ? { "If-Match": String(it.version) } : undefined,
            body: JSON.stringify({ properties: it.properties, version: it.version })
          })
        )
      );
      const failed: { recordId: string; error: string }[] = [];
      results.forEach((r, i) => {
        if (r.status === "rejected") {
          failed.push({ recordId: items[i].recordId, error: (r.reason as Error)?.message ?? "failed" });
        }
      });
      return { succeeded: results.length - failed.length, failed };
    },
    onSuccess: (_result, items) => {
      patchRecordsInCache(qc, databaseId, (r) => {
        const props = new Map(items.map((i) => [i.recordId, i.properties])).get(r.id);
        if (!props) return r;
        return {
          ...r,
          version: (r.version ?? 0) + 1,
          properties: { ...r.properties, ...props }
        };
      });
      qc.invalidateQueries({ queryKey: ["database", databaseId] });
    }
  });
}

export function useDeleteRecord(databaseId: string) {
  const qc = useQueryClient();
  return useMutation({
    mutationFn: async (recordId: string): Promise<RecordDto | null> => {
      // Hard delete requires `confirm` to equal the record id (typo protection).
      try {
        return unwrap(
          await apiFetch(`/records/${recordId}`, {
            method: "DELETE",
            body: JSON.stringify({ confirm: recordId })
          })
        );
      } catch (e) {
        // If hard delete is unavailable (404/405), fall back to archiving so
        // the UI always offers a recoverable path.
        const err = e as { code?: string; status?: number };
        if (err?.code === "NOT_FOUND" || err?.status === 404 || err?.status === 405) {
          return unwrap(await apiFetch(`/records/${recordId}/archive`, { method: "POST" }));
        }
        throw e;
      }
    },
    onSuccess: (_data, recordId) => {
      qc.removeQueries({ queryKey: ["record", recordId] });
      qc.invalidateQueries({ queryKey: ["database", databaseId] });
    }
  });
}

// --- Properties -------------------------------------------------------------

export function useCreateProperty(databaseId: string) {
  const qc = useQueryClient();
  return useMutation({
    mutationFn: async (payload: {
      name: string;
      type: string;
      position?: string;
      isVisible?: boolean;
      required?: boolean;
      config?: PropertyConfig;
      default?: unknown | null;
    }): Promise<DatabaseProperty> =>
      unwrap(await apiFetch(`/databases/${databaseId}/properties`, {
        method: "POST",
        body: JSON.stringify(payload)
      })),
    onSuccess: () => qc.invalidateQueries({ queryKey: dbKey(databaseId) })
  });
}

export function useUpdateProperty(databaseId: string) {
  const qc = useQueryClient();
  return useMutation({
    mutationFn: async (payload: {
      propertyId: string;
      name?: string;
      position?: string;
      isVisible?: boolean;
      required?: boolean;
      config?: PropertyConfig;
    }): Promise<DatabaseProperty> =>
      unwrap(
        await apiFetch(`/databases/${databaseId}/properties/${payload.propertyId}`, {
          method: "PATCH",
          body: JSON.stringify({
            name: payload.name,
            position: payload.position,
            isVisible: payload.isVisible,
            required: payload.required,
            config: payload.config
          })
        })
      ),
    onSuccess: () => qc.invalidateQueries({ queryKey: dbKey(databaseId) })
  });
}

export function useDeleteProperty(databaseId: string) {
  const qc = useQueryClient();
  return useMutation({
    mutationFn: async (propertyId: string): Promise<{ affectedRecords: number } | null> =>
      unwrap(await apiFetch(`/databases/${databaseId}/properties/${propertyId}`, { method: "DELETE" })),
    onSuccess: () => qc.invalidateQueries({ queryKey: dbKey(databaseId) })
  });
}

// --- Views ------------------------------------------------------------------

export function useCreateView(databaseId: string) {
  const qc = useQueryClient();
  return useMutation({
    mutationFn: async (payload: {
      name: string;
      type: string;
      config?: ViewConfig;
      filter?: FilterTree | null;
      sort?: SortSpec[] | null;
      group?: GroupSpec[] | null;
      pageSize?: number | null;
      position?: string;
    }): Promise<DatabaseView> =>
      unwrap(
        await apiFetch(`/databases/${databaseId}/views`, {
          method: "POST",
          // The wire contract is the UPPERCASE Prisma ViewType (the UI uses
          // lowercase); re-apply the casing on write (see normalizeViewType).
          body: JSON.stringify({ ...payload, type: payload.type.toUpperCase() })
        })
      ),
    onSuccess: () => qc.invalidateQueries({ queryKey: dbKey(databaseId) })
  });
}

export function useUpdateView(databaseId: string) {
  const qc = useQueryClient();
  return useMutation({
    mutationFn: async (payload: {
      viewId: string;
      name?: string;
      config?: ViewConfig;
      filter?: FilterTree | null;
      sort?: SortSpec[] | null;
      group?: GroupSpec[] | null;
      pageSize?: number | null;
    }): Promise<DatabaseView> =>
      unwrap(
        await apiFetch(`/databases/${databaseId}/views/${payload.viewId}`, {
          method: "PATCH",
          body: JSON.stringify({
            name: payload.name,
            config: payload.config,
            filter: payload.filter,
            sort: payload.sort,
            group: payload.group,
            pageSize: payload.pageSize
          })
        })
      ),
    onSuccess: (updated) => {
      const norm = normalizeViewType(updated);
      qc.setQueryData(dbKey(databaseId), (old: DatabaseDto | undefined) =>
        old
          ? { ...old, views: old.views.map((v) => (v.id === updated.id ? { ...v, ...norm } : v)) }
          : old
      );
    }
  });
}

export function useDeleteView(databaseId: string) {
  const qc = useQueryClient();
  return useMutation({
    mutationFn: async (viewId: string): Promise<void> =>
      unwrap(await apiFetch(`/databases/${databaseId}/views/${viewId}`, { method: "DELETE" })),
    onSuccess: () => qc.invalidateQueries({ queryKey: dbKey(databaseId) })
  });
}

/**
 * Reorder a view within the database (fractional `position`).
 * T-166: the real endpoint is `POST /databases/:id/views/:viewId/reorder`
 * with `{ after, before }` (view ids). The old body PATCHed `position`, which
 * the server's UpdateViewDto strips — so the call was a silent no-op and
 * "set as default" could never actually move the view.
 */
export function useReorderView(databaseId: string) {
  const qc = useQueryClient();
  return useMutation({
    mutationFn: async (payload: { viewId: string; after?: string; before?: string }): Promise<DatabaseView> =>
      unwrap(
        await apiFetch(`/databases/${databaseId}/views/${payload.viewId}/reorder`, {
          method: "POST",
          body: JSON.stringify({ after: payload.after, before: payload.before })
        })
      ),
    onSuccess: () => qc.invalidateQueries({ queryKey: dbKey(databaseId) })
  });
}

// --- Formulas ---------------------------------------------------------------

export interface FormulaValidationResult {
  ok: boolean;
  returnType?: string;
  samples?: { recordId: string; value: unknown; error?: string }[];
  error?: string;
  position?: number;
}

/**
 * Validate a formula expression server-side (safe AST parser — never eval,
 * PRODUCT-SPEC §9.6). POST /databases/:id/formulas/validate
 * { expression, sampleRecordIds }
 */
export function useValidateFormula(databaseId: string) {
  return useMutation({
    mutationFn: async (payload: { expression: string; sampleRecordIds?: string[] }): Promise<FormulaValidationResult> => {
      const data = unwrap<FormulaValidationResult | null>(
        await apiFetch(`/databases/${databaseId}/formulas/validate`, {
          method: "POST",
          body: JSON.stringify(payload)
        })
      );
      return (data ?? { ok: false }) as FormulaValidationResult;
    }
  });
}

// --- Relations (for relation cells) ------------------------------------------

/**
 * Resolve linked record titles for relation cells. The relation value carries
 * `recordIds`; targets are fetched lazily (first 3 shown, "+N" for the rest)
 * so table rows stay cheap on large databases.
 */
export function useRelations(
  _databaseId: string | undefined,
  targetDatabaseId: string | undefined,
  recordIds: string[]
) {
  const limited = (recordIds ?? []).slice(0, 3);
  return useQuery({
    queryKey: ["relations", targetDatabaseId, limited.join(",")],
    enabled: !!targetDatabaseId && limited.length > 0,
    queryFn: async () => {
      const results = await Promise.allSettled(
        limited.map(async (id) => {
          const record = unwrap<RecordDto>(await apiFetch(`/records/${id}`));
          return { recordId: id, record };
        })
      );
      // Unwrap the `PromiseFulfilledResult` → the inner `{ recordId, record }`.
      // Consumers read `x.recordId` / `x.record` directly; returning the raw
      // `{ status, value }` wrapper (the old behavior) made every lookup
      // `undefined` → relation chips always rendered the "Record" fallback.
      return results
        .filter(
          (r): r is PromiseFulfilledResult<{ recordId: string; record: RecordDto }> => r.status === "fulfilled"
        )
        .map((r) => r.value);
    }
  });
}

// --- CSV export (client-side, from the currently loaded records) -------------

function csvEscape(value: string): string {
  if (/[",\n]/.test(value)) return `"${value.replace(/"/g, '""')}"`;
  return value;
}

/**
 * Export the *currently loaded* records (client-side, no server round-trip)
 * as a CSV download. Columns = visible properties in order.
 */
export function useExportCsv(databaseId: string, properties: DatabaseProperty[]) {
  return useCallback(
    (records: RecordDto[], filename?: string) => {
      if (!records.length) return;
      const header = properties.map((p) => csvEscape(p.name)).join(",");
      const rows = records.map((r) =>
        properties.map((p) => csvEscape(valueToText(r.properties[p.id], p))).join(",")
      );
      const csv = [header, ...rows].join("\n");
      const blob = new Blob([csv], { type: "text/csv;charset=utf-8" });
      const url = URL.createObjectURL(blob);
      const a = document.createElement("a");
      a.href = url;
      a.download = filename ?? `database-${(databaseId ?? "export").slice(-8)}.csv`;
      a.click();
      URL.revokeObjectURL(url);
    },
    [databaseId, properties]
  );
}