Issue:
Bug: feat(payments): show aggregate count of refund status





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
Added new api for refunds status aggregate

### Additional Changes

- [x] 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
Closed #5796 
<!--
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?

Request
```
curl --location 'http://localhost:8080/refunds/aggregate?start_time=2022-08-28T18%3A30%3A00Z' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJ1c2VyX2lkIjoiZjNmYzEyNjYtZDQ2MS00NWU3LWFkMzEtODdlMDA4ZWVlYjZjIiwibWVyY2hhbnRfaWQiOiJtZXJjaGFudF8xNzI0NjYxNjQ3Iiwicm9sZV9pZCI6Im9yZ19hZG1pbiIsImV4cCI6MTcyNTYyMDQ1MSwib3JnX2lkIjoib3JnX3pXYVBBZDBxOGtxcnRpQzN4U3o3IiwicHJvZmlsZV9pZCI6bnVsbH0.VXacvv96BOspj033EFwKvaYdyqjlSNIzMFXYQ-DvsvE' \
--header 'Cookie: login_token=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJ1c2VyX2lkIjoiZjNmYzEyNjYtZDQ2MS00NWU3LWFkMzEtODdlMDA4ZWVlYjZjIiwibWVyY2hhbnRfaWQiOiJtZXJjaGFudF8xNzI0NjYxNjQ3Iiwicm9sZV9pZCI6Im9yZ19hZG1pbiIsImV4cCI6MTcyNTYyMDQ1MSwib3JnX2lkIjoib3JnX3pXYVBBZDBxOGtxcnRpQzN4U3o3IiwicHJvZmlsZV9pZCI6bnVsbH0.VXacvv96BOspj033EFwKvaYdyqjlSNIzMFXYQ-DvsvE'
``` 


Response
```
{
    "status_with_count": {
        "pending": 0,
        "transaction_failure": 0,
        "success": 40,
        "failure": 0,
        "manual_review": 0
    }
}
``` 

<!--
Did you write an integration/unit/API test to verify the code changes?
Or did you test this change manually (provide relevant screenshots)?
-->


## 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


Testing:
Request
```
curl --location 'http://localhost:8080/refunds/aggregate?start_time=2022-08-28T18%3A30%3A00Z' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJ1c2VyX2lkIjoiZjNmYzEyNjYtZDQ2MS00NWU3LWFkMzEtODdlMDA4ZWVlYjZjIiwibWVyY2hhbnRfaWQiOiJtZXJjaGFudF8xNzI0NjYxNjQ3Iiwicm9sZV9pZCI6Im9yZ19hZG1pbiIsImV4cCI6MTcyNTYyMDQ1MSwib3JnX2lkIjoib3JnX3pXYVBBZDBxOGtxcnRpQzN4U3o3IiwicHJvZmlsZV9pZCI6bnVsbH0.VXacvv96BOspj033EFwKvaYdyqjlSNIzMFXYQ-DvsvE' \
--header 'Cookie: login_token=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJ1c2VyX2lkIjoiZjNmYzEyNjYtZDQ2MS00NWU3LWFkMzEtODdlMDA4ZWVlYjZjIiwibWVyY2hhbnRfaWQiOiJtZXJjaGFudF8xNzI0NjYxNjQ3Iiwicm9sZV9pZCI6Im9yZ19hZG1pbiIsImV4cCI6MTcyNTYyMDQ1MSwib3JnX2lkIjoib3JnX3pXYVBBZDBxOGtxcnRpQzN4U3o3IiwicHJvZmlsZV9pZCI6bnVsbH0.VXacvv96BOspj033EFwKvaYdyqjlSNIzMFXYQ-DvsvE'
``` 


Response
```
{
    "status_with_count": {
        "pending": 0,
        "transaction_failure": 0,
        "success": 40,
        "failure": 0,
        "manual_review": 0
    }
}
``` 



Patch:
diff --git a/api-reference-v2/openapi_spec.json b/api-reference-v2/openapi_spec.json
index 6b9ba4afd01..923881ae376 100644
--- a/api-reference-v2/openapi_spec.json
+++ b/api-reference-v2/openapi_spec.json
@@ -15715,6 +15715,22 @@
           }
         }
       },
+      "RefundAggregateResponse": {
+        "type": "object",
+        "required": [
+          "status_with_count"
+        ],
+        "properties": {
+          "status_with_count": {
+            "type": "object",
+            "description": "The list of refund status with their count",
+            "additionalProperties": {
+              "type": "integer",
+              "format": "int64"
+            }
+          }
+        }
+      },
       "RefundListRequest": {
         "allOf": [
           {
diff --git a/crates/api_models/src/events/refund.rs b/crates/api_models/src/events/refund.rs
index 0717b66c6bf..d180753735f 100644
--- a/crates/api_models/src/events/refund.rs
+++ b/crates/api_models/src/events/refund.rs
@@ -1,9 +1,9 @@
 use common_utils::events::{ApiEventMetric, ApiEventsType};
 
 use crate::refunds::{
-    RefundListFilters, RefundListMetaData, RefundListRequest, RefundListResponse,
-    RefundManualUpdateRequest, RefundRequest, RefundResponse, RefundUpdateRequest,
-    RefundsRetrieveRequest,
+    RefundAggregateResponse, RefundListFilters, RefundListMetaData, RefundListRequest,
+    RefundListResponse, RefundManualUpdateRequest, RefundRequest, RefundResponse,
+    RefundUpdateRequest, RefundsRetrieveRequest,
 };
 
 impl ApiEventMetric for RefundRequest {
@@ -66,6 +66,12 @@ impl ApiEventMetric for RefundListResponse {
     }
 }
 
+impl ApiEventMetric for RefundAggregateResponse {
+    fn get_api_event_type(&self) -> Option<ApiEventsType> {
+        Some(ApiEventsType::ResourceListAPI)
+    }
+}
+
 impl ApiEventMetric for RefundListMetaData {
     fn get_api_event_type(&self) -> Option<ApiEventsType> {
         Some(ApiEventsType::ResourceListAPI)
diff --git a/crates/api_models/src/refunds.rs b/crates/api_models/src/refunds.rs
index 649c0dbf5cf..edc0e75bdf2 100644
--- a/crates/api_models/src/refunds.rs
+++ b/crates/api_models/src/refunds.rs
@@ -234,6 +234,12 @@ pub struct RefundListFilters {
     pub refund_status: Vec<enums::RefundStatus>,
 }
 
+#[derive(Clone, Debug, serde::Serialize, ToSchema)]
+pub struct RefundAggregateResponse {
+    /// The list of refund status with their count
+    pub status_with_count: HashMap<enums::RefundStatus, i64>,
+}
+
 /// The status for refunds
 #[derive(
     Debug,
diff --git a/crates/openapi/src/openapi.rs b/crates/openapi/src/openapi.rs
index f12539cc232..301a51089cf 100644
--- a/crates/openapi/src/openapi.rs
+++ b/crates/openapi/src/openapi.rs
@@ -458,6 +458,7 @@ Never share your secret api keys. Keep them guarded and secure.
         api_models::payment_methods::PaymentMethodCollectLinkResponse,
         api_models::refunds::RefundListRequest,
         api_models::refunds::RefundListResponse,
+        api_models::refunds::RefundAggregateResponse,
         api_models::payments::TimeRange,
         api_models::payments::AmountFilter,
         api_models::mandates::MandateRevokedResponse,
diff --git a/crates/openapi/src/openapi_v2.rs b/crates/openapi/src/openapi_v2.rs
index c58e4c5183b..e221fab6ac9 100644
--- a/crates/openapi/src/openapi_v2.rs
+++ b/crates/openapi/src/openapi_v2.rs
@@ -376,6 +376,7 @@ Never share your secret api keys. Keep them guarded and secure.
         api_models::payment_methods::PaymentMethodCollectLinkResponse,
         api_models::refunds::RefundListRequest,
         api_models::refunds::RefundListResponse,
+        api_models::refunds::RefundAggregateResponse,
         api_models::payments::TimeRange,
         api_models::payments::AmountFilter,
         api_models::mandates::MandateRevokedResponse,
diff --git a/crates/router/src/core/refunds.rs b/crates/router/src/core/refunds.rs
index 1761c0bad03..8eed5ecfda5 100644
--- a/crates/router/src/core/refunds.rs
+++ b/crates/router/src/core/refunds.rs
@@ -1034,6 +1034,32 @@ pub async fn get_filters_for_refunds(
     ))
 }
 
+#[instrument(skip_all)]
+#[cfg(feature = "olap")]
+pub async fn get_aggregates_for_refunds(
+    state: SessionState,
+    merchant: domain::MerchantAccount,
+    time_range: api::TimeRange,
+) -> RouterResponse<api_models::refunds::RefundAggregateResponse> {
+    let db = state.store.as_ref();
+    let refund_status_with_count = db
+        .get_refund_status_with_count(merchant.get_id(), &time_range, merchant.storage_scheme)
+        .await
+        .change_context(errors::ApiErrorResponse::InternalServerError)
+        .attach_printable("Failed to find status count")?;
+    let mut status_map: HashMap<enums::RefundStatus, i64> =
+        refund_status_with_count.into_iter().collect();
+    for status in enums::RefundStatus::iter() {
+        status_map.entry(status).or_default();
+    }
+
+    Ok(services::ApplicationResponse::Json(
+        api_models::refunds::RefundAggregateResponse {
+            status_with_count: status_map,
+        },
+    ))
+}
+
 impl ForeignFrom<storage::Refund> for api::RefundResponse {
     fn foreign_from(refund: storage::Refund) -> Self {
         let refund = refund;
diff --git a/crates/router/src/db/kafka_store.rs b/crates/router/src/db/kafka_store.rs
index 31ed4ad93e2..e34339fbc7b 100644
--- a/crates/router/src/db/kafka_store.rs
+++ b/crates/router/src/db/kafka_store.rs
@@ -2389,6 +2389,18 @@ impl RefundInterface for KafkaStore {
             .await
     }
 
+    #[cfg(feature = "olap")]
+    async fn get_refund_status_with_count(
+        &self,
+        merchant_id: &id_type::MerchantId,
+        constraints: &api_models::payments::TimeRange,
+        storage_scheme: MerchantStorageScheme,
+    ) -> CustomResult<Vec<(common_enums::RefundStatus, i64)>, errors::StorageError> {
+        self.diesel_store
+            .get_refund_status_with_count(merchant_id, constraints, storage_scheme)
+            .await
+    }
+
     #[cfg(feature = "olap")]
     async fn get_total_count_of_refunds(
         &self,
diff --git a/crates/router/src/db/refund.rs b/crates/router/src/db/refund.rs
index e6b46c5af5c..a78a4122f4a 100644
--- a/crates/router/src/db/refund.rs
+++ b/crates/router/src/db/refund.rs
@@ -1,5 +1,5 @@
 #[cfg(feature = "olap")]
-use std::collections::HashSet;
+use std::collections::{HashMap, HashSet};
 
 #[cfg(feature = "olap")]
 use common_utils::types::MinorUnit;
@@ -83,6 +83,14 @@ pub trait RefundInterface {
         storage_scheme: enums::MerchantStorageScheme,
     ) -> CustomResult<api_models::refunds::RefundListMetaData, errors::StorageError>;
 
+    #[cfg(feature = "olap")]
+    async fn get_refund_status_with_count(
+        &self,
+        merchant_id: &common_utils::id_type::MerchantId,
+        constraints: &api_models::payments::TimeRange,
+        storage_scheme: enums::MerchantStorageScheme,
+    ) -> CustomResult<Vec<(common_enums::RefundStatus, i64)>, errors::StorageError>;
+
     #[cfg(feature = "olap")]
     async fn get_total_count_of_refunds(
         &self,
@@ -250,6 +258,21 @@ mod storage {
             .await
             .map_err(|error|report!(errors::StorageError::from(error)))
         }
+
+        #[cfg(feature = "olap")]
+        #[instrument(skip_all)]
+        async fn get_refund_status_with_count(
+            &self,
+            merchant_id: &common_utils::id_type::MerchantId,
+            time_range: &api_models::payments::TimeRange,
+            _storage_scheme: enums::MerchantStorageScheme,
+        ) -> CustomResult<Vec<(common_enums::RefundStatus, i64)>, errors::StorageError> {
+            let conn = connection::pg_connection_read(self).await?;
+            <diesel_models::refund::Refund as storage_types::RefundDbExt>::get_refund_status_with_count(&conn, merchant_id, time_range)
+            .await
+            .map_err(|error|report!(errors::StorageError::from(error)))
+        }
+
         #[cfg(feature = "olap")]
         #[instrument(skip_all)]
         async fn get_total_count_of_refunds(
@@ -783,6 +806,20 @@ mod storage {
                         .map_err(|error|report!(errors::StorageError::from(error)))
         }
 
+        #[cfg(feature = "olap")]
+        #[instrument(skip_all)]
+        async fn get_refund_status_with_count(
+            &self,
+            merchant_id: &common_utils::id_type::MerchantId,
+            constraints: &api_models::payments::TimeRange,
+            _storage_scheme: enums::MerchantStorageScheme,
+        ) -> CustomResult<Vec<(common_enums::RefundStatus, i64)>, errors::StorageError> {
+            let conn = connection::pg_connection_read(self).await?;
+            <diesel_models::refund::Refund as storage_types::RefundDbExt>::get_refund_status_with_count(&conn, merchant_id, constraints)
+            .await
+            .map_err(|error| report!(errors::StorageError::from(error)))
+        }
+
         #[cfg(feature = "olap")]
         #[instrument(skip_all)]
         async fn get_total_count_of_refunds(
@@ -1112,6 +1149,41 @@ impl RefundInterface for MockDb {
         Ok(refund_meta_data)
     }
 
+    #[cfg(feature = "olap")]
+    async fn get_refund_status_with_count(
+        &self,
+        _merchant_id: &common_utils::id_type::MerchantId,
+        time_range: &api_models::payments::TimeRange,
+        _storage_scheme: enums::MerchantStorageScheme,
+    ) -> CustomResult<Vec<(api_models::enums::RefundStatus, i64)>, errors::StorageError> {
+        let refunds = self.refunds.lock().await;
+
+        let start_time = time_range.start_time;
+        let end_time = time_range
+            .end_time
+            .unwrap_or_else(common_utils::date_time::now);
+
+        let filtered_refunds = refunds
+            .iter()
+            .filter(|refund| refund.created_at >= start_time && refund.created_at <= end_time)
+            .cloned()
+            .collect::<Vec<diesel_models::refund::Refund>>();
+
+        let mut refund_status_counts: HashMap<api_models::enums::RefundStatus, i64> =
+            HashMap::new();
+
+        for refund in filtered_refunds {
+            *refund_status_counts
+                .entry(refund.refund_status)
+                .or_insert(0) += 1;
+        }
+
+        let result: Vec<(api_models::enums::RefundStatus, i64)> =
+            refund_status_counts.into_iter().collect();
+
+        Ok(result)
+    }
+
     #[cfg(feature = "olap")]
     async fn get_total_count_of_refunds(
         &self,
diff --git a/crates/router/src/routes/app.rs b/crates/router/src/routes/app.rs
index 4f8e269dff9..56991c6da1d 100644
--- a/crates/router/src/routes/app.rs
+++ b/crates/router/src/routes/app.rs
@@ -991,6 +991,7 @@ impl Refunds {
                 .service(web::resource("/profile/list").route(web::post().to(refunds_list_profile)))
                 .service(web::resource("/filter").route(web::post().to(refunds_filter_list)))
                 .service(web::resource("/v2/filter").route(web::get().to(get_refunds_filters)))
+                .service(web::resource("/aggregate").route(web::get().to(get_refunds_aggregates)))
                 .service(
                     web::resource("/v2/profile/filter")
                         .route(web::get().to(get_refunds_filters_profile)),
diff --git a/crates/router/src/routes/lock_utils.rs b/crates/router/src/routes/lock_utils.rs
index 13148801aa9..0cdfd90e998 100644
--- a/crates/router/src/routes/lock_utils.rs
+++ b/crates/router/src/routes/lock_utils.rs
@@ -153,6 +153,7 @@ impl From<Flow> for ApiIdentifier {
             | Flow::RefundsUpdate
             | Flow::RefundsList
             | Flow::RefundsFilters
+            | Flow::RefundsAggregate
             | Flow::RefundsManualUpdate => Self::Refunds,
 
             Flow::FrmFulfillment
diff --git a/crates/router/src/routes/refunds.rs b/crates/router/src/routes/refunds.rs
index 4b964dba626..2f5cb332837 100644
--- a/crates/router/src/routes/refunds.rs
+++ b/crates/router/src/routes/refunds.rs
@@ -421,6 +421,36 @@ pub async fn get_refunds_filters_profile(
     .await
 }
 
+#[instrument(skip_all, fields(flow = ?Flow::RefundsAggregate))]
+#[cfg(feature = "olap")]
+pub async fn get_refunds_aggregates(
+    state: web::Data<AppState>,
+    req: HttpRequest,
+    query_params: web::Query<api_models::payments::TimeRange>,
+) -> HttpResponse {
+    let flow = Flow::RefundsAggregate;
+    let query_params = query_params.into_inner();
+    Box::pin(api::server_wrap(
+        flow,
+        state,
+        &req,
+        query_params,
+        |state, auth: auth::AuthenticationData, req, _| {
+            get_aggregates_for_refunds(state, auth.merchant_account, req)
+        },
+        auth::auth_type(
+            &auth::HeaderAuth(auth::ApiKeyAuth),
+            &auth::JWTAuth {
+                permission: Permission::RefundRead,
+                minimum_entity_level: EntityType::Merchant,
+            },
+            req.headers(),
+        ),
+        api_locking::LockAction::NotApplicable,
+    ))
+    .await
+}
+
 #[instrument(skip_all, fields(flow = ?Flow::RefundsManualUpdate))]
 #[cfg(feature = "olap")]
 pub async fn refunds_manual_update(
diff --git a/crates/router/src/types/storage/refund.rs b/crates/router/src/types/storage/refund.rs
index 1860771d026..38556f77506 100644
--- a/crates/router/src/types/storage/refund.rs
+++ b/crates/router/src/types/storage/refund.rs
@@ -36,6 +36,12 @@ pub trait RefundDbExt: Sized {
         merchant_id: &common_utils::id_type::MerchantId,
         refund_list_details: &api_models::refunds::RefundListRequest,
     ) -> CustomResult<i64, errors::DatabaseError>;
+
+    async fn get_refund_status_with_count(
+        conn: &PgPooledConn,
+        merchant_id: &common_utils::id_type::MerchantId,
+        time_range: &api_models::payments::TimeRange,
+    ) -> CustomResult<Vec<(RefundStatus, i64)>, errors::DatabaseError>;
 }
 
 #[async_trait::async_trait]
@@ -288,4 +294,33 @@ impl RefundDbExt for Refund {
             .change_context(errors::DatabaseError::NotFound)
             .attach_printable_lazy(|| "Error filtering count of refunds")
     }
+
+    async fn get_refund_status_with_count(
+        conn: &PgPooledConn,
+        merchant_id: &common_utils::id_type::MerchantId,
+        time_range: &api_models::payments::TimeRange,
+    ) -> CustomResult<Vec<(RefundStatus, i64)>, errors::DatabaseError> {
+        let mut query = <Self as HasTable>::table()
+            .group_by(dsl::refund_status)
+            .select((dsl::refund_status, diesel::dsl::count_star()))
+            .filter(dsl::merchant_id.eq(merchant_id.to_owned()))
+            .into_boxed();
+
+        query = query.filter(dsl::created_at.ge(time_range.start_time));
+
+        query = match time_range.end_time {
+            Some(ending_at) => query.filter(dsl::created_at.le(ending_at)),
+            None => query,
+        };
+
+        logger::debug!(filter = %diesel::debug_query::<diesel::pg::Pg,_>(&query).to_string());
+
+        db_metrics::track_database_call::<<Self as HasTable>::Table, _, _>(
+            query.get_results_async::<(RefundStatus, i64)>(conn),
+            db_metrics::DatabaseOperation::Count,
+        )
+        .await
+        .change_context(errors::DatabaseError::NotFound)
+        .attach_printable_lazy(|| "Error filtering status count of refunds")
+    }
 }
diff --git a/crates/router_env/src/logger/types.rs b/crates/router_env/src/logger/types.rs
index 79ab704fbbe..6126ca9afbe 100644
--- a/crates/router_env/src/logger/types.rs
+++ b/crates/router_env/src/logger/types.rs
@@ -212,6 +212,8 @@ pub enum Flow {
     RefundsList,
     /// Refunds filters flow
     RefundsFilters,
+    /// Refunds aggregates flow
+    RefundsAggregate,
     // Retrieve forex flow.
     RetrieveForexFlow,
     /// Toggles recon service for a merchant.