package m.wqm.ipmscoresfull;

/**
 * @author anvesh, od, Shaun Case
 */
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 csip.utils.JSONUtils;
import java.sql.*;
import java.util.logging.Level;
import static wqm.utils.DBResources.*;
import wqm.utils.DBResources;

@Name("WQM-24: IPM Scores")
@Description("This service returns all rows and columns from the wqm_ipm_scores table.")
@Path("m/ipmscoresfull/1.0.1")
@Resource(from = DBResources.class)
public class V1_0_1 extends ModelDataService {

    int ipm_score_id;
    int ipm_mitigation_score;
    String resource_concern;
    String ipm_level;
    String wqm_concern;
    String ipm_level_name;

    ArrayList<Result1> result1 = new ArrayList<>();

    @Override
    protected void doProcess() throws Exception {
        try (Connection conn = getResourceJDBC(WQM_READONLY_ID);
                Statement statement = conn.createStatement()) {
            // We need to return the entire table, so we use the not standard SELECT * 
            // TODO why not select only what's needed?
            String query = "SELECT ipm_score_id, ipm_mitigation_score, resource_concern, ipm_level, wqm_concern, ipm_level_name  FROM ";
            if (conn.getMetaData().getDatabaseProductName().contains("Microsoft")) {
                query += " wqm.wqm_ipm_scores";
            } else {
                query += " wqm_ipm_scores";
            }

            try (ResultSet results = statement.executeQuery(query)) {
                while (results.next()) {
                    ipm_score_id = results.getInt("ipm_score_id");
                    ipm_mitigation_score = results.getInt("ipm_mitigation_score");
                    resource_concern = results.getString("resource_concern");
                    ipm_level = results.getString("ipm_level");
                    wqm_concern = results.getString("wqm_concern");
                    ipm_level_name = results.getString("ipm_level_name");

                    result1.add(new Result1(ipm_score_id, ipm_mitigation_score, resource_concern,
                            ipm_level, wqm_concern, ipm_level_name));
                }
            }
        } catch (SQLException se) {
            LOG.log(Level.SEVERE, "Did not open database for WQM-24!", se);
            throw new ServiceException("SQL problem", se);
        }
    }

    @Override
    protected void postProcess() throws Exception {
        JSONArray result1Arr = new JSONArray();
        for (Result1 rs1 : result1) {
            JSONArray tmpArr = new JSONArray();
            tmpArr.put(JSONUtils.dataDesc("ipm_score_id", rs1.ipm_score_id, "ipm_score_id"));
            tmpArr.put(JSONUtils.dataDesc("ipm_mitigation_score", rs1.ipm_mitigation_score, "ipm_mitigation_score"));
            tmpArr.put(JSONUtils.dataDesc("resource_concern", rs1.resource_concern, "NRCS resource concern"));
            tmpArr.put(JSONUtils.dataDesc("ipm_level", rs1.ipm_level, "Treatment level of integrated pest management:  I (Basic), II (Intermediate), or III (Advanced)"));
            tmpArr.put(JSONUtils.dataDesc("wqm_concern", rs1.wqm_concern, "WQM resource concern:  pesticide leaching, pesticide solution runoff, pesticide adsorbed runoff, pesticide drift"));
            tmpArr.put(JSONUtils.dataDesc("ipm_level_name", rs1.ipm_level_name, "Treatment level of integrated pest management:  Basic, Intermediate, Advanced"));
            result1Arr.put(JSONUtils.dataDesc("IPM  Scores", tmpArr, "IPMScores"));
        }
        putResult("operation", result1Arr);
    }

    static class Result1 {

        int ipm_score_id;
        int ipm_mitigation_score;
        String resource_concern;
        String ipm_level;
        String wqm_concern;
        String ipm_level_name;

        public Result1(int ipm_score_id,
                int ipm_mitigation_score,
                String resource_concern,
                String ipm_level,
                String wqm_concern,
                String ipm_level_name) {
            this.ipm_score_id = ipm_score_id;
            this.ipm_mitigation_score = ipm_mitigation_score;
            this.resource_concern = resource_concern;
            this.ipm_level = ipm_level;
            this.wqm_concern = wqm_concern;
            this.ipm_level_name = ipm_level_name;
        }
    }

}