package m.wqm.soilpestlosspot;

/**
 *
 * @author RUMPAL SIDHU
 */
import csip.ModelDataService;
import csip.annotations.Polling;
import csip.utils.JSONUtils;
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.Map;
import javax.ws.rs.Path;
import oms3.annotations.Name;
import oms3.annotations.Description;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONObject;

@Name("WQM-10")
@Description("Soil/Pesticide Interaction Loss Potentials")
@Path("m/soilpestlosspot/1.0")
@Polling(first = 10000, next = 2000)

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";

    //Request
    private ArrayList<Input> components;
    //Response
    private ArrayList<Result> result;

    @Override
    protected void preProcess() throws Exception {
        components = new ArrayList<>();
        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);
            int operation_id = JSONUtils.getIntParam(group, "operation_id", 0);
            String op_pesticide_id = JSONUtils.getStringParam(group, "op_pesticide_id", "err");
            String ai_plp = JSONUtils.getStringParam(group, "ai_plp", "err");
            String ai_psrp = JSONUtils.getStringParam(group, "ai_psrp", "err");
            String ai_parp = JSONUtils.getStringParam(group, "ai_parp", "err");
            String aoa_pslp = JSONUtils.getStringParam(group, "aoa_pslp", "err");
            String aoa_ssrp = JSONUtils.getStringParam(group, "aoa_ssrp", "err");
            String aoa_sarp = JSONUtils.getStringParam(group, "aoa_sarp", "err");
            String aoa_rain_prob = JSONUtils.getStringParam(group, "aoa_rain_prob", "err");
            Input input = new Input(AoAId, operation_id, op_pesticide_id, ai_plp, ai_psrp, ai_parp, aoa_pslp, aoa_ssrp, aoa_sarp, aoa_rain_prob);
            components.add(input);
        }
    }

    @Override
    protected String process() throws Exception {
        result = new ArrayList();
        Connection conn = null;
        Statement statement = null;
        String query;
        ResultSet resultSet;
        try {
            Class.forName(CLASS_NAME);
            conn = DriverManager.getConnection(CONNECTION, USER, PASS);
            conn.setAutoCommit(false);
            statement = conn.createStatement();

            for (Input ip : components) {

                query = "SELECT wqm_ilp FROM wqm_soil_pest_interaction_leaching WHERE wqm_plp='" + ip.ai_plp + "'AND wqm_slp='" + ip.aoa_pslp + "';";
                resultSet = statement.executeQuery(query);
                String op_pest_ilp = "err";
                while (resultSet.next()) {
                    op_pest_ilp = resultSet.getString("wqm_ilp");
                }
                if (ip.aoa_rain_prob.equals("LOW")) {
                    switch (op_pest_ilp) {
                        case "HIGH":
                            op_pest_ilp = "INTERMEDIATE";
                            break;
                        case "INTERMEDIATE":
                            op_pest_ilp = "LOW";
                            break;
                        case "LOW":
                            op_pest_ilp = "VERY LOW";
                            break;
                    }
                }

                query = "SELECT wqm_isrp FROM wqm_soil_pest_interaction_solution_runoff WHERE wqm_psrp='" + ip.ai_psrp + "'AND wqm_ssrp ='" + ip.aoa_ssrp + "';";
                resultSet = statement.executeQuery(query);
                String op_pest_isrp = "err";
                while (resultSet.next()) {
                    op_pest_isrp = resultSet.getString("wqm_isrp");
                }
                if (ip.aoa_rain_prob.equals("LOW")) {
                    switch (op_pest_isrp) {
                        case "HIGH":
                            op_pest_isrp = "INTERMEDIATE";
                            break;
                        case "INTERMEDIATE":
                            op_pest_isrp = "LOW";
                            break;
                        case "LOW":
                            op_pest_isrp = "VERY LOW";
                            break;
                    }
                }

                query = "SELECT wqm_iarp FROM wqm_soil_pest_interaction_adsorbed_runoff WHERE wqm_parp='" + ip.ai_parp + "'AND wqm_sarp='" + ip.aoa_sarp + "';";
                resultSet = statement.executeQuery(query);
                String op_pest_iarp = "err";
                while (resultSet.next()) {
                    op_pest_iarp = resultSet.getString("wqm_iarp");
                }
                if (ip.aoa_rain_prob.equals("LOW")) {
                    switch (op_pest_iarp) {
                        case "HIGH":
                            op_pest_iarp = "INTERMEDIATE";
                            break;
                        case "INTERMEDIATE":
                            op_pest_iarp = "LOW";
                            break;
                        case "LOW":
                            op_pest_iarp = "VERY LOW";
                            break;
                    }
                }

                Result result1 = new Result(ip.AoAId, ip.operation_id, ip.op_pesticide_id, op_pest_ilp, op_pest_isrp, op_pest_iarp);
                result.add(result1);
            }
        } catch (SQLException se) {
            LOG.info("Did not open database for WQM-10!");
            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 resultArr = new JSONArray();
        for (Result rs1 : result) {
            JSONArray tmpArr = new JSONArray();
            tmpArr.put(JSONUtils.dataDesc("AoAId", rs1.AoAId, "Area of Analysis Identifier"));
            tmpArr.put(JSONUtils.dataDesc("operation_id", rs1.operation_id, "Pesticide Application Operation Identifier"));
            tmpArr.put(JSONUtils.dataDesc("op_pesticide_id", rs1.op_pesticide_id, "Pesticide identifier, EPA Pesticide Chemical Code (PC_CODE)"));
            tmpArr.put(JSONUtils.dataDesc("op_pest_ilp", rs1.op_pest_ilp, "Operation Soil Pesticide Interaction Leaching Potential"));
            tmpArr.put(JSONUtils.dataDesc("op_pest_isrp", rs1.op_pest_isrp, "Operation Soil Pesticide Interaction Solution Runoff Potential"));
            tmpArr.put(JSONUtils.dataDesc("op_pest_iarp", rs1.op_pest_iarp, "Operation Soil Pesticide Interaction Adsorbed Runoff Potential"));
            resultArr.put(JSONUtils.dataDesc("pesticide summary", tmpArr, "Pest"));
        }
        putResult("operation", resultArr);
    }
}