"""
fetch_jp2y_robust.py
=====================
Robust JP2Y (Japan 2-year yield) data fetcher from Ministry of Finance Japan.

BUGS IDENTIFIED FROM EURUSD EXPERIENCE — ALL FIXED HERE:
=========================================================
EU Bug 1: SSL certificate verification fails on Windows Python 3.14
  Fix: Detect Windows + use ssl.create_default_context() with verify disabled.
       Also try requests library if available (verify=False).

EU Bug 2: Column index hardcoded — breaks if MoF changes CSV structure
  Fix: Read header row first, find '2Y' column by name. Fallback to index 5.

EU Bug 3: Date format YYYY/MM/DD vs YYYY-MM-DD causes parse failures
  Fix: Try multiple date formats. MoF uses YYYY/MM/DD specifically.

EU Bug 4: Empty rows / missing values mid-CSV cause crashes
  Fix: try/except per row, skip on parse error, don't abort entire download.

EU Bug 5: Network timeout with no retry
  Fix: 3 attempts with exponential backoff (5s, 10s, 20s).

EU Bug 6: Stale cached data not detected (DE2Y had this issue)
  Fix: Check last row date — if > 5 business days old, log warning + refetch.

EU Bug 7: BOM characters in CSV header cause column name mismatch
  Fix: Strip BOM with encoding='utf-8-sig' or explicit .lstrip('\\ufeff').

EU Bug 8: Server returns HTML error page instead of CSV (MoF outages)
  Fix: Validate first line starts with 'Reference Date' or a date pattern.

EU Bug 9: Duplicate dates in output (re-running appends existing data)
  Fix: Always write fresh file from full download, deduplicate by date.

EU Bug 10: VPS can't reach external URLs (firewall / proxy)
  Fix: Try two mirror approaches, fallback gracefully with clear error message.

DATA SOURCE:
  Ministry of Finance Japan — JGB Benchmark Rates (daily, free, no API key)
  URL: https://www.mof.go.jp/english/policy/jgbs/reference/interest_rate/historical/jgbcme_all.csv
  Format: CSV, header = "Reference Date,0.1Y,0.25Y,0.5Y,1Y,2Y,3Y,..."
  Update: T+1 trading day (after Tokyo close ~18:30 JST = 09:30 UTC)

Run standalone:
  python src/ingestion/fetch_jp2y_robust.py

Integrate into auto_rates_loader.py:
  from ingestion.fetch_jp2y_robust import fetch_jp2y
  fetch_jp2y(output_path)
"""

import ssl
import sys
import time
import logging
import urllib.request
from pathlib import Path
from datetime import datetime, timedelta

import pandas as pd
import numpy as np

log = logging.getLogger(__name__)

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

# ── Column detection ──────────────────────────────────────────────────────────
# MoF CSV header: "Reference Date,0.1Y,0.25Y,0.5Y,1Y,2Y,3Y,4Y,5Y,..."
# We want 2Y. Map known labels → column index as fallback.
KNOWN_2Y_LABELS  = ["2Y", "2y", "2.0Y", "2yr"]
FALLBACK_2Y_IDX  = 5   # 0=date, 1=0.1Y, 2=0.25Y, 3=0.5Y, 4=1Y, 5=2Y


def _make_ssl_ctx():
    """Create SSL context that bypasses verification (needed on Windows Python 3.14)."""
    ctx = ssl.create_default_context()
    ctx.check_hostname = False
    ctx.verify_mode    = ssl.CERT_NONE
    return ctx


def _download_raw(url: str, timeout: int = 30) -> str:
    """
    Download URL to string. Tries:
      1. urllib with SSL bypass (Windows-safe)
      2. requests with verify=False (if installed)
    Returns raw text or raises RuntimeError.
    """
    last_err = None

    # Attempt 1: urllib + SSL bypass
    try:
        ctx = _make_ssl_ctx()
        req = urllib.request.Request(
            url,
            headers={"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64)"}
        )
        with urllib.request.urlopen(req, timeout=timeout, context=ctx) as resp:
            raw = resp.read()
            # Try UTF-8 first, then Shift-JIS (MoF sometimes uses Shift-JIS)
            for enc in ("utf-8-sig", "utf-8", "shift-jis", "latin-1"):
                try:
                    return raw.decode(enc)
                except (UnicodeDecodeError, LookupError):
                    continue
            return raw.decode("utf-8", errors="replace")
    except Exception as e:
        last_err = e
        log.debug(f"urllib attempt failed: {e}")

    # Attempt 2: requests library (if available)
    try:
        import requests
        resp = requests.get(url, timeout=timeout, verify=False)
        resp.raise_for_status()
        return resp.text
    except ImportError:
        pass
    except Exception as e:
        last_err = e
        log.debug(f"requests attempt failed: {e}")

    raise RuntimeError(f"All download attempts failed for {url}: {last_err}")


def _validate_csv_content(text: str) -> bool:
    """
    Validate the downloaded text is actually a CSV and not an HTML error page.
    MoF returns HTML on maintenance/outage.
    """
    lines = [l for l in text.splitlines() if l.strip()]
    if not lines:
        return False
    first = lines[0].strip().lstrip("\ufeff")  # strip BOM
    # Valid CSV starts with 'Reference Date' or looks like a date (YYYY/MM/DD)
    if "Reference Date" in first:
        return True
    if len(lines) > 1:
        second = lines[1].strip()
        # Check second line looks like data: YYYY/MM/DD,...
        if len(second) >= 10 and second[:4].isdigit() and second[4] in ("/", "-"):
            return True
    # Reject HTML pages
    if first.lower().startswith("<!") or "<html" in first.lower():
        return False
    return True


def _parse_header_for_2y_col(header_line: str) -> int:
    """
    Parse the header row to find which column index contains 2Y yield.
    Returns column index (0-indexed).
    """
    # Strip BOM and whitespace
    header = header_line.lstrip("\ufeff").strip()
    cols   = [c.strip().strip('"') for c in header.split(",")]
    log.debug(f"CSV columns: {cols[:10]}")

    # Look for exact 2Y match
    for label in KNOWN_2Y_LABELS:
        if label in cols:
            idx = cols.index(label)
            log.info(f"Found 2Y column '{label}' at index {idx}")
            return idx

    # Look for partial match (e.g. "2Y" anywhere in column name)
    for i, col in enumerate(cols):
        if col.replace(" ", "") in KNOWN_2Y_LABELS:
            log.info(f"Found 2Y column '{col}' at index {i} (partial match)")
            return i

    log.warning(
        f"Could not find 2Y column in header: {cols[:8]}. "
        f"Falling back to index {FALLBACK_2Y_IDX}"
    )
    return FALLBACK_2Y_IDX


def _parse_date(date_str: str) -> datetime:
    """Parse MoF date string — tries YYYY/MM/DD and YYYY-MM-DD."""
    s = date_str.strip().strip('"')
    for fmt in ("%Y/%m/%d", "%Y-%m-%d", "%d/%m/%Y", "%m/%d/%Y"):
        try:
            return datetime.strptime(s, fmt)
        except ValueError:
            continue
    raise ValueError(f"Cannot parse date: {date_str!r}")


def _parse_csv(text: str) -> pd.DataFrame:
    """
    Parse MoF CSV text into clean jp2y DataFrame.
    Handles: BOM, missing values, header detection, column detection.
    """
    lines = [l for l in text.splitlines() if l.strip()]
    if not lines:
        raise ValueError("Empty CSV content")

    # Find header line — first line containing 'Reference Date' or 'Date'
    header_idx = 0
    for i, line in enumerate(lines[:5]):
        if "date" in line.lower() or "reference" in line.lower():
            header_idx = i
            break

    header_line = lines[header_idx]
    col_2y_idx  = _parse_header_for_2y_col(header_line)

    rows = []
    skipped = 0
    for line in lines[header_idx + 1:]:
        parts = [p.strip().strip('"') for p in line.split(",")]
        if len(parts) <= col_2y_idx:
            skipped += 1
            continue
        try:
            dt  = _parse_date(parts[0])
            val_str = parts[col_2y_idx].strip()
            if not val_str or val_str in ("-", "N/A", "n/a", ""):
                skipped += 1
                continue
            val = float(val_str)
            rows.append({"date": dt.strftime("%Y-%m-%d"), "jp2y": val})
        except (ValueError, IndexError):
            skipped += 1
            continue

    if skipped > 0:
        log.debug(f"Skipped {skipped} rows during parsing")

    df = pd.DataFrame(rows)
    df = df.drop_duplicates("date").sort_values("date").reset_index(drop=True)
    return df


def _check_staleness(df: pd.DataFrame) -> None:
    """Warn if data is stale (> 5 business days old)."""
    if df.empty:
        return
    last_date = pd.to_datetime(df["date"].iloc[-1])
    today     = pd.Timestamp.today().normalize()
    bdays     = np.busday_count(last_date.date(), today.date())
    if bdays > 5:
        log.warning(
            f"JP2Y data may be stale: last date is {last_date.date()} "
            f"({bdays} business days ago). "
            f"MoF Japan may be down or URL has changed."
        )
    else:
        log.info(f"JP2Y data is current: last date {last_date.date()} ({bdays} bdays ago)")


def fetch_jp2y(
    output_path: Path,
    max_retries: int = 3,
    retry_delay: float = 5.0,
) -> pd.DataFrame | None:
    """
    Download JP2Y from MoF Japan with full error handling and retry logic.

    Args:
        output_path: Where to save jp2y.csv
        max_retries: Number of download attempts
        retry_delay: Seconds between retries (doubles each attempt)

    Returns:
        DataFrame with columns [date, jp2y] or None on failure.
        If None, existing file is preserved (not overwritten).
    """
    output_path = Path(output_path)
    output_path.parent.mkdir(parents=True, exist_ok=True)

    delay = retry_delay
    last_err = None

    for attempt in range(1, max_retries + 1):
        try:
            log.info(f"JP2Y download attempt {attempt}/{max_retries}...")
            raw = _download_raw(MOF_URL, timeout=45)

            if not _validate_csv_content(raw):
                raise ValueError(
                    "Downloaded content does not look like MoF CSV "
                    "(possible HTML error page or server outage)"
                )

            df = _parse_csv(raw)

            if len(df) < 100:
                raise ValueError(
                    f"Too few rows ({len(df)}) — possible incomplete download"
                )

            _check_staleness(df)

            # Write output
            df.to_csv(output_path, index=False)
            log.info(
                f"JP2Y saved: {len(df):,} rows | "
                f"{df['date'].iloc[0]} → {df['date'].iloc[-1]} | "
                f"range: [{df['jp2y'].min():.3f}%, {df['jp2y'].max():.3f}%]"
            )
            return df

        except Exception as e:
            last_err = e
            log.warning(f"JP2Y attempt {attempt} failed: {e}")
            if attempt < max_retries:
                log.info(f"Retrying in {delay:.0f}s...")
                time.sleep(delay)
                delay *= 2  # exponential backoff

    # All attempts failed
    log.error(
        f"JP2Y download failed after {max_retries} attempts: {last_err}\n"
        f"  → Existing jp2y.csv preserved (if it exists)\n"
        f"  → Model will use last known rates (safe — rates change slowly)\n"
        f"  → Check: internet connectivity, MoF Japan site status\n"
        f"  → URL: {MOF_URL}"
    )
    return None


def update_jp2y_if_stale(output_path: Path, max_stale_days: int = 2) -> bool:
    """
    Only re-download if the existing file is missing or > max_stale_days old.
    Designed for daily scheduler — avoids unnecessary hits to MoF server.

    Returns True if data is current (either already was, or just updated).
    """
    output_path = Path(output_path)

    if output_path.exists():
        try:
            existing = pd.read_csv(output_path, parse_dates=["date"])
            if not existing.empty:
                last_date = pd.to_datetime(existing["date"].iloc[-1])
                today     = pd.Timestamp.today().normalize()
                bdays_old = np.busday_count(last_date.date(), today.date())

                if bdays_old <= max_stale_days:
                    log.info(
                        f"JP2Y already current ({last_date.date()}, "
                        f"{bdays_old} bdays old) — skipping download"
                    )
                    return True
                else:
                    log.info(
                        f"JP2Y is {bdays_old} bdays old — refreshing..."
                    )
        except Exception as e:
            log.warning(f"Could not read existing jp2y.csv: {e} — re-downloading")

    result = fetch_jp2y(output_path)
    return result is not None


def main():
    """Standalone run — download JP2Y and show diagnostics."""
    logging.basicConfig(
        level=logging.INFO,
        format="%(asctime)s [%(levelname)s] %(message)s",
    )

    base = Path(__file__).resolve().parents[2]
    out  = base / "data" / "raw" / "rates" / "jp2y.csv"

    print("="*60)
    print("JP2Y FETCHER — Ministry of Finance Japan")
    print(f"Output: {out}")
    print("="*60)

    df = fetch_jp2y(out, max_retries=3)

    if df is not None:
        print(f"\n  ✅ Downloaded {len(df):,} rows")
        print(f"  Date range: {df['date'].iloc[0]} → {df['date'].iloc[-1]}")
        print(f"  JP2Y range: {df['jp2y'].min():.3f}% → {df['jp2y'].max():.3f}%")
        print(f"  Latest:     {df['jp2y'].iloc[-1]:.3f}% on {df['date'].iloc[-1]}")
        print(f"\n  Sample (last 5 rows):")
        print(df.tail(5).to_string(index=False))
    else:
        print(f"\n  ❌ Download failed — check log for details")
        if out.exists():
            print(f"  Existing file preserved: {out}")
        sys.exit(1)


if __name__ == "__main__":
    main()