denya-onecare/app/models/ticket.py

"""Ticket, TicketTimeline, TicketPhoto, and Escalation models."""

from __future__ import annotations

from datetime import datetime
from decimal import Decimal

from sqlalchemy import DateTime, ForeignKey, Integer, Numeric, String, Text, func
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.core.database import Base


class Ticket(Base):
    __tablename__ = "tickets"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    ticket_number: Mapped[str] = mapped_column(
        String(20),
        unique=True,
        nullable=False,
        index=True,
        comment="Format: PAV-YYYY-NNNNN",
    )
    status: Mapped[str] = mapped_column(
        String(30),
        nullable=False,
        default="New",
        comment=(
            "New, Logged, Triage, Assigned, Accepted, Travelling, On Site, "
            "In Progress, Waiting Parts, Escalated, Completed, "
            "On-Field Verification, Wahab Review, Closed, Reopened, Cancelled"
        ),
    )
    unit_id: Mapped[int | None] = mapped_column(Integer, ForeignKey("units.id"), nullable=True)
    category_id: Mapped[int | None] = mapped_column(Integer, ForeignKey("categories.id"), nullable=True)
    priority: Mapped[str | None] = mapped_column(
        String(10),
        nullable=True,
        comment="urgent, high, medium, low",
    )
    reporter: Mapped[str | None] = mapped_column(String(255), nullable=True)
    phone: Mapped[str | None] = mapped_column(String(50), nullable=True)
    reported_via: Mapped[str | None] = mapped_column(
        String(20),
        nullable=True,
        comment="whatsapp, phone, walk-in, qr, agent",
    )
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    assigned_to: Mapped[int | None] = mapped_column(Integer, ForeignKey("users.id"), nullable=True)
    sla_deadline: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    eta: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    cost: Mapped[Decimal | None] = mapped_column(Numeric(10, 2), nullable=True)
    parts_used: Mapped[str | None] = mapped_column(Text, nullable=True)
    customer_rating: Mapped[int | None] = mapped_column(Integer, nullable=True)
    reopen_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    closed_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    reported_at: Mapped[datetime | None] = mapped_column(
        DateTime,
        nullable=True,
        comment="Original reported date. Backdated/backfilled tickets keep their true report date; NULL falls back to created_at.",
    )
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now(), nullable=False)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime,
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )

    # relationships
    unit = relationship("Unit")
    category = relationship("Category")
    assigned_technician = relationship("User", back_populates="assigned_tickets", foreign_keys=[assigned_to])
    timeline = relationship("TicketTimeline", back_populates="ticket", order_by="TicketTimeline.created_at")
    photos = relationship("TicketPhoto", back_populates="ticket")
    escalations = relationship("Escalation", back_populates="ticket")

    @property
    def assigned_technician_name(self) -> str | None:
        return self.assigned_technician.full_name if self.assigned_technician else None

    def __repr__(self) -> str:
        return f"<Ticket {self.ticket_number} ({self.status})>"


class TicketTimeline(Base):
    __tablename__ = "ticket_timeline"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    ticket_id: Mapped[int] = mapped_column(Integer, ForeignKey("tickets.id"), nullable=False)
    from_status: Mapped[str | None] = mapped_column(String(30), nullable=True)
    to_status: Mapped[str | None] = mapped_column(String(30), nullable=True)
    note: Mapped[str | None] = mapped_column(Text, nullable=True)
    user_id: Mapped[int | None] = mapped_column(Integer, ForeignKey("users.id"), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now(), nullable=False)

    ticket = relationship("Ticket", back_populates="timeline")
    user = relationship("User", back_populates="timeline_entries")

    def __repr__(self) -> str:
        return f"<TicketTimeline {self.ticket_id}: {self.from_status} → {self.to_status}>"


class TicketPhoto(Base):
    __tablename__ = "ticket_photos"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    ticket_id: Mapped[int] = mapped_column(Integer, ForeignKey("tickets.id"), nullable=False)
    photo_url: Mapped[str] = mapped_column(String(500), nullable=False)
    is_before: Mapped[bool] = mapped_column(nullable=False, default=True)

    ticket = relationship("Ticket", back_populates="photos")

    def __repr__(self) -> str:
        return f"<TicketPhoto {self.id} ticket={self.ticket_id}>"


class Escalation(Base):
    __tablename__ = "escalations"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    ticket_id: Mapped[int] = mapped_column(Integer, ForeignKey("tickets.id"), nullable=False)
    escalated_to: Mapped[int] = mapped_column(Integer, ForeignKey("users.id"), nullable=False)
    reason: Mapped[str | None] = mapped_column(Text, nullable=True)
    resolved_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now(), nullable=False)

    ticket = relationship("Ticket", back_populates="escalations")
    escalated_to_user = relationship("User", back_populates="escalations", foreign_keys=[escalated_to])

    def __repr__(self) -> str:
        return f"<Escalation ticket={self.ticket_id} → user={self.escalated_to}>"