package com.lunhan.xxx;

import com.lunhan.xxx.common.util.ListUtil;
import com.lunhan.xxx.common.util.StringUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Objects;

public class Gauss2Gauss {
    private static final String FROM_HOST = "113.250.189.120";
    private static final String FROM_PORT = "57654";
    private static final String FROM_USER = "lunhan";
    private static final String FROM_PASSWORD = "lunhan.20240330";
    private static final String FROM_DB_NAME = "water_trade_center";
    private static final String FROM_DRIVER = "org.postgresql.Driver";
    private static final String FROM_CONNECTION_STR = "jdbc:postgresql://" + FROM_HOST + ":" + FROM_PORT + "/" + FROM_DB_NAME;

    private static final String TO_HOST = "113.250.189.120";
    private static final String TO_PORT = "57654";
    private static final String TO_USER = "lunhan";
    private static final String TO_PASSWORD = "lunhan.20240330";
    private static final String TO_DB_NAME = "qinghe_wise_water_system";
    private static final String TO_DRIVER = "org.postgresql.Driver";
    private static final String TO_CONNECTION_STR = "jdbc:postgresql://" + TO_HOST + ":" + TO_PORT + "/" + TO_DB_NAME;

    private static Connection CONN_FROM = null;
    private static Connection CONN_TO = null;

    static {
        //加载驱动
        try {
            Class.forName(FROM_DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //获得连接
        try {
            CONN_FROM = DriverManager.getConnection(FROM_CONNECTION_STR, FROM_USER, FROM_PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //加载驱动
        try {
            Class.forName(TO_DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //获得连接
        try {
            CONN_TO = DriverManager.getConnection(TO_CONNECTION_STR, TO_USER, TO_PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws SQLException {
        // 创建触发器需要的函数
        GenCodeGauss.execSql(CONN_TO, GenCodeGauss.getAutoUpdateTimeFunction());

        // 获取mysql所有的表名称
        Map<String, String> tables = GenCodeGauss.getTables(CONN_FROM, FROM_DB_NAME);
        // 遍历表名，获取表结构
        for (Map.Entry<String, String> table : tables.entrySet()) {
            // 获取mysql表结构
            List<Map<String, Object>> columns = GenCodeGauss.getColumns(CONN_FROM, table.getKey());
            // 获取mysql表的主键
            Map<String, String> mapPK = GenCodeGauss.getPK(CONN_FROM, table.getKey());
            // 获取mysql表的索引
            Map<String, String> mapIndex = GenCodeGauss.getIndex(CONN_FROM, table.getKey());

            System.out.println("从mysql查询表结构：" + table.getKey());
            // 调用gauss创建表结构
            List<String> listGenTable = GenCodeGauss.genTable(table.getKey(), table.getValue(), columns, mapPK, mapIndex);
//            for (String sql : listGenTable) {
//                boolean result = GenCodeGauss.execSql(CONN_TO, sql);
//                System.out.println(sql + ", 执行结果：" + result);
//            }

            // 获取mysql表所有数据
            String sqlSelect = "SELECT * FROM " + table.getKey();
            Integer count = 1;
            Integer pageSize = 500;
            String orderBy = "id";
            Object lastId = null;
            while (count > 0) {
                count = 0;
                String sql = sqlSelect + (Objects.isNull(lastId) ? "" : " WHERE " + orderBy + " > " + GenCodeGauss.toDBValue(lastId) + "") + " ORDER BY " + orderBy + " LIMIT " + pageSize;
                List<Map<String, Object>> list = GenCodeGauss.getListAll(CONN_FROM, sql);
                if (ListUtil.isNullOrEmpty(list)) {
                    continue;
                }
                count = list.size();
                lastId  = list.get(list.size() - 1).get(orderBy);

                System.out.println("从mysql获取到数据：" + count);
                // 批量插入gauss数据库
                List<String> insertAll = GenCodeGauss.insertAll(list, table.getKey(), 1);
//                for (String addData : insertAll) {
//                    boolean result = GenCodeGauss.execSql(CONN_TO, addData);
//                    System.out.println(addData + ", 执行结果：" + result);
//                }
                String sqlInsert = StringUtil.join(insertAll, "\n");
                System.out.println(sqlInsert);
            }
        }
    }
}