/opt/canhelp/apps/api/src/lib
Edit: /opt/canhelp/apps/api/src/lib/balance.ts (3506B)
import { desc, eq, sql } from 'drizzle-orm'
import { balanceTransactions, payments, users } from '@canhelp/db'
type DbExecutor = any
type BalanceDirection = 'credit' | 'debit'
type BalanceKind = 'topup' | 'plan_purchase' | 'admin_topup' | 'admin_adjustment'
type ApplyBalanceChangeInput = {
userId: string
amount: number
direction: BalanceDirection
kind: BalanceKind
currency?: string
description?: string | null
adminUserId?: string | null
paymentId?: string | null
metadata?: Record
| null
}
function toFixedAmount(value: number) {
return value.toFixed(2)
}
export async function applyBalanceChange(executor: DbExecutor, input: ApplyBalanceChangeInput) {
const amount = Number(input.amount)
if (!Number.isFinite(amount) || amount <= 0) {
throw new Error('Invalid balance amount')
}
const signedAmount = input.direction === 'credit' ? amount : -amount
const signedAmountSql = toFixedAmount(signedAmount)
const updateResult = await executor.execute(sql`
UPDATE users
SET balance = balance + ${signedAmountSql}::numeric,
updated_at = now()
WHERE id = ${input.userId}
AND balance + ${signedAmountSql}::numeric >= 0
RETURNING
(balance - ${signedAmountSql}::numeric)::text AS balance_before,
balance::text AS balance_after
`)
const row = (updateResult as { rows?: Array<{ balance_before: string; balance_after: string }> }).rows?.[0]
if (!row) {
throw new Error(input.direction === 'debit' ? 'Insufficient balance' : 'User not found')
}
const [transaction] = await executor
.insert(balanceTransactions)
.values({
userId: input.userId,
adminUserId: input.adminUserId ?? null,
paymentId: input.paymentId ?? null,
kind: input.kind,
direction: input.direction,
amount: toFixedAmount(amount),
balanceBefore: row.balance_before,
balanceAfter: row.balance_after,
currency: input.currency ?? 'EUR',
description: input.description ?? null,
metadata: input.metadata ?? null,
})
.returning()
return {
transaction,
balanceBefore: Number(row.balance_before),
balanceAfter: Number(row.balance_after),
}
}
export async function getUserBalanceHistory(executor: DbExecutor, userId: string, limit = 50) {
return executor
.select({
id: balanceTransactions.id,
kind: balanceTransactions.kind,
direction: balanceTransactions.direction,
amount: balanceTransactions.amount,
balanceBefore: balanceTransactions.balanceBefore,
balanceAfter: balanceTransactions.balanceAfter,
currency: balanceTransactions.currency,
description: balanceTransactions.description,
metadata: balanceTransactions.metadata,
createdAt: balanceTransactions.createdAt,
paymentId: balanceTransactions.paymentId,
adminUserId: balanceTransactions.adminUserId,
})
.from(balanceTransactions)
.where(eq(balanceTransactions.userId, userId))
.orderBy(desc(balanceTransactions.createdAt))
.limit(limit)
}
export async function getPaymentByOrderRef(executor: DbExecutor, orderRef: string) {
const [payment] = await executor
.select()
.from(payments)
.where(eq(payments.orderRef, orderRef))
.limit(1)
return payment ?? null
}
export async function getUserById(executor: DbExecutor, userId: string) {
const [user] = await executor
.select()
.from(users)
.where(eq(users.id, userId))
.limit(1)
return user ?? null
}