ylrz_saas_pharmacy/java/scripts/SyncLobsterMenusTenantToMaster.java

import java.sql.*;
import java.util.*;

/**
 * Sync lobster menus tenant -> master templates:
 *   tenant.sys_menu     -> master.tenant_sys_menu
 *   tenant.company_menu -> master.tenant_company_menu (company_id = NULL)
 *
 * Usage:
 *   java -cp ".;lib/mysql-connector-j-8.0.33.jar" SyncLobsterMenusTenantToMaster [tenantDb]
 */
public class SyncLobsterMenusTenantToMaster {

    private static final String HOST = getenv("DB_HOST", "cq-cdb-8fjmemkb.sql.tencentcdb.com");
    private static final int PORT = Integer.parseInt(getenv("DB_PORT", "27220"));
    private static final String USER = getenv("DB_USER", "root");
    private static final String PASS = getenv("DB_PASSWORD", "Ylrz_1q2w3e4r5t6y");
    private static final String MASTER_DB = getenv("DB_NAME", "ylrz_saas");
    private static final String TENANT_DB = getenv("TENANT_DB", "fs_tenant_cs1");
    private static final long LOBSTER_ROOT = 29900L;

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String tenantDb = args.length > 0 ? args[0] : TENANT_DB;

        try (Connection master = open(MASTER_DB);
             Connection tenant = open(tenantDb)) {
            System.out.println("[SOURCE] " + tenantDb);
            System.out.println("[TARGET] " + MASTER_DB);

            Set<Long> tenantSysIds = collectSubtreeIds(tenant, "sys_menu", LOBSTER_ROOT);
            Set<Long> tenantCompanyIds = collectCompanyMenuIds(tenant);

            Set<Long> masterSysDeleteIds = collectMasterLobsterDeleteIds(master, "tenant_sys_menu");
            Set<Long> masterCompanyDeleteIds = collectMasterLobsterDeleteIds(master, "tenant_company_menu");

            System.out.println("tenant sys_menu lobster ids: " + tenantSysIds.size() + " -> " + tenantSysIds);
            System.out.println("tenant company_menu lobster ids: " + tenantCompanyIds.size() + " -> " + tenantCompanyIds);
            System.out.println("delete tenant_sys_menu ids: " + masterSysDeleteIds.size());
            System.out.println("delete tenant_company_menu ids: " + masterCompanyDeleteIds.size());

            master.setAutoCommit(false);
            try {
                deleteByIds(master, "tenant_sys_menu", masterSysDeleteIds);
                deleteByIds(master, "tenant_company_menu", masterCompanyDeleteIds);

                int sysInserted = copySysMenus(tenant, master, tenantSysIds);
                int companyInserted = copyCompanyMenus(tenant, master, tenantCompanyIds);

                master.commit();
                System.out.println("[OK] tenant_sys_menu inserted: " + sysInserted);
                System.out.println("[OK] tenant_company_menu inserted: " + companyInserted);
            } catch (Exception e) {
                master.rollback();
                throw e;
            }

            printVerify(master, "tenant_sys_menu");
            printVerify(master, "tenant_company_menu");
        }
    }

    /** company_menu: distinct menu_id under lobster root 29900 only */
    private static Set<Long> collectCompanyMenuIds(Connection tenant) throws SQLException {
        Set<Long> ids = collectSubtreeIds(tenant, "company_menu", LOBSTER_ROOT);
        if (ids.isEmpty()) {
            return ids;
        }
        Set<Long> filtered = new LinkedHashSet<>();
        String sql = "SELECT DISTINCT menu_id FROM company_menu WHERE menu_id IN (" + inClause(ids) + ") ORDER BY menu_id";
        try (Statement st = tenant.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            while (rs.next()) {
                filtered.add(rs.getLong(1));
            }
        }
        return filtered;
    }

    private static Set<Long> collectMasterLobsterDeleteIds(Connection master, String table) throws SQLException {
        Set<Long> ids = new LinkedHashSet<>();
        ids.addAll(collectSubtreeIds(master, table, LOBSTER_ROOT));
        ids.addAll(collectSubtreeIds(master, table, 2951L));
        String sql = "SELECT menu_id FROM " + table + " WHERE path = '/lobster' OR path = 'lobster' "
                + "OR component LIKE 'lobster/%' OR component LIKE 'company/workflowLobster/%' "
                + "OR menu_name LIKE '%lobster%'";
        if (tableExists(master, table)) {
            try (Statement st = master.createStatement(); ResultSet rs = st.executeQuery(sql)) {
                while (rs.next()) {
                    ids.add(rs.getLong(1));
                }
            }
        }
        return ids;
    }

    private static Set<Long> collectSubtreeIds(Connection conn, String table, long rootId) throws SQLException {
        Set<Long> ids = new LinkedHashSet<>();
        if (!tableExists(conn, table) || !menuExists(conn, table, rootId)) {
            return ids;
        }
        ArrayDeque<Long> queue = new ArrayDeque<>();
        queue.add(rootId);
        while (!queue.isEmpty()) {
            long id = queue.removeFirst();
            if (!ids.add(id)) {
                continue;
            }
            try (PreparedStatement ps = conn.prepareStatement(
                    "SELECT menu_id FROM " + table + " WHERE parent_id = ?")) {
                ps.setLong(1, id);
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        queue.addLast(rs.getLong(1));
                    }
                }
            }
        }
        return ids;
    }

    private static int copySysMenus(Connection tenant, Connection master, Set<Long> menuIds) throws SQLException {
        if (menuIds.isEmpty()) {
            return 0;
        }
        String selectSql = "SELECT menu_id, menu_name, parent_id, order_num, path, component, menu_type, icon, "
                + "visible, status, is_frame, is_cache, perms, create_by, create_time, remark "
                + "FROM sys_menu WHERE menu_id IN (" + inClause(menuIds) + ") ORDER BY parent_id, order_num, menu_id";
        String insertSql = "INSERT INTO tenant_sys_menu (menu_id, menu_name, parent_id, order_num, path, component, "
                + "menu_type, icon, visible, status, is_frame, is_cache, perms, create_by, create_time, remark) "
                + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        return batchInsertMenus(tenant, master, selectSql, insertSql, false);
    }

    private static int copyCompanyMenus(Connection tenant, Connection master, Set<Long> menuIds) throws SQLException {
        if (menuIds.isEmpty()) {
            return 0;
        }
        Map<Long, MenuRow> rows = loadCompanyMenuRows(tenant, menuIds);
        String insertSql = "INSERT INTO tenant_company_menu (menu_id, menu_name, parent_id, order_num, path, component, "
                + "menu_type, icon, visible, status, is_frame, is_cache, perms, create_by, create_time, company_id, remark) "
                + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        int count = 0;
        try (PreparedStatement ps = master.prepareStatement(insertSql)) {
            for (MenuRow row : rows.values()) {
                ps.setLong(1, row.menuId);
                ps.setString(2, row.menuName);
                ps.setLong(3, row.parentId);
                ps.setInt(4, row.orderNum);
                ps.setString(5, row.path);
                ps.setString(6, row.component);
                ps.setString(7, row.menuType);
                ps.setString(8, row.icon);
                ps.setString(9, row.visible);
                ps.setString(10, row.status);
                ps.setInt(11, row.isFrame);
                ps.setInt(12, row.isCache);
                ps.setString(13, row.perms);
                ps.setString(14, row.createBy);
                ps.setTimestamp(15, row.createTime);
                ps.setNull(16, Types.BIGINT);
                ps.setString(17, row.remark);
                ps.addBatch();
                count++;
            }
            if (count > 0) {
                ps.executeBatch();
            }
        }
        return count;
    }

    private static int batchInsertMenus(Connection tenant, Connection master, String selectSql, String insertSql,
                                        boolean includeCompanyId) throws SQLException {
        int count = 0;
        try (Statement st = tenant.createStatement();
             ResultSet rs = st.executeQuery(selectSql);
             PreparedStatement ps = master.prepareStatement(insertSql)) {
            while (rs.next()) {
                ps.setLong(1, rs.getLong("menu_id"));
                ps.setString(2, rs.getString("menu_name"));
                ps.setLong(3, rs.getLong("parent_id"));
                ps.setInt(4, rs.getInt("order_num"));
                ps.setString(5, rs.getString("path"));
                ps.setString(6, rs.getString("component"));
                ps.setString(7, rs.getString("menu_type"));
                ps.setString(8, rs.getString("icon"));
                ps.setString(9, rs.getString("visible"));
                ps.setString(10, rs.getString("status"));
                ps.setInt(11, rs.getInt("is_frame"));
                ps.setInt(12, rs.getInt("is_cache"));
                ps.setString(13, rs.getString("perms"));
                ps.setString(14, rs.getString("create_by"));
                ps.setTimestamp(15, rs.getTimestamp("create_time"));
                if (includeCompanyId) {
                    ps.setNull(16, Types.BIGINT);
                    ps.setString(17, rs.getString("remark"));
                } else {
                    ps.setString(16, rs.getString("remark"));
                }
                ps.addBatch();
                count++;
            }
            if (count > 0) {
                ps.executeBatch();
            }
        }
        return count;
    }

    private static Map<Long, MenuRow> loadCompanyMenuRows(Connection tenant, Set<Long> menuIds) throws SQLException {
        String sql = "SELECT menu_id, menu_name, parent_id, order_num, path, component, menu_type, icon, "
                + "visible, status, is_frame, is_cache, perms, create_by, create_time, company_id, remark "
                + "FROM company_menu WHERE menu_id IN (" + inClause(menuIds) + ") "
                + "ORDER BY menu_id, CASE WHEN company_id IS NULL THEN 0 ELSE 1 END, company_id";
        Map<Long, MenuRow> map = new LinkedHashMap<>();
        try (Statement st = tenant.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            while (rs.next()) {
                long menuId = rs.getLong("menu_id");
                if (map.containsKey(menuId)) {
                    continue;
                }
                MenuRow row = new MenuRow();
                row.menuId = menuId;
                row.menuName = rs.getString("menu_name");
                row.parentId = rs.getLong("parent_id");
                row.orderNum = rs.getInt("order_num");
                row.path = rs.getString("path");
                row.component = rs.getString("component");
                row.menuType = rs.getString("menu_type");
                row.icon = rs.getString("icon");
                row.visible = rs.getString("visible");
                row.status = rs.getString("status");
                row.isFrame = rs.getInt("is_frame");
                row.isCache = rs.getInt("is_cache");
                row.perms = rs.getString("perms");
                row.createBy = rs.getString("create_by");
                row.createTime = rs.getTimestamp("create_time");
                row.remark = rs.getString("remark");
                map.put(menuId, row);
            }
        }
        return map;
    }

    private static void deleteByIds(Connection conn, String table, Set<Long> ids) throws SQLException {
        if (ids.isEmpty()) {
            return;
        }
        List<Long> list = new ArrayList<>(ids);
        list.sort(Collections.reverseOrder());
        try (Statement st = conn.createStatement()) {
            for (int i = 0; i < list.size(); i += 200) {
                int end = Math.min(i + 200, list.size());
                StringBuilder sb = new StringBuilder("DELETE FROM ").append(table).append(" WHERE menu_id IN (");
                for (int j = i; j < end; j++) {
                    if (j > i) {
                        sb.append(',');
                    }
                    sb.append(list.get(j));
                }
                sb.append(')');
                st.executeUpdate(sb.toString());
            }
        }
    }

    private static void printVerify(Connection master, String table) throws SQLException {
        System.out.println("\n=== verify " + table + " ===");
        try (Statement st = master.createStatement();
             ResultSet rs = st.executeQuery(
                     "SELECT menu_id, menu_name, parent_id, order_num, visible, HEX(menu_name) hx "
                             + "FROM " + table + " WHERE menu_id = 29900 OR parent_id = 29900 "
                             + "ORDER BY parent_id, order_num, menu_id")) {
            while (rs.next()) {
                System.out.printf("[%d] %s | parent=%d order=%d visible=%s hx=%s%n",
                        rs.getLong(1), rs.getString(2), rs.getLong(3), rs.getInt(4),
                        rs.getString(5), rs.getString(6));
            }
        }
    }

    private static boolean tableExists(Connection conn, String table) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(
                "SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ? LIMIT 1")) {
            ps.setString(1, table);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next();
            }
        }
    }

    private static boolean menuExists(Connection conn, String table, long menuId) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement("SELECT 1 FROM " + table + " WHERE menu_id = ? LIMIT 1")) {
            ps.setLong(1, menuId);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next();
            }
        }
    }

    private static String inClause(Set<Long> ids) {
        StringBuilder sb = new StringBuilder();
        boolean first = true;
        for (Long id : ids) {
            if (!first) {
                sb.append(',');
            }
            sb.append(id);
            first = false;
        }
        return sb.toString();
    }

    private static Connection open(String database) throws SQLException {
        String url = "jdbc:mysql://" + HOST + ":" + PORT + "/" + database
                + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&allowMultiQueries=true";
        return DriverManager.getConnection(url, USER, PASS);
    }

    private static String getenv(String key, String defaultValue) {
        String v = System.getenv(key);
        return v != null && !v.isEmpty() ? v : defaultValue;
    }

    private static class MenuRow {
        long menuId;
        long parentId;
        int orderNum;
        int isFrame;
        int isCache;
        String menuName;
        String path;
        String component;
        String menuType;
        String icon;
        String visible;
        String status;
        String perms;
        String createBy;
        Timestamp createTime;
        String remark;
    }
}