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

-- POPULATE fact_order_item_parent
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from fact_order_item_parent;
insert into `fact_order_item_parent`
(order_id, order_item_id, fk_dim_complex_product,
 weight, row_weight, price, price_incl_tax, qty_ordered, `stg_inserted_at`, `inserted_at`)
  select order_id, order_item_id, fk_dim_complex_product,
    weight, row_weight, price, price_incl_tax, qty_ordered, stg_inserted_at, `inserted_at`
  from (
    -- this record will link single parent product record for all simple products
    -- all order items of simple products have parent_item_id 0.
    -- and it will be joined to 0 in order_item_id of this table
  SELECT
    0 order_id,
    0 order_item_id,
    -2 fk_dim_complex_product,
    0 weight,
    0 row_weight,
    0 price,
    0 price_incl_tax,
    0 qty_ordered,
    FROM_UNIXTIME(1) as stg_inserted_at,
    @db_date_time as inserted_at
  ) a
  where (select pk_fact_order_item_parent from `fact_order_item_parent` LIMIT 1) iS NULL
UNION
  SELECT
    op.order_id, op.order_item_id as order_item_id,
    op.product_id as fk_dim_complex_product,
    op.weight as weight,
    op.row_weight as row_weight,
    op.price as price,
    op.price_incl_tax as price_incl_tax,
    op.qty_ordered as qty_ordered,
    op.inserted_at as stg_inserted_at,
    @db_date_time as inserted_at
  from bim_stg_order_item op
    join bim_stg_order_item o
      on o.parent_item_id = op.order_item_id
    left join fact_order_item_parent f
      on f.order_item_id = op.order_item_id
  WHERE op.inserted_at >= @max_inserted_at
    AND f.pk_fact_order_item_parent IS NULL
        AND op.order_item_id is not null
  GROUP BY op.order_item_id
order by stg_inserted_at;

-- {{IF_ANALYZE}}fact_order_item_parent{{/IF_ANALYZE}}
