const mysql = require('mysql2/promise');
const { dbConfig } = require('../config/config');

/**
 * 验证表合并结果的脚本
 * 检查 order_allocations 表和 transfers 表的数据一致性
 */
async function verifyMerge() {
  console.log('开始验证表合并结果...');
  console.log('=' .repeat(60));
  
  let connection;
  
  try {
    // 创建数据库连接
    connection = await mysql.createConnection({
      host: dbConfig.host,
      user: dbConfig.user,
      password: dbConfig.password,
      database: dbConfig.database
    });
    
    // 1. 检查 order_allocations 表中有多少条记录
    const [allocationCount] = await connection.execute(
      'SELECT COUNT(*) as count FROM order_allocations'
    );
    console.log(`order_allocations 表总记录数: ${allocationCount[0].count}`);
    
    // 2. 检查 transfers 表中有多少条 allocation 类型的记录
    const [transferCount] = await connection.execute(
      'SELECT COUNT(*) as count FROM transfers WHERE source_type = \'allocation\''
    );
    console.log(`transfers 表中 allocation 类型记录数: ${transferCount[0].count}`);
    
    // 3. 检查 order_allocations 表中有多少条记录没有关联的 transfer_id
    const [unlinkedCount] = await connection.execute(
      'SELECT COUNT(*) as count FROM order_allocations WHERE transfer_id IS NULL'
    );
    console.log(`order_allocations 表中无关联 transfer_id 的记录数: ${unlinkedCount[0].count}`);
    
    // 4. 检查数据一致性 - 抽样检查
    console.log('\n数据一致性检查（抽样）:');
    const [sampleAllocations] = await connection.execute(
      'SELECT * FROM order_allocations WHERE transfer_id IS NOT NULL LIMIT 5'
    );
    
    for (const allocation of sampleAllocations) {
      const [transfer] = await connection.execute(
        'SELECT * FROM transfers WHERE id = ?',
        [allocation.transfer_id]
      );
      
      if (transfer.length === 0) {
        console.log(`  ✗ 错误: allocation_id=${allocation.id} 关联的 transfer_id=${allocation.transfer_id} 不存在`);
        continue;
      }
      
      const transferRecord = transfer[0];
      const isConsistent = 
        transferRecord.from_user_id == allocation.from_user_id &&
        transferRecord.to_user_id == allocation.to_user_id &&
        transferRecord.amount == allocation.amount &&
        transferRecord.matching_order_id == allocation.matching_order_id &&
        transferRecord.cycle_number == allocation.cycle_number;
      
      if (isConsistent) {
        console.log(`  ✓ allocation_id=${allocation.id} 与 transfer_id=${allocation.transfer_id} 数据一致`);
      } else {
        console.log(`  ✗ 错误: allocation_id=${allocation.id} 与 transfer_id=${allocation.transfer_id} 数据不一致`);
        console.log('    allocation:', {
          from_user_id: allocation.from_user_id,
          to_user_id: allocation.to_user_id,
          amount: allocation.amount,
          matching_order_id: allocation.matching_order_id,
          cycle_number: allocation.cycle_number
        });
        console.log('    transfer:', {
          from_user_id: transferRecord.from_user_id,
          to_user_id: transferRecord.to_user_id,
          amount: transferRecord.amount,
          matching_order_id: transferRecord.matching_order_id,
          cycle_number: transferRecord.cycle_number
        });
      }
    }
    
    console.log('\n' + '=' .repeat(60));
    
    // 总结
    if (allocationCount[0].count === transferCount[0].count && unlinkedCount[0].count === 0) {
      console.log('✓ 验证成功! 所有 order_allocations 记录都已正确迁移到 transfers 表');
    } else {
      console.log('⚠ 验证结果: 可能存在未完全迁移的数据');
      console.log(`  - order_allocations 总数: ${allocationCount[0].count}`);
      console.log(`  - transfers 中 allocation 类型数: ${transferCount[0].count}`);
      console.log(`  - 未关联记录数: ${unlinkedCount[0].count}`);
    }
    
  } catch (error) {
    console.error('验证失败:', error);
    throw error;
  } finally {
    // 关闭数据库连接
    if (connection) {
      await connection.end();
    }
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  verifyMerge().catch(console.error);
}

module.exports = verifyMerge;