source: server/trade.go@ a531b45

main
Last change on this file since a531b45 was 9577c79, checked in by Stefan <trsunovstefan@…>, 13 days ago

add reserved_quantity and modify the phases, add v_03.png and v_03.xml for P1
  • Property mode set to 100644
File size: 7.2 KB
Line 
1package main
2
3import (
4 "database/sql"
5 "fmt"
6 "strconv"
7
8 "bp_project/server/db"
9)
10
11// PlaceOrder - UC0004 (buy) / UC0005 (sell)
12// Market order that executes immediately against the latest price.
13// Runs inside a single database transaction so the orders, holdings,
14// users.balance and transactions tables always agree.
15//
16// The order still passes through 'open' before 'executed'. Placing it
17// reserves whatever it commits — on a sell, the crypto being sold, tracked in
18// holdings.reserved_quantity — before anything is actually moved, so a
19// second order against the same holding can never be granted the same units
20// twice. Because only market orders are implemented, reserve and settle
21// happen inside this one transaction rather than across two commits; a
22// future limit-order matcher would split them into a second transaction
23// later, without needing a schema change.
24func PlaceOrder(s *Session, side string) {
25 if side != "buy" && side != "sell" {
26 fmt.Println("Invalid side.")
27 return
28 }
29 fmt.Printf("\n-- Place market %s order --\n", side)
30
31 m, err := ChooseMarket()
32 if err != nil {
33 fmt.Println(err)
34 return
35 }
36 price, err := LatestPrice(m.ID)
37 if err != nil {
38 fmt.Println(err)
39 return
40 }
41 fmt.Printf("Latest price for %s/%s = %.6f\n", m.Symbol, m.Quote, price)
42
43 qtyStr := prompt("Quantity: ")
44 qty, err := strconv.ParseFloat(qtyStr, 64)
45 if err != nil || qty <= 0 {
46 fmt.Println("Invalid quantity.")
47 return
48 }
49 notional := qty * price
50
51 tx, err := db.DB.Begin()
52 if err != nil {
53 fmt.Println("Error:", err)
54 return
55 }
56 defer tx.Rollback()
57
58 // 1. record the order as 'open' — no trade has happened yet.
59 var orderID string
60 err = tx.QueryRow(
61 `INSERT INTO orders (user_id, market_id, side, type, status, quantity, price)
62 VALUES ($1, $2, $3, 'market', 'open', $4, $5)
63 RETURNING id`,
64 s.UserID, m.ID, side, qty, price,
65 ).Scan(&orderID)
66 if err != nil {
67 fmt.Println("Error creating order:", err)
68 return
69 }
70
71 if side == "buy" {
72 // check balance
73 var avail float64
74 if err := tx.QueryRow(
75 `SELECT available_balance FROM users WHERE id = $1 FOR UPDATE`,
76 s.UserID).Scan(&avail); err != nil {
77 fmt.Println("Error:", err)
78 return
79 }
80 if avail < notional {
81 fmt.Printf("Insufficient funds: need %.4f, have %.4f\n", notional, avail)
82 return
83 }
84
85 // debit balance
86 if _, err := tx.Exec(
87 `UPDATE users
88 SET available_balance = available_balance - $1,
89 invested_balance = invested_balance + $1,
90 updated_at = now()
91 WHERE id = $2`,
92 notional, s.UserID,
93 ); err != nil {
94 fmt.Println("Error:", err)
95 return
96 }
97
98 // a buy never reserves crypto, only ever adds it — upsert holding
99 // with running weighted average
100 if err := upsertHoldingOnBuy(tx, s.UserID, m.CryptoID, qty, price); err != nil {
101 fmt.Println("Error updating holding:", err)
102 return
103 }
104
105 // ledger entry
106 if _, err := tx.Exec(
107 `INSERT INTO transactions (user_id, type, amount, currency, related_order, description)
108 VALUES ($1, 'buy', $2, 'USD', $3, $4)`,
109 s.UserID, -notional, orderID,
110 fmt.Sprintf("Market buy %.4f %s @ %.6f", qty, m.Symbol, price),
111 ); err != nil {
112 fmt.Println("Error:", err)
113 return
114 }
115 } else {
116 // sell: lock the holding and check what is actually free to sell —
117 // quantity minus whatever another open order has already reserved.
118 var held, reserved, avgPrice float64
119 err := tx.QueryRow(
120 `SELECT quantity, reserved_quantity, avg_price FROM holdings
121 WHERE user_id = $1 AND crypto_id = $2 FOR UPDATE`,
122 s.UserID, m.CryptoID,
123 ).Scan(&held, &reserved, &avgPrice)
124 if err != nil && err != sql.ErrNoRows {
125 fmt.Println("Error:", err)
126 return
127 }
128 available := held - reserved
129 if err == sql.ErrNoRows || available < qty {
130 fmt.Printf("Insufficient holding: trying to sell %.4f, available %.4f (of %.4f held, %.4f reserved)\n",
131 qty, available, held, reserved)
132 return
133 }
134
135 // reserve: committed to this order, not yet removed from the position.
136 if _, err := tx.Exec(
137 `UPDATE holdings
138 SET reserved_quantity = reserved_quantity + $1,
139 updated_at = now()
140 WHERE user_id = $2 AND crypto_id = $3`,
141 qty, s.UserID, m.CryptoID,
142 ); err != nil {
143 fmt.Println("Error:", err)
144 return
145 }
146
147 // settle: a market order fills immediately, so release the
148 // reservation and remove the asset from the position in one step.
149 if _, err := tx.Exec(
150 `UPDATE holdings
151 SET quantity = quantity - $1,
152 reserved_quantity = reserved_quantity - $1,
153 updated_at = now()
154 WHERE user_id = $2 AND crypto_id = $3`,
155 qty, s.UserID, m.CryptoID,
156 ); err != nil {
157 fmt.Println("Error:", err)
158 return
159 }
160
161 // credit balance; reduce invested by cost basis (avg_price * qty)
162 costBasis := avgPrice * qty
163 if _, err := tx.Exec(
164 `UPDATE users
165 SET available_balance = available_balance + $1,
166 invested_balance = GREATEST(invested_balance - $2, 0),
167 updated_at = now()
168 WHERE id = $3`,
169 notional, costBasis, s.UserID,
170 ); err != nil {
171 fmt.Println("Error:", err)
172 return
173 }
174
175 // ledger entry
176 if _, err := tx.Exec(
177 `INSERT INTO transactions (user_id, type, amount, currency, related_order, description)
178 VALUES ($1, 'sell', $2, 'USD', $3, $4)`,
179 s.UserID, notional, orderID,
180 fmt.Sprintf("Market sell %.4f %s @ %.6f", qty, m.Symbol, price),
181 ); err != nil {
182 fmt.Println("Error:", err)
183 return
184 }
185 }
186
187 // record the resulting market trade so the book reflects this fill
188 if _, err := tx.Exec(
189 `INSERT INTO market_trades (market_id, executed_at, price, quantity, side, source)
190 VALUES ($1, now(), $2, $3, $4, 'user')`,
191 m.ID, price, qty, side,
192 ); err != nil {
193 fmt.Println("Error:", err)
194 return
195 }
196
197 // settle the order itself: it has now actually been filled.
198 if _, err := tx.Exec(
199 `UPDATE orders SET status = 'executed', executed_at = now() WHERE id = $1`,
200 orderID,
201 ); err != nil {
202 fmt.Println("Error:", err)
203 return
204 }
205
206 if err := tx.Commit(); err != nil {
207 fmt.Println("Commit error:", err)
208 return
209 }
210 fmt.Printf("Order executed: %s %.4f %s @ %.6f (notional %.4f USD)\n",
211 side, qty, m.Symbol, price, notional)
212}
213
214// upsertHoldingOnBuy creates or updates a holding using running weighted-average price.
215//
216// This is a single statement that relies on UNIQUE (user_id, crypto_id): the new
217// weighted average is recomputed by the database in numeric arithmetic rather
218// than in Go float64, and no separate SELECT ... FOR UPDATE round-trip is
219// needed because ON CONFLICT DO UPDATE locks the conflicting row itself.
220// Every SET expression sees the pre-update row, so `holdings.quantity` below is
221// still the old quantity while the average is being computed.
222func upsertHoldingOnBuy(tx *sql.Tx, userID, cryptoID string, qty, price float64) error {
223 _, err := tx.Exec(
224 `INSERT INTO holdings (user_id, crypto_id, quantity, avg_price, updated_at)
225 VALUES ($1, $2, $3, $4, now())
226 ON CONFLICT (user_id, crypto_id) DO UPDATE
227 SET avg_price = (holdings.quantity * holdings.avg_price
228 + EXCLUDED.quantity * EXCLUDED.avg_price)
229 / (holdings.quantity + EXCLUDED.quantity),
230 quantity = holdings.quantity + EXCLUDED.quantity,
231 updated_at = now()`,
232 userID, cryptoID, qty, price,
233 )
234 return err
235}
Note: See TracBrowser for help on using the repository browser.