jurong_circle_agent_black/routes/agent.js

const express = require('express');
const router = express.Router();
const {getDB} = require('../database');
const {agentAuth} = require('../middleware/agentAuth');
const {logger} = require('../config/logger');
const dayjs = require('dayjs');

/**
 * 获取代理统计数据
 * GET /api/agent/stats
 */
router.get('/stats', agentAuth, async (req, res) => {
    try {
        const {id: agentId, userId} = req.agent;


        // 获取下级用户统计
        const [userStats] = await getDB().execute(`
            SELECT COUNT(*)                                                 as total_users,
                   COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as today_new_users
            FROM users
            WHERE inviter = ?
        `, [userId]);
        //获取直推用户的分销用户数量
        const [userIds] = await getDB().execute(`
            SELECT id
            FROM users
            WHERE inviter = ?
        `, [userId]);
        let active_users = 0
        for (const id of userIds) {
            const [userTotal] = await getDB().execute(`
                SELECT COUNT(*) as total_users
                FROM users
                WHERE inviter = ?
            `, [id])
            active_users += userTotal[0].total_users
        }
        userStats[0].active_users = active_users;
        // 获取佣金统计
        const [commissionStats] = await getDB().execute(`
            SELECT COALESCE(SUM(amount), 0) AS total_commission,
                   COALESCE(SUM(CASE
                                    WHEN created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
                                        AND created_at < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)
                                        THEN amount
                       END), 0)             AS monthly_commission
            FROM transfers
            WHERE to_user_id = ?
              AND transfer_type = 'user_to_agent'
        `, [userId]);

        // 获取转账统计
        const [transferStats] = await getDB().execute(`
            SELECT COUNT(*)                                                   as total_transfers,
                   COUNT(CASE WHEN DATE(t.created_at) = CURDATE() THEN 1 END) as today_transfers,
                   CAST(COALESCE(SUM(t.amount), 0) AS DECIMAL(10, 2))         as total_amount,
                   CAST(COALESCE(SUM(CASE WHEN DATE(t.created_at) = CURDATE() THEN t.amount ELSE 0 END),
                                 0) AS DECIMAL(10, 2))                        as today_amount
            FROM transfers t
                     INNER JOIN agent_merchants am ON (t.from_user_id = am.merchant_id OR t.to_user_id = am.merchant_id)
            WHERE am.agent_id = ?
        `, [agentId]);

        const stats = {
            users: userStats[0] || {
                total_users: 0,
                today_new_users: 0,
                active_users: 0,
                agent_share_users: 0,
                operated_share_users: 0,
            },
            commissions: commissionStats[0] || {
                total_commission: '0.00',
                today_commission: '0.00',
                paid_commission: '0.00',
                monthly_commission: '0.00'
            },
            transfers: transferStats[0] || {
                total_transfers: 0,
                today_transfers: 0,
                total_amount: '0.00',
                today_amount: '0.00'
            }
        };

        res.json({
            success: true,
            data: stats
        });

    } catch (error) {
        logger.error('获取代理统计数据失败', {
            error: error.message,
            stack: error.stack,
            agentId: req.agent?.id
        });

        res.status(500).json({
            success: false,
            message: '获取统计数据失败'
        });
    }
});

/**
 * 获取代理统计数据-直营代理
 * GET /api/agent/stats
 */
router.get('/stats_agent_directly', agentAuth, async (req, res) => {
    try {
        const {id: agentId, userId} = req.agent;

        // 获取直营用户统计
        const [userStats] = await getDB().execute(`
            SELECT COUNT(*)                                                 as total_directly_agents,
                   COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as today_new_users
            FROM users
            WHERE inviter = ?
              AND user_type = 'directly_operated'
        `, [userId]);
        //获取直营用户的分销用户数量
        const [userIds] = await getDB().execute(`
            SELECT id
            FROM users
            WHERE inviter = ?
              AND user_type = 'directly_operated'
        `, [userId])

        //直营商户人数
        let active_users = 0
        let operated_commission_sql = []
        for (const id of userIds) {
            const [userTotal] = await getDB().execute(`
                SELECT COUNT(*) as total_users
                FROM users
                WHERE inviter = ?
            `, [id.id])
            active_users += userTotal[0].total_users
            operated_commission_sql.push(`to_user_id=${id.id}`)
        }
        userStats[0].active_users = active_users;
        userStats[0].total_operated = userIds.length;
        //直销人员分享人数
        let [agent_share_users] = await getDB().execute(`
           SELECT id FROM users WHERE inviter=? AND user_type='user'
        `, [userId])
        let total_share_users = 0
        for (const id of agent_share_users) {
            const [userTotal] = await getDB().execute(`
                SELECT COUNT(*) as total_users
                FROM users
                WHERE inviter = ?
            `, [id])
            total_share_users += userTotal[0].total_users
        }
        userStats[0].agent_share_users = total_share_users;
        //直营商户分享人数
        let [operated_share_users] = await getDB().execute(`
            SELECT id FROM users WHERE inviter=? AND user_type='directly_operated'
        `, [userId])
        let total_operated_share_users = 0
        let operated_user_ids = []
        let operated_user_sql = []
        for (const id of operated_share_users) {
            const [userTotal] = await getDB().execute(`
                SELECT COUNT(*) as total_users
                FROM users
                WHERE inviter = ?
            `, [id])
            total_operated_share_users += userTotal[0].total_users
            operated_user_sql.push(`to_user_id=?`)
            operated_user_ids.push(userTotal[0].total_users)
        }
        userStats[0].operated_share_users = total_operated_share_users;

        // 获取佣金统计
        const [commissionStats] = await getDB().execute(`
            SELECT COALESCE(SUM(amount), 0) AS total_commission,
                   COALESCE(SUM(CASE
                                    WHEN created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
                                        AND created_at < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)
                                        THEN amount
                       END), 0)             AS monthly_commission
            FROM transfers
            WHERE to_user_id = ?
              AND transfer_type = 'user_to_agent'
        `, [userId]);
        // 获取直营佣金
        let [operated_commission] = operated_commission_sql.length === 0 ? 
        await getDB().execute(`
            SELECT SUM(amount)  as amount FROM transfers
        `)
        : 
        await getDB().execute(`
            SELECT SUM(amount)  as amount FROM transfers WHERE ${operated_commission_sql.join(' or ')}
        `)

        // 获取直销商户利润
        let [directly_agents_income] = await getDB().execute(`
          SELECT SUM(amount)  as amount FROM transfers WHERE to_user_id=? AND transfer_type='user_to_agent' AND source_type='agent_operated'
        `,[userId])

        commissionStats[0].directly_agents_income = directly_agents_income[0].amount || 0

        // 获取直销商户分享利润
        let [active_users_income] = operated_user_sql.length === 0 ? 
        await getDB().execute(`
            SELECT SUM(amount) as amount
            FROM transfers
            WHERE transfer_type = 'user_to_agent'
              AND source_type = 'agent_operated'
        `,[...operated_user_ids])
        :
        await getDB().execute(`
            SELECT SUM(amount) as amount
            FROM transfers
            WHERE  (${operated_user_sql.join(' or ')})
              AND transfer_type = 'user_to_agent'
              AND source_type = 'agent_operated'
        `,[...operated_user_ids])
        commissionStats[0].active_users_income = active_users_income[0].amount || 0


        commissionStats[0].operated_commission = operated_commission[0].amount || 0

        // 获取已提现佣金
        let [get_commission] = await getDB().execute(`
            SELECT SUM(amount) as amount FROM transfers WHERE from_user_id=${userId} AND source_type='withdraw' AND created_at >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
        `)
        commissionStats[0].get_commission = get_commission[0].amount || 0

        // 获取待提现佣金
        let [loading_commission] = await getDB().execute(`
            SELECT SUM(balance) as balance FROM users WHERE inviter=${userId}
        `)
        commissionStats[0].loading_commission = Math.abs(loading_commission[0].balance) || 0

        // 获取总收入
        let [total_commission] = await getDB().execute(`
             SELECT SUM(balance) as balance FROM users WHERE id=${userId}
        `)
        commissionStats[0].total_commission =Math.abs(total_commission[0].balance) || 0

        // 获取转账统计
        const [transferStats] = await getDB().execute(`
            SELECT COUNT(*)                                                   as total_transfers,
                   COUNT(CASE WHEN DATE(t.created_at) = CURDATE() THEN 1 END) as today_transfers,
                   CAST(COALESCE(SUM(t.amount), 0) AS DECIMAL(10, 2))         as total_amount,
                   CAST(COALESCE(SUM(CASE WHEN DATE(t.created_at) = CURDATE() THEN t.amount ELSE 0 END),
                                 0) AS DECIMAL(10, 2))                        as today_amount
            FROM transfers t
                     INNER JOIN agent_merchants am ON (t.from_user_id = am.merchant_id OR t.to_user_id = am.merchant_id)
            WHERE am.agent_id = ?
        `, [agentId]);

        const stats = {
            users: userStats[0] || {
                total_directly_agents: 0,
                today_new_users: 0,
                active_users: 0,
                total_operated: userIds.length
            },
            commissions: commissionStats[0] || {
                total_commission: '0.00',
                today_commission: '0.00',
                paid_commission: '0.00',
                monthly_commission: '0.00'
            },
            transfers: transferStats[0] || {
                total_transfers: 0,
                today_transfers: 0,
                total_amount: '0.00',
                today_amount: '0.00'
            }
        };

        res.json({
            success: true,
            data: stats
        });

    } catch (error) {
        console.log('获取统计数据失败',error)
        res.status(500).json({
            success: false,
            message: '获取统计数据失败'
        });
    }
});

/**
 * 获取用户增长趋势数据
 * GET /api/agent/user-growth-trend
 */
router.get('/user-growth-trend', agentAuth, async (req, res) => {
    try {
        const {id: agentId, userId} = req.agent;
        const {days = 7} = req.query;

        const [trendData] = await getDB().execute(`
            SELECT DATE(created_at) as date,
                   COUNT(*)         as count
            FROM users
            WHERE inviter = ?
              AND created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
            GROUP BY DATE(created_at)
            ORDER BY date ASC
        `, [userId, parseInt(days)]);
        // 填充缺失的日期（注册数为0）
        const result = [];

        for (let i = days - 1; i >= 0; i--) {
            const date = dayjs().subtract(i, 'day');
            const dateStr = date.format('YYYY-MM-DD');

            // 修复日期比较：将数据库返回的Date对象转换为字符串进行比较
            const existingData = trendData.find(item => {
                const itemDateStr = dayjs(item.date).format('YYYY-MM-DD');
                return itemDateStr === dateStr;
            });

            result.push({
                date: date.format('MM-DD'),
                count: existingData ? existingData.count : 0
            });
        }
        res.json({
            success: true,
            data: result
        });

    } catch (error) {
        logger.error('获取用户增长趋势失败', {
            error: error.message,
            stack: error.stack,
            agentId: req.agent?.id
        });

        res.status(500).json({
            success: false,
            message: '获取趋势数据失败'
        });
    }
});

/**
 * 获取营收收入趋势数据
 * GET /api/agent/commission-trend
 */
router.get('/commission-trend', agentAuth, async (req, res) => {
    try {
        const {id: agentId, userId} = req.agent;
        const {days = 7} = req.query;

        const [trendData] = await getDB().execute(`
            SELECT DATE(created_at)                                 as date,
                   CAST(COALESCE(SUM(amount), 0) AS DECIMAL(10, 2)) as amount
            FROM transfers
            WHERE to_user_id = ?
              AND created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
              AND source_type = 'user_to_agent'
            GROUP BY DATE(created_at)
            ORDER BY date ASC
        `, [userId, parseInt(days)]);

        // 填充缺失的日期（佣金为0）
        const result = [];

        for (let i = days - 1; i >= 0; i--) {
            const date = dayjs().subtract(i, 'day');
            const dateStr = date.format('YYYY-MM-DD');

            const existingData = trendData.find(item => {
                const itemDateStr = dayjs(item.date).format('YYYY-MM-DD');
                return itemDateStr === dateStr;
            });

            result.push({
                date: date.format('MM-DD'),
                amount: existingData ? parseFloat(existingData.amount) : 0
            });
        }

        res.json({
            success: true,
            data: result
        });

    } catch (error) {
        logger.error('获取佣金趋势失败', {
            error: error.message,
            stack: error.stack,
            agentId: req.agent?.id
        });

        res.status(500).json({
            success: false,
            message: '获取趋势数据失败'
        });
    }
});

/**
 * 获取用户活跃度数据
 * GET /api/agent/user-activity
 */
router.get('/user-activity', agentAuth, async (req, res) => {
    try {
        const agentId = req.agent.id;
        const {days = 7} = req.query;

        // 获取活跃用户趋势（基于转账活动）
        const [activityTrend] = await getDB().execute(`
            SELECT DATE(t.created_at)                                                                as date,
                   COUNT(DISTINCT CASE WHEN t.from_user_id = am.merchant_id THEN t.from_user_id END) as active_senders,
                   COUNT(DISTINCT CASE
                                      WHEN t.to_user_id = am.merchant_id
                                          THEN t.to_user_id END)                                     as active_receivers,
                   COUNT(DISTINCT am.merchant_id)                                                    as total_active_users
            FROM transfers t
                     INNER JOIN agent_merchants am ON (t.from_user_id = am.merchant_id OR t.to_user_id = am.merchant_id)
            WHERE am.agent_id = ?
              AND t.created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
              AND t.status = 'completed'
            GROUP BY DATE(t.created_at)
            ORDER BY date ASC
        `, [agentId, parseInt(days)]);

        // 获取用户活跃度统计
        const [activityStats] = await getDB().execute(`
            SELECT COUNT(DISTINCT am.merchant_id)                                                                     as total_users,
                   COUNT(DISTINCT CASE
                                      WHEN t.created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY)
                                          THEN am.merchant_id END)                                                    as daily_active_users,
                   COUNT(DISTINCT CASE
                                      WHEN t.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
                                          THEN am.merchant_id END)                                                    as weekly_active_users,
                   COUNT(DISTINCT CASE
                                      WHEN t.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
                                          THEN am.merchant_id END)                                                    as monthly_active_users,
                   ROUND(
                           COUNT(DISTINCT
                                 CASE WHEN t.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN am.merchant_id END) *
                           100.0 /
                           NULLIF(COUNT(DISTINCT am.merchant_id), 0), 2
                   )                                                                                                  as weekly_activity_rate
            FROM agent_merchants am
                     LEFT JOIN transfers t ON (t.from_user_id = am.merchant_id OR t.to_user_id = am.merchant_id)
                AND t.status = 'completed'
            WHERE am.agent_id = ?
        `, [agentId]);

        // 填充缺失的日期
        const trendResult = [];
        for (let i = days - 1; i >= 0; i--) {
            const date = dayjs().subtract(i, 'day');
            const dateStr = date.format('YYYY-MM-DD');

            const existingData = activityTrend.find(item => {
                const itemDateStr = dayjs(item.date).format('YYYY-MM-DD');
                return itemDateStr === dateStr;
            });

            trendResult.push({
                date: date.format('MM-DD'),
                active_users: existingData ? existingData.total_active_users : 0,
                active_senders: existingData ? existingData.active_senders : 0,
                active_receivers: existingData ? existingData.active_receivers : 0
            });
        }

        res.json({
            success: true,
            data: {
                trend: trendResult,
                stats: activityStats[0] || {
                    total_users: 0,
                    daily_active_users: 0,
                    weekly_active_users: 0,
                    monthly_active_users: 0,
                    weekly_activity_rate: 0
                }
            }
        });

    } catch (error) {
        logger.error('获取用户活跃度失败', {
            error: error.message,
            stack: error.stack,
            agentId: req.agent?.id
        });

        res.status(500).json({
            success: false,
            message: '获取用户活跃度数据失败'
        });
    }
});

/**
 * 获取佣金类型分布数据
 * GET /api/agent/commission-distribution
 */
router.get('/commission-distribution', agentAuth, async (req, res) => {
    try {
        const agentId = req.agent.id;

        const [distributionData] = await getDB().execute(`
            SELECT commission_type                                             as type,
                   COUNT(*)                                                    as count,
                   CAST(COALESCE(SUM(commission_amount), 0) AS DECIMAL(10, 2)) as amount
            FROM agent_commission_records
            WHERE agent_id = ?
            GROUP BY commission_type
            ORDER BY amount DESC
        `, [agentId]);

        res.json({
            success: true,
            data: distributionData
        });

    } catch (error) {
        logger.error('获取佣金分布失败', {
            error: error.message,
            stack: error.stack,
            agentId: req.agent?.id
        });

        res.status(500).json({
            success: false,
            message: '获取分布数据失败'
        });
    }
});

/**
 * 获取最新下级用户
 * GET /api/agent/recent-users
 */
router.get('/recent-users', agentAuth, async (req, res) => {
    try {
        const {id: agentId, userId} = req.agent;
        const {limit = 10} = req.query;

        const limitValue = Math.max(1, Math.min(100, parseInt(limit))); // 限制在1-100之间
        const [recentUsers] = await getDB().execute(`
            SELECT u.id,
                   u.username,
                   u.real_name,
                   u.phone,
                   u.avatar,
                   u.balance,
                   u.created_at,
                   u.created_at as join_date
            FROM users u
            WHERE u.inviter = ?
            ORDER BY u.created_at DESC
            LIMIT ${limitValue}
        `, [userId]);

        res.json({
            success: true,
            data: recentUsers
        });

    } catch (error) {
        logger.error('获取最新用户失败', {
            error: error.message,
            stack: error.stack,
            agentId: req.agent?.id
        });

        res.status(500).json({
            success: false,
            message: '获取最新用户失败'
        });
    }
});

/**
 * 获取最新佣金记录
 * GET /api/agent/recent-commissions
 */
router.get('/recent-commissions', agentAuth, async (req, res) => {
    try {
        const {id: agentId, userId} = req.agent;
        const {limit = 10} = req.query;

        const limitValue = Math.max(1, Math.min(100, parseInt(limit))); // 限制在1-100之间
        const [recentCommissions] = await getDB().execute(`
            SELECT acr.id,
                   acr.amount as commission_amount,
                   acr.created_at,
                   u.username,
                   u.real_name
            FROM transfers acr
                     LEFT JOIN users u ON acr.from_user_id = u.id
            WHERE acr.to_user_id = ?
              AND source_type = 'user_to_agent'
            ORDER BY acr.created_at DESC
            LIMIT ${limitValue}
        `, [agentId]);

        res.json({
            success: true,
            data: recentCommissions
        });

    } catch (error) {
        logger.error('获取最新佣金失败', {
            error: error.message,
            stack: error.stack,
            agentId: req.agent?.id
        });

        res.status(500).json({
            success: false,
            message: '获取最新佣金失败'
        });
    }
});

module.exports = router;