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

-- POPULATE fact_credit_memo_item
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from fact_credit_memo_item;
insert into `fact_credit_memo_item`
(order_item_id, creditmemo_item_id, creditmemo_id, row_total, price, price_incl_tax,
 discount_amount, tax_amount, hidden_tax_amount,
 cost, quantity, `stg_inserted_at`, `inserted_at`)
  select
    mi.order_item_id,
    mi.creditmemo_item_id,
    mi.creditmemo_id,
    coalesce(IF (oip.product_type is not null && oip.product_type = 'configurable', mip.row_total, mi.row_total), 0) as row_total,
    coalesce(IF (oip.product_type is not null && oip.product_type = 'configurable', mip.price, mi.price), 0) as price,
    coalesce(IF (oip.product_type is not null && oip.product_type = 'configurable', mip.price_incl_tax, mi.price_incl_tax), 0) as price_incl_tax,
    coalesce(IF (oip.product_type is not null && oip.product_type = 'configurable', abs(mip.discount_amount), abs(mi.discount_amount)), 0) as discount_amount,
    coalesce(IF (oip.product_type is not null && oip.product_type = 'configurable', mip.tax_amount, mi.tax_amount), 0) as tax_amount,
    coalesce(IF (oip.product_type is not null && oip.product_type = 'configurable', mip.hidden_tax_amount, mi.hidden_tax_amount), 0) as hidden_tax_amount,
    coalesce(IF (oi.cost is not null && oi.cost <> 0, oi.cost, oip.cost), 0) as cost,
    coalesce(IF (oip.product_type is not null && oip.product_type = 'configurable', mip.qty, mi.qty), 0) as qty,
    mi.inserted_at as stg_inserted_at,
    @db_date_time as inserted_at
  from bim_stg_creditmemo_item mi
    join bim_stg_order_item oi
      on oi.order_item_id = mi.order_item_id
    left join bim_stg_order_item oip
      on oip.order_item_id = oi.parent_item_id
    left join bim_stg_creditmemo_item mip
      on mip.creditmemo_id = mi.creditmemo_id
         and mip.order_item_id = oi.parent_item_id
left join fact_credit_memo_item f
  on f.creditmemo_item_id = mi.creditmemo_item_id
WHERE mi.inserted_at >= @max_inserted_at
  AND oi.product_type not in ('configurable', 'bundle')
  AND f.creditmemo_item_id is NULL
order by stg_inserted_at;

-- {{IF_ANALYZE}}fact_credit_memo_item{{/IF_ANALYZE}}
