"""Module 1 — New connection applications, status history, documents."""
from datetime import date, datetime

from sqlalchemy import Date, DateTime, ForeignKey, Index, Integer, 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 ApplicationStatus, ConnectionType, DocumentType, pg_enum


class ConnectionApplication(Base, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "connection_applications"

    id: Mapped[int] = mapped_column(primary_key=True)
    application_no: Mapped[str] = mapped_column(String(30), nullable=False, index=True)

    # Applicant details (kept on the application; copied to customers on install)
    applicant_name: Mapped[str] = mapped_column(String(150), nullable=False)
    father_name: Mapped[str | None] = mapped_column(String(150), nullable=True)
    phone: Mapped[str] = mapped_column(String(20), nullable=False, index=True)
    alt_phone: Mapped[str | None] = mapped_column(String(20), nullable=True)
    nid: Mapped[str | None] = mapped_column(String(30), nullable=True, index=True)
    email: Mapped[str | None] = mapped_column(String(255), nullable=True)
    address: Mapped[str] = mapped_column(Text, nullable=False)
    area_id: Mapped[int] = mapped_column(
        ForeignKey("areas.id", ondelete="RESTRICT"), nullable=False, index=True
    )

    requested_date: Mapped[date] = mapped_column(Date, nullable=False, index=True)
    connection_type: Mapped[ConnectionType] = mapped_column(
        pg_enum(ConnectionType, "connection_type"),
        nullable=False,
        default=ConnectionType.ONU_OFFICE,
        server_default=ConnectionType.ONU_OFFICE.value,
    )
    assigned_technician_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True
    )
    status: Mapped[ApplicationStatus] = mapped_column(
        pg_enum(ApplicationStatus, "application_status"),
        nullable=False,
        default=ApplicationStatus.PENDING,
        server_default=ApplicationStatus.PENDING.value,
        index=True,
    )
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    # Workflow milestones
    survey_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    surveyed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    approved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    installed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    rejected_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    rejection_reason: Mapped[str | None] = mapped_column(Text, nullable=True)

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

    area = relationship("Area", lazy="joined")
    technician = relationship("User", foreign_keys=[assigned_technician_id], lazy="joined")
    created_by = relationship("User", foreign_keys=[created_by_id], lazy="joined")
    connection = relationship("Connection", back_populates="application", uselist=False)
    status_history: Mapped[list["ApplicationStatusHistory"]] = relationship(
        back_populates="application",
        cascade="all, delete-orphan",
        order_by="ApplicationStatusHistory.changed_at",
    )
    documents: Mapped[list["ApplicationDocument"]] = relationship(
        back_populates="application", cascade="all, delete-orphan"
    )

    __table_args__ = (
        Index(
            "uq_applications_application_no_active",
            "application_no",
            unique=True,
            postgresql_where=text("is_deleted = false"),
        ),
        Index("ix_applications_status_requested_date", "status", "requested_date"),
        Index("ix_applications_area_status", "area_id", "status"),
        Index("ix_applications_applicant_name", "applicant_name"),
    )


class ApplicationStatusHistory(Base):
    """Append-only: who changed the status, from what, to what, when."""

    __tablename__ = "application_status_history"

    id: Mapped[int] = mapped_column(primary_key=True)
    application_id: Mapped[int] = mapped_column(
        ForeignKey("connection_applications.id", ondelete="CASCADE"), nullable=False, index=True
    )
    from_status: Mapped[ApplicationStatus | None] = mapped_column(
        pg_enum(ApplicationStatus, "application_status", create_type=False),
        nullable=True,
    )
    to_status: Mapped[ApplicationStatus] = mapped_column(
        pg_enum(ApplicationStatus, "application_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)

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


class ApplicationDocument(Base, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "application_documents"

    id: Mapped[int] = mapped_column(primary_key=True)
    application_id: Mapped[int] = mapped_column(
        ForeignKey("connection_applications.id", ondelete="CASCADE"), nullable=False, index=True
    )
    doc_type: Mapped[DocumentType] = mapped_column(
        pg_enum(DocumentType, "document_type"),
        nullable=False,
        default=DocumentType.OTHER,
    )
    file_name: Mapped[str] = mapped_column(String(255), nullable=False)
    stored_path: Mapped[str] = mapped_column(String(500), nullable=False)
    content_type: Mapped[str | None] = mapped_column(String(120), nullable=True)
    file_size: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    uploaded_by_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )

    application: Mapped["ConnectionApplication"] = relationship(back_populates="documents")
    uploaded_by = relationship("User", lazy="joined")