/*
 * 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.pesticide.attributes;

import csip.ServiceException;
import csip.annotations.Polling;
import csip.annotations.Resource;
import csip.utils.JSONUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;
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;
import wqm.utils.DBResources;
import static wqm.utils.DBResources.WQM_ID;


/**
 *
 * @author od
 * @author Shaun Case
 */
@Name("WQM-03:  Pesticide Product List (PestProdList)")
@Description("This service returns a list of pesticide products and their active ingredients from the wqm_pesticide_products table of the WQM Data Mart.  The list is filtered by criteria provided in the request payload.")
@Path("m/wqm/pestprodlist/1.0")
@Resource(from = DBResources.class)
@Polling(first = 1000, next = 2000)

public class V1_0 extends csip.ModelDataService {

    private ArrayList<SearchFilter> filters;
 
    private ArrayList<SearchResult> searchResults;

    @Override
    protected void preProcess() throws csip.ServiceException {
	JSONArray filterArray;
	filters = new ArrayList<>();
	searchResults = new ArrayList<>();

	filterArray = getJSONArrayParam("filters");

	try {
	    for (int i = 0; i < filterArray.length(); i++) {
		SearchFilter tFilter = new SearchFilter(filterArray.getJSONArray(i));
		
		    filters.add(tFilter);
		
	    }
	} catch (JSONException ex) {
	    throw new csip.ServiceException(ex.getMessage());
	}
    }

    @Override
    protected void doProcess() throws csip.ServiceException {
	String query = "SELECT  reg_no, prod_name, type_desc, pc_code, ai_name, pc_pct from wqm_pesticide_products WHERE ";
	int counter = 0;
        
	    for (SearchFilter aFilter : filters) {
		
		    if (counter > 0) {
			query += " AND ";
		    }
		    query += aFilter.getWhereClause();
		    counter++;
	    }
		
		try (Connection conn = getResourceJDBC(WQM_ID);
			Statement statement = conn.createStatement()) {

		   try( ResultSet results = statement.executeQuery(query)) {
		    while (results.next()) {
			Map<String, String> resultMap = new HashMap<>();

			resultMap.put("reg_no", results.getString("reg_no"));
			resultMap.put("prod_name", results.getString("prod_name"));
			resultMap.put("type_desc", results.getString("type_desc"));
			resultMap.put("pc_code", results.getString("pc_code"));
			resultMap.put("ai_name", results.getString("ai_name"));
			resultMap.put("pc_pct", results.getString("pc_pct"));

			searchResults.add(new SearchResult(resultMap));
		    }
                   }
		} catch (ServiceException | SQLException ex) {
		    throw new csip.ServiceException("SQLException",ex);
		}
	    
	
    }

    @Override
    protected void postProcess() throws JSONException {
	JSONArray resultArr = new JSONArray();

	for (SearchResult result : searchResults) {
	    resultArr.put(result.getResultArray());
	}

	putResult("pesticides", resultArr, "List of query results");

    }

    protected static class SearchResult {

	private final Map<String, SearchResult.valueDescription> results = new HashMap<String, SearchResult.valueDescription>() {
	    {
		put("reg_no", new SearchResult.valueDescription("", "EPA product registration number (EPA Reg. No.)"));
		put("prod_name", new SearchResult.valueDescription("", "Pesticide product name"));
		put("type_desc", new SearchResult.valueDescription("", "Pesticide product type)"));
		put("pc_code", new SearchResult.valueDescription("", "Pesticide chemical code"));
		put("ai_name", new SearchResult.valueDescription("", "Active ingredient name"));
		put("pc_pct", new SearchResult.valueDescription("", "Active ingredient percentage"));
	    }
	};

	SearchResult(Map<String, String> dbResults) {
	    SearchResult.valueDescription resultValue;

	    for (Map.Entry<String, String> result : dbResults.entrySet()) {
		resultValue = results.get(result.getKey());
		if (null != resultValue) {
		    resultValue.value = result.getValue();
		}
	    }
	}

	public JSONArray getResultArray() throws JSONException {
	    JSONArray ret_val = new JSONArray();

	    for (Map.Entry<String, SearchResult.valueDescription> result : results.entrySet()) {
		ret_val.put(JSONUtils.dataDesc(result.getKey(), result.getValue().value, result.getValue().description));
	    }

	    return ret_val;
	}

	protected static class valueDescription {

	    String value;
	    String description;

	    valueDescription(String value, String description) {
		this.value = value;
		this.description = description;
	    }
	}

    }

    protected static class SearchFilter {

	private SearchFilter.columnName filterName;
	private String searchValue;
	private boolean beginsWith;
	private boolean contains;
	private boolean endsWith;
	
	SearchFilter(JSONArray filterArray) throws ServiceException {
	    this.filterName = new SearchFilter.columnName();
	    this.searchValue = "";
	    this.beginsWith = false;
	    this.contains = false;
	    this.endsWith = false;
	  
	    if (null != filterArray) {
		try {
		    Map<String, JSONObject> filterMap = JSONUtils.preprocess(filterArray);
		    if (filterMap.containsKey("pp_filter_name")) {
			filterName.setName(filterMap.get("pp_filter_name").getString("value"));
			if (filterMap.containsKey("pp_filter_value")) {
			    searchValue = filterMap.get("pp_filter_value").getString("value");
			    if (filterMap.containsKey("begins_with")) {
				beginsWith = filterMap.get("begins_with").getBoolean("value");
				if (filterMap.containsKey("contains")) {
				    contains = filterMap.get("contains").getBoolean("value");
				    if (filterMap.containsKey("ends_with")) {
					endsWith = filterMap.get("ends_with").getBoolean("value");
				    } else {
					throw new ServiceException("Missing 'ends_with' parameter for this filter request.  Cannot proceed.");
				    }
				} else {
				    throw new ServiceException("Missing 'contains' parameter for this filter request.  Cannot proceed.");
				}
			    } else {
				throw new ServiceException("Missing 'begins_with' parameter for this filter request.  Cannot proceed.");
			    }
			} else {
			    throw new ServiceException("Missing 'pp_filter_value' parameter for this filter request.  Cannot proceed.");
			}
		    } else {
			throw new ServiceException("Missing 'pp_filter_name' parameter for this filter request.  Cannot proceed.");
		    }

		} catch (JSONException ex) {
		    throw new ServiceException("JSONException",ex);
		}
	    } else {
		throw new ServiceException("No input values for the filter. Cannot proceed.");
	    }

	    if (!filterName.isValid()) {
		throw new ServiceException("Filter name specified is not a valid column name.  Cannot proceed.");
	    }
	}

	String getWhereClause() {
	    String ret_val = "( ";
	    int counter = 0;

	    if (filterName.isValid() && (beginsWith || contains || endsWith)) {
		ret_val += filterName.getName() + " LIKE ";

		if (beginsWith) {
		    ret_val += " '" + searchValue + "%' ";
		    counter++;
		}

		if (contains) {
		    if (counter > 0) {
			ret_val += " OR " + filterName.getName() + " LIKE ";
		    }
		    ret_val += " '%" + searchValue + "%' ";
		    counter++;
		}

		if (endsWith) {
		    if (counter > 0) {
			ret_val += " OR " + filterName.getName() + " LIKE ";
		    }
		    ret_val += " '%" + searchValue + "' ";
		    counter++;
		}

		ret_val += " ) ";

	    } else {
		ret_val = "";
	    }

	    return ret_val;
	}

	

	//  Since the column names used in the filter are constrained, this class will help
	// ensure that the contraints are adhered to.
	protected static class columnName {

	    private final String names[] = {"reg_no", "prod_name", "type_desc", "pc_code", "ai_name"};
	    private int nameIdx;

	    columnName() {
		nameIdx = -1;
	    }

	    public boolean setName(String cName) {
		nameIdx = -1;
		for (int i = 0; i < 5; i++) {
		    if (names[i].equals(cName)) {
			nameIdx = i;
			break;
		    }
		}
		return isValid();
	    }

	    public String getName() {
		String ret_val = "";
		if ((nameIdx >= 0) && (nameIdx <= 5)) {
		    ret_val = names[nameIdx];
		}
		return ret_val;
	    }

	    public boolean isValid() {
		return ((nameIdx >= 0) && (nameIdx <= 5));
	    }
	}
    }

}