o
    miK                     @   sx   d Z ddlZddlZddlZddlZddlmZm	Z	m
Z
 ddlmZmZ ejejejedZG dd dZdS )u   
SQLite 数据库模块

存储:
1. K线数据（增量更新，避免重复拉取）
2. 信号历史（所有检测到的信号）
3. 系统状态（上次运行时间、连败计数等）
    N)OptionalListDict)datetimetimezonezlanglang.dbc                   @   s$  e Zd ZdZefdefddZdd Zdeded	ej	fd
dZ
		d/dededee dee dej	f
ddZdededee fddZdededefddZdedefddZ		d0dedee dej	fddZdefdd Zd!ed"efd#d$Zd1d!ed&edefd'd(Zd)d* Zd+d, Zd-d. ZdS )2Databaseu   
    SQLite数据库管理器

    用法:
        db = Database()
        db.save_klines('BTCUSDT', '1h', df)
        df = db.load_klines('BTCUSDT', '1h')
        db.save_signal(signal_dict)
    db_pathc                 C   s   || _ t|| _|   d S N)r   sqlite3connectconn_init_tables)selfr    r   !/opt/langlang_ai/data/database.py__init__"   s   zDatabase.__init__c                 C   sJ   | j  }|d |d |d |d |d | j   dS )u   创建数据表a  
            CREATE TABLE IF NOT EXISTS klines (
                symbol TEXT NOT NULL,
                interval TEXT NOT NULL,
                timestamp TEXT NOT NULL,
                open REAL NOT NULL,
                high REAL NOT NULL,
                low REAL NOT NULL,
                close REAL NOT NULL,
                volume REAL NOT NULL,
                PRIMARY KEY (symbol, interval, timestamp)
            )
        a  
            CREATE TABLE IF NOT EXISTS signals (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                created_at TEXT NOT NULL DEFAULT (datetime('now')),
                coin TEXT NOT NULL,
                direction TEXT NOT NULL,
                signal_type TEXT NOT NULL,
                entry_time TEXT NOT NULL,
                entry_price REAL NOT NULL,
                stop_loss REAL NOT NULL,
                sl_distance REAL NOT NULL,
                box_high REAL,
                box_low REAL,
                box_width_pct REAL,
                box_duration_bars INTEGER,
                volume_ratio REAL,
                entry_method TEXT,
                suggested_position_pct REAL,
                btc_trend_60d REAL,
                coin_trend_60d REAL,
                filter_passed INTEGER DEFAULT 1,
                filter_reason TEXT,
                notified INTEGER DEFAULT 0,
                executed INTEGER DEFAULT 0,
                notes TEXT
            )
        z
            CREATE TABLE IF NOT EXISTS system_state (
                key TEXT PRIMARY KEY,
                value TEXT NOT NULL,
                updated_at TEXT NOT NULL DEFAULT (datetime('now'))
            )
        zx
            CREATE INDEX IF NOT EXISTS idx_klines_symbol_ts
            ON klines(symbol, interval, timestamp)
        zo
            CREATE INDEX IF NOT EXISTS idx_signals_coin_time
            ON signals(coin, entry_time)
        N)r   cursorexecutecommit)r   r   r   r   r   r   '   s   




	
zDatabase._init_tablessymbolintervaldfc                 C   s   |j rdS g }| D ]+\}}t|d }||||t|d t|d t|d t|d t|d f q| jd| | j  dS )	u4   
        保存K线数据（增量upsert）
        N	timestampopenhighlowclosevolumez
            INSERT OR REPLACE INTO klines
            (symbol, interval, timestamp, open, high, low, close, volume)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        )emptyiterrowsstrappendfloatr   executemanyr   )r   r   r   r   data_rowtsr   r   r   save_klinesn   s   
zDatabase.save_klinesNstartlimitreturnc                 C   sn   d}||g}|r|d7 }| | |d7 }|r|d| 7 }tj|| j|d}|js5tj|d dd|d< |S )	u   
        加载K线数据

        Args:
            symbol: 交易对
            interval: K线周期
            start: 起始时间 (ISO format)
            limit: 最多返回行数
        zZSELECT timestamp, open, high, low, close, volume FROM klines WHERE symbol=? AND interval=?z AND timestamp >= ?z ORDER BY timestamp ASCz LIMIT paramsr   T)utc)r!   pdread_sql_queryr   r   to_datetime)r   r   r   r)   r*   queryr-   r   r   r   r   load_klines   s   
zDatabase.load_klinesc                 C   s"   | j d||f}| d }|S )u   获取最新K线时间戳z?SELECT MAX(timestamp) FROM klines WHERE symbol=? AND interval=?r   r   r   fetchone)r   r   r   r   resultr   r   r   get_latest_timestamp   s   zDatabase.get_latest_timestampc                 C   s   | j d||f}| d S )u   获取K线数量z9SELECT COUNT(*) FROM klines WHERE symbol=? AND interval=?r   r4   )r   r   r   r   r   r   r   get_kline_count   s
   zDatabase.get_kline_countsignalc                    sv   g d} fdd|D }d dgt| }d |} fdd|D }| jd| d| d	|}| j  |jS )
u?   
        保存信号记录
        Returns: signal id
        )coin	directionsignal_type
entry_timeentry_price	stop_losssl_distancebox_highbox_lowbox_width_pctbox_duration_barsvolume_ratioentry_methodsuggested_position_pctbtc_trend_60dcoin_trend_60dfilter_passedfilter_reasonnotesc                    s   g | ]}| v r|qS r   r   .0cr9   r   r   
<listcomp>   s    z(Database.save_signal.<locals>.<listcomp>,?c                    s   g | ]} | qS r   r   rM   rP   r   r   rQ      s    zINSERT INTO signals (z
) VALUES ())joinlenr   r   r   	lastrowid)r   r9   colspresent_colsplaceholders	col_namesvaluesr   r   rP   r   save_signal   s   

zDatabase.save_signal   hoursr:   c                 C   sB   d}d| dg}|r|d7 }| | |d7 }tj|| j|dS )u   获取最近N小时的信号z^
            SELECT * FROM signals
            WHERE created_at >= datetime('now', ?)
        -z hoursz AND coin = ?z ORDER BY created_at DESCr,   )r!   r/   r0   r   )r   r_   r:   r2   r-   r   r   r   get_recent_signals   s   
zDatabase.get_recent_signals	signal_idc                 C   s   | j d|f | j   dS )u   标记信号已推送z(UPDATE signals SET notified=1 WHERE id=?Nr   r   r   )r   rb   r   r   r   mark_signal_notified   s   zDatabase.mark_signal_notifiedkeyvaluec                 C   s    | j d||f | j   dS )u   设置系统状态z[INSERT OR REPLACE INTO system_state (key, value, updated_at) VALUES (?, ?, datetime('now'))Nrc   )r   re   rf   r   r   r   	set_state   s
   zDatabase.set_state defaultc                 C   s(   | j d|f}| }|r|d S |S )u   获取系统状态z*SELECT value FROM system_state WHERE key=?r   r4   )r   re   ri   r   r6   r   r   r   	get_state   s
   zDatabase.get_statec                 C   s   | j   dS )u   关闭数据库连接N)r   r   r   r   r   r   r      s   zDatabase.closec                 C   s   | S r	   r   rk   r   r   r   	__enter__   s   zDatabase.__enter__c                 G   s   |    d S r	   )r   )r   argsr   r   r   __exit__  s   zDatabase.__exit__)NN)r^   N)rh   )__name__
__module____qualname____doc__DEFAULT_DB_PATHr    r   r   r/   	DataFramer(   r   intr3   r7   r8   dictr]   ra   rd   rg   rj   r   rl   rn   r   r   r   r   r      s<    
G
	

	
r   )rr   r
   ospandasr/   numpynptypingr   r   r   r   r   pathrU   dirname__file__rs   r   r   r   r   r   <module>   s    	