"""tradesys P0 指标计算引擎"""
import duckdb
from dataclasses import dataclass
from datetime import date

@dataclass
class P0Report:
    date: date
    portfolio_value: float
    daily_return: float
    drawdown_pct: float          # 负数
    drawdown_days: int
    drift: dict                  # {"DBMF": 0.02, ...}
    susde_peg_deviation: float
    alert_level: int             # 1-4
    alerts: list

TARGET_WEIGHTS = {"DBMF": 0.25, "GLD": 0.25, "sUSDe": 0.25, "BIL": 0.25}
DRIFT_THRESHOLD = 0.05
DD_DURATION_THRESHOLD = 180

def calc_p0(db_path: str = "tradesys.db") -> P0Report:
    con = duckdb.connect(db_path, read_only=True)
    
    # 1. 当前组合净值
    row = con.execute("""
        SELECT trade_date, total_value FROM portfolio_daily
        ORDER BY trade_date DESC LIMIT 1
    """).fetchone()
    today, portfolio_value = row
    
    # 2. 日收益率
    prev = con.execute("""
        SELECT total_value FROM portfolio_daily
        ORDER BY trade_date DESC LIMIT 1 OFFSET 1
    """).fetchone()
    daily_return = (portfolio_value / prev[0] - 1) if prev else 0.0
    
    # 3. 回撤
    peak = con.execute("SELECT MAX(total_value) FROM portfolio_daily").fetchone()[0]
    drawdown_pct = (portfolio_value / peak - 1)
    
    # 4. 回撤持续天数
    peak_date = con.execute("""
        SELECT trade_date FROM portfolio_daily
        WHERE total_value = (SELECT MAX(total_value) FROM portfolio_daily)
        ORDER BY trade_date DESC LIMIT 1
    """).fetchone()[0]
    drawdown_days = (today - peak_date).days if isinstance(peak_date, date) else 0
    
    # 5. 配比偏离度
    holdings = con.execute("SELECT asset, market_value FROM holdings_latest").fetchall()
    total = sum(h[1] for h in holdings)
    drift = {asset: mv/total - TARGET_WEIGHTS.get(asset, 0.25) for asset, mv in holdings}
    
    # 6. 告警判定
    dd_abs = abs(drawdown_pct)
    if dd_abs >= 0.15: alert_level = 4
    elif dd_abs >= 0.10: alert_level = 3
    elif dd_abs >= 0.05: alert_level = 2
    else: alert_level = 1
    
    alerts = []
    if alert_level >= 2:
        alerts.append(f"⚠️ 回撤 {drawdown_pct:.1%} 达到 Level {alert_level}")
    if drawdown_days > DD_DURATION_THRESHOLD:
        alerts.append(f"⚠️ 回撤已持续 {drawdown_days} 天")
    for asset, d in drift.items():
        if abs(d) > DRIFT_THRESHOLD:
            alerts.append(f"📊 {asset} 配比偏离 {d:+.1%}")
    
    con.close()
    return P0Report(today, portfolio_value, daily_return, drawdown_pct,
                    drawdown_days, drift, 0.0, alert_level, alerts)

ALERT_CONFIG = {
    1: {  # 观察 (DD < 5%)
        "channels": ["log"],
        "frequency": "daily",
        "action": "LOG_ONLY",
    },
    2: {  # 警戒 (DD 5-10%)
        "channels": ["log", "terminal_notification", "openclaw"],
        "frequency": "daily",
        "action": "DIAGNOSE",
        "cooldown_hours": 24,
    },
    3: {  # 干预 (DD 10-15%)
        "channels": ["log", "terminal_notification", "openclaw", "telegram"],
        "frequency": "4h",
        "action": "PAUSE_REBALANCE",
        "cooldown_hours": 4,
    },
    4: {  # 熔断 (DD > 15%)
        "channels": ["log", "terminal_notification", "openclaw", "telegram"],
        "frequency": "1h",
        "action": "CIRCUIT_BREAK",
        "cooldown_hours": 1,
        "require_ack": True,
    },
}

def should_alert(current_level: int, last_level: int, last_alert_time: float) -> bool:
    """只在级别变化或超过冷却期时告警"""
    import time
    if current_level != last_level:
        return True
    if current_level >= 3:
        cooldown = ALERT_CONFIG[current_level]["cooldown_hours"] * 3600
        return (time.time() - last_alert_time) > cooldown
    return False

import subprocess

def macos_notify(title: str, message: str, sound: str = "default"):
    """macOS 原生通知"""
    script = f'display notification "{message}" with title "{title}" sound name "{sound}"'
    subprocess.run(["osascript", "-e", script], check=True)

# 使用
macos_notify("tradeSys ⚠️ Level 2", "回撤 -6.2%, 持续 15 天")

## tradeSys 组合监控
- 运行 `python3 ~/tradesys/tradesys_check.py`
- 如果输出包含 "Level 2" 或以上，将结果发送给老板
- 如果输出 "Level 1"，忽略

"""tradesys 数据获取器"""
import pandas as pd
from datetime import date, timedelta

# Stooq 日频价格
def fetch_stooq(symbol: str, start: date, end: date) -> pd.DataFrame:
    url = f"https://stooq.com/q/d/l/?s={symbol}.us&d1={start:%Y%m%d}&d2={end:%Y%m%d}&i=d"
    df = pd.read_csv(url)
    df["Date"] = pd.to_datetime(df["Date"])
    df = df.rename(columns={"Close": "close", "Date": "trade_date"})
    df["asset"] = symbol.upper()
    return df[["trade_date", "asset", "close"]]

# Binance Funding Rate
def fetch_funding_rate(symbol: str = "BTCUSDT", limit: int = 30):
    import ccxt
    exchange = ccxt.binance({"options": {"defaultType": "swap"}})
    rates = exchange.fetch_funding_rate_history(symbol, limit=limit)
    return pd.DataFrame([{
        "timestamp": r["timestamp"],
        "symbol": symbol,
        "funding_rate": r["fundingRate"],
    } for r in rates])

# Ethena sUSDe 数据
def fetch_ethena_stats():
    import requests
    resp = requests.get("https://app.ethena.fi/api/yields/protocol-and-staking-yield")
    data = resp.json()
    return {
        "susde_apy": data.get("stakingYield", {}).get("value", 0),
        "tvl": data.get("tvl", 0),
    }

-- ═══════════════════════════════════════════════════
-- tradeSys DuckDB Schema
-- 文件: tradesys.db (单文件，本地存储)
-- ═══════════════════════════════════════════════════

-- 1. 资产日频价格
CREATE TABLE IF NOT EXISTS asset_prices (
    trade_date  DATE NOT NULL,
    asset       VARCHAR NOT NULL,
    open_price  DOUBLE,
    high_price  DOUBLE,
    low_price   DOUBLE,
    close_price DOUBLE NOT NULL,
    volume      BIGINT,
    source      VARCHAR DEFAULT 'stooq',
    fetched_at  TIMESTAMP DEFAULT current_timestamp,
    PRIMARY KEY (trade_date, asset)
);

-- 2. 组合每日快照
CREATE TABLE IF NOT EXISTS portfolio_daily (
    trade_date     DATE PRIMARY KEY,
    total_value    DOUBLE NOT NULL,
    daily_return   DOUBLE,
    cumulative_return DOUBLE,
    peak_value     DOUBLE,
    drawdown_pct   DOUBLE,
    drawdown_days  INTEGER,
    alert_level    INTEGER DEFAULT 1,
    created_at     TIMESTAMP DEFAULT current_timestamp
);

-- 3. 持仓明细
CREATE TABLE IF NOT EXISTS holdings (
    trade_date     DATE NOT NULL,
    asset          VARCHAR NOT NULL,
    quantity       DOUBLE NOT NULL,
    avg_cost       DOUBLE NOT NULL,
    market_price   DOUBLE NOT NULL,
    market_value   DOUBLE NOT NULL,
    weight         DOUBLE NOT NULL,
    target_weight  DOUBLE DEFAULT 0.25,
    drift          DOUBLE,
    unrealized_pnl DOUBLE,
    PRIMARY KEY (trade_date, asset)
);

-- 4. 策略健康度指标（P1 周度）
CREATE TABLE IF NOT EXISTS strategy_health (
    check_date         DATE PRIMARY KEY,
    rolling_sharpe_6m  DOUBLE,
    dbmf_cta_beta      DOUBLE,
    dbmf_cta_r_squared DOUBLE,
    max_single_asset_dd_contrib DOUBLE,
    correlation_dbmf_gld DOUBLE,
    vix_current        DOUBLE,
    created_at         TIMESTAMP DEFAULT current_timestamp
);

-- 5. Crypto Funding & Ethena 监控
CREATE TABLE IF NOT EXISTS crypto_monitor (
    check_time         TIMESTAMP PRIMARY KEY,
    btc_funding_rate   DOUBLE,
    eth_funding_rate   DOUBLE,
    btc_funding_7d_avg DOUBLE,
    ethena_tvl         DOUBLE,
    ethena_oi_pct      DOUBLE,
    susde_apy          DOUBLE,
    susde_price        DOUBLE,
    susde_peg_ok       BOOLEAN DEFAULT true,
    created_at         TIMESTAMP DEFAULT current_timestamp
);

-- 6. 交易记录 (执行成本追踪)
CREATE TABLE IF NOT EXISTS trades (
    trade_id       VARCHAR PRIMARY KEY,
    trade_date     DATE NOT NULL,
    asset          VARCHAR NOT NULL,
    side           VARCHAR NOT NULL,
    quantity       DOUBLE NOT NULL,
    price          DOUBLE NOT NULL,
    commission     DOUBLE DEFAULT 0,
    spread_cost    DOUBLE DEFAULT 0,
    gas_fee        DOUBLE DEFAULT 0,
    total_cost     DOUBLE,
    order_type     VARCHAR,
    execution_venue VARCHAR,
    notes          VARCHAR,
    created_at     TIMESTAMP DEFAULT current_timestamp
);

-- 7. 告警日志
CREATE TABLE IF NOT EXISTS alert_log (
    alert_id       INTEGER PRIMARY KEY,
    alert_time     TIMESTAMP NOT NULL,
    alert_level    INTEGER NOT NULL,
    prev_level     INTEGER,
    channel        VARCHAR NOT NULL,
    message        VARCHAR NOT NULL,
    acknowledged   BOOLEAN DEFAULT false,
    created_at     TIMESTAMP DEFAULT current_timestamp
);

-- 8. 再平衡记录
CREATE TABLE IF NOT EXISTS rebalance_log (
    rebalance_id   INTEGER PRIMARY KEY,
    rebalance_date DATE NOT NULL,
    trigger_type   VARCHAR NOT NULL,
    pre_weights    VARCHAR,
    post_weights   VARCHAR,
    total_cost     DOUBLE,
    notes          VARCHAR,
    created_at     TIMESTAMP DEFAULT current_timestamp
);

-- 视图：最新持仓
CREATE OR REPLACE VIEW holdings_latest AS
SELECT h.* FROM holdings h
INNER JOIN (SELECT MAX(trade_date) as max_date FROM holdings) m
ON h.trade_date = m.max_date;

-- 索引优化
CREATE INDEX IF NOT EXISTS idx_prices_asset_date ON asset_prices(asset, trade_date);
CREATE INDEX IF NOT EXISTS idx_portfolio_date ON portfolio_daily(trade_date);

"""一次性历史回填脚本"""
def backfill_etf_prices(db_path: str = "tradesys.db"):
    import duckdb
    con = duckdb.connect(db_path)
    for symbol in ["dbmf", "gld", "bil"]:
        df = fetch_stooq(symbol, date(2019, 6, 3), date.today())
        con.execute("""
            INSERT OR REPLACE INTO asset_prices
            (trade_date, asset, close_price, volume, source)
            SELECT trade_date, asset, close, volume, 'stooq' FROM df
        """)
        print(f"Backfilled {symbol}: {len(df)} rows")
    con.close()

portfolio:
  initial_capital: 50000
  target_weights:
    DBMF: 0.25
    GLD: 0.25
    sUSDe: 0.25
    BIL: 0.25
  rebalance_drift_threshold: 0.05

drawdown:
  level_2_threshold: 0.05
  level_3_threshold: 0.10
  level_4_threshold: 0.15
  duration_threshold_days: 180

strategy_health:
  rolling_sharpe_window: 126
  factor_beta_window: 60
  sharpe_yellow_threshold: -0.5
  beta_red_threshold: 0.5

alerts:
  telegram:
    enabled: false
    bot_token: ""
    chat_id: ""
  openclaw:
    enabled: true
  terminal_notification:
    enabled: true

api_keys:
  fred: ""
  binance_key: ""
  binance_secret: ""

data:
  db_path: "./tradesys.db"
  log_dir: "./logs"

# ═══════════════════════════════════════════════════
# tradeSys 冷启动步骤清单
# 预计耗时：30 分钟
# ═══════════════════════════════════════════════════

# Step 1: 创建项目目录 (1 分钟)
mkdir -p ~/tradesys/{data,engine,logs}
cd ~/tradesys

# Step 2: Python 环境 (3 分钟)
python3 -m venv .venv
source .venv/bin/activate
pip install duckdb pandas streamlit ccxt yfinance fredapi requests plotly pyyaml

# Step 3: 初始化数据库 (1 分钟)
python3 -c "
import duckdb
con = duckdb.connect('tradesys.db')
con.execute(open('data/schema.sql').read())
con.close()
print('✅ Database initialized')
"

# Step 4: 历史数据回填 (5 分钟)
python3 data/backfill.py

# Step 5: 配置 API Keys (5 分钟)
cp config.example.yaml config.yaml
# 编辑 config.yaml

# Step 6: 验证数据 (2 分钟)
python3 -c "
import duckdb
con = duckdb.connect('tradesys.db')
for table in ['asset_prices', 'portfolio_daily']:
    count = con.execute(f'SELECT COUNT(*) FROM {table}').fetchone()[0]
    print(f'{table}: {count} rows')
con.close()
"

# Step 7: 首次运行 P0 检查 (1 分钟)
python3 tradesys_check.py

# Step 8: 配置 cron (2 分钟)
crontab -e
# 粘贴 §4.3 的 cron 配置

# Step 9: 启动 Dashboard (即时)
streamlit run dashboard.py
# 浏览器自动打开 http://localhost:8501

# Step 10: 手机访问 (可选)
# 同一 WiFi 下，手机浏览器访问 http://<Mac-IP>:8501

## tradeSys 监控
- [ ] 运行 `cd ~/tradesys && .venv/bin/python tradesys_check.py --json`
- [ ] 如果返回的 alert_level >= 2，告诉老板
- [ ] 如果返回的 alert_level == 1，忽略

30 6 * * 2-6 cd ~/tradesys && \
  RESULT=$(.venv/bin/python tradesys_check.py --json) && \
  LEVEL=$(echo $RESULT | python3 -c "import sys,json; print(json.load(sys.stdin)['alert_level'])") && \
  [ "$LEVEL" -ge 2 ] && \
  openclaw message send --channel webchat --message "tradeSys Alert: $RESULT"

"""利用 QuantStats 生成月度报告"""
import quantstats as qs
import duckdb
import pandas as pd

def generate_monthly_report(db_path: str = "tradesys.db", output: str = "report.html"):
    con = duckdb.connect(db_path, read_only=True)
    df = con.execute("""
        SELECT trade_date, daily_return FROM portfolio_daily ORDER BY trade_date
    """).fetchdf()
    con.close()

    returns = df.set_index("trade_date")["daily_return"]
    returns.index = pd.to_datetime(returns.index)

    # 生成完整 HTML 报告（含 Sharpe、DD、月度热力图等 30+ 指标）
    qs.reports.html(returns, benchmark="SPY", output=output,
                    title="tradeSys Plan E3-AW Monthly Report")
    print(f"✅ Report saved to {output}")