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 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;

    // 处理入款命令
    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的数字');
        }
    }
    // 处理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('new_chat_members', async (msg) => {
    const chatId = msg.chat.id;
    const newMembers = msg.new_chat_members;

    for (const member of newMembers) {
        if (member.id === (await bot.getMe()).id) {
            // 检查群组是否在允许列表中
            const chatIdStr = chatId.toString();
            try {
                // 先检查数据库中是否存在该群组
                const existingGroup = await Group.findByGroupId(chatIdStr);

                // 检查邀请者是否已存在于用户表中
                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 pool.query(`
                        UPDATE groups 
                        SET is_active = true,
                            group_name = ?,
                            last_join_time = CURRENT_TIMESTAMP
                        WHERE group_id = ?
                    `, [msg.chat.title || existingGroup.group_name, chatIdStr]);

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

                    // 发送欢迎消息并显示当前账单
                    await sendMessage(chatId, '感谢重新添加我为群组成员！');
                    const billMessage = await generateBillMessage(chatId);
                    if (billMessage) {
                        await sendMessage(chatId, billMessage, {
                            reply_markup: generateInlineKeyboard(chatId)
                        });
                    }
                } else {
                    // 如果是新群组，打印被添加到新群组的信息
                    console.log(formatLog({
                        ID: chatId,
                        名称: msg.chat.title || '未命名群组',
                        类型: msg.chat.type,
                        描述: msg.chat.description || '无',
                        '添加者信息': {
                            ID: msg.from.id,
                            用户名: msg.from.username || '无',
                            姓名: msg.from.first_name,
                            ...(msg.from.last_name && {
                                姓: msg.from.last_name
                            })
                        },
                        添加时间: new Date().toLocaleString()
                    }));

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

                    console.log(formatLog(groupData));

                    try {
                        // 直接使用 SQL 插入群组数据
                        const [result] = await pool.query(`
                            INSERT INTO groups 
                            (group_id, group_name, group_type, creator_id, is_active, last_join_time) 
                            VALUES (?, ?, ?, ?, true, CURRENT_TIMESTAMP)
                        `, [
                            groupData.groupId,
                            groupData.groupName,
                            groupData.groupType,
                            groupData.creatorId
                        ]);

                        console.log(formatLog(result));

                        // 更新内存中的群组列表
                        if (!data.allowedGroups.includes(chatIdStr)) {
                            data.allowedGroups.push(chatIdStr);
                            saveData();
                            console.log(formatLog({
                                ID: chatIdStr,
                                名称: groupData.groupName,
                                类型: groupData.groupType,
                                状态: '已启用',
                                添加时间: new Date().toLocaleString(),
                                操作者: msg.from.username || msg.from.first_name + ' (' + msg.from.id + ')'
                            }));
                        }

                        console.log(formatLog({
                            ID: chatIdStr,
                            名称: groupData.groupName,
                            类型: groupData.groupType,
                            状态: '已启用',
                            添加时间: new Date().toLocaleString(),
                            操作者: msg.from.username || msg.from.first_name + ' (' + msg.from.id + ')'
                        }));

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

                            // 尝试发送账单消息
                            const billMessage = await generateBillMessage(chatId);
                            if (billMessage) {
                                const billResult = await bot.sendMessage(chatId, billMessage, {
                                    parse_mode: 'HTML',
                                    reply_markup: generateInlineKeyboard(chatId)
                                });
                                console.log(formatLog(billResult));
                            }
                        } catch (messageError) {
                            console.error(formatLog(messageError));
                        }
                    } catch (error) {
                        console.error(formatLog('创建群组过程中出错', error));
                        try {
                            await bot.sendMessage(chatId, '添加群组失败，请联系管理员。', {
                                parse_mode: 'HTML'
                            });
                        } catch (messageError) {
                            console.error(formatLog('发送错误消息失败', messageError));
                        }
                    }
                }
            } catch (error) {
                console.error(formatLog('处理群组加入失败', error));
                await sendMessage(chatId, '添加群组失败，请联系管理员。');
            }
        } else {
            // 其他新成员
            console.log(formatLog({
                member: member.username || member.first_name + ' (' + member.id + ')'
            }));
            await sendMessage(chatId, `欢迎 ${member.username || member.first_name} 加入群组！`);
        }
    }
});

// 处理机器人被移出群组
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('机器人首次被添加到群组');
        // console.log(formatLog(groupInfo));

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

            const id = await Group.create({
                groupId: groupData.groupId,
                groupName: groupData.groupName,
                creatorId: groupData.creatorId
            });

            if (id) {
                // 更新内存中的群组列表
                if (!data.allowedGroups.includes(chatIdStr)) {
                    data.allowedGroups.push(chatIdStr);
                    saveData();
                }
                console.log('机器人首次被添加到群组');
                console.log(formatLog(groupInfo));
                await sendMessage(chatId, '感谢添加我为群组成员！使用 /help 查看可用命令。');
            } else {
                await sendMessage(chatId, '添加群组失败，请联系管理员。');
            }
        }
    } catch (error) {
        console.error(formatLog({
            错误: '处理机器人首次加入群组失败',
            详情: error.message
        }));
        await sendMessage(chatId, '添加群组失败，请联系管理员。');
    }
}

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

    try {
        // 更新群组ID和类型
        const newType = chatType === 'private' ? 'private' :
            chatType === 'supergroup' ? 'supergroup' : 'group';
        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();

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

        // 获取群组详细信息（如果机器人还在群组中）
        if (newStatus !== 'kicked' && newStatus !== 'left') {
            try {
                const chatInfo = await bot.getChat(chatId);
            } catch (error) {
                console.log(formatLog('获取群组详细信息失败', error.message));
            }
        } else {
            console.log('机器人已被移出群组，无法获取详细信息');
        }

        const newType = chatType === 'private' ? 'private' :
            chatType === 'supergroup' ? 'supergroup' : 'group';
            
        // 如果是机器人首次被添加到群组（从非成员变为成员）
        if (oldStatus === 'left' && newStatus === 'member' && existingGroup.group_type != newType) {
            await handleBotAdded(msg, chatId, chatType, chatIdStr, existingGroup);
        }

        if (existingGroup) {
            await handleGroupUpdate(msg, chatId, chatType, chatIdStr, existingGroup, newStatus);
        }
    } 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;