const connection = require('../sql');

class UserService {
  // 用户登录
  async findUser(userName, userPwd, openId) {
    const statement =
      'SELECT id, open_id as openId, user_name as userName, nick_name as nickName, avatar FROM users WHERE (user_name = ? and user_pwd = ? and parent_id is null) or open_id = ? ;';
    const [result] = await connection.execute(statement, [userName, userPwd, openId]);
    return result[0];
  }
  // 子用户登录
  async findSubUser(userName, userPwd, openId) {
    const statement =
      'SELECT id, open_id as openId, user_name as userName, nick_name as nickName, avatar FROM users WHERE (user_name = ? and user_pwd = ?) or open_id = ? ;';
    const [result] = await connection.execute(statement, [userName, userPwd, openId]);
    return result[0];
  }
  // 用户注册
  async create(nickName, userName, userPwd, parentId = null) {
    const statement = 'INSERT INTO users (nick_name, user_name, user_pwd, parent_id) VALUES (?, ?, ?, ?);';
    const [result] = await connection.execute(statement, [nickName, userName, userPwd, parentId]);
    return result;
  }
  // 用户查找
  async search(userName) {
    const statement = 'select id, user_name as userName from users where user_name = ?;';
    const [result] = await connection.execute(statement, [userName]);
    return result[0];
  }
  // 个人信息
  async profile(userId) {
    const statement = 'select id as userId, user_name as userName, nick_name as nickName, avatar, created_at createdAt from users where id = ?;';
    const [result] = await connection.execute(statement, [userId]);
    return result[0];
  }
  // 绑定openId
  async bindOpenId({ id, openid, unionid, nickname, headimgurl }) {
    let statement = 'UPDATE users SET open_id = ?, union_id = ?, nick_name = ?,avatar=? WHERE id = ?;';
    const [result] = await connection.execute(statement, [openid, unionid, nickname, headimgurl, id]);
    return result;
  }
  // 更新用户
  async updateUserInfo(id, nickName, avatar) {
    const date = new Date();
    let statement = 'UPDATE users SET updated_at = ?';
    const params = [date];

    if (nickName) {
      statement += ', nick_name = ?';
      params.push(nickName);
    }
    if (avatar) {
      statement += ', avatar = ?';
      params.push(avatar);
    }
    statement += ' WHERE id = ?';
    params.push(id);

    const [result] = await connection.execute(statement, params);
    return result;
  }
  // 重置密码
  async resetPwd(userName, userPwd) {
    const statement = 'UPDATE users SET user_pwd = ? WHERE user_name = ?;';
    const [result] = await connection.execute(statement, [userPwd, userName]);
    return result;
  }

  // 验证旧密码
  async verifyOldPwd(userName, oldPwd) {
    const statement = 'SELECT id FROM users WHERE user_name = ? and user_pwd = ?;';
    const [result] = await connection.execute(statement, [userName, oldPwd]);
    return result[0];
  }

  // 用户注销
  async deleteUser(userId, userName) {
    const statement = 'DELETE FROM users WHERE id = ? and user_name = ?';
    const [result] = await connection.execute(statement, [userId, userName]);
    return result;
  }

  // 查询子用户列
  async getSubUsersCount(userId, keyword) {
    const statement = `SELECT count(id) FROM users WHERE parent_id = ? and (user_name like COALESCE(CONCAT('%',?,'%'), user_name) OR ? IS NULL) `;
    const [result] = await connection.execute(statement, [userId, keyword || null, keyword || null]);
    return result[0];
  }
  async getSubUsersList(userId, pageNum, pageSize, keyword) {
    const offset = (+pageNum - 1) * pageSize + '';
    const limit = pageSize;
    const statement = `SELECT 
      id, user_name as userName, nick_name as nickName, avatar, created_at as createdAt
    FROM users 
    WHERE 
      parent_id = ? and (user_name like COALESCE(CONCAT('%',?,'%'), user_name) OR ? IS NULL)
    ORDER BY updated_at DESC LIMIT ?,?;`;
    const [result] = await connection.execute(statement, [userId, keyword || null, keyword || null, offset, limit]);
    return result;
  }
  async deleteSubUser(id, parentId) {
    const statement = 'DELETE FROM users WHERE id = ? and parent_id = ?';
    const [result] = await connection.execute(statement, [id, parentId]);
    return result;
  }

  //addbywanghao  sso   20250512
  async findUserSso(userName) {
    const statement =
      'SELECT id, open_id as openId, user_name as userName, nick_name as nickName, avatar FROM users WHERE  user_name = ? and parent_id is null ;';
    const [result] = await connection.execute(statement, [userName]);
    return result[0];
  }
}
module.exports = new UserService();