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

/**
 *
 * @author <a href="mailto:shaun.case@colostate.edu">Shaun Case</a>
 */
/*
 * $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.
 */
import csip.ServiceException;
import csip.SessionLogger;
import csip.utils.Binaries;
import gisobjects.GISObject;
import gisobjects.GISObjectException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import static database.DBResources.NRT_DB;

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

    Connection conn;
    protected SessionLogger LOG;

    public NRT_DB(Class<?> c, SessionLogger log) throws SQLException, ServiceException {
        LOG = log;

        conn = Binaries.getRessourceJDBC(c, NRT_DB, log);
        validate();
    }

    @Override
    public void close() throws SQLException {
        if ( null != conn){
            conn.close();
            conn = null;
        }
    }
    
    public final void validate() throws SQLException {
        if ((null != conn) && (!conn.getMetaData().getDatabaseProductName().contains("Microsoft SQL"))) {
            throw new SQLException("Invalid connection type passed to NRT_DB.  Class requires a connection to a MsSQL server.");
        }

        String query = "SELECT name FROM sys.tables WHERE name='d_state_county';";
        try (Statement statement = conn.createStatement();) {
            ResultSet results = statement.executeQuery(query);

            if (!results.next()) {
                throw new SQLException("Invalid connection type passed to NRT_DB.  Class requires a connection to an NRT server that contains the d_state_county table.");
            }
        } catch (SQLException ex) {
            throw new SQLException("Invalid connection type passed to NRT_DB. SQL Error: " + ex.getMessage(), ex);
        }
    }    

    /**
     * New and improved version of findCounty. Uses the NRT database
     * d_state_county table as the definitive source
     *

     * @return
     * @throws java.sql.SQLException
     * @throws csip.ServiceException
     */
    public String findStateCode(GISObject location) throws SQLException, ServiceException {
        String ret_val = "";
        
        try (Statement s = conn.createStatement();) {
            double [] centroid = location.getCentroid();
            String select = "SELECT state_code, county_code, county_display FROM d_state_county "
                    + " WHERE state_county_geometry.STContains( geometry::STGeomFromText('POINT( " + centroid[0] + " " + centroid[1] +  " )', 4326)) = 1 "
                    + " AND state_county_geometry.STSrid is not NULL"
                    + " AND state_county_type='C';";
            try (ResultSet r = s.executeQuery(select)) {
                if (r.next()) {                    
                    ret_val = r.getString("state_code");                   
                } else {
                    throw new ServiceException("No county located in NRT State County query for that point: POINT( " + centroid[0] + " " + centroid[1] + " )");
                }
            }
        } catch (GISObjectException ex) {
            throw new ServiceException("Cannot find the state code for the location specified, GIS Object error: " + ex.getMessage(), ex);
        }
        
        if ( ret_val.isEmpty()){
            throw new ServiceException("Could not find the state code associated witht the GIS Object specified.");
        }
        return ret_val;
    }
}