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

-- POPULATE dim_salesrule
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from dim_salesrule;
insert into `dim_salesrule`
(pk_dim_salesrule, rule_id, rule_name, from_date, to_date, promotion_type, `action`, free_shipping, is_shipping_discount,
 discount_fixed, discount_percent, discount_qty, discount_step, final_rule, `stg_inserted_at`, `inserted_at`)
select
   a.rule_id pk_dim_salesrule,
   a.rule_id rule_id,
   a.rule_name rule_name,
   DATE_FORMAT(STR_TO_DATE(19000101, '%Y%m%d'), '%m/%d/%Y') as from_date,
   DATE_FORMAT(STR_TO_DATE(29990101, '%Y%m%d'), '%m/%d/%Y') as to_date,
   a.rule_name as promotion_type,
   a.rule_name as `action`,
   a.rule_name as free_shipping,
   a.rule_name as is_shipping_discount,
   0 as discount_fixed,
   0 as discount_percent,
   0 as discount_qty,
   0 as discount_step,
   0 as final_rule,
   FROM_UNIXTIME(1) `stg_inserted_at`,
   @db_date_time as inserted_at
    from (
         SELECT -1 as rule_id, 'Missing Rule' as rule_name
      UNION
         SELECT -2 as rule_id, 'No Rule' as rule_name
    ) a
    where (select rule_id from dim_salesrule LIMIT 1) iS NULL
UNION
select
  r.rule_id as pk_dim_salesrule,
  r.rule_id,
  r.name as rule_name,
  DATE_FORMAT(STR_TO_DATE(IF(r.from_date = 0, 19000101, r.from_date), '%Y%m%d'), '%m/%d/%Y') as from_date,
  DATE_FORMAT(STR_TO_DATE(IF(r.to_date = 0, 29990101, r.to_date), '%Y%m%d'), '%m/%d/%Y') as to_date,
  COALESCE(rt.coupon_type_name, 'n/a') as `promotion_type`,
  COALESCE(ra.simple_action_name, 'n/a') as `action`,
  COALESCE(rs.free_shipping_type_name, 'n/a') as free_shipping,
  -- TODO translate with magento
  IF(r.apply_to_shipping = 1, 'Shipping Discount', 'Product Discount') as is_shipping_discount,
  -- TODO use magento constant for the string value
  IF(r.simple_action = 'by_percent', 0, abs(r.discount_amount)) as discount_fixed,
  IF(r.simple_action = 'by_percent', abs(r.discount_amount), 0)/100 as discount_percent,
  r.discount_qty,
  r.discount_step,
  r.stop_rules_processing as final_rule,
  r.inserted_at as stg_inserted_at,
  @db_date_time as inserted_at
  from bim_stg_salesrule r
  left join bim_stg_salesrule_action ra
    on ra.simple_action = r.simple_action
  left join bim_stg_salesrule_type rt
    on rt.coupon_type = r.coupon_type
  left join bim_stg_salesrule_shipping rs
    on rs.free_shipping_type_id = r.simple_free_shipping
  left join dim_salesrule dim
    on dim.rule_id = r.rule_id
  where r.inserted_at >= @max_inserted_at
    AND dim.rule_id IS NULL;

-- {{IF_ANALYZE}}dim_salesrule{{/IF_ANALYZE}}
