+ \>jJ>Rt^RIt^RIt^RIt^RIt^RIt^RIHt^RIH t ^RI t Rs Rt !RR4t R&RltRtR tR tR'R ltR tR tRtRtRtRtRtRtRtR(RltR)RltRtRtRt Rt!Rt"Rt#Rt$Rt%Rt&R*R lt'R!t(R"t)R#t*R$t+R%t,R#)+u HEAD OR TAIL — data layer (PostgreSQL). All money moves go through ledger_move() so every transfer is mirrored in the dual-entry ledger. Balances and ledger are always updated in the SAME transaction, so they can never drift. N)ThreadedConnectionPool)RealDictCursorc\f;\\P\P\P R7sR#R#)N)dsn)_POOLrconfig DB_POOL_MIN DB_POOL_MAXDB_DSN/opt/headortail/db.py init_poolrs- }&    2 2  r c0a]tRt^toRtRtRtRtVtR#)conn_ctxzABorrow a pooled connection; commit on success, rollback on error.cL\P4VnVP#N)rgetconnconn)selfs&r __enter__conn_ctx.__enter__sMMO yyr cVfVPP4MVPP4\P VP4R#r)rcommitrollbackrputconn)rexc_typeexctbs&&&&r __exit__conn_ctx.__exit__#s8   II    II    dii r )rN) __name__ __module__ __qualname____firstlineno____doc__rr__static_attributes____classdictcell__) __classdict__s@r rrsK!!r rcL\4;_uu_4pVP\R7pTPY;'gR4VR8XdVP 4uuRRR4#VR8XdVP 4uuRRR4#RRR4R# +'giR#;i)zGConvenience for simple autonomous queries. fetch in {None,'one','all'}.cursor_factoryoneNallr )rcursorrexecutefetchonefetchall)sqlparamsfetchccurs&&& r qr7+sn qhhnh5 C2& E><<>  E><<>  sAB&BB B# cf\P!VP44P4#r)hashlibsha256encode hexdigest)ps&r pw_hashr>8s >>!((* % / / 11r cp\PP\PP4#r)datetimenowtimezoneutcr r r rArA<s%    !2!2!6!6 77r c<V\P!^4,#))secrets token_hex)prefixs&r new_idrI@s G%%a( ((r c 4VPRWW4VWgV34R#)zFRecord one dual-entry transfer. Caller manages balance columns itself.z|INSERT INTO ledger (txn_type,debit_type,debit_id,credit_type,credit_id,amount,round_no,ref) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)N)r/) r6txn_type debit_typedebit_id credit_type credit_idamountround_norefs &&&&&&&&&r ledger_moverSDs'KK @ xi 3 r c p\4;_uu_4pVP4pVPR4VP4^,^8XdJVPRR\P \ \P4\P34RRR4R# +'giR#;i)z.Create the default admin once, if none exists.zSELECT count(*) FROM adminszKINSERT INTO admins (id,username,password_hash,balance) VALUES (%s,%s,%s,%s)adminN) rr.r/r0rDEFAULT_ADMIN_USERr>DEFAULT_ADMIN_PASSDEFAULT_ADMIN_BALANCE)r5r6s r bootstrap_adminrYPsz qhhj 12 <<>!  ! KK'&33223V5Q5QS  s BB$$ B5 cB\RRR7pV'd VR,#R#)z,SELECT last_round FROM game_state WHERE id=1r,r4 last_roundir7)rows r load_last_roundr_^s! :% HC #3| --r c \RV34R#)z.UPDATE game_state SET last_round=%s WHERE id=1Nr])rnds&r save_last_roundrbcs6?r c4\RV\V43RR7#)z]SELECT id,username,balance FROM admins WHERE username=%s AND password_hash=%s AND active=TRUEr,r[r7r>usernamepasswords&&r admin_loginrhhs# 0) *% 99r c4\RV\V43RR7#)zfSELECT id,username,balance,admin_id FROM agents WHERE username=%s AND password_hash=%s AND active=TRUEr,r[rdres&&r agent_loginrjns# 4) *% 99r c4\RV\V43RR7#)zwSELECT id,username,real_balance,agent_id,shop_name FROM cashiers WHERE username=%s AND password_hash=%s AND active=TRUEr,r[rdres&&r cashier_loginrlts$ F) *% 99r c \RV3RR7#)z?SELECT cashier_id FROM terminals WHERE token=%s AND active=TRUEr,r[r])tokens&r cashier_by_terminalrozs NXU $$r c \4;_uu_4pVP\R7pVPRV34VP 4pV'dVR,V8dRRRR/uuRRR4#VPRW34VP 4'gRRRR /uuRRR4#VPR W 34VPR W!34\ VR R VRW4RR/uuRRR4# +'giR#;i)r*z1SELECT balance FROM admins WHERE id=%s FOR UPDATEbalancesuccessFerrorzInsufficient admin balanceNz1SELECT id FROM agents WHERE id=%s AND admin_id=%szNot your agentz0UPDATE admins SET balance=balance-%s WHERE id=%sz0UPDATE agents SET balance=balance+%s WHERE id=%s topup_agentrUagentTrr.rr/r0rS)admin_idagent_idrPr5r6as&&& r rtrts qhhnh5 G(U LLNAiL6)ug/KL  G( *||~~ug/?@  FHZ[ FHZ[C7HU4  AC03.C0,9C00 D c \4;_uu_4pVP\R7pVPRV34VP 4pV'dVR,V8dRRRR/uuRRR4#VPRW34VP 4'gRRRR /uuRRR4#VPR W 34VPR W!34\ VR R VRW4RR/uuRRR4# +'giR#;i)r*z1SELECT balance FROM agents WHERE id=%s FOR UPDATErqrrFrszInsufficient agent balanceN3SELECT id FROM cashiers WHERE id=%s AND agent_id=%sNot your cashierz0UPDATE agents SET balance=balance-%s WHERE id=%szpsycopg2IntegrityError)rwrfrgphoneaidr5r6s&&&& r create_agentrs +C F ZZ1((*C KK* 15C 4S11Z  " "F5'+DEEFs(A//A A/ A, 'A//B  B c "\R4p\4;_uu_4pVP4pVPRWQ\ V4WV34RRR4RRRV/# +'giL;i \ P d RRRR /u#i;i) z;Cashiers are always born under an agent (never unassigned).CzdINSERT INTO cashiers (id,username,password_hash,agent_id,shop_name,phone) VALUES (%s,%s,%s,%s,%s,%s)NrrTrFrsrr)rxrfrg shop_namercidr5r6s&&&&& r create_cashierrs +C F ZZ1((*C KK. 18N 4s33Z  " "F5'+DEEFs(A00A A0 A- (A00B Bc \4;_uu_4pVP\R7pVPRV34VP 4pV'gRRRR/uuRRR4#VPRV34VP 4'gRRRR /uuRRR4#VPR V34VP 4R ,^8dRRRR /uuRRR4#VR ,pWb8XdRRRR/uuRRR4#VPRW!34VPRWW%R,V34\ VRRVRVVR,RV,R7RRR\ VR,4/uuRRR4# +'giR#;i)z Move a cashier to a new agent. Allowed ONLY when no shift is open. Cashier keeps their balance (logged as a transfer). Past bets keep their original agent_id (history pins to the old agent). Future bets use the new. r*zASELECT agent_id,real_balance FROM cashiers WHERE id=%s FOR UPDATErrFrszCashier not foundNz!SELECT id FROM agents WHERE id=%szTarget agent not foundzFSELECT count(*) AS n FROM shifts WHERE cashier_id=%s AND status='open'nz,Close the cashier's shift before reassigningrxzAlready managed by that agentz+UPDATE cashiers SET agent_id=%s WHERE id=%szyINSERT INTO cashier_reassignments (cashier_id,from_agent_id,to_agent_id,balance_at_move,admin_id) VALUES (%s,%s,%s,%s,%s) real_balancereassign_transferruzcashier )rRT moved_balancerr.rr/r0rSfloat)rwr to_agent_idr5r6ca from_agents&&& r reassign_cashierrsf qhhnh5 WM # \\^ug/BC  7+H||~~ug/GH  \M # <<>#  "ug/]^  ^  $ug/NO! " AKC\]  &[^2Dh O  C,gz7K~&J,C E4%>8J2KL5 s+AE,%(E,E,4E,E,&gen_activation_code..sXx!7>>"DEExs#%r|rrFrsr}NzNINSERT INTO activation_codes (code_hash,cashier_id,agent_id) VALUES (%s,%s,%s)Tcode) joinrangerr.r/r0r9r:r;r<)rxrrr5r6s&& r gen_activation_coders 77XuUVxX XD qhhj I* ,||~~ug/AB  \ ^^DKKM * 4 4 6 M  tVT ** s?C>AC C c \P!VP44P4p\ 4;_uu_4pVP \ R7pVPRV34VP4pV'gRRRR/uuRRR4#VPRVR,34\P!^ 4pVPR WTR ,34RR R VR VR ,/uuRRR4# +'giR#;i) r*zLSELECT id,cashier_id FROM activation_codes WHERE code_hash=%s AND used=FALSErrFrszInvalid or used codeNz1UPDATE activation_codes SET used=TRUE WHERE id=%sidz7INSERT INTO terminals (token,cashier_id) VALUES (%s,%s)rTrn) r9r:r;r<rr.rr/r0rFrG)rhr5r6r^rns& r activate_terminalrst{{}%//1A qhhnh5 %'(d ,llnug/EF  G#d)V!!"% M -. 04%ss qhhnh5 79C  G \\^ug?  Cj]Slln^, "$'D? 44RX}eCjQ sAC %AC  C cT\RRR7pV'd\VR,4#R#)#SELECT profit FROM house WHERE id=1r,r[profitrrrs r get_house_profitrNs% /u=A!"58 ++r cNVPRV 2V34VP4#)zIAggregate one cashier's activity since the given moment (default: today).aSELECT COALESCE(SUM(CASE WHEN status<>'cancelled' THEN stake END),0) AS staked, COUNT(*) FILTER (WHERE status<>'cancelled') AS bets, COALESCE(SUM(CASE WHEN redeemed THEN win_amount END),0) AS paid, COUNT(*) FILTER (WHERE status='win') AS wins FROM bets WHERE cashier_id=%s AND created_at >= )r/r0)r6r since_sqls&&&r _cashier_statsrTs5KK 3 4=+  A   <<>r c\4;_uu_4pVP\R7pVPRV34VP 4p.pVFp\ W%R,4p\ VR,4p\ VR,4pTPRVR,RVR,R\ VR,4R VR ,;'gR R VR ,;'gR R VR ,R\VR,4RVRVR\VR,4R\Wx, ^4/ 4K VuuRRR4# +'giR#;i)zBAll cashiers under an agent, each with today's activity + balance.r*zuSELECT id,username,real_balance,shop_name,phone,active,created_at FROM cashiers WHERE agent_id=%s ORDER BY created_atrstakedpaidrfrqrshoprrractive bets_todaybets staked_today paid_today wins_todaywins profit_todayN) rr.rr/r1rrappendintround) rxr5r6rowsoutrsrrs & r agent_cashiersrbs qhhnh5 JLT; X||~AsdG,A1X;'Fai0@ JJagz1Z=5>!23VQ{^=Q=Qr7))r8Qx[c!F)nnfdL#ai.fmQ 7  # sC D8AD88 E c \4;_uu_4pVP\R7pVPRV34VP 4pVPRV34VP 4pRV'd VR,MRRV'd\ VR,4MRR\ VR ,4R \ VR ,4/uuR R R 4# +'giR #;i) zEAgent's own balance + rolled-up totals across their cashiers (today).r*z/SELECT username,balance FROM agents WHERE id=%szRSELECT COALESCE(SUM(real_balance),0) s, COUNT(*) n FROM cashiers WHERE agent_id=%srfrrqr cashier_countrcashier_float_totalrN)rr.rr/r0rr)rxr5r6ryaggs& r agent_overviewrxs qhhnh5 E{S LLN 68@{ Dlln *  auQy\*S SS] !5S?   sA8C 8C C cP\4;_uu_4pVP\R7pVPRV34VP 4p.pVEF-pVPRVR,34VP 4Uu.uF qfR,NK ppR;r^p VFWp \ W+4p V\ V R,4, q\ V R,4, q\V R,4, p KY TPRVR,R VR ,R \ VR ,4R VR ,;'gR R VR ,R\V4RV R\V^4R\V ^4R\W, ^4/ 4EK0 VuuRRR4#uupi +'giR#;i)zLAll agents under an admin, each with balance, cashier count, today's profit.r*zdSELECT id,username,balance,phone,active,created_at FROM agents WHERE admin_id=%s ORDER BY created_atz)SELECT id FROM cashiers WHERE agent_id=%srrrrrrfrqrrrrrrrrN) rr.rr/r1rrrrlenr) rwr5r6agentsrryrcidsrrrrrs & r admin_agentsrsm qhhnh5 HJR VA KKCagZ P%(\\^4^dGG^D4 F"3,%( ,,eAfI6F.FdPSTUV\T]P^H^ JJagz1Z=)U1Y'cancelled' THEN stake END),0) staked, COALESCE(SUM(CASE WHEN redeemed THEN win_amount END),0) paid, COUNT(*) FILTER (WHERE status<>'cancelled') bets FROM bets WHERE created_at >= CURRENT_DATErrrr agent_countragent_balance_totalrrrrrrr gross_today house_profitrrjackpotrPN)rr.rr/r0rrr) r5r6agrtodayrrhpjps r admin_overviewrsP qhhnh5 OP \\^ VW \\^ A B uX'eFm0D 9: \\^ ;< \\^ 3r#w<)>bg SC\+@%3. #eFm,neFA>N %a.-v}a9P 2E"X,/3 buR\*c  ! sFF;F;; G )NN)Nr)r)rr) CURRENT_DATE)-r%r9rFr@jsonr psycopg2.poolrpsycopg2.extrasrrrrrr7r>rArIrSrYr_rbrhrjrlrortr~rrrrrrrrrrrrrrrrrr r r rs 0*    ! ! 28) . @ 9 9 9 $ !"!$ F F MF + R":0, )B( R , , "2< r