"""Module 1 — connection applications: CRUD, filtering, workflow, documents."""
from datetime import date

from fastapi import APIRouter, Depends, File, Form, HTTPException, Query, Request, UploadFile, status
from fastapi.responses import FileResponse
from sqlalchemy import func, or_, select
from sqlalchemy.orm import Session, selectinload

from app.api.deps import client_ip, get_current_user, require_admin, require_write
from app.core.time import utcnow
from app.db.session import get_db
from app.models.application import ApplicationDocument, ConnectionApplication
from app.models.enums import ApplicationStatus, DocumentType, UserRole
from app.models.reference import Area
from app.models.user import User
from app.schemas.application import (
    ApplicationCreate,
    ApplicationDetail,
    ApplicationListItem,
    ApplicationStatusChange,
    ApplicationStatusCount,
    ApplicationUpdate,
    DocumentOut,
)
from app.schemas.common import Message, Page
from app.services import applications as workflow
from app.services import audit, storage
from app.services.numbering import next_number

router = APIRouter(prefix="/applications", tags=["applications"])

#: Detail responses need history + documents; the list view does not.
_DETAIL_LOADS = (
    selectinload(ConnectionApplication.status_history),
    selectinload(ConnectionApplication.documents),
)


def _get_or_404(db: Session, application_id: int, *, detail: bool = False) -> ConnectionApplication:
    stmt = select(ConnectionApplication).where(
        ConnectionApplication.id == application_id,
        ConnectionApplication.is_deleted.is_(False),
    )
    if detail:
        stmt = stmt.options(*_DETAIL_LOADS)
    application = db.scalar(stmt)
    if application is None:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Application not found")
    return application


def _as_detail(application: ConnectionApplication) -> ApplicationDetail:
    out = ApplicationDetail.model_validate(application)
    out.allowed_transitions = workflow.allowed_transitions(application.status)
    # The relationship loads every attachment; soft-deleted ones stay on the row for
    # audit but must not appear in the UI.
    out.documents = [
        DocumentOut.model_validate(d) for d in application.documents if not d.is_deleted
    ]
    return out


def _reload_detail(db: Session, application_id: int) -> ApplicationDetail:
    """Re-read an application after a write.

    The session runs with expire_on_commit=False so routers can still touch attributes
    after commit. The flip side is that a plain re-query returns the *same* cached
    instance, with the joined `area`/`technician` and the status-history collection
    as they were when first loaded — so a fresh PATCH would echo back the old values.
    Expiring first forces the reload.
    """
    db.expire_all()
    return _as_detail(_get_or_404(db, application_id, detail=True))


def _validate_refs(
    db: Session,
    *,
    area_id: int | None,
    technician_id: int | None,
) -> None:
    """Reject unknown/retired FKs with a field-level 422 rather than a bare 409."""
    if area_id is not None and not db.scalar(
        select(Area.id).where(Area.id == area_id, Area.is_deleted.is_(False))
    ):
        raise HTTPException(status_code=422, detail="Unknown area")
    if technician_id is not None and not db.scalar(
        select(User.id).where(
            User.id == technician_id,
            User.is_deleted.is_(False),
            User.is_active.is_(True),
            User.role.in_([UserRole.FIELD_STAFF, UserRole.MANAGER]),
        )
    ):
        raise HTTPException(status_code=422, detail="Unknown or inactive technician")


def _filtered(
    *,
    search: str | None,
    status_in: list[ApplicationStatus] | None,
    area_id: int | None,
    zone_id: int | None,
    technician_id: int | None,
    date_from: date | None,
    date_to: date | None,
):
    stmt = select(ConnectionApplication).where(ConnectionApplication.is_deleted.is_(False))

    if search:
        pattern = f"%{search.strip()}%"
        stmt = stmt.where(
            or_(
                ConnectionApplication.applicant_name.ilike(pattern),
                ConnectionApplication.phone.ilike(pattern),
                ConnectionApplication.application_no.ilike(pattern),
                ConnectionApplication.nid.ilike(pattern),
                ConnectionApplication.address.ilike(pattern),
            )
        )
    if status_in:
        stmt = stmt.where(ConnectionApplication.status.in_(status_in))
    if area_id is not None:
        stmt = stmt.where(ConnectionApplication.area_id == area_id)
    if zone_id is not None:
        stmt = stmt.where(
            ConnectionApplication.area_id.in_(select(Area.id).where(Area.zone_id == zone_id))
        )
    if technician_id is not None:
        stmt = stmt.where(ConnectionApplication.assigned_technician_id == technician_id)
    if date_from is not None:
        stmt = stmt.where(ConnectionApplication.requested_date >= date_from)
    if date_to is not None:
        stmt = stmt.where(ConnectionApplication.requested_date <= date_to)
    return stmt


# --- list / summary ---


@router.get("", response_model=Page[ApplicationListItem])
def list_applications(
    db: Session = Depends(get_db),
    _: User = Depends(get_current_user),
    search: str | None = Query(None, description="Name, phone, application no, NID, or address"),
    status_in: list[ApplicationStatus] | None = Query(None, alias="status"),
    area_id: int | None = None,
    zone_id: int | None = None,
    technician_id: int | None = None,
    date_from: date | None = Query(None, description="requested_date >= (Asia/Dhaka)"),
    date_to: date | None = Query(None, description="requested_date <= (Asia/Dhaka)"),
    sort: str = Query(
        "-created_at",
        pattern="^-?(application_no|applicant_name|phone|area|technician|requested_date|status|created_at)$",
    ),
    page: int = Query(1, ge=1),
    page_size: int = Query(25, ge=1, le=200),
) -> Page[ApplicationListItem]:
    stmt = _filtered(
        search=search,
        status_in=status_in,
        area_id=area_id,
        zone_id=zone_id,
        technician_id=technician_id,
        date_from=date_from,
        date_to=date_to,
    )
    total = db.scalar(select(func.count()).select_from(stmt.subquery())) or 0

    descending = sort.startswith("-")
    field = sort.lstrip("-")
    # Related-name sorts use a correlated subquery rather than a join: `area` and
    # `technician` are already joined eagerly, and re-joining the same table would
    # collide with the eager load's aliasing.
    if field == "area":
        order = select(Area.name).where(Area.id == ConnectionApplication.area_id).scalar_subquery()
    elif field == "technician":
        order = (
            select(User.full_name)
            .where(User.id == ConnectionApplication.assigned_technician_id)
            .scalar_subquery()
        )
    else:
        order = getattr(ConnectionApplication, field)
    stmt = stmt.order_by(order.desc() if descending else order.asc(), ConnectionApplication.id.desc())

    rows = db.scalars(stmt.offset((page - 1) * page_size).limit(page_size)).all()
    return Page.build(
        [ApplicationListItem.model_validate(r) for r in rows], total, page, page_size
    )


@router.get("/status-summary", response_model=list[ApplicationStatusCount])
def status_summary(
    db: Session = Depends(get_db),
    _: User = Depends(get_current_user),
    date_from: date | None = None,
    date_to: date | None = None,
) -> list[ApplicationStatusCount]:
    """Counts per status, for the list-view filter chips and the dashboard."""
    stmt = (
        select(ConnectionApplication.status, func.count())
        .where(ConnectionApplication.is_deleted.is_(False))
        .group_by(ConnectionApplication.status)
    )
    if date_from is not None:
        stmt = stmt.where(ConnectionApplication.requested_date >= date_from)
    if date_to is not None:
        stmt = stmt.where(ConnectionApplication.requested_date <= date_to)

    counts = {status_value: count for status_value, count in db.execute(stmt).all()}
    # Emit every status, including zeroes, so the UI chips never jump around.
    return [
        ApplicationStatusCount(status=s, count=counts.get(s, 0)) for s in ApplicationStatus
    ]


# --- create / read / update / delete ---


@router.post("", response_model=ApplicationDetail, status_code=status.HTTP_201_CREATED)
def create_application(
    payload: ApplicationCreate,
    request: Request,
    db: Session = Depends(get_db),
    current_user: User = Depends(require_write),
) -> ApplicationDetail:
    _validate_refs(
        db,
        area_id=payload.area_id,
        technician_id=payload.assigned_technician_id,
    )

    application = ConnectionApplication(
        application_no=next_number(db, "APP"),
        **payload.model_dump(),
        status=ApplicationStatus.PENDING,
        created_by_id=current_user.id,
    )
    db.add(application)
    db.flush()

    workflow.record_history(
        db,
        application,
        from_status=None,
        to_status=ApplicationStatus.PENDING,
        user_id=current_user.id,
        remarks="Application received",
    )
    audit.record(
        db,
        user_id=current_user.id,
        action="create",
        entity_type="application",
        entity_id=application.id,
        payload={"application_no": application.application_no},
        ip_address=client_ip(request),
    )
    db.commit()
    return _reload_detail(db, application.id)


@router.get("/{application_id}", response_model=ApplicationDetail)
def get_application(
    application_id: int,
    db: Session = Depends(get_db),
    _: User = Depends(get_current_user),
) -> ApplicationDetail:
    return _reload_detail(db, application_id)


@router.patch("/{application_id}", response_model=ApplicationDetail)
def update_application(
    application_id: int,
    payload: ApplicationUpdate,
    request: Request,
    db: Session = Depends(get_db),
    current_user: User = Depends(require_write),
) -> ApplicationDetail:
    application = _get_or_404(db, application_id)
    if application.status in (ApplicationStatus.INSTALLED, ApplicationStatus.REJECTED):
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=f"A '{application.status.value}' application can no longer be edited",
        )

    changes = payload.model_dump(exclude_unset=True)
    if not changes:
        return _reload_detail(db, application_id)

    _validate_refs(
        db,
        area_id=changes.get("area_id"),
        technician_id=changes.get("assigned_technician_id"),
    )
    for field, value in changes.items():
        setattr(application, field, value)

    audit.record(
        db,
        user_id=current_user.id,
        action="update",
        entity_type="application",
        entity_id=application.id,
        payload={k: str(v) for k, v in changes.items()},
        ip_address=client_ip(request),
    )
    db.commit()
    return _reload_detail(db, application_id)


@router.post("/{application_id}/status", response_model=ApplicationDetail)
def change_application_status(
    application_id: int,
    payload: ApplicationStatusChange,
    request: Request,
    db: Session = Depends(get_db),
    current_user: User = Depends(require_write),
) -> ApplicationDetail:
    """Advance the workflow. Approve/reject are manager-or-admin only."""
    application = _get_or_404(db, application_id, detail=True)

    if payload.status in (
        ApplicationStatus.APPROVED,
        ApplicationStatus.REJECTED,
    ) and current_user.role not in (UserRole.ADMIN, UserRole.MANAGER, UserRole.SYSTEM_DEVELOPER):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail=f"Only a manager or admin can set an application to "
            f"'{payload.status.value}'",
        )

    connection = workflow.change_status(db, application, payload, current_user)
    audit.record(
        db,
        user_id=current_user.id,
        action="status_change",
        entity_type="application",
        entity_id=application.id,
        payload={
            "to": payload.status.value,
            **({"connection_code": connection.connection_code} if connection else {}),
        },
        ip_address=client_ip(request),
    )
    db.commit()
    return _reload_detail(db, application_id)


@router.delete("/{application_id}", response_model=Message)
def delete_application(
    application_id: int,
    request: Request,
    db: Session = Depends(get_db),
    current_user: User = Depends(require_admin),
) -> Message:
    """Soft delete. Installed applications are kept — a live connection points at them."""
    application = _get_or_404(db, application_id)
    if application.status == ApplicationStatus.INSTALLED:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="An installed application cannot be deleted",
        )

    application.is_deleted = True
    application.deleted_at = utcnow()
    audit.record(
        db,
        user_id=current_user.id,
        action="delete",
        entity_type="application",
        entity_id=application.id,
        ip_address=client_ip(request),
    )
    db.commit()
    return Message(detail=f"Application {application.application_no} deleted")


# --- documents ---


@router.get("/{application_id}/documents", response_model=list[DocumentOut])
def list_documents(
    application_id: int,
    db: Session = Depends(get_db),
    _: User = Depends(get_current_user),
) -> list[DocumentOut]:
    _get_or_404(db, application_id)
    rows = db.scalars(
        select(ApplicationDocument)
        .where(
            ApplicationDocument.application_id == application_id,
            ApplicationDocument.is_deleted.is_(False),
        )
        .order_by(ApplicationDocument.id)
    ).all()
    return [DocumentOut.model_validate(r) for r in rows]


@router.post(
    "/{application_id}/documents",
    response_model=DocumentOut,
    status_code=status.HTTP_201_CREATED,
)
def upload_document(
    application_id: int,
    request: Request,
    file: UploadFile = File(...),
    doc_type: DocumentType = Form(DocumentType.OTHER),
    db: Session = Depends(get_db),
    current_user: User = Depends(require_write),
) -> DocumentOut:
    application = _get_or_404(db, application_id)
    stored_path, size = storage.save_upload(file, entity="applications", entity_id=application.id)

    document = ApplicationDocument(
        application_id=application.id,
        doc_type=doc_type,
        file_name=storage.sanitize_display_name(file.filename),
        stored_path=stored_path,
        content_type=file.content_type,
        file_size=size,
        uploaded_by_id=current_user.id,
    )
    db.add(document)
    try:
        db.flush()
        audit.record(
            db,
            user_id=current_user.id,
            action="upload_document",
            entity_type="application",
            entity_id=application.id,
            payload={"file_name": document.file_name, "doc_type": doc_type.value},
            ip_address=client_ip(request),
        )
        db.commit()
    except Exception:
        # Never leave an orphan file behind if the row could not be written.
        storage.delete_stored_file(stored_path)
        raise
    db.refresh(document)
    return DocumentOut.model_validate(document)


@router.get("/{application_id}/documents/{document_id}/download")
def download_document(
    application_id: int,
    document_id: int,
    db: Session = Depends(get_db),
    _: User = Depends(get_current_user),
) -> FileResponse:
    document = db.scalar(
        select(ApplicationDocument).where(
            ApplicationDocument.id == document_id,
            ApplicationDocument.application_id == application_id,
            ApplicationDocument.is_deleted.is_(False),
        )
    )
    if document is None:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Document not found")

    return FileResponse(
        storage.resolve_stored_path(document.stored_path),
        media_type=document.content_type or "application/octet-stream",
        filename=document.file_name,
    )


@router.delete("/{application_id}/documents/{document_id}", response_model=Message)
def delete_document(
    application_id: int,
    document_id: int,
    request: Request,
    db: Session = Depends(get_db),
    current_user: User = Depends(require_admin),
) -> Message:
    document = db.scalar(
        select(ApplicationDocument).where(
            ApplicationDocument.id == document_id,
            ApplicationDocument.application_id == application_id,
            ApplicationDocument.is_deleted.is_(False),
        )
    )
    if document is None:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Document not found")

    document.is_deleted = True
    document.deleted_at = utcnow()
    audit.record(
        db,
        user_id=current_user.id,
        action="delete_document",
        entity_type="application",
        entity_id=application_id,
        payload={"file_name": document.file_name},
        ip_address=client_ip(request),
    )
    db.commit()
    return Message(detail=f"Document {document.file_name} deleted")