progress on bank DB refactoring

4 files changed, 227 insertions, 127 deletions

diff --git a/database-versioning/new/libeufin-bank-0001.sql b/database-versioning/new/libeufin-bank-0001.sql
index 2e211316..339852ac 100644
--- a/database-versioning/new/libeufin-bank-0001.sql
+++ b/database-versioning/new/libeufin-bank-0001.sql
@@ -86,7 +86,8 @@ CREATE TABLE IF NOT EXISTS bank_accounts
   ,is_public BOOLEAN DEFAULT FALSE NOT NULL -- privacy by default
   ,last_nexus_fetch_row_id BIGINT
   ,balance taler_amount DEFAULT (0, 0)
-  ,has_debt BOOLEAN NON NULL DEFAULT TO FALSE
+  ,max_debt taler_amount DEFAULT (0, 0)
+  ,has_debt BOOLEAN NOT NULL DEFAULT FALSE
   ,UNIQUE (owning_customer_id, bank_account_label)
   );
 
diff --git a/database-versioning/new/procedures.sql b/database-versioning/new/procedures.sql
index 798ac53f..ca64f357 100644
--- a/database-versioning/new/procedures.sql
+++ b/database-versioning/new/procedures.sql
@@ -85,7 +85,7 @@ END $$;
 COMMENT ON PROCEDURE bank_set_config(TEXT, TEXT)
   IS 'Update or insert configuration values';
 
-CREATE OR REPLACE PROCEDURE bank_wire_transfer(
+CREATE OR REPLACE FUNCTION bank_wire_transfer(
   IN in_creditor_account_id BIGINT,
   IN in_debtor_account_id BIGINT,
   IN in_subject TEXT,
@@ -101,140 +101,159 @@ CREATE OR REPLACE PROCEDURE bank_wire_transfer(
 LANGUAGE plpgsql
 AS $$
 DECLARE
-debtor_account RECORD;
-creditor_account RECORD;
+debtor_has_debt BOOLEAN;
+debtor_balance taler_amount;
+debtor_max_debt taler_amount;
+creditor_has_debt BOOLEAN;
+creditor_balance taler_amount;
+potential_balance taler_amount;
+potential_balance_check BOOLEAN;
+new_debtor_balance taler_amount;
+new_creditor_balance taler_amount;
+will_debtor_have_debt BOOLEAN;
+will_creditor_have_debt BOOLEAN;
+spending_capacity taler_amount;
+potential_balance_ok BOOLEAN;
 BEGIN
 -- check debtor exists.
 SELECT
-  INTO debtor_account
+  has_debt,
+  (balance).val, (balance).frac,
+  (max_debt).val, (max_debt).frac
+  INTO
+    debtor_has_debt,
+    debtor_balance.val, debtor_balance.frac,
+    debtor_max_debt.val, debtor_max_debt.frac
   FROM bank_accounts
-  WHERE bank_account_id=in_debtor_account_id
+  WHERE bank_account_id=in_debtor_account_id;
 IF NOT FOUND
-  out_nx_debtor=FALSE
-  out_nx_creditor=NULL
-  out_balance_insufficient=NULL
+THEN
+  out_nx_debtor=TRUE;
   RETURN;
 END IF;
+out_nx_debtor=FALSE;
 -- check creditor exists.  Future versions may skip this
 -- due to creditors being hosted at other banks.
 SELECT
-  INTO creditor_account
+  has_debt,
+  (balance).val, (balance).frac
+  INTO
+    creditor_has_debt,
+    creditor_balance.val, creditor_balance.frac
   FROM bank_accounts
-  WHERE bank_account_id=in_creditor_account_id
+  WHERE bank_account_id=in_creditor_account_id;
 IF NOT FOUND
-  out_nx_debtor=TRUE
-  out_nx_creditor=FALSE
-  out_balance_insufficient=NULL
+THEN
+  out_nx_creditor=TRUE;
   RETURN;
 END IF;
+out_nx_creditor=FALSE;
 -- DEBTOR SIDE
 -- check debtor has enough funds.
-IF (debtor_account.has_debt)
+IF (debtor_has_debt)
 THEN -- debt case: simply checking against the max debt allowed.
-SELECT
-  INTO potential_balance
-  FROM amount_add(debtor_account.balance
-                  in_amount);
-SELECT *
-INTO potential_balance_check
-FROM amount_left_minus_right(debtor_account.max_debt,
-                             potential_balance);
-IF (NOT potential_balance_check.ok)
-THEN
-out_nx_creditor=TRUE;
-out_nx_debtor=TRUE;
-out_balance_insufficient=TRUE;
-RETURN;
-new_debtor_balance=potential_balance_check.diff;
-will_debtor_have_debt=TRUE;
-END IF;
+  SELECT
+    (sum).val, (sum).frac
+    INTO
+      potential_balance.val, potential_balance.frac
+    FROM amount_add(debtor_balance,
+                    in_amount);
+  SELECT ok
+    INTO potential_balance_check
+    FROM amount_left_minus_right(debtor_max_debt,
+                                 potential_balance);
+  IF (NOT potential_balance_check)
+  THEN
+    out_balance_insufficient=TRUE;
+    RETURN;
+  END IF;
+  new_debtor_balance=potential_balance;
+  will_debtor_have_debt=TRUE;
 ELSE -- not a debt account
-SELECT -- checking first funds availability.
-  INTO spending_capacity
-  FROM amount_add(debtor_account.balance,
-                  debtor_account.max_debt);
-IF (NOT spending_capacity.ok)
-THEN
-out_nx_creditor=TRUE;
-out_nx_debtor=TRUE;
-out_balance_insufficient=TRUE;
-RETURN;
+  SELECT
+    ok,
+    (diff).val, (diff).frac
+    INTO
+      potential_balance_ok,
+      potential_balance.val,
+      potential_balance.frac
+    FROM amount_left_minus_right(debtor_balance,
+                                 in_amount);
+  IF (potential_balance_ok) -- debtor has enough funds in the (positive) balance.
+  THEN
+    new_debtor_balance=potential_balance;
+    will_debtor_have_debt=FALSE;
+  ELSE -- debtor will switch to debt: determine their new negative balance.
+    SELECT
+      (diff).val, (diff).frac
+      INTO
+        new_debtor_balance.val, new_debtor_balance.frac
+      FROM amount_left_minus_right(in_amount,
+                                   debtor_balance);
+    will_debtor_have_debt=TRUE;
+    SELECT ok
+      INTO potential_balance_check
+      FROM amount_left_minus_right(debtor_max_debt,
+                                   new_debtor_balance);
+    IF (NOT potential_balance_check)
+    THEN
+      out_balance_insufficient=TRUE;
+      RETURN;
+    END IF;
+  END IF;
 END IF;
--- debtor has enough funds, now determine the new
--- balance and whether they go to debit.
-SELECT
-  INTO potential_balance
-  FROM amount_left_minus_right(debtor_account.balance,
-                               in_amount);
-IF (potential_balance.ok) -- debtor has enough funds in the (positive) balance.
-THEN
-new_debtor_balance=potential_balance.diff;
-will_debtor_have_debt=FALSE;
-ELSE -- debtor will switch to debt: determine their new negative balance.
-SELECT diff
-  INTO new_debtor_balance
-  FROM amount_left_minus_right(in_amount,
-                               debtor_account.balance);
-will_debtor_have_debt=TRUE;
-END IF; -- closes has_debt.
+
 -- CREDITOR SIDE.
 -- Here we figure out whether the creditor would switch
 -- from debit to a credit situation, and adjust the balance
 -- accordingly.
-IF (NOT creditor_account.has_debt) -- easy case.
+IF (NOT creditor_has_debt) -- easy case.
 THEN
-SELECT
-  INTO new_creditor_balance
-  FROM amount_add(creditor_account.balance,
-                  in_amount);
-will_creditor_have_debit=FALSE;
+  SELECT
+    (sum).val, (sum).frac
+    INTO new_creditor_balance.val, new_creditor_balance.frac
+    FROM amount_add(creditor_balance,
+                    in_amount);
+  will_creditor_have_debt=FALSE;
 ELSE -- creditor had debit but MIGHT switch to credit.
-SELECT
-  INTO new_creditor_balance
-  FROM amount_left_minus_right(creditor_account.balance,
-                               in_amount);
-IF (new_debtor_balance.ok)
--- the debt is bigger than the amount, keep
--- this last calculated balance but stay debt.
-will_creditor_have_debit=TRUE;
+  SELECT
+    (diff).val, (diff).frac
+    INTO new_creditor_balance.val, new_creditor_balance.frac
+    FROM amount_left_minus_right(creditor_account.balance,
+                                 in_amount);
+  IF (new_debtor_balance.ok)
+  -- the debt is bigger than the amount, keep
+  -- this last calculated balance but stay debt.
+  THEN
+    will_creditor_have_debt=TRUE;
+  ELSE
+  -- the amount would bring the account back to credit,
+  -- determine by how much.
+    SELECT
+      (diff).val, (diff).frac
+      INTO new_creditor_balance.val, new_creditor_balance.frac
+      FROM amount_left_minus_right(in_amount,
+                                   creditor_balance);
+    will_creditor_have_debt=FALSE;
+  END IF;
 END IF;
--- the amount would bring the account back to credit,
--- determine by how much.
-SELECT
-  INTO new_creditor_balance
-  FROM amount_left_minus_right(in_amount,
-                               creditor_account.balance);
-will_creditor_have_debit=FALSE;
-
--- checks and balances set up, now update bank accounts.
-UPDATE bank_accounts
-SET
-  balance=new_debtor_balance
-  has_debt=will_debtor_have_debt
-WHERE bank_account_id=in_debtor_account_id;
-
-UPDATE bank_accounts
-SET
-  balance=new_creditor_balance
-  has_debt=will_creditor_have_debt
-WHERE bank_account_id=in_creditor_account_id;
-
+out_balance_insufficient=FALSE;
 -- now actually create the bank transaction.
 -- debtor side:
 INSERT INTO bank_account_transactions (
-  ,creditor_iban 
+  creditor_iban
   ,creditor_bic
   ,creditor_name
-  ,debtor_iban 
+  ,debtor_iban
   ,debtor_bic
   ,debtor_name
   ,subject
-  ,amount taler_amount
+  ,amount
   ,transaction_date
   ,account_servicer_reference
   ,payment_information_id
   ,end_to_end_id
-  ,direction direction_enum
+  ,direction
   ,bank_account_id
   )
 VALUES (
@@ -256,19 +275,19 @@ VALUES (
 
 -- debtor side:
 INSERT INTO bank_account_transactions (
-  ,creditor_iban
+  creditor_iban
   ,creditor_bic
   ,creditor_name
   ,debtor_iban
   ,debtor_bic
   ,debtor_name
   ,subject
-  ,amount taler_amount
+  ,amount
   ,transaction_date
   ,account_servicer_reference
   ,payment_information_id
   ,end_to_end_id
-  ,direction direction_enum
+  ,direction
   ,bank_account_id
   )
 VALUES (
@@ -287,8 +306,18 @@ VALUES (
   "credit",
   in_creditor_account_id
 );
-out_nx_debtor=TRUE;
-out_nx_creditor=TRUE;
-out_balance_insufficient=FALSE;
+-- checks and balances set up, now update bank accounts.
+UPDATE bank_accounts
+SET
+  balance=new_debtor_balance,
+  has_debt=will_debtor_have_debt
+WHERE bank_account_id=in_debtor_account_id;
+
+UPDATE bank_accounts
+SET
+  balance=new_creditor_balance,
+  has_debt=will_creditor_have_debt
+WHERE bank_account_id=in_creditor_account_id;
+RETURN;
 END $$;
-COMMIT;
+COMMIT;
\ No newline at end of file
diff --git a/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt b/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt
index 76b99b8a..e7ca959a 100644
--- a/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt
+++ b/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt
@@ -40,6 +40,17 @@ enum class TransactionDirection {
     Credit, Debit
 }
 
+data class BankInternalTransaction(
+    val creditorAccountId: Long,
+    val debtorAccountId: Long,
+    val subject: String,
+    val amount: TalerAmount,
+    val transactionDate: Long,
+    val accountServicerReference: String,
+    val endToEndId: String,
+    val paymentInformationId: String
+)
+
 data class BankAccountTransaction(
     val creditorIban: String,
     val creditorBic: String,
@@ -216,6 +227,22 @@ class Database(private val dbConfig: String) {
         return myExecute(stmt)
     }
 
+    fun bankAccountSetMaxDebt(
+        bankAccountLabel: String,
+        maxDebt: TalerAmount
+    ): Boolean {
+        reconnect()
+        val stmt = prepare("""
+           UPDATE bank_accounts
+           SET max_debt=(?,?)::taler_amount
+           WHERE bank_account_label=?
+        """)
+        stmt.setLong(1, maxDebt.value)
+        stmt.setInt(2, maxDebt.frac)
+        stmt.setString(3, bankAccountLabel)
+        return myExecute(stmt)
+    }
+
     fun bankAccountGetFromLabel(bankAccountLabel: String): BankAccount? {
         reconnect()
         val stmt = prepare("""
@@ -250,7 +277,6 @@ class Database(private val dbConfig: String) {
     }
     // More bankAccountGetFrom*() to come, on a needed basis.
 
-    /*
     // BANK ACCOUNT TRANSACTIONS
     enum class BankTransactionResult {
         NO_CREDITOR,
@@ -259,30 +285,34 @@ class Database(private val dbConfig: String) {
         CONFLICT
     }
     fun bankTransactionCreate(
-        // tx: BankInternalTransaction
-        creditTx: BankAccountTransaction,
-        debitTx: BankAccountTransaction
+        tx: BankInternalTransaction
     ): BankTransactionResult {
         reconnect()
         val stmt = prepare("""
             SELECT out_nx_creditor, out_nx_debtor, out_balance_insufficient
-            FROM bank_wire_transfer(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
-        """ // FIXME: adjust balances.
+            FROM bank_wire_transfer(?,?,TEXT(?),(?,?)::taler_amount,?,TEXT(?),TEXT(?),TEXT(?))
+        """
         )
-        // FIXME: implement this operation with a stored procedure.
-        // Credit side
-        stmt.setString(1, tx.creditorAccountId)
-        stmt.setString(1, tx.debitorAccountId)
-        stmt.setString(7, tx.subject)
-        stmt.setObject(8, tx.amount)
-        stmt.setLong(9, tx.transactionDate)
-        stmt.setString(10, tx.accountServicerReference)
-        stmt.setString(11, tx.paymentInformationId)
-        stmt.setString(12, tx.endToEndId)
-
-        stmt.execute()
+        stmt.setLong(1, tx.creditorAccountId)
+        stmt.setLong(2, tx.debtorAccountId)
+        stmt.setString(3, tx.subject)
+        stmt.setLong(4, tx.amount.value)
+        stmt.setInt(5, tx.amount.frac)
+        stmt.setLong(6, tx.transactionDate)
+        stmt.setString(7, tx.accountServicerReference)
+        stmt.setString(8, tx.paymentInformationId)
+        stmt.setString(9, tx.endToEndId)
+        val rs = stmt.executeQuery()
+        rs.use {
+            if (!rs.next()) throw internalServerError("Bank transaction didn't properly return")
+            if (rs.getBoolean("out_nx_debtor")) return BankTransactionResult.NO_DEBTOR
+            if (rs.getBoolean("out_nx_creditor")) return BankTransactionResult.NO_CREDITOR
+            if (rs.getBoolean("out_balance_insufficient")) return BankTransactionResult.CONFLICT
+            return BankTransactionResult.SUCCESS
+        }
     }
 
+    /*
     fun bankTransactionGetForHistoryPage(
         upperBound: Long,
         bankAccountId: Long,
diff --git a/sandbox/src/test/kotlin/DatabaseTest.kt b/sandbox/src/test/kotlin/DatabaseTest.kt
index ead97a33..5c43698c 100644
--- a/sandbox/src/test/kotlin/DatabaseTest.kt
+++ b/sandbox/src/test/kotlin/DatabaseTest.kt
@@ -1,8 +1,5 @@
 import org.junit.Test
-import tech.libeufin.sandbox.BankAccount
-import tech.libeufin.sandbox.Customer
-import tech.libeufin.sandbox.Database
-import tech.libeufin.sandbox.TalerAmount
+import tech.libeufin.sandbox.*
 import tech.libeufin.util.execCommand
 
 class DatabaseTest {
@@ -15,6 +12,15 @@ class DatabaseTest {
         cashoutPayto = "payto://external-IBAN",
         cashoutCurrency = "KUDOS"
     )
+    private val c1 = Customer(
+        login = "bar",
+        passwordHash = "hash",
+        name = "Bar",
+        phone = "+00",
+        email = "foo@b.ar",
+        cashoutPayto = "payto://external-IBAN",
+        cashoutCurrency = "KUDOS"
+    )
     fun initDb(): Database {
         execCommand(
             listOf(
@@ -27,6 +33,40 @@ class DatabaseTest {
         )
         return Database("jdbc:postgresql:///libeufincheck")
     }
+
+    @Test
+    fun bankTransactionTest() {
+        val db = initDb()
+        // Need accounts first.
+        db.customerCreate(c)
+        db.customerCreate(c1)
+        db.bankAccountCreate(BankAccount(
+            iban = "XYZ",
+            bic = "not used",
+            bankAccountLabel = "foo",
+            lastNexusFetchRowId = 1L,
+            owningCustomerId = 1L
+        ))
+        db.bankAccountCreate(BankAccount(
+            iban = "ABC",
+            bic = "not used",
+            bankAccountLabel = "bar",
+            lastNexusFetchRowId = 1L,
+            owningCustomerId = 2L
+        ))
+        db.bankAccountSetMaxDebt("foo", TalerAmount(100, 0))
+        val res = db.bankTransactionCreate(BankInternalTransaction(
+            creditorAccountId = 2,
+            debtorAccountId = 1,
+            subject = "test",
+            amount = TalerAmount(3, 333),
+            accountServicerReference = "acct-svcr-ref",
+            endToEndId = "end-to-end-id",
+            paymentInformationId = "pmtinfid",
+            transactionDate = 100000L
+        ))
+        assert(res == Database.BankTransactionResult.SUCCESS)
+    }
     @Test
     fun customerCreationTest() {
         val db = initDb()