Bank DB: basic operations on withdrawals.

3 files changed, 107 insertions, 43 deletions

diff --git a/database-versioning/new/libeufin-bank-0001.sql b/database-versioning/new/libeufin-bank-0001.sql
index 339852ac..314c4ea2 100644
--- a/database-versioning/new/libeufin-bank-0001.sql
+++ b/database-versioning/new/libeufin-bank-0001.sql
@@ -356,7 +356,7 @@ CREATE TABLE IF NOT EXISTS bank_account_statements
 
 CREATE TABLE IF NOT EXISTS taler_withdrawal_operations
   (taler_withdrawal_id BIGINT GENERATED BY DEFAULT AS IDENTITY
-  ,withdrawal_id uuid NOT NULL
+  ,withdrawal_uuid uuid NOT NULL
   ,amount taler_amount NOT NULL
   ,selection_done BOOLEAN DEFAULT FALSE NOT NULL
   ,aborted BOOLEAN DEFAULT FALSE NOT NULL
diff --git a/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt b/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt
index b7b681fc..eab8d7f0 100644
--- a/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt
+++ b/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt
@@ -5,7 +5,6 @@ import tech.libeufin.util.internalServerError
 
 import java.sql.DriverManager
 import java.sql.PreparedStatement
-import java.sql.ResultSet
 import java.sql.SQLException
 import java.util.*
 
@@ -70,7 +69,7 @@ data class BankAccountTransaction(
 )
 
 data class TalerWithdrawalOperation(
-    val withdrawalId: UUID,
+    val withdrawalUuid: UUID,
     val amount: TalerAmount,
     val selectionDone: Boolean = false,
     val aborted: Boolean = false,
@@ -354,46 +353,88 @@ class Database(private val dbConfig: String) {
             do {
                 ret.add(
                     BankAccountTransaction(
-                    creditorIban = it.getString("creditor_iban"),
-                    creditorBic = it.getString("creditor_bic"),
-                    creditorName = it.getString("creditor_name"),
-                    debtorIban = it.getString("debtor_iban"),
-                    debtorBic = it.getString("debtor_bic"),
-                    debtorName = it.getString("debtor_name"),
-                    amount = TalerAmount(
-                        it.getLong("amount_val"),
-                        it.getInt("amount_frac")
-                    ),
-                    accountServicerReference = it.getString("account_servicer_reference"),
-                    endToEndId = it.getString("end_to_end_id"),
-                    direction = it.getString("direction").run {
-                        when(this) {
-                            "credit" -> TransactionDirection.Credit
-                            "debit" -> TransactionDirection.Debit
-                            else -> throw internalServerError("Wrong direction in transaction: $this")
-                        }
-                    },
-                    bankAccountId = it.getLong("bank_account_id"),
-                    paymentInformationId = it.getString("payment_information_id"),
-                    subject = it.getString("subject"),
-                    transactionDate = it.getLong("transaction_date")
+                        creditorIban = it.getString("creditor_iban"),
+                        creditorBic = it.getString("creditor_bic"),
+                        creditorName = it.getString("creditor_name"),
+                        debtorIban = it.getString("debtor_iban"),
+                        debtorBic = it.getString("debtor_bic"),
+                        debtorName = it.getString("debtor_name"),
+                        amount = TalerAmount(
+                            it.getLong("amount_val"),
+                            it.getInt("amount_frac")
+                        ),
+                        accountServicerReference = it.getString("account_servicer_reference"),
+                        endToEndId = it.getString("end_to_end_id"),
+                        direction = it.getString("direction").run {
+                            when(this) {
+                                "credit" -> TransactionDirection.Credit
+                                "debit" -> TransactionDirection.Debit
+                                else -> throw internalServerError("Wrong direction in transaction: $this")
+                            }
+                        },
+                        bankAccountId = it.getLong("bank_account_id"),
+                        paymentInformationId = it.getString("payment_information_id"),
+                        subject = it.getString("subject"),
+                        transactionDate = it.getLong("transaction_date")
                 ))
             } while (it.next())
             return ret
         }
     }
 
-    /*
     // WITHDRAWALS
-    fun talerWithdrawalCreate(opUUID: UUID, walletBankAccount: Long) {
+    fun talerWithdrawalCreate(
+        opUUID: UUID,
+        walletBankAccount: Long,
+        amount: TalerAmount
+    ): Boolean {
         reconnect()
         val stmt = prepare("""
-            INSERT INTO taler_withdrawals_operations (withdrawal_id, wallet_bank_account)
-            VALUES (?,?)
+            INSERT INTO
+              taler_withdrawal_operations
+              (withdrawal_uuid, wallet_bank_account, amount)
+            VALUES (?,?,(?,?)::taler_amount)
         """) // Take all defaults from the SQL.
         stmt.setObject(1, opUUID)
-        stmt.setObject(2, walletBankAccount)
-        stmt.execute()
+        stmt.setLong(2, walletBankAccount)
+        stmt.setLong(3, amount.value)
+        stmt.setInt(4, amount.frac)
+
+        return myExecute(stmt)
+    }
+    fun talerWithdrawalGet(opUUID: UUID): TalerWithdrawalOperation? {
+        reconnect()
+        val stmt = prepare("""
+            SELECT
+              (amount).val as amount_val
+              ,(amount).frac as amount_frac
+              ,withdrawal_uuid
+              ,selection_done     
+              ,aborted     
+              ,confirmation_done     
+              ,reserve_pub
+              ,selected_exchange_payto 
+	          ,wallet_bank_account
+            FROM taler_withdrawal_operations
+            WHERE withdrawal_uuid=?
+        """)
+        stmt.setObject(1, opUUID)
+        stmt.executeQuery().use {
+            if (!it.next()) return null
+            return TalerWithdrawalOperation(
+               amount = TalerAmount(
+                   it.getLong("amount_val"),
+                   it.getInt("amount_frac")
+               ),
+               selectionDone = it.getBoolean("selection_done"),
+               selectedExchangePayto = it.getString("selected_exchange_payto"),
+               walletBankAccount = it.getLong("wallet_bank_account"),
+               confirmationDone = it.getBoolean("confirmation_done"),
+               aborted = it.getBoolean("aborted"),
+               reservePub = it.getBytes("reserve_pub"),
+               withdrawalUuid = it.getObject("withdrawal_uuid") as UUID
+            )
+        }
     }
 
     // Values coming from the wallet.
@@ -401,34 +442,30 @@ class Database(private val dbConfig: String) {
         opUUID: UUID,
         exchangePayto: String,
         reservePub: ByteArray
-    ) {
+    ): Boolean {
         reconnect()
         val stmt = prepare("""
             UPDATE taler_withdrawal_operations
             SET selected_exchange_payto = ?, reserve_pub = ?, selection_done = true
-            WHERE withdrawal_id=?
+            WHERE withdrawal_uuid=?
         """
         )
         stmt.setString(1, exchangePayto)
         stmt.setBytes(2, reservePub)
         stmt.setObject(3, opUUID)
-        stmt.execute()
+        return myExecute(stmt)
     }
-
-    fun talerWithdrawalConfirm(opUUID: UUID) {
+    fun talerWithdrawalConfirm(opUUID: UUID): Boolean {
         reconnect()
         val stmt = prepare("""
             UPDATE taler_withdrawal_operations
             SET confirmation_done = true
-            WHERE withdrawal_id=?
+            WHERE withdrawal_uuid=?
         """
         )
         stmt.setObject(1, opUUID)
-        stmt.execute()
+        return myExecute(stmt)
     }
-
-
-    // NOTE: to run BFH, EBICS and cash-out tables can be postponed.
-*/
+    // NOTE: EBICS not needed for BFH and NB.
 
 }
diff --git a/sandbox/src/test/kotlin/DatabaseTest.kt b/sandbox/src/test/kotlin/DatabaseTest.kt
index eb7ef4d8..ff4f537b 100644
--- a/sandbox/src/test/kotlin/DatabaseTest.kt
+++ b/sandbox/src/test/kotlin/DatabaseTest.kt
@@ -1,6 +1,7 @@
 import org.junit.Test
 import tech.libeufin.sandbox.*
 import tech.libeufin.util.execCommand
+import java.util.UUID
 
 class DatabaseTest {
     private val customerFoo = Customer(
@@ -176,4 +177,30 @@ class DatabaseTest {
         assert(db.bankAccountGetFromLabel("foo")?.bankAccountLabel == "foo")
         assert(db.bankAccountGetFromLabel("foo")?.balance?.equals(TalerAmount(0, 0)) == true)
     }
+
+    @Test
+    fun withdrawalTest() {
+        val db = initDb()
+        val uuid = UUID.randomUUID()
+        // insert new.
+        assert(db.talerWithdrawalCreate(
+            uuid,
+            1L,
+            TalerAmount(1, 0)
+        ))
+        // get it.
+        val op = db.talerWithdrawalGet(uuid)
+        assert(op?.walletBankAccount == 1L && op.withdrawalUuid == uuid)
+        // Setting the details.
+        assert(db.talerWithdrawalSetDetails(
+            uuid,
+            "exchange-payto",
+            ByteArray(32)
+        ))
+        val opSelected = db.talerWithdrawalGet(uuid)
+        assert(opSelected?.selectionDone == true && !opSelected.confirmationDone)
+        assert(db.talerWithdrawalConfirm(uuid))
+        // Finally confirming the operation (means customer wired funds to the exchange.)
+        assert(db.talerWithdrawalGet(uuid)?.confirmationDone == true)
+    }
 }
\ No newline at end of file