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

import csip.Config;
import csip.ServiceException;
import gisobjects.GISObject;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

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

  Connection connection;
  String indexNRT = "";


  public NRT_DB(Connection c) throws ServiceException, SQLException {
    if (null != c) {
      connection = c;
      if (!connection.getMetaData().getDatabaseProductName().contains("Microsoft SQL")) {
        throw new csip.ServiceException("Invalid connection type passed to NRT Database class.  Class requires a connection to a MsSQL server.");
      }
      String indexName = Config.getString("NRT.index.name", "");
      indexNRT = ((indexName.isEmpty()) ? "" : (" WITH(INDEX(" + indexName + ")) "));
    } else {
      throw new ServiceException("No valid SQL Connection passed to NRT_DB class");
    }
  }


  /**
   * New and improved version of findCounty. Uses the NRT datasource's
   * d_state_county table as the definitive source
   *
   * @param point GISObject representing a location, for which to find which
   * county it lies in. This could potentially be a polygon or other
   * geographical shape, but the preferred object is a point.
   * @return
   * @throws java.sql.SQLException
   * @throws csip.ServiceException
   */
  public County findCounty(GISObject point) throws SQLException, ServiceException {
    County cnty = null;
    String wkt = point.toWKT();

    if (wkt.contains("POINT")) {
      try (Statement s = connection.createStatement();) {
        String select = "SELECT state_abbr, county_code, county_display, state_county_code, "
            + "geography::STGeomFromText(state_county_geometry.STAsText(), 4326).MakeValid().STAsText() AS state_county_geometry"
            + " FROM d_state_county " + indexNRT
            + " WHERE state_county_geometry.STContains( geometry::STGeomFromText('" + wkt + "', 4326)) = 1 "
            + " AND state_county_geometry.STSrid is not NULL"
            + " AND state_county_type='C';";
        try (ResultSet r = s.executeQuery(select)) {
          if (r.next()) {
            cnty = new County();
            cnty.st_abbr = r.getString("state_abbr");
            cnty.county_code = r.getString("county_code");
            cnty.name = r.getString("county_display");
            cnty.st_cnty_code = r.getString("state_county_code");
            cnty.state_county_geometry = r.getString("state_county_geometry");
          } else {
            throw new ServiceException("No county located in NRT State County query for that point: " + wkt);
          }
        }
      }
    } else {
      throw new ServiceException("Geometry passed to findCounty was not a point type.");
    }
    return cnty;
  }


  public State findState(GISObject point) throws SQLException, ServiceException {
    State state = null;
    String wkt = point.toWKT();

    if (wkt.contains("POINT")) {
      try (Statement s = connection.createStatement();) {
        String select = "SELECT state_code, geography::STGeomFromText(state_county_geometry.STAsText(), 4326).MakeValid().STAsText() AS state_geometry "
            + " FROM d_state_county " + indexNRT
            + " WHERE state_county_geometry.STContains( geometry::STGeomFromText('" + wkt + "', 4326)) = 1 "
            + " AND state_county_geometry.STSrid is not NULL AND county_name = 'All Counties';";
        try (ResultSet r = s.executeQuery(select)) {
          if (r.next()) {
            state = new State();
            state.st_code = r.getString("state_code");
            state.geometry = r.getString("state_geometry");
          } else {
            throw new ServiceException("No county located in NRT State County query for that point: " + wkt);
          }
        }
      }
    } else {
      throw new ServiceException("Geometry passed to findState was not a point type.");
    }

    return state;

  }

  public static class State {

    public String st_code;
    public String geometry;
  }

  public static class County {

    public String st_abbr;
    public String county_code;
    public String name;
    public String st_cnty_code;
    public String county_centroid_X;
    public String county_centroid_Y;
    public String state_county_geometry;


    @Override
    public String toString() {
      return "{" + st_abbr + county_code + "|" + name + "|" + county_centroid_X + "|" + county_centroid_Y + "}";
    }
  }
}