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

-- POPULATE fact_invoice_item_parent
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from fact_invoice_item_parent;
-- insert one new record for each order item id and provide qty as difference
insert into `fact_invoice_item_parent`
(invoice_item_id, invoice_id, order_item_id, quantity, `stg_inserted_at`, `inserted_at`)
  select
    -- max in this case is equivalent to first() function
    max(iip.invoice_item_id) as invoice_item_id,
    max(iip.invoice_id) as invoice_id,
    iip.order_item_id as order_item_id,
    if( oip.product_type = 'bundle',
       -- calculate difference between sum of already inserted quantity vs max quantity of new new records
       -- count functions help to deal with duplicates
        greatest(coalesce(sum(fo.quantity) * count(distinct fo.pk_fact_invoice_item_parent) / count(fo.pk_fact_invoice_item_parent), 0),
              max(iip.qty))
      - coalesce(sum(fo.quantity) * count(distinct fo.pk_fact_invoice_item_parent) / count(fo.pk_fact_invoice_item_parent), 0),
        -- for the rest of complex products just take the quantity as it is
       coalesce(max(iip.qty), 0)) as quantity,
    max(iip.inserted_at) as stg_inserted_at,
    @db_date_time as inserted_at
  from bim_stg_invoice_item iip
    join bim_stg_order_item oip
      on oip.order_item_id = iip.order_item_id
    left join fact_invoice_item_parent f
      on f.invoice_item_id = iip.invoice_item_id
    left join fact_invoice_item_parent fo
      on fo.order_item_id = iip.order_item_id
  where iip.inserted_at >= @max_inserted_at
    AND f.invoice_item_id is NULL
    AND oip.product_type in ('bundle', 'configurable')
  GROUP BY iip.order_item_id
  order by stg_inserted_at;

-- repeat the query to include extra parent item records, this time keeping the qty as 0
insert into `fact_invoice_item_parent`
(invoice_item_id, invoice_id, order_item_id, quantity, `stg_inserted_at`, `inserted_at`)
  select
    iip.invoice_item_id as invoice_item_id,
    iip.invoice_id as invoice_id,
    iip.order_item_id as order_item_id,
    if(oip.product_type = 'bundle', 0, iip.qty) as quantity,
    max(iip.inserted_at) as stg_inserted_at,
    @db_date_time as inserted_at
  from bim_stg_invoice_item iip
    join bim_stg_order_item oip
      on oip.order_item_id = iip.order_item_id
    left join fact_invoice_item_parent f
      on f.invoice_item_id = iip.invoice_item_id
    left join fact_invoice_item_parent fo
      on fo.order_item_id = iip.order_item_id
  where iip.inserted_at >= @max_inserted_at
        AND f.invoice_item_id is NULL
        AND oip.product_type in ('bundle', 'configurable')
  GROUP BY iip.invoice_item_id, iip.invoice_id, iip.order_item_id
  order by stg_inserted_at;

-- {{IF_ANALYZE}}fact_invoice_item_parent{{/IF_ANALYZE}}
