/**
 * 
 */
package org.testcase;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * @author u337668
 *
 */
public class TestCaseUtils {
	private static Log logger = LogFactory.getLog(TestCaseUtils.class);

	private static String testConnectionQuery;
	private static String testIsolationQuery;
	
	static {
		Properties sqlProps = new Properties();
		boolean propertiesLoaded = false;
		try {
			
			InputStream in = TestCaseUtils.class.getClassLoader().getResourceAsStream("sql.properties");
			if( in != null ) {
				sqlProps.load(in);
				in.close();
				
				testConnectionQuery = sqlProps.getProperty("testcase.sql.testconnection");
				testIsolationQuery = sqlProps.getProperty("testcase.sql.testisolation");
			
				logger.info("SQL properties have been successfully initialized !");
				
				propertiesLoaded = true;
			}
		} catch (Exception e) {
			logger.error("Can not load sql.properties\n", e);
		} finally {
			if( !propertiesLoaded ) {
				logger.warn("Assigning default values for expected properties");
				logger.warn("testcase.sql.testconnection = select 1 from dual");
				logger.warn("testcase.sql.testisolation  = select 1, 2 from dual");
				testConnectionQuery = "select 1 from dual";
				testIsolationQuery = "select 1, 2 from dual";
			}
		}


	}


	/**
	 * @param testcase 
	 * @param conn
	 * @throws SQLException
	 */
	public static void logIsolationLevel(String testcase, Connection conn) {
		int isolationLevel = 0;
		try {
			isolationLevel = conn.getTransactionIsolation();
		} catch (SQLException e) {
			logger.error(testcase+" -Database access error when retrieving the transaction isolation level");
			return;
		}

		switch( isolationLevel ) {
		case Connection.TRANSACTION_NONE:
			logger.info(testcase+" - Isolation level : TRANSACTION_NONE");
			break;
		case Connection.TRANSACTION_READ_COMMITTED:
			logger.info(testcase+" - Isolation level : TRANSACTION_READ_COMMITTED");
			break;
		case Connection.TRANSACTION_READ_UNCOMMITTED:
			logger.info(testcase+" - Isolation level : TRANSACTION_READ_UNCOMMITTED");
			break;
		case Connection.TRANSACTION_REPEATABLE_READ:
			logger.info(testcase+" - Isolation level : TRANSACTION_REPEATABLE_READ");
			break;
		case Connection.TRANSACTION_SERIALIZABLE:
			logger.info(testcase+" - Isolation level : TRANSACTION_SERIALIZABLE");
			break;
		default:
			logger.info(testcase+" - Isolation level : Unknown ("+isolationLevel+")");
		}
	}


	/**
	 * @param testcase
	 * @param conn
	 * @param rollbackOnError
	 */
	public static void testChangeIsolationLevel(String testcase, Connection conn) {
		testChangeIsolationLevel(testcase, conn, false);
	}

	/**
	 * @param testcase
	 * @param conn
	 * @param jtaEnabled
	 */
	public static void testChangeIsolationLevel(String testcase, Connection conn, boolean jtaEnabled) {

		logIsolationLevel(testcase, conn);

		int oldIsolation = Connection.TRANSACTION_NONE;
		
		if( !jtaEnabled ) {
			try {
				oldIsolation = conn.getTransactionIsolation();
			} catch (SQLException e) {
				logger.error(testcase+" - Database access error when retrieving the transaction isolation level", e);
				return;
			}

			try {
				conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			} catch (SQLException e) {
				logger.error(testcase+" - Database access error when setting the transaction isolation level", e);
				return;
			}
		}
		try {
			testConnectionIsolationLevel(conn);
		} catch (SQLException e) {
			logger.error(testcase+" - Error during the transaction isolation level test", e);
			return;
		}


		int testIsolation;
		try {
			testIsolation = conn.getTransactionIsolation();
		} catch (SQLException e) {
			logger.error(testcase+" - Database access error when retrieving the transaction isolation level", e);
			return;
		}

		if( testIsolation == Connection.TRANSACTION_SERIALIZABLE ) {
			logger.info(testcase+" - Test for changing isolation level is OK");
		} else {
			logger.error(testcase+" - Test for changing isolation level is KO");
		}

		if( !jtaEnabled ) {
			try {
				conn.rollback();
				logger.info(testcase + " - Rollback OK ...");
			} catch (SQLException e) {
				logger.error( "Error when making a rollback on the connection:\n", e );
				return;
			}
			logIsolationLevel(testcase, conn);


			try {
				conn.setTransactionIsolation(oldIsolation);
			} catch (SQLException e) {
				logger.error(testcase+" - Database access error when setting back the old transaction isolation level:\n"+e.getMessage());
				return;
			}
			logIsolationLevel(testcase, conn);
		} else {
			logger.info(testcase + " - No rollback on the transaction ...");
		}
	}

	/**
	 * @param conn
	 * @throws Exception 
	 * @throws SQLException 
	 */
	public static void testConnection( Connection conn ) throws SQLException {

		Statement stat = null;
		try {
			stat = conn.createStatement();
		} catch (SQLException e) {
			logger.error( "Error when creating the statement:\n", e );
			throw e;
		}

		ResultSet rs = null;
		try {
			rs = stat.executeQuery(testConnectionQuery);
		} catch (SQLException e) {
			logger.error( "Error when executing the statement:\n", e );
			cleaningSQLResources(stat, rs);
			throw e;
		}

		int count = 0;
		try {
			while( rs.next() ) {
				count += rs.getInt(1);
			}
		} catch (SQLException e) {
			logger.error( "Error while browsing the result set :\n", e );
		} finally {
			cleaningSQLResources(stat, rs);
		}

		logger.info("Test Connection : Result : count = "+count);
	}

	/**
	 * @param conn
	 * @throws Exception 
	 * @throws SQLException 
	 */
	public static void testConnectionIsolationLevel( Connection conn ) throws SQLException {

		Statement stat = null;
		try {
			stat = conn.createStatement();
		} catch (SQLException e) {
			logger.error( "Error when creating the statement:\n", e );
			throw e;
		}

		ResultSet rs = null;
		try {
			//
			rs = stat.executeQuery(testIsolationQuery);
		} catch (SQLException e) {
			logger.error( "Error when executing the statement:\n", e );
			cleaningSQLResources(stat, rs);
			throw e;
		}

		String globalLevel = "";
		String sessionLevel = "";
		try {
			while( rs.next() ) {
				globalLevel = rs.getString(1);
				sessionLevel = rs.getString(2);

				logger.info("Test Result : global = "+globalLevel+" session = "+sessionLevel);
			}
		} catch (SQLException e) {
			logger.error( "Error while browsing the result set :\n", e );
		} finally {
			cleaningSQLResources(stat, rs);
		}
	}


	/**
	 * @param stat
	 * @param rs
	 * @throws SQLException
	 */
	public static void cleaningSQLResources(Statement stat, ResultSet rs ) throws SQLException {

		SQLException t = null; 

		if( rs != null ) {

			try {
				rs.clearWarnings();
			} catch (SQLException e) {
				logger.error( "Error when clearing the result set:\n", e );
				t = e;
			}

			try {
				rs.close();
			} catch (SQLException e) {
				logger.error( "Error when clearing the result set:\n", e );
				if( t == null ) {
					t = e;
				}
			}
		}

		if( stat != null ) {
			try {
				stat.close();
			} catch (SQLException e) {
				logger.error( "Error when closing the statement:\n", e );
				if( t == null ) {
					t = e;
				}
			}
		}

		if( t!= null ) {
			throw t;
		}
	}

}
