from django.db.models import Case, Exists, IntegerField, OuterRef, Q, Value, When
from django.utils import timezone
from django.utils.dateparse import parse_date
from .models import Ticket, TicketReadState
from .policies import TicketPolicy
def customer_tickets(user): return Ticket.objects.filter(customer=user).select_related("category","owner__staff_profile","selected_expert__staff_profile")
def staff_ticket_rows(user):
    qs=Ticket.objects.select_related("customer__customer_profile","category","owner__staff_profile","selected_expert__staff_profile","owning_unit","assigned_unit","group_dispatch")
    if user.is_superuser or user.has_perm("tickets.view_all_tickets"): return qs
    return qs.filter(TicketPolicy.viewable_q(user)).distinct()

def filter_staff_tickets(qs, user, params):
    search=params.get("q", "").strip()
    if search:
        matches=Q(ticket_number__icontains=search)|Q(subject__icontains=search)|Q(customer__mobile__icontains=search)|Q(customer__customer_profile__first_name__icontains=search)|Q(customer__customer_profile__last_name__icontains=search)
        if params.get("tab") == "shared":
            from .staff_conversation import received_messages
            matches |= Q(pk__in=received_messages(user).filter(message__body__icontains=search).values("message__ticket_id"))
        qs=qs.filter(matches)
    if params.get("status") in Ticket.Status.values: qs=qs.filter(status=params["status"])
    if params.get("priority") in Ticket.Priority.values: qs=qs.filter(priority=params["priority"])
    if params.get("category", "").isdigit(): qs=qs.filter(category_id=params["category"])
    if params.get("owner", "").isdigit(): qs=qs.filter(owner_id=params["owner"])
    if params.get("unit", "").isdigit(): qs=qs.filter(Q(owning_unit_id=params["unit"])|Q(assigned_unit_id=params["unit"]))
    if params.get("owning_unit", "").isdigit(): qs=qs.filter(owning_unit_id=params["owning_unit"])
    if params.get("assigned_unit", "").isdigit(): qs=qs.filter(assigned_unit_id=params["assigned_unit"])
    if params.get("delivery_type") in Ticket.DeliveryType.values: qs=qs.filter(delivery_type=params["delivery_type"])
    stakeholder_type=params.get("stakeholder_type", "")
    if stakeholder_type: qs=qs.filter(customer__customer_profile__stakeholder_type=stakeholder_type)
    recipient=params.get("recipient", "").strip()
    if recipient: qs=qs.filter(Q(customer__mobile__icontains=recipient)|Q(customer__customer_profile__first_name__icontains=recipient)|Q(customer__customer_profile__last_name__icontains=recipient)|Q(customer__customer_profile__organization_name__icontains=recipient))
    group_ids=[value for value in params.getlist("contact_group") if value.isdigit()] if hasattr(params,"getlist") else []
    if group_ids: qs=qs.filter(Q(group_dispatch__contact_group_id__in=group_ids)|Q(customer__customer_profile__groups__id__in=group_ids))
    tag_ids=[value for value in params.getlist("tag") if value.isdigit()] if hasattr(params,"getlist") else []
    if tag_ids: qs=qs.filter(tags__id__in=tag_ids)
    created_from=parse_date(params.get("created_from", ""))
    created_to=parse_date(params.get("created_to", ""))
    if created_from: qs=qs.filter(created_at__date__gte=created_from)
    if created_to: qs=qs.filter(created_at__date__lte=created_to)
    updated_from=parse_date(params.get("updated_from", ""))
    updated_to=parse_date(params.get("updated_to", ""))
    if updated_from: qs=qs.filter(updated_at__date__gte=updated_from)
    if updated_to: qs=qs.filter(updated_at__date__lte=updated_to)
    tab=params.get("tab", "")
    if tab=="new": qs=qs.filter(status=Ticket.Status.NEW,owner__isnull=True,selected_expert__isnull=True)
    elif tab=="selected": qs=qs.filter(selected_expert=user,owner__isnull=True)
    elif tab=="mine": qs=qs.filter(owner=user)
    elif tab=="shared":
        from .staff_conversation import received_messages
        qs=qs.filter(Q(access_grants__user=user,access_grants__revoked_at__isnull=True) | Q(pk__in=received_messages(user).values("message__ticket_id")))
    elif tab=="waiting_staff": qs=qs.filter(status__in=[Ticket.Status.WAITING_STAFF,Ticket.Status.REOPENED])
    elif tab=="waiting_customer": qs=qs.filter(status=Ticket.Status.WAITING_CUSTOMER)
    elif tab=="resolved": qs=qs.filter(status=Ticket.Status.RESOLVED)
    elif tab=="closed": qs=qs.filter(status=Ticket.Status.CLOSED)
    if params.get("unread"):
        if tab == "shared":
            qs=qs.filter(Q(access_grants__user=user,access_grants__revoked_at__isnull=True,access_grants__first_viewed_at__isnull=True) | Q(pk__in=received_messages(user).filter(first_viewed_at__isnull=True).values("message__ticket_id")))
        else:
            read_by_user=TicketReadState.objects.filter(ticket_id=OuterRef('pk'),user=user,last_read_at__gte=OuterRef('last_activity_at'))
            qs=qs.alias(_read_by_current_user=Exists(read_by_user)).filter(_read_by_current_user=False)
    qs=qs.distinct()
    if tab in ("", "waiting_staff"):
        return qs.annotate(
            _action_rank=Case(
                When(status__in=[Ticket.Status.NEW,Ticket.Status.WAITING_STAFF,Ticket.Status.REOPENED],then=Value(0)),
                When(status=Ticket.Status.OPEN,then=Value(1)),
                When(status=Ticket.Status.WAITING_CUSTOMER,then=Value(2)),
                default=Value(3),output_field=IntegerField(),
            ),
        ).order_by("_action_rank","-last_activity_at","-created_at","-id")
    return qs.order_by("-last_activity_at","-created_at","-id")

def dashboard_counts(user):
    qs=staff_ticket_rows(user)
    return {
        "new":qs.filter(status=Ticket.Status.NEW,owner__isnull=True,selected_expert__isnull=True).count(),
        "mine":qs.filter(owner=user).count(),
        "waiting":qs.filter(status__in=[Ticket.Status.WAITING_STAFF,Ticket.Status.REOPENED]).count(),
        "overdue":qs.filter(Q(first_response_at__isnull=True,first_response_due_at__lt=timezone.now())|Q(resolved_at__isnull=True,resolution_due_at__lt=timezone.now())).count(),
    }