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 INT NOT NULL COMMENT '操作人ID',
                    INDEX idx_group_time (group_id, time),
                    FOREIGN KEY (operator_id) REFERENCES users(id)
                )
            `);
            console.log('交易表创建成功');
        } else {
            // 如果表存在，检查是否需要添加新字段
            const [columns] = await pool.query('SHOW COLUMNS FROM transactions');
            const columnNames = columns.map(col => col.Field);
            
            // 检查并添加缺失的字段
            if (!columnNames.includes('operator_id')) {
                try {
                    // 先添加字段
                    await pool.query(`
                        ALTER TABLE transactions 
                        ADD COLUMN operator_id INT NOT NULL DEFAULT 1 COMMENT '操作人ID' AFTER remark
                    `);
                    console.log('添加operator_id字段成功');

                    // 然后添加外键约束
                    await pool.query(`
                        ALTER TABLE transactions 
                        ADD CONSTRAINT fk_transaction_operator 
                        FOREIGN KEY (operator_id) REFERENCES users(id)
                    `);
                    console.log('添加外键约束成功');
                } catch (error) {
                    console.error('添加operator_id字段或外键约束失败:', error);
                    // 如果添加外键失败，至少保留字段
                }
            }
            
            if (!columnNames.includes('remark')) {
                await pool.query(`
                    ALTER TABLE transactions 
                    ADD COLUMN remark VARCHAR(255) DEFAULT NULL COMMENT '备注' AFTER time
                `);
                console.log('添加remark字段成功');
            }
        }
    } catch (error) {
        console.error('创建/更新交易表失败:', error);
        throw error;
    }
};

// 初始化表
createTransactionTable().catch(error => {
    console.error('初始化交易表失败:', error);
});

// 交易相关方法
const Transaction = {
    // 获取交易列表
    findAll: async (query = {}, page = 1, limit = 10) => {
        try {
            console.log('查询参数:', query);
            let sql = `
                SELECT 
                    t.*,
                    u.username as operator_name
                FROM transactions t
                LEFT JOIN users u ON t.operator_id = u.id
                WHERE 1=1
            `;
            const params = [];

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

            console.log('SQL查询:', sql);
            console.log('参数:', params);

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

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

            const [rows] = await pool.query(sql, params);
            console.log('查询结果数量:', rows.length);

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

    // 创建交易
    create: async (transactionData) => {
        const [result] = await pool.query(
            'INSERT INTO transactions (group_id, group_name, type, amount, remark, operator_id) VALUES (?, ?, ?, ?, ?, ?)',
            [
                transactionData.groupId,
                transactionData.groupName,
                transactionData.type,
                transactionData.amount,
                transactionData.remark || null,
                transactionData.operatorId
            ]
        );
        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 
                    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
            `).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.*,
                u.username as operator_name,
                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;