work on KYC support in auditor (WiP, not finished)

6 files changed, 293 insertions, 83 deletions

diff --git a/src/exchangedb/exchange-0002.sql.in b/src/exchangedb/exchange-0002.sql.in
index 9a810aa2e..b202d75ff 100644
--- a/src/exchangedb/exchange-0002.sql.in
+++ b/src/exchangedb/exchange-0002.sql.in
@@ -35,6 +35,20 @@ CREATE TYPE exchange_do_array_reserve_insert_return_type
 COMMENT ON TYPE exchange_do_array_reserve_insert_return_type
   IS 'Return type for exchange_do_array_reserves_insert() stored procedure';
 
+CREATE TYPE exchange_do_select_deposits_missing_wire_return_type
+  AS
+  (
+    total_amount_with_fee taler_amount,
+    payto_uri TEXT,
+    kyc_pending TEXT, -- can be NULL
+    wire_deadline INT8,
+    aml_status INT4,
+    aml_limit taler_amount
+  );
+COMMENT ON TYPE exchange_do_select_deposits_missing_wire_return_type
+  IS 'Return type for exchange_do_select_deposits_missing_wire';
+
+
 #include "0002-denominations.sql"
 #include "0002-denomination_revocations.sql"
 #include "0002-wire_targets.sql"
diff --git a/src/exchangedb/exchange_do_select_deposits_missing_wire.sql b/src/exchangedb/exchange_do_select_deposits_missing_wire.sql
new file mode 100644
index 000000000..9132e5b12
--- /dev/null
+++ b/src/exchangedb/exchange_do_select_deposits_missing_wire.sql
@@ -0,0 +1,217 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2023 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/>
+--
+-- @author: Christian Grothoff
+
+--CREATE TYPE exchange_do_select_deposits_missing_wire_return_type
+--  AS
+--  (
+--    total_amount taler_amount,
+--    payto_uri TEXT,
+--    kyc_pending TEXT,
+--    wire_deadline INT8,
+--    aml_status INT4
+--  );
+
+-- FIXME: this function is not working as intended at all yet, work in progress!
+
+CREATE OR REPLACE FUNCTION exchange_do_select_deposits_missing_wire(
+  IN in_start_date INT8,
+  IN in_end_date INT8)
+RETURNS SETOF exchange_do_select_deposits_missing_wire_return_type
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  missing CURSOR
+  FOR
+  SELECT
+    bdep.batch_deposit_serial_id
+   ,bdep.wire_target_h_payto
+   ,bdep.wire_deadline
+    FROM batch_deposits bdep
+    WHERE bdep.wire_deadline >= in_start_date
+      AND bdep.wire_deadline < in_end_date
+      AND NOT EXISTS (SELECT 1
+        FROM aggregation_tracking atr
+        WHERE (atr.batch_deposit_serial_id = bdep.batch_deposit_serial_id));
+DECLARE
+  my_earliest_deadline INT8; -- earliest deadline that was missed
+DECLARE
+  my_total_val INT8; -- all deposits without wire
+DECLARE
+  my_total_frac INT8; -- all deposits without wire (fraction, not normalized)
+DECLARE
+  my_refund_val INT8; -- all refunds without wire
+DECLARE
+  my_refund_frac INT8; -- all refunds without wire (fraction, not normalized)
+DECLARE
+  my_wire_target_h_payto BYTEA; -- hash of the target account
+DECLARE
+  my_payto_uri TEXT; -- the target account
+DECLARE
+  my_kyc_pending TEXT; -- pending KYC operations
+DECLARE
+  my_required_checks TEXT[];
+DECLARE
+  my_aml_status INT4; -- AML status (0: normal)
+DECLARE
+  my_total taler_amount; -- amount that was originally deposited
+DECLARE
+  my_batch_record RECORD;
+DECLARE
+  my_aml_data RECORD;
+DECLARE
+  my_aml_threshold taler_amount; -- threshold above which AML is triggered
+DECLARE
+  i RECORD;
+BEGIN
+
+OPEN missing;
+LOOP
+  FETCH NEXT FROM missing INTO i;
+  EXIT WHEN NOT FOUND;
+
+  IF ( (my_earliest_deadline IS NULL) OR
+       (my_earliest_deadline > i.wire_deadline) )
+  THEN
+    my_earliest_deadline = i.wire_deadline;
+  END IF;
+  SELECT
+    SUM((cdep.amount_with_fee).val) AS total_val
+   ,SUM((cdep.amount_with_fee).frac::INT8) AS total_frac
+   ,SUM((r.amount_with_fee).val) AS refund_val
+   ,SUM((r.amount_with_fee).frac::INT8) AS refund_frac
+    INTO
+      my_batch_record
+    FROM coin_deposits cdep
+    LEFT JOIN refunds r
+      ON ( (r.coin_pub = cdep.coin_pub) AND
+           (r.batch_deposit_serial_id = cdep.batch_deposit_serial_id) )
+    WHERE cdep.batch_deposit_serial_id = i.batch_deposit_serial_id;
+--    GROUP BY bdep.wire_target_h_payto; -- maybe use temporary table intead of cursor, or accumulate C-side?
+
+  my_total_val=my_batch_record.total_val;
+  my_total_frac=my_batch_record.total_frac;
+  my_refund_val=my_batch_record.refund_val;
+  my_refund_frac=my_batch_record.refund_frac;
+
+  RAISE WARNING 'tval: %', my_total_val;
+  RAISE WARNING 'tfrac: %', my_total_frac;
+  RAISE WARNING 'rval: %', my_refund_val;
+  RAISE WARNING 'rfrac: %', my_refund_frac;
+
+  IF my_refund_val IS NOT NULL
+  THEN
+    -- subtract refunds from total
+    my_total_val = my_total_val - my_refund_val;
+    -- note: frac could go negative here, that's OK
+    my_total_frac = my_total_frac - my_refund_frac;
+  END IF;
+  -- Normalize total amount
+  IF my_total_frac < 0
+  THEN
+    my_total.val = my_total_val - 1 + my_total_frac / 100000000;
+    my_total.frac = 100000000 + my_total_frac % 100000000;
+  ELSE
+    my_total.val = my_total_val + my_total_frac / 100000000;
+    my_total.frac = my_total_frac % 100000000;
+  END IF;
+  RAISE WARNING 'val: %', my_total.val;
+  RAISE WARNING 'frac: %', my_total.frac;
+  ASSERT my_total.frac >= 0, 'Normalized amount fraction must be non-negative';
+  ASSERT my_total.frac < 100000000, 'Normalized amount fraction must be below 100000000';
+
+  IF (my_total.val < 0)
+  THEN
+    -- Refunds above deposits. That's a problem, but not one for this auditor pass.
+    CONTINUE;
+  END IF;
+
+  -- Note: total amount here is NOT the exact amount due for the
+  -- wire transfer, as we did not consider deposit, refund and wire fees.
+  -- The amount given in the report is thus ONLY indicative of the non-refunded
+  -- gross amount, not the net transfer amount.
+
+  IF 0 = my_total_val + my_total_frac
+  THEN
+    -- full refund, skip report entirely
+    CONTINUE;
+  END IF;
+
+  -- Fetch payto URI
+  -- NOTE: we want to group by my_wire_target_h_payto and not do this repeatedly per batch deposit!
+  my_payto_uri = NULL;
+  SELECT payto_uri
+    INTO my_payto_uri
+    FROM wire_targets
+   WHERE wire_target_h_payto=my_wire_target_h_payto;
+
+  -- Get last AML decision
+  SELECT
+      new_threshold
+     ,kyc_requirements
+     ,new_status
+    INTO
+     my_aml_data
+     FROM aml_history
+    WHERE h_payto=my_wire_target_h_payto
+    ORDER BY aml_history_serial_id -- get last decision
+      DESC LIMIT 1;
+  IF FOUND
+  THEN
+    my_aml_threshold=my_aml_data.new_threshold;
+    my_kyc_pending=my_aml_data.kyc_requirements;
+    my_aml_status=my_aml_data.kyc_status;
+  ELSE
+    my_aml_threshold=NULL;
+    my_kyc_pending=NULL;
+    my_aml_status=0;
+  END IF;
+  IF 0 != my_aml_status
+  THEN
+    RETURN NEXT (
+       my_total
+      ,my_payto_uri
+      ,my_kyc_pending
+      ,my_earliest_deadline
+      ,my_aml_status
+      ,NULL);
+  END IF;
+
+  -- Check KYC status
+  SELECT string_to_array (required_checks, ' ')
+    INTO my_required_checks
+    FROM legitimization_requirements
+    WHERE h_payto=my_wire_target_h_payto;
+
+
+--  PERFORM -- provider
+--    FROM kyc_attributes
+--    WHERE legitimization_serial=my_legitimization_serial;
+  -- FIXME: can't tell if providers cover all required checks from DB!!!
+  -- Idea: expand kyc_attributes table with list of satisfied checks!??!
+
+    RETURN NEXT (
+       my_total
+      ,my_payto_uri
+      ,my_kyc_pending
+      ,my_earliest_deadline
+      ,my_aml_status
+      ,NULL::taler_amount);
+
+END LOOP;
+CLOSE missing;
+RETURN;
+END $$;
diff --git a/src/exchangedb/pg_select_batch_deposits_missing_wire.c b/src/exchangedb/pg_select_batch_deposits_missing_wire.c
index 7118135a1..25b532ad6 100644
--- a/src/exchangedb/pg_select_batch_deposits_missing_wire.c
+++ b/src/exchangedb/pg_select_batch_deposits_missing_wire.c
@@ -69,25 +69,34 @@ missing_wire_cb (void *cls,
 
   while (0 < num_results)
   {
-    uint64_t rowid;
-    struct TALER_CoinSpendPublicKeyP coin_pub;
-    struct TALER_Amount amount;
-    char *payto_uri;
+    struct TALER_Amount total_amount;
+    struct TALER_Amount aml_limit;
+    char *payto_uri = NULL;
+    char *kyc_pending = NULL;
+    uint32_t aml_status32 = TALER_AML_NORMAL;
     struct GNUNET_TIME_Timestamp deadline;
-    bool done;
+    bool no_aml_limit;
     struct GNUNET_PQ_ResultSpec rs[] = {
-      GNUNET_PQ_result_spec_uint64 ("batch_deposit_serial_id",
-                                    &rowid),
-      GNUNET_PQ_result_spec_auto_from_type ("coin_pub",
-                                            &coin_pub),
-      TALER_PQ_RESULT_SPEC_AMOUNT ("amount_with_fee",
-                                   &amount),
-      GNUNET_PQ_result_spec_string ("payto_uri",
-                                    &payto_uri),
+      TALER_PQ_RESULT_SPEC_AMOUNT ("total_amount_with_fee",
+                                   &total_amount),
+      GNUNET_PQ_result_spec_allow_null (
+        GNUNET_PQ_result_spec_string ("payto_uri",
+                                      &payto_uri),
+        NULL),
+      GNUNET_PQ_result_spec_allow_null (
+        GNUNET_PQ_result_spec_string ("kyc_pending",
+                                      &kyc_pending),
+        NULL),
       GNUNET_PQ_result_spec_timestamp ("wire_deadline",
                                        &deadline),
-      GNUNET_PQ_result_spec_bool ("done",
-                                  &done),
+      GNUNET_PQ_result_spec_allow_null (
+        GNUNET_PQ_result_spec_uint32 ("aml_status",
+                                      &aml_status32),
+        NULL),
+      GNUNET_PQ_result_spec_allow_null (
+        TALER_PQ_RESULT_SPEC_AMOUNT ("aml_limit",
+                                     &aml_limit),
+        &no_aml_limit),
       GNUNET_PQ_result_spec_end
     };
 
@@ -101,12 +110,12 @@ missing_wire_cb (void *cls,
       return;
     }
     mwc->cb (mwc->cb_cls,
-             rowid,
-             &coin_pub,
-             &amount,
+             &total_amount,
              payto_uri,
              deadline,
-             done);
+             kyc_pending,
+             (enum TALER_AmlDecisionState) aml_status32,
+             no_aml_limit ? NULL : &aml_limit);
     GNUNET_PQ_cleanup_result (rs);
   }
 }
@@ -134,39 +143,17 @@ TEH_PG_select_batch_deposits_missing_wire (
   };
   enum GNUNET_DB_QueryStatus qs;
 
-  // FIXME: used by the auditor; can probably be done
-  // smarter by checking if 'done' or 'blocked'
-  // are set correctly when going over deposits, instead
-  // of JOINing with refunds.
-  // Also unclear why we return by coin_pub here;
-  // Also fails to check overdue in case of PARTIAL refunds.
-
   PREPARE (pg,
            "deposits_get_overdue",
            "SELECT"
-           " bdep.batch_deposit_serial_id"
-           ",cdep.coin_pub"
-           ",cdep.amount_with_fee"
-           ",wt.payto_uri"
-           ",bdep.wire_deadline"
-           ",bdep.done"
-           " FROM batch_deposits bdep"
-           "   JOIN coin_deposits cdep"
-           "     USING (batch_deposit_serial_id)"
-           "   JOIN known_coins"
-           "     USING (coin_pub)"
-           "   JOIN wire_targets wt"
-           "     USING (wire_target_h_payto)"
-           " WHERE bdep.wire_deadline >= $1"
-           " AND bdep.wire_deadline < $2"
-           " AND NOT (EXISTS (SELECT 1"
-           "            FROM refunds r"
-           "            WHERE (r.coin_pub = cdep.coin_pub)"
-           "               AND (r.batch_deposit_serial_id = bdep.batch_deposit_serial_id))"
-           "       OR EXISTS (SELECT 1"
-           "            FROM aggregation_tracking atr"
-           "            WHERE (atr.batch_deposit_serial_id = bdep.batch_deposit_serial_id)))"
-           " ORDER BY bdep.wire_deadline ASC");
+           " total_amount_with_fee"
+           ",payto_uri"
+           ",kyc_pending"
+           ",wire_deadline"
+           ",aml_status"
+           ",aml_limit"
+           " FROM exchange_do_select_deposits_missing_wire"
+           " ($1,$2);");
   qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn,
                                              "deposits_get_overdue",
                                              params,
diff --git a/src/exchangedb/pg_select_batch_deposits_missing_wire.h b/src/exchangedb/pg_select_batch_deposits_missing_wire.h
index 697baa837..27e6b0850 100644
--- a/src/exchangedb/pg_select_batch_deposits_missing_wire.h
+++ b/src/exchangedb/pg_select_batch_deposits_missing_wire.h
@@ -37,13 +37,12 @@
  * @return transaction status code
  */
 enum GNUNET_DB_QueryStatus
-TEH_PG_select_batch_deposits_missing_wire (void *cls,
-                                           struct GNUNET_TIME_Timestamp
-                                           start_date,
-                                           struct GNUNET_TIME_Timestamp
-                                           end_date,
-                                           TALER_EXCHANGEDB_WireMissingCallback
-                                           cb,
-                                           void *cb_cls);
+TEH_PG_select_batch_deposits_missing_wire (
+  void *cls,
+  struct GNUNET_TIME_Timestamp start_date,
+  struct GNUNET_TIME_Timestamp end_date,
+  TALER_EXCHANGEDB_WireMissingCallback
+  cb,
+  void *cb_cls);
 
 #endif
diff --git a/src/exchangedb/procedures.sql.in b/src/exchangedb/procedures.sql.in
index cc67249fd..4a23ec742 100644
--- a/src/exchangedb/procedures.sql.in
+++ b/src/exchangedb/procedures.sql.in
@@ -26,6 +26,7 @@ SET search_path TO exchange;
 #include "exchange_do_recoup_by_reserve.sql"
 #include "exchange_do_deposit.sql"
 #include "exchange_do_melt.sql"
+#include "exchange_do_select_deposits_missing_wire.sql"
 #include "exchange_do_refund.sql"
 #include "exchange_do_recoup_to_reserve.sql"
 #include "exchange_do_recoup_to_coin.sql"
diff --git a/src/exchangedb/test_exchangedb.c b/src/exchangedb/test_exchangedb.c
index 6e1d3a006..081126637 100644
--- a/src/exchangedb/test_exchangedb.c
+++ b/src/exchangedb/test_exchangedb.c
@@ -1117,44 +1117,36 @@ drop:
  * and have not yet seen a wire transfer.
  *
  * @param cls closure a `struct TALER_EXCHANGEDB_Deposit *`
- * @param rowid deposit table row of the coin's deposit
- * @param coin_pub public key of the coin
- * @param amount value of the deposit, including fee
- * @param payto_uri where should the funds be wired
- * @param deadline what was the requested wire transfer deadline
- * @param done did the exchange claim that it made a transfer?
+ * @param total_amount value of all missing deposits, including fees
+ * @param payto_uri where should the funds be wired; URI in payto://-format
+ * @param deadline what was the earliest requested wire transfer deadline
+ * @param kyc_pending NULL if no KYC requirement is pending, otherwise text describing the missing KYC requirement
+ * @param aml_status status of AML possibly blocking the transfer
+ * @param aml_limit current monthly AML limit
  */
 static void
-wire_missing_cb (void *cls,
-                 uint64_t rowid,
-                 const struct TALER_CoinSpendPublicKeyP *coin_pub,
-                 const struct TALER_Amount *amount,
-                 const char *payto_uri,
-                 struct GNUNET_TIME_Timestamp deadline,
-                 bool done)
+wire_missing_cb (
+  void *cls,
+  const struct TALER_Amount *total_amount,
+  const char *payto_uri,
+  struct GNUNET_TIME_Timestamp deadline,
+  const char *kyc_pending,
+  enum TALER_AmlDecisionState status,
+  const struct TALER_Amount *aml_limit)
 {
   const struct TALER_EXCHANGEDB_CoinDepositInformation *deposit = cls;
 
   (void) payto_uri;
   (void) deadline;
-  (void) rowid;
-  if (done)
-  {
-    GNUNET_break (0);
-    result = 66;
-  }
-  if (0 != TALER_amount_cmp (amount,
+  (void) kyc_pending;
+  (void) status;
+  (void) aml_limit;
+  if (0 != TALER_amount_cmp (total_amount,
                              &deposit->amount_with_fee))
   {
     GNUNET_break (0);
     result = 66;
   }
-  if (0 != GNUNET_memcmp (coin_pub,
-                          &deposit->coin.coin_pub))
-  {
-    GNUNET_break (0);
-    result = 66;
-  }
 }