o pj4@@sUddlZddlZddlZeeZejddeejddejddejdd ejd d d d dZ da ej dBe d<ddZ ddZdCddZdeddfddZdeddfddZdCddZdedBfdd Zdeefd!d"Zd#eddfd$d%Zdeeeffd&d'Zd(ed)eddfd*d+Zd(ededBfd,d-Zd.ed/eddfd0d1Zd.edefd2d3Zd4ed5ed6edBddfd7d8Z deefd9d:Z!d4edefd;d<Z"d=edefd>d?Z#d@eddfdAdBZ$dS)DNDB_HOSTzofficialsite.krDB_PORTi [DB_USERadmin DB_PASSWORDz dlgks~123DB_NAME autotradingutf8mb4T)hostportuserpassworddbcharset autocommit_poolc stjddddtIdHat4IdH}|4IdH}|dIdHdD]0}z |d|IdHWq+ty[}zt| }d|vrQd |vrQWYd}~q+d}~ww|d IdH|d IdHz |d IdHWn!ty}zt| }d|vrd |vrWYd}~nd}~ww|d IdH|dIdH|dIdH|dIdH|dIdH|dIdHWdIdHn 1IdHswYWdIdHn 1IdHswYt ddS)u*커넥션 풀 생성 + 테이블 초기화)minsizemaxsizeNa CREATE TABLE IF NOT EXISTS trading_session ( id INT PRIMARY KEY DEFAULT 1, running TINYINT NOT NULL DEFAULT 0, total_budget DOUBLE NOT NULL DEFAULT 0, compound TINYINT NOT NULL DEFAULT 0, total_realized DOUBLE NOT NULL DEFAULT 0, total_trades INT NOT NULL DEFAULT 0, started_at VARCHAR(30), paper_mode TINYINT NULL DEFAULT NULL, virtual_krw DOUBLE NULL DEFAULT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) )z/ADD COLUMN paper_mode TINYINT NULL DEFAULT NULLz/ADD COLUMN virtual_krw DOUBLE NULL DEFAULT NULLzALTER TABLE trading_session duplicateexistsa+ CREATE TABLE IF NOT EXISTS positions ( ticker VARCHAR(20) PRIMARY KEY, coin VARCHAR(10) NOT NULL, status VARCHAR(10) NOT NULL, buy_price DOUBLE, buy_volume DOUBLE, bought_at VARCHAR(20), target_profit DOUBLE NOT NULL, stop_loss DOUBLE NOT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) a CREATE TABLE IF NOT EXISTS alerts ( id INT PRIMARY KEY, chat_id BIGINT NOT NULL, symbol VARCHAR(20) NOT NULL, target_price DOUBLE NOT NULL, direction VARCHAR(10) NOT NULL, price_unit VARCHAR(10) NOT NULL DEFAULT 'USD' ) z ALTER TABLE alerts ADD COLUMN price_unit VARCHAR(10) NOT NULL DEFAULT 'USD' a  CREATE TABLE IF NOT EXISTS bot_state ( name VARCHAR(50) PRIMARY KEY, value VARCHAR(255) NOT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) a CREATE TABLE IF NOT EXISTS app_event_deliveries ( event_key VARCHAR(191) PRIMARY KEY, event_type VARCHAR(30) NOT NULL, app_key VARCHAR(80) NOT NULL, event_time DATETIME NULL, delivered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_app_event_delivered_at (delivered_at) ) z{ DELETE FROM app_event_deliveries WHERE delivered_at < NOW() - INTERVAL 90 DAY aQ CREATE TABLE IF NOT EXISTS notification_subscribers ( chat_id BIGINT PRIMARY KEY, display_name VARCHAR(100) NOT NULL DEFAULT '', username VARCHAR(100), subscribed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) a CREATE TABLE IF NOT EXISTS notification_invites ( token_hash CHAR(64) PRIMARY KEY, created_by BIGINT NOT NULL, expires_at DATETIME NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_notification_invites_expires_at (expires_at) ) zW DELETE FROM notification_invites WHERE expires_at < NOW() zDB initialized) aiomysql create_pool DB_CONFIGracquirecursorexecute Exceptionstrlowerloggerinfo)conncur column_sqleerrrr,/var/www/back/telegram_server/persistence.pyinit_dbsJ       *(cr*cs(trttIdHdadSdS)N)rclose wait_closedrrrr)close_dbs r-returncs.tsdSzvt4IdH`}|4IdH@}|dt|j|jt|j|j|j |j t|j |j t|j|jt|j|j|j |j t|j |j fIdHWdIdHn 1IdHs]wYWdIdHWdS1IdHstwYWdSt y}ztd|WYd}~dSd}~ww)u#트레이딩 세션 저장 (upsert)Na INSERT INTO trading_session ( id, running, total_budget, compound, total_realized, total_trades, started_at, paper_mode, virtual_krw ) VALUES (1, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE running=%s, total_budget=%s, compound=%s, total_realized=%s, total_trades=%s, started_at=%s, paper_mode=%s, virtual_krw=%s zsave_session error: )rrrrintrunning total_budgetcompoundtotal_realized total_trades started_at paper_mode _virtual_krwrr"error)traderr$r%r'rrr) save_sessions*     *2r:poscs6tsdSzzt4IdHd}|4IdHD}|d|d|d|d|d|d|d|d|d |d|d|d|d|d|d fIdHWdIdHn 1IdHsawYWdIdHWdS1IdHsxwYWdSty}ztd |WYd}~dSd}~ww) Na{ INSERT INTO positions (ticker, coin, status, buy_price, buy_volume, bought_at, target_profit, stop_loss) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE status=%s, buy_price=%s, buy_volume=%s, bought_at=%s, target_profit=%s, stop_loss=%s tickercoinstatus buy_price buy_volume bought_at target_profit stop_losszsave_position error: )rrrrgetrr"r8)r;r$r%r'rrr) save_positions(  *2rEr<c stsdSzKt4IdH5}|4IdH}|d|fIdHWdIdHn 1IdHs2wYWdIdHWdS1IdHsIwYWdStyk}ztd|WYd}~dSd}~ww)Nz'DELETE FROM positions WHERE ticker = %szdelete_position error: rrrrrr"r8)r<r$r%r'rrr)delete_positions*2rGc stsdSzIt4IdH3}|4IdH}|dIdHWdIdHn 1IdHs0wYWdIdHWdS1IdHsGwYWdStyi}ztd|WYd}~dSd}~ww)NzDELETE FROM positionszclear_positions error: rFr$r%r'rrr)clear_positionss*2rIc stsdSz]t4IdHG}|tj4IdH%}|dIdH|IdHWdIdHWdIdHWS1IdHsDwYWdIdHWdS1IdHs[wYWdSty}}zt d|WYd}~dSd}~ww)Nz:SELECT * FROM trading_session WHERE id = 1 AND running = 1zload_session error: rrrr DictCursorrfetchonerr"r8rHrrr) load_sessions" 2rMc tsgSz]t4IdHG}|tj4IdH%}|dIdH|IdHWdIdHWdIdHWS1IdHsDwYWdIdHWdS1IdHs[wYWdSty~}zt d|gWYd}~Sd}~ww)NzSELECT * FROM positionszload_positions error: rrrrrKrfetchallrr"r8rHrrr)load_positionss" 2rQalertscs&tsdSzrt4IdH\}|4IdH<}|dIdH|D]$\}}|D]}|d|d||d|d|d|ddfIdHq*q$WdIdHn 1IdHsYwYWdIdHWdS1IdHspwYWdSty}ztd |WYd}~dSd}~ww) NzDELETE FROM alertszhINSERT INTO alerts (id, chat_id, symbol, target_price, direction, price_unit) VALUES (%s,%s,%s,%s,%s,%s)idsymbol target_price direction price_unitUSDzsave_alerts error: ) rrrritemsrDrr"r8)rRr$r%chat_id alert_listar'rrr) save_alertss6  *2r]c sPtsidfSzt4IdH:}|tj4IdH}|dIdH|IdH}WdIdHn 1IdHs;wYWdIdHn 1IdHsPwYi}d}|D])}|d}||g|d|d|d|d| d pyd d t ||d}q[||fWSt y}zt d |idfWYd}~Sd}~ww) z#Returns (alerts_dict, max_alert_id)rNzSELECT * FROM alertsrZrSrTrUrVrWrX)rSrTrUrVrWzload_alerts error: )rrrrrKrrP setdefaultappendrDmaxrr"r8)r$r%rowsrRmax_idrrZr'rrr) load_alertss6*(    rdnamevaluec stsdSzMt4IdH7}|4IdH}|d|||fIdHWdIdHn 1IdHs4wYWdIdHWdS1IdHsKwYWdStyp}ztd|d|WYd}~dSd}~ww)Nz INSERT INTO bot_state (name, value) VALUES (%s, %s) ON DUPLICATE KEY UPDATE value = %s zsave_bot_state error []: rF)rerfr$r%r'rrr)save_bot_states *2$rhc s>tsdSz{t4IdHe}|tj4IdHC}|d|fIdH|IdH}|rF|dWdIdHWdIdHWS WdIdHWdIdHWdS1IdHsbwYWdIdHWdS1IdHsywYWdSty}zt d|d|WYd}~dSd}~ww)Nz+SELECT value FROM bot_state WHERE name = %srfzload_bot_state error [rgrJ)rer$r%rowr'rrr)load_bot_state.s,2rj token_hash created_byc ststdt4IdH?}|4IdH }|d|fIdH|d||fIdHWdIdHn 1IdHs>wYWdIdHdS1IdHsTwYdS)NDB is not initializedzLDELETE FROM notification_invites WHERE created_by = %s OR expires_at < NOW()z INSERT INTO notification_invites (token_hash, created_by, expires_at) VALUES (%s, %s, DATE_ADD(NOW(), INTERVAL 15 MINUTE)) r RuntimeErrorrrr)rkrlr$r%rrr)create_notification_invite>s  *.rpc tsdSt4IdHC}|4IdH$}|d|fIdH|jdkWdIdHWdIdHS1IdHs@wYWdIdHdS1IdHsVwYdS)NFz DELETE FROM notification_invites WHERE token_hash = %s AND expires_at >= NOW() rrrrrrowcount)rkr$r%rrr)consume_notification_invitePs .rtrZ display_nameusernamec ststdt4IdH8}|4IdH}|d|||||fIdHWdIdHn 1IdHs7wYWdIdHdS1IdHsMwYdS)Nrmz INSERT INTO notification_subscribers (chat_id, display_name, username) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE display_name = %s, username = %s rn)rZrurvr$r%rrr)save_notification_subscriber_s  *.rwc rN)Nz SELECT chat_id, display_name, username, subscribed_at FROM notification_subscribers ORDER BY subscribed_at z%load_notification_subscribers error: rOrHrrr)load_notification_subscribersrs& 2 rxc rq)NFz7DELETE FROM notification_subscribers WHERE chat_id = %srrr)rZr$r%rrr)delete_notification_subscribers .ry event_keyc stsdSz_t4IdHI}|4IdH)}|d|fIdH|IdHduWdIdHWdIdHWS1IdHsFwYWdIdHWdS1IdHs]wYWdSty|}z td|d|d}~ww)NFz?SELECT 1 FROM app_event_deliveries WHERE event_key = %s LIMIT 1zhas_app_event_delivery error [rg)rrrrrLrr"r8)rzr$r%r'rrr)has_app_event_deliverys( 2r{eventc stsdSzrt4IdH\}|4IdH<}|dt|dp"dddt|dp.dddt|dp:ddd |d fIdHWdIdHn 1IdHsYwYWdIdHWdS1IdHspwYWdSty}z td |d|d}~ww) Nz INSERT IGNORE INTO app_event_deliveries (event_key, event_type, app_key, event_time) VALUES (%s, %s, %s, %s) rz event_typeunknownapp_keyP event_timez'mark_app_event_delivered error [%s]: %s) rrrrr rDrr"r8)r|r$r%r'rrr)mark_app_event_delivereds2 *2r)r.N)%osloggingr getLogger__name__r"environrDr/rrPool__annotations__r*r-r:dictrEr rGrIrMlistrQr]tuplerdrhrjrpboolrtrwrxryr{rrrrr)sP       m