/*
 * JORAM: Java(TM) Open Reliable Asynchronous Messaging
 * Copyright (C) 2018 - 2020 ScalAgent Distributed Technologies
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or any later version.
 * 
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307
 * USA.
 */
package fr.dyade.aaa.ext;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import fr.dyade.aaa.agent.AgentServer;
import fr.dyade.aaa.util.DBTransaction;

import org.objectweb.util.monolog.api.BasicLevel;

public class JDBCTransaction extends DBTransaction implements JDBCTransactionMBean {
  public final static String JDBC_TRANSACTION_PREFIX = "org.ow2.joram.jdbc.transaction"; 
  
  /**
   * This property allows to define the class that implements the JDBC driver, for example "com.mysql.jdbc.Driver".
   * This class needs to be in the classpath of the server.
   */
  public final static String JDBC_DRIVER_PROP = JDBC_TRANSACTION_PREFIX + ".driver";
  
  /**
   * This property allows to define the database url of the form "jdbc:subprotocol://host:port/dbname".
   * If this property is defined, the properties "protocol", "host", "port" and "dbname" below are ignored.
   */
  public final static String JDBC_URL_PROP = JDBC_TRANSACTION_PREFIX + ".url";
  
  /**
   * This property allows to define the protocol part of the URL used to establish the connection, for example "jdbc:mysql".
   * It is ignored if the url property is defined.
   */
  public final static String JDBC_DB_PROTOCOL_PROP = JDBC_TRANSACTION_PREFIX + ".protocol";
  
  /**
   * This property allows to define the hostname of the URL used to establish the connection.
   * It is ignored if the url property is defined.
   */
  public final static String JDBC_DB_HOST_PROP = JDBC_TRANSACTION_PREFIX + ".host";
  /**
   * This property allows to define the port of the URL used to establish the connection.
   * It is ignored if the url property is defined.
   */
  public final static String JDBC_DB_PORT_PROP = JDBC_TRANSACTION_PREFIX + ".port";
  
  /**
   * This property allows to define the database name of the URL used to establish the connection, by default "JoramDB".
   * It is ignored if the url property is defined.
   */
  public final static String JDBC_DB_NAME_PROP = JDBC_TRANSACTION_PREFIX + ".dbname";
  public final static String DFLT_JDBC_DB_PREFIX = "JoramDB";
  
  /**
   * This property allows to define the name of a file containing a list of arbitrary string tag/value pairs as connection
   * arguments; normally at least a "user" and "password" property should be included.
   */
  public final static String JDBC_PROPS_FILE_PROP = JDBC_TRANSACTION_PREFIX + ".properties";
  
  /**
   * This property allows to define the name of database user on whose behalf the connection is being made. If this value
   * is already set in properties it is ignored.
   */
  public final static String JDBC_DB_USER_PROP = JDBC_TRANSACTION_PREFIX + ".user";
  /**
   * This property allows to define the password of database user on whose behalf the connection is being made. If this value
   * is already set in properties it is ignored.
   */
  public final static String JDBC_DB_PASS_PROP = JDBC_TRANSACTION_PREFIX + ".password";
  /**
   * This property is used to set the number of reconnection attempts after a failure, by default 5.
   */
  public final static String JDBC_CONNECT_RETRY_COUNT_PROP = JDBC_TRANSACTION_PREFIX + ".connect_retry_count";
  /**
   * This property is used to set the minimum time between two attempts to reconnect after a failure, by default 1.000 (1 seconds).
   */
  public final static String JDBC_CONNECT_RETRY_MIN_DELAY_PROP = JDBC_TRANSACTION_PREFIX + ".connect_retry_min_delay";
  /**
   * This property is used to set the maximum time trying to reconnect after a failure, by default 60.000 (60 seconds).
   */
  public final static String JDBC_CONNECT_RETRY_MAX_PERIOD_PROP = JDBC_TRANSACTION_PREFIX + ".connect_retry_max_period";

  /**
   * This property allows to define the SQL statement allowing to create the table used by the module, for example:
   * "CREATE TABLE JoramDB (name VARCHAR(256), content LONG VARCHAR FOR BIT DATA, PRIMARY KEY(name))"
   * This property can be set only at first launching.
   */
  public final static String JDBC_DB_INIT_PROP = JDBC_TRANSACTION_PREFIX + ".dbinit";
  // For MySQL: "CREATE TABLE JoramDB (name VARCHAR(255), content LONGBLOB, PRIMARY KEY(name))";
  // For Derby: "CREATE TABLE JoramDB (name VARCHAR(256), content LONG VARCHAR FOR BIT DATA, PRIMARY KEY(name))";

  /**
   * This property allows to define the SQL statement allowing to insert an entry in the table used by the module,
   * by default: "INSERT INTO &lt;table&gt; VALUES (?, ?)"
   * This property can be set only at first launching.
   */
  public final static String JDBC_DB_INSERT_PROP = JDBC_TRANSACTION_PREFIX + ".dbinsert";
  
  /**
   * This property allows to define the SQL statement allowing to update an entry in the table used by the module,
   * by default: "UPDATE &lt;table&gt; SET content=? WHERE name=?"
   * This property can be set only at first launching.
   */
  public final static String JDBC_DB_UPDATE_PROP = JDBC_TRANSACTION_PREFIX + ".dbupdate";
  
    /**
   * This property allows to define the SQL statement allowing to load an entry from the table used by the module,
   * by default: "SELECT content FROM &lt;table&gt; WHERE name=?"
   * This property can be set only at first launching.
   */
  public final static String JDBC_DB_LOAD_PROP = JDBC_TRANSACTION_PREFIX + ".dbload";
  
  /**
   * This property allows to define the SQL statement allowing to delete an entry in the table used by the module,
   * by default: "DELETE FROM &lt;table&gt; WHERE name=?"
   * This property can be set only at first launching.
   */
  public final static String JDBC_DB_DELETE_PROP = JDBC_TRANSACTION_PREFIX + ".dbdelete";

  /**
   * This property is used to define the SQL statement executed at the end of the module, by default none.
   * This property can be set only at first launching.
   */
  public final static String JDBC_DB_CLOSE_PROP = JDBC_TRANSACTION_PREFIX + ".dbclose";

  private String driver;
  private String connurl;

  private String protocol;

  private String host;
  private String port;
  
  private String user;
  private String password;

  private String dbname;

  @Override
  public String getDBName() {
    return dbname;
  }

  private String dbinit;

  @Override
  public String getDBInitStatement() {
    return dbinit;
  }

  private String path = null;
  private Properties props = null;
  
  @Override
  protected void initDB() throws IOException {
    driver = AgentServer.getProperty(JDBC_DRIVER_PROP);
    if (driver == null)
      throw new IOException("Driver property is undefined");
    
    connurl = AgentServer.getProperty(JDBC_URL_PROP);
    // Allows to add the server id in the database name (used in tests).
    connurl = connurl.replaceAll("@@sid@@", "" + AgentServer.getServerId());
    
    protocol = AgentServer.getProperty(JDBC_DB_PROTOCOL_PROP);
    host = AgentServer.getProperty(JDBC_DB_HOST_PROP);
    port = AgentServer.getProperty(JDBC_DB_PORT_PROP);
    
    props = new Properties();
    path = AgentServer.getProperty(JDBC_PROPS_FILE_PROP);
    if (path != null) {
      try (FileInputStream fis = new FileInputStream(path)) {
        props.load(fis);
      } catch (Exception exc) {
        logmon.log(BasicLevel.FATAL,
                   "JDBCTransaction.initDB: Cannot load properties from " + path);
        throw new IOException("Bad JDBC configuration", exc);
      }
    }

    user = AgentServer.getProperty(JDBC_DB_USER_PROP);
    if (user != null) {
      if (props.containsKey("user")) {
        logmon.log(BasicLevel.WARN,
            "JDBCTransaction.initDB: user already defined in JDBC properties, cannot overload it.");
      } else {
        props.setProperty("user", user);
      }
    }
    password = AgentServer.getProperty(JDBC_DB_PASS_PROP);
    if (password != null) {
      if (props.containsKey("password")) {
        logmon.log(BasicLevel.WARN,
            "JDBCTransaction.initDB: password already defined in JDBC properties, cannot overload it.");
      } else {
        props.setProperty("password", password);
      }
    }

    if ((props.getProperty("user") == null) || (props.getProperty("password") == null)) {
      logmon.log(BasicLevel.FATAL, "JDBCTransaction.initDB: need to define authentication parameters.");
      throw new IOException("Bad JDBC configuration");
    }

    dbname = AgentServer.getProperty(JDBC_DB_NAME_PROP, DFLT_JDBC_DB_PREFIX + AgentServer.getServerId());
    // Allows to add the server id in the database name (used in tests).
    dbname = dbname.replaceAll("@@sid@@", "" + AgentServer.getServerId());
    
    dbinit = AgentServer.getProperty(JDBC_DB_INIT_PROP);
    if (dbinit == null) {
      logmon.log(BasicLevel.FATAL, "JDBCTransaction.initDB: JDBC init statement not defined.");
      throw new IOException("Bad JDBC configuration");
    }
    dbinsert = AgentServer.getProperty(JDBC_DB_INSERT_PROP);
    dbupdate = AgentServer.getProperty(JDBC_DB_UPDATE_PROP);
    dbload = AgentServer.getProperty(JDBC_DB_LOAD_PROP);
    dbdelete = AgentServer.getProperty(JDBC_DB_DELETE_PROP);
    dbclose = AgentServer.getProperty(JDBC_DB_CLOSE_PROP);

    if (connurl != null) {
      if ((protocol != null) || (host != null) || (port != null)) {
        logmon.log(BasicLevel.WARN,
                   "JDBCTransaction.initDB: JDBC URL defined, ignore other parameters (protocol, host and port).");
      }
    } else {
      if ((protocol == null) || (host == null) || (port == null)) {
        logmon.log(BasicLevel.FATAL,
                   "JDBCTransaction.initDB: Should define JDBC URL or protocol, host and port parameters.");
        throw new IOException("Bad JDBC configuration");
      }
      
      // Builds the URL from parameters
      StringBuffer strbuf = new StringBuffer(protocol).append("://");
      strbuf.append(host).append(':').append(port).append('/');
      strbuf.append(dbname);
      
      connurl = strbuf.toString();
    }
    
    connectRetryCount = AgentServer.getInteger(JDBC_CONNECT_RETRY_COUNT_PROP, JDBC_CONNECT_RETRY_COUNT_DFLT);
    if (connectRetryCount < 0) connectRetryCount = 0;
    connectRetryMinDelay = AgentServer.getLong(JDBC_CONNECT_RETRY_MIN_DELAY_PROP, JDBC_CONNECT_RETRY_MIN_DELAY_DFLT);
    if (connectRetryMinDelay < 0) connectRetryMinDelay = 0;
    connectRetryMaxPeriod = AgentServer.getLong(JDBC_CONNECT_RETRY_MAX_PERIOD_PROP, JDBC_CONNECT_RETRY_MAX_PERIOD_DFLT);
    if (connectRetryMaxPeriod < 0) connectRetryMaxPeriod = 0;

    try {
      Class.forName(driver).newInstance();
    } catch (InstantiationException | IllegalAccessException | ClassNotFoundException exc) {
      if (logmon.isLoggable(BasicLevel.DEBUG))
        logmon.log(BasicLevel.FATAL, "DBTransaction, init(): cannot load driver " + driver, exc);
      else
        logmon.log(BasicLevel.FATAL, "DBTransaction, init(): cannot load driver " + driver);
      throw new IOException(exc.getMessage());
    }

    connectDB();

    Statement s = null;
    try {
      // Creating a statement lets us issue commands against the connection.
      s = conn.createStatement();
      // We create the table.
      s.execute(dbinit);
    } catch (SQLException sqle) {
      if (logmon.isLoggable(BasicLevel.DEBUG))
        logmon.log(BasicLevel.WARN, "DBTransaction, init(): DB already exists", sqle);
      else
        logmon.log(BasicLevel.WARN, "DBTransaction, init(): DB already exists");
    } finally {
      try {
        if (s != null) s.close();
        conn.commit();
      } catch (SQLException sqle) {
        logmon.log(BasicLevel.WARN, "DBTransaction, init()", sqle);
      }
    }
    logmon.log(BasicLevel.INFO, "DBTransaction, init(): " + dumpProperties());
  }
  
  @Override
  protected void connectDB() throws IOException {
    try {
      conn = DriverManager.getConnection(connurl, props);
      conn.setAutoCommit(false);
    } catch (SQLException exc) {
      throw new IOException("JDBCTransaction.reconnectDB:", exc);
    }
  }

  @Override
  public String getDriver() {
    return driver;
  }

  @Override
  public String getURL() {
    return connurl;
  }

  @Override
  public Properties getClientInfo() {
    if (conn == null) return null;
    
    try {
      return conn.getClientInfo();
    } catch (SQLException sqle) {
      logmon.log(BasicLevel.WARN, "DBTransaction, getClientInfo(): ", sqle);
      return null;
    }
  }

  @Override
  public String getUser() {
    return user;
  }

  @Override
  public String getPropertiesPath() {
    return path;
  }
  
  
  protected void dumpProperties(StringBuilder strbuf) {
    super.dumpProperties(strbuf);
    strbuf.append('(').append("driver=").append(driver).append(')');
    strbuf.append('(').append("url=").append(connurl).append(')');
    strbuf.append('(').append("dbtable=").append(dbtable).append(')');
    strbuf.append('(').append("dbname=").append(dbname).append(')');
    strbuf.append('(').append("user=").append(user).append(')');
    strbuf.append('(').append("password=").append("***").append(')');
    strbuf.append('(').append("connect_retry_count=").append(connectRetryCount).append(')');
    strbuf.append('(').append("connect_retry_min_delay=").append(connectRetryMinDelay).append(')');
    strbuf.append('(').append("connect_retry_max_period=").append(connectRetryMaxPeriod).append(')');
  }
}
