namespace FundLab.Api.Tests open System open System.Text.Json open Npgsql open Xunit open FundLab.Api open FundLab.Domain [] type CashReconciliationTests(fixture: PostgresFixture) = let sharedRepository = lazy let value = FundRepository(fixture.ConnectionString) value.EnsureSchema() value let repository () = sharedRepository.Value let today = ConfirmationPolicy.tradeDateFor DateTimeOffset.UtcNow let createFund (initialCash: decimal) = let command = { Name = "现金对账测试 FOF" InitialCash = initialCash InitialUnitNav = 1.00000000m IsSynthetic = true } let key = fixture.Key(sprintf "cash-recon-fund-%s" (Guid.NewGuid().ToString("N"))) match repository().CreateFund(key, command) with | FundWriteResult.Created fund -> fund.Id | other -> failwithf "unexpected fund creation result: %A" other let seedInstrument () = let code = Random.Shared.Next(0, 1000000).ToString("D6") let payload = { Source = "akshare" SourceRevision = "akshare-test/eastmoney" CollectedAt = DateTimeOffset(2026, 9, 21, 8, 0, 0, TimeSpan.Zero) Instruments = [ { Code = code; Name = "现金对账测试基金"; FundType = None } ] } repository().UpsertInstruments(payload, "cash-recon-test-hash") code let insertQuoteOnDate (code: string) (nav: decimal) (navDate: DateOnly) = let revision = sprintf "cash-recon-test/%O" (Guid.NewGuid()) let payload: MarketDataNavPayload = { Source = "akshare" SourceRevision = revision CollectedAt = DateTimeOffset.UtcNow.AddSeconds(-10.0) Code = code Observations = [ { NavDate = navDate PublishedAt = None Nav = nav AccumulatedNav = Some nav DailyReturn = Some 0.0m } ] } repository().UpsertNavObservations(payload, sprintf "cash-recon-hash/%s" revision) let deposit fundId amount idempotencyKey = match repository().CreateCapitalDeposit( idempotencyKey, fundId, { Amount = amount; Note = Some "追加" } ) with | CapitalDepositWriteResult.CapitalDepositCreated _ | CapitalDepositWriteResult.CapitalDepositReplayed _ -> () | other -> failwithf "unexpected deposit result: %A" other let buyStock fundId code quantity price idempotencyKey = match repository().CreateStockTrade( idempotencyKey, fundId, { InstrumentCode = code; StockName = Some "贵州茅台"; Quantity = quantity; Price = price } ) with | StockTradeWriteResult.StockTradeCreated _ | StockTradeWriteResult.StockTradeReplayed _ -> () | other -> failwithf "unexpected stock trade result: %A" other let buyStockDebit fundId code quantity price idempotencyKey = match repository().CreateStockTrade( idempotencyKey, fundId, { InstrumentCode = code; StockName = Some "贵州茅台"; Quantity = quantity; Price = price }, debitAvailableCash = true ) with | StockTradeWriteResult.StockTradeCreated _ | StockTradeWriteResult.StockTradeReplayed _ -> () | other -> failwithf "unexpected stock trade result: %A" other let buyStockNoDebit fundId code quantity price idempotencyKey = match repository().CreateStockTrade( idempotencyKey, fundId, { InstrumentCode = code; StockName = Some "贵州茅台"; Quantity = quantity; Price = price }, debitAvailableCash = false ) with | StockTradeWriteResult.StockTradeCreated _ | StockTradeWriteResult.StockTradeReplayed _ -> () | other -> failwithf "unexpected stock trade result: %A" other let sellStock fundId code quantity price idempotencyKey = match repository().CreateStockSell( idempotencyKey, fundId, { InstrumentCode = code; StockName = Some "贵州茅台"; Quantity = quantity; Price = price; FeeAmount = 0m } ) with | StockSellWriteResult.StockSellCreated _ | StockSellWriteResult.StockSellReplayed _ -> () | other -> failwithf "unexpected stock sell result: %A" other let buyBondWith fundId code quantity price debitAvailableCash = match repository().CreateBondTrade( fixture.Key(sprintf "cash-recon-bond-%s" (Guid.NewGuid().ToString("N"))), fundId, { InstrumentCode = code BondName = Some "南航转债" Quantity = quantity Price = price CleanPrice = price AccruedInterest = 0m ParValue = 100m SettlementDate = today CouponRate = None ValueDate = None MaturityDate = None TradeDate = Some today }, debitAvailableCash = debitAvailableCash ) with | BondTradeWriteResult.BondTradeCreated _ -> () | other -> failwithf "unexpected bond trade result: %A" other let buyBond fundId code quantity price = buyBondWith fundId code quantity price true let buyBondNoDebit fundId code quantity price = buyBondWith fundId code quantity price false let recordBondCoupon fundId code amount idempotencyKey = match repository().RecordBondCashflow( idempotencyKey, fundId, { InstrumentCode = code BondName = Some "南航转债" EventType = "coupon" EventDate = today Quantity = 10m Amount = amount Note = None } ) with | BondCashflowWriteResult.BondCashflowCreated _ | BondCashflowWriteResult.BondCashflowReplayed _ -> () | other -> failwithf "unexpected bond cashflow result: %A" other let subscribe fundId code amount idempotencyKey = insertQuoteOnDate code 1.0m today let order = match repository().CreateSubscriptionOrder( idempotencyKey, fundId, { FundCode = code; Amount = amount; FeeAmount = 0m }, today ) with | SubscriptionOrderWriteResult.OrderCreated order | SubscriptionOrderWriteResult.OrderReplayed order -> order | other -> failwithf "unexpected subscription order result: %A" other match repository().ConfirmSubscriptionOrder( fixture.Key(sprintf "cash-recon-confirm-%s" (Guid.NewGuid().ToString("N"))), fundId, order.Id ) with | SubscriptionConfirmResult.OrderConfirmed _ -> () | other -> failwithf "unexpected subscription confirm: %A" other let redeem fundId code units idempotencyKey = let order = match repository().CreateRedemptionOrder( idempotencyKey, fundId, { InstrumentCode = code; Units = units; FeeAmount = 0m } ) with | RedemptionWriteResult.RedemptionCreated order | RedemptionWriteResult.RedemptionReplayed order -> order | other -> failwithf "unexpected redemption order result: %A" other match repository().ConfirmRedemptionOrder( fixture.Key(sprintf "cash-recon-redeem-confirm-%s" (Guid.NewGuid().ToString("N"))), fundId, order.Id ) with | RedemptionConfirmResult.RedemptionConfirmed _ | RedemptionConfirmResult.RedemptionConfirmReplayed _ -> () | other -> failwithf "unexpected redemption confirm: %A" other let app () = App.createApplication (repository ()) let reconciliation fundId = PersistenceTestHelpers.invoke (app ()) "GET" (sprintf "/api/funds/%O/cash-reconciliation" fundId) [ "Authorization", "Bearer test-token" ] "" let sourceEntry (body: string) (source: string) = use document = JsonDocument.Parse(body) document.RootElement.GetProperty("sources").EnumerateArray() |> Seq.find (fun entry -> entry.GetProperty("source").GetString() = source) |> fun entry -> entry.Clone() let eventCount (body: string) (source: string) = use document = JsonDocument.Parse(body) document.RootElement.GetProperty("events").EnumerateArray() |> Seq.filter (fun entry -> entry.GetProperty("source").GetString() = source) |> Seq.length let sourceNet (body: string) (source: string) = match (sourceEntry body source).GetProperty("netAmount").GetString() with | null -> failwithf "source %s has no netAmount" source | text -> Decimal.Parse(text, System.Globalization.CultureInfo.InvariantCulture) let deleteLedgerEvents fundId = use connection = new NpgsqlConnection(fixture.ConnectionString) connection.Open() use command = new NpgsqlCommand("DELETE FROM cash_ledger_events WHERE fund_id = @fund_id", connection) command.Parameters.AddWithValue("fund_id", fundId) |> ignore command.ExecuteNonQuery() |> ignore let ledgerEventCount fundId = use connection = new NpgsqlConnection(fixture.ConnectionString) connection.Open() use command = new NpgsqlCommand("SELECT count(*) FROM cash_ledger_events WHERE fund_id = @fund_id", connection) command.Parameters.AddWithValue("fund_id", fundId) |> ignore command.ExecuteScalar() :?> int64 let tableCount (table: string) fundId = use connection = new NpgsqlConnection(fixture.ConnectionString) connection.Open() use command = new NpgsqlCommand(sprintf "SELECT count(*) FROM %s WHERE fund_id = @fund_id" table, connection) command.Parameters.AddWithValue("fund_id", fundId) |> ignore command.ExecuteScalar() :?> int64 [] member _.``cash reconciliation balances across every source category``() = let fundId = createFund 10000.00m deposit fundId 5000.00m (fixture.Key "cash-recon-deposit") buyStockDebit fundId "600519" 100m 10.00m (fixture.Key "cash-recon-buy") sellStock fundId "600519" 40m 10.00m (fixture.Key "cash-recon-sell") buyBond fundId "110075" 10m 100.00m recordBondCoupon fundId "110075" 50.00m (fixture.Key "cash-recon-coupon") subscribe fundId (seedInstrument ()) 1000.00m (fixture.Key "cash-recon-subscribe") let status, body = reconciliation fundId Assert.Equal(200, status) Assert.Contains("\"openingCash\":\"10000.00\"", body) Assert.Contains("\"netInflow\":\"2450.00\"", body) Assert.Contains("\"closingCash\":\"12450.00\"", body) Assert.Contains("\"ledgerBalance\":\"12450.00\"", body) Assert.Contains("\"difference\":\"0.00\"", body) Assert.Equal("5000.00", (sourceEntry body "capital_deposit").GetProperty("netAmount").GetString()) Assert.Equal("-1000.00", (sourceEntry body "stock_buy").GetProperty("netAmount").GetString()) Assert.Equal("400.00", (sourceEntry body "stock_sell").GetProperty("netAmount").GetString()) Assert.Equal("-1000.00", (sourceEntry body "bond_buy").GetProperty("netAmount").GetString()) Assert.Equal("50.00", (sourceEntry body "bond_coupon").GetProperty("netAmount").GetString()) Assert.Equal("-1000.00", (sourceEntry body "subscription").GetProperty("netAmount").GetString()) // every category reconciles to zero, i.e. no unexplained cash movement Assert.Equal(0.00m, (repository().GetCashReconciliation fundId |> Result.defaultWith failwith).Difference) [] member _.``replayed operations do not double count ledger events``() = let fundId = createFund 1000.00m let depositKey = fixture.Key "cash-recon-idem-deposit" let buyKey = fixture.Key "cash-recon-idem-buy" deposit fundId 500.00m depositKey deposit fundId 500.00m depositKey buyStockDebit fundId "600519" 100m 10.00m buyKey buyStockDebit fundId "600519" 100m 10.00m buyKey let status, body = reconciliation fundId Assert.Equal(200, status) Assert.Contains("\"difference\":\"0.00\"", body) Assert.Contains("\"ledgerBalance\":\"500.00\"", body) // 1000 + 500 - 1000 Assert.Equal(1, eventCount body "capital_deposit") Assert.Equal(1, eventCount body "stock_buy") [] member _.``reconciliation for a missing fund answers 404``() = let status, body = reconciliation (Guid.NewGuid()) Assert.Equal(404, status) Assert.Contains("FUND_NOT_FOUND", body) [] member _.``backfill rebuilds the ledger for history written before it existed``() = let fundId = createFund 10000.00m deposit fundId 5000.00m (fixture.Key "cash-backfill-deposit") buyStockDebit fundId "600519" 100m 10.00m (fixture.Key "cash-backfill-buy") sellStock fundId "600519" 40m 10.00m (fixture.Key "cash-backfill-sell") buyBondNoDebit fundId "110075" 10m 100.00m recordBondCoupon fundId "110075" 50.00m (fixture.Key "cash-backfill-coupon") subscribe fundId (seedInstrument ()) 1000.00m (fixture.Key "cash-backfill-subscribe") // deposit + stock buy + stock sell + bond coupon + subscription settle let expected = 5L // simulate a database that predates the ledger: drop the hook-written events deleteLedgerEvents fundId Assert.Equal(0L, ledgerEventCount fundId) repository().BackfillCashLedger() |> ignore Assert.Equal(expected, ledgerEventCount fundId) Assert.Equal(0.00m, (repository().GetCashReconciliation fundId |> Result.defaultWith failwith).Difference) // re-running must be a no-op: same event count, same reconciled balance repository().BackfillCashLedger() |> ignore Assert.Equal(expected, ledgerEventCount fundId) Assert.Equal(0.00m, (repository().GetCashReconciliation fundId |> Result.defaultWith failwith).Difference) [] member _.``manual stock buy debits available cash and records a stock_buy event``() = let fundId = createFund 10000.00m buyStock fundId "600519" 100m 10.00m (fixture.Key "cash-manual-stock-buy") let status, body = reconciliation fundId Assert.Equal(200, status) Assert.Contains("\"openingCash\":\"10000.00\"", body) Assert.Contains("\"closingCash\":\"9000.00\"", body) Assert.Contains("\"ledgerBalance\":\"9000.00\"", body) Assert.Contains("\"difference\":\"0.00\"", body) Assert.Equal("-1000.00", (sourceEntry body "stock_buy").GetProperty("netAmount").GetString()) Assert.Equal(1, eventCount body "stock_buy") [] member _.``manual bond buy debits available cash and records a bond_buy event``() = let fundId = createFund 10000.00m buyBond fundId "110075" 10m 100.00m let status, body = reconciliation fundId Assert.Equal(200, status) Assert.Contains("\"openingCash\":\"10000.00\"", body) Assert.Contains("\"closingCash\":\"9000.00\"", body) Assert.Contains("\"ledgerBalance\":\"9000.00\"", body) Assert.Contains("\"difference\":\"0.00\"", body) Assert.Equal("-1000.00", (sourceEntry body "bond_buy").GetProperty("netAmount").GetString()) Assert.Equal(1, eventCount body "bond_buy") [] member _.``non-debiting buys leave cash and ledger untouched``() = let fundId = createFund 10000.00m buyStockNoDebit fundId "600519" 100m 10.00m (fixture.Key "cash-nodebit-stock") buyBondNoDebit fundId "110075" 10m 100.00m let status, body = reconciliation fundId Assert.Equal(200, status) Assert.Contains("\"closingCash\":\"10000.00\"", body) Assert.Contains("\"ledgerBalance\":\"10000.00\"", body) Assert.Contains("\"difference\":\"0.00\"", body) Assert.Equal(0, eventCount body "stock_buy") Assert.Equal(0, eventCount body "bond_buy") [] member _.``manual stock buy beyond available cash is rejected and changes nothing``() = let fundId = createFund 1000.00m let result = repository().CreateStockTrade( fixture.Key "cash-manual-stock-overspend", fundId, { InstrumentCode = "600519"; StockName = Some "贵州茅台"; Quantity = 100m; Price = 20.00m } ) Assert.Equal( StockTradeWriteResult.StockTradeInsufficientFunds "available cash is not enough for a stock purchase of 2000.00", result ) let status, body = reconciliation fundId Assert.Equal(200, status) Assert.Contains("\"closingCash\":\"1000.00\"", body) Assert.Contains("\"difference\":\"0.00\"", body) Assert.Equal(0, eventCount body "stock_buy") [] member _.``manual bond buy beyond available cash is rejected and changes nothing``() = let fundId = createFund 1000.00m let result = repository().CreateBondTrade( fixture.Key "cash-manual-bond-overspend", fundId, { InstrumentCode = "110075" BondName = Some "南航转债" Quantity = 10m Price = 200.00m CleanPrice = 200.00m AccruedInterest = 0m ParValue = 100m SettlementDate = today CouponRate = None ValueDate = None MaturityDate = None TradeDate = Some today } ) Assert.Equal( BondTradeWriteResult.BondTradeInsufficientFunds "available cash is not enough for a bond purchase of 2000.00", result ) let status, body = reconciliation fundId Assert.Equal(200, status) Assert.Contains("\"closingCash\":\"1000.00\"", body) Assert.Contains("\"difference\":\"0.00\"", body) Assert.Equal(0, eventCount body "bond_buy") // the rolled-back transaction must not leave trades, positions or dirty ledger rows behind Assert.Equal(0L, tableCount "bond_trades" fundId) Assert.Equal(0L, tableCount "bond_positions" fundId) Assert.Equal(0L, ledgerEventCount fundId) [] member _.``manual stock sell credits available cash and records a stock_sell event``() = let fundId = createFund 10000.00m buyStock fundId "600519" 100m 10.00m (fixture.Key "cash-sell-buy") sellStock fundId "600519" 40m 12.00m (fixture.Key "cash-sell-partial") let status, body = reconciliation fundId Assert.Equal(200, status) Assert.Contains("\"openingCash\":\"10000.00\"", body) Assert.Contains("\"closingCash\":\"9480.00\"", body) Assert.Contains("\"ledgerBalance\":\"9480.00\"", body) Assert.Contains("\"difference\":\"0.00\"", body) Assert.Equal("-1000.00", (sourceEntry body "stock_buy").GetProperty("netAmount").GetString()) Assert.Equal("480.00", (sourceEntry body "stock_sell").GetProperty("netAmount").GetString()) Assert.Equal(1, eventCount body "stock_sell") [] member _.``replaying a stock sell credits cash and the ledger only once``() = let fundId = createFund 10000.00m let sellKey = fixture.Key "cash-sell-replay" buyStock fundId "600519" 100m 10.00m (fixture.Key "cash-sell-replay-buy") sellStock fundId "600519" 40m 12.00m sellKey sellStock fundId "600519" 40m 12.00m sellKey let status, body = reconciliation fundId Assert.Equal(200, status) Assert.Contains("\"closingCash\":\"9480.00\"", body) Assert.Contains("\"difference\":\"0.00\"", body) Assert.Equal(1, eventCount body "stock_sell") Assert.Equal(1L, tableCount "stock_sells" fundId) [] member _.``external flows and internal trades stay separate sources reconciled against net inflow``() = let fundId = createFund 10000.00m let code = seedInstrument () deposit fundId 5000.00m (fixture.Key "cash-flow-split-deposit") subscribe fundId code 1000.00m (fixture.Key "cash-flow-split-subscribe") redeem fundId code 400m (fixture.Key "cash-flow-split-redeem") buyStock fundId "600519" 100m 10.00m (fixture.Key "cash-flow-split-buy") sellStock fundId "600519" 40m 12.00m (fixture.Key "cash-flow-split-sell") let status, body = reconciliation fundId Assert.Equal(200, status) Assert.Contains("\"openingCash\":\"10000.00\"", body) Assert.Contains("\"netInflow\":\"3880.00\"", body) Assert.Contains("\"closingCash\":\"13880.00\"", body) Assert.Contains("\"ledgerBalance\":\"13880.00\"", body) Assert.Contains("\"difference\":\"0.00\"", body) // external capital/subscription/redemption are their own ledger sources Assert.Equal(5000.00m, sourceNet body "capital_deposit") Assert.Equal(-1000.00m, sourceNet body "subscription") Assert.Equal(400.00m, sourceNet body "redemption") Assert.Equal(1, eventCount body "subscription") Assert.Equal(1, eventCount body "redemption") // internal stock trades are their own ledger sources, never external flows Assert.Equal(-1000.00m, sourceNet body "stock_buy") Assert.Equal(480.00m, sourceNet body "stock_sell") Assert.Equal(1, eventCount body "stock_buy") Assert.Equal(1, eventCount body "stock_sell") // the two buckets add up to net inflow: external flows are not smuggled into trade sources let externalTotal = sourceNet body "capital_deposit" + sourceNet body "subscription" + sourceNet body "redemption" let internalTotal = sourceNet body "stock_buy" + sourceNet body "stock_sell" Assert.Equal(4400.00m, externalTotal) Assert.Equal(-520.00m, internalTotal) Assert.Equal(3880.00m, externalTotal + internalTotal) Assert.Equal(0.00m, (repository().GetCashReconciliation fundId |> Result.defaultWith failwith).Difference) [] member _.``a subscription confirmed at the 2026-09-22 nav date reconciles to zero``() = let fundId = createFund 10000.00m let code = seedInstrument () let tradeDate = DateOnly(2026, 9, 22) insertQuoteOnDate code 1.327m tradeDate let order = match repository().CreateSubscriptionOrder( fixture.Key "cash-tday-order", fundId, { FundCode = code; Amount = 1000.00m; FeeAmount = 0m }, tradeDate ) with | SubscriptionOrderWriteResult.OrderCreated order -> order | other -> failwithf "unexpected subscription order result: %A" other match repository().ConfirmSubscriptionOrder( fixture.Key "cash-tday-confirm", fundId, order.Id ) with | SubscriptionConfirmResult.OrderConfirmed confirmed -> match confirmed.ConfirmedQuote with | Some quote -> Assert.Equal(tradeDate, quote.NavDate) Assert.Equal(1.327m, quote.Nav) | None -> failwith "confirmed quote evidence missing" | other -> failwithf "unexpected subscription confirm: %A" other let status, body = reconciliation fundId Assert.Equal(200, status) Assert.Contains("\"openingCash\":\"10000.00\"", body) Assert.Contains("\"difference\":\"0.00\"", body) Assert.Equal(1, eventCount body "subscription") // the confirmed subscription is an external outflow that fully explains cash let subscriptionNet = sourceNet body "subscription" Assert.True(subscriptionNet < 0m, sprintf "expected subscription outflow, got %M" subscriptionNet)