"""Scoped SQL inventory queries; no Python ledger summation."""
from django.db.models import BigIntegerField, Q, Sum, Value
from django.db.models.functions import Coalesce

from apps.access.authorization import authorized_queryset, require_permission
from apps.organization.models import Company, ServiceCenter
from apps.parts.locking import persisted_pk
from apps.parts.models import SparePart
from .models import InventoryLocation, SerializedStockUnit, StockLedgerEntry

VIEW_PERMISSION = "inventory.view_stock"
USABLE_TYPES = ("WAREHOUSE", "STORE")


def authorized_locations(*, actor, permission=VIEW_PERMISSION):
    companies = authorized_queryset(user=actor, permission=permission, queryset=Company.objects.all())
    centers = authorized_queryset(user=actor, permission=permission, queryset=ServiceCenter.objects.all())
    return InventoryLocation.objects.filter(
        Q(service_center__isnull=True, company_id__in=companies.values("pk"))
        | Q(service_center_id__in=centers.values("pk"))).select_related("company", "service_center__region").order_by("company_id", "code")


def _location(location, actor):
    pk = persisted_pk(location, InventoryLocation)
    # Querying inactive locations is permitted for authorized historical review.
    row = InventoryLocation.objects.select_related("company", "service_center").get(pk=pk)
    require_permission(user=actor, permission=VIEW_PERMISSION, target=row.service_center or row.company)
    return row


def _on_hand(location, spare_part):
    return StockLedgerEntry.objects.filter(location=location, spare_part=spare_part).aggregate(
        value=Coalesce(Sum("quantity_delta"), Value(0), output_field=BigIntegerField()))["value"]


def stock_on_hand(*, actor, location, spare_part):
    persisted_pk(spare_part, SparePart)
    return _on_hand(_location(location, actor), spare_part)


def available_stock(*, actor, location, spare_part):
    location = _location(location, actor)
    pk = persisted_pk(spare_part, SparePart)
    eligible = InventoryLocation.objects.filter(pk=location.pk, is_active=True, company__is_active=True).filter(
        Q(service_center__isnull=True) | Q(service_center__is_active=True, service_center__region__is_active=True)).exists()
    if (not eligible or location.location_type not in USABLE_TYPES
            or not SparePart.objects.filter(pk=pk, is_active=True, category__is_active=True).exists()):
        return 0
    from .request_services import reserved_quantity
    return _on_hand(location, spare_part) - reserved_quantity(location, spare_part)


def stock_positions_for_location(*, actor, location):
    location = _location(location, actor)
    return StockLedgerEntry.objects.filter(location=location).values("spare_part_id", "spare_part__part_code", "spare_part__name").annotate(
        on_hand=Sum("quantity_delta")).order_by("spare_part__part_code")


def stock_positions_for_part(*, actor, spare_part):
    pk = persisted_pk(spare_part, SparePart)
    return StockLedgerEntry.objects.filter(spare_part_id=pk, location_id__in=authorized_locations(actor=actor).values("pk")).values(
        "location_id", "location__code", "company_id", "location__location_type").annotate(on_hand=Sum("quantity_delta")).order_by("company_id", "location__code")


def serialized_history(*, actor, unit):
    from .models import StockMovementUnit
    pk = persisted_pk(unit, SerializedStockUnit)
    # Every returned movement must be visible on at least one relevant endpoint;
    # company-scoped identities never expose another company's ledger.
    locations = authorized_locations(actor=actor).values("pk")
    return StockMovementUnit.objects.filter(unit_id=pk).filter(
        Q(movement__source_id__in=locations) | Q(movement__destination_id__in=locations)).select_related(
        "movement__source", "movement__destination", "movement__actor", "unit__spare_part").order_by("movement__posted_at", "pk")