package m.wqm.nuttechscores;

import csip.ModelDataService;
import csip.ServiceException;
import csip.annotations.Resource;
import java.util.ArrayList;
import javax.ws.rs.Path;
import oms3.annotations.Description;
import oms3.annotations.Name;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONObject;
import csip.utils.JSONUtils;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import java.util.logging.Level;
import org.codehaus.jettison.json.JSONException;
import wqm.utils.DBResources;
import static wqm.utils.DBResources.WQM_ID;

/**
 * @version 1.0
 * @author SrinivasReddy kontham
 * @author Rumpal Sidhu
 * @ update Sandeep Kasavaraju
 */
@Name("WQM-14: Nutrient Technique Scores (NutTechScores)")
@Description("This service computes scores for techniques applied to "
        + "mitigate nitrogen leaching, nitrogen runoff, and phosphorus "
        + "runoff loss potential")
@Path("m/nut_tech_scores/1.0")
@Resource(from = DBResources.class)

public class V1_0 extends ModelDataService {

    private ArrayList<m.wqm.nuttechscores.V1_0.Input> components; // store the set of all input soilcomponents as objects
    private ArrayList<m.wqm.nuttechscores.V1_0.Result1> result1;  // store the result as objects
    private int aoaId;

    @Override
    protected void preProcess() throws ServiceException {

        components = new ArrayList<>();
        try {
            JSONArray groups = getJSONArrayParam("pestcomponents");
            for (int i = 0; i < groups.length(); i++) {
                Map<String, JSONObject> group = JSONUtils.preprocess(groups.getJSONArray(i));
                aoaId = JSONUtils.getIntParam(group, "AoAid", 0);
                int plan_techn_id = JSONUtils.getIntParam(group, "plan_techn_id", 0);
                String plan_techn_discrim_type = JSONUtils.getStringParam(group, "plan_techn_discrim_type", null);
                String plan_techn_discrim = JSONUtils.getStringParam(group, "plan_techn_discrim", null);
                components.add(new m.wqm.nuttechscores.V1_0.Input(aoaId, plan_techn_id,
                        plan_techn_discrim_type, plan_techn_discrim));
            }
        } catch (ServiceException | JSONException ex) {
            LOG.log(Level.SEVERE, "Error in processing the request JSON for WQM-14!", ex);
            throw new ServiceException("Error in processing the request JSON.", ex);
        }
    }

    @Override
    protected void doProcess() throws ServiceException {
        result1 = new ArrayList<>();
        try (Connection conn = getResourceJDBC(WQM_ID);
                Statement statement = conn.createStatement()) {

            int nleach_techn_score = 0;
            int nleach_avoid_techn_score = 0;
            int nleach_control_techn_score = 0;
            int nleach_trap_techn_score = 0;
            int nsurf_techn_score = 0;
            int nsurf_avoid_techn_score = 0;
            int nsurf_control_techn_score = 0;
            int nsurf_trap_techn_score = 0;
            int psurf_techn_score = 0;
            int psurf_avoid_techn_score = 0;
            int psurf_control_techn_score = 0;
            int psurf_trap_techn_score = 0;

            for (m.wqm.nuttechscores.V1_0.Input ip : components) {
                String q1;
                String q2;
                q1 = "SELECT nut_tech_score FROM wqm.wqm_nutrient_technique_scores "
                        + "WHERE nutrient_technique_id =" + ip.plan_techn_id
                        + " AND wqm_concern = %s"
                        + " AND tech_discrim_type ='" + ip.plan_techn_discrim_type
                        + "' AND tech_discrim ='" + ip.plan_techn_discrim + "' ;";
                q2 = "SELECT nut_tech_score FROM wqm.wqm_nutrient_technique_scores "
                        + "WHERE nutrient_technique_id =" + ip.plan_techn_id
                        + " AND wqm_concern = %s"
                        + " AND mode_of_action = %s"
                        + " AND tech_discrim_type ='" + ip.plan_techn_discrim_type
                        + "' AND tech_discrim ='" + ip.plan_techn_discrim + "' ;";

                if (ip.plan_techn_discrim_type != null && ip.plan_techn_discrim != null) {
                } else if (ip.plan_techn_discrim_type == null && ip.plan_techn_discrim == null) {
                    q1 = "SELECT nut_tech_score FROM wqm.wqm_nutrient_technique_scores "
                            + "WHERE nutrient_technique_id =" + ip.plan_techn_id
                            + " AND wqm_concern = %s"
                            + " AND tech_discrim_type IS NULL"
                            + " AND tech_discrim IS NULL;";
                    q2 = "SELECT nut_tech_score FROM wqm.wqm_nutrient_technique_scores "
                            + "WHERE nutrient_technique_id =" + ip.plan_techn_id
                            + " AND wqm_concern = %s"
                            + " AND mode_of_action = %s"
                            + " AND tech_discrim_type IS NULL"
                            + " AND tech_discrim IS NULL;";
                } else if (ip.plan_techn_discrim_type == null) {
                    q1 = "SELECT nut_tech_score FROM wqm.wqm_nutrient_technique_scores "
                            + "WHERE nutrient_technique_id =" + ip.plan_techn_id
                            + " AND wqm_concern = %s"
                            + " AND tech_discrim_type IS NULL"
                            + " AND tech_discrim ='" + ip.plan_techn_discrim + "' ;";
                    q2 = "SELECT nut_tech_score FROM wqm.wqm_nutrient_technique_scores "
                            + "WHERE nutrient_technique_id =" + ip.plan_techn_id
                            + " AND wqm_concern = %s"
                            + " AND mode_of_action = %s"
                            + " AND tech_discrim_type IS NULL"
                            + " AND tech_discrim ='" + ip.plan_techn_discrim + "' ;";
                } else if (ip.plan_techn_discrim == null) {
                    q1 = "SELECT nut_tech_score FROM wqm.wqm_nutrient_technique_scores "
                            + "WHERE nutrient_technique_id =" + ip.plan_techn_id
                            + " AND wqm_concern = %s"
                            + " AND tech_discrim_type ='" + ip.plan_techn_discrim_type
                            + "' AND tech_discrim IS NULL;";
                    q2 = "SELECT nut_tech_score FROM wqm.wqm_nutrient_technique_scores "
                            + "WHERE nutrient_technique_id =" + ip.plan_techn_id
                            + " AND wqm_concern = %s"
                            + " AND mode_of_action = %s"
                            + " AND tech_discrim_type ='" + ip.plan_techn_discrim_type
                            + "' AND tech_discrim IS NULL;";

                }

                String query = String.format(q1, "'Nitrogen in Ground Water'");
                ResultSet resultset = statement.executeQuery(query);
                while (resultset.next()) {
                    int techn_score = resultset.getInt("nut_tech_score");
                    nleach_techn_score += techn_score;
                }

                query = String.format(q2, "'Nitrogen in Ground Water'", "'Avoid'");
                resultset = statement.executeQuery(query);
                while (resultset.next()) {
                    int avoid_techn_score = resultset.getInt("nut_tech_score");
                    nleach_avoid_techn_score += avoid_techn_score;
                }

                query = String.format(q2, "'Nitrogen in Ground Water'", "'Control'");
                resultset = statement.executeQuery(query);
                while (resultset.next()) {
                    int control_techn_score = resultset.getInt("nut_tech_score");
                    nleach_control_techn_score += control_techn_score;
                }

                query = String.format(q2, "'Nitrogen in Ground Water'", "'Trap'");
                resultset = statement.executeQuery(query);
                while (resultset.next()) {
                    int trap_techn_score = resultset.getInt("nut_tech_score");
                    nleach_trap_techn_score += trap_techn_score;

                }

                //  #Compute nutrient technique mitigation scores for Nitrogen in Surface Water concern and increment total scores
                query = String.format(q1, "'Nitrogen in Surface Water'");
                resultset = statement.executeQuery(query);
                while (resultset.next()) {
                    int techn_score = resultset.getInt("nut_tech_score");
                    nsurf_techn_score += techn_score;
                }

                query = String.format(q2, "'Nitrogen in Surface Water'", "'Avoid'");
                resultset = statement.executeQuery(query);
                while (resultset.next()) {
                    int avoid_techn_score = resultset.getInt("nut_tech_score");
                    nsurf_avoid_techn_score += avoid_techn_score;
                }

                query = String.format(q2, "'Nitrogen in Surface Water'", "'Control'");
                resultset = statement.executeQuery(query);
                while (resultset.next()) {
                    int control_techn_score = resultset.getInt("nut_tech_score");
                    nsurf_control_techn_score += control_techn_score;
                }

                query = String.format(q2, "'Nitrogen in Surface Water'", "'Trap'");
                resultset = statement.executeQuery(query);
                while (resultset.next()) {
                    int trap_techn_score = resultset.getInt("nut_tech_score");
                    nsurf_trap_techn_score += trap_techn_score;
                }

                //#Compute nutrient technique mitigation scores for Phosphorus in Surface Water concern and increment total scores
                query = String.format(q1, "'Phosphorus in Surface Water'");
                resultset = statement.executeQuery(query);
                while (resultset.next()) {
                    int techn_score = resultset.getInt("nut_tech_score");
                    psurf_techn_score += techn_score;
                }

                query = String.format(q2, "'Phosphorus in Surface Water'", "'Avoid'");
                resultset = statement.executeQuery(query);
                while (resultset.next()) {
                    int avoid_techn_score = resultset.getInt("nut_tech_score");
                    psurf_avoid_techn_score += avoid_techn_score;
                }

                query = String.format(q2, "'Phosphorus in Surface Water'", "'Control'");
                resultset = statement.executeQuery(query);
                while (resultset.next()) {
                    int control_techn_score = resultset.getInt("nut_tech_score");
                    psurf_control_techn_score += control_techn_score;
                }

                query = String.format(q2, "'Phosphorus in Surface Water'", "'Trap'");
                resultset = statement.executeQuery(query);
                while (resultset.next()) {
                    int trap_techn_score = resultset.getInt("nut_tech_score");
                    psurf_trap_techn_score += trap_techn_score;
                }
            }
            result1.add(new m.wqm.nuttechscores.V1_0.Result1(aoaId, nleach_techn_score,
                    nsurf_techn_score, psurf_techn_score, nleach_avoid_techn_score,
                    nleach_control_techn_score, nleach_trap_techn_score,
                    nsurf_avoid_techn_score, nsurf_control_techn_score,
                    nsurf_trap_techn_score, psurf_avoid_techn_score,
                    psurf_control_techn_score, psurf_trap_techn_score));

        } catch (ServiceException | SQLException ex) {
            LOG.log(Level.SEVERE, "SQL problem for WQM-14!", ex);
            throw new ServiceException("SQL problem", ex);
        }

    }

    @Override
    //writing the results back to JSON
    protected void postProcess() throws ServiceException {
        try {
            JSONArray result1Arr = new JSONArray();
            for (m.wqm.nuttechscores.V1_0.Result1 rs1 : result1) {
                JSONArray tmpArr = new JSONArray();
                tmpArr.put(JSONUtils.dataDesc("AoAId", rs1.AoAid, "Area of Analysis Identifier"));
                tmpArr.put(JSONUtils.dataDesc("nleach_techn_score", rs1.nleach_techn_score, "nutrient management technique mitigation score for nitrogen in ground water concern"));
                tmpArr.put(JSONUtils.dataDesc("nsurf_techn_score", rs1.nsurf_techn_score, "nutrient management technique mitigation score for nitrogen in surface water concern"));
                tmpArr.put(JSONUtils.dataDesc("psurf_techn_score", rs1.psurf_techn_score, "nutrient management technique mitigation score for phosphorus in surface water concern"));
                tmpArr.put(JSONUtils.dataDesc("nleach_avoid_techn_score", rs1.nleach_avoid_techn_score, "integer, nutrient management technique mitigation score for avoiding excess nitrogen use or application and loss to groundwater"));
                tmpArr.put(JSONUtils.dataDesc("nleach_control_techn_score", rs1.nleach_control_techn_score, "nutrient management technique mitigation score for controlling in-field nitrogen losses to groundwater"));
                tmpArr.put(JSONUtils.dataDesc("nleach_trap_techn_score", rs1.nleach_trap_techn_score, "nutrient management technique mitigation score for trapping excess nitrogen and keeping it from groundwater"));
                tmpArr.put(JSONUtils.dataDesc("nsurf_avoid_techn_score", rs1.nsurf_avoid_techn_score, "nutrient management technique mitigation score for avoiding excess nitrogen use or application and loss to surface water"));
                tmpArr.put(JSONUtils.dataDesc("nsurf_control_techn_score", rs1.nsurf_control_techn_score, "nutrient management technique mitigation score for controlling in-field nitrogen losses to surface water runoff"));
                tmpArr.put(JSONUtils.dataDesc("nsurf_trap_techn_score", rs1.nsurf_trap_techn_score, "nutrient management technique mitigation score for trapping excess nitrogen and keeping it from surface water"));
                tmpArr.put(JSONUtils.dataDesc("psurf_avoid_techn_score", rs1.psurf_avoid_techn_score, "nutrient management technique mitigation score for avoiding excess phosphorus use or application and loss to surface water"));
                tmpArr.put(JSONUtils.dataDesc("psurf_control_techn_score", rs1.psurf_control_techn_score, "nutrient management technique mitigation score for controlling in-field phosphorus losses to surface water runoff"));
                tmpArr.put(JSONUtils.dataDesc("psurf_trap_techn_score", rs1.psurf_trap_techn_score, "nutrient management technique mitigation score for trapping excess phophorus and keeping it from surface water"));
                result1Arr.put(JSONUtils.dataDesc("Nutrient Technique Scores", tmpArr, "AoANutScores"));
            }
            putResult("operation", result1Arr);
        } catch (JSONException ex) {
            LOG.log(Level.SEVERE, "Error in processing the response JSON for WQM-14!", ex);
            throw new ServiceException("Error in processing the response JSON.", ex);
        }
    }

    static class Input {

        int AoAid;
        int plan_techn_id;
        String plan_techn_discrim_type;
        String plan_techn_discrim;

        public Input(int AoAid, int plan_techn_id, String plan_techn_discrim_type,
                String plan_techn_discrim) {
            this.AoAid = AoAid;
            this.plan_techn_id = plan_techn_id;
            this.plan_techn_discrim_type = plan_techn_discrim_type;
            this.plan_techn_discrim = plan_techn_discrim;
        }

    }

    static class Result1 {

        int nleach_techn_score = 0;
        int nleach_avoid_techn_score = 0;
        int nleach_control_techn_score = 0;
        int nleach_trap_techn_score = 0;
        int nsurf_techn_score = 0;
        int nsurf_avoid_techn_score = 0;
        int nsurf_control_techn_score = 0;
        int nsurf_trap_techn_score = 0;
        int psurf_techn_score = 0;
        int psurf_avoid_techn_score = 0;
        int psurf_control_techn_score = 0;
        int psurf_trap_techn_score = 0;
        int AoAid;

        public Result1(int AoAid, int nleach_techn_score, int nsurf_techn_score,
                int psurf_techn_score, int nleach_avoid_techn_score,
                int nleach_control_techn_score, int nleach_trap_techn_score,
                int nsurf_avoid_techn_score, int nsurf_control_techn_score,
                int nsurf_trap_techn_score, int psurf_avoid_techn_score,
                int psurf_control_techn_score, int psurf_trap_techn_score) {
            this.nleach_techn_score = nleach_techn_score;
            this.nleach_avoid_techn_score = nleach_avoid_techn_score;
            this.nleach_control_techn_score = nleach_control_techn_score;
            this.nleach_trap_techn_score = nleach_trap_techn_score;
            this.nsurf_techn_score = nsurf_techn_score;
            this.nsurf_avoid_techn_score = nsurf_avoid_techn_score;
            this.nsurf_control_techn_score = nsurf_control_techn_score;
            this.nsurf_trap_techn_score = nsurf_trap_techn_score;
            this.psurf_techn_score = psurf_techn_score;
            this.psurf_avoid_techn_score = psurf_avoid_techn_score;
            this.psurf_control_techn_score = psurf_control_techn_score;
            this.psurf_trap_techn_score = psurf_trap_techn_score;
            this.AoAid = AoAid;
        }
    }

}