-- TPAP FILL RATE 
CREATE TABLE team_kingkong.tpap_fill_rate AS
WITH

-- Total row count per table to avoid window function overhead
txn_info_total AS (
  SELECT COUNT(*) AS total_rows
  FROM switch.txn_info_snapshot_v3
  WHERE dl_last_updated > DATE(CURRENT_DATE - INTERVAL '7' DAY)
),
txn_info AS (
  SELECT *
  FROM switch.txn_info_snapshot_v3
  WHERE dl_last_updated > DATE(CURRENT_DATE - INTERVAL '7' DAY)
),

txn_participants_total AS (
  SELECT COUNT(*) AS total_rows
  FROM switch.txn_participants_snapshot_v3
  WHERE dl_last_updated > DATE(CURRENT_DATE - INTERVAL '7' DAY)
),
txn_participants AS (
  SELECT *
  FROM switch.txn_participants_snapshot_v3
  WHERE dl_last_updated > DATE(CURRENT_DATE - INTERVAL '7' DAY)
),

risk_data_total AS (
  SELECT COUNT(*) AS total_rows
  FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
  WHERE dl_last_updated > DATE(CURRENT_DATE - INTERVAL '7' DAY)
),
risk_data AS (
  SELECT request, response
  FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
  WHERE dl_last_updated > DATE(CURRENT_DATE - INTERVAL '7' DAY)
),

risk_parsed AS (
  SELECT
    json_extract_scalar(request, '$.evaluationType') AS evaluation_type,
    json_extract_scalar(request, '$.requestPayload.latitude') AS latitude,
    json_extract_scalar(request, '$.requestPayload.longitude') AS longitude,
    json_extract_scalar(request, '$.requestPayload.osVersion') AS os_version,
    json_extract_scalar(request, '$.requestPayload.payeeType') AS payee_type,
    json_extract_scalar(request, '$.requestPayload.payeeVpa') AS payee_vpa,
    json_extract_scalar(request, '$.requestPayload.payerType') AS payer_type,
    json_extract_scalar(request, '$.requestPayload.payerVpa') AS payer_vpa,
    json_extract_scalar(response, '$.messages.cst[0]') AS cst_risk_code,
    json_extract_scalar(response, '$.action_recommended') AS action_recommended
  FROM risk_data
)

-- Final column-level fill rates
SELECT 'switch' AS db_name, 'txn_info_snapshot_v3' AS table_name, 'category' AS column_name,
       100.0 * COUNT_IF(category IS NOT NULL AND TRIM(category) != '') / (SELECT total_rows FROM txn_info_total) AS fill_rate_pct
FROM txn_info

UNION ALL
SELECT 'switch', 'txn_info_snapshot_v3', 'status',
       100.0 * COUNT_IF(status IS NOT NULL AND TRIM(status) != '') / (SELECT total_rows FROM txn_info_total)
FROM txn_info

UNION ALL
SELECT 'switch', 'txn_info_snapshot_v3', 'txn_id',
       100.0 * COUNT_IF(txn_id IS NOT NULL AND TRIM(txn_id) != '') / (SELECT total_rows FROM txn_info_total)
FROM txn_info

UNION ALL
SELECT 'switch', 'txn_participants_snapshot_v3', 'amount',
       100.0 * COUNT_IF(amount IS NOT NULL) / (SELECT total_rows FROM txn_participants_total)
FROM txn_participants

UNION ALL
SELECT 'switch', 'txn_participants_snapshot_v3', 'participant_type',
       100.0 * COUNT_IF(participant_type IS NOT NULL AND TRIM(participant_type) != '') / (SELECT total_rows FROM txn_participants_total)
FROM txn_participants

UNION ALL
SELECT 'switch', 'txn_participants_snapshot_v3', 'scope_cust_id (payer)',
       100.0 * COUNT_IF(scope_cust_id IS NOT NULL AND participant_type = 'PAYER') /
       NULLIF(COUNT_IF(participant_type = 'PAYER'), 0)
FROM txn_participants

UNION ALL
SELECT 'switch', 'txn_participants_snapshot_v3', 'txn_id',
       100.0 * COUNT_IF(txn_id IS NOT NULL AND TRIM(txn_id) != '') / (SELECT total_rows FROM txn_participants_total)
FROM txn_participants

UNION ALL
SELECT 'switch', 'txn_participants_snapshot_v3', 'vpa',
       100.0 * COUNT_IF(vpa IS NOT NULL AND TRIM(vpa) != '') / (SELECT total_rows FROM txn_participants_total)
FROM txn_participants

UNION ALL
SELECT 'tpap_hss', 'upi_switchv2_dwh_risk_data_snapshot_v3', 'evaluation_type',
       100.0 * COUNT_IF(evaluation_type IS NOT NULL AND TRIM(evaluation_type) != '') / (SELECT total_rows FROM risk_data_total)
FROM risk_parsed

UNION ALL
SELECT 'tpap_hss', 'upi_switchv2_dwh_risk_data_snapshot_v3', 'latitude',
       100.0 * COUNT_IF(latitude IS NOT NULL AND TRIM(latitude) != '') / (SELECT total_rows FROM risk_data_total)
FROM risk_parsed

UNION ALL
SELECT 'tpap_hss', 'upi_switchv2_dwh_risk_data_snapshot_v3', 'longitude',
       100.0 * COUNT_IF(longitude IS NOT NULL AND TRIM(longitude) != '') / (SELECT total_rows FROM risk_data_total)
FROM risk_parsed

UNION ALL
SELECT 'tpap_hss', 'upi_switchv2_dwh_risk_data_snapshot_v3', 'os_version',
       100.0 * COUNT_IF(os_version IS NOT NULL AND TRIM(os_version) != '') / (SELECT total_rows FROM risk_data_total)
FROM risk_parsed

UNION ALL
SELECT 'tpap_hss', 'upi_switchv2_dwh_risk_data_snapshot_v3', 'payee_type',
       100.0 * COUNT_IF(payee_type IS NOT NULL AND TRIM(payee_type) != '') / (SELECT total_rows FROM risk_data_total)
FROM risk_parsed

UNION ALL
SELECT 'tpap_hss', 'upi_switchv2_dwh_risk_data_snapshot_v3', 'payee_vpa',
       100.0 * COUNT_IF(payee_vpa IS NOT NULL AND TRIM(payee_vpa) != '') / (SELECT total_rows FROM risk_data_total)
FROM risk_parsed

UNION ALL
SELECT 'tpap_hss', 'upi_switchv2_dwh_risk_data_snapshot_v3', 'payer_type',
       100.0 * COUNT_IF(payer_type IS NOT NULL AND TRIM(payer_type) != '') / (SELECT total_rows FROM risk_data_total)
FROM risk_parsed

UNION ALL
SELECT 'tpap_hss', 'upi_switchv2_dwh_risk_data_snapshot_v3', 'payer_vpa',
       100.0 * COUNT_IF(payer_vpa IS NOT NULL AND TRIM(payer_vpa) != '') / (SELECT total_rows FROM risk_data_total)
FROM risk_parsed

UNION ALL
SELECT 'tpap_hss', 'upi_switchv2_dwh_risk_data_snapshot_v3', 'cst_risk_code',
       100.0 * COUNT_IF(cst_risk_code IS NOT NULL AND TRIM(cst_risk_code) != '') / (SELECT total_rows FROM risk_data_total)
FROM risk_parsed

UNION ALL
SELECT 'tpap_hss', 'upi_switchv2_dwh_risk_data_snapshot_v3', 'action_recommended',
       100.0 * COUNT_IF(action_recommended IS NOT NULL AND TRIM(action_recommended) != '') / (SELECT total_rows FROM risk_data_total)
FROM risk_parsed;