// 管理员数据统计控制器
const { query } = require('../../config/database');

/**
 * 获取系统概览统计
 * @param {Object} req - 请求对象
 * @param {Object} res - 响应对象
 * @param {Function} next - 下一个中间件
 */
exports.getSystemOverview = async (req, res, next) => {
  try {
    // 用户统计
    const userStatsSql = `
      SELECT 
        COUNT(*) as total_users,
        COUNT(CASE WHEN status = 'active' THEN 1 END) as active_users,
        COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as new_users_today,
        COUNT(CASE WHEN DATE(created_at) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 1 END) as new_users_week
      FROM users
    `;
    const userStats = await query(userStatsSql);

    // 旅行统计
    const travelStatsSql = `
      SELECT 
        COUNT(*) as total_travels,
        COUNT(CASE WHEN status = 'published' THEN 1 END) as published_travels,
        COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as new_travels_today,
        COUNT(CASE WHEN DATE(created_at) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 1 END) as new_travels_week
      FROM travels
    `;
    const travelStats = await query(travelStatsSql);

    // 动物统计
    const animalStatsSql = `
      SELECT 
        COUNT(*) as total_animals,
        COUNT(CASE WHEN status = 'available' THEN 1 END) as available_animals,
        COUNT(CASE WHEN status = 'claimed' THEN 1 END) as claimed_animals
      FROM animals
    `;
    const animalStats = await query(animalStatsSql);

    // 认领统计
    const claimStatsSql = `
      SELECT 
        COUNT(*) as total_claims,
        COUNT(CASE WHEN status = 'approved' THEN 1 END) as approved_claims,
        COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as new_claims_today,
        COUNT(CASE WHEN DATE(created_at) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 1 END) as new_claims_week
      FROM animal_claims
    `;
    const claimStats = await query(claimStatsSql);

    // 订单统计
    const orderStatsSql = `
      SELECT 
        COUNT(*) as total_orders,
        COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_orders,
        COALESCE(SUM(CASE WHEN status = 'completed' THEN amount END), 0) as total_revenue,
        COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as new_orders_today
      FROM orders
    `;
    const orderStats = await query(orderStatsSql);

    // 推广统计
    const promotionStatsSql = `
      SELECT 
        COUNT(DISTINCT user_id) as total_promoters,
        COALESCE(SUM(commission_amount), 0) as total_commission,
        COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_withdrawals
      FROM promotion_records
    `;
    const promotionStats = await query(promotionStatsSql);

    res.status(200).json({
      success: true,
      code: 200,
      message: '获取成功',
      data: {
        users: userStats[0],
        travels: travelStats[0],
        animals: animalStats[0],
        claims: claimStats[0],
        orders: orderStats[0],
        promotions: promotionStats[0]
      }
    });
  } catch (error) {
    next(error);
  }
};

/**
 * 获取用户增长趋势
 * @param {Object} req - 请求对象
 * @param {Object} res - 响应对象
 * @param {Function} next - 下一个中间件
 */
exports.getUserGrowthTrend = async (req, res, next) => {
  try {
    const { period = '30d' } = req.query;
    
    let days;
    switch (period) {
      case '7d':
        days = 7;
        break;
      case '90d':
        days = 90;
        break;
      case '365d':
        days = 365;
        break;
      default:
        days = 30;
    }

    const trendSql = `
      SELECT 
        DATE(created_at) as date,
        COUNT(*) as new_users,
        COUNT(CASE WHEN user_type = 'farmer' THEN 1 END) as new_farmers,
        COUNT(CASE WHEN user_type = 'merchant' THEN 1 END) as new_merchants
      FROM users
      WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL ${days} DAY)
      GROUP BY DATE(created_at)
      ORDER BY date ASC
    `;
    
    const trendData = await query(trendSql);

    // 计算累计用户数
    const cumulativeSql = `
      SELECT COUNT(*) as cumulative_users
      FROM users
      WHERE created_at < DATE_SUB(CURDATE(), INTERVAL ${days} DAY)
    `;
    const cumulativeResult = await query(cumulativeSql);
    let cumulativeUsers = cumulativeResult[0].cumulative_users;

    const enrichedTrendData = trendData.map(item => {
      cumulativeUsers += item.new_users;
      return {
        ...item,
        cumulative_users: cumulativeUsers
      };
    });

    res.status(200).json({
      success: true,
      code: 200,
      message: '获取成功',
      data: {
        period,
        trendData: enrichedTrendData
      }
    });
  } catch (error) {
    next(error);
  }
};

/**
 * 获取业务数据统计
 * @param {Object} req - 请求对象
 * @param {Object} res - 响应对象
 * @param {Function} next - 下一个中间件
 */
exports.getBusinessStatistics = async (req, res, next) => {
  try {
    const { period = '30d' } = req.query;
    
    let days;
    switch (period) {
      case '7d':
        days = 7;
        break;
      case '90d':
        days = 90;
        break;
      default:
        days = 30;
    }

    // 旅行数据统计
    const travelStatsSql = `
      SELECT 
        DATE(created_at) as date,
        COUNT(*) as new_travels,
        COUNT(CASE WHEN status = 'published' THEN 1 END) as published_travels,
        COUNT(CASE WHEN status = 'matched' THEN 1 END) as matched_travels
      FROM travels
      WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL ${days} DAY)
      GROUP BY DATE(created_at)
      ORDER BY date ASC
    `;
    const travelStats = await query(travelStatsSql);

    // 认领数据统计
    const claimStatsSql = `
      SELECT 
        DATE(created_at) as date,
        COUNT(*) as new_claims,
        COUNT(CASE WHEN status = 'approved' THEN 1 END) as approved_claims,
        COUNT(CASE WHEN status = 'rejected' THEN 1 END) as rejected_claims
      FROM animal_claims
      WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL ${days} DAY)
      GROUP BY DATE(created_at)
      ORDER BY date ASC
    `;
    const claimStats = await query(claimStatsSql);

    // 订单数据统计
    const orderStatsSql = `
      SELECT 
        DATE(created_at) as date,
        COUNT(*) as new_orders,
        COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_orders,
        COALESCE(SUM(CASE WHEN status = 'completed' THEN amount END), 0) as daily_revenue
      FROM orders
      WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL ${days} DAY)
      GROUP BY DATE(created_at)
      ORDER BY date ASC
    `;
    const orderStats = await query(orderStatsSql);

    res.status(200).json({
      success: true,
      code: 200,
      message: '获取成功',
      data: {
        period,
        travelStats,
        claimStats,
        orderStats
      }
    });
  } catch (error) {
    next(error);
  }
};

/**
 * 获取地域分布统计
 * @param {Object} req - 请求对象
 * @param {Object} res - 响应对象
 * @param {Function} next - 下一个中间件
 */
exports.getGeographicDistribution = async (req, res, next) => {
  try {
    // 用户地域分布
    const userDistributionSql = `
      SELECT 
        province,
        city,
        COUNT(*) as user_count
      FROM users
      WHERE province IS NOT NULL AND city IS NOT NULL
      GROUP BY province, city
      ORDER BY user_count DESC
      LIMIT 50
    `;
    const userDistribution = await query(userDistributionSql);

    // 省份统计
    const provinceStatsSql = `
      SELECT 
        province,
        COUNT(*) as user_count,
        COUNT(CASE WHEN user_type = 'farmer' THEN 1 END) as farmer_count,
        COUNT(CASE WHEN user_type = 'merchant' THEN 1 END) as merchant_count
      FROM users
      WHERE province IS NOT NULL
      GROUP BY province
      ORDER BY user_count DESC
    `;
    const provinceStats = await query(provinceStatsSql);

    // 旅行目的地统计
    const destinationStatsSql = `
      SELECT 
        destination,
        COUNT(*) as travel_count
      FROM travels
      WHERE destination IS NOT NULL
      GROUP BY destination
      ORDER BY travel_count DESC
      LIMIT 20
    `;
    const destinationStats = await query(destinationStatsSql);

    res.status(200).json({
      success: true,
      code: 200,
      message: '获取成功',
      data: {
        userDistribution,
        provinceStats,
        destinationStats
      }
    });
  } catch (error) {
    next(error);
  }
};

/**
 * 获取用户行为分析
 * @param {Object} req - 请求对象
 * @param {Object} res - 响应对象
 * @param {Function} next - 下一个中间件
 */
exports.getUserBehaviorAnalysis = async (req, res, next) => {
  try {
    // 用户活跃度分析
    const activitySql = `
      SELECT 
        CASE 
          WHEN last_login_at >= DATE_SUB(NOW(), INTERVAL 1 DAY) THEN '今日活跃'
          WHEN last_login_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN '本周活跃'
          WHEN last_login_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN '本月活跃'
          ELSE '不活跃'
        END as activity_level,
        COUNT(*) as user_count
      FROM users
      WHERE last_login_at IS NOT NULL
      GROUP BY activity_level
    `;
    const activityStats = await query(activitySql);

    // 用户等级分布
    const levelDistributionSql = `
      SELECT 
        level,
        COUNT(*) as user_count,
        AVG(points) as avg_points,
        AVG(travel_count) as avg_travel_count,
        AVG(animal_claim_count) as avg_claim_count
      FROM users
      GROUP BY level
      ORDER BY 
        CASE level
          WHEN 'bronze' THEN 1
          WHEN 'silver' THEN 2
          WHEN 'gold' THEN 3
          WHEN 'platinum' THEN 4
        END
    `;
    const levelDistribution = await query(levelDistributionSql);

    // 用户行为偏好
    const behaviorSql = `
      SELECT 
        'travel_focused' as behavior_type,
        COUNT(*) as user_count
      FROM users
      WHERE travel_count > animal_claim_count AND travel_count > 0
      
      UNION ALL
      
      SELECT 
        'animal_focused' as behavior_type,
        COUNT(*) as user_count
      FROM users
      WHERE animal_claim_count > travel_count AND animal_claim_count > 0
      
      UNION ALL
      
      SELECT 
        'balanced' as behavior_type,
        COUNT(*) as user_count
      FROM users
      WHERE travel_count = animal_claim_count AND travel_count > 0
      
      UNION ALL
      
      SELECT 
        'inactive' as behavior_type,
        COUNT(*) as user_count
      FROM users
      WHERE travel_count = 0 AND animal_claim_count = 0
    `;
    const behaviorStats = await query(behaviorSql);

    res.status(200).json({
      success: true,
      code: 200,
      message: '获取成功',
      data: {
        activityStats,
        levelDistribution,
        behaviorStats
      }
    });
  } catch (error) {
    next(error);
  }
};

/**
 * 获取收入统计
 * @param {Object} req - 请求对象
 * @param {Object} res - 响应对象
 * @param {Function} next - 下一个中间件
 */
exports.getRevenueStatistics = async (req, res, next) => {
  try {
    const { period = '30d' } = req.query;
    
    let days;
    switch (period) {
      case '7d':
        days = 7;
        break;
      case '90d':
        days = 90;
        break;
      case '365d':
        days = 365;
        break;
      default:
        days = 30;
    }

    // 收入趋势
    const revenueTrendSql = `
      SELECT 
        DATE(created_at) as date,
        COALESCE(SUM(CASE WHEN status = 'completed' THEN amount END), 0) as daily_revenue,
        COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_orders,
        COUNT(*) as total_orders
      FROM orders
      WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL ${days} DAY)
      GROUP BY DATE(created_at)
      ORDER BY date ASC
    `;
    const revenueTrend = await query(revenueTrendSql);

    // 收入来源分析
    const revenueSourceSql = `
      SELECT 
        order_type,
        COUNT(*) as order_count,
        COALESCE(SUM(CASE WHEN status = 'completed' THEN amount END), 0) as total_revenue,
        AVG(CASE WHEN status = 'completed' THEN amount END) as avg_order_value
      FROM orders
      WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL ${days} DAY)
      GROUP BY order_type
    `;
    const revenueSource = await query(revenueSourceSql);

    // 支付方式统计
    const paymentMethodSql = `
      SELECT 
        payment_method,
        COUNT(*) as order_count,
        COALESCE(SUM(amount), 0) as total_amount
      FROM orders
      WHERE status = 'completed' 
        AND created_at >= DATE_SUB(CURDATE(), INTERVAL ${days} DAY)
      GROUP BY payment_method
    `;
    const paymentMethodStats = await query(paymentMethodSql);

    res.status(200).json({
      success: true,
      code: 200,
      message: '获取成功',
      data: {
        period,
        revenueTrend,
        revenueSource,
        paymentMethodStats
      }
    });
  } catch (error) {
    next(error);
  }
};

/**
 * 导出统计报告
 * @param {Object} req - 请求对象
 * @param {Object} res - 响应对象
 * @param {Function} next - 下一个中间件
 */
exports.exportStatisticsReport = async (req, res, next) => {
  try {
    const { 
      reportType = 'overview',
      period = '30d',
      format = 'csv'
    } = req.query;

    let reportData = {};
    const timestamp = new Date().toISOString().slice(0, 10);

    switch (reportType) {
      case 'overview':
        // 获取系统概览数据
        const overviewSql = `
          SELECT 
            '用户总数' as metric, COUNT(*) as value FROM users
          UNION ALL
          SELECT 
            '活跃用户' as metric, COUNT(*) as value FROM users WHERE status = 'active'
          UNION ALL
          SELECT 
            '旅行总数' as metric, COUNT(*) as value FROM travels
          UNION ALL
          SELECT 
            '认领总数' as metric, COUNT(*) as value FROM animal_claims
          UNION ALL
          SELECT 
            '订单总数' as metric, COUNT(*) as value FROM orders
          UNION ALL
          SELECT 
            '总收入' as metric, COALESCE(SUM(amount), 0) as value FROM orders WHERE status = 'completed'
        `;
        reportData.overview = await query(overviewSql);
        break;

      case 'users':
        // 用户详细报告
        const userReportSql = `
          SELECT 
            DATE(created_at) as date,
            COUNT(*) as new_users,
            COUNT(CASE WHEN user_type = 'farmer' THEN 1 END) as new_farmers,
            COUNT(CASE WHEN user_type = 'merchant' THEN 1 END) as new_merchants
          FROM users
          WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
          GROUP BY DATE(created_at)
          ORDER BY date ASC
        `;
        reportData.users = await query(userReportSql);
        break;

      case 'revenue':
        // 收入报告
        const revenueReportSql = `
          SELECT 
            DATE(created_at) as date,
            COUNT(*) as total_orders,
            COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_orders,
            COALESCE(SUM(CASE WHEN status = 'completed' THEN amount END), 0) as daily_revenue
          FROM orders
          WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
          GROUP BY DATE(created_at)
          ORDER BY date ASC
        `;
        reportData.revenue = await query(revenueReportSql);
        break;
    }

    if (format === 'csv') {
      // 生成CSV格式
      let csvContent = '';
      
      Object.keys(reportData).forEach(key => {
        csvContent += `\n${key.toUpperCase()} 报告\n`;
        if (reportData[key].length > 0) {
          // 添加表头
          const headers = Object.keys(reportData[key][0]).join(',');
          csvContent += headers + '\n';
          
          // 添加数据
          reportData[key].forEach(row => {
            const values = Object.values(row).join(',');
            csvContent += values + '\n';
          });
        }
      });

      res.setHeader('Content-Type', 'text/csv; charset=utf-8');
      res.setHeader('Content-Disposition', `attachment; filename=statistics_report_${timestamp}.csv`);
      res.send('\uFEFF' + csvContent);
    } else {
      // 返回JSON格式
      res.status(200).json({
        success: true,
        code: 200,
        message: '导出成功',
        data: {
          reportType,
          period,
          timestamp,
          ...reportData
        }
      });
    }

    // 记录操作日志
    const logSql = `
      INSERT INTO admin_operation_logs (admin_id, operation_type, target_type, target_id, operation_detail, created_at)
      VALUES (?, 'export_statistics', 'system', ?, ?, NOW())
    `;
    const operationDetail = JSON.stringify({
      reportType,
      period,
      format
    });
    await query(logSql, [req.admin.id, 0, operationDetail]);

  } catch (error) {
    next(error);
  }
};