/**
 *
 */
package com.pac8.core.util;

import com.datastax.driver.core.ColumnDefinitions;
import com.datastax.driver.core.ResultSet;
import com.datastax.driver.core.Row;
import com.datastax.driver.core.Session;
import com.pac8.core.common.Log;
import com.pac8.core.common.Pac8EnvConstants;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Alberto and Brajesh
 *
 */
public class DatabaseUtil {

    private final static String CONNECTION_MYSQL_URL_DATABASE = Pac8EnvConstants.DB_MYSQL_HOST
            + Pac8EnvConstants.DB_MYSQL_CONN;
    private final static String CONNECTION_MYSQL_USERNAME = Pac8EnvConstants.DB_MYSQL_USERNAME;
    private final static String CONNECTION_MYSQL_PASSWORD = Pac8EnvConstants.DB_MYSQL_PASSWORD;
    private final static String CONNECTION_ORACLE_URL_DATABASE = Pac8EnvConstants.DB_ORACLE_HOST
            + Pac8EnvConstants.DB_ORACLE_CONN;
    private final static String CONNECTION_ORACLE_USERNAME = Pac8EnvConstants.DB_ORACLE_USERNAME;
    private final static String CONNECTION_ORACLE_PASSWORD = Pac8EnvConstants.DB_ORACLE_PASSWORD;
    private final static String CONNECTION_ORACLE_TMPSH_URL_DATABASE = Pac8EnvConstants.DB_ORACLE_TMPSH_HOST
            + Pac8EnvConstants.DB_ORACLE_TMPSH_CONN;
    private final static String CONNECTION_ORACLE_TMPSH_USERNAME = Pac8EnvConstants.DB_ORACLE_TMPSH_USERNAME;
    private final static String CONNECTION_ORACLE_TMPSH_PASSWORD = Pac8EnvConstants.DB_ORACLE_TMPSH_PASSWORD;
    private final static String CONNECTION_CASSANDRA_URL_DATABASE = Pac8EnvConstants.DB_CASSANDRA_HOST;
    private final static String CONNECTION_CASSANDRA_USERNAME = Pac8EnvConstants.DB_CASSANDRA_USERNAME;
    private final static String CONNECTION_CASSANDRA_PASSWORD = Pac8EnvConstants.DB_CASSANDRA_PASSWORD;
    private final static String CONNECTION_CASSANDRA_KEYSPACE = Pac8EnvConstants.DB_CASSANDRA_KEYSPACE;

    private final static CassandraConnectorUtil client = new CassandraConnectorUtil();

    /**
     * This methods returns a List of objects based on the Query you create. You
     * can choose between Cassandra or MySQL DataBase.
     *
     * @param dB
     *            String that said which DataBase you want to use.
     * @param queryStatement
     *            The SQL or CQL query that you will execute.
     * @return A List with all the objects that return the Query.
     * @throws SQLException
     */
    public static List<Object[]> runQueryToReturnObject(String dB,
            String queryStatement) {

        List<Object[]> listObjects = new ArrayList<Object[]>();
        Connection conn;

        switch (dB.toUpperCase()) {
        case "MYSQL":
            conn = createMySQLConnection();
            listObjects = runQueryToReturnObjectList(conn, queryStatement);
            break;
        case "CASSANDRA":
            createCassandraConnection();
            listObjects = runQueryToReturnResultSetCassandra(queryStatement);
            break;
        case "MSSQL":
            // add logic to MSSQL
            break;
        case "ORACLE":
            conn = createOracleConnection();
            listObjects = runQueryToReturnObjectList(conn, queryStatement);
            break;
        case "ORACLETMPSH":
            conn = createOracleTmpshConnection();
            listObjects = runQueryToReturnObjectList(conn, queryStatement);
            break;

        default:
            throw new IllegalStateException(
                    "This method just accept \"MySQL\" or \"Cassandra\" or \"MSSQL\" as DataBase Parameter!");
        }
        return listObjects;

    }

    // This Method should include the MSSQL DB
    public static <T> T runQueryToReturnBean(String dB, String sqlStatement,
            Class<T> type) {

        Connection conn;
        switch (dB.toUpperCase()) {
        case "MYSQL":
            conn = createMySQLConnection();
            break;
        case "ORACLE":
            conn = createOracleConnection();
            break;
        case "ORACLETMPSH":
            conn = createOracleTmpshConnection();
            break;
        default:
            throw new IllegalStateException(
                    "This method just accept \"MySQL\" or \"Oracle\" or \"MSSQL\" as DataBase Parameter!");
        }

        return runQueryToReturnBean(conn, sqlStatement, type);
    }

    // This Method should include the MSSQL DB
    public static <T> List<T> runQueryToReturnBeanArray(String dB,
            String sqlStatement, Class<T> type) {

        Connection conn;

        switch (dB.toUpperCase()) {
        case "MYSQL":
            conn = createMySQLConnection();
            break;
        case "ORACLE":
            conn = createOracleConnection();
            break;
        case "ORACLETMPSH":
            conn = createOracleTmpshConnection();
            break;
        default:
            throw new IllegalStateException(
                    "This method just accept \"MySQL\" or \"Oracle\" or \"MSSQL\" as DataBase Parameter!");
        }
        return runQueryToReturnBeanArray(conn, sqlStatement, type);
    }

    public static int runUpdateQuery(String db, String query) {
        int result;
        Connection conn;
        switch (db.toUpperCase()) {
            case "MYSQL":
                conn = createMySQLConnection();
                break;
            default:
                throw new IllegalStateException(
                        "This method just accept \"MySQL\" as DataBase Parameter!");
        }
        try {
            Statement st = conn.createStatement();
            result = st.executeUpdate(query);
        } catch (SQLException e) {
            Log.error("SQLException : " + e.getMessage()
                    + " occured while executing the SQL statement : "
                    + query + "; Exception : " + e.toString());
            throw new RuntimeException(e);
        } finally {
            DbUtils.closeQuietly(conn);
        }
        return result;
    }

    private static Connection createMySQLConnection() {
        DbUtils.loadDriver("com.mysql.jdbc.Driver");

        Connection conn = null;
        try {
            conn = DriverManager.getConnection(CONNECTION_MYSQL_URL_DATABASE,
                    CONNECTION_MYSQL_USERNAME, CONNECTION_MYSQL_PASSWORD);

        } catch (SQLException e) {
            Log.error("Failed to connect to\nHost : "
                    + CONNECTION_MYSQL_URL_DATABASE + "\nUser Name : "
                    + CONNECTION_MYSQL_USERNAME + "\nPassword : "
                    + CONNECTION_MYSQL_PASSWORD);

        }
        return conn;
    }

    private static Connection createOracleConnection() {

        // Load the Driver class.
        DbUtils.loadDriver("oracle.jdbc.driver.OracleDriver");
        Connection conn;
        // Create the connection using the static getConnection method
        try {
            conn = DriverManager.getConnection(CONNECTION_ORACLE_URL_DATABASE,
                    CONNECTION_ORACLE_USERNAME, CONNECTION_ORACLE_PASSWORD);
        } catch (SQLException e) {
            Log.error("Failed to connect to\nHost : "
                    + CONNECTION_ORACLE_URL_DATABASE + "\nUser Name : "
                    + CONNECTION_ORACLE_USERNAME + "\nPassword : "
                    + CONNECTION_ORACLE_PASSWORD);
            throw new RuntimeException(e);
        }
        return conn;
    }

    /**
     * Needed a separate method to create the create Oracle Connection.
     * Refactoring the above method was too risky
     **/
    private static Connection createOracleTmpshConnection() {

        // Load the Driver class.
        DbUtils.loadDriver("oracle.jdbc.driver.OracleDriver");
        Connection conn;
        // Create the connection using the static getConnection method
        try {
            conn = DriverManager.getConnection(
                    CONNECTION_ORACLE_TMPSH_URL_DATABASE,
                    CONNECTION_ORACLE_TMPSH_USERNAME,
                    CONNECTION_ORACLE_TMPSH_PASSWORD);
        } catch (SQLException e) {
            Log.error("Failed to connect to\nHost : "
                    + CONNECTION_ORACLE_URL_DATABASE + "\nUser Name : "
                    + CONNECTION_ORACLE_USERNAME + "\nPassword : "
                    + CONNECTION_ORACLE_PASSWORD);
            throw new RuntimeException(e);
        }
        return conn;
    }

    private static void createCassandraConnection() {
        try {
            client.connect(CONNECTION_CASSANDRA_URL_DATABASE,
                    Integer.parseInt(Pac8EnvConstants.DB_CASSANDRA_PORT),
                    CONNECTION_CASSANDRA_USERNAME,
                    CONNECTION_CASSANDRA_PASSWORD,
                    CONNECTION_CASSANDRA_KEYSPACE);
        } catch (Exception e) {
            Log.error("Failed to connect to\nHost : "
                    + CONNECTION_CASSANDRA_URL_DATABASE + "\nUser Name : "
                    + CONNECTION_CASSANDRA_USERNAME + "\nPassword : "
                    + CONNECTION_CASSANDRA_PASSWORD + "\nKeyspace : "
                    + CONNECTION_CASSANDRA_KEYSPACE);

        }
    }

    private static List<Object[]> runQueryToReturnObjectList(Connection conn,
            String sqlStatement) {
        try {
            QueryRunner run = new QueryRunner();
            return run.query(conn, sqlStatement, new ArrayListHandler());
        } catch (SQLException e) {
            Log.error("SQLException : " + e.getMessage()
                    + " occured while executing the SQL statement : "
                    + sqlStatement + "; Exception : " + e.toString());
            throw new RuntimeException(e);
        } finally {
            DbUtils.closeQuietly(conn);
        }
    }

    private static List<Object[]> runQueryToReturnResultSetCassandra(
            String cqlStatement) {

        List<Object[]> newlist = new ArrayList<Object[]>();
        Session session = null;

        try {

            session = client.getSession();
            ResultSet resultSet = session.execute(cqlStatement);

            ColumnDefinitions columnDefinitions = resultSet
                    .getColumnDefinitions();

            int columnsSize = columnDefinitions.size();

            Object[] rowObject = new Object[columnsSize];

            for (Row r : resultSet) {
                rowObject = new Object[columnsSize];

                for (int i = 0; i < columnsSize; i++) {
                    rowObject[i] = r.getObject(i);
                }
                newlist.add(rowObject);
            }

        } catch (Exception e) {
            Log.error("CQLException : " + e.getMessage()
                    + " occured while executing the CQL statement : "
                    + cqlStatement + " Exception : " + e.toString());
            throw new RuntimeException(e);
        } finally {
            client.closeSession(session);
        }
        return newlist;
    }

    private static <T> T runQueryToReturnBean(Connection conn,
            String sqlStatement, Class<T> type) {
        try {
            conn.setAutoCommit(true);
            QueryRunner run = new QueryRunner();
            return run.query(conn, sqlStatement, new BeanHandler<T>(type));
        } catch (SQLException e) {
            Log.error("SQLException : " + e.getMessage()
                    + " occured while executing the SQL statement : "
                    + sqlStatement + "; Exception : " + e.toString());
            throw new RuntimeException(e);
        } finally {
            DbUtils.closeQuietly(conn);
        }
    }

    private static <T> List<T> runQueryToReturnBeanArray(Connection conn,
            String sqlStatement, Class<T> type) {
        try {
            conn.setAutoCommit(true);
            QueryRunner run = new QueryRunner();
            return run.query(conn, sqlStatement, new BeanListHandler<T>(type));
        } catch (SQLException e) {
            Log.error("SQLException : " + e.getMessage()
                    + " occured while executing the SQL statement : "
                    + sqlStatement + "; Exception : " + e.toString());
            throw new RuntimeException(e);
        } finally {
            DbUtils.closeQuietly(conn);
        }
    }

}