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('-', ''));
}

// 检查是否是管理员
function isAdmin(userId) {
    return process.env.ADMIN_IDS.split(',').includes(userId.toString());
}

// 检查用户是否有权限操作机器人
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;
        
        console.log(`权限检查 - 用户ID: ${userIdStr}, 创建者ID: ${groupInfo.creator_id}, 是否创建者: ${isCreator}`);
        
        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 => {
            const isOp = op.operator_id === userIdStr;
            console.log(`检查操作人 - 操作人ID: ${op.operator_id}, 用户ID: ${userIdStr}, 是否匹配: ${isOp}`);
            return isOp;
        });
        
        console.log(`权限检查结果 - 用户ID: ${userIdStr}, 是否创建者: ${isCreator}, 是否操作人: ${isOperator}`);
        
        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;
    }
}

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

    const text = msg.text?.trim();
    if (!text) return;

    // 检查用户权限
    const hasPermission = await checkUserPermission(msg.chat.id, msg.from.id);
    console.log(`用户权限检查 - 用户ID: ${msg.from.id}, 群组ID: ${msg.chat.id}, 是否有权限: ${hasPermission}`);
    
    if (!hasPermission) {
        // 如果不是创建人或操作人，直接返回
        console.log(`用户无权限操作 - 用户ID: ${msg.from.id}, 群组ID: ${msg.chat.id}`);
        return;
    }

    // 处理入款命令
    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, '记录入款失败，请稍后重试');
            }
        }
    } 
    // 处理入款修正命令
    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, '记录入款修正失败，请稍后重试');
            }
        }
    }
    // 处理回款命令
    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, '记录回款失败，请稍后重试');
            }
        }
    }
    // 处理回款修正命令
    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, '记录回款修正失败，请稍后重试');
            }
        }
    }
    // 处理设置费率命令
    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之间的数字');
        }
    }
    // 处理设置汇率命令
    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的数字');
        }
    }
    // 处理设置操作人命令
    else if (text.startsWith('设置操作人')) {
        try {
            const groupId = msg.chat.id.toString();
            const mentionedUser = msg.entities?.find(e => e.type === 'mention');
            
            if (!mentionedUser) {
                // 如果没有@用户，回复原消息并提示设置用户名
                await bot.sendMessage(msg.chat.id, '请设置您的Telegram用户名后再试', {
                    reply_to_message_id: msg.message_id
                });
                return;
            }

            // 获取被@的用户名
            const username = 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;
            if (!user || !user[0]) {
                // 如果用户不存在，创建新用户
                try {
                    const [result] = await pool.query(
                        'INSERT INTO users (username, password, role) VALUES (?, ?, ?)',
                        [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,
                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, '设置操作人失败，请稍后重试');
            }
        }
    }
    // 处理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, '处理地址失败，请稍后重试');
        }
    }
});

// 处理机器人被移出群组
bot.on('left_chat_member', async (msg) => {
    if (msg.left_chat_member.id === (await bot.getMe()).id) {
        const chatId = msg.chat.id.toString();
        try {
            // 更新数据库中的群组状态
            await pool.query(`
                UPDATE groups 
                SET is_active = false,
                    last_leave_time = CURRENT_TIMESTAMP
                WHERE group_id = ?
            `, [chatId]);

            // 从内存中的允许列表中移除
            const index = data.allowedGroups.indexOf(chatId);
            if (index > -1) {
                data.allowedGroups.splice(index, 1);
                saveData();
            }

            // console.log(formatLog({
            //     ID: chatId,
            //     名称: msg.chat.title || '未命名群组',
            //     类型: msg.chat.type,
            //     状态: '已移除',
            //     移除时间: new Date().toLocaleString(),
            //     操作者: msg.from.username || msg.from.first_name + ' (' + msg.from.id + ')'
            // }));
        } catch (error) {
            console.error(formatLog('处理机器人被移出群组失败', error));
        }
    }
});

// 处理管理员命令
bot.onText(/\/addgroup (.+)/, async (msg, match) => {
    if (!isAdmin(msg.from.id)) {
        sendMessage(msg.chat.id, '您没有权限执行此命令。');
        return;
    }

    const groupId = match[1].trim();
    if (!data.allowedGroups.includes(groupId)) {
        try {
            // 使用 createGroup 创建新群组
            const groupData = {
                groupId: groupId,
                groupName: '手动添加的群组',
                groupType: 'public',
                creatorId: msg.from.id.toString()
            };

            const result = await createGroup({
                body: groupData
            });
            if (result) {
                data.allowedGroups.push(groupId);
                saveData();
                console.log(formatLog({
                    ID: groupId,
                    名称: groupData.groupName,
                    状态: '已启用',
                    添加时间: new Date().toLocaleString(),
                    操作者: msg.from.username || msg.from.first_name + ' (' + msg.from.id + ')'
                }));
                sendMessage(msg.chat.id, `群组 ${groupId} 已添加到允许列表。`);
            } else {
                sendMessage(msg.chat.id, '添加群组失败，请检查群组ID是否正确。');
            }
        } catch (error) {
            console.error(formatLog('创建群组失败', error));
            sendMessage(msg.chat.id, '添加群组失败，请稍后重试。');
        }
    } else {
        sendMessage(msg.chat.id, '该群组已在允许列表中。');
    }
});

// 处理查看账单命令
bot.onText(/\/bill/, async (msg) => {
    const billMessage = await generateBillMessage(msg.chat.id);
    sendMessage(msg.chat.id, billMessage, {
        reply_markup: generateInlineKeyboard(msg.chat.id)
    });
});

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

📝 基础命令
• /deposit 数字 - 记录入款
• /withdraw 数字 - 记录下发
• /bill - 查看当前账单
• /help - 显示此帮助信息

⚡️ 快捷命令
• +数字 - 快速记录入款（例如：+2000）
• -数字 - 快速记录下发（例如：-2000）

👨‍💼 管理员命令
• /addgroup 群组ID - 添加允许的群组
• /removegroup 群组ID - 移除允许的群组
• /listgroups - 列出所有允许的群组

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

// 生成账单消息
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
            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 totalDeposit = deposits.reduce((sum, d) => sum + parseFloat(d.amount), 0);
        const totalWithdrawal = withdrawals.reduce((sum, w) => sum + parseFloat(w.amount), 0);
        const depositFee = totalDeposit * (inFeeRate / 100);
        const withdrawalFee = totalWithdrawal * (outFeeRate / 100);
        const remaining = totalDeposit - depositFee - totalWithdrawal - withdrawalFee;
        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)}|${((totalDeposit - depositFee) / inExchangeRate).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)}|${((totalWithdrawal - withdrawalFee) / outExchangeRate).toFixed(2)}U</code>\n\n`;

        // 添加余额信息
        message += `<b>应下发</b>：<code>${remainingU}U</code>\n`;
        message += `<b>已下发</b>：<code>${(totalWithdrawal / outExchangeRate).toFixed(2)}U</code>\n`;
        message += `<b>未下发</b>：<code>${(remainingU - (totalWithdrawal / outExchangeRate)).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];
            await bot.answerCallbackQuery(callbackQuery.id, {
                url: 'https://google.com'
            });
        } else if (data === 'business_contact') {
            await bot.answerCallbackQuery(callbackQuery.id, {
                url: 'https://t.me/your_business_account'
            });
        }
    } catch (error) {
        console.error(formatLog('处理内联按钮回调失败', error));
        await bot.answerCallbackQuery(callbackQuery.id, {
            text: '操作失败，请稍后重试',
            show_alert: true
        });
    }
});

// 保存数据
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);
});

// 处理机器人被添加到群组
async function handleBotAdded(msg, chatId, chatType, chatIdStr, existingGroup) {
    try {
        // 获取群组链接
        const chatInfo = await bot.getChat(chatId);
        const groupInfo = {
            ID: chatId,
            名称: msg.chat.title || '未命名群组',
            类型: chatType,
            状态: existingGroup ? '重新激活' : '已激活',
            群组链接: chatInfo.invite_link || '未设置',
            更新时间: new Date().toLocaleString()
        };

        console.log(formatLog({
            操作: '处理机器人添加',
            群组信息: groupInfo
        }));

        // 如果群组不存在，创建新群组
        if (!existingGroup) {
            const groupData = {
                groupId: chatIdStr,
                groupName: msg.chat.title || '未命名群组',
                groupType: chatType === 'private' ? 'personal' : chatType,
                creatorId: msg.from.id.toString()
            };

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

                if (existingGroupCheck && existingGroupCheck.length > 0) {
                    console.log(formatLog({
                        操作: '群组已存在，更新状态',
                        群组ID: chatIdStr
                    }));
                    
                    // 更新现有群组状态
                    await pool.query(`
                        UPDATE groups 
                        SET is_active = true,
                            group_name = ?,
                            group_type = ?,
                            last_join_time = CURRENT_TIMESTAMP
                        WHERE group_id = ?
                    `, [
                        groupData.groupName,
                        groupData.groupType,
                        chatIdStr
                    ]);
                } else {
                    // 创建新群组
                    await pool.query(`
                        INSERT INTO groups 
                        (group_id, group_name, group_type, creator_id, is_active, last_join_time, 
                         in_fee_rate, in_exchange_rate, out_fee_rate, out_exchange_rate, operators) 
                        VALUES (?, ?, ?, ?, true, CURRENT_TIMESTAMP, 0.00, 1.0000, 0.00, 1.0000, '[]')
                    `, [
                        groupData.groupId,
                        groupData.groupName,
                        groupData.groupType,
                        groupData.creatorId
                    ]);
                }

                // 更新内存中的群组列表
                if (!data.allowedGroups.includes(chatIdStr)) {
                    data.allowedGroups.push(chatIdStr);
                    saveData();
                }

                // 发送欢迎消息
                await bot.sendMessage(chatId, '感谢添加我为群组成员！使用 /help 查看可用命令。', {
                    parse_mode: 'HTML'
                });

                // 发送账单消息
                const billMessage = await generateBillMessage(chatId);
                if (billMessage) {
                    await bot.sendMessage(chatId, billMessage, {
                        parse_mode: 'HTML',
                        reply_markup: generateInlineKeyboard(chatId)
                    });
                }

                console.log(formatLog({
                    操作: '群组添加成功',
                    群组ID: chatIdStr,
                    群组名称: groupData.groupName
                }));

            } catch (error) {
                console.error(formatLog({
                    错误: '创建群组失败',
                    详情: error.message,
                    群组ID: groupData.groupId
                }));
                
                // 检查是否是重复添加导致的错误
                if (error.code === 'ER_DUP_ENTRY') {
                    console.log(formatLog({
                        操作: '检测到重复添加',
                        群组ID: chatIdStr
                    }));
                    return;
                }
                
                await bot.sendMessage(chatId, '添加群组失败，请联系管理员。', {
                    parse_mode: 'HTML'
                });
            }
        }
    } catch (error) {
        console.error(formatLog({
            错误: '处理群组添加失败',
            详情: error.message
        }));
        await sendMessage(chatId, '添加群组失败，请联系管理员。');
    }
}

// 处理群组信息更新
async function handleGroupUpdate(msg, chatId, chatType, chatIdStr, existingGroup, newStatus, newType) {
    const connection = await pool.getConnection();
    await connection.beginTransaction();

    try {
        // 更新群组ID和类型
        const oldGroupId = existingGroup.group_id;

        // 如果群组ID发生变化，更新所有相关记录
        if (existingGroup.group_type != newType) {
            console.log(formatLog({
                操作: '群组类型更新',
                名称: msg.chat.title || existingGroup.group_name,
                旧ID: oldGroupId,
                新ID: chatIdStr,
                旧类型: existingGroup.group_type,
                新类型: newType,
                状态: newStatus !== 'kicked' && newStatus !== 'left' ? '活跃' : '已移除'
            }));

            // 开始事务
            await connection.beginTransaction();

            try {
                // 检查目标ID是否已存在
                const [existingTargetGroup] = await connection.query(
                    'SELECT * FROM groups WHERE group_id = ?',
                    [chatIdStr]
                );

                if (existingTargetGroup && existingTargetGroup.length > 0) {
                    // 更新交易记录
                    await connection.query(`
                        UPDATE transactions 
                        SET group_id = ?
                        WHERE group_id = ?
                    `, [chatIdStr, oldGroupId]);

                    // 更新资金记录
                    await connection.query(`
                        UPDATE money_records 
                        SET group_id = ?
                        WHERE group_id = ?
                    `, [chatIdStr, oldGroupId]);

                    // 删除旧群组记录
                    await connection.query(
                        'DELETE FROM groups WHERE group_id = ?',
                        [oldGroupId]
                    );

                    // 更新目标群组信息
                    await connection.query(`
                        UPDATE groups 
                        SET group_type = ?,
                            group_name = ?,
                            is_active = ?,
                            updated_at = CURRENT_TIMESTAMP
                        WHERE group_id = ?
                    `, [
                        newType,
                        msg.chat.title || existingGroup.group_name,
                        newStatus !== 'kicked' && newStatus !== 'left',
                        chatIdStr
                    ]);
                } else {
                    // 如果目标ID不存在，执行正常的更新操作
                    // 先删除旧记录
                    await connection.query(
                        'DELETE FROM groups WHERE group_id = ?',
                        [oldGroupId]
                    );

                    // 插入新记录
                    await connection.query(`
                        INSERT INTO groups 
                        (group_id, group_name, group_type, creator_id, is_active, last_join_time) 
                        VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
                    `, [
                        chatIdStr,
                        msg.chat.title || existingGroup.group_name,
                        newType,
                        existingGroup.creator_id,
                        newStatus !== 'kicked' && newStatus !== 'left'
                    ]);

                    // 更新交易记录
                    await connection.query(`
                        UPDATE transactions 
                        SET group_id = ?
                        WHERE group_id = ?
                    `, [chatIdStr, oldGroupId]);

                    // 更新资金记录
                    await connection.query(`
                        UPDATE money_records 
                        SET group_id = ?
                        WHERE group_id = ?
                    `, [chatIdStr, oldGroupId]);
                }

                // 提交事务
                await connection.commit();

                // 更新内存中的群组列表
                const index = data.allowedGroups.indexOf(oldGroupId);
                if (index > -1) {
                    if (newStatus === 'kicked' || newStatus === 'left') {
                        data.allowedGroups.splice(index, 1);
                    } else {
                        data.allowedGroups[index] = chatIdStr;
                    }
                    saveData();
                }
            } catch (error) {
                // 回滚事务
                await connection.rollback();
                console.error(formatLog('更新群组信息失败', error));
                throw error;
            }
        } else {
            // 如果ID没有变化，只更新其他信息
            await connection.query(`
                UPDATE groups 
                SET group_type = ?,
                    group_name = ?,
                    is_active = ?,
                    updated_at = CURRENT_TIMESTAMP
                WHERE group_id = ?
            `, [
                newType,
                msg.chat.title || existingGroup.group_name,
                newStatus !== 'kicked' && newStatus !== 'left',
                chatIdStr
            ]);
        }

        await connection.commit();
        console.log('【群组状态变更】');
        console.log(formatLog({
            ID: oldGroupId + ' -> ' + chatIdStr,
            type: existingGroup.group_type + ' -> ' + newType,
            name: existingGroup.group_name + ' -> ' + msg.chat.title || existingGroup.group_name,
            status: newStatus === 'kicked' || newStatus === 'left' ? '已移除' : '活跃'
        }));
    } catch (error) {
        await connection.rollback();
        console.error(formatLog({
            错误: '更新群组信息失败',
            详情: error.message
        }));
        throw error;
    } finally {
        connection.release();
    }
}

// 处理群组状态变更
bot.on('my_chat_member', async (msg) => {
    try {
        const chatId = msg.chat.id;
        const newStatus = msg.new_chat_member.status;
        const oldStatus = msg.old_chat_member.status;
        const chatType = msg.chat.type;
        const chatIdStr = chatId.toString();

        console.log(formatLog({
            操作: '收到群组状态变更',
            群组ID: chatIdStr,
            群组名称: msg.chat.title,
            旧状态: oldStatus,
            新状态: newStatus,
            群组类型: chatType
        }));

        // 定义群组类型
        const newType = chatType === 'private' ? 'personal' : 
                       chatType === 'supergroup' ? 'supergroup' : 'group';

        // 查找群组，同时检查新旧ID
        const existingGroup = await Group.findByGroupId(chatIdStr) ||
            await Group.findByGroupId(chatIdStr.replace('-', ''));

        // 如果是机器人首次被添加到群组（从非成员变为成员）
        if (oldStatus === 'left' && newStatus === 'member') {
            // 如果群组已存在且处于活跃状态，则发送欢迎消息
            if (existingGroup && existingGroup.is_active) {
                console.log(formatLog({
                    操作: '机器人重新加入群组',
                    群组ID: chatIdStr,
                    群组名称: msg.chat.title || existingGroup.group_name
                }));
                
                try {
                    // 发送欢迎消息
                    await bot.sendMessage(chatId, '感谢重新添加我为群组成员！', {
                        parse_mode: 'HTML'
                    });
                    
                    // 发送账单消息
                    const billMessage = await generateBillMessage(chatId);
                    if (billMessage) {
                        await bot.sendMessage(chatId, billMessage, {
                            parse_mode: 'HTML',
                            reply_markup: generateInlineKeyboard(chatId)
                        });
                    }
                } catch (error) {
                    console.error(formatLog({
                        错误: '发送消息失败',
                        详情: error.message,
                        群组ID: chatIdStr
                    }));
                }
                return;
            }

            // 检查邀请者是否已存在于用户表中
            const [existingUser] = await pool.query(
                'SELECT * FROM users WHERE id = ?',
                [msg.from.id]
            );

            // 如果用户不存在，则创建新用户
            if (!existingUser || existingUser.length === 0) {
                // 生成唯一的用户名
                const username = msg.from.username || `user_${msg.from.id}`;
                
                await pool.query(`
                    INSERT INTO users 
                    (id, username, password, role) 
                    VALUES (?, ?, '', 'user')
                `, [msg.from.id, username]);

                console.log(formatLog({
                    操作: '新增用户',
                    ID: msg.from.id,
                    用户名: username,
                    角色: 'user',
                    时间: new Date().toLocaleString()
                }));
            }

            if (!existingGroup) {
                await handleBotAdded(msg, chatId, chatType, chatIdStr, null);
            } else if (existingGroup.group_type !== newType) {
                await handleBotAdded(msg, chatId, chatType, chatIdStr, existingGroup);
            }
        }

        if (existingGroup) {
            // 传递 newType 到 handleGroupUpdate 函数
            await handleGroupUpdate(msg, chatId, chatType, chatIdStr, existingGroup, newStatus, newType);
        } else if (newStatus === 'member') {
            // 如果是新群组且机器人被添加为成员
            await handleBotAdded(msg, chatId, chatType, chatIdStr, null);
        }
    } catch (error) {
        console.error(formatLog({
            错误: '处理群组状态变更失败',
            详情: error.message
        }));
    }
});

// 导入公共路由
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;