import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.util.logging.Logger;

import org.apache.commons.dbcp.BasicDataSource;

import com.eurogiro.common.exception.BusinessException;
import com.eurogiro.common.exception.DBException;
import com.eurogiro.common.exception.SuperException;
import com.eurogiro.common.server.util.CommonConstantsIF;
import com.eurogiro.common.server.util.EncryptDecryptUtil;
import com.eurogiro.common.util.ConstantsIF;
import com.eurogiro.els.server.ELSApplnConfig;

public class DatabaseConnectionManager {

    private static DatabaseConnectionManager dbMgr = null;
    private BasicDataSource datasource;
    private static Logger logger = Logger.
                                        getLogger(DatabaseBackupManager.
                                                class.getPackage()
                                                    .getName());

    /**
     * Constructor to create and initialize new DataSource.
     *
     * @throws BusinessException            Error while Decrypting Password
     */
    private DatabaseConnectionManager() throws BusinessException {
        datasource = new BasicDataSource();
        initializePool();
    }

    /**
     * This method returns the Singleton instance of DatabaseConnectionManager
     *
     * @return DatabaseConnectionManager   Singleton instance of
     *                                     DatabaseConnectionManager
     * @throws BusinessException           Error while Decrypting Password
     */
    public static DatabaseConnectionManager getInstance()
        throws BusinessException {
        if (dbMgr == null) {
            dbMgr = new DatabaseConnectionManager();
        }
        return dbMgr;
    }

    /**
     * This method retrieves Connection to the Database from the Pool.
     *
     * @return Connection                  The Connection from the Pool
     * @throws SQLException                Error occurred while retrieving the
     *                                     Connection.
     */
    public Connection getConnection() throws SQLException {
        return datasource.getConnection();
    }

    /**
     * This method initializes the Database Connection Pool
     *
     * @throws BusinessException            Error while Decrypting Password
     */
    private void initializePool() throws BusinessException {
        // Set the Database home as System Property
        String strDBPath = System.getProperty(ConstantsIF.ELS_HOME)
        + CommonConstantsIF.DB_PATH;
        System.setProperty(CommonConstantsIF.DERBY_SYSTEM_PROPERTY, strDBPath);

        // Set the Intial Pool Size and Maximum Active Connections in the pool
        datasource.setInitialSize(CommonConstantsIF.DB_INITIAL_CONN_POOL);
        datasource.setMaxActive(CommonConstantsIF.DB_MAX_ACTIVE_CONN_POOL);

        // Get the Connection Properties from the ELS Application Configuration
        String strBootPwd = ELSApplnConfig.getInstance().getElsConfig().
                                getKeyValue(CommonConstantsIF.DB_BOOT_PWD);
        strBootPwd = EncryptDecryptUtil.getDecrypted(strBootPwd);

        String strPwd = ELSApplnConfig.getInstance().getElsConfig().
                                getKeyValue(CommonConstantsIF.DB_PWD);
        strPwd = EncryptDecryptUtil.getDecrypted(strPwd);
        // Set the Driver
        datasource.setDriverClassName(CommonConstantsIF.DERBY_DRIVER);
        // Set the URL
        datasource.setUrl(CommonConstantsIF.DERBY_PROTOCOL
                + CommonConstantsIF.DB_NAME);
        // Set the Connection Properties
        datasource.addConnectionProperty(CommonConstantsIF.CONNECTION_BOOT_PWD,
                                                                strBootPwd);
        datasource.addConnectionProperty(CommonConstantsIF.CONNECTION_USER,
                                            CommonConstantsIF.DB_USER);
        datasource.addConnectionProperty(CommonConstantsIF.CONNECTION_PWD,
                                                                strPwd);
    } // End of initializePool

    /**
     * This method shuts down the Embedded Database.
     *
     * @throws SuperException              Error occurred while shutting down
     *                                     Embedded Database
     */
    public void shutDownDatabase() throws SuperException {
        Connection connection = null;
        try {

            // Load the Database Driver
            Class.forName(CommonConstantsIF.DERBY_DRIVER).newInstance();
            logger.fine("Database Driver loaded successfully.");
            String strDbName = CommonConstantsIF.DB_NAME;
            String strPwd = ELSApplnConfig.getInstance().
                                    getElsConfig().
                                        getKeyValue(CommonConstantsIF.DB_PWD);
            // Decrypt the User Password
            strPwd = EncryptDecryptUtil.getDecrypted(strPwd);
            String strBootPwd = ELSApplnConfig.getInstance().
                                    getElsConfig().
                                    getKeyValue(CommonConstantsIF.DB_BOOT_PWD);
            // Decrypt the Boot Password
            strBootPwd = EncryptDecryptUtil.getDecrypted(strBootPwd);
            // Set Connection Properties
            Properties props = new Properties();
            props.put(CommonConstantsIF.CONNECTION_USER,
                CommonConstantsIF.DB_USER);
            props.put(CommonConstantsIF.CONNECTION_PWD, strPwd);
            // Form the Connection URL
            String strUrl = CommonConstantsIF.DERBY_PROTOCOL
                + strDbName
                        + ";" + CommonConstantsIF.
                                    CONNECTION_BOOT_PWD
                                        + "=" + strBootPwd + ";shutdown=true";
            try {
                connection = DriverManager
                            .getConnection(strUrl, props);
            } catch (SQLException ex) {
                logger.warning("Database Successful ShutDown Exception");
            }
            logger.info("Database ShutDown successfully");
        } catch (BusinessException ex) {
            logger.severe("Error occured while shutting down Database. "
                    + "Reason: " + ex.getErrorCode());
            ex.setMessage("Error occured while shutting down Database. "
                    + "Reason: " + ex.getErrorCode());
            throw ex;
        } catch (Exception ex) {
            String strMessage = "Error occured while shutting down Database. ";
            if (null != ex.getMessage()) {
                strMessage = strMessage + "Reason: " + ex.getMessage();
            }
            logger.severe(strMessage);
            DBException dbException = new DBException();
            dbException.setMessage("Error occured while shutting "
                                         + "down Database.");
            throw dbException;
        }
    } // End of shutDownDatabase
}