DB Database Design for Trading

import sqlite3
import json
from datetime import datetime

class TradingDB:
    """Simple SQLite database for trading system."""
    
    def __init__(self, db_path='trading.db'):
        self.conn = sqlite3.connect(db_path)
        self.create_tables()
    
    def create_tables(self):
        self.conn.executescript("""
            CREATE TABLE IF NOT EXISTS trades (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                symbol TEXT NOT NULL,
                direction TEXT NOT NULL,
                entry_time TEXT NOT NULL,
                exit_time TEXT,
                entry_price REAL NOT NULL,
                exit_price REAL,
                stop_loss REAL,
                take_profit REAL,
                pnl_r REAL,
                cluster TEXT,
                l1_confidence REAL,
                l2_decision TEXT,
                status TEXT DEFAULT 'OPEN'
            );
            
            CREATE TABLE IF NOT EXISTS risk_state (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp TEXT NOT NULL,
                balance REAL,
                drawdown_pct REAL,
                dd_zone TEXT,
                risk_level REAL,
                open_positions INTEGER
            );
            
            CREATE INDEX IF NOT EXISTS idx_trades_symbol ON trades(symbol);
            CREATE INDEX IF NOT EXISTS idx_trades_entry ON trades(entry_time);
        """)
    
    def log_trade(self, trade: dict):
        self.conn.execute(
            "INSERT INTO trades (symbol, direction, entry_time, entry_price, "
            "stop_loss, cluster, l1_confidence, l2_decision) "
            "VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
            (trade['symbol'], trade['direction'], str(datetime.now()),
             trade['price'], trade['sl'], trade.get('cluster'),
             trade.get('confidence'), trade.get('l2_decision'))
        )
        self.conn.commit()
    
    def get_stats(self):
        cur = self.conn.execute(
            "SELECT COUNT(*), AVG(pnl_r), SUM(CASE WHEN pnl_r > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) "
            "FROM trades WHERE status = 'CLOSED'"
        )
        total, avg_r, win_rate = cur.fetchone()
        print(f"Trades: {total}, Avg R: {avg_r:.2f}, WR: {win_rate:.1f}%")