/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package m.wqm.wqsr;

import csip.ModelDataService;
import csip.ServiceException;
import csip.utils.JSONUtils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Map;
import javax.ws.rs.Path;
import oms3.annotations.Description;
import oms3.annotations.Name;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;

/**
 *
 * @author Sandeep
 */

@Name("WQM-01: Water Quality Sensitivity Rating (WQSR)")
@Description("This service intersects area of analysis (AoA) geometry with the NRCS Water Quality Sensitivity Rating (WQSR) spatial layer and computes a sensitivity rating and treatment level required for mitigating nutrient and pesticide loss potentials and hazards.")
@Path("m/wqm/wqsr/1.0")

public class V1_0 extends ModelDataService {
  
/*
    private final String USER = "postgres";
    private final String PASS = "admin";
    private final String HOST = "csip.engr.colostate.edu";
    private  final String PORT = "5435";
    private  final String DBNAME = "ssurgo";
    private final String SERVER = "localhost:5432/postgres";
    private final String JDBC_TYPE = "jdbc:postgresql://";
    private final String CLASS_NAME = "org.postgresql.Driver";
    private final String CONNECTION = JDBC_TYPE + HOST + ":" + PORT + "/" + DBNAME;
*/
    private Connection tempCon;
    private Statement stmt;

    private String aoaId;
    private JSONArray polygon,coordinates;
    String points,finalpoints="";
    JSONArray aoaArr;
    ArrayList<AoA> list;
    Double aoaBaseArea=0.0,aoaSensitiveArea=0.0,aoaCriticalArea=0.0;
    
    @Override
    public void preProcess() throws ServiceException, JSONException
    {
        tempCon = null;
        stmt = null;
        list=new ArrayList<AoA>();
         
        aoaArr = getJSONArrayParam("aoas");
        for(int i = 0; i < aoaArr.length(); i++) {
            //Map individual JSONObject & extract values
        Map<String, JSONObject> thisAoA = JSONUtils.preprocess(aoaArr.getJSONArray(i));
        aoaId=JSONUtils.getStringParam(thisAoA,"aoa_id","unknown");
        polygon=JSONUtils.getJSONArrayParam(thisAoA,"aoa_geometry");
       coordinates=polygon.getJSONArray(0);
       points=coordinates.toString();
 
       // Getting the array of coordinates and converting to a form that can be used in the query for intersection
       points=points.replace("[","");
       points=points.replace("]","");
       points=points.replace(","," ");
       String temp[] = points.split(" ");
       finalpoints="";
       finalpoints+=temp[0];
       for(int j=1;j<temp.length;j++)
       {
           if(j%2==0)
           finalpoints+=","+temp[j];
           else
         finalpoints+=" "+temp[j];
       }
       list.add(new AoA(aoaId,finalpoints));
        }

    }
    @Override
    public String process()
    {
        try {
          //Class.forName(CLASS_NAME);
          //tempCon=DriverManager.getConnection(CONNECTION,"postgres","admin");
          tempCon = wqm.utils.WQMTools.getConnection("ssurgo", LOG );
          tempCon.setAutoCommit(false);
          stmt=tempCon.createStatement();
         
        for(int i=0;i<list.size();i++)
      {       
          aoaBaseArea=0.0;
          aoaSensitiveArea=0.0;
          aoaCriticalArea=0.0;
        //Query to select the area,rating and treatment level which given coordinates intersects wqsr_area geometry
//          ResultSet results=stmt.executeQuery("select shape_area,wqs_rating,wqs_treatment_level from wqm_huc12.wqsr_area WHERE ST_Intersects(ST_PolygonFromText('POLYGON(("+list.get(i).getCoordinates()+"))'),geom)");
            ResultSet results=stmt.executeQuery("select shape_area,wqs_rating,wqs_treatment_level from wqm_huc12.wqsr_area WHERE ST_Intersects('POLYGON(("+list.get(i).getCoordinates()+"))'::geography::geometry, geom)");
          while(results.next())
      { 
        String wqsRating=results.getString("wqs_rating");
        if(wqsRating.equalsIgnoreCase("base"))
            aoaBaseArea+=Double.parseDouble(results.getString("shape_area"));
        else if(wqsRating.equalsIgnoreCase("sensitive"))
            aoaSensitiveArea+=Double.parseDouble(results.getString("shape_area"));
        else if(wqsRating.equalsIgnoreCase("critical"))
            aoaCriticalArea+=Double.parseDouble(results.getString("shape_area"));
      }
          
          if((aoaBaseArea>=aoaSensitiveArea)&&(aoaBaseArea>=aoaCriticalArea))
          {
          list.get(i).setWQSRating("Base");
          list.get(i).setTreatmentLevel("I");
          }
          else  if((aoaSensitiveArea>=aoaBaseArea)&&(aoaSensitiveArea>=aoaCriticalArea))
          {
          list.get(i).setWQSRating("Sensitive");
          list.get(i).setTreatmentLevel("II");
          }
          else
          {
          list.get(i).setWQSRating("Critical");
          list.get(i).setTreatmentLevel("III"); 	  
          }
      }   
        }
        catch(Exception e)
        {
            LOG.info(e.toString());
        }   
        return EXEC_OK;
    }
    
    @Override
    public void postProcess() throws JSONException
    {
        try{
            if ( this.stmt != null ){
                this.stmt.close();
                this.stmt = null;
            }
            if ( this.tempCon != null ){
                this.tempCon.close();
                this.tempCon = null;
            }
        }
        catch (Exception ex ){
            LOG.warning(" Could not close database connection: " + ex.getMessage() );
        }
            
        JSONArray finalArr= new JSONArray();
        for(int i=0;i<list.size();i++){
            JSONArray resultArr = new JSONArray();
            resultArr.put(JSONUtils.dataDesc("AoAId",list.get(i).getAoAId(),"Area of Analysis Identifier"));
            resultArr.put(JSONUtils.dataDesc("wqs_Rating",list.get(i).getWQSRating(),"WQS Rating"));
            resultArr.put(JSONUtils.dataDesc("wqs_treatment_level",list.get(i).getTreatmentLevel(),"WQS Treatment Level"));

            finalArr.put(resultArr);
        }
        
        putResult("",finalArr);
    }
}