package nxm.database.prim;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;

import nxm.sys.inc.InternalUseOnly;
import nxm.sys.intr.Debug;
import nxm.sys.lib.Convert;
import nxm.sys.lib.Message;
import nxm.sys.lib.MidasException;
import nxm.sys.lib.Primitive;
import nxm.sys.lib.Table;

/** NeXtMidas interface to JDBC

    created: February 9, 2005
    @author wgdenge
    @author Jay P. Hallam
*/
public class nmjdbc extends Primitive {
  private  Connection  dcon;
  private  Driver      myDriver;
  private  String      node      = null;
  private  String      dBase     = null;
  private  String      user      = null;
  private  String      pWord     = null;
  private  String      ddriver   = "org.gjt.mm.mysql.Driver";                                  //MSZ ADD
  private  String      ddbport   = "1521";                                                     //MSZ ADD
  private  String      dconnect  = "jdbc:mysql://";                                            //MSZ ADD
  private  String      driver, dbport, connectstring;                                          //MSZ
  private  boolean     checkAPIUpdate = false;
  private  Table       apiUpdateInfo;

  /** default constructor */
  public nmjdbc () {}

  public int open () {
      Table config = MA.getTable("CFG");
      node = config.getS("DBNODE"); //MA.getS("NODE");                                         // Node JDBC is running on
      dBase = config.getS("DBNAME");//MA.getS("DBASE");                                        // The database to connect to..
      user = config.getS("DBACCOUNT");//MA.getS("USER");                                       // Username for table
      pWord = config.getS("DBPASSWORD", user);//MA.getS("PWORD");                              // Password (Changed in 2.9.1 for Bug 2322)
      driver = config.getS("DBDRIVER");//MA.getS("/DRIVER");                                   // Driver //MSZ ADD
      dbport = config.getS("DBPORT");//MA.getS("/PORT");                                       // Port //MSZ ADD
      connectstring = config.getS("DBCONNECT");//MA.getS("/CONNECT");                          //connection string /
      dBase = dBase.toLowerCase();                                                             // Convert all to LOWERcase
      user = user.toLowerCase();                                                               // " "
      pWord = pWord.toLowerCase();                                                             // " "
      connectDB();
      return NORMAL;
  }
  public int close () {
      return NORMAL;
  }

  /** Calls {@link #isConnected()} to determine database connection status.
      @return true if connected to database, otherwise false
   */
  public boolean connected () {
    return isConnected();
  }

  /** Determine database connection status.
      @return true if connected to database, otherwise false
  */
  public boolean isConnected () {
    return (dcon != null);
  }

  private void connectDB () {
    String srcStr = null;
    try {
      if (connectstring.indexOf("oracle") >= 0) {                                          //MSZ ADD
        if (node.length() >=1) {
          srcStr = connectstring + user + "/" + pWord + "@" + node + ":" + dbport + ":" + dBase;
        }
        else {
          srcStr = connectstring + user + "/" + pWord + "@" + dBase; // node not specified (BUG 2220)
        }
      }                                                                                    //MSZ ADD
      else {
        srcStr = connectstring + node + "/" + dBase + "?user=" + user + "&password=" + pWord;
      }                                                                                    //MSZ MOD
      if ((M.debug & Debug.PRIMITIVE) != 0) {
        M.info("SQL STR is " + srcStr);
      }
      myDriver = (Driver) Class.forName(driver).getConstructor().newInstance(); // updated in 3.9.0 to use constructor
    } catch (Exception e) {
      throw new MidasException("Unable to connect", e);
    }
    boolean connected = false;
    while (!connected) {
      try {
        M.info("" + srcStr);
        dcon = DriverManager.getConnection(srcStr);
        connected = true;
        M.info("Connected to " + srcStr);
      } catch (SQLException sqlex) {
        M.info("Unable to connect " + sqlex);
      }
      if (!connected) {
        try {
          Thread.sleep(10000);
        } catch (Exception e) {}
      }
    }
    M.info("Connected to " + dcon + " " + dBase);
  }

  public synchronized int processMessage (Message msg) {                                       // Process MESSAGE
    if (!thisIsMe()) {
      MQ.put(msg);
      return 0;
    }                                                                                        // If our message, ignore
    if (msg.name.startsWith("QUERY")) {                                                      // If PSD Request
//           doQuery((String) msg.data);      //jph                                              // Goto Routine...?.
      doQuery((String) msg.data, msg.from);                                                 // Goto Routine...?.
    } else if (msg.name.startsWith("INSERT")) {
      doInsert(Convert.o2t(msg.data));
    } else if (msg.name.startsWith("DELETE")) {
      doDelete(Convert.o2t(msg.data));
    } else if (msg.name.startsWith("SSQL")) {                                                //MSZ ADD
      simpleQuery(Convert.o2s(msg.data));                                                      //MSZ ADD
    } else if (msg.name.startsWith("UPDATE")) {
      doUpdate(Convert.o2t(msg.data));
    }
    return NORMAL;
  }

  /** Run a DB update query with a Midas Table
   *  @param inTable the Midas Table
   */
  public void doUpdate (Table inTable) {
    String   dbName    = inTable.getS("DB");
    Table    nameVals  = inTable.getTable("DATA");
    Table    selVals   = inTable.getTable("SEL");
    if ((dbName == null) || (nameVals == null) || (selVals == null)) {
      M.error("NMJDBC::doUpdate : Badly formed table");
      return;
    }
    String str = "update " + dbName + " set ";
    for (Enumeration<String> e = nameVals.keys(); e.hasMoreElements(); ) {
      String key = e.nextElement();
      str = str + key + "='" + nameVals.getS(key) + "',";
    }
    int loc = str.lastIndexOf(",");
    if (loc == -1) {
      M.info("NMJDBC:: doUpdate, unknown failure, got -1 on indexof()");
      return;
    }
    str = str.substring(0, loc);
    str = str + " where ";
    for (Enumeration<String> e = selVals.keys(); e.hasMoreElements(); ) {
      String key = e.nextElement();
      str = str + key + "='" + selVals.getS(key) + "' and ";
    }
    loc = str.lastIndexOf(" and ");
    if (loc == -1) {
      M.info("NMJDBC:: doUpdate, unknown failure, got -1 on indexof()");
      return;
    }
    str = str.substring(0, loc);
//        str = str + ";";
    if ((M.debug & Debug.PRIMITIVE) != 0) {
      M.info("SQL Str is " + str);
    }

    boolean  done      = false;
    int      cmax      = 0;
    while (!done) {
      Statement  statement;
      ResultSet  resultset;
      try {
        statement = dcon.createStatement();                                              // Create a NEW Statment
        resultset = statement.executeQuery(str);                                         // Submit the QUERY String
        statement.close();                                                               // Finish off
        done = true;
      } catch (SQLException sqlex) {                                                       // Get ANY Failures
        M.info("FAIL:Exception Opening Database");
        M.info("SQL State = " + sqlex.getSQLState());
      }
      cmax++;
      if (cmax > 4) {
        done = true;
      }
    }
  }

  /** Run a DB delete query with a Midas Table
   *  @param inTable the Midas Table
   */
  public void doDelete (Table inTable) {
    M.info("Doing Delete " + inTable);
    Table  geos   = inTable.getTable("PERMGEOS");
    Table  combs  = inTable.getTable("PERMCMBS");

    if (geos != null) {
      String  pkeys  = new String("(");
      for (String key : geos.getKeys()) {
        pkeys = pkeys + "'" + (String) geos.get(key) + "'";
        pkeys = pkeys + ",";
      }
      pkeys = pkeys.substring(0, pkeys.length() - 1) + ")";
      if (checkAPIUpdate) apiUpdateInfo.put("DODELETE_GEOS", pkeys);
      simpleQuery("delete from measurements where pkey in " + pkeys);
      simpleQuery("delete from geos where felocation in " + pkeys);
    }

    if (combs != null) {
      String  pkeys  = new String("(");
      for (String key : combs.getKeys()) {
        pkeys = pkeys + "'" + (String) combs.get(key) + "'";
        pkeys = pkeys + ",";
      }
      pkeys = pkeys.substring(0, pkeys.length() - 1) + ")";
      if (checkAPIUpdate) apiUpdateInfo.put("DODELETE_COMBS", pkeys);
      simpleQuery("delete from measurements where pkey in " + pkeys);
      simpleQuery("delete from combs where resfile in " + pkeys);
    }
  }

  private void simpleQuery (String str) {
    Statement  statement;
    try {
      statement = dcon.createStatement();                                                  // Create a NEW Statment
      int  num  = statement.executeUpdate(str);                                            // Submit the QUERY String
      statement.close();                                                                   // Finish off
    } catch (SQLException sqlex) {                                                           // Get ANY Failures
    }
  }

  /** Run a DB insert query with a Midas Table
   *  @param inTable the Midas Table
   */
  public void doInsert (Table inTable) {
    String     dbName     = inTable.getS("DB");
    Table      nameVals   = inTable.getTable("DATA");
    if ((dbName == null) || (nameVals == null)) {
      M.error("NMJDBC::doInsert : Badly formed table");
      return;
    }
    String     str        = new String("insert into " + dbName + " (");
    for (Enumeration<String> e = nameVals.keys(); e.hasMoreElements(); ) {
      str = str + e.nextElement() + ",";
    }

    int        loc        = str.lastIndexOf(",");
    if (loc == -1) {
      M.info("NMJDBC:: doInsert, unknown failure, got -1 on indexof()");
      return;
    }
    str = str.substring(0, loc);
    str = str + ") VALUES (";
    for (Enumeration<String> e = nameVals.keys(); e.hasMoreElements(); ) {
      String  key  = e.nextElement();
      str = str + "'" + nameVals.getS(key) + "'" + ",";
    }

    //System.out.println("2->"+str);
    loc = str.lastIndexOf(",");
    if (loc == -1) {
      M.info("NMJDBC:: doInsert, unknown failure, got -1 on indexof()");
      return;
    }
    str = str.substring(0, loc);
    str = str + ")";

    Statement  statement;
    ResultSet  resultset;
    try {
      statement = dcon.createStatement();                                                  // Create a NEW Statment
      resultset = statement.executeQuery(str);                                             // Submit the QUERY String
      statement.close();                                                                   // Finish off
    } catch (SQLException sqlex) {                                                           // Get ANY Failures
      sqlex.printStackTrace();
      M.info("SQL State = " + sqlex.getSQLState());
      M.info("SQL Error Code = " + sqlex.getErrorCode());
      M.info("FAIL:Exception Inserting...");
    }
  }

  /** Calls {@link #doQuery(String, Object)} to run any given query and update the message queue
   *  @param query the query to be performed
   */
  public void doQuery (String query) {  //jph
    doQuery(query, null);              //jph
  }                                    //jph
//    public void doQuery(String query) {
  /** Run any given query and update the message queue
   *  @param query the query to be performed
   *  @param from the target destination of the message
   */
  public void doQuery(String query, Object from) {  //jph
    Statement  statement;
    ResultSet  resultset;

    if ((M.debug & Debug.PRIMITIVE) != 0) {
      M.info("Query is " + query);
    }

    try {
      statement = dcon.createStatement();                                                  // Create a NEW Statment
      resultset = statement.executeQuery(query);                                           // Submit the QUERY String
    } catch (SQLException sqlex) {                                                           // Get ANY Failures
      sqlex.printStackTrace();
      M.info("doQuery FAIL:Exception Opening Database");
      return;
    }
    Table      data       = new Table();
    int        row        = 1;
    try {
      if (!resultset.next()) {                                                             // Check for any record to process
        MQ.put("QUERYR", 0, (Object) data, from); //jph
        return;
      }
      try {                                                                                // Next Get DATA
        ResultSetMetaData  rsmd        = resultset.getMetaData();                        // Get MetaData for Current ROW
        int                total_cols  = rsmd.getColumnCount();
        do {                                                                             // Loop through all DATA
          Table  rowtable  = new Table();
          for (int i = 1; i <= total_cols; ++i) {                                      // Loop through RECORD
            if ((M.debug & Debug.PRIMITIVE) != 0) {
              M.info("Field is " + rsmd.getColumnName(i) + " Data is " + resultset.getString(i));
            }
            String  tfield  = rsmd.getColumnName(i);
            tfield = tfield.toUpperCase();
            rowtable.put(tfield, resultset.getString(i));
          }
          data.put("ROW" + row, rowtable);
          row++;
        } while (resultset.next());                                                      // Get Next RECORD
      } catch (SQLException sqlex) {
        M.info("FAIL:" + sqlex);
      }
      MQ.put("QUERYR", row - 1, (Object) data, from);    //jph
      resultset.close();
      statement.close();
    } catch (SQLException sqlex) {
      sqlex.printStackTrace();
    }
  }

  /** Set if API Update check is enabled
   *  @param enableCheck true to enable
   */
  @InternalUseOnly
  public void setCheckAPIUpdate (boolean enableCheck) {
    checkAPIUpdate = enableCheck;
    apiUpdateInfo = new Table();
  }

  /** Get Information to check for successful update to non-deprecated Table API
   *  @return Table with information
   */
  @InternalUseOnly
  public Table getAPIUpdateInfo () {
    return apiUpdateInfo;
  }
}