Debit available cash on manual stock and bond buys with ledger events (3d-34)

1 files changed, 88 insertions, 52 deletions

diff --git a/src/FundLab.Api/Persistence.fs b/src/FundLab.Api/Persistence.fs
index 2ca11cd..f18ad68 100644
--- a/src/FundLab.Api/Persistence.fs
+++ b/src/FundLab.Api/Persistence.fs
@@ -554,6 +554,7 @@ type BondTradeWriteResult =
     | BondTradeIdempotencyConflict
     | BondTradeInvalid of string
     | BondTradeFundNotFound
+    | BondTradeInsufficientFunds of string
 
 type SipPlanRecord =
     {
@@ -5999,7 +6000,7 @@ type FundRepository(connectionString: string) =
                             let costCash = Decimal.Round(normalized.Quantity * normalized.Price, 2, MidpointRounding.AwayFromZero)
 
                             let debited =
-                                if defaultArg debitAvailableCash false then
+                                if defaultArg debitAvailableCash true then
                                     use cashCommand =
                                         commandWithTransaction
                                             connection
@@ -6037,7 +6038,7 @@ type FundRepository(connectionString: string) =
                                 insertStockTrade connection (Some transaction) trade
                                 insertStockTradeIdempotency connection (Some transaction) idempotencyKey fingerprint trade.Id fundId
 
-                                if defaultArg debitAvailableCash false then
+                                if defaultArg debitAvailableCash true then
                                     insertCashLedgerEvent
                                         connection
                                         (Some transaction)
@@ -6481,7 +6482,7 @@ type FundRepository(connectionString: string) =
 
                         raise error
 
-    member _.CreateBondTrade(idempotencyKey: string, fundId: Guid, command: BondTradeCommand, ?executedAtOverride: DateTimeOffset) : BondTradeWriteResult =
+    member _.CreateBondTrade(idempotencyKey: string, fundId: Guid, command: BondTradeCommand, ?executedAtOverride: DateTimeOffset, ?debitAvailableCash: bool) : BondTradeWriteResult =
         if String.IsNullOrWhiteSpace idempotencyKey then
             BondTradeWriteResult.BondTradeInvalid "idempotency key cannot be empty"
         else
@@ -6544,61 +6545,96 @@ type FundRepository(connectionString: string) =
                                     MidpointRounding.AwayFromZero
                                 )
 
-                            let trade: BondTradeRecord =
-                                {
-                                    Id = Guid.NewGuid()
-                                    FundId = fundId
-                                    InstrumentCode = normalized.InstrumentCode
-                                    BondName = normalized.BondName
-                                    Quantity = normalized.Quantity
-                                    Price = normalized.Price
-                                    CleanPrice = normalized.CleanPrice
-                                    AccruedInterest = normalized.AccruedInterest
-                                    ParValue = normalized.ParValue
-                                    SettlementDate = normalized.SettlementDate
-                                    CouponRate = normalized.CouponRate
-                                    ValueDate = normalized.ValueDate
-                                    MaturityDate = normalized.MaturityDate
-                                    TradeDate = normalized.TradeDate |> Option.defaultValue normalized.SettlementDate
-                                    CostCash = costCash
-                                    IsSynthetic = isSynthetic
-                                    ExecutedAt = executedAt
-                                }
+                            let debited =
+                                if defaultArg debitAvailableCash true then
+                                    use cashCommand =
+                                        commandWithTransaction
+                                            connection
+                                            (Some transaction)
+                                            "UPDATE funds SET available_cash = available_cash - @cost WHERE id = @fund_id AND available_cash >= @cost"
 
-                            insertBondTrade connection (Some transaction) trade
-                            insertBondTradeIdempotency connection (Some transaction) idempotencyKey fingerprint trade.Id fundId
+                                    addParameter cashCommand "cost" NpgsqlDbType.Numeric (box costCash) |> ignore
+                                    addParameter cashCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore
+                                    cashCommand.ExecuteNonQuery() = 1
+                                else
+                                    true
 
-                            use positionCommand =
-                                commandWithTransaction
-                                    connection
-                                    (Some transaction)
-                                    """
-                                    INSERT INTO bond_positions
-                                        (fund_id, instrument_code, bond_name, quantity, cost_cash, last_traded_at)
-                                    VALUES (@fund_id, @code, @name, @quantity, @cost_cash, @last_traded_at)
-                                    ON CONFLICT (fund_id, instrument_code) DO UPDATE
-                                    SET quantity = bond_positions.quantity + EXCLUDED.quantity,
-                                        cost_cash = bond_positions.cost_cash + EXCLUDED.cost_cash,
-                                        bond_name = COALESCE(EXCLUDED.bond_name, bond_positions.bond_name),
-                                        last_traded_at = EXCLUDED.last_traded_at
-                                    """
+                            if not debited then
+                                transaction.Rollback()
 
-                            addParameter positionCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore
-                            addParameter positionCommand "code" NpgsqlDbType.Text (box normalized.InstrumentCode) |> ignore
+                                BondTradeWriteResult.BondTradeInsufficientFunds(
+                                    sprintf
+                                        "available cash is not enough for a bond purchase of %s"
+                                        (costCash.ToString("0.00", CultureInfo.InvariantCulture))
+                                )
+                            else
+                                let trade: BondTradeRecord =
+                                    {
+                                        Id = Guid.NewGuid()
+                                        FundId = fundId
+                                        InstrumentCode = normalized.InstrumentCode
+                                        BondName = normalized.BondName
+                                        Quantity = normalized.Quantity
+                                        Price = normalized.Price
+                                        CleanPrice = normalized.CleanPrice
+                                        AccruedInterest = normalized.AccruedInterest
+                                        ParValue = normalized.ParValue
+                                        SettlementDate = normalized.SettlementDate
+                                        CouponRate = normalized.CouponRate
+                                        ValueDate = normalized.ValueDate
+                                        MaturityDate = normalized.MaturityDate
+                                        TradeDate = normalized.TradeDate |> Option.defaultValue normalized.SettlementDate
+                                        CostCash = costCash
+                                        IsSynthetic = isSynthetic
+                                        ExecutedAt = executedAt
+                                    }
 
-                            let nameParameter =
-                                match normalized.BondName with
-                                | Some name -> box name
-                                | None -> box DBNull.Value
+                                insertBondTrade connection (Some transaction) trade
+                                insertBondTradeIdempotency connection (Some transaction) idempotencyKey fingerprint trade.Id fundId
 
-                            addParameter positionCommand "name" NpgsqlDbType.Text nameParameter |> ignore
-                            addParameter positionCommand "quantity" NpgsqlDbType.Numeric (box normalized.Quantity) |> ignore
-                            addParameter positionCommand "cost_cash" NpgsqlDbType.Numeric (box costCash) |> ignore
-                            addParameter positionCommand "last_traded_at" NpgsqlDbType.TimestampTz (box executedAt) |> ignore
-                            positionCommand.ExecuteNonQuery() |> ignore
+                                if defaultArg debitAvailableCash true then
+                                    insertCashLedgerEvent
+                                        connection
+                                        (Some transaction)
+                                        fundId
+                                        CashLedger.BondBuy
+                                        (DateOnly.FromDateTime executedAt.UtcDateTime)
+                                        -costCash
+                                        "bond_buy"
+                                        (trade.Id.ToString("D"))
+                                        None
 
-                            transaction.Commit()
-                            BondTradeWriteResult.BondTradeCreated trade
+                                use positionCommand =
+                                    commandWithTransaction
+                                        connection
+                                        (Some transaction)
+                                        """
+                                        INSERT INTO bond_positions
+                                            (fund_id, instrument_code, bond_name, quantity, cost_cash, last_traded_at)
+                                        VALUES (@fund_id, @code, @name, @quantity, @cost_cash, @last_traded_at)
+                                        ON CONFLICT (fund_id, instrument_code) DO UPDATE
+                                        SET quantity = bond_positions.quantity + EXCLUDED.quantity,
+                                            cost_cash = bond_positions.cost_cash + EXCLUDED.cost_cash,
+                                            bond_name = COALESCE(EXCLUDED.bond_name, bond_positions.bond_name),
+                                            last_traded_at = EXCLUDED.last_traded_at
+                                        """
+
+                                addParameter positionCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore
+                                addParameter positionCommand "code" NpgsqlDbType.Text (box normalized.InstrumentCode) |> ignore
+
+                                let nameParameter =
+                                    match normalized.BondName with
+                                    | Some name -> box name
+                                    | None -> box DBNull.Value
+
+                                addParameter positionCommand "name" NpgsqlDbType.Text nameParameter |> ignore
+                                addParameter positionCommand "quantity" NpgsqlDbType.Numeric (box normalized.Quantity) |> ignore
+                                addParameter positionCommand "cost_cash" NpgsqlDbType.Numeric (box costCash) |> ignore
+                                addParameter positionCommand "last_traded_at" NpgsqlDbType.TimestampTz (box executedAt) |> ignore
+                                positionCommand.ExecuteNonQuery() |> ignore
+
+                                transaction.Commit()
+                                BondTradeWriteResult.BondTradeCreated trade
                 with error ->
                     try
                         transaction.Rollback()