Issue:
Bug: DB Updates for Payment Methods Table





Pull request description:
## 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 -->
This PR adds 3 columns to the `payment_methods` table, with the purpose of enabling MIT Transaction Orchestration in the near future.
- `connector_mandate_details`: Intended to store Connector MIT details such as the mandate ID
- `customer_acceptance`: Intended to store customer consent and acceptance details
- `status`: The status of the payment method (Can be `processing` initially for connector MITs that rely on asynchronous flows and/or time-based factors for setup)

#### Flows Affected
- **List Payment Methods for Customer**: This was updated such that only payment methods with status `active` are listed for the customer. To make this backwards compatible, the default status for new saved cards is set to `active`.

### Additional Changes

- [ ] This PR modifies the API contract
- [x] 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).
-->
This change is important for setting up MIT orchestration in the future.

## 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)?
-->
Local testing, postman tests. The saved card payments flow needs to work as usual which is covered by Postman tests.

When saving a card, the values of the added columns are set to default for now. This is NULL for `connector_mandate_details` and `customer_acceptance`, and `active` for `status`.
<img width="420" alt="image" src="https://github.com/juspay/hyperswitch/assets/47862918/0bf2473c-90bb-4d9c-b3d2-b83905b5101c">

Since the default for the status column is set to `active`, the `List Payment Methods for Customer` flow works as intended for saved payment methods.
<img width="502" alt="image" src="https://github.com/juspay/hyperswitch/assets/47862918/6c7cbbea-4c99-4391-97db-81012d11e448">


## 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


Testing:

Local testing, postman tests. The saved card payments flow needs to work as usual which is covered by Postman tests.

When saving a card, the values of the added columns are set to default for now. This is NULL for `connector_mandate_details` and `customer_acceptance`, and `active` for `status`.
<img width="420" alt="image" src="https://github.com/juspay/hyperswitch/assets/47862918/0bf2473c-90bb-4d9c-b3d2-b83905b5101c">

Since the default for the status column is set to `active`, the `List Payment Methods for Customer` flow works as intended for saved payment methods.
<img width="502" alt="image" src="https://github.com/juspay/hyperswitch/assets/47862918/6c7cbbea-4c99-4391-97db-81012d11e448">

Patch:
diff --git a/crates/common_enums/src/enums.rs b/crates/common_enums/src/enums.rs
index c3976321f0a..29c416e9a85 100644
--- a/crates/common_enums/src/enums.rs
+++ b/crates/common_enums/src/enums.rs
@@ -1177,6 +1177,32 @@ pub enum PaymentMethodIssuerCode {
     JpBacs,
 }
 
+#[derive(
+    Clone,
+    Copy,
+    Debug,
+    PartialEq,
+    Eq,
+    Hash,
+    serde::Serialize,
+    serde::Deserialize,
+    strum::Display,
+    strum::EnumString,
+    ToSchema,
+)]
+#[router_derive::diesel_enum(storage_type = "text")]
+#[strum(serialize_all = "snake_case")]
+#[serde(rename_all = "snake_case")]
+pub enum PaymentMethodStatus {
+    /// Indicates that the payment method is active and can be used for payments.
+    Active,
+    /// Indicates that the payment method is not active and hence cannot be used for payments.
+    Inactive,
+    /// Indicates that the payment method is awaiting some data or action before it can be marked
+    /// as 'active'.
+    Processing,
+}
+
 /// To indicate the type of payment experience that the customer would go through
 #[derive(
     Eq,
diff --git a/crates/diesel_models/src/payment_method.rs b/crates/diesel_models/src/payment_method.rs
index f9767e2939b..09be739581e 100644
--- a/crates/diesel_models/src/payment_method.rs
+++ b/crates/diesel_models/src/payment_method.rs
@@ -34,6 +34,9 @@ pub struct PaymentMethod {
     pub metadata: Option<pii::SecretSerdeValue>,
     pub payment_method_data: Option<Encryption>,
     pub locker_id: Option<String>,
+    pub connector_mandate_details: Option<serde_json::Value>,
+    pub customer_acceptance: Option<pii::SecretSerdeValue>,
+    pub status: storage_enums::PaymentMethodStatus,
 }
 
 #[derive(Clone, Debug, Eq, PartialEq, Insertable, Queryable, router_derive::DebugAsDisplay)]
@@ -61,6 +64,9 @@ pub struct PaymentMethodNew {
     pub metadata: Option<pii::SecretSerdeValue>,
     pub payment_method_data: Option<Encryption>,
     pub locker_id: Option<String>,
+    pub connector_mandate_details: Option<serde_json::Value>,
+    pub customer_acceptance: Option<pii::SecretSerdeValue>,
+    pub status: storage_enums::PaymentMethodStatus,
 }
 
 impl Default for PaymentMethodNew {
@@ -90,6 +96,9 @@ impl Default for PaymentMethodNew {
             last_modified: now,
             metadata: Option::default(),
             payment_method_data: Option::default(),
+            connector_mandate_details: Option::default(),
+            customer_acceptance: Option::default(),
+            status: storage_enums::PaymentMethodStatus::Active,
         }
     }
 }
diff --git a/crates/diesel_models/src/query/payment_method.rs b/crates/diesel_models/src/query/payment_method.rs
index 298db2a234b..aa227962760 100644
--- a/crates/diesel_models/src/query/payment_method.rs
+++ b/crates/diesel_models/src/query/payment_method.rs
@@ -3,7 +3,7 @@ use router_env::{instrument, tracing};
 
 use super::generics;
 use crate::{
-    errors,
+    enums as storage_enums, errors,
     payment_method::{self, PaymentMethod, PaymentMethodNew},
     schema::payment_methods::dsl,
     PgPooledConn, StorageResult,
@@ -108,6 +108,31 @@ impl PaymentMethod {
         .await
     }
 
+    #[instrument(skip(conn))]
+    pub async fn find_by_customer_id_merchant_id_status(
+        conn: &PgPooledConn,
+        customer_id: &str,
+        merchant_id: &str,
+        status: storage_enums::PaymentMethodStatus,
+    ) -> StorageResult<Vec<Self>> {
+        generics::generic_filter::<
+            <Self as HasTable>::Table,
+            _,
+            <<Self as HasTable>::Table as Table>::PrimaryKey,
+            _,
+        >(
+            conn,
+            dsl::customer_id
+                .eq(customer_id.to_owned())
+                .and(dsl::merchant_id.eq(merchant_id.to_owned()))
+                .and(dsl::status.eq(status)),
+            None,
+            None,
+            None,
+        )
+        .await
+    }
+
     pub async fn update_with_payment_method_id(
         self,
         conn: &PgPooledConn,
diff --git a/crates/diesel_models/src/schema.rs b/crates/diesel_models/src/schema.rs
index 4ac75f5e7de..f3c6f23e086 100644
--- a/crates/diesel_models/src/schema.rs
+++ b/crates/diesel_models/src/schema.rs
@@ -831,6 +831,10 @@ diesel::table! {
         payment_method_data -> Nullable<Bytea>,
         #[max_length = 64]
         locker_id -> Nullable<Varchar>,
+        connector_mandate_details -> Nullable<Jsonb>,
+        customer_acceptance -> Nullable<Jsonb>,
+        #[max_length = 64]
+        status -> Varchar,
     }
 }
 
diff --git a/crates/router/src/core/payment_methods/cards.rs b/crates/router/src/core/payment_methods/cards.rs
index 0a1cc023ece..a92215936a0 100644
--- a/crates/router/src/core/payment_methods/cards.rs
+++ b/crates/router/src/core/payment_methods/cards.rs
@@ -2667,9 +2667,10 @@ pub async fn list_customer_payment_method(
     let requires_cvv = is_requires_cvv.config != "false";
 
     let resp = db
-        .find_payment_method_by_customer_id_merchant_id_list(
+        .find_payment_method_by_customer_id_merchant_id_status(
             customer_id,
             &merchant_account.merchant_id,
+            common_enums::PaymentMethodStatus::Active,
         )
         .await
         .to_not_found_response(errors::ApiErrorResponse::PaymentMethodNotFound)?;
diff --git a/crates/router/src/db/kafka_store.rs b/crates/router/src/db/kafka_store.rs
index acc703d5c1b..1c1ec00ce79 100644
--- a/crates/router/src/db/kafka_store.rs
+++ b/crates/router/src/db/kafka_store.rs
@@ -1273,6 +1273,17 @@ impl PaymentMethodInterface for KafkaStore {
             .await
     }
 
+    async fn find_payment_method_by_customer_id_merchant_id_status(
+        &self,
+        customer_id: &str,
+        merchant_id: &str,
+        status: common_enums::PaymentMethodStatus,
+    ) -> CustomResult<Vec<storage::PaymentMethod>, errors::StorageError> {
+        self.diesel_store
+            .find_payment_method_by_customer_id_merchant_id_status(customer_id, merchant_id, status)
+            .await
+    }
+
     async fn find_payment_method_by_locker_id(
         &self,
         locker_id: &str,
diff --git a/crates/router/src/db/payment_method.rs b/crates/router/src/db/payment_method.rs
index 4f4087f552e..f15ceecd1c4 100644
--- a/crates/router/src/db/payment_method.rs
+++ b/crates/router/src/db/payment_method.rs
@@ -26,6 +26,13 @@ pub trait PaymentMethodInterface {
         merchant_id: &str,
     ) -> CustomResult<Vec<storage::PaymentMethod>, errors::StorageError>;
 
+    async fn find_payment_method_by_customer_id_merchant_id_status(
+        &self,
+        customer_id: &str,
+        merchant_id: &str,
+        status: common_enums::PaymentMethodStatus,
+    ) -> CustomResult<Vec<storage::PaymentMethod>, errors::StorageError>;
+
     async fn insert_payment_method(
         &self,
         payment_method_new: storage::PaymentMethodNew,
@@ -105,6 +112,24 @@ impl PaymentMethodInterface for Store {
             .into_report()
     }
 
+    async fn find_payment_method_by_customer_id_merchant_id_status(
+        &self,
+        customer_id: &str,
+        merchant_id: &str,
+        status: common_enums::PaymentMethodStatus,
+    ) -> CustomResult<Vec<storage::PaymentMethod>, errors::StorageError> {
+        let conn = connection::pg_connection_read(self).await?;
+        storage::PaymentMethod::find_by_customer_id_merchant_id_status(
+            &conn,
+            customer_id,
+            merchant_id,
+            status,
+        )
+        .await
+        .map_err(Into::into)
+        .into_report()
+    }
+
     async fn delete_payment_method_by_merchant_id_payment_method_id(
         &self,
         merchant_id: &str,
@@ -196,6 +221,9 @@ impl PaymentMethodInterface for MockDb {
             payment_method_issuer_code: payment_method_new.payment_method_issuer_code,
             metadata: payment_method_new.metadata,
             payment_method_data: payment_method_new.payment_method_data,
+            connector_mandate_details: payment_method_new.connector_mandate_details,
+            customer_acceptance: payment_method_new.customer_acceptance,
+            status: payment_method_new.status,
         };
         payment_methods.push(payment_method.clone());
         Ok(payment_method)
@@ -223,6 +251,33 @@ impl PaymentMethodInterface for MockDb {
         }
     }
 
+    async fn find_payment_method_by_customer_id_merchant_id_status(
+        &self,
+        customer_id: &str,
+        merchant_id: &str,
+        status: common_enums::PaymentMethodStatus,
+    ) -> CustomResult<Vec<storage::PaymentMethod>, errors::StorageError> {
+        let payment_methods = self.payment_methods.lock().await;
+        let payment_methods_found: Vec<storage::PaymentMethod> = payment_methods
+            .iter()
+            .filter(|pm| {
+                pm.customer_id == customer_id
+                    && pm.merchant_id == merchant_id
+                    && pm.status == status
+            })
+            .cloned()
+            .collect();
+
+        if payment_methods_found.is_empty() {
+            Err(errors::StorageError::ValueNotFound(
+                "cannot find payment methods".to_string(),
+            ))
+            .into_report()
+        } else {
+            Ok(payment_methods_found)
+        }
+    }
+
     async fn delete_payment_method_by_merchant_id_payment_method_id(
         &self,
         merchant_id: &str,
diff --git a/migrations/2024-02-22-060352_add_mit_columns_to_payment_methods/down.sql b/migrations/2024-02-22-060352_add_mit_columns_to_payment_methods/down.sql
new file mode 100644
index 00000000000..65367b5cd54
--- /dev/null
+++ b/migrations/2024-02-22-060352_add_mit_columns_to_payment_methods/down.sql
@@ -0,0 +1,10 @@
+-- This file should undo anything in `up.sql`
+
+ALTER TABLE payment_methods
+DROP COLUMN status;
+
+ALTER TABLE payment_methods
+DROP COLUMN customer_acceptance;
+
+ALTER TABLE payment_methods
+DROP COLUMN connector_mandate_details;
diff --git a/migrations/2024-02-22-060352_add_mit_columns_to_payment_methods/up.sql b/migrations/2024-02-22-060352_add_mit_columns_to_payment_methods/up.sql
new file mode 100644
index 00000000000..c43c347da38
--- /dev/null
+++ b/migrations/2024-02-22-060352_add_mit_columns_to_payment_methods/up.sql
@@ -0,0 +1,13 @@
+-- Your SQL goes here
+
+ALTER TABLE payment_methods
+ADD COLUMN connector_mandate_details JSONB
+DEFAULT NULL;
+
+ALTER TABLE payment_methods
+ADD COLUMN customer_acceptance JSONB
+DEFAULT NULL;
+
+ALTER TABLE payment_methods
+ADD COLUMN status VARCHAR(64)
+NOT NULL DEFAULT 'active';