/* main with args (used for insert minipartners):

long iterations = 1;
		long startIndex = 0;

// 0) read application parameters: args[0] = number of SQL executions,
		// args
		if (args != null && args.length >= 1 && args[0] != null) {
			try {
				iterations = Long.parseLong(args[0]);
				debug("Application parameter (0) captured, will be used for number of iterations: "
						+ iterations);
			} catch (NumberFormatException e) {
				debug("Application parameter (0) is not a valid number (long), using 1 iteration instead.");
			}
		}
		if (args != null && args.length >= 2 && args[1] != null) {
			try {
				startIndex = Long.parseLong(args[1]);
				debug("Application parameter (1) captured, will be used as startIndex: "
						+ startIndex);
			} catch (NumberFormatException e) {
				debug("Application parameter (1) is not a valid number (long), using 0 as startIndex instead.");
			}
		}
*/


/**
	 * Add curly brackets and a key around an JSONArray-string, transforming it
	 * into valid JSON. From: [{a}, {b}] To: {type: [{a}, {b}]}
	 * 
	 * @param json
	 *            JSONArray string, in format [{a}, {b}]
	 * @param type
	 *            the key to add
	 * @return data in valid JSON notation
	 */
	private static String curly(String json, String type) {
		if (type != null)
			return json = "{" + type + ": " + json + "}";
		else
			return json = "{" + json + "}";
	} // end curly()

/**
	 * Executes a simple select SQL on vista.tpvpartner to check the connection.
	 * 
	 * @param config
	 *            client configuration object (contains url to bluemix)
	 * @return JSONObject containing the query result in JSON format
	 */
	private static JSONObject executeTestSQL(Config config) {

		JSONArray resultArray = null;
		JSONObject resultObj = null;
		String jdbcClassName = "com.ibm.db2.jcc.DB2Driver";
		String db2Url = config.db2Url + "/" + config.db2DBName;

		Connection connection = null;
		try {
			// Load class into memory
			Class.forName(jdbcClassName);
			// Establish connection
			debug("Connecting to DB2: " + db2Url);
			connection = DriverManager.getConnection(db2Url, config.db2User,
					config.db2Password);

			if (connection != null) {
				debug("Connected successfully to DB2: " + db2Url);
				PreparedStatement pstmt = null;
				String sql = "select count(*) as cnt_partners from vista.kpvpartner";
				pstmt = connection.prepareStatement(sql);
				// fire the SQL execution
				ResultSet queryResult = pstmt.executeQuery();
				if (queryResult == null) {
					debug("Error: no results found");
				} else {
					resultArray = ResultSetConverter.convert(queryResult);
					// the resultArray contains "raw" data like [{row1},{row2}].
					// we now need to enrich it with canton, application,
					// report_date, report_type
					resultObj = new JSONObject();
					java.util.Date date = new java.util.Date();
					String dateString = new Timestamp(date.getTime())
							.toString();
					resultObj.put("application", "PV");
					resultObj.put("description", "Test description");
					resultObj.put("report_type", "test");
					resultObj.put("report_date", dateString);
					resultObj.put("report_data", resultArray);
				}
				connection.close();
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (JSONException e) {
			e.printStackTrace();
		}
		return resultObj;
	}

	/**
	 * Executes a simple select SQL on vista.tpvpartner to check the connection.
	 * 
	 * @param config
	 *            db2 configuration object
	 * @param sql
	 *            The SQL to be executed
	 * @return JSONObject containing the query result in JSON format
	 */
	private static JSONObject executeSQL(JSONObject config, String sql) {

		JSONArray resultArray = null;
		JSONObject resultObj = null;
		String jdbcClassName = "com.ibm.db2.jcc.DB2Driver";
		String db2Url = null;
		try {
			db2Url = config.getString("db2Url") + "/"
					+ config.getString("db2DBName");
		} catch (JSONException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		Connection connection = null;
		try {
			// Load class into memory
			Class.forName(jdbcClassName);
			// Establish connection
			debug("Connecting to DB2: " + db2Url);
			String db2User = config.getString("db2User");
			String db2Password = config.getString("db2Password");

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

			if (connection != null) {
				debug("Connected successfully to DB2: " + db2Url);
				PreparedStatement pstmt = null;
				// String sql =
				// "select count(*) as cnt_partners from vista.kpvpartner";
				pstmt = connection.prepareStatement(sql);
				// fire the SQL execution
				ResultSet rs = pstmt.executeQuery();
				if (rs == null) {
					debug("Error: no results found");
				} else {
					/*
					 * resultArray = ResultSetConverter.convert(rs); // the
					 * resultArray contains "raw" data like [{row1},{row2}]. //
					 * we now need to enrich it with canton, application,
					 * report_date, report_type
					 */

					resultObj = new JSONObject();
					java.util.Date date = new java.util.Date();
					String dateString = new Timestamp(date.getTime())
							.toString();
					resultObj.put("application", "PV");
					resultObj.put("description", "Test description");
					resultObj.put("query_type", "test");
					resultObj.put("query_date", dateString);
					resultObj.put("query_data", resultArray);

					// Print:
					displayResultSet(rs);
					/*
					 * ResultSetMetaData rsmd = rs.getMetaData();
					 * 
					 * int numberOfColumns = rsmd.getColumnCount(); for (int i =
					 * 1; i <= numberOfColumns; i++) { if (i > 1)
					 * System.out.print(";\t"); String columnName =
					 * rsmd.getColumnName(i); //System.out.print(columnName);
					 * System.out.printf("%12.12s", columnName.trim()); }
					 * System.out.println("");
					 * 
					 * while (rs.next()) { for (int i = 1; i <= numberOfColumns;
					 * i++) { if (i > 1) System.out.print(";\t"); String
					 * columnValue = rs.getString(i);
					 * //System.out.print(columnValue);
					 * System.out.printf("%12.12s",
					 * columnValue.toString().trim()); } System.out.println("");
					 * }
					 */
				}
				connection.close();
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (JSONException e) {
			e.printStackTrace();
		}
		return resultObj;
	}

	/**
	 * Inserts (Mini-)Partners in PV. Note: no STA-Roles are created. Also, VNRs
	 * are not validated.<br>
	 * name = "PV_SqlTrigger_" + i;<br>
	 * firstname = "Firstname_" + i;<br>
	 * socialsecnr = 10000000000 + i;<br>
	 * personid = 7561000000000 + i;<br>
	 * 
	 * @param iterations
	 *            number of partners to be inserted
	 * @param config
	 *            client configuration object (contains url to bluemix)
	 * @return JSONObject containing the query result in JSON format
	 */
	private static JSONObject insertPVMiniPartners(long iterations,
			long startIndex, Config config) {

		JSONArray resultArray = null;
		JSONObject resultObj = null;
		String jdbcClassName = "com.ibm.db2.jcc.DB2Driver";
		String db2Url = config.db2Url + "/" + config.db2DBName;

		Connection connection = null;
		try {
			// Load class into memory
			Class.forName(jdbcClassName);
			// Establish connection
			debug("Connecting to DB2: " + db2Url);
			connection = DriverManager.getConnection(db2Url, config.db2User,
					config.db2Password);

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

				long socialsecnrInitialValue = 10000000000L + startIndex;
				long personidInitialValue = 7561000000000L + startIndex;
				// String kpvpartidSQL =
				// "insert into vista.kpvpartner values (next value for vista.vista_seq)";
				String kpvpartidSQL = "insert into vista.kpvpartner values (next value for vista.vista_seq)";
				// String kpvpartidNext =
				// "(select (max(kpvpartid) + 0) from vista.kpvpartner)";
				// String tpvpartidNext =
				// "(select (max(kpvpartid) + 1) from vista.kpvpartner)"; //
				// "next value for vista.vista_seq";
				// String kpvpartidNext = "values prevval for vista.vista_seq";
				// String tpvpartidNext = "values nextval for vista.vista_seq";
				String kpvpartidNext = "(previous value for vista.vista_seq)";
				String tpvpartidNext = "(next value for vista.vista_seq)";

				String insertSQL = "insert into vista.tpvpartner "
						+ "(tpvpartid, kpvpartid, cpartnertype, socialsecnr, personid, name, firstname,"
						+ "clanguage, birthdate, birthdateprec, cconfidlevel, csex, remarks, "
						+ "temp, validfrom, validto, lastupdated) "
						+ "values ("
						+ tpvpartidNext
						+ ", "
						+ kpvpartidNext
						+ ", 'PartnerTyp#NP', ?, ?, ?, ?, "
						+ "'CorresLang#D', '1990-01-01', 0, 'Protection#0', 'Sex#M', 'VistaSqlTrigger', "
						+ "'N', '1990-01-01', '9999-12-31', current timestamp)";
				String controlSQL = "select kpvpartid, tpvpartid, name, firstname, personid from vista.tpvpartner "
						+ "order by lastupdated desc fetch first 1 rows only";
				PreparedStatement pstmt = null;
				ResultSet queryResult = null;

				// step-by-step execution
				for (int i = 0; i < iterations; i++) {
					// prepare unique names and VNRs:
					String name = "PV_SqlTrigger_" + startIndex + i;
					String firstname = "Firstname_" + startIndex + i;
					String socialsecnr = "" + (socialsecnrInitialValue + i);
					String personid = "" + (personidInitialValue + i);

					// insert a new kpvpartid key:
					pstmt = connection.prepareStatement(kpvpartidSQL);
					int rows = pstmt.executeUpdate();
					debug("kpvpartid #" + (i + 1) + " inserted (of "
							+ iterations + ").");

					// insert a new partner:
					pstmt = connection.prepareStatement(insertSQL);
					pstmt.setString(1, socialsecnr);
					pstmt.setString(2, personid);
					pstmt.setString(3, name);
					pstmt.setString(4, firstname);

					rows = pstmt.executeUpdate();
					debug("Partner #" + (i + 1) + " inserted (of " + iterations
							+ ").");
				}

				/*
				 * Batch execution: for (int i=0; i<iterations; i++) { //
				 * prepare unique names and VNRs: String name = "PV_SqlTrigger_"
				 * + startIndex + i; String firstname = "Firstname_" +
				 * startIndex + i; String socialsecnr = "" +
				 * (socialsecnrInitialValue + i); String personid = "" +
				 * (personidInitialValue + i);
				 * 
				 * // insert a new kpvpartid key: pstmt =
				 * connection.prepareStatement(kpvpartidSQL); //int rows =
				 * pstmt.executeUpdate(); pstmt.addBatch(); debug("kpvpartid #"
				 * + (i+1) + " inserted (of " + iterations + ").");
				 * 
				 * // insert a new partner: pstmt =
				 * connection.prepareStatement(insertSQL); pstmt.setString(1,
				 * socialsecnr); pstmt.setString(2, personid);
				 * pstmt.setString(3, name); pstmt.setString(4, firstname);
				 * 
				 * //rows = pstmt.executeUpdate(); pstmt.addBatch();
				 * debug("Partner #" + (i+1) + " inserted (of " + iterations +
				 * ")."); } int[] results = null;
				 * 
				 * try { results = pstmt.executeBatch(); } catch ( SQLException
				 * ex) { while (ex != null) { debug("Message: " +
				 * ex.getMessage() ); ex = ex.getNextException(); } }
				 * 
				 * if (results != null && results.length > 0) {
				 * debug("Results:"); debug(Arrays.toString(results)); } else {
				 * debug("An error occured during batch execution"); }
				 */

				// Execute a control SQL after ALL inserts:
				pstmt = connection.prepareStatement(controlSQL);
				queryResult = pstmt.executeQuery();

				resultArray = ResultSetConverter.convert(queryResult);
				resultObj = new JSONObject();
				java.util.Date date = new java.util.Date();
				String dateString = new Timestamp(date.getTime()).toString();
				resultObj.put("application", "PV");
				resultObj.put("description", "Mass PV insert");
				resultObj.put("report_type", "insert");
				resultObj.put("report_date", dateString);
				resultObj.put("report_data", resultArray);

				// close the connection
				pstmt.close();
				connection.close();
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (JSONException e) {
			e.printStackTrace();
		}
		return resultObj;
	}

	/**
	 * Loads all queries from queries.json file.
	 * 
	 * @param config
	 *            client configuration object
	 * @return JSONObject containing JSON data of the defined queries
	 */
	private static JSONObject getQueries(Config config) {
		JSONObject resultObj = null;
		// TODO
		return resultObj;
	} // end getQueries()

	/**
	 * Executes the SQL query on VISTA database.
	 * 
	 * @param queryObj
	 *            JSONObject containing sql and meta data
	 * @param config
	 *            client configuration object (contains url to bluemix)
	 * @return JSONObject containing the query result in JSON format
	 */
	private static JSONObject executeQuery(JSONObject queryObj, Config config) {
		JSONArray resultArray = null;
		JSONObject resultObj = null;
		String jdbcClassName = "com.ibm.db2.jcc.DB2Driver";
		String db2Url = config.db2Url + "/" + config.db2DBName;

		Connection connection = null;
		try {
			// Load class into memory
			Class.forName(jdbcClassName);
			// Establish connection
			debug("Connecting to DB2: " + db2Url);
			connection = DriverManager.getConnection(db2Url, config.db2User,
					config.db2Password);

			if (connection != null) {
				debug("Connected successfully to DB2: " + db2Url);
				PreparedStatement pstmt = null;
				String sql = queryObj.getString("sql");
				String application = queryObj.getString("application");
				String description = queryObj.getString("description");
				String report_type = queryObj.getString("report_type");
				pstmt = connection.prepareStatement(sql);
				// fire the SQL execution (only select is allowed!):
				ResultSet queryResult = pstmt.executeQuery();
				if (queryResult == null) {
					debug("Error: no results found");
				} else {
					resultArray = ResultSetConverter.convert(queryResult);
					// the resultArray contains "raw" data like [{row1},{row2}].
					// we now need to enrich it with canton, application,
					// report_date, report_type
					resultObj = new JSONObject();
					java.util.Date date = new java.util.Date();
					String dateString = new Timestamp(date.getTime())
							.toString();
					resultObj.put("application", application);
					resultObj.put("description", description);
					resultObj.put("report_type", report_type);
					resultObj.put("report_date", dateString);
					resultObj.put("report_data", resultArray);
				}
				connection.close();
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (JSONException e) {
			e.printStackTrace();
		}
		return resultObj;
	} // end executeQuery()

	/**
	 * Formats a resultSet as a text table
	 * 
	 * @param resultSet2
	 * @throws SQLException
	 */
	public static void displayResultSet(ResultSet resultSet2)
			throws SQLException {
		ResultSetMetaData resultSetMetaData2 = resultSet2.getMetaData();
		int count2 = resultSetMetaData2.getColumnCount();
		Object[] objects = new Object[count2];

		for (int a = 1; a <= count2; a++) {
			System.out.printf("%1$22s", resultSetMetaData2.getColumnName(a)
					+ " ");
		}
		System.out.println("");
		while (resultSet2.next()) {
			for (int a = 1; a <= count2; a++) {
				Object o2 = resultSet2.getObject(a);
				String value = "(null)";
				if (o2 != null) {
					objects[a - 1] = o2.toString().trim();
					value = o2.toString().trim();
				} else {
					objects[a - 1] = value;
				}
				// objects[a-1] = o2.toString();
				System.out.printf("%1$22s", value + " ");
			}
			System.out.println("");
		}

	}