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

-- POPULATE fact_invoice_item
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from fact_invoice_item;
insert into `fact_invoice_item`
(invoice_item_id, invoice_id, order_item_id,
 price, row_total, discount_amount, tax_amount, price_incl_tax,
 cost, row_total_incl_tax, hidden_tax_amount,
 -- weee_tax_disposition, weee_tax_row_disposition,
 quantity, `stg_inserted_at`, `inserted_at`)
  select
    ii.invoice_item_id,
    ii.invoice_id,
    ii.order_item_id as order_item_id,
    IF (oip.product_type is not null && oip.product_type = 'configurable', coalesce(iip.price, 0), ii.price) as price,
    IF (oip.product_type is not null && oip.product_type = 'configurable', coalesce(iip.row_total, 0), ii.row_total) as row_total,
    IF (oip.product_type is not null && oip.product_type = 'configurable', abs(coalesce(iip.discount_amount, 0)), abs(ii.discount_amount)) as discount_amount,
    IF (oip.product_type is not null && oip.product_type = 'configurable', coalesce(iip.tax_amount, 0), ii.tax_amount) as tax_amount,
    IF (oip.product_type is not null && oip.product_type = 'configurable', coalesce(iip.price_incl_tax, 0), ii.price_incl_tax) as price_incl_tax,
    IF (ii.cost is not null && ii.cost <> 0, ii.cost, coalesce(iip.cost, 0)) as cost,
    IF (oip.product_type is not null && oip.product_type = 'configurable', coalesce(iip.row_total_incl_tax, 0), ii.row_total_incl_tax) as row_total_incl_tax,
    IF (oip.product_type is not null && oip.product_type = 'configurable', coalesce(iip.hidden_tax_amount, 0), ii.hidden_tax_amount) as hidden_tax_amount,
    IF (oip.product_type is not null && oip.product_type = 'configurable', coalesce(iip.qty, 0), ii.qty) as qty,
    ii.inserted_at as stg_inserted_at,
    @db_date_time as inserted_at
from bim_stg_invoice_item ii
join bim_stg_order_item oi
  on oi.order_item_id = ii.order_item_id
left join bim_stg_order_item oip
  on oip.order_item_id = oi.parent_item_id
left join bim_stg_invoice_item iip
  on iip.order_item_id = oi.parent_item_id
  and iip.invoice_id = ii.invoice_id
left join fact_invoice_item f
  on f.invoice_item_id = ii.invoice_item_id
where ii.inserted_at >= @max_inserted_at
  AND oi.product_type not in ('configurable', 'bundle')
  AND f.invoice_item_id is NULL
order by stg_inserted_at;

-- {{IF_ANALYZE}}fact_invoice_item{{/IF_ANALYZE}}
