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

-- POPULATE dim_product
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from dim_product;
insert into `dim_product`
(pk_dim_product, product_id, attribute_set_id, attribute_set_name, product_type, sku, product_name, product_date, `stg_inserted_at`, `inserted_at`)
  (select pk_dim_product, product_id, attribute_set_id, attribute_set_name, product_type,
     sku, product_name, product_date, stg_inserted_at, `inserted_at`
   from (
          SELECT
    -3 as pk_dim_product,
    -3 as product_id,
    -3 as attribute_set_id,
            -- TODO use translator
    'Missing Product' as attribute_set_name,
    'Missing Product' as product_type,
    'Missing Product' as sku,
    'Missing Product' as product_name,
    '' as product_date,
     FROM_UNIXTIME(1) as stg_inserted_at,
     @db_date_time as inserted_at
     UNION
          SELECT
    -2 as pk_dim_product,
    -2 as product_id,
    -3 as attribute_set_id,
    'No Parent' as attribute_set_name,
    'No Parent' as product_type,
    'No Parent' as sku,
    'No Parent' as product_name,
    '' as product_date,
    FROM_UNIXTIME(1) as stg_inserted_at,
    @db_date_time as inserted_at
        ) a
  where (select product_id from `dim_product` LIMIT 1) iS NULL)
UNION
  (select
    pe.product_id as pk_dim_product,
    pe.product_id,
    pe.attribute_set_id,
    COALESCE(sas.attribute_set_name, ''),
    pe.product_type,
    pe.sku,
    pe.product_name,
    COALESCE(DATE_FORMAT(STR_TO_DATE(pe.created_at_datetime, '%Y%m%d%H%i%s'), '%m/%d/%Y'), '') as product_date,
    pe.inserted_at as stg_inserted_at,
    @db_date_time as inserted_at
  from bim_stg_product pe
    LEFT JOIN `dim_product` dim
      on dim.pk_dim_product = pe.product_id
    left JOIN bim_stg_attribute_set sas
      on pe.attribute_set_id = sas.attribute_set_id
    WHERE pe.inserted_at >= @max_inserted_at
          AND dim.pk_dim_product is NULL
  )
order by stg_inserted_at;

-- {{IF_ANALYZE}}dim_product{{/IF_ANALYZE}}
