import calendar
import json
import logging
from collections import defaultdict

from django.db.models import Count, Q, Avg, Sum
from django.utils import timezone

from apps.complaints.models import Complaint, ComplaintInvolvedStaff
from apps.core.ai_service import AIService
from apps.dashboard.services.complaint_quarterly_service import (
    ComplaintQuarterlyService,
    TIMELINE_BUCKETS,
)
from apps.organizations.models import Hospital

from .models import (
    Presentation,
    Slide,
    PresentationTheme,
    SlideLayout,
    PresentationStatus,
)

logger = logging.getLogger(__name__)

QUARTER_MONTHS = {
    1: [1, 2, 3],
    2: [4, 5, 6],
    3: [7, 8, 9],
    4: [10, 11, 12],
}


class PresentationGeneratorService:
    def __init__(self, hospital_id, year, quarter=None, created_by=None):
        self.hospital_id = hospital_id
        self.year = year
        self.quarter = quarter
        self.created_by = created_by
        self.quarterly_service = ComplaintQuarterlyService(
            hospital_id=hospital_id, year=year
        )
        self._data = {}
        self._ai_insights = {}

    def generate(self):
        self._data = self._fetch_data()
        self._ai_insights = self._generate_ai_insights()

        period_label = self._period_label()
        hospital = Hospital.objects.get(pk=self.hospital_id)

        presentation = Presentation.objects.create(
            title=f"Patient Experience Report — {period_label}",
            subtitle=f"{hospital.name}",
            description=f"Auto-generated complaints presentation for {period_label}",
            theme=PresentationTheme.HEALTHCARE_MODERN,
            status=PresentationStatus.PUBLISHED,
            presentation_type="quarterly" if self.quarter else "yearly",
            created_by=self.created_by,
            hospital=hospital,
            presentation_date=timezone.now().date(),
            is_shared=True,
        )

        order = 0

        order = self._create_cover_slide(presentation, order, hospital)
        order = self._create_section_divider(
            presentation, order, "01", "OVERVIEW", "Key Performance Indicators"
        )
        order = self._create_kpi_dashboard_slide(presentation, order)
        order = self._create_monthly_chart_slide(presentation, order)
        order = self._create_section_divider(
            presentation, order, "02", "BREAKDOWN", "Complaint Breakdown & Sources"
        )
        order = self._create_source_chart_slide(presentation, order)
        order = self._create_dept_chart_slide(presentation, order)
        order = self._create_section_divider(
            presentation, order, "03", "ESCALATION", "Escalated Complaints"
        )
        order = self._create_escalation_kpi_slide(presentation, order)
        order = self._create_escalation_table_slide(presentation, order)
        order = self._create_section_divider(
            presentation, order, "04", "RESPONSE", "Response Rate Performance"
        )
        order = self._create_response_chart_slide(presentation, order)
        order = self._create_response_days_slide(presentation, order)
        order = self._create_section_divider(
            presentation, order, "05", "INSIGHTS", "Classification & Insights"
        )
        order = self._create_classification_table_slide(presentation, order)
        order = self._create_key_finding_slide(presentation, order)
        order = self._create_analysis_slide(presentation, order)
        order = self._create_employees_slide(presentation, order)
        order = self._create_closing_slide(presentation, order, hospital)

        return presentation

    def _period_label(self):
        if self.quarter:
            return f"Q{self.quarter} {self.year}"
        return str(self.year)

    def _active_months(self):
        if self.quarter:
            return QUARTER_MONTHS[self.quarter]
        return list(range(1, 13))

    def _month_labels(self):
        months = self._active_months()
        return [calendar.month_abbr[m] for m in months]

    def _fetch_data(self):
        kpi = self.quarterly_service.get_kpi_data()
        source = self.quarterly_service.get_source_breakdown()
        location = self.quarterly_service.get_location_breakdown()
        dept = self.quarterly_service.get_dept_type_breakdown()
        escalated = self.quarterly_service.get_escalated_breakdown()
        response = self.quarterly_service.get_response_rates()
        chart = self.quarterly_service.get_chart_data()

        months = self._active_months()

        filtered_kpi_months = {m: kpi["months"][m] for m in months}
        filtered_kpi_totals = {
            "total": sum(r["total"] for r in filtered_kpi_months.values()),
            "closed": sum(r["closed"] for r in filtered_kpi_months.values()),
            "resolved_72h": sum(r["resolved_72h"] for r in filtered_kpi_months.values()),
            "activated": sum(r["activated"] for r in filtered_kpi_months.values()),
        }
        t = filtered_kpi_totals
        filtered_kpi_totals["resolution_rate"] = (
            round(t["closed"] / t["total"], 4) if t["total"] > 0 else 0
        )
        filtered_kpi_totals["resolved_72h_rate"] = (
            round(t["resolved_72h"] / t["total"], 4) if t["total"] > 0 else 0
        )

        filtered_source = {m: source[m] for m in months}
        source_totals = {
            "internal": sum(s["internal"] for s in filtered_source.values()),
            "external": sum(s["external"] for s in filtered_source.values()),
            "moh": sum(s["moh"] for s in filtered_source.values()),
            "chi": sum(s["chi"] for s in filtered_source.values()),
            "insurance": sum(s["insurance"] for s in filtered_source.values()),
            "total": sum(s["total"] for s in filtered_source.values()),
        }

        filtered_location = {m: location[m] for m in months}
        location_totals = {
            "ip": sum(l["ip"] for l in filtered_location.values()),
            "op": sum(l["op"] for l in filtered_location.values()),
            "er": sum(l["er"] for l in filtered_location.values()),
        }

        filtered_dept = {m: dept[m] for m in months}
        dept_totals = {
            "medical": sum(d["medical"] for d in filtered_dept.values()),
            "admin": sum(d["admin"] for d in filtered_dept.values()),
            "nursing": sum(d["nursing"] for d in filtered_dept.values()),
            "support": sum(d["support"] for d in filtered_dept.values()),
        }

        monthly_totals = [filtered_kpi_months[m]["total"] for m in months]
        monthly_resolution = [
            filtered_kpi_months[m]["resolution_rate"] for m in months
        ]

        satisfaction_qs = Complaint.objects.filter(
            hospital_id=self.hospital_id,
            created_at__year=self.year,
            created_at__month__in=months,
            satisfaction__isnull=False,
        ).exclude(satisfaction="")
        sat_total = satisfaction_qs.count()
        sat_satisfied = satisfaction_qs.filter(satisfaction="satisfied").count()
        satisfaction_rate = (
            round(sat_satisfied / sat_total, 4) if sat_total > 0 else 0
        )

        return {
            "kpi_months": filtered_kpi_months,
            "kpi_totals": filtered_kpi_totals,
            "source": filtered_source,
            "source_totals": source_totals,
            "location_totals": location_totals,
            "dept_totals": dept_totals,
            "escalated": escalated,
            "response": response,
            "monthly_totals": monthly_totals,
            "monthly_resolution": monthly_resolution,
            "satisfaction_rate": satisfaction_rate,
            "satisfaction_total": sat_total,
            "months": months,
        }

    def _generate_ai_insights(self):
        d = self._data
        kpi = d["kpi_totals"]
        st = d["source_totals"]
        esc = d["escalated"]
        dt = d["dept_totals"]

        data_summary = (
            f"Period: {self._period_label()}\n"
            f"Hospital ID: {self.hospital_id}\n"
            f"Total Complaints: {kpi['total']}\n"
            f"Resolution Rate: {kpi['resolution_rate']*100:.1f}% (Target: 95%)\n"
            f"72h Resolution Rate: {kpi['resolved_72h_rate']*100:.1f}% (Target: 95%)\n"
            f"Patient Satisfaction Rate: {d['satisfaction_rate']*100:.1f}% (Target: 80%)\n"
            f"Source Breakdown: Internal {st['internal']}, MOH {st['moh']}, CCHI {st['chi']}, Insurance {st['insurance']}\n"
            f"Department Types: Medical {dt['medical']}, Admin {dt['admin']}, Nursing {dt['nursing']}, Support {dt['support']}\n"
            f"Escalated: {esc['total_escalated']} out of {esc['total_complaints']} ({esc['escalation_rate']*100:.1f}%)\n"
            f"Monthly Trend: {d['monthly_totals']}\n"
        )

        prompt = (
            "You are a healthcare quality analyst for a Saudi Arabian hospital. "
            "Based on the following complaint data, generate a JSON response with exactly these keys:\n"
            '- "key_finding": A 2-3 sentence quote summarizing the most critical finding.\n'
            '- "analysis": A 4-5 sentence analytical narrative about trends and patterns.\n'
            '- "recommendations": An array of 4-5 actionable recommendation strings.\n'
            '- "next_steps": An array of 3 concrete next-step strings with timelines.\n'
            '- "kpi_summary": A 1-2 sentence summary of the KPI dashboard performance.\n'
            '- "monthly_insight": A 1-2 sentence insight about the monthly complaint trend.\n'
            '- "source_insight": A 1-2 sentence insight about the complaint source distribution.\n'
            '- "dept_insight": A 1-2 sentence insight about the department breakdown.\n'
            '- "escalation_insight": A 1-2 sentence insight about escalated complaints.\n'
            '- "response_insight": A 1-2 sentence insight about response time performance.\n'
            '- "resolution_insight": A 1-2 sentence insight about average resolution time by department.\n'
            '- "classification_insight": A 1-2 sentence insight about the complaint classification breakdown.\n\n'
            f"Data:\n{data_summary}"
        )

        try:
            response = AIService.chat_completion(
                prompt=prompt,
                system_prompt="You are a healthcare quality analyst. Respond only in valid JSON.",
                response_format="json_object",
                temperature=0.4,
                max_tokens=1200,
            )
            insights = json.loads(response)
        except Exception as e:
            logger.warning(f"AI insights generation failed: {e}")
            insights = {
                "key_finding": f"A total of {kpi['total']} complaints were recorded in {self._period_label()}. The resolution rate stands at {kpi['resolution_rate']*100:.1f}% against a 95% target. Key areas requiring attention include response time optimization and escalation management.",
                "analysis": f"The complaint data for {self._period_label()} shows {kpi['total']} total complaints with a resolution rate of {kpi['resolution_rate']*100:.1f}%. Medical complaints ({dt['medical']}) form the largest category, followed by administrative ({dt['admin']}). The escalation rate of {esc['escalation_rate']*100:.1f}% indicates significant management attention is required. Response time analysis reveals opportunities for improvement across all departments.",
                "recommendations": [
                    "Implement a real-time complaint tracking dashboard for department heads",
                    "Set clear response time standards with escalation protocols for medical complaints",
                    "Provide customer service and communication skills training for all staff",
                    "Ensure internal complaints receive the same urgency as external ones",
                    "Schedule monthly review meetings with department heads to discuss complaint trends",
                ],
                "next_steps": [
                    "Schedule a meeting with department heads within 2 weeks",
                    "Develop an action plan with specific timelines within 1 month",
                    "Plan for a follow-up report to assess improvement impact within 3 months",
                ],
                "kpi_summary": f"The KPI dashboard for {self._period_label()} shows {kpi['total']} total complaints with a {kpi['resolution_rate']*100:.1f}% resolution rate and {kpi['resolved_72h_rate']*100:.1f}% resolved within 72 hours.",
                "monthly_insight": f"Monthly complaint volumes averaged {round(kpi['total']/12)} per month across the reporting period, with seasonal variations observed.",
                "source_insight": f"Internal sources accounted for {st['internal']} complaints while external government sources (MOH: {st['moh']}, CCHI: {st['chi']}) contributed significantly to the overall volume.",
                "dept_insight": f"Medical departments received {dt['medical']} complaints, making it the highest category, followed by admin ({dt['admin']}) and nursing ({dt['nursing']}).",
                "escalation_insight": f"A total of {esc['total_escalated']} complaints were escalated, representing {esc['escalation_rate']*100:.1f}% of all complaints, requiring senior management intervention.",
                "response_insight": "Response time distribution shows the majority of complaints are addressed within the 72-hour target, though some categories require attention.",
                "resolution_insight": "Average resolution times vary significantly across department types, with medical complaints typically taking longer to resolve.",
                "classification_insight": f"Complaint classifications reveal key areas of concern across {dt['medical']+dt['admin']+dt['nursing']+dt['support']} total complaints, with patterns indicating systemic issues in specific categories.",
            }

        return insights

    # ── Slide Builders ──────────────────────────────────────────

    def _make_slide(self, presentation, order, layout, title, subtitle="", content=None, notes=""):
        return Slide.objects.create(
            presentation=presentation,
            layout=layout,
            order=order,
            title=title,
            subtitle=subtitle,
            content=content or {},
            speaker_notes=notes,
        )

    def _create_cover_slide(self, presentation, order, hospital):
        self._make_slide(
            presentation, order, SlideLayout.COVER,
            title=f"Patient Experience Report",
            subtitle=f"{self._period_label()} — {hospital.name}",
            content={
                "prepared_by": str(self.created_by.get_full_name()) if self.created_by else "",
                "hospital_name": hospital.name,
            },
        )
        return order + 1

    def _create_section_divider(self, presentation, order, number, label, title):
        self._make_slide(
            presentation, order, SlideLayout.SECTION_DIVIDER,
            title=title,
            content={"section_number": number, "section_label": label},
        )
        return order + 1

    def _create_kpi_dashboard_slide(self, presentation, order):
        kpi = self._data["kpi_totals"]
        metrics = [
            {
                "label": "Total Complaints",
                "value": str(kpi["total"]),
                "icon": "bar-chart-3",
                "description": self._period_label(),
            },
            {
                "label": "Resolution Rate",
                "value": f"{kpi['resolution_rate']*100:.1f}%",
                "icon": "check-circle",
                "change": "Target: 95%",
                "change_direction": "neutral",
                "variant": "success" if kpi["resolution_rate"] >= 0.95 else ("warning" if kpi["resolution_rate"] >= 0.9 else "danger"),
                "description": f"{kpi['closed']} of {kpi['total']} complaints resolved",
            },
            {
                "label": "72h Resolution Rate",
                "value": f"{kpi['resolved_72h_rate']*100:.1f}%",
                "icon": "clock",
                "change": "Target: 95%",
                "change_direction": "neutral",
                "variant": "success" if kpi["resolved_72h_rate"] >= 0.95 else ("warning" if kpi["resolved_72h_rate"] >= 0.9 else "danger"),
                "description": f"{kpi['resolved_72h']} resolved within 72 hours",
            },
            {
                "label": "Patient Satisfaction",
                "value": f"{self._data['satisfaction_rate']*100:.1f}%",
                "icon": "heart",
                "change": "Target: 80%",
                "change_direction": "neutral",
                "variant": "success" if self._data["satisfaction_rate"] >= 0.8 else ("warning" if self._data["satisfaction_rate"] >= 0.7 else "danger"),
                "description": f"Based on {self._data['satisfaction_total']} responses",
            },
        ]
        kpi_summary = self._ai_insights.get("kpi_summary", "")
        self._make_slide(
            presentation, order, SlideLayout.KPI_DASHBOARD,
            title=f"Q{self.quarter} {self.year} — At a Glance" if self.quarter else f"{self.year} — At a Glance",
            subtitle=kpi_summary or "Key Performance Indicators",
            content={"metrics": metrics},
            notes=kpi_summary,
        )
        return order + 1

    def _create_monthly_chart_slide(self, presentation, order):
        labels = self._month_labels()
        totals = self._data["monthly_totals"]
        chart_config = {
            "chart": {"type": "bar", "height": "100%", "toolbar": {"show": False}},
            "series": [{"name": "Complaints", "data": totals}],
            "xaxis": {"categories": labels, "labels": {"style": {"fontSize": "14px", "fontWeight": 600}}},
            "colors": ["#005696"],
            "plotOptions": {"bar": {"borderRadius": 6, "columnWidth": "50%"}},
            "dataLabels": {"enabled": True, "style": {"fontSize": "14px", "fontWeight": 600}},
            "grid": {"borderColor": "#e2e8f0", "strokeDashArray": 4},
            "yaxis": {"labels": {"style": {"fontSize": "12px"}}},
        }

        total = sum(totals)
        peak_idx = totals.index(max(totals)) if totals else 0
        sidebar = [
            {"label": "Total Period", "value": str(total)},
            {"label": "Monthly Average", "value": str(round(total / len(totals))) if totals else "0"},
            {"label": f"Peak Month", "value": labels[peak_idx] if labels else "", "description": f"{max(totals)} complaints"},
        ]
        self._make_slide(
            presentation, order, SlideLayout.CHART_METRICS,
            title="Monthly Complaints Trend",
            subtitle=f"Complaint volume by month — {self._period_label()}",
            content={
                "chart_config": chart_config,
                "sidebar_metrics": sidebar,
                "key_insight": self._ai_insights.get("monthly_insight", ""),
            },
        )
        return order + 1

    def _create_source_chart_slide(self, presentation, order):
        st = self._data["source_totals"]
        total = st["total"] or 1

        source_items = [
            ("Internal", st["internal"]),
            ("MOH", st["moh"]),
            ("CCHI", st["chi"]),
            ("Insurance", st["insurance"]),
            ("Patients", st.get("patients", 0)),
        ]
        active_items = [(name, val) for name, val in source_items if val > 0]
        if not active_items:
            active_items = [("No Data", 1)]

        chart_config = {
            "chart": {"type": "donut", "height": "100%"},
            "series": [val for _, val in active_items],
            "labels": [name for name, _ in active_items],
            "colors": ["#005696", "#0EA5E9", "#14B8A6", "#F59E0B", "#FB7185"][:len(active_items)],
            "legend": {"position": "bottom", "fontSize": "14px"},
            "plotOptions": {
                "pie": {"donut": {"size": "65%", "labels": {"show": True, "total": {"show": True, "label": "Total", "fontSize": "18px"}}}}
            },
            "dataLabels": {"enabled": True, "style": {"fontSize": "13px"}},
        }
        sidebar = [
            {"label": name, "value": str(val), "description": f"{val/total*100:.1f}%"}
            for name, val in active_items[:3]
        ]
        self._make_slide(
            presentation, order, SlideLayout.CHART_METRICS,
            title="Complaint Sources — Internal vs External",
            subtitle=self._ai_insights.get("source_insight", "Breakdown by complaint origin"),
            content={
                "chart_config": chart_config,
                "sidebar_metrics": sidebar,
                "key_insight": self._ai_insights.get("source_insight", ""),
            },
        )
        return order + 1

    def _create_dept_chart_slide(self, presentation, order):
        dt = self._data["dept_totals"]
        loc = self._data["location_totals"]
        chart_config = {
            "chart": {"type": "bar", "height": "100%", "toolbar": {"show": False}},
            "series": [{"name": "Complaints", "data": [dt["medical"], dt["admin"], dt["nursing"], dt["support"]]}],
            "xaxis": {"categories": ["Medical", "Admin", "Nursing", "Support Services"]},
            "colors": ["#005696"],
            "plotOptions": {"bar": {"borderRadius": 6, "distributed": True}},
            "dataLabels": {"enabled": True, "style": {"fontSize": "14px", "fontWeight": 600}},
            "colors": ["#005696", "#0EA5E9", "#14B8A6", "#F59E0B"],
            "grid": {"borderColor": "#e2e8f0", "strokeDashArray": 4},
        }
        self._make_slide(
            presentation, order, SlideLayout.FULL_CHART,
            title="Breakdown by Department & Location",
            subtitle=self._ai_insights.get("dept_insight", f"Medical {dt['medical']} · Admin {dt['admin']} · Nursing {dt['nursing']} · Support {dt['support']}  |  IP {loc['ip']} · OP {loc['op']} · ER {loc['er']}"),
            content={"chart_config": chart_config, "footer_note": self._ai_insights.get("dept_insight", "")},
        )
        return order + 1

    def _create_escalation_kpi_slide(self, presentation, order):
        esc = self._data["escalated"]
        by_cat = esc.get("by_category", {})
        med_total = sum(by_cat.get("medical", {}).values()) if isinstance(by_cat.get("medical"), dict) else by_cat.get("medical", 0)
        nonmed_total = sum(by_cat.get("non_medical", {}).values()) if isinstance(by_cat.get("non_medical"), dict) else by_cat.get("non_medical", 0)
        nurs_total = sum(by_cat.get("nursing", {}).values()) if isinstance(by_cat.get("nursing"), dict) else by_cat.get("nursing", 0)

        metrics = [
            {"label": "Medical", "value": str(med_total), "icon": "stethoscope", "variant": "danger"},
            {"label": "Non-Medical", "value": str(nonmed_total), "icon": "building", "variant": "warning"},
            {"label": "Nursing", "value": str(nurs_total), "icon": "heart-pulse", "variant": "warning"},
            {"label": "Escalation Rate", "value": f"{esc['escalation_rate']*100:.1f}%", "icon": "trending-up",
             "description": f"{esc['total_escalated']} of {esc['total_complaints']}", "variant": "danger"},
        ]
        self._make_slide(
            presentation, order, SlideLayout.KPI_DASHBOARD,
            title="Escalated Complaints Overview",
            subtitle=self._ai_insights.get("escalation_insight", f"Total escalated: {esc['total_escalated']} complaints"),
            content={"metrics": metrics},
            notes=self._ai_insights.get("escalation_insight", ""),
        )
        return order + 1

    def _create_escalation_table_slide(self, presentation, order):
        esc = self._data["escalated"]
        by_cat = esc.get("by_category", {})
        headers = ["Department", "Count"]
        rows = []

        for cat_key, cat_label in [("medical", "Medical"), ("non_medical", "Non-Medical"), ("nursing", "Nursing")]:
            cat_data = by_cat.get(cat_key, {})
            if isinstance(cat_data, dict):
                for dept_name, count in sorted(cat_data.items(), key=lambda x: -x[1]):
                    rows.append([f"{cat_label} — {dept_name}", str(count)])

        self._make_slide(
            presentation, order, SlideLayout.DATA_TABLE,
            title="Escalated Complaints — Detailed Breakdown",
            subtitle="By department and domain type",
            content={"headers": headers, "rows": rows},
        )
        return order + 1

    def _create_response_chart_slide(self, presentation, order):
        resp = self._data["response"]
        chart_config = {
            "chart": {"type": "bar", "height": "100%", "stacked": False, "toolbar": {"show": False}},
            "series": [
                {"name": "Internal", "data": [resp["internal"]["counts"][b] for b in TIMELINE_BUCKETS]},
                {"name": "MOH", "data": [resp["moh"]["counts"][b] for b in TIMELINE_BUCKETS]},
                {"name": "CCHI", "data": [resp["chi"]["counts"][b] for b in TIMELINE_BUCKETS]},
            ],
            "xaxis": {"categories": TIMELINE_BUCKETS},
            "colors": ["#005696", "#0EA5E9", "#14B8A6"],
            "plotOptions": {"bar": {"borderRadius": 4, "columnWidth": "60%"}},
            "dataLabels": {"enabled": True, "style": {"fontSize": "13px"}},
            "grid": {"borderColor": "#e2e8f0", "strokeDashArray": 4},
            "legend": {"position": "top", "fontSize": "14px"},
        }
        sidebar = [
            {
                "label": "Internal Total",
                "value": str(resp["internal"]["total"]),
                "description": f"{resp['internal']['rates'].get('72h+', 0)*100:.0f}% over 72h",
            },
            {
                "label": "MOH Total",
                "value": str(resp["moh"]["total"]),
                "description": f"{resp['moh']['rates'].get('72h+', 0)*100:.0f}% over 72h",
            },
            {
                "label": "CCHI Total",
                "value": str(resp["chi"]["total"]),
                "description": f"{resp['chi']['rates'].get('72h+', 0)*100:.0f}% over 72h",
            },
        ]
        self._make_slide(
            presentation, order, SlideLayout.CHART_METRICS,
            title="Response Time Distribution",
            subtitle="Response rate by source — 24h / 48h / 72h / 72h+",
            content={
                "chart_config": chart_config,
                "sidebar_metrics": sidebar,
                "key_insight": self._ai_insights.get("response_insight", ""),
            },
        )
        return order + 1

    def _create_response_days_slide(self, presentation, order):
        months = self._active_months()
        qs = Complaint.objects.filter(
            hospital_id=self.hospital_id,
            created_at__year=self.year,
            created_at__month__in=months,
            status__in=["closed", "resolved"],
            closed_at__isnull=False,
        )

        dept_days = defaultdict(list)
        for c in qs.select_related("domain").iterator(chunk_size=2000):
            if c.created_at and c.closed_at:
                days = (c.closed_at - c.created_at).total_seconds() / 86400
                dt = ""
                if c.domain:
                    dt = c.domain.domain_type or ""
                if dt in ("admin", "non_medical"):
                    dept_days["Non-Medical"].append(days)
                elif dt == "nursing":
                    dept_days["Nursing"].append(days)
                elif dt in ("support", "support_services"):
                    dept_days["Support Services"].append(days)
                else:
                    dept_days["Medical"].append(days)

        categories = []
        values = []
        for name in ["Medical", "Non-Medical", "Nursing", "Support Services"]:
            days_list = dept_days.get(name, [0])
            avg = round(sum(days_list) / len(days_list), 1) if days_list else 0
            categories.append(name)
            values.append(avg)

        chart_config = {
            "chart": {"type": "bar", "height": "100%", "toolbar": {"show": False}},
            "series": [{"name": "Avg Days", "data": values}],
            "xaxis": {"categories": categories},
            "colors": ["#005696", "#0EA5E9", "#14B8A6", "#F59E0B"],
            "plotOptions": {"bar": {"borderRadius": 6, "distributed": True}},
            "dataLabels": {"enabled": True, "formatter": "function(v){return v+'d'}", "style": {"fontSize": "14px", "fontWeight": 600}},
            "grid": {"borderColor": "#e2e8f0", "strokeDashArray": 4},
            "yaxis": {"title": {"text": "Days"}, "labels": {"style": {"fontSize": "12px"}}},
        }
        self._make_slide(
            presentation, order, SlideLayout.FULL_CHART,
            title="Average Response Rate by Department",
            subtitle=self._ai_insights.get("resolution_insight", "Mean response time in days"),
            content={"chart_config": chart_config, "footer_note": self._ai_insights.get("resolution_insight", "")},
        )
        return order + 1

    def _create_classification_table_slide(self, presentation, order):
        months = self._active_months()
        qs = Complaint.objects.filter(
            hospital_id=self.hospital_id,
            created_at__year=self.year,
            created_at__month__in=months,
        ).select_related("domain", "category", "subcategory_obj", "classification_obj")

        classification_counts = defaultdict(lambda: defaultdict(int))
        domain_map = {}
        for c in qs.iterator(chunk_size=2000):
            domain_name = str(c.domain) if c.domain else "Uncategorized"
            cat_name = str(c.category) if c.category else "Other"
            domain_type = ""
            if c.domain:
                domain_type = c.domain.domain_type or ""
            if domain_type in ("admin", "non_medical"):
                group = "Non-Medical"
            elif domain_type == "nursing":
                group = "Nursing"
            elif domain_type in ("support", "support_services"):
                group = "Support Services"
            else:
                group = "Medical"
            classification_counts[group][cat_name] += 1

        headers = ["Group", "Category", "Count"]
        rows = []
        for group in ["Medical", "Non-Medical", "Nursing", "Support Services"]:
            cats = classification_counts.get(group, {})
            for cat_name, count in sorted(cats.items(), key=lambda x: -x[1]):
                rows.append([group, cat_name, str(count)])

        self._make_slide(
            presentation, order, SlideLayout.DATA_TABLE,
            title="Complaint Classification",
            subtitle=self._ai_insights.get("classification_insight", f"Categories breakdown — {self._period_label()}"),
            content={"headers": headers, "rows": rows},
            notes=self._ai_insights.get("classification_insight", ""),
        )
        return order + 1

    def _create_key_finding_slide(self, presentation, order):
        finding = self._ai_insights.get("key_finding", "No key finding generated.")
        self._make_slide(
            presentation, order, SlideLayout.QUOTE,
            title="Key Finding",
            content={"quote": finding, "attribution": f"AI-Generated Insight — {self._period_label()}"},
        )
        return order + 1

    def _create_analysis_slide(self, presentation, order):
        analysis = self._ai_insights.get("analysis", "")
        recs = self._ai_insights.get("recommendations", [])
        right_bullets = [{"text": r} for r in recs]

        self._make_slide(
            presentation, order, SlideLayout.TWO_COLUMN,
            title="Analysis & Recommendations",
            content={
                "left_title": "Analysis",
                "left_body": [analysis],
                "right_title": "Recommendations",
                "right_bullets": right_bullets,
            },
        )
        return order + 1

    def _create_employees_slide(self, presentation, order):
        months = self._active_months()
        staff_qs = ComplaintInvolvedStaff.objects.filter(
            complaint__hospital_id=self.hospital_id,
            complaint__created_at__year=self.year,
            complaint__created_at__month__in=months,
            role="accused",
        ).select_related("staff", "staff__department", "complaint")

        dept_staff = defaultdict(lambda: defaultdict(int))
        for inv in staff_qs.iterator(chunk_size=5000):
            staff_name = str(inv.staff) if inv.staff else "Unknown"
            dept_name = str(inv.staff.department) if inv.staff and inv.staff.department else "Other"
            dept_staff[dept_name][staff_name] += 1

        members = []
        for dept_name, staff_dict in sorted(dept_staff.items()):
            for staff_name, count in sorted(staff_dict.items(), key=lambda x: -x[1]):
                members.append({
                    "name": staff_name,
                    "role": dept_name,
                    "metric_value": str(count),
                    "metric_label": "Complaints",
                    "initials": staff_name[0].upper() if staff_name else "?",
                })

        if not members:
            members.append({"name": "No employee-specific data", "role": "N/A", "initials": "-"})

        self._make_slide(
            presentation, order, SlideLayout.TEAM_GRID,
            title="Employee-Specific Patient Complaints",
            subtitle=f"Staff with complaints — {self._period_label()}",
            content={"members": members},
        )
        return order + 1

    def _create_closing_slide(self, presentation, order, hospital):
        next_steps = self._ai_insights.get("next_steps", [])
        contact = []
        if hospital.email:
            contact.append(hospital.email)
        if hospital.phone:
            contact.append(f"Phone: {hospital.phone}")

        self._make_slide(
            presentation, order, SlideLayout.CLOSING,
            title="Thank You",
            subtitle=" | ".join(next_steps) if next_steps else "Thank you for your attention.",
            content={"contact": contact},
        )
        return order + 1


RATING_ROW_CLASSES = {
    "high": "high",
    "medium": "medium",
    "low": "low",
    "top": "top",
}


class DoctorRatingsReportService:
    DOCTORS_PER_SLIDE = 18

    def __init__(self, hospital_id, year, quarter=None, created_by=None):
        self.hospital_id = hospital_id
        self.year = year
        self.quarter = quarter
        self.created_by = created_by
        self._data = {}
        self._ai_insights = {}
        self._dept_managers = {}

    def generate(self):
        self._data = self._fetch_data()
        self._ai_insights = self._generate_ai_insights()

        period_label = self._period_label()
        hospital = Hospital.objects.get(pk=self.hospital_id)

        presentation = Presentation.objects.create(
            title=f"Doctors' Ratings Report — {period_label}",
            subtitle=f"{hospital.name}",
            description=f"Auto-generated doctor ratings presentation for {period_label}",
            theme=PresentationTheme.HEALTHCARE_MODERN,
            status=PresentationStatus.PUBLISHED,
            presentation_type="doctor_ratings",
            created_by=self.created_by,
            hospital=hospital,
            presentation_date=timezone.now().date(),
            is_shared=True,
        )

        order = 0
        order = self._create_cover_slide(presentation, order, hospital)
        order = self._create_section_divider(presentation, order, "01", "OVERVIEW", "Rating Performance Summary")
        order = self._create_kpi_dashboard_slide(presentation, order)
        order = self._create_distribution_chart_slide(presentation, order)
        order = self._create_monthly_trend_slide(presentation, order)
        order = self._create_section_divider(presentation, order, "02", "METHODOLOGY", "Survey & Rating Categories")
        order = self._create_overview_slide(presentation, order)
        order = self._create_section_divider(presentation, order, "03", "DEPARTMENTS", "Department Ratings Breakdown")
        order = self._create_department_slides(presentation, order)
        order = self._create_section_divider(presentation, order, "04", "INSIGHTS", "Top Performers & AI Insights")
        order = self._create_top_performers_slide(presentation, order)
        order = self._create_key_finding_slide(presentation, order)
        order = self._create_analysis_slide(presentation, order)
        order = self._create_closing_slide(presentation, order, hospital)

        return presentation

    def _period_label(self):
        if self.quarter:
            return f"Q{self.quarter}.{self.year}"
        return str(self.year)

    def _active_months(self):
        if self.quarter:
            return QUARTER_MONTHS[self.quarter]
        return list(range(1, 13))

    def _month_labels(self):
        months = self._active_months()
        return [calendar.month_abbr[m] for m in months]

    def _rating_row_class(self, avg, is_top=False):
        if is_top:
            return "top"
        if avg >= 4.5:
            return "high"
        elif avg >= 3.0:
            return "medium"
        return "low"

    def _get_dept_manager(self, dept_name):
        if dept_name in self._dept_managers:
            return self._dept_managers[dept_name]
        from apps.organizations.models import Department
        dept = Department.objects.filter(name__iexact=dept_name).first()
        if dept and dept.manager:
            name = dept.manager.get_full_name() or str(dept.manager)
            self._dept_managers[dept_name] = name
            return name
        self._dept_managers[dept_name] = ""
        return ""

    def _fetch_data(self):
        from apps.physicians.models import PhysicianMonthlyRating

        months = self._active_months()
        ratings_qs = PhysicianMonthlyRating.objects.filter(
            staff__hospital_id=self.hospital_id,
            year=self.year,
            month__in=months,
        ).select_related("staff", "staff__department")

        overall = ratings_qs.aggregate(
            total_doctors=Count("staff", distinct=True),
            total_surveys=Sum("total_surveys"),
            avg_rating=Avg("average_rating"),
            total_positive=Sum("positive_count"),
            total_neutral=Sum("neutral_count"),
            total_negative=Sum("negative_count"),
            total_r1=Sum("rating_1_count"),
            total_r2=Sum("rating_2_count"),
            total_r3=Sum("rating_3_count"),
            total_r4=Sum("rating_4_count"),
            total_r5=Sum("rating_5_count"),
        )

        total_surveys = overall["total_surveys"] or 0
        total_positive = overall["total_positive"] or 0
        total_negative = overall["total_negative"] or 0
        avg_rating = overall["avg_rating"] or 0

        doctor_ratings = defaultdict(list)
        for r in ratings_qs:
            dept_name = str(r.staff.department) if r.staff.department else "Uncategorized"
            doctor_ratings[r.staff_id].append({
                "name": r.staff.name or f"{r.staff.first_name} {r.staff.last_name}",
                "employee_id": r.staff.employee_id or "",
                "department": dept_name,
                "avg_rating": float(r.average_rating),
                "total_surveys": r.total_surveys,
                "positive": r.positive_count,
                "neutral": r.neutral_count,
                "negative": r.negative_count,
                "r1": r.rating_1_count,
                "r2": r.rating_2_count,
                "r3": r.rating_3_count,
                "r4": r.rating_4_count,
                "r5": r.rating_5_count,
                "month": r.month,
            })

        consolidated = {}
        for sid, entries in doctor_ratings.items():
            name = entries[0]["name"]
            eid = entries[0]["employee_id"]
            dept = entries[0]["department"]
            total_s = sum(e["total_surveys"] for e in entries)
            if total_s == 0:
                continue
            weighted_sum = sum(e["avg_rating"] * e["total_surveys"] for e in entries)
            avg = round(weighted_sum / total_s, 2)
            consolidated[sid] = {
                "name": name,
                "employee_id": eid,
                "department": dept,
                "avg_rating": avg,
                "total_surveys": total_s,
                "r1": sum(e["r1"] for e in entries),
                "r2": sum(e["r2"] for e in entries),
                "r3": sum(e["r3"] for e in entries),
                "r4": sum(e["r4"] for e in entries),
                "r5": sum(e["r5"] for e in entries),
                "positive": sum(e["positive"] for e in entries),
                "neutral": sum(e["neutral"] for e in entries),
                "negative": sum(e["negative"] for e in entries),
            }

        by_department = defaultdict(list)
        for doc in consolidated.values():
            by_department[doc["department"]].append(doc)

        for dept_docs in by_department.values():
            dept_docs.sort(key=lambda d: d["avg_rating"], reverse=True)

        monthly_avg = {}
        for m in months:
            m_ratings = ratings_qs.filter(month=m)
            m_avg = m_ratings.aggregate(a=Avg("average_rating"))["a"]
            monthly_avg[m] = round(float(m_avg), 2) if m_avg else 0

        return {
            "overall": {
                "total_doctors": overall["total_doctors"] or 0,
                "total_surveys": total_surveys,
                "avg_rating": round(float(avg_rating), 2),
                "total_positive": total_positive,
                "total_negative": total_negative,
                "total_neutral": overall["total_neutral"] or 0,
                "r1": overall["total_r1"] or 0,
                "r2": overall["total_r2"] or 0,
                "r3": overall["total_r3"] or 0,
                "r4": overall["total_r4"] or 0,
                "r5": overall["total_r5"] or 0,
                "positive_pct": round(total_positive / total_surveys * 100, 1) if total_surveys else 0,
                "negative_pct": round(total_negative / total_surveys * 100, 1) if total_surveys else 0,
            },
            "by_department": dict(by_department),
            "consolidated": consolidated,
            "monthly_avg": monthly_avg,
            "months": months,
        }

    def _generate_ai_insights(self):
        o = self._data["overall"]
        dept_count = len(self._data["by_department"])
        top_dept = max(self._data["by_department"].items(), key=lambda x: max(d["avg_rating"] for d in x[1])) if self._data["by_department"] else (None, [])
        top_dept_avg = max((d["avg_rating"] for d in top_dept[1]), default=0)

        data_summary = (
            f"Period: {self._period_label()}\n"
            f"Hospital ID: {self.hospital_id}\n"
            f"Total Physicians Rated: {o['total_doctors']}\n"
            f"Total Surveys: {o['total_surveys']}\n"
            f"Average Rating: {o['avg_rating']}/5.0\n"
            f"Positive Ratings (>=4): {o['positive_pct']}%\n"
            f"Negative Ratings (<3): {o['negative_pct']}%\n"
            f"Rating Distribution: 1*={o['r1']} 2*={o['r2']} 3*={o['r3']} 4*={o['r4']} 5*={o['r5']}\n"
            f"Departments: {dept_count}\n"
            f"Highest Dept: {top_dept[0]} (avg {top_dept_avg:.2f})\n"
        )

        prompt = (
            "You are a healthcare quality analyst for a Saudi Arabian hospital. "
            "Based on the following doctor ratings data, generate a JSON response with exactly these keys:\n"
            '- "key_finding": A 2-3 sentence quote summarizing the most critical finding.\n'
            '- "analysis": A 4-5 sentence analytical narrative about trends and patterns.\n'
            '- "recommendations": An array of 4-5 actionable recommendation strings.\n'
            '- "next_steps": An array of 3 concrete next-step strings with timelines.\n'
            '- "kpi_summary": A 1-2 sentence summary of the overall rating performance.\n'
            '- "distribution_insight": A 1-2 sentence insight about the rating distribution.\n'
            '- "trend_insight": A 1-2 sentence insight about monthly rating trends.\n'
            '- "department_insight": A 1-2 sentence insight about department-level performance.\n\n'
            f"Data:\n{data_summary}"
        )

        try:
            response = AIService.chat_completion(
                prompt=prompt,
                system_prompt="You are a healthcare quality analyst. Respond only in valid JSON.",
                response_format="json_object",
                temperature=0.4,
                max_tokens=1200,
            )
            insights = json.loads(response)
        except Exception as e:
            logger.warning(f"AI insights generation failed: {e}")
            insights = {
                "key_finding": f"Across {o['total_doctors']} physicians, the average patient rating is {o['avg_rating']}/5.0 based on {o['total_surveys']} surveys. {o['positive_pct']}% of ratings are positive (4-5 stars) while {o['negative_pct']}% are negative (1-2 stars).",
                "analysis": f"The doctor ratings data for {self._period_label()} covers {o['total_doctors']} physicians across {dept_count} departments with an overall average of {o['avg_rating']}/5.0. The distribution shows {o['r5']} five-star and {o['r4']} four-star ratings, indicating generally positive patient experiences. Areas for improvement are identified by departments with lower averages.",
                "recommendations": [
                    "Recognize top-performing physicians with the highest patient ratings quarterly",
                    "Provide targeted coaching for physicians with ratings below 3.0",
                    "Implement real-time patient feedback monitoring dashboards for department heads",
                    "Develop peer mentoring programs pairing high-rated with low-rated physicians",
                    "Schedule monthly department-level review meetings to discuss rating trends",
                ],
                "next_steps": [
                    "Schedule recognition ceremony for top performers within 2 weeks",
                    "Develop individual improvement plans for low-rated physicians within 1 month",
                    "Plan follow-up analysis for next quarter within 3 months",
                ],
                "kpi_summary": f"The hospital has {o['total_doctors']} rated physicians with an average rating of {o['avg_rating']}/5.0 from {o['total_surveys']} patient surveys.",
                "distribution_insight": f"Rating distribution shows {o['r5']} five-star and {o['r4']} four-star ratings as the most common.",
                "trend_insight": "Monthly rating trends indicate stable performance across the reporting period.",
                "department_insight": f"Across {dept_count} departments, performance varies with opportunities for targeted improvement in lower-rated specialties.",
            }

        return insights

    def _make_slide(self, presentation, order, layout, title, subtitle="", content=None, notes=""):
        return Slide.objects.create(
            presentation=presentation,
            layout=layout,
            order=order,
            title=title,
            subtitle=subtitle,
            content=content or {},
            speaker_notes=notes,
        )

    def _create_cover_slide(self, presentation, order, hospital):
        self._make_slide(
            presentation, order, SlideLayout.COVER,
            title="Doctors' Ratings Report",
            subtitle=f"{self._period_label()} — {hospital.name}",
            content={
                "prepared_by": str(self.created_by.get_full_name()) if self.created_by else "",
                "hospital_name": hospital.name,
                "department": "Patient Experience Department",
            },
        )
        return order + 1

    def _create_section_divider(self, presentation, order, number, label, title):
        self._make_slide(
            presentation, order, SlideLayout.SECTION_DIVIDER,
            title=title,
            content={"section_number": number, "section_label": label},
        )
        return order + 1

    def _create_kpi_dashboard_slide(self, presentation, order):
        o = self._data["overall"]
        metrics = [
            {
                "label": "Total Physicians",
                "value": str(o["total_doctors"]),
                "icon": "users",
                "description": f"Across {len(self._data['by_department'])} departments",
            },
            {
                "label": "Average Rating",
                "value": f"{o['avg_rating']:.2f}",
                "icon": "star",
                "description": f"Out of {o['total_surveys']} surveys",
                "variant": "success" if o["avg_rating"] >= 4.5 else ("warning" if o["avg_rating"] >= 3.5 else "danger"),
            },
            {
                "label": "Positive (>=4*)",
                "value": f"{o['positive_pct']}%",
                "icon": "thumbs-up",
                "description": f"{o['total_positive']} ratings",
                "variant": "success" if o["positive_pct"] >= 80 else ("warning" if o["positive_pct"] >= 60 else "danger"),
            },
            {
                "label": "Negative (<3*)",
                "value": f"{o['negative_pct']}%",
                "icon": "thumbs-down",
                "description": f"{o['total_negative']} ratings",
                "variant": "success" if o["negative_pct"] <= 10 else ("warning" if o["negative_pct"] <= 20 else "danger"),
            },
        ]
        self._make_slide(
            presentation, order, SlideLayout.KPI_DASHBOARD,
            title=f"{self._period_label()} — At a Glance",
            subtitle=self._ai_insights.get("kpi_summary", "Key Performance Indicators"),
            content={"metrics": metrics},
            notes=self._ai_insights.get("kpi_summary", ""),
        )
        return order + 1

    def _create_distribution_chart_slide(self, presentation, order):
        o = self._data["overall"]
        chart_config = {
            "chart": {"type": "bar", "height": "100%", "toolbar": {"show": False}},
            "series": [{"name": "Ratings", "data": [o["r1"], o["r2"], o["r3"], o["r4"], o["r5"]]}],
            "xaxis": {
                "categories": ["1 *", "2 *", "3 *", "4 *", "5 *"],
                "labels": {"style": {"fontSize": "14px", "fontWeight": 600}},
            },
            "colors": ["#ED1A24", "#f97316", "#eab308", "#0B769F", "#0B769F"],
            "plotOptions": {"bar": {"borderRadius": 6, "distributed": True}},
            "dataLabels": {"enabled": True, "style": {"fontSize": "14px", "fontWeight": 600}},
            "grid": {"borderColor": "#e2e8f0", "strokeDashArray": 4},
            "yaxis": {"labels": {"style": {"fontSize": "12px"}}, "title": {"text": "Count"}},
        }
        total = o["total_surveys"] or 1
        sidebar = [
            {"label": "5* Ratings", "value": str(o["r5"]), "description": f"{round(o['r5']/total*100,1)}% of total"},
            {"label": "4* Ratings", "value": str(o["r4"]), "description": f"{round(o['r4']/total*100,1)}% of total"},
            {"label": "1-2* Ratings", "value": str(o["r1"] + o["r2"]), "description": f"{round((o['r1']+o['r2'])/total*100,1)}% of total"},
        ]
        self._make_slide(
            presentation, order, SlideLayout.CHART_METRICS,
            title="Rating Distribution",
            subtitle=f"How patients rate their doctors — {self._period_label()}",
            content={
                "chart_config": chart_config,
                "sidebar_metrics": sidebar,
                "key_insight": self._ai_insights.get("distribution_insight", ""),
            },
        )
        return order + 1

    def _create_monthly_trend_slide(self, presentation, order):
        months = self._data["months"]
        monthly_avg = self._data["monthly_avg"]
        labels = [calendar.month_abbr[m] for m in months]
        values = [monthly_avg[m] for m in months]

        chart_config = {
            "chart": {"type": "line", "height": "100%", "toolbar": {"show": False}},
            "series": [{"name": "Avg Rating", "data": values}],
            "xaxis": {
                "categories": labels,
                "labels": {"style": {"fontSize": "14px", "fontWeight": 600}},
            },
            "colors": ["#0B769F"],
            "stroke": {"curve": "smooth", "width": 3},
            "markers": {"size": 5},
            "yaxis": {"min": 1, "max": 5, "tickAmount": 4, "labels": {"style": {"fontSize": "12px"}}},
            "grid": {"borderColor": "#e2e8f0", "strokeDashArray": 4},
            "annotations": {
                "yaxis": [{
                    "y": 4.5,
                    "borderColor": "#3A7C22",
                    "strokeDashArray": 4,
                    "label": {"text": "Target: 4.5", "style": {"color": "#fff", "background": "#3A7C22"}},
                }]
            },
        }
        o = self._data["overall"]
        sidebar = [
            {"label": "Overall Avg", "value": f"{o['avg_rating']:.2f}"},
            {"label": "Total Surveys", "value": str(o["total_surveys"])},
            {"label": "Departments", "value": str(len(self._data["by_department"]))},
        ]
        self._make_slide(
            presentation, order, SlideLayout.CHART_METRICS,
            title="Monthly Rating Trend",
            subtitle=f"Average physician rating by month — {self._period_label()}",
            content={
                "chart_config": chart_config,
                "sidebar_metrics": sidebar,
                "key_insight": self._ai_insights.get("trend_insight", ""),
            },
        )
        return order + 1

    def _create_overview_slide(self, presentation, order):
        o = self._data["overall"]
        left_body = (
            f"Ratings were collected from patients following each appointment, "
            f"and data was compiled throughout {self._period_label()}. "
            f"The report covers {o['total_doctors']} physicians across "
            f"{len(self._data['by_department'])} departments, with a total of "
            f"{o['total_surveys']} patient surveys. "
            f"The average rating is calculated by multiplying each rating by its "
            f"corresponding frequency, summing these products, and dividing by the "
            f"total number of ratings."
        )
        right_bullets = [
            {"text": "Blue (4.5 - 5.0): High Rating"},
            {"text": "Black (3.0 - 4.4): Medium Rating"},
            {"text": "Red (1.0 - 2.9): Low Rating"},
            {"text": "Green: Special Highlight (highest score among peers in department)"},
        ]
        self._make_slide(
            presentation, order, SlideLayout.TWO_COLUMN,
            title="Survey Overview & Rating Categories",
            content={
                "left_title": "Methodology",
                "left_body": [left_body],
                "right_title": "Rating Categories",
                "right_bullets": right_bullets,
            },
            notes="Rating categories: Blue >= 4.5, Black 3.0-4.4, Red < 3.0, Green = department highest",
        )
        return order + 1

    def _create_department_slides(self, presentation, order):
        DOCTORS_PER_SLIDE = self.DOCTORS_PER_SLIDE
        for dept_name, doctors in sorted(self._data["by_department"].items()):
            max_avg = max((d["avg_rating"] for d in doctors), default=0)
            manager_name = self._get_dept_manager(dept_name)
            for i in range(0, len(doctors), DOCTORS_PER_SLIDE):
                chunk = doctors[i:i + DOCTORS_PER_SLIDE]
                headers = ["Doctor's ID", "Doctor's Name", "Total Ratings", "Average Ratings"]
                rows = []
                for d in chunk:
                    is_top = d["avg_rating"] == max_avg
                    row_class = self._rating_row_class(d["avg_rating"], is_top)
                    rows.append([
                        {"text": d["employee_id"], "row_bg": row_class},
                        {"text": d["name"], "row_bg": row_class, "font_weight": "600"},
                        {"text": str(d["total_surveys"]), "row_bg": row_class},
                        {"rating_bar": {"value": f"{d['avg_rating']:.2f}", "pct": round(d["avg_rating"] / 5 * 100)}, "row_bg": row_class},
                    ])

                slide_title = dept_name
                slide_subtitle = f"{len(doctors)} physicians"
                if manager_name:
                    slide_subtitle = f"Head of Department: {manager_name}"
                if i > 0:
                    slide_subtitle += f"  |  Page {i // DOCTORS_PER_SLIDE + 1} of {(len(doctors) - 1) // DOCTORS_PER_SLIDE + 1}"

                self._make_slide(
                    presentation, order, SlideLayout.DATA_TABLE,
                    title=slide_title,
                    subtitle=slide_subtitle,
                    content={"headers": headers, "rows": rows},
                )
                order += 1
        return order

    def _create_top_performers_slide(self, presentation, order):
        all_docs = sorted(self._data["consolidated"].values(), key=lambda d: d["avg_rating"], reverse=True)
        top = all_docs[:10]
        members = []
        for d in top:
            members.append({
                "name": d["name"],
                "role": d["department"],
                "metric_value": f"{d['avg_rating']:.2f}",
                "metric_label": "Avg Rating",
                "initials": d["name"][0].upper() if d["name"] else "?",
            })
        if not members:
            members.append({"name": "No physician data", "role": "N/A", "initials": "-"})
        self._make_slide(
            presentation, order, SlideLayout.TEAM_GRID,
            title="Top Rated Physicians",
            subtitle=f"Highest rated doctors — {self._period_label()}",
            content={"members": members},
        )
        return order + 1

    def _create_key_finding_slide(self, presentation, order):
        finding = self._ai_insights.get("key_finding", "No key finding generated.")
        self._make_slide(
            presentation, order, SlideLayout.QUOTE,
            title="Key Finding",
            content={"quote": finding, "attribution": f"AI-Generated Insight — {self._period_label()}"},
        )
        return order + 1

    def _create_analysis_slide(self, presentation, order):
        analysis = self._ai_insights.get("analysis", "")
        recs = self._ai_insights.get("recommendations", [])
        right_bullets = [{"text": r} for r in recs]
        self._make_slide(
            presentation, order, SlideLayout.TWO_COLUMN,
            title="Analysis & Recommendations",
            content={
                "left_title": "Analysis",
                "left_body": [analysis],
                "right_title": "Recommendations",
                "right_bullets": right_bullets,
            },
        )
        return order + 1

    def _create_closing_slide(self, presentation, order, hospital):
        next_steps = self._ai_insights.get("next_steps", [])
        contact = []
        if hospital.email:
            contact.append(hospital.email)
        if hospital.phone:
            contact.append(f"Phone: {hospital.phone}")
        self._make_slide(
            presentation, order, SlideLayout.CLOSING,
            title="Thank You",
            subtitle=" | ".join(next_steps) if next_steps else "Thank you for your attention.",
            content={"contact": contact},
        )
        return order + 1
