/**
  * Used for text output
  *
  * @param rList
  */
 public static void updateColumnWidth(ResultList rList) {
   if (rList == null || rList.getColumnDescriptor() == null || rList.getRows().size() == 0) return;
   List<ColumnInfo> cols = rList.getColumnDescriptor().getColumns();
   for (ColumnInfo col : cols) {
     col.setMaxLength(col.getName().length());
   }
   for (ResultRow row : rList.getRows()) {
     List<String> l = row.getColumns();
     for (int i = 0; i < l.size(); i++) {
       String s = l.get(i);
       if (s != null && s.length() > cols.get(i).getMaxLength())
         cols.get(i).setMaxLength(s.length());
     }
   }
 }
  /**
   * 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();
  }
  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();
  }
  /**
   * 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();
  }
  /**
   * 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();
  }
  public static String toMultiListJSONString(
      LinkedHashMap<String, ResultList> resultMap,
      QueryParameters qps,
      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
    if (qps != null) {
      sb.append(",\"request\":{");
      sb.append("\"group\":\"").append(qps.getGroup()).append("\"");
      sb.append(",\"host\":\"").append(qps.getHost()).append("\"");
      for (Map.Entry<String, String> e : qps.getSqlParams().entrySet()) {
        sb.append(",\"").append(e.getKey()).append("\":\"").append(e.getValue()).append("\"");
      }
      sb.append("}");
    }
    // for(String key: resultMap.keySet())
    for (Map.Entry<String, ResultList> e : resultMap.entrySet()) {
      String key = e.getKey();
      ResultList rs = e.getValue();
      List<ColumnInfo> cols = rs.getColumnDescriptor().getColumns();

      sb.append(",\"").append(key).append("\":{");
      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 (!first) sb.append(",");
          first = false;
          sb.append("\"").append(cols.get(i).getName() + "\"");
        }
      }
      sb.append("],");
      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 (!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 sb.append(escapeJson(row.getColumns().get(i)));
          } else sb.append("\"").append(escapeJson(row.getColumns().get(i))).append("\"");
          first = false;
        }
        sb.append("}");
        firstRow = false;
      }
      sb.append("]}");
    }
    sb.append("}}");

    return sb.toString();
  }
  public static ResultList paserBindList(ResultList rList) {
    if (rList != null && rList.getRows().size() > 0) {
      logger.fine("BindController returns " + rList.getRows().size());
      ResultList newList = new ResultList();
      ColumnDescriptor desc = new ColumnDescriptor();
      for (ColumnInfo col : rList.getColumnDescriptor().getColumns()) {
        if ("binds_xml".equalsIgnoreCase(col.getName())) continue;
        desc.addColumn(col.getName(), col.isNumberType(), desc.getColumns().size());
      }
      desc.addColumn("NAME", false, desc.getColumns().size());
      desc.addColumn("POS", false, desc.getColumns().size());
      desc.addColumn("DTYSTR", false, desc.getColumns().size());
      desc.addColumn("MAXLEN", false, desc.getColumns().size());
      desc.addColumn("LEN", false, desc.getColumns().size());
      desc.addColumn("VALUE", false, desc.getColumns().size());
      newList.setColumnDescriptor(desc);

      int idx = rList.getColumnDescriptor().getColumnIndex("BINDS_XML");
      XMLInputFactory inputFactory = XMLInputFactory.newInstance();
      for (ResultRow row : rList.getRows()) {
        // logger.info("binds_xml.idx="+idx+", "+ row.getColumns().size());
        List<BindVariable> binds = null;
        try {
          String bindXml = row.getColumns().get(idx);
          binds = parseBinds(bindXml, inputFactory);
        } catch (Exception iex) {
        }
        if (binds == null || binds.size() == 0) {
          ResultRow newRow = new ResultRow();
          newRow.setColumnDescriptor(desc);
          List<String> cols = new java.util.ArrayList<String>(row.getColumns().size() + 5);
          for (int i = 0; i < row.getColumns().size(); i++) {
            if (i == idx) continue;
            cols.add(row.getColumns().get(i));
          }
          cols.add(null);
          cols.add(null);
          cols.add(null);
          cols.add(null);
          cols.add(null);
          cols.add(null);
          newRow.setColumns(cols);
          newList.addRow(newRow);
        } else {
          for (BindVariable var : binds) {
            ResultRow newRow = new ResultRow();
            newRow.setColumnDescriptor(desc);
            List<String> cols = new java.util.ArrayList<String>(row.getColumns().size() + 5);
            for (int i = 0; i < row.getColumns().size(); i++) {
              if (i == idx) continue;
              cols.add(row.getColumns().get(i));
            }
            cols.add(var.name);
            cols.add(var.pos);
            cols.add(var.dtystr);
            cols.add(var.maxLen);
            cols.add(var.len);
            cols.add(var.value);
            newRow.setColumns(cols);
            newList.addRow(newRow);
          }
        }
      }
      return newList;
    }
    return null;
  }