-- 托盘保存品种；仅从有效袋码中唯一且与已有批次兼容的品种回填。
SET @pallet_item_exists = (SELECT COUNT(*) FROM information_schema.columns
 WHERE table_schema=DATABASE() AND table_name='mes_pro_pallet' AND column_name='item_id');
SET @pallet_item_ddl = IF(@pallet_item_exists=0,
 'ALTER TABLE mes_pro_pallet ADD COLUMN item_id BIGINT DEFAULT NULL COMMENT ''托盘品种编号'' AFTER temp_code', 'SELECT 1');
PREPARE pallet_item_stmt FROM @pallet_item_ddl;
EXECUTE pallet_item_stmt;
DEALLOCATE PREPARE pallet_item_stmt;

UPDATE mes_pro_pallet p
JOIN (SELECT pallet_id, MIN(item_id) item_id FROM mes_pro_bag_code
 WHERE deleted=0 AND status<>30 AND pallet_id IS NOT NULL GROUP BY pallet_id
 HAVING COUNT(DISTINCT item_id)=1 AND COUNT(*)=COUNT(item_id)) b ON b.pallet_id=p.id
LEFT JOIN mes_pro_batch ba ON ba.id=p.batch_id AND ba.deleted=0
SET p.item_id=b.item_id
WHERE p.deleted=0 AND p.item_id IS NULL AND (ba.item_id IS NULL OR ba.item_id=b.item_id);

UPDATE mes_pro_batch ba
JOIN (SELECT batch_id,MIN(item_id) item_id FROM mes_pro_bag_code
 WHERE deleted=0 AND status<>30 AND batch_id IS NOT NULL GROUP BY batch_id
 HAVING COUNT(DISTINCT item_id)=1 AND COUNT(*)=COUNT(item_id)) b ON b.batch_id=ba.id
SET ba.item_id=b.item_id
WHERE ba.deleted=0 AND ba.item_id IS NULL
 AND NOT EXISTS (SELECT 1 FROM mes_trace_integrity_record r WHERE r.biz_type='BATCH' AND r.biz_id=ba.id AND r.deleted=0)
 AND NOT EXISTS (SELECT 1 FROM mes_pro_pallet p WHERE p.batch_id=ba.id AND p.deleted=0 AND p.item_id IS NOT NULL AND p.item_id<>b.item_id);