cj7+dZddlmZddlZddlZddlZddlZddlmZm Z m Z m Z ddl m Z mZmZmZGddZdS)z Persistent SQLite database repository for FreeExile Accounts, Tokens, and Characters. Implements ACID compliance, Write-Ahead Logging (WAL), and strict parameterization. ) annotationsN)AnyDictListOptional)Account AccountStatus OAuthIdentityOAuthProviderTypeceZdZdZd*d+dZd,d Zd-d Zd.dZej dfd/dZ d0dZ d1dZ d2dZ d3d Zd4d"Zd5d6d'Zd7d(Zd8d)ZdS)9DatabaseAccountRepositoryz;Persistent SQLite Account, Token, and Character Repository.data/freeexile_auth.dbdb_pathstrreturnNonec<||_|dS)N)r_init_database)selfrs -C:\Projects\FreeExile\server\auth\database.py__init__z"DatabaseAccountRepository.__init__s!  sqlite3.Connectionctj|jd}tj|_|d|d|d|S)Ng$@)timeoutzPRAGMA journal_mode = WAL;zPRAGMA foreign_keys = ON;zPRAGMA synchronous = NORMAL;)sqlite3connectrRow row_factoryexecuterconns r_get_connectionz)DatabaseAccountRepository._get_connections`t|T:::"; 1222 0111 3444 rc|5}|dddddS#1swxYwYdS)z/Create tables and indexes if they do not exist.ab CREATE TABLE IF NOT EXISTS accounts ( account_id TEXT PRIMARY KEY, email TEXT UNIQUE NOT NULL, username TEXT UNIQUE NOT NULL, password_hash TEXT NOT NULL, status INTEGER NOT NULL DEFAULT 1, roles TEXT NOT NULL DEFAULT '["player"]', created_at REAL NOT NULL, last_login_at REAL, failed_login_attempts INTEGER NOT NULL DEFAULT 0, lockout_until REAL ); CREATE TABLE IF NOT EXISTS oauth_identities ( provider INTEGER NOT NULL, provider_user_id TEXT NOT NULL, account_id TEXT NOT NULL, email TEXT NOT NULL, linked_at REAL NOT NULL, PRIMARY KEY (provider, provider_user_id), FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS refresh_tokens ( token_hash TEXT PRIMARY KEY, account_id TEXT NOT NULL, expires_at REAL NOT NULL, is_revoked INTEGER NOT NULL DEFAULT 0, created_at REAL NOT NULL, FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS remember_tokens ( token_hash TEXT PRIMARY KEY, account_id TEXT NOT NULL, device_id TEXT NOT NULL, device_name TEXT NOT NULL, hardware_hash TEXT NOT NULL, client_ip TEXT NOT NULL, expires_at REAL NOT NULL, is_revoked INTEGER NOT NULL DEFAULT 0, created_at REAL NOT NULL, FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS characters ( character_id TEXT PRIMARY KEY, account_id TEXT NOT NULL, name TEXT UNIQUE NOT NULL, class_type TEXT NOT NULL, level INTEGER NOT NULL DEFAULT 1, season_id TEXT NOT NULL, experience INTEGER NOT NULL DEFAULT 0, current_hp INTEGER NOT NULL DEFAULT 100, max_hp INTEGER NOT NULL DEFAULT 100, current_mana INTEGER NOT NULL DEFAULT 50, max_mana INTEGER NOT NULL DEFAULT 50, current_zone_id TEXT NOT NULL DEFAULT 'zone_boundless_sanctuary', pos_x REAL NOT NULL DEFAULT 0.0, pos_y REAL NOT NULL DEFAULT 10.0, inventory_json TEXT NOT NULL DEFAULT '[]', created_at REAL NOT NULL, FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS security_audit_logs ( log_id TEXT PRIMARY KEY, account_id TEXT, event_type TEXT NOT NULL, ip_address TEXT NOT NULL, device_fingerprint TEXT, status TEXT NOT NULL, details TEXT, timestamp REAL NOT NULL ); CREATE INDEX IF NOT EXISTS idx_accounts_email ON accounts(email); CREATE INDEX IF NOT EXISTS idx_accounts_username ON accounts(username); CREATE INDEX IF NOT EXISTS idx_characters_account ON characters(account_id); CREATE INDEX IF NOT EXISTS idx_audit_account ON security_audit_logs(account_id); N)r# executescriptr!s rrz(DatabaseAccountRepository._init_database$s  ! ! # #R t   Q Q Q Q R R R R R R R R R R R R R R R R R R s 8<<row sqlite3.Rowrc L|drtj|dndg}t|d|d|d|dt|d||d|d |d |d  }|5}|d |jf}|D]D}t|d}t||d|d|d|j |<E dddn #1swxYwY|S)Nrolesplayer account_idemailusername password_hashstatus created_at last_login_atfailed_login_attempts lockout_until) r+r,r-r.r/r)r0r1r2r3z^SELECT provider, provider_user_id, email, linked_at FROM oauth_identities WHERE account_id = ?providerprovider_user_id linked_at)r4r5r,r6) jsonloadsrr r#r r+fetchallr r oauth_identities)rr&r)accr"currp_types r_row_to_accountz)DatabaseAccountRepository._row_to_accountzs},/LH 3w<(((xj<(g,_o. X//<(o."%&=">o.     ! ! # # t,,p!C\\^^  *1Z=99/<#%&'9%:G* n 000$V,,                 sA6DD DNr,r.r-r/r r)Optional[list[str]]c dtjjdd}|}|}||ndg} t j| } tj} |5} | d|||||j | | f| dddn #1swxYwYt|||||| | S)Nacc_r*z INSERT INTO accounts (account_id, email, username, password_hash, status, roles, created_at) VALUES (?, ?, ?, ?, ?, ?, ?) )r+r,r-r.r/r)r0) uuiduuid4hexstriplowerr7dumpstimer#r valuecommitr) rr,r.r-r/r)r+ norm_email norm_user roles_list roles_jsonnowr"s rcreate_accountz(DatabaseAccountRepository.create_accountsI4DJLL,SbS133 [[]]((** NN$$ #/UUhZ Z ++ ikk  ! ! # # t LLZM6 ?r+)rJr#r rUrV)rrcrQr"r<r&s rget_account_by_refresh_tokenz6DatabaseAccountRepository.get_account_by_refresh_tokensikk  ! ! # # 9t,,r$C,,..C 9~~c,&788 9 9 9 9 9 9 9 9  9  9 9 9 9 9 9 9 9 9 9 9 9 9 9 9tsA B  BBc|5}|d|f|ddddS#1swxYwYdS)Nz=UPDATE refresh_tokens SET is_revoked = 1 WHERE token_hash = ?)r#r rL)rrcr"s rrevoke_refresh_tokenz.DatabaseAccountRepository.revoke_refresh_tokens  ! ! # # t LLX[hZj k k k KKMMM                  s,AAA)r)rrrr)rr)rr)r&r'rr) r,rr.rr-rr/r r)r@rr)r+rrrS)r,rrrS)r-rrrS)r4r r5rrrS)r_rrr)rb)rcrr+rrdrerr)rcrrrS)rcrrr)__name__ __module__ __qualname____doc__rr#rr?r PENDING_ACTIVATIONrRrVrYr\r^rarhrjrlrrr r s?EETTTTlJ!. @%) ! ! ! ! ! F    (         rr )rp __future__rr7rrJrDtypingrrrrserver.auth.modelsrr r r r rrrrrvs #"""""  ,,,,,,,,,,,,}}}}}}}}}}r