package nxm.database.lib;

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.SQLWarning;
import java.sql.Statement;
import java.sql.Types;

import nxm.sys.lib.Table;

/**
  The generic super class to describe a database in java. When all of the
  necessary fields are defined it can execute basic SQL functions. While
  not an abstract class it is not really meant to be instantiated but is
  extended by the specific database classes.

  @author Jay Hallam
  @since  NeXtMidas 2.1.0
 */
public class NxmDB {

  protected Driver      myDriver;
  protected Connection  myConnection;
  protected String      myHost;
  protected int         myPort;
  protected String      myName;
  protected String      myUserName;
  protected String      myUserPassword;
  protected boolean     verbose;
  protected String      myDrivStr;
  protected String      myURLStr;

  /** establish a connection with the database.
      @throws DatabaseException if a database access error occurs
   */
  public void connect() throws DatabaseException {
    try {
      /** since this class has no knowledge of what type of db it is accessing, everything
          needs to be explicitly defined in order to connect
       */
      myDriver = (Driver)Class.forName(myDrivStr).getConstructor().newInstance(); // Updated in 3.9.0 to use constructor
      myConnection = DriverManager.getConnection(myURLStr, myUserName, myUserPassword);
      if (verbose) {
        SQLWarning warn = myConnection.getWarnings();
        while (warn != null) {
          System.out.println("SQLState = " + warn.getSQLState());
          System.out.println("Message  = " + warn.getMessage());
          System.out.println("Vendor   = " + warn.getErrorCode());
          System.out.println("");
          warn = warn.getNextWarning();
        }
      }
    } catch (Exception e) {
      throw(new DatabaseException(e.getMessage()));
    }

  }

  /** generic constructor */
  public NxmDB() {
    myPort = 0;
    myDriver = null;
    myConnection = null;
    myHost = null;
    myName = null;
    myUserName = null;
    myUserPassword = null;
    verbose = false;
  }

  /** more detailed constructor
      @param host   hostname of the database server
      @param port   port the database is listening on
      @param dbName name of database
      @param user   username to login to database
      @param passwd password to database
   */
  public NxmDB(String host, int port, String dbName,
      String user, String passwd) {

    this();
    myHost = new String(host);
    myName = new String(dbName);
    myUserName = new String(user);
    myUserPassword = new String(passwd);
    myPort = port;

  }

  // ==== public interface functions ===
  /** Turn on/off verbose messages
   *  @param v The verbose flag
   */
  public void setVerbose(boolean v) { verbose = v; }
  /** Set the database driver name
   *  @param s the database driver name
   */
  public void setDriverString(String s) { myDrivStr = s; }
  /** Set the database URL
   *  @param s the database URL
   */
  public void setURLString(String s) { myURLStr = s; }
  /** Set the hostname of the database server
   *  @param s the hostname of the database server
   */
  public void setHost(String s) { myHost = s; }
  /** Set the name of the database
   *  @param s the name of the database
   */
  public void setName(String s) { myName = s; }
  /** Set the database username
   *  @param s the database username
   */
  public void setUserName(String s) { myUserName = s; }
  /** Set the database password for username
   *  @param s the database password for username
   */
  public void setUserPassword(String s) { myUserPassword = s; }
  /** Set the port the database is listening on
   *  @param i the port the database is listening on
   */
  public void setPort(int i) { myPort = i; }

  /** Getting a list of tables is database dependent, must be done in subclass.
      @return list of tables in database
   */
  public ResultSet getTables() {
    if (verbose) System.out.println("getTables() has not been implemented");
    return (null);
  }

  /** The fields and types of a table
      @param t table to describe
      @return the fields and types of a table but since this is dependent on
         the driver we will let the subclass implement this...could probably
         implement it here but we will be safe...
   */
  public ResultSet describeTable(String t) {
    if (verbose) System.out.println("describeTable(String table) has not been implemented");
    return (null);
  }

  /** run a generic SELECT SQL statement, returns a ResultSet object.
      @param q SELECT SQL statement to execute
      @return result set of running the SELECT SQL statement
   */
  public ResultSet selectQuery(String q) {
    ResultSet rs;
    try {
      Statement stmt = myConnection.createStatement();
      rs = stmt.executeQuery(q);
    } catch (SQLException e) {
      String errStr = "SQL Statement '" + q + "'\nProduced Exception: " + e;
      if (verbose) { System.out.println(errStr);}
      rs = null;
    }
    return(rs);
  }

  /** run a generic UPDATE SQL statement.
      @param q UPDATE SQL statement to execute
      @return number of rows updated as int
   */
  public int updateQuery(String q) {
    int numUpdate;
    try {
      Statement stmt = myConnection.createStatement();
      numUpdate = stmt.executeUpdate(q);
    } catch (SQLException e) {
      String errStr = "SQL Statement '" + q + "'\nProduced Exception: " + e;
      if (verbose) { System.out.println(errStr);}
      numUpdate = 0;
    }
    return(numUpdate);
  }

  /** runs a generic SQL statement.
      @param q SQL statement to execute
      @return Depending on the type of statement it will either return a ResultSet or an Integer
   */
  public Object executeQuery(String q) {
    boolean ret;
    ResultSet rs;
    int numUpdate;
    Statement stmt;

    try {
      stmt = myConnection.createStatement();
      ret = stmt.execute(q);
      if (ret) {
        rs = stmt.getResultSet();
        return (rs);
      } else {
        numUpdate = stmt.getUpdateCount();
        return (Integer.valueOf(numUpdate)); // Updated in 3.9.0 to fix use of deprecated code
      }

    } catch (SQLException e) {
      String errStr = "SQL Statement '" + q + "'\nProduced Exception: " + e;
      if (verbose) { System.out.println(errStr);}
      return (null);
    }
  }

  /** disconnect and close the database connection.
      @throws SQLException on issue with closing database
   */
  public void disConnect() throws SQLException {
    if (myConnection != null) myConnection.close();
  }

  /** Add a new entry to an existing Midas Table
   *  @param t The Midas Table in which to add a record to
   *  @return The Midas Table with the new entry added
   */
  public Table newTableEntry(String t) {
    Table retVal = new Table();
    ResultSet rs = describeTable(t);
    String resName;
    int resType;
    try {
      ResultSetMetaData md = rs.getMetaData();
      int cols = md.getColumnCount();
      while (rs.next()) {
        Table col = new Table();
        resName = "";
        resType = Types.NULL;
        for (int i = 1; i <= cols; i++) {
          String colName = md.getColumnName(i).toUpperCase();
          if (colName.equals("COLUMN_NAME")) { resName = rs.getString(i);}
          if (colName.equals("DATA_TYPE")) {resType =  rs.getInt(i);}
        }
        switch (resType) {
          case java.sql.Types.INTEGER:
            int ival = 0;
            col.put("VALUE", ival);
            break;
          case java.sql.Types.FLOAT:
            float fval = Float.valueOf(0.0f); // Updated in 3.9.0 to fix use of deprecated code
            col.put("VALUE", fval);
            break;
          case java.sql.Types.DECIMAL:
          case java.sql.Types.NUMERIC:
          case java.sql.Types.DOUBLE:
            double dval = 0.0;
            col.put("VALUE", dval);
            break;
          case java.sql.Types.CHAR:
          default:
            String sval = "";
            col.put("VALUE", sval);
        }
        col.put("NAME", resName.toUpperCase());
        col.put("COLUMN_NAME", resName);
        retVal.put(resName.toUpperCase(), col);
      }
    } catch (Exception e) {
      System.out.println("Caught exception in newTableEntry(String): " + e);
      retVal = null;
    }
    return (retVal);
  }

  /** Gets the state of whether or not the database connection is closed
   *  @return Whether or not the database connection is closed
   */
  public boolean isClosed() {
    boolean retVal;
    if (myConnection == null) return (true);
    try {
      retVal = myConnection.isClosed();
    } catch (Exception e) {
      System.out.println("Caught exception in isClosed " + e);
      retVal = true;
    }

    return (retVal);
  }

  /** Commit all changes made since the previous commit */
  public void commit() {
    try {
      myConnection.commit();
    } catch (SQLException e) {
      System.out.println("Problem with commit: " + e);
    }
  }
}