-- Oracle 版本
-- 第1步：每个产品自动创建cad_bom,并更新cad_bom的序列
DECLARE
  v_cad_bom_object_id_offset NUMBER;
  v_max_object_id_cad_bom NUMBER;
  v_sql VARCHAR2(500);
BEGIN
  -- 获取产品到cad_bom 最大的object_id
  SELECT NVL(MAX(object_id), 0) INTO v_cad_bom_object_id_offset FROM t_cad_bom;

  INSERT INTO t_cad_bom (object_id, internal_name, name, creator, description)
  SELECT object_id + v_cad_bom_object_id_offset, 'cad_bom', '设计BOM', creator, '产品：' || name || '的设计BOM'
  FROM t_product 
  WHERE object_id NOT IN (SELECT rolea_object_id FROM t_product_to_bom);

  -- 处理cad_bom序列
  SELECT NVL(MAX(object_id), 0) + 1 INTO v_max_object_id_cad_bom FROM T_CAD_BOM;

  -- 构建动态SQL语句
  v_sql := 'DROP SEQUENCE SEQ_ID_CAD_BOM';
  BEGIN
    EXECUTE IMMEDIATE v_sql;
  EXCEPTION
    WHEN OTHERS THEN NULL;
  END;
  
  v_sql := 'CREATE SEQUENCE SEQ_ID_CAD_BOM START WITH ' || v_max_object_id_cad_bom;
  EXECUTE IMMEDIATE v_sql;
END;
/

-- 第2步：创建产品到cad_bom的关系，并更新product_to_bom的序列
DECLARE
  v_max_object_id_product_to_cad_bom_offset NUMBER;
  v_max_object_id_product_to_cad_bom NUMBER;
  v_sql VARCHAR2(500);
BEGIN
  SELECT NVL(MAX(object_id), 0) INTO v_max_object_id_product_to_cad_bom_offset FROM t_product_to_bom;

  INSERT INTO t_product_to_bom (internal_name, creator, object_id, rolea_internal_name, rolea_object_id, roleb_internal_name, roleb_object_id)
  SELECT 'product_to_bom', creator, object_id + v_max_object_id_product_to_cad_bom_offset, 'product', object_id, 'cad_bom', object_id + v_cad_bom_object_id_offset
  FROM t_product 
  WHERE object_id NOT IN (SELECT rolea_object_id FROM t_product_to_bom);

  -- 获取product_to_bom的最大object_id并加1
  SELECT NVL(MAX(object_id), 0) + 1 INTO v_max_object_id_product_to_cad_bom FROM T_PRODUCT_TO_BOM;

  -- 构建动态SQL语句
  v_sql := 'DROP SEQUENCE SEQ_ID_PRODUCT_TO_BOM';
  BEGIN
    EXECUTE IMMEDIATE v_sql;
  EXCEPTION
    WHEN OTHERS THEN NULL;
  END;
  
  v_sql := 'CREATE SEQUENCE SEQ_ID_PRODUCT_TO_BOM START WITH ' || v_max_object_id_product_to_cad_bom;
  EXECUTE IMMEDIATE v_sql;
END;
/

-- 第3步：处理库里的零部件
-- 补齐零件属性
UPDATE t_part SET valid_flag = 1 WHERE MASTER_OBJECT_ID IS NULL;

UPDATE t_part SET MASTER_OBJECT_ID = OBJECT_ID 
WHERE MASTER_OBJECT_ID IS NULL OR MASTER_OBJECT_ID = 0;
-- todo 需要整理分类库，将零部件归类到不同的库中

-- 第4步：迁移产品到总装的关系到cad_bom_to_bom_item，并更新该表序列
DECLARE
  v_cad_bom_to_bom_item_object_id_offset NUMBER;
  v_max_object_id_cad_bom_to_bom_item NUMBER;
  v_sql VARCHAR2(500);
BEGIN
  -- 获取产品到cad_bom 最大的object_id
  SELECT NVL(MAX(object_id), 0) INTO v_cad_bom_to_bom_item_object_id_offset FROM T_CAD_BOM_TO_BOM_ITEM;
  
  INSERT INTO T_CAD_BOM_TO_BOM_ITEM (internal_name, creator, object_id, rolea_internal_name, rolea_object_id, roleb_internal_name, roleb_object_id)
  SELECT 'cad_bom_to_bom_item', creator, object_id + v_cad_bom_to_bom_item_object_id_offset, 'cad_bom', rolea_object_id + v_cad_bom_object_id_offset, ROLEB_INTERNAL_NAME, ROLEB_OBJECT_ID
  FROM T_PRODUCT_TO_ASSEMBLY 
  WHERE rolea_internal_name = 'product' AND roleb_internal_name = 'part'
    AND rolea_object_id NOT IN (
      -- 查找在 cad_bom到bom_item中 未升级过来的产品id
      SELECT product_to_bom.rolea_object_id 
      FROM T_PRODUCT_TO_BOM product_to_bom 
      INNER JOIN T_CAD_BOM_TO_BOM_ITEM bom_to_item 
        ON product_to_bom.ROLEA_INTERNAL_NAME = 'product'
        AND product_to_bom.ROLEB_OBJECT_ID = bom_to_item.ROLEA_OBJECT_ID 
        AND product_to_bom.ROLEB_INTERNAL_NAME = bom_to_item.ROLEA_INTERNAL_NAME
    ) 
    AND ROLEA_OBJECT_ID IN (SELECT object_id FROM t_product);

  -- 处理cad_bom_to_bom_item序列
  SELECT NVL(MAX(object_id), 0) + 1 INTO v_max_object_id_cad_bom_to_bom_item FROM T_CAD_BOM_TO_BOM_ITEM;
  
  -- 构建动态SQL语句
  v_sql := 'DROP SEQUENCE SEQ_ID_CAD_BOM_TO_BOM_ITEM';
  BEGIN
    EXECUTE IMMEDIATE v_sql;
  EXCEPTION
    WHEN OTHERS THEN NULL;
  END;
  
  v_sql := 'CREATE SEQUENCE SEQ_ID_CAD_BOM_TO_BOM_ITEM START WITH ' || v_max_object_id_cad_bom_to_bom_item;
  EXECUTE IMMEDIATE v_sql;
END;
/

-- 第5步：迁移产品到总装的关系到cad_bom_to_bom_item，并更新该表序列
DECLARE
  v_cad_bom_item_to_child_object_id_offset NUMBER;
  v_max_object_id_cad_bom_item_to_child NUMBER;
  v_sql VARCHAR2(500);
BEGIN
  -- 获取产品到cad_bom 最大的object_id
  SELECT NVL(MAX(object_id), 0) INTO v_cad_bom_item_to_child_object_id_offset FROM T_CAD_BOM_ITEM_TO_CHILD;

  INSERT INTO T_CAD_BOM_ITEM_TO_CHILD(internal_name, creator, object_id, rolea_internal_name, rolea_object_id, roleb_internal_name, roleb_object_id)
  SELECT 'cad_bom_item_to_child', creator, object_id + v_cad_bom_item_to_child_object_id_offset, 'part', rolea_object_id, 'part', ROLEB_OBJECT_ID
  FROM t_part_to_child 
  WHERE rolea_internal_name = 'part' AND roleb_internal_name = 'part'
    AND rolea_object_id NOT IN (
      SELECT part_to_child.rolea_object_id 
      FROM T_PART_TO_CHILD part_to_child
      INNER JOIN t_cad_bom_item_to_child cad_bom_item_to_child
        ON part_to_child.ROLEA_OBJECT_ID = cad_bom_item_to_child.ROLEA_OBJECT_ID
        AND part_to_child.ROLEA_INTERNAL_NAME = cad_bom_item_to_child.ROLEA_INTERNAL_NAME
        AND part_to_child.ROLEB_OBJECT_ID = cad_bom_item_to_child.ROLEB_OBJECT_ID
        AND part_to_child.ROLEB_INTERNAL_NAME = cad_bom_item_to_child.ROLEB_INTERNAL_NAME
    );

  -- 处理cad_bom_item_to_child序列
  SELECT NVL(MAX(object_id), 0) + 1 INTO v_max_object_id_cad_bom_item_to_child FROM T_CAD_BOM_ITEM_TO_CHILD;
  
  -- 构建动态SQL语句
  v_sql := 'DROP SEQUENCE SEQ_ID_CAD_BOM_ITEM_TO_CHILD';
  BEGIN
    EXECUTE IMMEDIATE v_sql;
  EXCEPTION
    WHEN OTHERS THEN NULL;
  END;
  
  v_sql := 'CREATE SEQUENCE SEQ_ID_CAD_BOM_ITEM_TO_CHILD START WITH ' || v_max_object_id_cad_bom_item_to_child;
  EXECUTE IMMEDIATE v_sql;
END;
/