package usda.weru.remoteDataAccess.jdbc.soil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import usda.weru.remoteDataAccess.exceptions.RdaConnectException;
import usda.weru.util.ConfigData;
import usda.weru.weps.Weps;

/**
 *
 * @author mhaas 
 */
public abstract class JdbcSoilInterfaceDatabase extends JdbcSoilInterface {
    
    private final String DEFAULT_QUERY = "SELECT lkey,areasymbol,areaname FROM legend ORDER BY areasymbol";
           
    /**
     * Catalog items from a Database where Nodes are new legend items.
     * 
     * code from usda.weru.soil.JdbcSoilDatabase
     * 
     * The Query will show all SDM files if the sort method is set to "showAll"
     * or the country isn't in the US.
     * 
     * The Query will show SDM files for a State if the sort method is "state" 
     * or the county doesn't have its own file.
     * 
     * The Query will show SDM files for a County if the sort method is "stateCounty"
     * and the county has a file with a matching name.
     * 
     * @return a list of Soil Files
     * @throws RdaConnectException - if cannot connect to remote service
     */
    @Override
    protected ArrayList<SoilCatParms> callSoilCatalog() throws RdaConnectException {
        ArrayList<SoilCatParms> catalog;

        // want to init here, since it takes time to access (i.e., don't initialize in constructor).
        // Only want the delay when user selects
        if (soilsDatabase == null) {
            initialize();
        }

        //Soil catalog for sweep, mass soil chooser, or weps
        if (Weps.getInstance() == null) {
            catalog = this.defaultSoilCatalog();
        } else if (Weps.getInstance().getMassSoilChooserSDM()) {
            catalog = this.defaultSoilCatalog();
            Weps.getInstance().setMassSoilChooserSDM(false);
        } else {
            catalog = this.wepsSoilCatalog();
        }

        return catalog;

    }
    
    private ArrayList<SoilCatParms> defaultSoilCatalog() throws RdaConnectException {
        ArrayList<SoilCatParms> catalog = new ArrayList<>();
        try {
            Statement stmt = soilsDbConnection.createStatement();
            ResultSet rs = stmt.executeQuery(DEFAULT_QUERY);

            while (rs.next()) {
                catalog.add(new SoilCatParms(
                        getString(rs, "areasymbol") + " - " + getString(rs, "areaname")).
                        setLegendVals(getString(rs, "lkey"), getString(rs, "areaname"), getString(rs, "areasymbol"))
                );
            }
        } catch (SQLException e) {
            throw (new RdaConnectException("Unable to connect remote service", e, this.interfaceName));
        }

        return catalog;

    }
    
    private ArrayList<SoilCatParms> wepsSoilCatalog() throws RdaConnectException {
        ArrayList<SoilCatParms> catalog = new ArrayList<>();

        try {
            Statement stmt = soilsDbConnection.createStatement();
            ResultSet rs;

            String site = Weps.getInstance().getSelectedFromSiteChooser().toString();
            String country = site.split("-")[0].split(":")[1]; //Looks like FIPS:US-CO or FIPS:AU
            String state = "", county = "";
            if (isUS(country)) {
                state = site.split("-")[1];
                county = Weps.getInstance().getSelectedFromSiteChooser().getDisplayName();
            }

            String statement = DEFAULT_QUERY;

            if (ConfigData.checkParmValue(ConfigData.SDMSoilSortMethod, "showAll") || !isUS(country)) {
                //IF it is showAll or the country isn't the US - do nothing default_query is fine
            } else if (!checkForCounty(county, state) || ConfigData.checkParmValue(ConfigData.SDMSoilSortMethod, "State")) {
                statement = "SELECT lkey, areasymbol, areaname FROM legend WHERE"
                        + " areasymbol LIKE '%" + state + "%' ORDER BY areasymbol";
            } else if (ConfigData.checkParmValue(ConfigData.SDMSoilSortMethod, "stateCounty")) {
                statement = "SELECT lkey, areasymbol, areaname FROM legend"
                        + " WHERE areaname LIKE '%" + queryWithApostrophe(county) + "%' AND"
                        + " areasymbol LIKE '%" + state + "%' ORDER BY areasymbol";
            }

            rs = stmt.executeQuery(statement);

            while (rs.next()) {
                if (ConfigData.checkParmValue(ConfigData.SDMSoilSortMethod, "stateCounty")) {
                    String countyQuery = getString(rs, "areaname").split(",")[0];

                    if (countyQuery.contains(county)) {
                        catalog.add(new SoilCatParms(
                                getString(rs, "areasymbol") + " - " + getString(rs, "areaname")).
                                setLegendVals(getString(rs, "lkey"), getString(rs, "areaname"), getString(rs, "areasymbol"))
                        );
                    }
                } else {
                    catalog.add(new SoilCatParms(
                            getString(rs, "areasymbol") + " - " + getString(rs, "areaname")).
                            setLegendVals(getString(rs, "lkey"), getString(rs, "areaname"), getString(rs, "areasymbol"))
                    );
                }
            }

            return catalog;
        } catch (SQLException e) {
            throw (new RdaConnectException("Unable to connect remote service", e, this.interfaceName));
        }
    }
    
    /**
     * Determines if the site is in the US.
     *
     * needs to search for AQ because that is the site name for "US Territory
     * Pacific Basin"
     *
     * @param country - 2 letter string country ID
     * @return - true if it is a US state or territory and false otherwise.
     */
    private boolean isUS(String country) {
        return country.equals("US");
    }

    /**
     * Add a second apostrophe for SQL queries.
     *
     * @param county - the county
     * @return - the county with a second apostrophe if it contained an
     * apostrophe
     */
    private String queryWithApostrophe(String county) {
        return county.contains("'") ? county.replace("'", "''") : county;
    }

    /**
     * Determines if the given area name has a valid soil data mart file
     *
     * @param areaname - location of state (2 letter initial) and county id = 3
     * numbers
     * @return - true if the soil data mart file exists, false otherwise
     * @throws RdaConnectException
     */
    private boolean checkForCounty(String county, String state) throws RdaConnectException {
        try {
            Statement stmt = soilsDbConnection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT lkey, areasymbol, areaname FROM legend"
                    + " WHERE areaname LIKE '%" + queryWithApostrophe(county) + "%' AND "
                    + "areasymbol LIKE '%" + state + "%' ORDER BY areasymbol");

            while (rs.next()) {
                if (getString(rs, "areaname").contains(county)) {
                    return true;
                }
            }

            return false;

        } catch (SQLException e) {
            throw (new RdaConnectException("Unable to connect remote service", e, this.interfaceName));
        }
    }
}