import {
ERepaymentReason,
ESaleStatus,
ESaleType,
ESaleVersion,
} from "@lendtable/common/dtos/SaleDto";
import { TTransaction, withTransaction } from "../utils/mysql";
import {
calculatePayPeriodBalanceEspp,
calculatePayPeriodBalanceK401,
} from "@lendtable/common/config/dynamic-payouts";
import { isPaidOut } from "@lendtable/common/config/sale";
import { EBalanceContributionStatus } from "@lendtable/common/dtos/BalanceContributionDto";
import { EBalanceSnapshotType } from "@lendtable/common/dtos/BalanceSnapshotDto";
import { EPayoutTransactionStatus } from "@lendtable/common/dtos/PayoutDto";
import { EPlaidConnectionType } from "@lendtable/common/dtos/PlaidConnectionDto";
import { EStatusUpdateType } from "@lendtable/common/dtos/StatusUpdateDto";
import { PayPeriodStatus } from "@prisma/client";
import { Customer } from "../entities/Customer";
import { prisma } from "../prisma/prisma";
import BalanceContributionRepository from "../repositories/BalanceContributionRepository";
import BalanceSnapshotRepository from "../repositories/BalanceSnapshotRepository";
import CustomerRepository from "../repositories/CustomerRepository";
import PayoutRepository from "../repositories/PayoutRepository";
import PlaidConnectionRepository from "../repositories/PlaidConnectionRepository";
import SaleRepository from "../repositories/SaleRepository";
import HotShot from "../utils/hot-shot";
import { logger } from "../utils/logger";
import { REPAYMENTS_PRODUCT_ID, REPAYMENT_PLAN } from "../utils/stripe";
import { isClonedCustomer } from "../utils/user";
import PlaidService from "./PlaidService";
import SaleStatusService from "./SaleStatusService";
import SlackService from "./SlackService";
import StripeService from "./StripeService";
class BalancesService {
// Recalculates the customer's lendtable balance
public static async updateLendtableBalance(
customer: Customer,
t?: TTransaction
) {
// Lendtable balance goes UP as customers get payouts, multiplied by the interest rate for each sale
// Lendtable balance goes DOWN when the customer pays us back, or gets credit for special actions, i.e. we subtract the total (confirmed) balance contributions, in the order which they were confirmed (updated)
// Combine the customer's sales and payouts, only choosing sales for which the contract is accepted and payouts that have not failed
const sales = await SaleRepository.getAllByCustomerId(
customer.id,
"ASC",
undefined,
t
);
const payouts = await PayoutRepository.getAllByCustomerId(
customer.id,
"ASC",
undefined,
t
);
const payoutNonDynamic = payouts.filter((p) => {
return p.payPeriodId === null;
});
const payoutsNotFailed = payoutNonDynamic.filter((p) => {
return (
p.payoutTransactionStatus === EPayoutTransactionStatus.Pending ||
p.payoutTransactionStatus === EPayoutTransactionStatus.Successful
);
});
const payPeriods = await prisma.payPeriod.findMany({
// OK that this isn't in the same transaction,
// because we never create payPeriods in this transaction
// payPeriods are only created in Prisma
where: {
customerId: customer.id,
status: PayPeriodStatus.APPROVED,
},
orderBy: {
payDateProjected: "desc",
},
});
const salesWithPayoutsAndPayPeriods = sales.map((sale) => ({
sale,
payouts: payoutsNotFailed.filter((p) => p.saleId === sale.id),
payPeriods: payPeriods.filter((pp) => {
return pp.esppSaleId === sale.id || pp.k401SaleId === sale.id;
}),
balanceContributions: [],
}));
// Go over the combined sales and sum the payouts
const salesWithPayoutsSum = salesWithPayoutsAndPayPeriods.map((sp) => ({
...sp,
payoutsSumCents: sp.payouts.reduce(
(acc, p) => acc + p.amountCents + p.subscriptionFromPayoutCents,
0
),
dynamicPayoutsSumCents: sp.payPeriods.reduce((acc, pp) => {
if (sp.sale.saleType === ESaleType.K401) {
return acc + (pp.k401PayoutAmount ?? 0);
} else if (sp.sale.saleType === ESaleType.Espp) {
return acc + (pp.esppPayoutAmount ?? 0);
} else {
throw new Error(`Invalid saleType ${sp.sale.id}`);
}
}, 0),
}));
// Apply the interest rate to the sum of the payouts
const salesWithPayoutsSumInterest = salesWithPayoutsSum.map((sp) => ({
...sp,
payoutsSumWithInterestCents: Math.floor(
sp.payoutsSumCents * (sp.sale.interestRatio() ?? 1)
),
payPeriodSumWithOurShareCents: sp.payPeriods.reduce((acc, pp) => {
const payoutSumForPeriod = payouts
.filter((p) => {
// conditional is different than above because we include scheduled payouts
return (
p.payPeriodId === pp.id &&
p.payoutTransactionStatus !== EPayoutTransactionStatus.Failed
);
})
.reduce((acc, p) => acc + p.amountCents, 0);
if (payoutSumForPeriod === 0) {
// If all payouts for this payPeriod have failed, then the payPeriod will not count towards balance
return acc;
}
const shouldBePaidAmount =
(pp.esppPayoutAmount ?? 0) +
(pp.k401PayoutAmount ?? 0) -
(pp.subscriptionFee ?? 0);
if (payoutSumForPeriod !== shouldBePaidAmount) {
// Edge case where the payPeriod has multiple payouts, and there is both succesful and failed payouts and we have not resent the failed payouts
// We need to manually reduce the values on the payPeriod
if (!isClonedCustomer(customer)) {
void SlackService.postMessage({
text:
`Pay Period ${pp.payDateProjectedStr} for customer ${customer.id} has multiple payouts, and there are both succesful and failed payouts.\n` +
`The sum of payouts sent doesn't not match payoutAmounts stored on the payPeriod. We need to manually adjust.\n` +
`Should be paid: $${shouldBePaidAmount / 100}, actual paid: $${
payoutSumForPeriod / 100
}.`,
channel: SlackService.DYNAMIC_CHANNEL,
});
}
}
if (sp.sale.saleType === ESaleType.K401) {
// Use pp.k401OurShare once backfilled
const taxRate = sp.payPeriods[0].taxRate; // use the taxRate from the most recent approved payPeriod
return (
acc +
calculatePayPeriodBalanceK401({ payPeriod: pp, taxRate }).balance
);
}
if (sp.sale.saleType === ESaleType.Espp) {
// Use pp.esppOurShare once backfilled
return acc + calculatePayPeriodBalanceEspp(pp).balance;
}
throw new Error("Invalid saleType");
}, 0),
}));
// Mark the payouts as being completed when enough money is sent to cover the loan amount, with a leeway of 10 USD
for (const { sale, payoutsSumCents } of salesWithPayoutsSumInterest) {
if (
sale.saleStatus !== ESaleStatus.PayoutScheduled &&
sale.saleStatus !== ESaleStatus.PayoutPaused
) {
continue;
}
// Only legacy sales can move to PayoutComplete in this way
if (
sale.saleVersion !== ESaleVersion._20220428 &&
sale.saleVersion !== ESaleVersion._20220505
) {
continue;
}
if (
payoutsSumCents > 0 &&
payoutsSumCents >= (sale.loanCents() ?? 0) - 10 * 100
) {
// Update SaleStatus
const oldStatus = sale.saleStatus;
sale.saleStatus = ESaleStatus.PayoutComplete;
await SaleRepository.updateSaleStatus(sale.id, sale.saleStatus, t);
await SaleStatusService.onStatusChanged(
sale,
customer,
{
oldStatus,
statusUpdateType: EStatusUpdateType.PayoutsCompleted,
recursionDepth: 0,
},
t
);
sale.repaymentReason = ERepaymentReason.ContractTermEnded;
await SaleRepository.updateRepaymentReason(
sale.id,
sale.repaymentReason,
t
);
}
}
// To start off, make the balances equal to the payouts sum with interest
const salesWithBalances = salesWithPayoutsSumInterest.map((sp) => ({
...sp,
balanceCents:
sp.payoutsSumWithInterestCents + sp.payPeriodSumWithOurShareCents,
}));
// Apply balance contributions, draining the balances
const balanceContributions =
await BalanceContributionRepository.getAllByCustomerIdStatusOrderUpdated(
customer.id,
EBalanceContributionStatus.Confirmed,
"ASC",
undefined,
t
);
// Apply BalanceContributions that have a SaleId first
// Refunds and disputes all have saleId set
for (const balanceContribution of balanceContributions.filter(
(b) => b.saleId !== null
)) {
const sb = salesWithBalances.find(
(s) => s.sale.id === balanceContribution.saleId
);
// TODO how should we handle the case where the sale is missing?
if (!sb) {
continue;
}
sb.balanceCents -= balanceContribution.amountCents;
}
// Apply BalanceContributions without a SaleId second
for (const balanceContribution of balanceContributions.filter(
(b) => b.saleId === null
)) {
const sb = salesWithBalances.find((sb) => sb.balanceCents > 0);
if (!sb) {
continue;
}
sb.balanceCents -= balanceContribution.amountCents;
// Update the BalanceContribution's SaleId
balanceContribution.saleId = sb.sale.id;
await BalanceContributionRepository.updateSaleId(
balanceContribution.id,
balanceContribution.saleId,
t
);
}
// Sum the total lendtable balance
const lendtableBalanceCents = salesWithBalances.reduce(
(acc, sb) => acc + sb.balanceCents,
0
);
// Sum total 401(k) and ESPP balances
const lendtableBalanceK401Cents = salesWithBalances
.filter((sb) => sb.sale.saleType === ESaleType.K401)
.reduce((acc, sb) => acc + sb.balanceCents, 0);
const lendtableBalanceEsppCents = salesWithBalances
.filter((sb) => sb.sale.saleType === ESaleType.Espp)
.reduce((acc, sb) => acc + sb.balanceCents, 0);
// Save overall Customer balances
customer.lendtableBalanceCents = lendtableBalanceCents;
customer.lendtableBalanceK401Cents = lendtableBalanceK401Cents;
customer.lendtableBalanceEsppCents = lendtableBalanceEsppCents;
await CustomerRepository.updateLendtableBalances(
customer.id,
{
lendtableBalanceCents,
lendtableBalanceK401Cents,
lendtableBalanceEsppCents,
},
t
);
// Save individual Sale balances
for (const {
sale,
balanceCents,
payoutsSumCents,
dynamicPayoutsSumCents,
} of salesWithBalances) {
sale.balanceCents = balanceCents;
sale.payoutSumCents = payoutsSumCents + dynamicPayoutsSumCents;
}
await Promise.all([
...salesWithBalances.map((sb) =>
SaleRepository.updateBalanceCents(sb.sale.id, sb.sale.balanceCents, t)
),
...salesWithBalances.map((sb) =>
SaleRepository.updatePayoutSumCents(
sb.sale.id,
sb.sale.payoutSumCents,
t
)
),
]);
// Sales that have had been paid out and were drained to a balance of zero are changed to RepaymentComplete
const salesToComplete = salesWithBalances.filter(
(sb) => isPaidOut(sb.sale) && sb.balanceCents <= 10
);
for (const { sale } of salesToComplete) {
if (sale.saleStatus === ESaleStatus.RepaymentComplete) {
continue;
}
const customerRepaymentPlans =
await StripeService.getCustomerRepaymentSubscriptions(customer);
if (customerRepaymentPlans) {
const saleRepaymentPlan = customerRepaymentPlans?.find(
(rp) => rp.saleId === sale.id
);
if (saleRepaymentPlan && saleRepaymentPlan.planStatus !== "canceled") {
await StripeService.cancelSubscription({
subscriptionId: saleRepaymentPlan.id,
stripeSubscriptionProductId: REPAYMENTS_PRODUCT_ID,
stripeSubscriptionTypeMedata: REPAYMENT_PLAN,
stripeSubscriptionSaleIdMetadata: sale.id,
});
}
}
// Update SaleStatus
const oldStatus = sale.saleStatus;
sale.saleStatus = ESaleStatus.RepaymentComplete;
await SaleRepository.updateSaleStatus(sale.id, sale.saleStatus, t);
await SaleStatusService.onStatusChanged(
sale,
customer,
{
oldStatus,
statusUpdateType: EStatusUpdateType.Repaid,
recursionDepth: 0,
},
t
);
}
// Remove preapproved status from sales with repaid parents.
if (salesToComplete) {
await this.removePreapprovedFromEsppSaleIfParentAndSiblingsRepaid(
customer.id,
t
);
}
}
// Remove preapproved status from child sales if parent sale and sibling sales have been repaid
// Call after some sales are repaid
public static async removePreapprovedFromEsppSaleIfParentAndSiblingsRepaid(
customerId: string,
t?: TTransaction
) {
const sales = await SaleRepository.getAllByCustomerId(
customerId,
undefined,
undefined,
t
);
const preapprovedSales = sales.filter(
(sale) => sale.saleType === ESaleType.Espp && sale.preapproved
);
for (const childSale of preapprovedSales) {
// check the parent sale and sibling sales
if (!childSale.parentId) {
continue;
}
const targetSales = await SaleRepository.getAllByParentId(
childSale.parentId,
undefined,
undefined,
t
);
const allRepaid = targetSales.every((sale) => {
return (
sale.preapproved || sale.saleStatus === ESaleStatus.RepaymentComplete
);
});
if (allRepaid) {
await SaleRepository.updatePreapproved(childSale.id, false, t);
}
}
}
// Recalculates the customer's 401(k) balance
public static async updateK401Balance(customer: Customer, t?: TTransaction) {
// The 401(k) balance is equal to what's in the customer's 401(k) account(s), we use Plaid to get that info
const balance = await PlaidService.getCustomerBalance(
customer,
EPlaidConnectionType.K401,
t
);
if (balance === null) {
return;
}
const balanceCents = Math.round(balance * 100);
// Save balance snapshot to DB but only if it's changed since the last time we got the info
await BalanceSnapshotRepository.createIfChanged(
{
customerId: customer.id,
balanceSnapshotType: EBalanceSnapshotType.K401,
amountCents: balanceCents,
},
t
);
}
// Recalculates the customer's ESPP balance
public static async updateEsppBalance(customer: Customer, t?: TTransaction) {
// The ESPP balance is equal to what's in the customer's ESPP account(s), we use Plaid to get that info
const balance = await PlaidService.getCustomerBalance(
customer,
EPlaidConnectionType.Espp,
t
);
if (balance === null) {
return;
}
const balanceCents = Math.round(balance * 100);
// Save balance snapshot to DB but only if it's changed since the last time we got the info
await BalanceSnapshotRepository.createIfChanged(
{
customerId: customer.id,
balanceSnapshotType: EBalanceSnapshotType.Espp,
amountCents: balanceCents,
},
t
);
}
// Recalculates the customer's checking account balance
public static async updateCheckingBalance(
customer: Customer,
t?: TTransaction
) {
// The ESPP balance is equal to what's in the customer's checking account(s), we use Plaid to get that info
const balance = await PlaidService.getCustomerBalance(
customer,
EPlaidConnectionType.Checking,
t
);
if (balance === null) {
return;
}
const balanceCents = Math.round(balance * 100);
// Save balance snapshot to DB but only if it's changed since the last time we got the info
await BalanceSnapshotRepository.createIfChanged(
{
customerId: customer.id,
balanceSnapshotType: EBalanceSnapshotType.Checking,
amountCents: balanceCents,
},
t
);
}
public static async updateAllLendtableBalances() {
logger.warn("updateAllLendtableBalances - started");
const customers = await CustomerRepository.getAllSignedUp();
for (const { id } of customers) {
try {
await withTransaction(async (t) => {
const customer = await CustomerRepository.getById(id, t);
if (!customer) {
return;
}
await this.updateLendtableBalance(customer, t);
});
} catch (err) {
logger.error("BalancesService.updateAllLendtableBalances", err);
}
}
logger.warn("updateAllLendtableBalances - complete");
}
public static async updateAllK401Balances() {
logger.info("updateAllK401Balances - started");
const connectionsK401 = await PlaidConnectionRepository.getAllByType(
EPlaidConnectionType.K401
);
for (const { id } of connectionsK401) {
HotShot.increment("BalancesService.updateAllK4O1Balances", {
service: "cronjob",
api: "plaid",
});
try {
await withTransaction(async (t) => {
const connection = await PlaidConnectionRepository.getById(id, t);
if (!connection) {
return;
}
const customer = await CustomerRepository.getById(
connection.customerId,
t
);
if (!customer) {
return;
}
await this.updateK401Balance(customer, t);
});
} catch (err) {
logger.warn("BalancesService.updateAllK401Balances", err);
HotShot.increment("BalancesService.updateAllK4O1Balances_error", {
service: "cronjob",
api: "plaid",
});
HotShot.event(
"BalancesService.updateAllK401Balances_error",
`Error updating 401K balances for Plaid Connection: ${id}`,
{
aggregation_key: "cronjob",
date_happened: new Date(),
alert_type: "error",
}
);
}
}
logger.info("updateAllK401Balances - complete");
}
public static async updateAllEsppBalances() {
logger.info("updateAllEsppBalances - started");
const connectionsEspp = await PlaidConnectionRepository.getAllByType(
EPlaidConnectionType.Espp
);
for (const { id } of connectionsEspp) {
HotShot.increment("BalancesService.updateAllEsppBalances", {
service: "cronjob",
api: "plaid",
});
try {
await withTransaction(async (t) => {
const connection = await PlaidConnectionRepository.getById(id, t);
if (!connection) {
return;
}
const customer = await CustomerRepository.getById(
connection.customerId,
t
);
if (!customer) {
return;
}
await this.updateEsppBalance(customer, t);
});
} catch (err) {
logger.warn("BalancesService.updateAllEsppBalances", err);
HotShot.increment("BalancesService.updateAllEsppBalances_error", {
service: "cronjob",
api: "plaid",
});
HotShot.event(
"BalancesService.updateAllEsppBalances_error",
`Error updating ESPP balances for Plaid Connection: ${id}`,
{
aggregation_key: "cronjob",
date_happened: new Date(),
alert_type: "error",
}
);
}
}
logger.info("updateAllEsppBalances - complete");
}
public static async updateAllCheckingBalances() {
logger.info("updateAllCheckingBalances - started");
const connectionsChecking = await PlaidConnectionRepository.getAllByType(
EPlaidConnectionType.Checking
);
for (const { id } of connectionsChecking) {
HotShot.increment("BalancesService.updateAllCheckingBalances", {
service: "cronjob",
api: "plaid",
});
try {
await withTransaction(async (t) => {
const connection = await PlaidConnectionRepository.getById(id, t);
if (!connection) {
return;
}
const customer = await CustomerRepository.getById(
connection.customerId,
t
);
if (!customer) {
return;
}
await this.updateCheckingBalance(customer, t);
});
} catch (err) {
logger.warn("BalancesService.updateAllCheckingBalances", err);
HotShot.increment("BalancesService.updateAllCheckingBalances_error", {
service: "cronjob",
api: "plaid",
});
HotShot.event(
"BalancesService.updateAllCheckingBalances_error",
`Error updating checking balances for Plaid Connection: ${id}`,
{
aggregation_key: "cronjob",
date_happened: new Date(),
alert_type: "error",
}
);
}
}
logger.info("updateAllCheckingBalances - complete");
}
}
export default BalancesService;
