work on #8061, needs more testing

13 files changed, 207 insertions, 70 deletions

diff --git a/contrib/wallet-core b/contrib/wallet-core
-Subproject 871ddf9da566c4abe5ebbedbaa637204536dd9d
+Subproject a17a7a51dd50e2f508b078b9aada038fe124ff9
diff --git a/src/backend/taler-merchant-depositcheck.c b/src/backend/taler-merchant-depositcheck.c
index 5fec222f..9576f04b 100644
--- a/src/backend/taler-merchant-depositcheck.c
+++ b/src/backend/taler-merchant-depositcheck.c
@@ -432,6 +432,7 @@ deposit_get_cb (void *cls,
       qs = db_plugin->update_deposit_confirmation_status (
         db_plugin->cls,
         w->deposit_serial,
+        false, /* we are done, wire_pending is now false */
         GNUNET_TIME_absolute_to_timestamp (future_retry),
         w->retry_backoff,
         NULL);
@@ -483,6 +484,8 @@ deposit_get_cb (void *cls,
         qs = db_plugin->update_deposit_confirmation_status (
           db_plugin->cls,
           w->deposit_serial,
+          false, /* we are blocked on KYC, wire_pending is now false */
+          /* FIXME: once the KYC is done, is there logic to get this back to TRUE? */
           GNUNET_TIME_absolute_to_timestamp (future_retry),
           w->retry_backoff,
           "Exchange reported 202 Accepted but no KYC block");
@@ -512,6 +515,7 @@ deposit_get_cb (void *cls,
       qs = db_plugin->update_deposit_confirmation_status (
         db_plugin->cls,
         w->deposit_serial,
+        true, /* this failed, wire_pending remains true */
         GNUNET_TIME_absolute_to_timestamp (future_retry),
         w->retry_backoff,
         msg);
@@ -530,6 +534,7 @@ deposit_get_cb (void *cls,
                                w_tail,
                                w);
   w_count--;
+  GNUNET_free (w->instance_id);
   GNUNET_free (w);
   GNUNET_assert (NULL != keys);
   if ( (w_count < CONCURRENCY_LIMIT / 2) ||
diff --git a/src/backenddb/drop.sql b/src/backenddb/drop.sql
index 1d79544d..07a8ff12 100644
--- a/src/backenddb/drop.sql
+++ b/src/backenddb/drop.sql
@@ -25,6 +25,7 @@ BEGIN;
 
 SELECT _v.unregister_patch('merchant-0001');
 SELECT _v.unregister_patch('merchant-0002');
+SELECT _v.unregister_patch('merchant-0003');
 
 
 DROP SCHEMA merchant CASCADE;
diff --git a/src/backenddb/merchant-0002.sql b/src/backenddb/merchant-0002.sql
index 447b705b..00053cf3 100644
--- a/src/backenddb/merchant-0002.sql
+++ b/src/backenddb/merchant-0002.sql
@@ -52,16 +52,13 @@ CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_and_session
 
 ALTER TABLE merchant_deposit_confirmations
    ADD COLUMN wire_transfer_deadline INT8 DEFAULT (0) NOT NULL,
-   ADD COLUMN retry_backoff INT8 DEFAULT (0) NOT NULL,
    ADD COLUMN wire_pending BOOL DEFAULT (TRUE) NOT NULL,
    ADD COLUMN exchange_failure TEXT DEFAULT NULL;
 
 COMMENT ON COLUMN merchant_deposit_confirmations.wire_transfer_deadline
   IS 'when should the exchange make the wire transfer at the latest';
-COMMENT ON COLUMN merchant_deposit_confirmations.retry_backoff
-  IS 'exponentially increasing value we add to the wire_transfer_deadline on each failure to confirm the wire transfer';
 COMMENT ON COLUMN merchant_deposit_confirmations.wire_pending
-  IS 'true if we are awaiting wire details for a deposit of this purchase and are not blocked on KYC';
+  IS 'true if we are awaiting wire details for a deposit of this purchase (and are not blocked on KYC); false once the exchange says that the wire transfer has happened (does not mean that we confirmed it happened though)';
 COMMENT ON COLUMN merchant_deposit_confirmations.exchange_failure
   IS 'Text describing exchange failures in making timely wire transfers for this deposit confirmation';
 
@@ -74,7 +71,6 @@ CREATE INDEX IF NOT EXISTS merchant_deposits_by_deposit_confirmation_serial
   ON merchant_deposits
   (deposit_confirmation_serial);
 
-
 -------------------------- Tokens -----------------------------
 
 CREATE TABLE IF NOT EXISTS merchant_token_families
diff --git a/src/backenddb/merchant-0003.sql b/src/backenddb/merchant-0003.sql
index 69a3c57b..f524218d 100644
--- a/src/backenddb/merchant-0003.sql
+++ b/src/backenddb/merchant-0003.sql
@@ -1,6 +1,6 @@
 --
 -- This file is part of TALER
--- Copyright (C) 2021 Taler Systems SA
+-- 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
@@ -18,9 +18,33 @@
 BEGIN;
 
 -- Check patch versioning is in place.
--- SELECT _v.register_patch('merchant-0003', NULL, NULL);
+SELECT _v.register_patch('merchant-0003', NULL, NULL);
 
 SET search_path TO merchant;
 
+ALTER TABLE merchant_deposit_to_transfer
+  ADD COLUMN wtid BYTEA CHECK (LENGTH(wtid)=32) DEFAULT NULL;
+
+UPDATE merchant_deposit_to_transfer dst
+  SET wtid=src.wtid
+  FROM merchant_transfers src
+  WHERE (src.credit_serial = dst.credit_serial);
+
+ALTER TABLE merchant_deposit_to_transfer
+  DROP COLUMN credit_serial,
+  ALTER COLUMN wtid SET NOT NULL,
+  ADD UNIQUE (deposit_serial,wtid);
+
+COMMENT ON COLUMN merchant_deposit_to_transfer.wtid
+  IS 'wire transfer identifier of the transfer the exchange claims to have done';
+
+
+ALTER TABLE merchant_deposit_confirmations
+  ADD COLUMN retry_backoff INT8 DEFAULT (0) NOT NULL;
+
+COMMENT ON COLUMN merchant_deposit_confirmations.retry_backoff
+  IS 'exponentially increasing value we add to the wire_transfer_deadline on each failure to confirm the wire transfer';
+
+
 -- Complete transaction
 COMMIT;
diff --git a/src/backenddb/pg_insert_deposit_to_transfer.c b/src/backenddb/pg_insert_deposit_to_transfer.c
index 7e02070f..7b6e6a79 100644
--- a/src/backenddb/pg_insert_deposit_to_transfer.c
+++ b/src/backenddb/pg_insert_deposit_to_transfer.c
@@ -45,19 +45,24 @@ TMH_PG_insert_deposit_to_transfer (
   };
   bool wpc;
   bool conflict;
+  bool no_exchange_pub;
   struct GNUNET_PQ_ResultSpec rs[] = {
     GNUNET_PQ_result_spec_bool ("out_wire_pending_cleared",
                                 &wpc),
     GNUNET_PQ_result_spec_bool ("out_conflict",
                                 &conflict),
+    GNUNET_PQ_result_spec_bool ("out_no_exchange_pub",
+                                &no_exchange_pub),
     GNUNET_PQ_result_spec_end
   };
   enum GNUNET_DB_QueryStatus qs;
 
   PREPARE (pg,
            "insert_deposit_to_transfer",
-           "SELECT out_wire_pending_cleared"
+           "SELECT"
+           "  out_wire_pending_cleared"
            " ,out_conflict"
+           " ,out_no_exchange_pub"
            " FROM merchant_insert_deposit_to_transfer"
            " ($1,$2,$3,$4,$5,$6);");
   qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
@@ -66,6 +71,9 @@ TMH_PG_insert_deposit_to_transfer (
                                                  rs);
   if (qs <= 0)
     return qs;
+  if (no_exchange_pub)
+    GNUNET_log (GNUNET_ERROR_TYPE_WARNING,
+                "Exchange public key unknown (bug!?)\n");
   if (wpc)
     GNUNET_log (GNUNET_ERROR_TYPE_INFO,
                 "Wire pending flag cleared (good!)\n");
diff --git a/src/backenddb/pg_insert_deposit_to_transfer.sql b/src/backenddb/pg_insert_deposit_to_transfer.sql
index bd3a5943..b2e587f1 100644
--- a/src/backenddb/pg_insert_deposit_to_transfer.sql
+++ b/src/backenddb/pg_insert_deposit_to_transfer.sql
@@ -23,56 +23,138 @@ CREATE OR REPLACE FUNCTION merchant_insert_deposit_to_transfer (
   IN in_exchange_pub BYTEA,
   IN in_wtid BYTEA,
   OUT out_wire_pending_cleared BOOL,
-  OUT out_conflict BOOL)
+  OUT out_conflict BOOL,
+  OUT out_no_exchange_pub BOOL)
 LANGUAGE plpgsql
 AS $$
 DECLARE
-  decose INT8;
+  my_signkey_serial INT8;
+DECLARE
+  my_confirmed BOOL;
+DECLARE
+  my_decose INT8;
+DECLARE
+  my_order_serial INT8;
 BEGIN
 
-out_wire_pending_cleared=FALSE;
+-- 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
-  ,credit_serial
+  ,wtid
   ,execution_time
   ,signkey_serial
   ,exchange_sig
   )
-  SELECT
-    in_deposit_serial
-   ,in_amount_with_fee
-   ,credit_serial
-   ,in_execution_time
-   ,signkey_serial
-   ,in_exchange_sig
-   FROM merchant_transfers
-     CROSS JOIN merchant_exchange_signing_keys
-     WHERE exchange_pub=in_exchange_pub
-     AND wtid=in_wtid
+  VALUES
+  (in_deposit_serial
+  ,in_amount_with_fee
+  ,in_wtid
+  ,in_execution_time
+  ,my_signkey_serial
+  ,in_exchange_sig
+  )
   ON CONFLICT DO NOTHING;
 
-out_conflict = NOT FOUND;
+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
-  SELECT deposit_confirmation_serial
-    INTO decose
-    FROM merchant_deposits
-   WHERE deposit_serial=in_deposit_serial;
-
-  -- we made a change, check about clearing wire_pending
-  UPDATE merchant_deposit_confirmations
-    SET wire_pending=FALSE
-    WHERE (deposit_confirmation_serial=decose)
-    AND NOT EXISTS (
-      SELECT *
-        FROM merchant_deposits md
-        LEFT JOIN merchant_deposit_to_transfer mdtt
-          USING (deposit_serial)
-        WHERE md.deposit_confirmation_serial=decose
-          AND mdtt.credit_serial IS NULL);
-  out_wire_pending_cleared=FOUND;
+  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 $$;
diff --git a/src/backenddb/pg_insert_transfer_details.c b/src/backenddb/pg_insert_transfer_details.c
index 36cb7fff..aa0ccc53 100644
--- a/src/backenddb/pg_insert_transfer_details.c
+++ b/src/backenddb/pg_insert_transfer_details.c
@@ -76,8 +76,8 @@ TMH_PG_insert_transfer_details (
        retries++)
   {
     if (GNUNET_OK !=
-        TMH_PG_start_read_committed (pg,
-                                     "insert transfer details"))
+        TMH_PG_start (pg,
+                      "insert transfer details"))
     {
       GNUNET_break (0);
       return GNUNET_DB_STATUS_HARD_ERROR;
diff --git a/src/backenddb/pg_insert_transfer_details.sql b/src/backenddb/pg_insert_transfer_details.sql
index a64d0139..1650d157 100644
--- a/src/backenddb/pg_insert_transfer_details.sql
+++ b/src/backenddb/pg_insert_transfer_details.sql
@@ -43,13 +43,9 @@ DECLARE
   my_affected_orders RECORD;
   i INT8;
   curs CURSOR (arg_coin_pub BYTEA) FOR
-    SELECT mcon.order_serial
+    SELECT mcon.deposit_confirmation_serial,
+           mcon.order_serial
       FROM merchant_deposits dep
-      -- Next 2 joins ensure transfers exist in the first place
-      JOIN merchant_deposit_to_transfer
-        USING (deposit_serial)
-      JOIN merchant_transfers mtrans
-        USING (credit_serial)
       JOIN merchant_deposit_confirmations mcon
         USING (deposit_confirmation_serial)
       WHERE dep.coin_pub=arg_coin_pub;
@@ -195,24 +191,37 @@ LOOP
     EXIT WHEN NOT FOUND;
 
     RAISE NOTICE 'checking affected order for completion';
-    UPDATE merchant_contract_terms
-       SET wired=TRUE
-     WHERE order_serial IN
-       (SELECT order_serial
-          FROM merchant_deposit_confirmations dcon
-         WHERE
-           order_serial=my_affected_orders.order_serial
-           AND NOT EXISTS
-           (SELECT 1
-              FROM merchant_deposit_confirmations dcon
-              JOIN merchant_deposits dep
-                USING (deposit_confirmation_serial)
-              JOIN merchant_deposit_to_transfer
-                USING (deposit_serial)
-              JOIN merchant_transfers mtrans
-                USING (credit_serial)
-             WHERE dcon.order_serial=my_affected_orders.order_serial
-               AND NOT mtrans.confirmed));
+
+    -- First, check if deposit confirmation is done.
+    UPDATE merchant_deposit_confirmations
+       SET wire_pending=FALSE
+     WHERE (deposit_confirmation_serial=my_affected_orders.deposit_confirmation_serial)
+       AND NOT EXISTS
+       (SELECT 1
+          FROM merchant_deposits md
+          LEFT JOIN merchant_deposit_to_transfer mdtt
+            USING (deposit_serial)
+          WHERE md.deposit_confirmation_serial=my_affected_orders.deposit_confirmation_serial
+            AND mdtt.wtid IS NULL);
+    -- wtid 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 FOUND
+    THEN
+      -- Also update contract terms, if all (other) associated
+      -- deposit_confirmations are also done.
+
+      UPDATE merchant_contract_terms
+         SET wired=TRUE
+       WHERE (order_serial=my_affected_orders.order_serial)
+         AND NOT EXISTS
+         (SELECT 1
+            FROM merchant_deposit_confirmations mdc
+             WHERE mdc.wire_pending
+               AND mdc.order_serial=my_affected_orders.order_serial);
+    END IF;
+
   END LOOP; -- END curs LOOP
   CLOSE curs;
 END LOOP; -- END FOR loop
diff --git a/src/backenddb/pg_update_deposit_confirmation_status.c b/src/backenddb/pg_update_deposit_confirmation_status.c
index e0def393..d83dc9b0 100644
--- a/src/backenddb/pg_update_deposit_confirmation_status.c
+++ b/src/backenddb/pg_update_deposit_confirmation_status.c
@@ -30,6 +30,7 @@ enum GNUNET_DB_QueryStatus
 TMH_PG_update_deposit_confirmation_status (
   void *cls,
   uint64_t deposit_serial,
+  bool wire_pending,
   struct GNUNET_TIME_Timestamp future_retry,
   struct GNUNET_TIME_Relative retry_backoff,
   const char *emsg)
@@ -42,6 +43,7 @@ TMH_PG_update_deposit_confirmation_status (
     ? GNUNET_PQ_query_param_null ()
     : GNUNET_PQ_query_param_string (emsg),
     GNUNET_PQ_query_param_relative_time (&retry_backoff),
+    GNUNET_PQ_query_param_bool (wire_pending),
     GNUNET_PQ_query_param_end
   };
 
@@ -52,6 +54,7 @@ TMH_PG_update_deposit_confirmation_status (
            " wire_transfer_deadline=$2"
            ",exchange_failure=$3"
            ",retry_backoff=$4"
+           ",wire_pending=$5"
            " WHERE deposit_confirmation_serial="
            "   (SELECT deposit_confirmation_serial"
            "      FROM merchant_deposits"
diff --git a/src/backenddb/pg_update_deposit_confirmation_status.h b/src/backenddb/pg_update_deposit_confirmation_status.h
index b494a3a1..ae995fec 100644
--- a/src/backenddb/pg_update_deposit_confirmation_status.h
+++ b/src/backenddb/pg_update_deposit_confirmation_status.h
@@ -32,6 +32,7 @@
  *
  * @param cls closure
  * @param deposit_serial deposit to update status for
+ * @param wire_pending did the exchange say that the wire is still pending?
  * @param future_retry when should we ask the exchange again
  * @param retry_backoff current value for the retry backoff
  * @param emsg error message to record
@@ -41,6 +42,7 @@ enum GNUNET_DB_QueryStatus
 TMH_PG_update_deposit_confirmation_status (
   void *cls,
   uint64_t deposit_serial,
+  bool wire_pending,
   struct GNUNET_TIME_Timestamp future_retry,
   struct GNUNET_TIME_Relative retry_backoff,
   const char *emsg);
diff --git a/src/backenddb/test_merchantdb.c b/src/backenddb/test_merchantdb.c
index e9a17b34..dbec520b 100644
--- a/src/backenddb/test_merchantdb.c
+++ b/src/backenddb/test_merchantdb.c
@@ -1869,11 +1869,16 @@ test_lookup_payment_status (const char *instance_id,
     paid = false;
     wired = false;
   }
-  if ( (expected_paid != paid) ||
-       (expected_wired != wired) )
+  if (expected_wired != wired)
   {
     GNUNET_log (GNUNET_ERROR_TYPE_ERROR,
-                "Lookup payment status failed\n");
+                "Lookup payment status failed: wired status is wrong\n");
+    return 1;
+  }
+  if (expected_paid != paid)
+  {
+    GNUNET_log (GNUNET_ERROR_TYPE_ERROR,
+                "Lookup payment status failed: paid status is wrong\n");
     return 1;
   }
   return 0;
diff --git a/src/include/taler_merchantdb_plugin.h b/src/include/taler_merchantdb_plugin.h
index 7d0f2080..705c4888 100644
--- a/src/include/taler_merchantdb_plugin.h
+++ b/src/include/taler_merchantdb_plugin.h
@@ -3710,6 +3710,7 @@ struct TALER_MERCHANTDB_Plugin
    *
    * @param cls closure
    * @param deposit_serial deposit to update status for
+   * @param wire_pending should we keep checking for the wire status with the exchange?
    * @param future_retry when should we ask the exchange again
    * @param retry_backoff current value for the retry backoff
    * @param emsg error message to record
@@ -3719,6 +3720,7 @@ struct TALER_MERCHANTDB_Plugin
   (*update_deposit_confirmation_status)(
     void *cls,
     uint64_t deposit_serial,
+    bool wire_pending,
     struct GNUNET_TIME_Timestamp future_retry,
     struct GNUNET_TIME_Relative retry_backoff,
     const char *emsg);