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

-- =====================================================================================
-- 1.0.504  ·  S0 MASTER DATA PLATFORM · PILOT（Batch 1~3）
--
-- 目标：为 S0 建立「业务主表 → mdp_stg_s0_* → dim_*」的主数据供给链路（只读取源，FULL REPLACE）。
--       S0 业务主表仍是 System of Record；本脚本不改任何 S0 业务表、不改任何共享 MDP 对象。
--
-- 新建 7 张表 + 4 条 mdp_entity：
--   贴源：mdp_stg_s0_work_center / mdp_stg_s0_department / mdp_stg_s0_location
--         （后者被 LocationMaster 与 LocationShelfMaster 共用，靠 source_table 隔离）
--   维度：dim_work_center / dim_department / dim_location / dim_location_shelf
--
-- 明确不做（跨模块红线）：
--   · 不建、不改、不删 mdp_stg_location（1.0.300 建的共享表，dopdemorq 侧在用）
--   · 不改 DOPDEMORQ_SQLSERVER 的 mdp_source / mdp_entity 任何一行
--   · 不改 mdp_std_item / mdp_std_supplier 及 S3 同步服务
--   · 不写 mdp_field_mapping：实测其运行时消费者只有 Excel 导入链路
--     （MdpExcelImportService / MdpExcelTemplateService / MdpExcelValidation），
--      DB 拉取链路读的是 mdp_entity.biz_key_expr —— 写了就是死配置
--
-- 幂等：CREATE TABLE IF NOT EXISTS + INSERT ... WHERE NOT EXISTS；DELETE = 0，UPDATE = 0。
-- 回滚：见文件末尾注释块（全部为新建对象，可整体 DROP）。
-- =====================================================================================

-- =====================================================================================
-- 一、贴源层（信封逐列对齐 1.0.300，另有 3 处刻意偏离，见下）
--
-- 偏离 1：UNIQUE 纳入 source_system。
--   1.0.300 的 uk 是 (tenant_id, source_table, source_row_id)，不含 source_system；
--   而不同源系统的 source_table 可能同名（dopdemorq 侧就与本库同名），
--   且 MdpStagingWriter 的 ON DUPLICATE KEY UPDATE 不更新 source_system —— 会静默串源。
-- 偏离 2：source_biz_key 放宽到 varchar(300)。
--   货架业务键 = domain_code(50)+'#'+location(100)+'#'+inv_shelf(100) 最长 252 字符，
--   原 varchar(200) 在 STRICT 模式下会直接报错。
-- 偏离 3：factory_id 用 bigint（对齐较新的 mdp_stg_item 信封），而非 1.0.300 的 varchar(64)。
--   MdpStagingWriter 写入的本就是 long?。
-- =====================================================================================

CREATE TABLE IF NOT EXISTS `mdp_stg_s0_work_center` (
  `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_work_center` (`tenant_id`,`source_system`,`source_table`,`source_row_id`),
  KEY `idx_stg_s0_work_center_biz` (`source_biz_key`),
  KEY `idx_stg_s0_work_center_batch` (`tenant_id`,`sync_batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S0 主数据贴源：工作中心（源 WorkCtrMaster）';

CREATE TABLE IF NOT EXISTS `mdp_stg_s0_department` (
  `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_department` (`tenant_id`,`source_system`,`source_table`,`source_row_id`),
  KEY `idx_stg_s0_department_biz` (`source_biz_key`),
  KEY `idx_stg_s0_department_batch` (`tenant_id`,`sync_batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S0 主数据贴源：部门（源 DepartmentMaster）';

-- LocationMaster 与 LocationShelfMaster 共用本表，靠 source_table 隔离。
-- 货架源主键是 rec_id（不在 MdpDbPullExecutor.ResolveSourceRowId 的候选列名里），
-- 会退化成随机 GUID；且父表 FULL Replace 会让 rec_id 每轮变化。
-- 因此本链路：purge 分区 → FULL pull → 物化只认本批 sync_batch_id，
-- 完全不依赖 source_row_id 的稳定性。
CREATE TABLE IF NOT EXISTS `mdp_stg_s0_location` (
  `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_location` (`tenant_id`,`source_system`,`source_table`,`source_row_id`),
  KEY `idx_stg_s0_location_biz` (`source_biz_key`),
  KEY `idx_stg_s0_location_batch` (`tenant_id`,`source_table`,`sync_batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S0 主数据贴源：库位 + 货架（源 LocationMaster / LocationShelfMaster）';

-- =====================================================================================
-- 二、维度层
--
-- 共同约定：
--   · id 只是技术行号：FULL REPLACE 每轮重建即变化，**禁止任何外部按它建稳定引用**，下游一律按业务键 join。
--   · tenant_id NOT NULL：dim 无 C# 实体、不进 CodeFirst，不受 ITenantIdFilter.TenantId 必须 long? 的约束。
--   · is_active 是源 IsActive 的 1:1 透传，**不是** dim 自己的存在性状态；
--     源删除的行下一轮直接从 dim 消失（FULL REPLACE），不做 SCD、不加 is_present/deleted_at。
--   · 排序规则与源表一致（utf8mb4_0900_ai_ci），保证 dim UK 的判等语义与源 UK 完全相同。
--   · 无 DB 外键：子维度孤儿策略是 ALLOW + QUALITY WARNING（保留并外化，不丢弃、不阻断管线）。
-- =====================================================================================

-- 源 WorkCtrMaster 没有 company_ref_id / factory_ref_id 两列，故本 dim 也不设 —— 不为四表整齐而发明数据。
CREATE TABLE IF NOT EXISTS `dim_work_center` (
  `id`                bigint       NOT NULL AUTO_INCREMENT COMMENT '技术行号；每轮 FULL 重建即变，禁止外部引用',
  `tenant_id`         bigint       NOT NULL COMMENT '租户',
  `domain_code`       varchar(50)  NOT NULL COMMENT '工厂域编码 ← raw_data.$.Domain',
  `work_center_code`  varchar(100) NOT NULL COMMENT '工作中心编码（业务键）← $.WorkCtr',
  `work_center_name`  varchar(255) DEFAULT NULL COMMENT '← $.Descr',
  `department_code`   varchar(100) DEFAULT NULL COMMENT '所属部门编码 ← $.Department；按业务键引用 dim_department，无 DB FK',
  `is_active`         tinyint(1)   NOT NULL COMMENT '源 IsActive 透传（raw_data 中为 JSON BOOLEAN）',
  `source_system`     varchar(50)  NOT NULL,
  `source_biz_key`    varchar(300) NOT NULL COMMENT '回溯 staging 用',
  `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_work_center` (`tenant_id`,`domain_code`,`work_center_code`),
  KEY `idx_dim_work_center_batch` (`sync_batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S0 主数据维度：工作中心。源 WorkCtrMaster，FULL REPLACE。';

CREATE TABLE IF NOT EXISTS `dim_department` (
  `id`                bigint       NOT NULL AUTO_INCREMENT COMMENT '技术行号；禁止外部引用',
  `tenant_id`         bigint       NOT NULL,
  `company_id`        bigint       NOT NULL COMMENT '← $.company_ref_id；legacy 数值域与 SysOrg 雪花域并存，原样透传',
  `factory_id`        bigint       NOT NULL COMMENT '← $.factory_ref_id；与 domain_code 非 1:1，属性非身份',
  `domain_code`       varchar(50)  NOT NULL COMMENT '← $.Domain（物理列名，不是 domain_code）',
  `department_code`   varchar(100) NOT NULL COMMENT '部门编码（业务键）← $.Department',
  `department_name`   varchar(255) DEFAULT NULL COMMENT '← $.Descr',
  `is_active`         tinyint(1)   NOT NULL COMMENT '源 IsActive 透传',
  `source_system`     varchar(50)  NOT NULL,
  `source_biz_key`    varchar(300) NOT NULL,
  `source_updated_at` datetime     DEFAULT NULL COMMENT '← $.UpdateTime；源侧存在恒 NULL 的历史行，FULL 语义下不受影响',
  `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_department` (`tenant_id`,`domain_code`,`department_code`),
  KEY `idx_dim_department_batch` (`sync_batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S0 主数据维度：部门。源 DepartmentMaster，FULL REPLACE。';

-- 🔴 源 LocationMaster 是 dual-column 复刻表，snake_case 一侧是**填了假值的死列**（不是 NULL）：
--    rec_id 恒 0 / domain_code 恒空串 / is_active 恒 0 / physical_address 恒 NULL / create_user 恒 NULL。
--    因此 COALESCE(rec_id, RecID) 这类防御写法无效，WHERE is_active=1 会静默返回空集。
--    本 dim 的所有取值一律显式声明 PascalCase 真列，见 S0DimCatalog.Location。
CREATE TABLE IF NOT EXISTS `dim_location` (
  `id`                bigint       NOT NULL AUTO_INCREMENT COMMENT '技术行号；禁止外部引用',
  `tenant_id`         bigint       NOT NULL,
  `company_id`        bigint       NOT NULL COMMENT '← $.company_ref_id',
  `factory_id`        bigint       NOT NULL COMMENT '← $.factory_ref_id',
  `domain_code`       varchar(50)  NOT NULL COMMENT '← $.Domain（真列）。禁止读 $.domain_code —— 该影子列恒空串',
  `location_code`     varchar(100) NOT NULL COMMENT '库位编码（业务键）← $.location',
  `location_name`     varchar(255) DEFAULT NULL COMMENT '← $.descr',
  `location_type`     varchar(50)  DEFAULT NULL COMMENT '← $.typed',
  `storer`            varchar(300) DEFAULT NULL COMMENT '货主/保管方 ← $.storer',
  `physical_address`  varchar(500) DEFAULT NULL COMMENT '← $.PhysicalAddress（真列）。禁止读 $.physical_address',
  `is_active`         tinyint(1)   NOT NULL COMMENT '← $.IsActive（真列）。禁止读 $.is_active —— 影子列恒 0',
  `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_location` (`tenant_id`,`domain_code`,`location_code`),
  KEY `idx_dim_location_code` (`tenant_id`,`location_code`) COMMENT '支撑 Reconciler 的「镜像源 UK (tenant_id,location) 唯一性」断言',
  KEY `idx_dim_location_batch` (`sync_batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S0 主数据维度：库位。源 LocationMaster（dual-column，域列真名 Domain），FULL REPLACE。';

-- 源 LocationShelfMaster 全列 snake_case、无影子列 —— 不可套用 LocationMaster 的禁用规则。
-- 源无 IsActive 列，故本 dim 也不设 is_active（不发明状态语义）。
CREATE TABLE IF NOT EXISTS `dim_location_shelf` (
  `id`                bigint       NOT NULL AUTO_INCREMENT COMMENT '技术行号；禁止外部引用（源 rec_id 每次父表保存即变，更不可引用）',
  `tenant_id`         bigint       NOT NULL,
  `company_id`        bigint       NOT NULL COMMENT '← $.company_ref_id',
  `factory_id`        bigint       NOT NULL COMMENT '← $.factory_ref_id',
  `domain_code`       varchar(50)  NOT NULL COMMENT '← $.domain_code（本表确为 snake_case）｜父引用',
  `location_code`     varchar(100) NOT NULL COMMENT '← $.location｜父引用 → dim_location(tenant_id,domain_code,location_code)',
  `shelf_code`        varchar(100) NOT NULL COMMENT '货架编码（业务键）← $.inv_shelf',
  `shelf_name`        varchar(255) DEFAULT NULL COMMENT '← $.descr',
  `area`              varchar(100) DEFAULT NULL COMMENT '← $.area',
  `source_system`     varchar(50)  NOT NULL,
  `source_biz_key`    varchar(300) NOT NULL,
  `source_updated_at` datetime     DEFAULT NULL COMMENT '← $.update_time；源侧该列全为 NULL，本列现阶段恒 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`),
  UNIQUE KEY `uk_dim_location_shelf` (`tenant_id`,`domain_code`,`location_code`,`shelf_code`),
  KEY `idx_dim_location_shelf_parent` (`tenant_id`,`domain_code`,`location_code`),
  KEY `idx_dim_location_shelf_batch` (`sync_batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S0 主数据维度子表：货架。源 LocationShelfMaster，FULL REPLACE，无 is_active，无 FK。';

-- =====================================================================================
-- 三、mdp_entity 入站配置（4 条，全 FULL）
--
-- · 源一律解析 mdp_source.source_code='AIDOPDEV_MYSQL'（本库样板源），禁止硬编码 source_id。
-- · 变量解析失败则全部 INSERT 降为空操作（@src_id IS NOT NULL 守卫）。
-- · mdp_entity 全库 tenant_id 恒为 0（全局配置），不涉及租户解析。
-- · incr_column 必须为 NULL：留了它会让 BuildSelectSql 生成 "col > @cursor"，
--   使源侧水位为 NULL 的行（DepartmentMaster 存在这样的历史行）永久不可达。
-- · biz_key_expr 是【源物理列名】的逗号列表，由 MdpStagingWriter.BuildBizKey 以 '#' 拼接；
--   任一字段取不到值就整体回落到 source_row_id —— 货架若写成 PascalCase 就会静默退化成 GUID。
-- =====================================================================================

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_WORK_CENTER', 'S0 工作中心主数据入站', 'TABLE',
       'WorkCtrMaster', 'mdp_stg_s0_work_center', 'FULL', NULL, 5000,
       'Domain,WorkCtr', 1, NOW(), NOW()
WHERE @src_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `mdp_entity` WHERE `entity_code` = 'S0_WORK_CENTER');

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_DEPARTMENT', 'S0 部门主数据入站', 'TABLE',
       'DepartmentMaster', 'mdp_stg_s0_department', 'FULL', NULL, 5000,
       'Domain,Department', 1, NOW(), NOW()
WHERE @src_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `mdp_entity` WHERE `entity_code` = 'S0_DEPARTMENT');

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_LOCATION', 'S0 库位主数据入站', 'TABLE',
       'LocationMaster', 'mdp_stg_s0_location', 'FULL', NULL, 5000,
       'Domain,location', 1, NOW(), NOW()
WHERE @src_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `mdp_entity` WHERE `entity_code` = 'S0_LOCATION');

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_LOCATION_SHELF', 'S0 货架主数据入站', 'TABLE',
       'LocationShelfMaster', 'mdp_stg_s0_location', 'FULL', NULL, 5000,
       'domain_code,location,inv_shelf', 1, NOW(), NOW()
WHERE @src_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `mdp_entity` WHERE `entity_code` = 'S0_LOCATION_SHELF');

-- =====================================================================================
-- 回滚（全部为本脚本新建对象；执行前请确认无其他消费者）
--
-- DROP TABLE IF EXISTS `dim_location_shelf`;
-- DROP TABLE IF EXISTS `dim_location`;
-- DROP TABLE IF EXISTS `dim_department`;
-- DROP TABLE IF EXISTS `dim_work_center`;
-- DROP TABLE IF EXISTS `mdp_stg_s0_location`;
-- DROP TABLE IF EXISTS `mdp_stg_s0_department`;
-- DROP TABLE IF EXISTS `mdp_stg_s0_work_center`;
-- DELETE FROM `mdp_entity`
--  WHERE `entity_code` IN ('S0_WORK_CENTER','S0_DEPARTMENT','S0_LOCATION','S0_LOCATION_SHELF');
-- =====================================================================================