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

-- POPULATE map_product_attribute
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from map_product_attribute;
insert into `map_product_attribute`
(fk_dim_product, fk_dim_attribute_option, `stg_inserted_at`, `inserted_at`)
select
  p.pk_dim_product as fk_dim_product,
  CONCAT_WS(':', cast(ea.attribute_group_id as CHAR),
            cast(v.attribute_id as CHAR),
            cast(v.attribute_value as CHAR)) as fk_dim_attribute_option,
            least(v.inserted_at, ea.inserted_at) as stg_inserted_at,
            @db_date_time as inserted_at
from bim_tmp_product_attribute_values_latest v
  join dim_product p
    on v.product_id = p.product_id
  join bim_stg_entity_attribute ea
    on ea.attribute_id = v.attribute_id
    and ea.attribute_set_id = p.attribute_set_id
  left join map_product_attribute m
    on m.fk_dim_product =  p.pk_dim_product
       and m.fk_dim_attribute_option = CONCAT_WS(':', cast(ea.attribute_group_id as CHAR),
                                                 cast(v.attribute_id as CHAR),
                                                 cast(v.attribute_value as CHAR))
where GREATEST(v.inserted_at, ea.inserted_at) >= @max_inserted_at
  AND v.attribute_value <> 0 and m.pk_map_product_attribute IS NULL
order by stg_inserted_at;

-- {{IF_ANALYZE}}map_product_attribute{{/IF_ANALYZE}}
