"""BPM read-only selectors — all data access goes through here."""

from __future__ import annotations

from uuid import UUID

from django.db.models import Avg, Count, Q, QuerySet

from simorgh.apps.bpm.models import (
    ControlPointExecution,
    PCFElement,
    PCFFramework,
    ProcessControlPoint,
    ProcessDataItem,
    ProcessDefinition,
    ProcessInstance,
    ProcessInstanceRuleCheck,
    ProcessInstanceStep,
    ProcessIO,
    ProcessKPI,
    ProcessKPIMeasurement,
    ProcessOperationalStep,
    ProcessRole,
    ProcessRule,
    RACIEntry,
    RACIMatrix,
)


# ---------------------------------------------------------------------------
# PCF / Framework selectors
# ---------------------------------------------------------------------------


def list_frameworks() -> QuerySet:
    return PCFFramework.objects.filter(is_active=True).order_by("code")


def get_framework_by_code(code: str) -> PCFFramework:
    return PCFFramework.objects.get(code=code, is_active=True)


def list_framework_elements(framework: PCFFramework) -> QuerySet:
    """Return all elements for a framework ordered by hierarchy."""
    return (
        PCFElement.objects.filter(framework=framework)
        .select_related("parent")
        .order_by("hierarchy_id")
    )


# ---------------------------------------------------------------------------
# Process selectors
# ---------------------------------------------------------------------------


def list_processes(tenant) -> QuerySet:
    return (
        ProcessDefinition.objects.filter(tenant=tenant)
        .select_related("framework", "pcf_element", "parent")
        .order_by("hierarchy_id", "name")
    )


def get_process(public_id: UUID, tenant) -> ProcessDefinition:
    return ProcessDefinition.objects.select_related(
        "framework", "pcf_element", "parent", "tenant"
    ).get(public_id=public_id, tenant=tenant)


def list_process_roles(process: ProcessDefinition) -> QuerySet:
    return ProcessRole.objects.filter(process=process).order_by("order", "code")


def list_process_raci(process: ProcessDefinition) -> QuerySet:
    return (
        RACIEntry.objects.filter(matrix__process=process)
        .select_related("matrix", "step", "role")
        .order_by("matrix__process", "step__order", "role__order")
    )


def list_process_kpis(process: ProcessDefinition) -> QuerySet:
    return (
        ProcessKPI.objects.filter(process=process)
        .select_related("owner_role")
        .order_by("code")
    )


def list_process_rules(process: ProcessDefinition) -> QuerySet:
    return ProcessRule.objects.filter(process=process).order_by("order", "code")


def list_process_control_points(process: ProcessDefinition) -> QuerySet:
    return (
        ProcessControlPoint.objects.filter(process=process)
        .select_related("responsible_role")
        .order_by("code")
    )


def list_process_io(process: ProcessDefinition, direction: str | None = None) -> QuerySet:
    qs = ProcessIO.objects.filter(process=process).select_related("data_item")
    if direction:
        qs = qs.filter(direction=direction)
    return qs.order_by("direction", "data_item__code")


def list_process_steps(process: ProcessDefinition) -> QuerySet:
    return ProcessOperationalStep.objects.filter(process=process).order_by("step_number")


# ---------------------------------------------------------------------------
# Instance selectors
# ---------------------------------------------------------------------------


def list_instances(tenant) -> QuerySet:
    return (
        ProcessInstance.objects.filter(tenant=tenant)
        .select_related("process", "created_by")
        .order_by("-created_at")
    )


def get_instance(public_id: UUID, tenant) -> ProcessInstance:
    return ProcessInstance.objects.select_related(
        "process", "created_by", "workspace"
    ).get(public_id=public_id, tenant=tenant)


def list_instance_steps(instance: ProcessInstance) -> QuerySet:
    return (
        ProcessInstanceStep.objects.filter(instance=instance)
        .select_related("step", "assigned_to")
        .order_by("step__order")
    )


def list_instance_rule_checks(instance: ProcessInstance) -> QuerySet:
    return (
        ProcessInstanceRuleCheck.objects.filter(instance=instance)
        .select_related("rule", "checked_by")
        .order_by("rule__order")
    )


def list_instance_cp_executions(instance: ProcessInstance) -> QuerySet:
    return (
        ControlPointExecution.objects.filter(instance=instance)
        .select_related("control_point", "executed_by")
        .order_by("executed_at")
    )


def list_instance_kpi_gauges(instance: ProcessInstance) -> list[dict]:
    """Return each KPI of the process with its latest measurement for this instance."""
    kpis = ProcessKPI.objects.filter(process=instance.process).order_by("code")
    result = []
    for kpi in kpis:
        measurement = (
            ProcessKPIMeasurement.objects
            .filter(kpi=kpi, process_instance_id=instance.public_id)
            .order_by("-measured_at")
            .first()
        )
        result.append({
            "kpi_code": kpi.code,
            "kpi_name": kpi.name,
            "kpi_name_fa": kpi.name_fa if hasattr(kpi, "name_fa") else "",
            "kpi_category": kpi.category,
            "target_value": str(kpi.target_value),
            "target_operator": kpi.target_operator,
            "unit": kpi.unit,
            "value": str(measurement.value) if measurement else None,
            "measured_at": measurement.measured_at.isoformat() if measurement else None,
            "is_target_met": measurement.is_target_met if measurement else None,
        })
    return result


# ---------------------------------------------------------------------------
# KPI selectors
# ---------------------------------------------------------------------------


def get_kpi_dashboard(tenant) -> dict:
    """Aggregate KPI compliance across all processes for a tenant."""
    kpis = ProcessKPI.objects.filter(process__tenant=tenant).select_related(
        "process", "owner_role"
    )
    total = kpis.count()
    result = {
        "total_kpis": total,
        "by_process": [],
        "by_category": {},
        "recent_alerts": [],
    }

    # Per-process summary
    process_qs = (
        ProcessDefinition.objects.filter(tenant=tenant)
        .annotate(kpi_count=Count("kpis"))
        .filter(kpi_count__gt=0)
        .order_by("hierarchy_id")
    )
    for process in process_qs:
        result["by_process"].append(
            {
                "process_id": str(process.public_id),
                "process_name": process.name,
                "kpi_count": process.kpi_count,
            }
        )

    # By category
    from simorgh.apps.bpm.models.kpi import KPICategory  # noqa: PLC0415

    for cat in KPICategory:
        result["by_category"][cat.value] = kpis.filter(category=cat).count()

    # Recent alerts (latest 5)
    from simorgh.apps.bpm.models import KPIAlert  # noqa: PLC0415

    alerts = (
        KPIAlert.objects.filter(kpi__process__tenant=tenant)
        .select_related("kpi", "measurement")
        .order_by("-measurement__measured_at")[:5]
    )
    for alert in alerts:
        result["recent_alerts"].append(
            {
                "kpi_code": alert.kpi.code,
                "kpi_name": alert.kpi.name,
                "alert_type": alert.alert_type,
                "value": float(alert.measurement.value),
                "measured_at": alert.measurement.measured_at.isoformat(),
            }
        )

    return result


# ---------------------------------------------------------------------------
# Benchmarking (Phase 13)
# ---------------------------------------------------------------------------

def list_benchmarks(pcf_metric=None, industry=None):
    """Return APQCBenchmark queryset, optionally filtered."""
    from simorgh.apps.bpm.models import APQCBenchmark  # noqa: PLC0415

    qs = APQCBenchmark.objects.select_related("pcf_metric").order_by(
        "pcf_metric", "-year", "industry"
    )
    if pcf_metric is not None:
        qs = qs.filter(pcf_metric=pcf_metric)
    if industry is not None:
        qs = qs.filter(industry=industry)
    return qs


def list_process_benchmark_comparisons(process):
    """Return OrgBenchmarkComparison for a process, latest first."""
    from simorgh.apps.bpm.models import OrgBenchmarkComparison  # noqa: PLC0415

    return (
        OrgBenchmarkComparison.objects.filter(process=process)
        .select_related("kpi", "benchmark__pcf_metric", "assessed_by")
        .order_by("-assessment_date")
    )


def get_benchmark_summary(process):
    """Return list of dicts with latest comparison per KPI for a process."""
    from simorgh.apps.bpm.models import OrgBenchmarkComparison, ProcessKPI  # noqa: PLC0415

    kpis = ProcessKPI.objects.filter(process=process).order_by("code")
    result = []
    for kpi in kpis:
        comparison = (
            OrgBenchmarkComparison.objects.filter(process=process, kpi=kpi)
            .select_related("benchmark__pcf_metric")
            .order_by("-assessment_date")
            .first()
        )
        entry = {
            "kpi_code": kpi.code,
            "kpi_name": kpi.name,
            "kpi_name_fa": kpi.name_fa,
            "unit": kpi.unit,
            "org_value": None,
            "percentile_position": None,
            "gap_to_median": None,
            "percentile_25": None,
            "percentile_50": None,
            "percentile_75": None,
            "percentile_90": None,
            "industry": None,
            "year": None,
        }
        if comparison:
            b = comparison.benchmark
            entry["org_value"] = str(comparison.org_value)
            entry["percentile_position"] = float(comparison.percentile_position) if comparison.percentile_position is not None else None
            entry["gap_to_median"] = str(comparison.gap_to_median) if comparison.gap_to_median is not None else None
            entry["percentile_25"] = str(b.percentile_25)
            entry["percentile_50"] = str(b.percentile_50)
            entry["percentile_75"] = str(b.percentile_75)
            entry["percentile_90"] = str(b.percentile_90)
            entry["industry"] = b.industry
            entry["year"] = b.year
        result.append(entry)
    return result


# ---------------------------------------------------------------------------
# Maturity Assessment (Phase 14)
# ---------------------------------------------------------------------------

def list_maturity_levels():
    """Return all MaturityLevel records ordered by level."""
    from simorgh.apps.bpm.models import MaturityLevel  # noqa: PLC0415
    return MaturityLevel.objects.all().order_by("level")


def list_process_maturity_assessments(process):
    """Return all assessments for a process, latest first."""
    from simorgh.apps.bpm.models import ProcessMaturityAssessment  # noqa: PLC0415
    return (
        ProcessMaturityAssessment.objects.filter(process=process)
        .select_related("assessed_by")
        .order_by("-assessment_date")
    )


def get_latest_maturity_assessment(process):
    """Return the most recent assessment for a process, or None."""
    from simorgh.apps.bpm.models import ProcessMaturityAssessment  # noqa: PLC0415
    return (
        ProcessMaturityAssessment.objects.filter(process=process)
        .select_related("assessed_by")
        .order_by("-assessment_date")
        .first()
    )


def list_maturity_improvement_actions(assessment):
    """Return all improvement actions for an assessment."""
    from simorgh.apps.bpm.models import MaturityImprovementAction  # noqa: PLC0415
    return (
        MaturityImprovementAction.objects.filter(assessment=assessment)
        .select_related("owner")
        .order_by("due_date", "status")
    )


def get_org_maturity_heatmap(tenant):
    """Return a heatmap list: [{process_id, process_name, hierarchy_id, current_level, target_level, gap}].

    Includes only processes that have at least one assessment.
    """
    from simorgh.apps.bpm.models import ProcessDefinition, ProcessMaturityAssessment  # noqa: PLC0415

    processes = ProcessDefinition.objects.filter(tenant=tenant).order_by("hierarchy_id")
    result = []
    for process in processes:
        latest = (
            ProcessMaturityAssessment.objects.filter(process=process)
            .order_by("-assessment_date")
            .first()
        )
        result.append({
            "process_id": str(process.public_id),
            "process_name": process.name,
            "hierarchy_id": process.hierarchy_id,
            "current_level": latest.current_level if latest else None,
            "target_level": latest.target_level if latest else None,
            "gap": latest.gap_to_target if latest else None,
            "assessment_date": latest.assessment_date.isoformat() if latest else None,
        })
    return result