"""Finance-flux HTTP surface (mounted under ``/api/v1/finance-flux``).

PR2 ships the ingestion path: finance uploads a NetSuite consolidated report
export (the SpreadsheetML ``.xls`` from cr=472 / cr=513 …), Helix parses the
report-computed consolidated USD figures and stores them per period. The
report is the source of truth — Helix never recomputes consolidation.

RBAC: ingest is a finance operation (``can_operate_finance`` — finance team,
not just global admin, per the "finance configures its own tooling" principle);
reads use ``can_view_finance``.
"""
from __future__ import annotations

import logging
import re
import uuid

from fastapi import APIRouter, Depends, File, Form, Query, Request, UploadFile
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from app.audit.service import AuditService
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.dependencies import (
    require_active_employee_for_mutations as _leaver_runtime_gate,
)
from app.core.exceptions import AppException, ForbiddenError
from app.core.idempotent_route import idempotent
from app.core.rbac import can_operate_finance, can_view_finance
from app.core.response import success_response
from app.core.upload import read_upload_bounded
from app.domains.finance_flux import (
    ai_service,
    config_service,
    editorial_service,
    flux_service,
)
from app.domains.finance_flux import constants as c
from app.domains.finance_flux.import_service import import_reports
from app.domains.finance_flux.models import FinanceFluxPeriod
from app.domains.finance_flux.report_parser import ReportParseError
from app.domains.finance_flux.schemas import (
    AccountMapUpdateBody,
    AiGenerateBody,
    AiSummaryBody,
    CardBody,
    CardUpdateBody,
    CommentaryBody,
    ConfigUpdateBody,
    FollowupBody,
    FollowupUpdateBody,
    KpiDefUpdateBody,
    KpiOverrideBody,
    LineDefUpdateBody,
)

logger = logging.getLogger(__name__)

router = APIRouter(
    prefix="/api/v1/finance-flux",
    tags=["finance-flux"],
    dependencies=[Depends(_leaver_runtime_gate)],
)

_MAX_UPLOAD = 20 * 1024 * 1024  # 20 MB — report exports are well under this
_PERIOD_RE = re.compile(r"^\d{4}-(0[1-9]|1[0-2])$")


def _trace_id(request: Request) -> str:
    return getattr(request.state, "trace_id", None) or str(uuid.uuid4())


@router.post("/import")
async def import_report_upload(
    request: Request,
    file: UploadFile = File(...),
    statement: str | None = Form(default=None),
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
    _rl: None = Depends(check_sensitive_endpoint_rate_limit),
):
    """Ingest a NetSuite consolidated report export (finance operation).

    ``statement`` optionally forces ``BS``/``PL`` when the report title is
    ambiguous (e.g. the BS Analysis Detail export); otherwise it is detected.
    """
    if not can_operate_finance(user.role_names):
        await file.close()
        raise ForbiddenError()
    if statement is not None and statement not in c.STATEMENTS:
        await file.close()
        raise AppException(
            code="FINANCE_FLUX_BAD_STATEMENT",
            message=f"statement must be one of {c.STATEMENTS}",
            status_code=422,
        )
    content = await read_upload_bounded(
        file,
        _MAX_UPLOAD,
        code="FINANCE_FLUX_UPLOAD_TOO_LARGE",
        message="report export exceeds 20 MB",
        close=True,
    )
    if not content:
        raise AppException(
            code="FINANCE_FLUX_EMPTY_UPLOAD", message="uploaded file is empty",
            status_code=422,
        )
    try:
        results = await import_reports(db, content, statement_override=statement)
    except ReportParseError as e:
        raise AppException(
            code="FINANCE_FLUX_UNPARSEABLE",
            message=f"not a valid NetSuite report export: {e}",
            status_code=422,
        ) from e
    imported = [{
        "period_key": r.period_key,
        "period_label": r.period_label,
        "statement": r.statement,
        "line_count": r.line_count,
        "created_period": r.created_period,
    } for r in results]
    latest = max(results, key=lambda r: r.period_key)
    payload = {
        # Back-compat single-period fields point at the latest period imported;
        # a multi-period export (FY P&L) reports the full closed-period range.
        "period_key": latest.period_key,
        "period_label": (
            latest.period_label if len(results) == 1
            else f"{results[0].period_label} … {results[-1].period_label}"
        ),
        "statement": latest.statement,
        "line_count": sum(r.line_count for r in results),
        "created_period": any(r.created_period for r in results),
        "period_count": len(results),
        "imported": imported,
        "source_filename": file.filename,
    }
    # Audit BEFORE the commit so the import + its audit row land in one
    # transaction (AuditService.log only flushes — committing first would
    # silently drop the audit entry).
    await AuditService(db).log(
        trace_id=_trace_id(request),
        actor_type="user",
        actor_user_id=user.id,
        action="finance_flux.report_import",
        status="success",
        resource_type="finance_flux.period",
        resource_id=latest.period_key,
        detail=payload,
    )
    await db.commit()
    return success_response(data=payload, request=request)


@router.get("/periods")
async def list_periods(
    request: Request,
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    """List ingested periods, newest first (finance / admin / auditor / mgmt)."""
    if not can_view_finance(user.role_names):
        raise ForbiddenError()
    rows = (await db.execute(
        select(FinanceFluxPeriod).order_by(FinanceFluxPeriod.period_key.desc())
    )).scalars().all()
    data = [{
        "period_key": p.period_key,
        "label": p.label,
        "is_closed": p.is_closed,
        "sync_status": p.sync_status,
        "last_synced_at": p.last_synced_at.isoformat() if p.last_synced_at else None,
    } for p in rows]
    return success_response(data=data, request=request)


def _validate_period(period: str) -> str:
    if not _PERIOD_RE.match((period or "").strip()):
        raise AppException(
            code="FINANCE_FLUX_BAD_PERIOD", message="period must be 'YYYY-MM'",
            status_code=422,
        )
    return period.strip()


@router.post("/gl-tieout")
async def gl_tieout(
    request: Request,
    period: str = Query(...),
    statement: str = Query(...),
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
    _rl: None = Depends(check_sensitive_endpoint_rate_limit),
):
    """Tie-out test: compute consolidated statement lines from NetSuite GL
    (via consolidatedexchangerate) and diff them against the manually
    imported ``.xls`` figures for the period. Read-only — this is the
    evidence for whether the manual upload can be retired. Finance op."""
    if not can_operate_finance(user.role_names):
        raise ForbiddenError()
    period = _validate_period(period)
    if statement not in c.STATEMENTS:
        raise AppException(
            code="FINANCE_FLUX_BAD_STATEMENT",
            message=f"statement must be one of {c.STATEMENTS}", status_code=422,
        )
    from app.core.coordinators import finance_flux_gl_coordinator as glc
    data = await glc.tie_out(db, period, statement)
    return success_response(data=data, request=request)


@router.post("/import-from-gl")
async def import_from_gl_endpoint(
    request: Request,
    period: str = Query(...),
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
    _rl: None = Depends(check_sensitive_endpoint_rate_limit),
):
    """Convergence (opt-in): populate the period's flux lines from the
    consolidated GL computation instead of a manual ``.xls`` upload. Use
    once ``/gl-tieout`` confirms the figures match. Finance op."""
    if not can_operate_finance(user.role_names):
        raise ForbiddenError()
    period = _validate_period(period)
    from app.core.coordinators import finance_flux_gl_coordinator as glc
    data = await glc.import_from_gl(db, period)
    # Audit BEFORE commit so imported lines + the audit row land in one
    # transaction (a skip writes only the audit row). Mirrors /import; the
    # old commit-then-log dropped the audit entry (log only flushes).
    await AuditService(db).log(
        trace_id=_trace_id(request), actor_type="user", actor_user_id=user.id,
        action="finance_flux.import_from_gl",
        status="success" if not data.get("skipped") else "skipped",
        resource_type="finance_flux.period", resource_id=period, detail=data,
    )
    await db.commit()
    return success_response(data=data, request=request)


def _validate_mode(mode: str) -> str:
    if mode not in c.COMPARE_MODES:
        raise AppException(
            code="FINANCE_FLUX_BAD_MODE",
            message=f"mode must be one of {c.COMPARE_MODES}", status_code=422,
        )
    return mode


@router.get("/statement")
async def get_statement(
    request: Request,
    period: str = Query(..., description="Period key 'YYYY-MM'."),
    statement: str = Query(..., description="BS or PL."),
    mode: str = Query(default=c.COMPARE_MOM, description="mom | yoy"),
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    """Per-line flux for one statement vs its comparison baseline."""
    if not can_view_finance(user.role_names):
        raise ForbiddenError()
    if statement not in c.STATEMENTS:
        raise AppException(
            code="FINANCE_FLUX_BAD_STATEMENT",
            message=f"statement must be one of {c.STATEMENTS}", status_code=422,
        )
    data = await flux_service.compute_statement(
        db, period_key=_validate_period(period), statement=statement,
        mode=_validate_mode(mode))
    return success_response(data=data, request=request)


@router.get("/summary")
async def get_summary(
    request: Request,
    period: str = Query(..., description="Period key 'YYYY-MM'."),
    mode: str = Query(default=c.COMPARE_MOM, description="mom | yoy"),
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    """Executive summary: KPI cards + variance-flag counts."""
    if not can_view_finance(user.role_names):
        raise ForbiddenError()
    data = await flux_service.compute_summary(
        db, period_key=_validate_period(period), mode=_validate_mode(mode))
    return success_response(data=data, request=request)


@router.get("/insights")
async def get_insights(
    request: Request,
    period: str = Query(..., description="Period key 'YYYY-MM'."),
    mode: str = Query(default=c.COMPARE_MOM, description="mom | yoy"),
    top_n: int = Query(default=8, ge=1, le=50),
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    """Auto variance rankings (top movers) for BS and P&L."""
    if not can_view_finance(user.role_names):
        raise ForbiddenError()
    _validate_mode(mode)
    period = _validate_period(period)
    data = {
        "period_key": period,
        "mode": mode,
        "top_bs": await flux_service.compute_variance_rankings(
            db, period_key=period, statement=c.STATEMENT_BS, mode=mode, top_n=top_n),
        "top_pl": await flux_service.compute_variance_rankings(
            db, period_key=period, statement=c.STATEMENT_PL, mode=mode, top_n=top_n),
    }
    return success_response(data=data, request=request)


# ─── Editorial layer + AI (finance operations) ──────────────────────


def _require_operate(user: AuthUser) -> None:
    if not can_operate_finance(user.role_names):
        raise ForbiddenError()


@router.post("/commentary")
async def set_commentary(
    body: CommentaryBody,
    request: Request,
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    """Set/clear a line's commentary and/or recon link (finance operation)."""
    _require_operate(user)
    data = await editorial_service.set_commentary(
        db, period_key=body.period, statement=body.statement, line_key=body.line_key,
        commentary=body.commentary, recon_link=body.recon_link, editor_id=user.id)
    await AuditService(db).log(
        trace_id=_trace_id(request), actor_type="user", actor_user_id=user.id,
        action="finance_flux.commentary_set", status="success",
        resource_type="finance_flux.line",
        resource_id=f"{body.period}/{body.statement}/{body.line_key}", detail={})
    await db.commit()
    return success_response(data=data, request=request)


@router.post("/ai/generate")
async def ai_generate(
    body: AiGenerateBody,
    request: Request,
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
    _rl: None = Depends(check_sensitive_endpoint_rate_limit),
):
    """Generate (and cache) the 百炼 AI flux narrative for one line."""
    _require_operate(user)
    if body.statement not in c.STATEMENTS:
        raise AppException(code="FINANCE_FLUX_BAD_STATEMENT",
                           message="bad statement", status_code=422)
    try:
        data = await ai_service.generate_narrative(
            db, period_key=body.period, statement=body.statement,
            line_key=body.line_key, mode=body.mode)
    except ai_service.FluxLineNotFound as e:
        raise AppException(code="FINANCE_FLUX_LINE_NOT_FOUND", message=str(e),
                           status_code=404) from e
    await db.commit()
    return success_response(data=data, request=request)


@router.post("/ai/summary")
async def ai_summary(
    body: AiSummaryBody,
    request: Request,
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
    _rl: None = Depends(check_sensitive_endpoint_rate_limit),
):
    """Executive 百炼 AI summary of a whole statement's flux (read-only)."""
    if not can_view_finance(user.role_names):
        raise ForbiddenError()
    if body.statement not in c.STATEMENTS:
        raise AppException(code="FINANCE_FLUX_BAD_STATEMENT",
                           message="bad statement", status_code=422)
    data = await ai_service.generate_statement_summary(
        db, period_key=body.period, statement=body.statement,
        mode=_validate_mode(body.mode))
    return success_response(data=data, request=request)


@router.post("/ai/adopt")
async def ai_adopt(
    body: CommentaryBody,
    request: Request,
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    """Adopt the cached AI narrative into the editable commentary."""
    _require_operate(user)
    data = await editorial_service.adopt_narrative(
        db, period_key=body.period, statement=body.statement,
        line_key=body.line_key, editor_id=user.id)
    await db.commit()
    return success_response(data=data, request=request)


@router.put("/kpi-override")
async def set_kpi_override(
    body: KpiOverrideBody,
    request: Request,
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    """Override a KPI card's value for a period (finance operation)."""
    _require_operate(user)
    data = await editorial_service.set_kpi_override(
        db, period_key=body.period, kpi_key=body.kpi_key, value=body.value,
        note=body.note, editor_id=user.id)
    await db.commit()
    return success_response(data=data, request=request)


@router.delete("/kpi-override")
async def clear_kpi_override(
    request: Request,
    period: str = Query(...),
    kpi_key: str = Query(...),
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    _require_operate(user)
    await editorial_service.clear_kpi_override(db, period_key=period, kpi_key=kpi_key)
    await db.commit()
    return success_response(data={"period_key": period, "kpi_key": kpi_key},
                            request=request)


@router.get("/editorial")
async def get_editorial(
    request: Request,
    period: str = Query(...),
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    """Insight cards + follow-ups for a period (Summary/Insights pages)."""
    if not can_view_finance(user.role_names):
        raise ForbiddenError()
    data = await editorial_service.list_editorial(db, period_key=period)
    return success_response(data=data, request=request)


@router.post("/cards")
# A double-click on the modal's OK used to create two cards (#2150).
# The frontend now mints an ``Idempotency-Key`` per modal-open; this is
# the half that makes the header mean something — without it the header
# was decoration. Scoped by period so the same client key against two
# months reserves two operations.
@idempotent("finance_flux.card_create", key_params=("body.period", "user.id"))
async def create_card(
    body: CardBody,
    request: Request,
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    _require_operate(user)
    data = await editorial_service.create_card(
        db, period_key=body.period, card_type=body.card_type, emoji=body.emoji,
        color=body.color, title=body.title, body=body.body, creator_id=user.id)
    await db.commit()
    return success_response(data=data, request=request)


@router.patch("/cards/{card_id}")
async def update_card(
    card_id: str,
    body: CardUpdateBody,
    request: Request,
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    _require_operate(user)
    data = await editorial_service.update_card(
        db, card_id=card_id, **body.model_dump(exclude_unset=True))
    await db.commit()
    return success_response(data=data, request=request)


@router.delete("/cards/{card_id}")
async def delete_card(
    card_id: str,
    request: Request,
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    _require_operate(user)
    await editorial_service.delete_card(db, card_id=card_id)
    await db.commit()
    return success_response(data={"id": card_id}, request=request)


@router.post("/followups")
# See ``create_card`` (#2150).
@idempotent(
    "finance_flux.followup_create", key_params=("body.period", "user.id"),
)
async def create_followup(
    body: FollowupBody,
    request: Request,
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    _require_operate(user)
    data = await editorial_service.create_followup(
        db, period_key=body.period, item=body.item, owner_label=body.owner_label,
        action=body.action, priority=body.priority, creator_id=user.id)
    await db.commit()
    return success_response(data=data, request=request)


@router.patch("/followups/{followup_id}")
async def update_followup(
    followup_id: str,
    body: FollowupUpdateBody,
    request: Request,
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    _require_operate(user)
    data = await editorial_service.update_followup(
        db, followup_id=followup_id, **body.model_dump(exclude_unset=True))
    await db.commit()
    return success_response(data=data, request=request)


@router.delete("/followups/{followup_id}")
async def delete_followup(
    followup_id: str,
    request: Request,
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    _require_operate(user)
    await editorial_service.delete_followup(db, followup_id=followup_id)
    await db.commit()
    return success_response(data={"id": followup_id}, request=request)


# ─── Finance self-service configuration ─────────────────────────────


@router.get("/config")
async def get_config(
    request: Request,
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    if not can_view_finance(user.role_names):
        raise ForbiddenError()
    return success_response(data=await config_service.get_config(db), request=request)


@router.put("/config")
async def update_config(
    body: ConfigUpdateBody,
    request: Request,
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    """Update flux config — thresholds / NS scope / comparison / AI (finance)."""
    _require_operate(user)
    if body.comparison_modes is not None and any(
        m not in c.COMPARE_MODES for m in body.comparison_modes
    ):
        raise AppException(code="FINANCE_FLUX_BAD_MODE",
                           message=f"comparison_modes ⊆ {c.COMPARE_MODES}",
                           status_code=422)
    data = await config_service.update_config(
        db, editor_id=user.id, **body.model_dump(exclude_unset=True))
    await AuditService(db).log(
        trace_id=_trace_id(request), actor_type="user", actor_user_id=user.id,
        action="finance_flux.config_update", status="success",
        resource_type="finance_flux.config", resource_id="singleton",
        # ``mode="json"`` coerces Decimal → string. Without it the raw
        # ``amount_threshold`` / ``pct_threshold`` Decimals reached
        # ``audit_logs.detail`` (a JSON column whose serializer is a bare
        # ``json.dumps`` — no ``default=``) and every save carrying a
        # threshold 500'd at the audit flush, rolling the config edit
        # back. ``AuditService.log``'s 16 KiB guard probes with
        # ``default=str``, so it does not catch this. Same failure mode
        # as the asset-inventory ``due_at`` datetime bug (#3357).
        detail=body.model_dump(exclude_unset=True, mode="json"))
    await db.commit()
    return success_response(data=data, request=request)


@router.get("/line-defs")
async def list_line_defs(
    request: Request,
    statement: str | None = Query(default=None),
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    if not can_view_finance(user.role_names):
        raise ForbiddenError()
    return success_response(
        data=await config_service.list_line_defs(db, statement=statement),
        request=request)


@router.patch("/line-defs/{line_def_id}")
async def update_line_def(
    line_def_id: str,
    body: LineDefUpdateBody,
    request: Request,
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    _require_operate(user)
    data = await config_service.update_line_def(
        db, line_def_id=line_def_id, **body.model_dump(exclude_unset=True))
    await db.commit()
    return success_response(data=data, request=request)


@router.get("/kpi-defs")
async def list_kpi_defs(
    request: Request,
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    if not can_view_finance(user.role_names):
        raise ForbiddenError()
    return success_response(data=await config_service.list_kpi_defs(db), request=request)


@router.patch("/kpi-defs/{kpi_def_id}")
async def update_kpi_def(
    kpi_def_id: str,
    body: KpiDefUpdateBody,
    request: Request,
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    _require_operate(user)
    data = await config_service.update_kpi_def(
        db, kpi_def_id=kpi_def_id, **body.model_dump(exclude_unset=True))
    await db.commit()
    return success_response(data=data, request=request)


@router.get("/account-map")
async def list_account_map(
    request: Request,
    statement: str | None = Query(default=None),
    unmapped_only: bool = Query(default=False),
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    if not can_view_finance(user.role_names):
        raise ForbiddenError()
    return success_response(
        data=await config_service.list_account_map(
            db, statement=statement, unmapped_only=unmapped_only),
        request=request)


@router.patch("/account-map/{entry_id}")
async def update_account_map(
    entry_id: str,
    body: AccountMapUpdateBody,
    request: Request,
    user: AuthUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
):
    _require_operate(user)
    data = await config_service.update_account_map_entry(
        db, entry_id=entry_id, line_key=body.line_key, sign=body.sign,
        is_active=body.is_active)
    await db.commit()
    return success_response(data=data, request=request)