-- =============================================
-- M4：SN 单品库存轻量闭环
-- 1. mes_wm_sn 增加在库库位维度列（status/batch_id/remark 表中已存在）
-- 2. mes_wm_transaction 增加 sn_code 列
-- 目标库：mom-xgdl（local profile）
-- =============================================

-- 1. mes_wm_sn.warehouse_id（在库仓库 ID）
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'mes_wm_sn' AND column_name = 'warehouse_id');
SET @sql = IF(@exists = 0,
              'ALTER TABLE mes_wm_sn ADD COLUMN warehouse_id BIGINT NULL COMMENT ''在库仓库 ID'' AFTER batch_id',
              'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2. mes_wm_sn.location_id（在库库区 ID）
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'mes_wm_sn' AND column_name = 'location_id');
SET @sql = IF(@exists = 0,
              'ALTER TABLE mes_wm_sn ADD COLUMN location_id BIGINT NULL COMMENT ''在库库区 ID'' AFTER warehouse_id',
              'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. mes_wm_sn.area_id（在库库位 ID）
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'mes_wm_sn' AND column_name = 'area_id');
SET @sql = IF(@exists = 0,
              'ALTER TABLE mes_wm_sn ADD COLUMN area_id BIGINT NULL COMMENT ''在库库位 ID'' AFTER location_id',
              'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 4. mes_wm_transaction.sn_code（SN 码）
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'mes_wm_transaction' AND column_name = 'sn_code');
SET @sql = IF(@exists = 0,
              'ALTER TABLE mes_wm_transaction ADD COLUMN sn_code VARCHAR(100) NULL COMMENT ''SN 码'' AFTER batch_code',
              'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;