Index: server/cli.go =================================================================== --- server/cli.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/cli.go (revision 8b447efcef1acebccdc8fd064521da4adc58d220) @@ -76,4 +76,6 @@ fmt.Println("[8] Manage watchlist") fmt.Println("[9] Logout") + fmt.Println("[10] Report: top traders") + fmt.Println("[11] Report: market performance") fmt.Println("[0] Exit") switch prompt("> ") { @@ -94,4 +96,8 @@ case "8": ManageWatchlist(s) + case "10": + ShowTopTraders(s) + case "11": + ShowMarketPerformance(s) case "9": s.UserID = "" Index: server/db/schema_creation.sql =================================================================== --- server/db/schema_creation.sql (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/db/schema_creation.sql (revision 8b447efcef1acebccdc8fd064521da4adc58d220) @@ -59,13 +59,18 @@ -- ============================================================================ CREATE TABLE project.holdings ( - id uuid PRIMARY KEY DEFAULT gen_random_uuid(), - user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE, - crypto_id uuid NOT NULL REFERENCES project.crypto(id), - quantity numeric(20,4) NOT NULL CHECK (quantity >= 0), + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE, + crypto_id uuid NOT NULL REFERENCES project.crypto(id), + quantity numeric(20,4) NOT NULL CHECK (quantity >= 0), + -- Committed to the user's own open sell orders, not yet removed from the + -- position. quantity - reserved_quantity is what is actually free to + -- sell — the crypto-side equivalent of users.available_balance. + reserved_quantity numeric(20,4) NOT NULL DEFAULT 0 + CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity), -- Weighted-average entry price. NOT NULL so that the P/L arithmetic in -- v_portfolio can never silently produce NULL for an existing position. - avg_price numeric(18,6) NOT NULL DEFAULT 0 CHECK (avg_price >= 0), - created_at timestamptz NOT NULL DEFAULT now(), - updated_at timestamptz, + avg_price numeric(18,6) NOT NULL DEFAULT 0 CHECK (avg_price >= 0), + created_at timestamptz NOT NULL DEFAULT now(), + updated_at timestamptz, CONSTRAINT uq_holdings_user_crypto UNIQUE (user_id, crypto_id) ); @@ -184,4 +189,6 @@ c.symbol, h.quantity, + h.reserved_quantity, + (h.quantity - h.reserved_quantity) AS available_quantity, h.avg_price, lp.price AS current_price, @@ -192,2 +199,111 @@ LEFT JOIN project.markets m ON m.crypto_id = c.id AND m.quote_currency = 'USD' LEFT JOIN project.v_latest_prices lp ON lp.market_id = m.id; + +-- ============================================================================ +-- REPORTS (P6 — Complex DB Reports) +-- Both are single SELECT statements (with CTEs), wrapped as SQL functions so +-- they can be called as parameterised reports from the prototype instead of +-- being copy-pasted SQL text. See docs/P6-AdvancedReports/AdvancedReports.md. +-- ============================================================================ + +-- report_top_traders: realized trading performance per user over [p_from, p_to), +-- bucketed into quarters to measure how consistently each user was profitable. +CREATE OR REPLACE FUNCTION project.report_top_traders(p_from timestamptz, p_to timestamptz) +RETURNS TABLE ( + username varchar, + realized_pl numeric, + total_invested numeric, + roi_pct numeric, + profitable_periods bigint, + losing_periods bigint, + total_periods bigint, + consistency_pct numeric +) +LANGUAGE sql STABLE AS $$ + WITH period_pl AS ( + SELECT + t.user_id, + date_trunc('quarter', t.created_at) AS period, + SUM(t.amount) AS period_pl, + SUM(t.amount) FILTER (WHERE t.type = 'buy') AS period_buy + FROM project.transactions t + WHERE t.type IN ('buy', 'sell', 'fee') + AND t.created_at >= p_from + AND t.created_at < p_to + GROUP BY t.user_id, date_trunc('quarter', t.created_at) + ) + SELECT + u.username, + SUM(pp.period_pl) AS realized_pl, + ABS(SUM(pp.period_buy)) AS total_invested, + ROUND(SUM(pp.period_pl) / NULLIF(ABS(SUM(pp.period_buy)), 0) * 100, 2) AS roi_pct, + COUNT(*) FILTER (WHERE pp.period_pl > 0) AS profitable_periods, + COUNT(*) FILTER (WHERE pp.period_pl < 0) AS losing_periods, + COUNT(*) AS total_periods, + ROUND(COUNT(*) FILTER (WHERE pp.period_pl > 0)::numeric + / NULLIF(COUNT(*), 0) * 100, 2) AS consistency_pct + FROM period_pl pp + JOIN project.users u ON u.id = pp.user_id + GROUP BY u.id, u.username + ORDER BY realized_pl DESC; +$$; + +-- report_market_performance: trading activity and price behaviour per market +-- over [p_from, p_to). Volume/trade-count/price stats come from market_trades +-- (the complete tape — user fills and simulated fills alike); participating +-- users can only come from orders, since market_trades has no user_id column. +CREATE OR REPLACE FUNCTION project.report_market_performance(p_from timestamptz, p_to timestamptz) +RETURNS TABLE ( + symbol varchar, + quote_currency char(3), + total_volume numeric, + trade_count bigint, + avg_price numeric, + market_return_pct numeric, + price_volatility numeric, + participating_users bigint +) +LANGUAGE sql STABLE AS $$ + WITH trades AS ( + SELECT + market_id, price, quantity, executed_at, + FIRST_VALUE(price) OVER w AS first_price, + LAST_VALUE(price) OVER (PARTITION BY market_id ORDER BY executed_at + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_price + FROM project.market_trades + WHERE executed_at >= p_from AND executed_at < p_to + WINDOW w AS (PARTITION BY market_id ORDER BY executed_at) + ), + market_stats AS ( + SELECT + market_id, + SUM(quantity) AS total_volume, + COUNT(*) AS trade_count, + AVG(price) AS avg_price, + STDDEV(price) AS price_volatility, + MAX(first_price) AS first_price, + MAX(last_price) AS last_price + FROM trades + GROUP BY market_id + ), + participation AS ( + SELECT market_id, COUNT(DISTINCT user_id) AS participating_users + FROM project.orders + WHERE status = 'executed' AND executed_at >= p_from AND executed_at < p_to + GROUP BY market_id + ) + SELECT + c.symbol, + m.quote_currency, + ms.total_volume, + ms.trade_count, + ROUND(ms.avg_price, 6) AS avg_price, + ROUND((ms.last_price - ms.first_price) / NULLIF(ms.first_price, 0) * 100, 2) AS market_return_pct, + ROUND(COALESCE(ms.price_volatility, 0), 6) AS price_volatility, + COALESCE(p.participating_users, 0) AS participating_users + FROM market_stats ms + JOIN project.markets m ON m.id = ms.market_id + JOIN project.crypto c ON c.id = m.crypto_id + LEFT JOIN participation p ON p.market_id = ms.market_id + ORDER BY ms.total_volume DESC; +$$; Index: server/portfolio.go =================================================================== --- server/portfolio.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/portfolio.go (revision 8b447efcef1acebccdc8fd064521da4adc58d220) @@ -3,4 +3,5 @@ import ( "fmt" + "strings" "bp_project/server/db" @@ -13,4 +14,6 @@ `SELECT symbol, quantity, + COALESCE(reserved_quantity, 0), + COALESCE(available_quantity, quantity), COALESCE(avg_price, 0), COALESCE(current_price, 0), @@ -28,8 +31,9 @@ defer rows.Close() + header := fmt.Sprintf(" %-8s %12s %12s %12s %14s %14s %14s %14s", + "Symbol", "Quantity", "Reserved", "Available", "Avg buy", "Current", "Value", "Unrealised P/L") fmt.Println() - fmt.Printf(" %-8s %12s %14s %14s %14s %14s\n", - "Symbol", "Quantity", "Avg buy", "Current", "Value", "Unrealised P/L") - fmt.Println(" ------------------------------------------------------------------------------------") + fmt.Println(header) + fmt.Println(" " + strings.Repeat("-", len(header)-2)) var totalValue, totalPnL float64 @@ -37,11 +41,11 @@ for rows.Next() { var sym string - var qty, avg, cur, val, pnl float64 - if err := rows.Scan(&sym, &qty, &avg, &cur, &val, &pnl); err != nil { + var qty, reserved, avail, avg, cur, val, pnl float64 + if err := rows.Scan(&sym, &qty, &reserved, &avail, &avg, &cur, &val, &pnl); err != nil { fmt.Println("scan error:", err) return } - fmt.Printf(" %-8s %12.4f %14.6f %14.6f %14.4f %+14.4f\n", - sym, qty, avg, cur, val, pnl) + fmt.Printf(" %-8s %12.4f %12.4f %12.4f %14.6f %14.6f %14.4f %+14.4f\n", + sym, qty, reserved, avail, avg, cur, val, pnl) totalValue += val totalPnL += pnl @@ -52,7 +56,7 @@ return } - fmt.Println(" ------------------------------------------------------------------------------------") - fmt.Printf(" %-8s %12s %14s %14s %14.4f %+14.4f\n", - "TOTAL", "", "", "", totalValue, totalPnL) + fmt.Println(" " + strings.Repeat("-", len(header)-2)) + fmt.Printf(" %-8s %12s %12s %12s %14s %14s %14.4f %+14.4f\n", + "TOTAL", "", "", "", "", "", totalValue, totalPnL) // cash summary Index: server/trade.go =================================================================== --- server/trade.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/trade.go (revision 8b447efcef1acebccdc8fd064521da4adc58d220) @@ -13,4 +13,13 @@ // Runs inside a single database transaction so the orders, holdings, // users.balance and transactions tables always agree. +// +// The order still passes through 'open' before 'executed'. Placing it +// reserves whatever it commits — on a sell, the crypto being sold, tracked in +// holdings.reserved_quantity — before anything is actually moved, so a +// second order against the same holding can never be granted the same units +// twice. Because only market orders are implemented, reserve and settle +// happen inside this one transaction rather than across two commits; a +// future limit-order matcher would split them into a second transaction +// later, without needing a schema change. func PlaceOrder(s *Session, side string) { if side != "buy" && side != "sell" { @@ -47,9 +56,9 @@ defer tx.Rollback() - // 1. create the order (status='executed' since we fill immediately) + // 1. record the order as 'open' — no trade has happened yet. var orderID string err = tx.QueryRow( - `INSERT INTO orders (user_id, market_id, side, type, status, quantity, price, executed_at) - VALUES ($1, $2, $3, 'market', 'executed', $4, $5, now()) + `INSERT INTO orders (user_id, market_id, side, type, status, quantity, price) + VALUES ($1, $2, $3, 'market', 'open', $4, $5) RETURNING id`, s.UserID, m.ID, side, qty, price, @@ -87,5 +96,6 @@ } - // upsert holding with running weighted average + // a buy never reserves crypto, only ever adds it — upsert holding + // with running weighted average if err := upsertHoldingOnBuy(tx, s.UserID, m.CryptoID, qty, price); err != nil { fmt.Println("Error updating holding:", err) @@ -104,25 +114,42 @@ } } else { - // sell: check holding - var held, avgPrice float64 + // sell: lock the holding and check what is actually free to sell — + // quantity minus whatever another open order has already reserved. + var held, reserved, avgPrice float64 err := tx.QueryRow( - `SELECT quantity, avg_price FROM holdings + `SELECT quantity, reserved_quantity, avg_price FROM holdings WHERE user_id = $1 AND crypto_id = $2 FOR UPDATE`, s.UserID, m.CryptoID, - ).Scan(&held, &avgPrice) + ).Scan(&held, &reserved, &avgPrice) if err != nil && err != sql.ErrNoRows { fmt.Println("Error:", err) return } - if err == sql.ErrNoRows || held < qty { - fmt.Printf("Insufficient holding: trying to sell %.4f, hold %.4f\n", qty, held) - return - } - - // reduce holding + available := held - reserved + if err == sql.ErrNoRows || available < qty { + fmt.Printf("Insufficient holding: trying to sell %.4f, available %.4f (of %.4f held, %.4f reserved)\n", + qty, available, held, reserved) + return + } + + // reserve: committed to this order, not yet removed from the position. if _, err := tx.Exec( `UPDATE holdings - SET quantity = quantity - $1, - updated_at = now() + SET reserved_quantity = reserved_quantity + $1, + updated_at = now() + WHERE user_id = $2 AND crypto_id = $3`, + qty, s.UserID, m.CryptoID, + ); err != nil { + fmt.Println("Error:", err) + return + } + + // settle: a market order fills immediately, so release the + // reservation and remove the asset from the position in one step. + if _, err := tx.Exec( + `UPDATE holdings + SET quantity = quantity - $1, + reserved_quantity = reserved_quantity - $1, + updated_at = now() WHERE user_id = $2 AND crypto_id = $3`, qty, s.UserID, m.CryptoID, @@ -163,4 +190,13 @@ VALUES ($1, now(), $2, $3, $4, 'user')`, m.ID, price, qty, side, + ); err != nil { + fmt.Println("Error:", err) + return + } + + // settle the order itself: it has now actually been filled. + if _, err := tx.Exec( + `UPDATE orders SET status = 'executed', executed_at = now() WHERE id = $1`, + orderID, ); err != nil { fmt.Println("Error:", err)