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


-- POPULATE fact_order_item
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from fact_order_item;
insert into `fact_order_item`
(order_item_id, order_id, parent_item_id, fk_dim_product,
  ordered_product_type,
 weight, qty_ordered, price, original_price,
 tax_amount, discount_amount, discount_percent, row_total,
 row_total_incl_tax, row_weight, tax_before_discount, price_incl_tax,
 hidden_tax_amount, cost, `stg_inserted_at`, `inserted_at`)
SELECT
  o.order_item_id, o.order_id, o.parent_item_id,
  o.product_id as fk_dim_product,
  COALESCE(op.product_type, o.product_type) as ordered_product_type,
  IF (op.product_type is not null && op.product_type = 'configurable', op.weight, o.weight) as weight,
  IF (op.product_type is not null && op.product_type = 'configurable', op.qty_ordered, o.qty_ordered) as qty_ordered,
  IF (op.product_type is not null && op.product_type = 'configurable', op.price, o.price) as price,
  IF (op.product_type is not null && op.product_type = 'configurable', op.original_price, o.original_price) as original_price,
  IF (op.product_type is not null && op.product_type = 'configurable', op.tax_amount, o.tax_amount) as tax_amount,
  IF (op.product_type is not null && op.product_type = 'configurable', abs(op.discount_amount), abs(o.discount_amount)) as discount_amount,
  IF (op.product_type is not null && op.product_type = 'configurable', abs(op.discount_percent)/100, abs(o.discount_percent)/100) as discount_percent,
  IF (op.product_type is not null && op.product_type = 'configurable', op.row_total, o.row_total) as row_total,
  IF (op.product_type is not null && op.product_type = 'configurable', op.row_total_incl_tax, o.row_total_incl_tax) as row_total_incl_tax,
  IF (op.product_type is not null && op.product_type = 'configurable', op.row_weight, o.row_weight) as row_weight,
  IF (op.product_type is not null && op.product_type = 'configurable', op.tax_before_discount, o.tax_before_discount) as tax_before_discount,
  IF (op.product_type is not null && op.product_type = 'configurable', op.price_incl_tax, o.price_incl_tax) as price_incl_tax,
  IF (op.product_type is not null && op.product_type = 'configurable', op.hidden_tax_amount, o.hidden_tax_amount) as hidden_tax_amount,
  IF (o.cost is not null && o.cost <> 0, o.cost, coalesce(op.cost, 0)) as cost,
  o.inserted_at as stg_inserted_at,
  @db_date_time as inserted_at
  from bim_stg_order_item o
    left join bim_stg_order_item op
        on op.order_item_id = o.parent_item_id
    left join fact_order_item f
      on f.order_item_id = o.order_item_id
WHERE o.inserted_at >= @max_inserted_at
  AND f.pk_fact_order_item IS NULL
  AND o.product_type not in ('configurable', 'bundle')
order by stg_inserted_at;

-- {{IF_ANALYZE}}fact_order_item{{/IF_ANALYZE}}
