pesco-ncr/backend/app/routers/ncrs.py

"""NCR endpoints: creation, queues/search, stage actions (the workflow state
machine), attachments, audit history, CSV export, and the printable PDF.

Every stage action re-validates BOTH the caller's role and the NCR's current
stage server-side; the frontend's `available_actions` hints are advisory only.
"""
import csv
import io
import logging

from fastapi import APIRouter, Depends, HTTPException, Query, UploadFile
from fastapi.responses import FileResponse, Response, StreamingResponse
from sqlalchemy import delete, func, or_, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.auth.deps import CurrentUser, get_current_user, require_roles
from app.database import get_db
from app.domain import STAGE_LABELS, Role, Stage
from app.models import (
    Attachment,
    Department,
    DeviationCategory,
    JobInfo,
    Ncr,
    NcrLink,
    NcrSecondaryAssignee,
    RootCauseCategory,
    User,
    UserRole,
)
from app.models.base import utcnow
from app.schemas.ncr import (
    AttachmentOut,
    AuditEntryOut,
    AuditListOut,
    CapaIn,
    CostingIn,
    InitialDispositionIn,
    InspectionIn,
    JobInfoOut,
    NcrCreateIn,
    NcrDetailOut,
    NcrLinkOut,
    NcrListItem,
    NcrListOut,
    NcrMutationOut,
    ReopenIn,
    SecondaryDispositionIn,
    TransitionOut,
)
from app.schemas.user import UserRef
from app.services.audit import apply_field_updates, audit_event
from app.services.job_lookup import get_job_lookup_service
from app.services.notifications import NotifyEvent, send_stage_notification
from app.services.numbering import allocate_ncr_number
from app.services.sanitize import sanitize_html
from app.services.storage import (
    UploadValidationError,
    attachment_abs_path,
    save_attachment,
)
from app.services.workflow import InvalidTransitionError, record_creation, transition

logger = logging.getLogger(__name__)
router = APIRouter(tags=["ncrs"])

_STAGE_ORDER = [
    Stage.NEW_REQUEST,
    Stage.SECONDARY_DISPOSITION,
    Stage.OPERATIONS,
    Stage.QC_INSPECTION,
    Stage.COSTING,
    Stage.CLOSED,
]


# ── helpers ──────────────────────────────────────────────────────────────────
async def _get_ncr(db: AsyncSession, ref: str | int) -> Ncr:
    """Resolve an NCR by its business identifier (e.g. NCR-2026-0021,
    case-insensitive) or — for backward compatibility with older links —
    by numeric database id."""
    ref = str(ref).strip()
    if ref.isdigit():
        ncr = await db.get(Ncr, int(ref))
    else:
        ncr = (
            await db.execute(select(Ncr).where(Ncr.ncr_number == ref.upper()))
        ).scalar_one_or_none()
    if ncr is None:
        raise HTTPException(status_code=404, detail="NCR not found.")
    return ncr


def _days_in_stage(ncr: Ncr) -> int:
    return max(0, (utcnow() - ncr.stage_entered_at).days)


def _ensure_stage(ncr: Ncr, expected: Stage) -> None:
    if ncr.stage == Stage.CLOSED.value and expected != Stage.CLOSED:
        raise HTTPException(
            status_code=409,
            detail=f"{ncr.ncr_number} is closed and locked. Only an Admin can reopen it.",
        )
    if ncr.stage != expected.value:
        raise HTTPException(
            status_code=409,
            detail=(
                f"{ncr.ncr_number} is in stage '{STAGE_LABELS[Stage(ncr.stage)]}', "
                f"but this action requires '{STAGE_LABELS[expected]}'."
            ),
        )


def _is_secondary_assignee(ncr: Ncr, current: CurrentUser) -> bool:
    return any(row.user_id == current.id for row in ncr.secondary_assignee_rows)


def _can_edit_capa(ncr: Ncr, current: CurrentUser) -> bool:
    return ncr.stage != Stage.CLOSED.value and current.has_role(
        Role.QC_INSPECTOR, Role.DISPOSITION_AUTHORITY
    )


def _available_actions(ncr: Ncr, current: CurrentUser) -> list[str]:
    actions: list[str] = []
    stage = Stage(ncr.stage)
    if stage == Stage.NEW_REQUEST and current.has_role(Role.DISPOSITION_AUTHORITY):
        actions.append("initial_disposition")
    if stage == Stage.SECONDARY_DISPOSITION and (
        current.is_admin or _is_secondary_assignee(ncr, current)
    ):
        actions.append("secondary_disposition")
    if stage == Stage.OPERATIONS and current.has_role(Role.OPERATIONS):
        actions.append("operations_complete")
    if stage == Stage.QC_INSPECTION and current.has_role(Role.QC_INSPECTOR):
        actions.append("inspection")
    if stage == Stage.COSTING and current.has_role(Role.COSTING):
        actions.append("costing")
    if stage == Stage.CLOSED and current.is_admin:
        actions.append("reopen")
    if _can_edit_capa(ncr, current):
        actions.append("capa")
    if stage != Stage.CLOSED:
        actions.append("add_attachment")
    if current.has_role(Role.QC_INSPECTOR):  # admins pass automatically
        actions.append("view_audit")
    return actions


async def _ncr_links(db: AsyncSession, ncr_id: int) -> tuple[list[NcrLinkOut], list[NcrLinkOut]]:
    """Outgoing links (prior NCRs this one references) and incoming links
    (later NCRs that flagged this one as a recurrence)."""

    async def _load(join_col, where_col) -> list[NcrLinkOut]:
        rows = (
            await db.execute(
                select(Ncr.id, Ncr.ncr_number, Ncr.job_number, Ncr.stage)
                .join(NcrLink, join_col == Ncr.id)
                .where(where_col == ncr_id)
                .order_by(Ncr.ncr_number)
            )
        ).all()
        return [
            NcrLinkOut(
                id=r.id,
                ncr_number=r.ncr_number,
                job_number=r.job_number,
                stage=r.stage,
                stage_label=STAGE_LABELS[Stage(r.stage)],
            )
            for r in rows
        ]

    related = await _load(NcrLink.related_ncr_id, NcrLink.ncr_id)
    referenced_by = await _load(NcrLink.ncr_id, NcrLink.related_ncr_id)
    return related, referenced_by


async def _detail(db: AsyncSession, ncr: Ncr, current: CurrentUser) -> NcrDetailOut:
    stage = Stage(ncr.stage)
    related_ncrs, referenced_by = await _ncr_links(db, ncr.id)
    return NcrDetailOut(
        id=ncr.id,
        ncr_number=ncr.ncr_number,
        job_number=ncr.job_number,
        created_at=ncr.created_at,
        stage=stage.value,
        stage_label=STAGE_LABELS[stage],
        stage_entered_at=ncr.stage_entered_at,
        days_in_stage=_days_in_stage(ncr),
        department=ncr.department.name,
        department_id=ncr.department_id,
        deviation_category=ncr.deviation_category.name,
        deviation_category_id=ncr.deviation_category_id,
        deviation_detail=ncr.deviation_detail,
        requester=UserRef.model_validate(ncr.requester),
        disposition_authority=UserRef.model_validate(ncr.disposition_authority),
        qc_authority=ncr.qc_authority,
        work_order=ncr.work_order,
        disposition_notes=ncr.disposition_notes,
        secondary_review_needed=ncr.secondary_review_needed,
        secondary_authorities=[
            UserRef.model_validate(u) for u in ncr.secondary_authorities
        ],
        operations_complete=ncr.operations_complete,
        operations_completed_at=ncr.operations_completed_at,
        operations_completed_by=(
            UserRef.model_validate(ncr.operations_completed_by)
            if ncr.operations_completed_by
            else None
        ),
        qc_approval=ncr.qc_approval,
        inspection_notes=ncr.inspection_notes,
        qc_closed=ncr.qc_closed,
        qc_closed_at=ncr.qc_closed_at,
        qc_closed_by=(
            UserRef.model_validate(ncr.qc_closed_by) if ncr.qc_closed_by else None
        ),
        root_cause=ncr.root_cause,
        root_cause_category=(
            ncr.root_cause_category.name if ncr.root_cause_category else None
        ),
        root_cause_category_id=ncr.root_cause_category_id,
        corrective_action_required=ncr.corrective_action_required,
        corrective_action_justification=ncr.corrective_action_justification,
        corrective_action_plan=ncr.corrective_action_plan,
        corrective_action_owner=(
            UserRef.model_validate(ncr.corrective_action_owner)
            if ncr.corrective_action_owner
            else None
        ),
        corrective_action_due_date=ncr.corrective_action_due_date,
        corrective_action_opened_at=ncr.corrective_action_opened_at,
        effectiveness_result=ncr.effectiveness_result,
        effectiveness_notes=ncr.effectiveness_notes,
        effectiveness_verified_at=ncr.effectiveness_verified_at,
        effectiveness_verified_by=(
            UserRef.model_validate(ncr.effectiveness_verified_by)
            if ncr.effectiveness_verified_by
            else None
        ),
        is_recurring=ncr.is_recurring,
        related_ncrs=related_ncrs,
        referenced_by=referenced_by,
        labor_cost=ncr.labor_cost,
        material_cost=ncr.material_cost,
        service_cost=ncr.service_cost,
        other_cost=ncr.other_cost,
        total_cost=ncr.total_cost,
        costing_completed_at=ncr.costing_completed_at,
        costing_completed_by=(
            UserRef.model_validate(ncr.costing_completed_by)
            if ncr.costing_completed_by
            else None
        ),
        closed_at=ncr.closed_at,
        closed_by=UserRef.model_validate(ncr.closed_by) if ncr.closed_by else None,
        job_info=JobInfoOut.model_validate(ncr.job_info) if ncr.job_info else None,
        attachments=[AttachmentOut.model_validate(a) for a in ncr.attachments],
        transitions=[TransitionOut.model_validate(t) for t in ncr.transitions],
        available_actions=_available_actions(ncr, current),
    )


async def _refetch(db: AsyncSession, ncr_id: int) -> Ncr:
    """Reload the NCR with fresh relationship collections after a commit."""
    db.expire_all()
    return await _get_ncr(db, ncr_id)


# ── create ───────────────────────────────────────────────────────────────────
@router.post("/ncrs", response_model=NcrMutationOut, status_code=201)
async def create_ncr(
    payload: NcrCreateIn,
    current: CurrentUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
) -> NcrMutationOut:
    """Stage 1 — New Request. Open to every authenticated user."""
    dept = await db.get(Department, payload.department_id)
    if dept is None or not dept.is_active:
        raise HTTPException(status_code=422, detail="Unknown or inactive department.")
    cat = await db.get(DeviationCategory, payload.deviation_category_id)
    if cat is None or not cat.is_active:
        raise HTTPException(status_code=422, detail="Unknown or inactive deviation category.")
    authority = await db.get(User, payload.disposition_authority_id)
    if (
        authority is None
        or not authority.is_active
        or Role.DISPOSITION_AUTHORITY.value not in authority.roles
    ):
        raise HTTPException(
            status_code=422,
            detail="Selected disposition authority does not hold the Disposition Authority role.",
        )

    # External enrichment BEFORE the numbering lock so a slow ERP lookup can
    # never serialize submissions. NullJobLookupService returns instantly.
    job_info_data = None
    try:
        job_info_data = await get_job_lookup_service().lookup(payload.job_number)
    except Exception:
        logger.exception("Job lookup failed for %s (non-blocking)", payload.job_number)

    ncr_number, year, seq = await allocate_ncr_number(db)
    ncr = Ncr(
        ncr_number=ncr_number,
        ncr_year=year,
        ncr_seq=seq,
        job_number=payload.job_number.strip(),
        department_id=payload.department_id,
        deviation_category_id=payload.deviation_category_id,
        disposition_authority_id=payload.disposition_authority_id,
        deviation_detail=payload.deviation_detail,
        requester_id=current.id,
        stage=Stage.NEW_REQUEST.value,
    )
    db.add(ncr)
    await db.flush()
    record_creation(db, ncr, current.id)
    if job_info_data is not None:
        db.add(
            JobInfo(
                ncr_id=ncr.id,
                part_id=job_info_data.part_id,
                part_description=job_info_data.part_description,
                customer_name=job_info_data.customer_name,
                work_order_status=job_info_data.work_order_status,
                source=job_info_data.source,
            )
        )
    await db.commit()

    ncr = await _refetch(db, ncr.id)
    warnings = await send_stage_notification(
        db,
        ncr,
        NotifyEvent.CREATED,
        current,
        f"{current.user.display_name} submitted a new NCR and selected you as the "
        "disposition authority.",
    )
    return NcrMutationOut(ncr=await _detail(db, ncr, current), warnings=warnings)


# ── queues / search / export ────────────────────────────────────────────────
def _apply_filters(
    stmt,
    *,
    q: str | None,
    job_number: str | None,
    department_id: int | None,
    category_id: int | None,
    stage: str | None,
    date_from: str | None,
    date_to: str | None,
    disposition_authority_id: int | None,
):
    if q:
        like = f"%{q.strip()}%"
        stmt = stmt.where(or_(Ncr.ncr_number.like(like), Ncr.job_number.like(like)))
    if job_number:
        stmt = stmt.where(Ncr.job_number.like(f"%{job_number.strip()}%"))
    if department_id:
        stmt = stmt.where(Ncr.department_id == department_id)
    if category_id:
        stmt = stmt.where(Ncr.deviation_category_id == category_id)
    if stage:
        stmt = stmt.where(Ncr.stage == stage)
    if date_from:
        stmt = stmt.where(Ncr.created_at >= date_from)
    if date_to:
        stmt = stmt.where(Ncr.created_at <= f"{date_to} 23:59:59")
    if disposition_authority_id:
        stmt = stmt.where(Ncr.disposition_authority_id == disposition_authority_id)
    return stmt


def _queue_filter(stmt, queue: str, current: CurrentUser):
    if queue == "my_requests":
        return stmt.where(Ncr.requester_id == current.id)
    if queue == "new_requests":
        return stmt.where(Ncr.stage == Stage.NEW_REQUEST.value)
    if queue == "secondary":
        return stmt.where(
            Ncr.stage == Stage.SECONDARY_DISPOSITION.value,
            Ncr.id.in_(
                select(NcrSecondaryAssignee.ncr_id).where(
                    NcrSecondaryAssignee.user_id == current.id
                )
            ),
        )
    if queue == "operations":
        return stmt.where(Ncr.stage == Stage.OPERATIONS.value)
    if queue == "inspection":
        return stmt.where(Ncr.stage == Stage.QC_INSPECTION.value)
    if queue == "costing":
        return stmt.where(Ncr.stage == Stage.COSTING.value)
    if queue == "recently_closed":
        return stmt.where(Ncr.stage == Stage.CLOSED.value)
    if queue in ("all", ""):
        return stmt
    raise HTTPException(status_code=422, detail=f"Unknown queue '{queue}'.")


def _list_item(ncr: Ncr) -> NcrListItem:
    return NcrListItem(
        id=ncr.id,
        ncr_number=ncr.ncr_number,
        job_number=ncr.job_number,
        department=ncr.department.name,
        deviation_category=ncr.deviation_category.name,
        requester=ncr.requester.display_name,
        disposition_authority=ncr.disposition_authority.display_name,
        stage=ncr.stage,
        stage_label=STAGE_LABELS[Stage(ncr.stage)],
        days_in_stage=_days_in_stage(ncr),
        created_at=ncr.created_at,
    )


@router.get("/ncrs", response_model=NcrListOut)
async def list_ncrs(
    queue: str = Query(default="all"),
    q: str | None = None,
    job_number: str | None = None,
    department_id: int | None = None,
    category_id: int | None = None,
    stage: str | None = None,
    date_from: str | None = Query(default=None, description="YYYY-MM-DD"),
    date_to: str | None = Query(default=None, description="YYYY-MM-DD"),
    disposition_authority_id: int | None = None,
    page: int = Query(default=1, ge=1),
    page_size: int = Query(default=25, ge=1, le=200),
    current: CurrentUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
) -> NcrListOut:
    stmt = select(Ncr)
    stmt = _queue_filter(stmt, queue, current)
    stmt = _apply_filters(
        stmt,
        q=q,
        job_number=job_number,
        department_id=department_id,
        category_id=category_id,
        stage=stage,
        date_from=date_from,
        date_to=date_to,
        disposition_authority_id=disposition_authority_id,
    )
    total = (
        await db.execute(select(func.count()).select_from(stmt.subquery()))
    ).scalar_one()
    order = Ncr.closed_at.desc() if queue == "recently_closed" else Ncr.created_at.desc()
    rows = (
        (await db.execute(stmt.order_by(order).offset((page - 1) * page_size).limit(page_size)))
        .scalars()
        .unique()
        .all()
    )
    return NcrListOut(
        items=[_list_item(n) for n in rows], total=total, page=page, page_size=page_size
    )


_CSV_COLUMNS = [
    "ncr_number", "job_number", "department", "deviation_category", "requester",
    "disposition_authority", "stage", "days_in_stage", "created_at", "work_order",
    "qc_authority", "secondary_review_needed", "operations_complete", "qc_approval",
    "qc_closed", "root_cause_category", "corrective_action_required",
    "corrective_action_owner", "corrective_action_due_date", "effectiveness_result",
    "effectiveness_verified_at", "is_recurring", "labor_cost", "material_cost",
    "service_cost", "other_cost", "total_cost", "closed_at",
]


@router.get("/ncrs/export.csv")
async def export_ncrs_csv(
    queue: str = Query(default="all"),
    q: str | None = None,
    job_number: str | None = None,
    department_id: int | None = None,
    category_id: int | None = None,
    stage: str | None = None,
    date_from: str | None = None,
    date_to: str | None = None,
    disposition_authority_id: int | None = None,
    current: CurrentUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
) -> StreamingResponse:
    """CSV export of any queue/search view (same filters as GET /ncrs)."""
    stmt = select(Ncr)
    stmt = _queue_filter(stmt, queue, current)
    stmt = _apply_filters(
        stmt,
        q=q,
        job_number=job_number,
        department_id=department_id,
        category_id=category_id,
        stage=stage,
        date_from=date_from,
        date_to=date_to,
        disposition_authority_id=disposition_authority_id,
    )
    rows = (
        (await db.execute(stmt.order_by(Ncr.created_at.desc()).limit(20000)))
        .scalars()
        .unique()
        .all()
    )

    buf = io.StringIO()
    writer = csv.writer(buf)
    writer.writerow(_CSV_COLUMNS)
    for n in rows:
        writer.writerow(
            [
                n.ncr_number, n.job_number, n.department.name, n.deviation_category.name,
                n.requester.display_name, n.disposition_authority.display_name,
                STAGE_LABELS[Stage(n.stage)], _days_in_stage(n),
                n.created_at.isoformat(sep=" "), n.work_order or "", n.qc_authority or "",
                n.secondary_review_needed, n.operations_complete, n.qc_approval or "",
                n.qc_closed,
                n.root_cause_category.name if n.root_cause_category else "",
                "" if n.corrective_action_required is None else n.corrective_action_required,
                n.corrective_action_owner.display_name if n.corrective_action_owner else "",
                n.corrective_action_due_date.isoformat() if n.corrective_action_due_date else "",
                n.effectiveness_result or "",
                n.effectiveness_verified_at.isoformat(sep=" ") if n.effectiveness_verified_at else "",
                n.is_recurring,
                n.labor_cost or "", n.material_cost or "",
                n.service_cost or "", n.other_cost or "", n.total_cost or "",
                n.closed_at.isoformat(sep=" ") if n.closed_at else "",
            ]
        )
    buf.seek(0)
    return StreamingResponse(
        iter([buf.getvalue()]),
        media_type="text/csv",
        headers={"Content-Disposition": 'attachment; filename="ncr-export.csv"'},
    )


@router.get("/ncrs/{ncr_ref}", response_model=NcrDetailOut)
async def get_ncr(
    ncr_ref: str,
    current: CurrentUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
) -> NcrDetailOut:
    ncr = await _get_ncr(db, ncr_ref)
    return await _detail(db, ncr, current)


# ── stage actions ────────────────────────────────────────────────────────────
@router.post("/ncrs/{ncr_ref}/initial-disposition", response_model=NcrMutationOut)
async def initial_disposition(
    ncr_ref: str,
    payload: InitialDispositionIn,
    current: CurrentUser = Depends(require_roles(Role.DISPOSITION_AUTHORITY)),
    db: AsyncSession = Depends(get_db),
) -> NcrMutationOut:
    """Stage 2 — Initial Disposition, performed on a New Request. Routes to
    Secondary Disposition (when secondary review is needed) or Operations."""
    ncr = await _get_ncr(db, ncr_ref)
    _ensure_stage(ncr, Stage.NEW_REQUEST)

    assignees: list[User] = []
    if payload.secondary_review_needed:
        if not payload.secondary_authority_ids:
            raise HTTPException(
                status_code=422,
                detail="Secondary review requires at least one person in 'Notify These People'.",
            )
        for uid in set(payload.secondary_authority_ids):
            u = await db.get(User, uid)
            if (
                u is None
                or not u.is_active
                or Role.SECONDARY_DISPOSITION_AUTHORITY.value not in u.roles
            ):
                raise HTTPException(
                    status_code=422,
                    detail="All selected people must hold the Secondary Disposition Authority role.",
                )
            assignees.append(u)

    updates = payload.model_dump(exclude_unset=True, exclude={"secondary_authority_ids"})
    if "disposition_notes" in updates:
        updates["disposition_notes"] = sanitize_html(updates["disposition_notes"])
    updates["secondary_review_needed"] = payload.secondary_review_needed
    apply_field_updates(db, ncr, current.id, updates, action="initial_disposition")

    if payload.secondary_review_needed:
        await db.execute(
            delete(NcrSecondaryAssignee).where(NcrSecondaryAssignee.ncr_id == ncr.id)
        )
        for u in assignees:
            db.add(NcrSecondaryAssignee(ncr_id=ncr.id, user_id=u.id))
        audit_event(
            db,
            ncr_id=ncr.id,
            user_id=current.id,
            action="initial_disposition",
            field_name="secondary_authorities",
            new_value=", ".join(u.display_name for u in assignees),
        )
        _do_transition(db, ncr, Stage.SECONDARY_DISPOSITION, "initial_disposition", current)
        event, summary = (
            NotifyEvent.SECONDARY_ASSIGNED,
            f"{current.user.display_name} completed initial disposition and assigned "
            "you for secondary disposition review.",
        )
    else:
        _do_transition(db, ncr, Stage.OPERATIONS, "initial_disposition", current)
        event, summary = (
            NotifyEvent.RELEASED_TO_OPERATIONS,
            f"{current.user.display_name} completed initial disposition; the NCR is "
            "ready for Operations.",
        )

    await db.commit()
    ncr = await _refetch(db, ncr.id)
    warnings = await send_stage_notification(db, ncr, event, current, summary)
    return NcrMutationOut(ncr=await _detail(db, ncr, current), warnings=warnings)


@router.post("/ncrs/{ncr_ref}/secondary-disposition", response_model=NcrMutationOut)
async def secondary_disposition(
    ncr_ref: str,
    payload: SecondaryDispositionIn,
    current: CurrentUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
) -> NcrMutationOut:
    """Stage 3 — Secondary Disposition. Only the assigned secondary
    authorities (or an Admin) may update or release to Operations."""
    ncr = await _get_ncr(db, ncr_ref)
    _ensure_stage(ncr, Stage.SECONDARY_DISPOSITION)
    if not (current.is_admin or _is_secondary_assignee(ncr, current)):
        raise HTTPException(
            status_code=403,
            detail="Only the assigned secondary disposition authority can act on this NCR.",
        )

    updates = payload.model_dump(exclude_unset=True, exclude={"release"})
    if "disposition_notes" in updates:
        updates["disposition_notes"] = sanitize_html(updates["disposition_notes"])
    apply_field_updates(db, ncr, current.id, updates, action="secondary_disposition")

    warnings: list[str] = []
    if payload.release:
        _do_transition(db, ncr, Stage.OPERATIONS, "secondary_release", current)
        await db.commit()
        ncr = await _refetch(db, ncr.id)
        warnings = await send_stage_notification(
            db,
            ncr,
            NotifyEvent.RELEASED_TO_OPERATIONS,
            current,
            f"{current.user.display_name} completed secondary disposition review and "
            "released the NCR to Operations.",
        )
    else:
        await db.commit()
        ncr = await _refetch(db, ncr.id)
    return NcrMutationOut(ncr=await _detail(db, ncr, current), warnings=warnings)


@router.post("/ncrs/{ncr_ref}/operations-complete", response_model=NcrMutationOut)
async def operations_complete(
    ncr_ref: str,
    current: CurrentUser = Depends(require_roles(Role.OPERATIONS)),
    db: AsyncSession = Depends(get_db),
) -> NcrMutationOut:
    """Stage 4 — Operations marks rework complete; NCR moves to QC Inspection."""
    ncr = await _get_ncr(db, ncr_ref)
    _ensure_stage(ncr, Stage.OPERATIONS)

    apply_field_updates(
        db,
        ncr,
        current.id,
        {
            "operations_complete": True,
            "operations_completed_at": utcnow(),
            "operations_completed_by_id": current.id,
        },
        action="operations_complete",
    )
    _do_transition(db, ncr, Stage.QC_INSPECTION, "operations_complete", current)
    await db.commit()
    ncr = await _refetch(db, ncr.id)
    warnings = await send_stage_notification(
        db,
        ncr,
        NotifyEvent.OPERATIONS_COMPLETE,
        current,
        f"{current.user.display_name} marked operations complete; the NCR is ready "
        "for QC inspection.",
    )
    return NcrMutationOut(ncr=await _detail(db, ncr, current), warnings=warnings)


@router.post("/ncrs/{ncr_ref}/inspection", response_model=NcrMutationOut)
async def inspection(
    ncr_ref: str,
    payload: InspectionIn,
    current: CurrentUser = Depends(require_roles(Role.QC_INSPECTOR)),
    db: AsyncSession = Depends(get_db),
) -> NcrMutationOut:
    """Stage 5 — QC Inspection. QC can save repeatedly; checking QC Closed
    advances the NCR to Costing."""
    ncr = await _get_ncr(db, ncr_ref)
    _ensure_stage(ncr, Stage.QC_INSPECTION)

    updates = payload.model_dump(exclude_unset=True, exclude={"qc_closed"})
    if payload.qc_closed:
        updates.update(
            {"qc_closed": True, "qc_closed_at": utcnow(), "qc_closed_by_id": current.id}
        )
    apply_field_updates(db, ncr, current.id, updates, action="inspection")

    warnings: list[str] = []
    if payload.qc_closed:
        _do_transition(db, ncr, Stage.COSTING, "qc_close", current)
        await db.commit()
        ncr = await _refetch(db, ncr.id)
        warnings = await send_stage_notification(
            db,
            ncr,
            NotifyEvent.QC_CLOSED,
            current,
            f"{current.user.display_name} closed QC inspection; the NCR is awaiting costing.",
        )
    else:
        await db.commit()
        ncr = await _refetch(db, ncr.id)
    return NcrMutationOut(ncr=await _detail(db, ncr, current), warnings=warnings)


@router.post("/ncrs/{ncr_ref}/capa", response_model=NcrMutationOut)
async def update_capa(
    ncr_ref: str,
    payload: CapaIn,
    current: CurrentUser = Depends(
        require_roles(Role.QC_INSPECTOR, Role.DISPOSITION_AUTHORITY)
    ),
    db: AsyncSession = Depends(get_db),
) -> NcrMutationOut:
    """Corrective & Preventive Action (API Q1 §5.9.1.2 / §6.4.2). Editable in
    any non-closed stage by QC Inspectors, Disposition Authorities, or Admins;
    the costing action refuses to close the NCR until this section is
    complete (see _capa_close_blockers)."""
    ncr = await _get_ncr(db, ncr_ref)
    if ncr.stage == Stage.CLOSED.value:
        raise HTTPException(
            status_code=409,
            detail=f"{ncr.ncr_number} is closed and locked. Only an Admin can reopen it.",
        )

    updates = payload.model_dump(exclude_unset=True, exclude={"related_ncr_ids"})

    # The Yes/No gate always carries a brief justification (either in this
    # request or already on record).
    if updates.get("corrective_action_required") is not None:
        justification = updates.get(
            "corrective_action_justification", ncr.corrective_action_justification
        )
        if not (justification or "").strip():
            raise HTTPException(
                status_code=422,
                detail="A brief justification is required when answering "
                "'Corrective Action Required?'.",
            )

    if updates.get("root_cause_category_id") is not None:
        cat = await db.get(RootCauseCategory, updates["root_cause_category_id"])
        if cat is None or not cat.is_active:
            raise HTTPException(
                status_code=422, detail="Unknown or inactive root cause category."
            )

    if updates.get("corrective_action_owner_id") is not None:
        owner = await db.get(User, updates["corrective_action_owner_id"])
        if owner is None or not owner.is_active:
            raise HTTPException(
                status_code=422, detail="Corrective action owner must be an active user."
            )

    ca_required = updates.get(
        "corrective_action_required", ncr.corrective_action_required
    )
    new_result = updates.get("effectiveness_result", ncr.effectiveness_result)
    if new_result is not None and ca_required is not True:
        raise HTTPException(
            status_code=422,
            detail="Answer 'Corrective Action Required? = Yes' before recording "
            "effectiveness verification.",
        )

    # Server-stamped bookkeeping: who/when verified, and when the CAPA opened.
    if (
        "effectiveness_result" in updates
        and updates["effectiveness_result"] != ncr.effectiveness_result
    ):
        if updates["effectiveness_result"] is None:
            updates.update(
                {"effectiveness_verified_at": None, "effectiveness_verified_by_id": None}
            )
        else:
            updates.update(
                {
                    "effectiveness_verified_at": utcnow(),
                    "effectiveness_verified_by_id": current.id,
                }
            )
    if (
        updates.get("corrective_action_required") is True
        and ncr.corrective_action_opened_at is None
    ):
        updates["corrective_action_opened_at"] = utcnow()

    changes = apply_field_updates(db, ncr, current.id, updates, action="capa")

    if payload.related_ncr_ids is not None:
        if ncr.id in payload.related_ncr_ids:
            raise HTTPException(
                status_code=422, detail="An NCR cannot be linked to itself."
            )
        new_ids = set(payload.related_ncr_ids)
        if new_ids:
            found = {
                r[0]
                for r in (
                    await db.execute(select(Ncr.id).where(Ncr.id.in_(new_ids)))
                ).all()
            }
            if new_ids - found:
                raise HTTPException(
                    status_code=422, detail="One or more linked NCRs do not exist."
                )
        old_ids = {
            r[0]
            for r in (
                await db.execute(
                    select(NcrLink.related_ncr_id).where(NcrLink.ncr_id == ncr.id)
                )
            ).all()
        }
        if new_ids != old_ids:
            numbers = {
                r[0]: r[1]
                for r in (
                    await db.execute(
                        select(Ncr.id, Ncr.ncr_number).where(
                            Ncr.id.in_(new_ids | old_ids)
                        )
                    )
                ).all()
            }
            await db.execute(delete(NcrLink).where(NcrLink.ncr_id == ncr.id))
            for rid in sorted(new_ids):
                db.add(NcrLink(ncr_id=ncr.id, related_ncr_id=rid))
            audit_event(
                db,
                ncr_id=ncr.id,
                user_id=current.id,
                action="capa",
                field_name="related_ncrs",
                old_value=", ".join(numbers[i] for i in sorted(old_ids)) or None,
                new_value=", ".join(numbers[i] for i in sorted(new_ids)) or None,
            )

    # Captured before commit: _refetch expires the session, and unlike stage
    # transitions nothing re-loads the acting user afterwards.
    actor_name = current.user.display_name
    await db.commit()
    ncr = await _refetch(db, ncr.id)

    warnings: list[str] = []
    if "corrective_action_owner_id" in changes and ncr.corrective_action_owner:
        await db.refresh(current.user)  # notification internals read actor email
        due = (
            f" Due date: {ncr.corrective_action_due_date.isoformat()}."
            if ncr.corrective_action_due_date
            else ""
        )
        warnings = await send_stage_notification(
            db,
            ncr,
            NotifyEvent.CAPA_ASSIGNED,
            current,
            f"{actor_name} assigned you as the corrective action "
            f"owner for this NCR.{due}",
        )
    return NcrMutationOut(ncr=await _detail(db, ncr, current), warnings=warnings)


def _capa_close_blockers(ncr: Ncr) -> list[str]:
    """What still blocks closure under the API Q1 §6.4.2 gate. Empty when the
    CA question is answered 'No' (with justification, enforced at entry) or
    answered 'Yes' with a complete, verified-effective action plan."""
    if ncr.corrective_action_required is None:
        return ["'Corrective Action Required?' has not been answered"]
    if not ncr.corrective_action_required:
        return []
    problems = []
    if not (ncr.root_cause or "").strip():
        problems.append("root cause is missing")
    if ncr.root_cause_category_id is None:
        problems.append("root cause category is not set")
    if not (ncr.corrective_action_plan or "").strip():
        problems.append("corrective action plan is missing")
    if ncr.corrective_action_owner_id is None:
        problems.append("no corrective action owner is assigned")
    if ncr.corrective_action_due_date is None:
        problems.append("no corrective action due date is set")
    if ncr.effectiveness_result != "effective":
        problems.append(
            "effectiveness verification has not confirmed the corrective action "
            "as effective"
        )
    return problems


@router.post("/ncrs/{ncr_ref}/costing", response_model=NcrMutationOut)
async def costing(
    ncr_ref: str,
    payload: CostingIn,
    current: CurrentUser = Depends(require_roles(Role.COSTING)),
    db: AsyncSession = Depends(get_db),
) -> NcrMutationOut:
    """Stage 6 — Costing. Saving costs completes the workflow and closes the
    NCR — provided the CAPA section passes the API Q1 closure gate."""
    ncr = await _get_ncr(db, ncr_ref)
    _ensure_stage(ncr, Stage.COSTING)

    blockers = _capa_close_blockers(ncr)
    if blockers:
        raise HTTPException(
            status_code=409,
            detail=f"{ncr.ncr_number} cannot be closed: " + "; ".join(blockers) + ". "
            "Complete the CAPA section first.",
        )

    now = utcnow()
    apply_field_updates(
        db,
        ncr,
        current.id,
        {
            "labor_cost": payload.labor_cost,
            "material_cost": payload.material_cost,
            "service_cost": payload.service_cost,
            "other_cost": payload.other_cost,
            "costing_completed_at": now,
            "costing_completed_by_id": current.id,
            "closed_at": now,
            "closed_by_id": current.id,
        },
        action="costing",
    )
    _do_transition(db, ncr, Stage.CLOSED, "complete_costing", current)
    await db.commit()
    ncr = await _refetch(db, ncr.id)
    warnings = await send_stage_notification(
        db,
        ncr,
        NotifyEvent.CLOSED,
        current,
        f"Costing is complete and your NCR has been closed. Total cost of "
        f"nonconformance: ${ncr.total_cost:,.2f}.",
    )
    return NcrMutationOut(ncr=await _detail(db, ncr, current), warnings=warnings)


@router.post("/ncrs/{ncr_ref}/reopen", response_model=NcrMutationOut)
async def reopen(
    ncr_ref: str,
    payload: ReopenIn,
    current: CurrentUser = Depends(require_roles(Role.ADMIN)),
    db: AsyncSession = Depends(get_db),
) -> NcrMutationOut:
    """Admin-only: reopen a closed NCR into a chosen prior stage. The reason
    is required and recorded in the audit trail and transition history."""
    ncr = await _get_ncr(db, ncr_ref)
    if ncr.stage != Stage.CLOSED.value:
        raise HTTPException(status_code=409, detail="Only closed NCRs can be reopened.")
    if payload.to_stage == Stage.SECONDARY_DISPOSITION and not ncr.secondary_assignee_rows:
        raise HTTPException(
            status_code=422,
            detail="This NCR has no secondary authorities assigned; reopen it to "
            "New Request so a disposition authority can assign them.",
        )

    target_idx = _STAGE_ORDER.index(payload.to_stage)
    resets: dict = {"closed_at": None, "closed_by_id": None}
    if target_idx <= _STAGE_ORDER.index(Stage.OPERATIONS):
        resets.update(
            {
                "operations_complete": False,
                "operations_completed_at": None,
                "operations_completed_by_id": None,
            }
        )
    if target_idx <= _STAGE_ORDER.index(Stage.QC_INSPECTION):
        resets.update({"qc_closed": False, "qc_closed_at": None, "qc_closed_by_id": None})
    if target_idx <= _STAGE_ORDER.index(Stage.COSTING):
        resets.update({"costing_completed_at": None, "costing_completed_by_id": None})
    apply_field_updates(db, ncr, current.id, resets, action="reopen")
    _do_transition(
        db, ncr, payload.to_stage, "reopen", current, note=f"Reopen reason: {payload.reason}"
    )
    await db.commit()
    ncr = await _refetch(db, ncr.id)
    warnings = await send_stage_notification(
        db,
        ncr,
        NotifyEvent.REOPENED,
        current,
        f"{current.user.display_name} reopened this NCR to "
        f"'{STAGE_LABELS[payload.to_stage]}'. Reason: {payload.reason}",
    )
    return NcrMutationOut(ncr=await _detail(db, ncr, current), warnings=warnings)


def _do_transition(
    db: AsyncSession,
    ncr: Ncr,
    to_stage: Stage,
    action: str,
    current: CurrentUser,
    note: str | None = None,
) -> None:
    try:
        transition(db, ncr, to_stage, action=action, actor_id=current.id, note=note)
    except InvalidTransitionError as exc:
        raise HTTPException(status_code=409, detail=str(exc)) from exc


# ── attachments ──────────────────────────────────────────────────────────────
@router.post("/ncrs/{ncr_ref}/attachments", response_model=list[AttachmentOut], status_code=201)
async def upload_attachments(
    ncr_ref: str,
    files: list[UploadFile],
    current: CurrentUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
) -> list[AttachmentOut]:
    """Photo/file attachments (multiple per request; camera capture on
    tablets posts here too). Blocked once the NCR is closed."""
    ncr = await _get_ncr(db, ncr_ref)
    if ncr.stage == Stage.CLOSED.value:
        raise HTTPException(
            status_code=409, detail="This NCR is closed; attachments are locked."
        )
    if not files:
        raise HTTPException(status_code=422, detail="No files provided.")

    saved: list[Attachment] = []
    for f in files:
        try:
            meta = await save_attachment(f, ncr.id)
        except UploadValidationError as exc:
            raise HTTPException(status_code=422, detail=str(exc)) from exc
        att = Attachment(ncr_id=ncr.id, uploaded_by_id=current.id, **meta)
        db.add(att)
        audit_event(
            db,
            ncr_id=ncr.id,
            user_id=current.id,
            action="attachment_add",
            field_name="attachments",
            new_value=meta["original_filename"],
            detail=f"{meta['size_bytes']} bytes, {meta['content_type']}",
        )
        saved.append(att)
    await db.commit()
    for att in saved:
        await db.refresh(att)
    return [AttachmentOut.model_validate(a) for a in saved]


@router.get("/attachments/{attachment_id}/download")
async def download_attachment(
    attachment_id: int,
    _: CurrentUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
) -> FileResponse:
    att = await db.get(Attachment, attachment_id)
    if att is None:
        raise HTTPException(status_code=404, detail="Attachment not found.")
    path = attachment_abs_path(att.stored_path)
    if not path.is_file():
        raise HTTPException(status_code=404, detail="Attachment file missing from storage.")
    return FileResponse(
        path,
        media_type=att.content_type,
        filename=att.original_filename,
        content_disposition_type="inline" if att.is_image else "attachment",
    )


# ── audit history ────────────────────────────────────────────────────────────
@router.get("/ncrs/{ncr_ref}/audit", response_model=AuditListOut)
async def ncr_audit(
    ncr_ref: str,
    current: CurrentUser = Depends(require_roles(Role.QC_INSPECTOR)),
    db: AsyncSession = Depends(get_db),
) -> AuditListOut:
    """Audit History tab — Admin and QC roles."""
    from app.models import AuditLog

    ncr = await _get_ncr(db, ncr_ref)
    rows = (
        (
            await db.execute(
                select(AuditLog)
                .where(AuditLog.ncr_id == ncr.id)
                .order_by(AuditLog.created_at.desc(), AuditLog.id.desc())
            )
        )
        .scalars()
        .all()
    )
    return AuditListOut(
        items=[AuditEntryOut.model_validate(r) for r in rows], total=len(rows)
    )


# ── printable PDF ────────────────────────────────────────────────────────────
@router.get("/ncrs/{ncr_ref}/pdf")
async def ncr_pdf(
    ncr_ref: str,
    current: CurrentUser = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
) -> Response:
    """Clean single-document rendering of the complete NCR for hard-copy
    travelers and audits."""
    from app.services.pdf import render_ncr_pdf

    ncr = await _get_ncr(db, ncr_ref)
    related_ncrs, _ = await _ncr_links(db, ncr.id)
    pdf_bytes = await render_ncr_pdf(ncr, [r.ncr_number for r in related_ncrs])
    return Response(
        content=pdf_bytes,
        media_type="application/pdf",
        headers={
            "Content-Disposition": f'inline; filename="{ncr.ncr_number}.pdf"'
        },
    )