ylrz_saas_pharmacy/java/scripts/ScanLobsterWorkflowColumns.java

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

/** Scan all active tenants for missing company_workflow_lobster.execution_mode */
public class ScanLobsterWorkflowColumns {

    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[] REQUIRED = {
            "execution_mode", "enable_content_personalization", "enable_flow_personalization",
            "strict_fixed_workflow", "auto_start_instance_on_tag", "template_kind", "source_template_id"
    };

    public static void main(String[] args) throws Exception {
        List<Long> missingTenants = new ArrayList<>();
        try (Connection master = open(MASTER_HOST, MASTER_PORT, MASTER_DB, MASTER_USER, MASTER_PASSWORD)) {
            List<TenantDb> tenants = loadAllTenants(master);
            for (TenantDb tenant : tenants) {
                try (Connection conn = open(tenant.host, tenant.port, tenant.database, tenant.user, tenant.password)) {
                    List<String> missing = new ArrayList<>();
                    for (String col : REQUIRED) {
                        if (!columnExists(conn, "company_workflow_lobster", col)) {
                            missing.add(col);
                        }
                    }
                    if (!missing.isEmpty()) {
                        missingTenants.add(tenant.id);
                        System.out.println("[MISSING] tenantId=" + tenant.id + " db=" + tenant.database + " cols=" + missing);
                    }
                } catch (Exception e) {
                    System.out.println("[ERROR] tenantId=" + tenant.id + " db=" + tenant.database + " " + e.getMessage());
                }
            }
        }
        if (missingTenants.isEmpty()) {
            System.out.println("[OK] all active tenants have required columns");
        } else {
            System.out.println("[SUMMARY] tenants needing migration: " + missingTenants);
        }
    }

    private static List<TenantDb> loadAllTenants(Connection master) throws SQLException {
        List<TenantDb> list = new ArrayList<>();
        String sql = "SELECT id, db_url, db_account, db_pwd FROM tenant_info WHERE status = 1 ORDER BY id";
        try (Statement st = master.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            while (rs.next()) {
                TenantDb t = parseJdbcUrl(rs.getString("db_url"), rs.getString("db_account"), rs.getString("db_pwd"));
                t.id = rs.getLong("id");
                list.add(t);
            }
        }
        return list;
    }

    private static boolean columnExists(Connection conn, String table, String column) throws SQLException {
        String sql = "SELECT COUNT(*) FROM information_schema.COLUMNS "
                + "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, table);
            ps.setString(2, column);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() && rs.getInt(1) > 0;
            }
        }
    }

    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;
        String host;
        int port;
        int colon = hostPort.indexOf(':');
        if (colon >= 0) {
            host = hostPort.substring(0, colon);
            port = Integer.parseInt(hostPort.substring(colon + 1));
        } else {
            host = hostPort;
            port = 3306;
        }
        TenantDb t = new TenantDb();
        t.host = host;
        t.port = port;
        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 {
        String url = "jdbc:mysql://" + host + ":" + port + "/" + database
                + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&allowMultiQueries=true";
        return DriverManager.getConnection(url, 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 {
        long id;
        String host;
        int port;
        String database;
        String user;
        String password;
    }
}