/*
 * $Id$
 *
 * This file is part of the Cloud Services Integration Platform (CSIP),
 * a Model-as-a-Service framework, API, and application suite.
 *
 * 2012-2017, OMSLab, Colorado State University.
 *
 * OMSLab licenses this file to you under the MIT license.
 * See the LICENSE file in the project root for more information.
 */
package d.wqm.wqm28_getfertlist;

import csip.ModelDataService;
import csip.api.server.ServiceException;
import csip.annotations.Polling;
import csip.annotations.Resource;
import java.sql.Connection;
import java.sql.SQLException;
import javax.ws.rs.Path;
import csip.annotations.Description;
import csip.annotations.Name;
import csip.utils.JSONUtils;
import java.sql.ResultSet;
import java.sql.Statement;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONException;
import wqm.utils.DBQueries;
import wqm.utils.DBResources;

/**
 * WQM-28: Fetch List of Fertilizers and Attributes
 *
 * @version 1.0
 * @author RUMPAL SIDHU
 */
@Name("WQM-28: Fetch List of Fertilizers and Attributes")
@Description("This service fetches a list of fertilizers and attributes "
        + "from tables in the CRdb.fertilzer schema for populating application choice lists.")
@Path("d/wqm/getfertlist/1.0")
@Polling(first = 10000, next = 2000)
@Resource(from = DBResources.class)

public class V1_0 extends ModelDataService {

    private String state;
    private double latitude;
    private double longitude;
    JSONArray result = new JSONArray();

    @Override
    protected void preProcess() throws ServiceException {
        state = parameter().getString("state");
        latitude = parameter().getDouble("latitude");
        longitude = parameter().getDouble("longitude");
    }

    @Override
    protected void doProcess() throws SQLException, ServiceException, JSONException {
        try (Connection connection = resources().getJDBC(DBResources.SSURGO_ID);
                Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery(DBQueries.WQM28Query(state))) {
            while (resultSet.next()) {
                JSONArray arr = new JSONArray();
                arr.put(JSONUtils.data("fertilizer_id", resultSet.getInt("id")));
                arr.put(JSONUtils.data("fertilizer_name", resultSet.getString("name")));
                arr.put(JSONUtils.data("fertilizer_display", resultSet.getString("display_name")));
                arr.put(JSONUtils.data("nitrogen_pct", resultSet.getDouble("nitrogen_pct")));
                arr.put(JSONUtils.data("phosphorus_pct", resultSet.getDouble("phosphorus_pct")));
                arr.put(JSONUtils.data("potassium_pct", resultSet.getDouble("potassium_pct")));
                arr.put(JSONUtils.data("default_placement", resultSet.getString("placement")));
                arr.put(JSONUtils.data("fertilizer_cost", resultSet.getDouble("cost")));
                arr.put(JSONUtils.data("fertilizer_cost_units", resultSet.getString("units")));
                result.put(arr);
            }
        }
    }

    @Override
    protected void postProcess() {
        results().put("Fertilizer List", result);
    }
}