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

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.Geometry;
import com.vividsolutions.jts.geom.LineString;
import com.vividsolutions.jts.geom.Point;
import com.vividsolutions.jts.geom.Polygon;

import com.vividsolutions.jts.io.WKTReader;

import de.intevation.gnv.geobackend.util.RedundancyRemover;

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;

/**
 * This Class wrapps an SDE-Statment to an java.sql.Statement.
 * This Class also handles the SDE-Specific -Spatial-Queries and
 * RasterQueries.
 * @author <a href="mailto:tim.englich@intevation.de">Tim Englich</a>
 * @author <a href="mailto:sascha.teichmann@intevation.de">Sascha L. Teichmann</a>
 */
public class ArcSDEStatement implements Statement {

    /**
     * the logger, used to log exceptions and additonaly information
     */
    private static Logger log = Logger.getLogger(ArcSDEStatement.class);

    /**
     * The Connection to the ArcSDE-Backend
     */
    private ArcSDEConnection connection = null;

    /**
     * Constructor
     * @param connection the Connection to the ArcSDE-Backend.
     */
    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"));
                    int andPos = where.lastIndexOf("and");
                    if (andPos < 0){
                        andPos = 0;
                    }
                    where = where.substring(0,andPos); // TODO support or also
                    String intersectsStmt = substr.substring(0, substr.lastIndexOf(")")); // Spatial-Statment is the last one
                    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: Support the selection of more than one Geometry
                            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());
        }


    }
    /**
     * This Methods Executes the ArcSDE Query if Spatial-Restrictions
     * are given
     * @param con The Connection to the ArcSDE-Backend.
     * @param pLayername The Name of tables which should be used in the Query
     * @param pSpatialColumnName the Name of the Spatial Column.
     * @param pWhere The where-Clause to limit the Data
     * @param g the Geometry to limit the Data
     * @param pReturnFields the Fileds that should be fetched from the Database
     * @param byClause the byClaus to order the Results.
     * @return the @see java.sql.ResultSet with the Data which was retrieved.
     * @throws SQLException
     */
    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;
                    }else if (g instanceof LineString){
                        shape.generateLine(lPoints.length, 1, null, lPoints);
                        searchMode = SeFilter.METHOD_LCROSS;
                    }

                    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 {
    }

    /**
     * This Method fetches all Rows from the SeQuery-Object and convert it into
     * an @see java.sql.ResultSet.
     *
     * @param pSeQuery the Queryobject where the Data should be fetched from.
     * @param isRaster Flag which indicates if the Result is Rasterdata.
     * @param geometry The Geometry which might be used to limit the data.
     * @return an java.sql:ResultSet which contains the Data.
     * @throws SeException
     */
    private ResultSet handleResultSet(SeQuery pSeQuery,
                                      boolean isRaster,
                                      Geometry geometry) throws SeException {
        boolean debug = log.isDebugEnabled();

        if (debug) {
            log.debug("ArcSDEStatement.handleResultSet()");
        }

        SDEResultSet lSet = isRaster
            ? new UncacheableSDEResultSet()
            : new SDEResultSet();
        SeRow row = null;
        int lCount;
        if (!isRaster){
            RedundancyRemover  [] removers = null;
            SeColumnDefinition [] lCols    = null;

            for (lCount = 0; (row = pSeQuery.fetch()) != null; lCount++) {
                // one time execution
                if (lCount == 0) {
                    // analyze cols of result set
                    lCols = row.getColumns();
                    for (SeColumnDefinition lCol : lCols) {
                        lSet.addCol(new ColDefinition(lCol.getName(), lCol.getType()));
                        // notice: esri-types have been copied into colDefinition class!
                    }
                    removers = new RedundancyRemover[lCols.length];
                    for (int i = 0; i < removers.length; ++i) {
                        removers[i] = new RedundancyRemover();
                    }
                }
                short lNumCols = row.getNumColumns();
                Row lBackingRow = new Row(lNumCols);
                for (int i = 0; i < lNumCols; i++) {
                    lBackingRow.addObject(
                        removers[i].filter(row.getObject(i)),
                        i);
                }
                lSet.addRow(lBackingRow);
            }

            if (debug && removers != null) {
                log.debug("datasets: " + lCount);
                StringBuilder sb = new StringBuilder("removed redundancy: ");
                float percent = 100f/lCount;
                for (int i = 0; i < removers.length; ++i) {
                    if (i > 0) sb.append(", ");
                    sb.append(lCols[i].getName())
                      .append(": ").append(removers[i].numRemoved())
                      .append(" (").append(removers[i].numRemoved()*percent)
                      .append("%)");
                }
                log.debug(sb.toString());
            }
        }else{
            try {
                pSeQuery.execute();
                row = pSeQuery.fetch();
                SeRasterAttr    attr       = row.getRaster(0);
                SeRaster        raster     = attr.getRasterInfo();
                SeRasterBand [] bands      = raster.getBands();
                SeRasterBand    rasterBand = bands[0];

                SeExtent extent = rasterBand.getExtent();

                double x = ((Point)geometry).getX();
                double y = ((Point)geometry).getY();

                boolean isPointInRaster =
                        x >= extent.getMinX() && x <= extent.getMaxX()
                    &&  y >= extent.getMinY() && y <= extent.getMaxY();

                if (isPointInRaster){

                    if (row == null){
                        pSeQuery.execute();
                        row = pSeQuery.fetch();
                    }

                    double midX = 0.5d*(extent.getMinX() + extent.getMaxX());
                    double midY = 0.5d*(extent.getMinY() + extent.getMaxY());

                    SDEPoint origin = rasterBand.getTileOrigin();

                    double maxX = origin.getX() < midX
                        ? extent.getMaxX()
                        : extent.getMinX();

                    double maxY = origin.getY() < midY
                        ? extent.getMaxY()
                        : extent.getMinY();

                    double mx = rasterBand.getBandWidth()/(maxX-origin.getX());
                    double bx = -origin.getX()*mx;
                    double px = mx*x + bx;

                    double my = rasterBand.getBandHeight()/(maxY-origin.getY());
                    double by = -origin.getY()*my;
                    double py = my*y + by;

                    SeRasterConstraint constraint = new SeRasterConstraint();
                    constraint.setLevel(0); // best resolution
                    constraint.setBands(rasterBand.getBandNumber());
                    int tx = (int)Math.floor(px / rasterBand.getTileWidth());
                    int ty = (int)Math.floor(py / rasterBand.getTileHeight());
                    constraint.setEnvelope(tx, ty, tx, ty);

                    pSeQuery.queryRasterTile(constraint);
                    SeRasterTile tile = row.getRasterTile();

                    if (tile != null){
                        double[] tileValues = new double[tile.getNumPixels()];
                        tileValues = tile.getPixels(tileValues);
                        lSet.addCol(new ColDefinition("tile", ColDefinition.FLOAT64));
                        Row lBackingRow = new Row(1);

                        double wx1 = (rasterBand.getTileWidth()*tx - bx)/mx;
                        double wx2 = (rasterBand.getTileWidth()*(tx+1) - bx)/mx;
                        double mxt = (rasterBand.getTileWidth()-1e-5d)/(wx2 - wx1);
                        double bxt = -wx1*mxt;

                        double wy1 = (rasterBand.getTileHeight()*ty - by)/my;
                        double wy2 = (rasterBand.getTileHeight()*(ty+1) - by)/my;
                        double myt = (rasterBand.getTileHeight()-1e-5d)/(wy2 - wy1);
                        double byt = -wy1*myt;

                        RasterObject ro = new RasterObject(
                            mxt, bxt,
                            myt, byt,
                            tile.getColumnIndex(),
                            tile.getRowIndex(),
                            tileValues,
                            rasterBand.getTileWidth(),
                            rasterBand.getTileHeight());
                        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;
    }

    /**
     * @see java.sql.Statement#isClosed()
     */
    public boolean isClosed() throws SQLException {
        return false;
    }

    /**
     * @see java.sql.Statement#isPoolable()
     */
    public boolean isPoolable() throws SQLException {
        return false;
    }

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

    /**
     * @see java.sql.Wrapper#isWrapperFor(java.lang.Class)
     */
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return false;
    }

    /**
     * @see java.sql.Wrapper#unwrap(java.lang.Class)
     */
    public <T> T unwrap(Class<T> iface) throws SQLException {
        return null;

    }
}