ylrz_saas_pharmacy/java/scripts/ApplyIndustryCorpusReseed.java

import com.fs.company.service.workflow.config.LobsterIndustryCatalog;
import com.fs.company.service.workflow.corpus.LobsterIndustryCorpusSeedCatalog;

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;

/**
 * Re-insert industry matrix seed corpus after garbled cleanup.
 * Requires fs-service compiled classes on classpath.
 * Usage: java -cp mysql.jar;../fs-service/target/classes;. ApplyIndustryCorpusReseed [tenantDbName]
 */
public class ApplyIndustryCorpusReseed {

    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");

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        try (Connection master = open(MASTER_HOST, MASTER_PORT, MASTER_DB, MASTER_USER, MASTER_PASSWORD)) {
            List<Long> tenantIds = loadActiveTenantIds(master);
            if (args.length > 0) {
                tenantIds.clear();
                for (String db : args) {
                    reseedTenantDb(master, db);
                }
                return;
            }
            for (Long tenantId : tenantIds) {
                TenantDb tenant = loadTenant(master, tenantId);
                if (tenant == null) {
                    continue;
                }
                reseedConn(master, tenantId, tenant);
            }
        }
    }

    private static void reseedTenantDb(Connection master, String dbName) throws Exception {
        try (Statement st = master.createStatement();
             ResultSet rs = st.executeQuery(
                     "SELECT id FROM tenant_info WHERE status=1 AND db_url LIKE '%/" + dbName + "%' LIMIT 1")) {
            if (!rs.next()) {
                System.out.println("[SKIP] no tenant for db " + dbName);
                return;
            }
            long tenantId = rs.getLong(1);
            TenantDb tenant = loadTenant(master, tenantId);
            if (tenant != null) {
                reseedConn(master, tenantId, tenant);
            }
        }
    }

    private static void reseedConn(Connection master, long tenantId, TenantDb tenant) throws Exception {
        System.out.println("\n[TENANT " + tenantId + "] " + tenant.database);
        try (Connection conn = open(tenant.host, tenant.port, tenant.database, tenant.user, tenant.password)) {
            if (!columnExists(conn, "lobster_sales_corpus", "customer_question")) {
                System.out.println("  [SKIP] lobster_sales_corpus schema not ready");
                return;
            }
            Long companyId = loadPrimaryCompanyId(conn, tenantId, master);
            if (companyId == null) {
                System.out.println("  [SKIP] no company_id");
                return;
            }
            int total = 0;
            if (!columnExists(conn, "lobster_sales_corpus", "industry_type")) {
                deleteAllIndustrySeeds(conn, companyId);
            }
            for (LobsterIndustryCatalog.IndustryPack pack : LobsterIndustryCatalog.allPacks()) {
                String code = pack.code;
                String label = LobsterIndustryCatalog.labelZh(code);
                if (columnExists(conn, "lobster_sales_corpus", "industry_type")) {
                    deleteExistingSeeds(conn, companyId, code);
                }
                List<LobsterIndustryCorpusSeedCatalog.SeedEntry> seeds =
                        LobsterIndustryCorpusSeedCatalog.entriesFor(code, label);
                for (LobsterIndustryCorpusSeedCatalog.SeedEntry seed : seeds) {
                    insertSeed(conn, companyId, code, seed);
                    total++;
                }
                System.out.println("  industry=" + code + " seeds=" + seeds.size());
            }
            System.out.println("  inserted_total=" + total);
        }
    }

    private static void deleteExistingSeeds(Connection conn, long companyId, String industryCode) throws SQLException {
        if (columnExists(conn, "lobster_sales_corpus", "industry_type")) {
            String sql = "DELETE FROM lobster_sales_corpus WHERE company_id=? AND industry_type=? "
                    + "AND source_type='industry_seed'";
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                ps.setLong(1, companyId);
                ps.setString(2, industryCode);
                ps.executeUpdate();
            }
            return;
        }
        String sql = "DELETE FROM lobster_sales_corpus WHERE company_id=? AND source_type='industry_seed' "
                + "AND scenario=?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, companyId);
            ps.setString(2, industryCode);
            ps.executeUpdate();
        }
    }

    private static void deleteAllIndustrySeeds(Connection conn, long companyId) throws SQLException {
        String sql = "DELETE FROM lobster_sales_corpus WHERE company_id=? AND source_type='industry_seed'";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, companyId);
            ps.executeUpdate();
        }
    }

    private static void insertSeed(Connection conn, long companyId, String industryCode,
                                   LobsterIndustryCorpusSeedCatalog.SeedEntry seed) throws SQLException {
        String dialog = "\u5ba2\u6237: " + seed.customerQuestion + "\n\u9500\u552e: " + seed.salesAnswer;
        boolean hasIndustryType = columnExists(conn, "lobster_sales_corpus", "industry_type");
        boolean hasSalesperson = columnExists(conn, "lobster_sales_corpus", "salesperson_name");
        StringBuilder cols = new StringBuilder("company_id");
        StringBuilder vals = new StringBuilder("?");
        List<Object> params = new ArrayList<>();
        params.add(companyId);
        if (hasIndustryType) {
            cols.append(", industry_type");
            vals.append(", ?");
            params.add(industryCode);
        }
        if (hasSalesperson) {
            cols.append(", salesperson_name");
            vals.append(", ?");
            params.add("\u884c\u4e1a\u8bed\u6599");
        }
        cols.append(", customer_question, sales_answer, dialog_text, scenario, source_type");
        vals.append(", ?, ?, ?, ?, ?");
        params.add(seed.customerQuestion);
        params.add(seed.salesAnswer);
        params.add(dialog);
        params.add(seed.scenario);
        params.add("industry_seed");
        if (columnExists(conn, "lobster_sales_corpus", "status")) {
            cols.append(", status, quality_status");
            vals.append(", ?, ?");
            params.add("analyzed");
            params.add("qualified");
        }
        if (columnExists(conn, "lobster_sales_corpus", "score")) {
            cols.append(", score");
            vals.append(", ?");
            params.add(new java.math.BigDecimal("85"));
        }
        if (columnExists(conn, "lobster_sales_corpus", "kb_written")) {
            cols.append(", kb_written");
            vals.append(", ?");
            params.add(0);
        }
        if (columnExists(conn, "lobster_sales_corpus", "enabled")) {
            cols.append(", enabled");
            vals.append(", ?");
            params.add(1);
        }
        cols.append(", create_by, create_time");
        vals.append(", ?, NOW()");
        params.add("system");
        String sql = "INSERT INTO lobster_sales_corpus (" + cols + ") VALUES (" + vals + ")";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            for (int i = 0; i < params.size(); i++) {
                ps.setObject(i + 1, params.get(i));
            }
            ps.executeUpdate();
        }
    }

    private static Long loadPrimaryCompanyId(Connection conn, long tenantId, Connection master) throws SQLException {
        try (PreparedStatement ps = master.prepareStatement(
                "SELECT company_id FROM tenant_info WHERE id=? AND status=1 LIMIT 1")) {
            ps.setLong(1, tenantId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next() && rs.getObject("company_id") != null) {
                    return rs.getLong("company_id");
                }
            }
        } catch (SQLException ignored) {
        }
        if (tableExists(conn, "lobster_sales_corpus")) {
            try (Statement st = conn.createStatement();
                 ResultSet rs = st.executeQuery(
                         "SELECT company_id FROM lobster_sales_corpus WHERE company_id IS NOT NULL "
                                 + "ORDER BY id DESC LIMIT 1")) {
                if (rs.next()) {
                    return rs.getLong(1);
                }
            }
        }
        String[] tables = {"company_info", "sys_company"};
        String[] cols = {"company_id", "id"};
        for (int i = 0; i < tables.length; i++) {
            if (!tableExists(conn, tables[i])) {
                continue;
            }
            try (Statement st = conn.createStatement();
                 ResultSet rs = st.executeQuery(
                         "SELECT " + cols[i] + " FROM " + tables[i] + " ORDER BY " + cols[i] + " LIMIT 1")) {
                if (rs.next()) {
                    return rs.getLong(1);
                }
            } catch (SQLException ignored) {
            }
        }
        return null;
    }

    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 boolean tableExists(Connection conn, String table) throws SQLException {
        try (ResultSet rs = conn.getMetaData().getTables(conn.getCatalog(), null, table, null)) {
            return rs.next();
        }
    }

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