119 lines
4.6 KiB
Go
119 lines
4.6 KiB
Go
package repository
|
|
|
|
import (
|
|
"context"
|
|
"fmt"
|
|
"time"
|
|
|
|
"github.com/google/uuid"
|
|
"gorm.io/gorm"
|
|
)
|
|
|
|
// DueLot is a lot whose expiry has passed and that still holds something.
|
|
type DueLot struct {
|
|
ID uuid.UUID
|
|
CustomerID uuid.UUID
|
|
Currency string
|
|
Remaining int64
|
|
ExpiresAt time.Time
|
|
// The description of the row that created the lot, for the EXPIRE row's.
|
|
SourceDescription string
|
|
}
|
|
|
|
// UpcomingExpiry is how much of a customer's balance expires on one day.
|
|
type UpcomingExpiry struct {
|
|
CustomerID uuid.UUID
|
|
// A calendar date in walletDisplayTimeZone, formatted YYYY-MM-DD.
|
|
Date string
|
|
Amount int64
|
|
}
|
|
|
|
// WalletExpiryRepository finds what the expiry job has to do (docs/prd-point-coin.md
|
|
// F12). Balances only change through WalletProcessor.
|
|
type WalletExpiryRepository interface {
|
|
// ListDueLots returns up to limit lots due at asOf, the longest overdue first. It
|
|
// takes no lock: locking a lot before its wallet would deadlock against payments,
|
|
// which lock the wallet first. WalletProcessor.ExpireLot locks and reads again.
|
|
ListDueLots(ctx context.Context, asOf time.Time, limit int) ([]DueLot, error)
|
|
|
|
// OrganizationsWithUpcomingExpiry lists the organizations that have balance
|
|
// expiring after asOf.
|
|
OrganizationsWithUpcomingExpiry(ctx context.Context, asOf time.Time) ([]uuid.UUID, error)
|
|
// UpcomingUnreminded sums, per customer and expiry day, the balance of one currency
|
|
// of an organization expiring after asOf and up to until, leaving out the days the
|
|
// customer has already been reminded of.
|
|
UpcomingUnreminded(ctx context.Context, organizationID uuid.UUID, currency string, asOf, until time.Time) ([]UpcomingExpiry, error)
|
|
// MarkReminded records a reminder, and reports false when it was already recorded,
|
|
// by this run or another.
|
|
MarkReminded(ctx context.Context, reminder UpcomingExpiry, currency string) (bool, error)
|
|
}
|
|
|
|
type walletExpiryRepository struct {
|
|
db *gorm.DB
|
|
}
|
|
|
|
func NewWalletExpiryRepository(db *gorm.DB) WalletExpiryRepository {
|
|
return &walletExpiryRepository{db: db}
|
|
}
|
|
|
|
func (r *walletExpiryRepository) ListDueLots(ctx context.Context, asOf time.Time, limit int) ([]DueLot, error) {
|
|
var lots []DueLot
|
|
err := DBFromContext(ctx, r.db).WithContext(ctx).Raw(`
|
|
SELECT l.id, l.customer_id, l.currency, l.remaining_amount AS remaining, l.expires_at,
|
|
t.description AS source_description
|
|
FROM wallet_lots l
|
|
JOIN wallet_transactions t ON t.id = l.source_transaction_id
|
|
WHERE l.remaining_amount > 0 AND l.expires_at <= ?
|
|
ORDER BY l.expires_at, l.id
|
|
LIMIT ?`, asOf, limit).Scan(&lots).Error
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to list due wallet lots: %w", err)
|
|
}
|
|
return lots, nil
|
|
}
|
|
|
|
func (r *walletExpiryRepository) OrganizationsWithUpcomingExpiry(ctx context.Context, asOf time.Time) ([]uuid.UUID, error) {
|
|
var ids []uuid.UUID
|
|
err := DBFromContext(ctx, r.db).WithContext(ctx).Raw(`
|
|
SELECT DISTINCT organization_id FROM wallet_lots
|
|
WHERE remaining_amount > 0 AND expires_at > ?`, asOf).Scan(&ids).Error
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to list organizations with expiring balances: %w", err)
|
|
}
|
|
return ids, nil
|
|
}
|
|
|
|
func (r *walletExpiryRepository) UpcomingUnreminded(ctx context.Context, organizationID uuid.UUID, currency string, asOf, until time.Time) ([]UpcomingExpiry, error) {
|
|
var rows []UpcomingExpiry
|
|
err := DBFromContext(ctx, r.db).WithContext(ctx).Raw(`
|
|
WITH by_day AS (
|
|
SELECT customer_id, (expires_at AT TIME ZONE ?)::date AS day, SUM(remaining_amount) AS amount
|
|
FROM wallet_lots
|
|
WHERE organization_id = ? AND currency = ? AND remaining_amount > 0
|
|
AND expires_at > ? AND expires_at <= ?
|
|
GROUP BY customer_id, day
|
|
)
|
|
SELECT d.customer_id, to_char(d.day, 'YYYY-MM-DD') AS date, d.amount
|
|
FROM by_day d
|
|
LEFT JOIN wallet_expiry_reminders w
|
|
ON w.customer_id = d.customer_id AND w.currency = ? AND w.expiry_date = d.day
|
|
WHERE w.customer_id IS NULL
|
|
ORDER BY d.day, d.customer_id`,
|
|
walletDisplayTimeZone, organizationID, currency, asOf, until, currency).Scan(&rows).Error
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to list upcoming expiry: %w", err)
|
|
}
|
|
return rows, nil
|
|
}
|
|
|
|
func (r *walletExpiryRepository) MarkReminded(ctx context.Context, reminder UpcomingExpiry, currency string) (bool, error) {
|
|
res := DBFromContext(ctx, r.db).WithContext(ctx).Exec(`
|
|
INSERT INTO wallet_expiry_reminders (customer_id, currency, expiry_date, amount)
|
|
VALUES (?, ?, ?::date, ?)
|
|
ON CONFLICT DO NOTHING`, reminder.CustomerID, currency, reminder.Date, reminder.Amount)
|
|
if res.Error != nil {
|
|
return false, fmt.Errorf("failed to record expiry reminder: %w", res.Error)
|
|
}
|
|
return res.RowsAffected == 1, nil
|
|
}
|