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

-- 1.0.350：统一 S3/S4 MDP 的 tenant_id + factory_id 作用域，并修复贴源表审计字段漂移。
SET NAMES utf8mb4;

-- S4 STG：四张活动贴源表补 factory_id；历史数据归入默认工厂 1。
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_stg_s4_iqc' AND COLUMN_NAME='factory_id')=0,'ALTER TABLE `mdp_stg_s4_iqc` ADD COLUMN `factory_id` BIGINT NOT NULL DEFAULT 1 AFTER `tenant_id`','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_stg_s4_return' AND COLUMN_NAME='factory_id')=0,'ALTER TABLE `mdp_stg_s4_return` ADD COLUMN `factory_id` BIGINT NOT NULL DEFAULT 1 AFTER `tenant_id`','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_stg_s4_shipment' AND COLUMN_NAME='factory_id')=0,'ALTER TABLE `mdp_stg_s4_shipment` ADD COLUMN `factory_id` BIGINT NOT NULL DEFAULT 1 AFTER `tenant_id`','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_stg_s4_shortage' AND COLUMN_NAME='factory_id')=0,'ALTER TABLE `mdp_stg_s4_shortage` ADD COLUMN `factory_id` BIGINT NOT NULL DEFAULT 1 AFTER `tenant_id`','DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- S3 STD / DWD 与 S4 QC DWD：补齐 factory_id，消除中途丢失工厂作用域。
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_delivery_schedule' AND COLUMN_NAME='factory_id')=0,'ALTER TABLE `mdp_std_delivery_schedule` ADD COLUMN `factory_id` BIGINT NOT NULL DEFAULT 1 AFTER `tenant_id`','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_delivery_result' AND COLUMN_NAME='factory_id')=0,'ALTER TABLE `mdp_std_delivery_result` ADD COLUMN `factory_id` BIGINT NOT NULL DEFAULT 1 AFTER `tenant_id`','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_material_readiness' AND COLUMN_NAME='factory_id')=0,'ALTER TABLE `mdp_std_material_readiness` ADD COLUMN `factory_id` BIGINT NOT NULL DEFAULT 1 AFTER `tenant_id`','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_process_outsource_order' AND COLUMN_NAME='factory_id')=0,'ALTER TABLE `mdp_std_process_outsource_order` ADD COLUMN `factory_id` BIGINT NOT NULL DEFAULT 1 AFTER `tenant_id`','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='dwd_supply_demand' AND COLUMN_NAME='factory_id')=0,'ALTER TABLE `dwd_supply_demand` ADD COLUMN `factory_id` BIGINT NOT NULL DEFAULT 1 AFTER `tenant_id`','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='dwd_supplier_delivery' AND COLUMN_NAME='factory_id')=0,'ALTER TABLE `dwd_supplier_delivery` ADD COLUMN `factory_id` BIGINT NOT NULL DEFAULT 1 AFTER `tenant_id`','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='dwd_supplier_risk' AND COLUMN_NAME='factory_id')=0,'ALTER TABLE `dwd_supplier_risk` ADD COLUMN `factory_id` BIGINT NOT NULL DEFAULT 1 AFTER `tenant_id`','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='dwd_process_outsource_delivery' AND COLUMN_NAME='factory_id')=0,'ALTER TABLE `dwd_process_outsource_delivery` ADD COLUMN `factory_id` BIGINT NOT NULL DEFAULT 1 AFTER `tenant_id`','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='dwd_material_readiness' AND COLUMN_NAME='factory_id')=0,'ALTER TABLE `dwd_material_readiness` ADD COLUMN `factory_id` BIGINT NOT NULL DEFAULT 1 AFTER `tenant_id`','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='dwd_material_shortage' AND COLUMN_NAME='factory_id')=0,'ALTER TABLE `dwd_material_shortage` ADD COLUMN `factory_id` BIGINT NOT NULL DEFAULT 1 AFTER `tenant_id`','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='dwd_qc_trans' AND COLUMN_NAME='factory_id')=0,'ALTER TABLE `dwd_qc_trans` ADD COLUMN `factory_id` BIGINT NOT NULL DEFAULT 1 AFTER `tenant_id`','DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 全部 MDP 贴源表必须具备 create_time / update_time；补齐已发现的历史漂移。
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_stg_external_row' AND COLUMN_NAME='update_time')=0,'ALTER TABLE `mdp_stg_external_row` ADD COLUMN `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP','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_stg_ipqc_inspection' AND COLUMN_NAME='create_time')=0,'ALTER TABLE `mdp_stg_ipqc_inspection` ADD COLUMN `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP','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_stg_ipqc_inspection_detail' AND COLUMN_NAME='create_time')=0,'ALTER TABLE `mdp_stg_ipqc_inspection_detail` ADD COLUMN `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP','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_stg_outsource_issue' AND COLUMN_NAME='create_time')=0,'ALTER TABLE `mdp_stg_outsource_issue` ADD COLUMN `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP','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_stg_outsource_issue_detail' AND COLUMN_NAME='create_time')=0,'ALTER TABLE `mdp_stg_outsource_issue_detail` ADD COLUMN `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP','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_stg_s4_return' AND COLUMN_NAME='create_time')=0,'ALTER TABLE `mdp_stg_s4_return` ADD COLUMN `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP','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_stg_s4_shipment' AND COLUMN_NAME='create_time')=0,'ALTER TABLE `mdp_stg_s4_shipment` ADD COLUMN `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP','DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 历史上该表的唯一键曾被异常启动流程移除，保留重复快照后按更新时间只留最新版本。
CREATE TABLE IF NOT EXISTS `dwd_supplier_delivery_dup_bak_1_0_350` LIKE `dwd_supplier_delivery`;
INSERT IGNORE INTO `dwd_supplier_delivery_dup_bak_1_0_350`
SELECT old.*
FROM `dwd_supplier_delivery` old
JOIN `dwd_supplier_delivery` newer
  ON newer.tenant_id=old.tenant_id
 AND newer.factory_id=old.factory_id
 AND newer.po_no=old.po_no
 AND newer.po_line=old.po_line
 AND newer.stat_date=old.stat_date
 AND (
      newer.update_time>old.update_time
      OR (newer.update_time=old.update_time AND newer.calc_time>old.calc_time)
      OR (newer.update_time=old.update_time AND newer.calc_time=old.calc_time AND newer.id>old.id)
 );
DELETE old
FROM `dwd_supplier_delivery` old
JOIN `dwd_supplier_delivery` newer
  ON newer.tenant_id=old.tenant_id
 AND newer.factory_id=old.factory_id
 AND newer.po_no=old.po_no
 AND newer.po_line=old.po_line
 AND newer.stat_date=old.stat_date
 AND (
      newer.update_time>old.update_time
      OR (newer.update_time=old.update_time AND newer.calc_time>old.calc_time)
      OR (newer.update_time=old.update_time AND newer.calc_time=old.calc_time AND newer.id>old.id)
 );

-- 唯一键纳入工厂，避免同一租户不同工厂的同业务键互相覆盖。
ALTER TABLE `mdp_stg_s4_iqc`
  DROP INDEX `uk_mdp_stg_s4_iqc`, ADD UNIQUE INDEX `uk_mdp_stg_s4_iqc` (`tenant_id`,`factory_id`,`source_table`,`source_row_id`),
  DROP INDEX `uk_source_key`, ADD UNIQUE INDEX `uk_source_key` (`tenant_id`,`factory_id`,`source_system`,`source_table`,`source_biz_key`);
ALTER TABLE `mdp_stg_s4_return`
  DROP INDEX `uk_mdp_stg_s4_return`, ADD UNIQUE INDEX `uk_mdp_stg_s4_return` (`tenant_id`,`factory_id`,`source_table`,`source_row_id`),
  DROP INDEX `uk_source_key`, ADD UNIQUE INDEX `uk_source_key` (`tenant_id`,`factory_id`,`source_system`,`source_table`,`source_biz_key`);
ALTER TABLE `mdp_stg_s4_shipment`
  DROP INDEX `uk_mdp_stg_s4_shipment`, ADD UNIQUE INDEX `uk_mdp_stg_s4_shipment` (`tenant_id`,`factory_id`,`source_table`,`source_row_id`),
  DROP INDEX `uk_source_key`, ADD UNIQUE INDEX `uk_source_key` (`tenant_id`,`factory_id`,`source_system`,`source_table`,`source_biz_key`);
ALTER TABLE `mdp_stg_s4_shortage`
  DROP INDEX `uk_mdp_stg_s4_shortage`, ADD UNIQUE INDEX `uk_mdp_stg_s4_shortage` (`tenant_id`,`factory_id`,`source_table`,`source_row_id`),
  DROP INDEX `uk_source_key`, ADD UNIQUE INDEX `uk_source_key` (`tenant_id`,`factory_id`,`source_system`,`source_table`,`source_biz_key`);

ALTER TABLE `mdp_std_delivery_schedule`
  DROP INDEX `uk_delivery_plan`, ADD UNIQUE INDEX `uk_delivery_plan` (`tenant_id`,`factory_id`,`delivery_plan_no`),
  DROP INDEX `uk_source_key`, ADD UNIQUE INDEX `uk_source_key` (`tenant_id`,`factory_id`,`source_system`,`source_biz_key`);
ALTER TABLE `mdp_std_delivery_result`
  DROP INDEX `uk_source_key`, ADD UNIQUE INDEX `uk_source_key` (`tenant_id`,`factory_id`,`source_system`,`source_biz_key`);
ALTER TABLE `mdp_std_material_readiness`
  DROP INDEX `uk_source_key`, ADD UNIQUE INDEX `uk_source_key` (`tenant_id`,`factory_id`,`source_system`,`source_biz_key`);
ALTER TABLE `mdp_std_process_outsource_order`
  DROP INDEX `uk_source_key`, ADD UNIQUE INDEX `uk_source_key` (`tenant_id`,`factory_id`,`source_system`,`source_biz_key`);

ALTER TABLE `dwd_supply_demand`
  DROP INDEX `uk_demand_stat`, ADD UNIQUE INDEX `uk_demand_stat` (`tenant_id`,`factory_id`,`stat_date`,`demand_no`,`demand_line`);
SET @has_supplier_delivery_uk := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_supplier_delivery' AND INDEX_NAME='uk_po_line_stat'
);
SET @sql := IF(
  @has_supplier_delivery_uk>0,
  'ALTER TABLE `dwd_supplier_delivery` DROP INDEX `uk_po_line_stat`, ADD UNIQUE INDEX `uk_po_line_stat` (`tenant_id`,`factory_id`,`po_no`,`po_line`,`stat_date`)',
  'ALTER TABLE `dwd_supplier_delivery` ADD UNIQUE INDEX `uk_po_line_stat` (`tenant_id`,`factory_id`,`po_no`,`po_line`,`stat_date`)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
ALTER TABLE `dwd_supplier_risk`
  DROP INDEX `uk_supplier_risk`, ADD UNIQUE INDEX `uk_supplier_risk` (`tenant_id`,`factory_id`,`stat_date`,`supplier_code`,`item_code`,`risk_type`);
CREATE TABLE IF NOT EXISTS `aidop_migration_derived_cleanup_log` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `migration_version` VARCHAR(32) NOT NULL,
  `table_name` VARCHAR(128) NOT NULL,
  `estimated_rows` BIGINT NOT NULL DEFAULT 0,
  `data_length` BIGINT NOT NULL DEFAULT 0,
  `reason` VARCHAR(500) NOT NULL,
  `captured_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_migration_cleanup` (`migration_version`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `aidop_migration_derived_cleanup_log`
(`migration_version`,`table_name`,`estimated_rows`,`data_length`,`reason`)
SELECT '1.0.350','dwd_process_outsource_delivery',IFNULL(TABLE_ROWS,0),IFNULL(DATA_LENGTH,0),
       '历史无作用域重算膨胀约 9628 万行；该表为可重建 DWD，清空后由 tenant+factory 作用域重新生成'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_process_outsource_delivery'
ON DUPLICATE KEY UPDATE reason=VALUES(reason);
TRUNCATE TABLE `dwd_process_outsource_delivery`;
ALTER TABLE `dwd_process_outsource_delivery`
  DROP INDEX `uk_work_op_stat`, ADD UNIQUE INDEX `uk_work_op_stat` (`tenant_id`,`factory_id`,`stat_date`,`work_order`,`op_code`,`po_no`,`po_line`);
ALTER TABLE `dwd_material_readiness`
  DROP INDEX `uk_work_component_stat`, ADD UNIQUE INDEX `uk_work_component_stat` (`tenant_id`,`factory_id`,`stat_date`,`work_order`,`op_code`,`component_item_code`);
ALTER TABLE `dwd_qc_trans`
  DROP INDEX `uk_dwd_qc_trans`, ADD UNIQUE INDEX `uk_dwd_qc_trans` (`tenant_id`,`factory_id`,`trans_date`,`item_code`,`supplier_code`,`batch_no`);

CREATE INDEX `idx_mdp_std_delivery_schedule_scope` ON `mdp_std_delivery_schedule` (`tenant_id`,`factory_id`);
CREATE INDEX `idx_mdp_std_delivery_result_scope` ON `mdp_std_delivery_result` (`tenant_id`,`factory_id`);
CREATE INDEX `idx_mdp_std_material_readiness_scope` ON `mdp_std_material_readiness` (`tenant_id`,`factory_id`);
CREATE INDEX `idx_mdp_std_process_outsource_scope` ON `mdp_std_process_outsource_order` (`tenant_id`,`factory_id`);
CREATE INDEX `idx_dwd_material_shortage_scope` ON `dwd_material_shortage` (`tenant_id`,`factory_id`);