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

-- POPULATE fact_credit_memo
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from fact_credit_memo;
insert into `fact_credit_memo`
(creditmemo_id, creditmemo_number, order_id, grand_total, subtotal, subtotal_incl_tax,
 tax_amount, shipping_amount, shipping_tax_amount, discount_amount, base_to_global_rate,
 adjustment, adjustment_positive, adjustment_negative, credit_memo_date, credit_memo_time,
 hidden_tax_amount, shipping_hidden_tax_amnt, shipping_incl_tax,
 `stg_inserted_at`, `inserted_at`)
  select
    m.creditmemo_id,
    m.increment_id as creditmemo_number,
    m.order_id,
    m.grand_total,
    m.subtotal,
    m.subtotal_incl_tax,
    m.tax_amount,
    m.shipping_amount,
    m.shipping_tax_amount,
    abs(m.discount_amount) as discount_amount,
    m.base_to_global_rate,
    m.adjustment,
    m.adjustment_positive,
    m.adjustment_negative,
    DATE_FORMAT(STR_TO_DATE(m.created_at_datetime, '%Y%m%d%H%i%s'), '%m/%d/%Y') as credit_memo_date,
    DATE_FORMAT(STR_TO_DATE(m.created_at_datetime, '%Y%m%d%H%i%s'), '%H:%i:%s') as credit_memo_time,
    m.hidden_tax_amount,
    m.shipping_hidden_tax_amnt,
    m.shipping_incl_tax,
    m.inserted_at as stg_inserted_at,
    @db_date_time as inserted_at
  from bim_stg_creditmemo m
left join fact_credit_memo f
  on f.creditmemo_id = m.creditmemo_id
WHERE m.inserted_at >= @max_inserted_at
  AND f.creditmemo_id is NULL
order by stg_inserted_at;

-- {{IF_ANALYZE}}fact_credit_memo{{/IF_ANALYZE}}
