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


NAVY_FILL = PatternFill(start_color="005696", end_color="005696", fill_type="solid")
BLUE_FILL = PatternFill(start_color="E0F2FE", end_color="E0F2FE", fill_type="solid")
GREEN_FILL = PatternFill(start_color="DCFCE7", end_color="DCFCE7", fill_type="solid")
LIGHT_FILL = PatternFill(start_color="F8FAFC", end_color="F8FAFC", fill_type="solid")
WHITE_FONT = Font(color="FFFFFF", bold=True, size=11)
HEADER_FONT = Font(color="FFFFFF", bold=True, size=12)
TITLE_FONT = Font(color="005696", bold=True, size=14)
SECTION_FONT = Font(color="005696", bold=True, size=11)
BODY_FONT = Font(size=10)
BOLD_FONT = Font(bold=True, size=10)
THIN_BORDER = Border(
    left=Side(style="thin", color="E2E8F0"),
    right=Side(style="thin", color="E2E8F0"),
    top=Side(style="thin", color="E2E8F0"),
    bottom=Side(style="thin", color="E2E8F0"),
)
WRAP_ALIGN = Alignment(wrap_text=True, vertical="top")
CENTER_ALIGN = Alignment(horizontal="center", vertical="center")


def _write_header_row(ws, row, headers, fill=None, font=None):
    col = 1
    for h in headers:
        cell = ws.cell(row=row, column=col, value=h)
        cell.font = font or WHITE_FONT
        cell.fill = fill or NAVY_FILL
        cell.alignment = CENTER_ALIGN
        cell.border = THIN_BORDER
        col += 1


def _write_row(ws, row, values, fill=None, font=None):
    col = 1
    for v in values:
        cell = ws.cell(row=row, column=col, value=v)
        cell.font = font or BODY_FONT
        cell.fill = fill or PatternFill()
        cell.alignment = WRAP_ALIGN
        cell.border = THIN_BORDER
        col += 1


def export_project_excel(project):
    wb = Workbook()

    # --- Sheet 1: Project Overview ---
    ws_overview = wb.active
    ws_overview.title = "Project Overview"

    ws_overview.merge_cells("A1:D1")
    title_cell = ws_overview.cell(row=1, column=1, value=f"QI Project: {project.name}")
    title_cell.font = TITLE_FONT

    info_data = [
        ("Description", project.description),
        ("Hospital", project.hospital.name if project.hospital else ""),
        ("Department", project.department.name if project.department else ""),
        ("Project Lead", project.project_lead.get_full_name() if project.project_lead else ""),
        ("Created By", project.created_by.get_full_name() if project.created_by else ""),
        ("Status", project.get_status_display()),
        ("Start Date", str(project.start_date) if project.start_date else ""),
        ("Target Completion", str(project.target_completion_date) if project.target_completion_date else ""),
        ("Actual Completion", str(project.actual_completion_date) if project.actual_completion_date else ""),
        ("Outcome", project.outcome_description or ""),
    ]

    _write_header_row(ws_overview, 3, ["Field", "Value"])
    for i, (field, value) in enumerate(info_data, start=4):
        fill = LIGHT_FILL if i % 2 == 0 else None
        _write_row(ws_overview, i, [field, value], fill=fill)
        ws_overview.cell(row=i, column=1).font = BOLD_FONT

    ws_overview.column_dimensions["A"].width = 25
    ws_overview.column_dimensions["B"].width = 60

    # Team Members
    row = 4 + len(info_data) + 1
    ws_overview.merge_cells(f"A{row}:D{row}")
    ws_overview.cell(row=row, column=1, value="Team Members").font = SECTION_FONT
    row += 1
    _write_header_row(ws_overview, row, ["Name", "Email", "Role(s)"])
    row += 1
    for member in project.team_members.all():
        roles = ", ".join(member.get_role_names()) if hasattr(member, "get_role_names") else ""
        _write_row(ws_overview, row, [member.get_full_name(), member.email, roles])
        row += 1

    # --- Sheet 2: PDCA Phases ---
    ws_pdca = wb.create_sheet("PDCA Cycle")

    ws_pdca.merge_cells("A1:F1")
    ws_pdca.cell(row=1, column=1, value="PDCA Cycle").font = TITLE_FONT

    _write_header_row(ws_pdca, 3, ["Phase", "Title", "Status", "Owner", "Start Date", "Due Date"])

    row = 4
    phase_fills = {
        "plan": PatternFill(start_color="FEF3C7", end_color="FEF3C7", fill_type="solid"),
        "do": PatternFill(start_color="DBEAFE", end_color="DBEAFE", fill_type="solid"),
        "check": PatternFill(start_color="F3E8FF", end_color="F3E8FF", fill_type="solid"),
        "act": PatternFill(start_color="DCFCE7", end_color="DCFCE7", fill_type="solid"),
    }

    for phase_key, phase_label in [("plan", "Plan"), ("do", "Do"), ("check", "Check"), ("act", "Act")]:
        pdca_phase = None
        for p in project.pdca_phases.all():
            if p.phase == phase_key:
                pdca_phase = p
                break

        fill = phase_fills.get(phase_key, LIGHT_FILL)
        if pdca_phase:
            _write_row(
                ws_pdca,
                row,
                [
                    phase_label,
                    pdca_phase.title,
                    pdca_phase.get_status_display(),
                    pdca_phase.owner.get_full_name() if pdca_phase.owner else "",
                    str(pdca_phase.start_date) if pdca_phase.start_date else "",
                    str(pdca_phase.due_date) if pdca_phase.due_date else "",
                ],
                fill=fill,
            )
            row += 1

            if pdca_phase.description:
                ws_pdca.merge_cells(f"B{row}:F{row}")
                desc_cell = ws_pdca.cell(row=row, column=2, value=f"Description: {pdca_phase.description}")
                desc_cell.font = Font(size=9, italic=True, color="64748B")
                desc_cell.alignment = WRAP_ALIGN
                row += 1

            if pdca_phase.findings:
                ws_pdca.merge_cells(f"B{row}:F{row}")
                find_cell = ws_pdca.cell(row=row, column=2, value=f"Findings: {pdca_phase.findings}")
                find_cell.font = Font(size=9, italic=True, color="64748B")
                find_cell.alignment = WRAP_ALIGN
                row += 1
        else:
            _write_row(ws_pdca, row, [phase_label, "(Not started)", "", "", "", ""], fill=fill)
            row += 1

        row += 1

    ws_pdca.column_dimensions["A"].width = 12
    ws_pdca.column_dimensions["B"].width = 40
    ws_pdca.column_dimensions["C"].width = 15
    ws_pdca.column_dimensions["D"].width = 20
    ws_pdca.column_dimensions["E"].width = 15
    ws_pdca.column_dimensions["F"].width = 15

    # --- Sheet 3: Phase Tasks ---
    ws_tasks = wb.create_sheet("Phase Tasks")

    ws_tasks.merge_cells("A1:G1")
    ws_tasks.cell(row=1, column=1, value="Tasks by PDCA Phase").font = TITLE_FONT

    row = 3
    phase_order = ["plan", "do", "check", "act"]
    for phase_key in phase_order:
        phase_label = dict(PDCAPhaseChoices.choices).get(phase_key, phase_key)
        phase_obj = None
        for p in project.pdca_phases.all():
            if p.phase == phase_key:
                phase_obj = p
                break

        # Phase header
        ws_tasks.merge_cells(f"A{row}:G{row}")
        phase_header = ws_tasks.cell(row=row, column=1, value=f"{phase_label} Phase")
        phase_header.font = SECTION_FONT
        phase_header.fill = LIGHT_FILL
        phase_header.alignment = CENTER_ALIGN
        phase_header.border = THIN_BORDER
        row += 1

        _write_header_row(ws_tasks, row, ["Title", "Description", "Assigned To", "Status", "Due Date", "Completed"])
        row += 1

        if phase_obj:
            phase_tasks = phase_obj.tasks.all().order_by("order")
            if phase_tasks:
                for task in phase_tasks:
                    fill = GREEN_FILL if task.status == "completed" else None
                    _write_row(
                        ws_tasks,
                        row,
                        [
                            task.title,
                            task.description,
                            task.assigned_to.get_full_name() if task.assigned_to else "",
                            task.get_status_display(),
                            str(task.due_date) if task.due_date else "",
                            str(task.completed_date) if task.completed_date else "",
                        ],
                        fill=fill,
                    )
                    row += 1
            else:
                ws_tasks.merge_cells(f"A{row}:G{row}")
                no_tasks = ws_tasks.cell(row=row, column=1, value="No tasks in this phase")
                no_tasks.font = Font(size=10, italic=True, color="94A3B8")
                no_tasks.alignment = CENTER_ALIGN
                row += 1
        else:
            ws_tasks.merge_cells(f"A{row}:G{row}")
            no_phase = ws_tasks.cell(row=row, column=1, value="Phase not started")
            no_phase.font = Font(size=10, italic=True, color="94A3B8")
            no_phase.alignment = CENTER_ALIGN
            row += 1

        row += 1  # spacing between phases

    ws_tasks.column_dimensions["A"].width = 30
    ws_tasks.column_dimensions["B"].width = 40
    ws_tasks.column_dimensions["C"].width = 20
    ws_tasks.column_dimensions["D"].width = 15
    ws_tasks.column_dimensions["E"].width = 15
    ws_tasks.column_dimensions["F"].width = 15

    return wb
