Backfill cash ledger from historical transactions and add reconciliation QA (3d-32)

4 files changed, 184 insertions, 0 deletions

diff --git a/qa/run.sh b/qa/run.sh
index d28a084..3058c07 100755
--- a/qa/run.sh
+++ b/qa/run.sh
@@ -110,6 +110,37 @@ for _ in $(seq 1 60); do
 done
 [ "$api_up" = 1 ] || { cat "$API_LOG" >&2; die "API did not become ready"; }
 
+log "checking cash-reconciliation API (curl assertion)"
+RECON_FUND_ID="$(curl -s -X POST "http://127.0.0.1:$API_PORT/api/funds" \
+  -H "Authorization: Bearer $AUTH_TOKEN" \
+  -H "Content-Type: application/json" \
+  -H "Idempotency-Key: qa-recon-fund-$RANDOM" \
+  -d '{"name":"QA 现金对账","initialCash":"1000.00","initialUnitNav":"1.00000000","isSynthetic":true}' \
+  | python3 -c 'import sys,json; print(json.load(sys.stdin)["id"])')"
+[ -n "$RECON_FUND_ID" ] || die "cash reconciliation assertion: fund creation failed"
+
+curl -s -X POST "http://127.0.0.1:$API_PORT/api/funds/$RECON_FUND_ID/capital/deposit" \
+  -H "Authorization: Bearer $AUTH_TOKEN" \
+  -H "Content-Type: application/json" \
+  -H "Idempotency-Key: qa-recon-deposit-$RANDOM" \
+  -d '{"amount":"2500.00","note":"qa"}' >/dev/null
+
+curl -s -H "Authorization: Bearer $AUTH_TOKEN" "http://127.0.0.1:$API_PORT/api/funds/$RECON_FUND_ID/cash-reconciliation" \
+  | python3 -c '
+import sys, json
+d = json.load(sys.stdin)
+required = ["openingCash", "netInflow", "closingCash", "ledgerBalance", "difference", "sources", "events"]
+missing = [k for k in required if k not in d]
+assert not missing, "missing fields: %s" % missing
+assert d["difference"] == "0.00", "difference=%s" % d["difference"]
+assert d["openingCash"] == "1000.00", d["openingCash"]
+assert d["closingCash"] == "3500.00", d["closingCash"]
+src = {s["source"]: s for s in d["sources"]}
+assert src.get("capital_deposit", {}).get("netAmount") == "2500.00", d["sources"]
+assert src["capital_deposit"]["eventCount"] == 1, d["sources"]
+print("PASS  C1 现金对账字段齐全且 difference==0  -- diff=%s sources=%s" % (d["difference"], d["sources"]))
+' || die "cash reconciliation curl assertion failed"
+
 log "compiling F# to JS (fable)"
 ( cd "$ROOT/src/FundLab.Web" && dotnet fable FundLab.Web.fsproj --outDir dist --noCache ) > "$TMP_DIR/fable.log" 2>&1
 
diff --git a/src/FundLab.Api/Persistence.fs b/src/FundLab.Api/Persistence.fs
index 47c4312..2ca11cd 100644
--- a/src/FundLab.Api/Persistence.fs
+++ b/src/FundLab.Api/Persistence.fs
@@ -7109,6 +7109,115 @@ type FundRepository(connectionString: string) =
             let ledgerBalance = fund.AvailableCash + fund.ReservedCash
             Ok(CashLedger.reconcile fund.InitialCash ledgerBalance events)
 
+    /// Backfill cash-ledger events for funds whose history predates the ledger. Each
+    /// statement derives its amount straight from the same table the live hook reads, and
+    /// the (fund_id, reference_type, reference_id) unique key makes re-runs no-ops, so a
+    /// migrated database is unchanged and no movement is ever counted twice.
+    member _.BackfillCashLedger() : int =
+        let statements =
+            [
+                """
+                INSERT INTO cash_ledger_events
+                    (id, fund_id, source_category, event_date, amount, reference_type, reference_id, note)
+                SELECT gen_random_uuid(), d.fund_id, 'capital_deposit',
+                       (d.created_at AT TIME ZONE 'Asia/Shanghai')::date, d.amount,
+                       'capital_deposit', d.id::text, NULL
+                FROM fund_capital_deposits d
+                ON CONFLICT (fund_id, reference_type, reference_id) DO NOTHING
+                """
+                """
+                INSERT INTO cash_ledger_events
+                    (id, fund_id, source_category, event_date, amount, reference_type, reference_id, note)
+                SELECT gen_random_uuid(), r.fund_id, 'dividend',
+                       r.nav_date, r.gross_cash,
+                       'dividend', r.id::text, NULL
+                FROM dividend_records r
+                WHERE r.gross_cash IS NOT NULL
+                ON CONFLICT (fund_id, reference_type, reference_id) DO NOTHING
+                """
+                """
+                INSERT INTO cash_ledger_events
+                    (id, fund_id, source_category, event_date, amount, reference_type, reference_id, note)
+                SELECT gen_random_uuid(), t.fund_id, 'stock_buy',
+                       (t.executed_at AT TIME ZONE 'UTC')::date, -t.cost_cash,
+                       'stock_buy', t.id::text, NULL
+                FROM stock_trades t
+                ON CONFLICT (fund_id, reference_type, reference_id) DO NOTHING
+                """
+                """
+                INSERT INTO cash_ledger_events
+                    (id, fund_id, source_category, event_date, amount, reference_type, reference_id, note)
+                SELECT gen_random_uuid(), s.fund_id, 'stock_sell',
+                       (s.executed_at AT TIME ZONE 'UTC')::date, s.proceeds,
+                       'stock_sell', s.id::text, NULL
+                FROM stock_sells s
+                ON CONFLICT (fund_id, reference_type, reference_id) DO NOTHING
+                """
+                """
+                INSERT INTO cash_ledger_events
+                    (id, fund_id, source_category, event_date, amount, reference_type, reference_id, note)
+                SELECT gen_random_uuid(), c.fund_id,
+                       CASE c.event_type
+                           WHEN 'coupon' THEN 'bond_coupon'
+                           WHEN 'maturity' THEN 'bond_maturity'
+                           ELSE 'bond_sell'
+                       END,
+                       c.event_date, c.amount,
+                       'bond_cashflow', c.id::text, NULL
+                FROM bond_cashflow_events c
+                ON CONFLICT (fund_id, reference_type, reference_id) DO NOTHING
+                """
+                """
+                INSERT INTO cash_ledger_events
+                    (id, fund_id, source_category, event_date, amount, reference_type, reference_id, note)
+                SELECT gen_random_uuid(), s.fund_id, 'bond_sell',
+                       (s.executed_at AT TIME ZONE 'UTC')::date, s.proceeds,
+                       'bond_sell', s.id::text, NULL
+                FROM bond_sells s
+                ON CONFLICT (fund_id, reference_type, reference_id) DO NOTHING
+                """
+                """
+                INSERT INTO cash_ledger_events
+                    (id, fund_id, source_category, event_date, amount, reference_type, reference_id, note)
+                SELECT gen_random_uuid(), o.fund_id, 'redemption',
+                       COALESCE(o.confirmed_nav_date, o.trade_date), o.confirmed_proceeds,
+                       'redemption', o.id::text, NULL
+                FROM redemption_orders o
+                WHERE o.confirmed_proceeds IS NOT NULL
+                ON CONFLICT (fund_id, reference_type, reference_id) DO NOTHING
+                """
+                """
+                INSERT INTO cash_ledger_events
+                    (id, fund_id, source_category, event_date, amount, reference_type, reference_id, note)
+                SELECT gen_random_uuid(), o.fund_id,
+                       CASE
+                           WHEN i.idempotency_key LIKE 'sip:%' OR i.idempotency_key LIKE 'investment-plan:%'
+                               THEN 'sip'
+                           ELSE 'subscription'
+                       END,
+                       COALESCE(o.confirmed_nav_date, o.trade_date),
+                       -(o.confirmed_invested_cash + o.fee_amount),
+                       'subscription_settle', o.id::text, NULL
+                FROM subscription_orders o
+                LEFT JOIN subscription_order_idempotencies i ON i.order_id = o.id
+                WHERE o.confirmed_invested_cash IS NOT NULL
+                ON CONFLICT (fund_id, reference_type, reference_id) DO NOTHING
+                """
+            ]
+
+        use connection = new NpgsqlConnection(connectionString)
+        connection.Open()
+        use transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)
+
+        let mutable inserted = 0
+
+        for statement in statements do
+            use command = commandWithTransaction connection (Some transaction) statement
+            inserted <- inserted + command.ExecuteNonQuery()
+
+        transaction.Commit()
+        inserted
+
     member _.CreateRedemptionOrder(idempotencyKey: string, fundId: Guid, command: RedemptionCommand) : RedemptionWriteResult =
         if String.IsNullOrWhiteSpace idempotencyKey then
             RedemptionWriteResult.RedemptionInvalid "idempotency key cannot be empty"
diff --git a/src/FundLab.Api/Program.fs b/src/FundLab.Api/Program.fs
index 768fb26..9c3be63 100644
--- a/src/FundLab.Api/Program.fs
+++ b/src/FundLab.Api/Program.fs
@@ -17,6 +17,7 @@ let main argv =
     builder.Services.AddGiraffe() |> ignore
     let repository = FundRepository(requiredEnvironment "FUND_LAB_DATABASE_URL")
     repository.EnsureSchema()
+    repository.BackfillCashLedger() |> ignore
     let collector = ProcessMarketDataCollector.FromEnvironment() :> IMarketDataCollector
     let marketData = MarketDataService(repository, collector) :> IMarketDataService
     let navDateProbe = AkshareNavDateProbe(collector) :> INavDateProbe
diff --git a/tests/FundLab.Api.Tests/CashReconciliationTests.fs b/tests/FundLab.Api.Tests/CashReconciliationTests.fs
index 59f6fd6..d8dc7bb 100644
--- a/tests/FundLab.Api.Tests/CashReconciliationTests.fs
+++ b/tests/FundLab.Api.Tests/CashReconciliationTests.fs
@@ -2,6 +2,7 @@ namespace FundLab.Api.Tests
 
 open System
 open System.Text.Json
+open Npgsql
 open Xunit
 open FundLab.Api
 open FundLab.Domain
@@ -201,6 +202,20 @@ type CashReconciliationTests(fixture: PostgresFixture) =
         |> Seq.filter (fun entry -> entry.GetProperty("source").GetString() = source)
         |> Seq.length
 
+    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
+
     [<Fact>]
     member _.``cash reconciliation balances across every source category``() =
         let fundId = createFund 10000.00m
@@ -252,3 +267,31 @@ type CashReconciliationTests(fixture: PostgresFixture) =
         let status, body = reconciliation (Guid.NewGuid())
         Assert.Equal(404, status)
         Assert.Contains("FUND_NOT_FOUND", body)
+
+    [<Fact>]
+    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")
+        buyBond 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)