ylrz_saas_pharmacy/java/scripts/ApplyLobsterMenuDedup.java

import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Apply lobster company_menu dedup on master + all active tenant DBs, then verify.
 */
public class ApplyLobsterMenuDedup {

    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");

    private static final Path ROOT = Paths.get(System.getProperty("user.dir")).getParent();
    private static final Path MASTER_SQL = ROOT.resolve(
            "sql/master/V20260619_10__lobster_tenant_company_menu_dedup.sql");
    private static final Path TENANT_SQL = ROOT.resolve(
            "fs-agent/src/main/resources/db/migration/tenant/V20260619_10__lobster_company_menu_dedup.sql");

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

        System.out.println("=== Lobster menu dedup ===");
        System.out.println("Master SQL: " + MASTER_SQL);
        System.out.println("Tenant SQL: " + TENANT_SQL);

        try (Connection master = open(MASTER_HOST, MASTER_PORT, MASTER_DB, MASTER_USER, MASTER_PASSWORD)) {
            System.out.println("\n[MASTER] before");
            printMasterDupes(master);
            execSql(master, masterSql);
            fixMenuNamesMaster(master);
            System.out.println("[MASTER] after dedup");
            printMasterDupes(master);

            List<Long> tenantIds = loadActiveTenantIds(master);
            System.out.println("\nActive tenants: " + tenantIds.size());

            int ok = 0;
            int fail = 0;
            for (Long tenantId : tenantIds) {
                TenantDb tenant = loadTenant(master, tenantId);
                if (tenant == null) {
                    System.out.println("[SKIP] tenant " + tenantId);
                    continue;
                }
                System.out.println("\n[TENANT " + tenantId + "] " + tenant.database);
                try (Connection conn = open(tenant.host, tenant.port, tenant.database, tenant.user, tenant.password)) {
                    System.out.println("  before dupes=" + countTenantDupes(conn));
                    printLobsterMenus(conn, "  before");
                    execSql(conn, tenantSql);
                    fixMenuNamesTenant(conn);
                    int dupes = countTenantDupes(conn);
                    System.out.println("  after dupes=" + dupes);
                    printLobsterMenus(conn, "  after");
                    if (dupes == 0) {
                        System.out.println("  [OK]");
                        ok++;
                    } else {
                        System.out.println("  [FAIL] still has duplicate components");
                        printTenantDupes(conn);
                        fail++;
                    }
                } catch (Exception e) {
                    System.out.println("  [ERR] " + e.getMessage());
                    fail++;
                }
            }

            System.out.println("\n=== SUMMARY ===");
            System.out.println("Tenants OK: " + ok + ", FAIL: " + fail);
            if (fail > 0) {
                System.exit(1);
            }
        }
    }

    private static String readText(Path path) throws Exception {
        byte[] bytes = Files.readAllBytes(path);
        try {
            return new String(bytes, StandardCharsets.UTF_8);
        } catch (Exception ignored) {
            return new String(bytes, "GBK");
        }
    }

    private static void execSql(Connection conn, String sql) throws SQLException {
        try (Statement st = conn.createStatement()) {
            st.execute(sql);
        }
    }

    private static void fixMenuNamesMaster(Connection conn) throws SQLException {
        updateMenuName(conn, "tenant_company_menu", 29950, "\u5ba2\u6237\u753b\u50cf");
        updateMenuName(conn, "tenant_company_menu", 32481, "\u5de5\u4f5c\u6d41\u6a21\u677f");
        updateMenuName(conn, "tenant_company_menu", 29903, "\u5de5\u4f5c\u6d41\u6a21\u677f");
        updateMenuName(conn, "tenant_company_menu", 29949, "\u5de5\u4f5c\u6d41\u6267\u884c");
        updateMenuName(conn, "sys_menu", 29950, "\u5ba2\u6237\u753b\u50cf");
        updateMenuName(conn, "sys_menu", 32481, "\u5de5\u4f5c\u6d41\u6a21\u677f");
        updateMenuName(conn, "sys_menu", 29903, "\u5de5\u4f5c\u6d41\u6a21\u677f");
        updateMenuName(conn, "sys_menu", 29949, "\u5de5\u4f5c\u6d41\u6267\u884c");
    }

    private static void fixMenuNamesTenant(Connection conn) throws SQLException {
        updateMenuName(conn, "company_menu", 29950, "\u5ba2\u6237\u753b\u50cf");
        updateMenuName(conn, "company_menu", 32481, "\u5de5\u4f5c\u6d41\u6a21\u677f");
        updateMenuName(conn, "company_menu", 29903, "\u5de5\u4f5c\u6d41\u6a21\u677f");
        updateMenuName(conn, "company_menu", 29949, "\u5de5\u4f5c\u6d41\u6267\u884c");
        updateMenuName(conn, "sys_menu", 29950, "\u5ba2\u6237\u753b\u50cf");
        updateMenuName(conn, "sys_menu", 32481, "\u5de5\u4f5c\u6d41\u6a21\u677f");
        updateMenuName(conn, "sys_menu", 29903, "\u5de5\u4f5c\u6d41\u6a21\u677f");
        updateMenuName(conn, "sys_menu", 29949, "\u5de5\u4f5c\u6d41\u6267\u884c");
    }

    private static void updateMenuName(Connection conn, String table, long menuId, String name)
            throws SQLException {
        String sql = "UPDATE " + table + " SET menu_name = ? WHERE menu_id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);
            ps.setLong(2, menuId);
            ps.executeUpdate();
        }
    }

    private static int countTenantDupes(Connection conn) throws SQLException {
        String sql = "SELECT COUNT(*) FROM ("
                + "SELECT path, component, company_id, COUNT(*) cnt FROM company_menu "
                + "WHERE parent_id = 29900 AND menu_type = 'C' AND path IS NOT NULL AND path <> '' "
                + "GROUP BY path, component, company_id HAVING cnt > 1"
                + ") t";
        try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            rs.next();
            return rs.getInt(1);
        }
    }

    private static void printTenantDupes(Connection conn) throws SQLException {
        String sql = "SELECT path, component, company_id, COUNT(*) cnt, GROUP_CONCAT(menu_id ORDER BY menu_id) ids "
                + "FROM company_menu "
                + "WHERE parent_id = 29900 AND menu_type = 'C' AND path IS NOT NULL AND path <> '' "
                + "GROUP BY path, component, company_id HAVING cnt > 1";
        try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            while (rs.next()) {
                System.out.println("    dupe path=" + rs.getString(1)
                        + " component=" + rs.getString(2)
                        + " company_id=" + rs.getString(3)
                        + " cnt=" + rs.getInt(4)
                        + " ids=" + rs.getString(5));
            }
        }
    }

    private static void printLobsterMenus(Connection conn, String prefix) throws SQLException {
        String sql = "SELECT menu_id, menu_name, path, component "
                + "FROM company_menu "
                + "WHERE parent_id = 29900 AND visible = '0' AND status = '0' AND menu_type = 'C' "
                + "ORDER BY order_num, menu_id";
        try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            int n = 0;
            while (rs.next()) {
                n++;
                System.out.println(prefix + " " + rs.getLong(1) + " | "
                        + rs.getString(2) + " | " + rs.getString(3) + " | " + rs.getString(4));
            }
            System.out.println(prefix + " visible menu count=" + n);
        }
    }

    private static void printMasterDupes(Connection conn) throws SQLException {
        String sql = "SELECT path, component, COUNT(*) cnt, GROUP_CONCAT(menu_id ORDER BY menu_id) ids "
                + "FROM tenant_company_menu "
                + "WHERE parent_id = 29900 AND menu_type = 'C' AND path IS NOT NULL AND path <> '' "
                + "GROUP BY path, component HAVING cnt > 1";
        try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            boolean any = false;
            while (rs.next()) {
                any = true;
                System.out.println("  dupe path=" + rs.getString(1)
                        + " component=" + rs.getString(2)
                        + " cnt=" + rs.getInt(3) + " ids=" + rs.getString(4));
            }
            if (!any) {
                System.out.println("  no duplicate path/component under 29900");
            }
        }
    }

    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 {
        try (PreparedStatement ps = master.prepareStatement(
                "SELECT db_url, db_account, db_pwd FROM tenant_info WHERE id = ? AND status = 1 LIMIT 1")) {
            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) {
        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;
        int colon = hostPort.indexOf(':');
        TenantDb t = new TenantDb();
        t.host = colon >= 0 ? hostPort.substring(0, colon) : hostPort;
        t.port = colon >= 0 ? Integer.parseInt(hostPort.substring(colon + 1)) : 3306;
        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 {
        return DriverManager.getConnection(
                "jdbc:mysql://" + host + ":" + port + "/" + database
                        + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&allowMultiQueries=true",
                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 class TenantDb {
        String host;
        int port;
        String database;
        String user;
        String password;
    }
}