libeufin

libeufin-bank-procedures.sql (71672B)

---

 --
 -- This file is part of TALER
 -- Copyright (C) 2023-2026 Taler Systems SA
 --
 -- TALER is free software; you can redistribute it and/or modify it under the
 -- terms of the GNU General Public License as published by the Free Software
 -- Foundation; either version 3, or (at your option) any later version.
 --
 -- TALER 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 General Public License for more details.
 --
 -- You should have received a copy of the GNU General Public License along with
 -- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
 
 BEGIN;
 SET search_path TO libeufin_bank;
 
 -- Remove all existing functions
 DO
 $do$
 DECLARE
   _sql text;
 BEGIN
   SELECT INTO _sql
         string_agg(format('DROP %s %s CASCADE;'
                         , CASE prokind
                             WHEN 'f' THEN 'FUNCTION'
                             WHEN 'p' THEN 'PROCEDURE'
                           END
                         , oid::regprocedure)
                   , E'\n')
   FROM   pg_proc
   WHERE  pronamespace = 'libeufin_bank'::regnamespace;
 
   IF _sql IS NOT NULL THEN
     EXECUTE _sql;
   END IF;
 END
 $do$;
 
 CREATE FUNCTION url_encode(input TEXT)
 RETURNS TEXT
 LANGUAGE plpgsql IMMUTABLE AS $$
 DECLARE
     result TEXT := '';
     char TEXT;
 BEGIN
     FOR i IN 1..length(input) LOOP
         char := substring(input FROM i FOR 1);
         IF char ~ '[A-Za-z0-9\-._~]' THEN
             result := result || char;
         ELSE
             result := result || '%' || lpad(upper(to_hex(ascii(char))), 2, '0');
         END IF;
     END LOOP;
     RETURN result;
 END;
 $$;
 
 CREATE OR REPLACE FUNCTION sort_uniq(anyarray)
 RETURNS anyarray LANGUAGE SQL IMMUTABLE AS $$
   SELECT COALESCE(array_agg(DISTINCT x ORDER BY x), $1[0:0])
   FROM unnest($1) AS t(x);
 $$;
 
 CREATE FUNCTION amount_normalize(
     IN amount taler_amount
   ,OUT normalized taler_amount
 )
 LANGUAGE plpgsql IMMUTABLE AS $$
 BEGIN
   normalized.val = amount.val + amount.frac / 100000000;
   IF (normalized.val > 1::INT8<<52) THEN
     RAISE EXCEPTION 'amount value overflowed';
   END IF;
   normalized.frac = amount.frac % 100000000;
 
 END $$;
 COMMENT ON FUNCTION amount_normalize
   IS 'Returns the normalized amount by adding to the .val the value of (.frac / 100000000) and removing the modulus 100000000 from .frac.'
       'It raises an exception when the resulting .val is larger than 2^52';
 
 CREATE FUNCTION amount_add(
    IN l taler_amount
   ,IN r taler_amount
   ,OUT sum taler_amount
 )
 LANGUAGE plpgsql IMMUTABLE AS $$
 BEGIN
   sum = (l.val + r.val, l.frac + r.frac);
   SELECT normalized.val, normalized.frac INTO sum.val, sum.frac FROM amount_normalize(sum) as normalized;
 END $$;
 COMMENT ON FUNCTION amount_add
   IS 'Returns the normalized sum of two amounts. It raises an exception when the resulting .val is larger than 2^52';
 
 CREATE FUNCTION amount_left_minus_right(
   IN l taler_amount
  ,IN r taler_amount
  ,OUT diff taler_amount
  ,OUT ok BOOLEAN
 )
 LANGUAGE plpgsql IMMUTABLE AS $$
 BEGIN
 diff = l;
 IF diff.frac < r.frac THEN
   IF diff.val <= 0 THEN
     diff = (-1, -1);
     ok = FALSE;
     RETURN;
   END IF;
   diff.frac = diff.frac + 100000000;
   diff.val = diff.val - 1;
 END IF;
 IF diff.val < r.val THEN
   diff = (-1, -1);
   ok = FALSE;
   RETURN;
 END IF;
 diff.val = diff.val - r.val;
 diff.frac = diff.frac - r.frac;
 ok = TRUE;
 END $$;
 COMMENT ON FUNCTION amount_left_minus_right
   IS 'Subtracts the right amount from the left and returns the difference and TRUE, if the left amount is larger than the right, or an invalid amount and FALSE otherwise.';
 
 CREATE FUNCTION account_balance_is_sufficient(
   IN in_account_id INT8,
   IN in_amount taler_amount,
   IN in_wire_transfer_fees taler_amount,
   IN in_min_amount taler_amount,
   IN in_max_amount taler_amount,
   OUT out_balance_insufficient BOOLEAN,
   OUT out_bad_amount BOOLEAN
 )
 LANGUAGE plpgsql STABLE AS $$
 DECLARE
 account_has_debt BOOLEAN;
 account_balance taler_amount;
 account_max_debt taler_amount;
 amount_with_fee taler_amount;
 BEGIN
 
 -- Check min and max
 SELECT (SELECT in_min_amount IS NOT NULL AND NOT ok FROM amount_left_minus_right(in_amount, in_min_amount)) OR
   (SELECT in_max_amount IS NOT NULL AND NOT ok FROM amount_left_minus_right(in_max_amount, in_amount))
   INTO out_bad_amount;
 IF out_bad_amount THEN
   RETURN;
 END IF;
 
 -- Add fees to the amount
 IF in_wire_transfer_fees IS NOT NULL AND in_wire_transfer_fees != (0, 0)::taler_amount THEN
   SELECT sum.val, sum.frac
     INTO amount_with_fee.val, amount_with_fee.frac
     FROM amount_add(in_amount, in_wire_transfer_fees) as sum;
 ELSE
   amount_with_fee = in_amount;
 END IF;
 
 -- Get account info, we expect the account to exist
 SELECT
   has_debt,
   (balance).val, (balance).frac,
   (max_debt).val, (max_debt).frac
   INTO
     account_has_debt,
     account_balance.val, account_balance.frac,
     account_max_debt.val, account_max_debt.frac
   FROM bank_accounts WHERE bank_account_id=in_account_id;
 
 -- Check enough funds
 IF account_has_debt THEN
   -- debt case: simply checking against the max debt allowed.
   SELECT sum.val, sum.frac
     INTO account_balance.val, account_balance.frac
     FROM amount_add(account_balance, amount_with_fee) as sum;
   SELECT NOT ok
     INTO out_balance_insufficient
     FROM amount_left_minus_right(account_max_debt, account_balance);
   IF out_balance_insufficient THEN
     RETURN;
   END IF;
 ELSE -- not a debt account
   SELECT NOT ok
     INTO out_balance_insufficient
     FROM amount_left_minus_right(account_balance, amount_with_fee);
   IF out_balance_insufficient THEN
      -- debtor will switch to debt: determine their new negative balance.
     SELECT
       (diff).val, (diff).frac
       INTO
         account_balance.val, account_balance.frac
       FROM amount_left_minus_right(amount_with_fee, account_balance);
     SELECT NOT ok
       INTO out_balance_insufficient
       FROM amount_left_minus_right(account_max_debt, account_balance);
     IF out_balance_insufficient THEN
       RETURN;
     END IF;
   END IF;
 END IF;
 END $$;
 COMMENT ON FUNCTION account_balance_is_sufficient IS 'Check if an account have enough fund to transfer an amount.';
 
 CREATE FUNCTION account_max_amount(
   IN in_account_id INT8,
   IN in_max_amount taler_amount,
   OUT out_max_amount taler_amount
 )
 LANGUAGE plpgsql STABLE AS $$
 BEGIN
 -- add balance and max_debt
 WITH computed AS (
   SELECT CASE has_debt
     WHEN false THEN amount_add(balance, max_debt)
     ELSE (SELECT diff FROM amount_left_minus_right(max_debt, balance))
   END AS amount
   FROM bank_accounts WHERE bank_account_id=in_account_id
 ) SELECT (amount).val, (amount).frac
   INTO out_max_amount.val, out_max_amount.frac
   FROM computed;
 
 IF in_account_id IS NULL 
   OR in_max_amount.val < out_max_amount.val
   OR (in_max_amount.val = out_max_amount.val AND in_max_amount.frac < out_max_amount.frac) THEN
   out_max_amount = in_max_amount;
 END IF;
 END $$;
 
 CREATE FUNCTION create_token(
   IN in_username TEXT,
   IN in_content BYTEA,
   IN in_creation_time INT8,
   IN in_expiration_time INT8,
   IN in_scope token_scope_enum,
   IN in_refreshable BOOLEAN,
   IN in_description TEXT,
   IN in_is_tan BOOLEAN,
   OUT out_tan_required BOOLEAN,
   OUT out_token_id INT8
 )
 LANGUAGE plpgsql AS $$
 DECLARE
 local_customer_id INT8;
 BEGIN
 -- Get account id and check if 2FA is required
 SELECT customer_id, NOT in_is_tan AND cardinality(tan_channels) > 0
 INTO local_customer_id, out_tan_required
 FROM customers JOIN bank_accounts ON owning_customer_id = customer_id
 WHERE username = in_username AND deleted_at IS NULL;
 IF out_tan_required THEN
   RETURN;
 END IF;
 INSERT INTO bearer_tokens (
   content,
   creation_time,
   expiration_time,
   scope,
   bank_customer,
   is_refreshable,
   description,
   last_access
 ) VALUES (
   in_content,
   in_creation_time,
   in_expiration_time,
   in_scope,
   local_customer_id,
   in_refreshable,
   in_description,
   in_creation_time
 ) RETURNING bearer_token_id INTO out_token_id;
 END $$;
 
 CREATE FUNCTION bank_wire_transfer(
   IN in_creditor_account_id INT8,
   IN in_debtor_account_id INT8,
   IN in_subject TEXT,
   IN in_amount taler_amount,
   IN in_timestamp INT8,
   IN in_wire_transfer_fees taler_amount,
   IN in_min_amount taler_amount,
   IN in_max_amount taler_amount,
   -- Error status
   OUT out_balance_insufficient BOOLEAN,
   OUT out_bad_amount BOOLEAN,
   -- Success return
   OUT out_credit_row_id INT8,
   OUT out_debit_row_id INT8
 )
 LANGUAGE plpgsql AS $$
 DECLARE
 has_fee BOOLEAN;
 amount_with_fee taler_amount;
 admin_account_id INT8;
 admin_has_debt BOOLEAN;
 admin_balance taler_amount;
 admin_payto TEXT;
 admin_name TEXT;
 debtor_has_debt BOOLEAN;
 debtor_balance taler_amount;
 debtor_max_debt taler_amount;
 debtor_payto TEXT;
 debtor_name TEXT;
 creditor_has_debt BOOLEAN;
 creditor_balance taler_amount;
 creditor_payto TEXT;
 creditor_name TEXT;
 tmp_balance taler_amount;
 BEGIN
 -- Check min and max
 SELECT (SELECT in_min_amount IS NOT NULL AND NOT ok FROM amount_left_minus_right(in_amount, in_min_amount)) OR
   (SELECT in_max_amount IS NOT NULL AND NOT ok FROM amount_left_minus_right(in_max_amount, in_amount))
   INTO out_bad_amount;
 IF out_bad_amount THEN
   RETURN;
 END IF;
 
 has_fee = in_wire_transfer_fees IS NOT NULL AND in_wire_transfer_fees != (0, 0)::taler_amount;
 IF has_fee THEN
   -- Retrieve admin info
   SELECT
     bank_account_id, has_debt,
     (balance).val, (balance).frac,
     internal_payto, customers.name
     INTO
       admin_account_id, admin_has_debt,
       admin_balance.val, admin_balance.frac,
       admin_payto, admin_name
     FROM bank_accounts
       JOIN customers ON customer_id=owning_customer_id
     WHERE username = 'admin';
   IF NOT FOUND THEN
     RAISE EXCEPTION 'No admin';
   END IF;
 END IF;
 
 -- Retrieve debtor info
 SELECT
   has_debt,
   (balance).val, (balance).frac,
   (max_debt).val, (max_debt).frac,
   internal_payto, customers.name
   INTO
     debtor_has_debt,
     debtor_balance.val, debtor_balance.frac,
     debtor_max_debt.val, debtor_max_debt.frac,
     debtor_payto, debtor_name
   FROM bank_accounts
     JOIN customers ON customer_id=owning_customer_id
   WHERE bank_account_id=in_debtor_account_id;
 IF NOT FOUND THEN
   RAISE EXCEPTION 'Unknown debtor %', in_debtor_account_id;
 END IF;
 -- Retrieve creditor info
 SELECT
   has_debt,
   (balance).val, (balance).frac,
   internal_payto, customers.name
   INTO
     creditor_has_debt,
     creditor_balance.val, creditor_balance.frac,
     creditor_payto, creditor_name
   FROM bank_accounts
     JOIN customers ON customer_id=owning_customer_id
   WHERE bank_account_id=in_creditor_account_id;
 IF NOT FOUND THEN
   RAISE EXCEPTION 'Unknown creditor %', in_creditor_account_id;
 END IF;
 
 -- Add fees to the amount
 IF has_fee AND admin_account_id != in_debtor_account_id THEN
   SELECT sum.val, sum.frac
     INTO amount_with_fee.val, amount_with_fee.frac
     FROM amount_add(in_amount, in_wire_transfer_fees) as sum;
 ELSE
   has_fee=false;
   amount_with_fee = in_amount;
 END IF;
 
 -- DEBTOR SIDE
 -- check debtor has enough funds.
 IF debtor_has_debt THEN
   -- debt case: simply checking against the max debt allowed.
   SELECT sum.val, sum.frac
     INTO debtor_balance.val, debtor_balance.frac
     FROM amount_add(debtor_balance, amount_with_fee) as sum;
   SELECT NOT ok
     INTO out_balance_insufficient
     FROM amount_left_minus_right(debtor_max_debt,
                                  debtor_balance);
   IF out_balance_insufficient THEN
     RETURN;
   END IF;
 ELSE -- not a debt account
   SELECT
     NOT ok,
     (diff).val, (diff).frac
     INTO
       out_balance_insufficient,
       tmp_balance.val,
       tmp_balance.frac
     FROM amount_left_minus_right(debtor_balance,
                                  amount_with_fee);
   IF NOT out_balance_insufficient THEN -- debtor has enough funds in the (positive) balance.
     debtor_balance=tmp_balance;
   ELSE -- debtor will switch to debt: determine their new negative balance.
     SELECT
       (diff).val, (diff).frac
       INTO
         debtor_balance.val, debtor_balance.frac
       FROM amount_left_minus_right(amount_with_fee,
                                    debtor_balance);
     debtor_has_debt=TRUE;
     SELECT NOT ok
       INTO out_balance_insufficient
       FROM amount_left_minus_right(debtor_max_debt,
                                    debtor_balance);
     IF out_balance_insufficient THEN
       RETURN;
     END IF;
   END IF;
 END IF;
 
 -- 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_has_debt THEN -- easy case.
   SELECT sum.val, sum.frac
     INTO creditor_balance.val, creditor_balance.frac
     FROM amount_add(creditor_balance, in_amount) as sum;
 ELSE -- creditor had debit but MIGHT switch to credit.
   SELECT
     (diff).val, (diff).frac,
     NOT ok
     INTO
       tmp_balance.val, tmp_balance.frac,
       creditor_has_debt
     FROM amount_left_minus_right(in_amount,
                                  creditor_balance);
   IF NOT creditor_has_debt THEN
     creditor_balance=tmp_balance;
   ELSE
     -- the amount is not enough to bring the receiver
     -- to a credit state, switch operators to calculate the new balance.
     SELECT
       (diff).val, (diff).frac
       INTO creditor_balance.val, creditor_balance.frac
       FROM amount_left_minus_right(creditor_balance,
 	                           in_amount);
   END IF;
 END IF;
 
 -- ADMIN SIDE.
 -- Here we figure out whether the administrator would switch
 -- from debit to a credit situation, and adjust the balance
 -- accordingly.
 IF has_fee THEN
   IF NOT admin_has_debt THEN -- easy case.
     SELECT sum.val, sum.frac
       INTO admin_balance.val, admin_balance.frac
       FROM amount_add(admin_balance, in_wire_transfer_fees) as sum;
   ELSE -- creditor had debit but MIGHT switch to credit.
     SELECT (diff).val, (diff).frac, NOT ok
       INTO
         tmp_balance.val, tmp_balance.frac,
         admin_has_debt
       FROM amount_left_minus_right(in_wire_transfer_fees, admin_balance);
     IF NOT admin_has_debt THEN
       admin_balance=tmp_balance;
     ELSE
       -- the amount is not enough to bring the receiver
       -- to a credit state, switch operators to calculate the new balance.
       SELECT (diff).val, (diff).frac
         INTO admin_balance.val, admin_balance.frac
         FROM amount_left_minus_right(admin_balance, in_wire_transfer_fees);
     END IF;
   END IF;
 END IF;
 
 -- Lock account in order to prevent deadlocks
 PERFORM FROM bank_accounts
   WHERE bank_account_id IN (in_debtor_account_id, in_creditor_account_id, admin_account_id)
   ORDER BY bank_account_id
   FOR UPDATE;
 
 -- now actually create the bank transaction.
 -- debtor side:
 INSERT INTO bank_account_transactions (
   creditor_payto
   ,creditor_name
   ,debtor_payto
   ,debtor_name
   ,subject
   ,amount
   ,transaction_date
   ,direction
   ,bank_account_id
   )
 VALUES (
   creditor_payto,
   creditor_name,
   debtor_payto,
   debtor_name,
   in_subject,
   in_amount,
   in_timestamp,
   'debit',
   in_debtor_account_id
 ) RETURNING bank_transaction_id INTO out_debit_row_id;
 
 -- debtor side:
 INSERT INTO bank_account_transactions (
   creditor_payto
   ,creditor_name
   ,debtor_payto
   ,debtor_name
   ,subject
   ,amount
   ,transaction_date
   ,direction
   ,bank_account_id
   )
 VALUES (
   creditor_payto,
   creditor_name,
   debtor_payto,
   debtor_name,
   in_subject,
   in_amount,
   in_timestamp,
   'credit',
   in_creditor_account_id
 ) RETURNING bank_transaction_id INTO out_credit_row_id;
 
 -- checks and balances set up, now update bank accounts.
 UPDATE bank_accounts
 SET
   balance=debtor_balance,
   has_debt=debtor_has_debt
 WHERE bank_account_id=in_debtor_account_id;
 
 UPDATE bank_accounts
 SET
   balance=creditor_balance,
   has_debt=creditor_has_debt
 WHERE bank_account_id=in_creditor_account_id;
 
 -- Fee part
 IF has_fee THEN
   INSERT INTO bank_account_transactions (
     creditor_payto
     ,creditor_name
     ,debtor_payto
     ,debtor_name
     ,subject
     ,amount
     ,transaction_date
     ,direction
     ,bank_account_id
     )
   VALUES (
     admin_payto,
     admin_name,
     debtor_payto,
     debtor_name,
     'wire transfer fees for tx ' || out_debit_row_id,
     in_wire_transfer_fees,
     in_timestamp,
     'debit',
     in_debtor_account_id
   ), (
     admin_payto,
     admin_name,
     debtor_payto,
     debtor_name,
     'wire transfer fees for tx ' || out_debit_row_id,
     in_wire_transfer_fees,
     in_timestamp,
     'credit',
     admin_account_id
   );
 
   UPDATE bank_accounts
   SET
     balance=admin_balance,
     has_debt=admin_has_debt
   WHERE bank_account_id=admin_account_id;
 END IF;
 
 -- notify new transaction
 PERFORM pg_notify('bank_tx', in_debtor_account_id || ' ' || in_creditor_account_id || ' ' || out_debit_row_id || ' ' || out_credit_row_id);
 END $$;
 
 CREATE FUNCTION account_delete(
   IN in_username TEXT,
   IN in_timestamp INT8,
   IN in_is_tan BOOLEAN,
   OUT out_not_found BOOLEAN,
   OUT out_balance_not_zero BOOLEAN,
   OUT out_tan_required BOOLEAN
 )
 LANGUAGE plpgsql AS $$
 DECLARE
 my_customer_id INT8;
 BEGIN
 -- check if account exists, has zero balance and if 2FA is required
 SELECT
    customer_id
   ,NOT in_is_tan AND cardinality(tan_channels) > 0
   ,(balance).val != 0 OR (balance).frac != 0
   INTO
      my_customer_id
     ,out_tan_required
     ,out_balance_not_zero
   FROM customers
     JOIN bank_accounts ON owning_customer_id = customer_id
   WHERE username = in_username AND deleted_at IS NULL;
 IF NOT FOUND OR out_balance_not_zero OR out_tan_required THEN
   out_not_found=NOT FOUND;
   RETURN;
 END IF;
 
 -- actual deletion
 UPDATE customers SET deleted_at = in_timestamp WHERE customer_id = my_customer_id;
 END $$;
 COMMENT ON FUNCTION account_delete IS 'Deletes an account if the balance is zero';
 
 CREATE FUNCTION register_incoming(
   IN in_tx_row_id INT8,
   IN in_type taler_incoming_type,
   IN in_metadata BYTEA,
   IN in_account_id INT8,
   IN in_authorization_pub BYTEA,
   IN in_authorization_sig BYTEA
 )
 RETURNS void
 LANGUAGE plpgsql AS $$
 DECLARE
 local_amount taler_amount;
 local_taler_in_id INT8;
 BEGIN
 -- Register incoming transaction
 INSERT INTO taler_exchange_incoming (
   metadata,
   bank_transaction,
   type,
   authorization_pub,
   authorization_sig
 ) VALUES (
   in_metadata,
   in_tx_row_id,
   in_type,
   in_authorization_pub,
   in_authorization_sig
 ) RETURNING exchange_incoming_id INTO local_taler_in_id;
 -- Update stats
 IF in_type = 'reserve' THEN
   SELECT (amount).val, (amount).frac
     INTO local_amount.val, local_amount.frac
     FROM bank_account_transactions WHERE bank_transaction_id=in_tx_row_id;
   CALL stats_register_payment('taler_in', NULL, local_amount, null);
 END IF;
 -- Notify new incoming transaction
 PERFORM pg_notify('bank_incoming_tx', in_account_id || ' ' || local_taler_in_id);
 END $$;
 COMMENT ON FUNCTION register_incoming
   IS 'Register a bank transaction as a taler incoming transaction and announce it';
 
 CREATE FUNCTION bounce(
   IN in_debtor_account_id INT8,
   IN in_credit_transaction_id INT8,
   IN in_bounce_cause TEXT,
   IN in_timestamp INT8
 )
 RETURNS void
 LANGUAGE plpgsql AS $$
 DECLARE
 local_creditor_account_id INT8;
 local_amount taler_amount;
 BEGIN
 -- Load transaction info
 SELECT (amount).frac, (amount).val, bank_account_id
 INTO local_amount.frac, local_amount.val, local_creditor_account_id
 FROM bank_account_transactions
 WHERE bank_transaction_id=in_credit_transaction_id;
 
 -- No error can happens because an opposite transaction already took place in the same transaction
 PERFORM bank_wire_transfer(
   in_debtor_account_id,
   local_creditor_account_id,
   'Bounce ' || in_credit_transaction_id || ': ' || in_bounce_cause,
   local_amount,
   in_timestamp,
   NULL,
   NULL,
   NULL
 );
 
 -- Delete from pending if any
 DELETE FROM pending_recurrent_incoming_transactions WHERE bank_transaction_id = in_credit_transaction_id;
 END$$;
 
 CREATE FUNCTION make_incoming(
   IN in_creditor_account_id INT8,
   IN in_debtor_account_id INT8,
   IN in_subject TEXT,
   IN in_amount taler_amount,
   IN in_timestamp INT8,
   IN in_type taler_incoming_type,
   IN in_metadata BYTEA,
   IN in_wire_transfer_fees taler_amount,
   IN in_min_amount taler_amount,
   IN in_max_amount taler_amount,
   -- Error status
   OUT out_balance_insufficient BOOLEAN,
   OUT out_bad_amount BOOLEAN,
   OUT out_reserve_pub_reuse BOOLEAN,
   OUT out_mapping_reuse BOOLEAN,
   OUT out_unknown_mapping BOOLEAN,
   -- Success return
   OUT out_pending BOOLEAN,
   OUT out_credit_row_id INT8,
   OUT out_debit_row_id INT8
 )
 LANGUAGE plpgsql AS $$
 DECLARE
 local_withdrawal_uuid UUID;
 local_authorization_pub BYTEA;
 local_authorization_sig BYTEA;
 BEGIN
 out_pending=FALSE;
 
 -- Resolve mapping logic
 IF in_type = 'map' THEN
   SELECT prepared_transfers.type, account_pub, authorization_pub, authorization_sig, withdrawal_uuid,
       bank_transaction_id IS NOT NULL AND NOT recurrent,
       bank_transaction_id IS NOT NULL AND recurrent
     INTO in_type, in_metadata, local_authorization_pub, local_authorization_sig, local_withdrawal_uuid, out_mapping_reuse, out_pending
     FROM prepared_transfers
     LEFT JOIN taler_withdrawal_operations USING (withdrawal_id)
     WHERE authorization_pub = in_metadata;
   out_unknown_mapping = NOT FOUND;
   IF out_unknown_mapping OR out_mapping_reuse THEN
     RETURN;
   END IF;
 END IF;
 
 -- Check reserve pub reuse
 out_reserve_pub_reuse=in_type = 'reserve' AND NOT out_pending AND EXISTS(SELECT FROM taler_exchange_incoming WHERE metadata = in_metadata AND type = 'reserve');
 IF out_reserve_pub_reuse THEN
   RETURN;
 END IF;
 
 -- Perform bank wire transfer
 SELECT
   transfer.out_balance_insufficient,
   transfer.out_bad_amount,
   transfer.out_credit_row_id,
   transfer.out_debit_row_id
   INTO
     out_balance_insufficient,
     out_bad_amount,
     out_credit_row_id,
     out_debit_row_id
   FROM bank_wire_transfer(
     in_creditor_account_id,
     in_debtor_account_id,
     in_subject,
     in_amount,
     in_timestamp,
     in_wire_transfer_fees,
     in_min_amount,
     in_max_amount
   ) as transfer;
 IF out_balance_insufficient OR out_bad_amount THEN
   RETURN;
 END IF;
 
 IF out_pending THEN
   -- Delay talerable registration until mapping again
   INSERT INTO pending_recurrent_incoming_transactions (bank_transaction_id, debtor_account_id, authorization_pub)
     VALUES (out_credit_row_id, in_debtor_account_id, local_authorization_pub);
 ELSE
   UPDATE prepared_transfers
   SET bank_transaction_id = out_credit_row_id
   WHERE (
     bank_transaction_id IS NULL AND account_pub = in_metadata AND in_type=type AND type='reserve'
   ) OR authorization_pub = local_authorization_pub;
   IF local_withdrawal_uuid IS NOT NULL THEN
     PERFORM abort_taler_withdrawal(local_withdrawal_uuid);
   END IF;
   PERFORM register_incoming(out_credit_row_id, in_type, in_metadata, in_creditor_account_id, local_authorization_pub, local_authorization_sig);
 END IF;
 END $$;
 
 
 CREATE FUNCTION taler_transfer(
   IN in_request_uid BYTEA,
   IN in_wtid BYTEA,
   IN in_subject TEXT,
   IN in_amount taler_amount,
   IN in_exchange_base_url TEXT,
   IN in_metadata TEXT,
   IN in_credit_account_payto TEXT,
   IN in_username TEXT,
   IN in_timestamp INT8,
   IN in_conversion BOOLEAN,
   -- Error status
   OUT out_debtor_not_found BOOLEAN,
   OUT out_debtor_not_exchange BOOLEAN,
   OUT out_both_exchanges BOOLEAN,
   OUT out_creditor_admin BOOLEAN,
   OUT out_request_uid_reuse BOOLEAN,
   OUT out_wtid_reuse BOOLEAN,
   OUT out_exchange_balance_insufficient BOOLEAN,
   -- Success return
   OUT out_tx_row_id INT8,
   OUT out_timestamp INT8
 )
 LANGUAGE plpgsql AS $$
 DECLARE
 exchange_account_id INT8;
 creditor_account_id INT8;
 account_conversion_rate_class_id INT8;
 creditor_name TEXT;
 creditor_admin BOOLEAN;
 credit_row_id INT8;
 debit_row_id INT8;
 outgoing_id INT8;
 bounce_tx INT8;
 bounce_amount taler_amount;
 BEGIN
 -- Check for idempotence and conflict
 SELECT (amount != in_amount
           OR creditor_payto != in_credit_account_payto
           OR exchange_base_url != in_exchange_base_url
           OR metadata IS DISTINCT FROM in_metadata
           OR wtid != in_wtid)
         ,transfer_operation_id, transfer_date
   INTO out_request_uid_reuse, out_tx_row_id, out_timestamp
   FROM transfer_operations
   WHERE request_uid = in_request_uid;
 IF found THEN
   RETURN;
 END IF;
 out_wtid_reuse = EXISTS(SELECT FROM transfer_operations WHERE wtid = in_wtid);
 IF out_wtid_reuse THEN
   RETURN;
 END IF;
 out_timestamp=in_timestamp;
 -- Find exchange bank account id
 SELECT
   bank_account_id, NOT is_taler_exchange, conversion_rate_class_id
   INTO exchange_account_id, out_debtor_not_exchange, account_conversion_rate_class_id
   FROM bank_accounts
       JOIN customers
         ON customer_id=owning_customer_id
   WHERE username = in_username AND deleted_at IS NULL;
 out_debtor_not_found=NOT FOUND;
 IF out_debtor_not_found OR out_debtor_not_exchange THEN
   RETURN;
 END IF;
 -- Find creditor bank account id
 SELECT
   bank_account_id, is_taler_exchange, username = 'admin'
   INTO creditor_account_id, out_both_exchanges, creditor_admin
   FROM bank_accounts
   JOIN customers ON owning_customer_id=customer_id
   WHERE internal_payto = in_credit_account_payto;
 IF NOT FOUND THEN
   -- Register failure
   INSERT INTO transfer_operations (
     request_uid,
     wtid,
     amount,
     exchange_base_url,
     metadata,
     transfer_date,
     exchange_outgoing_id,
     creditor_payto,
     status,
     status_msg,
     exchange_id
   ) VALUES (
     in_request_uid,
     in_wtid,
     in_amount,
     in_exchange_base_url,
     in_metadata,
     in_timestamp,
     NULL,
     in_credit_account_payto,
     'permanent_failure',
     'Unknown account',
     exchange_account_id
   ) RETURNING transfer_operation_id INTO out_tx_row_id;
   RETURN;
 ELSIF out_both_exchanges THEN
   RETURN;
 END IF;
 
 IF creditor_admin THEN
   -- Check if this is a conversion bounce
   IF NOT in_conversion THEN
     out_creditor_admin=TRUE;
     RETURN;
   END IF;
 
   -- Find the bounced transaction
   SELECT (amount).val, (amount).frac, incoming_transaction_id
     INTO bounce_amount.val, bounce_amount.frac, bounce_tx
     FROM libeufin_nexus.incoming_transactions
     JOIN libeufin_nexus.talerable_incoming_transactions USING (incoming_transaction_id)
     WHERE metadata=in_wtid AND type='reserve';
   IF NOT FOUND THEN
     -- Register failure
     INSERT INTO transfer_operations (
       request_uid,
       wtid,
       amount,
       exchange_base_url,
       metadata,
       transfer_date,
       exchange_outgoing_id,
       creditor_payto,
       status,
       status_msg,
       exchange_id
     ) VALUES (
       in_request_uid,
       in_wtid,
       in_amount,
       in_exchange_base_url,
       in_metadata,
       in_timestamp,
       NULL,
       in_credit_account_payto,
       'permanent_failure',
       'Unknown bounced transaction',
       exchange_account_id
     ) RETURNING transfer_operation_id INTO out_tx_row_id;
     RETURN;
   END IF;
 
   -- Bounce the transaction
   PERFORM libeufin_nexus.bounce_incoming(
     bounce_tx
     ,((bounce_amount).val, (bounce_amount).frac)::libeufin_nexus.taler_amount
     ,libeufin_nexus.ebics_id_gen()
     ,in_timestamp
     ,'exchange bounced'
   );
 END IF;
 -- Perform bank transfer
 SELECT
   out_balance_insufficient,
   out_debit_row_id, out_credit_row_id
   INTO
     out_exchange_balance_insufficient,
     debit_row_id, credit_row_id
   FROM bank_wire_transfer(
     creditor_account_id,
     exchange_account_id,
     in_subject,
     in_amount,
     in_timestamp,
     NULL,
     NULL,
     NULL
   );
 IF out_exchange_balance_insufficient THEN
   RETURN;
 END IF;
 -- Register outgoing transaction
 INSERT INTO taler_exchange_outgoing (
   bank_transaction
 ) VALUES (
   debit_row_id
 ) RETURNING exchange_outgoing_id INTO outgoing_id;
 -- Update stats
 CALL stats_register_payment('taler_out', NULL, in_amount, null);
 -- Register success
 INSERT INTO transfer_operations (
   request_uid,
   wtid,
   amount,
   exchange_base_url,
   metadata,
   transfer_date,
   exchange_outgoing_id,
   creditor_payto,
   status,
   status_msg,
   exchange_id
 ) VALUES (
   in_request_uid,
   in_wtid,
   in_amount,
   in_exchange_base_url,
   in_metadata,
   in_timestamp,
   outgoing_id,
   in_credit_account_payto,
   'success',
   NULL,
   exchange_account_id
 ) RETURNING transfer_operation_id INTO out_tx_row_id;
 
 -- Notify new transaction
 PERFORM pg_notify('bank_outgoing_tx', exchange_account_id || ' ' || creditor_account_id || ' ' || debit_row_id || ' ' || credit_row_id);
 
 IF creditor_admin THEN
   -- Create cashout operation
   INSERT INTO cashout_operations (
     request_uid
     ,amount_debit
     ,amount_credit
     ,creation_time
     ,bank_account
     ,subject
     ,local_transaction
   ) VALUES (
     NULL
     ,in_amount
     ,bounce_amount
     ,in_timestamp
     ,exchange_account_id
     ,in_subject
     ,debit_row_id
   );
 
   -- update stats
   CALL stats_register_payment('cashout', NULL, in_amount, bounce_amount);
 END IF;
 END $$;
 COMMENT ON FUNCTION taler_transfer IS 'Create an outgoing taler transaction and register it';
 
 CREATE FUNCTION taler_add_incoming(
   IN in_key BYTEA,
   IN in_subject TEXT,
   IN in_amount taler_amount,
   IN in_debit_account_payto TEXT,
   IN in_username TEXT,
   IN in_timestamp INT8,
   IN in_type taler_incoming_type,
   -- Error status
   OUT out_creditor_not_found BOOLEAN,
   OUT out_creditor_not_exchange BOOLEAN,
   OUT out_debtor_not_found BOOLEAN,
   OUT out_both_exchanges BOOLEAN,
   OUT out_reserve_pub_reuse BOOLEAN,
   OUT out_mapping_reuse BOOLEAN,
   OUT out_unknown_mapping BOOLEAN,
   OUT out_debitor_balance_insufficient BOOLEAN,
   -- Success return
   OUT out_tx_row_id INT8,
   OUT out_pending BOOLEAN
 )
 LANGUAGE plpgsql AS $$
 DECLARE
 exchange_bank_account_id INT8;
 sender_bank_account_id INT8;
 BEGIN
 -- Find exchange bank account id
 SELECT
   bank_account_id, NOT is_taler_exchange
   INTO exchange_bank_account_id, out_creditor_not_exchange
   FROM bank_accounts
       JOIN customers
         ON customer_id=owning_customer_id
   WHERE username = in_username AND deleted_at IS NULL;
 IF NOT FOUND OR out_creditor_not_exchange THEN
   out_creditor_not_found=NOT FOUND;
   RETURN;
 END IF;
 -- Find sender bank account id
 SELECT
   bank_account_id, is_taler_exchange
   INTO sender_bank_account_id, out_both_exchanges
   FROM bank_accounts
   WHERE internal_payto = in_debit_account_payto;
 IF NOT FOUND OR out_both_exchanges THEN
   out_debtor_not_found=NOT FOUND;
   RETURN;
 END IF;
 -- Perform bank transfer
 SELECT
   out_balance_insufficient,
   out_credit_row_id,
   t.out_reserve_pub_reuse,
   t.out_mapping_reuse,
   t.out_unknown_mapping
   INTO
     out_debitor_balance_insufficient,
     out_tx_row_id,
     out_reserve_pub_reuse,
     out_mapping_reuse,
     out_unknown_mapping
   FROM make_incoming(
     exchange_bank_account_id,
     sender_bank_account_id,
     in_subject,
     in_amount,
     in_timestamp,
     in_type,
     in_key,
     NULL,
     NULL,
     NULL
   ) as t;
 END $$;
 COMMENT ON FUNCTION taler_add_incoming IS 'Create an incoming taler transaction and register it';
 
 CREATE 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 INT8,
   IN in_is_tan BOOLEAN,
   IN in_request_uid BYTEA,
   IN in_wire_transfer_fees taler_amount,
   IN in_min_amount taler_amount,
   IN in_max_amount taler_amount,
   IN in_type taler_incoming_type,
   IN in_metadata BYTEA,
   IN in_bounce_cause 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,
   OUT out_creditor_admin BOOLEAN,
   OUT out_tan_required BOOLEAN,
   OUT out_request_uid_reuse BOOLEAN,
   OUT out_bad_amount BOOLEAN,
   -- Success return
   OUT out_credit_bank_account_id INT8,
   OUT out_debit_bank_account_id INT8,
   OUT out_credit_row_id INT8,
   OUT out_debit_row_id INT8,
   OUT out_creditor_is_exchange BOOLEAN,
   OUT out_debtor_is_exchange BOOLEAN,
   OUT out_idempotent BOOLEAN
 )
 LANGUAGE plpgsql AS $$
 DECLARE
 local_reserve_pub_reuse BOOLEAN;
 local_mapping_reuse BOOLEAN;
 local_unknown_mapping BOOLEAN;
 BEGIN
 -- Find credit bank account id and check it's not admin
 SELECT bank_account_id, is_taler_exchange, username='admin'
   INTO out_credit_bank_account_id, out_creditor_is_exchange, out_creditor_admin
   FROM bank_accounts
     JOIN customers ON customer_id=owning_customer_id
   WHERE internal_payto = in_credit_account_payto AND deleted_at IS NULL;
 IF NOT FOUND OR out_creditor_admin THEN
   out_creditor_not_found=NOT FOUND;
   RETURN;
 END IF;
 -- Find debit bank account ID and check it's a different account and if 2FA is required
 SELECT bank_account_id, is_taler_exchange, out_credit_bank_account_id=bank_account_id, NOT in_is_tan AND cardinality(tan_channels) > 0
   INTO out_debit_bank_account_id, out_debtor_is_exchange, out_same_account, out_tan_required
   FROM bank_accounts
     JOIN customers ON customer_id=owning_customer_id
   WHERE username = in_debit_account_username AND deleted_at IS NULL;
 IF NOT FOUND OR out_same_account THEN
   out_debtor_not_found=NOT FOUND;
   RETURN;
 END IF;
 -- Check for idempotence and conflict
 IF in_request_uid IS NOT NULL THEN
   SELECT (amount != in_amount
       OR subject != in_subject
       OR bank_account_id != out_debit_bank_account_id), bank_transaction
     INTO out_request_uid_reuse, out_debit_row_id
     FROM bank_transaction_operations
       JOIN bank_account_transactions ON bank_transaction = bank_transaction_id
     WHERE request_uid = in_request_uid;
   IF found OR out_tan_required THEN
     out_idempotent = found AND NOT out_request_uid_reuse;
     RETURN;
   END IF;
 ELSIF out_tan_required THEN
   RETURN;
 END IF;
 
 -- Try to perform an incoming transfer
 IF out_creditor_is_exchange AND NOT out_debtor_is_exchange AND in_bounce_cause IS NULL THEN
   -- Perform an incoming transfer
   SELECT
     transfer.out_balance_insufficient,
     transfer.out_bad_amount,
     transfer.out_credit_row_id,
     transfer.out_debit_row_id,
     out_reserve_pub_reuse,
     out_mapping_reuse,
     out_unknown_mapping
     INTO
       out_balance_insufficient,
       out_bad_amount,
       out_credit_row_id,
       out_debit_row_id,
       local_reserve_pub_reuse,
       local_mapping_reuse,
       local_unknown_mapping
     FROM make_incoming(
       out_credit_bank_account_id,
       out_debit_bank_account_id,
       in_subject,
       in_amount,
       in_timestamp,
       in_type,
       in_metadata,
       in_wire_transfer_fees,
       in_min_amount,
       in_max_amount
     ) as transfer;
   IF out_balance_insufficient OR out_bad_amount THEN
     RETURN;
   END IF;
   IF local_reserve_pub_reuse THEN
     in_bounce_cause = 'reserve public key reuse';
   ELSIF local_mapping_reuse THEN
     in_bounce_cause = 'mapping public key reuse';
   ELSIF local_unknown_mapping THEN
     in_bounce_cause = 'unknown mapping public key';
   END IF;
 END IF;
 
 IF out_credit_row_id IS NULL THEN
   -- Perform common bank transfer
   SELECT
     transfer.out_balance_insufficient,
     transfer.out_bad_amount,
     transfer.out_credit_row_id,
     transfer.out_debit_row_id
     INTO
       out_balance_insufficient,
       out_bad_amount,
       out_credit_row_id,
       out_debit_row_id
     FROM bank_wire_transfer(
       out_credit_bank_account_id,
       out_debit_bank_account_id,
       in_subject,
       in_amount,
       in_timestamp,
       in_wire_transfer_fees,
       in_min_amount,
       in_max_amount
     ) as transfer;
   IF out_balance_insufficient OR out_bad_amount THEN
     RETURN;
   END IF;
 END IF;
 
 -- Bounce if necessary
 IF out_creditor_is_exchange AND in_bounce_cause IS NOT NULL THEN
   PERFORM bounce(out_debit_bank_account_id, out_credit_row_id, in_bounce_cause, in_timestamp);
 END IF;
 
 -- Store operation
 IF in_request_uid IS NOT NULL THEN
   INSERT INTO bank_transaction_operations (request_uid, bank_transaction)
     VALUES (in_request_uid, out_debit_row_id);
 END IF;
 END $$;
 COMMENT ON FUNCTION bank_transaction IS 'Create a bank transaction';
 
 CREATE FUNCTION create_taler_withdrawal(
   IN in_account_username TEXT,
   IN in_withdrawal_uuid UUID,
   IN in_amount taler_amount,
   IN in_suggested_amount taler_amount,
   IN in_no_amount_to_wallet BOOLEAN,
   IN in_timestamp INT8,
   IN in_wire_transfer_fees taler_amount,
   IN in_min_amount taler_amount,
   IN in_max_amount taler_amount,
    -- Error status
   OUT out_account_not_found BOOLEAN,
   OUT out_account_is_exchange BOOLEAN,
   OUT out_balance_insufficient BOOLEAN,
   OUT out_bad_amount BOOLEAN
 )
 LANGUAGE plpgsql AS $$
 DECLARE
 account_id INT8;
 amount_with_fee taler_amount;
 BEGIN
 IF in_account_username IS NOT NULL THEN
   -- Check account exists
   SELECT bank_account_id, is_taler_exchange
     INTO account_id, out_account_is_exchange
     FROM bank_accounts
     JOIN customers ON bank_accounts.owning_customer_id = customers.customer_id
     WHERE username=in_account_username AND deleted_at IS NULL;
   out_account_not_found=NOT FOUND;
   IF out_account_not_found OR out_account_is_exchange THEN
     RETURN;
   END IF;
 
   -- Check enough funds
   IF in_amount IS NOT NULL OR in_suggested_amount IS NOT NULL THEN
     SELECT test.out_balance_insufficient, test.out_bad_amount FROM account_balance_is_sufficient(
       account_id,
       COALESCE(in_amount, in_suggested_amount),
       in_wire_transfer_fees,
       in_min_amount,
       in_max_amount
     ) AS test INTO out_balance_insufficient, out_bad_amount;
     IF out_balance_insufficient OR out_bad_amount THEN
       RETURN;
     END IF;
   END IF;
 END IF;
 
 -- Create withdrawal operation
 INSERT INTO taler_withdrawal_operations (
   withdrawal_uuid,
   wallet_bank_account,
   amount,
   suggested_amount,
   no_amount_to_wallet,
   type,
   creation_date
 ) VALUES (
   in_withdrawal_uuid,
   account_id,
   in_amount,
   in_suggested_amount,
   in_no_amount_to_wallet,
   'reserve',
   in_timestamp
 );
 END $$;
 COMMENT ON FUNCTION create_taler_withdrawal IS 'Create a new withdrawal operation';
 
 CREATE FUNCTION select_taler_withdrawal(
   IN in_withdrawal_uuid uuid,
   IN in_reserve_pub BYTEA,
   IN in_subject TEXT,
   IN in_selected_exchange_payto TEXT,
   IN in_amount taler_amount,
   IN in_wire_transfer_fees taler_amount,
   IN in_min_amount taler_amount,
   IN in_max_amount taler_amount,
   -- Error status
   OUT out_no_op BOOLEAN,
   OUT out_already_selected BOOLEAN,
   OUT out_reserve_pub_reuse BOOLEAN,
   OUT out_account_not_found BOOLEAN,
   OUT out_account_is_not_exchange BOOLEAN,
   OUT out_amount_differs BOOLEAN,
   OUT out_balance_insufficient BOOLEAN,
   OUT out_bad_amount BOOLEAN,
   OUT out_aborted BOOLEAN,
   -- Success return
   OUT out_status TEXT
 )
 LANGUAGE plpgsql AS $$
 DECLARE
 selected BOOLEAN;
 account_id INT8;
 exchange_account_id INT8;
 amount_with_fee taler_amount;
 BEGIN
 -- Check exchange account
 SELECT bank_account_id, NOT is_taler_exchange
   INTO exchange_account_id, out_account_is_not_exchange
   FROM bank_accounts
   WHERE internal_payto=in_selected_exchange_payto;
 out_account_not_found=NOT FOUND;
 IF out_account_not_found OR out_account_is_not_exchange THEN
   RETURN;
 END IF;
 
 -- Check for conflict and idempotence
 SELECT
   selection_done,
   aborted,
   CASE
     WHEN confirmation_done THEN 'confirmed'
     ELSE 'selected'
   END,
   selection_done
     AND (exchange_bank_account != exchange_account_id OR reserve_pub != in_reserve_pub OR amount != in_amount),
   amount != in_amount,
   wallet_bank_account
   INTO selected, out_aborted, out_status, out_already_selected, out_amount_differs, account_id
   FROM taler_withdrawal_operations
   WHERE withdrawal_uuid=in_withdrawal_uuid;
 out_no_op = NOT FOUND;
 IF out_no_op OR out_aborted OR out_already_selected OR out_amount_differs OR selected THEN
   RETURN;
 END IF;
 
 -- Check reserve_pub reuse
 out_reserve_pub_reuse=EXISTS(SELECT FROM taler_exchange_incoming WHERE metadata = in_reserve_pub AND type = 'reserve') OR
   EXISTS(SELECT FROM taler_withdrawal_operations WHERE reserve_pub = in_reserve_pub AND type = 'reserve');
 IF out_reserve_pub_reuse THEN
   RETURN;
 END IF;
 
 IF in_amount IS NOT NULL THEN
   SELECT test.out_balance_insufficient, test.out_bad_amount FROM account_balance_is_sufficient(
     account_id,
     in_amount,
     in_wire_transfer_fees,
     in_min_amount,
     in_max_amount
   ) AS test INTO out_balance_insufficient, out_bad_amount;
   IF out_balance_insufficient OR out_bad_amount THEN
     RETURN;
   END IF;
 END IF;
 
 -- Update withdrawal operation
 UPDATE taler_withdrawal_operations
   SET exchange_bank_account=exchange_account_id,
       reserve_pub=in_reserve_pub,
       subject=in_subject,
       selection_done=true,
       amount=COALESCE(amount, in_amount)
   WHERE withdrawal_uuid=in_withdrawal_uuid;
 
 -- Notify status change
 PERFORM pg_notify('bank_withdrawal_status', in_withdrawal_uuid::text || ' selected');
 END $$;
 COMMENT ON FUNCTION select_taler_withdrawal IS 'Set details of a withdrawal operation';
 
 CREATE FUNCTION abort_taler_withdrawal(
   IN in_withdrawal_uuid uuid,
   OUT out_no_op BOOLEAN,
   OUT out_already_confirmed BOOLEAN
 )
 LANGUAGE plpgsql AS $$
 BEGIN
 UPDATE taler_withdrawal_operations
   SET aborted = NOT confirmation_done
   WHERE withdrawal_uuid=in_withdrawal_uuid
   RETURNING confirmation_done
   INTO out_already_confirmed;
 IF NOT FOUND OR out_already_confirmed THEN
   out_no_op=NOT FOUND;
   RETURN;
 END IF;
 
 -- Notify status change
 PERFORM pg_notify('bank_withdrawal_status', in_withdrawal_uuid::text || ' aborted');
 END $$;
 COMMENT ON FUNCTION abort_taler_withdrawal IS 'Abort a withdrawal operation.';
 
 CREATE FUNCTION confirm_taler_withdrawal(
   IN in_username TEXT,
   IN in_withdrawal_uuid uuid,
   IN in_timestamp INT8,
   IN in_is_tan BOOLEAN,
   IN in_wire_transfer_fees taler_amount,
   IN in_min_amount taler_amount,
   IN in_max_amount taler_amount,
   IN in_amount taler_amount,
   OUT out_no_op BOOLEAN,
   OUT out_balance_insufficient BOOLEAN,
   OUT out_reserve_pub_reuse BOOLEAN,
   OUT out_bad_amount BOOLEAN,
   OUT out_creditor_not_found BOOLEAN,
   OUT out_not_selected BOOLEAN,
   OUT out_missing_amount BOOLEAN,
   OUT out_amount_differs BOOLEAN,
   OUT out_aborted BOOLEAN,
   OUT out_tan_required BOOLEAN
 )
 LANGUAGE plpgsql AS $$
 DECLARE
   already_confirmed BOOLEAN;
   subject_local TEXT;
   reserve_pub_local BYTEA;
   wallet_bank_account_local INT8;
   amount_local taler_amount;
   exchange_bank_account_id INT8;
   local_type taler_incoming_type;
 BEGIN
 -- Load account info
 SELECT bank_account_id, NOT in_is_tan AND cardinality(tan_channels) > 0
 INTO wallet_bank_account_local, out_tan_required
 FROM bank_accounts
 JOIN customers ON owning_customer_id=customer_id
 WHERE username=in_username AND deleted_at IS NULL;
 
 -- Check op exists and conflict
 SELECT
   confirmation_done,
   aborted, NOT selection_done,
   reserve_pub, subject, type,
   exchange_bank_account,
   (amount).val, (amount).frac,
   amount IS NULL AND in_amount IS NULL,
   amount != in_amount
   INTO
     already_confirmed,
     out_aborted, out_not_selected,
     reserve_pub_local, subject_local, local_type,
     exchange_bank_account_id,
     amount_local.val, amount_local.frac,
     out_missing_amount,
     out_amount_differs
   FROM taler_withdrawal_operations
   WHERE withdrawal_uuid=in_withdrawal_uuid
   -- Prepared-transfer withdrawals are intentionally unbound until the
   -- first confirmation; ordinary withdrawals are bound at creation.
   AND (wallet_bank_account IS NULL OR wallet_bank_account=wallet_bank_account_local);
 out_no_op=NOT FOUND;
 IF out_no_op OR already_confirmed OR out_aborted OR out_not_selected OR out_missing_amount OR out_amount_differs OR out_tan_required THEN
   RETURN;
 ELSIF in_amount IS NOT NULL THEN
   amount_local = in_amount;
 END IF;
 
 SELECT -- not checking for accounts existence, as it was done above.
   transfer.out_balance_insufficient,
   transfer.out_bad_amount,
   transfer.out_reserve_pub_reuse
   INTO out_balance_insufficient, out_bad_amount, out_reserve_pub_reuse
 FROM make_incoming(
   exchange_bank_account_id,
   wallet_bank_account_local,
   subject_local,
   amount_local,
   in_timestamp,
   local_type,
   reserve_pub_local,
   in_wire_transfer_fees,
   in_min_amount,
   in_max_amount
 ) as transfer;
 IF out_balance_insufficient OR out_reserve_pub_reuse OR out_bad_amount THEN
   RETURN;
 END IF;
 
 -- Confirm operation and update amount
 UPDATE taler_withdrawal_operations
   SET amount=amount_local,
       confirmation_done=true,
       wallet_bank_account=COALESCE(wallet_bank_account, wallet_bank_account_local)
   WHERE withdrawal_uuid=in_withdrawal_uuid;
 
 -- Notify status change
 PERFORM pg_notify('bank_withdrawal_status', in_withdrawal_uuid::text || ' confirmed');
 END $$;
 COMMENT ON FUNCTION confirm_taler_withdrawal
   IS 'Set a withdrawal operation as confirmed and wire the funds to the exchange.';
 
 CREATE FUNCTION cashin(
   IN in_timestamp INT8,
   IN in_reserve_pub BYTEA,
   IN in_amount taler_amount,
   IN in_subject TEXT,
   -- Error status
   OUT out_no_account BOOLEAN,
   OUT out_too_small BOOLEAN,
   OUT out_balance_insufficient BOOLEAN
 )
 LANGUAGE plpgsql AS $$
 DECLARE
   converted_amount taler_amount;
   admin_account_id INT8;
   exchange_account_id INT8;
   exchange_conversion_rate_class_id INT8;
   tx_row_id INT8;
 BEGIN
 -- TODO check reserve_pub reuse ?
 
 -- Recover exchange account info
 SELECT bank_account_id, conversion_rate_class_id
   INTO exchange_account_id, exchange_conversion_rate_class_id
   FROM bank_accounts
     JOIN customers
       ON customer_id=owning_customer_id
   WHERE username = 'exchange';
 IF NOT FOUND THEN
   out_no_account = true;
   RETURN;
 END IF;
 
 -- Retrieve admin account id
 SELECT bank_account_id
   INTO admin_account_id
   FROM bank_accounts
     JOIN customers
       ON customer_id=owning_customer_id
   WHERE username = 'admin';
 
 -- Perform conversion
 SELECT (converted).val, (converted).frac, too_small
   INTO converted_amount.val, converted_amount.frac, out_too_small
   FROM conversion_to(in_amount, 'cashin'::text, exchange_conversion_rate_class_id);
 IF out_too_small THEN
   RETURN;
 END IF;
 
 -- Perform incoming transaction
 SELECT
   transfer.out_balance_insufficient,
   transfer.out_credit_row_id
   INTO
     out_balance_insufficient,
     tx_row_id
   FROM make_incoming(
     exchange_account_id,
     admin_account_id,
     in_subject,
     converted_amount,
     in_timestamp,
     'reserve'::taler_incoming_type,
     in_reserve_pub,
     NULL,
     NULL,
     NULL
   ) as transfer;
 IF out_balance_insufficient THEN
   RETURN;
 END IF;
 
 -- update stats
 CALL stats_register_payment('cashin', NULL, converted_amount, in_amount);
 
 END $$;
 COMMENT ON FUNCTION cashin IS 'Perform a cashin operation';
 
 
 CREATE FUNCTION cashout_create(
   IN in_username TEXT,
   IN in_request_uid BYTEA,
   IN in_amount_debit taler_amount,
   IN in_amount_credit taler_amount,
   IN in_subject TEXT,
   IN in_timestamp INT8,
   IN in_is_tan BOOLEAN,
   -- Error status
   OUT out_bad_conversion BOOLEAN,
   OUT out_account_not_found BOOLEAN,
   OUT out_account_is_exchange BOOLEAN,
   OUT out_balance_insufficient BOOLEAN,
   OUT out_request_uid_reuse BOOLEAN,
   OUT out_no_cashout_payto BOOLEAN,
   OUT out_tan_required BOOLEAN,
   OUT out_under_min BOOLEAN,
   -- Success return
   OUT out_cashout_id INT8
 )
 LANGUAGE plpgsql AS $$
 DECLARE
 account_id INT8;
 account_conversion_rate_class_id INT8;
 account_cashout_payto TEXT;
 admin_account_id INT8;
 tx_id INT8;
 BEGIN
 
 -- Check account exists, has all info and if 2FA is required
 SELECT
     bank_account_id, is_taler_exchange, conversion_rate_class_id,
     -- Remove potential residual query string an add the receiver_name
     split_part(cashout_payto, '?', 1) || '?receiver-name=' || url_encode(name),
     NOT in_is_tan AND cardinality(tan_channels) > 0
   INTO
     account_id, out_account_is_exchange, account_conversion_rate_class_id,
     account_cashout_payto, out_tan_required
   FROM bank_accounts
   JOIN customers ON owning_customer_id=customer_id
   WHERE username=in_username;
 IF NOT FOUND THEN
   out_account_not_found=TRUE;
   RETURN;
 ELSIF account_cashout_payto IS NULL THEN
   out_no_cashout_payto=TRUE;
   RETURN;
 ELSIF out_account_is_exchange THEN
   RETURN;
 END IF;
 
 -- check conversion
 SELECT under_min, too_small OR in_amount_credit!=converted
   INTO out_under_min, out_bad_conversion
   FROM conversion_to(in_amount_debit, 'cashout'::text, account_conversion_rate_class_id);
 IF out_bad_conversion THEN
   RETURN;
 END IF;
 
 -- Retrieve admin account id
 SELECT bank_account_id
   INTO admin_account_id
   FROM bank_accounts
     JOIN customers
       ON customer_id=owning_customer_id
   WHERE username = 'admin';
 
 -- Check for idempotence and conflict
 SELECT (amount_debit != in_amount_debit
           OR amount_credit != in_amount_credit
           OR subject != in_subject
           OR bank_account != account_id)
         , cashout_id
   INTO out_request_uid_reuse, out_cashout_id
   FROM cashout_operations
   WHERE request_uid = in_request_uid;
 IF found OR out_request_uid_reuse OR out_tan_required THEN
   RETURN;
 END IF;
 
 -- Perform bank wire transfer
 SELECT transfer.out_balance_insufficient, out_debit_row_id
 INTO out_balance_insufficient, tx_id
 FROM bank_wire_transfer(
   admin_account_id,
   account_id,
   in_subject,
   in_amount_debit,
   in_timestamp,
   NULL,
   NULL,
   NULL
 ) as transfer;
 IF out_balance_insufficient THEN
   RETURN;
 END IF;
 
 -- Create cashout operation
 INSERT INTO cashout_operations (
   request_uid
   ,amount_debit
   ,amount_credit
   ,creation_time
   ,bank_account
   ,subject
   ,local_transaction
 ) VALUES (
   in_request_uid
   ,in_amount_debit
   ,in_amount_credit
   ,in_timestamp
   ,account_id
   ,in_subject
   ,tx_id
 ) RETURNING cashout_id INTO out_cashout_id;
 
 -- Initiate libeufin-nexus transaction
 INSERT INTO libeufin_nexus.initiated_outgoing_transactions (
   amount
   ,subject
   ,credit_payto
   ,initiation_time
   ,end_to_end_id
 ) VALUES (
   ((in_amount_credit).val, (in_amount_credit).frac)::libeufin_nexus.taler_amount
   ,in_subject
   ,account_cashout_payto
   ,in_timestamp
   ,libeufin_nexus.ebics_id_gen()
 );
 
 -- update stats
 CALL stats_register_payment('cashout', NULL, in_amount_debit, in_amount_credit);
 END $$;
 
 CREATE FUNCTION tan_challenge_mark_sent (
   IN in_uuid UUID,
   IN in_timestamp INT8,
   IN in_retransmission_period INT8
 ) RETURNS void
 LANGUAGE sql AS $$
   UPDATE tan_challenges SET
     retransmission_date = in_timestamp + in_retransmission_period
   WHERE uuid = in_uuid;
 $$;
 COMMENT ON FUNCTION tan_challenge_mark_sent IS 'Register a challenge as successfully sent';
 
 CREATE FUNCTION tan_challenge_try (
   IN in_uuid UUID,
   IN in_code TEXT,
   IN in_timestamp INT8,
   -- Error status
   OUT out_ok BOOLEAN,
   OUT out_no_op BOOLEAN,
   OUT out_no_retry BOOLEAN,
   OUT out_expired BOOLEAN,
   -- Success return
   OUT out_op op_enum,
   OUT out_channel tan_enum,
   OUT out_info TEXT
 )
 LANGUAGE plpgsql as $$
 DECLARE
 account_id INT8;
 token_creation BOOLEAN;
 BEGIN
 
 -- Try to solve challenge
 UPDATE tan_challenges SET
   confirmation_date = CASE
     WHEN (retry_counter > 0 AND in_timestamp < expiration_date AND code = in_code) THEN in_timestamp
     ELSE confirmation_date
   END,
   retry_counter = retry_counter - 1
 WHERE uuid = in_uuid
 RETURNING
   confirmation_date IS NOT NULL,
   retry_counter <= 0 AND confirmation_date IS NULL,
   in_timestamp >= expiration_date AND confirmation_date IS NULL,
   op = 'create_token',
   customer
 INTO out_ok, out_no_retry, out_expired, token_creation, account_id;
 out_no_op = NOT FOUND;
 
 IF NOT out_ok AND token_creation THEN
   UPDATE customers SET token_creation_counter=token_creation_counter+1 WHERE customer_id=account_id;
 END IF;
 
 IF out_no_op OR NOT out_ok OR out_no_retry OR out_expired THEN
   RETURN;
 END IF;
 
 -- Recover body and op from challenge
 SELECT op, tan_channel, tan_info
   INTO out_op, out_channel, out_info
   FROM tan_challenges WHERE uuid = in_uuid;
 END $$;
 COMMENT ON FUNCTION tan_challenge_try IS 'Try to confirm a challenge, return true if the challenge have been confirmed';
 
 CREATE FUNCTION stats_get_frame(
   IN date TIMESTAMP,
   IN in_timeframe stat_timeframe_enum
 )
 RETURNS TABLE (
   cashin_count INT8,
   cashin_regional_volume taler_amount,
   cashin_fiat_volume taler_amount,
   cashout_count INT8,
   cashout_regional_volume taler_amount,
   cashout_fiat_volume taler_amount,
   taler_in_count INT8,
   taler_in_volume taler_amount,
   taler_out_count INT8,
   taler_out_volume taler_amount
 )
 LANGUAGE sql AS $$
   SELECT
     cashin_count
     ,cashin_regional_volume
     ,cashin_fiat_volume
     ,cashout_count
     ,cashout_regional_volume
     ,cashout_fiat_volume
     ,taler_in_count
     ,taler_in_volume
     ,taler_out_count
     ,taler_out_volume
   FROM bank_stats
   WHERE timeframe = in_timeframe
     AND start_time = date_trunc(in_timeframe::text, date)
 $$;
 
 CREATE PROCEDURE stats_register_payment(
   IN name TEXT,
   IN now TIMESTAMP,
   IN regional_amount taler_amount,
   IN fiat_amount taler_amount
 )
 LANGUAGE plpgsql AS $$
 BEGIN
   IF now IS NULL THEN
     now = timezone('utc', now())::TIMESTAMP;
   END IF;
   IF name = 'taler_in' THEN
     INSERT INTO bank_stats AS s (
       timeframe,
       start_time,
       taler_in_count,
       taler_in_volume
     ) SELECT
       frame,
       date_trunc(frame::text, now),
       1,
       regional_amount
     FROM unnest(enum_range(null::stat_timeframe_enum)) AS frame
     ON CONFLICT (timeframe, start_time) DO UPDATE
     SET taler_in_count=s.taler_in_count+1,
         taler_in_volume=(SELECT amount_add(s.taler_in_volume, regional_amount));
   ELSIF name = 'taler_out' THEN
     INSERT INTO bank_stats AS s (
       timeframe,
       start_time,
       taler_out_count,
       taler_out_volume
     ) SELECT
       frame,
       date_trunc(frame::text, now),
       1,
       regional_amount
     FROM unnest(enum_range(null::stat_timeframe_enum)) AS frame
     ON CONFLICT (timeframe, start_time) DO UPDATE
     SET taler_out_count=s.taler_out_count+1,
         taler_out_volume=(SELECT amount_add(s.taler_out_volume, regional_amount));
   ELSIF name = 'cashin' THEN
     INSERT INTO bank_stats AS s (
       timeframe,
       start_time,
       cashin_count,
       cashin_regional_volume,
       cashin_fiat_volume
     ) SELECT
       frame,
       date_trunc(frame::text, now),
       1,
       regional_amount,
       fiat_amount
     FROM unnest(enum_range(null::stat_timeframe_enum)) AS frame
     ON CONFLICT (timeframe, start_time) DO UPDATE
     SET cashin_count=s.cashin_count+1,
         cashin_regional_volume=(SELECT amount_add(s.cashin_regional_volume, regional_amount)),
         cashin_fiat_volume=(SELECT amount_add(s.cashin_fiat_volume, fiat_amount));
   ELSIF name = 'cashout' THEN
     INSERT INTO bank_stats AS s (
       timeframe,
       start_time,
       cashout_count,
       cashout_regional_volume,
       cashout_fiat_volume
     ) SELECT
       frame,
       date_trunc(frame::text, now),
       1,
       regional_amount,
       fiat_amount
     FROM unnest(enum_range(null::stat_timeframe_enum)) AS frame
     ON CONFLICT (timeframe, start_time) DO UPDATE
     SET cashout_count=s.cashout_count+1,
         cashout_regional_volume=(SELECT amount_add(s.cashout_regional_volume, regional_amount)),
         cashout_fiat_volume=(SELECT amount_add(s.cashout_fiat_volume, fiat_amount));
   ELSE
     RAISE EXCEPTION 'Unknown stat %', name;
   END IF;
 END $$;
 
 CREATE FUNCTION conversion_apply_ratio(
    IN amount taler_amount
   ,IN ratio taler_amount
   ,IN fee taler_amount
   ,IN tiny taler_amount       -- Result is rounded to this amount
   ,IN rounding rounding_mode  -- With this rounding mode
   ,OUT result taler_amount
   ,OUT out_too_small BOOLEAN
 )
 LANGUAGE plpgsql IMMUTABLE AS $$
 DECLARE
   amount_numeric NUMERIC(33, 8); -- 16 digit for val, 8 for frac and 1 for rounding error
   tiny_numeric NUMERIC(24);
 BEGIN
   -- Handle no config case
   IF ratio = (0, 0)::taler_amount THEN
     out_too_small=TRUE;
     RETURN;
   END IF;
 
   -- Perform multiplication using big numbers
   amount_numeric = (amount.val::numeric(24) * 100000000 + amount.frac::numeric(24)) * (ratio.val::numeric(24, 8) + ratio.frac::numeric(24, 8) / 100000000);
 
   -- Apply fees
   amount_numeric = amount_numeric - (fee.val::numeric(24) * 100000000 + fee.frac::numeric(24));
   IF (sign(amount_numeric) != 1) THEN
     out_too_small = TRUE;
     result = (0, 0);
     RETURN;
   END IF;
 
   -- Round to tiny amounts
   tiny_numeric = (tiny.val::numeric(24) * 100000000 + tiny.frac::numeric(24));
   case rounding
     when 'zero' then amount_numeric = trunc(amount_numeric / tiny_numeric) * tiny_numeric;
     when 'up' then amount_numeric = ceil(amount_numeric / tiny_numeric) * tiny_numeric;
     when 'nearest' then amount_numeric = round(amount_numeric / tiny_numeric) * tiny_numeric;
   end case;
 
   -- Extract product parts
   result = (trunc(amount_numeric / 100000000)::int8, (amount_numeric % 100000000)::int4);
 
   IF (result.val > 1::INT8<<52) THEN
     RAISE EXCEPTION 'amount value overflowed';
   END IF;
 END $$;
 COMMENT ON FUNCTION conversion_apply_ratio
   IS 'Apply a ratio to an amount rounding the result to a tiny amount following a rounding mode. It raises an exception when the resulting .val is larger than 2^52';
 
 CREATE FUNCTION conversion_revert_ratio(
    IN amount taler_amount
   ,IN ratio taler_amount
   ,IN fee taler_amount
   ,IN tiny taler_amount       -- Result is rounded to this amount
   ,IN rounding rounding_mode  -- With this rounding mode
   ,IN reverse_tiny taler_amount
   ,OUT result taler_amount
   ,OUT bad_value BOOLEAN
 )
 LANGUAGE plpgsql IMMUTABLE AS $$
 DECLARE
   amount_numeric NUMERIC(33, 8); -- 16 digit for val, 8 for frac and 1 for rounding error
   tiny_numeric NUMERIC(24);
   roundtrip BOOLEAN;
 BEGIN
   -- Handle no config case
   IF ratio = (0, 0)::taler_amount THEN
     bad_value=TRUE;
     RETURN;
   END IF;
 
   -- Apply fees
   amount_numeric = (amount.val::numeric(24) * 100000000 + amount.frac::numeric(24)) + (fee.val::numeric(24) * 100000000 + fee.frac::numeric(24));
 
   -- Perform division using big numbers
   amount_numeric = amount_numeric / (ratio.val::numeric(24, 8) + ratio.frac::numeric(24, 8) / 100000000);
 
   -- Round to input digits
   tiny_numeric = (reverse_tiny.val::numeric(24) * 100000000 + reverse_tiny.frac::numeric(24));
   amount_numeric = trunc(amount_numeric / tiny_numeric) * tiny_numeric;
 
   -- Extract division parts
   result = (trunc(amount_numeric / 100000000)::int8, (amount_numeric % 100000000)::int4);
 
   -- Recover potentially lost tiny amount during rounding
   -- There must be a clever way to compute this but I am a little limited with math
   -- and revert ratio computation is not a hot function so I just use the apply ratio
   -- function to be conservative and correct
   SELECT ok INTO roundtrip FROM amount_left_minus_right((SELECT conversion_apply_ratio.result FROM conversion_apply_ratio(result, ratio, fee, tiny, rounding)), amount);
   IF NOT roundtrip THEN
     amount_numeric = amount_numeric + tiny_numeric;
     result = (trunc(amount_numeric / 100000000)::int8, (amount_numeric % 100000000)::int4);
   END IF;
 
   IF (result.val > 1::INT8<<52) THEN
     RAISE EXCEPTION 'amount value overflowed';
   END IF;
 END $$;
 COMMENT ON FUNCTION conversion_revert_ratio
   IS 'Revert the application of a ratio. This function does not always return the smallest possible amount. It raises an exception when the resulting .val is larger than 2^52';
 
 
 CREATE FUNCTION conversion_to(
   IN amount taler_amount,
   IN direction TEXT,
   IN conversion_rate_class_id INT8,
   OUT converted taler_amount,
   OUT too_small BOOLEAN,
   OUT under_min BOOLEAN
 )
 LANGUAGE plpgsql STABLE AS $$
 DECLARE
   at_ratio taler_amount;
   out_fee taler_amount;
   tiny_amount taler_amount;
   min_amount taler_amount;
   mode rounding_mode;
 BEGIN
   -- Load rate
   IF direction='cashin' THEN
     SELECT
       (cashin_ratio).val, (cashin_ratio).frac,
       (cashin_fee).val, (cashin_fee).frac,
       (cashin_tiny_amount).val, (cashin_tiny_amount).frac,
       (cashin_min_amount).val, (cashin_min_amount).frac,
       cashin_rounding_mode
     INTO
       at_ratio.val, at_ratio.frac,
       out_fee.val, out_fee.frac,
       tiny_amount.val, tiny_amount.frac,
       min_amount.val, min_amount.frac,
       mode
     FROM get_conversion_class_rate(conversion_rate_class_id);
   ELSE
     SELECT
       (cashout_ratio).val, (cashout_ratio).frac,
       (cashout_fee).val, (cashout_fee).frac,
       (cashout_tiny_amount).val, (cashout_tiny_amount).frac,
       (cashout_min_amount).val, (cashout_min_amount).frac,
       cashout_rounding_mode
     INTO
       at_ratio.val, at_ratio.frac,
       out_fee.val, out_fee.frac,
       tiny_amount.val, tiny_amount.frac,
       min_amount.val, min_amount.frac,
       mode
     FROM get_conversion_class_rate(conversion_rate_class_id);
   END IF;
 
   -- Check min amount
   SELECT NOT ok INTO too_small FROM amount_left_minus_right(amount, min_amount);
   IF too_small THEN
     under_min = true;
     converted = (0, 0);
     RETURN;
   END IF;
 
   -- Perform conversion
   SELECT (result).val, (result).frac, out_too_small INTO converted.val, converted.frac, too_small
     FROM conversion_apply_ratio(amount, at_ratio, out_fee, tiny_amount, mode);
 END $$;
 
 CREATE FUNCTION conversion_from(
   IN amount taler_amount,
   IN direction TEXT,
   IN conversion_rate_class_id INT8,
   OUT converted taler_amount,
   OUT too_small BOOLEAN,
   OUT under_min BOOLEAN
 )
 LANGUAGE plpgsql STABLE AS $$
 DECLARE
   ratio taler_amount;
   out_fee taler_amount;
   tiny_amount taler_amount;
   reverse_tiny_amount taler_amount;
   min_amount taler_amount;
   mode rounding_mode;
 BEGIN
   -- Load rate
   IF direction='cashin' THEN
     SELECT
       (cashin_ratio).val, (cashin_ratio).frac,
       (cashin_fee).val, (cashin_fee).frac,
       (cashin_tiny_amount).val, (cashin_tiny_amount).frac,
       (cashout_tiny_amount).val, (cashout_tiny_amount).frac,
       (cashin_min_amount).val, (cashin_min_amount).frac,
       cashin_rounding_mode
     INTO
       ratio.val, ratio.frac,
       out_fee.val, out_fee.frac,
       tiny_amount.val, tiny_amount.frac,
       reverse_tiny_amount.val, reverse_tiny_amount.frac,
       min_amount.val, min_amount.frac,
       mode
     FROM get_conversion_class_rate(conversion_rate_class_id);
   ELSE
     SELECT
       (cashout_ratio).val, (cashout_ratio).frac,
       (cashout_fee).val, (cashout_fee).frac,
       (cashout_tiny_amount).val, (cashout_tiny_amount).frac,
       (cashin_tiny_amount).val, (cashin_tiny_amount).frac,
       (cashout_min_amount).val, (cashout_min_amount).frac,
       cashout_rounding_mode
     INTO
       ratio.val, ratio.frac,
       out_fee.val, out_fee.frac,
       tiny_amount.val, tiny_amount.frac,
       reverse_tiny_amount.val, reverse_tiny_amount.frac,
       min_amount.val, min_amount.frac,
       mode
     FROM get_conversion_class_rate(conversion_rate_class_id);
   END IF;
 
   -- Perform conversion
   SELECT (result).val, (result).frac, bad_value INTO converted.val, converted.frac, too_small
     FROM conversion_revert_ratio(amount, ratio, out_fee, tiny_amount, mode, reverse_tiny_amount);
   IF too_small THEN
     RETURN;
   END IF;
 
   -- Check min amount
   SELECT NOT ok INTO too_small FROM amount_left_minus_right(converted, min_amount);
   IF too_small THEN
     under_min = true;
     converted = (0, 0);
   END IF;
 END $$;
 
 CREATE FUNCTION config_get_conversion_rate()
 RETURNS TABLE (
   cashin_ratio taler_amount,
   cashin_fee taler_amount,
   cashin_tiny_amount taler_amount,
   cashin_min_amount taler_amount,
   cashin_rounding_mode rounding_mode,
   cashout_ratio taler_amount,
   cashout_fee taler_amount,
   cashout_tiny_amount taler_amount,
   cashout_min_amount taler_amount,
   cashout_rounding_mode rounding_mode
 )
 LANGUAGE sql STABLE AS $$
   SELECT
     (value->'cashin'->'ratio'->'val', value->'cashin'->'ratio'->'frac')::taler_amount,
     (value->'cashin'->'fee'->'val', value->'cashin'->'fee'->'frac')::taler_amount,
     (value->'cashin'->'tiny_amount'->'val', value->'cashin'->'tiny_amount'->'frac')::taler_amount,
     (value->'cashin'->'min_amount'->'val', value->'cashin'->'min_amount'->'frac')::taler_amount,
     (value->'cashin'->>'rounding_mode')::rounding_mode,
     (value->'cashout'->'ratio'->'val', value->'cashout'->'ratio'->'frac')::taler_amount,
     (value->'cashout'->'fee'->'val', value->'cashout'->'fee'->'frac')::taler_amount,
     (value->'cashout'->'tiny_amount'->'val', value->'cashout'->'tiny_amount'->'frac')::taler_amount,
     (value->'cashout'->'min_amount'->'val', value->'cashout'->'min_amount'->'frac')::taler_amount,
     (value->'cashout'->>'rounding_mode')::rounding_mode
   FROM config WHERE key='conversion_rate'
   UNION ALL
   SELECT (0, 0)::taler_amount, (0, 0)::taler_amount, (0, 1000000)::taler_amount, (0, 0)::taler_amount, 'zero'::rounding_mode,
          (0, 0)::taler_amount, (0, 0)::taler_amount, (0, 1000000)::taler_amount, (0, 0)::taler_amount, 'zero'::rounding_mode
   LIMIT 1
 $$;
 
 CREATE FUNCTION get_conversion_class_rate(
   IN in_conversion_rate_class_id INT8
 )
 RETURNS TABLE (
   cashin_ratio taler_amount,
   cashin_fee taler_amount,
   cashin_tiny_amount taler_amount,
   cashin_min_amount taler_amount,
   cashin_rounding_mode rounding_mode,
   cashout_ratio taler_amount,
   cashout_fee taler_amount,
   cashout_tiny_amount taler_amount,
   cashout_min_amount taler_amount,
   cashout_rounding_mode rounding_mode
 )
 LANGUAGE sql STABLE AS $$
   SELECT
     COALESCE(class.cashin_ratio, cfg.cashin_ratio),
     COALESCE(class.cashin_fee, cfg.cashin_fee),
     cashin_tiny_amount,
     COALESCE(class.cashin_min_amount, cfg.cashin_min_amount),
     COALESCE(class.cashin_rounding_mode, cfg.cashin_rounding_mode),
     COALESCE(class.cashout_ratio, cfg.cashout_ratio),
     COALESCE(class.cashout_fee, cfg.cashout_fee),
     cashout_tiny_amount,
     COALESCE(class.cashout_min_amount, cfg.cashout_min_amount),
     COALESCE(class.cashout_rounding_mode, cfg.cashout_rounding_mode)
   FROM config_get_conversion_rate() as cfg
     LEFT JOIN conversion_rate_classes as class
       ON (conversion_rate_class_id=in_conversion_rate_class_id)
 $$;
 
 CREATE PROCEDURE config_set_conversion_rate(
   IN cashin_ratio taler_amount,
   IN cashin_fee taler_amount,
   IN cashin_tiny_amount taler_amount,
   IN cashin_min_amount taler_amount,
   IN cashin_rounding_mode rounding_mode,
   IN cashout_ratio taler_amount,
   IN cashout_fee taler_amount,
   IN cashout_tiny_amount taler_amount,
   IN cashout_min_amount taler_amount,
   IN cashout_rounding_mode rounding_mode
 )
 LANGUAGE sql AS $$
   INSERT INTO config (key, value) VALUES ('conversion_rate', jsonb_build_object(
     'cashin', jsonb_build_object(
       'ratio', jsonb_build_object('val', cashin_ratio.val, 'frac', cashin_ratio.frac),
       'fee', jsonb_build_object('val', cashin_fee.val, 'frac', cashin_fee.frac),
       'tiny_amount', jsonb_build_object('val', cashin_tiny_amount.val, 'frac', cashin_tiny_amount.frac),
       'min_amount', jsonb_build_object('val', cashin_min_amount.val, 'frac', cashin_min_amount.frac),
       'rounding_mode', cashin_rounding_mode
     ),
     'cashout', jsonb_build_object(
       'ratio', jsonb_build_object('val', cashout_ratio.val, 'frac', cashout_ratio.frac),
       'fee', jsonb_build_object('val', cashout_fee.val, 'frac', cashout_fee.frac),
       'tiny_amount', jsonb_build_object('val', cashout_tiny_amount.val, 'frac', cashout_tiny_amount.frac),
       'min_amount', jsonb_build_object('val', cashout_min_amount.val, 'frac', cashout_min_amount.frac),
       'rounding_mode', cashout_rounding_mode
     )
   )) ON CONFLICT (key) DO UPDATE SET value = excluded.value
 $$;
 
 CREATE FUNCTION register_prepared_transfers (
   IN in_credit_account TEXT,
   IN in_type taler_incoming_type,
   IN in_account_pub BYTEA,
   IN in_authorization_pub BYTEA,
   IN in_authorization_sig BYTEA,
   IN in_recurrent BOOLEAN,
   IN in_amount taler_amount,
   IN in_timestamp INT8,
   IN in_subject TEXT,
   -- Error status
   OUT out_unknown_creditor BOOLEAN,
   OUT out_not_exchange BOOLEAN,
   OUT out_reserve_pub_reuse BOOLEAN,
   -- Success status
   OUT out_withdrawal_uuid UUID
 )
 LANGUAGE plpgsql AS $$
 DECLARE
   local_withdrawal_id INT8;
   exchange_account_id INT8;
   talerable_tx INT8;
   idempotent BOOLEAN;
 BEGIN
 -- Retrieve exchange account if
 SELECT bank_account_id, NOT is_taler_exchange
   INTO exchange_account_id, out_not_exchange
   FROM bank_accounts
     JOIN customers ON customer_id=owning_customer_id
   WHERE internal_payto = in_credit_account;
 out_unknown_creditor=NOT FOUND;
 if out_unknown_creditor OR out_not_exchange THEN RETURN; END IF;
 
 -- Check idempotency
 SELECT withdrawal_uuid, prepared_transfers.type = in_type
     AND account_pub = in_account_pub
     AND recurrent = in_recurrent
     AND amount = in_amount
 INTO out_withdrawal_uuid, idempotent
 FROM prepared_transfers
 LEFT JOIN taler_withdrawal_operations USING (withdrawal_id)
 WHERE authorization_pub = in_authorization_pub;
 
 -- Check idempotency and delay garbage collection
 IF FOUND AND idempotent THEN
   UPDATE prepared_transfers
   SET registered_at=in_timestamp, authorization_sig=in_authorization_sig
   WHERE authorization_pub=in_authorization_pub;
   RETURN;
 END IF;
 
 -- Check reserve pub reuse
 out_reserve_pub_reuse=in_type = 'reserve' AND (
   EXISTS(SELECT FROM taler_exchange_incoming WHERE metadata = in_account_pub AND type = 'reserve') OR
   EXISTS(SELECT FROM prepared_transfers WHERE account_pub = in_account_pub AND type = 'reserve' AND authorization_pub != in_authorization_pub)
 );
 IF out_reserve_pub_reuse THEN
   RETURN;
 END IF;
 
 -- Create/replace withdrawal
 IF out_withdrawal_uuid IS NOT NULL THEN
   PERFORM abort_taler_withdrawal(out_withdrawal_uuid);
 END IF;
 out_withdrawal_uuid=null;
 
 IF in_recurrent THEN
   -- Finalize one pending right now
   DELETE FROM pending_recurrent_incoming_transactions
   WHERE bank_transaction_id = (
     SELECT bank_transaction_id
     FROM pending_recurrent_incoming_transactions
     JOIN bank_account_transactions USING (bank_transaction_id)
     WHERE authorization_pub = in_authorization_pub
     ORDER BY transaction_date ASC
     LIMIT 1
   )
   RETURNING bank_transaction_id
   INTO talerable_tx;
   IF FOUND THEN
     PERFORM register_incoming(talerable_tx, in_type, in_account_pub, exchange_account_id, in_authorization_pub, in_authorization_sig);
   END IF;
 ELSE
   -- Bounce all pending
   PERFORM bounce(debtor_account_id, bank_transaction_id, 'cancelled mapping', in_timestamp)
   FROM pending_recurrent_incoming_transactions
   WHERE authorization_pub = in_authorization_pub;
 
   -- Create withdrawal
   INSERT INTO taler_withdrawal_operations (
     withdrawal_uuid,
     wallet_bank_account,
     amount,
     suggested_amount,
     no_amount_to_wallet,
     exchange_bank_account,
     type,
     reserve_pub,
     subject,
     selection_done,
     creation_date
   ) VALUES (
     gen_random_uuid(),
     NULL,
     in_amount,
     NULL,
     true,
     exchange_account_id,
     'map',
     in_account_pub,
     in_subject,
     true,
     in_timestamp
   ) RETURNING withdrawal_uuid, withdrawal_id
     INTO out_withdrawal_uuid, local_withdrawal_id;
 END IF;
 
 -- Upsert registration
 INSERT INTO prepared_transfers (
   type,
   account_pub,
   authorization_pub,
   authorization_sig,
   recurrent,
   registered_at,
   bank_transaction_id,
   withdrawal_id
 ) VALUES (
   in_type,
   in_account_pub,
   in_authorization_pub,
   in_authorization_sig,
   in_recurrent,
   in_timestamp,
   talerable_tx,
   local_withdrawal_id
 ) ON CONFLICT (authorization_pub)
 DO UPDATE SET
   type = EXCLUDED.type,
   account_pub = EXCLUDED.account_pub,
   recurrent = EXCLUDED.recurrent,
   registered_at = EXCLUDED.registered_at,
   bank_transaction_id = EXCLUDED.bank_transaction_id,
   withdrawal_id = EXCLUDED.withdrawal_id,
   authorization_sig = EXCLUDED.authorization_sig;
 END $$;
 
 CREATE FUNCTION delete_prepared_transfers (
   IN in_authorization_pub BYTEA,
   IN in_timestamp INT8,
   OUT out_found BOOLEAN
 )
 LANGUAGE plpgsql AS $$
 BEGIN
 -- Bounce all pending
 PERFORM bounce(debtor_account_id, bank_transaction_id, 'cancelled mapping', in_timestamp)
 FROM pending_recurrent_incoming_transactions
 WHERE authorization_pub = in_authorization_pub;
 
 -- Delete registration
 DELETE FROM prepared_transfers
 WHERE authorization_pub = in_authorization_pub;
 out_found = FOUND;
 
 -- TODO abort withdrawal
 END $$;
 
 COMMIT;