ylrz_saas_pharmacy/java/scripts/DeleteLobsterProfileConfigMenu.java

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

/**
 * 删除所有租户库中的"用户画像配置"菜单（menu_id=29922）。
 *
 * 影响表（每个租户库）：
 *   1. sys_menu              (租户管理端菜单)
 *   2. sys_role_menu         (租户管理端角色菜单关联)
 *   3. company_menu          (租户销售端菜单)
 *   4. company_role_menu     (租户销售端角色菜单关联)
 *
 * 删除条件：
 *   - menu_id = 29922
 *   - 或 path='profile-config' AND component='lobster/profile-config/index'（兜底）
 *
 * 用法：
 *   java DeleteLobsterProfileConfigMenu           # 真实执行
 *   java DeleteLobsterProfileConfigMenu dry-run   # 仅预览，不执行删除
 */
public class DeleteLobsterProfileConfigMenu {

    private static final long MENU_ID = 29922L;
    private static final String PATH = "profile-config";
    private static final String COMPONENT = "lobster/profile-config/index";

    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 {
        Class.forName("com.mysql.cj.jdbc.Driver");
        boolean dryRun = args.length > 0 && "dry-run".equalsIgnoreCase(args[0]);
        System.out.println("=== 删除租户库「用户画像配置」菜单 (menu_id=29922) ===");
        System.out.println("dryRun=" + dryRun);

        List<Long> tenantIds;
        try (Connection master = open(MASTER_HOST, MASTER_PORT, MASTER_DB, MASTER_USER, MASTER_PASSWORD)) {
            tenantIds = loadActiveTenantIds(master);
        }

        int ok = 0;
        int skip = 0;
        int fail = 0;
        for (Long tenantId : tenantIds) {
            TenantDb tenant;
            try (Connection master = open(MASTER_HOST, MASTER_PORT, MASTER_DB, MASTER_USER, MASTER_PASSWORD)) {
                tenant = loadTenant(master, tenantId);
            }
            if (tenant == null) {
                skip++;
                continue;
            }
            System.out.println("\n[TENANT " + tenantId + "] " + tenant.database);
            try (Connection conn = open(tenant.host, tenant.port, tenant.database, tenant.user, tenant.password)) {
                boolean changed = false;

                // 1. 租户管理端 sys_menu
                if (tableExists(conn, "sys_menu")) {
                    int n = deleteMenu(conn, "sys_menu", "sys_role_menu", dryRun);
                    if (n > 0) {
                        System.out.println("  sys_menu: deleted rows=" + n);
                        changed = true;
                    }
                } else {
                    System.out.println("  [SKIP] no sys_menu");
                }

                // 2. 租户销售端 company_menu
                if (tableExists(conn, "company_menu")) {
                    int n = deleteMenu(conn, "company_menu", "company_role_menu", dryRun);
                    if (n > 0) {
                        System.out.println("  company_menu: deleted rows=" + n);
                        changed = true;
                    }
                } else {
                    System.out.println("  [SKIP] no company_menu");
                }

                if (changed) {
                    ok++;
                } else {
                    skip++;
                }
            } catch (Exception e) {
                System.out.println("  [ERR] " + e.getMessage());
                fail++;
            }
        }

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

    /**
     * 删除指定菜单表中的记录，并清理对应的 role_menu 关联表。
     * 返回删除的菜单行数。
     */
    private static int deleteMenu(Connection conn, String menuTable, String roleMenuTable, boolean dryRun)
            throws SQLException {
        // 收集所有匹配的 menu_id（兜底匹配 path+component）
        List<Long> ids = new ArrayList<>();
        String sql = "SELECT menu_id FROM " + menuTable
                + " WHERE menu_id = ? "
                + " OR (path = ? AND component = ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, MENU_ID);
            ps.setString(2, PATH);
            ps.setString(3, COMPONENT);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    ids.add(rs.getLong(1));
                }
            }
        }
        if (ids.isEmpty()) {
            return 0;
        }

        if (dryRun) {
            System.out.println("  [DRY-RUN] " + menuTable + " would delete ids=" + ids);
            return ids.size();
        }

        // 先删 role_menu 关联
        if (tableExists(conn, roleMenuTable)) {
            String in = placeholders(ids.size());
            String delRole = "DELETE FROM " + roleMenuTable + " WHERE menu_id IN (" + in + ")";
            try (PreparedStatement ps = conn.prepareStatement(delRole)) {
                bindIds(ps, ids);
                int n = ps.executeUpdate();
                if (n > 0) {
                    System.out.println("  " + roleMenuTable + ": deleted rows=" + n);
                }
            }
        }

        // 再删 menu
        int total = 0;
        String delMenu = "DELETE FROM " + menuTable + " WHERE menu_id = ?";
        try (PreparedStatement ps = conn.prepareStatement(delMenu)) {
            for (Long id : ids) {
                ps.setLong(1, id);
                total += ps.executeUpdate();
            }
        }
        return total;
    }

    private static String placeholders(int n) {
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < n; i++) {
            if (i > 0) sb.append(',');
            sb.append('?');
        }
        return sb.toString();
    }

    private static void bindIds(PreparedStatement ps, List<Long> ids) throws SQLException {
        int i = 1;
        for (Long id : ids) {
            ps.setLong(i++, id);
        }
    }

    private static boolean tableExists(Connection conn, String table) throws SQLException {
        try (ResultSet rs = conn.getMetaData().getTables(null, null, table, null)) {
            return rs.next();
        }
    }

    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;
    }
}