import pandas as pd
from pathlib import Path

BASE_PATH = Path(__file__).resolve().parents[2]
RATES_PATH = BASE_PATH / "data" / "raw" / "rates"


def _load_two_col_csv(file_path: Path, value_name: str) -> pd.DataFrame:
    df = pd.read_csv(file_path)
    df.columns = [c.strip().lower() for c in df.columns]

    if len(df.columns) < 2:
        raise ValueError(f"{file_path.name} does not have at least 2 columns.")

    df = df.iloc[:, :2].copy()
    df.columns = ["date", value_name]

    df["date"] = pd.to_datetime(df["date"], errors="coerce")
    df[value_name] = pd.to_numeric(df[value_name], errors="coerce")

    df = (
        df.dropna(subset=["date", value_name])
          .drop_duplicates(subset=["date"])
          .sort_values("date")
          .reset_index(drop=True)
    )

    return df


def load_rates():
    us2y = _load_two_col_csv(RATES_PATH / "us2y.csv", "us2y")
    de2y = _load_two_col_csv(RATES_PATH / "de2y.csv", "de2y")
    us10y = _load_two_col_csv(RATES_PATH / "us10y.csv", "us10y")
    de10y = _load_two_col_csv(RATES_PATH / "de10y.csv", "de10y")

    df = us2y.merge(de2y, on="date", how="inner")
    df = df.merge(us10y, on="date", how="left")
    df = df.merge(de10y, on="date", how="left")

    df["spread_2y"] = df["us2y"] - df["de2y"]
    df["spread_10y"] = df["us10y"] - df["de10y"]

    df = df.sort_values("date").reset_index(drop=True)

    return df


if __name__ == "__main__":
    rates = load_rates()

    print(rates.head())
    print(rates.tail())
    print("\nDate range:", rates["date"].min(), "to", rates["date"].max())
    print("Rows:", len(rates))
    print("\nColumns:")
    print(rates.columns.tolist())