import crypto from 'node:crypto';
import fs from 'node:fs/promises';
import path from 'node:path';
import { DEFAULT_MAX_FILE_BYTES } from './mindspace.mjs';
import { runBasicFileScan } from './mindspace-scan.mjs';
import { extractAttachmentText } from './mindspace-attachment-text.mjs';
import {
  assetThumbnailKey,
  ensureHtmlThumbnail,
  scheduleHtmlThumbnail,
} from './mindspace-thumbnails.mjs';
import { removeZoneMirror, resolveUserWorkspaceRoot } from './user-space.mjs';
import { PUBLIC_ZONE_DIR, resolvePublicBaseUrl } from './user-publish.mjs';
import { createMindSpacePublicUrl } from './mindspace-canonical-url.mjs';
import {
  buildPublicImagePaths,
  buildUserImagePublicUrl,
  isPublicImageStorageKey,
} from './user-image-url.mjs';
import { canPreviewAsset, renderAssetPreviewHtml } from './mindspace-asset-preview.mjs';
import { createWorkspaceAssetSync } from './mindspace-workspace-sync.mjs';
import { resolveWorkspaceStoragePath, isWorkspaceStorageKey } from './workspace-storage.mjs';
import {
  DEFAULT_IMAGE_INPUT_MAX_PIXELS,
  DEFAULT_IMAGE_UPLOAD_MAX_BYTES,
  normalizeImageForStorage,
} from './user-image-normalize.mjs';
import {
  normalizeWorkspaceRelativePath,
  resolveAssetWorkspaceRelativePath,
} from './mindspace-workspace-path.mjs';

const ALLOWED_EXTENSIONS = new Map([
  ['.txt', 'text/plain'],
  ['.md', 'text/markdown'],
  ['.csv', 'text/csv'],
  ['.pdf', 'application/pdf'],
  ['.png', 'image/png'],
  ['.jpg', 'image/jpeg'],
  ['.jpeg', 'image/jpeg'],
  ['.webp', 'image/webp'],
  ['.doc', 'application/msword'],
  ['.docx', 'application/vnd.openxmlformats-officedocument.wordprocessingml.document'],
  ['.xls', 'application/vnd.ms-excel'],
  ['.xlsx', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'],
  ['.ppt', 'application/vnd.ms-powerpoint'],
  ['.pptx', 'application/vnd.openxmlformats-officedocument.presentationml.presentation'],
  ['.html', 'text/html'],
  ['.htm', 'text/html'],
]);
const MAX_IMAGE_UPLOAD_BYTES = DEFAULT_IMAGE_UPLOAD_MAX_BYTES;
const PUBLIC_TEMP_IMAGE_DIR = '.tmp-images';

const PUBLIC_IMAGE_EXTENSIONS = new Map([
  ['image/png', '.png'],
  ['image/jpeg', '.jpg'],
  ['image/webp', '.webp'],
]);

function asNumber(value) {
  return Number(value ?? 0);
}

function normalizeFilename(filename) {
  const normalized = String(filename ?? '').normalize('NFKC').trim();
  if (
    !normalized ||
    normalized === '.' ||
    normalized === '..' ||
    normalized.includes('/') ||
    normalized.includes('\\') ||
    normalized.includes('\0') ||
    /[\u0000-\u001f\u007f]/.test(normalized)
  ) {
    throw Object.assign(new Error('文件名无效'), { code: 'invalid_filename' });
  }
  return normalized.slice(0, 255);
}

function expectedMimeType(filename) {
  return ALLOWED_EXTENSIONS.get(path.extname(filename).toLowerCase()) ?? null;
}

function extractImageDimensions(buffer, mimeType) {
  if (mimeType === 'image/png' && buffer.length >= 24) {
    const width = buffer.readUInt32BE(16);
    const height = buffer.readUInt32BE(20);
    return { width, height };
  }
  if (mimeType === 'image/jpeg' && buffer.length >= 2) {
    let offset = 2;
    while (offset < buffer.length - 9) {
      if (buffer[offset] !== 0xff) break;
      const marker = buffer[offset + 1];
      if (marker === 0xd9) break;
      if (marker >= 0xd0 && marker <= 0xd8) {
        offset += 2;
        continue;
      }
      const segmentLength = buffer.readUInt16BE(offset + 2);
      if (marker === 0xc0 || marker === 0xc1 || marker === 0xc2) {
        const height = buffer.readUInt16BE(offset + 5);
        const width = buffer.readUInt16BE(offset + 7);
        return { width, height };
      }
      offset += segmentLength + 2;
    }
  }
  if (mimeType === 'image/webp' && buffer.length >= 30) {
    const width = buffer.readUInt32LE(24) + 1;
    const height = buffer.readUInt32LE(28) + 1;
    return { width, height };
  }
  return null;
}

function validateImagePixels(buffer, mimeType) {
  if (!mimeType?.startsWith('image/')) return null;
  const dims = extractImageDimensions(buffer, mimeType);
  if (!dims) return { ok: true };
  const { width, height } = dims;
  const megapixels = (width * height) / 1_000_000;
  const maxMegapixels = DEFAULT_IMAGE_INPUT_MAX_PIXELS / 1_000_000;
  if (megapixels > maxMegapixels) {
    throw Object.assign(
      new Error(`图片像素超过限制（${megapixels.toFixed(1)}MP > ${maxMegapixels.toFixed(0)}MP）`),
      { code: 'image_pixel_exceeded' }
    );
  }
  return { ok: true, width, height };
}

function detectMimeType(buffer, filename) {
  const head = buffer.subarray(0, 256).toString('utf8').trimStart().toLowerCase();
  if (head.startsWith('<!doctype html') || head.startsWith('<html')) return 'text/html';
  if (buffer.subarray(0, 5).toString('ascii') === '%PDF-') return 'application/pdf';
  if (buffer.subarray(0, 8).equals(Buffer.from([137, 80, 78, 71, 13, 10, 26, 10]))) {
    return 'image/png';
  }
  if (buffer[0] === 0xff && buffer[1] === 0xd8 && buffer[2] === 0xff) return 'image/jpeg';
  if (
    buffer.subarray(0, 4).toString('ascii') === 'RIFF' &&
    buffer.subarray(8, 12).toString('ascii') === 'WEBP'
  ) {
    return 'image/webp';
  }
  const extensionMime = expectedMimeType(filename);
  if (buffer.subarray(0, 2).toString('ascii') === 'PK') return extensionMime;
  if (extensionMime?.startsWith('text/')) return extensionMime;
  if (['.doc', '.xls', '.ppt'].includes(path.extname(filename).toLowerCase())) {
    return extensionMime;
  }
  return null;
}

function assetTypeForMime(mimeType) {
  if (mimeType.startsWith('image/')) return 'image';
  if (mimeType === 'text/html') return 'html';
  if (mimeType === 'application/pdf') return 'pdf';
  if (mimeType.includes('spreadsheet') || mimeType.includes('excel')) return 'excel';
  if (mimeType.includes('word')) return 'word';
  if (mimeType.includes('presentation') || mimeType.includes('powerpoint')) return 'ppt';
  if (mimeType === 'text/markdown') return 'markdown';
  return 'file';
}

function visibilityForCategory(categoryCode) {
  return categoryCode === 'public' ? 'public_candidate' : 'private';
}

const UPLOADABLE_CATEGORY_CODES = ['oa', 'public'];

function resolvePublicImagePaths(userId, assetId, mimeType, filename, uniqueSuffix = null) {
  const suffix = uniqueSuffix ?? String(assetId ?? '').replace(/-/g, '').slice(0, 12);
  return buildPublicImagePaths({
    userId,
    assetId,
    mimeType,
    originalFilename: filename,
    uniqueSuffix: suffix,
  });
}

function isPublicImageAsset(row) {
  const mime = String(row?.mime_type ?? '');
  if (!mime.startsWith('image/')) return false;
  if (isPublicImageStorageKey(row?.storage_key)) return true;
  return row?.category_code === 'public';
}

function isCanonicalPublicImageStorageKey(storageKey) {
  const normalized = String(storageKey ?? '').replace(/\\/g, '/');
  if (isWorkspaceStorageKey(normalized)) return false;
  return isPublicImageStorageKey(normalized);
}

function publicUrlForAsset(row) {
  if (!isPublicImageAsset(row)) return null;
  if (row?.storage_key && isCanonicalPublicImageStorageKey(row.storage_key)) {
    return buildUserImagePublicUrl({
      userId: row.user_id,
      storageKey: row.storage_key,
      mimeType: row.mime_type,
      originalFilename: row.original_filename,
    });
  }
  const workspacePath = normalizeWorkspaceRelativePath(row?.workspace_relative_path);
  if (workspacePath && isWorkspaceStorageKey(row?.storage_key)) {
    return createMindSpacePublicUrl({
      publicBaseUrl: resolvePublicBaseUrl(),
      ownerKey: row.user_id,
      relativePath: workspacePath,
    });
  }
  if (row?.category_code === 'public' && row?.id) {
    return publicTempImageUrl(row.user_id, row.id, row.mime_type, row.original_filename);
  }
  return null;
}

function publicTempImageFilename(assetId, mimeType, fallbackFilename = '') {
  const fallbackExtension = path.extname(String(fallbackFilename)).toLowerCase();
  const extension = PUBLIC_IMAGE_EXTENSIONS.get(mimeType) || fallbackExtension || '.img';
  return `${assetId}${extension}`;
}

function publicTempImageStorageKey(userId, assetId, mimeType, fallbackFilename = '') {
  return path.posix.join(
    'users',
    userId,
    PUBLIC_ZONE_DIR,
    PUBLIC_TEMP_IMAGE_DIR,
    publicTempImageFilename(assetId, mimeType, fallbackFilename),
  );
}

function publicTempImageUrl(userId, assetId, mimeType, fallbackFilename = '') {
  const filename = publicTempImageFilename(assetId, mimeType, fallbackFilename);
  return createMindSpacePublicUrl({
    publicBaseUrl: resolvePublicBaseUrl(),
    ownerKey: userId,
    relativePath: `${PUBLIC_ZONE_DIR}/${PUBLIC_TEMP_IMAGE_DIR}/${filename}`,
  });
}

function assetResponse(row) {
  return {
    id: row.id,
    categoryId: row.category_id,
    categoryCode: row.category_code,
    assetType: row.asset_type,
    mimeType: row.mime_type,
    filename: row.original_filename,
    displayName: row.display_name,
    sizeBytes: asNumber(row.size_bytes),
    checksum: row.checksum,
    riskLevel: row.risk_level,
    visibility: row.visibility,
    status: row.status,
    scanStatus: row.scan_status ?? 'passed',
    sourceType: row.source_type,
    hasThumbnail: Boolean(row.has_thumbnail ?? row.mime_type === 'text/html'),
    publicUrl: publicUrlForAsset(row),
    sourcePageId: row.source_page_id ?? null,
    createdAt: asNumber(row.created_at),
    updatedAt: asNumber(row.updated_at),
  };
}

function assertAssetDownloadable(asset) {
  if (asset.status === 'quarantined') {
    throw Object.assign(new Error('文件尚未通过安全检查'), { code: 'security_scan_required' });
  }
  if (asset.scan_status === 'blocked') {
    throw Object.assign(new Error('文件存在安全风险，暂不可下载'), { code: 'security_risk_blocked' });
  }
  if (asset.scan_status === 'pending') {
    throw Object.assign(new Error('文件尚未完成安全检查'), { code: 'security_scan_required' });
  }
}

export function validateUploadRequest({ filename, sizeBytes, maxFileBytes = DEFAULT_MAX_FILE_BYTES }) {
  const normalizedFilename = normalizeFilename(filename);
  const normalizedSize = Number(sizeBytes);
  if (!Number.isSafeInteger(normalizedSize) || normalizedSize <= 0) {
    throw Object.assign(new Error('文件不能为空'), { code: 'invalid_file_size' });
  }
  const mimeType = expectedMimeType(normalizedFilename);
  if (!mimeType) {
    throw Object.assign(new Error('暂不支持该文件类型'), { code: 'unsupported_file_type' });
  }
  const effectiveMaxBytes = mimeType.startsWith('image/')
    ? MAX_IMAGE_UPLOAD_BYTES
    : maxFileBytes;
  if (normalizedSize > effectiveMaxBytes) {
    throw Object.assign(
      new Error(mimeType.startsWith('image/') ? '图片文件超过单文件大小限制' : '文件超过单文件大小限制'),
      { code: 'file_too_large' },
    );
  }
  if (mimeType.startsWith('image/') && normalizedSize > MAX_IMAGE_UPLOAD_BYTES) {
    throw Object.assign(new Error('图片文件超过单文件大小限制'), { code: 'file_too_large' });
  }
  return { filename: normalizedFilename, sizeBytes: normalizedSize, expectedMimeType: mimeType };
}

export function createAssetService(pool, options = {}) {
  const storageRoot = path.resolve(options.storageRoot ?? path.join(process.cwd(), 'data', 'mindspace'));
  const h5Root = options.h5Root ? path.resolve(options.h5Root) : null;
  const maxFileBytes = Number(options.maxFileBytes ?? DEFAULT_MAX_FILE_BYTES);
  const uploadTtlMs = Number(options.uploadTtlMs ?? 30 * 60 * 1000);
  const idFactory = options.idFactory ?? (() => crypto.randomUUID());
  const normalizeStoredImage = options.normalizeStoredImage ?? normalizeImageForStorage;
  const conversationPackageRegistry = options.conversationPackageRegistry ?? null;
  const workspaceSync = createWorkspaceAssetSync({
    pool,
    storageRoot,
    h5Root,
    maxFileBytes,
    idFactory,
    conversationPackageRegistry,
  });

  const mirrorToUserWorkspace = async (userId, { categoryCode, filename, sourcePath }) => {
    if (!h5Root) return null;
    return mirrorAssetToZone({
      workspaceRoot: resolveUserWorkspaceRoot(h5Root, { id: userId }),
      categoryCode,
      filename,
      sourcePath,
    });
  };

  const writePublicImageMirror = async (userId, workspaceRelativePath, sourcePath) => {
    if (!h5Root) return null;
    const workspaceRoot = resolveUserWorkspaceRoot(h5Root, { id: userId });
    const target = path.join(workspaceRoot, workspaceRelativePath);
    await fs.mkdir(path.dirname(target), { recursive: true });
    await fs.copyFile(sourcePath, target);
    return target;
  };

  const removePublicImageMirror = async (userId, workspaceRelativePath) => {
    if (!h5Root || !workspaceRelativePath) return;
    const workspaceRoot = resolveUserWorkspaceRoot(h5Root, { id: userId });
    const target = path.join(workspaceRoot, workspaceRelativePath);
    await fs.rm(target, { force: true });
  };

  const resolvePublicCategory = async (conn, userId) => {
    const [rows] = await conn.query(
      `SELECT id, space_id, category_code
       FROM h5_space_categories
       WHERE user_id = ? AND category_code = 'public'
       LIMIT 1`,
      [userId],
    );
    return rows[0] ?? null;
  };

  const absoluteStoragePath = (storageKey) => {
    const resolved = path.resolve(storageRoot, storageKey);
    if (resolved !== storageRoot && !resolved.startsWith(`${storageRoot}${path.sep}`)) {
      throw new Error('存储路径越界');
    }
    return resolved;
  };

  const resolveStoragePathCandidates = (storageKey) => {
    const normalized = String(storageKey ?? '').replace(/\\/g, '/');
    const withoutMd = normalized.endsWith('.md') ? normalized.slice(0, -3) : normalized;
    const match = withoutMd.match(/^users\/([^/]+)\/(assets|pages)\/([^/]+)\/(v\d+)$/);
    if (!match) return [normalized];
    const [, userId, scope, entityId, versionTag] = match;
    return [
      normalized,
      `users/${userId}/${scope}/${entityId}/versions/${versionTag}.md`,
      `users/${userId}/${scope}/${entityId}/versions/${versionTag}`,
    ].filter((candidate, index, list) => list.indexOf(candidate) === index);
  };

  const resolveReadableStoragePath = async (storageKey) => {
    if (h5Root && isWorkspaceStorageKey(storageKey)) {
      const workspacePath = resolveWorkspaceStoragePath(h5Root, storageKey);
      if (workspacePath) {
        try {
          await fs.stat(workspacePath);
          return workspacePath;
        } catch (error) {
          if (error?.code !== 'ENOENT') throw error;
        }
      }
    }
    let lastError = null;
    for (const candidate of resolveStoragePathCandidates(storageKey)) {
      const absolutePath = absoluteStoragePath(candidate);
      try {
        await fs.stat(absolutePath);
        return absolutePath;
      } catch (error) {
        if (error?.code === 'ENOENT') {
          lastError = error;
          continue;
        }
        throw error;
      }
    }
    throw lastError ?? Object.assign(new Error('存储文件不存在'), { code: 'storage_not_found' });
  };

  const normalizeStoredAssetImage = async ({ buffer, mimeType, filename }) => {
    if (!mimeType?.startsWith('image/')) {
      return { buffer, mimeType, filename };
    }
    return normalizeStoredImage({
      buffer,
      mimeType,
      filename,
      maxUploadBytes: MAX_IMAGE_UPLOAD_BYTES,
      maxPixels: DEFAULT_IMAGE_INPUT_MAX_PIXELS,
    });
  };

  const createUpload = async (userId, input) => {
    const validated = validateUploadRequest({ ...input, maxFileBytes });
    const conn = await pool.getConnection();
    try {
      await conn.beginTransaction();
      const [categories] = await conn.query(
        `SELECT c.id, c.space_id, c.category_code
         FROM h5_space_categories c
         WHERE c.id = ? AND c.user_id = ?
         LIMIT 1
         FOR UPDATE`,
        [input.categoryId, userId],
      );
      const category = categories[0];
      if (!category) {
        throw Object.assign(new Error('分类不存在'), { code: 'category_not_found' });
      }
      if (!UPLOADABLE_CATEGORY_CODES.includes(category.category_code)) {
        throw Object.assign(new Error('该分类不允许直接上传'), {
          code: 'category_not_uploadable',
        });
      }

      let targetCategory = category;
      if (validated.expectedMimeType.startsWith('image/')) {
        const publicCategory = await resolvePublicCategory(conn, userId);
        if (publicCategory) targetCategory = publicCategory;
      }

      const [spaces] = await conn.query(
        `SELECT id, quota_bytes, used_bytes, reserved_bytes, status
         FROM h5_user_spaces
         WHERE id = ? AND user_id = ?
         LIMIT 1
         FOR UPDATE`,
        [category.space_id, userId],
      );
      const space = spaces[0];
      if (!space || space.status !== 'active') {
        throw Object.assign(new Error('用户空间不可用'), { code: 'space_unavailable' });
      }
      const available =
        asNumber(space.quota_bytes) - asNumber(space.used_bytes) - asNumber(space.reserved_bytes);
      if (available < validated.sizeBytes) {
        throw Object.assign(new Error('剩余空间不足'), {
          code: 'quota_exceeded',
          details: { requiredBytes: validated.sizeBytes, availableBytes: Math.max(0, available) },
        });
      }

      const uploadId = idFactory();
      const now = Date.now();
      const temporaryStorageKey = path.posix.join('users', userId, 'temp', `${uploadId}.upload`);
      await conn.query(
        `UPDATE h5_user_spaces
         SET reserved_bytes = reserved_bytes + ?, updated_at = ?
         WHERE id = ? AND user_id = ?`,
        [validated.sizeBytes, now, space.id, userId],
      );
      await conn.query(
        `INSERT INTO h5_upload_sessions
         (id, user_id, space_id, category_id, filename, expected_size, declared_mime_type,
          reserved_bytes, temporary_storage_key, status, expires_at, created_at,
          source_session_id, source_message_id)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'reserved', ?, ?, ?, ?)`,
        [
          uploadId,
          userId,
          targetCategory.space_id,
          targetCategory.id,
          validated.filename,
          validated.sizeBytes,
          input.declaredMimeType || null,
          validated.sizeBytes,
          temporaryStorageKey,
          now + uploadTtlMs,
          now,
          input.sourceSessionId ?? input.source_session_id ?? null,
          input.sourceMessageId ?? input.source_message_id ?? null,
        ],
      );
      await conn.commit();
      return {
        id: uploadId,
        filename: validated.filename,
        expectedSize: validated.sizeBytes,
        uploadUrl: `/api/mindspace/v1/uploads/${uploadId}/content`,
        expiresAt: now + uploadTtlMs,
        reservedBytes: validated.sizeBytes,
      };
    } catch (error) {
      await conn.rollback();
      throw error;
    } finally {
      conn.release();
    }
  };

  const writeUploadContent = async (userId, uploadId, buffer) => {
    if (!Buffer.isBuffer(buffer) || buffer.length === 0) {
      throw Object.assign(new Error('上传内容为空'), { code: 'invalid_file_size' });
    }
    const [rows] = await pool.query(
      `SELECT id, filename, expected_size, temporary_storage_key, status, expires_at
       FROM h5_upload_sessions
       WHERE id = ? AND user_id = ?
       LIMIT 1`,
      [uploadId, userId],
    );
    const upload = rows[0];
    if (!upload) throw Object.assign(new Error('上传会话不存在'), { code: 'upload_not_found' });
    if (upload.status !== 'reserved') {
      throw Object.assign(new Error('上传会话状态无效'), { code: 'invalid_upload_state' });
    }
    if (asNumber(upload.expires_at) <= Date.now()) {
      throw Object.assign(new Error('上传会话已过期'), { code: 'upload_expired' });
    }
    if (buffer.length !== asNumber(upload.expected_size)) {
      throw Object.assign(new Error('上传内容大小与预期不一致'), {
        code: 'file_size_mismatch',
      });
    }
    const detectedMimeType = detectMimeType(buffer, upload.filename);
    if (!detectedMimeType) {
      throw Object.assign(new Error('无法确认文件类型'), { code: 'unsupported_file_type' });
    }
    const effectiveMaxBytes = detectedMimeType.startsWith('image/')
      ? MAX_IMAGE_UPLOAD_BYTES
      : maxFileBytes;
    if (buffer.length > effectiveMaxBytes) {
      throw Object.assign(
        new Error(detectedMimeType.startsWith('image/') ? '图片文件超过单文件大小限制' : '文件超过单文件大小限制'),
        { code: 'file_too_large' },
      );
    }
    if (detectedMimeType.startsWith('image/') && buffer.length > MAX_IMAGE_UPLOAD_BYTES) {
      throw Object.assign(new Error('图片文件超过单文件大小限制'), { code: 'file_too_large' });
    }
    if (detectedMimeType.startsWith('image/')) {
      validateImagePixels(buffer, detectedMimeType);
    }

    const target = absoluteStoragePath(upload.temporary_storage_key);
    await fs.mkdir(path.dirname(target), { recursive: true });
    await fs.writeFile(target, buffer, { flag: 'wx' }).catch(async (error) => {
      if (error?.code !== 'EEXIST') throw error;
      await fs.writeFile(target, buffer);
    });
    const checksum = crypto.createHash('sha256').update(buffer).digest('hex');
    await pool.query(
      `UPDATE h5_upload_sessions
       SET actual_size = ?, detected_mime_type = ?, checksum = ?, status = 'uploaded'
       WHERE id = ? AND user_id = ? AND status = 'reserved'`,
      [buffer.length, detectedMimeType, checksum, uploadId, userId],
    );
    return { sizeBytes: buffer.length, mimeType: detectedMimeType, checksum };
  };

  const completeUpload = async (userId, uploadId) => {
  const conn = await pool.getConnection();
  let temporaryPath;
  let finalPath;
  let publicMirror = null;
    try {
      await conn.beginTransaction();
      const [rows] = await conn.query(
        `SELECT u.*, c.category_code
         FROM h5_upload_sessions u
         JOIN h5_space_categories c ON c.id = u.category_id AND c.user_id = u.user_id
         WHERE u.id = ? AND u.user_id = ?
         LIMIT 1
         FOR UPDATE`,
        [uploadId, userId],
      );
      const upload = rows[0];
      if (!upload) throw Object.assign(new Error('上传会话不存在'), { code: 'upload_not_found' });
      if (upload.status === 'completed' && upload.completed_asset_id) {
        const [existing] = await conn.query(
          `SELECT a.*, c.category_code
           FROM h5_assets a
           JOIN h5_space_categories c ON c.id = a.category_id
           WHERE a.id = ? AND a.user_id = ?`,
          [upload.completed_asset_id, userId],
        );
        await conn.commit();
        return existing[0] ? assetResponse(existing[0]) : null;
      }
      if (upload.status !== 'uploaded') {
        throw Object.assign(new Error('文件内容尚未上传'), { code: 'invalid_upload_state' });
      }
      if (
        asNumber(upload.actual_size) !== asNumber(upload.expected_size) ||
        !upload.detected_mime_type ||
        !upload.checksum
      ) {
        throw Object.assign(new Error('上传内容不完整'), { code: 'file_size_mismatch' });
      }

      const assetId = idFactory();
      const versionId = idFactory();
      temporaryPath = absoluteStoragePath(upload.temporary_storage_key);
      const sourceBuffer = await fs.readFile(temporaryPath);
      const normalizedImage = await normalizeStoredAssetImage({
        buffer: sourceBuffer,
        mimeType: upload.detected_mime_type,
        filename: upload.filename,
      });
      const storedBuffer = normalizedImage.buffer;
      const storedMimeType = normalizedImage.mimeType;
      const storedUploadFilename = normalizedImage.filename;
      const storedSizeBytes = storedBuffer.length;
      const storedChecksum = crypto.createHash('sha256').update(storedBuffer).digest('hex');
      const scan = runBasicFileScan(storedBuffer, {
        filename: storedUploadFilename,
        mimeType: storedMimeType,
      });
      const assetStatus = scan.scanStatus === 'passed' ? 'ready' : 'quarantined';
      const versionScanStatus = scan.scanStatus === 'passed' ? 'passed' : 'blocked';
      const isImage = storedMimeType.startsWith('image/');
      const shouldPublishImage = isImage && scan.scanStatus === 'passed';
      let finalStorageKey = upload.temporary_storage_key;
      let workspaceRelativePath = null;
      let storedFilename = storedUploadFilename;
      if (shouldPublishImage) {
        const imagePaths = resolvePublicImagePaths(
          userId,
          assetId,
          storedMimeType,
          storedUploadFilename,
        );
        finalStorageKey = imagePaths.storageKey;
        workspaceRelativePath = imagePaths.workspaceRelativePath;
        storedFilename = imagePaths.workspaceRelativePath;
      }
      finalPath = absoluteStoragePath(finalStorageKey);
      if (finalStorageKey !== upload.temporary_storage_key) {
        await fs.mkdir(path.dirname(finalPath), { recursive: true });
        await fs.writeFile(finalPath, storedBuffer);
      } else if (isImage) {
        await fs.writeFile(finalPath, storedBuffer);
      }
      if (shouldPublishImage && workspaceRelativePath) {
        publicMirror = await writePublicImageMirror(userId, workspaceRelativePath, finalPath);
      }

      const now = Date.now();
      const visibility = shouldPublishImage ? 'public_candidate' : visibilityForCategory(upload.category_code);
      const indexedWorkspacePath = resolveAssetWorkspaceRelativePath({
        categoryCode: upload.category_code,
        originalFilename: storedFilename,
        explicitPath: workspaceRelativePath,
      });
      await conn.query(
        `INSERT INTO h5_assets
         (id, user_id, space_id, category_id, asset_type, mime_type, original_filename,
          display_name, workspace_relative_path, current_version_id, size_bytes, checksum, risk_level, visibility,
          status, source_type, created_at, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'upload', ?, ?)`,
        [
          assetId,
          userId,
          upload.space_id,
          upload.category_id,
          assetTypeForMime(storedMimeType),
          storedMimeType,
          storedFilename,
          upload.filename,
          indexedWorkspacePath,
          versionId,
          storedSizeBytes,
          storedChecksum,
          scan.riskLevel,
          visibility,
          assetStatus,
          now,
          now,
        ],
      );
      await conn.query(
        `INSERT INTO h5_asset_versions
         (id, asset_id, version_no, storage_key, size_bytes, checksum, mime_type,
          created_by, change_note, scan_status, created_at)
         VALUES (?, ?, 1, ?, ?, ?, ?, ?, '初始上传', ?, ?)`,
        [
          versionId,
          assetId,
          finalStorageKey,
          storedSizeBytes,
          storedChecksum,
          storedMimeType,
          userId,
          versionScanStatus,
          now,
        ],
      );
      await conn.query(
        `UPDATE h5_user_spaces
         SET reserved_bytes = GREATEST(0, reserved_bytes - ?),
             used_bytes = used_bytes + ?,
             updated_at = ?
         WHERE id = ? AND user_id = ?`,
        [upload.reserved_bytes, storedSizeBytes, now, upload.space_id, userId],
      );
      await conn.query(
        `UPDATE h5_upload_sessions
         SET status = 'completed', completed_at = ?, completed_asset_id = ?
         WHERE id = ? AND user_id = ?`,
        [now, assetId, uploadId, userId],
      );
      await conn.commit();
      const result = {
        id: assetId,
        user_id: userId,
        categoryId: upload.category_id,
        categoryCode: upload.category_code,
        assetType: assetTypeForMime(storedMimeType),
        mimeType: storedMimeType,
        filename: upload.filename,
        displayName: upload.filename,
        sizeBytes: storedSizeBytes,
        checksum: storedChecksum,
        riskLevel: scan.riskLevel,
        visibility,
        status: assetStatus,
        scanStatus: versionScanStatus,
        sourceType: 'upload',
        createdAt: now,
        updatedAt: now,
        publicUrl: shouldPublishImage
          ? buildUserImagePublicUrl({
              userId,
              storageKey: finalStorageKey,
              mimeType: storedMimeType,
              originalFilename: storedUploadFilename,
            })
          : null,
      };
      if (finalStorageKey !== upload.temporary_storage_key) {
        await fs.rm(temporaryPath, { force: true }).catch(() => {});
      }
      await registerUploadArtifactForConversation({
        registry: conversationPackageRegistry,
        userId,
        upload,
        asset: result,
        assetId,
        storageKey: finalStorageKey,
        canonicalUrl: result.publicUrl,
        now,
      });
      return result;
    } catch (error) {
      await conn.rollback();
      if (finalPath && finalPath !== temporaryPath) await fs.rm(finalPath, { force: true }).catch(() => {});
      if (publicMirror) await fs.rm(publicMirror, { force: true }).catch(() => {});
      throw error;
    } finally {
      conn.release();
    }
  };

  const claimUploadArtifactsForConversation = async (userId, { sessionId, messageId } = {}) => {
    const normalizedSessionId = String(sessionId ?? '').trim();
    const normalizedMessageId = String(messageId ?? '').trim();
    if (!normalizedSessionId || !normalizedMessageId) return { claimedCount: 0 };

    const conn = await pool.getConnection();
    try {
      await conn.beginTransaction();
      const [rows] = await conn.query(
        `SELECT
           u.*,
           a.id AS asset_id,
           a.mime_type AS asset_mime_type,
           a.original_filename AS asset_original_filename,
           a.display_name AS asset_display_name,
           a.size_bytes AS asset_size_bytes,
           a.updated_at AS asset_updated_at,
           v.storage_key AS asset_storage_key,
           c.category_code
         FROM h5_upload_sessions u
         JOIN h5_assets a
           ON a.id = u.completed_asset_id AND a.user_id = u.user_id
         LEFT JOIN h5_asset_versions v
           ON v.id = a.current_version_id AND v.asset_id = a.id
         LEFT JOIN h5_space_categories c
           ON c.id = a.category_id
         WHERE u.user_id = ?
           AND u.source_message_id = ?
           AND u.status = 'completed'
           AND (u.source_session_id IS NULL OR u.source_session_id = '')
         LIMIT 20
         FOR UPDATE`,
        [userId, normalizedMessageId],
      );
      if (rows.length === 0) {
        await conn.commit();
        return { claimedCount: 0 };
      }
      await conn.query(
        `UPDATE h5_upload_sessions
         SET source_session_id = ?
         WHERE user_id = ?
           AND source_message_id = ?
           AND status = 'completed'
           AND (source_session_id IS NULL OR source_session_id = '')`,
        [normalizedSessionId, userId, normalizedMessageId],
      );
      await conn.commit();

      const now = Date.now();
      let claimedCount = 0;
      for (const row of rows) {
        const upload = {
          ...row,
          source_session_id: normalizedSessionId,
          source_message_id: normalizedMessageId,
          detected_mime_type: row.asset_mime_type,
          actual_size: row.asset_size_bytes,
        };
        const asset = {
          id: row.asset_id,
          user_id: userId,
          categoryCode: row.category_code,
          mimeType: row.asset_mime_type,
          filename: row.asset_original_filename,
          displayName: row.asset_display_name,
          sizeBytes: asNumber(row.asset_size_bytes),
          updatedAt: asNumber(row.asset_updated_at),
        };
        const artifact = await registerUploadArtifactForConversation({
          registry: conversationPackageRegistry,
          userId,
          upload,
          asset,
          assetId: row.asset_id,
          storageKey: row.asset_storage_key,
          canonicalUrl: publicUrlForAsset({
            id: row.asset_id,
            user_id: userId,
            category_code: row.category_code,
            mime_type: row.asset_mime_type,
            storage_key: row.asset_storage_key,
            original_filename: row.asset_original_filename,
          }),
          now,
        });
        if (artifact) claimedCount += 1;
      }
      return { claimedCount };
    } catch (error) {
      await conn.rollback();
      throw error;
    } finally {
      conn.release();
    }
  };

  const cancelUpload = async (userId, uploadId) => {
    const conn = await pool.getConnection();
    let storageKey;
    try {
      await conn.beginTransaction();
      const [rows] = await conn.query(
        `SELECT id, space_id, reserved_bytes, temporary_storage_key, status
         FROM h5_upload_sessions
         WHERE id = ? AND user_id = ?
         LIMIT 1
         FOR UPDATE`,
        [uploadId, userId],
      );
      const upload = rows[0];
      if (!upload) throw Object.assign(new Error('上传会话不存在'), { code: 'upload_not_found' });
      if (['completed', 'cancelled', 'expired'].includes(upload.status)) {
        await conn.commit();
        return { cancelled: upload.status !== 'completed' };
      }
      storageKey = upload.temporary_storage_key;
      const now = Date.now();
      await conn.query(
        `UPDATE h5_user_spaces
         SET reserved_bytes = GREATEST(0, reserved_bytes - ?), updated_at = ?
         WHERE id = ? AND user_id = ?`,
        [upload.reserved_bytes, now, upload.space_id, userId],
      );
      await conn.query(
        `UPDATE h5_upload_sessions SET status = 'cancelled' WHERE id = ? AND user_id = ?`,
        [uploadId, userId],
      );
      await conn.commit();
      if (storageKey) await fs.rm(absoluteStoragePath(storageKey), { force: true });
      return { cancelled: true };
    } catch (error) {
      await conn.rollback();
      throw error;
    } finally {
      conn.release();
    }
  };

  const listAssets = async (userId, { categoryId, categoryCode, syncWorkspace = true } = {}) => {
    if (syncWorkspace && categoryCode && UPLOADABLE_CATEGORY_CODES.includes(categoryCode)) {
      await workspaceSync.syncUserWorkspace(userId, { categoryCode }).catch((error) => {
        console.warn(
          `[MindSpace] workspace sync failed (${categoryCode}):`,
          error?.message ?? error,
        );
      });
    }
    const filters = [`a.user_id = ?`, `a.status <> 'deleted'`];
    const params = [userId];
    if (categoryId) {
      filters.push(`a.category_id = ?`);
      params.push(categoryId);
    }
    if (categoryCode) {
      filters.push(`c.category_code = ?`);
      params.push(categoryCode);
    }
    const [rows] = await pool.query(
      `SELECT a.*, c.category_code, v.storage_key,
              ANY_VALUE(p.id) AS source_page_id
       FROM h5_assets a
       JOIN h5_space_categories c ON c.id = a.category_id AND c.user_id = a.user_id
       JOIN h5_asset_versions v ON v.id = a.current_version_id
       LEFT JOIN h5_page_versions pv ON pv.bundle_asset_id = a.id
       LEFT JOIN h5_page_records p ON p.id = pv.page_id AND p.status <> 'deleted' AND p.user_id = a.user_id
       WHERE ${filters.join(' AND ')}
       GROUP BY a.id, v.storage_key
       ORDER BY a.updated_at DESC
       LIMIT 100`,
      params,
    );
    return rows.map(assetResponse);
  };

  const deleteAsset = async (userId, assetId) => {
    const conn = await pool.getConnection();
    let mirrorCleanup = null;
    let storageCleanup = null;
    try {
      await conn.beginTransaction();
      const [rows] = await conn.query(
        `SELECT a.id, a.space_id, a.size_bytes, a.status, a.original_filename, a.mime_type,
                c.category_code, v.storage_key
         FROM h5_assets a
         JOIN h5_space_categories c ON c.id = a.category_id AND c.user_id = a.user_id
         LEFT JOIN h5_asset_versions v ON v.id = a.current_version_id
         WHERE a.id = ? AND a.user_id = ?
         LIMIT 1
         FOR UPDATE`,
        [assetId, userId],
      );
      const asset = rows[0];
      if (!asset || asset.status === 'deleted') {
        throw Object.assign(new Error('资产不存在'), { code: 'asset_not_found' });
      }
      const [pageReferences] = await conn.query(
        `SELECT p.id, p.title, p.status,
                EXISTS(
                  SELECT 1 FROM h5_publish_records pr
                  WHERE pr.page_id = p.id AND pr.user_id = p.user_id AND pr.status = 'online'
                ) AS published_online
         FROM h5_page_versions pv
         JOIN h5_page_records p ON p.id = pv.page_id AND p.status <> 'deleted'
         WHERE pv.content_asset_id = ? OR pv.bundle_asset_id = ?
         GROUP BY p.id, p.title, p.status
         LIMIT 10`,
        [assetId, assetId],
      );
      if (pageReferences.length > 0) {
        throw Object.assign(new Error('资产正在被页面使用，不能删除'), {
          code: 'asset_in_use',
          details: {
            hint: '请打开关联页面并删除；删除页面会自动下线公开链接并删除页面内容资产，之后即可删除原始资料。',
            references: pageReferences.map((page) => ({
              type: 'page',
              id: page.id,
              title: page.title,
              status: page.status,
              publishedOnline: Boolean(page.published_online),
            })),
          },
        });
      }
      const now = Date.now();
      await conn.query(
        `UPDATE h5_assets SET status = 'deleted', deleted_at = ?, updated_at = ?
         WHERE id = ? AND user_id = ?`,
        [now, now, assetId, userId],
      );
      await conn.query(
        `UPDATE h5_user_spaces
         SET used_bytes = GREATEST(0, used_bytes - ?), updated_at = ?
         WHERE id = ? AND user_id = ?`,
        [asset.size_bytes, now, asset.space_id, userId],
      );
      await conn.commit();
      if (h5Root) {
        mirrorCleanup = {
          id: asset.id,
          categoryCode: asset.category_code,
          filename: asset.original_filename,
          mimeType: asset.mime_type,
        };
      }
      if (asset.storage_key) storageCleanup = asset.storage_key;
    } catch (error) {
      await conn.rollback();
      throw error;
    } finally {
      conn.release();
    }
    if (mirrorCleanup) {
      try {
        removeZoneMirror({
          workspaceRoot: resolveUserWorkspaceRoot(h5Root, { id: userId }),
          categoryCode: mirrorCleanup.categoryCode,
          filename: mirrorCleanup.filename,
        });
        if (/\.html$/i.test(mirrorCleanup.filename)) {
          const thumbName = mirrorCleanup.filename.replace(/\.html$/i, '.thumbnail.svg');
          removeZoneMirror({
            workspaceRoot: resolveUserWorkspaceRoot(h5Root, { id: userId }),
            categoryCode: mirrorCleanup.categoryCode,
            filename: thumbName,
          });
        }
        if (mirrorCleanup.mimeType?.startsWith?.('image/')) {
          await removePublicImageMirror(userId, mirrorCleanup.filename);
        }
      } catch {
        // Best-effort workspace mirror cleanup; DB delete already committed.
      }
    }
    if (storageCleanup) {
      try {
        await fs.rm(absoluteStoragePath(storageCleanup), { force: true });
      } catch {
        // Best-effort physical storage cleanup; DB delete already committed.
      }
    }
    return { deleted: true };
  };

  const readAsset = async (userId, assetId) => {
    const [rows] = await pool.query(
      `SELECT a.*, c.category_code, v.storage_key, v.scan_status
       FROM h5_assets a
       JOIN h5_space_categories c ON c.id = a.category_id AND c.user_id = a.user_id
       JOIN h5_asset_versions v ON v.id = a.current_version_id
       WHERE a.id = ? AND a.user_id = ? AND a.status <> 'deleted'
       LIMIT 1`,
      [assetId, userId],
    );
    const asset = rows[0];
    if (!asset) throw Object.assign(new Error('资产不存在'), { code: 'asset_not_found' });
    assertAssetDownloadable(asset);
    return {
      asset: assetResponse(asset),
      path: await resolveReadableStoragePath(asset.storage_key),
    };
  };

  const readPublicAsset = async (assetId) => {
    const [rows] = await pool.query(
      `SELECT a.*, c.category_code, v.storage_key, v.scan_status
       FROM h5_assets a
       JOIN h5_space_categories c ON c.id = a.category_id AND c.user_id = a.user_id
       JOIN h5_asset_versions v ON v.id = a.current_version_id
       WHERE a.id = ? AND a.status <> 'deleted'
       LIMIT 1`,
      [assetId],
    );
    const asset = rows[0];
    if (!asset) throw Object.assign(new Error('资产不存在'), { code: 'asset_not_found' });
    assertAssetDownloadable(asset);
    return {
      asset: assetResponse(asset),
      path: await resolveReadableStoragePath(asset.storage_key),
    };
  };

  const createChatAsset = async (
    userId,
    { categoryCode, buffer, filename, displayName, sourceType = 'chat' },
  ) => {
    if (!Buffer.isBuffer(buffer) || buffer.length === 0) {
      throw Object.assign(new Error('资产内容为空'), { code: 'invalid_file_size' });
    }
    const normalizedFilename = normalizeFilename(filename);
    const detectedMimeType = detectMimeType(buffer, normalizedFilename);
    if (!detectedMimeType) {
      throw Object.assign(new Error('无法确认文件类型'), { code: 'unsupported_file_type' });
    }
    const effectiveMaxBytes = detectedMimeType.startsWith('image/')
      ? MAX_IMAGE_UPLOAD_BYTES
      : maxFileBytes;
    if (buffer.length > effectiveMaxBytes) {
      throw Object.assign(
        new Error(detectedMimeType.startsWith('image/') ? '图片文件超过单文件大小限制' : '文件超过单文件大小限制'),
        { code: 'file_too_large' },
      );
    }
    if (detectedMimeType.startsWith('image/') && buffer.length > MAX_IMAGE_UPLOAD_BYTES) {
      throw Object.assign(new Error('图片文件超过单文件大小限制'), { code: 'file_too_large' });
    }
    const normalizedImage = await normalizeStoredAssetImage({
      buffer,
      mimeType: detectedMimeType,
      filename: normalizedFilename,
    });
    const storedBuffer = normalizedImage.buffer;
    const storedMimeType = normalizedImage.mimeType;
    const storedFilenameInput = normalizedImage.filename;
    const storedSizeBytes = storedBuffer.length;
    const storedChecksum = crypto.createHash('sha256').update(storedBuffer).digest('hex');
    const isImage = storedMimeType.startsWith('image/');
    const effectiveCategoryCode = isImage ? 'public' : categoryCode;
    const conn = await pool.getConnection();
    let finalPath;
    let publicMirror = null;
    try {
      await conn.beginTransaction();
      const [categories] = await conn.query(
        `SELECT c.id, c.space_id, c.category_code
         FROM h5_space_categories c
         WHERE c.user_id = ? AND c.category_code = ?
         LIMIT 1
         FOR UPDATE`,
        [userId, effectiveCategoryCode],
      );
      const category = categories[0];
      if (!category) {
        throw Object.assign(new Error('分类不存在'), { code: 'category_not_found' });
      }
      if (!UPLOADABLE_CATEGORY_CODES.includes(category.category_code) && category.category_code !== 'draft') {
        throw Object.assign(new Error('该分类不允许保存聊天资产'), {
          code: 'category_not_uploadable',
        });
      }
      const [spaces] = await conn.query(
        `SELECT id, quota_bytes, used_bytes, reserved_bytes, status
         FROM h5_user_spaces
         WHERE id = ? AND user_id = ?
         LIMIT 1
         FOR UPDATE`,
        [category.space_id, userId],
      );
      const space = spaces[0];
      if (!space || space.status !== 'active') {
        throw Object.assign(new Error('用户空间不可用'), { code: 'space_unavailable' });
      }
      const available =
        asNumber(space.quota_bytes) - asNumber(space.used_bytes) - asNumber(space.reserved_bytes);
      if (available < storedSizeBytes) {
        throw Object.assign(new Error('剩余空间不足'), {
          code: 'quota_exceeded',
          details: { requiredBytes: storedSizeBytes, availableBytes: Math.max(0, available) },
        });
      }

      const assetId = idFactory();
      const versionId = idFactory();
      const scan = runBasicFileScan(storedBuffer, {
        filename: storedFilenameInput,
        mimeType: storedMimeType,
      });
      const assetStatus = scan.scanStatus === 'passed' ? 'ready' : 'quarantined';
      const versionScanStatus = scan.scanStatus === 'passed' ? 'passed' : 'blocked';
      const shouldPublishImage = isImage && scan.scanStatus === 'passed';
      let finalStorageKey = path.posix.join('users', userId, 'assets', assetId, 'versions', versionId);
      let workspaceRelativePath = null;
      let storedFilename = storedFilenameInput;
      if (shouldPublishImage) {
        const imagePaths = resolvePublicImagePaths(
          userId,
          assetId,
          storedMimeType,
          storedFilenameInput,
        );
        finalStorageKey = imagePaths.storageKey;
        workspaceRelativePath = imagePaths.workspaceRelativePath;
        storedFilename = imagePaths.workspaceRelativePath;
      }
      finalPath = absoluteStoragePath(finalStorageKey);
      await fs.mkdir(path.dirname(finalPath), { recursive: true });
      await fs.writeFile(finalPath, storedBuffer, { flag: 'wx' });
      if (shouldPublishImage && workspaceRelativePath) {
        publicMirror = await writePublicImageMirror(userId, workspaceRelativePath, finalPath);
      }
      const now = Date.now();
      const visibility = shouldPublishImage ? 'public_candidate' : visibilityForCategory(category.category_code);
      const indexedWorkspacePath = resolveAssetWorkspaceRelativePath({
        categoryCode: category.category_code,
        originalFilename: storedFilename,
        explicitPath: workspaceRelativePath,
      });
      await conn.query(
        `INSERT INTO h5_assets
         (id, user_id, space_id, category_id, asset_type, mime_type, original_filename,
          display_name, workspace_relative_path, current_version_id, size_bytes, checksum, risk_level, visibility,
          status, source_type, created_at, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          assetId,
          userId,
          category.space_id,
          category.id,
          assetTypeForMime(storedMimeType),
          storedMimeType,
          storedFilename,
          displayName || path.basename(storedFilename),
          indexedWorkspacePath,
          versionId,
          storedSizeBytes,
          storedChecksum,
          scan.riskLevel,
          visibility,
          assetStatus,
          sourceType,
          now,
          now,
        ],
      );
      await conn.query(
        `INSERT INTO h5_asset_versions
         (id, asset_id, version_no, storage_key, size_bytes, checksum, mime_type,
          created_by, change_note, scan_status, created_at)
         VALUES (?, ?, 1, ?, ?, ?, ?, ?, '从聊天保存', ?, ?)`,
        [
          versionId,
          assetId,
          finalStorageKey,
          storedSizeBytes,
          storedChecksum,
          storedMimeType,
          userId,
          versionScanStatus,
          now,
        ],
      );
      await conn.query(
        `UPDATE h5_user_spaces SET used_bytes = used_bytes + ?, updated_at = ?
         WHERE id = ? AND user_id = ?`,
        [storedSizeBytes, now, category.space_id, userId],
      );
      await conn.commit();
      if (storedMimeType === 'text/html') {
        scheduleHtmlThumbnail(storageRoot, assetThumbnailKey(userId, assetId), storedBuffer.toString('utf8'), {
          title: displayName || normalizedFilename,
          subtitle: category.category_code.toUpperCase(),
          contentStorageKey: finalStorageKey,
        });
      }
      return assetResponse({
        id: assetId,
        user_id: userId,
        category_id: category.id,
        category_code: category.category_code,
        asset_type: assetTypeForMime(storedMimeType),
        mime_type: storedMimeType,
        original_filename: storedFilename,
        display_name: displayName || path.basename(storedFilename),
        storage_key: finalStorageKey,
        size_bytes: storedSizeBytes,
        checksum: storedChecksum,
        risk_level: scan.riskLevel,
        visibility,
        status: assetStatus,
        scan_status: versionScanStatus,
        source_type: sourceType,
        created_at: now,
        updated_at: now,
        has_thumbnail: storedMimeType === 'text/html',
      });
    } catch (error) {
      await conn.rollback();
      if (finalPath) await fs.rm(finalPath, { force: true }).catch(() => {});
      if (publicMirror) await fs.rm(publicMirror, { force: true }).catch(() => {});
      throw error;
    } finally {
      conn.release();
    }
  };

  const renderAssetThumbnail = async (userId, assetId) => {
    const { asset, path: assetPath } = await readAsset(userId, assetId);
    if (asset.mimeType !== 'text/html') {
      throw Object.assign(new Error('该资产不支持缩略图'), { code: 'thumbnail_not_supported' });
    }
    const [versions] = await pool.query(
      `SELECT storage_key FROM h5_asset_versions
       WHERE asset_id = ? AND version_no = 1
       LIMIT 1`,
      [assetId],
    );
    const html = await fs.readFile(assetPath, 'utf8');
    return ensureHtmlThumbnail(storageRoot, assetThumbnailKey(userId, assetId), html, {
      title: asset.displayName,
      subtitle: asset.categoryCode?.toUpperCase?.() ?? 'HTML',
      contentStorageKey: versions[0]?.storage_key,
    });
  };

  const renderAssetPreview = async (userId, assetId, { downloadPath = null } = {}) => {
    const { asset, path: assetPath } = await readAsset(userId, assetId);
    if (!canPreviewAsset(asset.mimeType)) {
      throw Object.assign(new Error('该资产不支持预览'), { code: 'preview_not_supported' });
    }
    const buffer = await fs.readFile(assetPath);
    const downloadUrl =
      downloadPath ??
      `/api/mindspace/v1/assets/${encodeURIComponent(assetId)}/download?inline=1`;
    return renderAssetPreviewHtml({ asset, buffer, downloadUrl });
  };

  const extractAssetText = async (userId, assetId) => {
    const { asset, path: assetPath } = await readAsset(userId, assetId);
    const buffer = await fs.readFile(assetPath);
    const extracted = extractAttachmentText(buffer, asset.mimeType, asset.filename);
    return {
      asset: assetResponse(asset),
      ...extracted,
      charCount: extracted.text.length,
    };
  };

  const expireStaleUploads = async (now = Date.now()) => {
    const [rows] = await pool.query(
      `SELECT id, user_id, space_id, reserved_bytes, temporary_storage_key, status
       FROM h5_upload_sessions
       WHERE status IN ('reserved', 'uploaded') AND expires_at <= ?
       LIMIT 200`,
      [now],
    );
    let expired = 0;
    for (const upload of rows) {
      const conn = await pool.getConnection();
      try {
        await conn.beginTransaction();
        const [locked] = await conn.query(
          `SELECT id, reserved_bytes, temporary_storage_key, status
           FROM h5_upload_sessions
           WHERE id = ? AND user_id = ? AND status IN ('reserved', 'uploaded')
           LIMIT 1
           FOR UPDATE`,
          [upload.id, upload.user_id],
        );
        const row = locked[0];
        if (!row) {
          await conn.commit();
          continue;
        }
        await conn.query(
          `UPDATE h5_user_spaces
           SET reserved_bytes = GREATEST(0, reserved_bytes - ?), updated_at = ?
           WHERE id = ? AND user_id = ?`,
          [row.reserved_bytes, now, upload.space_id, upload.user_id],
        );
        await conn.query(
          `UPDATE h5_upload_sessions SET status = 'expired' WHERE id = ? AND user_id = ?`,
          [upload.id, upload.user_id],
        );
        await conn.commit();
        if (row.temporary_storage_key) {
          await fs.rm(absoluteStoragePath(row.temporary_storage_key), { force: true });
        }
        expired += 1;
      } catch {
        await conn.rollback();
      } finally {
        conn.release();
      }
    }
    return expired;
  };

  const findAssetByWorkspaceRelativePath = async (userId, relativePath) => {
    const normalized = normalizeWorkspaceRelativePath(relativePath);
    if (!normalized) return null;
    const [rows] = await pool.query(
      `SELECT a.*, c.category_code, av.storage_key
       FROM h5_assets a
       JOIN h5_space_categories c ON c.id = a.category_id AND c.user_id = a.user_id
       JOIN h5_asset_versions av ON av.id = a.current_version_id
       WHERE a.user_id = ?
         AND a.status <> 'deleted'
         AND (
           a.workspace_relative_path = ?
           OR (
             (a.workspace_relative_path IS NULL OR a.workspace_relative_path = '')
             AND (
               a.original_filename = ?
               OR CONCAT(c.category_code, '/', a.original_filename) = ?
             )
           )
         )
       ORDER BY a.updated_at DESC, a.id DESC
       LIMIT 1`,
      [userId, normalized, normalized, normalized],
    );
    return rows[0] ? assetResponse(rows[0]) : null;
  };

  return {
    storageRoot,
    createUpload,
    writeUploadContent,
    completeUpload,
    claimUploadArtifactsForConversation,
    cancelUpload,
    createChatAsset,
    listAssets,
    findAssetByWorkspaceRelativePath,
    deleteAsset,
    readAsset,
    readPublicAsset,
    renderAssetPreview,
    extractAssetText,
    renderAssetThumbnail,
    syncWorkspaceAssets: workspaceSync.syncUserWorkspace,
    expireStaleUploads,
  };
}

async function registerUploadArtifactForConversation({
  registry,
  userId,
  upload,
  asset,
  assetId,
  storageKey,
  canonicalUrl,
  now,
}) {
  const sessionId = String(upload?.source_session_id ?? '').trim();
  if (!registry || !sessionId || !assetId) return null;
  try {
    const packageRecord = await registry.ensurePackage({
      userId,
      sessionId,
      now,
    });
    const isImage = String(asset?.mimeType ?? '').startsWith('image/');
    const artifact = await registry.recordArtifact({
      id: `ca_${assetId}`,
      packageId: packageRecord.id,
      artifactKind: isImage ? 'input_image' : 'input_file',
      role: 'user',
      assetId,
      messageId: upload.source_message_id ?? null,
      displayName: asset?.displayName ?? asset?.filename ?? upload.filename,
      mimeType: asset?.mimeType ?? upload.detected_mime_type ?? null,
      sizeBytes: asset?.sizeBytes ?? upload.actual_size ?? null,
      storageKey,
      canonicalUrl,
      sortOrder: Number.isFinite(Number(now)) ? Number(now) : 0,
      now,
    });
    await registry.writeManifestForSession({ userId, sessionId });
    return artifact;
  } catch (error) {
    console.warn(
      '[MindSpace] conversation package upload artifact registration failed:',
      error instanceof Error ? error.message : error,
    );
    return null;
  }
}

export const assetInternals = {
  detectMimeType,
  normalizeFilename,
  expectedMimeType,
  assetTypeForMime,
  publicUrlForAsset,
  registerUploadArtifactForConversation,
};