ylrz_saas_pharmacy/java/scripts/ApplyLobsterSchemaSync.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.ResultSet;
import java.sql.Statement;

/**
 * Apply lobster schema sync migration to tenant database(s).
 */
public class ApplyLobsterSchemaSync {
    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";

    public static void main(String[] args) throws Exception {
        String[] dbs = args.length > 0 ? args : new String[]{"fs_tenant_cs1"};
        String sql = Files.readString(
                Path.of("d:/ylrz_saas_new/java/fs-service/src/main/resources/db/tenant-initTable-migration-lobster-schema-sync.sql"),
                StandardCharsets.UTF_8);
        for (String db : dbs) {
            String url = "jdbc:mysql://" + HOST + "/" + db
                    + "?useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true&connectTimeout=15000&socketTimeout=120000";
            System.out.println("Applying lobster schema sync to " + db + " ...");
            try (Connection c = DriverManager.getConnection(url, USER, PWD);
                 Statement st = c.createStatement()) {
                st.execute(sql);
                for (String tbl : new String[]{"lobster_execution_config", "lobster_tenant_keywords", "lobster_user_profile"}) {
                    try (ResultSet rs = st.executeQuery(
                            "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='" + tbl + "'")) {
                        rs.next();
                        System.out.println("  " + tbl + ": " + (rs.getInt(1) > 0 ? "OK" : "MISSING"));
                    }
                }
                try (ResultSet rs = st.executeQuery(
                        "SELECT company_id FROM lobster_execution_config WHERE company_id=338 LIMIT 1")) {
                    if (rs.next()) {
                        System.out.println("  lobster_execution_config row for company 338: exists");
                    } else {
                        st.execute("INSERT IGNORE INTO lobster_execution_config (company_id) VALUES (338)");
                        System.out.println("  lobster_execution_config row for company 338: inserted default");
                    }
                } catch (Exception e) {
                    System.out.println("  seed company 338 config skipped: " + e.getMessage());
                }
                st.execute("CREATE TABLE IF NOT EXISTS lobster_tool_config ("
                        + "id BIGINT AUTO_INCREMENT PRIMARY KEY,"
                        + "company_id BIGINT NOT NULL DEFAULT 0,"
                        + "tool_name VARCHAR(128) NOT NULL,"
                        + "tool_type VARCHAR(64) DEFAULT NULL,"
                        + "description VARCHAR(500) DEFAULT NULL,"
                        + "config_json TEXT,"
                        + "enabled TINYINT NOT NULL DEFAULT 1,"
                        + "deleted TINYINT NOT NULL DEFAULT 0,"
                        + "create_time DATETIME DEFAULT CURRENT_TIMESTAMP,"
                        + "UNIQUE KEY uk_company_tool (company_id, tool_name)"
                        + ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
                System.out.println("  lobster_tool_config: ensured");
            }
            System.out.println("Done " + db);
        }
    }
}