properly deploy procedures.sql, add trigger for taler-merchant-depositcheck, rename field to match contract terms

13 files changed, 67 insertions, 27 deletions

diff --git a/src/backend/taler-merchant-httpd_post-orders-ID-pay.c b/src/backend/taler-merchant-httpd_post-orders-ID-pay.c
index d2d0fb6b..75be6ff5 100644
--- a/src/backend/taler-merchant-httpd_post-orders-ID-pay.c
+++ b/src/backend/taler-merchant-httpd_post-orders-ID-pay.c
@@ -95,14 +95,14 @@ enum PayPhase
 
   /**
    * Perform batch deposits with exchange(s).
-   */ 
+   */
   PP_BATCH_DEPOSITS,
 
   /**
    * Return response in payment context.
    */
   PP_RETURN_RESPONSE,
-  
+
   /**
    * Return #MHD_YES to end processing.
    */
@@ -113,7 +113,7 @@ enum PayPhase
    */
   PP_END_NO
 };
-  
+
 
 /**
  * Information kept during a pay request for each coin.
@@ -450,9 +450,9 @@ struct PayContext
 
   /**
    * Payment processing phase we are in.
-   */ 
+   */
   enum PayPhase phase;
-  
+
   /**
    * #GNUNET_NO if the @e connection was not suspended,
    * #GNUNET_YES if the @e connection was suspended,
@@ -984,6 +984,7 @@ batch_deposit_transaction (const struct ExchangeGroup *eg,
     dr->details.ok.deposit_timestamp,
     &pc->h_contract_terms,
     eg->exchange_url,
+    pc->wire_transfer_deadline,
     &total_without_fees,
     &eg->wire_fee,
     &pc->wm->h_wire,
@@ -1668,8 +1669,8 @@ phase_success_response (struct PayContext *pc)
              GNUNET_JSON_pack_allow_null (
                GNUNET_JSON_pack_string ("pos_confirmation",
                                         pos_confirmation)),
-               GNUNET_JSON_pack_data_auto ("sig",
-                                           &sig)));
+             GNUNET_JSON_pack_data_auto ("sig",
+                                         &sig)));
   GNUNET_free (pos_confirmation);
 }
 
@@ -2480,7 +2481,7 @@ phase_contract_paid (struct PayContext *pc)
              pc->connection,
              MHD_HTTP_CONFLICT,
              TALER_MHD_PACK_EC (
-                                TALER_EC_MERCHANT_POST_ORDERS_ID_PAY_ALREADY_PAID),
+               TALER_EC_MERCHANT_POST_ORDERS_ID_PAY_ALREADY_PAID),
              GNUNET_JSON_pack_array_steal ("refunds",
                                            refunds)));
 }
@@ -2637,7 +2638,7 @@ phase_check_contract (struct PayContext *pc)
     return;
   }
 
-  for (unsigned int i=0;i<pc->coins_cnt;i++)
+  for (unsigned int i = 0; i<pc->coins_cnt; i++)
   {
     struct DepositConfirmation *dc = &pc->dc[i];
 
@@ -2854,7 +2855,7 @@ phase_parse_pay (struct PayContext *pc)
                    MHD_HTTP_BAD_REQUEST,
                    TALER_EC_GENERIC_PARAMETER_MALFORMED,
                    "inconsistent: 'age_commitment' vs. 'minimum_age_sig'"
-                                             ));
+                   ));
         return;
       }
 
@@ -3001,7 +3002,7 @@ TMH_post_orders_ID_pay (const struct TMH_RequestHandler *rh,
       GNUNET_log (GNUNET_ERROR_TYPE_INFO,
                   "Processing /pay ends due to shutdown in phase %d\n",
                   (int) pc->phase);
-      return MHD_NO; 
+      return MHD_NO;
     case GNUNET_NO:
       /* continue to next phase */
       break;
diff --git a/src/backenddb/Makefile.am b/src/backenddb/Makefile.am
index bb559ced..149a43ed 100644
--- a/src/backenddb/Makefile.am
+++ b/src/backenddb/Makefile.am
@@ -16,6 +16,7 @@ sqlinputs = \
 
 sql_DATA = \
   versioning.sql \
+  procedures.sql \
   merchant-0001.sql \
   merchant-0002.sql \
   merchant-0003.sql \
diff --git a/src/backenddb/merchant-0002.sql b/src/backenddb/merchant-0002.sql
index e5e91631..76371a42 100644
--- a/src/backenddb/merchant-0002.sql
+++ b/src/backenddb/merchant-0002.sql
@@ -51,11 +51,11 @@ CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_and_session
 
 
 ALTER TABLE merchant_deposit_confirmations
-   ADD COLUMN deposit_deadline INT8 DEFAULT (0) NOT NULL,
+   ADD COLUMN wire_transfer_deadline 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.deposit_deadline
+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.wire_pending
   IS 'true if we are awaiting wire details for a deposit of this purchase and are not blocked on KYC';
@@ -64,7 +64,7 @@ COMMENT ON COLUMN merchant_deposit_confirmations.exchange_failure
 
 CREATE INDEX IF NOT EXISTS merchant_deposit_confirmations_by_pending_wire
   ON merchant_deposit_confirmations
-  (exchange_url,deposit_deadline)
+  (exchange_url,wire_transfer_deadline)
   WHERE wire_pending;
 
 CREATE INDEX IF NOT EXISTS merchant_deposits_by_deposit_confirmation_serial
diff --git a/src/backenddb/pg_insert_deposit_confirmation.c b/src/backenddb/pg_insert_deposit_confirmation.c
index 377ba2d8..6ad0f5fb 100644
--- a/src/backenddb/pg_insert_deposit_confirmation.c
+++ b/src/backenddb/pg_insert_deposit_confirmation.c
@@ -1,6 +1,6 @@
 /*
    This file is part of TALER
-   Copyright (C) 2022, 2023 Taler Systems SA
+   Copyright (C) 2022, 2023, 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
@@ -33,6 +33,7 @@ TMH_PG_insert_deposit_confirmation (
   struct GNUNET_TIME_Timestamp deposit_timestamp,
   const struct TALER_PrivateContractHashP *h_contract_terms,
   const char *exchange_url,
+  struct GNUNET_TIME_Timestamp wire_transfer_deadline,
   const struct TALER_Amount *total_without_fees,
   const struct TALER_Amount *wire_fee,
   const struct TALER_MerchantWireHashP *h_wire,
@@ -40,6 +41,10 @@ TMH_PG_insert_deposit_confirmation (
   const struct TALER_ExchangePublicKeyP *exchange_pub,
   uint64_t *deposit_confirmation_serial_id)
 {
+  struct GNUNET_DB_EventHeaderP es = {
+    .size = htons (sizeof (es)),
+    .type = htons (TALER_DBEVENT_MERCHANT_NEW_WIRE_DEADLINE)
+  };
   struct PostgresClosure *pg = cls;
   struct GNUNET_PQ_QueryParam params[] = {
     GNUNET_PQ_query_param_string (instance_id),
@@ -53,6 +58,7 @@ TMH_PG_insert_deposit_confirmation (
     GNUNET_PQ_query_param_auto_from_type (h_wire), /* 7 */
     GNUNET_PQ_query_param_auto_from_type (exchange_sig),
     GNUNET_PQ_query_param_auto_from_type (exchange_pub),
+    GNUNET_PQ_query_param_timestamp (&wire_transfer_deadline),
     GNUNET_PQ_query_param_end
   };
   struct GNUNET_PQ_ResultSpec rs[] = {
@@ -60,6 +66,7 @@ TMH_PG_insert_deposit_confirmation (
                                   deposit_confirmation_serial_id),
     GNUNET_PQ_result_spec_end
   };
+  enum GNUNET_DB_QueryStatus qs;
 
   /* no preflight check here, run in transaction by caller! */
   GNUNET_log (GNUNET_ERROR_TYPE_DEBUG,
@@ -91,11 +98,12 @@ TMH_PG_insert_deposit_confirmation (
            ",total_without_fee"
            ",wire_fee"
            ",exchange_sig"
+           ",wire_transfer_deadline"
            ",signkey_serial"
            ",account_serial)"
            " SELECT "
            "   order_serial"
-           "  ,$3, $4, $5, $6, $8"
+           "  ,$3, $4, $5, $6, $8, $10"
            "  ,ed.signkey_serial"
            "  ,md.account_serial"
            "  FROM merchant_contract_terms"
@@ -103,8 +111,20 @@ TMH_PG_insert_deposit_confirmation (
            "   FULL OUTER JOIN ed ON TRUE"
            "  WHERE h_contract_terms=$2"
            " RETURNING deposit_confirmation_serial");
-  return GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
-                                                   "insert_deposit_confirmation",
-                                                   params,
-                                                   rs);
+  qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
+                                                 "insert_deposit_confirmation",
+                                                 params,
+                                                 rs);
+  if (GNUNET_DB_STATUS_SUCCESS_ONE_RESULT == qs)
+  {
+    /* inform taler-merchant-depositcheck about new deadline */
+    struct GNUNET_TIME_AbsoluteNBO nbo;
+
+    nbo = GNUNET_TIME_absolute_hton (wire_transfer_deadline.abs_time);
+    GNUNET_PQ_event_notify (pg->conn,
+                            &es,
+                            &nbo,
+                            sizeof (nbo));
+  }
+  return qs;
 }
diff --git a/src/backenddb/pg_insert_deposit_confirmation.h b/src/backenddb/pg_insert_deposit_confirmation.h
index 3d9802e2..243d5a4d 100644
--- a/src/backenddb/pg_insert_deposit_confirmation.h
+++ b/src/backenddb/pg_insert_deposit_confirmation.h
@@ -33,6 +33,7 @@
  * @param deposit_timestamp time when the exchange generated the deposit confirmation
  * @param h_contract_terms proposal data's hashcode
  * @param exchange_url URL of the exchange that issued @a coin_pub
+ * @param wire_transfer_deadline when do we expect the wire transfer from the exchange
  * @param total_without_fees deposited total in the batch without fees
  * @param wire_fee wire fee the exchange charges
  * @param h_wire hash of the wire details of the target account of the merchant
@@ -48,6 +49,7 @@ TMH_PG_insert_deposit_confirmation (
   struct GNUNET_TIME_Timestamp deposit_timestamp,
   const struct TALER_PrivateContractHashP *h_contract_terms,
   const char *exchange_url,
+  struct GNUNET_TIME_Timestamp wire_transfer_deadline,
   const struct TALER_Amount *total_without_fees,
   const struct TALER_Amount *wire_fee,
   const struct TALER_MerchantWireHashP *h_wire,
diff --git a/src/backenddb/pg_insert_deposit_to_transfer.c b/src/backenddb/pg_insert_deposit_to_transfer.c
index 1baf8963..7e02070f 100644
--- a/src/backenddb/pg_insert_deposit_to_transfer.c
+++ b/src/backenddb/pg_insert_deposit_to_transfer.c
@@ -44,24 +44,32 @@ TMH_PG_insert_deposit_to_transfer (
     GNUNET_PQ_query_param_end
   };
   bool wpc;
+  bool conflict;
   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_end
   };
   enum GNUNET_DB_QueryStatus qs;
 
   PREPARE (pg,
            "insert_deposit_to_transfer",
-           "SELECT out_wire_cleared"
+           "SELECT out_wire_pending_cleared"
+           " ,out_conflict"
            " FROM merchant_insert_deposit_to_transfer"
            " ($1,$2,$3,$4,$5,$6);");
   qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
                                                  "insert_deposit_to_transfer",
                                                  params,
                                                  rs);
+  if (qs <= 0)
+    return qs;
   if (wpc)
     GNUNET_log (GNUNET_ERROR_TYPE_INFO,
                 "Wire pending flag cleared (good!)\n");
+  if (conflict)
+    return GNUNET_DB_STATUS_SUCCESS_NO_RESULTS;
   return qs;
 }
diff --git a/src/backenddb/pg_insert_deposit_to_transfer.sql b/src/backenddb/pg_insert_deposit_to_transfer.sql
index ddc291c3..bd3a5943 100644
--- a/src/backenddb/pg_insert_deposit_to_transfer.sql
+++ b/src/backenddb/pg_insert_deposit_to_transfer.sql
@@ -22,7 +22,8 @@ CREATE OR REPLACE FUNCTION merchant_insert_deposit_to_transfer (
   IN in_exchange_sig BYTEA,
   IN in_exchange_pub BYTEA,
   IN in_wtid BYTEA,
-  OUT out_wire_pending_cleared BOOL)
+  OUT out_wire_pending_cleared BOOL,
+  OUT out_conflict BOOL)
 LANGUAGE plpgsql
 AS $$
 DECLARE
@@ -51,6 +52,8 @@ INSERT INTO merchant_deposit_to_transfer
      AND wtid=in_wtid
   ON CONFLICT DO NOTHING;
 
+out_conflict = NOT FOUND;
+
 IF NOT FOUND
 THEN
   SELECT deposit_confirmation_serial
diff --git a/src/backenddb/pg_lookup_pending_deposits.c b/src/backenddb/pg_lookup_pending_deposits.c
index 27509f36..2f4cf1a4 100644
--- a/src/backenddb/pg_lookup_pending_deposits.c
+++ b/src/backenddb/pg_lookup_pending_deposits.c
@@ -177,10 +177,10 @@ TMH_PG_lookup_pending_deposits (
            "  USING (deposit_confirmation_serial)"
            " WHERE mdc.wire_pending"
            "  AND (mdc.exchange_url=$1)"
-           "  AND ($4 OR (mdc.deposit_deadline < $2))"
+           "  AND ($4 OR (mdc.wire_transfer_deadline < $2))"
            "  AND kyc.kyc_ok"
            "  AND (0=kyc.aml_decision)"
-           " ORDER BY mdc.deposit_deadline ASC"
+           " ORDER BY mdc.wire_transfer_deadline ASC"
            " LIMIT $3");
   qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn,
                                              "lookup_pending_deposits",
diff --git a/src/backenddb/pg_update_deposit_confirmation_status.c b/src/backenddb/pg_update_deposit_confirmation_status.c
index cd0e827b..61bda33b 100644
--- a/src/backenddb/pg_update_deposit_confirmation_status.c
+++ b/src/backenddb/pg_update_deposit_confirmation_status.c
@@ -45,7 +45,7 @@ TMH_PG_update_deposit_confirmation_status (
   PREPARE (pg,
            "update_deposit_confirmation_status",
            "UPDATE merchant_deposit_confirmations SET"
-           " deposit_deadline_type=$2"
+           " wire_transfer_deadline=$2"
            ",exchange_failure=$3"
            " WHERE deposit_confirmation_serial="
            "   (SELECT deposit_confirmation_serial"
diff --git a/src/backenddb/pg_update_webhook.c b/src/backenddb/pg_update_webhook.c
index c12ae9e3..087feb2a 100644
--- a/src/backenddb/pg_update_webhook.c
+++ b/src/backenddb/pg_update_webhook.c
@@ -47,7 +47,6 @@ TMH_PG_update_webhook (void *cls,
     GNUNET_PQ_query_param_end
   };
 
-
   check_connection (pg);
   PREPARE (pg,
            "update_webhook",
diff --git a/src/backenddb/plugin_merchantdb_postgres.c b/src/backenddb/plugin_merchantdb_postgres.c
index 291008d0..40fb1d19 100644
--- a/src/backenddb/plugin_merchantdb_postgres.c
+++ b/src/backenddb/plugin_merchantdb_postgres.c
@@ -202,6 +202,7 @@ postgres_create_tables (void *cls)
     GNUNET_PQ_make_try_execute ("SET search_path TO merchant;"),
     GNUNET_PQ_EXECUTE_STATEMENT_END
   };
+  enum GNUNET_GenericReturnValue ret;
 
   conn = GNUNET_PQ_connect_with_cfg (pc->cfg,
                                      "merchantdb-postgres",
@@ -210,8 +211,10 @@ postgres_create_tables (void *cls)
                                      NULL);
   if (NULL == conn)
     return GNUNET_SYSERR;
+  ret = GNUNET_PQ_exec_sql (conn,
+                            "procedures");
   GNUNET_PQ_disconnect (conn);
-  return GNUNET_OK;
+  return ret;
 }
 
 
diff --git a/src/backenddb/test_merchantdb.c b/src/backenddb/test_merchantdb.c
index d85bccd3..8d684371 100644
--- a/src/backenddb/test_merchantdb.c
+++ b/src/backenddb/test_merchantdb.c
@@ -2504,6 +2504,7 @@ test_insert_deposit (const struct InstanceData *instance,
                                          deposit->timestamp,
                                          &deposit->h_contract_terms,
                                          deposit->exchange_url,
+                                         deposit->timestamp,
                                          &awf,
                                          &deposit->wire_fee,
                                          &deposit->h_wire,
diff --git a/src/include/taler_merchantdb_plugin.h b/src/include/taler_merchantdb_plugin.h
index 953c7666..fd65e056 100644
--- a/src/include/taler_merchantdb_plugin.h
+++ b/src/include/taler_merchantdb_plugin.h
@@ -2167,6 +2167,7 @@ struct TALER_MERCHANTDB_Plugin
    * @param deposit_timestamp time when the exchange generated the deposit confirmation
    * @param h_contract_terms proposal data's hashcode
    * @param exchange_url URL of the exchange that issued @a coin_pub
+   * @param wire_transfer_deadline when do we expect the wire transfer from the exchange
    * @param total_without_fees deposited total in the batch without fees
    * @param wire_fee wire fee the exchange charges
    * @param h_wire hash of the wire details of the target account of the merchant
@@ -2182,6 +2183,7 @@ struct TALER_MERCHANTDB_Plugin
     struct GNUNET_TIME_Timestamp deposit_timestamp,
     const struct TALER_PrivateContractHashP *h_contract_terms,
     const char *exchange_url,
+    struct GNUNET_TIME_Timestamp wire_transfer_deadline,
     const struct TALER_Amount *total_without_fees,
     const struct TALER_Amount *wire_fee,
     const struct TALER_MerchantWireHashP *h_wire,