package nxm.database.lib;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

import nxm.sys.inc.AsciiMap;
import nxm.sys.inc.Keyable;
import nxm.sys.lib.Table;

/** A collection of utility methods useful when working with files.

    @author  Pete Perrino
    @since   NeXtMidas 2.1.0
 */
public class ResultSetTable extends Table implements Keyable, AsciiMap, java.io.Serializable {
  /** Remove these characters from column names */
  public static String INVALID_CHARS = "()"; //jph -- remove these characters from column names
  /** NOTE: this should be changed if the structure/fields of this class changes. */
  private static final long serialVersionUID = -6340786352685911269L;

  /** This constructor was automatically provided by Java,
   *  but it is unneeded as all the methods are static methods.
   *  @deprecated now that Javadocs are required, it is temporarily
   *              being explicitely provided but deprecated
   */
  @Deprecated
  public ResultSetTable () {}

  /** Convert a database result set into a NeXtMidas table for a non Midas type 3000 file.
      @param rs The result set
      @return Table with values from the result set
   */
  public static Table fromResultSet(ResultSet rs) {
    return fromResultSet(rs, false);
  }

  /** Convert a database result set into a NeXtMidas table
      @param rs The result set
      @param type3000 Keep the output table compatible with a Midas 3000 file (flat)
      @return Table with values from the result set
   */
  public static Table fromResultSet(ResultSet rs, boolean type3000) {
    Table retVal = new Table();
    try {
      ResultSetMetaData md = rs.getMetaData();
      int cols = md.getColumnCount();
      int j = 1;
      while (rs.next()) {
        Table row = new Table();
        for (int i = 1; i <= cols; i++) {
          Table col = null;

          if (!type3000) {col = new Table();} // Must be flat for type 3000 compatibility
          String colName = md.getColumnName(i);
          colName = removeInvalidCharacters(colName);
          String colTypeName = md.getColumnTypeName(i).toUpperCase();
          int colType = md.getColumnType(i);
          if (row.get(colName.toUpperCase()) != null) {
            String tblName = md.getTableName(i);
            colName = tblName + "__" + colName;
            if(col != null)col.put("NAME", colName);
          } else {
            if(col != null)col.put("NAME", colName);
          }
          if(col != null)col.put("TYPE", colTypeName);
          switch (colType) {
            case java.sql.Types.INTEGER:
              int ival = rs.getInt(i);
              if (col == null) row.put(colName.toUpperCase(), ival); //type 3000 compatible
              else             col.put("VALUE", ival);
              break;
            case java.sql.Types.FLOAT:
              float fval = rs.getFloat(i);
              if (col == null) row.put(colName.toUpperCase(), fval); //type 3000 compatible
              else             col.put("VALUE", fval);
              break;
            case java.sql.Types.DECIMAL:
            case java.sql.Types.NUMERIC:
            case java.sql.Types.DOUBLE:
              double dval = rs.getDouble(i);
              if (col == null) row.put(colName.toUpperCase(), dval); //type 3000 compatible
              else             col.put("VALUE", dval);
              break;
            case java.sql.Types.CHAR:
            default:
              String sval = rs.getString(i);
            if (col == null) row.put(colName.toUpperCase(), sval); //type 3000 compatible
            else             col.put("VALUE", sval);
          }
          if (col != null)row.put(colName.toUpperCase(), col);
        }

        String rowName = "ROW" + j;
        retVal.put(rowName, row);
        j++;
      }
      if(!type3000)retVal.put("SIZE",--j);
    } catch (Exception e) {
      System.out.println("Caught exception in from ResultSetTable: " + e);
    }

    return (retVal);
  }

  /** Remove any invalid characters from a Table key */
  private static String removeInvalidCharacters(String in) {
    return in.replaceAll("//W","");
  }

}