qiwe-common/scripts/init-db.ts

/**
 * 数据库初始化脚本 — 创建 Parse Schema（PostgreSQL 表结构）
 *
 * 用法:
 *   1. 复制 .env.example 为 .env，填入真实的数据库地址和 masterKey
 *   2. 运行: npm run init-db
 *
 * 创建的表（Parse Class）:
 *   GroupChat          — 群聊表（逻辑删除: status='dismissed'）
 *   GroupMember        — 群成员表（逻辑删除: status='left' + leftAt）
 *   Message            — 消息表
 *   MessageSyncCursor  — 消息同步游标
 *   Contact            — 联系人表（逻辑删除: status='inactive'）
 *   WebhookLog         — Webhook 系统事件日志（审计追溯）
 */

import 'dotenv/config';
import Parse from '../src/config/parse-client.js';

// ── 工具函数 ──

async function ensureSchema(
  className: string,
  fields: Array<{
    name: string;
    type: string;
    options?: Record<string, any>;
  }>,
): Promise<void> {
  const schema = new Parse.Schema(className);
  let isNew = false;

  try {
    await schema.get({ useMasterKey: true });
    console.log(`  [${className}] Schema 已存在，补充字段...`);
  } catch {
    console.log(`  [${className}] 创建新 Schema...`);
    isNew = true;
  }

  for (const field of fields) {
    try {
      if (field.options) {
        await schema.addField(field.name, field.type, field.options);
      } else {
        await schema.addField(field.name, field.type);
      }
      console.log(`    + ${field.name} (${field.type})`);
    } catch (err: any) {
      if (err.message?.includes('already') || err.code === 122) {
        // 字段已存在，跳过
      } else {
        console.warn(`    ⚠ ${field.name} 添加失败: ${err.message}`);
      }
    }
  }

  // 设置 CLP: 仅认证用户可读写
  try {
    await schema.setCLP({
      get: { 'requiresAuthentication': true },
      find: { 'requiresAuthentication': true },
      create: { 'requiresAuthentication': true },
      update: { 'requiresAuthentication': true },
      delete: { 'requiresAuthentication': true },
      addField: {},
    });
  } catch { /* 某些 Parse Server 版本可能不支持 */ }

  if (isNew) {
    await schema.save({ useMasterKey: true });
  } else {
    await schema.update({ useMasterKey: true });
  }
  console.log(`  [${className}] Schema 更新完成\n`);
}

// ══════════════════════════════════════════════
//  表 1: GroupChat — 群聊
// ══════════════════════════════════════════════

async function createGroupChat(): Promise<void> {
  await ensureSchema('GroupChat', [
    { name: 'roomId',    type: 'String' },
    { name: 'guid',      type: 'String' },
    { name: 'roomName',  type: 'String' },
    { name: 'ownerId',   type: 'String' },
    { name: 'ownerName', type: 'String' },
    { name: 'ownerPhone', type: 'String' },
    { name: 'status',    type: 'String' },   // 'active' | 'dismissed'
    { name: 'memberCount',      type: 'Number' },
    { name: 'messageCountTotal', type: 'Number' },
    { name: 'messageCountToday', type: 'Number' },
    { name: 'lastActivityAt',   type: 'Date' },
  ]);
}

// ══════════════════════════════════════════════
//  表 2: GroupMember — 群成员
// ══════════════════════════════════════════════

async function createGroupMember(): Promise<void> {
  await ensureSchema('GroupMember', [
    { name: 'roomId',    type: 'String' },
    { name: 'userId',    type: 'String' },
    { name: 'nickname',  type: 'String' },
    { name: 'guid',      type: 'String' },
    { name: 'role',      type: 'String' },   // 'owner' | 'member'
    { name: 'status',    type: 'String' },   // 'active' | 'left'
    { name: 'joinedAt',  type: 'Date' },
    { name: 'leftAt',    type: 'Date' },
  ]);
}

// ══════════════════════════════════════════════
//  表 3: Message — 消息
// ══════════════════════════════════════════════

async function createMessage(): Promise<void> {
  await ensureSchema('Message', [
    { name: 'msgUniqueIdentifier', type: 'String' },
    { name: 'roomId',       type: 'String' },
    { name: 'senderId',     type: 'String' },
    { name: 'senderName',   type: 'String' },
    { name: 'content',      type: 'String' },
    { name: 'atList',       type: 'Array' },
    { name: 'msgType',      type: 'Number' },
    { name: 'isGroupChat',  type: 'Number' },
    { name: 'timestamp',    type: 'Date' },
    { name: 'guid',         type: 'String' },
    { name: 'seq',          type: 'Number' },
  ]);
}

// ══════════════════════════════════════════════
//  表 4: MessageSyncCursor — 消息同步游标
// ══════════════════════════════════════════════

async function createMessageSyncCursor(): Promise<void> {
  await ensureSchema('MessageSyncCursor', [
    { name: 'guid',       type: 'String' },
    { name: 'lastSeq',    type: 'Number' },
    { name: 'lastSyncAt', type: 'Date' },
  ]);
}

// ══════════════════════════════════════════════
//  表 5: Contact — 联系人（逻辑删除）
// ══════════════════════════════════════════════

async function createContact(): Promise<void> {
  await ensureSchema('Contact', [
    { name: 'userId',       type: 'String' },
    { name: 'guid',         type: 'String' },
    { name: 'nickname',     type: 'String' },
    { name: 'remark',       type: 'String' },
    { name: 'avatar',       type: 'String' },
    { name: 'sex',          type: 'Number' },
    { name: 'phone',        type: 'String' },
    { name: 'corpName',     type: 'String' },
    { name: 'corpFullName', type: 'String' },
    { name: 'contactType',  type: 'Number' },   // 0=外部 1=内部
    { name: 'status',       type: 'String' },   // 'active' | 'inactive'
  ]);
}

// ══════════════════════════════════════════════
//  表 6: WebhookLog — Webhook 事件日志
// ══════════════════════════════════════════════

async function createWebhookLog(): Promise<void> {
  await ensureSchema('WebhookLog', [
    { name: 'guid',      type: 'String' },
    { name: 'cmd',       type: 'Number' },
    { name: 'msgType',   type: 'Number' },
    { name: 'msgData',   type: 'Object' },
    { name: 'timestamp', type: 'Date' },
  ]);
}

// ══════════════════════════════════════════════
//  PostgreSQL 索引创建 SQL
// ══════════════════════════════════════════════

function printIndexSQL(): void {
  console.log('========================================');
  console.log('  建议在 PostgreSQL 中手动执行以下索引');
  console.log('  （连接到 Parse Server 使用的数据库执行）');
  console.log('========================================\n');

  const sql = `
-- GroupChat: 快速按 roomId+guid 查找
CREATE INDEX IF NOT EXISTS idx_groupchat_roomid_guid
  ON "GroupChat" ("roomId", "guid");
-- GroupChat: 每日计数器重置查询
CREATE INDEX IF NOT EXISTS idx_groupchat_reset_today
  ON "GroupChat" ("guid", "roomId")
  WHERE "messageCountToday" > 0;

-- GroupMember: 快速按 roomId 或 userId 查找
CREATE INDEX IF NOT EXISTS idx_groupmember_roomid_guid
  ON "GroupMember" ("roomId", "guid");
CREATE INDEX IF NOT EXISTS idx_groupmember_userid_roomid
  ON "GroupMember" ("userId", "roomId");

-- Message: 去重查询（唯一索引）
CREATE UNIQUE INDEX IF NOT EXISTS idx_message_unique_id
  ON "Message" ("msgUniqueIdentifier");
-- Message: 按群和时间查询
CREATE INDEX IF NOT EXISTS idx_message_roomid_timestamp
  ON "Message" ("guid", "roomId", "timestamp");
-- Message: senderName 回填空值扫描（部分索引）
CREATE INDEX IF NOT EXISTS idx_message_sendername_backfill
  ON "Message" ("guid", "isGroupChat", "senderName")
  WHERE "senderName" = '' AND "roomId" IS NOT NULL;

-- MessageSyncCursor: 按 guid 查找（唯一索引）
CREATE UNIQUE INDEX IF NOT EXISTS idx_synccursor_guid
  ON "MessageSyncCursor" ("guid");

-- Contact: 按 userId+guid 去重
CREATE INDEX IF NOT EXISTS idx_contact_userid_guid
  ON "Contact" ("userId", "guid");

-- WebhookLog: 按时间倒序查询
CREATE INDEX IF NOT EXISTS idx_webhooklog_timestamp
  ON "WebhookLog" ("guid", "timestamp" DESC);
`;

  console.log(sql);
  console.log('========================================\n');
}

// ══════════════════════════════════════════════
//  主流程
// ══════════════════════════════════════════════

async function main(): Promise<void> {
  console.log('\n╔══════════════════════════════════════╗');
  console.log('║   Lami QiWe 数据库初始化            ║');
  console.log('╚══════════════════════════════════════╝\n');

  console.log(`Parse Server: ${Parse.serverURL}`);
  console.log(`App ID:       ${Parse.applicationId}\n`);

  // 验证连接
  try {
    const userCount = await new Parse.Query('_User').count({ useMasterKey: true });
    console.log(`✅ Parse 连接正常 (_User 表 ${userCount} 条记录)\n`);
  } catch (err: any) {
    console.error(`❌ Parse 连接失败: ${err.message}`);
    console.error('   请检查 .env 中的 PARSE_SERVER_URL 和 PARSE_MASTER_KEY');
    process.exit(1);
  }

  console.log('── 开始创建/更新 Schema ──\n');

  await createGroupChat();
  await createGroupMember();
  await createMessage();
  await createMessageSyncCursor();
  await createContact();
  await createWebhookLog();

  console.log('✅ 所有 Schema 初始化完成 (6 张表)\n');

  printIndexSQL();

  console.log('提示: 索引 SQL 可后续在数据库中手动执行以优化查询性能。\n');
}

main().catch((err) => {
  console.error('初始化失败:', err.message);
  console.error(err.stack);
  process.exit(1);
});