ylrz_saas_pharmacy/java/scripts/ApplyLobsterIntentKbMigration.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.Statement;
import java.util.ArrayList;
import java.util.List;

/** Apply V20260724_01 DDL + UTF-8 safe JDBC seeds to tenant DB(s). */
public class ApplyLobsterIntentKbMigration {

    private static final String HOST = "cq-cdb-8fjmemkb.sql.tencentcdb.com:27220";
    private static final String USER = "root";
    private static final String PASS = "Ylrz_1q2w3e4r5t6y";
    private static final Path SQL_PATH = Path.of("d:/ylrz_saas_new/java/fs-agent/src/main/resources/db/migration/tenant/V20260724_01__lobster_intent_kb_and_fixed_reply.sql");

    private static final String REPLY_HANDOFF =
            "\u597d\u7684\uff0c\u5df2\u4e3a\u60a8\u5b89\u6392\u4e13\u4eba\u8ddf\u8fdb\uff0c\u8bf7\u7a0d\u5019\u3002";
    private static final String REPLY_VIDEO =
            "\u6536\u5230\u60a8\u7684\u89c6\u9891\u4e86\uff0c\u6211\u5df2\u8f6c\u4ea4\u540c\u4e8b\u67e5\u770b\uff0c\u7a0d\u540e\u56de\u590d\u60a8\u3002";
    private static final String REPLY_FILE =
            "\u6536\u5230\u60a8\u7684\u6587\u4ef6\u4e86\uff0c\u6211\u5df2\u8f6c\u4ea4\u540c\u4e8b\u5904\u7406\uff0c\u7a0d\u540e\u56de\u590d\u60a8\u3002";

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String target = args.length > 0 ? args[0] : "fs_tenant_cs1";
        String ddl = loadSql();
        List<String> dbs = new ArrayList<>();
        if ("all".equalsIgnoreCase(target)) {
            String rootUrl = "jdbc:mysql://" + HOST + "/?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=utf8";
            try (Connection c = DriverManager.getConnection(rootUrl, USER, PASS);
                 Statement st = c.createStatement();
                 ResultSet rs = st.executeQuery("SHOW DATABASES LIKE 'fs_tenant_%'")) {
                while (rs.next()) {
                    dbs.add(rs.getString(1));
                }
            }
            System.out.println("Found " + dbs.size() + " tenant databases");
        } else {
            dbs.add(target);
        }

        int ok = 0;
        int fail = 0;
        for (String db : dbs) {
            try {
                apply(db, ddl);
                ok++;
            } catch (Exception e) {
                fail++;
                System.err.println("[FAIL] " + db + ": " + e.getMessage());
            }
        }
        System.out.println("Done. ok=" + ok + " fail=" + fail);
    }

    private static void apply(String db, String ddl) throws Exception {
        String url = "jdbc:mysql://" + HOST + "/" + db
                + "?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=utf8&allowMultiQueries=true";
        try (Connection c = DriverManager.getConnection(url, USER, PASS)) {
            try (Statement st = c.createStatement()) {
                st.execute(ddl);
            }
            seedViaJdbc(c);
            try (Statement st = c.createStatement();
                 ResultSet rs = st.executeQuery(
                         "SELECT (SELECT COUNT(*) FROM lobster_intent_kb_mapping) kb_cnt,"
                                 + " (SELECT COUNT(*) FROM lobster_fixed_reply_rule) reply_cnt")) {
                if (rs.next()) {
                    System.out.println("[OK] " + db + " kb=" + rs.getInt(1) + " fixedReply=" + rs.getInt(2));
                }
            }
        }
    }

    private static void seedViaJdbc(Connection c) throws Exception {
        upsertKbMapping(c, "*", "knowledge", 0);
        upsertFixedReply(c, "signal", "HANDOFF", REPLY_HANDOFF, 10);
        upsertFixedReply(c, "signal", "MEDIA_VIDEO", REPLY_VIDEO, 10);
        upsertFixedReply(c, "signal", "MEDIA_FILE", REPLY_FILE, 10);
    }

    private static void upsertKbMapping(Connection c, String intent, String category, int priority) throws Exception {
        String update = "UPDATE lobster_intent_kb_mapping SET kb_category=?, priority=?, enabled=1 "
                + "WHERE company_id=0 AND intent=?";
        try (PreparedStatement ps = c.prepareStatement(update)) {
            ps.setString(1, category);
            ps.setInt(2, priority);
            ps.setString(3, intent);
            if (ps.executeUpdate() > 0) {
                return;
            }
        }
        String insert = "INSERT INTO lobster_intent_kb_mapping "
                + "(company_id, intent, kb_category, priority, enabled, create_time) VALUES (0,?,?,?,1,NOW())";
        try (PreparedStatement ps = c.prepareStatement(insert)) {
            ps.setString(1, intent);
            ps.setString(2, category);
            ps.setInt(3, priority);
            ps.executeUpdate();
        }
    }

    private static void upsertFixedReply(Connection c, String matchType, String matchKey,
                                           String reply, int priority) throws Exception {
        String update = "UPDATE lobster_fixed_reply_rule SET reply_template=?, priority=?, enabled=1 "
                + "WHERE company_id=0 AND match_type=? AND match_key=?";
        try (PreparedStatement ps = c.prepareStatement(update)) {
            ps.setString(1, reply);
            ps.setInt(2, priority);
            ps.setString(3, matchType);
            ps.setString(4, matchKey);
            if (ps.executeUpdate() > 0) {
                return;
            }
        }
        String insert = "INSERT INTO lobster_fixed_reply_rule "
                + "(company_id, match_type, match_key, reply_template, priority, enabled, create_time) "
                + "VALUES (0,?,?,?,?,1,NOW())";
        try (PreparedStatement ps = c.prepareStatement(insert)) {
            ps.setString(1, matchType);
            ps.setString(2, matchKey);
            ps.setString(3, reply);
            ps.setInt(4, priority);
            ps.executeUpdate();
        }
    }

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