admin / Synapse-Cortex

import uuid
from datetime import datetime, timedelta, timezone

from sqlalchemy import or_
from sqlalchemy.orm import Query, Session

from . import models

OPEN_TICKET_STATUSES = [
    models.TicketStatus.NEW,
    models.TicketStatus.IN_PROGRESS,
    models.TicketStatus.AWAITING_USER,
]

# Every queue-card filter name maps to a predicate applied to a base
# tenant-scoped Ticket query. Shared by the dashboard (for counts) and the
# tickets list page (for the filtered view a card links to), so the two can
# never drift out of sync.
QUEUE_FILTERS = ("unassigned", "awaiting_user", "sla_breaching", "resolved_today")


def tenant_tickets(db: Session, tenant_id: uuid.UUID) -> Query:
    return db.query(models.Ticket).filter(models.Ticket.tenant_id == tenant_id)


def apply_queue_filter(query: Query, filter_name: str) -> Query:
    now = datetime.now(timezone.utc)

    if filter_name == "unassigned":
        return query.filter(
            models.Ticket.assigned_to_id.is_(None), models.Ticket.status.in_(OPEN_TICKET_STATUSES)
        )
    if filter_name == "awaiting_user":
        return query.filter(models.Ticket.status == models.TicketStatus.AWAITING_USER)
    if filter_name == "sla_breaching":
        return query.filter(
            models.Ticket.status.in_(OPEN_TICKET_STATUSES),
            models.Ticket.resolution_due_at.isnot(None),
            models.Ticket.resolution_due_at <= now,
        )
    if filter_name == "resolved_today":
        today_start = now.replace(hour=0, minute=0, second=0, microsecond=0)
        return query.filter(
            models.Ticket.status.in_([models.TicketStatus.RESOLVED, models.TicketStatus.CLOSED]),
            models.Ticket.resolved_at.isnot(None),
            models.Ticket.resolved_at >= today_start,
        )
    return query


def apply_ticket_search(query: Query, search_text: str) -> Query:
    """Keyword search across title/description/ticket number. Splits on
    whitespace and requires every keyword to match somewhere (AND across
    keywords, OR across fields per keyword), so "printer jammed" narrows
    results rather than broadening them."""
    keywords = [kw for kw in search_text.strip().split() if kw]
    for kw in keywords:
        pattern = f"%{kw}%"
        query = query.filter(
            or_(
                models.Ticket.title.ilike(pattern),
                models.Ticket.description.ilike(pattern),
                models.Ticket.ticket_number.ilike(pattern),
            )
        )
    return query


TICKET_SORTS = ("newest", "oldest", "priority", "status")

_PRIORITY_ORDER = {
    models.TicketPriority.CRITICAL: 0,
    models.TicketPriority.HIGH: 1,
    models.TicketPriority.MEDIUM: 2,
    models.TicketPriority.LOW: 3,
}


def apply_ticket_sort(query: Query, sort_name: str):
    """Returns (query, in_python_sort_key_or_None). Priority has no natural
    DB column ordering (it's an enum whose values don't sort the way we want
    across dialects), so that one case sorts in Python after fetching."""
    if sort_name == "oldest":
        return query.order_by(models.Ticket.created_at.asc()), None
    if sort_name == "status":
        return query.order_by(models.Ticket.status.asc(), models.Ticket.created_at.desc()), None
    if sort_name == "priority":
        return query, lambda t: _PRIORITY_ORDER.get(t.priority, 99)
    return query.order_by(models.Ticket.created_at.desc()), None


def queue_counts(db: Session, tenant_id: uuid.UUID) -> dict:
    base = tenant_tickets(db, tenant_id)
    return {name: apply_queue_filter(base, name).count() for name in QUEUE_FILTERS}


def next_ticket_number(db: Session, tenant_id: uuid.UUID) -> str:
    count = db.query(models.Ticket).filter(models.Ticket.tenant_id == tenant_id).count()
    return f"TCK-{count + 1:06d}"


def apply_sla(db: Session, ticket: "models.Ticket") -> None:
    policy = (
        db.query(models.SLAPolicy)
        .filter(models.SLAPolicy.tenant_id == ticket.tenant_id, models.SLAPolicy.priority == ticket.priority)
        .first()
    )
    if not policy:
        return
    ticket.sla_policy_id = policy.id
    anchor = ticket.created_at or datetime.now(timezone.utc)
    ticket.response_due_at = anchor + timedelta(minutes=policy.response_time_minutes)
    ticket.resolution_due_at = anchor + timedelta(minutes=policy.resolution_time_minutes)