import warnings

import requests
from bs4 import BeautifulSoup
import pandas as pd
import os
import re
import matplotlib

# 設置matplotlib的字體配置，以支持中文顯示
matplotlib.rcParams['font.sans-serif'] = ['SimHei']  # 或者 ['Microsoft YaHei']
matplotlib.rcParams['axes.unicode_minus'] = False
matplotlib.use('TkAgg')
from matplotlib import pyplot as plt


def fetch_html_page(url):
    """
    獲取HTML頁面內容。

    參數(shù):
    url (str): 目標網頁的URL。

    返回:
    str: 頁面的HTML內容，如果請求失敗則返回None。
    """
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36"
    }
    response = requests.get(url, headers=headers)
    if response.status_code == 200:
        print(f"成功獲取頁面: {url}")
        return response.text
    else:
        print(f"Error: {response.status_code}, {response.text}")
        return None


def parse_html_for_excel_links(html_content):
    """
    解析HTML內容中的Excel鏈接。

    參數(shù):
    html_content (str): HTML頁面內容。

    返回:
    list: 包含所有找到的Excel文件鏈接的列表。
    """
    soup = BeautifulSoup(html_content, 'html.parser')
    excel_links = []
    for a_tag in soup.find_all('a', href=True):
        href = a_tag.get('href')
        if href and href.endswith('.xlsx'):
            excel_links.append(href)
    return excel_links


def parse_html_for_max_page(html_content):
    """
    解析HTML內容以找到最大頁面數(shù)。

    參數(shù):
    html_content (str): HTML頁面內容。

    返回:
    int: 最大頁面數(shù)。
    """
    soup = BeautifulSoup(html_content, 'html.parser')
    max_page = 1
    for a_tag in soup.find_all('a', class_='pagingNormal'):
        onclick = a_tag.get('onclick')
        if onclick:
            match = re.search(r"'(/test/j/[^']+)'", onclick)
            if match:
                page_number = match.group(1).split('-')[-1].split('.')[0]
                max_page = max(max_page, int(page_number))
    return max_page


def download_excel_file(url, save_path):
    """
    下載Excel文件并保存到指定路徑。

    參數(shù):
    url (str): Excel文件的URL。
    save_path (str): 文件的保存路徑。
    """
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36"
    }
    response = requests.get(url, headers=headers)
    if response.status_code == 200:
        with open(save_path, 'wb') as f:
            f.write(response.content)
        print(f"下載完成: {save_path}")
    else:
        print(f"Error: {response.status_code}, {response.text}")


def download_excel_data():
    """
    下載所有Excel數(shù)據(jù)文件。
    """
    base_url = 'https://test/index.html'  # 替換為實際網頁地址
    current_url = base_url
    page_number = 1

    html_content = fetch_html_page(current_url)
    if not html_content:
        return

    max_page = parse_html_for_max_page(html_content)
    print(f"最大頁面數(shù): {max_page}")

    while page_number <= max_page:
        print(f"正在處理第 {page_number} 頁: {current_url}")
        html_content = fetch_html_page(current_url)
        if not html_content:
            break

        excel_links = parse_html_for_excel_links(html_content)
        if not excel_links:
            print("未找到Excel鏈接。")
            break

        for link in excel_links:
            full_url = f"https://www.test.cn{link}"
            # 提取日期和文件名部分
            file_path_parts = link.split('/')
            file_name = ('/'.join(file_path_parts[-3:-1]) + '/' + file_path_parts[-1]).replace('/', '-')
            save_path = os.path.join('downloads', file_name)
            os.makedirs(os.path.dirname(save_path), exist_ok=True)
            download_excel_file(full_url, save_path)

        if page_number < max_page:
            next_page_link = f"/test/d2bb5c19-{page_number + 1}.html"
            current_url = f"https://www.test.cn{next_page_link}"
            page_number += 1
        else:
            print("沒有更多頁面。")
            break


def read_excel_file(file_path):
    """
    讀取Excel文件內容。

    參數(shù):
    file_path (str): Excel文件的路徑。

    返回:
    DataFrame: 讀取到的Excel文件內容，如果讀取失敗則返回None。
    """
    try:
        with warnings.catch_warnings():
            warnings.simplefilter("ignore", UserWarning)
            df = pd.read_excel(file_path, engine='openpyxl', header=None)
        return df
    except Exception as e:
        print(f"讀取Excel文件時出錯: {e}")
        return None


def process_excel_data(df):
    """
    處理Excel數(shù)據(jù)，將其轉換為字典格式。

    參數(shù):
    df (DataFrame): Excel文件內容。

    返回:
    dict: 轉換后的字典數(shù)據(jù)。
    """
    if df is None:
        return {}

    # 處理合并單元格
    # df = df.fillna(method='ffill').fillna(method='bfill')

    # 將數(shù)據(jù)轉換為字典
    data_dict = {}
    current_section = None
    for index, row in df.iterrows():
        if index == 1:  # 第二行
            key = row[1]
            if pd.isnull(key):
                key = df.iloc[1, 0]
            value = row[2] if pd.notnull(row[2]) else None
            data_dict[key] = value
        elif index > 1:
            if pd.notnull(row[0]):
                current_section = row[0]
                data_dict[current_section] = {}
            if pd.notnull(row[1]):
                key = row[1]
                value = row[2] if pd.notnull(row[2]) else None
                data_dict[current_section][key] = value

    return data_dict


def process_downloaded_files(directory):
    """
    處理下載的Excel文件，提取數(shù)據(jù)。

    參數(shù):
    directory (str): 存放下載文件的目錄路徑。

    返回:
    list: 包含所有處理后的數(shù)據(jù)字典的列表。
    """
    data_list = []
    for filename in os.listdir(directory):
        if filename.endswith('.xlsx'):
            file_path = os.path.join(directory, filename)
            df = read_excel_file(file_path)
            if df is not None:
                print(f"處理文件: {filename}")
                data_dict = process_excel_data(df)
                print(data_dict)  # 打印處理后的字典
                data_list.append(data_dict)
    return data_list


def plot_investor_trends(data_list):
    """
    繪制投資者數(shù)量趨勢圖。

    參數(shù):
    data_list (list): 包含投資者數(shù)據(jù)的列表。
    """
    # 提取時間值和投資者數(shù)量
    dates = []
    individual_investors = []
    institutional_investors = []

    for data_dict in data_list:
        date_str = data_dict['統(tǒng)計指標']
        date = pd.to_datetime(date_str)
        dates.append(date)
        individual_investors.append(data_dict['證券公司開展業(yè)務情況']['個人投資者數(shù)量（萬名）'])
        institutional_investors.append(data_dict['證券公司開展業(yè)務情況']['機構投資者數(shù)量（家）'])

    # 創(chuàng)建折線圖
    fig, ax1 = plt.subplots(figsize=(10, 6))

    # 繪制個人投資者數(shù)量
    color = 'tab:red'
    ax1.set_xlabel('日期')
    ax1.set_ylabel('個人投資者數(shù)量（萬名）', color=color)
    ax1.plot(dates, individual_investors, color=color, label='個人投資者數(shù)量（萬名）', marker='o')
    ax1.tick_params(axis='y', labelcolor=color)

    # 創(chuàng)建第二個 Y 軸
    ax2 = ax1.twinx()  # 共享 X 軸

    # 繪制機構投資者數(shù)量
    color = 'tab:blue'
    ax2.set_ylabel('機構投資者數(shù)量（家）', color=color)
    ax2.plot(dates, institutional_investors, color=color, label='機構投資者數(shù)量（家）', marker='o')
    ax2.tick_params(axis='y', labelcolor=color)

    # 設置標題和圖例
    fig.tight_layout()  # 調整子圖參數(shù)，防止標簽重疊
    plt.title('投資者數(shù)量趨勢')
    fig.legend(loc='upper left', bbox_to_anchor=(0.1, 0.9))

    # 顯示圖形
    plt.show()


# 調用函數(shù)繪制投資者趨勢圖
plot_investor_trends(process_downloaded_files('downloads'))