ylrz_saas_pharmacy/java/scripts/ApplyCustomerProfileDomainMigration.java

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

/**
 * Apply customer profile domain tables (V20260701_01) to tenant DB(s).
 */
public class ApplyCustomerProfileDomainMigration {
    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 IDENTITY_SQL =
            "CREATE TABLE IF NOT EXISTS `customer_profile_identity` ("
                    + "`id` BIGINT NOT NULL AUTO_INCREMENT,"
                    + "`company_id` BIGINT NOT NULL,"
                    + "`channel_type` VARCHAR(8) NOT NULL,"
                    + "`profile_key` VARCHAR(128) NOT NULL,"
                    + "`customer_id` BIGINT DEFAULT NULL,"
                    + "`shard_id` INT NOT NULL,"
                    + "`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,"
                    + "`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,"
                    + "PRIMARY KEY (`id`),"
                    + "UNIQUE KEY `uk_identity` (`company_id`, `channel_type`, `profile_key`),"
                    + "KEY `idx_customer` (`company_id`, `customer_id`),"
                    + "KEY `idx_shard` (`company_id`, `shard_id`)"
                    + ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

    public static void main(String[] args) throws Exception {
        String[] dbs = args.length > 0 ? args : new String[]{"fs_tenant_cs1"};
        for (String db : dbs) {
            apply(db);
        }
    }

    private static void apply(String db) throws Exception {
        String url = "jdbc:mysql://" + HOST + "/" + db
                + "?useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true";
        try (Connection c = DriverManager.getConnection(url, USER, PWD);
             Statement st = c.createStatement()) {
            st.execute(IDENTITY_SQL);
            for (int i = 0; i < 32; i++) {
                st.execute(projectionSql(i));
                st.execute(eventSql(i));
            }
            try (ResultSet rs = st.executeQuery(
                    "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE()"
                            + " AND TABLE_NAME LIKE 'customer_profile_%'")) {
                rs.next();
                System.out.println(db + " customer_profile_* tables: " + rs.getInt(1));
            }
        }
    }

    private static String projectionSql(int shard) {
        return "CREATE TABLE IF NOT EXISTS `customer_profile_projection_" + shard + "` ("
                + "`id` BIGINT NOT NULL AUTO_INCREMENT,"
                + "`company_id` BIGINT NOT NULL,"
                + "`channel_type` VARCHAR(8) NOT NULL,"
                + "`profile_key` VARCHAR(128) NOT NULL,"
                + "`customer_id` BIGINT DEFAULT NULL,"
                + "`shard_id` INT NOT NULL,"
                + "`nickname` VARCHAR(128) DEFAULT NULL,"
                + "`lifecycle_stage` VARCHAR(32) DEFAULT 'NEW',"
                + "`value_score` INT DEFAULT 0,"
                + "`total_purchase` DECIMAL(14,2) DEFAULT 0,"
                + "`interaction_count` INT DEFAULT 0,"
                + "`last_active_time` DATETIME DEFAULT NULL,"
                + "`internal_tags` VARCHAR(1024) DEFAULT NULL,"
                + "`current_state` VARCHAR(64) DEFAULT NULL,"
                + "`profile_ext` JSON DEFAULT NULL,"
                + "`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,"
                + "`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,"
                + "PRIMARY KEY (`id`),"
                + "UNIQUE KEY `uk_profile` (`company_id`, `channel_type`, `profile_key`),"
                + "KEY `idx_customer` (`company_id`, `customer_id`),"
                + "KEY `idx_active` (`company_id`, `last_active_time`)"
                + ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    }

    private static String eventSql(int shard) {
        return "CREATE TABLE IF NOT EXISTS `customer_profile_event_" + shard + "` ("
                + "`id` BIGINT NOT NULL AUTO_INCREMENT,"
                + "`company_id` BIGINT NOT NULL,"
                + "`channel_type` VARCHAR(8) NOT NULL,"
                + "`profile_key` VARCHAR(128) NOT NULL,"
                + "`source` VARCHAR(64) NOT NULL,"
                + "`source_version` VARCHAR(128) NOT NULL,"
                + "`event_payload` JSON DEFAULT NULL,"
                + "`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,"
                + "PRIMARY KEY (`id`),"
                + "UNIQUE KEY `uk_event` (`company_id`, `channel_type`, `profile_key`, `source`, `source_version`),"
                + "KEY `idx_profile_time` (`company_id`, `channel_type`, `profile_key`, `create_time`)"
                + ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    }
}