package com.thinkbiganalytics.jobrepo.query.substitution;

/**
 * Created by sr186054 on 9/17/15.
 */
public class MysqlDatabaseQuerySubstitution implements DatabaseQuerySubstitution {

  public String truncateTimestampToDate(String column) {
    return "date(" + column + ")";
  }

  public String getDateDiffWhereClause(String column, DATE_PART datePart, Integer number) {
    return " " + column + " >= DATE_SUB(NOW(),INTERVAL " + number + " " + datePart.name() + ") ";
  }

  /**
   * Return a SQL fragment that will return The Current Date - some Interval
   */
  public String getTimeBeforeNow(DATE_PART datePart, Integer number) {
    return "DATE_SUB(NOW(),INTERVAL " + number + " " + datePart.name() + ") ";
  }


  public String getTimeAfterNow(DATE_PART datePart, Integer number) {
    return "DATE_ADD(NOW(),INTERVAL " + number + " " + datePart.name() + ") ";
  }

  public String getJobExecutionRunTimeSql() {
    return "TIMESTAMPDIFF(SECOND,e.START_TIME,COALESCE(e.END_TIME,NOW()))";
  }

  public String getTimeSinceEndTimeSql(String tableAlias) {
    return "TIMESTAMPDIFF(SECOND,COALESCE("+tableAlias+".END_TIME,NOW()),NOW())";
  }



  @Override
  public String getFeedExecutionRunTimeSql() {
    return "TIMESTAMPDIFF(SECOND,e.START_TIME,COALESCE(childJobs.END_TIME,COALESCE(e.END_TIME,NOW())))";
  }

  public String toDateSql(String date) {
    return "STR_TO_DATE('" + date + "','%m/%d/%Y')";
  }

  @Override
  public String limitAndOffset(Integer limit, Integer offset) {
    String limitString = "";
    if (limit != null && limit > 0) {
      if (offset != null) {
        limitString += " LIMIT " + offset + "," + limit;
      } else {
        limitString = "LIMIT " + limit;
      }

    }
    return limitString;
  }
  public String dateTimeAsMillisecondsSql(String dateTimeColumn){
    return " (UNIX_TIMESTAMP("+dateTimeColumn+")*1000) ";
  }
}

@@ -400,35 +400,45 @@
 + %22 %3E= DATE_SUB(
-NOW
+UTC_TIMESTAMP
 (),INTERVAL %22 + 
@@ -662,19 +662,29 @@
 ATE_SUB(
-NOW
+UTC_TIMESTAMP
 (),INTER
@@ -998,27 +998,37 @@
 (e.END_TIME,
-NOW
+UTC_TIMESTAMP
 ()))%22;%0A  %7D%0A%0A
@@ -1157,18 +1157,38 @@
 IME,
-NOW()),NOW
+UTC_TIMESTAMP()),UTC_TIMESTAMP
 ())%22
@@ -1349,19 +1349,29 @@
 ND_TIME,
-NOW
+UTC_TIMESTAMP
 ())))%22;%0A

fix job timing to go against UTC Time instead of NOW() time