package com.yeejoin.amos.adpter.service;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.jdbc.RuntimeSqlException;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.charset.Charset;
import java.nio.file.Files;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class AdpterService {

    private final static Logger log = LoggerFactory.getLogger(AdpterService.class);

    //开始时间
    private static Long startTime = 0L;
    //结束时间
    private static Long endTime = 0L;


    @Value("${source.jdbc.ip}")
    String sourceJdbc;

    @Value("${goal.jdbc.ip}")
    String goalJdbc;

    @Value("${source.jdbc.ip.idx}")
    String sourceJdbcIdx;

    @Value("${goal.jdbc.ip.idx}")
    String goalJdbcIdx;

    @Value("${source.jdbc.ip.buss}")
    String sourceJdbcBuss;

    @Value("${goal.jdbc.ip.buss}")
    String goalJdbcBuss;

    String driver = "com.mysql.cj.jdbc.Driver";

    @Value("${yeejoin.user}")
    String userName;

    @Value("${yeejoin.pwd}")
    String pwd;


    public Map<String, String> handle() {
        Map<String, String> rMap = new HashMap<>();

        // 处理common库
        copy(sourceJdbc, goalJdbc, "cb_firefighters", "cb_firefighters", "sequence_nbr", rMap);
        copy(sourceJdbc, goalJdbc, "cb_org_usr", "cb_org_usr", "sequence_nbr", rMap);
        copy(sourceJdbc, goalJdbc, "cb_water_resource", "cb_water_resource", "sequence_nbr", rMap);
        copy(sourceJdbc, goalJdbc, "cb_water_resource_crane", "cb_water_resource_crane", "sequence_nbr", rMap);
        copy(sourceJdbc, goalJdbc, "cb_water_resource_hydrant", "cb_water_resource_hydrant", "sequence_nbr", rMap);
        copy(sourceJdbc, goalJdbc, "cb_water_resource_index", "cb_water_resource_index", "sequence_nbr", rMap);
        copy(sourceJdbc, goalJdbc, "cb_water_resource_iot", "cb_water_resource_iot", "sequence_nbr", rMap);
        copy(sourceJdbc, goalJdbc, "cb_water_resource_natural", "cb_water_resource_natural", "sequence_nbr", rMap);
        copy(sourceJdbc, goalJdbc, "cb_water_resource_pool", "cb_water_resource_pool", "sequence_nbr", rMap);

        // 处理 idx库
        copy(sourceJdbcIdx, goalJdbcIdx, "cb_org_usr", "cb_org_usr", "sequence_nbr", rMap);
        copy(sourceJdbcIdx, goalJdbcIdx, "idx_biz_unit_person", "idx_biz_unit_person", "SEQUENCE_NBR", rMap);

        // 处理business
        copy(sourceJdbcBuss, goalJdbcBuss, "f_equipment", "f_equipment", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "cb_org_usr", "cb_org_usr", "sequence_nbr", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "cb_water_resource", "cb_water_resource", "sequence_nbr", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "f_equipment_fire_equipment", "f_equipment_fire_equipment", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "f_fire_fighting_system", "f_fire_fighting_system", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_input_item", "p_input_item", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_plan", "p_plan", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_plan_exec_statistics", "p_plan_exec_statistics", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_plan_task", "p_plan_task", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_plan_task_detail", "p_plan_task_detail", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_point", "p_point", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_point_classify", "p_point_classify", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_point_inputitem", "p_point_inputitem", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_point_photo", "p_point_photo", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_route", "p_route", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_route_point", "p_route_point", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_point_config", "p_point_config", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "p_route_point_item", "p_route_point_item", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_car", "wl_car", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_car_property", "wl_car_property", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_dynamic_form_instance", "wl_dynamic_form_instance", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_equipment_detail", "wl_equipment_detail", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_equipment_specific", "wl_equipment_specific", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_equipment_specific_index", "wl_equipment_specific_index", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_form_instance", "wl_form_instance", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_form_instance_equip", "wl_form_instance_equip", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_warehouse", "wl_warehouse", "id", rMap);
        copy(sourceJdbcBuss, goalJdbcBuss, "wl_warehouse_structure", "wl_warehouse_structure", "id", rMap);
        log.info("执行结束================");
        return rMap;
    }

    public void executeSqlFile(HttpServletRequest request, HttpServletResponse response, String dbHost, String port, String dbName, String userName, String dbPwd, MultipartFile file) throws UnsupportedEncodingException {
        String fileName = file.getOriginalFilename();
        if (!fileName.endsWith(".sql")) {
            throw new RuntimeException("文件格式错误");
        }
        String filePath = "output.txt";
        PrintWriter printWriter = null;
        File logFile = new File(filePath);
        if (logFile.exists()) {
            deleteFile(filePath);
        }
        try {
            printWriter = new PrintWriter(logFile, "UTF-8");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        initAndExecute(dbHost, port, dbName, userName, dbPwd, file, printWriter);
        String outPutFileName = "脚本执行结果.txt";
        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/plain");
        response.setHeader("Content-Disposition", "attachment; filename=\"" + URLEncoder.encode(outPutFileName, "UTF-8") + "\"");
        //输入流
        InputStream in = null;
        try {
            in = new FileInputStream(filePath);
            ServletOutputStream os = response.getOutputStream();
            //缓冲区
            int len =1;
            byte[] b = new byte[1024];
            while ((len = in.read(b))!=-1){
                os.write(b,0,len);
                in.close();
                os.close();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (Exception Ignored) {
        } finally {
            deleteFile(filePath);
        }
    }

    /**
     * 获取数据库连接
     *
     * @param
     * @return
     */
    public Connection getConnection(String jdbc) {
        //加载驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        String jdbcUrl = jdbc;
        String user = userName;
        String password = pwd;
        //连接数据库
        try {
            return DriverManager.getConnection(jdbcUrl, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("连接数据库失败");
        }
    }

    /**
     * 将查询的数据复制到另一个数据库的表中，要求两张表的字段名，字段类型完全相同。
     *
     * @param srcTableName  要查询的表
     * @param destTableName 目标表名称
     * @return
     */
    public int[] copy(String source, String goal, String srcTableName, String destTableName, String id, Map rMap) {
        startTime = System.currentTimeMillis();

        Connection conn = null;
        PreparedStatement pst = null;
        conn = getConnection(goal);

        int count[] = new int[0];
        List<String> list = new ArrayList<>();

        //查询数据
        String sql = String.format("select * from  %s ", srcTableName);
        //查询数据
        String sqlGoal = "select count(*) as num from  " + destTableName + " where " + id + " = ";
        List<Map<String, Object>> queryOld = query(source, sql);
        List<Map<String, Object>> query = new ArrayList<>();
        //插入数据
        String insertSql = "insert into %s(%s) values(%s)";
        StringBuilder key = new StringBuilder();
        StringBuilder value = new StringBuilder();
        List<String> columns = new ArrayList<>();
        List<List<Object>> params = new ArrayList<>();

        // 剔除已存在的数据
        for (Map map : queryOld
        ) {
            try {
                pst = conn.prepareStatement(sqlGoal + map.get(id));
                ResultSet resultSet = pst.executeQuery();
                while (resultSet.next()) {
                    int num = resultSet.getInt("num");
                    if (num < 1) {
                        query.add(map);
                    } else {
                        list.add(map.get(id).toString());
                    }
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                try {
                    if (pst != null && !pst.isClosed()) {
                        pst.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        if (query.size() > 0) {
            for (String column : query.get(0).keySet()) {
                key.append(column).append(",");
                value.append("?,");
                columns.add(column);
            }
            insertSql = String.format(insertSql,
                    destTableName,
                    key.substring(0, key.length() - 1).toString(),
                    value.substring(0, value.length() - 1).toString());

            for (Map<String, Object> map : query) {
                List<Object> param = new ArrayList<>();
                for (String column : columns) {
                    param.add(map.get(column));
                }
                params.add(param);
            }
            count = executeBatch(goal, insertSql, params, destTableName);
            endTime = System.currentTimeMillis();
            log.debug("复制表" + destTableName + "成功" + "用时" + (endTime - startTime) + "ms");
        }
        if (list.size() > 0) {
            rMap.put(destTableName, String.join(",", list));
        } else {
            rMap.put(destTableName, "复制表" + destTableName + "成功" + "用时" + (endTime - startTime) + "ms");
        }
        return count;
    }

    /**
     * 批量执行一个 SQL 语句，可以传不同的参数
     *
     * @param sql    SQL 语句
     * @param params 参数列表
     * @return
     */
    public int[] executeBatch(String goal, String sql, List<List<Object>> params, String destTableName) {
        int count[] = new int[0];
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = getConnection(goal);
            boolean autoCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);
            pst = conn.prepareStatement(sql);

            for (List<Object> list : params) {
                for (int i = 0; i < list.size(); i++) {
                    pst.setObject(i + 1, list.get(i));
                }
                pst.addBatch();
            }
            count = pst.executeBatch();
            conn.commit();
            conn.setAutoCommit(autoCommit);
        } catch (Exception e) {
            log.error("插入数据失败" + e.getMessage() + "========" + destTableName);
            throw new RuntimeException("插入数据失败" + e.getMessage());
        } finally {
            closeStatement(pst);
            closeConnection(conn);
        }
        return count;
    }


    /**
     * 查询数据并封装到 List 集合中
     *
     * @param sql  SQL 语句
     * @param args 参数列表
     * @return List<Map < 字段名, 值>>
     */
    public List<Map<String, Object>> query(String source, String sql, Object... args) {
        List<Map<String, Object>> result = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = getConnection(source);
            pst = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                pst.setObject(i + 1, args[i]);
            }
            rs = pst.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();

            //获取字段数量
            int columnCount = rsmd.getColumnCount();

            List<String> columns = new ArrayList<>(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                columns.add(rsmd.getColumnName(i)); // 字段名
            }
            //封装数据
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                for (String column : columns) {
                    map.put(column, rs.getObject(column));
                }
                result.add(map);
            }
        } catch (Exception e) {
            throw new RuntimeException("查询异常" + e.getMessage());
        } finally {
            closeStatement(pst);
            closeConnection(conn);
            closeResultSet(rs);
        }
        return result;
    }

    /**
     * 关闭 Statement
     *
     * @param stmt
     * @return
     */
    public static boolean closeStatement(Statement stmt) {
        boolean flag = true;
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                flag = false;
            }
        }
        return flag;
    }

    /**
     * 关闭 ResultSet
     *
     * @param rs
     * @return
     */
    public static boolean closeResultSet(ResultSet rs) {
        boolean flag = true;
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                flag = false;
            }
        }
        return flag;
    }

    /**
     * 关闭 Connection
     *
     * @param conn
     * @return
     */
    public static boolean closeConnection(Connection conn) {
        boolean flag = true;
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                flag = false;
            }
        }
        return flag;
    }


    private PrintWriter initAndExecute(String dbHost, String port, String dbName, String userName, String dbPwd, MultipartFile file, PrintWriter printWriter) {
        Connection conn = getManagerConnection(dbHost, port, dbName, userName, dbPwd, printWriter);
        if (ObjectUtils.isEmpty(conn)) {
            return printWriter;
        }
        FileReader reader = null;
        try {
            reader = new FileReader(convertMultipartFileToFile(file));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        ScriptRunner scriptRunner = new ScriptRunner(conn);
        // 设置编码，防止中文乱码
        Resources.setCharset(Charset.forName("UTF-8"));
        // 获取整个脚本再执行
//        scriptRunner.setSendFullScript(true);
        // 执行报错时，true 则回滚, false 则会继续执行
        scriptRunner.setStopOnError(false);
        scriptRunner.setErrorLogWriter(printWriter);
        try {
            // 执行
            scriptRunner.runScript(reader);

        } catch (RuntimeSqlException e) {
            throw new RuntimeSqlException(e);
        }
        scriptRunner.closeConnection();
        try {
            reader.close();
            printWriter.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn);
        }
        return printWriter;
    }


    public File convertMultipartFileToFile(MultipartFile file) {
        File convertedFile = null;
        try {
            convertedFile = Files.createTempFile("temp-", "-" + file.getOriginalFilename()).toFile();
            file.transferTo(convertedFile);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return convertedFile;
    }

    private Connection getManagerConnection(String dbHost, String port, String dbName, String userName, String dbPwd, PrintWriter printWriter) {
        String jdbcUrl = "jdbc:mysql://" + dbHost + ":" + port + "/" + dbName + "?useUnicode=true&allowMultiQueries=true&characterEncoding=utf-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai";
        //加载驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //连接数据库
        try {
            return DriverManager.getConnection(jdbcUrl, userName, dbPwd);
        } catch (SQLException e) {
            String errorMsg = "连接数据库失败，请检查数据库连接信息是否有误！";
            if (printWriter.checkError()) {
                try
                {
                    File logFile = new File("output.txt");
                    printWriter = new PrintWriter(logFile, "UTF-8");
                    printWriter.write(errorMsg);
                    if (printWriter.checkError())
                    {
                        log.error("文件写入异常:" + errorMsg);
                    }
                }
                catch (Exception exception)
                {
                    log.error("日志写入异常！" + errorMsg, exception);
                }
            } else {
                printWriter.write(errorMsg);
            }
            printWriter.flush();
            printWriter.close();
            e.printStackTrace();
            return null;
        }
    }

    public static  boolean deleteFile(String path) {
        boolean flag = false;
        try {
            File file = new File(path);
            // 路径为文件且不为空则进行删除
            if (file.isFile()) {
                file.delete();
                flag = true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return flag;
    }

}
