server/prisma/check-migration-dependencies.js

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

/**
 * 检查迁移文件的外键依赖关系
 */

// 存储表创建和引用信息的映射
const tableCreationOrder = {};
const foreignKeyReferences = {};

/**
 * 解析迁移文件中的表创建和外键引用
 */
function parseMigrationFile(filePath) {
  try {
    const sqlContent = fs.readFileSync(filePath, 'utf8');
    const lines = sqlContent.split('\n');

    let currentTable = null;
    let inCreateTable = false;

    lines.forEach((line, index) => {
      const trimmedLine = line.trim();

      // 检测 CREATE TABLE 语句
      if (trimmedLine.startsWith('CREATE TABLE')) {
        const tableMatch = trimmedLine.match(/CREATE TABLE "([^"]+)"/);
        if (tableMatch) {
          currentTable = tableMatch[1];
          inCreateTable = true;
          tableCreationOrder[currentTable] = path.basename(path.dirname(filePath));
        }
      }

      // 检测外键约束
      if (trimmedLine.includes('FOREIGN KEY') && trimmedLine.includes('REFERENCES')) {
        const fkMatch = trimmedLine.match(/FOREIGN KEY \("[^"]+"\)\s+REFERENCES\s+"([^"]+)"/);
        if (fkMatch && currentTable) {
          const referencedTable = fkMatch[1];
          if (!foreignKeyReferences[currentTable]) {
            foreignKeyReferences[currentTable] = [];
          }
          foreignKeyReferences[currentTable].push({
            referencedTable,
            line: index + 1
          });
        }
      }

      // 检测表创建结束
      if (inCreateTable && trimmedLine === ');') {
        inCreateTable = false;
        currentTable = null;
      }
    });

    return true;
  } catch (error) {
    console.error(`解析文件失败: ${filePath}`, error.message);
    return false;
  }
}

/**
 * 递归处理目录中的所有迁移文件
 */
function processMigrationsDirectory(migrationsDir) {
  const items = fs.readdirSync(migrationsDir);

  items.forEach(item => {
    const itemPath = path.join(migrationsDir, item);
    const stat = fs.statSync(itemPath);

    if (stat.isDirectory() && item !== 'migration_lock.toml') {
      // 递归处理子目录
      processMigrationsDirectory(itemPath);
    } else if (item === 'migration.sql') {
      // 处理迁移文件
      parseMigrationFile(itemPath);
    }
  });
}

/**
 * 检查依赖关系
 */
function checkDependencies() {
  console.log('=== 迁移文件外键依赖关系检查 ===\n');

  const issues = [];

  // 检查每个表的外键引用
  Object.entries(foreignKeyReferences).forEach(([table, references]) => {
    references.forEach(({ referencedTable, line }) => {
      // 检查被引用的表是否在当前迁移或更早的迁移中创建
      if (tableCreationOrder[referencedTable]) {
        const currentMigration = tableCreationOrder[table];
        const referencedMigration = tableCreationOrder[referencedTable];

        // 如果被引用的表在更新的迁移中创建，这可能有问题
        if (currentMigration < referencedMigration) {
          issues.push({
            table,
            referencedTable,
            issue: `表 ${table} 引用了在更晚迁移中创建的表 ${referencedTable}`,
            migration: currentMigration
          });
        }
      } else {
        issues.push({
          table,
          referencedTable,
          issue: `表 ${table} 引用了不存在的表 ${referencedTable}`,
          migration: tableCreationOrder[table]
        });
      }
    });
  });

  if (issues.length === 0) {
    console.log('✅ 没有发现外键依赖关系问题');
  } else {
    console.log('❌ 发现以下外键依赖关系问题:');
    issues.forEach(issue => {
      console.log(`  表: ${issue.table}`);
      console.log(`  引用表: ${issue.referencedTable}`);
      console.log(`  问题: ${issue.issue}`);
      console.log(`  迁移: ${issue.migration}`);
      console.log('');
    });
  }

  return issues;
}

/**
 * 显示表创建顺序
 */
function showTableCreationOrder() {
  console.log('\n=== 表创建顺序 ===');

  const sortedTables = Object.entries(tableCreationOrder)
    .sort(([,a], [,b]) => a.localeCompare(b))
    .map(([table, migration]) => `  ${table} (${migration})`);

  sortedTables.forEach(table => console.log(table));
}

/**
 * 主函数
 */
function main() {
  const migrationsDir = path.join(__dirname, 'migrations');

  console.log('开始检查迁移文件外键依赖关系...\n');

  processMigrationsDirectory(migrationsDir);

  showTableCreationOrder();
  const issues = checkDependencies();

  return {
    tableCreationOrder,
    foreignKeyReferences,
    issues: issues.length
  };
}

// 运行检查
if (require.main === module) {
  main();
}

module.exports = { parseMigrationFile, processMigrationsDirectory, checkDependencies };