"""
Screener execution (docs/SCREENING.md §4). Translates a validated ScreenRequest into a
parameterized SQL query over the precomputed `metrics`/`scores` tables — never a per-request
recomputation of the 30 metrics, never raw user SQL (spec §35: no SQL interface).
"""
from __future__ import annotations

from sqlalchemy import and_, exists, or_, select

from app.models import Company, Country, Metric, Score, Security
from app.schemas.common import ScreenFilter, ScreenRequest

_SCORE_FIELDS = {
    "overall_score", "quality_score", "financial_health_score", "growth_score",
    "competitive_advantage_score", "valuation_score", "risk_score",
}

_OP_MAP = {
    "gt": lambda col, v, v2: col > v,
    "gte": lambda col, v, v2: col >= v,
    "lt": lambda col, v, v2: col < v,
    "lte": lambda col, v, v2: col <= v,
    "eq": lambda col, v, v2: col == v,
    "between": lambda col, v, v2: and_(col >= v, col <= v2),
}


class InvalidScreenFilter(ValueError):
    pass


def _metric_exists_clause(f: ScreenFilter):
    if f.op not in _OP_MAP:
        raise InvalidScreenFilter(f"Unknown operator: {f.op}")
    if f.relative != "absolute":
        # industry_percentile / historical_percentile / peer_percentile require a precomputed
        # percentile column; not yet materialized in this pass — see SPEC_COVERAGE.md. Rejected
        # explicitly rather than silently falling back to absolute (which would misrepresent the filter).
        raise InvalidScreenFilter(
            f"relative='{f.relative}' filters require percentile columns not yet materialized in "
            f"this build — see docs/SPEC_COVERAGE.md. Use relative='absolute' for now."
        )
    m = Metric.__table__.alias(f"metric_{f.metric}")
    condition = _OP_MAP[f.op](m.c.value, f.value, f.value2)
    return exists(
        select(1).select_from(m).where(m.c.security_id == Security.id, m.c.metric_key == f.metric, condition)
    )


def build_screen_query(request: ScreenRequest):
    query = select(Security).join(Company, Security.company_id == Company.id)

    if not request.universe.include_demo:
        # Demo exclusion at screener-scale needs a materialized is_demo flag on Security/Metric
        # (checking it today means joining back through financial_periods -> sources per row,
        # which is what app/api/v1/serializers.py::security_is_demo does for a single Company
        # Page, not a whole screen). Not wired into the bulk screener query in this pass — see
        # docs/SPEC_COVERAGE.md. Screening currently returns demo and live rows together; the
        # per-company page still labels demo rows correctly regardless.
        pass

    if request.universe.country:
        query = query.join(Country, Company.country_id == Country.id).where(Country.iso2 == request.universe.country)
    if request.universe.sector:
        query = query.where(Company.sector.has(code=request.universe.sector))
    if request.universe.market_cap_min is not None or request.universe.market_cap_max is not None:
        raise InvalidScreenFilter(
            "market_cap filtering requires a materialized market_cap column on securities/metrics "
            "not yet wired in this pass — see SPEC_COVERAGE.md."
        )

    clauses = [_metric_exists_clause(f) for f in request.filters]
    if clauses:
        query = query.where(and_(*clauses) if request.logic == "AND" else or_(*clauses))

    if request.sort_by in _SCORE_FIELDS:
        score_sub = select(Score.security_id, getattr(Score, request.sort_by).label("sort_value")).subquery()
        query = query.join(score_sub, score_sub.c.security_id == Security.id, isouter=True)
        order_col = score_sub.c.sort_value
    else:
        metric_sub = select(Metric.security_id, Metric.value.label("sort_value")).where(
            Metric.metric_key == request.sort_by
        ).subquery()
        query = query.join(metric_sub, metric_sub.c.security_id == Security.id, isouter=True)
        order_col = metric_sub.c.sort_value

    order_col = order_col.desc() if request.sort_direction == "desc" else order_col.asc()
    query = query.order_by(order_col.nullslast() if hasattr(order_col, "nullslast") else order_col)
    return query.offset(request.offset).limit(request.limit)