package hirondelle.web4j.database;

import hirondelle.web4j.BuildImpl;
import hirondelle.web4j.readconfig.Config;
import hirondelle.web4j.util.Args;
import hirondelle.web4j.util.Consts;
import hirondelle.web4j.util.Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Logger;

/**
 Perform an edit on the database corresponding to a single SQL command, and return 
 the number of affected records.

 <P>Here, an edit usually corresponds to a single INSERT, UPDATE, or DELETE operation. 
 (Note that {@link SqlFetcher} is used for SELECT operations.)
 
<P>The convenient utilities in the {@link Db} class should always be considered as a 
 simpler alternative to this class.
  
 <P>This class can use either an internal connection, or an external connection. 
 This important distinction is made explicit through the static factory methods 
 provided by this class. It corresponds to whether or not this action takes place 
 in a transaction. When tying together several operations in a transaction, the 
 caller will often use {@link TxTemplate}, combined with multiple <tt>SqlEditor</tt> 
 objects returned by the <tt>forTx</tt> factory methods.

 <P>If an internal {@link Connection} is used, then it will use the isolation 
 level configured in <tt>web.xml</tt> by default. The level can be overridden
 by calling {@link #setTxIsolationLevel}.

 <P>Internal <tt>Connection</tt>s are obtained from 
 {@link hirondelle.web4j.database.ConnectionSource}, using {@link SqlId#getDatabaseName()} 
 to identify the database. 

 <P><tt>INSERT</tt> operations have a special character, since they often create 
 identifiers used elsewhere. An <tt>INSERT</tt> can be performed in various ways :
<ul>
 <li>one of the two <tt>addRecord</tt> methods, in which auto-generated 
 keys, if present, are returned to the caller
 <li><tt>editDatabase</tt>, in which auto-generated keys are NOT returned 
 to the caller
</ul>

<P>The user of this class is able to edit the database in a very 
 compact style, without concern for details regarding 
<ul>
 <li>database connections
 <li>manipulating raw SQL text
 <li>inserting parameters into SQL statements
 <li>retrieving auto-generated keys
 <li>error handling 
 <li>logging of warnings
 <li>closing statements and connections
</ul>
<P>Example use case taken from a DAO :
<PRE>
void add(Message aNewMessage) throws DAOException {
  List params = Util.asList(
    aNewMessage.getLoginName(),
    aNewMessage.getBody(),
    aNewMessage.getDate()
  );
  SqlEditor add = SqlEditor.forSingleOp(ADD_MESSAGE, params);
  add.editDatabase();
}
</PRE>
 <P>Note that, given the reusable database utility classes in this package,
 the above feature is implemented using only
<ul>
 <li>one entry in an <tt>.sql</tt> text file
 <li>one <tt>public static final SqlId</tt> constant (<tt>ADD_MESSAGE</tt> in the example)
 <li>three lines of straight-line code in a data access object (DAO)
</ul>
 Given these database utility classes, many DAO implementations 
 become impressively compact. (The {@link Db} class can often reduce the size of  
 implementations even further.)

 <P>The <tt>forSingleOp</tt> methods may actually refer to a stored procedure. 
 In that case, many operations can actually be performed, not just one.

 <P>If the operation throws a {@link SQLException} having the specific error code 
 configured in <tt>web.xml</tt>, then the {@link DAOException} thrown by 
 this class will be a {@link DuplicateException}. DAOs for operations which may 
 have such an exception should declare both <tt>DAOException</tt> and 
 <tt>DuplicateException</tt> in their <tt>throws</tt> clauses. If the operation cannot 
 have a duplicate problem, then the DAO must not declare <tt>DuplicateException</tt> 
 in its <tt>throws</tt> clause.

 @used.By {@link Db}, DAO implementation classes which edit the database in some way, usually with 
 an <tt>INSERT</tt>, <tt>UPDATE</tt>, or <tt>DELETE</tt> command.
 @author <a href="http://www.javapractices.com/">javapractices.com</a>
*/
final class SqlEditor {

  /**
   Factory method for a single <tt>INSERT</tt>, <tt>DELETE</tt>, or <tt>UPDATE</tt> 
   operation having parameters.
  */
  static SqlEditor forSingleOp(SqlId aSqlId, Object... aParams){
    return new SqlEditor(aSqlId, INTERNAL_CONNECTION, aParams);
  }
  
  /**
   Factory method for a single <tt>INSERT</tt>, <tt>DELETE</tt>, or <tt>UPDATE</tt> 
   operation having parameters, and participating in a transaction.
  
   @param aConnection used by all operations participating in the given transaction; 
   this connection is owned by the caller.
  */
  static SqlEditor forTx(SqlId aSqlId, Connection aConnection, Object... aParams ){
    return new SqlEditor(aSqlId, aConnection, aParams);
  }
  
  /**
   Perform a single <tt>INSERT</tt>, <tt>DELETE</tt>, or <tt>UPDATE</tt> operation.
   Return the number of records edited by this operation.
  */
  int editDatabase() throws DAOException {
    return editDatabase(null, null);
  }
  
  /**
   Perform an INSERT of a single new record, and return its auto-generated id.
   Return {@link Consts#EMPTY_STRING} if no record can be added.
  
   @param aColumnIdx in range <tt>1..100</tt>, and identifies the auto-generated key; 
   see {@link Statement#getGeneratedKeys}.
  */
  String addRecord(int aColumnIdx) throws DAOException {
    fLogger.fine("Adding record, and returning autogenerated id : " + fSql);
    StringBuilder result = new StringBuilder();
    editDatabase(result, new Integer(aColumnIdx));
    fLogger.finest("Primary key of item just added : " + result);
    return result.toString();
  }

  /**
   Convenience method calls {@link #addRecord(int)} with a default value of 
   <tt>1</tt> for its  single argument.
  */
  String addRecord() throws DAOException {
    return addRecord(fDEFAULT_AUTO_GENERATED_COLUMN_IDX);
  }
  
  /**
   Override the default transaction isolation level specified in <tt>web.xml</tt>.
  
    <P>This setting is applied only if this class is using its own internal connection, and 
   has not received a connection from the caller.

   <P><span class="highlight">If the user passed an external 
   <tt>Connection</tt> to this class, then this method 
   cannot be called.</span> Changing the isolation level after a transaction 
   has started is not permitted - see {@link Connection}.
  
   @see TxIsolationLevel
  */ 
  void setTxIsolationLevel(TxIsolationLevel aTransactionIsolationLevel) {
    if ( ! fConnectionIsInternal ) {
      throw new IllegalStateException (
        "Cannot set transaction isolation level after transaction has started."
      );
    }
    fLogger.fine("Setting transaction isolation level to " + aTransactionIsolationLevel);
    fExplicitTxIsolationLevel = aTransactionIsolationLevel;
  }
  
  // PRIVATE
  
  private SqlStatement fSql;
  
  private Connection fConnection;
  private boolean fConnectionIsInternal;
  /** 
    Isolation level for an internal connection only.
    If the caller does not customize this setting, then the default level for the 
    given database name is used. 
  */
  private TxIsolationLevel fExplicitTxIsolationLevel;
  private Config fConfig = new Config();
  
  private static final Connection INTERNAL_CONNECTION = null;
  private static final int fDEFAULT_AUTO_GENERATED_COLUMN_IDX = 1;
  private static final Logger fLogger = Util.getLogger(SqlEditor.class);  

  private SqlEditor(SqlId aSqlId, Connection aConnection, Object...  aParams){
    fSql = new SqlStatement(aSqlId, null, aParams);
    if ( aConnection != null ) {
      fConnectionIsInternal = false;
      fConnection = aConnection;
    }
    else {
      fConnectionIsInternal = true;
    }
  }
  
  private int editDatabase(StringBuilder aOutAutoGeneratedKey, Integer aAutoGeneratedColIdx) throws DAOException {
    if ( aAutoGeneratedColIdx != null ) {
      Args.checkForRange(aAutoGeneratedColIdx.intValue(), 1, 100);
    }
    if ( 
      aOutAutoGeneratedKey != null && 
      Util.textHasContent(aOutAutoGeneratedKey.toString()) 
    ) {
      throw new IllegalArgumentException("'Out' param for autogenerated key must be empty");
    }
    
    int result = 0;
    PreparedStatement statement = null;
    try {
      if ( fConnectionIsInternal ){
        initConnection();
      }
      statement = fSql.getPreparedStatement(fConnection);
      //Not implemented by MySql 3.23, nor in MySql 5.0.
      //fLogger.fine("Num args : " + statement.getParameterMetaData().getParameterCount());
      result = statement.executeUpdate();
      populateAutoGenKey(result, aOutAutoGeneratedKey, aAutoGeneratedColIdx, statement);
    }
    catch (SQLException rootCause){
      String message = 
        "Cannot execute edit. Error Id code : " +  rootCause.getErrorCode() + Consts.SPACE + 
        rootCause + Consts.SPACE + fSql
      ;
      Integer errorCode = rootCause.getErrorCode();
      String dbName = fSql.getSqlId().getDatabaseName();
      if (fConfig.getErrorCodesForDuplicateKey(dbName).contains(errorCode)){
        throw new DuplicateException(message, rootCause);
      }
      else if (fConfig.getErrorCodesForForeignKey(dbName).contains(errorCode)){
        throw new ForeignKeyException(message, rootCause);
      }
      throw new DAOException(message, rootCause);
    }
    finally {
      if ( fConnectionIsInternal ) {
        DbUtil.close(statement, fConnection);
      }
      else {
        DbUtil.close(statement);
      }
    }
    return result;
  }
  
  private void initConnection() throws DAOException {
    String dbName = fSql.getSqlId().getDatabaseName();
    if( Util.textHasContent(dbName) ){
      fConnection = BuildImpl.forConnectionSource().getConnection(dbName);
    }
    else {
      fConnection = BuildImpl.forConnectionSource().getConnection();
    }
    TxIsolationLevel.set(getIsolationLevel(dbName), fConnection);
  }
  
  private TxIsolationLevel getIsolationLevel(String aDbName){
    TxIsolationLevel result = null;
    if(fExplicitTxIsolationLevel != null) {
      result = fExplicitTxIsolationLevel;
    }
    else {
      result = fConfig.getSqlEditorDefaultTxIsolationLevel(aDbName);
    }
    return result;
  }
  
  private void populateAutoGenKey(
    int aNumEdits, StringBuilder aOutAutoGenKey, Integer aAutoGenColIdx, Statement aStatement
  ) throws SQLException {
    if ( aOutAutoGenKey != null ){
      if ( Util.isSuccess(aNumEdits) ) {
        aOutAutoGenKey.append(getAutoGeneratedKey(aAutoGenColIdx.intValue(), aStatement));
      }
      else {
        aOutAutoGenKey.append(Consts.EMPTY_STRING);
      }
    }
  }

  private String getAutoGeneratedKey(int aColumnIdx, Statement aStatement) throws SQLException {
    String result = null;
    ResultSet keys = aStatement.getGeneratedKeys();
    if ( keys.next() ) {
      result = keys.getString(aColumnIdx); 
    }
    else {
      throw new IllegalArgumentException(
        "Invalid column for auto-generated key. Idx: " + aColumnIdx
      );
    }
    return result;
  }
}
