db.sql

CREATE DATABASE trading CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE trading;

CREATE TABLE IF NOT EXISTS trading_signals (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    symbol VARCHAR(20) NOT NULL,
    timeframe VARCHAR(10) NOT NULL,
    signal_type VARCHAR(20) NOT NULL,
    price DECIMAL(20, 8) NOT NULL,
    supertrend DECIMAL(20, 8) NOT NULL,
    stop_distance_pct DECIMAL(10, 4),
    risk_level VARCHAR(20),
    rsi DECIMAL(10, 4),
    atr DECIMAL(20, 8),
    oi_change_pct DECIMAL(10, 4),
    funding_rate DECIMAL(10, 6),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_symbol_time (symbol, created_at),
    INDEX idx_signal (signal_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS backtest_results (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    symbol VARCHAR(20) NOT NULL,
    timeframe VARCHAR(10) NOT NULL,
    strategy_name VARCHAR(50) NOT NULL,
    atr_period INT NOT NULL,
    multiplier DECIMAL(5, 2) NOT NULL,
    total_trades INT,
    win_count INT,
    loss_count INT,
    win_rate DECIMAL(10, 4),
    profit_loss_ratio DECIMAL(10, 4),
    expectancy DECIMAL(10, 4),
    total_return_pct DECIMAL(10, 4),
    annual_return_pct DECIMAL(10, 4),
    max_drawdown_pct DECIMAL(10, 4),
    sharpe_ratio DECIMAL(10, 4),
    sortino_ratio DECIMAL(10, 4),
    calmar_ratio DECIMAL(10, 4),
    avg_holding_days DECIMAL(10, 2),
    data_start_date DATE,
    data_end_date DATE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_symbol_strategy (symbol, strategy_name),
    INDEX idx_performance (sharpe_ratio, total_return_pct)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS market_risk_alerts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    symbol VARCHAR(20) NOT NULL,
    alert_type VARCHAR(50) NOT NULL,
    risk_level VARCHAR(20) NOT NULL,
    description TEXT,
    price DECIMAL(20, 8) NOT NULL,
    price_change_pct DECIMAL(10, 4),
    oi_change_pct DECIMAL(10, 4),
    funding_rate DECIMAL(10, 6),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_symbol_time (symbol, created_at),
    INDEX idx_risk (risk_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 币种信号阈值配置表（支持不同币种使用不同参数）
CREATE TABLE IF NOT EXISTS signal_thresholds (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    symbol VARCHAR(20) NOT NULL UNIQUE,
    strong_oi_change DECIMAL(10, 4) DEFAULT 10.0 COMMENT '强持仓变化阈值 %',
    medium_oi_change DECIMAL(10, 4) DEFAULT 3.0 COMMENT '中等变化阈值 %',
    funding_rate_threshold DECIMAL(10, 6) DEFAULT 0.005 COMMENT '资金费率阈值',
    volume_spike DECIMAL(10, 4) DEFAULT 1.3 COMMENT '成交量突增倍数',
    rsi_oversold DECIMAL(10, 4) DEFAULT 35.0 COMMENT 'RSI 超卖阈值',
    rsi_overbought DECIMAL(10, 4) DEFAULT 65.0 COMMENT 'RSI 超买阈值',
    price_change_threshold DECIMAL(10, 4) DEFAULT 0.5 COMMENT '价格变化阈值 %',
    is_active TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_symbol (symbol)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='持仓信号阈值配置';

-- 币种信号状态表（用于持久化上次状态，避免重复告警）
CREATE TABLE IF NOT EXISTS signal_state (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    symbol VARCHAR(20) NOT NULL UNIQUE,
    last_price DECIMAL(20, 8) COMMENT '上次价格',
    last_oi DECIMAL(30, 8) COMMENT '上次持仓量',
    last_volume DECIMAL(30, 8) COMMENT '上次成交量',
    last_signal_type VARCHAR(20) COMMENT 'DANGER/SAFE/BULLISH/TOPPING',
    last_signal_strength VARCHAR(20) COMMENT 'STRONG/MEDIUM/WEAK',
    last_rsi DECIMAL(10, 4) COMMENT '上次 RSI',
    last_funding_rate DECIMAL(10, 6) COMMENT '上次资金费率',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_symbol (symbol)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='持仓信号状态';

-- 插入默认阈值配置（基于回测优化）
-- BTCUSDT: SAFE 信号 71% 胜率，DANGER 信号 59% 胜率
-- ETHUSDT: SAFE 信号 80% 胜率，DANGER 信号效果差（建议只关注 SAFE）
INSERT INTO signal_thresholds (symbol, strong_oi_change, medium_oi_change, funding_rate_threshold, rsi_oversold, rsi_overbought, price_change_threshold) 
VALUES 
    ('BTCUSDT', 10.0, 3.0, 0.005, 35.0, 65.0, 0.5),
    ('ETHUSDT', 12.0, 4.0, 0.008, 32.0, 68.0, 0.5)
ON DUPLICATE KEY UPDATE updated_at = NOW();