AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.408.sql

-- ===========================================================================
-- S0-FULL-DEMO-COVERAGE-CLOSURE-1 / STEP 2B — MFG / WAREHOUSE / MASTER DATA
--
-- 目标租户：UATTEST_CHL
-- 目标页面：20 页（0 行 → 有数据）
-- 行数：110 = COPY 53 + CONSTRUCT 57
--
-- 数据源（只读，绝不写）：
--   @src      AIDOP        797…229   主源
--   @src_crc  AIDOP-Demo   797…230   仅合同评审周期（唯一存在该数据的租户，且只取完整工厂 …002）
--   默认租户 1300000000001 全程不作为 seed 源，不读不写
--
-- 幂等：每条 INSERT 均带 NOT EXISTS 业务键 guard，重跑影响行数为 0
--
-- PK 策略（逐表实证 information_schema.COLUMNS.EXTRA）：
--   18 张表为 auto_increment       → 不给主键
--   ic_substitute / ado_kitting_exclude_rule → 固定 ID 块 3200000000000xxxxx
--   PriorityCode.RecID varchar(36) → 固定 GUID 字面量（schema 要求 GUID 形态）
--
-- 不含 tenant 的 UK（全表级碰撞风险）：
--   uk_ItemPackMaster_domain_itemnum (Domain+ItemNum)
--     → 10 行全部取自实测「目标 Domain 下无占用」的物料集，见 §10
--   IX_LineSkillMaster / IX_LineSkillDetail
--     → 本批不涉及；LineSkill 已整体排除（Site 语义无出处），
--        另立 S0-LINESKILL-TENANT-UK-CLOSURE-1
-- ===========================================================================

SET @src     := (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id = t.OrgId WHERE o.Code = 'AIDOP'       AND t.Status = 1 LIMIT 1);
SET @src_crc := (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id = t.OrgId WHERE o.Code = 'AIDOP-Demo'  AND t.Status = 1 LIMIT 1);
SET @dst     := (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id = t.OrgId WHERE o.Code = 'UATTEST_CHL' AND t.Status = 1 LIMIT 1);
SET @org_dst := (SELECT t.OrgId FROM SysTenant t WHERE t.Id = @dst LIMIT 1);


-- ===========================================================================
-- 01 · PersonSkill  COPY 5   （人员技能）
--     业务键 Code = 101/102/301/501/602
--     rekey：CompanyRefId/FactoryRefId 1000 → 目标租户组织
--     hygiene：Name 去掉 seed 标记 "[DEMO] "，业务名（纵割/工中核心/…）不变
-- ===========================================================================
INSERT INTO PersonSkill (tenant_id, CompanyRefId, FactoryRefId, Code, Name, SkillLevel, Remark, IsEnabled, CreatedAt, UpdatedAt)
SELECT @dst, @org_dst, @org_dst, s.Code,
       TRIM(REPLACE(s.Name, '[DEMO]', '')),
       s.SkillLevel, s.Remark, 1, NOW(), NOW()
FROM PersonSkill s
WHERE s.tenant_id = @src
  AND @src IS NOT NULL AND @dst IS NOT NULL AND @org_dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM PersonSkill x
                  WHERE x.tenant_id = @dst AND x.CompanyRefId = @org_dst
                    AND x.FactoryRefId = @org_dst AND x.Code = s.Code);


-- ===========================================================================
-- 02 · EmpSkills  CONSTRUCT 10   （人员技能维护）
--     5 名 UAT 真实生产员工 × 各 2 项技能（1 主 4 级 + 1 备 3 级），每技能恰好 2 人
--     Site（工作组）留 NULL —— 源值 "[DEMO]数控组" 不适用于 UAT，且不发明
--     JOIN 保证 Employee / SkillNo 双向 FK 闭合
-- ===========================================================================
INSERT INTO EmpSkills (tenant_id, Employee, EmployeeID, EmployeeName, Site, SkillNo, SkillDescription, SkillLevel, Domain, CreateTime, UpdateTime)
SELECT @dst, c.emp, c.emp, e.Name, NULL, c.skill, p.Name, c.lvl, '8010', NOW(), NOW()
FROM (
            SELECT 'RQTJ022' AS emp, '101' AS skill, 4 AS lvl
  UNION ALL SELECT 'RQTJ022', '102', 3
  UNION ALL SELECT 'RQTJ033', '102', 4
  UNION ALL SELECT 'RQTJ033', '301', 3
  UNION ALL SELECT 'RQTJ939', '301', 4
  UNION ALL SELECT 'RQTJ939', '501', 3
  UNION ALL SELECT 'RQTJ940', '501', 4
  UNION ALL SELECT 'RQTJ940', '602', 3
  UNION ALL SELECT 'RQTJ987', '602', 4
  UNION ALL SELECT 'RQTJ987', '101', 3
) c
JOIN EmployeeMaster e ON e.tenant_id = @dst AND e.Employee = c.emp
JOIN PersonSkill    p ON p.tenant_id = @dst AND p.Code     = c.skill
WHERE @dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM EmpSkills x
                  WHERE x.tenant_id = @dst AND x.Domain = '8010'
                    AND x.Employee = c.emp AND x.SkillNo = c.skill);


-- ===========================================================================
-- 03 · ImageType  COPY 4   （SOP 文件类型）
--     ★ DEMO-hygiene：image_type_id 是页面「文件类型编码」列直接展示给用户的业务码
--       （SopFileTypeList.vue:15），故 rekey DEMO-SOP-* → UATD-SOP-*
--       实测 UATD-SOP-* 全库碰撞 = 0
--     ★ 源 domain 存的是 797 组织 Id（字段被误用），rekey 为 '8010'
-- ===========================================================================
INSERT INTO ImageType (tenant_id, image_type_id, descr, domain)
SELECT @dst,
       REPLACE(s.image_type_id, 'DEMO-', 'UATD-'),
       TRIM(REPLACE(s.descr, '[DEMO]', '')),
       '8010'
FROM ImageType s
WHERE s.tenant_id = @src
  AND @src IS NOT NULL AND @dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM ImageType x
                  WHERE x.tenant_id = @dst AND x.domain = '8010'
                    AND x.image_type_id = REPLACE(s.image_type_id, 'DEMO-', 'UATD-'));


-- ===========================================================================
-- 04 · QualitySegmentImage  CONSTRUCT 3   （SOP 维护）
--     ★ image_type_id 是 varchar 业务码，按【目标】业务码回绑，
--       绝不复制源码值；JOIN ImageType 强制目标侧存在
--     item/op 均取自 UAT 真实 Routing
-- ===========================================================================
INSERT INTO QualitySegmentImage (tenant_id, line, item_num, op, image_type_id, descr, rev, sort_id, domain)
SELECT @dst, c.line, c.item, c.op, it.image_type_id, c.descr, 'A.0', c.sort, c.dom
FROM (
            SELECT 'M01'  AS line, '91HC0497'  AS item, '101' AS op, 'UATD-SOP-WI' AS itype, '作业指导书-底板组件安装脚垫' AS descr, 1 AS sort, '8010' AS dom
  UNION ALL SELECT 'DD01',        '91HC0497',         '704',        'UATD-SOP-EQ',          '设备操作规程-主机包装',              2,        '8010'
  UNION ALL SELECT NULL,          '3221C0031',        '50',         'UATD-SOP-QC',          '检验规范-声波清洗',                  3,        '2410'
) c
JOIN ImageType  it ON it.tenant_id = @dst AND it.image_type_id = c.itype
JOIN ItemMaster i  ON i.tenant_id  = @dst AND i.ItemNum        = c.item
WHERE @dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM QualitySegmentImage x
                  WHERE x.tenant_id = @dst AND x.domain = c.dom
                    AND x.item_num = c.item AND x.op = c.op
                    AND x.image_type_id = c.itype);


-- ===========================================================================
-- 05 · PreprocessElementParam  COPY 5   （前处理要素参数）
--     无外部 FK；device_number / message_code 为业务键
--     ★ DEMO-hygiene：device_number(要素编码) 与 message_code(参数编码) 均为
--       PreprocessElementParamList.vue:15/17 直接展示给用户的业务码，
--       故一并 rekey DEMO-* → UATD-*（源侧字面量仅出现在 SELECT 表达式，
--       不会成为目标投影值）：
--         DEMO-PRE-ELEM-01/02/03  → UATD-PRE-ELEM-01/02/03
--         DEMO-PRE-TEMP/TIME/DRYT/PRESS/SPEED → UATD-PRE-TEMP/TIME/DRYT/PRESS/SPEED
--       实测 UATD-* 全表碰撞 = 0；两列均无跨表 code 引用（controller 只做关键字检索）
--     descr 去 seed 标记
-- ===========================================================================
INSERT INTO PreprocessElementParam (tenant_id, device_number, device_descr, message_code, code_descr, value_type, std_value, top_limit, low_limit, ufld1, um, domain, create_time, update_time)
SELECT @dst,
       REPLACE(s.device_number, 'DEMO-', 'UATD-'),
       TRIM(REPLACE(s.device_descr, '[DEMO]', '')),
       REPLACE(s.message_code, 'DEMO-', 'UATD-'),
       TRIM(REPLACE(s.code_descr, '[DEMO]', '')),
       s.value_type, s.std_value, s.top_limit, s.low_limit, s.ufld1, s.um,
       '8010', NOW(), NOW()
FROM PreprocessElementParam s
WHERE s.tenant_id = @src
  AND @src IS NOT NULL AND @dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM PreprocessElementParam x
                  WHERE x.tenant_id = @dst AND x.domain = '8010'
                    AND x.device_number = REPLACE(s.device_number, 'DEMO-', 'UATD-')
                    AND x.message_code  = REPLACE(s.message_code,  'DEMO-', 'UATD-'));


-- ===========================================================================
-- 06 · PreprocessElement  CONSTRUCT 4   （前处理要素）
--     ★ 已修正：源候选 91HC0497@Op101 = "底板组件安装脚垫"，与「前处理/清洗」矛盾，已剔除
--       改用 UAT Routing 中真实的清洗类工序：
--         3221C0031@50 声波清洗 / 3124C0015@40 光清洗 / 322AD001@30 洗吹干 / 312CD008@60 清洗
--       四者 ItemMaster.Domain 与 Routing.Domain 同为 2410，无域歧义
--     line 留 NULL —— UAT 20 条产线均为 8010 装配/包装线，与这些 2410 机加工序不匹配，不发明
-- ===========================================================================
INSERT INTO PreprocessElement (tenant_id, item_num, op, line, typed, item_code, descr, is_main, domain, create_time, update_time)
SELECT @dst, c.item, c.op, NULL, c.typed, c.code, c.descr, 1, '2410', NOW(), NOW()
FROM (
            SELECT '3221C0031' AS item, '50' AS op, 'CLEAN' AS typed, 'UATD-PRE-01' AS code, '前处理-声波清洗' AS descr
  UNION ALL SELECT '3124C0015',        '40',        'CLEAN',          'UATD-PRE-02',          '前处理-光清洗'
  UNION ALL SELECT '322AD001',         '30',        'CLEAN',          'UATD-PRE-03',          '前处理-洗吹干'
  UNION ALL SELECT '312CD008',         '60',        'CLEAN',          'UATD-PRE-04',          '前处理-清洗'
) c
JOIN ItemMaster i ON i.tenant_id = @dst AND i.ItemNum = c.item
WHERE @dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM PreprocessElement x
                  WHERE x.tenant_id = @dst AND x.domain = '2410'
                    AND x.item_num = c.item AND x.is_main = 1);


-- ===========================================================================
-- 07 · DevMonitor  COPY 5   （生产要素参数）
--     ★ 页面 element-param-production 实际读的是 DevMonitor（非 ElementParam 孤儿表）
--     无外部 FK
--     ★ DEMO-hygiene：DeviceNumber(要素编码) 与 MessageCode(参数编码) 均为
--       ProductionElementParamList.vue:15/17 直接展示给用户的业务码，故一并 rekey：
--         DEMO-ELEM-01/02/03 → UATD-ELEM-01/02/03
--         DEMO-TEMP/PRESS/SPEED/CURR/HUMI → UATD-TEMP/PRESS/SPEED/CURR/HUMI
--       实测 UATD-* 全表碰撞 = 0；两列均无跨表 code 引用
-- ===========================================================================
INSERT INTO DevMonitor (tenant_id, DeviceNumber, DeviceDescr, MessageCode, CodeDescr, ValueType, StdValue, TopLimit, LowLimit, Ufld1, UM, CodeType, Domain, CreateTime, UpdateTime)
SELECT @dst,
       REPLACE(s.DeviceNumber, 'DEMO-', 'UATD-'),
       TRIM(REPLACE(s.DeviceDescr, '[DEMO]', '')),
       REPLACE(s.MessageCode, 'DEMO-', 'UATD-'),
       TRIM(REPLACE(s.CodeDescr, '[DEMO]', '')),
       s.ValueType, s.StdValue, s.TopLimit, s.LowLimit, s.Ufld1, s.UM,
       s.CodeType, '8010', NOW(), NOW()
FROM DevMonitor s
WHERE s.tenant_id = @src
  AND @src IS NOT NULL AND @dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM DevMonitor x
                  WHERE x.tenant_id = @dst AND x.Domain = '8010'
                    AND x.DeviceNumber = REPLACE(s.DeviceNumber, 'DEMO-', 'UATD-')
                    AND x.MessageCode  = REPLACE(s.MessageCode,  'DEMO-', 'UATD-')
                    AND x.CodeType = s.CodeType);


-- ===========================================================================
-- 08 · ProdLineDetail  CONSTRUCT 6   （生产线物料）
--     ★ 上一轮 12 行已作废：那 4 个物料的工序是冲压/数控/机加工，
--       而 UAT 20 条产线全是组装/包装/热封线，属不同生产域，语义不成立
--     改用 UAT 唯一整机装配件 91HC0497（36 道真实装配工序，Item/Routing 同为 Domain 8010）
--     仅保留 HIGH/MEDIUM 可解释行；601 拷机→能量线 等 LOW 行已删除
-- ===========================================================================
INSERT INTO ProdLineDetail (tenant_id, Part, Op, Site, Line, Rate, SetupTime, OverlapTime, Sequence, StandardStaffCount, IsActive, IsConfirm, Domain, CreateTime, UpdateTime)
SELECT @dst, c.part, c.op, NULL, c.line, NULL, NULL, 0, c.seq, 1, 1, 0, '8010', NOW(), NOW()
FROM (
            SELECT '91HC0497' AS part, 101 AS op, 'M01'  AS line, 1 AS seq
  UNION ALL SELECT '91HC0497',        201,        'P01',         2
  UNION ALL SELECT '91HC0497',        301,        'BD01',        3
  UNION ALL SELECT '91HC0497',        401,        'BN01',        4
  UNION ALL SELECT '91HC0497',        501,        'BD02',        5
  UNION ALL SELECT '91HC0497',        704,        'DD01',        6
) c
JOIN ItemMaster i ON i.tenant_id = @dst AND i.ItemNum = c.part
JOIN LineMaster l ON l.tenant_id = @dst AND l.Line    = c.line
WHERE @dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM ProdLineDetail x
                  WHERE x.tenant_id = @dst AND x.Domain = '8010'
                    AND x.Part = c.part AND x.Op = c.op AND x.Line = c.line);


-- ===========================================================================
-- 09 · ItemOpCondition  CONSTRUCT 5   （物料工艺要素）
--     ★ 页面 material-process-element 实际读 ItemOpCondition（非 MaterialProcessElement 孤儿表）
--     UK = tenant+Domain+ItemNum+IsMain+CodeType → 每物料一行
--     Op 全部取自各物料真实 Routing；仅 91HC0497 有可解释产线，其余 Line 留 NULL 不发明
-- ===========================================================================
INSERT INTO ItemOpCondition (tenant_id, ItemNum, Op, Line, Typed, ItemCode, Descr, IsMain, CodeType, Domain, CreateTime, UpdateTime)
SELECT @dst, c.item, c.op, c.line, c.typed, c.code, c.descr, 1, 'Prod', c.dom, NOW(), NOW()
FROM (
            SELECT '91HC0497'  AS item, '101' AS op, 'M01' AS line, 'ASSY'  AS typed, 'UATD-OPC-01' AS code, '底板组件安装脚垫工序生产要素' AS descr, '8010' AS dom
  UNION ALL SELECT '3124C0015',        '10',        NULL,          'PRESS', 'UATD-OPC-02',          '压铆苞工序生产要素',                   '2410'
  UNION ALL SELECT '3123C0013',        '10',        NULL,          'MACH',  'UATD-OPC-03',          '胀管工序生产要素',                     '2410'
  UNION ALL SELECT '3221C0031',        '10',        NULL,          'PRESS', 'UATD-OPC-04',          '落片工序生产要素',                     '2410'
  UNION ALL SELECT '3521C0015',        '10',        NULL,          'GRIND', 'UATD-OPC-05',          '磨六面工序生产要素',                   '2410'
) c
JOIN ItemMaster i ON i.tenant_id = @dst AND i.ItemNum = c.item
WHERE @dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM ItemOpCondition x
                  WHERE x.tenant_id = @dst AND x.Domain = c.dom
                    AND x.ItemNum = c.item AND x.IsMain = 1 AND x.CodeType = 'Prod');


-- ===========================================================================
-- 10 · ItemPackMaster  CONSTRUCT 10   （零件包装规格）
--     ★ Provenance = UAT_DEMO_SEED，不是真实包装规格：
--       AIDOPB 574 行虽与 UAT 交集 17，但 PackingQty=999999999、净重/长宽高全 0、
--       类型全 NULL，属占位垃圾；AIDOP 797 交集仅 1 行且同样为占位值 → 拒绝 COPY
--     包装数量依 ItemType 类别单调分级（RS11 耗材 > RS12 零件 > RS13 附件 > RS30 整机）
--     ★ uk_ItemPackMaster_domain_itemnum 不含 tenant：
--       10 个物料全部取自实测「Domain 8010 下无占用」的 16 个物料集
-- ===========================================================================
INSERT INTO ItemPackMaster (tenant_id, company_ref_id, factory_ref_id, Domain, ItemNum, PackingType, PackingQty, WeightUM, DimUM, IsActive, IsConfirm, Descr, CreateTime, UpdateTime)
SELECT @dst, @org_dst, @org_dst, '8010', c.item, c.ptype, c.qty, 'KG', 'MM', 1, 0, c.descr, NOW(), NOW()
FROM (
            SELECT '91C0D2C'  AS item, '中包装' AS ptype,   50 AS qty, '管组件中包装'   AS descr
  UNION ALL SELECT '91CC0231',        '中包装',            50,        '管组件中包装'
  UNION ALL SELECT '1A03B22',         '中包装',           100,        '零件中包装'
  UNION ALL SELECT '1A0C885',         '小包装',           200,        '小零件包装'
  UNION ALL SELECT '1AB9275',         '小包装',           200,        '小零件包装'
  UNION ALL SELECT '91A0DC9',         '中包装',           100,        '零件中包装'
  UNION ALL SELECT '81HC0497',        '整机包装',          10,        '主机小批包装'
  UNION ALL SELECT '81HC0568',        '附件包装',          20,        '附件包装'
  UNION ALL SELECT '91FC0295',        '整机包装',           5,        '整机最小包装'
  UNION ALL SELECT 'A2C3115',         '耗材包装',        1000,        '标签耗材大批量包装'
) c
JOIN ItemMaster i ON i.tenant_id = @dst AND i.ItemNum = c.item
WHERE @dst IS NOT NULL AND @org_dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM ItemPackMaster x
                  WHERE x.Domain = '8010' AND x.ItemNum = c.item);


-- ===========================================================================
-- 11 · ItemSubstituteDetail  CONSTRUCT 6   （物料替代）
--     源 797 的 10 行三个 Item 列与 UAT 交集全部为 0 → 不可 COPY
--     6 条全部由 UAT 真实 BOM（9 个父件）推导，替代双方均为 UAT 已存在物料
--     无自替代 / 无 A↔B 反向对 / 无循环
-- ===========================================================================
INSERT INTO ItemSubstituteDetail (tenant_id, ParentItem, ItemNum, SubstituteItem, SubstituteQty, SubstituteType, Remark, Domain, CreateTime, UpdateTime)
SELECT @dst, c.parent, c.orig, c.sub, c.qty, c.stype, c.remark, c.dom, NOW(), NOW()
FROM (
            SELECT '91C0D2C'  AS parent, '3123C0013' AS orig, '312CD0A'   AS sub, 1.000000 AS qty, '替代' AS stype, '同属91C0D2C BOM，描述同为「工（2305H原材料改善后）」，等量替代' AS remark, '8010' AS dom
  UNION ALL SELECT '91CC0231',          '3123C0014',        '3223C0011',        1.000000,        '替代',          '同属91CC0231 BOM，1405H同规格互换',                              '8010'
  UNION ALL SELECT '3124C0015',         '3152C0C7',         '3152C0C8',         1.000000,        '替代',          '同属3124C0015 BOM，4*B0.1*5*D6*50L-4F ↔ 3*B0.1*4*D6*50L-4F 同系列', '2410'
  UNION ALL SELECT '3124C0015',         '3152B0273',        '3152B0274',        1.000000,        '替代',          '同属3124C0015 BOM，3.0*4D*50L-4T ↔ 2.0*4D*50L-4T 同系列降规格',    '2410'
  UNION ALL SELECT '3124C0015',         '3521C0015',        '3521C0016',        1.000000,        '替代',          '同属3124C0015 BOM，60/45 两款共用件互换',                          '2410'
  UNION ALL SELECT '91C0D2C',           '312CD008',         '3145C0047',        1.000000,        '取代',          '同属91C0D2C BOM，F3605H-M ↔ EF3605P 同位取代',                    '8010'
) c
JOIN ItemMaster p ON p.tenant_id = @dst AND p.ItemNum = c.parent
JOIN ItemMaster o ON o.tenant_id = @dst AND o.ItemNum = c.orig
JOIN ItemMaster b ON b.tenant_id = @dst AND b.ItemNum = c.sub
WHERE @dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM ItemSubstituteDetail x
                  WHERE x.tenant_id = @dst AND x.Domain = c.dom
                    AND x.ParentItem = c.parent AND x.ItemNum = c.orig
                    AND x.SubstituteItem = c.sub);


-- ===========================================================================
-- 12 · ic_substitute  CONSTRUCT 5   （选择替代方案）
--     源 797 的 796 行 substitute_code 为 1003779A 类不可读码、strategy/mode 全 0，
--     无 demo 价值且与 UAT 物料零交集 → 不 COPY
--     构造 5 个方案头 = §11 六条替代关系去重后的原物料
--     ★ 该表 PK 非 auto_increment → 固定 ID 块 3200000000000012001..12005
-- ===========================================================================
INSERT INTO ic_substitute (Id, tenant_id, substitute_code, substitute_strategy, substitute_mode, substitute_type, company_id, factory_id, IsDeleted, org_id, create_time, update_time)
SELECT c.new_id, @dst, c.code, 0, 0, 0, @org_dst, @org_dst, 0, @org_dst, NOW(), NOW()
FROM (
            SELECT 3200000000000012001 AS new_id, '3123C0013' AS code
  UNION ALL SELECT 3200000000000012002,           '3123C0014'
  UNION ALL SELECT 3200000000000012003,           '3152C0C7'
  UNION ALL SELECT 3200000000000012004,           '3152B0273'
  UNION ALL SELECT 3200000000000012005,           '3521C0015'
) c
JOIN ItemMaster i ON i.tenant_id = @dst AND i.ItemNum = c.code
WHERE @dst IS NOT NULL AND @org_dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM ic_substitute x
                  WHERE x.tenant_id = @dst AND x.substitute_code = c.code
                    AND x.substitute_strategy = 0 AND x.substitute_mode = 0);


-- ===========================================================================
-- 13 · S0ProcessFlowCard  CONSTRUCT 4   （工序流转卡）
--     源 4 行 item_num 与 UAT 交集为 0 → 不可 COPY
--     routing_code 取物料号（实测该表 RoutingCode 即 MaterialCode）
-- ===========================================================================
INSERT INTO S0ProcessFlowCard (tenant_id, company_ref_id, factory_ref_id, domain_code, work_order_no, work_order_batch_no, flow_card_no, nature, item_num, item_name, routing_code, is_active, create_time, update_time)
SELECT @dst, @org_dst, @org_dst, i.Domain, c.wo, '01', c.fc, '正常', c.item, i.Descr, c.item, 1, NOW(), NOW()
FROM (
            SELECT 'FC-UATD-0001' AS fc, 'WO-UATD-0001' AS wo, '91HC0497'  AS item
  UNION ALL SELECT 'FC-UATD-0002',        'WO-UATD-0002',        '91C0D2C'
  UNION ALL SELECT 'FC-UATD-0003',        'WO-UATD-0003',        '91CC0231'
  UNION ALL SELECT 'FC-UATD-0004',        'WO-UATD-0004',        '3124C0015'
) c
JOIN ItemMaster i ON i.tenant_id = @dst AND i.ItemNum = c.item
WHERE @dst IS NOT NULL AND @org_dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM S0ProcessFlowCard x
                  WHERE x.tenant_id = @dst AND x.factory_ref_id = @org_dst
                    AND x.flow_card_no = c.fc);


-- ===========================================================================
-- 14 · wms_rwzp  CONSTRUCT 4   （物料状态任务指派）
--     ★ 源 4 行的库位 yskw/mdkw(1000–1005) 实测在 UAT 100% 闭合 → 库位沿用源真实值
--       但 wlbm（物料）与 sqr（申请人）在 UAT 不存在 → 换 UAT 真实物料与真实生产员工
--     库位流向业务合理：待检→合格 / 合格→材料 / 材料→辅料 / 不合格→料不良品
-- ===========================================================================
INSERT INTO wms_rwzp (tenant_id, company_ref_id, factory_ref_id, domain_code, tcrq, sqr, rwlx, wlbm, sl, rqpc, yskw, mdkw, xqsj, zt, create_time, update_time)
SELECT @dst, @org_dst, @org_dst, '8010', NOW(), e.Name, c.rwlx, c.wlbm, c.sl, c.rqpc, c.ys, c.md, NOW(), c.zt, NOW(), NOW()
FROM (
            SELECT '移库' AS rwlx, '91HC0497'  AS wlbm, 10 AS sl, 'UATD-20260828-01' AS rqpc, '1000' AS ys, '1001' AS md, '待处理' AS zt, 'RQTJ022' AS emp
  UNION ALL SELECT '上架',        '91C0D2C',          50,       'UATD-20260828-02',          '1001',          '1003',          '处理中',          'RQTJ033'
  UNION ALL SELECT '领料',        '91CC0231',         30,       'UATD-20260828-03',          '1003',          '1005',          '已完成',          'RQTJ939'
  UNION ALL SELECT '移库',        '1A0C885',          20,       'UATD-20260828-04',          '1002',          '1004',          '待处理',          'RQTJ940'
) c
JOIN ItemMaster      i  ON i.tenant_id  = @dst AND i.ItemNum  = c.wlbm
JOIN EmployeeMaster  e  ON e.tenant_id  = @dst AND e.Employee = c.emp
JOIN LocationMaster  ls ON ls.tenant_id = @dst AND ls.location = c.ys
JOIN LocationMaster  ld ON ld.tenant_id = @dst AND ld.location = c.md
WHERE @dst IS NOT NULL AND @org_dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM wms_rwzp x
                  WHERE x.tenant_id = @dst AND x.company_ref_id = @org_dst
                    AND x.factory_ref_id = @org_dst AND x.domain_code = '8010'
                    AND x.rwlx = c.rwlx AND x.wlbm = c.wlbm AND x.rqpc = c.rqpc
                    AND x.yskw = c.ys AND x.mdkw = c.md);


-- ===========================================================================
-- 15 · PriorityCode  COPY 4   （订单优先级配置）
--     真实配置：客户优先级/工单加急/工单优先级/工单完工日期
--     ★ RecID 为 varchar(36) GUID 且非 auto_increment → 固定 GUID 字面量（可预先稳定确定）
-- ===========================================================================
INSERT INTO PriorityCode (RecID, tenant_id, Descr, Value, Priority, SourceTable, SourceColumn, OrderBy, IsActive, Domain, CreateTime, UpdateTime)
SELECT c.new_id, @dst, s.Descr, s.Value, s.Priority, s.SourceTable, s.SourceColumn, s.OrderBy, 1, '8010', NOW(), NOW()
FROM PriorityCode s
JOIN (
            SELECT '客户优先级'   AS d, 'A0000000-0000-4000-8000-000000002B01' AS new_id
  UNION ALL SELECT '工单加急',           'A0000000-0000-4000-8000-000000002B02'
  UNION ALL SELECT '工单优先级',         'A0000000-0000-4000-8000-000000002B03'
  UNION ALL SELECT '工单完工日期',       'A0000000-0000-4000-8000-000000002B04'
) c ON c.d = s.Descr
WHERE s.tenant_id = @src
  AND @src IS NOT NULL AND @dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM PriorityCode x
                  WHERE x.tenant_id = @dst AND x.Descr = s.Descr);


-- ===========================================================================
-- 16 · BarCodeType  COPY 6   （标签格式）
--     无外部 FK；(domain_code, bar_type) 为业务键
--     必须先于 BarCodeNbr（条码规则的 Type 引用这里）
-- ===========================================================================
INSERT INTO BarCodeType (tenant_id, company_ref_id, factory_ref_id, domain_code, bar_type, class, input_string, create_time, update_time)
SELECT @dst, @org_dst, @org_dst, s.domain_code, s.bar_type, s.class, s.input_string, NOW(), NOW()
FROM BarCodeType s
WHERE s.tenant_id = @src
  AND @src IS NOT NULL AND @dst IS NOT NULL AND @org_dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM BarCodeType x
                  WHERE x.tenant_id = @dst AND x.domain_code = s.domain_code
                    AND x.bar_type = s.bar_type);


-- ===========================================================================
-- 17 · BarCodeNbr  COPY 2   （条码规则）
--     ★ 源 3 行中 Customer=10000001 在 UAT 不存在；
--       把条码构成规则改挂到其它客户等于断言两家客户条码格式相同 —— 无依据 → DROP
--     仅 COPY 客户 10000000(瑞贝德) / 10000002(通广) 两条，FK 由 JOIN CustMaster 强制
--     FirmString*Note 去 seed 标记
-- ===========================================================================
INSERT INTO BarCodeNbr (tenant_id, company_ref_id, factory_ref_id, Domain, Type, Customer, WaterRules, WaterLen, `Separator`, FirmLength, FirmString1, FirmStringLen1, FirmString1Note, FirmString2, FirmStringLen2, FirmString2Note, ItemNum, CreateTime, UpdateTime)
SELECT @dst, @org_dst, @org_dst, s.Domain, s.Type, s.Customer, s.WaterRules, s.WaterLen, s.`Separator`, s.FirmLength,
       s.FirmString1, s.FirmStringLen1, TRIM(REPLACE(IFNULL(s.FirmString1Note, ''), '[DEMO]', '')),
       s.FirmString2, s.FirmStringLen2, TRIM(REPLACE(IFNULL(s.FirmString2Note, ''), '[DEMO]', '')),
       s.ItemNum, NOW(), NOW()
FROM BarCodeNbr s
JOIN CustMaster  cu ON cu.tenant_id = @dst AND cu.Cust    = s.Customer
JOIN BarCodeType bt ON bt.tenant_id = @dst AND bt.bar_type = s.Type AND bt.domain_code = s.Domain
WHERE s.tenant_id = @src
  AND @src IS NOT NULL AND @dst IS NOT NULL AND @org_dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM BarCodeNbr x
                  WHERE x.tenant_id = @dst AND x.Domain = s.Domain
                    AND x.Customer = s.Customer AND x.Type = s.Type);


-- ===========================================================================
-- 18 · WorkOrdControl  COPY 1   （工单控制参数）
--     ★ 页面 work-order-control 实际读 WorkOrdControl（非 WorkOrderControl 孤儿模型）
--     单例配置（UK = tenant_id + Domain）；全部参数取源真实值，不发明
--     Domain 保持源真实兼容语义（源为空串，DTO 明确其已降级为历史兼容字段）
-- ===========================================================================
INSERT INTO WorkOrdControl (tenant_id, EnteringPer, PaintingPer, IssuedInterval, PostVarsatSFC, IsIssued, IsPrintRejectBarcode, IsPrintScrapBarcode, IsQualityConfirm, Domain, IsActive, IsConfirm, CreateTime, UpdateTime)
SELECT @dst, s.EnteringPer, s.PaintingPer, s.IssuedInterval, s.PostVarsatSFC, s.IsIssued,
       s.IsPrintRejectBarcode, s.IsPrintScrapBarcode, s.IsQualityConfirm,
       s.Domain, s.IsActive, s.IsConfirm, NOW(), NOW()
FROM WorkOrdControl s
WHERE s.tenant_id = @src
  AND @src IS NOT NULL AND @dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM WorkOrdControl x
                  WHERE x.tenant_id = @dst AND x.Domain = s.Domain);


-- ===========================================================================
-- 19 · ado_kitting_exclude_rule  COPY 1   （齐套检查排除规则）
--     排除物料类型 ["RS11"] —— UAT 存在 RS11 物料（A2C3115），配置有效
--     ★ 该表 PK 非 auto_increment → 固定 ID 3200000000000019001
--     该表无业务 UK → 用逻辑幂等键 (tenant_id, excluded_item_types, excluded_owner_apps)
-- ===========================================================================
INSERT INTO ado_kitting_exclude_rule (id, tenant_id, excluded_item_types, excluded_owner_apps, is_active, remark, create_time, update_time)
SELECT 3200000000000019001, @dst, s.excluded_item_types, s.excluded_owner_apps, s.is_active, s.remark, NOW(), NOW()
FROM ado_kitting_exclude_rule s
WHERE s.tenant_id = @src
  AND @src IS NOT NULL AND @dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM ado_kitting_exclude_rule x
                  WHERE x.tenant_id = @dst
                    AND x.excluded_item_types <=> s.excluded_item_types
                    AND x.excluded_owner_apps <=> s.excluded_owner_apps);


-- ===========================================================================
-- 20 · S0ContractReviewCycle  COPY 5   （合同评审周期 · 主表）
--     ★ 唯一数据源在兄弟租户 AIDOP-Demo(797…230)，且为【双工厂重复】：
--         factory 1329900200002 = 5 main + 15 breakdown（完整）
--         factory 1329900200003 = 5 main +  3 breakdown（残缺）
--       UK = tenant+factory_ref_id+stage_code，两工厂 10 行 rekey 到同一目标工厂必然冲突
--       → 只取完整的 …002 一套
-- ===========================================================================
INSERT INTO S0ContractReviewCycle (tenant_id, company_ref_id, factory_ref_id, domain_code, stage_code, stage_name, std_hours, order_no, is_active, create_time, update_time)
SELECT @dst, @org_dst, @org_dst, '8010', s.stage_code, s.stage_name, s.std_hours, s.order_no, 1, NOW(), NOW()
FROM S0ContractReviewCycle s
WHERE s.tenant_id = @src_crc AND s.factory_ref_id = 1329900200002
  AND @src_crc IS NOT NULL AND @dst IS NOT NULL AND @org_dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM S0ContractReviewCycle x
                  WHERE x.tenant_id = @dst AND x.factory_ref_id = @org_dst
                    AND x.stage_code = s.stage_code);


-- ===========================================================================
-- 21 · S0ContractReviewCycleBreakdown  COPY 15   （合同评审周期 · 分解从表）
--     主从链完整：15 条 parent_stage_code 全部命中上一条插入的 5 条主表阶段
--     实证一致性：分解工时按阶段求和 = 8/12/8/10/2，与主表 std_hours 精确相等
--     JOIN 主表强制父子闭合，杜绝「只补 main 不补 breakdown」或父键悬空
-- ===========================================================================
INSERT INTO S0ContractReviewCycleBreakdown (tenant_id, company_ref_id, factory_ref_id, domain_code, parent_stage_code, group_code, group_name, std_hours, order_no, is_active, create_time, update_time)
SELECT @dst, @org_dst, @org_dst, '8010', s.parent_stage_code, s.group_code, s.group_name, s.std_hours, s.order_no, 1, NOW(), NOW()
FROM S0ContractReviewCycleBreakdown s
JOIN S0ContractReviewCycle m
  ON m.tenant_id = @dst AND m.factory_ref_id = @org_dst AND m.stage_code = s.parent_stage_code
WHERE s.tenant_id = @src_crc AND s.factory_ref_id = 1329900200002
  AND @src_crc IS NOT NULL AND @dst IS NOT NULL AND @org_dst IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM S0ContractReviewCycleBreakdown x
                  WHERE x.tenant_id = @dst AND x.factory_ref_id = @org_dst
                    AND x.parent_stage_code = s.parent_stage_code
                    AND x.group_code = s.group_code);