"""
Comments workflow export utilities.

Generates Excel exports for Steps 1, 2, 3, and 5 of the comments workflow.
"""

from collections import defaultdict

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

from .models import (
    CommentClassification,
    CommentSubCategory,
    COMMENT_SUB_CATEGORY_MAP,
)


HEADER_FONT = Font(bold=True, color="FFFFFF", size=11)
HEADER_FILL = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
HEADER_ALIGNMENT = Alignment(horizontal="center", vertical="center", wrap_text=True)
SECTION_FONT = Font(bold=True, size=12)
SECTION_FILL = PatternFill(start_color="D9E2F3", end_color="D9E2F3", fill_type="solid")
SUBSECTION_FONT = Font(bold=True, size=11, italic=True)
THIN_BORDER = Border(
    left=Side(style="thin"),
    right=Side(style="thin"),
    top=Side(style="thin"),
    bottom=Side(style="thin"),
)


def _write_header(ws, row, headers):
    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=row, column=col_num, value=header)
        cell.font = HEADER_FONT
        cell.fill = HEADER_FILL
        cell.alignment = HEADER_ALIGNMENT
        cell.border = THIN_BORDER


def _write_row(ws, row, values, border=True):
    for col_num, val in enumerate(values, 1):
        cell = ws.cell(row=row, column=col_num, value=val)
        if border:
            cell.border = THIN_BORDER


def export_classified_comments(queryset):
    """
    Step 1 — Classification export.

    Exports all classified comments with their categories,
    sub-categories, and sentiment keywords.
    """
    wb = Workbook()
    ws = wb.active
    ws.title = "Classification"

    _write_header(
        ws,
        1,
        [
            "SN",
            "Source Category",
            "Comment",
            "Classification",
            "Sub-Category",
            "Negative",
            "Positive",
            "Gratitude",
            "Suggestions",
        ],
    )

    row = 2
    for idx, c in enumerate(queryset, 1):
        _write_row(
            ws,
            row,
            [
                c.serial_number or idx,
                c.get_source_category_display(),
                c.comment_text,
                c.get_classification_display(),
                c.get_sub_category_display(),
                c.negative_keywords,
                c.positive_keywords,
                c.gratitude_keywords,
                c.suggestions,
            ],
        )
        row += 1

    ws.column_dimensions["A"].width = 8
    ws.column_dimensions["B"].width = 15
    ws.column_dimensions["C"].width = 60
    ws.column_dimensions["D"].width = 18
    ws.column_dimensions["E"].width = 22
    for col_letter in ["F", "G", "H", "I"]:
        ws.column_dimensions[col_letter].width = 30

    response = HttpResponse(content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    response["Content-Disposition"] = 'attachment; filename="comments_classification.xlsx"'
    wb.save(response)
    return response


def export_filtered_comments_by_dept(queryset):
    """
    Step 2 — Filtering by department.

    Creates one sheet per department category/sub-category,
    grouping comments with translations and negative summaries.
    """
    wb = Workbook()

    dept_comments = defaultdict(list)
    for c in queryset:
        dept_key = f"{c.get_classification_display()} / {c.get_sub_category_display()}"
        dept_comments[dept_key].append(c)

    first_sheet = True
    for dept_key, comments in dept_comments.items():
        classification, _, sub_category = dept_key.partition(" / ")

        if first_sheet:
            ws = wb.active
            ws.title = dept_key[:31]
            first_sheet = False
        else:
            ws = wb.create_sheet(title=dept_key[:31])

        ws.cell(row=1, column=1, value=classification).font = SECTION_FONT
        ws.cell(row=2, column=1, value=sub_category).font = SUBSECTION_FONT

        _write_header(ws, 4, ["#", "Comment (Arabic)", "Comment (English)", "Negative", "Sentiment"])
        row = 5
        for idx, c in enumerate(comments, 1):
            _write_row(
                ws,
                row,
                [
                    idx,
                    c.comment_text,
                    c.comment_text_en,
                    c.negative_keywords,
                    c.get_sentiment_display(),
                ],
            )
            row += 1

        ws.column_dimensions["A"].width = 6
        ws.column_dimensions["B"].width = 50
        ws.column_dimensions["C"].width = 50
        ws.column_dimensions["D"].width = 40
        ws.column_dimensions["E"].width = 12

    if first_sheet:
        ws = wb.active
        ws.cell(row=1, column=1, value="No comments found for the selected filters.")

    response = HttpResponse(content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    response["Content-Disposition"] = 'attachment; filename="comments_filtered_by_dept.xlsx"'
    wb.save(response)
    return response


def export_action_plans(queryset):
    """
    Step 3/5 — Action plan export with tracking status.

    Groups action plans by department with problem numbers,
    recommendations, responsible departments, and status.
    """
    wb = Workbook()
    ws = wb.active
    ws.title = "Action Plans"

    _write_header(
        ws,
        1,
        [
            "Problem #",
            "Comment",
            "English Translation",
            "Frequency",
            "Recommendation / Action Plan",
            "Responsible Department",
            "Timeframe",
            "Status",
            "Evidences",
        ],
    )

    dept_plans = defaultdict(list)
    for plan in queryset:
        dept_label = plan.department_label or (plan.department.name if plan.department else "Unassigned")
        dept_plans[dept_label].append(plan)

    row = 2
    current_dept = None
    for dept_label, plans in dept_plans.items():
        if dept_label != current_dept:
            ws.cell(row=row, column=1, value=dept_label).font = SECTION_FONT
            row += 1
            current_dept = dept_label

        for plan in plans:
            _write_row(
                ws,
                row,
                [
                    plan.problem_number or "",
                    plan.comment_text,
                    plan.comment_text_en,
                    plan.frequency,
                    plan.recommendation,
                    plan.responsible_department or (plan.department.name if plan.department else ""),
                    plan.timeframe,
                    plan.get_status_display(),
                    plan.evidences,
                ],
            )
            row += 1

    row += 2
    ws.cell(row=row, column=1, value="Summary").font = SECTION_FONT
    row += 1
    _write_header(ws, row, ["Department", "Total Plans", "Completed", "On Process", "Pending"])
    row += 1

    for dept_label, plans in dept_plans.items():
        completed = sum(1 for p in plans if p.status == "completed")
        on_process = sum(1 for p in plans if p.status == "on_process")
        pending = sum(1 for p in plans if p.status == "pending")
        _write_row(ws, row, [dept_label, len(plans), completed, on_process, pending])
        row += 1

    ws.column_dimensions["A"].width = 20
    ws.column_dimensions["B"].width = 50
    ws.column_dimensions["C"].width = 50
    ws.column_dimensions["D"].width = 10
    ws.column_dimensions["E"].width = 40
    ws.column_dimensions["F"].width = 25
    ws.column_dimensions["G"].width = 15
    ws.column_dimensions["H"].width = 12
    ws.column_dimensions["I"].width = 30

    response = HttpResponse(content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    response["Content-Disposition"] = 'attachment; filename="comments_action_plans.xlsx"'
    wb.save(response)
    return response
