-- =============================================
-- MES 托盘码自动生成（按有效袋码累计阈值）迁移脚本
-- 1. mes_pro_batch 增加包装袋型 bag_type（小袋 10 / 大袋 20）
-- 2. mes_pro_pallet 增加 auto_generated 自动生成标识
-- 3. 增加包装袋型字典
-- MySQL 8.4 适用，不包含 tenant_id
-- =============================================

SET NAMES utf8mb4;

-- ----------------------------
-- 1. 生产批次增加包装袋型
-- ----------------------------
SET @batch_bag_type_exists = (SELECT COUNT(*) FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'mes_pro_batch' AND column_name = 'bag_type');
SET @batch_bag_type_sql = IF(@batch_bag_type_exists = 0,
    'ALTER TABLE mes_pro_batch ADD COLUMN bag_type TINYINT DEFAULT NULL COMMENT ''包装袋型（字典 mes_pro_bag_type：10 小袋 20 大袋），用于自动归集托盘'' AFTER item_id',
    'SELECT 1');
PREPARE batch_bag_type_stmt FROM @batch_bag_type_sql;
EXECUTE batch_bag_type_stmt;
DEALLOCATE PREPARE batch_bag_type_stmt;

-- ----------------------------
-- 2. 托盘码表增加自动生成标识
-- ----------------------------
SET @pallet_auto_generated_exists = (SELECT COUNT(*) FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'mes_pro_pallet' AND column_name = 'auto_generated');
SET @pallet_auto_generated_sql = IF(@pallet_auto_generated_exists = 0,
    'ALTER TABLE mes_pro_pallet ADD COLUMN auto_generated BIT(1) NOT NULL DEFAULT b''0'' COMMENT ''是否自动生成（按有效袋码累计阈值生成，容量固定为阈值）'' AFTER bag_count',
    'SELECT 1');
PREPARE pallet_auto_generated_stmt FROM @pallet_auto_generated_sql;
EXECUTE pallet_auto_generated_stmt;
DEALLOCATE PREPARE pallet_auto_generated_stmt;

-- ----------------------------
-- 3. 包装袋型字典
-- ----------------------------
INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT 'MES 包装袋型', 'mes_pro_bag_type', 0, '托盘自动归集阈值判定', '1', NOW(), '1', NOW(), b'0' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM (SELECT id FROM system_dict_type WHERE `type` = 'mes_pro_bag_type' AND deleted = 0) t);

INSERT INTO `system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
(1065364, 1, '小袋', '10', 'mes_pro_bag_type', 0, 'primary', '', '1', NOW(), '1', NOW(), b'0'),
(1065365, 2, '大袋', '20', 'mes_pro_bag_type', 0, 'warning', '', '1', NOW(), '1', NOW(), b'0');