"""资金管理API — 资金缺口预测 + 收付款计划 + 预警 + 应收催收闭环 + 网银流水导入 (资金管理智能体)"""
import io
import json
import logging
import os
from datetime import datetime, timedelta
from pathlib import Path
from fastapi import APIRouter, HTTPException, Depends, Query, Request, UploadFile, File
from fastapi.responses import FileResponse
from sqlalchemy.orm import Session
from sqlalchemy import or_
import pandas as pd
from app.database import get_db
from app.deps import get_entity_id, resolve_entity_for_request
from app.auth_middleware import require_role
from app.models import CashPlan
from app.utils.cash_forecast_engine import (
    forecast_cash_flow_with_plans,
    check_cash_alerts,
    DEFAULT_CASH_WARNING,
    DEFAULT_CASH_CRITICAL,
)

logger = logging.getLogger("cma.cash")

router = APIRouter(
    prefix="/api/cma/cash",
    tags=["资金管理"],
    dependencies=[Depends(require_role("ceo", "finance", "business", "it"))],
)


def _aging_bucket(days_late: int) -> str:
    """账龄区间（按逾期天数）：未到期 / 0-30天 / 30-60天 / 60-90天 / 90天以上"""
    if days_late <= 0:
        return "未到期"
    if days_late <= 30:
        return "0-30天"
    if days_late <= 60:
        return "30-60天"
    if days_late <= 90:
        return "60-90天"
    return "90天以上"


def _plan_dict(p: CashPlan) -> dict:
    today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
    due = p.plan_date.replace(hour=0, minute=0, second=0, microsecond=0) if p.plan_date else today
    days_late = (today - due).days
    paid = round(p.paid_amount or 0, 2)
    balance = round((p.amount or 0) - paid, 2)
    if p.plan_type == "receive":
        overdue = p.status == "pending" and due < today
        if p.status == "pending":
            aging = _aging_bucket(days_late)
        elif p.status == "completed":
            aging = "已结清"
        else:
            aging = "已取消"
    else:
        overdue = False
        aging = "—"
    return {
        "id": p.id,
        "entity_id": p.entity_id,
        "plan_type": p.plan_type,
        "plan_type_label": "收款" if p.plan_type == "receive" else "付款",
        "amount": round(p.amount or 0, 2),
        "paid_amount": paid,
        "receivable_balance": max(balance, 0),
        "plan_date": p.plan_date.strftime("%Y-%m-%d") if p.plan_date else "",
        "counterparty": p.counterparty or "",
        "description": p.description or "",
        "status": p.status,
        "status_label": {"pending": "待执行", "completed": "已完成", "cancelled": "已取消"}.get(p.status, p.status),
        "owner": p.owner or "",
        "source": p.source or "manual",
        "overdue": overdue,
        "overdue_days": max(days_late, 0) if overdue else 0,
        "aging_bucket": aging,
        "completed_at": p.completed_at.strftime("%Y-%m-%d %H:%M") if p.completed_at else None,
        "created_at": p.created_at.strftime("%Y-%m-%d %H:%M") if p.created_at else None,
    }


# ══════════════════════════════════════════════════════════
# 1. 资金缺口预测
# ══════════════════════════════════════════════════════════

@router.get("/gap-forecast")
def api_gap_forecast(
    entity_id: int = Depends(get_entity_id),
    days: int = Query(30, ge=1, le=90, description="预测天数"),
    current_cash: float = Query(None, description="当前现金余额(万元)，不传则自动获取"),
    db: Session = Depends(get_db),
):
    """资金缺口预测 — 趋势引擎 + 收付款计划，识别余额<警戒线的缺口日期"""
    try:
        return forecast_cash_flow_with_plans(entity_id, db, days=days, current_cash=current_cash)
    except Exception as e:
        logger.error(f"资金缺口预测失败: {e}", exc_info=True)
        raise HTTPException(400, f"资金缺口预测失败: {str(e)}")


@router.get("/balance")
def api_get_balance(
    entity_id: int = Depends(get_entity_id),
    db: Session = Depends(get_db),
):
    """获取当前现金余额（预测基线）"""
    from app.utils.cash_forecast_engine import get_current_cash_balance
    value = get_current_cash_balance(db, entity_id)
    return {"entity_id": entity_id, "current_cash": value}


@router.post("/balance")
def api_set_balance(
    data: dict,
    db: Session = Depends(get_db),
):
    """设置当前现金余额（万元），作为预测基线"""
    from app.utils.cash_forecast_engine import set_current_cash_balance
    value = float(data.get("current_cash", 0))
    if value < 0:
        raise HTTPException(400, "现金余额不能为负")
    set_current_cash_balance(db, value)
    return {"message": "当前现金余额已更新", "current_cash": value}


# ══════════════════════════════════════════════════════════
# 2. 收付款计划 CRUD
# ══════════════════════════════════════════════════════════

@router.get("/plans")
def api_list_plans(
    entity_id: int = Depends(get_entity_id),
    plan_type: str = Query(None, description="receive/pay"),
    status: str = Query(None, description="pending/completed/cancelled"),
    month: str = Query(None, description="YYYY-MM 按计划月份过滤"),
    page: int = Query(1, ge=1),
    page_size: int = Query(100, ge=1, le=500),
    db: Session = Depends(get_db),
):
    """收付款计划列表"""
    query = db.query(CashPlan).filter(CashPlan.entity_id == entity_id)
    if plan_type:
        query = query.filter(CashPlan.plan_type == plan_type)
    if status:
        query = query.filter(CashPlan.status == status)
    if month:
        try:
            y, m = int(month[:4]), int(month[5:7])
            start = datetime(y, m, 1)
            end = (start + timedelta(days=32)).replace(day=1)
            query = query.filter(CashPlan.plan_date >= start, CashPlan.plan_date < end)
        except Exception:
            raise HTTPException(400, "month格式应为YYYY-MM")
    total = query.count()
    plans = query.order_by(CashPlan.plan_date.asc(), CashPlan.id.desc()) \
        .offset((page - 1) * page_size).limit(page_size).all()
    return {"total": total, "data": [_plan_dict(p) for p in plans]}


@router.post("/plans")
def api_create_plan(request: Request, data: dict, db: Session = Depends(get_db)):
    """新建收付款计划"""
    plan_type = data.get("plan_type")
    if plan_type not in ("receive", "pay"):
        raise HTTPException(400, "plan_type必须为receive(收)或pay(付)")
    amount = float(data.get("amount", 0))
    if amount <= 0:
        raise HTTPException(400, "金额必须大于0")
    date_str = data.get("plan_date")
    if not date_str:
        raise HTTPException(400, "缺少计划日期")
    try:
        plan_date = datetime.strptime(str(date_str)[:10], "%Y-%m-%d")
    except Exception:
        raise HTTPException(400, "plan_date格式应为YYYY-MM-DD")

    plan = CashPlan(
        entity_id=resolve_entity_for_request(request, int(data.get("entity_id", 1))),
        plan_type=plan_type,
        amount=amount,
        plan_date=plan_date,
        counterparty=(data.get("counterparty") or "").strip(),
        description=(data.get("description") or "").strip(),
        status=data.get("status", "pending"),
        owner=(data.get("owner") or "").strip() or None,
        source=(data.get("source") or "manual").strip(),
        paid_amount=float(data.get("paid_amount") or 0),
    )
    db.add(plan)
    db.commit()
    db.refresh(plan)
    logger.info(f"新建收付款计划 #{plan.id} [{plan_type}] {amount}万 {date_str}")
    return {"message": "收付款计划已创建", "data": _plan_dict(plan)}


@router.put("/plans/{plan_id}")
def api_update_plan(plan_id: int, data: dict, db: Session = Depends(get_db)):
    """更新收付款计划"""
    plan = db.query(CashPlan).filter(CashPlan.id == plan_id).first()
    if not plan:
        raise HTTPException(404, "计划不存在")
    if "plan_type" in data:
        if data["plan_type"] not in ("receive", "pay"):
            raise HTTPException(400, "plan_type必须为receive或pay")
        plan.plan_type = data["plan_type"]
    if "amount" in data:
        amount = float(data["amount"])
        if amount <= 0:
            raise HTTPException(400, "金额必须大于0")
        plan.amount = amount
    if "plan_date" in data and data["plan_date"]:
        try:
            plan.plan_date = datetime.strptime(str(data["plan_date"])[:10], "%Y-%m-%d")
        except Exception:
            raise HTTPException(400, "plan_date格式应为YYYY-MM-DD")
    if "counterparty" in data:
        plan.counterparty = (data["counterparty"] or "").strip()
    if "description" in data:
        plan.description = (data["description"] or "").strip()
    if "owner" in data:
        plan.owner = (data["owner"] or "").strip() or None
    if "source" in data:
        plan.source = (data["source"] or "manual").strip()
    if "paid_amount" in data:
        plan.paid_amount = float(data["paid_amount"] or 0)
    if "status" in data:
        plan.status = data["status"]
        if data["status"] == "completed" and not plan.completed_at:
            plan.completed_at = datetime.now()
        elif data["status"] in ("pending", "cancelled"):
            plan.completed_at = None
    db.commit()
    db.refresh(plan)
    # 完成收款时联动：消除到期未收款预警 + 催收行动 + F_AR_DAYS
    if plan.plan_type == "receive" and plan.status == "completed":
        try:
            _resolve_plan_alerts(db, plan.entity_id, plan.id, plan.paid_amount or plan.amount)
            _sync_collection_action_plan(db, plan.entity_id)
            _update_ar_days_kpi(db, plan.entity_id)
            db.commit()
        except Exception as e:
            logger.warning(f"完成收款联动失败: {e}")
    return {"message": "计划已更新", "data": _plan_dict(plan)}


@router.delete("/plans/{plan_id}")
def api_delete_plan(plan_id: int, db: Session = Depends(get_db)):
    """删除收付款计划"""
    plan = db.query(CashPlan).filter(CashPlan.id == plan_id).first()
    if not plan:
        raise HTTPException(404, "计划不存在")
    db.delete(plan)
    db.commit()
    return {"message": "计划已删除"}


@router.post("/plans/{plan_id}/complete")
def api_complete_plan(plan_id: int, db: Session = Depends(get_db)):
    """标记计划为已完成（收款到账/付款完成）"""
    plan = db.query(CashPlan).filter(CashPlan.id == plan_id).first()
    if not plan:
        raise HTTPException(404, "计划不存在")
    plan.status = "completed"
    plan.completed_at = datetime.now()
    if plan.plan_type == "receive" and not plan.paid_amount:
        plan.paid_amount = plan.amount
    db.commit()
    db.refresh(plan)
    if plan.plan_type == "receive":
        try:
            _resolve_plan_alerts(db, plan.entity_id, plan.id, plan.paid_amount or plan.amount)
            _sync_collection_action_plan(db, plan.entity_id)
            _update_ar_days_kpi(db, plan.entity_id)
            db.commit()
        except Exception as e:
            logger.warning(f"完成收款联动失败: {e}")
    return {"message": "已标记完成", "data": _plan_dict(plan)}


# ══════════════════════════════════════════════════════════
# 3. 到期提醒 + 页面看板
# ══════════════════════════════════════════════════════════

@router.get("/upcoming")
def api_upcoming(
    entity_id: int = Depends(get_entity_id),
    days: int = Query(7, ge=1, le=30),
    db: Session = Depends(get_db),
):
    """未来N天到期提醒 + 已逾期未收/未付"""
    today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
    upcoming = db.query(CashPlan).filter(
        CashPlan.entity_id == entity_id,
        CashPlan.status == "pending",
        CashPlan.plan_date >= today,
        CashPlan.plan_date <= today + timedelta(days=days),
    ).order_by(CashPlan.plan_date.asc()).all()
    overdue = db.query(CashPlan).filter(
        CashPlan.entity_id == entity_id,
        CashPlan.status == "pending",
        CashPlan.plan_date < today,
    ).order_by(CashPlan.plan_date.asc()).all()
    return {
        "days": days,
        "upcoming": [_plan_dict(p) for p in upcoming],
        "overdue": [_plan_dict(p) for p in overdue],
        "overdue_receive_amount": round(sum(p.amount for p in overdue if p.plan_type == "receive"), 2),
        "overdue_pay_amount": round(sum(p.amount for p in overdue if p.plan_type == "pay"), 2),
    }


@router.get("/dashboard")
def api_cash_dashboard(
    entity_id: int = Depends(get_entity_id),
    month: str = Query(None, description="YYYY-MM 默认本月"),
    days: int = Query(30),
    current_cash: float = Query(None, description="当前现金余额(万元)"),
    db: Session = Depends(get_db),
):
    """收付款计划页面看板 — 日历汇总 + 预测 + 到期提醒"""
    today = datetime.now()
    if month:
        try:
            y, m = int(month[:4]), int(month[5:7])
        except Exception:
            raise HTTPException(400, "month格式应为YYYY-MM")
    else:
        y, m = today.year, today.month
    start = datetime(y, m, 1)
    end = (start + timedelta(days=32)).replace(day=1)

    plans = db.query(CashPlan).filter(
        CashPlan.entity_id == entity_id,
        CashPlan.status == "pending",
        CashPlan.plan_date >= start,
        CashPlan.plan_date < end,
    ).all()

    # 日历：按天汇总 应收/应付
    calendar = {}
    for p in plans:
        dkey = p.plan_date.strftime("%Y-%m-%d")
        cell = calendar.setdefault(dkey, {"receive": 0.0, "pay": 0.0, "items": []})
        if p.plan_type == "receive":
            cell["receive"] += p.amount
        else:
            cell["pay"] += p.amount
        cell["items"].append(_plan_dict(p))

    # 本月合计
    month_receive = round(sum(p.amount for p in plans if p.plan_type == "receive"), 2)
    month_pay = round(sum(p.amount for p in plans if p.plan_type == "pay"), 2)

    forecast = forecast_cash_flow_with_plans(entity_id, db, days=days, current_cash=current_cash)
    upcoming = api_upcoming(entity_id, 7, db)

    return {
        "entity_id": entity_id,
        "month": f"{y:04d}-{m:02d}",
        "month_receive": month_receive,
        "month_pay": month_pay,
        "month_net": round(month_receive - month_pay, 2),
        "calendar": calendar,
        "forecast": forecast,
        "upcoming": upcoming,
    }


# ══════════════════════════════════════════════════════════
# 4. 资金预警 — 缺口前3天预警 + 到期未收款提醒
# ══════════════════════════════════════════════════════════

@router.post("/check-alerts")
def api_check_cash_alerts(entity_id: int = Depends(get_entity_id), db: Session = Depends(get_db)):
    """手动触发资金预警检查（写入预警中心kpi_alerts）"""
    try:
        return check_cash_alerts(db, entity_id=entity_id)
    except Exception as e:
        logger.error(f"资金预警检查失败: {e}", exc_info=True)
        raise HTTPException(400, f"资金预警检查失败: {str(e)}")


@router.get("/alerts/status")
def api_cash_alert_status(entity_id: int = Depends(get_entity_id), db: Session = Depends(get_db)):
    """资金预警状态概览 — 当前缺口/逾期情况（不写库，只读）"""
    result = forecast_cash_flow_with_plans(entity_id, db, days=30)
    return {
        "entity_id": entity_id,
        "warning_line": result["warning_line"],
        "critical_line": result["critical_line"],
        "gap_dates": result["gap_dates"],
        "pre_alerts": result["pre_alerts"],
        "min_cash": result["min_cash"],
        "min_cash_date": result["min_cash_date"],
        "summary": result["summary"],
    }


# ══════════════════════════════════════════════════════════
# 5. 应收催收闭环 — 催收视图 / 回款登记 / 博海数据录入
#    唯一应收载体：cash_plans(plan_type=receive)
# ══════════════════════════════════════════════════════════

# 博海应收款汇总表（2026H1期末余额，按业务员）— 录入源数据
# 来源: 应收款汇总表(业务员+客户).xlsx / bohai_comprehensive_report.md
BOHAI_AR_DATA = [
    # (业务员, 期末余额元, 业务说明)
    ("董均国", 387676, "IT业务-重点催收"),
    ("蒋亚文", 529786, "IT业务-重点催收"),
    ("陈艳", 89949, "IT业务"),
    ("李亚玲", 92450, "IT业务"),
    ("李巧玲", 45006, "IT业务"),
    ("贾妮", 27820, "IT业务"),
    ("王平安", 31298, "IT业务"),
    ("任富海", 26978, "IT业务"),
    ("其他", 6559, "IT业务"),
    ("王婧", 1065000, "酣客酒类(独立核算)"),
]


def _resolve_plan_alerts(db: Session, entity_id: int, plan_id: int, amount: float) -> int:
    """回款登记后自动消除该计划的【到期未收款】预警（kpi_alerts.alert_type=cash_plan）"""
    from app.models import KPIAlert
    alerts = db.query(KPIAlert).filter(
        KPIAlert.alert_type == "cash_plan",
        KPIAlert.status.in_(["pending", "processing"]),
    ).all()
    resolved = 0
    for a in alerts:
        try:
            sug = json.loads(a.suggestion or "{}")
        except Exception:
            continue
        if sug.get("plan_id") == plan_id:
            a.status = "resolved"
            a.resolution = f"回款登记+{amount:.2f}万，系统自动消除"
            a.resolved_at = datetime.now()
            resolved += 1
    if resolved:
        db.commit()
    return resolved


def _sync_collection_action_plan(db: Session, entity_id: int):
    """逾期应收 → 『应收账款催收行动』联动：有逾期→in_progress，全部结清→completed"""
    from app.models import ActionPlan
    today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
    plans = db.query(CashPlan).filter(
        CashPlan.entity_id == entity_id,
        CashPlan.plan_type == "receive",
    ).all()
    pending = [p for p in plans if p.status == "pending"]
    overdue = [p for p in pending if p.plan_date < today]
    total_amount = sum(p.amount or 0 for p in plans)
    paid_amount = sum(p.paid_amount or 0 for p in plans)
    progress = round(paid_amount / total_amount * 100) if total_amount > 0 else 0
    if overdue:
        new_status = "in_progress"
    elif not pending:
        new_status = "completed"
        progress = 100
    else:
        new_status = "in_progress"

    ap = db.query(ActionPlan).filter(
        ActionPlan.title.like("%催收%"),
        ActionPlan.status != "cancelled",
    ).order_by(ActionPlan.id.asc()).first()
    if not ap:
        return
    changed = False
    if ap.status != new_status:
        ap.status = new_status
        changed = True
    if ap.progress is None or progress > (ap.progress or 0):
        ap.progress = progress
        changed = True
    if changed:
        db.commit()
        logger.info(f"催收行动#{ap.id} 联动: status={new_status} progress={progress}%")


def _update_ar_days_kpi(db: Session, entity_id: int):
    """联动F_AR_DAYS（应收周转天数KPI）— 按当前应收余额重算当期值 = 应收余额/月营收×30"""
    from app.models import KPIDefinition, KPIValue
    kpi = db.query(KPIDefinition).filter(
        KPIDefinition.entity_id == entity_id,
        KPIDefinition.kpi_code == "F_AR_DAYS",
    ).first()
    if not kpi:
        return
    plans = db.query(CashPlan).filter(
        CashPlan.entity_id == entity_id,
        CashPlan.plan_type == "receive",
        CashPlan.status == "pending",
    ).all()
    ar_balance_wan = round(sum((p.amount or 0) - (p.paid_amount or 0) for p in plans), 2)
    period = datetime.now().strftime("%Y-%m")
    monthly_rev = 100.0  # 缺省月营收(万元)
    rev_kpi = db.query(KPIDefinition).filter(
        KPIDefinition.entity_id == entity_id,
        KPIDefinition.kpi_code == "F_REVENUE",
    ).first()
    if rev_kpi:
        val = db.query(KPIValue).filter(
            KPIValue.kpi_id == rev_kpi.id,
            KPIValue.period == period,
            KPIValue.actual_value.isnot(None),
        ).order_by(KPIValue.id.desc()).first()
        if val and val.actual_value:
            monthly_rev = float(val.actual_value)
    ar_days = round(ar_balance_wan / monthly_rev * 30, 1) if monthly_rev > 0 else None
    if ar_days is None:
        return
    remark = f"应收催收闭环联动: 应收余额{ar_balance_wan:.2f}万/月营收{monthly_rev:.1f}万×30天"
    existing = db.query(KPIValue).filter(
        KPIValue.kpi_id == kpi.id,
        KPIValue.period == period,
    ).order_by(KPIValue.id.desc()).first()
    if existing:
        existing.actual_value = ar_days
        existing.source_type = "cash_plan"
        existing.remark = remark
        existing.calculated_at = datetime.now()
    else:
        db.add(KPIValue(
            kpi_id=kpi.id,
            period=period,
            actual_value=ar_days,
            source_type="cash_plan",
            data_status="calculated",
            remark=remark,
        ))
    db.commit()
    logger.info(f"F_AR_DAYS联动更新: {period} = {ar_days}天 (应收{ar_balance_wan}万)")


@router.get("/receivables")
def api_receivables(
    entity_id: int = Depends(get_entity_id),
    owner: str = Query(None, description="按负责人/业务员筛选"),
    status: str = Query(None, description="pending/completed/cancelled/overdue"),
    aging: str = Query(None, description="账龄: 未到期/0-30天/30-60天/60-90天/90天以上/已结清"),
    keyword: str = Query(None, description="客户/说明关键字"),
    db: Session = Depends(get_db),
):
    """应收催收视图 — 应收余额 + 账龄(30/60/90) + 逾期状态 + 负责人筛选"""
    query = db.query(CashPlan).filter(
        CashPlan.entity_id == entity_id,
        CashPlan.plan_type == "receive",
    )
    if owner:
        query = query.filter(CashPlan.owner == owner)
    if keyword:
        kw = f"%{keyword}%"
        query = query.filter(or_(CashPlan.counterparty.like(kw), CashPlan.description.like(kw)))

    plans = query.order_by(CashPlan.plan_date.asc(), CashPlan.id.asc()).all()
    items = []
    total_amount = total_paid = 0.0
    pending_balance = 0.0
    overdue_count = 0
    overdue_amount = 0.0
    aging_amount = {}
    completed_amount = 0.0
    for p in plans:
        d = _plan_dict(p)
        if status:
            if status == "overdue":
                if not d["overdue"]:
                    continue
            elif d["status"] != status:
                continue
        if aging and d["aging_bucket"] != aging:
            continue
        items.append(d)
        total_amount += d["amount"]
        total_paid += d["paid_amount"]
        if d["status"] == "pending":
            pending_balance += d["receivable_balance"]
        if d["overdue"]:
            overdue_count += 1
            overdue_amount += d["receivable_balance"]
        if d["status"] == "pending":
            aging_amount[d["aging_bucket"]] = aging_amount.get(d["aging_bucket"], 0) + d["receivable_balance"]
        elif d["status"] == "completed":
            completed_amount += d["amount"]

    owners = [r[0] for r in db.query(CashPlan.owner).filter(
        CashPlan.entity_id == entity_id,
        CashPlan.plan_type == "receive",
        CashPlan.owner.isnot(None),
        CashPlan.owner != "",
    ).distinct().order_by(CashPlan.owner.asc()).all()]

    return {
        "entity_id": entity_id,
        "total": len(items),
        "owners": owners,
        "summary": {
            "total_amount": round(total_amount, 2),
            "total_paid": round(total_paid, 2),
            "total_balance": round(total_amount - total_paid, 2),
            "pending_balance": round(pending_balance, 2),
            "overdue_count": overdue_count,
            "overdue_amount": round(overdue_amount, 2),
            "completed_amount": round(completed_amount, 2),
            "aging": {k: round(v, 2) for k, v in sorted(aging_amount.items(), key=lambda x: x[0])},
        },
        "data": items,
    }


@router.post("/receivables/{plan_id}/payment")
def api_register_payment(plan_id: int, data: dict, db: Session = Depends(get_db)):
    """回款登记 — 更新计划状态 + 自动消除到期未收款预警 + 联动催收行动/F_AR_DAYS"""
    plan = db.query(CashPlan).filter(CashPlan.id == plan_id).first()
    if not plan:
        raise HTTPException(404, "应收计划不存在")
    if plan.plan_type != "receive":
        raise HTTPException(400, "仅应收(receive)计划支持回款登记")
    if plan.status == "cancelled":
        raise HTTPException(400, "已取消的计划不能登记回款")
    amount = float(data.get("amount", 0))
    if amount <= 0:
        raise HTTPException(400, "回款金额必须大于0")
    paid_date_str = str(data.get("paid_date") or "")[:10]
    if not paid_date_str:
        paid_date_str = datetime.now().strftime("%Y-%m-%d")
    try:
        paid_date = datetime.strptime(paid_date_str, "%Y-%m-%d")
    except Exception:
        raise HTTPException(400, "paid_date格式应为YYYY-MM-DD")

    balance = round((plan.amount or 0) - (plan.paid_amount or 0), 2)
    if amount > balance + 1e-9:
        raise HTTPException(400, f"回款金额{amount}万超过应收余额{balance}万")

    plan.paid_amount = round((plan.paid_amount or 0) + amount, 2)
    if plan.paid_amount >= (plan.amount or 0) - 1e-9:
        plan.paid_amount = plan.amount
        plan.status = "completed"
        plan.completed_at = paid_date
    else:
        plan.status = "pending"
        plan.completed_at = None
    db.commit()

    entity_id = plan.entity_id
    resolved = _resolve_plan_alerts(db, entity_id, plan.id, amount)
    _sync_collection_action_plan(db, entity_id)
    _update_ar_days_kpi(db, entity_id)
    db.commit()
    db.refresh(plan)
    logger.info(f"回款登记 #{plan.id} {plan.counterparty or ''} +{amount}万 → {plan.status}, 消除预警{resolved}条")
    return {
        "message": "回款登记成功",
        "resolved_alerts": resolved,
        "data": _plan_dict(plan),
    }


@router.post("/import/bohai-ar")
def api_import_bohai_ar(data: dict = None, entity_id: int = Depends(get_entity_id), db: Session = Depends(get_db)):
    """录入博海应收汇总表数据(¥2.3M, 按业务员)到cash_plans — 幂等，重复调用不重复导入"""
    if data is None:
        data = {}
    existing = db.query(CashPlan).filter(
        CashPlan.source == "bohai_ar",
        CashPlan.entity_id == entity_id,
    ).count()
    if existing and not data.get("force"):
        return {"message": "博海应收已录入，未重复导入", "existing": existing, "imported": 0}
    if existing:
        db.query(CashPlan).filter(
            CashPlan.source == "bohai_ar",
            CashPlan.entity_id == entity_id,
        ).delete()
        db.commit()
    plan_date = datetime(2026, 6, 30)  # 2026H1期末余额基准日
    imported = 0
    total_wan = 0.0
    for sp, yuan, note in BOHAI_AR_DATA:
        wan = round(yuan / 10000, 2)
        total_wan += wan
        db.add(CashPlan(
            entity_id=entity_id,
            plan_type="receive",
            amount=wan,
            plan_date=plan_date,
            counterparty=sp,
            description=f"博海应收款汇总表2026H1期末余额({note})",
            status="pending",
            owner=sp,
            source="bohai_ar",
            paid_amount=0,
        ))
        imported += 1
    db.commit()
    # 触发预警检查生成到期未收款预警 + 联动催收行动/F_AR_DAYS
    try:
        check_cash_alerts(db, entity_id=entity_id)
    except Exception as e:
        logger.warning(f"导入后预警检查失败: {e}")
    _sync_collection_action_plan(db, entity_id)
    _update_ar_days_kpi(db, entity_id)
    db.commit()
    return {
        "message": "博海应收已录入",
        "imported": imported,
        "total_amount_wan": round(total_wan, 2),
        "entity_id": entity_id,
    }


# ══════════════════════════════════════════════════════════
# 6. 网银流水标准导入 — 三校验规则 + 现金流余额联动 (P1方案② 2026-08-28)
#    模板列: 凭证日期/凭证号/科目编码/科目名称/借方金额/贷方金额/摘要
# ══════════════════════════════════════════════════════════

_TEMPLATE_PATH = Path(__file__).resolve().parents[2] / "scripts" / "templates" / "网银流水导入模板.xlsx"
_TEMPLATE_PATH = str(_TEMPLATE_PATH)  # FileResponse/os.path.exists 兼容 str

# 模板列别名映射（兼容中英文列名）
_VOUCHER_COL_ALIASES = {
    "voucher_date": ["凭证日期", "日期", "voucher_date", "date"],
    "voucher_no": ["凭证号", "凭证编号", "凭证字号", "voucher_no"],
    "subject_code": ["科目编码", "科目代码", "subject_code", "code"],
    "subject_name": ["科目名称", "subject_name", "name"],
    "debit_amount": ["借方金额", "借方", "debit_amount", "debit"],
    "credit_amount": ["贷方金额", "贷方", "credit_amount", "credit"],
    "summary": ["摘要", "备注", "summary", "remark"],
}


def _resolve_voucher_cols(cols: list) -> dict:
    """列名归一化：返回 {字段: 实际列名}（不区分大小写/空格）"""
    col_map = {}
    for c in cols:
        key = str(c).strip().lower()
        if key and key not in col_map:
            col_map[key] = str(c)
    resolved = {}
    for field, aliases in _VOUCHER_COL_ALIASES.items():
        for alias in aliases:
            if alias.lower() in col_map:
                resolved[field] = col_map[alias.lower()]
                break
    return resolved


def _norm_str(raw) -> str:
    """单元格→干净字符串：NaN/None→空；整数float→去.0（如1002.0→1002）"""
    if raw is None:
        return ""
    if isinstance(raw, float) and pd.isna(raw):
        return ""
    if isinstance(raw, (int, float)) and not isinstance(raw, bool):
        f = float(raw)
        return str(int(f)) if f.is_integer() else str(f)
    s = str(raw).strip()
    return "" if s.lower() in ("nan", "none") else s


def _is_carry_forward(summary: str, subject_name: str) -> bool:
    """结转行识别：摘要含'结转' 或 科目名称含'本年利润'/'结转'"""
    return ("结转" in summary) or ("本年利润" in subject_name) or ("结转" in subject_name)


def _sync_cash_balance_from_ledger(db: Session, entity_id: int, batch: str) -> float:
    """货币资金类科目(1001/1002开头)期末余额 → set_current_cash_balance（万元）"""
    from app.models import VoucherDetail
    from app.utils.cash_forecast_engine import set_current_cash_balance
    rows = db.query(VoucherDetail).filter(VoucherDetail.entity_id == entity_id).all()
    balance_yuan = round(sum(
        (r.debit_amount or 0) - (r.credit_amount or 0)
        for r in rows
        if r.carry_forward == 0
        and r.subject_code
        and (r.subject_code.startswith("1001") or r.subject_code.startswith("1002"))
    ), 2)
    cash_wan = round(balance_yuan / 10000, 4)
    set_current_cash_balance(db, cash_wan)
    logger.info(f"网银流水导入[{batch}] 货币资金期末余额{balance_yuan}元 = {cash_wan}万元 → 现金余额联动")
    return cash_wan


def _sync_cash_kpis(db: Session, entity_id: int, batch: str, periods: list) -> list:
    """现金流KPI联动：
    ① EXT_现金类KPI → 货币资金科目期末余额（元，与存量口径一致，source_type=ledger）
    ② F_CASH_SAFETY 现金安全垫（万元 = 货币资金余额 - 短期借款EXT_139，entity_id隔离，不存在则创建）
    """
    from app.models import KPIDefinition, KPIValue, VoucherDetail
    updates = []
    period = periods[-1] if periods else datetime.now().strftime("%Y-%m")

    rows = db.query(VoucherDetail).filter(VoucherDetail.entity_id == entity_id).all()
    monetary = [
        r for r in rows
        if r.carry_forward == 0 and r.subject_code
        and (r.subject_code.startswith("1001") or r.subject_code.startswith("1002"))
    ]
    balance_yuan = round(sum((r.debit_amount or 0) - (r.credit_amount or 0) for r in monetary), 2)
    cash_1001 = round(sum((r.debit_amount or 0) - (r.credit_amount or 0)
                          for r in monetary if r.subject_code.startswith("1001")), 2)
    cash_1002 = round(sum((r.debit_amount or 0) - (r.credit_amount or 0)
                          for r in monetary if r.subject_code.startswith("1002")), 2)
    cash_wan = round(balance_yuan / 10000, 2)

    # ── ① EXT_现金类KPI（名称含'现金'/'货币资金'，active；排除F_CASH_SAFETY，由②专用逻辑按万元口径处理）──
    cash_kpis = db.query(KPIDefinition).filter(
        KPIDefinition.entity_id == entity_id,
        or_(KPIDefinition.kpi_name.like("%现金%"), KPIDefinition.kpi_name.like("%货币资金%")),
        KPIDefinition.status == "active",
        KPIDefinition.kpi_code != "F_CASH_SAFETY",
    ).all()
    for k in cash_kpis:
        if "库存现金" in k.kpi_name:
            val = cash_1001
        elif "银行" in k.kpi_name:
            val = cash_1002
        else:
            val = balance_yuan
        remark = f"网银流水导入[{batch}]联动: 货币资金科目期末余额{balance_yuan}元(库存现金{cash_1001}/银行存款{cash_1002})"
        existing = db.query(KPIValue).filter(
            KPIValue.kpi_id == k.id,
            KPIValue.period == period,
            KPIValue.source_type == "ledger",
        ).order_by(KPIValue.id.desc()).first()
        if existing:
            existing.actual_value = val
            existing.source_batch = batch  # type: ignore[assignment]  # SQLAlchemy Column类型推断噪音
            existing.remark = remark
            existing.calculated_at = datetime.now()
        else:
            db.add(KPIValue(
                entity_id=entity_id,
                kpi_id=k.id,
                period=period,
                actual_value=val,
                source_type="ledger",
                source_batch=batch,
                data_status="verified",
                remark=remark,
            ))
        updates.append({"kpi_code": k.kpi_code, "kpi_name": k.kpi_name, "period": period, "value": val})

    # ── ② F_CASH_SAFETY 现金安全垫（万元）──
    safety_kpi = db.query(KPIDefinition).filter(
        KPIDefinition.entity_id == entity_id,
        or_(KPIDefinition.kpi_code == "F_CASH_SAFETY", KPIDefinition.kpi_name.like("%安全垫%")),
    ).first()
    if not safety_kpi:
        safety_kpi = KPIDefinition(
            entity_id=entity_id,
            kpi_code="F_CASH_SAFETY",
            kpi_name="现金安全垫",
            dimension="finance",
            category="cash_risk",
            formula="货币资金余额-短期借款",
            formula_desc="货币资金科目(1001/1002)期末余额 - 短期借款(EXT_139)，单位万元",
            unit="万元",
            target_value=0,  # kpi_definitions.target_value NOT NULL DEFAULT 0.00，ORM显式传None会绕过默认值导致IntegrityError
            data_source_type="ledger",
            data_source="网银流水导入联动",
            data_owner="财务Bot",
            frequency="monthly",
            status="active",
            kpi_level="operational",
            important_flag=1,
            data_level="core",
            epic="Epic2",
        )
        db.add(safety_kpi)
        db.flush()
        logger.info(f"新增KPI F_CASH_SAFETY 现金安全垫 (entity_id={entity_id})")

    short_debt_wan = None
    debt_kpi = db.query(KPIDefinition).filter(
        KPIDefinition.entity_id == entity_id,
        KPIDefinition.kpi_code == "EXT_139",
    ).first()
    if debt_kpi:
        dv = db.query(KPIValue).filter(
            KPIValue.kpi_id == debt_kpi.id,
            KPIValue.actual_value.isnot(None),
        ).order_by(KPIValue.period.desc()).first()
        if dv and dv.actual_value is not None:
            short_debt_wan = round(float(dv.actual_value) / 10000, 2)  # EXT_139单位元

    if short_debt_wan is not None:
        safety_value = round(cash_wan - short_debt_wan, 2)
        remark = f"网银流水导入[{batch}]联动: 货币资金{cash_wan}万 - 短期借款{short_debt_wan}万 = 安全垫{safety_value}万"
    else:
        safety_value = cash_wan
        remark = f"网银流水导入[{batch}]联动: 无短期借款(EXT_139)数据，现金安全垫=货币资金余额{cash_wan}万"
    existing = db.query(KPIValue).filter(
        KPIValue.kpi_id == safety_kpi.id,
        KPIValue.period == period,
        KPIValue.source_type == "ledger",
    ).order_by(KPIValue.id.desc()).first()
    if existing:
        existing.actual_value = safety_value
        existing.remark = remark
        existing.calculated_at = datetime.now()
    else:
        db.add(KPIValue(
            entity_id=entity_id,
            kpi_id=safety_kpi.id,
            period=period,
            actual_value=safety_value,
            source_type="ledger",
            source_batch=batch,
            data_status="verified",
            remark=remark,
        ))
    updates.append({"kpi_code": "F_CASH_SAFETY", "kpi_name": "现金安全垫", "period": period, "value": safety_value})
    return updates


@router.get("/import/template")
def api_get_voucher_template():
    """下载网银流水导入模板xlsx"""
    if not os.path.exists(_TEMPLATE_PATH):
        raise HTTPException(404, "模板文件不存在，请联系管理员生成")
    return FileResponse(
        _TEMPLATE_PATH,
        filename="网银流水导入模板.xlsx",
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    )


@router.post("/import/vouchers")
async def api_import_vouchers(
    file: UploadFile = File(...),
    entity_id: int = Depends(get_entity_id),
    db: Session = Depends(get_db),
):
    """网银流水标准导入 — 三校验(借贷平衡/期间合计/结转行) → voucher_details/import_logs → 现金流余额联动"""
    from app.models import VoucherDetail, ImportLog

    content = await file.read()
    fname = (file.filename or "网银流水.xlsx").strip()
    try:
        df = pd.read_excel(io.BytesIO(content))
    except Exception as e:
        raise HTTPException(400, f"无法读取Excel文件: {e}")
    if df is None or len(df) == 0:
        raise HTTPException(400, "Excel文件为空（无数据行）")

    colmap = _resolve_voucher_cols(list(df.columns))
    missing = [f for f in ("voucher_date", "voucher_no", "subject_code") if f not in colmap]
    if missing:
        raise HTTPException(400, f"缺少必要列: {', '.join(missing)}（模板列: 凭证日期/凭证号/科目编码/科目名称/借方金额/贷方金额/摘要）")

    # ── 逐行校验 ──
    errors = []
    valid_rows = []
    for idx, row in df.iterrows():
        excel_row = idx + 2  # 表头占第1行
        raw_date = row.get(colmap["voucher_date"])
        if raw_date is None or (isinstance(raw_date, float) and pd.isna(raw_date)):
            errors.append({"row": excel_row, "field": "voucher_date", "reason": "凭证日期为空"})
            continue
        try:
            voucher_date = pd.to_datetime(raw_date).to_pydatetime()
        except Exception:
            errors.append({"row": excel_row, "field": "voucher_date", "reason": f"日期无法解析: {raw_date}"})
            continue

        voucher_no = _norm_str(row.get(colmap["voucher_no"]))
        if not voucher_no:
            errors.append({"row": excel_row, "field": "voucher_no", "reason": "凭证号为空"})
            continue
        subject_code = _norm_str(row.get(colmap["subject_code"]))
        if not subject_code:
            errors.append({"row": excel_row, "field": "subject_code", "reason": "科目编码为空"})
            continue
        subject_name = _norm_str(row.get(colmap["subject_name"]) if "subject_name" in colmap else "")
        if not subject_name:
            errors.append({"row": excel_row, "field": "subject_name", "reason": "科目名称为空"})
            continue

        def _parse_amount(raw) -> float:
            """金额解析：空→0；数字→float；字符串去逗号→float；失败→None"""
            if raw is None or (isinstance(raw, float) and pd.isna(raw)):
                return 0.0
            if isinstance(raw, (int, float)) and not isinstance(raw, bool):
                return float(raw)
            s = str(raw).strip().replace(",", "")
            try:
                return float(s)
            except Exception:
                return None

        debit = _parse_amount(row.get(colmap["debit_amount"]) if "debit_amount" in colmap else None)
        credit = _parse_amount(row.get(colmap["credit_amount"]) if "credit_amount" in colmap else None)
        if debit is None:
            errors.append({"row": excel_row, "field": "debit_amount", "reason": f"借方金额不是有效数字: {row.get(colmap['debit_amount'])}"})
            continue
        if credit is None:
            errors.append({"row": excel_row, "field": "credit_amount", "reason": f"贷方金额不是有效数字: {row.get(colmap['credit_amount'])}"})
            continue
        if debit < 0 or credit < 0:
            errors.append({"row": excel_row, "field": "amount", "reason": "金额不能为负"})
            continue
        if debit == 0 and credit == 0:
            errors.append({"row": excel_row, "field": "amount", "reason": "借贷金额不能同时为0"})
            continue

        summary = _norm_str(row.get(colmap["summary"]) if "summary" in colmap else "")
        carry_forward = 1 if _is_carry_forward(summary, subject_name) else 0
        valid_rows.append({
            "voucher_no": voucher_no,
            "voucher_date": voucher_date,
            "period": voucher_date.strftime("%Y-%m"),
            "subject_code": subject_code,
            "subject_name": subject_name,
            "debit_amount": round(debit, 2),
            "credit_amount": round(credit, 2),
            "summary": summary,
            "carry_forward": carry_forward,
        })

    total = len(df)
    success = len(valid_rows)
    failed = len(errors)

    # ── 校验规则① 借贷平衡（Σ借 vs Σ贷，容差0.01）──
    debit_total = round(sum(r["debit_amount"] for r in valid_rows), 2)
    credit_total = round(sum(r["credit_amount"] for r in valid_rows), 2)
    diff = round(debit_total - credit_total, 2)
    balance_ok = abs(diff) <= 0.01
    balance_check = {
        "passed": balance_ok,
        "debit_total": debit_total,
        "credit_total": credit_total,
        "diff": diff,
        "tolerance": 0.01,
    }
    if not balance_ok:
        errors.append({"row": 0, "field": "balance", "reason": f"借贷不平衡: 借方合计{debit_total} ≠ 贷方合计{credit_total}，差额{diff}"})

    # ── 校验规则② 期间合计（按period汇总，供对账）──
    period_totals = {}
    for r in valid_rows:
        pt = period_totals.setdefault(r["period"], {"debit_total": 0.0, "credit_total": 0.0})
        pt["debit_total"] = round(pt["debit_total"] + r["debit_amount"], 2)
        pt["credit_total"] = round(pt["credit_total"] + r["credit_amount"], 2)

    # ── 校验规则③ 结转行 ──
    carry_forward_count = sum(1 for r in valid_rows if r["carry_forward"])

    # ── 入库（部分成功模式：失败行不阻断整体）──
    batch = f"{os.path.splitext(fname)[0]}_{datetime.now().strftime('%Y%m%d%H%M%S')}"
    for r in valid_rows:
        db.add(VoucherDetail(
            entity_id=entity_id,
            voucher_no=r["voucher_no"],
            voucher_date=r["voucher_date"],
            subject_code=r["subject_code"],
            subject_name=r["subject_name"],
            debit_amount=r["debit_amount"],
            credit_amount=r["credit_amount"],
            summary=r["summary"],
            carry_forward=r["carry_forward"],
            period=r["period"],
            batch=batch,
        ))
    periods_in = sorted(set(r["period"] for r in valid_rows))
    db.add(ImportLog(
        entity_id=entity_id,
        filename=fname,
        batch=batch,
        total_rows=total,
        success_rows=success,
        failed_rows=failed,
        errors=errors or None,
        period=periods_in[0] if periods_in else None,
        import_type="vouchers",
        created_by="finance-bot",
    ))
    db.commit()
    logger.info(f"网银流水导入[{batch}] entity={entity_id}: 总{total}/成功{success}/失败{failed}, 借贷平衡={'通过' if balance_ok else '失败'}")

    # ── 现金流联动 ──
    cash_balance = None
    kpi_updates = []
    try:
        cash_balance = _sync_cash_balance_from_ledger(db, entity_id, batch)
        kpi_updates = _sync_cash_kpis(db, entity_id, batch, periods_in)
        db.commit()
    except Exception as e:
        db.rollback()
        logger.error(f"网银流水导入[{batch}] 现金流联动失败: {e}", exc_info=True)

    return {
        "success": True,
        "total": total,
        "success_rows": success,
        "failed_rows": failed,
        "errors": errors,
        "balance_check": balance_check,
        "period_totals": period_totals,
        "carry_forward_count": carry_forward_count,
        "cash_balance": cash_balance,
        "kpi_updates": kpi_updates,
        "batch": batch,
        "entity_id": entity_id,
    }