ylrz_saas_pharmacy/java/scripts/ApplyLobsterMenuIcons.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.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/** Apply V20260802 lobster menu icon SVG migration to master + all active tenant DBs. */
public class ApplyLobsterMenuIcons {

    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 String VERSION = "V20260803";
    private static final String SCRIPT_NAME = "V20260803_01__lobster_company_menu_icons_fixup.sql";
    private static final Path TENANT_SQL_PATH = Path.of(
            "d:/ylrz_saas_new/java/fs-agent/src/main/resources/db/migration/tenant/V20260803_01__lobster_company_menu_icons_fixup.sql");
    private static final Path MASTER_SQL_PATH = Path.of(
            "d:/ylrz_saas_new/java/fs-agent/src/main/resources/db/migration/master/V20260802_01__lobster_menu_icons_svg.sql");

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String tenantSql = readSql(TENANT_SQL_PATH);
        String masterSql = readSql(MASTER_SQL_PATH);

        System.out.println("=== MASTER " + MASTER_DB + " (tenant_sys_menu + tenant_company_menu) ===");
        try (Connection master = open(MASTER_HOST, MASTER_PORT, MASTER_DB, MASTER_USER, MASTER_PASSWORD)) {
            execScript(master, masterSql);
            reportIcons(master, "tenant_sys_menu");
            reportIcons(master, "tenant_company_menu");

            for (Long tenantId : loadActiveTenantIds(master)) {
                TenantDb tenant = loadTenant(master, tenantId);
                if (tenant == null) {
                    System.out.println("[SKIP] tenant " + tenantId + " (no db config)");
                    continue;
                }
                System.out.println("\n=== TENANT " + tenantId + " -> " + tenant.database + " ===");
                try (Connection c = open(tenant.host, tenant.port, tenant.database, tenant.user, tenant.password)) {
                    if (alreadyApplied(c)) {
                        System.out.println("[SKIP] " + VERSION + " already SUCCESS in tenant_db_migration");
                    } else {
                        execScript(c, tenantSql);
                        recordMigration(c);
                        System.out.println("[OK] applied " + SCRIPT_NAME);
                    }
                    reportIcons(c, "sys_menu");
                    reportIcons(c, "company_menu");
                } catch (Exception e) {
                    System.err.println("[FAIL] tenant " + tenantId + ": " + e.getMessage());
                    e.printStackTrace(System.err);
                }
            }
        }
        System.out.println("\nDone. Re-login tenant/sales UI to refresh menus.");
    }

    private static boolean alreadyApplied(Connection c) throws SQLException {
        try (Statement st = c.createStatement();
             ResultSet rs = st.executeQuery(
                     "SELECT 1 FROM tenant_db_migration WHERE version = '" + VERSION
                             + "' AND status = 'SUCCESS' LIMIT 1")) {
            return rs.next();
        } catch (SQLException e) {
            if (isMissingMigrationTable(e)) {
                return false;
            }
            throw e;
        }
    }

    private static void recordMigration(Connection c) throws SQLException {
        try {
            try (Statement st = c.createStatement()) {
                st.execute("DELETE FROM tenant_db_migration WHERE version = '" + VERSION + "'");
            }
            String sql = "INSERT INTO tenant_db_migration "
                    + "(version, script_name, checksum, status, started_at, finished_at, executor) "
                    + "VALUES (?, ?, '', 'SUCCESS', NOW(), NOW(), 'ApplyLobsterMenuIcons')";
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setString(1, VERSION);
                ps.setString(2, SCRIPT_NAME);
                ps.executeUpdate();
            }
        } catch (SQLException e) {
            if (isMissingMigrationTable(e)) {
                System.out.println("[WARN] tenant_db_migration table missing, skip record");
                return;
            }
            throw e;
        }
    }

    private static boolean isMissingMigrationTable(SQLException e) {
        String msg = e.getMessage();
        return msg != null && msg.contains("tenant_db_migration");
    }

    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) == '\uFEFF') {
            sql = sql.substring(1);
        }
        return sql;
    }

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

    private static void reportIcons(Connection c, String table) throws SQLException {
        System.out.println("--- " + table + " icons ---");
        String sql = "SELECT menu_id, menu_name, path, icon FROM " + table
                + " WHERE menu_id = 29900 OR parent_id = 29900 ORDER BY order_num, menu_id";
        try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            while (rs.next()) {
                System.out.printf("[%d] %s | %s | icon=%s%n",
                        rs.getLong("menu_id"),
                        rs.getString("menu_name"),
                        rs.getString("path"),
                        rs.getString("icon"));
            }
        }
    }

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