package m.wqm.wqmsoilattributes;

/**
 *
 * @author RUMPAL SIDHU
 */
import oms3.annotations.*;
import csip.ModelDataService;
import csip.annotations.*;
import csip.utils.JSONUtils;
import org.codehaus.jettison.json.JSONArray;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;
import javax.ws.rs.Path;

@Name("WQM 2:")
@Description("Soil Component Attributes")
@Path("m/wqmsoilattributes/1.0")
@Polling(first = 10000, next = 2000)

public class V1_0 extends ModelDataService {

    //SQL params names here for quick modification
    private final String USER = "gras_ssurgo";
    private final String PASS = "admin";
    private final String HOST = "csip.engr.colostate.edu";
    private final String PORT = "5435";
    private final String DBNAME = "ssurgo";
    private final String JDBC_TYPE = "jdbc:postgresql://";
    private final String CONNECTION = JDBC_TYPE + HOST + ":" + PORT + "/" + DBNAME;
    private final String CLASS_NAME = "org.postgresql.Driver";

    //Request
    private int aoaId;
    private String aoaGeometry;

    //Response
    private ArrayList<Component> componentList;

    @Override
    protected void preProcess() throws Exception {
        aoaId = getIntParam("AoAId");
        aoaGeometry = getJSONParam("aoa_geometry").toString();
    }

    @Override
    protected String process() throws Exception {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet;
        String query;
        componentList = new ArrayList();

        HashMap<String, Double> aoa_mukeyList = new ServiceCall().intersect(aoaGeometry);

        try {
            Class.forName(CLASS_NAME);
            conn = DriverManager.getConnection(CONNECTION, USER, PASS);
            conn.setAutoCommit(false);
            statement = conn.createStatement();

            Set keys = aoa_mukeyList.keySet();
            Iterator ite = keys.iterator();
            while (ite.hasNext()) {
                String mukey = ite.next().toString();
                double aoaArea = (Double) aoa_mukeyList.get(mukey);
                query = "SELECT cokey, compname, comppct_r FROM ssurgo.component WHERE mukey='" + mukey + "' AND comppct_r IS NOT NULL ORDER BY cokey;";
                resultSet = statement.executeQuery(query);
                while (resultSet.next()) {
                    Component component = new Component();
                    component.setCokey(resultSet.getString("cokey"));
                    component.setName(resultSet.getString("compname"));
                    component.setArea(aoaArea * resultSet.getDouble("comppct_r"));
                    componentList.add(component);
                }
            }

            //For each soil component (cokey) in the AoA
            for (Component component : componentList) {
                //#Assign component-level parameters
                query = "SELECT hydgrp, slope_r, taxorder FROM ssurgo.component WHERE cokey='" + component.getCokey() + "';";
                resultSet = statement.executeQuery(query);
                while (resultSet.next()) {
                    component.setHsg(resultSet.getString("hydgrp"));
                    component.setSlope(resultSet.getDouble("slope_r"));
                    component.setTaxorder(resultSet.getString("taxorder"));
                }

                //#Find first mineral horizon layer and assign horizon-level parameters
                //For each chkey
                double aoa_comp_kfact;
                //For each cokey
                boolean check = false;
                query = "SELECT chkey, om_r, hzthk_r, kwfact, kffact FROM ssurgo.chorizon WHERE chkey IN (SELECT chkey FROM ssurgo.chorizon WHERE cokey='" + component.getCokey() + "');";
                resultSet = statement.executeQuery(query);
                while (resultSet.next()) {
                    String chkey = resultSet.getString("chkey");
                    double kwfact = resultSet.getDouble("kwfact");
                    boolean kwfact_b = resultSet.wasNull();
                    double kffact = resultSet.getDouble("kffact");
                    boolean kffact_b = resultSet.wasNull();

                    if (!kwfact_b || !kffact_b) {
                        if (!kffact_b) {
                            aoa_comp_kfact = kffact;
                        } else {
                            aoa_comp_kfact = kwfact;
                        }

                        component.setKfactor(aoa_comp_kfact);
                        component.setOrganicMatter(resultSet.getDouble("om_r"));
                        component.setHzdepth(resultSet.getDouble("hzthk_r"));
                        String query2 = "SELECT fragvol_r FROM ssurgo.chfrags WHERE chkey ='" + chkey + "';";
                        ResultSet resultSet2 = statement.executeQuery(query2);
                        while (resultSet2.next()) {
                            component.setCoarseFrag(resultSet2.getInt("fragvol_r"));
                        }

                        check = true;
                        break;
                    }
                }

                if (!check) {
                    query = "SELECT chkey, om_r, hzthk_r FROM ssurgo.chorizon WHERE chkey IN (SELECT chkey FROM ssurgo.chorizon WHERE cokey='" + component.getCokey() + "');";
                    resultSet = statement.executeQuery(query);
                    while (resultSet.next()) {
                        String chkey = resultSet.getString("chkey");

                        component.setKfactor(0.02);
                        component.setOrganicMatter(resultSet.getDouble("om_r"));
                        component.setHzdepth(resultSet.getDouble("hzthk_r"));

                        String query2 = "SELECT fragvol_r FROM ssurgo.chfrags WHERE chkey ='" + chkey + "';";
                        ResultSet resultSet2 = statement.executeQuery(query2);
                        while (resultSet2.next()) {
                            component.setCoarseFrag(resultSet2.getInt("fragvol_r"));
                        }
                        break;
                    }
                }
            }

        } catch (SQLException se) {
            LOG.info("Did not open database for GRAS-16f!");
            LOG.info(se.getMessage());
        } finally {
            if (statement != null) {
                statement.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return EXEC_OK;
    }

    @Override
    protected void postProcess() throws Exception {
        putResult("AoaId", aoaId, "Area of Analysis Identifier");
        JSONArray resultArray = new JSONArray();
        for (Component component : componentList) {
            JSONArray tmpArr = new JSONArray();
            tmpArr.put(JSONUtils.dataDesc("cokey", component.getCokey(), "Soil Component Key"));
            tmpArr.put(JSONUtils.dataDesc("compname", component.getName(), "Soil Component Name"));
            tmpArr.put(JSONUtils.dataDesc("aoa_comp_area", component.getArea(), "Soil Component Area (Acres) in the Area of Analysis"));
            tmpArr.put(JSONUtils.dataDesc("aoa_comp_hsg", component.getHsg(), "Hydrologic Soil Group of the Soil Component"));
            tmpArr.put(JSONUtils.dataDesc("aoa_comp_taxorder", component.getTaxorder(), "Taxonomic Order of the Soil Component"));
            tmpArr.put(JSONUtils.dataDesc("aoa_comp_kfact", component.getKfactor(), "K factor of the Surface Mineral Horizon of the Soil Component"));
            tmpArr.put(JSONUtils.dataDesc("aoa_comp_slope", component.getSlope(), "Slope Percentage of the Soil Component"));
            tmpArr.put(JSONUtils.dataDesc("aoa_comp_coarse_frag", component.getCoarseFrag(), "Weighted Average Coarse Rock Fragment Volume Percentage through the Profile of the Soil Component"));
            tmpArr.put(JSONUtils.dataDesc("aoa_comp_om", component.getOrganicMatter(), "Organic Matter Percentage of the  Surface Horizon of the Soil Componen"));
            tmpArr.put(JSONUtils.dataDesc("aoa_comp_hzdepth", component.getHzdepth(), "Depth (inches) of the Surface Horizon of the Soil Component"));
            tmpArr.put(JSONUtils.dataDesc("aoa_comp_cracksgr24", component.getCracksgr24(), "Surface Connected Macropores (Cracks) at Least 24 Inches Deep"));
            tmpArr.put(JSONUtils.dataDesc("aoa_comp_slopegr15", component.getSlopegr15(), "Field Slope is Greater Than 15%"));
            tmpArr.put(JSONUtils.dataDesc("aoa_comp_hwt_lt_24", component.getHwt_lt_24(), "High Water is Less than 24 Inches Under the Surface"));
            resultArray.put(JSONUtils.dataDesc("soil_component", tmpArr, "Entry for Soil Component"));
        }
        putResult("soil_component_list", resultArray);
    }
}