/**
		*
		*/
package de.intevation.gnv.geobackend.sde.datasources;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;

import org.apache.log4j.Logger;

import com.esri.sde.sdk.client.SDEPoint;
import com.esri.sde.sdk.client.SeColumnDefinition;
import com.esri.sde.sdk.client.SeConnection;
import com.esri.sde.sdk.client.SeException;
import com.esri.sde.sdk.client.SeFilter;
import com.esri.sde.sdk.client.SeLayer;
import com.esri.sde.sdk.client.SeQuery;
import com.esri.sde.sdk.client.SeRow;
import com.esri.sde.sdk.client.SeShape;
import com.esri.sde.sdk.client.SeShapeFilter;
import com.esri.sde.sdk.client.SeSqlConstruct;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.io.WKTReader;


		/**
 * @author Tim Englich <tim.englich@intevation.de>
 *
 */
public class ArcSDEStatement implements Statement {

	/**
     * the logger, used to log exceptions and additonaly information
     */
    private static Logger log = Logger.getLogger(ArcSDEStatement.class);
    
	private ArcSDEConnection connection = null;
	
	/**
	 * Constructor
	 */
	public ArcSDEStatement(ArcSDEConnection connection) {
		this.connection = connection;
	}

	/**
	 * @see java.sql.Statement#addBatch(java.lang.String)
	 */
	public void addBatch(String arg0) throws SQLException {
	}

	/**
	 * @see java.sql.Statement#cancel()
	 */
	public void cancel() throws SQLException {
	}

	/**
	 * @see java.sql.Statement#clearBatch()
	 */
	public void clearBatch() throws SQLException {
	}

	/**
	 * @see java.sql.Statement#clearWarnings()
	 */
	public void clearWarnings() throws SQLException {
	}

	/**
	 * @see java.sql.Statement#close()
	 */
	public void close() throws SQLException {
	}

	/**
	 * @see java.sql.Statement#execute(java.lang.String)
	 */
	public boolean execute(String arg0) throws SQLException {

		return false;
	}

	/**
	 * @see java.sql.Statement#execute(java.lang.String, int)
	 */
	public boolean execute(String arg0, int arg1) throws SQLException {

		return false;
	}

	/**
	 * @see java.sql.Statement#execute(java.lang.String, int[])
	 */
	public boolean execute(String arg0, int[] arg1) throws SQLException {

		return false;
	}

	/**
	 * @see java.sql.Statement#execute(java.lang.String, java.lang.String[])
	 */
	public boolean execute(String arg0, String[] arg1) throws SQLException {

		return false;
	}

	/**
	 * @see java.sql.Statement#executeBatch()
	 */
	public int[] executeBatch() throws SQLException {

		return null;
	}

	/**
	 * @see java.sql.Statement#executeQuery(java.lang.String)
	 */
	public ResultSet executeQuery(String statement) throws SQLException {
		try {
		    SeQuery query = null;
	        if (statement.toLowerCase().contains("st_astext")){
	            
	            String[] values =  statement.toLowerCase().split("where", 2);
	            String where = values.length > 1 ? values[1].trim() : "";
	            values = values[0].split("from", 2);
	            String layerName = values[1].toUpperCase().trim();
	            String[] returnFields  = values[0].replaceAll("select", "").trim().split(",");
	            String geometryColumnName = "N/N";
	            for (int i = 0; i < returnFields.length; i++){
	                returnFields[i] = returnFields[i].trim();
	                if (returnFields[i].startsWith("st_astext(")){
	                    returnFields[i] = returnFields[i].replaceAll("st_astext", "");
	                    returnFields[i] = returnFields[i].substring(1, returnFields[i].length()-1);
	                    geometryColumnName = returnFields[i];
	                }
	            }
	            Geometry g = null;
	            int pos = where.indexOf("intersects");
	            if (pos > 0 ){
	                String substr =  where.substring(pos);
	                where = where.substring(0,where.lastIndexOf("and")); // TODO auch or unterst�tzen
	                String intersectsStmt = substr.substring(0, substr.lastIndexOf(")")); // Annahme r�umliches Stmt steht am Ende
	                String wkt = intersectsStmt.substring(intersectsStmt.indexOf("\""), intersectsStmt.lastIndexOf("\"")).replace("\"", "").trim();
	                g = new WKTReader().read(wkt);
	                
	            }
	            return this.executeQuery(this.connection.getSeConnection(), layerName, geometryColumnName, where, g, returnFields);
	        }else{
	            query = new SeQuery(this.connection.getSeConnection());
	            query.prepareSql(statement);
	            query.execute();
	            return this.handleResultSet(query);
	        }
	       
		} catch (Exception e) {
	        log.error(e,e);
	        throw new SQLException(e.getMessage());
        }
        
        
	}
	
    private ResultSet executeQuery(SeConnection con, String pLayername,
                                   String pSpatialColumnName, String pWhere,
                                   Geometry g, String[] pReturnFields)
                                                                              throws SQLException {
        log.debug("executeQuery()");
        try {
            // get the layer for querying
            SeLayer lLayer = new SeLayer(con, pLayername, pSpatialColumnName);
            SeShapeFilter[] filters  = null;
            if (g != null){
                SeShape shape = new SeShape();
                shape.setCoordRef(lLayer.getCoordRef());
                
                SDEPoint[] lPoints = new ArcSDEUtils().createPoints(g);
    
                shape.generatePolygon(lPoints.length, 1, null, lPoints);
                SeShapeFilter filter = new SeShapeFilter(pLayername,
                        pSpatialColumnName, shape, SeFilter.METHOD_AI);
                filters = new SeShapeFilter[1];
                filters[0] = filter;
            }

            SeQuery spatialQuery = null;
            SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere);
            spatialQuery = new SeQuery(con, pReturnFields, sqlCons);
            spatialQuery.prepareQuery();
            /*
             * Set spatial constraints
             */
            if (filters != null){
                spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false,
                        filters);
            }
            spatialQuery.execute();

            return this.handleResultSet(spatialQuery);

        } catch (Exception e) {
            log.error(e.getMessage(), e);
            throw new SQLException(e.getMessage());
        }
    }

	/**
	 * @see java.sql.Statement#executeUpdate(java.lang.String)
	 */
	public int executeUpdate(String arg0) throws SQLException {

		return 0;
	}

	/**
	 * @see java.sql.Statement#executeUpdate(java.lang.String, int)
	 */
	public int executeUpdate(String arg0, int arg1) throws SQLException {

		return 0;
	}

	/**
	 * @see java.sql.Statement#executeUpdate(java.lang.String, int[])
	 */
	public int executeUpdate(String arg0, int[] arg1) throws SQLException {

		return 0;
	}

	/**
	 * @see java.sql.Statement#executeUpdate(java.lang.String, java.lang.String[])
	 */
	public int executeUpdate(String arg0, String[] arg1) throws SQLException {

		return 0;
	}

	/**
	 * @see java.sql.Statement#getConnection()
	 */
	public Connection getConnection() throws SQLException {
		return this.connection;
	}

	/**
	 * @see java.sql.Statement#getFetchDirection()
	 */
	public int getFetchDirection() throws SQLException {

		return 0;
	}

	/**
	 * @see java.sql.Statement#getFetchSize()
	 */
	public int getFetchSize() throws SQLException {

		return 0;
	}

	/**
	 * @see java.sql.Statement#getGeneratedKeys()
	 */
	public ResultSet getGeneratedKeys() throws SQLException {

		return null;
	}

	/**
	 * @see java.sql.Statement#getMaxFieldSize()
	 */
	public int getMaxFieldSize() throws SQLException {

		return 0;
	}

	/**
	 * @see java.sql.Statement#getMaxRows()
	 */
	public int getMaxRows() throws SQLException {

		return 0;
	}

	/**
	 * @see java.sql.Statement#getMoreResults()
	 */
	public boolean getMoreResults() throws SQLException {

		return false;
	}

	/**
	 * @see java.sql.Statement#getMoreResults(int)
	 */
	public boolean getMoreResults(int arg0) throws SQLException {

		return false;
	}

	/**
	 * @see java.sql.Statement#getQueryTimeout()
	 */
	public int getQueryTimeout() throws SQLException {

		return 0;
	}

	/**
	 * @see java.sql.Statement#getResultSet()
	 */
	public ResultSet getResultSet() throws SQLException {

		return null;
	}

	/**
	 * @see java.sql.Statement#getResultSetConcurrency()
	 */
	public int getResultSetConcurrency() throws SQLException {

		return 0;
	}

	/**
	 * @see java.sql.Statement#getResultSetHoldability()
	 */
	public int getResultSetHoldability() throws SQLException {

		return 0;
	}

	/**
	 * @see java.sql.Statement#getResultSetType()
	 */
	public int getResultSetType() throws SQLException {

		return 0;
	}

	/**
	 * @see java.sql.Statement#getUpdateCount()
	 */
	public int getUpdateCount() throws SQLException {

		return 0;
	}

	/**
	 * @see java.sql.Statement#getWarnings()
	 */
	public SQLWarning getWarnings() throws SQLException {

		return null;
	}

	/**
	 * @see java.sql.Statement#setCursorName(java.lang.String)
	 */
	public void setCursorName(String arg0) throws SQLException {
	}

	/**
	 * @see java.sql.Statement#setEscapeProcessing(boolean)
	 */
	public void setEscapeProcessing(boolean arg0) throws SQLException {
	}

	/**
	 * @see java.sql.Statement#setFetchDirection(int)
	 */
	public void setFetchDirection(int arg0) throws SQLException {
	}

	/**
	 * @see java.sql.Statement#setFetchSize(int)
	 */
	public void setFetchSize(int arg0) throws SQLException {
	}

	/**
	 * @see java.sql.Statement#setMaxFieldSize(int)
	 */
	public void setMaxFieldSize(int arg0) throws SQLException {
	}

	/**
	 * @see java.sql.Statement#setMaxRows(int)
	 */
	public void setMaxRows(int arg0) throws SQLException {
	}

	/**
	 * @see java.sql.Statement#setQueryTimeout(int)
	 */
	public void setQueryTimeout(int arg0) throws SQLException {
	}
	
	/**
	 * Copied from de.intevation.gnv.geobackend.sde.datasources.SDEQuery
	 *  @param pSeQuery
	 *  @return
	 * @throws SeException
	 */
    private ResultSet handleResultSet(SeQuery pSeQuery) throws SeException {
        log.debug("ArcSDEStatement,handleResultSet()");
        SDEResultSet lSet = new SDEResultSet();
        SeRow row;
        int lCount;
        for (lCount = 0; (row = pSeQuery.fetch()) != null; lCount++) {
            // one time execution
            if (lCount == 0) {
                // analyze cols of result set
                SeColumnDefinition[] lCols = row.getColumns();
                for (SeColumnDefinition lCol : lCols) {
                    lSet.addCol(new ColDefinition(lCol.getName(), lCol.getType()));// notice: esri-types have been copied into colDefinition class!
                }
            }
            short lNumCols = row.getNumColumns();
            Row lBackingRow = new Row(lNumCols);
            for (int i = 0; i < lNumCols; i++) {
                lBackingRow.addObject(row.getObject(i), i);
            }
            lSet.addRow(lBackingRow);
        }
        pSeQuery.close();
        return lSet;
    }

}