package nxm.database.prim;

import java.sql.ResultSet;
import java.sql.SQLException;

import nxm.database.lib.NxmDB;
import nxm.database.lib.ResultSetTable;
import nxm.sys.lib.Data;
import nxm.sys.lib.Message;
import nxm.sys.lib.Midas;
import nxm.sys.lib.MidasException;
import nxm.sys.lib.Primitive;
import nxm.sys.lib.Table;

/**
   NeXtMidas database server

   @author Pete Perrino
   @since  NeXtMidas 2.1.0
*/
public class dbserver extends Primitive {
  NxmDB   myDB;
  String  result;
  boolean bg;
  /** The list of valid output types */
  public static String outTypeList = "Full,3000";
  private static int OT_FULL=1, OT_3000=2;
  private int outType = OT_FULL;

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

  public int open() {
    String  host     = MA.getCS("HOST", "localhost");
    int     port     = MA.getL("PORT", -1);
    String  dbname   = MA.getCS("NAME");
    String  user     = MA.getCS("USER");
    String  password = MA.getCS("PASSWORD");
    verbose          = MA.getState("/VERBOSE", false);
    String  dpath    = MA.getS("/DRIVER");
    String  dtype    = MA.getS("/TYPE");
    String  durl     = MA.getS("/URL");
    outType = MA.getSelectionIndex("/OUTTYPE",outTypeList, outType); // Define table output type

    bg = MA.getState("/BG");
    if (M.pipeMode==Midas.POFF && !bg) {
       M.warning("DBSERVER can only be run in piped mode or in the background");
       return FINISH;
    }

    // specifying the exact url overrides everything else
    if (dtype == null || dtype == "") {
      if (dpath == null || dpath == "") {
        M.error("You must either specify a type or driver path");
      } else if (durl == null) {
        M.error("You must specify a database url");
      }
      myDB = new NxmDB(host, port, dbname, user, password);
      myDB.setDriverString(dpath);
      myDB.setURLString(durl);
    } else {
      String tmpS = "";
      Class<?> tmpDB;
      try {
        tmpS = NxmDB.class.getPackage().getName();
        tmpS += "." + dtype + "DB";
        if (verbose) M.info("Trying to find: " + tmpS);
        tmpDB = Class.forName(tmpS);
        if (verbose) M.info("Found: " + tmpDB);
        myDB = (NxmDB)tmpDB.getConstructor().newInstance(); // updated in 3.9.0 to use constructor
      } catch (Exception e) {
        throw new MidasException("Could not find class: " + tmpS, e);
      }
    }

    myDB.setVerbose(verbose);
    myDB.setDriverString(dpath);
    myDB.setURLString(durl);
    myDB.setHost(host);
    if (port > 0) myDB.setPort(port);
    myDB.setName(dbname);
    myDB.setUserName(user);
    myDB.setUserPassword(password);

    return NORMAL;
  }

  public int process() {
    if (M.pipeMode==Midas.POFF && !bg) {
      return FINISH;
    }
    return NOOP;
  }

  public int close() {
    try {
      if (myDB != null) myDB.disConnect();
    } catch (Exception e) {
      M.warning("Caught exception while trying to close db connection: " + e);
    }
    return NORMAL;
  }

  public int processMessage(Message msg) {
    return NORMAL;
  }

  /** Get Key - NOT YET IMPLEMENTED
   *  @param name database name
   *  @return null
   */
  public Object getKey (String name) {
    return null;
  }

  /** Execute an sql query in the database
   *  @param sql the sql query to be executed as a string
   *  @return An empty default Midas Table
   */
  public Object doQuery(String sql) {
    Object retVal;

    if (verbose) M.info("doing the string query:\n" + sql);

    if (sql != null && sql != "") {
      if (!ensureOpen()) return null;

      retVal = myDB.executeQuery(sql);
      if (retVal == null) {
        M.warning("SQL statement returned null value");
      } else {
        if ( retVal instanceof Integer) {
          Data tmp = new Data(retVal);
          return tmp;
        } else {
          boolean type3000 = (outType == OT_3000);
          Table tmp = ResultSetTable.fromResultSet((ResultSet)retVal, type3000);
          try {
            ((ResultSet)retVal).close();
          } catch (SQLException e) {
            M.warning("Caught SQL Exception when trying to close ResultSet: " + e);
          }
          return tmp;
        }
      }
    } else {
      M.warning("Empty SQL string, nothing to do");
    }
    return (new Table());
  }

  /** Get the last inserted id from a Table
   *  @param tbl The target Table
   *  @return 0 if successful, otherwise 1
   */
  public int getLastInsertId(String tbl) {
    int retVal = 0;
    if (tbl == null || tbl == "") return retVal;
    if (!ensureOpen()) return retVal;

    String qry = "select LAST_INSERT_ID() from " + tbl;
    ResultSet rs = myDB.selectQuery(qry);
    try {
      rs.next();
      retVal = rs.getInt(1);
    } catch (java.sql.SQLException e) {
      M.warning("Caught exception: " + e);
    }
    return retVal;
  }

  /** Execute an sql query in the database - NOT YET IMPLEMENTED
   *  @param sql the sql query to be executed as a Midas Table
   *  @return null
   */
  public Object doQuery(Table sql) {
    M.info("Not implemented yet");
    return null;
  }

  /** Get a list of the tables in the database
   *  @return the list of tables in the database
   */
  public Object getTables() {
    ResultSet retVal = myDB.getTables();
    return (ResultSetTable.fromResultSet(retVal));
  }

  /** Describe a table
   *  @param t the table to describe
   *  @return the fields and types of a table
   */
  public Object describeTable(String t) {
    ResultSet retVal = myDB.describeTable(t);
    return (ResultSetTable.fromResultSet(retVal));
  }

  /** Add a new entry to the existing Midas Table
   *  @param t the new entry to be added
   *  @return the updated Midas Table
   */
  public Object newTableEntry(String t) {
    Table e = myDB.newTableEntry(t);
    return e;
  }

  /** Opens a connection to the database if the connection is closed
   *  @return true if the connection is successfully opened, false otherwise
   */
  public boolean ensureOpen() {
    if (isClosed()) {
      try {
        myDB.connect();
      } catch (Exception e) {
        M.warning("Caught exception while trying to open db connection: " + e);
        return false;
      }
    }
    return true;
  }

  /** Check whether or not the connection to the database has been closed
   *  @return whether or not the connection to the database has been closed
   */
  public boolean isClosed() {
    if (myDB == null) return true;
    return (myDB.isClosed());
  }

  /** Connect to the database */
  public void connect() {
    try {
      myDB.connect();
    } catch (Exception e) {
      M.warning("Caught exception while trying to open db connection: " + e);
    }
  }

  /** Disconnect from the database */
  public void disConnect() {
    try {
      myDB.disConnect();
    } catch (Exception e) {
      M.warning("Caught exception while trying to close db connection: " + e);
    }
  }

}