"""Realized cost-optimization outcomes — the leadership-facing rollup.

Reads the ``cmdb_efficiency_savings_events`` ledger (written by
``savings_tracker``) and computes the REALIZED monthly saving per event as
``baseline − actual post-action cost`` from ``cmdb_cost_facts`` — so a
destroy shows the full drop and a restarted box self-corrects to ~0. Also
surfaces the still-OPEN opportunity (current idle/oversized estimated
savings not yet acted on) so the leadership view is meaningful from day one.

Read-only; no cron. All amounts USD. "Realized" accrues from the feature's
launch (we do not fabricate historical outcomes — actions before the
tracker existed were never flagged/snapshotted).
"""
from __future__ import annotations

from datetime import UTC, datetime, timedelta
from decimal import Decimal
from typing import Any

from sqlalchemy import func, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.domains.cmdb.cost_view import fact_engineering_amount
from app.domains.cmdb.models import (
    SAVINGS_METHOD_DESTROY,
    CmdbCostFact,
    CmdbEfficiencySavingsEvent,
    CmdbServer,
    CmdbServerEfficiency,
)

_ACTIONABLE = ("idle", "oversized")
_UNASSIGNED = "未分配"

# Cloud billing lands with a lag (CUR/billing-export/cost-management all
# settle ~T-2). Cost facts for the trailing ``_BILLING_LAG_DAYS`` are not
# yet complete, so a post-action window that includes them systematically
# under-reads the instance's true post cost. Exclude them from the settled
# window before normalizing.
_BILLING_LAG_DAYS = 2
# Minimum number of SETTLED post-action days required before a non-destroy
# realized saving is credited. Right after an action there are ~no settled
# post-window facts, so ``post_monthly`` reads ~0 and ``baseline − post``
# reads ~full baseline — a downsize (true ~50% saving) would spike to ~2×
# and a day-0 stop to the entire pre-action cost. Wait until enough settled
# days exist to actually measure the post cost.
_MIN_SETTLED_POST_DAYS = 3


def _f(v: Any) -> float:
    if v is None:
        return 0.0
    return float(v)


async def _post_action_monthly(
    db: AsyncSession, ev: CmdbEfficiencySavingsEvent, now: datetime,
) -> Decimal | None:
    """Actual monthly cost of the instance AFTER the action — normalized to
    a 30-day rate over the SETTLED post-action window (capped at 30d, with
    the trailing billing-lag days excluded). A destroyed instance stops
    accruing cost lines → ~0. A restarted instance resumes billing → rises
    back toward baseline, shrinking realized to ~0.

    Returns ``None`` when there are not yet ``_MIN_SETTLED_POST_DAYS`` of
    settled post-action facts — the post cost cannot be measured that soon
    after the action, and callers must NOT credit a (baseline − post)
    saving from an unsettled window (see ``_realized_monthly``)."""
    if not ev.instance_id or not ev.integration_id:
        return None
    # ``acted_at`` read back from MySQL is tz-naive; make it UTC-aware so it
    # compares with ``now``.
    start = ev.acted_at if ev.acted_at.tzinfo else ev.acted_at.replace(tzinfo=UTC)
    # Settled window: post-action, capped at 30d, minus the trailing
    # billing-lag days whose facts have not fully landed.
    settled_through = min(
        (now - timedelta(days=_BILLING_LAG_DAYS)).date(),
        (start + timedelta(days=30)).date(),
    )
    settled_days = (settled_through - start.date()).days
    if settled_days < _MIN_SETTLED_POST_DAYS:
        return None
    total = (
        await db.execute(
            select(func.coalesce(func.sum(fact_engineering_amount()), 0)).where(
                CmdbCostFact.integration_id == ev.integration_id,
                CmdbCostFact.resource_id == ev.instance_id,
                CmdbCostFact.date > start.date(),
                CmdbCostFact.date <= settled_through,
            )
        )
    ).scalar_one()
    daily = Decimal(str(total)) / Decimal(settled_days)
    return (daily * Decimal("30")).quantize(Decimal("0.01"))


def _realized_monthly(
    ev: CmdbEfficiencySavingsEvent, post_monthly: Decimal | None,
) -> Decimal:
    if ev.monthly_cost_baseline_usd is None:
        return Decimal("0")
    # Destroy is a clean elimination — credit the full baseline (post lines,
    # if any lag/proration noise exists, must not inflate it). This does not
    # depend on the post-window, so it credits immediately.
    if ev.method == SAVINGS_METHOD_DESTROY:
        realized = ev.monthly_cost_baseline_usd
    elif post_monthly is None:
        # Non-destroy (stop / downsize): the post cost isn't settled enough
        # to measure yet. Do NOT credit (baseline − ~0) — that would spike
        # realized savings right after the action. Wait for settled facts.
        return Decimal("0")
    else:
        realized = ev.monthly_cost_baseline_usd - post_monthly
    return realized if realized > 0 else Decimal("0")


async def realized_savings_by_event(
    db: AsyncSession, events: list[CmdbEfficiencySavingsEvent], *,
    now: datetime | None = None,
) -> dict[str, Decimal]:
    """Return each event's current self-correcting realized monthly saving."""
    evaluated_at = now or datetime.now(UTC)
    values: dict[str, Decimal] = {}
    for event in events:
        values[str(event.id)] = _realized_monthly(
            event, await _post_action_monthly(db, event, evaluated_at),
        )
    return values


async def savings_outcomes(
    db: AsyncSession, *, since_days: int | None = None,
    now: datetime | None = None,
) -> dict[str, Any]:
    """Leadership rollup: realized monthly + annualized savings, split by
    method (destroy/stop/downsize), by source (helix-executed vs detected
    console-side), and by owner department, a per-month trend, and the
    still-open estimated opportunity. ``since_days`` optionally limits
    realized events to those acted on within the window (None = all-time).

    ``now`` injects the clock (same convention as
    ``savings_detector.detect_console_savings_for_integration`` and
    ``efficiency.compute_efficiency_for_integration(today=...)``). It is read
    exactly ONCE per call and threaded into every window derivation
    (``since_days`` cut-off and each event's settled post-action window), so
    a UTC date rollover mid-call can never shift one window relative to
    another. Callers that already hold an instant should pass it; omitting it
    keeps the previous behaviour (``datetime.now(UTC)``)."""
    now = now or datetime.now(UTC)
    conds = []
    if since_days:
        conds.append(
            CmdbEfficiencySavingsEvent.acted_at >= now - timedelta(days=since_days),
        )
    events = list(
        (await db.execute(select(CmdbEfficiencySavingsEvent).where(*conds)))
        .scalars()
        .all()
    )

    realized_monthly_total = Decimal("0")
    by_method: dict[str, Decimal] = {}
    by_source: dict[str, Decimal] = {}
    by_dept: dict[str, Decimal] = {}
    by_month: dict[str, Decimal] = {}
    counted = 0
    for ev in events:
        post = await _post_action_monthly(db, ev, now)
        realized = _realized_monthly(ev, post)
        if realized <= 0:
            continue
        counted += 1
        realized_monthly_total += realized
        by_method[ev.method] = by_method.get(ev.method, Decimal("0")) + realized
        src = ev.source or "helix"
        by_source[src] = by_source.get(src, Decimal("0")) + realized
        dept = ev.owner_department or _UNASSIGNED
        by_dept[dept] = by_dept.get(dept, Decimal("0")) + realized
        mkey = ev.acted_at.strftime("%Y-%m")
        by_month[mkey] = by_month.get(mkey, Decimal("0")) + realized

    # Still-open opportunity: current idle/oversized estimated savings for
    # servers that have NOT been acted on (not in the ledger).
    #
    # The exclusion set must span ALL acted servers, not just those inside
    # ``since_days``. ``events`` is windowed for the realized rollup, but a
    # server acted on BEFORE the window can still carry a live actionable
    # verdict in ``cmdb_server_efficiency`` (the daily recompute keeps
    # scoring the — possibly still-idle — box). Building the exclusion set
    # from the windowed ``events`` would then re-count it as a fresh open
    # opportunity, double-counting savings already realized earlier. Query
    # the acted server_ids unfiltered.
    acted_ids = set(
        (
            await db.execute(select(CmdbEfficiencySavingsEvent.server_id))
        )
        .scalars()
        .all()
    )
    opp_rows = (
        await db.execute(
            select(
                CmdbServerEfficiency.server_id,
                CmdbServerEfficiency.estimated_monthly_savings_usd,
            )
            .select_from(
                CmdbServerEfficiency.__table__.join(
                    CmdbServer.__table__,
                    CmdbServer.id == CmdbServerEfficiency.server_id,
                )
            )
            .where(
                CmdbServer.deleted_at.is_(None),
                CmdbServerEfficiency.verdict.in_(_ACTIONABLE),
                CmdbServerEfficiency.estimated_monthly_savings_usd.isnot(None),
            )
        )
    ).all()
    open_opportunity = Decimal("0")
    open_count = 0
    for sid, est in opp_rows:
        if str(sid) in acted_ids or est is None:
            continue
        open_opportunity += Decimal(str(est))
        open_count += 1

    def _rows(d: dict[str, Decimal]) -> list[dict[str, Any]]:
        return sorted(
            (
                {"key": k, "monthly_usd": _f(v), "annualized_usd": _f(v) * 12}
                for k, v in d.items()
            ),
            key=lambda r: r["monthly_usd"],
            reverse=True,
        )

    return {
        "realized": {
            "monthly_usd": _f(realized_monthly_total),
            "annualized_usd": _f(realized_monthly_total) * 12,
            "events": counted,
        },
        "by_method": _rows(by_method),
        "by_source": _rows(by_source),
        "by_department": _rows(by_dept)[:10],
        "trend": sorted(
            (
                {"month": k, "monthly_usd": _f(v), "annualized_usd": _f(v) * 12}
                for k, v in by_month.items()
            ),
            key=lambda r: r["month"],
        ),
        "open_opportunity": {
            "monthly_usd": _f(open_opportunity),
            "annualized_usd": _f(open_opportunity) * 12,
            "servers": open_count,
        },
        "currency": "USD",
    }