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.sql.PreparedStatement;
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;
import com.ximple.eofms.util.postjts.JtsBinaryWriter;

public abstract class AbstractOracleToPostGISJobContext extends AbstractOracleJobContext
{
    private static Map<String, Class> GEOM_TYPE_MAP = new HashMap<String, Class>();
    private static Map<String, Class> GEOM3D_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("LINESTRING", LineString.class);
        GEOM_TYPE_MAP.put("POLYGON", Polygon.class);
        GEOM_TYPE_MAP.put("MULTIPOINT", MultiPoint.class);
        GEOM_TYPE_MAP.put("MULTILINESTRING", MultiLineString.class);
        GEOM_TYPE_MAP.put("MULTIPOLYGON", MultiPolygon.class);
        GEOM_TYPE_MAP.put("GEOMETRYCOLLECTION", GeometryCollection.class);

        GEOM3D_TYPE_MAP.put("POINTM", Point.class);
        GEOM3D_TYPE_MAP.put("LINESTRINGM", LineString.class);
        GEOM3D_TYPE_MAP.put("POLYGONM", Polygon.class);
        GEOM3D_TYPE_MAP.put("MULTIPOINTM", MultiPoint.class);
        GEOM3D_TYPE_MAP.put("MULTILINESTRINGM", MultiLineString.class);
        GEOM3D_TYPE_MAP.put("MULTIPOLYGONM", MultiPolygon.class);
        GEOM3D_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 = 256;

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

    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();
    }

    protected String dropGeometryColumn(String dbSchema, String tableName, String geomField)
    {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT DropGeometryColumn('");
        sb.append(dbSchema);
        sb.append("','");
        sb.append(tableName);
        sb.append("','");
        sb.append(geomField);
        sb.append("')");
        getLogger().info("Execute-" + sb.toString());
        return sb.toString();
    }

    private String addGeometryColumn(String dbSchema, String tableName, GeometryAttributeType geometryAttribute, int srid)
    {
        StringBuilder sql;
        String typeName = getGeometrySQLTypeName(geometryAttribute.getBinding());
        if (typeName == null)
        {
            getLogger().warn("Error: " + geometryAttribute.getLocalName() + " unknown type!!!");
            throw new RuntimeException("Error: " + geometryAttribute.getLocalName() + " unknown type!!!");
        }

        sql = new StringBuilder("SELECT AddGeometryColumn('");
        sql.append(dbSchema);
        sql.append("','");
        sql.append(tableName);
        sql.append("','");
        sql.append(geometryAttribute.getLocalName());
        sql.append("','");
        sql.append(srid);
        sql.append("','");
        sql.append(typeName);
        sql.append("', 2);");

        //prints statement for later reuse
        return sql.toString();
    }

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

        ArrayList<String> result = new ArrayList<String>();

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

        Connection con = getConnection();

        boolean shouldDrop = tablePresent(tableName, con);
        if (shouldDrop)
        {
            String sqlStr = "DROP TABLE " + encodeSchemaTableName(tableName) + ";";
            getLogger().info(sqlStr);
            result.add(sqlStr);
        }

        StringBuffer sql = new StringBuffer("CREATE TABLE ");
        sql.append(encodeSchemaTableName(tableName));
        sql.append(" ( gid serial PRIMARY KEY, ");
        sql.append(makeNonGeomSqlCreate(attributeType));
        sql.append(");");

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

        for (AttributeType anAttributeType : attributeType)
        {
            if (!(anAttributeType instanceof GeometryAttributeType))
            {
                continue;
            }
            GeometryAttributeType geomAttribute = (GeometryAttributeType) anAttributeType;

            if (shouldDrop)
            {
            sqlStr = dropGeometryColumn("", tableName, geomAttribute.getLocalName());
            getLogger().info(sqlStr);
            result.add(sqlStr);
            }

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

            sqlStr = addGeometryColumn("", tableName, geomAttribute, SRID);
            getLogger().info(sqlStr);
            result.add(sqlStr);


            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(" gist_geometry_ops);");

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

            result.add(sqlStr);
        }

        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 makeNonGeomSqlCreate(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) continue;
            }

            if (typeName != null)
            {
                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 makePrepareInsertSql(FeatureType featureType)
    {
        String tableName = encodeSchemaTableName(featureType.getTypeName());
        AttributeType[] attributeTypes = featureType.getAttributeTypes();

        String attrValue;

        StringBuffer statementSQL = new StringBuffer("INSERT INTO " + tableName + " (");

        // 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();

            String colName = encodeSchemaColumnName(attName);
            statementSQL.append(colName).append(",");
        }

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

        for (AttributeType attributeType : attributeTypes)
        {
            statementSQL.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 + "\"";
    }

    protected void bindFeatureParameters(PreparedStatement pstmt, Feature feature) throws SQLException
    {
        FeatureType featureType = feature.getFeatureType();

        AttributeType[] attributeTypes = featureType.getAttributeTypes();
        Object[] attributes = feature.getAttributes(null);

        for (int i = 0; i < attributeTypes.length; i++)
        {
            if (attributeTypes[i] instanceof GeometryAttributeType)
            {
                pstmt.setBytes(i + 1, binaryWriter.writeBinary((Geometry) attributes[i]));
            } else
            {
                if (attributeTypes[i].getBinding().equals(Short.class))
                {
                    pstmt.setShort(i + 1, (Short) attributes[i]);
                } else if (attributeTypes[i].getBinding().equals(Integer.class))
                {
                    pstmt.setInt(i + 1, (Short) attributes[i]);
                } else if (attributeTypes[i].getBinding().equals(Long.class))
                {
                    pstmt.setLong(i + 1, (Long) attributes[i]);
                } else if (attributeTypes[i].getBinding().equals(String.class))
                {
                    pstmt.setString(i + 1, (String) attributes[i]);
                } else if (attributeTypes[i].getBinding().equals(Float.class))
                {
                    pstmt.setFloat(i + 1, (Float) attributes[i]);
                } else if (attributeTypes[i].getBinding().equals(Double.class))
                {
                    pstmt.setDouble(i + 1, (Double) attributes[i]);
                } else if (attributeTypes[i].getBinding().equals(Boolean.class))
                {
                    pstmt.setBoolean(i + 1, (Boolean) attributes[i]);
                } else if (attributeTypes[i].getBinding().equals(BigDecimal.class))
                {
                    pstmt.setBigDecimal(i + 1, (BigDecimal) attributes[i]);
                } else if (attributeTypes[i].getBinding().equals(java.sql.Date.class))
                {
                    pstmt.setDate(i + 1, (java.sql.Date) attributes[i]);
                } else if (attributeTypes[i].getBinding().equals(java.sql.Time.class))
                {
                    pstmt.setTime(i + 1, (java.sql.Time) attributes[i]);
                } else if (attributeTypes[i].getBinding().equals(java.sql.Timestamp.class))
                {
                    pstmt.setTimestamp(i + 1, (java.sql.Timestamp) attributes[i]);
                } else if (attributeTypes[i].getBinding().equals(java.util.Date.class))
                {
                    java.sql.Date sDate = new java.sql.Date(((java.util.Date) attributes[i]).getTime());
                    pstmt.setDate(i + 1, sDate);
                }
            }

        }
    }
}