/*
 * 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;
import java.util.ArrayList;

/**
 *
 * @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.");
        }        
    } 
    
    public static class PolygonLatLon{
	private ArrayList<WQMTools.PolygonLatLon.PointLatLon> points;
	private Boolean bValid;
	
	public PolygonLatLon(){
	    this.bValid = true;
	    this.points = new ArrayList<>();
	}
	
	public PolygonLatLon( ArrayList<WQMTools.PolygonLatLon.PointLatLon> tPoints ){
	    if ( null != tPoints ){
		this.bValid = true;
		this.points = new ArrayList<>( tPoints );
		for ( WQMTools.PolygonLatLon.PointLatLon tPoint : tPoints ){
		    if ( !tPoint.isValid() ){
			this.bValid = false;
			break;
		    }
		}
	    }
	}
	
	public void add( WQMTools.PolygonLatLon.PointLatLon tPoint ){
	    this.points.add( tPoint );
	    if ( !tPoint.isValid() ){
		this.bValid = false;
	    }
	}
	
	public void add( Double Lat, Double Lon ){
	    WQMTools.PolygonLatLon.PointLatLon tPoint = new WQMTools.PolygonLatLon.PointLatLon( Lat, Lon );
	    this.points.add( tPoint );
	    if ( !tPoint.isValid() ){
		this.bValid = false;
	    }	    
	}
	
	public String toWKT(){
	    String ret_val = "'POLYGON((";
	    int currentPoint = 0;
	    if ( this.bValid ){
		for ( WQMTools.PolygonLatLon.PointLatLon tPoint : this.points ){
		    if ( currentPoint > 0 ){
			ret_val += ", "; 
		    }
		    //  Remember Lon is X and Lat is Y...
		    ret_val += tPoint.getLon().toString() + " " + tPoint.getLat().toString();
		    currentPoint++;
		}

		ret_val += "))'";
	    }
	    else{
		ret_val = "";
	    }
	    
	    return ret_val;	    	    
	}
	
	public Boolean isValid(){return this.bValid;}
	
	public static class PointLatLon{
	    private Double Lat;
	    private Double Lon;
	    private Boolean bValid;
	    
	    public PointLatLon( Double Lat, Double Lon ){
		this.Lat = Lat;
		this.Lon = Lon;
		this.bValid = true;
		
		if ( Math.abs( Lon ) > 180 ){
		    this.bValid = false;	
		}
		
		if ( Math.abs( Lat ) > 90 ){
		    this.bValid = false;
		}				
	    }
	    
	    public Boolean isValid(){return this.bValid;}
	    public Double getLat(){return this.Lat;}
	    public Double getLon(){return this.Lon;}
	    
	    public void setLon( Double Lon ){
		this.Lon = Lon;
		if ( Math.abs( Lon ) > 180 ){
		    this.bValid = false;
		}		
	    }
	    
	    public void setLat( Double Lat ){
		this.Lat = Lat;
		if ( Math.abs( Lat ) > 90 ){
		    this.bValid = false;
		}
	    }
	}
    }      
}