from io import BytesIO

from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter

from .observation_report import SOURCE_MAP

BLUE_FILL = PatternFill(start_color="2F75B5", end_color="2F75B5", fill_type="solid")
HEADER_FONT = Font(name="Calibri", size=11, bold=True, color="FFFFFF")
HEADER_ALIGN = Alignment(horizontal="center", vertical="center", wrap_text=True)
DATA_FONT = Font(name="Calibri", size=11)
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)

ROW1_AR = [
    ("A1", "F1", "Portal "),
    ("G1", "L1", "Portal "),
    ("M1", "M1", "اسم الشخص الذي تم تبليغه"),
    ("N1", "N1", "القسم الذي تم تبليغه"),
    ("O1", "O1", "وسيلة التواصل"),
    ("P1", "P1", "تاريخ التواصل"),
    ("Q1", "Q1", "وقت التواصل"),
    ("R1", "R1", "خطة العمل او الاجراء المتخذ في الوقت الراهن "),
    ("S1", "S1", "المتابعة - هل تم حل المشكلة"),
    ("T1", "T1", "حلول واقتراحات "),
]

ROW2_EN = [
    "Note NO.",
    "تاريخ ارسال الرسالة",
    "وقت إرسال الرسالة",
    "رقم الجوال المرسل إليه",
    "رقم الملف ",
    "الرقم الوظيفي للمرسل",
    "مصدر الملاحظة",
    "التصنيف الرئيسي",
    "التصنيف الفرعي",
    "موضوع الملاحظة",
    "كتابة الملاحظة",
    "English",
    "Person Noted ",
    "Department Noted",
    "Cummunication Via ",
    "Date",
    "Time",
    "Action Plan",
    "Follow-Up",
    "Follow-Up",
]


def _apply_header_style(ws):
    for start, end, val in ROW1_AR:
        start_col = ord(start[0]) - ord("A") + 1
        end_col = ord(end[0]) - ord("A") + 1
        cell = ws.cell(row=1, column=start_col, value=val)
        cell.font = HEADER_FONT
        cell.fill = BLUE_FILL
        cell.alignment = HEADER_ALIGN
        cell.border = THIN_BORDER
        if start != end:
            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

    for i, h in enumerate(ROW2_EN):
        cell = ws.cell(row=2, column=i + 1, value=h)
        cell.font = HEADER_FONT
        cell.fill = BLUE_FILL
        cell.alignment = HEADER_ALIGN
        cell.border = THIN_BORDER


def _parse_notes(obs):
    action_taken = ""
    resolved = ""
    solutions = ""
    for note in obs.notes.all().order_by("created_at"):
        text = note.note or ""
        text_lower = text.lower()
        if not action_taken and ("action" in text_lower or "taken" in text_lower or "تواصل" in text or "تنبيه" in text or "توجيه" in text):
            action_taken = text
        if "resolved" in text_lower or "done" in text_lower or "تم حل" in text or "تمت" in text:
            resolved = "Yes"
        if "suggestion" in text_lower or "solution" in text_lower or "حلول" in text or "اقتراح" in text:
            solutions = text

    if not resolved and obs.status in ("resolved", "closed"):
        resolved = "Yes"

    return action_taken, resolved, solutions


def _write_data_sheet(ws, queryset):
    _apply_header_style(ws)

    ordered_qs = queryset.order_by("created_at")

    row_num = 3
    note_num = 1

    for obs in ordered_qs:
        _write_obs_row(ws, row_num, obs, note_num)
        row_num += 1
        note_num += 1

    col_widths = {
        1: 16, 2: 8, 3: 14, 4: 10, 5: 14, 6: 14, 7: 14, 8: 18,
        9: 18, 10: 18, 11: 20, 12: 40, 13: 40, 14: 18, 15: 18,
        16: 14, 17: 14, 18: 10, 19: 35, 20: 14, 21: 35,
    }
    for col, w in col_widths.items():
        ws.column_dimensions[get_column_letter(col)].width = w

    ws.freeze_panes = "C3"


def _write_obs_row(ws, row, obs, note_num):
    action_taken, resolved, solutions = _parse_notes(obs)
    obs_source = SOURCE_MAP.get(obs.source, obs.source or "")

    values = [
        note_num,
        obs.incident_datetime.date() if obs.incident_datetime else "",
        obs.incident_datetime.strftime("%H:%M") if obs.incident_datetime else "",
        obs.reporter_phone or "",
        obs.patient_file_number or "",
        obs.reporter_staff_id or "",
        obs_source,
        obs.category.name_en if obs.category else "",
        obs.sub_category.name_en if obs.sub_category else "",
        obs.title or "",
        obs.description or "",
        obs.description_en or "",
        obs.person_noted or "",
        obs.department_noted.name if obs.department_noted else "",
        obs.communication_method or "",
        obs.communication_datetime.date() if obs.communication_datetime else "",
        obs.communication_datetime.strftime("%H:%M") if obs.communication_datetime else "",
        action_taken,
        resolved,
        solutions,
    ]

    for col, val in enumerate(values, 2):
        cell = ws.cell(row=row, column=col, value=val)
        cell.font = DATA_FONT
        cell.alignment = RTL_ALIGN if col in (12, 19, 21) else DATA_ALIGN
        cell.border = THIN_BORDER


def _write_calculations_sheet(wb, employee_breakdown):
    ws = wb.create_sheet("Calculations")

    ws.cell(row=2, column=2, value="Total Number of Notes").font = Font(name="Calibri", size=11, bold=True)
    ws.cell(row=2, column=6, value="Total Number of Notes").font = Font(name="Calibri", size=11, bold=True)
    ws.cell(row=3, column=2, value="Total Number of Notes").font = DATA_FONT

    ws.cell(row=4, column=2, value="1st Half of the month, From 1 to 15").font = DATA_FONT
    ws.cell(row=5, column=2, value="2nd Half of the month, From 16 to 31").font = DATA_FONT

    ws.cell(row=2, column=9, value="Employees").font = Font(name="Calibri", size=11, bold=True)
    ws.cell(row=2, column=10, value="Total Number").font = Font(name="Calibri", size=11, bold=True)
    ws.cell(row=2, column=11, value="Percentage").font = Font(name="Calibri", size=11, bold=True)

    total = sum(e["total"] for e in employee_breakdown)

    ws.cell(row=3, column=3, value=total)
    ws.cell(row=4, column=3, value=sum(1 for e in employee_breakdown for _ in range(e["total"])))
    ws.cell(row=5, column=3, value=0)
    ws.cell(row=4, column=4, value="=C4/C3" if total > 0 else 0)
    ws.cell(row=5, column=4, value="=C5/C3" if total > 0 else 0)
    ws.cell(row=3, column=7, value="=G3+G4+G5+G6+G7+G8")

    for i in range(3, 9):
        ws.cell(row=i, column=6, value=f"Week {i - 3}")

    ws.cell(row=9, column=6, value="Total")
    ws.cell(row=9, column=7, value="=G3+G4+G5+G6+G7+G8")

    for i, emp in enumerate(employee_breakdown):
        r = 3 + i
        ws.cell(row=r, column=9, value=emp["name"]).font = DATA_FONT
        ws.cell(row=r, column=10, value=emp["total"]).font = DATA_FONT
        pct_cell = ws.cell(row=r, column=11, value=emp["total"] / total if total > 0 else 0)
        pct_cell.font = DATA_FONT
        pct_cell.number_format = "0.0%"

    total_row = 3 + len(employee_breakdown)
    ws.cell(row=total_row, column=9, value="Total").font = Font(name="Calibri", size=11, bold=True)
    ws.cell(row=total_row, column=10, value=total).font = Font(name="Calibri", size=11, bold=True)
    ws.cell(row=total_row, column=11, value=1).font = Font(name="Calibri", size=11, bold=True)
    ws.cell(row=total_row, column=11).number_format = "0.0%"

    for col in range(2, 12):
        ws.column_dimensions[get_column_letter(col)].width = 20


def generate_observation_excel(service):
    queryset = service._base_qs().prefetch_related("notes")
    employee_breakdown = service.get_employee_breakdown()
    date_label = f"{service.date_from.isoformat()} to {service.date_to.isoformat()}"

    wb = Workbook()
    ws = wb.active
    ws.title = "Observations"

    _write_data_sheet(ws, queryset)
    _write_calculations_sheet(wb, employee_breakdown)

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