fix-postgresql-migrations.js

const fs = require('fs');
const path = require('path');

// 读取所有迁移文件
const migrationsDir = path.join(__dirname, 'server', 'prisma', 'migrations');
const migrationFiles = fs.readdirSync(migrationsDir)
  .filter(file => file.endsWith('.sql'))
  .sort();

console.log('发现的迁移文件:', migrationFiles);

// 1. 重命名迁移文件，移除 _init 后缀
migrationFiles.forEach(file => {
  if (file.includes('_init')) {
    const newFile = file.replace('_init', '');
    const oldPath = path.join(migrationsDir, file);
    const newPath = path.join(migrationsDir, newFile);
    
    console.log(`重命名: ${file} -> ${newFile}`);
    fs.renameSync(oldPath, newPath);
  }
});

// 2. 修复 20230921191814.sql 中的外键约束问题
const mainMigrationFile = path.join(migrationsDir, '20230921191814.sql');
if (fs.existsSync(mainMigrationFile)) {
  let content = fs.readFileSync(mainMigrationFile, 'utf8');
  
  // 重新排序表创建，确保被引用的表先创建
  const tables = [
    {
      name: 'users',
      sql: `CREATE TABLE "users" (
    "id" SERIAL PRIMARY KEY,
    "username" TEXT,
    "password" TEXT NOT NULL,
    "role" TEXT NOT NULL DEFAULT 'default',
    "suspended" INTEGER NOT NULL DEFAULT 0,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);`
    },
    {
      name: 'workspaces',
      sql: `CREATE TABLE "workspaces" (
    "id" SERIAL PRIMARY KEY,
    "name" TEXT NOT NULL,
    "slug" TEXT NOT NULL,
    "vectorTag" TEXT,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "openAiTemp" REAL,
    "openAiHistory" INTEGER NOT NULL DEFAULT 20,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "openAiPrompt" TEXT
);`
    },
    {
      name: 'api_keys',
      sql: `CREATE TABLE "api_keys" (
    "id" SERIAL PRIMARY KEY,
    "secret" TEXT,
    "createdBy" INTEGER,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);`
    },
    {
      name: 'system_settings',
      sql: `CREATE TABLE "system_settings" (
    "id" SERIAL PRIMARY KEY,
    "label" TEXT NOT NULL,
    "value" TEXT,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);`
    },
    {
      name: 'document_vectors',
      sql: `CREATE TABLE "document_vectors" (
    "id" SERIAL PRIMARY KEY,
    "docId" TEXT NOT NULL,
    "vectorId" TEXT NOT NULL,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);`
    },
    {
      name: 'welcome_messages',
      sql: `CREATE TABLE "welcome_messages" (
    "id" SERIAL PRIMARY KEY,
    "user" TEXT NOT NULL,
    "response" TEXT NOT NULL,
    "orderIndex" INTEGER,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);`
    },
    {
      name: 'invites',
      sql: `CREATE TABLE "invites" (
    "id" SERIAL PRIMARY KEY,
    "code" TEXT NOT NULL,
    "status" TEXT NOT NULL DEFAULT 'pending',
    "claimedBy" INTEGER,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "createdBy" INTEGER NOT NULL,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);`
    },
    {
      name: 'workspace_documents',
      sql: `CREATE TABLE "workspace_documents" (
    "id" SERIAL PRIMARY KEY,
    "docId" TEXT NOT NULL,
    "filename" TEXT NOT NULL,
    "docpath" TEXT NOT NULL,
    "workspaceId" INTEGER NOT NULL,
    "metadata" TEXT,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);`
    },
    {
      name: 'workspace_chats',
      sql: `CREATE TABLE "workspace_chats" (
    "id" SERIAL PRIMARY KEY,
    "workspaceId" INTEGER NOT NULL,
    "prompt" TEXT NOT NULL,
    "response" TEXT NOT NULL,
    "include" BOOLEAN NOT NULL DEFAULT true,
    "user_id" INTEGER,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);`
    },
    {
      name: 'workspace_users',
      sql: `CREATE TABLE "workspace_users" (
    "id" SERIAL PRIMARY KEY,
    "user_id" INTEGER NOT NULL,
    "workspace_id" INTEGER NOT NULL,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);`
    }
  ];
  
  // 提取索引创建语句
  const indexRegex = /-- CreateIndex\s+CREATE UNIQUE INDEX.*?;\n/g;
  const indexes = content.match(indexRegex) || [];
  
  // 构建新的迁移文件内容
  let newContent = '-- CreateTable\n\n';
  
  // 添加所有表创建语句
  tables.forEach(table => {
    newContent += table.sql + '\n\n';
  });
  
  // 添加所有外键约束（在所有表创建之后）
  newContent += '-- Add foreign key constraints\n';
  newContent += 'ALTER TABLE "workspace_documents" ADD CONSTRAINT "workspace_documents_workspaceId_fkey" FOREIGN KEY ("workspaceId") REFERENCES "workspaces" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;\n';
  newContent += 'ALTER TABLE "workspace_chats" ADD CONSTRAINT "workspace_chats_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE;\n';
  newContent += 'ALTER TABLE "workspace_users" ADD CONSTRAINT "workspace_users_workspace_id_fkey" FOREIGN KEY ("workspace_id") REFERENCES "workspaces" ("id") ON DELETE CASCADE ON UPDATE CASCADE;\n';
  newContent += 'ALTER TABLE "workspace_users" ADD CONSTRAINT "workspace_users_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE;\n';
  newContent += '\n';
  
  // 添加索引
  newContent += '-- CreateIndex\n\n';
  indexes.forEach(index => newContent += index + '\n');
  
  // 写入修复后的文件
  fs.writeFileSync(mainMigrationFile, newContent);
  console.log('已修复主迁移文件的外键约束问题');
}

console.log('迁移文件修复完成！');
console.log('\n请执行以下命令来完成迁移：');
console.log('1. cd server');
console.log('2. npx prisma migrate reset');
console.log('3. npx prisma generate');