package com.ltkj.web.controller.his;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.ltkj.common.core.domain.AjaxResult;
import com.ltkj.hosp.domain.ApiConfig;
import com.ltkj.hosp.domain.HisApiConfig;
import com.ltkj.hosp.service.ApiConfigService;
import com.ltkj.hosp.service.HisApiConfigService;
import com.ltkj.web.wxUtils.HttpClientUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.util.stream.Collectors;

/**
 * @Company: 西安路泰科技有限公司
 * @Author: zhaowenxuan
 * @Date: 2024/5/31 14:38
 */
@Slf4j
@Component
public class HisApiGetMethodService {


    // 数据库配置文件路径
//    private static final String CONFIG_PATH = url;
//    private static final String CONFIG_PATH = "/Users/chacca/开发相关/代码/ltkj_peis/ltkj-admin/src/main/resources/config.properties";
    // 数据库名
    private static String DB_NAME = "";
    private static String HIS_URL = "";
    private static String DATA_URL = "";
    private static String DATA_USER ="";
    private static String DATA_PASS = "";
    private static String LIS_URL = "";
    private static String PACS_URL = "";
    @Autowired
    private HisApiMethodService controller;
    @Autowired
    private HisApiConfigService hisApiConfigService;
    @Autowired
    private ApiConfigService apiConfigService;

    private static String url;


    @Value ("${config.properties}")
    public  void set(String urls){
         url=urls;
        FileInputStream inputStream = null;
        try {
            inputStream = new FileInputStream(url);
            Properties props = new Properties();
            props.load(inputStream);
            DB_NAME = props.getProperty("name");
            DATA_URL = "jdbc:mysql://" + props.getProperty("ip") + ":" + props.getProperty("prot") + "/" + DB_NAME + "" +
                    "?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8";
            DATA_USER = props.getProperty("username");
            DATA_PASS = props.getProperty("password");
            String apiUrl = props.getProperty("his_api_url");
            String apiPort = props.getProperty("his_api_port");
            HIS_URL = apiUrl+":"+apiPort;
            String lisApiUrl = props.getProperty("lis_api_url");
            String lisApiPort = props.getProperty("lis_api_port");
            LIS_URL = lisApiUrl+":"+lisApiPort;
            String pacsApiUrl = props.getProperty("pacs_api_url");
            String pacsApiPort = props.getProperty("pacs_api_port");
            PACS_URL = pacsApiUrl+":"+pacsApiPort;
        } catch (IOException e) {
            System.out.println("初始化数据库异常 ->"+e.getMessage());
        }
    }

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            log.error(String.valueOf(e),e.getMessage());
        }
//        try {
//            InputStreamReader reader = new InputStreamReader(new FileInputStream(url), StandardCharsets.UTF_8);
//            Properties props = new Properties();
//            props.load(reader);
//            String api_url = props.getProperty("his_api_url");
//            String port = props.getProperty("his_api_port");
//            DB_NAME = props.getProperty("name");
//            DATA_URL = "jdbc:mysql://" + props.getProperty("ip") + ":" + props.getProperty("prot") + "/" + DB_NAME +
//                    "?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8";
//            DATA_USER = props.getProperty("username");
//            DATA_PASS = props.getProperty("password");
//            HIS_URL=api_url+":"+port;
//        } catch (IOException throwables) {
//            throwables.printStackTrace();
//        }
    }


    /**
     * 获取数据
     *
     * @param type   接口代码
     * @param params 请求参数
     * @return
     * @deprecated
     * 方法已弃用,将switch更改为数据库匹配 使用新方法{@link #getHISDataNew(String, Map)}
     */
    @Deprecated
    @Transactional
    public AjaxResult getHISData(String type, Map<String, Object> params) {
        AjaxResult result = null;
        // TODO 创建表 弄成数据库动态获取 接口地址、代码、是否分页、主键id、主键字段
        // TODO 从2.4.2获取到数据 根据his_registration_id删除旧数据在插入新数据 下层业务均一样根据返回的JYBGID删除旧数据在插入新数据
        switch (type) {
            case "Getoutaccountrecord":
                result = controller.Getoutaccountrecord(params);
//                result.put("limit", true);
                break;
            case "Getoutpatientcostinfo":
                result = controller.Getoutpatientcostinfo(params);
//                result.put("limit", true);
                break;
            case "Getlaburgentinfo":
                result = controller.Getlaburgentinfo(params);
//                result.put("limit", false);
                break;
            case "Getlabgermdetailinfo":
                result = controller.Getlabgermdetailinfo(params);
//                result.put("limit", false);
                break;
            case "Getlabgermrepinfo":
                result = controller.Getlabgermrepinfo(params);
//                result.put("limit", false);
                break;
            case "Getlabreportinfo":
                result = controller.Getlabreportinfo(params);
//                result.put("limit", true);
                break;
            case "Getlabapplyinfo":
                result = controller.Getlabapplyinfo(params);
//                result.put("limit", false);
                break;
            case "Getexamurgentinfo":
                result = controller.Getexamurgentinfo(params);
//                result.put("limit", true);
                break;
            case "Getexamreportinfo":
                result = controller.Getexamreportinfo(params);
//                result.put("limit", true);
                break;
            case "Getexamapplyinfo":
                result = controller.Getexamapplyinfo(params);
//                result.put("limit", false);
                break;
            case "Getlabdetailinfo":
                result = controller.Getlabdetailinfo(params);
//                result.put("limit", false);
                break;
        }
        String json = result.get("data").toString();
        JSONObject response = JSONUtil.parseObj(json).getJSONObject("Response");
        if (response.getStr("ResultCode").toString().equals("0")) {
            AjaxResult ajaxResult = AjaxResult.success();
//            if ((boolean) result.get("limit")) {
//                ajaxResult = saveArray(json, type);
            JSONArray resultData = response.getJSONArray("ResultData");
            List<Map<String, String>> list = new ArrayList<>();
            for (Object resultDatum : resultData) {
                JSONObject object = (JSONObject) resultDatum;
                ajaxResult = save(object, type,null,JSONUtil.toJsonStr(params));
                Map<String, String> map = new HashMap<>();
                for (String key : object.keySet()) {
                    String defaultVal = object.get(key).toString();
                    String val = defaultVal.trim().replaceAll("\\s+", "");
                    map.put(key, val);
                }
                list.add(map);
            }
            ajaxResult.put("data", list);
//            } else {
//                // 以集合返回
//                JSONArray resultData = response.getJSONArray("ResultData");
//                List<JSONObject> list = JSONUtil.toList(resultData, JSONObject.class);
//                ArrayList<Map<String, String>> maps = new ArrayList<>();
//                for (JSONObject data : list) {
//                    ajaxResult = save(data, type);
//                    HashMap<String, String> map1 = new HashMap<>();
//                    for (String key : data.keySet()) {
//                        String defaultVal = data.get(key).toString();
//                        String val = defaultVal.trim().replaceAll("\\s+", "");
//                        map1.put(key, val);
//                    }
//                    maps.add(map1);
//                }
//                ajaxResult.put("data", maps);
//            }
            return ajaxResult;
        } else {
            return AjaxResult.error().put("result", JSONUtil.parseObj(json));
        }
    }

//    @Transactional
    public AjaxResult getHISDataNew(String type, Map<String, Object> params){
        try {
            LambdaQueryWrapper<HisApiConfig> lambdaQueryWrapper = new LambdaQueryWrapper<>();
            lambdaQueryWrapper.eq(HisApiConfig::getApiMethod, type);
            HisApiConfig hisApiConfig = hisApiConfigService.getOne(lambdaQueryWrapper);
            String responseJson = HttpClientUtils.sendPost(HIS_URL + hisApiConfig.getApiUrl(), params);
            Integer isResponse = hisApiConfig.getIsResponse();
            JSONObject response = null;
            if (isResponse == 1) {
                response = JSONUtil.parseObj(responseJson).getJSONObject("Response");
            } else{
                response = JSONUtil.parseObj(responseJson);
            }
            if (response.getStr(hisApiConfig.getResultCodeKey()).equals("0")) {
                AjaxResult ajaxResult = AjaxResult.success();
                JSONArray resultData = response.getJSONArray(hisApiConfig.getResultDataKey());
                List<Map<String, String>> list = new ArrayList<>();
                for (Object resultDatum : resultData) {
                    JSONObject object = (JSONObject) resultDatum;
                    ajaxResult = save(object, type,hisApiConfig,JSONUtil.toJsonStr(params));
//                    save(object, type,hisApiConfig,JSONUtil.toJsonStr(params));
                    Map<String, String> map = new HashMap<>();
                    for (String key : object.keySet()) {
                        String defaultVal = object.get(key).toString();
                        String val = defaultVal.trim().replaceAll("\\s+", "");
                        map.put(key, val);
                    }
                    list.add(map);
                }
                ajaxResult.put("data", list);
                return ajaxResult;
            } else {
                return AjaxResult.error().put("result", JSONUtil.parseObj(responseJson));
            }
        } catch (Exception e) {
//            throw new RuntimeException(e);
            log.error(String.valueOf(e),e.getMessage());
            return AjaxResult.error();
        }
    }

    public AjaxResult getLisData(String type,Map<String ,Object> params){
        LambdaQueryWrapper<ApiConfig> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(ApiConfig::getType,"lis");
        wrapper.eq(ApiConfig::getApiMethod,type);
        return getAjaxResult("lis", params, wrapper);
    }

    public AjaxResult getPacsData(String type,Map<String ,Object> params){
        LambdaQueryWrapper<ApiConfig> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(ApiConfig::getType,"pacs");
        wrapper.eq(ApiConfig::getApiMethod,type);
        return getAjaxResult("pacs", params, wrapper);
    }

    private AjaxResult getAjaxResult(String type, Map<String, Object> params, LambdaQueryWrapper<ApiConfig> wrapper) {
        ApiConfig apiConfig = apiConfigService.getOne(wrapper);
        String responseJson;
        switch (type){
            case "pacs":
                responseJson = HttpClientUtils.sendPost(PACS_URL + apiConfig.getApiUrl(), params);
                break;
            default:
                responseJson = HttpClientUtils.sendPost(LIS_URL + apiConfig.getApiUrl(), params);
                break;
        }
        Integer isResponse = apiConfig.getIsResponse();
        JSONObject response = null;
        if (isResponse == 1) {
            response = JSONUtil.parseObj(responseJson).getJSONObject("Response");
        } else{
            response = JSONUtil.parseObj(responseJson);
        }
        if (response.getStr(apiConfig.getResultCodeKey()).equals("1")) {
            AjaxResult ajaxResult = AjaxResult.success();
            if (response.getStr(apiConfig.getResultDataKey()) != null && StrUtil.isNotBlank(response.getStr(apiConfig.getResultDataKey()))){
                JSONArray resultData = response.getJSONArray(apiConfig.getResultDataKey());
                List<Map<String, String>> list = new ArrayList<>();
                for (Object resultDatum : resultData) {
                    JSONObject object = (JSONObject) resultDatum;
                    ajaxResult = save(object, type,apiConfig,JSONUtil.toJsonStr(params));
                    Map<String, String> map = new HashMap<>();
                    for (String key : object.keySet()) {
                        String defaultVal = object.get(key).toString();
                        String val = defaultVal.trim().replaceAll("\\s+", "");
                        map.put(key, val);
                    }
                    list.add(map);
                }
                ajaxResult.put("data", list);
            }else {
                ajaxResult.put("data",response);
            }
            return ajaxResult;
        } else {
            return AjaxResult.error().put("result", JSONUtil.parseObj(responseJson));
        }
    }

    /**
     * ResultData为对象
     * 根据his接口返回值 插入数据
     * 如果没有表则创建表 接口增加了字段则为表增加字段
     *
     * @param code 接口代码
     * @return 执行是否成功
     */
    public AjaxResult save(JSONObject object, String code,Object hisApiConfig,String paramsJson) {
        String tabName = "";
        if (code.equals("lis") || code.equals("pacs"))
            tabName = ((ApiConfig)hisApiConfig).getTabName();
        else tabName = ((HisApiConfig) hisApiConfig).getTabName();
        Connection connection = getConnection();
        Boolean isExists = tabIsExists(connection, tabName);
        if (null == isExists)
            return AjaxResult.error();
        if (!isExists) {
            try {
                creatTable(object, tabName, connection);
            } catch (SQLException e) {
                log.error(String.valueOf(e),e.getMessage());
//                return AjaxResult.error();
            }
        }
        //插入数据
        try {
            operationTable(object,connection,hisApiConfig,paramsJson,code);
        } catch (SQLException e) {
            log.error(String.valueOf(e),e.getMessage());
//            return AjaxResult.error();
        }finally {
            try {
                connection.close();
            } catch (SQLException ignored) {
            }
        }
        return AjaxResult.success();
    }

    /**
     * 操作表
     *
     * @param connection
     * @throws SQLException
     */
    private void operationTable(JSONObject jsonObject, Connection connection,Object hisApiConfig,String paramsJson,String code) throws SQLException {
        String tabName = "";
        if (code.equals("lis") || code.equals("pacs"))
            tabName = ((ApiConfig)hisApiConfig).getTabName();
        else tabName = ((HisApiConfig) hisApiConfig).getTabName();
        List<String> columns = getColumns(tabName, connection);
        ArrayList<String> responseColums = new ArrayList<>();
        for (Map.Entry<String, Object> entry : jsonObject.entrySet()) {
            String key = entry.getKey().trim().toLowerCase();
            responseColums.add(key);
        }
        Map<String, Object> beanToMap = BeanUtil.beanToMap(JSONUtil.parseObj(paramsJson));
        Set<Map.Entry<String, Object>> entries = beanToMap.entrySet();
        for (Map.Entry<String, Object> entry : entries) {
            String key = "request_params_"+entry.getKey().trim().toLowerCase();
            if (!columns.contains(key))
                responseColums.add(key);
        }
        responseColums.removeAll(columns);
        if (!responseColums.isEmpty()) {
            for (String colum : responseColums) {
                String sql = "alter table " + tabName + " add column " + colum + " VARCHAR(200) null";
                Statement statement = connection.createStatement();
                statement.executeUpdate(sql);
                statement.close();
            }
            insertDataNew(connection, jsonObject,hisApiConfig,paramsJson,code);
        } else {
            insertDataNew(connection, jsonObject,hisApiConfig,paramsJson,code);
        }
    }

    /**
     * 插入数据
     *
     * @param hisApiConfig
     * @param connection
     * @param jsonObject
     * @throws SQLException
     */
    private void insertData(HisApiConfig hisApiConfig, Connection connection, JSONObject jsonObject) throws SQLException {
        StringBuilder insertSqlBuilder = new StringBuilder();
        StringBuilder valueBuilder = new StringBuilder();
        StringBuilder selectSqlBuilder = new StringBuilder();
        insertSqlBuilder.append("insert into ").append(hisApiConfig.getTabName()).append(" (");
        selectSqlBuilder.append("select count(1) as count from ").append(hisApiConfig.getTabName()).append(" where ");
        for (Map.Entry<String, Object> entry : jsonObject.entrySet()) {
            String defaultVal = entry.getValue().toString();
            String key = entry.getKey().trim().toLowerCase();
            if (StrUtil.isBlank(defaultVal) || defaultVal.equals("null"))
                continue;
            String val = defaultVal.trim().replaceAll("\\s+", "");
            insertSqlBuilder.append(key).append(", ");
            valueBuilder.append("'").append(val).append("', ");
            selectSqlBuilder.append(key).append(" = '").append(val).append("' and ");
            // 判断数据是否大于字段创建值如果大于则增加超过阈值则使用text
            String columnType = getColumnType(connection, hisApiConfig.getTabName(), key);
            if (!"text".equalsIgnoreCase(columnType)){
                int currentSize = getColumnSize(connection, hisApiConfig.getTabName(), key);
                if (currentSize < val.length()){
                    if (val.length()>= 1000){
                        alterColumnTypeToText(connection, hisApiConfig.getTabName(), key);
                    }else {
                        int newSize = ((val.length() / 100) + 1) * 100;
                        alterColumnSize(connection, hisApiConfig.getTabName(), key, newSize);
                    }
                }
            }
        }
        selectSqlBuilder.delete(selectSqlBuilder.length() - 5, selectSqlBuilder.length());
        Statement statement = connection.prepareStatement(selectSqlBuilder.toString());
        ResultSet resultSet = statement.executeQuery(selectSqlBuilder.toString());
        resultSet.next();
        String string = resultSet.getString("count");
        statement.close();
        // 如果不为0 则这条数据存在 不进行插入
        if (!"0".equals(string)) {
//            log.info("数据存在不需要插入 {}", jsonObject);
            return;
        }
        insertSqlBuilder.append("insert_time, ");
        String time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
        valueBuilder.append("'").append(time).append("'").append(", ");
        insertSqlBuilder.delete(insertSqlBuilder.length() - 2, insertSqlBuilder.length());
        valueBuilder.delete(valueBuilder.length() - 2, valueBuilder.length());
        insertSqlBuilder.append(") values (").append(valueBuilder).append(")");
        // 插入数据
        statement = connection.createStatement();
        statement.execute(insertSqlBuilder.toString());
        statement.close();
    }

    /**
     * 插入数据
     *
     * @param connection
     * @param jsonObject
     * @throws SQLException
     */
    @Transactional
    public void insertDataNew(Connection connection, JSONObject jsonObject, Object hisApiConfig, String paramsJson, String code) throws SQLException {
        String tabName = "";
        List<String> primaryKesList;
        if (code.equals("lis") || code.equals("pacs")) {
            tabName = ((ApiConfig) hisApiConfig).getTabName();
            primaryKesList = Arrays.stream(((ApiConfig)hisApiConfig).getPrimaryKeys().split(",")).map(String::toLowerCase).collect(Collectors.toList());
        } else {
            tabName = ((HisApiConfig) hisApiConfig).getTabName();
            primaryKesList = Arrays.stream(((HisApiConfig)hisApiConfig).getPrimaryKeys().split(",")).map(String::toLowerCase).collect(Collectors.toList());
        }
        StringBuilder deleteSqlBuilder = new StringBuilder();
        StringBuilder insertSqlBuilder = new StringBuilder();
        StringBuilder valueBuilder = new StringBuilder();
        insertSqlBuilder.append("insert into ").append(tabName).append(" (");
        deleteSqlBuilder.append("delete from ").append(tabName).append(" where ");
        for (Map.Entry<String, Object> entry : jsonObject.entrySet()) {
            String defaultVal = entry.getValue().toString();
            String key = entry.getKey().trim().toLowerCase();
            if (StrUtil.isBlank(defaultVal) || defaultVal.equals("null"))
                continue;
            String val = defaultVal.trim().replaceAll("\\s+", "").replaceAll("[^\\x00-\\x7F]", "");
            if (primaryKesList.contains(key)) {
                deleteSqlBuilder.append(key).append(" = '").append(val).append("' and ");
            }
            insertSqlBuilder.append(key).append(", ");
            valueBuilder.append("'").append(val).append("', ");
            // 判断数据是否大于字段创建值如果大于则增加超过阈值则使用text
            String columnType = getColumnType(connection, tabName, key);
            if (!"text".equalsIgnoreCase(columnType)){
                int currentSize = getColumnSize(connection, tabName, key);
                if (currentSize < val.length()){
                    if (val.length()>= 1000){
                        alterColumnTypeToText(connection, tabName, key);
                    }else {
                        int newSize = ((val.length() / 100) + 1) * 100;
                        alterColumnSize(connection, tabName, key, newSize);
                    }
                }
            }
        }
        deleteSqlBuilder.delete(deleteSqlBuilder.length() - 5, deleteSqlBuilder.length());
        Statement statement = connection.prepareStatement(deleteSqlBuilder.toString());
        log.info("HIS数据保存类 执行删除 ->{}",deleteSqlBuilder.toString());
        int i = statement.executeUpdate(deleteSqlBuilder.toString());
        log.info("HIS数据保存类 删除条数 ->{}",i);
        statement.close();
        insertSqlBuilder.append("insert_time, ");
        insertSqlBuilder.append("request_params, ");
        DatabaseMetaData metaData = connection.getMetaData();
        try (ResultSet resultSet = metaData.getColumns(null, null, tabName, "request_params")) {
            if (!resultSet.next()){
                String alterTableSQL = String.format("ALTER TABLE %s ADD COLUMN %s %s", tabName, "request_params", "text");
                statement = connection.createStatement();
                statement.execute(alterTableSQL);
            }
        }
        String time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
        valueBuilder.append("'").append(time).append("'").append(", ");
        valueBuilder.append("'").append(paramsJson).append("'").append(", ");
        Map<String, Object> beanToMap = BeanUtil.beanToMap(JSONUtil.parseObj(paramsJson));
        Set<Map.Entry<String, Object>> entries = beanToMap.entrySet();
        for (Map.Entry<String, Object> entry : entries) {
            String key = "request_params_"+entry.getKey().trim().toLowerCase();
            insertSqlBuilder.append(key).append(", ");
            valueBuilder.append("'").append(entry.getValue().toString()).append("', ");
        }
        insertSqlBuilder.delete(insertSqlBuilder.length() - 2, insertSqlBuilder.length());
        valueBuilder.delete(valueBuilder.length() - 2, valueBuilder.length());
        insertSqlBuilder.append(") values (").append(valueBuilder).append(")");
//        log.info("执行插入 ->{}",insertSqlBuilder.toString());
        // 插入数据
//        boolean execute = statement.execute(insertSqlBuilder.toString());
//        for (int j = 0; j < 10; j++) {
//            statement = connection.createStatement();
//            log.info("HIS数据保存类 执行插入 ->{}",insertSqlBuilder.toString());
//            boolean execute = statement.execute(insertSqlBuilder.toString());
//            log.info("HIS数据保存类 执行插入状态 ->{},次数->{}",execute,(j+1));
//            statement.close();
//            if (execute)
//                break;
//        }
        statement = connection.createStatement();
        log.info("HIS数据保存类 执行插入 ->{}",insertSqlBuilder.toString());
        boolean execute = statement.execute(insertSqlBuilder.toString());
        if (!execute){
            int updateCount = statement.getUpdateCount();
            log.info("HIS数据保存类 执行插入成功 影响行数->{}",updateCount);
        }
        statement.close();
    }

    /**
     * 获取列类型
     * @param connection
     * @param tableName
     * @param columnName
     * @return
     * @throws SQLException
     */
    private static String getColumnType(Connection connection, String tableName, String columnName) throws SQLException {
        String query = "SELECT DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_NAME = ? AND COLUMN_NAME = ?";
        try (PreparedStatement statement = connection.prepareStatement(query)) {
            statement.setString(1, tableName);
            statement.setString(2, columnName);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getString("DATA_TYPE");
            }
        }
        return "";
    }

    /**
     * 获取列长度
     * @param connection
     * @param tableName
     * @param columnName
     * @return
     * @throws SQLException
     */
    private static int getColumnSize(Connection connection, String tableName, String columnName) throws SQLException {
        String query = "SELECT CHARACTER_MAXIMUM_LENGTH FROM information_schema.COLUMNS WHERE TABLE_NAME = ? AND COLUMN_NAME = ?";
        try (PreparedStatement statement = connection.prepareStatement(query)) {
            statement.setString(1, tableName);
            statement.setString(2, columnName);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt("CHARACTER_MAXIMUM_LENGTH");
            }
        }
        return 0;
    }

    /**
     * 修改列长度
     * @param connection
     * @param tableName
     * @param columnName
     * @param newSize
     * @throws SQLException
     */
    private static void alterColumnSize(Connection connection, String tableName, String columnName, int newSize) throws SQLException {
        String alterQuery = String.format("ALTER TABLE %s MODIFY %s VARCHAR(%d)", tableName, columnName, newSize);
        try (Statement statement = connection.createStatement()) {
            statement.executeUpdate(alterQuery);
        }
    }

    /**
     * 超过阈值则修改为text类型
     * @param connection
     * @param tableName
     * @param columnName
     * @throws SQLException
     */
    private static void alterColumnTypeToText(Connection connection, String tableName, String columnName) throws SQLException {
        String alterQuery = String.format("ALTER TABLE %s MODIFY %s TEXT", tableName, columnName);
        try (Statement statement = connection.createStatement()) {
            statement.executeUpdate(alterQuery);
        }
    }

    /**
     * 获取表的列
     *
     * @param tabName
     * @param connection
     * @return
     * @throws SQLException
     */
    private List<String> getColumns(String tabName, Connection connection) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet columns = metaData.getColumns(null, null, tabName, null);
        ArrayList<String> tabColumns = new ArrayList<>();
        while (columns.next()) {
            String columnName = columns.getString("column_name");
            tabColumns.add(columnName);
        }
        return tabColumns;
    }

    /**
     * 创建表
     *
     * @param data
     * @param tabName
     * @param connection
     * @throws SQLException
     */
    private void creatTable(JSONObject data, String tabName, Connection connection) throws SQLException {
        StringBuilder sql = new StringBuilder("CREATE TABLE " + tabName + " (");
        for (Map.Entry<String, Object> entry : data.entrySet()) {
            String key = entry.getKey().trim().toLowerCase();
            sql.append(key).append(" VARCHAR(200) null,");
        }
        sql.append("insert_time").append(" VARCHAR(200) null,");
        sql.append("request_params").append(" text null COMMENT '请求参数',");
        sql = new StringBuilder(sql.substring(0, sql.length() - 1));
        sql.append(");");
        Statement statement = connection.createStatement();
        statement.execute(sql.toString());
    }

    /**
     * 判断表是否存在
     *
     * @param connection
     * @param tableName
     * @return
     */
    private Boolean tabIsExists(Connection connection, String tableName) {
        String tabSql = "SELECT table_name FROM information_schema.tables WHERE table_schema = ? AND table_name = ?";
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(tabSql);
            statement.setString(1, DB_NAME);
            statement.setString(2, tableName);
            ResultSet resultSet = statement.executeQuery();
            boolean next = resultSet.next();
            statement.close();
            return next;
        } catch (SQLException throwables) {
            log.error(String.valueOf(throwables),throwables.getMessage());
        }
        log.error("查询表执行sql返回为null ->{},{},{}",tabSql,DB_NAME,tableName);
        return null;
    }

    /**
     * 获取数据库连接
     *
     * @return
     */
    private Connection getConnection() {
        try {
            return DriverManager.getConnection(DATA_URL, DATA_USER, DATA_PASS);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}