"""Scoped stock control queues and SQL integrity diagnostics."""
from django.db.models import BigIntegerField, Case, Count, F, IntegerField, OuterRef, Q, Subquery, Sum, Value, When, Exists
from django.db.models.functions import Coalesce
from .models import StockCount, StockAdjustment, StockPosition, StockLedgerEntry, StockReservation, SerializedStockUnit, StockMovementUnit, PartsIssue, PartsRequest
from .queries import authorized_locations
from .usage_queries import parts_issues
from .request_queries import parts_requests


def stock_counts(*, actor, permission="inventory.view_stock"):
    return StockCount.objects.filter(location_id__in=authorized_locations(actor=actor, permission=permission).values("pk")).select_related(
        "company", "location", "spare_part", "created_by", "started_by", "finished_by").order_by("created_at", "pk")


def stock_adjustments(*, actor):
    return StockAdjustment.objects.filter(location_id__in=authorized_locations(actor=actor).values("pk")).select_related(
        "company", "location", "spare_part", "actor", "movement", "count").order_by("created_at", "pk")


def control_positions(*, actor, permission="inventory.view_stock"):
    ledger = StockLedgerEntry.objects.filter(location_id=OuterRef("location_id"), spare_part_id=OuterRef("spare_part_id")).order_by().values("location_id", "spare_part_id")
    reservations = StockReservation.objects.filter(location_id=OuterRef("location_id"), spare_part_id=OuterRef("spare_part_id")).order_by().values("location_id", "spare_part_id")
    def scalar(rows, expression):
        return Coalesce(Subquery(rows.annotate(value=expression).values("value")[:1]), Value(0), output_field=BigIntegerField())
    return StockPosition.objects.filter(location_id__in=authorized_locations(actor=actor, permission=permission).values("pk")).select_related(
        "location", "spare_part").annotate(on_hand=scalar(ledger, Sum("quantity_delta")), ledger_count=scalar(ledger, Count("pk")),
        reservation_count=scalar(reservations, Count("pk")), reserved=scalar(reservations.filter(status="ACTIVE"), Sum("quantity"))).order_by("location_id", "spare_part_id")


def inventory_anomalies(*, actor):
    """Return lazy scoped querysets; detection does not mutate or repair history."""
    positions = control_positions(actor=actor)
    locations = authorized_locations(actor=actor).values("pk")
    visible_links = StockMovementUnit.objects.filter(unit_id=OuterRef("pk")).filter(Q(movement__source_id__in=locations) | Q(movement__destination_id__in=locations))
    current_visible = visible_links.filter(movement_id=OuterRef("current_movement_id"))
    links = StockMovementUnit.objects.filter(unit_id=OuterRef("pk")).order_by().values("unit_id")
    total = links.annotate(value=Sum(Case(When(movement__source=None, then=Value(1)), default=Value(0), output_field=IntegerField()))
        - Sum(Case(When(movement__destination=None, then=Value(1)), default=Value(0), output_field=IntegerField()))).values("value")[:1]
    current = links.annotate(value=Sum(Case(When(movement__destination_id=OuterRef("current_location_id"), then=Value(1)), default=Value(0), output_field=IntegerField()))
        - Sum(Case(When(movement__source_id=OuterRef("current_location_id"), then=Value(1)), default=Value(0), output_field=IntegerField()))).values("value")[:1]
    units = SerializedStockUnit.objects.filter(Exists(current_visible) | (Q(state="REGISTERED") & Exists(visible_links))).annotate(
        ledger_total=Coalesce(Subquery(total), Value(0)), ledger_current=Coalesce(Subquery(current), Value(0)))
    active = Q(state__in=["IN_STOCK", "IN_TRANSIT", "IN_CUSTODY"])
    inconsistent = units.filter((active & (~Q(ledger_total=1) | ~Q(ledger_current=1)))
        | (~active & ~Q(ledger_total=0)) | Q(state="REGISTERED"))
    return {
        "negative_stock": positions.filter(on_hand__lt=0),
        "over_reserved": positions.filter(reserved__gt=F("on_hand")),
        "serialized_position_mismatch": inconsistent.order_by("pk"),
        "issue_reservation_mismatch": parts_issues(actor=actor).exclude(reservation__status="ISSUED"),
        "over_resolved_issue": parts_issues(actor=actor).filter(resolved_quantity__gt=F("reservation__quantity")),
        "issued_reservation_without_issue": StockReservation.objects.filter(location_id__in=locations, status="ISSUED", issue__isnull=True).order_by("pk"),
        "empty_request": parts_requests(actor=actor).filter(lines__isnull=True),
    }