package hirondelle.web4j.database;

import static hirondelle.web4j.util.Consts.NEW_LINE;
import hirondelle.web4j.BuildImpl;
import hirondelle.web4j.model.AppException;
import hirondelle.web4j.model.DateTime;
import hirondelle.web4j.model.DateTime.Unit;
import hirondelle.web4j.model.Decimal;
import hirondelle.web4j.model.Id;
import hirondelle.web4j.readconfig.Config;
import hirondelle.web4j.readconfig.ConfigReader;
import hirondelle.web4j.security.SafeText;
import hirondelle.web4j.util.Util;

import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Enumeration;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.TimeZone;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
  Encapsulates an SQL statement used in the application.
  
  <P><span class='highlight'>See package overview for important information</span>.
  
  <P>This class hides details regarding all SQL statements used by the application. These items
  are hidden from the caller of this class :
  <ul>
  <li>the retrieval of SQL statements from an underlying textual <tt>.sql</tt> file or
  files
  <li>the textual content of SQL statements
  <li>the details of placing parameters into a {@link PreparedStatement}
  </ul>
  
  <P> Only {@link PreparedStatement} objects are used here, since they <a
  href="http://www.javapractices.com/Topic212.cjp">are usually preferable</a> to
  {@link Statement} objects.
 */
final class SqlStatement {

  /**
    Called by the framework upon startup, to read and validate all SQL statements from the
    underlying <tt>*.sql</tt> text file(s).
    
    <P>Verifies that there is no mismatch
    whatsoever between the <tt>public static final</tt> {@link SqlId} fields used in the
    application, and the keys of the corresponding <tt>*.sql</tt> file(s). If there is a
    mismatch, then an exception is thrown when this class loads, to ensure that errors are
    reported as soon as possible.
    
    <P> Upon startup, the framework can optionally
    attempt a test precompile of each SQL statement, by calling
    {@link Connection#prepareStatement(String)}. If the SQL text is not syntactically
    correct, then a call to <tt>Connection.prepareStatement()</tt> 
    <em>might</em> throw
    an {@link SQLException}, according to the implementation of the driver/database. (For
    example, JavaDB/Derby will throw an {@link SQLException}, while MySql and Oracle will not.)
    If such an error is detected, then it is logged as <tt>SEVERE</tt>.
    
    <P>A setting in <tt>web.xml</tt> can disable this pre-compilation, if desired.
   */
  static void readSqlFile() {
    readSqlText();
    checkStoredProcedures();
    checkSqlFilesVersusSqlIdFields();
    precompileAll();
  }
  
  public static void initSqlStatementsManually(Map<String, String> aStatements){
    if (fSqlProperties == null){
      fSqlProperties = new Properties();
    }
    fSqlProperties.clear();
    fSqlProperties.putAll(aStatements);
  }

  /**
    SQL statement which takes parameters.
    
    <P>This class supports the same classes as parameters as {@link ConvertColumnImpl}.
    That is, only objects of the those classes can be present in the <tt>aSqlParams</tt>
    list. A parameter may also be <tt>null</tt>.
    
    <P> For <tt>Id</tt> objects, in particular, the underlying column must modeled as text,
    not a number. If the underlying column is numeric, then the caller must convert an
    {@link Id} into a numeric form using {@link Id#asInteger} or {@link Id#asLong}.
    
    @param aSqlId corresponds to a key in the underlying <tt>.sql</tt> file
    @param aSearchCriteria is possibly <tt>null</tt>, and represents the criteria input
    by the user during a search operation for a particular record (or records). If present,
    then {@link DynamicSql#toString()} is appended to the text of the underlying SQL
    statement from the <tt>.sql</tt> files.
    @param aSqlParams contains at least one object of the supported classes noted above;
    <span class="highlight">the number and order of these parameter objects matches the
    number and order of "?" parameters in the underlying SQL</span>.
   */
  SqlStatement(SqlId aSqlId, DynamicSql aSearchCriteria, Object... aSqlParams) {
    fSqlId = aSqlId;
    fSqlText = getSqlTextFromId(aSqlId);
    if (aSearchCriteria != null) {
      fSqlText = fSqlText + aSearchCriteria.toString();
    }
    checkNumParamsMatches(aSqlParams);
    checkParamsOfSupportedType(aSqlParams);
    fParams = aSqlParams;
    fLogger.finest(this.toString());
  }

  /**
    Return a {@link PreparedStatement} whose parameters, if any, have been populated using
    the <tt>aSqlParams</tt> passed to the constructor.
    
    <P>If the underlying database auto-generates any keys by executing the returned
    <tt>PreparedStatement</tt>, they will be available from the returned value using
    {@link Statement#getGeneratedKeys}.
    
    <P>If the returned statement is a <tt>SELECT</tt>, then a limit, as configured in
    <tt>web.xml</tt>, is placed on the maximum number of rows which can be returned.
    This is meant as a defensive safety measure, to avoid returning an excessively large
    number of rows.
   */
  PreparedStatement getPreparedStatement(Connection aConnection) throws SQLException {
    PreparedStatement result = null;
    result = getPS(fSqlText, aConnection, fSqlId);
    populateParamsUsingPS(result);
    result.setMaxRows(fConfig.getMaxRows(fSqlId.getDatabaseName()).intValue());
    result.setFetchSize(fConfig.getFetchSize(fSqlId.getDatabaseName()).intValue());
    return result;
  }

  /** Return the {@link SqlId} passed to the constructor. */
  public SqlId getSqlId() {
    return fSqlId;
  }

  /**
    Return the number of <tt>'?'</tt> placeholders appearing in the underlying SQL
    statement.
   */
  static int getNumParameters(SqlId aSqlId) {
    int result = 0;
    String sqlText = getSqlTextFromId(aSqlId);
    result = getNumParams(fQUESTION_MARK, sqlText);
    return result;
  }

  /** Intended for debugging only. */
  @Override  public String toString() {
    StringBuilder result = new StringBuilder();
    result.append(fSqlId);
    result.append(" {");
    result.append(NEW_LINE);
    result.append(" fSqlText = ").append(fSqlText).append(NEW_LINE);
    List<Object> params = Arrays.asList(fParams);
    result.append(" Params = ").append(params).append(NEW_LINE);
    result.append("}");
    result.append(NEW_LINE);
    return result.toString();
  }

  // PRIVATE

  /** The id of the SQL statement, as named in the underlying .sql file. */
  private final SqlId fSqlId;

  /** Parameter values to be placed into a SQL statement. */
  private final Object[] fParams;

  /**
    The raw text of the SQL statement, as retrieved from the underlying *.sql file(s) (for
    example "SELECT Name FROM Blah").
   */
  private String fSqlText;

  /** Contents of the underlying *.sql file(s). */
  private static Properties fSqlProperties;
  private static final Pattern fQUESTION_MARK = Pattern.compile("\\?");
  private static final Pattern fSQL_PROPERTIES_FILE_NAME_PATTERN = Pattern.compile("(?:.)*\\.sql");
  private static final String fTESTING_SQL_PROPERTIES = "C:\\johanley\\Projects\\web4j-jar-trunk\\classes\\hirondelle\\web4j\\database\\mysql.sql";
  private static final String fSTORED_PROC = "{call";
  private static final Pattern fSELECT_PATTERN = Pattern.compile("^SELECT", Pattern.CASE_INSENSITIVE);
  private static final String fUNSUPPORTED_STORED_PROC = "{?=";
  
  private Config fConfig = new Config();
  private static final Logger fLogger = Util.getLogger(SqlStatement.class);

  private static PreparedStatement getPS(String aSqlText, Connection aConnection, SqlId aSqlId)  throws SQLException {
    PreparedStatement result = null;
    if (isStoredProcedure(aSqlText)) {
      result = aConnection.prepareCall(aSqlText);
    }
    else {
      if (isSelect(aSqlText)) {
        // allow scrolling of SELECT result sets; this is needed by ModelFromRow, to create lists with children
        result = aConnection.prepareStatement(aSqlText, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      }
      else {
        Config config = new Config();
        if (config.getHasAutoGeneratedKeys(aSqlId.getDatabaseName())) {
          result = aConnection.prepareStatement(aSqlText, Statement.RETURN_GENERATED_KEYS);
        }
        else {
          result = aConnection.prepareStatement(aSqlText);
        }
      }
    }
    return result;
  }

  private void populateParamsUsingPS(PreparedStatement aStatement) throws SQLException {
    // parameter indexes are 1-based, not 0-based.
    for (int idx = 1; idx <= fParams.length; ++idx) {
      Object param = fParams[idx - 1];
      if (param == null) {
        fLogger.finest("Param" + idx + ": null");
        // is there a better way of doing this?
        // setNull needs the type of the underlying column, which is not available
        aStatement.setString(idx, null);
      }
      else if (param instanceof String) {
        fLogger.finest("Param" + idx + ": String");
        aStatement.setString(idx, (String)param);
      }
      else if (param instanceof Integer) {
        fLogger.finest("Param" + idx + ": Integer");
        Integer paramVal = (Integer)param;
        aStatement.setInt(idx, paramVal.intValue());
      }
      else if (param instanceof Boolean) {
        fLogger.finest("Param" + idx + ": Boolean");
        Boolean paramVal = (Boolean)param;
        aStatement.setBoolean(idx, paramVal.booleanValue());
      }
      else if (param instanceof hirondelle.web4j.model.DateTime) {
        fLogger.finest("Param" + idx + ": hirondelle.web4j.model.DateTime");
        setDateTime(param, aStatement, idx);
      }
      else if (param instanceof java.util.Date) {
        fLogger.finest("Param" + idx + ": Date");
        setDate(param, aStatement, idx);
      }
      else if (param instanceof java.math.BigDecimal) {
        fLogger.finest("Param" + idx + ": BigDecimal");
        aStatement.setBigDecimal(idx, (BigDecimal)param);
      }
      else if (param instanceof Decimal) {
        fLogger.finest("Param" + idx + ": Decimal");
        Decimal value = (Decimal)param;
        aStatement.setBigDecimal(idx, value.getAmount());
      }
      else if (param instanceof Long) {
        fLogger.finest("Param" + idx + ": Long");
        Long paramVal = (Long)param;
        aStatement.setLong(idx, paramVal.longValue());
      }
      else if (param instanceof Id) {
        fLogger.finest("Param" + idx + ": Id");
        Id paramId = (Id)param;
        aStatement.setString(idx, paramId.getRawString());
      }
      else if (param instanceof SafeText) {
        fLogger.finest("Param" + idx + ": SafeText");
        SafeText paramText = (SafeText)param;
        aStatement.setString(idx, paramText.getRawString());
      }
      else if (param instanceof Locale){
        fLogger.finest("Param" + idx + ": Locale");
        Locale locale = (Locale)param;
        String nonLocalizedId = locale.toString(); //en_US_south; independent of any JRE locale
        aStatement.setString(idx, nonLocalizedId);
      }
      else if (param instanceof TimeZone){
        fLogger.finest("Param" + idx + ": TimeZone");
        TimeZone timeZone = (TimeZone)param;
        String nonLocalizedId = timeZone.getID(); //America/Montreal  
        aStatement.setString(idx, nonLocalizedId);
      }
      else if (param instanceof InputStream){
        fLogger.finest("Param" + idx + ": InputStream");
        InputStream stream = (InputStream)param;
        aStatement.setBinaryStream(idx, stream); //jdk1.6 ; 1.5 needs an extra int, the stream length
      }
      else {
        throw new IllegalArgumentException("Unsupported type of parameter: " + param.getClass());
      }
    }
  }

  private void setDate(Object aParam, PreparedStatement aStatement, int aIdx) throws SQLException {
    // java.sql.Date has date only, and java.sql.Time has time only
    java.util.Date dateUtil = (java.util.Date)aParam;
    java.sql.Timestamp timestampSql = new java.sql.Timestamp(dateUtil.getTime());
    if (fConfig.hasTimeZoneHint()) {
      aStatement.setTimestamp(aIdx, timestampSql, fConfig.getTimeZoneHint());
    }
    else {
      aStatement.setTimestamp(aIdx, timestampSql);
    }
  }
  
  private void setDateTime(Object aParam, PreparedStatement aStatement, int aIdx) throws SQLException {
    DateTime dateTime = (DateTime)aParam;
    String formattedDateTime = "";
    if (
      dateTime.unitsAllPresent(Unit.YEAR, Unit.MONTH, Unit.DAY) && 
      dateTime.unitsAllAbsent(Unit.HOUR, Unit.MINUTE, Unit.SECOND)
    ){
      fLogger.finest("Treating DateTime as a date (year-month-day).");
      formattedDateTime = dateTime.format(fConfig.getDateFormat(fSqlId.getDatabaseName()));
    }
    else if ( 
      dateTime.unitsAllAbsent(Unit.YEAR, Unit.MONTH, Unit.DAY) && 
      dateTime.unitsAllPresent(Unit.HOUR, Unit.MINUTE, Unit.SECOND)
    ){
      fLogger.finest("Treating DateTime as a time (hour-minute-second).");
      formattedDateTime = dateTime.format(fConfig.getTimeFormat(fSqlId.getDatabaseName()));
    }
    else if ( 
      dateTime.unitsAllPresent(Unit.YEAR, Unit.MONTH, Unit.DAY) && 
      dateTime.unitsAllPresent(Unit.HOUR, Unit.MINUTE, Unit.SECOND)
    ) {
      fLogger.finest("Treating DateTime as a date+time (year-month-day-hour-minute-second).");
      formattedDateTime = dateTime.format(fConfig.getDateTimeFormat(fSqlId.getDatabaseName()));
    }
    else {
      String message = 
         "Unable to format DateTime using the DateTimeFormatForPassingParamsToDb setting in web.xml." +
         " The units present in the DateTime object do not match any of the expected combinations. " + 
         "If needed, you can always format the DateTime manually in your DAO, and pass a String to the database instead of a DateTime."
       ;
      fLogger.severe(message);
      throw new IllegalArgumentException(message);
    }
    aStatement.setString(aIdx, formattedDateTime);
  }

  private static String getSqlTextFromId(SqlId aSqlId) {
    return fSqlProperties.getProperty(aSqlId.toString());
  }

  private void checkNumParamsMatches(Object[] aSqlParams) {
    checkNumParams(fQUESTION_MARK, aSqlParams);
  }

  private static boolean isStoredProcedure(String aSqlText) {
    return aSqlText.startsWith(fSTORED_PROC);
  }

  private static boolean isSelect(String aSqlText) {
    return Util.contains(fSELECT_PATTERN, aSqlText);
  }

  private void checkNumParams(Pattern aPattern, Object[] aParams) {
    Matcher matcher = aPattern.matcher(fSqlText);
    int numParams = 0;
    while (matcher.find()) {
      ++numParams;
    }
    if (numParams != aParams.length) { 
      throw new IllegalArgumentException(aParams.length + " params should be " + numParams); 
    }
  }

  private static int getNumParams(Pattern aPlaceholderPattern, String aSqlText) {
    int result = 0;
    Matcher matcher = aPlaceholderPattern.matcher(aSqlText);
    while (matcher.find()) {
      ++result;
    }
    return result;
  }

  private void checkParamsOfSupportedType(Object[] aSqlParams) {
    for (Object param : aSqlParams) {
      if (!isSupportedType(param)) {
        throw new IllegalArgumentException("Unsupported type of SQL parameter: " + param.getClass()); 
      }
    }
  }

  private boolean isSupportedType(Object aParam) {
    return aParam == null || BuildImpl.forConvertParam().isSupported(aParam.getClass());
  }

  private static void readSqlText() {
    if (fSqlProperties != null) {
      fSqlProperties.clear();
    }
    Config config = new Config();
    if (!config.isTesting()) {
      fSqlProperties = ConfigReader.fetchMany(fSQL_PROPERTIES_FILE_NAME_PATTERN, ConfigReader.FileType.TEXT_BLOCK);
    }
    else {
      fSqlProperties = ConfigReader.fetchForTesting(fTESTING_SQL_PROPERTIES, ConfigReader.FileType.TEXT_BLOCK);
    }
  }

  private static void checkSqlFilesVersusSqlIdFields() {
    Map sqlIdFields = ConfigReader.fetchPublicStaticFinalFields(SqlId.class);
    Set<String> sqlIdStrings = convertToSetOfStrings(sqlIdFields);
    fLogger.config("SqlId fields " + Util.logOnePerLine(sqlIdStrings));
    AppException mismatches = getMismatches(sqlIdStrings, fSqlProperties.keySet());
    if (mismatches.isNotEmpty()) {
      fLogger.severe("MISMATCH found between .sql files and SqlId fields. " + Util.logOnePerLine(mismatches.getMessages()));
      throw new IllegalStateException(Util.logOnePerLine(mismatches.getMessages()));
    }
    fLogger.config("No mismatches found between .sql files and SqlId fields.");
  }

  /**
    Map <tt>aSqlIdFields</tt> contains KEY - containing Class VALUE - Set of SqlId Fields
    <P>
    In this case, we are interested only in the "global" set of SqlId fields, unrelated to
    any particular class. This method will doubly iterate through its argument, and return
    a Set of Strings extracted from the SqlId.toString() method. This is to allow
    comparison with the identifiers in the .sql files.
   */
  private static Set<String> convertToSetOfStrings(Map<Class<?>, Set<SqlId>> aSqlIdFields) {
    Set<String> result = new LinkedHashSet<String>();
    Set classes = aSqlIdFields.keySet();
    Iterator classesIter = classes.iterator();
    while (classesIter.hasNext()) {
      Class containingClass = (Class)classesIter.next();
      Set<SqlId> fields = aSqlIdFields.get(containingClass);
      result.addAll(getSqlIdFieldsAsStrings(fields));
    }
    return result;
  }

  private static Set<String> getSqlIdFieldsAsStrings(Set<SqlId> aSqlIds) {
    Set<String> result = new LinkedHashSet<String>();
    for (SqlId sqlId : aSqlIds) {
      result.add(sqlId.toString());
    }
    return result;
  }

  private static AppException getMismatches(Set<String> aSqlIdStrings,
  Collection<Object> aSqlTextFileKeys) {
    AppException result = new AppException();
    for (String fieldValue : aSqlIdStrings) {
      if (!aSqlTextFileKeys.contains(fieldValue)) {
        result.add("SqlId field " + fieldValue + " is not present in any underlying .sql file.");
      }
    }
    for (Object sqlFileKey : aSqlTextFileKeys) {
      if (!aSqlIdStrings.contains(sqlFileKey)) {
        result.add("The key " + sqlFileKey  + " in a .sql file does not match any corresponding public static final SqlId field in any class.");
      }
    }
    return result;
  }

  private static void checkStoredProcedures() {
    AppException errors = new AppException();

    Enumeration allSqlIds = fSqlProperties.propertyNames();
    while (allSqlIds.hasMoreElements()) {
      String sqlId = (String)allSqlIds.nextElement();
      String sql = (String)fSqlProperties.get(sqlId);
      if (sql.startsWith(fUNSUPPORTED_STORED_PROC)) {
        errors.add(
          "The stored procedured called " + Util.quote(sqlId) + " has an explict return "
          + "value since it begins with " + fUNSUPPORTED_STORED_PROC + ". "
          + "A *.sql file can contain stored procedures, but only if they do not "
          + "have any OUT or INOUT parameters, including *explicit* return values (which "
          + "would need registration as an OUT parameter). See hirondelle.web4j.database "
          + "package overview for more information."
        );
      }
    }

    if (errors.isNotEmpty()) { throw new IllegalStateException(errors.getMessages().toString()); }
  }

  /**
    Attempt a precompile of all statements.
    <P>Precompilation is not supported by some drivers/databases.
   */
  private static void precompileAll() {
    fLogger.config("Attempting precompile of all SQL statements by calling Connection.prepareStatement(String). Precompilation is not supported by all drivers/databases. If not supported, then this checking is not useful. See web.xml.");
    ConnectionSource connSrc = BuildImpl.forConnectionSource();
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    String sqlText = null;
    SqlId sqlId = null;
    String sqlIdString = null;
    List<String> successIds = new ArrayList<String>();
    List<String> failIds = new ArrayList<String>();
    Set<Object> statementIds = fSqlProperties.keySet();
    Iterator<Object> iter = statementIds.iterator();
    while (iter.hasNext()) {
      sqlId = SqlId.fromStringId((String) iter.next());
      sqlIdString = sqlId.toString();
      sqlText = fSqlProperties.getProperty(sqlIdString);
      String dbName = sqlId.getDatabaseName();
      Config config = new Config();
      if(config.getIsSQLPrecompilationAttempted(dbName)){
        try {
          connection = (Util.textHasContent(dbName)) ? connSrc.getConnection(dbName) : connSrc.getConnection();
          preparedStatement = getPS(sqlText, connection, sqlId); // SQLException depends on driver/db
          successIds.add(sqlIdString);
        }
        catch (SQLException ex) {
          failIds.add(sqlIdString);
          fLogger.severe("SQLException occurs for attempted precompile of " + sqlId + " " + ex.getMessage() + NEW_LINE + sqlText);
        }
        catch (DAOException ex) {
          fLogger.severe("Error encountered during attempts to precompile SQL statements : " + ex);
        }
        finally {
          try {
            DbUtil.close(preparedStatement, connection);
          }
          catch (DAOException ex) {
            fLogger.severe("Cannot close connection and/or statement : " + ex);
          }
        }
      }
    }
    fLogger.config("Attempted SQL precompile, and found no failure for : " + Util.logOnePerLine(successIds));
    if (!failIds.isEmpty()) {
      fLogger.config("Attempted SQL precompile, and found *** FAILURE *** for : " + Util.logOnePerLine(failIds));
    }
  }
}
