CREATE TABLE IF NOT EXISTS live_trade_accounts (
    id BIGSERIAL PRIMARY KEY,
    account_code TEXT NOT NULL UNIQUE,
    exchange TEXT NOT NULL DEFAULT 'binance',
    market_type TEXT NOT NULL DEFAULT 'um_futures',
    testnet INTEGER NOT NULL DEFAULT 1,
    status TEXT NOT NULL DEFAULT 'disabled',
    api_key_env TEXT DEFAULT '',
    api_secret_env TEXT DEFAULT '',
    permissions_json TEXT DEFAULT '{}',
    risk_config_json TEXT DEFAULT '{}',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    last_checked_at TEXT DEFAULT ''
);

CREATE INDEX IF NOT EXISTS idx_live_trade_accounts_status ON live_trade_accounts(status, updated_at DESC);

CREATE TABLE IF NOT EXISTS live_order_intents (
    id BIGSERIAL PRIMARY KEY,
    source_type TEXT NOT NULL DEFAULT 'manual',
    source_id BIGINT DEFAULT 0,
    recommendation_id BIGINT DEFAULT 0,
    paper_trade_id BIGINT DEFAULT 0,
    paper_order_id BIGINT DEFAULT 0,
    account_id BIGINT DEFAULT 0,
    exchange TEXT NOT NULL DEFAULT 'binance',
    market_type TEXT NOT NULL DEFAULT 'um_futures',
    symbol TEXT NOT NULL,
    side TEXT NOT NULL DEFAULT 'long',
    position_side TEXT NOT NULL DEFAULT 'long',
    order_type TEXT NOT NULL DEFAULT 'market',
    status TEXT NOT NULL DEFAULT 'blocked',
    reason TEXT DEFAULT '',
    quantity DOUBLE PRECISION DEFAULT 0,
    price DOUBLE PRECISION DEFAULT 0,
    stop_loss DOUBLE PRECISION DEFAULT 0,
    take_profit DOUBLE PRECISION DEFAULT 0,
    notional_usdt DOUBLE PRECISION DEFAULT 0,
    leverage DOUBLE PRECISION DEFAULT 1,
    reduce_only INTEGER NOT NULL DEFAULT 0,
    client_order_id TEXT DEFAULT '',
    exchange_order_id TEXT DEFAULT '',
    risk_check_json TEXT DEFAULT '{}',
    request_json TEXT DEFAULT '{}',
    response_json TEXT DEFAULT '{}',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    approved_at TEXT DEFAULT '',
    submitted_at TEXT DEFAULT '',
    finished_at TEXT DEFAULT ''
);

CREATE INDEX IF NOT EXISTS idx_live_order_intents_status_updated ON live_order_intents(status, updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_live_order_intents_symbol_status ON live_order_intents(symbol, status);
CREATE INDEX IF NOT EXISTS idx_live_order_intents_source ON live_order_intents(source_type, source_id);
CREATE INDEX IF NOT EXISTS idx_live_order_intents_recommendation ON live_order_intents(recommendation_id);
CREATE INDEX IF NOT EXISTS idx_live_order_intents_paper_trade ON live_order_intents(paper_trade_id);

CREATE TABLE IF NOT EXISTS live_order_events (
    id BIGSERIAL PRIMARY KEY,
    intent_id BIGINT NOT NULL,
    event_type TEXT NOT NULL,
    status TEXT DEFAULT '',
    message TEXT DEFAULT '',
    payload_json TEXT DEFAULT '{}',
    event_time TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_live_order_events_intent_time ON live_order_events(intent_id, event_time DESC);
CREATE INDEX IF NOT EXISTS idx_live_order_events_type_time ON live_order_events(event_type, event_time DESC);