"""Helpdesk selectors — read-side queries.

All selectors filter by tenant_id to prevent cross-tenant data leaks.
Business code must not query helpdesk models directly — use these
selectors instead for read operations.

Commands (write operations) remain in ``services.py``.
"""

from __future__ import annotations

from django.db.models import Q, QuerySet

# ---------------------------------------------------------------------------
# Ticket selectors
# ---------------------------------------------------------------------------


def tickets_for_tenant(
    tenant_id: int,
    *,
    status: str | None = None,
    priority: str | None = None,
    queue_id: int | None = None,
    category_id: int | None = None,
    assigned_to_id: int | None = None,
    requester_id: int | None = None,
    search: str | None = None,
) -> QuerySet:
    """Return all active tickets for *tenant_id* with optional filters."""
    from simorgh.apps.helpdesk.models import Ticket

    qs = Ticket.objects.filter(tenant_id=tenant_id).select_related(
        "queue", "category", "requester", "assigned_to"
    ).prefetch_related("tags")

    if status:
        qs = qs.filter(status__in=status.split(","))
    if priority:
        qs = qs.filter(priority=priority)
    if queue_id:
        qs = qs.filter(queue_id=queue_id)
    if category_id:
        qs = qs.filter(category_id=category_id)
    if assigned_to_id:
        qs = qs.filter(assigned_to_id=assigned_to_id)
    if requester_id:
        qs = qs.filter(requester_id=requester_id)
    if search:
        q = search.strip()
        qs = qs.filter(
            Q(subject__icontains=q)
            | Q(requester_email__icontains=q)
            | Q(requester_name__icontains=q)
        )

    return qs.order_by("-created_at")


def ticket_by_id(tenant_id: int, pk: int) -> Ticket | None:  # noqa: F821
    from simorgh.apps.helpdesk.models import Ticket

    return Ticket.objects.filter(tenant_id=tenant_id, pk=pk).select_related(
        "queue__default_sla_policy", "category", "requester", "assigned_to",
    ).prefetch_related("tags", "replies", "attachments").first()


def ticket_by_public_id(tenant_id: int, public_id: str) -> Ticket | None:  # noqa: F821
    from simorgh.apps.helpdesk.models import Ticket

    return Ticket.objects.filter(
        tenant_id=tenant_id, public_id=public_id
    ).select_related(
        "queue", "category", "requester", "assigned_to",
    ).first()


def ticket_count_by_status(tenant_id: int) -> dict[str, int]:
    """Return counts keyed by status (e.g. ``{"new": 5, "open": 12, ...}``)."""
    from django.db.models import Count

    from simorgh.apps.helpdesk.models import Ticket

    rows = (
        Ticket.objects.filter(tenant_id=tenant_id)
        .values("status")
        .annotate(count=Count("pk"))
    )
    return {r["status"]: r["count"] for r in rows}


# ---------------------------------------------------------------------------
# TicketReply selectors
# ---------------------------------------------------------------------------


def replies_for_ticket(
    ticket_id: int,
    *,
    include_internal: bool = False,
) -> QuerySet:
    from simorgh.apps.helpdesk.models import TicketReply

    qs = TicketReply.objects.filter(ticket_id=ticket_id).select_related("author")
    if not include_internal:
        qs = qs.filter(is_internal=False)
    return qs.order_by("created_at")


# ---------------------------------------------------------------------------
# Queue selectors
# ---------------------------------------------------------------------------


def queues_for_tenant(
    tenant_id: int,
    *,
    active_only: bool = True,
) -> QuerySet:
    from simorgh.apps.helpdesk.models import Queue

    qs = Queue.objects.filter(tenant_id=tenant_id).select_related("default_sla_policy")
    if active_only:
        qs = qs.filter(is_active=True)
    return qs.order_by("sort_order", "name")


def queue_by_id(tenant_id: int, pk: int) -> Queue | None:  # noqa: F821
    from simorgh.apps.helpdesk.models import Queue

    return Queue.objects.filter(tenant_id=tenant_id, pk=pk).first()


# ---------------------------------------------------------------------------
# Category selectors
# ---------------------------------------------------------------------------


def categories_for_tenant(
    tenant_id: int,
    *,
    queue_id: int | None = None,
    active_only: bool = True,
) -> QuerySet:
    from simorgh.apps.helpdesk.models import Category

    qs = Category.objects.filter(tenant_id=tenant_id)
    if queue_id is not None:
        qs = qs.filter(queue_id=queue_id)
    if active_only:
        qs = qs.filter(is_active=True)
    return qs.order_by("sort_order", "name")


# ---------------------------------------------------------------------------
# Tag selectors
# ---------------------------------------------------------------------------


def tags_for_tenant(
    tenant_id: int,
    *,
    active_only: bool = True,
) -> QuerySet:
    from simorgh.apps.helpdesk.models import Tag

    qs = Tag.objects.filter(tenant_id=tenant_id)
    if active_only:
        qs = qs.filter(is_active=True)
    return qs.order_by("name")


# ---------------------------------------------------------------------------
# SLA Policy selectors
# ---------------------------------------------------------------------------


def sla_policies_for_tenant(
    tenant_id: int,
    *,
    active_only: bool = True,
) -> QuerySet:
    from simorgh.apps.helpdesk.models import SLAPolicy

    qs = SLAPolicy.objects.filter(tenant_id=tenant_id)
    if active_only:
        qs = qs.filter(is_active=True)
    return qs.order_by("name")


# ---------------------------------------------------------------------------
# Automation selectors
# ---------------------------------------------------------------------------


def automations_for_tenant(
    tenant_id: int,
    *,
    trigger: str | None = None,
    active_only: bool = True,
) -> QuerySet:
    from simorgh.apps.helpdesk.models import Automation

    qs = Automation.objects.filter(tenant_id=tenant_id)
    if trigger:
        qs = qs.filter(trigger=trigger)
    if active_only:
        qs = qs.filter(is_active=True)
    return qs.order_by("sort_order")


# ---------------------------------------------------------------------------
# AgentTeam selectors
# ---------------------------------------------------------------------------


def agent_teams_for_tenant(
    tenant_id: int,
    *,
    active_only: bool = True,
) -> QuerySet:
    from simorgh.apps.helpdesk.models import AgentTeam

    qs = AgentTeam.objects.filter(tenant_id=tenant_id).prefetch_related("members")
    if active_only:
        qs = qs.filter(is_active=True)
    return qs.order_by("name")


def agent_team_by_id(tenant_id: int, pk: int) -> AgentTeam | None:  # noqa: F821
    from simorgh.apps.helpdesk.models import AgentTeam

    return AgentTeam.objects.filter(tenant_id=tenant_id, pk=pk).prefetch_related(
        "members"
    ).first()