ylrz_saas_pharmacy/java/scripts/ApplyGarbledDbRepairAll.java

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

/**
 * Apply garbled-text repair SQL to master + all active tenant DBs.
 * Usage: java -cp mysql-connector-j.jar;. ApplyGarbledDbRepairAll
 */
public class ApplyGarbledDbRepairAll {

    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 JAVA_ROOT = Paths.get(System.getProperty("user.dir")).getParent();

    private static final Path[] TENANT_SQL = {
            JAVA_ROOT.resolve("fs-agent/src/main/resources/db/migration/tenant/V20260724_01__lobster_industry_corpus_garbled_repair.sql"),
            JAVA_ROOT.resolve("fs-agent/src/main/resources/db/migration/tenant/V20260724_02__billing_and_menu_comment_repair.sql"),
    };

    private static final Path[] MASTER_SQL = {
            JAVA_ROOT.resolve("sql/master/V20260724_01__lobster_industry_corpus_garbled_repair.sql"),
            JAVA_ROOT.resolve("sql/fix_tenant_sys_menu_other_parent.sql"),
    };

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        System.out.println("=== Garbled DB repair (master + tenants) ===");

        try (Connection master = open(MASTER_HOST, MASTER_PORT, MASTER_DB, MASTER_USER, MASTER_PASSWORD)) {
            for (Path p : MASTER_SQL) {
                applyFile(master, "[MASTER]", p);
            }
            verifyMasterMenus(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)) {
                    for (Path p : TENANT_SQL) {
                        applyFile(conn, "  ", p);
                    }
                    verifyTenant(conn);
                    ok++;
                } catch (Exception e) {
                    System.out.println("  [ERR] " + e.getMessage());
                    e.printStackTrace(System.out);
                    fail++;
                }
            }

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

    private static void applyFile(Connection conn, String prefix, Path path) throws Exception {
        if (!Files.exists(path)) {
            System.out.println(prefix + "[SKIP missing] " + path.getFileName());
            return;
        }
        String sql = stripLineComments(readUtf8(path));
        System.out.println(prefix + "[APPLY] " + path.getFileName());
        for (String stmt : splitStatements(sql)) {
            if (stmt.isBlank()) {
                continue;
            }
            try (Statement st = conn.createStatement()) {
                st.execute(stmt);
            } catch (SQLException e) {
                String msg = e.getMessage() != null ? e.getMessage() : "";
                if (msg.contains("Unknown column") || msg.contains("doesn't exist")) {
                    System.out.println(prefix + "[SKIP stmt] " + truncate(stmt) + " -> " + msg);
                } else {
                    throw e;
                }
            }
        }
    }

    private static String truncate(String stmt) {
        String oneLine = stmt.replaceAll("\\s+", " ").trim();
        return oneLine.length() > 80 ? oneLine.substring(0, 80) + "..." : oneLine;
    }

    private static List<String> splitStatements(String sql) {
        List<String> list = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        boolean inSingle = false;
        for (int i = 0; i < sql.length(); i++) {
            char c = sql.charAt(i);
            if (c == '\'') {
                inSingle = !inSingle;
                sb.append(c);
                continue;
            }
            if (c == ';' && !inSingle) {
                list.add(sb.toString().trim());
                sb.setLength(0);
                continue;
            }
            sb.append(c);
        }
        if (sb.length() > 0) {
            list.add(sb.toString().trim());
        }
        return list;
    }

    private static void verifyMasterMenus(Connection conn) throws SQLException {
        try (Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery(
                     "SELECT menu_id, menu_name FROM tenant_sys_menu WHERE menu_id IN (3008,35300) ORDER BY menu_id")) {
            System.out.println("\n[MASTER verify tenant_sys_menu]");
            while (rs.next()) {
                System.out.println("  " + rs.getLong(1) + " = " + rs.getString(2));
            }
        }
    }

    private static void verifyTenant(Connection conn) throws SQLException {
        int badMenu = scalar(conn,
                "SELECT COUNT(*) FROM sys_menu WHERE menu_name LIKE '%?%' OR HEX(menu_name) LIKE '3F3F%'");
        System.out.println("  garbled sys_menu=" + badMenu);
        if (!columnExists(conn, "lobster_sales_corpus", "customer_question")) {
            System.out.println("  garbled industry_seed=skipped(no corpus columns)");
            return;
        }
        int badCorpus = scalar(conn,
                "SELECT COUNT(*) FROM lobster_sales_corpus WHERE source_type='industry_seed' "
                        + "AND (customer_question LIKE '%?%' OR sales_answer LIKE '%?%')");
        System.out.println("  garbled industry_seed=" + badCorpus);
    }

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

    private static int scalar(Connection conn, String sql) throws SQLException {
        try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            rs.next();
            return rs.getInt(1);
        }
    }

    private static String stripLineComments(String sql) {
        StringBuilder out = new StringBuilder();
        for (String line : sql.split("\\R")) {
            int idx = line.indexOf("--");
            if (idx >= 0) {
                line = line.substring(0, idx);
            }
            out.append(line).append('\n');
        }
        return out.toString();
    }

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

    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 host, int port, String db, String user, String password)
            throws SQLException {
        return DriverManager.getConnection(
                "jdbc:mysql://" + host + ":" + port + "/" + db
                        + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&allowMultiQueries=true",
                user, password);
    }

    private static String getenv(String key, String def) {
        String v = System.getenv(key);
        return v != null && !v.isBlank() ? v : def;
    }

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