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

-- POPULATE fact_order_cancel_item
-- get only records that are not present in fact_order_cancel_item
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from fact_order_cancel_item;
DROP TEMPORARY TABLE IF EXISTS tmp_new_canceled_updates;
CREATE TEMPORARY TABLE tmp_new_canceled_updates
(INDEX tmp_index_oii (order_item_id), UNIQUE tmp_index_pk (pk))
    (
      -- Configurable product may only have one and only one child product.
      -- This child product is used to get canceled quantity.
      -- So, my goal is to configurable records and use their children only.
      -- But if parent is not a configurable product, I should take the parent record
      -- Bundle products on other side may have multiple child products.
      -- In both cases other facts keep only child records but the records are linked to
      -- parents for being able to use the parent in report.
      -- Measures for configured and bundle products are stored differently:
      -- bundle keeps them in child items, configurable keeps them in parent.

      -- Because of this mess with parent items, I do the following:
      -- 1. I get children of bundle products and ignore their parents
      -- 2. I get parents of configurable and ignore their children
      -- 3. I replace id of configurable record with id of a child
      --    (to be consistent and keep only child records)
      select
        o.pk,
        o.order_id,
        -- replace id of configurable record with id of a child
        -- (to be consistent and keep only child records)
        COALESCE(oi_config_child.order_item_id, o.order_item_id) as order_item_id,
        o.updated_at_datetime,
        o.qty_canceled,
        o.inserted_at
      from bim_stg_canceled_order_item o
        -- join order item info
        join bim_stg_order_item oi
          on o.order_item_id = oi.order_item_id
        -- join parent config
        left join bim_stg_order_item oi_config_parent
          on oi_config_parent.order_item_id = oi.parent_item_id
             and oi_config_parent.product_type = 'configurable'
        -- join child config
        left join bim_stg_order_item oi_config_child
          on oi_config_child.parent_item_id = oi.order_item_id
             and oi.product_type = 'configurable'
        left join fact_order_cancel_item f_conf
          on oi_config_child.order_item_id = f_conf.order_item_id
             and o.updated_at_datetime = f_conf.updated_at_datetime
        left join fact_order_cancel_item f
          on o.order_item_id = f.order_item_id
             and o.updated_at_datetime = f.updated_at_datetime
      where
        o.inserted_at >= @max_inserted_at
        -- keep children of bundle products and ignore their parents
        AND COALESCE(oi.product_type, '') <> 'bundle'
        -- keep parents of configurable and ignore their children
        and oi_config_parent.order_item_id IS NULL
        and f.order_item_id IS NULL
        and f_conf.order_item_id IS NULL
    );

DROP TEMPORARY TABLE IF EXISTS tmp_new_canceled_updates_2;
CREATE TEMPORARY TABLE tmp_new_canceled_updates_2
(INDEX tmp_index_oii (order_item_id), UNIQUE tmp_index_pk (pk))
    (select * from tmp_new_canceled_updates);
insert into `fact_order_cancel_item`
(order_item_id, order_id, qty_canceled, updated_at_datetime, `stg_inserted_at`, `inserted_at`)
  select
    o.order_item_id,
    o.order_id,
    sum(o.qty_canceled) - sum(COALESCE(f.qty_canceled, 0)) as qty_canceled_res,
    o.updated_at_datetime,
    max(o.inserted_at) as stg_inserted_at,
    @db_date_time as inserted_at
  from tmp_new_canceled_updates o
    left join tmp_new_canceled_updates_2 o2
      on o2.order_item_id = o.order_item_id
         and o2.pk > o.pk
    left join fact_order_cancel_item f
      on f.order_item_id = o.order_item_id
  where o2.pk is null
  group by o.pk, o.order_item_id, o.updated_at_datetime
  having qty_canceled_res <> 0
  order by stg_inserted_at;
DROP TEMPORARY TABLE IF EXISTS tmp_new_canceled_updates;
DROP TEMPORARY TABLE IF EXISTS tmp_new_canceled_updates_2;

-- {{IF_ANALYZE}}fact_order_cancel_item{{/IF_ANALYZE}}
