/**
		*
		*/
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.SeExtent;
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.SeQueryInfo;
import com.esri.sde.sdk.client.SeRaster;
import com.esri.sde.sdk.client.SeRasterAttr;
import com.esri.sde.sdk.client.SeRasterBand;
import com.esri.sde.sdk.client.SeRasterConstraint;
import com.esri.sde.sdk.client.SeRasterTile;
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.Coordinate;
import com.vividsolutions.jts.geom.Envelope;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.geom.Point;
import com.vividsolutions.jts.geom.Polygon;
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")  || statement.toLowerCase().contains("intersects")){
	            
	            String[] values =  statement.toLowerCase().split("where", 2);
	            String where = values.length > 1 ? values[1].trim() : "";
	            String[] tableNames = values[0].substring(values[0].indexOf("from")).replaceFirst("from", "").toUpperCase().trim().split(", ");
	            String columnValueString = values[0].substring(0, values[0].indexOf("from")).trim();
	            columnValueString = columnValueString.replaceFirst("select", "").trim();
	            String[] returnFields  = columnValueString.split(", ");
	            String geometryColumnName = null;
	            String byClause = null;
	            int byClausePos = where.indexOf("group by");
	            if (byClausePos < 0){
	                byClausePos = where.indexOf("order by");
	            }
	            if (byClausePos > 0){
	                byClause = where.substring(byClausePos);
	                where = where.substring(0,byClausePos);
	            }
	            
	            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];
	                }
	            }
	            
	            for (int i = 0; i < tableNames.length; i++){
	                tableNames[i] = tableNames[i].trim();
	            }
	            
	            Geometry g = null;
	            int pos = where.indexOf("intersects");
	            if (pos >= 0 ){
	                String substr =  where.substring(pos);
	                where = where.substring(0,where.lastIndexOf("intersects")); // TODO auch or unterst�tzen
	                int andPos = where.lastIndexOf("and");
	                if (andPos < 0){
	                    andPos = 0;
	                }
	                where = where.substring(0,andPos); // TODO auch or unterst�tzen
	                String intersectsStmt = substr.substring(0, substr.lastIndexOf(")")); // Annahme r�umliches Stmt steht am Ende
	                String wkt = null;
	                if (intersectsStmt.contains("select")){
	                    // Anstelle eines WKT ist ein InnerSelect zum Bestimmen der Comparatorgeometrie gegeben.
	                    String stmt = intersectsStmt.substring(intersectsStmt.indexOf("select"),intersectsStmt.lastIndexOf(")"));
	                    ResultSet resultSet = this.executeQuery(stmt);
	                    if (resultSet != null && resultSet.next()){
	                        // TODO Selektion von mehreren Geometrien unterst�tzen.
	                        wkt = resultSet.getString("SHAPE");
	                    }
	                }else{
	                    wkt = intersectsStmt.substring(intersectsStmt.indexOf("\""), intersectsStmt.lastIndexOf("\"")).replace("\"", "").trim();
	                }
	                g = new WKTReader().read(wkt.toUpperCase());
	                
	                if (geometryColumnName == null){
	                    geometryColumnName = "SHAPE"; // TODO dynamisch aus Intersects auslesen.
	                }
	                
	            }
	            
	            return this.executeQuery(this.connection.getSeConnection(), tableNames, geometryColumnName, where, g, returnFields,byClause);
	        }else{
	            query = new SeQuery(this.connection.getSeConnection());
	            query.prepareSql(statement);
	            query.execute();
	            return this.handleResultSet(query,false,null);
	        }
	       
		} 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, String byClause)
                                                                              throws SQLException {
        log.debug("executeQuery()");
        try {
            // get the layer for querying
            boolean isRaster = pSpatialColumnName.equalsIgnoreCase("raster");
            
            SeShapeFilter[] filters  = null;
            if (g != null){
                if (!isRaster){
                    SeLayer lLayer = new SeLayer(con, pLayername[0], pSpatialColumnName);
                    SeShape shape = new SeShape();
                    shape.setCoordRef(lLayer.getCoordRef());
                    
                    SDEPoint[] lPoints = new ArcSDEUtils().createPoints(g);
        
                    int searchMode = SeFilter.METHOD_AI;
                    if (g instanceof Polygon){
                        shape.generatePolygon(lPoints.length, 1, null, lPoints);
                    }else if (g instanceof Point){
                        shape.generatePoint(1, lPoints);
                        searchMode = SeFilter.METHOD_PC;
                    }
                    
                    SeShapeFilter filter = new SeShapeFilter(pLayername[0],
                            pSpatialColumnName, shape,searchMode);
                    filters = new SeShapeFilter[1];
                    filters[0] = filter;
                }
            }

            SeQuery spatialQuery = null;
            SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere);
            spatialQuery = new SeQuery(con);//, pReturnFields, sqlCons);
            
            SeQueryInfo queryInfo = new SeQueryInfo();
            queryInfo.setColumns(pReturnFields);
            
            if (byClause != null){
                queryInfo.setByClause(byClause);
            }
            
            queryInfo.setConstruct(sqlCons);
            spatialQuery.prepareQueryInfo(queryInfo);

            /*
             * Set spatial constraints
             */
            if (filters != null){
                spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false,
                        filters);
            }
           
            if (!isRaster){
                spatialQuery.execute();
            }
            
            return this.handleResultSet(spatialQuery,isRaster,g);

        } catch (Exception e) {
            if (e instanceof SeException){
                ArcSDEUtils.printError((SeException)e);
            }else{
                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, boolean isRaster, Geometry geometry) throws SeException {
        log.debug("ArcSDEStatement,handleResultSet()");
        SDEResultSet lSet = new SDEResultSet();
        SeRow row = null;;
        int lCount;
        if (!isRaster){
            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);
            }
        }else{
            try {
               
                int rasterSize = 128;
                pSeQuery.execute();
                row = pSeQuery.fetch();
                SeRasterAttr attr = row.getRaster(0);
                SeRaster raster = attr.getRasterInfo();
                SeRasterBand[] bands = raster.getBands();
                SeRasterBand rasterBand = bands[0];
                
                
                int height = rasterBand.getBandHeight();
                int width = rasterBand.getBandWidth();
                
                SeExtent extent = rasterBand.getExtent();
                log.debug("Extent: "+ extent.getMinX() +" "+extent.getMinY()+" "+extent.getMaxX()+ " "+extent.getMaxY() );
                log.debug("Querygeometry: "+geometry.toText());
                
                double x = ((Point)geometry).getX();
                double y = ((Point)geometry).getY();
                boolean isPointInRaster = false;
                if ((x >= extent.getMinX() && x <= extent.getMaxX()) && (y >= extent.getMinY() && y <= extent.getMaxY())){
                    isPointInRaster = true;
                }
                
                double dxNature = x - extent.getMinX();
                double dyNature = extent.getMaxY() - y;//y-extent.getMinY();
                double widthNature = extent.getMaxX()-extent.getMinX(); 
                double heightNature = extent.getMaxY()-extent.getMinY();
                
                int pixelX = (int)Math.round(dxNature * width / widthNature);
                int pixelY = (int)Math.round(dyNature * height/ heightNature);
                
                log.debug("Rasterdimesion (Pixel) " + width + " / " + height);
                log.debug ("Required Pixel: " + pixelX + " / "+ pixelY);
                int bandNumber = rasterBand.getBandNumber();
                log.debug("BAND-ID "+rasterBand.getId().longValue());
                int maxLevel = 0;
                log.debug("Maxlevel: "+maxLevel);
                
                if (isPointInRaster){
                                        
                    if (row == null){
                        pSeQuery.execute();
                        row = pSeQuery.fetch();
                    }
                    SeRasterConstraint constraint = new SeRasterConstraint();
                    constraint.setLevel(maxLevel);
                    constraint.setBands(bandNumber);
                    constraint.setEnvelope(pixelX / rasterSize, pixelY / rasterSize, pixelX / rasterSize, pixelY / rasterSize);
                    pSeQuery.queryRasterTile(constraint);
                    SeRasterTile tile = row.getRasterTile();
                    
                    if(tile != null){
                        log.debug("BAND-ID Tile "+tile.getBandId().longValue());
                        log.debug("Pixel "+ tile.getNumPixels());
                        log.debug("Column / Row "+tile.getColumnIndex()+" / "+tile.getRowIndex());
                        
                        
                        double[] tileValues = new double[tile.getNumPixels()];
                        tileValues = tile.getPixels(tileValues);
                        lSet.addCol(new ColDefinition("tile",ColDefinition.FLOAT64));
                        Row lBackingRow = new Row(1);
                        Envelope envelope = new Envelope(
                                            new Coordinate(extent.getMinX(), 
                                                           extent.getMinY()), 
                                            new Coordinate(extent.getMaxX(), 
                                                           extent.getMaxY()));
                        RasterObject ro = new RasterObject(envelope, 
                                                           tileValues, 
                                                           rasterSize, 
                                                           tile.getRowIndex(), 
                                                           tile.getColumnIndex(),
                                                           width,
                                                           height);
                        lBackingRow.addObject(ro,0);
                        lSet.addRow(lBackingRow);
                     }
                    
                }else{
                    log.debug("The Query doesn't deliver any Information because the Point is located outside the Raster.");
                }
            } catch (Exception e) {
                log.error(e,e);
            }
        }
        pSeQuery.close();
        return lSet;
    }

    public boolean isClosed() throws SQLException {
        return false;
    }

    public boolean isPoolable() throws SQLException {
        return false;
    }

    public void setPoolable(boolean arg0) throws SQLException {
    }

    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return false;
    }

    public <T> T unwrap(Class<T> iface) throws SQLException {
        return null;
    }

}