import calendar
from io import BytesIO

from openpyxl import Workbook
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")
HEADER_FONT = Font(name="Calibri", size=11, bold=True, color="FFFFFF")
DATA_FONT = Font(name="Calibri", size=10)
HEADER_ALIGN = Alignment(horizontal="center", vertical="center", wrap_text=True)
DATA_ALIGN = Alignment(horizontal="center", vertical="center", wrap_text=True)
RTL_ALIGN = Alignment(horizontal="right", vertical="center", wrap_text=True)
THIN = Side(style="thin")
THIN_BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)

VERT_MERGE_HEADERS = [
    (2, "No."),
    (3, "رقم الشكوى"),
    (4, "رقم الملف"),
    (5, "جهة الشكوى"),
    (6, "الموقع"),
    (7, "القسم الرئيس"),
    (8, "القسم الفرعي"),
    (9, "تاريخ إستلام الشكوى"),
    (10, "المدخل"),
    (14, "رقم الجوال"),
    (15, "Time line"),
    (18, "Employee"),
    (23, "Employee"),
    (26, "Employee"),
    (27, "الوقت بين التحرير والارسال"),
    (30, "Employee"),
    (31, "الوقت بين اول ايميل والارسال"),
    (34, "Employee"),
    (35, "الوقت بين ثاني ايميل والاول"),
    (38, "Employee"),
    (39, "الوقت بين التصعيد والارسال"),
    (42, "Employee"),
    (43, "الوقت بين الاغلاق والارسال"),
    (46, "Employee"),
    (47, "الوقت بين المعالجة و الارسال"),
    (50, "الوقت بين الرد والارسال"),
    (51, "اسم الشخص المشتكى عليه"),
    (52, "موضوع الشكوى الأساسية"),
    (53, "الشكوى باختصار (عربي)"),
    (54, "الشكوى باختصار English"),
    (55, "توثيق تذكيرات للقسم"),
    (56, "تاريخ التذكير"),
    (57, "سبب تأخير القسم بالرد"),
    (58, "سبب تأخير اغلاق الشكوى خلال 72 ساعه"),
    (59, "الشخص المتأخر"),
    (60, "Satisfied/Dissatisfied"),
    (61, "الاجراء المتخذ من قبل القسم المعني"),
    (62, "نتيجة الاجراء المتخذ بعد التحقيق"),
    (63, "حلول واقتراحات"),
    (64, "Recommendation/Action plan"),
    (65, "Responsible Department"),
    (66, "The Rightful Side"),
    (67, "PR - Observations / Patient Supervisor"),
]

HORIZ_MERGE_HEADERS = [
    (11, 13, "MOH & CHI"),
    (16, 17, "إرسال نموذج الشكوى"),
    (19, 20, "تحرير الشكوى"),
    (21, 22, "تفعيل الشكوى"),
    (24, 25, "تم ارسال الشكوى"),
    (28, 29, "First Reminder Sent"),
    (32, 33, "Second Reminder Sent"),
    (36, 37, "Escalated"),
    (40, 41, "Closed"),
    (44, 45, "Resolved"),
    (48, 49, "تاريخ الرد"),
]

DATE_TIME_SUBS = {
    11: ("Date", "Time"),
    16: ("Date", "Time"),
    19: ("Date", "Time"),
    21: ("Date", "Time"),
    24: ("Date", "Time"),
    28: ("Date", "Time"),
    32: ("Date", "Time"),
    36: ("Date", "Time"),
    40: ("Date", "Time"),
    44: ("Date", "Time"),
    48: ("Date", "Time"),
}


def _dt(v):
    return v.date() if v else ""


def _tm(v):
    return v.strftime("%H:%M") if v else ""


def _write_headers(ws):
    cell = ws.cell(row=1, column=1, value="Week")
    cell.font = HEADER_FONT
    cell.fill = BLUE_FILL
    cell.alignment = HEADER_ALIGN
    cell.border = THIN_BORDER
    ws.merge_cells(start_row=1, start_column=1, end_row=2, end_column=1)

    for col, label in VERT_MERGE_HEADERS:
        cell = ws.cell(row=1, column=col, value=label)
        cell.font = HEADER_FONT
        cell.fill = BLUE_FILL
        cell.alignment = HEADER_ALIGN
        cell.border = THIN_BORDER
        ws.merge_cells(start_row=1, start_column=col, end_row=2, end_column=col)
        bc = ws.cell(row=2, column=col)
        bc.font = HEADER_FONT
        bc.fill = BLUE_FILL
        bc.border = THIN_BORDER

    for start_col, end_col, label in HORIZ_MERGE_HEADERS:
        cell = ws.cell(row=1, column=start_col, value=label)
        cell.font = HEADER_FONT
        cell.fill = BLUE_FILL
        cell.alignment = HEADER_ALIGN
        cell.border = THIN_BORDER
        ws.merge_cells(start_row=1, start_column=start_col, end_row=1, end_column=end_col)
        for c in range(start_col + 1, end_col + 1):
            bc = ws.cell(row=1, column=c)
            bc.font = HEADER_FONT
            bc.fill = BLUE_FILL
            bc.border = THIN_BORDER

        date_l, time_l = DATE_TIME_SUBS.get(start_col, ("Date", "Time"))
        dc = ws.cell(row=2, column=start_col, value=date_l)
        dc.font = HEADER_FONT
        dc.fill = BLUE_FILL
        dc.alignment = HEADER_ALIGN
        dc.border = THIN_BORDER
        tc = ws.cell(row=2, column=end_col, value=time_l)
        tc.font = HEADER_FONT
        tc.fill = BLUE_FILL
        tc.alignment = HEADER_ALIGN
        tc.border = THIN_BORDER


def _write_data_rows(ws, queryset):
    complaints = list(queryset.order_by("created_at"))
    if not complaints:
        return

    weeks = {}
    for idx, c in enumerate(complaints, 1):
        day = c.created_at.day
        if day <= 7:
            wk = 1
        elif day <= 15:
            wk = 2
        elif day <= 22:
            wk = 3
        elif day <= 28:
            wk = 4
        else:
            wk = 5
        weeks[idx] = wk

    row_num = 3
    current_week = None
    week_start_row = None

    for idx, c in enumerate(complaints, 1):
        wk = weeks[idx]

        if wk != current_week:
            if current_week is not None and week_start_row is not None and row_num - 1 > week_start_row:
                ws.merge_cells(start_row=week_start_row, start_column=1, end_row=row_num - 1, end_column=1)
            cell = ws.cell(row=row_num, column=1, value=f"Wk {wk}")
            cell.font = DATA_FONT
            cell.alignment = Alignment(horizontal="center", vertical="center")
            cell.border = THIN_BORDER
            current_week = wk
            week_start_row = row_num

        inv_dept = None
        for id_obj in c.involved_departments.all():
            if id_obj.is_primary:
                inv_dept = id_obj
                break
        if inv_dept is None and c.involved_departments.exists():
            inv_dept = c.involved_departments.first()

        satisfaction = ""
        if c.satisfaction:
            satisfaction = c.satisfaction.replace("_", " ").title()

        resolution_outcome = ""
        if c.resolution_outcome:
            resolution_outcome = c.resolution_outcome.replace("_", " ").title()

        vals = [
            idx,
            c.reference_number or "",
            c.file_number or "",
            c.source.name_en if c.source else (c.complaint_source_type or ""),
            c.location.name if c.location else "",
            c.domain.name_en if c.domain else "",
            c.category.name_en if c.category else "",
            c.created_at,
            c.created_by.get_full_name() if c.created_by else "",
            c.moh_reference_date, "", "",
            c.contact_phone or "",
            c.due_at,
            _dt(c.form_sent_at), _tm(c.form_sent_at),
            c.created_by.get_full_name() if c.created_by else "",
            _dt(c.created_at), _tm(c.created_at),
            _dt(c.activated_at), _tm(c.activated_at),
            c.assigned_to.get_full_name() if c.assigned_to else "",
            _dt(c.forwarded_to_dept_at), _tm(c.forwarded_to_dept_at),
            c.assigned_to.get_full_name() if c.assigned_to else "",
            "",
            _dt(c.reminder_sent_at), _tm(c.reminder_sent_at),
            "",
            "",
            _dt(c.second_reminder_sent_at), _tm(c.second_reminder_sent_at),
            "",
            "",
            _dt(c.escalated_at), _tm(c.escalated_at),
            "",
            "",
            _dt(c.closed_at), _tm(c.closed_at),
            c.closed_by.get_full_name() if c.closed_by else "",
            "",
            _dt(c.resolved_at), _tm(c.resolved_at),
            c.resolved_by.get_full_name() if c.resolved_by else "",
            "",
            _dt(c.response_date) if c.response_date else "", "",
            "",
            c.staff_name or "",
            c.complaint_subject or c.title or "",
            c.ai_brief_ar or "",
            c.ai_brief_en or "",
            inv_dept.response_notes if inv_dept else "",
            inv_dept.first_reminder_sent_at if inv_dept else "",
            inv_dept.delay_reason if inv_dept else c.explanation_delay_reason or "",
            c.delay_reason_closure or "",
            inv_dept.delayed_person if inv_dept else "",
            satisfaction,
            c.action_taken_by_dept or "",
            c.action_result or "",
            c.recommendation_action_plan or "",
            c.recommendation_action_plan or "",
            c.department.name if c.department else "",
            resolution_outcome,
            "",
        ]

        for col, val in enumerate(vals, 2):
            cell = ws.cell(row=row_num, column=col, value=val)
            cell.font = DATA_FONT
            cell.alignment = RTL_ALIGN if col in (53, 54, 61, 62, 63, 64) else DATA_ALIGN
            cell.border = THIN_BORDER

        row_num += 1

    if week_start_row and row_num - 1 > week_start_row:
        ws.merge_cells(start_row=week_start_row, start_column=1, end_row=row_num - 1, end_column=1)


def _set_col_widths(ws):
    ws.column_dimensions[get_column_letter(1)].width = 8
    ws.column_dimensions[get_column_letter(2)].width = 5
    for c in range(3, 68):
        w = 14
        if c in (9,):
            w = 16
        elif c in (52, 53, 54, 61, 62, 63, 64):
            w = 35
        elif c in (13, 15):
            w = 14
        ws.column_dimensions[get_column_letter(c)].width = w
    ws.freeze_panes = "C3"


def generate_complaint_monthly_excel(service):
    queryset = service._base_qs().prefetch_related(
        "involved_departments__department",
    )
    month_label = f"{service.year}-{service.month:02d}"
    month_abbr = calendar.month_abbr[service.month].upper()

    wb = Workbook()
    ws = wb.active
    ws.title = month_abbr

    _write_headers(ws)
    _write_data_rows(ws, queryset)
    _set_col_widths(ws)

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