"""
daily_rates_collector.py
=========================
Collects daily government bond yield data from official central bank
and treasury sources for UK, Japan, and Australia.

Sources
-------
UK (Bank of England):
  Nominal spot yield curve published daily.
  URL: https://www.bankofengland.co.uk/boeapps/database/_iadb-FromShowColumns.asp
  Series codes used:
    IUDSSPY = UK 2Y nominal spot yield
    IUDSNPY = UK 10Y nominal spot yield

Japan (Ministry of Finance):
  Daily JGB (Japanese Government Bond) yield curve.
  URL: https://www.mof.go.jp/english/jgbs/reference/interest_rate/historical/jgbcm_all.csv
  Contains 2Y and 10Y columns directly.

Australia (Reserve Bank of Australia):
  Daily government bond yields - Table F2.
  URL: https://www.rba.gov.au/statistics/tables/xls-hist/f2hist.xls
  Contains 2Y and 10Y government bond yields.

Output
------
  data/raw/rates/uk2y_daily.csv   (date, uk2y)
  data/raw/rates/uk10y_daily.csv  (date, uk10y)
  data/raw/rates/jp2y_daily.csv   (date, jp2y)
  data/raw/rates/jp10y_daily.csv  (date, jp10y)
  data/raw/rates/au2y_daily.csv   (date, au2y)
  data/raw/rates/au10y_daily.csv  (date, au10y)

Once verified, these replace the monthly files in multi_pair_signals_v1.py.

Place this file in:
  C:\\Users\\paul_\\OneDrive\\fx_macro_intraday\\src\\ingestion\\daily_rates_collector.py

Run from project root:
  python src/ingestion/daily_rates_collector.py
"""

import pandas as pd
import numpy as np
import requests
from pathlib import Path
from io import StringIO, BytesIO
import sys

BASE_PATH  = Path(__file__).resolve().parents[2]
SRC_PATH   = BASE_PATH / "src"
if str(SRC_PATH) not in sys.path:
    sys.path.append(str(SRC_PATH))

RATES_PATH = BASE_PATH / "data" / "raw" / "rates"
RATES_PATH.mkdir(parents=True, exist_ok=True)

HEADERS = {
    "User-Agent": (
        "Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
        "AppleWebKit/537.36 (KHTML, like Gecko) "
        "Chrome/120.0.0.0 Safari/537.36"
    )
}


# ── Helpers ───────────────────────────────────────────────────────────────────
def save_series(df: pd.DataFrame, filename: str, value_col: str) -> None:
    out = RATES_PATH / filename
    df.to_csv(out, index=False)
    print(f"  Saved {filename}  |  {len(df)} rows  |  "
          f"{df['date'].min()} → {df['date'].max()}")


def clean_series(df: pd.DataFrame, date_col: str, value_col: str,
                 out_col: str) -> pd.DataFrame:
    """Standard cleaning for any rate series."""
    df = df[[date_col, value_col]].copy()
    df.columns = ["date", out_col]
    df["date"]   = pd.to_datetime(df["date"], errors="coerce")
    df[out_col]  = pd.to_numeric(df[out_col], errors="coerce")
    df = (df.dropna()
            .drop_duplicates(subset=["date"])
            .sort_values("date")
            .reset_index(drop=True))
    df["date"] = df["date"].dt.strftime("%Y-%m-%d")
    return df


# ── UK — Bank of England ──────────────────────────────────────────────────────
def fetch_boe_yields() -> tuple[pd.DataFrame | None, pd.DataFrame | None]:
    """
    Fetches UK nominal spot yields from the Bank of England database.

    The BOE publishes a downloadable CSV with the nominal yield curve.
    Series codes:
      IUDSSPY = 2-year nominal spot rate
      IUDSNPY = 10-year nominal spot rate
    """
    print("\n  Fetching UK yields from Bank of England...")

    # BOE CSV export URL
    url = (
        "https://www.bankofengland.co.uk/boeapps/database/"
        "_iadb-FromShowColumns.asp?"
        "csv.x=yes"
        "&Datefrom=01/Jan/2000"
        "&Dateto=now"
        "&SeriesCodes=IUDSSPY,IUDSNPY"
        "&CSVF=TT"
        "&UsingCodes=Y"
    )

    try:
        r = requests.get(url, headers=HEADERS, timeout=60)
        r.raise_for_status()
        text = r.text

        # BOE CSV has a specific format — skip header rows until we find the data
        lines = text.strip().splitlines()
        data_lines = []
        header_found = False

        for line in lines:
            if not header_found:
                if "DATE" in line.upper() or "date" in line.lower():
                    header_found = True
                    data_lines.append(line)
            else:
                data_lines.append(line)

        if not data_lines:
            # Fallback: try reading directly
            df_raw = pd.read_csv(StringIO(text), skiprows=3)
        else:
            df_raw = pd.read_csv(StringIO("\n".join(data_lines)))

        df_raw.columns = [c.strip() for c in df_raw.columns]
        print(f"    Columns: {list(df_raw.columns)[:6]}")
        print(f"    Rows: {len(df_raw)}")
        print(f"    First row: {df_raw.iloc[0].tolist()[:4]}")

        # Identify date column and rate columns
        date_col = df_raw.columns[0]

        # Find 2Y and 10Y columns
        uk2y_col  = None
        uk10y_col = None
        for col in df_raw.columns:
            cu = col.upper().strip()
            if "IUDSSPY" in cu or "2YR" in cu or "2 YR" in cu or "2YEAR" in cu:
                uk2y_col = col
            if "IUDSNPY" in cu or "10YR" in cu or "10 YR" in cu or "10YEAR" in cu:
                uk10y_col = col

        # If specific codes not found, try positional (BOE format: date, 2Y, 10Y, ...)
        if uk2y_col is None and len(df_raw.columns) >= 2:
            uk2y_col  = df_raw.columns[1]
            print(f"    Using column '{uk2y_col}' as UK 2Y (positional fallback)")
        if uk10y_col is None and len(df_raw.columns) >= 3:
            uk10y_col = df_raw.columns[2]
            print(f"    Using column '{uk10y_col}' as UK 10Y (positional fallback)")

        uk2y  = clean_series(df_raw, date_col, uk2y_col,  "uk2y")  if uk2y_col  else None
        uk10y = clean_series(df_raw, date_col, uk10y_col, "uk10y") if uk10y_col else None

        if uk2y  is not None: print(f"    UK 2Y : {len(uk2y)} rows")
        if uk10y is not None: print(f"    UK 10Y: {len(uk10y)} rows")

        return uk2y, uk10y

    except Exception as e:
        print(f"    [ERROR] BOE fetch failed: {e}")
        return None, None


def fetch_boe_yields_alternative() -> tuple[pd.DataFrame | None, pd.DataFrame | None]:
    """
    Alternative BOE endpoint using their statistics search API.
    Fetches the gilt par yield curve as a proxy for 2Y/10Y spot rates.
    """
    print("\n  Trying BOE alternative endpoint...")

    results = {}
    series_map = {
        "uk2y" : "IUDSSPY",   # 2-year nominal spot
        "uk10y": "IUDSNPY",   # 10-year nominal spot
    }

    for col, code in series_map.items():
        url = (
            f"https://www.bankofengland.co.uk/boeapps/database/"
            f"_iadb-FromShowColumns.asp?"
            f"csv.x=yes"
            f"&Datefrom=01/Jan/2000"
            f"&Dateto=now"
            f"&SeriesCodes={code}"
            f"&CSVF=TT"
            f"&UsingCodes=Y"
        )
        try:
            r = requests.get(url, headers=HEADERS, timeout=30)
            r.raise_for_status()
            df_raw = pd.read_csv(StringIO(r.text), skiprows=0)
            df_raw.columns = [c.strip() for c in df_raw.columns]

            # First col = date, second = value
            date_col  = df_raw.columns[0]
            value_col = df_raw.columns[1] if len(df_raw.columns) > 1 else None

            if value_col:
                cleaned = clean_series(df_raw, date_col, value_col, col)
                if len(cleaned) > 100:
                    results[col] = cleaned
                    print(f"    {code}: {len(cleaned)} rows ✓")
                else:
                    print(f"    {code}: too few rows ({len(cleaned)})")
            else:
                print(f"    {code}: no value column found")

        except Exception as e:
            print(f"    {code}: failed — {e}")

    uk2y  = results.get("uk2y")
    uk10y = results.get("uk10y")
    return uk2y, uk10y


# ── Japan — Ministry of Finance ───────────────────────────────────────────────
def fetch_mof_japan_yields() -> tuple[pd.DataFrame | None, pd.DataFrame | None]:
    """
    Fetches daily JGB yields from Japan Ministry of Finance.
    URL: https://www.mof.go.jp/english/jgbs/reference/interest_rate/historical/jgbcm_all.csv
    Contains columns: Date, 1year, 2year, 3year, 5year, 7year, 10year, 20year, 30year, 40year
    """
    print("\n  Fetching Japan JGB yields from Ministry of Finance...")

    url = "https://www.mof.go.jp/english/jgbs/reference/interest_rate/historical/jgbcm_all.csv"

    try:
        r = requests.get(url, headers=HEADERS, timeout=60)
        r.raise_for_status()

        # Try reading — MoF CSV may have a header row
        text = r.text
        df_raw = pd.read_csv(StringIO(text), skiprows=0)
        df_raw.columns = [c.strip().lower() for c in df_raw.columns]

        print(f"    Columns: {list(df_raw.columns)[:8]}")
        print(f"    Rows: {len(df_raw)}")

        # Find date column
        date_col = df_raw.columns[0]

        # Find 2Y and 10Y
        jp2y_col  = None
        jp10y_col = None
        for col in df_raw.columns:
            cl = col.lower().strip()
            if cl in ["2year", "2yr", "2-year", "2 year", "maturity2y"]:
                jp2y_col = col
            if cl in ["10year", "10yr", "10-year", "10 year", "maturity10y"]:
                jp10y_col = col

        if jp2y_col is None:
            print(f"    Available columns: {list(df_raw.columns)}")

        jp2y  = clean_series(df_raw, date_col, jp2y_col,  "jp2y")  if jp2y_col  else None
        jp10y = clean_series(df_raw, date_col, jp10y_col, "jp10y") if jp10y_col else None

        if jp2y  is not None: print(f"    JP 2Y : {len(jp2y)} rows  ✓")
        if jp10y is not None: print(f"    JP 10Y: {len(jp10y)} rows  ✓")

        return jp2y, jp10y

    except Exception as e:
        print(f"    [ERROR] MoF Japan fetch failed: {e}")
        return None, None


def fetch_mof_japan_current() -> tuple[pd.DataFrame | None, pd.DataFrame | None]:
    """
    Fetches current year JGB yields (MoF also has year-by-year files).
    """
    print("\n  Trying MoF Japan current year file...")
    url = "https://www.mof.go.jp/english/jgbs/reference/interest_rate/jgbcm.csv"
    try:
        r = requests.get(url, headers=HEADERS, timeout=30)
        r.raise_for_status()
        df_raw = pd.read_csv(StringIO(r.text))
        df_raw.columns = [c.strip().lower() for c in df_raw.columns]
        print(f"    Columns: {list(df_raw.columns)[:8]}")
        return None, None
    except Exception as e:
        print(f"    Failed: {e}")
        return None, None


# ── Australia — Reserve Bank of Australia ─────────────────────────────────────
def fetch_rba_yields() -> tuple[pd.DataFrame | None, pd.DataFrame | None]:
    """
    Fetches daily Australian government bond yields from RBA Table F2.
    URL: https://www.rba.gov.au/statistics/tables/xls-hist/f2hist.xls
    Contains daily 2Y and 10Y government bond yields.
    """
    print("\n  Fetching Australia yields from Reserve Bank of Australia...")

    # Try the historical Excel file first
    urls_to_try = [
        "https://www.rba.gov.au/statistics/tables/xls-hist/f2hist.xls",
        "https://www.rba.gov.au/statistics/tables/xls/f2.xls",
        "https://www.rba.gov.au/statistics/tables/xls-hist/f2.xls",
    ]

    for url in urls_to_try:
        try:
            print(f"    Trying: {url}")
            r = requests.get(url, headers=HEADERS, timeout=60)
            r.raise_for_status()

            # RBA files are Excel format
            try:
                df_raw = pd.read_excel(BytesIO(r.content), sheet_name=0, skiprows=1)
            except Exception:
                df_raw = pd.read_excel(BytesIO(r.content), sheet_name=0)

            df_raw.columns = [str(c).strip() for c in df_raw.columns]
            print(f"    Columns: {list(df_raw.columns)[:8]}")
            print(f"    Rows: {len(df_raw)}")

            # Find date column (usually first column)
            date_col = df_raw.columns[0]

            # Find 2Y and 10Y columns
            au2y_col  = None
            au10y_col = None
            for col in df_raw.columns:
                cl = str(col).lower().strip()
                if any(x in cl for x in ["2 year", "2year", "2-year", "2yr"]):
                    au2y_col = col
                if any(x in cl for x in ["10 year", "10year", "10-year", "10yr"]):
                    au10y_col = col

            if au2y_col is None:
                print(f"    Could not find 2Y column. All columns: {list(df_raw.columns)}")
                continue

            au2y  = clean_series(df_raw, date_col, au2y_col,  "au2y")  if au2y_col  else None
            au10y = clean_series(df_raw, date_col, au10y_col, "au10y") if au10y_col else None

            if au2y  is not None and len(au2y)  > 100:
                print(f"    AU 2Y : {len(au2y)} rows  ✓")
                return au2y, au10y
            if au10y is not None and len(au10y) > 100:
                print(f"    AU 10Y: {len(au10y)} rows  ✓")
                return au2y, au10y

        except Exception as e:
            print(f"    Failed: {e}")
            continue

    return None, None


# ── Main ──────────────────────────────────────────────────────────────────────
def main():
    print("=" * 65)
    print("DAILY RATES COLLECTOR")
    print(f"  Sources: Bank of England / MoF Japan / RBA")
    print(f"  Output : {RATES_PATH}")
    print("=" * 65)

    results = {}

    # ── UK ────────────────────────────────────────────────────────────────────
    print("\n" + "─" * 65)
    print("UK — Bank of England")
    uk2y, uk10y = fetch_boe_yields()

    if uk2y is None or uk10y is None:
        print("  Primary BOE endpoint failed. Trying alternative...")
        uk2y, uk10y = fetch_boe_yields_alternative()

    if uk2y is not None and len(uk2y) > 100:
        save_series(uk2y,  "uk2y_daily.csv",  "uk2y")
        results["uk2y"]  = True
    else:
        print("  [FAILED] UK 2Y daily — manual download required")
        results["uk2y"]  = False

    if uk10y is not None and len(uk10y) > 100:
        save_series(uk10y, "uk10y_daily.csv", "uk10y")
        results["uk10y"] = True
    else:
        print("  [FAILED] UK 10Y daily — manual download required")
        results["uk10y"] = False

    # ── Japan ─────────────────────────────────────────────────────────────────
    print("\n" + "─" * 65)
    print("Japan — Ministry of Finance")
    jp2y, jp10y = fetch_mof_japan_yields()

    if jp2y is not None and len(jp2y) > 100:
        save_series(jp2y,  "jp2y_daily.csv",  "jp2y")
        results["jp2y"]  = True
    else:
        print("  [FAILED] Japan 2Y daily — manual download required")
        results["jp2y"]  = False

    if jp10y is not None and len(jp10y) > 100:
        save_series(jp10y, "jp10y_daily.csv", "jp10y")
        results["jp10y"] = True
    else:
        print("  [FAILED] Japan 10Y daily — manual download required")
        results["jp10y"] = False

    # ── Australia ─────────────────────────────────────────────────────────────
    print("\n" + "─" * 65)
    print("Australia — Reserve Bank of Australia")
    au2y, au10y = fetch_rba_yields()

    if au2y is not None and len(au2y) > 100:
        save_series(au2y,  "au2y_daily.csv",  "au2y")
        results["au2y"]  = True
    else:
        print("  [FAILED] Australia 2Y daily — manual download required")
        results["au2y"]  = False

    if au10y is not None and len(au10y) > 100:
        save_series(au10y, "au10y_daily.csv", "au10y")
        results["au10y"] = True
    else:
        print("  [FAILED] Australia 10Y daily — manual download required")
        results["au10y"] = False

    # ── Summary ───────────────────────────────────────────────────────────────
    print(f"\n{'='*65}")
    print("SUMMARY")
    print(f"{'='*65}")
    passed = sum(results.values())
    print(f"  Collected: {passed}/{len(results)} series")
    for name, ok in results.items():
        print(f"    {name:<12}: {'✓' if ok else '✗ FAILED — see manual instructions below'}")

    failed = [k for k, v in results.items() if not v]
    if failed:
        print(f"""
  MANUAL DOWNLOAD INSTRUCTIONS FOR FAILED SERIES
  ─────────────────────────────────────────────────

  UK (Bank of England):
    1. Go to: https://www.bankofengland.co.uk/statistics/yield-curves
    2. Download "Nominal" spot curve CSV
    3. Look for columns containing 2-year and 10-year spot rates
    4. Save date + 2Y column as: data/raw/rates/uk2y_daily.csv
    5. Save date + 10Y column as: data/raw/rates/uk10y_daily.csv
    Format needed: date (YYYY-MM-DD), value (decimal percent e.g. 4.25)

  Japan (Ministry of Finance):
    1. Go to: https://www.mof.go.jp/english/jgbs/reference/interest_rate/
    2. Download the historical CSV file (jgbcm_all.csv)
    3. Extract date + 2year column → data/raw/rates/jp2y_daily.csv
    4. Extract date + 10year column → data/raw/rates/jp10y_daily.csv

  Australia (RBA):
    1. Go to: https://www.rba.gov.au/statistics/tables/
    2. Find Table F2 "Capital Market Yields — Government Bonds — Daily"
    3. Download the spreadsheet
    4. Extract date + 2Y yield → data/raw/rates/au2y_daily.csv
    5. Extract date + 10Y yield → data/raw/rates/au10y_daily.csv

  Once files are saved, run:
    python src/ingestion/update_multi_pair_signals.py
""")
    else:
        print(f"""
  All series collected successfully.

  Next step: update multi_pair_signals_v1.py to use daily files:
    Change config filenames from 'uk2y.csv' to 'uk2y_daily.csv' etc.
  Or run: python src/ingestion/update_multi_pair_signals.py
""")


if __name__ == "__main__":
    main()