1
import { sql } from "drizzle-orm";
2
 
3
import type { Db } from "@/worker/db/d1/client";
4
import { MAX_TREE_DEPTH } from "@/shared/constants";
5
import type { PageKind } from "@/shared/types";
6
 
7
const RESOLVE_MENTIONS_BATCH_SIZE = 90;
8
 
9
// Local D1/SQLite planner note:
10
// The recursive ancestor CTE is bounded by MAX_TREE_DEPTH, while
11
// published_pages can grow with every published root in a workspace. A normal
12
// JOIN let SQLite reorder the join and search published_pages by workspace_id
13
// only, then build an automatic index over the tiny CTE. CROSS JOIN keeps the
14
// bounded CTE as the outer loop so EXPLAIN QUERY PLAN shows published_pages
15
// probed by its composite primary key: workspace_id=? AND page_id=?.
16
 
17
export interface PagePublishStatus {
18
  page: { id: string; kind: PageKind; title: string; archived_at: string | null } | null;
19
  is_explicit_root: boolean;
20
  inherited_from: { id: string; title: string; icon: string | null } | null;
21
}
22
 
23
/**
24
 * Resolve publication status for a single page, walking unarchived ancestors
25
 * looking for the nearest `published_pages` row. Used by the site-status
26
 * endpoint.
27
 *
28
 * - `is_explicit_root` is true iff there is a direct row in `published_pages`
29
 *   for this page in this workspace.
30
 * - `inherited_from` is the nearest unarchived ancestor that is an explicit
31
 *   publish root. It is independent of `is_explicit_root` (a page can be both
32
 *   a direct root AND inherit from an ancestor; the response surfaces both).
33
 * - An archived ancestor between the page and a published ancestor breaks
34
 *   the chain because the CTE filters `archived_at IS NULL` at every step.
35
 */
36
export async function resolvePagePublishStatus(
37
  db: Db,
38
  workspaceId: string,
39
  pageId: string,
40
): Promise<PagePublishStatus> {
41
  const pageRow = await db.all<{ id: string; kind: PageKind; title: string; archived_at: string | null }>(sql`
42
    SELECT id, kind, title, archived_at
43
    FROM pages
44
    WHERE id = ${pageId} AND workspace_id = ${workspaceId}
45
    LIMIT 1
46
  `);
47
  const page = pageRow[0] ?? null;
48
  if (!page) {
49
    return { page: null, is_explicit_root: false, inherited_from: null };
50
  }
51
 
52
  const rows = await db.all<{ page_id: string; title: string; icon: string | null; depth: number }>(sql`
53
    WITH RECURSIVE
54
    ancestors(id, parent_id, title, icon, depth) AS (
55
      SELECT p.id, p.parent_id, p.title, p.icon, 0
56
      FROM pages p
57
      WHERE p.id = ${pageId}
58
        AND p.workspace_id = ${workspaceId}
59
        AND p.archived_at IS NULL
60
61
      UNION ALL
62
63
      SELECT p.id, p.parent_id, p.title, p.icon, a.depth + 1
64
      FROM pages p
65
      JOIN ancestors a ON p.id = a.parent_id
66
      WHERE p.workspace_id = ${workspaceId}
67
        AND p.archived_at IS NULL
68
        AND a.depth < ${MAX_TREE_DEPTH - 1}
69
    )
70
    SELECT a.id AS page_id, a.title, a.icon, a.depth
71
    FROM ancestors a
72
    CROSS JOIN published_pages pp
73
    WHERE pp.workspace_id = ${workspaceId}
74
      AND pp.page_id = a.id
75
    ORDER BY a.depth ASC
76
    LIMIT 2
77
  `);
78
 
79
  const direct = rows.find((r) => r.depth === 0);
80
  const ancestor = rows.find((r) => r.depth > 0);
81
 
82
  return {
83
    page,
84
    is_explicit_root: Boolean(direct),
85
    inherited_from: ancestor ? { id: ancestor.page_id, title: ancestor.title, icon: ancestor.icon } : null,
86
  };
87
}
88
 
89
export interface ResolvedSite {
90
  workspace_id: string;
91
  slug: string;
92
  home_page_id: string | null;
93
  published_at: string;
94
  updated_at: string;
95
  workspace_name: string;
96
  workspace_icon: string | null;
97
}
98
 
99
export interface ResolvedPublishedPage {
100
  id: string;
101
  workspace_id: string;
102
  kind: PageKind;
103
  title: string;
104
  icon: string | null;
105
  cover_url: string | null;
106
  updated_at: string;
107
  // Nearest ancestor with a published_pages row, or the page itself if it is
108
  // an explicit root.
109
  published_root_id: string;
110
}
111
 
112
export interface ResolvedPublishedSitePage {
113
  site: ResolvedSite;
114
  page: ResolvedPublishedPage | null;
115
}
116
 
117
/**
118
 * Resolve a published site and requested public page in one D1 query.
119
 *
120
 * Returns null only when the published site itself is missing. Page failures
121
 * stay collapsed to `page: null` so callers can render the same site-branded
122
 * 404 for missing, archived, non-doc, cross-workspace, or unpublished targets.
123
 */
124
export async function resolvePublishedSitePage(
125
  db: Db,
126
  slug: string,
127
  requestedPath: string,
128
  requestedPageId: string | null,
129
): Promise<ResolvedPublishedSitePage | null> {
130
  const rows = await db.all<{
131
    workspace_id: string;
132
    slug: string;
133
    home_page_id: string | null;
134
    published_at: string;
135
    updated_at: string;
136
    workspace_name: string;
137
    workspace_icon: string | null;
138
    page_id: string | null;
139
    page_workspace_id: string | null;
140
    page_kind: PageKind | null;
141
    page_title: string | null;
142
    page_icon: string | null;
143
    page_cover_url: string | null;
144
    page_updated_at: string | null;
145
    published_root_id: string | null;
146
  }>(sql`
147
    WITH RECURSIVE
148
    site AS (
149
      SELECT
150
        ws.workspace_id,
151
        ws.slug,
152
        ws.home_page_id,
153
        ws.published_at,
154
        ws.updated_at,
155
        w.name AS workspace_name,
156
        w.icon AS workspace_icon
157
      FROM workspace_sites ws
158
      JOIN workspaces w ON w.id = ws.workspace_id
159
      WHERE ws.slug = ${slug}
160
        AND ws.published_at IS NOT NULL
161
      LIMIT 1
162
    ),
163
    target AS (
164
      SELECT
165
        site.workspace_id,
166
        CASE WHEN ${requestedPath} = '/' THEN site.home_page_id ELSE ${requestedPageId} END AS page_id
167
      FROM site
168
    ),
169
    ancestors(workspace_id, root_id, id, parent_id, depth) AS (
170
      SELECT t.workspace_id, p.id, p.id, p.parent_id, 0
171
      FROM target t
172
      JOIN pages p ON p.id = t.page_id
173
      WHERE p.workspace_id = t.workspace_id
174
        AND p.archived_at IS NULL
175
176
      UNION ALL
177
178
      SELECT a.workspace_id, a.root_id, p.id, p.parent_id, a.depth + 1
179
      FROM pages p
180
      JOIN ancestors a ON p.id = a.parent_id
181
      WHERE p.workspace_id = a.workspace_id
182
        AND p.archived_at IS NULL
183
        AND a.depth < ${MAX_TREE_DEPTH - 1}
184
    ),
185
    nearest_published AS (
186
      SELECT a.root_id, a.id AS published_root_id, a.depth
187
      FROM ancestors a
188
      CROSS JOIN published_pages pp
189
      WHERE pp.workspace_id = a.workspace_id
190
        AND pp.page_id = a.id
191
      ORDER BY a.depth ASC
192
      LIMIT 1
193
    )
194
    SELECT
195
      site.workspace_id,
196
      site.slug,
197
      site.home_page_id,
198
      site.published_at,
199
      site.updated_at,
200
      site.workspace_name,
201
      site.workspace_icon,
202
      p.id AS page_id,
203
      p.workspace_id AS page_workspace_id,
204
      p.kind AS page_kind,
205
      p.title AS page_title,
206
      p.icon AS page_icon,
207
      p.cover_url AS page_cover_url,
208
      p.updated_at AS page_updated_at,
209
      np.published_root_id
210
    FROM site
211
    LEFT JOIN target t ON t.workspace_id = site.workspace_id
212
    LEFT JOIN nearest_published np ON np.root_id = t.page_id
213
    LEFT JOIN pages p
214
      ON p.id = t.page_id
215
      AND p.workspace_id = site.workspace_id
216
      AND p.archived_at IS NULL
217
      AND p.kind = 'doc'
218
      AND np.root_id = p.id
219
    LIMIT 1
220
  `);
221
 
222
  const row = rows[0];
223
  if (!row) return null;
224
 
225
  const site: ResolvedSite = {
226
    workspace_id: row.workspace_id,
227
    slug: row.slug,
228
    home_page_id: row.home_page_id,
229
    published_at: row.published_at,
230
    updated_at: row.updated_at,
231
    workspace_name: row.workspace_name,
232
    workspace_icon: row.workspace_icon,
233
  };
234
 
235
  const page: ResolvedPublishedPage | null =
236
    row.page_id === null
237
      ? null
238
      : {
239
          id: row.page_id,
240
          workspace_id: row.page_workspace_id!,
241
          kind: row.page_kind!,
242
          title: row.page_title!,
243
          icon: row.page_icon,
244
          cover_url: row.page_cover_url,
245
          updated_at: row.page_updated_at!,
246
          published_root_id: row.published_root_id!,
247
        };
248
 
249
  return { site, page };
250
}
251
 
252
export interface ResolvedMention {
253
  pageId: string;
254
  reachable: boolean;
255
  title: string | null;
256
  icon: string | null;
257
}
258
 
259
/**
260
 * Batch-resolve mention pageIds against the published set for a single site.
261
 * Mention pageIds that are missing, archived, in another workspace, canvas, or
262
 * not reachable from a published root all collapse to `reachable: false` so
263
 * the renderer can redact the pageId attr before emitting HTML.
264
 */
265
export async function resolvePublishedMentions(
266
  db: Db,
267
  workspaceId: string,
268
  pageIds: string[],
269
): Promise<Map<string, ResolvedMention>> {
270
  const unique = [...new Set(pageIds)].filter((p) => p.length > 0);
271
  const result = new Map<string, ResolvedMention>();
272
  for (const id of unique) {
273
    result.set(id, { pageId: id, reachable: false, title: null, icon: null });
274
  }
275
  if (unique.length === 0) return result;
276
 
277
  for (let i = 0; i < unique.length; i += RESOLVE_MENTIONS_BATCH_SIZE) {
278
    const rows = await resolvePublishedMentionBatch(db, workspaceId, unique.slice(i, i + RESOLVE_MENTIONS_BATCH_SIZE));
279
    for (const row of rows) {
280
      result.set(row.page_id, {
281
        pageId: row.page_id,
282
        reachable: true,
283
        title: row.title,
284
        icon: row.icon,
285
      });
286
    }
287
  }
288
 
289
  return result;
290
}
291
 
292
async function resolvePublishedMentionBatch(
293
  db: Db,
294
  workspaceId: string,
295
  pageIds: string[],
296
): Promise<Array<{ page_id: string; title: string; icon: string | null }>> {
297
  const values = sql.join(
298
    pageIds.map((id) => sql`(${id})`),
299
    sql`, `,
300
  );
301
 
302
  return db.all<{ page_id: string; title: string; icon: string | null }>(sql`
303
    WITH RECURSIVE
304
    requested(root_id) AS (VALUES ${values}),
305
    ancestors(root_id, id, parent_id, depth) AS (
306
      SELECT r.root_id, p.id, p.parent_id, 0
307
      FROM requested r
308
      JOIN pages p ON p.id = r.root_id
309
      WHERE p.workspace_id = ${workspaceId}
310
        AND p.archived_at IS NULL
311
        AND p.kind = 'doc'
312
313
      UNION ALL
314
315
      SELECT a.root_id, p.id, p.parent_id, a.depth + 1
316
      FROM pages p
317
      JOIN ancestors a ON p.id = a.parent_id
318
      WHERE p.workspace_id = ${workspaceId}
319
        AND p.archived_at IS NULL
320
        AND a.depth < ${MAX_TREE_DEPTH - 1}
321
    ),
322
    reachable AS (
323
      SELECT DISTINCT a.root_id
324
      FROM ancestors a
325
      CROSS JOIN published_pages pp
326
      WHERE pp.workspace_id = ${workspaceId}
327
        AND pp.page_id = a.id
328
    )
329
    SELECT p.id AS page_id, p.title, p.icon
330
    FROM reachable r
331
    JOIN pages p ON p.id = r.root_id
332
    WHERE p.workspace_id = ${workspaceId}
333
      AND p.archived_at IS NULL
334
  `);
335
}
336
 
337
export function isSitesFeatureEnabled(env: Pick<Env, "PUBLISHED_SITE_DOMAIN">): boolean {
338
  return Boolean(env.PUBLISHED_SITE_DOMAIN?.trim());
339
}
340
 
341
export function getSitesBaseDomain(env: Pick<Env, "PUBLISHED_SITE_DOMAIN">): string | null {
342
  const v = env.PUBLISHED_SITE_DOMAIN?.trim();
343
  return v ? v : null;
344
}