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

import com.sysu.admin.MarkApplication;
import com.sysu.admin.api.base.BaseTest;
import com.sysu.admin.controller.crop.CropLandService;
import com.sysu.admin.utils.TextUtil;
import com.sysu.admin.utils.shape.ShapeReader;
import lombok.SneakyThrows;
import org.hibernate.Transaction;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.persistence.EntityManager;
import java.io.*;
import java.util.List;
import java.util.Map;

//@RunWith(SpringRunner.class)
//@SpringBootTest(classes = MarkApplication.class)
public class PostGisUtilTest {

    static String sourceDir = "D:\\BaiduNetdiskDownload\\merge\\leizhou";
    static String outputDir = "D:";
    static String tablesString = "banan,beibei,bishan,changshou,chengkou,dadukou,dazu,dianjiang,fengdu,fengjie,fuling,hechuan,jiangbei,jiangjin,jiulongpo,kaizhou,liangping,nanan,nanchuan,pengshui,qianjiang,qijiang,qiuyang,rongchang,shapingba,shizhu,tongliang,tongnan,wanzhou,wulong,wushan,wuxi,xiushan,yongchuan,yubei,yunyang,yuzhong,zhong";

    @Autowired
    public CropLandService cropLandService;

    @Test
    public void updateExec(){
        try {
            BufferedReader reader = new BufferedReader(new FileReader(new File(sourceDir, "sql.txt")));
            while(reader.ready()){
                cropLandService.exec(reader.readLine());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static String[] getTableNames(){
        String[] typeNames = TextUtil.split(tablesString,",");
        for(int i=0;i<typeNames.length;i++){
            typeNames[i] = "p_land_" + typeNames[i];
        }
        return typeNames;
    }

    public static void createTable() throws Exception {

        BufferedReader fileReader = new BufferedReader(new FileReader(new File("D:","create_table.model.txt")));
        StringBuffer model = new StringBuffer();
        while(fileReader.ready()){
            model.append(fileReader.readLine());
            model.append("\n");
        }
        FileWriter fileWriter = new FileWriter(new File(outputDir,"createTales.sql"));
        for(String typeName : getTableNames()) {
            fileWriter.append(model.toString().replaceAll("#table", typeName));
            fileWriter.append("\n\n");
        }
        fileWriter.flush();
        fileWriter.close();

    }


    @Test
    public void testInsertShp() {
        File shpDir = new File(sourceDir);
        File[] shpList = shpDir.listFiles((dir, name) -> {
            if(name.endsWith("shp")){
                return true;
            }
            return false;
        });

        try {
            FileWriter fileWriter = new FileWriter(new File(outputDir,"insert.sql"));
            for(File shp : shpList) {
                String shpName = shp.getName();
                List<String> list = ShapeReader.getShapeFile(shp);
                System.out.println("数据准备：");
                System.out.println(list.size());
                getInsertSqls(fileWriter, "p_land_" + shpName.substring(0, shpName.indexOf(".")).toLowerCase(), list);
            }

            fileWriter.flush();
            fileWriter.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @SneakyThrows
    public void getInsertSqls(FileWriter fileWriter,String typeName, List<String> wkts){
        int i=0;
        int c = 200;
        while(i < wkts.size()) {
            if(i % c == 0) {
                fileWriter.append("insert into " + typeName + "(geom) values");
            }
            fileWriter.append("(st_geomfromtext('");
            fileWriter.append(wkts.get(i));
            fileWriter.append("',4326))");

            if(i % c < (c-1) && i < wkts.size() - 1){
                fileWriter.append(",");
            }else{
                fileWriter.append(";");
                fileWriter.append("\n");
            }
            i++;
        }
    }


    @Test
    public void testInsertShpLeizhou() {
        File shpDir = new File(sourceDir);
        File[] shpList = shpDir.listFiles((dir, name) -> {
            if(name.endsWith("shp")){
                return true;
            }
            return false;
        });

        try {
            FileWriter fileWriter = new FileWriter(new File(outputDir,"insert.sql"));
            for(File shp : shpList) {
                List<Map<String,Object>> list = ShapeReader.getShapeFileLeizhou(shp);
                System.out.println("数据准备：");
                System.out.println(list.size());
                getInsertSqlsLeizhou(fileWriter, list);
            }

            fileWriter.flush();
            fileWriter.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @SneakyThrows
    public void getInsertSqlsLeizhou(FileWriter fileWriter, List<Map<String, Object>> list){
        int i=0;
        int c = 100;
        while(i < list.size()) {
            if(i % c == 0) {
                fileWriter.append("insert into leizhou_land (area,simpgnflag,toc,quxian,shengdao,hupo,gdp_pop,slope,aspect,name,id_card,crop_type,value,intensity,parcel_type,district_code,town_id,geom) values");
            }
            fileWriter.append("(");
            Map<String, Object> map = list.get(i);
            fileWriter.append("'"+map.get("area")+"',");
            fileWriter.append("'"+map.get("simpgnflag")+"',");
            fileWriter.append("'"+map.get("toc")+"',");
            fileWriter.append("'"+map.get("quxian")+"',");
            fileWriter.append("'"+map.get("shengdao")+"',");
            fileWriter.append("'"+map.get("hupo")+"',");
            fileWriter.append("'"+map.get("gdp_pop")+"',");
            fileWriter.append("'"+map.get("slope")+"',");
            fileWriter.append("'"+map.get("aspect")+"',");
            fileWriter.append("'"+map.get("name")+"',");
            fileWriter.append("'"+map.get("id_card")+"',");
            fileWriter.append("'"+map.get("crop_type")+"',");
            fileWriter.append("'"+map.get("value")+"',");
            fileWriter.append("'"+((Double)map.get("intensity")).intValue()+"',");
            fileWriter.append("'"+Double.valueOf(map.get("parceltype").toString()).intValue()+"',");
            fileWriter.append("'"+map.get("district_code")+"',");
            fileWriter.append("'"+map.get("town_id")+"',");
            fileWriter.append("st_geomfromtext('");
            fileWriter.append(map.get("geom").toString());
            fileWriter.append("',4326))");

            if(i % c < (c-1) && i < list.size() - 1){
                fileWriter.append(",");
            }else{
                fileWriter.append(";");
                fileWriter.append("\n");
            }
            i++;
        }
    }

    public void updateCity(){
        for(String tableName : getTableNames()){
            System.out.println("update "+ tableName + " t1 set province =500000,city = 500100, district = t2.code from p_land_range_index t2 where t2.table_name = '"+tableName+"';");
        }
    }

    public void updateCenterPoint(){
        for(String tableName : getTableNames()){
            System.out.println("update "+ tableName + " t1 set  center_point = st_centroid(geom);");
        }
    }

    public void updateSrid(){
        for(String tableName : getTableNames()){
            System.out.println("update "+ tableName + " t1 set  geom = ST_Transform(geom,'EPSG:3857',4326);");
        }
    }

    public void removeTables(){
        for(String tableName : getTableNames()){
            System.out.println("delete from "+ tableName + " ;");
        }
    }

    public void insertLandRangeIndex(){
        for(String tableName : getTableNames()){
            System.out.println("insert into p_land_range_index(\"start\",\"end\",\"table_name\") ");
            System.out.println("select (select id from "+tableName+" order by id limit 1),(select id from "+tableName+" order by id desc limit 1),'"+tableName+"';");
        }
    }

    public void layers(){
        System.out.print("[");
        for(String tableName : getTableNames()){
            System.out.print("'wuhan:"+tableName+"',");
        }
        System.out.print("]");
    }

    public void ST_Extent(){
        for(String tableName : getTableNames()){
            System.out.println("select '"+tableName+"',ST_Extent(geom) from "+tableName);
            System.out.println("union all");
        }
    }

    public void createIndex(){
        for(String tableName : getTableNames()) {
            System.out.println("CREATE INDEX IF NOT EXISTS "+tableName+"_status_index ON public."+tableName+" USING hash(status) TABLESPACE pg_default;");
        }
    }


    public void createCOLUMN(){
        for(String tableName : getTableNames()) {
            System.out.println("ALTER TABLE IF EXISTS public."+tableName+" ADD COLUMN confirm integer;");
        }
    }








}
