package eu.dnetlib.data.claimsDemo;

import org.apache.log4j.Logger;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author eri Sql Store class to connect to the DB backend
 */

public class SqlStore {

	private Logger log = Logger.getLogger(this.getClass());

	private String dbUrl;
	private String dbUser;
	private String dbPassword;
	private Connection connection;

	private String dbDriver;

	public SqlStore(String dbUrl, String dbUser, String dbPassword, String dbDriver)  {
		this.dbDriver = dbDriver;
		this.dbUser = dbUser;
		this.dbUrl = dbUrl;
		this.dbPassword = dbPassword;

	}

	public SqlStore() throws Exception {

	}


	public void init() throws ClassNotFoundException, SQLException {
 			Class.forName(dbDriver);
			this.getConnection();


	}

	public void destroy() throws SQLException {
		this.closeConnection();

	}

	public void close() throws SQLException {
		closeConnection();

	}

	/**
	 * Establishes a JDBC connection to the database server without a given DB.
	 * Used in order to create or drop databases.
	 *
	 * @throws SQLException
	 */
	public void getConnectionNoDatabase() throws SQLException {
		String parsedUrl = this.parseUrl(dbUrl);
		log.info("Connecting to " + parsedUrl + dbUser + dbPassword);
		connection = (Connection) DriverManager.getConnection(parsedUrl, dbUser, dbPassword);
		log.info("Connected to " + parsedUrl);

	}

	/**
	 * Establishes a connection to a given DB.
	 *
	 * @throws SQLException
	 */
	public void getConnection() throws SQLException {

			log.info("Attempting connection to : " + this.dbUrl + " for " + this.dbUser + " and  " + this.dbPassword);
			connection = (Connection) DriverManager.getConnection(this.dbUrl, dbUser, dbPassword);
			log.info("Connected to " + this.dbUrl);

	}

	/**
	 * Closes Database connections.
	 *
	 * @throws SQLException
	 */
	public void closeConnection() throws SQLException {

			if (connection != null) {
				connection.close();
			}

	}

	/**
	 * Executes the @param command stored procedure in the Database. Stored
	 * procedure parameters are specified in values.
	 *
	 * @param command
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeStoredProcedure(String command, ArrayList<String> values) throws SQLException {
		java.sql.CallableStatement callableStatement = null;
		log.debug("  Executing   Query ..." + command);

			callableStatement = this.connection.prepareCall(command);
			int pos = 1;

			for (String v : values) {

				callableStatement.setObject(pos, v);

				pos++;
			}

			callableStatement.execute();
			return callableStatement.getResultSet();



	}

	/**
	 * Executes the stored query procedure specified by @param command.
	 *
	 * @param command
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeStoredProcedure(String command) throws SQLException {
		log.debug("  Executing   Query ..." + command);
		java.sql.CallableStatement callableStatement = null;

			callableStatement = this.connection.prepareCall(command);

			callableStatement.execute();
			return callableStatement.getResultSet();



	}

	/**
	 * Executes the @param query
	 *
	 * @param command
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeQuery(String command) throws SQLException, SQLStoreException {
		log.debug("  Executing   Query ... " + command);
 			Statement st = connection.createStatement();

			if (st.execute(command)) {

				ResultSet rs = st.getResultSet();
				//st.close();
				return rs;
			} else {
				log.error("Fail to execute command " + st.getWarnings());
				throw  new SQLStoreException("Fail to execute command " + st.getWarnings());

			}



	}

	/**
	 * Executes the @param  prepared query with the @values as parameters
	 * Returns a result set.
	 *
	 * @param command
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeQuery(String command, ArrayList<String> values) throws SQLException, SQLStoreException {
		log.debug("  Executing   Query ..." + command);

			PreparedStatement st = connection.prepareStatement(command);

			int pos = 1;

			for (String v : values) {

				st.setObject(pos, v);

				pos++;
			}

			if (st.execute()) {
				ResultSet rs = st.getResultSet();
			//	st.close();
				return rs;
			} else {
				log.error("Fail to execute command " + st.getWarnings());
				throw  new SQLStoreException("Fail to execute command " + st.getWarnings());
			}



	}

	/**
	 * Executes the @param command Update query
	 *
	 * @param command
	 * @return
	 * @throws SQLException
	 */
	public void executeUpdate(String command) throws SQLException {
		log.debug("  Executing   Query ..." + command);
 			Statement st = connection.createStatement();
			st.executeUpdate(command);
			//st.close();
	}
    public void executeUpdateWithRollback(List<String> commandS)  throws  SQLException{
        log.debug("  Executing   Query ..." + commandS);
            Statement st = connection.createStatement();
            connection.setAutoCommit(false);
            for(String command : commandS) {
                if(command.equals("EXCEPTION")){
                    throw new SQLException("Force rollback ");
                }
                st.executeUpdate(command);
            }
            //st.close();
            connection.commit();
            connection.setAutoCommit(true);

    }
	/**
	 * Executes the @param command  Prepared Update query  with the
	 *
	 * @param command
	 * @param values
	 * @return
	 * @throws Exception
	 * @values as parameters
	 */
	public void executeUpdate(String command, ArrayList<String> values) throws SQLException {
		log.debug("  Executing   Query ..." + command);
			PreparedStatement st = connection.prepareStatement(command);

			int pos = 1;

			for (String v : values) {

				st.setObject(pos, v);

				pos++;
			}

			boolean executed = st.execute();

			if (!executed) {
				log.error("Fail to execute update command " + st.getWarnings());
				throw new SQLException("Fail to execute update command " + st.getWarnings());
			}
	}


	/**
	 * Turns autocommit on and off.
	 *
	 * @param on
	 * @throws Exception
	 */
	public void setautoCommit(boolean on) throws SQLException {
			this.connection.setAutoCommit(on);
	}


	/**
	 * Creates a serialized string from the ResultSet returned from a query
	 *
	 * @throws Exception
	 */
	public String getResults(ResultSet rs) throws SQLException {
		String data = new String();
		try {


			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()) {
				for (int i = 1; i < rsmd.getColumnCount() - 1; i++)

				{

					log.debug(rsmd.getColumnName(i) + "," + rs.getString(i));
					data += rsmd.getColumnName(i) + "," + rs.getString(i) + "\n";
				}

			}
			log.debug(" Generated Data :" + data);
			rs.close();
			return data;


		} finally {
			this.closeConnection();
		}

	}

	private String parseUrl(String url) {

		if (url.endsWith("/")) {
			url = url.substring(0, url.lastIndexOf('/'));

		}
		url = url.substring(0, url.lastIndexOf('/') + 1);
		log.debug(" db url  parsed" + url);
		return url;
	}

	public String getDbDriver() {
		return dbDriver;
	}

	public void setDbDriver(String dbDriver) {
		this.dbDriver = dbDriver;
	}

	public String getDbUrl() {
		return dbUrl;
	}

	public void setDbUrl(String dbUrl) {
		this.dbUrl = dbUrl;
	}

	public String getDbUser() {
		return dbUser;
	}

	public void setDbUser(String dbUser) {
		this.dbUser = dbUser;
	}

	public String getDbPassword() {
		return dbPassword;
	}

	public void setDbPassword(String dbPassword) {
		this.dbPassword = dbPassword;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}
}