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

-- POPULATE fact_shipment
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from fact_shipment;
insert into `fact_shipment`
(shipment_id, shipment_number, order_id, total_weight, total_qty, fk_shipment_date, shipment_date, shipment_time, fk_shipping_address, fk_dim_address_details, `stg_inserted_at`, `inserted_at`)
  select
    s.shipment_id,
    s.increment_id as shipment_number,
    s.order_id as order_id,
    s.total_weight as total_weight,
    s.total_qty as total_qty,
    floor(s.created_at_datetime/1000000) as fk_shipment_date,
    DATE_FORMAT(STR_TO_DATE(s.created_at_datetime, '%Y%m%d%H%i%s'), '%m/%d/%Y') as shipment_date,
    DATE_FORMAT(STR_TO_DATE(s.created_at_datetime, '%Y%m%d%H%i%s'), '%H:%i:%s') as shipment_time,
    da.pk_dim_shipping_address as fk_shipping_address,
    ad.pk_dim_address_details as fk_dim_address_details,
    s.inserted_at as stg_inserted_at,
    @db_date_time as inserted_at
  from bim_stg_shipment s
    LEFT JOIN bim_stg_order_address a
      on a.order_address_id = s.shipping_address_id
    LEFT join bim_stg_directory_country dc
      on dc.country_id = a.country_id
    LEFT join bim_stg_directory_country_region dr
      on dr.region_id = a.region_id
    LEFT join dim_shipping_address da
      on da.city = COALESCE(NULLIF(a.city, ''), 'n/a')
         and da.postcode = COALESCE(NULLIF(a.postcode, ''), 'n/a')
         and da.country_id = COALESCE(NULLIF(a.country_id, ''), '--')
         and da.country_iso2_code = COALESCE(NULLIF(dc.iso2_code, ''), '--')
         and da.country_iso3_code = COALESCE(NULLIF(dc.iso3_code, ''), 'n/a')
         and da.region_code = COALESCE(NULLIF(dr.region_code, ''), 'n/a')
         and da.region_country = COALESCE(NULLIF(dr.country_id, ''), '--')
         and da.region_default_name = COALESCE(NULLIF(dr.region_default_name, ''), 'n/a')
         and da.region_name = COALESCE(NULLIF(a.region_name, ''), 'n/a')
    LEFT JOIN dim_address_details ad
      on ad.address_id = s.shipping_address_id
    left join fact_shipment f
      on f.shipment_id = s.shipment_id
WHERE s.inserted_at >= @max_inserted_at
  AND f.pk_fact_shipment is NULL
order by stg_inserted_at;

-- {{IF_ANALYZE}}fact_shipment{{/IF_ANALYZE}}
