fithia2/apps/orb_trader/models.py

"""SQLite schema and dataclasses for ORB paper trading state."""
from __future__ import annotations

from dataclasses import dataclass, field

# ── SQL DDL ───────────────────────────────────────────────────────────────────

CREATE_TABLES_SQL = """
CREATE TABLE IF NOT EXISTS sessions (
    session_id      TEXT PRIMARY KEY,
    session_name    TEXT NOT NULL UNIQUE,
    config_path     TEXT NOT NULL,
    initial_equity  REAL NOT NULL,
    created_at      TEXT NOT NULL,
    status          TEXT NOT NULL DEFAULT 'active'
);

CREATE TABLE IF NOT EXISTS orb_positions (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id      TEXT NOT NULL REFERENCES sessions(session_id),
    date            TEXT NOT NULL,
    ticker          TEXT NOT NULL,
    direction       TEXT NOT NULL,
    entry_price     REAL NOT NULL,
    entry_time      TEXT NOT NULL,
    shares          INTEGER NOT NULL,
    orb_high        REAL NOT NULL,
    orb_low         REAL NOT NULL,
    atr_at_entry    REAL NOT NULL,
    stop_distance   REAL NOT NULL,
    current_stop    REAL NOT NULL,
    peak_price      REAL NOT NULL,
    trailing_active INTEGER NOT NULL DEFAULT 0,
    trigger_type    TEXT NOT NULL DEFAULT 'orb',
    rvol            REAL,
    composite_score REAL,
    order_id        TEXT,
    status          TEXT NOT NULL DEFAULT 'open',
    UNIQUE(session_id, date, ticker)
);

CREATE TABLE IF NOT EXISTS trades (
    trade_id        TEXT PRIMARY KEY,
    session_id      TEXT NOT NULL,
    date            TEXT NOT NULL,
    ticker          TEXT NOT NULL,
    direction       TEXT NOT NULL,
    entry_price     REAL NOT NULL,
    exit_price      REAL NOT NULL,
    entry_time      TEXT NOT NULL,
    exit_time       TEXT NOT NULL,
    shares          INTEGER NOT NULL,
    pnl             REAL NOT NULL,
    r_multiple      REAL NOT NULL,
    exit_reason     TEXT NOT NULL,
    trigger_type    TEXT NOT NULL DEFAULT 'orb',
    atr_at_entry    REAL,
    rvol            REAL,
    composite_score REAL
);

CREATE TABLE IF NOT EXISTS daily_snapshots (
    session_id      TEXT NOT NULL,
    date            TEXT NOT NULL,
    equity          REAL NOT NULL,
    daily_pnl       REAL,
    total_pnl       REAL,
    trades_taken    INTEGER,
    stops_hit       INTEGER,
    drawdown_pct    REAL,
    PRIMARY KEY (session_id, date)
);

CREATE TABLE IF NOT EXISTS daily_state (
    session_id      TEXT NOT NULL,
    date            TEXT NOT NULL,
    cumulative_loss REAL NOT NULL DEFAULT 0.0,
    stops_hit       INTEGER NOT NULL DEFAULT 0,
    kill_switch     INTEGER NOT NULL DEFAULT 0,
    phase           TEXT NOT NULL DEFAULT 'idle',
    PRIMARY KEY (session_id, date)
);

CREATE TABLE IF NOT EXISTS orb_candidates (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id      TEXT NOT NULL,
    date            TEXT NOT NULL,
    ticker          TEXT NOT NULL,
    direction       TEXT NOT NULL,
    orb_high        REAL NOT NULL,
    orb_low         REAL NOT NULL,
    breakout_level  REAL NOT NULL,
    atr             REAL NOT NULL,
    rvol            REAL NOT NULL,
    gap_pct         REAL NOT NULL,
    composite_score REAL NOT NULL,
    size_scale      REAL NOT NULL DEFAULT 1.0,
    metadata_json   TEXT,
    status          TEXT NOT NULL DEFAULT 'pending',
    UNIQUE(session_id, date, ticker)
);
"""


# ── Dataclasses ───────────────────────────────────────────────────────────────

@dataclass
class ORBSessionRow:
    session_id: str
    session_name: str
    config_path: str
    initial_equity: float
    created_at: str
    status: str = "active"


@dataclass
class ORBPositionRow:
    session_id: str
    date: str
    ticker: str
    direction: str          # 'long' or 'short'
    entry_price: float
    entry_time: str
    shares: int
    orb_high: float
    orb_low: float
    atr_at_entry: float
    stop_distance: float
    current_stop: float
    peak_price: float
    trailing_active: bool = False
    trigger_type: str = "orb"
    rvol: float | None = None
    composite_score: float | None = None
    order_id: str | None = None
    status: str = "open"
    id: int | None = None   # autoincrement, set after insert


@dataclass
class ORBTradeRow:
    trade_id: str
    session_id: str
    date: str
    ticker: str
    direction: str
    entry_price: float
    exit_price: float
    entry_time: str
    exit_time: str
    shares: int
    pnl: float
    r_multiple: float
    exit_reason: str        # 'stop_loss', 'trailing_stop', 'close', 'kill_switch'
    trigger_type: str = "orb"
    atr_at_entry: float | None = None
    rvol: float | None = None
    composite_score: float | None = None


@dataclass
class ORBDailySnapshotRow:
    session_id: str
    date: str
    equity: float
    daily_pnl: float = 0.0
    total_pnl: float = 0.0
    trades_taken: int = 0
    stops_hit: int = 0
    drawdown_pct: float = 0.0


@dataclass
class ORBDailyStateRow:
    session_id: str
    date: str
    cumulative_loss: float = 0.0
    stops_hit: int = 0
    kill_switch: bool = False
    phase: str = "idle"     # idle, pre_market, orb_detection, breakout, management, eod, done


@dataclass
class ORBCandidateRow:
    session_id: str
    date: str
    ticker: str
    direction: str
    orb_high: float
    orb_low: float
    breakout_level: float
    atr: float
    rvol: float
    gap_pct: float
    composite_score: float
    size_scale: float = 1.0
    metadata_json: str | None = None
    status: str = "pending"  # pending, filled, timeout, cancelled
    id: int | None = None