ylrz_saas_pharmacy/java/scripts/InvestigateIndustryConfig.java

import java.sql.*;
import java.util.*;

/**
 * 行业配置调研程序：
 *   1) 列出 company 表中所有企业及其行业字段
 *   2) 列出 lobster_workflow / company_workflow_lobster 中所有工作流及其行业/场景
 *   3) 查找所有行业相关字典表/配置表
 *
 * 用法：
 *   javac -encoding UTF-8 -cp "C:\Users\77944\.m2\repository\com\mysql\mysql-connector-j\8.0.33\mysql-connector-j-8.0.33.jar" InvestigateIndustryConfig.java
 *   java -cp ".;C:\Users\77944\.m2\repository\com\mysql\mysql-connector-j\8.0.33\mysql-connector-j-8.0.33.jar" InvestigateIndustryConfig
 */
public class InvestigateIndustryConfig {

    private static final String HOST = "cq-cdb-8fjmemkb.sql.tencentcdb.com";
    private static final int PORT = 27220;
    private static final String USER = "root";
    private static final String PWD = "Ylrz_1q2w3e4r5t6y";
    private static final String TENANT_DB = "ylrz_saas_tenant_1";

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://" + HOST + ":" + PORT + "/" + TENANT_DB
                + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false";
        try (Connection c = DriverManager.getConnection(url, USER, PWD)) {
            System.out.println("========================================================");
            System.out.println(" 连接租户库: " + TENANT_DB);
            System.out.println("========================================================");

            // 0) 列出所有表
            printAllTables(c);

            // 1) company 表（或 tenant_company）企业及行业字段
            printSection("1) 企业表（company/tenant_company）行业字段");
            printCompanyIndustry(c);

            // 2) lobster_workflow 表中所有工作流及其行业/场景
            printSection("2) 龙虾工作流表（lobster_workflow / company_workflow_lobster）行业字段");
            printWorkflowIndustry(c);

            // 3) 所有行业相关字典/配置表
            printSection("3) 行业相关字典/配置表");
            printIndustryDicts(c);
        }
    }

    private static void printAllTables(Connection c) {
        System.out.println("\n=== 0) 所有表名（含 lobster / industry / company 字眼的）===");
        try (Statement st = c.createStatement();
             ResultSet rs = st.executeQuery(
                     "SELECT table_name FROM information_schema.tables WHERE table_schema=DATABASE() "
                             + "AND (table_name LIKE '%lobster%' OR table_name LIKE '%industry%' "
                             + "OR table_name LIKE '%company%' OR table_name LIKE '%workflow%' "
                             + "OR table_name LIKE '%dict%' OR table_name LIKE '%corpus%') "
                             + "ORDER BY table_name")) {
            while (rs.next()) {
                System.out.println("  " + rs.getString(1));
            }
        } catch (SQLException e) {
            System.out.println("  [ERR] " + e.getMessage());
        }
    }

    private static void printCompanyIndustry(Connection c) {
        // 探测 company 表的列名
        String[] candidates = {"company", "tenant_company"};
        for (String t : candidates) {
            if (!tableExists(c, t)) continue;
            System.out.println("\n-- 表: " + t);
            List<String> cols = tableColumns(c, t);
            System.out.println("   列: " + cols);
            // 找出与行业相关的列
            List<String> industryCols = new ArrayList<>();
            for (String col : cols) {
                String l = col.toLowerCase();
                if (l.contains("industry") || l.contains("scene")
                        || l.contains("type") || l.contains("category")
                        || l.contains("business")) {
                    industryCols.add(col);
                }
            }
            System.out.println("   行业相关列: " + industryCols);
            if (industryCols.isEmpty()) {
                continue;
            }
            StringBuilder sql = new StringBuilder("SELECT id, company_name");
            for (String col : industryCols) {
                if (!col.equalsIgnoreCase("id") && !col.equalsIgnoreCase("company_name")) {
                    sql.append(", ").append(col);
                }
            }
            sql.append(" FROM ").append(t).append(" WHERE del_flag='0' OR del_flag IS NULL ORDER BY id LIMIT 100");
            System.out.println("   SQL: " + sql);
            try (Statement st = c.createStatement();
                 ResultSet rs = st.executeQuery(sql.toString())) {
                int cnt = 0;
                while (rs.next()) {
                    StringBuilder row = new StringBuilder();
                    int n = rs.getMetaData().getColumnCount();
                    for (int i = 1; i <= n; i++) {
                        if (i > 1) row.append(" | ");
                        row.append(rs.getMetaData().getColumnLabel(i))
                                .append("=").append(rs.getString(i));
                    }
                    System.out.println("   " + row);
                    cnt++;
                }
                System.out.println("   共 " + cnt + " 行");
            } catch (SQLException e) {
                System.out.println("   [ERR] " + e.getMessage());
            }
        }
    }

    private static void printWorkflowIndustry(Connection c) {
        String[] candidates = {"lobster_workflow", "company_workflow_lobster", "lobster_workflow_template"};
        for (String t : candidates) {
            if (!tableExists(c, t)) continue;
            System.out.println("\n-- 表: " + t);
            List<String> cols = tableColumns(c, t);
            System.out.println("   列: " + cols);
            // 选取 id/name/industry/scene 等关键列
            List<String> wantCols = new ArrayList<>();
            for (String col : cols) {
                String l = col.toLowerCase();
                if (l.equals("id") || l.contains("name") || l.contains("industry")
                        || l.contains("scene") || l.contains("type")
                        || l.equals("status") || l.equals("template_code")) {
                    wantCols.add(col);
                }
            }
            if (wantCols.isEmpty()) continue;
            String sql = "SELECT " + String.join(", ", wantCols)
                    + " FROM " + t + " WHERE del_flag='0' OR del_flag IS NULL ORDER BY id LIMIT 200";
            System.out.println("   SQL: " + sql);
            try (Statement st = c.createStatement();
                 ResultSet rs = st.executeQuery(sql)) {
                int cnt = 0;
                while (rs.next()) {
                    StringBuilder row = new StringBuilder();
                    int n = rs.getMetaData().getColumnCount();
                    for (int i = 1; i <= n; i++) {
                        if (i > 1) row.append(" | ");
                        row.append(rs.getMetaData().getColumnLabel(i))
                                .append("=").append(rs.getString(i));
                    }
                    System.out.println("   " + row);
                    cnt++;
                }
                System.out.println("   共 " + cnt + " 行");
            } catch (SQLException e) {
                System.out.println("   [ERR] " + e.getMessage());
            }
        }
    }

    private static void printIndustryDicts(Connection c) {
        // 1. sys_dict_type / sys_dict_data 中的行业相关
        String[] dictTables = {"sys_dict_type", "sys_dict_data"};
        for (String t : dictTables) {
            if (!tableExists(c, t)) continue;
            System.out.println("\n-- 表: " + t);
            List<String> cols = tableColumns(c, t);
            String keyCol = cols.contains("dict_type") ? "dict_type" : (cols.contains("dict_key") ? "dict_key" : null);
            String labelCol = cols.contains("dict_label") ? "dict_label" : null;
            String valueCol = cols.contains("dict_value") ? "dict_value" : null;
            if (keyCol == null) {
                System.out.println("   跳过：找不到 dict_type/dict_key 列");
                continue;
            }
            // 找 dict_type 含 industry/行业/lobster 的
            String sql = "SELECT * FROM " + t + " WHERE " + keyCol
                    + " LIKE '%industry%' OR " + keyCol + " LIKE '%lobster%' OR "
                    + (labelCol != null ? labelCol + " LIKE '%行业%' OR " : "")
                    + (labelCol != null ? labelCol + " LIKE '%龙虾%' OR " : "")
                    + keyCol + " LIKE '%scene%' LIMIT 200";
            System.out.println("   SQL: " + sql);
            try (Statement st = c.createStatement();
                 ResultSet rs = st.executeQuery(sql)) {
                int cnt = 0;
                while (rs.next()) {
                    StringBuilder row = new StringBuilder();
                    int n = rs.getMetaData().getColumnCount();
                    for (int i = 1; i <= n; i++) {
                        if (i > 1) row.append(" | ");
                        row.append(rs.getMetaData().getColumnLabel(i))
                                .append("=").append(rs.getString(i));
                    }
                    System.out.println("   " + row);
                    cnt++;
                }
                System.out.println("   共 " + cnt + " 行");
            } catch (SQLException e) {
                System.out.println("   [ERR] " + e.getMessage());
            }
        }

        // 2. lobster 相关配置/字典表
        try (Statement st = c.createStatement();
             ResultSet rs = st.executeQuery(
                     "SELECT table_name FROM information_schema.tables WHERE table_schema=DATABASE() "
                             + "AND (table_name LIKE 'lobster_%' OR table_name LIKE '%industry_corpus%' "
                             + "OR table_name LIKE '%tenant_industry%' OR table_name LIKE '%industry_pack%') "
                             + "ORDER BY table_name")) {
            System.out.println("\n-- 行业/龙虾相关配置表清单：");
            List<String> tables = new ArrayList<>();
            while (rs.next()) {
                tables.add(rs.getString(1));
                System.out.println("   " + rs.getString(1));
            }
            // 对每个表给出列信息和前若干行
            for (String t : tables) {
                List<String> cols = tableColumns(c, t);
                System.out.println("\n   >>> 表 " + t + " 列: " + cols);
                String sql = "SELECT * FROM " + t + " LIMIT 5";
                try (Statement st2 = c.createStatement();
                     ResultSet rs2 = st2.executeQuery(sql)) {
                    while (rs2.next()) {
                        StringBuilder row = new StringBuilder();
                        int n = rs2.getMetaData().getColumnCount();
                        for (int i = 1; i <= n; i++) {
                            if (i > 1) row.append(" | ");
                            String v = rs2.getString(i);
                            if (v != null && v.length() > 80) v = v.substring(0, 80) + "...";
                            row.append(rs2.getMetaData().getColumnLabel(i)).append("=").append(v);
                        }
                        System.out.println("      " + row);
                    }
                } catch (SQLException e) {
                    System.out.println("      [ERR] " + e.getMessage());
                }
            }
        } catch (SQLException e) {
            System.out.println("   [ERR] " + e.getMessage());
        }
    }

    private static boolean tableExists(Connection c, String table) {
        try (Statement st = c.createStatement();
             ResultSet rs = st.executeQuery(
                     "SELECT 1 FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='"
                             + table + "'")) {
            return rs.next();
        } catch (SQLException e) {
            return false;
        }
    }

    private static List<String> tableColumns(Connection c, String table) {
        List<String> cols = new ArrayList<>();
        try (Statement st = c.createStatement();
             ResultSet rs = st.executeQuery(
                     "SELECT column_name FROM information_schema.columns WHERE table_schema=DATABASE() "
                             + "AND table_name='" + table + "' ORDER BY ordinal_position")) {
            while (rs.next()) {
                cols.add(rs.getString(1));
            }
        } catch (SQLException e) {
            System.out.println("   [columns ERR] " + e.getMessage());
        }
        return cols;
    }

    private static void printSection(String title) {
        System.out.println("\n========================================================");
        System.out.println(" " + title);
        System.out.println("========================================================");
    }
}