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

-- POPULATE fact_invoice
SELECT @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) FROM fact_invoice;
INSERT INTO `fact_invoice`
(invoice_id, invoice_number, order_id, grand_total, tax_amount, shipping_tax_amount,
 discount_amount, subtotal_incl_tax, shipping_amount, subtotal, base_to_global_rate, fk_invoice_date, invoice_date, invoice_time,
 hidden_tax_amount, shipping_hidden_tax_amnt, shipping_incl_tax,
 fk_billing_address, fk_dim_address_details, `stg_inserted_at`, `inserted_at`)
SELECT
  i.invoice_id,
  i.increment_id AS invoice_number,
  i.order_id AS order_id,
  i.grand_total,
  i.tax_amount,
  i.shipping_tax_amount,
  abs(i.discount_amount),
  i.subtotal_incl_tax,
  i.shipping_amount,
  i.subtotal,
  i.base_to_global_rate,
  floor(i.created_at_datetime/1000000) AS fk_invoice_date,
  DATE_FORMAT(STR_TO_DATE(i.created_at_datetime, '%Y%m%d%H%i%s'), '%m/%d/%Y') AS invoice_date,
  DATE_FORMAT(STR_TO_DATE(i.created_at_datetime, '%Y%m%d%H%i%s'), '%H:%i:%s') AS invoice_time,
  i.hidden_tax_amount,
  i.shipping_hidden_tax_amnt,
  i.shipping_incl_tax,
  da.pk_dim_billing_address AS fk_billing_address,
  ad.pk_dim_address_details as fk_dim_address_details,
  i.inserted_at AS stg_inserted_at,
  @db_date_time as inserted_at
FROM bim_stg_invoice i
  LEFT JOIN bim_stg_order_address a ON a.order_address_id = i.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 = i.billing_address_id
  LEFT JOIN fact_invoice f ON f.invoice_id = i.invoice_id
WHERE i.inserted_at >= @max_inserted_at AND f.invoice_id IS NULL
ORDER BY stg_inserted_at;

-- {{IF_ANALYZE}}fact_invoice{{/IF_ANALYZE}}
