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

-- POPULATE fact_shipment_item_parent
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from fact_shipment_item_parent;
insert into `fact_shipment_item_parent`
(shipment_item_id, order_item_id, quantity, `stg_inserted_at`, `inserted_at`)
  SELECT
      -- max in this case is equivalent to first() function
    max(sip.shipment_item_id) as shipment_item_id,
    sip.order_item_id,
    if( oip.product_type = 'bundle',
        -- calculate difference between sum of already inserted quantity vs max quantity of new new records
        -- count functions help to deal with duplicates
        greatest(coalesce(sum(fo.quantity) * count(distinct fo.pk_fact_shipment_item_parent) / count(fo.pk_fact_shipment_item_parent), 0),
                 max(sip.qty))
        - coalesce(sum(fo.quantity) * count(distinct fo.pk_fact_shipment_item_parent) / count(fo.pk_fact_shipment_item_parent), 0),
        -- for the rest of complex products just take the quantity as it is
        coalesce(max(sip.qty), 0)) as quantity,
    max(sip.inserted_at) as stg_inserted_at,
    @db_date_time as inserted_at
  from bim_stg_shipped_item sip
    join bim_stg_order_item oip
      on oip.order_item_id = sip.order_item_id
    left join fact_shipment_item_parent f
      on f.shipment_item_id = sip.shipment_item_id
    left join fact_shipment_item_parent fo
      on fo.order_item_id = sip.order_item_id
  WHERE sip.inserted_at >= @max_inserted_at
    AND f.shipment_item_id is NULL
    AND oip.product_type in ('bundle', 'configurable')
  GROUP BY sip.order_item_id
order by stg_inserted_at;
insert into `fact_shipment_item_parent`
(shipment_item_id, order_item_id, quantity, `stg_inserted_at`, `inserted_at`)
  SELECT
    sip.shipment_item_id,
    sip.order_item_id,
    if( oip.product_type = 'bundle', 0, sip.qty) as quantity,
    sip.inserted_at as stg_inserted_at,
    @db_date_time as inserted_at
  from bim_stg_shipped_item sip
    join bim_stg_order_item oip
      on oip.order_item_id = sip.order_item_id
    left join fact_shipment_item_parent f
      on f.shipment_item_id = sip.shipment_item_id
  WHERE sip.inserted_at >= @max_inserted_at
    AND f.shipment_item_id is NULL
    AND oip.product_type in ('bundle', 'configurable')
  GROUP BY sip.shipment_item_id
  order by stg_inserted_at;

-- {{IF_ANALYZE}}fact_shipment_item_parent{{/IF_ANALYZE}}
