Improve transactions endpoint

5 files changed, 228 insertions, 83 deletions

diff --git a/bank/src/main/kotlin/tech/libeufin/bank/CoreBankApi.kt b/bank/src/main/kotlin/tech/libeufin/bank/CoreBankApi.kt
index 459c662e..1a1b7cbe 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/CoreBankApi.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/CoreBankApi.kt
@@ -483,7 +483,7 @@ fun Routing.accountsMgmtApi(db: Database, ctx: BankApplicationContext) {
 
 fun Routing.coreBankTransactionsApi(db: Database, ctx: BankApplicationContext) {
     get("/accounts/{USERNAME}/transactions") {
-        val username = call.authCheck(db, TokenScope.readonly, true)
+        call.authCheck(db, TokenScope.readonly, true)
         val params = getHistoryParams(call.request.queryParameters)
         val bankAccount = call.bankAccount(db)
 
@@ -491,7 +491,7 @@ fun Routing.coreBankTransactionsApi(db: Database, ctx: BankApplicationContext) {
         call.respond(BankAccountTransactionsResponse(history))
     }
     get("/accounts/{USERNAME}/transactions/{T_ID}") {
-        val username = call.authCheck(db, TokenScope.readonly, true)
+        call.authCheck(db, TokenScope.readonly, true)
         val tId = call.expectUriComponent("T_ID")
         val txRowId = try {
             tId.toLong()
@@ -525,27 +525,20 @@ fun Routing.coreBankTransactionsApi(db: Database, ctx: BankApplicationContext) {
         val tx = call.receive<BankAccountTransactionCreate>()
 
         val subject = tx.payto_uri.message ?: throw badRequest("Wire transfer lacks subject")
-        val amount = tx.payto_uri.amount ?: tx.amount
-        if (amount == null) throw badRequest("Wire transfer lacks amount")
+        val amount = tx.payto_uri.amount ?: tx.amount ?: throw badRequest("Wire transfer lacks amount")
         if (amount.currency != ctx.currency) throw badRequest(
             "Wrong currency: ${amount.currency}",
             talerErrorCode = TalerErrorCode.TALER_EC_GENERIC_CURRENCY_MISMATCH
         )
-        // TODO rewrite all thos database query in a single database function
-        val debtorBankAccount = call.bankAccount(db)
-        val creditorBankAccount = db.bankAccountGetFromInternalPayto(tx.payto_uri)
-            ?: throw notFound(
-                "Creditor account not found",
-                TalerErrorCode.TALER_EC_BANK_UNKNOWN_ACCOUNT
-            )
-        val dbInstructions = BankInternalTransaction(
-            debtorAccountId = debtorBankAccount.id,
-            creditorAccountId = creditorBankAccount.expectRowId(),
+
+        val result = db.bankTransaction(
+            creditAccountPayto = tx.payto_uri,
+            debitAccountUsername = username,
             subject = subject,
             amount = amount,
-            transactionDate = Instant.now()
+            timestamp = Instant.now(),
         )
-        when (db.bankTransactionCreate(dbInstructions)) {
+        when (result) {
             BankTransactionResult.BALANCE_INSUFFICIENT -> throw conflict(
                 "Insufficient funds",
                 TalerErrorCode.TALER_EC_BANK_UNALLOWED_DEBIT
@@ -554,8 +547,14 @@ fun Routing.coreBankTransactionsApi(db: Database, ctx: BankApplicationContext) {
                 "Wire transfer attempted with credit and debit party being the same bank account",
                 TalerErrorCode.TALER_EC_BANK_SAME_ACCOUNT
             )
-            BankTransactionResult.NO_CREDITOR -> throw internalServerError("Creditor not found despite previous checks.")
-            BankTransactionResult.NO_DEBTOR -> throw internalServerError("Debtor not found despite the request was authenticated.")
+            BankTransactionResult.NO_DEBTOR -> throw notFound(
+                "Customer $username not found",
+                TalerErrorCode.TALER_EC_BANK_UNKNOWN_ACCOUNT
+            )
+            BankTransactionResult.NO_CREDITOR -> throw notFound(
+                "Creditor account was not found",
+                TalerErrorCode.TALER_EC_BANK_UNKNOWN_ACCOUNT
+            )
             BankTransactionResult.SUCCESS -> call.respond(HttpStatusCode.OK)
         }
     }
diff --git a/bank/src/main/kotlin/tech/libeufin/bank/Database.kt b/bank/src/main/kotlin/tech/libeufin/bank/Database.kt
index a7fbbb42..b3616cf4 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/Database.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/Database.kt
@@ -99,33 +99,35 @@ private fun <R> PgConnection.transaction(lambda: (PgConnection) -> R): R {
 fun initializeDatabaseTables(cfg: DatabaseConfig) {
     logger.info("doing DB initialization, sqldir ${cfg.sqlDir}, dbConnStr ${cfg.dbConnStr}")
     pgDataSource(cfg.dbConnStr).pgConnection().use { conn ->
-        val sqlVersioning = File("${cfg.sqlDir}/versioning.sql").readText()
-        conn.execSQLUpdate(sqlVersioning)
-
-        val checkStmt = conn.prepareStatement("SELECT count(*) as n FROM _v.patches where patch_name = ?")
-
-        for (n in 1..9999) {
-            val numStr = n.toString().padStart(4, '0')
-            val patchName = "libeufin-bank-$numStr"
-
-            checkStmt.setString(1, patchName)
-            val patchCount = checkStmt.oneOrNull { it.getInt(1) } ?: throw Error("unable to query patches");
-            if (patchCount >= 1) {
-                logger.info("patch $patchName already applied")
-                continue
-            }
-
-            val path = File("${cfg.sqlDir}/libeufin-bank-$numStr.sql")
-            if (!path.exists()) {
-                logger.info("path $path doesn't exist anymore, stopping")
-                break
+        conn.transaction {
+            val sqlVersioning = File("${cfg.sqlDir}/versioning.sql").readText()
+            conn.execSQLUpdate(sqlVersioning)
+    
+            val checkStmt = conn.prepareStatement("SELECT count(*) as n FROM _v.patches where patch_name = ?")
+    
+            for (n in 1..9999) {
+                val numStr = n.toString().padStart(4, '0')
+                val patchName = "libeufin-bank-$numStr"
+    
+                checkStmt.setString(1, patchName)
+                val patchCount = checkStmt.oneOrNull { it.getInt(1) } ?: throw Error("unable to query patches");
+                if (patchCount >= 1) {
+                    logger.info("patch $patchName already applied")
+                    continue
+                }
+    
+                val path = File("${cfg.sqlDir}/libeufin-bank-$numStr.sql")
+                if (!path.exists()) {
+                    logger.info("path $path doesn't exist anymore, stopping")
+                    break
+                }
+                logger.info("applying patch $path")
+                val sqlPatchText = path.readText()
+                conn.execSQLUpdate(sqlPatchText)
             }
-            logger.info("applying patch $path")
-            val sqlPatchText = path.readText()
-            conn.execSQLUpdate(sqlPatchText)
-        }
-        val sqlProcedures = File("${cfg.sqlDir}/procedures.sql").readText()
-        conn.execSQLUpdate(sqlProcedures)
+            val sqlProcedures = File("${cfg.sqlDir}/procedures.sql").readText()
+            conn.execSQLUpdate(sqlProcedures)
+        } 
     }
 }
 
@@ -723,6 +725,110 @@ class Database(dbConfig: String, private val bankCurrency: String): java.io.Clos
 
     // BANK ACCOUNT TRANSACTIONS
 
+    private fun handleExchangeTx(
+        conn: PgConnection,
+        subject: String,
+        creditorAccountId: Long,
+        debtorAccountId: Long,
+        it: ResultSet
+    ) {
+        val metadata = TxMetadata.parse(subject)
+        if (it.getBoolean("out_creditor_is_exchange")) {
+            val rowId = it.getLong("out_credit_row_id")
+            if (metadata is IncomingTxMetadata) {
+                val stmt = conn.prepareStatement("""
+                    INSERT INTO taler_exchange_incoming 
+                        (reserve_pub, bank_transaction) 
+                    VALUES (?, ?)
+                """)
+                stmt.setBytes(1, metadata.reservePub.raw)
+                stmt.setLong(2, rowId)
+                stmt.executeUpdate()
+                conn.execSQLUpdate("NOTIFY incoming_tx, '$creditorAccountId $rowId'")
+            } else {
+                // TODO bounce
+                logger.warn("exchange account $creditorAccountId received a transaction $rowId with malformed metadata, will bounce in future version")
+            }
+        }
+        if (it.getBoolean("out_debtor_is_exchange")) {
+            val rowId = it.getLong("out_debit_row_id")
+            if (metadata is OutgoingTxMetadata) {
+                val stmt = conn.prepareStatement("""
+                    INSERT INTO taler_exchange_outgoing 
+                        (wtid, exchange_base_url, bank_transaction) 
+                    VALUES (?, ?, ?)
+                """)
+                stmt.setBytes(1, metadata.wtid.raw)
+                stmt.setString(2, metadata.exchangeBaseUrl.url)
+                stmt.setLong(3, rowId)
+                stmt.executeUpdate()
+                conn.execSQLUpdate("NOTIFY outgoing_tx, '$debtorAccountId $rowId'")
+            } else {
+                logger.warn("exchange account $debtorAccountId sent a transaction $rowId with malformed metadata")
+            }
+        }
+    }
+
+    suspend fun bankTransaction(
+        creditAccountPayto: IbanPayTo,
+        debitAccountUsername: String,
+        subject: String,
+        amount: TalerAmount,
+        timestamp: Instant,
+        accountServicerReference: String = "not used", // ISO20022
+        endToEndId: String = "not used", // ISO20022
+        paymentInformationId: String = "not used" // ISO20022
+    ): BankTransactionResult = conn { conn ->
+        conn.transaction {
+            val stmt = conn.prepareStatement("""
+                SELECT 
+                    out_creditor_not_found 
+                    ,out_debtor_not_found
+                    ,out_same_account
+                    ,out_balance_insufficient
+                    ,out_credit_bank_account_id
+                    ,out_debit_bank_account_id
+                    ,out_credit_row_id
+                    ,out_debit_row_id
+                    ,out_creditor_is_exchange 
+                    ,out_debtor_is_exchange
+                FROM bank_transaction(?,?,?,(?,?)::taler_amount,?,?,?,?)
+            """
+            )
+            stmt.setString(1, creditAccountPayto.canonical)
+            stmt.setString(2, debitAccountUsername)
+            stmt.setString(3, subject)
+            stmt.setLong(4, amount.value)
+            stmt.setInt(5, amount.frac)
+            stmt.setLong(6, timestamp.toDbMicros() ?: throw faultyTimestampByBank())
+            stmt.setString(7, accountServicerReference)
+            stmt.setString(8, paymentInformationId)
+            stmt.setString(9, endToEndId)
+            stmt.executeQuery().use {
+                when {
+                    !it.next() -> throw internalServerError("Bank transaction didn't properly return")
+                    it.getBoolean("out_creditor_not_found") -> {
+                        logger.error("No creditor account found")
+                        BankTransactionResult.NO_CREDITOR
+                    }
+                    it.getBoolean("out_debtor_not_found") -> {
+                        logger.error("No debtor account found")
+                        BankTransactionResult.NO_DEBTOR
+                    }
+                    it.getBoolean("out_same_account") -> BankTransactionResult.SAME_ACCOUNT
+                    it.getBoolean("out_balance_insufficient") -> {
+                        logger.error("Balance insufficient")
+                        BankTransactionResult.BALANCE_INSUFFICIENT
+                    }
+                    else -> {
+                        handleExchangeTx(conn, subject, it.getLong("out_credit_bank_account_id"), it.getLong("out_debit_bank_account_id"), it)
+                        BankTransactionResult.SUCCESS
+                    }
+                }
+            }
+        }
+    }
+
     suspend fun bankTransactionCreate(
         tx: BankInternalTransaction
     ): BankTransactionResult = conn { conn ->
@@ -766,41 +872,7 @@ class Database(dbConfig: String, private val bankCurrency: String): java.io.Clos
                         BankTransactionResult.BALANCE_INSUFFICIENT
                     }
                     else -> {
-                        val metadata = TxMetadata.parse(tx.subject)
-                        if (it.getBoolean("out_creditor_is_exchange")) {
-                            val rowId = it.getLong("out_credit_row_id")
-                            if (metadata is IncomingTxMetadata) {
-                                val stmt = conn.prepareStatement("""
-                                    INSERT INTO taler_exchange_incoming 
-                                        (reserve_pub, bank_transaction) 
-                                    VALUES (?, ?)
-                                """)
-                                stmt.setBytes(1, metadata.reservePub.raw)
-                                stmt.setLong(2, rowId)
-                                stmt.executeUpdate()
-                                conn.execSQLUpdate("NOTIFY incoming_tx, '${"${tx.creditorAccountId} $rowId"}'")
-                            } else {
-                                // TODO bounce
-                                logger.warn("exchange account ${tx.creditorAccountId} received a transaction $rowId with malformed metadata, will bounce in future version")
-                            }
-                        }
-                        if (it.getBoolean("out_debtor_is_exchange")) {
-                            val rowId = it.getLong("out_debit_row_id")
-                            if (metadata is OutgoingTxMetadata) {
-                                val stmt = conn.prepareStatement("""
-                                    INSERT INTO taler_exchange_outgoing 
-                                        (wtid, exchange_base_url, bank_transaction) 
-                                    VALUES (?, ?, ?)
-                                """)
-                                stmt.setBytes(1, metadata.wtid.raw)
-                                stmt.setString(2, metadata.exchangeBaseUrl.url)
-                                stmt.setLong(3, rowId)
-                                stmt.executeUpdate()
-                                conn.execSQLUpdate("NOTIFY outgoing_tx, '${"${tx.debtorAccountId} $rowId"}'")
-                            } else {
-                                logger.warn("exchange account ${tx.debtorAccountId} sent a transaction $rowId with malformed metadata")
-                            }
-                        }
+                        handleExchangeTx(conn, tx.subject, tx.creditorAccountId, tx.debtorAccountId, it)
                         BankTransactionResult.SUCCESS
                     }
                 }
diff --git a/bank/src/main/kotlin/tech/libeufin/bank/WireGatewayApi.kt b/bank/src/main/kotlin/tech/libeufin/bank/WireGatewayApi.kt
index 657ce0f1..5b956512 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/WireGatewayApi.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/WireGatewayApi.kt
@@ -58,7 +58,7 @@ fun Routing.wireGatewayApi(db: Database, ctx: BankApplicationContext) {
         when (dbRes.txResult) {
             TalerTransferResult.NO_DEBITOR -> throw notFound(
                 "Customer $username not found",
-                TalerErrorCode.TALER_EC_END // FIXME: need EC.
+                TalerErrorCode.TALER_EC_BANK_UNKNOWN_ACCOUNT
             )
             TalerTransferResult.NOT_EXCHANGE -> throw conflict(
                 "$username is not an exchange account.",
@@ -142,7 +142,7 @@ fun Routing.wireGatewayApi(db: Database, ctx: BankApplicationContext) {
         when (dbRes.txResult) {
             TalerAddIncomingResult.NO_CREDITOR -> throw notFound(
                 "Customer $username not found",
-                TalerErrorCode.TALER_EC_END // FIXME: need EC.
+                TalerErrorCode.TALER_EC_BANK_UNKNOWN_ACCOUNT
             )
             TalerAddIncomingResult.NOT_EXCHANGE -> throw conflict(
                 "$username is not an exchange account.",
diff --git a/bank/src/main/kotlin/tech/libeufin/bank/helpers.kt b/bank/src/main/kotlin/tech/libeufin/bank/helpers.kt
index 8f2c56c0..26ca601c 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/helpers.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/helpers.kt
@@ -67,7 +67,7 @@ suspend fun ApplicationCall.bankAccount(db: Database): Database.BankInfo {
     val username = getResourceName("USERNAME")
     return db.bankAccountInfoFromCustomerLogin(username) ?: throw notFound(
         hint = "Customer $username not found",
-        talerEc = TalerErrorCode.TALER_EC_END // FIXME: need EC.
+        talerEc = TalerErrorCode.TALER_EC_BANK_UNKNOWN_ACCOUNT
     )
 }
 
diff --git a/database-versioning/procedures.sql b/database-versioning/procedures.sql
index ec26a242..c2d07a8d 100644
--- a/database-versioning/procedures.sql
+++ b/database-versioning/procedures.sql
@@ -432,7 +432,81 @@ COMMENT ON FUNCTION taler_add_incoming(
   IS 'function that (1) inserts the TWG requests'
      'details into the database and (2) performs '
      'the actual bank transaction to pay the merchant';
-  
+
+CREATE OR REPLACE FUNCTION bank_transaction(
+  IN in_credit_account_payto TEXT,
+  IN in_debit_account_username TEXT,
+  IN in_subject TEXT,
+  IN in_amount taler_amount,
+  IN in_timestamp BIGINT,
+  IN in_account_servicer_reference TEXT,
+  IN in_payment_information_id TEXT,
+  IN in_end_to_end_id TEXT,
+  -- Error status
+  OUT out_creditor_not_found BOOLEAN,
+  OUT out_debtor_not_found BOOLEAN,
+  OUT out_same_account BOOLEAN,
+  OUT out_balance_insufficient BOOLEAN,
+  -- Success return
+  OUT out_credit_bank_account_id BIGINT,
+  OUT out_debit_bank_account_id BIGINT,
+  OUT out_credit_row_id BIGINT,
+  OUT out_debit_row_id BIGINT,
+  OUT out_creditor_is_exchange BOOLEAN,
+  OUT out_debtor_is_exchange BOOLEAN
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+-- Find credit bank account id
+SELECT bank_account_id
+  INTO out_credit_bank_account_id
+  FROM bank_accounts
+  WHERE internal_payto_uri = in_credit_account_payto;
+IF NOT FOUND THEN
+  out_creditor_not_found=TRUE;
+  RETURN;
+END IF;
+-- Find debit bank account id
+SELECT bank_account_id
+  INTO out_debit_bank_account_id
+  FROM bank_accounts 
+      JOIN customers 
+        ON customer_id=owning_customer_id
+  WHERE login = in_debit_account_username;
+IF NOT FOUND THEN
+  out_debtor_not_found=TRUE;
+  RETURN;
+END IF;
+-- Perform bank transfer
+SELECT
+  transfer.out_balance_insufficient,
+  transfer.out_credit_row_id,
+  transfer.out_debit_row_id,
+  transfer.out_same_account,
+  transfer.out_creditor_is_exchange,
+  transfer.out_debtor_is_exchange
+  INTO
+    out_balance_insufficient,
+    out_credit_row_id,
+    out_debit_row_id,
+    out_same_account,
+    out_creditor_is_exchange,
+    out_debtor_is_exchange
+  FROM bank_wire_transfer(
+    out_credit_bank_account_id,
+    out_debit_bank_account_id,
+    in_subject,
+    in_amount,
+    in_timestamp,
+    in_account_servicer_reference,
+    in_payment_information_id,
+    in_end_to_end_id
+  ) as transfer;
+IF out_balance_insufficient THEN
+  RETURN;
+END IF;
+END $$;
 
 CREATE OR REPLACE FUNCTION confirm_taler_withdrawal(
   IN in_withdrawal_uuid uuid,