"""LSEG Workspace data loader — reads bond data from downloaded Excel/CSV files."""
from __future__ import annotations

import logging
from pathlib import Path
from typing import Optional

import pandas as pd

logger = logging.getLogger(__name__)

# Maps each normalized field → possible LSEG column header spellings
_COL_MAP: dict[str, list[str]] = {
    "isin":              ["ISIN", "Identifier", "Bond ID", "CUSIP", "RIC"],
    "name":              ["Instrument Name", "Bond Name", "Name", "Description", "Security Name", "Bond"],
    "issuer":            ["Issuer Name", "Issuer", "Company Name", "Issuer Short Name"],
    "coupon":            ["Coupon", "Coupon Rate", "Coupon (%)", "Coupon Rate (%)"],
    "maturity_date":     ["Maturity Date", "Maturity", "Maturity (Date)", "Mat Date"],
    "ytm":               ["YTM", "Yield to Maturity", "Yield", "YTM (%)", "Yield to Maturity (%)"],
    "ytw":               ["YTW", "Yield to Worst", "YTW (%)"],
    "oas":               ["OAS", "OAS Spread", "Option Adjusted Spread", "OAS (bps)", "OAS Spread (bps)"],
    "z_spread":          ["Z-Spread", "Z Spread", "ZSpread", "Z Spread (bps)", "Z-Spread (bps)"],
    "g_spread":          ["G-Spread", "G Spread", "GSpread", "G Spread (bps)"],
    "modified_duration": ["Modified Duration", "Mod Duration", "Duration", "Mod. Duration", "ModDuration"],
    "convexity":         ["Convexity"],
    "rating_moodys":     ["Moody's Rating", "Moody's", "Moodys Rating", "Moodys", "Moody's Long-Term"],
    "rating_sp":         ["S&P Rating", "S&P", "SP Rating", "S and P Rating", "Standard & Poor's", "S&P Long-Term"],
    "rating_fitch":      ["Fitch Rating", "Fitch", "Fitch Long-Term"],
    "price":             ["Price", "Bid Price", "Clean Price", "Mid Price", "Last Price", "Ask Price"],
    "country":           ["Country", "Country of Risk", "Country of Domicile", "Issuer Country"],
    "sector":            ["Sector", "Industry", "Industry Sector", "GICS Sector", "Bloomberg Sector"],
    "currency":          ["Currency", "CCY", "Ccy"],
    "issue_size":        ["Issue Size", "Amount Outstanding", "Face Amount", "Notional", "Amt Outstanding"],
}

_NULL_STRINGS = {"nan", "none", "n/a", "n/a.", "-", "#n/a", "#value!", ""}


def _find_col(df: pd.DataFrame, aliases: list[str]) -> Optional[str]:
    cols_lower = {c.lower(): c for c in df.columns}
    for alias in aliases:
        if alias in df.columns:
            return alias
        if alias.lower() in cols_lower:
            return cols_lower[alias.lower()]
    return None


def _normalize_df(df: pd.DataFrame) -> pd.DataFrame:
    rename: dict[str, str] = {}
    for norm_key, aliases in _COL_MAP.items():
        found = _find_col(df, aliases)
        if found and found != norm_key:
            rename[found] = norm_key
    df = df.rename(columns=rename)
    for norm_key in _COL_MAP:
        if norm_key not in df.columns:
            df[norm_key] = None
    return df


def _to_float(val) -> Optional[float]:
    if val is None:
        return None
    if isinstance(val, float) and pd.isna(val):
        return None
    try:
        return float(str(val).replace(",", "").replace("%", "").strip())
    except (ValueError, TypeError):
        return None


def _to_str(val) -> Optional[str]:
    if val is None:
        return None
    s = str(val).strip()
    return None if s.lower() in _NULL_STRINGS else s


def _read_file(path: Path) -> pd.DataFrame:
    suffix = path.suffix.lower()
    if suffix in {".xlsx", ".xls"}:
        return pd.read_excel(path, engine="openpyxl")
    if suffix == ".csv":
        return pd.read_csv(path)
    raise ValueError(f"Unsupported file type: {suffix}")


class LsegDataLoader:
    """Reads and normalizes bond data from LSEG Workspace exported files."""

    def __init__(self, data_path: str):
        self.data_path = Path(data_path)

    def _iter_files(self):
        if not self.data_path.exists():
            logger.warning("LSEG data path does not exist: %s", self.data_path)
            return
        for p in sorted(self.data_path.iterdir()):
            if p.suffix.lower() in {".xlsx", ".xls", ".csv"} and not p.name.startswith("~"):
                yield p

    def load_bonds(self) -> list[dict]:
        """Return a list of normalized bond dicts from all files in data_path."""
        frames: list[pd.DataFrame] = []
        for path in self._iter_files():
            try:
                df = _read_file(path)
                df = _normalize_df(df)
                frames.append(df)
                logger.info("LSEG: loaded %d rows from %s", len(df), path.name)
            except Exception as exc:
                logger.warning("LSEG: skipping %s — %s", path.name, exc)

        if not frames:
            return []

        combined = pd.concat(frames, ignore_index=True)
        combined = combined[combined["isin"].notna()]
        combined = combined.drop_duplicates(subset=["isin"], keep="last")

        records = []
        for _, row in combined.iterrows():
            records.append({
                "isin":              _to_str(row["isin"]),
                "name":              _to_str(row["name"]),
                "issuer":            _to_str(row["issuer"]),
                "coupon":            _to_float(row["coupon"]),
                "maturity_date":     _to_str(row["maturity_date"]),
                "ytm":               _to_float(row["ytm"]),
                "ytw":               _to_float(row["ytw"]),
                "oas":               _to_float(row["oas"]),
                "z_spread":          _to_float(row["z_spread"]),
                "g_spread":          _to_float(row["g_spread"]),
                "modified_duration": _to_float(row["modified_duration"]),
                "convexity":         _to_float(row["convexity"]),
                "rating_moodys":     _to_str(row["rating_moodys"]),
                "rating_sp":         _to_str(row["rating_sp"]),
                "rating_fitch":      _to_str(row["rating_fitch"]),
                "price":             _to_float(row["price"]),
                "country":           _to_str(row["country"]),
                "sector":            _to_str(row["sector"]),
                "currency":          _to_str(row["currency"]),
                "issue_size":        _to_float(row["issue_size"]),
            })

        logger.info("LSEG: %d unique bonds loaded", len(records))
        return records