Add gc logic

7 files changed, 264 insertions, 40 deletions

diff --git a/bank/src/main/kotlin/tech/libeufin/bank/db/Database.kt b/bank/src/main/kotlin/tech/libeufin/bank/db/Database.kt
index 19706a7b..a37048c4 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/db/Database.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/db/Database.kt
@@ -1,6 +1,6 @@
 /*
  * This file is part of LibEuFin.
- * Copyright (C) 2023 Taler Systems S.A.
+ * Copyright (C) 2023-2024 Taler Systems S.A.
 
  * LibEuFin is free software; you can redistribute it and/or modify
  * it under the terms of the GNU Affero General Public License as
@@ -46,6 +46,7 @@ class Database(dbConfig: String, internal val bankCurrency: String, internal val
     val transaction = TransactionDAO(this)
     val token = TokenDAO(this)
     val tan = TanDAO(this)
+    val gc = GcDAO(this)
 
     suspend fun monitor(
         params: MonitorParams
diff --git a/bank/src/main/kotlin/tech/libeufin/bank/db/GcDAO.kt b/bank/src/main/kotlin/tech/libeufin/bank/db/GcDAO.kt
new file mode 100644
index 00000000..9f5e9431
--- /dev/null
+++ b/bank/src/main/kotlin/tech/libeufin/bank/db/GcDAO.kt
@@ -0,0 +1,85 @@
+/*
+ * This file is part of LibEuFin.
+ * Copyright (C) 2024 Taler Systems S.A.
+
+ * LibEuFin is free software; you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation; either version 3, or
+ * (at your option) any later version.
+
+ * LibEuFin 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 Affero General
+ * Public License for more details.
+
+ * You should have received a copy of the GNU Affero General Public
+ * License along with LibEuFin; see the file COPYING.  If not, see
+ * <http://www.gnu.org/licenses/>
+ */
+
+package tech.libeufin.bank.db
+
+import tech.libeufin.bank.*
+import tech.libeufin.common.*
+import tech.libeufin.common.crypto.*
+import java.time.Instant
+import java.time.ZoneOffset
+import java.time.LocalDateTime
+import java.time.temporal.TemporalAmount
+import java.time.chrono.ChronoLocalDateTime
+
+/** Data access logic for garbage collection */
+class GcDAO(private val db: Database) {
+    /** Run garbage collection  */
+    suspend fun collect(
+        now: Instant,
+        abortAfter: TemporalAmount,
+        cleanAfter: TemporalAmount,
+        deleteAfter: TemporalAmount
+    ) = db.conn { conn ->
+        val dateTime = LocalDateTime.ofInstant(now, ZoneOffset.UTC)
+        val abortAfterMicro = dateTime.minus(abortAfter).toInstant(ZoneOffset.UTC).micros()
+        val cleanAfterMicro = dateTime.minus(cleanAfter).toInstant(ZoneOffset.UTC).micros()
+        val deleteAfterMicro = dateTime.minus(deleteAfter).toInstant(ZoneOffset.UTC).micros()
+        
+        // Abort pending operations
+        conn.prepareStatement(
+            "UPDATE taler_withdrawal_operations SET aborted = true WHERE creation_date < ?"
+        ).run {
+            setLong(1, abortAfterMicro) 
+            execute()
+        }
+
+        // Clean aborted operations, expired challenges and expired tokens
+        for (smt in listOf(
+            "DELETE FROM taler_withdrawal_operations WHERE aborted = true AND creation_date < ?",
+            "DELETE FROM tan_challenges WHERE expiration_date < ?",
+            "DELETE FROM bearer_tokens WHERE expiration_time < ?"
+        )) {
+            conn.prepareStatement(smt).run {
+                setLong(1, cleanAfterMicro) 
+                execute()
+            }
+        }
+
+        // Delete old bank transactions, linked operations are deleted by CASCADE
+        conn.prepareStatement(
+            "DELETE FROM bank_account_transactions WHERE transaction_date < ?"
+        ).run {
+            setLong(1, deleteAfterMicro) 
+            execute()
+        }
+
+        // Hard delete soft deleted customer without bank transactions, bank account are deleted by CASCADE
+        conn.prepareStatement("""
+            DELETE FROM customers WHERE deleted_at IS NOT NULL AND NOT EXISTS(
+                SELECT 1 FROM bank_account_transactions NATURAL JOIN bank_accounts
+                    WHERE owning_customer_id=customer_id
+            )
+        """).run {
+            execute()
+        }
+
+        // TODO clean stats
+    }
+}
\ No newline at end of file
diff --git a/bank/src/test/kotlin/GcTest.kt b/bank/src/test/kotlin/GcTest.kt
new file mode 100644
index 00000000..550178a4
--- /dev/null
+++ b/bank/src/test/kotlin/GcTest.kt
@@ -0,0 +1,167 @@
+/*
+ * This file is part of LibEuFin.
+ * Copyright (C) 2024 Taler Systems S.A.
+
+ * LibEuFin is free software; you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation; either version 3, or
+ * (at your option) any later version.
+
+ * LibEuFin 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 Affero General
+ * Public License for more details.
+
+ * You should have received a copy of the GNU Affero General Public
+ * License along with LibEuFin; see the file COPYING.  If not, see
+ * <http://www.gnu.org/licenses/>
+ */
+
+import org.junit.Test
+import tech.libeufin.bank.DecimalNumber
+import tech.libeufin.bank.db.TransactionDAO.BankTransactionResult
+import tech.libeufin.bank.db.WithdrawalDAO.*
+import tech.libeufin.bank.db.TransactionDAO.*
+import tech.libeufin.bank.db.CashoutDAO.CashoutCreationResult
+import tech.libeufin.bank.db.ExchangeDAO.TransferResult
+import tech.libeufin.common.*
+import io.ktor.client.request.*
+import io.ktor.client.statement.*
+import io.ktor.http.*
+import tech.libeufin.bank.*
+import java.time.*
+import java.util.*
+import kotlin.test.*
+
+class GcTest {
+    @Test
+    fun gc() = bankSetup { db -> db.conn { conn ->
+        suspend fun assertNb(nb: Int, stmt: String) {
+            assertEquals(nb, conn.prepareStatement(stmt).one { it.getInt(1) })
+        }
+        suspend fun assertNbAccount(nb: Int) = assertNb(nb, "SELECT count(*) from bank_accounts")
+        suspend fun assertNbTokens(nb: Int) = assertNb(nb, "SELECT count(*) from bearer_tokens")
+        suspend fun assertNbTan(nb: Int) = assertNb(nb, "SELECT count(*) from tan_challenges")
+        suspend fun assertNbCashout(nb: Int) = assertNb(nb, "SELECT count(*) from cashout_operations")
+        suspend fun assertNbWithdrawal(nb: Int) = assertNb(nb, "SELECT count(*) from taler_withdrawal_operations")
+        suspend fun assertNbBankTx(nb: Int) = assertNb(nb, "SELECT count(*) from bank_transaction_operations")
+        suspend fun assertNbTx(nb: Int) = assertNb(nb, "SELECT count(*) from bank_account_transactions")
+        suspend fun assertNbIncoming(nb: Int) = assertNb(nb, "SELECT count(*) from taler_exchange_incoming")
+        suspend fun assertNbOutgoing(nb: Int) = assertNb(nb, "SELECT count(*) from taler_exchange_outgoing")
+
+        // Time calculation
+        val abortAfter = Duration.ofMinutes(15)
+        val cleanAfter = Period.ofDays(14)
+        val deleteAfter = Period.ofYears(10)
+        val now = Instant.now()
+        val dateTime = LocalDateTime.ofInstant(now, ZoneOffset.UTC)
+        val abort = dateTime.minus(abortAfter).toInstant(ZoneOffset.UTC)
+        val clean = dateTime.minus(cleanAfter).toInstant(ZoneOffset.UTC)
+        val delete = dateTime.minus(deleteAfter).toInstant(ZoneOffset.UTC)
+   
+
+        // Create test accounts
+        val payto = IbanPayto.rand()
+        val oldPayto = client.post("/accounts") {
+            json {
+                "username" to "old_account"
+                "password" to "old_account-password"
+                "name" to "Old Account"
+                "cashout_payto_uri" to payto
+            }
+        }.assertOkJson<RegisterAccountResponse>().internal_payto_uri
+        val recentPayto = client.post("/accounts") {
+            json {
+                "username" to "recent_account"
+                "password" to "recent_account-password"
+                "name" to "Recent Account"
+                "cashout_payto_uri" to payto
+            }
+
+        }.assertOkJson<RegisterAccountResponse>().internal_payto_uri
+        assertNbAccount(6)
+
+        // Create test tokens
+        for (time in listOf(now, clean)) {
+            for (account in listOf("old_account", "recent_account")) {
+                assert(db.token.create(account, ByteArray(32).rand(), time, time, TokenScope.readonly, false))
+                db.tan.new(account, Operation.cashout, "", "", time, 0, Duration.ZERO, null, null)
+            }
+        }
+        assertNbTokens(4)
+        assertNbTan(4)
+
+        // Create test operations
+        val from = TalerAmount("KUDOS:1")
+        val to = convert("KUDOS:1")
+        for ((account, times) in listOf(
+            Pair("old_account", listOf(delete)),
+            Pair("recent_account", listOf(now, abort, clean, delete))
+        )) {
+            for (time in times) {
+                val uuid = UUID.randomUUID()
+                assertEquals(
+                    db.withdrawal.create(account, uuid, from, time),
+                    WithdrawalCreationResult.Success
+                )
+                assertIs<WithdrawalSelectionResult.Success>(
+                    db.withdrawal.setDetails(uuid, exchangePayto, EddsaPublicKey.rand())
+                )
+                assertEquals(
+                    db.withdrawal.confirm(account, uuid, time, false),
+                    WithdrawalConfirmationResult.Success
+                )
+                assertIs<CashoutCreationResult.Success>(
+                    db.cashout.create(account, ShortHashCode.rand(), from, to, "", time, false),
+                )
+                assertIs<BankTransactionResult.Success>(
+                    db.transaction.create(customerPayto, account, "", from, time, false, ShortHashCode.rand()),
+                )
+            }
+            for (time in listOf(now, abort, clean, delete)) {
+                assertEquals(
+                    db.withdrawal.create(account, UUID.randomUUID(), from, time),
+                    WithdrawalCreationResult.Success
+                )
+            }
+        }
+        for (time in listOf(now, abort, clean, delete)) {
+            assertIs<TransferResult.Success>(
+                db.exchange.transfer(
+                    TransferRequest(HashCode.rand(), from, ExchangeUrl("http://localhost"), ShortHashCode.rand(), customerPayto),
+                    "exchange", time
+                )
+            )
+        }
+        assertNbTx(38)
+        assertNbCashout(5)
+        assertNbBankTx(5)
+        assertNbWithdrawal(13)
+        assertNbIncoming(5)
+        assertNbOutgoing(4)
+
+        // Check soft delete
+        conn.execSQLUpdate("UPDATE bank_accounts SET balance = (0, 0)::taler_amount")
+        for (account in listOf("old_account", "recent_account")) {
+            client.deleteA("/accounts/$account").assertNoContent()
+        }
+        assertNbAccount(6)
+
+        db.gc.collect(
+            Instant.now(),
+            abortAfter,
+            cleanAfter,
+            deleteAfter
+        )
+        // Check hard delete
+        assertNbAccount(5)
+        assertNbTokens(1)
+        assertNbTan(1)
+        assertNbTx(24)
+        assertNbCashout(3)
+        assertNbBankTx(3)
+        assertNbWithdrawal(4)
+        assertNbIncoming(3)
+        assertNbOutgoing(3)
+    }}
+}
\ No newline at end of file
diff --git a/bank/src/test/kotlin/helpers.kt b/bank/src/test/kotlin/helpers.kt
index d78817b6..bb720197 100644
--- a/bank/src/test/kotlin/helpers.kt
+++ b/bank/src/test/kotlin/helpers.kt
@@ -481,12 +481,6 @@ fun HttpRequestBuilder.pwAuth(username: String? = null) {
 
 /* ----- Random data generation ----- */
 
-fun randBase32Crockford(length: Int) = Base32Crockford.encode(randBytes(length))
-
-fun randIncomingSubject(reservePub: EddsaPublicKey): String {
-    return "$reservePub"
-}
-
-fun randOutgoingSubject(wtid: ShortHashCode, url: ExchangeUrl): String {
-    return "$wtid $url"
-}
\ No newline at end of file
+fun randBase32Crockford(length: Int) = Base32Crockford.encode(ByteArray(length).rand())
+fun randIncomingSubject(reservePub: EddsaPublicKey): String = "$reservePub"
+fun randOutgoingSubject(wtid: ShortHashCode, url: ExchangeUrl): String = "$wtid $url"
\ No newline at end of file
diff --git a/common/src/main/kotlin/TalerCommon.kt b/common/src/main/kotlin/TalerCommon.kt
index aff68ee3..7d561c5c 100644
--- a/common/src/main/kotlin/TalerCommon.kt
+++ b/common/src/main/kotlin/TalerCommon.kt
@@ -351,7 +351,7 @@ class Base32Crockford32B {
     }
 
     companion object {
-        fun rand(): Base32Crockford32B = Base32Crockford32B(randBytes(32))
+        fun rand(): Base32Crockford32B = Base32Crockford32B(ByteArray(32).rand())
     }
 }
 
@@ -405,7 +405,7 @@ class Base32Crockford64B {
     }
 
     companion object {
-        fun rand(): Base32Crockford64B = Base32Crockford64B(randBytes(64))
+        fun rand(): Base32Crockford64B = Base32Crockford64B(ByteArray(64).rand())
     }
 }
 
diff --git a/common/src/main/kotlin/helpers.kt b/common/src/main/kotlin/helpers.kt
index 2eba2d16..895f82ad 100644
--- a/common/src/main/kotlin/helpers.kt
+++ b/common/src/main/kotlin/helpers.kt
@@ -27,6 +27,7 @@ import java.util.zip.ZipInputStream
 import java.io.FilterInputStream
 import java.io.InputStream
 import java.io.ByteArrayOutputStream
+import kotlin.random.Random
 
 fun getQueryParam(uriQueryString: String, param: String): String? {
     // TODO replace with ktor API ?
@@ -56,6 +57,10 @@ fun BigInteger.encodeBase64(): String = this.toByteArray().encodeBase64()
 
 /* ----- ByteArray ----- */
 
+fun ByteArray.rand(): ByteArray {
+    Random.nextBytes(this)
+    return this
+}
 fun ByteArray.encodeHex(): String = HexFormat.of().formatHex(this)
 fun ByteArray.encodeUpHex(): String = HexFormat.of().withUpperCase().formatHex(this)
 fun ByteArray.encodeBase64(): String = Base64.getEncoder().encodeToString(this)
diff --git a/common/src/main/kotlin/random.kt b/common/src/main/kotlin/random.kt
deleted file mode 100644
index d939ab80..00000000
--- a/common/src/main/kotlin/random.kt
+++ /dev/null
@@ -1,28 +0,0 @@
-/*
- * This file is part of LibEuFin.
- * Copyright (C) 2024 Taler Systems S.A.
- *
- * LibEuFin is free software; you can redistribute it and/or modify
- * it under the terms of the GNU Affero General Public License as
- * published by the Free Software Foundation; either version 3, or
- * (at your option) any later version.
- *
- * LibEuFin 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 Affero General
- * Public License for more details.
- *
- * You should have received a copy of the GNU Affero General Public
- * License along with LibEuFin; see the file COPYING.  If not, see
- * <http://www.gnu.org/licenses/>
- */
-
-package tech.libeufin.common
-
-import kotlin.random.Random
-
-fun randBytes(length: Int): ByteArray {
-    val bytes = ByteArray(length)
-    Random.nextBytes(bytes)
-    return bytes
-}
\ No newline at end of file