-- =====================================================================
-- 新建合格仓库 + 仓库类型 + 质检入合格库改造
--
-- 1) mes_wm_warehouse 增加 type 字段（10 暂存库 / 20 合格库）
-- 2) 现有仓库分类：虚拟线边仓库=10（暂存库），产品库/物料库=20（合格库）
-- 3) 新建"成品合格库"(type=20) + 一个库区 + 一个库位
-- 4) 虚拟仓库中 stock_state=20 的合格库存迁移到新合格库
--
-- 说明：本脚本幂等，可重复执行；无租户字段。
-- =====================================================================

-- 1) 增加仓库类型字段（MySQL 不支持 ALTER ADD COLUMN IF NOT EXISTS，先查存在性）
SET @type_exists = (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'mes_wm_warehouse'
    AND column_name = 'type'
);
SET @type_sql = IF(
  @type_exists = 0,
  'ALTER TABLE mes_wm_warehouse ADD COLUMN type tinyint NOT NULL DEFAULT 10 COMMENT ''仓库类型：10暂存库，20合格库'' AFTER frozen',
  'SELECT 1'
);
PREPARE type_stmt FROM @type_sql;
EXECUTE type_stmt;
DEALLOCATE PREPARE type_stmt;

-- 2) 现有仓库分类
UPDATE mes_wm_warehouse SET type = 10 WHERE code = 'WIP_VIRTUAL_WAREHOUSE' AND deleted = 0;
UPDATE mes_wm_warehouse SET type = 20 WHERE code IN ('1212', 'WHS000001') AND deleted = 0;

-- 3) 新建"成品合格库"（type=20）
SET @wh_exists = (
  SELECT COUNT(*) FROM mes_wm_warehouse WHERE code = 'FG_QUALIFIED_WH' AND deleted = 0
);
INSERT INTO mes_wm_warehouse (code, name, frozen, type, remark)
SELECT 'FG_QUALIFIED_WH', '成品合格库', 0, 20, '质检合格直接入库的目标仓库（type=20 合格库）'
WHERE @wh_exists = 0;
SET @wh_id = (SELECT id FROM mes_wm_warehouse WHERE code = 'FG_QUALIFIED_WH' AND deleted = 0 LIMIT 1);

-- 新建"成品合格库区"
SET @loc_exists = (
  SELECT COUNT(*) FROM mes_wm_warehouse_location WHERE code = 'FG_QUALIFIED_WH_LOC' AND deleted = 0
);
INSERT INTO mes_wm_warehouse_location (code, name, warehouse_id, frozen, remark)
SELECT 'FG_QUALIFIED_WH_LOC', '成品合格库区', @wh_id, 0, '质检合格入库默认库区'
WHERE @loc_exists = 0;
SET @loc_id = (SELECT id FROM mes_wm_warehouse_location WHERE code = 'FG_QUALIFIED_WH_LOC' AND deleted = 0 LIMIT 1);

-- 新建"成品合格库位"
SET @area_exists = (
  SELECT COUNT(*) FROM mes_wm_warehouse_area WHERE code = 'FG_QUALIFIED_WH_AREA' AND deleted = 0
);
INSERT INTO mes_wm_warehouse_area (code, name, location_id, frozen, remark)
SELECT 'FG_QUALIFIED_WH_AREA', '成品合格库位', @loc_id, 0, '质检合格入库默认库位'
WHERE @area_exists = 0;
SET @area_id = (SELECT id FROM mes_wm_warehouse_area WHERE code = 'FG_QUALIFIED_WH_AREA' AND deleted = 0 LIMIT 1);

-- 4) 迁移虚拟仓库合格库存到新合格库
-- 4.1 若新合格库已存在同 (item_id, batch_id, vendor_id) 的 state=20 记录，先数量累加合并
UPDATE mes_wm_material_stock t
JOIN mes_wm_material_stock s
  ON s.warehouse_id = 3 AND s.stock_state = 20 AND s.deleted = 0
 AND t.warehouse_id = @wh_id AND t.stock_state = 20 AND t.deleted = 0
 AND t.item_id = s.item_id
 AND IFNULL(t.batch_id, 0) = IFNULL(s.batch_id, 0)
 AND IFNULL(t.vendor_id, 0) = IFNULL(s.vendor_id, 0)
SET t.quantity = t.quantity + s.quantity,
    t.reserved_quantity = t.reserved_quantity + s.reserved_quantity,
    t.frozen_quantity = t.frozen_quantity + s.frozen_quantity;

-- 4.2 删除已合并到新合格库的源记录（虚拟仓库中）
DELETE s FROM mes_wm_material_stock s
JOIN mes_wm_material_stock t
  ON s.warehouse_id = 3 AND s.stock_state = 20 AND s.deleted = 0
 AND t.warehouse_id = @wh_id AND t.stock_state = 20 AND t.deleted = 0
 AND t.item_id = s.item_id
 AND IFNULL(t.batch_id, 0) = IFNULL(s.batch_id, 0)
 AND IFNULL(t.vendor_id, 0) = IFNULL(s.vendor_id, 0);

-- 4.3 其余合格库存直接改仓库/库区/库位
UPDATE mes_wm_material_stock
SET warehouse_id = @wh_id, location_id = @loc_id, area_id = @area_id
WHERE warehouse_id = 3 AND stock_state = 20 AND deleted = 0;