"""Module 3 — Complaints, status history."""
from datetime import datetime

from sqlalchemy import DateTime, ForeignKey, Index, String, Text, text
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.db.base_class import Base, SoftDeleteMixin, TimestampMixin
from app.models.enums import ComplaintPriority, ComplaintStatus, ComplaintType, pg_enum


class Complaint(Base, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "complaints"

    id: Mapped[int] = mapped_column(primary_key=True)
    ticket_no: Mapped[str] = mapped_column(String(30), nullable=False, index=True)
    connection_id: Mapped[int | None] = mapped_column(
        ForeignKey("connections.id", ondelete="RESTRICT"), nullable=True, index=True
    )
    #: Free-text name of the caller, kept for complaints logged without a linked line.
    customer_name: Mapped[str | None] = mapped_column(String(120), nullable=True)
    #: Customer ID as typed or picked from suggestions (billing Cust ID / username
    #: or any custom reference). Free text — the panels own the canonical record.
    customer_ref: Mapped[str | None] = mapped_column(String(80), nullable=True, index=True)
    complaint_type: Mapped[ComplaintType] = mapped_column(
        pg_enum(ComplaintType, "complaint_type"),
        nullable=False,
        index=True,
    )
    description: Mapped[str] = mapped_column(Text, nullable=False)
    priority: Mapped[ComplaintPriority] = mapped_column(
        pg_enum(ComplaintPriority, "complaint_priority", create_type=False),
        nullable=False,
        default=ComplaintPriority.MEDIUM,
        server_default=ComplaintPriority.MEDIUM.value,
        index=True,
    )
    assigned_technician_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True
    )
    status: Mapped[ComplaintStatus] = mapped_column(
        pg_enum(ComplaintStatus, "complaint_status"),
        nullable=False,
        default=ComplaintStatus.OPEN,
        server_default=ComplaintStatus.OPEN.value,
        index=True,
    )
    resolution_notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    contact_phone: Mapped[str | None] = mapped_column(String(20), nullable=True)
    #: Where the customer is located, as they described it over the phone.
    address: Mapped[str | None] = mapped_column(Text, nullable=True)

    reported_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, index=True)
    resolved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True, index=True)
    closed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)

    created_by_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )

    connection = relationship("Connection", back_populates="complaints", lazy="joined")
    technician = relationship("User", foreign_keys=[assigned_technician_id], lazy="joined")
    created_by = relationship("User", foreign_keys=[created_by_id], lazy="joined")
    status_history: Mapped[list["ComplaintStatusHistory"]] = relationship(
        back_populates="complaint",
        cascade="all, delete-orphan",
        order_by="ComplaintStatusHistory.changed_at",
    )

    __table_args__ = (
        Index(
            "uq_complaints_ticket_no_active",
            "ticket_no",
            unique=True,
            postgresql_where=text("is_deleted = false"),
        ),
        Index("ix_complaints_status_reported_at", "status", "reported_at"),
        Index("ix_complaints_priority_status", "priority", "status"),
        Index("ix_complaints_technician_status", "assigned_technician_id", "status"),
    )


class ComplaintStatusHistory(Base):
    """Append-only complaint status trail."""

    __tablename__ = "complaint_status_history"

    id: Mapped[int] = mapped_column(primary_key=True)
    complaint_id: Mapped[int] = mapped_column(
        ForeignKey("complaints.id", ondelete="CASCADE"), nullable=False, index=True
    )
    from_status: Mapped[ComplaintStatus | None] = mapped_column(
        pg_enum(ComplaintStatus, "complaint_status", create_type=False), nullable=True
    )
    to_status: Mapped[ComplaintStatus] = mapped_column(
        pg_enum(ComplaintStatus, "complaint_status", create_type=False), nullable=False
    )
    changed_by_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    changed_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, index=True)
    remarks: Mapped[str | None] = mapped_column(Text, nullable=True)

    complaint: Mapped["Complaint"] = relationship(back_populates="status_history")
    changed_by = relationship("User", lazy="joined")