package com.sysu.admin.utils.shape.postgis;

import java.io.File;
import java.io.IOException;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

import com.sysu.admin.utils.shape.FileFormat;
import com.sysu.admin.utils.shape.SrsCastListener;
import com.sysu.admin.utils.shape.vo.Param;
import com.sysu.admin.utils.shape.vo.ParamType;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.geotools.data.*;
import org.geotools.data.shapefile.ShapefileDataStore;
import org.geotools.feature.FeatureIterator;
import org.geotools.feature.simple.SimpleFeatureBuilder;
import org.geotools.filter.text.cql2.CQL;
import org.geotools.filter.text.cql2.CQLException;
import org.geotools.geojson.feature.FeatureJSON;
import org.geotools.jdbc.JDBCDataStore;
import org.geotools.jdbc.PrimaryKey;
import org.geotools.jdbc.SQLDialect;
import org.geotools.util.factory.Hints;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.operation.overlay.snap.SnapOverlayOp;
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 static org.geotools.jdbc.VirtualTable.setKeepWhereClausePlaceHolderHint;

public class PostGisUtil {
    JDBCDataStore pgDatastore;
    Connection connection;
    Statement statement;

    public JDBCDataStore connPostGis(PostGisConfig postGisConfig){
        postGisConfig = postGisConfig == null ? new PostGisConfig() :  postGisConfig;
        Map params = new HashMap();
        params.put("dbtype", "postgis");
        params.put("host", postGisConfig.getUrl());
        params.put("port", postGisConfig.getPort());
        params.put("database", postGisConfig.getDatabase());
        params.put("user", postGisConfig.getUser());
        params.put("passwd", postGisConfig.getPassword());
        try {
            DataStore dataStore = DataStoreFinder.getDataStore(params);
            pgDatastore = (JDBCDataStore)dataStore;
            if(pgDatastore!=null){
                System.out.println("系统连接到位于："+postGisConfig.getUrl()+"的空间数据库"+postGisConfig.getDatabase()+"成功！");
            }else{
                System.out.println("系统连接到位于："+postGisConfig.getUrl()+"的空间数据库"+postGisConfig.getDatabase()+"失败！请检查相关参数");
            }
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("系统连接到位于："+postGisConfig.getUrl()+"的空间数据库"+postGisConfig.getDatabase()+"失败！请检查相关参数");
        }
        return pgDatastore;
    }

    public  JDBCDataStore connPostGis(){
        return connPostGis(null);
    }

    public void dispose(){
        pgDatastore.dispose();
    }

    public  StringBuffer selectColumns(String prefix,Query query,SimpleFeatureType featureType, SQLDialect sqlDialect) throws IOException, SQLException {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ");

        pgDatastore.selectColumns(featureType, prefix, query, sql);

        sql.setLength(sql.length() - 1);
        sqlDialect.encodePostSelect(featureType, sql);
        return sql;
    }

    public void openConnection(){
        try {
            connection = pgDatastore.getConnection(Transaction.AUTO_COMMIT);
            statement = connection.createStatement();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public void closeConnection(){
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


    /**
     * 查询元素
     * @return
     */
    public  ArrayList<SimpleFeature> queryMethod(String filter, String prefix, String name, boolean isSql) throws IOException, SQLException {
        // Step 2 - connection
        StringBuffer sql = null;
        ArrayList<SimpleFeature> featureList = new ArrayList<>();
        SimpleFeatureType featureType = pgDatastore.getSchema(name);
        SQLDialect sqlDialect = pgDatastore.getSQLDialect();

        //生成查询sql语句
        {
            Query query = new Query(name);
            if (!isSql) {
                if (StringUtils.isNotEmpty(filter)) {
                    try {
                        query.setFilter(CQL.toFilter(filter));
                    } catch (CQLException e) {
                        e.printStackTrace();
                    }
                }
                sql = new StringBuffer();
                sql.append(pgDatastore.selectSQL(featureType, query));
            } else {
                sql = selectColumns(prefix, query, featureType, sqlDialect);
                sql.append(filter);
            }
            System.out.println(sql.toString());
        }
        ResultSet resultSet = statement.executeQuery(sql.toString());

        SimpleFeatureBuilder builder = new SimpleFeatureBuilder(featureType, pgDatastore.getFeatureFactory());
        PrimaryKey pkey = pgDatastore.getPrimaryKey(featureType);

        final int attributeCount = featureType.getAttributeCount();
        while(resultSet.next()) {

            for (int i = 0; i < attributeCount; i++) {
                AttributeDescriptor descriptor = featureType.getAttributeDescriptors().get(i);
                Object value = null;
                if (descriptor instanceof GeometryDescriptor) {
                    value = sqlDialect.decodeGeometryValue((GeometryDescriptor) descriptor, resultSet, descriptor.getName().toString(),
                            pgDatastore.getGeometryFactory(),
                            connection, new Hints());
                }else{
                    value = resultSet.getObject(descriptor.getName().toString());
                    value = sqlDialect.convertValue(value, descriptor);
                }
                builder.add(value);
            }

            String fid = name + "." + pgDatastore.encodeFID(pkey, resultSet);
            featureList.add(builder.buildFeature(fid));
        }
        resultSet.close();
        return featureList;
    }

    public boolean insertShp(String typeName, File shpFile){
        return insertShp(typeName, shpFile, null, true, null);
    }

    public boolean insertShp(String typeName, File shpFile, SrsCastListener srsCastListener, boolean extAttr, String[] notIgnore){
        Transaction t = new DefaultTransaction("handle");
        Connection tempConnection = null;
        Connection connection = null;
        try{
            ShapefileDataStore shpDataStore = new ShapefileDataStore(shpFile.toURL());
            //先清空数据库 主键从1开始
            tempConnection = pgDatastore.getConnection(Transaction.AUTO_COMMIT);
            tempConnection.createStatement().execute("TRUNCATE "+typeName+ " RESTART IDENTITY");
            connection = pgDatastore.getConnection(t);
            FeatureIterator<SimpleFeature> itertor = FileFormat.getFeatures(shpDataStore);
            FeatureWriter<SimpleFeatureType, SimpleFeature> writer = pgDatastore.getFeatureWriterAppend(typeName, t);
            SimpleFeatureType featureType = pgDatastore.getSchema(typeName);
            List<AttributeType>  attributeTypes = featureType.getTypes();
            while (itertor.hasNext())
            {
                SimpleFeature sourceFeature = itertor.next();
                SimpleFeature targetFeature = writer.next();
                if(ObjectUtils.isNotEmpty(notIgnore)){
                    for(String name : notIgnore) {
                        targetFeature.setAttribute(name, sourceFeature.getAttribute(name));
                    }
                }else{
                    for(AttributeType attributeType : attributeTypes){
                        targetFeature.setAttribute(attributeType.getName(), sourceFeature.getAttribute(attributeType.getName()));
                    }
                }

                if(extAttr) {
                    targetFeature.setAttribute("objectid", sourceFeature.getAttribute("OBJECTID"));
                    targetFeature.setAttribute("shape_leng", sourceFeature.getAttribute("Shape_Leng"));
                    targetFeature.setAttribute("shape_area", sourceFeature.getAttribute("Shape_Area"));
                }
                if(srsCastListener == null)
                    targetFeature.setDefaultGeometry(sourceFeature.getDefaultGeometry());
                else
                    targetFeature.setDefaultGeometry(srsCastListener.listener((Geometry) sourceFeature.getDefaultGeometry()));
                writer.write();
            }
            itertor.close();
            t.commit();
        }catch (Exception e){
            try {
                t.rollback();
            } catch (IOException ioException) {
                ioException.printStackTrace();
            }
            e.printStackTrace();
            return false;
        }finally {
            try {
                t.close();
                if(connection != null){connection.close();}
                if(tempConnection != null){tempConnection.close();}
            } catch (IOException | SQLException e) {
                e.printStackTrace();
                return false;
            }
        }
        return true;
    }



    public static String featureToGeoJson(SimpleFeature simpleFeature){
        FeatureJSON fjson = new FeatureJSON();
        SimpleFeature feature = simpleFeature;
        StringWriter writer = new StringWriter();
        try {
            fjson.writeFeature(feature, writer);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return writer.toString();
    }

    public static void main(String[] args) throws IOException, SQLException {
        PostGisUtil postGisUtil = new PostGisUtil();
        //连接数据库
        postGisUtil.connPostGis();

        //查询出用于测量得图斑
        ArrayList<SimpleFeature> list = postGisUtil.queryMethod(null,null,"yd_land", false);
        Geometry geometry = (Geometry) list.get(0).getDefaultGeometry();

        //查询出与测量面积相交的区域
        String filter = " FROM whxzqh  WHERE ST_Relate(geom,(select geom from test3  where gid = 1),'T********') = true";
        list = postGisUtil.queryMethod(filter,null,"whxzqh", true);
        //交叉数组
        ArrayList<Geometry> intersections = new ArrayList<>(list.size());
        List<Param> paramList = new ArrayList<>();
        String[] names = {"locname","area"};//属性名
        Class[] types = {String.class, Double.class};//属性类型
        for(SimpleFeature s : list){
            System.out.println(featureToGeoJson(s));
            Object locname = s.getProperties("locname").iterator().next().getValue();
            System.out.println(locname);
            Geometry intersection =  SnapOverlayOp.intersection(geometry.intersection(geometry),(Geometry)s.getDefaultGeometry());
            if(!intersection.isEmpty()) {
                intersections.add(intersection);
                Object[] vals = {locname, intersection.getArea()};
                paramList.add(new Param(names,vals));
            }
        }
        ParamType[] paramTypes = ParamType.build(names,types);
        FileFormat.toShpPostGis(intersections,postGisUtil.pgDatastore, paramTypes, paramList);
    }


}
