bot/admin/models/Transaction.js

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

// 创建交易表
const createTransactionTable = async () => {
    try {
        await pool.query(`
            CREATE TABLE IF NOT EXISTS 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,
                amount DECIMAL(10,2) NOT NULL,
                time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_group_time (group_id, time)
            )
        `);
    } catch (error) {
        console.error('创建交易表失败:', error);
    }
};

// 初始化表
createTransactionTable();

// 交易相关方法
const Transaction = {
    // 获取交易列表
    findAll: async (query = {}, page = 1, limit = 10) => {
        let sql = 'SELECT * FROM transactions WHERE 1=1';
        const params = [];

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

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

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

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

        return {
            transactions: rows,
            total,
            page: parseInt(page),
            pages: Math.ceil(total / limit)
        };
    },

    // 创建交易
    create: async (transactionData) => {
        const [result] = await pool.query(
            'INSERT INTO transactions (group_id, group_name, type, amount) VALUES (?, ?, ?, ?)',
            [transactionData.groupId, transactionData.groupName, transactionData.type, transactionData.amount]
        );
        return result.insertId;
    },

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

    // 获取仪表板数据
    getDashboardData: async () => {
        const today = new Date();
        today.setHours(0, 0, 0, 0);

        const [
            totalGroups,
            totalTransactions,
            totalAmount,
            todayTransactions,
            recentTransactions
        ] = await Promise.all([
            pool.query('SELECT COUNT(*) as count FROM groups').then(([rows]) => rows[0].count),
            pool.query('SELECT COUNT(*) as count FROM transactions').then(([rows]) => rows[0].count),
            pool.query('SELECT SUM(amount) as total FROM transactions').then(([rows]) => rows[0].total || 0),
            pool.query('SELECT COUNT(*) as count FROM transactions WHERE time >= ?', [today]).then(([rows]) => rows[0].count),
            pool.query('SELECT * FROM transactions ORDER BY time DESC LIMIT 5').then(([rows]) => rows)
        ]);

        // 获取活跃群组
        const [activeGroups] = await pool.query(`
            SELECT 
                g.group_name as name,
                COUNT(t.id) as totalTransactions,
                SUM(CASE WHEN t.time >= ? THEN 1 ELSE 0 END) as todayTransactions
            FROM groups g
            LEFT JOIN transactions t ON g.group_id = t.group_id
            GROUP BY g.id
            ORDER BY todayTransactions DESC
            LIMIT 5
        `, [today]);

        return {
            totalGroups,
            totalTransactions,
            totalAmount,
            todayTransactions,
            recentTransactions,
            activeGroups
        };
    },

    // 入款方法
    deposit: async (transactionData) => {
        try {
            const id = await Transaction.create({
                groupId: transactionData.groupId,
                groupName: transactionData.groupName,
                type: 'deposit',
                amount: parseFloat(transactionData.amount)
            });

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

            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.*,
                DATE_FORMAT(t.time, '%Y-%m-%d %H:%i:%s') as formatted_time
            FROM transactions t 
            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 [countResult] = await pool.query(
            sql.replace('SELECT t.*, DATE_FORMAT(t.time, \'%Y-%m-%d %H:%i:%s\') as formatted_time', 'SELECT COUNT(*) as total'),
            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 * FROM transactions WHERE id = ?',
                [id]
            );
            return rows[0] || null;
        } catch (error) {
            console.error('查询交易记录失败:', error);
            return null;
        }
    }
};

module.exports = Transaction;