Add bond sell/redemption ledger, bond cashflow events and maturity calendar (3d-30 A)

1 files changed, 434 insertions, 4 deletions

diff --git a/src/FundLab.Api/Persistence.fs b/src/FundLab.Api/Persistence.fs
index 2471c58..9ccb0c7 100644
--- a/src/FundLab.Api/Persistence.fs
+++ b/src/FundLab.Api/Persistence.fs
@@ -444,6 +444,52 @@ type BondPositionRecord =
         LastTradedAt: DateTimeOffset
     }
 
+type BondSellCommand =
+    {
+        InstrumentCode: string
+        BondName: string option
+        Quantity: decimal
+        /// All-in (dirty/全价) sell price per 100 of face value.
+        Price: decimal
+        CleanPrice: decimal
+        AccruedInterest: decimal
+        ParValue: decimal
+        SettlementDate: DateOnly
+        TradeDate: DateOnly option
+        FeeAmount: decimal
+    }
+
+type BondSellRecord =
+    {
+        Id: Guid
+        FundId: Guid
+        InstrumentCode: string
+        BondName: string option
+        Quantity: decimal
+        Price: decimal
+        CleanPrice: decimal
+        AccruedInterest: decimal
+        ParValue: decimal
+        SettlementDate: DateOnly
+        TradeDate: DateOnly
+        FeeAmount: decimal
+        /// Cash credited to the fund after fees (dirty amount minus fee).
+        Proceeds: decimal
+        /// Average-cost basis removed from the position.
+        CostReleased: decimal
+        RealizedPnl: decimal
+        IsSynthetic: bool
+        ExecutedAt: DateTimeOffset
+    }
+
+type BondSellWriteResult =
+    | BondSellCreated of BondSellRecord
+    | BondSellReplayed of BondSellRecord
+    | BondSellIdempotencyConflict
+    | BondSellInvalid of string
+    | BondSellInsufficientHoldings of string
+    | BondSellFundNotFound
+
 type InstrumentSnapshotRecord =
     {
         InstrumentCode: string
@@ -1096,7 +1142,7 @@ type FundRepository(connectionString: string) =
             fund_id uuid NOT NULL REFERENCES funds(id),
             instrument_code text NOT NULL,
             bond_name text NULL,
-            event_type text NOT NULL CHECK (event_type IN ('coupon', 'maturity')),
+            event_type text NOT NULL CHECK (event_type IN ('coupon', 'maturity', 'redemption')),
             event_date date NOT NULL,
             quantity numeric(28, 8) NOT NULL CHECK (quantity > 0),
             amount numeric(20, 2) NOT NULL CHECK (amount >= 0),
@@ -1105,6 +1151,9 @@ type FundRepository(connectionString: string) =
             created_at timestamptz NOT NULL
         );
 
+        ALTER TABLE bond_cashflow_events DROP CONSTRAINT IF EXISTS bond_cashflow_events_event_type_check;
+        ALTER TABLE bond_cashflow_events ADD CONSTRAINT bond_cashflow_events_event_type_check CHECK (event_type IN ('coupon', 'maturity', 'redemption'));
+
         CREATE TABLE IF NOT EXISTS bond_cashflow_idempotencies (
             idempotency_key text PRIMARY KEY,
             request_hash text NOT NULL,
@@ -1113,6 +1162,34 @@ type FundRepository(connectionString: string) =
             created_at timestamptz NOT NULL DEFAULT now()
         );
 
+        CREATE TABLE IF NOT EXISTS bond_sells (
+            id uuid PRIMARY KEY,
+            fund_id uuid NOT NULL REFERENCES funds(id),
+            instrument_code text NOT NULL,
+            bond_name text NULL,
+            quantity numeric(28, 8) NOT NULL CHECK (quantity > 0),
+            price numeric(20, 4) NOT NULL CHECK (price > 0),
+            clean_price numeric(20, 4) NOT NULL DEFAULT 0,
+            accrued_interest numeric(20, 4) NOT NULL DEFAULT 0,
+            par_value numeric(20, 4) NOT NULL DEFAULT 100,
+            settlement_date date NOT NULL DEFAULT CURRENT_DATE,
+            trade_date date NOT NULL DEFAULT CURRENT_DATE,
+            fee_amount numeric(20, 2) NOT NULL DEFAULT 0 CHECK (fee_amount >= 0),
+            proceeds numeric(20, 2) NOT NULL DEFAULT 0,
+            cost_released numeric(20, 2) NOT NULL DEFAULT 0,
+            realized_pnl numeric(20, 2) NOT NULL DEFAULT 0,
+            is_synthetic boolean NOT NULL,
+            executed_at timestamptz NOT NULL
+        );
+
+        CREATE TABLE IF NOT EXISTS bond_sell_idempotencies (
+            idempotency_key text PRIMARY KEY,
+            request_hash text NOT NULL,
+            sell_id uuid NOT NULL REFERENCES bond_sells(id),
+            fund_id uuid NOT NULL REFERENCES funds(id),
+            created_at timestamptz NOT NULL DEFAULT now()
+        );
+
         CREATE TABLE IF NOT EXISTS bond_positions (
             fund_id uuid NOT NULL REFERENCES funds(id),
             instrument_code text NOT NULL,
@@ -2540,6 +2617,154 @@ type FundRepository(connectionString: string) =
 
         Convert.ToHexString(SHA256.HashData(Encoding.UTF8.GetBytes(payload)))
 
+    let bondSellRecordFromReader (reader: DbDataReader) : BondSellRecord =
+        {
+            Id = reader.GetGuid(0)
+            FundId = reader.GetGuid(1)
+            InstrumentCode = reader.GetString(2)
+            BondName = if reader.IsDBNull(3) then None else Some(reader.GetString(3))
+            Quantity = reader.GetDecimal(4)
+            Price = reader.GetDecimal(5)
+            CleanPrice = reader.GetDecimal(6)
+            AccruedInterest = reader.GetDecimal(7)
+            ParValue = reader.GetDecimal(8)
+            SettlementDate = reader.GetFieldValue<DateOnly>(9)
+            TradeDate = reader.GetFieldValue<DateOnly>(10)
+            FeeAmount = reader.GetDecimal(11)
+            Proceeds = reader.GetDecimal(12)
+            CostReleased = reader.GetDecimal(13)
+            RealizedPnl = reader.GetDecimal(14)
+            IsSynthetic = reader.GetBoolean(15)
+            ExecutedAt = reader.GetFieldValue<DateTimeOffset>(16)
+        }
+
+    let bondSellColumns =
+        "id, fund_id, instrument_code, bond_name, quantity, price, clean_price, accrued_interest, par_value, settlement_date, trade_date, fee_amount, proceeds, cost_released, realized_pnl, is_synthetic, executed_at"
+
+    let insertBondSell connection transaction (record: BondSellRecord) =
+        use command =
+            commandWithTransaction
+                connection
+                transaction
+                """
+                INSERT INTO bond_sells
+                    (id, fund_id, instrument_code, bond_name, quantity, price, clean_price, accrued_interest,
+                     par_value, settlement_date, trade_date, fee_amount, proceeds, cost_released, realized_pnl,
+                     is_synthetic, executed_at)
+                VALUES
+                    (@id, @fund_id, @instrument_code, @bond_name, @quantity, @price, @clean_price, @accrued_interest,
+                     @par_value, @settlement_date, @trade_date, @fee_amount, @proceeds, @cost_released, @realized_pnl,
+                     @is_synthetic, @executed_at)
+                """
+
+        addParameter command "id" NpgsqlDbType.Uuid (box record.Id) |> ignore
+        addParameter command "fund_id" NpgsqlDbType.Uuid (box record.FundId) |> ignore
+        addParameter command "instrument_code" NpgsqlDbType.Text (box record.InstrumentCode) |> ignore
+
+        let nameParameter =
+            match record.BondName with
+            | Some name -> box name
+            | None -> box DBNull.Value
+
+        addParameter command "bond_name" NpgsqlDbType.Text nameParameter |> ignore
+        addParameter command "quantity" NpgsqlDbType.Numeric (box record.Quantity) |> ignore
+        addParameter command "price" NpgsqlDbType.Numeric (box record.Price) |> ignore
+        addParameter command "clean_price" NpgsqlDbType.Numeric (box record.CleanPrice) |> ignore
+        addParameter command "accrued_interest" NpgsqlDbType.Numeric (box record.AccruedInterest) |> ignore
+        addParameter command "par_value" NpgsqlDbType.Numeric (box record.ParValue) |> ignore
+        addParameter command "settlement_date" NpgsqlDbType.Date (box record.SettlementDate) |> ignore
+        addParameter command "trade_date" NpgsqlDbType.Date (box record.TradeDate) |> ignore
+        addParameter command "fee_amount" NpgsqlDbType.Numeric (box record.FeeAmount) |> ignore
+        addParameter command "proceeds" NpgsqlDbType.Numeric (box record.Proceeds) |> ignore
+        addParameter command "cost_released" NpgsqlDbType.Numeric (box record.CostReleased) |> ignore
+        addParameter command "realized_pnl" NpgsqlDbType.Numeric (box record.RealizedPnl) |> ignore
+        addParameter command "is_synthetic" NpgsqlDbType.Boolean (box record.IsSynthetic) |> ignore
+        addParameter command "executed_at" NpgsqlDbType.TimestampTz (box record.ExecutedAt) |> ignore
+        command.ExecuteNonQuery() |> ignore
+
+    let insertBondSellIdempotency connection transaction key requestHash sellId fundId =
+        use command =
+            commandWithTransaction
+                connection
+                transaction
+                """
+                INSERT INTO bond_sell_idempotencies (idempotency_key, request_hash, sell_id, fund_id)
+                VALUES (@idempotency_key, @request_hash, @sell_id, @fund_id)
+                """
+
+        addParameter command "idempotency_key" NpgsqlDbType.Text (box key) |> ignore
+        addParameter command "request_hash" NpgsqlDbType.Text (box requestHash) |> ignore
+        addParameter command "sell_id" NpgsqlDbType.Uuid (box sellId) |> ignore
+        addParameter command "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore
+        command.ExecuteNonQuery() |> ignore
+
+    let findBondSellIdempotency connection transaction key =
+        use command =
+            commandWithTransaction
+                connection
+                transaction
+                """
+                SELECT request_hash, fund_id, sell_id
+                FROM bond_sell_idempotencies
+                WHERE idempotency_key = @idempotency_key
+                """
+
+        addParameter command "idempotency_key" NpgsqlDbType.Text (box key) |> ignore
+
+        use reader = command.ExecuteReader()
+
+        if reader.Read() then
+            Some(reader.GetString(0), reader.GetGuid(1), reader.GetGuid(2))
+        else
+            None
+
+    let findBondSell connection transaction sellId =
+        use command =
+            commandWithTransaction
+                connection
+                transaction
+                $"""
+                SELECT {bondSellColumns}
+                FROM bond_sells
+                WHERE id = @id
+                """
+
+        addParameter command "id" NpgsqlDbType.Uuid (box sellId) |> ignore
+
+        use reader = command.ExecuteReader()
+
+        if reader.Read() then
+            Some(bondSellRecordFromReader reader)
+        else
+            None
+
+    let bondSellRequestHash (fundId: Guid) (command: BondSellCommand) =
+        let invariant = CultureInfo.InvariantCulture
+        let encoded (value: string) = sprintf "%d:%s" value.Length value
+        let name = command.BondName |> Option.defaultValue ""
+        let dateText (value: DateOnly) = value.ToString("yyyy-MM-dd", invariant)
+        let optionTradeDate = command.TradeDate |> Option.map dateText |> Option.defaultValue ""
+
+        let payload =
+            String.concat
+                "|"
+                [
+                    "bond-sell"
+                    encoded (fundId.ToString("D"))
+                    encoded command.InstrumentCode
+                    encoded name
+                    encoded (command.Quantity.ToString("G29", invariant))
+                    encoded (command.Price.ToString("G29", invariant))
+                    encoded (command.CleanPrice.ToString("G29", invariant))
+                    encoded (command.AccruedInterest.ToString("G29", invariant))
+                    encoded (command.ParValue.ToString("G29", invariant))
+                    encoded (dateText command.SettlementDate)
+                    encoded optionTradeDate
+                    encoded (command.FeeAmount.ToString("G29", invariant))
+                ]
+
+        Convert.ToHexString(SHA256.HashData(Encoding.UTF8.GetBytes(payload)))
+
     let sipPlanRecordFromReader (reader: DbDataReader) : SipPlanRecord =
         {
             Id = reader.GetGuid(0)
@@ -5590,8 +5815,8 @@ type FundRepository(connectionString: string) =
             BondCashflowWriteResult.BondCashflowInvalid "idempotency key cannot be empty"
         elif code.Length <> 6 || not (code |> Seq.forall Char.IsDigit) then
             BondCashflowWriteResult.BondCashflowInvalid "bond code must contain exactly six digits"
-        elif eventType <> "coupon" && eventType <> "maturity" then
-            BondCashflowWriteResult.BondCashflowInvalid "event type must be coupon or maturity"
+        elif eventType <> "coupon" && eventType <> "maturity" && eventType <> "redemption" then
+            BondCashflowWriteResult.BondCashflowInvalid "event type must be coupon, maturity or redemption"
         elif command.Quantity <= 0m then
             BondCashflowWriteResult.BondCashflowInvalid "quantity must be positive"
         elif command.Amount < 0m then
@@ -5676,7 +5901,7 @@ type FundRepository(connectionString: string) =
                             addParameter cashCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore
                             cashCommand.ExecuteNonQuery() |> ignore
 
-                            if normalized.EventType = "maturity" then
+                            if normalized.EventType = "maturity" || normalized.EventType = "redemption" then
                                 use removeCommand =
                                     commandWithTransaction
                                         connection
@@ -5722,6 +5947,211 @@ type FundRepository(connectionString: string) =
 
         records |> Seq.toList
 
+    member _.CreateBondSell(idempotencyKey: string, fundId: Guid, command: BondSellCommand, ?executedAtOverride: DateTimeOffset) : BondSellWriteResult =
+        let code = if isNull command.InstrumentCode then "" else command.InstrumentCode.Trim()
+
+        if String.IsNullOrWhiteSpace idempotencyKey then
+            BondSellWriteResult.BondSellInvalid "idempotency key cannot be empty"
+        elif code.Length <> 6 || not (code |> Seq.forall Char.IsDigit) then
+            BondSellWriteResult.BondSellInvalid "bond code must contain exactly six digits"
+        elif command.Quantity <= 0m then
+            BondSellWriteResult.BondSellInvalid "quantity must be positive"
+        elif command.Price <= 0m then
+            BondSellWriteResult.BondSellInvalid "price must be positive"
+        elif command.CleanPrice <= 0m then
+            BondSellWriteResult.BondSellInvalid "clean price must be positive"
+        elif command.ParValue <= 0m then
+            BondSellWriteResult.BondSellInvalid "par value must be positive"
+        elif command.AccruedInterest < 0m then
+            BondSellWriteResult.BondSellInvalid "accrued interest cannot be negative"
+        elif command.FeeAmount < 0m then
+            BondSellWriteResult.BondSellInvalid "fee cannot be negative"
+        else
+            let normalized = { command with InstrumentCode = code }
+            let fingerprint = bondSellRequestHash fundId normalized
+            use connection = new NpgsqlConnection(connectionString)
+            connection.Open()
+            use transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)
+
+            try
+                use lockCommand =
+                    commandWithTransaction
+                        connection
+                        (Some transaction)
+                        "SELECT pg_advisory_xact_lock(hashtext(@lock_key))"
+
+                addParameter lockCommand "lock_key" NpgsqlDbType.Text (box idempotencyKey) |> ignore
+                lockCommand.ExecuteNonQuery() |> ignore
+
+                match findBondSellIdempotency connection (Some transaction) idempotencyKey with
+                | Some(existingHash, existingFundId, sellId)
+                    when existingHash = fingerprint && existingFundId = fundId ->
+                    match findBondSell connection (Some transaction) sellId with
+                    | Some record ->
+                        transaction.Commit()
+                        BondSellWriteResult.BondSellReplayed record
+                    | None ->
+                        transaction.Rollback()
+                        BondSellWriteResult.BondSellInvalid "idempotency record references a missing sell"
+                | Some _ ->
+                    transaction.Rollback()
+                    BondSellWriteResult.BondSellIdempotencyConflict
+                | None ->
+                    match lockFundForOrder connection (Some transaction) fundId with
+                    | None ->
+                        transaction.Rollback()
+                        BondSellWriteResult.BondSellFundNotFound
+                    | Some isSynthetic ->
+                        let position =
+                            use positionQuery =
+                                commandWithTransaction
+                                    connection
+                                    (Some transaction)
+                                    "SELECT quantity, cost_cash FROM bond_positions WHERE fund_id = @fund_id AND instrument_code = @code FOR UPDATE"
+
+                            addParameter positionQuery "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore
+                            addParameter positionQuery "code" NpgsqlDbType.Text (box normalized.InstrumentCode) |> ignore
+                            use reader = positionQuery.ExecuteReader()
+                            if reader.Read() then Some(reader.GetDecimal(0), reader.GetDecimal(1)) else None
+
+                        match position with
+                        | None ->
+                            transaction.Rollback()
+                            BondSellWriteResult.BondSellInsufficientHoldings "fund does not hold this bond"
+                        | Some(positionQuantity, positionCost) ->
+                            if normalized.Quantity > positionQuantity then
+                                transaction.Rollback()
+
+                                BondSellWriteResult.BondSellInsufficientHoldings(
+                                    sprintf "cannot sell %O 张: only %O held" normalized.Quantity positionQuantity
+                                )
+                            else
+                                let executedAt = defaultArg executedAtOverride DateTimeOffset.UtcNow
+
+                                let gross =
+                                    Decimal.Round(
+                                        normalized.Quantity * normalized.Price * normalized.ParValue / 100m,
+                                        2,
+                                        MidpointRounding.AwayFromZero
+                                    )
+
+                                let proceeds = Decimal.Round(gross - normalized.FeeAmount, 2, MidpointRounding.AwayFromZero)
+
+                                if proceeds < 0m then
+                                    transaction.Rollback()
+                                    BondSellWriteResult.BondSellInvalid "fee exceeds gross proceeds"
+                                else
+                                    let costReleased =
+                                        if normalized.Quantity = positionQuantity then
+                                            positionCost
+                                        else
+                                            Decimal.Round(
+                                                positionCost * normalized.Quantity / positionQuantity,
+                                                2,
+                                                MidpointRounding.AwayFromZero
+                                            )
+
+                                    let realizedPnl = Decimal.Round(proceeds - costReleased, 2, MidpointRounding.AwayFromZero)
+
+                                    let record: BondSellRecord =
+                                        {
+                                            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
+                                            TradeDate = normalized.TradeDate |> Option.defaultValue normalized.SettlementDate
+                                            FeeAmount = normalized.FeeAmount
+                                            Proceeds = proceeds
+                                            CostReleased = costReleased
+                                            RealizedPnl = realizedPnl
+                                            IsSynthetic = isSynthetic
+                                            ExecutedAt = executedAt
+                                        }
+
+                                    insertBondSell connection (Some transaction) record
+                                    insertBondSellIdempotency connection (Some transaction) idempotencyKey fingerprint record.Id fundId
+
+                                    use cashCommand =
+                                        commandWithTransaction
+                                            connection
+                                            (Some transaction)
+                                            "UPDATE funds SET available_cash = available_cash + @proceeds WHERE id = @fund_id"
+
+                                    addParameter cashCommand "proceeds" NpgsqlDbType.Numeric (box proceeds) |> ignore
+                                    addParameter cashCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore
+                                    cashCommand.ExecuteNonQuery() |> ignore
+
+                                    if normalized.Quantity = positionQuantity then
+                                        use removeCommand =
+                                            commandWithTransaction
+                                                connection
+                                                (Some transaction)
+                                                "DELETE FROM bond_positions WHERE fund_id = @fund_id AND instrument_code = @code"
+
+                                        addParameter removeCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore
+                                        addParameter removeCommand "code" NpgsqlDbType.Text (box normalized.InstrumentCode) |> ignore
+                                        removeCommand.ExecuteNonQuery() |> ignore
+                                    else
+                                        use positionCommand =
+                                            commandWithTransaction
+                                                connection
+                                                (Some transaction)
+                                                """
+                                                UPDATE bond_positions
+                                                SET quantity = quantity - @quantity,
+                                                    cost_cash = cost_cash - @cost_released,
+                                                    last_traded_at = @last_traded_at
+                                                WHERE fund_id = @fund_id AND instrument_code = @code
+                                                """
+
+                                        addParameter positionCommand "quantity" NpgsqlDbType.Numeric (box normalized.Quantity) |> ignore
+                                        addParameter positionCommand "cost_released" NpgsqlDbType.Numeric (box costReleased) |> ignore
+                                        addParameter positionCommand "last_traded_at" NpgsqlDbType.TimestampTz (box executedAt) |> ignore
+                                        addParameter positionCommand "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore
+                                        addParameter positionCommand "code" NpgsqlDbType.Text (box normalized.InstrumentCode) |> ignore
+                                        positionCommand.ExecuteNonQuery() |> ignore
+
+                                    transaction.Commit()
+                                    BondSellWriteResult.BondSellCreated record
+            with error ->
+                try
+                    transaction.Rollback()
+                with _ ->
+                    ()
+
+                raise error
+
+    member _.GetBondSells(fundId: Guid) : BondSellRecord list =
+        use connection = new NpgsqlConnection(connectionString)
+        connection.Open()
+
+        use command =
+            commandWithTransaction
+                connection
+                None
+                $"""
+                SELECT {bondSellColumns}
+                FROM bond_sells
+                WHERE fund_id = @fund_id
+                ORDER BY executed_at, id
+                """
+
+        addParameter command "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore
+
+        use reader = command.ExecuteReader()
+        let records = ResizeArray<BondSellRecord>()
+
+        while reader.Read() do
+            records.Add(bondSellRecordFromReader reader)
+
+        records |> Seq.toList
+
     member _.GetBondPositions(fundId: Guid) : BondPositionRecord list =
         use connection = new NpgsqlConnection(connectionString)
         connection.Open()