/* Daily ER append for EFRM fraud users */
SELECT
  DISTINCT SUBSTR(CAST(txn_date_switch AS VARCHAR(30)), 1, 7) AS txn_year_month,
  payer_vpa_switch,
  'NA' AS reporting_date,
  ingestion_date,
  'EFRM' AS source
FROM
  (
    SELECT
      txn_id AS upi_txn_id,
      DATE(MIN(dl_last_updated)) AS ingestion_date,
      CAST(REGEXP_REPLACE(txn_amount, ',', '') AS DOUBLE) AS txn_amount
    FROM
      frauds.efrm_data_snapshot_v3
    WHERE
     DATE(dl_last_updated) >= DATE_TRUNC('MONTH', CURRENT_DATE - INTERVAL '1 MONTH')
      AND DATE(dl_last_updated) < DATE_TRUNC('MONTH', CURRENT_DATE)
    GROUP BY
      1,
      3
  ) AS a
  INNER JOIN (
    SELECT
      DISTINCT DATE(b.created_on) AS txn_date_switch,
      a.txn_id AS upi_txn_id_switch,
      a.vpa AS payer_vpa_switch,
      b.vpa AS payee_vpa_switch,
      CAST(a.amount AS DOUBLE) AS txn_amount_switch,
      a.mcc AS payer_mcc_switch,
      b.mcc AS payee_mcc_switch,
      a.handle AS payer_handle,
      b.handle AS payee_handle,
      a.scope_cust_id,
      a.mobile_no AS payer_mobile_no,
      (
        CASE
          WHEN (
            LOWER(b.vpa) LIKE '%@paytm%'
            OR LOWER(b.vpa) LIKE '%@pt%'
          ) THEN 'paytm merchant/bene'
          ELSE 'others'
        END
      ) AS merchant_bene_type
    FROM
      switch.txn_participants_snapshot_v3 AS a
      INNER JOIN switch.txn_participants_snapshot_v3 AS b ON a.txn_id = b.txn_id
    WHERE
      DATE(a.dl_last_updated) >= DATE(CURRENT_DATE - INTERVAL '90' DAY)
      AND DATE(b.dl_last_updated) >= DATE(CURRENT_DATE - INTERVAL '90' DAY)
      AND DATE(a.created_on) >= DATE(CURRENT_DATE - INTERVAL '90' DAY)
      AND a.participant_type IN ('PAYER')
      AND b.participant_type IN ('PAYEE')
      AND (
        LOWER(a.vpa) LIKE '%@paytm%'
        OR LOWER(a.vpa) LIKE '%@pt%'
      )
  ) AS c ON a.upi_txn_id = c.upi_txn_id_switch
  AND a.txn_amount = c.txn_amount_switch
  AND C.txn_date_switch < a.ingestion_date
  INNER JOIN (
    SELECT
      DISTINCT txn_id,
      category,
      CAST(amount AS DOUBLE) AS amount,
      DATE(created_on) AS created_on
    FROM
      switch.txn_info_snapshot_v3
    WHERE
      dl_last_updated >= DATE(CURRENT_DATE - INTERVAL '90' DAY)
      AND created_on >= DATE(CURRENT_DATE - INTERVAL '90' DAY)
      AND UPPER(status) IN ('SUCCESS')
  ) AS e ON a.upi_txn_id = e.txn_id
  AND a.txn_amount = e.amount
  AND e.created_on < a.ingestion_date