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

-- POPULATE fact_comment
SELECT @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) FROM fact_comment;
INSERT INTO `fact_comment` (
    `comment_id`,
    `order_id`,
    `is_customer_notified`,
    `order_comment`,
    `comment_time`,
    `comment_date`,
    `stg_inserted_at`,
    `inserted_at`
)
SELECT 
    bim_status.change_id AS comment_id,
    bim_status.order_id AS order_id,
    bim_status.is_customer_notified AS is_customer_notified,
    bim_status.comment AS order_comment,
    DATE_FORMAT(STR_TO_DATE(bim_status.created_at_datetime, '%Y%m%d%H%i%s'), '%H:%i:%s') AS comment_time,
    DATE_FORMAT(STR_TO_DATE(bim_status.created_at_datetime, '%Y%m%d%H%i%s'), '%m/%d/%Y') AS comment_date,
    bim_status.inserted_at AS stg_inserted_at,
    @db_date_time as inserted_at
FROM bim_stg_order_status_history bim_status
LEFT JOIN fact_comment f ON bim_status.change_id = f.comment_id 
WHERE bim_status.comment <> "" AND bim_status.inserted_at >= @max_inserted_at AND f.comment_id is NULL;
-- END POPULATE fact_comment-- {{IF_ANALYZE}}fact_comment{{/IF_ANALYZE}}


-- {{IF_ANALYZE}}fact_comment{{/IF_ANALYZE}}
