complete-migration-fix.mjs

import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';

// 获取当前文件的目录
const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

console.log('🔧 开始修复 PostgreSQL 迁移问题...\n');

// 1. 清理现有的迁移文件
const migrationsDir = path.join(__dirname, 'server', 'prisma', 'migrations');
const migrationFiles = fs.readdirSync(migrationsDir)
  .filter(file => file.endsWith('.sql'));

console.log('📂 发现的迁移文件:');
migrationFiles.forEach(file => console.log(`  - ${file}`));

// 2. 删除所有现有的迁移文件（除了 _prisma_migrations 文件夹）
console.log('\n🗑️  清理现有迁移文件...');
migrationFiles.forEach(file => {
  const filePath = path.join(migrationsDir, file);
  fs.unlinkSync(filePath);
  console.log(`  已删除: ${file}`);
});

// 3. 创建新的合并迁移文件
console.log('\n📝 创建新的合并迁移文件...');
const newMigrationFile = path.join(migrationsDir, '20230921191814_initial_migration.sql');

const migrationSQL = `-- Initial Migration for PostgreSQL
-- This migration combines all tables from the previous separate migrations

-- Create all tables in the correct order to satisfy foreign key constraints

-- Users table (referenced by many others)
CREATE TABLE "users" (
    "id" SERIAL PRIMARY KEY,
    "username" TEXT UNIQUE,
    "password" TEXT NOT NULL,
    "pfpFilename" TEXT,
    "role" TEXT NOT NULL DEFAULT 'default',
    "suspended" INTEGER NOT NULL DEFAULT 0,
    "seen_recovery_codes" BOOLEAN DEFAULT false,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "dailyMessageLimit" INTEGER,
    "bio" TEXT DEFAULT ''
);

-- Workspaces table (referenced by many others)
CREATE TABLE "workspaces" (
    "id" SERIAL PRIMARY KEY,
    "name" TEXT NOT NULL,
    "slug" TEXT NOT NULL UNIQUE,
    "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,
    "similarityThreshold" REAL DEFAULT 0.25,
    "chatProvider" TEXT,
    "chatModel" TEXT,
    "topN" INTEGER DEFAULT 4,
    "chatMode" TEXT DEFAULT 'chat',
    "pfpFilename" TEXT,
    "agentProvider" TEXT,
    "agentModel" TEXT,
    "queryRefusalResponse" TEXT,
    "vectorSearchMode" TEXT DEFAULT 'default'
);

-- System settings table
CREATE TABLE "system_settings" (
    "id" SERIAL PRIMARY KEY,
    "label" TEXT NOT NULL UNIQUE,
    "value" TEXT,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- API keys table
CREATE TABLE "api_keys" (
    "id" SERIAL PRIMARY KEY,
    "secret" TEXT UNIQUE,
    "createdBy" INTEGER REFERENCES "users"("id"),
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Invites table
CREATE TABLE "invites" (
    "id" SERIAL PRIMARY KEY,
    "code" TEXT NOT NULL UNIQUE,
    "status" TEXT NOT NULL DEFAULT 'pending',
    "claimedBy" INTEGER REFERENCES "users"("id"),
    "workspaceIds" TEXT,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "createdBy" INTEGER NOT NULL REFERENCES "users"("id"),
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Document vectors table
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
);

-- Welcome messages table
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
);

-- Workspace documents table
CREATE TABLE "workspace_documents" (
    "id" SERIAL PRIMARY KEY,
    "docId" TEXT NOT NULL UNIQUE,
    "filename" TEXT NOT NULL,
    "docpath" TEXT NOT NULL,
    "workspaceId" INTEGER NOT NULL REFERENCES "workspaces"("id") ON DELETE CASCADE,
    "metadata" TEXT,
    "pinned" BOOLEAN DEFAULT false,
    "watched" BOOLEAN DEFAULT false,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Workspace chats table
CREATE TABLE "workspace_chats" (
    "id" SERIAL PRIMARY KEY,
    "workspaceId" INTEGER NOT NULL REFERENCES "workspaces"("id") ON DELETE CASCADE,
    "prompt" TEXT NOT NULL,
    "response" TEXT NOT NULL,
    "include" BOOLEAN NOT NULL DEFAULT true,
    "user_id" INTEGER REFERENCES "users"("id") ON DELETE CASCADE,
    "thread_id" INTEGER,
    "api_session_id" TEXT,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "feedbackScore" BOOLEAN
);

-- Workspace users table (junction table)
CREATE TABLE "workspace_users" (
    "id" SERIAL PRIMARY KEY,
    "user_id" INTEGER NOT NULL REFERENCES "users"("id") ON DELETE CASCADE,
    "workspace_id" INTEGER NOT NULL REFERENCES "workspaces"("id") ON DELETE CASCADE,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Workspace suggested messages table
CREATE TABLE "workspace_suggested_messages" (
    "id" SERIAL PRIMARY KEY,
    "workspaceId" INTEGER NOT NULL REFERENCES "workspaces"("id") ON DELETE CASCADE,
    "heading" TEXT NOT NULL,
    "message" TEXT NOT NULL,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Event logs table
CREATE TABLE "event_logs" (
    "id" SERIAL PRIMARY KEY,
    "event" TEXT NOT NULL,
    "metadata" TEXT,
    "userId" INTEGER REFERENCES "users"("id"),
    "occurredAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Cache data table
CREATE TABLE "cache_data" (
    "id" SERIAL PRIMARY KEY,
    "name" TEXT NOT NULL,
    "data" TEXT NOT NULL,
    "belongsTo" TEXT,
    "ById" INTEGER,
    "expiresAt" TIMESTAMP,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Embed configs table
CREATE TABLE "embed_configs" (
    "id" SERIAL PRIMARY KEY,
    "uuid" TEXT NOT NULL UNIQUE,
    "enabled" BOOLEAN NOT NULL DEFAULT false,
    "chat_mode" TEXT NOT NULL DEFAULT 'query',
    "allowlist_domains" TEXT,
    "allow_model_override" BOOLEAN NOT NULL DEFAULT false,
    "allow_temperature_override" BOOLEAN NOT NULL DEFAULT false,
    "allow_prompt_override" BOOLEAN NOT NULL DEFAULT false,
    "max_chats_per_day" INTEGER,
    "max_chats_per_session" INTEGER,
    "message_limit" INTEGER DEFAULT 20,
    "workspace_id" INTEGER NOT NULL REFERENCES "workspaces"("id") ON DELETE CASCADE,
    "createdBy" INTEGER,
    "usersId" INTEGER REFERENCES "users"("id") ON DELETE SET NULL,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "workspace" FOREIGN KEY ("workspace_id") REFERENCES "workspaces"("id") ON DELETE CASCADE,
    "users" FOREIGN KEY ("usersId") REFERENCES "users"("id") ON DELETE SET NULL
);

-- Embed chats table
CREATE TABLE "embed_chats" (
    "id" SERIAL PRIMARY KEY,
    "prompt" TEXT NOT NULL,
    "response" TEXT NOT NULL,
    "session_id" TEXT NOT NULL,
    "include" BOOLEAN NOT NULL DEFAULT true,
    "connection_information" TEXT,
    "embed_id" INTEGER NOT NULL REFERENCES "embed_configs"("id") ON DELETE CASCADE,
    "usersId" INTEGER REFERENCES "users"("id") ON DELETE SET NULL,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "embed_config" FOREIGN KEY ("embed_id") REFERENCES "embed_configs"("id") ON DELETE CASCADE,
    "users" FOREIGN KEY ("usersId") REFERENCES "users"("id") ON DELETE SET NULL
);

-- Workspace threads table
CREATE TABLE "workspace_threads" (
    "id" SERIAL PRIMARY KEY,
    "name" TEXT NOT NULL,
    "slug" TEXT NOT NULL UNIQUE,
    "workspace_id" INTEGER NOT NULL REFERENCES "workspaces"("id") ON DELETE CASCADE,
    "user_id" INTEGER REFERENCES "users"("id") ON DELETE CASCADE,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "workspace" FOREIGN KEY ("workspace_id") REFERENCES "workspaces"("id") ON DELETE CASCADE,
    "user" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE
);

-- Workspace agent invocations table
CREATE TABLE "workspace_agent_invocations" (
    "id" SERIAL PRIMARY KEY,
    "uuid" TEXT NOT NULL UNIQUE,
    "prompt" TEXT NOT NULL,
    "closed" BOOLEAN NOT NULL DEFAULT false,
    "user_id" INTEGER REFERENCES "users"("id") ON DELETE CASCADE,
    "thread_id" INTEGER,
    "workspace_id" INTEGER NOT NULL REFERENCES "workspaces"("id") ON DELETE CASCADE,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "user" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE,
    "workspace" FOREIGN KEY ("workspace_id") REFERENCES "workspaces"("id") ON DELETE CASCADE
);

-- Slash command presets table
CREATE TABLE "slash_command_presets" (
    "id" SERIAL PRIMARY KEY,
    "command" TEXT NOT NULL,
    "prompt" TEXT NOT NULL,
    "description" TEXT,
    "uid" INTEGER NOT NULL DEFAULT 0,
    "userId" INTEGER REFERENCES "users"("id") ON DELETE CASCADE,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "user" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE
);

-- Recovery codes table
CREATE TABLE "recovery_codes" (
    "id" SERIAL PRIMARY KEY,
    "user_id" INTEGER NOT NULL REFERENCES "users"("id") ON DELETE CASCADE,
    "code_hash" TEXT NOT NULL,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "user" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE
);

-- Password reset tokens table
CREATE TABLE "password_reset_tokens" (
    "id" SERIAL PRIMARY KEY,
    "user_id" INTEGER NOT NULL REFERENCES "users"("id") ON DELETE CASCADE,
    "token" TEXT NOT NULL UNIQUE,
    "expiresAt" TIMESTAMP NOT NULL,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "user" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE
);

-- Browser extension API keys table
CREATE TABLE "browser_extension_api_keys" (
    "id" SERIAL PRIMARY KEY,
    "key" TEXT NOT NULL UNIQUE,
    "user_id" INTEGER REFERENCES "users"("id") ON DELETE CASCADE,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastUpdatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "user" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE
);

-- Temporary auth tokens table
CREATE TABLE "temporary_auth_tokens" (
    "id" SERIAL PRIMARY KEY,
    "token" TEXT NOT NULL UNIQUE,
    "userId" INTEGER NOT NULL REFERENCES "users"("id") ON DELETE CASCADE,
    "expiresAt" TIMESTAMP NOT NULL,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "user" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE
);

-- System prompt variables table
CREATE TABLE "system_prompt_variables" (
    "id" SERIAL PRIMARY KEY,
    "key" TEXT NOT NULL UNIQUE,
    "value" TEXT,
    "description" TEXT,
    "type" TEXT NOT NULL DEFAULT 'system',
    "userId" INTEGER REFERENCES "users"("id") ON DELETE CASCADE,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "user" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE
);

-- Prompt history table
CREATE TABLE "prompt_history" (
    "id" SERIAL PRIMARY KEY,
    "workspaceId" INTEGER NOT NULL REFERENCES "workspaces"("id") ON DELETE CASCADE,
    "prompt" TEXT NOT NULL,
    "modifiedBy" INTEGER REFERENCES "users"("id") ON DELETE SET NULL,
    "modifiedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "workspace" FOREIGN KEY ("workspaceId") REFERENCES "workspaces"("id") ON DELETE CASCADE,
    "user" FOREIGN KEY ("modifiedBy") REFERENCES "users"("id") ON DELETE SET NULL
);

-- Desktop mobile devices table
CREATE TABLE "desktop_mobile_devices" (
    "id" SERIAL PRIMARY KEY,
    "deviceOs" TEXT NOT NULL,
    "deviceName" TEXT NOT NULL,
    "token" TEXT NOT NULL UNIQUE,
    "approved" BOOLEAN NOT NULL DEFAULT false,
    "userId" INTEGER REFERENCES "users"("id") ON DELETE CASCADE,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "user" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE
);

-- Document sync queues table
CREATE TABLE "document_sync_queues" (
    "id" SERIAL PRIMARY KEY,
    "staleAfterMs" INTEGER NOT NULL DEFAULT 604800000,
    "nextSyncAt" TIMESTAMP NOT NULL,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastSyncedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "workspaceDocId" INTEGER NOT NULL UNIQUE REFERENCES "workspace_documents"("id") ON DELETE CASCADE,
    "workspaceDoc" FOREIGN KEY ("workspaceDocId") REFERENCES "workspace_documents"("id") ON DELETE CASCADE
);

-- Document sync executions table
CREATE TABLE "document_sync_executions" (
    "id" SERIAL PRIMARY KEY,
    "queueId" INTEGER NOT NULL REFERENCES "document_sync_queues"("id") ON DELETE CASCADE,
    "status" TEXT NOT NULL DEFAULT 'unknown',
    "result" TEXT,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "queue" FOREIGN KEY ("queueId") REFERENCES "document_sync_queues"("id") ON DELETE CASCADE
);

-- Workspace parsed files table
CREATE TABLE "workspace_parsed_files" (
    "id" SERIAL PRIMARY KEY,
    "filename" TEXT NOT NULL UNIQUE,
    "workspaceId" INTEGER NOT NULL REFERENCES "workspaces"("id") ON DELETE CASCADE,
    "userId" INTEGER REFERENCES "users"("id") ON DELETE CASCADE,
    "threadId" INTEGER REFERENCES "workspace_threads"("id") ON DELETE CASCADE,
    "metadata" TEXT,
    "tokenCountEstimate" INTEGER DEFAULT 0,
    "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "workspace" FOREIGN KEY ("workspaceId") REFERENCES "workspaces"("id") ON DELETE CASCADE,
    "user" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE,
    "thread" FOREIGN KEY ("threadId") REFERENCES "workspace_threads"("id") ON DELETE CASCADE
);

-- Create indexes for better performance
CREATE INDEX "workspace_documents_workspaceId_idx" ON "workspace_documents"("workspaceId");
CREATE INDEX "workspace_chats_workspaceId_idx" ON "workspace_chats"("workspaceId");
CREATE INDEX "workspace_chats_user_id_idx" ON "workspace_chats"("user_id");
CREATE INDEX "workspace_users_user_id_idx" ON "workspace_users"("user_id");
CREATE INDEX "workspace_users_workspace_id_idx" ON "workspace_users"("workspace_id");
CREATE INDEX "workspace_threads_workspace_id_idx" ON "workspace_threads"("workspace_id");
CREATE INDEX "workspace_threads_user_id_idx" ON "workspace_threads"("user_id");
CREATE INDEX "workspace_agent_invocations_workspace_id_idx" ON "workspace_agent_invocations"("workspace_id");
CREATE INDEX "workspace_agent_invocations_user_id_idx" ON "workspace_agent_invocations"("user_id");
CREATE INDEX "workspace_agent_invocations_uuid_idx" ON "workspace_agent_invocations"("uuid");
CREATE INDEX "workspace_suggested_messages_workspaceId_idx" ON "workspace_suggested_messages"("workspaceId");
CREATE INDEX "event_logs_event_idx" ON "event_logs"("event");
CREATE INDEX "event_logs_userId_idx" ON "event_logs"("userId");
CREATE INDEX "cache_data_expiresAt_idx" ON "cache_data"("expiresAt");
CREATE INDEX "embed_configs_workspace_id_idx" ON "embed_configs"("workspace_id");
CREATE INDEX "embed_chats_embed_id_idx" ON "embed_chats"("embed_id");
CREATE INDEX "embed_chats_session_id_idx" ON "embed_chats"("session_id");
CREATE INDEX "document_sync_queues_workspaceDocId_idx" ON "document_sync_queues"("workspaceDocId");
CREATE INDEX "document_sync_executions_queueId_idx" ON "document_sync_executions"("queueId");
CREATE INDEX "slash_command_presets_uid_idx" ON "slash_command_presets"("uid");
CREATE INDEX "slash_command_presets_userId_idx" ON "slash_command_presets"("userId");
CREATE INDEX "recovery_codes_user_id_idx" ON "recovery_codes"("user_id");
CREATE INDEX "password_reset_tokens_user_id_idx" ON "password_reset_tokens"("user_id");
CREATE INDEX "password_reset_tokens_token_idx" ON "password_reset_tokens"("token");
CREATE INDEX "temporary_auth_tokens_userId_idx" ON "temporary_auth_tokens"("userId");
CREATE INDEX "temporary_auth_tokens_token_idx" ON "temporary_auth_tokens"("token");
CREATE INDEX "system_prompt_variables_userId_idx" ON "system_prompt_variables"("userId");
CREATE INDEX "prompt_history_workspaceId_idx" ON "prompt_history"("workspaceId");
CREATE INDEX "prompt_history_modifiedBy_idx" ON "prompt_history"("modifiedBy");
CREATE INDEX "desktop_mobile_devices_userId_idx" ON "desktop_mobile_devices"("userId");
CREATE INDEX "desktop_mobile_devices_token_idx" ON "desktop_mobile_devices"("token");
CREATE INDEX "workspace_parsed_files_workspaceId_idx" ON "workspace_parsed_files"("workspaceId");
CREATE INDEX "workspace_parsed_files_userId_idx" ON "workspace_parsed_files"("userId");
CREATE INDEX "workspace_parsed_files_threadId_idx" ON "workspace_parsed_files"("threadId");
`;

fs.writeFileSync(newMigrationFile, migrationSQL);
console.log(`✅ 已创建新的迁移文件: ${newMigrationFile}`);

// 4. 清理迁移锁定文件
const lockFile = path.join(migrationsDir, 'migration_lock.toml');
if (fs.existsSync(lockFile)) {
  fs.unlinkSync(lockFile);
  console.log('🔓 已删除迁移锁定文件');
}

console.log('\n🎉 PostgreSQL 迁移修复完成！');
console.log('\n📋 后续步骤:');
console.log('1. 确保 DATABASE_URL 环境变量指向 PostgreSQL 数据库');
console.log('2. 运行: cd server && npx prisma migrate dev');
console.log('3. 运行: npx prisma generate');
console.log('4. 运行: npx prisma db seed');