/**
 * 用户数据模型
 *
 * 功能：封装所有与用户表相关的数据库操作
 *
 * 说明：Model层负责与数据库交互，不包含业务逻辑
 */

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

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

  /**
   * 根据用户名查找用户
   * @param {string} username - 用户名
   * @returns {Promise<object|null>} 用户对象或null
   */
  static async findByUsername(username) {
    try {
      const [rows] = await pool.query(
        'SELECT * FROM users WHERE username = ?',
        [username]
      );
      return rows[0] || null;
    } catch (error) {
      throw new Error('查询用户失败: ' + error.message);
    }
  }

  /**
   * 根据邮箱查找用户
   * @param {string} email - 邮箱
   * @returns {Promise<object|null>} 用户对象或null
   */
  static async findByEmail(email) {
    try {
      const [rows] = await pool.query(
        'SELECT * FROM users WHERE email = ?',
        [email]
      );
      return rows[0] || null;
    } catch (error) {
      throw new Error('查询用户失败: ' + error.message);
    }
  }

  /**
   * 创建新用户
   * @param {object} userData - 用户数据
   * @returns {Promise<object>} 创建的用户信息
   */
  static async create(userData) {
    try {
      const { username, password, real_name, phone, email, role = 'customer' } = userData;

      const [result] = await pool.query(
        `INSERT INTO users (username, password, real_name, phone, email, role)
         VALUES (?, ?, ?, ?, ?, ?)`,
        [username, password, real_name, phone, email, role]
      );

      // 返回新创建的用户（不包含密码）
      return await this.findByIdWithoutPassword(result.insertId);
    } catch (error) {
      // 处理唯一性约束错误
      if (error.code === 'ER_DUP_ENTRY') {
        throw new Error('用户名或邮箱已存在');
      }
      throw new Error('创建用户失败: ' + error.message);
    }
  }

  /**
   * 更新用户信息
   * @param {number} id - 用户ID
   * @param {object} userData - 要更新的数据
   * @returns {Promise<object>} 更新后的用户信息
   */
  static async update(id, userData) {
    try {
      const { real_name, phone, email, avatar } = userData;

      // 构建动态更新语句
      const updates = [];
      const values = [];

      if (real_name !== undefined) {
        updates.push('real_name = ?');
        values.push(real_name);
      }
      if (phone !== undefined) {
        updates.push('phone = ?');
        values.push(phone);
      }
      if (email !== undefined) {
        updates.push('email = ?');
        values.push(email);
      }
      if (avatar !== undefined) {
        updates.push('avatar = ?');
        values.push(avatar);
      }

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

      values.push(id); // 添加WHERE条件的参数

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

      return await this.findByIdWithoutPassword(id);
    } catch (error) {
      throw new Error('更新用户失败: ' + error.message);
    }
  }

  /**
   * 更新密码
   * @param {number} id - 用户ID
   * @param {string} newPassword - 新密码（已加密）
   * @returns {Promise<boolean>} 是否成功
   */
  static async updatePassword(id, newPassword) {
    try {
      await pool.query(
        'UPDATE users SET password = ? WHERE id = ?',
        [newPassword, id]
      );
      return true;
    } catch (error) {
      throw new Error('更新密码失败: ' + error.message);
    }
  }

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

  /**
   * 获取用户列表（分页）
   * @param {object} options - 查询选项
   * @returns {Promise<object>} 用户列表和总数
   */
  static async findAll(options = {}) {
    try {
      const { page = 1, pageSize = 10, role, status } = options;
      const offset = (page - 1) * pageSize;

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

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

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

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

      // 查询列表（排除密码）
      const [rows] = await pool.query(
        `SELECT id, username, real_name, phone, email, role, avatar, status, created_at, updated_at
         FROM users ${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 {number} id - 用户ID
   * @returns {Promise<object|null>} 用户对象（不含密码）
   */
  static async findByIdWithoutPassword(id) {
    try {
      const [rows] = await pool.query(
        `SELECT id, username, real_name, phone, email, role, avatar, status, created_at, updated_at
         FROM users WHERE id = ?`,
        [id]
      );
      return rows[0] || null;
    } catch (error) {
      throw new Error('查询用户失败: ' + error.message);
    }
  }

  /**
   * 更新用户状态
   * @param {number} id - 用户ID
   * @param {number} status - 状态值（1启用/0禁用）
   * @returns {Promise<boolean>} 是否成功
   */
  static async updateStatus(id, status) {
    try {
      await pool.query(
        'UPDATE users SET status = ? WHERE id = ?',
        [status, id]
      );
      return true;
    } catch (error) {
      throw new Error('更新用户状态失败: ' + error.message);
    }
  }

  /**
   * 检查用户名是否已存在
   * @param {string} username - 用户名
   * @param {number} excludeId - 排除的用户ID（用于更新时检查）
   * @returns {Promise<boolean>} 是否存在
   */
  static async isUsernameExists(username, excludeId = null) {
    try {
      let query = 'SELECT COUNT(*) as count FROM users WHERE username = ?';
      const params = [username];

      if (excludeId) {
        query += ' AND id != ?';
        params.push(excludeId);
      }

      const [rows] = await pool.query(query, params);
      return rows[0].count > 0;
    } catch (error) {
      throw new Error('检查用户名失败: ' + error.message);
    }
  }

  /**
   * 检查邮箱是否已存在
   * @param {string} email - 邮箱
   * @param {number} excludeId - 排除的用户ID
   * @returns {Promise<boolean>} 是否存在
   */
  static async isEmailExists(email, excludeId = null) {
    try {
      let query = 'SELECT COUNT(*) as count FROM users WHERE email = ?';
      const params = [email];

      if (excludeId) {
        query += ' AND id != ?';
        params.push(excludeId);
      }

      const [rows] = await pool.query(query, params);
      return rows[0].count > 0;
    } catch (error) {
      throw new Error('检查邮箱失败: ' + error.message);
    }
  }
}

module.exports = User;