zkxw/docs/sql/2026-05-21-cell-product-alter.sql

-- 细胞产品库字段重设计迁移
-- 日期: 2026-05-21
-- 设计文档: docs/superpowers/specs/2026-05-21-cell-product-library-design.md

-- 1. 新增 4 个业务字段（放在 storage_slot_id 之后，product_status 之前）
ALTER TABLE biz_cell_product
  ADD COLUMN `passage` VARCHAR(8) DEFAULT NULL COMMENT '代次(P1~P8)' AFTER `storage_slot_id`,
  ADD COLUMN `freeze_date` DATETIME DEFAULT NULL COMMENT '冻存日期' AFTER `passage`,
  ADD COLUMN `expected_cell_count` DECIMAL(18,4) DEFAULT NULL COMMENT '预计细胞数量(单位:亿)' AFTER `freeze_date`,
  ADD COLUMN `viability` DECIMAL(5,2) DEFAULT NULL COMMENT '活性(百分比,0~100)' AFTER `expected_cell_count`;

-- 2. 更新 product_status 默认值和注释
ALTER TABLE biz_cell_product
  MODIFY COLUMN `product_status` VARCHAR(16) DEFAULT 'frozen' COMMENT '状态: frozen已冻存/sold已售卖/used已使用';

-- 3. 迁移旧状态数据（如有）
UPDATE biz_cell_product SET product_status = 'frozen' WHERE product_status = 'zk';
UPDATE biz_cell_product SET product_status = 'sold' WHERE product_status = 'yc';
UPDATE biz_cell_product SET product_status = 'used' WHERE product_status = 'ysy';