package m.wqm.nuttechscores;

/**
 *
 * @author SrinivasReddy kontham
 * @ update Rumpal Sidhu
 */
import csip.ModelDataService;
import static csip.ModelDataService.EXEC_OK;
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.util.Map;
import java.sql.*;
import java.util.concurrent.*;

@Name("WQM-14:(NutTechScores)")
@Description("Nutrient Technique Scores")
@Path("m/nut_tech_scores/1.0")

public class V1_0 extends ModelDataService {

    //SQL params names here for quick modification
    private final String USER = "postgres";
    private final String PASS = "admin";
    private final String HOST = "localhost";
    private final String PORT = "5432";
    private final String DBNAME = "postgres";
    private final String JDBC_TYPE = "jdbc:postgresql://";
    private final String CONNECTION = JDBC_TYPE + HOST + ":" + PORT + "/" + DBNAME;
    private final String CLASS_NAME = "org.postgresql.Driver";

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

    @Override
    // reading the inputs from the json file into input object and placing it in the arraylist
    protected void preProcess() throws Exception {
        components = new ArrayList<>();
        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", "err");
            String plan_techn_discrim = JSONUtils.getStringParam(group, "plan_techn_discrim", "err");
            Input input = new Input(aoaId, plan_techn_id, plan_techn_discrim_type, plan_techn_discrim);
            components.add(input);
        }
    }

    @Override
    protected String process() throws Exception {
        result1 = new ArrayList<>();
        Connection conn = null;
        Statement statement = null;
        try {
            Class.forName(CLASS_NAME);
            conn = DriverManager.getConnection(CONNECTION, USER, PASS);
            conn.setAutoCommit(false);
            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 (Input ip : components) {
                String query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores "
                        + "WHERE nutrient_technique_id = " + ip.plan_techn_id
                        + " AND wqm_concern ='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 = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores "
                        + "WHERE nutrient_technique_id= " + ip.plan_techn_id
                        + " AND wqm_concern = 'Nitrogen in Ground Water' AND mode_of_action = '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 = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores "
                        + "WHERE nutrient_technique_id = " + ip.plan_techn_id
                        + " AND wqm_concern = 'Nitrogen in Ground Water' AND mode_of_action = '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 = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores "
                        + "WHERE nutrient_technique_id = " + ip.plan_techn_id
                        + " AND wqm_concern = 'Nitrogen in Ground Water' AND mode_of_action = '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 = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores "
                        + "WHERE nutrient_technique_id = " + ip.plan_techn_id
                        + " AND wqm_concern = '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 = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores "
                        + "WHERE nutrient_technique_id = " + ip.plan_techn_id
                        + " AND wqm_concern = 'Nitrogen in Surface Water' AND mode_of_action = '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 = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores "
                        + "WHERE nutrient_technique_id = " + ip.plan_techn_id
                        + " AND wqm_concern = 'Nitrogen in Surface Water' AND mode_of_action = '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 = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores "
                        + "WHERE nutrient_technique_id = " + ip.plan_techn_id
                        + " AND wqm_concern = 'Nitrogen in Surface Water' AND mode_of_action = '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 = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores "
                        + "WHERE nutrient_technique_id = " + ip.plan_techn_id
                        + " AND wqm_concern = 'Phosphorous in Surface Water'"
                        + " AND tech_discrim_type ='" + ip.plan_techn_discrim_type
                        + "' AND tech_discrim = '" + ip.plan_techn_discrim + "';";
                resultset = statement.executeQuery(query);
                while (resultset.next()) {
                    int techn_score = resultset.getInt("nut_tech_score");
                    psurf_techn_score += techn_score;
                }

                query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores "
                        + "WHERE nutrient_technique_id = " + ip.plan_techn_id
                        + " AND wqm_concern = 'Phosphorus in Surface Water' AND mode_of_action = '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 = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores "
                        + "WHERE nutrient_technique_id = " + ip.plan_techn_id
                        + "AND wqm_concern = 'Phosphorus in Surface Water' AND mode_of_action = '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 = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores "
                        + "WHERE nutrient_technique_id = " + ip.plan_techn_id
                        + " AND wqm_concern = 'Phosphorus in Surface Water' AND mode_of_action = '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 result = new 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);
            result1.add(result);

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

    @Override
    //writing the results back to JSON
    protected void postProcess() throws Exception {
        JSONArray result1Arr = new JSONArray();
        for (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);
    }

}