"""Authoritative intake, completed diagnosis and performed-action co-occurrence."""
from django.db.models import Count, Exists, F, OuterRef, Q, Value, CharField
from django.db.models.functions import Coalesce

from apps.service.models import (
    ServiceCaseComplaint, ServiceDiagnosticFinding, ServiceRepairAction,
    ServiceRepairExecution, ServiceQualityControl, ServiceQualityControlCheck,
    ServiceQualityControlComplaintCheck, ServiceEngineerAssignment,
)
from .filters import period
from .permissions import SERVICE
from .scope import cases
from .query import grouped, daily, durations, table, totals, combined_totals

UNKNOWN = "Unknown / Unconfirmed"
PRODUCT_DIMENSIONS = {
    "brand": ("device__product_model__brand_id", "device__product_model__brand__name", "Brand"),
    "product_category": ("device__product_model__category_id", "device__product_model__category__name", "Product Category"),
    "model": ("device__product_model_id", "device__product_model__name", "Model"),
    "variant": ("device__product_variant_id", "device__product_variant__name", "Variant"),
    "service_center": ("service_center_id", "service_center__name", "ServiceCenter"),
}


RATES = (("repair", "Repair success (completion period)"), ("first_pass", "First-pass QC yield (first completion period)"),
         ("rework", "Repair attempts after failed QC (start period)"))


def rate_totals(user, filters, *, permission=SERVICE, export=False):
    """Lazy single-row counts behind service_rates(), so a page can read them with its other totals."""
    repair = period(repairs(user, filters, permission=permission, export=export).filter(status="COMPLETED"), "completed_at", filters)
    qc = period(quality_controls(user, filters, permission=permission, export=export).filter(status="COMPLETED", first_completed_attempt=True), "completed_at", filters)
    started = period(repairs(user, filters, permission=permission, export=export), "started_at", filters)
    return {"repair": totals(repair, denominator=Count("pk"), numerator=Count("pk", filter=Q(outcome="REPAIRED"))),
            "first_pass": totals(qc, denominator=Count("pk"), numerator=Count("pk", filter=Q(outcome="PASSED"))),
            "rework": totals(started, denominator=Count("pk"), numerator=Count("pk", filter=Q(after_failed_qc=True)))}


def rates_from(counts):
    result = []
    for key, label in RATES:
        denominator, numerator = counts[key]["denominator"], counts[key]["numerator"]
        result.append(dict(metric=label, denominator=denominator, numerator=numerator, percent=100 * numerator / denominator if denominator else None))
    return result


def service_rates(user, filters, *, permission=SERVICE, export=False):
    """Explicit operational ratios, with no employee score or inferred causality."""
    return rates_from(combined_totals(**rate_totals(user, filters, permission=permission, export=export)))


def event_cases(user, permission, filters, export=False):
    # Event engineer is the event's attributed assignment, not today's assignment.
    return cases(user, permission, {k: v for k, v in filters.items() if k != "engineer"}, export=export)


def event_engineer(rows, field, filters):
    return rows.filter(**{field: filters["engineer"]}) if filters.get("engineer") else rows


def product_group(rows, prefix, dimension):
    identity, name, label = PRODUCT_DIMENSIONS[dimension]
    return rows.annotate(dimension_id=F(prefix + identity), dimension=F(prefix + name)), label


def complaint_rows(user, filters, *, permission=SERVICE, export=False):
    rows = period(ServiceCaseComplaint.objects.filter(service_case_id__in=cases(user, permission, filters, export=export).values("pk"), removed_at=None), "service_case__received_at", filters)
    return rows.filter(complaint_symptom_id=filters["complaint"]) if filters.get("complaint") else rows


def complaint_frequency(user, filters, *, dimension="complaint", permission=SERVICE, export=False):
    rows = complaint_rows(user, filters, permission=permission, export=export)
    fields = ["complaint_symptom_id", "complaint_symptom__name"]
    if dimension in PRODUCT_DIMENSIONS:
        rows, _ = product_group(rows, "service_case__", dimension)
        fields += ["dimension_id", "dimension"]
    return grouped(rows, fields)


def diagnosis_rows(user, filters, *, permission=SERVICE, export=False):
    rows = ServiceDiagnosticFinding.objects.filter(assessment__service_case_id__in=event_cases(user, permission, filters, export).values("pk"),
        assessment__completed_at__isnull=False, assessment__abandoned_at=None, removed_at=None)
    rows = event_engineer(rows, "assessment__engineer_assignment__engineer_id", filters)
    rows = finding_filters(rows, filters)
    return period(rows, "assessment__completed_at", filters).annotate(root_cause_label=Coalesce("root_cause__name", Value(UNKNOWN), output_field=CharField()))


def finding_filters(rows, filters):
    for key in ("fault_diagnosis", "root_cause"):
        if filters.get(key):
            rows = rows.filter(**{key + "_id": filters[key]})
    if filters.get("unknown_root_cause"):
        rows = rows.filter(root_cause=None)
    if filters.get("complaint"):
        rows = rows.filter(Exists(ServiceCaseComplaint.objects.filter(service_case_id=OuterRef("assessment__service_case_id"), removed_at=None, complaint_symptom_id=filters["complaint"])))
    return rows


def performed_actions(user, filters, *, permission=SERVICE, export=False):
    rows = ServiceRepairAction.objects.filter(repair_execution__service_case_id__in=event_cases(user, permission, filters, export).values("pk"), performed_at__isnull=False)
    rows = event_engineer(rows, "repair_execution__engineer_assignment__engineer_id", filters)
    if filters.get("repair_action"):
        rows = rows.filter(repair_action_id=filters["repair_action"])
    # Soft-removed performed actions remain actual performed evidence.
    return period(rows, "performed_at", filters)


def repair_action_diagnosis_cooccurrence(user, filters, *, grouping="diagnosis_root_cause", dimension=None, permission=SERVICE, export=False):
    """Count DISTINCT action IDs in assessment-level groups; never causal attribution."""
    if grouping not in {"diagnosis", "root_cause", "diagnosis_root_cause"}:
        raise ValueError("Unsupported co-occurrence grouping.")
    actions = performed_actions(user, filters, permission=permission, export=export)
    findings = ServiceDiagnosticFinding.objects.filter(removed_at=None, assessment__completed_at__isnull=False,
        assessment__repair_executions__actions__pk__in=actions.values("pk"))
    findings = finding_filters(findings, filters)
    findings = findings.annotate(action_id=F("assessment__repair_executions__actions__repair_action_id"),
        action_name=F("assessment__repair_executions__actions__repair_action__name"),
        root_cause_label=Coalesce("root_cause__name", Value(UNKNOWN), output_field=CharField()))
    fields = ["action_id", "action_name"]
    if grouping in {"diagnosis", "diagnosis_root_cause"}:
        fields += ["fault_diagnosis_id", "fault_diagnosis__name"]
    if grouping in {"root_cause", "diagnosis_root_cause"}:
        fields += ["root_cause_id", "root_cause_label"]
    if dimension:
        if dimension not in PRODUCT_DIMENSIONS:
            raise ValueError("Unsupported product dimension.")
        findings, _ = product_group(findings, "assessment__service_case__", dimension)
        fields += ["dimension_id", "dimension"]
    return grouped(findings, fields, performed_actions=Count("assessment__repair_executions__actions__pk", distinct=True))


def repairs(user, filters, *, permission=SERVICE, export=False):
    rows = ServiceRepairExecution.objects.filter(service_case_id__in=event_cases(user, permission, filters, export).values("pk"))
    rows = event_engineer(rows, "engineer_assignment__engineer_id", filters)
    if filters.get("event_outcome"):
        rows = rows.filter(outcome=filters["event_outcome"])
    previous = ServiceRepairExecution.objects.filter(service_case_id=OuterRef("service_case_id")).filter(Q(started_at__lt=OuterRef("started_at")) | Q(started_at=OuterRef("started_at"), pk__lt=OuterRef("pk")))
    failed = ServiceQualityControl.objects.filter(service_case_id=OuterRef("service_case_id"), outcome="FAILED", completed_at__lt=OuterRef("started_at"))
    return rows.annotate(repeat_attempt=Exists(previous), after_failed_qc=Exists(failed))


def quality_controls(user, filters, *, permission=SERVICE, export=False):
    rows = ServiceQualityControl.objects.filter(service_case_id__in=event_cases(user, permission, filters, export).values("pk"))
    rows = event_engineer(rows, "repair_execution__engineer_assignment__engineer_id", filters)
    if filters.get("event_outcome"):
        rows = rows.filter(outcome=filters["event_outcome"])
    earlier = ServiceQualityControl.objects.filter(service_case_id=OuterRef("service_case_id"), status="COMPLETED").filter(
        Q(completed_at__lt=OuterRef("completed_at")) | Q(completed_at=OuterRef("completed_at"), pk__lt=OuterRef("pk")))
    return rows.annotate(first_completed_attempt=~Exists(earlier))


def service_tables(user, filters, *, permission=SERVICE, export=False):
    result = []
    for dimension in ["complaint", *PRODUCT_DIMENSIONS]:
        cols = [("complaint_symptom__name", "ComplaintSymptom")]
        if dimension != "complaint":
            cols.append(("dimension", PRODUCT_DIMENSIONS[dimension][2]))
        title = "Intake complaint frequency" + (" by " + PRODUCT_DIMENSIONS[dimension][2] if dimension != "complaint" else " by ComplaintSymptom")
        result.append(table("complaints_" + dimension, title, complaint_frequency(user, filters, dimension=dimension, permission=permission, export=export), cols + [("count", "Intake complaint records")], "Received-date cohort; retained, nonremoved complaints. Applicability is not classification."))
    complaints = complaint_rows(user, filters, permission=permission, export=export)
    result.append(table("complaint_records", "Intake complaint drill-down", complaints.order_by("service_case__received_at", "pk").values("pk", "service_case__job_number", "complaint_symptom__name", "service_case__received_at"), [("pk", "Complaint record ID"), ("service_case__job_number", "Job"), ("complaint_symptom__name", "ComplaintSymptom"), ("service_case__received_at", "Received")]))
    result.append(table("complaint_trend", "Intake complaint trend", daily(complaints, "service_case__received_at"), [("day", "Received local date"), ("count", "Complaints")]))
    findings = diagnosis_rows(user, filters, permission=permission, export=export)
    result.append(table("diagnosis_records", "Completed diagnosis drill-down", findings.order_by("assessment__completed_at", "pk").values("pk", "assessment__service_case__job_number", "fault_diagnosis__name", "root_cause_label", "assessment__completed_at"), [("pk", "Finding ID"), ("assessment__service_case__job_number", "Job"), ("fault_diagnosis__name", "FaultDiagnosis"), ("root_cause_label", "RootCause"), ("assessment__completed_at", "Completed")]))
    for key, fields, labels in [
        ("diagnoses", ["fault_diagnosis_id", "fault_diagnosis__name"], [("fault_diagnosis__name", "FaultDiagnosis")]),
        ("root_causes", ["root_cause_id", "root_cause_label"], [("root_cause_label", "RootCause")]),
    ]:
        result.append(table(key, "Completed diagnosis findings", grouped(findings, fields), labels + [("count", "Findings")], "Assessment completion date; NULL cause means Unknown / Unconfirmed."))
    for dimension in ("model", "service_center"):
        rows, label = product_group(findings, "assessment__service_case__", dimension)
        result.append(table("diagnosis_" + dimension, "Completed diagnosis by " + label, grouped(rows, ["fault_diagnosis_id", "fault_diagnosis__name", "dimension_id", "dimension"]), [("fault_diagnosis__name", "FaultDiagnosis"), ("dimension", label), ("count", "Findings")]))
    complaint_group = {"assessment__service_case__complaints__removed_at": None,
                       "assessment__service_case__complaints__isnull": False}
    if filters.get("complaint"):
        complaint_group["assessment__service_case__complaints__complaint_symptom_id"] = filters["complaint"]
    by_complaint = findings.filter(**complaint_group)
    result.append(table("diagnosis_complaint", "Diagnosis / intake complaint co-occurrence", grouped(by_complaint, ["fault_diagnosis_id", "fault_diagnosis__name", "assessment__service_case__complaints__complaint_symptom_id", "assessment__service_case__complaints__complaint_symptom__name"]), [("fault_diagnosis__name", "FaultDiagnosis"), ("assessment__service_case__complaints__complaint_symptom__name", "ComplaintSymptom"), ("count", "Distinct findings")], "Same-case co-occurrence, not attribution. Groups overlap."))
    result.append(table("diagnosis_trend", "Completed diagnosis trend", daily(findings, "assessment__completed_at"), [("day", "Completion local date"), ("count", "Findings")]))
    actions = performed_actions(user, filters, permission=permission, export=export)
    result.append(table("actions", "Performed repair actions", grouped(actions, ["repair_action_id", "repair_action__name"]), [("repair_action__name", "Repair action"), ("count", "Distinct performed actions")], "Performed date. Includes retained performed evidence even if subsequently removed from the plan."))
    for dimension in PRODUCT_DIMENSIONS:
        action_rows, label = product_group(actions, "repair_execution__service_case__", dimension)
        result.append(table("actions_" + dimension, "Performed repair actions by " + label, grouped(action_rows, ["repair_action_id", "repair_action__name", "dimension_id", "dimension"]), [("repair_action__name", "Repair action"), ("dimension", label), ("count", "Distinct performed actions")], "Performed date. Unique action counts independent of diagnostic co-occurrence groupings."))
    for grouping in ("diagnosis", "root_cause", "diagnosis_root_cause"):
        for dimension in (None, *PRODUCT_DIMENSIONS):
            cols = [("action_name", "Repair action")]
            if grouping != "root_cause":
                cols.append(("fault_diagnosis__name", "FaultDiagnosis"))
            if grouping != "diagnosis":
                cols.append(("root_cause_label", "RootCause"))
            if dimension:
                cols.append(("dimension", PRODUCT_DIMENSIONS[dimension][2]))
            label = {"diagnosis": "Diagnosis", "root_cause": "Root Cause", "diagnosis_root_cause": "Diagnosis and Root Cause"}[grouping]
            title = "Repair Action / " + label + " Co-occurrence" + (" by " + PRODUCT_DIMENSIONS[dimension][2] if dimension else "")
            result.append(table("cooccurrence_" + grouping + ("_" + dimension if dimension else ""), title, repair_action_diagnosis_cooccurrence(user, filters, grouping=grouping, dimension=dimension, permission=permission, export=export), cols + [("performed_actions", "Distinct performed actions in group")], "Referenced assessment only. Groups overlap and MUST NOT be added to obtain a unique action total. The separate distinct performed-action total is independent of diagnostic group filters."))
    repair = repairs(user, filters, permission=permission, export=export)
    started = period(repair, "started_at", filters)
    completed = period(repair.filter(status="COMPLETED"), "completed_at", filters)
    result.append(table("completed_repair_records", "Completed repair drill-down", completed.order_by("completed_at", "pk").values("pk", "service_case__job_number", "outcome", "completed_at"), [("pk", "Execution ID"), ("service_case__job_number", "Job"), ("outcome", "Outcome"), ("completed_at", "Completed")]))
    result.append(table("repair_attempts", "Repair attempts started in period", grouped(started, ["status", "outcome", "repeat_attempt", "after_failed_qc"]), [("status", "Current attempt status"), ("outcome", "Outcome"), ("repeat_attempt", "Repeat attempt"), ("after_failed_qc", "After failed QC"), ("count", "Attempts")]))
    result.append(table("repair_completed", "Repairs completed in period", grouped(completed, ["outcome"]), [("outcome", "Outcome"), ("count", "Completed executions")], "Success denominator is completed REPAIRED + NOT_REPAIRED executions; excludes open and abandoned attempts."))
    qc = quality_controls(user, filters, permission=permission, export=export)
    result.append(table("qc_attempts", "QC attempts started in period", grouped(period(qc, "started_at", filters), ["status", "outcome"]), [("status", "Current attempt status"), ("outcome", "Outcome"), ("count", "Attempts")]))
    completed_qc = period(qc.filter(status="COMPLETED"), "completed_at", filters)
    result.append(table("completed_qc_records", "Completed QC drill-down", completed_qc.order_by("completed_at", "pk").values("pk", "service_case__job_number", "outcome", "completed_at"), [("pk", "QC ID"), ("service_case__job_number", "Job"), ("outcome", "Outcome"), ("completed_at", "Completed")]))
    result.append(table("qc_completed", "Completed QC outcomes", grouped(completed_qc, ["outcome", "first_completed_attempt"]), [("outcome", "Outcome"), ("first_completed_attempt", "First completed QC for case"), ("count", "Attempts")], "First-pass yield = PASSED first completed attempts / all first completed attempts in completion period. Abandoned attempts excluded."))
    checks = ServiceQualityControlCheck.objects.filter(quality_control_id__in=completed_qc.values("pk"), result="FAIL")
    result.append(table("qc_failed_checks", "Failed QC checklist items", grouped(checks, ["check_code", "check_name"]), [("check_code", "Code"), ("check_name", "Checklist item snapshot"), ("count", "Failures")]))
    checks = ServiceQualityControlComplaintCheck.objects.filter(quality_control_id__in=completed_qc.values("pk"), result="NOT_RESOLVED")
    result.append(table("qc_unresolved", "QC unresolved complaints", grouped(checks, ["complaint__complaint_symptom_id", "complaint__complaint_symptom__name"]), [("complaint__complaint_symptom__name", "ComplaintSymptom"), ("count", "Unresolved checks")]))
    failed = completed_qc.filter(outcome="FAILED")
    for dimension in ("model", "service_center"):
        rows, label = product_group(failed, "service_case__", dimension)
        result.append(table("qc_failure_" + dimension, "QC failures by " + label, grouped(rows, ["dimension_id", "dimension"]), [("dimension", label), ("count", "Failed attempts")]))
    result.append(table("qc_failure_actions", "QC failure / performed action co-occurrence", grouped(failed.filter(repair_execution__actions__performed_at__isnull=False), ["repair_execution__actions__repair_action_id", "repair_execution__actions__repair_action__name"]), [("repair_execution__actions__repair_action__name", "Repair action"), ("count", "Distinct failed QC attempts")], "Linked execution co-occurrence; not engineer negligence or causal attribution. Groups overlap."))
    result.append(table("qc_failure_trend", "QC failure trend", daily(failed, "completed_at"), [("day", "Completion local date"), ("count", "Failed attempts")]))
    result.append(table("qc_drilldown", "QC attempt drill-down", period(qc, "started_at", filters).order_by("started_at", "pk").values("pk", "service_case__job_number", "service_case__service_center__name", "started_at", "completed_at", "status", "outcome"), [("pk", "QC ID"), ("service_case__job_number", "Job"), ("service_case__service_center__name", "ServiceCenter"), ("started_at", "Started"), ("completed_at", "Completed"), ("status", "Status"), ("outcome", "Outcome")]))
    return result