Ηi$LdZddlZddlZddlmZeejdz ZdZdZ d%de d e d e d e d e d e de de de de de fdZ d&de de deefdZde defdZde fdZd e de de de fdZd e d e d e fd!Zd e dedzfd"Zde dee fd#Zde deefd$ZdS)'z Simple SQLite database for trade history. The blockchain is the source of truth, but we cache trades locally so the leaderboard and trade history load instantly. N)Pathzturbo.dbcttjtt}tj|_|S)N)sqlite3connectstrDB_PATHRow row_factoryconns !/var/www/html/turbo/backend/db.py_connrs& ?3w<< ( (D{D Kcnt}|d|d|d|d|d|d||dS)z"Create tables if they don't exist.a CREATE TABLE IF NOT EXISTS trades ( id INTEGER PRIMARY KEY AUTOINCREMENT, tx_hash TEXT, market_id TEXT, buyer TEXT, seller TEXT, outcome INTEGER, price INTEGER, size INTEGER, fee INTEGER DEFAULT 0, timestamp REAL, created_at REAL DEFAULT (strftime('%s','now')) ) zCREATE INDEX IF NOT EXISTS idx_trades_seller ON trades(seller)zDCREATE INDEX IF NOT EXISTS idx_trades_timestamp ON trades(timestamp)zACREATE INDEX IF NOT EXISTS idx_trades_market ON trades(market_id)aq CREATE TABLE IF NOT EXISTS markets ( market_id TEXT PRIMARY KEY, strike INTEGER, start_time INTEGER, end_time INTEGER, resolved INTEGER DEFAULT 0, outcome TEXT DEFAULT '', final_price INTEGER DEFAULT 0, created_at REAL DEFAULT (strftime('%s','now')) ) Nrexecutecommitcloser s r init_dbrs 77DLL    LLOPPPLLQRRRLLWXXXLLTUUULL      KKMMMJJLLLLLrbuyerselleroutcomepricesize market_idtx_hashfee timestamptakermakerc *|dkrtj}t} | d|||||||||f | | dS)z5Record a trade. One row per fill, showing both sides.rzwINSERT INTO trades (tx_hash, market_id, buyer, seller, outcome, price, size, fee, timestamp) VALUES (?,?,?,?,?,?,?,?,?)N)timerrlowerrr) rrrrrrrrrr r!r s r record_trader%<s A~~IKK 77DLL B )U[[]]FLLNNGUDRUW`a KKMMMJJLLLLLraddresslimitreturnc |}t}|d|||f}|g}|D]e}|d|k}||d|d|d|ddkrdnd|d|d |d ||d d f|S) z Get a user's most recent trades.zNSELECT * FROM trades WHERE buyer=? OR seller=? ORDER BY timestamp DESC LIMIT ?rrrrUPDOWNrrrr) rrrsiderrrisBuyertxHash)r$rrfetchallrappend)r'r(addrr rowstradesris_buyers r get_user_tradesr7Ls ==??D 77D <<X tU  hjj  JJLLL F    W:% wZk|iLA--DD6wZfI; l    Mrsince_timestampct}|d|f}|d}|i}|D]}|d||d<i}|D]}|d|dzdz }|ddz } |ddz } |d } |d } |d} || }| | fD]}||vr |d d d ||<|| d xx|z cc<|| d xx|z cc<|rh|dkr6|| dxx| |z z cc<|| dxx| d| z zzcc<|| dxx|zcc<|| dxx|z cc<t |dd}td|D}t|dd|ddDdS)urGet weekly leaderboard — volume and PnL per wallet since timestamp. PnL calculation: - Buyer pays size*price USDC, gets YES tokens - Seller JIT-splits: pays size*(1-price), gets NO tokens + receives size*price - If YES wins: YES tokens worth $1 each, NO worthless - If NO wins: NO tokens worth $1 each, YES worthless - PnL = payout - cost zMSELECT buyer, seller, price, size, market_id FROM trades WHERE timestamp >= ?zASELECT market_id, outcome, resolved FROM markets WHERE resolved=1rrrrJ)@Brrgwalletvolumepnlr>YESr?c|dS)Nr>)ws r z!get_leaderboard..s AhKrT)keyreversec3&K|] }|dV dS)r>NrC.0rDs r z"get_leaderboard..s&,,( ,,,,,,rcvg|]6}|dt|ddt|ddd7S)r=r>rLr?r<)roundrIs r z#get_leaderboard..sV   {eAhK.C.CERSTYRZ\]L^L^ _ _   rN) totalVolume leaderboard) rrr0rgetsortedvaluessumrN)r8r r4markets resolutionsmwalletsr5 cost_usdc size_shares price_fracrrmidrr2rankedtotals r get_leaderboardrags 77D \\W hjj llKhjj  JJLLLK 33&' l AkN##!G 44vY7+0EF i)+ wZ)+ ' 8 n//#&&FO L LD7""+/3s K K  x   I-   !!!Y.!!!  4%u%%%y)@@%%%&&&+Z*HH&&&&u%%%2%%%&&&)3&&& GNN$$*?*? N N NF ,,V,,, , ,EUA  CRC[     rct}|dd}||S)NzSELECT COUNT(*) FROM tradesr)rrfetchoner)r counts r get_trade_countres@ 77D LL6 7 7 @ @ B B1 EEJJLLL Lrstrike start_timeend_timect}|d||||f||dS)z&Record a new market when it's created.zXINSERT OR IGNORE INTO markets (market_id, strike, start_time, end_time) VALUES (?,?,?,?)Nr)rrfrgrhr s r save_marketrjsP 77DLLb FJ1 KKMMMJJLLLLLr final_pricect}|d|||f||dS)z+Mark a market as resolved with its outcome.zIUPDATE markets SET resolved=1, outcome=?, final_price=? WHERE market_id=?Nr)rrrkr s r resolve_marketrmsN 77DLLS +y) KKMMMJJLLLLLrct}|d|f}||dSt |S)zGet a single market by ID.z'SELECT * FROM markets WHERE market_id=?N)rrrcrdict)rr rows r get_marketrqsP 77D ,,@9, O O X X Z ZCJJLLL {t 99rc|}t}|d||f}|d|DS)z9Get all distinct market IDs where this wallet has traded.zSSELECT DISTINCT market_id FROM trades WHERE buyer=? OR seller=? ORDER BY rowid DESCcg|] }|d S)rrC)rJr5s r rOz'get_user_market_ids..s ) ) )qAkN ) ) )r)r$rrr0r)r'r2r r3s r get_user_market_idsrtsf ==??D 77D <<] t   hjj  JJLLL ) )D ) ) ))rc~|}t}|d||f}|i}|D]}|d}||vr |dddd||<||}|d|k}|d|dzdz } |r'|d xx|dz cc<|d xx| z cc<i|d xx|dz cc<|d xx|dd z | z z cc<|D]}|d } |d } | dkrp| dkrj| | z } | dkr$| |d <d|d <| dkr|d | | z znd|d <M| dkr&d|d <| |d <| dkr|d | | z znd|d <yd|d <d|d <d|d <t |S) z Aggregate trades per market to compute net position and cost basis. Returns list of {market_id, yes_shares, no_shares, total_cost}. zSELECT market_id, buyer, seller, outcome, price, size FROM trades WHERE buyer=? OR seller=? ORDER BY timestamp ASCrr)r yes_shares no_shares total_costrrrr:rvrxrwr;)r$rrr0rrUlist) r'r2r r3 by_marketr5r^posr6costyes_sno_snets r get_user_positions_by_marketrst ==??D 77D << % t    hjj   JJLLL!#I @@ n i  +.aa_`aaIcNnW:%& AgJ&+@A  @    6 *        %        & )       !F)i"74!? ?    !!&&L!; 199$,CQww$'L!#$K INQRC $5u$E$EXYL!!q$%L!$'4K IMPQC $5#$E$EWXL!!$%L!#$K $%L!    "" # ##r)rrrrrr)r&)__doc__rr#pathlibr__file__parentrrrrintfloatr%ryror7rarerjrmrqrtrrCrr rs2   $x.. * , %%%RAC23/1   S 3 s  '* :=  */  *-     ST$Z6AUAtAAAAH3cCc#$+ * *c * * * *3$#3$$t*3$3$3$3$3$3$r