ylrz_saas_pharmacy/java/scripts/ApplyLobsterLearningTables.java

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

/** Ensure Phase2 learning tables exist on tenant DB. */
public class ApplyLobsterLearningTables {
    public static void main(String[] args) throws Exception {
        String db = args.length > 0 ? args[0] : "fs_tenant_cs1";
        String url = "jdbc:mysql://cq-cdb-8fjmemkb.sql.tencentcdb.com:27220/" + db
                + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8";
        String user = "root";
        String pass = "Ylrz_1q2w3e4r5t6y";
        String[] sqls = {
                "CREATE TABLE IF NOT EXISTS lobster_learning_event_log ("
                        + "id bigint NOT NULL AUTO_INCREMENT, company_id bigint NOT NULL, instance_id bigint DEFAULT NULL,"
                        + "node_code varchar(100) DEFAULT NULL, event_type varchar(50) DEFAULT NULL,"
                        + "quality_score double DEFAULT NULL, context_snapshot text,"
                        + "create_time datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id),"
                        + "KEY idx_company_event (company_id, event_type)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
                "CREATE TABLE IF NOT EXISTS lobster_learning_replay_buffer ("
                        + "id bigint NOT NULL AUTO_INCREMENT, company_id bigint NOT NULL, instance_id bigint DEFAULT NULL,"
                        + "node_code varchar(100) DEFAULT NULL, customer_message text, ai_reply text,"
                        + "quality_score double DEFAULT NULL, create_time datetime DEFAULT CURRENT_TIMESTAMP,"
                        + "PRIMARY KEY (id), KEY idx_company_id (company_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
                "CREATE TABLE IF NOT EXISTS lobster_learning_replay_archive ("
                        + "id bigint NOT NULL AUTO_INCREMENT, company_id bigint NOT NULL, instance_id bigint DEFAULT NULL,"
                        + "node_code varchar(100) DEFAULT NULL, customer_message text, ai_reply text,"
                        + "quality_score double DEFAULT NULL, create_time datetime DEFAULT NULL,"
                        + "archived_time datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id),"
                        + "KEY idx_company_archived (company_id, archived_time)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
                "ALTER TABLE lobster_learning_replay_buffer "
                        + "ADD INDEX idx_company_quality_time (company_id, quality_score, create_time)"
        };
        try (Connection c = DriverManager.getConnection(url, user, pass); Statement st = c.createStatement()) {
            for (String sql : sqls) {
                try {
                    st.execute(sql);
                    System.out.println("[OK] " + sql.substring(0, Math.min(60, sql.length())) + "...");
                } catch (Exception e) {
                    String msg = e.getMessage() != null ? e.getMessage() : "";
                    if (msg.contains("Duplicate key name") || msg.contains("1061")) {
                        System.out.println("[SKIP] index exists");
                    } else {
                        System.err.println("[WARN] " + msg);
                    }
                }
            }
        }
        System.out.println("Done " + db);
    }
}