#== XENDIT
SELECT
a.log_reference_id,
a.log_pg_name,
a.log_timestamp,
a.log_response
FROM
`prod-datarangers.galaxy_stg.hades_log__credit_card`a
WHERE
1=1
AND a.log_timestamp BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR)
AND a.log_service_name = 'create_charge'
AND JSON_EXTRACT(a.log_response, '$.status') LIKE '%CAPTURED%'
AND a.log_pg_name = 'xendit'
AND EXISTS ( SELECT DISTINCT lpn.reference_id FROM `prod-datarangers.galaxy_stg.hades_log__payment_notification` lpn WHERE lpn.reference_id = a.log_reference_id AND lpn.status != "00" ) UNION ALL
#== NICEPAY
SELECT
a.log_reference_id,
a.log_pg_name,
a.log_timestamp,
a.log_response
FROM
`prod-datarangers.galaxy_stg.hades_log__credit_card` a
WHERE
1=1
AND a.log_timestamp BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR)
AND a.log_service_name = 'payment_notification'
AND JSON_EXTRACT(a.log_response, '$.result') LIKE '%SUCCESS%'
AND a.log_pg_name = 'nicepay'
AND EXISTS ( SELECT DISTINCT lpn.reference_id FROM `prod-datarangers.galaxy_stg.hades_log__payment_notification` lpn WHERE lpn.reference_id = a.log_reference_id AND lpn.status != "00" ) UNION ALL
#==2C2P
SELECT
a.log_reference_id,
a.log_pg_name,
a.log_timestamp,
a.log_response
FROM
`prod-datarangers.galaxy_stg.hades_log__credit_card` a
WHERE
1=1
AND a.log_timestamp BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR)
AND a.log_service_name = '2c2p_payment_response' -- 2C2P Payment Response
AND JSON_EXTRACT(a.log_response, '$.failReason') LIKE '%Approved%'
AND a.log_pg_name = '2c2p'
AND EXISTS ( SELECT DISTINCT lpn.reference_id FROM `prod-datarangers.galaxy_stg.hades_log__payment_notification` lpn WHERE lpn.reference_id = a.log_reference_id AND lpn.status != "00" ) UNION ALL
#==STRIPE
SELECT
a.log_reference_id,
a.log_pg_name,
a.log_timestamp,
a.log_response
FROM
`prod-datarangers.galaxy_stg.hades_log__credit_card` a
WHERE
1=1
AND a.log_timestamp BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR)
AND a.log_service_name = 'payment_notification'
AND a.log_response = 'SUCCESS'
AND a.log_pg_name = 'stripe'
AND EXISTS ( SELECT DISTINCT lpn.reference_id FROM `prod-datarangers.galaxy_stg.hades_log__payment_notification` lpn WHERE lpn.reference_id = a.log_reference_id AND lpn.status != "00" );