/**
   * Display diff data by key. Metrics will be displayed as {metric}_a and {metric}_b
   *
   * @param rsA
   * @param rsB
   * @param qpsA
   * @param qpsB
   * @param status
   * @param message
   * @return
   */
  public static String toJSONDiffFlatString(
      boolean diffOnly,
      Sql sqlA,
      Sql sqlB,
      ResultList rsA,
      ResultList rsB,
      QueryParameters qpsA,
      QueryParameters qpsB,
      int status,
      String message) {
    ResultList combined = ResultListMerger.mergeByKey(sqlA, sqlB, rsA, rsB, diffOnly);
    StringBuilder sb = new StringBuilder();
    sb.append("{\"resp\":{\"status\":").append(status); // start and status
    sb.append(",\"message\":\"").append(escapeJson(message)).append("\""); // message line

    // requests
    sb.append(",\"request\":{");
    if (qpsA != null) // parameter from a
    {
      sb.append("\"a_group\":\"").append(qpsA.getGroup()).append("\"");
      sb.append(",\"a_host\":\"").append(qpsA.getHost()).append("\"");
      // sb.append(",\"port\":\"").append(qps.getPort()).append("\"");
      sb.append(",\"a_sql\":\"").append(qpsA.getSql()).append("\"");
      for (Map.Entry<String, String> e : qpsA.getSqlParams().entrySet()) {
        sb.append(",\"a_").append(e.getKey()).append("\":\"").append(e.getValue()).append("\"");
      }
      if (qpsB != null) sb.append(",");
    }
    if (qpsB != null) // parameter from b
    {
      sb.append("\"b_group\":\"").append(qpsB.getGroup()).append("\"");
      sb.append(",\"b_host\":\"").append(qpsB.getHost()).append("\"");
      // sb.append(",\"port\":\"").append(qps.getPort()).append("\"");
      sb.append(",\"b_sql\":\"").append(qpsB.getSql()).append("\"");
      for (Map.Entry<String, String> e : qpsB.getSqlParams().entrySet()) {
        sb.append(",\"b_").append(e.getKey()).append("\":\"").append(e.getValue()).append("\"");
      }
    }
    sb.append("}\r\n"); // end of request

    int cnt = combined != null ? combined.getRows().size() : 0;
    List<ColumnInfo> cols = combined != null ? combined.getColumnDescriptor().getColumns() : null;

    sb.append(",\"results\":{");
    sb.append("\"total\":\"").append(cnt).append("\","); // end of count
    // column names
    sb.append("\"columns\":["); // start of column names
    if (cols != null) {
      boolean first = true;
      for (int i = 0; i < cols.size(); i++) {
        if (!first) sb.append(",");
        else first = false;
        sb.append("\"").append(cols.get(i).getName() + "\"");
      }
    }
    sb.append("],\r\n"); // end of column names
    boolean firstRow = true;
    sb.append("\"results\":["); // start of the result list
    if (combined != null)
      for (ResultRow row : combined.getRows()) {
        if (!firstRow) sb.append(",");
        int len = row.getColumns().size();
        boolean first = true;
        sb.append("{"); // start of one row
        for (int i = 0; i < len; i++) {
          if (!first) sb.append(",");
          sb.append("\"").append(escapeJson(cols.get(i).getName())).append("\":");
          if (cols.get(i).isNumberType()) {
            if (row.getColumns().get(i) == null || row.getColumns().get(i).trim().length() == 0)
              sb.append("\"\"");
            else {
              if (row.getColumns().get(i).startsWith("-."))
                sb.append(row.getColumns().get(i).replace("-.", "-0."));
              else sb.append(escapeJson(row.getColumns().get(i)));
            }
          } else sb.append("\"").append(escapeJson(row.getColumns().get(i))).append("\"");
          first = false;
        }
        sb.append("}\r\n"); // end of one row
        firstRow = false;
      }
    sb.append("]"); // end of result list
    sb.append("}"); // end of outer results
    sb.append("}"); // end of resp
    sb.append("}"); // end of all

    return sb.toString();
  }
  /**
   * We will filter data by the provided columns. Also make all column names in uppercase. If no
   * filter provided, only make column names uppercase
   *
   * @param rs
   * @param qps
   * @param status
   * @param message
   * @param subset
   * @return
   */
  public static String toJSONStringSubset(
      ResultList rs, QueryParameters qps, int status, String message, String[] subset) {
    HashSet<String> ms = new HashSet<String>();
    if (subset != null) for (int i = 0; i < subset.length; i++) ms.add(subset[i]);
    boolean filter = ms.size() > 0;

    StringBuilder sb = new StringBuilder();
    sb.append("{\"resp\":{\"status\":").append(status); // start and status
    if (rs != null && rs.getTotalResponseTime() > 0) {
      sb.append(",\"totalTime\":\"").append(rs.getTotalResponseTime()).append("ms\"");
      sb.append(",\"execTime\":\"").append(rs.getTotalExecutionTime()).append("ms\"");
      sb.append(",\"fetchTime\":\"").append(rs.getTotalFetchTime()).append("ms\"");
    }
    sb.append(",\"message\":\"").append(escapeJson(message)).append("\""); // message line

    // requests
    if (qps != null) {
      sb.append(",\"request\":{");
      sb.append("\"group\":\"").append(qps.getGroup()).append("\"");
      sb.append(",\"host\":\"").append(qps.getHost()).append("\"");
      // sb.append(",\"port\":\"").append(qps.getPort()).append("\"");
      sb.append(",\"sql\":\"").append(qps.getSql()).append("\"");
      for (Map.Entry<String, String> e : qps.getSqlParams().entrySet()) {
        sb.append(",\"").append(e.getKey()).append("\":\"").append(e.getValue()).append("\"");
      }
      sb.append("}\r\n");
    }

    if (rs != null) {
      List<ColumnInfo> cols = rs.getColumnDescriptor().getColumns();

      sb.append(",\"results\":{");
      sb.append("\"total\":\"").append(rs.getRows().size()).append("\",");
      // column names
      sb.append("\"columns\":[");
      {
        boolean first = true;
        for (int i = 0; i < cols.size(); i++) {
          if (filter && !ms.contains(cols.get(i).getName())) continue;
          if (!first) sb.append(",");
          first = false;
          sb.append("\"").append(cols.get(i).getName().toUpperCase() + "\"");
        }
      }
      sb.append("],\r\n");
      boolean firstRow = true;
      sb.append("\"results\":[");
      for (ResultRow row : rs.getRows()) {
        if (!firstRow) sb.append(",");
        int len = row.getColumns().size();
        boolean first = true;
        sb.append("{");
        for (int i = 0; i < len; i++) {
          if (filter && !ms.contains(cols.get(i).getName())) continue;
          if (!first) sb.append(",");
          sb.append("\"").append(escapeJson(cols.get(i).getName().toUpperCase())).append("\":");
          if (cols.get(i).isNumberType()) {
            if (row.getColumns().get(i) == null || row.getColumns().get(i).trim().length() == 0)
              sb.append("\"\"");
            else sb.append(escapeJson(row.getColumns().get(i)));
          } else sb.append("\"").append(escapeJson(row.getColumns().get(i))).append("\"");
          first = false;
        }
        sb.append("}\r\n");
        firstRow = false;
      }
      sb.append("]}");
    }
    sb.append("}}");
    return sb.toString();
  }
  public static String toJSONDiffListString(
      ResultList rsA,
      ResultList rsB,
      QueryParameters qpsA,
      QueryParameters qpsB,
      int status,
      String message) {
    StringBuilder sb = new StringBuilder();
    sb.append("{\"resp\":{\"status\":").append(status); // start and status
    sb.append(",\"message\":\"").append(escapeJson(message)).append("\""); // message line

    // requests
    sb.append(",\"request\":{");
    if (qpsA != null) {
      sb.append("\"a_group\":\"").append(qpsA.getGroup()).append("\"");
      sb.append(",\"a_host\":\"").append(qpsA.getHost()).append("\"");
      // sb.append(",\"port\":\"").append(qps.getPort()).append("\"");
      sb.append(",\"a_sql\":\"").append(qpsA.getSql()).append("\"");
      for (Map.Entry<String, String> e : qpsA.getSqlParams().entrySet()) {
        sb.append(",\"a_").append(e.getKey()).append("\":\"").append(e.getValue()).append("\"");
      }
      if (qpsB != null) sb.append(",");
    }
    if (qpsB != null) {
      sb.append("\"b_group\":\"").append(qpsB.getGroup()).append("\"");
      sb.append(",\"b_host\":\"").append(qpsB.getHost()).append("\"");
      // sb.append(",\"port\":\"").append(qps.getPort()).append("\"");
      sb.append(",\"b_sql\":\"").append(qpsB.getSql()).append("\"");
      for (Map.Entry<String, String> e : qpsB.getSqlParams().entrySet()) {
        sb.append(",\"b_").append(e.getKey()).append("\":\"").append(e.getValue()).append("\"");
      }
    }
    sb.append("}\r\n");

    int cnt = 0;
    List<ColumnInfo> cols = null;
    if (rsA != null || rsB != null) {
      if (rsA != null) {
        cnt += rsA.getRows().size();
        cols = rsA.getColumnDescriptor().getColumns();
      }
      if (rsB != null) {
        cnt += rsB.getRows().size();
        if (cols == null) cols = rsB.getColumnDescriptor().getColumns();
      }

      sb.append(",\"results\":{");
      sb.append("\"total\":\"").append(cnt).append("\",");
      // column names
      sb.append("\"columns\":[\"DB\"");
      {
        for (int i = 0; i < cols.size(); i++) {
          sb.append(",");
          sb.append("\"").append(cols.get(i).getName() + "\"");
        }
      }
      sb.append("],\r\n");
      sb.append("\"results\":[");
    }

    boolean firstRow = true;
    if (rsA != null) {
      for (ResultRow row : rsA.getRows()) {
        if (!firstRow) sb.append(",");
        int len = row.getColumns().size();
        sb.append("{\"DB\":\"A\"");
        for (int i = 0; i < len; i++) {
          sb.append(",");
          sb.append("\"").append(escapeJson(cols.get(i).getName())).append("\":");
          if (cols.get(i).isNumberType()) {
            if (row.getColumns().get(i) == null || row.getColumns().get(i).trim().length() == 0)
              sb.append("\"\"");
            else sb.append(escapeJson(row.getColumns().get(i)));
          } else sb.append("\"").append(escapeJson(row.getColumns().get(i))).append("\"");
        }
        sb.append("}\r\n");
        firstRow = false;
      }
    }
    if (rsB != null) {
      for (ResultRow row : rsB.getRows()) {
        if (!firstRow) sb.append(",");
        int len = row.getColumns().size();
        sb.append("{\"DB\":\"B\"");
        for (int i = 0; i < len; i++) {
          sb.append(",");
          sb.append("\"").append(escapeJson(cols.get(i).getName())).append("\":");
          if (cols.get(i).isNumberType()) {
            if (row.getColumns().get(i) == null || row.getColumns().get(i).trim().length() == 0)
              sb.append("\"\"");
            else sb.append(escapeJson(row.getColumns().get(i)));
          } else sb.append("\"").append(escapeJson(row.getColumns().get(i))).append("\"");
        }
        sb.append("}\r\n");
        firstRow = false;
      }
    }
    if (rsA != null || rsB != null) sb.append("]}");
    sb.append("}}");
    return sb.toString();
  }
  /**
   * JSON Output metrics with multiple rows, with each row represent different entity such as disk,
   * username, table anme, etc.
   *
   * @param rs
   * @param keyColumn
   * @param groupByColumns columns used to group metrics, such as ["SNAP_ID", "TS"]
   * @param metricName we only handle a single metric for now
   * @param qps
   * @param status
   * @param message
   * @return
   */
  public static String toMetricsJSONStringWithMultiRowsKeys(
      ResultList rs,
      String keyColumn,
      String[] groupByColumns,
      String metricName,
      QueryParameters qps,
      int status,
      String message) {
    String[] keys = getDistinctKeys(keyColumn, rs);
    if (keys == null
        || keys.length == 0
        || groupByColumns == null
        || groupByColumns.length
            == 0) // we don't want to bother about the case without multiple key values
    return toJSONString(rs, qps, status, message);

    Map<String, String> keyMap = new HashMap<String, String>(keys.length);
    for (int i = 0; i < keys.length; i++) keyMap.put(keys[i], "k" + i);

    StringBuilder sb = new StringBuilder();
    sb.append("{\"resp\":{\"status\":").append(status); // start and status
    if (rs != null && rs.getTotalResponseTime() > 0) {
      sb.append(",\"totalTime\":\"").append(rs.getTotalResponseTime()).append("ms\"");
      sb.append(",\"execTime\":\"").append(rs.getTotalExecutionTime()).append("ms\"");
      sb.append(",\"fetchTime\":\"").append(rs.getTotalFetchTime()).append("ms\"");
    }
    sb.append(",\"message\":\"").append(escapeJson(message)).append("\""); // message line

    // requests
    if (qps != null) {
      sb.append(",\"request\":{");
      sb.append("\"group\":\"").append(qps.getGroup()).append("\"");
      sb.append(",\"host\":\"").append(qps.getHost()).append("\"");
      // sb.append(",\"port\":\"").append(qps.getPort()).append("\"");
      sb.append(",\"sql\":\"").append(qps.getSql()).append("\"");
      for (Map.Entry<String, String> e : qps.getSqlParams().entrySet()) {
        sb.append(",\"").append(e.getKey()).append("\":\"").append(e.getValue()).append("\"");
      }
      sb.append("}\r\n");
    }

    if (rs != null) {
      List<ColumnInfo> cols = rs.getColumnDescriptor().getColumns();

      sb.append(",\"results\":{");
      sb.append("\"total\":\"").append(rs.getRows().size()).append("\",");
      // column names
      sb.append("\"columns\":[");
      {
        boolean first = true;
        for (int i = 0; i < cols.size(); i++) {
          // ignore key column
          if (keyColumn.equalsIgnoreCase(cols.get(i).getName())) continue;

          if (!first) sb.append(",");
          first = false;
          sb.append("\"").append(cols.get(i).getName() + "\"");
        }
      }
      sb.append("],\r\n");
      // add keys
      sb.append("\"keys\":[");
      for (int i = 0; i < keys.length; i++) {
        if (i > 0) sb.append(",");
        sb.append("{")
            .append("\"name\":\"")
            .append(keys[i])
            .append("\", ")
            .append("\"shortName\":\"")
            .append("k")
            .append(i)
            .append("\"")
            .append("}");
      }
      sb.append("],\r\n");
      if (rs.getCustomObjects() != null && rs.getCustomObjects().size() > 0) {
        for (Map.Entry<String, CustomResultObject> e : rs.getCustomObjects().entrySet()) {
          sb.append("\"").append(e.getKey()).append("\":");
          sb.append(e.getValue().getValueJsonString());
          sb.append(",\r\n");
        }
      }
      boolean firstRow = true;
      String[] prevGrpKey = new String[groupByColumns.length];
      String[] newGrpKey = new String[groupByColumns.length];
      int keyIdx = rs.getColumnIndex(keyColumn);
      int mtrIdx = rs.getColumnIndex(metricName);
      int[] grpKeyIdx = new int[groupByColumns.length];
      for (int i = 0; i < groupByColumns.length; i++) {
        prevGrpKey[i] = null;
        newGrpKey[i] = null;
        grpKeyIdx[i] = rs.getColumnIndex(groupByColumns[i]); // don't expect invalid
      }
      sb.append("\"results\":[");
      Map<String, String> valueMap = new TreeMap<String, String>(); // to store values temporarily
      for (ResultRow row : rs.getRows()) {
        for (int i = 0; i < groupByColumns.length; i++) {
          newGrpKey[i] = row.getColumns().get(grpKeyIdx[i]);
        }
        // if we have a new group by key, we should generate output for previous one
        // and clear the temp storage
        if (valueMap.size() > 0 && isDiff(prevGrpKey, newGrpKey)) {
          if (!firstRow) sb.append(",");
          firstRow = false;
          sb.append("{");
          for (int i = 0; i < groupByColumns.length; i++) {
            sb.append("\"")
                .append(groupByColumns[i])
                .append("\":")
                .append(prevGrpKey[i])
                .append(",");
          }
          sb.append("\"").append(metricName).append("\":{");
          boolean firstKey = true;
          for (Map.Entry<String, String> e : valueMap.entrySet()) {
            if (e.getValue() == null) continue;
            String jkey = keyMap.get(e.getKey());
            if (!firstKey) sb.append(",");
            firstKey = false;
            sb.append("\"").append(jkey).append("\":").append(e.getValue());
          }
          sb.append("}"); // end metric
          sb.append("}"); // end the row
          valueMap.clear();
        }
        // otherwise, just store it
        valueMap.put(row.getColumns().get(keyIdx), row.getColumns().get(mtrIdx));
        for (int i = 0; i < groupByColumns.length; i++) {
          prevGrpKey[i] = newGrpKey[i];
        }
      }
      // todo: output last group
      if (!firstRow) sb.append(",");
      {
        sb.append("{");
        for (int i = 0; i < groupByColumns.length; i++) {
          sb.append("\"").append(groupByColumns[i]).append("\":").append(newGrpKey[i]).append(",");
        }
        sb.append("\"").append(metricName).append("\":{");
        boolean firstKey = true;
        for (Map.Entry<String, String> e : valueMap.entrySet()) {
          if (e.getValue() == null) continue;
          String jkey = keyMap.get(e.getKey());
          if (!firstKey) sb.append(",");
          firstKey = false;
          sb.append("\"").append(jkey).append("\":").append(e.getValue());
        }
        sb.append("}"); // end metric
        sb.append("}"); // end the row
      }
      sb.append("]}");
    }
    sb.append("}}");
    return sb.toString();
  }