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

-- POPULATE DIM_CUSTOMER
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from dim_customer;
-- Insert records into dim_customer table
INSERT into `dim_customer` (`pk_dim_customer`,
                            `customer_id`,
                            `website_id`,
                            `store_id`,
                            `email`,
                            `email_hash`,
                            `is_active`,
                            `created_in_store`,
                            `prefix`,
                            `first_name`,
                            `middle_name`,
                            `last_name`,
                            `suffix`,
                            `vat_number`,
                            `registration_date`,
                            `registration_time`,
                            `group_code`,
                            `fk_dim_default_billing_address`,
                            `fk_dim_default_shipping_address`,
                            `stg_inserted_at`,
                            `inserted_at`)
-- add row that contains -3 as customer_id for missing customer record
select a.customer_id as pk, a.customer_id,
 0 as `website_id`,
 0 as `store_id`,
 'missing.customer@web.com' as `email`,
 'Missing Customer' as `email_hash`,
 0 as `is_active`,
 'Missing Customer' as `created_in_store`,
 'Missing Customer' as `prefix`,
 'Missing Customer' as `first_name`,
 'Missing Customer' as `middle_name`,
 'Missing Customer' as `last_name`,
 'Missing Customer' as `suffix`,
 'Missing Customer' as `vat_number`,
 '01/01/1970' as registration_date,
 '00:00:00' as registration_time,
 'Missing Customer Group' as group_code,
 0 as fk_dim_default_billing_address,
 0 as fk_dim_default_shipping_address,
 FROM_UNIXTIME(1) as stg_inserted_at, @db_date_time as inserted_at from (SELECT -3 as customer_id) a
  where (select customer_id from dim_customer LIMIT 1) iS NULL
UNION
(select c.customer_id as pk, c.customer_id,
 c.`website_id`,
 c.`store_id`,
 c.`email`,
 sha(c.`email`) as `email_hash`,
 c.`is_active`,
 c.`created_in` as `created_in_store`,
 c.`prefix`,
 c.`firstname` as `first_name`,
 c.`middlename` as `middle_name`,
 c.`lastname` as `last_name`,
 c.`suffix`,
 c.`taxvat` as vat_number,
 DATE_FORMAT(STR_TO_DATE(c.created_at_datetime, '%Y%m%d%H%i%s'), '%m/%d/%Y') as registration_date,
 DATE_FORMAT(STR_TO_DATE(c.created_at_datetime, '%Y%m%d%H%i%s'), '%H:%i:%s') as registration_time,
 cg.`customer_group_code` as `group_code`,
 c.default_billing_address_id as fk_dim_default_billing_address,
 c.default_shipping_address_id as fk_dim_default_shipping_address,
 c.inserted_at as stg_inserted_at,
 @db_date_time as inserted_at
from bim_stg_customer as c
  left join dim_customer dim
    on dim.customer_id = c.customer_id
  left join bim_stg_customer_group as cg
      on c.group_id = cg.customer_group_id
  where c.inserted_at >= @max_inserted_at
    AND dim.customer_id IS NULL)
order by stg_inserted_at;

-- {{IF_ANALYZE}}dim_customer{{/IF_ANALYZE}}