źiLUdZddlZddlZddlZddlZddlmZddlmZda ee e d<da eeje d<de fdZd ejfd Zd ejfd Z dFd e dee dee deed ef dZd e d eefdZd e d efdZdGd e ded efdZd e ded efdZded efdZde d eefdZdHd e de dee d e fdZ dIde d e d!ee d"ee d ef d#Zde d efd$Z dJd e dee d%ee ded ef d&Zde d eefd'Zd efd(Z de d efd)Z!d efd*Z"dKd+ed efd,Z#dKd-efd.Z$d ee dee dee d/e d0ed ef d1Z% dLde d2ee d3ee d4ee d5eed6ed7e d8ee d efd9Z&de d efd:Z'd;ed efd<Z(d=ed>ed efd?Z) dMd@e dAe dBe dCe*ded ef dDZ+d efdEZ,y)Num SQLite Database Layer for Battle System Python 표준 라이브러리만 사용 (sqlite3, json, time, uuid) N)Path)Optional_db_path _connectiondb_pathc|at|}|jjddttjjdz dz }t j |d}|jd|jd|jd |jr!|j}|j||a t||S) u 데이터베이스 초기화 - 스키마 파일 실행 - WAL 모드 설정 - foreign_keys, busy_timeout 설정 - 마이그레이션 적용 T)parentsexist_okschemaszbattle-db-schema.sqlF)check_same_threadzPRAGMA journal_mode=WALzPRAGMA foreign_keys=ONzPRAGMA busy_timeout=5000) rrparentmkdir__file__sqlite3connectexecuteexists read_text executescriptr_apply_migrations)rdb_file schema_pathconn schema_sqls D/home/jooyoung/.openclaw/workspace-260312/orchestration/server/db.pyinit_dbrsH7mG NN5x.''..:=SSK ??7e  wxjk^_ qyy{n &  ;<)'' !SV\\^3NOP sCD#(DDreturnc0t tdtS)u/단일 connection 반환 (thread-safe with WAL)z/Database not initialized. Call init_db() first.)r RuntimeErrorrrget_connectionr.SsLMM ruser_id device_hash display_namesettingsc *t}|j}tj}|jd|f|j }|rp|\}} } |r't j | } | j|nt j | } |jd||t j| |||fn9d\}} |xsi} |jd|||| t j| ||||f |j||| || dS)u!유저 생성 또는 업데이트z3SELECT level, exp, settings FROM users WHERE id = ?a  UPDATE users SET display_name = COALESCE(?, display_name), device_hash = COALESCE(?, device_hash), settings = ?, last_seen = ?, updated_at = ? WHERE id = ? rz INSERT INTO users (id, display_name, level, exp, settings, device_hash, created_at, last_seen, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) )userIdlevelexp displayNamer2) r.r r&rr!jsonloadsupdatedumpsr') r/r0r1r2rr nowrowr7r8existing_settingsmerged_settingss r upsert_userrB]s  D [[]F ))+C NNH7*U // C (+%s% "jj):;O  " "8 ,"jj):;O KO)Dc3PW X Z s".b |UCO1L3S*  + KKM##  rc t}|j}|jd|f|j}|sy|d|d|d|d|dt j |d|d |d |d |d d S)u 유저 조회z SELECT id, display_name, level, exp, preferred_locale, settings, device_hash, created_at, last_seen, updated_at FROM users WHERE id = ? Nrr5r ) r6r9r7r8preferredLocaler2 deviceHash createdAtlastSeen updatedAtr.r rr!r:r;)r/rr r?s rget_userrQs  D [[]F NN   // C a&1vQ1vq6JJs1v&!fVFV  rc >t}|j}t|}|s |ddddddidS|jd|f|j }t d|D}t ||z }i}|D]H\}} | |vr dddd|| <|| dxxdz cc<|dk(r|| d xxdz cc<9|| d xxdz cc<J|D]%} || d} | dkDr|| d | z d znd|| d <'||d |dt ||||r|t |z d z|dSd|dS)u/유저 통계 (기존 API 응답 형태 유지)r5r)r6r7r8 totalBattleswinslosseswinRate adapterStatszC SELECT result, adapter FROM battles WHERE user_id = ? c32K|]}|ddk(s dyw)rwinr5Nr-).0bs r z!get_user_stats..s3QQqTU]q3s )rTrUtotalr]rYrTrUdrVr7r8)r.r rQrfetchallsumlen) r/rr userbattlesrTrU adapter_statsresultadapterr]s rget_user_statsrgs  D [[]F G D    NN ooG 3'3 3D \D FM"2 - './1q%IM' "gw'1,' U? ' "6 *a / * ' "8 , 1 ,2! g&w/ K // C G s s:C * s    *  NN diik7 + -  KKM  r battle_datact}|j}|jdtt j }|jd}|jd}|jd}|jdd}|jdd}|dk(r|jdrd }|jd d } d |vr(t |d tr|d jd | } |d k(} | r| dzn| dz} d } |rt|| } | d } |jd||||||jd|jd||jd|jd|jd| | |jd|jdr$tj|jdnd|jd r$tj|jd ndtj|jdg|jd|jdrd nd|jdtj||jdtjf|r(|jd| rd nd|jd|f|jt|}|j|| | |d |S)!u전투 결과 저장rjr6 sessionIdgameIdrfunknownrerlrY enemyLevelr5 enemyStatsr7rFa INSERT INTO battles ( battle_id, user_id, session_id, game_id, adapter, battle_slot_id, scenario_node_id, result, remaining_hp, turns_used, total_damage, enemy_level, exp_gained, duration_ms, player_stats, enemy_stats, special_conditions, input_count, is_autoplay, client_build, raw_data, created_at ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) battleSlotIdscenarioNodeIdrn turnsUsed totalDamage durationMs playerStatsNspecialConditions inputCount isAutoplayr clientBuildrkz UPDATE game_sessions SET battles_played = battles_played + 1, battles_won = battles_won + ?, last_node_id = COALESCE(?, last_node_id) WHERE session_id = ? )rjrs userLevelre)r.r getr$uuiduuid4 isinstancedictrtrr:r=r&r'r<)rurr battle_idr/ session_idgame_idrfre enemy_level is_victoryrq user_level user_resultresponses r insert_battler/s  D [[]F C ,=>Iooh'G-Jooh'Gooi3G__Xy 1F {y9//,2K{"z+l2KT'R!,/33G[I E/J%/r![1_JJ%gz:  )  NN '() & $ & %6Aoom6T ;??=12Z^5@__\5R ;??<01X\ ;??#6;< % __\ * & ; TYY[1- D a1koo6F&G T  V KKMK H OO  Orrcht}|j}|jd|f|j}|sy|drt j |dni}id|dd|dd|d d |d d |d d|dd|dd|dd|dd|dd|dd|dd|dd|dd |d!rt j |d!ndd"|d#rt j |d#ndd$|d%rt j |d%ng|d&t |d'|d(|d)d*|S)+u전투 결과 조회a SELECT battle_id, user_id, session_id, game_id, adapter, battle_slot_id, scenario_node_id, result, remaining_hp, turns_used, total_damage, enemy_level, exp_gained, duration_ms, player_stats, enemy_stats, special_conditions, input_count, is_autoplay, client_build, raw_data, created_at FROM battles WHERE battle_id = ? Nrjrr6r5rwrrxrDrfrEr}rFr~rGrerHrnrIrrJr rz rs r rr{r|r)rrrrk)r.r rr!r:r;bool)rrr r?raw_datas r get_battlers  D [[]F NN  // C '*"gtzz#b'"2H CF #a&  SV  #a&   3q6   A   #a&  #a&  s1v  SV  s2w  c"g  SW  c"g  c"gtzz#b'*4  SWdjjR)$! " CGTZZB0# $"g3r7m2wW+ , - rr client_buildct}|j}ttj}t j }|j d|f|js|j d||f|j d|f|js|j d||||f|j d|||||f|j|S)u게임 세션 생성+SELECT game_id FROM games WHERE game_id = ?zl INSERT INTO games (game_id, adapters_used, created_at) VALUES (?, '[]', ?) z!SELECT id FROM users WHERE id = ?z INSERT INTO users (id, level, exp, created_at, last_seen, updated_at) VALUES (?, 1, 0, ?, ?, ?) z INSERT INTO game_sessions ( session_id, user_id, game_id, started_at, client_build ) VALUES (?, ?, ?, ?, ?) ) r.r r$rrr&rr!r')r/rrrr rr>s rcreate_sessionrs  D [[]FTZZ\"J ))+C NN@7*M ??  s^   NN6 C ??  sC % '  NN gw\ : <  KKM rr end_reason ending_id drop_node_idc Ft}|j}tj}|jd|||||f|j |jd|f|j }|sddiS|d|d|d|d|d |d |d |d |d S)u 세션 종료z UPDATE game_sessions SET ended_at = ?, end_reason = ?, ending_id = ?, drop_node_id = ? WHERE session_id = ? z SELECT session_id, user_id, game_id, started_at, ended_at, battles_played, battles_won, chapters_completed FROM game_sessions WHERE session_id = ? status not_foundrr5rrDrErFrGrH) rwr6rx startedAtendedAt battlesPlayed battlesWonchaptersCompleted endReason)r.r r&rr'r!)rrrrrr r>r?s r end_sessionrs  D [[]F ))+C NN z9lJ ?A KKM NN   // C +&&Va&a&Vq6Q!f V  rc t}|j}dddddd}gd}g}g}i}|jD]\} } | ||j| | <|jD]\} } | |vs |j | d| dk(rO| Mt | t tfr%|j tj| a|j | s|j | |sd d iS|j |d d j|d } |j| ||jd|dS)u세션 업데이트 last_node_idbattles_played battles_wonchapters_completed save_state) lastNodeIdrrr saveState)rrrrrz = ?r no_updateszUPDATE game_sessions SET z, z WHERE session_id = ?ok)rrw) r.r itemsrrorrlistr:r=joinrr') rkwargsrr camel_to_snakeallowed_fieldsupdatesvalues normalizedkeyvaluesqls rupdate_sessionrsM  D [[]F%)#1! NkNG FJlln9 U38 >%%c3/09!&&( % U . NNcU$< (l"u'8edD\2MM$**U"34MM%( e$ % ,'' MM* %dii&8%99N OC NN3KKM 44rrc Dt}|j}dg}|g}|r"|jd|j||dk(r|jdn|dk(r|jddj|}|j||j d|d |g} |j D]U} d } | d r t j| d } | j| d | d | d| d| d| d| d| d| d W| S#| d } Y@xYw)u 유저의 세션 목록 조회 status='active' → ended_at IS NULL status='completed' → end_reason='completed' z user_id = ?z game_id = ?activezended_at IS NULL completedzend_reason = 'completed' AND z SELECT session_id, game_id, started_at, ended_at, end_reason, battles_played, battles_won, last_node_id, save_state FROM game_sessions WHERE z6 ORDER BY started_at DESC LIMIT ? NrIrr5rrDrErFrGrH) rwrxrrrrrrr)r.r rorrr_r:r;) r/rrrhrr conditionsparams where_clausesessionsr?rs rget_user_sessionsr4sV  D [[]FJYF-( g ,- ; 45<< +L MM% NNn  H   q6 $!ZZA/  Q!fQ1vQ Va&a&#   ( O $ V s DDcJt}|j}|jd|f|j}|syd}|dr t j |d}|d|d|d|d|d|d |d |d |d |d |d|d|d|dS#|d}YAxYw)u(세션 상세 조회 (save_state 포함)a SELECT session_id, user_id, game_id, started_at, ended_at, end_reason, ending_id, chapters_completed, battles_played, battles_won, last_node_id, drop_node_id, client_build, save_state FROM game_sessions WHERE session_id = ? Nrrr5rrDrErFrGrHrIrJrrr)rwr6rxrrrendingIdrrrr dropNodeIdrrrP)rrr r?rs r get_sessionrms  D [[]F NN   // C J 2w !CG,J Va&a&Vq6VF VQ!f"g"g2w  !RJs BB"c t}|j}|jd|jd}|jd|jd}|jd|jd}|jd|jd}|jd|jd}|jd|jd}|dkDr||z dznd}|jd |jdxsd} ||||||t |d t | d d S) u글로벌 통계z1SELECT COUNT(DISTINCT user_id) FROM game_sessionsrz"SELECT COUNT(*) FROM game_sessionsz9SELECT COUNT(*) FROM game_sessions WHERE ended_at IS NULLzASELECT COUNT(*) FROM game_sessions WHERE end_reason = 'completed'zSELECT COUNT(*) FROM battlesz1SELECT COUNT(*) FROM battles WHERE result = 'win'r^zv SELECT AVG((ended_at - started_at) * 1000) FROM game_sessions WHERE ended_at IS NOT NULL r) uniqueUsers totalSessionsactiveSessionscompletedSessionsrSrTrVavgSessionDurationMs)r.r rr!round) rr unique_userstotal_sessionsactive_sessionscompleted_sessions total_battlesrTwin_rate avg_durations rget_analytics_overviewrsK  D [[]F NNFG??$Q'L NN78__&q)N NNNOoo'*O NNVW*1- NN12OO%a(M NNFG ?? Q D/1C7TUO3 rc t}|j}|jdi}|jD]}|d}|d}|dxsd}|dxsd}||vr ddggd||<||dxxdz cc<|dk(r||d xxdz cc<|dkDr||d j ||dkDst||d j |i}|j D]\}}|d} |d } | dkDr| | z d znd} |d rt |d t|d z nd} |d rt |d t|d z nd} | | t| dt| dt| dd ||<d|iS)u어댑터별 통계zt SELECT adapter, result, turns_used, duration_ms FROM battles WHERE adapter IS NOT NULL rr5rrD)r]rT turnsList durationListr]rYrTrrr^)r]rTrVavgTurns avgDurationMsadapters) r.r rr_rorr`rar)rr rr?rfreturnsrmstatsr]rTr avg_turnsrs rget_analytics_adaptersrs  D [[]F NN H ?a&QA !q6;Q ( " " !HW  '"a'" U? W f % * % 19 W k * 1 1% 8 a< W n - 4 4X >+?0F"..* gV}+019D5L3&!IN{I[Ck*+c% 2D.EEab RWXfRgs501Cn8M4NNmn Xq)i+"<3  w  rdaysc t}|j}tj|dzz }|jd|f|j Dcic] }|d|d }}|jd|f|j Dcic] }|d|d }}t t t|jt|jzDcgc]}|| c}}g} |D]7} | j| |j| d|j| dd9d| iScc}wcc}wcc}w)u일별 세션/전투 수iQz SELECT DATE(started_at, 'unixepoch') as date, COUNT(*) as count FROM game_sessions WHERE started_at >= ? GROUP BY date ORDER BY date rr5z SELECT DATE(created_at, 'unixepoch') as date, COUNT(*) as count FROM battles WHERE created_at >= ? GROUP BY date ORDER BY date )daterrcr) r.r r&rr_sortedsetrkeysror) rrr cutoffr?sessions_by_datebattles_by_dated all_datestimeliners rget_analytics_timeliner*sY  D [[]F YY[D5L )F NN  39//2CD3AADD NN  281BC#s1vs1v~COCst,<,A,A,C'DtOL`L`LbGc'cu!ghgtAuvwIH (,,T15&**43    H 1EDvsD;E"E *E timeout_minutesct}|j}tj|dzz }|jdtj|f|j}|j d|iS)u)오래된 진행 중 세션 자동 종료<z UPDATE game_sessions SET ended_at = ?, end_reason = 'timeout' WHERE ended_at IS NULL AND started_at < ? cleaned)r.r r&rrowcountr')r rr rupdateds rcleanup_stale_sessionsrTsj  D [[]F YY[Ob0 1F NN ))+v   ooGKKM w r event_type event_datac t}|j}|jd||||tj|t j f|j |jS)u이벤트 로그 저장z INSERT INTO events (user_id, session_id, game_id, event_type, event_data, created_at) VALUES (?, ?, ?, ?, ?, ?) )r.r rr:r=r&r' lastrowid)r/rrrrrr s r insert_eventrlsa  D [[]F NN :w DJJz4JDIIK XZ  KKM   r request_idnamegenre adapters_usedbattle_slot_countversion serving_urlc t}|j} tj} | jd|f| j } | r2| jd||||rt j |nd||||fn2| jd||||t j |xsg|||| f |j|ddS)u*게임 레지스트리 생성/업데이트ra UPDATE games SET request_id = COALESCE(?, request_id), name = COALESCE(?, name), genre = COALESCE(?, genre), adapters_used = COALESCE(?, adapters_used), battle_slot_count = COALESCE(?, battle_slot_count), version = COALESCE(?, version), serving_url = COALESCE(?, serving_url) WHERE game_id = ? Nz INSERT INTO games ( game_id, request_id, name, genre, adapters_used, battle_slot_count, version, serving_url, created_at ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) r)rxr)r.r r&rr!r:r=r') rrrrrrrrrr r>rs r upsert_gamers  D [[]F ))+C NN@7*M __ F   $+8djj'd+w@ A  z4jj,"-+s<  = KKM ..rct}|j}|jd|f|jd}|jd|f|jd}|dkDr||z dznd}|jd|f|j }i}|D]7\}} ||vrddd||<||dxxdz cc<| d k(s(||d xxdz cc<9|D]%}||d} | dkDr||d | z dznd||d <'|||||d S) u 게임 통계zB SELECT COUNT(*) FROM game_sessions WHERE game_id = ? rzh SELECT COUNT(*) FROM game_sessions WHERE game_id = ? AND end_reason = 'completed' r^zC SELECT adapter, result FROM battles WHERE game_id = ? )rTr]r]r5rYrTrV)rx playCountcompletedCount avgCompletionrW)r.r rr!r_) rrr play_countcompleted_countavg_completionrcrdrfrer]s rget_game_statsr%st  D [[]F NN "1%J NN oo'*O=G!^o 2S8QRN NN ooGM"0 - './!%r\s (3-,0 Xg(( )0"S"H=G..=>**<@OS448C=4&sm4>Ftn4X\4lchtn88C8D8tc#t4&S&c&d&XVtVVp,#,(4.,d!C!#!Xc]!VY!H,0.2)C)S)#C=)&sm)7;)V+5s+5+5Z>BAC7s7Xc]7&sm7;>7HL7r'C'HTN'R00d(#($(T6 6 p((d(T C 0 (3- Xc] "3- 58 FJ OR &;?CGOPCG*/*/(3-*/sm*/3;C=*/'~*/IL*/*/4