/*
 * $Id$
 *
 * This file is part of the Cloud Services Integration Platform (CSIP),
 * a Model-as-a-Service framework, API, and application suite.
 *
 * 2012-2020, 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;

import csip.annotations.Resource;
import static csip.annotations.ResourceType.JDBC;
import static m.rhem.DBResources.*;

@Resource(type = JDBC, file = "${crdb.db}", id = CRDB, env = {
  "removeAbandoned=false", "defaultReadOnly=true", "defaultAutoCommit=false",
  "jdbcInterceptors=org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
  + "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;"
  + "org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer"
})
@Resource(type = JDBC, file = "${esd.db}", id = ESD, env = {
  "removeAbandoned=false", "defaultReadOnly=true", "defaultAutoCommit=false",
  "jdbcInterceptors=org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
  + "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;"
  + "org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer"
})

@Resource(type = JDBC, file = "${sdm.rest.db}", id = SDM_REST, env = {
  "driverClassName=csip.sdm.SDMDriver", "validationQuery=SELECT 1 FROM mapunit;",
  "maxWait=300000", "testOnBorrow=false"
})

/**
 * All external RHEM service resources.
 */
public interface DBResources {

  String CRDB = "crdb";
  String ESD = "esd";
  String SDM_REST = "SDMDriver";


  static String RunRHEMQuery01(String soilTexture) {
    return "SELECT clay_diameter, silt_diameter, "
        + "small_aggregates_diameter, large_aggregates_diameter, "
        + "sand_diameter, clay_specific_gravity, "
        + "silt_specific_gravity, small_aggregates_specific_gravity, "
        + "large_aggregates_specific_gravity, sand_specific_gravity, "
        + "mean_matric_potential, pore_size_distribution, mean_porosity, "
        + "clay_fraction, silt_fraction, small_aggregates_fraction, "
        + "large_aggregates_fraction, sand_fraction, max_sat "
        + "FROM rhem.soil_texture_table "
        + "WHERE class_name ='" + soilTexture + "';";
  }


  static String RHEM01Query02(String climateStationId) {
    return "SELECT avg_yearly_precip_mm "
        + "FROM rhem.d_rhem_climate_stations_avg_300yr_est_rain "
        + "WHERE station_id = '" + climateStationId + "';";
  }


  static String RHEM02Query01(String stateId) {
    return "SELECT state_name, latitude, longitude, zoom "
        + "FROM rhem.d_rhem_climate_station_states "
        + "WHERE state_id = '" + stateId + "';";
  }


  static String RHEM02Query02(String stateId) {
    return "SELECT s.state, s.station, s.station_id, s.latitude, "
        + "s.longitude, s.years, s.elevation, "
        + "est.avg_yearly_precip_mm, est.jan_precip_mm, "
        + "est.feb_precip_mm, est.mar_precip_mm, est.apr_precip_mm, "
        + "est.may_precip_mm, est.jun_precip_mm, est.jul_precip_mm, "
        + "est.aug_precip_mm, est.sep_precip_mm, est.oct_precip_mm, "
        + "est.nov_precip_mm, est.dec_precip_mm "
        + "FROM rhem.d_rhem_climate_stations s "
        + "JOIN rhem.d_rhem_climate_stations_avg_300yr_est_rain est "
        + "ON (s.station_id = est.station_id) "
        + "WHERE s.state = '" + stateId + "';";
  }


  static String RHEM03Query01(String geometry) {
    return "SELECT mupoly.mukey, mapunit.musym, mapunit.muname "
        + "FROM dbo.mupolygon AS mupoly "
        + "WITH (index(SI_mupolygon_24876)) "
        + "JOIN dbo.mapunit AS mapunit "
        + "ON mupoly.mukey = mapunit.mukey "
        + "INNER JOIN dbo.legend ON mapunit.lkey=dbo.legend.lkey "
        + "WHERE mupoly.mupolygongeo.STIntersects( geometry::STGeomFromText('"
        + geometry + "', 4326)) = 1 AND legend.areatypename like 'Non-MLRA%';";
  }


  static String RHEM03Query02(String mukey) {
    return "SELECT cokey, compname, comppct_r, slope_l, slope_r, slope_h "
        + "FROM dbo.component "
        + "WHERE mukey ='" + mukey + "' ORDER BY comppct_r DESC;";
  }


  static String RHEM03Query03(String cokey) {
    return "SELECT ecoclassid "
        + "FROM dbo.coecoclass "
        + "WHERE cokey = '" + cokey
        + "' AND ecoclassid LIKE 'R%';";
  }


  static String RHEM03Query04(String ecoclassid) {
    return "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 + "';";
  }


  static String RHEM03Query05(String cokey) {
    return "SELECT texcl FROM dbo.chtexture "
        + "WHERE chtgkey IN "
        + "(SELECT chtgkey FROM dbo.chtexturegrp WHERE chkey IN "
        + "(SELECT chkey FROM dbo.chorizon WHERE cokey = '"
        + cokey + "' AND hzdept_r = 0));";
  }


  static String RHEM03Query06(String texcl) {
    return "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';";
  }


  static String RHEM04Query01() {
    return "SELECT text_id, text_abreviation, text_label "
        + "FROM rhem.d_rhem_text_lookup "
        + "WHERE text_id "
        + "IN (SELECT DISTINCT(text_class_id) "
        + "FROM rhem.d_rhem_texture_class_subclass "
        + "WHERE obsolete = 'false');";
  }


  static String RHEM05Query01() {
    return "SELECT choice_id, choice_label "
        + "FROM rhem.d_rhem_slope_shape "
        + "WHERE obsolete = 'false';";
  }


  static String RHEM07Query01(String climatestationId) {
    return "SELECT avg_yearly_precip_mm "
        + "FROM rhem.d_rhem_climate_stations_avg_300yr_est_rain "
        + "WHERE station_id = '" + climatestationId + "';";
  }
}