-- 一袋一码：破损袋码替换迁移
-- 说明：旧袋码保留并标记已作废，新袋码与旧袋码通过替换记录关联。

SET @cancel_time_exists = (
    SELECT COUNT(*) FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'mes_pro_bag_code' AND column_name = 'cancel_time'
);
SET @sql = IF(@cancel_time_exists = 0,
    'ALTER TABLE mes_pro_bag_code ADD COLUMN cancel_time DATETIME DEFAULT NULL COMMENT ''作废时间'' AFTER print_time',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @cancel_reason_exists = (
    SELECT COUNT(*) FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'mes_pro_bag_code' AND column_name = 'cancel_reason'
);
SET @sql = IF(@cancel_reason_exists = 0,
    'ALTER TABLE mes_pro_bag_code ADD COLUMN cancel_reason VARCHAR(255) DEFAULT NULL COMMENT ''作废原因'' AFTER cancel_time',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS `mes_pro_bag_code_replace` (
    `id`              BIGINT       NOT NULL AUTO_INCREMENT COMMENT '编号',
    `replace_uuid`    VARCHAR(32)  NOT NULL COMMENT '替换组 UUID',
    `batch_id`        BIGINT       NOT NULL COMMENT '生产批次编号',
    `batch_code`      VARCHAR(64)  DEFAULT NULL COMMENT '生产批次号',
    `old_bag_code_id` BIGINT       NOT NULL COMMENT '旧袋码编号',
    `old_code`        VARCHAR(64)  NOT NULL COMMENT '旧袋码',
    `old_bag_no`      INT          NOT NULL COMMENT '旧袋序号',
    `old_status`      TINYINT      NOT NULL COMMENT '旧袋码作废前状态',
    `old_pallet_id`   BIGINT       DEFAULT NULL COMMENT '旧袋码原托盘编号',
    `old_pallet_code` VARCHAR(64)  DEFAULT NULL COMMENT '旧袋码原托盘码',
    `new_bag_code_id` BIGINT       NOT NULL COMMENT '新袋码编号',
    `new_code`        VARCHAR(64)  NOT NULL COMMENT '新袋码',
    `new_bag_no`      INT          NOT NULL COMMENT '新袋序号',
    `reason`          VARCHAR(255) NOT NULL COMMENT '替换原因',
    `replace_time`    DATETIME     NOT NULL COMMENT '替换时间',
    `creator`         VARCHAR(64)  DEFAULT '' COMMENT '创建者',
    `create_time`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updater`         VARCHAR(64)  DEFAULT '' COMMENT '更新者',
    `update_time`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted`         BIT(1)       NOT NULL DEFAULT b'0' COMMENT '是否删除',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_old_bag_code_id` (`old_bag_code_id`),
    UNIQUE KEY `uk_new_bag_code_id` (`new_bag_code_id`),
    KEY `idx_replace_uuid` (`replace_uuid`),
    KEY `idx_batch_id` (`batch_id`),
    KEY `idx_old_code` (`old_code`),
    KEY `idx_new_code` (`new_code`)
) ENGINE = InnoDB COMMENT = 'MES 袋码替换记录';

INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT 602629, '袋码破损替换', 'mes:pro-bag-code:replace', 3, 6, 602618, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 602629 OR (`permission` = 'mes:pro-bag-code:replace' AND `deleted` = b'0'));