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

-- POPULATE map_order_state
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from map_order_state;
insert into `map_order_state`
(state_number, order_id, `stg_inserted_at`, `inserted_at`)
select
  q.state_number - coalesce(cur_state_number, 0),
  q.order_id,
  q.inserted_at as stg_inserted_at,
  @db_date_time as inserted_at
from (
  select
    s.pk_dim_order_state as state_number,
    o.order_id,
    sum(m.state_number) as cur_state_number,
    o.inserted_at as inserted_at
  from bim_stg_order_state o
    left join bim_stg_order_state o2
      on o.order_id = o2.order_id
         and o2.pk > o.pk
    left join map_order_state m
      on m.order_id = o.order_id
    left join dim_order_state s
      on s.state_name = o.state
  WHERE o.inserted_at >= @max_inserted_at
    AND o2.order_id IS NULL
    AND o2.pk is NULL
  GROUP BY s.pk_dim_order_state, o.order_id
) q
WHERE q.state_number - coalesce(cur_state_number, 0) <> 0
order by stg_inserted_at;

-- {{IF_ANALYZE}}map_order_state{{/IF_ANALYZE}}
