spb/note-spboucher-ai

1/** Loads everything an export needs for a workspace (or a subtree) in a few queries. Next-only (used by /api/export). */2import { and, asc, eq, inArray, isNull, sql as dsql } from "drizzle-orm";3import { db, schema } from "@/db";4import { slugify } from "@/lib/utils";5import { displayValue } from "@/lib/databases/values";6import type { Property } from "@/lib/databases/types";78export type PageRow = typeof schema.pages.$inferSelect;9export type DatabaseRow = typeof schema.databases.$inferSelect;10export type PropertyRow = typeof schema.databaseProperties.$inferSelect;11export type RowRow = typeof schema.databaseRows.$inferSelect;12export type AttachmentRow = typeof schema.attachments.$inferSelect;13export type ReferenceRow = typeof schema.researchReferences.$inferSelect;1415export interface ExportSet {16  workspace: { id: string; name: string; slug: string; createdAt: Date };17  rootId: string | null;18  pages: PageRow[];19  databases: DatabaseRow[]; properties: PropertyRow[]; rows: RowRow[]; views: (typeof schema.databaseViews.$inferSelect)[];20  attachments: AttachmentRow[]; references: ReferenceRow[]; tags: (typeof schema.tags.$inferSelect)[]; pageTags: { pageId: string; tagId: string }[]; tasks: (typeof schema.tasks.$inferSelect)[];21}2223const chunked = async <T, R>(ids: T[], fn: (part: T[]) => Promise<R[]>, size = 500) => { const out: R[] = []; for (let i = 0; i < ids.length; i += size) out.push(...(await fn(ids.slice(i, i + size)))); return out; };2425export async function loadExportSet(wsId: string, rootId?: string | null): Promise<ExportSet> {26  const [ws] = await db.select({ id: schema.workspaces.id, name: schema.workspaces.name, slug: schema.workspaces.slug, createdAt: schema.workspaces.createdAt }).from(schema.workspaces).where(eq(schema.workspaces.id, wsId));27  if (!ws) throw new Error("Workspace not found");28  let pages: PageRow[];29  if (rootId) {30    const ids = (await db.execute(dsql`with recursive t as (select id from pages where id = ${rootId} and workspace_id = ${wsId} and deleted_at is null union all select p.id from pages p join t on (p.parent_id = t.id) where p.deleted_at is null) select id from t`)) as unknown as { id: string }[];31    const idList = ids.map((r) => r.id); if (!idList.length) throw new Error("Page not found");32    pages = await chunked(idList, (part) => db.select().from(schema.pages).where(inArray(schema.pages.id, part)));33    // row pages of databases hosted inside the subtree (their parent_id may be null)34    const dbs = await db.select({ id: schema.databases.id }).from(schema.databases).where(and(eq(schema.databases.workspaceId, wsId), isNull(schema.databases.deletedAt), inArray(schema.databases.pageId, idList)));35    if (dbs.length) { const have = new Set(pages.map((p) => p.id)); const rowsPages = await db.select().from(schema.pages).where(and(inArray(schema.pages.databaseId, dbs.map((d) => d.id)), isNull(schema.pages.deletedAt))); for (const rp of rowsPages) if (!have.has(rp.id)) pages.push(rp); }36  } else pages = await db.select().from(schema.pages).where(and(eq(schema.pages.workspaceId, wsId), isNull(schema.pages.deletedAt))).orderBy(asc(schema.pages.position), asc(schema.pages.createdAt));37  const pageIds = pages.map((p) => p.id); const pageSet = new Set(pageIds);38  const databases = (await db.select().from(schema.databases).where(and(eq(schema.databases.workspaceId, wsId), isNull(schema.databases.deletedAt)))).filter((d) => pageSet.has(d.pageId));39  const dbIds = databases.map((d) => d.id);40  const properties = dbIds.length ? (await db.select().from(schema.databaseProperties).where(inArray(schema.databaseProperties.databaseId, dbIds)).orderBy(asc(schema.databaseProperties.position))) : [];41  const rows = dbIds.length ? (await db.select().from(schema.databaseRows).where(and(inArray(schema.databaseRows.databaseId, dbIds), isNull(schema.databaseRows.deletedAt))).orderBy(asc(schema.databaseRows.position))) : [];42  const views = dbIds.length ? await db.select().from(schema.databaseViews).where(inArray(schema.databaseViews.databaseId, dbIds)).orderBy(asc(schema.databaseViews.position)) : [];43  const allAtt = await db.select().from(schema.attachments).where(and(eq(schema.attachments.workspaceId, wsId), isNull(schema.attachments.deletedAt)));44  const attachments = rootId ? allAtt.filter((a) => a.pageId && pageSet.has(a.pageId)) : allAtt;45  const allRefs = await db.select().from(schema.researchReferences).where(and(eq(schema.researchReferences.workspaceId, wsId), isNull(schema.researchReferences.deletedAt)));46  const references = rootId ? allRefs.filter((r) => r.pageId && pageSet.has(r.pageId)) : allRefs;47  const tags = await db.select().from(schema.tags).where(eq(schema.tags.workspaceId, wsId));48  const pageTags = pageIds.length ? await chunked(pageIds, (part) => db.select().from(schema.pageTags).where(inArray(schema.pageTags.pageId, part))) : [];49  const allTasks = await db.select().from(schema.tasks).where(and(eq(schema.tasks.workspaceId, wsId), isNull(schema.tasks.deletedAt)));50  const tasks = rootId ? allTasks.filter((t) => t.pageId && pageSet.has(t.pageId)) : allTasks;51  return { workspace: ws, rootId: rootId ?? null, pages, databases, properties, rows, views, attachments, references, tags, pageTags, tasks };52}5354/** Tag names per page (page_tags ∪ properties.tags). */55export function tagsOf(set: ExportSet, page: PageRow): string[] {56  const byId = new Map(set.tags.map((t) => [t.id, t.name]));57  const fromTable = set.pageTags.filter((pt) => pt.pageId === page.id).map((pt) => byId.get(pt.tagId)).filter((x): x is string => Boolean(x));58  const fromProps = Array.isArray((page.properties as any)?.tags) ? ((page.properties as any).tags as unknown[]).map(String) : [];59  return [...new Set([...fromTable, ...fromProps])];60}6162/** Property values of a row page keyed by property *name* (database_rows.values is keyed by property id). */63export function rowValuesByName(set: ExportSet, page: PageRow): Record<string, unknown> | null {64  if (!page.databaseId) return null;65  const row = set.rows.find((r) => r.pageId === page.id); if (!row) return null;66  const props = set.properties.filter((p) => p.databaseId === page.databaseId && p.type !== "title").sort((a, b) => a.position - b.position);67  const out: Record<string, unknown> = {};68  for (const p of props) {69    const v = (row.values as any)?.[p.id] ?? (row.values as any)?.[p.name]; if (v === undefined || v === null || v === "") continue;70    if (p.type === "select" || p.type === "status" || p.type === "multi_select" || p.type === "relation" || p.type === "file") { try { out[p.name] = displayValue({ id: p.id, databaseId: p.databaseId, name: p.name, type: p.type as Property["type"], config: (p.config ?? {}) as Property["config"], position: p.position, width: p.width }, v); continue; } catch {} }71    out[p.name] = v;72  }73  return out;74}7576export interface Placed { page: PageRow; file: string; dir: string; depth: number }77/**78 * Folder hierarchy mirroring parents: page → `<dir>/<slug>.md`, its children → `<dir>/<slug>/…`.79 * Database row pages are always placed under the database page's folder.80 */81export function assignPaths(set: ExportSet): Map<string, Placed> {82  const dbPageByDbId = new Map(set.databases.map((d) => [d.id, d.pageId]));83  const inSet = new Set(set.pages.map((p) => p.id));84  const parentOf = (p: PageRow) => { if (p.databaseId && dbPageByDbId.has(p.databaseId)) return dbPageByDbId.get(p.databaseId)!; return p.parentId && inSet.has(p.parentId) ? p.parentId : null; };85  const children = new Map<string | null, PageRow[]>();86  for (const p of set.pages) { const k = parentOf(p); if (!children.has(k)) children.set(k, []); children.get(k)!.push(p); }87  for (const arr of children.values()) arr.sort((a, b) => a.position - b.position || a.createdAt.getTime() - b.createdAt.getTime());88  const out = new Map<string, Placed>();89  const walk = (parent: string | null, dir: string, depth: number) => {90    const used = new Set<string>();91    for (const p of children.get(parent) ?? []) {92      let base = slugify(p.title).slice(0, 80) || "untitled"; let slug = base; let n = 2; while (used.has(slug)) slug = `${base}-${n++}`; used.add(slug);93      const file = dir ? `${dir}/${slug}.md` : `${slug}.md`; const sub = dir ? `${dir}/${slug}` : slug;94      out.set(p.id, { page: p, file, dir: sub, depth }); walk(p.id, sub, depth + 1);95    }96  };97  walk(null, "", 0);98  // pages whose parent chain is broken (should not happen) → root99  for (const p of set.pages) if (!out.has(p.id)) { const slug = `${slugify(p.title) || "untitled"}-${p.id.slice(0, 8)}`; out.set(p.id, { page: p, file: `${slug}.md`, dir: slug, depth: 0 }); }100  return out;101}102export const attachmentFile = (a: { id: string; filename: string }) => `attachments/${a.id.slice(0, 8)}-${a.filename.replace(/[\\/:*?"<>|\u0000-\u001f]/g, "_").slice(0, 150)}`;103