package com.example.service.shanjianyi.shanxiqin.baoji.bjsqyy;

import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.example.domain.DictCommonHisConfig;
import com.example.domain.HisSyncDict;
import com.example.mapper.HisSyncDictMapper;
import com.example.service.DictCommonHisConfigService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @Company: 西安路泰科技有限公司
 * @Author: zhaowenxuan
 * @Date: 2025/3/18 11:19
 */
@Slf4j
@Component
public class DictionaryUtilShanXiBaoJiBjsqyy {

    @Autowired
    private HisSyncDictMapper hisSyncDictMapper;
    @Autowired
    private DictCommonHisConfigService dictCommonHisConfigService;

    private static final int SIZE = 100;

    // 分页每次1000条
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (Exception e) {
            System.out.println("静态代码块异常 ->"+e.getMessage());
        }
    }

    /**
     * 进行同步
     */
    public void exec(List<HisSyncDict> hisSyncDicts, String token){
        DictCommonHisConfig config = dictCommonHisConfigService.getConfigByHospital("bjsqyy");
        log.info("开始执行同步");
        Connection connection = getConnection(config);
        if (connection == null)
            return;
        for (HisSyncDict hisSyncDict : hisSyncDicts) {
            boolean isFormatJson = false;
            if (hisSyncDict.getDictName().equals("getKeShizd")
                    || hisSyncDict.getDictName().equals("getKeShiByConditionszd")
                    || hisSyncDict.getDictName().equals("getShouFeiXmzd")
                    || hisSyncDict.getDictName().equals("getListYiShengZd")
            )
                isFormatJson = true;
            hisSyncDict.setDictName(hisSyncDict.getHospId()+"_"+hisSyncDict.getDictName());
            extracted(config.getDbName(), connection, hisSyncDict,token,isFormatJson,config);
        }
        try {
            connection.close();
        } catch (SQLException ignored) { }
        hisSyncDictMapper.proSyncCommonDict();
        log.info("执行同步完毕");
    }

    private void extracted(String name, Connection connection, HisSyncDict hisSyncDict,String token,Boolean isFormatJson,DictCommonHisConfig config) {
        String method = hisSyncDict.getDictName();
        log.info("开始请求代码 ->{}", method);
        String tabName = "ltkj_" + method.toLowerCase();
        boolean isLimit = false;
        int page = 1;
        int maxPage = 1;
        String params = hisSyncDict.getParams();
        params = params.replace("${pageIndex}",String.valueOf(page));
        params = params.replace("${pageSize}",String.valueOf(SIZE));
        JSONObject object = JSONUtil.parseObj(params);
        if (hisSyncDict.getIsLimit() == 1) {
            isLimit = true;
        }
        log.info("请求接口 ->{}, 请求参数 ->{}", config.getHisUrl()+hisSyncDict.getUrl(), object);
        JSONObject entries = execRequest(config,object.toString(),hisSyncDict,token);
        log.info("请求返回 ->{}", entries.toString());
        if (isFormatJson){
            if ("1".equals(entries.getStr("returnCode"))) {
                JSONObject response = entries.getJSONObject("returnData");
                try {
                    dropTable(tabName, connection);
                } catch (SQLException e) {
                    log.error("删除表异常 ->{}", e.getMessage());
                }
                if (isLimit) {
                    maxPage = LimitInsertData(connection, tabName, response, name, page);
                    log.info("计算页码为 ->{}", maxPage);
                    if (maxPage > 1) {
                        for (page = 2; page <= maxPage; page++) {
                            params = hisSyncDict.getParams();
                            params = params.replace("${pageIndex}",String.valueOf(page));
                            params = params.replace("${pageSize}",String.valueOf(SIZE));
                            object = JSONUtil.parseObj(params);
                            log.info("请求接口 ->{}, 请求参数 ->{}", config.getHisUrl()+hisSyncDict.getUrl(), object.toString());
                            entries = execRequest(config,object.toString(),hisSyncDict,token);
                            log.info("请求返回 ->{}", entries.toString());
                            response = entries.getJSONObject("returnData");
                            if ("1".equals(entries.getStr("returnCode"))) {
                                LimitInsertData(connection, tabName, response, name, page);
                            } else {
                                log.error("{} 请求失败：{}", method, object.toString());
                            }
                        }
                    }
                } else {
                    JSONArray resultData = response.getJSONArray("list");
                    List<JSONObject> list = JSONUtil.toList(resultData, JSONObject.class);
                    for (JSONObject jsonObject : list) {
                        editDataBase(connection, tabName, jsonObject, name);
                    }
                }
            }
        }else {
            if ("1".equals(entries.getStr("returnCode"))) {
                Object returnData = entries.get("returnData");
                if (returnData instanceof JSONObject){
                    JSONObject response = entries.getJSONObject("returnData");
                    try {
                        dropTable(tabName, connection);
                    } catch (SQLException e) {
                        log.error("删除表异常 ->{}", e.getMessage());
                    }
                    editDataBase(connection, tabName, response, name);
                }else {
                    JSONArray response = entries.getJSONArray("returnData");
                    try {
                        dropTable(tabName, connection);
                        List<JSONObject> list = JSONUtil.toList(response, JSONObject.class);
                        for (JSONObject jsonObject : list) {
                            editDataBase(connection, tabName, jsonObject, name);
                        }
                    } catch (SQLException e) {
                        log.error("删除表异常 ->{}", e.getMessage());
                    }
                }
                if (isLimit) {
                    JSONArray response = entries.getJSONArray("returnData");
                    if (tabName.contains("_getjianchaxmzd")) maxPage = 10000;
                    log.info("计算页码为 ->{}", maxPage);
                    if (maxPage > 1) {
                        for (page = 2; page <= maxPage; page++) {
                            params = hisSyncDict.getParams();
                            params = params.replace("${pageIndex}",String.valueOf(page));
                            params = params.replace("${pageSize}",String.valueOf(SIZE));
                            object = JSONUtil.parseObj(params);
                            log.info("请求接口 ->{}, 请求参数 ->{}", config.getHisUrl()+hisSyncDict.getUrl(), object.toString());
                            entries = execRequest(config,object.toString(),hisSyncDict,token);
                            log.info("请求返回 ->{}", entries.toString());
                            response = entries.getJSONArray("returnData");
                            if (response.isEmpty())
                                break;
                            List<JSONObject> list = JSONUtil.toList(response, JSONObject.class);
                            for (JSONObject jsonObject : list) {
                                editDataBase(connection, tabName, jsonObject, name);
                            }
                        }
                    }
                } else {
                    JSONArray response = entries.getJSONArray("returnData");
                    List<JSONObject> list = JSONUtil.toList(response, JSONObject.class);
                    for (JSONObject jsonObject : list) {
                        editDataBase(connection, tabName, jsonObject, name);
                    }
                }
            }
        }

    }

    /**
     * 针对于分页接口 创建表、插入数据
     *
     * @param connection
     * @param tabName
     * @param response
     * @return
     */
    private int LimitInsertData(Connection connection, String tabName, JSONObject response,String name,Integer page) {
        JSONArray jsonArray = response.getJSONArray("list");
        JSONObject entries = (JSONObject) jsonArray.get(0);
        int maxPage;
        // 行数 数据返回的第几行
        Integer rowNumber = response.getInt("RowNumber");
        // 总条数
        Integer totalCount = response.getInt("totalRows");
        Integer pageCount = response.getInt("pageCount");
        maxPage = (totalCount + 100 - 1) / 100;
        log.info("请求返回总条数 ->{},当前页 ->{},总页数 ->{}",totalCount,page,maxPage);
        jsonArray.forEach(obj -> {
            editDataBase(connection, tabName, (JSONObject) obj,name);
        });
        return pageCount;
    }

    /**
     * 操作数据库
     *
     * @param connection
     * @param tabName
     * @param obj
     */
    private void editDataBase(Connection connection, String tabName, JSONObject obj,String name) {
        try {
            if (!tabIsExists(connection, tabName,name)) {
                // 创建表
                creatTable(obj, tabName, connection);
            }
        } catch (SQLException throwables) {
            log.error("创建表异常");
            log.error(throwables.getSQLState());
            log.error(throwables.getMessage());
        }
        // 对比字段 并插入数据
        try {
            operationTable(obj, tabName, connection);
        } catch (SQLException e) {
            log.error("对比字段插入数据异常");
            log.error(e.getSQLState());
            log.error(e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * 执行post请求
     *
     * @param params 请求参数 例如： param1=val1&param2=val2
     * @return 请求返回的json转换后的JSONObject对象
     */
    private JSONObject execRequest(DictCommonHisConfig config,String params,HisSyncDict hisSyncDict,String token) {
        URL url = null;
        HttpURLConnection connection = null;
        OutputStreamWriter writer = null;
        BufferedReader reader = null;
        StringBuilder response = new StringBuilder();
        try {
            url = new URL(config.getHisUrl()+hisSyncDict.getUrl());
            connection = (HttpURLConnection) url.openConnection();
            connection.setRequestMethod("POST");
            connection.setRequestProperty("Content-Type", "application/x-www-form-urlencoded");
            connection.setRequestProperty("Authorization", token);
            log.info("请求接口 ->{}", hisSyncDict.getUrl());
            if (hisSyncDict.getUrl().equals("/zhuShuJu/getListYiShengZd") || hisSyncDict.getUrl().equals("/zhuShuJu/getZhiGongPage") || hisSyncDict.getUrl().equals("/zhuShuJu/getJianChaXm")){
                connection.setRequestProperty("dangQianYhId","1001");
                if (hisSyncDict.getUrl().equals("/zhuShuJu/getJianChaXm"))
                    connection.setRequestProperty("yuanQuId","1");
            }
            connection.setDoOutput(true);
            StringBuilder postData = new StringBuilder();
            JSONObject jsonObject = JSONUtil.parseObj(params);
            for (String key : jsonObject.keySet()) {
                if (postData.length() > 0) {
                    postData.append("&");
                }
                String encode = URLEncoder.encode(key, "UTF-8");
                String encode1 = URLEncoder.encode(String.valueOf(jsonObject.get(key)), "UTF-8");
                postData.append(encode).append("=").append(encode1);
            }

            writer = new OutputStreamWriter(connection.getOutputStream());
            writer.write(postData.toString());
            writer.flush();
            reader = new BufferedReader(new InputStreamReader(connection.getInputStream()));
            String line;
            while ((line = reader.readLine()) != null) {
                response.append(line);
            }
            return JSONUtil.parseObj(response);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.disconnect();
            }
        }
        return null;
    }

    public Connection getConnection(DictCommonHisConfig config) {
        //    public void setConfigPath(String configPath) {
//        CONFIG_PATH = configPath;
//        NAME = configValue.getConfigValue("ShanXi_Qin_BaoJi_Bjxjyy.name");
//        URL = "jdbc:mysql://" + configValue.getConfigValue("ShanXi_Qin_BaoJi_Bjxjyy.ip")
//        + ":" + configValue.getConfigValue("ShanXi_Qin_BaoJi_Bjxjyy.prot") + "/" + NAME +
//                "?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8";
//        USER = configValue.getConfigValue("ShanXi_Qin_BaoJi_Bjxjyy.username");
//        PASSWORD = configValue.getConfigValue("ShanXi_Qin_BaoJi_Bjxjyy.password");
//        String apiUrl = configValue.getConfigValue("ShanXi_Qin_BaoJi_Bjxjyy.his_api_url");
//        String apiPort = configValue.getConfigValue("ShanXi_Qin_BaoJi_Bjxjyy.his_api_port");
//        BASE_API_URL = apiUrl+":"+apiPort+configValue.getConfigValue("ShanXi_Qin_BaoJi_Bjxjyy.hisapiappend");
//    }
        try {
            String url = "jdbc:mysql://" + config.getDbIp()
                    + ":" + config.getDbPort() + "/" + config.getDbName() +
                    "?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8";
            log.info("数据库信息 ->{},{},{}",config.getDbUser(),config.getDbPassword(),url);
            return DriverManager.getConnection(url,config.getDbUser(),config.getDbPassword());
        } catch (Exception throwables) {
            log.error("获取sql连接失败");
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 表是否存在
     *
     * @param connection
     * @param tableName
     * @return
     * @throws SQLException
     */
    private Boolean tabIsExists(Connection connection, String tableName,String name) throws SQLException {
        String tabSql = "SELECT table_name FROM information_schema.tables WHERE table_schema = ? AND table_name = ?";
        PreparedStatement statement = connection.prepareStatement(tabSql);
        statement.setString(1, name);
        statement.setString(2, tableName);
//        log.info("判断表是否存在 sql-> {}", statement.toString());
        ResultSet resultSet = statement.executeQuery();
        boolean next = resultSet.next();
        statement.close();
        return next;
    }

    /**
     * 操作表
     *
     * @param tabName
     * @param connection
     * @throws SQLException
     */
    private void operationTable(JSONObject jsonObject, String tabName, Connection connection) throws SQLException {
        List<String> columns = getColumns(tabName, connection);
//        log.info("当前表字段为 ->{}", columns);
        ArrayList<String> responseColums = new ArrayList<>();
        for (Map.Entry<String, Object> entry : jsonObject.entrySet()) {
            String key = entry.getKey().trim().toLowerCase();
            responseColums.add(key);
        }
//        log.info("请求返回的字段为 ->{}", responseColums);
        responseColums.removeAll(columns);
//        log.info("需要增加的字段 ->{}",responseColums);
        if (!responseColums.isEmpty()) {
            // 需要增加字段 并插入数据
            for (String colum : responseColums) {
                String sql = "alter table " + tabName + " add column " + colum + " VARCHAR(200) null";
//                log.info("修改字段 ->{}",sql.toString());
                Statement statement = connection.createStatement();
                statement.executeUpdate(sql);
                statement.close();
            }
            insertData(tabName, connection, jsonObject);
        } else {
            insertData(tabName, connection, jsonObject);
        }
    }

    /**
     * 插入数据
     *
     * @param tabName
     * @param connection
     * @param jsonObject
     * @throws SQLException
     */
    private void insertData(String tabName, Connection connection, JSONObject jsonObject) throws SQLException {
        // 插入数据前 先查询数据是否存在
        StringBuilder insertSqlBuilder = new StringBuilder();
        StringBuilder valueBuilder = new StringBuilder();
        insertSqlBuilder.append("insert into ").append(tabName).append(" (");
        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+", "").replace("\\","");
            insertSqlBuilder.append(key).append(", ");
            valueBuilder.append("'").append(val.replaceAll("'","‘")).append("', ");
        }
        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 statement = connection.createStatement();
        log.info("插入数据 sql-> {}", insertSqlBuilder.toString());
        statement.execute(insertSqlBuilder.toString());
        statement.close();
    }

    /**
     * 获取表的列
     *
     * @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 resultDataIndex1 返回数据中的第一个参数
     * @param tabName
     * @param connection
     * @throws SQLException
     */
    private void creatTable(JSONObject resultDataIndex1, String tabName, Connection connection) throws SQLException {
        StringBuilder sql = new StringBuilder("CREATE TABLE " + tabName + " (");
        for (Map.Entry<String, Object> entry : resultDataIndex1.entrySet()) {
            String key = entry.getKey().trim().toLowerCase();
            sql.append(key).append(" VARCHAR(200) null,");
        }
        sql.append("insert_time").append(" VARCHAR(100) null,");
        sql = new StringBuilder(sql.substring(0, sql.length() - 1));
        sql.append(");");
        log.info("创建表格 -> {}",sql.toString());
        Statement statement = connection.createStatement();
        statement.execute(sql.toString());
    }

    /**
     * 删除表
     * @param tabName
     * @param connection
     * @throws SQLException
     */
    private void dropTable(String tabName,Connection connection) throws SQLException {
        String sql = "DROP TABLE IF EXISTS " + tabName;
        Statement statement = connection.createStatement();
        statement.executeUpdate(sql);
        statement.close();
    }
}