/*
 *    Geotools2 - OpenSource mapping toolkit
 *    http://geotools.org
 *    (C) 2002, 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;
 *    version 2.1 of the License.
 *
 *    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.
 *
 */
package org.geotools.data.geometryless;

import java.io.IOException;
import java.net.URI;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.sql.DataSource;

import net.sf.jsqlparser.statement.select.SelectBody;

import org.geotools.data.DataSourceException;
import org.geotools.data.FeatureReader;
import org.geotools.data.FeatureWriter;
import org.geotools.data.SchemaNotFoundException;
import org.geotools.data.Transaction;
import org.geotools.data.jdbc.ConnectionPool;
import org.geotools.data.jdbc.FeatureTypeHandler;
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.attributeio.WKTAttributeIO;
import org.geotools.data.jdbc.fidmapper.FIDMapper;
import org.geotools.data.jdbc.fidmapper.NullFIDMapper;
import org.geotools.feature.AttributeType;
import org.geotools.feature.FeatureType;
import org.geotools.feature.FeatureTypeFactory;
import org.geotools.filter.UnaliasSQLEncoder;
import org.opengis.filter.Filter;
// import org.geotools.filter.SQLEncoder;
// import org.geotools.data.geometryless.filter.GeometrylessSQLEncoder;
import org.geotools.data.jdbc.FilterToSQL;
import org.geotools.data.sql.BypassSqlFeatureTypeHandler;
import org.geotools.data.sql.RsMd2DbMdResultSet;
import org.geotools.data.sql.SqlDataStore;

/**
 * An implementation of the GeoTools Data Store API for a generic non-spatial
 * database platform. The plan is to support traditional jdbc datatypes, and
 * support geometry held within such types (eg, x,y columns, or possibly WKT
 * strings)<br>
 * <br>
 * Please see {@link org.geotools.data.jdbc.JDBCDataStore class JDBCDataStore}
 * and {@link org.geotools.data.DataStore interface DataStore} for DataStore
 * usage details.
 * 
 * @author Rob Atkinson rob@socialchange.net.au
 * @source $URL:
 *         http://svn.geotools.org/geotools/trunk/gt/modules/unsupported/geometryless/src/main/java/org/geotools/data/geometryless/JDBCDataStore.java $
 */

public class JDBCDataStore extends org.geotools.data.jdbc.JDBCDataStore implements SqlDataStore {
    /** The logger for the mysql module. */
    private static final Logger LOGGER = org.geotools.util.logging.Logging.getLogger("org.geotools.data.geometryless");

    /**
     * Basic constructor for JDBCDataStore. Requires creation of a
     * {@link org.geotools.data.jdbc.ConnectionPool ConnectionPool}, which
     * could be done similar to the following:<br>
     * <br>
     * <code>MySQLConnectionFactory connectionFactory = new MySQLConnectionFactory("mysqldb.geotools.org", "3306", "myCoolSchema");</code><br>
     * <code>ConnectionPool connectionPool = connectionFactory.getConnectionPool("omcnoleg", "myTrickyPassword123");</code><br>
     * <code>DataStore dataStore = new JDBCDataStore(connectionPool);</code><br>
     * 
     * @param connectionPool
     *            a MySQL
     *            {@link org.geotools.data.jdbc.ConnectionPool ConnectionPool}
     * @throws IOException
     *             if the database cannot be properly accessed
     * @see org.geotools.data.jdbc.ConnectionPool
     * @see org.geotools.data.mysql.MySQLConnectionFactory
     */
    public JDBCDataStore(DataSource connectionPool) throws IOException {
        super(connectionPool, new JDBCDataStoreConfig());
    }

    /** <code>DEFAULT_NAMESPACE</code> field */
    public static String DEFAULT_NAMESPACE = "http://geotools.org/jdbc";

    /**
     * Constructor for JDBCDataStore where the database schema name is provided.
     * 
     * @param connectionPool
     *            a MySQL
     *            {@link org.geotools.data.jdbc.ConnectionPool ConnectionPool}
     * @param databaseSchemaName
     *            the database schema. Can be null. See the comments for the
     *            parameter schemaPattern in
     *            {@link java.sql.DatabaseMetaData#getTables(String, String, String, String[]) DatabaseMetaData.getTables},
     *            because databaseSchemaName behaves in the same way.
     * @throws IOException
     *             if the database cannot be properly accessed
     */
    public JDBCDataStore(DataSource connectionPool, String databaseSchemaName)
            throws IOException {
        this(connectionPool, databaseSchemaName, DEFAULT_NAMESPACE);
    }

    /**
     * Constructor for JDBCDataStore where the database schema name is provided.
     * 
     * @param connectionPool
     *            a MySQL
     *            {@link org.geotools.data.jdbc.ConnectionPool ConnectionPool}
     * @param databaseSchemaName
     *            the database schema. Can be null. See the comments for the
     *            parameter schemaPattern in
     *            {@link java.sql.DatabaseMetaData#getTables(String, String, String, String[]) DatabaseMetaData.getTables},
     *            because databaseSchemaName behaves in the same way.
     * @param namespace
     *            the namespace for this data store. Can be null, in which case
     *            the namespace will simply be the schema name.
     * @throws IOException
     *             if the database cannot be properly accessed
     */
    public JDBCDataStore(DataSource connectionPool, String databaseSchemaName, String namespace)
            throws IOException {
        super(connectionPool, JDBCDataStoreConfig.createWithNameSpaceAndSchemaName(namespace,
                databaseSchemaName));
    }

    /**
     * A utility method for creating a JDBCDataStore from database connection
     * parameters, using the default port (3306) for MySQL.
     * 
     * @param host
     *            the host name or IP address of the database server
     * @param schema
     *            the name of the database instance
     * @param username
     *            the database username
     * @param password
     *            the password corresponding to <code>username</code>
     * @return a JDBCDataStore for the specified parameters
     * 
     * public static JDBCDataStore getInstance( String host, String schema,
     * String username, String password) throws IOException, SQLException {
     * return getInstance(host, 3306, schema, username, password); }
     */
    /**
     * Utility method for creating a JDBCDataStore from database connection
     * parameters.
     * 
     * @param host
     *            the host name or IP address of the database server
     * @param port
     *            the port number of the database
     * @param schema
     *            the name of the database instance
     * @param username
     *            the database username
     * @param password
     *            the password corresponding to <code>username</code>
     * @throws IOException
     *             if the JDBCDataStore cannot be created because the database
     *             cannot be properly accessed
     * @throws SQLException
     *             if a MySQL connection pool cannot be established
     * 
     * public static JDBCDataStore getInstance( String host, int port, String
     * schema, String username, String password) throws IOException,
     * SQLException { return new JDBCDataStore( new MySQLConnectionFactory(host,
     * port, schema).getConnectionPool(username, password)); }
     */
    /**
     * Utility method for getting a FeatureWriter for modifying existing
     * features, using no feature filtering and auto-committing. Not used for
     * adding new features.
     * 
     * @param typeName
     *            the feature type name (the table name)
     * @return a FeatureWriter for modifying existing features
     * @throws IOException
     *             if the database cannot be properly accessed
     */
    public FeatureWriter getFeatureWriter(String typeName) throws IOException {
        return getFeatureWriter(typeName, Filter.INCLUDE, Transaction.AUTO_COMMIT);
    }

    /**
     * Utility method for getting a FeatureWriter for adding new features, using
     * auto-committing. Not used for modifying existing features.
     * 
     * @param typeName
     *            the feature type name (the table name)
     * @return a FeatureWriter for adding new features
     * @throws IOException
     *             if the database cannot be properly accessed
     */
    public FeatureWriter getFeatureWriterAppend(String typeName) throws IOException {
        return getFeatureWriterAppend(typeName, Transaction.AUTO_COMMIT);
    }

    /**
     * Constructs an AttributeType from a row in a ResultSet. The ResultSet
     * contains the information retrieved by a call to getColumns() on the
     * DatabaseMetaData object. This information can be used to construct an
     * Attribute Type.
     * 
     * <p>
     * In addition to standard SQL types, this method identifies MySQL 4.1's
     * geometric datatypes and creates attribute types accordingly. This happens
     * when the datatype, identified by column 5 of the ResultSet parameter, is
     * equal to java.sql.Types.OTHER. If a Types.OTHER ends up not being
     * geometric, this method simply calls the parent class's buildAttributeType
     * method to do something with it.
     * </p>
     * 
     * <p>
     * Note: Overriding methods must never move the current row pointer in the
     * result set.
     * </p>
     * 
     * @param rs
     *            The ResultSet containing the result of a
     *            DatabaseMetaData.getColumns call.
     * 
     * @return The AttributeType built from the ResultSet.
     * 
     * @throws SQLException
     *             If an error occurs processing the ResultSet.
     * @throws DataSourceException
     *             Provided for overriding classes to wrap exceptions caused by
     *             other operations they may perform to determine additional
     *             types. This will only be thrown by the default implementation
     *             if a type is present that is not present in the
     *             TYPE_MAPPINGS.
     */
    protected AttributeType buildAttributeType(ResultSet rs) throws IOException {
        final int COLUMN_NAME = 4;
        final int DATA_TYPE = 5;
        final int TYPE_NAME = 6;

        try {
            int dataType = rs.getInt(DATA_TYPE);
            LOGGER.fine("dataType: " + dataType + " " + rs.getString(TYPE_NAME) + " "
                    + rs.getString(COLUMN_NAME));

            if (dataType == Types.OTHER) {
                // this is MySQL-specific; handle it
                // String typeName = rs.getString(TYPE_NAME);
                // String typeNameLower = typeName.toLowerCase();
                return super.buildAttributeType(rs);
            } else {
                return super.buildAttributeType(rs);
            }
        } catch (SQLException e) {
            throw new IOException("SQL exception occurred: " + e.getMessage());
        }
    }

    /**
     * @see org.geotools.data.jdbc.JDBCDataStore#getGeometryAttributeIO(org.geotools.feature.AttributeType)
     */
    protected AttributeIO getGeometryAttributeIO(AttributeType type, QueryData queryData) {
        return new WKTAttributeIO();
    }

    protected JDBCFeatureWriter createFeatureWriter(FeatureReader reader, QueryData queryData)
            throws IOException {
        LOGGER.fine("returning jdbc feature writer");

        return new GeometrylessFeatureWriter(reader, queryData);
    }

    /*
     * public SQLBuilder getSqlBuilder2(String typeName) throws IOException {
     * FilterToSQL encoder = new FilterToSQL();
     * encoder.setFIDMapper(getFIDMapper(typeName)); return new
     * GeometrylessSQLBuilder(encoder); }
     */

    /**
     * Returns a SQLBuilder for the requested FeatureType.
     * <p>
     * If the requested FeatureType corresponds to an in-process view (a view
     * specified through {@linkplain #registerView(String, String)}, the
     * returned SQLBuilder takes care of it.
     * </p>
     */
    public SQLBuilder getSqlBuilder(String typeName) throws IOException {
        BypassSqlFeatureTypeHandler ftHandler = (BypassSqlFeatureTypeHandler) super.typeHandler;
        FilterToSQL encoder = new UnaliasSQLEncoder();
        FeatureType schema = getSchema( typeName );
		encoder.setFeatureType( schema );
        encoder.setFIDMapper(getFIDMapper(typeName));
        SQLBuilder sqlBuilder;
        /*
         * if (ftHandler.isView(typeName)) { sqlBuilder = new
         * GeometrylessSQLBuilder(encoder, ftHandler); } else {
         */
        sqlBuilder = new GeometrylessSQLBuilder(encoder);
        /*
         * }
         */
        return sqlBuilder;
    }

    // /////////////////////////////

    /**
     * Overrides <code>JDBC1DataStore.getFeatureTypeHandler</code> to return
     * an in-process view aware one, a {@linkplain BypassSqlFeatureTypeHandler}
     * 
     * @param config
     * @return a {@linkplain BypassSqlFeatureTypeHandler} that maintains a
     *         registry of user defined SQL queries exposed as read-only
     *         FeatureTypes
     */
    protected FeatureTypeHandler getFeatureTypeHandler(
            JDBCDataStoreConfig config) throws IOException {
        return new BypassSqlFeatureTypeHandler(this,
                buildFIDMapperFactory(config), config.getTypeHandlerTimeout());
    }
    
    /**
     * I'm adding this method right now just to get the class compiling. By now
     * it just delegates to the old {@link #registerView(String, String)} with
     * the <code>toString()</code> value of the select body. In the short
     * term, the delegation shuold be inversed so the datastore uses the object
     * model representing the query instead of parsing the string query "by
     * hand".
     */
    public void registerView(final String typeName, final SelectBody select) throws IOException {
        String sqlQuery = select.toString();
        registerView(typeName, sqlQuery);
    }

    /**
     * Creates an in-process data view against one or more actual FeatureTypes
     * of this DataStore, which will be advertised as <code>typeName</code>
     * 
     * @param typeName
     *            the name of the view's FeatureType.
     * @param sqlQuery
     *            a full SQL query which will act as the view definition.
     * @throws IOException
     * @throws IllegalArgumentException
     *             if <code>typeName</code> already exists as one of this
     *             datastore's feature types, regardless of type name case.
     */
    public void registerView(final String typeName, final String sqlQuery) throws IOException {
        if (typeName == null || sqlQuery == null) {
            throw new NullPointerException(typeName + "=" + sqlQuery);
        }
        String[] existingTypeNames = getTypeNames();
        for (int i = 0; i < existingTypeNames.length; i++) {
            if (typeName.equalsIgnoreCase(existingTypeNames[i])) {
                throw new IllegalArgumentException(typeName + " already exists: "
                        + existingTypeNames[i]);
            }
        }
        LOGGER.fine("registering view " + typeName + " as " + sqlQuery);
        Connection conn = getConnection(Transaction.AUTO_COMMIT);
        ResultSetMetaData rsmd;
        try {
            Statement st = conn.createStatement();
            st.setMaxRows(1);
            
            ResultSet rs = st.executeQuery(sqlQuery);
            rsmd = rs.getMetaData();

            // TODO: set a more appropiate fid mapper
            FIDMapper fidMapper = new NullFIDMapper();
            FeatureType viewType = buildSchema(typeName, rsmd);
            BypassSqlFeatureTypeHandler th = (BypassSqlFeatureTypeHandler) typeHandler;
            th.registerView(viewType, sqlQuery, fidMapper);
        } catch (SQLException e) {
            LOGGER.log(Level.WARNING, "executing query " + sqlQuery, e);
            throw new DataSourceException("executing " + sqlQuery, e);
        } finally {
            try {
                conn.close();
            } catch (Exception e) {
                LOGGER.warning(e.getMessage());
            }
        }
    }

    /**
     * Creates a FeatureType from a <code>java.sql.ResultSetMetaData</code>
     * object, obtained from the execution of a SQL query configured as the
     * source of an in-process view.
     * 
     * @param typeName
     * @param rsmd
     * @return
     * @throws IOException
     * @throws SQLException
     */
    private FeatureType buildSchema(String typeName, ResultSetMetaData rsmd) throws IOException,
            SQLException {
        FeatureType viewType;

        final int NAME_COLUMN = 4;
        final int TYPE_NAME = 6;

        ResultSet tableInfo = null;
        FIDMapper mapper = new NullFIDMapper();
        // the mapping from resultset medatada to the
        // resultset schema expected by JDBCDataStore
        tableInfo = new RsMd2DbMdResultSet(rsmd);

        try {
            List attributeDescriptors = new ArrayList();

            boolean tableInfoFound = false;

            while (tableInfo.next()) {
                tableInfoFound = true;
                try {
                    String columnName = tableInfo.getString(NAME_COLUMN);

                    if (!mapper.returnFIDColumnsAsAttributes()) {
                        boolean isPresent = false;

                        for (int i = 0; i < mapper.getColumnCount(); i++) {
                            if (columnName.equalsIgnoreCase(mapper.getColumnName(i))) {
                                isPresent = true;
                                break;
                            }
                        }
                        if (isPresent) {
                            continue;
                        }
                    }

                    // AttributeDescriptor attribute =
                    // buildAttributeDescriptor(tableInfo);
                    AttributeType attribute = buildAttributeType(tableInfo);

                    if (attribute != null) {
                        attributeDescriptors.add(attribute);
                    } else {
                        LOGGER.finest("Unknown SQL Type: (may be consumed) "
                                + tableInfo.getString(TYPE_NAME));
                    }
                } catch (DataSourceException dse) {
                    String msg = "Error building attribute type. The column will be ignored";
                    LOGGER.log(Level.WARNING, msg, dse);
                }
            }

            if (!tableInfoFound) {
                throw new SchemaNotFoundException(typeName);

            }

            URI namespace = getNameSpace();
            String ns = null;
            if (namespace != null) {
                ns = namespace.toString();
            }

            // AttributeName ftName = new AttributeName(ns, typeName);
            // viewType = (FeatureType)tf.createFeatureType(ftName, schema,
            // null);

            AttributeType[] types = (AttributeType[]) attributeDescriptors
                    .toArray(new AttributeType[0]);
            viewType = FeatureTypeFactory.newFeatureType(types, typeName, namespace, false, null,
                    null);

            return viewType;

        } catch (Exception sqlException) {
            throw new DataSourceException("SQL Error building FeatureType for " + typeName + " "
                    + sqlException.getMessage(), sqlException);
        } finally {
            JDBCUtils.close(tableInfo);
        }
    }

}
