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

-- POPULATE dim_default_billing_address
SELECT @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) FROM dim_default_billing_address;
INSERT INTO dim_default_billing_address
(
  address_id,
  firstname,
  lastname,
  middlename,
  prefix,
  suffix,
  company_name,
  street,
  email,
  telephone,
  vat_id,
  stg_inserted_at,
  inserted_at
)
select
    -1 as `address_id`,
    'n/a' as `firstname`,
    'n/a' as `lastname`,
    'n/a' as `middlename`,
    'n/a' as `prefix`,
    'n/a' as `suffix`,
    'n/a' as `company_name`,
    'n/a' as `street`,
    'n/a' as `email`,
    'n/a' as `telephone`,
    'n/a' as `vat_id`,
    FROM_UNIXTIME(1) AS `stg_inserted_at`,
    @db_date_time as inserted_at
where (select address_id from dim_default_billing_address LIMIT 1) iS NULL
UNION
select
  s.customer_address_id as `address_id`,
  s.`firstname`,
  s.`lastname`,
  s.`middlename`,
  s.`prefix`,
  s.`suffix`,
  s.`company` as `company_name`,
  s.`street`,
  s.`email`,
  s.`telephone`,
  s.`vat_id`,
  s.`inserted_at` AS `stg_inserted_at`,
  @db_date_time as inserted_at
from bim_stg_customer_address s
     left join dim_default_billing_address dim on dim.address_id = s.customer_address_id
where s.inserted_at >= @max_inserted_at AND dim.address_id is null;

-- {{IF_ANALYZE}}dim_default_billing_address{{/IF_ANALYZE}}