package com.ibm.vista.sqltrigger;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.StringWriter;
import java.nio.charset.Charset;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.json.JSONException;
import org.json.JSONObject;

import com.ibm.vista.bi.common.OutputTable;

/**
 * This class executes SQL statements on a defined VISTA database. For running
 * with Java 1.8, make sure that db2jcc4.jar in version 11.x is included.
 * 
 * @author Gerry Broennimann
 *
 */
public class VistaSqlTrigger {

	private static boolean debug = true;

	/**
	 * Main method which starts the client
	 * 
	 * @param args
	 *            args[0] = number of iterations<br
	 *            args[1] = start index
	 */
	public static void main(String[] args) {
		String reportString = "";
		Long initialTime = System.currentTimeMillis();
		Long endTime = System.currentTimeMillis();
		Query query = null;

		// 1) load client configuration (DB connection parameters)
		ConfigMulti configMulti = new ConfigMulti();

		// 2) Load query data (sql, environments):
		query = new Query();
		String sql = "";
		try {
			sql = readFile("queries/query.sql", Charset.defaultCharset());
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		reportString += "SQL:\n" + sql 
				+ "\n-----------------------------------------------------------------------------\n\n";
		debug(reportString);

		// 3) Execute the SQL on each environment:
		for (int i = 0; i < query.environments.length; i++) {
			String environment = query.environments[i];

			JSONObject config = null;

			try {
				// fetch the current config (match query.json -> environments with configMulti.json -> db2DBAlias):
				for (int k = 0; k < configMulti.db2configs.length; k++) {
					config = configMulti.db2configs[k];

					if (config.has("db2DBAlias")
							&& config.getString("db2DBAlias").equalsIgnoreCase(
									environment)) {
						break;
					} else {
						config = null;
					}
				}

				if (config != null) {
					String startAlias = "#### Start "
							+ config.getString("db2DBAlias") + " ####";
					reportString += startAlias;		
					System.out.println(startAlias);

					OutputTable outputTable = processQueryTable(sql, query, config);
					StringWriter stringWriter = new StringWriter(50000);
					outputTable.print(stringWriter);
					String output = stringWriter.toString();
					
					reportString += "\n" + output + "\n";
					System.out.println(output);
					String endAlias = "#### End "
							+ config.getString("db2DBAlias") + " ####\n";
					reportString += endAlias + "\n";
					System.out.println(endAlias);
				} // end config != null
				else {
					String errorMsg = "Could not find configuration for db2DBAlias = "
							+ environment;
					reportString += "\nERROR: " + errorMsg + "\n";
					error(errorMsg);
				}

			} catch (JSONException e) {
				e.printStackTrace();
			}
		}
		endTime = System.currentTimeMillis();
		
		// Setting / formatting the report date
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");    
		Date reportDate = new Date(endTime);
		String reportDateString = "Report date: " + sdf.format(reportDate);
		reportString += "\n" + reportDateString;
		System.out.println(reportDateString);
		
		// Setting the execution time (in seconds)
		String executionTime = "Execution time: " + (endTime - initialTime)
				/ 1000.0 + " (sec)";
		reportString += "\n" + executionTime;
		System.out.println(executionTime);
		
		// Write the results to a file:
		writeReportFile(reportString);

	} // end main()
	
	/**
	 * Saves the query results to a file (/out/report.txt).
	 * @param s The query results as String
	 */
	private static void writeReportFile(String s) {
		FileWriter fw;
		try {
		    fw = new FileWriter(new File("out/report.txt"));
		    fw.write(s);
		    fw.close();

		} catch (IOException e) {
		    e.printStackTrace();
		}
	}

	/**
	 * Executes the defined SQL and puts the result in an OutputTable structure.
	 * 
	 * @param sql The SQL to be executed
	 * @param config
	 *            The config JSON containing DB connection parameters (URI,
	 *            username, password)
	 * @return formatted container for the query result
	 */
	private static OutputTable processQueryTable(String sql, Query query, JSONObject config) {
		OutputTable outputTable = new OutputTable();
		String jdbcClassName = "com.ibm.db2.jcc.DB2Driver";
		String db2Url = null;
		String db2DBAlias = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection connection = null;
		int maxRows = 100; // used for "fetch first xx rows only" (if not provided by user)

		try {
			db2Url = config.getString("db2Url") + "/"
					+ config.getString("db2DBName");
			db2DBAlias = config.getString("db2DBAlias");
			// Load class into memory
			Class.forName(jdbcClassName);
			// Establish connection
			debug("Connecting to DB2 (" + db2DBAlias + ")... ");
			String db2User = config.getString("db2User");
			String db2Password = config.getString("db2Password");
			// apply a fetch first xxx rows only security:
			//String sql = query.sql;
			if (sql.endsWith(";")) {
				sql = sql.substring(0, sql.length()-1);
			}
			if (!sql.toLowerCase().contains("fetch first")) {
				sql += " fetch first " + maxRows + " rows only";
			}

			connection = DriverManager.getConnection(db2Url, db2User,
					db2Password);

			if (connection != null) {
				debug("Connected successfully to " + db2Url);

				ps = connection.prepareStatement(sql);
				ps.setFetchSize(maxRows); // does not work with DB2???
				// Fire the SQL execution
				rs = ps.executeQuery();
				rs.setFetchSize(maxRows); // does not work with DB2???
				// wrap the resultSet into an OutputTable (formatting)
				boolean resultColumnNamesSet = false;
				while (rs.next()) {
					ResultSetMetaData md = rs.getMetaData();
					int colCount = md.getColumnCount();
					List row = new ArrayList(colCount);
					for (int i = 1; i <= colCount; i++) {
						if (!resultColumnNamesSet) {
							outputTable.addColumnName(md.getColumnName(i));
						}
						row.add(rs.getObject(i));
					}
					outputTable.addRow(row);
					resultColumnNamesSet = true;
				} // end while
				outputTable.setTitle("- " + query.queryname + "");
			} // end connection != null
			else {
				error("Could not connect to " + db2Url);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (JSONException e) {
			e.printStackTrace();
		} finally {
			// Clean-Up:
			if (connection != null) {
				try {
					if (!connection.isClosed()) {
						connection.close();
					}
				} catch (SQLException e) {
					OutputTable.ignoredException();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// Ignore:
					OutputTable.ignoredException();
				}
				rs = null;
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					// Ignore:
					OutputTable.ignoredException();
				}
				ps = null;
			}
		}
		return outputTable;
	}

	/**
	 * Prints debug messages to console, a System.out.println() replacement.
	 * Messages are only logged if static variable "debug" is set to true.
	 * 
	 * @param o Any object, normally a String.
	 */
	private static void debug(Object o) {
		if (debug)
			System.out.println(o);
	} // end debug()

	/**
	 * Prints an error message to console, a System.out.println() replacement.
	 * 
	 * @param o Any object, normally a String.
	 */
	private static void error(Object o) {
		System.out.println("ERROR: " + o.toString());
	} // end error()
	
	/**
	 * Utility method to simply read a file (requires Java 1.7).
	 * 
	 * @param path Path incl. file name
	 * @param encoding (e.g. UTF-8 - check what is available)
	 * @return The file content as String
	 * @throws IOException
	 */
	private static String readFile(String path, Charset encoding) throws IOException {
		byte[] encoded = Files.readAllBytes(Paths.get(path));
		return new String(encoded, encoding);
	}

} // end VistaSqlTrigger
