/*
 * $Id$
 *
 * This file is part of the Cloud Services Integration Platform (CSIP),
 * a Model-as-a-Service framework, API, and application suite.
 *
 * 2012-2017, OMSLab, Colorado State University.
 *
 * OMSLab licenses this file to you under the MIT license.
 * See the LICENSE file in the project root for more information.
 */
package m.rhem.rhem03_compEsd;

import gisobjects.db.GISEngine;
import static gisobjects.db.GISEngineFactory.createGISEngine;
import gisobjects.GISObject;
import gisobjects.GISObjectFactory;
import csip.ModelDataService;
import csip.api.server.ServiceException;
import csip.annotations.Polling;
import csip.annotations.Resource;
import csip.utils.JSONUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import javax.ws.rs.Path;
import csip.annotations.Description;
import csip.annotations.Name;
import java.util.List;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONObject;
import m.rhem.DBResources;
import static m.rhem.DBResources.SDM_REST;

/**
 * RHEM-03: Get Soil Component, Ecological Site, Soil Component Surface Texture
 * Class, Soil Component Slope Length and Steepness for RHEM Evaluation Area
 *
 * @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/getrhemsoilcomponent/1.0")
@Polling(first = 10000, next = 2000)
@Resource(from = DBResources.class)
public class V1_0 extends ModelDataService {

  int aoaId, rhemSiteId;
  JSONObject rhemSiteGeometryPoint;
  List<Mapunit> mapunitList = new ArrayList<>();


  @Override
  public void preProcess() throws ServiceException {
    aoaId = parameter().getInt("AoAID");
    rhemSiteId = parameter().getInt("rhem_site_id");
    rhemSiteGeometryPoint = parameter().getParamJSON("rhem_site_geometry");
  }


  @Override
  public void doProcess() throws Exception {
    try (Connection sdmConnection = resources().getJDBC(SDM_REST);
        Connection crdbConnection = resources().getJDBC(DBResources.CRDB)) {
      GISEngine gisEngine = createGISEngine(sdmConnection);
      GISObject geometry = GISObjectFactory.createGISObject(rhemSiteGeometryPoint, gisEngine);
      getMapunitValues(sdmConnection, geometry);
      try (Connection esdConnection = resources().getJDBC(DBResources.ESD)) {
        getComponentValues(sdmConnection, esdConnection);
      }
      getSurfaceTextureClass(sdmConnection, crdbConnection);
    }
  }


  private void getMapunitValues(Connection sdmConnection, GISObject geometry) throws SQLException {
    try (Statement statement = sdmConnection.createStatement()) {
      String mukey = null, musym = null, muname = null;

      try (ResultSet rs = statement.executeQuery(DBResources.RHEM03Query01(geometry.getGeometry().toString()))) {
        while (rs.next()) {
          mukey = rs.getString("mukey");
          musym = rs.getString("musym");
          muname = rs.getString("muname");
        }
      }
      if (mukey != null) {
        List<Component> componentList = new ArrayList<>();
        try (ResultSet rs = statement.executeQuery(DBResources.RHEM03Query02(mukey))) {
          while (rs.next()) {
            componentList.add(new Component(rs.getString("cokey"),
                rs.getString("compname"),
                rs.getInt("comppct_r"),
                rs.getInt("slope_l"),
                rs.getInt("slope_r"),
                rs.getInt("slope_h")));
          }
        }
        mapunitList.add(new Mapunit(musym, muname, componentList));
      }
    }
  }


  private void getComponentValues(Connection connection, Connection esdConnection) throws SQLException {
    try (Statement statement = connection.createStatement()) {
      for (Mapunit mapunit : mapunitList) {
        for (Component component : mapunit.getComponentList()) {
          List<EcologicalSite> ecoList = new ArrayList<>();
          String ecoclassid = null;
          try (ResultSet resultSet = statement.executeQuery(DBResources.RHEM03Query03(component.getCokey()))) {
            while (resultSet.next()) {
              ecoclassid = resultSet.getString("ecoclassid");
            }
          }
          if (ecoclassid != null) {
            try (Statement esdStmt = esdConnection.createStatement();
                ResultSet rs = esdStmt.executeQuery(DBResources.RHEM03Query04(ecoclassid))) {

              while (rs.next()) {
                ecoList.add(new EcologicalSite(ecoclassid, rs.getString("es_range_name")));
              }
            }
          }
          component.setEcoSiteList(ecoList);
        }
      }
    }
  }


  private void getSurfaceTextureClass(Connection connection, Connection rhemConnection) throws SQLException {
    try (Statement statement = connection.createStatement()) {
      for (Mapunit mapunit : mapunitList) {
        for (Component component : mapunit.getComponentList()) {
          ArrayList<SurfaceTexture> surfaceTextureList = new ArrayList<>();
          try (ResultSet resultSet = statement.executeQuery(DBResources.RHEM03Query05(component.cokey))) {
            while (resultSet.next()) {
              String texcl = resultSet.getString("texcl");
              try (Statement stmt = rhemConnection.createStatement();
                  ResultSet rst = stmt.executeQuery(DBResources.RHEM03Query06(texcl))) {
                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);
        }
      }
    }
  }


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

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

      JSONArray componentArray = new JSONArray();
      for (Component component : mapunit.getComponentList()) {
        JSONArray componentArr = new JSONArray()
            .put(JSONUtils.dataDesc("cokey", component.getCokey(), "Soil Component Key"))
            .put(JSONUtils.dataDesc("compname", component.getCompName(), "Soil Component Name"))
            .put(JSONUtils.dataDesc("comppct_r", component.getComppctR(), "Soil Component Percentage Relative Value"))
            .put(JSONUtils.dataDesc("slopepct_l", component.getSlopePctL(), "Slope Percent – Low Value"))
            .put(JSONUtils.dataDesc("slopepct_r", component.getSlopePctR(), "Slope Percent – Representative Value"))
            .put(JSONUtils.dataDesc("slopepct_h", component.getSlopePctH(), "Slope Percent – High Value"));

        JSONArray ecoSiteArray = new JSONArray();
        for (EcologicalSite ecoSite : component.getEcoSiteList()) {
          JSONArray ecoSiteArr = new JSONArray()
              .put(JSONUtils.dataDesc("es_id", ecoSite.getEsId(), "Ecological Site Identifier"))
              .put(JSONUtils.dataDesc("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()
              .put(JSONUtils.dataDesc("texcl", texture.getTexcl(), "SSURGO Texture Class"))
              .put(JSONUtils.dataDesc("rhem_texture_id ", texture.getTextureId(), "RHEM Texture Class Identifier"))
              .put(JSONUtils.dataDesc("rhem_texture_class ", texture.getTextureClass(), "RHEM Texture Class"))
              .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"));
      }
      mapunitArr.put(JSONUtils.dataDesc("component_list", componentArray, "Component List"));
      mapUnitArray.put(JSONUtils.dataDesc("mapunit", mapunitArr, "Mapunit"));
    }
    results().put("mapunit_list", mapUnitArray, "Mapunit List");
  }

  static class Mapunit {

    String musym;
    String muname;
    List<Component> componentList;


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


    public String getMusym() {
      return musym;
    }


    public String getMuname() {
      return muname;
    }


    public List<Component> getComponentList() {
      return componentList;
    }
  }

  static class Component {

    String cokey;
    String compName;
    int comppctR;
    //slope length and steepness
    int slopePctL;
    int slopePctR;
    int slopePctH;

    List<EcologicalSite> ecoSiteList;
    List<SurfaceTexture> surfaceTextureList;


    public Component(String cokey, String compName, int comppctR,
        int slopePctL, int slopePctR, int slopePctH) {
      this.cokey = cokey;
      this.compName = compName;
      this.comppctR = comppctR;
      this.slopePctL = slopePctL;
      this.slopePctR = slopePctR;
      this.slopePctH = slopePctH;
    }


    public String getCokey() {
      return cokey;
    }


    public String getCompName() {
      return compName;
    }


    public int getComppctR() {
      return comppctR;
    }


    public int getSlopePctL() {
      return slopePctL;
    }


    public int getSlopePctR() {
      return slopePctR;
    }


    public int getSlopePctH() {
      return slopePctH;
    }


    public List<EcologicalSite> getEcoSiteList() {
      return ecoSiteList;
    }


    public List<SurfaceTexture> getSurfaceTextureList() {
      return surfaceTextureList;
    }


    public void setEcoSiteList(List<EcologicalSite> list) {
      ecoSiteList = list;
    }


    public void setSurfaceTextureList(List<SurfaceTexture> list) {
      surfaceTextureList = list;
    }

  }

  static class EcologicalSite {

    String esId;
    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 {

    String texcl;
    int textureId;
    String textureClass;
    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 texcl;
    }


    public int getTextureId() {
      return textureId;
    }


    public String getTextureClass() {
      return textureClass;
    }


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