/* Copyright 2017 Alfa Financial Software
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *    http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.alfasoftware.morf.jdbc.mysql;

import static com.google.common.base.Predicates.instanceOf;
import static com.google.common.collect.Iterables.tryFind;
import static org.alfasoftware.morf.metadata.DataType.INTEGER;
import static org.alfasoftware.morf.metadata.SchemaUtils.index;
import static org.alfasoftware.morf.metadata.SchemaUtils.namesOfColumns;
import static org.alfasoftware.morf.metadata.SchemaUtils.primaryKeysForTable;
import static org.alfasoftware.morf.sql.SqlUtils.parameter;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.NoSuchElementException;
import java.util.Optional;

import org.alfasoftware.morf.jdbc.DatabaseType;
import org.alfasoftware.morf.jdbc.NamedParameterPreparedStatement;
import org.alfasoftware.morf.jdbc.SqlDialect;
import org.alfasoftware.morf.jdbc.SqlScriptExecutor;
import org.alfasoftware.morf.jdbc.SqlScriptExecutor.ResultSetProcessor;
import org.alfasoftware.morf.metadata.Column;
import org.alfasoftware.morf.metadata.DataType;
import org.alfasoftware.morf.metadata.Index;
import org.alfasoftware.morf.metadata.Table;
import org.alfasoftware.morf.metadata.View;
import org.alfasoftware.morf.sql.AbstractSelectStatement;
import org.alfasoftware.morf.sql.Hint;
import org.alfasoftware.morf.sql.MergeStatement;
import org.alfasoftware.morf.sql.SelectStatement;
import org.alfasoftware.morf.sql.UseImplicitJoinOrder;
import org.alfasoftware.morf.sql.element.AliasedField;
import org.alfasoftware.morf.sql.element.Cast;
import org.alfasoftware.morf.sql.element.ConcatenatedField;
import org.alfasoftware.morf.sql.element.FieldReference;
import org.alfasoftware.morf.sql.element.Function;
import org.alfasoftware.morf.sql.element.SqlParameter;
import org.alfasoftware.morf.sql.element.WindowFunction;
import org.apache.commons.lang3.StringUtils;

import com.google.common.base.Joiner;
import com.google.common.base.Predicate;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.Iterables;
import com.google.common.collect.Lists;

/**
 * Implements database specific statement generation for MySQL.
 *
 * @author Copyright (c) Alfa Financial Software 2010
 */
class MySqlDialect extends SqlDialect {

  private static final long AUTONUMBER_LIMIT = 1000;

  /**
   * Default constructor.
   */
  public MySqlDialect() {
    super(""); // no schema name needed for MySQL.
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#tableDeploymentStatements(org.alfasoftware.morf.metadata.Table)
   */
  @Override
  public Collection<String> internalTableDeploymentStatements(Table table) {
    List<String> statements = new ArrayList<>();

    // Create the table deployment statement
    StringBuilder createTableStatement = new StringBuilder();
    createTableStatement.append("CREATE ");

    if (table.isTemporary()) {
      createTableStatement.append("TEMPORARY ");
    }

    createTableStatement.append("TABLE `");
    createTableStatement.append(table.getName());
    createTableStatement.append("` (");

    List<String> primaryKeys = new ArrayList<>();
    boolean first = true;
    Column autoIncrementColumn = null;
    int autoNumberStart = -1;

    for (Column column : table.columns()) {
      if (!first) {
        createTableStatement.append(", ");
      }

      createTableStatement.append("`");
      createTableStatement.append(column.getName());
      createTableStatement.append("` ");
      createTableStatement.append(sqlRepresentationOfColumnType(column));
      if (column.isAutoNumbered()) {
        autoNumberStart = column.getAutoNumberStart() == -1 ? 1 : column.getAutoNumberStart();
        createTableStatement.append(" AUTO_INCREMENT COMMENT 'AUTONUMSTART:[" + autoNumberStart + "]'");
        autoIncrementColumn = column;
      }

      if (column.isPrimaryKey()) {
        primaryKeys.add(column.getName());
      }

      first = false;
    }
    // Put on the primary key constraint
    if (!primaryKeys.isEmpty()) {
      createTableStatement
        .append(", ")
        .append(buildPrimaryKeyConstraint(table));
    }
    createTableStatement.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin");

    if (autoIncrementColumn != null && autoIncrementColumn.getAutoNumberStart() != 0) {
      createTableStatement.append(" AUTO_INCREMENT=" + autoNumberStart);
    }

    statements.add(createTableStatement.toString());

    return statements;
  }


  /**
   * CONSTRAINT TABLENAME_PK PRIMARY KEY (`X`, `Y`, `Z`)
   */
  private String buildPrimaryKeyConstraint(Table table) {
    return buildPrimaryKeyConstraint(table.getName(), namesOfColumns(primaryKeysForTable(table)));
  }

  /**
   * CONSTRAINT TABLENAME_PK PRIMARY KEY (`X`, `Y`, `Z`)
   */
  private String buildPrimaryKeyConstraint(String tableName, List<String> primaryKeyColumns) {
    return new StringBuilder()
    .append("CONSTRAINT `")
    .append(tableName)
    .append("_PK` ")
    .append("PRIMARY KEY (`")
    .append(Joiner.on("`, `").join(primaryKeyColumns))
    .append("`)").toString();
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#alterTableAddColumnStatements(org.alfasoftware.morf.metadata.Table, org.alfasoftware.morf.metadata.Column)
   */
  @Override
  public Collection<String> alterTableAddColumnStatements(Table table, Column column) {
    List<String> result = new ArrayList<>();

    // We use CHANGE, not ALTER on MySQL
    StringBuilder statement = new StringBuilder()
      .append("ALTER TABLE `")
      .append(table.getName())
      .append("` ")
      .append("ADD ")
      .append('`')
      .append(column.getName())
      .append('`')
      .append(' ')
      .append(sqlRepresentationOfColumnType(column));

    result.add(statement.toString());

    if (column.isPrimaryKey()) {
      StringBuilder primaryKeyStatement = new StringBuilder()
        .append("ALTER TABLE `")
        .append(table.getName())
        .append("` ADD ")
        .append(buildPrimaryKeyConstraint(table));
      result.add(primaryKeyStatement.toString());
    }

    return result;
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#alterTableChangeColumnStatements(org.alfasoftware.morf.metadata.Table, org.alfasoftware.morf.metadata.Column, org.alfasoftware.morf.metadata.Column)
   */
  @Override
  public Collection<String> alterTableChangeColumnStatements(Table table, Column oldColumn, Column newColumn) {
    List<String> result = new ArrayList<>();

    // build the old version of the table
    Table oldTable = oldTableForChangeColumn(table, oldColumn, newColumn);

    // recreate the PK if there's any change in the PK
    boolean recreatePrimaryKey = oldColumn.isPrimaryKey() != newColumn.isPrimaryKey();

    // drop the existing PK if there is one, and we're changing it
    if (recreatePrimaryKey && !primaryKeysForTable(oldTable).isEmpty()) {
      result.add(dropPrimaryKey(oldTable));
    }

    result.add(
      "ALTER TABLE `" + table.getName() + "` CHANGE `" + oldColumn.getName() + "` `" + newColumn.getName() + "` " + sqlRepresentationOfColumnType(newColumn)
    );

    // Put the PK back if there is one, and we're changing it
    if (recreatePrimaryKey && !primaryKeysForTable(table).isEmpty()) {
      result.add(new StringBuilder()
        .append("ALTER TABLE `")
        .append(table.getName())
        .append("` ADD ")
        .append(buildPrimaryKeyConstraint(table)).toString());
    }

    return result;
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#alterTableDropColumnStatements(org.alfasoftware.morf.metadata.Table, org.alfasoftware.morf.metadata.Column)
   */
  @Override
  public Collection<String> alterTableDropColumnStatements(Table table, Column column) {
    List<String> result = new ArrayList<>();

    StringBuilder statement = new StringBuilder().append("ALTER TABLE `").append(table.getName()).append("` ")
        .append("DROP").append(' ');

    statement.append('`').append(column.getName()).append('`');

    result.add(statement.toString());

    return result;
  }


  /**
   * ALTER TABLE `XYZ` DROP PRIMARY KEY
   */
  private String dropPrimaryKey(Table table) {
    return dropPrimaryKey(table.getName());
  }


  /**
   * ALTER TABLE `XYZ` DROP PRIMARY KEY
   */
  private String dropPrimaryKey(String tableName) {
    return "ALTER TABLE `" + tableName + "` DROP PRIMARY KEY";
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#dropStatements(org.alfasoftware.morf.metadata.Table)
   */
  @Override
  public Collection<String> dropStatements(Table table) {
    return Arrays.asList(
      "FLUSH TABLES `" + table.getName() + "`",
      "DROP TABLE `" + table.getName() + "`");
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#dropStatements(org.alfasoftware.morf.metadata.View)
   */
  @Override
  public Collection<String> dropStatements(View view) {
    return Arrays.asList("DROP VIEW IF EXISTS `" + view.getName() + "`");
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#truncateTableStatements(org.alfasoftware.morf.metadata.Table)
   */
  @Override
  public Collection<String> truncateTableStatements(Table table) {
    return Arrays.asList("TRUNCATE " + table.getName());
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#deleteAllFromTableStatements(org.alfasoftware.morf.metadata.Table)
   */
  @Override
  public Collection<String> deleteAllFromTableStatements(Table table) {
    return Arrays.asList("delete from " + table.getName());
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#postInsertWithPresetAutonumStatements(org.alfasoftware.morf.metadata.Table, boolean)
   */
  @Override
  public void postInsertWithPresetAutonumStatements(Table table, SqlScriptExecutor executor,Connection connection, boolean insertingUnderAutonumLimit) {
    repairAutoNumberStartPosition(table,executor,connection);
  }


 /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#repairAutoNumberStartPosition(org.alfasoftware.morf.metadata.Table)
   */
  @Override
  public void repairAutoNumberStartPosition(Table table, SqlScriptExecutor executor,Connection connection) {
    Column autoIncrementColumn = getAutoIncrementColumnForTable(table);

    if (autoIncrementColumn == null) {
      executor.execute(updateStatisticsStatement(table), connection);
      return;
    }

    long maxId = executor
                .executeQuery(checkMaxIdAutonumberStatement(table,autoIncrementColumn))
                .withConnection(connection)
                .processWith(new ResultSetProcessor<Long>() {
                  @Override
                  public Long process(ResultSet resultSet) throws SQLException {
                    if (!resultSet.next()) {
                      throw new UnsupportedOperationException("Nothing returned by results set");
                    }

                    return resultSet.getLong(1);
                  }
                });

    // We reset the auto increment seed to our start value every time we bulk insert data.  If the max value
    // on the table is greater, mySQL will just use that instead
    Collection<String> repairStatements = maxId < AUTONUMBER_LIMIT ?
                                                    ImmutableList.of(alterAutoincrementStatement(table,autoIncrementColumn),updateStatisticsStatement(table)) :
                                                    ImmutableList.of(updateStatisticsStatement(table));

    executor.execute(repairStatements,connection);
  }


  /**
   * Returns a statement which will update the statistics for a specific table.
   */
  private String alterAutoincrementStatement(Table table,Column autoIncrementColumn) {
    return "ALTER TABLE " + table.getName() + " AUTO_INCREMENT = " + autoIncrementColumn.getAutoNumberStart();
  }


  /**
   * Returns a statement which will update the statistics for a specific table.
   */
  private String updateStatisticsStatement(Table table) {
    return "ANALYZE TABLE " + table.getName();
  }


  /**
   * Returns a statement which will check that the max id value on the table is less than the autonumber start value
   */
  private String checkMaxIdAutonumberStatement(Table table,Column autoIncrementColumn) {
    return "SELECT MAX(" + autoIncrementColumn.getName()+") FROM "+table.getName();
  }


  /**
   * MySQL defaults to <a href="http://stackoverflow.com/questions/20496616/fetchsize-in-resultset-set-to-0-by-default">fetching
   * <em>all</em> records</a> into memory when a JDBC query is executed, which causes OOM
   * errors when used with large data sets (Cryo and ETLs being prime offenders). Ideally
   * we would use a nice big paging size here (like 200 as used in {@link OracleDialect})
   * but as noted in the link above, MySQL only supports one record at a time or all at
   * once, with nothing in between.  As a result, we default to one record for bulk loads
   * as the only safe choice.
   *
   * @see org.alfasoftware.morf.jdbc.SqlDialect#fetchSizeForBulkSelects()
   */
  @Override
  public int fetchSizeForBulkSelects() {
    return Integer.MIN_VALUE;
  }


  /**
   * MySQL doesn't permit a open connection to be used for anything else while using a streaming
   * {@link ResultSet}, so if we know it will be, we disable streaming entirely. This has obvious
   * memory implications for large data sets, so bulk loads should generally open new transactions
   * inside the loop iterating the result set, which implicitly opens separate connections, allowing
   * {@link Integer#MIN_VALUE} to be used instead.
   *
   * @see org.alfasoftware.morf.jdbc.SqlDialect#fetchSizeForBulkSelectsAllowingConnectionUseDuringStreaming()
   */
  @Override
  public int fetchSizeForBulkSelectsAllowingConnectionUseDuringStreaming() {
    return Integer.MAX_VALUE;
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getColumnRepresentation(org.alfasoftware.morf.metadata.DataType,
   *      int, int)
   */
  @Override
  protected String getColumnRepresentation(DataType dataType, int width, int scale) {
    switch (dataType) {
      case STRING:
        return String.format("VARCHAR(%d)", width);

      case DECIMAL:
        return String.format("DECIMAL(%d,%d)", width, scale);

      case DATE:
        return "DATE";

      case BOOLEAN:
        // See http://www.xaprb.com/blog/2006/04/11/bit-values-in-mysql/
        return "TINYINT(1)";

      case INTEGER:
        return "INTEGER";

      case BIG_INTEGER:
        return "BIGINT";

      case BLOB:
        return "LONGBLOB";

      case CLOB:
        return "LONGTEXT";

      default:
        throw new UnsupportedOperationException("Cannot map column with type [" + dataType + "]");
    }
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#prepareBooleanParameter(org.alfasoftware.morf.jdbc.NamedParameterPreparedStatement, java.lang.Boolean, org.alfasoftware.morf.sql.element.SqlParameter)
   */
  @Override
  protected void prepareBooleanParameter(NamedParameterPreparedStatement statement, Boolean boolVal, SqlParameter parameter) throws SQLException {
    Integer intValue = boolVal == null ? null : boolVal ? 1 : 0;
    super.prepareIntegerParameter(statement, intValue, parameter(parameter.getImpliedName()).type(INTEGER));
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#connectionTestStatement()
   */
  @Override
  public String connectionTestStatement() {
    return "select 1";
  }

  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getDatabaseType()
   */
  @Override
  public DatabaseType getDatabaseType() {
    return DatabaseType.Registry.findByIdentifier(MySql.IDENTIFIER);
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getSqlFrom(ConcatenatedField)
   */
  @Override
  protected String getSqlFrom(ConcatenatedField concatenatedField) {
    List<String> sql = new ArrayList<>();
    for (AliasedField field : concatenatedField.getConcatenationFields()) {
      sql.add(getSqlFrom(field));
    }
    // Using "_WithSeparator" but not passing a separator because MySQL vanilla CONCAT doesn't support null arguments properly
    return "CONCAT_WS('', " + StringUtils.join(sql, ", ") + ")";
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getSqlForIsNull(org.alfasoftware.morf.sql.element.Function)
   */
  @Override
  protected String getSqlForIsNull(Function function) {
    return "COALESCE(" + getSqlFrom(function.getArguments().get(0)) + ", " + getSqlFrom(function.getArguments().get(1)) + ") ";
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getFromDummyTable()
   */
  @Override
  protected String getFromDummyTable() {
    return " FROM dual";
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getSqlForOrderByField(org.alfasoftware.morf.sql.element.FieldReference)
   */
  @Override
  protected String getSqlForOrderByField(FieldReference orderByField) {
    StringBuilder result = new StringBuilder();
    String sqlFromField = getSqlFrom(orderByField);

    if (orderByField.getNullValueHandling().isPresent()) {
      switch (orderByField.getNullValueHandling().get()) {
        case FIRST:
          result.append("-ISNULL(").append(sqlFromField).append("), ");
          break;
        case LAST:
          result.append("ISNULL(").append(sqlFromField).append("), ");
          break;
        case NONE:
        default:
          break;
      }
    }

    result.append(sqlFromField);

    switch (orderByField.getDirection()) {
      case DESCENDING:
        result.append(" DESC");
        break;
      case ASCENDING:
      case NONE:
      default:
        break;
    }

    return result.toString().trim();
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#indexDropStatements(org.alfasoftware.morf.metadata.Table, org.alfasoftware.morf.metadata.Index)
   */
  @Override
  public Collection<String> indexDropStatements(Table table, Index indexToBeRemoved) {
    StringBuilder statement = new StringBuilder();

    statement.append("ALTER TABLE `")
             .append(table.getName())
             .append("` DROP INDEX `")
             .append(indexToBeRemoved.getName())
             .append("`");

    return Arrays.asList(statement.toString());
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#indexDeploymentStatements(org.alfasoftware.morf.metadata.Table, org.alfasoftware.morf.metadata.Index)
   */
  @Override
  protected Collection<String> indexDeploymentStatements(Table table, Index index) {
    StringBuilder statement = new StringBuilder();

    statement.append("ALTER TABLE `");
    statement.append(table.getName());
    statement.append("` ADD ");
    if (index.isUnique()) {
      statement.append("UNIQUE ");
    }
    statement.append("INDEX `")
             .append(index.getName())
             .append("` (`")
             .append(Joiner.on("`, `").join(index.columnNames()))
             .append("`)");

    return Collections.singletonList(statement.toString());
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#changePrimaryKeyColumns(java.lang.String, java.util.List, java.util.List)
   */
  @Override
  public Collection<String> changePrimaryKeyColumns(Table table, List<String> oldPrimaryKeyColumns, List<String> newPrimaryKeyColumns) {
    ArrayList<String> result = Lists.newArrayList();

    if (!oldPrimaryKeyColumns.isEmpty()) {
      result.add(dropPrimaryKey(table.getName()));
    }

    if (!newPrimaryKeyColumns.isEmpty()) {
      result.add(new StringBuilder()
        .append("ALTER TABLE `")
        .append(table.getName())
        .append("` ADD ")
        .append(buildPrimaryKeyConstraint(table.getName(), newPrimaryKeyColumns)).toString());
    }

    return result;
  }


  /**
   * Casting to BIGINT is not supported by MySQL at the moment as per
   * <a href="http://bugs.mysql.com/bug.php?id=26130"> this bug</a>. This method
   * skips the casting if the field type is BIGINT otherwise, it proceeds as
   * normal. See WEB-15027 for details.
   *
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getSqlFrom(org.alfasoftware.morf.sql.element.Cast)
   */
  @Override
  protected String getSqlFrom(Cast cast) {
    if (cast.getDataType() == DataType.BIG_INTEGER) {
      return getSqlFrom(cast.getExpression());
    } else if (cast.getDataType() == DataType.STRING) {
      // MySQL doesn't permit cast as VARCHAR - http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html
      return String.format("CAST(%s AS CHAR(%d))", getSqlFrom(cast.getExpression()), cast.getWidth());
    } else if (cast.getDataType() == DataType.INTEGER) {
      // MySQL doesn't permit cast as INTEGER - http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html
      return String.format("CAST(%s AS SIGNED)", getSqlFrom(cast.getExpression()));
    } else {
     return super.getSqlFrom(cast);
    }
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getSqlForYYYYMMDDToDate(org.alfasoftware.morf.sql.element.Function)
   */
  @Override
  protected String getSqlForYYYYMMDDToDate(Function function) {
    return "DATE(" + getSqlFrom(function.getArguments().get(0)) + ")";
  }



  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getSqlForDateToYyyymmdd(org.alfasoftware.morf.sql.element.Function)
   */
  @Override
  protected String getSqlForDateToYyyymmdd(Function function) {
    return String.format("CAST(DATE_FORMAT(%s, '%%Y%%m%%d') AS DECIMAL(8))",getSqlFrom(function.getArguments().get(0)));
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getSqlForDateToYyyymmddHHmmss(org.alfasoftware.morf.sql.element.Function)
   */
  @Override
  protected String getSqlForDateToYyyymmddHHmmss(Function function) {
    return String.format("CAST(DATE_FORMAT(%s, '%%Y%%m%%d%%H%%i%%s') AS DECIMAL(14))",getSqlFrom(function.getArguments().get(0)));
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getSqlForNow(org.alfasoftware.morf.sql.element.Function)
   */
  @Override
  protected String getSqlForNow(Function function) {
    return "UTC_TIMESTAMP()";
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getSqlForDaysBetween(org.alfasoftware.morf.sql.element.AliasedField, org.alfasoftware.morf.sql.element.AliasedField)
   */
  @Override
  protected String getSqlForDaysBetween(AliasedField toDate, AliasedField fromDate) {
    return "TO_DAYS("  + getSqlFrom(toDate) + ") - TO_DAYS("+ getSqlFrom(fromDate) + ")";
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getSqlForMonthsBetween(org.alfasoftware.morf.sql.element.AliasedField, org.alfasoftware.morf.sql.element.AliasedField)
   */
  @Override
  protected String getSqlForMonthsBetween(AliasedField toDate, AliasedField fromDate) {
    String toDateStr = getSqlFrom(toDate);
    String fromDateStr = getSqlFrom(fromDate);
    return String.format(
       "CASE " +
        "WHEN %s = %s THEN 0 " +
        "ELSE " +
         "PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM %s), EXTRACT(YEAR_MONTH FROM %s)) + " +
         "CASE " +
          "WHEN %s > %s THEN " +
            "CASE " +
             "WHEN DAY(%s) <= DAY(%s) OR %s = LAST_DAY(%s) THEN 0 " +
             "ELSE -1 " +
            "END " +
          "ELSE " +
            "CASE " +
             "WHEN DAY(%s) <= DAY(%s) OR %s = LAST_DAY(%s) THEN 0 " +
             "ELSE 1 " +
            "END " +
         "END " +
       "END ",
       fromDateStr, toDateStr,
       toDateStr, fromDateStr,
       toDateStr, fromDateStr,
       fromDateStr, toDateStr, toDateStr, toDateStr,
       toDateStr, fromDateStr, fromDateStr, fromDateStr
    );
  }


  /**
   * For MySQL, we need to alter the way we render a date literal in a default clause: We need to suppress the "DATE" prefix.
   */
  @Override
  protected String sqlForDefaultClauseLiteral(Column column) {
    if (column.getType() != DataType.DATE) {
      return super.sqlForDefaultClauseLiteral(column);
    }

    // suppress the "DATE" prefix for MySQL, just output the date part directly
    return String.format("'%s'", column.getDefaultValue());
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#leftTrim(org.alfasoftware.morf.sql.element.Function)
   */
  @Override
  protected String leftTrim(Function function) {
    return "LTRIM(" + getSqlFrom(function.getArguments().get(0)) + ")";
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#rightTrim(org.alfasoftware.morf.sql.element.Function)
   */
  @Override
  protected String rightTrim(Function function) {
    return "RTRIM(" + getSqlFrom(function.getArguments().get(0)) + ")";
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getSqlForAddDays(org.alfasoftware.morf.sql.element.Function)
   */
  @Override
  protected String getSqlForAddDays(Function function) {
    return String.format(
      "DATE_ADD(%s, INTERVAL %s DAY)",
      getSqlFrom(function.getArguments().get(0)),
      getSqlFrom(function.getArguments().get(1))
    );
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getSqlForAddMonths(org.alfasoftware.morf.sql.element.Function)
   */
  @Override
  protected String getSqlForAddMonths(Function function) {
    return String.format(
      "DATE_ADD(%s, INTERVAL %s MONTH)",
      getSqlFrom(function.getArguments().get(0)),
      getSqlFrom(function.getArguments().get(1))
        );
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#renameTableStatements(org.alfasoftware.morf.metadata.Table, org.alfasoftware.morf.metadata.Table)
   */
  @Override
  public Collection<String> renameTableStatements(Table from, Table to) {
    return Collections.singletonList("RENAME TABLE " + from.getName() + " TO " + to.getName());
  }


  /**
   * Backslashes in MySQL denote escape sequences and have to themselves be escaped.
   *
   * @see http://dev.mysql.com/doc/refman/5.0/en/string-literals.html
   * @see org.alfasoftware.morf.jdbc.SqlDialect#makeStringLiteral(java.lang.String)
   */
  @Override
  protected String makeStringLiteral(String literalValue) {
    if (StringUtils.isEmpty(literalValue)) {
      return "NULL";
    }
    return String.format("'%s'", StringUtils.replace(super.escapeSql(literalValue), "\\", "\\\\"));
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getSqlFrom(org.alfasoftware.morf.sql.MergeStatement)
   */
  @Override
  protected String getSqlFrom(final MergeStatement statement) {

    if (StringUtils.isBlank(statement.getTable().getName())) {
      throw new IllegalArgumentException("Cannot create SQL for a blank table");
    }

    checkSelectStatementHasNoHints(statement.getSelectStatement(), "MERGE may not be used with SELECT statement hints");

    final boolean hasNonKeyFields = getNonKeyFieldsFromMergeStatement(statement).iterator().hasNext();

    final String destinationTableName = statement.getTable().getName();

    // Add the preamble
    StringBuilder sqlBuilder = new StringBuilder("INSERT ");
    if (!hasNonKeyFields) {
      sqlBuilder.append("IGNORE ");
    }
    sqlBuilder.append("INTO ");
    sqlBuilder.append(schemaNamePrefix(statement.getTable()));
    sqlBuilder.append(destinationTableName);
    sqlBuilder.append("(");
    Iterable<String> intoFields = Iterables.transform(statement.getSelectStatement().getFields(), AliasedField::getImpliedName);
    sqlBuilder.append(Joiner.on(", ").join(intoFields));
    sqlBuilder.append(") ");

    // Add select statement
    sqlBuilder.append(getSqlFrom(statement.getSelectStatement()));

    // Add the update expressions
    if (hasNonKeyFields) {
      sqlBuilder.append(" ON DUPLICATE KEY UPDATE ");
      Iterable<AliasedField> updateExpressions = getMergeStatementUpdateExpressions(statement);
      String updateExpressionsSql = getMergeStatementAssignmentsSql(updateExpressions);
      sqlBuilder.append(updateExpressionsSql);
    }
    return sqlBuilder.toString();
  }


  @Override
  protected String getSqlFrom(MergeStatement.InputField field) {
    return "values(" + field.getName() + ")";
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#renameIndexStatements(org.alfasoftware.morf.metadata.Table,
   *      java.lang.String, java.lang.String)
   */
  @Override
  public Collection<String> renameIndexStatements(final Table table, final String fromIndexName, final String toIndexName) {
    Index newIndex, existingIndex;

    try {
      newIndex = Iterables.find(table.indexes(), new Predicate<Index>() {
        @Override public boolean apply(Index input) {
          return input.getName().equals(toIndexName);
        }
      });

      existingIndex = newIndex.isUnique()
        ? index(fromIndexName).columns(newIndex.columnNames()).unique()
        : index(fromIndexName).columns(newIndex.columnNames());
    } catch (NoSuchElementException nsee) {
      // If the index wasn't found, we must have the old schema instead of the
      // new one so try the other way round
      existingIndex = Iterables.find(table.indexes(), new Predicate<Index>() {
        @Override public boolean apply(Index input) {
          return input.getName().equals(fromIndexName);
        }
      });

      newIndex = existingIndex.isUnique()
        ? index(toIndexName).columns(existingIndex.columnNames()).unique()
        : index(toIndexName).columns(existingIndex.columnNames());
    }

    return ImmutableList.<String>builder()
      .addAll(indexDropStatements(table, existingIndex))
      .addAll(indexDeploymentStatements(table, newIndex))
      .build();
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getSqlForRandomString(org.alfasoftware.morf.sql.element.Function)
   */
  @Override
  protected String getSqlForRandomString(Function function) {
    return  String.format("SUBSTRING(MD5(RAND()), 1, %s)",getSqlFrom(function.getArguments().get(0)));
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#rebuildTriggers(org.alfasoftware.morf.metadata.Table)
   */
  @Override
  public Collection<String> rebuildTriggers(Table table) {
    return SqlDialect.NO_STATEMENTS;
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#supportsWindowFunctions()
   */
  @Override
  public boolean supportsWindowFunctions() {
    return false;
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getSqlFrom(org.alfasoftware.morf.sql.element.WindowFunction)
   */
  @Override
  protected String getSqlFrom(final WindowFunction windowFunctionField) {
    throw new UnsupportedOperationException(this.getClass().getSimpleName()+" does not support window functions.");
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#likeEscapeSuffix()
   */
  @Override
  protected String likeEscapeSuffix() {
    return ""; // On MySql the escape character is \ by default. We don't need to set it, and setting it appears to be challenging anyway as it is a general escape char.
  }




  /**
   * If using {@link SelectStatement#useImplicitJoinOrder()}, we switch inner joins to STRAIGHT_JOINs.
   *
   * @see org.alfasoftware.morf.jdbc.SqlDialect#innerJoinKeyword(org.alfasoftware.morf.sql.AbstractSelectStatement)
   */
  @Override
  protected String innerJoinKeyword(AbstractSelectStatement<?> stmt) {
    if (stmt instanceof SelectStatement) {
      List<Hint> hints = ((SelectStatement)stmt).getHints();
      if (tryFind(hints, instanceOf(UseImplicitJoinOrder.class)).isPresent()) {
        return "STRAIGHT_JOIN";
      }
    }
    return super.innerJoinKeyword(stmt);
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect#getSqlForLastDayOfMonth
   */
  @Override
  protected String getSqlForLastDayOfMonth(AliasedField date) {
    return "LAST_DAY(" + getSqlFrom(date) + ")";
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect.getSqlForAnalyseTable(Table)
   */
  @Override
  public Collection<String> getSqlForAnalyseTable(Table table) {
    return SqlDialect.NO_STATEMENTS;
  }


  /**
   * @see org.alfasoftware.morf.jdbc.SqlDialect.getDeleteLimitSuffixSql(int)
   */
  @Override
  protected Optional<String> getDeleteLimitSuffix(int limit) {
    return Optional.of("LIMIT " + limit);
  }
}