Drafting the bank_wire_transfer() SQL function.

3 files changed, 213 insertions, 5 deletions

diff --git a/database-versioning/new/libeufin-bank-0001.sql b/database-versioning/new/libeufin-bank-0001.sql
index 5497b793..2e211316 100644
--- a/database-versioning/new/libeufin-bank-0001.sql
+++ b/database-versioning/new/libeufin-bank-0001.sql
@@ -30,7 +30,7 @@ COMMENT ON TYPE taler_amount
 
 -- Indicates whether a transaction is incoming or outgoing.
 CREATE TYPE direction_enum
-  AS ENUM ('CRDT', 'DBIT');
+  AS ENUM ('credit', 'debit');
 
 CREATE TYPE tan_enum
   AS ENUM ('sms', 'email', 'file'); -- file is for testing purposes.
@@ -86,6 +86,7 @@ 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
   ,UNIQUE (owning_customer_id, bank_account_label)
   );
 
@@ -96,7 +97,8 @@ did).  The idea was to provide multiple bank accounts to one
 user.  Nonetheless, for simplicity the current version enforces
 one bank account for one user, and additionally the bank
 account label matches always the login.';
-
+COMMENT ON COLUMN bank_accounts.has_debt
+  IS 'When true, the balance is negative';
 COMMENT ON COLUMN bank_accounts.last_nexus_fetch_row_id
   IS 'Keeps the ID of the last incoming payment that was learnt
 from Nexus.  For that reason, this ID is stored verbatim as
@@ -131,7 +133,6 @@ CREATE TABLE IF NOT EXISTS bank_account_transactions
   ,account_servicer_reference TEXT NOT NULL
   ,payment_information_id TEXT
   ,end_to_end_id TEXT
-  ,is_pending BOOLEAN NOT NULL DEFAULT TRUE
   ,direction direction_enum NOT NULL
   ,bank_account_id BIGINT NOT NULL
     REFERENCES bank_accounts(bank_account_id)
diff --git a/database-versioning/new/procedures.sql b/database-versioning/new/procedures.sql
index 5798c5fd..798ac53f 100644
--- a/database-versioning/new/procedures.sql
+++ b/database-versioning/new/procedures.sql
@@ -84,4 +84,211 @@ END IF;
 END $$;
 COMMENT ON PROCEDURE bank_set_config(TEXT, TEXT)
   IS 'Update or insert configuration values';
-COMMIT;
\ No newline at end of file
+
+CREATE OR REPLACE PROCEDURE bank_wire_transfer(
+  IN in_creditor_account_id BIGINT,
+  IN in_debtor_account_id BIGINT,
+  IN in_subject TEXT,
+  IN in_amount taler_amount,
+  IN in_transaction_date BIGINT, -- GNUnet microseconds.
+  IN in_account_servicer_reference TEXT,
+  IN in_payment_information_id TEXT,
+  IN in_end_to_end_id TEXT,
+  OUT out_nx_creditor BOOLEAN,
+  OUT out_nx_debtor BOOLEAN,
+  OUT out_balance_insufficient BOOLEAN
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+debtor_account RECORD;
+creditor_account RECORD;
+BEGIN
+-- check debtor exists.
+SELECT
+  INTO debtor_account
+  FROM bank_accounts
+  WHERE bank_account_id=in_debtor_account_id
+IF NOT FOUND
+  out_nx_debtor=FALSE
+  out_nx_creditor=NULL
+  out_balance_insufficient=NULL
+  RETURN;
+END IF;
+-- check creditor exists.  Future versions may skip this
+-- due to creditors being hosted at other banks.
+SELECT
+  INTO creditor_account
+  FROM bank_accounts
+  WHERE bank_account_id=in_creditor_account_id
+IF NOT FOUND
+  out_nx_debtor=TRUE
+  out_nx_creditor=FALSE
+  out_balance_insufficient=NULL
+  RETURN;
+END IF;
+-- DEBTOR SIDE
+-- check debtor has enough funds.
+IF (debtor_account.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;
+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;
+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.
+THEN
+SELECT
+  INTO new_creditor_balance
+  FROM amount_add(creditor_account.balance,
+                  in_amount);
+will_creditor_have_debit=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;
+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;
+
+-- now actually create the bank transaction.
+-- debtor side:
+INSERT INTO bank_account_transactions (
+  ,creditor_iban 
+  ,creditor_bic
+  ,creditor_name
+  ,debtor_iban 
+  ,debtor_bic
+  ,debtor_name
+  ,subject
+  ,amount taler_amount
+  ,transaction_date
+  ,account_servicer_reference
+  ,payment_information_id
+  ,end_to_end_id
+  ,direction direction_enum
+  ,bank_account_id
+  )
+VALUES (
+  creditor_account.iban,
+  creditor_account.bic,
+  creditor_account.name,
+  debtor_account.iban,
+  debtor_account.bic,
+  debtor_account.name,
+  in_subject,
+  in_amount,
+  in_transaction_date,
+  in_account_servicer_reference,
+  in_payment_information_id,
+  in_end_to_end_id,
+  "debit",
+  in_debtor_account_id
+);
+
+-- debtor side:
+INSERT INTO bank_account_transactions (
+  ,creditor_iban
+  ,creditor_bic
+  ,creditor_name
+  ,debtor_iban
+  ,debtor_bic
+  ,debtor_name
+  ,subject
+  ,amount taler_amount
+  ,transaction_date
+  ,account_servicer_reference
+  ,payment_information_id
+  ,end_to_end_id
+  ,direction direction_enum
+  ,bank_account_id
+  )
+VALUES (
+  creditor_account.iban,
+  creditor_account.bic,
+  creditor_account.name,
+  debtor_account.iban,
+  debtor_account.bic,
+  debtor_account.name,
+  in_subject,
+  in_amount,
+  in_transaction_date,
+  in_account_servicer_reference,
+  in_payment_information_id,
+  in_end_to_end_id, -- does this interest the receiving party?
+  "credit",
+  in_creditor_account_id
+);
+out_nx_debtor=TRUE;
+out_nx_creditor=TRUE;
+out_balance_insufficient=FALSE;
+END $$;
+COMMIT;
diff --git a/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt b/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt
index 6eae4aff..76b99b8a 100644
--- a/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt
+++ b/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt
@@ -265,7 +265,7 @@ class Database(private val dbConfig: String) {
     ): BankTransactionResult {
         reconnect()
         val stmt = prepare("""
-            SELECT out_nx_creditor, out_nx_debitor, out_balance_insufficient
+            SELECT out_nx_creditor, out_nx_debtor, out_balance_insufficient
             FROM bank_wire_transfer(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
         """ // FIXME: adjust balances.
         )