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

-- POPULATE fact_order
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from fact_order;
insert into `fact_order`
(order_id, order_number, discount_amount, shipping_amount,
 tax_amount, hidden_tax_amount, shipping_hidden_tax_amnt, subtotal, base_to_global_rate, total_amount, order_date, order_time, fk_order_date, fk_order_time, customer_id, email,
 email_hash, email_domain,
 fk_dim_customer_mini, fk_dim_store, fk_billing_address, fk_dim_shipping_method, `stg_inserted_at`, `inserted_at`)
select
  o.order_id,
  o.increment_id as order_number,
  abs(o.discount_amount) as discount_amount,
  o.shipping_amount,
  o.tax_amount,
  o.hidden_tax_amount,
  o.shipping_hidden_tax_amnt,
  o.subtotal,
  o.base_to_global_rate,
  o.grand_total as total_amount,
  DATE_FORMAT(STR_TO_DATE(o.created_at_datetime, '%Y%m%d%H%i%s'), '%m/%d/%Y') as order_date,
  DATE_FORMAT(STR_TO_DATE(o.created_at_datetime, '%Y%m%d%H%i%s'), '%H:%i:%s') as order_time,
  floor(o.created_at_datetime/1000000) as fk_order_date,
  o.created_at_datetime%1000000 as fk_order_time,
  o.customer_id as customer_id,
  o.customer_email as email,
  sha(o.customer_email) as email_hash,
  SUBSTRING_INDEX(SUBSTRING_INDEX(o.customer_email, '@', 2), '@', -1) AS email_domain,
  -- pk_dim_customer_mini = customer_group_id*100000+age*100+gender
  COALESCE(o.customer_group_id*100000
    + GREATEST(0, IF(c.dob_date < 18000101, 0, TIMESTAMPDIFF(YEAR, STR_TO_DATE(c.dob_date, '%Y%m%d'), STR_TO_DATE(floor(o.created_at_datetime/1000000), '%Y%m%d'))))*100
    + c.gender, 0) as fk_dim_customer_mini,
  o.store_id as fk_dim_store,
  COALESCE(da.pk_dim_billing_address, '') AS fk_billing_address,
  s.pk_dim_shipping_method as fk_dim_shipping_method,
  o.inserted_at as stg_inserted_at,
  @db_date_time as inserted_at
from bim_stg_order o
  join dim_shipping_method s
    on s.method_code = o.shipping_method
    and s.description = o.shipping_description
  left join bim_stg_customer c
    on o.customer_id = c.customer_id
  left join fact_order f
    on f.order_id = o.order_id
  left join bim_stg_order_address a ON a.order_address_id = o.billing_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_billing_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 = o.billing_address_id
WHERE o.inserted_at >= @max_inserted_at
  AND f.order_id IS NULL
order by stg_inserted_at;

-- {{IF_ANALYZE}}fact_order{{/IF_ANALYZE}}
