sqxp/backend/scripts/migration_20260627_bas_product_spec_standard.sql

-- 数据库: sanqiang
-- 说明: 将所有未删除商品的规格（spec）统一设为「标准」
-- 规范: 先写入 bas_product_his 历史快照，再更新主表
-- 建议: 先在测试库执行并核对预览结果，再在生产库执行

USE sanqiang;

-- ---------------------------------------------------------------------------
-- 1. 预览：将要变更的记录
-- ---------------------------------------------------------------------------
SELECT
  id,
  name,
  sku_code,
  spec AS spec_before
FROM bas_product
WHERE is_del = 0
  AND (spec IS NULL OR TRIM(spec) = '' OR spec <> '标准');

-- ---------------------------------------------------------------------------
-- 2. 历史表：更新前完整复制（仅涉及将要变更的行）
-- ---------------------------------------------------------------------------
INSERT INTO bas_product_his (
  id,
  name,
  sku_code,
  category_id,
  unit_id,
  measure_value,
  spec,
  description,
  detail_content,
  image_url,
  price_coefficient,
  suggested_supplier_price,
  is_active,
  create_at,
  create_by,
  create_name,
  update_at,
  update_by,
  his_create_at
)
SELECT
  id,
  name,
  sku_code,
  category_id,
  unit_id,
  measure_value,
  spec,
  description,
  detail_content,
  image_url,
  price_coefficient,
  suggested_supplier_price,
  is_active,
  create_at,
  create_by,
  create_name,
  update_at,
  update_by,
  NOW()
FROM bas_product
WHERE is_del = 0
  AND (spec IS NULL OR TRIM(spec) = '' OR spec <> '标准');

-- ---------------------------------------------------------------------------
-- 3. 主表：统一规格为「标准」
-- ---------------------------------------------------------------------------
UPDATE bas_product
SET
  spec = '标准',
  update_at = NOW()
WHERE is_del = 0
  AND (spec IS NULL OR TRIM(spec) = '' OR spec <> '标准');

-- ---------------------------------------------------------------------------
-- 4. 验证：未删除商品应全部为「标准」
-- ---------------------------------------------------------------------------
SELECT
  COUNT(*) AS total_active,
  SUM(CASE WHEN spec = '标准' THEN 1 ELSE 0 END) AS spec_standard_count,
  SUM(CASE WHEN spec IS NULL OR spec <> '标准' THEN 1 ELSE 0 END) AS spec_not_standard_count
FROM bas_product
WHERE is_del = 0;