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

-- POPULATE dim_shipping_method
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from dim_shipping_method;
insert into `dim_shipping_method`
(pk_dim_shipping_method, carrier_code, shipping_method, method_code, description, `stg_inserted_at`, `inserted_at`)
  select
      s.pk_dim_shipping_method,
      s.carrier_code,
      s.shipping_method,
      s.method_code,
      s.description,
      s.inserted_at as stg_inserted_at,
      @db_date_time as inserted_at
  from (
    SELECT
      min(o.order_id) as pk_dim_shipping_method,
      SUBSTRING_INDEX(o.shipping_method, '_', 1) AS carrier_code,
      SUBSTRING_INDEX(SUBSTRING_INDEX(o.shipping_method, '_', 2), '_', -1)  AS shipping_method,
      o.shipping_method  AS method_code,
      o.shipping_description as description,
      max(o.inserted_at) as inserted_at
    FROM bim_stg_order o
      where o.inserted_at >= @max_inserted_at
   GROUP BY o.shipping_method,
     o.shipping_description
  ) s
  left join dim_shipping_method dim
    on dim.method_code = s.method_code
    and dim.description = s.description
  WHERE dim.pk_dim_shipping_method is NULL
order by stg_inserted_at;

-- {{IF_ANALYZE}}dim_shipping_method{{/IF_ANALYZE}}
