ylrz_saas_pharmacy/java/scripts/FixLobsterMenuGarbledNames.java

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

/** Fix lobster menu names corrupted as literal u5de5... unicode escapes. */
public class FixLobsterMenuGarbledNames {

    private static final String MASTER_HOST = "cq-cdb-8fjmemkb.sql.tencentcdb.com";
    private static final int MASTER_PORT = 27220;
    private static final String MASTER_USER = "root";
    private static final String MASTER_PASSWORD = "Ylrz_1q2w3e4r5t6y";
    private static final String MASTER_DB = "ylrz_saas";

    private static final String TEMPLATE = "\u5de5\u4f5c\u6d41\u6a21\u677f";
    private static final String EXECUTION = "\u5de5\u4f5c\u6d41\u6267\u884c";
    private static final String PROFILE = "\u5ba2\u6237\u753b\u50cf";

    public static void main(String[] args) throws Exception {
        try (Connection master = open(MASTER_DB)) {
            fixConn(master, "tenant_company_menu", true);
            fixConn(master, "sys_menu", true);

            for (Long tenantId : loadActiveTenantIds(master)) {
                TenantDb t = loadTenant(master, tenantId);
                if (t == null) continue;
                System.out.println("[TENANT " + tenantId + "] " + t.database);
                try (Connection c = openDb(t)) {
                    int n = fixConn(c, "company_menu", false);
                    n += fixConn(c, "sys_menu", false);
                    System.out.println("  fixed_rows=" + n);
                    verify(c);
                }
            }
        }
    }

    private static int fixConn(Connection conn, String table, boolean master) throws SQLException {
        int total = 0;
        total += fixByMenuId(conn, table, 32481, TEMPLATE);
        total += fixByMenuId(conn, table, 29903, TEMPLATE);
        total += fixByMenuId(conn, table, 29949, EXECUTION);
        total += fixByMenuId(conn, table, 29950, PROFILE);
        total += fixGarbledPattern(conn, table, "u5de5u4f5cu6d41u6a21u677f", TEMPLATE);
        total += fixGarbledPattern(conn, table, "u5de5u4f5cu6d41u6267u884c", EXECUTION);
        total += fixGarbledPattern(conn, table, "u5ba2u6237u753bu50cf", PROFILE);
        total += fixGarbledPattern(conn, table, "\\u5de5\\u4f5c\\u6d41\\u6a21\\u677f", TEMPLATE);
        total += fixGarbledPattern(conn, table, "\\u5de5\\u4f5c\\u6d41\\u6267\\u884c", EXECUTION);
        total += fixLikeGarbled(conn, table, TEMPLATE, EXECUTION, PROFILE);
        return total;
    }

    private static int fixByMenuId(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);
            return ps.executeUpdate();
        }
    }

    private static int fixGarbledPattern(Connection conn, String table, String bad, String good) throws SQLException {
        String sql = "UPDATE " + table + " SET menu_name = ? WHERE menu_name = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, good);
            ps.setString(2, bad);
            return ps.executeUpdate();
        }
    }

    private static int fixLikeGarbled(Connection conn, String table, String template, String execution, String profile)
            throws SQLException {
        int n = 0;
        n += fixLike(conn, table, "u5de5u4f5cu6d41u6a21%", template);
        n += fixLike(conn, table, "u5de5u4f5cu6d41u6267%", execution);
        n += fixLike(conn, table, "u5ba2u6237u753b%", profile);
        n += fixLike(conn, table, "\\\\u5de5\\\\u4f5c\\\\u6d41\\\\u6a21%", template);
        n += fixLike(conn, table, "\\\\u5de5\\\\u4f5c\\\\u6d41\\\\u6267%", execution);
        return n;
    }

    private static int fixLike(Connection conn, String table, String like, String good) throws SQLException {
        String sql = "UPDATE " + table + " SET menu_name = ? WHERE menu_name LIKE ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, good);
            ps.setString(2, like);
            return ps.executeUpdate();
        }
    }

    private static void verify(Connection conn) throws SQLException {
        String sql = "SELECT menu_id, menu_name FROM company_menu "
                + "WHERE parent_id = 29900 AND menu_id IN (32481, 29949, 29950) ORDER BY menu_id";
        try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            while (rs.next()) {
                System.out.println("  menu " + rs.getLong(1) + " = " + rs.getString(2));
            }
        }
        String bad = "SELECT COUNT(*) FROM company_menu WHERE menu_name LIKE 'u5de5%' OR menu_name LIKE '\\\\u5de5%'";
        try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(bad)) {
            rs.next();
            System.out.println("  garbled_remaining=" + rs.getInt(1));
        }
    }

    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;
                TenantDb t = new TenantDb();
                String url = rs.getString("db_url");
                String body = url.substring("jdbc:mysql://".length());
                int slash = body.indexOf('/');
                String hostPort = body.substring(0, slash);
                String db = body.substring(slash + 1).replaceAll("\\?.*", "");
                int colon = hostPort.indexOf(':');
                t.host = colon >= 0 ? hostPort.substring(0, colon) : hostPort;
                t.port = colon >= 0 ? Integer.parseInt(hostPort.substring(colon + 1)) : 3306;
                t.database = db;
                t.user = rs.getString("db_account");
                t.password = rs.getString("db_pwd");
                return t;
            }
        }
    }

    private static Connection open(String db) throws SQLException {
        return DriverManager.getConnection(
                "jdbc:mysql://" + MASTER_HOST + ":" + MASTER_PORT + "/" + db
                        + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8",
                MASTER_USER, MASTER_PASSWORD);
    }

    private static Connection openDb(TenantDb t) throws SQLException {
        return DriverManager.getConnection(
                "jdbc:mysql://" + t.host + ":" + t.port + "/" + t.database
                        + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8",
                t.user != null ? t.user : MASTER_USER,
                t.password != null ? t.password : MASTER_PASSWORD);
    }

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