const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');
require('dotenv').config();

async function createRegulatoryTables() {
  let connection;
  
  try {
    // 创建数据库连接
    connection = await mysql.createConnection({
      host: process.env.DB_HOST || '129.211.213.226',
      port: process.env.DB_PORT || 9527,
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || 'aiotAiot123!',
      database: process.env.DB_DATABASE || 'nxxmdata',
      multipleStatements: true
    });

    console.log('数据库连接成功');

    // 读取SQL文件
    const sqlFilePath = path.join(__dirname, 'regulatory_task_completion.sql');
    const sqlContent = fs.readFileSync(sqlFilePath, 'utf8');

    // 分割SQL语句（按分号分割，但忽略注释中的分号）
    const sqlStatements = sqlContent
      .split('\n')
      .filter(line => !line.trim().startsWith('--') && line.trim() !== '')
      .join('\n')
      .split(';')
      .filter(statement => statement.trim() !== '');

    console.log(`准备执行 ${sqlStatements.length} 条SQL语句`);

    // 逐条执行SQL语句
    for (let i = 0; i < sqlStatements.length; i++) {
      const statement = sqlStatements[i].trim();
      if (statement) {
        try {
          console.log(`执行第 ${i + 1} 条SQL语句...`);
          await connection.execute(statement);
          console.log(`第 ${i + 1} 条SQL语句执行成功`);
        } catch (error) {
          console.error(`第 ${i + 1} 条SQL语句执行失败:`, error.message);
          console.error('SQL语句:', statement.substring(0, 100) + '...');
        }
      }
    }

    // 验证表是否创建成功
    const [tables] = await connection.execute(`
      SELECT TABLE_NAME, TABLE_COMMENT 
      FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_SCHEMA = ? AND TABLE_NAME LIKE 'regulatory_task_%'
    `, [process.env.DB_DATABASE || 'nxxmdata']);

    console.log('\n创建的监管任务结项相关表:');
    tables.forEach(table => {
      console.log(`- ${table.TABLE_NAME}: ${table.TABLE_COMMENT}`);
    });

    // 查询示例数据
    const [sampleData] = await connection.execute(`
      SELECT application_no, policy_no, customer_name, completion_status 
      FROM regulatory_task_completions 
      LIMIT 3
    `);

    console.log('\n示例数据:');
    console.table(sampleData);

    console.log('\n监管任务结项表创建完成！');

  } catch (error) {
    console.error('创建表时发生错误:', error);
  } finally {
    if (connection) {
      await connection.end();
      console.log('数据库连接已关闭');
    }
  }
}

// 执行创建表操作
createRegulatoryTables();