KiSrSSKrSSKrSSKrSSKJr SSKJrJr SSK J r SSK J r \"SS55r \"S S 55r"S S 5rg) z SQLite database for persistence.N)Path) dataclassfield)Table) TrustLevelc\rSrSr%Sr\\S'\\S'\\S'\\S'Sr\\S'S r \\S '\ Rr \ \S 'S r \\S '\"\S9r\\\S'Srg) UserRecord aUser record loaded from the database. Attributes: id: Internal numeric id. username: Username (display name). password_hash: Stored password hash. uuid: Persistent UUID for stats tracking. locale: Preferred locale. preferences_json: JSON preferences blob. trust_level: Trust level for permissions. approved: Whether the account is approved. fluent_languages: Languages the user knows. idusername password_hashuuidenlocale{}preferences_json trust_levelFapproved)default_factoryfluent_languagesN)__name__ __module__ __qualname____firstlineno____doc__int__annotations__strrrrUSERrrboolrlistr__static_attributes__r:c:\Users\dbart\PlayPalace11\server\persistence\database.pyr r s_  GM IFC c (ooK-Hd"'"=d3i=r$r c`\rSrSr%Sr\\S'\\S'\\S'\\S'\\S'\\S'\\S 'S rg ) SavedTableRecord(a*Saved table record from the database. Attributes: id: Internal numeric id. username: Owner username. save_name: User-visible save name. game_type: Game type identifier. game_json: Serialized game state. members_json: Serialized member list. saved_at: Timestamp string. r r save_name game_type game_json members_jsonsaved_atrN) rrrrrrrrr#rr$r%r'r'(s-  GMNNNMr$r'c\rSrSrSrShS\\-4SjjrSiSjrSiSjr SiS jr SiS jr \ S \ RS\4S j5rS rS\S\S-4SjrS\R(S4S\S\S\S\S\S\4 SjjrS\S\4SjrS\S\SS4SjrS\S\SS4SjrS\S\SS4SjrS\S\S\S\SS4 SjrS\S\ RS-4S jrSjS\S!\S"\S-SS4S#jjrS\4S$jrS\S-4S%jr S\S\SS4S&jr!SkS'\S\"\4S(jjr#S\"\4S)jr$S\S\4S*jr%S\S\4S+jr&SkS'\S\"\4S,jjr'S\S-4S-jr(SkS.\S\"\4S/jjr)S\S\"\4S0jr*S\S1\"\SS4S2jr+S\S\"\4S3jr,S\S4\S\4S5jr-S\S4\S\4S6jr.S4\S\"\4S7jr/S\0\\"\44S8jr1S9\2SS4S:jr3S;\S\2S-4S<jr4S\"\24S=jr5S;\SS4S>jr6SiS?jr7S@\"\2SS4SAjr8S\SB\SC\SD\SE\S\94 SFjr:S\S\"\94SGjr;SH\S\9S-4SIjr\\\\4SN\0S-S\4 SOjjr?SlSP\SC\S-SQ\S\"\04SRjjr@SS\S\"\04STjrASjSC\SQ\S-S\"\>4SUjjrBSC\S\04SVjrCSjSP\SC\S-S\04SWjjrDSP\SC\S\>\E\E4S-4SXjrFSP\SC\SY\ESZ\ESS4 S[jrGSmSC\SQ\S\"\>\\E\E44S\jjrHSiS]jrIS^\S_\S`\Sa\S;\S-Sb\SS4ScjrJS\"\04SdjrKS^\SS4SejrLSiSfjrMSgrNg)nDatabase?zcSQLite database for PlayPalace persistence. Stores users, tables, saved tables, and game results. db_pathc2[U5UlSUlg)z,Initialize the database wrapper with a path.N)rr1_conn)selfr1s r%__init__Database.__init__EsG} 04 r$returnNc[R"[UR55Ul[RURl URRS5 UR5 g![R a9n[ SURSU3[RS9 [S5UeSnAff=f)z4Connect to the database and create tables if needed.z#ERROR: Failed to open database at 'z': fileNzPRAGMA foreign_keys = ON)sqlite3connectrr1r3Errorprintsysstderr SystemExitRow row_factoryexecute_create_tables)r4excs r%r=Database.connectJs ) T\\):;DJ")  56 }} ) 5dll^3seLZZ Q-S (  )s.A;;C4CCcjUR(a"URR5 SUlgg)zClose the database connection.N)r3close)r4s r%rJDatabase.closeXs% :: JJ   DJ r$cFURR5nURS5 URS5 URS5 URS5 URS5 URS5 URS5 URS5 URS 5 URS 5 URS 5 URS 5 URS 5 URR5 UR 5 g)z+Create database tables if they don't exist.a CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT UNIQUE NOT NULL, password_hash TEXT NOT NULL, uuid TEXT NOT NULL, locale TEXT DEFAULT 'en', preferences_json TEXT DEFAULT '{}', trust_level INTEGER DEFAULT 1, approved INTEGER DEFAULT 0, fluent_languages TEXT DEFAULT '[]' ) a* CREATE TABLE IF NOT EXISTS transcriber_assignments ( user_id INTEGER NOT NULL, lang_code TEXT NOT NULL, PRIMARY KEY (user_id, lang_code), FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) a9 CREATE TABLE IF NOT EXISTS tables ( table_id TEXT PRIMARY KEY, game_type TEXT NOT NULL, host TEXT NOT NULL, members_json TEXT NOT NULL, game_json TEXT, status TEXT DEFAULT 'waiting' ) ay CREATE TABLE IF NOT EXISTS saved_tables ( id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, save_name TEXT NOT NULL, game_type TEXT NOT NULL, game_json TEXT NOT NULL, members_json TEXT NOT NULL, saved_at TEXT NOT NULL ) a CREATE TABLE IF NOT EXISTS game_results ( id INTEGER PRIMARY KEY AUTOINCREMENT, game_type TEXT NOT NULL, timestamp TEXT NOT NULL, duration_ticks INTEGER, custom_data TEXT ) a CREATE TABLE IF NOT EXISTS game_result_players ( id INTEGER PRIMARY KEY AUTOINCREMENT, result_id INTEGER REFERENCES game_results(id) ON DELETE CASCADE, player_id TEXT NOT NULL, player_name TEXT NOT NULL, is_bot INTEGER NOT NULL, is_virtual_bot INTEGER NOT NULL DEFAULT 0 ) zm CREATE INDEX IF NOT EXISTS idx_game_results_type ON game_results(game_type) zr CREATE INDEX IF NOT EXISTS idx_game_results_timestamp ON game_results(timestamp) zx CREATE INDEX IF NOT EXISTS idx_result_players_player ON game_result_players(player_id) a CREATE TABLE IF NOT EXISTS player_ratings ( player_id TEXT NOT NULL, game_type TEXT NOT NULL, mu REAL NOT NULL, sigma REAL NOT NULL, PRIMARY KEY (player_id, game_type) ) ax CREATE TABLE IF NOT EXISTS refresh_tokens ( id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, token TEXT UNIQUE NOT NULL, expires_at INTEGER NOT NULL, created_at INTEGER NOT NULL, revoked_at INTEGER, replaced_by TEXT ) zt CREATE INDEX IF NOT EXISTS idx_refresh_tokens_username ON refresh_tokens(username) zu CREATE INDEX IF NOT EXISTS idx_refresh_tokens_expires ON refresh_tokens(expires_at) N)r3cursorrEcommit_run_migrationsr4rMs r%rFDatabase._create_tables^s+""$                                       r$c$URR5nURS5 UR5Vs/sHo"SPM nnSU;a+URS5 URR 5 SU;a<URS5 URS5 URR 5 URS5 UR5Vs/sHo"SPM nnS U;a+URS 5 URR 5 URS 5 URR 5 SU;a+URS5 URR 5 URS5 UR5(d+URS5 URR 5 URS5 UR5(dNURS5 URS5 URS5 URR 5 ggs snfs snf![ R a)n[S [RS 9 [S5UeSnAff=f)z/Run database migrations for existing databases.zPRAGMA table_info(users)r;rz:ALTER TABLE users ADD COLUMN trust_level INTEGER DEFAULT 1rz7ALTER TABLE users ADD COLUMN approved INTEGER DEFAULT 0zUPDATE users SET approved = 1z&PRAGMA table_info(game_result_players)is_virtual_botzKALTER TABLE game_result_players ADD COLUMN is_virtual_bot INTEGER DEFAULT 0zTCREATE UNIQUE INDEX IF NOT EXISTS idx_users_username_lower ON users(lower(username))zzERROR: Duplicate usernames exist when compared case-insensitively. Please resolve duplicates before restarting the server.r9Nrz?ALTER TABLE users ADD COLUMN fluent_languages TEXT DEFAULT '[]'zTSELECT name FROM sqlite_master WHERE type='table' AND name='transcriber_assignments'aF CREATE TABLE IF NOT EXISTS transcriber_assignments ( user_id INTEGER NOT NULL, lang_code TEXT NOT NULL, PRIMARY KEY (user_id, lang_code), FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) zKSELECT name FROM sqlite_master WHERE type='table' AND name='refresh_tokens'a CREATE TABLE IF NOT EXISTS refresh_tokens ( id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, token TEXT UNIQUE NOT NULL, expires_at INTEGER NOT NULL, created_at INTEGER NOT NULL, revoked_at INTEGER, replaced_by TEXT ) z CREATE INDEX IF NOT EXISTS idx_refresh_tokens_username ON refresh_tokens(username) z CREATE INDEX IF NOT EXISTS idx_refresh_tokens_expires ON refresh_tokens(expires_at) ) r3rMrEfetchallrNr<IntegrityErrorr?r@rArBfetchone)r4rMrowcolumns grp_columnsrGs r%rODatabase._run_migrationss4""$ 12%+__%67%6cq6%67  ' NNW X JJ    W $ NNT U NN: ; JJ    ?@)/):;):#1v): ; ; . NN]  JJ    ) NNf  JJ    W , NN\ ] JJ     b   NN  JJ     Y   NN   NN  NN  JJ   )!u8<%% ) JZZ  Q-S (  )s#I$I &+IJ&$J  JrWcUSbUSOSn[USUSUSUSUS=(d SUS =(d S [U5US b[US 5OS [R"US =(d S5S9 $)z'Build a UserRecord from a database row.rr;r r r rrrrrrFr[]) r r r rrrrrr)r rr!jsonloads)rWtrust_level_ints r%_user_from_rowDatabase._user_from_row5s14M0B0N#m,TU4y_o.Vx=(D !34<"?3.1*o.IT#j/*u!ZZ,>(?(G4H  r$zdid, username, password_hash, uuid, locale, preferences_json, trust_level, approved, fluent_languagesr cURR5nURSURS3U45 UR 5nU(aUR U5$g)zGet a user by username.SELECT z, FROM users WHERE lower(username) = lower(?)N)r3rMrE _USER_COLUMNSrVr`r4r rMrWs r%get_userDatabase.get_userGs^""$d(())U V K oo &&s+ +r$rFr rrrc (SSKn[UR55nURR 5nUR SXXsUR U(aSOS45 URR5 [URUUUUUUS9$)aCreate a new user with a generated UUID. Args: username: Username (display name). password_hash: Hashed password. locale: Preferred locale. trust_level: Initial trust level. approved: Whether the account is approved. Returns: Created UserRecord. rNzjINSERT INTO users (username, password_hash, uuid, locale, trust_level, approved) VALUES (?, ?, ?, ?, ?, ?)r;)r r r rrrr) rruuid4r3rMrEvaluerNr lastrowid) r4r r rrr uuid_module user_uuidrMs r% create_userDatabase.create_userSs( # ))+, ""$ x i9J9JQYA_` a  '#  r$cURR5nURSU45 UR5SL$)zCheck if a user exists.z4SELECT 1 FROM users WHERE lower(username) = lower(?)Nr3rMrErVr4r rMs r% user_existsDatabase.user_existszs8""$MPX{[ ,,r$cURR5nURSX!45 URR5 g)zUpdate a user's locale.z ?z FROM users WHERE approved = 0 r3rMrErdrBANNEDrjrTr`r4rrMrWs r%get_pending_usersDatabase.get_pending_userss ""$  NN$,,--_`""((*  NNWT%7%7$88VW X4:OO4EF4ES##C(4EFFFs B*cURR5nURSURS3[R R 45 UR5Vs/sHo RU5PM sn$s snf)z@Get all banned users. Returns: List of banned UserRecords. rc! FROM users WHERE trust_level = ?rr4rMrWs r%get_banned_usersDatabase.get_banned_users st ""$d(())J K    $ $ & 5;OO4EF4ES##C(4EFFFs&BcURR5nURSU45 URR5 URS:$)zuApprove a user account. Args: username: Username to approve. Returns: True if user was found and approved. z>UPDATE users SET approved = 1 WHERE lower(username) = lower(?)rr3rMrErNrowcountrrs r% approve_userDatabase.approve_usersK""$ L K  ""r$cURR5nURSU45 URR5 URS:$)zrDelete a user account. Args: username: Username to delete. Returns: True if user was found and deleted. z2DELETE FROM users WHERE lower(username) = lower(?)rrrrs r% delete_userDatabase.delete_user+sF""$Kh[Y ""r$cURR5nU(aSURSURS3[R R [RR 45 O9URSURS3[RR 45 UR5Vs/sHo0RU5PM sn$s snf)zGet all approved users who are not admins (trust_level < ADMIN). Args: exclude_banned: If True (default), excludes banned users from the results. rczX FROM users WHERE approved = 1 AND trust_level > ? AND trust_level < ? ORDER BY usernamezD FROM users WHERE approved = 1 AND trust_level < ? ORDER BY username) r3rMrErdrrrjADMINrTr`rs r%get_non_admin_usersDatabase.get_non_admin_users9s ""$  NN$,,-.FG""((**:*:*@*@A  NN$,,--qr!!'') 5;OO4EF4ES##C(4EFFFsCcURR5nURSURS3[R R 45 UR5nU(aURU5$g)z0Get the server owner (there should only be one).rcrN) r3rMrErdrrrjrVr`rs r%get_server_ownerDatabase.get_server_ownerLsk""$d(())J K  $ $ * * , oo &&s+ +r$include_server_ownercURR5nU(a:URSURS3[R R 45 O9URSURS3[R R 45 UR5Vs/sHo0RU5PM sn$s snf)zGet all users who are admins (trust_level >= ADMIN). Args: include_server_owner: If True, includes the server owner in the list. If False, only returns admins (not server owner). rcz4 FROM users WHERE trust_level >= ? ORDER BY usernamez3 FROM users WHERE trust_level = ? ORDER BY username) r3rMrErdrrrjrTr`)r4rrMrWs r%get_admin_usersDatabase.get_admin_usersXs""$  NN$,,--ab!!'')  NN$,,--`a!!'') 5;OO4EF4ES##C(4EFFFs'CcURR5nURSU45 UR5nU(a"[R "US=(d S5$/$)zpGet the languages a user knows. Args: username: Username to look up. Returns: List of language codes. zCSELECT fluent_languages FROM users WHERE lower(username) = lower(?)rr\)r3rMrErVr]r^res r%get_user_fluent_languages"Database.get_user_fluent_languagesnsZ""$ Q K oo ::c"45=> > r$ languagescURR5nURS[R"U5U45 URR 5 g)z|Replace a user's fluent languages list. Args: username: Username to update. languages: New list of language codes. zFUPDATE users SET fluent_languages = ? WHERE lower(username) = lower(?)N)r3rMrEr]dumpsrN)r4r rrMs r%set_user_fluent_languages"Database.set_user_fluent_languagessH""$ T ZZ "H -  r$cURR5nURSU45 UR5Vs/sHo3SPM sn$s snf)zGet languages a user is approved to transcribe. Args: username: Username to look up. Returns: List of assigned language codes. zSELECT ta.lang_code FROM transcriber_assignments ta JOIN users u ON ta.user_id = u.id WHERE lower(u.username) = lower(?) ORDER BY ta.lang_code lang_coder3rMrErTres r%get_transcriber_languages"Database.get_transcriber_languagessU""$ GK  -3OO,=>,=SK ,=>>>Arc(URR5nURSU45 UR5nU(dgUSnURSXR45 URR 5 g![ R a gf=f)zAssign a user as transcriber for a language. Args: username: Username to assign. lang_code: Language code to assign. Returns: True if added, False if the assignment already exists. 5SELECT id FROM users WHERE lower(username) = lower(?)Fr zFINSERT INTO transcriber_assignments (user_id, lang_code) VALUES (?, ?)T)r3rMrErVrNr<rUr4r rrMrWuser_ids r%add_transcriber_assignment#Database.add_transcriber_assignments""$NQYP[\ood)  NNX$  JJ   %%  s -A::BBcURR5nURSU45 UR5nU(dgUSnURSXR45 URR 5 UR S:$)zRemove a transcriber assignment. Args: username: Username to remove assignment from. lang_code: Language code to remove. Returns: True if removed, False if the assignment was not found. rFr zGDELETE FROM transcriber_assignments WHERE user_id = ? AND lang_code = ?r)r3rMrErVrNrrs r%remove_transcriber_assignment&Database.remove_transcriber_assignmentsz""$NQYP[\ood) U    ""r$cURR5nURSU45 UR5Vs/sHo3SPM sn$s snf)zGet all usernames assigned as transcribers for a language. Args: lang_code: Language code to look up. Returns: List of usernames. z~SELECT u.username FROM transcriber_assignments ta JOIN users u ON ta.user_id = u.id WHERE ta.lang_code = ? ORDER BY u.usernamer r)r4rrMrWs r%get_transcribers_for_language&Database.get_transcribers_for_languagesS""$ 9L  ,2??+<=+ L r$cURR5nURS5 URR5 g)z$Delete all tables from the database.zDELETE FROM tablesNrvrPs r%delete_all_tablesDatabase.delete_all_tables=s1""$+, r$rc8UHnURU5 M g)zSave multiple tables.N)r)r4rrs r%save_all_tablesDatabase.save_all_tablesCsE OOE "r$r)r*r+r,c SSKJn UR5R5nURR 5nUR SXX4XW45 URR 5 [URUUUUUUS9$)z,Save a table state to a user's saved tables.r)datetimez INSERT INTO saved_tables (username, save_name, game_type, game_json, members_json, saved_at) VALUES (?, ?, ?, ?, ?, ?) r r r)r*r+r,r-) rnow isoformatr3rMrErNr'rk) r4r r)r*r+r,rr-rMs r%save_user_tableDatabase.save_user_tableJs &<<>++-""$ ) O   %  r$c URR5nURSU45 /nUR5H6nUR [ USUSUSUSUSUSUSS 95 M8 U$) z Get all saved tables for a user.zRSELECT * FROM saved_tables WHERE lower(username) = lower(?) ORDER BY saved_at DESCr r r)r*r+r,r-r)r3rMrErTrr')r4r rMrecordsrWs r%get_user_saved_tablesDatabase.get_user_saved_tablesks""$ ` K ??$C NN 4y _!+.!+.!+.!$^!4 _ %r$save_idc URR5nURSU45 UR5nU(dg[ USUSUSUSUSUSUS S 9$) zGet a saved table by ID.z'SELECT * FROM saved_tables WHERE id = ?Nr r r)r*r+r,r-r)r3rMrErVr')r4rrMrWs r%get_saved_tableDatabase.get_saved_tablesy""$@7*Moo4y_+&+&+&^,_  r$cURR5nURSU45 URR5 g)zDelete a saved table.z%DELETE FROM saved_tables WHERE id = ?Nrv)r4rrMs r%delete_saved_tableDatabase.delete_saved_tables5""$> K r$ timestampduration_ticksplayers custom_datac RURR5nURSUUUU(a[R"U5OS45 UR nUH-uppURSXxX(aSOSU (aSOS45 M/ URR 5 U$)a7 Save a game result to the database. Args: game_type: The game type identifier timestamp: ISO format timestamp duration_ticks: Game duration in ticks players: List of (player_id, player_name, is_bot, is_virtual_bot) tuples custom_data: Game-specific result data Returns: The result ID z INSERT INTO game_results (game_type, timestamp, duration_ticks, custom_data) VALUES (?, ?, ?, ?) Nz INSERT INTO game_result_players (result_id, player_id, player_name, is_bot, is_virtual_bot) VALUES (?, ?, ?, ?, ?) r;r)r3rMrEr]rrkrN) r4r*r r r rrM result_id player_id player_nameis_botrSs r%save_game_resultDatabase.save_game_results.""$   +6 ;'D   $$ ?F :IF NN{AQ^abc  ?F r$rlimitc VURR5nU(aURSXU45 OURSX45 /nUR5HInUR USUSUSUSUS(a[ R "US5O0S.5 MK U$) z Get a player's game history. Args: player_id: The player ID to look up game_type: Optional filter by game type limit: Maximum number of results Returns: List of game result dictionaries a^ SELECT gr.id, gr.game_type, gr.timestamp, gr.duration_ticks, gr.custom_data FROM game_results gr INNER JOIN game_result_players grp ON gr.id = grp.result_id WHERE grp.player_id = ? AND gr.game_type = ? ORDER BY gr.timestamp DESC LIMIT ? aI SELECT gr.id, gr.game_type, gr.timestamp, gr.duration_ticks, gr.custom_data FROM game_results gr INNER JOIN game_result_players grp ON gr.id = grp.result_id WHERE grp.player_id = ? ORDER BY gr.timestamp DESC LIMIT ? r r*r r r)r r*r r r)r3rMrErTrr]r^)r4rr*rrMresultsrWs r%get_player_game_history Database.get_player_game_historys"""$  NNu-  NN" ??$C NNd)!$[!1!$[!1&)*:&;EHEW4::c-.@#A]_  %r$rc URR5nURSU45 UR5Vs/sH0nUSUS[ US5USb[ US5OSS.PM2 sn$s snf)z+Get all players for a specific game result.z SELECT player_id, player_name, is_bot, is_virtual_bot FROM game_result_players WHERE result_id = ? rrrrSF)rrrrS)r3rMrErTr!)r4rrMrWs r%get_game_result_players Database.get_game_result_players s""$  L  "(  )!-"=1s8}-'(4#'s+;'<"=  )   s7A:c URR5nU(aURSX45 OURSU45 UR5Vs/sHnUSUSUSUSUS4PM sn$s snf)z Get game results for a game type. Args: game_type: The game type to query limit: Optional maximum number of results Returns: List of tuples: (id, game_type, timestamp, duration_ticks, custom_data) z SELECT id, game_type, timestamp, duration_ticks, custom_data FROM game_results WHERE game_type = ? ORDER BY timestamp DESC LIMIT ? z SELECT id, game_type, timestamp, duration_ticks, custom_data FROM game_results WHERE game_type = ? ORDER BY timestamp DESC r r*r r rrr4r*rrMrWs r%get_game_statsDatabase.get_game_stats s""$  NN"  NN   $(  ) D K K $%M"  )   s A>cURR5nURSU45 UR5nUS=(d SUS=(d SUS=(d SS.$)zq Get aggregate statistics for a game type. Returns: Dictionary with total_games, total_duration_ticks, etc. z SELECT COUNT(*) as total_games, SUM(duration_ticks) as total_duration, AVG(duration_ticks) as avg_duration FROM game_results WHERE game_type = ? total_gamesrtotal_duration avg_duration)r#total_duration_ticksavg_duration_ticksrq)r4r*rMrWs r%get_game_stats_aggregate!Database.get_game_stats_aggregateNsn""$ L oo}-2$'(8$9$>Q"%n"5":  r$cURR5nU(aURSX45 OURSU45 UR5nSUS=(d S0$)z Get statistics for a player. Args: player_id: The player ID game_type: Optional filter by game type Returns: Dictionary with games_played, etc. z SELECT COUNT(*) as games_played FROM game_result_players grp INNER JOIN game_results gr ON grp.result_id = gr.id WHERE grp.player_id = ? AND gr.game_type = ? z SELECT COUNT(*) as games_played FROM game_result_players WHERE player_id = ? games_playedrrqr4rr*rMrWs r%get_player_statsDatabase.get_player_statshss""$  NN &  NN   oo C/41  r$cURR5nURSX45 UR5nU(a USUS4$g)zd Get a player's rating for a game type. Returns: (mu, sigma) tuple or None if no rating exists zp SELECT mu, sigma FROM player_ratings WHERE player_id = ? AND game_type = ? musigmaNrqr,s r%get_player_ratingDatabase.get_player_ratingsV""$  "  oo Is7|, ,r$r0r1cURR5nURSXX445 URR5 g)z0Set or update a player's rating for a game type.z INSERT OR REPLACE INTO player_ratings (player_id, game_type, mu, sigma) VALUES (?, ?, ?, ?) Nrv)r4rr*r0r1rMs r%set_player_ratingDatabase.set_player_ratingsA""$ 2 -   r$cURR5nURSX45 UR5Vs/sHoDSUSUS4PM sn$s snf)zy Get the rating leaderboard for a game type. Returns: List of (player_id, mu, sigma) tuples sorted by mu descending z SELECT player_id, mu, sigma FROM player_ratings WHERE game_type = ? ORDER BY mu DESC LIMIT ? rr0r1rrs r%get_rating_leaderboardDatabase.get_rating_leaderboardsf""$    HNGXYGX[!3t9c'l;GXYYYsAcURR5nURS5 URR5 g)z.Create virtual_bots table if it doesn't exist.ao CREATE TABLE IF NOT EXISTS virtual_bots ( name TEXT PRIMARY KEY, state TEXT NOT NULL, online_ticks INTEGER NOT NULL DEFAULT 0, target_online_ticks INTEGER NOT NULL DEFAULT 0, table_id TEXT, game_join_tick INTEGER NOT NULL DEFAULT 0 ) NrvrPs r%_ensure_virtual_bots_table#Database._ensure_virtual_bots_tables7""$   r$namestate online_tickstarget_online_ticksgame_join_tickc UR5 URR5nURSXX4XV45 URR 5 g)z%Save or update a virtual bot's state.z INSERT OR REPLACE INTO virtual_bots (name, state, online_ticks, target_online_ticks, table_id, game_join_tick) VALUES (?, ?, ?, ?, ?, ?) Nr;r3rMrErN)r4r=r>r?r@rrArMs r%save_virtual_botDatabase.save_virtual_botsQ '')""$  ,X V   r$c UR5 URR5nURS5 UR 5Vs/sHnUSUSUSUSUSUSS.PM sn$s snf) z.Load all virtual bot states from the database.z SELECT name, state, online_ticks, target_online_ticks, table_id, game_join_tick FROM virtual_bots r=r>r?r@rrA)r=r>r?r@rrA)r;r3rMrErTrs r%load_all_virtual_botsDatabase.load_all_virtual_botss '')""$  (  )F W #N 3'*+@'A O"%&6"7  )   s%A6cUR5 URR5nURSU45 URR 5 g)z.Delete a single virtual bot from the database.z'DELETE FROM virtual_bots WHERE name = ?NrC)r4r=rMs r%delete_virtual_botDatabase.delete_virtual_botsA '')""$@4'J r$cUR5 URR5nURS5 URR 5 g)z*Delete all virtual bots from the database.zDELETE FROM virtual_botsNrCrPs r%delete_all_virtual_bots Database.delete_all_virtual_bots s= '')""$12 r$)r3r1)z playpalace.db)r7N)N)T)N2) )Orrrrrrrr5r=rJrFrO staticmethodr<rCr r`rdrfrr r!rnrsrwrzr}rrrrrrrr"rrrrrrrrrrrrrdictrrrrrrrrr'rrrr tuplerrrr r(r-floatr2r5r8r;rDrGrJrMr#rr$r%r/r/?s 5d 5  }~T p  GKK  J    {M  d):  ",// % % %  %  %  %  % N-C-D- 3st S     $' 58 FI   !s!w{{T/A!FJ  &) 8;d    $$ &t&P  * QU GGZ@PG G$z"2 G#S#T#" #C #D #G$G$zBRG& *t"3 GDGDDTG,#$s)& # $s) PT  ?#?$s)?$3344#c#c#d#.>s>tCy>$d3S >&:&$2 3 54< 4 e ST  #d5k#d#           Bcd;K6L, s /?$/F $#$ $(333 3  #sD$& ' 3D[3 3p!% 77:7 7 d 7r  d ., , C$J, $u+, \ # $ 4$ #$ #*$ PT$ P335PUCVY]C]( 3 3 E RW \` ,.ZZ%(Z eC%& 'Z."  !  *  , tDz ,str$r/)rr<r@r]pathlibr dataclassesrrrrserver.core.users.baserr r'r/rr$r%rXs[& (*- >> >4  ,QQr$