package com.yeejoin.amos.kgd.config;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.util.StringUtil;
import com.yeejoin.amos.boot.biz.common.utils.DateUtils;
import com.yeejoin.amos.component.robot.AmosRequestContext;
import com.yeejoin.amos.feign.privilege.util.DesUtil;
import com.yeejoin.amos.kgd.message.Constant;
import com.yeejoin.amos.kgd.message.model.TableFieldModel;
import com.yeejoin.amos.kgd.message.model.TableModel;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.InputStreamResource;
import org.springframework.core.io.Resource;
import org.springframework.http.*;
import org.springframework.util.LinkedMultiValueMap;
import org.springframework.util.MultiValueMap;
import org.springframework.web.client.RestTemplate;
import org.typroject.tyboot.core.foundation.context.SpringContextHelper;

import java.io.*;
import java.net.Socket;
import java.nio.file.FileSystems;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @Author: xl
 * @Description:
 * @Date: 2023/10/10 16:21
 */
@SuppressWarnings("UnnecessarySemicolon")
public class ClientHandler<path> implements Runnable {
    private static final Logger log = LoggerFactory.getLogger(ClientHandler.class);

    private final Socket socket;

    private final String hostAndPort;

    private final String url;

    private final String username;

    private final String password;

    private final String passwordSecretKey;

    private final String dataSourcesGroupId;

    public ClientHandler(Socket socket, String hostAndPort, String url, String username, String password, String passwordSecretKey, String dataSourcesGroupId) {
        this.socket = socket;
        this.hostAndPort = hostAndPort;  //127.0.0.1:30009
        this.url = url; // 数据库连接地址
        this.username = username;// 数据库用户名
        this.password = password;// 数据库密码
        this.passwordSecretKey = passwordSecretKey;// 数据库密码加密密钥
        this.dataSourcesGroupId = dataSourcesGroupId;// 空工大数据源所属分类Id
    }

    /*String*/
    public static final String TYPE_STRING = "String";
    /*Integer*/
    public static final String TYPE_INTEGER = "Integer";
    /*Double*/
    public static final String TYPE_DOUBLE = "Double";
    /*Date*/
    public static final String TYPE_DATETIME = "DateTime";
    /*BigDecimal*/
    public static final String TYPE_BIGDECIMAL = "BigDecimal";
    /*longtext*/
    public static final String TYPE_LONGTEXT = "longtext";
    /*text*/
    public static final String TYPE_TEXT = "text";


    @Override
    public void run() {
        try {
            // 获取输入流和输出流
            InputStream ips = socket.getInputStream();
            //存入缓存，方便重复使用
            InputStreamCacher inputStreamCacher = new InputStreamCacher(ips);

            // ips中，不是单纯的文件流，还有一些其他字段，所以要将单纯文件的部分过滤出来
            // 获取文件地址信息
            String filePath = filenameFilter(inputStreamCacher.getInputStream());

            //截取文件名
            String filenameWithSuffix = FileSystems.getDefault().getPath(filePath).getFileName().toString();
            String postfix = filenameWithSuffix.substring(filenameWithSuffix.lastIndexOf(".") + 1);
            if (!"xlsx".equals(postfix) && !"xls".equals(postfix)) {
                throw new Exception("导入失败，请选择正确的文件格式支持xlsx或xls");
            }
            // 获取文件名
            String filename = filenameWithSuffix.substring(0, filenameWithSuffix.lastIndexOf("."));
            // 获取表名称
            String tableName = "";
            // 匹配开头到日期时间开始前的字符
            Pattern pattern = Pattern.compile("^.*?(?=\\d{4}年\\d{1,2}月\\d{1,2}日\\d{2}时\\d{2}分\\d{2}秒)");
            Matcher matcher = pattern.matcher(filename);
            if (matcher.find()) {
                tableName = matcher.group();
            } else {
                throw new Exception("导入失败，文件名称格式不正确！");
            }

            InputStream ipsClone2 = inputStreamCacher.getInputStream();
            InputStream contentIps = fileInputStreamFilter(ipsClone2, postfix);

            // 获取Excel表头内容 和 Excel表数据内容
            Map<String, Object> tableInfoMap = ExcelTool.readFromExcel(contentIps, filenameWithSuffix);

            // 新增mysql表结构和数据和数据源
            if (StringUtil.isNotEmpty(tableName) && !tableInfoMap.isEmpty() && tableInfoMap.get("tableColumns") != null) {
                List<String> tableColumns = (List<String>) tableInfoMap.get("tableColumns");
                List<List<String>> tableDatas = (List<List<String>>) tableInfoMap.get("tableDatas");
                //新增mysql数据库和表
                this.addMySqlDatasources(tableColumns, tableDatas, tableName);
                //新增 maas 数据源
                this.addMaasDatasources(tableName);
            }
            // 关闭连接
            socket.close();
            ips.close();
            // 销毁缓存
            if (inputStreamCacher != null) inputStreamCacher.destroyCache();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    //新增My Sql数据源
    private void addMySqlDatasources(List<String> tableColumns, List<List<String>> dataList, String tableName) throws Exception {
        AmosRequestContext robotAuthentication = SpringContextHelper.getBean(AmosRequestContext.class);
        if (Objects.nonNull(robotAuthentication)) {
//            String token = robotAuthentication.getToken();
//            String product = robotAuthentication.getProduct();
//            String appKey = robotAuthentication.getAppKey();

            String token = "a1bae8ec-6ef4-40f3-94b0-78f16a249b0e";  // 如果token、失效，就去浏览器复制最新的token、
            String product = "AMOS_STUDIO_WEB";
            String appKey = "AMOS_STUDIO";

            Connection conn = null;
            Statement state = null;
            try {
                //1.jdbc连接数据库
                Map<String, Object> connMap = connectMySQL();
                if (connMap.isEmpty()) {
                    throw new Exception("获取数据库连接失败...");
                }
                // 获取数据库连接
                conn = (Connection) connMap.get("connection");
                // 获取SQL执行环境
                state = (Statement) connMap.get("statement");
                //2.判断数据库表是否存在【不存在时，新增表。存在时，直接录入数据.】
                // 获取数据库表名
                ResultSet tables = conn.getMetaData().getTables(null, null, tableName, null);
                // 2.1是否存在表
                if (!tables.next()) {
                    // 不存在则创建表
                    TableModel tableModel = new TableModel();

                    List<TableFieldModel> tableFieldList = new ArrayList<>();
                    tableModel.setTableName(tableName);

                    // start 添加默认字段ID和createDate
                    TableFieldModel defaultFieldId = new TableFieldModel();
                    defaultFieldId.setFieldType(TYPE_STRING);
                    defaultFieldId.setFieldName("ID");
                    defaultFieldId.setPrimaryKey("1");
                    defaultFieldId.setIsNull("0");
                    defaultFieldId.setFieldLength("50");
                    tableFieldList.add(defaultFieldId);

                    TableFieldModel defaultFieldCreateDate = new TableFieldModel();
                    defaultFieldCreateDate.setFieldType(TYPE_STRING);
                    defaultFieldCreateDate.setFieldName("CREATEDATE");
                    defaultFieldCreateDate.setIsNull("0");
                    defaultFieldCreateDate.setFieldLength("50");
                    tableFieldList.add(defaultFieldCreateDate);
                    // end 添加默认字段ID和createDate

                    //Excel中字段
                    if (tableColumns.size() > 0) {
                        for (int i = 0; i < tableColumns.size(); i++) {
                            TableFieldModel tableFieldModel = new TableFieldModel();
                            tableFieldModel.setFieldName(tableColumns.get(i));
                            tableFieldModel.setFieldType(TYPE_LONGTEXT);
                            tableFieldList.add(tableFieldModel);
                        }
                    }
                    tableModel.setTableFields(tableFieldList);
                    //生成建表语句
                    String createTableSql = createTableSQL(tableModel);
                    if (StringUtil.isEmpty(createTableSql)) {
                        throw new Exception("mysql建表语句出错！");
                    }
                    // 创建表
                    state.executeUpdate(createTableSql);
                }
                // 2.2插入数据
                if (dataList.size() > 0) {
                    if (dataList.size() <= 1000) {
                        String insertSQL = insertDataSQLBatch(tableName, tableColumns, dataList);
                        if (StringUtil.isNotEmpty(insertSQL)) {
                            state.execute(insertSQL);
                        }
                    } else {
                        for (int i = 0; i <= dataList.size(); i++) {
                            List<List<String>> lists = null;
                            if (i == dataList.size()) {
                                lists = dataList.subList(dataList.size() - i % 1000, dataList.size());
                            }
                            if (i % 1000 == 0 && i > 0) {
                                lists = dataList.subList(i - 1000, i);
                            }
                            if (lists != null) {
                                String insertSQL = insertDataSQLBatch(tableName, tableColumns, lists);
                                if (StringUtil.isNotEmpty(insertSQL)) {
                                    state.execute(insertSQL);
                                }
                            }
                        }
                    }
                }

            } catch (Exception e) {
                e.printStackTrace();
                throw new Exception("数据表创建失败！");
            } finally {
                // 释放资源
                state.close();
                conn.close();
            }

        }
    }

    // 新增 maas 数据源
    private void addMaasDatasources(String tableName) {
        AmosRequestContext robotAuthentication = SpringContextHelper.getBean(AmosRequestContext.class);
        if (Objects.nonNull(robotAuthentication)) {
//            String token = robotAuthentication.getToken();
//            String product = robotAuthentication.getProduct();
//            String appKey = robotAuthentication.getAppKey();

            String token = "a1bae8ec-6ef4-40f3-94b0-78f16a249b0e";  // 如果token、失效，就去浏览器复制最新的token、
            String product = "AMOS_STUDIO_WEB";
            String appKey = "AMOS_STUDIO";

            String[] parts = url.split("/|:");
            String dbIp = parts[4];
            int dbPort = Integer.parseInt(parts[5]);
            String dbName = parts[6].split("\\?")[0];

            RestTemplate restTemplate = new RestTemplate();
            String defaultDatabaseName = "空工大_" + dbName;
            //查询数据库是否已创建
            String connId = "";
            String datasourcesUrl = "http://" + hostAndPort + "/maas/maas/desktop/conns-jdbc";
            Map<String, String> datasourcesParams = new HashMap<>();
            HttpEntity<Map<String, String>> datasourcesRequestEntity = new HttpEntity<>(datasourcesParams, getHeader(token, product, appKey, hostAndPort, false));
            ResponseEntity<String> datasourcesResponse = restTemplate.exchange(datasourcesUrl, HttpMethod.GET, datasourcesRequestEntity, String.class);
            String datasourcesBody = datasourcesResponse.getBody();
            JSONObject datasourcesJsonObject = JSONObject.parseObject(datasourcesBody);
            JSONArray datasourcesResult = datasourcesJsonObject.getJSONArray("result");
            if (datasourcesResult != null && datasourcesResult.size() > 0) {
                for (int i = 0; i < datasourcesResult.size(); i++) {
                    Map<String, Object> stringObjectMap = (Map<String, Object>) datasourcesResult.get(i);
                    if (defaultDatabaseName.equals(stringObjectMap.get("name"))) {
                        connId = (String) stringObjectMap.get("id");
                        break;
                    }
                }
            }
            //查询数据库表是否已创建
            boolean isCreateTable = false;
            if (StringUtil.isNotEmpty(connId)) {

                String tablelistUrl = "http://" + hostAndPort + "/maas/maas/desktop/tablelist";
                Map<String, String> tablelistParams = new HashMap<>();
                tablelistParams.put("connId", connId);
                tablelistParams.put("subcjectId", dataSourcesGroupId);
                HttpEntity<Map<String, String>> tablelistRequestEntity = new HttpEntity<>(tablelistParams, getHeader(token, product, appKey, hostAndPort, false));
                ResponseEntity<String> tablelistResponse = restTemplate.exchange(tablelistUrl, HttpMethod.POST, tablelistRequestEntity, String.class);
                String tablelistBody = tablelistResponse.getBody();
                JSONObject tablelistJsonObject = JSONObject.parseObject(tablelistBody);
                JSONArray tablelistResult = tablelistJsonObject.getJSONArray("result");
                if (tablelistResult != null && tablelistResult.size() > 0) {
                    for (int i = 0; i < tablelistResult.size(); i++) {
                        Map<String, Object> stringObjectMap = (Map<String, Object>) tablelistResult.get(i);
                        if (tableName.equals(stringObjectMap.get("name"))) {
                            isCreateTable = true;
                            break;
                        }
                    }
                }
            }


            //3. 调用conn接口,数据库已创建过就不在创建
            if (StringUtil.isEmpty(connId)) {
                String connsUrl = "http://" + hostAndPort + "/maas/maas/desktop/conns";
                Map<String, String> connsParams = new HashMap<>();
                connsParams.put("name", defaultDatabaseName);
                connsParams.put("connType", "mysql");
                connsParams.put("subjectid", dataSourcesGroupId);
                connsParams.put("detail", "{\"hostname\":\"" + dbIp + "\",\"port\":" + dbPort + ",\"username\":\"" + username + "\",\"password\":\"" + DesUtil.encode(password, passwordSecretKey) + "\",\"name\":\"" + defaultDatabaseName + "\",\"database\":\"" + dbName + "\",\"dbtype\":\"mysql\"}");

                HttpEntity<Map<String, String>> connsRequestEntity = new HttpEntity<>(connsParams, getHeader(token, product, appKey, hostAndPort, false));
                ResponseEntity<String> connsResponse = restTemplate.exchange(connsUrl, HttpMethod.POST, connsRequestEntity, String.class);
                String connsBody = connsResponse.getBody();
                JSONObject connsJsonObject = JSONObject.parseObject(connsBody);
                connId = connsJsonObject.getString("result");
            }
            //数据库表已创建过就不在创建
            if (StringUtil.isNotEmpty(connId) && !isCreateTable) {
                //4.新增mass数据源
                String tablesUrl = "http://" + hostAndPort + "/maas/maas/desktop/tables";
                Map<String, String> tablesParams = new HashMap<>();
                tablesParams.put("config", "{\"tableType\":\"Filter\",\"criterias\":[],\"tableName\":\"" + tableName + "\"}");
                tablesParams.put("connId", connId);
                tablesParams.put("name", tableName);
                tablesParams.put("pid", dataSourcesGroupId);

                HttpEntity<Map<String, String>> tablesRequestEntity = new HttpEntity<>(tablesParams, getHeader(token, product, appKey, hostAndPort, false));
                ResponseEntity<String> tablesResponse = restTemplate.exchange(tablesUrl, HttpMethod.POST, tablesRequestEntity, String.class);
                String tablesBody = tablesResponse.getBody();
                JSONObject tablesJsonObject = JSONObject.parseObject(tablesBody);
                String tablesResult = tablesJsonObject.getString("result");

            }
        }
    }

    /**
     * 连接MySQL数据库
     */
    public Map<String, Object> connectMySQL() {
        Connection connection = null;
        Statement statement = null;
        Map<String, Object> map = new HashMap<>();
        try {
            // 获取数据库连接
            connection = DriverManager.getConnection(url, username, password);
            // 根据连接获取可执行Statement
            statement = connection.createStatement();

            map.put("connection", connection);
            map.put("statement", statement);
            return map;
        } catch (Exception e) {
            e.printStackTrace();
            log.error("连接数据库错误！");
        }
        return null;
    }

    /**
     * 创建生成表的SQL
     *
     * @param tableModel
     * @return
     */
    public static String createTableSQL(TableModel tableModel) {
        String tableName = tableModel.getTableName();
        // 创建主键集合
        List<String> priKeyList = new ArrayList<String>();
        // 创建 StringBuffer 拼接sql
        StringBuffer sb = new StringBuffer();

        sb.append("CREATE TABLE `" + tableName.toUpperCase() + "` (\n");
        List<TableFieldModel> tableFields = tableModel.getTableFields();
        for (int i = 0; i < tableFields.size(); i++) {
            // 当前条数据
            TableFieldModel field = tableFields.get(i);
            // 判断数据类型
            String fieldType = judgeDataType(field.getFieldType());
            sb.append("" + field.getFieldName() + "");
            if ("double".equals(fieldType)) {
                // 追加列
                sb.append(" " + fieldType + "(" + field.getFieldLength() + "," + field.getDecimalPoint() + ") ");
            } else if ("decimal".equals(fieldType)) {
                // 追加列
                sb.append(" " + fieldType + "(" + field.getFieldLength() + "," + field.getDecimalPoint() + ") ");
            } else if ("datetime".equals(fieldType) || "longtext".equals(fieldType)) {
                // 追加列
                sb.append(" " + fieldType + " ");
            } else {
                // 追加列
                sb.append(" " + fieldType + "(" + field.getFieldLength() + ") ");
            }

            // 判断是否为主键 - 等于1是主键
            if ("1".equals(field.getPrimaryKey())) {
                // 字段名称放进去
                priKeyList.add(field.getFieldName());
                // 判断是否允许为空 等于1是允许为空; 只有不为空的时候，需要设置
                if (!"1".equals(field.getIsNull())) {
                    sb.append("NOT NULL COMMENT '" + field.getFieldRemark() + "',\n");
                }
                // 如果到了最后一条，并且只有一个主键时
                if (i >= tableFields.size() - 1 && priKeyList.size() == 1) {
                    sb.append("PRIMARY KEY (`" + priKeyList.get(0) + "`)");
                    sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;");

                } else if (i >= tableFields.size() - 1 && priKeyList.size() > 1) {
                    // 最后一条，并且存在多个主键时
                    sb.append("PRIMARY KEY (");
                    // 遍历主键集合
                    for (int j = 0; j < priKeyList.size(); j++) {
                        // 最后一个时
                        if (j == priKeyList.size() - 1) {
                            sb.append("`" + priKeyList.get(j) + "`) USING BTREE \n");
                        } else {
                            sb.append("`" + priKeyList.get(j) + "`,");
                        }
                    }
                    sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
                }
                // 非主键，直接判断是否允许为空
            } else {
                // 存在主键，并且为最后一个了
                if (priKeyList.size() > 0 && i >= tableFields.size() - 1) {
                    // 判断是否为空 if是可以为空
                    if ("1".equals(field.getIsNull())) {
                        sb.append("DEFAULT NULL COMMENT '" + field.getFieldRemark() + "',\n");
                    } else {
                        sb.append("NOT NULL COMMENT '" + field.getFieldRemark() + "',\n");
                    }
                    // 表示只有一个主键
                    if (priKeyList.size() == 1) {
                        sb.append("PRIMARY KEY (`" + priKeyList.get(0) + "`)\n");
                        sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
                    } else {
                        // 最后一条，并且存在多个主键时
                        sb.append("PRIMARY KEY (");
                        // 遍历主键集合
                        for (int j = 0; j < priKeyList.size(); j++) {
                            // 最后一个时
                            if (j == priKeyList.size() - 1) {
                                sb.append("`" + priKeyList.get(j) + "`) USING BTREE \n");
                            } else {
                                sb.append("`" + priKeyList.get(j) + "`,");
                            }
                        }
                        sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
                    }
                } else {
                    // 没有就追加 判断是否为空
                    if ("1".equals(field.getIsNull())) {
                        sb.append("DEFAULT NULL COMMENT '" + field.getFieldRemark() + "',\n");
                    } else {
                        sb.append("NOT NULL COMMENT '" + field.getFieldRemark() + "',\n");
                    }
                }
            }
        }
        return sb.toString();
    }

    /**
     * da
     * 创建批量插入数据的SQL
     *
     * @param tableName    表名
     * @param tableColumns 列名
     * @param dataList     数据
     * @return
     */
    public String insertDataSQLBatch(String tableName, List<String> tableColumns, List<List<String>> dataList) {
        StringBuilder sqlBuilder = new StringBuilder();
        StringBuilder batchValues = new StringBuilder();
        for (int i = 0; i < dataList.size(); i++) {
            List<String> rowData = dataList.get(i);
            if (!rowData.isEmpty()) {
                batchValues.append("(");
                //默认ID和时间
                String itemValues = "'" + UUID.randomUUID() + "','" + DateUtils.getDateNowString() + "'";
                for (int j = 0; j < rowData.size(); j++) {
                    Object value = rowData.get(j);
                    if (value instanceof Integer || value instanceof Long) {
                        itemValues += "," + value;
                    } else {
                        itemValues += ",'" + value + "'";
                    }
                }
                if (tableColumns.size() > rowData.size()) {
                    for (int t = 0; t < tableColumns.size() - rowData.size(); t++) {
                        itemValues += ",''";
                    }
                }
                batchValues.append(itemValues);
                batchValues.append(")");
                if (i < dataList.size() - 1) {
                    batchValues.append(",");
                }
            }
        }

        sqlBuilder.append("INSERT INTO " + tableName + " VALUES " + batchValues.toString() + ";\n");
        if (sqlBuilder != null) {
            return sqlBuilder.toString();
        }
        return null;
    }

    /**
     * 判断类型
     * varchar
     * int
     * double
     * datetime
     * decimal
     * text
     *
     * @param type
     * @return
     */
    private static String judgeDataType(String type) {
        switch (type) {
            case TYPE_STRING:
                return "varchar";
            case TYPE_INTEGER:
                return "int";
            case TYPE_DOUBLE:
                return "double";
            case TYPE_DATETIME:
                return "datetime";
            case TYPE_BIGDECIMAL:
                return "decimal";
            case TYPE_LONGTEXT:
                return "longtext";
            case TYPE_TEXT:
                return "text";
            default:
                return "varchar";
        }
    }

    ;

    //新增Excel数据源
    private String upload2Maas(InputStream inputStream, String hostAndPort, String filename) throws IOException {

        AmosRequestContext robotAuthentication = SpringContextHelper.getBean(AmosRequestContext.class);
        if (Objects.nonNull(robotAuthentication)) {
//            String token = robotAuthentication.getToken();
//            String product = robotAuthentication.getProduct();
//            String appKey = robotAuthentication.getAppKey();

            String token = "17c26e9a-6b54-448e-bea4-72c01990ae7e";  // 如果token、失效，就去浏览器复制最新的token、
            String product = "AMOS_STUDIO_WEB";
            String appKey = "AMOS_STUDIO";

            //1, 上传到maas
            String uploadUrl = "http://" + hostAndPort + "/maas/dsm/excel/upload";
            MultiValueMap<String, Object> params = new LinkedMultiValueMap<>();
            Resource resource = new InputStreamResource(inputStream) {
                @Override
                public long contentLength() throws IOException {
                    long size = inputStream.available();
                    return size;
                }

                @Override
                public String getFilename() {
                    return "xlsx";
                }
            };
            params.add("file", resource);
            HttpEntity<MultiValueMap<String, Object>> requestEntity = new HttpEntity<>(params, getHeader(token, product, appKey, hostAndPort, true));
            RestTemplate restTemplate = new RestTemplate();
            ResponseEntity<String> responseEntity = restTemplate.exchange(uploadUrl, HttpMethod.POST, requestEntity, String.class);
            String body = responseEntity.getBody();
            JSONObject jsonObject = JSONObject.parseObject(body);
            String result = jsonObject.getString("result");
            String path = jsonObject.getString("path");
            if (jsonObject.getString("status").equals("200")) {
                log.info("路径：" + path + "/" + result);
            } else {
                throw new RuntimeException("返回状态码错误");
            }

            // 2， 调用conn接口
            String connsUrl = "http://" + hostAndPort + "/maas/maas/desktop/conns";
            Map<String, String> params2 = new HashMap<>();
            params2.put("connType", "excel");
            params2.put("detail", "{\"location\":{\"type\":\"excel\",\"path\":\"" + result + "\"}}");
            params2.put("subjectid", "534de50c-bb8c-4450-87a0-557aac745a94");
            HttpEntity<Map<String, String>> requestEntity2 = new HttpEntity<>(params2, getHeader(token, product, appKey, hostAndPort, false));
            ResponseEntity<String> response2 = restTemplate.exchange(connsUrl, HttpMethod.POST, requestEntity2, String.class);
            String body2 = response2.getBody();
            JSONObject jsonObject2 = JSONObject.parseObject(body2);
            String connId = jsonObject2.getString("result");
            String pathConns = jsonObject2.getString("path");

//            String queryUrl = "http://" + hostAndPort + "/maas/maas/desktop/data/query?page=0&size=50";
//            Map<String, String> params3 = new HashMap<>();
//            params3.put("connId", connId);
//            params3.put("query", "{\"columnNameLineNumber\":1,\"skipEmptyLines\":true,\"skipEmptyColumns\":false,\"columnFormatter\":{},\"sheet\":\"Sheet1\",\"configed\":true}");

            //3， sheets
            String sheetsUrl = "http://" + hostAndPort + "/maas/dsm/excel/sheets";
            Map<String, String> sheetsParams = new HashMap<>();
            sheetsParams.put("fileName", result);
            HttpEntity<Map<String, String>> sheetsRequestEntity = new HttpEntity<>(sheetsParams, getHeader(token, product, appKey, hostAndPort, false));
            ResponseEntity<String> sheetsResponseEntity = restTemplate.exchange(sheetsUrl, HttpMethod.POST, sheetsRequestEntity, String.class);
            String sheetsResponseEntityBody = sheetsResponseEntity.getBody();
            JSONObject sheetJsonObject = JSONObject.parseObject(sheetsResponseEntityBody);
            JSONArray sheetArray = sheetJsonObject.getJSONArray("result");
            //result可能有多个sheet
            String[] sheets = new String[sheetArray.size()];
            for (int i = 0; i < sheetArray.size(); i++) {
                sheets[i] = (String) sheetArray.getJSONObject(i).get("sheet");
            }
            log.info("sheets的结果是:" + sheetsResponseEntityBody);


            // 4，
            String tablesUrl = "http://" + hostAndPort + "/maas/maas/desktop/tables";
            Map<String, String> params3 = new HashMap<>();
            params3.put("name", filename);
            params3.put("pid", "534de50c-bb8c-4450-87a0-557aac745a94");
            params3.put("connId", connId);
//            params3.add("config", sheetArray.get(0).toString());
            params3.put("config", "{\"columnNameLineNumber\":1,\"skipEmptyLines\":true,\"skipEmptyColumns\":false,\"columnFormatter\":{\"zztest\":{\"name\":\"zztest\",\"alias\":\"zztest\",\"type\":\"VARCHAR\"},\"zz\":{\"name\":\"zz\",\"alias\":\"zz\",\"type\":\"VARCHAR\"}},\"sheet\":\"Sheet1\"}");
            HttpEntity<Map<String, String>> requestEntity3 = new HttpEntity<>(params3, getHeader(token, product, appKey, hostAndPort, false));
            ResponseEntity<String> response3 = restTemplate.exchange(tablesUrl, HttpMethod.POST, requestEntity3, String.class);
            String body3 = response3.getBody();
            JSONObject jsonObject3 = JSONObject.parseObject(body3);
            String result2 = jsonObject3.getString("result");
            String path2 = jsonObject3.getString("path");


//            //datasource(name:excel+时间戳)
//            // 获取当前时间的时间戳
//            long timestamp = System.currentTimeMillis();
//            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//            String timestampStr = sdf.format(new Date(timestamp));
//            String datasourceUrl = "http://" + hostAndPort + "/maas/dsm/datasources";
//            JSONObject all = new JSONObject();
//            JSONObject oneJson = new JSONObject();
//            all.put("filepath",result);
//            for (int i = 0; i < sheetArray.size(); i++) {
//                oneJson.put(sheets[i],sheetArray.get(i));
//            }
//            all.put("config",oneJson);

//            Map<String, String> datasourceParams = new HashMap<>();
//            datasourceParams.put("detail", all.toString());
//            datasourceParams.put("groupid", "72684d79-5d28-4086-9f21-5b091b6675db");
//            datasourceParams.put("subtype", "excel");
//            datasourceParams.put("name", "excel"+timestampStr);
//            datasourceParams.put("type", "File");
//            HttpEntity<Map<String, String>> datasourceRequestEntity = new HttpEntity<>(datasourceParams, getHeader(token,product,appKey,hostAndPort,false));
//            ResponseEntity<String> datasourceResponseEntity = restTemplate.exchange(datasourceUrl, HttpMethod.POST, datasourceRequestEntity, String.class);
//            String datasourceResponseEntityBody = datasourceResponseEntity.getBody();
//            log.info("datasourceResponseEntityBody:" + datasourceResponseEntityBody);
//            return path + "/" + result;
        }
        return null;
    }

    //根据机器人生成的校验参数将其放入请求头
    private HttpHeaders getHeader(String token, String product, String appKey, String hostAndPort, Boolean isUpload) {
        HttpHeaders header = new HttpHeaders();
        header.add(Constant.TOKEN, token);
        header.add(Constant.PRODUCT, product);
        header.add(Constant.APPKEY, appKey);
        if (isUpload) {
            header.setContentType(MediaType.MULTIPART_FORM_DATA);
        } else {
            header.setContentType(MediaType.APPLICATION_JSON);
        }
        return header;
    }


    public void findDelimiter(InputStream is, String delimiter) throws IOException {
        StringBuilder sb = new StringBuilder();
        int nextByte;
        while ((nextByte = is.read()) != -1) {
            char c = (char) nextByte;
            sb.append(c);
            if (sb.toString().contains(delimiter)) {
                return;
            }
        }
        throw new IOException("没有开始分隔符");
    }

    public static byte[] checkDelimiter(byte[] buf, int byteRead, byte[] delimiter) {
        if (delimiter.length > buf.length) return buf;

        for (int i = 0; i <= buf.length - delimiter.length; i++) {
            boolean isMatch = true;
            for (int j = 0; j < delimiter.length; j++) {
                if (buf[i + j] != delimiter[j]) {
                    isMatch = false;
                    break;
                }
            }
            if (isMatch) {
                return Arrays.copyOf(buf, i);
            }
        }

        return buf;
    }


    public String filenameFilter(InputStream is) throws IOException {
        BufferedInputStream bis = new BufferedInputStream(is);

        String start = "##STATITLE##";
        String end = "##ENDTITLE##";

        //校验内容格式
        findDelimiter(bis, start);

        byte[] endDelimiter = end.getBytes();
        int byteRead;
        byte[] buf = new byte[4096];
        if ((byteRead = bis.read(buf)) != -1) {
            byte[] newByte = checkDelimiter(buf, byteRead, endDelimiter);
            return new String(newByte, "GBK");
        }
        throw new IOException("没有输入流内容或者文件名太长");
    }

    public InputStream fileInputStreamFilter(InputStream is, String postfix) throws IOException {
        BufferedInputStream bis = new BufferedInputStream(is);
        String fileTmpPath = "xls".equals(postfix) ? "D:\\tmp.xls" : "D:\\tmp.xlsx";
        FileOutputStream fos = new FileOutputStream(fileTmpPath);

        String start = "##STAFILE##";
        String end = "##ENDFILE##";

        //校验内容格式
        findDelimiter(bis, start);

        byte[] endDelimiter = end.getBytes();
        int byteRead;
        byte[] buf = new byte[4000];
        while ((byteRead = bis.read(buf)) != -1) {
            byte[] newByte = checkDelimiter(buf, byteRead, endDelimiter);
            fos.write(newByte, 0, Math.min(byteRead, newByte.length));
        }
        return new FileInputStream(fileTmpPath);
    }


    //截取文件流
    public static InputStream intercept(InputStream is) throws IOException {


        BufferedReader reader = new BufferedReader(new InputStreamReader(is));
        StringBuilder sb = new StringBuilder();
        String line = null;
        try {
            while ((line = reader.readLine()) != null) {
                sb.append(line + "/n");
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        String string1 = sb.toString();

//        int fileStart = string1.indexOf(".xlsx") + 5;
////        int fileStart = string1.indexOf(".txt") + 4;
//        String file = string1.substring(fileStart);
//
//        InputStream fileInputStream = new ByteArrayInputStream(file.getBytes());
//        return fileInputStream;

//
        int startFilePath = string1.indexOf("##STATITLE##");
        int endFilePath = string1.indexOf("##ENDTITLE##");
        int startFile = string1.indexOf("##STAFILE##");
        int endFile = string1.indexOf("##ENDFILE##");

        if (startFilePath < 0) {
            log.info("不存在文件名开始标志");
            return null;
        }
        if (endFilePath < 0) {
            log.info("不存在文件名结束标志");
            return null;
        }
        if (startFile < 0) {
            log.info("不存在文件开始标志");
            return null;
        }
        if (endFile < 0) {
            log.info("不存在文件结束标志");
            return null;
        }

        String filePath = string1.substring(startFilePath, endFilePath).substring("##STATITLE##".length());
        log.info("文件名：" + filePath);
        String file = string1.substring(startFile, endFile).substring("##STAFILE##".length());
        log.info("文件：" + file);
        System.out.println("file size " + file.length());

//        FileOutputStream fileOutputStream = new FileOutputStream(new File("D:\\kgdcz\\data\\maas\\test.xlsx"));
//        fileOutputStream.write(file.getBytes());
//        fileOutputStream.close();

        InputStream fileInputStream = new ByteArrayInputStream(file.getBytes());
        return fileInputStream;

    }


    private static void requestInfoToSocketServer() {
        try {
            Socket socket = new Socket("127.0.0.1", 7777);
//            Socket socket = new Socket("localhost", 7777);
            OutputStream ops = socket.getOutputStream();
            FileInputStream fis = new FileInputStream("D:\\SamData\\RecordXLS\\測試\\DYKZZH2023年04月13日14时49分36秒.xls");
            int len = 0;
            byte[] bs = new byte[20480];
            while ((len = fis.read(bs)) != -1) {
                ops.write(bs, 0, len);
            }
            socket.shutdownOutput();
            ops.flush();
            //开始接收服务端的消息
            BufferedReader in = new BufferedReader(new InputStreamReader(socket.getInputStream()));
            log.info("接收到服务端的回复:" + in.readLine());
        } catch (Exception e) {
            log.info("Socket传输数据异常!" + e.getMessage());
        }
    }

    public static void main(String[] args) throws Exception {
        requestInfoToSocketServer();
    }
}