import calendar
from io import BytesIO

from openpyxl import Workbook
from openpyxl.chart import BarChart, LineChart, PieChart, Reference
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter

BLUE_FILL = PatternFill(start_color="2F75B5", end_color="2F75B5", fill_type="solid")
GREEN_FILL = PatternFill(start_color="00B050", end_color="00B050", fill_type="solid")
HEADER_FONT = Font(name="Calibri", size=11, bold=True, color="FFFFFF")
BOLD_FONT = Font(name="Calibri", size=11, bold=True)
DATA_FONT = Font(name="Calibri", size=11)
PCT_FMT = "0.0%"
NUM_FMT = "0.0"
THIN = Side(style="thin")
BORDER_ALL = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
CENTER = Alignment(horizontal="center", vertical="center", wrap_text=True)
RIGHT_ALIGN = Alignment(horizontal="right", vertical="center")


def _style_header(ws, row, col, value):
    cell = ws.cell(row=row, column=col, value=value)
    cell.font = HEADER_FONT
    cell.fill = BLUE_FILL
    cell.alignment = CENTER
    cell.border = BORDER_ALL
    return cell


def _style_data(ws, row, col, value, fmt=None):
    cell = ws.cell(row=row, column=col, value=value)
    cell.font = DATA_FONT
    cell.alignment = CENTER
    cell.border = BORDER_ALL
    if fmt:
        cell.number_format = fmt
    return cell


def _style_bold(ws, row, col, value, fmt=None):
    cell = ws.cell(row=row, column=col, value=value)
    cell.font = BOLD_FONT
    cell.border = BORDER_ALL
    cell.alignment = CENTER
    if fmt:
        cell.number_format = fmt
    return cell


def _strip_tz(dt):
    if dt is None:
        return None
    return dt.replace(tzinfo=None)


def _write_all_quarters_sheet(wb, service):
    rows = service.get_all_complaints_raw()
    ws = wb.create_sheet("ALL Quarters")

    headers = [
        "Week", "#", "Reference", "Source", "Location", "Department", "Domain",
        "Created At", "Entered By", "Timeline",
        "Form Sent", "Activated At", "Assigned To",
        "Sent To Dept", "Time Entry→Send",
        "First Reminder", "Time 1st Reminder→Send",
        "Second Reminder", "Escalated At",
        "Closed At", "Resolved At",
        "Processing Hours", "Response Date", "Status", "Satisfaction",
    ]
    for i, h in enumerate(headers, 1):
        _style_header(ws, 1, i, h)

    for idx, r in enumerate(rows, 2):
        week_num = (r["created_at"].day - 1) // 15 + 1
        week_label = "1st Half" if r["created_at"].day <= 15 else "2nd Half"
        col = 1
        _style_data(ws, idx, col, f"{week_label} of the month, From {'1' if r['created_at'].day <= 15 else '16'} to {'15' if r['created_at'].day <= 15 else str(calendar.monthrange(r['created_at'].year, r['created_at'].month)[1])}"); col += 1
        _style_data(ws, idx, col, idx - 1); col += 1
        _style_data(ws, idx, col, r["reference"]); col += 1
        _style_data(ws, idx, col, r["source"]); col += 1
        _style_data(ws, idx, col, r["location"]); col += 1
        _style_data(ws, idx, col, r["department"]); col += 1
        _style_data(ws, idx, col, r["domain"]); col += 1
        _style_data(ws, idx, col, _strip_tz(r["created_at"])); col += 1
        _style_data(ws, idx, col, r["entered_by"]); col += 1
        _style_data(ws, idx, col, r["timeline_bucket"]); col += 1
        _style_data(ws, idx, col, _strip_tz(r["form_sent_at"])); col += 1
        _style_data(ws, idx, col, _strip_tz(r["activated_at"])); col += 1
        _style_data(ws, idx, col, r["assigned_to"]); col += 1
        _style_data(ws, idx, col, _strip_tz(r["sent_at"])); col += 1
        _style_data(ws, idx, col, r["time_entry_to_send"]); col += 1
        _style_data(ws, idx, col, _strip_tz(r["first_reminder"])); col += 1
        _style_data(ws, idx, col, r["time_first_reminder_to_send"]); col += 1
        _style_data(ws, idx, col, _strip_tz(r.get("second_reminder"))); col += 1
        _style_data(ws, idx, col, _strip_tz(r["escalated_at"])); col += 1
        _style_data(ws, idx, col, _strip_tz(r["closed_at"])); col += 1
        _style_data(ws, idx, col, _strip_tz(r["resolved_at"])); col += 1
        _style_data(ws, idx, col, r["processing_hours"]); col += 1
        _style_data(ws, idx, col, r["response_date"]); col += 1
        _style_data(ws, idx, col, r["status"]); col += 1
        _style_data(ws, idx, col, r["satisfaction"]); col += 1

    for c in range(1, len(headers) + 1):
        ws.column_dimensions[get_column_letter(c)].width = 18
    ws.column_dimensions["B"].width = 8
    ws.column_dimensions["C"].width = 20


def _write_kpi_sheet(wb, service):
    kpi = service.get_kpi_data()
    satisfaction = service.get_satisfaction_data()
    moh_kpi = service.get_moh_kpi_data()
    months = service.active_month_labels
    active_months = service.active_months
    total_col = 2 + len(months)

    def _kpi_row(ws, row, label, get_val, total_val, is_pct=False):
        _style_data(ws, row, 1, label)
        for i, m in enumerate(active_months):
            v = get_val(m)
            fmt = PCT_FMT if is_pct else None
            _style_data(ws, row, 2 + i, v, fmt)
        _style_data(ws, row, total_col, total_val, PCT_FMT if is_pct else None)

    blocks = [
        {
            "kpi_label": "KPI #1",
            "header": "Numerator/Denominator",
            "rows": [
                ("Total Number of Closed Complaints", lambda m: kpi["months"][m]["closed"], kpi["totals"]["closed"]),
                ("Total Number of Complaints", lambda m: kpi["months"][m]["total"], kpi["totals"]["total"]),
                ("Result (%)", lambda m: kpi["months"][m]["resolution_rate"], kpi["totals"]["resolution_rate"], True),
            ],
        },
        {
            "kpi_label": "KPI #2",
            "header": "Numerator/Denominator",
            "rows": [
                ("Number of Complaints Resolved Within 72 Hours", lambda m: kpi["months"][m]["resolved_72h"], kpi["totals"]["resolved_72h"]),
                ("Total Number of Complaints", lambda m: kpi["months"][m]["total"], kpi["totals"]["total"]),
                ("Result (%)", lambda m: kpi["months"][m]["resolved_72h_rate"], kpi["totals"]["resolved_72h_rate"], True),
            ],
        },
        {
            "kpi_label": "KPI #3",
            "header": "Satisfaction Rate",
            "rows": [
                ("Number\u00a0of\u00a0Satisfied\u00a0Responses", lambda m: satisfaction["months"][m]["satisfied"], satisfaction["totals"]["satisfied"]),
                ("Total Number of Patient Surveyed (Responses)", lambda m: satisfaction["months"][m]["surveyed"], satisfaction["totals"]["surveyed"]),
                ("Result (%)", lambda m: satisfaction["months"][m]["rate"], satisfaction["totals"]["rate"], True),
            ],
        },
        {
            "kpi_label": "KPI #3",
            "header": "Response Rate - Calls",
            "rows": [
                ("Total Responses", lambda m: satisfaction["call_months"][m]["responses"], satisfaction["call_totals"]["responses"]),
                ("Total Complaints", lambda m: satisfaction["call_months"][m]["total"], satisfaction["call_totals"]["total"]),
                ("Result (%)", lambda m: satisfaction["call_months"][m]["rate"], satisfaction["call_totals"]["rate"], True),
            ],
        },
        {
            "kpi_label": "KPI #3",
            "header": "Response Rate - Survey",
            "rows": [
                ("Total Responses", lambda m: satisfaction["survey_months"][m]["responses"], satisfaction["survey_totals"]["responses"]),
                ("Total Complaints", lambda m: satisfaction["survey_months"][m]["total"], satisfaction["survey_totals"]["total"]),
                ("Result (%)", lambda m: satisfaction["survey_months"][m]["rate"], satisfaction["survey_totals"]["rate"], True),
            ],
        },
        {
            "kpi_label": "KPI #3",
            "header": "MOH KPI",
            "rows": [
                ("Complaints which received a (satisfied) scoring on its resolution", lambda m: moh_kpi["moh"]["months"][m]["satisfied_scoring"], moh_kpi["moh"]["totals"]["satisfied_scoring"]),
                ("Total number of complaints received", lambda m: moh_kpi["moh"]["months"][m]["total"], moh_kpi["moh"]["totals"]["total"]),
                ("Result (%)", lambda m: moh_kpi["moh"]["months"][m]["rate"], moh_kpi["moh"]["totals"]["rate"], True),
            ],
        },
        {
            "kpi_label": "KPI #3",
            "header": "MOH KPI - Real Numbers",
            "rows": [
                ("Complaints which received a (satisfied) scoring on its resolution", lambda m: moh_kpi["real"]["months"][m]["satisfied_scoring"], moh_kpi["real"]["totals"]["satisfied_scoring"]),
                ("Total number of complaints received", lambda m: moh_kpi["real"]["months"][m]["total"], moh_kpi["real"]["totals"]["total"]),
                ("Result (%)", lambda m: moh_kpi["real"]["months"][m]["rate"], moh_kpi["real"]["totals"]["rate"], True),
            ],
        },
        {
            "kpi_label": "KPI #4",
            "header": "Numerator/Denominator",
            "rows": [
                ("Total Responses Within 48 Hours", lambda m: kpi["months"][m]["resolved_48h"], kpi["totals"]["resolved_48h"]),
                ("Total Number of Complaints", lambda m: kpi["months"][m]["total"], kpi["totals"]["total"]),
                ("Result (%)", lambda m: kpi["months"][m]["resolved_48h_rate"], kpi["totals"]["resolved_48h_rate"], True),
            ],
        },
    ]

    ws = wb.create_sheet("1. KPI")
    row = 1

    for block in blocks:
        _style_header(ws, row, 1, block["header"])
        for i, ml in enumerate(months):
            _style_header(ws, row, 2 + i, ml)
        _style_header(ws, row, total_col, "Total")
        ws.cell(row=row, column=total_col + 2, value=block["kpi_label"]).font = BOLD_FONT
        ws.merge_cells(start_row=row, start_column=total_col + 2, end_row=row + 2, end_column=total_col + 2)

        for row_data in block["rows"]:
            row += 1
            is_pct = len(row_data) > 3 and row_data[3]
            _kpi_row(ws, row, row_data[0], row_data[1], row_data[2], is_pct)

        row += 2

    for c in range(1, total_col + 3):
        ws.column_dimensions[get_column_letter(c)].width = 16


def _write_source_table_sheet(wb, service):
    source = service.get_source_breakdown()
    source_totals = service.get_source_totals()
    location = service.get_location_breakdown()
    dept_type = service.get_dept_type_breakdown()
    dept_type_monthly = service.get_dept_type_monthly_table()
    monthly_summary = service.get_monthly_summary()
    location_ratios = service.get_location_ratios()
    quarterly_volumes = service.get_quarterly_volumes()
    active_months = service.active_months
    months = [calendar.month_abbr[m].upper() for m in active_months]

    ws = wb.create_sheet("2. 1st Table")

    def _month_col(idx):
        return 3 * idx + 5

    def _write_month_header(ws, row):
        _style_header(ws, row, 1, "%")
        _style_header(ws, row, 2, "Number")
        for i, m in enumerate(active_months):
            c = _month_col(i)
            _style_header(ws, row, c, months[i])

    def _write_subrow(ws, row, label, annual_total, get_monthly_count, get_monthly_pct):
        _style_data(ws, row, 4, annual_total)
        for i, m in enumerate(active_months):
            c = _month_col(i)
            _style_data(ws, row, c, label)
            cnt = get_monthly_count(m)
            if cnt:
                _style_data(ws, row, c + 1, cnt)
                pct = get_monthly_pct(m)
                if pct:
                    _style_data(ws, row, c + 2, pct, PCT_FMT)

    def _write_toprow(ws, row, pct, count, label, sub_label, sub_annual, get_sub_count, get_sub_pct):
        _style_data(ws, row, 1, pct, PCT_FMT)
        _style_data(ws, row, 2, count)
        _style_data(ws, row, 3, label)
        _write_subrow(ws, row, sub_label, sub_annual, get_sub_count, get_sub_pct)

    def _write_total_row(ws, row, total_all, monthly_totals):
        _style_data(ws, row, 1, "%")
        _style_data(ws, row, 2, "Number")
        _style_bold(ws, row, 4, total_all)
        for i, m in enumerate(active_months):
            c = _month_col(i)
            _style_data(ws, row, c, "Total")
            _style_data(ws, row, c + 1, monthly_totals[m])
            _style_data(ws, row, c + 2, 1, PCT_FMT)

    row = 1
    _write_month_header(ws, row)

    total_all = sum(source[m]["total"] for m in active_months)
    ext_total = sum(source[m]["external"] for m in active_months)
    int_total = sum(source[m]["internal"] for m in active_months)

    row = 2
    ins_total = sum(source[m]["insurance"] for m in active_months)
    _write_toprow(ws, row,
        round(ext_total / total_all, 3) if total_all else 0, ext_total, "External",
        "Insurance company", ins_total,
        lambda m: source[m]["insurance"],
        lambda m: round(source[m]["insurance"] / source[m]["total"], 3) if source[m]["total"] else 0)

    row = 3
    moh_total = sum(source[m]["moh"] for m in active_months)
    _write_subrow(ws, row, "MOH", moh_total,
        lambda m: source[m]["moh"],
        lambda m: round(source[m]["moh"] / source[m]["total"], 3) if source[m]["total"] else 0)

    row = 4
    chi_total = sum(source[m]["chi"] for m in active_months)
    _write_subrow(ws, row, "CHI", chi_total,
        lambda m: source[m]["chi"],
        lambda m: round(source[m]["chi"] / source[m]["total"], 3) if source[m]["total"] else 0)

    row = 5
    pat_total = sum(source[m]["patients"] for m in active_months)
    _write_toprow(ws, row,
        round(int_total / total_all, 3) if total_all else 0, int_total, "Internal",
        "Patients", pat_total,
        lambda m: source[m]["patients"],
        lambda m: round(source[m]["patients"] / source[m]["total"], 3) if source[m]["total"] else 0)

    row = 6
    rel_total = sum(source[m]["relatives"] for m in active_months)
    _write_subrow(ws, row, "Patient's relatives", rel_total,
        lambda m: source[m]["relatives"],
        lambda m: round(source[m]["relatives"] / source[m]["total"], 3) if source[m]["total"] else 0)

    row = 7
    _write_total_row(ws, row, total_all, {m: source[m]["total"] for m in active_months})

    row = 9
    for label, key in [("In-Patient", "ip"), ("Out-Patient", "op"), ("ER", "er")]:
        total_v = sum(location[m][key] for m in active_months)
        _style_data(ws, row, 1, round(total_v / total_all, 3) if total_all else 0, PCT_FMT)
        _style_data(ws, row, 2, total_v)
        _style_data(ws, row, 3, label)
        _style_data(ws, row, 4, total_v)
        for i, m in enumerate(active_months):
            c = _month_col(i)
            _style_data(ws, row, c, label)
            cnt = location[m][key]
            if cnt:
                _style_data(ws, row, c + 1, cnt)
                _style_data(ws, row, c + 2, round(cnt / source[m]["total"], 3) if source[m]["total"] else 0, PCT_FMT)
        row += 1

    _write_total_row(ws, row, total_all, {m: source[m]["total"] for m in active_months})

    row += 2
    for label, key in [("Medical", "medical"), ("Admin", "admin"), ("Nursing", "nursing"), ("Support Services", "support")]:
        total_v = sum(dept_type[m][key] for m in active_months)
        _style_data(ws, row, 1, round(total_v / total_all, 3) if total_all else 0, PCT_FMT)
        _style_data(ws, row, 2, total_v)
        _style_data(ws, row, 3, label)
        _style_data(ws, row, 4, total_v)
        for i, m in enumerate(active_months):
            c = _month_col(i)
            _style_data(ws, row, c, label)
            cnt = dept_type[m][key]
            if cnt:
                _style_data(ws, row, c + 1, cnt)
                _style_data(ws, row, c + 2, round(cnt / dept_type[m]["total"], 3) if dept_type[m]["total"] else 0, PCT_FMT)
        row += 1

    _write_total_row(ws, row, total_all, {m: source[m]["total"] for m in active_months})

    row += 2
    summary_headers = ["Month", "MOH Complaints", "CHI", "Insurance Company", "Internal", "Total Complaints", "MOH Percentage", "CCHI Percentage"]
    for i, h in enumerate(summary_headers):
        _style_header(ws, row, 5 + i, h)

    for ms in monthly_summary:
        row += 1
        _style_data(ws, row, 5, ms["month"])
        _style_data(ws, row, 6, ms["moh"])
        _style_data(ws, row, 7, ms["chi"])
        _style_data(ws, row, 8, ms["insurance"])
        _style_data(ws, row, 9, ms["internal"])
        _style_data(ws, row, 10, ms["total"])
        _style_data(ws, row, 11, ms["moh_pct"], PCT_FMT)
        _style_data(ws, row, 12, ms["chi_pct"], PCT_FMT)

    row += 2
    source_total_items = [
        ("Internal Complaints", source_totals["internal"]["count"], "% Internal", source_totals["internal"]["pct"]),
        ("External Complaints", source_totals["external"]["count"], "% External", source_totals["external"]["pct"]),
        ("MOH", source_totals["moh"]["count"], "% MOH", source_totals["moh"]["pct"]),
        ("CHI", source_totals["chi"]["count"], "% CHI", source_totals["chi"]["pct"]),
        ("Insurance Company", source_totals["insurance"]["count"], "% Insurance Comp.", source_totals["insurance"]["pct"]),
    ]
    for label, count, pct_label, pct in source_total_items:
        _style_bold(ws, row, 6, label)
        _style_data(ws, row, 7, count)
        _style_bold(ws, row, 9, pct_label)
        _style_data(ws, row, 10, pct, PCT_FMT)
        row += 1

    row += 1
    for area, title, comp_col, pat_col in [
        ("ip", "In-Patient Complaints vs Patients", "In-Patient Complaints", "Total In-Patient "),
        ("op", "Out-Patient Complaints vs Patients", "Out-Patient Complaint", "Total Out-Patient"),
        ("er", "ER Complaints vs Patients", "ER Complaints", "Total ER Patients"),
    ]:
        headers = ["Month", comp_col, pat_col, "Persentage"]
        for i, h in enumerate(headers):
            _style_header(ws, row, 5 + i, h)
        for i, m in enumerate(active_months):
            row += 1
            d = location_ratios["months"][m]
            _style_data(ws, row, 5, months[i])
            _style_data(ws, row, 6, d[f"{area}_complaints"])
            _style_data(ws, row, 7, d[f"{area}_patients"])
            _style_data(ws, row, 8, d[f"{area}_ratio"], PCT_FMT)
        row += 1
        t = location_ratios["totals"][area]
        _style_bold(ws, row, 5, "TOTAL")
        _style_bold(ws, row, 6, t["complaints"])
        _style_bold(ws, row, 7, t["patients"])
        _style_bold(ws, row, 8, t["ratio"], PCT_FMT)
        row += 1

    row += 1
    summary_ratio_data = [
        ("In-Patient Complaints", location_ratios["totals"]["ip"]["complaints"],
         "Total In-Patient ", location_ratios["totals"]["ip"]["patients"],
         "Ratio", location_ratios["totals"]["ip"]["ratio"]),
        ("Out-Patient Complaint", location_ratios["totals"]["op"]["complaints"],
         "Total Out-Patient", location_ratios["totals"]["op"]["patients"],
         "Ratio", location_ratios["totals"]["op"]["ratio"]),
        ("ER Complaints", location_ratios["totals"]["er"]["complaints"],
         "Total ER Patients", location_ratios["totals"]["er"]["patients"],
         "Ratio", location_ratios["totals"]["er"]["ratio"]),
    ]
    for comp_label, comp_count, pat_label, pat_count, ratio_label, ratio_val in summary_ratio_data:
        _style_bold(ws, row, 10, comp_label)
        _style_data(ws, row, 11, comp_count)
        _style_bold(ws, row, 12, ratio_val, PCT_FMT)
        row += 1

    row += 1
    for q_num in ["1", "2", "3", "4"]:
        q_data = quarterly_volumes.get(q_num, {})
        q_months = q_data.get("months", {})
        _style_header(ws, row, 10, "Area/Unit")
        for i, m_num in enumerate(sorted(q_months.keys())):
            _style_header(ws, row, 11 + i, months[m_num - 1])
        _style_header(ws, row, 14, "Total")

        row += 1
        for area_label, area_key in [("OPD", "OPD"), ("ER", "ER"), ("IP", "IP")]:
            _style_data(ws, row, 10, area_label)
            q_total = 0
            for i, m_num in enumerate(sorted(q_months.keys())):
                val = q_months[m_num].get(area_key, 0)
                _style_data(ws, row, 11 + i, val)
                q_total += val
            _style_data(ws, row, 14, q_total)
            row += 1

    row += 1
    dept_headers = ["Month", "Medical", "Admin", "Nursing", "Support Services", "Total Complaints"]
    for i, h in enumerate(dept_headers):
        _style_header(ws, row, 5 + i, h)

    for mr in dept_type_monthly["months"]:
        row += 1
        _style_data(ws, row, 5, mr["month"])
        _style_data(ws, row, 6, mr["medical"])
        _style_data(ws, row, 7, mr["admin"])
        _style_data(ws, row, 8, mr["nursing"])
        _style_data(ws, row, 9, mr["support"])
        _style_data(ws, row, 10, mr["total"])

    row += 1
    _style_bold(ws, row, 5, "TOTAL")
    _style_bold(ws, row, 6, dept_type_monthly["totals"]["medical"])
    _style_bold(ws, row, 7, dept_type_monthly["totals"]["admin"])
    _style_bold(ws, row, 8, dept_type_monthly["totals"]["nursing"])
    _style_bold(ws, row, 9, dept_type_monthly["totals"]["support"])
    _style_bold(ws, row, 10, dept_type_monthly["totals"]["total"])

    row += 1
    _style_bold(ws, row, 5, "% From total")
    _style_bold(ws, row, 6, dept_type_monthly["percentages"]["medical"], PCT_FMT)
    _style_bold(ws, row, 7, dept_type_monthly["percentages"]["admin"], PCT_FMT)
    _style_bold(ws, row, 8, dept_type_monthly["percentages"]["nursing"], PCT_FMT)
    _style_bold(ws, row, 9, dept_type_monthly["percentages"]["support"], PCT_FMT)
    _style_bold(ws, row, 10, dept_type_monthly["percentages"]["total"], PCT_FMT)

    row += 2
    for i, h in enumerate(["Medical", "Admin", "Nursing", "Support Services", "Total Complaints"]):
        _style_header(ws, row, 5 + i, h)
    row += 1
    _style_data(ws, row, 5, "Persentage")
    _style_data(ws, row, 6, dept_type_monthly["percentages"]["medical"], PCT_FMT)
    _style_data(ws, row, 7, dept_type_monthly["percentages"]["admin"], PCT_FMT)
    _style_data(ws, row, 8, dept_type_monthly["percentages"]["nursing"], PCT_FMT)
    _style_data(ws, row, 9, dept_type_monthly["percentages"]["support"], PCT_FMT)
    _style_data(ws, row, 10, dept_type_monthly["percentages"]["total"], PCT_FMT)
    row += 1
    _style_bold(ws, row, 5, "TOTAL")
    _style_bold(ws, row, 6, dept_type_monthly["totals"]["medical"])
    _style_bold(ws, row, 7, dept_type_monthly["totals"]["admin"])
    _style_bold(ws, row, 8, dept_type_monthly["totals"]["nursing"])
    _style_bold(ws, row, 9, dept_type_monthly["totals"]["support"])
    _style_bold(ws, row, 10, dept_type_monthly["totals"]["total"])

    for c in range(1, 42):
        ws.column_dimensions[get_column_letter(c)].width = 14
    ws.column_dimensions["C"].width = 22


def _write_escalated_sheet(wb, service):
    escalated = service.get_escalated_breakdown()
    by_cat = escalated["by_category"]
    by_source = escalated.get("by_source", {})

    ws = wb.create_sheet("3. Escalated Complaints")

    categories = [
        ("Medical", "medical"),
        ("Non-Medical", "non_medical"),
        ("Nursing", "nursing"),
        ("Support Services", "support"),
    ]

    cat_headers = []
    for cat_label, cat_key in categories:
        cat_headers.append((cat_label, "Sub-department"))
        cat_headers.append((cat_label, "Escalated"))

    num_cols = len(cat_headers)
    for i in range(num_cols):
        _style_header(ws, 1, i + 1, cat_headers[i][1])
    for i in range(0, num_cols, 2):
        ws.cell(row=1, column=i + 1).value = cat_headers[i][0]
        ws.merge_cells(start_row=1, start_column=i + 1, end_row=1, end_column=i + 2)
        ws.cell(row=1, column=i + 1).font = HEADER_FONT
        ws.cell(row=1, column=i + 1).fill = BLUE_FILL
        ws.cell(row=1, column=i + 1).alignment = CENTER

    _style_header(ws, 2, 1, "Sub-department")
    _style_header(ws, 2, 2, "Escalated")
    for i in range(2, num_cols, 2):
        _style_header(ws, 2, i + 1, "Sub-department")
        _style_header(ws, 2, i + 2, "Escalated")

    max_rows = max(
        len(by_cat.get(cat_key, {})) for _, cat_key in categories
    )

    cat_totals = {}
    for cat_label, cat_key in categories:
        depts = by_cat.get(cat_key, {})
        cat_totals[cat_key] = {
            "total_escalated": sum(depts.values()),
            "total_complaints": 0,
        }
        sorted_depts = sorted(depts.items(), key=lambda x: -x[1])
        col_start = categories.index((cat_label, cat_key)) * 2 + 1
        for r, (dept_name, count) in enumerate(sorted_depts):
            _style_data(ws, 3 + r, col_start, dept_name)
            _style_data(ws, 3 + r, col_start + 1, count)
        total_row = 3 + max_rows
        _style_bold(ws, total_row, col_start, "Total")
        _style_bold(ws, total_row, col_start + 1, sum(depts.values()))

        total_complaints_row = total_row + 1
        _style_bold(ws, total_complaints_row, col_start, "Total Complaints")
        per_dept = service.get_per_department_breakdown()
        dept_names = [d["name"] for d in per_dept["categories"].get(cat_key, [])]
        tc = sum(d["total"] for d in per_dept["categories"].get(cat_key, []))
        _style_bold(ws, total_complaints_row, col_start + 1, tc)

        pct_row = total_complaints_row + 1
        esc_count = sum(depts.values())
        _style_bold(ws, pct_row, col_start, "% Escalated Complaints")
        _style_bold(ws, pct_row, col_start + 1, round(esc_count / tc, 3) if tc > 0 else 0, PCT_FMT)

    summary_row = 3 + max_rows + 4
    ws.cell(row=summary_row, column=1, value="Total number of Escalated Complaints").font = BOLD_FONT
    row = summary_row + 1
    for label, key in [("Internal Complaints", "internal"), ("MOH", "moh"), ("CHI", "chi")]:
        _style_bold(ws, row, 1, label)
        _style_data(ws, row, 2, by_source.get(key, 0))
        row += 1
    _style_bold(ws, row, 1, "Total")
    _style_data(ws, row, 2, sum(by_source.values()))

    for c in range(1, num_cols + 1):
        ws.column_dimensions[get_column_letter(c)].width = 30 if c % 2 == 1 else 12


def _write_per_department_sheet(wb, service):
    per_dept = service.get_per_department_breakdown()
    categories = per_dept["categories"]
    source_totals = per_dept["source_totals"]

    ws = wb.create_sheet("4. Each dep. complaints")

    cat_configs = [
        ("Medical", "medical", 2),
        ("Non-Medical", "non_medical", 12),
        ("Nursing", "nursing", 20),
        ("Support Services", "support", 28),
    ]

    for cat_label, cat_key, col_start in cat_configs:
        _style_header(ws, 1, col_start, cat_label)
        ws.merge_cells(start_row=1, start_column=col_start, end_row=1, end_column=col_start + 7)

        sub_headers = ["Sub-dept", "MOH", "CHI", "Insurance", "Internal", "Total", "Escalated", "Response Rate (Days)"]
        for i, h in enumerate(sub_headers):
            _style_header(ws, 2, col_start + i, h)

        depts = categories.get(cat_key, [])
        for r, d in enumerate(depts):
            row = 3 + r
            _style_data(ws, row, col_start, d["name"])
            _style_data(ws, row, col_start + 1, d["moh"])
            _style_data(ws, row, col_start + 2, d["chi"])
            _style_data(ws, row, col_start + 3, d["insurance"])
            _style_data(ws, row, col_start + 4, d["internal"])
            _style_data(ws, row, col_start + 5, d["total"])
            _style_data(ws, row, col_start + 6, d["escalated"])
            _style_data(ws, row, col_start + 7, d["avg_response_days"], NUM_FMT)

        total_row = 3 + len(depts)
        _style_bold(ws, total_row, col_start, "Total")
        _style_bold(ws, total_row, col_start + 1, sum(d["moh"] for d in depts))
        _style_bold(ws, total_row, col_start + 2, sum(d["chi"] for d in depts))
        _style_bold(ws, total_row, col_start + 3, sum(d["insurance"] for d in depts))
        _style_bold(ws, total_row, col_start + 4, sum(d["internal"] for d in depts))
        _style_bold(ws, total_row, col_start + 5, sum(d["total"] for d in depts))
        _style_bold(ws, total_row, col_start + 6, sum(d["escalated"] for d in depts))

    summary_row = 3 + max(len(categories.get(ck, [])) for _, ck, _ in cat_configs) + 3

    source_items = [
        ("MOH", source_totals["moh"]),
        ("CHI", source_totals["chi"]),
        ("Internal", source_totals["internal"]),
        ("Insurance Co.", source_totals["insurance"]),
        ("Total", source_totals["total"]),
    ]
    for label, val in source_items:
        _style_bold(ws, summary_row, 2, label)
        _style_data(ws, summary_row, 3, val)
        summary_row += 1

    avg_row = summary_row + 1
    _style_bold(ws, avg_row, 2, "Average Response Rate")
    avg_row += 1
    avg_resp = per_dept.get("avg_response_days", {})
    for cat_label, cat_key, _ in cat_configs:
        _style_data(ws, avg_row, 2, cat_label)
        _style_data(ws, avg_row, 3, avg_resp.get(cat_key, 0), NUM_FMT)
        avg_row += 1

    for c in range(1, 40):
        ws.column_dimensions[get_column_letter(c)].width = 12
    for _, _, cs in cat_configs:
        ws.column_dimensions[get_column_letter(cs)].width = 30


def _write_response_rate_sheet(wb, title, rate_data):
    ws = wb.create_sheet(title)
    _style_header(ws, 1, 1, "Timeline")
    _style_header(ws, 1, 2, "Count")
    _style_header(ws, 1, 3, "Rate")

    buckets = ["24h", "48h", "72h", "72h+"]
    for i, b in enumerate(buckets):
        row = 2 + i
        _style_data(ws, row, 1, b)
        _style_data(ws, row, 2, rate_data["counts"].get(b, 0))
        _style_data(ws, row, 3, rate_data["rates"].get(b, 0), PCT_FMT)

    row = 6
    _style_bold(ws, row, 1, "Total")
    _style_data(ws, row, 2, rate_data["total"])

    individual = rate_data.get("individual", [])
    if individual:
        row += 2
        _style_header(ws, row, 1, "Reference")
        _style_header(ws, row, 2, "Department")
        _style_header(ws, row, 3, "Hours")
        _style_header(ws, row, 4, "Bucket")
        _style_header(ws, row, 5, "Status")
        for i, item in enumerate(individual):
            r = row + 1 + i
            _style_data(ws, r, 1, item["ref"])
            _style_data(ws, r, 2, item["dept"])
            _style_data(ws, r, 3, item["hours"], NUM_FMT)
            _style_data(ws, r, 4, item["bucket"])
            _style_data(ws, r, 5, item["status"])

    chart = PieChart()
    chart.title = f"{title}"
    data_ref = Reference(ws, min_col=2, min_row=1, max_row=5)
    cats_ref = Reference(ws, min_col=1, min_row=2, max_row=5)
    chart.add_data(data_ref, titles_from_data=True)
    chart.set_categories(cats_ref)
    ws.add_chart(chart, "G2")

    ws.column_dimensions["A"].width = 18
    ws.column_dimensions["B"].width = 30
    ws.column_dimensions["C"].width = 12
    ws.column_dimensions["D"].width = 14
    ws.column_dimensions["E"].width = 12


def _write_per_dept_response_rate_sheets(wb, service):
    dept_times = service.get_per_dept_response_times()

    sheet_configs = [
        ("8.1 Response Rate Medical", ["medical"]),
        ("8.2 Response Rate Non-Medical", ["non_medical", "admin"]),
        ("8.3 RR Nursing&Support", ["nursing", "support"]),
    ]

    for sheet_title, domain_types in sheet_configs:
        ws = wb.create_sheet(sheet_title)

        all_depts = {}
        for dt in domain_types:
            all_depts.update(dept_times.get(dt, {}))

        if not all_depts:
            continue

        dept_names = sorted(all_depts.keys())
        col = 2
        for dept_name in dept_names:
            cell = ws.cell(row=1, column=col, value=dept_name)
            cell.font = HEADER_FONT
            cell.fill = BLUE_FILL
            cell.alignment = CENTER
            cell.border = BORDER_ALL
            ws.merge_cells(start_row=1, start_column=col, end_row=1, end_column=col)

            cell2 = ws.cell(row=2, column=col)
            cell2.value = "Time between processing and sending"
            cell2.font = Font(name="Calibri", size=9, italic=True)
            cell2.alignment = CENTER
            cell2.border = BORDER_ALL

            times = all_depts[dept_name]
            for i, t in enumerate(times):
                r = 3 + i
                if t.get("status") == "Escalated":
                    _style_data(ws, r, col, "Escalated")
                elif t.get("status") == "Closed":
                    _style_data(ws, r, col, "Closed")
                else:
                    hours = t["hours"]
                    days = int(hours // 24)
                    remaining_hours = int(hours % 24)
                    td_str = f"{days}d {remaining_hours}h" if days > 0 else f"{int(hours)}h"
                    _style_data(ws, r, col, td_str)

            col += 1

        ws.column_dimensions["A"].width = 5
        for c in range(2, col):
            ws.column_dimensions[get_column_letter(c)].width = 22


def generate_complaint_quarterly_excel(service):
    wb = Workbook()
    wb.remove(wb.active)

    _write_all_quarters_sheet(wb, service)
    _write_kpi_sheet(wb, service)
    _write_source_table_sheet(wb, service)
    _write_escalated_sheet(wb, service)
    _write_per_department_sheet(wb, service)

    response_rates = service.get_response_rates()
    _write_response_rate_sheet(wb, "5. Internal Response Rate", response_rates["internal"])
    _write_response_rate_sheet(wb, "6. MOH Response Rate", response_rates["moh"])
    _write_response_rate_sheet(wb, "7. CHI Response Rate", response_rates["chi"])

    _write_per_dept_response_rate_sheets(wb, service)

    buf = BytesIO()
    wb.save(buf)
    buf.seek(0)
    return buf