package m.rhem.rhem03_compEsd;

import GISObjects.GISEngineFactory;
import GISObjects.GISObject;
import GISObjects.GISObjectException;
import GISObjects.GISObjectFactory;
import csip.ModelDataService;
import csip.ServiceException;
import csip.annotations.Polling;
import csip.annotations.Resource;
import csip.utils.JSONUtils;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import javax.ws.rs.Path;
import oms3.annotations.Description;
import oms3.annotations.Name;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;
import rhem.utils.DBResources;

/**
 * @version 1.0
 * @author Rumpal Sidhu
 */
@Name("RHEM-03: Get Soil Component, Ecological Site, "
        + "Soil Component Surface Texture Class, "
        + "Soil Component Slope Length and Steepness for RHEM Evaluation Area.")
@Description("Get map unit, soil component and ecological site information for "
        + "RHEM evaluation site. Get surface soil texture class relevant to "
        + "the soil component associated with a RHEM evaluation site within "
        + "an AoA. Get slope length and steepness relevant to the soil "
        + "component associated with a RHEM evaluation site within an AoA.")
@Path("m/rhem/getrhemcompesdsurftxtclsslopelen/1.0")
@Polling(first = 10000, next = 2000)
@Resource(from = DBResources.class)

public class V1_0 extends ModelDataService {

    private int aoaId;
    private int rhemSiteId;
    private JSONObject rhemSiteGeometryPoint;

    //Result Payload
    private ArrayList<Mapuint> mapunitList;

    @Override
    public void preProcess() throws ServiceException {
        this.mapunitList = new ArrayList<>();
        try {
            this.aoaId = getIntParam("AoAID");
            this.rhemSiteId = getIntParam("rhem_site_id");
            this.rhemSiteGeometryPoint = getParam("rhem_site_geometry");
        } catch (ServiceException ex) {
            LOG.log(Level.SEVERE, "RHEM-03: Error in processing the request JSON.", ex);
            throw new ServiceException("Error in processing the request JSON.", ex);
        }
    }

    @Override
    public void doProcess() throws ServiceException {
        try (Connection connection = getResourceJDBC(DBResources.MSSQL_SSURGO);) {
            this.getMapunitValues(connection);
            this.getComponentValues(connection);
            this.getSurfaceTextureClass(connection);
        } catch (ServiceException | SQLException se) {
            LOG.log(Level.SEVERE, "RHEM-03: SQLException.", se);
            throw new ServiceException("SQL problem.", se);
        }
    }

    private void getMapunitValues(Connection connection) throws ServiceException {
        try (Statement statement = connection.createStatement();) {
            //  Sets the GIS Engine of the GISObjects to the DBMS specified in
            //the creation of the “connection” variable.  MsSQL in this case
            GISObjectFactory.setGISObjectEngine(GISEngineFactory.createGISEngine(connection));
            GISObject geometry = GISObjectFactory.createGISObject(rhemSiteGeometryPoint);
            String mukey = null;
            String query = "SELECT mukey "
                    + "FROM ssurgo.soilmu_a "
                    + "WITH (index(geom_sidx)) "
                    + "WHERE the_geom.STContains(geometry::STGeomFromText('"
                    + geometry.getGeometry() + "', 0))=1 "
                    + "AND (geometry::STGeomFromText('"
                    + geometry.getGeometry() + "', 0).STIsValid())=1;";
            try (ResultSet resultSet = statement.executeQuery(query)) {
                while (resultSet.next()) {
                    mukey = resultSet.getString("mukey");
                }
            }
            if (mukey != null) {
                query = "SELECT musym, muname FROM ssurgo.mapunit WHERE mukey ='" + mukey + "';";
                String musym = null;
                String muname = null;
                try (ResultSet resultSet = statement.executeQuery(query)) {
                    while (resultSet.next()) {
                        musym = resultSet.getString("musym");
                        muname = resultSet.getString("muname");
                    }
                }

                ArrayList<Component> componentList = new ArrayList<>();
                query = "SELECT cokey, compname, comppct_r, slope_l, slope_r, "
                        + "slope_h, slopelenusle_l, slopelenusle_r, slopelenusle_h "
                        + "FROM ssurgo.component "
                        + "WHERE mukey ='" + mukey + "' ORDER BY comppct_r DESC;";
                try (ResultSet resultSet = statement.executeQuery(query)) {
                    while (resultSet.next()) {
                        componentList.add(new Component(resultSet.getString("cokey"),
                                resultSet.getString("compname"),
                                resultSet.getInt("comppct_r"),
                                resultSet.getInt("slope_l"),
                                resultSet.getInt("slope_r"),
                                resultSet.getInt("slope_h"),
                                resultSet.getDouble("slopelenusle_l"),
                                resultSet.getDouble("slopelenusle_r"),
                                resultSet.getDouble("slopelenusle_h")));
                    }
                }
                mapunitList.add(new Mapuint(musym, muname, componentList));
            }
        } catch (SQLException | GISObjectException | JSONException | IOException ex) {
            LOG.log(Level.SEVERE, "RHEM-03: SQLException.", ex);
            throw new ServiceException("SQL problem.", ex);
        }
    }

    private void getComponentValues(Connection connection) throws ServiceException {
        try (Statement statement = connection.createStatement();
                Connection esdConnection = getResourceJDBC(DBResources.MSSQL_ESD);
                Statement esdStmt = esdConnection.createStatement();) {
            for (Mapuint mapuint : this.mapunitList) {
                for (Component component : mapuint.getComponentList()) {
                    ArrayList<EcologicalSite> ecoList = new ArrayList<>();

                    String query = "SELECT ecoclassid "
                            + "FROM ssurgo.coecoclass "
                            + "WHERE cokey = '" + component.getCokey()
                            + "' AND ecoclassid LIKE 'R%';";
                    String ecoclassid = null;
                    try (ResultSet resultSet = statement.executeQuery(query)) {
                        while (resultSet.next()) {
                            ecoclassid = resultSet.getString("ecoclassid");
                        }
                    }

                    if (ecoclassid != null) {
                        query = "SELECT concat(range_site_primary_name, ' ', "
                                + "range_site_secondary_name, ' ', range_site_tertiary_name) "
                                + "AS es_range_name "
                                + "FROM esd.ecological_sites "
                                + "WHERE concat(es_type, es_mlra, es_mlru, es_site_number, es_state) = '"
                                + ecoclassid + "';";

                        try (ResultSet resultSet = esdStmt.executeQuery(query)) {
                            while (resultSet.next()) {
                                ecoList.add(new EcologicalSite(ecoclassid,
                                        resultSet.getString("es_range_name")));
                            }
                        }
                    }
                    component.setEcoSiteList(ecoList);
                }
            }
        } catch (SQLException ex) {
            LOG.log(Level.SEVERE, "RHEM-03: SQLException.", ex);
            throw new ServiceException("SQL problem.", ex);
        }
    }

    private void getSurfaceTextureClass(Connection connection) throws ServiceException {
        try (Statement statement = connection.createStatement();) {
            for (Mapuint mapuint : this.mapunitList) {
                for (Component component : mapuint.getComponentList()) {
                    ArrayList<SurfaceTexture> surfaceTextureList = new ArrayList<>();

                    String query = "SELECT texcl FROM ssurgo.chtexture "
                            + "WHERE chtgkey IN "
                            + "(SELECT chtgkey FROM ssurgo.chtexturegrp WHERE chkey IN "
                            + "(SELECT chkey FROM ssurgo.chorizon WHERE cokey = '"
                            + component.getCokey() + "' AND hzdept_r = 0));";
                    try (ResultSet resultSet = statement.executeQuery(query);) {
                        while (resultSet.next()) {
                            String texcl = resultSet.getString("texcl");

                            String q = "SELECT d2.texture_subclass_id, d3.text_abreviation, d3.text_label "
                                    + "FROM rhem.d_rhem_text_lookup d1 "
                                    + "JOIN rhem.d_rhem_texture_class_subclass d2 ON (d1.text_id = d2.text_subclass_id) "
                                    + "JOIN rhem.d_rhem_text_lookup d3 ON (d2.text_class_id = d3.text_id) "
                                    + "WHERE d1.text_label = '" + texcl + "' AND d2.obsolete = 'false';";

                            try (Statement stmt = connection.createStatement();
                                    ResultSet rst = stmt.executeQuery(q);) {
                                while (rst.next()) {
                                    int textureId = rst.getInt("texture_subclass_id");
                                    String textureClass = rst.getString("text_abreviation");
                                    String textureClassLabel = rst.getString("text_label");
                                    surfaceTextureList.add(new SurfaceTexture(texcl,
                                            textureId, textureClass, textureClassLabel));
                                }
                            }
                        }
                    }
                    component.setSurfaceTextureList(surfaceTextureList);
                }
            }
        } catch (SQLException ex) {
            LOG.log(Level.SEVERE, "RHEM-03: SQLException.", ex);
            throw new ServiceException("SQL problem.", ex);
        }
    }

    @Override
    public void postProcess() throws Exception {
        try {
            putResult("AoAID", this.aoaId, "Area of Analysis Identifier");
            putResult("rhem_site_id", this.rhemSiteId, "RHEM Evaluation Site Identifier");

            JSONArray mapUnitArray = new JSONArray();
            for (Mapuint mapunit : this.mapunitList) {
                JSONArray mapuintArr = new JSONArray();
                mapuintArr.put(JSONUtils.data("musym", mapunit.getMusym(), "Mapunit Symbol"));
                mapuintArr.put(JSONUtils.data("muname", mapunit.getMuname(), "Mapunit Name"));

                JSONArray componentArray = new JSONArray();
                for (Component component : mapunit.getComponentList()) {
                    JSONArray componentArr = new JSONArray();
                    componentArr.put(JSONUtils.data("cokey", component.getCokey(), "Soil Component Key"));
                    componentArr.put(JSONUtils.data("compname", component.getCompName(), "Soil Component Name"));
                    componentArr.put(JSONUtils.data("comppct_r", component.getComppctR(), "Soil Component Percentage Relative Value"));
                    componentArr.put(JSONUtils.dataDesc("slopepct_l", component.getSlopePctL(), "Slope Percent – Low Value"));
                    componentArr.put(JSONUtils.dataDesc("slopepct_r", component.getSlopePctR(), "Slope Percent – Representative Value"));
                    componentArr.put(JSONUtils.dataDesc("slopepct_h", component.getSlopePctH(), "Slope Percent – High Value"));
                    componentArr.put(JSONUtils.dataDesc("slopelen_l", Math.round(component.getSlopeLenL()), "Slope Length (meters) – Low Value"));
                    componentArr.put(JSONUtils.dataDesc("slopelen_r", Math.round(component.getSlopeLenR()), "Slope Length (meters) – Representative Value"));
                    componentArr.put(JSONUtils.dataDesc("slopelen_h", Math.round(component.getSlopeLenH()), "Slope Length (meters) – High Value"));
                    componentArr.put(JSONUtils.dataDesc("slopelen_ft_l", Math.round(component.getSlopeLenFtL()), "Slope Length (feet) – Low Value"));
                    componentArr.put(JSONUtils.dataDesc("slopelen_ft_r", Math.round(component.getSlopeLenFtR()), "Slope Length (feet) – Representative Value"));
                    componentArr.put(JSONUtils.dataDesc("slopelen_ft_h", Math.round(component.getSlopeLenFtH()), "Slope Length (feet) – High Value"));

                    JSONArray ecoSiteArray = new JSONArray();
                    for (EcologicalSite ecoSite : component.getEcoSiteList()) {
                        JSONArray ecoSiteArr = new JSONArray();
                        ecoSiteArr.put(JSONUtils.data("es_id", ecoSite.getEsId(), "Ecological Site Identifier"));
                        ecoSiteArr.put(JSONUtils.data("es_range_name", ecoSite.getEsRangeName(), "Ecological Site Name"));
                        ecoSiteArray.put(JSONUtils.dataDesc("ecological_site", ecoSiteArr, "Ecological Site"));
                    }
                    componentArr.put(JSONUtils.dataDesc("ecological_site_list", ecoSiteArray, "Ecological Site List"));

                    JSONArray surfaceTestureArray = new JSONArray();
                    for (SurfaceTexture texture : component.getSurfaceTextureList()) {
                        JSONArray surfaceTextureArr = new JSONArray();
                        surfaceTextureArr.put(JSONUtils.dataDesc("texcl", texture.getTexcl(), "SSURGO Texture Class"));
                        surfaceTextureArr.put(JSONUtils.dataDesc("rhem_texture_id ", texture.getTextureId(), "RHEM Texture Class Identifier"));
                        surfaceTextureArr.put(JSONUtils.dataDesc("rhem_texture_class ", texture.getTextureClass(), "RHEM Texture Class"));
                        surfaceTextureArr.put(JSONUtils.dataDesc("rhem_texture_class_label ", texture.getTextureClassLabel(), "RHEM Texture Class Label"));
                        surfaceTestureArray.put(JSONUtils.dataDesc("surface_texture_class", surfaceTextureArr, "Soil Component Surface Texture Class"));
                    }
                    componentArr.put(JSONUtils.dataDesc("surface_texture_class_list", surfaceTestureArray, "Soil Component Surface Texture Class List"));

                    componentArray.put(JSONUtils.dataDesc("component", componentArr, "Component"));
                }
                mapuintArr.put(JSONUtils.dataDesc("component_list", componentArray, "Component List"));
                mapUnitArray.put(JSONUtils.dataDesc("mapunit", mapuintArr, "Mapuint"));
            }
            putResult("mapunit_list", mapUnitArray, "Mapuint List");
        } catch (JSONException ex) {
            LOG.log(Level.SEVERE, "RHEM-03: Error in processing the reponse JSON.", ex);
            throw new ServiceException("Error in processing the reponse JSON.", ex);
        }
    }

    /**
     * Get the full JSON parameter record.
     *
     * @param name
     * @return the JSON record.
     */
    private JSONObject getParam(String name) throws ServiceException {
        JSONObject p = getParamMap().get(name);
        if (p == null) {
            throw new ServiceException("Parameter not found: '" + name + "'");
        }
        return p;
    }

    static class Mapuint {

        protected String musym;
        protected String muname;
        protected ArrayList<Component> componentList;

        public Mapuint(String musym, String muname,
                ArrayList<Component> componentList) {
            this.musym = musym;
            this.muname = muname;
            this.componentList = componentList;
        }

        public String getMusym() {
            return this.musym;
        }

        public String getMuname() {
            return this.muname;
        }

        public ArrayList<Component> getComponentList() {
            return this.componentList;
        }
    }

    static class Component {

        protected String cokey;
        protected String compName;
        protected int comppctR;
        //slope length and steepness
        protected int slopePctL;
        protected int slopePctR;
        protected int slopePctH;
        protected double slopeLenL;
        protected double slopeLenR;
        protected double slopeLenH;
        protected double slopeLenFtL;
        protected double slopeLenFtR;
        protected double slopeLenFtH;

        protected ArrayList<EcologicalSite> ecoSiteList;
        protected ArrayList<SurfaceTexture> surfaceTextureList;

        public Component(String cokey, String compName, int comppctR,
                int slopePctL, int slopePctR, int slopePctH,
                double slopeLenL, double slopeLenR,
                double slopeLenH) {
            this.cokey = cokey;
            this.compName = compName;
            this.comppctR = comppctR;
            this.slopePctL = slopePctL;
            this.slopePctR = slopePctR;
            this.slopePctH = slopePctH;
            this.slopeLenL = slopeLenL;
            this.slopeLenR = slopeLenR;
            this.slopeLenH = slopeLenH;
            this.slopeLenFtL = 3.28084 * slopeLenL;
            this.slopeLenFtR = 3.28084 * slopeLenR;
            this.slopeLenFtH = 3.28084 * slopeLenH;
        }

        public String getCokey() {
            return this.cokey;
        }

        public String getCompName() {
            return this.compName;
        }

        public int getComppctR() {
            return this.comppctR;
        }

        public int getSlopePctL() {
            return this.slopePctL;
        }

        public int getSlopePctR() {
            return this.slopePctR;
        }

        public int getSlopePctH() {
            return this.slopePctH;
        }

        public double getSlopeLenL() {
            return this.slopeLenL;
        }

        public double getSlopeLenR() {
            return this.slopeLenR;
        }

        public double getSlopeLenH() {
            return this.slopeLenH;
        }

        public double getSlopeLenFtL() {
            return this.slopeLenFtL;
        }

        public double getSlopeLenFtR() {
            return this.slopeLenFtR;
        }

        public double getSlopeLenFtH() {
            return this.slopeLenFtH;
        }

        public ArrayList<EcologicalSite> getEcoSiteList() {
            return this.ecoSiteList;
        }

        public ArrayList<SurfaceTexture> getSurfaceTextureList() {
            return this.surfaceTextureList;
        }

        public void setEcoSiteList(ArrayList<EcologicalSite> list) {
            this.ecoSiteList = list;
        }

        public void setSurfaceTextureList(ArrayList<SurfaceTexture> list) {
            this.surfaceTextureList = list;
        }

    }

    static class EcologicalSite {

        protected String esId;
        protected String esRangeName;

        public EcologicalSite(String esId, String esRangeName) {
            this.esId = esId;
            this.esRangeName = esRangeName;
        }

        public String getEsId() {
            return esId;
        }

        public String getEsRangeName() {
            return esRangeName;
        }
    }

    static class SurfaceTexture {

        protected String texcl;
        protected int textureId;
        protected String textureClass;
        protected String textureClassLabel;

        public SurfaceTexture(String texcl, int textureId, String textureClass,
                String textureClassLabel) {
            this.texcl = texcl;
            this.textureId = textureId;
            this.textureClass = textureClass;
            this.textureClassLabel = textureClassLabel;
        }

        public String getTexcl() {
            return this.texcl;
        }

        public int getTextureId() {
            return this.textureId;
        }

        public String getTextureClass() {
            return this.textureClass;
        }

        public String getTextureClassLabel() {
            return this.textureClassLabel;
        }
    }
}