/**
   * updates existing public key
   *
   * @param publicKey key object
   */
  public static void updatePublicKey(PublicKey publicKey) {

    Connection con = null;
    try {
      con = DBUtils.getConn();
      PreparedStatement stmt =
          con.prepareStatement(
              "update public_keys set key_nm=?, type=?, fingerprint=?, public_key=?, profile_id=? where id=? and user_id=? and enabled=true");
      stmt.setString(1, publicKey.getKeyNm());
      stmt.setString(2, SSHUtil.getKeyType(publicKey.getPublicKey()));
      stmt.setString(3, SSHUtil.getFingerprint(publicKey.getPublicKey()));
      stmt.setString(4, publicKey.getPublicKey().trim());
      if (publicKey.getProfile() == null || publicKey.getProfile().getId() == null) {
        stmt.setNull(5, Types.NULL);
      } else {
        stmt.setLong(5, publicKey.getProfile().getId());
      }
      stmt.setLong(6, publicKey.getId());
      stmt.setLong(7, publicKey.getUserId());
      stmt.execute();
      DBUtils.closeStmt(stmt);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);
  }
  /**
   * inserts new public key
   *
   * @param publicKey key object
   */
  public static void insertPublicKey(PublicKey publicKey) {

    Connection con = null;
    try {
      con = DBUtils.getConn();
      PreparedStatement stmt =
          con.prepareStatement(
              "insert into public_keys(key_nm, type, fingerprint, public_key, profile_id, user_id) values (?,?,?,?,?,?)");
      stmt.setString(1, publicKey.getKeyNm());
      stmt.setString(2, SSHUtil.getKeyType(publicKey.getPublicKey()));
      stmt.setString(3, SSHUtil.getFingerprint(publicKey.getPublicKey()));
      stmt.setString(4, publicKey.getPublicKey().trim());
      if (publicKey.getProfile() == null || publicKey.getProfile().getId() == null) {
        stmt.setNull(5, Types.NULL);
      } else {
        stmt.setLong(5, publicKey.getProfile().getId());
      }
      stmt.setLong(6, publicKey.getUserId());
      stmt.execute();

      DBUtils.closeStmt(stmt);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);
  }
  /**
   * returns public key base on id
   *
   * @param con DB connection
   * @param publicKeyId key id
   * @return script object
   */
  public static PublicKey getPublicKey(Connection con, Long publicKeyId) {

    PublicKey publicKey = null;
    try {
      PreparedStatement stmt = con.prepareStatement("select * from  public_keys where id=?");
      stmt.setLong(1, publicKeyId);
      ResultSet rs = stmt.executeQuery();

      while (rs.next()) {
        publicKey = new PublicKey();
        publicKey.setId(rs.getLong("id"));
        publicKey.setKeyNm(rs.getString("key_nm"));
        publicKey.setPublicKey(rs.getString("public_key"));
        publicKey.setProfile(ProfileDB.getProfile(con, rs.getLong("profile_id")));
        publicKey.setType(rs.getString("type"));
        publicKey.setFingerprint(rs.getString("fingerprint"));
        publicKey.setCreateDt(rs.getTimestamp("create_dt"));
      }
      DBUtils.closeRs(rs);
      DBUtils.closeStmt(stmt);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }

    return publicKey;
  }
  /**
   * returns public keys based on sort order defined
   *
   * @param sortedSet object that defines sort order
   * @param userId user id
   * @return sorted script list
   */
  public static SortedSet getPublicKeySet(SortedSet sortedSet, Long userId) {

    ArrayList<PublicKey> publicKeysList = new ArrayList<PublicKey>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
      orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select * from public_keys where user_id = ? and enabled=true " + orderBy;

    Connection con = null;
    try {
      con = DBUtils.getConn();
      PreparedStatement stmt = con.prepareStatement(sql);
      stmt.setLong(1, userId);
      ResultSet rs = stmt.executeQuery();

      while (rs.next()) {
        PublicKey publicKey = new PublicKey();
        publicKey.setId(rs.getLong("id"));
        publicKey.setKeyNm(rs.getString("key_nm"));
        publicKey.setPublicKey(rs.getString("public_key"));
        publicKey.setProfile(ProfileDB.getProfile(con, rs.getLong("profile_id")));
        publicKey.setType(SSHUtil.getKeyType(publicKey.getPublicKey()));
        publicKey.setFingerprint(SSHUtil.getFingerprint(publicKey.getPublicKey()));
        publicKey.setCreateDt(rs.getTimestamp("create_dt"));
        publicKeysList.add(publicKey);
      }
      DBUtils.closeRs(rs);
      DBUtils.closeStmt(stmt);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);

    sortedSet.setItemList(publicKeysList);
    return sortedSet;
  }
  /**
   * checks if key has already been registered under user's profile
   *
   * @param userId user id
   * @param publicKey public key
   * @return true if duplicate
   */
  public static boolean isKeyRegistered(Long userId, PublicKey publicKey) {
    boolean isDuplicate = false;
    PreparedStatement stmt;
    Connection con = null;
    try {
      con = DBUtils.getConn();

      stmt =
          con.prepareStatement(
              "select * from public_keys where user_id=? and fingerprint like ? and profile_id is ? and id is not ?");
      stmt.setLong(1, userId);
      stmt.setString(2, SSHUtil.getFingerprint(publicKey.getPublicKey()));
      if (publicKey.getProfile() != null && publicKey.getProfile().getId() != null) {
        stmt.setLong(3, publicKey.getProfile().getId());
      } else {
        stmt.setNull(3, Types.NULL);
      }
      if (publicKey.getId() != null) {
        stmt.setLong(4, publicKey.getId());
      } else {
        stmt.setNull(4, Types.NULL);
      }

      ResultSet rs = stmt.executeQuery();
      if (rs.next()) {
        isDuplicate = true;
      }
      DBUtils.closeRs(rs);
      DBUtils.closeStmt(stmt);

    } catch (Exception ex) {
      log.error(ex.toString(), ex);
    }

    DBUtils.closeConn(con);

    return isDuplicate;
  }
  /**
   * select all unique public keys for user
   *
   * @param userId user id
   * @return public key list for user
   */
  public static List<PublicKey> getUniquePublicKeysForUser(Long userId) {

    Connection con = null;
    Map<String, PublicKey> keyMap = new LinkedHashMap();
    try {
      con = DBUtils.getConn();
      PreparedStatement stmt =
          con.prepareStatement(
              "select * from public_keys where user_id=? and enabled=true order by key_nm asc");
      stmt.setLong(1, userId);
      ResultSet rs = stmt.executeQuery();
      while (rs.next()) {

        PublicKey publicKey = new PublicKey();
        publicKey.setId(rs.getLong("id"));
        publicKey.setKeyNm(rs.getString("key_nm"));
        publicKey.setPublicKey(rs.getString("public_key"));
        publicKey.setProfile(ProfileDB.getProfile(con, rs.getLong("profile_id")));
        publicKey.setType(SSHUtil.getKeyType(publicKey.getPublicKey()));
        publicKey.setFingerprint(SSHUtil.getFingerprint(publicKey.getPublicKey()));
        publicKey.setCreateDt(rs.getTimestamp("create_dt"));
        keyMap.put(publicKey.getKeyNm() + " (" + publicKey.getFingerprint() + ")", publicKey);
      }
      DBUtils.closeRs(rs);
      DBUtils.closeStmt(stmt);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);

    return new ArrayList(keyMap.values());
  }
  /**
   * returns public keys based on sort order defined
   *
   * @param sortedSet object that defines sort order
   * @return sorted script list
   */
  public static SortedSet getPublicKeySet(SortedSet sortedSet) {

    ArrayList<PublicKey> publicKeysList = new ArrayList<PublicKey>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
      orderBy = " order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select p.*, u.username from public_keys p, users u where u.id=p.user_id  ";

    sql +=
        StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_USER_ID))
            ? " and p.user_id=? "
            : "";
    sql +=
        StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))
            ? " and p.profile_id=? "
            : "";
    sql +=
        StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_ENABLED))
            ? " and p.enabled=? "
            : " and p.enabled=true";
    sql = sql + orderBy;

    Connection con = null;
    try {
      con = DBUtils.getConn();
      PreparedStatement stmt = con.prepareStatement(sql);
      int i = 1;
      // set filters in prepared statement
      if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_USER_ID))) {
        stmt.setLong(i++, Long.valueOf(sortedSet.getFilterMap().get(FILTER_BY_USER_ID)));
      }
      if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID))) {
        stmt.setLong(i++, Long.valueOf(sortedSet.getFilterMap().get(FILTER_BY_PROFILE_ID)));
      }
      if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_ENABLED))) {
        stmt.setBoolean(i++, Boolean.valueOf(sortedSet.getFilterMap().get(FILTER_BY_ENABLED)));
      }
      ResultSet rs = stmt.executeQuery();

      while (rs.next()) {
        PublicKey publicKey = new PublicKey();
        publicKey.setId(rs.getLong("id"));
        publicKey.setKeyNm(rs.getString("key_nm"));
        publicKey.setPublicKey(rs.getString("public_key"));
        publicKey.setProfile(ProfileDB.getProfile(con, rs.getLong("profile_id")));
        publicKey.setType(SSHUtil.getKeyType(publicKey.getPublicKey()));
        publicKey.setFingerprint(SSHUtil.getFingerprint(publicKey.getPublicKey()));
        publicKey.setCreateDt(rs.getTimestamp("create_dt"));
        publicKey.setUsername(rs.getString("username"));
        publicKey.setUserId(rs.getLong("user_id"));
        publicKey.setEnabled(rs.getBoolean("enabled"));
        publicKeysList.add(publicKey);
      }
      DBUtils.closeRs(rs);
      DBUtils.closeStmt(stmt);

    } catch (Exception e) {
      log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);

    sortedSet.setItemList(publicKeysList);
    return sortedSet;
  }