-- ============================================================
-- BOM单多级优化 - 数据库变更脚本
-- 版本: v2.0.016
-- 作者: 周启威
-- 日期: 2026-03-27
-- 说明: 在 md_new_bom_item 新增子BOM引用字段，支持多级BOM结构
-- ============================================================

-- --------------------------------------------------
-- 1. md_new_bom_item 表新增字段
--    - sub_bom_id     : 引用的子BOM主键ID（当 is_sub_bom=1 时有效）
--    - is_sub_bom     : 是否子BOM项 0-否 1-是
--    - level          : BOM明细层级深度（0=顶层物料，1=子BOM展开第一层，以此类推）
--    - is_key_material: 是否关键物料（从 is_key_component 独立出来更通用）
-- --------------------------------------------------

ALTER TABLE `md_new_bom_item`
  ADD COLUMN `sub_bom_id` bigint(20) DEFAULT NULL COMMENT '引用的子BOM主键ID（多级BOM用）' AFTER `process_route`,
  ADD COLUMN `is_sub_bom` tinyint(1) DEFAULT '0' COMMENT '是否子BOM项 0-否 1-是' AFTER `sub_bom_id`,
  ADD COLUMN `level` int(11) DEFAULT '0' COMMENT 'BOM明细层级深度（0=顶层）' AFTER `is_sub_bom`,
  ADD KEY `idx_sub_bom_id` (`sub_bom_id`);

-- --------------------------------------------------
-- 2. md_new_bom 表新增字段（辅助多级展示）
--    - max_level       : BOM最大层级深度（由系统计算写入，供前端展示参考）
--    - is_multilevel   : 是否多级BOM 0-否 1-是
--    - component_count : 直接子件数量（含子BOM项）
-- --------------------------------------------------

ALTER TABLE `md_new_bom`
  ADD COLUMN `max_level` int(11) DEFAULT '0' COMMENT 'BOM最大层级深度' AFTER `sort_order`,
  ADD COLUMN `is_multilevel` tinyint(1) DEFAULT '0' COMMENT '是否多级BOM 0-否 1-是' AFTER `max_level`,
  ADD COLUMN `component_count` int(11) DEFAULT '0' COMMENT '直接子件数量（含子BOM项）' AFTER `is_multilevel`;

-- --------------------------------------------------
-- 3. 新增 BOM 层级关系表 md_new_bom_level_relation
--    用于记录任意BOM到其直接父BOM的引用关系（支持快速查询"哪些BOM用到了我"）
--    子BOM被引用时自动写入，废弃/删除时清理
-- --------------------------------------------------

CREATE TABLE IF NOT EXISTS `md_new_bom_level_relation` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `parent_bom_id` bigint(20) NOT NULL COMMENT '父级BOM主键ID',
  `parent_material_id` bigint(20) NOT NULL COMMENT '父级产品物料ID',
  `child_bom_id` bigint(20) NOT NULL COMMENT '子级BOM主键ID',
  `child_material_id` bigint(20) NOT NULL COMMENT '子级产品物料ID',
  `level` int(11) NOT NULL DEFAULT '1' COMMENT '层级（1=直接子级，2=孙子级，以此类推）',
  `bom_item_id` bigint(20) DEFAULT NULL COMMENT '对应明细行ID（来源 md_new_bom_item.id）',
  `create_by` varchar(64) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_parent_bom_id` (`parent_bom_id`),
  KEY `idx_child_bom_id` (`child_bom_id`),
  KEY `idx_parent_material_id` (`parent_material_id`),
  KEY `idx_child_material_id` (`child_material_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='BOM多级层级关系表';

-- --------------------------------------------------
-- 4. 新增 BOM 多级展开视图 v_bom_multilevel_expand
--    将多级BOM按层级展开为扁平视图，方便前端树形展示和报表
--    注意：MySQL 8.0+ 递归CTE实现
-- --------------------------------------------------

DROP VIEW IF EXISTS `v_bom_multilevel_expand`;

CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW `v_bom_multilevel_expand` AS
SELECT
  -- 层级标识（用于前端树形缩进）
  CAST(REPEAT('　', `r`.`level` - 1) AS CHAR(100)) AS `indent_space`,
  `r`.`level`                                                   AS `bom_level`,
  `r`.`parent_bom_id`                                          AS `parent_bom_id`,
  `r`.`child_bom_id`                                           AS `bom_id`,
  `b`.`bom_number`                                              AS `bom_number`,
  `b`.`product_code`                                           AS `product_code`,
  `b`.`product_name`                                           AS `product_name`,
  `b`.`specification`                                          AS `specification`,
  `b`.`version`                                                AS `bom_version`,
  `b`.`bom_type`                                                AS `bom_type`,
  `b`.`status`                                                  AS `bom_status`,
  `b`.`base_quantity`                                          AS `base_quantity`,
  `b`.`unit_name`                                              AS `unit_name`,
  `b`.`max_level`                                              AS `max_level`,
  `b`.`is_multilevel`                                          AS `is_multilevel`,
  `r`.`bom_item_id`                                            AS `bom_item_id`,
  `r`.`parent_material_id`                                     AS `parent_material_id`,
  `r`.`child_material_id`                                      AS `child_material_id`,
  `b`.`material_cost`                                          AS `direct_material_cost`,
  `b`.`labor_cost`                                              AS `direct_labor_cost`,
  `b`.`manufacturing_cost`                                     AS `direct_manufacturing_cost`,
  `b`.`total_cost`                                              AS `direct_total_cost`,
  `b`.`create_time`                                            AS `create_time`,
  `b`.`update_time`                                            AS `update_time`
FROM `md_new_bom_level_relation` `r`
INNER JOIN `md_new_bom` `b` ON `r`.`child_bom_id` = `b`.`id`
ORDER BY `r`.`parent_bom_id`, `r`.`level`, `b`.`product_name`;

-- --------------------------------------------------
-- 5. 初始化存储过程 sp_refresh_bom_level_relation
--    在BOM发布时调用，更新 md_new_bom_level_relation 表
--    逻辑：扫描指定BOM的所有明细行，按 is_sub_bom=1 的 sub_bom_id 递归收集子BOM
-- --------------------------------------------------

DROP PROCEDURE IF EXISTS `sp_refresh_bom_level_relation`;

DELIMITER $$

CREATE PROCEDURE `sp_refresh_bom_level_relation`(
  IN p_bom_id BIGINT       -- 起始BOM ID（通常为发布的BOM）
)
BEGIN
  DECLARE v_done       INT DEFAULT 0;
  DECLARE v_max_level  INT DEFAULT 10;  -- 最大递归层级保护

  -- 临时表：递归收集 BOM->子BOM 关系
  DROP TEMPORARY TABLE IF EXISTS tmp_bom_relation;
  CREATE TEMPORARY TABLE tmp_bom_relation (
    parent_bom_id     BIGINT,
    parent_material_id BIGINT,
    child_bom_id      BIGINT,
    child_material_id BIGINT,
    level             INT,
    bom_item_id       BIGINT
  );

  -- 递归游标：从指定BOM出发，收集所有 is_sub_bom=1 的明细行对应的子BOM
  INSERT INTO tmp_bom_relation
  SELECT DISTINCT
    p_bom_id                        AS parent_bom_id,
    b.material_id                   AS parent_material_id,
    item.sub_bom_id                  AS child_bom_id,
    item.material_id                 AS child_material_id,
    1                               AS level,
    item.id                          AS bom_item_id
  FROM md_new_bom b
  INNER JOIN md_new_bom_item item ON item.bom_id = b.id AND item.is_sub_bom = 1 AND item.sub_bom_id IS NOT NULL
  WHERE b.id = p_bom_id;

  -- 逐层展开（最多10层，防止死循环）
  level_loop: WHILE v_done = 0 DO
    INSERT IGNORE INTO tmp_bom_relation
    SELECT DISTINCT
      rel.child_bom_id               AS parent_bom_id,
      sub.material_id                AS parent_material_id,
      item.sub_bom_id                AS child_bom_id,
      item.material_id               AS child_material_id,
      rel.level + 1                 AS level,
      item.id                        AS bom_item_id
    FROM tmp_bom_relation rel
    INNER JOIN md_new_bom sub ON sub.id = rel.child_bom_id
    INNER JOIN md_new_bom_item item ON item.bom_id = sub.id AND item.is_sub_bom = 1 AND item.sub_bom_id IS NOT NULL
    WHERE rel.level < v_max_level;
    IF ROW_COUNT() = 0 THEN
      SET v_done = 1;
    END IF;
  END WHILE level_loop;

  -- 删除旧关系（以该BOM为父级的所有记录）
  DELETE FROM md_new_bom_level_relation WHERE parent_bom_id = p_bom_id;

  -- 写入新关系
  INSERT INTO md_new_bom_level_relation
    (parent_bom_id, parent_material_id, child_bom_id, child_material_id, level, bom_item_id)
  SELECT parent_bom_id, parent_material_id, child_bom_id, child_material_id, level, bom_item_id
  FROM tmp_bom_relation;

  -- 清理临时表
  DROP TEMPORARY TABLE tmp_bom_relation;
END$$

DELIMITER ;

-- --------------------------------------------------
-- 6. 触发器：md_new_bom_item 增删改后自动刷新层级关系
-- --------------------------------------------------

DROP TRIGGER IF EXISTS `trg_md_new_bom_item_after_insert`;

DELIMITER $$

CREATE TRIGGER `trg_md_new_bom_item_after_insert`
AFTER INSERT ON `md_new_bom_item`
FOR EACH ROW
BEGIN
  -- 如果插入的是子BOM行，且该BOM已有父BOM引用关系，则需要刷新关系表
  -- 此处仅记录，待 BOM 发布时统一刷新 sp_refresh_bom_level_relation
  -- 实际刷新由业务层在 publishBomVersion 时调用存储过程
  -- 此触发器仅用于：删除 bom_item 时连带清理关系记录
END$$

DELIMITER ;

DROP TRIGGER IF EXISTS `trg_md_new_bom_item_after_delete`;

DELIMITER $$

CREATE TRIGGER `trg_md_new_bom_item_after_delete`
AFTER DELETE ON `md_new_bom_item`
FOR EACH ROW
BEGIN
  -- 删除时清理孤立的关系记录
  DELETE FROM md_new_bom_level_relation WHERE bom_item_id = OLD.id;
END$$

DELIMITER ;

-- --------------------------------------------------
-- 7. 批量初始化脚本（对现有已发布的多级BOM执行刷新）
--    示例：对 id = 1 的 BOM 执行一次层级关系构建
--    正式上线时，取消下面注释并填写实际 BOM ID 列表
-- --------------------------------------------------

-- CALL sp_refresh_bom_level_relation(1);
-- CALL sp_refresh_bom_level_relation(2);
-- CALL sp_refresh_bom_level_relation(3);

-- --------------------------------------------------
-- 8. 更新 BOM 主表的 max_level / is_multilevel 辅助字段
--    在 sp_refresh_bom_level_relation 执行后调用
-- --------------------------------------------------

DROP PROCEDURE IF EXISTS `sp_update_bom_multilevel_flags`;

DELIMITER $$

CREATE PROCEDURE `sp_update_bom_multilevel_flags`(
  IN p_bom_id BIGINT
)
BEGIN
  DECLARE v_max_level     INT;
  DECLARE v_is_multilevel INT;
  DECLARE v_component_count INT;

  -- 计算最大层级
  SELECT COALESCE(MAX(level), 0) INTO v_max_level
  FROM md_new_bom_level_relation
  WHERE parent_bom_id = p_bom_id;

  -- 判断是否多级（直接子级中存在子BOM项即为多级）
  SELECT COUNT(*) INTO v_is_multilevel
  FROM md_new_bom_item
  WHERE bom_id = p_bom_id AND is_sub_bom = 1 AND sub_bom_id IS NOT NULL;

  -- 统计直接子件数量
  SELECT COUNT(*) INTO v_component_count
  FROM md_new_bom_item
  WHERE bom_id = p_bom_id;

  UPDATE md_new_bom
  SET max_level = v_max_level,
      is_multilevel = IF(v_max_level > 0 OR v_is_multilevel > 0, 1, 0),
      component_count = v_component_count,
      update_time = NOW()
  WHERE id = p_bom_id;
END$$

DELIMITER ;

-- 示例调用：
-- CALL sp_update_bom_multilevel_flags(1);