ylrz_saas_pharmacy/java/scripts/ApplyLobsterForbiddenTypoMenus.java

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * Apply forbidden/typo word menus to master + all tenant DBs.
 * Usage:
 *   java -cp ".;mysql-connector-j.jar" ApplyLobsterForbiddenTypoMenus
 */
public class ApplyLobsterForbiddenTypoMenus {

    private static final String MASTER_HOST = getenv("DB_HOST", "cq-cdb-8fjmemkb.sql.tencentcdb.com");
    private static final int MASTER_PORT = Integer.parseInt(getenv("DB_PORT", "27220"));
    private static final String MASTER_USER = getenv("DB_USER", "root");
    private static final String MASTER_PASSWORD = getenv("DB_PASSWORD", "Ylrz_1q2w3e4r5t6y");
    private static final String MASTER_DB = getenv("DB_NAME", "ylrz_saas");

    public static void main(String[] args) throws Exception {
        String masterSql = MASTER_SQL;
        String tenantSql = TENANT_SQL;

        System.out.println("[MASTER] " + MASTER_DB);
        try (Connection master = open(MASTER_HOST, MASTER_PORT, MASTER_DB, MASTER_USER, MASTER_PASSWORD)) {
            execSqlBatch(master, masterSql);
            System.out.println("[OK] master menus applied");

            List<Long> tenantIds = loadActiveTenantIds(master);
            for (Long tenantId : tenantIds) {
                TenantDb tenant = loadTenant(master, tenantId);
                if (tenant == null) {
                    System.err.println("[SKIP] tenant " + tenantId);
                    continue;
                }
                System.out.println("[TENANT] " + tenantId + " -> " + tenant.database);
                try (Connection tenantConn = open(tenant.host, tenant.port, tenant.database, tenant.user, tenant.password)) {
                    execSqlBatch(tenantConn, tenantSql);
                }
                System.out.println("[OK] tenant " + tenantId);
            }
        }
    }

    private static void execSqlBatch(Connection conn, String sql) throws SQLException {
        List<String> statements = splitStatements(sql);
        try (Statement st = conn.createStatement()) {
            for (String s : statements) {
                st.execute(s);
            }
        }
    }

    private static List<String> splitStatements(String sql) {
        return java.util.Arrays.stream(sql.split(";"))
                .map(String::trim)
                .filter(s -> !s.isEmpty() && !s.startsWith("--"))
                .map(s -> {
                    String[] lines = s.split("\n");
                    StringBuilder sb = new StringBuilder();
                    for (String line : lines) {
                        String t = line.trim();
                        if (t.startsWith("--")) continue;
                        sb.append(line).append('\n');
                    }
                    return sb.toString().trim();
                })
                .filter(s -> !s.isEmpty())
                .collect(Collectors.toList());
    }

    private static List<Long> loadActiveTenantIds(Connection master) throws SQLException {
        List<Long> ids = new ArrayList<>();
        try (Statement st = master.createStatement();
             ResultSet rs = st.executeQuery("SELECT id FROM tenant_info WHERE status = 1 ORDER BY id")) {
            while (rs.next()) ids.add(rs.getLong(1));
        }
        return ids;
    }

    private static TenantDb loadTenant(Connection master, long tenantId) throws SQLException {
        String sql = "SELECT db_url, db_account, db_pwd FROM tenant_info WHERE id = ? AND status = 1 LIMIT 1";
        try (PreparedStatement ps = master.prepareStatement(sql)) {
            ps.setLong(1, tenantId);
            try (ResultSet rs = ps.executeQuery()) {
                if (!rs.next()) return null;
                return parseJdbcUrl(rs.getString("db_url"), rs.getString("db_account"), rs.getString("db_pwd"));
            }
        }
    }

    private static TenantDb parseJdbcUrl(String jdbcUrl, String user, String password) {
        if (jdbcUrl == null || !jdbcUrl.startsWith("jdbc:mysql://")) {
            throw new IllegalArgumentException("Unsupported db_url: " + jdbcUrl);
        }
        String body = jdbcUrl.substring("jdbc:mysql://".length());
        int slash = body.indexOf('/');
        String hostPort = body.substring(0, slash);
        String rest = body.substring(slash + 1);
        int q = rest.indexOf('?');
        String database = q >= 0 ? rest.substring(0, q) : rest;
        String host;
        int port;
        int colon = hostPort.indexOf(':');
        if (colon >= 0) {
            host = hostPort.substring(0, colon);
            port = Integer.parseInt(hostPort.substring(colon + 1));
        } else {
            host = hostPort;
            port = 3306;
        }
        TenantDb t = new TenantDb();
        t.host = host;
        t.port = port;
        t.database = database;
        t.user = user != null ? user : MASTER_USER;
        t.password = password != null ? password : MASTER_PASSWORD;
        return t;
    }

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

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

    private static final String MASTER_SQL =
            "INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, is_frame, is_cache, create_by, create_time) "
                    + "SELECT '\u7981\u7528\u8bcd\u5e93', 2864, 6, 'forbidden-words', 'lobster/replace-words/index', 'C', '0', '0', 'workflow:lobster:replace', 'warning', 0, 0, 'admin', NOW() "
                    + "FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE parent_id = 2864 AND path = 'forbidden-words');"
                    + "INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, is_frame, is_cache, create_by, create_time) "
                    + "SELECT '\u6613\u9519\u8bcd\u5e93', 2864, 7, 'typo-words', 'lobster/replace-words/index', 'C', '0', '0', 'workflow:lobster:replace', 'edit', 0, 0, 'admin', NOW() "
                    + "FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE parent_id = 2864 AND path = 'typo-words');"
                    + "UPDATE sys_menu SET menu_name='\u7981\u7528\u8bcd\u5e93', order_num=6, component='lobster/replace-words/index', icon='warning', visible='0', status='0', perms='workflow:lobster:replace' WHERE parent_id=2864 AND path='forbidden-words';"
                    + "UPDATE sys_menu SET menu_name='\u6613\u9519\u8bcd\u5e93', order_num=7, component='lobster/replace-words/index', icon='edit', visible='0', status='0', perms='workflow:lobster:replace' WHERE parent_id=2864 AND path='typo-words';"
                    + "INSERT INTO sys_role_menu (role_id, menu_id) SELECT DISTINCT rm.role_id, m_new.menu_id FROM sys_role_menu rm "
                    + "JOIN sys_menu m_ref ON rm.menu_id=m_ref.menu_id AND m_ref.parent_id=2864 "
                    + "JOIN sys_menu m_new ON m_new.parent_id=2864 AND m_new.path='forbidden-words' "
                    + "WHERE NOT EXISTS (SELECT 1 FROM sys_role_menu x WHERE x.role_id=rm.role_id AND x.menu_id=m_new.menu_id);"
                    + "INSERT INTO sys_role_menu (role_id, menu_id) SELECT DISTINCT rm.role_id, m_new.menu_id FROM sys_role_menu rm "
                    + "JOIN sys_menu m_ref ON rm.menu_id=m_ref.menu_id AND m_ref.parent_id=2864 "
                    + "JOIN sys_menu m_new ON m_new.parent_id=2864 AND m_new.path='typo-words' "
                    + "WHERE NOT EXISTS (SELECT 1 FROM sys_role_menu x WHERE x.role_id=rm.role_id AND x.menu_id=m_new.menu_id);"
                    + "INSERT IGNORE 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 "
                    + "(29963, '\u7981\u7528\u8bcd\u5e93', 29900, 29, 'forbidden-words', 'lobster/replace-words/index', 'C', 'el-icon-warning', '0', '0', 0, 0, 'workflow:lobster:replace', 'admin', NOW(), 'forbidden'), "
                    + "(29964, '\u6613\u9519\u8bcd\u5e93', 29900, 30, 'typo-words', 'lobster/replace-words/index', 'C', 'el-icon-edit', '0', '0', 0, 0, 'workflow:lobster:replace', 'admin', NOW(), 'typo');"
                    + "INSERT IGNORE INTO tenant_company_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, company_id, remark) "
                    + "SELECT menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, NULL, remark FROM tenant_sys_menu WHERE menu_id IN (29963,29964);";

    private static final String TENANT_SQL =
            "INSERT IGNORE INTO 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 "
                    + "(29963, '\u7981\u7528\u8bcd\u5e93', 29900, 29, 'forbidden-words', 'lobster/replace-words/index', 'C', 'el-icon-warning', '0', '0', 0, 0, 'workflow:lobster:replace', 'admin', NOW(), 'forbidden'), "
                    + "(29964, '\u6613\u9519\u8bcd\u5e93', 29900, 30, 'typo-words', 'lobster/replace-words/index', 'C', 'el-icon-edit', '0', '0', 0, 0, 'workflow:lobster:replace', 'admin', NOW(), 'typo');"
                    + "INSERT IGNORE INTO company_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, company_id, remark) "
                    + "SELECT 29963, '\u7981\u7528\u8bcd\u5e93', 29900, 29, 'forbidden-words', 'lobster/replace-words/index', 0, 0, 'C', '0', '0', 'workflow:lobster:replace', 'el-icon-warning', 'admin', NOW(), c.company_id, 'forbidden' "
                    + "FROM (SELECT DISTINCT company_id FROM company_menu WHERE company_id IS NOT NULL) c "
                    + "WHERE NOT EXISTS (SELECT 1 FROM company_menu x WHERE x.menu_id=29963 AND x.company_id=c.company_id);"
                    + "INSERT IGNORE INTO company_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, company_id, remark) "
                    + "SELECT 29964, '\u6613\u9519\u8bcd\u5e93', 29900, 30, 'typo-words', 'lobster/replace-words/index', 0, 0, 'C', '0', '0', 'workflow:lobster:replace', 'el-icon-edit', 'admin', NOW(), c.company_id, 'typo' "
                    + "FROM (SELECT DISTINCT company_id FROM company_menu WHERE company_id IS NOT NULL) c "
                    + "WHERE NOT EXISTS (SELECT 1 FROM company_menu x WHERE x.menu_id=29964 AND x.company_id=c.company_id);"
                    + "INSERT IGNORE INTO sys_role_menu (role_id, menu_id) SELECT DISTINCT srm.role_id, 29963 FROM sys_role_menu srm WHERE srm.menu_id=29925 "
                    + "AND NOT EXISTS (SELECT 1 FROM sys_role_menu x WHERE x.role_id=srm.role_id AND x.menu_id=29963);"
                    + "INSERT IGNORE INTO sys_role_menu (role_id, menu_id) SELECT DISTINCT srm.role_id, 29964 FROM sys_role_menu srm WHERE srm.menu_id=29925 "
                    + "AND NOT EXISTS (SELECT 1 FROM sys_role_menu x WHERE x.role_id=srm.role_id AND x.menu_id=29964);"
                    + "INSERT IGNORE INTO company_role_menu (role_id, menu_id) SELECT DISTINCT crm.role_id, 29963 FROM company_role_menu crm WHERE crm.menu_id=29925 "
                    + "AND NOT EXISTS (SELECT 1 FROM company_role_menu x WHERE x.role_id=crm.role_id AND x.menu_id=29963);"
                    + "INSERT IGNORE INTO company_role_menu (role_id, menu_id) SELECT DISTINCT crm.role_id, 29964 FROM company_role_menu crm WHERE crm.menu_id=29925 "
                    + "AND NOT EXISTS (SELECT 1 FROM company_role_menu x WHERE x.role_id=crm.role_id AND x.menu_id=29964);";

    private static final class TenantDb {
        String host;
        int port;
        String database;
        String user;
        String password;
    }
}