package com.xxxxxxx.drvpp.fw.common.test;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.MalformedURLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.dbunit.DatabaseUnitException;
import org.dbunit.database.AmbiguousTableNameException;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.Column;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ReplacementDataSet;
import org.dbunit.dataset.SortedTable;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.operation.DatabaseOperation;

public class DbTestUtil {
	private static class DbTestUtilHolder {
		private static final DbTestUtil instance = new DbTestUtil();
	}

	public static DbTestUtil getInstance() {
		return DbTestUtilHolder.instance;
	}

	private final static String REPLACE_NULL = "[null]";
	private final static String REPLACE_SYSTEM_TIME = "[system_time]";

	private FlatXmlDataSetBuilder builder = new FlatXmlDataSetBuilder();
	private IDatabaseConnection connection = null;
	private boolean isCommit = false;

	private Map<String, Object> replaceMap = new HashMap<String, Object>();
	private ColumnConverter columnConverter = new ColumnConverter() {
		@Override
		public String convert(String column, Object value) {
			if (value == null) {
				return null;
			}

			if (replaceMap.containsKey(value)) {
				value = replaceMap.get(value);
				if (value == null) {
					return null;
				}
			}

			String s = value.toString();
			if (value instanceof Timestamp) {
				int pos = s.indexOf('.');
				if (pos >= 0) {
					return s.substring(0, pos);
				}
			}
			return s;
		}
	};

	private DbTestUtil() {
		replaceMap.put(REPLACE_NULL, null);
		replaceMap.put(REPLACE_SYSTEM_TIME,
				new Timestamp(System.currentTimeMillis()));
	}

	public void connect() {
		try {
			if (connection == null){
				// TODO
				Connection conn = DriverManager.getConnection(
						"jdbc:hsqldb:hsql://localhost/", "sa", "");
				conn.setAutoCommit(false);
				connection = new DatabaseConnection(conn);
			}
			else {
				if (!isCommit){
					rollback();
				}
				isCommit = false;
			}
			// TODO
			// DatabaseConfig config = databaseConnection.getConfig();
			// config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new
			// HsqldbDataTypeFactory());
		} catch (SQLException e) {
			throw new TestUtilRuntimeException(e);
		} catch (DatabaseUnitException e) {
			throw new TestUtilRuntimeException(e);
		}
	}

	public void commit() {
		try {
			connection.getConnection().commit();
			isCommit = true;
		} catch (SQLException e) {
			throw new TestUtilRuntimeException(e);
		}
	}

	public void rollback() {
		try {
			connection.getConnection().rollback();
		} catch (SQLException e) {
			throw new TestUtilRuntimeException(e);
		}
	}

	public IDataSet cleanInsert(File file) {
		IDataSet dataSet = createDataSet(file);
		ReplacementDataSet replaceDataSet = new ReplacementDataSet(dataSet);
		Set<Entry<String, Object>> entrySet = replaceMap.entrySet();
		for (Iterator<Entry<String, Object>> it = entrySet.iterator(); it
				.hasNext();) {
			Entry<String, Object> entry = it.next();
			replaceDataSet.addReplacementObject(entry.getKey(),
					entry.getValue());
		}
		try {
			DatabaseOperation.CLEAN_INSERT.execute(connection, replaceDataSet);
		} catch (DataSetException e) {
			throw new TestUtilRuntimeException(e);
		} catch (DatabaseUnitException e) {
			throw new TestUtilRuntimeException(e);
		} catch (SQLException e) {
			throw new TestUtilRuntimeException(e);
		}
		return dataSet;
	}

	public IDataSet createDataSet(File file) {
		// TODO
		// builder.setColumnSensing(true);
		FlatXmlDataSet dataSet;
		try {
			dataSet = builder.build(file);
		} catch (MalformedURLException | DataSetException e) {
			throw new TestUtilRuntimeException(e);
		}
		return dataSet;
	}

	public IDataSet write(String tableName, File file) {
		return write(tableName, null, file);
	}

	public IDataSet write(String tableName, String query, File file) {
		QueryDataSet dataSet = new QueryDataSet(connection);
		try {
			dataSet.addTable(tableName, query);
			FlatXmlDataSet.write(dataSet, new FileOutputStream(file));
		} catch (AmbiguousTableNameException e) {
			throw new TestUtilRuntimeException(e);
		} catch (DataSetException e) {
			throw new TestUtilRuntimeException(e);
		} catch (FileNotFoundException e) {
			throw new TestUtilRuntimeException(e);
		} catch (IOException e) {
			throw new TestUtilRuntimeException(e);
		}

		return dataSet;
	}

	public boolean compare(IDataSet dataSet1, IDataSet dataSet2,
			String tableName) {
		try {
			SortedTable sortedTable1 = new SortedTable(
					dataSet1.getTable(tableName));
			SortedTable sortedTable2 = new SortedTable(
					dataSet2.getTable(tableName));

			int count1 = sortedTable1.getRowCount();
			int count2 = sortedTable2.getRowCount();
			if (count1 != count2) {
				return false;
			}

			Column[] columns = sortedTable1.getSortColumns();
			for (int i = 0; i < count1; i++) {
				for (int j = 0; j < columns.length; j++) {
					String columnName = columns[j].getColumnName();
					Object o1 = sortedTable1.getValue(i, columnName);
					Object o2 = sortedTable2.getValue(i, columnName);

					String s1 = columnConverter.convert(columnName, o1);
					String s2 = columnConverter.convert(columnName, o2);

					if (!(s1 == null && s2 == null) && !s1.equals(s2)) {
						return false;
					}
				}
			}
			return true;
		} catch (DataSetException e) {
			throw new TestUtilRuntimeException(e);
		}
	}

	public Map<String, Object> getReplaceMap() {
		return replaceMap;
	}

	public void setReplaceMap(Map<String, Object> replaceMap) {
		this.replaceMap = replaceMap;
	}

	public ColumnConverter getColumnConverter() {
		return columnConverter;
	}

	public void setColumnConverter(ColumnConverter columnConverter) {
		this.columnConverter = columnConverter;
	}
}
