/**
 * 会员数据模型
 *
 * 功能：封装会员表、优惠券表和用户优惠券关联表的数据库操作
 */

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

/**
 * 会员模型类
 */
class Member {
  /**
   * 根据用户ID查找会员信息
   * @param {number} userId - 用户ID
   * @returns {Promise<object|null>} 会员对象或null
   */
  static async findByUserId(userId) {
    try {
      const [rows] = await pool.query(
        `SELECT
          m.*,
          u.username,
          u.email,
          u.phone
         FROM members m
         JOIN users u ON m.user_id = u.id
         WHERE m.user_id = ?`,
        [userId]
      );
      return rows[0] || null;
    } catch (error) {
      throw new Error('查询会员信息失败: ' + error.message);
    }
  }

  /**
   * 根据会员ID查找会员信息
   * @param {number} id - 会员ID
   * @returns {Promise<object|null>} 会员对象或null
   */
  static async findById(id) {
    try {
      const [rows] = await pool.query(
        `SELECT
          m.*,
          u.username,
          u.email,
          u.phone
         FROM members m
         JOIN users u ON m.user_id = u.id
         WHERE m.id = ?`,
        [id]
      );
      return rows[0] || null;
    } catch (error) {
      throw new Error('查询会员信息失败: ' + error.message);
    }
  }

  /**
   * 获取会员列表（分页、筛选）
   * @param {object} options - 查询选项
   * @returns {Promise<object>} 会员列表和总数
   */
  static async findAll(options = {}) {
    try {
      const {
        page = 1,
        pageSize = 10,
        level,
        search
      } = options;

      const offset = (page - 1) * pageSize;

      // 构建WHERE条件
      const conditions = [];
      const params = [];

      if (level) {
        conditions.push('m.level = ?');
        params.push(level);
      }

      if (search) {
        conditions.push('(u.username LIKE ? OR u.phone LIKE ? OR u.email LIKE ?)');
        params.push(`%${search}%`, `%${search}%`, `%${search}%`);
      }

      const whereClause = conditions.length > 0
        ? 'WHERE ' + conditions.join(' AND ')
        : '';

      // 查询总数
      const [countResult] = await pool.query(
        `SELECT COUNT(*) as total
         FROM members m
         JOIN users u ON m.user_id = u.id
         ${whereClause}`,
        params
      );
      const total = countResult[0].total;

      // 查询列表
      const [rows] = await pool.query(
        `SELECT
          m.*,
          u.username,
          u.email,
          u.phone
         FROM members m
         JOIN users u ON m.user_id = u.id
         ${whereClause}
         ORDER BY m.total_points DESC
         LIMIT ? OFFSET ?`,
        [...params, pageSize, offset]
      );

      return {
        list: rows,
        total,
        page: parseInt(page),
        pageSize: parseInt(pageSize)
      };
    } catch (error) {
      throw new Error('查询会员列表失败: ' + error.message);
    }
  }

  /**
   * 创建会员
   * @param {number} userId - 用户ID
   * @returns {Promise<object>} 创建的会员
   */
  static async create(userId) {
    try {
      const [result] = await pool.query(
        `INSERT INTO members (user_id, level, points, total_points, total_spent)
         VALUES (?, 'bronze', 0, 0, 0)`,
        [userId]
      );

      return await this.findById(result.insertId);
    } catch (error) {
      throw new Error('创建会员失败: ' + error.message);
    }
  }

  /**
   * 更新会员等级
   * @param {number} userId - 用户ID
   * @param {string} level - 新等级
   * @returns {Promise<object>} 更新后的会员
   */
  static async updateLevel(userId, level) {
    try {
      await pool.query(
        'UPDATE members SET level = ?, updated_at = NOW() WHERE user_id = ?',
        [level, userId]
      );

      return await this.findByUserId(userId);
    } catch (error) {
      throw new Error('更新会员等级失败: ' + error.message);
    }
  }

  /**
   * 增加积分
   * @param {number} userId - 用户ID
   * @param {number} points - 积分数量
   * @param {string} reason - 积分来源
   * @returns {Promise<object>} 更新后的会员
   */
  static async addPoints(userId, points, reason = '消费获得') {
    const connection = await pool.getConnection();

    try {
      await connection.beginTransaction();

      // 更新会员积分
      await connection.query(
        `UPDATE members
         SET points = points + ?,
             total_points = total_points + ?,
             updated_at = NOW()
         WHERE user_id = ?`,
        [points, points, userId]
      );

      // 记录积分历史（如果需要可以创建积分历史表）

      // 检查是否需要升级会员等级
      const [member] = await connection.query(
        'SELECT * FROM members WHERE user_id = ?',
        [userId]
      );

      if (member.length > 0) {
        const currentMember = member[0];
        const newLevel = this.calculateLevel(currentMember.total_points);

        if (newLevel !== currentMember.level) {
          await connection.query(
            'UPDATE members SET level = ? WHERE user_id = ?',
            [newLevel, userId]
          );
        }
      }

      await connection.commit();

      return await this.findByUserId(userId);
    } catch (error) {
      await connection.rollback();
      throw new Error('增加积分失败: ' + error.message);
    } finally {
      connection.release();
    }
  }

  /**
   * 扣减积分
   * @param {number} userId - 用户ID
   * @param {number} points - 积分数量
   * @param {string} reason - 扣减原因
   * @returns {Promise<object>} 更新后的会员
   */
  static async deductPoints(userId, points, reason = '兑换使用') {
    try {
      // 检查积分是否足够
      const member = await this.findByUserId(userId);
      if (!member) {
        throw new Error('会员不存在');
      }

      if (member.points < points) {
        throw new Error('积分不足');
      }

      await pool.query(
        `UPDATE members
         SET points = points - ?,
             updated_at = NOW()
         WHERE user_id = ?`,
        [points, userId]
      );

      return await this.findByUserId(userId);
    } catch (error) {
      throw new Error('扣减积分失败: ' + error.message);
    }
  }

  /**
   * 更新消费金额
   * @param {number} userId - 用户ID
   * @param {number} amount - 消费金额
   * @returns {Promise<object>} 更新后的会员
   */
  static async updateSpent(userId, amount) {
    try {
      await pool.query(
        `UPDATE members
         SET total_spent = total_spent + ?,
             updated_at = NOW()
         WHERE user_id = ?`,
        [amount, userId]
      );

      return await this.findByUserId(userId);
    } catch (error) {
      throw new Error('更新消费金额失败: ' + error.message);
    }
  }

  /**
   * 根据累计积分计算会员等级
   * @param {number} totalPoints - 累计积分
   * @returns {string} 会员等级
   */
  static calculateLevel(totalPoints) {
    if (totalPoints >= 10000) {
      return 'diamond';
    } else if (totalPoints >= 5000) {
      return 'gold';
    } else if (totalPoints >= 1000) {
      return 'silver';
    } else {
      return 'bronze';
    }
  }

  /**
   * 获取会员统计信息
   * @returns {Promise<object>} 统计信息
   */
  static async getStatistics() {
    try {
      // 总会员数和各等级数量
      const [levelStats] = await pool.query(
        `SELECT
          COUNT(*) as total_members,
          SUM(CASE WHEN level = 'bronze' THEN 1 ELSE 0 END) as bronze_count,
          SUM(CASE WHEN level = 'silver' THEN 1 ELSE 0 END) as silver_count,
          SUM(CASE WHEN level = 'gold' THEN 1 ELSE 0 END) as gold_count,
          SUM(CASE WHEN level = 'diamond' THEN 1 ELSE 0 END) as diamond_count,
          SUM(total_spent) as total_revenue,
          AVG(total_spent) as avg_spent_per_member
         FROM members`
      );

      return levelStats[0];
    } catch (error) {
      throw new Error('查询会员统计失败: ' + error.message);
    }
  }

  /**
   * 检查用户是否已是会员
   * @param {number} userId - 用户ID
   * @returns {Promise<boolean>} 是否是会员
   */
  static async isMember(userId) {
    try {
      const [rows] = await pool.query(
        'SELECT id FROM members WHERE user_id = ?',
        [userId]
      );
      return rows.length > 0;
    } catch (error) {
      throw new Error('检查会员状态失败: ' + error.message);
    }
  }

  /**
   * 获取会员优惠折扣
   * @param {string} level - 会员等级
   * @returns {number} 折扣率（0.95表示95折）
   */
  static getDiscount(level) {
    const discounts = {
      bronze: 1.0,    // 无折扣
      silver: 0.95,   // 95折
      gold: 0.9,      // 9折
      diamond: 0.85   // 85折
    };

    return discounts[level] || 1.0;
  }
}

/**
 * 优惠券模型类
 */
class Coupon {
  /**
   * 根据ID查找优惠券
   * @param {number} id - 优惠券ID
   * @returns {Promise<object|null>} 优惠券对象或null
   */
  static async findById(id) {
    try {
      const [rows] = await pool.query(
        'SELECT * FROM coupons WHERE id = ?',
        [id]
      );
      return rows[0] || null;
    } catch (error) {
      throw new Error('查询优惠券失败: ' + error.message);
    }
  }

  /**
   * 获取优惠券列表
   * @param {object} options - 查询选项
   * @returns {Promise<object>} 优惠券列表
   */
  static async findAll(options = {}) {
    try {
      const {
        page = 1,
        pageSize = 10,
        type,
        is_active
      } = options;

      const offset = (page - 1) * pageSize;

      const conditions = [];
      const params = [];

      if (type) {
        conditions.push('type = ?');
        params.push(type);
      }

      if (is_active !== undefined) {
        conditions.push('is_active = ?');
        params.push(is_active);
      }

      const whereClause = conditions.length > 0
        ? 'WHERE ' + conditions.join(' AND ')
        : '';

      const [countResult] = await pool.query(
        `SELECT COUNT(*) as total FROM coupons ${whereClause}`,
        params
      );
      const total = countResult[0].total;

      const [rows] = await pool.query(
        `SELECT * FROM coupons ${whereClause}
         ORDER BY created_at DESC
         LIMIT ? OFFSET ?`,
        [...params, pageSize, offset]
      );

      return {
        list: rows,
        total,
        page: parseInt(page),
        pageSize: parseInt(pageSize)
      };
    } catch (error) {
      throw new Error('查询优惠券列表失败: ' + error.message);
    }
  }

  /**
   * 创建优惠券
   * @param {object} couponData - 优惠券数据
   * @returns {Promise<object>} 创建的优惠券
   */
  static async create(couponData) {
    try {
      const {
        name,
        type,
        discount_value,
        min_amount,
        max_discount,
        valid_from,
        valid_to,
        total_quantity,
        points_required,
        is_active
      } = couponData;

      const [result] = await pool.query(
        `INSERT INTO coupons
         (name, type, discount_value, min_amount, max_discount, valid_from,
          valid_to, total_quantity, remaining_quantity, points_required, is_active)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          name,
          type,
          discount_value,
          min_amount || null,
          max_discount || null,
          valid_from,
          valid_to,
          total_quantity,
          total_quantity, // remaining_quantity初始等于total_quantity
          points_required || 0,
          is_active !== undefined ? is_active : 1
        ]
      );

      return await this.findById(result.insertId);
    } catch (error) {
      throw new Error('创建优惠券失败: ' + error.message);
    }
  }

  /**
   * 更新优惠券
   * @param {number} id - 优惠券ID
   * @param {object} couponData - 要更新的数据
   * @returns {Promise<object>} 更新后的优惠券
   */
  static async update(id, couponData) {
    try {
      const updates = [];
      const values = [];

      const fields = ['name', 'discount_value', 'min_amount', 'max_discount',
                      'valid_from', 'valid_to', 'total_quantity', 'points_required', 'is_active'];

      fields.forEach(field => {
        if (couponData[field] !== undefined) {
          updates.push(`${field} = ?`);
          values.push(couponData[field]);
        }
      });

      if (updates.length === 0) {
        throw new Error('没有要更新的数据');
      }

      values.push(id);

      await pool.query(
        `UPDATE coupons SET ${updates.join(', ')} WHERE id = ?`,
        values
      );

      return await this.findById(id);
    } catch (error) {
      throw new Error('更新优惠券失败: ' + error.message);
    }
  }

  /**
   * 删除优惠券
   * @param {number} id - 优惠券ID
   * @returns {Promise<boolean>} 是否成功
   */
  static async delete(id) {
    try {
      await pool.query('DELETE FROM coupons WHERE id = ?', [id]);
      return true;
    } catch (error) {
      throw new Error('删除优惠券失败: ' + error.message);
    }
  }

  /**
   * 用户领取优惠券
   * @param {number} userId - 用户ID
   * @param {number} couponId - 优惠券ID
   * @returns {Promise<object>} 用户优惠券记录
   */
  static async claimByUser(userId, couponId) {
    const connection = await pool.getConnection();

    try {
      await connection.beginTransaction();

      // 检查优惠券是否存在且有效
      const [coupons] = await connection.query(
        'SELECT * FROM coupons WHERE id = ? AND is_active = 1',
        [couponId]
      );

      if (coupons.length === 0) {
        throw new Error('优惠券不存在或已失效');
      }

      const coupon = coupons[0];

      // 检查剩余数量
      if (coupon.remaining_quantity <= 0) {
        throw new Error('优惠券已被领完');
      }

      // 检查用户是否已领取
      const [existing] = await connection.query(
        'SELECT id FROM user_coupons WHERE user_id = ? AND coupon_id = ?',
        [userId, couponId]
      );

      if (existing.length > 0) {
        throw new Error('您已领取过该优惠券');
      }

      // 检查积分是否足够
      if (coupon.points_required > 0) {
        const [members] = await connection.query(
          'SELECT points FROM members WHERE user_id = ?',
          [userId]
        );

        if (members.length === 0 || members[0].points < coupon.points_required) {
          throw new Error('积分不足，无法领取');
        }

        // 扣减积分
        await connection.query(
          'UPDATE members SET points = points - ? WHERE user_id = ?',
          [coupon.points_required, userId]
        );
      }

      // 创建用户优惠券记录
      const [result] = await connection.query(
        `INSERT INTO user_coupons (user_id, coupon_id, status)
         VALUES (?, ?, 'unused')`,
        [userId, couponId]
      );

      // 减少优惠券剩余数量
      await connection.query(
        'UPDATE coupons SET remaining_quantity = remaining_quantity - 1 WHERE id = ?',
        [couponId]
      );

      await connection.commit();

      // 返回用户优惠券记录
      const [userCoupon] = await connection.query(
        `SELECT
          uc.*,
          c.name,
          c.type,
          c.discount_value,
          c.min_amount,
          c.max_discount,
          c.valid_from,
          c.valid_to
         FROM user_coupons uc
         JOIN coupons c ON uc.coupon_id = c.id
         WHERE uc.id = ?`,
        [result.insertId]
      );

      return userCoupon[0];
    } catch (error) {
      await connection.rollback();
      throw new Error('领取优惠券失败: ' + error.message);
    } finally {
      connection.release();
    }
  }

  /**
   * 获取用户的优惠券列表
   * @param {number} userId - 用户ID
   * @param {string} status - 状态筛选（可选）
   * @returns {Promise<array>} 用户优惠券列表
   */
  static async getUserCoupons(userId, status = null) {
    try {
      let query = `
        SELECT
          uc.*,
          c.name,
          c.type,
          c.discount_value,
          c.min_amount,
          c.max_discount,
          c.valid_from,
          c.valid_to
        FROM user_coupons uc
        JOIN coupons c ON uc.coupon_id = c.id
        WHERE uc.user_id = ?
      `;

      const params = [userId];

      if (status) {
        query += ' AND uc.status = ?';
        params.push(status);
      }

      query += ' ORDER BY uc.created_at DESC';

      const [rows] = await pool.query(query, params);
      return rows;
    } catch (error) {
      throw new Error('查询用户优惠券失败: ' + error.message);
    }
  }

  /**
   * 使用优惠券
   * @param {number} userCouponId - 用户优惠券ID
   * @param {number} orderId - 订单ID
   * @returns {Promise<boolean>} 是否成功
   */
  static async useCoupon(userCouponId, orderId) {
    try {
      await pool.query(
        `UPDATE user_coupons
         SET status = 'used', used_at = NOW(), order_id = ?
         WHERE id = ? AND status = 'unused'`,
        [orderId, userCouponId]
      );

      return true;
    } catch (error) {
      throw new Error('使用优惠券失败: ' + error.message);
    }
  }
}

module.exports = { Member, Coupon };