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

-- POPULATE dim_payment_method
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from dim_payment_method;
insert into `dim_payment_method`
(pk_dim_payment_method, method_code, method_name, `stg_inserted_at`, `inserted_at`)
  select pk_dim_payment_method, method_code, method_name, stg_inserted_at, @db_date_time as inserted_at
  from (
      select
        '' as pk_dim_payment_method,
        '' method_code,
        'Empty Payment Method' method_name,
        FROM_UNIXTIME(1) as stg_inserted_at
    ) a
  where (select pk_dim_payment_method from `dim_payment_method` LIMIT 1) iS NULL
UNION
  select
    s.code pk_dim_payment_method,
    s.code method_code,
    s.name method_name,
    s.inserted_at as stg_inserted_at,
    @db_date_time as inserted_at
  from bim_stg_payment_methods s
    left join dim_payment_method dim
      on dim.pk_dim_payment_method = s.code
  WHERE s.inserted_at >= @max_inserted_at
    AND dim.pk_dim_payment_method IS NULL
order by stg_inserted_at;

-- add method codes from bim_stg_payment that does not exist in the list of current payment methods (maybe it was deleted now)
insert into `dim_payment_method`
(pk_dim_payment_method, method_code, method_name, `stg_inserted_at`, `inserted_at`)
select
    used.method as pk_dim_payment_method,
    used.method as method_code,
    'Missing Payment Method' method_name,
    FROM_UNIXTIME(1) as stg_inserted_at,
    @db_date_time as inserted_at
from (
         select `method`
         from bim_stg_payment
         group by `method`) used
 left join dim_payment_method d
           on d.method_code = method
where d.method_code is null;

-- {{IF_ANALYZE}}dim_payment_method{{/IF_ANALYZE}}
