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

import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

import com.vividsolutions.jts.geom.LinearRing;
import com.ximple.eofms.util.FeatureTypeBuilderUtil;
import org.geotools.data.DataSourceException;
import org.geotools.data.DataStore;
import org.geotools.data.SchemaNotFoundException;
import org.geotools.data.Transaction;
import org.geotools.data.jdbc.JDBCUtils;
import org.geotools.filter.LengthFunction;
import org.geotools.jdbc.JDBCDataStore;
import org.geotools.referencing.NamedIdentifier;
import org.geotools.referencing.crs.DefaultGeographicCRS;
import org.opengis.feature.simple.SimpleFeature;
import org.opengis.feature.simple.SimpleFeatureType;
import org.opengis.feature.type.AttributeDescriptor;
import org.opengis.feature.type.AttributeType;
import org.opengis.feature.type.GeometryDescriptor;
import org.opengis.feature.type.PropertyType;
import org.opengis.filter.BinaryComparisonOperator;
import org.opengis.filter.Filter;
import org.opengis.filter.PropertyIsLessThan;
import org.opengis.filter.PropertyIsLessThanOrEqualTo;
import org.opengis.filter.expression.Literal;
import org.opengis.referencing.crs.CoordinateReferenceSystem;

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

import com.ximple.eofms.jobs.context.AbstractDgnFileJobContext;
import com.ximple.eofms.util.postjts.JtsBinaryWriter;

public abstract class AbstractDgnToEdbGeoJobContext extends AbstractDgnFileJobContext {
    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 = 2048;
    protected static final int BATCHSIZE = 256;

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

    private JDBCDataStore targetDataStore;
    // protected Connection connection;
    protected String targetSchema = "public";

    protected boolean schemaEnabled = true;

    public AbstractDgnToEdbGeoJobContext(String dataPath, DataStore targetDataStore, String targetSchema,
                                          boolean profileMode, boolean useTransform) {
        super(dataPath, profileMode, useTransform);
        if ((targetDataStore != null) && (targetDataStore instanceof JDBCDataStore)) {
            this.targetDataStore = (JDBCDataStore) targetDataStore;
        } else {
            getLogger().info("targetDataStore has wrong.");
        }
        // this.connection = connection;
        setTargetSchema(targetSchema);
    }

    public JDBCDataStore getTargetDataStore() {
        return targetDataStore;
    }

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

    public String getTargetSchema() {
        return targetSchema;
    }

    public void setTargetSchema(String schemaName) {
        targetSchema = schemaName;
    }

    public Connection getConnection() {
        try {
            return targetDataStore.getConnection(Transaction.AUTO_COMMIT);

        } catch (IOException e)
        {
            getLogger().warn(e.getMessage(), e);
            return null;
        }
        // return connection;
    }

    protected boolean isExistFeature(SimpleFeatureType featureType) {
        try {
            SimpleFeatureType 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(getTargetSchema());
        sb.append("\".\"");
        sb.append(tableName);
        sb.append('\"');
        stmt.execute(sb.toString());
        if (!conn.getAutoCommit())
            conn.commit();
        JDBCUtils.close(stmt);
    }

    protected void dropTable(Connection conn, String tableName) throws SQLException {
        Statement stmt = conn.createStatement();
        StringBuilder sb = new StringBuilder();
        sb.append("DROP TABLE \"");
        sb.append(getTargetSchema());
        sb.append("\".\"");
        sb.append(tableName);
        sb.append("\"");
        // sb.append(" CASCADE");
        stmt.execute(sb.toString());
        if (!conn.getAutoCommit())
            conn.commit();
        JDBCUtils.close(stmt);
    }

    protected void dropGeometryColumn(Connection conn, String tableName, String geomField) throws SQLException {
        Statement stmt = conn.createStatement();
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT \"public\".DropGeometryColumn('','");
        sb.append(tableName);
        sb.append("','");
        sb.append(geomField);
        sb.append("')");
        stmt.execute(sb.toString());
        if (!conn.getAutoCommit())
            conn.commit();
        JDBCUtils.close(stmt);
    }

    protected String dropGeometryColumn(String dbSchema, String tableName, String geomField) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT \"public\".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, GeometryDescriptor geometryDescriptor, int srid) {
        StringBuilder sql;
        String typeName = getGeometrySQLTypeName(((AttributeType) geometryDescriptor.getType()).getBinding());
        if (typeName == null) {
            getLogger().warn("Error: " + geometryDescriptor.getLocalName() + " unknown type!!!");
            throw new RuntimeException("Error: " + geometryDescriptor.getLocalName() + " unknown type!!!");
        }

        sql = new StringBuilder("SELECT \"public\".AddGeometryColumn('");
        sql.append(dbSchema);
        sql.append("','");
        sql.append(tableName);
        sql.append("','");
        sql.append(geometryDescriptor.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(Connection conn, SimpleFeatureType featureType) throws IOException {
        String origintableName = featureType.getTypeName();
        String tableName = origintableName.toLowerCase();

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

        List<AttributeDescriptor> descriptors = featureType.getAttributeDescriptors();

        boolean shouldDrop = tablePresent(getTargetSchema(), tableName, conn);

        if (shouldDrop) {
            String sqlStr;
            for (AttributeDescriptor descriptor : descriptors) {
                if (!(descriptor instanceof GeometryDescriptor)) {
                    continue;
                }
                GeometryDescriptor geometryDescriptor = (GeometryDescriptor) descriptor;
                sqlStr = dropGeometryColumn(getTargetSchema(), tableName, geometryDescriptor.getLocalName());
                getLogger().info(sqlStr);
                result.add(sqlStr);
            }
            // sqlStr = "DROP TABLE " + encodeSchemaTableName(tableName) + " CASCADE;";
            sqlStr = "DROP TABLE " + encodeSchemaTableName(tableName) + " ;";
            getLogger().info(sqlStr);
            result.add(sqlStr);
        }

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

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

        for (AttributeDescriptor descriptor : descriptors) {
            if (!(descriptor instanceof GeometryDescriptor)) {
                continue;
            }
            GeometryDescriptor geometryDescriptor = (GeometryDescriptor) descriptor;

            CoordinateReferenceSystem refSys = geometryDescriptor.getCoordinateReferenceSystem();
            int SRID = getSRID(refSys);

            sqlStr = addGeometryColumn(getTargetSchema(), tableName, geometryDescriptor, SRID);
            getLogger().debug(sqlStr);
            result.add(sqlStr);


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

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

            result.add(sqlStr);
        }
        return result;
    }

    private int getSRID(CoordinateReferenceSystem refSys) {
        int SRID;
        if (refSys != null) {
            try {
                Set ident = refSys.getIdentifiers();
                if ((ident == null || ident.isEmpty()) && refSys == DefaultGeographicCRS.WGS84) {
                    SRID = 4326;
                } else {
                    assert ident != null;
                    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;
        }
        return SRID;
    }

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

        ResultSet tables = null;
        try {
            DatabaseMetaData meta = conn.getMetaData();
            String[] tableType = {"TABLE"};
            tables = meta.getTables(null, schema, "%", 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) {
            String message = "Error querying database for list of tables:"
                + sqlException.getMessage();
            throw new DataSourceException(message, sqlException);
        } finally {
            if (tables != null)
                JDBCUtils.close(tables);
        }
    }

    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 StringBuilder makeNonGeomSqlCreate(List<AttributeDescriptor> descriptors)
        throws IOException {
        StringBuilder buf = new StringBuilder("");

        for (AttributeDescriptor descriptor : descriptors) {
            String typeName;
            typeName = CLASS_MAPPINGS.get(((AttributeType) descriptor.getType()).getBinding());
            if (typeName == null) {
                typeName = GEOM_CLASS_MAPPINGS.get(((AttributeType) descriptor.getType()).getBinding());
                if (typeName != null) continue;
            }

            if (typeName != null) {
                if (typeName.equals("VARCHAR")) {
                    int length = -1;
                    List<Filter> flist = ((PropertyType) descriptor.getType()).getRestrictions();
                    for (Filter f : flist) {
                        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;
                        }
                        break;
                    }
                    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 (!descriptor.isNillable()) {
                    typeName = typeName + " NOT NULL";
                }

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

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

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

            } else {
                String msg;
                if (descriptor == null) {
                    msg = "AttributeType was null!";
                } else {
                    msg = "Type '" + ((AttributeType) descriptor.getType()).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 + ")";
        */
        if (geom instanceof LinearRing) {
            //postgis does not handle linear rings, convert to just a line string
            geom = geom.getFactory().createLineString(((LinearRing) geom).getCoordinateSequence());
        }
        return "ST_GeomFromText('" + geom.toText() + "', " + srid + ")";
    }

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

        String tableName = encodeSchemaTableName(featureType.getTypeName());
        List<AttributeDescriptor> descriptors = featureType.getAttributeDescriptors();

        String attrValue;

        StringBuilder statementSQL = new StringBuilder("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 (AttributeDescriptor attributeType : descriptors) {
            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 (");

        List<Object> attributes = feature.getAttributes();

        for (int i = 0; i < descriptors.size(); i++) {
            attrValue = null;

            if (descriptors.get(i) instanceof GeometryDescriptor) {
                // String geomName = descriptors[i].getLocalName();
                // int srid = ftInfo.getSRID(geomName);
                Geometry geometry = (Geometry) attributes.get(i);
                geometry.setSRID(FeatureTypeBuilderUtil.getDefaultFeatureSRID());

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

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

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

        return (statementSQL.toString());
    }

    protected String makePrepareInsertSql(SimpleFeatureType featureType) {
        String tableName = encodeSchemaTableName(featureType.getTypeName());
        List<AttributeDescriptor> attributeTypes = featureType.getAttributeDescriptors();

        String attrValue;

        StringBuilder statementSQL = new StringBuilder("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 (AttributeDescriptor descriptor : attributeTypes) {
            String attName = descriptor.getName().getLocalPart();

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

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

        for (AttributeDescriptor 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 ? ("\"" + getTargetSchema() + "\".\"" + tableName + "\"")
            : ("\"" + tableName + "\"");
    }

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

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

        List<AttributeDescriptor> descriptors = featureType.getAttributeDescriptors();
        List<Object> attributes = feature.getAttributes();

        for (int i = 0; i < descriptors.size(); i++) {
            if (descriptors.get(i) instanceof GeometryDescriptor) {
                pstmt.setBytes(i + 1, binaryWriter.writeBinary((Geometry) attributes.get(i)));
            } else {
                Class<?> bindingType = ((AttributeType) descriptors.get(i).getType()).getBinding();
                if (bindingType.equals(Short.class)) {
                    pstmt.setShort(i + 1, (Short) attributes.get(i));
                } else if (bindingType.equals(Integer.class)) {
                    pstmt.setInt(i + 1, (Short) attributes.get(i));
                } else if (bindingType.equals(Long.class)) {
                    pstmt.setLong(i + 1, (Long) attributes.get(i));
                } else if (bindingType.equals(String.class)) {
                    pstmt.setString(i + 1, (String) attributes.get(i));
                } else if (bindingType.equals(Float.class)) {
                    pstmt.setFloat(i + 1, (Float) attributes.get(i));
                } else if (bindingType.equals(Double.class)) {
                    pstmt.setDouble(i + 1, (Double) attributes.get(i));
                } else if (bindingType.equals(Boolean.class)) {
                    pstmt.setBoolean(i + 1, (Boolean) attributes.get(i));
                } else if (bindingType.equals(BigDecimal.class)) {
                    pstmt.setBigDecimal(i + 1, (BigDecimal) attributes.get(i));
                } else if (bindingType.equals(java.sql.Date.class)) {
                    pstmt.setDate(i + 1, (java.sql.Date) attributes.get(i));
                } else if (bindingType.equals(java.sql.Time.class)) {
                    pstmt.setTime(i + 1, (java.sql.Time) attributes.get(i));
                } else if (bindingType.equals(java.sql.Timestamp.class)) {
                    pstmt.setTimestamp(i + 1, (java.sql.Timestamp) attributes.get(i));
                } else if (bindingType.equals(java.util.Date.class)) {
                    java.sql.Date sDate = new java.sql.Date(((java.util.Date) attributes.get(i)).getTime());
                    pstmt.setDate(i + 1, sDate);
                }
            }
        }
    }
}