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

-- =====================================================================================
-- 1.0.509  ·  S0 MASTER DATA PLATFORM · PHASE 2 · C3 关系桥（3 个）
--
--   EmpWorkDutyMaster → mdp_stg_s0_emp_work_duty → bridge_employee_work_duty
--   LineSkillMaster   → mdp_stg_s0_line_post     → dim_line_post
--   LineSkillDetail   → mdp_stg_s0_line_post     → bridge_line_post_skill   （父子共用 staging）
--
-- 身份口径（沿用 D-025）：tenant_id + 业务码；Domain/company/factory 均 LEGACY_SCOPE。
-- 但**有一处例外经实测确立**：EmpWorkDuty 的 location_code **必须留在身份里** ——
--   tenant+Employee+Duty 实测有 1 组重复（797…229 的 G00308 同一串 Duty 下
--   Location=1001 与 1000 是两条不同职责、物料也不同）。库位是真实区分维，不是 legacy scope。
--
-- 🔴 LineSkillDetail 是本仓第 4 张 dual-column 表，且最危险：四对双列里三对是「有值但发散」型
--    （影子列 250/261 非空，与真列**没有一行相等**）—— 取错不报错，得到看起来合法的错值。
--    尤其 bridge 的目标列 effective_date 其源物理列是 **StartDate**，
--    而源表另有一个字面叫 effective_date 的 0/261 影子列。已加契约测试钉死。
--
-- 🔴 父连接必须走自然键：代理键 LineSkillRecID 同租户孤儿 210/261、忽略租户后 175 行落到别的租户；
--    PersonSkillId 更甚，250/261 属于别的租户。自然键 (tenant, ProdLine, JOBNo) 三租户全 0 孤儿。
--    两个 legacy id 仅作留痕列物化，任何 join 都不得使用。
--
-- EmpSkills **不在本批**：源侧 tenant+Employee+SkillNo 有 2 组重复（RecID 4848/4943 与 5156/4945），
--   需业务 owner 经 CRUD 删除接口清理后才能接入（见 KNOWN-ISSUES）。
-- =====================================================================================

-- ── 一、贴源表（2 张）──

CREATE TABLE IF NOT EXISTS `mdp_stg_s0_emp_work_duty` (
  `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_emp_work_duty` (`tenant_id`,`source_system`,`source_table`,`source_row_id`),
  KEY `idx_stg_s0_emp_work_duty_biz` (`source_biz_key`),
  KEY `idx_stg_s0_emp_work_duty_batch` (`tenant_id`,`sync_batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S0 贴源：员工物料职责（源 EmpWorkDutyMaster）';

CREATE TABLE IF NOT EXISTS `mdp_stg_s0_line_post` (
  `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_line_post` (`tenant_id`,`source_system`,`source_table`,`source_row_id`),
  KEY `idx_stg_s0_line_post_biz` (`source_biz_key`),
  KEY `idx_stg_s0_line_post_batch` (`tenant_id`,`sync_batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S0 贴源：产线岗位与岗位技能（LineSkillMaster + LineSkillDetail 共用，靠 source_table 隔离）';

-- ── 二、桥接 / 维度表（3 张）──

CREATE TABLE IF NOT EXISTS `bridge_employee_work_duty` (
  `id`                bigint       NOT NULL AUTO_INCREMENT,
  `tenant_id`         bigint       NOT NULL,
  `employee_code`     varchar(100) NOT NULL COMMENT '工号（业务键）← $.Employee；按业务键引用 dim_employee',
  `duty`              varchar(500) NOT NULL COMMENT '职责串（业务键）← $.Duty',
  `location_code`     varchar(100) NOT NULL COMMENT '库位（**业务键，不可省**）← $.Location；实测同 Employee+Duty 下有两个不同 Location',
  `domain_code`       varchar(50)  NOT NULL COMMENT 'LEGACY_SCOPE ← $.Domain',
  `company_id`        bigint       NOT NULL COMMENT 'LEGACY_SCOPE ← $.company_ref_id',
  `factory_id`        bigint       NOT NULL COMMENT 'LEGACY_SCOPE ← $.factory_ref_id',
  `line_code`         varchar(100) DEFAULT NULL COMMENT '← $.ProdLine；软引用 dim_line，无 FK',
  `item_num`          varchar(100) DEFAULT NULL COMMENT '← $.ItemNum',
  `item_num_from`     varchar(100) DEFAULT NULL COMMENT '← $.ItemNum1',
  `item_num_to`       varchar(100) DEFAULT NULL COMMENT '← $.ItemNum2',
  `emp_type`          varchar(50)  DEFAULT NULL COMMENT '← $.EmpType',
  `source_system`     varchar(50)  NOT NULL,
  `source_biz_key`    varchar(300) NOT NULL,
  `source_updated_at` datetime     DEFAULT NULL COMMENT '← $.UpdateTime（源 10/25，允许 NULL）',
  `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 + 500*4 + 100*4 = 2808 < 3072（DYNAMIC/utf8mb4）。任一列加宽都会越界，改前必须重算。
  UNIQUE KEY `uk_bridge_employee_work_duty` (`tenant_id`,`employee_code`,`duty`,`location_code`),
  KEY `idx_bridge_ewd_batch` (`sync_batch_id`),
  KEY `idx_bridge_ewd_emp` (`tenant_id`,`employee_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S0 关系桥：员工物料职责。源 EmpWorkDutyMaster，FULL REPLACE。父 = dim_employee。';

CREATE TABLE IF NOT EXISTS `dim_line_post` (
  `id`                bigint       NOT NULL AUTO_INCREMENT,
  `tenant_id`         bigint       NOT NULL,
  `line_code`         varchar(100) NOT NULL COMMENT '产线码（业务键）← $.ProdLine；软引用 dim_line，28/57 对不上故**不设父**',
  `job_no`            varchar(100) NOT NULL COMMENT '岗位码（业务键）← $.JOBNo；当前每租户单值，保留以防不可逆键收缩',
  `domain_code`       varchar(50)  NOT NULL COMMENT 'LEGACY_SCOPE ← $.Domain',
  `job_name`          varchar(255) DEFAULT NULL COMMENT '← $.JOBDescr',
  `site`              varchar(12)  DEFAULT NULL COMMENT '← $.Site（源 0/57 全 NULL）；保留可见性——它在源 UK 里，丢了将来看不出身份为何被破坏',
  `order_by`          int          DEFAULT NULL COMMENT '← $.Orderby',
  `run_crew`          decimal(10,3) DEFAULT NULL COMMENT '← $.RunCrew；**scale 必须与源 decimal(10,3) 逐位相同**，否则 CAST AS CHAR 后 0.000≠0.0，校验和 FAIL',
  `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`),
  UNIQUE KEY `uk_dim_line_post` (`tenant_id`,`line_code`,`job_no`),
  KEY `idx_dim_line_post_batch` (`sync_batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S0 维度：产线岗位。源 LineSkillMaster。源侧 UK 含全 NULL 的 Site 故形同虚设，本表 UK 是第一道真守卫。';

CREATE TABLE IF NOT EXISTS `bridge_line_post_skill` (
  `id`                        bigint       NOT NULL AUTO_INCREMENT,
  `tenant_id`                 bigint       NOT NULL,
  `line_code`                 varchar(100) NOT NULL COMMENT '业务键 + 父键 ← $.ProdLine',
  `job_no`                    varchar(100) NOT NULL COMMENT '业务键 + 父键 ← $.JOBNo',
  `skill_code`                varchar(100) NOT NULL COMMENT '业务键 ← $.SkillNo；软引用 dim_person_skill（210/261 对不上，**不设父**）',
  `domain_code`               varchar(50)  DEFAULT NULL COMMENT 'LEGACY_SCOPE ← $.Domain（源可空，故不设 Required）',
  `site`                      varchar(12)  DEFAULT NULL COMMENT '← $.Site（源 0/261）',
  `effective_date`            datetime     DEFAULT NULL COMMENT '🔴 源物理列是 **$.StartDate**，不是同表那个 0/261 的影子列 effective_date',
  `required_level`            varchar(50)  DEFAULT NULL COMMENT '← $.required_level',
  `remark`                    varchar(500) DEFAULT NULL COMMENT '← $.remark',
  `is_enabled`                tinyint(1)   NOT NULL COMMENT '← $.is_enabled',
  `legacy_line_skill_rec_id`  bigint       DEFAULT NULL COMMENT '⚠️ 仅留痕 ← $.LineSkillRecID；同租户孤儿 210/261，**禁止 join**',
  `legacy_person_skill_id`    bigint       DEFAULT NULL COMMENT '⚠️ 仅留痕 ← $.PersonSkillId；250/261 属于别的租户，**禁止 join**',
  `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`),
  UNIQUE KEY `uk_bridge_line_post_skill` (`tenant_id`,`line_code`,`job_no`,`skill_code`),
  KEY `idx_bls_parent` (`tenant_id`,`line_code`,`job_no`),
  KEY `idx_bls_batch` (`sync_batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S0 关系桥：产线岗位技能。源 LineSkillDetail，FULL REPLACE。父 = dim_line_post，**走自然键**。';

-- ── 三、mdp_entity 入站配置（3 条，全 FULL / incr_column=NULL）──

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

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_EMP_WORK_DUTY', 'S0 员工物料职责入站', 'TABLE',
       'EmpWorkDutyMaster', 'mdp_stg_s0_emp_work_duty', 'FULL', NULL, 5000,
       'Employee,Duty,Location', 1, NOW(), NOW()
WHERE @src_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `mdp_entity` WHERE `entity_code` = 'S0_EMP_WORK_DUTY');

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_LINE_POST', 'S0 产线岗位入站', 'TABLE',
       'LineSkillMaster', 'mdp_stg_s0_line_post', 'FULL', NULL, 5000,
       'ProdLine,JOBNo', 1, NOW(), NOW()
WHERE @src_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `mdp_entity` WHERE `entity_code` = 'S0_LINE_POST');

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_LINE_POST_SKILL', 'S0 产线岗位技能入站', 'TABLE',
       'LineSkillDetail', 'mdp_stg_s0_line_post', 'FULL', NULL, 5000,
       'ProdLine,JOBNo,SkillNo', 1, NOW(), NOW()
WHERE @src_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `mdp_entity` WHERE `entity_code` = 'S0_LINE_POST_SKILL');

-- ── 回滚（注释，非可执行）──
-- DROP TABLE IF EXISTS `bridge_line_post_skill`,`dim_line_post`,`bridge_employee_work_duty`;
-- DROP TABLE IF EXISTS `mdp_stg_s0_line_post`,`mdp_stg_s0_emp_work_duty`;
-- DELETE FROM `mdp_entity` WHERE `tenant_id`=0 AND `entity_code` IN
--   ('S0_EMP_WORK_DUTY','S0_LINE_POST','S0_LINE_POST_SKILL');