Index: server/db/reports_demo_data.sql =================================================================== --- server/db/reports_demo_data.sql (revision 35bcb41d9597d97650b4e40110ab3f3842f3db89) +++ server/db/reports_demo_data.sql (revision 35bcb41d9597d97650b4e40110ab3f3842f3db89) @@ -0,0 +1,115 @@ +-- reports_demo_data.sql +-- EduBerza - optional historical data for the P6 reports +-- Course: Databases 2025/2026 Winter, FINKI UKIM +-- +-- data_load.sql only seeds ~10 minutes of trade history, which is enough to +-- demonstrate UC0001-UC0007 but not enough to show report_top_traders() or +-- report_market_performance() doing anything interesting: everything falls +-- into a single quarter, so "number of profitable periods" and "consistency" +-- are trivial and "market return" has almost no history to work with. +-- +-- This script adds five quarters of synthetic transactions, market trades and +-- executed orders on top of an already-loaded data_load.sql, spanning +-- 2025-07 to 2026-07, so the two P6 reports have several periods and two +-- markets with opposite price trends to actually compare. +-- +-- Deliberately NOT part of -init / -load-data: it only inserts into +-- transactions, market_trades and orders, and does not touch +-- users.available_balance/invested_balance or holdings, so it does not +-- disturb the balances the other use cases' documented "verified run" +-- sections depend on. Run it by hand, after data_load.sql, only to exercise +-- the two reports: +-- +-- psql "$DATABASE_URL" -f server/db/schema_creation.sql +-- psql "$DATABASE_URL" -f server/db/data_load.sql +-- psql "$DATABASE_URL" -f server/db/reports_demo_data.sql +-- +-- Idempotent: deletes its own previously-inserted rows (tagged via +-- description/source) before re-inserting. + +SET search_path TO project, public; + +DELETE FROM transactions WHERE description = 'P6 demo data'; +DELETE FROM orders WHERE id IN ( + 'e1111111-1111-1111-1111-111111111111', 'e2222222-2222-2222-2222-222222222222', + 'e3333333-3333-3333-3333-333333333333', 'e4444444-4444-4444-4444-444444444444', + 'e5555555-5555-5555-5555-555555555555' +); +DELETE FROM market_trades WHERE source = 'p6_demo'; + +-- ============================================================================ +-- Alice: five quarterly round trips, 3 profitable / 2 losing (60% consistency) +-- ============================================================================ +INSERT INTO transactions (user_id, type, amount, currency, created_at, description) VALUES + ('b1111111-1111-1111-1111-111111111111', 'buy', -5000.0000, 'USD', '2025-07-15 10:00', 'P6 demo data'), + ('b1111111-1111-1111-1111-111111111111', 'sell', 5800.0000, 'USD', '2025-07-20 10:00', 'P6 demo data'), + ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2025-07-20 10:00', 'P6 demo data'), + + ('b1111111-1111-1111-1111-111111111111', 'buy', -4000.0000, 'USD', '2025-10-15 10:00', 'P6 demo data'), + ('b1111111-1111-1111-1111-111111111111', 'sell', 3500.0000, 'USD', '2025-10-20 10:00', 'P6 demo data'), + ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2025-10-20 10:00', 'P6 demo data'), + + ('b1111111-1111-1111-1111-111111111111', 'buy', -6000.0000, 'USD', '2026-01-15 10:00', 'P6 demo data'), + ('b1111111-1111-1111-1111-111111111111', 'sell', 6700.0000, 'USD', '2026-01-20 10:00', 'P6 demo data'), + ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2026-01-20 10:00', 'P6 demo data'), + + ('b1111111-1111-1111-1111-111111111111', 'buy', -3000.0000, 'USD', '2026-04-15 10:00', 'P6 demo data'), + ('b1111111-1111-1111-1111-111111111111', 'sell', 2600.0000, 'USD', '2026-04-20 10:00', 'P6 demo data'), + ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2026-04-20 10:00', 'P6 demo data'), + + ('b1111111-1111-1111-1111-111111111111', 'buy', -4500.0000, 'USD', '2026-07-15 10:00', 'P6 demo data'), + ('b1111111-1111-1111-1111-111111111111', 'sell', 5200.0000, 'USD', '2026-07-20 10:00', 'P6 demo data'), + ('b1111111-1111-1111-1111-111111111111', 'fee', -5.0000, 'USD', '2026-07-20 10:00', 'P6 demo data'); + +-- ============================================================================ +-- Bob: three quarterly round trips, all profitable (100% consistency), +-- smaller total P/L than Alice but a higher ROI. +-- ============================================================================ +INSERT INTO transactions (user_id, type, amount, currency, created_at, description) VALUES + ('b2222222-2222-2222-2222-222222222222', 'buy', -2000.0000, 'USD', '2025-10-10 10:00', 'P6 demo data'), + ('b2222222-2222-2222-2222-222222222222', 'sell', 2300.0000, 'USD', '2025-10-12 10:00', 'P6 demo data'), + ('b2222222-2222-2222-2222-222222222222', 'fee', -3.0000, 'USD', '2025-10-12 10:00', 'P6 demo data'), + + ('b2222222-2222-2222-2222-222222222222', 'buy', -2500.0000, 'USD', '2026-01-10 10:00', 'P6 demo data'), + ('b2222222-2222-2222-2222-222222222222', 'sell', 2900.0000, 'USD', '2026-01-12 10:00', 'P6 demo data'), + ('b2222222-2222-2222-2222-222222222222', 'fee', -3.0000, 'USD', '2026-01-12 10:00', 'P6 demo data'), + + ('b2222222-2222-2222-2222-222222222222', 'buy', -1800.0000, 'USD', '2026-04-10 10:00', 'P6 demo data'), + ('b2222222-2222-2222-2222-222222222222', 'sell', 2100.0000, 'USD', '2026-04-12 10:00', 'P6 demo data'), + ('b2222222-2222-2222-2222-222222222222', 'fee', -3.0000, 'USD', '2026-04-12 10:00', 'P6 demo data'); + +-- ============================================================================ +-- Market trades: BTC/USD trending up, ETH/USD trending down, five quarters. +-- source='p6_demo' keeps these separate from data_load.sql's own rows and +-- from live user/bot fills so this script can clean up after itself. +-- ============================================================================ +INSERT INTO market_trades (market_id, executed_at, price, quantity, side, source) VALUES + ('a1111111-1111-1111-1111-111111111111', '2025-07-15 10:00', 40000.000000, 0.500000, 'buy', 'p6_demo'), + ('a1111111-1111-1111-1111-111111111111', '2025-10-15 10:00', 45000.000000, 0.800000, 'buy', 'p6_demo'), + ('a1111111-1111-1111-1111-111111111111', '2026-01-15 10:00', 55000.000000, 1.200000, 'buy', 'p6_demo'), + ('a1111111-1111-1111-1111-111111111111', '2026-04-15 10:00', 60000.000000, 1.000000, 'buy', 'p6_demo'), + + ('a2222222-2222-2222-2222-222222222222', '2025-07-15 10:00', 4000.000000, 3.000000, 'sell', 'p6_demo'), + ('a2222222-2222-2222-2222-222222222222', '2025-10-15 10:00', 3800.000000, 2.500000, 'sell', 'p6_demo'), + ('a2222222-2222-2222-2222-222222222222', '2026-01-15 10:00', 3600.000000, 2.000000, 'sell', 'p6_demo'), + ('a2222222-2222-2222-2222-222222222222', '2026-04-15 10:00', 3550.000000, 1.800000, 'sell', 'p6_demo'); + +-- ============================================================================ +-- Executed orders: who participated in which market, across the same quarters. +-- ============================================================================ +INSERT INTO orders (id, user_id, market_id, side, type, status, quantity, price, placed_at, executed_at) VALUES + ('e1111111-1111-1111-1111-111111111111', 'b1111111-1111-1111-1111-111111111111', + 'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 0.5000, 40000.000000, + '2025-07-15 10:00', '2025-07-15 10:00'), + ('e2222222-2222-2222-2222-222222222222', 'b1111111-1111-1111-1111-111111111111', + 'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 3.0000, 4000.000000, + '2025-10-15 10:00', '2025-10-15 10:00'), + ('e3333333-3333-3333-3333-333333333333', 'b2222222-2222-2222-2222-222222222222', + 'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.2000, 55000.000000, + '2026-01-15 10:00', '2026-01-15 10:00'), + ('e4444444-4444-4444-4444-444444444444', 'b2222222-2222-2222-2222-222222222222', + 'a1111111-1111-1111-1111-111111111111', 'buy', 'market', 'executed', 1.0000, 60000.000000, + '2026-04-15 10:00', '2026-04-15 10:00'), + ('e5555555-5555-5555-5555-555555555555', 'b3333333-3333-3333-3333-333333333333', + 'a2222222-2222-2222-2222-222222222222', 'sell', 'market', 'executed', 2.0000, 3600.000000, + '2026-01-15 10:00', '2026-01-15 10:00');