/**
 * 批量终止锁定的MySQL进程
 * 安全版本 - 会列出要终止的进程供确认
 */

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

// 创建命令行接口用于用户确认
const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout
});

function question(query) {
    return new Promise(resolve => rl.question(query, resolve));
}

async function killLockedProcesses() {
    let connection;

    try {
        connection = await mysql.createConnection({
            host: process.env.DB_HOST,
            port: process.env.DB_PORT || 3306,
            user: process.env.DB_USER,
            password: process.env.DB_PASSWORD,
            database: process.env.DB_DATABASE
        });

        console.log('✅ 已连接到数据库\n');

        // 查找所有符合条件的进程
        console.log('📋 查找需要终止的进程...\n');

        const [processes] = await connection.query(`
            SELECT Id, User, Host, db, Command, Time, State, Info
            FROM INFORMATION_SCHEMA.PROCESSLIST
            WHERE (
                State LIKE '%waiting for handler commit%'
                OR State LIKE '%Locked%'
                OR (Command = 'Sleep' AND Time > 300)
            )
            AND Id != CONNECTION_ID()
        `);

        if (processes.length === 0) {
            console.log('✅ 没有找到需要终止的进程\n');
            rl.close();
            return;
        }

        // 显示找到的进程
        console.log(`找到 ${processes.length} 个需要终止的进程：\n`);
        console.log('ID\t用户\t\t命令\t时间(秒)\t状态');
        console.log('─'.repeat(80));

        processes.forEach(p => {
            const user = (p.User || '').padEnd(12);
            const command = (p.Command || '').padEnd(8);
            const time = String(p.Time || 0).padEnd(8);
            const state = (p.State || 'Sleep').substring(0, 40);
            console.log(`${p.Id}\t${user}\t${command}\t${time}\t${state}`);
        });

        console.log('\n');

        // 询问用户确认
        const answer = await question('确认要终止这些进程吗？(yes/no): ');

        if (answer.toLowerCase() !== 'yes' && answer.toLowerCase() !== 'y') {
            console.log('\n❌ 操作已取消\n');
            rl.close();
            return;
        }

        console.log('\n🔪 开始终止进程...\n');

        // 批量终止进程
        let successCount = 0;
        let failCount = 0;

        for (const p of processes) {
            try {
                await connection.query(`KILL ${p.Id}`);
                console.log(`✅ 已终止进程 ${p.Id} (${p.Command}, ${p.State})`);
                successCount++;
            } catch (err) {
                console.log(`❌ 无法终止进程 ${p.Id}: ${err.message}`);
                failCount++;
            }
        }

        console.log('\n' + '═'.repeat(80));
        console.log(`✅ 成功终止: ${successCount} 个进程`);
        if (failCount > 0) {
            console.log(`❌ 失败: ${failCount} 个进程`);
        }
        console.log('═'.repeat(80) + '\n');

        // 验证结果
        console.log('📋 验证当前进程状态...\n');
        const [remaining] = await connection.query(`
            SELECT COUNT(*) as count
            FROM INFORMATION_SCHEMA.PROCESSLIST
            WHERE (
                State LIKE '%waiting for handler commit%'
                OR State LIKE '%Locked%'
                OR (Command = 'Sleep' AND Time > 300)
            )
            AND Id != CONNECTION_ID()
        `);

        if (remaining[0].count === 0) {
            console.log('✅ 所有锁定的进程已清除！\n');
        } else {
            console.log(`⚠️  仍有 ${remaining[0].count} 个锁定的进程\n`);
        }

        rl.close();

    } catch (error) {
        console.error('❌ 操作失败:', error.message);
        console.error('\n提示：如果遇到权限问题，请联系数据库管理员\n');
        rl.close();
        process.exit(1);
    } finally {
        if (connection) {
            await connection.end();
        }
    }
}

// 执行
killLockedProcesses();