"""Maps raw Yahoo Finance SSR JSON → fin_equity row and raw holder tables.

Pure raw data extraction — no display formatting or view computation applied.
Formatting and percentages belong in SQL views (vw_*).

Holders Output per ticker:
  - fin_equity_major_holders : 4 raw metrics (All Insiders, Institutions, Float Inst, Inst Count)
  - fin_equity_inst_holders  : 10 raw rows (Holder, shares_held, report_dt, pct_held, value_usd, pct_change)
  - fin_equity_fund_holders  : 10 raw rows (Fund, shares_held, report_dt, pct_held, value_usd, pct_change)
"""

import logging
from datetime import date, datetime
from typing import Any, Dict, List, Optional, Tuple

logger = logging.getLogger(__name__)


# ── Helpers ───────────────────────────────────────────────────────────────────

def _safe(data: dict, *keys, default=None):
    """Safely traverse nested dict keys. Unwraps {"raw": x} automatically."""
    for key in keys:
        if not isinstance(data, dict):
            return default
        data = data.get(key, default)
        if data is None:
            return default
    if isinstance(data, dict) and "raw" in data:
        return data["raw"]
    return data


def _int_or_none(value) -> Optional[int]:
    if isinstance(value, dict):
        value = value.get("raw")
    try:
        return int(value)
    except (TypeError, ValueError):
        return None


def _float_or_none(value) -> Optional[float]:
    if isinstance(value, dict):
        value = value.get("raw")
    try:
        return float(value)
    except (TypeError, ValueError):
        return None


def _ts_to_date(ts) -> Optional[str]:
    """Convert Unix timestamp → ISO date string."""
    try:
        return datetime.utcfromtimestamp(int(ts)).strftime("%Y-%m-%d")
    except (TypeError, ValueError, OSError):
        return None


def _date_or_none(val: Any) -> Optional[str]:
    """Extract YYYY-MM-DD date string from dict or timestamp."""
    if isinstance(val, dict):
        fmt = val.get("fmt")
        if fmt and len(str(fmt).strip()) == 10 and str(fmt).count("-") == 2:
            return str(fmt).strip()
        raw = val.get("raw")
        if raw:
            return _ts_to_date(raw)
    elif val:
        return _ts_to_date(val)
    return None


def _holder_report_date(holder: dict) -> Optional[str]:
    """Extract report date as YYYY-MM-DD from holder dict."""
    rd = holder.get("reportDate", None)
    if isinstance(rd, dict):
        if rd.get("fmt"):
            # e.g. "2026-06-30" or unix ts
            fmt = str(rd["fmt"]).strip()
            if len(fmt) == 10 and fmt.count("-") == 2:
                return fmt
        raw = rd.get("raw")
        if raw:
            return _ts_to_date(raw)
    return None


# ── Main parser ───────────────────────────────────────────────────────────────

def parse_ticker(
    ticker: str, raw: Dict[str, Any]
) -> Tuple[Dict, List, List, List, Dict]:
    """Parse raw modules into pure data rows (no formatting applied)."""
    today = date.today().isoformat()

    price      = raw.get("price", {})
    quote_type = raw.get("quoteType", {})
    key_stats  = raw.get("defaultKeyStatistics", {})
    holders    = raw.get("majorHoldersBreakdown", {})
    inst_own   = raw.get("institutionOwnership", {})
    fund_own   = raw.get("fundOwnership", {})
    fin_data   = raw.get("financialData", {})
    profile    = raw.get("summaryProfile", {})
    summary    = raw.get("summaryDetail", {})
    fund_prof  = raw.get("fundProfile", {})
    asset_prof = raw.get("assetProfile", {})

    # ── Identifiers ───────────────────────────────────────────────────────────
    comp_id   = _safe(quote_type, "symbol") or ticker
    comp_name = _safe(price, "longName") or _safe(quote_type, "longName")

    # ── Exchange / type ───────────────────────────────────────────────────────
    q_type   = (
        _safe(price, "quoteType")
        or _safe(quote_type, "quoteType")
        or _safe(fund_prof, "legalType")
        or "EQUITY"
    )
    exchange = (
        _safe(price, "exchangeName")
        or _safe(quote_type, "exchange")
        or _safe(price, "exchange")
    )

    # ── Shares outstanding (in thousands for fin_equity INT column) ───────────
    shr_raw = _int_or_none(
        _safe(key_stats, "sharesOutstanding") or _safe(price, "sharesOutstanding")
    )
    shr_no = int(shr_raw // 1_000) if shr_raw else None

    # ── Major Holders Breakdown (raw decimal values) ──────────────────────────
    inst_pct       = _float_or_none(_safe(holders, "institutionsPercentHeld"))
    insider_pct    = _float_or_none(_safe(holders, "insidersPercentHeld"))
    inst_count     = _int_or_none(_safe(holders, "institutionsCount"))
    inst_float_pct = _float_or_none(_safe(holders, "institutionsFloatPercentHeld"))

    ind_pct = None
    if inst_pct is not None and insider_pct is not None:
        ind_pct = max(0.0, 1.0 - inst_pct - insider_pct)

    # ── Price & market data ───────────────────────────────────────────────────
    last_price = _float_or_none(
        _safe(price, "regularMarketPrice") or _safe(fin_data, "currentPrice")
    )
    last_pr_str = str(last_price) if last_price is not None else None

    market_cap   = _int_or_none(
        _safe(price, "marketCap")
        or _safe(summary, "totalAssets")
        or _safe(key_stats, "totalAssets")
    )
    currency     = _safe(price, "currency")
    market_state = _safe(price, "marketState", default="UNKNOWN")
    curr_stat    = "ACTIVE" if market_state in (
        "REGULAR", "PRE", "POST", "PREPRE", "POSTPOST"
    ) else "INACTIVE"

    # ── Valuation & fundamentals ──────────────────────────────────────────────
    pe_ratio      = _float_or_none(_safe(summary, "trailingPE"))
    fwd_pe        = _float_or_none(_safe(summary, "forwardPE"))
    beta          = _float_or_none(
        _safe(summary, "beta")
        or _safe(summary, "beta3Year")
        or _safe(key_stats, "beta3Year")
    )
    wk52_high     = _float_or_none(_safe(summary, "fiftyTwoWeekHigh"))
    wk52_low      = _float_or_none(_safe(summary, "fiftyTwoWeekLow"))
    div_yield     = _float_or_none(
        _safe(summary, "dividendYield")
        or _safe(summary, "yield")
        or _safe(key_stats, "yield")
    )
    eps_ttm       = _float_or_none(
        _safe(fin_data, "epsTrailingTwelveMonths") or _safe(key_stats, "trailingEps")
    )
    revenue       = _int_or_none(_safe(fin_data, "totalRevenue"))
    profit_margin = _float_or_none(_safe(fin_data, "profitMargins"))
    roe           = _float_or_none(_safe(fin_data, "returnOnEquity"))
    debt_to_eq    = _float_or_none(_safe(fin_data, "debtToEquity"))
    free_cf       = _int_or_none(_safe(fin_data, "freeCashflow"))
    analyst_rec   = _safe(fin_data, "recommendationKey")
    target_price  = _float_or_none(_safe(fin_data, "targetMeanPrice"))

    # ── Profile & Fiscal ───────────────────────────────────────────────────────
    sector        = _safe(profile, "sector") or _safe(fund_prof, "categoryName") or _safe(fund_prof, "family")
    industry      = _safe(profile, "industry") or _safe(fund_prof, "legalType")
    country       = _safe(profile, "country")
    website       = _safe(profile, "website") or _safe(asset_prof, "website")
    description   = _safe(profile, "longBusinessSummary") or _safe(asset_prof, "longBusinessSummary")
    employees     = _int_or_none(_safe(profile, "fullTimeEmployees"))
    fiscal_yr_end = _date_or_none(key_stats.get("lastFiscalYearEnd"))

    cusip = _safe(key_stats, "cusip") or _safe(quote_type, "cusip")
    isin  = _safe(key_stats, "isin")  or _safe(quote_type, "isin")

    # ── fin_equity row ────────────────────────────────────────────────────────
    fin_equity_row = {
        "comp_id":             comp_id,
        "comp_name":           comp_name,
        "ticker":              ticker,
        "equity_type":         q_type,
        "eq_exchange":         exchange,
        "sector":              sector,
        "industry":            industry,
        "country":             country,
        "currency":            currency,
        "cusip_no":            cusip,
        "isin_no":             isin,
        "website":             website,
        "description":         description,
        "fiscal_yr_end":       fiscal_yr_end,
        "employees":           employees,
        "shr_no":              shr_no,
        "market_cap":          market_cap,
        "last_pr":             last_pr_str,
        "curr_stat":           curr_stat,
        "pe_ratio":            pe_ratio,
        "fwd_pe":              fwd_pe,
        "beta":                beta,
        "wk52_high":           wk52_high,
        "wk52_low":            wk52_low,
        "div_yield":           div_yield,
        "eps_ttm":             eps_ttm,
        "revenue":             revenue,
        "profit_margin":       profit_margin,
        "roe":                 roe,
        "debt_to_eq":          debt_to_eq,
        "free_cashflow":       free_cf,
        "target_price":        target_price,
        "hld_inst_shr_pcnt":   str(inst_pct) if inst_pct is not None else None,
        "inst_count":          inst_count,
        "hld_inst_float_pcnt": str(inst_float_pct) if inst_float_pct is not None else None,
        "insider_pct":         insider_pct,
        "hld_ind_shr_pcnt":    str(ind_pct) if ind_pct is not None else None,
        "hld_run_dt":          today,
        "run_hld":             "Y",
        "chk_stat":            "N",
        "chk_st_dt":           today,
        "chk_lst_dt":          today,
    }

    # ── 1. Major Holders Breakdown (4 raw metric rows) ────────────────────────
    major_holders_rows = []
    major_configs = [
        ("insidersPercentHeld", "insiders_percent_held", "% of Shares Held by All Insider"),
        ("institutionsPercentHeld", "institutions_percent_held", "% of Shares Held by Institutions"),
        ("institutionsFloatPercentHeld", "institutions_float_percent_held", "% of Float Held by Institutions"),
        ("institutionsCount", "institutions_count", "Number of Institutions Holding Shares"),
    ]
    for yf_key, m_key, m_label in major_configs:
        v = holders.get(yf_key, {})
        val_raw = v.get("raw") if isinstance(v, dict) else v
        major_holders_rows.append({
            "ticker":       ticker,
            "metric_key":   m_key,
            "metric_label": m_label,
            "val_raw":      _float_or_none(val_raw),
            "run_dt":       today,
        })

    # ── 2. Top Institutional Holders (10 raw rows, no fmt, no rank) ───────────
    inst_list = inst_own.get("ownershipList", []) if isinstance(inst_own, dict) else []
    inst_holders_rows = []
    for h in inst_list[:10]:
        inst_holders_rows.append({
            "ticker":      ticker,
            "holder_name": h.get("organization") or "Unknown",
            "shares_held": _int_or_none(h.get("position")),
            "report_dt":   _holder_report_date(h),
            "pct_held":    _float_or_none(h.get("pctHeld")),
            "value_usd":   _int_or_none(h.get("value")),
            "pct_change":  _float_or_none(h.get("pctChange")),
            "run_dt":      today,
        })

    # ── 3. Top Mutual Fund Holders (10 raw rows, no fmt, no rank) ─────────────
    fund_list = fund_own.get("ownershipList", []) if isinstance(fund_own, dict) else []
    fund_holders_rows = []
    for h in fund_list[:10]:
        fund_holders_rows.append({
            "ticker":      ticker,
            "fund_name":   h.get("organization") or "Unknown",
            "shares_held": _int_or_none(h.get("position")),
            "report_dt":   _holder_report_date(h),
            "pct_held":    _float_or_none(h.get("pctHeld")),
            "value_usd":   _int_or_none(h.get("value")),
            "pct_change":  _float_or_none(h.get("pctChange")),
            "run_dt":      today,
        })

    # ── 4. Insider Roster (Directory of key executives & direct shares) ───────
    ins_holders_mod = raw.get("insiderHolders", {})
    ins_holders_list = ins_holders_mod.get("holders", []) if isinstance(ins_holders_mod, dict) else []
    insider_roster_rows = []
    for h in ins_holders_list:
        insider_roster_rows.append({
            "ticker":             ticker,
            "name":               h.get("name") or "Unknown",
            "relation":           h.get("relation"),
            "position_direct":    _int_or_none(h.get("positionDirect")),
            "latest_tx_date":     _date_or_none(h.get("latestTransDate")),
            "tx_desc":            h.get("transactionDescription"),
            "position_direct_dt": _date_or_none(h.get("positionDirectDate")),
            "run_dt":             today,
        })

    # ── 5. Insider Transactions (Reported last 2 years, ceiling max 200) ───────
    ins_tx_mod = raw.get("insiderTransactions", {})
    ins_tx_list = ins_tx_mod.get("transactions", []) if isinstance(ins_tx_mod, dict) else []
    insider_tx_rows = []
    for t in ins_tx_list[:200]:
        insider_tx_rows.append({
            "ticker":        ticker,
            "filer_name":    t.get("filerName") or "Unknown",
            "filer_role":    t.get("filerRelation"),
            "tx_date":       _date_or_none(t.get("startDate")),
            "tx_type":       t.get("transactionText") or "Non-Market Award / Vesting",
            "shares":        _int_or_none(t.get("shares")),
            "value_usd":     _int_or_none(t.get("value")),
            "ownership":     t.get("ownership"),
            "run_dt":        today,
        })

    # ── 6. 6-Month Insider Activity & Net Institutional Purchases Summary ─────
    net_act = raw.get("netSharePurchaseActivity", {})
    insider_summary_rows = []
    if isinstance(net_act, dict) and any([
        net_act.get("buyInfoCount"), net_act.get("sellInfoCount"), net_act.get("netInstSharesBuying")
    ]):
        insider_summary_rows.append({
            "ticker":               ticker,
            "period":               net_act.get("period") or "6m",
            "buy_count":            _int_or_none(net_act.get("buyInfoCount")),
            "buy_shares":           _int_or_none(net_act.get("buyInfoShares")),
            "buy_pct_insider":      _float_or_none(net_act.get("buyPercentInsiderShares")),
            "sell_count":           _int_or_none(net_act.get("sellInfoCount")),
            "sell_shares":          _int_or_none(net_act.get("sellInfoShares")),
            "sell_pct_insider":     _float_or_none(net_act.get("sellPercentInsiderShares")),
            "net_count":            _int_or_none(net_act.get("netInfoCount")),
            "net_shares":           _int_or_none(net_act.get("netInfoShares")),
            "net_pct_insider":      _float_or_none(net_act.get("netPercentInsiderShares")),
            "net_inst_shares":      _int_or_none(net_act.get("netInstSharesBuying")),
            "net_inst_pct":         _float_or_none(net_act.get("netInstBuyingPercent")),
            "total_insider_shares": _int_or_none(net_act.get("totalInsiderShares")),
            "run_dt":               today,
        })

    # ── Extras ────────────────────────────────────────────────────────────────
    extras = {
        "ticker":          ticker,
        "company_name":    comp_name,
        "sector":          sector,
        "industry":        industry,
        "country":         country,
        "currency":        currency,
        "market_cap":      market_cap,
        "pe_ratio":        pe_ratio,
        "fwd_pe":          fwd_pe,
        "beta":            beta,
        "52w_high":        wk52_high,
        "52w_low":         wk52_low,
        "div_yield":       div_yield,
        "eps_ttm":         eps_ttm,
        "revenue":         revenue,
        "profit_margin":   profit_margin,
        "roe":             roe,
        "debt_to_eq":      debt_to_eq,
        "free_cashflow":   free_cf,
        "analyst_rec":     analyst_rec,
        "target_price":    target_price,
        "inst_count":      inst_count,
        "description":     _safe(profile, "longBusinessSummary"),
        "website":         website,
        "major_holders":   major_holders_rows,
        "top_inst":        inst_holders_rows,
        "top_funds":       fund_holders_rows,
        "insider_roster":  insider_roster_rows,
        "insider_tx":      insider_tx_rows,
        "insider_summary": insider_summary_rows,
    }

    return (
        fin_equity_row,
        major_holders_rows,
        inst_holders_rows,
        fund_holders_rows,
        insider_roster_rows,
        insider_tx_rows,
        insider_summary_rows,
        extras,
    )


def parse_all(
    raw_results: Dict[str, Any]
) -> Tuple[list, list, list, list, list, list, list, list]:
    """Parse all tickers. Returns (fin_rows, major_rows, inst_rows, fund_rows, roster_rows, tx_rows, summary_rows, extras_list)."""
    fin_rows, major_rows, inst_rows, fund_rows = [], [], [], []
    roster_rows, tx_rows, summary_rows, extras_list = [], [], [], []

    for ticker, raw in raw_results.items():
        if raw is None:
            logger.warning("[%s] Skipping — no data.", ticker)
            continue
        try:
            (
                fe, maj, inst, fund,
                roster, tx, summary,
                extras,
            ) = parse_ticker(ticker, raw)
            fin_rows.append(fe)
            major_rows.extend(maj)
            inst_rows.extend(inst)
            fund_rows.extend(fund)
            roster_rows.extend(roster)
            tx_rows.extend(tx)
            summary_rows.extend(summary)
            extras_list.append(extras)
            logger.info(
                "[%s] Parsed raw: major=%d, inst=%d, fund=%d, roster=%d, tx=%d, summary=%d",
                ticker, len(maj), len(inst), len(fund), len(roster), len(tx), len(summary),
            )
        except Exception as exc:
            logger.error("[%s] Parse error: %s", ticker, exc, exc_info=True)

    return (
        fin_rows,
        major_rows,
        inst_rows,
        fund_rows,
        roster_rows,
        tx_rows,
        summary_rows,
        extras_list,
    )
