"""Flux computation over the imported (report-exact) figures.

All values come straight from the NetSuite report export (so they already
reflect NetSuite's consolidation); this layer only diffs a period against its
comparison baseline and flags variances. No consolidation logic lives here.

Comparison baselines:
  * ``mom`` — the immediately prior accounting month.
  * ``yoy`` — the same month one year earlier.

A line is flagged when ``|Δ|`` breaches the amount threshold and/or ``|Δ%|``
breaches the percentage threshold (per-line overrides on the line_def win over
the singleton ``finance_flux_config`` defaults), mirroring the legacy portal's
⚐ (one threshold) / ⚑ (both) semantics.
"""
from __future__ import annotations

from dataclasses import dataclass
from decimal import Decimal

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

from app.domains.finance_flux import constants as c
from app.domains.finance_flux.models import (
    FinanceFluxCommentary,
    FinanceFluxConfig,
    FinanceFluxKpiDef,
    FinanceFluxKpiOverride,
    FinanceFluxLine,
    FinanceFluxLineDef,
    FinanceFluxLineDetail,
    FinanceFluxPeriod,
)

# Flag levels
FLAG_NONE = "none"
FLAG_AMOUNT = "amount"
FLAG_PCT = "pct"
FLAG_BOTH = "both"


@dataclass
class _Thresholds:
    amount: Decimal
    pct: Decimal


def prior_period_key(period_key: str, mode: str) -> str:
    """'2026-05' → '2026-04' (mom) or '2025-05' (yoy)."""
    year, month = (int(x) for x in period_key.split("-"))
    if mode == c.COMPARE_YOY:
        return f"{year - 1:04d}-{month:02d}"
    month -= 1
    if month == 0:
        year, month = year - 1, 12
    return f"{year:04d}-{month:02d}"


def _flag(delta: Decimal, prior: Decimal, thr: _Thresholds) -> str:
    amount_hit = abs(delta) > thr.amount
    pct_hit = prior != 0 and abs(delta / prior * 100) > thr.pct
    if amount_hit and pct_hit:
        return FLAG_BOTH
    if amount_hit:
        return FLAG_AMOUNT
    if pct_hit:
        return FLAG_PCT
    return FLAG_NONE


def _pct(delta: Decimal, prior: Decimal) -> float | None:
    if prior == 0:
        return None
    return float((delta / abs(prior) * 100).quantize(Decimal("0.1")))


async def _get_config(db: AsyncSession) -> FinanceFluxConfig:
    cfg = (await db.execute(
        select(FinanceFluxConfig).order_by(FinanceFluxConfig.created_at)
    )).scalars().first()
    if cfg is None:  # migration seeds exactly one row; be defensive in tests
        cfg = FinanceFluxConfig(
            amount_threshold=Decimal(c.DEFAULT_AMOUNT_THRESHOLD),
            pct_threshold=Decimal(c.DEFAULT_PCT_THRESHOLD),
            currency=c.DEFAULT_CURRENCY,
        )
    return cfg


async def _values_for(
    db: AsyncSession, period_key: str, statement: str,
) -> dict[str, Decimal]:
    period = (await db.execute(
        select(FinanceFluxPeriod).where(FinanceFluxPeriod.period_key == period_key)
    )).scalar_one_or_none()
    if period is None:
        return {}
    rows = (await db.execute(
        select(FinanceFluxLine).where(
            FinanceFluxLine.period_id == period.id,
            FinanceFluxLine.statement == statement,
        )
    )).scalars()
    return {r.line_key: r.value for r in rows}


async def _details_for(
    db: AsyncSession, period_key: str, statement: str,
) -> dict[str, list[tuple[str, Decimal]]]:
    """Sub-account details grouped by parent line_key (in sort order)."""
    period = (await db.execute(
        select(FinanceFluxPeriod).where(FinanceFluxPeriod.period_key == period_key)
    )).scalar_one_or_none()
    if period is None:
        return {}
    rows = (await db.execute(
        select(FinanceFluxLineDetail).where(
            FinanceFluxLineDetail.period_id == period.id,
            FinanceFluxLineDetail.statement == statement,
        ).order_by(
            FinanceFluxLineDetail.parent_line_key, FinanceFluxLineDetail.sort_order)
    )).scalars()
    out: dict[str, list[tuple[str, Decimal]]] = {}
    for r in rows:
        out.setdefault(r.parent_line_key, []).append((r.label, r.value))
    return out


async def compute_statement(
    db: AsyncSession, *, period_key: str, statement: str, mode: str,
) -> dict:
    """Per-line flux for one statement vs the comparison baseline."""
    cfg = await _get_config(db)
    base = _Thresholds(Decimal(cfg.amount_threshold), Decimal(cfg.pct_threshold))
    prior_key = prior_period_key(period_key, mode)

    cur = await _values_for(db, period_key, statement)
    prev = await _values_for(db, prior_key, statement)
    cur_details = await _details_for(db, period_key, statement)
    prev_details = await _details_for(db, prior_key, statement)

    defs = (await db.execute(
        select(FinanceFluxLineDef).where(
            FinanceFluxLineDef.statement == statement,
            FinanceFluxLineDef.is_active == True,  # noqa: E712
        ).order_by(FinanceFluxLineDef.sort_order)
    )).scalars().all()

    commentary = {
        cm.line_key: cm for cm in (await db.execute(
            select(FinanceFluxCommentary).join(
                FinanceFluxPeriod,
                FinanceFluxPeriod.id == FinanceFluxCommentary.period_id,
            ).where(
                FinanceFluxPeriod.period_key == period_key,
                FinanceFluxCommentary.statement == statement,
            )
        )).scalars()
    }

    out_lines = []
    for d in defs:
        if d.line_key not in cur and d.line_key not in prev:
            continue  # a catalog line with no data this/last period
        current = cur.get(d.line_key, Decimal(0))
        prior = prev.get(d.line_key, Decimal(0))
        delta = current - prior
        thr = _Thresholds(
            Decimal(d.threshold_amount) if d.threshold_amount is not None else base.amount,
            Decimal(d.threshold_pct) if d.threshold_pct is not None else base.pct,
        )
        flag = _flag(delta, prior, thr) if d.row_type == c.ROW_LINE else FLAG_NONE
        cm = commentary.get(d.line_key)
        # Sub-account drill-down: each detail's current/prior (matched by label)
        # + Δ. Aggregate same-label rows (a line can carry two GL leaves with
        # the same label) so neither side double-counts in the diff.
        def _by_label(rows: list[tuple[str, Decimal]]) -> dict[str, Decimal]:
            agg: dict[str, Decimal] = {}
            for lbl, val in rows:
                agg[lbl] = agg.get(lbl, Decimal(0)) + val
            return agg
        cur_d = _by_label(cur_details.get(d.line_key, []))
        prev_d = _by_label(prev_details.get(d.line_key, []))
        details = []
        for lbl, dcur in cur_d.items():
            dprior = prev_d.get(lbl, Decimal(0))
            ddelta = dcur - dprior
            details.append({
                "label": lbl, "current": str(dcur), "prior": str(dprior),
                "delta": str(ddelta), "delta_pct": _pct(ddelta, dprior),
            })
        # Details present last period but gone this period (dropped to 0).
        for lbl, dprior in prev_d.items():
            if lbl not in cur_d:
                details.append({
                    "label": lbl, "current": "0", "prior": str(dprior),
                    "delta": str(-dprior), "delta_pct": _pct(-dprior, dprior),
                })
        out_lines.append({
            "line_key": d.line_key,
            "label": d.label,
            "section": d.section,
            "row_type": d.row_type,
            "owner_label": d.owner_label,
            "current": str(current),
            "prior": str(prior),
            "delta": str(delta),
            "delta_pct": _pct(delta, prior),
            "flag": flag,
            "commentary": cm.commentary if cm else None,
            "recon_link": cm.recon_link if cm else None,
            "ai_narrative": cm.ai_narrative if cm else None,
            "details": details,
        })
    return {
        "period_key": period_key,
        "prior_period_key": prior_key,
        "statement": statement,
        "mode": mode,
        "currency": cfg.currency,
        "lines": out_lines,
    }


async def compute_flag_summary(
    db: AsyncSession, *, period_key: str, mode: str,
) -> dict:
    """Variance-flag counts across BS+PL line rows (the Summary banner)."""
    amount_n = pct_n = both_n = 0
    for statement in c.STATEMENTS:
        st = await compute_statement(
            db, period_key=period_key, statement=statement, mode=mode)
        for ln in st["lines"]:
            if ln["row_type"] != c.ROW_LINE:
                continue
            if ln["flag"] == FLAG_AMOUNT:
                amount_n += 1
            elif ln["flag"] == FLAG_PCT:
                pct_n += 1
            elif ln["flag"] == FLAG_BOTH:
                amount_n += 1
                pct_n += 1
                both_n += 1
    return {
        "amount_threshold_exceeded": amount_n,
        "pct_threshold_exceeded": pct_n,
        "both_thresholds_exceeded": both_n,
    }


async def compute_summary(
    db: AsyncSession, *, period_key: str, mode: str,
) -> dict:
    """Summary page: KPI cards (auto from source line + override) + flag counts."""
    cfg = await _get_config(db)
    prior_key = prior_period_key(period_key, mode)
    kpi_defs = (await db.execute(
        select(FinanceFluxKpiDef).where(
            FinanceFluxKpiDef.is_active == True  # noqa: E712
        ).order_by(FinanceFluxKpiDef.sort_order)
    )).scalars().all()

    # per-period KPI overrides — fetch BOTH the current and prior period.
    # The prior period's override is what users actually saw on that
    # period's card, so the MoM delta / delta_pct / tone must compare
    # against it, not the raw auto line value (else an overridden prior
    # month shows a wrong movement and possibly an inverted good/bad tone).
    async def _overrides_for(pkey: str) -> dict[str, Decimal]:
        return {
            o.kpi_key: o.value for o in (await db.execute(
                select(FinanceFluxKpiOverride).join(
                    FinanceFluxPeriod,
                    FinanceFluxPeriod.id == FinanceFluxKpiOverride.period_id,
                ).where(FinanceFluxPeriod.period_key == pkey)
            )).scalars()
        }

    overrides = await _overrides_for(period_key)
    prior_overrides = await _overrides_for(prior_key)

    values_cache: dict[str, dict[str, Decimal]] = {}

    async def line_value(stmt: str, key: str, pkey: str) -> Decimal:
        ck = f"{pkey}:{stmt}"
        if ck not in values_cache:
            values_cache[ck] = await _values_for(db, pkey, stmt)
        return values_cache[ck].get(key, Decimal(0))

    cards = []
    for k in kpi_defs:
        auto_cur = await line_value(k.statement, k.source_line_key, period_key)
        cur = overrides.get(k.kpi_key, auto_cur)
        auto_prior = await line_value(k.statement, k.source_line_key, prior_key)
        prior = prior_overrides.get(k.kpi_key, auto_prior)
        delta = cur - prior
        pct = _pct(delta, prior)
        tone = "neutral"
        if pct is not None and delta != 0:
            good = (delta > 0) if k.higher_is_better else (delta < 0)
            tone = "good" if good else "bad"
        cards.append({
            "kpi_key": k.kpi_key,
            "label": k.label,
            "value": str(cur),
            "prior": str(prior),
            "delta": str(delta),
            "delta_pct": pct,
            "tone": tone,
            "overridden": k.kpi_key in overrides,
        })

    flags = await compute_flag_summary(db, period_key=period_key, mode=mode)
    return {
        "period_key": period_key,
        "prior_period_key": prior_key,
        "mode": mode,
        "currency": cfg.currency,
        "kpis": cards,
        "flag_summary": flags,
    }


async def compute_variance_rankings(
    db: AsyncSession, *, period_key: str, statement: str, mode: str, top_n: int = 8,
) -> list[dict]:
    """Top line movers for a statement, ranked by |Δ| (excludes totals)."""
    st = await compute_statement(
        db, period_key=period_key, statement=statement, mode=mode)
    movers = [
        ln for ln in st["lines"] if ln["row_type"] == c.ROW_LINE
    ]
    movers.sort(key=lambda ln: abs(Decimal(ln["delta"])), reverse=True)
    return movers[:top_n]