-- 1.0.125.sql
-- S8-ORDER-CHAIN-MATERIAL-PROCUREMENT-BASELINE-FIELD-ALIGN-1
-- 给既有库 ado_s8_order_flow_procurement_pivot 表补两列：impact_count（影响台次）/ kit_rate（准时齐套率），
-- 并为 BASELINE_PPT 的 XX / YY / ZZ 关键材料 grand 单元（supplier_code=TOTAL && spec_code=TOTAL）回填业务基线值。
--
-- 修正范围：
--   ado_s8_order_flow_procurement_pivot：
--     1) 幂等加列 impact_count INT NULL；
--     2) 幂等加列 kit_rate DECIMAL(5,4) NULL；
--     3) 限定 order_id IS NULL && supplier_code='TOTAL' && spec_code='TOTAL' && is_deleted=0
--        分别 UPDATE XX(55, 0.8500) / YY(23, 0.8400) / ZZ(10, 0.9700) 三行。
--
-- 安全边界：
--   * 仅 ALTER TABLE ADD COLUMN（幂等）+ UPDATE（精确 4 条件限定单行）
--   * 禁 DELETE / TRUNCATE / DROP；表行数保持原 48 行不变
--   * 不动 cycle_days / status / scenario_code / data_source 等既有字段值
--   * 不动 ado_s8_order_flow_order / stage / substep / substep_unit / snapshot
--   * 不动 ado_s8_order_flow_product_design_drawing（产品设计阶段）
--   * 不动 S0 / .vscode / Database.json / SysMenu
--   * 不引入 TOTAL material 行（保持现有 3 material × 4 supplier × 4 spec = 48 行结构）
--
-- 幂等性：
--   * ALTER：通过 information_schema.COLUMNS 判断列是否存在，PREPARE/EXECUTE 条件执行
--   * UPDATE：四条件复合 WHERE 唯一定位 grand 单元，二次执行得到同样结果
--
-- 兼容性：
--   * MySQL 5.7+ / 8.x 通用语法
--
-- 验证（阶段 4 重演后必查）：
--   SELECT material_code, impact_count, kit_rate
--     FROM ado_s8_order_flow_procurement_pivot
--    WHERE order_id IS NULL
--      AND supplier_code = 'TOTAL'
--      AND spec_code = 'TOTAL'
--      AND is_deleted = 0
--    ORDER BY material_code;
--   → 3 行：XX=55/0.8500，YY=23/0.8400，ZZ=10/0.9700
-- 2026-05-25


-- ─── 1) 幂等加列：impact_count ───
SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA = DATABASE()
     AND TABLE_NAME = 'ado_s8_order_flow_procurement_pivot'
     AND COLUMN_NAME = 'impact_count'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE `ado_s8_order_flow_procurement_pivot` ADD COLUMN `impact_count` INT NULL COMMENT ''影响台次''',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ─── 2) 幂等加列：kit_rate ───
SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA = DATABASE()
     AND TABLE_NAME = 'ado_s8_order_flow_procurement_pivot'
     AND COLUMN_NAME = 'kit_rate'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE `ado_s8_order_flow_procurement_pivot` ADD COLUMN `kit_rate` DECIMAL(5,4) NULL COMMENT ''准时齐套率''',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ─── 3) 回填 BASELINE_PPT grand 单元（每条 UPDATE 精确命中 1 行） ───
UPDATE `ado_s8_order_flow_procurement_pivot`
   SET `impact_count` = 55,
       `kit_rate` = 0.8500
 WHERE `order_id` IS NULL
   AND `material_code` = 'XX'
   AND `supplier_code` = 'TOTAL'
   AND `spec_code` = 'TOTAL'
   AND `is_deleted` = 0;

UPDATE `ado_s8_order_flow_procurement_pivot`
   SET `impact_count` = 23,
       `kit_rate` = 0.8400
 WHERE `order_id` IS NULL
   AND `material_code` = 'YY'
   AND `supplier_code` = 'TOTAL'
   AND `spec_code` = 'TOTAL'
   AND `is_deleted` = 0;

UPDATE `ado_s8_order_flow_procurement_pivot`
   SET `impact_count` = 10,
       `kit_rate` = 0.9700
 WHERE `order_id` IS NULL
   AND `material_code` = 'ZZ'
   AND `supplier_code` = 'TOTAL'
   AND `spec_code` = 'TOTAL'
   AND `is_deleted` = 0;