from datetime import timedelta

from django.db.models import Avg, Count, DurationField, ExpressionWrapper, F, Q, Value, Case, When, CharField
from django.utils import timezone

from apps.service.models import ServiceCase, ServiceDiagnosticAssessment, ServiceCaseHandover, ServiceCaseHandoverAccessory, ServiceCaseClosure, ServiceEngineerAssignment
from .filters import period
from .permissions import OPERATIONAL
from .scope import cases
from .query import daily, durations, grouped, table
from .service_analytics import event_cases, event_engineer, repairs, quality_controls

BUCKETS = ((2, "0–1 day"), (4, "2–3 days"), (8, "4–7 days"), (15, "8–14 days"), (31, "15–30 days"))
TERMINAL = ("CLOSED", "CANCELLED")


def aging(rows, field, *, now=None):
    now = now or timezone.now()
    return rows.filter(**{field + "__lte": now}).annotate(age_bucket=Case(
        *(When(**{field + "__gt": now - timedelta(days=days)}, then=Value(label)) for days, label in BUCKETS),
        default=Value("31+ days"), output_field=CharField()))


def dashboard(user, filters, *, permission=OPERATIONAL, export=False):
    rows = cases(user, permission, filters, export=export)
    today = timezone.localdate()
    received = period(rows, "received_at", filters)
    counts = rows.aggregate(current_open=Count("pk", filter=~Q(status__in=TERMINAL)),
        ready_for_delivery=Count("pk", filter=Q(status="READY_FOR_DELIVERY")),
        currently_delivered=Count("pk", filter=Q(status="DELIVERED")))
    counts.update(received_today=period(rows, "received_at", {"date_from": today, "date_to": today}).count(),
        received_in_period=received.count(),
        closed_in_period=period(rows, "closure__closed_at", filters).filter(closure__isnull=False).count(),
        cancelled_in_period=period(rows.filter(status="CANCELLED"), "cancelled_at", filters).count(),
        delivered_in_period=period(rows, "handover__delivered_at", filters).filter(handover__isnull=False).count())
    return counts


def turnaround(user, filters, *, permission=OPERATIONAL, export=False):
    scoped = event_cases(user, permission, filters, export)
    diagnoses = ServiceDiagnosticAssessment.objects.filter(service_case_id__in=scoped.values("pk"), completed_at__isnull=False)
    diagnoses = event_engineer(diagnoses, "engineer_assignment__engineer_id", filters)
    repair = repairs(user, filters, permission=permission, export=export).filter(status="COMPLETED", outcome="REPAIRED")
    qc = quality_controls(user, filters, permission=permission, export=export).filter(status="COMPLETED", outcome="PASSED")
    handovers = ServiceCaseHandover.objects.filter(service_case_id__in=cases(user, permission, filters, export=export).values("pk"))
    closures = ServiceCaseClosure.objects.filter(service_case_id__in=cases(user, permission, filters, export=export).values("pk"))
    definitions = (
        ("Received → diagnosis completed", diagnoses, "service_case__received_at", "completed_at"),
        ("Diagnosis → successful repair", repair, "diagnostic_assessment__completed_at", "completed_at"),
        ("Repair → QC pass", qc, "repair_execution__completed_at", "completed_at"),
        ("QC pass → delivery", handovers, "delivery_release__quality_control__completed_at", "delivered_at"),
        ("Received → delivery", handovers, "service_case__received_at", "delivered_at"),
        ("Received → closure", closures, "service_case__received_at", "closed_at"),
    )
    return [dict(metric=label, **durations(period(rows, end, filters), start, end)) for label, rows, start, end in definitions]


def engineer_tables(user, filters, *, permission=OPERATIONAL, export=False):
    scoped = cases(user, permission, filters, export=export)
    assignments = ServiceEngineerAssignment.objects.filter(service_case_id__in=scoped.values("pk"), ended_at=None).exclude(service_case__status__in=TERMINAL)
    if filters.get("engineer"):
        assignments = assignments.filter(engineer_id=filters["engineer"])
    fields = ["engineer_id", "engineer__first_name", "engineer__last_name", "service_case__service_center_id", "service_case__service_center__name"]
    workload = grouped(assignments, fields, assigned=Count("pk"), diagnosing=Count("pk", filter=Q(service_case__status="DIAGNOSING")),
        awaiting_repair=Count("pk", filter=Q(service_case__status="DIAGNOSED")), repairing=Count("pk", filter=Q(service_case__status="REPAIRING")))
    completed = period(repairs(user, filters, permission=permission, export=export).filter(status="COMPLETED"), "completed_at", filters)
    completed = completed.annotate(elapsed=ExpressionWrapper(F("completed_at") - F("started_at"), output_field=DurationField()))
    return [
        table("engineer_workload", "Current engineer workload", workload, [("engineer_id", "Engineer ID"), ("engineer__first_name", "First name"), ("engineer__last_name", "Last name"), ("service_case__service_center__name", "ServiceCenter"), ("assigned", "Assigned"), ("diagnosing", "Diagnosing"), ("awaiting_repair", "Awaiting repair"), ("repairing", "Repairing")], "Current nonterminal cases; date filters do not restrict this queue."),
        table("engineer_completed", "Engineer completed repairs in period", grouped(completed, ["engineer_assignment__engineer_id", "service_case__service_center_id", "service_case__service_center__name"], completed=Count("pk"), rework=Count("pk", filter=Q(after_failed_qc=True)), rework_cases=Count("service_case_id", distinct=True, filter=Q(after_failed_qc=True)), average=Avg("elapsed")), [("engineer_assignment__engineer_id", "Engineer ID"), ("service_case__service_center__name", "ServiceCenter"), ("completed", "Completed repairs"), ("rework", "Completed attempts after failed QC"), ("rework_cases", "Distinct rework cases"), ("average", "Average repair duration")]),
        table("engineer_queue", "Current engineer case queue", assignments.order_by("engineer_id", "service_case__received_at", "pk").values("pk", "engineer_id", "engineer__first_name", "engineer__last_name", "service_case_id", "service_case__job_number", "service_case__service_center__name", "service_case__device__product_model__name", "service_case__status"), [("engineer_id", "Engineer ID"), ("engineer__first_name", "First name"), ("engineer__last_name", "Last name"), ("service_case__job_number", "Job"), ("service_case__service_center__name", "ServiceCenter"), ("service_case__device__product_model__name", "Model"), ("service_case__status", "Status")]),
    ]


def dashboard_tables(user, filters, *, permission=OPERATIONAL, export=False):
    rows = cases(user, permission, filters, export=export)
    open_rows = rows.exclude(status__in=TERMINAL)
    handed = ServiceCaseHandover.objects.filter(service_case_id__in=rows.values("pk"))
    handed = period(handed, "delivered_at", filters)
    accessories = ServiceCaseHandoverAccessory.objects.filter(handover_id__in=handed.values("pk"), returned_quantity__lt=F("intake_accessory__quantity"))
    case_fields = [("pk", "Case ID"), ("job_number", "Job"), ("service_center__name", "ServiceCenter"), ("device__product_model__name", "Model"), ("status", "Status"), ("received_at", "Received")]
    tables = [
        table("cases", "Cases received in period", period(rows, "received_at", filters).order_by("received_at", "pk").values(*(f for f, _ in case_fields)), case_fields),
        table("open_cases", "Current open cases", open_rows.order_by("received_at", "pk").values(*(f for f, _ in case_fields)), case_fields, "Includes delivered cases awaiting closure; excludes CLOSED and CANCELLED."),
        table("workflow", "Current workflow", grouped(rows, ["status"]), [("status", "Status"), ("count", "Cases")], "Current state, independent of selected dates."),
        table("aging", "Current open-case aging", grouped(aging(open_rows, "received_at"), ["age_bucket"]), [("age_bucket", "Elapsed age"), ("count", "Open cases")], "Elapsed 24-hour days: [0,2), [2,4), [4,8), [8,15), [15,31), [31,∞). No SLA target is defined."),
        table("received_trend", "Received trend", daily(period(rows, "received_at", filters), "received_at"), [("day", "Received local date"), ("count", "Cases")]),
        table("closure_trend", "Closure trend", daily(period(rows.filter(closure__isnull=False), "closure__closed_at", filters), "closure__closed_at"), [("day", "Closure local date"), ("count", "Cases")]),
        table("awaiting_delivery", "Current awaiting-delivery aging", grouped(aging(rows.filter(status="READY_FOR_DELIVERY"), "delivery_release__readied_at"), ["age_bucket"]), [("age_bucket", "Elapsed age since release"), ("count", "Cases")]),
        table("recipient_types", "Delivery recipient types", grouped(handed, ["recipient_type"]), [("recipient_type", "Recipient type"), ("count", "Deliveries")], "Delivery timestamp; no recipient personal details."),
        table("accessory_discrepancies", "Delivery accessory shortages", grouped(accessories, ["handover__service_case__service_center_id", "handover__service_case__service_center__name"]), [("handover__service_case__service_center__name", "ServiceCenter"), ("count", "Accessory rows with shortage")]),
    ]
    return tables + engineer_tables(user, filters, permission=permission, export=export)