Untitled

#== 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" );