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

-- POPULATE fact_credit_memo_item_parent
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from fact_credit_memo_item_parent;
insert into `fact_credit_memo_item_parent`
(order_item_id, creditmemo_item_id, creditmemo_id, quantity, `stg_inserted_at`, `inserted_at`)
  select
    mip.order_item_id,
    max(mip.creditmemo_item_id) as creditmemo_item_id,
    max(mip.creditmemo_id) as creditmemo_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_credit_memo_item_parent) / count(fo.pk_fact_credit_memo_item_parent), 0),
               max(mip.qty))
      - coalesce(sum(fo.quantity) * count(distinct fo.pk_fact_credit_memo_item_parent) / count(fo.pk_fact_credit_memo_item_parent), 0),
        coalesce(max(mip.qty), 0)) as qty,
    max(mip.inserted_at) as stg_inserted_at,
    @db_date_time as inserted_at
  from bim_stg_creditmemo_item mip
    join bim_stg_order_item oip
      on oip.order_item_id = mip.order_item_id
    left join fact_credit_memo_item_parent f
      on f.creditmemo_item_id = mip.creditmemo_item_id
    left join fact_credit_memo_item_parent fo
      on fo.order_item_id = mip.order_item_id
  WHERE mip.inserted_at >= @max_inserted_at
    AND f.creditmemo_item_id is NULL
    AND oip.product_type in ('bundle', 'configurable')
  group by mip.order_item_id
  order by stg_inserted_at;
insert into `fact_credit_memo_item_parent`
(order_item_id, creditmemo_item_id, creditmemo_id, quantity, `stg_inserted_at`, `inserted_at`)
  select
    mip.order_item_id,
    mip.creditmemo_item_id,
    mip.creditmemo_id,
    if( oip.product_type = 'bundle', 0, mip.qty) as qty,
    mip.inserted_at as stg_inserted_at,
    @db_date_time as inserted_at
  from bim_stg_creditmemo_item mip
    join bim_stg_order_item oip
      on oip.order_item_id = mip.order_item_id
    left join fact_credit_memo_item_parent f
      on f.creditmemo_item_id = mip.creditmemo_item_id
  WHERE mip.inserted_at >= @max_inserted_at
        AND f.creditmemo_item_id is NULL
        AND oip.product_type in ('bundle', 'configurable')
  group by mip.creditmemo_item_id, mip.order_item_id, mip.creditmemo_id
  order by stg_inserted_at;

-- {{IF_ANALYZE}}fact_credit_memo_item_parent{{/IF_ANALYZE}}
