const { query, transaction } = require('../config/database');

class UserMySQL {
  // 创建用户
  static async create(userData) {
    const {
      username,
      password_hash,
      user_type = 'farmer',
      real_name = '',
      avatar_url = '',
      email = null,
      phone = null
    } = userData;

    const sql = `
      INSERT INTO users (
        username, password_hash, user_type, real_name, avatar_url, email, phone,
        created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
    `;

    const params = [
      username,
      password_hash,
      user_type,
      real_name,
      avatar_url,
      email,
      phone
    ];

    const result = await query(sql, params);
    return result.insertId;
  }

  // 根据ID查找用户
  static async findById(id) {
    if (id === undefined || id === null) {
      throw new Error('User ID cannot be undefined or null');
    }
    const sql = 'SELECT * FROM users WHERE id = ?';
    const rows = await query(sql, [id]);
    return rows[0] || null;
  }

  // 根据用户名查找用户
  static async findByUsername(username) {
    const sql = 'SELECT * FROM users WHERE username = ?';
    const rows = await query(sql, [username]);
    return rows[0] || null;
  }

  // 根据手机号查找用户
  static async findByPhone(phone) {
    const sql = 'SELECT * FROM users WHERE phone = ?';
    const rows = await query(sql, [phone]);
    return rows[0] || null;
  }

  // 根据邮箱查找用户
  static async findByEmail(email) {
    const sql = 'SELECT * FROM users WHERE email = ?';
    const rows = await query(sql, [email]);
    return rows[0] || null;
  }

  // 更新用户信息
  static async update(id, updates) {
    const allowedFields = ['real_name', 'avatar_url', 'email', 'phone', 'user_type'];
    const setClauses = [];
    const params = [];

    for (const [key, value] of Object.entries(updates)) {
      if (allowedFields.includes(key) && value !== undefined) {
        setClauses.push(`${key} = ?`);
        params.push(value);
      }
    }

    if (setClauses.length === 0) {
      return false;
    }

    params.push(id);
    const sql = `UPDATE users SET ${setClauses.join(', ')}, updated_at = NOW() WHERE id = ?`;
    
    const result = await query(sql, params);
    return result.affectedRows > 0;
  }

  // 更新密码
  static async updatePassword(id, newPassword) {
    const sql = 'UPDATE users SET password_hash = ?, updated_at = NOW() WHERE id = ?';
    const result = await query(sql, [newPassword, id]);
    return result.affectedRows > 0;
  }

  // 更新最后登录时间
  static async updateLastLogin(id) {
    const sql = 'UPDATE users SET last_login_at = NOW() WHERE id = ?';
    await query(sql, [id]);
  }

  // 检查用户名是否存在
  static async isUsernameExists(username, excludeId = null) {
    let sql = 'SELECT COUNT(*) as count FROM users WHERE username = ?';
    const params = [username];
    
    if (excludeId) {
      sql += ' AND id != ?';
      params.push(excludeId);
    }
    
    const rows = await query(sql, params);
    return rows[0].count > 0;
  }

  // 检查用户是否激活
  static isActive(user) {
    return user && user.status === 'active';
  }

  // 通用查询方法
  static async query(sql, params = []) {
    return await query(sql, params);
  }

  // 检查邮箱是否存在
  static async isEmailExists(email, excludeId = null) {
    if (!email) return false;
    
    let sql = 'SELECT COUNT(*) as count FROM users WHERE email = ?';
    const params = [email];
    
    if (excludeId) {
      sql += ' AND id != ?';
      params.push(excludeId);
    }
    
    const rows = await query(sql, params);
    return rows[0].count > 0;
  }

  // 检查手机号是否存在
  static async isPhoneExists(phone, excludeId = null) {
    if (!phone) return false;
    
    let sql = 'SELECT COUNT(*) as count FROM users WHERE phone = ?';
    const params = [phone];
    
    if (excludeId) {
      sql += ' AND id != ?';
      params.push(excludeId);
    }
    
    const rows = await query(sql, params);
    return rows[0].count > 0;
  }

  // 清理用户数据（移除敏感信息）
  static sanitize(user) {
    if (!user) return null;
    const { password_hash, ...sanitizedUser } = user;
    return sanitizedUser;
  }

  // 保存邮箱验证码
  static async saveVerificationCode(email, code, expiresAt) {
    const sql = `
      INSERT INTO email_verifications (email, code, expires_at, created_at)
      VALUES (?, ?, ?, NOW())
      ON DUPLICATE KEY UPDATE
      code = VALUES(code),
      expires_at = VALUES(expires_at),
      created_at = NOW()
    `;
    return await query(sql, [email, code, expiresAt]);
  }

  // 验证邮箱验证码
  static async verifyEmailCode(email, code) {
    const sql = `
      SELECT * FROM email_verifications 
      WHERE email = ? AND code = ? AND expires_at > NOW()
    `;
    const rows = await query(sql, [email, code]);
    return rows[0] || null;
  }

  // 删除邮箱验证码
  static async deleteVerificationCode(email, code) {
    const sql = 'DELETE FROM email_verifications WHERE email = ? AND code = ?';
    return await query(sql, [email, code]);
  }

  // 保存密码重置token
  static async savePasswordResetToken(userId, token, expiresAt) {
    const sql = `
      INSERT INTO password_resets (user_id, token, expires_at, created_at)
      VALUES (?, ?, ?, NOW())
      ON DUPLICATE KEY UPDATE
      token = VALUES(token),
      expires_at = VALUES(expires_at),
      created_at = NOW()
    `;
    return await query(sql, [userId, token, expiresAt]);
  }

  // 查找密码重置token
  static async findPasswordResetToken(token) {
    const sql = `
      SELECT * FROM password_resets 
      WHERE token = ? AND expires_at > NOW()
    `;
    const rows = await query(sql, [token]);
    return rows[0] || null;
  }

  // 删除密码重置token
  static async deletePasswordResetToken(token) {
    const sql = 'DELETE FROM password_resets WHERE token = ?';
    return await query(sql, [token]);
  }

  // 记录登录失败次数
  static async recordLoginFailure(identifier) {
    const sql = `
      INSERT INTO login_attempts (identifier, attempts, last_attempt, created_at)
      VALUES (?, 1, NOW(), NOW())
      ON DUPLICATE KEY UPDATE
      attempts = attempts + 1,
      last_attempt = NOW()
    `;
    return await query(sql, [identifier]);
  }

  // 获取登录失败次数
  static async getLoginAttempts(identifier) {
    const sql = `
      SELECT attempts, last_attempt FROM login_attempts 
      WHERE identifier = ? AND last_attempt > DATE_SUB(NOW(), INTERVAL 1 HOUR)
    `;
    const rows = await query(sql, [identifier]);
    return rows[0] || { attempts: 0 };
  }

  // 清除登录失败记录
  static async clearLoginAttempts(identifier) {
    const sql = 'DELETE FROM login_attempts WHERE identifier = ?';
    return await query(sql, [identifier]);
  }

  // 检查账户是否被锁定
  static async isAccountLocked(identifier) {
    const attempts = await this.getLoginAttempts(identifier);
    return attempts.attempts >= 5; // 5次失败后锁定
  }
}

module.exports = UserMySQL;