618 lines
25 KiB
C#
618 lines
25 KiB
C#
using System.Text.Json;
|
|
using System.Text.RegularExpressions;
|
|
using Asentrafin.Api.Contracts.Plaid;
|
|
using Asentrafin.Api.Data;
|
|
using Asentrafin.Api.Entities;
|
|
using Microsoft.EntityFrameworkCore;
|
|
|
|
namespace Asentrafin.Api.Services.Plaid;
|
|
|
|
public class PlaidIngestionService(AppDbContext dbContext, IPlaidClient plaidClient) : IPlaidIngestionService
|
|
{
|
|
private static readonly Regex SlugSanitizer = new("[^a-z0-9]+", RegexOptions.Compiled);
|
|
|
|
public async Task<Guid> CreateItemAsync(CreatePlaidItemRequest request, CancellationToken cancellationToken)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(request.AccessToken))
|
|
{
|
|
throw new ArgumentException("AccessToken is required.", nameof(request));
|
|
}
|
|
|
|
var existingItem = request.PlaidItemId is null
|
|
? null
|
|
: await dbContext.PlaidItems
|
|
.SingleOrDefaultAsync(item => item.PlaidItemId == request.PlaidItemId, cancellationToken);
|
|
|
|
if (existingItem is not null)
|
|
{
|
|
existingItem.AccessToken = request.AccessToken;
|
|
existingItem.InstitutionId = request.InstitutionId;
|
|
existingItem.InstitutionName = request.InstitutionName;
|
|
existingItem.UpdatedAt = DateTimeOffset.UtcNow;
|
|
await dbContext.SaveChangesAsync(cancellationToken);
|
|
return existingItem.Id;
|
|
}
|
|
|
|
var item = new PlaidItem
|
|
{
|
|
AccessToken = request.AccessToken,
|
|
PlaidItemId = request.PlaidItemId,
|
|
InstitutionId = request.InstitutionId,
|
|
InstitutionName = request.InstitutionName
|
|
};
|
|
|
|
dbContext.PlaidItems.Add(item);
|
|
await dbContext.SaveChangesAsync(cancellationToken);
|
|
return item.Id;
|
|
}
|
|
|
|
public async Task<IngestionSummary> IngestAccountsAsync(Guid itemId, CancellationToken cancellationToken)
|
|
{
|
|
var item = await GetItemAsync(itemId, cancellationToken);
|
|
using var response = await plaidClient.AccountsGetAsync(item.AccessToken, cancellationToken);
|
|
|
|
var accountsUpserted = await UpsertAccountsAsync(item, response.RootElement, cancellationToken);
|
|
item.LastAccountsSyncedAt = DateTimeOffset.UtcNow;
|
|
item.UpdatedAt = DateTimeOffset.UtcNow;
|
|
|
|
await dbContext.SaveChangesAsync(cancellationToken);
|
|
|
|
return new IngestionSummary(item.Id, accountsUpserted, 0, 0, 0, 0, 0);
|
|
}
|
|
|
|
public async Task<IngestionSummary> IngestTransactionsAsync(Guid itemId, CancellationToken cancellationToken)
|
|
{
|
|
var item = await GetItemAsync(itemId, cancellationToken);
|
|
var cursor = item.TransactionsCursor;
|
|
var accountsUpserted = 0;
|
|
var transactionsUpserted = 0;
|
|
var transactionsRemoved = 0;
|
|
var hasMore = true;
|
|
|
|
while (hasMore)
|
|
{
|
|
using var response = await plaidClient.TransactionsSyncAsync(item.AccessToken, cursor, cancellationToken);
|
|
var root = response.RootElement;
|
|
|
|
accountsUpserted += await UpsertAccountsAsync(item, root, cancellationToken);
|
|
transactionsUpserted += await UpsertTransactionsAsync(item, root, "added", cancellationToken);
|
|
transactionsUpserted += await UpsertTransactionsAsync(item, root, "modified", cancellationToken);
|
|
transactionsRemoved += await MarkTransactionsRemovedAsync(root, cancellationToken);
|
|
|
|
cursor = root.String("next_cursor") ?? cursor;
|
|
hasMore = root.Property("has_more") is { ValueKind: JsonValueKind.True };
|
|
}
|
|
|
|
item.TransactionsCursor = cursor;
|
|
item.LastTransactionsSyncedAt = DateTimeOffset.UtcNow;
|
|
item.UpdatedAt = DateTimeOffset.UtcNow;
|
|
|
|
await dbContext.SaveChangesAsync(cancellationToken);
|
|
|
|
return new IngestionSummary(item.Id, accountsUpserted, transactionsUpserted, transactionsRemoved, 0, 0, 0);
|
|
}
|
|
|
|
public async Task<IngestionSummary> IngestInvestmentsAsync(
|
|
Guid itemId,
|
|
DateOnly? startDate,
|
|
DateOnly? endDate,
|
|
CancellationToken cancellationToken)
|
|
{
|
|
var item = await GetItemAsync(itemId, cancellationToken);
|
|
using var holdingsResponse = await plaidClient.InvestmentsHoldingsGetAsync(item.AccessToken, cancellationToken);
|
|
|
|
var accountsUpserted = await UpsertAccountsAsync(item, holdingsResponse.RootElement, cancellationToken);
|
|
var securitiesUpserted = await UpsertSecuritiesAsync(holdingsResponse.RootElement, cancellationToken);
|
|
var holdingsUpserted = await UpsertHoldingsAsync(item, holdingsResponse.RootElement, cancellationToken);
|
|
|
|
var rangeEnd = endDate ?? DateOnly.FromDateTime(DateTime.UtcNow);
|
|
var rangeStart = startDate ?? rangeEnd.AddYears(-2);
|
|
|
|
var investmentTransactionsUpserted = 0;
|
|
var count = 500;
|
|
var offset = 0;
|
|
var totalInvestmentTransactions = 0;
|
|
|
|
do
|
|
{
|
|
using var transactionsResponse = await plaidClient.InvestmentsTransactionsGetAsync(
|
|
item.AccessToken,
|
|
rangeStart,
|
|
rangeEnd,
|
|
count,
|
|
offset,
|
|
cancellationToken);
|
|
|
|
accountsUpserted += await UpsertAccountsAsync(item, transactionsResponse.RootElement, cancellationToken);
|
|
securitiesUpserted += await UpsertSecuritiesAsync(transactionsResponse.RootElement, cancellationToken);
|
|
investmentTransactionsUpserted += await UpsertInvestmentTransactionsAsync(
|
|
item,
|
|
transactionsResponse.RootElement,
|
|
cancellationToken);
|
|
|
|
totalInvestmentTransactions = transactionsResponse.RootElement.Int("total_investment_transactions") ?? 0;
|
|
offset += count;
|
|
}
|
|
while (offset < totalInvestmentTransactions);
|
|
|
|
item.LastInvestmentsSyncedAt = DateTimeOffset.UtcNow;
|
|
item.UpdatedAt = DateTimeOffset.UtcNow;
|
|
|
|
await dbContext.SaveChangesAsync(cancellationToken);
|
|
|
|
return new IngestionSummary(
|
|
item.Id,
|
|
accountsUpserted,
|
|
0,
|
|
0,
|
|
securitiesUpserted,
|
|
holdingsUpserted,
|
|
investmentTransactionsUpserted);
|
|
}
|
|
|
|
public async Task<IngestionSummary> IngestAllAsync(
|
|
Guid itemId,
|
|
DateOnly? investmentStartDate,
|
|
DateOnly? investmentEndDate,
|
|
CancellationToken cancellationToken)
|
|
{
|
|
var accounts = await IngestAccountsAsync(itemId, cancellationToken);
|
|
var transactions = await IngestTransactionsAsync(itemId, cancellationToken);
|
|
var investments = await IngestInvestmentsAsync(itemId, investmentStartDate, investmentEndDate, cancellationToken);
|
|
|
|
return new IngestionSummary(
|
|
itemId,
|
|
accounts.AccountsUpserted + transactions.AccountsUpserted + investments.AccountsUpserted,
|
|
transactions.TransactionsAddedOrModified,
|
|
transactions.TransactionsRemoved,
|
|
investments.SecuritiesUpserted,
|
|
investments.HoldingsUpserted,
|
|
investments.InvestmentTransactionsUpserted);
|
|
}
|
|
|
|
private async Task<PlaidItem> GetItemAsync(Guid itemId, CancellationToken cancellationToken)
|
|
{
|
|
return await dbContext.PlaidItems.SingleOrDefaultAsync(item => item.Id == itemId, cancellationToken)
|
|
?? throw new KeyNotFoundException($"Plaid item '{itemId}' was not found.");
|
|
}
|
|
|
|
private async Task<int> UpsertAccountsAsync(
|
|
PlaidItem item,
|
|
JsonElement root,
|
|
CancellationToken cancellationToken)
|
|
{
|
|
var accounts = root.Property("accounts");
|
|
if (accounts is not { ValueKind: JsonValueKind.Array })
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
var existingAccounts = await dbContext.PlaidAccounts
|
|
.Where(account => account.PlaidItemId == item.Id)
|
|
.ToDictionaryAsync(account => account.PlaidAccountId, cancellationToken);
|
|
|
|
var upserted = 0;
|
|
foreach (var accountJson in accounts.Value.EnumerateArray())
|
|
{
|
|
var plaidAccountId = accountJson.String("account_id");
|
|
if (string.IsNullOrWhiteSpace(plaidAccountId))
|
|
{
|
|
continue;
|
|
}
|
|
|
|
if (!existingAccounts.TryGetValue(plaidAccountId, out var account))
|
|
{
|
|
account = new PlaidAccount
|
|
{
|
|
PlaidItemId = item.Id,
|
|
PlaidAccountId = plaidAccountId
|
|
};
|
|
dbContext.PlaidAccounts.Add(account);
|
|
existingAccounts[plaidAccountId] = account;
|
|
}
|
|
|
|
var balances = accountJson.Property("balances");
|
|
account.Name = accountJson.String("name") ?? account.Name;
|
|
account.OfficialName = accountJson.String("official_name");
|
|
account.Mask = accountJson.String("mask");
|
|
account.Type = accountJson.String("type") ?? account.Type;
|
|
account.Subtype = accountJson.String("subtype");
|
|
account.CurrentBalance = balances?.Decimal("current");
|
|
account.AvailableBalance = balances?.Decimal("available");
|
|
account.Limit = balances?.Decimal("limit");
|
|
account.IsoCurrencyCode = balances?.String("iso_currency_code");
|
|
account.UnofficialCurrencyCode = balances?.String("unofficial_currency_code");
|
|
account.BalanceLastUpdatedAt = balances?.DateTimeOffset("last_updated_datetime");
|
|
account.RawJson = accountJson.GetRawText();
|
|
account.UpdatedAt = DateTimeOffset.UtcNow;
|
|
upserted++;
|
|
}
|
|
|
|
return upserted;
|
|
}
|
|
|
|
private async Task<int> UpsertTransactionsAsync(
|
|
PlaidItem item,
|
|
JsonElement root,
|
|
string arrayName,
|
|
CancellationToken cancellationToken)
|
|
{
|
|
var transactions = root.Property(arrayName);
|
|
if (transactions is not { ValueKind: JsonValueKind.Array })
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
var accountLookup = await dbContext.PlaidAccounts
|
|
.Where(account => account.PlaidItemId == item.Id)
|
|
.ToDictionaryAsync(account => account.PlaidAccountId, account => account.Id, cancellationToken);
|
|
|
|
var ids = transactions.Value.EnumerateArray()
|
|
.Select(transaction => transaction.String("transaction_id"))
|
|
.Where(id => !string.IsNullOrWhiteSpace(id))
|
|
.Select(id => id!)
|
|
.ToArray();
|
|
|
|
var existingTransactions = await dbContext.PlaidTransactions
|
|
.Where(transaction => ids.Contains(transaction.PlaidTransactionId))
|
|
.ToDictionaryAsync(transaction => transaction.PlaidTransactionId, cancellationToken);
|
|
|
|
var upserted = 0;
|
|
foreach (var transactionJson in transactions.Value.EnumerateArray())
|
|
{
|
|
var plaidTransactionId = transactionJson.String("transaction_id");
|
|
var accountPlaidId = transactionJson.String("account_id");
|
|
var date = transactionJson.Date("date");
|
|
|
|
if (string.IsNullOrWhiteSpace(plaidTransactionId) ||
|
|
string.IsNullOrWhiteSpace(accountPlaidId) ||
|
|
date is null)
|
|
{
|
|
continue;
|
|
}
|
|
|
|
if (!existingTransactions.TryGetValue(plaidTransactionId, out var transaction))
|
|
{
|
|
transaction = new PlaidTransaction
|
|
{
|
|
PlaidItemId = item.Id,
|
|
PlaidTransactionId = plaidTransactionId
|
|
};
|
|
dbContext.PlaidTransactions.Add(transaction);
|
|
existingTransactions[plaidTransactionId] = transaction;
|
|
}
|
|
|
|
var personalFinanceCategory = transactionJson.Property("personal_finance_category");
|
|
transaction.AccountPlaidId = accountPlaidId;
|
|
transaction.PlaidAccountId = accountLookup.TryGetValue(accountPlaidId, out var accountId)
|
|
? accountId
|
|
: null;
|
|
transaction.Date = date.Value;
|
|
transaction.AuthorizedDate = transactionJson.Date("authorized_date");
|
|
transaction.Name = transactionJson.String("name") ?? transaction.Name;
|
|
transaction.MerchantName = transactionJson.String("merchant_name");
|
|
transaction.Amount = transactionJson.Decimal("amount") ?? transaction.Amount;
|
|
transaction.Pending = transactionJson.Bool("pending");
|
|
transaction.IsoCurrencyCode = transactionJson.String("iso_currency_code");
|
|
transaction.UnofficialCurrencyCode = transactionJson.String("unofficial_currency_code");
|
|
transaction.Categories = transactionJson.StringArray("category");
|
|
transaction.PersonalFinanceCategoryPrimary = personalFinanceCategory?.String("primary");
|
|
transaction.PersonalFinanceCategoryDetailed = personalFinanceCategory?.String("detailed");
|
|
transaction.RawJson = transactionJson.GetRawText();
|
|
transaction.RemovedAt = null;
|
|
transaction.UpdatedAt = DateTimeOffset.UtcNow;
|
|
await UpsertPlaidClassificationAsync(transaction, personalFinanceCategory, cancellationToken);
|
|
upserted++;
|
|
}
|
|
|
|
return upserted;
|
|
}
|
|
|
|
private async Task<int> MarkTransactionsRemovedAsync(JsonElement root, CancellationToken cancellationToken)
|
|
{
|
|
var removed = root.Property("removed");
|
|
if (removed is not { ValueKind: JsonValueKind.Array })
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
var removedIds = removed.Value.EnumerateArray()
|
|
.Select(transaction => transaction.String("transaction_id"))
|
|
.Where(id => !string.IsNullOrWhiteSpace(id))
|
|
.Select(id => id!)
|
|
.ToArray();
|
|
|
|
var transactions = await dbContext.PlaidTransactions
|
|
.Where(transaction => removedIds.Contains(transaction.PlaidTransactionId))
|
|
.ToListAsync(cancellationToken);
|
|
|
|
foreach (var transaction in transactions)
|
|
{
|
|
transaction.RemovedAt = DateTimeOffset.UtcNow;
|
|
transaction.UpdatedAt = DateTimeOffset.UtcNow;
|
|
}
|
|
|
|
return transactions.Count;
|
|
}
|
|
|
|
private async Task<int> UpsertSecuritiesAsync(JsonElement root, CancellationToken cancellationToken)
|
|
{
|
|
var securities = root.Property("securities");
|
|
if (securities is not { ValueKind: JsonValueKind.Array })
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
var ids = securities.Value.EnumerateArray()
|
|
.Select(security => security.String("security_id"))
|
|
.Where(id => !string.IsNullOrWhiteSpace(id))
|
|
.Select(id => id!)
|
|
.ToArray();
|
|
|
|
var existingSecurities = await dbContext.PlaidSecurities
|
|
.Where(security => ids.Contains(security.PlaidSecurityId))
|
|
.ToDictionaryAsync(security => security.PlaidSecurityId, cancellationToken);
|
|
|
|
var upserted = 0;
|
|
foreach (var securityJson in securities.Value.EnumerateArray())
|
|
{
|
|
var plaidSecurityId = securityJson.String("security_id");
|
|
if (string.IsNullOrWhiteSpace(plaidSecurityId))
|
|
{
|
|
continue;
|
|
}
|
|
|
|
if (!existingSecurities.TryGetValue(plaidSecurityId, out var security))
|
|
{
|
|
security = new PlaidSecurity
|
|
{
|
|
PlaidSecurityId = plaidSecurityId
|
|
};
|
|
dbContext.PlaidSecurities.Add(security);
|
|
existingSecurities[plaidSecurityId] = security;
|
|
}
|
|
|
|
security.Cusip = securityJson.String("cusip");
|
|
security.Isin = securityJson.String("isin");
|
|
security.Sedol = securityJson.String("sedol");
|
|
security.TickerSymbol = securityJson.String("ticker_symbol");
|
|
security.Name = securityJson.String("name");
|
|
security.Type = securityJson.String("type");
|
|
security.ClosePrice = securityJson.Decimal("close_price");
|
|
security.ClosePriceAsOf = securityJson.Date("close_price_as_of");
|
|
security.IsoCurrencyCode = securityJson.String("iso_currency_code");
|
|
security.UnofficialCurrencyCode = securityJson.String("unofficial_currency_code");
|
|
security.RawJson = securityJson.GetRawText();
|
|
security.UpdatedAt = DateTimeOffset.UtcNow;
|
|
upserted++;
|
|
}
|
|
|
|
return upserted;
|
|
}
|
|
|
|
private async Task<int> UpsertHoldingsAsync(
|
|
PlaidItem item,
|
|
JsonElement root,
|
|
CancellationToken cancellationToken)
|
|
{
|
|
var holdings = root.Property("holdings");
|
|
if (holdings is not { ValueKind: JsonValueKind.Array })
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
var accountLookup = await dbContext.PlaidAccounts
|
|
.Where(account => account.PlaidItemId == item.Id)
|
|
.ToDictionaryAsync(account => account.PlaidAccountId, account => account.Id, cancellationToken);
|
|
var securityLookup = await dbContext.PlaidSecurities
|
|
.ToDictionaryAsync(security => security.PlaidSecurityId, security => security.Id, cancellationToken);
|
|
var existingHoldings = await dbContext.PlaidHoldings
|
|
.Where(holding => holding.PlaidItemId == item.Id)
|
|
.ToListAsync(cancellationToken);
|
|
|
|
var upserted = 0;
|
|
foreach (var holdingJson in holdings.Value.EnumerateArray())
|
|
{
|
|
var accountPlaidId = holdingJson.String("account_id");
|
|
var securityPlaidId = holdingJson.String("security_id");
|
|
|
|
if (string.IsNullOrWhiteSpace(accountPlaidId) ||
|
|
string.IsNullOrWhiteSpace(securityPlaidId) ||
|
|
!accountLookup.TryGetValue(accountPlaidId, out var accountId) ||
|
|
!securityLookup.TryGetValue(securityPlaidId, out var securityId))
|
|
{
|
|
continue;
|
|
}
|
|
|
|
var holding = existingHoldings.SingleOrDefault(existing =>
|
|
existing.PlaidAccountId == accountId && existing.PlaidSecurityId == securityId);
|
|
|
|
if (holding is null)
|
|
{
|
|
holding = new PlaidHolding
|
|
{
|
|
PlaidItemId = item.Id,
|
|
PlaidAccountId = accountId,
|
|
PlaidSecurityId = securityId
|
|
};
|
|
dbContext.PlaidHoldings.Add(holding);
|
|
existingHoldings.Add(holding);
|
|
}
|
|
|
|
holding.Quantity = holdingJson.Decimal("quantity") ?? holding.Quantity;
|
|
holding.InstitutionPrice = holdingJson.Decimal("institution_price");
|
|
holding.InstitutionValue = holdingJson.Decimal("institution_value");
|
|
holding.InstitutionPriceAsOf = holdingJson.Date("institution_price_as_of");
|
|
holding.CostBasis = holdingJson.Decimal("cost_basis");
|
|
holding.IsoCurrencyCode = holdingJson.String("iso_currency_code");
|
|
holding.UnofficialCurrencyCode = holdingJson.String("unofficial_currency_code");
|
|
holding.RawJson = holdingJson.GetRawText();
|
|
holding.UpdatedAt = DateTimeOffset.UtcNow;
|
|
upserted++;
|
|
}
|
|
|
|
return upserted;
|
|
}
|
|
|
|
private async Task<int> UpsertInvestmentTransactionsAsync(
|
|
PlaidItem item,
|
|
JsonElement root,
|
|
CancellationToken cancellationToken)
|
|
{
|
|
var transactions = root.Property("investment_transactions");
|
|
if (transactions is not { ValueKind: JsonValueKind.Array })
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
var accountLookup = await dbContext.PlaidAccounts
|
|
.Where(account => account.PlaidItemId == item.Id)
|
|
.ToDictionaryAsync(account => account.PlaidAccountId, account => account.Id, cancellationToken);
|
|
var securityLookup = await dbContext.PlaidSecurities
|
|
.ToDictionaryAsync(security => security.PlaidSecurityId, security => security.Id, cancellationToken);
|
|
var ids = transactions.Value.EnumerateArray()
|
|
.Select(transaction => transaction.String("investment_transaction_id"))
|
|
.Where(id => !string.IsNullOrWhiteSpace(id))
|
|
.Select(id => id!)
|
|
.ToArray();
|
|
var existingTransactions = await dbContext.PlaidInvestmentTransactions
|
|
.Where(transaction => ids.Contains(transaction.PlaidInvestmentTransactionId))
|
|
.ToDictionaryAsync(transaction => transaction.PlaidInvestmentTransactionId, cancellationToken);
|
|
|
|
var upserted = 0;
|
|
foreach (var transactionJson in transactions.Value.EnumerateArray())
|
|
{
|
|
var investmentTransactionId = transactionJson.String("investment_transaction_id");
|
|
var accountPlaidId = transactionJson.String("account_id");
|
|
var date = transactionJson.Date("date");
|
|
|
|
if (string.IsNullOrWhiteSpace(investmentTransactionId) ||
|
|
string.IsNullOrWhiteSpace(accountPlaidId) ||
|
|
date is null)
|
|
{
|
|
continue;
|
|
}
|
|
|
|
if (!existingTransactions.TryGetValue(investmentTransactionId, out var transaction))
|
|
{
|
|
transaction = new PlaidInvestmentTransaction
|
|
{
|
|
PlaidItemId = item.Id,
|
|
PlaidInvestmentTransactionId = investmentTransactionId
|
|
};
|
|
dbContext.PlaidInvestmentTransactions.Add(transaction);
|
|
existingTransactions[investmentTransactionId] = transaction;
|
|
}
|
|
|
|
var securityPlaidId = transactionJson.String("security_id");
|
|
transaction.AccountPlaidId = accountPlaidId;
|
|
transaction.PlaidAccountId = accountLookup.TryGetValue(accountPlaidId, out var accountId)
|
|
? accountId
|
|
: null;
|
|
transaction.SecurityPlaidId = securityPlaidId;
|
|
transaction.PlaidSecurityId = securityPlaidId is not null &&
|
|
securityLookup.TryGetValue(securityPlaidId, out var securityId)
|
|
? securityId
|
|
: null;
|
|
transaction.Date = date.Value;
|
|
transaction.Name = transactionJson.String("name") ?? transaction.Name;
|
|
transaction.Quantity = transactionJson.Decimal("quantity") ?? transaction.Quantity;
|
|
transaction.Amount = transactionJson.Decimal("amount") ?? transaction.Amount;
|
|
transaction.Price = transactionJson.Decimal("price");
|
|
transaction.Fees = transactionJson.Decimal("fees");
|
|
transaction.Type = transactionJson.String("type");
|
|
transaction.Subtype = transactionJson.String("subtype");
|
|
transaction.IsoCurrencyCode = transactionJson.String("iso_currency_code");
|
|
transaction.UnofficialCurrencyCode = transactionJson.String("unofficial_currency_code");
|
|
transaction.RawJson = transactionJson.GetRawText();
|
|
transaction.UpdatedAt = DateTimeOffset.UtcNow;
|
|
upserted++;
|
|
}
|
|
|
|
return upserted;
|
|
}
|
|
|
|
private async Task UpsertPlaidClassificationAsync(
|
|
PlaidTransaction transaction,
|
|
JsonElement? personalFinanceCategory,
|
|
CancellationToken cancellationToken)
|
|
{
|
|
var primary = personalFinanceCategory?.String("primary") ?? "UNCATEGORIZED";
|
|
var detailed = personalFinanceCategory?.String("detailed") ?? primary;
|
|
var category = await GetOrCreateCategoryAsync(primary, detailed, cancellationToken);
|
|
|
|
var classification = await dbContext.TransactionClassifications
|
|
.SingleOrDefaultAsync(
|
|
existing => existing.PlaidTransactionId == transaction.Id,
|
|
cancellationToken);
|
|
|
|
if (classification is not null && classification.Source != "plaid")
|
|
{
|
|
return;
|
|
}
|
|
|
|
if (classification is null)
|
|
{
|
|
classification = new TransactionClassification
|
|
{
|
|
PlaidTransaction = transaction,
|
|
Source = "plaid"
|
|
};
|
|
dbContext.TransactionClassifications.Add(classification);
|
|
}
|
|
|
|
classification.Category = category;
|
|
classification.Source = "plaid";
|
|
classification.UpdatedAt = DateTimeOffset.UtcNow;
|
|
}
|
|
|
|
private async Task<TransactionCategory> GetOrCreateCategoryAsync(
|
|
string primary,
|
|
string detailed,
|
|
CancellationToken cancellationToken)
|
|
{
|
|
var slug = BuildCategorySlug(detailed);
|
|
var category = await dbContext.TransactionCategories
|
|
.SingleOrDefaultAsync(existing => existing.Slug == slug, cancellationToken);
|
|
|
|
if (category is not null)
|
|
{
|
|
category.PlaidPrimary ??= primary;
|
|
category.PlaidDetailed ??= detailed;
|
|
category.UpdatedAt = DateTimeOffset.UtcNow;
|
|
return category;
|
|
}
|
|
|
|
category = new TransactionCategory
|
|
{
|
|
Name = BuildCategoryName(detailed),
|
|
Slug = slug,
|
|
PlaidPrimary = primary,
|
|
PlaidDetailed = detailed,
|
|
IsSystem = true
|
|
};
|
|
|
|
dbContext.TransactionCategories.Add(category);
|
|
return category;
|
|
}
|
|
|
|
private static string BuildCategorySlug(string value)
|
|
{
|
|
var normalized = value.Trim().ToLowerInvariant().Replace("&", "and");
|
|
normalized = SlugSanitizer.Replace(normalized, "-").Trim('-');
|
|
|
|
return string.IsNullOrWhiteSpace(normalized)
|
|
? "uncategorized"
|
|
: normalized;
|
|
}
|
|
|
|
private static string BuildCategoryName(string value)
|
|
{
|
|
return string.Join(
|
|
" ",
|
|
value.ToLowerInvariant()
|
|
.Split(['_', '-'], StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries)
|
|
.Select(word => char.ToUpperInvariant(word[0]) + word[1..]));
|
|
}
|
|
}
|