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

-- POPULATE dim_shipping_address
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from dim_shipping_address;
insert ignore into `dim_shipping_address`
(pk_dim_shipping_address, country_iso2_code, country_iso3_code, region_name, country_id,
 region_code, region_default_name, region_country, city, postcode, `stg_inserted_at`, `inserted_at`)
  select sha1(concat_ws('|', country_id, country_iso2_code, country_iso3_code,
                        region_name, region_code, region_default_name, region_country,
                        city, postcode)) as pk_dim_shipping_address,
    g.*
  from (
    SELECT
      COALESCE(NULLIF(dc.iso2_code, ''), '--')                  AS country_iso2_code,
      COALESCE(NULLIF(dc.iso3_code, ''), 'n/a')                 AS country_iso3_code,
      COALESCE(NULLIF(ca.region_name, ''), 'n/a')         AS region_name,
      COALESCE(NULLIF(ca.country_id, ''), '--')                 AS country_id,
      COALESCE(NULLIF(dr.region_code, ''), 'n/a')         AS region_code,
      COALESCE(NULLIF(dr.region_default_name, ''), 'n/a') AS region_default_name,
      COALESCE(NULLIF(dr.country_id, ''), '--')                 AS region_country,
      COALESCE(NULLIF(ca.city, ''), 'n/a')                AS city,
      COALESCE(NULLIF(ca.postcode, ''), 'n/a')            AS postcode,
      max(ca.inserted_at) as stg_inserted_at,
      @db_date_time as inserted_at
    FROM bim_stg_order_address ca
      LEFT JOIN bim_stg_directory_country dc
        ON ca.country_id = dc.country_id
      LEFT JOIN bim_stg_directory_country_region dr
        ON ca.region_id = dr.region_id
      LEFT JOIN `dim_shipping_address` dim
        ON dim.city = COALESCE(NULLIF(ca.city, ''), 'n/a')
           AND dim.postcode = COALESCE(NULLIF(ca.postcode, ''), 'n/a')
           AND dim.country_id = COALESCE(NULLIF(ca.country_id, ''), '--')
           AND dim.country_iso2_code = COALESCE(NULLIF(dc.iso2_code, ''), '--')
           AND dim.country_iso3_code = COALESCE(NULLIF(dc.iso3_code, ''), 'n/a')
           AND dim.region_code = COALESCE(NULLIF(dr.region_code, ''), 'n/a')
           AND dim.region_country = COALESCE(NULLIF(dr.country_id, ''), '--')
           AND dim.region_default_name = COALESCE(NULLIF(dr.region_default_name, ''), 'n/a')
           AND dim.region_name = COALESCE(NULLIF(ca.region_name, ''), 'n/a')
    WHERE ca.inserted_at >= @max_inserted_at
          AND dim.pk_dim_shipping_address IS NULL
    group by  COALESCE(NULLIF(ca.city, ''), 'n/a'),
           COALESCE(NULLIF(ca.postcode, ''), 'n/a'),
           COALESCE(NULLIF(ca.country_id, ''), '--'),
           COALESCE(NULLIF(dc.iso2_code, ''), '--'),
           COALESCE(NULLIF(dc.iso3_code, ''), 'n/a'),
           COALESCE(NULLIF(dr.region_code, ''), 'n/a'),
           COALESCE(NULLIF(dr.country_id, ''), '--'),
           COALESCE(NULLIF(dr.region_default_name, ''), 'n/a'),
           COALESCE(NULLIF(ca.region_name, ''), 'n/a')
  ) g
order by stg_inserted_at;

-- {{IF_ANALYZE}}dim_shipping_address{{/IF_ANALYZE}}
