"""
Analytics Console UI views
"""

from datetime import datetime

from django.contrib.auth.decorators import login_required
from django.core.cache import cache
from django.core.paginator import Paginator
from django.db.models import Avg, Count, F, Q, Value
from django.db.models.functions import Concat
from django.http import JsonResponse
from django.shortcuts import render

from apps.complaints.models import Complaint
from apps.organizations.models import Department, Hospital
from apps.px_action_center.models import PXAction
from apps.surveys.models import SurveyInstance
from apps.physicians.models import PhysicianMonthlyRating

from .models import KPI, KPIValue
from .services import UnifiedAnalyticsService, ExportService
from .services.ai_analytics import (
    ExecutiveSummaryGenerator,
    EarlyWarningSystem,
    ComplaintVolumeForecaster,
    SLABreachPredictor,
    ActionRecommendationEngine,
)
from apps.core.decorators import block_source_user
import json


def serialize_queryset_values(queryset):
    """Properly serialize QuerySet values to JSON string."""
    if queryset is None:
        return "[]"
    data = list(queryset)
    result = []
    for item in data:
        row = {}
        for key, value in item.items():
            # Convert UUID to string
            if hasattr(value, "hex"):  # UUID object
                row[key] = str(value)
            # Convert Python None to JavaScript null
            elif value is None:
                row[key] = None
            else:
                row[key] = value
        result.append(row)
    return json.dumps(result, default=str)


@block_source_user
@login_required
def analytics_dashboard(request):
    """
    Analytics dashboard with KPIs and charts.

    Comprehensive dashboard showing:
    - KPI cards with current values for Complaints, Actions, Surveys, Feedback
    - Trend charts
    - Department rankings
    - Source distribution
    - Status breakdown
    """
    from apps.feedback.models import Feedback
    from apps.complaints.models import Inquiry
    from apps.observations.models import Observation
    from apps.appreciation.models import Appreciation
    from apps.integrations.models import HISPatientVisit
    from django.utils import timezone
    from datetime import timedelta
    from django.db.models.functions import ExtractQuarter, ExtractYear, TruncDate, TruncMonth

    user = request.user

    # Parse year filters for complaints-by-quarter chart
    current_year = timezone.now().year
    chart_from_year = request.GET.get("from_year", str(current_year - 3))
    chart_to_year = request.GET.get("to_year", str(current_year))

    # Build cache key based on user, hospital, and year range
    cache_key = f"analytics_dashboard_{user.id}_{request.GET.get('hospital', 'all')}_{chart_from_year}_{chart_to_year}"
    cached = cache.get(cache_key)
    if cached:
        return render(request, "analytics/dashboard.html", cached)

    # Get hospital filter
    hospital_filter = request.GET.get("hospital")
    if hospital_filter:
        hospital = Hospital.objects.filter(id=hospital_filter).first()
    elif user.is_px_admin() and hasattr(request, "tenant_hospital") and request.tenant_hospital:
        hospital = request.tenant_hospital
    elif user.hospital:
        hospital = user.hospital
    else:
        hospital = None

    # Base querysets
    complaints_queryset = Complaint.objects.all()
    actions_queryset = PXAction.objects.all()
    surveys_queryset = SurveyInstance.objects.filter(status="completed")
    feedback_queryset = Feedback.objects.all()
    inquiry_queryset = Inquiry.objects.all()
    observation_queryset = Observation.objects.all()
    appreciation_queryset = Appreciation.objects.all()

    if hospital:
        complaints_queryset = complaints_queryset.filter(hospital=hospital)
        actions_queryset = actions_queryset.filter(hospital=hospital)
        surveys_queryset = surveys_queryset.filter(survey_template__hospital=hospital)
        feedback_queryset = feedback_queryset.filter(hospital=hospital)
        inquiry_queryset = inquiry_queryset.filter(hospital=hospital)
        observation_queryset = observation_queryset.filter(hospital=hospital)
        appreciation_queryset = appreciation_queryset.filter(hospital=hospital)

    # ============ COMPLAINTS KPIs ============
    # Single query for all status counts
    status_counts_qs = complaints_queryset.values("status").annotate(count=Count("id"))
    status_map = {item["status"]: item["count"] for item in status_counts_qs}
    total_complaints = sum(status_map.values())
    open_complaints = status_map.get("open", 0)
    in_progress_complaints = status_map.get("in_progress", 0)
    resolved_complaints = status_map.get("resolved", 0)
    closed_complaints = status_map.get("closed", 0)
    overdue_complaints = complaints_queryset.filter(is_overdue=True).count()
    reopened_complaints = complaints_queryset.filter(reopened_from__isnull=False).count()
    escalated_ovr_complaints = complaints_queryset.filter(is_escalated_ovr=True).count()

    # Complaint source types (internal vs external) — single query
    source_type_counts = complaints_queryset.values("complaint_source_type").annotate(count=Count("id"))
    source_type_map = {item["complaint_source_type"]: item["count"] for item in source_type_counts}
    internal_complaints = source_type_map.get("internal", 0)
    external_complaints = source_type_map.get("external", 0)

    # Complaint sources (by PXSource name)
    complaint_sources = (
        complaints_queryset.filter(source__isnull=False)
        .values("source__name_en")
        .annotate(count=Count("id"))
        .order_by("-count")[:6]
    )

    # Complaint domains (Level 1)
    top_domains = (
        complaints_queryset.filter(domain__isnull=False)
        .values("domain__name_en")
        .annotate(count=Count("id"))
        .order_by("-count")[:5]
    )

    # Complaint categories (Level 2)
    top_categories = (
        complaints_queryset.filter(category__isnull=False)
        .values("category__name_en")
        .annotate(count=Count("id"))
        .order_by("-count")[:5]
    )

    # Complaint severity — single query
    severity_counts_qs = complaints_queryset.values("severity").annotate(count=Count("id"))
    severity_map = {item["severity"]: item["count"] for item in severity_counts_qs}
    critical_complaints = severity_map.get("critical", 0)
    high_complaints = severity_map.get("high", 0)
    medium_complaints = severity_map.get("medium", 0)
    low_complaints = severity_map.get("low", 0)

    # Severity breakdown for JSON
    severity_breakdown = severity_counts_qs.order_by("-count")

    # Status breakdown
    status_breakdown = status_counts_qs.order_by("-count")

    # Complaints by department (top 10)
    complaints_by_dept = (
        complaints_queryset.filter(department__isnull=False)
        .values("department__name")
        .annotate(count=Count("id"))
        .order_by("-count")[:10]
    )

    # ============ INQUIRY ANALYTICS ============
    inquiry_status_counts = inquiry_queryset.values("status").annotate(count=Count("id")).order_by("-count")
    inquiry_priority_counts = inquiry_queryset.exclude(priority="").values("priority").annotate(count=Count("id")).order_by("-count")
    inquiries_by_dept = (
        inquiry_queryset.filter(department__isnull=False)
        .values("department__name")
        .annotate(count=Count("id"))
        .order_by("-count")[:10]
    )
    inquiries_by_category = inquiry_queryset.exclude(category="").values("category").annotate(count=Count("id")).order_by("-count")[:10]

    # ============ OBSERVATION ANALYTICS ============
    observation_status_counts = observation_queryset.values("status").annotate(count=Count("id")).order_by("-count")
    observation_severity_counts = observation_queryset.values("severity").annotate(count=Count("id")).order_by("-count")
    observations_by_dept = (
        observation_queryset.filter(assigned_department__isnull=False)
        .values("assigned_department__name")
        .annotate(count=Count("id"))
        .order_by("-count")[:10]
    )
    observations_by_category = (
        observation_queryset.filter(category__isnull=False)
        .values("category__name_en")
        .annotate(count=Count("id"))
        .order_by("-count")[:10]
    )

    # ============ SUGGESTION ANALYTICS (Feedback type=suggestion) ============
    suggestion_queryset = feedback_queryset.filter(feedback_type="suggestion")
    suggestion_status_counts = suggestion_queryset.values("status").annotate(count=Count("id")).order_by("-count")
    suggestion_sentiment_counts = suggestion_queryset.exclude(sentiment="").values("sentiment").annotate(count=Count("id")).order_by("-count")
    suggestions_by_dept = (
        suggestion_queryset.filter(department__isnull=False)
        .values("department__name")
        .annotate(count=Count("id"))
        .order_by("-count")[:10]
    )
    suggestions_by_category = suggestion_queryset.exclude(category="").values("category").annotate(count=Count("id")).order_by("-count")[:10]

    # ============ APPRECIATION ANALYTICS ============
    appreciation_status_counts = appreciation_queryset.values("status").annotate(count=Count("id")).order_by("-count")
    appreciations_by_dept = (
        appreciation_queryset.filter(department__isnull=False)
        .values("department__name")
        .annotate(count=Count("id"))
        .order_by("-count")[:10]
    )
    appreciations_by_category = (
        appreciation_queryset.filter(category__isnull=False)
        .values("category__name_en")
        .annotate(count=Count("id"))
        .order_by("-count")[:10]
    )
    appreciations_by_visibility = appreciation_queryset.values("visibility").annotate(count=Count("id")).order_by("-count")

    # ============ ACTIONS KPIs ============
    action_status_counts = actions_queryset.values("status").annotate(count=Count("id"))
    action_status_map = {item["status"]: item["count"] for item in action_status_counts}
    total_actions = sum(action_status_map.values())
    open_actions = action_status_map.get("open", 0)
    in_progress_actions = action_status_map.get("in_progress", 0)
    approved_actions = action_status_map.get("approved", 0)
    closed_actions = action_status_map.get("closed", 0)
    pending_actions = action_status_map.get("pending_approval", 0)
    overdue_actions = actions_queryset.filter(is_overdue=True).count()

    # Action sources
    action_sources = (
        actions_queryset.filter(source_type__isnull=False)
        .values("source_type")
        .annotate(count=Count("id"))
        .order_by("-count")[:6]
    )

    # Action categories - build explicit counts
    action_categories = (
        actions_queryset.exclude(category="").values("category").annotate(count=Count("id")).order_by("-count")[:5]
    )
    action_category_map = {item["category"]: item["count"] for item in action_categories}
    training_actions = action_category_map.get("training", 0)
    process_actions = action_category_map.get("process_improvement", 0)
    policy_actions = action_category_map.get("policy", 0)
    facility_actions = action_category_map.get("facility", 0)
    other_actions = action_category_map.get("other", 0)

    # ============ SURVEYS KPIs ============
    total_surveys = surveys_queryset.count()
    avg_survey_score = surveys_queryset.aggregate(avg=Avg("total_score"))["avg"] or 0
    negative_surveys = surveys_queryset.filter(is_negative=True).count()

    # Survey completion rate — single query
    all_surveys = SurveyInstance.objects.all()
    if hospital:
        all_surveys = all_surveys.filter(survey_template__hospital=hospital)
    survey_status_counts = all_surveys.values("status").annotate(count=Count("id"))
    survey_status_map = {item["status"]: item["count"] for item in survey_status_counts}
    total_sent = sum(survey_status_map.values())
    completed_surveys = survey_status_map.get("completed", 0)
    completion_rate = (completed_surveys / total_sent * 100) if total_sent > 0 else 0

    # Survey types
    survey_types = all_surveys.values("survey_template__survey_type").annotate(count=Count("id")).order_by("-count")[:5]

    # ============ FEEDBACK KPIs ============
    feedback_type_counts = feedback_queryset.values("feedback_type").annotate(count=Count("id"))
    feedback_type_map = {item["feedback_type"]: item["count"] for item in feedback_type_counts}
    total_feedback = sum(feedback_type_map.values())
    compliments = feedback_type_map.get("compliment", 0)
    suggestions = feedback_type_map.get("suggestion", 0)

    # Sentiment analysis
    sentiment_breakdown = feedback_queryset.values("sentiment").annotate(count=Count("id")).order_by("-count")

    # Feedback categories
    feedback_categories = feedback_queryset.values("category").annotate(count=Count("id")).order_by("-count")[:5]

    # Average rating
    avg_rating = feedback_queryset.filter(rating__isnull=False).aggregate(avg=Avg("rating"))["avg"] or 0

    # ============ TRENDS (Last 30 days) ============
    thirty_days_ago = timezone.now() - timedelta(days=30)

    # Complaint trends
    complaint_trend = (
        complaints_queryset.filter(created_at__gte=thirty_days_ago)
        .annotate(day=TruncDate("created_at"))
        .values("day")
        .annotate(count=Count("id"))
        .order_by("day")
    )

    # Complaints by year & quarter (area chart with year range filter)
    try:
        from_year_int = int(chart_from_year)
        to_year_int = int(chart_to_year)
        from_start = datetime(from_year_int, 1, 1, tzinfo=timezone.get_current_timezone())
        to_end = datetime(to_year_int, 12, 31, 23, 59, 59, tzinfo=timezone.get_current_timezone())
    except (ValueError, TypeError):
        from_start = datetime(current_year - 3, 1, 1, tzinfo=timezone.get_current_timezone())
        to_end = datetime(current_year, 12, 31, 23, 59, 59, tzinfo=timezone.get_current_timezone())

    complaints_by_quarter = (
        complaints_queryset.filter(created_at__gte=from_start, created_at__lte=to_end)
        .annotate(year=ExtractYear("created_at"), quarter=ExtractQuarter("created_at"))
        .values("year", "quarter")
        .annotate(count=Count("id"))
        .order_by("year", "quarter")
    )

    inquiries_by_quarter = (
        inquiry_queryset.filter(created_at__gte=from_start, created_at__lte=to_end)
        .annotate(year=ExtractYear("created_at"), quarter=ExtractQuarter("created_at"))
        .values("year", "quarter")
        .annotate(count=Count("id"))
        .order_by("year", "quarter")
    )

    suggestions_by_quarter = (
        feedback_queryset.filter(feedback_type="suggestion", created_at__gte=from_start, created_at__lte=to_end)
        .annotate(year=ExtractYear("created_at"), quarter=ExtractQuarter("created_at"))
        .values("year", "quarter")
        .annotate(count=Count("id"))
        .order_by("year", "quarter")
    )

    observations_by_quarter = (
        observation_queryset.filter(created_at__gte=from_start, created_at__lte=to_end)
        .annotate(year=ExtractYear("created_at"), quarter=ExtractQuarter("created_at"))
        .values("year", "quarter")
        .annotate(count=Count("id"))
        .order_by("year", "quarter")
    )

    appreciations_by_quarter = (
        appreciation_queryset.filter(created_at__gte=from_start, created_at__lte=to_end)
        .annotate(year=ExtractYear("created_at"), quarter=ExtractQuarter("created_at"))
        .values("year", "quarter")
        .annotate(count=Count("id"))
        .order_by("year", "quarter")
    )

    visits_by_quarter_qs = HISPatientVisit.objects.filter(created_at__gte=from_start, created_at__lte=to_end)
    if hospital:
        visits_by_quarter_qs = visits_by_quarter_qs.filter(hospital=hospital)
    visits_by_quarter = (
        visits_by_quarter_qs
        .annotate(year=ExtractYear("created_at"), quarter=ExtractQuarter("created_at"))
        .values("year", "quarter")
        .annotate(count=Count("id"))
        .order_by("year", "quarter")
    )

    # Survey score trend - last 6 months for chart
    six_months_ago = timezone.now() - timedelta(days=180)
    survey_score_trend_6m = (
        surveys_queryset.filter(completed_at__gte=six_months_ago)
        .annotate(month=TruncMonth("completed_at"))
        .values("month")
        .annotate(avg_score=Avg("total_score"))
        .order_by("month")
    )

    # Build survey trend array for last 6 months (pad with zeros if missing)
    from calendar import month_name

    now = timezone.now()
    survey_trend_values = []
    survey_trend_labels = []
    for i in range(5, -1, -1):
        target_month = now.month - i
        target_year = now.year
        while target_month <= 0:
            target_month += 12
            target_year -= 1
        survey_trend_labels.append(month_name[target_month][:3])
        # Find matching data point
        found = None
        for item in survey_score_trend_6m:
            if item["month"].month == target_month and item["month"].year == target_year:
                found = round(item["avg_score"], 2) if item["avg_score"] else 0
                break
        survey_trend_values.append(found if found is not None else 0)

    # ============ DEPARTMENT RANKINGS ============
    dept_base_qs = Department.objects.filter(status="active")
    if hospital:
        dept_base_qs = dept_base_qs.filter(hospital=hospital)

    department_rankings = (
        dept_base_qs.annotate(
            avg_score=Avg(
                "journey_instances__surveys__total_score", filter=Q(journey_instances__surveys__status="completed")
            ),
            survey_count=Count("journey_instances__surveys", filter=Q(journey_instances__surveys__status="completed")),
            complaint_count=Count("complaints"),
            resolved_count=Count("complaints", filter=Q(complaints__status__in=["resolved", "closed"])),
            action_count=Count("px_actions"),
        )
        .filter(survey_count__gt=0)
        .order_by("-avg_score")[:7]
    )

    # Build department_stats list — all data now comes from annotations, zero extra queries
    department_stats = []
    for dept in department_rankings:
        resolution_rate = (
            round((dept.resolved_count / dept.complaint_count * 100), 1) if dept.complaint_count > 0 else 0
        )
        department_stats.append(
            {
                "name_en": dept.name_en if hasattr(dept, "name_en") else str(dept),
                "name_ar": dept.name_ar
                if hasattr(dept, "name_ar")
                else (dept.name_en if hasattr(dept, "name_en") else str(dept)),
                "complaints": dept.complaint_count,
                "actions": dept.action_count,
                "survey_avg": round(dept.avg_score, 2) if dept.avg_score else 0,
                "resolution_rate": resolution_rate,
            }
        )

    # ============ TIME-BASED CALCULATIONS ============
    # Average resolution time (complaints)
    resolved_with_time = complaints_queryset.filter(
        status__in=["resolved", "closed"], resolved_at__isnull=False, activated_at__isnull=False
    )
    if resolved_with_time.exists():
        avg_resolution_hours = resolved_with_time.annotate(
            resolution_time=F("resolved_at") - F("activated_at")
        ).aggregate(avg=Avg("resolution_time"))["avg"]
        if avg_resolution_hours:
            avg_resolution_hours = avg_resolution_hours.total_seconds() / 3600
        else:
            avg_resolution_hours = 0
    else:
        avg_resolution_hours = 0

    # Average action completion time
    closed_actions_with_time = actions_queryset.filter(
        status="closed", closed_at__isnull=False, created_at__isnull=False
    )
    if closed_actions_with_time.exists():
        avg_action_days = closed_actions_with_time.annotate(completion_time=F("closed_at") - F("created_at")).aggregate(
            avg=Avg("completion_time")
        )["avg"]
        if avg_action_days:
            avg_action_days = avg_action_days.days
        else:
            avg_action_days = 0
    else:
        avg_action_days = 0

    # ============ SLA COMPLIANCE ============
    total_with_sla = complaints_queryset.filter(due_at__isnull=False).count()
    resolved_within_sla = complaints_queryset.filter(
        status__in=["resolved", "closed"], resolved_at__lte=F("due_at")
    ).count()
    sla_compliance = (resolved_within_sla / total_with_sla * 100) if total_with_sla > 0 else 0

    # ============ NPS CALCULATION ============
    # NPS = % Promoters (9-10) - % Detractors (0-6)
    nps_surveys = surveys_queryset.filter(survey_template__survey_type="nps", total_score__isnull=False)
    if nps_surveys.exists():
        promoters = nps_surveys.filter(total_score__gte=9).count()
        detractors = nps_surveys.filter(total_score__lte=6).count()
        total_nps = nps_surveys.count()
        nps_score = ((promoters - detractors) / total_nps * 100) if total_nps > 0 else 0
    else:
        nps_score = 0

    kpis = {
        "total_complaints": total_complaints,
        "open_complaints": open_complaints,
        "in_progress_complaints": in_progress_complaints,
        "resolved_complaints": resolved_complaints,
        "closed_complaints": closed_complaints,
        "overdue_complaints": overdue_complaints,
        "internal_complaints": internal_complaints,
        "external_complaints": external_complaints,
        "critical_complaints": critical_complaints,
        "high_complaints": high_complaints,
        "medium_complaints": medium_complaints,
        "low_complaints": low_complaints,
        "avg_resolution_hours": round(avg_resolution_hours, 1),
        "sla_compliance": round(sla_compliance, 1),
        "reopened_complaints": reopened_complaints,
        "escalated_ovr_complaints": escalated_ovr_complaints,
        "total_actions": total_actions,
        "open_actions": open_actions,
        "in_progress_actions": in_progress_actions,
        "approved_actions": approved_actions,
        "closed_actions": closed_actions,
        "pending_actions": pending_actions,
        "overdue_actions": overdue_actions,
        "training_actions": training_actions,
        "process_actions": process_actions,
        "policy_actions": policy_actions,
        "facility_actions": facility_actions,
        "other_actions": other_actions,
        "avg_action_days": round(avg_action_days, 1),
        "total_surveys": total_surveys,
        "avg_survey_score": round(avg_survey_score, 2),
        "nps_score": round(nps_score, 1),
        "negative_surveys": negative_surveys,
        "completion_rate": round(completion_rate, 1),
        "total_feedback": total_feedback,
        "compliments": compliments,
        "suggestions": suggestions,
        "avg_rating": round(avg_rating, 2),
        "survey_trend_1": survey_trend_values[0] if len(survey_trend_values) > 0 else 0,
        "survey_trend_2": survey_trend_values[1] if len(survey_trend_values) > 1 else 0,
        "survey_trend_3": survey_trend_values[2] if len(survey_trend_values) > 2 else 0,
        "survey_trend_4": survey_trend_values[3] if len(survey_trend_values) > 3 else 0,
        "survey_trend_5": survey_trend_values[4] if len(survey_trend_values) > 4 else 0,
        "survey_trend_6": survey_trend_values[5] if len(survey_trend_values) > 5 else 0,
    }

    # ============ VISIT ANALYTICS ============

    visit_qs = HISPatientVisit.objects.all()
    if hospital:
        visit_qs = visit_qs.filter(hospital=hospital)

    total_visits = visit_qs.count()

    visit_type_data = {}

    def _fmt_dur(minutes):
        if minutes <= 0:
            return "-"
        if minutes < 60:
            return f"{int(minutes)}m"
        h = int(minutes // 60)
        m = int(minutes % 60)
        return f"{h}h" + (f" {m}m" if m else "")

    for pt in ["ED", "IP", "OP"]:
        pt_qs = visit_qs.filter(patient_type=pt)
        pt_count = pt_qs.count()
        pt_completed = pt_qs.filter(is_visit_complete=True).count()
        pt_with_events = pt_qs.filter(visit_events__isnull=False).distinct().count()

        durations = []
        for v in pt_qs.filter(admit_date__isnull=False, discharge_date__isnull=False).values_list(
            "admit_date", "discharge_date"
        )[:1000]:
            d = (v[1] - v[0]).total_seconds() / 60
            if 0 < d < 100000:
                durations.append(d)
        avg_duration_min = sum(durations) / len(durations) if durations else 0

        visit_type_data[pt] = {
            "count": pt_count,
            "completed": pt_completed,
            "completion_rate": round(pt_completed / pt_count * 100) if pt_count else 0,
            "avg_duration": _fmt_dur(avg_duration_min),
            "avg_duration_min": round(avg_duration_min),
            "with_events": pt_with_events,
        }

    visit_monthly = (
        visit_qs.filter(admit_date__isnull=False)
        .annotate(month=TruncMonth("admit_date"))
        .values("month", "patient_type")
        .annotate(count=Count("id"))
        .order_by("month", "patient_type")
    )

    visit_trend_months = []
    visit_trend_ed = []
    visit_trend_ip = []
    visit_trend_op = []

    months_map = {}
    for row in visit_monthly:
        if row["month"]:
            key = row["month"].strftime("%Y-%m")
            if key not in months_map:
                months_map[key] = {"ED": 0, "IP": 0, "OP": 0}
            months_map[key][row["patient_type"]] = row["count"]

    for key in sorted(months_map.keys())[-12:]:
        visit_trend_months.append(key)
        visit_trend_ed.append(months_map[key].get("ED", 0))
        visit_trend_ip.append(months_map[key].get("IP", 0))
        visit_trend_op.append(months_map[key].get("OP", 0))

    kpis["total_visits"] = total_visits
    kpis["visit_type_data"] = visit_type_data

    # Visit stage duration breakdown by patient type
    from apps.integrations.models import HISVisitEvent

    STAGE_CATEGORIES = {
        "Registration": ["consultation", "registration", "triage", "admission"],
        "Lab": ["lab", "sample"],
        "Radiology": ["rad", "radiology"],
        "Pharmacy": ["drug", "pharmacy"],
        "Doctor": ["doctor", "procedure", "episode"],
    }
    STAGE_ORDER = ["Registration", "Lab", "Radiology", "Pharmacy", "Doctor", "Other"]

    def _classify_event(event_type):
        et = (event_type or "").lower()
        for cat, keywords in STAGE_CATEGORIES.items():
            if any(kw in et for kw in keywords):
                return cat
        return "Other"

    visit_stage_data = {"stages": STAGE_ORDER}

    for pt in ["ED", "IP", "OP"]:
        visit_ids = list(
            visit_qs.filter(
                patient_type=pt,
                visit_events__isnull=False,
            )
            .distinct()
            .values_list("id", flat=True)[:500]
        )

        stage_totals = {s: [] for s in STAGE_ORDER}

        for visit_id in visit_ids:
            events = list(
                HISVisitEvent.objects.filter(
                    visit_id=visit_id, parsed_date__isnull=False
                ).order_by("parsed_date")
            )

            visit_stage_time = {s: 0.0 for s in STAGE_ORDER}

            for i in range(len(events) - 1):
                gap = (events[i + 1].parsed_date - events[i].parsed_date).total_seconds() / 60
                if gap > 0:
                    cat = _classify_event(events[i].event_type)
                    visit_stage_time[cat] += gap

            for s in STAGE_ORDER:
                if visit_stage_time[s] > 0:
                    stage_totals[s].append(visit_stage_time[s])

        visit_stage_data[pt] = [
            round(sum(stage_totals[s]) / len(stage_totals[s])) if stage_totals[s] else 0
            for s in STAGE_ORDER
        ]

    kpis["visit_stage_data"] = visit_stage_data



    # ============ AI-POWERED ANALYTICS ============
    hospital_id = str(hospital.id) if hospital else None

    # Trigger async Celery tasks to refresh cache in background
    from .tasks import (
        generate_executive_summary_task,
        generate_action_recommendations_task,
        precompute_visit_efficiency_task,
    )

    generate_executive_summary_task.delay(user_id=str(user.id), hospital_id=hospital_id, period="30d")
    generate_action_recommendations_task.delay(user_id=str(user.id), hospital_id=hospital_id)
    precompute_visit_efficiency_task.delay(hospital_id=hospital_id)

    # Read AI analytics from cache ONLY (populated hourly by Celery beat).
    # If cache miss, return lightweight placeholders so page loads instantly.
    exec_summary = cache.get(ExecutiveSummaryGenerator._cache_key(hospital_id, None, "30d"))
    if not exec_summary:
        exec_summary = {
            "summary_en": "Executive summary is being computed in the background...",
            "summary_ar": "جاري حساب الملخص التنفيذي في الخلفية...",
            "key_findings_en": [],
            "key_findings_ar": [],
            "recommendations_en": [],
            "recommendations_ar": [],
            "risk_level": "medium",
            "_data": {},
        }

    early_warnings = cache.get(EarlyWarningSystem._cache_key(hospital_id, 5))
    if early_warnings is None:
        early_warnings = []

    complaint_forecast = cache.get(ComplaintVolumeForecaster._cache_key(hospital_id, 30))
    if not complaint_forecast:
        complaint_forecast = ComplaintVolumeForecaster._insufficient_data_response(30)

    sla_breach_predictions = cache.get(SLABreachPredictor._cache_key(hospital_id, 10))
    if sla_breach_predictions is None:
        sla_breach_predictions = []

    action_recommendations = cache.get(ActionRecommendationEngine._cache_key(hospital_id, None, 5))
    if not action_recommendations:
        action_recommendations = ActionRecommendationEngine._no_data_response()

    from apps.analytics.services.ai_analytics import VisitEfficiencyAnalyzer

    visit_efficiency = cache.get(VisitEfficiencyAnalyzer._cache_key(hospital_id))
    if not visit_efficiency:
        visit_efficiency = {
            "bottlenecks_en": [],
            "bottlenecks_ar": [],
            "recommendations_en": [],
            "recommendations_ar": [],
            "efficiency_score": None,
            "priority_type": None,
            "summary_en": "Visit efficiency analysis is being computed in the background...",
            "summary_ar": "جاري حساب تحليل كفاءة الزيارة في الخلفية...",
            "_data": {},
        }

    context = {
        "kpis": kpis,
        "selected_hospital": hospital,
        "complaint_sources": serialize_queryset_values(complaint_sources),
        "top_domains": serialize_queryset_values(top_domains),
        "top_categories": serialize_queryset_values(top_categories),
        "severity_breakdown": serialize_queryset_values(severity_breakdown),
        "status_breakdown": serialize_queryset_values(status_breakdown),
        "complaints_by_dept": serialize_queryset_values(complaints_by_dept),
        "inquiry_status_counts": serialize_queryset_values(inquiry_status_counts),
        "inquiry_priority_counts": serialize_queryset_values(inquiry_priority_counts),
        "inquiries_by_dept": serialize_queryset_values(inquiries_by_dept),
        "inquiries_by_category": serialize_queryset_values(inquiries_by_category),
        "observation_status_counts": serialize_queryset_values(observation_status_counts),
        "observation_severity_counts": serialize_queryset_values(observation_severity_counts),
        "observations_by_dept": serialize_queryset_values(observations_by_dept),
        "observations_by_category": serialize_queryset_values(observations_by_category),
        "suggestion_status_counts": serialize_queryset_values(suggestion_status_counts),
        "suggestion_sentiment_counts": serialize_queryset_values(suggestion_sentiment_counts),
        "suggestions_by_dept": serialize_queryset_values(suggestions_by_dept),
        "suggestions_by_category": serialize_queryset_values(suggestions_by_category),
        "appreciation_status_counts": serialize_queryset_values(appreciation_status_counts),
        "appreciations_by_dept": serialize_queryset_values(appreciations_by_dept),
        "appreciations_by_category": serialize_queryset_values(appreciations_by_category),
        "appreciations_by_visibility": serialize_queryset_values(appreciations_by_visibility),
        "complaint_trend": serialize_queryset_values(complaint_trend),
        "complaints_by_quarter": serialize_queryset_values(complaints_by_quarter),
        "inquiries_by_quarter": serialize_queryset_values(inquiries_by_quarter),
        "suggestions_by_quarter": serialize_queryset_values(suggestions_by_quarter),
        "observations_by_quarter": serialize_queryset_values(observations_by_quarter),
        "appreciations_by_quarter": serialize_queryset_values(appreciations_by_quarter),
        "visits_by_quarter": serialize_queryset_values(visits_by_quarter),
        "chart_from_year": chart_from_year,
        "chart_to_year": chart_to_year,
        "action_sources": serialize_queryset_values(action_sources),
        "action_categories": serialize_queryset_values(action_categories),
        "survey_types": serialize_queryset_values(survey_types),
        "survey_score_trend": serialize_queryset_values(survey_score_trend_6m),
        "sentiment_breakdown": serialize_queryset_values(sentiment_breakdown),
        "feedback_categories": serialize_queryset_values(feedback_categories),
        "department_rankings": department_rankings,
        "department_stats": department_stats,
        "survey_trend_labels": json.dumps(survey_trend_labels),
        # AI-powered features
        "exec_summary": exec_summary,
        "early_warnings": early_warnings,
        "complaint_forecast": complaint_forecast,
        "sla_breach_predictions": sla_breach_predictions,
        "action_recommendations": action_recommendations,
        "visit_efficiency": visit_efficiency,
        "visit_type_data_json": json.dumps(visit_type_data),
        "visit_stage_data_json": json.dumps(visit_stage_data),
        "total_visits": total_visits,
        "visit_trend_months": json.dumps(visit_trend_months),
        "visit_trend_ed": json.dumps(visit_trend_ed),
        "visit_trend_ip": json.dumps(visit_trend_ip),
        "visit_trend_op": json.dumps(visit_trend_op),
    }

    # Cache the full dashboard context for 5 minutes so next load is instant
    cache.set(cache_key, context, 300)

    return render(request, "analytics/dashboard.html", context)


@block_source_user
@login_required
def refresh_ai_analytics(request):
    """
    API endpoint: Trigger async AI analytics refresh and return status.
    POST to trigger, GET to check if cache is fresh.
    """
    if request.method == "POST":
        from .tasks import (
            generate_executive_summary_task,
            generate_action_recommendations_task,
            precompute_dashboard_cache_task,
        )

        hospital_id = request.POST.get("hospital") or request.GET.get("hospital")
        user = request.user

        # Trigger async tasks
        generate_executive_summary_task.delay(
            user_id=str(user.id), hospital_id=hospital_id, period="30d", force_refresh=True
        )
        generate_action_recommendations_task.delay(user_id=str(user.id), hospital_id=hospital_id)

        # Also clear caches so next page load triggers fresh computation
        from apps.analytics.services.ai_analytics import (
            ExecutiveSummaryGenerator,
            ActionRecommendationEngine,
        )

        cache.delete(ExecutiveSummaryGenerator._cache_key(hospital_id, None, "30d"))
        cache.delete(ActionRecommendationEngine._cache_key(hospital_id, None, 5))

        return JsonResponse(
            {"status": "triggered", "message": "AI analytics refresh queued. Results will be available in ~30 seconds."}
        )

    # GET — check cache freshness
    hospital_id = request.GET.get("hospital") or (
        str(request.tenant_hospital.id) if hasattr(request, "tenant_hospital") and request.tenant_hospital else None
    )
    user = request.user

    from apps.analytics.services.ai_analytics import (
        ExecutiveSummaryGenerator,
        ActionRecommendationEngine,
    )

    summary_cached = cache.get(ExecutiveSummaryGenerator._cache_key(hospital_id, None, "30d"))
    recommendations_cached = cache.get(ActionRecommendationEngine._cache_key(hospital_id, None, 5))

    return JsonResponse(
        {
            "cached": {
                "executive_summary": summary_cached is not None,
                "action_recommendations": recommendations_cached is not None,
            },
            "risk_level": summary_cached.get("risk_level", "unknown") if summary_cached else None,
        }
    )


@block_source_user
@login_required
def refresh_dashboard_cache(request):
    """
    API endpoint: Trigger dashboard cache refresh on demand.
    POST to trigger refresh, returns immediately with task status.
    """
    if request.method != "POST":
        return JsonResponse({"error": "POST method required"}, status=405)

    from .tasks import precompute_dashboard_cache_task

    user = request.user

    # Trigger async cache refresh
    task = precompute_dashboard_cache_task.delay()

    # Clear user's dashboard cache so next load gets fresh data
    cache.delete(f"analytics_dashboard_{user.id}_all")
    if hasattr(request, "tenant_hospital") and request.tenant_hospital:
        cache.delete(f"analytics_dashboard_{user.id}_{request.tenant_hospital.id}")

    return JsonResponse(
        {
            "status": "triggered",
            "message": "Dashboard cache refresh queued. Please reload the page in a few seconds.",
            "task_id": str(task.id),
        }
    )


@block_source_user
@login_required
def kpi_list(request):
    """KPI definitions list view"""
    queryset = KPI.objects.all()

    # Apply filters
    category_filter = request.GET.get("category")
    if category_filter:
        queryset = queryset.filter(category=category_filter)

    is_active = request.GET.get("is_active")
    if is_active == "true":
        queryset = queryset.filter(is_active=True)
    elif is_active == "false":
        queryset = queryset.filter(is_active=False)

    # Ordering
    queryset = queryset.order_by("category", "name")

    # Pagination
    page_size = int(request.GET.get("page_size", 25))
    paginator = Paginator(queryset, page_size)
    page_number = request.GET.get("page", 1)
    page_obj = paginator.get_page(page_number)

    context = {
        "page_obj": page_obj,
        "kpis": page_obj.object_list,
        "filters": request.GET,
    }

    return render(request, "analytics/kpi_list.html", context)


@block_source_user
@login_required
def command_center(request):
    """
    PX Command Center - Unified Dashboard

    Comprehensive dashboard showing all PX360 metrics:
    - Complaints, Surveys, Actions KPIs
    - Interactive charts with ApexCharts
    - Department and Physician rankings
    - Export to Excel/PDF
    """
    user = request.user

    # Get filter parameters
    filters = {
        "date_range": request.GET.get("date_range", "30d"),
        "hospital": request.GET.get("hospital", ""),
        "department": request.GET.get("department", ""),
        "kpi_category": request.GET.get("kpi_category", ""),
        "custom_start": request.GET.get("custom_start", ""),
        "custom_end": request.GET.get("custom_end", ""),
    }

    # Get hospitals for filter
    hospitals = Hospital.objects.filter(status="active")
    if not user.is_px_admin() and user.hospital:
        hospitals = hospitals.filter(id=user.hospital.id)

    # Get departments for filter
    departments = Department.objects.filter(status="active")
    if filters.get("hospital"):
        departments = departments.filter(hospital_id=filters["hospital"])
    elif not user.is_px_admin() and user.hospital:
        departments = departments.filter(hospital=user.hospital)

    # Get initial KPIs
    custom_start = None
    custom_end = None
    if filters["custom_start"] and filters["custom_end"]:
        custom_start = datetime.strptime(filters["custom_start"], "%Y-%m-%d")
        custom_end = datetime.strptime(filters["custom_end"], "%Y-%m-%d")

    kpis = UnifiedAnalyticsService.get_all_kpis(
        user=user,
        date_range=filters["date_range"],
        hospital_id=filters["hospital"] if filters["hospital"] else None,
        department_id=filters["department"] if filters["department"] else None,
        custom_start=custom_start,
        custom_end=custom_end,
    )

    # Initial AI data for server-side render
    from .services.ai_analytics import (
        ExecutiveSummaryGenerator,
        EarlyWarningSystem,
        ComplaintVolumeForecaster,
        SLABreachPredictor,
        ActionRecommendationEngine,
    )

    hospital_id = filters["hospital"] if filters["hospital"] else None
    department_id = filters["department"] if filters["department"] else None

    if not hospital_id and (user.is_px_admin() or user.is_executive()):
        tenant = getattr(request, "tenant_hospital", None)
        if not tenant:
            tenant = getattr(user, "hospital", None)
        if tenant:
            hospital_id = str(tenant.id)

    # Trigger async refresh
    from .tasks import generate_executive_summary_task, generate_action_recommendations_task

    generate_executive_summary_task.delay(
        user_id=str(user.id), hospital_id=hospital_id, department_id=department_id, period=filters["date_range"]
    )
    generate_action_recommendations_task.delay(
        user_id=str(user.id), hospital_id=hospital_id, department_id=department_id
    )

    # Read AI analytics from cache ONLY (never block request with LLM calls)
    exec_summary = cache.get(ExecutiveSummaryGenerator._cache_key(hospital_id, department_id, filters["date_range"]))
    if not exec_summary:
        exec_summary = {
            "summary_en": "Executive summary is being computed in the background...",
            "summary_ar": "جاري حساب الملخص التنفيذي في الخلفية...",
            "key_findings_en": [],
            "key_findings_ar": [],
            "recommendations_en": [],
            "recommendations_ar": [],
            "risk_level": "medium",
            "_data": {},
        }

    early_warnings = cache.get(EarlyWarningSystem._cache_key(hospital_id, 5))
    if early_warnings is None:
        early_warnings = []

    complaint_forecast = cache.get(ComplaintVolumeForecaster._cache_key(hospital_id, 30))
    if not complaint_forecast:
        complaint_forecast = ComplaintVolumeForecaster._insufficient_data_response(30)

    sla_breach_predictions = cache.get(SLABreachPredictor._cache_key(hospital_id, 10))
    if sla_breach_predictions is None:
        sla_breach_predictions = []

    action_recommendations = cache.get(ActionRecommendationEngine._cache_key(hospital_id, department_id, 5))
    if not action_recommendations:
        action_recommendations = ActionRecommendationEngine._no_data_response()

    context = {
        "filters": filters,
        "departments": departments,
        "kpis": kpis,
        "exec_summary": exec_summary,
        "early_warnings": early_warnings,
        "complaint_forecast": complaint_forecast,
        "sla_breach_predictions": sla_breach_predictions,
        "action_recommendations": action_recommendations,
    }

    return render(request, "analytics/command_center.html", context)


@block_source_user
@login_required
def command_center_api(request):
    """
    API endpoint for Command Center data

    Returns JSON data for KPIs, charts, and tables based on filters.
    Used by JavaScript to dynamically update dashboard.
    """
    if request.method != "GET":
        return JsonResponse({"error": "Only GET requests allowed"}, status=405)

    user = request.user

    # Get filter parameters
    date_range = request.GET.get("date_range", "30d")
    hospital_id = request.GET.get("hospital")
    department_id = request.GET.get("department")
    kpi_category = request.GET.get("kpi_category")
    custom_start_str = request.GET.get("custom_start")
    custom_end_str = request.GET.get("custom_end")

    # Parse custom dates
    custom_start = None
    custom_end = None
    if custom_start_str and custom_end_str:
        try:
            custom_start = datetime.strptime(custom_start_str, "%Y-%m-%d")
            custom_end = datetime.strptime(custom_end_str, "%Y-%m-%d")
        except ValueError:
            pass

    # Handle hospital_id (can be integer or UUID string)
    hospital_id = hospital_id if hospital_id else None

    # Handle department_id (UUID string)
    department_id = department_id if department_id else None

    if not hospital_id and (user.is_px_admin() or user.is_executive()):
        tenant = getattr(request, "tenant_hospital", None)
        if not tenant:
            tenant = getattr(user, "hospital", None)
        if tenant:
            hospital_id = str(tenant.id)

    # Get KPIs
    kpis = UnifiedAnalyticsService.get_all_kpis(
        user=user,
        date_range=date_range,
        hospital_id=hospital_id,
        department_id=department_id,
        kpi_category=kpi_category,
        custom_start=custom_start,
        custom_end=custom_end,
    )

    # Ensure numeric KPIs are proper Python types for JSON serialization
    numeric_kpis = [
        "total_complaints",
        "open_complaints",
        "overdue_complaints",
        "high_severity_complaints",
        "resolved_complaints",
        "reopened_complaints",
        "escalated_ovr_complaints",
        "total_actions",
        "open_actions",
        "overdue_actions",
        "escalated_actions",
        "resolved_actions",
        "total_surveys",
        "negative_surveys",
        "avg_survey_score",
        "negative_social_mentions",
        "low_call_ratings",
        "total_sentiment_analyses",
    ]

    for key in numeric_kpis:
        if key in kpis:
            value = kpis[key]
            if value is None:
                kpis[key] = 0.0 if key == "avg_survey_score" else 0
            elif isinstance(value, (int, float)):
                # Already a number - ensure floats for specific fields
                if key == "avg_survey_score":
                    kpis[key] = float(value)
            else:
                # Try to convert to number
                try:
                    kpis[key] = float(value)
                except (ValueError, TypeError):
                    kpis[key] = 0.0 if key == "avg_survey_score" else 0

    # Handle nested trend data
    if "complaints_trend" in kpis and isinstance(kpis["complaints_trend"], dict):
        trend = kpis["complaints_trend"]
        trend["current"] = int(trend.get("current", 0))
        trend["previous"] = int(trend.get("previous", 0))
        trend["percentage_change"] = float(trend.get("percentage_change", 0))

    # Get chart data
    chart_types = [
        "complaints_trend",
        "complaints_by_category",
        "survey_satisfaction_trend",
        "survey_distribution",
        "department_performance",
        "physician_leaderboard",
    ]

    charts = {}
    for chart_type in chart_types:
        charts[chart_type] = UnifiedAnalyticsService.get_chart_data(
            user=user,
            chart_type=chart_type,
            date_range=date_range,
            hospital_id=hospital_id,
            department_id=department_id,
            custom_start=custom_start,
            custom_end=custom_end,
        )

    # Get table data
    tables = {}

    # Overdue complaints table
    complaints_qs = Complaint.objects.filter(is_overdue=True)
    if hospital_id:
        complaints_qs = complaints_qs.filter(hospital_id=hospital_id)
    if department_id:
        complaints_qs = complaints_qs.filter(department_id=department_id)

    # Apply role-based filtering
    if not user.is_px_admin() and user.hospital:
        complaints_qs = complaints_qs.filter(hospital=user.hospital)
    if user.is_department_manager() and user.department:
        complaints_qs = complaints_qs.filter(department=user.department)

    tables["overdue_complaints"] = list(
        complaints_qs.select_related("hospital", "department", "patient", "source")
        .order_by("due_at")[:20]
        .values(
            "id",
            "title",
            "severity",
            "due_at",
            "complaint_source_type",
            hospital_name=F("hospital__name"),
            department_name=F("department__name"),
            patient_full_name=Concat("patient__first_name", Value(" "), "patient__last_name"),
            source_name=F("source__name_en"),
            assigned_to_full_name=Concat("assigned_to__first_name", Value(" "), "assigned_to__last_name"),
        )
    )

    # Physician leaderboard table
    physician_data = charts.get("physician_leaderboard", {}).get("metadata", [])
    tables["physician_leaderboard"] = [
        {
            "physician_id": p["physician_id"],
            "name": p["name"],
            "specialization": p["specialization"],
            "department": p["department"],
            "rating": float(p["rating"]) if p["rating"] is not None else 0.0,
            "surveys": int(p["surveys"]) if p["surveys"] is not None else 0,
            "positive": int(p["positive"]) if p["positive"] is not None else 0,
            "neutral": int(p["neutral"]) if p["neutral"] is not None else 0,
            "negative": int(p["negative"]) if p["negative"] is not None else 0,
        }
        for p in physician_data
    ]

    # ============ AI-POWERED ANALYTICS ============
    from .services.ai_analytics import (
        ExecutiveSummaryGenerator,
        EarlyWarningSystem,
        ComplaintVolumeForecaster,
        SLABreachPredictor,
        ActionRecommendationEngine,
    )

    # Trigger async Celery tasks for background refresh
    from .tasks import (
        generate_executive_summary_task,
        generate_action_recommendations_task,
    )

    generate_executive_summary_task.delay(
        user_id=str(user.id),
        hospital_id=hospital_id,
        department_id=department_id,
        period=date_range.replace("d", "") if date_range.endswith("d") else "30d",
    )
    generate_action_recommendations_task.delay(
        user_id=str(user.id), hospital_id=hospital_id, department_id=department_id
    )

    # AI features — read from cache ONLY (never block request with LLM calls)
    exec_summary = cache.get(ExecutiveSummaryGenerator._cache_key(hospital_id, department_id, date_range))
    if not exec_summary:
        exec_summary = {
            "summary_en": "Executive summary is being computed in the background...",
            "summary_ar": "جاري حساب الملخص التنفيذي في الخلفية...",
            "key_findings_en": [],
            "key_findings_ar": [],
            "recommendations_en": [],
            "recommendations_ar": [],
            "risk_level": "medium",
            "_data": {},
        }

    early_warnings = cache.get(EarlyWarningSystem._cache_key(hospital_id, 5))
    if early_warnings is None:
        early_warnings = []

    complaint_forecast = cache.get(ComplaintVolumeForecaster._cache_key(hospital_id, 30))
    if not complaint_forecast:
        complaint_forecast = ComplaintVolumeForecaster._insufficient_data_response(30)

    sla_breach_predictions = cache.get(SLABreachPredictor._cache_key(hospital_id, 10))
    if sla_breach_predictions is None:
        sla_breach_predictions = []

    action_recommendations = cache.get(ActionRecommendationEngine._cache_key(hospital_id, department_id, 5))
    if not action_recommendations:
        action_recommendations = ActionRecommendationEngine._no_data_response()

    ai_data = {
        "executive_summary": exec_summary,
        "early_warnings": early_warnings,
        "complaint_forecast": complaint_forecast,
        "sla_breach_predictions": sla_breach_predictions,
        "action_recommendations": action_recommendations,
    }

    return JsonResponse({"kpis": kpis, "charts": charts, "tables": tables, "ai": ai_data})


@block_source_user
@login_required
def export_command_center(request, export_format):
    """
    Export Command Center data to Excel or PDF

    Args:
        export_format: 'excel' or 'pdf'

    Returns:
        HttpResponse with file download
    """
    if export_format not in ["excel", "pdf"]:
        return JsonResponse({"error": "Invalid export format"}, status=400)

    user = request.user

    # Get filter parameters
    date_range = request.GET.get("date_range", "30d")
    hospital_id = request.GET.get("hospital")
    department_id = request.GET.get("department")
    kpi_category = request.GET.get("kpi_category")
    custom_start_str = request.GET.get("custom_start")
    custom_end_str = request.GET.get("custom_end")

    # Parse custom dates
    custom_start = None
    custom_end = None
    if custom_start_str and custom_end_str:
        try:
            custom_start = datetime.strptime(custom_start_str, "%Y-%m-%d")
            custom_end = datetime.strptime(custom_end_str, "%Y-%m-%d")
        except ValueError:
            pass

    # Handle hospital_id and department_id (can be integer or UUID string)
    hospital_id = hospital_id if hospital_id else None
    department_id = department_id if department_id else None

    # Get all data
    kpis = UnifiedAnalyticsService.get_all_kpis(
        user=user,
        date_range=date_range,
        hospital_id=hospital_id,
        department_id=department_id,
        kpi_category=kpi_category,
        custom_start=custom_start,
        custom_end=custom_end,
    )

    chart_types = [
        "complaints_trend",
        "complaints_by_category",
        "survey_satisfaction_trend",
        "survey_distribution",
        "department_performance",
        "physician_leaderboard",
    ]

    charts = {}
    for chart_type in chart_types:
        charts[chart_type] = UnifiedAnalyticsService.get_chart_data(
            user=user,
            chart_type=chart_type,
            date_range=date_range,
            hospital_id=hospital_id,
            department_id=department_id,
            custom_start=custom_start,
            custom_end=custom_end,
        )

    # Get table data
    tables = {}

    # Overdue complaints
    complaints_qs = Complaint.objects.filter(is_overdue=True)
    if hospital_id:
        complaints_qs = complaints_qs.filter(hospital_id=hospital_id)
    if department_id:
        complaints_qs = complaints_qs.filter(department_id=department_id)

    if not user.is_px_admin() and user.hospital:
        complaints_qs = complaints_qs.filter(hospital=user.hospital)
    if user.is_department_manager() and user.department:
        complaints_qs = complaints_qs.filter(department=user.department)

    tables["overdue_complaints"] = {
        "headers": ["ID", "Title", "Patient", "Severity", "Hospital", "Department", "Due Date"],
        "rows": list(
            complaints_qs.select_related("hospital", "department", "patient")
            .order_by("due_at")[:100]
            .annotate(
                patient_full_name=Concat("patient__first_name", Value(" "), "patient__last_name"),
                hospital_name=F("hospital__name"),
                department_name=F("department__name"),
            )
            .values_list("id", "title", "patient_full_name", "severity", "hospital_name", "department_name", "due_at")
        ),
    }

    # Physician leaderboard
    physician_data = charts.get("physician_leaderboard", {}).get("metadata", [])
    tables["physician_leaderboard"] = {
        "headers": ["Name", "Specialization", "Department", "Rating", "Surveys", "Positive", "Neutral", "Negative"],
        "rows": [
            [
                p["name"],
                p["specialization"],
                p["department"],
                str(p["rating"]),
                str(p["surveys"]),
                str(p["positive"]),
                str(p["neutral"]),
                str(p["negative"]),
            ]
            for p in physician_data
        ],
    }

    # Prepare export data
    export_data = ExportService.prepare_dashboard_data(user=user, kpis=kpis, charts=charts, tables=tables)

    # Export based on format
    if export_format == "excel":
        return ExportService.export_to_excel(export_data)
    elif export_format == "pdf":
        return ExportService.export_to_pdf(export_data)

    return JsonResponse({"error": "Export failed"}, status=500)
