import os
from typing import List, Tuple
import pandas as pd
import matplotlib.pyplot as plt

def read_and_preprocess_data(file_path: str) -> pd.DataFrame:
    """엑셀 파일을 읽고 전처리하는 함수"""
    # 엑셀 파일 읽기 (상위 2행 스킵)
    df = pd.read_excel(file_path, index_col=False, skiprows=[0, 1])

    # 컬럼 이름 변경
    new_columns = ['ml', 'mAU', 'ml_1', 'mS_cm', 'ml_2', 'percent', 'ml_3', '%B',
                   'ml_4', 'pH', 'ml_5', 'MPa', 'ml_6', 'ml_min', 'ml_7',
                   'temperature_C', 'ml_8', 'Frac', 'ml_9', 'Injections',
                   'ml_10', 'Set_Marks']
    df.columns = new_columns

    # %B 값 수정
    elution_start = df[df['Set_Marks'] == "Block Isocratic_Elution"]["ml_10"].values[0]
    df['%B'] = df['ml'].apply(lambda x: 100 if x >= elution_start else 0)

    return df

def setup_plot() -> Tuple[plt.Figure, plt.Axes]:
    """플롯 초기 설정"""
    fig, ax_main = plt.subplots(figsize=(10, 6))
    plt.subplots_adjust(top=0.85)
    return fig, ax_main

def plot_mau(ax: plt.Axes, df: pd.DataFrame) -> None:
    """mAU 데이터 플로팅"""
    ax.plot(df['ml'], df['mAU'], color='blue', label='mAU')
    ax.fill_between(df['ml'], df['mAU'], color='lightblue', alpha=0.3)
    ax.set_xlabel('ml')
    ax.set_ylabel('mAU', color='blue')
    ax.set_ylim(0, 2500)
    ax.tick_params(axis='y', labelcolor='blue')

def plot_b_percentage(ax: plt.Axes, df: pd.DataFrame) -> plt.Axes:
    """%B 데이터 플로팅"""
    ax_b = ax.twinx()
    ax_b.plot(df['ml'], df['%B'], color='red', label='%B')
    ax_b.set_ylabel('%B', color='red')
    ax_b.tick_params(axis='y', labelcolor='red')
    return ax_b

def plot_ph(ax: plt.Axes, df: pd.DataFrame) -> plt.Axes:
    """pH 데이터 플로팅"""
    ax_ph = ax.twinx()
    ax_ph.plot(df['ml_4'], df['pH'], color='green', label='pH')
    ax_ph.set_ylabel('pH', color='green')
    ax_ph.set_ylim(0, 12)
    ax_ph.tick_params(axis='y', labelcolor='green')
    ax_ph.spines['right'].set_position(('outward', 60))
    return ax_ph

def add_fraction_lines(ax: plt.Axes, df: pd.DataFrame) -> None:
    """분획 정보 표시"""
    for _, row in df.iterrows():
        if pd.notna(row['Frac']):
            ax.axvline(x=row['ml_8'], color='gray', linestyle='--', alpha=0.5)
            ax.text(row['ml_8'], ax.get_ylim()[1], row['Frac'], rotation=90, va='top', ha='right')

def add_combined_legend(axes: List[plt.Axes]) -> None:
    """모든 축의 범례 통합"""
    lines, labels = [], []
    for ax in axes:
        ax_lines, ax_labels = ax.get_legend_handles_labels()
        lines.extend(ax_lines)
        labels.extend(ax_labels)
    axes[0].legend(lines, labels, loc='upper left')

def process_and_plot_file(file_path: str) -> None:
    """단일 파일 처리 및 플로팅"""
    print(f'Processing file: {os.path.basename(file_path)}')

    df = read_and_preprocess_data(file_path)
    fig, ax_main = setup_plot()

    plot_mau(ax_main, df)
    ax_b = plot_b_percentage(ax_main, df)
    ax_ph = plot_ph(ax_main, df)
    add_fraction_lines(ax_main, df)
    add_combined_legend([ax_main, ax_b, ax_ph])

    plt.title(f'{os.path.basename(file_path)} Plot', y=1.01)
    ax_main.grid(True, linestyle='--', alpha=0.7)
    plt.tight_layout()
    plt.show()

def process_folder(folder_path: str) -> None:
    """폴더 내 모든 .xls 파일 처리"""
    for filename in os.listdir(folder_path):
        if filename.endswith('.xls'):
            file_path = os.path.join(folder_path, filename)
            process_and_plot_file(file_path)