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

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.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 Select.
 * @author egavaldo
 */
public class CLauncherImpl extends CLauncher implements IConstantsResults {

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

	static final String TRACE_HEADER = "{sql_select    }  ";

	private String sqlScriptRootPath;

	private String connectionUrl;
	private String username;
	private String password;
	private String driverJar;
	private String driverClassPath;

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

	private Connection connection = 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

			connectionUrl                       = getStringParamValue("Connection", "connection url");    // i.e. jdbc:mysql://localhost/xstudio_db
			username                            = getStringParamValue("Connection", "username");          // i.e. xstudio
			password                            = getStringParamValue("Connection", "password");          // i.e. xstudiopwd
			driverJar                           = getStringParamValue("Connection", "driver jar");        // i.e. Y:/mysql-connector-java-3.1.12/mysql-connector-java-3.1.12-bin.jar
			driverClassPath                     = getStringParamValue("Connection", "driver classpath");  // i.e. com.mysql.jdbc.Driver

			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(driverJar);
			URL driverUrl1 = driverFile1.toURI().toURL();
			URLClassLoader jarLoader1 = URLClassLoader.newInstance(new URL[] { driverUrl1 }, CLauncherImpl.class.getClassLoader());
			Class driverClass1 = Class.forName(driverClassPath, true, jarLoader1);
			DriverManager.registerDriver(new CDriverWrapper((Driver) driverClass1.newInstance()));
			traceln(LOG_PRIORITY_INFO, "connecting to " + connectionUrl + " with " + username + ":" + password + "...");
			connection = DriverManager.getConnection(connectionUrl, username, password);
		} catch (Exception e) {
			traceln(LOG_PRIORITY_SEVERE, e.getStackTrace().toString());
			traceln(LOG_PRIORITY_SEVERE, "connection to sql database  failed: " + e.getMessage());
			executionSteps.add(new CExecutionStep(RESULT_FAILURE, "couldn't connect to sql database"));
			return new CReturnStatus(RESULT_FAILURE, executionSteps);
		}

		executionSteps.add(new CExecutionStep(RESULT_SUCCESS, "successfully connected to the database"));
		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("sql_select_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>();

		String scriptPath = sqlScriptRootPath + "/" + testPath + "/" + testName + "_" + testcaseIndex + ".sql";
		File script = new File(scriptPath);
		if (!script.exists()) {
			executionSteps.add(new CExecutionStep(RESULT_FAILURE, scriptPath + " script  not found"));
			return new CReturnStatus(RESULT_FAILURE, executionSteps);
		}
		String scriptContent = CFileUtils.FileToString(script).trim();
		traceln(LOG_PRIORITY_INFO, "scriptContent (oracle)=" + scriptContent);

		writeLog("query:" + scriptContent, logFile);
		try {
			Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ResultSet resultSet = statement.executeQuery(scriptContent);
			if (resultSet.next()) {
				int count = 1;
				Vector<String> itemVector = new Vector<String>();
				itemVector.add(resultSet.getString(1));
				while (resultSet.next()) {
					count++;
					if (count < 100) {
						itemVector.add(resultSet.getString(1));
					} else if (count == 100) {
						itemVector.add("...");
						break;
					}
				}
				// Retrieve the number of records
				resultSet.last();
				int nombreLignes = resultSet.getRow();
				resultSet.beforeFirst();

				executionSteps.add(new CExecutionStep(RESULT_FAILURE, "result select: " + nombreLignes + " item(s) returned " + itemVector));
				return new CReturnStatus(RESULT_FAILURE, executionSteps);
			} else {
				executionSteps.add(new CExecutionStep(RESULT_SUCCESS, "result select:  no item returned"));
				return new CReturnStatus(RESULT_SUCCESS, executionSteps);
			}
		} catch (SQLException e) {
			traceln(LOG_PRIORITY_SEVERE, "SQL error: " + e.getMessage());
			executionSteps.add(new CExecutionStep(RESULT_FAILURE, e.getMessage()));
			return new CReturnStatus(RESULT_FAILURE, executionSteps);
		}

	}

	@Override
	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>();
		return new CReturnStatus(RESULT_SUCCESS, executionSteps);
	}

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

	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...");
		}
	}
}