"""
auto_rates_loader.py
=====================
Automated rate data fetcher for EURUSD + USDJPY dual macro model.

Source priority (FinanceFlow first — real-time, authenticated):
  Primary:   FinanceFlow Real-Time Government Bond API
  Fallback:  FRED (St. Louis Fed) / Bundesbank / MoF Japan CSV

Series fetched:
  us2y.csv   — US 2Y Treasury yield
  us10y.csv  — US 10Y Treasury yield
  de2y.csv   — DE 2Y Bund yield
  de10y.csv  — DE 10Y Bund yield
  jp2y.csv   — JP 2Y JGB yield

FinanceFlow bond API:
  Endpoint: /real-time-government-bond
  Auth: api_key parameter
  Countries: United States, Germany, Japan
  Maturity mapping: 2Y, 10Y

Run standalone:
  python src/ingestion/auto_rates_loader.py
"""

from __future__ import annotations

import os
import ssl
import csv as _csv
import urllib.request
import urllib.parse
from io import StringIO
from pathlib import Path
from typing import Optional

import pandas as pd
import requests

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

# ── API credentials ───────────────────────────────────────────────────────────
FF_API_KEY   = os.getenv("FINANCEFLOW_API_KEY",
               "2d9e4c37c81f1b4c970d7b1f8d7d382ff450312cd0adf57117f28d5708e6e38e")
FF_BASE_URL  = "https://financeflowapi.com/api/v1"
FRED_API_KEY = os.getenv("FRED_API_KEY", "b4e39ea4507e1ceca1a389cfacee8a00")
FRED_BASE    = "https://api.stlouisfed.org/fred/series/observations"

BUNDESBANK_DE2Y_CSV = (
    "https://api.statistiken.bundesbank.de/rest/download/BBSSY/"
    "D.REN.EUR.A610.000000WT0202.A?format=csv&lang=en"
)
BUNDESBANK_DE10Y_CSV = (
    "https://api.statistiken.bundesbank.de/rest/download/BBSSY/"
    "D.REN.EUR.A630.000000WT1010.A?format=csv&lang=en"
)

# FinanceFlow country + maturity → CSV filename + value column
FF_BOND_MAP = {
    # (country_name, maturity_label): (csv_file, col_name, history_days)
    # Country names must match /bonds-catalog output: Title_Case with underscore
    # Confirmed from catalog: United_States, Germany, Japan all support 2y + 10y
    ("United States", "2y"):  ("us2y.csv",  "us2y",  365 * 36),
    ("United States", "10y"): ("us10y.csv", "us10y", 365 * 36),
    ("Germany",       "2y"):  ("de2y.csv",  "de2y",  365 * 22),
    ("Germany",       "10y"): ("de10y.csv", "de10y", 365 * 22),
    ("Japan",         "2y"):  ("jp2y.csv",  "jp2y",  365 * 52),
}

# FRED series IDs for fallback
FRED_SERIES = {
    "us2y.csv":  ("DGS2",  "us2y"),
    "us10y.csv": ("DGS10", "us10y"),
}

# ECB fallback URLs for DE rates
ECB_DE2Y_URL  = (
    "https://data-api.ecb.europa.eu/service/data/YC/B.U2.EUR.4F.G_N_A.SV_C_YM.SR_2Y"
    "?format=csvdata&startPeriod=2004-01-01"
)
ECB_DE10Y_URL = (
    "https://data-api.ecb.europa.eu/service/data/YC/B.U2.EUR.4F.G_N_A.SV_C_YM.SR_10Y"
    "?format=csvdata&startPeriod=2004-01-01"
)

MOF_JAPAN_URL = (
    "https://www.mof.go.jp/english/policy/jgbs/reference/interest_rate/"
    "historical/jgbcme_all.csv"
)


# ═══════════════════════════════════════════════════════════════════════════════
# HELPERS
# ═══════════════════════════════════════════════════════════════════════════════

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


def _clean(df: pd.DataFrame, col: str) -> pd.DataFrame:
    df = df.copy()
    df.columns = [c.strip().lower() for c in df.columns]
    for old, new in [("observation_date","date"),("time_period","date"),
                     ("obs_value","value"),("time_period","date")]:
        if old in df.columns and new not in df.columns:
            df.rename(columns={old: new}, inplace=True)
    if "date" not in df.columns or "value" not in df.columns:
        if len(df.columns) >= 2:
            df = df.iloc[:, :2].copy(); df.columns = ["date","value"]
        else:
            raise ValueError(f"Cannot find date/value columns: {df.columns.tolist()}")
    df["date"]  = pd.to_datetime(df["date"], errors="coerce", utc=False)
    df["value"] = pd.to_numeric(df["value"], errors="coerce")
    df = (df.dropna(subset=["date","value"])
            .drop_duplicates("date")
            .sort_values("date")
            .reset_index(drop=True))
    df["date"] = df["date"].dt.strftime("%Y-%m-%d")
    return df.rename(columns={"value": col})[["date", col]]


def _merge_save(existing_path: Path, fresh: pd.DataFrame, col: str) -> pd.DataFrame:
    """Merge fresh data into existing CSV, deduplicate, save."""
    if existing_path.exists():
        old = pd.read_csv(existing_path)
        old.columns = [c.strip().lower() for c in old.columns]
        if len(old.columns) >= 2:
            old = old.iloc[:, :2].copy(); old.columns = ["date", col]
        old["date"] = pd.to_datetime(old["date"], errors="coerce")
        old[col]    = pd.to_numeric(old[col], errors="coerce")
        combined = pd.concat([old, fresh], ignore_index=True)
    else:
        combined = fresh.copy()

    # Force date to datetime — prevents str vs Timestamp comparison errors
    combined["date"] = pd.to_datetime(combined["date"], errors="coerce")
    combined[col]    = pd.to_numeric(combined[col], errors="coerce")
    combined = (combined.dropna(subset=["date", col])
                        .drop_duplicates("date")
                        .sort_values("date")
                        .reset_index(drop=True))
    combined["date"] = combined["date"].dt.strftime("%Y-%m-%d")
    _save(combined, existing_path.name)
    return combined


def _ssl_ctx():
    ctx = ssl.create_default_context()
    ctx.check_hostname = False
    ctx.verify_mode    = ssl.CERT_NONE
    return ctx


# ═══════════════════════════════════════════════════════════════════════════════
# PRIMARY — FINANCEFLOW REAL-TIME GOVERNMENT BOND
# ═══════════════════════════════════════════════════════════════════════════════

def fetch_financeflow_bond(country: str, maturity: str, col: str,
                           days_history: int = 365) -> pd.DataFrame:
    """
    Fetch government bond yield history from FinanceFlow.

    Correct endpoints (from official docs):
      Spot:    GET /bonds-spot?country=united_states&type=2y
      History: GET /bonds-history?country=united_states&type=2y
                                  &frequency=day&date_from=...&date_to=...

    Country format: lowercase underscore (e.g. "united_states", "germany", "japan")
    Maturity type:  lowercase (e.g. "2y", "10y")
    Spot response:  data[0].bond_yield
    History response: data[].yield, data[].date

    Returns DataFrame with columns [date, col].
    """
    from datetime import datetime, timedelta

    # Country name → API format (lowercase, spaces to underscores)
    # API expects Title_Case with underscores: 'United_States', 'Germany', 'Japan'
    country_api  = "_".join(w.capitalize() for w in country.split())
    maturity_api = maturity.lower()  # "2y" or "10y"

    # Cap end_date at yesterday — FinanceFlow rejects current/future dates
    # Cap window at 99 days — API enforces max 100 days per request
    # Full history lives in the existing CSV; we only fetch recent data to top it up
    end_date   = (datetime.now() - timedelta(days=1)).strftime("%Y-%m-%d")
    window     = min(days_history, 99)
    start_date = (datetime.now() - timedelta(days=window)).strftime("%Y-%m-%d")

    # ── Try /bonds-history first (gives full date range) ─────────────────────
    url    = f"{FF_BASE_URL}/bonds-history"
    params = {
        "api_key":   FF_API_KEY,
        "country":   country_api,
        "type":      maturity_api,
        "frequency": "day",
        "date_from": start_date,
        "date_to":   end_date,
    }

    r = requests.get(url, params=params, timeout=30)

    if r.status_code == 403:
        raise ValueError("FinanceFlow 403 — check API key/subscription")
    if r.status_code == 404:
        raise ValueError(f"FinanceFlow 404 — endpoint not found: {url}")
    r.raise_for_status()

    payload = r.json()
    if not payload.get("success", True):
        raise ValueError(f"FinanceFlow error: {payload.get('message', 'unknown')}")

    records = payload.get("data", [])
    if not records:
        raise ValueError(
            f"FinanceFlow: no history data for {country_api} {maturity_api}")

    # History response: [{"date": "2025-01-31", "bond_type": "2y", "yield": 4.541}, ...]
    df = pd.DataFrame(records)
    df.columns = [c.lower().strip() for c in df.columns]

    # Find yield value — field is "yield" in history response
    yield_col = next((c for c in df.columns
                      if c in ("yield", "bond_yield", "value", "rate")), None)
    date_col  = next((c for c in df.columns if "date" in c), None)

    if not yield_col or not date_col:
        raise ValueError(
            f"FinanceFlow: unexpected columns {df.columns.tolist()} "
            f"for {country_api} {maturity_api}")

    result = df[[date_col, yield_col]].copy()
    result.columns = ["date", "value"]
    return _clean(result, col)


def fetch_financeflow_spot(country: str, maturity: str, col: str) -> pd.DataFrame:
    """
    Fetch single real-time bond yield from /bonds-spot.
    Returns one-row DataFrame with today's date and current yield.
    Used to append today's value when history has a lag.
    """
    from datetime import datetime
    # API expects Title_Case with underscores: 'United_States', 'Germany', 'Japan'
    country_api  = "_".join(w.capitalize() for w in country.split())
    maturity_api = maturity.lower()
    url    = f"{FF_BASE_URL}/bonds-spot"
    params = {"api_key": FF_API_KEY, "country": country_api, "type": maturity_api}
    r = requests.get(url, params=params, timeout=20)
    if r.status_code != 200:
        raise ValueError(f"FinanceFlow spot {r.status_code}")
    payload = r.json()
    records = payload.get("data", [])
    if not records:
        raise ValueError("FinanceFlow spot: no data")
    rec   = records[0]
    yield_val = rec.get("bond_yield", rec.get("yield", rec.get("value")))
    today = datetime.now().strftime("%Y-%m-%d")
    df = pd.DataFrame([{"date": today, "value": float(yield_val)}])
    return _clean(df, col)


def fetch_all_financeflow() -> dict[str, pd.DataFrame]:
    """
    Fetch all required rate series from FinanceFlow.
    Uses /bonds-history for full date range, then appends /bonds-spot
    for today's value (history may lag by 1 day).
    Returns {filename: (DataFrame, col)} for successful fetches.
    """
    results = {}
    for (country, maturity), (fname, col, days) in FF_BOND_MAP.items():
        try:
            df = fetch_financeflow_bond(country, maturity, col, days_history=days)
            # Append today's spot price to ensure no 1-day lag
            try:
                spot = fetch_financeflow_spot(country, maturity, col)
                df = pd.concat([df, spot], ignore_index=True)
                df["date"] = pd.to_datetime(df["date"], errors="coerce")
                df[col]    = pd.to_numeric(df[col], errors="coerce")
                df = (df.dropna(subset=["date", col])
                        .drop_duplicates("date")
                        .sort_values("date")
                        .reset_index(drop=True))
                df["date"] = df["date"].dt.strftime("%Y-%m-%d")
            except Exception:
                pass  # spot is best-effort only
            results[fname] = (df, col)
            print(f"  FinanceFlow {country} {maturity}: "
                  f"{len(df)} rows → {df['date'].max()}")
        except Exception as e:
            print(f"  FinanceFlow {country} {maturity} failed: {e}")
    return results


# ═══════════════════════════════════════════════════════════════════════════════
# FALLBACK 1 — FRED (US RATES)
# ═══════════════════════════════════════════════════════════════════════════════

def fetch_fred_series(series_id: str, col: str,
                      observation_start: str = "1990-01-01") -> pd.DataFrame:
    params = {
        "series_id":         series_id,
        "api_key":           FRED_API_KEY,
        "file_type":         "json",
        "observation_start": observation_start,
    }
    r = requests.get(FRED_BASE, params=params, timeout=30)
    r.raise_for_status()
    obs = r.json().get("observations", [])
    if not obs:
        raise ValueError(f"No FRED observations for {series_id}")
    raw = pd.DataFrame(obs)[["date", "value"]]
    return _clean(raw, col)


# ═══════════════════════════════════════════════════════════════════════════════
# FALLBACK 2 — BUNDESBANK / ECB (DE RATES)
# ═══════════════════════════════════════════════════════════════════════════════

def fetch_bundesbank_series(csv_url: str, col: str) -> pd.DataFrame:
    headers = {"Accept": "text/csv"}
    r = requests.get(csv_url, headers=headers, timeout=60)
    r.raise_for_status()
    lines = r.text.splitlines()
    header_idx = next(
        (i for i, l in enumerate(lines)
         if ("date" in l.lower() or "time_period" in l.lower()) and
            ("value" in l.lower() or "obs_value" in l.lower() or ";" in l)),
        None
    )
    sample = "\n".join(lines[header_idx:]) if header_idx is not None else r.text
    try:
        raw = pd.read_csv(StringIO(sample), sep=None, engine="python")
    except Exception:
        raw = pd.read_csv(StringIO(sample))
    return _clean(raw, col)


def fetch_ecb_series(url: str, col: str) -> pd.DataFrame:
    """ECB SDMX CSV fallback for DE rates."""
    r = requests.get(url, timeout=30,
                     headers={"Accept": "text/csv"})
    r.raise_for_status()
    lines  = [l for l in r.text.splitlines() if l.strip()]
    header = next((i for i, l in enumerate(lines)
                   if "time_period" in l.lower() or "date" in l.lower()), 0)
    raw    = pd.read_csv(StringIO("\n".join(lines[header:])))
    raw.columns = [c.strip().lower() for c in raw.columns]
    tc = next((c for c in raw.columns if "time" in c or "date" in c), None)
    vc = next((c for c in raw.columns if "obs_value" in c or "value" in c), None)
    if not tc or not vc:
        raise ValueError(f"ECB: can't find date/value cols: {raw.columns.tolist()}")
    raw = raw[[tc, vc]].rename(columns={tc: "date", vc: "value"})
    return _clean(raw, col)


# ═══════════════════════════════════════════════════════════════════════════════
# FALLBACK 3 — MOF JAPAN (JP2Y)
# ═══════════════════════════════════════════════════════════════════════════════

def fetch_mof_jp2y() -> pd.DataFrame:
    """MoF Japan JGB benchmark rates — robust fetcher."""
    ctx = _ssl_ctx()
    req = urllib.request.Request(
        MOF_JAPAN_URL,
        headers={"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64)"}
    )
    with urllib.request.urlopen(req, timeout=45, context=ctx) as resp:
        raw_bytes = resp.read()
    for enc in ("utf-8-sig", "utf-8", "shift-jis", "latin-1"):
        try:
            text = raw_bytes.decode(enc); break
        except (UnicodeDecodeError, LookupError):
            continue

    lines = [l for l in text.splitlines() if l.strip() and not l.startswith("#")]
    if not lines:
        raise ValueError("MoF Japan: empty response")

    # Find 2Y column
    header = lines[0].lstrip("\ufeff")
    cols   = [c.strip().strip('"') for c in header.split(",")]
    col2y  = next((i for i, c in enumerate(cols) if c in ("2Y","2y")), 5)

    rows = []
    for line in lines[1:]:
        parts = [p.strip().strip('"') for p in line.split(",")]
        if len(parts) <= col2y:
            continue
        try:
            for fmt in ("%Y/%m/%d", "%Y-%m-%d"):
                try:
                    from datetime import datetime
                    dt = datetime.strptime(parts[0], fmt)
                    break
                except ValueError:
                    continue
            val = float(parts[col2y])
            rows.append({"date": dt.strftime("%Y-%m-%d"), "jp2y": val})
        except (ValueError, IndexError):
            continue

    df = pd.DataFrame(rows).drop_duplicates("date").sort_values("date")
    if len(df) < 100:
        raise ValueError(f"MoF Japan: too few rows ({len(df)})")
    return df


# ═══════════════════════════════════════════════════════════════════════════════
# MAIN UPDATE FUNCTION
# ═══════════════════════════════════════════════════════════════════════════════

def update_all_rates() -> bool:
    """
    Update all rate CSVs using FinanceFlow as primary, fallbacks as backup.

    Priority per series:
      us2y / us10y:  FinanceFlow → FRED
      de2y / de10y:  FinanceFlow → Bundesbank → ECB
      jp2y:          FinanceFlow → MoF Japan

    Returns True if all critical rates updated, False if any failed.
    """
    print("Updating rate data...")
    updated   = {}
    failed    = []

    # ── Step 1: Try FinanceFlow for everything ────────────────────────────────
    print("  [Primary] FinanceFlow Real-Time Government Bond API...")
    ff_results = fetch_all_financeflow()
    for fname, (df, col) in ff_results.items():
        path = RATES_PATH / fname
        _merge_save(path, df, col)
        updated[fname] = True

    # ── Step 2: Fallback for anything FinanceFlow missed ─────────────────────
    # US rates → FRED
    for fname, (series_id, col) in FRED_SERIES.items():
        if fname in updated:
            continue
        print(f"  [Fallback FRED] {fname}...")
        try:
            df = fetch_fred_series(series_id, col)
            _merge_save(RATES_PATH / fname, df, col)
            updated[fname] = True
        except Exception as e:
            print(f"    FRED failed: {e}")
            failed.append(fname)

    # DE rates → Bundesbank → ECB
    de_fallbacks = [
        ("de2y.csv",  "de2y",  BUNDESBANK_DE2Y_CSV,  ECB_DE2Y_URL),
        ("de10y.csv", "de10y", BUNDESBANK_DE10Y_CSV, ECB_DE10Y_URL),
    ]
    for fname, col, bb_url, ecb_url in de_fallbacks:
        if fname in updated:
            continue
        print(f"  [Fallback Bundesbank] {fname}...")
        try:
            df = fetch_bundesbank_series(bb_url, col)
            _merge_save(RATES_PATH / fname, df, col)
            updated[fname] = True
            continue
        except Exception as e:
            print(f"    Bundesbank failed ({str(e)[:60]}) — trying ECB...")
        try:
            df = fetch_ecb_series(ecb_url, col)
            _merge_save(RATES_PATH / fname, df, col)
            updated[fname] = True
            print(f"    Source: ECB fallback ({len(df)} rows)")
        except Exception as e2:
            print(f"    ECB also failed: {e2}")
            failed.append(fname)

    # JP2Y → MoF Japan
    if "jp2y.csv" not in updated:
        print("  [Fallback MoF Japan] jp2y.csv...")
        try:
            df = fetch_mof_jp2y()
            _merge_save(RATES_PATH / "jp2y.csv", df, "jp2y")
            updated["jp2y.csv"] = True
        except Exception as e:
            print(f"    MoF Japan failed: {e}")
            failed.append("jp2y.csv")

    # ── Summary ───────────────────────────────────────────────────────────────
    print(f"\n  Updated: {len(updated)}/5 series"
          + (f" | Failed: {failed}" if failed else " | All OK ✅"))

    if failed:
        print("  NOTE: Failed series will use last known values from CSV.")
        print("  Rate data changes slowly — 1-2 day lag is acceptable.")

    return len(failed) == 0


# ── Compatibility shims for live_signal_monitor_v2.py ─────────────────────────
# The monitor calls update_local_csv() directly — keep these working.

def update_local_csv(local_filename: str, fetch_fn=None, *,
                     value_col_name: str, **fetch_kwargs) -> pd.DataFrame:
    """
    Compatibility shim — called by live_signal_monitor_v2 with old-style args.
    Ignores fetch_fn and fetch_kwargs entirely; just calls update_all_rates()
    which handles FinanceFlow primary + all fallbacks correctly.
    Returns the saved CSV as a DataFrame.
    """
    path = RATES_PATH / local_filename
    try:
        update_all_rates()
    except Exception as e:
        print(f"  update_all_rates failed: {e} — using cached CSV")
    if path.exists():
        return pd.read_csv(path)
    raise FileNotFoundError(f"Rate file not found after update: {path}")


BUNDESBANK_DE2Y_CSV  = BUNDESBANK_DE2Y_CSV
BUNDESBANK_DE10Y_CSV = BUNDESBANK_DE10Y_CSV


def fetch_bundesbank_series_compat(csv_url: str,
                                    value_col_name: str,
                                    output_name: str = "") -> pd.DataFrame:
    """Bundesbank with ECB fallback — used by compatibility shim."""
    try:
        return fetch_bundesbank_series(csv_url, value_col_name)
    except Exception as e:
        print(f"  Bundesbank failed ({str(e)[:60]}) — trying ECB fallback...")
        ecb_url = ECB_DE2Y_URL if "de2y" in value_col_name else ECB_DE10Y_URL
        df = fetch_ecb_series(ecb_url, value_col_name)
        print(f"  Source: ECB fallback ({len(df)} rows)")
        return df


# Note: fetch_bundesbank_series_compat is available for callers that need
# ECB fallback. The original fetch_bundesbank_series is NOT overwritten here
# to avoid infinite recursion (compat calls original internally).


if __name__ == "__main__":
    update_all_rates()