payment error

--create table pmt_daily_success_rate_full as
WITH
payment_raw AS
(
    SELECT  payment_id
            ,channel_ref2
            ,transaction_id
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.params.checkout.checkout_id') AS checkout_id
            ,DATE(from_unixtime(ctime-3600)) AS payment_date
            ,amount/100000 AS checkout_amount
            ,channel_id
            ,CASE channel_id WHEN 5002601 THEN 'Shopee Pay Later' WHEN 5000101 THEN 'Cybersource Installment' WHEN 5001300 THEN 'Shopee Balance' WHEN 5001800 THEN 'ShopeePay Wallet' WHEN 5002900 THEN 'ShopeePay GIRO' WHEN 5000102 THEN 'Cybersource' WHEN 5006400 THEN 'WechatPay' WHEN 5006400 THEN 'WechatPay' WHEN 5002800 THEN 'Online Banking' WHEN 5001600 THEN 'Payoneer VN' WHEN 5003200 THEN 'IS PAYOUT' END spm_channel_name
            --  else cast(channel_id as string)
            ,CASE   WHEN STATUS = 0 THEN 'INITIAL'
                    WHEN STATUS = 2 THEN 'PAYMENT_INIT'
                    WHEN STATUS = 4 THEN 'AUTHORIZE'
                    WHEN STATUS = 6 THEN 'USER_PROCESSING'
                    WHEN STATUS = 8 THEN 'PENDING'
                    WHEN STATUS = 9 THEN 'BINDING'
                    WHEN STATUS = 10 THEN 'BLOCKED'
                    WHEN STATUS = 14 THEN 'RECONCILING'
                    WHEN STATUS = 16 THEN 'LOCAL_OPS_PROCESSING'
                    WHEN STATUS = 17 THEN 'SSG_BLOCKED'
                    WHEN STATUS = 20 THEN 'SUCCESS'
                    WHEN STATUS = 22 THEN 'FAILED'
                    WHEN STATUS = 24 THEN 'LATE_SUCCESS'
                    WHEN STATUS = 26 THEN 'SUCCESS_BUT_REJECTED_BY_SERVER'
                    WHEN STATUS = 40 THEN 'OVERPAID'
                    WHEN STATUS = 50 THEN 'CANCELLED'
                    WHEN STATUS = 51 THEN 'CANCEL_READY'
                    WHEN STATUS = 52 THEN 'VOID READY'
                    WHEN STATUS = 54 THEN 'EXPIRED'
                    WHEN STATUS = 100 THEN 'FRAUD'
                    WHEN STATUS = 999 THEN 'REJECTED'
                    ELSE CAST(STATUS AS string)
             END AS payment_status
            -- CARD NUMBER --
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.cybs_response.req_card_number') AS card_number_1
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.channel_item.card_number') AS card_number_2
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.card_number') AS card_number_3
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.params.channel_item_option_info.credit_card_data.card_number') AS card_number_4
            -- BANK NAME
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.params.channel_item_option_info.credit_card_data.bank_name') AS bank_name_1
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.params.channel_item_option_info.option_info') AS option_info
            -- CYBS REASON CODE & MESSAGE
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.commit_log.reason_code_text') AS payment_responsetext1
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.notify_log.reason_code_text') AS payment_responsetext2
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.cybs_response.message') AS cybs_message
            -- AIRPAY ERROR & MESSAGE --
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.charge_response.errorInformation.reason') AS error_reason1
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.charge_response.reason') AS error_reason2
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.update_response.reason_code') AS error_reason3
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.fail_reason') AS error_reason4
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.pay_result.fail_reason') AS error_reason5
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.otp_result.fail_reason') AS error_reason6
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.pay_result.error') AS error_reason7
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.otp_required_pay_result.error') AS error_reason8
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.otp_require_redirect_pay_result.error') AS error_reason9
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.pay_result.result') AS airpay_error1
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.otp_required_pay_result.result') AS airpay_error2
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.otp_require_redirect_pay_result.result') AS airpay_error3
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.charge_response.errorInformation.message') AS error_message1
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.charge_response.message') AS error_message2
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.update_response.reason_code_text') AS error_message3
            -- VN Airpay iBanking ERROR --
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.notify_request_params.message') AS ibank_error_message1
            ,get_json_object(decode(extra_data, 'UTF-8'), '$.check_txn_response.message') AS ibank_error_message2
    FROM    shopeepay.shopee_payment_module_payment_vn_db__payment_v2_tab__vn_continuous_s0_live p
    WHERE   1 = 1
      AND   payment_type = 4
      AND   ctime >= CAST(to_unix_timestamp(date_trunc('month', CURRENT_DATE - INTERVAL '120' DAY)) AS BIGINT) + 3600
      AND   channel_id IN (5001800, 5002900, 5002601, 5011000)
)
--select * from payment_raw;
-- GIRO ERROR
,
--with
giro_error AS
(
    SELECT  id
            ,phone_number
            ,reply_error_code
            ,response_code
    FROM    shopeepay.giro_bank__bank_subscribe_tab__vn_continuous_s0_live
    WHERE   1 = 1
      AND   created_at >= date_trunc('month', CURRENT_DATE - INTERVAL '120' DAY)
)
,giro_bank AS
(
    SELECT  g.id
            ,g.phone_number
            ,g.client_txn_id    --channel_ref2
            ,REPLACE(split(g.description, '-', 2)[1], substr(split(description, '-', 2)[1], -13), '') AS bank_name_4
            ,e.reply_error_code
            ,e.response_code
    FROM    shopeepay.giro_bank__giro_txn_tab__vn_continuous_s0_live g
    LEFT JOIN    giro_error e
    ON      g.id = e.id
     -- and   g.phone_number = e.phone_number
      AND   g.created_at >= date_trunc('month', CURRENT_DATE - INTERVAL '120' DAY)
      AND   (
                    substr(g.description, 1, 6) = 'AirPay'
                OR  substr(g.description, 1, 9) = 'ShopeePay'
            )
    WHERE   1 = 1
      AND   created_at >= date_trunc('month', CURRENT_DATE - INTERVAL '120' DAY)
      AND   g.created_at >= date_trunc('month', CURRENT_DATE - INTERVAL '120' DAY)
      AND   (
                    substr(g.description, 1, 6) = 'AirPay'
                OR  substr(g.description, 1, 9) = 'ShopeePay'
            )
)
--select * from giro_bank;
,bank_option AS     -- for bank name
(
    SELECT  DISTINCT option_id
            ,name
    FROM    shopeepay.shopee_payment_module_vn_db__payment_channel_option_tab__vn_daily_s0_live
)
,provision AS
(
    SELECT  entity_id
            ,transaction_id
            ,CASE   WHEN channel_id = 5005601 THEN 'Foody Order'
                    WHEN channel_id = 5000301 THEN 'Digital Product'
                    WHEN channel_id = 5000101 THEN 'Shopee Checkout'
                    WHEN channel_id = 5008101 THEN 'Seller Center SVS Order'
                    WHEN channel_id = 5006601 THEN 'Shopee Play Order'
                    WHEN channel_id = 500740131 THEN 'Insurance General'
             END AS provision
    FROM    shopeepay.shopee_payment_module_provision_vn_db__provision_v2_tab__vn_continuous_s0_live
    WHERE   1 = 1
      AND   ctime >= CAST(to_unix_timestamp(date_trunc('month', CURRENT_DATE - INTERVAL '120' DAY)) AS BIGINT) + 3600
      AND   channel_id IN (5000101) -- MKP only
)
,bin_map AS
(
    SELECT  DISTINCT *
    FROM    vnbi_tracking.shopee_payment_module_bin_bank_card_type
)
,payment AS
(
    SELECT  s.payment_id
            ,s.spm_channel_name
            ,s.transaction_id
            ,s.checkout_id
            ,v.entity_id
            ,s.payment_date
            ,s.payment_status
            ,s.checkout_amount
            ,v.provision
            ,COALESCE(card_number_1, card_number_2, card_number_3, card_number_4) AS card_number
            ,substr(COALESCE(card_number_1, card_number_2, card_number_3, card_number_4), 1, 6) AS bin
            ,COALESCE(g.bank_name_4, op.name, b.bank_name_2, s.bank_name_1) AS bank_name
            ,CASE b.brand WHEN 'NONE' THEN 'VISA' ELSE b.brand END brand
            -- CYBERSOURCE ERROR
            ,COALESCE(s.payment_responsetext1, s.payment_responsetext2) AS payment_responsetext
            ,s.cybs_message
            -- SHOPEEPAY ERROR
            ,COALESCE(error_reason1, error_reason2, error_reason3, error_reason4, error_reason5, error_reason6, error_reason7, error_reason8, error_reason9) AS error_reason
            ,COALESCE(error_message1, error_message2, error_message3) AS error_message
            ,g.reply_error_code
            ,g.response_code
    FROM    payment_raw s
    JOIN    provision v
    ON      s.transaction_id = v.transaction_id
    LEFT JOIN bank_option op
    ON      s.option_info = CAST(op.option_id AS string)
    LEFT JOIN bin_map b
    ON      substr(COALESCE(card_number_1, card_number_2, card_number_3, card_number_4), 1, 6) = b.bin
    LEFT JOIN giro_bank g
    ON      s.channel_ref2 = CAST(g.client_txn_id AS string)
      AND   s.channel_id IN (5002900, 5001800)
    WHERE   1 = 1
)
,joined AS
(
    SELECT  p.payment_id
            ,p.provision
            ,p.spm_channel_name
            ,p.transaction_id
            ,p.checkout_id
            --  ,o.tenor
            ,p.payment_date
            ,p.payment_status
            ,p.checkout_amount
            ,p.bin
            ,p.brand
            --  ,p.card_number
            --  ,p.expiry_date
            ,CASE   WHEN p.bank_name LIKE '%INDOVINA%' THEN 'Indovina Bank'
                    WHEN p.bank_name IN ('Ngân hàng TNHH Indovina - Indovina Bank Limited') THEN 'Indovina Bank'
                    WHEN p.bank_name LIKE '%AN BINH C%' THEN 'ABBank'
                    WHEN p.bank_name LIKE '%ACB%' THEN 'ACB'
                    WHEN p.bank_name LIKE '%VIETNAM BANK FOR AGRICULTURE%' THEN 'AgriBank'
                    WHEN p.bank_name LIKE '%BIDV%' THEN 'BIDV'
                    WHEN p.bank_name LIKE '%ĐẦU TƯ VÀ PHÁT TRIỂN VIỆT NAM%' THEN 'BIDV'
                    WHEN p.bank_name LIKE '%CIMB%' THEN 'CIMB'
                    WHEN p.bank_name LIKE '%CITIBANK%' THEN 'Citibank'
                    WHEN p.bank_name IN ('DONGA COMMERCIAL JSB') THEN 'DongABank'
                    WHEN p.bank_name LIKE '%VIETNAM EXPORT IMPORT%' THEN 'EximBank'
                    WHEN p.bank_name LIKE 'FE%CREDIT%' THEN 'FE Credit'
                    WHEN p.bank_name LIKE 'HO CHI MINH CITY DEVELOPMENT%' THEN 'HDBank'
                    WHEN p.bank_name IN ('Ho Chi Minh City Development Commercial Joint Stock Bank', 'Ho Chi Minh City Development Joint Stock Commercial Bank') THEN 'HDBank'
                    WHEN p.bank_name LIKE 'HDSAISON' THEN 'HDSaison'
                    WHEN p.bank_name LIKE 'HOME CREDIT%' THEN 'Home Credit'
                    WHEN p.bank_name IN ('Home Credit Vietnam Finance Company Limited') THEN 'Home Credit'
                    WHEN p.bank_name LIKE 'HONG LEONG BANK%' THEN 'HONG LEONG '
                    WHEN p.bank_name LIKE '%HONGKONG AND SHANGHAI BANKING%' THEN 'HSBC'
                    WHEN p.bank_name LIKE 'HSBC%' THEN 'HSBC'
                    WHEN p.bank_name LIKE 'KIEN LONG COMMERCIAL%' THEN 'Kien Long Bank'
                    WHEN p.bank_name LIKE 'KIEN LONG%' THEN 'Kien Long Bank'
                    WHEN p.bank_name IN ('Kien Long Commercial Joint Stock Bank') THEN 'Kien Long Bank'
                    WHEN p.bank_name LIKE 'MILITARY COMMERCIAL%' THEN 'MB Bank'
                    WHEN p.bank_name LIKE 'MS BANK%' THEN 'MSB'
                    WHEN p.bank_name LIKE 'MSB%' THEN 'MSB'
                    WHEN p.bank_name LIKE 'VIETNAM MARITIME COMMERCIAL%' THEN 'MSB'
                    WHEN p.bank_name LIKE 'NAM%ABANK%' THEN 'NamABank'
                    WHEN p.bank_name LIKE 'NAM%Á%' THEN 'NamABank'
                    WHEN p.bank_name IN ('National Citizen Commercial Joint Stock Bank') THEN 'NCB'
                    WHEN p.bank_name IN ('Ocean Commercial Joint Stock Bank') THEN 'OceanBank'
                    WHEN p.bank_name IN ('Orient Commercial Joint Stock Bank') THEN 'OCB'
                    WHEN p.bank_name LIKE '%PETROLIMEX%' THEN 'PGBank'
                    WHEN p.bank_name IN ('NH TMCP Xăng dầu Petrolimex - Petrolimex Group Commercial Joint Stock Bank (PGB)') THEN 'PGBank'
                    WHEN p.bank_name LIKE ('%Dai%Chung%') THEN 'PVComBank'
                    WHEN p.bank_name LIKE '%SAIGON THUONG TIN%' THEN 'Sacombank'
                    WHEN p.bank_name LIKE '%SACOMBANK%' THEN 'Sacombank'
                    WHEN p.bank_name LIKE 'SCB%' THEN 'SCB'
                    WHEN p.bank_name LIKE '%SOUTHEAST ASIA COMMERCIAL%BANK%' THEN 'SeABank'
                    WHEN p.bank_name LIKE '%SEA%BANK%' THEN 'SeABank'
                    WHEN p.bank_name LIKE 'SAIGON HANOI COMMERCIAL%BANK%' THEN 'SHB'
                    WHEN p.bank_name IN ('Saigon Hanoi Commercial Joint Stock Bank') THEN 'SHB'
                    WHEN p.bank_name LIKE 'SHINHAN%BANK%' THEN 'ShinhanBank'
                    WHEN p.bank_name IN ('Shinhan Bank Vietnam', 'ShinhanBank') THEN 'ShinhanBank'
                    WHEN p.bank_name LIKE 'STANDARD%CHARTERED%' THEN 'Standard Chartered'
                    WHEN p.bank_name LIKE 'VIETNAM%TECHNOLOGICAL AND COMMERCIAL%' THEN 'Techcombank'
                    WHEN p.bank_name LIKE 'TIENPHONG%BANK%' THEN 'TPBank'
                    WHEN p.bank_name LIKE 'TPBANK%' THEN 'TPBank'
                    WHEN p.bank_name IN ('TienPhong Commercial Joint Stock Bank') THEN 'TPBank'
                    WHEN p.bank_name LIKE 'UNITED OVERSEAS BANK%' THEN 'UOB'
                    WHEN p.bank_name LIKE 'VIB%' THEN 'VIB'
                    WHEN p.bank_name LIKE '%VIETNAM INTERNATIONAL COMMERCIAL JOINT STOCK BANK%' THEN 'VIB'
                    WHEN p.bank_name LIKE 'VIETBANK%' THEN 'VietBank'
                    WHEN p.bank_name LIKE 'VIETNAM THUONG TIN%' THEN 'VietBank'
                    WHEN p.bank_name IN ('Vietnam Thuong Tin Commercial Joint Stock Bank') THEN 'VietBank'
                    WHEN p.bank_name IN ('VIETNAM-ASIA COMMERCIAL JSB') THEN 'VietABank'
                    WHEN p.bank_name LIKE 'VIET%CAPITAL%BANK%' THEN 'VietCapital'
                    WHEN p.bank_name IN ('Viet Capital Bank - NHTMCP Ban Viet') THEN 'VietCapital'
                    WHEN p.bank_name LIKE 'BANK%FOREIGN TRADE OF VIETNAM%' THEN 'Vietcombank'
                    WHEN p.bank_name LIKE '%VIETCOMBANK%' THEN 'Vietcombank'
                    WHEN p.bank_name LIKE 'VIETNAM%INDUSTRY AND TRADE%' THEN 'VietinBank'
                    WHEN p.bank_name IN ('INDUSTRIAL AND COMMERCIAL BANK OF VIETNAM') THEN 'VietinBank'
                    WHEN p.bank_name LIKE 'VPBANK%' THEN 'VPBank'
                    WHEN p.bank_name LIKE 'VP%BANK%' THEN 'VPBank'
                    WHEN p.bank_name LIKE 'VIETNAM%RUSSIA%' THEN 'VRB'
                    WHEN p.bank_name IN ('Vietnam - Russia Joint Venture Bank') THEN 'VRB'
                    WHEN p.bank_name IN ('Lien Viet Post Joint Stock Commercial Bank') THEN 'Lien Viet Post Bank' ELSE p.bank_name
             END bank_name
            ,p.payment_responsetext
            ,p.cybs_message
            ,COALESCE(p.cybs_message, p.payment_responsetext, p.error_reason, p.error_message, p.reply_error_code, p.response_code) AS fail_reason
    FROM    payment p
    WHERE   1 = 1
)
,final AS
(
    SELECT  *
            ,CASE   WHEN fail_reason LIKE 'limit' THEN 'Exceed limit'
                    WHEN fail_reason LIKE 'hạn mức' THEN 'Exceed limit'
                    WHEN fail_reason LIKE 'OTP' THEN 'OTP related'
                    WHEN fail_reason LIKE 'PIN' THEN 'PIN related'
                    WHEN fail_reason LIKE 'insufficient' THEN 'Insufficient balance'
                    WHEN fail_reason LIKE 'not enough' THEN 'Insufficient balance'
                    WHEN fail_reason LIKE 'không đủ' THEN 'Insufficient balance'
                    WHEN fail_reason LIKE 'phone' THEN 'Phone number related'
                    WHEN fail_reason LIKE 'số điện thoại' THEN 'Phone number related'
                    WHEN fail_reason LIKE 'processed' THEN 'Processing'
                    WHEN fail_reason LIKE 'xử lý' THEN 'Processing'
                    WHEN fail_reason LIKE 'Can not get card' THEN 'Cannot get card id info'
                    WHEN fail_reason LIKE 'online' THEN 'Online banking service unregistered'
                    WHEN fail_reason LIKE 'trực tuyến' THEN 'Online banking service unregistered'
                    WHEN fail_reason LIKE 'JPA EntityManager' THEN 'JPA EntityManager related'
                    WHEN fail_reason LIKE 'maintenance' THEN 'System maintenance'
                    WHEN fail_reason LIKE 'bảo trì' THEN 'System maintenance'
                    WHEN fail_reason IN ('An error has occurred. Please try again', 'Có lỗi xảy ra. Vui lòng thử lại') THEN 'An error has occurred. Please try again'
                    ELSE fail_reason
             END AS fail_reason_grouped
    FROM    joined
)
,summary AS
(
    SELECT  payment_date
            ,spm_channel_name
            ,provision
            ,payment_status
            ,fail_reason
            ,fail_reason_grouped
            ,bank_name
            ,brand
            ,COUNT(payment_id) AS pmt
    FROM    final
    WHERE   1=1
    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8
)
SELECT * FROM summary
;