/*
 * $Id:$
 *
 * This file is part of the Cloud Services Integration Platform (CSIP),
 * a Model-as-a-Service framework, API, and application suite.
 *
 * 2012-2020, 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.eofdb.getContractDetails;

import csip.ModelDataService;
import csip.annotations.*;
import csip.api.server.ServiceException;
import eofdb.ServiceResources;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.ws.rs.Path;

/**
 * @author Lucas Yaege
 */
@Name("Edge of Field DB - Get Contract Details")
@Description("Takes a contract ID number and returns relevant data and geometries for use in the "
        + "Edge Of Field Document Management Database Tool")
@Path("d/eofdb/getContractDetails/1.0")
@Resource(from = ServiceResources.class)
public class V1_0 extends ModelDataService {

  @Override
  protected void doProcess() throws Exception {
    String contractNumber = parameter().getString("contractNumber");

    try (Connection conNRT = resources().getJDBC(ServiceResources.NRT_ID);
            Statement stmtNRT = conNRT.createStatement()) {
      String query = "SELECT \n"
              + "    NPAD.dbo.contract.contract_nbr, \n"
              + "    NPAD.dbo.contract.state_code, \n"
              + "    NRT.dbo.d_state_county.state_name, \n"
              + "    NPAD.dbo.contract.county_code, \n"
              + "    NRT.dbo.d_state_county.county_name, \n"
              + "    NPAD.dbo.contract.huc12, \n"
              + "    NRT.dbo.d_huc.subwatershed_name, \n"
              + "    NPAD.dbo.land_unit.program_acres, \n"
              + "    NPAD.dbo.land_unit.fsa_farm_number, \n"
              + "    NPAD.dbo.land_unit.fsa_tract_number, \n"
              + "    land_unit_shape.land_unit_geometry.STAsText() AS land_unit_geometry, \n"
              + "    land_unit_shape.land_unit_centroid, \n"
              + "    NPAD.dbo.practice_schedule.practice_id, \n"
              + "    NPAD.dbo.practice_schedule.practice_scheduled_date, \n"
              + "    NPAD.dbo.practice_schedule.practice_certified_date, \n"
              + "    NRT.dbo.d_practice.practice_code, \n"
              + "    NRT.dbo.d_practice.practice_name, \n"
              + "    NPAD.dbo.practice_shape.practice_shape_geometry \n"
              + "FROM \n"
              + "	NPAD.dbo.contract \n"
              + "		LEFT JOIN \n"
              + "			NRT.dbo.d_huc \n"
              + "			ON \n"
              + "				NPAD.dbo.contract.huc12 = NRT.dbo.d_huc.huc_12 \n"
              + "		LEFT JOIN \n"
              + "			NRT.dbo.d_state_county \n"
              + "			ON \n"
              + "				NPAD.dbo.contract.county_code = NRT.dbo.d_state_county.state_county_code \n"
              + "		LEFT JOIN \n"
              + "			NPAD.dbo.contract_item \n"
              + "			ON \n"
              + "				NPAD.dbo.contract.contract_id = NPAD.dbo.contract_item.contract_id \n"
              + "		LEFT JOIN \n"
              + "			eContracts.dbo.agreement_item \n"
              + "			ON \n"
              + "				NPAD.DBO.contract_item.contract_item_id = eContracts.dbo.agreement_item.contract_item_id \n"
              + "		LEFT JOIN \n"
              + "			eContracts.dbo.practice_agreement_item \n"
              + "			ON \n"
              + "				eContracts.dbo.agreement_item.agreement_item_id = eContracts.dbo.practice_agreement_item.agreement_item_id \n"
              + "		LEFT JOIN \n"
              + "			NPAD.dbo.practice_schedule \n"
              + "			ON \n"
              + "	             eContracts.dbo.practice_agreement_item.scheduled_practice_id = NPAD.dbo.practice_schedule.scheduled_practice_id \n"
              + "		LEFT JOIN \n"
              + "			NRT.dbo.d_practice \n"
              + "			ON \n"
              + "				NPAD.dbo.practice_schedule.practice_id = NRT.dbo.d_practice.practice_id \n"
              + "		LEFT JOIN \n"
              + "			NPAD.dbo.practice_shape \n"
              + "			ON \n"
              + "				NPAD.dbo.practice_schedule.scheduled_practice_id = NPAD.dbo.practice_shape.scheduled_practice_id \n"
              + "		LEFT JOIN \n"
              + "			NPAD.dbo.practice_land_unit \n"
              + "			ON \n"
              + "				NPAD.dbo.practice_schedule.scheduled_practice_id = NPAD.dbo.practice_land_unit.scheduled_practice_id \n"
              + "		LEFT JOIN \n"
              + "			NPAD.dbo.land_unit \n"
              + "			ON \n"
              + "				NPAD.dbo.practice_land_unit.land_unit_id = NPAD.dbo.land_unit.land_unit_id \n"
              + "		LEFT JOIN \n"
              + "			NPAD.dbo.land_unit_shape AS land_unit_shape \n"
              + "			ON \n"
              + "				NPAD.dbo.land_unit.land_unit_id = land_unit_shape.land_unit_id \n"
              + "WHERE practice_code = '201' AND \n"
              + "contract_nbr = '" + contractNumber + "'";

      stmtNRT.execute(query);
      ResultSet rs = stmtNRT.getResultSet();

      if (rs.next()) {
        results().put("landUnitGeometry", rs.getString("land_unit_geometry"));
        results().put("contractNumber", rs.getString("contract_nbr"));
        results().put("stateCode", rs.getInt("state_code"));
        results().put("stateName", rs.getString("state_name"));
        results().put("countyCode", rs.getInt("county_code"));
        results().put("countyName", rs.getString("county_name"));
        results().put("huc12", rs.getString("huc12"));
        results().put("subwatershedName", rs.getString("subwatershed_name"));
        results().put("programAcres", rs.getDouble("program_acres"));
        results().put("fsaFarmNumber", rs.getInt("fsa_farm_number"));
        results().put("fsaTractNumber", rs.getInt("fsa_tract_number"));
        results().put("landUnitCentroid", rs.getString("land_unit_centroid"));
        results().put("practiceId", rs.getInt("practice_id"));
        results().put("practiceScheduledDate", rs.getString("practice_scheduled_date"));
        results().put("practiceCertifiedDate", rs.getString("practice_certified_date"));
        results().put("practice_code", rs.getString("practice_code"));
        results().put("practice_name", rs.getString("practice_name"));
        results().put("practice_shape_geometry", rs.getString("practice_shape_geometry"));
      } else {
        throw new ServiceException("No contract details were found for that contract ID.");
      }
    }
  }
}