Backfill the missing cash debit for pre-3d-34 stock buys (3d-39)

2 files changed, 85 insertions, 9 deletions

diff --git a/src/FundLab.Api/Persistence.fs b/src/FundLab.Api/Persistence.fs
index f18ad68..0f81aea 100644
--- a/src/FundLab.Api/Persistence.fs
+++ b/src/FundLab.Api/Persistence.fs
@@ -7149,6 +7149,13 @@ type FundRepository(connectionString: string) =
     /// 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.
+    ///
+    /// Stock buys executed before the 3d-34 debit fix (2dedc30) never reduced
+    /// available_cash, so rebuilding only their ledger event would leave the fund
+    /// imbalanced by the cost. That batch runs last and also debits available_cash for the
+    /// funds still missing it, but only where the fund reconciles to zero before the batch
+    /// -- exactly the case where cash never paid for the buy. Funds whose debit already
+    /// happened are left untouched, so it stays idempotent.
     member _.BackfillCashLedger() : int =
         let statements =
             [
@@ -7174,15 +7181,6 @@ type FundRepository(connectionString: string) =
                 """
                 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
@@ -7239,6 +7237,48 @@ type FundRepository(connectionString: string) =
                 WHERE o.confirmed_invested_cash IS NOT NULL
                 ON CONFLICT (fund_id, reference_type, reference_id) DO NOTHING
                 """
+                """
+                WITH candidate AS (
+                    SELECT t.fund_id,
+                           t.id::text AS reference_id,
+                           (t.executed_at AT TIME ZONE 'UTC')::date AS event_date,
+                           -t.cost_cash AS amount
+                    FROM stock_trades t
+                    WHERE NOT EXISTS (
+                        SELECT 1
+                        FROM cash_ledger_events e
+                        WHERE e.fund_id = t.fund_id
+                          AND e.reference_type = 'stock_buy'
+                          AND e.reference_id = t.id::text
+                    )
+                ),
+                inserted AS (
+                    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, 'stock_buy', c.event_date, c.amount,
+                           'stock_buy', c.reference_id, NULL
+                    FROM candidate c
+                    ON CONFLICT (fund_id, reference_type, reference_id) DO NOTHING
+                    RETURNING fund_id, amount
+                ),
+                totals AS (
+                    SELECT fund_id, SUM(amount) AS amount
+                    FROM inserted
+                    GROUP BY fund_id
+                ),
+                missing_debit AS (
+                    SELECT f.id AS fund_id, t.amount
+                    FROM funds f
+                    JOIN totals t ON t.fund_id = f.id
+                    WHERE f.initial_cash
+                          + COALESCE((SELECT SUM(e.amount) FROM cash_ledger_events e WHERE e.fund_id = f.id), 0)
+                          - (f.available_cash + f.reserved_cash) = 0
+                )
+                UPDATE funds f
+                SET available_cash = f.available_cash + m.amount
+                FROM missing_debit m
+                WHERE f.id = m.fund_id
+                """
             ]
 
         use connection = new NpgsqlConnection(connectionString)
diff --git a/tests/FundLab.Api.Tests/CashReconciliationTests.fs b/tests/FundLab.Api.Tests/CashReconciliationTests.fs
index fa14bff..b31b358 100644
--- a/tests/FundLab.Api.Tests/CashReconciliationTests.fs
+++ b/tests/FundLab.Api.Tests/CashReconciliationTests.fs
@@ -366,6 +366,42 @@ type CashReconciliationTests(fixture: PostgresFixture) =
         Assert.Equal(0.00m, (repository().GetCashReconciliation fundId |> Result.defaultWith failwith).Difference)
 
     [<Fact>]
+    member _.``backfill debits cash for a pre-3d-34 stock buy that never reduced available cash``() =
+        let fundId = createFund 10000.00m
+
+        // a manual stock buy from before the 3d-34 debit fix: no ledger event and no debit
+        buyStockNoDebit fundId "600519" 100m 10.00m (fixture.Key "cash-pre34-buy")
+
+        // simulate a database that predates the ledger
+        deleteLedgerEvents fundId
+        Assert.Equal(0L, ledgerEventCount fundId)
+
+        let before =
+            repository().GetFund fundId |> Option.defaultWith (fun () -> failwith "fund was not found")
+
+        Assert.Equal(10000.00m, before.AvailableCash)
+
+        repository().BackfillCashLedger() |> ignore
+
+        let after =
+            repository().GetFund fundId |> Option.defaultWith (fun () -> failwith "fund was not found")
+
+        // the rebuilt stock_buy event is paired with the missing 1000.00 debit
+        Assert.Equal(9000.00m, after.AvailableCash)
+        Assert.Equal(1L, ledgerEventCount fundId)
+        Assert.Equal(0.00m, (repository().GetCashReconciliation fundId |> Result.defaultWith failwith).Difference)
+
+        // re-running must not debit twice or write another event
+        repository().BackfillCashLedger() |> ignore
+
+        let rerun =
+            repository().GetFund fundId |> Option.defaultWith (fun () -> failwith "fund was not found")
+
+        Assert.Equal(9000.00m, rerun.AvailableCash)
+        Assert.Equal(1L, ledgerEventCount fundId)
+        Assert.Equal(0.00m, (repository().GetCashReconciliation fundId |> Result.defaultWith failwith).Difference)
+
+    [<Fact>]
     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")