Bug: Store Network Reference ID against the Payment Method ID for all off-session payments



During the CIT there needs to be a validation which check for the `setup_future_usage` field, if it is set to `off_session` the NRID returned from the connector needs to be stored against the particular `payment_method_id` in the `network_transaction_id` column of the `payment_methods` table.
diff --git a/crates/diesel_models/src/payment_method.rs b/crates/diesel_models/src/payment_method.rs
index 7e8dcb639c3..7c28f4cd281 100644
--- a/crates/diesel_models/src/payment_method.rs
+++ b/crates/diesel_models/src/payment_method.rs
@@ -126,6 +126,10 @@ pub enum PaymentMethodUpdate {
     LastUsedUpdate {
         last_used_at: PrimitiveDateTime,
     },
+    NetworkTransactionIdAndStatusUpdate {
+        network_transaction_id: Option<String>,
+        status: Option<storage_enums::PaymentMethodStatus>,
+    },
     StatusUpdate {
         status: Option<storage_enums::PaymentMethodStatus>,
     },
@@ -140,6 +144,7 @@ pub struct PaymentMethodUpdateInternal {
     metadata: Option<serde_json::Value>,
     payment_method_data: Option<Encryption>,
     last_used_at: Option<PrimitiveDateTime>,
+    network_transaction_id: Option<String>,
     status: Option<storage_enums::PaymentMethodStatus>,
     connector_mandate_details: Option<serde_json::Value>,
 }
@@ -159,6 +164,7 @@ impl From<PaymentMethodUpdate> for PaymentMethodUpdateInternal {
                 metadata,
                 payment_method_data: None,
                 last_used_at: None,
+                network_transaction_id: None,
                 status: None,
                 connector_mandate_details: None,
             },
@@ -168,6 +174,7 @@ impl From<PaymentMethodUpdate> for PaymentMethodUpdateInternal {
                 metadata: None,
                 payment_method_data,
                 last_used_at: None,
+                network_transaction_id: None,
                 status: None,
                 connector_mandate_details: None,
             },
@@ -175,13 +182,26 @@ impl From<PaymentMethodUpdate> for PaymentMethodUpdateInternal {
                 metadata: None,
                 payment_method_data: None,
                 last_used_at: Some(last_used_at),
+                network_transaction_id: None,
                 status: None,
                 connector_mandate_details: None,
             },
+            PaymentMethodUpdate::NetworkTransactionIdAndStatusUpdate {
+                network_transaction_id,
+                status,
+            } => Self {
+                metadata: None,
+                payment_method_data: None,
+                last_used_at: None,
+                network_transaction_id,
+                status,
+                connector_mandate_details: None,
+            },
             PaymentMethodUpdate::StatusUpdate { status } => Self {
                 metadata: None,
                 payment_method_data: None,
                 last_used_at: None,
+                network_transaction_id: None,
                 status,
                 connector_mandate_details: None,
             },
@@ -193,6 +213,7 @@ impl From<PaymentMethodUpdate> for PaymentMethodUpdateInternal {
                 last_used_at: None,
                 status: None,
                 connector_mandate_details,
+                network_transaction_id: None,
             },
         }
     }
diff --git a/crates/router/src/connector/stripe/transformers.rs b/crates/router/src/connector/stripe/transformers.rs
index 7edf28f2c38..45aeb1e9074 100644
--- a/crates/router/src/connector/stripe/transformers.rs
+++ b/crates/router/src/connector/stripe/transformers.rs
@@ -2231,6 +2231,7 @@ impl Deref for PaymentIntentSyncResponse {
 pub struct StripeAdditionalCardDetails {
     checks: Option<Value>,
     three_d_secure: Option<Value>,
+    network_transaction_id: Option<String>,
 }
 
 #[derive(Deserialize, Clone, Debug, PartialEq, Eq, Serialize)]
@@ -2685,12 +2686,24 @@ impl<F, T>
                 item.response.id.clone(),
             ))
         } else {
+            let network_transaction_id = match item.response.latest_attempt {
+                Some(LatestAttempt::PaymentIntentAttempt(attempt)) => attempt
+                    .payment_method_details
+                    .and_then(|payment_method_details| match payment_method_details {
+                        StripePaymentMethodDetailsResponse::Card { card } => {
+                            card.network_transaction_id
+                        }
+                        _ => None,
+                    }),
+                _ => None,
+            };
+
             Ok(types::PaymentsResponseData::TransactionResponse {
                 resource_id: types::ResponseId::ConnectorTransactionId(item.response.id.clone()),
                 redirection_data,
                 mandate_reference,
                 connector_metadata: None,
-                network_txn_id: Option::foreign_from(item.response.latest_attempt),
+                network_txn_id: network_transaction_id,
                 connector_response_reference_id: Some(item.response.id),
                 incremental_authorization_allowed: None,
             })
@@ -3140,6 +3153,7 @@ pub struct LatestPaymentAttempt {
     pub payment_method_options: Option<StripePaymentMethodOptions>,
     pub payment_method_details: Option<StripePaymentMethodDetailsResponse>,
 }
+
 // #[derive(Deserialize, Debug, Clone, Eq, PartialEq)]
 // pub struct Card
 #[derive(serde::Serialize, serde::Deserialize, Clone, Debug, Default, Eq, PartialEq)]
diff --git a/crates/router/src/core/payment_methods/cards.rs b/crates/router/src/core/payment_methods/cards.rs
index 64346a94a9d..d94864c75b5 100644
--- a/crates/router/src/core/payment_methods/cards.rs
+++ b/crates/router/src/core/payment_methods/cards.rs
@@ -87,6 +87,7 @@ pub async fn create_payment_method(
     payment_method_data: Option<Encryption>,
     key_store: &domain::MerchantKeyStore,
     connector_mandate_details: Option<serde_json::Value>,
+    network_transaction_id: Option<String>,
 ) -> errors::CustomResult<storage::PaymentMethod, errors::ApiErrorResponse> {
     let customer = db
         .find_customer_by_customer_id_merchant_id(customer_id, merchant_id, key_store)
@@ -107,6 +108,7 @@ pub async fn create_payment_method(
             payment_method_data,
             connector_mandate_details,
             customer_acceptance: customer_acceptance.map(masking::Secret::new),
+            network_transaction_id: network_transaction_id.to_owned(),
             ..storage::PaymentMethodNew::default()
         })
         .await
@@ -205,6 +207,7 @@ pub async fn get_or_insert_payment_method(
                     None,
                     locker_id,
                     None,
+                    None,
                 )
                 .await
             } else {
@@ -392,6 +395,7 @@ pub async fn add_payment_method(
                 None,
                 locker_id,
                 None,
+                None,
             )
             .await?;
         }
@@ -412,6 +416,7 @@ pub async fn insert_payment_method(
     customer_acceptance: Option<serde_json::Value>,
     locker_id: Option<String>,
     connector_mandate_details: Option<serde_json::Value>,
+    network_transaction_id: Option<String>,
 ) -> errors::RouterResult<diesel_models::PaymentMethod> {
     let pm_card_details = resp
         .card
@@ -430,6 +435,7 @@ pub async fn insert_payment_method(
         pm_data_encrypted,
         key_store,
         connector_mandate_details,
+        network_transaction_id,
     )
     .await
 }
diff --git a/crates/router/src/core/payments/operations/payment_response.rs b/crates/router/src/core/payments/operations/payment_response.rs
index 66f35e63b2c..f418b660380 100644
--- a/crates/router/src/core/payments/operations/payment_response.rs
+++ b/crates/router/src/core/payments/operations/payment_response.rs
@@ -7,7 +7,7 @@ use data_models::payments::payment_attempt::PaymentAttempt;
 use error_stack::{report, ResultExt};
 use futures::FutureExt;
 use router_derive;
-use router_env::{instrument, tracing};
+use router_env::{instrument, logger, tracing};
 use storage_impl::DataModelExt;
 use tracing_futures::Instrument;
 
@@ -34,7 +34,7 @@ use crate::{
         self, api,
         storage::{self, enums},
         transformers::{ForeignFrom, ForeignTryFrom},
-        CaptureSyncResponse,
+        CaptureSyncResponse, ErrorResponse,
     },
     utils,
 };
@@ -833,7 +833,7 @@ async fn payment_response_update_tracker<F: Clone, T: types::Capturable>(
     };
 
     let amount_captured = get_total_amount_captured(
-        router_data.request,
+        &router_data.request,
         router_data.amount_captured,
         router_data.status,
         &payment_data,
@@ -862,7 +862,13 @@ async fn payment_response_update_tracker<F: Clone, T: types::Capturable>(
         },
     };
 
-    update_payment_method_status(state, &mut payment_data, router_data.status).await?;
+    update_payment_method_status_and_ntid(
+        state,
+        &mut payment_data,
+        router_data.status,
+        router_data.response.clone(),
+    )
+    .await?;
     let m_db = state.clone().store;
     let m_payment_data_payment_intent = payment_data.payment_intent.clone();
     let m_payment_intent_update = payment_intent_update.clone();
@@ -954,10 +960,11 @@ async fn payment_response_update_tracker<F: Clone, T: types::Capturable>(
     Ok(payment_data)
 }
 
-async fn update_payment_method_status<F: Clone>(
+async fn update_payment_method_status_and_ntid<F: Clone>(
     state: &AppState,
     payment_data: &mut PaymentData<F>,
     attempt_status: common_enums::AttemptStatus,
+    payment_response: Result<types::PaymentsResponseData, ErrorResponse>,
 ) -> RouterResult<()> {
     if let Some(id) = &payment_data.payment_attempt.payment_method_id {
         let pm = state
@@ -965,8 +972,20 @@ async fn update_payment_method_status<F: Clone>(
             .find_payment_method(id)
             .await
             .to_not_found_response(errors::ApiErrorResponse::PaymentMethodNotFound)?;
+        let network_transaction_id = payment_response
+            .map(|resp| match resp {
+                crate::types::PaymentsResponseData::TransactionResponse { network_txn_id, .. } => {
+                    network_txn_id.to_owned()
+                }
+                _ => None,
+            })
+            .map_err(|err| {
+                logger::error!(error=?err, "Failed to obtain the network_transaction_id from payment response");
+            })
+            .ok()
+            .flatten();
 
-        if pm.status != common_enums::PaymentMethodStatus::Active
+        let pm_update = if pm.status != common_enums::PaymentMethodStatus::Active
             && pm.status != attempt_status.into()
         {
             let updated_pm_status = common_enums::PaymentMethodStatus::from(attempt_status);
@@ -975,16 +994,23 @@ async fn update_payment_method_status<F: Clone>(
                 .payment_method_info
                 .as_mut()
                 .map(|info| info.status = updated_pm_status);
-            let pm_update = storage::PaymentMethodUpdate::StatusUpdate {
+            storage::PaymentMethodUpdate::NetworkTransactionIdAndStatusUpdate {
+                network_transaction_id,
                 status: Some(updated_pm_status),
-            };
-            state
-                .store
-                .update_payment_method(pm, pm_update)
-                .await
-                .change_context(errors::ApiErrorResponse::InternalServerError)
-                .attach_printable("Failed to update payment method in db")?;
-        }
+            }
+        } else {
+            storage::PaymentMethodUpdate::NetworkTransactionIdAndStatusUpdate {
+                network_transaction_id,
+                status: None,
+            }
+        };
+
+        state
+            .store
+            .update_payment_method(pm, pm_update)
+            .await
+            .change_context(errors::ApiErrorResponse::InternalServerError)
+            .attach_printable("Failed to update payment method in db")?;
     };
     Ok(())
 }
@@ -1043,7 +1069,7 @@ fn get_capture_update_for_unmapped_capture_responses(
 }
 
 fn get_total_amount_captured<F: Clone, T: types::Capturable>(
-    request: T,
+    request: &T,
     amount_captured: Option<i64>,
     router_data_status: enums::AttemptStatus,
     payment_data: &PaymentData<F>,
diff --git a/crates/router/src/core/payments/operations/payment_status.rs b/crates/router/src/core/payments/operations/payment_status.rs
index 4d616146908..7a8f487d73a 100644
--- a/crates/router/src/core/payments/operations/payment_status.rs
+++ b/crates/router/src/core/payments/operations/payment_status.rs
@@ -399,6 +399,18 @@ async fn get_tracker_for_sync<
         .to_not_found_response(errors::ApiErrorResponse::BusinessProfileNotFound {
             id: profile_id.to_string(),
         })?;
+    let payment_method_info =
+        if let Some(ref payment_method_id) = payment_attempt.payment_method_id.clone() {
+            Some(
+                db.find_payment_method(payment_method_id)
+                    .await
+                    .to_not_found_response(errors::ApiErrorResponse::PaymentMethodNotFound)
+                    .attach_printable("error retrieving payment method from DB")?,
+            )
+        } else {
+            None
+        };
+
     let merchant_id = payment_intent.merchant_id.clone();
     let authentication = payment_attempt.authentication_id.clone().async_map(|authentication_id| async move {
             db.find_authentication_by_merchant_id_authentication_id(
@@ -436,7 +448,7 @@ async fn get_tracker_for_sync<
         token_data: None,
         confirm: Some(request.force_sync),
         payment_method_data: None,
-        payment_method_info: None,
+        payment_method_info,
         force_sync: Some(
             request.force_sync
                 && (helpers::check_force_psync_precondition(&payment_attempt.status)
diff --git a/crates/router/src/core/payments/tokenization.rs b/crates/router/src/core/payments/tokenization.rs
index 3941fad5a42..47cd1cd9eb7 100644
--- a/crates/router/src/core/payments/tokenization.rs
+++ b/crates/router/src/core/payments/tokenization.rs
@@ -57,6 +57,13 @@ where
                 .map(|token_filter| token_filter.long_lived_token)
                 .unwrap_or(false);
 
+            let network_transaction_id = match responses.clone() {
+                types::PaymentsResponseData::TransactionResponse { network_txn_id, .. } => {
+                    network_txn_id
+                }
+                _ => None,
+            };
+
             let connector_token = if token_store {
                 let tokens = resp
                     .payment_method_token
@@ -255,6 +262,7 @@ where
                                             pm_data_encrypted,
                                             key_store,
                                             connector_mandate_details,
+                                            network_transaction_id,
                                         )
                                         .await
                                     } else {
@@ -337,6 +345,7 @@ where
                                                 customer_acceptance,
                                                 locker_id,
                                                 connector_mandate_details,
+                                                network_transaction_id,
                                             )
                                             .await
                                         } else {
@@ -459,6 +468,7 @@ where
                             pm_data_encrypted,
                             key_store,
                             connector_mandate_details,
+                            network_transaction_id,
                         )
                         .await?;
                     }
diff --git a/crates/router/src/core/payouts/helpers.rs b/crates/router/src/core/payouts/helpers.rs
index 1a94186a86f..6efe6ee9a77 100644
--- a/crates/router/src/core/payouts/helpers.rs
+++ b/crates/router/src/core/payouts/helpers.rs
@@ -13,8 +13,8 @@ use crate::{
     core::{
         errors::{self, RouterResult},
         payment_methods::{
-            cards, transformers,
-            transformers::{StoreCardReq, StoreGenericReq, StoreLockerReq},
+            cards,
+            transformers::{self, StoreCardReq, StoreGenericReq, StoreLockerReq},
             vault,
         },
         payments::{
@@ -389,6 +389,7 @@ pub async fn save_payout_data_to_locker(
         card_details_encrypted,
         key_store,
         None,
+        None,
     )
     .await?;
 

## Type of Change
<!-- Put an `x` in the boxes that apply -->

- [ ] Bugfix
- [x] New feature
- [ ] Enhancement
- [ ] Refactoring
- [ ] Dependency updates
- [ ] Documentation
- [ ] CI/CD

## Description
<!-- Describe your changes in detail -->
During the CIT there needs to be a validation which check for the setup_future_usage field, if it is set to off_session the NRID returned from the connector needs to be stored against the particular payment_method_id in the network_transaction_id column of the payment_methods table.

### Additional Changes

- [ ] This PR modifies the API contract
- [ ] This PR modifies the database schema
- [ ] This PR modifies application configuration/environment variables

<!--
Provide links to the files with corresponding changes.

Following are the paths where you can find config files:
1. `config`
2. `crates/router/src/configs`
3. `loadtest/config`
-->


## Motivation and Context
<!--
Why is this change required? What problem does it solve?
If it fixes an open issue, please link to the issue here.

If you don't have an issue, we'd recommend starting with one first so the PR
can focus on the implementation (unless it is an obvious bug or documentation fix
that will have little conversation).
-->


## How did you test it?
<!--
Did you write an integration/unit/API test to verify the code changes?
Or did you test this change manually (provide relevant screenshots)?
-->
Create a Merchant account 
Create a mca for stripe, adyen or cybersource
Create a mandate payment (Setup mandate)
```
{
    "amount": 0,
    "currency": "USD",
    "confirm": true,
    "capture_method": "automatic",
    "capture_on": "2022-09-10T10:11:12Z",
    "customer_id": "singh",
    "email": "guest@example.com",
    "name": "John Doe",
    "phone": "999999999",
    "phone_country_code": "+65",
    "description": "Its my first payment request",
    "authentication_type": "no_three_ds",
    "return_url": "https://google.com",
    "payment_method": "card",
    "payment_method_type": "credit",
    "payment_method_data": {
        "card": {
            "card_number": "4242424242424242",
            "card_exp_month": "03",
            "card_exp_year": "2030",
            "card_holder_name": "joseph Doe",
            "card_cvc": "123"
        }
    },
    "setup_future_usage": "off_session",
    "payment_type": "setup_mandate",
        "customer_acceptance": {
            "acceptance_type": "offline",
            "accepted_at": "1963-05-03T04:07:52.723Z",
            "online": {
                "ip_address": "127.0.0.1",
                "user_agent": "amet irure esse"
            }
    },
    "billing": {
        "address": {
            "line1": "1467",
            "line2": "Harrison Street",
            "line3": "Harrison Street",
            "city": "San Fransico",
            "state": "California",
            "zip": "94122",
            "country": "US",
            "first_name": "John",
            "last_name": "Doe"
        },
        "phone": {
            "number": "8056594427",
            "country_code": "+91"
        }
    },
    "shipping": {
        "address": {
            "line1": "1467",
            "line2": "Harrison Street",
            "line3": "Harrison Street",
            "city": "San Fransico",
            "state": "California",
            "zip": "94122",
            "country": "US",
            "first_name": "John",
            "last_name": "Doe"
        },
        "phone": {
            "number": "8056594427",
            "country_code": "+91"
        }
    },
    "browser_info": {
        "user_agent": "Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/70.0.3538.110 Safari\/537.36",
        "accept_header": "text\/html,application\/xhtml+xml,application\/xml;q=0.9,image\/webp,image\/apng,*\/*;q=0.8",
        "language": "nl-NL",
        "color_depth": 24,
        "screen_height": 723,
        "screen_width": 1536,
        "time_zone": 0,
        "java_enabled": true,
        "java_script_enabled": true,
        "ip_address": "125.0.0.1"
    },
    "statement_descriptor_name": "joseph",
    "statement_descriptor_suffix": "JS",
    "metadata": {},
    "order_details": [
        {
            "product_name": "Apple iphone 15",
            "quantity": 1,
            "amount": 0,
            "account_name": "transaction_processing"
        }
    ]
}
```
Once the mandate is successful the network_transaction_id is stored as below
`network_transaction_id` being stored in `payment_method` table
![image](https://github.com/juspay/hyperswitch/assets/83439957/ddaed1ec-1b75-4d29-b889-c3e3cedaa62d)


## Checklist
<!-- Put an `x` in the boxes that apply -->

- [x] I formatted the code `cargo +nightly fmt --all`
- [x] I addressed lints thrown by `cargo clippy`
- [x] I reviewed the submitted code
- [ ] I added unit tests for my changes where possible
- [ ] I added a [CHANGELOG](/CHANGELOG.md) entry if applicable
