ALTER TABLE recommendation
    ADD COLUMN IF NOT EXISTS strategy_code TEXT DEFAULT '',
    ADD COLUMN IF NOT EXISTS strategy_signal_id BIGINT DEFAULT 0,
    ADD COLUMN IF NOT EXISTS strategy_snapshot_json TEXT DEFAULT '{}',
    ADD COLUMN IF NOT EXISTS factor_roles_json TEXT DEFAULT '{}';

ALTER TABLE paper_trades
    ADD COLUMN IF NOT EXISTS strategy_code TEXT DEFAULT '',
    ADD COLUMN IF NOT EXISTS strategy_signal_id BIGINT DEFAULT 0,
    ADD COLUMN IF NOT EXISTS strategy_snapshot_json TEXT DEFAULT '{}',
    ADD COLUMN IF NOT EXISTS factor_roles_json TEXT DEFAULT '{}';

ALTER TABLE paper_orders
    ADD COLUMN IF NOT EXISTS strategy_code TEXT DEFAULT '',
    ADD COLUMN IF NOT EXISTS strategy_signal_id BIGINT DEFAULT 0,
    ADD COLUMN IF NOT EXISTS strategy_snapshot_json TEXT DEFAULT '{}',
    ADD COLUMN IF NOT EXISTS factor_roles_json TEXT DEFAULT '{}';

ALTER TABLE paper_trade_events
    ADD COLUMN IF NOT EXISTS strategy_code TEXT DEFAULT '',
    ADD COLUMN IF NOT EXISTS strategy_signal_id BIGINT DEFAULT 0;

CREATE TABLE IF NOT EXISTS strategy_catalog (
    strategy_code TEXT PRIMARY KEY,
    strategy_name TEXT NOT NULL,
    strategy_version TEXT DEFAULT '',
    status TEXT DEFAULT 'active',
    mode TEXT DEFAULT 'paper_only',
    description TEXT DEFAULT '',
    config_json TEXT DEFAULT '{}',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS strategy_signals (
    id BIGSERIAL PRIMARY KEY,
    run_id TEXT DEFAULT '',
    strategy_code TEXT NOT NULL,
    strategy_version TEXT DEFAULT '',
    symbol TEXT NOT NULL,
    direction TEXT DEFAULT 'long',
    signal_status TEXT DEFAULT 'candidate',
    confidence DOUBLE PRECISION DEFAULT 0,
    score DOUBLE PRECISION DEFAULT 0,
    market_regime TEXT DEFAULT '',
    trigger_json TEXT DEFAULT '{}',
    factor_roles_json TEXT DEFAULT '{}',
    entry_plan_json TEXT DEFAULT '{}',
    risk_plan_json TEXT DEFAULT '{}',
    decision_log_json TEXT DEFAULT '{}',
    created_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_strategy_signals_code_time
    ON strategy_signals(strategy_code, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_strategy_signals_symbol_time
    ON strategy_signals(symbol, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_rec_strategy_code_time
    ON recommendation(strategy_code, rec_time DESC);
CREATE INDEX IF NOT EXISTS idx_paper_trades_strategy_code
    ON paper_trades(strategy_code, opened_at DESC);
CREATE INDEX IF NOT EXISTS idx_paper_orders_strategy_code
    ON paper_orders(strategy_code, created_at DESC);

INSERT INTO strategy_catalog (
    strategy_code, strategy_name, strategy_version, status, mode, description, config_json, created_at, updated_at
) VALUES
    ('main_composite_v1', '综合确认策略', '', 'active', 'paper_enabled', '迁移期兼容综合策略：统一承载旧的综合筛选与确认逻辑，与其他策略平等运行。', '{}', NOW()::TEXT, NOW()::TEXT),
    ('box_retest_4h_v1', '4H箱体突破回踩', '', 'active', 'paper_only', '底部箱体突破后回踩箱体上沿或EMA承接的结构策略雏形。', '{}', NOW()::TEXT, NOW()::TEXT)
ON CONFLICT(strategy_code) DO UPDATE SET
    strategy_name=EXCLUDED.strategy_name,
    status=EXCLUDED.status,
    mode=EXCLUDED.mode,
    description=EXCLUDED.description,
    updated_at=NOW()::TEXT;

UPDATE recommendation
SET strategy_code='main_composite_v1'
WHERE COALESCE(strategy_code, '') = '';

UPDATE paper_trades pt
SET strategy_code=COALESCE(NULLIF(r.strategy_code, ''), 'main_composite_v1'),
    strategy_signal_id=COALESCE(NULLIF(pt.strategy_signal_id, 0), r.strategy_signal_id, 0),
    strategy_snapshot_json=CASE
        WHEN COALESCE(pt.strategy_snapshot_json, '{}') != '{}' THEN pt.strategy_snapshot_json
        ELSE COALESCE(NULLIF(r.strategy_snapshot_json, ''), '{}')
    END,
    factor_roles_json=CASE
        WHEN COALESCE(pt.factor_roles_json, '{}') != '{}' THEN pt.factor_roles_json
        ELSE COALESCE(NULLIF(r.factor_roles_json, ''), '{}')
    END
FROM recommendation r
WHERE pt.recommendation_id = r.id
  AND COALESCE(pt.strategy_code, '') = '';

UPDATE paper_trades
SET strategy_code='main_composite_v1'
WHERE COALESCE(strategy_code, '') = '';

UPDATE paper_orders po
SET strategy_code=COALESCE(NULLIF(r.strategy_code, ''), 'main_composite_v1'),
    strategy_signal_id=COALESCE(NULLIF(po.strategy_signal_id, 0), r.strategy_signal_id, 0),
    strategy_snapshot_json=CASE
        WHEN COALESCE(po.strategy_snapshot_json, '{}') != '{}' THEN po.strategy_snapshot_json
        ELSE COALESCE(NULLIF(r.strategy_snapshot_json, ''), '{}')
    END,
    factor_roles_json=CASE
        WHEN COALESCE(po.factor_roles_json, '{}') != '{}' THEN po.factor_roles_json
        ELSE COALESCE(NULLIF(r.factor_roles_json, ''), '{}')
    END
FROM recommendation r
WHERE po.recommendation_id = r.id
  AND COALESCE(po.strategy_code, '') = '';

UPDATE paper_orders
SET strategy_code='main_composite_v1'
WHERE COALESCE(strategy_code, '') = '';

UPDATE paper_trade_events e
SET strategy_code=COALESCE(NULLIF(pt.strategy_code, ''), 'main_composite_v1'),
    strategy_signal_id=COALESCE(pt.strategy_signal_id, 0)
FROM paper_trades pt
WHERE e.trade_id = pt.id
  AND COALESCE(e.strategy_code, '') = '';

UPDATE paper_trade_events
SET strategy_code='main_composite_v1'
WHERE COALESCE(strategy_code, '') = '';