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.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.logging.Logger;

/**
 Perform a single SELECT command.

<P>To perform single INSERT, DELETE, or UPDATE operation, use {@link SqlEditor} instead. 

<P><span class="highlight">The utilities in the {@link Db} class should always 
 be considered as a simpler alternative to this class.</span> 
 
<P>The user of this class is able to fetch records 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>error handling 
 <li>logging of warnings
 <li>closing statements and connections
</ul>
<P>Example use case in a Data Access Object(DAO), where a <tt>Person</tt> object 
is fetched using <tt>aName</tt> as a simple business identifier : 
<PRE>
Person fetch(String aName) throws DAOException {
  Args.checkForContent(aName);
  SqlFetcher3 fetcher = SqlFetcher3.forSingleOp(FETCH_PERSON, Util.asList(aName));
  ModelBuilder3 builder = new ModelFromRow(Person.class);
  return (Person)fetcher.fetchObject( builder );
}
</PRE>
 <P>Note that the above feature is implemented using only
<ul>
 <li>one entry in a <tt>.sql</tt> text file, which stores the underlying SQL
 statement as text, outside compiled code
 <li>one <tt>public static final</tt> {@link SqlId} field declared in some class 
 (usually the {@link hirondelle.web4j.action.Action} of the DAO itself) 
 <li>a short method in a DAO. (In addition, if this method uses 
 {@link ModelFromRow}, then the length of this method is almost always 3 or 4 lines, 
 <em>regardless of the number of fields in the underlying Model Object</em>)
</ul>
 Given these database utility classes, many DAO implementations 
 become very compact. (The {@link Db} class can reduce the size of  
 implementations even further.)

<P> Almost all callers of <tt>SqlFetcher</tt> will also use a {@link ModelBuilder},
 which is closely related to this class.

 <P><tt>SqlFetcher</tt> can participate in a transaction by passing a
 {@link Connection} to one of its static factory methods. Otherwise, an internal 
 <tt>Connection</tt> is obtained from {@link hirondelle.web4j.database.ConnectionSource}, 
 using {@link SqlId#getDatabaseName()} to identify the database. 
*/
final class SqlFetcher {

  /**
   Factory method for single <tt>SELECT</tt> operation which takes parameters.
  */
  static SqlFetcher forSingleOp(SqlId aSqlId, Object... aParams){
    return new SqlFetcher(aSqlId, INTERNAL_CONNECTION, NO_SEARCH_CRITERIA, aParams);
  }

  /**
   Factory method for <tt>SELECT</tt> operation which takes parameters and participates in 
   a transaction.
  
   @param aConnection used by all operations participating in the given transaction; 
   this connection is owned by the caller.
  */
  static SqlFetcher forTx(SqlId aSqlId, Connection aConnection, Object... aParams){
    return new SqlFetcher(aSqlId, aConnection, NO_SEARCH_CRITERIA, aParams);
  }
  
  /**
   Factory method for a <tt>SELECT</tt> operation which takes parameters, and whose criteria are dynamic.
  */
  static SqlFetcher forSearch(SqlId aSqlId, DynamicSql aSearchCriteria, Object... aParams) {
    return new SqlFetcher(aSqlId, INTERNAL_CONNECTION, aSearchCriteria, aParams);
  }
  
  /**
   Perform a SELECT and return the first row as a Model Object.
    
   <P>Intended for use with SELECT statements which usually return one row.<br>
   If <tt>1..N</tt> rows are returned, parse the first row into a Model Object and 
   return it. Ignore any other rows.<br> If no rows are returned, return <tt>null</tt>.
   (This behavior can occasionally be used to limit the result set to a single record. 
   It is a convenient alternative when the database has no robust or simple method of
   limiting the number of returned rows. However, this technique should be used 
   only if the number of returned rows is not excessively large.)
  
   @param aModelBuilder parses a row into a corresponding Model Object.
  */
  <T> T fetchObject(ModelBuilder<T> aModelBuilder) throws DAOException {
    List<T> list = new ArrayList<T>();
    fetchObjects(aModelBuilder, list);
    return list.isEmpty()? null : list.get(0);
  }
  
  /**
   Perform a SELECT and return a corresponding <tt>Collection</tt> of Model Objects.
  
   <P>Intended for use with SELECT statements which usually return multiple rows.<br>
   If <tt>1..N</tt> rows are found, parse each row into a Model Object and 
   place it in <tt>aResult</tt>.<br> Items are added to <tt>aResult</tt>
   in the order returned from the underlying <tt>ResultSet</tt>.<br>
   If <tt>0</tt> rows are found, then <tt>aResult</tt> will remain empty.
  
   @param aModelBuilder parses a row into a corresponding Model Object.
   @param aResult acts as an "out" parameter, and is initially empty.
  */
  <T> void fetchObjects(ModelBuilder<T> aModelBuilder, Collection<T> aResult) throws DAOException {
    checkIsEmpty(aResult);
    
    ResultSet resultSet = fetchRows();
    RowCycler cycler = new CollectionRowCycler(aResult);
    cycler.cycleRows(resultSet, aModelBuilder);
    if (aResult.size() == 0){
      fLogger.fine("No Results Found for query: " + fSql);
    }
  }

  /**
    Set parameters for limiting returned items to a specific block of rows.
   
    <P><em>This method is not usually called</em>. It is intended only for those 
    cases in which only a subset of the underlying <tt>ResultSet</tt> is to be 
    returned. Usually, all rows of the underlying result set are of interest, 
    and none should be eliminated from processing.
   
    @param aStartIdx 1 or more
    @param aPageSize 1 or more, the number of items returned to the caller
   */
   void limitRowsToRange(Integer aStartIdx, Integer aPageSize){
     Args.checkForPositive(aStartIdx.intValue());
     Args.checkForPositive(aPageSize.intValue());
     fStartIdx = aStartIdx;
     fEndIdx = getEndIdx(aStartIdx, aPageSize);
   }

  /**
   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 a constructor of this class, then this method 
   must not be called.</span> (Changing the isolation level after a transaction 
   has started is not permitted - see {@link Connection}).
  
   @see TxIsolationLevel
  */ 
  void setTxIsolationLevel(TxIsolationLevel aTxIsolationLevel) {
    if ( ! fConnectionIsInternal ) {
      throw new IllegalStateException (
        "Cannot set transaction isolation level after transaction has started."
      );
    }
    fLogger.fine("Setting transaction isolation level to " + aTxIsolationLevel);
    fExplicitTxIsolationLevel = aTxIsolationLevel;
  }
  
  // PRIVATE 
  
  private SqlStatement fSql;
  private PreparedStatement fStatement;
  
  private Connection fConnection;
  private boolean fConnectionIsInternal;
  /** 
    Explicit Isolation level, for internal connections only.
    If null, then the isolation level defined by {@link Config} for the given db name will be used. 
  */
  private TxIsolationLevel fExplicitTxIsolationLevel;
  
  private Integer fStartIdx;
  private Integer fEndIdx;
  
  private static final Connection INTERNAL_CONNECTION = null;
  private static final DynamicSql NO_SEARCH_CRITERIA = null;
  
  private static final Logger fLogger = Util.getLogger(SqlFetcher.class);  

  private SqlFetcher(SqlId aSqlId, Connection aConnection, DynamicSql aSearchCriteria, Object... aParams){
    fSql = new SqlStatement(aSqlId, aSearchCriteria, aParams);
    if ( aConnection != null ) {
      fConnectionIsInternal = false;
      fConnection = aConnection;
    }
    else {
      fConnectionIsInternal = true;
    }
  }
  
  private ResultSet fetchRows() throws DAOException {
    ResultSet result = null;
    Connection connection = null;
    try {
      connection = getConnection();
      fStatement = fSql.getPreparedStatement(connection);
      result = fStatement.executeQuery();
      DbUtil.logWarnings(fStatement);
    }
    catch (SQLException rootCause){
      String message = 
        "Cannot execute fetch: " + rootCause + Consts.SPACE +  rootCause.getMessage() + 
        Consts.SPACE + fSql
      ;
      fLogger.severe(message);
      //unusual: the close is not in a finally block, since it must only be closed if there's a problem
      close(fStatement, connection);
      throw new DAOException(message, rootCause);
    }
    return result;
  }

  /**
   Cycles over a <tt>ResultSet</tt>, but what is done to each row is left 
   up to subclasses.
  
   (This private class exists in order to eliminate code repetition between the 
   Collection and Map styles used above.) 
  */
  private abstract class RowCycler<T> {
    void cycleRows(ResultSet aResultSet, ModelBuilder<T> aModelBuilder) throws DAOException {
      try {
        while (aResultSet.next()){
          if ( ! isLimitingRows() ) {
            addItemToResult(aModelBuilder, aResultSet);
          }
          else {
            if ( isAfterStart(aResultSet) ){
              if ( ! isAfterEnd(aResultSet) ) {
                addItemToResult(aModelBuilder, aResultSet);
              }
              else {
                fLogger.fine(
                  "Finished processing ResultSet. This row is passed the end :  " + 
                  aResultSet.getRow()
                );
                break;
              }
            }
            else {
              fLogger.fine(
                "Skipping row. Index not yet high enough to start processing : " + 
                aResultSet.getRow()
              );
            }
          }
        }
      }
      catch (SQLException rootCause){
        String message = 
          "Cannot execute fetch: " + rootCause + Consts.SPACE + 
          rootCause.getErrorCode() + Consts.SPACE + fSql
        ;
        throw new DAOException(message, rootCause);
      }
      finally {
        close(fStatement, fConnection);
      }
    }
    abstract void addItemToResult(ModelBuilder<T> aModelBuilder, ResultSet aResultSet) throws DAOException, SQLException;
  }
  
  private final class CollectionRowCycler<T> extends RowCycler<T> {
    CollectionRowCycler(Collection<T> aCollection){
      fCollection = aCollection;
    }
    void addItemToResult(ModelBuilder<T> aModelBuilder, ResultSet aResultSet) throws DAOException {
      T item = aModelBuilder.buildObject(aResultSet);
      fCollection.add( item );
    }
    private Collection<T> fCollection;
  }
  
  private void checkIsEmpty(Collection aOutParam){
    if ( ! aOutParam.isEmpty() ) {
      throw new IllegalArgumentException("Out parameter is not initially empty.");
    }
  }
  
  /**
   Return either the external connection, passed by the caller, or an internal one with 
   the appropriate isolation level.
  */
  private Connection getConnection() throws DAOException {
    if ( fConnectionIsInternal ) {
      String dbName = fSql.getSqlId().getDatabaseName();
      if( Util.textHasContent(dbName) ){
        fConnection = BuildImpl.forConnectionSource().getConnection(dbName);
      }
      else{ 
        fConnection = BuildImpl.forConnectionSource().getConnection();
      }
      TxIsolationLevel.set(getIsolationLevel(dbName), fConnection);
    }
    else {
      //use the external connection passed to the constructor
    }
    return fConnection;
  }
  
  private TxIsolationLevel getIsolationLevel(String aDbName){
    TxIsolationLevel result = null;
    if(fExplicitTxIsolationLevel != null) {
      result = fExplicitTxIsolationLevel;
    }
    else {
      result = new Config().getSqlFetcherDefaultTxIsolationLevel(aDbName);
    }
    return result;
  }
  
  private void close(
    PreparedStatement aStatement, Connection aConnection
  ) throws DAOException {
    if ( fConnectionIsInternal ) {
      DbUtil.close(aStatement, aConnection);
    }
    else {
      //do not close the connection, since it is owned by the caller, and is
      //needed by other statements in the transaction
      DbUtil.close(aStatement);
    }
  }
  
  private Integer getEndIdx(Integer aStartIdx, Integer aPageSize){
    return new Integer( aStartIdx.intValue() + aPageSize.intValue() - 1);
  }
  
  private boolean isLimitingRows() {
    return fStartIdx != null;
  }
  
  private boolean isAfterStart(ResultSet aResultSet) throws SQLException {
    return aResultSet.getRow() >= fStartIdx.intValue();
  }

  private boolean isAfterEnd(ResultSet aResultSet) throws SQLException {
    return aResultSet.getRow() > fEndIdx.intValue();
  }
}
