"""Helpdesk import and export services.

Import
------
Accepts CSV or Excel files via API endpoint, validates rows, and creates
tickets asynchronously through Celery tasks.  Supports dry-run mode.

Export
------
Registers ``helpdesk.ticket`` and ``helpdesk.reply`` export specs with the
platform-wide ExportRegistry.  Provides a unified ``/export/`` endpoint that
delegates to the registry and returns CSV, Excel, or PDF output.
"""

from __future__ import annotations

import csv
import io
import json
from dataclasses import dataclass
from datetime import datetime
from typing import Any

import structlog
import openpyxl
from celery import shared_task
from django.utils import timezone

_log = structlog.get_logger("simorgh.helpdesk.import_export")

# ──────────────────────────────────────────────────────────────────
# Export registry registration
# ──────────────────────────────────────────────────────────────────


def register_export_specs() -> None:
    """Called from HelpdeskConfig.ready() to register export specs."""
    from simorgh.apps.platform_core.export_registry import (
        ColumnExportSpec,
        ExportSpec,
        export_registry,
    )

    # ── Ticket export ─────────────────────────────────────────────
    export_registry.register(
        ExportSpec(
            entity_type="helpdesk.ticket",
            display_name="Help Desk Tickets",
            columns=[
                ColumnExportSpec(key="reference_number", label="Reference"),
                ColumnExportSpec(key="subject", label="Subject"),
                ColumnExportSpec(key="description", label="Description"),
                ColumnExportSpec(key="status", label="Status"),
                ColumnExportSpec(key="priority", label="Priority"),
                ColumnExportSpec(key="channel", label="Channel"),
                ColumnExportSpec(key="queue_name", label="Queue"),
                ColumnExportSpec(key="category_name", label="Category"),
                ColumnExportSpec(key="requester_display", label="Requester"),
                ColumnExportSpec(key="requester_email", label="Requester Email"),
                ColumnExportSpec(key="assigned_to_name", label="Assigned To"),
                ColumnExportSpec(key="satisfaction_rating", label="CSAT"),
                ColumnExportSpec(key="first_response_due_at", label="First Resp Due"),
                ColumnExportSpec(key="resolution_due_at", label="Resolution Due"),
                ColumnExportSpec(key="resolved_at", label="Resolved At"),
                ColumnExportSpec(key="closed_at", label="Closed At"),
                ColumnExportSpec(key="created_at", label="Created At"),
                ColumnExportSpec(key="updated_at", label="Updated At"),
                ColumnExportSpec(key="tags", label="Tags"),
            ],
            queryset_fn=_tickets_queryset,
            row_fn=_ticket_to_row,
        )
    )

    # ── TicketReply export ─────────────────────────────────────────
    export_registry.register(
        ExportSpec(
            entity_type="helpdesk.reply",
            display_name="Help Desk Replies",
            columns=[
                ColumnExportSpec(key="ticket_reference", label="Ticket Ref"),
                ColumnExportSpec(key="ticket_subject", label="Ticket Subject"),
                ColumnExportSpec(key="author_display", label="Author"),
                ColumnExportSpec(key="author_email", label="Author Email"),
                ColumnExportSpec(key="body", label="Body"),
                ColumnExportSpec(key="is_internal", label="Internal"),
                ColumnExportSpec(key="channel", label="Channel"),
                ColumnExportSpec(key="created_at", label="Created At"),
            ],
            queryset_fn=_replies_queryset,
            row_fn=_reply_to_row,
        )
    )


def _tickets_queryset(*, tenant, actor, filters):
    from simorgh.apps.helpdesk.models import Ticket

    qs = Ticket.objects.filter(tenant=tenant)
    if not actor or not actor.is_superuser:
        status_filter = filters.get("status")
        if status_filter:
            qs = qs.filter(status__in=status_filter.split(","))
        queue_id = filters.get("queue_id")
        if queue_id:
            qs = qs.filter(queue_id=queue_id)
        priority = filters.get("priority")
        if priority:
            qs = qs.filter(priority=priority)
        q_search = filters.get("q")
        if q_search:
            from django.db.models import Q

            qs = qs.filter(
                Q(subject__icontains=q_search)
                | Q(requester_email__icontains=q_search)
                | Q(requester_name__icontains=q_search)
            )
    return qs.select_related("queue", "category", "requester", "assigned_to").prefetch_related("tags")


def _ticket_to_row(ticket) -> dict:
    return {
        "reference_number": ticket.reference_number or "",
        "subject": ticket.subject,
        "description": ticket.description or "",
        "status": ticket.status,
        "priority": ticket.priority,
        "channel": ticket.channel,
        "queue_name": ticket.queue.name if ticket.queue else "",
        "category_name": ticket.category.name if ticket.category else "",
        "requester_display": ticket.requester_display,
        "requester_email": ticket.requester_email or "",
        "assigned_to_name": _format_user_name(ticket.assigned_to),
        "satisfaction_rating": str(ticket.satisfaction_rating) if ticket.satisfaction_rating else "",
        "first_response_due_at": _format_dt(ticket.first_response_due_at),
        "resolution_due_at": _format_dt(ticket.resolution_due_at),
        "resolved_at": _format_dt(ticket.resolved_at),
        "closed_at": _format_dt(ticket.closed_at),
        "created_at": _format_dt(ticket.created_at),
        "updated_at": _format_dt(ticket.updated_at),
        "tags": ", ".join(t.name for t in ticket.tags.all()),
    }


def _replies_queryset(*, tenant, actor, filters):
    from simorgh.apps.helpdesk.models import TicketReply

    qs = TicketReply.objects.filter(ticket__tenant=tenant).select_related("ticket", "author")
    ticket_id = filters.get("ticket_id")
    if ticket_id:
        qs = qs.filter(ticket_id=ticket_id)
    return qs


def _reply_to_row(reply) -> dict:
    return {
        "ticket_reference": reply.ticket.reference_number or f"#{reply.ticket_id}",
        "ticket_subject": reply.ticket.subject,
        "author_display": reply.author_display,
        "author_email": reply.author_email or "",
        "body": reply.body,
        "is_internal": "Yes" if reply.is_internal else "No",
        "channel": reply.channel,
        "created_at": _format_dt(reply.created_at),
    }


def _format_user_name(user) -> str:
    if user is None:
        return ""
    return user.get_full_name() or user.email or ""


def _format_dt(dt_value):
    if dt_value is None:
        return ""
    if isinstance(dt_value, datetime):
        if timezone.is_naive(dt_value):
            return dt_value.isoformat()
        return dt_value.astimezone(timezone.utc).isoformat()
    return str(dt_value)


# ──────────────────────────────────────────────────────────────────
# CSV / Excel export helpers
# ──────────────────────────────────────────────────────────────────


def generate_csv(spec, results: list[dict]) -> bytes:
    buf = io.StringIO()
    writer = csv.DictWriter(buf, fieldnames=spec.column_keys(), extrasaction="ignore")
    writer.writerow(dict(zip(spec.column_keys(), spec.column_labels())))
    for row in results:
        writer.writerow(row)
    return buf.getvalue().encode("utf-8-sig")


def generate_excel(spec, results: list[dict]) -> bytes:
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = spec.display_name or spec.entity_type
    ws.append(spec.column_labels())
    for row in results:
        ws.append([row.get(key, "") for key in spec.column_keys()])
    buf = io.BytesIO()
    wb.save(buf)
    buf.seek(0)
    return buf.getvalue()


# ──────────────────────────────────────────────────────────────────
# PDF export helpers (via WeasyPrint HTML)
# ──────────────────────────────────────────────────────────────────


def generate_pdf(spec, results: list[dict], title: str = "") -> bytes:
    import weasyprint

    columns = spec.columns
    html_parts = [
        "<html><head><meta charset='utf-8'>",
        "<style>",
        "body { font-family: sans-serif; font-size: 10pt; margin: 20px; }",
        "h1 { font-size: 14pt; margin-bottom: 4px; }",
        "table { width: 100%; border-collapse: collapse; margin-top: 12px; }",
        "th { background: #f3f4f6; text-align: left; padding: 6px 4px; border-bottom: 2px solid #d1d5db; }",
        "td { padding: 4px; border-bottom: 1px solid #e5e7eb; vertical-align: top; max-width: 180px; overflow: hidden; }",
        "tr:nth-child(even) td { background: #fafafa; }",
        ".text-muted { color: #9ca3af; font-size: 9pt; }",
        "</style></head><body>",
        f"<h1>{title or (spec.display_name or spec.entity_type)}</h1>",
        f"<p class='text-muted'>Generated: {timezone.now().strftime('%Y-%m-%d %H:%M UTC')} | Rows: {len(results)}</p>",
        "<table><thead><tr>",
    ]
    for col in columns:
        html_parts.append(f"<th>{col.label}</th>")
    html_parts.append("</tr></thead><tbody>")

    for row in results:
        html_parts.append("<tr>")
        for col in columns:
            value = row.get(col.key, "")
            if value is None:
                value = ""
            elif not isinstance(value, str):
                value = str(value)
            html_parts.append(f"<td>{value}</td>")
        html_parts.append("</tr>")

    html_parts.append("</tbody></table></body></html>")
    html = "".join(html_parts)
    return weasyprint.HTML(string=html).write_pdf()


# ──────────────────────────────────────────────────────────────────
# Import: row parsing + validation
# ──────────────────────────────────────────────────────────────────


@dataclass(slots=True)
class ImportRowResult:
    row_number: int
    data: dict[str, Any] | None
    errors: list[str]
    created: bool = False
    ticket_id: int | None = None


def parse_csv_file(file_content: bytes) -> list[dict]:
    buf = io.StringIO(file_content.decode("utf-8-sig"))
    reader = csv.DictReader(buf)
    return list(reader)


def parse_excel_file(file_content: bytes) -> list[dict]:
    wb = openpyxl.load_workbook(io.BytesIO(file_content), read_only=True)
    ws = wb.active
    rows_iter = ws.iter_rows(values_only=True)
    headers = [str(h).strip().lower() if h else "" for h in next(rows_iter)]
    results = []
    for row in rows_iter:
        results.append(dict(zip(headers, (str(v) if v is not None else "" for v in row))))
    wb.close()
    return results


TICKET_IMPORT_FIELDS = {
    "subject",
    "description",
    "priority",
    "queue_id",
    "queuename",
    "queue_name",
    "category_id",
    "categoryname",
    "category_name",
    "requester_email",
    "requester_name",
    "channel",
    "status",
    "tags",
    "custom_fields",
}


@shared_task(bind=True, max_retries=1, default_retry_delay=120)
def process_ticket_import(
    self,
    tenant_id: int,
    org_node_id: int,
    actor_id: int,
    rows: list[dict],
    queue_id: int | None = None,
) -> dict:
    """Process imported ticket rows asynchronously.  Called from the import view."""
    from django.contrib.auth import get_user_model

    from simorgh.apps.helpdesk.models import Queue
    from simorgh.apps.helpdesk.services import TicketService

    User = get_user_model()
    actor = User.objects.filter(pk=actor_id).first()

    results: list[ImportRowResult] = []
    created = 0
    skipped = 0

    for idx, row in enumerate(rows, start=1):
        errors = []
        normalized = _normalize_import_row(row, queue_id)

        if not normalized.get("subject"):
            errors.append("Missing required field: subject")

        if errors:
            results.append(ImportRowResult(row_number=idx, data=None, errors=errors))
            skipped += 1
            continue

        try:
            resolved_queue_id = normalized.get("queue_id") or queue_id
            if not resolved_queue_id:
                default_q = (
                    Queue.objects.filter(tenant_id=tenant_id, is_default=True, is_active=True).first()
                    or Queue.objects.filter(tenant_id=tenant_id, is_active=True).order_by("sort_order", "id").first()
                )
                if default_q:
                    resolved_queue_id = default_q.pk
                else:
                    errors.append("No queue found for tenant")
                    results.append(ImportRowResult(row_number=idx, data=normalized, errors=errors))
                    skipped += 1
                    continue

            ticket = TicketService.create(
                tenant_id=tenant_id,
                organization_node_id=org_node_id,
                queue_id=resolved_queue_id,
                subject=normalized["subject"],
                description=normalized.get("description", ""),
                priority=normalized.get("priority", "normal"),
                channel=normalized.get("channel", "web"),
                category_id=normalized.get("category_id"),
                requester_email=normalized.get("requester_email", ""),
                requester_name=normalized.get("requester_name", ""),
                created_by_id=actor_id,
                custom_fields=normalized.get("custom_fields", {}),
                tag_ids=normalized.get("tag_ids", []),
                actor=actor,
            )
            results.append(
                ImportRowResult(
                    row_number=idx,
                    data=normalized,
                    errors=[],
                    created=True,
                    ticket_id=ticket.pk,
                )
            )
            created += 1
        except Exception as exc:
            results.append(
                ImportRowResult(
                    row_number=idx,
                    data=normalized,
                    errors=[str(exc)],
                )
            )
            skipped += 1

    _log.info("helpdesk.import.completed", tenant_id=tenant_id, created=created, skipped=skipped)
    return {"created": created, "skipped": skipped, "total": len(rows)}


def _normalize_import_row(row: dict, fallback_queue_id: int | None) -> dict:
    normalized: dict[str, Any] = {}

    for key in TICKET_IMPORT_FIELDS:
        value = row.get(key) or row.get(key.lower()) or row.get(key.replace("_", ""))
        if value is None or value == "":
            continue
        normalized[key] = str(value).strip()

    mapping = {
        "queuename": "queue_name",
        "categoryname": "category_name",
    }
    for alt, canonical in mapping.items():
        if alt in normalized and canonical not in normalized:
            normalized[canonical] = normalized.pop(alt)

    if "queue_name" in normalized and "queue_id" not in normalized:
        from simorgh.apps.helpdesk.models import Queue

        q = Queue.objects.filter(name__iexact=normalized["queue_name"]).first()
        if q:
            normalized["queue_id"] = q.pk

    if "category_name" in normalized and "category_id" not in normalized:
        from simorgh.apps.helpdesk.models import Category

        c = Category.objects.filter(name__iexact=normalized["category_name"]).first()
        if c:
            normalized["category_id"] = c.pk

    if "tags" in normalized and isinstance(normalized["tags"], str):
        tag_names = [t.strip() for t in normalized["tags"].split(",") if t.strip()]
        from simorgh.apps.helpdesk.models import Tag

        tag_ids = list(
            Tag.objects.filter(name__in=tag_names, is_active=True).values_list("pk", flat=True)
        )
        normalized["tag_ids"] = tag_ids
        del normalized["tags"]

    if "custom_fields" in normalized and isinstance(normalized["custom_fields"], str):
        try:
            normalized["custom_fields"] = json.loads(normalized["custom_fields"])
        except (json.JSONDecodeError, TypeError):
            normalized["custom_fields"] = {}

    return normalized