"""SQL work eligibility and queues. These do not authorize the caller."""
from django.contrib.auth import get_user_model
from django.contrib.auth.models import Permission
from django.db.models import Count, Exists, F, OuterRef, Q, Subquery

from apps.access.scopes import assignment_is_valid, company_scope
from apps.organization.models import ServiceCenter, UserOrganizationAssignment
from .models import ServiceCase, ServiceEngineerAssignment
from .queries import _saved, _rows

ENGINEER_PERMISSION = "service.handle_servicecase"


def _known_permission(codename="handle_servicecase"):
    return Permission.objects.filter(content_type__app_label="service", codename=codename).values(
        "content_type__app_label", "codename").annotate(matches=Count("pk")).filter(matches=1)


def _paths(*, user, company, region, center, codename="handle_servicecase"):
    # Explicit ServiceCenter duty semantics, matching the frozen center adapter.
    # All permission predicates share ONE role-assignment join on ONE org path.
    return UserOrganizationAssignment.objects.filter(assignment_is_valid()).filter(
        user_id=user, company_id=company,
        role_assignments__user_id=F("user_id"), role_assignments__is_active=True,
        role_assignments__role__is_active=True,
        role_assignments__role__permissions__content_type__app_label="service",
        role_assignments__role__permissions__codename=codename,
    ).filter(company_scope() | Q(region_id=region, service_center__isnull=True, department__isnull=True)
             | Q(service_center_id=center))


def _operational():
    return ServiceCase.objects.filter(
        status__in=[ServiceCase.Status.RECEIVED, ServiceCase.Status.ASSIGNED, ServiceCase.Status.DIAGNOSING, ServiceCase.Status.DIAGNOSED, ServiceCase.Status.REPAIRING, ServiceCase.Status.REPAIRED, ServiceCase.Status.QC_PENDING, ServiceCase.Status.QC_IN_PROGRESS, ServiceCase.Status.QC_PASSED, ServiceCase.Status.READY_FOR_DELIVERY],
        company__is_active=True, service_center__is_active=True,
        service_center__company_id=F("company_id"), service_center__region__is_active=True,
        service_center__region__company_id=F("company_id"),
    )


def eligible_engineers_for_service_case(service_case):
    users = get_user_model().objects.all()
    if not _saved(service_case, ServiceCase):
        return users.none()
    case = _operational().filter(pk=service_case.pk).order_by()
    paths = _paths(user=OuterRef("pk"), company=Subquery(case.values("company_id")[:1]),
                   region=Subquery(case.values("service_center__region_id")[:1]),
                   center=Subquery(case.values("service_center_id")[:1]))
    # No superuser bypass for receiving work: a real active posting is required.
    return users.filter(is_active=True).filter(Exists(case), Exists(_known_permission()), Exists(paths)).order_by("username", "pk")


def engineer_assignment_history(service_case):
    rows = ServiceEngineerAssignment.objects.select_related("engineer", "assigned_by", "ended_by", "service_case")
    return rows.filter(service_case_id=service_case.pk) if _saved(service_case, ServiceCase) else rows.none()


def current_engineer_assignment(service_case):
    return engineer_assignment_history(service_case).filter(ended_at__isnull=True).first()


def current_engineer(service_case):
    assignment = current_engineer_assignment(service_case)
    return assignment.engineer if assignment else None


def _queue():
    current = ServiceEngineerAssignment.objects.filter(service_case_id=OuterRef("pk"), ended_at__isnull=True)
    return _rows().filter(pk__in=_operational().values("pk")).annotate(
        _engineer_id=Subquery(current.values("engineer_id")[:1]),
    ).order_by("received_at", "job_number", "pk")


def unassigned_service_cases_for_center(service_center):
    rows = _queue().filter(status="RECEIVED", _engineer_id__isnull=True)
    return rows.filter(service_center_id=service_center.pk) if _saved(service_center, ServiceCenter) else rows.none()


def _assigned_queue():
    paths = _paths(user=OuterRef("_engineer_id"), company=OuterRef("company_id"),
                   region=OuterRef("service_center__region_id"), center=OuterRef("service_center_id"))
    return _queue().filter(status__in=["ASSIGNED", "DIAGNOSING", "DIAGNOSED", "REPAIRING", "REPAIRED", "QC_PENDING", "QC_IN_PROGRESS", "QC_PASSED", "READY_FOR_DELIVERY"], _engineer_id__isnull=False).filter(Exists(_known_permission()), Exists(paths))


def assigned_service_cases_for_engineer(engineer):
    rows = _assigned_queue()
    return rows.filter(_engineer_id=engineer.pk) if _saved(engineer, get_user_model()) else rows.none()


def service_center_engineer_work_queue(service_center):
    rows = _assigned_queue()
    return rows.filter(service_center_id=service_center.pk) if _saved(service_center, ServiceCenter) else rows.none()