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

-- 1.0.565 —— 库位角色重算、阶段码回填、演示数据与默认租户清理、来源登记、S8 刷新权限
-- 不修改已发布的 1.0.564.sql。可重复执行。不使用事务。
-- WHEN 分支与 NeutralTransTypeCodes 逐字相同。
-- 开发库可能尚未执行 1.0.564，这里先保证表存在，再把错误角色纠正过来。

CREATE TABLE IF NOT EXISTS mdp_location_role (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    domain VARCHAR(50) NOT NULL DEFAULT '',
    location VARCHAR(100) NOT NULL,
    location_role VARCHAR(24) NOT NULL DEFAULT 'UNKNOWN' COMMENT 'MAT_INSPECT/MATERIAL/MAT_LINE_LOC/SEMI/FG_INSPECT/FG/FG_LINE_LOC/REJECT_SCRAP/VMI/OTHER/UNKNOWN',
    role_source VARCHAR(24) NOT NULL DEFAULT 'AUTO_TYPED' COMMENT 'RATIFIED=已定口径 AUTO_TYPED=按库位类型推断 MANUAL=运维维护',
    remark VARCHAR(255) NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_location_role (tenant_id, domain, location),
    KEY idx_location_role_role (tenant_id, location_role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='库位中立角色（阶段码判定用）';

CREATE TABLE IF NOT EXISTS mdp_std_inv_trans_unmapped (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    source_system VARCHAR(50) NOT NULL,
    src_rec_id VARCHAR(100) NOT NULL,
    src_trans_type_raw VARCHAR(64) NOT NULL,
    row_count BIGINT NOT NULL DEFAULT 1,
    first_seen DATETIME NOT NULL,
    last_seen DATETIME NOT NULL,
    UNIQUE KEY uk_inv_unmapped (tenant_id, source_system, src_rec_id),
    KEY idx_inv_unmapped_code (tenant_id, source_system, src_trans_type_raw)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='未映射到中立阶段码的库存流水';

SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans_unmapped' AND COLUMN_NAME='unmapped_reason')=0, 'ALTER TABLE `mdp_std_inv_trans_unmapped` ADD COLUMN `unmapped_reason` VARCHAR(40) NOT NULL DEFAULT ''UNKNOWN_CODE''', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans_unmapped' AND COLUMN_NAME='location')=0, 'ALTER TABLE `mdp_std_inv_trans_unmapped` ADD COLUMN `location` VARCHAR(100) NOT NULL DEFAULT ''''', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

ALTER TABLE mdp_location_role
    MODIFY COLUMN location_role VARCHAR(24) NOT NULL DEFAULT 'UNKNOWN'
    COMMENT 'MAT_INSPECT/MATERIAL/MAT_LINE_LOC/SEMI/FG_INSPECT/FG/FG_LINE_LOC/REJECT_SCRAP/VMI/OTHER/UNKNOWN';

INSERT INTO mdp_location_role (tenant_id, domain, location, location_role, role_source, remark)
SELECT IFNULL(lm.tenant_id, 0), IFNULL(lm.Domain, ''), lm.location,
       CASE WHEN NULLIF(TRIM(IFNULL(lm.typed,'')),'') IS NULL OR (CASE WHEN IFNULL(lm.typed,'') LIKE '%半成品%' THEN 'SEMI' WHEN IFNULL(lm.typed,'') LIKE '%成品%' AND (IFNULL(lm.typed,'') LIKE '%待检%' OR IFNULL(lm.typed,'') LIKE '%检验%') THEN 'FG_INSPECT' WHEN IFNULL(lm.typed,'') LIKE '%成品%' AND (IFNULL(lm.typed,'') LIKE '%线边%' OR IFNULL(lm.typed,'') LIKE '%产线%') THEN 'FG_LINE_LOC' WHEN IFNULL(lm.typed,'') LIKE '%不合格%' OR IFNULL(lm.typed,'') LIKE '%不良%' OR IFNULL(lm.typed,'') LIKE '%报废%' OR IFNULL(lm.typed,'') LIKE '%呆滞%' OR IFNULL(lm.typed,'') LIKE '%无价值%' THEN 'REJECT_SCRAP' WHEN IFNULL(lm.typed,'') LIKE '%待检%' OR IFNULL(lm.typed,'') LIKE '%检验%' OR IFNULL(lm.typed,'') LIKE '%IQC%' THEN 'MAT_INSPECT' WHEN IFNULL(lm.typed,'') LIKE '%线边%' OR IFNULL(lm.typed,'') LIKE '%产线%' THEN 'MAT_LINE_LOC' WHEN IFNULL(lm.typed,'') LIKE '%成品%' THEN 'FG' WHEN IFNULL(lm.typed,'') LIKE '%VMI%' OR IFNULL(lm.typed,'') LIKE '%寄售%' THEN 'VMI' WHEN IFNULL(lm.typed,'') LIKE '%材料%' OR IFNULL(lm.typed,'') LIKE '%物料%' OR IFNULL(lm.typed,'') LIKE '%原料%' OR IFNULL(lm.typed,'') LIKE '%辅料%' THEN 'MATERIAL' ELSE 'OTHER' END)='OTHER' THEN (CASE WHEN IFNULL(lm.descr,'') LIKE '%半成品%' THEN 'SEMI' WHEN IFNULL(lm.descr,'') LIKE '%成品%' AND (IFNULL(lm.descr,'') LIKE '%待检%' OR IFNULL(lm.descr,'') LIKE '%检验%') THEN 'FG_INSPECT' WHEN IFNULL(lm.descr,'') LIKE '%成品%' AND (IFNULL(lm.descr,'') LIKE '%线边%' OR IFNULL(lm.descr,'') LIKE '%产线%') THEN 'FG_LINE_LOC' WHEN IFNULL(lm.descr,'') LIKE '%不合格%' OR IFNULL(lm.descr,'') LIKE '%不良%' OR IFNULL(lm.descr,'') LIKE '%报废%' OR IFNULL(lm.descr,'') LIKE '%呆滞%' OR IFNULL(lm.descr,'') LIKE '%无价值%' THEN 'REJECT_SCRAP' WHEN IFNULL(lm.descr,'') LIKE '%待检%' OR IFNULL(lm.descr,'') LIKE '%检验%' OR IFNULL(lm.descr,'') LIKE '%IQC%' THEN 'MAT_INSPECT' WHEN IFNULL(lm.descr,'') LIKE '%线边%' OR IFNULL(lm.descr,'') LIKE '%产线%' THEN 'MAT_LINE_LOC' WHEN IFNULL(lm.descr,'') LIKE '%成品%' THEN 'FG' WHEN IFNULL(lm.descr,'') LIKE '%VMI%' OR IFNULL(lm.descr,'') LIKE '%寄售%' THEN 'VMI' WHEN IFNULL(lm.descr,'') LIKE '%材料%' OR IFNULL(lm.descr,'') LIKE '%物料%' OR IFNULL(lm.descr,'') LIKE '%原料%' OR IFNULL(lm.descr,'') LIKE '%辅料%' THEN 'MATERIAL' ELSE 'OTHER' END) ELSE (CASE WHEN IFNULL(lm.typed,'') LIKE '%半成品%' THEN 'SEMI' WHEN IFNULL(lm.typed,'') LIKE '%成品%' AND (IFNULL(lm.typed,'') LIKE '%待检%' OR IFNULL(lm.typed,'') LIKE '%检验%') THEN 'FG_INSPECT' WHEN IFNULL(lm.typed,'') LIKE '%成品%' AND (IFNULL(lm.typed,'') LIKE '%线边%' OR IFNULL(lm.typed,'') LIKE '%产线%') THEN 'FG_LINE_LOC' WHEN IFNULL(lm.typed,'') LIKE '%不合格%' OR IFNULL(lm.typed,'') LIKE '%不良%' OR IFNULL(lm.typed,'') LIKE '%报废%' OR IFNULL(lm.typed,'') LIKE '%呆滞%' OR IFNULL(lm.typed,'') LIKE '%无价值%' THEN 'REJECT_SCRAP' WHEN IFNULL(lm.typed,'') LIKE '%待检%' OR IFNULL(lm.typed,'') LIKE '%检验%' OR IFNULL(lm.typed,'') LIKE '%IQC%' THEN 'MAT_INSPECT' WHEN IFNULL(lm.typed,'') LIKE '%线边%' OR IFNULL(lm.typed,'') LIKE '%产线%' THEN 'MAT_LINE_LOC' WHEN IFNULL(lm.typed,'') LIKE '%成品%' THEN 'FG' WHEN IFNULL(lm.typed,'') LIKE '%VMI%' OR IFNULL(lm.typed,'') LIKE '%寄售%' THEN 'VMI' WHEN IFNULL(lm.typed,'') LIKE '%材料%' OR IFNULL(lm.typed,'') LIKE '%物料%' OR IFNULL(lm.typed,'') LIKE '%原料%' OR IFNULL(lm.typed,'') LIKE '%辅料%' THEN 'MATERIAL' ELSE 'OTHER' END) END,
       'AUTO_TYPED',
       CONCAT('自动生成于 1.0.565；typed=', IFNULL(lm.typed,''), '；descr=', IFNULL(lm.descr,''))
FROM LocationMaster lm
WHERE TRIM(lm.location) <> ''
  AND IFNULL(lm.typed,'') <> 'Supp'
ON DUPLICATE KEY UPDATE
    location_role = IF(
        role_source = 'MANUAL'
        OR (role_source = 'RATIFIED' AND IFNULL(remark,'') NOT LIKE '自动生成于 1.0.564%' AND location_role NOT IN ('INSPECT','LINE','SCRAP')),
        location_role,
        VALUES(location_role)),
    role_source = IF(
        role_source = 'MANUAL'
        OR (role_source = 'RATIFIED' AND IFNULL(remark,'') NOT LIKE '自动生成于 1.0.564%' AND location_role NOT IN ('INSPECT','LINE','SCRAP')),
        role_source,
        'AUTO_TYPED'),
    remark = IF(
        role_source = 'MANUAL'
        OR (role_source = 'RATIFIED' AND IFNULL(remark,'') NOT LIKE '自动生成于 1.0.564%' AND location_role NOT IN ('INSPECT','LINE','SCRAP')),
        remark,
        VALUES(remark));

-- 开发库可能尚未执行 1.0.562：先补来源码列，再把仍写在 trans_type 上的原始事务码迁过去。
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='biz_doc_type')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `biz_doc_type` VARCHAR(32) NOT NULL DEFAULT ''OTHER'' COMMENT ''中立业务单据类型''', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='src_trans_type_raw')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `src_trans_type_raw` VARCHAR(64) NULL', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='approved_time')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `approved_time` DATETIME NULL', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='approved_flag')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `approved_flag` TINYINT NOT NULL DEFAULT 1', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='void_flag')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `void_flag` TINYINT NOT NULL DEFAULT 0', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='summary_flag')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `summary_flag` TINYINT NOT NULL DEFAULT 0', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='ref_task_no')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `ref_task_no` VARCHAR(80) NULL', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

UPDATE mdp_std_inv_trans
SET src_trans_type_raw = trans_type
WHERE src_trans_type_raw IS NULL
  AND 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');

UPDATE mdp_std_inv_trans t
LEFT JOIN mdp_location_role r
       ON r.tenant_id = t.tenant_id AND r.domain = IFNULL(t.domain,'') AND r.location = IFNULL(t.location,'')
SET t.trans_type = CASE WHEN t.src_trans_type_raw IN ('inv-frz','inv-unfrz','iss-po','tag-cnf','tag-cnt') THEN NULL WHEN t.src_trans_type_raw='iss-wo' THEN 'MAT_ISSUE' WHEN t.src_trans_type_raw='rct-po' THEN 'MAT_RECEIPT' WHEN t.src_trans_type_raw='rct-po-ins' THEN 'MAT_RECEIPT' WHEN t.src_trans_type_raw='rct-rv' THEN 'FG_PROD_RECEIPT' WHEN t.src_trans_type_raw='iss-tr' AND IFNULL(t.qty_change,0)<0 AND IFNULL(r.location_role,'UNKNOWN')='MAT_INSPECT' THEN 'MAT_IQC_RELEASE' WHEN t.src_trans_type_raw='iss-tr' AND IFNULL(t.qty_change,0)<0 AND IFNULL(r.location_role,'UNKNOWN')='MATERIAL' THEN 'MAT_PICK' WHEN t.src_trans_type_raw='iss-tr' AND IFNULL(t.qty_change,0)<0 AND IFNULL(r.location_role,'UNKNOWN')='VMI' THEN 'MAT_PICK' WHEN t.src_trans_type_raw='iss-tr' AND IFNULL(t.qty_change,0)<0 AND IFNULL(r.location_role,'UNKNOWN')='FG_INSPECT' THEN 'FG_FQC_RELEASE' WHEN t.src_trans_type_raw='iss-tr' AND IFNULL(t.qty_change,0)<0 AND IFNULL(r.location_role,'UNKNOWN')='FG' THEN 'FG_PICK' WHEN t.src_trans_type_raw='iss-tr' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='FG' THEN 'FG_PUTAWAY' WHEN t.src_trans_type_raw='iss-tr' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='MATERIAL' THEN 'MAT_PUTAWAY' WHEN t.src_trans_type_raw='iss-tr' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='MAT_LINE_LOC' THEN 'MAT_LINE' WHEN t.src_trans_type_raw='iss-tr-ins' AND IFNULL(t.qty_change,0)<0 AND IFNULL(r.location_role,'UNKNOWN')='MAT_INSPECT' THEN 'MAT_IQC_RELEASE' WHEN t.src_trans_type_raw='iss-tr-ins' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='MATERIAL' THEN 'MAT_PUTAWAY' WHEN t.src_trans_type_raw='iss-tr-ins' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='MAT_LINE_LOC' THEN 'MAT_LINE' WHEN t.src_trans_type_raw='rct-tr' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='MATERIAL' THEN 'MAT_PUTAWAY' WHEN t.src_trans_type_raw='rct-tr' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='MAT_LINE_LOC' THEN 'MAT_LINE' WHEN t.src_trans_type_raw='rct-tr' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='FG' THEN 'FG_PUTAWAY' WHEN t.src_trans_type_raw='rct-wo' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='FG_LINE_LOC' THEN 'FG_PROD_RECEIPT' WHEN t.src_trans_type_raw='rct-wo' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='FG' THEN 'FG_PROD_RECEIPT' WHEN t.src_trans_type_raw='rct-wo' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='SEMI' THEN 'MAT_RECEIPT' WHEN t.src_trans_type_raw='rct-wo' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='MATERIAL' THEN 'MAT_RECEIPT' WHEN t.src_trans_type_raw='iss-unp' AND IFNULL(t.qty_change,0)<0 AND IFNULL(r.location_role,'UNKNOWN')='MATERIAL' THEN 'MAT_ISSUE' WHEN t.src_trans_type_raw='rct-unp' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='MATERIAL' THEN 'MAT_RECEIPT' WHEN t.src_trans_type_raw='VMI' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='VMI' THEN 'MAT_RECEIPT' WHEN t.src_trans_type_raw='MAT_RECEIPT' THEN 'MAT_RECEIPT' WHEN t.src_trans_type_raw='MAT_IQC_RELEASE' THEN 'MAT_IQC_RELEASE' WHEN t.src_trans_type_raw='MAT_PUTAWAY' THEN 'MAT_PUTAWAY' WHEN t.src_trans_type_raw='MAT_PICK' THEN 'MAT_PICK' WHEN t.src_trans_type_raw='MAT_ISSUE' THEN 'MAT_ISSUE' WHEN t.src_trans_type_raw='MAT_LINE' THEN 'MAT_LINE' WHEN t.src_trans_type_raw='FG_RECEIPT' THEN 'FG_RECEIPT' WHEN t.src_trans_type_raw='FG_PUTAWAY' THEN 'FG_PUTAWAY' WHEN t.src_trans_type_raw='FG_PICK' THEN 'FG_PICK' WHEN t.src_trans_type_raw='FG_SHIP' THEN 'FG_SHIP' WHEN t.src_trans_type_raw='FG_PROD_RECEIPT' THEN 'FG_PROD_RECEIPT' WHEN t.src_trans_type_raw='FG_FQC_RELEASE' THEN 'FG_FQC_RELEASE' ELSE NULL END,
    t.biz_doc_type = CASE t.src_trans_type_raw WHEN 'iss-wo' THEN 'PROD_ISSUE' WHEN 'rct-wo' THEN 'PROD_RECEIPT' WHEN 'rct-po' THEN 'PUR_RECEIPT' WHEN 'rct-unp' THEN 'PUR_RECEIPT' WHEN 'iss-so' THEN 'SALES_SHIP' WHEN 'rct-po-ins' THEN 'PUR_RECEIPT' WHEN 'rct-rv' THEN 'OUTSOURCE_RECEIPT' WHEN 'iss-po' THEN 'PUR_RETURN' WHEN 'tag-cnf' THEN 'INVENTORY_ADJUST' WHEN 'tag-cnt' THEN 'INVENTORY_ADJUST' WHEN 'VMI' THEN 'CONSIGNMENT_RECEIPT' ELSE IFNULL(t.biz_doc_type,'OTHER') END
WHERE t.source_system = 'DOPDEMORQ_SQLSERVER'
  AND t.src_trans_type_raw IN ('iss-wo','rct-po','iss-tr','rct-tr','iss-unp','rct-wo','iss-tr-ins','VMI','rct-rv','rct-po-ins','iss-rv','rct-unp','inv-frz','inv-unfrz','iss-po','tag-cnf','tag-cnt');

DELETE FROM mdp_std_inv_trans
WHERE source_system = 'DOPDEMORQ_SQLSERVER'
  AND src_trans_type_raw = 'iss-rv';

DELETE FROM mdp_std_inv_trans_unmapped
WHERE src_trans_type_raw IN ('inv-frz','inv-unfrz','iss-po','tag-cnf','tag-cnt','iss-rv');

DELETE u 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;

UPDATE mdp_std_inv_trans_unmapped u
LEFT 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
LEFT JOIN mdp_location_role r
       ON r.tenant_id = u.tenant_id AND r.domain = IFNULL(t.domain,'') AND r.location = IFNULL(u.location,'')
SET u.unmapped_reason = CASE WHEN 1=0 THEN 'PENDING_VERIFY' WHEN u.src_trans_type_raw IN ('iss-tr','iss-tr-ins','rct-tr','rct-wo','iss-unp','rct-unp','VMI') AND IFNULL(r.location_role,'UNKNOWN')='UNKNOWN' THEN 'LOCATION_ROLE_UNCONFIGURED' WHEN u.src_trans_type_raw IN ('iss-tr','iss-tr-ins','rct-tr','rct-wo','iss-unp','rct-unp','VMI') THEN 'OUT_OF_SCOPE_ROLE' ELSE 'UNKNOWN_CODE' END;

INSERT INTO mdp_std_inv_trans_unmapped
  (tenant_id, source_system, src_rec_id, src_trans_type_raw, unmapped_reason, location, row_count, first_seen, last_seen)
SELECT t.tenant_id, t.source_system, t.src_rec_id, t.src_trans_type_raw,
       CASE WHEN 1=0 THEN 'PENDING_VERIFY' WHEN t.src_trans_type_raw IN ('iss-tr','iss-tr-ins','rct-tr','rct-wo','iss-unp','rct-unp','VMI') AND IFNULL(lr.location_role,'UNKNOWN')='UNKNOWN' THEN 'LOCATION_ROLE_UNCONFIGURED' WHEN t.src_trans_type_raw IN ('iss-tr','iss-tr-ins','rct-tr','rct-wo','iss-unp','rct-unp','VMI') THEN 'OUT_OF_SCOPE_ROLE' ELSE 'UNKNOWN_CODE' END,
       IFNULL(t.location,''), 1, NOW(), NOW()
FROM mdp_std_inv_trans t
LEFT JOIN mdp_location_role lr
       ON lr.tenant_id = t.tenant_id AND lr.domain = IFNULL(t.domain,'') AND lr.location = IFNULL(t.location,'')
WHERE t.source_system = 'DOPDEMORQ_SQLSERVER'
  AND t.trans_type IS NULL
  AND t.src_trans_type_raw IS NOT NULL
  AND t.src_trans_type_raw NOT IN ('inv-frz','inv-unfrz','iss-po','tag-cnf','tag-cnt')
  AND t.src_trans_type_raw NOT IN ('iss-rv')
  AND t.tenant_id <> 1300000000001
ON DUPLICATE KEY UPDATE
  last_seen = VALUES(last_seen),
  unmapped_reason = VALUES(unmapped_reason),
  location = VALUES(location);

DELETE FROM mdp_std_inv_trans
WHERE source_system = 'UAT_GENERATOR'
  AND tenant_id = 838257186181189;

SET @kpi_default_tenant := (SELECT COUNT(*) FROM ado_s9_kpi_value_l1_day WHERE tenant_id = 1300000000001);
DELETE FROM mdp_std_inv_trans
WHERE source_system = 'DOPDEMORQ_SQLSERVER'
  AND tenant_id = 1300000000001
  AND @kpi_default_tenant = 0;
DELETE FROM mdp_std_inventory
WHERE source_system = 'DOPDEMORQ_SQLSERVER'
  AND tenant_id = 1300000000001
  AND @kpi_default_tenant = 0;

CREATE TABLE IF NOT EXISTS mdp_source_table_registry (
    id BIGINT NOT NULL AUTO_INCREMENT,
    source_table VARCHAR(64) NOT NULL COMMENT '贴源记录的原始表名',
    source_system VARCHAR(50) NOT NULL COMMENT '真实来源系统码',
    remark VARCHAR(255) NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_source_table (source_table)
) COMMENT='原始表名 → 真实来源系统 的唯一对照';

SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='written_by')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `written_by` VARCHAR(32) NOT NULL DEFAULT ''LEGACY_UNVERIFIED'' COMMENT ''DB_SYNC/API_INBOUND/PLATFORM_FORM/SEED/LEGACY_UNVERIFIED''', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

UPDATE mdp_std_inv_trans
SET written_by = 'DB_SYNC'
WHERE source_system = 'DOPDEMORQ_SQLSERVER'
  AND written_by = 'LEGACY_UNVERIFIED';

INSERT INTO `SysMenu` (`Id`,`Pid`,`Title`,`Name`,`Type`,`Permission`,`OrderNo`,`Status`,`IsHide`,`IsKeepAlive`,`IsAffix`,`IsIframe`,`Remark`,`CreateTime`)
SELECT 1329008000124, 1322000000027, '刷新看板数据', 'aidopS8Btn24', 3, 's8:dashboard:refresh', 320, 1, 0, 1, 0, 0, 'S8 权限点 s8:dashboard:refresh', NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `SysMenu` m WHERE m.`Id` = 1329008000124 OR m.`Permission` = 's8:dashboard:refresh');

INSERT INTO `SysRoleMenu` (`Id`, `RoleId`, `MenuId`)
SELECT 1329008000124000 + ROW_NUMBER() OVER (ORDER BY src.RoleId), src.RoleId, 1329008000124
FROM (
  SELECT DISTINCT rm.`RoleId`
    FROM `SysRoleMenu` rm
    JOIN `SysMenu` m ON m.`Id` = rm.`MenuId`
   WHERE m.`Permission` LIKE 's8:config:%'
     AND NOT EXISTS (
           SELECT 1 FROM `SysRoleMenu` owned
            WHERE owned.`RoleId` = rm.`RoleId` AND owned.`MenuId` = 1329008000124
         )
) src;