ALTER TABLE `quality_inspect`
    ADD COLUMN `qualified_quantity` decimal(18, 2) NULL DEFAULT NULL COMMENT '合格数量' AFTER `quantity`,
    ADD COLUMN `unqualified_quantity` decimal(18, 2) NULL DEFAULT NULL COMMENT '不合格数量' AFTER `qualified_quantity`,
    ADD COLUMN `pass_rate` decimal(18, 2) NULL DEFAULT NULL COMMENT '合格率(%)' AFTER `unqualified_quantity`;

-- 历史数据：按原「检测结果」与总数量回填（无总数量则跳过）
UPDATE `quality_inspect`
SET `unqualified_quantity` = CASE WHEN `check_result` = '不合格' THEN IFNULL(`quantity`, 0) ELSE 0 END,
    `qualified_quantity`   = CASE WHEN `check_result` = '不合格' THEN 0 ELSE IFNULL(`quantity`, 0) END
WHERE `qualified_quantity` IS NULL
  AND `unqualified_quantity` IS NULL
  AND `quantity` IS NOT NULL;

UPDATE `quality_inspect`
SET `pass_rate` = CASE
                      WHEN `quantity` IS NULL OR `quantity` = 0 THEN 0
                      ELSE ROUND(IFNULL(`qualified_quantity`, 0) * 100 / `quantity`, 2)
    END
WHERE `pass_rate` IS NULL;