ylrz_saas_pharmacy/java/scripts/ApplyLobsterSimplifiedMenus.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/** Simplified lobster engine menus: 8 visible items under company_menu/sys_menu. */
public class ApplyLobsterSimplifiedMenus {

    private static final String HOST = "cq-cdb-8fjmemkb.sql.tencentcdb.com:27220";
    private static final String USER = "root";
    private static final String PASS = "Ylrz_1q2w3e4r5t6y";

    private static final String KEEP =
            "29900,32481,29949,29905,29950,29934,29906,3008,29912";

    private static final Object[][] MENUS = {
            {29900, "\u9f99\u867e\u5f15\u64ce", 0, 100, "/lobster", "", "M", "el-icon-cpu", "0", null},
            {32481, "\u5de5\u4f5c\u6d41\u6a21\u7248", 29900, 1, "workflowLobster", "company/workflowLobster/index", "C", "el-icon-share", "0", "workflow:lobster:edit"},
            {29949, "\u5de5\u4f5c\u6d41\u6267\u884c", 29900, 2, "workflow-execution", "lobster/workflow-execution/index", "C", "el-icon-guide", "0", "workflow:lobster:edit"},
            {29905, "\u5b9e\u4f8b\u76d1\u63a7", 29900, 3, "instance", "lobster/instance/index", "C", "el-icon-monitor", "0", "workflow:lobster:query"},
            {29950, "\u5ba2\u6237\u753b\u50cf", 29900, 4, "profile", "lobster/profile/hub", "C", "el-icon-user", "0", "workflow:lobster:profile"},
            {29934, "\u5bf9\u5916\u89d2\u8272\u914d\u7f6e", 29900, 5, "identity-config", "lobster/identity-config/index", "C", "el-icon-view", "0", "workflow:lobster:identity"},
            {29906, "\u8bdd\u672f\u4f18\u5316\u5ba1\u6838", 29900, 6, "optimization", "lobster/optimization/index", "C", "el-icon-eye-open", "0", "workflow:lobster:query"},
            {3008, "\u52a8\u6001\u8282\u70b9\u5ba1\u6279", 29900, 7, "dynamic-impl", "lobster/dynamic-impl/index", "C", "el-icon-connection", "0", "workflow:lobster:audit"},
            {29912, "\u805a\u5408\u804a\u5929", 29900, 8, "chat-aggregate", "lobster/chat-aggregate/index", "C", "el-icon-message", "0", "workflow:lobster:query"},
    };

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String tenantDb = args.length > 0 ? args[0] : "fs_tenant_cs1";
        String url = "jdbc:mysql://" + HOST + "/" + tenantDb
                + "?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=utf8&allowMultiQueries=true";
        try (Connection c = DriverManager.getConnection(url, USER, PASS)) {
            c.setAutoCommit(false);
            try (Statement st = c.createStatement()) {
                st.executeUpdate("DELETE FROM sys_role_menu WHERE menu_id IN ("
                        + "SELECT menu_id FROM (SELECT menu_id FROM sys_menu WHERE (menu_id=29900 OR parent_id=29900 OR parent_id=29901) "
                        + "AND menu_id NOT IN (" + KEEP + ")) t)");
                st.executeUpdate("DELETE FROM company_role_menu WHERE menu_id IN ("
                        + "SELECT menu_id FROM (SELECT menu_id FROM company_menu WHERE (menu_id=29900 OR parent_id=29900 OR parent_id=29901) "
                        + "AND menu_id NOT IN (" + KEEP + ")) t)");
                st.executeUpdate("DELETE FROM sys_menu WHERE (menu_id=29900 OR parent_id=29900 OR parent_id=29901) "
                        + "AND menu_id NOT IN (" + KEEP + ")");
                st.executeUpdate("DELETE FROM company_menu WHERE (menu_id=29900 OR parent_id=29900 OR parent_id=29901) "
                        + "AND menu_id NOT IN (" + KEEP + ")");
            }

            String replaceSql = "REPLACE 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 (?,?,?,?,?, ?,?,?, ?, '0',0,0, ?, 'admin', NOW(), NULL)";
            try (PreparedStatement ps = c.prepareStatement(replaceSql)) {
                for (Object[] row : MENUS) {
                    ps.setInt(1, (Integer) row[0]);
                    ps.setString(2, (String) row[1]);
                    ps.setInt(3, (Integer) row[2]);
                    ps.setInt(4, (Integer) row[3]);
                    ps.setString(5, (String) row[4]);
                    ps.setString(6, (String) row[5]);
                    ps.setString(7, (String) row[6]);
                    ps.setString(8, (String) row[7]);
                    ps.setString(9, (String) row[8]);
                    ps.setString(10, (String) row[9]);
                    ps.addBatch();
                }
                ps.executeBatch();
            }

            try (Statement st = c.createStatement()) {
                st.executeUpdate(
                        "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 s.menu_id, s.menu_name, s.parent_id, s.order_num, s.path, s.component, "
                                + "s.is_frame, s.is_cache, s.menu_type, s.visible, s.status, s.perms, s.icon, s.create_by, s.create_time, c.company_id, s.remark "
                                + "FROM sys_menu s CROSS JOIN (SELECT DISTINCT company_id FROM company_menu WHERE company_id IS NOT NULL UNION SELECT 338) c "
                                + "WHERE s.menu_id IN (" + KEEP + ") "
                                + "AND NOT EXISTS (SELECT 1 FROM company_menu x WHERE x.menu_id=s.menu_id AND x.company_id=c.company_id)");
                st.executeUpdate(
                        "UPDATE company_menu cm INNER JOIN sys_menu sm ON cm.menu_id=sm.menu_id "
                                + "SET cm.menu_name=sm.menu_name, cm.parent_id=sm.parent_id, cm.order_num=sm.order_num, "
                                + "cm.path=sm.path, cm.component=sm.component, cm.is_frame=sm.is_frame, cm.is_cache=sm.is_cache, "
                                + "cm.menu_type=sm.menu_type, cm.visible=sm.visible, cm.status=sm.status, cm.perms=sm.perms, "
                                + "cm.icon=sm.icon, cm.remark=sm.remark "
                                + "WHERE sm.menu_id IN (" + KEEP + ")");
                st.executeUpdate(
                        "INSERT IGNORE INTO sys_role_menu (role_id, menu_id) "
                                + "SELECT r.role_id, s.menu_id FROM sys_role_menu r CROSS JOIN sys_menu s "
                                + "WHERE r.menu_id=29900 AND s.parent_id=29900 AND s.menu_type='C' AND s.visible='0' "
                                + "AND NOT EXISTS (SELECT 1 FROM sys_role_menu x WHERE x.role_id=r.role_id AND x.menu_id=s.menu_id)");
                st.executeUpdate(
                        "INSERT IGNORE INTO company_role_menu (role_id, menu_id) "
                                + "SELECT r.role_id, s.menu_id FROM company_role_menu r CROSS JOIN sys_menu s "
                                + "WHERE r.menu_id=29900 AND s.parent_id=29900 AND s.menu_type='C' AND s.visible='0' "
                                + "AND NOT EXISTS (SELECT 1 FROM company_role_menu x WHERE x.role_id=r.role_id AND x.menu_id=s.menu_id)");
            }
            c.commit();
            printMenus(c, "company_menu");
            printMenus(c, "sys_menu");
            System.out.println("Applied simplified lobster menus to " + tenantDb);
        }
    }

    private static void printMenus(Connection c, String table) throws Exception {
        System.out.println("\n=== " + table + " visible under 29900 ===");
        try (Statement st = c.createStatement();
             ResultSet rs = st.executeQuery(
                     "SELECT menu_id, menu_name, order_num, path FROM " + table
                             + " WHERE parent_id=29900 AND menu_type='C' AND visible='0' AND status='0' "
                             + "ORDER BY order_num, menu_id")) {
            int n = 0;
            while (rs.next()) {
                n++;
                System.out.printf("%2d. [%d] %s | %s%n",
                        n, rs.getLong("menu_id"), rs.getString("menu_name"), rs.getString("path"));
            }
            System.out.println("count=" + n);
        }
    }
}