package com.ximple.eofms.jobs.context.postgis;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.Map;
import java.util.HashMap;
import java.util.Set;
import java.util.ArrayList;
import java.math.BigDecimal;

import org.geotools.data.DataStore;
import org.geotools.data.Transaction;
import org.geotools.data.SchemaNotFoundException;
import org.geotools.data.DataSourceException;
import org.geotools.data.jdbc.JDBCUtils;
import org.geotools.data.postgis.PostgisDataStore;
import org.geotools.feature.FeatureType;
import org.geotools.feature.AttributeType;
import org.geotools.feature.GeometryAttributeType;
import org.geotools.feature.Feature;
import org.geotools.referencing.crs.DefaultGeographicCRS;
import org.geotools.referencing.NamedIdentifier;
import org.geotools.filter.LengthFunction;
import org.opengis.referencing.crs.CoordinateReferenceSystem;
import org.opengis.filter.Filter;
import org.opengis.filter.PropertyIsLessThan;
import org.opengis.filter.PropertyIsLessThanOrEqualTo;
import org.opengis.filter.BinaryComparisonOperator;
import org.opengis.filter.expression.Literal;

import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.geom.Point;
import com.vividsolutions.jts.geom.LineString;
import com.vividsolutions.jts.geom.Polygon;
import com.vividsolutions.jts.geom.MultiPoint;
import com.vividsolutions.jts.geom.MultiLineString;
import com.vividsolutions.jts.geom.MultiPolygon;
import com.vividsolutions.jts.geom.GeometryCollection;
import com.vividsolutions.jts.io.WKTWriter;
import com.vividsolutions.jts.io.WKBWriter;

import com.ximple.eofms.jobs.context.AbstractOracleJobContext;

public abstract class AbstractOracleToPostGISJobContext extends AbstractOracleJobContext
{
    private static Map<String, Class> GEOM_TYPE_MAP = new HashMap<String, Class>();

    static
    {
        GEOM_TYPE_MAP.put("GEOMETRY", Geometry.class);
        GEOM_TYPE_MAP.put("POINT", Point.class);
        GEOM_TYPE_MAP.put("POINTM", Point.class);
        GEOM_TYPE_MAP.put("LINESTRING", LineString.class);
        GEOM_TYPE_MAP.put("LINESTRINGM", LineString.class);
        GEOM_TYPE_MAP.put("POLYGON", Polygon.class);
        GEOM_TYPE_MAP.put("POLYGONM", Polygon.class);
        GEOM_TYPE_MAP.put("MULTIPOINT", MultiPoint.class);
        GEOM_TYPE_MAP.put("MULTIPOINTM", MultiPoint.class);
        GEOM_TYPE_MAP.put("MULTILINESTRING", MultiLineString.class);
        GEOM_TYPE_MAP.put("MULTILINESTRINGM", MultiLineString.class);
        GEOM_TYPE_MAP.put("MULTIPOLYGON", MultiPolygon.class);
        GEOM_TYPE_MAP.put("MULTIPOLYGONM", MultiPolygon.class);
        GEOM_TYPE_MAP.put("GEOMETRYCOLLECTION", GeometryCollection.class);
        GEOM_TYPE_MAP.put("GEOMETRYCOLLECTIONM", GeometryCollection.class);
    }

    private static Map<Class, String> CLASS_MAPPINGS = new HashMap<Class, String>();

    static
    {
        CLASS_MAPPINGS.put(String.class, "VARCHAR");

        CLASS_MAPPINGS.put(Boolean.class, "BOOLEAN");

        CLASS_MAPPINGS.put(Short.class, "SMALLINT");
        CLASS_MAPPINGS.put(Integer.class, "INTEGER");
        CLASS_MAPPINGS.put(Long.class, "BIGINT");

        CLASS_MAPPINGS.put(Float.class, "REAL");
        CLASS_MAPPINGS.put(Double.class, "DOUBLE PRECISION");

        CLASS_MAPPINGS.put(BigDecimal.class, "DECIMAL");

        CLASS_MAPPINGS.put(java.sql.Date.class, "DATE");
        CLASS_MAPPINGS.put(java.util.Date.class, "DATE");
        CLASS_MAPPINGS.put(java.sql.Time.class, "TIME");
        CLASS_MAPPINGS.put(java.sql.Timestamp.class, "TIMESTAMP");
    }

    private static Map<Class, String> GEOM_CLASS_MAPPINGS = new HashMap<Class, String>();

    //why don't we just stick this in with the non-geom class mappings?
    static
    {
        // init the inverse map
        Set keys = GEOM_TYPE_MAP.keySet();

        for (Object key : keys)
        {
            String name = (String) key;
            Class geomClass = GEOM_TYPE_MAP.get(name);
            GEOM_CLASS_MAPPINGS.put(geomClass, name);
        }
    }

    /**
     * Maximum string size for postgres
     */
    private static final int MAX_ALLOWED_VALUE = 10485760;

    protected static final int BATCHSIZE = 25;

    /**
     * Well Known Text writer (from JTS).
     */
    protected static WKTWriter geometryWriter = new WKTWriter();

    protected PostgisDataStore targetDataStore;

    private Connection connection;
    protected boolean schemaEnabled = true;

    public AbstractOracleToPostGISJobContext(String dataPath, DataStore targetDataStore)
    {
        if ((targetDataStore != null) && (targetDataStore instanceof PostgisDataStore))
        {
            this.targetDataStore = (PostgisDataStore) targetDataStore;
        } else
        {
            getLogger().info("targetDataStore has wrong.");
        }
        setDataPath(dataPath);
    }

    public PostgisDataStore getTargetDataStore()
    {
        return targetDataStore;
    }

    public void setTargetDataStore(PostgisDataStore targetDataStore)
    {
        this.targetDataStore = targetDataStore;
    }

    public Connection getConnection()
    {
        if (connection != null) return connection;

        if (targetDataStore != null)
        {
            try
            {
                connection = targetDataStore.getDataSource().getConnection();
            } catch (SQLException e)
            {
                getLogger().warn(e.getMessage(), e);
                return null;
            }
        }
        return connection;
    }

    public void closeConnection()
    {
        if (connection == null)
        {
            JDBCUtils.close(connection, Transaction.AUTO_COMMIT, null);
            connection = null;
        }
    }

    protected boolean isExistFeature(FeatureType featureType)
    {
        try
        {
            FeatureType existFeatureType = targetDataStore.getSchema(featureType.getTypeName());
            return existFeatureType != null; // && existFeatureType.equals(featureType);
        } catch (SchemaNotFoundException e)
        {
            return false;
        } catch (IOException e)
        {
            getLogger().info(e.getMessage(), e);
            return false;
        }
    }

    protected void deleteTable(Connection conn, String tableName) throws SQLException
    {
        Statement stmt = conn.createStatement();
        StringBuilder sb = new StringBuilder();
        sb.append("DELETE FROM \"");
        sb.append(targetDataStore.getDatabaseSchemaName());
        sb.append("\".\"");
        sb.append(tableName);
        sb.append('\"');
        getLogger().info("Execute-" + sb.toString());
        stmt.execute(sb.toString());
        stmt.close();
        conn.commit();
    }

    protected void dropTable(Connection conn, String tableName) throws SQLException
    {
        Statement stmt = conn.createStatement();
        StringBuilder sb = new StringBuilder();
        sb.append("DROP TABLE \"");
        sb.append(targetDataStore.getDatabaseSchemaName());
        sb.append("\".\"");
        sb.append(tableName);
        sb.append("\" CASCADE");
        getLogger().info("Execute-" + sb.toString());
        stmt.execute(sb.toString());
        stmt.close();
        conn.commit();
    }

    protected void dropGeometryColumn(Connection conn, String tableName, String geomField) throws SQLException
    {
        Statement stmt = conn.createStatement();
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT DropGeometryColumn('','");
        sb.append(tableName);
        sb.append("','");
        sb.append(geomField);
        sb.append("')");
        getLogger().info("Execute-" + sb.toString());
        stmt.execute(sb.toString());
        stmt.close();
        conn.commit();
    }

    public ArrayList<String> createSchemaTexts(FeatureType featureType) throws IOException
    {
        String tableName = featureType.getTypeName();

        // String lcTableName = tableName.toLowerCase();
        ArrayList<String> result = new ArrayList<String>();

        AttributeType[] attributeType = featureType.getAttributeTypes();
        String dbSchema = targetDataStore.getDatabaseSchemaName();

        Connection con = getConnection();

        boolean shouldExecute = !tablePresent(tableName, con);

        try
        {
            StringBuffer sql = new StringBuffer("CREATE TABLE ");
            sql.append(encodeSchemaTableName(tableName));
            sql.append(" (");
            sql.append(makeSqlCreate(attributeType));
            sql.append(");");

            String sqlStr = sql.toString();
            getLogger().info(sqlStr);

            if (shouldExecute)
            {
                result.add(sqlStr);
            }

            //fix from pr: it may be that table existed and then was dropped
            //without removing its geometry info from GEOMETRY_COLUMNS.
            //To support this, try to delete before inserting.
            //Preserving case for table names gives problems,
            //so convert to lower case

            sql = new StringBuffer("DELETE FROM GEOMETRY_COLUMNS WHERE f_table_catalog=''");
            sql.append(" AND f_table_schema = '");
            sql.append(dbSchema);
            sql.append("'");
            sql.append("AND f_table_name = '");
            sql.append(tableName);
            sql.append("';");

            //prints statement for later reuse
            sqlStr = sql.toString();
            getLogger().info(sqlStr);

            if (shouldExecute)
            {
                result.add(sqlStr);
            }

            //Ok, so Paolo Rizzi suggested that we get rid of our hand-adding
            //of geometry column information and use AddGeometryColumn instead
            //as it is better (this is in GEOT-379, he attached an extended
            //datastore that does postgis fixes).  But I am pretty positive
            //the reason we are doing things this way is to preserve the order
            //of FeatureTypes.  I know this is fairly silly, from most
            //information perspectives, but from another perspective it seems
            //to make sense - if you were transfering a featureType from one
            //data store to another then it should have the same order, right?
            //And order is important in WFS.  There are a few caveats though
            //for one I don't even know if things work right.  I imagine the
            //proper constraints that a AddGeometryColumn operation does are
            //not set in our hand version, for one.  I would feel better about
            //ignoring the order and just doing things as we like if we had
            //views in place, if users could add the schema, and then be able
            //to get it back in exactly the order they wanted.  So for now
            //let's leave things as is, and maybe talk about it in an irc. -ch
            for (AttributeType anAttributeType : attributeType)
            {
                if (!(anAttributeType instanceof GeometryAttributeType))
                {
                    continue;
                }
                GeometryAttributeType geomAttribute = (GeometryAttributeType) anAttributeType;
                String columnName = anAttributeType.getLocalName();

                CoordinateReferenceSystem refSys = geomAttribute
                        .getCoordinateSystem();
                int SRID;

                if (refSys != null)
                {
                    try
                    {
                        Set ident = refSys.getIdentifiers();
                        if ((ident == null || ident.isEmpty()) && refSys == DefaultGeographicCRS.WGS84)
                        {
                            SRID = 4326;
                        } else
                        {
                            String code = ((NamedIdentifier) ident.toArray()[0]).getCode();
                            SRID = Integer.parseInt(code);
                        }
                    } catch (Exception e)
                    {
                        getLogger().warn("SRID could not be determined");
                        SRID = -1;
                    }
                } else
                {
                    SRID = -1;
                }

                String typeName;

                //this construct seems unnecessary, since we already would
                //pass over if this wasn't a geometry...
                Class type = geomAttribute.getType();

                if (geomAttribute instanceof GeometryAttributeType)
                {
                    typeName = getGeometrySQLTypeName(type);
                } else
                {
                    typeName = CLASS_MAPPINGS.get(type);
                }

                if (typeName != null)
                {
                    //add a row to the geometry_columns table
                    sql = new StringBuffer("INSERT INTO GEOMETRY_COLUMNS VALUES (");
                    sql.append("'','");
                    sql.append(dbSchema);
                    sql.append("','");
                    sql.append(tableName);
                    sql.append("','");
                    sql.append(columnName);
                    sql.append("',2,");
                    sql.append(SRID);
                    sql.append(",'");
                    sql.append(typeName);
                    sql.append("');");

                    sqlStr = sql.toString();
                    getLogger().info(sqlStr);

                    if (shouldExecute)
                    {
                        result.add(sqlStr);
                    }

                    //add geometry constaints to the table
                    if (SRID > -1)
                    {
                        sql = new StringBuffer("ALTER TABLE ");
                        sql.append(encodeSchemaTableName(tableName));
                        sql.append(" ADD CONSTRAINT enforce_srid_");
                        sql.append(columnName);
                        sql.append(" CHECK (SRID(");
                        sql.append(encodeSchemaColumnName(columnName));
                        sql.append(") = ");
                        sql.append(SRID);
                        sql.append(");");
                        sqlStr = sql.toString();
                        getLogger().info(sqlStr);
                        if (shouldExecute)
                        {
                            result.add(sqlStr);
                        }
                    }

                    sql = new StringBuffer("ALTER TABLE ");
                    sql.append(encodeSchemaTableName(tableName));
                    sql.append(" ADD CONSTRAINT enforce_dims_");
                    sql.append(columnName);
                    sql.append(" CHECK (ndims(");
                    sql.append(encodeSchemaColumnName(columnName));
                    sql.append(") = 2);");
                    sqlStr = sql.toString();
                    getLogger().info(sqlStr);
                    if (shouldExecute)
                    {
                        result.add(sqlStr);
                    }

                    if (!typeName.equals("GEOMETRY"))
                    {
                        sql = new StringBuffer("ALTER TABLE ");
                        sql.append(encodeSchemaTableName(tableName));
                        sql.append(" ADD CONSTRAINT enforce_geotype_");
                        sql.append(columnName);
                        sql.append(" CHECK (geometrytype(");
                        sql.append(encodeSchemaColumnName(columnName));
                        sql.append(") = '");
                        sql.append(typeName);
                        sql.append("'::text OR ");
                        sql.append(encodeSchemaColumnName(columnName));
                        sql.append(" IS NULL);");
                        sqlStr = sql.toString();
                        getLogger().info(sqlStr);
                        if (shouldExecute)
                        {
                            result.add(sqlStr);
                        }
                    }

                } else
                {
                    getLogger().warn("Error: " + geomAttribute.getLocalName() + " unknown type!!!");
                }

                String indexName = tableName.replace('-', '_');
                //also build a spatial index on each geometry column.
                sql = new StringBuffer("CREATE INDEX spatial_");
                sql.append(indexName);
                sql.append("_");
                sql.append(anAttributeType.getLocalName().toLowerCase());
                sql.append(" ON ");
                sql.append(encodeSchemaTableName(tableName));
                sql.append(" USING GIST (");
                sql.append(encodeSchemaColumnName(anAttributeType.getLocalName()));
                sql.append(");");

                sqlStr = sql.toString();
                getLogger().info(sqlStr);

                if (shouldExecute)
                {
                    result.add(sqlStr);
                }
            }

            con.commit();

        } catch (SQLException e)
        {
            try
            {
                if (con != null)
                {
                    con.rollback();
                }
            } catch (SQLException sqle)
            {
                throw new IOException(sqle.getMessage());
            }

            throw (IOException) new IOException(e.getMessage()).initCause(e);
        } finally
        {
        }

        if (!shouldExecute)
        {
            throw new IOException("The table " + tableName + " already exists.");
        }

        return result;
    }

    private boolean tablePresent(String table, Connection conn) throws IOException
    {
        final int TABLE_NAME_COL = 3;

        try
        {
            conn = getConnection();

            DatabaseMetaData meta = conn.getMetaData();
            String[] tableType = {"TABLE"};
            ResultSet tables = meta.getTables(null,
                    targetDataStore.getDatabaseSchemaName(), "%", tableType);

            while (tables.next())
            {
                String tableName = tables.getString(TABLE_NAME_COL);

                if (allowTable(tableName) && (tableName != null)
                        && (tableName.equalsIgnoreCase(table)))
                {
                    return (true);
                }
            }

            return false;
        } catch (SQLException sqlException)
        {
            // JDBCUtils.close(conn, Transaction.AUTO_COMMIT, sqlException);
            // conn = null;

            String message = "Error querying database for list of tables:"
                    + sqlException.getMessage();
            throw new DataSourceException(message, sqlException);
        } finally
        {
            // JDBCUtils.close(conn, Transaction.AUTO_COMMIT, null);
        }
    }

    protected boolean allowTable(String tablename)
    {
        if (tablename.equals("geometry_columns"))
        {
            return false;
        } else if (tablename.startsWith("spatial_ref_sys"))
        {
            return false;
        }

        //others?
        return true;
    }


    private StringBuffer makeSqlCreate(AttributeType[] attributeType)
            throws IOException
    {
        StringBuffer buf = new StringBuffer("");

        for (AttributeType anAttributeType : attributeType)
        {
            String typeName;
            typeName = CLASS_MAPPINGS.get(anAttributeType.getBinding());
            if (typeName == null)
                typeName = GEOM_CLASS_MAPPINGS.get(anAttributeType.getBinding());

            if (typeName != null)
            {
                if (anAttributeType instanceof GeometryAttributeType)
                {
                    typeName = "GEOMETRY";
                } else if (typeName.equals("VARCHAR"))
                {
                    int length = -1;
                    Filter f = anAttributeType.getRestriction();
                    if (f != null && f != Filter.EXCLUDE && f != Filter.INCLUDE &&
                            (f instanceof PropertyIsLessThan || f instanceof PropertyIsLessThanOrEqualTo))
                    {
                        try
                        {
                            BinaryComparisonOperator cf = (BinaryComparisonOperator) f;
                            if (cf.getExpression1() instanceof LengthFunction)
                            {
                                length = Integer.parseInt(((Literal) cf.getExpression2()).getValue().toString());
                            } else
                            {
                                if (cf.getExpression2() instanceof LengthFunction)
                                {
                                    length = Integer.parseInt(((Literal) cf.getExpression1()).getValue().toString());
                                }
                            }
                        } catch (NumberFormatException e)
                        {
                            length = 256;
                        }
                    } else
                    {
                        length = 256;
                    }

                    if (length < 1)
                    {
                        getLogger().warn("FeatureType did not specify string length; defaulted to 256");
                        length = 256;
                    } else if (length > MAX_ALLOWED_VALUE)
                    {
                        length = MAX_ALLOWED_VALUE;
                    }
                    typeName = typeName + "(" + length + ")";
                }

                if (!anAttributeType.isNillable())
                {
                    typeName = typeName + " NOT NULL";
                }

                //TODO review!!! Is toString() always OK???
                Object defaultValue = anAttributeType.createDefaultValue();

                if (defaultValue != null)
                {
                    typeName = typeName + " DEFAULT '"
                            + defaultValue.toString() + "'";
                }

                buf.append(" \"").append(anAttributeType.getLocalName()).append("\" ").append(typeName).append(",");

            } else
            {
                String msg;
                if (anAttributeType == null)
                {
                    msg = "AttributeType was null!";
                } else
                {
                    msg = "Type '" + anAttributeType.getBinding() + "' not supported!";
                }
                throw (new IOException(msg));
            }
        }

        return buf.deleteCharAt(buf.length() - 1);
    }

    private String getGeometrySQLTypeName(Class type)
    {
        String res = GEOM_CLASS_MAPPINGS.get(type);

        if (res == null)
        {
            throw new RuntimeException("Unknown type name for class " + type
                    + " please update GEOMETRY_MAPPINGS");
        }

        return res;
    }

    protected String getGeometryInsertText(Geometry geom, int srid) // throws IOException
    {
        if (geom == null)
        {
            return "null";
        }

        if (targetDataStore.isWKBEnabled())
        {
            //String wkb = WKBEncoder.encodeGeometryHex(geom);
            String wkb = WKBWriter.bytesToHex(new WKBWriter().write(geom));

            if (targetDataStore.isByteaWKB())
            {
                return "setSRID('" + wkb + "'::geometry," + srid + ")";
            } else
            {
                return "GeomFromWKB('" + wkb + "', " + srid + ")";
            }
        }

        String geoText = geometryWriter.write(geom);

        return "GeometryFromText('" + geoText + "', " + srid + ")";
    }

    protected String makeInsertSql(Feature feature, int srid) // throws IOException
    {
        FeatureType featureType = feature.getFeatureType();

        String tableName = encodeSchemaTableName(featureType.getTypeName());
        AttributeType[] attributeTypes = featureType.getAttributeTypes();

        String attrValue;

        StringBuilder statementSQL = new StringBuilder(512);
        statementSQL.append("INSERT INTO ").append(tableName).append(" (");

        // encode insertion for attributes, but remember to avoid auto-increment ones,
        // they may be included in the feature type as well
        for (AttributeType attributeType : attributeTypes)
        {
            String attName = attributeType.getLocalName();

            if (feature.getAttribute(attName) != null)
            {
                String colName = encodeSchemaColumnName(attName);
                statementSQL.append(colName).append(",");
            }
        }

        statementSQL.setCharAt(statementSQL.length() - 1, ')');
        statementSQL.append(" VALUES (");

        Object[] attributes = feature.getAttributes(null);

        for (int i = 0; i < attributeTypes.length; i++)
        {
            attrValue = null;

            if (attributeTypes[i] instanceof GeometryAttributeType)
            {
                // String geomName = attributeTypes[i].getLocalName();
                // int srid = ftInfo.getSRID(geomName);
                Geometry geometry = (Geometry) attributes[i];

                if (geometry == null)
                {
                    attrValue = "NULL";
                } else
                {
                    attrValue = getGeometryInsertText(geometry, srid);
                }
            } else
            {
                if (attributes[i] != null)
                {
                    attrValue = addQuotes(attributes[i]);
                }
            }

            if (attrValue != null)
            {
                statementSQL.append(attrValue).append(",");
            }
        }

        statementSQL.setCharAt(statementSQL.length() - 1, ')');

        return (statementSQL.toString());
    }

    protected String addQuotes(Object value)
    {
        String retString;

        if (value != null)
        {
            if (value instanceof Number)
            {
                retString = value.toString();
            } else
            {
                retString = "'" + doubleQuote(value) + "'";
            }
        } else
        {
            retString = "null";
        }

        return retString;
    }

    String doubleQuote(Object obj)
    {
        return obj.toString().replaceAll("'", "''");
    }

    protected String encodeName(String tableName)
    {
        return tableName;
    }

    protected String encodeColumnName(String colName)
    {
        return encodeName(colName);
    }

    public String encodeSchemaTableName(String tableName) {
        return schemaEnabled ? ("\"" + targetDataStore.getDatabaseSchemaName() + "\".\"" + tableName + "\"")
                             : ("\"" + tableName + "\"");
    }

    public String encodeSchemaColumnName(String columnName) {
        return "\"" + columnName + "\"";
    }
}