bot/admin/index.js

require('dotenv').config();
const express = require('express');
const cors = require('cors');
const path = require('path');
const TelegramBot = require('node-telegram-bot-api');
const fs = require('fs');
const moment = require('moment');
const {
    pool,
    testConnection
} = require('./config/database');
// const initDatabase = require('./config/initDb');
const Group = require('./models/Group');
const Transaction = require('./models/Transaction');

// 日志格式化函数
function formatLog(data) {
    const separator = '-'.repeat(30);
    let logMessage = `${separator}\n`;

    if (typeof data === 'object') {
        logMessage += Object.entries(data)
            .map(([key, value]) => `${key}: ${value}`)
            .join('\n');
    } else {
        logMessage += data;
    }

    logMessage += `\n${separator}\n`;
    return logMessage;
}

const app = express();

// 初始化数据存储
let data = {
    deposits: [], // 入款记录
    withdrawals: [], // 下发记录
    lastUpdate: null,
    allowedGroups: [] // 允许使用的群组ID
};

// 创建机器人实例
const bot = new TelegramBot(process.env.BOT_TOKEN, {
    polling: true
});

// 中间件
app.use(cors());
app.use(express.json());
app.use(express.urlencoded({ extended: true }));
app.use('/admin/views', express.static(path.join(__dirname, 'views')));

// 路由
app.get('/', (req, res) => {
    res.sendFile(path.join(__dirname, 'views', 'login.html'));
});

app.use('/api/users', require('./routes/userRoutes'));
app.use('/api/groups', require('./routes/groupRoutes'));
app.use('/api/transactions', require('./routes/transactionRoutes'));
app.use('/api/statistics', require('./routes/statisticsRoutes'));
app.use('/api/settings', require('./routes/settingsRoutes'));

// 检查群组权限
function isGroupAllowed(chatId) {
    const chatIdStr = chatId.toString();
    return data.allowedGroups.includes(chatIdStr) ||
        data.allowedGroups.includes(chatIdStr.replace('-', ''));
}

// 前置处理
// 检查用户是否有权限操作机器人
async function checkUserPermission(chatId, userId) {
    try {
        const [group] = await pool.query(
            'SELECT creator_id, COALESCE(operators, "[]") as operators FROM groups WHERE group_id = ?',
            [chatId.toString()]
        );

        if (!group || !group[0]) {
            console.log(`权限检查失败 - 群组不存在: ${chatId}`);
            return false;
        }

        const groupInfo = group[0];
        const userIdStr = userId.toString();
        const isCreator = groupInfo.creator_id === userIdStr;
        
        let operators = [];
        try {
            if (groupInfo.operators) {
                operators = JSON.parse(groupInfo.operators);
                if (!Array.isArray(operators)) {
                    operators = [];
                }
            }
        } catch (e) {
            console.error('解析操作人列表失败:', e);
            operators = [];
        }
        // console.log(groupInfo.operators)
        // console.log(operators);
        // console.log(userIdStr);
        const isOperator = operators.some(op => op.operator_id == userIdStr);
        
        // 只在权限检查失败时输出详细日志
        if (!isCreator && !isOperator) {
            console.log(`权限检查失败 - 用户ID: ${userIdStr}, 群组ID: ${chatId}, 创建者ID: ${groupInfo.creator_id}, 操作人数量: ${operators.length}`);
        }
        
        return isCreator || isOperator;
    } catch (error) {
        console.error('检查用户权限失败:', error);
        return false;
    }
}
// 处理消息发送
async function sendMessage(chatId, text, options = {}) {
    try {
        // 如果包含内联键盘，验证URL
        if (options.reply_markup && options.reply_markup.inline_keyboard) {
            const keyboard = generateInlineKeyboard(chatId);
            if (!keyboard) {
                // 如果键盘无效，发送不带键盘的消息
                return await bot.sendMessage(chatId, text, {
                    parse_mode: 'HTML'
                });
            }
            options.reply_markup = keyboard;
        }
        return await bot.sendMessage(chatId, text, {
            ...options,
            parse_mode: 'HTML'
        });
    } catch (error) {
        console.error('发送消息失败:', error);
        if (error.message.includes('bot was kicked from the group chat')) {
            const index = data.allowedGroups.indexOf(chatId.toString());
            if (index > -1) {
                data.allowedGroups.splice(index, 1);
                saveData();
                console.log(`群组 ${chatId} 已被移除出允许列表`);
            }
        }
        return null;
    }
}

// - 1.快捷命令
bot.on('message', async (msg) => {
    if (!isGroupAllowed(msg.chat.id)) return;

    const text = msg.text?.trim();
    if (!text) return;
    console.error(msg);
    // 0. 检查用户权限
    const hasPermission = await checkUserPermission(msg.chat.id, msg.from.id);
    if (!hasPermission) {
        // 如果不是创建人或操作人，直接返回
        return;
    }

    // 1. 处理入款命令
    if (text.startsWith('+')) {
        let amount, exchangeRate, feeRate;
        const parts = text.substring(1).split('/');
        amount = parseFloat(parts[0]);
        
        // 如果指定了汇率，则使用指定的汇率
        if (parts.length > 1) {
            exchangeRate = parseFloat(parts[1]);
        }
        
        // 如果指定了费率，则使用指定的费率
        if (parts.length > 2) {
            feeRate = parseFloat(parts[2]);
        }

        if (!isNaN(amount)) {
            const transactionData = {
                groupId: msg.chat.id.toString(),
                groupName: msg.chat.title || '未命名群组',
                amount: amount,
                type: 'deposit',
                exchangeRate: exchangeRate,
                feeRate: feeRate,
                operatorId: msg.from.id
            };

            // console.log(transactionData);

            try {
                const result = await Transaction.deposit(transactionData);
                if (result.success) {
                    const billMessage = await generateBillMessage(msg.chat.id);
                    if (billMessage) {
                        await sendMessage(msg.chat.id, billMessage, {
                            reply_markup: generateInlineKeyboard(msg.chat.id)
                        });
                        console.log(`入款成功 - 群组: ${msg.chat.title}, 金额: ${amount}, 时间: ${new Date().toLocaleString()}`);
                    } else {
                        await sendMessage(msg.chat.id, '入款成功，但获取账单信息失败');
                        console.log(`入款成功(无账单) - 群组: ${msg.chat.title}, 金额: ${amount}, 时间: ${new Date().toLocaleString()}`);
                    }
                } else {
                    await sendMessage(msg.chat.id, result.message || '入款失败');
                    console.log(`入款失败 - 群组: ${msg.chat.title}, 金额: ${amount}, 原因: ${result.message}, 时间: ${new Date().toLocaleString()}`);
                }
            } catch (error) {
                console.error('快捷入款失败:', error);
                await sendMessage(msg.chat.id, '记录入款失败，请稍后重试');
            }
        }
    } 
    // 1.1 处理入款修正命令
    else if (text.startsWith('-') && !text.includes('下发')) {
        let amount, exchangeRate, feeRate;
        const parts = text.substring(1).split('/');
        amount = parseFloat(parts[0]);
        
        // 如果指定了汇率，则使用指定的汇率
        if (parts.length > 1) {
            exchangeRate = parseFloat(parts[1]);
        }
        
        // 如果指定了费率，则使用指定的费率
        if (parts.length > 2) {
            feeRate = parseFloat(parts[2]);
        }

        if (!isNaN(amount)) {
            const transactionData = {
                groupId: msg.chat.id.toString(),
                groupName: msg.chat.title || '未命名群组',
                amount: -amount,
                type: 'deposit',
                exchangeRate: exchangeRate,
                feeRate: feeRate,
                operatorId: msg.from.id
            };

            try {
                const result = await Transaction.deposit(transactionData);
                if (result.success) {
                    const billMessage = await generateBillMessage(msg.chat.id);
                    if (billMessage) {
                        await sendMessage(msg.chat.id, billMessage, {
                            reply_markup: generateInlineKeyboard(msg.chat.id)
                        });
                        console.log(`入款修正成功 - 群组: ${msg.chat.title}, 金额: -${amount}, 时间: ${new Date().toLocaleString()}`);
                    } else {
                        await sendMessage(msg.chat.id, '入款修正成功，但获取账单信息失败');
                        console.log(`入款修正成功(无账单) - 群组: ${msg.chat.title}, 金额: -${amount}, 时间: ${new Date().toLocaleString()}`);
                    }
                } else {
                    await sendMessage(msg.chat.id, result.message || '入款修正失败');
                    console.log(`入款修正失败 - 群组: ${msg.chat.title}, 金额: -${amount}, 原因: ${result.message}, 时间: ${new Date().toLocaleString()}`);
                }
            } catch (error) {
                console.error('快捷入款修正失败:', error);
                await sendMessage(msg.chat.id, '记录入款修正失败，请稍后重试');
            }
        }
    }

    // 2. 处理回款命令
    else if (text.startsWith('下发')) {
        let amount, exchangeRate, feeRate;
        const parts = text.replace(/[^0-9./-]/g, '').split('/');
        amount = parseFloat(parts[0]);
        
        // 如果指定了汇率，则使用指定的汇率
        if (parts.length > 1) {
            exchangeRate = parseFloat(parts[1]);
        }
        
        // 如果指定了费率，则使用指定的费率
        if (parts.length > 2) {
            feeRate = parseFloat(parts[2]);
        }

        if (!isNaN(amount)) {
            const transactionData = {
                groupId: msg.chat.id.toString(),
                groupName: msg.chat.title || '未命名群组',
                amount: amount,
                type: 'withdrawal',
                exchangeRate: exchangeRate,
                feeRate: feeRate,
                operatorId: msg.from.id
            };

            try {
                const result = await Transaction.withdrawal(transactionData);
                if (result.success) {
                    const billMessage = await generateBillMessage(msg.chat.id);
                    if (billMessage) {
                        await sendMessage(msg.chat.id, billMessage, {
                            reply_markup: generateInlineKeyboard(msg.chat.id)
                        });
                        console.log(`回款成功 - 群组: ${msg.chat.title}, 金额: ${amount}, 时间: ${new Date().toLocaleString()}`);
                    } else {
                        await sendMessage(msg.chat.id, '回款成功，但获取账单信息失败');
                        console.log(`回款成功(无账单) - 群组: ${msg.chat.title}, 金额: ${amount}, 时间: ${new Date().toLocaleString()}`);
                    }
                } else {
                    await sendMessage(msg.chat.id, result.message || '回款失败');
                    console.log(`回款失败 - 群组: ${msg.chat.title}, 金额: ${amount}, 原因: ${result.message}, 时间: ${new Date().toLocaleString()}`);
                }
            } catch (error) {
                console.error('快捷回款失败:', error);
                await sendMessage(msg.chat.id, '记录回款失败，请稍后重试');
            }
        }
    }
    // 2.1 处理回款修正命令
    else if (text.startsWith('下发-')) {
        let amount, exchangeRate, feeRate;
        const parts = text.replace(/[^0-9./-]/g, '').split('/');
        amount = parseFloat(parts[0]);
        
        // 如果指定了汇率，则使用指定的汇率
        if (parts.length > 1) {
            exchangeRate = parseFloat(parts[1]);
        }
        
        // 如果指定了费率，则使用指定的费率
        if (parts.length > 2) {
            feeRate = parseFloat(parts[2]);
        }

        if (!isNaN(amount)) {
            const transactionData = {
                groupId: msg.chat.id.toString(),
                groupName: msg.chat.title || '未命名群组',
                amount: -amount,
                type: 'withdrawal',
                exchangeRate: exchangeRate,
                feeRate: feeRate,
                operatorId: msg.from.id
            };

            try {
                const result = await Transaction.withdrawal(transactionData);
                if (result.success) {
                    const billMessage = await generateBillMessage(msg.chat.id);
                    if (billMessage) {
                        await sendMessage(msg.chat.id, billMessage, {
                            reply_markup: generateInlineKeyboard(msg.chat.id)
                        });
                        console.log(`回款修正成功 - 群组: ${msg.chat.title}, 金额: -${amount}, 时间: ${new Date().toLocaleString()}`);
                    } else {
                        await sendMessage(msg.chat.id, '回款修正成功，但获取账单信息失败');
                        console.log(`回款修正成功(无账单) - 群组: ${msg.chat.title}, 金额: -${amount}, 时间: ${new Date().toLocaleString()}`);
                    }
                } else {
                    await sendMessage(msg.chat.id, result.message || '回款修正失败');
                    console.log(`回款修正失败 - 群组: ${msg.chat.title}, 金额: -${amount}, 原因: ${result.message}, 时间: ${new Date().toLocaleString()}`);
                }
            } catch (error) {
                console.error('快捷回款修正失败:', error);
                await sendMessage(msg.chat.id, '记录回款修正失败，请稍后重试');
            }
        }
    }

    // 3. 处理设置费率命令
    else if (text.startsWith('设置费率')) {
        const feeRate = parseFloat(text.replace('设置费率', '').trim());
        
        if (!isNaN(feeRate) && feeRate >= 0 && feeRate <= 100) {
            try {
                // 更新群组的入款和出款费率
                await pool.query(`
                    UPDATE groups 
                    SET in_fee_rate = ?,
                        out_fee_rate = ?,
                        updated_at = CURRENT_TIMESTAMP
                    WHERE group_id = ?
                `, [feeRate, feeRate, msg.chat.id.toString()]);

                await sendMessage(msg.chat.id, `费率${feeRate}%已设置成功`);
                console.log(`费率设置成功 - 群组: ${msg.chat.title}, 费率: ${feeRate}%, 时间: ${new Date().toLocaleString()}`);
            } catch (error) {
                console.error('设置费率失败:', error);
                await sendMessage(msg.chat.id, '设置费率失败，请稍后重试');
            }
        } else {
            await sendMessage(msg.chat.id, '费率设置失败，请输入0-100之间的数字');
        }
    }

    // 3.1 处理设置入款费率命令
    else if (text.startsWith('设置入款费率')) {
        const feeRate = parseFloat(text.replace('设置入款费率', '').trim());
        
        if (!isNaN(feeRate) && feeRate >= 0 && feeRate <= 100) {
            try {
                // 只更新群组的入款费率
                await pool.query(`
                    UPDATE groups 
                    SET in_fee_rate = ?,
                        updated_at = CURRENT_TIMESTAMP
                    WHERE group_id = ?
                `, [feeRate, msg.chat.id.toString()]);

                await sendMessage(msg.chat.id, `入款费率${feeRate}%已设置成功`);
                console.log(`入款费率设置成功 - 群组: ${msg.chat.title}, 费率: ${feeRate}%, 时间: ${new Date().toLocaleString()}`);
            } catch (error) {
                console.error('设置入款费率失败:', error);
                await sendMessage(msg.chat.id, '设置入款费率失败，请稍后重试');
            }
        } else {
            await sendMessage(msg.chat.id, '入款费率设置失败，请输入0-100之间的数字');
        }
    }

    // 3.2 处理设置出款费率命令
    else if (text.startsWith('设置出款费率')) {
        const feeRate = parseFloat(text.replace('设置出款费率', '').trim());
        
        if (!isNaN(feeRate) && feeRate >= 0 && feeRate <= 100) {
            try {
                // 只更新群组的出款费率
                await pool.query(`
                    UPDATE groups 
                    SET out_fee_rate = ?,
                        updated_at = CURRENT_TIMESTAMP
                    WHERE group_id = ?
                `, [feeRate, msg.chat.id.toString()]);

                await sendMessage(msg.chat.id, `出款费率${feeRate}%已设置成功`);
                console.log(`出款费率设置成功 - 群组: ${msg.chat.title}, 费率: ${feeRate}%, 时间: ${new Date().toLocaleString()}`);
            } catch (error) {
                console.error('设置出款费率失败:', error);
                await sendMessage(msg.chat.id, '设置出款费率失败，请稍后重试');
            }
        } else {
            await sendMessage(msg.chat.id, '出款费率设置失败，请输入0-100之间的数字');
        }
    }

    // 4. 处理设置汇率命令
    else if (text.startsWith('设置汇率')) {
        const exchangeRate = parseFloat(text.replace('设置汇率', '').trim());
        
        if (!isNaN(exchangeRate) && exchangeRate > 0) {
            try {
                // 更新群组的入款和出款汇率
                await pool.query(`
                    UPDATE groups 
                    SET in_exchange_rate = ?,
                        out_exchange_rate = ?,
                        updated_at = CURRENT_TIMESTAMP
                    WHERE group_id = ?
                `, [exchangeRate, exchangeRate, msg.chat.id.toString()]);

                await sendMessage(msg.chat.id, `汇率${exchangeRate}已设置成功`);
                console.log(`汇率设置成功 - 群组: ${msg.chat.title}, 汇率: ${exchangeRate}, 时间: ${new Date().toLocaleString()}`);
            } catch (error) {
                console.error('设置汇率失败:', error);
                await sendMessage(msg.chat.id, '设置汇率失败，请稍后重试');
            }
        } else {
            await sendMessage(msg.chat.id, '汇率设置失败，请输入大于0的数字');
        }
    }

    // 4.1 处理设置入款汇率命令
    else if (text.startsWith('设置入款汇率')) {
        const exchangeRate = parseFloat(text.replace('设置入款汇率', '').trim());
        
        if (!isNaN(exchangeRate) && exchangeRate > 0) {
            try {
                // 只更新群组的入款汇率
                await pool.query(`
                    UPDATE groups 
                    SET in_exchange_rate = ?,
                        updated_at = CURRENT_TIMESTAMP
                    WHERE group_id = ?
                `, [exchangeRate, msg.chat.id.toString()]);

                await sendMessage(msg.chat.id, `入款汇率${exchangeRate}已设置成功`);
                console.log(`入款汇率设置成功 - 群组: ${msg.chat.title}, 汇率: ${exchangeRate}, 时间: ${new Date().toLocaleString()}`);
            } catch (error) {
                console.error('设置入款汇率失败:', error);
                await sendMessage(msg.chat.id, '设置入款汇率失败，请稍后重试');
            }
        } else {
            await sendMessage(msg.chat.id, '入款汇率设置失败，请输入大于0的数字');
        }
    }

    // 4.2 处理设置出款汇率命令
    else if (text.startsWith('设置出款汇率')) {
        const exchangeRate = parseFloat(text.replace('设置出款汇率', '').trim());
        
        if (!isNaN(exchangeRate) && exchangeRate > 0) {
            try {
                // 只更新群组的出款汇率
                await pool.query(`
                    UPDATE groups 
                    SET out_exchange_rate = ?,
                        updated_at = CURRENT_TIMESTAMP
                    WHERE group_id = ?
                `, [exchangeRate, msg.chat.id.toString()]);

                await sendMessage(msg.chat.id, `出款汇率${exchangeRate}已设置成功`);
                console.log(`出款汇率设置成功 - 群组: ${msg.chat.title}, 汇率: ${exchangeRate}, 时间: ${new Date().toLocaleString()}`);
            } catch (error) {
                console.error('设置出款汇率失败:', error);
                await sendMessage(msg.chat.id, '设置出款汇率失败，请稍后重试');
            }
        } else {
            await sendMessage(msg.chat.id, '出款汇率设置失败，请输入大于0的数字');
        }
    }

    // 5. 处理设置操作人命令
    else if (text.startsWith('设置操作人')) {
        try {
            const groupId = msg.chat.id.toString();
            // const mentionedUser = msg.entities?.find(e => e.type === 'mention');
            
            if (!msg.reply_to_message.from.id) {
                // 如果没有@用户，回复原消息并提示设置用户名
                await bot.sendMessage(msg.chat.id, '请通过回复要添加操作人的消息设置', {
                    reply_to_message_id: msg.message_id
                });
                return;
            }

            // 获取被@的用户名
            const username = msg.reply_to_message.from.first_name;//text.slice(mentionedUser.offset + 1, mentionedUser.offset + mentionedUser.length);
            
            // 获取群组信息
            const [group] = await pool.query(
                'SELECT creator_id, COALESCE(operators, "[]") as operators FROM groups WHERE group_id = ?',
                [groupId]
            );

            if (!group || !group[0]) {
                await sendMessage(msg.chat.id, '群组信息不存在');
                return;
            }

            const groupInfo = group[0];
            const userId = msg.from.id.toString();
            
            // 检查操作权限（群组创建者或现有操作人）
            const isCreator = groupInfo.creator_id === userId;
            let operators = [];
            
            try {
                if (groupInfo.operators) {
                    operators = JSON.parse(groupInfo.operators);
                    if (!Array.isArray(operators)) {
                        operators = [];
                    }
                }
            } catch (e) {
                console.error('解析操作人列表失败:', e);
                operators = [];
            }
            
            const isOperator = operators.some(op => op.operator_id === userId);

            if (!isCreator && !isOperator) {
                await sendMessage(msg.chat.id, '您没有权限设置操作人');
                return;
            }

            // 获取被@用户的ID
            const [user] = await pool.query(
                'SELECT id FROM users WHERE username = ?',
                [username]
            );

            let newOperatorId=msg.reply_to_message.from.id;
            let newOperatorUid=msg.reply_to_message.from.username; 
            if (!user || !user[0]) {
                // 如果用户不存在，创建新用户
                try {
                    const [result] = await pool.query(
                        'INSERT INTO users (id,uid,username, password, role) VALUES (?,?,?, ?, ?)',
                        [newOperatorId,newOperatorUid,username, '', 'user']
                    );
                    // newOperatorId = result.insertId.toString();
                    console.log(`创建新用户成功 - 用户名: ${username}, ID: ${newOperatorId}`);
                } catch (error) {
                    console.error('创建新用户失败:', error);
                    await sendMessage(msg.chat.id, '创建用户失败，请稍后重试');
                    return;
                }
            } else {
                // newOperatorId = user[0].id.toString();
            }

            // 检查是否已经是操作人
            if (operators.some(op => op.operator_id === newOperatorId)) {
                await sendMessage(msg.chat.id, '该用户已经是操作人');
                return;
            }

            // 添加新操作人
            operators.push({
                operator_id: newOperatorId,
                operator_username: username,
                operator_uid:newOperatorUid,
                added_by: userId,
                added_at: new Date().toISOString()
            });

            // 更新群组
            await pool.query(
                'UPDATE groups SET operators = ? WHERE group_id = ?',
                [JSON.stringify(operators), groupId]
            );

            await sendMessage(msg.chat.id, `已成功设置 @${username} 为操作人`);
            console.log(`设置操作人成功 - 群组: ${msg.chat.title}, 新操作人: ${username}, 设置者: ${msg.from.username || msg.from.first_name}, 时间: ${new Date().toLocaleString()}`);
        } catch (error) {
            console.error('设置操作人失败:', error);
            if (error.code === 'ER_BAD_FIELD_ERROR') {
                await sendMessage(msg.chat.id, '系统正在升级，请稍后再试');
            } else {
                await sendMessage(msg.chat.id, '设置操作人失败，请稍后重试');
            }
        }
    }

    // 6. 处理TRX地址
    else if (/^T[A-Za-z0-9]{33}$/.test(text)) {
        try {
            const groupId = msg.chat.id.toString();
            
            // 检查地址是否已存在
            const [existingAddress] = await pool.query(
                'SELECT * FROM trx_addresses WHERE address = ? AND group_id = ?',
                [text, groupId]
            );

            if (existingAddress && existingAddress.length > 0) {
                // 更新使用次数和最后出现时间
                await pool.query(`
                    UPDATE trx_addresses 
                    SET usage_count = usage_count + 1,
                        last_seen_time = CURRENT_TIMESTAMP
                    WHERE address = ? AND group_id = ?
                `, [text, groupId]);

                const newCount = existingAddress[0].usage_count + 1;
                await sendMessage(msg.chat.id, `此地址累计发送第${newCount}次`);
                console.log(`TRX地址使用次数更新 - 群组: ${msg.chat.title}, 地址: ${text}, 次数: ${newCount}, 时间: ${new Date().toLocaleString()}`);
            } else {
                // 插入新地址记录
                await pool.query(`
                    INSERT INTO trx_addresses (address, group_id)
                    VALUES (?, ?)
                `, [text, groupId]);

                await sendMessage(msg.chat.id, '此地址累计发送第1次');
                console.log(`新TRX地址记录 - 群组: ${msg.chat.title}, 地址: ${text}, 时间: ${new Date().toLocaleString()}`);
            }
        } catch (error) {
            console.error('处理TRX地址失败:', error);
            await sendMessage(msg.chat.id, '处理地址失败，请稍后重试');
        }
    }
});

// - 2.查看账单
// 生成账单消息
async function generateBillMessage(chatId) {
    try {
        // 获取群组的最后加入时间和费率信息
        const [groupInfo] = await pool.query(
            'SELECT last_join_time, in_fee_rate, in_exchange_rate, out_fee_rate, out_exchange_rate FROM groups WHERE group_id = ?',
            [chatId.toString()]
        );

        if (!groupInfo || groupInfo.length === 0) {
            return '暂无交易记录';
        }

        const lastJoinTime = groupInfo[0].last_join_time;
        const inFeeRate = parseFloat(groupInfo[0].in_fee_rate) || 0;
        const inExchangeRate = parseFloat(groupInfo[0].in_exchange_rate) || 0;
        const outFeeRate = parseFloat(groupInfo[0].out_fee_rate) || 0;
        const outExchangeRate = parseFloat(groupInfo[0].out_exchange_rate) || 0;

        // 获取机器人加入后的交易记录
        const [records] = await pool.query(`
            SELECT t.*, 
                   COALESCE(t.fee_rate, g.in_fee_rate) as fee_rate,
                   COALESCE(t.exchange_rate, g.in_exchange_rate) as exchange_rate,
                   t.total_deposit,
                   t.total_withdrawal,
                   t.deposit_fee,
                   t.withdrawal_fee,
                   t.total_u_deposit,
                   t.total_u_withdrawal
            FROM transactions t
            LEFT JOIN groups g ON t.group_id = g.group_id
            WHERE t.group_id = ? 
            AND DATE(t.time) = CURDATE()
            ORDER BY t.time DESC
        `, [chatId.toString()]);

        if (!records || records.length === 0) {
            return '暂无交易记录';
        }

        // 将记录按类型分类：入款和下发
        const deposits = records.filter(r => r.type === 'deposit');
        const withdrawals = records.filter(r => r.type === 'withdrawal');

        // 获取最新一条记录的总金额数据
        const latestRecord = records[0];
        const totalDeposit = parseFloat(latestRecord.total_deposit) || 0;
        const totalWithdrawal = parseFloat(latestRecord.total_withdrawal) || 0;
        const depositFee = parseFloat(latestRecord.deposit_fee) || 0;
        const withdrawalFee = parseFloat(latestRecord.withdrawal_fee) || 0;
        const totalUDeposit = parseFloat(latestRecord.total_u_deposit) || 0;
        const totalUWithdrawal = parseFloat(latestRecord.total_u_withdrawal) || 0;

        // 计算剩余金额：总入款 - 入款手续费 - 总下发 - 下发手续费
        const remaining = totalDeposit - depositFee - totalWithdrawal - withdrawalFee;
        // 将剩余金额转换为U币：剩余金额 ÷ 入款汇率，保留2位小数
        const remainingU = (remaining / inExchangeRate).toFixed(2);

        // 获取当前日期
        const today = new Date();
        const version = 'v29';
        const dateStr = today.toISOString().split('T')[0].replace(/-/g, '-');

        let message = `当前版本：<b>${dateStr}:${version}</b>\n\n`;

        // 添加入款记录
        if (deposits.length > 0) {
            message += `<b>入款笔数</b>：<code>${deposits.length}</code>\n`;
            deposits.forEach(deposit => {
                message += `<code>${moment(deposit.time).format('HH:mm:ss')} ${parseFloat(deposit.amount).toFixed(2)}</code>\n`;
            });
            message += '\n';
        } else {
            message += `<b>入款笔数</b>：<code>0</code>\n\n`;
        }

        // 添加出款记录
        if (withdrawals.length > 0) {
            message += `<b>出款笔数</b>：<code>${withdrawals.length}</code>\n`;
            withdrawals.forEach(withdrawal => {
                message += `<code>${moment(withdrawal.time).format('HH:mm:ss')} ${parseFloat(withdrawal.amount).toFixed(2)}</code>\n`;
            });
            message += '\n';
        } else {
            message += `<b>出款笔数</b>：<code>0</code>\n\n`;
        }

        // 添加费率信息
        message += `<b>入款费率</b>：<code>${inFeeRate}%</code>\n`;
        message += `<b>入款汇率</b>：<code>${inExchangeRate}</code>\n`;
        message += `<b>入款总额</b>：<code>${totalDeposit.toFixed(2)}</code>\n`;
        message += `<b>入款合计</b>：<code>${(totalDeposit - depositFee).toFixed(2)}|${totalUDeposit.toFixed(2)}U</code>\n\n`;

        message += `<b>出款费率</b>：<code>${outFeeRate}%</code>\n`;
        message += `<b>出款汇率</b>：<code>${outExchangeRate}</code>\n`;
        message += `<b>出款总额</b>：<code>${totalWithdrawal.toFixed(2)}</code>\n`;
        message += `<b>出款合计</b>：<code>${(totalWithdrawal - withdrawalFee).toFixed(2)}|${totalUWithdrawal.toFixed(2)}U</code>\n\n`;

        // 添加余额信息
        message += `<b>应下发</b>：<code>${remainingU}U</code>\n`;
        message += `<b>已下发</b>：<code>${totalUWithdrawal.toFixed(2)}U</code>\n`;
        message += `<b>未下发</b>：<code>${(remainingU - totalUWithdrawal).toFixed(2)}U</code>`;

        return message;
    } catch (error) {
        console.error(formatLog('生成账单消息失败', error));
        return '获取账单信息失败，请稍后重试';
    }
}
// 生成内联键盘
function generateInlineKeyboard(chatId) {
    const keyboard = {
        inline_keyboard: [
            [{
                text: '点击跳转完整账单',
                callback_data: `bill_page_${chatId}`
            }],
            [{
                text: '24小时商务对接',
                callback_data: 'business_contact'
            }]
        ]
    };
    return keyboard;
}
// 处理内联按钮回调
bot.on('callback_query', async (callbackQuery) => {
    const chatId = callbackQuery.message.chat.id;
    const data = callbackQuery.data;
    try {
        if (data.startsWith('bill_page_')) {
            const groupId = data.split('_')[2];
            console.log('https://jfpay.top/admin/views/statistics_bill.html?groupId='+groupId)
            await bot.sendMessage(chatId, `点击查看完整账单：[完整账单](https://jfpay.top/admin/views/statistics_bill.html?groupId=${groupId})`, {parse_mode: 'Markdown'});
        } else if (data === 'business_contact') {
            await bot.sendMessage(chatId, `24小时商务对接：[点击跳转](https://t.me/yyyyaaaa123_bot)`, {parse_mode: 'Markdown'})
        }
    } catch (error) {
        console.log(error)
        console.error(formatLog('处理内联按钮回调失败', error));
        await bot.answerCallbackQuery(callbackQuery.id, {
            text: '操作失败，请稍后重试',
            show_alert: true
        });
    }
});
bot.onText(/\/bill/, async (msg) => {
    const billMessage = await generateBillMessage(msg.chat.id);
    sendMessage(msg.chat.id, billMessage, {
        reply_markup: generateInlineKeyboard(msg.chat.id)
    });
});

// - 3.帮助命令
bot.onText(/\/help/, (msg) => {
    const helpMessage = `
🤖 <b>机器人使用指南</b>

<b>📝 快捷命令</b>
• <code>+数字</code> 快速记录入款
• <code>-数字</code> 快速记录入款修正
• <code>下发数字</code> 快速记录下发
• <code>下发-数字</code> 快速记录下发修正

<b>⚙️ 设置命令</b>
• <code>设置费率数字</code> 同时设置入款和出款费率
• <code>设置入款费率数字</code> 设置入款费率
• <code>设置出款费率数字</code> 设置出款费率
• <code>设置汇率数字</code> 同时设置入款和出款汇率
• <code>设置入款汇率数字</code> 设置入款汇率
• <code>设置出款汇率数字</code> 设置出款汇率
• <code>设置操作人@用户名</code> 设置群组操作人

<b>📊 查询命令</b>
• <code>/bill</code> 查看当前账单
• <code>/help</code> 显示此帮助信息

<b>💡 使用提示</b>
• 所有金额输入请使用数字
• 账单信息实时更新
• 如需帮助请联系管理员
    `;
    sendMessage(msg.chat.id, helpMessage);
});

// - 4.群组信息更新
// 机器人被添加到群组
bot.on('new_chat_members', async (msg) => {
    // 获取机器人ID
    const botInfo = await bot.getMe();
    // console.log('机器人信息:', botInfo);

    // 检查是否机器人被添加
    const botMember = msg.new_chat_members.find(member => member.id === botInfo.id);
    if (!botMember) {
        console.log('机器人未被添加，忽略事件');
        return;
    }

    try {
        const chatId = msg.chat.id.toString();
        const chatTitle = msg.chat.title || '未命名群组';
        const creatorId = msg.from.id.toString();

        // 检查群组是否已存在
        const [existingGroup] = await pool.query(
            'SELECT * FROM groups WHERE group_id = ?',
            [chatId]
        );

        if (!existingGroup || existingGroup.length === 0) {
            // 创建新群组记录
            await pool.query(`
                INSERT INTO groups (
                    group_id,
                    group_name,
                    creator_id,
                    in_fee_rate,
                    out_fee_rate,
                    in_exchange_rate,
                    out_exchange_rate,
                    last_join_time,
                    created_at,
                    updated_at,
                    operators
                ) VALUES (?, ?, ?, 0, 0, 1, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '[]')
            `, [chatId, chatTitle, creatorId]);

            // 将群组添加到允许列表
            if (!data.allowedGroups.includes(chatId)) {
                data.allowedGroups.push(chatId);
                saveData();
            }

            // 发送欢迎消息
            await sendMessage(msg.chat.id, `感谢您添加我进入群组！\n\n我已初始化群组账单系统，您可以使用以下命令开始使用：\n\n• <code>/help</code> 查看使用指南\n• <code>/bill</code> 查看当前账单`);
            
            console.log(`新群组初始化成功 - 群组: ${chatTitle}, ID: ${chatId}, 创建者: ${creatorId}, 时间: ${new Date().toLocaleString()}`);
        } else {
            // 更新群组信息
            await pool.query(`
                UPDATE groups 
                SET group_name = ?,
                    last_join_time = CURRENT_TIMESTAMP,
                    updated_at = CURRENT_TIMESTAMP
                WHERE group_id = ?
            `, [chatTitle, chatId]);

            // 确保群组在允许列表中
            if (!data.allowedGroups.includes(chatId)) {
                data.allowedGroups.push(chatId);
                saveData();
            }

            await sendMessage(msg.chat.id, `我已重新加入群组！\n\n您可以使用 <code>/help</code> 查看使用指南`);
            console.log(`群组信息更新成功 - 群组: ${chatTitle}, ID: ${chatId}, 时间: ${new Date().toLocaleString()}`);
        }
    } catch (error) {
        console.error('处理新群组失败:', error);
        await sendMessage(msg.chat.id, '初始化群组失败，请稍后重试');
    }
});

// 保存数据
function saveData() {
    try {
        fs.writeFileSync(process.env.DB_FILE, JSON.stringify(data, null, 2));
    } catch (error) {
        console.error(formatLog('Error saving data', error));
    }
}
// 加载数据
function loadData() {
    try {
        if (fs.existsSync(process.env.DB_FILE)) {
            const savedData = JSON.parse(fs.readFileSync(process.env.DB_FILE));
            data = {
                ...data,
                ...savedData
            };
        }
    } catch (error) {
        console.error(formatLog('Error loading data', error));
    }
}
// 测试数据库连接并初始化
testConnection().then(() => {
    // return initDatabase();
}).then(() => {
    // 加载数据
    loadData();

    // 启动服务器
    const PORT = process.env.PORT || 3000;
    app.listen(PORT, () => {
        console.log(formatLog({
            PORT: PORT
        }));
        console.log('机器人已准备就绪！');
    });
}).catch(error => {
    console.error(formatLog('启动失败', error));
    process.exit(1);
});

// 导入公共路由
const publicRoutes = require('./routes/public');

// 注册公共路由
app.use('/api/public', publicRoutes);

// 错误处理中间件
app.use((err, req, res, next) => {
    console.error(err.stack);
    res.status(500).json({ message: '服务器错误' });
});

// 404 处理
app.use((req, res) => {
    res.status(404).json({ message: '未找到请求的资源' });
});

module.exports = app;