-- SET @pbi_datetime = '2018-01-01 10:00:10'

-- POPULATE fact_shipment_item
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from fact_shipment_item;
insert into `fact_shipment_item`
(shipment_item_id, order_item_id, weight, quantity, `stg_inserted_at`, `inserted_at`)
SELECT
  si.shipment_item_id,
  coalesce(oic.order_item_id, si.order_item_id) as order_item_id,
  si.weight as weight,
  si.qty as quantity,
  si.inserted_at as stg_inserted_at,
  @db_date_time as inserted_at
from bim_stg_shipped_item si
  join bim_stg_order_item oi
    on si.order_item_id = oi.order_item_id
    -- exclude bungle products
    -- keep configurable because they have no simple records
    and product_type <> 'bundle'
  left join bim_stg_order_item oic
    on oic.parent_item_id = si.order_item_id
    AND oi.product_type = 'configurable'
  left join fact_shipment_item f
    on f.shipment_item_id = si.shipment_item_id
WHERE si.inserted_at >= @max_inserted_at
      AND f.shipment_item_id is NULL
order by stg_inserted_at;

-- {{IF_ANALYZE}}fact_shipment_item{{/IF_ANALYZE}}
