// 系统统计服务
const database = require('../../config/database');

class SystemStatsService {
  /**
   * 获取系统统计数据
   * @returns {Promise<Object>} 系统统计数据
   */
  async getSystemStats() {
    try {
      // 获取用户总数
      const userCountResult = await database.query('SELECT COUNT(*) as count FROM users');
      const userCount = userCountResult[0].count;

      // 获取商家总数
      const merchantCountResult = await database.query('SELECT COUNT(*) as count FROM merchants');
      const merchantCount = merchantCountResult[0].count;

      // 获取旅行计划总数
      const travelCountResult = await database.query('SELECT COUNT(*) as count FROM travel_plans');
      const travelCount = travelCountResult[0].count;

      // 获取动物总数
      const animalCountResult = await database.query('SELECT COUNT(*) as count FROM animals');
      const animalCount = animalCountResult[0].count;

      // 获取订单总数
      const orderCountResult = await database.query('SELECT COUNT(*) as count FROM orders');
      const orderCount = orderCountResult[0].count;

      // 获取今日新增用户数
      const todayUserCountResult = await database.query(
        'SELECT COUNT(*) as count FROM users WHERE DATE(created_at) = CURDATE()'
      );
      const todayUserCount = todayUserCountResult[0].count;

      // 获取今日新增订单数
      const todayOrderCountResult = await database.query(
        'SELECT COUNT(*) as count FROM orders WHERE DATE(ordered_at) = CURDATE()'
      );
      const todayOrderCount = todayOrderCountResult[0].count;

      return {
        userCount,
        merchantCount,
        travelCount,
        animalCount,
        orderCount,
        todayUserCount,
        todayOrderCount
      };
    } catch (error) {
      throw new Error(`获取系统统计数据失败: ${error.message}`);
    }
  }

  /**
   * 获取用户统计信息
   * @returns {Promise<Object>} 用户统计数据
   */
  async getUserStats() {
    try {
      // 用户总数
      const totalResult = await database.query('SELECT COUNT(*) as total FROM users');
      
      // 按用户类型统计
      const byTypeResult = await database.query(`
        SELECT 
          user_type,
          COUNT(*) as count
        FROM users 
        GROUP BY user_type
      `);
      
      // 按注册时间统计（近7天）
      const byDateResult = await database.query(`
        SELECT 
          DATE(created_at) as date,
          COUNT(*) as count
        FROM users 
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) 
        GROUP BY DATE(created_at)
        ORDER BY date ASC
      `);
      
      return {
        total: totalResult[0].total,
        byType: byTypeResult,
        byDate: byDateResult
      };
    } catch (error) {
      throw new Error(`获取用户统计数据失败: ${error.message}`);
    }
  }

  /**
   * 获取订单统计信息
   * @returns {Promise<Object>} 订单统计数据
   */
  async getOrderStats() {
    try {
      // 订单总数和总金额
      const totalResult = await database.query(`
        SELECT 
          COUNT(*) as total,
          COALESCE(SUM(total_amount), 0) as totalAmount
        FROM orders
      `);
      
      // 按状态统计
      const byStatusResult = await database.query(`
        SELECT 
          status,
          COUNT(*) as count
        FROM orders
        GROUP BY status
      `);
      
      // 按日期统计（近7天）
      const byDateResult = await database.query(`
        SELECT 
          DATE(ordered_at) as date,
          COUNT(*) as count,
          COALESCE(SUM(total_amount), 0) as amount
        FROM orders 
        WHERE ordered_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
        GROUP BY DATE(ordered_at)
        ORDER BY date ASC
      `);
      
      return {
        total: totalResult[0].total,
        totalAmount: parseFloat(totalResult[0].totalAmount || 0),
        byStatus: byStatusResult,
        byDate: byDateResult
      };
    } catch (error) {
      throw new Error(`获取订单统计数据失败: ${error.message}`);
    }
  }

  /**
   * 获取系统信息
   * @returns {Promise<Object>} 系统信息
   */
  async getSystemInfo() {
    try {
      // 数据库信息
      const dbInfo = await database.query('SELECT VERSION() as version');
      
      return {
        nodeVersion: process.version,
        platform: process.platform,
        arch: process.arch,
        uptime: Math.floor(process.uptime()),
        databaseVersion: dbInfo[0].version
      };
    } catch (error) {
      throw new Error(`获取系统信息失败: ${error.message}`);
    }
  }
}

module.exports = new SystemStatsService();