Add stock buy to holdings minimal vertical slice (3d-21)

1 files changed, 351 insertions, 0 deletions

diff --git a/src/FundLab.Api/Persistence.fs b/src/FundLab.Api/Persistence.fs
index 6028b7c..ab3fe0a 100644
--- a/src/FundLab.Api/Persistence.fs
+++ b/src/FundLab.Api/Persistence.fs
@@ -289,6 +289,44 @@ type SipPlanCommand =
         Frequency: SipFrequency
     }
 
+type StockTradeCommand =
+    {
+        InstrumentCode: string
+        StockName: string option
+        Quantity: decimal
+        Price: decimal
+    }
+
+type StockTradeRecord =
+    {
+        Id: Guid
+        FundId: Guid
+        InstrumentCode: string
+        StockName: string option
+        Quantity: decimal
+        Price: decimal
+        CostCash: decimal
+        IsSynthetic: bool
+        ExecutedAt: DateTimeOffset
+    }
+
+type StockPositionRecord =
+    {
+        FundId: Guid
+        InstrumentCode: string
+        StockName: string option
+        Quantity: decimal
+        CostCash: decimal
+        LastTradedAt: DateTimeOffset
+    }
+
+type StockTradeWriteResult =
+    | StockTradeCreated of StockTradeRecord
+    | StockTradeReplayed of StockTradeRecord
+    | StockTradeIdempotencyConflict
+    | StockTradeInvalid of string
+    | StockTradeFundNotFound
+
 type SipPlanRecord =
     {
         Id: Guid
@@ -829,6 +867,36 @@ type FundRepository(connectionString: string) =
             created_at timestamptz NOT NULL DEFAULT now()
         );
 
+        CREATE TABLE IF NOT EXISTS stock_trades (
+            id uuid PRIMARY KEY,
+            fund_id uuid NOT NULL REFERENCES funds(id),
+            instrument_code text NOT NULL,
+            stock_name text NULL,
+            quantity numeric(28, 8) NOT NULL CHECK (quantity > 0),
+            price numeric(20, 4) NOT NULL CHECK (price > 0),
+            cost_cash numeric(20, 2) NOT NULL CHECK (cost_cash >= 0),
+            is_synthetic boolean NOT NULL,
+            executed_at timestamptz NOT NULL
+        );
+
+        CREATE TABLE IF NOT EXISTS stock_trade_idempotencies (
+            idempotency_key text PRIMARY KEY,
+            request_hash text NOT NULL,
+            trade_id uuid NOT NULL REFERENCES stock_trades(id),
+            fund_id uuid NOT NULL REFERENCES funds(id),
+            created_at timestamptz NOT NULL DEFAULT now()
+        );
+
+        CREATE TABLE IF NOT EXISTS stock_positions (
+            fund_id uuid NOT NULL REFERENCES funds(id),
+            instrument_code text NOT NULL,
+            stock_name text NULL,
+            quantity numeric(28, 8) NOT NULL CHECK (quantity > 0),
+            cost_cash numeric(20, 2) NOT NULL CHECK (cost_cash >= 0),
+            last_traded_at timestamptz NOT NULL,
+            PRIMARY KEY (fund_id, instrument_code)
+        );
+
         CREATE TABLE IF NOT EXISTS dividend_idempotencies (
             idempotency_key text PRIMARY KEY,
             request_hash text NOT NULL,
@@ -1687,6 +1755,123 @@ type FundRepository(connectionString: string) =
 
         Convert.ToHexString(SHA256.HashData(Encoding.UTF8.GetBytes(payload)))
 
+    let stockTradeRecordFromReader (reader: DbDataReader) : StockTradeRecord =
+        {
+            Id = reader.GetGuid(0)
+            FundId = reader.GetGuid(1)
+            InstrumentCode = reader.GetString(2)
+            StockName = if reader.IsDBNull(3) then None else Some(reader.GetString(3))
+            Quantity = reader.GetDecimal(4)
+            Price = reader.GetDecimal(5)
+            CostCash = reader.GetDecimal(6)
+            IsSynthetic = reader.GetBoolean(7)
+            ExecutedAt = reader.GetFieldValue<DateTimeOffset>(8)
+        }
+
+    let insertStockTrade connection transaction (trade: StockTradeRecord) =
+        use command =
+            commandWithTransaction
+                connection
+                transaction
+                """
+                INSERT INTO stock_trades
+                    (id, fund_id, instrument_code, stock_name, quantity, price, cost_cash, is_synthetic, executed_at)
+                VALUES
+                    (@id, @fund_id, @instrument_code, @stock_name, @quantity, @price, @cost_cash, @is_synthetic, @executed_at)
+                """
+
+        addParameter command "id" NpgsqlDbType.Uuid (box trade.Id) |> ignore
+        addParameter command "fund_id" NpgsqlDbType.Uuid (box trade.FundId) |> ignore
+        addParameter command "instrument_code" NpgsqlDbType.Text (box trade.InstrumentCode) |> ignore
+
+        let nameParameter =
+            match trade.StockName with
+            | Some name -> box name
+            | None -> box DBNull.Value
+
+        addParameter command "stock_name" NpgsqlDbType.Text nameParameter |> ignore
+        addParameter command "quantity" NpgsqlDbType.Numeric (box trade.Quantity) |> ignore
+        addParameter command "price" NpgsqlDbType.Numeric (box trade.Price) |> ignore
+        addParameter command "cost_cash" NpgsqlDbType.Numeric (box trade.CostCash) |> ignore
+        addParameter command "is_synthetic" NpgsqlDbType.Boolean (box trade.IsSynthetic) |> ignore
+        addParameter command "executed_at" NpgsqlDbType.TimestampTz (box trade.ExecutedAt) |> ignore
+        command.ExecuteNonQuery() |> ignore
+
+    let insertStockTradeIdempotency connection transaction key requestHash tradeId fundId =
+        use command =
+            commandWithTransaction
+                connection
+                transaction
+                """
+                INSERT INTO stock_trade_idempotencies (idempotency_key, request_hash, trade_id, fund_id)
+                VALUES (@idempotency_key, @request_hash, @trade_id, @fund_id)
+                """
+
+        addParameter command "idempotency_key" NpgsqlDbType.Text (box key) |> ignore
+        addParameter command "request_hash" NpgsqlDbType.Text (box requestHash) |> ignore
+        addParameter command "trade_id" NpgsqlDbType.Uuid (box tradeId) |> ignore
+        addParameter command "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore
+        command.ExecuteNonQuery() |> ignore
+
+    let findStockTradeIdempotency connection transaction key =
+        use command =
+            commandWithTransaction
+                connection
+                transaction
+                """
+                SELECT request_hash, fund_id, trade_id
+                FROM stock_trade_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 findStockTrade connection transaction tradeId =
+        use command =
+            commandWithTransaction
+                connection
+                transaction
+                """
+                SELECT id, fund_id, instrument_code, stock_name, quantity, price, cost_cash, is_synthetic, executed_at
+                FROM stock_trades
+                WHERE id = @id
+                """
+
+        addParameter command "id" NpgsqlDbType.Uuid (box tradeId) |> ignore
+
+        use reader = command.ExecuteReader()
+
+        if reader.Read() then
+            Some(stockTradeRecordFromReader reader)
+        else
+            None
+
+    let stockTradeRequestHash (fundId: Guid) (command: StockTradeCommand) =
+        let invariant = CultureInfo.InvariantCulture
+        let encoded (value: string) = sprintf "%d:%s" value.Length value
+        let name = command.StockName |> Option.defaultValue ""
+
+        let payload =
+            String.concat
+                "|"
+                [
+                    "stock-trade"
+                    encoded (fundId.ToString("D"))
+                    encoded command.InstrumentCode
+                    encoded name
+                    encoded (command.Quantity.ToString("G29", invariant))
+                    encoded (command.Price.ToString("G29", invariant))
+                ]
+
+        Convert.ToHexString(SHA256.HashData(Encoding.UTF8.GetBytes(payload)))
+
     let sipPlanRecordFromReader (reader: DbDataReader) : SipPlanRecord =
         {
             Id = reader.GetGuid(0)
@@ -4020,6 +4205,172 @@ type FundRepository(connectionString: string) =
 
                     raise error
 
+    member _.CreateStockTrade(idempotencyKey: string, fundId: Guid, command: StockTradeCommand, ?executedAtOverride: DateTimeOffset) : StockTradeWriteResult =
+        if String.IsNullOrWhiteSpace idempotencyKey then
+            StockTradeWriteResult.StockTradeInvalid "idempotency key cannot be empty"
+        else
+            let code = if isNull command.InstrumentCode then "" else command.InstrumentCode.Trim()
+
+            if code.Length <> 6 || not (code |> Seq.forall Char.IsDigit) then
+                StockTradeWriteResult.StockTradeInvalid "stock code must contain exactly six digits"
+            elif command.Quantity <= 0m then
+                StockTradeWriteResult.StockTradeInvalid "quantity must be positive"
+            elif command.Price <= 0m then
+                StockTradeWriteResult.StockTradeInvalid "price must be positive"
+            else
+                let normalized = { command with InstrumentCode = code }
+                let fingerprint = stockTradeRequestHash 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 findStockTradeIdempotency connection (Some transaction) idempotencyKey with
+                    | Some(existingHash, existingFundId, tradeId)
+                        when existingHash = fingerprint && existingFundId = fundId ->
+                        match findStockTrade connection (Some transaction) tradeId with
+                        | Some trade ->
+                            transaction.Commit()
+                            StockTradeWriteResult.StockTradeReplayed trade
+                        | None ->
+                            transaction.Rollback()
+                            StockTradeWriteResult.StockTradeInvalid "idempotency record references a missing trade"
+                    | Some _ ->
+                        transaction.Rollback()
+                        StockTradeWriteResult.StockTradeIdempotencyConflict
+                    | None ->
+                        match lockFundForOrder connection (Some transaction) fundId with
+                        | None ->
+                            transaction.Rollback()
+                            StockTradeWriteResult.StockTradeFundNotFound
+                        | Some isSynthetic ->
+                            let executedAt = defaultArg executedAtOverride DateTimeOffset.UtcNow
+                            let costCash = Decimal.Round(normalized.Quantity * normalized.Price, 2, MidpointRounding.AwayFromZero)
+
+                            let trade: StockTradeRecord =
+                                {
+                                    Id = Guid.NewGuid()
+                                    FundId = fundId
+                                    InstrumentCode = normalized.InstrumentCode
+                                    StockName = normalized.StockName
+                                    Quantity = normalized.Quantity
+                                    Price = normalized.Price
+                                    CostCash = costCash
+                                    IsSynthetic = isSynthetic
+                                    ExecutedAt = executedAt
+                                }
+
+                            insertStockTrade connection (Some transaction) trade
+                            insertStockTradeIdempotency connection (Some transaction) idempotencyKey fingerprint trade.Id fundId
+
+                            use positionCommand =
+                                commandWithTransaction
+                                    connection
+                                    (Some transaction)
+                                    """
+                                    INSERT INTO stock_positions
+                                        (fund_id, instrument_code, stock_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 = stock_positions.quantity + EXCLUDED.quantity,
+                                        cost_cash = stock_positions.cost_cash + EXCLUDED.cost_cash,
+                                        stock_name = COALESCE(EXCLUDED.stock_name, stock_positions.stock_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.StockName 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
+
+                            let persisted = { trade with ExecutedAt = executedAt }
+                            transaction.Commit()
+                            StockTradeWriteResult.StockTradeCreated persisted
+                with error ->
+                    try
+                        transaction.Rollback()
+                    with _ ->
+                        ()
+
+                    raise error
+
+    member _.GetStockTrades(fundId: Guid) : StockTradeRecord list =
+        use connection = new NpgsqlConnection(connectionString)
+        connection.Open()
+
+        use command =
+            commandWithTransaction
+                connection
+                None
+                """
+                SELECT id, fund_id, instrument_code, stock_name, quantity, price, cost_cash, is_synthetic, executed_at
+                FROM stock_trades
+                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<StockTradeRecord>()
+
+        while reader.Read() do
+            records.Add(stockTradeRecordFromReader reader)
+
+        records |> Seq.toList
+
+    member _.GetStockPositions(fundId: Guid) : StockPositionRecord list =
+        use connection = new NpgsqlConnection(connectionString)
+        connection.Open()
+
+        use command =
+            commandWithTransaction
+                connection
+                None
+                """
+                SELECT instrument_code, stock_name, quantity, cost_cash, last_traded_at
+                FROM stock_positions
+                WHERE fund_id = @fund_id
+                ORDER BY instrument_code
+                """
+
+        addParameter command "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore
+
+        use reader = command.ExecuteReader()
+        let records = ResizeArray<StockPositionRecord>()
+
+        while reader.Read() do
+            records.Add(
+                {
+                    FundId = fundId
+                    InstrumentCode = reader.GetString(0)
+                    StockName = if reader.IsDBNull(1) then None else Some(reader.GetString(1))
+                    Quantity = reader.GetDecimal(2)
+                    CostCash = reader.GetDecimal(3)
+                    LastTradedAt = reader.GetFieldValue<DateTimeOffset>(4)
+                }
+            )
+
+        records |> Seq.toList
+
     member _.GetCapitalDeposits(fundId: Guid) =
         use connection = new NpgsqlConnection(connectionString)
         connection.Open()