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

-- POPULATE dim_store
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from dim_store;
insert into `dim_store`
(pk_dim_store, store_id, store_code, website_id, store_group_id, store_name,
           website_code, website_name, store_group_name, `stg_inserted_at`, `inserted_at`)
select
  s.store_id as pk_dim_store,
  s.store_id,
  s.store_code,
  s.website_id,
  s.store_group_id,
  s.store_name,
  w.code as website_code,
  w.name as website_name,
  sg.name as store_group_name,
  least(s.inserted_at, sg.inserted_at, w.inserted_at) as stg_inserted_at,
  @db_date_time as inserted_at
from bim_stg_store s
join bim_stg_store_group sg
  on s.store_group_id = sg.group_id
join bim_stg_website w
  on s.website_id = w.website_id
left join dim_store dim
  on dim.pk_dim_store = s.store_id
WHERE greatest(s.inserted_at, sg.inserted_at, w.inserted_at) >= @max_inserted_at
  AND dim.pk_dim_store IS NULL
order by stg_inserted_at;

-- {{IF_ANALYZE}}dim_store{{/IF_ANALYZE}}
