/*
 *    GeoTools - OpenSource mapping toolkit
 *    http://geotools.org
 *    (C) 2002-2006, Geotools Project Managment Committee (PMC)
 *
 *    This library is free software; you can redistribute it and/or
 *    modify it under the terms of the GNU Lesser General Public
 *    License as published by the Free Software Foundation; either
 *    version 2.1 of the License, or (at your option) any later version.
 *
 *    This library is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *    Lesser General Public License for more details.
 *
 *    Created on 16/10/2003
 */
package org.geotools.data.oracle;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.sql.DataSource;
import oracle.jdbc.OracleConnection;
import oracle.sql.ARRAY;
import oracle.sql.Datum;
import oracle.sql.STRUCT;

import org.geotools.data.DataSourceException;
import org.geotools.data.DataUtilities;
import org.geotools.data.DefaultQuery;
import org.geotools.data.DefaultTransaction;
import org.geotools.data.FeatureReader;
import org.geotools.data.FeatureSource;
import org.geotools.data.Query;
import org.geotools.data.Transaction;
import org.geotools.data.jdbc.DefaultSQLBuilder;
import org.geotools.data.jdbc.FeatureTypeInfo;
import org.geotools.data.jdbc.JDBCDataStore;
import org.geotools.data.jdbc.JDBCDataStoreConfig;
import org.geotools.data.jdbc.JDBCFeatureWriter;
import org.geotools.data.jdbc.JDBCUtils;
import org.geotools.data.jdbc.QueryData;
import org.geotools.data.jdbc.SQLBuilder;
import org.geotools.data.jdbc.attributeio.AttributeIO;
import org.geotools.data.jdbc.datasource.DataSourceFinder;
import org.geotools.data.jdbc.datasource.UnWrapper;
import org.geotools.data.oracle.attributeio.SDOAttributeIO;
import org.geotools.data.oracle.referencing.OracleAuthorityFactory;
import org.geotools.data.oracle.sdo.GeometryConverter;
import org.geotools.data.oracle.sdo.TT;
import org.geotools.feature.AttributeType;
import org.geotools.feature.AttributeTypeFactory;
import org.geotools.feature.FeatureType;
import org.geotools.feature.GeometryAttributeType;
import org.geotools.feature.SchemaException;
import org.geotools.filter.SQLEncoder;
import org.geotools.filter.SQLEncoderException;
import org.geotools.filter.SQLEncoderOracle;
import org.geotools.geometry.jts.ReferencedEnvelope;
import org.geotools.referencing.CRS;
import org.opengis.filter.Filter;
import org.opengis.referencing.FactoryException;
import org.opengis.referencing.crs.CoordinateReferenceSystem;
import org.opengis.referencing.crs.GeodeticCRS;

import com.vividsolutions.jts.geom.Envelope;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.geom.GeometryFactory;
import com.vividsolutions.jts.io.ParseException;

/**
 * @author Sean Geoghegan, Defence Science and Technology Organisation.
 * @source $URL: http://svn.geotools.org/geotools/tags/2.4.1/modules/unsupported/oracle-spatial/src/main/java/org/geotools/data/oracle/OracleDataStore.java $
 */
public class OracleDataStore extends JDBCDataStore {
     private static final Logger LOGGER = org.geotools.util.logging.Logging.getLogger("org.geotools.data.oracle");
     private OracleAuthorityFactory af;

    /**
     * @param connectionPool
     * @param config
     * @throws IOException
     */
    public OracleDataStore(DataSource dataSource, JDBCDataStoreConfig config) throws IOException {
        super(dataSource, config);
    }

    /**
     * @param connectionPool
     * @throws DataSourceException
     */
    public OracleDataStore(DataSource dataSource, String schemaName, Map fidGeneration) throws IOException {
        this(dataSource, schemaName, schemaName, fidGeneration);
    }
    
    /**
     * @param connectionPool
     * @param namespace
     * @throws DataSourceException
     */
    public OracleDataStore(DataSource dataSource, String namespace, String schemaName, Map fidGeneration) throws IOException {
        //Ok, this needs more investigation, since the config constructor being
        //used seems to ignoe the fid map stuff.  I don't quite understand it,
        //and I think it may get picked up later, or at least auto-generated
        //later - maybe this is for the user specified stuff that never got
        //implemented.  Point being this needs to be looked into, I'm just 
        //setting it like this to get things working. -ch
        this(dataSource, new JDBCDataStoreConfig(namespace, schemaName, null, fidGeneration));

    }

    
    /** Crops non feature type tables. 
     * There are alot of additional tables in a Oracle tablespace. This tries
     * to remove some of them.  If the schemaName is provided in the Constructor
     * then the job of narrowing down tables will be mush easier.  Otherwise
     * there are alot of Meta tables and SDO tables to cull.  This method tries
     * to remove as many as possible. 
     * 
     * @see org.geotools.data.jdbc.JDBCDataStore#allowTable(java.lang.String)
     */
    protected boolean allowTable(String tablename) {
	LOGGER.finer("checking table name: " + tablename);
		if (tablename.endsWith("$"))  {
            return false;
		} else if (tablename.startsWith("BIN$"))  { // Added to ignore some Oracle 10g tables
            return false;
        } else if (tablename.startsWith("XDB$"))  {
            return false;
        } else if (tablename.startsWith("DR$"))  {
            return false;
        } else if (tablename.startsWith("DEF$"))  {
            return false;
        } else if (tablename.startsWith("SDO_"))  {
            return false;
        } else if (tablename.startsWith("WM$"))  {
            return false;
        } else if (tablename.startsWith("WK$"))  {
            return false;
        } else if (tablename.startsWith("AW$"))  {
            return false;
        } else if (tablename.startsWith("AQ$"))  {
            return false;
	} else if (tablename.startsWith("APPLY$"))  {
            return false;
	} else if (tablename.startsWith("REPCAT$"))  {
            return false;
        } else if (tablename.startsWith("CWM$"))  {
            return false;
        } else if (tablename.startsWith("CWM2$"))  {
            return false;
        } else if (tablename.startsWith("EXF$"))  {
            return false;
        } else if (tablename.startsWith("DM$"))  {
            return false;
        } 
        LOGGER.finer("returning true for tablename: " + tablename);
        return true;
    }

    /**
     * Overrides the buildAttributeType method to check for SDO_GEOMETRY columns.
     * 
     * @see <a href="http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10826.pdf">A doc from Oracle.</a>
     * 
     *  TODO: Determine the specific type of the geometry.
     */
    protected AttributeType buildAttributeType(ResultSet rs) throws IOException {
    	final int TABLE_NAME = 3;
        final int COLUMN_NAME = 4;
        final int TYPE_NAME = 6;
        final int IS_NULLABLE = 18; // "NO", "YES" or ""
        try {
			if (rs.getString(TYPE_NAME).equals("SDO_GEOMETRY")) {
			    String tableName = rs.getString(TABLE_NAME);
                String columnName = rs.getString(COLUMN_NAME);
                String isNullable = rs.getString( IS_NULLABLE );
                return getSDOGeometryAttribute(tableName, columnName, "YES".equals(isNullable) );
			} else  {
			    return super.buildAttributeType(rs);
			}
		} catch (SQLException e) {
			throw new DataSourceException("Sql error occurred", e);
		}
    }
   
    /**
     * Construct and SDO_GEOMETRY attribute.
     * 
     * @see org.geotools.data.jdbc.JDBCDataStore#buildAttributeType(java.sql.ResultSet) 
     * @param tableName
     * @param columnName
     * @param isNillable 
     */
    private AttributeType getSDOGeometryAttribute(String tableName, String columnName, boolean isNullable ) {
    	int srid = 0; // aka NULL
    	CoordinateReferenceSystem crs = null;
    	// here we have two separate point of failure, better keep them separated, 
		try {
			srid = determineSRID( tableName, columnName );
			try {
			    crs = CRS.decode("EPSG:" + srid);
			} catch(Exception e) {
			    // decode failed, let's try the internal authority
			    crs = determineCRS(srid);
			}
		} catch (Exception e) {
			LOGGER.warning( "Could not map SRID "+srid+" to CRS:"+e );
		}
		try {
		    Class geomClass = determineGeometryClass(tableName, columnName);
            return AttributeTypeFactory.newAttributeType(columnName, geomClass,isNullable, 0, null, crs);
		} catch(IOException e) {
		    LOGGER.warning( "Could not determine geometry class for [" + tableName + "." + columnName + "]" );
		}
    	return AttributeTypeFactory.newAttributeType(columnName, Geometry.class,isNullable);		
	}
    private Class determineGeometryClass(String tableName, String columnName) throws IOException {
        Connection conn = null;
        Statement statement = null;
        ResultSet result = null;
        try {
            String sqlStatement = //
            "select meta.sdo_layer_gtype \n" + "from mdsys.ALL_SDO_INDEX_INFO info \n"
                    + " inner join mdsys.user_sdo_index_metadata meta \n"
                    + " on info.index_name = meta.sdo_index_name \n" //
                    + "where info.table_name = '" + tableName + "' \n" //
                    + "and info.column_name = '" + columnName + "'";
            String schema = config.getDatabaseSchemaName();
            if(schema != null && !"".equals(schema)) {
                sqlStatement += " and info.table_owner = '" + schema + "'";
            }
            conn = getConnection(Transaction.AUTO_COMMIT);
            LOGGER.finer("the sql statement for geometry type check is " + sqlStatement);
            statement = conn.createStatement();
            result = statement.executeQuery(sqlStatement);

            if (result.next()) {
                String gType = result.getString(1);
                Class geometryClass = (Class) TT.GEOM_CLASSES.get(gType);
                if(geometryClass == null)
                    geometryClass = Geometry.class;

                return geometryClass;
            } else {
                return Geometry.class;
            }
        } catch (SQLException sqle) {
            String message = sqle.getMessage();
            LOGGER.fine("Could not determine geometry class due to an error_: "  + sqle);

            throw new DataSourceException(message, sqle);
        } finally {
            JDBCUtils.close(result);
            JDBCUtils.close(statement);
            JDBCUtils.close(conn, Transaction.AUTO_COMMIT, null);
        }

    }

    protected CoordinateReferenceSystem determineCRS(int srid) throws IOException {
        try {
            return getOracleAuthorityFactory().createCRS(srid);
        } catch(FactoryException e) {
            return null;
        }
    }
        
	/**
     * @see org.geotools.data.jdbc.JDBCDataStore#determineSRID(java.lang.String, java.lang.String)
     */
    protected int determineSRID(String tableName, String geometryColumnName) throws IOException {
        Connection conn = null;        
	 try {
            String sqlStatement = "SELECT SRID FROM MDSYS.ALL_SDO_GEOM_METADATA "
                + "WHERE TABLE_NAME='" + tableName + "' AND COLUMN_NAME='"
                + geometryColumnName + "'";
            String schema = config.getDatabaseSchemaName();
            if(schema != null && !"".equals(schema)) {
                sqlStatement += " and OWNER = '" + schema + "'";
            }
            conn = getConnection(Transaction.AUTO_COMMIT);
            LOGGER.finer("the sql statement for srid is " + sqlStatement);
            Statement statement = conn.createStatement();
            ResultSet result = statement.executeQuery(sqlStatement);

            if (result.next()) {
                int retSrid = result.getInt("srid");
                JDBCUtils.close(statement);

                return retSrid;
            } else {
                String mesg = "No geometry column row for srid in table: "
                    + tableName + ", geometry column " + geometryColumnName +
                    ", be sure column is defined in USER_SDO_GEOM_METADATA";
                throw new DataSourceException(mesg);
            }
        } catch (SQLException sqle) {
            String message = sqle.getMessage();

            throw new DataSourceException(message, sqle);
        } finally {
            JDBCUtils.close(conn, Transaction.AUTO_COMMIT, null);            
        }        
    }
    
    private OracleAuthorityFactory getOracleAuthorityFactory() {
        if (af == null) {
            af = new OracleAuthorityFactory(dataSource);
        }

        return af;
    }
    
    /**
     * @see org.geotools.data.jdbc.JDBCDataStore#getSqlBuilder(java.lang.String)
     */
    public SQLBuilder getSqlBuilder(String typeName) throws IOException {
    	FeatureTypeInfo info = typeHandler.getFeatureTypeInfo(typeName);
        SQLEncoder encoder = new SQLEncoderOracle(info.getSRIDs());
        encoder.setFIDMapper(getFIDMapper(typeName));
        return new DefaultSQLBuilder(encoder, info.getSchema(), null);
    }
    
    /**
     * @see org.geotools.data.jdbc.JDBCDataStore#getGeometryAttributeIO(org.geotools.feature.AttributeType, org.geotools.data.jdbc.QueryData)
     */
    protected AttributeIO getGeometryAttributeIO(AttributeType type, QueryData queryData) throws IOException {
	return new SDOAttributeIO(type, queryData);
    }
    
    /**
     * Returns a Oracle text based feature writer that just issues the sql
     * statements directly, as text.  Jody and Sean say things will go faster 
     * if we use updatable resultsets and all that jazz, but I can't get
     * those to work, and this does, so I'm going forth with it.
     *
     * @task TODO: Comment out this method and try out the default JDBC
     *             FeatureWriter - Jody thinks it will go faster.  It will
     *             need to be debugged, however, as it would not work.
     */ 
    protected JDBCFeatureWriter createFeatureWriter(FeatureReader fReader,
        QueryData queryData) throws IOException {
        return new OracleFeatureWriter(fReader, queryData);
    }
    
    /**
     * Retrieve approx bounds of all Features.
     * <p>
     * This result is suitable for a quick map display, illustrating the data.
     * This value is often stored as metadata in databases such as oraclespatial.
     * </p>
     * @return null as a generic implementation is not provided.
     */

    public Envelope getEnvelope(String typeName) {
        try {
            return bounds(new DefaultQuery(typeName));
        } catch(IOException e) {
            LOGGER.log(Level.WARNING, "Could not compute feature type bounds", e);
            return null;
        }
    }
    
    /** This is used by helper classes to hammer sql back to the database */
    public boolean sql( Transaction t, String sql ) throws IOException, SQLException  {
    	Connection conn = getConnection( t );
    	Statement st = conn.createStatement();
    	LOGGER.info( sql );
    	return st.execute( sql );    	
    }
    
    public void createSchema(FeatureType featureType) throws IOException {
    	String tableName = featureType.getTypeName();
    	Transaction t = new DefaultTransaction("createSchema");
    	
    	//CoordinateReferenceSystem crs = featureType.getDefaultGeometry().getCoordinateSystem();
    	// TODO: lookup srid for crs
    	Envelope bounds = new Envelope();
		bounds.expandToInclude( -180, -90 );
		bounds.expandToInclude( 180, 90 );
		int srid = -1;
		
    	SQLEncoderOracle encoder = new SQLEncoderOracle("fid",srid); // should figure out from encoding
    	SqlStatementEncoder statements = new SqlStatementEncoder( encoder, tableName, "fid" );
    	
    	try {
			sql( t, "DROP TABLE "+tableName );
			sql( t, "DELETE FROM user_sdo_geom_metadata WHERE TABLE_NAME='"+tableName+"'");			
    	}
    	catch( SQLException ignore ){
    		// table probably did not exist
    	}
    	try{
    		sql( t, statements.makeCreateTableSQL( featureType ) );
    		
  
    		sql( t, statements.makeAddGeomMetadata( featureType, bounds, srid ) );
    		//sql( t, statements.makeCreateFidIndex() );
    		//sql( t, statements.makeCreateGeomIndex( featureType ) );
    		
    		t.commit();
    		
    	} catch (SQLException e) {
    		t.rollback();
    		throw (IOException) new IOException( e.getLocalizedMessage() ).initCause( e );
		}    	    	
    	finally {
    		t.close();
    	}
    }
    
    /**
     * Default implementation based on getFeatureReader and getFeatureWriter.
     *
     * <p>
     * We should be able to optimize this to only get the RowSet once
     * </p>
     *
     * @see org.geotools.data.DataStore#getFeatureSource(java.lang.String)
     */
    public FeatureSource getFeatureSource(String typeName) throws IOException {
        if (!typeHandler.getFIDMapper(typeName).isVolatile()
                || allowWriteOnVolatileFIDs) {
            if (getLockingManager() != null) {
                // Use default JDBCFeatureLocking that delegates all locking
                // the getLockingManager
                //
                return new OracleFeatureLocking(this, getSchema(typeName));
            } else {
                // subclass should provide a FeatureLocking implementation
                // but for now we will simply forgo all locking
                return new OracleFeatureStore(this, getSchema(typeName));
            }
        } else {
            return new OracleFeatureSource(this, getSchema(typeName));
        }
    }
    
    /**
     * This is (unfortunately) a copy and paste from PostgisFeatureStore, I simply did not
     * know a better place to put this...
     * @param query
     * @return
     * @throws IOException
     */
    protected ReferencedEnvelope bounds(Query query) throws IOException {
        Filter filter = query.getFilter();

        if (filter == Filter.EXCLUDE) {
            return new ReferencedEnvelope(new Envelope(), query.getCoordinateSystem());
        }

        FeatureType schema = getSchema(query.getTypeName());
        SQLBuilder sqlBuilder = getSqlBuilder(schema.getTypeName());

        Filter postQueryFilter = sqlBuilder.getPostQueryFilter(query.getFilter());
        if (postQueryFilter != null && !postQueryFilter.equals(Filter.INCLUDE)) {
            // this would require postprocessing the filter
            // so we cannot optimize
            return null;
        }

        Connection conn = null;
        try {
            conn = getConnection(Transaction.AUTO_COMMIT);

            Envelope retEnv = new Envelope();
            Filter preFilter = sqlBuilder.getPreQueryFilter(query.getFilter());
            AttributeType[] attributeTypes = schema.getAttributeTypes();
            FeatureType schemaNew = schema;
            if (!query.retrieveAllProperties()) {
                try {
                    schemaNew = DataUtilities.createSubType(schema, query.getPropertyNames());
                    if (schemaNew.getDefaultGeometry() == null) // does the sub-schema have a
                                                                // geometry in it?
                    {
                        // uh-oh better get one!
                        if (schema.getDefaultGeometry() != null) // does the entire schema have a
                                                                    // geometry in it?
                        {
                            // buff-up the sub-schema so it has the default geometry in it.
                            ArrayList al = new ArrayList(Arrays.asList(query.getPropertyNames()));
                            al.add(schema.getDefaultGeometry().getName());
                            schemaNew = DataUtilities.createSubType(schema, (String[]) al
                                    .toArray(new String[1]));
                        }
                    }
                } catch (SchemaException e1) {
                    throw new DataSourceException("Could not create subtype", e1);
                }
            }
            // at this point, the query should have a geometry in it.
            // BUT, if there's no geometry in the table, then the query will not (obviously) have a
            // geometry in it.

            attributeTypes = schemaNew.getAttributeTypes();

            for (int j = 0, n = schemaNew.getAttributeCount(); j < n; j++) {
                if (Geometry.class.isAssignableFrom(attributeTypes[j].getType())) // same as
                                                                                    // .isgeometry()
                                                                                    // - see new
                                                                                    // featuretype
                                                                                    // javadoc
                {
                    String attName = attributeTypes[j].getName();
                    Envelope curEnv = getEnvelope(conn, schemaNew, attName, sqlBuilder, filter);

                    if (curEnv == null) {
                        return null;
                    }

                    retEnv.expandToInclude(curEnv);
                }
            }

            LOGGER.finer("returning bounds " + retEnv);

            if ((schemaNew != null) && (schemaNew.getDefaultGeometry() != null))
                return new ReferencedEnvelope(retEnv, schemaNew.getDefaultGeometry()
                        .getCoordinateSystem());
            if (query.getCoordinateSystem() != null)
                return new ReferencedEnvelope(retEnv, query.getCoordinateSystem());
            return new ReferencedEnvelope(retEnv, null);
        } catch (SQLException sqlException) {
            JDBCUtils.close(conn, Transaction.AUTO_COMMIT, sqlException);
            conn = null;
            throw new DataSourceException("Could not count " + query.getHandle(), sqlException);
        } catch (SQLEncoderException e) {
            // could not encode count
            // but at least we did not break the connection
            return null;
        } catch (ParseException parseE) {
            String message = "Could not read geometry: " + parseE.getMessage();

            return null;
        } finally {
            JDBCUtils.close(conn, Transaction.AUTO_COMMIT, null);
        }
    }
    
    protected Envelope getEnvelope(
        Connection conn,
        FeatureType schema,
        String geomName,
        SQLBuilder sqlBuilder,
        Filter filter)
        throws SQLException, SQLEncoderException, IOException, ParseException {
        
        StringBuffer sql = new StringBuffer();
        GeometryAttributeType gat = (GeometryAttributeType) schema.getAttributeType(geomName);
        // from the Oracle docs: "The SDO_TUNE.EXTENT_OF function has better performance than the
        // SDO_AGGR_MBR function if the data is non-geodetic and if a spatial index is defined
        // on the geometry column; however, the SDO_TUNE.EXTENT_OF function is limited to
        // two-dimensional geometries, whereas the SDO_AGGR_MBR function is not".
        // And also: "In addition, the SDO_TUNE.EXTENT_OF function computes the extent for all
        // geometries in a table; by contrast, the SDO_AGGR_MBR function can operate on
        // subsets of rows. The SDO_TUNE.EXTENT_OF function returns NULL if the data is inconsistent."
        // Long story short: under restrictive conditions SDO_TUNE.EXTENT_OF works, but we have
        // to be prepared to fall back on SDO_AGGR_MBR.
        List queries = new ArrayList();
        if(Filter.INCLUDE.equals(filter) && !(gat.getCoordinateSystem() instanceof GeodeticCRS)) {
            sql.append("SELECT SDO_TUNE.EXTENT_OF('").append(schema.getTypeName()).append("', '");
            sql.append(geomName).append("') from dual");
            queries.add(sql.toString());
            sql = new StringBuffer();
        } 
        sql.append("SELECT SDO_AGGR_MBR(").append(geomName).append(") ");
        sqlBuilder.sqlFrom(sql, schema.getTypeName());
        sqlBuilder.sqlWhere(sql, filter);
        queries.add(sql.toString());
      
        LOGGER.fine("SQL: " + sql);

        // loop over the (eventual) two sql statements, so that if the first does not provide
        // an answer, we fall back on the second
        Statement statement = null;
        ResultSet results = null;
        Envelope result = null;
        for (Iterator it = queries.iterator(); it.hasNext();) {
            String query = (String) it.next();
            try {
                statement = conn.createStatement();
                results = statement.executeQuery(query);
                
                results.next();
                
                Geometry geom = null;
                Object struct = results.getObject(1);
                UnWrapper unwrapper = DataSourceFinder.getUnWrapper(conn);
                OracleConnection oraConn = (OracleConnection) unwrapper.unwrap(conn);
                GeometryConverter converter = new GeometryConverter(oraConn, new GeometryFactory());
                geom = converter.asGeometry( (STRUCT) struct );
                
                // Oracle may return a point, a line or a polygon
                if(geom != null)
                    return geom.getEnvelopeInternal();
            } finally {
                JDBCUtils.close(results);
                JDBCUtils.close(statement);
            }
        }
        return result;
    }   
}
