libeufin

DatabaseTest.kt (50741B)

---

 /*
  * This file is part of LibEuFin.
  * Copyright (C) 2024, 2025, 2026 Taler Systems S.A.
 
  * LibEuFin is free software; you can redistribute it and/or modify
  * it under the terms of the GNU Affero General Public License as
  * published by the Free Software Foundation; either version 3, or
  * (at your option) any later version.
 
  * LibEuFin is distributed in the hope that it will be useful, but
  * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
  * or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Affero General
  * Public License for more details.
 
  * You should have received a copy of the GNU Affero General Public
  * License along with LibEuFin; see the file COPYING.  If not, see
  * <http://www.gnu.org/licenses/>
  */
 
 import org.junit.Test
 import tech.libeufin.common.*
 import tech.libeufin.common.db.*
 import tech.libeufin.nexus.AccountType
 import tech.libeufin.nexus.NexusIngestConfig
 import tech.libeufin.nexus.iso20022.*
 import tech.libeufin.nexus.cli.*
 import tech.libeufin.nexus.db.*
 import tech.libeufin.nexus.db.PaymentDAO.*
 import tech.libeufin.nexus.db.InitiatedDAO.*
 import tech.libeufin.nexus.db.TransferDAO.*
 import tech.libeufin.ebics.*
 import java.time.Instant
 import java.util.UUID;
 import kotlin.test.*
 
 suspend fun Database.checkInCount(nbIncoming: Int, nbBounce: Int, nbTalerable: Int) = serializable(
     """
         SELECT (SELECT count(*) FROM incoming_transactions) AS incoming,
                (SELECT count(*) FROM bounced_transactions) AS bounce,
                (SELECT count(*) FROM talerable_incoming_transactions) AS talerable;
     """
 ) {
     one {
         assertEquals(
             Triple(nbIncoming, nbBounce, nbTalerable),
             Triple(it.getInt("incoming"), it.getInt("bounce"), it.getInt("talerable"))
         )
     }
 }
 
 suspend fun Database.checkOutCount(nbIncoming: Int, nbTalerable: Int) = serializable(
     """
         SELECT (SELECT count(*) FROM outgoing_transactions) AS incoming,
                (SELECT count(*) FROM talerable_outgoing_transactions) AS talerable;
     """
 ) {
     one {
         assertEquals(
             Pair(nbIncoming, nbTalerable),
             Pair(it.getInt("incoming"), it.getInt("talerable"))
         )
     }
 }
 
 sealed interface Status {
     data object Simple : Status
     data object Pending : Status
     data object Bounced : Status
     data object Incomplete : Status
     data class Reserve(val key: EddsaPublicKey) : Status
     data class Kyc(val key: EddsaPublicKey) : Status
 }
 
 suspend fun Database.checkIn(vararg expected: Status) {
     val current = this.serializable(
         """
             SELECT pending_recurrent_incoming_transactions.authorization_pub IS NOT NULL, initiated_outgoing_transaction_id IS NOT NULL, debit_payto IS NULL OR subject IS NULL, type, metadata 
             FROM incoming_transactions
                 LEFT JOIN talerable_incoming_transactions USING (incoming_transaction_id)
                 LEFT JOIN pending_recurrent_incoming_transactions USING (incoming_transaction_id)
                 LEFT JOIN bounced_transactions USING (incoming_transaction_id)
             ORDER BY incoming_transaction_id
         """
     ) {
         all {
             if (it.getBoolean(1)) {
                 Status.Pending
             } else if (it.getBoolean(2)) {
                 Status.Bounced
             } else if (it.getBoolean(3)) {
                 Status.Incomplete
             } else {
                 when (it.getOptEnum<TransferType>(4)) {
                     null -> Status.Simple
                     TransferType.reserve -> Status.Reserve(EddsaPublicKey(it.getBytes(5)))
                     TransferType.kyc -> Status.Kyc(EddsaPublicKey(it.getBytes(5)))
                 }
             }
         }.toList()
     }
     assertContentEquals(listOf(*expected), current)
 }
 
 class OutgoingPaymentsTest {
     @Test
     fun register() = setup { db, _ -> 
         // Register initiated transaction
         for (subject in sequenceOf(
             "initiated by nexus",
             "${ShortHashCode.rand()} https://exchange.com/"
         )) {
             val pay = genOutPay(subject)
             assertIs<PaymentInitiationResult.Success>(
                 db.initiated.create(genInitPay(pay.id.endToEndId!!, subject))
             )
             val first = registerOutgoingPayment(db, pay)
             assertEquals(OutgoingRegistrationResult(id = first.id, initiated = true, new = true), first)
             assertEquals(
                 OutgoingRegistrationResult(id = first.id, initiated = true, new = false),
                 registerOutgoingPayment(db, pay)
             )
 
             val refOnly = pay.copy(id = OutgoingId(null, null, acctSvcrRef = pay.id.endToEndId))
             val second = registerOutgoingPayment(db, refOnly)
             assertEquals(OutgoingRegistrationResult(id = first.id + 1, initiated = false, new = true), second)
             assertEquals(
                 OutgoingRegistrationResult(id = second.id, initiated = false, new = false),
                 registerOutgoingPayment(db, refOnly)
             )
         }
         db.checkOutCount(nbIncoming = 4, nbTalerable = 1)
 
         // Register unknown
         for (subject in sequenceOf(
             "not initiated by nexus",
             "${ShortHashCode.rand()} https://exchange.com/"
         )) {
             val pay = genOutPay(subject)
             val first = registerOutgoingPayment(db, pay)
             assertEquals(OutgoingRegistrationResult(id = first.id, initiated = false, new = true), first)
             assertEquals(
                 OutgoingRegistrationResult(id = first.id, initiated = false, new = false),
                 registerOutgoingPayment(db, pay)
             )
         }
         db.checkOutCount(nbIncoming = 6, nbTalerable = 2)
 
         // Register wtid reuse
         val wtid = ShortHashCode.rand()
         for (subject in sequenceOf(
             "$wtid https://exchange.com/",
             "$wtid https://exchange.com/"
         )) {
             val pay = genOutPay(subject)
             val first = registerOutgoingPayment(db, pay)
             assertEquals(OutgoingRegistrationResult(id = first.id, initiated = false, new = true), first)
             assertEquals(
                 OutgoingRegistrationResult(id = first.id, initiated = false, new = false),
                 db.payment.registerOutgoing(pay, null, null, null)
             )
         }
         db.checkOutCount(nbIncoming = 8, nbTalerable = 3)
     }
 
     @Test
     fun registerBatch() = setup { db, _ ->
         // Init batch
         val wtid = ShortHashCode.rand()
         for (subject in sequenceOf(
             "initiated by nexus",
             "${ShortHashCode.rand()} https://exchange.com/",
             "$wtid https://exchange.com/",
             "$wtid https://exchange.com/"
         )) {
             assertIs<PaymentInitiationResult.Success>(
                 db.initiated.create(genInitPay(randEbicsId(), subject=subject))
             )
         }
         db.initiated.batch(Instant.now(), "BATCH", false)
 
         // Register batch
         registerOutgoingBatch(db, OutgoingBatch("BATCH", Instant.now()));
         db.checkOutCount(nbIncoming = 4, nbTalerable = 2)
 
         // Test manual ack
         val txs = List(3) { nb ->
             assertIs<PaymentInitiationResult.Success>(
                 db.initiated.create(genInitPay(randEbicsId(), subject="tx $nb"))
             ).id
         }
 
         // Check not sent without ack
         db.initiated.batch(Instant.now(), "BATCH_MANUAL", true)
         registerOutgoingBatch(db, OutgoingBatch("BATCH_MANUAL", Instant.now()));
         db.checkOutCount(nbIncoming = 4, nbTalerable = 2)
 
         // Check sent with ack
         for (tx in txs) {
             db.initiated.ack(tx)
         }
         db.initiated.batch(Instant.now(), "BATCH_MANUAL", true)
         registerOutgoingBatch(db, OutgoingBatch("BATCH_MANUAL", Instant.now()));
         db.checkOutCount(nbIncoming = 7, nbTalerable = 2)
     }
 }
 
 class IncomingPaymentsTest {
     // Tests creating and bouncing incoming payments in one DB transaction
     @Test
     fun bounce() = setup { db, _ -> 
         // creating and bouncing one incoming transaction.
         val payment = genInPay("incoming and bounced")
         val id = randEbicsId()
         db.payment.registerMalformedIncoming(
             payment,
             TalerAmount("KUDOS:2.53"),
             id,
             Instant.now(),
             "manual bounce"
         ).run {
             assertIs<IncomingBounceRegistrationResult.Success>(this)
             assertTrue(new)
             assertEquals(id, bounceId)
         }
         db.payment.registerMalformedIncoming(
             payment,
             TalerAmount("KUDOS:2.53"),
             randEbicsId(),
             Instant.now(),
             "manual bounce"
         ).run {
             assertIs<IncomingBounceRegistrationResult.Success>(this)
             assertFalse(new)
             assertEquals(id, bounceId)
         }
         db.conn {
             // Checking one incoming got created
             val checkIncoming = it.talerStatement("""
                 SELECT (amount).val as amount_value, (amount).frac as amount_frac 
                 FROM incoming_transactions WHERE incoming_transaction_id = 1
             """).executeQuery()
             assertTrue(checkIncoming.next())
             assertEquals(payment.amount.value, checkIncoming.getLong("amount_value"))
             assertEquals(payment.amount.frac, checkIncoming.getInt("amount_frac"))
             // Checking the bounced table got its row.
             val checkBounced = it.talerStatement("""
                 SELECT 1 FROM bounced_transactions 
                 WHERE incoming_transaction_id = 1 AND initiated_outgoing_transaction_id = 1
             """).executeQuery()
             assertTrue(checkBounced.next())
             // check the related initiated payment exists.
             val checkInitiated = it.talerStatement("""
                 SELECT
                     (amount).val as amount_value
                     ,(amount).frac as amount_frac
                 FROM initiated_outgoing_transactions
                 WHERE initiated_outgoing_transaction_id = 1
             """).executeQuery()
             assertTrue(checkInitiated.next())
             assertEquals(
                 53000000,
                 checkInitiated.getInt("amount_frac")
             )
             assertEquals(
                 2,
                 checkInitiated.getInt("amount_value")
             )
         }
     }
 
     // Test creating an incoming reserve transaction without and ID and reconcile it later again
     @Test
     fun simple() = setup { db, _ ->
         val cfg = NexusIngestConfig.default(AccountType.exchange)
         val subject = "test"  
         
         // Register
         val incoming = genInPay(subject)
         registerIncomingPayment(db, cfg, incoming)
         db.checkIn(Status.Bounced)
 
         // Idempotent
         registerIncomingPayment(db, cfg, incoming)
         db.checkIn(Status.Bounced)
 
         // No key reuse
         registerIncomingPayment(db, cfg, genInPay(subject, "KUDOS:9"))
         registerIncomingPayment(db, cfg, genInPay("another $subject"))
         db.checkIn(Status.Bounced, Status.Bounced, Status.Bounced)
 
         // Admin balance adjust is ignored
         registerIncomingPayment(db, cfg, genInPay("ADMIN BALANCE ADJUST"))
         db.checkIn(Status.Bounced, Status.Bounced, Status.Bounced, Status.Simple)
 
         val original = genInPay("test 2")
         val incomplete = original.copy(subject = null, debtor = null)
         // Register incomplete transaction
         registerIncomingPayment(db, cfg, incomplete)
         db.checkIn(Status.Bounced, Status.Bounced, Status.Bounced, Status.Simple, Status.Incomplete)
 
         // Idempotent
         registerIncomingPayment(db, cfg, incomplete)
         db.checkIn(Status.Bounced, Status.Bounced, Status.Bounced, Status.Simple, Status.Incomplete)
 
         // Recover info when complete
         registerIncomingPayment(db, cfg, original)
         db.checkIn(Status.Bounced, Status.Bounced, Status.Bounced, Status.Simple, Status.Bounced)
     }
 
     // Test creating an incoming reserve taler transaction without and ID and reconcile it later again
     @Test
     fun talerable() = setup { db, _ ->
         val cfg = NexusIngestConfig.default(AccountType.exchange)
         val key = EddsaPublicKey.randEdsaKey()
         val subject = "test with $key reserve pub"  
         
         // Register
         val incoming = genInPay(subject)
         registerIncomingPayment(db, cfg, incoming)
         db.checkIn(Status.Reserve(key))
 
         // Idempotent
         registerIncomingPayment(db, cfg, incoming)
         db.checkIn(Status.Reserve(key))
 
         // Key reuse is bounced
         registerIncomingPayment(db, cfg, genInPay(subject, "KUDOS:9"))
         registerIncomingPayment(db, cfg, genInPay("another $subject"))
         db.checkIn(Status.Reserve(key), Status.Bounced, Status.Bounced)
 
         // Admin balance adjust is ignored
         registerIncomingPayment(db, cfg, genInPay("ADMIN BALANCE ADJUST"))
         db.checkIn(Status.Reserve(key), Status.Bounced, Status.Bounced, Status.Simple)
 
         val newKey = EddsaPublicKey.randEdsaKey()
         val original = genInPay("test 2 with $newKey reserve pub")
         val incomplete = original.copy(subject = null, debtor = null)
         // Register incomplete transaction
         registerIncomingPayment(db, cfg, incomplete)
         db.checkIn(Status.Reserve(key), Status.Bounced, Status.Bounced, Status.Simple, Status.Incomplete)
 
         // Idempotent
         registerIncomingPayment(db, cfg, incomplete)
         db.checkIn(Status.Reserve(key), Status.Bounced, Status.Bounced, Status.Simple, Status.Incomplete)
 
         // Recover info when complete
         registerIncomingPayment(db, cfg, original)
         db.checkIn(Status.Reserve(key), Status.Bounced, Status.Bounced, Status.Simple, Status.Reserve(newKey))
     }
 
     // Test creating an mapped reserve taler transaction without and ID and reconcile it later again
     @Test
     fun mapping() = setup { db, _ ->
         val cfg = NexusIngestConfig.default(AccountType.exchange)
         val firstKey = EddsaPublicKey.randEdsaKey()
         val authPub = EddsaPublicKey.randEdsaKey()
         val sig = EddsaSignature.rand()
         val referenceNumber = subjectFmtQrBill(authPub)
         val subject = "test with MAP:$authPub auth pub"
 
         assertEquals(
             RegistrationResult.Success,
             db.transfer.register(
                 type = TransferType.reserve,
                 accountPub = firstKey,
                 authPub = authPub,
                 authSig = sig,
                 referenceNumber = referenceNumber,
                 timestamp = Instant.now(),
                 recurrent = true
             )
         )
         
         // Register
         val incoming = genInPay(subject)
         registerIncomingPayment(db, cfg, incoming)
         db.checkIn(Status.Reserve(firstKey))
 
         // Idempotent
         registerIncomingPayment(db, cfg, incoming)
         db.checkIn(Status.Reserve(firstKey))
 
         // Admin balance adjust is ignored
         registerIncomingPayment(db, cfg, genInPay("ADMIN BALANCE ADJUST"))
         db.checkIn(Status.Reserve(firstKey), Status.Simple)
 
         val original = genInPay("test 2 for $subject")
         val incomplete = original.copy(subject = null, debtor = null)
         // Register incomplete transaction
         registerIncomingPayment(db, cfg, incomplete)
         db.checkIn(Status.Reserve(firstKey), Status.Simple, Status.Incomplete)
 
         // Idempotent
         registerIncomingPayment(db, cfg, incomplete)
         db.checkIn(Status.Reserve(firstKey), Status.Simple, Status.Incomplete)
 
         // Recover info when complete
         registerIncomingPayment(db, cfg, original)
         db.checkIn(Status.Reserve(firstKey), Status.Simple, Status.Pending)
 
         val secondKey = EddsaPublicKey.randEdsaKey()
         assertEquals(
             RegistrationResult.Success,
             db.transfer.register(
                 type = TransferType.reserve,
                 accountPub = secondKey,
                 authPub = authPub,
                 authSig = sig,
                 referenceNumber = referenceNumber,
                 timestamp = Instant.now(),
                 recurrent = true
             )
         )
 
         db.checkIn(Status.Reserve(firstKey), Status.Simple, Status.Reserve(secondKey))
 
         // Key reuse is pending
         registerIncomingPayment(db, cfg, genInPay(subject, "KUDOS:9"))
         registerIncomingPayment(db, cfg, genInPay("another $subject"))
         db.checkIn(Status.Reserve(firstKey), Status.Simple, Status.Reserve(secondKey), Status.Pending, Status.Pending)
 
         // Finish pending
         val thirdKey = EddsaPublicKey.randEdsaKey()
         assertEquals(
             RegistrationResult.Success,
             db.transfer.register(
                 type = TransferType.reserve,
                 accountPub = thirdKey,
                 authPub = authPub,
                 authSig = sig,
                 referenceNumber = referenceNumber,
                 timestamp = Instant.now(),
                 recurrent = true
             )
         )
         db.checkIn(Status.Reserve(firstKey), Status.Simple, Status.Reserve(secondKey), Status.Reserve(thirdKey), Status.Pending)
     }
 
     // Test creating an mapped reserve taler transaction without and ID and reconcile it later again
     @Test
     fun reference() = setup { db, _ ->
         val cfg = NexusIngestConfig.default(AccountType.exchange)
         val firstKey = EddsaPublicKey.randEdsaKey()
         val authPub = EddsaPublicKey.randEdsaKey()
         val sig = EddsaSignature.rand()
         val referenceNumber = subjectFmtQrBill(authPub)
 
         assertEquals(
             RegistrationResult.Success,
             db.transfer.register(
                 type = TransferType.reserve,
                 accountPub = firstKey,
                 authPub = authPub,
                 authSig = sig,
                 referenceNumber = referenceNumber,
                 timestamp = Instant.now(),
                 recurrent = true
             )
         )
         
         // Register
         val incoming = genInPay(referenceNumber)
         registerIncomingPayment(db, cfg, incoming)
         db.checkIn(Status.Reserve(firstKey))
 
         // Idempotent
         registerIncomingPayment(db, cfg, incoming)
         db.checkIn(Status.Reserve(firstKey))
 
         // Admin balance adjust is ignored
         registerIncomingPayment(db, cfg, genInPay("ADMIN BALANCE ADJUST"))
         db.checkIn(Status.Reserve(firstKey), Status.Simple)
 
         val original = genInPay(referenceNumber)
         val incomplete = original.copy(subject = null, debtor = null)
         // Register incomplete transaction
         registerIncomingPayment(db, cfg, incomplete)
         db.checkIn(Status.Reserve(firstKey), Status.Simple, Status.Incomplete)
 
         // Idempotent
         registerIncomingPayment(db, cfg, incomplete)
         db.checkIn(Status.Reserve(firstKey), Status.Simple, Status.Incomplete)
 
         // Recover info when complete
         registerIncomingPayment(db, cfg, original)
         db.checkIn(Status.Reserve(firstKey), Status.Simple, Status.Pending)
 
         val secondKey = EddsaPublicKey.randEdsaKey()
         assertEquals(
             RegistrationResult.Success,
             db.transfer.register(
                 type = TransferType.reserve,
                 accountPub = secondKey,
                 authPub = authPub,
                 authSig = sig,
                 referenceNumber = referenceNumber,
                 timestamp = Instant.now(),
                 recurrent = true
             )
         )
 
         db.checkIn(Status.Reserve(firstKey), Status.Simple, Status.Reserve(secondKey))
 
         // Key reuse is pending
         registerIncomingPayment(db, cfg, genInPay(referenceNumber, "KUDOS:9"))
         registerIncomingPayment(db, cfg, genInPay(referenceNumber))
         db.checkIn(Status.Reserve(firstKey), Status.Simple, Status.Reserve(secondKey), Status.Pending, Status.Pending)
 
         // Finish pending
         val thirdKey = EddsaPublicKey.randEdsaKey()
         assertEquals(
             RegistrationResult.Success,
             db.transfer.register(
                 type = TransferType.reserve,
                 accountPub = thirdKey,
                 authPub = authPub,
                 authSig = sig,
                 referenceNumber = referenceNumber,
                 timestamp = Instant.now(),
                 recurrent = true
             )
         )
         db.checkIn(Status.Reserve(firstKey), Status.Simple, Status.Reserve(secondKey), Status.Reserve(thirdKey), Status.Pending)
     }
 
     @Test 
     fun recoverInfo() = setup { db, _ ->
         val cfg = NexusIngestConfig.default(AccountType.exchange)
 
         suspend fun Database.checkContent(payment: IncomingPayment) = serializable(
             """
                 SELECT
                     uetr IS NOT DISTINCT FROM ? AND
                     tx_id IS NOT DISTINCT FROM ? AND
                     acct_svcr_ref IS NOT DISTINCT FROM ? AND
                     subject IS NOT DISTINCT FROM ? AND
                     debit_payto IS NOT DISTINCT FROM ?
                 FROM incoming_transactions ORDER BY incoming_transaction_id DESC LIMIT 1
             """
         ) {
             bind(payment.id.uetr)
             bind(payment.id.txId)
             bind(payment.id.acctSvcrRef)
             bind(payment.subject)
             bind(payment.debtor?.toString())
             one {
                 assertTrue(it.getBoolean(1))
             }
         }
 
         // Non talerable
         for ((index, partialId) in sequenceOf(
                 IncomingId(UUID.randomUUID(), null, null),
                 IncomingId(null, randEbicsId(), null),
                 IncomingId(null, null, randEbicsId()),
             ).withIndex()) {
             val payment = genInPay("subject")
 
             // Register minimal
             val partialPayment = payment.copy(id = partialId, subject = null, debtor = null)
             registerIncomingPayment(db, cfg, partialPayment)
             db.checkContent(partialPayment)
             db.checkInCount(index + 1, index, 0)
 
             // Recover ID
             val fullId = IncomingId(
                 partialId.uetr ?: UUID.randomUUID(),
                 partialId.txId ?: randEbicsId(),
                 partialId.acctSvcrRef ?: randEbicsId()
             )
             val idPayment = partialPayment.copy(id = fullId)
             registerIncomingPayment(db, cfg, idPayment)
             db.checkContent(idPayment)
             db.checkInCount(index + 1, index, 0)
 
             // Recover subject & debtor
             val fullPayment = payment.copy(id = fullId)
             registerIncomingPayment(db, cfg, fullPayment)
             db.checkContent(fullPayment)
             db.checkInCount(index + 1, index + 1, 0)
         }
 
         // Talerable
         for ((index, partialId) in sequenceOf(
                 IncomingId(UUID.randomUUID(), null, null),
                 IncomingId(null, randEbicsId(), null),
                 IncomingId(null, null, randEbicsId()),
             ).withIndex()) {
             val payment = genInPay("test with ${EddsaPublicKey.randEdsaKey()} reserve pub")
 
             // Register minimal
             val partialPayment = payment.copy(id = partialId, subject = null, debtor = null)
             registerIncomingPayment(db, cfg, partialPayment)
             db.checkContent(partialPayment)
             db.checkInCount(index + 4, 3, index)
 
             // Recover ID
             val fullId = IncomingId(
                 partialId.uetr ?: UUID.randomUUID(),
                 partialId.txId ?: randEbicsId(),
                 partialId.acctSvcrRef ?: randEbicsId()
             )
             val idPayment = partialPayment.copy(id = fullId)
             registerIncomingPayment(db, cfg, idPayment)
             db.checkContent(idPayment)
             db.checkInCount(index + 4, 3, index)
 
             // Recover subject & debtor
             val fullPayment = payment.copy(id = fullId)
             registerIncomingPayment(db, cfg, fullPayment)
             db.checkContent(fullPayment)
             db.checkInCount(index + 4, 3, index + 1)
         }
     }
 
     @Test 
     fun horror() = setup { db, _ ->
         val cfg = NexusIngestConfig.default(AccountType.exchange)
 
         // Check we do not bounce already registered talerable transaction
         val key = EddsaPublicKey.randEdsaKey()
         val talerablePayment = genInPay("test with $key reserve pub")
         registerIncomingPayment(db, cfg, talerablePayment)
         db.payment.registerMalformedIncoming(
             talerablePayment,
             TalerAmount("KUDOS:2.53"),
             randEbicsId(),
             Instant.now(),
             "manual bounce"
         ).run {
             assertEquals(IncomingBounceRegistrationResult.Talerable, this)
         }
         registerIncomingPayment(db, cfg, talerablePayment.copy(subject=null))
         registerIncomingPayment(db, cfg, talerablePayment)
         registerIncomingPayment(db, cfg, talerablePayment.copy(subject=null))
         db.checkIn(Status.Reserve(key))
 
         // Check we do not register as talerable bounced transaction
         val newKey = EddsaPublicKey.randEdsaKey()
         val bouncedPayment = genInPay("bounced $key")
         registerIncomingPayment(db, cfg, bouncedPayment.copy(subject=null))
         registerIncomingPayment(db, cfg, bouncedPayment)
         registerIncomingPayment(db, cfg, bouncedPayment.copy(subject=null))
         registerIncomingPayment(db, cfg, bouncedPayment)
         db.checkIn(Status.Reserve(key), Status.Bounced)
     }
 }
 
 class PaymentInitiationsTest {
 
     // Test skipping transaction based on config
     @Test
     fun skipping() = setup("skip.conf") { db, cfg ->
         suspend fun checkCount(nbTxs: Int, nbBounce: Int) {
             db.serializable(
                 """
                     SELECT (SELECT count(*) FROM incoming_transactions) + (SELECT count(*) FROM outgoing_transactions) AS transactions,
                         (SELECT count(*) FROM bounced_transactions) AS bounce
                 """
             ) {
                 one {
                     assertEquals(
                         Pair(nbTxs, nbBounce),
                         Pair(it.getInt("transactions"), it.getInt("bounce"))
                     )
                 }
             }
         }
 
         suspend fun ingest(executionTime: Instant) {
             for (tx in sequenceOf(
                 genInPay("test at $executionTime", executionTime = executionTime),
                 genOutPay("test at $executionTime", executionTime = executionTime)
             )) {
                 registerTransaction(db, cfg.ingest, tx)
             }
         }
 
         assertEquals(cfg.fetch.ignoreTransactionsBefore, dateToInstant("2024-04-04"))
         assertEquals(cfg.fetch.ignoreBouncesBefore, dateToInstant("2024-06-12"))
 
         // No transaction at the beginning
         checkCount(0, 0)
 
         // Skipped transactions
         ingest(cfg.fetch.ignoreTransactionsBefore.minusMillis(10))
         checkCount(0, 0)
 
         // Skipped bounces
         ingest(cfg.fetch.ignoreTransactionsBefore)
         ingest(cfg.fetch.ignoreTransactionsBefore.plusMillis(10))
         ingest(cfg.fetch.ignoreBouncesBefore.minusMillis(10))
         checkCount(6, 0)
 
         // Bounces
         ingest(cfg.fetch.ignoreBouncesBefore)
         ingest(cfg.fetch.ignoreBouncesBefore.plusMillis(10))
         checkCount(10, 2)
     }
 
     @Test
     fun status() = setup { db, _ ->
         suspend fun checkPart(
             batchId: Long, 
             batchStatus: SubmissionState, 
             batchMsg: String?, 
             txStatus: SubmissionState, 
             txMsg: String?,
             settledStatus: SubmissionState, 
             settledMsg: String?,
         ) {
             // Check batch status
             val msgId = db.serializable(
                 """
                 SELECT message_id, status, status_msg FROM initiated_outgoing_batches WHERE initiated_outgoing_batch_id=?
                 """
             ) {
                 bind(batchId)
                 one {
                     val msgId = it.getString("message_id")
                     assertEquals(
                         batchStatus to batchMsg,
                         it.getEnum<SubmissionState>("status") to it.getString("status_msg"),
                         msgId
                     )
                     msgId
                 }
             }
             // Check tx status
             db.serializable(
                 """
                 SELECT end_to_end_id, status, status_msg FROM initiated_outgoing_transactions WHERE initiated_outgoing_batch_id=?
                 """
             ) {
                 bind(batchId)
                 all { 
                     val endToEndId = it.getString("end_to_end_id")
                     val expected = when (endToEndId) {
                         "TX" -> Pair(txStatus, txMsg)
                         "TX_SETTLED" -> Pair(settledStatus, settledMsg)
                         else -> throw Exception("Unexpected tx $endToEndId")
                     }
                     assertEquals(
                         expected,
                         it.getEnum<SubmissionState>("status") to it.getString("status_msg"),
                         "$msgId.$endToEndId"
                     )
                 }
             }
         }
         suspend fun checkBatch(batchId: Long, status: SubmissionState, msg: String?, txStatus: SubmissionState? = null) {
             val txStatus = txStatus ?: status
             checkPart(batchId, status, msg, txStatus, msg, txStatus, msg)
         }
         suspend fun checkOrder(orderId: String, status: SubmissionState, msg: String?, txStatus: SubmissionState? = null) {
             val batchId = db.serializable(
                 "SELECT initiated_outgoing_batch_id FROM initiated_outgoing_batches WHERE order_id=?"
             ) {
                 bind(orderId)
                 one { 
                     it.getLong("initiated_outgoing_batch_id")
                 }
             }
             checkBatch(batchId, status, msg, txStatus)
         }
 
         suspend fun test(lambda: suspend (Long) -> Unit) {
             // Reset DB
             db.conn { conn -> 
                 conn.execSQLUpdate("DELETE FROM initiated_outgoing_transactions");
                 conn.execSQLUpdate("DELETE FROM initiated_outgoing_batches");
             }
             // Create a test batch with three transactions
             for (id in sequenceOf("TX", "TX_SETTLED")) {
                 assertIs<PaymentInitiationResult.Success>(
                     db.initiated.create(genInitPay(id))
                 )
             }
             db.initiated.batch(Instant.now(), "BATCH", false)
             // Create witness transactions and batch
             for (id in sequenceOf("WITNESS_1", "WITNESS_2")) {
                 assertIs<PaymentInitiationResult.Success>(
                     db.initiated.create(genInitPay(id))
                 )
             }
             db.initiated.batch(Instant.now(), "BATCH_WITNESS", false)
             for (id in sequenceOf("WITNESS_3", "WITNESS_4")) {
                 assertIs<PaymentInitiationResult.Success>(
                     db.initiated.create(genInitPay(id))
                 )
             }
             // Check everything is unsubmitted
             db.serializable(
                 """
                 SELECT (SELECT bool_and(status = 'unsubmitted') FROM initiated_outgoing_batches)
                    AND (SELECT bool_and(status = 'unsubmitted') FROM initiated_outgoing_transactions)
                 """
             ) {
                 one { assertTrue(it.getBoolean(1)) }
             }
             // Run test
             lambda(db.initiated.submittable().find { it.messageId == "BATCH" }!!.id)
             // Check witness status is unaltered
             db.serializable(
                 """
                 SELECT (SELECT bool_and(status = 'unsubmitted') FROM initiated_outgoing_batches WHERE message_id != 'BATCH')
                    AND (SELECT bool_and(initiated_outgoing_transactions.status = 'unsubmitted') 
                             FROM initiated_outgoing_transactions JOIN initiated_outgoing_batches USING (initiated_outgoing_batch_id)
                             WHERE message_id != 'BATCH')
                 """
             ) {
                 one { assertTrue(it.getBoolean(1)) }
             }
         }
 
         // Submission retry status
         test { batchId ->
             db.initiated.batchSubmissionFailure(batchId, Instant.now(), "First failure")
             checkBatch(batchId, SubmissionState.transient_failure, "First failure")
             db.initiated.batchSubmissionFailure(batchId, Instant.now(), "Second failure")
             checkBatch(batchId, SubmissionState.transient_failure, "Second failure")
             db.initiated.batchSubmissionSuccess(batchId, Instant.now(), "ORDER")
             checkOrder("ORDER", SubmissionState.pending, null)
             db.initiated.batchSubmissionSuccess(batchId, Instant.now(), "ORDER")
             checkOrder("ORDER", SubmissionState.pending, null)
             db.initiated.orderStep("ORDER", "step msg")
             checkOrder("ORDER", SubmissionState.pending, "step msg")
             db.initiated.orderStep("ORDER", "success msg")
             checkOrder("ORDER", SubmissionState.pending, "success msg")
             db.initiated.orderSuccess("ORDER")
             checkOrder("ORDER", SubmissionState.success, "success msg", SubmissionState.pending)
             db.initiated.orderStep("ORDER", "late msg")
             checkOrder("ORDER", SubmissionState.success, "success msg", SubmissionState.pending)
         }
 
         // Order step message on failure
         test { batchId ->
             db.initiated.batchSubmissionSuccess(batchId, Instant.now(), "ORDER")
             checkOrder("ORDER", SubmissionState.pending, null)
             db.initiated.orderStep("ORDER", "step msg")
             checkOrder("ORDER", SubmissionState.pending, "step msg")
             db.initiated.orderStep("ORDER", "failure msg")
             checkOrder("ORDER", SubmissionState.pending, "failure msg")
             assertEquals("failure msg", db.initiated.orderFailure("ORDER")!!.second)
             checkOrder("ORDER", SubmissionState.permanent_failure, "failure msg")
             db.initiated.orderStep("ORDER", "late msg")
             checkOrder("ORDER", SubmissionState.permanent_failure, "failure msg")
         }
 
         // Payment & batch status
         test { batchId ->
             checkBatch(batchId, SubmissionState.unsubmitted, null)
             db.initiated.batchStatusUpdate("BATCH", StatusUpdate.pending, "progress")
             checkBatch(batchId, SubmissionState.pending, "progress")
             db.initiated.txStatusUpdate("TX_SETTLED", null, StatusUpdate.success, "success")
             checkPart(batchId, SubmissionState.pending, "progress", SubmissionState.pending, "progress", SubmissionState.success, "success")
             db.initiated.batchStatusUpdate("BATCH", StatusUpdate.transient_failure, "waiting")
             checkPart(batchId, SubmissionState.transient_failure, "waiting", SubmissionState.transient_failure, "waiting", SubmissionState.success, "success")
             db.initiated.txStatusUpdate("TX", "BATCH", StatusUpdate.permanent_failure, "failure")
             checkPart(batchId, SubmissionState.success, null, SubmissionState.permanent_failure, "failure", SubmissionState.success, "success")
             db.initiated.txStatusUpdate("TX_SETTLED", "BATCH", StatusUpdate.permanent_failure, "late")
             checkPart(batchId, SubmissionState.success, null, SubmissionState.permanent_failure, "failure", SubmissionState.late_failure, "late")
         }
 
         // Registration
         test { batchId ->
             checkBatch(batchId, SubmissionState.unsubmitted, null)
             registerOutgoingPayment(db, genOutPay("", endToEndId = "TX_SETTLED"))
             checkPart(batchId, SubmissionState.unsubmitted, null, SubmissionState.unsubmitted, null, SubmissionState.success, null)
             registerOutgoingPayment(db, genOutPay("", endToEndId = "TX", msgId = "BATCH"))
             checkPart(batchId, SubmissionState.success, null, SubmissionState.success, null, SubmissionState.success, null)
         }
 
         // Transaction failure take over batch failures
         test { batchId -> 
             checkBatch(batchId, SubmissionState.unsubmitted, null)
             db.initiated.batchStatusUpdate("BATCH", StatusUpdate.permanent_failure, "batch")
             checkPart(batchId, SubmissionState.permanent_failure, "batch", SubmissionState.permanent_failure, "batch", SubmissionState.permanent_failure, "batch")
             db.initiated.txStatusUpdate("TX", "BATCH", StatusUpdate.permanent_failure, "tx")
             db.initiated.batchStatusUpdate("BATCH", StatusUpdate.permanent_failure, "batch2")
             checkPart(batchId, SubmissionState.permanent_failure, "batch", SubmissionState.permanent_failure, "tx", SubmissionState.permanent_failure, "batch")
         }
        
         // Unknown order and batch
         db.initiated.batchSubmissionSuccess(42, Instant.now(), "ORDER_X")
         db.initiated.batchSubmissionFailure(42, Instant.now(), null)
         db.initiated.orderStep("ORDER_X", "msg")
         db.initiated.batchStatusUpdate("BATCH_X", StatusUpdate.success, null)
         db.initiated.txStatusUpdate("TX_X", "BATCH_X", StatusUpdate.success, "msg")
         assertNull(db.initiated.orderSuccess("ORDER_X"))
         assertNull(db.initiated.orderFailure("ORDER_X"))
     }
 
     @Test
     fun submittable() = setup { db, _ -> 
         repeat(6) {
             assertIs<PaymentInitiationResult.Success>(
                 db.initiated.create(genInitPay("PAY$it"))
             )
             db.initiated.batch(Instant.now(), "BATCH$it", false)
         }
         suspend fun checkIds(vararg ids: String) {
             assertEquals(
                 listOf(*ids),
                 db.initiated.submittable().flatMap { it.payments.map { it.endToEndId } }
             )
         }
         checkIds("PAY0", "PAY1", "PAY2", "PAY3", "PAY4", "PAY5")
 
         // Check submitted not submitable
         db.initiated.batchSubmissionSuccess(1, Instant.now(), "ORDER1")
         checkIds("PAY1", "PAY2", "PAY3", "PAY4", "PAY5")
 
         // Check transient failure submitable last
         db.initiated.batchSubmissionFailure(2, Instant.now(), "Failure")
         checkIds("PAY2", "PAY3", "PAY4", "PAY5", "PAY1")
 
         // Check persistent failure not submitable
         db.initiated.batchSubmissionSuccess(4, Instant.now(), "ORDER3")
         db.initiated.orderFailure("ORDER3")
         checkIds("PAY2", "PAY4", "PAY5", "PAY1")
         db.initiated.batchSubmissionSuccess(5, Instant.now(), "ORDER4")
         db.initiated.orderFailure("ORDER4")
         checkIds("PAY2", "PAY5", "PAY1")
 
         // Check rotation
         db.initiated.batchSubmissionFailure(3, Instant.now(), "Failure")
         checkIds("PAY5", "PAY1", "PAY2")
         db.initiated.batchSubmissionFailure(6, Instant.now(), "Failure")
         checkIds("PAY1", "PAY2", "PAY5")
         db.initiated.batchSubmissionFailure(2, Instant.now(), "Failure")
         checkIds("PAY2", "PAY5", "PAY1")
     }
 
     // TODO test for unsettledTxInBatch
 }
 
 class EbicsTxTest {
     // Test pending transaction's id
     @Test
     fun pending() = setup { db, _ ->
         val ids = setOf("first", "second", "third")
         for (id in ids) {
             db.ebics.register(id)
         }
 
         repeat(ids.size) {
             val id = db.ebics.first()
             assert(ids.contains(id))
             db.ebics.remove(id!!)
         }
 
         assertNull(db.ebics.first())
     }
 }
 
 class TransferTest {
     suspend fun Database.mapTx(authPub: EddsaPublicKey) = this.payment.registerTalerableIncoming(
         genInPay("subject"), IncomingSubject.Map(authPub)
     )
 
     suspend fun Database.qrTx(reference: String) = this.payment.registerQrBillIncoming(
         genInPay(reference), reference
     )
 
     @Test
     fun registration() = setup { db, cfg ->
         val now = Instant.now()
         val accountPub = EddsaPublicKey.randEdsaKey()
         val authPub = EddsaPublicKey.randEdsaKey()
         val sig = EddsaSignature.rand()
         val referenceNumber = subjectFmtQrBill(authPub)
 
         // Register
         assertEquals(
             RegistrationResult.Success,
             db.transfer.register(
                 type = TransferType.reserve,
                 accountPub = accountPub,
                 authPub = authPub,
                 authSig = sig,
                 referenceNumber = referenceNumber,
                 timestamp = now,
                 recurrent = false
             )
         )
         // Idempotent
         assertEquals(
             RegistrationResult.Success,
             db.transfer.register(
                 type = TransferType.reserve,
                 accountPub = accountPub,
                 authPub = authPub,
                 authSig = sig,
                 referenceNumber = referenceNumber,
                 timestamp = now,
                 recurrent = false
             )
         )
 
         // Reference number reuse
         assertEquals(
             RegistrationResult.SubjectReuse,
             db.transfer.register(
                 type = TransferType.reserve,
                 accountPub = accountPub,
                 authPub = EddsaPublicKey.randEdsaKey(),
                 authSig = sig,
                 referenceNumber = referenceNumber,
                 timestamp = now,
                 recurrent = false
             )
         )
 
         // Auth pub reuse replace existing one
         assertEquals(
             RegistrationResult.Success,
             db.transfer.register(
                 type = TransferType.reserve,
                 accountPub = accountPub,
                 authPub = authPub,
                 authSig = sig,
                 referenceNumber = "032847109247158302947510329",
                 timestamp = now,
                 recurrent = false
             )
         )
 
         // Reserve pub reuse
         assertEquals(
             RegistrationResult.ReservePubReuse,
             db.transfer.register(
                 type = TransferType.reserve,
                 accountPub = accountPub,
                 authPub = EddsaPublicKey.randEdsaKey(),
                 authSig = sig,
                 referenceNumber = "032847109247158302947510330",
                 timestamp = now,
                 recurrent = true
             )
         )
 
         // Non recurrent accept one then bounce
         assertEquals(
             RegistrationResult.Success,
             db.transfer.register(
                 type = TransferType.reserve,
                 accountPub = accountPub,
                 authPub = authPub,
                 authSig = sig,
                 referenceNumber = referenceNumber,
                 timestamp = now,
                 recurrent = false
             )
         )
         assertEquals(
             IncomingRegistrationResult.Success(1, true, false, null, false),
             db.mapTx(authPub)
         )
         db.checkIn(
             Status.Reserve(accountPub)
         )
         assertEquals(
             IncomingRegistrationResult.MappingReuse,
             db.mapTx(authPub)
         )
        
         // Recurrent accept one and delay
         val newKey = EddsaPublicKey.randEdsaKey()
         assertEquals(
             RegistrationResult.Success,
             db.transfer.register(
                 type = TransferType.reserve,
                 accountPub = newKey,
                 authPub = authPub,
                 authSig = sig,
                 referenceNumber = referenceNumber,
                 timestamp = now,
                 recurrent = true
             )
         )
         assertEquals(
             IncomingRegistrationResult.Success(2, true, false, null, false),
             db.mapTx(authPub)
         )
         assertEquals(
             IncomingRegistrationResult.Success(3, true, false, null, true),
             db.mapTx(authPub)
         )
         assertEquals(
             IncomingRegistrationResult.Success(4, true, false, null, true),
             db.mapTx(authPub)
         )
         assertEquals(
             IncomingRegistrationResult.Success(5, true, false, null, true),
             db.mapTx(authPub)
         )
         assertEquals(
             IncomingRegistrationResult.Success(6, true, false, null, true),
             db.mapTx(authPub)
         )
         db.checkIn(
             Status.Reserve(accountPub),
             Status.Reserve(newKey),
             Status.Pending,
             Status.Pending,
             Status.Pending,
             Status.Pending
         )
 
         // Complete pending on recurrent update
         val kycKey = EddsaPublicKey.randEdsaKey()
         assertEquals(
             RegistrationResult.Success,
             db.transfer.register(
                 type = TransferType.kyc,
                 accountPub = kycKey,
                 authPub = authPub,
                 authSig = sig,
                 referenceNumber = referenceNumber,
                 timestamp = now,
                 recurrent = true
             )
         )
         assertEquals(
             RegistrationResult.Success,
             db.transfer.register(
                 type = TransferType.reserve,
                 accountPub = kycKey,
                 authPub = authPub,
                 authSig = sig,
                 referenceNumber = referenceNumber,
                 timestamp = now,
                 recurrent = true
             )
         )
         
         db.checkIn(
             Status.Reserve(accountPub),
             Status.Reserve(newKey),
             Status.Kyc(kycKey),
             Status.Reserve(kycKey),
             Status.Pending,
             Status.Pending,
         )
 
         // Kyc key reuse keep pending ones
         registerIncomingPayment(db, NexusIngestConfig.default(AccountType.exchange), genInPay(fmtIncomingSubject(IncomingType.kyc, kycKey)))
         db.checkIn(
             Status.Reserve(accountPub),
             Status.Reserve(newKey),
             Status.Kyc(kycKey),
             Status.Reserve(kycKey),
             Status.Pending,
             Status.Pending,
             Status.Kyc(kycKey)
         )
 
         // Switching to non recurrent cancel pending
         assertEquals(
             RegistrationResult.Success,
             db.transfer.register(
                 type = TransferType.kyc,
                 accountPub = kycKey,
                 authPub = authPub,
                 authSig = sig,
                 referenceNumber = referenceNumber,
                 timestamp = now,
                 recurrent = false
             )
         )
         db.checkIn(
             Status.Reserve(accountPub),
             Status.Reserve(newKey),
             Status.Kyc(kycKey),
             Status.Reserve(kycKey),
             Status.Bounced,
             Status.Bounced,
             Status.Kyc(kycKey)
         )
     }
 
     @Test
     fun delete() = setup { db, _ ->
         val authPub = EddsaPublicKey.randEdsaKey()
         val sig = EddsaSignature.rand()
         val referenceNumber = subjectFmtQrBill(authPub)
         val payto = IbanPayto.rand("Sir Florian")
         val amount = TalerAmount("KUDOS:2.53")
 
         // Unknown
         assertFalse(db.transfer.unregister(authPub, Instant.now()))
 
         // Unused
         assertEquals(
             RegistrationResult.Success,
             db.transfer.register(
                 type = TransferType.reserve,
                 accountPub = authPub,
                 authPub = authPub,
                 authSig = sig,
                 referenceNumber = referenceNumber,
                 timestamp = Instant.now(),
                 recurrent = false
             )
         )
         assertTrue(db.transfer.unregister(authPub, Instant.now()))
         assertFalse(db.transfer.unregister(authPub, Instant.now()))
 
         assertEquals(
             IncomingRegistrationResult.UnknownMapping,
             db.mapTx(authPub)
         )
         assertEquals(
             IncomingRegistrationResult.UnknownMapping,
             db.qrTx(referenceNumber)
         )
 
         // Register after deletion is idempotent if already known
         assertEquals(
             RegistrationResult.Success,
             db.transfer.register(
                 type = TransferType.reserve,
                 accountPub = authPub,
                 authPub = authPub,
                 authSig = sig,
                 referenceNumber = referenceNumber,
                 timestamp = Instant.now(),
                 recurrent = false
             )
         )
         val cfg = NexusIngestConfig.default(AccountType.exchange)
         val payment = genInPay(referenceNumber)
         assertEquals(IncomingRegistrationResult.Success(1, true, false, null, false), db.payment.registerQrBillIncoming(payment, referenceNumber))
         assertEquals(IncomingRegistrationResult.Success(1, false, false, null, false), db.payment.registerQrBillIncoming(payment, referenceNumber))
         db.checkIn(Status.Reserve(authPub))
         assertTrue(db.transfer.unregister(authPub, Instant.now()))
         registerIncomingPayment(db, cfg, payment)
         assertEquals(IncomingRegistrationResult.Success(1, false, false, null, false), db.payment.registerQrBillIncoming(payment, referenceNumber))
         db.checkIn(Status.Reserve(authPub))
 
         // Test mapped transfers behavior after deletion
         assertEquals(
             RegistrationResult.Success,
             db.transfer.register(
                 type = TransferType.kyc,
                 accountPub = authPub,
                 authPub = authPub,
                 authSig = sig,
                 referenceNumber = referenceNumber,
                 timestamp = Instant.now(),
                 recurrent = true
             )
         )
 
         // First is registered
         assertEquals(
             IncomingRegistrationResult.Success(2, true, false, null, false),
             db.qrTx(referenceNumber)
         )
         // Other are pending
         assertEquals(
             IncomingRegistrationResult.Success(3, true, false, null, true),
             db.qrTx(referenceNumber)
         )
         assertEquals(
             IncomingRegistrationResult.Success(4, true, false, null, true),
             db.mapTx(authPub)
         )
         db.checkIn(Status.Reserve(authPub), Status.Kyc(authPub), Status.Pending, Status.Pending)
 
         assertTrue(db.transfer.unregister(authPub, Instant.now()))
         db.checkIn(Status.Reserve(authPub), Status.Kyc(authPub), Status.Bounced, Status.Bounced)
 
         assertEquals(
             IncomingRegistrationResult.UnknownMapping,
             db.mapTx(authPub)
         )
         assertEquals(
             IncomingRegistrationResult.UnknownMapping,
             db.qrTx(referenceNumber)
         )
     }
 }