Repository: digitalfabrik/entitlementcard
Issue: [Bavaria]Notification of applications sends to all users

**Describe the bug**
When a new application arrives a notification will be send to users of this region that have activated this in user settings.
But only the users of this specific region should get a notification

**To Reproduce**
Steps to reproduce the behavior:
1. Enable notifications in user settings
2. Create an application for another region than yours
3. Check your mails

**Expected behavior**
Notifications will only be send if the user belongs to the region of the application


Changed files:
backend/src/main/kotlin/app/ehrenamtskarte/backend/mail/Mailer.kt
backend/src/main/kotlin/app/ehrenamtskarte/backend/auth/database/repos/AdministratorsRepository.kt
backend/src/main/kotlin/app/ehrenamtskarte/backend/application/webservice/EakApplicationMutationService.kt

diff --git a/backend/src/main/kotlin/app/ehrenamtskarte/backend/application/webservice/EakApplicationMutationService.kt b/backend/src/main/kotlin/app/ehrenamtskarte/backend/application/webservice/EakApplicationMutationService.kt
index 4e5f014e..5357a40d 100644
--- a/backend/src/main/kotlin/app/ehrenamtskarte/backend/application/webservice/EakApplicationMutationService.kt
+++ b/backend/src/main/kotlin/app/ehrenamtskarte/backend/application/webservice/EakApplicationMutationService.kt
@@ -2,6 +2,7 @@ package app.ehrenamtskarte.backend.application.webservice
 
 import app.ehrenamtskarte.backend.application.database.ApplicationEntity
 import app.ehrenamtskarte.backend.application.database.repos.ApplicationRepository
+import app.ehrenamtskarte.backend.application.database.repos.ApplicationRepository.getApplicationByApplicationVerificationAccessKey
 import app.ehrenamtskarte.backend.application.webservice.schema.create.Application
 import app.ehrenamtskarte.backend.auth.database.AdministratorEntity
 import app.ehrenamtskarte.backend.auth.service.Authorizer.mayDeleteApplicationsInRegion
@@ -68,7 +69,7 @@ class EakApplicationMutationService {
                 dataFetcherResultBuilder.error(exception)
             }
         }
-        Mailer.sendNotificationForApplicationMails(project, backendConfig, projectConfig)
+        Mailer.sendNotificationForApplicationMails(project, backendConfig, projectConfig, regionId)
 
         return dataFetcherResultBuilder.data(true).build()
     }
@@ -111,13 +112,14 @@ class EakApplicationMutationService {
         verified: Boolean,
         dfe: DataFetchingEnvironment
     ): Boolean {
+        val application = transaction { getApplicationByApplicationVerificationAccessKey(accessKey) }
         return transaction {
             if (verified) {
                 val context = dfe.getContext<GraphQLContext>()
                 val backendConfig = context.backendConfiguration
                 val projectConfig = backendConfig.projects.first { it.id == project }
                 val successful = ApplicationRepository.verifyApplicationVerification(accessKey)
-                Mailer.sendNotificationForVerificationMails(project, backendConfig, projectConfig)
+                Mailer.sendNotificationForVerificationMails(project, backendConfig, projectConfig, application.regionId)
 
                 successful
             } else {
diff --git a/backend/src/main/kotlin/app/ehrenamtskarte/backend/auth/database/repos/AdministratorsRepository.kt b/backend/src/main/kotlin/app/ehrenamtskarte/backend/auth/database/repos/AdministratorsRepository.kt
index 8fff2c6d..1da238c4 100644
--- a/backend/src/main/kotlin/app/ehrenamtskarte/backend/auth/database/repos/AdministratorsRepository.kt
+++ b/backend/src/main/kotlin/app/ehrenamtskarte/backend/auth/database/repos/AdministratorsRepository.kt
@@ -122,17 +122,17 @@ object AdministratorsRepository {
         administrator.notificationOnVerification = notificationSettings.notificationOnVerification
     }
 
-    fun getNotificationRecipientsForApplication(project: String): List<AdministratorEntity> =
+    fun getNotificationRecipientsForApplication(project: String, regionId: Int): List<AdministratorEntity> =
         transaction {
             (Administrators innerJoin Projects).select {
-                (Projects.project eq project) and (Administrators.notificationOnApplication eq true)
+                (Projects.project eq project) and (Administrators.notificationOnApplication eq true) and (Administrators.regionId eq regionId) and (Administrators.deleted eq false)
             }.let { AdministratorEntity.wrapRows(it) }.toList()
         }
 
-    fun getNotificationRecipientsForVerification(project: String): List<AdministratorEntity> =
+    fun getNotificationRecipientsForVerification(project: String, regionId: Int): List<AdministratorEntity> =
         transaction {
             (Administrators innerJoin Projects).select {
-                (Projects.project eq project) and (Administrators.notificationOnVerification eq true)
+                (Projects.project eq project) and (Administrators.notificationOnVerification eq true) and (Administrators.regionId eq regionId) and (Administrators.deleted eq false)
             }.let { AdministratorEntity.wrapRows(it) }.toList()
         }
 }
diff --git a/backend/src/main/kotlin/app/ehrenamtskarte/backend/mail/Mailer.kt b/backend/src/main/kotlin/app/ehrenamtskarte/backend/mail/Mailer.kt
index a9dc37a6..76bdf165 100644
--- a/backend/src/main/kotlin/app/ehrenamtskarte/backend/mail/Mailer.kt
+++ b/backend/src/main/kotlin/app/ehrenamtskarte/backend/mail/Mailer.kt
@@ -67,9 +67,10 @@ object Mailer {
     fun sendNotificationForApplicationMails(
         project: String,
         backendConfig: BackendConfiguration,
-        projectConfig: ProjectConfig
+        projectConfig: ProjectConfig,
+        regionId: Int
     ) {
-        val recipients = AdministratorsRepository.getNotificationRecipientsForApplication(project)
+        val recipients = AdministratorsRepository.getNotificationRecipientsForApplication(project, regionId)
         val message = """
         Guten Tag,
 
@@ -99,9 +100,10 @@ object Mailer {
     fun sendNotificationForVerificationMails(
         project: String,
         backendConfig: BackendConfiguration,
-        projectConfig: ProjectConfig
+        projectConfig: ProjectConfig,
+        regionId: Int
     ) {
-        val recipients = AdministratorsRepository.getNotificationRecipientsForVerification(project)
+        val recipients = AdministratorsRepository.getNotificationRecipientsForVerification(project, regionId)
         val message = """
         Guten Tag,