-- Phase 1.5: 为高频表添加 MySQL RANGE 月分区（分区键 create_time）
-- 架构设计文档：docs/architecture/2026-07-06-message-customer-interaction-design.md 第 2.6.2 节
--
-- 生产部署注意：
-- 1. 本迁移对主键/唯一键和外键做了破坏性调整，上线前请先在预发布环境用真实数据量验证。
-- 2. 大表（message_event / send_record）执行 DROP/ADD PRIMARY KEY 可能锁表，建议使用 pt-online-schema-change
--    或预留低峰窗口；当前脚本为开发/MVP 阶段使用。
-- 3. 未提供 down migration：取消分区需要重建表并恢复外键，操作复杂，请在上线前自行备份 schema。

-- ============================
-- 1. mci_message_event（消息事件表）
-- ============================
-- 调整主键/唯一键，确保包含分区键 create_time
ALTER TABLE mci_message_event
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (id, create_time),
    DROP INDEX uk_event_id,
    ADD UNIQUE KEY uk_event_id (event_id, create_time);

-- 改为 RANGE COLUMNS 月分区，并预创建 2026-07 至 2026-12 分区
ALTER TABLE mci_message_event
    PARTITION BY RANGE COLUMNS(create_time) (
        PARTITION p202607 VALUES LESS THAN ('2026-08-01'),
        PARTITION p202608 VALUES LESS THAN ('2026-09-01'),
        PARTITION p202609 VALUES LESS THAN ('2026-10-01'),
        PARTITION p202610 VALUES LESS THAN ('2026-11-01'),
        PARTITION p202611 VALUES LESS THAN ('2026-12-01'),
        PARTITION p202612 VALUES LESS THAN ('2027-01-01'),
        PARTITION pfuture VALUES LESS THAN MAXVALUE
    );

-- ============================
-- 2. mci_send_record（发送记录表）
-- ============================
ALTER TABLE mci_send_record
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (id, create_time),
    DROP INDEX uk_task_target,
    ADD UNIQUE KEY uk_task_target (task_id, conversation_id, person_id, create_time);

ALTER TABLE mci_send_record
    PARTITION BY RANGE COLUMNS(create_time) (
        PARTITION p202607 VALUES LESS THAN ('2026-08-01'),
        PARTITION p202608 VALUES LESS THAN ('2026-09-01'),
        PARTITION p202609 VALUES LESS THAN ('2026-10-01'),
        PARTITION p202610 VALUES LESS THAN ('2026-11-01'),
        PARTITION p202611 VALUES LESS THAN ('2026-12-01'),
        PARTITION p202612 VALUES LESS THAN ('2027-01-01'),
        PARTITION pfuture VALUES LESS THAN MAXVALUE
    );

-- ============================
-- 3. mci_conversation（会话表，可选但推荐）
-- ============================
-- MySQL 不支持外键与分区共存，因此需要先删除与会话表相关的外键约束。
-- 这些外键属于数据库级完整性约束，应用层（MyBatis-Plus）不依赖它们。
ALTER TABLE mci_conversation_participant
    DROP FOREIGN KEY fk_participant_conversation_id;

ALTER TABLE mci_channel_account_conversation
    DROP FOREIGN KEY fk_binding_conversation_id;

ALTER TABLE mci_conversation_tag_binding
    DROP FOREIGN KEY fk_conv_tag_conversation_id;

ALTER TABLE mci_conversation
    DROP FOREIGN KEY fk_conversation_owner_account;

ALTER TABLE mci_conversation
    DROP FOREIGN KEY fk_conversation_subject_id;

-- 调整主键/唯一键，确保包含分区键 create_time
ALTER TABLE mci_conversation
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (id, create_time),
    DROP INDEX uk_tenant_channel_conv,
    ADD UNIQUE KEY uk_tenant_channel_conv (tenant_code, channel_type, channel_conversation_id, deleted, create_time);

ALTER TABLE mci_conversation
    PARTITION BY RANGE COLUMNS(create_time) (
        PARTITION p202607 VALUES LESS THAN ('2026-08-01'),
        PARTITION p202608 VALUES LESS THAN ('2026-09-01'),
        PARTITION p202609 VALUES LESS THAN ('2026-10-01'),
        PARTITION p202610 VALUES LESS THAN ('2026-11-01'),
        PARTITION p202611 VALUES LESS THAN ('2026-12-01'),
        PARTITION p202612 VALUES LESS THAN ('2027-01-01'),
        PARTITION pfuture VALUES LESS THAN MAXVALUE
    );

-- ============================
-- 4. 创建定期扩展未来分区的存储过程
-- ============================
DELIMITER $$

CREATE PROCEDURE ExtendMonthlyPartitions(
    IN p_table_name VARCHAR(64),
    IN p_months_ahead INT
)
BEGIN
    DECLARE v_current DATE;
    DECLARE v_target DATE;
    DECLARE v_partition_name VARCHAR(16);
    DECLARE v_less_than VARCHAR(12);
    DECLARE v_max_partition VARCHAR(64);
    DECLARE v_sql VARCHAR(2000);

    -- 从下个月开始补充分区
    SET v_current = DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-01');
    SET v_target = DATE_FORMAT(CURDATE() + INTERVAL p_months_ahead MONTH, '%Y-%m-01');

    -- 找到当前 MAXVALUE 分区名（通常为 pfuture）
    SELECT PARTITION_NAME INTO v_max_partition
    FROM information_schema.PARTITIONS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table_name
      AND PARTITION_DESCRIPTION = 'MAXVALUE';

    IF v_max_partition IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'No MAXVALUE partition found; table may not be range-partitioned';
    END IF;

    WHILE v_current <= v_target DO
        SET v_partition_name = CONCAT('p', DATE_FORMAT(v_current, '%Y%m'));
        SET v_less_than = DATE_FORMAT(v_current + INTERVAL 1 MONTH, '%Y-%m-%d');

        -- 仅当分区不存在时才添加，保证幂等
        IF NOT EXISTS (
            SELECT 1 FROM information_schema.PARTITIONS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = p_table_name
              AND PARTITION_NAME = v_partition_name
        ) THEN
            SET v_sql = CONCAT(
                'ALTER TABLE ', p_table_name,
                ' REORGANIZE PARTITION ', v_max_partition,
                ' INTO (PARTITION ', v_partition_name, ' VALUES LESS THAN (''', v_less_than, '''), ',
                'PARTITION ', v_max_partition, ' VALUES LESS THAN MAXVALUE)'
            );
            SET @sql = v_sql;
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;

        SET v_current = v_current + INTERVAL 1 MONTH;
    END WHILE;
END$$

DELIMITER ;
