"""ORM row -> API schema serializers, kept out of the route functions for reuse/testability."""
from __future__ import annotations

from typing import Optional

from sqlalchemy import select
from sqlalchemy.orm import Session, selectinload

from app.api.v1.aggregation import aggregate_is_demo_rows
from app.models import Company, FinancialPeriod, Metric, Score, Security, Source, Valuation
from app.schemas.common import CompanyPageOut, CompanySummaryOut, MetricOut, ScoreOut, ValuationOut


def latest_scores_by_security(db: Session, security_ids: list[str]) -> dict[str, Score]:
    """Batched "latest Score per security_id" lookup for a page of results (screener/rankings).

    AUDIT FIX (StockLab overhaul, performance audit, docs/AUDIT_PERFORMANCE.md finding #2):
    replaces what used to be one `Score` query per security (N+1) with a single query using
    Postgres's `DISTINCT ON`, ordered `(security_id, calculation_date DESC)` -- the same column
    order as `uq_score_security_date`'s implicit unique index (app/models/derived.py), so this is
    an index-only "latest row per group" scan, not a sequential scan. `DISTINCT ON` is
    Postgres-specific; this repo targets Postgres exclusively (docs/DATA_MODEL.md), so this is not
    a portability regression.
    """
    if not security_ids:
        return {}
    rows = db.execute(
        select(Score)
        .where(Score.security_id.in_(security_ids))
        .order_by(Score.security_id, Score.calculation_date.desc())
        .distinct(Score.security_id)
    ).scalars().all()
    return {s.security_id: s for s in rows}


def latest_valuations_by_security(db: Session, security_ids: list[str]) -> dict[str, Valuation]:
    """Batched "latest Valuation per security_id" lookup -- see latest_scores_by_security above,
    same fix, same reasoning, mirrors `uq_valuation_security_date`."""
    if not security_ids:
        return {}
    rows = db.execute(
        select(Valuation)
        .where(Valuation.security_id.in_(security_ids))
        .order_by(Valuation.security_id, Valuation.calculation_date.desc())
        .distinct(Valuation.security_id)
    ).scalars().all()
    return {v.security_id: v for v in rows}


def security_eager_load_options():
    """Options for `.options(*security_eager_load_options())` on any query that selects `Security`
    rows destined for `company_summary()` in a loop (screener/rankings/watchlist).

    AUDIT FIX (StockLab overhaul, Part A2, docs/AUDIT_PERFORMANCE.md's remaining company_summary()
    N+1 finding -- the Score/Valuation N+1 was fixed in an earlier pass via
    latest_scores_by_security()/latest_valuations_by_security() above; this is the same fix applied
    to the company/country/sector/industry side of the same function). Without this,
    `security.company` and each of `company.country`/`company.sector`/`company.industry` is a
    separate lazy-loaded SELECT per row -- SQLAlchemy relationships default to lazy="select"
    (app/models/companies.py, app/models/reference.py never override it) -- so a page of 50 screener
    results costs up to 200 extra queries beyond the base query. `selectinload` issues one extra
    SELECT per relationship level (2 levels here: Security->Company, then Company->{country,
    sector, industry}) using IN(...) over every id already fetched in this page -- a fixed number of
    additional queries regardless of how many rows there are, not one per row.
    """
    return (
        selectinload(Security.company).selectinload(Company.country),
        selectinload(Security.company).selectinload(Company.sector),
        selectinload(Security.company).selectinload(Company.industry),
    )


def is_demo_by_security(db: Session, security_ids: list[str]) -> dict[str, bool]:
    """Batched version of `security_is_demo()` below -- same fix, same reasoning as
    `latest_scores_by_security()`. `is_demo` is derived through FinancialPeriod -> Source, not a
    mapped SQLAlchemy relationship on Security/Company, so `selectinload` (used by
    `security_eager_load_options()` above) can't cover it; this is a second, explicit batched query
    instead of one `security_is_demo()` call per row.

    Semantics: True if ANY of a security's FinancialPeriods came from a demo Source (see
    `aggregate_is_demo_rows()`). The original single-security `security_is_demo()` takes an
    arbitrary (unordered, `.first()`) matching row instead -- in practice a security's periods all
    come from one ingestion pipeline and are uniformly demo or uniformly real (never mixed), so
    this "any" aggregation is not expected to change any real result. Documented explicitly since
    it is the one piece of this fix that is not a byte-for-byte-identical query to the original,
    only an equivalent-in-practice one, per this pass's "don't change API output values" constraint.
    """
    if not security_ids:
        return {}
    rows = db.execute(
        select(FinancialPeriod.security_id, Source.is_demo)
        .join(Source, FinancialPeriod.source_id == Source.id)
        .where(FinancialPeriod.security_id.in_(security_ids))
    ).all()
    return aggregate_is_demo_rows(rows)


def _display(value, applicability: str) -> str:
    if value is None or applicability in ("NOT_MEANINGFUL", "INSUFFICIENT_DATA"):
        return "N/M"
    return f"{value:.4f}"


def security_is_demo(db: Session, security_id: str) -> bool:
    from app.models import FinancialPeriod

    row = (
        db.query(Source.is_demo)
        .join(FinancialPeriod, FinancialPeriod.source_id == Source.id)
        .filter(FinancialPeriod.security_id == security_id)
        .first()
    )
    return bool(row and row[0])


def company_summary(db: Session, security: Security, is_demo: Optional[bool] = None) -> CompanySummaryOut:
    """AUDIT NOTE (StockLab overhaul, Part A2): pass a precomputed `is_demo` (from
    `is_demo_by_security()` above) when calling this in a loop over many securities, so it doesn't
    run `security_is_demo()`'s own query per row. Omit it (as the single-security Company Page call
    site does) to fall back to the original per-call query -- output is identical either way. The
    `security.company`/`.country`/`.sector`/`.industry` accesses below cost nothing extra when the
    query that fetched `security` used `security_eager_load_options()`; otherwise they still work,
    just via SQLAlchemy's normal per-access lazy load (correct either way, just not batched)."""
    company = security.company
    return CompanySummaryOut(
        security_id=security.id, ticker=security.ticker, company_name=company.display_name,
        country=company.country.name if company.country else None,
        sector=company.sector.name if company.sector else None,
        industry=company.industry.name if company.industry else None,
        is_demo=security_is_demo(db, security.id) if is_demo is None else is_demo,
    )


def score_out(score: Score | None) -> ScoreOut | None:
    if score is None:
        return None
    return ScoreOut(
        quality_score=score.quality_score, financial_health_score=score.financial_health_score,
        growth_score=score.growth_score, competitive_advantage_score=score.competitive_advantage_score,
        valuation_score=score.valuation_score, overall_score=score.overall_score, risk_score=score.risk_score,
        recommendation=score.recommendation,
        # `recommendation_confidence` used to be serialised here as a literal None. Nothing
        # computes it, so the field was removed from ScoreOut entirely in the final master pass
        # (§104.20) rather than shipping a permanently-null field in the API contract. See
        # app/schemas/common.py for the full reasoning and app/models/derived.py for the DB column.
        confidence_score=score.confidence_score, data_quality_score=score.data_quality_score,
        weights_used=score.weights_used, triggers_fired=score.triggers_fired,
        subscore_detail=score.subscore_detail,
    )


def valuation_out(val: Valuation | None) -> ValuationOut | None:
    if val is None:
        return None
    return ValuationOut(
        wacc=val.wacc, bear_fair_value=val.bear_fair_value, base_fair_value=val.base_fair_value,
        bull_fair_value=val.bull_fair_value, weighted_fair_value=val.weighted_fair_value,
        fair_value_confidence=val.fair_value_confidence, price_at_calculation=val.price_at_calculation,
        margin_of_safety=val.margin_of_safety, strong_buy_price=val.strong_buy_price, buy_price=val.buy_price,
        overvalued_price=val.overvalued_price, expected_return_5y=val.expected_return_5y,
        business_profile=val.business_profile,
    )


def build_company_page(db: Session, security: Security, score: Score | None, val: Valuation | None, metrics: list[Metric]) -> CompanyPageOut:
    why = []
    if metrics:
        by_key = {m.metric_key: m for m in metrics}
        for key, label in (("roic", "ROIC"), ("roic_minus_wacc", "ROIC-WACC"), ("fcf_margin", "FCF Margin"),
                            ("net_debt_to_ebitda", "Net Debt/EBITDA"), ("revenue_cagr_5y", "5Y Revenue CAGR")):
            m = by_key.get(key)
            if m and m.value is not None:
                why.append(f"{label}: {m.value:.2%}" if "margin" in key or "cagr" in key or key == "roic" else f"{label}: {m.value:.2f}")
    risks = score.triggers_fired if score and score.triggers_fired else []

    return CompanyPageOut(
        company=company_summary(db, security),
        score=score_out(score), valuation=valuation_out(val),
        why=why, risks=risks,
        metrics=[
            MetricOut(key=m.metric_key, value=m.value, display=_display(m.value, m.applicability),
                       status=m.status, applicability=m.applicability, formula_version=m.formula_version, note=m.note)
            for m in metrics
        ],
        as_of=score.calculation_date if score else (val.calculation_date if val else None),
    )