/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package wqm.utils;


import csip.Config;
import csip.ServiceException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;
import org.apache.tomcat.jdbc.pool.DataSourceProxy;
import org.apache.tomcat.jdbc.pool.PoolProperties;
import java.net.URL;

/**
 *
 * @author Shaun Case
 */
public class WQMTools {
    private static DataSource datasourceWQM;
    private static DataSource datasourceR2GIS;
    private static DataSource datasourceSSURGO;
    
    private static String connectionStringWQM;
    private static String connectionStringR2GIS;
    private static String connectionStringSSURGO;    
    
    private static Boolean debugging;

    public static String getDatabaseHostname( String className ) throws Exception{
        String ret_val = null;        
        
        switch( className.toLowerCase() ){
            case "r2gis":
                ret_val = new URL(Config.getString( "r2gis.db", "").replace("jdbc:postgresql", "http")).getHost();                
                break;
                
            case "ssurgo":
                ret_val = new URL(Config.getString( "ssurgo.db", "").replace("jdbc:postgresql", "http")).getHost();
                break;
                
            case "wqm":
                ret_val = new URL( Config.getString( ( debugging? "wqm.debug.db":"wqm.db")).replace("jdbc:postgresql", "http")).getHost();
                break;
        }        
        
        return ret_val;
    }
    
    public static String getR2GISFileURL( String path, String name ) throws Exception{
        return ( (("http://" + wqm.utils.WQMTools.getDatabaseHostname("r2gis") + "/r2/" + path + "/" + name + ".xml")).replace(" ","%20")).replace("\\","/");                                      
    } 

    public static synchronized Connection getConnection(String className, Logger logger) throws ServiceException {
        debugging = Boolean.parseBoolean( Config.getString( "wqm.debugging", "false" ) );
        String confString = null;
        Connection ret_val = null;
        
        switch( className.toLowerCase() ){
            case "r2gis":
                confString = Config.getString( "r2gis.db", "");                
                break;
                
            case "ssurgo":
                confString = Config.getString( "ssurgo.db", "");                
                break;
                
            case "wqm":
                confString = Config.getString( ( debugging? "wqm.debug.db":"wqm.db"), "");
                break;
        }
        

                
        if(confString == null || confString.isEmpty())
        {
            if ( logger != null ){
                logger.log(Level.SEVERE, "Connection string not provided.  Please configure wqm.db configuration parameter.");            
            }
            throw new ServiceException("Unable to connect to WQM database.  Please check the " + className + " configuration parameter");
        }
        
        try {
            String tempConnectionString = "";
            Boolean createNew = false;
            
            switch( className ){
                case "r2gis":
                    if ( (datasourceR2GIS == null) || ( !confString.equals(connectionStringR2GIS))){
                        DataSourceProxy proxy = (DataSourceProxy) datasourceR2GIS;
                        if (datasourceR2GIS != null) {
                            proxy.close();
                            datasourceR2GIS = null;
                        }                        
                        connectionStringR2GIS = confString;
                        PoolProperties p = new PoolProperties();
                        p.setUrl(connectionStringR2GIS);
                        p.setUsername("postgres");
                        p.setPassword("admin");                               
                        //p.setDefaultReadOnly( true );
                        p.setDriverClassName("org.postgresql.Driver");
                        p.setJmxEnabled(false);
                        p.setTestOnBorrow(true);
                        p.setValidationQuery("SELECT 1");
                        p.setTestOnReturn(false);
                        p.setValidationInterval(30000);
                        p.setTimeBetweenEvictionRunsMillis(30000);
                        p.setMaxWait(10000);
                        p.setRemoveAbandonedTimeout(600);
                        p.setRemoveAbandoned(true);
                        p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
                                                + "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;"
                                                + "org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer"); 
                        
                        datasourceR2GIS = new org.apache.tomcat.jdbc.pool.DataSource(p);
                        if ( logger != null ){
                            logger.log(Level.INFO, "\nCreated datasource {0}", datasourceR2GIS.toString());
                        }                        
                    }
                    
                    ret_val = datasourceR2GIS.getConnection();                                            
                break;
                case "ssurgo":
                    if ( (datasourceSSURGO == null) || ( !confString.equals(connectionStringSSURGO))){
                        DataSourceProxy proxy = (DataSourceProxy) datasourceSSURGO;
                        if (datasourceSSURGO != null) {
                            proxy.close();
                            datasourceSSURGO = null;
                        }                        
                        connectionStringSSURGO = confString;
                        PoolProperties p = new PoolProperties();
                        p.setUrl(connectionStringSSURGO);
                        p.setUsername("postgres");
                        p.setPassword("admin");                            
                        p.setDriverClassName("org.postgresql.Driver");
                        p.setJmxEnabled(false);
                        p.setTestOnBorrow(true);
                        p.setValidationQuery("SELECT 1");
                        p.setTestOnReturn(false);
                        p.setValidationInterval(30000);
                        p.setTimeBetweenEvictionRunsMillis(30000);
                        p.setMaxWait(10000);
                        p.setRemoveAbandonedTimeout(600);
                        p.setRemoveAbandoned(true);
                        datasourceSSURGO = new org.apache.tomcat.jdbc.pool.DataSource(p);
                        if ( logger != null ){
                            logger.log(Level.INFO, "\nCreated datasource {0}", datasourceSSURGO.toString());
                        }                        
                    }
                    
                    ret_val = datasourceSSURGO.getConnection();                        
                    
                break;
                case "wqm":
                    if ( (datasourceWQM == null) || ( !confString.equals(connectionStringWQM))){
                        DataSourceProxy proxy = (DataSourceProxy) datasourceWQM;
                        if (datasourceWQM != null) {
                            proxy.close();
                            datasourceWQM = null;
                        }                        
                        connectionStringWQM = confString;
                        PoolProperties p = new PoolProperties();
                        p.setUrl(connectionStringWQM);
                        p.setUsername("postgres");
                        p.setPassword("admin");                        
                        p.setDriverClassName("org.postgresql.Driver");
                        p.setJmxEnabled(false);
                        p.setTestOnBorrow(true);
                        p.setValidationQuery("SELECT 1");
                        p.setTestOnReturn(false);
                        p.setValidationInterval(30000);
                        p.setTimeBetweenEvictionRunsMillis(30000);
                        p.setMaxWait(10000);
                        p.setRemoveAbandonedTimeout(600);
                        p.setRemoveAbandoned(true);
                        datasourceWQM = new org.apache.tomcat.jdbc.pool.DataSource(p);
                        if ( logger != null ){
                            logger.log(Level.INFO, "\nCreated datasource {0}", datasourceWQM.toString());
                        }                        
                    }

                    ret_val = datasourceWQM.getConnection();                        

                break;                    
                    
            }
            
        } catch (SQLException ex) {
            if ( logger != null ){
                logger.log(Level.SEVERE, null, ex);
            }
            throw new ServiceException("Failed to connect to WQM database.  Please check the " + className + " configuration parameter");
        }
        
        return ret_val;
    }


    public static void shutdownDataSource() {
        if (datasourceR2GIS != null && datasourceR2GIS instanceof DataSourceProxy) {
            DataSourceProxy proxy = (DataSourceProxy) datasourceR2GIS;
            proxy.close(true);
            datasourceR2GIS = null;
            System.out.println("Closed R2GIS datasource.");
        }
        
        if (datasourceSSURGO != null && datasourceSSURGO instanceof DataSourceProxy) {
            DataSourceProxy proxy = (DataSourceProxy) datasourceSSURGO;
            proxy.close(true);
            datasourceSSURGO = null;
            System.out.println("Closed SSURGO datasource.");
        }
        
        if (datasourceWQM != null && datasourceWQM instanceof DataSourceProxy) {
            DataSourceProxy proxy = (DataSourceProxy) datasourceWQM;
            proxy.close(true);
            datasourceWQM = null;
            System.out.println("Closed WQM datasource.");
        }        
    }    
}