# coding:utf-8

import requests
import pandas as pd
from sqlalchemy import create_engine, text
from datetime import datetime
from config import XUEQIU_HEADERS

def fetch_and_store_stock_data(db_url, table_name='stock_changes', page_size=90):
    """获取雪球数据并保存到数据库"""
    base_url = 'https://stock.xueqiu.com/v5/stock/screener/quote/list.json'
    types = ['sha', 'sza', 'kcb']  # 数据类型
    headers = XUEQIU_HEADERS

    all_data = []

    for stock_type in types:
        params = {
            'page': 1,
            'size': page_size,
            'order': 'desc',
            'order_by': 'percent',
            'market': 'CN',
            'type': stock_type
        }

        # 初次请求以获取总页数
        response = requests.get(base_url, headers=headers, params=params)
        if response.status_code != 200:
            print(f"请求 {stock_type} 数据失败，状态码：{response.status_code}")
            continue

        data = response.json()
        total_count = data['data']['count']
        total_pages = (total_count // page_size) + 1

        for page in range(1, total_pages + 1):
            params['page'] = page
            response = requests.get(base_url, headers=headers, params=params)
            if response.status_code == 200:
                data = response.json()
                all_data.extend(data['data']['list'])
            else:
                print(f"请求 {stock_type} 数据第 {page} 页失败，状态码：{response.status_code}")
    # 转换为 DataFrame
    df = pd.DataFrame(all_data)

    if not df.empty:
        # 添加 id 列
        df['id'] = range(1, len(df) + 1)

        # 创建数据库连接
        engine = create_engine(db_url)

        # 将数据写入数据库表
        df.to_sql(table_name, con=engine, if_exists='replace', index=False)
        print(f"成功将数据写入数据库表 {table_name}。")
    else:
        print("未获取到任何数据。")

def get_cache_mark():
    """获取当前时间对应的缓存标记（0m, 2m, 4m, 6m, 8m）"""
    current_minute = datetime.now().minute
    mark = (current_minute % 10) // 2 * 2  # 将分钟数转换为0,2,4,6,8
    return "{}m".format(mark)

def save_sector_cache(engine, df_result, cache_mark):
    """保存板块数据到缓存表"""
    try:
        # 复制数据并添加缓存标记
        df_cache = df_result.copy()
        df_cache['cache_mark'] = cache_mark
        
        # 删除旧的缓存数据
        delete_query = text("DELETE FROM sector_performance_cache WHERE cache_mark = :cache_mark")
        with engine.connect() as conn:  # 使用 connect() 创建连接
            conn.execute(delete_query, {'cache_mark': cache_mark})
            conn.commit()  # 提交事务
        
        # 保存新的缓存数据
        df_cache.to_sql('sector_performance_cache', con=engine, if_exists='append', index=False)
        print("缓存数据已保存，标记: {}".format(cache_mark))
    except Exception as e:
        print("保存缓存数据时发生错误: {}".format(str(e)))

def ensure_cache_table_exists(engine):
    """确保缓存表存在"""
    try:
        create_table_query = text("""
            CREATE TABLE IF NOT EXISTS sector_performance_cache (
                id INT AUTO_INCREMENT PRIMARY KEY,
                sector_name VARCHAR(100),
                up_count INT,
                total_count INT,
                ratio FLOAT,
                rank_num INT,
                rank_change INT,
                add_time DATETIME,
                cache_mark VARCHAR(10)
            )
        """)
        with engine.connect() as conn:  # 使用 connect() 创建连接
            conn.execute(create_table_query)
            conn.commit()  # 提交事务
        print("缓存表检查/创建完成")
    except Exception as e:
        print("创建缓存表时发生错误: {}".format(str(e)))

def get_previous_cache_marks():
    """获取缓存标记的对应关系"""
    return {
        0: 4,  # 0m 对比 4m的数据
        2: 6,  # 2m 对比 6m的数据
        4: 8,  # 4m 对比 8m的数据
        6: 0,  # 6m 对比 0m的数据
        8: 2   # 8m 对比 2m的数据
    }

def get_previous_cache_ranks(engine):
    """获取上一个时间点的缓存数据"""
    try:
        # 获取当前缓存标记
        current_mark = int(get_cache_mark().replace('m', ''))
        
        # 获取应该对比的缓存标记
        cache_marks = get_previous_cache_marks()
        previous_mark = cache_marks.get(current_mark)
        
        if previous_mark is None:
            print("无法确定对比的缓存标记")
            return {}
            
        previous_cache_mark = "{}m".format(previous_mark)
        print("当前时间标记: {}m, 对比的缓存标记: {}".format(current_mark, previous_cache_mark))
        
        query = text("""
            SELECT sector_name, rank_num
            FROM sector_performance_cache
            WHERE cache_mark = :cache_mark
        """)
        
        result = pd.read_sql_query(query, engine, params={'cache_mark': previous_cache_mark})
        if result.empty:
            print("未找到缓存标记 {} 的数据".format(previous_cache_mark))
            return {}
            
        return dict(zip(result['sector_name'], result['rank_num']))
    except Exception as e:
        print("获取缓存数据时发生错误: {}".format(str(e)))
        return {}

def calculate_rank_change(row, previous_ranks):
    """计算排名变化"""
    previous_rank = previous_ranks.get(row['sector_name'])
    if previous_rank is None:
        return 0
    return previous_rank - row['rank_num']

def get_high_performance_stocks(db_url):
    """获取涨幅超过1.5%的股票及其板块分布情况"""
    engine = create_engine(db_url)

    try:
        # 确保缓存表存在
        ensure_cache_table_exists(engine)

        # 先获取最新数据
        fetch_and_store_stock_data(db_url)

        # 读取数据
        stock_changes_df = pd.read_sql_table('stock_changes', con=engine)
        gp_gnbk_df = pd.read_sql_table('gp_gnbk', con=engine)

        # 去掉 symbol 字段的前两个字符
        stock_changes_df['symbol'] = stock_changes_df['symbol'].str[2:]

        # 筛选涨幅超过 1.5% 的股票
        high_performance_stocks = stock_changes_df[stock_changes_df['percent'] > 1.5]

        # 关联两个表，获取 bk_name
        merged_df = high_performance_stocks.merge(gp_gnbk_df, left_on='symbol', right_on='gp_code')

        # 统计每个 bk_name 的数量
        total_counts = gp_gnbk_df['bk_name'].value_counts()
        high_performance_counts = merged_df['bk_name'].value_counts()

        # 计算每个板块的上涨家数及占比
        result = []
        for bk_name, count in high_performance_counts.items():
            total_count = total_counts[bk_name]
            ratio = count / total_count
            result.append((bk_name, count, total_count, ratio))

        # 按比重排序
        result.sort(key=lambda x: x[3], reverse=True)

        # 创建DataFrame并添加排序字段
        df_result = pd.DataFrame(result, columns=['sector_name', 'up_count', 'total_count', 'ratio'])
        df_result['rank_num'] = range(1, len(df_result) + 1)  # 添加排序字段，从1开始
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        df_result['add_time'] = current_time  # 添加时间字段

        # 获取上一个时间点的缓存数据进行对比
        previous_ranks = get_previous_cache_ranks(engine)
        # 计算排名变化
        df_result['rank_change'] = df_result.apply(
            lambda row: calculate_rank_change(row, previous_ranks), axis=1
        )

        # 保存到数据库（追加模式）
        df_result.to_sql('sector_performance', con=engine, if_exists='append', index=False)

        # 保存到缓存表
        cache_mark = get_cache_mark()
        save_sector_cache(engine, df_result, cache_mark)

        # 输出结果
        for _, row in df_result.iterrows():
            print("板块名称: {}, 上涨家数: {}, 总数: {}, 比重: {:.2%}, 排名: {}, 排名变化: {}".format(
                row['sector_name'], row['up_count'], row['total_count'],
                row['ratio'], row['rank_num'], row['rank_change']
            ))
            
        return result
    except Exception as e:
        print("分析数据时发生错误: {}".format(str(e)))
        return []

def get_top_industries_and_stocks(db_url, top_start=1, top_end=10):
    """
    分析在多个热门板块中出现的股票

    Parameters:
    -----------
    db_url : str
        数据库连接URL
    top_start : int
        起始排名
    top_end : int
        结束排名
    """
    try:
        engine = create_engine(db_url)

        # 获取高表现股票及其板块信息
        top_industries = get_high_performance_stocks(db_url)
        if not top_industries:
            print("无法获取行业数据")
            return

        # 提取指定范围的行名称
        top_industry_names = [industry[0] for industry in top_industries[top_start-1:top_end]]

        # 读取数据
        stock_changes_df = pd.read_sql_table('stock_changes', con=engine)
        gp_gnbk_df = pd.read_sql_table('gp_gnbk', con=engine)

        # 去掉 symbol 字段的前两个字符
        stock_changes_df['symbol'] = stock_changes_df['symbol'].str[2:]

        # 关联两个表，获取 bk_name 和 gp_name
        merged_df = stock_changes_df.merge(gp_gnbk_df, left_on='symbol', right_on='gp_code')

        # 筛选指定范围的行业的股票
        filtered_df = merged_df[merged_df['bk_name'].isin(top_industry_names)]

        # 统计每只股票命中行业的数量
        stock_industry_counts = filtered_df.groupby(['symbol', 'gp_name'])['bk_name'].nunique().sort_values(ascending=False).head(10)

        # 获取每只股票的命中行业数量
        stock_industry_list = stock_industry_counts.reset_index()
        stock_industry_list.columns = ['symbol', 'stock_name', 'sector_count']
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        stock_industry_list['add_time'] = current_time  # 添加时间字段

        # 保存结果到数据库（改为追加模式）
        stock_industry_list.to_sql('top_sector_stocks', con=engine, if_exists='append', index=False)

        # 输出结果
        for index, row in stock_industry_list.iterrows():
            print("股票代码: {}, 股票名称: {}, 命中行业数量: {}".format(
                row['symbol'], row['stock_name'], row['sector_count']
            ))

    except Exception as e:
        print("分析行业数据时发生错误: {}".format(str(e)))

def clean_historical_data(engine):
    """清理当天0点之前的历史数据"""
    try:
        today = datetime.now().strftime('%Y-%m-%d 00:00:00')
        
        # 清理sector_performance表
        clean_performance_query = text("""
            DELETE FROM sector_performance 
            WHERE add_time < :today
        """)
        
        # 清理sector_performance_cache表
        clean_cache_query = text("""
            DELETE FROM sector_performance_cache 
            WHERE add_time < :today
        """)
        
        # 清理top_sector_stocks表
        clean_stocks_query = text("""
            DELETE FROM top_sector_stocks 
            WHERE add_time < :today
        """)
        
        with engine.connect() as conn:  # 使用 connect() 创建连接
            result1 = conn.execute(clean_performance_query, {'today': today})
            result2 = conn.execute(clean_cache_query, {'today': today})
            result3 = conn.execute(clean_stocks_query, {'today': today})
            conn.commit()  # 提交事务
            
        print("清理历史数据完成:")
        print("- sector_performance表删除了{}条记录".format(result1.rowcount))
        print("- sector_performance_cache表删除了{}条记录".format(result2.rowcount))
        print("- top_sector_stocks表删除了{}条记录".format(result3.rowcount))
        
    except Exception as e:
        print("清理历史数据时发生错误: {}".format(str(e)))

if __name__ == "__main__":
    # 示例调用
    db_url = 'mysql+pymysql://root:Chlry#$.8@192.168.18.199:3306/db_gp_cj'
    
    # 创建数据库连接
    engine = create_engine(db_url)
    
    # 清理历史数据
    clean_historical_data(engine)
    
    # 执行主要分析
    get_top_industries_and_stocks(db_url, 1, 10)