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

-- POPULATE fact_payment
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from fact_payment;
insert into `fact_payment`
(payment_id, order_id, fk_payment_method, shipping_captured, amount_paid, amount_authorized, amount_paid_online,
 amount_refunded_online, shipping_amount, amount_ordered, shipping_refunded, amount_refunded, amount_canceled, `stg_inserted_at`, `inserted_at`)
  select
    p.payment_id,
    p.order_id,
    p.method as fk_payment_method,
    p.shipping_captured,
    p.amount_paid,
    p.amount_authorized,
    p.amount_paid_online,
    p.amount_refunded_online,
    p.shipping_amount,
    p.amount_ordered,
    p.shipping_refunded,
    p.amount_refunded,
    p.amount_canceled,
    p.inserted_at as stg_inserted_at,
    @db_date_time as inserted_at
  from bim_stg_payment p
    left join fact_payment f
      on f.payment_id = p.payment_id
  WHERE p.inserted_at >= @max_inserted_at
    AND f.payment_id is NULL
  order by stg_inserted_at;

-- {{IF_ANALYZE}}fact_payment{{/IF_ANALYZE}}
