/*
+----------------------------------------------------------------------+
|                    Class: CLauncherImpl                              |
|                                                                      |
| Developper:  Eric Gavaldo (eric.gavaldo@xqual.com)                   |
| Version:     1.0                                                     |
+----------------------------------------------------------------------+
 */
package com.xqual.xlauncher.sql_compare;

import java.io.File;
import java.io.FileWriter;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Vector;

import com.xqual.xagent.launcher.CExecutionStep;
import com.xqual.xagent.launcher.CLauncher;
import com.xqual.xagent.launcher.CParamParsingException;
import com.xqual.xagent.launcher.CReturnStatus;
import com.xqual.xagent.launcher.runner.CRunner;
import com.xqual.xagent.launcher.runner.IRunner;
import com.xqual.xcommon.CAttribute;
import com.xqual.xcommon.CDriverWrapper;
import com.xqual.xcommon.IConstantsResults;
import com.xqual.xcommon.utils.CCalendarUtils;
import com.xqual.xcommon.utils.CFileUtils;

/**
 * The <code>CLauncherImpl</code> implementation of <code>ILauncher</code> for SQL Compare.
 * @author egavaldo
 */
public class CLauncherImpl extends CLauncher implements IConstantsResults {

	// +==============================================================+
	// | Attributes                                                   |
	// +==============================================================+

	static final String TRACE_HEADER = "{sql_compare   }  ";

	// parameters impacting executing at run time set by the test operator
	private String sqlScriptRootPath;

	private String connectionUrl1;
	private String username1;
	private String password1;
	private String driverJar1;
	private String driverClassPath1;
	private boolean writeAllValues1;

	private String connectionUrl2;
	private String username2;
	private String password2;
	private String driverJar2;
	private String driverClassPath2;
	private boolean writeAllValues2;

	private boolean runCommandRemotelyBeforeRunningTest;
	private String plinkExe;
	private String plinkRemoteHost;
	private String plinkUsername;
	private String plinkPassword;
	private String command;

	private Connection connection1 = null;
	private Connection connection2 = null;

	File resultLogFile1 = null;
	File resultLogFile2 = null;

	// +==============================================================+
	// | Constructors                                                 |
	// +==============================================================+

	public CLauncherImpl() {
		super(TRACE_HEADER);
	}

	// +==============================================================+
	// | Methods                                                      |
	// +==============================================================+

	@Override
	public CReturnStatus initialize(int sutId, String sutName, String sutVersion) {
		setSutDetails(sutId, sutName, sutVersion);

		setDefaultTestcaseMustBeCreated(true); // if there is not testcase, let the systems create a default one

		// check the configuration sent by the manager
		printConfiguration();

		Vector<CExecutionStep> executionSteps = new Vector<CExecutionStep>();
		try {
			// retrieve the parameters we need
			sqlScriptRootPath                   = getStringParamValue("SQL", "sql script root path");        // i.e. Y:/XStudio/src

			connectionUrl1                      = getStringParamValue("Connection1", "connection url");      // i.e. jdbc:mysql://localhost/xstudio_db
			username1                           = getStringParamValue("Connection1", "username");            // i.e. xstudio
			password1                           = getStringParamValue("Connection1", "password");            // i.e. xstudiopwd
			driverJar1                          = getStringParamValue("Connection1", "driver jar");          // i.e. Y:/mysql-connector-java-3.1.12/mysql-connector-java-3.1.12-bin.jar
			driverClassPath1                    = getStringParamValue("Connection1", "driver classpath");    // i.e. com.mysql.jdbc.Driver
			writeAllValues1                     = getBooleanParamValue("Connection1", "write all values");

			connectionUrl2                      = getStringParamValue("Connection2", "connection url");      // i.e. jdbc:mysql://localhost/xstudio_db
			username2                           = getStringParamValue("Connection2", "username");            // i.e. xstudio
			password2                           = getStringParamValue("Connection2", "password");            // i.e. xstudiopwd
			driverJar2                          = getStringParamValue("Connection2", "driver jar");          // i.e. Y:/mysql-connector-java-3.1.12/mysql-connector-java-3.1.12-bin.jar
			driverClassPath2                    = getStringParamValue("Connection2", "driver classpath");    // i.e. com.mysql.jdbc.Driver
			writeAllValues2                     = getBooleanParamValue("Connection2", "write all values");

			runCommandRemotelyBeforeRunningTest = getBooleanParamValue("Scripts", "run command remotely before running test");
			plinkExe                            = getStringParamValue("Scripts", "plink executable");        // i.e. C:/progra~1/Putty/plink.exe -batch
			plinkRemoteHost                     = getStringParamValue("Scripts", "remote host");
			plinkUsername                       = getStringParamValue("Scripts", "username");
			plinkPassword                       = getStringParamValue("Scripts", "password");
			command                             = getStringParamValue("Scripts", "command");                 // i.e. grep /~eric/ /var/log/httpd/access.log > fredlog
		} catch (CParamParsingException e) {
			traceln(LOG_PRIORITY_SEVERE, "parsing error during initialization");
			executionSteps.add(new CExecutionStep(RESULT_FAILURE, e.getMessage()));
			return new CReturnStatus(RESULT_FAILURE, executionSteps);
		}

		try {
			File driverFile1 = new File(driverJar1);
			URL driverUrl1 = driverFile1.toURI().toURL();
			URLClassLoader jarLoader1 = URLClassLoader.newInstance(new URL[] { driverUrl1 }, CLauncherImpl.class.getClassLoader());
			Class driverClass1 = Class.forName(driverClassPath1, true, jarLoader1);
			DriverManager.registerDriver(new CDriverWrapper((Driver) driverClass1.newInstance()));
			traceln(LOG_PRIORITY_INFO, "connecting to " + connectionUrl1 + " with " + username1 + ":" + password1 + "...");
			connection1 = DriverManager.getConnection(connectionUrl1, username1, password1);
		} catch (Exception e) {
			traceln(LOG_PRIORITY_SEVERE, e.getStackTrace().toString());
			traceln(LOG_PRIORITY_SEVERE, "connection to sql database 1 failed: " + e.getMessage());
			executionSteps.add(new CExecutionStep(RESULT_FAILURE, "couldn't connect to sql database 1"));
			return new CReturnStatus(RESULT_FAILURE, executionSteps);
		}

		try {
			File driverFile2 = new File(driverJar2);
			URL driverUrl2 = driverFile2.toURI().toURL();
			URLClassLoader jarLoader2 = URLClassLoader.newInstance(new URL[] { driverUrl2 }, CLauncherImpl.class.getClassLoader());
			Class driverClass2 = Class.forName(driverClassPath2, true, jarLoader2);
			DriverManager.registerDriver(new CDriverWrapper((Driver) driverClass2.newInstance()));
			traceln(LOG_PRIORITY_INFO, "connecting to " + connectionUrl2 + " with " + username2 + ":" + password2 + "...");
			connection2 = DriverManager.getConnection(connectionUrl2, username2, password2);
		} catch (Exception e) {
			traceln(LOG_PRIORITY_SEVERE, "connection to sql database 2 failed: " + e.getMessage());
			traceln(LOG_PRIORITY_SEVERE, e.getStackTrace().toString());
			executionSteps.add(new CExecutionStep(RESULT_FAILURE, "couldn't connect to sql database 2"));
			return new CReturnStatus(RESULT_FAILURE, executionSteps);
		}

		executionSteps.add(new CExecutionStep(RESULT_SUCCESS, "successfully connected to both sql databases"));
		return new CReturnStatus(RESULT_SUCCESS, executionSteps);
	}

	@Override
	public CReturnStatus preRun(int testId, String testPath, String testName, Vector<CAttribute> attributes, String additionalInfo) {
		traceln(LOG_PRIORITY_INFO, "preRun testId=" + testId + " testPath=" + testPath + " [" + testName + "]...");
		Vector<CExecutionStep> executionSteps = new Vector<CExecutionStep>();

		if (runCommandRemotelyBeforeRunningTest) {
			traceln(LOG_PRIORITY_INFO, "running the command remotely...");
			CRunner pLinkRunner = new CRunner("[" + testId + "] " + testPath + ":" + testName, plinkExe + " " + plinkRemoteHost + " -l " + plinkUsername + " -pw " + plinkPassword + " " + "\"" + command + "\"");
			Calendar startTime = Calendar.getInstance();
			short resultCommand = pLinkRunner.requestAction(IRunner.START_PROCESS, IRunner.WAIT_END_OF_EXECUTION);
			Calendar stopTime = Calendar.getInstance();
			long duration = stopTime.getTimeInMillis() - startTime.getTimeInMillis();
			if (resultCommand == RESULT_FAILURE) {
				executionSteps.add(new CExecutionStep(resultCommand, "plink failed in " + CCalendarUtils.millisTimeToDurationString(duration)));
				return new CReturnStatus(RESULT_FAILURE, executionSteps);
			} else {
				executionSteps.add(new CExecutionStep(RESULT_SUCCESS, "plink completed in " + CCalendarUtils.millisTimeToDurationString(duration)));
			}
		}

		return new CReturnStatus(RESULT_SUCCESS, executionSteps);
	}

	@Override
	public CReturnStatus run(int testId, String testPath, String testName, int testcaseIndex, String testcaseName, String additionalInfo) {
		File logFile = new File("sqlCompare_log" + testcaseIndex + ".txt");

		if (writeAllValues1) resultLogFile1 = new File("Results1_log" + testcaseIndex + ".txt");
		if (writeAllValues2) resultLogFile2 = new File("Results2_log" + testcaseIndex + ".txt");

		traceln(LOG_PRIORITY_INFO, "run testId=" + testId + " testPath=" + testPath + ":" + testName + " testcaseIndex=" + testcaseIndex + "...");
		writeLog("\n\n\n" + "Executing test: " + testName + " testcaseIndex=" + testcaseIndex, logFile);
		Vector<CExecutionStep> executionSteps = new Vector<CExecutionStep>();

		// +-----------------+
		// |     script 1    |
		// +-----------------+
		String scriptPath1 = sqlScriptRootPath + "/" + testPath + "/" + testName + "_" + testcaseIndex + "_a.sql";
		File script1 = new File(scriptPath1);
		if (!script1.exists()) {
			executionSteps.add(new CExecutionStep(RESULT_FAILURE, scriptPath1 + " script 1 not found"));
			return new CReturnStatus(RESULT_FAILURE, executionSteps);
		}
		String scriptContent1 = CFileUtils.FileToString(script1).trim();
		writeLog("Query 1:" + scriptContent1, logFile);
		if (writeAllValues1) writeLog("Query 1:" + scriptContent1, resultLogFile1);

		// +-----------------+
		// |     script 1    |
		// +-----------------+
		String scriptPath2 = sqlScriptRootPath + "/" + testPath + "/" + testName + "_" + testcaseIndex + "_b.sql";
		File script2 = new File(scriptPath2);
		if (!script2.exists()) {
			executionSteps.add(new CExecutionStep(RESULT_FAILURE, scriptPath2 + " script 2 not found"));
			return new CReturnStatus(RESULT_FAILURE, executionSteps);
		}
		String scriptContent2 = CFileUtils.FileToString(script2).trim();
		writeLog("Query: 2" + scriptContent2, logFile);
		if (writeAllValues2) writeLog("Query 2:" + scriptContent2, resultLogFile2);



		Vector<String> itemVector = new Vector<String>();
		int count = 1;
		boolean error = false;
		try {

			Statement statement1 = connection1.createStatement();
			Statement statement2 = connection2.createStatement();
			ResultSet resultSet1 = statement1.executeQuery(scriptContent1);
			ResultSet resultSet2 = statement2.executeQuery(scriptContent2);

			ResultSetMetaData rsmd1 = resultSet1.getMetaData();
			int numberOfColumns1 = rsmd1.getColumnCount();

			ResultSetMetaData rsmd2 = resultSet2.getMetaData();
			int numberOfColumns2 = rsmd2.getColumnCount();

			boolean sameNumberOfColumn = numberOfColumns1 == numberOfColumns2;

			if (!sameNumberOfColumn) {
				executionSteps.add(new CExecutionStep(RESULT_FAILURE, "1st query Results have " + numberOfColumns1 + " column(s) and " +
						                                              "2nd query Results have " + numberOfColumns2 + " column(s) => cannot compare"));
			}

			while (resultSet1.next()) {
				resultSet2.next();
				traceln(LOG_PRIORITY_INFO, "[Record at line: " + count + "]");
				writeLog("\nRecord at line: " + count + "", logFile);
				writeResults("\nRecord at line: " + count + "");

				for (int i = 1; i <= numberOfColumns1; i++) {
					String columnsNameMysql = rsmd1.getColumnName(i);
					String columnsNameOracle = rsmd2.getColumnName(i);

					String field1 = resultSet1.getString(i);
					String field2 = resultSet2.getString(i);

					if (field1 == null) field1 = "null";
					if (field2 == null) field2 = "null";

					traceln(LOG_PRIORITY_INFO, "> " + columnsNameMysql + " (query 1)=" + field1);
					writeLog(columnsNameMysql + " (query 1)=" + field1, logFile);
					if (writeAllValues1) writeLog(columnsNameMysql + "(query 1)=" + field1, resultLogFile1);

					traceln(LOG_PRIORITY_INFO, "> " + columnsNameOracle + " (query 2)=" + field2);
					writeLog(columnsNameOracle + " (query 2)=" + field2, logFile);
					if (writeAllValues2) writeLog(columnsNameOracle + " (query 2)=" + field2, resultLogFile2);

					traceln(LOG_PRIORITY_INFO, "\n");
					writeLog("", logFile);
					writeResults("");

					if (field1.equals(field2)) {
						itemVector.add(field1);
					} else {
						executionSteps.add(new CExecutionStep(RESULT_FAILURE, "results differ at line #" + count + "and column #" + i + " where " + field1 + " != " + field2));
						writeLog("ERROR: results differ at line #" + count + "and column #" + i + " where " + field1 + " != " + field2, logFile);
						executionSteps.add(new CExecutionStep(RESULT_FAILURE, "first correct results: " + itemVector));
						writeLog("first correct results: " + itemVector, logFile);
						error = true;
						if (!writeAllValues1 && !writeAllValues2) {
							addAttachment(logFile);
							return new CReturnStatus(RESULT_FAILURE, executionSteps);
						}
					}
				}
				count++;
			}

		} catch (SQLException e) {
			traceln(LOG_PRIORITY_SEVERE, e.getMessage() + " / parsing error during comparison at entry " + count);
			executionSteps.add(new CExecutionStep(RESULT_FAILURE, e.getMessage() + " / parsing error during comparison at entry " + count));

			addAttachment(logFile);
			if (writeAllValues1) addAttachment(resultLogFile1);
			if (writeAllValues2) addAttachment(resultLogFile2);

			return new CReturnStatus(RESULT_FAILURE, executionSteps);
		}

		addAttachment(logFile);
		if (writeAllValues1) addAttachment(resultLogFile1);
		if (writeAllValues2) addAttachment(resultLogFile2);

		if (!error) {
			executionSteps.add(new CExecutionStep(RESULT_SUCCESS, "results are equals\n Test sucess"));
			writeLog("results are equals\n Test sucess", logFile);
			return new CReturnStatus(RESULT_SUCCESS, executionSteps);
		} else {
			return new CReturnStatus(RESULT_FAILURE, executionSteps);
		}

	}

	public CReturnStatus postRun(int testId, String testPath, String testName) {
		traceln(LOG_PRIORITY_INFO, "postRun testId=" + testId + " testPath=" + testPath + ":" + testName + "...");

		Vector<CExecutionStep> executionSteps = new Vector<CExecutionStep>();
		executionSteps.add(new CExecutionStep(RESULT_SUCCESS, "post-run successful"));
		return new CReturnStatus(RESULT_SUCCESS, executionSteps);
	}

	public CReturnStatus terminate() {
		return new CReturnStatus(RESULT_SUCCESS);
	}

	private void writeResults(String string) {
		if (writeAllValues1) writeLog(string, resultLogFile1);
		if (writeAllValues2) writeLog(string, resultLogFile2);
	}

	private void writeLog(String s, File f) {
		try {
			FileWriter aWriter = new FileWriter(f, true);
			aWriter.write(s + System.getProperty("line.separator"));
			aWriter.flush();
			aWriter.close();
		} catch (java.io.IOException ex) {
			traceln(LOG_PRIORITY_INFO, "Problem with log file...");
		}
	}
}