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

-- POPULATE fact_order_status_updates
SELECT @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) FROM fact_order_status_updates;
INSERT INTO `fact_order_status_updates` (
	`order_status_change_id`, 
	`order_id`, 
  `order_status`, 
	`rank`, 
	`prev_status`, 
	`next_status`, 
	`processing_time`, 
	`event_type`,
  `entity_type`,
  `status_update_time`,
  `status_update_date`,  
	`stg_inserted_at`, `inserted_at`)
SELECT 
	bg.change_id, 
	bg.order_id, 
	bg.status, 
  bg.rank, 
	COALESCE(prev.status, "") as prev_status, 
	"" AS next_status, 
	"0" AS processing_time,  
	"begin" as event_type,   
  bg.entity_type AS entity_type,
  DATE_FORMAT(STR_TO_DATE(bg.created_at_datetime, '%Y%m%d%H%i%s'), '%H:%i:%s') AS status_update_time,
  DATE_FORMAT(STR_TO_DATE(bg.created_at_datetime, '%Y%m%d%H%i%s'), '%m/%d/%Y') AS status_update_date,
	bg.stg_inserted_at,
    @db_date_time as inserted_at
FROM bim_tmp_order_status_history_rank bg
LEFT JOIN bim_tmp_order_status_history_rank prev ON bg.order_id = prev.order_id AND prev.rank = bg.rank - 1
LEFT JOIN fact_order_status_updates f ON bg.change_id = f.order_status_change_id AND f.event_type = "begin"
WHERE bg.stg_inserted_at >= @max_inserted_at AND f.order_status_change_id is NULL;
-- END POPULATE fact_order_status_updates

-- POPULATE fact_order_status_updates
INSERT INTO `fact_order_status_updates` (
	`order_status_change_id`, 
	`order_id`, 
	`order_status`, 
	`rank`, 
	`prev_status`, 
	`next_status`, 
	`processing_time`,  
	`event_type`,  
  `entity_type`,
  `status_update_time`,
  `status_update_date`,
	`stg_inserted_at`, `inserted_at`)
SELECT 
	e.change_id, 
	e.order_id, 
	e.status, 
	e.rank, 
	"" AS prev_status, 
	next.status AS next_status,
    UNIX_TIMESTAMP(STR_TO_DATE(next.created_at_datetime, '%Y%m%d%H%i%s')) - UNIX_TIMESTAMP(STR_TO_DATE(e.created_at_datetime, '%Y%m%d%H%i%s')) AS processing_time,
	"end" as event_type,
  e.entity_type AS entity_type,
  DATE_FORMAT(STR_TO_DATE(next.created_at_datetime, '%Y%m%d%H%i%s'), '%H:%i:%s') AS status_update_time,
  DATE_FORMAT(STR_TO_DATE(next.created_at_datetime, '%Y%m%d%H%i%s'), '%m/%d/%Y') AS status_update_date,
	e.stg_inserted_at,
    @db_date_time as inserted_at
FROM bim_tmp_order_status_history_rank e
JOIN bim_tmp_order_status_history_rank next ON e.order_id = next.order_id AND next.rank = e.rank + 1
LEFT JOIN fact_order_status_updates f ON e.change_id = f.order_status_change_id AND f.event_type = "end"
WHERE next.stg_inserted_at >= @max_inserted_at AND f.order_status_change_id is NULL;
-- END POPULATE fact_order_status_updates-- {{IF_ANALYZE}}fact_order_status_updates{{/IF_ANALYZE}}

-- {{IF_ANALYZE}}fact_order_status_updates{{/IF_ANALYZE}}
