add some scriptsmain

1 files changed, 171 insertions, 0 deletions

diff --git a/internal/billing/ledger.go b/internal/billing/ledger.go
new file mode 100644
index 0000000..28cbe4c
--- /dev/null
+++ b/internal/billing/ledger.go
@@ -0,0 +1,171 @@
+package billing
+
+import (
+	"context"
+	"errors"
+	"fmt"
+	"math"
+	"strings"
+
+	"github.com/jackc/pgx/v5"
+)
+
+func (s *Service) ListAccounts(ctx context.Context, tenantID string) ([]Account, error) {
+	query := `
+		SELECT t.id::text, t.name, COALESCE(w.currency, $1), COALESCE(w.balance_micros, 0),
+		       COALESCE(w.reserved_micros, 0), COALESCE(w.balance_micros - w.reserved_micros, 0),
+		       COALESCE(w.updated_at, t.created_at)
+		FROM tenants t LEFT JOIN tenant_wallets w ON w.tenant_id = t.id`
+	args := []any{s.currency}
+	if strings.TrimSpace(tenantID) != "" {
+		query += ` WHERE t.id=$2`
+		args = append(args, tenantID)
+	}
+	query += ` ORDER BY t.name`
+	rows, err := s.db.Query(ctx, query, args...)
+	if err != nil {
+		return nil, fmt.Errorf("query billing accounts: %w", err)
+	}
+	defer rows.Close()
+	result := make([]Account, 0)
+	for rows.Next() {
+		var item Account
+		if err := rows.Scan(&item.TenantID, &item.TenantName, &item.Currency, &item.BalanceMicros,
+			&item.ReservedMicros, &item.AvailableMicros, &item.UpdatedAt); err != nil {
+			return nil, fmt.Errorf("scan billing account: %w", err)
+		}
+		result = append(result, item)
+	}
+	return result, rows.Err()
+}
+
+func (s *Service) ListLedger(ctx context.Context, tenantID string, limit int) ([]LedgerEntry, error) {
+	if limit < 1 || limit > 500 {
+		limit = 200
+	}
+	query := `
+		SELECT id::text, tenant_id::text, COALESCE(project_id::text, ''), currency, amount_micros,
+		       balance_after_micros, kind, source_type, source_id, description, created_at
+		FROM billing_ledger`
+	args := []any{}
+	if strings.TrimSpace(tenantID) != "" {
+		query += ` WHERE tenant_id = $1 ORDER BY created_at DESC LIMIT $2`
+		args = append(args, tenantID, limit)
+	} else {
+		query += ` ORDER BY created_at DESC LIMIT $1`
+		args = append(args, limit)
+	}
+	rows, err := s.db.Query(ctx, query, args...)
+	if err != nil {
+		return nil, fmt.Errorf("query billing ledger: %w", err)
+	}
+	defer rows.Close()
+	result := make([]LedgerEntry, 0)
+	for rows.Next() {
+		var item LedgerEntry
+		if err := rows.Scan(&item.ID, &item.TenantID, &item.ProjectID, &item.Currency, &item.AmountMicros,
+			&item.BalanceAfterMicros, &item.Kind, &item.SourceType, &item.SourceID,
+			&item.Description, &item.CreatedAt); err != nil {
+			return nil, fmt.Errorf("scan billing ledger: %w", err)
+		}
+		result = append(result, item)
+	}
+	return result, rows.Err()
+}
+
+func (s *Service) AdjustBalance(ctx context.Context, input AdjustmentInput) (LedgerEntry, error) {
+	input.TenantID = strings.TrimSpace(input.TenantID)
+	input.Description = normalizeDescription(input.Description)
+	if input.TenantID == "" || input.AmountMicros == 0 {
+		return LedgerEntry{}, ErrInvalidAmount
+	}
+	tx, err := s.db.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.ReadCommitted})
+	if err != nil {
+		return LedgerEntry{}, fmt.Errorf("begin balance adjustment: %w", err)
+	}
+	defer tx.Rollback(ctx)
+	if _, err := tx.Exec(ctx, `
+		INSERT INTO tenant_wallets (tenant_id, currency) VALUES ($1, $2)
+		ON CONFLICT (tenant_id) DO NOTHING`, input.TenantID, s.currency); err != nil {
+		return LedgerEntry{}, fmt.Errorf("ensure adjustment wallet: %w", err)
+	}
+	var currency string
+	var balance, held int64
+	if err := tx.QueryRow(ctx, `SELECT currency, balance_micros, reserved_micros FROM tenant_wallets WHERE tenant_id = $1 FOR UPDATE`, input.TenantID).Scan(&currency, &balance, &held); err != nil {
+		return LedgerEntry{}, fmt.Errorf("lock adjustment wallet: %w", err)
+	}
+	if currency != s.currency {
+		return LedgerEntry{}, fmt.Errorf("tenant wallet currency %s does not match %s", currency, s.currency)
+	}
+	if input.AmountMicros > 0 && balance > math.MaxInt64-input.AmountMicros {
+		return LedgerEntry{}, ErrInvalidAmount
+	}
+	newBalance := balance + input.AmountMicros
+	if newBalance < held || newBalance < 0 {
+		return LedgerEntry{}, ErrInsufficientBalance
+	}
+	if _, err := tx.Exec(ctx, `UPDATE tenant_wallets SET balance_micros = $2, updated_at = now() WHERE tenant_id = $1`, input.TenantID, newBalance); err != nil {
+		return LedgerEntry{}, fmt.Errorf("apply balance adjustment: %w", err)
+	}
+	sourceID := "adj_" + randomHex(16)
+	var result LedgerEntry
+	err = tx.QueryRow(ctx, `
+		INSERT INTO billing_ledger (tenant_id, currency, amount_micros, balance_after_micros, kind, source_type, source_id, description)
+		VALUES ($1,$2,$3,$4,'adjustment','admin',$5,$6)
+		RETURNING id::text, tenant_id::text, '', currency, amount_micros, balance_after_micros,
+		          kind, source_type, source_id, description, created_at`,
+		input.TenantID, s.currency, input.AmountMicros, newBalance, sourceID, input.Description,
+	).Scan(&result.ID, &result.TenantID, &result.ProjectID, &result.Currency, &result.AmountMicros,
+		&result.BalanceAfterMicros, &result.Kind, &result.SourceType, &result.SourceID,
+		&result.Description, &result.CreatedAt)
+	if err != nil {
+		return LedgerEntry{}, fmt.Errorf("write adjustment ledger entry: %w", err)
+	}
+	if err := tx.Commit(ctx); err != nil {
+		return LedgerEntry{}, fmt.Errorf("commit balance adjustment: %w", err)
+	}
+	return result, nil
+}
+
+func (s *Service) createTopUpOrder(ctx context.Context, input CheckoutInput) (string, int64, error) {
+	if !s.stripeEnabled {
+		return "", 0, ErrStripeDisabled
+	}
+	if strings.TrimSpace(input.TenantID) == "" || input.AmountMinor < s.minTopUpMinor || input.AmountMinor > s.maxTopUpMinor {
+		return "", 0, ErrInvalidAmount
+	}
+	amountMicros, err := minorToMicros(s.currency, input.AmountMinor)
+	if err != nil {
+		return "", 0, err
+	}
+	var orderID string
+	err = s.db.QueryRow(ctx, `
+		INSERT INTO topup_orders (tenant_id, amount_minor, amount_micros, currency)
+		VALUES ($1,$2,$3,$4) RETURNING id::text`, input.TenantID, input.AmountMinor, amountMicros, s.currency,
+	).Scan(&orderID)
+	if err != nil {
+		return "", 0, fmt.Errorf("create top-up order: %w", err)
+	}
+	return orderID, amountMicros, nil
+}
+
+func minorToMicros(currency string, amount int64) (int64, error) {
+	if amount <= 0 {
+		return 0, ErrInvalidAmount
+	}
+	factor := int64(10_000)
+	switch strings.ToLower(currency) {
+	case "bif", "clp", "djf", "gnf", "jpy", "kmf", "krw", "mga", "pyg", "rwf", "ugx", "vnd", "vuv", "xaf", "xof", "xpf":
+		factor = 1_000_000
+	case "bhd", "jod", "kwd", "omr", "tnd":
+		factor = 1_000
+	}
+	if amount > math.MaxInt64/factor {
+		return 0, ErrInvalidAmount
+	}
+	return amount * factor, nil
+}
+
+func isNotFound(err error) bool {
+	return errors.Is(err, pgx.ErrNoRows)
+}