#!/usr/bin/env python3
"""
下载 510300.SSE 数据并转换到vn.py数据库
使用赵云将军的AKShare-vnpy适配器
"""

import sys
import os
import sqlite3
from datetime import datetime

# 添加赵云的数据脚本路径
sys.path.insert(0, '/Users/chufeng/.openclaw/workspace-zhaoyun/sanguo_quant_live/zhaoyun-data/scripts/common_tools')

from akshare_vnpy_adapter import AKShareDataAdapter

def create_vnpy_database(db_path: str):
    """创建vn.py数据库结构"""
    print(f"🔧 创建vn.py数据库: {db_path}")
    
    os.makedirs(os.path.dirname(db_path), exist_ok=True)
    
    # 如果数据库已存在，先删除
    if os.path.exists(db_path):
        os.remove(db_path)
        print("   删除旧数据库")
    
    # 创建新数据库
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # 创建vn.py标准表结构
    # dbbardata - K线数据表
    cursor.execute("""
    CREATE TABLE dbbardata (
        symbol TEXT NOT NULL,
        exchange TEXT,
        interval TEXT NOT NULL,
        datetime INTEGER NOT NULL,
        open REAL NOT NULL,
        high REAL NOT NULL,
        low REAL NOT NULL,
        close REAL NOT NULL,
        volume INTEGER NOT NULL,
        open_interest REAL,
        turnover REAL,
        PRIMARY KEY (symbol, interval, datetime)
    );
    """)
    
    # 创建索引
    cursor.execute("CREATE INDEX ix_dbbardata_symbol ON dbbardata(symbol);")
    cursor.execute("CREATE INDEX ix_dbbardata_symbol_interval ON dbbardata(symbol, interval);")
    cursor.execute("CREATE INDEX ix_dbbardata_datetime ON dbbardata(datetime);")
    
    # dbtickdata - Tick数据表（暂时不需要）
    cursor.execute("""
    CREATE TABLE dbtickdata (
        symbol TEXT NOT NULL,
        exchange TEXT,
        datetime INTEGER NOT NULL,
        last_price REAL NOT NULL,
        volume INTEGER NOT NULL,
        turnover REAL NOT NULL,
        open_interest REAL NOT NULL,
        bid_price_1 REAL NOT NULL,
        bid_volume_1 INTEGER NOT NULL,
        ask_price_1 REAL NOT NULL,
        ask_volume_1 INTEGER NOT NULL,
        PRIMARY KEY (symbol, datetime)
    );
    """)
    
    # sqlite_sequence表（sqlite自动维护）
    
    conn.commit()
    conn.close()
    
    print("✅ 数据库结构创建完成")
    return True

def download_510300_data():
    """下载510300.SSE数据"""
    print("🚀 开始下载 510300.SSE (沪深300ETF) 日线数据...")
    
    # 创建适配器
    adapter = AKShareDataAdapter()
    
    # 下载数据 - 最近10年
    end_date = datetime.now().strftime('%Y%m%d')
    start_date = '20160101'
    
    print(f"   时间范围: {start_date} - {end_date}")
    
    # 注意：AKShare中，需要YYYYMMDD格式
    print(f"   使用日期格式: {start_date} - {end_date} (YYYYMMDD)")
    
    # 注意：AKShare中，510300的代码是 510300，不需要后缀
    try:
        # 尝试获取指数数据
        # 510300是ETF，使用股票数据接口
        df = adapter.get_stock_daily(
            symbol='510300',
            start_date=start_date,
            end_date=end_date
        )
    except Exception as e:
        print(f"   使用股票接口失败: {e}，尝试指数接口")
        df = adapter.get_index_daily(
            index_symbol='510300',
            start_date=start_date.replace('%m%d', '').replace('%Y', '').replace('%d', ''),
            end_date=end_date.replace('%Y%m%d', '')
        )
    
    if df.empty:
        print("❌ 获取数据失败")
        return None
    
    print(f"✅ 获取数据成功: {len(df)} 行")
    print("\n数据预览:")
    print(df.head())
    print("\n数据统计:")
    print(df.describe())
    
    return df

def import_to_vnpy_database(df, symbol: str, db_path: str):
    """导入数据到vn.py数据库"""
    print(f"\n📥 导入数据到vn.py数据库...")
    print(f"   标的: {symbol}")
    print(f"   数据库: {db_path}")
    
    # 连接数据库
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # 统计导入行数
    imported = 0
    
    # 遍历DataFrame并插入
    for _, row in df.iterrows():
        # 转换日期为unix时间戳（vn.py使用整数时间戳）
        # AKShare返回的日期格式是字符串或datetime
        date_val = row['date']
        if hasattr(date_val, 'timestamp'):
            # datetime对象
            dt = date_val
        else:
            # 字符串，尝试解析
            from datetime import datetime
            try:
                dt = datetime.strptime(str(date_val), '%Y-%m-%d')
            except:
                dt = datetime.strptime(str(date_val), '%Y%m%d')
        
        timestamp = int(dt.timestamp())
        
        # 插入数据
        # vn.py标准dbbardata结构
        cursor.execute("""
        INSERT INTO dbbardata (
            symbol, exchange, interval, datetime,
            open, high, low, close, volume, turnover
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            symbol,
            'SSE',  # 交易所
            '1d',   # 日线
            timestamp,
            float(row['open']),
            float(row['high']),
            float(row['low']),
            float(row['close']),
            int(row.get('volume', 0)),
            float(row.get('amount', row.get('volume', 0)))
        ))
        
        imported += 1
    
    # 提交
    conn.commit()
    
    # 验证
    cursor.execute("SELECT COUNT(*) FROM dbbardata WHERE symbol = ?", (symbol,))
    count = cursor.fetchone()[0]
    
    conn.close()
    
    print(f"✅ 导入完成: {imported} 行")
    print(f"   验证: 数据库中共有 {count} 行数据")
    
    return True

def main():
    """主函数"""
    print("🚀 下载并导入 510300.SSE 数据到vn.py数据库")
    print("="*60)
    
    # 配置
    symbol = "510300.SSE"
    db_path = "/Users/chufeng/.openclaw/workspace-zhaoyun/zhaoyun-data/data/database_test.db"
    
    print(f"目标标的: {symbol}")
    print(f"目标数据库: {db_path}")
    
    # 1. 创建数据库
    if not create_vnpy_database(db_path):
        print("❌ 创建数据库失败")
        return False
    
    # 2. 下载数据
    df = download_510300_data()
    if df is None or df.empty:
        print("❌ 下载数据失败")
        return False
    
    # 3. 导入到数据库
    if not import_to_vnpy_database(df, symbol, db_path):
        print("❌ 导入数据失败")
        return False
    
    # 4. 验证结果
    print("\n" + "="*60)
    print("验证结果:")
    
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM dbbardata WHERE symbol = ?", (symbol,))
    count = cursor.fetchone()[0]
    
    cursor.execute("SELECT MIN(datetime), MAX(datetime) FROM dbbardata WHERE symbol = ?", (symbol,))
    min_ts, max_ts = cursor.fetchone()
    
    from datetime import datetime
    min_dt = datetime.fromtimestamp(min_ts).strftime('%Y-%m-%d') if min_ts else 'N/A'
    max_dt = datetime.fromtimestamp(max_ts).strftime('%Y-%m-%d') if max_ts else 'N/A'
    
    conn.close()
    
    print(f"✅ 标的 {symbol} 数据已成功导入")
    print(f"   数据行数: {count}")
    print(f"   时间范围: {min_dt} -> {max_dt}")
    
    print("\n" + "="*60)
    print("🎯 完成！")
    print("下一步:")
    print("1. 配置回测API使用这个数据库路径")
    print("2. 重启API服务")
    print("3. 关羽将军重新回测")
    print("="*60)
    
    return True

if __name__ == "__main__":
    main()