attachmentRepository.ts

/**
 * 附件仓储。
 *
 * 这里负责附件记录本身的查询和写入，以及“附件 -> 工作区”的关联查询。
 * 真实文件是否存在、是否需要删除，由上层服务或路由根据路径再处理。
 */

import path from "node:path";
import { config } from "../config";
import type { AttachmentRecord } from "../types";
import { db, now } from "./shared";

/**
 * 按内容块批量加载附件。
 */
export function listAttachmentsByBlockIds(blockIds: number[]): AttachmentRecord[] {
  if (blockIds.length === 0) {
    return [];
  }

  const placeholders = blockIds.map(() => "?").join(", ");
  const rows = db
    .prepare(
      `
        SELECT *
        FROM attachments
        WHERE block_id IN (${placeholders})
        ORDER BY id ASC
      `
    )
    .all(...blockIds) as unknown[];
  return rows as AttachmentRecord[];
}

/**
 * 查询附件及其所属工作区信息。
 *
 * 媒体访问、附件删除等场景都需要先拿到这份关联结果，
 * 才能同时完成权限判断和磁盘路径定位。
 */
export function findAttachmentWithWorkspace(
  attachmentId: number
): {
  attachment: AttachmentRecord;
  workspaceSlug: string;
  workspaceAccessPasswordHash: string | null;
  workspaceAccessPasswordUpdatedAt: string | null;
  absoluteFilePath: string;
} | null {
  const row = db
    .prepare(
      `
        SELECT
          a.id,
          a.block_id,
          a.storage_name,
          a.original_name,
          a.mime_type,
          a.size_bytes,
          a.created_at,
          a.updated_at,
          w.slug AS workspace_slug,
          w.access_password_hash AS workspace_access_password_hash,
          w.access_password_updated_at AS workspace_access_password_updated_at
        FROM attachments a
        JOIN blocks b ON a.block_id = b.id
        JOIN workspaces w ON b.workspace_id = w.id
        WHERE a.id = ?
      `
    )
    .get(attachmentId) as
    | (AttachmentRecord & {
        workspace_slug: string;
        workspace_access_password_hash: string | null;
        workspace_access_password_updated_at: string | null;
      })
    | undefined;

  if (!row) {
    return null;
  }

  const {
    workspace_slug,
    workspace_access_password_hash,
    workspace_access_password_updated_at,
    ...attachment
  } = row;

  return {
    attachment,
    workspaceSlug: workspace_slug,
    workspaceAccessPasswordHash: workspace_access_password_hash,
    workspaceAccessPasswordUpdatedAt: workspace_access_password_updated_at,
    absoluteFilePath: path.join(config.uploadRootDir, workspace_slug, attachment.storage_name)
  };
}

/**
 * 向现有文件块追加附件。
 */
export function addAttachmentToBlock(
  blockId: number,
  file: {
    storageName: string;
    originalName: string;
    mimeType: string | null;
    sizeBytes: number | null;
  }
): AttachmentRecord {
  const timestamp = now();
  const result = db.prepare(
    "INSERT INTO attachments (block_id, storage_name, original_name, mime_type, size_bytes, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)"
  ).run(
    blockId,
    file.storageName,
    file.originalName,
    file.mimeType,
    file.sizeBytes,
    timestamp,
    timestamp
  ) as { lastInsertRowid: number | bigint };

  const attachment = db.prepare("SELECT * FROM attachments WHERE id = ?").get(Number(result.lastInsertRowid)) as
    | AttachmentRecord
    | undefined;
  if (!attachment) {
    throw new Error("Failed to create attachment");
  }

  return attachment;
}

/**
 * 删除单个附件记录。
 */
export function deleteAttachment(attachmentId: number): AttachmentRecord | null {
  const attachment = db.prepare("SELECT * FROM attachments WHERE id = ?").get(attachmentId) as
    | AttachmentRecord
    | undefined;
  if (!attachment) {
    return null;
  }

  db.prepare("DELETE FROM attachments WHERE id = ?").run(attachmentId);
  return attachment;
}

/**
 * 统计文件块下的附件数量。
 */
export function countAttachmentsByBlockId(blockId: number): number {
  const result = db.prepare("SELECT COUNT(*) as count FROM attachments WHERE block_id = ?").get(blockId) as {
    count: number;
  };
  return result.count;
}

/**
 * 按附件 ID 批量查询附件，并校验它们属于指定工作区。
 */
export function findAttachmentsByIds(
  attachmentIds: number[],
  workspaceId: number
): Array<AttachmentRecord & { workspaceSlug: string }> {
  if (attachmentIds.length === 0) {
    return [];
  }

  const placeholders = attachmentIds.map(() => "?").join(",");
  const rows = db.prepare(
    `
      SELECT
        a.*,
        w.slug as workspace_slug
      FROM attachments a
      JOIN blocks b ON a.block_id = b.id
      JOIN workspaces w ON b.workspace_id = w.id
      WHERE a.id IN (${placeholders}) AND b.workspace_id = ?
    `
  ).all(...attachmentIds, workspaceId) as unknown as Array<
    AttachmentRecord & { workspace_slug: string }
  >;

  return rows.map((row) => ({
    ...row,
    workspaceSlug: row.workspace_slug
  }));
}