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

-- ============================================================================
-- 1.0.562 · 中立标准层：4 张新表 + 既有表补列 + 库存事务码迁移 + 宽表改名
-- 幂等。AutoVersionUpdate 按 ; 拆句、无事务。语句内不得再嵌套分号。
-- 口径：approved_flag 默认 1（源系统不提供审核标记时视为已审核）。
-- ============================================================================

CREATE TABLE IF NOT EXISTS mdp_std_work_order_line (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    factory_id BIGINT NULL DEFAULT 1,
    source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP',
    domain VARCHAR(80) NULL,
    doc_type VARCHAR(32) NOT NULL,
    src_doc_type_raw VARCHAR(64) NULL,
    order_no VARCHAR(120) NOT NULL,
    line_no VARCHAR(50) NULL,
    task_no VARCHAR(80) NULL,
    item_code VARCHAR(100) NULL,
    qty_planned DECIMAL(18,6) NULL DEFAULT 0,
    qty_completed DECIMAL(18,6) NULL DEFAULT 0,
    plan_finish_date DATETIME NULL,
    release_time DATETIME NULL,
    closed_flag TINYINT NOT NULL DEFAULT 0,
    closed_time DATETIME NULL,
    approved_flag TINYINT NOT NULL DEFAULT 1,
    void_flag TINYINT NOT NULL DEFAULT 0,
    source_row_id VARCHAR(100) NOT NULL,
    source_biz_key VARCHAR(200) NOT NULL,
    sync_batch_id VARCHAR(100) NOT NULL,
    sync_time DATETIME NOT NULL,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_std_wo_line (tenant_id, source_system, source_biz_key),
    KEY idx_std_wo_line_order (tenant_id, doc_type, order_no, line_no),
    KEY idx_std_wo_line_task (tenant_id, task_no),
    KEY idx_std_wo_line_plan (tenant_id, plan_finish_date),
    KEY idx_std_wo_line_closed (tenant_id, closed_flag, closed_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='中立任务/订单行';

CREATE TABLE IF NOT EXISTS mdp_std_work_order_bom (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    factory_id BIGINT NULL DEFAULT 1,
    source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP',
    domain VARCHAR(80) NULL,
    order_no VARCHAR(120) NOT NULL,
    order_line_no VARCHAR(50) NULL,
    task_no VARCHAR(80) NULL,
    item_code VARCHAR(100) NULL,
    qty_required DECIMAL(18,6) NULL DEFAULT 0,
    unit VARCHAR(50) NULL,
    source_row_id VARCHAR(100) NOT NULL,
    source_biz_key VARCHAR(200) NOT NULL,
    sync_batch_id VARCHAR(100) NOT NULL,
    sync_time DATETIME NOT NULL,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_std_wo_bom (tenant_id, source_system, source_biz_key),
    KEY idx_std_wo_bom_order (tenant_id, order_no),
    KEY idx_std_wo_bom_item (tenant_id, item_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='中立任务用料清单';

CREATE TABLE IF NOT EXISTS mdp_std_employee (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    factory_id BIGINT NULL DEFAULT 1,
    source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP',
    domain VARCHAR(80) NOT NULL,
    employee_no VARCHAR(100) NOT NULL,
    employee_name VARCHAR(100) NULL,
    department_code VARCHAR(50) NULL,
    department_name VARCHAR(200) NULL,
    position_code VARCHAR(32) NOT NULL DEFAULT 'OTHER',
    src_position_raw VARCHAR(100) NULL,
    employment_status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
    src_employment_status_raw VARCHAR(100) NULL,
    source_row_id VARCHAR(100) NOT NULL,
    source_biz_key VARCHAR(200) NOT NULL,
    sync_batch_id VARCHAR(100) NOT NULL,
    sync_time DATETIME NOT NULL,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_std_employee (tenant_id, source_system, domain, employee_no),
    KEY idx_std_employee_pos (tenant_id, position_code, employment_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='中立人员与岗位';

CREATE TABLE IF NOT EXISTS mdp_std_inventory_balance_monthly (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    factory_id BIGINT NULL DEFAULT 1,
    source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP',
    domain VARCHAR(80) NULL,
    period_ym VARCHAR(6) NOT NULL,
    category_code VARCHAR(50) NOT NULL DEFAULT '',
    category_name VARCHAR(200) NULL,
    warehouse_code VARCHAR(50) NOT NULL DEFAULT '',
    warehouse_name VARCHAR(200) NULL,
    item_code VARCHAR(100) NOT NULL DEFAULT '',
    avg_balance_amount DECIMAL(18,6) NOT NULL DEFAULT 0,
    issue_cost_amount DECIMAL(18,6) NOT NULL DEFAULT 0,
    source_biz_key VARCHAR(200) NOT NULL,
    sync_batch_id VARCHAR(100) NOT NULL,
    sync_time DATETIME NOT NULL,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_std_inv_bal_m (tenant_id, source_system, period_ym, category_code, warehouse_code, item_code),
    KEY idx_std_inv_bal_m_period (tenant_id, period_ym)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='月度库存金额（成本口径）';

-- mdp_std_inv_trans 补列
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_biz_doc_type_raw')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `src_biz_doc_type_raw` VARCHAR(100) 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='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;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans' AND COLUMN_NAME='doc_qty')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `doc_qty` DECIMAL(18,6) NULL 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='line_closed_flag')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `line_closed_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='line_closed_time')=0, 'ALTER TABLE `mdp_std_inv_trans` ADD COLUMN `line_closed_time` DATETIME NULL', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 原始事务码迁到 src_trans_type_raw，trans_type 只留中立阶段码
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
SET trans_type = CASE src_trans_type_raw
      WHEN 'iss-wo' THEN 'MAT_ISSUE'
      WHEN 'rct-po' THEN 'MAT_RECEIPT'
      WHEN 'rct-unp' THEN 'MAT_RECEIPT'
      WHEN 'MAT_RECEIPT' THEN 'MAT_RECEIPT'
      WHEN 'MAT_IQC_RELEASE' THEN 'MAT_IQC_RELEASE'
      WHEN 'MAT_PUTAWAY' THEN 'MAT_PUTAWAY'
      WHEN 'MAT_PICK' THEN 'MAT_PICK'
      WHEN 'MAT_ISSUE' THEN 'MAT_ISSUE'
      WHEN 'MAT_LINE' THEN 'MAT_LINE'
      WHEN 'FG_RECEIPT' THEN 'FG_RECEIPT'
      WHEN 'FG_PUTAWAY' THEN 'FG_PUTAWAY'
      WHEN 'FG_PICK' THEN 'FG_PICK'
      WHEN 'FG_SHIP' THEN 'FG_SHIP'
      WHEN 'FG_PROD_RECEIPT' THEN 'FG_PROD_RECEIPT'
      WHEN 'FG_FQC_RELEASE' THEN 'FG_FQC_RELEASE'
      ELSE NULL END,
    biz_doc_type = CASE 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'
      ELSE biz_doc_type END
WHERE src_trans_type_raw IS NOT NULL
  AND (trans_type IS NULL OR 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'));

-- 工单排程补列，并把唯一键扩成（租户、单据类型、单号），避免生产任务与销售订单重号覆盖
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_work_order_schedule' AND COLUMN_NAME='doc_type')=0, 'ALTER TABLE `mdp_std_work_order_schedule` ADD COLUMN `doc_type` VARCHAR(32) NOT NULL DEFAULT ''PROD_TASK''', '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_work_order_schedule' AND COLUMN_NAME='approved_flag')=0, 'ALTER TABLE `mdp_std_work_order_schedule` 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_work_order_schedule' AND COLUMN_NAME='void_flag')=0, 'ALTER TABLE `mdp_std_work_order_schedule` 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.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_work_order_schedule' AND INDEX_NAME='uk_std_work_order_schedule')>0 AND (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_work_order_schedule' AND INDEX_NAME='uk_std_wo_sched_type')=0, 'ALTER TABLE `mdp_std_work_order_schedule` DROP INDEX `uk_std_work_order_schedule`, ADD UNIQUE KEY `uk_std_wo_sched_type` (`tenant_id`,`doc_type`,`work_order`)', '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_production_receipt' AND COLUMN_NAME='closed_flag')=0, 'ALTER TABLE `mdp_std_production_receipt` ADD COLUMN `closed_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_production_receipt' AND COLUMN_NAME='closed_time')=0, 'ALTER TABLE `mdp_std_production_receipt` ADD COLUMN `closed_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_production_receipt' AND COLUMN_NAME='task_no')=0, 'ALTER TABLE `mdp_std_production_receipt` ADD COLUMN `task_no` VARCHAR(80) 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_purchase_receipt' AND COLUMN_NAME='approved_flag')=0, 'ALTER TABLE `mdp_std_purchase_receipt` 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_purchase_receipt' AND COLUMN_NAME='approved_time')=0, 'ALTER TABLE `mdp_std_purchase_receipt` 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_s6_report' AND COLUMN_NAME='start_work_date')=0, 'ALTER TABLE `mdp_std_s6_report` ADD COLUMN `start_work_date` 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_s6_report' AND COLUMN_NAME='task_no')=0, 'ALTER TABLE `mdp_std_s6_report` ADD COLUMN `task_no` VARCHAR(80) 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_s6_report' AND COLUMN_NAME='domain')=0, 'ALTER TABLE `mdp_std_s6_report` ADD COLUMN `domain` VARCHAR(80) 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_fqc_task' AND COLUMN_NAME='sales_order_no')=0, 'ALTER TABLE `mdp_std_fqc_task` ADD COLUMN `sales_order_no` VARCHAR(100) 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_fqc_task' AND COLUMN_NAME='sales_order_line')=0, 'ALTER TABLE `mdp_std_fqc_task` ADD COLUMN `sales_order_line` VARCHAR(50) 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_fqc_task' AND COLUMN_NAME='apply_flag')=0, 'ALTER TABLE `mdp_std_fqc_task` ADD COLUMN `apply_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_fqc_task' AND COLUMN_NAME='domain')=0, 'ALTER TABLE `mdp_std_fqc_task` ADD COLUMN `domain` VARCHAR(80) 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_so' AND COLUMN_NAME='line_closed_flag')=0, 'ALTER TABLE `mdp_std_so` ADD COLUMN `line_closed_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_so' AND COLUMN_NAME='line_closed_time')=0, 'ALTER TABLE `mdp_std_so` ADD COLUMN `line_closed_time` DATETIME NULL', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 九张宽表去掉 t8 前缀，旧名保留为视图
SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_material_online_cycle' AND TABLE_TYPE='BASE TABLE')=1, 'RENAME TABLE `dwd_t8_material_online_cycle` TO `dwd_material_online_cycle`', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_material_online_cycle' AND TABLE_TYPE='BASE TABLE')=1 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_material_online_cycle')=0, 'CREATE VIEW `dwd_t8_material_online_cycle` AS SELECT * FROM `dwd_material_online_cycle`', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_material_online_fulfillment' AND TABLE_TYPE='BASE TABLE')=1, 'RENAME TABLE `dwd_t8_material_online_fulfillment` TO `dwd_material_online_fulfillment`', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_material_online_fulfillment' AND TABLE_TYPE='BASE TABLE')=1 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_material_online_fulfillment')=0, 'CREATE VIEW `dwd_t8_material_online_fulfillment` AS SELECT * FROM `dwd_material_online_fulfillment`', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_material_warehouse_efficiency' AND TABLE_TYPE='BASE TABLE')=1, 'RENAME TABLE `dwd_t8_material_warehouse_efficiency` TO `dwd_material_warehouse_efficiency`', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_material_warehouse_efficiency' AND TABLE_TYPE='BASE TABLE')=1 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_material_warehouse_efficiency')=0, 'CREATE VIEW `dwd_t8_material_warehouse_efficiency` AS SELECT * FROM `dwd_material_warehouse_efficiency`', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_material_inventory_turnover' AND TABLE_TYPE='BASE TABLE')=1, 'RENAME TABLE `dwd_t8_material_inventory_turnover` TO `dwd_material_inventory_turnover`', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_material_inventory_turnover' AND TABLE_TYPE='BASE TABLE')=1 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_material_inventory_turnover')=0, 'CREATE VIEW `dwd_t8_material_inventory_turnover` AS SELECT * FROM `dwd_material_inventory_turnover`', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_work_order_mfg_fulfillment' AND TABLE_TYPE='BASE TABLE')=1, 'RENAME TABLE `dwd_t8_work_order_mfg_fulfillment` TO `dwd_work_order_mfg_fulfillment`', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_work_order_mfg_fulfillment' AND TABLE_TYPE='BASE TABLE')=1 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_work_order_mfg_fulfillment')=0, 'CREATE VIEW `dwd_t8_work_order_mfg_fulfillment` AS SELECT * FROM `dwd_work_order_mfg_fulfillment`', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_work_order_mfg_efficiency' AND TABLE_TYPE='BASE TABLE')=1, 'RENAME TABLE `dwd_t8_work_order_mfg_efficiency` TO `dwd_work_order_mfg_efficiency`', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_work_order_mfg_efficiency' AND TABLE_TYPE='BASE TABLE')=1 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_work_order_mfg_efficiency')=0, 'CREATE VIEW `dwd_t8_work_order_mfg_efficiency` AS SELECT * FROM `dwd_work_order_mfg_efficiency`', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_order_shipment_cycle' AND TABLE_TYPE='BASE TABLE')=1, 'RENAME TABLE `dwd_t8_order_shipment_cycle` TO `dwd_order_shipment_cycle`', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_order_shipment_cycle' AND TABLE_TYPE='BASE TABLE')=1 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_order_shipment_cycle')=0, 'CREATE VIEW `dwd_t8_order_shipment_cycle` AS SELECT * FROM `dwd_order_shipment_cycle`', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_order_shipment_fulfillment' AND TABLE_TYPE='BASE TABLE')=1, 'RENAME TABLE `dwd_t8_order_shipment_fulfillment` TO `dwd_order_shipment_fulfillment`', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_order_shipment_fulfillment' AND TABLE_TYPE='BASE TABLE')=1 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_order_shipment_fulfillment')=0, 'CREATE VIEW `dwd_t8_order_shipment_fulfillment` AS SELECT * FROM `dwd_order_shipment_fulfillment`', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_finished_warehouse_efficiency' AND TABLE_TYPE='BASE TABLE')=1, 'RENAME TABLE `dwd_t8_finished_warehouse_efficiency` TO `dwd_finished_warehouse_efficiency`', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_finished_warehouse_efficiency' AND TABLE_TYPE='BASE TABLE')=1 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_t8_finished_warehouse_efficiency')=0, 'CREATE VIEW `dwd_t8_finished_warehouse_efficiency` AS SELECT * FROM `dwd_finished_warehouse_efficiency`', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

UPDATE ado_smart_ops_kpi_dimension_config
SET SqlScript = REPLACE(SqlScript, 'dwd_t8_', 'dwd_'),
    BusinessSqlSource = REPLACE(BusinessSqlSource, 'dwd_t8_', 'dwd_')
WHERE SqlScript LIKE '%dwd_t8_%' OR BusinessSqlSource LIKE '%dwd_t8_%';

UPDATE ado_smart_ops_kpi_calc_config
SET SqlScript = REPLACE(SqlScript, 'dwd_t8_', 'dwd_')
WHERE SqlScript LIKE '%dwd_t8_%';