src/app/models/ticket.py

"""
TICKET Models - Work Orders

Tickets are work orders assigned to field agents.

Sources:
- sales_order: Installation tickets (new customer service)
- incident: Support tickets (existing customer issue)  
- task: Infrastructure tickets (project rollout)

Deduplication Strategy:
- dedup_key = MD5(source + source_id + ticket_type)
- Prevents duplicate ACTIVE tickets from same source
- Cleared on completion/cancellation to allow re-creation
- This is useful because FK alone doesn't prevent re-creation (e.g., cancelled then recreated)
"""
from sqlalchemy import Column, String, Boolean, Integer, Text, Date, DateTime, Numeric, ForeignKey, CheckConstraint
from sqlalchemy.dialects.postgresql import UUID, JSONB, ENUM
from sqlalchemy.orm import relationship
from datetime import datetime, date
from typing import Optional
import uuid
import hashlib

from app.models.base import BaseModel
from app.models.enums import TicketSource, TicketType, TicketStatus, TicketPriority, ServiceType


class Ticket(BaseModel):
    """
    Ticket (Work Order) Model
    
    Represents work orders assigned to field agents.
    Can originate from sales orders, incidents, or tasks.
    """
    __tablename__ = "tickets"
    
    # Project Link
    project_id = Column(UUID(as_uuid=True), ForeignKey("projects.id", ondelete="RESTRICT"), nullable=False, index=True)
    # Note: Subcontractor assignment handled via ticket_assignments → users → project_team
    
    # Source Tracking (Polymorphic)
    source = Column(ENUM(TicketSource, name="ticket_source", create_type=False, values_callable=lambda x: [e.value for e in x]), nullable=False)
    source_id = Column(UUID(as_uuid=True), nullable=True)
    ticket_name = Column(Text, nullable=True)  # Cached from source (customer/task name)
    
    # Ticket Type & Work Details
    ticket_type = Column(ENUM(TicketType, name="ticket_type", create_type=False, values_callable=lambda x: [e.value for e in x]), nullable=False)
    service_type = Column(ENUM(ServiceType, name="service_type", create_type=False, values_callable=lambda x: [e.value for e in x]), nullable=True)
    work_description = Column(Text, nullable=True)
    
    # Status & Priority
    status = Column(ENUM(TicketStatus, name="ticket_status", create_type=False, values_callable=lambda x: [e.value for e in x]), nullable=False, default=TicketStatus.OPEN.value)
    priority = Column(ENUM(TicketPriority, name="ticket_priority", create_type=False, values_callable=lambda x: [e.value for e in x]), nullable=False, default=TicketPriority.NORMAL.value)
    
    # Scheduling
    scheduled_date = Column(Date, nullable=True)
    scheduled_time_slot = Column(String(100), nullable=True)
    
    # Deadline Tracking & SLA
    due_date = Column(DateTime(timezone=True), nullable=True)
    sla_target_date = Column(DateTime(timezone=True), nullable=True)
    sla_violated = Column(Boolean, nullable=False, default=False)
    
    # Execution Timeline
    started_at = Column(DateTime(timezone=True), nullable=True)
    completed_at = Column(DateTime(timezone=True), nullable=True)
    
    # Invoicing Tracking
    is_invoiced = Column(Boolean, nullable=False, default=False)
    invoiced_at = Column(DateTime(timezone=True), nullable=True)
    contractor_invoice_id = Column(UUID(as_uuid=True), ForeignKey("contractor_invoices.id", ondelete="SET NULL"), nullable=True)
    
    # Region Reference (inherited from source)
    project_region_id = Column(UUID(as_uuid=True), ForeignKey("project_regions.id", ondelete="SET NULL"), nullable=True)
    
    # Work Location (GPS where work was performed)
    work_location_latitude = Column(Numeric(precision=10, scale=7), nullable=True)
    work_location_longitude = Column(Numeric(precision=10, scale=7), nullable=True)
    work_location_accuracy = Column(Numeric(precision=10, scale=2), nullable=True)
    work_location_verified = Column(Boolean, nullable=False, default=False)
    
    # Deduplication Key (Permanent - includes timestamp)
    # Format: MD5(source + source_id + ticket_type + created_at)
    # NEVER cleared - permanently unique
    # One source can only create ONE ticket ever (business rule)
    dedup_key = Column(Text, nullable=True, unique=True)
    
    # Team Size Tracking (Optimistic Locking for Ghost Workers)
    required_team_size = Column(Integer, nullable=False, default=1)  # How many agents needed
    # assigned_team_size is computed from active assignments (see property below)
    
    # Completion Tracking
    completion_data = Column(JSONB, nullable=False, default=dict, server_default='{}')  # Activation data (ONT serial, etc.)
    completion_photos_verified = Column(Boolean, nullable=False, default=False)  # PM verified photos
    completion_data_verified = Column(Boolean, nullable=False, default=False)  # PM verified data
    
    # Metadata
    notes = Column(Text, nullable=True)
    additional_metadata = Column(JSONB, nullable=False, default=dict, server_default='{}')

    # Timesheet Sync Tracking
    timesheet_synced = Column(Boolean, default=False, nullable=False)  # Has this been synced to timesheets?
    timesheet_synced_at = Column(DateTime(timezone=True), nullable=True)  # When was it synced?

    # Concurrency Control (Optimistic Locking)
    version = Column(Integer, nullable=False, default=1)
    
    # Constraints
    __table_args__ = (
        CheckConstraint(
            "started_at IS NULL OR completed_at IS NULL OR completed_at >= started_at",
            name="chk_ticket_dates"
        ),
    )
    
    # Relationships
    project = relationship("Project", back_populates="tickets")
    project_region = relationship("ProjectRegion", foreign_keys=[project_region_id])
    contractor_invoice = relationship("ContractorInvoice", foreign_keys=[contractor_invoice_id], backref="tickets")
    sales_order = relationship("SalesOrder", back_populates="ticket", uselist=False, foreign_keys="[Ticket.source_id]", 
                               primaryjoin="and_(Ticket.source_id==SalesOrder.id, Ticket.source=='sales_order')")
    assignments = relationship("TicketAssignment", back_populates="ticket", cascade="all, delete-orphan", lazy="select")
    expenses = relationship("TicketExpense", back_populates="ticket", cascade="all, delete-orphan", lazy="select")
    comments = relationship("TicketComment", back_populates="ticket", cascade="all, delete-orphan", lazy="select")
    communications = relationship("CustomerCommunication", back_populates="ticket", cascade="all, delete-orphan", lazy="select")
    images = relationship("TicketImage", back_populates="ticket", cascade="all, delete-orphan", lazy="select")
    progress_reports = relationship("TicketProgressReport", back_populates="ticket", cascade="all, delete-orphan", lazy="select")
    incident_reports = relationship("TicketIncidentReport", back_populates="ticket", cascade="all, delete-orphan", lazy="select")
    status_history = relationship("TicketStatusHistory", back_populates="ticket", cascade="all, delete-orphan", lazy="select")
    
    # ============================================
    # COMPUTED PROPERTIES
    # ============================================
    
    @property
    def is_open(self) -> bool:
        """Check if ticket is open (not yet assigned)"""
        return self.status == TicketStatus.OPEN.value
    
    @property
    def is_assigned(self) -> bool:
        """Check if ticket is assigned (awaiting agent acceptance)"""
        return self.status == TicketStatus.ASSIGNED.value
    
    @property
    def is_in_progress(self) -> bool:
        """Check if ticket is in progress"""
        return self.status == TicketStatus.IN_PROGRESS.value
    
    @property
    def is_completed(self) -> bool:
        """Check if ticket is completed"""
        return self.status == TicketStatus.COMPLETED.value
    
    @property
    def is_cancelled(self) -> bool:
        """Check if ticket is cancelled"""
        return self.status == TicketStatus.CANCELLED.value
    
    @property
    def is_active(self) -> bool:
        """Check if ticket is active (not completed or cancelled)"""
        return self.status not in [TicketStatus.COMPLETED.value, TicketStatus.CANCELLED.value]
    
    @property
    def is_overdue(self) -> bool:
        """Check if ticket is overdue (past due_date and still active)"""
        if not self.due_date or not self.is_active:
            return False
        # Make datetime timezone-aware for comparison
        from datetime import timezone
        now = datetime.now(timezone.utc)
        return now > self.due_date
    
    @property
    def has_region(self) -> bool:
        """Check if ticket has region assignment"""
        return self.project_region_id is not None
    
    @property
    def has_schedule(self) -> bool:
        """Check if ticket has scheduled date"""
        return self.scheduled_date is not None
    
    @property
    def assigned_team_size(self) -> int:
        """
        Auto-calculate number of active assignments.
        Active = ended_at IS NULL
        """
        if not self.assignments:
            return 0
        return sum(1 for a in self.assignments if a.is_active)
    
    @property
    def is_fully_assigned(self) -> bool:
        """Check if ticket has all required agents assigned"""
        return self.assigned_team_size >= self.required_team_size
    
    @property
    def can_be_assigned(self) -> bool:
        """Check if ticket can be assigned to agent"""
        return self.status == TicketStatus.OPEN.value
    
    @property
    def can_be_started(self) -> bool:
        """Check if ticket can be started"""
        return self.status == TicketStatus.ASSIGNED.value
    
    @property
    def can_be_completed(self) -> bool:
        """Check if ticket can be completed"""
        return self.status == TicketStatus.IN_PROGRESS.value
    
    @property
    def can_be_cancelled(self) -> bool:
        """Check if ticket can be cancelled"""
        return self.status in [TicketStatus.OPEN.value, TicketStatus.ASSIGNED.value]
    
    # ============================================
    # BUSINESS LOGIC METHODS
    # ============================================
    
    @staticmethod
    def generate_dedup_key(source: str, source_id: uuid.UUID, ticket_type: str, created_at: datetime) -> str:
        """
        Generate PERMANENT deduplication key for ticket.
        
        Format: MD5(source + '::' + source_id + '::' + ticket_type + '::' + timestamp)
        
        Business Rules:
        - One sales order/task/incident can only create ONE ticket EVER
        - Dedup key is NEVER cleared (permanent unique identifier)
        - If a ticket is cancelled and work needs to be redone:
          → Create NEW sales order → Creates NEW ticket
        - Timestamp ensures uniqueness even if same source tries multiple times
        
        Why permanent?
        - Real-world: Once a sales order is fulfilled, it shouldn't create another work order
        - If cancelled and needs re-fulfillment → NEW sales order (new business transaction)
        - Prevents accidental duplicate ticket creation
        - Audit trail: One source = One ticket forever
        """
        timestamp = created_at.isoformat()
        dedup_string = f"{source}::{str(source_id)}::{ticket_type}::{timestamp}"
        return hashlib.md5(dedup_string.encode()).hexdigest()
    
    def mark_as_assigned(self) -> None:
        """Mark ticket as assigned"""
        self.status = TicketStatus.ASSIGNED.value
        self.updated_at = datetime.utcnow()
    
    def mark_as_in_progress(self) -> None:
        """Mark ticket as in progress"""
        self.status = TicketStatus.IN_PROGRESS.value
        if not self.started_at:
            self.started_at = datetime.utcnow()
        self.updated_at = datetime.utcnow()
    
    def mark_as_completed(self) -> None:
        """Mark ticket as completed (dedup_key remains permanent)"""
        self.status = TicketStatus.COMPLETED.value
        self.completed_at = datetime.utcnow()
        self.updated_at = datetime.utcnow()
        # Note: dedup_key is NEVER cleared - it's permanent
    
    def mark_as_cancelled(self, reason: Optional[str] = None) -> None:
        """Mark ticket as cancelled (dedup_key remains permanent)"""
        self.status = TicketStatus.CANCELLED.value
        if reason:
            self.notes = f"Cancellation reason: {reason}\n{self.notes or ''}"
        self.updated_at = datetime.utcnow()
        # Note: dedup_key is NEVER cleared - it's permanent
    
    def reschedule(self, new_date: date, new_time_slot: Optional[str] = None, reason: Optional[str] = None) -> None:
        """
        Reschedule ticket to new date/time.
        Note: This changes the ticket schedule, original sales_order schedule remains unchanged.
        """
        old_date = self.scheduled_date
        self.scheduled_date = new_date
        if new_time_slot:
            self.scheduled_time_slot = new_time_slot
        
        # Log reschedule in notes
        if reason:
            reschedule_note = f"Rescheduled from {old_date} to {new_date}. Reason: {reason}\n"
            self.notes = reschedule_note + (self.notes or '')
        
        self.updated_at = datetime.utcnow()
    
    def __repr__(self):
        return f"<Ticket {self.id} ({self.ticket_type}) - {self.status}>"