"""Budget pool service (PR 3).

The Phase 1 model is deliberately simple:

* A ``procurement_budgets`` row is one (year, department, cost_center,
  category_id, currency) tuple holding a ``total_amount`` ceiling.
* PO writes that affect a budget emit one ``procurement_budget_
  consumptions`` row with ``event ∈ {lock, release, actualize}``.
* Remaining balance is computed at read time as
  ``total - SUM(lock) + SUM(release)`` — open-commitment model. The
  ``actualize`` event is informational only (it decorates the activity
  log when payment lands; the underlying lock continues to count
  against remaining until the PO is finalised + the lock is "closed
  out" via a release at completion time — Phase 1 doesn't auto-close,
  admins close manually).

Trigger points (call sites in ``service.py``):

* ``create_order`` — if a budget can be matched to the new PO's
  (year, department, cost_center, category_id, currency), emit a
  ``lock`` event for the order's total_amount AND stamp the order's
  ``budget_id`` + ``budget_locked_at``. The match is best-effort —
  no budget found → no lock, no error (admins create budgets at
  their own pace).
* ``update_order`` — calls ``reconcile_budget_for_order`` after every
  edit. That reconciler keeps ``SUM(lock) - SUM(release)`` for the PO
  equal to its *desired* open commitment, which is ``total_amount``
  while the PO is an active in-flight commitment and ``0`` once it is
  ``cancelled`` / ``rejected``. So it covers every edit path with one
  rule: a cancel emits a ``release``, a re-open of a cancelled PO
  emits a fresh ``lock``, an amount bump/cut emits a delta ``lock`` /
  ``release``, and a scope change (department / cost_center /
  category_id / currency) fully releases the old pool, detaches, then
  re-matches + locks against the new scope. No edit can silently drift
  the pool away from PO reality.
* (later) — payment workflow could emit ``actualize`` events; not
  wired in PR 3 to keep the surface contained.
"""
from __future__ import annotations

import logging
import uuid
from datetime import UTC, datetime
from decimal import Decimal
from typing import Any

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

from app.core.fx_rate import resolve_cny_per_usd
from app.domains.procurement.models import (
    ProcurementBudget,
    ProcurementBudgetConsumption,
    ProcurementOrder,
)

logger = logging.getLogger(__name__)


async def _resolve_fx_cny(
    db: AsyncSession, amount: Decimal | None, currency: str | None
) -> tuple[Decimal | None, Decimal | None]:
    """Pull amount → CNY for activity-log convenience. Same lenient
    contract as service._resolve_fx — failure returns (None, None)."""
    if amount is None or currency is None:
        return None, None
    cur = currency.upper()
    if cur == "CNY":
        return Decimal("1.000000"), amount
    if cur == "USD":
        try:
            rate = await resolve_cny_per_usd(db)
        except RuntimeError:
            return None, None
        d_rate = Decimal(str(rate)).quantize(Decimal("0.000001"))
        return d_rate, (amount * d_rate).quantize(Decimal("0.01"))
    return None, None


async def find_matching_budget(
    db: AsyncSession,
    *,
    year: int,
    department: str | None,
    cost_center: str | None,
    category_id: uuid.UUID | None,
    currency: str,
) -> ProcurementBudget | None:
    """Find an active budget that matches the PO scope tuple.

    Match rule: exact ``(year, department, cost_center, category_id,
    currency)`` — a budget with ``category_id=NULL`` is "any category
    under this dept/cost_center", treated as a more general fallback.
    Returns the most specific match (non-NULL category wins over NULL),
    or None if nothing matches.
    """
    if not department:
        return None
    base = select(ProcurementBudget).where(
        ProcurementBudget.year == year,
        ProcurementBudget.department == department,
        ProcurementBudget.is_active.is_(True),
        ProcurementBudget.currency == currency,
    )
    if cost_center:
        base = base.where(ProcurementBudget.cost_center == cost_center)
    else:
        base = base.where(ProcurementBudget.cost_center.is_(None))

    # Try category-specific first. The (year, dept, cost_center,
    # category_id, currency) UNIQUE constraint is *loose* on MySQL/TiDB
    # whenever any column is NULL (NULLs compare distinct), so a scope
    # can legitimately match more than one row. We must NOT use
    # scalar_one_or_none() here — it raises MultipleResultsFound, which
    # the create_order best-effort wrapper would swallow, silently
    # killing every budget lock for that scope. Pick the newest active
    # row deterministically instead.
    if category_id is not None:
        result = await db.execute(
            base.where(ProcurementBudget.category_id == str(category_id))
            .order_by(ProcurementBudget.created_at.desc())
            .limit(1)
        )
        specific = result.scalars().first()
        if specific is not None:
            return specific

    # Fall back to a category-NULL ("any category") budget.
    result = await db.execute(
        base.where(ProcurementBudget.category_id.is_(None))
        .order_by(ProcurementBudget.created_at.desc())
        .limit(1)
    )
    return result.scalars().first()


async def lock_budget_for_order(
    db: AsyncSession,
    order: ProcurementOrder,
    *,
    actor_person_id: uuid.UUID | None = None,
) -> ProcurementBudget | None:
    """Find + lock budget for ``order``. Returns the matched budget
    (or None if no match). Sets order.budget_id + budget_locked_at,
    writes one ``lock`` consumption row.

    Idempotent: if ``order.budget_id`` is already set, return that
    budget without writing another lock row.
    """
    if order.budget_id is not None:
        existing = await db.get(ProcurementBudget, str(order.budget_id))
        return existing
    if order.total_amount is None or order.total_amount <= 0:
        return None

    year = (order.requested_at or order.created_at or datetime.now(UTC)).year
    budget = await find_matching_budget(
        db,
        year=year,
        department=order.department,
        cost_center=order.cost_center,
        category_id=order.category_id,
        currency=order.currency,
    )
    if budget is None:
        return None

    fx_rate, amount_cny = await _resolve_fx_cny(
        db, order.total_amount, order.currency,
    )
    db.add(ProcurementBudgetConsumption(
        budget_id=budget.id,
        po_id=order.id,
        event="lock",
        amount=order.total_amount,
        currency=order.currency,
        fx_rate_to_cny=fx_rate,
        amount_cny=amount_cny,
        actor_person_id=actor_person_id,
        notes=f"PO {order.order_number} created",
    ))
    order.budget_id = budget.id
    order.budget_locked_at = datetime.now(UTC)
    await db.flush()
    return budget


async def release_budget_for_order(
    db: AsyncSession,
    order: ProcurementOrder,
    *,
    reason: str,
    actor_person_id: uuid.UUID | None = None,
) -> bool:
    """Emit a ``release`` event for ``order`` if it has a locked
    budget that hasn't already been released.

    Returns True if a release row was written; False if there was
    nothing to release.
    """
    if order.budget_id is None:
        return False
    if order.budget_released_at is not None:
        return False

    # Sum existing locks − releases to figure out current open
    # commitment for this PO. We release exactly that amount.
    open_amount = await _open_amount_for_order(db, order.id)
    if open_amount <= 0:
        return False

    await _emit_consumption(
        db,
        budget_id=order.budget_id,
        order=order,
        event="release",
        amount=open_amount,
        actor_person_id=actor_person_id,
        notes=f"Released — {reason}",
    )
    order.budget_released_at = datetime.now(UTC)
    await db.flush()
    return True


async def _open_amount_for_order(
    db: AsyncSession, order_id: uuid.UUID,
) -> Decimal:
    """Current open commitment for one PO = SUM(lock) − SUM(release)."""
    val = await db.scalar(
        select(
            func.coalesce(
                func.sum(
                    case(
                        (ProcurementBudgetConsumption.event == "lock",
                         ProcurementBudgetConsumption.amount),
                        else_=-ProcurementBudgetConsumption.amount,
                    ),
                ),
                0,
            ),
        ).where(
            ProcurementBudgetConsumption.po_id == str(order_id),
            ProcurementBudgetConsumption.event.in_(["lock", "release"]),
        )
    )
    return Decimal(str(val or 0))


async def _emit_consumption(
    db: AsyncSession,
    *,
    budget_id: uuid.UUID,
    order: ProcurementOrder,
    event: str,
    amount: Decimal,
    actor_person_id: uuid.UUID | None,
    notes: str,
    currency: str | None = None,
) -> None:
    """Append one consumption row with FX decoration. Append-only — never
    UPDATEs/DELETEs an existing row, preserving the full audit trail.

    ``currency`` defaults to the order's current currency, but a ``release``
    against an existing pool MUST use the currency that pool was locked in
    (which differs from ``order.currency`` when the edit changed the PO
    currency) — otherwise the consumption row, and the budget's per-currency
    ledger, would be mis-tagged. Callers pass it explicitly for releases.
    """
    ccy = currency or order.currency
    fx_rate, amount_cny = await _resolve_fx_cny(db, amount, ccy)
    db.add(ProcurementBudgetConsumption(
        budget_id=budget_id,
        po_id=order.id,
        event=event,
        amount=amount,
        currency=ccy,
        fx_rate_to_cny=fx_rate,
        amount_cny=amount_cny,
        actor_person_id=actor_person_id,
        notes=notes,
    ))


async def reconcile_budget_for_order(
    db: AsyncSession,
    order: ProcurementOrder,
    *,
    scope_changed: bool = False,
    prev_currency: str | None = None,
    actor_person_id: uuid.UUID | None = None,
) -> None:
    """Keep this PO's budget lock in sync with its current amount + status.

    Single rule: ``SUM(lock) - SUM(release)`` for the PO must equal its
    *desired* open commitment, which is ``total_amount`` while the PO is an
    active in-flight commitment and ``0`` once it is cancelled / rejected.
    A single compensating ``lock`` (positive delta) or ``release`` (negative
    delta) row is written to close the gap — covering amount edits, status
    reopen, and cancel uniformly.

    ``prev_currency`` is the PO's currency BEFORE this edit (``order.currency``
    has already been mutated by the caller). Any release against the
    currently-attached pool is tagged with it, because that pool was locked
    in the old currency — using the new currency would mis-tag the row and
    skew the pool's per-currency ledger.

    A scope change (department / cost_center / category_id / currency) only
    migrates pools while the PO is *still active*: the old pool is released
    in full, the PO detached, then re-matched + locked against the new scope.
    A cancelled / rejected PO is never migrated — it simply releases its
    current pool via the delta path (keeping ``budget_id`` + stamping
    ``budget_released_at`` so the on-PO trace survives).
    """
    desired = (
        order.total_amount
        if (
            order.status not in ("cancelled", "rejected")
            and order.total_amount is not None
            and order.total_amount > 0
        )
        else Decimal("0")
    )
    # Currency a release must be tagged with = the pool's lock currency =
    # the PO's pre-edit currency (falls back to current when unchanged).
    release_ccy = prev_currency or order.currency

    # Active PO whose scope moved → release the old pool in its lock
    # currency, detach, then re-match below against the new scope. Skipped
    # for cancelled/rejected POs (no pool to migrate to — they just release).
    if scope_changed and desired > 0 and order.budget_id is not None:
        open_old = await _open_amount_for_order(db, order.id)
        if open_old > 0:
            await _emit_consumption(
                db,
                budget_id=order.budget_id,
                order=order,
                event="release",
                amount=open_old,
                actor_person_id=actor_person_id,
                notes=f"Released — scope change on PO {order.order_number}",
                currency=release_ccy,
            )
        order.budget_id = None
        order.budget_locked_at = None
        order.budget_released_at = None

    # No pool attached yet → match + lock for the desired amount.
    if order.budget_id is None:
        if desired > 0:
            await lock_budget_for_order(
                db, order, actor_person_id=actor_person_id,
            )
        return

    # Pool attached → write a single delta to reach `desired`.
    open_amount = await _open_amount_for_order(db, order.id)
    delta = desired - open_amount
    if delta > 0:
        # A delta LOCK only runs same-pool/same-currency (a currency change
        # is a scope move handled above), so order.currency is correct here.
        await _emit_consumption(
            db,
            budget_id=order.budget_id,
            order=order,
            event="lock",
            amount=delta,
            actor_person_id=actor_person_id,
            notes=f"Re-lock delta — PO {order.order_number} amount/status change",
        )
        order.budget_locked_at = datetime.now(UTC)
        order.budget_released_at = None
    elif delta < 0:
        await _emit_consumption(
            db,
            budget_id=order.budget_id,
            order=order,
            event="release",
            amount=-delta,
            actor_person_id=actor_person_id,
            notes=f"Release delta — PO {order.order_number} amount/status change",
            currency=release_ccy,
        )
        order.budget_released_at = (
            datetime.now(UTC) if desired <= 0 else None
        )
    await db.flush()


async def compute_budget_balance(
    db: AsyncSession, budget_id: uuid.UUID,
) -> dict[str, Any]:
    """Return remaining + activity counts for one budget."""
    bid = str(budget_id)
    budget = await db.get(ProcurementBudget, bid)
    if budget is None:
        return {"error": "not_found"}

    locks = await db.scalar(
        select(func.coalesce(func.sum(ProcurementBudgetConsumption.amount), 0))
        .where(
            ProcurementBudgetConsumption.budget_id == bid,
            ProcurementBudgetConsumption.event == "lock",
        )
    ) or Decimal(0)
    releases = await db.scalar(
        select(func.coalesce(func.sum(ProcurementBudgetConsumption.amount), 0))
        .where(
            ProcurementBudgetConsumption.budget_id == bid,
            ProcurementBudgetConsumption.event == "release",
        )
    ) or Decimal(0)
    actualized = await db.scalar(
        select(func.coalesce(func.sum(ProcurementBudgetConsumption.amount), 0))
        .where(
            ProcurementBudgetConsumption.budget_id == bid,
            ProcurementBudgetConsumption.event == "actualize",
        )
    ) or Decimal(0)

    locked = Decimal(str(locks)) - Decimal(str(releases))
    remaining = budget.total_amount - locked
    pct_used = (
        (locked / budget.total_amount * 100)
        if budget.total_amount and budget.total_amount > 0 else Decimal(0)
    )

    return {
        "id": str(budget.id),
        "total": str(budget.total_amount),
        "currency": budget.currency,
        "locked": str(locked),
        "remaining": str(remaining),
        "actualized": str(actualized),
        "pct_used": float(pct_used),
        # Threshold flags for the reminder cron / UI badges.
        "is_over_80": float(pct_used) >= 80.0,
        "is_over_95": float(pct_used) >= 95.0,
        "is_over_total": locked > budget.total_amount,
    }