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

/**
 * 验证 PostgreSQL 转换是否正确
 */

// 需要检查的 SQLite 语法模式
const sqlitePatterns = [
  /AUTOINCREMENT/i,
  /DATETIME/i,
  /INTEGER\s+NOT\s+NULL\s+PRIMARY\s+KEY\s+AUTOINCREMENT/i
];

/**
 * 验证单个 SQL 文件
 */
function verifySqlFile(filePath) {
  try {
    const sqlContent = fs.readFileSync(filePath, 'utf8');
    const issues = [];

    // 检查是否还有 SQLite 语法
    sqlitePatterns.forEach(pattern => {
      if (pattern.test(sqlContent)) {
        issues.push(`发现 SQLite 语法: ${pattern.source}`);
      }
    });

    // 检查 PostgreSQL 语法是否正确
    if (!sqlContent.includes('SERIAL PRIMARY KEY') && sqlContent.includes('CREATE TABLE')) {
      // 检查是否有 SERIAL 或者 SERIAL PRIMARY KEY
      const hasSerial = /\bSERIAL\b/.test(sqlContent);
      const hasPrimaryKey = /\bPRIMARY\s+KEY\b/.test(sqlContent);

      if (!hasSerial && !hasPrimaryKey) {
        issues.push('缺少 SERIAL 或 PRIMARY KEY 定义');
      }
    }

    return {
      file: path.basename(filePath),
      issues: issues,
      isValid: issues.length === 0
    };
  } catch (error) {
    return {
      file: path.basename(filePath),
      issues: [`读取文件失败: ${error.message}`],
      isValid: false
    };
  }
}

/**
 * 递归验证目录中的所有迁移文件
 */
function verifyMigrationsDirectory(migrationsDir) {
  const items = fs.readdirSync(migrationsDir);
  const results = [];

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

    if (stat.isDirectory() && item !== 'migration_lock.toml') {
      // 递归处理子目录
      const subResults = verifyMigrationsDirectory(itemPath);
      results.push(...subResults);
    } else if (item === 'migration.sql') {
      // 验证迁移文件
      results.push(verifySqlFile(itemPath));
    }
  });

  return results;
}

/**
 * 生成验证报告
 */
function generateReport(results) {
  const validFiles = results.filter(r => r.isValid);
  const invalidFiles = results.filter(r => !r.isValid);

  console.log('=== PostgreSQL 转换验证报告 ===\n');

  console.log(`总文件数: ${results.length}`);
  console.log(`有效文件: ${validFiles.length}`);
  console.log(`有问题的文件: ${invalidFiles.length}\n`);

  if (invalidFiles.length > 0) {
    console.log('=== 有问题的文件 ===');
    invalidFiles.forEach(result => {
      console.log(`\n📄 ${result.file}:`);
      result.issues.forEach(issue => {
        console.log(`  ❌ ${issue}`);
      });
    });
  }

  console.log('\n=== 有效文件 ===');
  validFiles.forEach(result => {
    console.log(`  ✅ ${result.file}`);
  });

  return {
    total: results.length,
    valid: validFiles.length,
    invalid: invalidFiles.length
  };
}

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

  console.log('开始验证 PostgreSQL 转换...\n');

  const results = verifyMigrationsDirectory(migrationsDir);
  const report = generateReport(results);

  if (report.invalid === 0) {
    console.log('\n🎉 所有文件都已正确转换为 PostgreSQL 语法！');
  } else {
    console.log(`\n⚠️  发现 ${report.invalid} 个文件需要手动检查。`);
  }

  return report;
}

// 运行验证
if (require.main === module) {
  main();
}

module.exports = { verifySqlFile, verifyMigrationsDirectory, generateReport };