1 files changed, 160 insertions, 0 deletions
diff --git a/src/backenddb/pg_insert_deposit_to_transfer.sql b/src/backenddb/pg_insert_deposit_to_transfer.sql
new file mode 100644
index 00000000..b2e587f1
--- /dev/null
+++ b/src/backenddb/pg_insert_deposit_to_transfer.sql
@@ -0,0 +1,160 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2024 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/>
+--
+
+
+CREATE OR REPLACE FUNCTION merchant_insert_deposit_to_transfer (
+  IN in_deposit_serial INT8,
+  IN in_amount_with_fee taler_amount_currency,
+  IN in_execution_time INT8,
+  IN in_exchange_sig BYTEA,
+  IN in_exchange_pub BYTEA,
+  IN in_wtid BYTEA,
+  OUT out_wire_pending_cleared BOOL,
+  OUT out_conflict BOOL,
+  OUT out_no_exchange_pub BOOL)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  my_signkey_serial INT8;
+DECLARE
+  my_confirmed BOOL;
+DECLARE
+  my_decose INT8;
+DECLARE
+  my_order_serial INT8;
+BEGIN
+
+-- Find exchange sign key
+SELECT signkey_serial
+  INTO my_signkey_serial
+  FROM merchant_exchange_signing_keys
+ WHERE exchange_pub=in_exchange_pub
+   ORDER BY start_date DESC
+   LIMIT 1;
+
+IF NOT FOUND
+THEN
+  out_no_exchange_pub=TRUE;
+  out_conflict=FALSE;
+  out_wire_pending_cleared=FALSE;
+  RETURN;
+END IF;
+out_no_exchange_pub=FALSE;
+
+
+-- Try to insert new wire transfer
+INSERT INTO merchant_deposit_to_transfer
+  (deposit_serial
+  ,coin_contribution_value
+  ,wtid
+  ,execution_time
+  ,signkey_serial
+  ,exchange_sig
+  )
+  VALUES
+  (in_deposit_serial
+  ,in_amount_with_fee
+  ,in_wtid
+  ,in_execution_time
+  ,my_signkey_serial
+  ,in_exchange_sig
+  )
+  ON CONFLICT DO NOTHING;
+
+IF NOT FOUND
+THEN
+  -- Same or conflicting wire transfer existed in the table already
+  -- Note: we don't distinguish here between
+  -- conflict and duplicate. Do we need to?
+  out_conflict=TRUE;
+  out_wire_pending_cleared=FALSE;
+  return;
+END IF;
+out_conflict=FALSE;
+
+
+-- Check if we already imported the (confirmed)
+-- wire transfer *and* if it is mapped to this deposit.
+PERFORM
+  FROM merchant_transfers mt
+  JOIN merchant_transfer_to_coin mtc
+    USING (credit_serial)
+  WHERE mt.wtid=in_wtid
+    AND mt.confirmed
+    AND mtc.deposit_serial=in_deposit_serial;
+
+IF NOT FOUND
+THEN
+  out_wire_pending_cleared=FALSE;
+  RETURN;
+END IF;
+
+
+RAISE NOTICE 'checking affected deposit confirmation for completion';
+
+SELECT deposit_confirmation_serial
+  INTO my_decose
+  FROM merchant_deposits
+ WHERE deposit_serial=in_deposit_serial;
+
+-- we made a change, check about clearing wire_pending
+-- for the entire deposit confirmation
+UPDATE merchant_deposit_confirmations
+  SET wire_pending=FALSE
+  WHERE (deposit_confirmation_serial=decose)
+    AND NOT EXISTS
+    (SELECT 1
+      FROM merchant_deposits md
+      LEFT JOIN merchant_deposit_to_transfer mdtt
+        USING (wtid)
+      WHERE md.deposit_confirmation_serial=my_decose
+        AND mdtt.credit_serial IS NULL);
+-- credit_serial will be NULL due to LEFT JOIN
+-- if we do not have an entry in mdtt for the deposit
+-- and thus some entry in md was not yet wired.
+
+IF NOT FOUND
+THEN
+  out_wire_pending_cleared=FALSE;
+  RETURN;
+END IF;
+out_wire_pending_cleared=TRUE;
+
+
+RAISE NOTICE 'checking affected contracts for completion';
+
+-- Check if all deposit confirmations of the same
+-- contract are now wired.
+SELECT deposit_confirmation_serial
+  INTO my_order_serial
+  FROM merchant_deposit_confirmations
+ WHERE deposit_confirmation_serial=my_decose;
+-- The above MUST succeed by invariants.
+
+-- Check about setting 'wired' for the contract term.
+-- Note: the same contract may be paid from
+-- multiple exchanges, so we need to check if
+-- payments were wired from all of them!
+UPDATE merchant_contract_terms
+  SET wired=TRUE
+  WHERE (order_serial=my_order_serial)
+    AND NOT EXISTS
+    (SELECT 1
+       FROM merchant_deposit_confirmations mdc
+      WHERE mdc.wire_pending
+        AND mdc.order_serial=my_order_serial);
+
+END $$;