package com.yeejoin.amos.adpter.service;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

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;
    }



    /**
     * 获取数据库连接
     * @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;
    }

}
