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

import crlmod.ServiceResources;
import static crlmod.ServiceResources.CR_LMOD_R2;
import csip.ModelDataService;
import csip.annotations.Description;
import csip.annotations.Name;
import csip.annotations.Resource;
import csip.annotations.State;
import static csip.annotations.State.RELEASED;
import csip.annotations.VersionInfo;
import csip.api.server.ServiceException;
import csip.utils.JSONUtils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.http.HttpServletRequest;
import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.MultivaluedMap;
import javax.ws.rs.core.Response;
import javax.ws.rs.core.UriInfo;
import org.apache.http.client.HttpResponseException;
import org.codehaus.jettison.json.JSONArray;

/**
 *
 * @author <a href="mailto:shaun.case@colostate.edu">Shaun Case</a>
 */
@Name("RUSLE2")
@Description("Retrieves XML data files for any RUSLE2 object")
@VersionInfo("1.0")
@State(RELEASED)
@Path("d/rusle2/1.0")
@Resource(from = ServiceResources.class)
public class V1_0 extends ModelDataService {

  public static String ID_KEY = "id";
  public static String NAME_KEY = "name";
  public static String PARTIAL_NAME_KEY = "partial_name";
  public static String PATH_KEY = "path";
  public static String PARTIAL_PATH_KEY = "partial_path";
  public static String LIMIT_KEY = "limit";

  private String[] ids;
  private String[] names;
  private String[] partial_names;
  private String[] paths;
  private String[] partial_paths;
  private int limit = 0;

  private List<TableResult> searchResults = new ArrayList<>();

  @Override
  protected void preProcess() throws Exception {
    boolean haveValidRequest = false;

    //   If we have the ids input key, don't check the other keys, that would not
    // make logical sense to structure such a request.
    if (parameter().has(ID_KEY)) {
      ids = parameter().getStringArray(ID_KEY);
      haveValidRequest = true;
    } else {

      //  If we have a names list, no need to check for a partial names list
      if (parameter().has(NAME_KEY)) {
        names = parameter().getStringArray(NAME_KEY);
        haveValidRequest = true;
      } else {
        if (parameter().has(PARTIAL_NAME_KEY)) {
          partial_names = parameter().getStringArray(PARTIAL_NAME_KEY);
          haveValidRequest = true;
        }
      }

      //  If we have a paths list, no need to check for a partial paths list
      if (parameter().has(PATH_KEY)) {
        paths = parameter().getStringArray(PATH_KEY);
        haveValidRequest = true;
      } else {
        if (parameter().has(PARTIAL_PATH_KEY)) {
          partial_paths = parameter().getStringArray(PARTIAL_PATH_KEY);
          haveValidRequest = true;
        }
      }
    }

    //  Shall we limit the return amount.  Really only useful if the client 
    // requests a list of objects by specifying an empty ids list input.
    if (parameter().has(LIMIT_KEY)) {
      limit = parameter().getInt(LIMIT_KEY);
    }

    if (!haveValidRequest) {
      throw new ServiceException("Invalid input provided.  Please check the required parameters documentation.");
    }
  }

  @Override
  protected void doProcess() throws Exception {
    if ((null != ids) && (ids.length <= 0)) {
      // ************************************************************************
      //
      //   Client wants a list of available objects.  Ideally, this should not be
      // called by any model code.  This will return a huge amount of data and a
      // model should already know what it is looking for.
      //
      //   This will return only a list of ID, Path, Name values, and will not
      // return any XML data (result would be too large otherwise)
      //
      // ************************************************************************
      String query = "SELECT " + ((limit > 0) ? " TOP " + limit : "") + " id, path, name, scienceDate FROM R2_Data ORDER BY path, name;";
      try (Connection conn = getConnection(); Statement stmnt = conn.createStatement();) {
        ResultSet results = stmnt.executeQuery(query);
        while (results.next()) {
          TableResult row = new TableResult();

          row.id = results.getString("id");
          row.path = results.getString("path");
          row.name = results.getString("name");
          row.scienceDate = results.getString("scienceDate");

          searchResults.add(row);
        }
      }

    } else {
      if (null != ids) {
        //  Client wants data by Id
        String query = "SELECT " + ((limit > 0) ? " TOP " + limit : "") + " id, path, name, scienceDate, xmlData FROM R2_Data WHERE ";
        query += buildPreparedWhereIsList("id", ids) + " ORDER BY path, name;";
        try (Connection conn = getConnection(); PreparedStatement stmnt = conn.prepareStatement(query)) {
          int counter = 1;
          for (String id : ids) {
            stmnt.setString(counter++, id);
          }

          ResultSet results = stmnt.executeQuery();

          while (results.next()) {
            TableResult row = new TableResult();

            row.id = results.getString("id");
            row.path = results.getString("path");
            row.name = results.getString("name");
            row.scienceDate = results.getString("scienceDate");
            row.XML = results.getString("xmlData");
            searchResults.add(row);
          }
        }
      } else {
        //  Client wants data by other fields.  Query other fields.
        String query = "SELECT " + ((limit > 0) ? " TOP " + limit : "") + " id, path, name, scienceDate, xmlData FROM R2_Data WHERE ";
        boolean add = false;

        if ((null != paths) && (paths.length >= 0)) {
          add = true;
          query += buildPreparedWhereIsList("path", paths);
        }

        if ((null != names) && (names.length >= 0)) {
          query += ((add) ? " AND " : " ") + buildPreparedWhereIsList("name", names);
          add = true;
        }

        if ((null != partial_paths) && (partial_paths.length >= 0)) {
          query += ((add) ? " AND " : " ") + buildPreparedWhereLikeList("path", partial_paths);
          add = true;
        }

        if ((null != partial_names) && (partial_names.length >= 0)) {
          query += ((add) ? " AND " : " ") + buildPreparedWhereLikeList("name", partial_names);
          add = true;
        }
        query += " ORDER BY path,name;";

        try (Connection conn = getConnection(); PreparedStatement stmnt = conn.prepareStatement(query)) {
          int counter = 1;

          if ((null != paths) && (paths.length >= 0)) {
            for (String path : paths) {
              stmnt.setString(counter++, path);
            }
          }

          if ((null != names) && (names.length >= 0)) {
            for (String name : names) {
              stmnt.setString(counter++, name);
            }
          }

          if ((null != partial_paths) && (partial_paths.length >= 0)) {
            for (String path : partial_paths) {
              stmnt.setString(counter++, "%" + path + "%");
            }
          }

          if ((null != partial_names) && (partial_names.length >= 0)) {
            for (String name : partial_names) {
              stmnt.setString(counter++, "%" + name + "%");
            }
          }

          ResultSet results = stmnt.executeQuery();

          while (results.next()) {
            TableResult row = new TableResult();

            row.id = results.getString("id");
            row.path = results.getString("path");
            row.name = results.getString("name");
            row.scienceDate = results.getString("scienceDate");
            row.XML = results.getString("xmlData");
            searchResults.add(row);
          }
        }
      }
    }

  }

  @Override
  protected void postProcess() throws Exception {
    JSONArray rows = new JSONArray();

    for (TableResult row : searchResults) {
      rows.put(row.toJSON());
    }

    results().put("r2_data_list", rows);
  }

  protected String buildPreparedWhereIsList(String fieldName, String[] values) {
    String where = "";
    if ((null != values) && (values.length > 0)) {
      where = fieldName + " IN (";
      for (int i = 0; i < values.length; i++) {
        where += "?";
        if ((i + 1) < values.length) {
          where += ",";
        }
      }
      where += ")";
    }

    return where;
  }

  protected String buildPreparedWhereLikeList(String fieldName, String[] values) {
    String where = "";
    if ((null != values) && (values.length > 0)) {
      where = "(";
      for (int i = 0; i < values.length; i++) {
        where += fieldName + " LIKE ? ";
        if ((i + 1) < values.length) {
          where += " OR ";
        }
      }
      where += ")";
    }

    return where;
  }

  protected Connection getConnection() throws ServiceException {
    return resources().getJDBC(CR_LMOD_R2);
  }

  protected class TableResult {

    String id;
    String path;
    String name;
    String scienceDate;
    String XML = null;

    public JSONArray toJSON() {
      JSONArray result = new JSONArray();

      result.put(JSONUtils.data(ID_KEY, id, "The unique id for this path/name combination.", null));
      result.put(JSONUtils.data(PATH_KEY, path, "The path for this object", null));
      result.put(JSONUtils.data(NAME_KEY, name, "The name for this object.", null));
      result.put(JSONUtils.data("science_date", scienceDate, "The date associated with this version of the XML file in this record.", null));
      result.put(JSONUtils.data("xml_file", XML, "The formatted XML file, if any, for this path/name combination..", null));
      return result;
    }
  }

  @GET
  @Produces(MediaType.APPLICATION_XML)
  public Response fetchFile(@Context UriInfo uriInfo,
      @Context HttpServletRequest httpReq){

    String requestString = httpReq.getQueryString();
    MultivaluedMap<String, String> requestMap = uriInfo.getQueryParameters();

    if ((null != requestMap) && (requestMap.size() > 0)) {
      if (requestMap.containsKey("path")) {
        String path = requestMap.getFirst("path").replaceAll("//", "/").replaceAll("//", "/");
        String name = path.substring(path.lastIndexOf("/") + 1);
        path = path.substring(0, path.lastIndexOf("/"));
        path = path.replaceAll("/", "\\");

        String query = "SELECT xmlData FROM R2_Data WHERE path=? AND name=?";
        try (Connection conn = createDataSource(); PreparedStatement stmnt = conn.prepareStatement(query)) {
          stmnt.setString(1, path);
          stmnt.setString(2, name);

          ResultSet results = stmnt.executeQuery();

          while (results.next()) {
            return Response.status(Response.Status.OK)
                .entity(results.getString("xmlData"))
                .build();
          }

          return Response.status(Response.Status.NOT_FOUND)
              .build();
        } catch (ClassNotFoundException | SQLException ex) {
          return Response.status(Response.Status.INTERNAL_SERVER_ERROR)
              .entity(ex.getMessage())
              .build();
        }
      }
      return Response.status(Response.Status.NOT_FOUND)
          .build();
    }

    return Response.status(Response.Status.INTERNAL_SERVER_ERROR)
        .build();
  }

  private static synchronized Connection createDataSource() throws ClassNotFoundException, SQLException {
    Connection connection = null;

    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    //System.out.println("Driver was loaded for: com.microsoft.sqlserver.jdbc.SQLServerDriver");
    connection = DriverManager.getConnection("jdbc:sqlserver://129.82.20.129:1433;databaseName=CR_LMOD_2022_step_r2;user=sa;password=csurams#1");

    //System.out.println("Connection to the local Conservation Resources DB Service was successful\n================================================================================\n");
    connection.setAutoCommit(true);
    return connection;
  }
}