apskel-pos-backend/internal/repository/wallet_reconciliation_repository.go

package repository

import (
	"context"
	"fmt"

	"github.com/google/uuid"
	"gorm.io/gorm"
)

// The reconciliation checks of docs/prd-point-coin.md §7.5.
const (
	// Wallet balance = SUM(amount) of the customer's ledger rows, per currency.
	WalletCheckBalanceVsLedger = "BALANCE_VS_LEDGER"
	// Wallet balance = SUM(remaining_amount) of the customer's lots, per currency.
	WalletCheckBalanceVsLots = "BALANCE_VS_LOTS"
	// For every lot: original_amount - SUM(allocations) = remaining_amount.
	WalletCheckLotVsAllocations = "LOT_VS_ALLOCATIONS"
	// For every deduction: SUM(allocations) = |amount|.
	WalletCheckDebitVsAllocations = "DEBIT_VS_ALLOCATIONS"
	// For every addition: SUM(original_amount) of the lots it created = amount. Not
	// listed in §7.5, but the engine keeps it and the other checks rely on it.
	WalletCheckCreditVsLots = "CREDIT_VS_LOTS"
)

// WalletDiscrepancy is one place where the wallet tables disagree with each other.
type WalletDiscrepancy struct {
	Check          string
	OrganizationID uuid.UUID
	CustomerID     uuid.UUID
	Currency       string
	// The lot or ledger row the check is about. Nil for the per-wallet checks.
	ObjectID *uuid.UUID
	Expected int64
	Actual   int64
}

// WalletReconciliationRepository runs the §7.5 checks across every wallet.
type WalletReconciliationRepository interface {
	// FindDiscrepancies returns every discrepancy, at most limit per check, so one
	// systematic bug cannot produce an unbounded report.
	FindDiscrepancies(ctx context.Context, limit int) ([]WalletDiscrepancy, error)
}

type walletReconciliationRepository struct {
	db *gorm.DB
}

func NewWalletReconciliationRepository(db *gorm.DB) WalletReconciliationRepository {
	return &walletReconciliationRepository{db: db}
}

// Each query returns check, organization_id, customer_id, currency, object_id,
// expected and actual. Aggregates are joined rather than correlated, so each check is
// a handful of scans however many customers there are.
var walletReconciliationQueries = []struct {
	check string
	sql   string
}{
	{WalletCheckBalanceVsLedger, `
		WITH ledger AS (
			SELECT customer_id, currency, MAX(organization_id::text) AS organization_id, SUM(amount) AS total
			FROM wallet_transactions GROUP BY customer_id, currency
		), balances AS (
			SELECT customer_id, organization_id::text AS organization_id, 'POINT' AS currency, point_balance AS balance FROM customer_wallets
			UNION ALL
			SELECT customer_id, organization_id::text, 'COIN', coin_balance FROM customer_wallets
		)
		SELECT COALESCE(b.organization_id, l.organization_id) AS organization_id,
			COALESCE(b.customer_id, l.customer_id)::text AS customer_id,
			COALESCE(b.currency, l.currency) AS currency,
			NULL AS object_id,
			COALESCE(l.total, 0) AS expected,
			COALESCE(b.balance, 0) AS actual
		FROM balances b
		FULL JOIN ledger l ON l.customer_id = b.customer_id AND l.currency = b.currency
		WHERE COALESCE(b.balance, 0) <> COALESCE(l.total, 0)
		LIMIT ?`},
	{WalletCheckBalanceVsLots, `
		WITH lots AS (
			SELECT customer_id, currency, MAX(organization_id::text) AS organization_id, SUM(remaining_amount) AS total
			FROM wallet_lots GROUP BY customer_id, currency
		), balances AS (
			SELECT customer_id, organization_id::text AS organization_id, 'POINT' AS currency, point_balance AS balance FROM customer_wallets
			UNION ALL
			SELECT customer_id, organization_id::text, 'COIN', coin_balance FROM customer_wallets
		)
		SELECT COALESCE(b.organization_id, l.organization_id) AS organization_id,
			COALESCE(b.customer_id, l.customer_id)::text AS customer_id,
			COALESCE(b.currency, l.currency) AS currency,
			NULL AS object_id,
			COALESCE(l.total, 0) AS expected,
			COALESCE(b.balance, 0) AS actual
		FROM balances b
		FULL JOIN lots l ON l.customer_id = b.customer_id AND l.currency = b.currency
		WHERE COALESCE(b.balance, 0) <> COALESCE(l.total, 0)
		LIMIT ?`},
	{WalletCheckLotVsAllocations, `
		SELECT l.organization_id::text AS organization_id, l.customer_id::text AS customer_id, l.currency,
			l.id::text AS object_id,
			l.original_amount - COALESCE(a.total, 0) AS expected,
			l.remaining_amount AS actual
		FROM wallet_lots l
		LEFT JOIN (SELECT lot_id, SUM(amount) AS total FROM wallet_lot_allocations GROUP BY lot_id) a ON a.lot_id = l.id
		WHERE l.original_amount - COALESCE(a.total, 0) <> l.remaining_amount
		LIMIT ?`},
	{WalletCheckDebitVsAllocations, `
		SELECT t.organization_id::text AS organization_id, t.customer_id::text AS customer_id, t.currency,
			t.id::text AS object_id,
			-t.amount AS expected,
			COALESCE(a.total, 0) AS actual
		FROM wallet_transactions t
		LEFT JOIN (SELECT transaction_id, SUM(amount) AS total FROM wallet_lot_allocations GROUP BY transaction_id) a ON a.transaction_id = t.id
		WHERE t.amount < 0 AND -t.amount <> COALESCE(a.total, 0)
		LIMIT ?`},
	{WalletCheckCreditVsLots, `
		SELECT t.organization_id::text AS organization_id, t.customer_id::text AS customer_id, t.currency,
			t.id::text AS object_id,
			t.amount AS expected,
			COALESCE(l.total, 0) AS actual
		FROM wallet_transactions t
		LEFT JOIN (SELECT source_transaction_id, SUM(original_amount) AS total FROM wallet_lots GROUP BY source_transaction_id) l ON l.source_transaction_id = t.id
		WHERE t.amount > 0 AND t.amount <> COALESCE(l.total, 0)
		LIMIT ?`},
}

func (r *walletReconciliationRepository) FindDiscrepancies(ctx context.Context, limit int) ([]WalletDiscrepancy, error) {
	db := DBFromContext(ctx, r.db).WithContext(ctx)
	var found []WalletDiscrepancy
	for _, q := range walletReconciliationQueries {
		var rows []struct {
			OrganizationID string
			CustomerID     string
			Currency       string
			ObjectID       *string
			Expected       int64
			Actual         int64
		}
		if err := db.Raw(q.sql, limit).Scan(&rows).Error; err != nil {
			return nil, fmt.Errorf("wallet reconciliation check %s failed: %w", q.check, err)
		}
		for _, row := range rows {
			d := WalletDiscrepancy{
				Check:    q.check,
				Currency: row.Currency,
				Expected: row.Expected,
				Actual:   row.Actual,
			}
			d.OrganizationID, _ = uuid.Parse(row.OrganizationID)
			d.CustomerID, _ = uuid.Parse(row.CustomerID)
			if row.ObjectID != nil {
				if id, err := uuid.Parse(*row.ObjectID); err == nil {
					d.ObjectID = &id
				}
			}
			found = append(found, d)
		}
	}
	return found, nil
}