ylrz_saas_pharmacy/java/scripts/ApplyLobsterSysOnlyAdminMenus.java

import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Apply lobster sys-only admin menus to master + tenant DB(s).
 * Usage:
 *   java -cp ".;lib/mysql-connector-j-8.0.33.jar" ApplyLobsterSysOnlyAdminMenus [tenantDb]
 */
public class ApplyLobsterSysOnlyAdminMenus {

    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 MASTER_DB = "ylrz_saas";

    private static final String SALES =
            "29900,32481,29949,29905,29950,29934,29906,3008,29912";
    private static final String ADMIN_ONLY =
            "29925,29951,29963,29964,29907,29908,29909,29933,29965,29966";
    private static final String ADMIN_DROP =
            "29947,29938,29944,29936,29946,29937";

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Path tenantSqlPath = Path.of("d:/ylrz_saas_new/java/fs-agent/src/main/resources/db/migration/tenant/V20260724_04__lobster_sys_only_admin_menus.sql");
        Path masterSqlPath = Path.of("d:/ylrz_saas_new/java/sql/master/V20260724_04__lobster_sys_only_admin_menus.sql");
        String tenantSql = readSql(tenantSqlPath);
        String masterSql = readSql(masterSqlPath);

        String tenantDb = args.length > 0 ? args[0] : "fs_tenant_cs1";
        System.out.println("=== MASTER " + MASTER_DB + " ===");
        try (Connection c = open(MASTER_DB)) {
            exec(c, masterSql);
            report(c, "tenant_sys_menu", "29900");
            report(c, "tenant_company_menu", "29900");
        }

        System.out.println("\n=== TENANT " + tenantDb + " ===");
        try (Connection c = open(tenantDb)) {
            exec(c, tenantSql);
            report(c, "sys_menu", "29900");
            report(c, "company_menu", "29900");
        }

        writeReport(tenantDb);
        System.out.println("\nDone.");
    }

    private static String readSql(Path path) throws Exception {
        byte[] bytes = Files.readAllBytes(path);
        String sql = new String(bytes, StandardCharsets.UTF_8);
        if (!sql.isEmpty() && sql.charAt(0) == '') {
            sql = sql.substring(1);
        }
        return sql;
    }

    private static void exec(Connection c, String sql) throws Exception {
        try (Statement st = c.createStatement()) {
            st.execute(sql);
        }
    }

    private static void report(Connection c, String table, String rootId) throws Exception {
        System.out.println("\n--- " + table + " ---");
        int sysCount = count(c, table, rootId, false);
        int salesCount = count(c, table, rootId, true);
        System.out.println("total under " + rootId + ": " + sysCount);
        System.out.println("sales visible leaf: " + salesCount);
        if ("sys_menu".equals(table) || "tenant_sys_menu".equals(table)) {
            listAdminOnly(c, table);
        }
    }

    private static int count(Connection c, String table, String rootId, boolean salesOnly) throws Exception {
        String sql = "SELECT COUNT(*) FROM " + table + " WHERE menu_id=" + rootId
                + " OR parent_id=" + rootId;
        if (salesOnly) {
            sql += " AND menu_type='C' AND visible='0' AND status='0' AND menu_id IN (" + SALES + ")";
        }
        try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            rs.next();
            return rs.getInt(1);
        }
    }

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

    private static void writeReport(String tenantDb) throws Exception {
        Path out = Path.of("d:/ylrz_saas_new/java/scripts/output/lobster-sys-only-admin-menus-report.txt");
        Files.createDirectories(out.getParent());
        List<String> lines = new ArrayList<>();
        lines.add("lobster sys-only admin menus applied");
        lines.add("tenant=" + tenantDb);
        lines.add("sales_keep=" + SALES);
        lines.add("admin_only=" + ADMIN_ONLY);
        lines.add("admin_drop=" + ADMIN_DROP);
        try (Connection master = open(MASTER_DB); Connection tenant = open(tenantDb)) {
            lines.add("");
            lines.add("=== MASTER tenant_sys_menu admin-only ===");
            appendAdminLines(master, lines, "tenant_sys_menu");
            lines.add("");
            lines.add("=== MASTER tenant_company_menu (should be sales only) ===");
            appendLeafLines(master, lines, "tenant_company_menu", SALES);
            lines.add("");
            lines.add("=== TENANT sys_menu admin-only ===");
            appendAdminLines(tenant, lines, "sys_menu");
            lines.add("");
            lines.add("=== TENANT company_menu (should be sales only) ===");
            appendLeafLines(tenant, lines, "company_menu", SALES);
        }
        Files.write(out, lines, StandardCharsets.UTF_8);
        System.out.println("Report: " + out);
    }

    private static void appendAdminLines(Connection c, List<String> lines, String table) throws Exception {
        String sql = "SELECT menu_id, menu_name, order_num, path FROM " + table
                + " WHERE parent_id=29900 AND menu_id IN (" + ADMIN_ONLY + ") ORDER BY order_num, menu_id";
        try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            while (rs.next()) {
                lines.add(String.format("[%d] %s | order=%d | %s",
                        rs.getLong("menu_id"), rs.getString("menu_name"),
                        rs.getInt("order_num"), rs.getString("path")));
            }
        }
    }

    private static void appendLeafLines(Connection c, List<String> lines, String table, String ids) throws Exception {
        String sql = "SELECT menu_id, menu_name, order_num, path FROM " + table
                + " WHERE parent_id=29900 AND menu_type='C' AND visible='0' AND status='0'"
                + " AND menu_id IN (" + ids + ") ORDER BY order_num, menu_id";
        try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            while (rs.next()) {
                lines.add(String.format("[%d] %s | order=%d | %s",
                        rs.getLong("menu_id"), rs.getString("menu_name"),
                        rs.getInt("order_num"), rs.getString("path")));
            }
        }
    }

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