ylrz_saas_pharmacy/java/scripts/ApplyLobsterPerformanceSchemaPatch.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * Patch schema gaps that cause optional-page / scheduler warnings.
 * Usage: java -cp mysql-connector-j.jar;. ApplyLobsterPerformanceSchemaPatch [db1 db2 ...]
 */
public class ApplyLobsterPerformanceSchemaPatch {

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

    private static final String TOKEN_TABLE = """
            CREATE TABLE IF NOT EXISTS `lobster_token_consumption` (
                `id` bigint NOT NULL AUTO_INCREMENT,
                `company_id` bigint NOT NULL COMMENT '\u516c\u53f8ID',
                `instance_id` bigint DEFAULT NULL COMMENT '\u5b9e\u4f8bID',
                `node_code` varchar(100) DEFAULT NULL COMMENT '\u8282\u70b9\u7f16\u7801',
                `model_identifier` varchar(200) DEFAULT NULL COMMENT '\u6a21\u578b\u6807\u8bc6',
                `consume_type` varchar(50) DEFAULT NULL COMMENT '\u6d88\u8017\u7c7b\u578b',
                `token_count` bigint DEFAULT 0 COMMENT 'Token\u6570\u91cf',
                `estimated_cost` decimal(18,6) DEFAULT NULL COMMENT '\u9884\u4f30\u6210\u672c',
                `request_time` datetime DEFAULT NULL COMMENT '\u8bf7\u6c42\u65f6\u95f4',
                `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                KEY `idx_company_time` (`company_id`, `request_time`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='\u9f99\u8679Token\u6d88\u8017\u8bb0\u5f55'
            """;

    public static void main(String[] args) throws Exception {
        String[] dbs = args.length > 0 ? args : new String[]{"fs_tenant_cs1", "ylrz_saas"};
        for (String db : dbs) {
            System.out.println("\n===== " + db + " =====");
            String url = "jdbc:mysql://" + HOST + "/" + db + "?useSSL=false&serverTimezone=GMT%2B8";
            try (Connection c = DriverManager.getConnection(url, USER, PWD);
                 Statement st = c.createStatement()) {
                st.execute(TOKEN_TABLE);
                addColumnIfMissing(st, c, "lobster_sales_corpus", "optimized_answer",
                        pickAfterColumn(c, "lobster_sales_corpus",
                                new String[]{"quality_status", "status", "sales_answer", "customer_question"},
                                "optimized_answer text NULL"));
                addColumnIfMissing(st, c, "lobster_sales_corpus", "ai_feedback",
                        pickAfterColumn(c, "lobster_sales_corpus",
                                new String[]{"optimized_answer", "sales_answer"},
                                "ai_feedback text NULL"));
                addColumnIfMissing(st, c, "company_workflow_lobster_task", "external_contact_id",
                        "ALTER TABLE company_workflow_lobster_task ADD COLUMN external_contact_id bigint DEFAULT NULL COMMENT 'qw_external_contact.id' AFTER binding_id");
                addColumnIfMissing(st, c, "company_workflow_lobster_task", "channel_type",
                        "ALTER TABLE company_workflow_lobster_task ADD COLUMN channel_type varchar(30) DEFAULT 'QW' COMMENT '\u6e20\u9053\u7c7b\u578b QW/WX/IM' AFTER external_contact_id");
                addColumnIfMissing(st, c, "company_workflow_lobster_task", "channel_contact_id",
                        "ALTER TABLE company_workflow_lobster_task ADD COLUMN channel_contact_id bigint DEFAULT NULL COMMENT '\u6e20\u9053\u8054\u7cfb\u4ebaID' AFTER channel_type");
                addColumnIfMissing(st, c, "company_workflow_lobster_task", "channel_context_json",
                        "ALTER TABLE company_workflow_lobster_task ADD COLUMN channel_context_json text COMMENT '\u6e20\u9053\u4e0a\u4e0b\u6587JSON' AFTER channel_contact_id");
                audit(c);
            }
        }
    }

    private static String pickAfterColumn(Connection c, String table, String[] candidates, String columnDef)
            throws Exception {
        for (String col : candidates) {
            if (columnExists(c, table, col)) {
                return "ALTER TABLE " + table + " ADD COLUMN " + columnDef + " AFTER " + col;
            }
        }
        return "ALTER TABLE " + table + " ADD COLUMN " + columnDef;
    }

    private static void addColumnIfMissing(Statement st, Connection c, String table, String column, String ddl)
            throws Exception {
        if (columnExists(c, table, column)) {
            System.out.println("[SKIP] " + table + "." + column);
            return;
        }
        st.execute(ddl);
        System.out.println("[OK] " + table + "." + column);
    }

    private static boolean columnExists(Connection c, String table, String column) throws Exception {
        try (ResultSet rs = c.getMetaData().getColumns(c.getCatalog(), null, table, column)) {
            return rs.next();
        }
    }

    private static void audit(Connection c) throws Exception {
        try (Statement st = c.createStatement()) {
            printExists(st, "lobster_token_consumption");
            printColumn(st, "lobster_sales_corpus", "optimized_answer");
            printColumn(st, "company_workflow_lobster_task", "external_contact_id");
        }
    }

    private static void printExists(Statement st, String table) throws Exception {
        try (ResultSet rs = st.executeQuery(
                "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='" + table + "'")) {
            rs.next();
            System.out.println("  " + table + " exists=" + (rs.getInt(1) > 0));
        }
    }

    private static void printColumn(Statement st, String table, String column) throws Exception {
        try (ResultSet rs = st.executeQuery(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='"
                        + table + "' AND COLUMN_NAME='" + column + "'")) {
            rs.next();
            System.out.println("  " + table + "." + column + " exists=" + (rs.getInt(1) > 0));
        }
    }
}