package org.sagebionetworks.repo.manager.migration;

import java.sql.Timestamp;
import java.util.List;

import org.sagebionetworks.repo.model.dbo.DatabaseObject;
import org.sagebionetworks.repo.model.dbo.dao.table.TableTransactionDao;
import org.sagebionetworks.repo.model.dbo.persistence.table.DBOTableRowChange;
import org.sagebionetworks.repo.model.dbo.persistence.table.DBOTableTransaction;
import org.sagebionetworks.repo.model.migration.MigrationType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class TableTransactionBackfillMigrationListener implements MigrationTypeListener {
	
	@Autowired
	TableTransactionDao tableTransactionDao;
	
	@Autowired
	JdbcTemplate jdbcTemplate;
	
	public static long ONE_HOUR_MS = 1000L*60L;
	
	RowMapper<DBOTableTransaction> TRANSACTION_MAPPER = new DBOTableTransaction().getTableMapping();

	@Override
	public <D extends DatabaseObject<?>> void afterCreateOrUpdate(MigrationType type, List<D> delta) {
		// only watch table changes.
		if(MigrationType.TABLE_CHANGE.equals(type)) {
			// disable foreign keys
			setGlobalForeignKeyChecks(false);
			try {
				for(D dto: delta) {
					if(!(dto instanceof DBOTableRowChange)) {
						throw new IllegalArgumentException("Unexpected DTO: "+dto.getClass().getName());
					}
					DBOTableRowChange rowChange = (DBOTableRowChange) dto;
					if(rowChange.getTransactionId() == null) {
						// only need to back-fill changes without a transaction
						findOrCreateTransaction(rowChange);
					}
				}
			}finally {
				// enable foreign keys
				setGlobalForeignKeyChecks(true);
			}
		}
	}
	/**
	 * Find an existing transaction to match the provided change.  If 
	 * a match cannot be found create a new transaction.
	 * @param rowChange
	 */
	public void findOrCreateTransaction(DBOTableRowChange rowChange) {
		Long transactionId = null;
		// Attempt to match the previous change's transaction to this change.
		DBOTableTransaction previousTransaction = getPreviousTransaction(rowChange);
		if(previousTransaction != null) {
			if(previousTransaction.getStartedBy().equals(rowChange.getCreatedBy())) {
				// The same user created this change as the previous change.
				if(rowChange.getCreatedOn() < previousTransaction.getStartedOn()) {
					throw new IllegalStateException("Table change createdOn is less than previous changes's transaction startedOn: "+rowChange);
				}
				if(previousTransaction.getStartedOn() + ONE_HOUR_MS >= rowChange.getCreatedOn()) {
					// The previous change was started within one hour of this change.
					transactionId = previousTransaction.getTransactionId();
				}
			}
		}

		if(transactionId == null) {
			// no match found so start a new transaction for this change
			transactionId = startTransactionForChange(rowChange);
		}
		// Assign the transaction ID to this change.
		setChangeTransactionId(rowChange, transactionId);
	}
	
	/**
	 * Start a new transaction for the given table
	 * @param rowChange
	 * @return
	 */
	public long startTransactionForChange(DBOTableRowChange rowChange) {
		return tableTransactionDao.startTransaction(""+rowChange.getTableId(), rowChange.getCreatedBy(), rowChange.getCreatedOn());
	}
	
	/**
	 * Lookup the previous transaction for this table.
	 * @param rowChange
	 * @return
	 */
	public DBOTableTransaction getPreviousTransaction(DBOTableRowChange rowChange) {
		try {
			// Find the previous row version
			Long previousRowVersion = jdbcTemplate.queryForObject(
					"SELECT MAX(ROW_VERSION) FROM TABLE_ROW_CHANGE WHERE TABLE_ID = ? AND ROW_VERSION < ?",
					Long.class, rowChange.getTableId(), rowChange.getRowVersion());
			if(previousRowVersion == null) {
				return null;
			}
			// Lookup the transaction for the previous vesion.s
			return jdbcTemplate.queryForObject("SELECT T.* FROM TABLE_TRANSACTION T"
					+ " JOIN TABLE_ROW_CHANGE C ON (T.TABLE_ID = C.TABLE_ID AND T.TRX_ID = C.TRX_ID)"
					+ " WHERE C.TABLE_ID = ? AND C.ROW_VERSION = ?", TRANSACTION_MAPPER,
					rowChange.getTableId(), previousRowVersion);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}
	
	/**
	 * Set the transaction ID for the given table change.
	 * 
	 * @param rowChange
	 * @param transactionId
	 */
	public void setChangeTransactionId(DBOTableRowChange rowChange, long transactionId) {
		jdbcTemplate.update("UPDATE TABLE_ROW_CHANGE SET TRX_ID = ? WHERE TABLE_ID = ? AND ROW_VERSION = ?",
				transactionId, rowChange.getTableId(), rowChange.getRowVersion());
	}
	
	/**
	 * Helper to enable/disable foreign keys.
	 * @param enabled
	 */
	private void setGlobalForeignKeyChecks(boolean enabled) {
		int value;
		if(enabled){
			// trun it on.
			value = 1;
		} else {
			// turn it off
			value = 0;
		}
		jdbcTemplate.update("SET FOREIGN_KEY_CHECKS = ?", value);
	}

}

@@ -1981,37 +1981,8 @@
 ) %7B%0A
-%09%09Long transactionId = null;%0A
 %09%09//
@@ -2055,37 +2055,14 @@
 .%0A%09%09
-DBOTableTransaction previousT
+Long t
 rans
@@ -2067,16 +2067,18 @@
 nsaction
+Id
  = getPr
@@ -2086,32 +2086,43 @@
 viousTransaction
+IdIfMatches
 (rowChange);%0A%09%09i
@@ -2121,630 +2121,8 @@
 ge);
-%0A%09%09if(previousTransaction != null) %7B%0A%09%09%09if(previousTransaction.getStartedBy().equals(rowChange.getCreatedBy())) %7B%0A%09%09%09%09// The same user created this change as the previous change.%0A%09%09%09%09if(rowChange.getCreatedOn() %3C previousTransaction.getStartedOn()) %7B%0A%09%09%09%09%09throw new IllegalStateException(%22Table change createdOn is less than previous changes's transaction startedOn: %22+rowChange);%0A%09%09%09%09%7D%0A%09%09%09%09if(previousTransaction.getStartedOn() + ONE_HOUR_MS %3E= rowChange.getCreatedOn()) %7B%0A%09%09%09%09%09// The previous change was started within one hour of this change.%0A%09%09%09%09%09transactionId = previousTransaction.getTransactionId();%0A%09%09%09%09%7D%0A%09%09%09%7D%0A%09%09%7D%0A
 %0A%09%09i
@@ -2772,35 +2772,20 @@
 %09public 
-DBOTableTransacti
+L
 on
+g
  getPrev
@@ -2791,32 +2791,43 @@
 viousTransaction
+IdIfMatches
 (DBOTableRowChan
@@ -3214,16 +3214,60 @@
 ous 
-vesion.s
+change if it matches this user and is within an hour
 %0A%09%09%09
@@ -3311,17 +3311,22 @@
 ELECT T.
-*
+TRX_ID
  FROM TA
@@ -3486,78 +3486,178 @@
  = ?
-%22, TRANSACTION_MAPPER,%0A%09%09%09%09%09rowChange.getTableId(), previousRowVersion
+ AND C.CREATED_BY = ? AND C.CREATED_ON %3E ?%22, Long.class,%0A%09%09%09%09%09rowChange.getTableId(), previousRowVersion, rowChange.getCreatedBy(), rowChange.getCreatedOn() - ONE_HOUR_MS
 );%0A%09

use query instead of compare in code