path: root/tests/FundLab.Api.Tests/OrderTests.fs

namespace FundLab.Api.Tests

open System
open System.Collections.Concurrent
open System.Net.Http
open System.Text.Json
open System.Threading.Tasks
open FundLab.Api
open Npgsql
open Xunit

[<Collection("postgres")>]
type OrderTests(fixture: PostgresFixture) =
    let repository () =
        let value = FundRepository(fixture.ConnectionString)
        value.EnsureSchema()
        value

    let seedInstrument () =
        let code = Random.Shared.Next(0, 1000000).ToString("D6")
        let payload =
            {
                Source = "akshare"
                SourceRevision = "akshare-test/eastmoney"
                CollectedAt = DateTimeOffset(2026, 9, 21, 10, 0, 0, TimeSpan.Zero)
                Instruments = [ { Code = code; Name = "订单测试基金"; FundType = None } ]
            }

        repository().UpsertInstruments(payload, "order-test-hash")
        code

    let createFund (initialCash: decimal) =
        let command =
            {
                Name = "订单测试 FOF"
                InitialCash = initialCash
                InitialUnitNav = 1.00000000m
                IsSynthetic = true
            }

        let key = fixture.Key(sprintf "order-fund-%s" (Guid.NewGuid().ToString("N")))

        match repository().CreateFund(key, command) with
        | FundWriteResult.Created fund -> fund.Id
        | other -> failwithf "unexpected fund creation result: %A" other

    let app () = App.createApplication (repository ())

    let orderBody fundCode amount fee =
        sprintf "{\"fundCode\":\"%s\",\"amount\":\"%s\",\"feeAmount\":\"%s\"}" fundCode amount fee

    let postOrder (api) fundId idempotencyKey body =
        PersistenceTestHelpers.invoke
            api
            "POST"
            (sprintf "/api/funds/%O/orders" fundId)
            [
                "Authorization", "Bearer test-token"
                "Idempotency-Key", idempotencyKey
            ]
            body

    let getOrderList (api) fundId =
        PersistenceTestHelpers.invoke
            api
            "GET"
            (sprintf "/api/funds/%O/orders" fundId)
            [ "Authorization", "Bearer test-token" ]
            ""

    let getFund (api) fundId =
        PersistenceTestHelpers.invoke
            api
            "GET"
            (sprintf "/api/funds/%O" fundId)
            [ "Authorization", "Bearer test-token" ]
            ""

    let jsonProperty (body: string) (name: string) =
        use document = JsonDocument.Parse(body)
        document.RootElement.GetProperty(name).GetString()

    let orderCount (body: string) =
        use document = JsonDocument.Parse(body)
        document.RootElement.GetArrayLength()

    [<Fact>]
    member _.``subscription order reserves cash and stays visible as pending``() =
        let code = seedInstrument ()
        let fundId = createFund 1000.00m
        let api = app ()
        let key = fixture.Key("order-happy-path")

        let status, body =
            postOrder api fundId key (orderBody code "100.00" "1.50")

        Assert.Equal(201, status)
        Assert.Contains(sprintf "\"fundCode\":\"%s\"" code, body)
        Assert.Contains("\"amount\":\"100.00\"", body)
        Assert.Contains("\"feeAmount\":\"1.50\"", body)
        Assert.Contains("\"reservedTotal\":\"101.50\"", body)
        Assert.Contains("\"status\":\"submitted\"", body)
        Assert.Contains(sprintf "\"fundId\":\"%O\"" fundId, body)

        let orderId = PersistenceTestHelpers.responseId body

        let fundStatus, fundBody = getFund api fundId
        Assert.Equal(200, fundStatus)
        Assert.Contains("\"availableCash\":\"898.50\"", fundBody)
        Assert.Contains("\"reservedCash\":\"101.50\"", fundBody)

        let listStatus, listBody = getOrderList api fundId
        Assert.Equal(200, listStatus)
        Assert.Equal(1, orderCount listBody)
        Assert.Contains(sprintf "\"id\":\"%O\"" orderId, listBody)

    [<Fact>]
    member _.``subscription order replays the same order for an identical idempotency key``() =
        let code = seedInstrument ()
        let fundId = createFund 500.00m
        let api = app ()
        let key = fixture.Key("order-replay")
        let body = orderBody code "50.00" "2.50"

        let createStatus, createResponse = postOrder api fundId key body
        Assert.Equal(201, createStatus)
        let orderId = PersistenceTestHelpers.responseId createResponse

        let replayStatus, replayResponse = postOrder api fundId key body
        Assert.Equal(200, replayStatus)
        Assert.Equal(orderId, PersistenceTestHelpers.responseId replayResponse)

        let _, fundBody = getFund api fundId
        Assert.Contains("\"availableCash\":\"447.50\"", fundBody)
        Assert.Contains("\"reservedCash\":\"52.50\"", fundBody)

        let _, listBody = getOrderList api fundId
        Assert.Equal(1, orderCount listBody)

    [<Fact>]
    member _.``subscription order rejects conflicting reuse of an idempotency key``() =
        let code = seedInstrument ()
        let fundId = createFund 300.00m
        let api = app ()
        let key = fixture.Key("order-conflict")

        let _, _ = postOrder api fundId key (orderBody code "30.00" "3.00")

        let conflictStatus, conflictBody =
            postOrder api fundId key (orderBody code "31.00" "3.00")

        Assert.Equal(409, conflictStatus)
        Assert.Contains("IDEMPOTENCY_CONFLICT", conflictBody)

        let _, fundBody = getFund api fundId
        Assert.Contains("\"availableCash\":\"267.00\"", fundBody)
        Assert.Contains("\"reservedCash\":\"33.00\"", fundBody)

    [<Fact>]
    member _.``subscription order validates amount fee and catalog input``() =
        let code = seedInstrument ()
        let fundId = createFund 1000.00m
        let api = app ()
        let key = fixture.Key("order-validation")

        let invalidBodies =
            [
                orderBody code "0.00" "1.00"
                orderBody code "-5.00" "1.00"
                orderBody code "10.005" "1.00"
                orderBody code "10.00" "-0.01"
                orderBody code "10.00" "1.005"
                "{\"amount\":\"10.00\",\"feeAmount\":\"1.00\"}"
                "{\"fundCode\":123,\"amount\":\"10.00\",\"feeAmount\":\"1.00\"}"
                "{\"fundCode\":\"" + code + "\",\"amount\":\"abc\",\"feeAmount\":\"1.00\"}"
                "[]"
                ""
            ]

        for invalidBody in invalidBodies do
            let status, responseBody = postOrder api fundId key invalidBody
            Assert.Equal(400, status)
            Assert.Contains("INVALID_ORDER_REQUEST", responseBody)

        let _, fundBody = getFund api fundId
        Assert.Contains("\"availableCash\":\"1000.00\"", fundBody)
        Assert.Contains("\"reservedCash\":\"0.00\"", fundBody)

        let _, listBody = getOrderList api fundId
        Assert.Equal(0, orderCount listBody)

        let boundaryStatus, boundaryBody =
            postOrder api fundId key (orderBody code "0.01" "0.00")

        Assert.Equal(201, boundaryStatus)
        Assert.Contains("\"reservedTotal\":\"0.01\"", boundaryBody)

        let _, fundAfterBoundary = getFund api fundId
        Assert.Contains("\"availableCash\":\"999.99\"", fundAfterBoundary)
        Assert.Contains("\"reservedCash\":\"0.01\"", fundAfterBoundary)

    [<Fact>]
    member _.``subscription order requires authentication and an idempotency key``() =
        let fundId = createFund 100.00m
        let api = app ()
        let body = orderBody "000001" "10.00" "1.00"

        let unauthorizedStatus, _ =
            PersistenceTestHelpers.invoke
                api
                "POST"
                (sprintf "/api/funds/%O/orders" fundId)
                [ "Idempotency-Key", fixture.Key("order-unauthorized") ]
                body

        Assert.Equal(401, unauthorizedStatus)

        let missingKeyStatus, missingKeyBody =
            PersistenceTestHelpers.invoke
                api
                "POST"
                (sprintf "/api/funds/%O/orders" fundId)
                [ "Authorization", "Bearer test-token" ]
                body

        Assert.Equal(400, missingKeyStatus)
        Assert.Contains("INVALID_ORDER_REQUEST", missingKeyBody)

    [<Fact>]
    member _.``subscription order rejects unknown funds and unknown fund codes without cash movement``() =
        let code = seedInstrument ()
        let fundId = createFund 800.00m
        let api = app ()

        let invalidIdStatus, invalidIdBody =
            PersistenceTestHelpers.invoke
                api
                "POST"
                "/api/funds/not-a-guid/orders"
                [
                    "Authorization", "Bearer test-token"
                    "Idempotency-Key", fixture.Key("order-bad-id")
                ]
                (orderBody code "10.00" "1.00")

        Assert.Equal(400, invalidIdStatus)
        Assert.Contains("INVALID_FUND_ID", invalidIdBody)

        let unknownFundStatus, unknownFundBody =
            postOrder api (Guid.NewGuid()) (fixture.Key("order-unknown-fund")) (orderBody code "10.00" "1.00")

        Assert.Equal(404, unknownFundStatus)
        Assert.Contains("FUND_NOT_FOUND", unknownFundBody)

        let unknownCodeStatus, unknownCodeBody =
            postOrder api fundId (fixture.Key("order-unknown-code")) (orderBody "999999" "10.00" "1.00")

        Assert.Equal(404, unknownCodeStatus)
        Assert.Contains("INSTRUMENT_NOT_FOUND", unknownCodeBody)

        let _, fundBody = getFund api fundId
        Assert.Contains("\"availableCash\":\"800.00\"", fundBody)
        Assert.Contains("\"reservedCash\":\"0.00\"", fundBody)

        let _, listBody = getOrderList api fundId
        Assert.Equal(0, orderCount listBody)

        let missingListStatus, _ = getOrderList api (Guid.NewGuid())
        Assert.Equal(404, missingListStatus)

    [<Fact>]
    member _.``subscription order refuses insufficient available cash atomically``() =
        let code = seedInstrument ()
        let fundId = createFund 10.00m
        let api = app ()

        let status, body = postOrder api fundId (fixture.Key("order-insufficient")) (orderBody code "6.00" "5.00")

        Assert.Equal(409, status)
        Assert.Contains("INSUFFICIENT_FUNDS", body)

        let _, fundBody = getFund api fundId
        Assert.Contains("\"availableCash\":\"10.00\"", fundBody)
        Assert.Contains("\"reservedCash\":\"0.00\"", fundBody)

        let _, listBody = getOrderList api fundId
        Assert.Equal(0, orderCount listBody)

    [<Fact>]
    member _.``concurrent subscription orders reserve exactly the affordable amount``() =
        let code = seedInstrument ()
        let fundId = createFund 10.00m
        let api = app ()
        let body = orderBody code "3.50" "0.50"
        let statuses = ConcurrentBag<int>()

        Parallel.For(
            0,
            8,
            fun index ->
                let key = fixture.Key(sprintf "order-concurrent-%s-%d" (Guid.NewGuid().ToString("N")) index)
                let status, _ = postOrder api fundId key body
                statuses.Add(status)
        )
        |> ignore

        Assert.Equal(2, (statuses |> Seq.filter (fun status -> status = 201) |> Seq.length))
        Assert.Equal(6, (statuses |> Seq.filter (fun status -> status = 409) |> Seq.length))

        let _, fundBody = getFund api fundId
        Assert.Contains("\"availableCash\":\"2.00\"", fundBody)
        Assert.Contains("\"reservedCash\":\"8.00\"", fundBody)

        let _, listBody = getOrderList api fundId
        Assert.Equal(2, orderCount listBody)

    [<Fact>]
    member _.``real Kestrel API preserves subscription orders and idempotency across process restart``() =
        let code = seedInstrument ()
        let key = fixture.Key("order-restart")

        use firstProcess = new ApiProcess(fixture.ConnectionString)
        PersistenceTestHelpers.waitForApi firstProcess

        use client = new HttpClient()
        client.BaseAddress <- Uri(firstProcess.BaseAddress)

        let fundBody =
            "{\"name\":\"订单重启 FOF\",\"initialCash\":\"100.00\",\"initialUnitNav\":\"1.00000000\",\"isSynthetic\":true}"

        let fundStatus, fundResponse =
            PersistenceTestHelpers.send
                client
                "POST"
                "/api/funds"
                (Some "test-token")
                (Some(fixture.Key("order-restart-fund")))
                fundBody

        Assert.Equal(201, fundStatus)
        let fundId = PersistenceTestHelpers.responseId fundResponse

        let createStatus, createResponse =
            PersistenceTestHelpers.send
                client
                "POST"
                (sprintf "/api/funds/%O/orders" fundId)
                (Some "test-token")
                (Some key)
                (orderBody code "60.00" "4.00")

        Assert.Equal(201, createStatus)
        Assert.Contains("\"reservedTotal\":\"64.00\"", createResponse)
        Assert.Contains("\"status\":\"submitted\"", createResponse)
        let orderId = PersistenceTestHelpers.responseId createResponse

        firstProcess.Stop() |> ignore

        use restartedProcess = new ApiProcess(fixture.ConnectionString)
        PersistenceTestHelpers.waitForApi restartedProcess

        use restartedClient = new HttpClient()
        restartedClient.BaseAddress <- Uri(restartedProcess.BaseAddress)

        let restartedFundStatus, restartedFundBody =
            PersistenceTestHelpers.send
                restartedClient
                "GET"
                (sprintf "/api/funds/%O" fundId)
                (Some "test-token")
                None
                ""

        Assert.Equal(200, restartedFundStatus)
        Assert.Contains("\"availableCash\":\"36.00\"", restartedFundBody)
        Assert.Contains("\"reservedCash\":\"64.00\"", restartedFundBody)

        let restartedOrderListStatus, restartedOrderListBody =
            PersistenceTestHelpers.send
                restartedClient
                "GET"
                (sprintf "/api/funds/%O/orders" fundId)
                (Some "test-token")
                None
                ""

        Assert.Equal(200, restartedOrderListStatus)
        Assert.Equal(1, orderCount restartedOrderListBody)
        Assert.Contains(sprintf "\"id\":\"%O\"" orderId, restartedOrderListBody)

        let replayStatus, replayResponse =
            PersistenceTestHelpers.send
                restartedClient
                "POST"
                (sprintf "/api/funds/%O/orders" fundId)
                (Some "test-token")
                (Some key)
                (orderBody code "60.00" "4.00")

        Assert.Equal(200, replayStatus)
        Assert.Equal(orderId, PersistenceTestHelpers.responseId replayResponse)

    [<Fact>]
    member _.``zero fee subscription order is accepted and replays``() =
        let code = seedInstrument ()
        let fundId = createFund 200.00m
        let api = app ()
        let key = fixture.Key("order-zero-fee")

        let status, body =
            postOrder api fundId key (orderBody code "50.00" "0.00")

        Assert.Equal(201, status)
        Assert.Contains("\"amount\":\"50.00\"", body)
        Assert.Contains("\"feeAmount\":\"0.00\"", body)
        Assert.Contains("\"reservedTotal\":\"50.00\"", body)
        Assert.Contains("\"status\":\"submitted\"", body)
        let orderId = PersistenceTestHelpers.responseId body

        let replayStatus, replayBody = postOrder api fundId key (orderBody code "50.00" "0.00")
        Assert.Equal(200, replayStatus)
        Assert.Equal(orderId, PersistenceTestHelpers.responseId replayBody)

        let _, fundBody = getFund api fundId
        Assert.Contains("\"availableCash\":\"150.00\"", fundBody)
        Assert.Contains("\"reservedCash\":\"50.00\"", fundBody)

        let _, listBody = getOrderList api fundId
        Assert.Equal(1, orderCount listBody)

    [<Fact>]
    member _.``database constraint rejects negative fee but allows exact zero fee``() =
        let code = seedInstrument ()
        let fundId = createFund 100.00m
        repository () |> ignore

        use connection = new NpgsqlConnection(fixture.ConnectionString)
        connection.Open()

        let insertOrder (fee: decimal) (reservedTotal: decimal) =
            use command = connection.CreateCommand()
            command.CommandText <-
                """
                INSERT INTO subscription_orders
                    (id, fund_id, fund_code, amount, fee_amount, reserved_total, status, is_synthetic)
                VALUES
                    (@id, @fund_id, @fund_code, @amount, @fee_amount, @reserved_total, 'submitted', true)
                """

            command.Parameters.Add("id", NpgsqlTypes.NpgsqlDbType.Uuid).Value <- box (Guid.NewGuid())
            command.Parameters.Add("fund_id", NpgsqlTypes.NpgsqlDbType.Uuid).Value <- box fundId
            command.Parameters.Add("fund_code", NpgsqlTypes.NpgsqlDbType.Text).Value <- box code
            command.Parameters.Add("amount", NpgsqlTypes.NpgsqlDbType.Numeric).Value <- box 10.00m
            command.Parameters.Add("fee_amount", NpgsqlTypes.NpgsqlDbType.Numeric).Value <- box fee
            command.Parameters.Add("reserved_total", NpgsqlTypes.NpgsqlDbType.Numeric).Value <- box reservedTotal
            command.ExecuteNonQuery() |> ignore

        Assert.Throws<Npgsql.PostgresException>(fun () -> insertOrder -1.00m 9.00m) |> ignore

        insertOrder 0.00m 10.00m

        let negativeCount =
            PersistenceTestHelpers.queryCount
                fixture.ConnectionString
                "SELECT count(*) FROM subscription_orders WHERE fund_id = @fund_id AND fee_amount < 0"
                [ "fund_id", box fundId, NpgsqlTypes.NpgsqlDbType.Uuid ]

        let zeroCount =
            PersistenceTestHelpers.queryCount
                fixture.ConnectionString
                "SELECT count(*) FROM subscription_orders WHERE fund_id = @fund_id AND fee_amount = 0"
                [ "fund_id", box fundId, NpgsqlTypes.NpgsqlDbType.Uuid ]

        Assert.Equal(0L, negativeCount)
        Assert.Equal(1L, zeroCount)

type ConfirmationPolicyTests() =
    let shanghai (hour: int) (minute: int) (second: int) (day: int) =
        DateTimeOffset(2026, 9, day, hour, minute, second, TimeSpan.FromHours(8.0))

    let navQuote navDate nav collectedAt publishedAt : ConfirmationPolicy.NavQuote =
        { NavDate = navDate
          Nav = nav
          CollectedAt = collectedAt
          PublishedAt = publishedAt }

    [<Fact>]
    member _.``trade date is the shanghai date before the 15:00 cutoff``() =
        let submitted = shanghai 14 59 59 21
        Assert.Equal(DateOnly(2026, 9, 21), ConfirmationPolicy.tradeDateFor submitted)

    [<Fact>]
    member _.``trade date rolls to the next day at the 15:00 cutoff``() =
        let submitted = shanghai 15 0 0 21
        Assert.Equal(DateOnly(2026, 9, 22), ConfirmationPolicy.tradeDateFor submitted)

    [<Fact>]
    member _.``trade date is derived from shanghai wall clock regardless of offset``() =
        let utcBeforeCutoff = DateTimeOffset(2026, 9, 21, 6, 59, 59, TimeSpan.Zero)
        let utcAtCutoff = DateTimeOffset(2026, 9, 21, 7, 0, 0, TimeSpan.Zero)
        Assert.Equal(DateOnly(2026, 9, 21), ConfirmationPolicy.tradeDateFor utcBeforeCutoff)
        Assert.Equal(DateOnly(2026, 9, 22), ConfirmationPolicy.tradeDateFor utcAtCutoff)

    [<Fact>]
    member _.``friday cutoff rolls the trade date to monday``() =
        let submitted = shanghai 15 0 0 25
        Assert.Equal(DateOnly(2026, 9, 28), ConfirmationPolicy.tradeDateFor submitted)

    [<Fact>]
    member _.``weekend submissions roll the trade date to monday``() =
        Assert.Equal(DateOnly(2026, 9, 28), ConfirmationPolicy.tradeDateFor (shanghai 10 0 0 26))
        Assert.Equal(DateOnly(2026, 9, 28), ConfirmationPolicy.tradeDateFor (shanghai 15 0 0 27))

    [<Fact>]
    member _.``modeled trading day covers weekends only``() =
        Assert.False(ConfirmationPolicy.isModeledTradingDay (DateOnly(2026, 9, 26)))
        Assert.True(ConfirmationPolicy.isModeledTradingDay (DateOnly(2026, 9, 21)))
        Assert.True(ConfirmationPolicy.isModeledTradingDay (DateOnly(2026, 10, 1)))

    [<Fact>]
    member _.``units truncate at eight decimals and invested cash at cents``() =
        match ConfirmationPolicy.compute 1000.00m 2.375m with
        | Ok confirmed ->
            Assert.Equal(421.05263157m, confirmed.Units)
            Assert.Equal(999.99m, confirmed.InvestedCash)
            Assert.Equal(0.01m, confirmed.ResidualCash)
        | Error reason -> failwithf "unexpected deferral: %s" reason

    [<Fact>]
    member _.``exact division keeps the full amount invested``() =
        match ConfirmationPolicy.compute 100.00m 2.5m with
        | Ok confirmed ->
            Assert.Equal(40m, confirmed.Units)
            Assert.Equal(100.00m, confirmed.InvestedCash)
            Assert.Equal(0.00m, confirmed.ResidualCash)
        | Error reason -> failwithf "unexpected deferral: %s" reason

    [<Fact>]
    member _.``amounts below one unit are deferred``() =
        Assert.Equal(
            Error "amount converts to zero units at this unit nav",
            ConfirmationPolicy.compute 0.00000001m 2.5m
        )

    [<Fact>]
    member _.``unit amounts beyond database precision are deferred``() =
        Assert.Equal(
            Error "unit amount exceeds database precision",
            ConfirmationPolicy.compute 999999999999999999.99m 0.00000001m
        )

    [<Fact>]
    member _.``non positive unit nav is rejected``() =
        Assert.Equal(Error "unit nav must be positive", ConfirmationPolicy.compute 100.00m 0m)

    [<Fact>]
    member _.``usable nav quotes pass the no lookahead checks``() =
        let quote =
            navQuote (DateOnly(2026, 9, 21)) 2.5m (shanghai 14 0 0 21) (Some(shanghai 13 0 0 21))

        let now = shanghai 15 0 0 21

        Assert.Equal(
            None,
            ConfirmationPolicy.navDeferralReason quote (DateOnly(2026, 9, 21)) (DateOnly(2026, 9, 21)) now
        )

    [<Fact>]
    member _.``quotes observed before confirmation without a publish timestamp are usable``() =
        let quote = navQuote (DateOnly(2026, 9, 21)) 2.5m (shanghai 14 0 0 21) None
        let now = shanghai 15 0 0 21

        Assert.Equal(
            None,
            ConfirmationPolicy.navDeferralReason quote (DateOnly(2026, 9, 21)) (DateOnly(2026, 9, 21)) now
        )

    [<Fact>]
    member _.``quotes collected after the confirmation moment defer without backdating``() =
        let quote = navQuote (DateOnly(2026, 9, 21)) 2.5m (shanghai 16 0 0 21) None
        let now = shanghai 15 0 0 21

        Assert.Equal(
            Some "nav for trade date 2026-09-21 is not yet collected",
            ConfirmationPolicy.navDeferralReason quote (DateOnly(2026, 9, 21)) (DateOnly(2026, 9, 21)) now
        )

    [<Fact>]
    member _.``stale nav quotes defer confirmation``() =
        let quote = navQuote (DateOnly(2026, 9, 20)) 2.5m (shanghai 14 0 0 21) None
        let now = shanghai 15 0 0 21

        Assert.Equal(
            Some "nav for trade date 2026-09-21 is not available yet",
            ConfirmationPolicy.navDeferralReason quote (DateOnly(2026, 9, 21)) (DateOnly(2026, 9, 21)) now
        )

    [<Fact>]
    member _.``future dated nav quotes defer confirmation``() =
        let quote = navQuote (DateOnly(2026, 9, 22)) 2.5m (shanghai 14 0 0 21) None
        let now = shanghai 15 0 0 21

        Assert.Equal(
            Some "nav for trade date 2026-09-21 is dated in the future",
            ConfirmationPolicy.navDeferralReason quote (DateOnly(2026, 9, 21)) (DateOnly(2026, 9, 21)) now
        )

    [<Fact>]
    member _.``unpublished nav quotes defer confirmation``() =
        let quote =
            navQuote (DateOnly(2026, 9, 21)) 2.5m (shanghai 14 0 0 21) (Some(shanghai 16 0 0 21))

        let now = shanghai 15 0 0 21

        Assert.Equal(
            Some "nav for trade date 2026-09-21 is not yet published",
            ConfirmationPolicy.navDeferralReason quote (DateOnly(2026, 9, 21)) (DateOnly(2026, 9, 21)) now
        )

    [<Fact>]
    member _.``holiday trade dates without a matching nav stay pending``() =
        let quote = navQuote (DateOnly(2026, 9, 30)) 2.5m (shanghai 10 0 0 30) None
        let now = DateTimeOffset(2026, 10, 1, 15, 0, 0, TimeSpan.FromHours(8.0))

        Assert.Equal(
            Some "nav for trade date 2026-10-01 is not available yet",
            ConfirmationPolicy.navDeferralReason quote (DateOnly(2026, 10, 1)) (DateOnly(2026, 10, 1)) now
        )

[<Collection("postgres")>]
type SubscriptionConfirmationTests(fixture: PostgresFixture) =
    let sharedRepository =
        lazy
            let value = FundRepository(fixture.ConnectionString)
            value.EnsureSchema()
            value

    let repository () = sharedRepository.Value

    let seedInstrument () =
        let code = Random.Shared.Next(0, 1000000).ToString("D6")
        let payload =
            {
                Source = "akshare"
                SourceRevision = "akshare-test/eastmoney"
                CollectedAt = DateTimeOffset(2026, 9, 21, 10, 0, 0, TimeSpan.Zero)
                Instruments = [ { Code = code; Name = "确认测试基金"; FundType = None } ]
            }

        repository().UpsertInstruments(payload, "confirm-test-hash")
        code

    let createFund (initialCash: decimal) =
        let command =
            {
                Name = "确认测试 FOF"
                InitialCash = initialCash
                InitialUnitNav = 1.00000000m
                IsSynthetic = true
            }

        let key = fixture.Key(sprintf "confirm-fund-%s" (Guid.NewGuid().ToString("N")))

        match repository().CreateFund(key, command) with
        | FundWriteResult.Created fund -> fund.Id
        | other -> failwithf "unexpected fund creation result: %A" other

    let app () = App.createApplication (repository ())

    let postOrder fundId idempotencyKey amount fee =
        let body =
            sprintf
                "{\"fundCode\":\"%s\",\"amount\":\"%s\",\"feeAmount\":\"%s\"}"
                (seedInstrument ())
                amount
                fee

        PersistenceTestHelpers.invoke
            (app ())
            "POST"
            (sprintf "/api/funds/%O/orders" fundId)
            [
                "Authorization", "Bearer test-token"
                "Idempotency-Key", idempotencyKey
            ]
            body
        |> fun (status, response) ->
            if status <> 201 then failwithf "unexpected order status %d: %s" status response
            PersistenceTestHelpers.responseId response

    let truncateMicroseconds (moment: DateTimeOffset) =
        let utc = moment.ToUniversalTime()
        DateTimeOffset(utc.Ticks - (utc.Ticks % 10L), TimeSpan.Zero)

    let insertQuoteOnDate (code: string) (nav: decimal) (navDate: DateOnly) (collectedAt: DateTimeOffset) (publishedAt: DateTimeOffset option) =
        let revision = sprintf "akshare-test/%O" (Guid.NewGuid())
        let payload: MarketDataNavPayload =
            {
                Source = "akshare"
                SourceRevision = revision
                CollectedAt = collectedAt
                Code = code
                Observations =
                    [
                        {
                            NavDate = navDate
                            PublishedAt = publishedAt
                            Nav = nav
                            AccumulatedNav = Some nav
                            DailyReturn = Some 0.0m
                        }
                    ]
            }

        repository().UpsertNavObservations(payload, sprintf "confirm-quote-hash/%s" revision)

    let insertQuote (code: string) (nav: decimal) (collectedAt: DateTimeOffset) (publishedAt: DateTimeOffset option) =
        insertQuoteOnDate code nav (ConfirmationPolicy.tradeDateFor DateTimeOffset.Now) collectedAt publishedAt

    let execute (sql: string) (parameters: (string * obj * NpgsqlTypes.NpgsqlDbType) list) =
        use connection = new NpgsqlConnection(fixture.ConnectionString)
        connection.Open()
        use command = connection.CreateCommand()
        command.CommandText <- sql

        for name, value, dbType in parameters do
            let parameter = command.Parameters.Add(name, dbType)
            parameter.Value <- value

        command.ExecuteNonQuery() |> ignore

    let scalarDecimal (sql: string) (parameters: (string * obj * NpgsqlTypes.NpgsqlDbType) list) =
        use connection = new NpgsqlConnection(fixture.ConnectionString)
        connection.Open()
        use command = connection.CreateCommand()
        command.CommandText <- sql

        for name, value, dbType in parameters do
            let parameter = command.Parameters.Add(name, dbType)
            parameter.Value <- value

        command.ExecuteScalar() :?> decimal

    let scalarText (sql: string) (parameters: (string * obj * NpgsqlTypes.NpgsqlDbType) list) =
        use connection = new NpgsqlConnection(fixture.ConnectionString)
        connection.Open()
        use command = connection.CreateCommand()
        command.CommandText <- sql

        for name, value, dbType in parameters do
            let parameter = command.Parameters.Add(name, dbType)
            parameter.Value <- value

        command.ExecuteScalar() :?> string

    let scalarTimestamp (sql: string) (parameters: (string * obj * NpgsqlTypes.NpgsqlDbType) list) =
        use connection = new NpgsqlConnection(fixture.ConnectionString)
        connection.Open()
        use command = connection.CreateCommand()
        command.CommandText <- sql

        for name, value, dbType in parameters do
            let parameter = command.Parameters.Add(name, dbType)
            parameter.Value <- value

        let value = command.ExecuteScalar() :?> DateTime
        DateTimeOffset(DateTime.SpecifyKind(value, DateTimeKind.Utc))

    let orderFundCode orderId =
        scalarText "SELECT fund_code FROM subscription_orders WHERE id = @order_id" [ "order_id", box orderId, NpgsqlTypes.NpgsqlDbType.Uuid ]

    let availableCash fundId =
        scalarDecimal "SELECT available_cash FROM funds WHERE id = @fund_id" [ "fund_id", box fundId, NpgsqlTypes.NpgsqlDbType.Uuid ]

    let reservedCash fundId =
        scalarDecimal "SELECT reserved_cash FROM funds WHERE id = @fund_id" [ "fund_id", box fundId, NpgsqlTypes.NpgsqlDbType.Uuid ]

    let orderStatus orderId =
        scalarDecimal
            "SELECT count(*)::numeric FROM subscription_orders WHERE id = @order_id AND status = 'confirmed'"
            [ "order_id", box orderId, NpgsqlTypes.NpgsqlDbType.Uuid ]
        |> int64

    let pendingOrderReason orderId =
        use connection = new NpgsqlConnection(fixture.ConnectionString)
        connection.Open()
        use command = connection.CreateCommand()
        command.CommandText <- "SELECT status || '|' || coalesce(pending_reason, '') FROM subscription_orders WHERE id = @order_id"
        let parameter = command.Parameters.Add("order_id", NpgsqlTypes.NpgsqlDbType.Uuid)
        parameter.Value <- box orderId
        command.ExecuteScalar() :?> string

    let confirmKey fundId orderId key = repository().ConfirmSubscriptionOrder(key, fundId, orderId)

    [<Fact>]
    member _.``confirmation persists quote evidence cash and positions atomically``() =
        let fundId = createFund 10000.00m
        let orderId = postOrder fundId "confirm-success-1" "1000.00" "0.00"
        let code = orderFundCode orderId
        let collected = truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-10.0))
        insertQuote code 2.5m collected None
        let tradeDate = ConfirmationPolicy.tradeDateFor DateTimeOffset.Now
        let key = fixture.Key("confirm-success-1")

        match confirmKey fundId orderId key with
        | OrderConfirmed confirmed ->
            Assert.Equal("confirmed", confirmed.Status)
            Assert.Equal(Some key, confirmed.ConfirmIdempotencyKey)
            Assert.Equal(Some 400.00000000m, confirmed.ConfirmedUnits)
            Assert.Equal(Some 1000.00m, confirmed.ConfirmedInvestedCash)
            Assert.Equal(Some 0.00m, confirmed.ConfirmedResidualCash)
            Assert.True(confirmed.ConfirmedAt.IsSome)

            match confirmed.ConfirmedQuote with
            | Some quote ->
                Assert.Equal(2.5m, quote.Nav)
                Assert.Equal(tradeDate, quote.NavDate)
                Assert.Equal("akshare", quote.Source)
                Assert.Equal(collected, quote.CollectedAt)
                Assert.Equal(None, quote.PublishedAt)
            | None -> failwith "confirmed quote evidence missing"

            Assert.Equal(9000.00m, availableCash fundId)
            Assert.Equal(0.00m, reservedCash fundId)

            let positions = repository().GetFundPositions(fundId)
            Assert.Equal(1, positions.Length)
            let position = positions.[0]
            Assert.Equal(400.00000000m, position.Units)
            Assert.Equal(1000.00m, position.CostCash)
            Assert.Equal(Some 2.5m, position.ValuationNav)
            Assert.Equal(Some tradeDate, position.ValuationNavDate)
        | other -> failwithf "unexpected confirm result: %A" other

    [<Fact>]
    member _.``replaying the same idempotency key never double executes``() =
        let fundId = createFund 10000.00m
        let orderId = postOrder fundId "confirm-replay-1" "1000.00" "0.00"
        let code = orderFundCode orderId
        let collected = truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-10.0))
        insertQuote code 2.5m collected None
        let key = fixture.Key("confirm-replay-key")

        let originalHash, originalFirstSeen =
            match confirmKey fundId orderId key with
            | OrderConfirmed confirmed ->
                match confirmed.ConfirmedQuote with
                | Some quote -> quote.PayloadHash, quote.FirstSeenAt
                | None -> failwith "confirmed quote evidence missing"
            | other -> failwithf "unexpected first confirm result: %A" other

        let cashAfterFirst = availableCash fundId

        insertQuote code 9.9m (truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-8.0))) None

        let supersededHash =
            scalarText
                "SELECT source_payload_hash FROM fund_nav_observations WHERE instrument_code = @code AND nav_date = @nav_date"
                [ "code", box code, NpgsqlTypes.NpgsqlDbType.Text
                  "nav_date", box (ConfirmationPolicy.tradeDateFor DateTimeOffset.Now), NpgsqlTypes.NpgsqlDbType.Date ]

        Assert.NotEqual<string>(originalHash, supersededHash)

        match confirmKey fundId orderId key with
        | ConfirmReplayed replayed ->
            Assert.Equal("confirmed", replayed.Status)

            match replayed.ConfirmedQuote with
            | Some quote ->
                Assert.Equal(2.5m, quote.Nav)
                Assert.Equal(collected, quote.CollectedAt)
                Assert.Equal(None, quote.PublishedAt)
                Assert.Equal(originalHash, quote.PayloadHash)
                Assert.Equal(originalFirstSeen, quote.FirstSeenAt)
            | None -> failwith "replayed quote evidence missing"

            Assert.Equal(400.00000000m, replayed.ConfirmedUnits |> Option.get)
        | other -> failwithf "unexpected replay result: %A" other

        Assert.Equal(cashAfterFirst, availableCash fundId)
        Assert.Equal(1, (repository().GetFundPositions(fundId)).Length)
        Assert.Equal(1L, PersistenceTestHelpers.queryCount fixture.ConnectionString "SELECT count(*) FROM subscription_order_events WHERE order_id = @order_id" [ "order_id", box orderId, NpgsqlTypes.NpgsqlDbType.Uuid ])

    [<Fact>]
    member _.``a confirmed order cannot be confirmed with a different key``() =
        let fundId = createFund 10000.00m
        let orderId = postOrder fundId "confirm-other-key-1" "1000.00" "0.00"
        let code = orderFundCode orderId
        let collected = truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-10.0))
        insertQuote code 2.5m collected None
        let cashAfterFirst =
            match confirmKey fundId orderId (fixture.Key("confirm-other-key-first")) with
            | OrderConfirmed _ -> availableCash fundId
            | other -> failwithf "unexpected first confirm result: %A" other

        match confirmKey fundId orderId (fixture.Key("confirm-other-key-second")) with
        | ConfirmAlreadyConfirmed -> Assert.Equal(cashAfterFirst, availableCash fundId)
        | other -> failwithf "unexpected second confirm result: %A" other

    [<Fact>]
    member _.``a consumed confirm key cannot bind to another order``() =
        let fundId = createFund 10000.00m
        let firstOrder = postOrder fundId "confirm-consume-1" "1000.00" "0.00"
        let code = orderFundCode firstOrder
        let collected = truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-10.0))
        insertQuote code 2.5m collected None
        let key = fixture.Key("confirm-consume-shared-key")

        match confirmKey fundId firstOrder key with
        | OrderConfirmed _ -> ()
        | other -> failwithf "unexpected first confirm result: %A" other

        let secondOrder = postOrder fundId "confirm-consume-2" "500.00" "0.00"

        match confirmKey fundId secondOrder key with
        | ConfirmIdempotencyConflict ->
            Assert.Equal(0L, orderStatus secondOrder)
            Assert.Equal(400.00000000m, (repository().GetFundPositions(fundId)).[0].Units)
        | other -> failwithf "unexpected conflict result: %A" other

    [<Fact>]
    member _.``mid confirmation failure rolls back every write and the same key can retry``() =
        let fundId = createFund 10000.00m
        let orderId = postOrder fundId "confirm-rollback-1" "1000.00" "0.00"
        let code = orderFundCode orderId
        let collected = truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-10.0))
        insertQuote code 2.5m collected None
        let key = fixture.Key("confirm-rollback-key")

        execute
            "UPDATE subscription_orders SET reserved_total = reserved_total + 1 WHERE id = @order_id"
            [ "order_id", box orderId, NpgsqlTypes.NpgsqlDbType.Uuid ]

        Assert.Throws<Exception>(fun () -> confirmKey fundId orderId key |> ignore) |> ignore

        Assert.Equal(0L, orderStatus orderId)
        Assert.Equal(0L, PersistenceTestHelpers.queryCount fixture.ConnectionString "SELECT count(*) FROM subscription_confirm_idempotencies WHERE order_id = @order_id" [ "order_id", box orderId, NpgsqlTypes.NpgsqlDbType.Uuid ])
        Assert.Equal(0L, PersistenceTestHelpers.queryCount fixture.ConnectionString "SELECT count(*) FROM subscription_order_events WHERE order_id = @order_id" [ "order_id", box orderId, NpgsqlTypes.NpgsqlDbType.Uuid ])
        Assert.Equal(0L, PersistenceTestHelpers.queryCount fixture.ConnectionString "SELECT count(*) FROM fund_positions WHERE fund_id = @fund_id" [ "fund_id", box fundId, NpgsqlTypes.NpgsqlDbType.Uuid ])
        Assert.Equal(9000.00m, availableCash fundId)
        Assert.Equal(1000.00m, reservedCash fundId)

        execute
            "UPDATE subscription_orders SET reserved_total = reserved_total - 1 WHERE id = @order_id"
            [ "order_id", box orderId, NpgsqlTypes.NpgsqlDbType.Uuid ]

        match confirmKey fundId orderId key with
        | OrderConfirmed confirmed ->
            Assert.Equal(400.00000000m, confirmed.ConfirmedUnits |> Option.get)
            Assert.Equal(9000.00m, availableCash fundId)
        | other -> failwithf "unexpected retry result: %A" other

    [<Fact>]
    member _.``missing nav stays pending without consuming the key and confirms later``() =
        let fundId = createFund 10000.00m
        let orderId = postOrder fundId "confirm-pending-1" "1000.00" "0.00"
        let key = fixture.Key("confirm-pending-key")

        match confirmKey fundId orderId key with
        | ConfirmPendingNav pending ->
            Assert.Equal("pending_nav", pending.Status)
            Assert.True(pending.PendingReason.IsSome)
            Assert.True(pending.PendingReason.Value.Contains("not available yet"))
            Assert.Equal(None, pending.ConfirmIdempotencyKey)
            Assert.Equal(None, pending.ConfirmedAt)
            Assert.Equal(None, pending.ConfirmedQuote)
        | other -> failwithf "unexpected pending result: %A" other

        let code = orderFundCode orderId
        let collected = truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-10.0))
        insertQuote code 2.5m collected None

        match confirmKey fundId orderId key with
        | OrderConfirmed confirmed ->
            Assert.Equal(Some key, confirmed.ConfirmIdempotencyKey)
            Assert.Equal(9000.00m, availableCash fundId)

            match confirmed.ConfirmedQuote with
            | Some quote ->
                Assert.Equal(ConfirmationPolicy.tradeDateFor DateTimeOffset.Now, quote.NavDate)
                Assert.Equal(collected, quote.CollectedAt)
            | None -> failwith "late confirmed quote evidence missing"
        | other -> failwithf "unexpected late confirm result: %A" other

    [<Fact>]
    member _.``a revision first observed after the confirmation attempt stays pending without moving cash or holdings``() =
        let fundId = createFund 10000.00m
        let orderId = postOrder fundId "confirm-evidence-1" "1000.00" "0.00"
        let code = orderFundCode orderId
        let collected = truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-10.0))
        insertQuote code 2.5m collected None

        execute
            "UPDATE fund_nav_observation_evidence SET first_seen_at = @future WHERE instrument_code = @code AND nav_date = @nav_date"
            [ "future", box (truncateMicroseconds (DateTimeOffset.Now.AddSeconds(5.0))), NpgsqlTypes.NpgsqlDbType.TimestampTz
              "code", box code, NpgsqlTypes.NpgsqlDbType.Text
              "nav_date", box (ConfirmationPolicy.tradeDateFor DateTimeOffset.Now), NpgsqlTypes.NpgsqlDbType.Date ]

        match confirmKey fundId orderId (fixture.Key("confirm-evidence-key")) with
        | ConfirmPendingNav pending ->
            Assert.Equal("pending_nav", pending.Status)
            Assert.True(pending.PendingReason.IsSome)
            Assert.True(pending.PendingReason.Value.Contains("first observed after"))
            Assert.Equal(None, pending.ConfirmIdempotencyKey)
            Assert.Equal(None, pending.ConfirmedAt)
            Assert.Equal(None, pending.ConfirmedQuote)
        | other -> failwithf "unexpected evidence-deferral result: %A" other

        Assert.Equal(9000.00m, availableCash fundId)
        Assert.Equal(1000.00m, reservedCash fundId)
        Assert.Equal(0, (repository().GetFundPositions(fundId)).Length)
        Assert.Equal(0L, PersistenceTestHelpers.queryCount fixture.ConnectionString "SELECT count(*) FROM subscription_order_events WHERE order_id = @order_id" [ "order_id", box orderId, NpgsqlTypes.NpgsqlDbType.Uuid ])
        Assert.Equal(0L, PersistenceTestHelpers.queryCount fixture.ConnectionString "SELECT count(*) FROM subscription_confirm_idempotencies WHERE order_id = @order_id" [ "order_id", box orderId, NpgsqlTypes.NpgsqlDbType.Uuid ])

    [<Fact>]
    member _.``reobserving the same revision keeps the earliest first seen and a changed revision is not backdated``() =
        let code = seedInstrument ()
        let navDate = ConfirmationPolicy.tradeDateFor DateTimeOffset.Now
        let observationRow =
            "SELECT first_seen_at FROM fund_nav_observations WHERE instrument_code = @code AND nav_date = @nav_date"
        let rowParameters =
            [ "code", box code, NpgsqlTypes.NpgsqlDbType.Text
              "nav_date", box navDate, NpgsqlTypes.NpgsqlDbType.Date ]

        let buildPayload (revision: string) (collectedAt: DateTimeOffset) (nav: decimal) : MarketDataNavPayload =
            {
                Source = "akshare"
                SourceRevision = revision
                CollectedAt = collectedAt
                Code = code
                Observations =
                    [
                        {
                            NavDate = navDate
                            PublishedAt = None
                            Nav = nav
                            AccumulatedNav = Some nav
                            DailyReturn = Some 0.0m
                        }
                    ]
            }

        repository().UpsertNavObservations(buildPayload "akshare-test/first-seen-r1" (truncateMicroseconds (DateTimeOffset.Now.AddHours(-6.0))) 2.5m, "confirm-first-seen-hash-r1")

        let earliest = truncateMicroseconds (DateTimeOffset.Now.AddHours(-2.0))

        execute
            "UPDATE fund_nav_observations SET first_seen_at = @first_seen WHERE instrument_code = @code AND nav_date = @nav_date"
            [ "first_seen", box earliest, NpgsqlTypes.NpgsqlDbType.TimestampTz
              "code", box code, NpgsqlTypes.NpgsqlDbType.Text
              "nav_date", box navDate, NpgsqlTypes.NpgsqlDbType.Date ]

        repository().UpsertNavObservations(buildPayload "akshare-test/first-seen-r1" (truncateMicroseconds (DateTimeOffset.Now.AddHours(-3.0))) 2.5m, "confirm-first-seen-hash-r1")
        Assert.Equal(earliest, scalarTimestamp observationRow rowParameters)

        let marker = truncateMicroseconds DateTimeOffset.UtcNow
        let r2Collected = truncateMicroseconds (DateTimeOffset.Now.AddHours(-5.0))
        repository().UpsertNavObservations(buildPayload "akshare-test/first-seen-r2" r2Collected 3.3m, "confirm-first-seen-hash-r2")

        Assert.Equal("confirm-first-seen-hash-r2", scalarText "SELECT source_payload_hash FROM fund_nav_observations WHERE instrument_code = @code AND nav_date = @nav_date" rowParameters)
        Assert.Equal(3.3m, scalarDecimal "SELECT nav FROM fund_nav_observations WHERE instrument_code = @code AND nav_date = @nav_date" rowParameters)
        Assert.Equal(r2Collected, scalarTimestamp "SELECT source_collected_at FROM fund_nav_observations WHERE instrument_code = @code AND nav_date = @nav_date" rowParameters)

        let firstSeenAfterChange = scalarTimestamp observationRow rowParameters
        Assert.True(
            firstSeenAfterChange >= marker.AddSeconds(-1.0),
            sprintf "expected non-backdated first_seen_at after revision change, got %O (marker %O, earliest %O)" firstSeenAfterChange marker earliest
        )

    [<Fact>]
    member _.``re-observing a superseded revision preserves its earliest first seen evidence``() =
        let code = seedInstrument ()
        let navDate = ConfirmationPolicy.tradeDateFor DateTimeOffset.Now
        let rowParameters =
            [ "code", box code, NpgsqlTypes.NpgsqlDbType.Text
              "nav_date", box navDate, NpgsqlTypes.NpgsqlDbType.Date ]

        let buildPayload (revision: string) (collectedAt: DateTimeOffset) (nav: decimal) : MarketDataNavPayload =
            {
                Source = "akshare"
                SourceRevision = revision
                CollectedAt = collectedAt
                Code = code
                Observations =
                    [
                        {
                            NavDate = navDate
                            PublishedAt = None
                            Nav = nav
                            AccumulatedNav = Some nav
                            DailyReturn = Some 0.0m
                        }
                    ]
            }

        repository().UpsertNavObservations(buildPayload "akshare-test/aba-r1" (truncateMicroseconds (DateTimeOffset.Now.AddHours(-6.0))) 2.5m, "confirm-aba-hash-r1")

        let evidenceAFirst =
            scalarTimestamp
                "SELECT first_seen_at FROM fund_nav_observation_evidence WHERE instrument_code = @code AND nav_date = @nav_date AND source_payload_hash = @hash"
                (rowParameters @ [ "hash", box "confirm-aba-hash-r1", NpgsqlTypes.NpgsqlDbType.Text ])

        repository().UpsertNavObservations(buildPayload "akshare-test/aba-r2" (truncateMicroseconds (DateTimeOffset.Now.AddHours(-3.0))) 3.3m, "confirm-aba-hash-r2")
        Assert.Equal(3.3m, scalarDecimal "SELECT nav FROM fund_nav_observations WHERE instrument_code = @code AND nav_date = @nav_date" rowParameters)

        repository().UpsertNavObservations(buildPayload "akshare-test/aba-r1" (truncateMicroseconds (DateTimeOffset.Now.AddHours(-6.0))) 2.5m, "confirm-aba-hash-r1")

        Assert.Equal(evidenceAFirst, scalarTimestamp "SELECT first_seen_at FROM fund_nav_observation_evidence WHERE instrument_code = @code AND nav_date = @nav_date AND source_payload_hash = @hash" (rowParameters @ [ "hash", box "confirm-aba-hash-r1", NpgsqlTypes.NpgsqlDbType.Text ]))
        Assert.Equal("confirm-aba-hash-r1", scalarText "SELECT source_payload_hash FROM fund_nav_observations WHERE instrument_code = @code AND nav_date = @nav_date" rowParameters)
        Assert.Equal(2.5m, scalarDecimal "SELECT nav FROM fund_nav_observations WHERE instrument_code = @code AND nav_date = @nav_date" rowParameters)

    [<Fact>]
    member _.``retrying after the cutoff keeps the original order trade date``() =
        let fundId = createFund 10000.00m
        let orderId = postOrder fundId "confirm-overnight-1" "1000.00" "0.00"
        let code = orderFundCode orderId
        let originalTradeDate = ConfirmationPolicy.tradeDateFor (DateTimeOffset.Now.AddDays(-1.0))
        let originalCollected = truncateMicroseconds (DateTimeOffset.Now.AddDays(-1.0).AddHours(-2.0))
        insertQuoteOnDate code 3.7m originalTradeDate originalCollected None

        execute
            "UPDATE subscription_orders SET submitted_at = submitted_at - interval '1 day', trade_date = @trade_date WHERE id = @order_id"
            [ "trade_date", box originalTradeDate, NpgsqlTypes.NpgsqlDbType.Date; "order_id", box orderId, NpgsqlTypes.NpgsqlDbType.Uuid ]

        match confirmKey fundId orderId (fixture.Key("confirm-overnight-key")) with
        | OrderConfirmed confirmed ->
            Assert.Equal(originalTradeDate, confirmed.TradeDate)

            match confirmed.ConfirmedQuote with
            | Some quote ->
                Assert.Equal(originalTradeDate, quote.NavDate)
                Assert.Equal(3.7m, quote.Nav)
                Assert.Equal(originalCollected, quote.CollectedAt)
            | None -> failwith "overnight quote evidence missing"

            Assert.Equal(Some 270.27027027m, confirmed.ConfirmedUnits)
            Assert.Equal(Some 999.99m, confirmed.ConfirmedInvestedCash)
            Assert.Equal(9000.01m, availableCash fundId)
        | other -> failwithf "unexpected overnight confirm result: %A" other

    [<Fact>]
    member _.``quotes collected after the confirmation moment stay pending``() =
        let fundId = createFund 10000.00m
        let orderId = postOrder fundId "confirm-future-collect-1" "1000.00" "0.00"
        let code = orderFundCode orderId
        let futureCollected = truncateMicroseconds (DateTimeOffset.Now.AddHours(1.0))
        insertQuote code 2.5m futureCollected None

        match confirmKey fundId orderId (fixture.Key("confirm-future-collect-key")) with
        | ConfirmPendingNav pending ->
            Assert.Equal("pending_nav", pending.Status)
            Assert.True(pending.PendingReason.Value.Contains("not yet collected"))
            Assert.Equal(None, pending.ConfirmIdempotencyKey)
        | other -> failwithf "unexpected future collected result: %A" other

    [<Fact>]
    member _.``concurrent confirms with distinct keys execute exactly once``() =
        let fundId = createFund 10000.00m
        let orderId = postOrder fundId "confirm-parallel-1" "1000.00" "0.00"
        let code = orderFundCode orderId
        let collected = truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-10.0))
        insertQuote code 2.5m collected None

        let results =
            [| 1..4 |]
            |> Array.map (fun index ->
                Task.Run(fun () ->
                    confirmKey fundId orderId (fixture.Key(sprintf "confirm-parallel-key-%d" index))))
            |> Task.WhenAll
            |> fun task -> task.GetAwaiter().GetResult()
            |> Array.toList

        let confirmedCount =
            results
            |> List.filter (function
                | OrderConfirmed _ -> true
                | _ -> false)
            |> List.length

        Assert.Equal(1, confirmedCount)

        for result in results do
            match result with
            | OrderConfirmed _ -> ()
            | ConfirmAlreadyConfirmed
            | ConfirmIdempotencyConflict -> ()
            | other -> failwithf "unexpected concurrent result: %A" other

        Assert.Equal(9000.00m, availableCash fundId)
        Assert.Equal(0.00m, reservedCash fundId)
        Assert.Equal(1, (repository().GetFundPositions(fundId)).Length)
        Assert.Equal(400.00000000m, (repository().GetFundPositions(fundId)).[0].Units)

    [<Fact>]
    member _.``a rebuilt repository re reads the persisted evidence``() =
        let fundId = createFund 10000.00m
        let orderId = postOrder fundId "confirm-rebuild-1" "1000.00" "0.00"
        let code = orderFundCode orderId
        let collected = truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-10.0))
        insertQuote code 2.5m collected (Some(truncateMicroseconds (DateTimeOffset.Now.AddSeconds(-5.0))))
        let key = fixture.Key("confirm-rebuild-key")

        match confirmKey fundId orderId key with
        | OrderConfirmed _ -> ()
        | other -> failwithf "unexpected confirm result: %A" other

        let rebuilt = FundRepository(fixture.ConnectionString)
        rebuilt.EnsureSchema()

        let orders = rebuilt.GetSubscriptionOrders(fundId)
        Assert.Equal(1, orders.Length)
        let order = orders.[0]

        match order.ConfirmedQuote with
        | Some quote ->
            Assert.Equal(2.5m, quote.Nav)
            Assert.Equal("akshare", quote.Source)
            Assert.Equal(collected, quote.CollectedAt)
            Assert.True(quote.PublishedAt.IsSome)
        | None -> failwith "rebuilt quote evidence missing"

        Assert.Equal(Some key, order.ConfirmIdempotencyKey)
        Assert.Equal(Some 400.00000000m, order.ConfirmedUnits)
        Assert.Equal(Some 1000.00m, order.ConfirmedInvestedCash)
        Assert.Equal(Some 0.00m, order.ConfirmedResidualCash)

        let positions = rebuilt.GetFundPositions(fundId)
        Assert.Equal(1, positions.Length)
        Assert.Equal(400.00000000m, positions.[0].Units)