/********************************************************************
 * Copyright © 2004 VHA. All rights reserved
 ********************************************************************/
// Package
package gov.va.med.esr.common.persistent.history;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.StringTokenizer;
import org.apache.commons.lang.Validate;


public class HistoryChangeColumns {

	String table;
	HashSet includeColumnList;
	HashSet excludeColumnList = new HashSet();
	
	String partitionKey;
	String primaryKey;
	String whereClause;

	String historyChangeTimesSqlQuery;
	
	public HashSet getIncludeColumnList() {
		return includeColumnList;
	}
	
	public void setIncludeColumns(String columns) {
		includeColumnList = new HashSet();
		
		StringTokenizer columnTokenizer = new StringTokenizer(columns, ",");

		while (columnTokenizer.hasMoreTokens()) {
			String changeColumn = columnTokenizer.nextToken();
			if (changeColumn != null) {
				changeColumn = changeColumn.trim();
				if (changeColumn.equals("")) {
					changeColumn = null;
				}
			}

			if (changeColumn != null) {
				includeColumnList.add(changeColumn.toUpperCase());
			}
		}
		if (includeColumnList.size() == 0) {
			Validate.notEmpty(includeColumnList, "Empty include column list not allowed");
		}
	}

	public void setIncludeColumnList(HashSet includeColumnList) {
		this.includeColumnList = includeColumnList;
	}

	public boolean isIncludeColumnsSet() {
		if (includeColumnList != null && includeColumnList.size() > 0) {
			return true;
		}
		
		return false;
	}
	
	public HashSet getExcludeColumnList() {
		return excludeColumnList;
	}
	
	public void setExcludeColumns(String columns) {
		
		StringTokenizer columnTokenizer = new StringTokenizer(columns, ",");

		while (columnTokenizer.hasMoreTokens()) {
			String changeColumn = columnTokenizer.nextToken();
			if (changeColumn != null) {
				changeColumn = changeColumn.trim();
				if (changeColumn.equals("")) {
					changeColumn = null;
				}
			}

			if (changeColumn != null) {
				excludeColumnList.add(changeColumn.toUpperCase());
			}
		}
		if (excludeColumnList.size() == 0) {
			Validate.notEmpty(excludeColumnList, "Empty exclude column list not allowed");
		}
	}
	
	public String getPartitionKey() {
		if (partitionKey == null) {
			partitionKey = table.substring(0, table.length()-2) + "_ID";
			partitionKey = partitionKey.toUpperCase();
		}
		return partitionKey;
	}
	public void setPartitionKey(String partitionKey) {
		if (partitionKey != null) {partitionKey = partitionKey.toUpperCase();};
		
		partitionKey = partitionKey.toUpperCase();
	}
	public String getTable() {
		return table;
	}
	public void setTable(String table) {
		this.table = table;
	}
	public String getWhereClause() {
		return whereClause;
	}
	public void setWhereClause(String whereClause) {
		this.whereClause = whereClause;
	}

	public String getPrimaryKey() {
		if (primaryKey == null) {
			primaryKey = table + "_ID";
			primaryKey = primaryKey.toUpperCase();
		}
		return primaryKey;
	}
	public void setPrimaryKey(String primaryKey) {
		if (primaryKey != null) {primaryKey = primaryKey.toUpperCase();}
		this.primaryKey = primaryKey;
	}

	String getHistoryChangeTimesSqlQuery() {

		if (historyChangeTimesSqlQuery != null) {
			return historyChangeTimesSqlQuery;
		}
    
    // ESR3.1 CCR10112 -- added deleteWhereClause such that the query will also pickup
    // the delete record from history (even though the data between the delete record 
    // and the previous would be identical)
		
		String sqlStr = "SELECT :modifiedDate from "
						+ "("
						+ "SELECT :modifiedDate, :changeColumns, :leadChangeColumns from "
						+ "("
						+ "SELECT max(:primaryKey), :partitionKey, :modifiedDate, :renamedChangeColumns from :historyTable "
						+ ":whereClause group by :partitionKey, :modifiedDate, :groupByColumns"
						+ ")"
						+ ")"
						+ " where  :changeWhereClause"
						+ " union all "
						+ " select min(:modifiedDate) from :historyTable :whereClause group by :partitionKey" 
            + " union all "
            + " select max(:modifiedDate) from :historyTable :deleteWhereClause group by :partitionKey";

		StringBuffer renamedChangeColumns = new StringBuffer();
		StringBuffer groupByColumns = new StringBuffer();
		StringBuffer changeColumns = new StringBuffer();
		StringBuffer leadChangeColumns = new StringBuffer();
		StringBuffer changeWhereClause = new StringBuffer();
 
		Iterator iter = includeColumnList.iterator();
		int colCount = 0;
		while (iter.hasNext()) {
			String changeColumn = (String)iter.next();
			if (changeColumn != null) {
				changeColumn = changeColumn.trim();
				if (changeColumn.equals("")) {
					changeColumn = null;
				}
			}

			if (changeColumn != null) {
				colCount++;

				// renamed change columns
				if (colCount != 1) renamedChangeColumns.append(", ");
				renamedChangeColumns.append(changeColumn);
				renamedChangeColumns.append(" as col_");
				renamedChangeColumns.append(colCount);

				// group by columns
				if (colCount != 1) groupByColumns.append(", ");
				groupByColumns.append(changeColumn);

				// change columns
				if (colCount != 1) changeColumns.append(", ");
				changeColumns.append("col_");
				changeColumns.append(colCount);

				// lead change columns
				if (colCount != 1) leadChangeColumns.append(", ");
				leadChangeColumns.append("lead(");
				leadChangeColumns.append("col_");
				leadChangeColumns.append(colCount);
				leadChangeColumns.append(", 1, null) over(partition by ");
				leadChangeColumns.append(this.getPartitionKey());
				leadChangeColumns.append(" order by record_modified_date desc)");
				leadChangeColumns.append(" as p_col_");
				leadChangeColumns.append(colCount);
			}

			// change where clause
			if (colCount != 1) changeWhereClause.append(" OR ");
			changeWhereClause.append("col_");
			changeWhereClause.append(colCount);
			changeWhereClause.append(" != ");
			changeWhereClause.append("p_col_");
			changeWhereClause.append(colCount);
			changeWhereClause.append(" OR (");
			changeWhereClause.append("col_");
			changeWhereClause.append(colCount);
			changeWhereClause.append(" is null and ");
			changeWhereClause.append("p_col_");
			changeWhereClause.append(colCount);
			changeWhereClause.append(" is not null)");
			changeWhereClause.append(" OR (");
			changeWhereClause.append("col_");
			changeWhereClause.append(colCount);
			changeWhereClause.append(" is not null and ");
			changeWhereClause.append("p_col_");
			changeWhereClause.append(colCount);
			changeWhereClause.append(" is null)");
		}

    StringBuffer deleteWhereClause = new StringBuffer()
          .append(whereClause)
          .append(" and transaction_type_code = 'D'");
       
		sqlStr = sqlStr.replaceAll(":modifiedDate", "record_modified_date");
		sqlStr = sqlStr.replaceAll(":primaryKey", this.getPrimaryKey());
		sqlStr = sqlStr.replaceAll(":partitionKey", this.getPartitionKey());
		sqlStr = sqlStr.replaceAll(":historyTable", this.getTable());
		sqlStr = sqlStr.replaceAll(":whereClause", this.getWhereClause());

		sqlStr = sqlStr.replaceAll(":changeColumns", changeColumns.toString());
		sqlStr = sqlStr.replaceAll(":leadChangeColumns", leadChangeColumns.toString());
		sqlStr = sqlStr.replaceAll(":renamedChangeColumns", renamedChangeColumns.toString());
		sqlStr = sqlStr.replaceAll(":groupByColumns", groupByColumns.toString());
		sqlStr = sqlStr.replaceAll(":changeWhereClause", changeWhereClause.toString());

    sqlStr = sqlStr.replaceAll(":deleteWhereClause", deleteWhereClause.toString());

    historyChangeTimesSqlQuery = sqlStr;
		
		return historyChangeTimesSqlQuery;
	}

}
