bot/admin/models/Transaction.js

const { pool } = require('../config/database');

// 创建交易表
const createTransactionTable = async () => {
    try {
        // 先检查表是否存在
        const [tables] = await pool.query('SHOW TABLES LIKE "transactions"');
        if (tables.length === 0) {
            // 如果表不存在，创建新表
            await pool.query(`
                CREATE TABLE transactions (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    group_id VARCHAR(50) NOT NULL,
                    group_name VARCHAR(100) NOT NULL,
                    type ENUM('deposit', 'withdrawal') NOT NULL COMMENT 'deposit:入款,withdrawal:出款',
                    amount DECIMAL(10,2) NOT NULL,
                    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    remark VARCHAR(255) DEFAULT NULL COMMENT '备注',
                    operator_id VARCHAR(50) NOT NULL COMMENT '操作人ID',
                    fee_rate DECIMAL(5,2) DEFAULT NULL COMMENT '费率',
                    exchange_rate DECIMAL(10,4) DEFAULT NULL COMMENT '汇率',
                    INDEX idx_group_time (group_id, time),
                    FOREIGN KEY (operator_id) REFERENCES users(id)
                )
            `);
            console.log('交易表创建成功');
        }
    } catch (error) {
        console.error('创建交易表失败:', error);
        throw error;
    }
};

// 初始化表
createTransactionTable();

const Transaction = {
    // 获取交易列表
    findAll: async (query = {}, page = 1, limit = 10) => {
        try {
            const offset = (page - 1) * limit;
            let sql = `
                SELECT 
                    t.*,
                    u.username as operator_name,
                    u.uid as uid,
                    g.group_name
                FROM transactions t
                LEFT JOIN users u ON t.operator_id = u.id
                LEFT JOIN groups g ON t.group_id = g.group_id
                WHERE 1=1
            `;
            const params = [];

            if (query.groupId) {
                sql += ' AND t.group_id = ?';
                params.push(query.groupId);
            }

            if (query.type) {
                sql += ' AND t.type = ?';
                params.push(query.type);
            }

            if (query.startDate) {
                sql += ' AND DATE(t.time) >= ?';
                params.push(query.startDate);
            }

            if (query.endDate) {
                sql += ' AND DATE(t.time) <= ?';
                params.push(query.endDate);
            }

            // 获取总记录数
            const [countResult] = await pool.query(
                sql.replace('t.*,', 'COUNT(*) as total,'),
                params
            );
            const total = countResult[0].total;

            // 添加排序和分页
            sql += ' ORDER BY t.time DESC LIMIT ? OFFSET ?';
            params.push(limit, offset);

            const [rows] = await pool.query(sql, params);

            return {
                transactions: rows,
                total,
                page: parseInt(page),
                pages: Math.ceil(total / limit)
            };
        } catch (error) {
            console.error('查询交易列表失败:', error);
            throw error;
        }
    },

    // 创建交易
    create: async (transactionData) => {
        try {
            // 获取群组的默认费率和汇率
            const [groupInfo] = await pool.query(
                'SELECT in_fee_rate, in_exchange_rate, out_fee_rate, out_exchange_rate FROM groups WHERE group_id = ?',
                [transactionData.groupId]
            );

            if (!groupInfo || groupInfo.length === 0) {
                throw new Error('群组不存在');
            }

            // 根据交易类型选择对应的费率和汇率
            const defaultFeeRate = transactionData.type === 'deposit' ? 
                groupInfo[0].in_fee_rate : groupInfo[0].out_fee_rate;
            const defaultExchangeRate = transactionData.type === 'deposit' ? 
                groupInfo[0].in_exchange_rate : groupInfo[0].out_exchange_rate;

            // 使用指定的费率和汇率，如果没有指定则使用默认值
            const feeRate = transactionData.feeRate || defaultFeeRate;
            const exchangeRate = transactionData.exchangeRate || defaultExchangeRate;

            // 使用群内操作人的ID作为operator_id
            const operatorId = transactionData.operatorId || 1;

            // 获取上一条记录的总金额数据
            const [lastRecord] = await pool.query(
                'SELECT total_deposit, total_withdrawal, total_u_deposit, total_u_withdrawal FROM transactions WHERE group_id = ? ORDER BY time DESC LIMIT 1',
                [transactionData.groupId]
            );

            // 初始化总金额数据
            let totalDeposit = 0;
            let totalWithdrawal = 0;
            let totalUDeposit = 0;
            let totalUWithdrawal = 0;

            // 如果有上一条记录，使用其数据
            if (lastRecord && lastRecord.length > 0) {
                totalDeposit = parseFloat(lastRecord[0].total_deposit) || 0;
                totalWithdrawal = parseFloat(lastRecord[0].total_withdrawal) || 0;
                totalUDeposit = parseFloat(lastRecord[0].total_u_deposit) || 0;
                totalUWithdrawal = parseFloat(lastRecord[0].total_u_withdrawal) || 0;
            }

            // 计算本条交易的手续费
            const currentFee = Math.abs(transactionData.amount) * (feeRate / 100);
            
            // 计算本条交易的实际金额（减去手续费）
            const actualAmount = Math.abs(transactionData.amount) - currentFee;
            
            // 计算本条交易的U币金额
            const uAmount = actualAmount / exchangeRate;

            // 根据交易类型更新总金额
            if (transactionData.type === 'deposit') {
                totalDeposit += transactionData.amount;
                totalUDeposit += uAmount;
            } else {
                totalWithdrawal += transactionData.amount;
                totalUWithdrawal += uAmount;
            }

            const [result] = await pool.query(
                `INSERT INTO transactions (
                    group_id, group_name, type, amount, remark, operator_id, 
                    fee_rate, exchange_rate, total_deposit, total_withdrawal,
                    deposit_fee, withdrawal_fee, total_u_deposit, total_u_withdrawal
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
                [
                    transactionData.groupId,
                    transactionData.groupName,
                    transactionData.type,
                    transactionData.amount,
                    transactionData.remark || null,
                    operatorId,
                    feeRate,
                    exchangeRate,
                    totalDeposit,
                    totalWithdrawal,
                    transactionData.type === 'deposit' ? currentFee : 0,
                    transactionData.type === 'withdrawal' ? currentFee : 0,
                    totalUDeposit,
                    totalUWithdrawal
                ]
            );
            return result.insertId;
        } catch (error) {
            console.error('创建交易记录失败:', error);
            throw error;
        }
    },

    // 删除交易
    delete: async (id) => {
        await pool.query('DELETE FROM transactions WHERE id = ?', [id]);
    },

    // 获取仪表板数据
    getDashboardData: async () => {
        try {
            // 获取总群组数
            const [groupResult] = await pool.query('SELECT COUNT(*) as total FROM groups WHERE is_active = true');
            const totalGroups = groupResult[0].total;

            // 获取总交易数
            const [transactionResult] = await pool.query('SELECT COUNT(*) as total FROM transactions');
            const totalTransactions = transactionResult[0].total;

            // 获取总金额
            const [amountResult] = await pool.query(`
                SELECT 
                    SUM(CASE WHEN type = 'deposit' THEN amount ELSE -amount END) as total
                FROM transactions
            `);
            const totalAmount = amountResult[0].total || 0;

            // 获取今日交易数
            const [todayResult] = await pool.query(`
                SELECT COUNT(*) as total 
                FROM transactions 
                WHERE DATE(time) = CURDATE()
            `);
            const todayTransactions = todayResult[0].total;

            // 获取最近交易
            const [recentTransactions] = await pool.query(`
                SELECT 
                    t.*,
                    u.username as operator_name
                FROM transactions t
                LEFT JOIN users u ON t.operator_id = u.id
                ORDER BY t.time DESC
                LIMIT 5
            `);

            // 获取活跃群组
            const [activeGroups] = await pool.query(`
                SELECT 
                    g.*,
                    COUNT(t.id) as transaction_count,
                    SUM(CASE WHEN t.type = 'deposit' THEN t.amount ELSE 0 END) as total_deposit,
                    SUM(CASE WHEN t.type = 'withdrawal' THEN t.amount ELSE 0 END) as total_withdrawal
                FROM groups g
                LEFT JOIN transactions t ON g.group_id = t.group_id
                WHERE g.is_active = true
                GROUP BY g.id
                ORDER BY transaction_count DESC
                LIMIT 5
            `);

            return {
                totalGroups,
                totalTransactions,
                totalAmount,
                todayTransactions,
                recentTransactions,
                activeGroups
            };
        } catch (error) {
            console.error('获取仪表板数据失败:', error);
            throw error;
        }
    },

    // 入款方法
    deposit: async (transactionData) => {
        try {
            const id = await Transaction.create({
                groupId: transactionData.groupId,
                groupName: transactionData.groupName,
                type: 'deposit',
                amount: parseFloat(transactionData.amount),
                operatorId: transactionData.operatorId || 1,  // 添加默认操作者ID
                feeRate: transactionData.feeRate,
                exchangeRate: transactionData.exchangeRate
            });

            const transaction = await Transaction.findById(id);
            if (transaction) {
                return {
                    success: true,
                    transaction,
                    message: '入款记录创建成功'
                };
            } else {
                return {
                    success: false,
                    message: '入款记录创建失败'
                };
            }
        } catch (error) {
            console.error('入款记录创建失败:', error);
            return {
                success: false,
                message: '入款记录创建失败，请稍后重试'
            };
        }
    },

    // 出款方法
    withdrawal: async (transactionData) => {
        try {
            const id = await Transaction.create({
                groupId: transactionData.groupId,
                groupName: transactionData.groupName,
                type: 'withdrawal',
                amount: parseFloat(transactionData.amount),
                operatorId: transactionData.operatorId || 1,  // 添加默认操作者ID
                feeRate: transactionData.feeRate,
                exchangeRate: transactionData.exchangeRate
            });

            const transaction = await Transaction.findById(id);
            if (transaction) {
                return {
                    success: true,
                    transaction,
                    message: '出款记录创建成功'
                };
            } else {
                return {
                    success: false,
                    message: '出款记录创建失败'
                };
            }
        } catch (error) {
            console.error('出款记录创建失败:', error);
            return {
                success: false,
                message: '出款记录创建失败，请稍后重试'
            };
        }
    },

    // 查询群组账单
    getGroupTransactions: async (groupId, options = {}) => {
        const {
            startDate,
            endDate,
            type,
            page = 1,
            limit = 10
        } = options;

        let sql = `
            SELECT 
                t.*,
                u.username as operator_name,
                u.uid as uid,
                DATE_FORMAT(t.time, '%Y-%m-%d %H:%i:%s') as formatted_time
            FROM transactions t
            LEFT JOIN users u ON t.operator_id = u.id
            WHERE t.group_id = ?
        `;
        const params = [groupId];

        if (startDate) {
            sql += ' AND t.time >= ?';
            params.push(startDate);
        }
        if (endDate) {
            sql += ' AND t.time <= ?';
            params.push(endDate);
        }
        if (type) {
            sql += ' AND t.type = ?';
            params.push(type);
        }

        // 获取总数
        const countSql = `
            SELECT COUNT(*) as total 
            FROM transactions t
            WHERE 1=1
            ${startDate ? 'AND t.time >= ?' : ''}
            ${endDate ? 'AND t.time <= ?' : ''}
            ${type ? 'AND t.type = ?' : ''}
            ${groupId ? 'AND t.group_id = ?' : ''}
        `;
        
        const [countResult] = await pool.query(countSql, params);
        const total = countResult[0].total;

        // 获取分页数据
        sql += ' ORDER BY t.time DESC LIMIT ? OFFSET ?';
        params.push(limit, (page - 1) * limit);

        const [rows] = await pool.query(sql, params);

        // 计算总入款和总出款
        const [summary] = await pool.query(`
            SELECT 
                SUM(CASE WHEN type = 'deposit' THEN amount ELSE 0 END) as total_deposit,
                SUM(CASE WHEN type = 'withdrawal' THEN amount ELSE 0 END) as total_withdrawal
            FROM transactions 
            WHERE group_id = ?
            ${startDate ? 'AND time >= ?' : ''}
            ${endDate ? 'AND time <= ?' : ''}
        `, [groupId, ...(startDate ? [startDate] : []), ...(endDate ? [endDate] : [])]);

        return {
            transactions: rows,
            total,
            page: parseInt(page),
            pages: Math.ceil(total / limit),
            summary: {
                totalDeposit: summary[0].total_deposit || 0,
                totalWithdrawal: summary[0].total_withdrawal || 0,
                balance: (summary[0].total_deposit || 0) - (summary[0].total_withdrawal || 0)
            }
        };
    },

    // 根据ID查找交易记录
    findById: async (id) => {
        try {
            const [rows] = await pool.query(`
                SELECT 
                    t.*,
                    u.username as operator_name
                FROM transactions t
                LEFT JOIN users u ON t.operator_id = u.id
                WHERE t.id = ?
            `, [id]);
            return rows[0] || null;
        } catch (error) {
            console.error('查询交易记录失败:', error);
            return null;
        }
    }
};

module.exports = Transaction;