from __future__ import annotations

from datetime import datetime
from typing import TYPE_CHECKING, List
from uuid import UUID

from sqlalchemy import DateTime, ForeignKey, Index, String, text
from sqlalchemy.dialects.postgresql import UUID as PG_UUID
from sqlalchemy.orm import Mapped, mapped_column, relationship

from src.core.constants import TicketStatus
from src.database.base import TenantModel

if TYPE_CHECKING:
    from src.apps.support.models.support_ticket_message import SupportTicketMessage


class SupportTicket(TenantModel):
    """One row per support ticket, tenant-scoped (INDL-57).

    No soft delete — tickets are never deleted, only status-transitioned
    (see TicketStatus). `ticket_number` is a platform-wide, human-readable
    identifier (e.g. "SUP-000001") generated by the service layer from the
    `support_ticket_seq` Postgres sequence (migration 0074), not by the ORM.
    """

    __tablename__ = "support_tickets"

    __table_args__ = (
        Index("idx_support_tickets_tenant_created", "tenant_id", "created_at"),
        Index("idx_support_tickets_status_created", "status", "created_at"),
    )

    # Override tenant_id with explicit FK, matching Record/other tenant models.
    tenant_id: Mapped[UUID] = mapped_column(
        PG_UUID(as_uuid=True),
        ForeignKey("accounts.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )

    ticket_number: Mapped[str] = mapped_column(String(20), nullable=False, unique=True)
    subject: Mapped[str] = mapped_column(String(200), nullable=False)
    status: Mapped[str] = mapped_column(
        String(20), nullable=False, default=TicketStatus.OPEN.value, server_default="open"
    )
    created_by_user_id: Mapped[UUID] = mapped_column(
        PG_UUID(as_uuid=True), ForeignKey("users.id"), nullable=False
    )
    last_message_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=text("NOW()")
    )

    messages: Mapped[List["SupportTicketMessage"]] = relationship(
        back_populates="ticket", cascade="all, delete-orphan"
    )