/********************************************************************************
 * Copyright (c) 2018 Mettenmeier GmbH 
 *
 * See the NOTICE file(s) distributed with this work for additional 
 * information regarding copyright ownership.
 *
 * This program and the accompanying materials are made available under the 
 * terms of the Eclipse Public License 2.0 which is available at
 * http://www.eclipse.org/legal/epl-2.0
 *
 * SPDX-License-Identifier: EPL-2.0 
 ********************************************************************************/
package org.eclipse.openk.sp.db.config;

import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.Properties;

import org.apache.commons.io.IOUtils;
import org.apache.log4j.Logger;
import org.eclipse.openk.sp.util.FileHelper;
import org.springframework.stereotype.Service;

@Service
public class DbInitializer {
	public static final Logger LOGGER = Logger.getLogger(DbInitializer.class);
	private String dbUrl = "jdbc:postgresql://localhost:5432/BP";
	private String dbDriver = "org.postgresql.Driver";
	private String dbUsername = "bp";
	private String dbPass = "bp";
	private String dbSchema = "public";
	private String dbScriptDir = "postgres";

	private FileHelper fh = new FileHelper();

	public static final String APP_PROPERTIES_NAME = "/application.properties";
	private static final String SQL_SCRIPT_DELETE_KEY = "sql.scripts.delete";
	private static final String SQL_SCRIPT_INIT_KEY = "sql.scripts.init";
	private static final String SQL_SCRIPT_HIST_KEY = "sql.scripts.hist";

	public void configureDatabase()
			throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException, IOException {
		Properties property = fh.loadPropertiesFromResource(APP_PROPERTIES_NAME);
		dbUrl = property.getProperty("db.url");
		dbDriver = property.getProperty("db.driver");
		dbUsername = property.getProperty("db.username");
		dbPass = property.getProperty("db.password");
		dbSchema = property.getProperty("db.schema");

		if (dbDriver.equals("oracle.jdbc.OracleDriver")) {
			dbScriptDir = "oracle";
		}

		try {
			if (Boolean.parseBoolean(property.getProperty("db.redeploy"))) {
				this.initializeDatabase();
			}
		} catch (Exception e) {
			LOGGER.error(e, e);
		}
	}

	protected void initializeDatabase()
			throws IOException, InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
		Properties property = fh.loadPropertiesFromResource(APP_PROPERTIES_NAME);
		// run "delete from" script
		this.loopOverSqlFiles(property.getProperty(SQL_SCRIPT_DELETE_KEY).split(","));
		// run "hist" script
		this.loopOverSqlFiles(property.getProperty(SQL_SCRIPT_HIST_KEY).split(","));
		// run "init" script
		this.loopOverSqlFiles(property.getProperty(SQL_SCRIPT_INIT_KEY).split(","));

	}

	protected void loopOverSqlFiles(String[] sqlFileNames)
			throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
		for (String sqlScriptName : sqlFileNames) {
			LOGGER.debug("run script: /META-INF/sql/" + dbScriptDir + "/" + sqlScriptName + ".sql");
			this.runSQLStatementByFile("/META-INF/sql/" + dbScriptDir + "/" + sqlScriptName + ".sql");
		}
	}

	protected void runSQLStatementByFile(String fileName)
			throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
		Connection conn = this.getJdbcConnection();
		PreparedStatement stmt = null;
		try {
			conn.setAutoCommit(false);
			String sql = this.readSqlFromFile(fileName);
			// replace schema name with configured name
			sql = sql.replaceAll("'", "''");
			sql = MessageFormat.format(sql, dbSchema);
			String[] sqlRow = sql.split("[\\r\\n]+");
			int a = 0;
			for (String sqlLine : sqlRow) {
				LOGGER.trace("SQL: " + sqlLine);
				if (!sqlLine.isEmpty() && !sqlLine.startsWith("--") && sqlLine.charAt(0) != '/') {

					stmt = conn.prepareStatement(this.cutAndTrimSQL(sqlLine));
					stmt.execute();
					conn.commit();
					stmt.close();
				}

				// close and reopen connection after 50 records to avoid that all cursors are in
				// use
				if (a > 50) {
					conn.close();
					conn = this.getJdbcConnection();
					conn.setAutoCommit(false);
					a = 0;
				}
				a++;
			}

		} catch (Exception e) {
			LOGGER.error(e, e);
			conn.rollback();
		} finally {
			if (stmt != null) {
				stmt.close();
			}
			conn.close();
		}
	}

	protected String readSqlFromFile(String fileName) throws IOException {
		String result = "";
		InputStream is = fh.loadFileFromResource(fileName);
		if (is != null) {
			result = IOUtils.toString(is, StandardCharsets.UTF_8.name());
		}
		return result;
	}

	protected Connection getJdbcConnection()
			throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		try {
			Class.forName(dbDriver).newInstance();
			return DriverManager.getConnection(dbUrl, dbUsername, dbPass);
		} catch (SQLException e) {
			LOGGER.error(e, e);
			throw e;
		}
	}

	protected void closeConnection(Connection conn) throws SQLException {
		try {
			conn.close();
		} catch (SQLException e) {
			LOGGER.error(e, e);
			throw e;
		}
	}

	protected void closeStatement(Statement stmt) throws SQLException {
		try {
			if (stmt != null && !stmt.isClosed()) {
				stmt.close();
			}
		} catch (SQLException e) {
			LOGGER.error(e, e);
			throw e;
		}
	}

	/**
	 * Method to trim() and cut off ending ; for oracle scripts.
	 * 
	 * @param sql
	 * @param schema
	 * @return
	 */
	protected String cutAndTrimSQL(String sql) {
		sql = sql.trim();
		if (dbScriptDir.equalsIgnoreCase("Oracle") && !sql.isEmpty() && sql.charAt(sql.length() - 1) == ';') {
			return sql.substring(0, sql.length() - 1).trim();
		}
		return sql.trim();
	}

	public String getSchemaName() {
		return dbSchema;
	}

}