from django.db.models import BigIntegerField, Case, Count, F, OuterRef, Q, Subquery, Sum, Value, When
from django.db.models.functions import Coalesce

from apps.inventory.control_queries import control_positions
from apps.inventory.models import (SerializedStockUnit, StockLedgerEntry, PartsDisposition, DefectiveRecovery, StockReservation, StockTransferLine, StockCount)
from .permissions import INVENTORY
from .scope import locations
from .service_analytics import event_cases, event_engineer, product_group, PRODUCT_DIMENSIONS
from .filters import period
from .query import grouped, daily, table


def positions(user, filters, *, permission=INVENTORY, export=False):
    visible = locations(user, permission, filters, export=export)
    rows = control_positions(actor=user, permission=permission).filter(location_id__in=visible.values("pk"))
    units = SerializedStockUnit.objects.filter(current_location_id=OuterRef("location_id"), spare_part_id=OuterRef("spare_part_id"), state__in=["IN_STOCK", "IN_TRANSIT", "IN_CUSTODY"]).order_by().values("current_location_id", "spare_part_id").annotate(n=Count("pk")).values("n")
    usable = Q(location__is_active=True, location__company__is_active=True, location__location_type__in=["WAREHOUSE", "STORE"], spare_part__is_active=True, spare_part__category__is_active=True) & (Q(location__service_center=None) | Q(location__service_center__is_active=True, location__service_center__region__is_active=True))
    return rows.annotate(available=Case(When(usable, then=F("on_hand") - F("reserved")), default=Value(0), output_field=BigIntegerField()),
        serialized_units=Coalesce(Subquery(units), Value(0), output_field=BigIntegerField())).annotate(nonserialized_quantity=F("on_hand") - F("serialized_units"))


def usage(user, filters, *, permission=INVENTORY, export=False):
    visible = locations(user, permission, filters, export=export)
    rows = PartsDisposition.objects.filter(issue__service_case_id__in=event_cases(user, permission, filters, export).values("pk"), issue__custody_location_id__in=visible.values("pk"))
    rows = event_engineer(rows, "issue__engineer_assignment__engineer_id", filters)
    return period(rows, "recorded_at", filters)


def inventory_tables(user, filters, *, permission=INVENTORY, export=False):
    visible = locations(user, permission, filters, export=export)
    pos = positions(user, filters, permission=permission, export=export)
    position_columns = [("location__company__code", "Company"), ("location__code", "Location"), ("location__location_type", "Location type"), ("spare_part__part_code", "Part"), ("on_hand", "On hand"), ("available", "Available"), ("reserved", "Reserved"), ("serialized_units", "Serialized units"), ("nonserialized_quantity", "Nonserialized quantity")]
    position_fields = [field for field, _ in position_columns]
    result = [
        table("positions", "Current inventory position", pos.order_by("location_id", "spare_part_id", "pk").values(*position_fields), position_columns, "Current ledger balances; dates do not reconstruct an as-of position. Serialized/nonserialized amounts include OPTIONAL_SERIAL mixed stock."),
        table("lowest_available", "Lowest available recorded stock positions", pos.order_by("available", "location_id", "spare_part_id", "pk").values(*position_fields), position_columns, "No reorder threshold or FAST/SLOW policy is inferred."),
        table("zero_stock", "Zero on-hand recorded positions", pos.filter(on_hand=0).order_by("location_id", "spare_part_id", "pk").values(*position_fields), position_columns, "Existing position anchors only; never-stocked global parts are not assumed to be stocked at every location."),
        table("location_types", "Current stock by location type", grouped(pos, ["location__location_type"], on_hand=Sum("on_hand"), available=Sum("available"), reserved=Sum("reserved"), serialized=Sum("serialized_units"), nonserialized=Sum("nonserialized_quantity")), [("location__location_type", "Location type"), ("on_hand", "On hand"), ("available", "Available"), ("reserved", "Reserved"), ("serialized", "Serialized units"), ("nonserialized", "Nonserialized quantity")]),
    ]
    rows = usage(user, filters, permission=permission, export=export)
    metrics = dict(consumed=Sum("quantity", filter=Q(kind="CONSUMED"), default=0), returned_unused=Sum("quantity", filter=Q(kind="RETURNED"), default=0), net_consumption=Sum("quantity", filter=Q(kind="CONSUMED"), default=0))
    cols = [("consumed", "Consumed"), ("returned_unused", "Returned unused"), ("net_consumption", "Net consumed")]
    note = "Disposition recorded date. Unused returns are not consumption reversals: net consumed equals consumed. Inventory evidence, not invoice lines."
    part_fields = ["movement__spare_part_id", "movement__spare_part__part_code", "movement__spare_part__name"]
    result.append(table("usage_parts", "Parts consumption velocity", grouped(rows, part_fields, **metrics).order_by("-net_consumption", "movement__spare_part_id"), [("movement__spare_part__part_code", "Part"), ("movement__spare_part__name", "Part name"), *cols], note))
    for dimension in PRODUCT_DIMENSIONS:
        dimensional, label = product_group(rows, "issue__service_case__", dimension)
        result.append(table("usage_" + dimension, "Parts usage by " + label, grouped(dimensional, part_fields + ["dimension_id", "dimension"], **metrics), [("movement__spare_part__part_code", "Part"), ("dimension", label), *cols], note))
    for key, identity, field, label in (("engineer", "issue__engineer_assignment__engineer_id", "issue__engineer_assignment__engineer_id", "Engineer ID"), ("case", "issue__service_case_id", "issue__service_case__job_number", "Job"), ("action", "repair_action__repair_action_id", "repair_action__repair_action__name", "Repair action")):
        fields = list(dict.fromkeys(part_fields + [identity, field]))
        labels = [("movement__spare_part__part_code", "Part"), (field, label)]
        if identity != field:
            labels.append((identity, label + " ID"))
        result.append(table("usage_" + key, "Parts usage by " + label, grouped(rows, fields, **metrics), labels + cols, note))
    result.append(table("usage_trend", "Parts consumption trend", daily(rows, "recorded_at", **metrics), [("day", "Recorded local date"), *cols], note))
    result.append(table("usage_records", "Parts disposition drill-down", rows.order_by("recorded_at", "pk").values("pk", "recorded_at", "issue__service_case__job_number", "movement__spare_part__part_code", "kind", "quantity"), [("pk", "Disposition ID"), ("recorded_at", "Recorded"), ("issue__service_case__job_number", "Job"), ("movement__spare_part__part_code", "Part"), ("kind", "Kind"), ("quantity", "Quantity")], note))
    ledger = period(StockLedgerEntry.objects.filter(location_id__in=visible.values("pk")), "movement__posted_at", filters)
    result.append(table("movements", "Posted movements at authorized locations", grouped(ledger, ["location_id", "location__company__code", "location__code", "movement__kind", "spare_part_id", "spare_part__part_code"], delta=Sum("quantity_delta"), entries=Count("pk")), [("location__company__code", "Company"), ("location__code", "Location"), ("movement__kind", "Movement kind"), ("spare_part__part_code", "Part"), ("delta", "Signed stock delta"), ("entries", "Ledger entries")], "Posted timestamp, signed endpoint entries grouped by authoritative location ID. Internal movements have two entries; do not sum their absolute quantities as transferred units."))
    transfers = StockTransferLine.objects.filter(transfer__source_id__in=visible.values("pk"), transfer__destination_id__in=visible.values("pk"), dispatch_movement__isnull=False)
    transfers = period(transfers, "dispatch_movement__posted_at", filters)
    result.append(table("transfers", "Posted transfer dispatches", grouped(transfers, ["spare_part_id", "spare_part__part_code"], quantity=Sum("quantity")), [("spare_part__part_code", "Part"), ("quantity", "Dispatched quantity")], "Both document endpoints must be authorized, matching frozen transfer disclosure. One dispatch per line; arrival is not another dispatch."))
    reservations = period(StockReservation.objects.filter(location_id__in=visible.values("pk"), service_case_id__in=event_cases(user, permission, filters, export).values("pk")), "reserved_at", filters)
    result.append(table("reservations", "Authoritative reservation events", grouped(reservations, ["spare_part_id", "spare_part__part_code", "status"], quantity=Sum("quantity")), [("spare_part__part_code", "Part"), ("status", "Current reservation status"), ("quantity", "Reserved event quantity")], "Reserved timestamp; actual reservations are authoritative, draft requests are excluded. Reservations are not stock movements."))
    recovery = DefectiveRecovery.objects.filter(service_case_id__in=event_cases(user, permission, filters, export).values("pk"), location_id__in=visible.values("pk"))
    recovery = event_engineer(recovery, "repair_action__repair_execution__engineer_assignment__engineer_id", filters)
    result.append(table("recovery", "Removed customer component recovery", grouped(period(recovery, "recovered_at", filters), ["location_id", "location__company__code", "location__code", "service_case__service_center_id", "service_case__service_center__name"], quantity=Sum("quantity")), [("location__company__code", "Company"), ("location__code", "Location"), ("service_case__service_center__name", "ServiceCenter"), ("quantity", "Recovered components")], "Recovered timestamp. Separate customer-component evidence; never counted as replacement stock."))
    counts = period(StockCount.objects.filter(location_id__in=visible.values("pk"), status="RECONCILED"), "finished_at", filters).annotate(variance=F("counted_quantity") - F("expected_quantity"))
    result.append(table("count_variances", "Reconciled physical count variances", counts.order_by("finished_at", "pk").values("pk", "location__code", "spare_part__part_code", "expected_quantity", "counted_quantity", "variance"), [("pk", "Count ID"), ("location__code", "Location"), ("spare_part__part_code", "Part"), ("expected_quantity", "Expected at count start"), ("counted_quantity", "Observed"), ("variance", "Variance")], "Finished timestamp; excludes drafts, active and cancelled counts. No financial valuation."))
    return result