/**
 * 统计分析数据模型
 *
 * 功能：封装各类统计分析的数据库操作
 */

const { pool } = require('../config/database');

/**
 * 统计分析模型类
 */
class Analytics {
  /**
   * 获取综合仪表盘数据
   * @param {string} startDate - 开始日期
   * @param {string} endDate - 结束日期
   * @returns {Promise<object>} 仪表盘数据
   */
  static async getDashboard(startDate = null, endDate = null) {
    try {
      const today = new Date().toISOString().split('T')[0];
      const start = startDate || today;
      const end = endDate || today;

      // 订单统计
      const [orderStats] = await pool.query(
        `SELECT
          COUNT(*) as total_orders,
          SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_orders,
          SUM(CASE WHEN status = 'confirmed' THEN 1 ELSE 0 END) as confirmed_orders,
          SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_orders,
          SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) as paid_orders,
          SUM(final_amount) as total_revenue,
          AVG(final_amount) as avg_order_amount
         FROM orders
         WHERE DATE(created_at) BETWEEN ? AND ?`,
        [start, end]
      );

      // 支付统计
      const [paymentStats] = await pool.query(
        `SELECT
          COUNT(*) as total_payments,
          SUM(amount) as total_payment_amount,
          SUM(CASE WHEN payment_method = 'cash' THEN amount ELSE 0 END) as cash_amount,
          SUM(CASE WHEN payment_method = 'card' THEN amount ELSE 0 END) as card_amount,
          SUM(CASE WHEN payment_method = 'wechat' THEN amount ELSE 0 END) as wechat_amount,
          SUM(CASE WHEN payment_method = 'alipay' THEN amount ELSE 0 END) as alipay_amount
         FROM payments
         WHERE status = 'paid' AND DATE(paid_at) BETWEEN ? AND ?`,
        [start, end]
      );

      // 会员统计
      const [memberStats] = await pool.query(
        `SELECT
          COUNT(*) as total_members,
          SUM(CASE WHEN level = 'bronze' THEN 1 ELSE 0 END) as bronze_members,
          SUM(CASE WHEN level = 'silver' THEN 1 ELSE 0 END) as silver_members,
          SUM(CASE WHEN level = 'gold' THEN 1 ELSE 0 END) as gold_members,
          SUM(CASE WHEN level = 'diamond' THEN 1 ELSE 0 END) as diamond_members
         FROM members`
      );

      // 预订统计
      const [reservationStats] = await pool.query(
        `SELECT
          COUNT(*) as total_reservations,
          SUM(CASE WHEN type = 'dine_in' THEN 1 ELSE 0 END) as dine_in_count,
          SUM(CASE WHEN type = 'takeaway' THEN 1 ELSE 0 END) as takeaway_count,
          SUM(CASE WHEN status = 'confirmed' THEN 1 ELSE 0 END) as confirmed_count
         FROM reservations
         WHERE DATE(reservation_time) BETWEEN ? AND ?`,
        [start, end]
      );

      // 库存预警
      const [inventoryAlerts] = await pool.query(
        `SELECT COUNT(*) as low_stock_count
         FROM inventory
         WHERE quantity <= alert_quantity`
      );

      return {
        period: { start_date: start, end_date: end },
        orders: orderStats[0],
        payments: paymentStats[0],
        members: memberStats[0],
        reservations: reservationStats[0],
        inventory: inventoryAlerts[0]
      };
    } catch (error) {
      throw new Error('获取仪表盘数据失败: ' + error.message);
    }
  }

  /**
   * 获取销售趋势（按日期）
   * @param {string} startDate - 开始日期
   * @param {string} endDate - 结束日期
   * @returns {Promise<array>} 每日销售数据
   */
  static async getSalesTrend(startDate, endDate) {
    try {
      const [rows] = await pool.query(
        `SELECT
          DATE(paid_at) as date,
          COUNT(*) as order_count,
          SUM(amount) as revenue,
          AVG(amount) as avg_amount
         FROM payments
         WHERE status = 'paid'
         AND DATE(paid_at) BETWEEN ? AND ?
         GROUP BY DATE(paid_at)
         ORDER BY date ASC`,
        [startDate, endDate]
      );

      return rows;
    } catch (error) {
      throw new Error('获取销售趋势失败: ' + error.message);
    }
  }

  /**
   * 获取热门菜品排行
   * @param {number} limit - 限制数量
   * @param {string} startDate - 开始日期（可选）
   * @param {string} endDate - 结束日期（可选）
   * @returns {Promise<array>} 热门菜品列表
   */
  static async getTopDishes(limit = 10, startDate = null, endDate = null) {
    try {
      let query = `
        SELECT
          oi.dish_id,
          oi.dish_name,
          COUNT(*) as order_count,
          SUM(oi.quantity) as total_quantity,
          SUM(oi.subtotal) as total_revenue,
          AVG(oi.price) as avg_price
        FROM order_items oi
        JOIN orders o ON oi.order_id = o.id
        WHERE o.status IN ('completed', 'paid')
      `;

      const params = [];

      if (startDate && endDate) {
        query += ' AND DATE(o.created_at) BETWEEN ? AND ?';
        params.push(startDate, endDate);
      }

      query += `
        GROUP BY oi.dish_id, oi.dish_name
        ORDER BY total_quantity DESC
        LIMIT ?
      `;
      params.push(limit);

      const [rows] = await pool.query(query, params);

      return rows;
    } catch (error) {
      throw new Error('获取热门菜品失败: ' + error.message);
    }
  }

  /**
   * 获取分类销售统计
   * @param {string} startDate - 开始日期（可选）
   * @param {string} endDate - 结束日期（可选）
   * @returns {Promise<array>} 分类销售数据
   */
  static async getCategorySales(startDate = null, endDate = null) {
    try {
      let query = `
        SELECT
          dc.id as category_id,
          dc.name as category_name,
          COUNT(DISTINCT oi.order_id) as order_count,
          SUM(oi.quantity) as total_quantity,
          SUM(oi.subtotal) as total_revenue
        FROM order_items oi
        JOIN dishes d ON oi.dish_id = d.id
        JOIN dish_categories dc ON d.category_id = dc.id
        JOIN orders o ON oi.order_id = o.id
        WHERE o.status IN ('completed', 'paid')
      `;

      const params = [];

      if (startDate && endDate) {
        query += ' AND DATE(o.created_at) BETWEEN ? AND ?';
        params.push(startDate, endDate);
      }

      query += `
        GROUP BY dc.id, dc.name
        ORDER BY total_revenue DESC
      `;

      const [rows] = await pool.query(query, params);

      return rows;
    } catch (error) {
      throw new Error('获取分类销售统计失败: ' + error.message);
    }
  }

  /**
   * 获取营业时段分析
   * @param {string} startDate - 开始日期（可选）
   * @param {string} endDate - 结束日期（可选）
   * @returns {Promise<array>} 时段销售数据
   */
  static async getHourlyAnalysis(startDate = null, endDate = null) {
    try {
      let query = `
        SELECT
          HOUR(created_at) as hour,
          COUNT(*) as order_count,
          SUM(final_amount) as revenue,
          AVG(final_amount) as avg_amount
        FROM orders
        WHERE status IN ('completed', 'paid')
      `;

      const params = [];

      if (startDate && endDate) {
        query += ' AND DATE(created_at) BETWEEN ? AND ?';
        params.push(startDate, endDate);
      }

      query += `
        GROUP BY HOUR(created_at)
        ORDER BY hour ASC
      `;

      const [rows] = await pool.query(query, params);

      return rows;
    } catch (error) {
      throw new Error('获取时段分析失败: ' + error.message);
    }
  }

  /**
   * 获取会员消费排行
   * @param {number} limit - 限制数量
   * @param {string} startDate - 开始日期（可选）
   * @param {string} endDate - 结束日期（可选）
   * @returns {Promise<array>} 会员消费排行
   */
  static async getTopMembers(limit = 10, startDate = null, endDate = null) {
    try {
      let query = `
        SELECT
          m.user_id,
          u.username,
          m.level,
          COUNT(o.id) as order_count,
          SUM(o.final_amount) as total_spent,
          AVG(o.final_amount) as avg_spent,
          m.points as current_points
        FROM members m
        JOIN users u ON m.user_id = u.id
        LEFT JOIN orders o ON m.user_id = o.customer_id
      `;

      const params = [];
      const conditions = ["o.status IN ('completed', 'paid')"];

      if (startDate && endDate) {
        conditions.push('DATE(o.created_at) BETWEEN ? AND ?');
        params.push(startDate, endDate);
      }

      if (conditions.length > 0) {
        query += ' WHERE ' + conditions.join(' AND ');
      }

      query += `
        GROUP BY m.user_id, u.username, m.level, m.points
        ORDER BY total_spent DESC
        LIMIT ?
      `;
      params.push(limit);

      const [rows] = await pool.query(query, params);

      return rows;
    } catch (error) {
      throw new Error('获取会员排行失败: ' + error.message);
    }
  }

  /**
   * 获取订单类型统计
   * @param {string} startDate - 开始日期（可选）
   * @param {string} endDate - 结束日期（可选）
   * @returns {Promise<array>} 订单类型数据
   */
  static async getOrderTypeStats(startDate = null, endDate = null) {
    try {
      let query = `
        SELECT
          order_type,
          COUNT(*) as count,
          SUM(final_amount) as revenue,
          AVG(final_amount) as avg_amount
        FROM orders
        WHERE status IN ('completed', 'paid')
      `;

      const params = [];

      if (startDate && endDate) {
        query += ' AND DATE(created_at) BETWEEN ? AND ?';
        params.push(startDate, endDate);
      }

      query += ' GROUP BY order_type';

      const [rows] = await pool.query(query, params);

      return rows;
    } catch (error) {
      throw new Error('获取订单类型统计失败: ' + error.message);
    }
  }

  /**
   * 获取支付方式统计
   * @param {string} startDate - 开始日期（可选）
   * @param {string} endDate - 结束日期（可选）
   * @returns {Promise<array>} 支付方式数据
   */
  static async getPaymentMethodStats(startDate = null, endDate = null) {
    try {
      let query = `
        SELECT
          payment_method,
          COUNT(*) as count,
          SUM(amount) as total_amount,
          AVG(amount) as avg_amount
        FROM payments
        WHERE status = 'paid'
      `;

      const params = [];

      if (startDate && endDate) {
        query += ' AND DATE(paid_at) BETWEEN ? AND ?';
        params.push(startDate, endDate);
      }

      query += ' GROUP BY payment_method ORDER BY total_amount DESC';

      const [rows] = await pool.query(query, params);

      return rows;
    } catch (error) {
      throw new Error('获取支付方式统计失败: ' + error.message);
    }
  }

  /**
   * 获取月度对比报表
   * @param {number} months - 对比月份数（默认6个月）
   * @returns {Promise<array>} 月度数据
   */
  static async getMonthlyComparison(months = 6) {
    try {
      const [rows] = await pool.query(
        `SELECT
          DATE_FORMAT(paid_at, '%Y-%m') as month,
          COUNT(*) as order_count,
          SUM(amount) as revenue,
          AVG(amount) as avg_amount
         FROM payments
         WHERE status = 'paid'
         AND paid_at >= DATE_SUB(NOW(), INTERVAL ? MONTH)
         GROUP BY DATE_FORMAT(paid_at, '%Y-%m')
         ORDER BY month ASC`,
        [months]
      );

      return rows;
    } catch (error) {
      throw new Error('获取月度对比失败: ' + error.message);
    }
  }

  /**
   * 获取库存价值报表
   * @returns {Promise<object>} 库存价值数据
   */
  static async getInventoryValue() {
    try {
      const [categoryStats] = await pool.query(
        `SELECT
          category,
          COUNT(*) as item_count,
          SUM(quantity * purchase_price) as total_value,
          SUM(CASE WHEN quantity <= alert_quantity THEN 1 ELSE 0 END) as low_stock_count
         FROM inventory
         GROUP BY category
         ORDER BY total_value DESC`
      );

      const [totalStats] = await pool.query(
        `SELECT
          COUNT(*) as total_items,
          SUM(quantity * purchase_price) as total_value,
          SUM(CASE WHEN quantity <= alert_quantity THEN 1 ELSE 0 END) as total_low_stock
         FROM inventory`
      );

      return {
        summary: totalStats[0],
        by_category: categoryStats
      };
    } catch (error) {
      throw new Error('获取库存价值报表失败: ' + error.message);
    }
  }

  /**
   * 获取优惠券使用统计
   * @param {string} startDate - 开始日期（可选）
   * @param {string} endDate - 结束日期（可选）
   * @returns {Promise<object>} 优惠券统计
   */
  static async getCouponStats(startDate = null, endDate = null) {
    try {
      let whereClause = "WHERE uc.status = 'used'";
      const params = [];

      if (startDate && endDate) {
        whereClause += ' AND DATE(uc.used_at) BETWEEN ? AND ?';
        params.push(startDate, endDate);
      }

      const [rows] = await pool.query(
        `SELECT
          c.id as coupon_id,
          c.name as coupon_name,
          c.type,
          COUNT(*) as used_count,
          c.total_quantity,
          c.remaining_quantity
         FROM user_coupons uc
         JOIN coupons c ON uc.coupon_id = c.id
         ${whereClause}
         GROUP BY c.id, c.name, c.type, c.total_quantity, c.remaining_quantity
         ORDER BY used_count DESC`,
        params
      );

      return rows;
    } catch (error) {
      throw new Error('获取优惠券统计失败: ' + error.message);
    }
  }

  /**
   * 获取预订转化率
   * @param {string} startDate - 开始日期（可选）
   * @param {string} endDate - 结束日期（可选）
   * @returns {Promise<object>} 预订转化数据
   */
  static async getReservationConversion(startDate = null, endDate = null) {
    try {
      let whereClause = '';
      const params = [];

      if (startDate && endDate) {
        whereClause = 'WHERE DATE(reservation_time) BETWEEN ? AND ?';
        params.push(startDate, endDate);
      }

      const [stats] = await pool.query(
        `SELECT
          COUNT(*) as total_reservations,
          SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_count,
          SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_count,
          SUM(CASE WHEN status = 'no_show' THEN 1 ELSE 0 END) as no_show_count
         FROM reservations
         ${whereClause}`,
        params
      );

      const result = stats[0];
      result.completion_rate = result.total_reservations > 0
        ? (result.completed_count / result.total_reservations * 100).toFixed(2)
        : 0;

      return result;
    } catch (error) {
      throw new Error('获取预订转化率失败: ' + error.message);
    }
  }
}

module.exports = Analytics;