package sql;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.Data;
import java.sql.*;
import java.util.*;
/**
 * 數(shù)據(jù)庫比較工具類
 * 溫馨提示：
 * （1）如果想知道現(xiàn)數(shù)據(jù)庫和原數(shù)據(jù)庫有哪些區(qū)別，則原數(shù)據(jù)庫配置 old，現(xiàn)數(shù)據(jù)庫配置 now;
 * （2）比較結果以現(xiàn)數(shù)據(jù)庫的視角說明，如：提示新增 xxx 表，則表示現(xiàn)數(shù)據(jù)庫有，原數(shù)據(jù)庫沒有，反之；
 * （3）整個比較過程中，并不會對兩個數(shù)據(jù)庫造成任何影響，可放心使用；
 * （4）配置好兩個數(shù)據(jù)庫后，直接運行本類中的 main() 方法即可，最終比較結果將以 json 的格式直接輸出在控制臺。
 *
 * @author Yuanqiang.Zhang
 * @since 2023/7/3
 */
@Data
public class CompareDataBaseUtils {
    public static Map<String, String> old;
    public static Map<String, String> now;
    static {
        // 原數(shù)據(jù)庫
        old = new HashMap<>();
        old.put(Constant.ip, "127.0.0.1");
        old.put(Constant.port, "3306");
        old.put(Constant.dataBaseName, "zyq_test");
        old.put(Constant.userName, "root");
        old.put(Constant.password, "root123");
        old.put(Constant.description, "原數(shù)據(jù)庫");
        // 現(xiàn)數(shù)據(jù)庫
        now = new HashMap<>();
        now.put(Constant.ip, "127.0.0.1");
        now.put(Constant.port, "3306");
        now.put(Constant.dataBaseName, "zyq_test2");
        now.put(Constant.userName, "root");
        now.put(Constant.password, "root123");
        now.put(Constant.description, "現(xiàn)數(shù)據(jù)庫");
    }
    /**
     * 【主方法】配置好數(shù)據(jù)源后直接執(zhí)行即可
     */
    public static void main(String[] args) {
        long startTime = System.currentTimeMillis();
        print("比較中，請等待...");
        JSONObject compareResult = compareDataBase();
        if (Objects.nonNull(compareResult)) {
            print(String.format("比較完成，累計耗時%s毫秒", (System.currentTimeMillis() - startTime)));
            print("最終比較結果如下：");
            print(compareResult.toString());
        }
    }
    public static JSONObject compareDataBase() {
        Connection oldConn = getConnection(old);
        Connection nowConn = getConnection(now);
        if (Objects.isNull(oldConn) || Objects.isNull(nowConn)) {
            return null;
        }
        JSONObject oldTables = getCompareTable(old, oldConn);
        JSONObject nowTables = getCompareTable(now, nowConn);
        Set<String> allTableNames = new HashSet<>();
        allTableNames.addAll(oldTables.keySet());
        allTableNames.addAll(nowTables.keySet());
        List<String> nowMoreTableNames = new ArrayList<>();
        List<String> nowLessTableNames = new ArrayList<>();
        List<String> commonTableNames = new ArrayList<>();
        for (String tableName : allTableNames) {
            boolean existOld = oldTables.containsKey(tableName);
            boolean existNow = nowTables.containsKey(tableName);
            if (existNow && existOld) {
                commonTableNames.add(tableName);
            } else {
                if (existNow) {
                    nowMoreTableNames.add(tableName);
                } else {
                    nowLessTableNames.add(tableName);
                }
            }
        }
        // 表相關修改
        JSONObject compare = new JSONObject(new LinkedHashMap<>());
        compare.put(Constant.more_tables, nowMoreTableNames);
        compare.put(Constant.less_tables, nowLessTableNames);
        // 獲取相同兩張表的比較結果
        JSONObject changeTables = new JSONObject(new LinkedHashMap<>());
        for (String commonTableName : commonTableNames) {
            JSONObject oldInfo = oldTables.getJSONObject(commonTableName);
            JSONObject nowInfo = nowTables.getJSONObject(commonTableName);
            JSONObject compareInfo = getCompareInfo(oldInfo, nowInfo);
            Boolean changed = compareInfo.getBoolean(Constant.changed);
            if (changed) {
                compareInfo.remove(Constant.changed);
                changeTables.put(commonTableName, compareInfo);
            }
        }
        compare.put(Constant.update_tables, changeTables);
        return compare;
    }
    /**
     * 獲取老表和新表之間的比較結果
     */
    public static JSONObject getCompareInfo(JSONObject old, JSONObject now) {
        // 表信息比較
        JSONObject oldBasic = old.getJSONObject(Constant.basics);
        JSONObject nowBasic = now.getJSONObject(Constant.basics);
        JSONArray basicCompareResult = compareSameFieldsObject(oldBasic, nowBasic);
        // 表字段比較
        JSONObject oldFields = old.getJSONObject(Constant.fields);
        JSONObject nowFields = now.getJSONObject(Constant.fields);
        Set<String> allFields = new HashSet<>();
        allFields.addAll(oldFields.keySet());
        allFields.addAll(nowFields.keySet());
        JSONArray nowMoreFields = new JSONArray();
        JSONArray nowLessFields = new JSONArray();
        JSONArray commonFields = new JSONArray();
        for (String field : allFields) {
            boolean existOld = oldFields.containsKey(field);
            boolean existNow = nowFields.containsKey(field);
            if (existOld && existNow) {
                commonFields.add(field);
            } else {
                if (existNow) {
                    nowMoreFields.add(field);
                } else {
                    nowLessFields.add(field);
                }
            }
        }
        // 相同字段更新比較
        JSONObject fieldsCompareResult = new JSONObject(new LinkedHashMap<>());
        for (Object field : commonFields) {
            String fileName = field.toString();
            JSONObject oldField = oldFields.getJSONObject(fileName);
            JSONObject nowField = nowFields.getJSONObject(fileName);
            JSONArray commonResultArray = compareSameFieldsObject(oldField, nowField);
            if (!commonResultArray.isEmpty()) {
                fieldsCompareResult.put(fileName, commonResultArray);
            }
        }
        // 字段變更信息
        boolean fieldChanged = !nowMoreFields.isEmpty() || !nowLessFields.isEmpty() || !fieldsCompareResult.isEmpty();
        JSONObject fieldsResult = new JSONObject(new LinkedHashMap<>());
        if (!nowMoreFields.isEmpty()) {
            fieldsResult.put(Constant.more_fields, nowMoreFields);
        }
        if (!nowLessFields.isEmpty()) {
            fieldsResult.put(Constant.less_fields, nowLessFields);
        }
        if (!fieldsCompareResult.isEmpty()) {
            fieldsResult.put(Constant.update_fields, fieldsCompareResult);
        }
        // 基礎變更信息
        boolean basicChanged = !basicCompareResult.isEmpty();
        // 總變更信息
        boolean changed = fieldChanged || basicChanged;
        // 字段比較結果
        JSONObject compareResult = new JSONObject(new LinkedHashMap<>());
        if (basicChanged) {
            compareResult.put(Constant.table_change_info, basicCompareResult);
        }
        if (fieldChanged) {
            compareResult.put(Constant.field_change_info, fieldsResult);
        }
        compareResult.put(Constant.changed, changed);
        return compareResult;
    }
    /**
     * 比較新老兩個相同屬性的對象
     *
     * @param oldObj 老對象
     * @param nowObj 新對象
     * @return 比較結果
     */
    private static JSONArray compareSameFieldsObject(JSONObject oldObj, JSONObject nowObj) {
        Set<String> keys = oldObj.keySet();
        JSONArray array = new JSONArray();
        String oldDesc = old.get(Constant.description);
        String nowDesc = now.get(Constant.description);
        for (String key : keys) {
            String oldValue = oldObj.getString(key);
            String nowValue = nowObj.getString(key);
            boolean same;
            if (Objects.isNull(oldValue)) {
                same = Objects.isNull(nowValue);
            } else {
                same = oldValue.equals(nowValue);
            }
            if (!same) {
                JSONObject diff = new JSONObject(new LinkedHashMap<>());
                diff.put(Constant.change_dimension, key);
                diff.put(oldDesc, oldValue);
                diff.put(nowDesc, nowValue);
                array.add(diff);
            }
        }
        return array;
    }
    /**
     * 獲取所有表字段屬性
     *
     * @param dataSource 數(shù)據(jù)源
     * @return 表信息
     */
    public static JSONObject getCompareTable(Map<String, String> dataSource, Connection conn) {
        // 獲取數(shù)據(jù)庫所有表基礎信息
        JSONObject basics = getAllTableList(conn);
        String dbName = dataSource.get(Constant.dataBaseName);
        String desc = dataSource.get(Constant.description);
        int tableCount = 0;
        int tableTotal = basics.size();
        // 獲取每張表的每個字段詳情
        JSONObject tables = new JSONObject(new LinkedHashMap<>());
        for (String tableName : basics.keySet()) {
            tableCount++;
            JSONObject table = new JSONObject(new LinkedHashMap<>());
            table.put(Constant.basics, basics.getJSONObject(tableName));
            JSONObject fields = getTableFields(conn, dbName, tableName);
            table.put(Constant.fields, fields);
            String info = String.format("[%s][%s/%s][%s]解析完成，共%s個字段。",
                    desc, tableCount, tableTotal, tableName, fields.size());
            print(info);
            tables.put(tableName, table);
        }
        return tables;
    }
    /**
     * 獲取數(shù)據(jù)源屬性
     *
     * @param conn         數(shù)據(jù)庫連接
     * @param dataBaseName 數(shù)據(jù)庫名稱
     * @param tableName    表名稱
     * @return 該表的所有字段信息
     */
    private static JSONObject getTableFields(Connection conn, String dataBaseName, String tableName) {
        String sql = "SELECT * FROM information_schema.columns WHERE table_schema = '" + dataBaseName + "' AND table_name = '" + tableName + "'";
        JSONObject columns = new JSONObject(new LinkedHashMap<>());
        try {
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(sql);
            while (rs.next()) {
                JSONObject column = new JSONObject(new LinkedHashMap<>());
                String name = rs.getString("COLUMN_NAME");
                column.put(Constant.column_name, name);
                column.put(Constant.column_comment, rs.getString("COLUMN_COMMENT"));
                column.put(Constant.column_is_nullable, rs.getString("IS_NULLABLE"));
                column.put(Constant.column_type, rs.getString("COLUMN_TYPE"));
                column.put(Constant.column_collation, rs.getString("COLLATION_NAME"));
                column.put(Constant.column_key, rs.getString("COLUMN_KEY"));
                column.put(Constant.column_extra, rs.getString("EXTRA"));
                columns.put(name, column);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return columns;
    }
    /**
     * 獲取數(shù)據(jù)庫鏈接
     *
     * @param map 數(shù)據(jù)源
     * @return Connection
     */
    private static Connection getConnection(Map<String, String> map) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = String.format("jdbc:mysql://%s:%s/%s",
                    map.get(Constant.ip), map.get(Constant.port), map.get(Constant.dataBaseName));
            return DriverManager.getConnection(url, map.get(Constant.userName), map.get(Constant.password));
        } catch (Exception e) {
            System.err.printf("[%s]連接失敗，請檢查配置是否正確！%n", map.get(Constant.description));
            return null;
        }
    }
    /**
     * 獲取所有數(shù)據(jù)表名
     *
     * @param conn 數(shù)據(jù)庫鏈接信息
     * @return Map<表名, 備注>
     */
    private static JSONObject getAllTableList(Connection conn) {
        String sql = "SHOW TABLE status";
        JSONObject basics = new JSONObject(new LinkedHashMap<>());
        try {
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(sql);
            while (rs.next()) {
                JSONObject basic = new JSONObject(new LinkedHashMap<>());
                String name = rs.getString("Name");
                basic.put(Constant.table_name, name);
                basic.put(Constant.table_engine, rs.getString("Engine"));
                basic.put(Constant.table_comment, rs.getString("Comment"));
                basic.put(Constant.table_collation, rs.getString("Collation"));
                basics.put(name, basic);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return basics;
    }
    private static JSONObject getTableIndexes(Connection conn, String tableName) {
        String sql = "SHOW INDEX FROM " + tableName;
        JSONObject columns = new JSONObject(new LinkedHashMap<>());
        try {
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(sql);
            while (rs.next()) {
                JSONObject column = new JSONObject(new LinkedHashMap<>());
                String name = rs.getString("COLUMN_NAME");
                column.put(Constant.column_name, name);
                column.put(Constant.column_comment, rs.getString("COLUMN_COMMENT"));
                column.put(Constant.column_is_nullable, rs.getString("IS_NULLABLE"));
                column.put(Constant.column_type, rs.getString("COLUMN_TYPE"));
                column.put(Constant.column_collation, rs.getString("COLLATION_NAME"));
                column.put(Constant.column_key, rs.getString("COLUMN_KEY"));
                column.put(Constant.column_extra, rs.getString("EXTRA"));
                columns.put(name, column);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return columns;
    }
    /**
     * 輸出打印
     *
     * @param msg 打印信息
     */
    private static void print(String msg) {
        System.out.println(msg);
    }
    /**
     * 工具類中含的常量（為方便閱讀，本工具類中與輸入無相關的常量字段均采用下劃線小寫）
     */
    private static class Constant {
        // 數(shù)據(jù)源配置常量鍵
        public static final String ip = "ip";
        public static final String port = "port";
        public static final String dataBaseName = "dataBaseName";
        public static final String userName = "userName";
        public static final String password = "password";
        public static final String description = "description";
        // 代碼邏輯常量
        public static final String basics = "basics";
        public static final String fields = "fields";
        public static final String changed = "changed";
        // 比較結果鍵名（其他）
        public static final String table_change_info = "表信息變更";
        public static final String field_change_info = "表字段變更";
        public static final String more_tables = "新增的表";
        public static final String less_tables = "減少的表";
        public static final String update_tables = "變更的表";
        public static final String more_fields = "新增的字段";
        public static final String less_fields = "減少的字段";
        public static final String update_fields = "變更的字段";
        public static final String change_dimension = "變更維度";
        // 比較結果鍵名（表信息）
        public static final String table_name = "名稱";
        public static final String table_engine = "引擎";
        public static final String table_comment = "備注";
        public static final String table_collation = "字符集";
        // 比較結果鍵名（字段信息）
        public static final String column_name = "名稱";
        public static final String column_comment = "備注";
        public static final String column_is_nullable = "是否可空";
        public static final String column_type = "數(shù)據(jù)類型";
        public static final String column_collation = "字符集";
        public static final String column_key = "鍵類型";
        public static final String column_extra = "額外信息";
    }
}