Index: server/account.go =================================================================== --- server/account.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/account.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,98 @@ +package main + +import ( + "fmt" + "strconv" + "strings" + + "bp_project/server/db" +) + +// ShowBalance prints the logged-in user's balances. +func ShowBalance(s *Session) { + var avail, invested float64 + err := db.DB.QueryRow( + `SELECT available_balance, invested_balance FROM users WHERE id = $1`, + s.UserID, + ).Scan(&avail, &invested) + if err != nil { + fmt.Println("Error:", err) + return + } + fmt.Printf("\n Available: %.4f USD\n", avail) + fmt.Printf(" Invested : %.4f USD\n", invested) + fmt.Printf(" Total : %.4f USD\n", avail+invested) +} + +// Deposit - UC0003 +// Transactional: updates users.available_balance and inserts a ledger row. +func Deposit(s *Session) { + fmt.Println("\n-- Deposit virtual funds --") + amtStr := prompt("Amount (USD): ") + amt, err := strconv.ParseFloat(amtStr, 64) + if err != nil || amt <= 0 { + fmt.Println("Invalid amount.") + return + } + + tx, err := db.DB.Begin() + if err != nil { + fmt.Println("Error:", err) + return + } + defer tx.Rollback() + + if _, err := tx.Exec( + `UPDATE users + SET available_balance = available_balance + $1, + updated_at = now() + WHERE id = $2`, + amt, s.UserID, + ); err != nil { + fmt.Println("Error:", err) + return + } + if _, err := tx.Exec( + `INSERT INTO transactions (user_id, type, amount, currency, description) + VALUES ($1, 'deposit', $2, 'USD', 'Virtual deposit')`, + s.UserID, amt, + ); err != nil { + fmt.Println("Error:", err) + return + } + if err := tx.Commit(); err != nil { + fmt.Println("Error:", err) + return + } + fmt.Printf("Deposited %.4f USD.\n", amt) +} + +// ShowTransactions lists the last 20 ledger entries for the user. +func ShowTransactions(s *Session) { + rows, err := db.DB.Query( + `SELECT created_at, type, amount, currency, COALESCE(description, '') + FROM transactions + WHERE user_id = $1 + ORDER BY created_at DESC + LIMIT 20`, + s.UserID, + ) + if err != nil { + fmt.Println("Error:", err) + return + } + defer rows.Close() + + fmt.Println() + fmt.Printf(" %-20s %-8s %12s %-3s %s\n", "When", "Type", "Amount", "Cur", "Description") + fmt.Println(" " + strings.Repeat("-", 70)) + for rows.Next() { + var when, typ, cur, desc string + var amt float64 + if err := rows.Scan(&when, &typ, &amt, &cur, &desc); err != nil { + fmt.Println("scan error:", err) + return + } + fmt.Printf(" %-20s %-8s %12.4f %-3s %s\n", when[:19], typ, amt, cur, desc) + } +} Index: server/auth.go =================================================================== --- server/auth.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/auth.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,108 @@ +package main + +import ( + "crypto/sha256" + "database/sql" + "encoding/hex" + "errors" + "fmt" + "strings" + + "bp_project/server/db" +) + +func hashPassword(pw string) string { + sum := sha256.Sum256([]byte(pw)) + return hex.EncodeToString(sum[:]) +} + +// Register - UC0001 +func Register() { + fmt.Println("\n-- Register --") + username := prompt("Username: ") + email := prompt("Email: ") + fullName := prompt("Full name: ") + pw := prompt("Password (min 6 chars): ") + + if username == "" || email == "" || pw == "" { + fmt.Println("Username, email and password are required.") + return + } + if !strings.Contains(email, "@") { + fmt.Println("Invalid email.") + return + } + if len(pw) < 6 { + fmt.Println("Password must be at least 6 characters.") + return + } + + var exists bool + err := db.DB.QueryRow( + `SELECT EXISTS(SELECT 1 FROM users WHERE username = $1 OR email = $2)`, + username, email, + ).Scan(&exists) + if err != nil { + fmt.Println("Database error:", err) + return + } + if exists { + fmt.Println("Username or email already taken.") + return + } + + _, err = db.DB.Exec( + `INSERT INTO users (username, email, full_name, password_hash, available_balance) + VALUES ($1, $2, $3, $4, 0)`, + username, email, fullName, hashPassword(pw), + ) + if err != nil { + fmt.Println("Failed to register:", err) + return + } + fmt.Println("Account created. You can now log in.") +} + +// Login - UC0002 +func Login(s *Session) { + fmt.Println("\n-- Login --") + username := prompt("Username: ") + pw := prompt("Password: ") + if username == "" || pw == "" { + fmt.Println("Username and password are required.") + return + } + + id, err := authenticate(username, pw) + if err != nil { + if errors.Is(err, errInvalidCreds) { + fmt.Println("Invalid credentials.") + return + } + fmt.Println("Login error:", err) + return + } + s.UserID = id + s.Username = username + fmt.Println("Login successful.") +} + +var errInvalidCreds = errors.New("invalid credentials") + +func authenticate(username, pw string) (string, error) { + var id, stored string + err := db.DB.QueryRow( + `SELECT id, password_hash FROM users WHERE username = $1`, + username, + ).Scan(&id, &stored) + if err == sql.ErrNoRows { + return "", errInvalidCreds + } + if err != nil { + return "", err + } + if stored != hashPassword(pw) { + return "", errInvalidCreds + } + return id, nil +} Index: server/cli.go =================================================================== --- server/cli.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/cli.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,106 @@ +package main + +import ( + "bufio" + "fmt" + "os" + "strings" +) + +// Session holds the currently-logged-in user, if any. +type Session struct { + UserID string + Username string +} + +var stdin = bufio.NewReader(os.Stdin) + +func prompt(label string) string { + fmt.Print(label) + line, err := stdin.ReadString('\n') + // On EOF (Ctrl-D, or the end of a piped script) ReadString keeps returning + // an error forever. Without this the menu loop would spin printing + // "Unknown option." indefinitely instead of ending. + if err != nil && strings.TrimSpace(line) == "" { + fmt.Println("\nInput closed. Goodbye.") + os.Exit(0) + } + return strings.TrimSpace(line) +} + +func RunCLI() { + fmt.Println("=========================================") + fmt.Println(" EduBerza - Crypto Exchange Simulation") + fmt.Println("=========================================") + + var s Session + for { + if s.UserID == "" { + anonymousMenu(&s) + } else { + authenticatedMenu(&s) + } + } +} + +func anonymousMenu(s *Session) { + fmt.Println() + fmt.Println("[1] Register") + fmt.Println("[2] Login") + fmt.Println("[3] Browse markets") + fmt.Println("[0] Exit") + switch prompt("> ") { + case "1": + Register() + case "2": + Login(s) + case "3": + ListMarkets() + case "0": + fmt.Println("Goodbye.") + os.Exit(0) + default: + fmt.Println("Unknown option.") + } +} + +func authenticatedMenu(s *Session) { + fmt.Printf("\n--- Logged in as %s ---\n", s.Username) + fmt.Println("[1] View balance") + fmt.Println("[2] Deposit virtual funds") + fmt.Println("[3] Browse markets") + fmt.Println("[4] Place market BUY order") + fmt.Println("[5] Place market SELL order") + fmt.Println("[6] View portfolio") + fmt.Println("[7] View transaction history") + fmt.Println("[8] Manage watchlist") + fmt.Println("[9] Logout") + fmt.Println("[0] Exit") + switch prompt("> ") { + case "1": + ShowBalance(s) + case "2": + Deposit(s) + case "3": + ListMarkets() + case "4": + PlaceOrder(s, "buy") + case "5": + PlaceOrder(s, "sell") + case "6": + ShowPortfolio(s) + case "7": + ShowTransactions(s) + case "8": + ManageWatchlist(s) + case "9": + s.UserID = "" + s.Username = "" + fmt.Println("Logged out.") + case "0": + fmt.Println("Goodbye.") + os.Exit(0) + default: + fmt.Println("Unknown option.") + } +} Index: server/db/data_load.sql =================================================================== --- server/db/data_load.sql (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/db/data_load.sql (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,144 @@ +-- data_load.sql +-- EduBerza - sample data +-- Course: Databases 2025/2026 Winter, FINKI UKIM +-- +-- Idempotent. Truncates all tables in the `project` schema and reloads +-- deterministic sample data. Run schema_creation.sql first if tables do +-- not yet exist. +-- +-- All sample users have the password: test123 + +SET search_path TO project, public; + +TRUNCATE TABLE + project.watchlist_items, + project.watchlists, + project.market_candles, + project.market_trades, + project.transactions, + project.orders, + project.holdings, + project.markets, + project.crypto, + project.users +RESTART IDENTITY CASCADE; + +-- ============================================================================ +-- CRYPTO +-- ============================================================================ +INSERT INTO project.crypto (id, symbol, name) VALUES + ('11111111-1111-1111-1111-111111111111', 'BTC', 'Bitcoin'), + ('22222222-2222-2222-2222-222222222222', 'ETH', 'Ethereum'), + ('33333333-3333-3333-3333-333333333333', 'ADA', 'Cardano'), + ('44444444-4444-4444-4444-444444444444', 'SOL', 'Solana'), + ('55555555-5555-5555-5555-555555555555', 'DOGE', 'Dogecoin'); + +-- ============================================================================ +-- MARKETS (all quoted in USD) +-- ============================================================================ +INSERT INTO project.markets (id, crypto_id, quote_currency, is_active) VALUES + ('a1111111-1111-1111-1111-111111111111', '11111111-1111-1111-1111-111111111111', 'USD', true), + ('a2222222-2222-2222-2222-222222222222', '22222222-2222-2222-2222-222222222222', 'USD', true), + ('a3333333-3333-3333-3333-333333333333', '33333333-3333-3333-3333-333333333333', 'USD', true), + ('a4444444-4444-4444-4444-444444444444', '44444444-4444-4444-4444-444444444444', 'USD', true), + ('a5555555-5555-5555-5555-555555555555', '55555555-5555-5555-5555-555555555555', 'USD', true); + +-- ============================================================================ +-- USERS +-- Password for all: test123 (stored as sha256 hex hash) +-- ============================================================================ +INSERT INTO project.users (id, username, email, full_name, password_hash, available_balance, invested_balance) VALUES + ('b1111111-1111-1111-1111-111111111111', 'alice', 'alice@example.com', 'Alice Johnson', + encode(digest('test123', 'sha256'), 'hex'), 10000.0000, 0), + ('b2222222-2222-2222-2222-222222222222', 'bob', 'bob@example.com', 'Bob Smith', + encode(digest('test123', 'sha256'), 'hex'), 5000.0000, 0), + ('b3333333-3333-3333-3333-333333333333', 'charlie', 'charlie@example.com', 'Charlie Davis', + encode(digest('test123', 'sha256'), 'hex'), 2500.0000, 0); + +-- ============================================================================ +-- MARKET TRADES +-- Recent simulated trades per market, used as price source. +-- ============================================================================ +INSERT INTO project.market_trades (market_id, executed_at, price, quantity, side, source) VALUES + -- BTC/USD around $67,000 + ('a1111111-1111-1111-1111-111111111111', now() - interval '10 min', 66850.250000, 0.120000, 'buy', 'simulation'), + ('a1111111-1111-1111-1111-111111111111', now() - interval '8 min', 66910.500000, 0.075000, 'sell', 'simulation'), + ('a1111111-1111-1111-1111-111111111111', now() - interval '5 min', 67020.750000, 0.200000, 'buy', 'simulation'), + ('a1111111-1111-1111-1111-111111111111', now() - interval '2 min', 67105.100000, 0.050000, 'buy', 'simulation'), + ('a1111111-1111-1111-1111-111111111111', now() - interval '30 second', 67140.000000, 0.030000, 'sell', 'simulation'), + -- ETH/USD around $3,500 + ('a2222222-2222-2222-2222-222222222222', now() - interval '10 min', 3490.500000, 1.500000, 'buy', 'simulation'), + ('a2222222-2222-2222-2222-222222222222', now() - interval '6 min', 3502.750000, 0.800000, 'sell', 'simulation'), + ('a2222222-2222-2222-2222-222222222222', now() - interval '2 min', 3515.250000, 2.100000, 'buy', 'simulation'), + ('a2222222-2222-2222-2222-222222222222', now() - interval '30 second', 3520.000000, 0.650000, 'buy', 'simulation'), + -- ADA/USD around $0.45 + ('a3333333-3333-3333-3333-333333333333', now() - interval '10 min', 0.446500, 500.000000, 'buy', 'simulation'), + ('a3333333-3333-3333-3333-333333333333', now() - interval '3 min', 0.452000, 1200.000000, 'buy', 'simulation'), + ('a3333333-3333-3333-3333-333333333333', now() - interval '30 second', 0.453750, 800.000000, 'sell', 'simulation'), + -- SOL/USD around $165 + ('a4444444-4444-4444-4444-444444444444', now() - interval '10 min', 164.250000, 10.000000, 'buy', 'simulation'), + ('a4444444-4444-4444-4444-444444444444', now() - interval '4 min', 165.500000, 5.500000, 'sell', 'simulation'), + ('a4444444-4444-4444-4444-444444444444', now() - interval '30 second', 166.100000, 8.000000, 'buy', 'simulation'), + -- DOGE/USD around $0.12 + ('a5555555-5555-5555-5555-555555555555', now() - interval '10 min', 0.118500, 10000.000000, 'buy', 'simulation'), + ('a5555555-5555-5555-5555-555555555555', now() - interval '3 min', 0.121250, 7500.000000, 'sell', 'simulation'), + ('a5555555-5555-5555-5555-555555555555', now() - interval '30 second', 0.122000, 12000.000000, 'buy', 'simulation'); + +-- ============================================================================ +-- MARKET CANDLES (1h aggregates, last 5 hours per market) +-- ============================================================================ +INSERT INTO project.market_candles (market_id, timeframe, open, high, low, close, volume, candle_time) VALUES + ('a1111111-1111-1111-1111-111111111111', '1h', 66200, 66500, 66050, 66400, 12.50, date_trunc('hour', now() - interval '5 hour')), + ('a1111111-1111-1111-1111-111111111111', '1h', 66400, 66800, 66380, 66700, 15.30, date_trunc('hour', now() - interval '4 hour')), + ('a1111111-1111-1111-1111-111111111111', '1h', 66700, 66950, 66650, 66900, 11.80, date_trunc('hour', now() - interval '3 hour')), + ('a1111111-1111-1111-1111-111111111111', '1h', 66900, 67100, 66800, 67050, 14.20, date_trunc('hour', now() - interval '2 hour')), + ('a1111111-1111-1111-1111-111111111111', '1h', 67050, 67200, 66900, 67140, 10.75, date_trunc('hour', now() - interval '1 hour')), + ('a2222222-2222-2222-2222-222222222222', '1h', 3460, 3490, 3450, 3485, 120.0, date_trunc('hour', now() - interval '5 hour')), + ('a2222222-2222-2222-2222-222222222222', '1h', 3485, 3510, 3480, 3500, 135.0, date_trunc('hour', now() - interval '4 hour')), + ('a2222222-2222-2222-2222-222222222222', '1h', 3500, 3520, 3495, 3515, 110.0, date_trunc('hour', now() - interval '3 hour')), + ('a2222222-2222-2222-2222-222222222222', '1h', 3515, 3525, 3500, 3520, 125.5, date_trunc('hour', now() - interval '2 hour')), + ('a2222222-2222-2222-2222-222222222222', '1h', 3520, 3530, 3510, 3520, 140.0, date_trunc('hour', now() - interval '1 hour')); + +-- ============================================================================ +-- EXAMPLE ORDERS, HOLDINGS AND TRANSACTIONS for alice +-- Shows a fully-filled market buy and its resulting holding & ledger entry. +-- ============================================================================ +INSERT INTO project.orders (id, user_id, market_id, side, type, status, quantity, price, placed_at, executed_at) VALUES + ('c1111111-1111-1111-1111-111111111111', + 'b1111111-1111-1111-1111-111111111111', + 'a2222222-2222-2222-2222-222222222222', + 'buy', 'market', 'executed', 0.5000, 3500.000000, + now() - interval '1 hour', now() - interval '1 hour'); + +INSERT INTO project.holdings (user_id, crypto_id, quantity, avg_price, updated_at) VALUES + ('b1111111-1111-1111-1111-111111111111', + '22222222-2222-2222-2222-222222222222', + 0.5000, 3500.000000, now() - interval '1 hour'); + +INSERT INTO project.transactions (user_id, type, amount, currency, related_order, description) VALUES + ('b1111111-1111-1111-1111-111111111111', 'deposit', 10000.0000, 'USD', NULL, + 'Initial virtual deposit'), + ('b1111111-1111-1111-1111-111111111111', 'buy', -1750.0000, 'USD', + 'c1111111-1111-1111-1111-111111111111', + 'Market buy 0.5 ETH @ 3500.00'); + +-- After the buy, alice's invested_balance reflects the used funds. +UPDATE project.users + SET available_balance = 10000.0000 - 1750.0000, + invested_balance = 1750.0000, + updated_at = now() + WHERE id = 'b1111111-1111-1111-1111-111111111111'; + +-- ============================================================================ +-- WATCHLISTS +-- ============================================================================ +INSERT INTO project.watchlists (id, user_id, name) VALUES + ('d1111111-1111-1111-1111-111111111111', 'b1111111-1111-1111-1111-111111111111', 'Favorites'), + ('d2222222-2222-2222-2222-222222222222', 'b2222222-2222-2222-2222-222222222222', 'Bobs Picks'); + +INSERT INTO project.watchlist_items (watchlist_id, crypto_id) VALUES + ('d1111111-1111-1111-1111-111111111111', '11111111-1111-1111-1111-111111111111'), + ('d1111111-1111-1111-1111-111111111111', '22222222-2222-2222-2222-222222222222'), + ('d1111111-1111-1111-1111-111111111111', '44444444-4444-4444-4444-444444444444'), + ('d2222222-2222-2222-2222-222222222222', '11111111-1111-1111-1111-111111111111'), + ('d2222222-2222-2222-2222-222222222222', '55555555-5555-5555-5555-555555555555'); Index: server/db/db.go =================================================================== --- server/db/db.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/db/db.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,153 @@ +package db + +import ( + "bufio" + "database/sql" + "embed" + "fmt" + "log" + "os" + "path/filepath" + "strings" + + _ "github.com/lib/pq" +) + +var DB *sql.DB + +// The SQL scripts are compiled into the binary so that -init works no matter +// which directory the program is started from. +// +//go:embed schema_creation.sql data_load.sql +var sqlScripts embed.FS + +func Connect() error { + loadEnvFile() + + host := getenv("DBHOST", "localhost") + port := getenv("DBPORT", "5432") + user := getenv("DBUSER", "postgres") + pass := getenv("DBPASSWORD", "") + name := getenv("DBNAME", "postgres") + + dsn := fmt.Sprintf( + "host=%s port=%s user=%s password=%s dbname=%s sslmode=disable options='--search_path=project,public'", + host, port, user, pass, name, + ) + + var err error + DB, err = sql.Open("postgres", dsn) + if err != nil { + return fmt.Errorf("sql.Open: %w", err) + } + if err := DB.Ping(); err != nil { + return fmt.Errorf("db ping (host=%s port=%s user=%s dbname=%s): %w", + host, port, user, name, err) + } + return nil +} + +// runScript executes one of the embedded .sql scripts as a single statement. +func runScript(name string) error { + content, err := sqlScripts.ReadFile(name) + if err != nil { + return fmt.Errorf("read embedded %s: %w", name, err) + } + if _, err := DB.Exec(string(content)); err != nil { + return fmt.Errorf("exec %s: %w", name, err) + } + return nil +} + +// RunSQLFile executes a .sql file from disk as a single statement. +func RunSQLFile(path string) error { + content, err := os.ReadFile(path) + if err != nil { + return fmt.Errorf("read %s: %w", path, err) + } + if _, err := DB.Exec(string(content)); err != nil { + return fmt.Errorf("exec %s: %w", path, err) + } + return nil +} + +// InitSchema runs schema_creation.sql then data_load.sql. +// Destructive: drops the `project` schema. Intended for the -init flag. +func InitSchema() error { + log.Println("Running schema_creation.sql ...") + if err := runScript("schema_creation.sql"); err != nil { + return err + } + if err := LoadData(); err != nil { + return err + } + log.Println("Database initialised.") + return nil +} + +// LoadData reloads the sample data without touching the schema. +func LoadData() error { + log.Println("Running data_load.sql ...") + return runScript("data_load.sql") +} + +// loadEnvFile looks for a .env file in the working directory and in every +// parent directory, so the program can be started from the repo root, from +// server/, or from anywhere else inside the checkout. Variables already set +// in the real environment always win over the file, which is what lets you +// point the prototype at the faculty database with DBHOST=... ./eduberza +func loadEnvFile() { + dir, err := os.Getwd() + if err != nil { + return + } + for { + path := filepath.Join(dir, ".env") + if applyEnvFile(path) { + return + } + parent := filepath.Dir(dir) + if parent == dir { + return // reached the filesystem root + } + dir = parent + } +} + +// applyEnvFile reports whether the file existed and was read. +func applyEnvFile(path string) bool { + f, err := os.Open(path) + if err != nil { + return false + } + defer f.Close() + + s := bufio.NewScanner(f) + for s.Scan() { + line := strings.TrimSpace(s.Text()) + if line == "" || strings.HasPrefix(line, "#") { + continue + } + key, value, ok := strings.Cut(line, "=") + if !ok { + continue + } + key = strings.TrimSpace(key) + // Do not clobber variables that are already set in the environment. + if _, exists := os.LookupEnv(key); exists { + continue + } + os.Setenv(key, strings.Trim(strings.TrimSpace(value), `"'`)) + } + if err := s.Err(); err != nil { + log.Printf("warning: could not fully read %s: %v", path, err) + } + return true +} + +func getenv(key, def string) string { + if v := os.Getenv(key); v != "" { + return v + } + return def +} Index: server/db/schema_creation.sql =================================================================== --- server/db/schema_creation.sql (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/db/schema_creation.sql (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,193 @@ +-- schema_creation.sql +-- EduBerza - crypto exchange simulation database +-- Course: Databases 2025/2026 Winter, FINKI UKIM +-- +-- This script is idempotent. It drops the `project` schema and all contained +-- objects, then recreates them from scratch. Safe to run on an empty database +-- or on a database where the schema already exists. + +DROP SCHEMA IF EXISTS project CASCADE; +CREATE SCHEMA project; + +CREATE EXTENSION IF NOT EXISTS pgcrypto; + +SET search_path TO project, public; + +-- ============================================================================ +-- USERS +-- Platform users. Each user has virtual (prop) balances used for simulation. +-- ============================================================================ +CREATE TABLE project.users ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + username varchar(50) NOT NULL UNIQUE, + email varchar(255) NOT NULL UNIQUE, + full_name varchar(200), + password_hash varchar(255) NOT NULL, + available_balance numeric(18,4) NOT NULL DEFAULT 0 CHECK (available_balance >= 0), + invested_balance numeric(18,4) NOT NULL DEFAULT 0 CHECK (invested_balance >= 0), + created_at timestamptz NOT NULL DEFAULT now(), + updated_at timestamptz +); + +-- ============================================================================ +-- CRYPTO +-- Catalog of crypto assets available on the platform. +-- ============================================================================ +CREATE TABLE project.crypto ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + symbol varchar(20) NOT NULL UNIQUE, + name varchar(255) NOT NULL, + created_at timestamptz NOT NULL DEFAULT now() +); + +-- ============================================================================ +-- MARKETS +-- A market is a (crypto, quote_currency) pair, e.g. BTC/USD. +-- ============================================================================ +CREATE TABLE project.markets ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + crypto_id uuid NOT NULL REFERENCES project.crypto(id), + quote_currency char(3) NOT NULL DEFAULT 'USD', + is_active boolean NOT NULL DEFAULT true, + created_at timestamptz NOT NULL DEFAULT now(), + CONSTRAINT uq_markets UNIQUE (crypto_id, quote_currency) +); + +-- ============================================================================ +-- HOLDINGS +-- Per-user crypto position with running weighted average entry price. +-- ============================================================================ +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), + -- 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, + CONSTRAINT uq_holdings_user_crypto UNIQUE (user_id, crypto_id) +); + +-- ============================================================================ +-- ORDERS +-- Orders placed by users on a market. +-- ============================================================================ +CREATE TABLE project.orders ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE, + market_id uuid NOT NULL REFERENCES project.markets(id), + side varchar(4) NOT NULL CHECK (side IN ('buy', 'sell')), + type varchar(20) NOT NULL CHECK (type IN ('market', 'limit')), + status varchar(20) NOT NULL CHECK (status IN ('open', 'executed', 'cancelled')), + quantity numeric(20,4) NOT NULL CHECK (quantity > 0), + price numeric(18,6), + placed_at timestamptz NOT NULL DEFAULT now(), + executed_at timestamptz +); + +CREATE INDEX idx_orders_user ON project.orders(user_id); +CREATE INDEX idx_orders_market ON project.orders(market_id); +CREATE INDEX idx_orders_status ON project.orders(status); + +-- ============================================================================ +-- TRANSACTIONS +-- Financial ledger: deposits, buys, sells, fees. +-- ============================================================================ +CREATE TABLE project.transactions ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE, + type varchar(50) NOT NULL CHECK (type IN ('deposit', 'buy', 'sell', 'fee')), + amount numeric(18,4) NOT NULL, + currency char(3) NOT NULL DEFAULT 'USD', + related_order uuid REFERENCES project.orders(id), + created_at timestamptz NOT NULL DEFAULT now(), + description text +); + +CREATE INDEX idx_transactions_user ON project.transactions(user_id, created_at DESC); + +-- ============================================================================ +-- MARKET TRADES +-- Raw executed trades on a market. Source of truth for current price. +-- ============================================================================ +CREATE TABLE project.market_trades ( + id bigserial PRIMARY KEY, + market_id uuid NOT NULL REFERENCES project.markets(id), + executed_at timestamptz NOT NULL, + price numeric(18,6) NOT NULL CHECK (price > 0), + quantity numeric(20,6) NOT NULL CHECK (quantity > 0), + side varchar(4) CHECK (side IN ('buy', 'sell')), + source varchar(50) NOT NULL DEFAULT 'simulation' +); + +CREATE INDEX idx_market_trades_market_time ON project.market_trades(market_id, executed_at DESC); + +-- ============================================================================ +-- MARKET CANDLES +-- OHLCV aggregates over standard timeframes. +-- ============================================================================ +CREATE TABLE project.market_candles ( + id bigserial PRIMARY KEY, + market_id uuid NOT NULL REFERENCES project.markets(id), + timeframe varchar(5) NOT NULL CHECK (timeframe IN ('1m', '5m', '1h', '1d')), + open numeric(18,6) NOT NULL, + high numeric(18,6) NOT NULL, + low numeric(18,6) NOT NULL, + close numeric(18,6) NOT NULL, + volume numeric(20,6) NOT NULL, + candle_time timestamptz NOT NULL, + CONSTRAINT uq_candle UNIQUE (market_id, timeframe, candle_time) +); + +CREATE INDEX idx_market_candles_market_tf_time ON project.market_candles(market_id, timeframe, candle_time DESC); + +-- ============================================================================ +-- WATCHLISTS +-- ============================================================================ +CREATE TABLE project.watchlists ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE, + name varchar(100) NOT NULL, + created_at timestamptz NOT NULL DEFAULT now() +); + +CREATE TABLE project.watchlist_items ( + id uuid PRIMARY KEY DEFAULT gen_random_uuid(), + watchlist_id uuid NOT NULL REFERENCES project.watchlists(id) ON DELETE CASCADE, + crypto_id uuid NOT NULL REFERENCES project.crypto(id), + added_at timestamptz NOT NULL DEFAULT now(), + CONSTRAINT uq_watchlist_crypto UNIQUE (watchlist_id, crypto_id) +); + +-- ============================================================================ +-- VIEWS +-- ============================================================================ + +-- Latest trade price per market (current price). +CREATE OR REPLACE VIEW project.v_latest_prices AS +SELECT DISTINCT ON (t.market_id) + t.market_id, + c.symbol, + m.quote_currency, + t.price, + t.executed_at +FROM project.market_trades t +JOIN project.markets m ON m.id = t.market_id +JOIN project.crypto c ON c.id = m.crypto_id +ORDER BY t.market_id, t.executed_at DESC; + +-- Portfolio valuation per user (holdings x latest price). +CREATE OR REPLACE VIEW project.v_portfolio AS +SELECT h.user_id, + c.symbol, + h.quantity, + h.avg_price, + lp.price AS current_price, + (h.quantity * lp.price) AS market_value, + (h.quantity * (lp.price - h.avg_price)) AS unrealized_pnl +FROM project.holdings h +JOIN project.crypto c ON c.id = h.crypto_id +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; Index: server/main.go =================================================================== --- server/main.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/main.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,37 @@ +package main + +import ( + "flag" + "fmt" + "log" + + "bp_project/server/db" +) + +func main() { + initFlag := flag.Bool("init", false, "drop and recreate the project schema, then load sample data") + loadData := flag.Bool("load-data", false, "reload sample data (without dropping schema)") + flag.Parse() + + if err := db.Connect(); err != nil { + log.Fatalf("database connect failed: %v", err) + } + defer db.DB.Close() + + if *initFlag { + if err := db.InitSchema(); err != nil { + log.Fatalf("init failed: %v", err) + } + fmt.Println("Schema initialised. Re-run without -init to start the CLI.") + return + } + if *loadData { + if err := db.LoadData(); err != nil { + log.Fatalf("data load failed: %v", err) + } + fmt.Println("Sample data reloaded.") + return + } + + RunCLI() +} Index: server/market.go =================================================================== --- server/market.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/market.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,85 @@ +package main + +import ( + "database/sql" + "fmt" + + "bp_project/server/db" +) + +// Market represents a trading pair. +type Market struct { + ID string + CryptoID string + Symbol string + Quote string +} + +// ListMarkets prints all active markets with their latest price. +func ListMarkets() { + rows, err := db.DB.Query(` + SELECT m.id, c.symbol, m.quote_currency, + COALESCE(lp.price, 0) AS price + FROM markets m + JOIN crypto c ON c.id = m.crypto_id + LEFT JOIN v_latest_prices lp ON lp.market_id = m.id + WHERE m.is_active = true + ORDER BY c.symbol`) + if err != nil { + fmt.Println("Error:", err) + return + } + defer rows.Close() + + fmt.Println() + fmt.Printf(" %-4s %-8s %-5s %15s\n", "#", "Symbol", "Quote", "Last price") + fmt.Println(" -----------------------------------------") + i := 1 + for rows.Next() { + var id, sym, quote string + var price float64 + if err := rows.Scan(&id, &sym, "e, &price); err != nil { + fmt.Println("scan error:", err) + return + } + fmt.Printf(" %-4d %-8s %-5s %15.6f\n", i, sym, quote, price) + i++ + } +} + +// ChooseMarket asks the user to pick a market by symbol and returns it. +func ChooseMarket() (*Market, error) { + ListMarkets() + sym := prompt("Market symbol (e.g. BTC): ") + if sym == "" { + return nil, fmt.Errorf("no symbol entered") + } + var m Market + err := db.DB.QueryRow(` + SELECT m.id, c.id, c.symbol, m.quote_currency + FROM markets m + JOIN crypto c ON c.id = m.crypto_id + WHERE upper(c.symbol) = upper($1) + AND m.is_active = true + LIMIT 1`, sym, + ).Scan(&m.ID, &m.CryptoID, &m.Symbol, &m.Quote) + if err == sql.ErrNoRows { + return nil, fmt.Errorf("market %s not found", sym) + } + if err != nil { + return nil, err + } + return &m, nil +} + +// LatestPrice returns the last traded price on a market. +func LatestPrice(marketID string) (float64, error) { + var price float64 + err := db.DB.QueryRow( + `SELECT price FROM v_latest_prices WHERE market_id = $1`, marketID, + ).Scan(&price) + if err == sql.ErrNoRows { + return 0, fmt.Errorf("no trades yet for this market") + } + return price, err +} Index: server/portfolio.go =================================================================== --- server/portfolio.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/portfolio.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,67 @@ +package main + +import ( + "fmt" + + "bp_project/server/db" +) + +// ShowPortfolio - UC0006 +// Uses the v_portfolio view to list holdings with current market value and P&L. +func ShowPortfolio(s *Session) { + rows, err := db.DB.Query( + `SELECT symbol, + quantity, + COALESCE(avg_price, 0), + COALESCE(current_price, 0), + COALESCE(market_value, 0), + COALESCE(unrealized_pnl, 0) + FROM v_portfolio + WHERE user_id = $1 AND quantity > 0 + ORDER BY symbol`, + s.UserID, + ) + if err != nil { + fmt.Println("Error:", err) + return + } + defer rows.Close() + + fmt.Println() + fmt.Printf(" %-8s %12s %14s %14s %14s %14s\n", + "Symbol", "Quantity", "Avg buy", "Current", "Value", "Unrealised P/L") + fmt.Println(" ------------------------------------------------------------------------------------") + + var totalValue, totalPnL float64 + empty := true + 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 { + 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) + totalValue += val + totalPnL += pnl + empty = false + } + if empty { + fmt.Println(" (no holdings yet)") + return + } + fmt.Println(" ------------------------------------------------------------------------------------") + fmt.Printf(" %-8s %12s %14s %14s %14.4f %+14.4f\n", + "TOTAL", "", "", "", totalValue, totalPnL) + + // cash summary + var avail, invested float64 + _ = db.DB.QueryRow( + `SELECT available_balance, invested_balance FROM users WHERE id = $1`, + s.UserID, + ).Scan(&avail, &invested) + fmt.Printf("\n Cash available : %.4f USD\n", avail) + fmt.Printf(" Portfolio value: %.4f USD\n", totalValue) + fmt.Printf(" Net worth : %.4f USD\n", avail+totalValue) +} Index: server/trade.go =================================================================== --- server/trade.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/trade.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,199 @@ +package main + +import ( + "database/sql" + "fmt" + "strconv" + + "bp_project/server/db" +) + +// PlaceOrder - UC0004 (buy) / UC0005 (sell) +// Market order that executes immediately against the latest price. +// Runs inside a single database transaction so the orders, holdings, +// users.balance and transactions tables always agree. +func PlaceOrder(s *Session, side string) { + if side != "buy" && side != "sell" { + fmt.Println("Invalid side.") + return + } + fmt.Printf("\n-- Place market %s order --\n", side) + + m, err := ChooseMarket() + if err != nil { + fmt.Println(err) + return + } + price, err := LatestPrice(m.ID) + if err != nil { + fmt.Println(err) + return + } + fmt.Printf("Latest price for %s/%s = %.6f\n", m.Symbol, m.Quote, price) + + qtyStr := prompt("Quantity: ") + qty, err := strconv.ParseFloat(qtyStr, 64) + if err != nil || qty <= 0 { + fmt.Println("Invalid quantity.") + return + } + notional := qty * price + + tx, err := db.DB.Begin() + if err != nil { + fmt.Println("Error:", err) + return + } + defer tx.Rollback() + + // 1. create the order (status='executed' since we fill immediately) + 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()) + RETURNING id`, + s.UserID, m.ID, side, qty, price, + ).Scan(&orderID) + if err != nil { + fmt.Println("Error creating order:", err) + return + } + + if side == "buy" { + // check balance + var avail float64 + if err := tx.QueryRow( + `SELECT available_balance FROM users WHERE id = $1 FOR UPDATE`, + s.UserID).Scan(&avail); err != nil { + fmt.Println("Error:", err) + return + } + if avail < notional { + fmt.Printf("Insufficient funds: need %.4f, have %.4f\n", notional, avail) + return + } + + // debit balance + if _, err := tx.Exec( + `UPDATE users + SET available_balance = available_balance - $1, + invested_balance = invested_balance + $1, + updated_at = now() + WHERE id = $2`, + notional, s.UserID, + ); err != nil { + fmt.Println("Error:", err) + return + } + + // upsert holding with running weighted average + if err := upsertHoldingOnBuy(tx, s.UserID, m.CryptoID, qty, price); err != nil { + fmt.Println("Error updating holding:", err) + return + } + + // ledger entry + if _, err := tx.Exec( + `INSERT INTO transactions (user_id, type, amount, currency, related_order, description) + VALUES ($1, 'buy', $2, 'USD', $3, $4)`, + s.UserID, -notional, orderID, + fmt.Sprintf("Market buy %.4f %s @ %.6f", qty, m.Symbol, price), + ); err != nil { + fmt.Println("Error:", err) + return + } + } else { + // sell: check holding + var held, avgPrice float64 + err := tx.QueryRow( + `SELECT quantity, avg_price FROM holdings + WHERE user_id = $1 AND crypto_id = $2 FOR UPDATE`, + s.UserID, m.CryptoID, + ).Scan(&held, &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 + if _, err := tx.Exec( + `UPDATE holdings + SET quantity = 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 + } + + // credit balance; reduce invested by cost basis (avg_price * qty) + costBasis := avgPrice * qty + if _, err := tx.Exec( + `UPDATE users + SET available_balance = available_balance + $1, + invested_balance = GREATEST(invested_balance - $2, 0), + updated_at = now() + WHERE id = $3`, + notional, costBasis, s.UserID, + ); err != nil { + fmt.Println("Error:", err) + return + } + + // ledger entry + if _, err := tx.Exec( + `INSERT INTO transactions (user_id, type, amount, currency, related_order, description) + VALUES ($1, 'sell', $2, 'USD', $3, $4)`, + s.UserID, notional, orderID, + fmt.Sprintf("Market sell %.4f %s @ %.6f", qty, m.Symbol, price), + ); err != nil { + fmt.Println("Error:", err) + return + } + } + + // record the resulting market trade so the book reflects this fill + if _, err := tx.Exec( + `INSERT INTO market_trades (market_id, executed_at, price, quantity, side, source) + VALUES ($1, now(), $2, $3, $4, 'user')`, + m.ID, price, qty, side, + ); err != nil { + fmt.Println("Error:", err) + return + } + + if err := tx.Commit(); err != nil { + fmt.Println("Commit error:", err) + return + } + fmt.Printf("Order executed: %s %.4f %s @ %.6f (notional %.4f USD)\n", + side, qty, m.Symbol, price, notional) +} + +// upsertHoldingOnBuy creates or updates a holding using running weighted-average price. +// +// This is a single statement that relies on UNIQUE (user_id, crypto_id): the new +// weighted average is recomputed by the database in numeric arithmetic rather +// than in Go float64, and no separate SELECT ... FOR UPDATE round-trip is +// needed because ON CONFLICT DO UPDATE locks the conflicting row itself. +// Every SET expression sees the pre-update row, so `holdings.quantity` below is +// still the old quantity while the average is being computed. +func upsertHoldingOnBuy(tx *sql.Tx, userID, cryptoID string, qty, price float64) error { + _, err := tx.Exec( + `INSERT INTO holdings (user_id, crypto_id, quantity, avg_price, updated_at) + VALUES ($1, $2, $3, $4, now()) + ON CONFLICT (user_id, crypto_id) DO UPDATE + SET avg_price = (holdings.quantity * holdings.avg_price + + EXCLUDED.quantity * EXCLUDED.avg_price) + / (holdings.quantity + EXCLUDED.quantity), + quantity = holdings.quantity + EXCLUDED.quantity, + updated_at = now()`, + userID, cryptoID, qty, price, + ) + return err +} Index: server/watchlist.go =================================================================== --- server/watchlist.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) +++ server/watchlist.go (revision b715712c7d5d3ee27c932a8a9d8e0a2bc6e85127) @@ -0,0 +1,134 @@ +package main + +import ( + "database/sql" + "fmt" + + "bp_project/server/db" +) + +// ManageWatchlist - UC0007 +// Ensures the user has a default watchlist, then allows listing, adding, +// removing entries. +func ManageWatchlist(s *Session) { + wlID, err := ensureDefaultWatchlist(s.UserID) + if err != nil { + fmt.Println("Error:", err) + return + } + for { + fmt.Println("\n-- Watchlist --") + fmt.Println("[1] List items") + fmt.Println("[2] Add crypto") + fmt.Println("[3] Remove crypto") + fmt.Println("[0] Back") + switch prompt("> ") { + case "1": + listWatchlist(wlID) + case "2": + addToWatchlist(wlID) + case "3": + removeFromWatchlist(wlID) + case "0": + return + default: + fmt.Println("Unknown option.") + } + } +} + +func ensureDefaultWatchlist(userID string) (string, error) { + var id string + err := db.DB.QueryRow( + `SELECT id FROM watchlists WHERE user_id = $1 ORDER BY created_at LIMIT 1`, + userID, + ).Scan(&id) + if err == sql.ErrNoRows { + err = db.DB.QueryRow( + `INSERT INTO watchlists (user_id, name) VALUES ($1, 'Favorites') RETURNING id`, + userID, + ).Scan(&id) + return id, err + } + return id, err +} + +func listWatchlist(wlID string) { + rows, err := db.DB.Query(` + SELECT c.symbol, c.name, COALESCE(lp.price, 0) + FROM watchlist_items wi + JOIN crypto c ON c.id = wi.crypto_id + LEFT JOIN markets m ON m.crypto_id = c.id AND m.quote_currency = 'USD' + LEFT JOIN v_latest_prices lp ON lp.market_id = m.id + WHERE wi.watchlist_id = $1 + ORDER BY c.symbol`, wlID) + if err != nil { + fmt.Println("Error:", err) + return + } + defer rows.Close() + + fmt.Println() + fmt.Printf(" %-8s %-20s %15s\n", "Symbol", "Name", "Last price") + fmt.Println(" --------------------------------------------------") + empty := true + for rows.Next() { + var sym, name string + var price float64 + if err := rows.Scan(&sym, &name, &price); err != nil { + fmt.Println("scan error:", err) + return + } + fmt.Printf(" %-8s %-20s %15.6f\n", sym, name, price) + empty = false + } + if empty { + fmt.Println(" (watchlist is empty)") + } +} + +func addToWatchlist(wlID string) { + sym := prompt("Crypto symbol to add: ") + var cryptoID string + err := db.DB.QueryRow( + `SELECT id FROM crypto WHERE upper(symbol) = upper($1)`, sym, + ).Scan(&cryptoID) + if err == sql.ErrNoRows { + fmt.Println("Unknown crypto symbol.") + return + } + if err != nil { + fmt.Println("Error:", err) + return + } + _, err = db.DB.Exec( + `INSERT INTO watchlist_items (watchlist_id, crypto_id) + VALUES ($1, $2) + ON CONFLICT (watchlist_id, crypto_id) DO NOTHING`, + wlID, cryptoID, + ) + if err != nil { + fmt.Println("Error:", err) + return + } + fmt.Println("Added.") +} + +func removeFromWatchlist(wlID string) { + sym := prompt("Crypto symbol to remove: ") + res, err := db.DB.Exec(` + DELETE FROM watchlist_items + WHERE watchlist_id = $1 + AND crypto_id = (SELECT id FROM crypto WHERE upper(symbol) = upper($2))`, + wlID, sym) + if err != nil { + fmt.Println("Error:", err) + return + } + n, _ := res.RowsAffected() + if n == 0 { + fmt.Println("Not in watchlist.") + return + } + fmt.Println("Removed.") +}