Re-implement Trustly refund tracker on top of the ledger

Previously we explicitly called the API about the withdrawal entry, but
that API is now restricted. And we don't *really* need it -- refunds
show up on the ledger, so just use that.

The only case this doesn't work is refunds in a different currency, and
in that case we just estimate the fees and send a notice to hope for the
best :) It's not a common scenario, and not worth spending too much on
(in production it has never happened outside of testing).

To do this, we now track refund transactions int he TrustlyWithdrawals
table, and also store the orderid on them if we have them.

This removes the separate job to match trustly refunds, and handles it
all from the fetch withdrawals job.

Not fully tested since it needs production data, so expect some
follow-up commits..

4 files changed, 142 insertions, 139 deletions

diff --git a/postgresqleu/trustlypayment/management/commands/trustly_fetch_withdrawals.py b/postgresqleu/trustlypayment/management/commands/trustly_fetch_withdrawals.py
index 707865d1..d55382c4 100644
--- a/postgresqleu/trustlypayment/management/commands/trustly_fetch_withdrawals.py
+++ b/postgresqleu/trustlypayment/management/commands/trustly_fetch_withdrawals.py
@@ -7,24 +7,26 @@
 
 from django.core.management.base import BaseCommand
 from django.db import transaction
+from django.conf import settings
 
-from datetime import time, datetime, timedelta
+from datetime import datetime, timedelta
 from decimal import Decimal
 
 from postgresqleu.accounting.util import create_accounting_entry
 from postgresqleu.invoices.util import is_managed_bank_account
 from postgresqleu.invoices.util import register_pending_bank_matcher
 
-from postgresqleu.invoices.models import InvoicePaymentMethod
+from postgresqleu.invoices.models import InvoicePaymentMethod, Invoice
+from postgresqleu.invoices.util import InvoiceManager
 from postgresqleu.trustlypayment.util import Trustly
-from postgresqleu.trustlypayment.models import TrustlyWithdrawal, TrustlyLog
+from postgresqleu.trustlypayment.models import TrustlyWithdrawal, TrustlyLog, TrustlyTransaction
 
 
 class Command(BaseCommand):
-    help = 'Fetch Trustly withdrawals'
+    help = 'Fetch Trustly withdrawals/refunds'
 
     class ScheduledJob:
-        scheduled_times = [time(22, 00), ]
+        scheduled_interval = timedelta(hours=6)
 
         @classmethod
         def should_run(self):
@@ -43,13 +45,23 @@ class Command(BaseCommand):
         transactions = trustly.getledgerforrange(datetime.today() - timedelta(days=7), datetime.today())
 
         for t in transactions:
-            if t['accountname'] == 'BANK_WITHDRAWAL_QUEUED' and not t['orderid']:
-                # If it has an orderid, it's a refund, but if not, then it's a transfer out (probably)
+            if t['accountname'] == 'BANK_WITHDRAWAL_QUEUED':
+                if t['currency'] != settings.CURRENCY_ABBREV:
+                    TrustlyLog(
+                        message="Received Trustly withdrawal with gluepayid {} in currency {}, expected {}.".format(
+                            t['gluepayid'], t['currency'], settings.CURRENCY_ABBREV,
+                        ),
+                        error=True,
+                        paymentmethod=method,
+                    ).save()
+                    continue
+
                 w, created = TrustlyWithdrawal.objects.get_or_create(paymentmethod=method,
                                                                      gluepayid=t['gluepayid'],
                                                                      defaults={
                                                                          'amount': -Decimal(t['amount']),
                                                                          'message': t['messageid'],
+                                                                         'orderid': t['orderid'],
                                                                      },
                 )
                 w.save()
@@ -58,17 +70,92 @@ class Command(BaseCommand):
                     TrustlyLog(message='New bank withdrawal of {0} found'.format(-Decimal(t['amount'])),
                                paymentmethod=method).save()
 
-                    accstr = 'Transfer from Trustly to bank'
-                    accrows = [
-                        (pm.config('accounting_income'), accstr, -w.amount, None),
-                        (pm.config('accounting_transfer'), accstr, w.amount, None),
-                    ]
-                    entry = create_accounting_entry(accrows,
-                                                    True,
-                                                    [],
-                    )
-                    if is_managed_bank_account(pm.config('accounting_transfer')):
-                        register_pending_bank_matcher(pm.config('accounting_transfer'),
-                                                      '.*TRUSTLY.*{0}.*'.format(w.gluepayid),
-                                                      w.amount,
-                                                      entry)
+                    if w.orderid:
+                        # This is either a payout (which we don't support) or a refund (which we do, so track it here)
+                        if not w.message.startswith('Refund '):
+                            TrustlyLog(
+                                message="Received bank withdrawal with orderid {} that does not appear to be a refund. What is it?".format(w.orderid),
+                                error=True,
+                                paymentmethod=method,
+                            ).save()
+                            continue
+
+                        try:
+                            trans = TrustlyTransaction.objects.get(orderid=t['orderid'])
+                        except TrustlyTransaction.DoesNotExist:
+                            TrustlyLog(
+                                message="Received bank withdrawal with orderid {} which does not exist!".format(w.orderid),
+                                error=True,
+                                paymentmethod=method,
+                            ).save()
+                            continue
+
+                        # Do we have a matching refund object?
+                        refundlist = list(Invoice.objects.get(pk=trans.invoiceid).invoicerefund_set.filter(issued__isnull=False, completed__isnull=True))
+                        for r in refundlist:
+                            if r.fullamount == w.amount:
+                                # Found the matching refund!
+                                manager = InvoiceManager()
+                                manager.complete_refund(
+                                    r.id,
+                                    r.fullamount,
+                                    0,
+                                    pm.config('accounting_income'),
+                                    pm.config('accounting_fee'),
+                                    [],
+                                    method,
+                                )
+                                w.matched_refund = r
+                                w.save(update_fields=['matched_refund'])
+                                break
+                        else:
+                            # Another option is it's a refund in a different currency and we lost out on some currency conversion.
+                            # If we find a refund that's within 5% of the original value and we didn't find an exact one, then let's assume that's the case.
+                            # (in 99.999% of all cases there will only be one refund pending, so it'll very likely be correct)
+                            for r in refundlist:
+                                if w.amount < r.fullamount and w.amount / r.fullamount > 0.95:
+                                    manager = InvoiceManager()
+                                    manager.complete_refund(
+                                        r.id,
+                                        r.fullamount,
+                                        r.fullamount - w.amount,
+                                        pm.config('accounting_income'),
+                                        pm.config('accounting_fee'),
+                                        [],
+                                        method,
+                                    )
+                                    w.matched_refund = r
+                                    w.save(update_fields=['matched_refund'])
+                                    TrustlyLog(
+                                        message="Refund for order {}, invoice {}, was made as {} {}. Found no exact match for a refund, but matched to a refund of {} {} with fees of {} {}. Double check!".format(
+                                            w.orderid, r.invoice_id,
+                                            w.amount, settings.CURRENCY_ABBREV,
+                                            r.fullamount, settings.CURRENCY_ABBREV,
+                                            r.fullamount - w.amount, settings.CURRENCY_ABBREV,
+                                        ),
+                                        error=False,
+                                        paymentmethod=method,
+                                    ).save()
+                                    break
+                            else:
+                                TrustlyLog(
+                                    message="Received refund of {} for orderid {}, but could not find a matching refund object.".format(w.amount, w.orderid),
+                                    error=True,
+                                    paymentmethod=method,
+                                ).save()
+                    else:
+                        # No orderid means it's a payout/settlement
+                        accstr = 'Transfer from Trustly to bank'
+                        accrows = [
+                            (pm.config('accounting_income'), accstr, -w.amount, None),
+                            (pm.config('accounting_transfer'), accstr, w.amount, None),
+                        ]
+                        entry = create_accounting_entry(accrows,
+                                                        True,
+                                                        [],
+                        )
+                        if is_managed_bank_account(pm.config('accounting_transfer')):
+                            register_pending_bank_matcher(pm.config('accounting_transfer'),
+                                                          '.*TRUSTLY.*{0}.*'.format(w.gluepayid),
+                                                          w.amount,
+                                                          entry)
diff --git a/postgresqleu/trustlypayment/management/commands/trustly_match_refunds.py b/postgresqleu/trustlypayment/management/commands/trustly_match_refunds.py
deleted file mode 100644
index 9ee9c146..00000000
--- a/postgresqleu/trustlypayment/management/commands/trustly_match_refunds.py
+++ /dev/null
@@ -1,116 +0,0 @@
-#
-# Trustly does not send notifications on refunds completed (they just
-# give success as response to API call). For this reason, we have this
-# script that polls to check if a refund has completed successfully.
-#
-# Copyright (C) 2010-2018, PostgreSQL Europe
-#
-
-
-from django.core.management.base import BaseCommand, CommandError
-from django.db import transaction
-from django.conf import settings
-
-from postgresqleu.trustlypayment.util import Trustly
-from postgresqleu.trustlypayment.models import TrustlyTransaction, TrustlyLog
-from postgresqleu.invoices.models import InvoiceRefund, InvoicePaymentMethod
-from postgresqleu.invoices.util import InvoiceManager
-from postgresqleu.util.currency import format_currency
-
-from decimal import Decimal
-from datetime import timedelta
-import dateutil
-
-
-class Command(BaseCommand):
-    help = 'Flag completed Trustly refunds'
-
-    class ScheduledJob:
-        scheduled_interval = timedelta(hours=4)
-
-        @classmethod
-        def should_run(self):
-            if not InvoicePaymentMethod.objects.filter(active=True, classname='postgresqleu.util.payment.trustly.TrustlyPayment').exists():
-                return False
-
-            return InvoiceRefund.objects.filter(completed__isnull=True, invoice__paidusing__classname='postgresqleu.util.payment.trustly.TrustlyPayment').exists()
-
-    @transaction.atomic
-    def handle(self, *args, **options):
-        for method in InvoicePaymentMethod.objects.filter(active=True, classname='postgresqleu.util.payment.trustly.TrustlyPayment'):
-            self.process_one_account(method)
-
-    def process_one_account(self, method):
-        pm = method.get_implementation()
-
-        trustly = Trustly(pm)
-        manager = InvoiceManager()
-
-        refunds = InvoiceRefund.objects.filter(completed__isnull=True, invoice__paidusing=method)
-
-        for r in refunds:
-            # Find the matching Trustly transaction
-            trustlytransactionlist = list(TrustlyTransaction.objects.filter(invoiceid=r.invoice.pk, paymentmethod=method))
-            if len(trustlytransactionlist) == 0:
-                raise CommandError("Could not find trustly transaction for invoice {0}".format(r.invoice.pk))
-            elif len(trustlytransactionlist) != 1:
-                raise CommandError("Found {0} trustly transactions for invoice {1}!".format(len(trustlytransactionlist), r.invoice.pk))
-            trustlytrans = trustlytransactionlist[0]
-            w = trustly.getwithdrawal(trustlytrans.orderid)
-            if not w:
-                # No refund yet
-                continue
-
-            if w['transferstate'] != 'CONFIRMED':
-                # Still pending
-                continue
-
-            if w['currency'] != settings.CURRENCY_ABBREV:
-                # If somebody paid in a different currency (and Trustly converted it for us),
-                # the withdrawal entry is specified in the original currency, which is more than
-                # a little annoying. To deal with it, attempt to fetch the ledger for the day
-                # and if we can find it there, use the amount from that one.
-                day = dateutil.parser.parse(w['datestamp']).date()
-                ledgerrows = trustly.getledgerforday(day)
-                for lr in ledgerrows:
-                    if int(lr['orderid']) == trustlytrans.orderid and lr['accountname'] == 'BANK_WITHDRAWAL_QUEUED':
-                        # We found the corresponding accounting row. So we take the amount from
-                        # this and convert the difference to what we expeced into the fee. This
-                        # can end up being a negative fee, but it should be small enough that
-                        # it's not a real problem.
-                        fees = (r.fullamount + Decimal(lr['amount']).quantize(Decimal('0.01')))
-                        TrustlyLog(
-                            message="Refund for order {0}, invoice {1}, was made as {2} {3} instead of {4}. Using ledger mapped to {5} with difference of {6} booked as fees".format(
-                                trustlytrans.orderid,
-                                r.invoice.pk,
-                                Decimal(w['amount']),
-                                w['currency'],
-                                format_currency(r.fullamount),
-                                format_currency(Decimal(lr['amount']).quantize(Decimal('0.01'))),
-                                format_currency(fees),
-                            ),
-                            error=False,
-                            paymentmethod=method,
-                        ).save()
-                        break
-                else:
-                    # Unable to find the refund in the ledger. This could be a matter of timing,
-                    # so yell about it but try agian.
-                    raise CommandError("Trustly refund for invoice {0} was made in {1} instead of {2}, but could not be found in ledger.".format(r.invoice.pk, w['currency'], settings.CURRENCY_ABBREV))
-            else:
-                # Currency is correct, so check that the refunded amount is the same as
-                # the one we expected.
-                if Decimal(w['amount']) != r.fullamount:
-                    raise CommandError("Mismatch in amount on Trustly refund for invoice {0} ({1} vs {2})".format(r.invoice.pk, Decimal(w['amount']), r.fullamount))
-                fees = 0
-
-            # Ok, things look good!
-            TrustlyLog(message="Refund for order {0}, invoice {1}, completed".format(trustlytrans.orderid, r.invoice.pk), error=False, paymentmethod=method).save()
-            manager.complete_refund(
-                r.id,
-                r.fullamount,
-                fees,
-                pm.config('accounting_income'),
-                pm.config('accounting_fee'),
-                [],
-                method)
diff --git a/postgresqleu/trustlypayment/migrations/0005_refund_matching_on_withdrawals.py b/postgresqleu/trustlypayment/migrations/0005_refund_matching_on_withdrawals.py
new file mode 100644
index 00000000..ccdbf545
--- /dev/null
+++ b/postgresqleu/trustlypayment/migrations/0005_refund_matching_on_withdrawals.py
@@ -0,0 +1,30 @@
+# Generated by Django 4.2.11 on 2025-10-10 11:30
+
+from django.db import migrations, models
+import django.db.models.deletion
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ('invoices', '0021_alter_vatrate_vatpercent'),
+        ('trustlypayment', '0004_trustlywithdrawal'),
+    ]
+
+    operations = [
+        migrations.AddField(
+            model_name='trustlywithdrawal',
+            name='matched_refund',
+            field=models.ForeignKey(blank=True, null=True, on_delete=django.db.models.deletion.SET_NULL, to='invoices.invoicerefund'),
+        ),
+        migrations.AddField(
+            model_name='trustlywithdrawal',
+            name='orderid',
+            field=models.BigIntegerField(blank=True, null=True),
+        ),
+        migrations.AlterField(
+            model_name='trustlytransaction',
+            name='orderid',
+            field=models.BigIntegerField(unique=True),
+        ),
+    ]
diff --git a/postgresqleu/trustlypayment/models.py b/postgresqleu/trustlypayment/models.py
index 89e93c9c..7f8c0624 100644
--- a/postgresqleu/trustlypayment/models.py
+++ b/postgresqleu/trustlypayment/models.py
@@ -1,7 +1,7 @@
 from django.db import models
 
 
-from postgresqleu.invoices.models import InvoicePaymentMethod
+from postgresqleu.invoices.models import InvoicePaymentMethod, InvoiceRefund
 
 
 class TrustlyTransaction(models.Model):
@@ -11,7 +11,7 @@ class TrustlyTransaction(models.Model):
     amount = models.DecimalField(decimal_places=2, max_digits=20, null=False)
     invoiceid = models.IntegerField(null=False, blank=False)
     redirecturl = models.CharField(max_length=2000, null=False, blank=False)
-    orderid = models.BigIntegerField(null=False, blank=False)
+    orderid = models.BigIntegerField(null=False, blank=False, unique=True)
     paymentmethod = models.ForeignKey(InvoicePaymentMethod, blank=False, null=False, on_delete=models.CASCADE)
 
     def __str__(self):
@@ -59,5 +59,7 @@ class ReturnAuthorizationStatus(models.Model):
 class TrustlyWithdrawal(models.Model):
     paymentmethod = models.ForeignKey(InvoicePaymentMethod, blank=False, null=False, on_delete=models.CASCADE)
     gluepayid = models.BigIntegerField(null=False, blank=False)
+    orderid = models.BigIntegerField(null=True, blank=True)
     amount = models.DecimalField(decimal_places=2, max_digits=20, null=False, blank=False)
     message = models.CharField(max_length=200, null=False, blank=True)
+    matched_refund = models.ForeignKey(InvoiceRefund, null=True, blank=True, on_delete=models.SET_NULL)