"""
Physicians Console UI views - Server-rendered templates for physician management
"""

from django.contrib.auth.decorators import login_required
from django.core.paginator import Paginator
from django.db.models import Avg, Count, Q, Sum
from django.http import JsonResponse
from django.shortcuts import get_object_or_404, render
from django.utils import timezone

from apps.organizations.models import Department, Hospital, Staff

from .models import PhysicianMonthlyRating


@login_required
def physician_list(request):
    """
    Physicians list view with filters.

    Features:
    - Server-side pagination
    - Filters (hospital, department, specialization, status)
    - Search by name or license number
    - Current month rating display
    """
    # Base queryset with optimizations - only show staff marked as physicians
    # Include both: staff with physician=True (from rating imports) OR staff_type='physician'
    queryset = (
        Staff.objects.filter(Q(physician=True) | Q(staff_type=Staff.StaffType.PHYSICIAN))
        .select_related("hospital", "department")
        .distinct()
    )

    # Apply RBAC filters
    user = request.user
    if user.is_px_admin():
        pass  # See all
    elif user.hospital:
        queryset = queryset.filter(hospital=user.hospital)
    else:
        queryset = queryset.none()

    # Apply filters
    hospital_filter = request.GET.get("hospital")
    if hospital_filter:
        queryset = queryset.filter(hospital_id=hospital_filter)

    department_filter = request.GET.get("department")
    if department_filter:
        queryset = queryset.filter(department_id=department_filter)

    specialization_filter = request.GET.get("specialization")
    if specialization_filter:
        queryset = queryset.filter(specialization__icontains=specialization_filter)

    status_filter = request.GET.get("status", "active")
    if status_filter:
        queryset = queryset.filter(status=status_filter)

    # Search
    search_query = request.GET.get("search")
    if search_query:
        queryset = queryset.filter(
            Q(first_name__icontains=search_query)
            | Q(last_name__icontains=search_query)
            | Q(license_number__icontains=search_query)
            | Q(specialization__icontains=search_query)
        )

    # Ordering
    order_by = request.GET.get("order_by", "last_name")
    queryset = queryset.order_by(order_by)

    # 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)

    # Get current month ratings for displayed physicians
    now = timezone.now()
    physician_ids = [p.id for p in page_obj.object_list]
    current_ratings = PhysicianMonthlyRating.objects.filter(
        staff_id__in=physician_ids, year=now.year, month=now.month
    ).select_related("staff")

    # Create rating lookup
    ratings_dict = {r.staff_id: r for r in current_ratings}

    # Attach ratings to physicians
    for physician in page_obj.object_list:
        physician.current_rating = ratings_dict.get(physician.id)

    departments = Department.objects.filter(status="active")
    if not user.is_px_admin() and user.hospital:
        departments = departments.filter(hospital=user.hospital)

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

    return render(request, "physicians/physician_list.html", context)


@login_required
def leaderboard(request):
    """
    Physician leaderboard - Top rated physicians.

    Features:
    - Ranked list of physicians by rating
    - Filters (hospital, department, year, month, physician, rating range)
    - Statistics summary
    """
    now = timezone.now()
    year = int(request.GET.get("year", now.year))
    month = int(request.GET.get("month", now.month))

    # Base queryset for ratings
    queryset = PhysicianMonthlyRating.objects.filter(year=year, month=month, staff__physician=True).select_related(
        "staff", "staff__hospital", "staff__department"
    )

    # Apply RBAC filters
    user = request.user
    if not user.is_px_admin() and user.hospital:
        queryset = queryset.filter(staff__hospital=user.hospital)

    # Apply filters
    hospital_filter = request.GET.get("hospital")
    if hospital_filter:
        queryset = queryset.filter(staff__hospital_id=hospital_filter)

    department_filter = request.GET.get("department")
    if department_filter:
        queryset = queryset.filter(staff__department_id=department_filter)

    physician_filter = request.GET.get("physician")
    if physician_filter:
        queryset = queryset.filter(staff_id=physician_filter)

    min_rating = request.GET.get("min_rating")
    if min_rating:
        try:
            queryset = queryset.filter(average_rating__gte=float(min_rating))
        except ValueError:
            pass

    max_rating = request.GET.get("max_rating")
    if max_rating:
        try:
            queryset = queryset.filter(average_rating__lte=float(max_rating))
        except ValueError:
            pass

    # Order by rating (highest first)
    queryset = queryset.order_by("-average_rating", "-total_surveys")

    limit = int(request.GET.get("limit", 50))
    leaderboard_qs = queryset[:limit]

    # Previous month ratings for trend calculation
    prev_month = month - 1
    prev_year = year
    if prev_month <= 0:
        prev_month = 12
        prev_year = year - 1
    prev_ratings_qs = PhysicianMonthlyRating.objects.filter(
        year=prev_year, month=prev_month, staff__physician=True
    )
    if not user.is_px_admin() and user.hospital:
        prev_ratings_qs = prev_ratings_qs.filter(staff__hospital=user.hospital)
    prev_ratings_dict = {r.staff_id: r.average_rating for r in prev_ratings_qs}

    # Last 6 months of ratings for sparkline charts
    sparkline_months = []
    for i in range(5, -1, -1):
        m = month - i
        y = year
        while m <= 0:
            m += 12
            y -= 1
        sparkline_months.append((y, m))

    staff_ids = [r.staff_id for r in leaderboard_qs]
    history_qs = PhysicianMonthlyRating.objects.filter(
        staff_id__in=staff_ids
    )
    if not user.is_px_admin() and user.hospital:
        history_qs = history_qs.filter(staff__hospital=user.hospital)

    history_map = {}
    for r in history_qs:
        history_map.setdefault(r.staff_id, {})[(r.year, r.month)] = float(r.average_rating)

    from types import SimpleNamespace

    leaderboard = []
    for rank, rating in enumerate(leaderboard_qs, 1):
        prev_rating = prev_ratings_dict.get(rating.staff_id)
        if prev_rating is not None:
            diff = float(rating.average_rating) - float(prev_rating)
            trend = "up" if diff > 0 else ("down" if diff < 0 else "stable")
        else:
            trend = "stable"

        staff_history = history_map.get(rating.staff_id, {})
        trend_data = [staff_history.get((y, m)) for y, m in sparkline_months]
        trend_labels = [f"{m}" for y, m in sparkline_months]

        total = rating.total_surveys or 1
        leaderboard.append(
            SimpleNamespace(
                physician=rating.staff,
                rating=SimpleNamespace(
                    average_rating=rating.average_rating,
                    total_surveys=rating.total_surveys,
                    positive_count=rating.positive_count,
                    neutral_count=rating.neutral_count,
                    negative_count=rating.negative_count,
                    rating_1_count=rating.rating_1_count,
                    rating_2_count=rating.rating_2_count,
                    rating_3_count=rating.rating_3_count,
                    rating_4_count=rating.rating_4_count,
                    rating_5_count=rating.rating_5_count,
                    rating_1_pct=round((rating.rating_1_count / total) * 100, 1),
                    rating_2_pct=round((rating.rating_2_count / total) * 100, 1),
                    rating_3_pct=round((rating.rating_3_count / total) * 100, 1),
                    rating_4_pct=round((rating.rating_4_count / total) * 100, 1),
                    rating_5_pct=round((rating.rating_5_count / total) * 100, 1),
                ),
                rank=rank,
                trend=trend,
                trend_data=trend_data,
                trend_labels=trend_labels,
            )
        )

    departments = Department.objects.filter(status="active")
    if not user.is_px_admin() and user.hospital:
        departments = departments.filter(hospital=user.hospital)

    rated_staff_ids = PhysicianMonthlyRating.objects.filter(
        year=year, month=month
    ).values_list("staff_id", flat=True)
    if not user.is_px_admin() and user.hospital:
        rated_staff_ids = PhysicianMonthlyRating.objects.filter(
            year=year, month=month, staff__hospital=user.hospital
        ).values_list("staff_id", flat=True)
    physicians = Staff.objects.filter(
        id__in=rated_staff_ids, status="active"
    ).order_by("first_name", "last_name")

    # Calculate statistics
    all_ratings = PhysicianMonthlyRating.objects.filter(year=year, month=month)
    if not user.is_px_admin() and user.hospital:
        all_ratings = all_ratings.filter(staff__hospital=user.hospital)

    stats = all_ratings.aggregate(
        total_physicians=Count("id"), average_rating=Avg("average_rating"), total_surveys=Sum("total_surveys")
    )

    # Distribution
    excellent = all_ratings.filter(average_rating__gte=4.5).count()
    good = all_ratings.filter(average_rating__gte=3.5, average_rating__lt=4.5).count()
    average = all_ratings.filter(average_rating__gte=2.5, average_rating__lt=3.5).count()
    poor = all_ratings.filter(average_rating__lt=2.5).count()

    context = {
        "leaderboard": leaderboard,
        "year": year,
        "month": month,
        "departments": departments,
        "physicians": physicians,
        "filters": request.GET,
        "stats": stats,
        "distribution": {"excellent": excellent, "good": good, "average": average, "poor": poor},
    }

    return render(request, "physicians/leaderboard.html", context)


@login_required
def physician_ratings_dashboard(request):
    """
    Physician ratings dashboard - Main analytics view with charts.

    Features:
    - Statistics cards
    - Rating trend over 6 months
    - Rating distribution
    - Department comparison
    - Sentiment analysis
    - Top physicians table
    """
    now = timezone.now()
    year = int(request.GET.get("year", now.year))
    month = int(request.GET.get("month", now.month))
    hospital_filter = request.GET.get("hospital")
    department_filter = request.GET.get("department")

    # Get available years (2024 to current year)
    current_year = now.year
    years = list(range(2024, current_year + 1))
    years.reverse()  # Most recent first

    # Get departments for filter dropdown
    from apps.organizations.models import Department

    if request.user.is_px_admin():
        departments = Department.objects.filter(status="active").order_by("name")
    elif request.user.hospital:
        departments = Department.objects.filter(hospital=request.user.hospital, status="active").order_by("name")
    else:
        departments = Department.objects.none()

    context = {
        "years": years,
        "departments": departments,
        "filters": request.GET,
    }

    return render(request, "physicians/physician_ratings_dashboard.html", context)


@login_required
def physician_ratings_dashboard_api(request):
    """
    API endpoint for physician ratings dashboard data.

    Returns JSON data for all dashboard charts and statistics.
    """
    try:
        now = timezone.now()
        year = int(request.GET.get("year", now.year))
        month = int(request.GET.get("month", now.month))
        hospital_filter = request.GET.get("hospital")
        department_filter = request.GET.get("department")

        # Base queryset - only include staff marked as physicians
        queryset = PhysicianMonthlyRating.objects.filter(staff__physician=True).select_related(
            "staff", "staff__hospital", "staff__department"
        )

        # Apply RBAC filters
        user = request.user
        if not user.is_px_admin() and user.hospital:
            queryset = queryset.filter(staff__hospital=user.hospital)

        # Apply filters
        if hospital_filter:
            queryset = queryset.filter(staff__hospital_id=hospital_filter)
        if department_filter:
            queryset = queryset.filter(staff__department_id=department_filter)

        # Filter for selected period
        current_period = queryset.filter(year=year, month=month)

        # 1. Statistics
        stats = current_period.aggregate(
            total_physicians=Count("id", distinct=True),
            average_rating=Avg("average_rating"),
            total_surveys=Sum("total_surveys"),
        )

        excellent_count = current_period.filter(average_rating__gte=4.5).count()

        # 2. Rating Trend (last 6 months)
        trend_data = []
        for i in range(5, -1, -1):
            m = month - i
            y = year
            if m <= 0:
                m += 12
                y -= 1

            period_data = queryset.filter(year=y, month=m).aggregate(
                avg=Avg("average_rating"), surveys=Sum("total_surveys")
            )

            trend_data.append(
                {
                    "period": f"{y}-{m:02d}",
                    "average_rating": round(float(period_data["avg"] or 0), 1),
                    "total_surveys": period_data["surveys"] or 0,
                }
            )

        # 3. Rating Distribution
        excellent = current_period.filter(average_rating__gte=4.5).count()
        good = current_period.filter(average_rating__gte=3.5, average_rating__lt=4.5).count()
        average = current_period.filter(average_rating__gte=2.5, average_rating__lt=3.5).count()
        poor = current_period.filter(average_rating__lt=2.5).count()

        distribution = {"excellent": excellent, "good": good, "average": average, "poor": poor}

        # 4. Department Comparison (grouped by rating 1-5)
        all_dept_data = (
            current_period.values("staff__department__name")
            .annotate(
                average_rating=Avg("average_rating"),
                total_surveys=Sum("total_surveys"),
                physician_count=Count("id", distinct=True),
            )
            .filter(
                staff__department__isnull=False,
                total_surveys__gte=3,
                physician_count__gte=2,
            )
            .order_by("-average_rating")
        )

        departments_list = [
            {
                "name": item["staff__department__name"] or "Unknown",
                "average_rating": round(float(item["average_rating"] or 0), 1),
                "total_surveys": item["total_surveys"] or 0,
                "physician_count": item["physician_count"] or 0,
            }
            for item in all_dept_data
        ]

        # Group departments by integer rating (1-5)
        rating_groups = {5: [], 4: [], 3: [], 2: [], 1: []}
        for dept in departments_list:
            rating_int = min(5, max(1, round(dept["average_rating"])))
            rating_groups[rating_int].append(dept)

        # 5. Sentiment Analysis
        sentiment = current_period.aggregate(
            positive=Sum("positive_count"), neutral=Sum("neutral_count"), negative=Sum("negative_count")
        )

        total_sentiment = (sentiment["positive"] or 0) + (sentiment["neutral"] or 0) + (sentiment["negative"] or 0)

        if total_sentiment > 0:
            sentiment_pct = {
                "positive": ((sentiment["positive"] or 0) / total_sentiment) * 100,
                "neutral": ((sentiment["neutral"] or 0) / total_sentiment) * 100,
                "negative": ((sentiment["negative"] or 0) / total_sentiment) * 100,
            }
        else:
            sentiment_pct = {"positive": 0, "neutral": 0, "negative": 0}

        # 6. Top 10 Physicians
        top_physicians = current_period.select_related("staff", "staff__hospital", "staff__department").order_by(
            "-average_rating", "-total_surveys"
        )[:10]

        physicians_list = [
            {
                "id": rating.staff.id,
                "name": rating.staff.get_full_name(),
                "license_number": rating.staff.license_number,
                "specialization": rating.staff.specialization or "-",
                "department": rating.staff.department.name if rating.staff.department else "-",
                "hospital": rating.staff.hospital.name if rating.staff.hospital else "-",
                "rating": round(float(rating.average_rating), 1),
                "surveys": rating.total_surveys,
            }
            for rating in top_physicians
        ]

        return JsonResponse(
            {
                "statistics": {
                    "total_physicians": stats["total_physicians"] or 0,
                    "average_rating": round(float(stats["average_rating"] or 0), 1),
                    "total_surveys": stats["total_surveys"] or 0,
                    "excellent_count": excellent_count,
                },
                "trend": trend_data,
                "distribution": distribution,
                "rating_groups": rating_groups,
                "sentiment": sentiment_pct,
                "top_physicians": physicians_list,
            }
        )
    except Exception as e:
        import traceback

        return JsonResponse({"error": str(e), "traceback": traceback.format_exc()}, status=500)


@login_required
def ratings_list(request):
    """
    Monthly ratings list view with filters.

    Features:
    - All monthly ratings
    - Filters (physician, hospital, department, year, month)
    - Search by physician name
    - Pagination
    """
    # Base queryset - only include staff marked as physicians
    queryset = PhysicianMonthlyRating.objects.filter(staff__physician=True).select_related(
        "staff", "staff__hospital", "staff__department"
    )

    # Apply RBAC filters
    user = request.user
    if not user.is_px_admin() and user.hospital:
        queryset = queryset.filter(staff__hospital=user.hospital)

    # Apply filters
    physician_filter = request.GET.get("physician")
    if physician_filter:
        queryset = queryset.filter(staff_id=physician_filter)

    hospital_filter = request.GET.get("hospital")
    if hospital_filter:
        queryset = queryset.filter(staff__hospital_id=hospital_filter)

    department_filter = request.GET.get("department")
    if department_filter:
        queryset = queryset.filter(staff__department_id=department_filter)

    year_filter = request.GET.get("year")
    if year_filter:
        queryset = queryset.filter(year=int(year_filter))

    month_filter = request.GET.get("month")
    if month_filter:
        queryset = queryset.filter(month=int(month_filter))

    # Search
    search_query = request.GET.get("search")
    if search_query:
        queryset = queryset.filter(
            Q(staff__first_name__icontains=search_query)
            | Q(staff__last_name__icontains=search_query)
            | Q(staff__license_number__icontains=search_query)
        )

    # Ordering
    order_by = request.GET.get("order_by", "-year,-month,-average_rating")
    queryset = queryset.order_by(*order_by.split(","))

    # 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)

    departments = Department.objects.filter(status="active")
    if not user.is_px_admin() and user.hospital:
        departments = departments.filter(hospital=user.hospital)

    # Get available years
    years = PhysicianMonthlyRating.objects.values_list("year", flat=True).distinct().order_by("-year")

    context = {
        "page_obj": page_obj,
        "ratings": page_obj.object_list,
        "departments": departments,
        "years": years,
        "filters": request.GET,
    }

    return render(request, "physicians/ratings_list.html", context)


@login_required
def specialization_overview(request):
    """
    Specialization overview - aggregated ratings by specialization.

    Features:
    - Average rating per specialization
    - Total physicians per specialization
    - Total surveys per specialization
    - Drill-down to physicians in specialization
    """
    # Get parameters
    now = timezone.now()
    year = int(request.GET.get("year", now.year))
    month = int(request.GET.get("month", now.month))
    hospital_filter = request.GET.get("hospital")

    # Base queryset - only include staff marked as physicians
    queryset = PhysicianMonthlyRating.objects.filter(year=year, month=month, staff__physician=True).select_related(
        "staff", "staff__hospital", "staff__department"
    )

    # Apply RBAC filters
    user = request.user
    if not user.is_px_admin() and user.hospital:
        queryset = queryset.filter(staff__hospital=user.hospital)

    # Apply filters
    if hospital_filter:
        queryset = queryset.filter(staff__hospital_id=hospital_filter)

    # Aggregate by specialization
    specialization_data = {}
    for rating in queryset:
        spec = rating.staff.specialization
        if spec not in specialization_data:
            specialization_data[spec] = {
                "specialization": spec,
                "physicians": [],
                "total_physicians": 0,
                "total_surveys": 0,
                "total_positive": 0,
                "total_neutral": 0,
                "total_negative": 0,
                "ratings_sum": 0,
            }

        specialization_data[spec]["physicians"].append(rating)
        specialization_data[spec]["total_physicians"] += 1
        specialization_data[spec]["total_surveys"] += rating.total_surveys
        specialization_data[spec]["total_positive"] += rating.positive_count
        specialization_data[spec]["total_neutral"] += rating.neutral_count
        specialization_data[spec]["total_negative"] += rating.negative_count
        specialization_data[spec]["ratings_sum"] += float(rating.average_rating)

    # Calculate averages
    specializations = []
    for spec, data in specialization_data.items():
        avg_rating = data["ratings_sum"] / data["total_physicians"] if data["total_physicians"] > 0 else 0
        specializations.append(
            {
                "specialization": spec,
                "total_physicians": data["total_physicians"],
                "average_rating": round(avg_rating, 2),
                "total_surveys": data["total_surveys"],
                "positive_count": data["total_positive"],
                "neutral_count": data["total_neutral"],
                "negative_count": data["total_negative"],
                "physicians": sorted(data["physicians"], key=lambda x: x.average_rating, reverse=True),
            }
        )

    # Sort by average rating
    specializations.sort(key=lambda x: x["average_rating"], reverse=True)

    context = {
        "specializations": specializations,
        "year": year,
        "month": month,
        "filters": request.GET,
    }

    return render(request, "physicians/specialization_overview.html", context)


@login_required
def physician_detail(request, pk):
    """
    Physician detail view - shows detailed information and ratings for a specific physician.

    Features:
    - Physician profile information
    - Monthly ratings history
    - Rating trends over time
    - Statistics and achievements
    """
    from django.shortcuts import get_object_or_404

    # Get the physician (staff member marked as physician)
    physician = get_object_or_404(
        Staff.objects.filter(Q(physician=True) | Q(staff_type=Staff.StaffType.PHYSICIAN)).select_related(
            "hospital", "department"
        ),
        pk=pk,
    )

    # Check permissions
    user = request.user
    if not user.is_px_admin() and user.hospital and physician.hospital != user.hospital:
        from django.core.exceptions import PermissionDenied

        raise PermissionDenied("You don't have permission to view this physician.")

    # Get all monthly ratings for this physician
    ratings = PhysicianMonthlyRating.objects.filter(staff=physician).order_by("-year", "-month")

    # Get current month rating
    now = timezone.now()
    current_rating = ratings.filter(year=now.year, month=now.month).first()

    # Calculate statistics
    stats = ratings.aggregate(
        total_months=Count("id"),
        average_rating=Avg("average_rating"),
        total_surveys=Sum("total_surveys"),
        total_positive=Sum("positive_count"),
        total_neutral=Sum("neutral_count"),
        total_negative=Sum("negative_count"),
    )

    # Get trend data (last 12 months)
    trend_data = []
    for i in range(11, -1, -1):
        m = now.month - i
        y = now.year
        if m <= 0:
            m += 12
            y -= 1

        rating = ratings.filter(year=y, month=m).first()
        trend_data.append(
            {
                "period": f"{y}-{m:02d}",
                "rating": float(rating.average_rating) if rating else None,
                "surveys": rating.total_surveys if rating else 0,
            }
        )

    context = {
        "physician": physician,
        "current_rating": current_rating,
        "ratings": ratings[:12],  # Last 12 months
        "stats": stats,
        "trend_data": trend_data,
    }

    return render(request, "physicians/physician_detail.html", context)


@login_required
def department_overview(request):
    """
    Department overview - aggregated ratings by department.

    Features:
    - Average rating per department
    - Total physicians per department
    - Total surveys per department
    - Drill-down to physicians in department
    """
    # Get parameters
    now = timezone.now()
    year = int(request.GET.get("year", now.year))
    month = int(request.GET.get("month", now.month))
    hospital_filter = request.GET.get("hospital")

    # Base queryset - only include staff marked as physicians
    queryset = PhysicianMonthlyRating.objects.filter(year=year, month=month, staff__physician=True).select_related(
        "staff", "staff__hospital", "staff__department"
    )

    # Apply RBAC filters
    user = request.user
    if not user.is_px_admin() and user.hospital:
        queryset = queryset.filter(staff__hospital=user.hospital)

    # Apply filters
    if hospital_filter:
        queryset = queryset.filter(staff__hospital_id=hospital_filter)

    # Aggregate by department
    department_data = {}
    for rating in queryset:
        dept = rating.staff.department
        if not dept:
            continue

        dept_key = str(dept.id)
        if dept_key not in department_data:
            department_data[dept_key] = {
                "department": dept,
                "physicians": [],
                "total_physicians": 0,
                "total_surveys": 0,
                "total_positive": 0,
                "total_neutral": 0,
                "total_negative": 0,
                "ratings_sum": 0,
            }

        department_data[dept_key]["physicians"].append(rating)
        department_data[dept_key]["total_physicians"] += 1
        department_data[dept_key]["total_surveys"] += rating.total_surveys
        department_data[dept_key]["total_positive"] += rating.positive_count
        department_data[dept_key]["total_neutral"] += rating.neutral_count
        department_data[dept_key]["total_negative"] += rating.negative_count
        department_data[dept_key]["ratings_sum"] += float(rating.average_rating)

    # Calculate averages
    departments = []
    for dept_key, data in department_data.items():
        avg_rating = data["ratings_sum"] / data["total_physicians"] if data["total_physicians"] > 0 else 0
        departments.append(
            {
                "department": data["department"],
                "total_physicians": data["total_physicians"],
                "average_rating": round(avg_rating, 2),
                "total_surveys": data["total_surveys"],
                "positive_count": data["total_positive"],
                "neutral_count": data["total_neutral"],
                "negative_count": data["total_negative"],
                "physicians": sorted(data["physicians"], key=lambda x: x.average_rating, reverse=True),
            }
        )

    # Sort by average rating
    departments.sort(key=lambda x: x["average_rating"], reverse=True)

    context = {
        "departments": departments,
        "year": year,
        "month": month,
        "filters": request.GET,
    }

    return render(request, "physicians/department_overview.html", context)


@login_required
def ratings_export(request):
    """Export physician monthly ratings (Step 1 - Calculations format)."""
    from django.core.exceptions import PermissionDenied

    if not (request.user.is_px_admin() or request.user.is_hospital_admin()):
        raise PermissionDenied

    from .models import PhysicianMonthlyRating
    from openpyxl import Workbook
    from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
    from django.http import HttpResponse

    year = request.GET.get("year")
    month = request.GET.get("month")
    quarter = request.GET.get("quarter")
    patient_type = request.GET.get("patient_type", "OP")

    qs = PhysicianMonthlyRating.objects.select_related("staff", "staff__department", "staff__hospital")

    if year:
        qs = qs.filter(year=int(year))
    if month:
        qs = qs.filter(month=int(month))
    elif quarter:
        quarter_months = {1: (1, 3), 2: (4, 6), 3: (7, 9), 4: (10, 12)}
        start_m, end_m = quarter_months[int(quarter)]
        qs = qs.filter(month__gte=start_m, month__lte=end_m)

    if request.user.is_hospital_admin() and request.user.hospital:
        qs = qs.filter(staff__hospital=request.user.hospital)
    elif request.user.is_px_admin() and request.tenant_hospital:
        qs = qs.filter(staff__hospital=request.tenant_hospital)

    qs = qs.order_by("-average_rating")

    wb = Workbook()
    ws = wb.active
    label = f"Q{quarter}-{year}" if quarter else f"{year}-{month}" if month else f"{year}" if year else "all"
    ws.title = f"Ratings {label}"

    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    thin_border = Border(
        left=Side(style="thin"),
        right=Side(style="thin"),
        top=Side(style="thin"),
        bottom=Side(style="thin"),
    )

    headers = [
        "Clinic/Department",
        "Doctor ID",
        "Doctor Name",
        "Total Ratings",
        "Average Rating",
        "1-Star",
        "2-Star",
        "3-Star",
        "4-Star",
        "5-Star",
        "1-Star %",
        "2-Star %",
        "3-Star %",
        "4-Star %",
        "5-Star %",
    ]
    for col, h in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col, value=h)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = Alignment(horizontal="center")

    row = 2
    for rating in qs:
        total = rating.total_surveys or 1
        dept = rating.staff.department.name if rating.staff.department else ""
        doc_id = rating.staff.license_number or ""
        doc_name = rating.staff.get_full_name()

        star_counts = [
            rating.negative_count or 0,
            0,
            rating.neutral_count or 0,
            rating.positive_count or 0,
            0,
        ]
        star_pcts = [round(c / total * 100, 1) for c in star_counts]

        vals = [dept, doc_id, doc_name, total, float(rating.average_rating)] + star_counts + star_pcts
        for col, val in enumerate(vals, 1):
            cell = ws.cell(row=row, column=col, value=val)
            cell.border = thin_border
        row += 1

    ws.column_dimensions["A"].width = 30
    ws.column_dimensions["B"].width = 15
    ws.column_dimensions["C"].width = 30
    for c in "DEFGHIJKLMNO":
        ws.column_dimensions[c].width = 12

    response = HttpResponse(content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    response["Content-Disposition"] = f'attachment; filename="physician_ratings_{patient_type}_{label}.xlsx"'
    wb.save(response)
    return response
