1
import { sql } from "drizzle-orm";
2
 
3
import { MAX_TREE_DEPTH } from "@/shared/constants";
4
import type { ArchivedPage } from "@/shared/types";
5
import type { Db } from "@/worker/db/d1/client";
6
 
7
export type PageAncestorRow = {
8
  id: string;
9
  parent_id: string | null;
10
  title: string;
11
  icon: string | null;
12
  depth: number;
13
};
14
 
15
export type PageSubtreeRow = {
16
  id: string;
17
  workspace_id: string;
18
  parent_id: string | null;
19
  kind: "doc" | "canvas";
20
  title: string;
21
  icon: string | null;
22
  cover_url: string | null;
23
  position: number;
24
  created_by: string;
25
  created_at: string;
26
  updated_at: string;
27
  archived_at: string | null;
28
  archive_root_id: string | null;
29
  depth: number;
30
};
31
 
32
export type ArchivedAncestorRow = {
33
  id: string;
34
  parent_id: string | null;
35
  archived_at: string;
36
  archive_root_id: string | null;
37
  depth: number;
38
};
39
 
40
export type PageMoveValidationResult = { ok: true } | { ok: false; reason: "self_parent" | "cycle" | "depth_exceeded" };
41
 
42
export async function getPageAncestorChain(db: Db, pageId: string, workspaceId: string): Promise<PageAncestorRow[]> {
43
  return db.all<PageAncestorRow>(sql`
44
    WITH RECURSIVE ancestors(id, parent_id, title, icon, depth) AS (
45
      SELECT p.id, p.parent_id, p.title, p.icon, 0
46
      FROM pages p
47
      WHERE p.id = ${pageId}
48
        AND p.workspace_id = ${workspaceId}
49
        AND p.archived_at IS NULL
50
51
      UNION ALL
52
53
      SELECT p.id, p.parent_id, p.title, p.icon, a.depth + 1
54
      FROM pages p
55
      JOIN ancestors a ON p.id = a.parent_id
56
      WHERE p.workspace_id = ${workspaceId}
57
        AND p.archived_at IS NULL
58
        AND a.depth < ${MAX_TREE_DEPTH}
59
    )
60
    SELECT id, parent_id, title, icon, depth
61
    FROM ancestors
62
    ORDER BY depth ASC
63
  `);
64
}
65
 
66
export function getPageAncestorDepthFromChain(
67
  chain: Pick<PageAncestorRow, "id" | "parent_id" | "depth">[],
68
): number | null {
69
  if (chain.length === 0) {
70
    return 0;
71
  }
72
 
73
  const visited = new Set<string>();
74
  for (const row of chain) {
75
    if (visited.has(row.id)) return null;
76
    visited.add(row.id);
77
  }
78
 
79
  const deepest = chain[chain.length - 1];
80
  if (deepest.depth >= MAX_TREE_DEPTH && deepest.parent_id !== null) {
81
    return null;
82
  }
83
 
84
  return deepest.depth;
85
}
86
 
87
export async function getPageSubtreeMaxDepth(db: Db, pageId: string, workspaceId: string): Promise<number> {
88
  const rows = await db.all<{ max_depth: number | null }>(sql`
89
    WITH RECURSIVE descendants(id, depth) AS (
90
      SELECT p.id, 0
91
      FROM pages p
92
      WHERE p.id = ${pageId}
93
        AND p.workspace_id = ${workspaceId}
94
95
      UNION ALL
96
97
      SELECT child.id, d.depth + 1
98
      FROM pages child
99
      JOIN descendants d ON child.parent_id = d.id
100
      WHERE child.workspace_id = ${workspaceId}
101
        AND d.depth < ${MAX_TREE_DEPTH - 1}
102
    )
103
    SELECT MAX(depth) AS max_depth
104
    FROM descendants
105
  `);
106
 
107
  return rows[0]?.max_depth ?? 0;
108
}
109
 
110
export async function getPageSubtreeRows(db: Db, pageId: string, workspaceId: string): Promise<PageSubtreeRow[]> {
111
  return db.all<PageSubtreeRow>(sql`
112
    WITH RECURSIVE descendants(
113
      id,
114
      workspace_id,
115
      parent_id,
116
      kind,
117
      title,
118
      icon,
119
      cover_url,
120
      position,
121
      created_by,
122
      created_at,
123
      updated_at,
124
      archived_at,
125
      archive_root_id,
126
      depth
127
    ) AS (
128
      SELECT
129
        p.id,
130
        p.workspace_id,
131
        p.parent_id,
132
        p.kind,
133
        p.title,
134
        p.icon,
135
        p.cover_url,
136
        p.position,
137
        p.created_by,
138
        p.created_at,
139
        p.updated_at,
140
        p.archived_at,
141
        p.archive_root_id,
142
        0
143
      FROM pages p
144
      WHERE p.id = ${pageId}
145
        AND p.workspace_id = ${workspaceId}
146
147
      UNION ALL
148
149
      SELECT
150
        child.id,
151
        child.workspace_id,
152
        child.parent_id,
153
        child.kind,
154
        child.title,
155
        child.icon,
156
        child.cover_url,
157
        child.position,
158
        child.created_by,
159
        child.created_at,
160
        child.updated_at,
161
        child.archived_at,
162
        child.archive_root_id,
163
        d.depth + 1
164
      FROM pages child
165
      JOIN descendants d ON child.parent_id = d.id
166
      WHERE child.workspace_id = ${workspaceId}
167
        AND d.depth < ${MAX_TREE_DEPTH - 1}
168
    )
169
    SELECT
170
      id,
171
      workspace_id,
172
      parent_id,
173
      kind,
174
      title,
175
      icon,
176
      cover_url,
177
      position,
178
      created_by,
179
      created_at,
180
      updated_at,
181
      archived_at,
182
      archive_root_id,
183
      depth
184
    FROM descendants
185
    ORDER BY depth ASC, position ASC, id ASC
186
  `);
187
}
188
 
189
export async function getArchivedPageRootRows(
190
  db: Db,
191
  workspaceId: string,
192
  options: { createdBy?: string } = {},
193
): Promise<ArchivedPage[]> {
194
  // Trash is currently unpaginated. The query is indexed by archived roots, but
195
  // add cursor pagination before this surface can return large trash histories.
196
  const createdByClause = options.createdBy === undefined ? sql.empty() : sql`AND p.created_by = ${options.createdBy}`;
197
 
198
  return db.all<ArchivedPage>(sql`
199
    SELECT
200
      p.id,
201
      p.workspace_id,
202
      p.parent_id,
203
      p.kind,
204
      p.title,
205
      p.icon,
206
      p.cover_url,
207
      p.position,
208
      p.created_by,
209
      p.created_at,
210
      p.updated_at,
211
      p.archived_at,
212
      p.archive_root_id,
213
      (
214
        SELECT COUNT(*)
215
        FROM pages child
216
        WHERE child.workspace_id = p.workspace_id
217
          AND child.archive_root_id = p.id
218
          AND child.id <> p.id
219
      ) AS archived_descendant_count
220
    FROM pages p
221
    WHERE p.workspace_id = ${workspaceId}
222
      ${createdByClause}
223
      AND p.archived_at IS NOT NULL
224
      AND p.archive_root_id = p.id
225
    ORDER BY p.archived_at DESC
226
  `);
227
}
228
 
229
export async function getArchivedAncestorRows(
230
  db: Db,
231
  pageId: string,
232
  workspaceId: string,
233
): Promise<ArchivedAncestorRow[]> {
234
  return db.all<ArchivedAncestorRow>(sql`
235
    WITH RECURSIVE ancestors(id, parent_id, archived_at, archive_root_id, depth) AS (
236
      SELECT parent.id, parent.parent_id, parent.archived_at, parent.archive_root_id, 0
237
      FROM pages child
238
      JOIN pages parent ON parent.id = child.parent_id
239
      WHERE child.id = ${pageId}
240
        AND child.workspace_id = ${workspaceId}
241
        AND parent.workspace_id = ${workspaceId}
242
243
      UNION ALL
244
245
      SELECT parent.id, parent.parent_id, parent.archived_at, parent.archive_root_id, a.depth + 1
246
      FROM pages parent
247
      JOIN ancestors a ON parent.id = a.parent_id
248
      WHERE parent.workspace_id = ${workspaceId}
249
        AND a.depth < ${MAX_TREE_DEPTH - 1}
250
    )
251
    SELECT id, parent_id, archived_at, archive_root_id, depth
252
    FROM ancestors
253
    WHERE archived_at IS NOT NULL
254
    ORDER BY depth ASC
255
  `);
256
}
257
 
258
export async function validatePageMove(
259
  db: Db,
260
  pageId: string,
261
  newParentId: string,
262
  workspaceId: string,
263
): Promise<PageMoveValidationResult> {
264
  if (newParentId === pageId) {
265
    return { ok: false, reason: "self_parent" };
266
  }
267
 
268
  const parentChain = await getPageAncestorChain(db, newParentId, workspaceId);
269
  if (parentChain.some((ancestor) => ancestor.id === pageId)) {
270
    return { ok: false, reason: "cycle" };
271
  }
272
 
273
  const parentDepth = getPageAncestorDepthFromChain(parentChain);
274
  const subtreeDepth = await getPageSubtreeMaxDepth(db, pageId, workspaceId);
275
 
276
  if (parentDepth === null || parentDepth + 1 + subtreeDepth >= MAX_TREE_DEPTH) {
277
    return { ok: false, reason: "depth_exceeded" };
278
  }
279
 
280
  return { ok: true };
281
}
282
 
283
export async function archivePageSubtree(
284
  db: Db,
285
  pageId: string,
286
  workspaceId: string,
287
  archivedAt: string,
288
): Promise<void> {
289
  await updatePageSubtreeArchiveState(db, pageId, workspaceId, {
290
    mode: "archive",
291
    updatedAt: archivedAt,
292
  });
293
}
294
 
295
export async function restorePageSubtree(
296
  db: Db,
297
  pageId: string,
298
  workspaceId: string,
299
  updatedAt: string,
300
): Promise<void> {
301
  await updatePageSubtreeArchiveState(db, pageId, workspaceId, {
302
    mode: "restore",
303
    updatedAt,
304
  });
305
}
306
 
307
type PageSubtreeArchiveMutation =
308
  | {
309
      mode: "archive";
310
      updatedAt: string;
311
    }
312
  | {
313
      mode: "restore";
314
      updatedAt: string;
315
    };
316
 
317
async function updatePageSubtreeArchiveState(
318
  db: Db,
319
  pageId: string,
320
  workspaceId: string,
321
  mutation: PageSubtreeArchiveMutation,
322
): Promise<void> {
323
  const setClause =
324
    mutation.mode === "archive"
325
      ? sql`
326
          archived_at = ${mutation.updatedAt},
327
          archive_root_id = ${pageId},
328
          updated_at = ${mutation.updatedAt}
329
        `
330
      : sql`
331
          archived_at = NULL,
332
          archive_root_id = NULL,
333
          updated_at = ${mutation.updatedAt}
334
        `;
335
  const rowPredicate =
336
    mutation.mode === "archive"
337
      ? sql`archived_at IS NULL`
338
      : sql`workspace_id = ${workspaceId} AND archive_root_id = ${pageId}`;
339
 
340
  await db.run(sql`
341
    WITH RECURSIVE descendants(id, depth) AS (
342
      SELECT id, 0
343
      FROM pages
344
      WHERE id = ${pageId}
345
        AND workspace_id = ${workspaceId}
346
347
      UNION ALL
348
349
      SELECT child.id, d.depth + 1
350
      FROM pages child
351
      JOIN descendants d ON child.parent_id = d.id
352
      WHERE child.workspace_id = ${workspaceId}
353
        AND d.depth < ${MAX_TREE_DEPTH - 1}
354
    )
355
    UPDATE pages
356
    SET ${setClause}
357
    -- Archive keeps workspace scoping in the descendants CTE and updates by
358
    -- primary-key id. Adding workspace_id here makes SQLite prefer
359
    -- idx_pages_workspace over primary-key lookups for small subtrees.
360
    -- Restore keeps workspace_id here so idx_pages_archive_operation narrows
361
    -- the operation before intersecting with the descendant ids.
362
    WHERE ${rowPredicate}
363
      AND id IN (SELECT id FROM descendants)
364
  `);
365
}