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

import csip.api.server.PayloadResults;
import csip.api.server.ServiceException;
import csip.utils.JSONUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONException;
import static wqm.utils.DBResources.WQM_ID;
import static wqm.utils.Pesticides.AI_LIST;
import static wqm.utils.Pesticides.PRODUCTS_LIST;

/**
 *
 * @author <a href="mailto:shaun.case@colostate.edu">Shaun Case</a>
 */
class Products {

  ProductsData productsData = new ProductsData();

  LinkedHashMap<String, Product> productsFound = new LinkedHashMap<>();
  String query = " wqm.d_pesticides_products.reg_no as " + ProductData.REG_NO + ", wqm.d_pesticides_products.Id as " + ProductNameData.PRODUCT_ID + ", "
      + "            wqm.d_pesticides_products.epa_code as " + ProductData.EPA_NO + ", "
      + "            wqm.d_pesticides_products.prod_name as " + ProductNameData.PRODUCT_NAME + ", wqm.d_pesticides_ais.Id as " + ActiveIngredientsData.AI_ID + ", "
      + "            wqm.d_pesticides_products.app_area as " + ProductData.APP_AREA + ", wqm.d_pesticides_products.app_meth as " + ProductData.APP_METHOD + ", "
      + "            wqm.d_pesticides_ais.ai_name as " + ActiveIngredientsData.AI_NAME + ", wqm.d_pesticides_formula.PC_PCT as " + ActiveIngredientsData.AI_PCT + ", "
      + "            wqm.d_pesticides_ais.app_rate as " + ActiveIngredientsData.DEFAULT_APP_RATE_ID + "  "
      + " FROM wqm.d_pesticides_products "
      + "		INNER JOIN wqm.d_pesticides_formula ON wqm.d_pesticides_products.REG_NO=wqm.d_pesticides_formula.REG_NO "
      + "		INNER JOIN wqm.d_pesticides_ais ON wqm.d_pesticides_formula.PC_CODE=wqm.d_pesticides_ais.pc_code "
      + "     WHERE ";

  Products(PestProdListRequest request, Connection conn) throws SQLException, ServiceException {
    if ((null != request) && (request.numFilters() > 0)) {
      int counter = 0;
      String newQuery = "SELECT  " + query;
      ArrayList<String> whereList = new ArrayList<>();

      for (SearchFilter aFilter : request.searchFilters()) {

        if (counter > 0) {
          newQuery += " " + request.filterOperator().trim() + " ";
        } else {
          newQuery += " ( ";
        }

        String tWhere = aFilter.getWhereClause();
        if (!aFilter.isIntegerType()) {
          whereList.add(aFilter.getWhereCondition());
        }

        newQuery += tWhere;
        counter++;
      }

      if (request.searchFilters().size() > 0) {
        newQuery += " ) ";
      }

      newQuery += " AND wqm.d_pesticides_ais.HAS_DATA=1 "
          + " ORDER BY wqm.d_pesticides_products.reg_no, product_name;";

      try (PreparedStatement statement = conn.prepareStatement(newQuery)) {

        for (int i = 0; i < whereList.size(); i++) {
          statement.setString(i + 1, whereList.get(i));
        }

        ResultSet results = statement.executeQuery();

        counter = 0;

        while (results.next()) {
          String reg_no = results.getString(ProductData.REG_NO);

          if (productsFound.containsKey(reg_no)) {
            productsFound.get(reg_no).addProduct(results);
          } else {
            Product product = new Product(results);
            productsFound.put(reg_no, product);
            counter++;
          }

        }
        //  Removed:  Just report a finding result count of "0", since this isn't necessarily an error condition.
        //else {  
        //  throw new ServiceException("Database query returned no results for this search criteria.");
        // }
        productsData.ResultCount(counter);
      }
    }
  }

  public JSONArray toJSON(int max_products, PayloadResults results) throws JSONException {
    JSONArray ret_val = new JSONArray();
    int counter = 0;
    productsData.toJSON(ret_val);

    results.put("return_count", (productsFound.size() > max_products) ? max_products : productsFound.size(), "Actual count of products returned for this search.  May be smaller than the actual result size found, if max_products input value was set to a value less than the actual result size.");

    JSONArray listArray = new JSONArray();
    for (Product product : productsFound.values()) {
      product.toJSON(listArray);
      counter++;
      if (counter >= max_products) {
        break;
      }
    }
    results.put(PRODUCTS_LIST, listArray, "List of products found in the database matching the search criteria");

    return ret_val;
  }

  protected class Product {

    ProductData data = new ProductData();
    LinkedHashMap<String, ProductNameData> productNames = new LinkedHashMap<>();
    LinkedHashMap<String, ActiveIngredientsData> activeIngredients = new LinkedHashMap<>();

    Product(ResultSet results) throws ServiceException, SQLException {
      data.readValuesFromSQL(results);
      addProduct(results);
    }

    private void addProduct(ResultSet results) throws ServiceException, SQLException {
      String reg_no = results.getString(ProductData.REG_NO);
      String nameId = results.getString(ProductNameData.PRODUCT_ID);
      String aiId = results.getString(ActiveIngredientsData.AI_ID);
      if (data.RegNo().equals(reg_no)) {

        if (!productNames.containsKey(nameId)) {
          ProductNameData newData = new ProductNameData();
          newData.readValuesFromSQL(results);
          productNames.put(nameId, newData);
        }

        if (!activeIngredients.containsKey(aiId)) {
          ActiveIngredientsData newData = new ActiveIngredientsData();
          newData.readValuesFromSQL(results);
          activeIngredients.put(aiId, newData);
        }

      } else {
        throw new ServiceException("Invalid " + ProductData.REG_NO + " passed to Product::addProduct");
      }
    }

    public void toJSON(JSONArray tArray) throws JSONException {
      JSONArray ret_val = new JSONArray();

      data.toJSON(ret_val);
      JSONArray names = new JSONArray();
      for (ProductNameData nameData : productNames.values()) {
        JSONArray tempArray = new JSONArray();
        nameData.toJSON(tempArray);
        names.put(tempArray);
      }
      ret_val.put(JSONUtils.data(PRODUCTS_LIST, names));

      JSONArray ingredients = new JSONArray();
      for (ActiveIngredientsData aiData : activeIngredients.values()) {
        JSONArray tempArray = new JSONArray();
        aiData.toJSON(tempArray);
        ingredients.put(tempArray);
      }
      ret_val.put(JSONUtils.data(AI_LIST, ingredients));

      tArray.put(ret_val);
    }
  }

}