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

module.exports = Transaction;