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

-- POPULATE dim_attribute_option
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from dim_attribute_option;
INSERT into `dim_attribute_option`
(pk_dim_attribute_option, attribute_id, attribute_code, attribute_name,
 attribute_group_id, attribute_group_name,
  attribute_option_label, attribute_option_value, `stg_inserted_at`, `inserted_at`)
select
  CONCAT_WS(':', cast(map.attribute_group_id as CHAR),
  cast(ao.attribute_id as CHAR),
  cast(ao.value as CHAR)) as pk_dim_attribute_option,
  a.attribute_id,
  a.attribute_code,
  a.frontend_label as attritube_name,
  map.attribute_group_id,
  g.attribute_group_name,
  ao.label as attribute_option_label,
  ao.value as attribute_option_value,
  LEAST(a.inserted_at, ao.inserted_at) as stg_inserted_at,
  @db_date_time as inserted_at
from bim_stg_product_attributes a
join bim_stg_entity_attribute map
  on map.attribute_id = a.attribute_id
join bim_stg_product_options ao
  on ao.attribute_id = a.attribute_id
join bim_stg_attribute_group g
  on g.attribute_group_id = map.attribute_group_id
LEFT join dim_attribute_option dim
  on dim.attribute_id = a.attribute_id
    and dim.attribute_group_id = g.attribute_group_id
    and dim.attribute_option_value = ao.value
where GREATEST(a.inserted_at, ao.inserted_at) >= @max_inserted_at
      AND dim.pk_dim_attribute_option IS NULL
order by stg_inserted_at;

-- {{IF_ANALYZE}}dim_attribute_option{{/IF_ANALYZE}}