import pandas as pd
import plotly.graph_objects as go
from plotly.subplots import make_subplots
from dash import Dash, dcc, html, Input, Output
from sqlalchemy import create_engine
from datetime import datetime

# 数据库连接配置
DB_CONFIG = {
    'host': '8.155.23.172',
    'port': 3306,
    'user': 'root2',
    'password': 'tG0f6PVYh18le41BCb',
    'database': 'elonX'
}
TABLE_NAME = 'poly_after2024'
db_uri = f"mysql+pymysql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['database']}"
engine = create_engine(db_uri)

# 第一步：提取用户的交易记录（从2024-01-01开始）
user_query = """
select IF(maker_asset_id = 0, 'buy', 'sell') as sellorbuy,
       maker_amount_filled/1000000 as maker_amount,
       taker_amount_filled/1000000 as taker_amount,
       maker_asset_id, taker_asset_id,
       timestamp, IF(maker_asset_id = 0, taker_asset_id, maker_asset_id) as assetid 
from poly_after2024
where maker = '0x7c3db723f1d4d8cb9c550095203b686cb11e5c6b' and timestamp >= 1704085200
union all
select IF(taker_asset_id = 0, 'buy', 'sell') as sellorbuy,
       taker_amount_filled/1000000 as maker_amount,
       maker_amount_filled/1000000 as taker_amount,
       maker_asset_id, taker_asset_id,
       timestamp, IF(maker_asset_id = 0, taker_asset_id, maker_asset_id) as assetid 
from poly_after2024
where taker = '0x7c3db723f1d4d8cb9c550095203b686cb11e5c6b' and timestamp >= 1704085200
"""
user_df = pd.read_sql_query(user_query, engine)

# 第二步：提取涉及的assetid的交易记录，计算价格
asset_ids = user_df['assetid'].unique()
asset_query = f"""
select timestamp, maker_amount_filled/1000000 as maker_amount, taker_amount_filled/1000000 as taker_amount,
       maker_asset_id, taker_asset_id,
       IF(maker_asset_id = 0, taker_asset_id, maker_asset_id) as assetid
from poly_after2024
where (IF(maker_asset_id = 0, taker_asset_id, maker_asset_id) in ({','.join([f"'{x}'" for x in asset_ids])}))
and timestamp >= 1704085200
"""
asset_df = pd.read_sql_query(asset_query, engine)

# 计算价格：金额/数量
asset_df['price'] = 0.0
for idx, row in asset_df.iterrows():
    if row['maker_asset_id'] == '0':
        asset_df.at[idx, 'price'] = row['maker_amount'] / row['taker_amount']
    else:
        asset_df.at[idx, 'price'] = row['taker_amount'] / row['maker_amount']
asset_df['date'] = pd.to_datetime(asset_df['timestamp'], unit='s').dt.date
daily_prices = asset_df.groupby(['date', 'assetid'])['price'].mean().reset_index()

# 第三步：计算用户持仓
user_df['date'] = pd.to_datetime(user_df['timestamp'], unit='s').dt.date
holdings = {}  # 记录持仓：{assetid: 数量}
daily_holdings = []  # 按天记录持仓和市值
dates = pd.date_range(start='2024-01-01', end='2025-05-22', freq='D')

for date in dates:
    date = date.date()
    day_trades = user_df[user_df['date'] <= date]
    # 更新持仓
    for idx, trade in day_trades.iterrows():
        assetid = trade['assetid']
        amount = trade['maker_amount'] if trade['sellorbuy'] == 'buy' else -trade['maker_amount']
        holdings[assetid] = holdings.get(assetid, 0) + amount
        if holdings[assetid] <= 0:  # 持仓为0，移除
            del holdings[assetid]
    # 计算市值
    total_value = 0
    asset_values = {}
    for assetid, amount in holdings.items():
        price_data = daily_prices[(daily_prices['assetid'] == assetid) & (daily_prices['date'] <= date)]
        if not price_data.empty:
            latest_price = price_data.sort_values('date', ascending=False).iloc[0]['price']
            value = amount * latest_price
            total_value += value
            asset_values[assetid] = value
    daily_holdings.append({'date': date, 'total_value': total_value, 'asset_values': asset_values})

# 转换为DataFrame
daily_holdings_df = pd.DataFrame(daily_holdings)

# 第四步：饼图数据（默认显示整个时间段的市场占比）
market_share = user_df.groupby('assetid').apply(
    lambda x: x[x['sellorbuy'] == 'buy']['taker_amount'].sum()
).reset_index(name='amount')
total_amount = market_share['amount'].sum()

# 第五步：初始化Dash应用
app = Dash(__name__)

# 初始图表
fig = make_subplots(rows=1, cols=2, specs=[[{"type": "pie"}, {"type": "scatter"}]], subplot_titles=("市场占比", "资产价值随时间变化"))

# 饼图（初始）
fig.add_trace(
    go.Pie(
        labels=market_share['assetid'],
        values=market_share['amount'],
        hoverinfo='label+value',
        textinfo='percent',
        texttemplate='%{percent:.2%}',
        marker=dict(colors=['#1f77b4', '#aec7e8', '#ff7f0e', '#ffbb78', '#2ca02c']),
        showlegend=False
    ),
    row=1, col=1
)

# 折线图
fig.add_trace(
    go.Scatter(
        x=daily_holdings_df['date'],
        y=daily_holdings_df['total_value'],
        mode='lines+markers',
        name='资产价值',
        customdata=daily_holdings_df['asset_values'],
        hovertemplate='<b>日期</b>: %{x}<br><b>总资产</b>: %{y:.2f}'
    ),
    row=1, col=2
)

# 布局
fig.update_layout(
    title_text="交易员 0x7c3db723f1d4d8cb9c550095203b686cb11e5c6b 资产分析",
    showlegend=False,
    height=500,
    width=1000
)

# 饼图中心显示总金额
fig.add_annotation(
    text=f"${total_amount:,.2f}<br>总计",
    x=0.18, y=0.5,
    showarrow=False,
    font=dict(size=16)
)

# Dash布局
app.layout = html.Div([
    dcc.Graph(id='main-graph', figure=fig),
    dcc.Store(id='holdings-data', data=daily_holdings_df.to_dict('records'))  # 存储持仓数据
])

# 回调：当鼠标悬停在折线图上时，更新饼图
@app.callback(
    Output('main-graph', 'figure'),
    Input('main-graph', 'hoverData'),
    Input('holdings-data', 'data')
)
def update_pie_chart(hoverData, holdings_data):
    # 恢复初始图表
    fig = make_subplots(rows=1, cols=2, specs=[[{"type": "pie"}, {"type": "scatter"}]], subplot_titles=("市场占比", "资产价值随时间变化"))
    holdings_df = pd.DataFrame(holdings_data)

    # 折线图保持不变
    fig.add_trace(
        go.Scatter(
            x=holdings_df['date'],
            y=holdings_df['total_value'],
            mode='lines+markers',
            name='资产价值',
            customdata=holdings_df['asset_values'],
            hovertemplate='<b>日期</b>: %{x}<br><b>总资产</b>: %{y:.2f}'
        ),
        row=1, col=2
    )

    # 如果没有hover数据，显示默认饼图
    if hoverData is None:
        fig.add_trace(
            go.Pie(
                labels=market_share['assetid'],
                values=market_share['amount'],
                hoverinfo='label+value',
                textinfo='percent',
                texttemplate='%{percent:.2%}',
                marker=dict(colors=['#1f77b4', '#aec7e8', '#ff7f0e', '#ffbb78', '#2ca02c']),
                showlegend=False
            ),
            row=1, col=1
        )
        fig.add_annotation(
            text=f"${total_amount:,.2f}<br>总计",
            x=0.18, y=0.5,
            showarrow=False,
            font=dict(size=16)
        )
    else:
        # 获取悬停的日期
        hover_date = hoverData['points'][0]['x']
        hover_date = pd.to_datetime(hover_date).date()
        # 找到该日期的持仓数据
        holdings_row = holdings_df[holdings_df['date'] == hover_date]
        if not holdings_row.empty:
            asset_values = holdings_row.iloc[0]['asset_values']
            labels = list(asset_values.keys())
            values = list(asset_values.values())
            total_value = sum(values)
            fig.add_trace(
                go.Pie(
                    labels=labels,
                    values=values,
                    hoverinfo='label+value',
                    textinfo='percent',
                    texttemplate='%{percent:.2%}',
                    marker=dict(colors=['#1f77b4', '#aec7e8', '#ff7f0e', '#ffbb78', '#2ca02c']),
                    showlegend=False
                ),
                row=1, col=1
            )
            fig.add_annotation(
                text=f"${total_value:,.2f}<br>总计",
                x=0.18, y=0.5,
                showarrow=False,
                font=dict(size=16)
            )

    fig.update_layout(
        title_text="交易员 0x7c3db723f1d4d8cb9c550095203b686cb11e5c6b 资产分析",
        showlegend=False,
        height=500,
        width=1000
    )
    return fig

# 运行Dash应用
if __name__ == '__main__':
    app.run_server(debug=True)