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

-- POPULATE dim_category
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from dim_category;
insert into `dim_category`
(pk_dim_category, category_id, category_name, root_category, sub_category_1, sub_category_2, sub_category_3, sub_category_4,
 root_category_id, sub_category_1_id, sub_category_2_id, sub_category_3_id, sub_category_4_id,
 parent_id, path, position, level, `stg_inserted_at`, `inserted_at`)
select
  c.category_id as pk_dim_category,
  c.category_id,
  c.category_name,
  c1.category_name as root_category,
  c2.category_name as subcategory_1,
  c3.category_name as subcategory_2,
  c4.category_name as subcategory_3,
  c5.category_name as subcategory_4,
  c1.category_id as root_category_id,
  c2.category_id as subcategory_1_id,
  c3.category_id as subcategory_2_id,
  c4.category_id as subcategory_3_id,
  c5.category_id as subcategory_4_id,
  c.parent_category_id as parent_id,
  c.path,
  c.position,
  c.level,
  c.inserted_at as stg_inserted_at,
  @db_date_time as inserted_at
from bim_stg_category c
  join bim_stg_category c1
    on substring_index(c.`path`,'/',1) = c1.category_id
  join bim_stg_category c2
    on substring_index(substring_index(c.`path`,'/',2),'/',-1) = c2.category_id
  join bim_stg_category c3
    on substring_index(substring_index(c.`path`,'/',3),'/',-1) = c3.category_id
  join bim_stg_category c4
    on substring_index(substring_index(c.`path`,'/',4),'/',-1) = c4.category_id
  join bim_stg_category c5
    on substring_index(substring_index(c.`path`,'/',5),'/',-1) = c5.category_id
  left join dim_category dim
    on c.category_id = dim.pk_dim_category
WHERE c.inserted_at >= @max_inserted_at
      AND dim.pk_dim_category is NULL
order by stg_inserted_at;

-- {{IF_ANALYZE}}dim_category{{/IF_ANALYZE}}