package m.wqm.thresholdscores;

import csip.ModelDataService;
import csip.ServiceException;
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 java.util.Map;
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;

/**
 *
 * @author Srinivas Reddy Kontham
 * @author Shaun Case
 */
@Name("WQM-13: WQM Concern Treatment Level Threshold Scores (WQMThresholdScores)")
@Description("This service computes treatment level threshold scores for each of the WQM concerns for an area of analysis. The service uses nutrient soil leaching potentials from WQM-5, sediment and nutrient soil runoff potentials from WQM-6, hazard ratings from pesticide-related WQM concerns from WQM-11, required treatment level from WQM-1, and the AoA climate R factor from WQM-12 to calculate the threshold scores.")
@Path("m/thresholdscores/1.0")

public class V1_0 extends ModelDataService {

    private String error_msg;

    ArrayList<V1_0.Input> components; // store the set of all input soilcomponents as objects
    ArrayList<V1_0.Result1> result1;  // store the result as objects

    @Override
    // reading the inputs from the json file into input object and placing it in the arraylist
    protected void preProcess() throws Exception {
        this.components = new ArrayList<>();
        this.result1 = new ArrayList<>();
        this.error_msg = "";

        try {
            JSONArray groups = getJSONArrayParam("components");
            for (int i = 0; i < groups.length(); i++) {
                Map<String, JSONObject> group = JSONUtils.preprocess(groups.getJSONArray(i));
                int AoAId = JSONUtils.getIntParam(group, "AoAId", 0);
                String aoa_nslp = JSONUtils.getStringParam(group, "aoa_nslp", "err");
                String aoa_srp = JSONUtils.getStringParam(group, "aoa_srp", "err");
                String aoa_phr_leach_human = JSONUtils.getStringParam(group, "aoa_phr_leach_human", "err");
                String aoa_phr_leach_matcfish = JSONUtils.getStringParam(group, "aoa_phr_leach_matcfish", "err");
                String aoa_phr_sorun_human = JSONUtils.getStringParam(group, "aoa_phr_sorun_human", "err");
                String aoa_phr_sorun_matcfish = JSONUtils.getStringParam(group, "aoa_phr_sorun_matcfish", "err");
                String aoa_phr_adrun_human = JSONUtils.getStringParam(group, "aoa_phr_adrun_human", "err");
                String aoa_phr_adrun_stvfish = JSONUtils.getStringParam(group, "aoa_phr_adrun_stvfish", "err");
                String aoa_treatment_level = JSONUtils.getStringParam(group, "aoa_treatment_level", "err");
                int aoa_rfactor = JSONUtils.getIntParam(group, "aoa_rfactor", 0);
                components.add(new V1_0.Input(AoAId, aoa_nslp, aoa_srp, aoa_phr_leach_human, aoa_phr_leach_matcfish, aoa_phr_sorun_human, aoa_phr_sorun_matcfish, aoa_phr_adrun_human, aoa_phr_adrun_stvfish, aoa_treatment_level, aoa_rfactor));
            }
        } catch (ServiceException | JSONException ex) {
            this.error_msg = "Cannot process the input JSON: " + ex.getMessage();
            LOG.log(Level.SEVERE, this.error_msg);
        }
    }

    @Override
    protected String process() throws Exception {
        if (this.error_msg.isEmpty()) {
            try (
                    Connection conn = wqm.utils.WQMTools.getConnection("wqm", LOG);
                    Statement statement = conn.createStatement();) {

                for (V1_0.Input ip : components) {
                    String query = "SELECT threshold_treatment_score FROM wqm_threshold_scores WHERE wqm_concern=" + "'" + "Nitrogen in Ground Water" + "'" + "AND hazard_loss_rating ='" + ip.aoa_nslp + "' AND treatment_level='" + ip.aoa_treatment_level + "';";
                    ResultSet results = statement.executeQuery(query);
                    int aoa_nleach_threshold = 0;
                    while (results.next()) {
                        aoa_nleach_threshold = results.getInt("threshold_treatment_score");
                    }
                    query = "SELECT threshold_treatment_score FROM wqm_threshold_scores WHERE wqm_concern=" + "'" + "Nitrogen in Surface Water" + "'" + "AND hazard_loss_rating ='" + ip.aoa_srp + "'AND rfact_range_min <" + ip.aoa_rfactor + " AND rfact_range_max >=" + ip.aoa_rfactor + "AND treatment_level='" + ip.aoa_treatment_level + "';";
                    results = statement.executeQuery(query);
                    int aoa_nrun_threshold = 0;
                    while (results.next()) {
                        aoa_nrun_threshold = results.getInt("threshold_treatment_score");
                    }
                    query = "SELECT threshold_treatment_score FROM wqm_threshold_scores WHERE wqm_concern=" + "'" + "Sediment in Surface Water" + "'" + "AND hazard_loss_rating ='" + ip.aoa_srp + "'AND rfact_range_min <" + ip.aoa_rfactor + " AND rfact_range_max >=" + ip.aoa_rfactor + "AND treatment_level='" + ip.aoa_treatment_level + "';";
                    results = statement.executeQuery(query);
                    int aoa_sedrun_threshold = 0;
                    while (results.next()) {
                        aoa_sedrun_threshold = results.getInt("threshold_treatment_score");
                    }
                    query = "SELECT threshold_treatment_score FROM wqm_threshold_scores WHERE wqm_concern=" + "'" + "Phosphorus in Surface Water" + "'" + "AND hazard_loss_rating ='" + ip.aoa_srp + "'AND rfact_range_min <" + ip.aoa_rfactor + " AND rfact_range_max >=" + ip.aoa_rfactor + "AND treatment_level='" + ip.aoa_treatment_level + "';";
                    results = statement.executeQuery(query);
                    int aoa_prun_threshold = 0;
                    while (results.next()) {
                        aoa_prun_threshold = results.getInt("threshold_treatment_score");
                    }
                    query = "SELECT threshold_treatment_score FROM wqm_threshold_scores WHERE wqm_concern=" + "'" + "Pesticide (All)" + "'" + "AND hazard_loss_rating ='" + ip.aoa_phr_leach_human + "' AND treatment_level='" + ip.aoa_treatment_level + "';";
                    results = statement.executeQuery(query);
                    int aoa_pleach_human_threshold = 0;
                    while (results.next()) {
                        aoa_pleach_human_threshold = results.getInt("threshold_treatment_score");
                    }
                    query = "SELECT threshold_treatment_score FROM wqm_threshold_scores WHERE wqm_concern=" + "'" + "Pesticide (All)" + "'" + "AND hazard_loss_rating ='" + ip.aoa_phr_leach_matcfish + "' AND treatment_level='" + ip.aoa_treatment_level + "';";
                    results = statement.executeQuery(query);
                    int aoa_pleach_matcfish_threshold = 0;
                    while (results.next()) {
                        aoa_pleach_matcfish_threshold = results.getInt("threshold_treatment_score");
                    }
                    query = "SELECT threshold_treatment_score FROM wqm_threshold_scores WHERE wqm_concern=" + "'" + "Pesticide (All)" + "'" + "AND hazard_loss_rating ='" + ip.aoa_phr_sorun_human + "' AND treatment_level='" + ip.aoa_treatment_level + "';";
                    results = statement.executeQuery(query);
                    int aoa_psorun_human_threshold = 0;
                    while (results.next()) {
                        aoa_psorun_human_threshold = results.getInt("threshold_treatment_score");
                    }
                    query = "SELECT threshold_treatment_score FROM wqm_threshold_scores WHERE wqm_concern=" + "'" + "Pesticide (All)" + "'" + "AND hazard_loss_rating ='" + ip.aoa_phr_sorun_matcfish + "' AND treatment_level='" + ip.aoa_treatment_level + "';";
                    results = statement.executeQuery(query);
                    int aoa_psorun_matcfish_threshold = 0;
                    while (results.next()) {
                        aoa_psorun_matcfish_threshold = results.getInt("threshold_treatment_score");
                    }
                    query = "SELECT threshold_treatment_score FROM wqm_threshold_scores WHERE wqm_concern=" + "'" + "Pesticide (All)" + "'" + "AND hazard_loss_rating ='" + ip.aoa_phr_adrun_human + "' AND treatment_level='" + ip.aoa_treatment_level + "';";
                    results = statement.executeQuery(query);
                    int aoa_padrun_human_threshold = 0;
                    while (results.next()) {
                        aoa_padrun_human_threshold = results.getInt("threshold_treatment_score");
                    }
                    query = "SELECT threshold_treatment_score FROM wqm_threshold_scores WHERE wqm_concern=" + "'" + "Pesticide (All)" + "'" + "AND hazard_loss_rating ='" + ip.aoa_phr_adrun_stvfish + "' AND treatment_level='" + ip.aoa_treatment_level + "';";
                    results = statement.executeQuery(query);
                    int aoa_padrun_stvfish_threshold = 0;
                    while (results.next()) {
                        aoa_padrun_stvfish_threshold = results.getInt("threshold_treatment_score");
                    }
                    String rating = "INTERMEDIATE";
                    query = "SELECT threshold_treatment_score FROM wqm_threshold_scores WHERE wqm_concern=" + "'" + "Pesticide (All)" + "'" + "AND hazard_loss_rating ='" + rating + "' AND treatment_level='" + ip.aoa_treatment_level + "';";
                    results = statement.executeQuery(query);
                    int aoa_pdrift_human_threshold = 0;
                    while (results.next()) {
                        aoa_pdrift_human_threshold = results.getInt("threshold_treatment_score");
                    }
                    query = "SELECT threshold_treatment_score FROM wqm_threshold_scores WHERE wqm_concern=" + "'" + "Pesticide (All)" + "'" + "AND hazard_loss_rating ='" + rating + "' AND treatment_level='" + ip.aoa_treatment_level + "';";
                    results = statement.executeQuery(query);
                    int aoa_pdrift_fish_threshold = 0;
                    while (results.next()) {
                        aoa_pdrift_fish_threshold = results.getInt("threshold_treatment_score");
                    }
                    result1.add(new V1_0.Result1(ip.AoAId, aoa_nleach_threshold, aoa_nrun_threshold, aoa_sedrun_threshold, aoa_prun_threshold, aoa_pleach_human_threshold, aoa_pleach_matcfish_threshold, aoa_psorun_human_threshold, aoa_psorun_matcfish_threshold, aoa_padrun_human_threshold, aoa_padrun_stvfish_threshold, aoa_pdrift_human_threshold, aoa_pdrift_fish_threshold));
                }
            } catch (ServiceException | SQLException ex) {
                this.error_msg = "Cannot process that request: " + ex.getMessage();
                LOG.log(Level.SEVERE, this.error_msg);
            }
        }

        return (this.error_msg.isEmpty() ? EXEC_OK : this.error_msg);
    }

    @Override
    //writing the results back to JSON
    protected void postProcess() throws Exception {
        if (this.error_msg.isEmpty()) {
            try {
                JSONArray result1Arr = new JSONArray();
                for (V1_0.Result1 rs1 : result1) {
                    JSONArray tmpArr = new JSONArray();
                    tmpArr.put(JSONUtils.dataDesc("AoAId", rs1.AoAId, "Area of Analysis Identifier"));
                    tmpArr.put(JSONUtils.dataDesc("aoa_nleach_threshold", rs1.aoa_nleach_threshold, "Nitrogen Leaching Threshold Score"));
                    tmpArr.put(JSONUtils.dataDesc("aoa_nrun_threshold", rs1.aoa_nrun_threshold, "Nitrogen Runoff Threshold Score"));
                    tmpArr.put(JSONUtils.dataDesc("aoa_sedrun_threshold", rs1.aoa_sedrun_threshold, "Sediment Runoff Threshold Score"));
                    tmpArr.put(JSONUtils.dataDesc("aoa_prun_threshold", rs1.aoa_prun_threshold, "Phosphorus Runoff Threshold Score"));
                    tmpArr.put(JSONUtils.dataDesc("aoa_pleach_human_threshold", rs1.aoa_pleach_human_threshold, "Pesticide Leaching Threshold Score,Human"));
                    tmpArr.put(JSONUtils.dataDesc("aoa_pleach_matcfish_threshold", rs1.aoa_pleach_matcfish_threshold, "Pesticide Leaching Threshold Score, Fish"));
                    tmpArr.put(JSONUtils.dataDesc("aoa_psorun_human_threshold", rs1.aoa_psorun_human_threshold, "Pesticide Solution Runoff Threshold Score,Human"));
                    tmpArr.put(JSONUtils.dataDesc("aoa_psorun_matcfish_threshold", rs1.aoa_psorun_matcfish_threshold, "Pesticide Solution Runoff Threshold Score, Fish"));
                    tmpArr.put(JSONUtils.dataDesc("aoa_padrun_human_threshold", rs1.aoa_padrun_human_threshold, "Pesticide Adsorbed Runoff Threshold Score,Human"));
                    tmpArr.put(JSONUtils.dataDesc("aoa_padrun_stvfish_threshold", rs1.aoa_padrun_stvfish_threshold, "Pesticide Adsorbed Runoff Threshold Score,Fish"));
                    tmpArr.put(JSONUtils.dataDesc("aoa_pdrift_human_threshold", rs1.aoa_pdrift_human_threshold, "Pesticide Drift Threshold Score, Human"));
                    tmpArr.put(JSONUtils.dataDesc("aoa_pdrift_fish_threshold", rs1.aoa_pdrift_fish_threshold, "Pesticide Drift Threshold Score, Fish"));
                    result1Arr.put(JSONUtils.dataDesc("pesticide summary", tmpArr, "Pest"));
                }

                putResult("operation", result1Arr);
            } catch (JSONException ex) {
                this.error_msg = "Could not create result JSON: " + ex.getMessage();
                LOG.log(Level.SEVERE, this.error_msg);
                throw new Exception(this.error_msg);
            }
        }
    }

    public class Input {

        int AoAId;
        String aoa_nslp;
        String aoa_srp;
        String aoa_phr_leach_human;
        String aoa_phr_leach_matcfish;
        String aoa_phr_sorun_human;
        String aoa_phr_sorun_matcfish;
        String aoa_phr_adrun_human;
        String aoa_phr_adrun_stvfish;
        String aoa_treatment_level;
        int aoa_rfactor;

        public Input(int AoAId, String aoa_nslp, String aoa_srp, String aoa_phr_leach_human, String aoa_phr_leach_matcfish, String aoa_phr_sorun_human, String aoa_phr_sorun_matcfish, String aoa_phr_adrun_human, String aoa_phr_adrun_stvfish, String aoa_treatment_level, int aoa_rfactor) {
            this.AoAId = AoAId;
            this.aoa_nslp = aoa_nslp;
            this.aoa_srp = aoa_srp;
            this.aoa_phr_leach_human = aoa_phr_leach_human;
            this.aoa_phr_leach_matcfish = aoa_phr_leach_matcfish;
            this.aoa_phr_sorun_human = aoa_phr_sorun_human;
            this.aoa_phr_sorun_matcfish = aoa_phr_sorun_matcfish;
            this.aoa_phr_adrun_human = aoa_phr_adrun_human;
            this.aoa_phr_adrun_stvfish = aoa_phr_adrun_stvfish;
            this.aoa_treatment_level = aoa_treatment_level;
            this.aoa_rfactor = aoa_rfactor;
        }

    }

    public class Result1 {

        int AoAId;
        int aoa_nleach_threshold;
        int aoa_nrun_threshold;
        int aoa_sedrun_threshold;
        int aoa_prun_threshold;
        int aoa_pleach_human_threshold;
        int aoa_pleach_matcfish_threshold;
        int aoa_psorun_human_threshold;
        int aoa_psorun_matcfish_threshold;
        int aoa_padrun_human_threshold;
        int aoa_padrun_stvfish_threshold;
        int aoa_pdrift_human_threshold;
        int aoa_pdrift_fish_threshold;

        public Result1(int AoAId, int aoa_nleach_threshold, int aoa_nrun_threshold, int aoa_sedrun_threshold, int aoa_prun_threshold, int aoa_pleach_human_threshold, int aoa_pleach_matcfish_threshold, int aoa_psorun_human_threshold, int aoa_psorun_matcfish_threshold, int aoa_padrun_human_threshold, int aoa_padrun_stvfish_threshold, int aoa_pdrift_human_threshold, int aoa_pdrift_fish_threshold) {
            this.AoAId = AoAId;
            this.aoa_nleach_threshold = aoa_nleach_threshold;
            this.aoa_nrun_threshold = aoa_nrun_threshold;
            this.aoa_sedrun_threshold = aoa_sedrun_threshold;
            this.aoa_prun_threshold = aoa_prun_threshold;
            this.aoa_pleach_human_threshold = aoa_pleach_human_threshold;
            this.aoa_pleach_matcfish_threshold = aoa_pleach_matcfish_threshold;
            this.aoa_psorun_human_threshold = aoa_psorun_human_threshold;
            this.aoa_psorun_matcfish_threshold = aoa_psorun_matcfish_threshold;
            this.aoa_padrun_human_threshold = aoa_padrun_human_threshold;
            this.aoa_padrun_stvfish_threshold = aoa_padrun_stvfish_threshold;
            this.aoa_pdrift_human_threshold = aoa_pdrift_human_threshold;
            this.aoa_pdrift_fish_threshold = aoa_pdrift_fish_threshold;
        }
    }

}