crypto_monitor/strategy_db.py

"""策略交易相关表结构（各所 crypto.db 共用 schema）。"""

ROLL_GROUPS_SQL = """
CREATE TABLE IF NOT EXISTS roll_groups (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_monitor_id INTEGER,
    symbol TEXT NOT NULL,
    exchange_symbol TEXT,
    direction TEXT NOT NULL,
    initial_take_profit REAL,
    initial_stop_loss REAL,
    current_stop_loss REAL,
    risk_percent REAL DEFAULT 2,
    leg_count INTEGER DEFAULT 0,
    status TEXT DEFAULT 'active',
    created_at TEXT,
    updated_at TEXT
)
"""

ROLL_LEGS_SQL = """
CREATE TABLE IF NOT EXISTS roll_legs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    roll_group_id INTEGER NOT NULL,
    leg_index INTEGER NOT NULL,
    add_mode TEXT NOT NULL,
    fib_upper REAL,
    fib_lower REAL,
    limit_price REAL,
    fill_price REAL,
    amount REAL,
    new_stop_loss REAL,
    exchange_order_id TEXT,
    status TEXT DEFAULT 'filled',
    created_at TEXT,
    FOREIGN KEY (roll_group_id) REFERENCES roll_groups(id)
)
"""

TREND_PLANS_SQL = """
CREATE TABLE IF NOT EXISTS trend_pullback_plans (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    status TEXT DEFAULT 'active',
    symbol TEXT NOT NULL,
    exchange_symbol TEXT,
    direction TEXT NOT NULL DEFAULT 'long',
    leverage INTEGER NOT NULL,
    stop_loss REAL NOT NULL,
    add_upper REAL NOT NULL,
    take_profit REAL NOT NULL,
    risk_percent REAL DEFAULT 5,
    snapshot_available_usdt REAL,
    snapshot_at TEXT,
    plan_margin_capital REAL,
    target_order_amount REAL,
    first_order_amount REAL,
    remainder_total REAL,
    dca_legs INTEGER DEFAULT 5,
    per_leg_amount REAL,
    grid_prices_json TEXT,
    leg_amounts_json TEXT,
    legs_done INTEGER DEFAULT 0,
    first_order_done INTEGER DEFAULT 0,
    last_mark_price REAL,
    avg_entry_price REAL,
    order_amount_open REAL,
    opened_at TEXT,
    opened_at_ms INTEGER,
    session_date TEXT,
    message TEXT,
    initial_stop_loss REAL,
    breakeven_applied INTEGER DEFAULT 0,
    breakeven_applied_at TEXT
)
"""

TREND_PREVIEWS_SQL = """
CREATE TABLE IF NOT EXISTS trend_pullback_previews (
    id TEXT PRIMARY KEY,
    symbol TEXT NOT NULL,
    exchange_symbol TEXT NOT NULL,
    direction TEXT NOT NULL,
    leverage INTEGER NOT NULL,
    stop_loss REAL NOT NULL,
    add_upper REAL NOT NULL,
    take_profit REAL NOT NULL,
    risk_percent REAL NOT NULL,
    snapshot_available_usdt REAL NOT NULL,
    snapshot_at TEXT,
    live_price_ref REAL,
    plan_margin_capital REAL,
    target_order_amount REAL,
    first_order_amount REAL,
    remainder_total REAL,
    dca_legs INTEGER,
    per_leg_amount REAL,
    grid_prices_json TEXT,
    leg_amounts_json TEXT,
    expires_at_ms INTEGER NOT NULL,
    created_at TEXT
)
"""

TREND_PREVIEW_SNAPSHOTS_SQL = """
CREATE TABLE IF NOT EXISTS trend_pullback_preview_snapshots (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    preview_id TEXT NOT NULL UNIQUE,
    symbol TEXT NOT NULL,
    exchange_symbol TEXT NOT NULL,
    direction TEXT NOT NULL,
    leverage INTEGER NOT NULL,
    stop_loss REAL NOT NULL,
    add_upper REAL NOT NULL,
    take_profit REAL NOT NULL,
    risk_percent REAL NOT NULL,
    snapshot_available_usdt REAL NOT NULL,
    snapshot_at TEXT,
    live_price_ref REAL,
    plan_margin_capital REAL,
    target_order_amount REAL,
    first_order_amount REAL,
    remainder_total REAL,
    dca_legs INTEGER,
    per_leg_amount REAL,
    grid_prices_json TEXT,
    leg_amounts_json TEXT,
    expires_at_ms INTEGER NOT NULL,
    preview_created_at TEXT,
    outcome TEXT DEFAULT 'open',
    executed_plan_id INTEGER
)
"""


def init_strategy_tables(conn) -> None:
    from strategy_snapshot_lib import init_strategy_snapshot_table

    conn.execute(ROLL_GROUPS_SQL)
    conn.execute(ROLL_LEGS_SQL)
    conn.execute(TREND_PLANS_SQL)
    conn.execute(TREND_PREVIEWS_SQL)
    conn.execute(TREND_PREVIEW_SNAPSHOTS_SQL)
    init_strategy_snapshot_table(conn)
    for ddl in (
        "ALTER TABLE trend_pullback_plans ADD COLUMN leg_amounts_json TEXT",
        "ALTER TABLE trend_pullback_plans ADD COLUMN initial_stop_loss REAL",
        "ALTER TABLE trend_pullback_plans ADD COLUMN breakeven_applied INTEGER DEFAULT 0",
        "ALTER TABLE trend_pullback_plans ADD COLUMN breakeven_applied_at TEXT",
        "ALTER TABLE trend_pullback_preview_snapshots ADD COLUMN preview_created_at TEXT",
        "ALTER TABLE trend_pullback_preview_snapshots ADD COLUMN outcome TEXT DEFAULT 'open'",
        "ALTER TABLE trend_pullback_preview_snapshots ADD COLUMN executed_plan_id INTEGER",
        "ALTER TABLE trade_records ADD COLUMN trend_plan_id INTEGER",
        "ALTER TABLE order_monitors ADD COLUMN trend_plan_id INTEGER",
        "ALTER TABLE order_monitors ADD COLUMN monitor_type TEXT",
        "ALTER TABLE order_monitors ADD COLUMN key_signal_type TEXT",
        "ALTER TABLE trend_pullback_plans ADD COLUMN leg_fill_prices_json TEXT",
        "ALTER TABLE roll_legs ADD COLUMN stop_offset_pct REAL",
        "ALTER TABLE roll_legs ADD COLUMN breakthrough_price REAL",
        "ALTER TABLE roll_legs ADD COLUMN last_mark_price REAL",
    ):
        try:
            conn.execute(ddl)
        except Exception:
            pass