-- =====================================================================================
-- WIP-C3B  ·  S0 · C3-B 物料桥：ProductStructureMaster / ItemSubstituteDetail
--   （占位名，提交那刻 rename 为正式 1.0.<next>.sql）
--
--   ProductStructureMaster → mdp_stg_s0_item_bom        → bridge_item_bom
--   ItemSubstituteDetail   → mdp_stg_s0_item_substitute → bridge_item_substitute
--
-- 🔴🔴 legacy 代理键跨租户实证（2026-09-08）：
--    ComponentMaterialId → ItemMaster.RecID  同租户失败 170 / 忽略租户失败 0
--    ParentMaterialId    → ItemMaster.RecID  同租户失败 2060 / 忽略租户失败 0
--    那 170 行恰恰全部挂到**别的租户**的 RecID 上 —— 去掉租户条件不报错，只会静默串租户。
--    同租户可解析的 61866 行里，代理键解出的码与 ComponentItem 61866/61866 完全一致
--    ⇒ 自然键完全可靠。本批一律走自然键，两个 id 仅作技术属性留痕。
-- =====================================================================================

CREATE TABLE IF NOT EXISTS `mdp_stg_s0_item_bom` (
  `id`              bigint       NOT NULL AUTO_INCREMENT,
  `tenant_id`       bigint       NOT NULL DEFAULT 0,
  `factory_id`      bigint       DEFAULT NULL,
  `source_system`   varchar(50)  NOT NULL DEFAULT 'AIDOPDEV_MYSQL',
  `source_table`    varchar(100) NOT NULL,
  `source_row_id`   varchar(100) NOT NULL,
  `source_biz_key`  varchar(300) DEFAULT NULL,
  `sync_batch_id`   varchar(100) NOT NULL,
  `sync_time`       datetime     NOT NULL,
  `process_status`  varchar(20)  NOT NULL DEFAULT 'PENDING',
  `raw_data`        json         NOT NULL,
  `process_message` varchar(500) DEFAULT NULL,
  `update_time`     datetime     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_time`     datetime     DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stg_s0_item_bom` (`tenant_id`,`source_system`,`source_table`,`source_row_id`),
  KEY `idx_stg_s0_item_bom_biz` (`source_biz_key`),
  KEY `idx_stg_s0_item_bom_batch` (`tenant_id`,`sync_batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S0 贴源：BOM 结构（源 ProductStructureMaster）';

CREATE TABLE IF NOT EXISTS `bridge_item_bom` (
  `id`                        bigint        NOT NULL AUTO_INCREMENT,
  `tenant_id`                 bigint        NOT NULL,
  `item_code`                 varchar(50)   NOT NULL COMMENT '业务键 + 父键 ← $.ParentItem（BOM 父件）；父 = dim_item.item_code（实测孤儿 0/62036）',
  `component_item_code`       varchar(50)   NOT NULL COMMENT '业务键 ← $.ComponentItem（子件）；对 dim_item 同样零孤儿，但框架单父故不设父',
  `qty`                       decimal(18,5) DEFAULT NULL COMMENT '← $.Qty',
  `unit`                      varchar(50)   DEFAULT NULL COMMENT '← $.UM',
  `scrap`                     decimal(18,5) DEFAULT NULL COMMENT '← $.Scrap',
  `qty_consumed`              decimal(18,5) DEFAULT NULL COMMENT '← $.QtyConsumed',
  `qty_exchanged`             decimal(18,5) DEFAULT NULL COMMENT '← $.QtyExchd',
  `refs`                      varchar(200)  DEFAULT NULL COMMENT '← $.Refs',
  `operation_no`              int           DEFAULT NULL COMMENT '← $.Op',
  `company_id`                bigint        NOT NULL COMMENT 'LEGACY_SCOPE ← $.CompanyRefId',
  `factory_id`                bigint        NOT NULL COMMENT 'LEGACY_SCOPE ← $.FactoryRefId',
  `legacy_parent_item_id`     bigint        NOT NULL COMMENT '🔴 技术属性 ← $.ParentMaterialId。解析 → ItemMaster.RecID 时**必须带 tenant_id**：忽略租户会 0 失败但静默串租户（同租户失败 2060 行）',
  `legacy_component_item_id`  bigint        NOT NULL COMMENT '🔴 技术属性 ← $.ComponentMaterialId。同上：忽略租户 0 失败，而那 170 行恰恰全挂到别的租户',
  `source_system`             varchar(50)   NOT NULL,
  `source_biz_key`            varchar(300)  NOT NULL,
  `source_updated_at`         datetime      DEFAULT NULL COMMENT '← $.UpdatedAt（源仅 4/62036，保留系框架约定槽位）',
  `sync_batch_id`             varchar(64)   NOT NULL,
  `sync_time`                 datetime      NOT NULL,
  `create_time`               datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`               datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  -- 索引字节 = 8 + 50*4 + 50*4 = 408 < 3072
  UNIQUE KEY `uk_bridge_item_bom` (`tenant_id`,`item_code`,`component_item_code`),
  KEY `idx_bib_parent` (`tenant_id`,`item_code`),
  KEY `idx_bib_component` (`tenant_id`,`component_item_code`),
  KEY `idx_bib_batch` (`sync_batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  COMMENT='S0 关系桥：BOM 结构。源 ProductStructureMaster，FULL REPLACE。父 = dim_item（自然键 item_code）。IsActive 是 bit(1) 且含 1 行 NULL，双重地雷故不物化；IsEnabled/SequenceNum/StructureType distinct=1、LineOp/Remark/StartEff/EndEff 全空，一并不物化。';

CREATE TABLE IF NOT EXISTS `mdp_stg_s0_item_substitute` (
  `id`              bigint       NOT NULL AUTO_INCREMENT,
  `tenant_id`       bigint       NOT NULL DEFAULT 0,
  `factory_id`      bigint       DEFAULT NULL,
  `source_system`   varchar(50)  NOT NULL DEFAULT 'AIDOPDEV_MYSQL',
  `source_table`    varchar(100) NOT NULL,
  `source_row_id`   varchar(100) NOT NULL,
  `source_biz_key`  varchar(300) DEFAULT NULL,
  `sync_batch_id`   varchar(100) NOT NULL,
  `sync_time`       datetime     NOT NULL,
  `process_status`  varchar(20)  NOT NULL DEFAULT 'PENDING',
  `raw_data`        json         NOT NULL,
  `process_message` varchar(500) DEFAULT NULL,
  `update_time`     datetime     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_time`     datetime     DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stg_s0_item_substitute` (`tenant_id`,`source_system`,`source_table`,`source_row_id`),
  KEY `idx_stg_s0_item_substitute_biz` (`source_biz_key`),
  KEY `idx_stg_s0_item_substitute_batch` (`tenant_id`,`sync_batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S0 贴源：替代料（源 ItemSubstituteDetail）';

CREATE TABLE IF NOT EXISTS `bridge_item_substitute` (
  `id`                   bigint        NOT NULL AUTO_INCREMENT,
  `tenant_id`            bigint        NOT NULL,
  `parent_item_code`     varchar(100)  NOT NULL COMMENT '业务键 ← $.ParentItem；对 dim_item 零孤儿但框架单父故不设父。实测与 item_code 16 行无一相等，是真实不同信息',
  `item_code`            varchar(100)  NOT NULL COMMENT '业务键 + 父键 ← $.ItemNum（被替代的料）；父 = dim_item.item_code（实测孤儿 0/16）',
  `substitute_item_code` varchar(100)  NOT NULL COMMENT '业务键 ← $.SubstituteItem；对 dim_item 零孤儿，不设父',
  `substitute_qty`       decimal(18,6) DEFAULT NULL COMMENT '← $.SubstituteQty',
  `substitute_type`      varchar(20)   DEFAULT NULL COMMENT '← $.SubstituteType',
  `remark`               varchar(255)  DEFAULT NULL COMMENT '← $.Remark',
  `domain_code`          varchar(50)   NOT NULL COMMENT 'LEGACY_SCOPE ← $.Domain；在旧 UK 内但按 D-025 不入身份',
  `source_system`        varchar(50)   NOT NULL,
  `source_biz_key`       varchar(300)  NOT NULL,
  `source_updated_at`    datetime      DEFAULT NULL COMMENT '← $.UpdateTime',
  `sync_batch_id`        varchar(64)   NOT NULL,
  `sync_time`            datetime      NOT NULL,
  `create_time`          datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`          datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  -- 索引字节 = 8 + 100*4 + 100*4 + 100*4 = 1208 < 3072
  UNIQUE KEY `uk_bridge_item_substitute` (`tenant_id`,`parent_item_code`,`item_code`,`substitute_item_code`),
  KEY `idx_bis_parent` (`tenant_id`,`item_code`),
  KEY `idx_bis_batch` (`sync_batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  COMMENT='S0 关系桥：替代料。源 ItemSubstituteDetail，FULL REPLACE。父 = dim_item（自然键 item_code = 被替代料）。三个 item 引用全部统一到 dim_item。';

SET @src_id = (SELECT `id` FROM `mdp_source` WHERE `source_code` = 'AIDOPDEV_MYSQL' LIMIT 1);

-- batch_size 按最大租户留 3× 余量：ProductStructureMaster 单租户最大 57199 → 200000。
-- AssertNoSourceDuplicateAsync 是 sourceCount >= batchSize 严格判定，必须单页装下。
INSERT INTO `mdp_entity`
  (`tenant_id`,`source_id`,`entity_code`,`entity_name`,`entity_type`,
   `source_table_name`,`target_table_name`,`sync_mode`,`incr_column`,`batch_size`,
   `biz_key_expr`,`status`,`create_time`,`update_time`)
SELECT 0, @src_id, 'S0_ITEM_BOM', 'S0 BOM 结构入站', 'TABLE',
       'ProductStructureMaster', 'mdp_stg_s0_item_bom', 'FULL', NULL, 200000,
       'ParentItem,ComponentItem', 1, NOW(), NOW()
WHERE @src_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `mdp_entity` WHERE `entity_code` = 'S0_ITEM_BOM');

INSERT INTO `mdp_entity`
  (`tenant_id`,`source_id`,`entity_code`,`entity_name`,`entity_type`,
   `source_table_name`,`target_table_name`,`sync_mode`,`incr_column`,`batch_size`,
   `biz_key_expr`,`status`,`create_time`,`update_time`)
SELECT 0, @src_id, 'S0_ITEM_SUBSTITUTE', 'S0 替代料入站', 'TABLE',
       'ItemSubstituteDetail', 'mdp_stg_s0_item_substitute', 'FULL', NULL, 5000,
       'ParentItem,ItemNum,SubstituteItem', 1, NOW(), NOW()
WHERE @src_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `mdp_entity` WHERE `entity_code` = 'S0_ITEM_SUBSTITUTE');

-- ── 回滚（注释，非可执行）──
-- DROP TABLE IF EXISTS `bridge_item_bom`, `bridge_item_substitute`;
-- DROP TABLE IF EXISTS `mdp_stg_s0_item_bom`, `mdp_stg_s0_item_substitute`;
-- DELETE FROM `mdp_entity` WHERE `tenant_id`=0 AND `entity_code` IN ('S0_ITEM_BOM','S0_ITEM_SUBSTITUTE');