AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.564.verify.sql

-- 1.0.564.verify.sql — 每条 SELECT 必须返回真值。

-- 库位角色表已建出
SELECT COUNT(*) = 1
  FROM information_schema.tables
 WHERE table_schema = DATABASE()
   AND table_name = 'mdp_location_role';

-- 隔离表已补原因列
SELECT COUNT(*) = 1
  FROM information_schema.columns
 WHERE table_schema = DATABASE()
   AND table_name = 'mdp_std_inv_trans_unmapped'
   AND column_name = 'unmapped_reason';

-- 角色取值只允许中立枚举。
-- 1.0.565 把 INSPECT/LINE/SCRAP 改成 MAT_INSPECT/MAT_LINE_LOC/REJECT_SCRAP 等。
-- 表若已被 565 写过，本脚本的 INSERT IGNORE 不会改回旧码，故两套都接受，避免重跑卡启动。
SELECT COUNT(*) = 0
  FROM mdp_location_role
 WHERE location_role NOT IN (
   'INSPECT','MATERIAL','SEMI','LINE','FG','SCRAP','OTHER','UNKNOWN',
   'MAT_INSPECT','MAT_LINE_LOC','FG_INSPECT','FG_LINE_LOC','REJECT_SCRAP','VMI');

-- 待检仓口径到位（无 1000 库位时也算通过）。1.0.565 将 INSPECT 改为 MAT_INSPECT，两者都算到位。
SELECT (SELECT COUNT(*) FROM mdp_location_role WHERE location = '1000') = 0
    OR (SELECT COUNT(*) FROM mdp_location_role WHERE location = '1000' AND location_role NOT IN ('INSPECT','MAT_INSPECT') AND role_source <> 'MANUAL') = 0;

-- trans_type 只允许 12 个中立阶段码或 NULL
SELECT COUNT(*) = 0
  FROM mdp_std_inv_trans
 WHERE trans_type IS NOT NULL
   AND trans_type NOT IN ('MAT_RECEIPT','MAT_IQC_RELEASE','MAT_PUTAWAY','MAT_PICK','MAT_ISSUE','MAT_LINE','FG_RECEIPT','FG_PUTAWAY','FG_PICK','FG_SHIP','FG_PROD_RECEIPT','FG_FQC_RELEASE');

-- 非阶段码不得残留在隔离表
SELECT COUNT(*) = 0
  FROM mdp_std_inv_trans_unmapped
 WHERE src_trans_type_raw IN ('inv-frz','inv-unfrz');

-- 隔离原因只允许三类；1.0.565 追加 OUT_OF_SCOPE_ROLE，重跑时一并接受。
SELECT COUNT(*) = 0
  FROM mdp_std_inv_trans_unmapped
 WHERE unmapped_reason NOT IN ('PENDING_VERIFY','LOCATION_ROLE_UNCONFIGURED','UNKNOWN_CODE','OUT_OF_SCOPE_ROLE');

-- 已映射成功的行不得留在隔离表
SELECT COUNT(*) = 0
  FROM mdp_std_inv_trans_unmapped u
  JOIN mdp_std_inv_trans t
    ON t.tenant_id = u.tenant_id AND t.source_system = u.source_system AND t.src_rec_id = u.src_rec_id
 WHERE t.trans_type IS NOT NULL;